PERWSDOH: Missing Rows in PA_ORG_HIERARCHY_DENORM After Organizations are Moved Within the HR Hierarchy (Doc ID 2296495.1)

A.  Users are unable to generate Projects Intercompany Invoices for specific transactions.  PAICGEN: PRC: Generate Intercompany Invoices for a Range
of Projects process has following errors on the Invoice Item Eligibility Report:

There is no schedule line found for the schedule id, provider, receiver organization and operating unit combinations using transfer price rules

Issue was determine to be a code issue and development advises:

Follow the below action plan in a test instance. This action plan is to ensure the latest code is available to insert TP_SCHEDULE usage data in pa_org_hierarchy_denorm table and also to verify the behavior of adding one org to an existing hierarchy.  Once it is confirmed that the data insertion is correct, the datafix will need to be logged to address the existing missing records.

1. Please apply released patch 26628977 or 26661179
2. Create a new organization in 'Define Organization' window.
3. Classify this new organization as both 'Project Task Owning Organization' and 'Project Expend/Event Organization'.
4. Add this organization in one of the Organization Hierarchies.

Steps to Reproduce:

Projects responsibility | Other | Request | Run the process PRC: Generate Intercompany Invoices for a Range of Projects

B.  When trying to choose the organization in a project template, the following error appears after changes to the project hierarchy:

ERROR:

FRM-40212: Invalid value for field ORGANIZATION_NAME

The PA_ORG_HIERARCHY_DENORM table was not in synch with Project Hierarchy changes made previously.  

Steps to Reproduce:

Projects responsibility | Setup | Project | Project Template

C.  When matching a role to a person, no result is being fetch during the resource search.

Errors:

1.Please enter an Expenditure Starting Organization to search for resources.
2.The specified Starting Organization does not belong to the specified Organizational Hierarchy
3.This requirement has already ended. Please select a requirement which has not already ended in order to perform a resource search

or

When selecting Organization Hierarchy in the resource matching search criteria, the correct organization hierarchy and operating
unit is selected but the starting organization hierarchy LOV shows incorrect values.

Steps to Reproduce:

1.  Staffing Manager responsibility
2.  Open Requirements
3.  Enter the project
4.  Project tab:  Open Resource Requirements
5.  Select Match resources
6.  Modify the Search Criteria
7.  Search by Starting Organization gives the error

Workaround:  R12: Resources not Appearing in Resource Searches (Doc ID 1557503.1)

The permanent solution is to apply the below patch

D.  Poor performance with the following statement when applying changes to an organization

SELECT A.ORGANIZATION_ID
FROM
PA_ALL_ORGANIZATIONS A LEFT OUTER JOIN ( SELECT SE.ORGANIZATION_ID_CHILD
 ORGANIZATION_ID FROM PER_ORG_STRUCTURE_ELEMENTS SE WHERE
 ORG_STRUCTURE_VERSION_ID = :B1 AND EXISTS (SELECT 'X' FROM
 HR_ORGANIZATION_INFORMATION INFO WHERE INFO.ORGANIZATION_ID =
 SE.ORGANIZATION_ID_CHILD AND INFO.ORG_INFORMATION1 = 'PA_PROJECT_ORG' AND
 INFO.ORG_INFORMATION_CONTEXT||'' = 'CLASS' AND INFO.ORG_INFORMATION2 = 'Y')
 CONNECT BY PRIOR SE.ORGANIZATION_ID_CHILD = SE.ORGANIZATION_ID_PARENT AND
 ORG_STRUCTURE_VERSION_ID = :B1 START WITH SE.ORGANIZATION_ID_PARENT = :B2
 AND ORG_STRUCTURE_VERSION_ID = :B1 UNION SELECT :B2 FROM SYS.DUAL WHERE
 EXISTS (SELECT 'X' FROM HR_ORGANIZATION_INFORMATION INFO WHERE
 INFO.ORG_INFORMATION1 = 'PA_PROJECT_ORG' AND
 INFO.ORG_INFORMATION_CONTEXT||'' = 'CLASS' AND INFO.ORG_INFORMATION2 = 'Y'
 AND INFO.ORGANIZATION_ID = :B2 )) B ON A.ORGANIZATION_ID= B.ORGANIZATION_ID
 WHERE A.PA_ORG_USE_TYPE = 'PROJECTS' AND A.INACTIVE_DATE IS NULL AND
 A.ORG_ID = :B3 AND B.ORGANIZATION_ID IS NULL

Steps to Reproduce:

Projects responsibility | Setup | Resources and Organization | HR Foundation | Work Structures | Organization 

E.  When attempting to change the parent organization of an organization to a different organization, all the data with pa_org_use_type as 'EXPENDITURES' is deleted from the table 'PA_ORG_HIERARCHY_DENORM' .

Steps to Reproduce:

1. Run below query:

Select * from pa_org_hierarchy_denorm
where pa_org_use_type='EXPENDITURES'

2. Projects responsibility | Setup | Resources and Organization | HR Foundation | Work Structures | Organization.  Update and save.

3. Check the issue by running below query:

Select * from pa_org_hierarchy_denorm
where pa_org_use_type='EXPENDITURES'

Note:  The "EXPENDITURES" entries are deleted.

F.  When trying to add a new organization to the existing global hierarchy, the system is giving the error ORA-01403: NO DATA FOUND

Steps to Reproduce:

1. Projects responsibility | Setup | Resources and Organization | HR Foundation | Work Structures | Organization | Hierarchy
2. Query up the Project's Hierarchy and move one of the organizations.