Performance Issue in PRC: Process Payroll Actuals with PAY_COSTING_PROJECTS_V (Doc ID 2024412.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Costing - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

PRC: Process Payroll Actuals has a performance issue with the following:

INSERT INTO PA_PAY_INTERFACE_TEMP ( COST_TYPE, EFFECTIVE_DATE,
 BUSINESS_GROUP_ID, CONSOLIDATION_SET_NAME, PAYROLL_NAME, ORGANIZATION_NAME,
 LOCATION_CODE, FULL_NAME, FIRST_NAME, LAST_NAME, MIDDLE_NAMES,
 EMPLOYEE_NUMBER, NATIONAL_IDENTIFIER, ASSIGNMENT_NUMBER, ELEMENT_NAME,
 REPORTING_NAME, CLASSIFICATION_NAME, INPUT_VALUE_NAME, UOM,
 OUTPUT_CURRENCY_CODE, CREDIT_AMOUNT, DEBIT_AMOUNT, CONCATENATED_SEGMENTS,
 SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5, SEGMENT6, SEGMENT7,
 SEGMENT8, SEGMENT9, SEGMENT10, SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14,
 SEGMENT15, SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20, SEGMENT21,
  SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25, SEGMENT26, SEGMENT27,
 SEGMENT28, SEGMENT29, SEGMENT30, PAYROLL_ACTION_ID, ASSIGNMENT_ACTION_ID,
 CONSOLIDATION_SET_ID, PAYROLL_ID, TAX_UNIT_ID, ORGANIZATION_ID, LOCATION_ID,
  PERSON_ID, ASSIGNMENT_ID, CLASSIFICATION_ID, ELEMENT_TYPE_ID,
 INPUT_VALUE_ID, RUN_RESULT_ID, GRE_NAME, COST_ID, TRANSFERED_TO_PRJ,
 TIME_PERIOD_ID, TIME_PERIOD_START_DATE, TIME_PERIOD_END_DATE,
 SOURCE_CREATOR_TYPE, SOURCE_START_DATE, SOURCE_END_DATE ) SELECT COST_TYPE,
 EFFECTIVE_DATE, PCPV.BUSINESS_GROUP_ID, CONSOLIDATION_SET_NAME,
 PAYROLL_NAME, ORGANIZATION_NAME, LOCATION_CODE, FULL_NAME, FIRST_NAME,
 LAST_NAME, MIDDLE_NAMES, PCPV.EMPLOYEE_NUMBER, NATIONAL_IDENTIFIER,
 PCPV.ASSIGNMENT_NUMBER, ELEMENT_NAME, REPORTING_NAME, CLASSIFICATION_NAME,
 INPUT_VALUE_NAME, UOM, OUTPUT_CURRENCY_CODE, CREDIT_AMOUNT, DEBIT_AMOUNT,
 CONCATENATED_SEGMENTS, SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
 SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10, SEGMENT11, SEGMENT12,
 SEGMENT13, SEGMENT14, SEGMENT15, SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19,
  SEGMENT20, SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,
 SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30, PAYROLL_ACTION_ID,
 ASSIGNMENT_ACTION_ID, CONSOLIDATION_SET_ID, PCPV.PAYROLL_ID, TAX_UNIT_ID,
 PCPV.ORGANIZATION_ID, PCPV.LOCATION_ID, PCPV.PERSON_ID, PCPV.ASSIGNMENT_ID,
 CLASSIFICATION_ID, ELEMENT_TYPE_ID, INPUT_VALUE_ID, RUN_RESULT_ID, GRE_NAME,
  COST_ID, TRANSFERED_TO_PRJ, TIME_PERIOD_ID, TIME_PERIOD_START_DATE,
 TIME_PERIOD_END_DATE, SOURCE_CREATOR_TYPE, (CASE WHEN SOURCE_CREATOR_TYPE
 IN ('R','RR','EE','PR','NR') THEN SOURCE_START_DATE ELSE NULL END), (CASE
 WHEN SOURCE_CREATOR_TYPE IN ('R','RR','EE','PR','NR') THEN SOURCE_END_DATE
 ELSE NULL END) FROM PAY_COSTING_PROJECTS_V PCPV , PER_ALL_ASSIGNMENTS_F
 PAAF , HR_ORGANIZATION_INFORMATION HOI WHERE PCPV.PERSON_ID =
 PAAF.PERSON_ID AND PCPV.ASSIGNMENT_NUMBER = PAAF.ASSIGNMENT_NUMBER AND
 HOI.ORGANIZATION_ID = PAAF.ORGANIZATION_ID AND HOI.ORG_INFORMATION1 =
 TO_CHAR(FND_GLOBAL.ORG_ID) AND PCPV.TIME_PERIOD_END_DATE BETWEEN (:B5 ) AND
 ( :B4 ) AND PCPV.PAYROLL_ID = NVL(:B3 ,PCPV.PAYROLL_ID) AND PCPV.PERSON_ID =
  NVL(:B2 ,PCPV.PERSON_ID) AND PCPV.ORGANIZATION_ID = NVL(:B1 ,
 PCPV.ORGANIZATION_ID) AND BALANCE_OR_COST = 'C' AND NVL(TRANSFERED_TO_PRJ,
 'N' ) = 'N'

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms