View: HR_ADP_GTL_V

Product:	PER - Human Resources
Description:	ADP payroll interface view
Implementation/DBA Data:	APPS.HR_ADP_GTL_V

View Text

SELECT 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 PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PAY_INPUT_VALUES_F PIV4
, PAY_ELEMENT_ENTRIES_F PB
, PAY_ELEMENT_LINKS_F EL
, PER_ALL_ASSIGNMENTS_F PASG
, HR_SOFT_CODING_KEYFLEX HS
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_PAYROLLS_F PPR
, PER_TIME_PERIOD_TYPES PTP
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
, PAY_ELEMENT_ENTRY_VALUES_F PV4
, PER_ALL_PEOPLE_F PP
, HR_ADP_EMP_REF_V REF
WHERE /* JOIN PERSON
AND ASSIGNMENT */ PASG.PERSON_ID = PP.PERSON_ID
AND /* JOIN ASSIGNMENT TO ELEMENT ENTRY */ PASG.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 + 0 = 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 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 (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 (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) /* JOIN PAYROLL/TIME PERIOD TYPES */
AND PTP.PERIOD_TYPE = PPR.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID /* RESTRICT THE PAYROLL BY SYSDATE */
AND TRUNC(SYSDATE) BETWEEN PPR.EFFECTIVE_START_DATE
AND PPR.EFFECTIVE_END_DATE /* RESTRICT BY THE ELEMENT TYPE
AND LINK */
AND (PB.ELEMENT_LINK_ID
, PIV1.ELEMENT_TYPE_ID
, PIV2.ELEMENT_TYPE_ID
, PIV3.ELEMENT_TYPE_ID
, PIV4.ELEMENT_TYPE_ID) IN (SELECT EL.ELEMENT_LINK_ID
, EL.ELEMENT_TYPE_ID
, EL.ELEMENT_TYPE_ID
, EL.ELEMENT_TYPE_ID
, EL.ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F PT
, PAY_ELEMENT_LINKS_F EL
WHERE EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PT.ELEMENT_NAME = 'ADP GTL'
AND PT.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 = PT.ELEMENT_TYPE_ID)
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)) /* =========== DATE RESTRICTION OF DT TABLES SECTION =========== */
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 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

Columns