VIEW: APPS.HR_ADP_ELE_COSTING_V

Source

View Text - Preformatted

SELECT ref.company_code_equivalent company_code_equivalent , hou.name gre_name , hs.segment1 gre_id , pasg.organization_id organization_id , pp.employee_number employee_number , pp.business_group_id business_group_id , pp.person_id person_id , decode(ptp.number_per_fiscal_year, 52,'W',24,'S',26,'B', 12,'M', ptp.number_per_fiscal_year) , pasg.assignment_id assignment_id , pasg.assignment_number assignment_number , decode(pasg.primary_flag, 'Y',0,pasg.assignment_sequence) assignment_sequence , pasg.primary_flag primary_flag , pbee.element_entry_id element_entry_id , pbet.element_name deduction_name , pbee.effective_start_date effective_start_date , pbee.effective_end_date effective_end_date , pca.segment1 cost_segment1 , pca.segment2 cost_segment2 , pca.segment3 cost_segment3 , pca.segment4 cost_segment4 , pca.segment5 cost_segment5 , pca.segment6 cost_segment6 , pca.segment7 cost_segment7 , pca.segment8 cost_segment8 , pca.segment9 cost_segment9 , pca.segment10 cost_segment10 , pca.segment11 cost_segment11 , pca.segment12 cost_segment12 , pca.segment13 cost_segment13 , pca.segment14 cost_segment14 , pca.segment15 cost_segment15 , pca.segment16 cost_segment16 , pca.segment17 cost_segment17 , pca.segment18 cost_segment18 , pca.segment19 cost_segment19 , pca.segment20 cost_segment20 , pca.segment21 cost_segment21 , pca.segment22 cost_segment22 , pca.segment23 cost_segment23 , pca.segment24 cost_segment24 , pca.segment25 cost_segment25 , pca.segment26 cost_segment26 , pca.segment27 cost_segment27 , pca.segment28 cost_segment28 , pca.segment29 cost_segment29 , pca.segment30 cost_segment30 , greatest(nvl(pbee.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pbee.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date) greatest_last_update_date from hr_adp_emp_ref_v ref , per_all_people_f pp , pay_cost_allocation_keyflex pca , hr_all_organization_units hou , hr_soft_coding_keyflex hs , per_time_period_types ptp , pay_payrolls_x ppr , pay_element_entries_f pbee , per_all_assignments_f pasg , pay_element_links_x pbel , pay_element_types_x pbet WHERE pasg.person_id = pp.person_id and pasg.assignment_id = ref.assignment_id and pasg.rowid = ref.asg_rowid and ppr.period_type = ptp.period_type and ppr.payroll_id = pasg.payroll_id and hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and pca.cost_allocation_keyflex_id = pbee.cost_allocation_keyflex_id and pp.employee_number is not null and trunc(hr_adp.get_adp_extract_date) between pp.effective_start_date and pp.effective_end_date and pasg.assignment_type = 'E' and pasg.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(hr_adp.get_adp_extract_date) and a2.assignment_id = pasg.assignment_id)) and pbel.element_type_id = pbet.element_type_id and pbee.element_link_id = pbel.element_link_id and pasg.assignment_id = pbee.assignment_id and pbee.effective_start_date = (select max(pbee2.effective_start_date) from pay_element_entries_f pbee2, pay_element_links_x pbel2 where (pbee2.effective_start_date <= trunc(hr_adp.get_adp_extract_date) and pbee2.assignment_id = pbee.assignment_id and pbee2.element_link_id = pbel2.element_link_id and pbel2.element_type_id = pbet.element_type_id AND ((pbee2.effective_end_date > trunc(hr_adp.get_adp_extract_date)) or (pbee2.effective_end_date >= hr_adp.get_end_deduction_date))))

View Text - HTML Formatted

SELECT REF.COMPANY_CODE_EQUIVALENT COMPANY_CODE_EQUIVALENT
, HOU.NAME GRE_NAME
, HS.SEGMENT1 GRE_ID
, PASG.ORGANIZATION_ID ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PP.PERSON_ID PERSON_ID
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, PASG.ASSIGNMENT_ID ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE) ASSIGNMENT_SEQUENCE
, PASG.PRIMARY_FLAG PRIMARY_FLAG
, PBEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, PBET.ELEMENT_NAME DEDUCTION_NAME
, PBEE.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PBEE.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PCA.SEGMENT1 COST_SEGMENT1
, PCA.SEGMENT2 COST_SEGMENT2
, PCA.SEGMENT3 COST_SEGMENT3
, PCA.SEGMENT4 COST_SEGMENT4
, PCA.SEGMENT5 COST_SEGMENT5
, PCA.SEGMENT6 COST_SEGMENT6
, PCA.SEGMENT7 COST_SEGMENT7
, PCA.SEGMENT8 COST_SEGMENT8
, PCA.SEGMENT9 COST_SEGMENT9
, PCA.SEGMENT10 COST_SEGMENT10
, PCA.SEGMENT11 COST_SEGMENT11
, PCA.SEGMENT12 COST_SEGMENT12
, PCA.SEGMENT13 COST_SEGMENT13
, PCA.SEGMENT14 COST_SEGMENT14
, PCA.SEGMENT15 COST_SEGMENT15
, PCA.SEGMENT16 COST_SEGMENT16
, PCA.SEGMENT17 COST_SEGMENT17
, PCA.SEGMENT18 COST_SEGMENT18
, PCA.SEGMENT19 COST_SEGMENT19
, PCA.SEGMENT20 COST_SEGMENT20
, PCA.SEGMENT21 COST_SEGMENT21
, PCA.SEGMENT22 COST_SEGMENT22
, PCA.SEGMENT23 COST_SEGMENT23
, PCA.SEGMENT24 COST_SEGMENT24
, PCA.SEGMENT25 COST_SEGMENT25
, PCA.SEGMENT26 COST_SEGMENT26
, PCA.SEGMENT27 COST_SEGMENT27
, PCA.SEGMENT28 COST_SEGMENT28
, PCA.SEGMENT29 COST_SEGMENT29
, PCA.SEGMENT30 COST_SEGMENT30
, GREATEST(NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PBEE.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PAY_COST_ALLOCATION_KEYFLEX PCA
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PAY_ELEMENT_ENTRIES_F PBEE
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_ELEMENT_LINKS_X PBEL
, PAY_ELEMENT_TYPES_X PBET
WHERE PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PCA.COST_ALLOCATION_KEYFLEX_ID = PBEE.COST_ALLOCATION_KEYFLEX_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND A2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID))
AND PBEL.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEE.ELEMENT_LINK_ID = PBEL.ELEMENT_LINK_ID
AND PASG.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEE.EFFECTIVE_START_DATE = (SELECT MAX(PBEE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEE2
, PAY_ELEMENT_LINKS_X PBEL2
WHERE (PBEE2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PBEE2.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEE2.ELEMENT_LINK_ID = PBEL2.ELEMENT_LINK_ID
AND PBEL2.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND ((PBEE2.EFFECTIVE_END_DATE > TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)) OR (PBEE2.EFFECTIVE_END_DATE >= HR_ADP.GET_END_DEDUCTION_DATE))))