PAY SuperStream Contribution Report XBRL

Description
Categories: BI Publisher
Columns: Payee Context Id, Payee Usi, Payee Abn, Payee Payment Method, Payee Bp Biller Code, Payee Bp Crn, Payee Dc Bsb, Payee Dc Account No, Payee Dc Account Name, Payee Ele Rpt Address ...
Application: Payroll
Source: SuperStream Contribution Report XBRL
Short Name: PYAUSUPCONTR_XML
DB package: PAY_AU_SUPER_STREAM_ARCHIVE
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'))