PAY SuperStream Contribution Report XBRL

Description
Categories: BI Publisher, Human Resources
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'))