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", decode(OUTER.tax_unit_id,null,null,(select HOU_GRE.NAME from HR_ORGANIZATION_UNITS HOU_GRE WHERE HOU_GRE.ORGANIZATION_ID = OUTER.tax_unit_id and rownum = 1)) gre_name FROM (SELECT 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, decode(ppa.action_type,'EC',paa.tax_unit_id,(select paa1.tax_unit_id from PAY_ASSIGNMENT_ACTIONS paa1, pay_action_interlocks pai where paa1.assignment_action_id = pai.LOCKED_ACTION_ID and pai.locking_action_id = paa.assignment_action_id and rownum = 1)) 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 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"
, DECODE(OUTER.TAX_UNIT_ID
, NULL
, NULL
, (SELECT HOU_GRE.NAME
FROM HR_ORGANIZATION_UNITS HOU_GRE
WHERE HOU_GRE.ORGANIZATION_ID = OUTER.TAX_UNIT_ID
AND ROWNUM = 1)) GRE_NAME
FROM (SELECT 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
, DECODE(PPA.ACTION_TYPE
, 'EC'
, PAA.TAX_UNIT_ID
, (SELECT PAA1.TAX_UNIT_ID
FROM PAY_ASSIGNMENT_ACTIONS PAA1
, PAY_ACTION_INTERLOCKS PAI
WHERE PAA1.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND ROWNUM = 1)) 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
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