VIEW: APPS.PAY_AU_ASG_PAYMENTS_V

Source

View Text - Preformatted

SELECT pac.rowid row_id, pac.assignment_action_id assignment_action_id, pop.org_payment_method_name payment_method, pea.segment1 bank, pea.segment2 bank_account_name, pea.segment3 bank_account, ppp.value payment_amount, ppm.effective_start_date pay_meth_start_date, /* Start 9221420 */ ppm.effective_end_date pay_meth_end_date, pop.effective_start_date org_pay_meth_start_date, pop.effective_end_date org_pay_meth_end_date /* End 9221420 */ from pay_external_accounts pea, pay_personal_payment_methods_f ppm, pay_pre_payments ppp, pay_assignment_actions pac, pay_payroll_actions ppa, pay_org_payment_methods_f pop, hr_lookups where ppa.payroll_action_id = pac.payroll_action_id and ppa.action_type in ('P','U') and pac.assignment_action_id = ppp.assignment_action_id and pop.org_payment_method_id = ppm.org_payment_method_id and ppm.personal_payment_method_id = ppp.personal_payment_method_id and pea.external_account_id (+) = ppm.external_account_id and pea.segment1 = hr_lookups.lookup_code (+) and hr_lookups.lookup_type(+) = 'AU_BANK' union all select pac.rowid row_id, pac.assignment_action_id assignment_action_id, pop.org_payment_method_name payment_method, null bank, null bank_account_name, null bank_account, ppp.value payment_amount, null pay_meth_start_date, /* Start 9221420 */ null pay_meth_end_date, pop.effective_start_date org_pay_meth_start_date, pop.effective_end_date org_pay_meth_end_date /* End 9221420 */ from pay_pre_payments ppp, pay_assignment_actions pac, pay_payroll_actions ppa, pay_org_payment_methods_f pop where ppa.payroll_action_id = pac.payroll_action_id and ppa.action_type in ('P','U') and pac.assignment_action_id = ppp.assignment_action_id and pop.org_payment_method_id = ppp.org_payment_method_id and ppp.personal_payment_method_id is null

View Text - HTML Formatted

SELECT PAC.ROWID ROW_ID
, PAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, POP.ORG_PAYMENT_METHOD_NAME PAYMENT_METHOD
, PEA.SEGMENT1 BANK
, PEA.SEGMENT2 BANK_ACCOUNT_NAME
, PEA.SEGMENT3 BANK_ACCOUNT
, PPP.VALUE PAYMENT_AMOUNT
, PPM.EFFECTIVE_START_DATE PAY_METH_START_DATE
, /* START 9221420 */ PPM.EFFECTIVE_END_DATE PAY_METH_END_DATE
, POP.EFFECTIVE_START_DATE ORG_PAY_METH_START_DATE
, POP.EFFECTIVE_END_DATE ORG_PAY_METH_END_DATE /* END 9221420 */
FROM PAY_EXTERNAL_ACCOUNTS PEA
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PAY_PRE_PAYMENTS PPP
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_PAYROLL_ACTIONS PPA
, PAY_ORG_PAYMENT_METHODS_F POP
, HR_LOOKUPS
WHERE PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('P'
, 'U')
AND PAC.ASSIGNMENT_ACTION_ID = PPP.ASSIGNMENT_ACTION_ID
AND POP.ORG_PAYMENT_METHOD_ID = PPM.ORG_PAYMENT_METHOD_ID
AND PPM.PERSONAL_PAYMENT_METHOD_ID = PPP.PERSONAL_PAYMENT_METHOD_ID
AND PEA.EXTERNAL_ACCOUNT_ID (+) = PPM.EXTERNAL_ACCOUNT_ID
AND PEA.SEGMENT1 = HR_LOOKUPS.LOOKUP_CODE (+)
AND HR_LOOKUPS.LOOKUP_TYPE(+) = 'AU_BANK' UNION ALL SELECT PAC.ROWID ROW_ID
, PAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, POP.ORG_PAYMENT_METHOD_NAME PAYMENT_METHOD
, NULL BANK
, NULL BANK_ACCOUNT_NAME
, NULL BANK_ACCOUNT
, PPP.VALUE PAYMENT_AMOUNT
, NULL PAY_METH_START_DATE
, /* START 9221420 */ NULL PAY_METH_END_DATE
, POP.EFFECTIVE_START_DATE ORG_PAY_METH_START_DATE
, POP.EFFECTIVE_END_DATE ORG_PAY_METH_END_DATE /* END 9221420 */
FROM PAY_PRE_PAYMENTS PPP
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_PAYROLL_ACTIONS PPA
, PAY_ORG_PAYMENT_METHODS_F POP
WHERE PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('P'
, 'U')
AND PAC.ASSIGNMENT_ACTION_ID = PPP.ASSIGNMENT_ACTION_ID
AND POP.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND PPP.PERSONAL_PAYMENT_METHOD_ID IS NULL