SQLize Online / PHPize Online  /  SQLtest Online

Declare @IPR_Payroll_Code nvarchar(max) Declare @IPR_CurrentPeriod float DECLARE @Option varchar(1) SELECT @IPR_Payroll_Code = '<span>Payroll(s):</span>' /* 'AFGHOLD_2021,AFGSERV_2021,AFHEALTH_2021,AFINDCA_2021,AFINVEST_2021,AFLIFE_2021,AFRETCA_2021' */ SELECT @IPR_CurrentPeriod = '<span>Period:</span>' /* 1 */ SELECT @Option = '<span>File:</span>' /* 'F = File Extract C = Check File */ SELECT 'CONFIG' [CONFIG],'N' [SHOW_COLUMN_HEADINGS],',' [DELIMITER] IF @Option = 'F' BEGIN SELECT 'Group_id,Emp_id,Employment_Type,EMP_NAME,emp_initials,emp_name_first,emp_name_pref,CostCode,ROP1,ROP_Date,ROP2,payroll_run_date,MMMYYYY,Date_Engaged,Grade,TOTAL COST,UNDEFINED,CASH COMPONENT,FIXED DRAW,BONUS OTHER,COMMISSION,GIFT VOUCHER,LEAVE PAY,SKILLS DEVELOPMENT,UIF ER,CAR ALLOWANCE,VAT ON COMPANY CARS,MEDICAL AID,PENSION FUND CTC,PROVIDENT ER,WCA PROVISION,GROUP LIFE,CONSULTANTS FEES,CAR/NEW INCENTIVE,DREAD DISEASE ER,ANTEDATED SALARY,SUBSISTENCE ALLOW,PC ALLOWANCE,STATED BENEFITS,PHI ER,SPOUSE COVER,FUNERAL COVER ER,BONUS PROVISION,RETAINER,OVERTIME,LUMP SUMS,TEMP\ARREARS PAY,OTHER PAYMENTS\ALLOW,FOREIGN SUBSISTANCE,DIRECTORS EMOLUMENTS,FOREIGN INCOME TAX,MATERNITY PAY,INCENTIVE BONUS EXP,OTHER ER CONTRIBUT,HOUSE ALLOWANCE,SIGN ON BONUS,SHARE OPTIONS,SA PAYE CO CONT,VAT,CLUB SUBS,SEVERANCE PAY,SIX MONTH TCC,13TH CHEQUE,AWARDS,SA SALARY,UK SALARY,RELOCATION EXP,IISA,ILPA,SHIFT ALLOWANCE,MED BUY OUT OFFER,WESBANK REFUND,DEFER COMP CTC,REIMBURSIVE EXPENSES,ADDITIONAL COVER GR,SURPLUS CLAIMS,STUDY LOAN,ADMED GAP ER,PAST SERV PENSION,PENSION FUND ER,INCENTIVE BONUS Y/E,CONNECTIVITY REIMBURSEMENT,POWER OF 1' UNION ALL SELECT ltrim(rtrim(case when in02.IPR_Payroll_Code like 'GFWEALTH%' Then 'GF' Else 'AF' End)) /*as [Group_id],*/ +','+ltrim(rtrim(in02.IHR_EmpCode)) /*as [Emp_id],*/ +','+ltrim(rtrim(hn01.IHR_EmployeeTypeCode)) /*as [Employment_Type],*/ +','+ltrim(rtrim(isnull(hn01.IHR_EmpSurname,''))) /*as [EMP_NAME],*/ +','+ltrim(rtrim(isnull(hn01.IHR_EmpInitials,''))) /*as [emp_initials],*/ +','+ltrim(rtrim(isnull(hn01.IHR_EmpName,''))) /*as [emp_name_first],*/ +','+ltrim(rtrim(isnull(hn01.IHR_EmpKnownName,''))) /*as [emp_name_pref],*/ +','+ltrim(rtrim(isnull(hn02.IHR_CostCenter,''))) /*as [CostCode],*/ +','+ltrim(rtrim(cast(round(in02.IPR_Rate_Amount_2,2) as decimal(38,2)))) /*as [ROP1],*/ +','+ltrim(rtrim(isnull(dbo.HRF_FN_Convert_DateTime_To_Text('dd/mm/yyyy', case When coalesce(hn01.IHR_IRP5Date,s.Start_Salary) = '1900/01/01' Then '2020/03/01' Else coalesce(hn01.IHR_IRP5Date,s.Start_Salary) End),''))) /*as [ROP_Date],*/ +','+ltrim(rtrim(cast(round(isnull(in06_ROP2.IPR_Payroll_Total_Amount,0),2) as decimal(38,2)))) /*as [ROP2],*/ +','+ltrim(rtrim(dbo.HRF_FN_Convert_DateTime_To_Text('dd/mm/yyyy',ir69.IPR_Period_End_Date))) /*as [payroll_run_date],*/ +','+ltrim(rtrim(dbo.HRF_FN_Convert_DateTime_To_Text('MMM.yyyy',ir69.IPR_Period_End_Date))) /*as [MMMYYYY],*/ +','+ltrim(rtrim(dbo.HRF_FN_Convert_DateTime_To_text('dd/mm/yyyy',hn01.IHR_EngagementDate))) /*as [Date_Engaged],*/ +','+ltrim(rtrim(isnull(e.Grade_Code,''))) /*as [Grade],*/ +','+ltrim(rtrim(cast(round(isnull(tc.IPR_Value,0),2) as decimal(38,2)))) /*as [TOTAL COST],*/ +','+ltrim(rtrim(cast(round(isnull(tc.IPR_Value,0) - isnull(rie_cc.IPR_Value,0) - isnull(rie_fd.IPR_Value,0) - isnull(rie_bo.IPR_Value,0) - isnull(rie_com.IPR_Value,0) - isnull(rie_lp.IPR_Value,0) - isnull(rib_sdl.IPR_Value,0) - isnull(rib_uifER.IPR_Value,0) - isnull(rie_ca.IPR_Value,0) - isnull(rie_ma.IPR_Value,0) - isnull(rib_provER.IPR_Value,0) - isnull(rib_gl.IPR_Value,0) - isnull(rie_cf.IPR_Value,0) - isnull(rib_ddER.IPR_Value,0) - isnull(rib_PHI.IPR_Value,0) - isnull(rib_sc.IPR_Value,0) - isnull(rib_fcER.IPR_Value,0) - isnull(rie_OT.IPR_Value,0) - isnull(rie_oa.IPR_Value,0) - isnull(rie_de.IPR_Value,0) - isnull(rie_so.IPR_Value,0) - isnull(rie_sap.IPR_Value,0) - isnull(rie_VAT.IPR_Value,0) - isnull(rie_sp.IPR_Value,0) - isnull(rie_dcCTC.IPR_Value,0) - isnull(rie_re.IPR_Value,0) - isnull(rib_sl.IPR_Value,0) - isnull(rib_agER.IPR_Value,0) - isnull(rib_pfER.IPR_Value,0) - isnull(rie_ib.IPR_Value,0) - isnull(rie_cr.IPR_Value,0) - isnull(rie_aw.IPR_Value,0) - isnull(rie_rel.IPR_Value,0) - isnull(rie_sa.IPR_Value,0) - isnull(rie_ibe.IPR_Value,0) - isnull(rie_bp.IPR_Value,0) - isnull(rie_pone.IPR_Value,0) ,2) as decimal(38,2)))) /*as [UNDEFINED],*/ +','+ltrim(rtrim(cast(round(isnull(rie_cc.IPR_Value,0),2) as decimal(38,2)))) /*as [CASH COMPONENT],*/ +','+ltrim(rtrim(cast(round(isnull(rie_fd.IPR_Value,0),2) as decimal(38,2)))) /*as [FIXED DRAW],*/ +','+ltrim(rtrim(cast(round(isnull(rie_bo.IPR_Value,0),2) as decimal(38,2)))) /*as [BONUS OTHER],*/ +','+ltrim(rtrim(cast(round(isnull(rie_com.IPR_Value,0),2) as decimal(38,2)))) /*as [COMMISSION],*/ +','+ltrim(rtrim('0.00')) /*as [GIFT VOUCHER],*/ +','+ltrim(rtrim(cast(round(isnull(rie_lp.IPR_Value,0),2) as decimal(38,2)))) /*as [LEAVE PAY],*/ +','+ltrim(rtrim(cast(round(isnull(rib_sdl.IPR_Value,0),2) as decimal(38,2)))) /*as [SKILLS DEVELOPMENT],*/ +','+ltrim(rtrim(cast(round(isnull(rib_uifER.IPR_Value,0),2) as decimal(38,2)))) /*as [UIF ER],*/ +','+ltrim(rtrim(cast(round(isnull(rie_ca.IPR_Value,0),2) as decimal(38,2)))) /*as [CAR ALLOWANCE],*/ +','+ltrim(rtrim('0.00')) /*as [VAT ON COMPANY CARS],*/ +','+ltrim(rtrim(cast(round(isnull(rie_ma.IPR_Value,0),2) as decimal(38,2)))) /*as [MEDICAL AID],*/ +','+ltrim(rtrim('0.00')) /*as [PENSION FUND CTC],*/ +','+ltrim(rtrim(cast(round(isnull(rib_provER.IPR_Value,0),2) as decimal(38,2)))) /*as [PROVIDENT ER],*/ +','+ltrim(rtrim('')) /*as [WCA PROVISION],*/ +','+ltrim(rtrim(cast(round(isnull(rib_gl.IPR_Value,0),2) as decimal(38,2)))) /*as [GROUP LIFE],*/ +','+ltrim(rtrim(cast(round(isnull(rie_cf.IPR_Value,0),2) as decimal(38,2)))) /*as [CONSULTANTS FEES],*/ +','+ltrim(rtrim('0.00')) /*as [CAR/NEW INCENTIVE],*/ +','+ltrim(rtrim(cast(round(isnull(rib_ddER.IPR_Value,0),2) as decimal(38,2)))) /*as [DREAD DISEASE ER],*/ +','+ltrim(rtrim('0.00')) /*as [ANTEDATED SALARY],*/ +','+ltrim(rtrim(cast(round(isnull(rie_sa.IPR_Value,0),2) as decimal(38,2)))) /*as [SUBSISTENCE ALLOW],*/ +','+ltrim(rtrim('0.00')) /*as [PC ALLOWANCE],*/ +','+ltrim(rtrim('0.00')) /*as [STATED BENEFITS],*/ +','+ltrim(rtrim(cast(round(isnull(rib_PHI.IPR_Value,0),2) as decimal(38,2)))) /*as [PHI ER],*/ +','+ltrim(rtrim(cast(round(isnull(rib_sc.IPR_Value,0),2) as decimal(38,2)))) /*as [SPOUSE COVER],*/ +','+ltrim(rtrim(cast(round(isnull(rib_fcER.IPR_Value,0),2) as decimal(38,2)))) /*as [FUNERAL COVER ER],*/ +','+ltrim(rtrim(cast(round(isnull(rie_bp.IPR_Value,0),2) as decimal(38,2)))) /*as [BONUS PROVISION],*/ +','+ltrim(rtrim('0.00')) /*as [RETAINER],*/ +','+ltrim(rtrim(cast(round(isnull(rie_OT.IPR_Value,0),2) as decimal(38,2)))) /*as [OVERTIME],*/ +','+ltrim(rtrim('0.00')) /*as [LUMP SUMS],*/ +','+ltrim(rtrim('0.00')) /*as [TEMP\ARREARS PAY],*/ +','+ltrim(rtrim(cast(round(isnull(rie_oa.IPR_Value,0),2) as decimal(38,2)))) /*as [OTHER PAYMENTS\ALLOW],*/ +','+ltrim(rtrim('0.00')) /*as [FOREIGN SUBSISTANCE],*/ +','+ltrim(rtrim(cast(round(isnull(rie_de.IPR_Value,0),2) as decimal(38,2)))) /*as [DIRECTORS EMOLUMENTS],*/ +','+ltrim(rtrim('0.00')) /*as [FOREIGN INCOME TAX],*/ +','+ltrim(rtrim('0.00')) /*as [MATERNITY PAY],*/ +','+ltrim(rtrim(cast(round(isnull(rie_ibe.IPR_Value,0),2) as decimal(38,2)))) /*as [INCENTIVE BONUS EXP],*/ +','+ltrim(rtrim('0.00')) /*as [OTHER ER CONTRIBUT],*/ +','+ltrim(rtrim('0.00')) /*as [HOUSE ALLOWANCE],*/ +','+ltrim(rtrim(cast(round(isnull(rie_so.IPR_Value,0),2) as decimal(38,2)))) /*as [SIGN ON BONUS],*/ +','+ltrim(rtrim('0.00')) /*as [SHARE OPTIONS],*/ +','+ltrim(rtrim(cast(round(isnull(rie_sap.IPR_Value,0),2) as decimal(38,2)))) /*as [SA PAYE CO CONT],*/ +','+ltrim(rtrim(cast(round(isnull(rie_VAT.IPR_Value,0),2) as decimal(38,2)))) /*as [VAT],*/ +','+ltrim(rtrim('0.00')) /*as [CLUB SUBS],*/ +','+ltrim(rtrim(cast(round(isnull(rie_sp.IPR_Value,0),2) as decimal(38,2)))) /*as [SEVERANCE PAY],*/ +','+ltrim(rtrim('0.00')) /*as [SIX MONTH TCC],*/ +','+ltrim(rtrim('0.00')) /*as [13TH CHEQUE],*/ +','+ltrim(rtrim(cast(round(isnull(rie_aw.IPR_Value,0),2) as decimal(38,2)))) /*as [AWARDS],*/ +','+ltrim(rtrim('0.00')) /*as [SA SALARY],*/ +','+ltrim(rtrim('0.00')) /*as [UK SALARY],*/ +','+ltrim(rtrim(cast(round(isnull(rie_rel.IPR_Value,0),2) as decimal(38,2)))) /*as [RELOCATION EXP],*/ +','+ltrim(rtrim('0.00')) /*as [IISA],*/ +','+ltrim(rtrim('0.00')) /*as [ILPA],*/ +','+ltrim(rtrim('0.00')) /*as [SHIFT ALLOWANCE],*/ +','+ltrim(rtrim('0.00')) /*as [MED BUY OUT OFFER],*/ +','+ltrim(rtrim('0.00')) /*as [WESBANK REFUND],*/ +','+ltrim(rtrim(cast(round(isnull(rie_dcCTC.IPR_Value,0),2) as decimal(38,2)))) /*as [DEFER COMP CTC],*/ +','+ltrim(rtrim(cast(round(isnull(rie_re.IPR_Value,0),2) as decimal(38,2)))) /*as [REIMBURSIVE EXPENSES],*/ +','+ltrim(rtrim('0.00')) /*as [ADDITIONAL COVER GR],*/ +','+ltrim(rtrim('0.00')) /*as [SURPLUS CLAIMS],*/ +','+ltrim(rtrim(cast(round(isnull(rib_sl.IPR_Value,0),2) as decimal(38,2)))) /*as [STUDY LOAN],*/ +','+ltrim(rtrim(cast(round(isnull(rib_agER.IPR_Value,0),2) as decimal(38,2)))) /*as [ADMED GAP ER],*/ +','+ltrim(rtrim('0.00')) /*as [PAST SERV PENSION],*/ +','+ltrim(rtrim(cast(round(isnull(rib_pfER.IPR_Value,0),2) as decimal(38,2)))) /*as [PENSION FUND ER],*/ +','+ltrim(rtrim(cast(round(isnull(rie_ib.IPR_Value,0),2) as decimal(38,2)))) /*as [INCENTIVE BONUS Y/E]*/ +','+ltrim(rtrim(cast(round(isnull(rie_cr.IPR_Value,0),2) as decimal(38,2)))) /*as [CONNECTIVITY REIMBURSEMENT]*/ +','+ltrim(rtrim(cast(round(isnull(rie_pone.IPR_Value,0),2) as decimal(38,2)))) /*as [POWER OF ONE]*/ as [Result] From IPR_N002 in02 with (NoLock) Inner Join IHR_N001 hn01 with (NoLock) on hn01.IHR_EmpCode = in02.IHR_EmpCode Inner Join IHR_N002 hn02 with (NoLock) on hn02.IHR_EmpCode = hn01.IHR_EmpCode Inner Join IPR_R069 ir69 with (NoLock) on ir69.IPR_Payroll_Code = in02.IPR_Payroll_Code and ir69.IPR_CurrentPeriod = in02.IPR_CurrentPeriod Left Join IPR_N006 in06_ROP2 with (NoLock) on in06_ROP2.IPR_Payroll_Code = in02.IPR_Payroll_Code and in06_ROP2.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and in06_ROP2.IHR_EmpCode = in02.IHR_EmpCode and in06_ROP2.IPR_Position_Number = 28 Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 60 /* E (060) Disability Income */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 62 /* E (062) Temp Pay */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 63 /* E (063) Fixed Term Temp Pay */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 64 /* E (064) Learner Temp Pay */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 65 /* E (065) Intern Temp Pay */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 66 /* E (066) Deferred Retirement Temp */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 71 /* E (071) Cash Component */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 114 /* E (114) Salary Adjustment */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 124 /* E (124) Unpaid Leave */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 67 /* Independent Contractors */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 117 /* Maternity Pay Adjusted */ UNION ALL /* Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 124 /* Unpaid Leave */ -- Removed 2024-01-16; DUPLICATED UNION ALL */ Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 100 /* Unpaid Sick Leave */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 7 /* Disability */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_cc on rie_cc.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_cc.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_cc.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Earnings rie_fd with (NoLock) on rie_fd.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_fd.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_fd.IHR_EmpCode = in02.IHR_EmpCode and rie_fd.IPR_Position_Number = 61 /* E (061) Fixed Draw */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 127 /* E (127) Bonus Other */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 105 /* E (105) Referral Bonus */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 106 /* E (106) Exam Bonus */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 96 /* Restraint Of Trade */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 79 /* Settling In Allow - Taxab */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_bo on rie_bo.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_bo.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_bo.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 76 /* E (076) Commission */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 77 /* E (077) Lead Commission */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 78 /* E (078) Lead Commission FPC */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode )rie_com on rie_com.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_com.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_com.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 90 /* E (090) Leave Pay */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 128 /* E (128) Leave Pay Retrenchment */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_lp on rie_lp.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_lp.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_lp.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Benefits rib_sdl with (NoLock) on rib_sdl.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_sdl.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_sdl.IHR_EmpCode = in02.IHR_EmpCode and rib_sdl.IPR_Position_Number = 1 Left Join Report_IPR_Benefits rib_UIFER with (NoLock) on rib_UIFER.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_UIFER.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_UIFER.IHR_EmpCode = in02.IHR_EmpCode and rib_UIFER.IPR_Position_Number = 2 Left Join Report_IPR_Earnings rie_ca with (NoLock) on rie_ca.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_ca.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_ca.IHR_EmpCode = in02.IHR_EmpCode and rie_ca.IPR_Position_Number = 29 /* E (029) Car Allowance */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 37 /* Bonitas Advised */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 35 /* Discovery Advised */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_ma on rie_ma.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_ma.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_ma.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Benefits rib_ProvER with (NoLock) on rib_ProvER.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_ProvER.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_ProvER.IHR_EmpCode = in02.IHR_EmpCode and rib_ProvER.IPR_Position_Number = 12 Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 20 /* Group Life Pension */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 21 /* Group Life Provident */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 22 /* Group Life Supplementary */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rib_gl on rib_gl.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_gl.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_gl.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Earnings rie_cf with (NoLock) on rie_cf.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_cf.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_cf.IHR_EmpCode = in02.IHR_EmpCode and rie_cf.IPR_Position_Number = 125 /* E (125) Trustee Pay */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 14 /* Dreaded Disease Pension */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 15 /* Dreaded Disease Provident */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 16 /* Dreaded Disease Supplemen */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rib_dder on rib_dder.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_dder.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_dder.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Benefits rib_PHI with (NoLock) on rib_PHI.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_PHI.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_PHI.IHR_EmpCode = in02.IHR_EmpCode and rib_PHI.IPR_Position_Number = 26 /* Permanent Health Insuranc */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 28 /* Spouses Cover */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 34 /* Spouses Cover Based On Ag */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rib_sc on rib_sc.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_sc.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_sc.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Benefits rib_fcER with (NoLock) on rib_fcER.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_fcER.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_fcER.IHR_EmpCode = in02.IHR_EmpCode and rib_fcER.IPR_Position_Number = 19 /* Funeral Cover */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 2 /* E (002) Overtime 1.5 */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 3 /* E (003) Overtime 2.0 */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 92 /* E (092) Overtime Advised Amount */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_OT on rie_OT.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_OT.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_OT.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 91 /* E (091) 3G Allowance */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 93 /* E (093) Other Allow Taxable */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 113 /* Sundry Payments */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_oa on rie_oa.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_oa.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_oa.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 68 /* E (068) Ned Fees Excl VAT */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 70 /* E (070) Ned Fees Taxable */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 73 /* E (073) Ned Res Vat Fee */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 72 /* Ned Nonres Vat Fee */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 69 /* Ned Non Resident Excl vat */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_de on rie_de.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_de.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_de.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Earnings rie_so with (NoLock) on rie_so.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_so.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_so.IHR_EmpCode = in02.IHR_EmpCode and rie_so.IPR_Position_Number = 123 /* E (123) Sign On Bonus */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 126 /* E (126) Sa Paye Company Tax Power */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 81 /* Share Options Tax Directi */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_sap on rie_sap.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_sap.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_sap.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Earnings rie_VAT with (NoLock) on rie_VAT.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_VAT.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_VAT.IHR_EmpCode = in02.IHR_EmpCode and rie_VAT.IPR_Position_Number = 101 /* VAT */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 88 /* E (088) Severance Py Undr 55 */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 86 /* Arbitration Tx Directive */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 85 /* Arbitration Taxed Directi */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 84 /* Ex Gratia Payment */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 82 /* Ex Gratia Taxed Directive */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 83 /* Ex Gratia Tx Directive */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 89 /* Notice Pay */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 87 /* Severance Py Over 55 */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_sp on rie_sp.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_sp.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_sp.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Earnings rie_dcCTC with (NoLock) on rie_dcCTC.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_dcCTC.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_dcCTC.IHR_EmpCode = in02.IHR_EmpCode and rie_dcCTC.IPR_Position_Number = 112 /* Def Comp Nonretire Tax */ Left Join Report_IPR_Earnings rie_re with (NoLock) on rie_re.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_re.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_re.IHR_EmpCode = in02.IHR_EmpCode and rie_re.IPR_Position_Number = 116 /* E (116) Travel Kms Reimbursement */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 30 /* Studt Debt Employer Pays */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 31 /* Study Debt Employer Pays */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 29 /* Study Loan F/B */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 32 /* Study Material F/B */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rib_sl on rib_sl.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_sl.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_sl.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 10 /* Admed Primary */ UNION ALL Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 11 /* Admed Supreme */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rib_agER on rib_agER.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_agER.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_agER.IHR_EmpCode = in02.IHR_EmpCode Left Join Report_IPR_Benefits rib_pfER with (NoLock) on rib_pfER.IPR_Payroll_Code = in02.IPR_Payroll_Code and rib_pfER.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rib_pfER.IHR_EmpCode = in02.IHR_EmpCode and rib_pfER.IPR_Position_Number = 25 /* Pension Fund */ Left Join Report_IPR_Earnings rie_ib with (NoLock) on rie_ib.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_ib.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_ib.IHR_EmpCode = in02.IHR_EmpCode and rie_ib.IPR_Position_Number = 80 /* STI-Short Term Incentive */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * FRom Report_IPR_Earnings with (NoLock) UNION ALL Select * FRom Report_IPR_Benefits with (NoLock) ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) tc on tc.IPR_Payroll_Code = in02.IPR_Payroll_Code and tc.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and tc.IHR_EmpCode = in02.IHR_EmpCode Left Join Employee e with (NoLock) on e.Emp_No = in02.IHR_EmpCode Left Join Salary s with (NoLock) on s.Comp_Code = e.Comp_Code and s.Emp_No = e.Emp_No Left Join Report_IPR_Earnings rie_cr with (NoLock) on rie_cr.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_cr.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_cr.IHR_EmpCode = in02.IHR_EmpCode and rie_cr.IPR_Position_Number = 137 /* E (137) Connectivity Reimbursement */ Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 108 /* E (108) Long Service Awards */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 118 /* E (118) Once Off Reward */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_aw on rie_aw.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_aw.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_aw.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 95 /* Relocation Fees Taxable */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_rel on rie_rel.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_rel.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_rel.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 98 /* Subsistence */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_sa on rie_sa.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_sa.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_sa.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 107 /* E (107) Incentive Bonus */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_ibe on rie_ibe.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_ibe.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_ibe.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 111 /* Retention Bonus */ UNION ALL Select * From Report_IPR_Earnings with (NoLock) Where IPR_Position_Number = 97 /* E (097) Retainer */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_bp on rie_bp.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_bp.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_bp.IHR_EmpCode = in02.IHR_EmpCode Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * From Report_IPR_Benefits with (NoLock) Where IPR_Position_Number = 36 /* B (036) Power of 1 */ ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) rie_pone on rie_pone.IPR_Payroll_Code = in02.IPR_Payroll_Code and rie_pone.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and rie_pone.IHR_EmpCode = in02.IHR_EmpCode Where in02.IPR_Payroll_Code in (Select * From dbo.HRF_FN_CSV_TO_TXT(@IPR_Payroll_Code)) AND in02.IPR_CurrentPeriod = @IPR_CurrentPeriod /* --JvdW 2020-07-21 removed-- and in02.IPR_M_Earnings_Total <> 0 */ -- Order by in02.IPR_Payroll_Code,in02.IHR_EmpCode END ELSE BEGIN SELECT 'ER,Employee Number,Amt' UNION ALL SELECT ltrim(rtrim(in02.IPR_Payroll_Code)) -- as [ER], +','+ltrim(rtrim(in02.IHR_EmpCode)) -- as [Employee Number], +','+ltrim(rtrim(cast(round(isnull(tc.IPR_Value,0),2) as decimal(38,2)))) -- as [Amt] From IPR_N002 in02 with (NoLock) Left Join ( Select IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode,sum(isnull(IPR_Value,0)) as [IPR_Value] From ( Select * FRom Report_IPR_Earnings with (NoLock) UNION ALL Select * FRom Report_IPR_Benefits with (NoLock) ) A Group By IPR_Payroll_Code,IPR_CurrentPeriod,IHR_EmpCode ) tc on tc.IPR_Payroll_Code = in02.IPR_Payroll_Code and tc.IPR_CurrentPeriod = in02.IPR_CurrentPeriod and tc.IHR_EmpCode = in02.IHR_EmpCode Where in02.IPR_Payroll_Code in (Select * From dbo.HRF_FN_CSV_TO_TXT(@IPR_Payroll_Code)) AND in02.IPR_CurrentPeriod = @IPR_CurrentPeriod /* --JvdW 2020-07-21 removed-- and in02.IPR_M_Earnings_Total <> 0 */ END