VIEW: APPS.PAY_NZ_ASG_PAYMENTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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