select 'PAYEE' PAYEE_CONTEXT_ID,
DECODE(ORG_INFORMATION12, 'Y', NULL, :P_SUPER_FUND_USI) PAYEE_USI,
ORG_INFORMATION4 PAYEE_ABN,
ORG_INFORMATION5 PAYEE_PAYMENT_METHOD,
ORG_INFORMATION6 PAYEE_BP_BILLER_CODE,
ORG_INFORMATION7 PAYEE_BP_CRN,
ORG_INFORMATION8 PAYEE_DC_BSB,
ORG_INFORMATION9 PAYEE_DC_ACCOUNT_NO,
ORG_INFORMATION10 PAYEE_DC_ACCOUNT_NAME,
ORG_INFORMATION11 PAYEE_ELE_RPT_ADDRESS,
:P_PAYMENT_REFERENCE_NUMBER PAYEE_PAYMENT_REFERENCE_NUMBER,
:P_PAYMENT_TRANSACTION_DATE PAYEE_PAYMENT_TRANSACTION_DATE,
DECODE(ORG_INFORMATION5, 'BPAY', ORG_INFORMATION7, :P_PAYMENT_REFERENCE_NUMBER) PAYEE_CRN_PRN
from HR_ORGANIZATION_INFORMATION HOI,
HR_ORGANIZATION_UNITS HOU
WHERE HOU.BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID
AND HOI.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'AU_BG_SUPER_FUND'
AND HOI.ORG_INFORMATION2 = :P_SUPER_FUND_NAME
AND HOI.ORG_INFORMATION3 = :P_SUPER_FUND_USI
UNION
select 'PAYEE' PAYEE_CONTEXT_ID,
DECODE(ORG_INFORMATION20, 'Y', NULL, :P_SUPER_FUND_USI) PAYEE_USI,
ORG_INFORMATION12 PAYEE_ABN,
ORG_INFORMATION14 PAYEE_PAYMENT_METHOD,
ORG_INFORMATION15 PAYEE_BP_BILLER_CODE,
ORG_INFORMATION16 PAYEE_BP_CRN,
ORG_INFORMATION17 PAYEE_DC_BSB,
ORG_INFORMATION18 PAYEE_DC_ACCOUNT_NO,
ORG_INFORMATION19 PAYEE_DC_ACCOUNT_NAME,
ORG_INFORMATION13 PAYEE_ELE_RPT_ADDRESS,
:P_PAYMENT_REFERENCE_NUMBER PAYEE_PAYMENT_REFERENCE_NUMBER,
:P_PAYMENT_TRANSACTION_DATE PAYEE_PAYMENT_TRANSACTION_DATE,
DECODE(ORG_INFORMATION14, 'BPAY', ORG_INFORMATION16, :P_PAYMENT_REFERENCE_NUMBER) PAYEE_CRN_PRN
from HR_ORGANIZATION_INFORMATION HOI,
HR_ORGANIZATION_UNITS HOU
WHERE HOU.BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID
AND HOI.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'AU_SUPER_FUND'
AND HOI.ORG_INFORMATION2 = :P_SUPER_FUND_NAME
AND HOI.ORG_INFORMATION11 = :P_SUPER_FUND_USI
and :P_END_DATE between to_date(hoi.org_information9,'yyyy/mm/dd hh24:mi:ss')
and nvl(to_date(hoi.org_information10,'yyyy/mm/dd hh24:mi:ss'), to_date('4712/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')) |
