PAY SuperStream Registration Report Alternative Format (ALL FUNDS)

Description
Categories: BI Publisher
Columns: Msgrcr Context Id, Msgrcr Name, Msgrcr Abn, Payee Context Id, Payee Usi, Payee Abn, Payee Ele Rpt Address, Pai Reg Assignment Id, Employee Context Id, Pai Reg Tfn Not Provided ...
Application: Payroll
Source: SuperStream Registration Report Alternative Format (ALL FUNDS)
Short Name: PYAUSUPREGIALLCSV_XSLFO
DB package: PAY_AU_SUPER_STREAM_ARCHIVE
SELECT 'MSGRCR' MSGRCR_CONTEXT_ID,
       PAYEE.ACTION_INFORMATION3 MSGRCR_NAME,
       PAYEE.ACTION_INFORMATION4 MSGRCR_ABN,
        'PAYEE' PAYEE_CONTEXT_ID,
       PAYEE.ACTION_INFORMATION5 PAYEE_USI,
       PAYEE.ACTION_INFORMATION6 PAYEE_ABN,
       PAYEE.ACTION_INFORMATION13 PAYEE_ELE_RPT_ADDRESS,
       PAI_REG.ASSIGNMENT_ID PAI_REG_ASSIGNMENT_ID,
       'EMPLOYEE'||PAI_REG.ACTION_INFORMATION6 EMPLOYEE_CONTEXT_ID,
       PAI_REG.ACTION_INFORMATION7 PAI_REG_TFN_NOT_PROVIDED,
       PAI_REG.ACTION_INFORMATION8 PAI_REG_TFN,
       PAI_REG.ACTION_INFORMATION9 PAI_REG_FIRST_NAME,
       PAI_REG.ACTION_INFORMATION10 PAI_REG_FAMILY_NAME,
       PAI_REG.ACTION_INFORMATION27 PAI_REG_MIDDLE_NAME,
       PAI_REG.ACTION_INFORMATION25 PAI_REG_TITLE,
       PAI_REG.ACTION_INFORMATION11 PAI_REG_DOB,
       PAI_REG.ACTION_INFORMATION12 PAI_REG_SEX,
       'RES' PAI_REG_ADDRESS_USAGE,
       PAI_REG.ACTION_INFORMATION13 PAI_REG_ADDRESS_LINE1,
       PAI_REG.ACTION_INFORMATION14 PAI_REG_ADDRESS_LINE2,
       PAI_REG.ACTION_INFORMATION15 PAI_REG_LOCALITY,
       PAI_REG.ACTION_INFORMATION16 PAI_REG_STATE,
       PAI_REG.ACTION_INFORMATION17 PAI_REG_COUNTRY,
       PAI_REG.ACTION_INFORMATION18 PAI_REG_POSTRAL_CODE,
       PAI_REG.ACTION_INFORMATION19 PAI_REG_EMPLOYEE_NUMBER,
       PAI_REG.ACTION_INFORMATION23 PAI_REG_TERM_DATE,
       PAI_REG.ACTION_INFORMATION24 PAI_REG_TERM_REASON,
       PAI_INFO.ACTION_INFORMATION6 PAI_INFO_MEMBER_NO,
       PAI_REG.ACTION_INFORMATION20 PAI_REG_WEEKLY_HOURS,
       PAI_REG.ACTION_INFORMATION21 PAI_REG_EMPLOYMENT_STATUS,
       PAI_REG.ACTION_INFORMATION22 PAI_REG_DATE_START,
       PAI_INFO.ACTION_INFORMATION7 PAI_INFO_FUND_REGI_DATE,
       PAI_INFO.ACTION_INFORMATION8 PAI_INFO_SAL_BENEFIT,
       PAI_INFO.ACTION_INFORMATION9 PAI_INFO_SAL_CONTR,
       PAI_INFO.ACTION_INFORMATION10 PAI_INFO_SAL_START_DATE,
       PAI_INFO.ACTION_INFORMATION11 PAI_INFO_SAL_END_DATE,
       PAI_INFO.ACTION_INFORMATION12 PAI_INFO_SAL_INSURANCE,
       PAI_INFO.ACTION_INFORMATION13 PAI_INFO_OCCUPATION,
       PAI_INFO.ACTION_INFORMATION14 PAI_INFO_INSURANCE_OPT,
       PAI_INFO.ACTION_INFORMATION15 PAI_INFO_AT_WORK,
       PAI_INFO.ACTION_INFORMATION16 PAI_INFO_CONTR_START_DATE,
       PAI_INFO.ACTION_INFORMATION17 PAI_INFO_CONTR_END_DATE,
       PAI_INFO.ACTION_INFORMATION18 PAI_INFO_AMEND_REASON,
       PAI_INFO_DB1.ACTION_INFORMATION6 PAI_DB_SERV_FRAC,
       PAI_INFO_DB1.ACTION_INFORMATION7 PAI_DB_SERV_FRAC_START,
       PAI_INFO_DB1.ACTION_INFORMATION8 PAI_DB_SERV_FRAC_END,
       PAI_INFO_DB1.ACTION_INFORMATION9 PAI_DB_LEAVE_WO_PAY,
       PAI_INFO_DB1.ACTION_INFORMATION10 PAI_DB_LEAVE_WO_PAY_START,
       PAI_INFO_DB1.ACTION_INFORMATION11 PAI_DB_LEAVE_WO_PAY_END,
       PAI_INFO_DB1.ACTION_INFORMATION12 PAI_DB_SAL_INS_DATE,
       PAI_INFO_DB1.ACTION_INFORMATION13 PAI_DB_SAL_BEN_DATE,
       PAI_INFO_DB1.ACTION_INFORMATION14 PAI_DB_EMP_STATUS_DATE,
       PAI_INFO_DB1.ACTION_INFORMATION15 PAI_DB_EMP_BEN_CAT_DATE,
       PAI_INFO_DB1.ACTION_INFORMATION16 PAI_DB_EMP_LOC_ID,
       PAI_INFO_DB1.ACTION_INFORMATION17 PAI_DB_EMP_LOC_ID_START,
       PAI_INFO_DB1.ACTION_INFORMATION18 PAI_DB_EMP_LOC_ID_END,
       PAI_INFO_DB2.ACTION_INFORMATION6 PAI_DB_ER_RATE,
       PAI_INFO_DB2.ACTION_INFORMATION7 PAI_DB_ER_RATE_START,
       PAI_INFO_DB2.ACTION_INFORMATION8 PAI_DB_ER_RATE_END,
       PAI_INFO_DB2.ACTION_INFORMATION9 PAI_DB_MEM_RATE,
       PAI_INFO_DB2.ACTION_INFORMATION10 PAI_DB_MEM_RATE_START,
       PAI_INFO_DB2.ACTION_INFORMATION11 PAI_DB_MEM_RATE_END,
       PAI_INFO_DB2.ACTION_INFORMATION12 PAI_DB_SAL1,
       PAI_INFO_DB2.ACTION_INFORMATION13 PAI_DB_SAL1_START,
       PAI_INFO_DB2.ACTION_INFORMATION14 PAI_DB_SAL1_END,
       PAI_INFO_DB2.ACTION_INFORMATION15 PAI_DB_SAL2,
       PAI_INFO_DB2.ACTION_INFORMATION16 PAI_DB_SAL2_START,
       PAI_INFO_DB2.ACTION_INFORMATION17 PAI_DB_SAL2_END,
       PAI_INFO_DB2.ACTION_INFORMATION18 PAI_DB_SAL3,
       PAI_INFO_DB2.ACTION_INFORMATION19 PAI_DB_SAL3_START,
       PAI_INFO_DB2.ACTION_INFORMATION20 PAI_DB_SAL3_END,
       PAI_INFO_DB2.ACTION_INFORMATION21 PAI_DB_SAL4,
       PAI_INFO_DB2.ACTION_INFORMATION22 PAI_DB_SAL4_START,
       PAI_INFO_DB2.ACTION_INFORMATION23 PAI_DB_SAL4_END,
       PAI_INFO_DB2.ACTION_INFORMATION24 PAI_DB_SAL5,
       PAI_INFO_DB2.ACTION_INFORMATION25 PAI_DB_SAL5_START,
       PAI_INFO_DB2.ACTION_INFORMATION26 PAI_DB_SAL5_END
 FROM PAY_ACTION_INFORMATION PAYEE,
      PAY_ACTION_INFORMATION PAI_REG, 
      PAY_ACTION_INFORMATION PAI_INFO,
      PAY_ACTION_INFORMATION PAI_INFO_DB1,
      PAY_ACTION_INFORMATION PAI_INFO_DB2
 WHERE PAYEE.ACTION_CONTEXT_ID = :P_PAYROLL_ACTION_ID
 AND PAYEE.ACTION_INFORMATION_CATEGORY = 'AU_SUPER_STREAM_REGI_PAYEE'
 AND PAYEE.ACTION_CONTEXT_TYPE = 'PA'
 AND PAYEE.ACTION_INFORMATION1 = PAI_REG.ACTION_INFORMATION1 -- FUND NAME
 AND PAYEE.ACTION_INFORMATION2 = PAI_REG.ACTION_INFORMATION2 -- FUND USI
 AND PAI_REG.ACTION_CONTEXT_ID = :P_PAYROLL_ACTION_ID
 AND PAI_REG.ACTION_INFORMATION_CATEGORY = 'AU_SUPER_STREAM_REGI_EMP'
 AND PAI_REG.ACTION_CONTEXT_TYPE = 'PA'
 AND PAI_REG.ACTION_INFORMATION1 = PAI_INFO.ACTION_INFORMATION1(+)
 AND PAI_REG.ACTION_INFORMATION2 = PAI_INFO.ACTION_INFORMATION2(+)
 AND PAI_REG.ASSIGNMENT_ID = PAI_INFO.ASSIGNMENT_ID(+)
 AND PAI_INFO.ACTION_CONTEXT_ID(+) = :P_PAYROLL_ACTION_ID
 AND PAI_INFO.ACTION_INFORMATION_CATEGORY(+) = 'AU_SUPER_STREAM_REGI_INFO'
 AND PAI_INFO.ACTION_CONTEXT_TYPE(+) = 'PA'
 AND PAI_REG.ASSIGNMENT_ID = PAI_INFO_DB1.ASSIGNMENT_ID(+)
 AND PAI_INFO_DB1.ACTION_CONTEXT_ID(+) = :P_PAYROLL_ACTION_ID
 AND PAI_INFO_DB1.ACTION_INFORMATION_CATEGORY(+) = 'AU_SUPER_STREAM_DB_REGI_INFO1'
 AND PAI_INFO_DB1.ACTION_CONTEXT_TYPE(+) = 'PA'
 AND PAI_INFO_DB1.ACTION_INFORMATION1(+) = PAI_REG.ACTION_INFORMATION1
 AND PAI_INFO_DB1.ACTION_INFORMATION2(+) = PAI_REG.ACTION_INFORMATION2
 AND PAI_REG.ASSIGNMENT_ID = PAI_INFO_DB2.ASSIGNMENT_ID(+)
 AND PAI_INFO_DB2.ACTION_CONTEXT_ID(+) = :P_PAYROLL_ACTION_ID
 AND PAI_INFO_DB2.ACTION_INFORMATION_CATEGORY(+) = 'AU_SUPER_STREAM_DB_REGI_INFO2'
 AND PAI_INFO_DB2.ACTION_CONTEXT_TYPE(+) = 'PA'
 AND PAI_INFO_DB2.ACTION_INFORMATION1(+) = PAI_REG.ACTION_INFORMATION1
 AND PAI_INFO_DB2.ACTION_INFORMATION2(+) = PAI_REG.ACTION_INFORMATION2
ORDER BY EMPLOYEE_CONTEXT_ID