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.

VIEW: APPS.HR_ADP_ASSIGNMENT_V

Source

View Text - Preformatted


SELECT REF.company_code_equivalent, hou.name, hs.segment1, pasg.organization_id, pp.employee_number, pp.business_group_id, pp.person_id, DECODE (ptp.number_per_fiscal_year, 52, 'W', 24, 'S', 26, 'B', 12, 'M', ptp.number_per_fiscal_year), pasg.assignment_id, pasg.assignment_number, pasg.primary_flag, pasg.effective_start_date, pasg.effective_end_date, decode(pasg.primary_flag, 'Y',0,pasg.assignment_sequence), pj.name, pj.job_information3, pj.job_information1, TO_NUMBER (pbev.screen_entry_value), DECODE (ppb.pay_basis, 'PERIOD', 'P', 'HOURLY', 'H', 'MONTHLY', 'M', 'ANNUAL', 'A', NULL), pasg.normal_hours, ppp.last_change_date, ppp.next_sal_review_date, ppp.proposal_reason, pbee.effective_start_date, hs.segment10, hs.segment11, pps.final_process_date, DECODE (DECODE (LEAST (pasg.effective_end_date + 1, TRUNC (hr_adp.get_adp_extract_date)), pasg.effective_end_date + 1, 'Y', 'NO'), 'Y', 'D', NVL (asta.pay_system_status, astb.pay_system_status)) payroll_status, hou2.name, pj.name, pasg.change_reason, hr_general.decode_lookup('EMP_ASSIGN_REASON',pasg.change_reason), hl.location_code, nvl(hl.loc_information17, hl.region_2), GREATEST (NVL (pp.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date), GREATEST (NVL (pasg.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date), GREATEST (NVL (ppp.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), ppp.change_date), GREATEST (NVL(pps.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), NVL(pps.final_process_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD'))), GREATEST (NVL (pbee.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pbee.effective_start_date), GREATEST (NVL (pj.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), NVL (pj.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD'))), GREATEST (NVL (astb.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), NVL (asta.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD'))), GREATEST ( DECODE( GREATEST( TRUNC(hr_adp.get_adp_extract_date), nvl(ref.final_process_date, to_date ('1900/01/01','YYYY/MM/DD'))), TRUNC(hr_adp.get_adp_extract_date), GREATEST(nvl(ref.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(ref.final_process_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(pp.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), pp.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), pasg.effective_start_date, nvl(ppp.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), ppp.change_date, nvl(pbee.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), pbee.effective_start_date, nvl(pj.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(pj.last_update_date, to_date('1900/01/01','YYYY/MM/DD'))), GREATEST( nvl(ref.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(pp.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), pp.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), pasg.effective_start_date, nvl(ppp.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), ppp.change_date, nvl(pbee.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), pbee.effective_start_date, nvl(pj.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(pj.last_update_date, to_date('1900/01/01','YYYY/MM/DD')))), ref.date_start) greatest_last_update_date FROM hr_adp_emp_ref_v ref, per_ass_status_type_amends asta, per_assignment_status_types astb, hr_all_organization_units hou, hr_all_organization_units hou2, hr_soft_coding_keyflex hs, per_time_period_types ptp, per_pay_proposals ppp, per_periods_of_service pps, per_jobs pj, hr_locations_all hl, per_all_people_f pp, per_pay_bases ppb, per_all_assignments_f pasg, pay_element_entry_values_f pbev, pay_element_entries_f pbee, pay_input_values_f ppiv, pay_element_links_f pbel, pay_element_types_f pbet WHERE TRUNC(SYSDATE) BETWEEN pbel.effective_start_date AND pbel.effective_end_date AND TRUNC(SYSDATE) BETWEEN pbet.effective_start_date AND pbet.effective_end_date AND pasg.person_id = pp.person_id AND pasg.rowid = ref.asg_rowid AND pp.rowid = ref.per_rowid AND nvl(hs.segment18,pasg.location_id) = hl.location_id AND hl.style in ('US', 'US_GLB') AND pasg.assignment_type = 'E' AND pasg.person_id = pps.person_id AND pasg.job_id = pj.job_id AND pps.period_of_service_id = pasg.period_of_service_id AND ref.period_type = ptp.period_type AND hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id AND hs.segment1 = hou.organization_id AND pasg.organization_id = hou2.organization_id AND pbev.effective_start_date = (SELECT MAX (pbev2.effective_start_date) FROM pay_element_entries_f pbev2 WHERE pbev2.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND pbev2.element_entry_id = pbev.element_entry_id) AND pasg.pay_basis_id = ppb.pay_basis_id AND ppb.input_value_id = pbev.input_value_id + 0 AND ppb.input_value_id = ppiv.input_value_id AND ppiv.element_type_id = pbet.element_type_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 pbev.element_entry_id = pbee.element_entry_id AND pbev.effective_start_date = pbee.effective_start_date AND pbev.effective_end_date = pbee.effective_end_date AND pasg.assignment_status_type_id = astb.assignment_status_type_id AND astb.assignment_status_type_id = asta.assignment_status_type_id (+) AND astb.business_group_id = asta.business_group_id (+) AND ppp.assignment_id = pasg.assignment_id AND ppp.change_date = (SELECT MAX(pp2.change_date) FROM per_pay_proposals pp2 WHERE pp2.assignment_id = pasg.assignment_id AND pp2.change_date <= TRUNC(hr_adp.get_adp_extract_date)) AND ppiv.effective_start_date = (SELECT MAX(ppiv2.effective_start_date) FROM pay_input_values_f ppiv2 WHERE ppiv2.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND ppiv2.input_value_id = ppiv.input_value_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.element_link_id = pbel2.element_link_id and pbel2.element_type_id = pbet.element_type_id and pbee2.assignment_id = pbee.assignment_id ))

View Text - HTML Formatted

SELECT REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, DECODE (PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, PASG.PRIMARY_FLAG
, PASG.EFFECTIVE_START_DATE
, PASG.EFFECTIVE_END_DATE
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PJ.NAME
, PJ.JOB_INFORMATION3
, PJ.JOB_INFORMATION1
, TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE)
, DECODE (PPB.PAY_BASIS
, 'PERIOD'
, 'P'
, 'HOURLY'
, 'H'
, 'MONTHLY'
, 'M'
, 'ANNUAL'
, 'A'
, NULL)
, PASG.NORMAL_HOURS
, PPP.LAST_CHANGE_DATE
, PPP.NEXT_SAL_REVIEW_DATE
, PPP.PROPOSAL_REASON
, PBEE.EFFECTIVE_START_DATE
, HS.SEGMENT10
, HS.SEGMENT11
, PPS.FINAL_PROCESS_DATE
, DECODE (DECODE (LEAST (PASG.EFFECTIVE_END_DATE + 1
, TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE))
, PASG.EFFECTIVE_END_DATE + 1
, 'Y'
, 'NO')
, 'Y'
, 'D'
, NVL (ASTA.PAY_SYSTEM_STATUS
, ASTB.PAY_SYSTEM_STATUS)) PAYROLL_STATUS
, HOU2.NAME
, PJ.NAME
, PASG.CHANGE_REASON
, HR_GENERAL.DECODE_LOOKUP('EMP_ASSIGN_REASON'
, PASG.CHANGE_REASON)
, HL.LOCATION_CODE
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, GREATEST (NVL (PP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST (NVL (PASG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE)
, GREATEST (NVL (PPP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PPP.CHANGE_DATE)
, GREATEST (NVL(PPS.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST (NVL (PBEE.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PBEE.EFFECTIVE_START_DATE)
, GREATEST (NVL (PJ.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, NVL (PJ.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST (NVL (ASTB.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, NVL (ASTA.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PPP.CHANGE_DATE
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PBEE.EFFECTIVE_START_DATE
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST( NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PPP.CHANGE_DATE
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PBEE.EFFECTIVE_START_DATE
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, REF.DATE_START) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASSIGNMENT_STATUS_TYPES ASTB
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP
, PER_PAY_PROPOSALS PPP
, PER_PERIODS_OF_SERVICE PPS
, PER_JOBS PJ
, HR_LOCATIONS_ALL HL
, PER_ALL_PEOPLE_F PP
, PER_PAY_BASES PPB
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_ELEMENT_ENTRY_VALUES_F PBEV
, PAY_ELEMENT_ENTRIES_F PBEE
, PAY_INPUT_VALUES_F PPIV
, PAY_ELEMENT_LINKS_F PBEL
, PAY_ELEMENT_TYPES_F PBET
WHERE TRUNC(SYSDATE) BETWEEN PBEL.EFFECTIVE_START_DATE
AND PBEL.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PBET.EFFECTIVE_START_DATE
AND PBET.EFFECTIVE_END_DATE
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PP.ROWID = REF.PER_ROWID
AND NVL(HS.SEGMENT18
, PASG.LOCATION_ID) = HL.LOCATION_ID
AND HL.STYLE IN ('US'
, 'US_GLB')
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.PERSON_ID = PPS.PERSON_ID
AND PASG.JOB_ID = PJ.JOB_ID
AND PPS.PERIOD_OF_SERVICE_ID = PASG.PERIOD_OF_SERVICE_ID
AND REF.PERIOD_TYPE = PTP.PERIOD_TYPE
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.ORGANIZATION_ID = HOU2.ORGANIZATION_ID
AND PBEV.EFFECTIVE_START_DATE = (SELECT MAX (PBEV2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEV2
WHERE PBEV2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PBEV2.ELEMENT_ENTRY_ID = PBEV.ELEMENT_ENTRY_ID)
AND PASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PPB.INPUT_VALUE_ID = PBEV.INPUT_VALUE_ID + 0
AND PPB.INPUT_VALUE_ID = PPIV.INPUT_VALUE_ID
AND PPIV.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_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 PBEV.ELEMENT_ENTRY_ID = PBEE.ELEMENT_ENTRY_ID
AND PBEV.EFFECTIVE_START_DATE = PBEE.EFFECTIVE_START_DATE
AND PBEV.EFFECTIVE_END_DATE = PBEE.EFFECTIVE_END_DATE
AND PASG.ASSIGNMENT_STATUS_TYPE_ID = ASTB.ASSIGNMENT_STATUS_TYPE_ID
AND ASTB.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASTB.BUSINESS_GROUP_ID = ASTA.BUSINESS_GROUP_ID (+)
AND PPP.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (SELECT MAX(PP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PP2
WHERE PP2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PP2.CHANGE_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE))
AND PPIV.EFFECTIVE_START_DATE = (SELECT MAX(PPIV2.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PPIV2
WHERE PPIV2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PPIV2.INPUT_VALUE_ID = PPIV.INPUT_VALUE_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.ELEMENT_LINK_ID = PBEL2.ELEMENT_LINK_ID
AND PBEL2.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEE2.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID ))