VIEW: APPS.PAY_ASSIGNMENT_ACTIONS_V

Source

View Text - Preformatted

SELECT AAC.ROWID ROW_ID , AAC.ASSIGNMENT_ID , AAC.PAYROLL_ACTION_ID , PAC.EFFECTIVE_DATE , PAC.ACTION_TYPE , decode( PAC.ACTION_TYPE, 'T', decode(instrb(PAC.LEGISLATIVE_PARAMETERS,'SLA_MODE=Y'), 0, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), null, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'SLA') ), LO1.MEANING ) TYPE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME , AAC.PRE_PAYMENT_ID , PEO.FULL_NAME , PEO.EFFECTIVE_START_DATE PEO_START_DATE , PEO.EFFECTIVE_END_DATE PEO_END_DATE , ASG.ASSIGNMENT_NUMBER , ASG.EFFECTIVE_START_DATE ASG_START_DATE , ASG.EFFECTIVE_END_DATE ASG_END_DATE , AAC.ASSIGNMENT_ACTION_ID , AAC.ACTION_STATUS STATUS_CODE , PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID, PAC.ACTION_TYPE, AAC.ACTION_STATUS) ACTION_STATUS , AAC.ACTION_SEQUENCE , PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID ,PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST , PAC.DATE_EARNED , AAC.SECONDARY_STATUS , PAC.PURGE_PHASE FROM PER_ASSIGNMENTS_F2 ASG, PER_PEOPLE_F PEO, HR_LOOKUPS LO1, PAY_PAYROLL_ACTIONS PAC, PAY_ASSIGNMENT_ACTIONS AAC WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG.PERSON_ID = PEO.PERSON_ID AND LO1.LOOKUP_TYPE = 'ACTION_TYPE' AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID /* EXCLUDE CERTAIN ACTION TYPES */ AND PAC.ACTION_TYPE NOT IN ('Z', 'X','BEE') AND PAC.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE AND PEO.EFFECTIVE_END_DATE AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE UNION ALL SELECT AAC.ROWID ROW_ID , AAC.ASSIGNMENT_ID , AAC.PAYROLL_ACTION_ID , PAC.EFFECTIVE_DATE , PAC.ACTION_TYPE , decode( PAC.ACTION_TYPE, 'T', decode(instrb(PAC.LEGISLATIVE_PARAMETERS,'SLA_MODE=Y'), 0, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), null, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'SLA') ), LO1.MEANING ) TYPE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME , AAC.PRE_PAYMENT_ID , PEO.FULL_NAME , PEO.EFFECTIVE_START_DATE PEO_START_DATE , PEO.EFFECTIVE_END_DATE PEO_END_DATE , ASG.ASSIGNMENT_NUMBER , ASG.EFFECTIVE_START_DATE ASG_START_DATE , ASG.EFFECTIVE_END_DATE ASG_END_DATE , AAC.ASSIGNMENT_ACTION_ID , AAC.ACTION_STATUS STATUS_CODE , PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID, PAC.ACTION_TYPE, AAC.ACTION_STATUS) ACTION_STATUS , AAC.ACTION_SEQUENCE , PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID ,PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST , PAC.DATE_EARNED , AAC.SECONDARY_STATUS , PAC.PURGE_PHASE FROM PER_ASSIGNMENTS_F2 ASG, PER_PEOPLE_F PEO, HR_LOOKUPS LO1, PAY_PAYROLL_ACTIONS PAC, PAY_ASSIGNMENT_ACTIONS AAC WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG.PERSON_ID = PEO.PERSON_ID AND LO1.LOOKUP_TYPE = 'ACTION_TYPE' AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID AND PAC.ACTION_TYPE = 'X' /* ONLY SHOW THE ASSIGNMENTS FOR THE NEW ARCHIVE PROCESS */ AND PAC.REPORT_CATEGORY IS NOT NULL AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(ASG2.EFFECTIVE_START_DATE) FROM PER_ALL_ASSIGNMENTS_F ASG2 WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG2.EFFECTIVE_START_DATE <= PAC.EFFECTIVE_DATE) AND PEO.EFFECTIVE_START_DATE = (SELECT MAX(PEO2.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F PEO2 WHERE PEO2.PERSON_ID = PEO.PERSON_ID AND PEO2.EFFECTIVE_START_DATE <= PAC.EFFECTIVE_DATE) UNION ALL SELECT AAC.ROWID ROW_ID , AAC.ASSIGNMENT_ID , AAC.PAYROLL_ACTION_ID , PAC.EFFECTIVE_DATE , PAC.ACTION_TYPE , HR_GENERAL.DECODE_LOOKUP('PURGE_PHASE', TO_CHAR(PAC.PURGE_PHASE)) TYPE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME , AAC.PRE_PAYMENT_ID , PEO.FULL_NAME , PEO.EFFECTIVE_START_DATE PEO_START_DATE , PEO.EFFECTIVE_END_DATE PEO_END_DATE , ASG.ASSIGNMENT_NUMBER , ASG.EFFECTIVE_START_DATE ASG_START_DATE , ASG.EFFECTIVE_END_DATE ASG_END_DATE , AAC.ASSIGNMENT_ACTION_ID , AAC.ACTION_STATUS STATUS_CODE , DECODE( PAC.PURGE_PHASE, 2, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID, PAC.ACTION_TYPE, AAC.SECONDARY_STATUS), PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID, PAC.ACTION_TYPE, AAC.ACTION_STATUS)) ACTION_STATUS , AAC.ACTION_SEQUENCE , PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID ,PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST , PAC.DATE_EARNED , AAC.SECONDARY_STATUS , PAC.PURGE_PHASE FROM PER_ASSIGNMENTS_F2 ASG, PER_PEOPLE_F PEO, HR_LOOKUPS LO1, PAY_PAYROLL_ACTIONS PAC, PAY_ASSIGNMENT_ACTIONS AAC WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG.PERSON_ID = PEO.PERSON_ID AND LO1.LOOKUP_TYPE = 'ACTION_TYPE' AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID AND PAC.ACTION_TYPE = 'Z' AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(ASG2.EFFECTIVE_START_DATE) FROM PER_ALL_ASSIGNMENTS_F ASG2 WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG2.EFFECTIVE_START_DATE <= PAC.EFFECTIVE_DATE) AND PEO.EFFECTIVE_START_DATE = (SELECT MAX(PEO2.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F PEO2 WHERE PEO2.PERSON_ID = PEO.PERSON_ID AND PEO2.EFFECTIVE_START_DATE <= PAC.EFFECTIVE_DATE) UNION ALL SELECT AAC.ROWID ROW_ID , AAC.ASSIGNMENT_ID , AAC.PAYROLL_ACTION_ID , PAC.EFFECTIVE_DATE , PAC.ACTION_TYPE , decode( PAC.ACTION_TYPE, 'T', decode(instrb(PAC.LEGISLATIVE_PARAMETERS,'SLA_MODE=Y'), 0, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), null, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'SLA') ), LO1.MEANING ) TYPE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME , AAC.PRE_PAYMENT_ID , PEO.FULL_NAME , PEO.EFFECTIVE_START_DATE PEO_START_DATE , PEO.EFFECTIVE_END_DATE PEO_END_DATE , ASG.ASSIGNMENT_NUMBER , ASG.EFFECTIVE_START_DATE ASG_START_DATE , ASG.EFFECTIVE_END_DATE ASG_END_DATE , AAC.ASSIGNMENT_ACTION_ID , AAC.ACTION_STATUS STATUS_CODE , PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID, PAC.ACTION_TYPE, AAC.ACTION_STATUS) ACTION_STATUS , AAC.ACTION_SEQUENCE , PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID ,PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST , PAC.DATE_EARNED , AAC.SECONDARY_STATUS , PAC.PURGE_PHASE FROM PER_ASSIGNMENTS_F2 ASG, PER_PEOPLE_F PEO, HR_LOOKUPS LO1, PAY_PAYROLL_ACTIONS PAC, PAY_ASSIGNMENT_ACTIONS AAC WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG.PERSON_ID = PEO.PERSON_ID AND LO1.LOOKUP_TYPE = 'ACTION_TYPE' AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID AND PAC.ACTION_TYPE = 'BEE' AND ASG.EFFECTIVE_START_DATE = (SELECT /*+ INDEX (pbl PAY_BATCH_LINES_N2) */ MAX(ASG2.EFFECTIVE_START_DATE) FROM PER_ALL_ASSIGNMENTS_F ASG2, PAY_BATCH_LINES PBL WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND PBL.ASSIGNMENT_ID (+) = ASG.ASSIGNMENT_ID AND PBL.BATCH_ID (+) = PAC.BATCH_ID AND PBL.EFFECTIVE_DATE (+) BETWEEN ASG2.EFFECTIVE_START_DATE AND ASG2.EFFECTIVE_END_DATE) AND PEO.EFFECTIVE_START_DATE = (SELECT /*+ INDEX (pbl PAY_BATCH_LINES_N2) */ MAX(PEO2.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F PEO2, PAY_BATCH_LINES PBL WHERE PEO2.PERSON_ID = PEO.PERSON_ID AND PEO2.PERSON_ID = ASG.PERSON_ID AND PBL.ASSIGNMENT_ID (+) = ASG.ASSIGNMENT_ID AND PBL.BATCH_ID (+) = PAC.BATCH_ID AND PBL.EFFECTIVE_DATE (+) BETWEEN PEO2.EFFECTIVE_START_DATE AND PEO2.EFFECTIVE_END_DATE) UNION ALL SELECT AAC.ROWID ROW_ID , AAC2.ASSIGNMENT_ID , PAC.PAYROLL_ACTION_ID , PAC.EFFECTIVE_DATE , PAC.ACTION_TYPE , decode( PAC.ACTION_TYPE, 'T', decode(instrb(PAC.LEGISLATIVE_PARAMETERS,'SLA_MODE=Y'), 0, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), null, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'SLA') ), LO1.MEANING ) TYPE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME , AAC2.PRE_PAYMENT_ID , PEO.FULL_NAME , PEO.EFFECTIVE_START_DATE PEO_START_DATE , PEO.EFFECTIVE_END_DATE PEO_END_DATE , ASG.ASSIGNMENT_NUMBER , ASG.EFFECTIVE_START_DATE ASG_START_DATE , ASG.EFFECTIVE_END_DATE ASG_END_DATE , AAC2.ASSIGNMENT_ACTION_ID , AAC2.ACTION_STATUS STATUS_CODE , PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC2.ASSIGNMENT_ACTION_ID , PAC.ACTION_TYPE , AAC2.ACTION_STATUS) ACTION_STATUS , AAC2.ACTION_SEQUENCE , PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID ,PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC2.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST , PAC.DATE_EARNED , AAC2.SECONDARY_STATUS , PAC.PURGE_PHASE FROM PER_ASSIGNMENTS_F2 ASG , PER_PEOPLE_F PEO , HR_LOOKUPS LO1 , PAY_PAYROLL_ACTIONS PAC , PAY_ASSIGNMENT_ACTIONS AAC , PAY_ASSIGNMENT_ACTIONS AAC2 WHERE AAC2.ASSIGNMENT_ID IS NULL AND AAC2.SOURCE_ACTION_ID IS NULL AND AAC.SOURCE_ACTION_ID = AAC2.ASSIGNMENT_ACTION_ID AND ASG.PRIMARY_FLAG = 'Y' AND AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND ASG.PERSON_ID = PEO.PERSON_ID AND LO1.LOOKUP_TYPE = 'ACTION_TYPE' AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE AND AAC2.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID AND PAC.ACTION_TYPE = 'L' AND PAC.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE AND PEO.EFFECTIVE_END_DATE AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE UNION ALL SELECT AAC.ROWID ROW_ID , AAC2.ASSIGNMENT_ID , PAC.PAYROLL_ACTION_ID , PAC.EFFECTIVE_DATE , PAC.ACTION_TYPE , decode( PAC.ACTION_TYPE, 'T', decode(instrb(PAC.LEGISLATIVE_PARAMETERS,'SLA_MODE=Y'), 0, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), null, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'SLA') ), LO1.MEANING ) TYPE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME , AAC.PRE_PAYMENT_ID , PEO.FULL_NAME , PEO.EFFECTIVE_START_DATE PEO_START_DATE , PEO.EFFECTIVE_END_DATE PEO_END_DATE , ASG.ASSIGNMENT_NUMBER , ASG.EFFECTIVE_START_DATE ASG_START_DATE , ASG.EFFECTIVE_END_DATE ASG_END_DATE , AAC.ASSIGNMENT_ACTION_ID , AAC.ACTION_STATUS STATUS_CODE , PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID , PAC.ACTION_TYPE , AAC.ACTION_STATUS) ACTION_STATUS , AAC.ACTION_SEQUENCE , PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID ,PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST , PAC.DATE_EARNED , AAC.SECONDARY_STATUS , PAC.PURGE_PHASE FROM PER_ASSIGNMENTS_F2 ASG , PER_PEOPLE_F PEO , HR_LOOKUPS LO1 , PAY_PAYROLL_ACTIONS PAC , PAY_ASSIGNMENT_ACTIONS AAC , PAY_ASSIGNMENT_ACTIONS AAC2 WHERE AAC2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND AAC2.SOURCE_ACTION_ID = AAC.ASSIGNMENT_ACTION_ID AND AAC.SOURCE_ACTION_ID IS NULL AND AAC.ASSIGNMENT_ID IS NULL AND ASG.PERSON_ID = PEO.PERSON_ID AND LO1.LOOKUP_TYPE = 'ACTION_TYPE' AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID AND PAC.ACTION_TYPE = 'L' AND PAC.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE AND PEO.EFFECTIVE_END_DATE AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE

View Text - HTML Formatted

SELECT AAC.ROWID ROW_ID
, AAC.ASSIGNMENT_ID
, AAC.PAYROLL_ACTION_ID
, PAC.EFFECTIVE_DATE
, PAC.ACTION_TYPE
, DECODE( PAC.ACTION_TYPE
, 'T'
, DECODE(INSTRB(PAC.LEGISLATIVE_PARAMETERS
, 'SLA_MODE=Y')
, 0
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, NULL
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'SLA') )
, LO1.MEANING ) TYPE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, AAC.PRE_PAYMENT_ID
, PEO.FULL_NAME
, PEO.EFFECTIVE_START_DATE PEO_START_DATE
, PEO.EFFECTIVE_END_DATE PEO_END_DATE
, ASG.ASSIGNMENT_NUMBER
, ASG.EFFECTIVE_START_DATE ASG_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_END_DATE
, AAC.ASSIGNMENT_ACTION_ID
, AAC.ACTION_STATUS STATUS_CODE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID
, PAC.ACTION_TYPE
, AAC.ACTION_STATUS) ACTION_STATUS
, AAC.ACTION_SEQUENCE
, PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST
, PAC.DATE_EARNED
, AAC.SECONDARY_STATUS
, PAC.PURGE_PHASE
FROM PER_ASSIGNMENTS_F2 ASG
, PER_PEOPLE_F PEO
, HR_LOOKUPS LO1
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND LO1.LOOKUP_TYPE = 'ACTION_TYPE'
AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID /* EXCLUDE CERTAIN ACTION TYPES */
AND PAC.ACTION_TYPE NOT IN ('Z'
, 'X'
, 'BEE')
AND PAC.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE UNION ALL SELECT AAC.ROWID ROW_ID
, AAC.ASSIGNMENT_ID
, AAC.PAYROLL_ACTION_ID
, PAC.EFFECTIVE_DATE
, PAC.ACTION_TYPE
, DECODE( PAC.ACTION_TYPE
, 'T'
, DECODE(INSTRB(PAC.LEGISLATIVE_PARAMETERS
, 'SLA_MODE=Y')
, 0
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, NULL
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'SLA') )
, LO1.MEANING ) TYPE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, AAC.PRE_PAYMENT_ID
, PEO.FULL_NAME
, PEO.EFFECTIVE_START_DATE PEO_START_DATE
, PEO.EFFECTIVE_END_DATE PEO_END_DATE
, ASG.ASSIGNMENT_NUMBER
, ASG.EFFECTIVE_START_DATE ASG_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_END_DATE
, AAC.ASSIGNMENT_ACTION_ID
, AAC.ACTION_STATUS STATUS_CODE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID
, PAC.ACTION_TYPE
, AAC.ACTION_STATUS) ACTION_STATUS
, AAC.ACTION_SEQUENCE
, PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST
, PAC.DATE_EARNED
, AAC.SECONDARY_STATUS
, PAC.PURGE_PHASE
FROM PER_ASSIGNMENTS_F2 ASG
, PER_PEOPLE_F PEO
, HR_LOOKUPS LO1
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND LO1.LOOKUP_TYPE = 'ACTION_TYPE'
AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ACTION_TYPE = 'X' /* ONLY SHOW THE ASSIGNMENTS FOR THE NEW ARCHIVE PROCESS */
AND PAC.REPORT_CATEGORY IS NOT NULL
AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(ASG2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG2
WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG2.EFFECTIVE_START_DATE <= PAC.EFFECTIVE_DATE)
AND PEO.EFFECTIVE_START_DATE = (SELECT MAX(PEO2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PEO2
WHERE PEO2.PERSON_ID = PEO.PERSON_ID
AND PEO2.EFFECTIVE_START_DATE <= PAC.EFFECTIVE_DATE) UNION ALL SELECT AAC.ROWID ROW_ID
, AAC.ASSIGNMENT_ID
, AAC.PAYROLL_ACTION_ID
, PAC.EFFECTIVE_DATE
, PAC.ACTION_TYPE
, HR_GENERAL.DECODE_LOOKUP('PURGE_PHASE'
, TO_CHAR(PAC.PURGE_PHASE)) TYPE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, AAC.PRE_PAYMENT_ID
, PEO.FULL_NAME
, PEO.EFFECTIVE_START_DATE PEO_START_DATE
, PEO.EFFECTIVE_END_DATE PEO_END_DATE
, ASG.ASSIGNMENT_NUMBER
, ASG.EFFECTIVE_START_DATE ASG_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_END_DATE
, AAC.ASSIGNMENT_ACTION_ID
, AAC.ACTION_STATUS STATUS_CODE
, DECODE( PAC.PURGE_PHASE
, 2
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID
, PAC.ACTION_TYPE
, AAC.SECONDARY_STATUS)
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID
, PAC.ACTION_TYPE
, AAC.ACTION_STATUS)) ACTION_STATUS
, AAC.ACTION_SEQUENCE
, PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST
, PAC.DATE_EARNED
, AAC.SECONDARY_STATUS
, PAC.PURGE_PHASE
FROM PER_ASSIGNMENTS_F2 ASG
, PER_PEOPLE_F PEO
, HR_LOOKUPS LO1
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND LO1.LOOKUP_TYPE = 'ACTION_TYPE'
AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ACTION_TYPE = 'Z'
AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(ASG2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG2
WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG2.EFFECTIVE_START_DATE <= PAC.EFFECTIVE_DATE)
AND PEO.EFFECTIVE_START_DATE = (SELECT MAX(PEO2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PEO2
WHERE PEO2.PERSON_ID = PEO.PERSON_ID
AND PEO2.EFFECTIVE_START_DATE <= PAC.EFFECTIVE_DATE) UNION ALL SELECT AAC.ROWID ROW_ID
, AAC.ASSIGNMENT_ID
, AAC.PAYROLL_ACTION_ID
, PAC.EFFECTIVE_DATE
, PAC.ACTION_TYPE
, DECODE( PAC.ACTION_TYPE
, 'T'
, DECODE(INSTRB(PAC.LEGISLATIVE_PARAMETERS
, 'SLA_MODE=Y')
, 0
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, NULL
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'SLA') )
, LO1.MEANING ) TYPE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, AAC.PRE_PAYMENT_ID
, PEO.FULL_NAME
, PEO.EFFECTIVE_START_DATE PEO_START_DATE
, PEO.EFFECTIVE_END_DATE PEO_END_DATE
, ASG.ASSIGNMENT_NUMBER
, ASG.EFFECTIVE_START_DATE ASG_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_END_DATE
, AAC.ASSIGNMENT_ACTION_ID
, AAC.ACTION_STATUS STATUS_CODE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID
, PAC.ACTION_TYPE
, AAC.ACTION_STATUS) ACTION_STATUS
, AAC.ACTION_SEQUENCE
, PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST
, PAC.DATE_EARNED
, AAC.SECONDARY_STATUS
, PAC.PURGE_PHASE
FROM PER_ASSIGNMENTS_F2 ASG
, PER_PEOPLE_F PEO
, HR_LOOKUPS LO1
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND LO1.LOOKUP_TYPE = 'ACTION_TYPE'
AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ACTION_TYPE = 'BEE'
AND ASG.EFFECTIVE_START_DATE = (SELECT /*+ INDEX (PBL PAY_BATCH_LINES_N2) */ MAX(ASG2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG2
, PAY_BATCH_LINES PBL
WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PBL.ASSIGNMENT_ID (+) = ASG.ASSIGNMENT_ID
AND PBL.BATCH_ID (+) = PAC.BATCH_ID
AND PBL.EFFECTIVE_DATE (+) BETWEEN ASG2.EFFECTIVE_START_DATE
AND ASG2.EFFECTIVE_END_DATE)
AND PEO.EFFECTIVE_START_DATE = (SELECT /*+ INDEX (PBL PAY_BATCH_LINES_N2) */ MAX(PEO2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PEO2
, PAY_BATCH_LINES PBL
WHERE PEO2.PERSON_ID = PEO.PERSON_ID
AND PEO2.PERSON_ID = ASG.PERSON_ID
AND PBL.ASSIGNMENT_ID (+) = ASG.ASSIGNMENT_ID
AND PBL.BATCH_ID (+) = PAC.BATCH_ID
AND PBL.EFFECTIVE_DATE (+) BETWEEN PEO2.EFFECTIVE_START_DATE
AND PEO2.EFFECTIVE_END_DATE) UNION ALL SELECT AAC.ROWID ROW_ID
, AAC2.ASSIGNMENT_ID
, PAC.PAYROLL_ACTION_ID
, PAC.EFFECTIVE_DATE
, PAC.ACTION_TYPE
, DECODE( PAC.ACTION_TYPE
, 'T'
, DECODE(INSTRB(PAC.LEGISLATIVE_PARAMETERS
, 'SLA_MODE=Y')
, 0
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, NULL
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'SLA') )
, LO1.MEANING ) TYPE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, AAC2.PRE_PAYMENT_ID
, PEO.FULL_NAME
, PEO.EFFECTIVE_START_DATE PEO_START_DATE
, PEO.EFFECTIVE_END_DATE PEO_END_DATE
, ASG.ASSIGNMENT_NUMBER
, ASG.EFFECTIVE_START_DATE ASG_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_END_DATE
, AAC2.ASSIGNMENT_ACTION_ID
, AAC2.ACTION_STATUS STATUS_CODE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC2.ASSIGNMENT_ACTION_ID
, PAC.ACTION_TYPE
, AAC2.ACTION_STATUS) ACTION_STATUS
, AAC2.ACTION_SEQUENCE
, PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC2.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST
, PAC.DATE_EARNED
, AAC2.SECONDARY_STATUS
, PAC.PURGE_PHASE
FROM PER_ASSIGNMENTS_F2 ASG
, PER_PEOPLE_F PEO
, HR_LOOKUPS LO1
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
, PAY_ASSIGNMENT_ACTIONS AAC2
WHERE AAC2.ASSIGNMENT_ID IS NULL
AND AAC2.SOURCE_ACTION_ID IS NULL
AND AAC.SOURCE_ACTION_ID = AAC2.ASSIGNMENT_ACTION_ID
AND ASG.PRIMARY_FLAG = 'Y'
AND AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND LO1.LOOKUP_TYPE = 'ACTION_TYPE'
AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE
AND AAC2.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ACTION_TYPE = 'L'
AND PAC.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE UNION ALL SELECT AAC.ROWID ROW_ID
, AAC2.ASSIGNMENT_ID
, PAC.PAYROLL_ACTION_ID
, PAC.EFFECTIVE_DATE
, PAC.ACTION_TYPE
, DECODE( PAC.ACTION_TYPE
, 'T'
, DECODE(INSTRB(PAC.LEGISLATIVE_PARAMETERS
, 'SLA_MODE=Y')
, 0
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, NULL
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'SLA') )
, LO1.MEANING ) TYPE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, AAC.PRE_PAYMENT_ID
, PEO.FULL_NAME
, PEO.EFFECTIVE_START_DATE PEO_START_DATE
, PEO.EFFECTIVE_END_DATE PEO_END_DATE
, ASG.ASSIGNMENT_NUMBER
, ASG.EFFECTIVE_START_DATE ASG_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_END_DATE
, AAC.ASSIGNMENT_ACTION_ID
, AAC.ACTION_STATUS STATUS_CODE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( AAC.ASSIGNMENT_ACTION_ID
, PAC.ACTION_TYPE
, AAC.ACTION_STATUS) ACTION_STATUS
, AAC.ACTION_SEQUENCE
, PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAY_ASSIGNMENT_ACTIONS_PKG.MESSAGE_LINE_EXISTS(AAC.ASSIGNMENT_ACTION_ID) MESSAGES_EXIST
, PAC.DATE_EARNED
, AAC.SECONDARY_STATUS
, PAC.PURGE_PHASE
FROM PER_ASSIGNMENTS_F2 ASG
, PER_PEOPLE_F PEO
, HR_LOOKUPS LO1
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
, PAY_ASSIGNMENT_ACTIONS AAC2
WHERE AAC2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND AAC2.SOURCE_ACTION_ID = AAC.ASSIGNMENT_ACTION_ID
AND AAC.SOURCE_ACTION_ID IS NULL
AND AAC.ASSIGNMENT_ID IS NULL
AND ASG.PERSON_ID = PEO.PERSON_ID
AND LO1.LOOKUP_TYPE = 'ACTION_TYPE'
AND LO1.LOOKUP_CODE = PAC.ACTION_TYPE
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ACTION_TYPE = 'L'
AND PAC.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE