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_GTL_V

Source

View Text - Preformatted


SELECT /*+ ordered index(pet PAY_ELEMENT_TYPES_F_UK2) */ REF.company_code_equivalent, hou.name, hs.segment1, pasg.organization_id, pp.employee_number, pp.person_id, pp.business_group_id, pasg.assignment_id, pasg.assignment_number, decode(pasg.primary_flag, 'Y',0,pasg.assignment_sequence), pasg.primary_flag, TO_NUMBER (DECODE (piv1.hot_default_flag, 'N', pv1.screen_entry_value, DECODE (pv1.screen_entry_value, NULL, hr_pay_interface_pkg.get_hot_default (piv1.input_value_id, pb.element_link_id), pv1.screen_entry_value))), TO_NUMBER (DECODE (piv2.hot_default_flag, 'N', pv2.screen_entry_value, DECODE (pv2.screen_entry_value, NULL, hr_pay_interface_pkg.get_hot_default (piv2.input_value_id, pb.element_link_id), pv2.screen_entry_value))), TO_NUMBER (DECODE (piv3.hot_default_flag, 'N', pv3.screen_entry_value, DECODE (pv3.screen_entry_value, NULL, hr_pay_interface_pkg.get_hot_default (piv3.input_value_id, pb.element_link_id), pv3.screen_entry_value))), SUBSTR (DECODE (piv4.hot_default_flag, 'N', pv4.screen_entry_value, DECODE (pv4.screen_entry_value, NULL, hr_pay_interface_pkg.get_hot_default (piv4.input_value_id, pb.element_link_id), pv4.screen_entry_value)), 1, 1), DECODE (ptp.number_per_fiscal_year, 52, 'W', 24, 'S', 26, 'B', 12, 'M', ptp.number_per_fiscal_year), MIN(pb.effective_start_date), MAX(pb.effective_end_date), 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 (pb.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pb.effective_start_date), GREATEST (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 (pb.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pb.effective_start_date)) FROM /* please do NOT change this table from ordering */ pay_element_types_f pet, /* added for bug 3870695 */ pay_input_values_f piv1, pay_input_values_f piv2, pay_input_values_f piv3, pay_input_values_f piv4, pay_element_links_f el, pay_element_entries_f pb, hr_adp_emp_ref_v ref, per_time_period_types ptp, per_all_assignments_f pasg, per_all_people_f pp, hr_soft_coding_keyflex hs, hr_all_organization_units hou, pay_element_entry_values_f pv1, pay_element_entry_values_f pv2, pay_element_entry_values_f pv3, pay_element_entry_values_f pv4 WHERE pet.element_name = 'ADP GTL' AND el.element_type_id = pet.element_type_id /* join person and assignment */ AND pasg.person_id = pp.person_id AND /* join assignment to element entry */ pasg.assignment_id = pb.assignment_id AND ref.assignment_id = pb.assignment_id AND /* join the assignment to REF view by rowid */ pasg.rowid = REF.asg_rowid AND /* join the person to REF view by rowid */ pp.rowid = REF.per_rowid AND /* join the soft coding keyflex to assignment */ hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id AND /* join soft coding keyflex to org */ hs.segment1 = hou.organization_id AND /* join pay_element_links_f to element entry */ el.element_link_id = pb.element_link_id /* join the input/entry values note: the pay_element_entry_values_f tables - no need for subqueries because the pay_element_entries_f and pay_element_entry_values_f are logically modelled as on DT entity */ AND (pv1.input_value_id = piv1.input_value_id AND piv1.display_sequence = 1 AND pb.element_entry_id = pv1.element_entry_id AND pv1.effective_start_date = pb.effective_start_date AND pv1.effective_end_date = pb.effective_end_date AND pet.element_type_id = piv1.element_type_id AND piv1.name <> 'Pay Value') AND (pv2.input_value_id = piv2.input_value_id AND piv2.display_sequence = 2 AND pb.element_entry_id = pv2.element_entry_id AND pv2.effective_start_date = pb.effective_start_date AND pv2.effective_end_date = pb.effective_end_date AND pet.element_type_id = piv2.element_type_id) AND (pv3.input_value_id = piv3.input_value_id AND piv3.display_sequence = 3 AND pb.element_entry_id = pv3.element_entry_id AND pv3.effective_start_date = pb.effective_start_date AND pv3.effective_end_date = pb.effective_end_date AND pet.element_type_id = piv3.element_type_id) AND (pv4.input_value_id = piv4.input_value_id AND piv4.display_sequence = 4 AND pb.element_entry_id = pv4.element_entry_id AND pv4.effective_start_date = pb.effective_start_date AND pv4.effective_end_date = pb.effective_end_date AND pet.element_type_id = piv4.element_type_id) /* join payroll/time period types */ AND ref.period_type = ptp.period_type /* =========== date restriction of DT tables section =========== */ /* element types dt restriction */ AND pet.effective_start_date = (SELECT MAX (pt2.effective_start_date) FROM pay_element_types_f pt2 WHERE pt2.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND pt2.element_type_id = pet.element_type_id) /* element link dt restriction */ AND el.effective_start_date = (SELECT MAX(el2.effective_start_date) FROM pay_element_links_f el2 WHERE el2.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND el2.element_link_id = el.element_link_id) AND pb.effective_start_date = (SELECT MAX (pb2.effective_start_date) FROM pay_element_entries_f pb2 WHERE pb2.effective_start_date <= TRUNC (hr_adp.get_adp_extract_date) AND pb2.element_link_id = el.element_link_id AND pb2.assignment_id = pb.assignment_id AND ((pb2.effective_end_date > trunc(hr_adp.get_adp_extract_date)) or (pb2.effective_end_date >= hr_adp.get_end_deduction_date))) AND piv1.effective_start_date = (SELECT MAX (piv12.effective_start_date) FROM pay_input_values_f piv12 WHERE piv12.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND piv12.input_value_id = piv1.input_value_id) AND piv2.effective_start_date = (SELECT MAX (piv22.effective_start_date) FROM pay_input_values_f piv22 WHERE piv22.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND piv22.input_value_id = piv2.input_value_id) AND piv3.effective_start_date = (SELECT MAX (piv32.effective_start_date) FROM pay_input_values_f piv32 WHERE piv32.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND piv32.input_value_id = piv3.input_value_id) AND piv4.effective_start_date = (SELECT MAX (piv42.effective_start_date) FROM pay_input_values_f piv42 WHERE piv42.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND piv42.input_value_id = piv4.input_value_id) GROUP BY ref.company_code_equivalent, hou.name, hs.segment1, pasg.organization_id, pp.employee_number, pp.person_id, pp.business_group_id, pasg.assignment_id, pasg.assignment_number, pasg.assignment_sequence, pasg.primary_flag, piv1.hot_default_flag, pv1.screen_entry_value, piv1.input_value_id, pb.element_link_id, piv2.hot_default_flag, pv2.screen_entry_value, piv2.input_value_id, piv3.hot_default_flag, pv3.screen_entry_value, piv3.input_value_id, piv4.hot_default_flag, pv4.screen_entry_value, piv4.input_value_id, ptp.number_per_fiscal_year, pb.effective_start_date, pb.effective_end_date, pp.last_update_date, pp.effective_start_date, pasg.last_update_date, pasg.effective_start_date, pb.last_update_date, pp.last_update_date, pasg.last_update_date, pasg.effective_start_date

View Text - HTML Formatted

SELECT /*+ ORDERED INDEX(PET PAY_ELEMENT_TYPES_F_UK2) */ REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PASG.PRIMARY_FLAG
, TO_NUMBER (DECODE (PIV1.HOT_DEFAULT_FLAG
, 'N'
, PV1.SCREEN_ENTRY_VALUE
, DECODE (PV1.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV1.INPUT_VALUE_ID
, PB.ELEMENT_LINK_ID)
, PV1.SCREEN_ENTRY_VALUE)))
, TO_NUMBER (DECODE (PIV2.HOT_DEFAULT_FLAG
, 'N'
, PV2.SCREEN_ENTRY_VALUE
, DECODE (PV2.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV2.INPUT_VALUE_ID
, PB.ELEMENT_LINK_ID)
, PV2.SCREEN_ENTRY_VALUE)))
, TO_NUMBER (DECODE (PIV3.HOT_DEFAULT_FLAG
, 'N'
, PV3.SCREEN_ENTRY_VALUE
, DECODE (PV3.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV3.INPUT_VALUE_ID
, PB.ELEMENT_LINK_ID)
, PV3.SCREEN_ENTRY_VALUE)))
, SUBSTR (DECODE (PIV4.HOT_DEFAULT_FLAG
, 'N'
, PV4.SCREEN_ENTRY_VALUE
, DECODE (PV4.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV4.INPUT_VALUE_ID
, PB.ELEMENT_LINK_ID)
, PV4.SCREEN_ENTRY_VALUE))
, 1
, 1)
, DECODE (PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, MIN(PB.EFFECTIVE_START_DATE)
, MAX(PB.EFFECTIVE_END_DATE)
, 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 (PB.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PB.EFFECTIVE_START_DATE)
, GREATEST (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 (PB.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PB.EFFECTIVE_START_DATE))
FROM /* PLEASE DO NOT CHANGE THIS TABLE
FROM ORDERING */ PAY_ELEMENT_TYPES_F PET
, /* ADDED FOR BUG 3870695 */ PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PAY_INPUT_VALUES_F PIV4
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_ENTRIES_F PB
, HR_ADP_EMP_REF_V REF
, PER_TIME_PERIOD_TYPES PTP
, PER_ALL_ASSIGNMENTS_F PASG
, PER_ALL_PEOPLE_F PP
, HR_SOFT_CODING_KEYFLEX HS
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
, PAY_ELEMENT_ENTRY_VALUES_F PV4
WHERE PET.ELEMENT_NAME = 'ADP GTL'
AND EL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID /* JOIN PERSON
AND ASSIGNMENT */
AND PASG.PERSON_ID = PP.PERSON_ID
AND /* JOIN ASSIGNMENT TO ELEMENT ENTRY */ PASG.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND REF.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND /* JOIN THE ASSIGNMENT TO REF VIEW BY ROWID */ PASG.ROWID = REF.ASG_ROWID
AND /* JOIN THE PERSON TO REF VIEW BY ROWID */ PP.ROWID = REF.PER_ROWID
AND /* JOIN THE SOFT CODING KEYFLEX TO ASSIGNMENT */ HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND /* JOIN SOFT CODING KEYFLEX TO ORG */ HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND /* JOIN PAY_ELEMENT_LINKS_F TO ELEMENT ENTRY */ EL.ELEMENT_LINK_ID = PB.ELEMENT_LINK_ID /* JOIN THE INPUT/ENTRY VALUES NOTE: THE PAY_ELEMENT_ENTRY_VALUES_F TABLES - NO NEED FOR SUBQUERIES BECAUSE THE PAY_ELEMENT_ENTRIES_F
AND PAY_ELEMENT_ENTRY_VALUES_F ARE LOGICALLY MODELLED AS ON DT ENTITY */
AND (PV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV1.ELEMENT_ENTRY_ID
AND PV1.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV1.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PET.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PIV1.NAME <> 'PAY VALUE')
AND (PV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND PIV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PV2.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV2.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PET.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID)
AND (PV3.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID
AND PIV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PV3.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV3.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PET.ELEMENT_TYPE_ID = PIV3.ELEMENT_TYPE_ID)
AND (PV4.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID
AND PIV4.DISPLAY_SEQUENCE = 4
AND PB.ELEMENT_ENTRY_ID = PV4.ELEMENT_ENTRY_ID
AND PV4.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV4.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PET.ELEMENT_TYPE_ID = PIV4.ELEMENT_TYPE_ID) /* JOIN PAYROLL/TIME PERIOD TYPES */
AND REF.PERIOD_TYPE = PTP.PERIOD_TYPE /* =========== DATE RESTRICTION OF DT TABLES SECTION =========== */ /* ELEMENT TYPES DT RESTRICTION */
AND PET.EFFECTIVE_START_DATE = (SELECT MAX (PT2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PT2
WHERE PT2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PT2.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID) /* ELEMENT LINK DT RESTRICTION */
AND EL.EFFECTIVE_START_DATE = (SELECT MAX(EL2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_F EL2
WHERE EL2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID)
AND PB.EFFECTIVE_START_DATE = (SELECT MAX (PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
WHERE PB2.EFFECTIVE_START_DATE <= TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE)
AND PB2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND PB2.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND ((PB2.EFFECTIVE_END_DATE > TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)) OR (PB2.EFFECTIVE_END_DATE >= HR_ADP.GET_END_DEDUCTION_DATE)))
AND PIV1.EFFECTIVE_START_DATE = (SELECT MAX (PIV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV12
WHERE PIV12.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PIV12.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID)
AND PIV2.EFFECTIVE_START_DATE = (SELECT MAX (PIV22.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV22
WHERE PIV22.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PIV22.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID)
AND PIV3.EFFECTIVE_START_DATE = (SELECT MAX (PIV32.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV32
WHERE PIV32.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PIV32.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID)
AND PIV4.EFFECTIVE_START_DATE = (SELECT MAX (PIV42.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV42
WHERE PIV42.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PIV42.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID) GROUP BY REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, PASG.ASSIGNMENT_SEQUENCE
, PASG.PRIMARY_FLAG
, PIV1.HOT_DEFAULT_FLAG
, PV1.SCREEN_ENTRY_VALUE
, PIV1.INPUT_VALUE_ID
, PB.ELEMENT_LINK_ID
, PIV2.HOT_DEFAULT_FLAG
, PV2.SCREEN_ENTRY_VALUE
, PIV2.INPUT_VALUE_ID
, PIV3.HOT_DEFAULT_FLAG
, PV3.SCREEN_ENTRY_VALUE
, PIV3.INPUT_VALUE_ID
, PIV4.HOT_DEFAULT_FLAG
, PV4.SCREEN_ENTRY_VALUE
, PIV4.INPUT_VALUE_ID
, PTP.NUMBER_PER_FISCAL_YEAR
, PB.EFFECTIVE_START_DATE
, PB.EFFECTIVE_END_DATE
, PP.LAST_UPDATE_DATE
, PP.EFFECTIVE_START_DATE
, PASG.LAST_UPDATE_DATE
, PASG.EFFECTIVE_START_DATE
, PB.LAST_UPDATE_DATE
, PP.LAST_UPDATE_DATE
, PASG.LAST_UPDATE_DATE
, PASG.EFFECTIVE_START_DATE