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 |
