PAY NZ Payday Reconciliation

Description
Categories: BI Publisher
Imported from BI Publisher
Description: NZ Reconciliation report spawned by NZ Payday Report
Application: Payroll
Source: NZ Payday Reconciliation Report
Short Name: PYNZEIVALID_XML
DB package:

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 
       nvl(old.X_PEI_EMP_IRD_NO, new.X_PEI_EMP_IRD_NO) X_PEI_EMP_IRD_NO
      ,nvl(old.X_PEI_EMP_NAME, new.X_PEI_EMP_NAME) X_PEI_EMP_NAME
      ,nvl(old.X_PEI_EMP_TAX_CODE, new.X_PEI_EMP_TAX_CODE) X_PEI_EMP_TAX_CODE
      ,nvl(old.X_PEI_EMP_START_DATE, new.X_PEI_EMP_START_DATE) X_PEI_EMP_START_DATE
      ,nvl(old.X_PEI_EMP_FINISH_DATE, new.X_PEI_EMP_FINISH_DATE) X_PEI_EMP_FINISH_DATE
      ,nvl(old.X_PEI_EMP_PERIOD_START_DATE, new.X_PEI_EMP_PERIOD_START_DATE) X_PEI_EMP_PERIOD_START_DATE
      ,nvl(old.X_PEI_EMP_PERIOD_END_DATE, new.X_PEI_EMP_PERIOD_END_DATE) X_PEI_EMP_PERIOD_END_DATE
      ,nvl(old.X_PEI_EMP_PAY_CYCLE, new.X_PEI_EMP_PAY_CYCLE) X_PEI_EMP_PAY_CYCLE
      ,(old.X_PEI_EMP_GROSS_EARNINGS - old.X_PEI_EMP_ESS_EARNINGS) GROSS_EARNINGS 
      ,(old.X_PEI_EMP_EARNINGS_NOT_LIABLE - old.X_PEI_EMP_ESS_EARNINGS) EARNINGS_NOT_LIABLE
      ,old.X_PEI_EMP_LUMP_SUM_INDICATOR
      ,old.X_PEI_EMP_PAYE_DEDUCTIONS
      ,old.X_PEI_EMP_CS_DEDUCTIONS
      ,old.X_PEI_EMP_CS_CODE
      ,old.X_PEI_EMP_SL_DEDUTIONS
      ,old.X_PEI_EMP_KS_DEDUCTIONS
      ,old.X_PEI_EMP_KS_ER_DEDUCTIONS
      ,old.X_PEI_EMP_ESCT_DEDUCTED
      ,old.X_PEI_EMP_TAX_CREDITS
      ,new.X_PEI_ARC_REPORT_FLAG
      ,'O' REPORT_TYPE
      ,old.assignment_id
      ,old.assignment_action_id
from pay_nz_pei_archive_v old, pay_nz_pei_archive_v new
where old.ASSIGNMENT_ACTION_ID  = new.X_PEI_ARC_AMEND_ORIG_ACT_ID
and new.X_PEI_ARC_TYPE = 'A'
and new.X_PEI_ARC_AMEND_TYPE is not null
and:P_DTI_TYPE ='UN'
and new.X_PEI_ARC_AMEND_PAYE_IND is not null
and new.payroll_action_id = :P_PAYROLL_ACTION_ID
AND :P_VALID_TYPE = 'AMEND'
union all
select 
       X_PEI_EMP_IRD_NO
      ,X_PEI_EMP_NAME
      ,X_PEI_EMP_TAX_CODE
      ,X_PEI_EMP_START_DATE
      ,X_PEI_EMP_FINISH_DATE
      ,X_PEI_EMP_PERIOD_START_DATE
      ,X_PEI_EMP_PERIOD_END_DATE
      ,X_PEI_EMP_PAY_CYCLE
      ,(X_PEI_EMP_GROSS_EARNINGS - X_PEI_EMP_ESS_EARNINGS) GROSS_EARNINGS 
      ,(X_PEI_EMP_EARNINGS_NOT_LIABLE - X_PEI_EMP_ESS_EARNINGS) EARNINGS_NOT_LIABLE
      ,X_PEI_EMP_LUMP_SUM_INDICATOR
      ,X_PEI_EMP_PAYE_DEDUCTIONS
      ,X_PEI_EMP_CS_DEDUCTIONS
      ,X_PEI_EMP_CS_CODE
      ,X_PEI_EMP_SL_DEDUTIONS
      ,X_PEI_EMP_KS_DEDUCTIONS
      ,X_PEI_EMP_KS_ER_DEDUCTIONS
      ,X_PEI_EMP_ESCT_DEDUCTED
      ,X_PEI_EMP_TAX_CREDITS
      ,X_PEI_ARC_REPORT_FLAG
      ,'A' REPORT_TYPE
      ,assignment_id
      ,assignment_action_id
from pay_nz_pei_archive_v
where X_PEI_ARC_TYPE = 'A'
and X_PEI_ARC_AMEND_TYPE is not null
and:P_DTI_TYPE ='UN'
and X_PEI_ARC_AMEND_PAYE_IND is not null
and payroll_action_id = :P_PAYROLL_ACTION_ID
AND :P_VALID_TYPE = 'AMEND'
order by assignment_id, assignment_action_id