VIEW: APPS.PAY_COSTING_PROJECTS_V

Source

View Text - Preformatted

SELECT OUTER."COST_TYPE", OUTER."EFFECTIVE_DATE", OUTER."BUSINESS_GROUP_ID", OUTER."CONSOLIDATION_SET_NAME", OUTER."PAYROLL_NAME", OUTER."ORGANIZATION_NAME", OUTER."LOCATION_CODE", OUTER."FULL_NAME", OUTER."FIRST_NAME", OUTER."LAST_NAME", OUTER."MIDDLE_NAMES", OUTER."EMPLOYEE_NUMBER", OUTER."NATIONAL_IDENTIFIER", OUTER."ASSIGNMENT_NUMBER", OUTER."ELEMENT_NAME", OUTER."REPORTING_NAME", OUTER."CLASSIFICATION_NAME_TL", OUTER."CLASSIFICATION_NAME", OUTER."INPUT_VALUE_NAME", OUTER."UOM", OUTER."OUTPUT_CURRENCY_CODE", OUTER."CREDIT_AMOUNT", OUTER."DEBIT_AMOUNT", OUTER."CONCATENATED_SEGMENTS", OUTER."SEGMENT1", OUTER."SEGMENT2", OUTER."SEGMENT3", OUTER."SEGMENT4", OUTER."SEGMENT5", OUTER."SEGMENT6", OUTER."SEGMENT7", OUTER."SEGMENT8", OUTER."SEGMENT9", OUTER."SEGMENT10", OUTER."SEGMENT11", OUTER."SEGMENT12", OUTER."SEGMENT13", OUTER."SEGMENT14", OUTER."SEGMENT15", OUTER."SEGMENT16", OUTER."SEGMENT17", OUTER."SEGMENT18", OUTER."SEGMENT19", OUTER."SEGMENT20", OUTER."SEGMENT21", OUTER."SEGMENT22", OUTER."SEGMENT23", OUTER."SEGMENT24", OUTER."SEGMENT25", OUTER."SEGMENT26", OUTER."SEGMENT27", OUTER."SEGMENT28", OUTER."SEGMENT29", OUTER."SEGMENT30", OUTER."PAYROLL_ACTION_ID", OUTER."ASSIGNMENT_ACTION_ID", OUTER."CONSOLIDATION_SET_ID", OUTER."PAYROLL_ID", OUTER."TAX_UNIT_ID", OUTER."ORGANIZATION_ID", OUTER."LOCATION_ID", OUTER."PERSON_ID", OUTER."ASSIGNMENT_ID", OUTER."CLASSIFICATION_ID", OUTER."ELEMENT_TYPE_ID", OUTER."INPUT_VALUE_ID", OUTER."RUN_RESULT_ID", PAY_COSTING_DETAIL_REP_PKG.get_costing_tax_unit_name(outer.TAX_UNIT_ID) GRE_NAME , OUTER."COST_ID", OUTER."TRANSFERED_TO_PRJ", OUTER."TIME_PERIOD_ID", OUTER."START_DATE", OUTER."END_DATE", OUTER."CREATOR_TYPE", OUTER."SOURCE_START_DATE", OUTER."SOURCE_END_DATE", OUTER."BALANCE_OR_COST" FROM (SELECT /*+leading(PPA,PCS,PAA,PAF,PAY,PPF)*/ DECODE(PPA.ACTION_TYPE,'EC', DECODE(PC.SOURCE_ID,NULL,'EST_COST','EST_REVERSAL'),'COST_TMP') COST_TYPE, PPA.EFFECTIVE_DATE, PPA.BUSINESS_GROUP_ID, PCS.CONSOLIDATION_SET_NAME, PAY.PAYROLL_NAME, HOU_ORG.NAME ORGANIZATION_NAME, HL.LOCATION_CODE, PPF.FULL_NAME, PPF.FIRST_NAME, PPF.LAST_NAME, PPF.MIDDLE_NAMES, PPF.EMPLOYEE_NUMBER, PPF.NATIONAL_IDENTIFIER, PAF.ASSIGNMENT_NUMBER, PETTL.ELEMENT_NAME, PETTL.REPORTING_NAME, PIVTL.NAME INPUT_VALUE_NAME, DECODE(SUBSTR(PIV.UOM,1,1), 'H', SUBSTR(HR_GENERAL.DECODE_FND_COMM_LOOKUP('UNITS', UOM),1, INSTR(HR_GENERAL.DECODE_FND_COMM_LOOKUP('UNITS', UOM), ' ', 1)) , 'M', HR_GENERAL.DECODE_FND_COMM_LOOKUP('UNITS', UOM) ,NULL) UOM, NVL( DECODE(PC.DEBIT_OR_CREDIT, 'C', PC.COSTED_VALUE, NULL),0) CREDIT_AMOUNT, NVL( DECODE(PC.DEBIT_OR_CREDIT, 'D', PC.COSTED_VALUE, NULL),0) DEBIT_AMOUNT, PCAK.CONCATENATED_SEGMENTS, PCAK.SEGMENT1, PCAK.SEGMENT2, PCAK.SEGMENT3, PCAK.SEGMENT4, PCAK.SEGMENT5, PCAK.SEGMENT6, PCAK.SEGMENT7, PCAK.SEGMENT8, PCAK.SEGMENT9, PCAK.SEGMENT10, PCAK.SEGMENT11, PCAK.SEGMENT12, PCAK.SEGMENT13, PCAK.SEGMENT14, PCAK.SEGMENT15, PCAK.SEGMENT16, PCAK.SEGMENT17, PCAK.SEGMENT18, PCAK.SEGMENT19, PCAK.SEGMENT20, PCAK.SEGMENT21, PCAK.SEGMENT22, PCAK.SEGMENT23, PCAK.SEGMENT24, PCAK.SEGMENT25, PCAK.SEGMENT26, PCAK.SEGMENT27, PCAK.SEGMENT28, PCAK.SEGMENT29, PCAK.SEGMENT30, PPA.PAYROLL_ACTION_ID, PAA.ASSIGNMENT_ACTION_ID, PCS.CONSOLIDATION_SET_ID, PAY.PAYROLL_ID, PAY_COSTING_DETAIL_REP_PKG.get_costing_tax_unit_id(ppa.ACTION_TYPE, paa.TAX_UNIT_ID, paa.assignment_action_id, pet.element_type_id) TAX_UNIT_ID, PAF.ORGANIZATION_ID, HL.LOCATION_ID, PAF.PERSON_ID, PAF.ASSIGNMENT_ID, PET.CLASSIFICATION_ID, PET.ELEMENT_TYPE_ID, PIV.INPUT_VALUE_ID, PC.RUN_RESULT_ID, PC.COST_ID, PC.TRANSFERED_TO_PRJ, PET.OUTPUT_CURRENCY_CODE, PECTL.CLASSIFICATION_NAME CLASSIFICATION_NAME_TL, PEC.CLASSIFICATION_NAME, PTP.TIME_PERIOD_ID, PTP.START_DATE, PTP.END_DATE, PEEF.CREATOR_TYPE, PEEF.SOURCE_START_DATE, PEEF.SOURCE_END_DATE, PC.BALANCE_OR_COST FROM PAY_PAYROLL_ACTIONS PPA, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA1, PAY_ASSIGNMENT_ACTIONS PAA1, PAY_CONSOLIDATION_SETS PCS, PER_ASSIGNMENTS_F PAF, PAY_PAYROLLS_F PAY, PER_PEOPLE_F PPF, HR_ORGANIZATION_UNITS HOU_ORG, HR_LOCATIONS HL, PAY_COSTS PC, PAY_INPUT_VALUES_F PIV, PAY_INPUT_VALUES_F_TL PIVTL, PAY_ELEMENT_TYPES_F PET, PAY_ELEMENT_TYPES_F_TL PETTL, PAY_COST_ALLOCATION_KEYFLEX PCAK, PAY_ELEMENT_CLASSIFICATIONS PEC, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL, PER_TIME_PERIODS PTP, PAY_RUN_RESULTS PRR, PAY_ELEMENT_ENTRIES_F PEEF WHERE PPA.ACTION_TYPE IN ('C', 'S','EC') AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PAA.ACTION_STATUS = 'C' AND PCS.CONSOLIDATION_SET_ID = PPA.CONSOLIDATION_SET_ID AND PC.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PC.RUN_RESULT_ID = PRR.RUN_RESULT_ID AND PRR.SOURCE_ID = PEEF.ELEMENT_ENTRY_ID AND PRR.ASSIGNMENT_ACTION_ID = PAA1.ASSIGNMENT_ACTION_ID AND PAA1.PAYROLL_ACTION_ID = PPA1.PAYROLL_ACTION_ID AND PPA1.EFFECTIVE_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE AND PIV.INPUT_VALUE_ID = PC.INPUT_VALUE_ID AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND PIV.INPUT_VALUE_ID = PIVTL.INPUT_VALUE_ID AND PIVTL.LANGUAGE = USERENV('LANG') AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND PETTL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PETTL.LANGUAGE = USERENV('LANG') AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID AND PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID AND PECTL.LANGUAGE = USERENV('LANG') AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PAF.PRIMARY_FLAG = 'Y' AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE AND PAY.PAYROLL_ID = PAF.PAYROLL_ID +0 AND PAY.PAYROLL_ID > 0 AND PPA.EFFECTIVE_DATE BETWEEN PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE AND HOU_ORG.ORGANIZATION_ID = PAF.ORGANIZATION_ID AND HL.LOCATION_ID (+) = PAF.LOCATION_ID AND PPF.PERSON_ID = PAF.PERSON_ID AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE AND PAF.PAYROLL_ID = PTP.PAYROLL_ID AND PPA1.EFFECTIVE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE AND PCAK.COST_ALLOCATION_KEYFLEX_ID = PC.COST_ALLOCATION_KEYFLEX_ID ) OUTER

View Text - HTML Formatted

SELECT OUTER."COST_TYPE"
, OUTER."EFFECTIVE_DATE"
, OUTER."BUSINESS_GROUP_ID"
, OUTER."CONSOLIDATION_SET_NAME"
, OUTER."PAYROLL_NAME"
, OUTER."ORGANIZATION_NAME"
, OUTER."LOCATION_CODE"
, OUTER."FULL_NAME"
, OUTER."FIRST_NAME"
, OUTER."LAST_NAME"
, OUTER."MIDDLE_NAMES"
, OUTER."EMPLOYEE_NUMBER"
, OUTER."NATIONAL_IDENTIFIER"
, OUTER."ASSIGNMENT_NUMBER"
, OUTER."ELEMENT_NAME"
, OUTER."REPORTING_NAME"
, OUTER."CLASSIFICATION_NAME_TL"
, OUTER."CLASSIFICATION_NAME"
, OUTER."INPUT_VALUE_NAME"
, OUTER."UOM"
, OUTER."OUTPUT_CURRENCY_CODE"
, OUTER."CREDIT_AMOUNT"
, OUTER."DEBIT_AMOUNT"
, OUTER."CONCATENATED_SEGMENTS"
, OUTER."SEGMENT1"
, OUTER."SEGMENT2"
, OUTER."SEGMENT3"
, OUTER."SEGMENT4"
, OUTER."SEGMENT5"
, OUTER."SEGMENT6"
, OUTER."SEGMENT7"
, OUTER."SEGMENT8"
, OUTER."SEGMENT9"
, OUTER."SEGMENT10"
, OUTER."SEGMENT11"
, OUTER."SEGMENT12"
, OUTER."SEGMENT13"
, OUTER."SEGMENT14"
, OUTER."SEGMENT15"
, OUTER."SEGMENT16"
, OUTER."SEGMENT17"
, OUTER."SEGMENT18"
, OUTER."SEGMENT19"
, OUTER."SEGMENT20"
, OUTER."SEGMENT21"
, OUTER."SEGMENT22"
, OUTER."SEGMENT23"
, OUTER."SEGMENT24"
, OUTER."SEGMENT25"
, OUTER."SEGMENT26"
, OUTER."SEGMENT27"
, OUTER."SEGMENT28"
, OUTER."SEGMENT29"
, OUTER."SEGMENT30"
, OUTER."PAYROLL_ACTION_ID"
, OUTER."ASSIGNMENT_ACTION_ID"
, OUTER."CONSOLIDATION_SET_ID"
, OUTER."PAYROLL_ID"
, OUTER."TAX_UNIT_ID"
, OUTER."ORGANIZATION_ID"
, OUTER."LOCATION_ID"
, OUTER."PERSON_ID"
, OUTER."ASSIGNMENT_ID"
, OUTER."CLASSIFICATION_ID"
, OUTER."ELEMENT_TYPE_ID"
, OUTER."INPUT_VALUE_ID"
, OUTER."RUN_RESULT_ID"
, PAY_COSTING_DETAIL_REP_PKG.GET_COSTING_TAX_UNIT_NAME(OUTER.TAX_UNIT_ID) GRE_NAME
, OUTER."COST_ID"
, OUTER."TRANSFERED_TO_PRJ"
, OUTER."TIME_PERIOD_ID"
, OUTER."START_DATE"
, OUTER."END_DATE"
, OUTER."CREATOR_TYPE"
, OUTER."SOURCE_START_DATE"
, OUTER."SOURCE_END_DATE"
, OUTER."BALANCE_OR_COST"
FROM (SELECT /*+LEADING(PPA
, PCS
, PAA
, PAF
, PAY
, PPF)*/ DECODE(PPA.ACTION_TYPE
, 'EC'
, DECODE(PC.SOURCE_ID
, NULL
, 'EST_COST'
, 'EST_REVERSAL')
, 'COST_TMP') COST_TYPE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID
, PCS.CONSOLIDATION_SET_NAME
, PAY.PAYROLL_NAME
, HOU_ORG.NAME ORGANIZATION_NAME
, HL.LOCATION_CODE
, PPF.FULL_NAME
, PPF.FIRST_NAME
, PPF.LAST_NAME
, PPF.MIDDLE_NAMES
, PPF.EMPLOYEE_NUMBER
, PPF.NATIONAL_IDENTIFIER
, PAF.ASSIGNMENT_NUMBER
, PETTL.ELEMENT_NAME
, PETTL.REPORTING_NAME
, PIVTL.NAME INPUT_VALUE_NAME
, DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'H'
, SUBSTR(HR_GENERAL.DECODE_FND_COMM_LOOKUP('UNITS'
, UOM)
, 1
, INSTR(HR_GENERAL.DECODE_FND_COMM_LOOKUP('UNITS'
, UOM)
, ' '
, 1))
, 'M'
, HR_GENERAL.DECODE_FND_COMM_LOOKUP('UNITS'
, UOM)
, NULL) UOM
, NVL( DECODE(PC.DEBIT_OR_CREDIT
, 'C'
, PC.COSTED_VALUE
, NULL)
, 0) CREDIT_AMOUNT
, NVL( DECODE(PC.DEBIT_OR_CREDIT
, 'D'
, PC.COSTED_VALUE
, NULL)
, 0) DEBIT_AMOUNT
, PCAK.CONCATENATED_SEGMENTS
, PCAK.SEGMENT1
, PCAK.SEGMENT2
, PCAK.SEGMENT3
, PCAK.SEGMENT4
, PCAK.SEGMENT5
, PCAK.SEGMENT6
, PCAK.SEGMENT7
, PCAK.SEGMENT8
, PCAK.SEGMENT9
, PCAK.SEGMENT10
, PCAK.SEGMENT11
, PCAK.SEGMENT12
, PCAK.SEGMENT13
, PCAK.SEGMENT14
, PCAK.SEGMENT15
, PCAK.SEGMENT16
, PCAK.SEGMENT17
, PCAK.SEGMENT18
, PCAK.SEGMENT19
, PCAK.SEGMENT20
, PCAK.SEGMENT21
, PCAK.SEGMENT22
, PCAK.SEGMENT23
, PCAK.SEGMENT24
, PCAK.SEGMENT25
, PCAK.SEGMENT26
, PCAK.SEGMENT27
, PCAK.SEGMENT28
, PCAK.SEGMENT29
, PCAK.SEGMENT30
, PPA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PCS.CONSOLIDATION_SET_ID
, PAY.PAYROLL_ID
, PAY_COSTING_DETAIL_REP_PKG.GET_COSTING_TAX_UNIT_ID(PPA.ACTION_TYPE
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PET.ELEMENT_TYPE_ID) TAX_UNIT_ID
, PAF.ORGANIZATION_ID
, HL.LOCATION_ID
, PAF.PERSON_ID
, PAF.ASSIGNMENT_ID
, PET.CLASSIFICATION_ID
, PET.ELEMENT_TYPE_ID
, PIV.INPUT_VALUE_ID
, PC.RUN_RESULT_ID
, PC.COST_ID
, PC.TRANSFERED_TO_PRJ
, PET.OUTPUT_CURRENCY_CODE
, PECTL.CLASSIFICATION_NAME CLASSIFICATION_NAME_TL
, PEC.CLASSIFICATION_NAME
, PTP.TIME_PERIOD_ID
, PTP.START_DATE
, PTP.END_DATE
, PEEF.CREATOR_TYPE
, PEEF.SOURCE_START_DATE
, PEEF.SOURCE_END_DATE
, PC.BALANCE_OR_COST
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA1
, PAY_ASSIGNMENT_ACTIONS PAA1
, PAY_CONSOLIDATION_SETS PCS
, PER_ASSIGNMENTS_F PAF
, PAY_PAYROLLS_F PAY
, PER_PEOPLE_F PPF
, HR_ORGANIZATION_UNITS HOU_ORG
, HR_LOCATIONS HL
, PAY_COSTS PC
, PAY_INPUT_VALUES_F PIV
, PAY_INPUT_VALUES_F_TL PIVTL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_COST_ALLOCATION_KEYFLEX PCAK
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PER_TIME_PERIODS PTP
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_ENTRIES_F PEEF
WHERE PPA.ACTION_TYPE IN ('C'
, 'S'
, 'EC')
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PCS.CONSOLIDATION_SET_ID = PPA.CONSOLIDATION_SET_ID
AND PC.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PC.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRR.SOURCE_ID = PEEF.ELEMENT_ENTRY_ID
AND PRR.ASSIGNMENT_ACTION_ID = PAA1.ASSIGNMENT_ACTION_ID
AND PAA1.PAYROLL_ACTION_ID = PPA1.PAYROLL_ACTION_ID
AND PPA1.EFFECTIVE_DATE BETWEEN PEEF.EFFECTIVE_START_DATE
AND PEEF.EFFECTIVE_END_DATE
AND PIV.INPUT_VALUE_ID = PC.INPUT_VALUE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PIV.INPUT_VALUE_ID = PIVTL.INPUT_VALUE_ID
AND PIVTL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PETTL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV('LANG')
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAF.PRIMARY_FLAG = 'Y'
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAY.PAYROLL_ID = PAF.PAYROLL_ID +0
AND PAY.PAYROLL_ID > 0
AND PPA.EFFECTIVE_DATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE
AND HOU_ORG.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND HL.LOCATION_ID (+) = PAF.LOCATION_ID
AND PPF.PERSON_ID = PAF.PERSON_ID
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PAF.PAYROLL_ID = PTP.PAYROLL_ID
AND PPA1.EFFECTIVE_DATE BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PCAK.COST_ALLOCATION_KEYFLEX_ID = PC.COST_ALLOCATION_KEYFLEX_ID ) OUTER