View: PAY_EXTERNAL_ACCOUNTS_V

Product:	PAY - Payroll
Description:	View based on PAY_PRE_PAYMENTS used in the PAYGBSOE form and Pay Advice Report.
Implementation/DBA Data:	APPS.PAY_EXTERNAL_ACCOUNTS_V

View Text

SELECT ASSACT.ROWID ROW_ID
, ASSACT.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, SUBSTR(OPMTL.ORG_PAYMENT_METHOD_NAME
, 1
, 30) PAYMENT_METHOD
, SUBSTR(BANK.MEANING
, 1
, 30) BANK
, SUBSTR(TARGET.SEGMENT3
, 1
, 6) SORT_CODE
, SUBSTR(TARGET.SEGMENT4
, 1
, 8) ACCOUNT_NO
, SUBSTR(TARGET.SEGMENT7
, 1
, 18) SOCIETY_ACCOUNT_NO
, 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
, HR_LOOKUPS BANK
, PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_ORG_PAYMENT_METHODS_F OPM
WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
AND OPMTL.LANGUAGE = USERENV('LANG')
AND 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 BANK.LOOKUP_CODE(+) = TARGET.SEGMENT1
AND BANK.LOOKUP_TYPE(+) = 'GB_BANKS'
AND PACT.EFFECTIVE_DATE BETWEEN PERPAY.EFFECTIVE_START_DATE
AND PERPAY.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE
AND OPM.EFFECTIVE_END_DATE
AND PERPAY.PERSONAL_PAYMENT_METHOD_ID = PP.PERSONAL_PAYMENT_METHOD_ID
AND TARGET.EXTERNAL_ACCOUNT_ID(+) = PERPAY.EXTERNAL_ACCOUNT_ID UNION ALL SELECT ASSACT.ROWID ROW_ID
, ASSACT.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, SUBSTR(OPMTL.ORG_PAYMENT_METHOD_NAME
, 1
, 30) PAYMENT_METHOD
, NULL BANK
, NULL SORT_CODE
, NULL ACCOUNT_NO
, NULL SOCIETY_ACCOUNT_NO
, PP.VALUE PAYMENT_AMOUNT
FROM PAY_PRE_PAYMENTS PP
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_ORG_PAYMENT_METHODS_F OPM
WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
AND OPMTL.LANGUAGE = USERENV('LANG')
AND 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 PACT.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE
AND OPM.EFFECTIVE_END_DATE
AND PP.PERSONAL_PAYMENT_METHOD_ID IS NULL

Columns