PAY SuperStream Contribution Report XBRL

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'))

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'))