PAY SuperStream Combined Report Alternative Format (ALL FUNDS)

➜ Back to Library
Description
Categories: BI Publisher
Application: Payroll
Source: SuperStream Combined Report Alternative Format (ALL FUNDS)
Short Name: PYAUSUPCOMBALLCSV_XSLFO
DB package: PAY_AU_SUPER_STREAM_ARCHIVE

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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,
       'C'||PAI_ASG.ACTION_INFORMATION6 EMPLOYEE_CONTEXT_ID,
       PAI_ASG.ACTION_INFORMATION7 TFN_NOT_PROVIDED,
       PAI_ASG.ACTION_INFORMATION8 TFN,
       PAI_ASG.ACTION_INFORMATION9 FIRST_NAME,
       PAI_ASG.ACTION_INFORMATION10 FAMILY_NAME,
       PAI_ASG.ACTION_INFORMATION25 MIDDLE_NAME,
       PAI_ASG.ACTION_INFORMATION22 TITLE,
       PAI_ASG.ACTION_INFORMATION11 DOB,
       PAI_ASG.ACTION_INFORMATION12 SEX,
       'RES' ADDRESS_USAGE,
       PAI_ASG.ACTION_INFORMATION13 ADDRESS_LINE1,
       PAI_ASG.ACTION_INFORMATION14 ADDRESS_LINE2,
       PAI_ASG.ACTION_INFORMATION15 LOCALITY,
       PAI_ASG.ACTION_INFORMATION16 STATE,
       PAI_ASG.ACTION_INFORMATION17 COUNTRY,
       PAI_ASG.ACTION_INFORMATION18 POSTRAL_CODE,
       PAI_ASG.ACTION_INFORMATION19 EMPLOYEE_NUMBER,
       PAI_ASG.ACTION_INFORMATION20 TERM_DATE,
       PAI_ASG.ACTION_INFORMATION21 TERM_REASON,
       PAI_ASG.ACTION_INFORMATION23 MEMBER_NO,
       :P_START_DATE PERIOD_START_DATE,
       :P_END_DATE PERIOD_END_DATE,
       PAI_DB.ACTION_INFORMATION9 DB_ORD_TIME_EARN,
       PAI_DB.ACTION_INFORMATION10 DB_ACT_PTD_SAL,
       PAI_DB.ACTION_INFORMATION11 DB_SUP_ALLOW_PAID,
       PAI_DB.ACTION_INFORMATION12 DB_NOT_SUP_ALLOW,
       PAI_DB.ACTION_INFORMATION13 DB_SERV_FRAC,
       PAI_DB.ACTION_INFORMATION14 DB_SERV_FRAC_DATE,
       PAI_DB.ACTION_INFORMATION15 DB_FULL_TIME_HR,
       PAI_DB.ACTION_INFORMATION16 DB_CONTR_HR,
       PAI_DB.ACTION_INFORMATION17 DB_ACT_HR_PAID,
       PAI_DB.ACTION_INFORMATION18 DB_EMP_LOC_ID,
       null WEEKLY_HOURS,
       null EMPLOYMENT_STATUS,
       null DATE_START,
       null FUND_REGI_DATE,
       null SAL_BENEFIT,
       null SAL_CONTR,
       null SAL_START_DATE,
       null SAL_END_DATE,
       null SAL_INSURANCE,
       null OCCUPATION,
       null INSURANCE_OPT,
       null AT_WORK,
       null CONTR_START_DATE,
       null CONTR_END_DATE,
       null AMEND_REASON,
       null PAI_DB_SERV_FRAC,
       null PAI_DB_SERV_FRAC_START,
       null PAI_DB_SERV_FRAC_END,
       null PAI_DB_LEAVE_WO_PAY,
       null PAI_DB_LEAVE_WO_PAY_START,
       null PAI_DB_LEAVE_WO_PAY_END,
       null PAI_DB_SAL_INS_DATE,
       null PAI_DB_SAL_BEN_DATE,
       null PAI_DB_EMP_STATUS_DATE,
       null PAI_DB_EMP_BEN_CAT_DATE,
       null PAI_DB_EMP_LOC_ID,
       null PAI_DB_EMP_LOC_ID_START,
       null PAI_DB_EMP_LOC_ID_END,
       null PAI_DB_ER_RATE,
       null PAI_DB_ER_RATE_START,
       null PAI_DB_ER_RATE_END,
       null PAI_DB_MEM_RATE,
       null PAI_DB_MEM_RATE_START,
       null PAI_DB_MEM_RATE_END,
       null PAI_DB_SAL1,
       null PAI_DB_SAL1_START,
       null PAI_DB_SAL1_END,
       null PAI_DB_SAL2,
       null PAI_DB_SAL2_START,
       null PAI_DB_SAL2_END,
       null PAI_DB_SAL3,
       null PAI_DB_SAL3_START,
       null PAI_DB_SAL3_END,
       null PAI_DB_SAL4,
       null PAI_DB_SAL4_START,
       null PAI_DB_SAL4_END,
       null PAI_DB_SAL5,
       null PAI_DB_SAL5_START,
       null PAI_DB_SAL5_END
 FROM PAY_ACTION_INFORMATION PAI_ASG,
      PAY_ACTION_INFORMATION PAYEE,
      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  -- FUND NAME
 AND PAI_ASG.ACTION_INFORMATION2 = PAYEE.ACTION_INFORMATION2  -- FUND USI
 AND PAI_ASG.ACTION_CONTEXT_ID =  :P_PAYROLL_ACTION_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.ASSIGNMENT_ID(+) = PAI_ASG.ASSIGNMENT_ID
 AND PAI_DB.ACTION_CONTEXT_TYPE(+) = 'PA'
 AND PAI_DB.ACTION_INFORMATION_CATEGORY(+) = 'AU_SUPER_STREAM_DB_CONTR_INFO'
 AND PAI_DB.ACTION_INFORMATION1(+) = PAI_ASG.ACTION_INFORMATION1  -- FUND NAME
 AND PAI_DB.ACTION_INFORMATION2(+) = PAI_ASG.ACTION_INFORMATION2  -- FUND USI
 AND NOT EXISTS ( SELECT NULL FROM PAY_ACTION_INFORMATION PAI_REG
                  WHERE PAI_REG.ASSIGNMENT_ID = PAI_ASG.ASSIGNMENT_ID
                    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_ASG.ACTION_INFORMATION1
                    AND PAI_REG.ACTION_INFORMATION2 = PAI_ASG.ACTION_INFORMATION2)
UNION ALL
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_REG.ASSIGNMENT_ID PAI_ASG_ASSIGNMENT_ID,
       'R'||PAI_REG.ACTION_INFORMATION6 EMPLOYEE_CONTEXT_ID,
       PAI_REG.ACTION_INFORMATION7 TFN_NOT_PROVIDED,
       PAI_REG.ACTION_INFORMATION8 TFN,
       PAI_REG.ACTION_INFORMATION9 FIRST_NAME,
       PAI_REG.ACTION_INFORMATION10 FAMILY_NAME,
       PAI_REG.ACTION_INFORMATION27 MIDDLE_NAME,
       PAI_REG.ACTION_INFORMATION25 TITLE,
       PAI_REG.ACTION_INFORMATION11 DOB,
       PAI_REG.ACTION_INFORMATION12 SEX,
       'RES' ADDRESS_USAGE,
       PAI_REG.ACTION_INFORMATION13 ADDRESS_LINE1,
       PAI_REG.ACTION_INFORMATION14 ADDRESS_LINE2,
       PAI_REG.ACTION_INFORMATION15 LOCALITY,
       PAI_REG.ACTION_INFORMATION16 STATE,
       PAI_REG.ACTION_INFORMATION17 COUNTRY,
       PAI_REG.ACTION_INFORMATION18 POSTRAL_CODE,
       PAI_REG.ACTION_INFORMATION19 EMPLOYEE_NUMBER,
       PAI_REG.ACTION_INFORMATION23 TERM_DATE,
       PAI_REG.ACTION_INFORMATION24 TERM_REASON,
       PAI_INFO.ACTION_INFORMATION6 MEMBER_NO,
       :P_START_DATE PERIOD_START_DATE,
       :P_END_DATE PERIOD_END_DATE,
       PAI_DB.ACTION_INFORMATION9 DB_ORD_TIME_EARN,
       PAI_DB.ACTION_INFORMATION10 DB_ACT_PTD_SAL,
       PAI_DB.ACTION_INFORMATION11 DB_SUP_ALLOW_PAID,
       PAI_DB.ACTION_INFORMATION12 DB_NOT_SUP_ALLOW,
       PAI_DB.ACTION_INFORMATION13 DB_SERV_FRAC,
       PAI_DB.ACTION_INFORMATION14 DB_SERV_FRAC_DATE,
       PAI_DB.ACTION_INFORMATION15 DB_FULL_TIME_HR,
       PAI_DB.ACTION_INFORMATION16 DB_CONTR_HR,
       PAI_DB.ACTION_INFORMATION17 DB_ACT_HR_PAID,
       PAI_DB.ACTION_INFORMATION18 DB_EMP_LOC_ID,
       PAI_REG.ACTION_INFORMATION20 WEEKLY_HOURS,
       PAI_REG.ACTION_INFORMATION21 EMPLOYMENT_STATUS,
       PAI_REG.ACTION_INFORMATION22 DATE_START,
       PAI_INFO.ACTION_INFORMATION7 FUND_REGI_DATE,
       PAI_INFO.ACTION_INFORMATION8 SAL_BENEFIT,
       PAI_INFO.ACTION_INFORMATION9 SAL_CONTR,
       PAI_INFO.ACTION_INFORMATION10 SAL_START_DATE,
       PAI_INFO.ACTION_INFORMATION11 SAL_END_DATE,
       PAI_INFO.ACTION_INFORMATION12 SAL_INSURANCE,
       PAI_INFO.ACTION_INFORMATION13 OCCUPATION,
       PAI_INFO.ACTION_INFORMATION14 INSURANCE_OPT,
       PAI_INFO.ACTION_INFORMATION15 AT_WORK,
       PAI_INFO.ACTION_INFORMATION16 CONTR_START_DATE,
       PAI_INFO.ACTION_INFORMATION17 CONTR_END_DATE,
       PAI_INFO.ACTION_INFORMATION18 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 PAI_REG, 
      PAY_ACTION_INFORMATION PAI_INFO,
      PAY_ACTION_INFORMATION PAYEE,
      PAY_ACTION_INFORMATION PAI_DB,
      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_INFO.ASSIGNMENT_ID(+) = PAI_REG.ASSIGNMENT_ID
 AND PAI_INFO.ACTION_INFORMATION1(+) = PAI_REG.ACTION_INFORMATION1
 AND PAI_INFO.ACTION_INFORMATION2(+) = PAI_REG.ACTION_INFORMATION2
 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_DB.ACTION_CONTEXT_ID(+) =  :P_PAYROLL_ACTION_ID
 AND PAI_DB.ASSIGNMENT_ID(+) = PAI_REG.ASSIGNMENT_ID
 AND PAI_DB.ACTION_CONTEXT_TYPE(+) = 'PA'
 AND PAI_DB.ACTION_INFORMATION_CATEGORY(+) = 'AU_SUPER_STREAM_DB_CONTR_INFO'
 AND PAI_DB.ACTION_INFORMATION1(+) = PAI_REG.ACTION_INFORMATION1
 AND PAI_DB.ACTION_INFORMATION2(+) = PAI_REG.ACTION_INFORMATION2
 AND PAI_INFO_DB1.ASSIGNMENT_ID(+) = PAI_REG.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_INFO_DB2.ASSIGNMENT_ID(+) = PAI_REG.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
Download
 
Related Blitz Reports
PAY RTI FPS Run Reconciliation PAY RTI - Full Payment Submission Output 2014/15 PAY RTI - Full Payment Submission Output 2016/17 PAY RTI - Full Payment Submission Output 2017/18 PAY RTI - Full Payment Submission Output 2013/14 PAY RTI - Full Payment Submission Output 2015/16 PAY SuperStream Combined Report Alternative Format PAY RTI - Year End P60 Report Process (Multi Threaded) PAY RTI - Year End P60 Report Process
Blitz Report™

Blitz Report™ provides multiple benefits: