[Home] [Help]
View: PAY_NZ_ASG_PAYMENTS_V
View Text
SELECT ASSACT.ROWID ROW_ID
, ASSACT.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, OPM.ORG_PAYMENT_METHOD_NAME PAYMENT_METHOD
, HR_LOOKUPS.MEANING
, TARGET.SEGMENT1 || '-' || TARGET.SEGMENT2 || '-' || TARGET.SEGMENT3 BANK_ACCOUNT
, TARGET.SEGMENT4 CODE
, TARGET.SEGMENT5 REFERENCE
, PP.VALUE PAYMENT_AMOUNT
FROM PAY_EXTERNAL_ACCOUNTS TARGET
, PAY_PERSONAL_PAYMENT_METHODS_F PERPAY
, PAY_PRE_PAYMENTS PP
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_ORG_PAYMENT_METHODS_F OPM
, HR_LOOKUPS
WHERE ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.ACTION_TYPE IN ('P'
, 'U')
AND PP.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND PERPAY.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID
AND PACT.EFFECTIVE_DATE BETWEEN PERPAY.EFFECTIVE_START_DATE
AND PERPAY.EFFECTIVE_END_DATE
AND PERPAY.PERSONAL_PAYMENT_METHOD_ID = PP.PERSONAL_PAYMENT_METHOD_ID
AND TARGET.EXTERNAL_ACCOUNT_ID (+) = PERPAY.EXTERNAL_ACCOUNT_ID
AND SUBSTR(TARGET.SEGMENT1
, 1
, 2) = HR_LOOKUPS.LOOKUP_CODE (+)
AND HR_LOOKUPS.LOOKUP_TYPE(+) = 'NZ_BANK' UNION SELECT ASSACT.ROWID ROW_ID
, ASSACT.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, SUBSTR(OPM.ORG_PAYMENT_METHOD_NAME
, 1
, 30) PAYMENT_METHOD
, NULL BANK
, NULL BANK_ACCOUNT
, NULL CODE
, NULL REFERENCE
, PP.VALUE PAYMENT_AMOUNT
FROM PAY_PRE_PAYMENTS PP
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_ORG_PAYMENT_METHODS_F OPM
WHERE ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.ACTION_TYPE IN ('P'
, 'U')
AND PP.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND PP.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID
AND PP.PERSONAL_PAYMENT_METHOD_ID IS NULL
Columns
Name |
ROW_ID |
ASSIGNMENT_ACTION_ID |
PAYMENT_METHOD |
BANK |
BANK_ACCOUNT |
REFERENCE |
CODE |
PAYMENT_AMOUNT |