PAY NZ Generate EI Amended

Description
Categories: BI Publisher
Imported from BI Publisher
Description: The eText output of NZ Payday Report - Amended
Application: Payroll
Source: NZ Generate EI Amended Report
Short Name: PYNZEIAM_XML
DB package:
Run PAY NZ Generate EI Amended and other Oracle EBS reports with Blitz Report™ on our demo environment
select 
      'DTI' DETAIL_TYPE
      ,old.X_PEI_EMP_IRD_NO
      ,old.X_PEI_EMP_NAME
      ,old.X_PEI_EMP_TAX_CODE
      ,old.X_PEI_EMP_START_DATE
      ,old.X_PEI_EMP_FINISH_DATE
      ,old.X_PEI_EMP_PERIOD_START_DATE
      ,old.X_PEI_EMP_PERIOD_END_DATE
      ,old.X_PEI_EMP_PAY_CYCLE
      ,decode(sign(old.X_PEI_EMP_GROSS_EARNINGS - old.X_PEI_EMP_ESS_EARNINGS), -1, 0, (old.X_PEI_EMP_GROSS_EARNINGS - old.X_PEI_EMP_ESS_EARNINGS)) GROSS_EARNINGS 
      ,decode(sign(old.X_PEI_EMP_EARNINGS_NOT_LIABLE - old.X_PEI_EMP_ESS_EARNINGS), -1, 0,  (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
      ,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 new.X_PEI_ARC_REPORT_FLAG = 1 
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
union all
select 
      'DAI' DETAIL_TYPE
      ,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
      ,decode(sign(X_PEI_EMP_GROSS_EARNINGS - X_PEI_EMP_ESS_EARNINGS), -1, 0, (X_PEI_EMP_GROSS_EARNINGS - X_PEI_EMP_ESS_EARNINGS)) GROSS_EARNINGS 
      ,decode(sign(X_PEI_EMP_EARNINGS_NOT_LIABLE - X_PEI_EMP_ESS_EARNINGS), -1, 0,  (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
      ,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 X_PEI_ARC_REPORT_FLAG = 1 
and :P_DTI_TYPE in ('U','UN')
and X_PEI_ARC_AMEND_PAYE_IND is not null
and payroll_action_id = :P_PAYROLL_ACTION_ID
order by assignment_id, assignment_action_id
Blitz Report™