eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: APPS.PAY_COSTING_DETAILS_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."INPUT_VALUE_NAME",OUTER."UOM",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 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 FROM PAY_PAYROLL_ACTIONS PPA, PAY_ASSIGNMENT_ACTIONS PAA, 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 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 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 PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID 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 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."INPUT_VALUE_NAME"
, OUTER."UOM"
, 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 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
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, 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
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 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 PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
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 PCAK.COST_ALLOCATION_KEYFLEX_ID = PC.COST_ALLOCATION_KEYFLEX_ID) OUTER