View: PAY_ASSIGNMENT_ACTIONS_V

Product:	PAY - Payroll
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PAY_ASSIGNMENT_ACTIONS_V

View Text

SELECT /*+ INDEX (PAC PAY_PAYROLL_ACTIONS_PK) USE_NL (PAC
, AAC) */ 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 /*+ INDEX (PAC PAY_PAYROLL_ACTIONS_PK) USE_NL (PAC
, AAC) */ 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 /*+ INDEX (PAC PAY_PAYROLL_ACTIONS_PK) USE_NL (PAC
, AAC) */ 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 /*+ INDEX (PAC PAY_PAYROLL_ACTIONS_PK) USE_NL (PAC
, AAC) */ 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 /*+ INDEX (PAC PAY_PAYROLL_ACTIONS_PK) USE_NL (PAC
, AAC) */ 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 /*+ INDEX (PAC PAY_PAYROLL_ACTIONS_PK) USE_NL (PAC
, AAC) */ 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

Columns