VIEW: APPS.PAY_EXTERNAL_ACCOUNTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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