VIEW: APPS.PAY_PAYROLL_ACTIONS_V5

Source

View Text - Preformatted

SELECT DISTINCT PAC.PAYROLL_ACTION_ID , PAC.EFFECTIVE_DATE , PAC.TARGET_PAYROLL_ACTION_ID , PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID , PAC.PAYROLL_ID , PAC.ORG_PAYMENT_METHOD_ID , OPMTL.ORG_PAYMENT_METHOD_NAME OVERRIDE_PAYMENT_METHOD_NAME , PAC.OBJECT_VERSION_NUMBER P_OBJECT_VERSION_NUMBER , AAC.OBJECT_VERSION_NUMBER A_OBJECT_VERSION_NUMBER , AAC.ASSIGNMENT_ACTION_ID , AAC.ASSIGNMENT_ID , AAC.ACTION_STATUS DB_ACTION_STATUS , DECODE(ACI.LOCKING_ACTION_ID, NULL, 'N', 'Y') DB_PAID_PAYMENTS , PAC.LAST_UPDATE_DATE , PAC.LAST_UPDATED_BY , PAC.LAST_UPDATE_LOGIN , PAC.CREATED_BY , PAC.CREATION_DATE FROM PAY_ORG_PAYMENT_METHODS_F_TL OPMTL, PAY_ORG_PAYMENT_METHODS_F OPM , PAY_ASSIGNMENT_ACTIONS AAC , PAY_PAYROLL_ACTIONS PAC , PAY_ACTION_INTERLOCKS ACI WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID (+) AND DECODE(OPMTL.ORG_PAYMENT_METHOD_ID,null,'1',OPMTL.LANGUAGE) = DECODE(OPMTL.ORG_PAYMENT_METHOD_ID,null,'1',userenv('LANG')) /* OVERRIDE PAYMENT METHOD */ AND PAC.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE (+) AND OPM.EFFECTIVE_END_DATE (+) AND PAC.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID (+) /* DERIVE DB_PAID_PAYMENTS. FIND OUT IF AT LEAST ONE OF THE PRE-PAYMENT ROWS HAS BEEN PAID YET.*/ AND ACI.LOCKED_ACTION_ID (+) = AAC.ASSIGNMENT_ACTION_ID AND ( ACI.LOCKING_ACTION_ID IS NULL OR EXISTS (SELECT AAC2.ASSIGNMENT_ACTION_ID FROM PAY_PAYROLL_ACTIONS PAC2 , PAY_ASSIGNMENT_ACTIONS AAC2 , PAY_ACTION_INTERLOCKS ACI2 WHERE ACI2.LOCKED_ACTION_ID = AAC.ASSIGNMENT_ACTION_ID AND AAC2.ASSIGNMENT_ACTION_ID = ACI2.LOCKING_ACTION_ID AND PAC2.PAYROLL_ACTION_ID = AAC2.PAYROLL_ACTION_ID AND PAC2.ACTION_TYPE IN ('E', 'H', 'A', 'M','X') ) ) /* RESTRICTIONS AND JOINS TO PAY_ASSIGNMENT_ACTIONS */ AND PAC.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID AND PAC.ACTION_TYPE = 'U'

View Text - HTML Formatted

SELECT DISTINCT PAC.PAYROLL_ACTION_ID
, PAC.EFFECTIVE_DATE
, PAC.TARGET_PAYROLL_ACTION_ID
, PAC.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAC.PAYROLL_ID
, PAC.ORG_PAYMENT_METHOD_ID
, OPMTL.ORG_PAYMENT_METHOD_NAME OVERRIDE_PAYMENT_METHOD_NAME
, PAC.OBJECT_VERSION_NUMBER P_OBJECT_VERSION_NUMBER
, AAC.OBJECT_VERSION_NUMBER A_OBJECT_VERSION_NUMBER
, AAC.ASSIGNMENT_ACTION_ID
, AAC.ASSIGNMENT_ID
, AAC.ACTION_STATUS DB_ACTION_STATUS
, DECODE(ACI.LOCKING_ACTION_ID
, NULL
, 'N'
, 'Y') DB_PAID_PAYMENTS
, PAC.LAST_UPDATE_DATE
, PAC.LAST_UPDATED_BY
, PAC.LAST_UPDATE_LOGIN
, PAC.CREATED_BY
, PAC.CREATION_DATE
FROM PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_ORG_PAYMENT_METHODS_F OPM
, PAY_ASSIGNMENT_ACTIONS AAC
, PAY_PAYROLL_ACTIONS PAC
, PAY_ACTION_INTERLOCKS ACI
WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID (+)
AND DECODE(OPMTL.ORG_PAYMENT_METHOD_ID
, NULL
, '1'
, OPMTL.LANGUAGE) = DECODE(OPMTL.ORG_PAYMENT_METHOD_ID
, NULL
, '1'
, USERENV('LANG')) /* OVERRIDE PAYMENT METHOD */
AND PAC.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE (+)
AND OPM.EFFECTIVE_END_DATE (+)
AND PAC.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID (+) /* DERIVE DB_PAID_PAYMENTS. FIND OUT IF AT LEAST ONE OF THE PRE-PAYMENT ROWS HAS BEEN PAID YET.*/
AND ACI.LOCKED_ACTION_ID (+) = AAC.ASSIGNMENT_ACTION_ID
AND ( ACI.LOCKING_ACTION_ID IS NULL OR EXISTS (SELECT AAC2.ASSIGNMENT_ACTION_ID
FROM PAY_PAYROLL_ACTIONS PAC2
, PAY_ASSIGNMENT_ACTIONS AAC2
, PAY_ACTION_INTERLOCKS ACI2
WHERE ACI2.LOCKED_ACTION_ID = AAC.ASSIGNMENT_ACTION_ID
AND AAC2.ASSIGNMENT_ACTION_ID = ACI2.LOCKING_ACTION_ID
AND PAC2.PAYROLL_ACTION_ID = AAC2.PAYROLL_ACTION_ID
AND PAC2.ACTION_TYPE IN ('E'
, 'H'
, 'A'
, 'M'
, 'X') ) ) /* RESTRICTIONS
AND JOINS TO PAY_ASSIGNMENT_ACTIONS */
AND PAC.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
AND PAC.ACTION_TYPE = 'U'