PAY SuperStream Contribution Report Alternative Format (ALL FUNDS)

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_INFORMATION7 PAYEE_PAYMENT_METHOD,
       PAYEE.ACTION_INFORMATION8 PAYEE_BP_BILLER_CODE,
       PAYEE.ACTION_INFORMATION9 PAYEE_BP_CRN,
       PAYEE.ACTION_INFORMATION10 PAYEE_DC_BSB,
       PAYEE.ACTION_INFORMATION11 PAYEE_DC_ACCOUNT_NO,
       PAYEE.ACTION_INFORMATION12 PAYEE_DC_ACCOUNT_NAME,
       PAYEE.ACTION_INFORMATION13 PAYEE_ELE_RPT_ADDRESS,
       PAYEE.ACTION_INFORMATION14 PAYEE_PAYMENT_REFERENCE_NUMBER,
       PAYEE.ACTION_INFORMATION15 PAYEE_PAYMENT_TRANSACTION_DATE,
       PAYEE.ACTION_INFORMATION16 PAYEE_CRN_PRN,
       PAI_ASG.ASSIGNMENT_ID PAI_ASG_ASSIGNMENT_ID,
       'EMPLOYEE'||PAI_ASG.ACTION_INFORMATION6 EMPLOYEE_CONTEXT_ID,
       PAI_ASG.ACTION_INFORMATION7 PAI_ASG_TFN_NOT_PROVIDED,
       PAI_ASG.ACTION_INFORMATION8 PAI_ASG_TFN,
       PAI_ASG.ACTION_INFORMATION9 PAI_ASG_FIRST_NAME,
       PAI_ASG.ACTION_INFORMATION10 PAI_ASG_FAMILY_NAME,
       PAI_ASG.ACTION_INFORMATION25 PAI_ASG_MIDDLE_NAME,
       PAI_ASG.ACTION_INFORMATION22 PAI_ASG_TITLE,
       PAI_ASG.ACTION_INFORMATION11 PAI_ASG_DOB,
       PAI_ASG.ACTION_INFORMATION12 PAI_ASG_SEX,
       'RES' PAI_ASG_ADDRESS_USAGE,
       PAI_ASG.ACTION_INFORMATION13 PAI_ASG_ADDRESS_LINE1,
       PAI_ASG.ACTION_INFORMATION14 PAI_ASG_ADDRESS_LINE2,
       PAI_ASG.ACTION_INFORMATION15 PAI_ASG_LOCALITY,
       PAI_ASG.ACTION_INFORMATION16 PAI_ASG_STATE,
       PAI_ASG.ACTION_INFORMATION17 PAI_ASG_COUNTRY,
       PAI_ASG.ACTION_INFORMATION18 PAI_ASG_POSTRAL_CODE,
       PAI_ASG.ACTION_INFORMATION19 PAI_ASG_EMPLOYEE_NUMBER,
       PAI_ASG.ACTION_INFORMATION20 PAI_ASG_TERM_DATE,
       PAI_ASG.ACTION_INFORMATION21 PAI_ASG_TERM_REASON,
       PAI_ASG.ACTION_INFORMATION23 PAI_ASG_MEMBER_NO,
       PAI_DB.ACTION_INFORMATION9 PAI_DB_ORD_TIME_EARN,
       PAI_DB.ACTION_INFORMATION10 PAI_DB_ACT_PTD_SAL,
       PAI_DB.ACTION_INFORMATION11 PAI_DB_SUP_ALLOW_PAID,
       PAI_DB.ACTION_INFORMATION12 PAI_DB_NOT_SUP_ALLOW,
       PAI_DB.ACTION_INFORMATION13 PAI_DB_SERV_FRAC,
       PAI_DB.ACTION_INFORMATION14 PAI_DB_SERV_FRAC_DATE,
       PAI_DB.ACTION_INFORMATION15 PAI_DB_FULL_TIME_HR,
       PAI_DB.ACTION_INFORMATION16 PAI_DB_CONTR_HR,
       PAI_DB.ACTION_INFORMATION17 PAI_DB_ACT_HR_PAID,
       PAI_DB.ACTION_INFORMATION18 PAI_DB_EMP_LOC_ID,
       :P_START_DATE PAI_ASG_PERIOD_START_DATE,
       :P_END_DATE PAI_ASG_PERIOD_END_DATE
 FROM PAY_ACTION_INFORMATION PAYEE,
      PAY_ACTION_INFORMATION PAI_ASG,
      PAY_ACTION_INFORMATION PAI_DB
 WHERE PAYEE.ACTION_CONTEXT_ID = :P_PAYROLL_ACTION_ID
 AND PAYEE.ACTION_INFORMATION_CATEGORY = 'AU_SUPER_STREAM_CONTR_PAYEE'
 AND PAYEE.ACTION_CONTEXT_TYPE = 'PA'
 AND PAI_ASG.ACTION_INFORMATION1 = PAYEE.ACTION_INFORMATION1 
 AND PAI_ASG.ACTION_INFORMATION2 = PAYEE.ACTION_INFORMATION2 
 AND PAI_ASG.ACTION_CONTEXT_ID = :P_PAYROLL_ACTION_ID
 AND PAI_ASG.ACTION_CONTEXT_ID = PAYEE.ACTION_CONTEXT_ID
 AND PAI_ASG.ACTION_INFORMATION_CATEGORY = 'AU_SUPER_STREAM_CONTR_EMP'
 AND PAI_ASG.ACTION_CONTEXT_TYPE = 'PA'
 AND PAI_ASG.TAX_UNIT_ID = :P_LEGAL_EMPLOYER
 AND PAI_DB.ACTION_CONTEXT_ID(+) = :P_PAYROLL_ACTION_ID
 AND PAI_DB.ACTION_CONTEXT_ID(+) = PAI_ASG.ACTION_CONTEXT_ID
 AND PAI_DB.ASSIGNMENT_ID(+) = PAI_ASG.ASSIGNMENT_ID
 AND PAI_DB.ACTION_INFORMATION1(+) = PAI_ASG.ACTION_INFORMATION1 
 AND PAI_DB.ACTION_INFORMATION2(+) = PAI_ASG.ACTION_INFORMATION2 
 AND PAI_DB.ACTION_CONTEXT_TYPE(+) = 'PA'
 AND PAI_DB.ACTION_INFORMATION_CATEGORY(+) = 'AU_SUPER_STREAM_DB_CONTR_INFO'
ORDER BY EMPLOYEE_CONTEXT_ID