!******************************************************************** !******************************************************************** #include 'setenv.sqc' !Set environment !#include 'setup32.sqc' !

Printer and page-size initialization Begin-Setup End-Setup begin-program do Init-Report ! do Report do Break-File if $Trigger='Y' do create-trigger-file end-if do sftp-file do Stdapi-Term end-program begin-procedure Init-Report move 'HPY213I' to $ReportID move 'Payroll G/L Interface' to $ReportTitle display $ReportTitle do Init-DateTime do Init-Number do Get-Current-DateTime do Stdapi-Init if $prcs_process_instance = ' ' display ' ' display 'ERROR - This program must be run via the Process Scheduler.' display ' ' stop else do Select-RunCntl-Parameters end-if end-procedure Init-Report !********************************************************* !Select parameters from runcontrol record! !********************************************************* begin-procedure Select-RunCntl-Parameters let $Trigger='N' let $Run_Id=' ' Let #filenum = 9 begin-select ! RC.RUN_ID &run_id RC.COUNTRY &country RC.FROMDATE &fromdate RC.THRUDATE &thruddate RC.H_TIME_RANGE &timerange !********************************************************* !get the list of run ids select! !********************************************************* do Get-Values if isblank ($Slctrunid) display 'All Runid selected'

let $runid_list = ' ' else Let $runid_list='''' || $Slctrunid End-if FROM PS_H_PY_RUN_GL RC WHERE RC.OPRID= $Prcs_OprID AND RC.RUN_CNTL_ID = $Prcs_Run_Cntl_ID End-Select if $runid_list <> ' ' let $runid_list = 'AND A.RUN_ID IN (' || $runid_list || ')' end-if let $Cmp_list=' ' let $Pygrp_list=' ' let $hfmenty_list=' ' let $Fincocd = ' ' begin-select AM.COMPANY &run_cmpny AM.PAYGROUP &run_pygrp AM.H_HFM_ENTY &run_hfmenty AM.H_RUN_ENTCD &run_fincocd !********************************************************* !Get companies entered on run control page! !********************************************************* if not isblank(&run_cmpny) if $Cmp_list=' ' let $Cmp_list = '''' || &run_cmpny else let $Cmp_list = $Cmp_list || ',' || &run_cmpny end-if end-if !********************************************************* !Get the list of paygroups! !********************************************************* if not isblank(&run_pygrp) if $Pygrp_list=' ' let $Pygrp_list = '''' || &run_pygrp else let $Pygrp_list =$Pygrp_list || ',' || &run_pygrp end-if end-if !********************************************************* !get the list of hfm entities! !********************************************************* If not isblank(&run_hfmenty) If $hfmenty_list = '' Let $hfmenty_list = '''' || &run_hfmenty Else let $hfmenty_list = $hfmenty_list || ''',''' || &run_hfmenty End-if End-if If not isblank(&run_fincocd) If $Fincocd_List = '' Let $Fincocd_List = '''' || &run_fincocd Else Let $Fincocd_List = $Fincocd_List || ''',''' || &run_fincocd End-if End-if If (&run_hfmenty = 206240 ) or (&run_hfmenty = 206235 )

let $Trigger='Y' end-if !********************************************************* !get the list of financial codes from run control page! !********************************************************* let $Fincocd_List = $Fincocd || ',' || &run_fincocd || ',' || ',' from PS_H_PY_RUN_GLOPT AM where AM.OPRID=$Prcs_OprID and AM.RUN_CNTL_ID=$Prcs_Run_Cntl_ID end-select if isblank ($Cmp_list)! <> ' ' display ' All Cmp_list selected' let $Cmp_list = '' Else let $Cmp_list = ' AND A.COMPANY IN (' || $Cmp_list || ')' end-if if isblank ($Pygrp_list)! <> ' ' display ' All Pygrp_list selected' let $Pygrp_list = '' Else let $Pygrp_list = 'AND A.PAYGROUP IN (' || $Pygrp_list || ')' end-if if isblank ($hfmenty_list)! <> ' ' display ' All hfmenty_list selected' let $hfmenty_list = '' Else Let $hfmenty_list_B4 ='AND B4.H_HFM_ENTY IN (' || $hfmenty_list || ''')' Let $hfmenty_list_L4 ='AND L4.H_HFM_ENTY IN (' || $hfmenty_list || ''')' Let $hfmenty_list_L6 ='AND L6.H_HFM_ENTY IN (' || $hfmenty_list || ''')' Let $hfmenty_list_P14 ='AND P14.H_HFM_ENTY IN (' || $hfmenty_list || ''')' Let $hfmenty_list_P4 ='AND P4.H_HFM_ENTY IN (' || $hfmenty_list || ''')' Let $hfmenty_list_B4 ='AND B4.H_HFM_ENTY IN (' || $hfmenty_list || ''')' Let $hfmenty_list_J6 ='AND J6.H_HFM_ENTY IN (' || $hfmenty_list || ''')' End-if if isblank ($Fincocd_List)! <> ' ' display ' All Fincocd_List selected' let $Fincocd_List = '' Else Let $Fincocd_List_B4 ='AND B4.H_ENTITYCD IN Let $Fincocd_List_L4 ='AND L4.H_ENTITYCD IN Let $Fincocd_List_L6 ='AND L6.H_ENTITYCD IN Let Let Let Let $Fincocd_List_P4 ='AND P4.H_ENTITYCD IN $Fincocd_List_P14 ='AND P14.H_ENTITYCD IN $Fincocd_List_J6 ='AND J6.H_ENTITYCD IN $Fincocd_List_B4 ='AND B4.H_ENTITYCD IN

(' (' (' (' (' (' (' ('

|| $Fincocd_List || ''')' || $Fincocd_List || ''')' || $Fincocd_List || ''')' || || || || $Fincocd_List $Fincocd_List $Fincocd_List $Fincocd_List || || || || ''')' ''')' ''')' ''')'

Let $Fincocd_List ='AND AM.H_ENTITYCD IN End-if

|| $Fincocd_List || ''')'

if &timerange = 'R' let $wher=$runid_list else Let $wher = 'AND A.CHECK_DT between ' || '''' || &fromdate || '''' || ' AND ' || '''' || &thrudate || '''' end-if

do Get-paycalendar end-procedure Select-RunCntl-Parameters begin-procedure Get-Values Do Get-Run-Control-List ( $Prcs_OprID $Prcs_Run_Cntl_ID 'PS_H_PY_HPYRUNID' 'RUN_ID' $Slctrunid ) end-procedure Get-Values Begin-Procedure Get-Run-Control-List ( $Prcs_OprID $Prcs_Run_Cntl_ID $Table_Name $Column_Name :$SlctList ) Let $SlctList = '' BEGIN-SELECT OPRID RUN_CNTL_ID [$Column_Name] &Value=char if NOT isblank(&Value) if $SlctList = '' let $SlctList = '''' || &Value else let $SlctList = $SlctList || ''',''' || &Value End-If End-If FROM [$Table_Name] WHERE OPRID = $Prcs_OprID AND RUN_CNTL_ID = $Prcs_Run_Cntl_ID END-SELECT if $SlctList <> '' let $SlctList = $SlctList || '''' End-If End-Procedure Get-Run-Control-List !************************************************************************** !Get the data from pay calendar based on the input parameters! !************************************************************************** begin-procedure Get-paycalendar let #filenum ='9' Begin-Procedure Get-paycalendar ! Let #filenum = 9 Begin-Select A.Company A.Paygroup A.Pay_end_dt B.Currency_Cd A.Check_Dt A.Pay_Period Let $Company=&A.Company

$Paygroup = &A.paygroup $Pay_Period=&A.payperiod $pay_end_dt=&A.payenddt $Check_Dt=&A.Check_Dt $CURRENCY_CD=&B.CURRENCY_CD do Prcoess-Earnings do Process-Deductions if $Country='USA' do Process-Taxes-USA else do Process-Taxes-CAN end-if do Process-NetChk do Process-Deposit From PS_PAY_CALENDAR A,PS_PAYGROUP_TBL B WHERE A. PAY_CONFIRM_RUN = 'Y' AND A.PAYGROUP=B.PAYGROUP AND A.COMPANY=B.COMPANY AND B.EFFDT=(SELECT MAX(EFFDT) FROM PS_PAYGROUP_TBL BD WHERE BD.COMPANY=B.COMPANY AND BD.PAYGROUP=B.PAYGROUP AN D BD.EFFDT<=SYSDATE) [$Cmp_list] [$Pygrp_list] [$wher] End-Select End-Procedure Get-paycalendar begin-procedure Prcoess-Earnings do Intialization if isblank ($hfmenty_list_B4) display ' All Entity selected' let $hfmenty_list_B4 = '' else display ' Specified Entity:' $hfmenty_list_B4 End-If if isblank ($Fincocd_List_B4) display ' All Fincocd selected' let $Fincocd_List_B4 = '' else display ' Specified Fincocd:' $Fincocd_List_B4 End-If let $pay_type = '1' BEGIN-SELECT B1.OFF_CYCLE B1.PAGE_NUM B1.LINE_NUM B1.ADDL_NBR B1.SEPCHK B1.ERNCD_REG_HRS B1.REG_HRLY_EARNS B1.ERNCD_OT_HRS B1.OT_HRLY_EARNS B1.ERNCD_REG_EARNS B1.REG_EARNS B1.DEPTID B4.H_ENTITYCD B4.HFM_ENTY

Let Let Let Let LET

&hjkhrs

&B4.H_HFM_ENTY

B4.H_FIN_TIER B4.H_PAY_CODE B1.OT_HRS B1.REG_HRS B1.LOCALITY

&B4.H_FIN_TIER &B4.H_PAY_CODE

let $LOCALITY=&b1.locality let $H_HFM_ENTY=&B4.h_HFM_ENTY ! Added the hfm entity flag if no value is entered. If ($H_HFM_ENTY = '206240') OR ($H_HFM_ENTY = '206235') Let $Trigger= 'Y' End-if !Ended the hfm entity flag if no value is entered. LET $H_FIN_TIER=&B4.H_FIN_TIER LET $H_PAY_CODE=&B4.H_PAY_CODE LET $H_ENTITYCD=&B4.H_ENTITYCD LET $DEPTID=&B1.DEPTID LET $TAX_CLASS_SEL=' ' LET $TAX_CLASS_SXS=' ' LET #Amount=0 move &B1.REG_HRLY_EARNS to #Amount if #Amount <> 0 let $EdtCd= &hjkhrs move &b1.REG_EARN_HRS to #hours do Get-Ern-GLcode do Insert-GL LET #Amount=0 end-if Let $ErnCd1=$ErnCd move &B1.OT_HRLY_EARNS if #Amount <> 0 to #Amount

move &B1.ERNCD_REG_HRS to $EdtCd move &b1.OT_HRS to #hours do Get-Ern-GLcode do Insert-GL LET #Amount=0 end-if Let $ErnCd2=$ErnCd move &B1.REG_HRLY_EARNS to #Amount if #Amount <> 0 move &B1.ERNCD_REG_EARNS to $EdtCd move &b1.REG_HRS to #hours do Get-Ern-GLcode do Insert-GL LET #Amount=0 end-if Do Get-Other-Earnings from PS_PAY_EARNINGS B1,

PS_PAY_CHECK B2, PS_JOB B3, PS_H_HR_DP_FIN_TBL B4 where B1.COMPANY = $Company and B1.PAYGROUP = $PayGroup and B1.PAY_END_DT = $PayEndDt and B1.PAY_LINE_STATUS = 'F' and B1.OK_TO_PAY = 'Y' and B2.COMPANY = B1.COMPANY and B2.PAYGROUP = B1.PAYGROUP and B2.PAY_END_DT = B1.PAY_END_DT and B2.OFF_CYCLE = B1.OFF_CYCLE and B2.PAGE_NUM = B1.PAGE_NUM and B2.LINE_NUM = B1.LINE_NUM and B2.SEPCHK = B1.SEPCHK and B2.PAYCHECK_STATUS IN ( F , R , A ) and B2.PAYCHECK_OPTION IN ( A , C , R ) and B3.emplid = B2.emplid and B3.EFFDT = (select MAX(B31.EFFDT) from PS_JOB B31 where B31.EMPLID = B3.EMPLID and B31.EMPL_RCD = B3.EMPL_RCD and B31.EFFDT <= $PayEndDt) and B3.EFFSEQ = (select MAX(B32.EFFSEQ) from PS_JOB B32 where B32.EMPLID = B3.EMPLID and B32.EMPL_RCD = B3.EMPL_RCD and B32.EFFDT = B3.EFFDT) and B3.SETID_DEPT=B4.SETID and B3.DEPTID=B4.DEPTID and B4.EFFDT = (SELECT MAX(B41.EFFDT) FROM PS_H_HR_DP_FIN_TBL B41 WHERE B4.SETI D=B41.SETID AND B41.DEPTID=B4.DEPTID AND B41.EFFDT<=$PayEndDt) [$hfmenty_list_B4] [$Fincocd_List_B4] END-SELECT End-Procedure Prcoess-Earnings !******************************************************************* !Get the other earns! !******************************************************************* begin-procedure Get-Other-Earnings begin-select D.ERNCD D.OTH_EARNS D.OTH_HRS move &D.ErnCd to $EDTCd move &D.Oth_Earns to #Amount MOVE &D.OTH_HRS TO #hours do Get-Ern-GLcode do Insert-GL-Earnings FROM PS_PAY_OTH_EARNS D WHERE D.COMPANY = $Company AND D.PAYGROUP = $PayGroup AND D.PAY_END_DT = $PayEndDt

AND D.OFF_CYCLE = &B1.Off_Cycle AND D.PAGE_NUM = &B1.PAGE_NUM AND D.LINE_NUM = &B1.LINE_NUM AND D.ADDL_NBR = &B1.ADDL_NBR AND D.SEPCHK = &B1.SepChk AND D.ADD_GROSS = ' Y AND D.OTH_EARNS <> 0 end-SELECT end-procedure Get-Other-Earnings !********************************************************************* !********************************************************************* begin-procedure Get-Ern-GLcode Let $Exp_Lia = ' ' BEGIN-SELECT DI.ACCT_CD Let $ EXP_LIA = & DI.ACCT_CD FROM PS_PAY_NA_ERN_DIST DI WHERE DI.COMPANY=$Company And DI.PAYGROUP=$paygroup AND DI.PAY_END_DT = $PayEndDt AND DI.OFF_CYCLE = &B1.Off_Cycle AND DI.PAGE_NUM = &B1.PAGE_NUM AND DI.LINE_NUM = &B1.LINE_NUM AND DI.ADDL_NBR = &B1.ADDL_NBR AND DI.SEPCHK = &B1.SepChk AND DI.ERNCD=$ErnCd END-SELECT end-procedure Get-Ern-GLcode !********************************************************************** !Processing pay Deductions! !********************************************************************** Begin-Procedure Process-Deductions Do Intialization Let $quote = ''''

Let $pay_type = 2 Let $class=$quote || A || $quote || , || $quote || B || $quote || , || $quote || $quo || , || $quote || N || $quote || , || $quote || P || $quote || , || $quote || T If $country= CAN Let $class=$quote || A || $quote || , || B || $quote || , || $quote || L || $quote quote || N || $quote || , || $quote || P || $quote || , || $quote || T || $quote End-if Let $dedclass= AND J1.DED_CALSS IN ( || $class || if isblank ($hfmenty_list_J6) display ' All Entity selected' let $hfmenty_list_J6 = '' else display 'Specified Entity:' $hfmenty_list_J6 End-If if isblank ($Fincocd_List_J6) display ' All Fincocd selected' let $Fincocd_List_J6 = '' else display 'Specified Fincocd:' $Fincocd_List_J6 End-If )

begin-select J1.OFF_CYCLE J1.PAGE_NUM J1.LINE_NUM J1.SEPCHK J1.DED_CUR J1.PLAN_TYPE J1.DEDCD J1.DED_CLASS J1.DED_SLSTX_CLASS J1.BENEFIT_RCD_NBR J1.BENEFIT_PLAN J3.DEPTID &dep J6.H_ENTITYCD &cd J6.HFM_ENTY &enty J6.H_FIN_TIER &tier J6.H_PAY_CODE &pycd move &J1.DED_SLSTX_CLASS to $Ded_SlsTx_Class LET $TAX_CLASS_SEL=$Ded_Class LET $TAX_CLASS_SXS=$Ded_SlsTx_Class do get-ded-GLcd if #Amount <> 0 do Insert-GL end-if from PS_PAY_DEDUCTION J1, PS_PAY_CHECK J3, PS_JOB J4, PS_H_HR_DP_FIN_TBL J6 where J1.COMPANY = $Company and J1.PAYGROUP = $PayGroup and J1.PAY_END_DT = $PayEndDt [$dedclass] and J3.COMPANY = J1.COMPANY and J3.PAYGROUP = J1.PAYGROUP and J3.PAY_END_DT = J1.PAY_END_DT and J3.OFF_CYCLE = J1.OFF_CYCLE and J3.PAGE_NUM = J1.PAGE_NUM and J3.LINE_NUM = J1.LINE_NUM and J3.SEPCHK = J1.SEPCHK and J3.PAYCHECK_STATUS in ('F','R','A') and J3.PAYCHECK_OPTION in ('A','C','R') and J4.EMPLID = J3.EMPLID and J4.EMPL_RCD = J3.EMPL_RCD and J4.EFFDT = (select MAX(J41.EFFDT) from PS_JOB J41 where J41.EMPLID = J4.EMPLID and J41.EMPL_RCD = J4.EMPL_RCD and J41.EFFDT <= $PayEndDt) and J4.EFFSEQ = (select MAX(J42.EFFSEQ) from PS_JOB J42 where J42.EMPLID = J4.EMPLID and J42.EMPL_RCD = J4.EMPL_RCD and J42.EFFDT = J4.EFFDT) and J4.SETID_DEPT=J6.SETID and J4.DEPTID=J6.DEPTID

and J6.EFFDT = (select MAX(J61.EFFDT) from PS_H_HR_DP_FIN_TBL J61 where J6.SET ID=J61.SETID and J61.DEPTID=J6.DEPTID and J61.EFFDT<=$PayEndDt) [$hfmenty_list_J6] [$Fincocd_List_J6] end-select end-procedure Process-Deductions Begin-Procedure Get-ded-GLcd LET $EXP_LIA=' ' BEGIN-SELECT EF.ACCT_CD Let $Exp_Lia= &EF.ACCT_CD FROM PS_PAY_NA_DED_DIST EF WHERE EF.COMPANY=$Company And EF.PAYGROUP=$paygroup AND EF.PAY_END_DT = $PayEndDt AND EF.OFF_CYCLE = &J1.Off_Cycle AND EF.PAGE_NUM = &J1.PAGE_NUM AND EF.LINE_NUM = &J1.LINE_NUM AND EF.SEPCHK = &J1.SepChk AND EF.BENEFIT_RCD_NBR=&J1.BENEFIT_RCD_NBR AND EF.PLAN_TYPE=&J1.PLAN_TYPE AND EF.BENEFIT_PLAN=&J1.BENEFIT_PLAN AND EF.DEDCD=&J1.DEDCD AND EF.DED_CLASS=&J1.DED_CLASS AND EF.DED_SLSTX_CLASS=&J1. DED_SLSTX_CLASS END-SELECT End-Procedure Get-ded-GLcd begin-procedure Process-Taxes-USA if isblank ($hfmenty_list_L4) display 'All Entity selected' let $hfmenty_list_L4 = '' else display 'Specified Entity:' $hfmenty_list_J6 End-If if isblank ($Fincocd_List_L4) display 'All Fincocd selected' let $Fincocd_List_L4 = '' else display 'Specified Fincocd:' $Fincocd_List_J6 End-If let $paytype='3' do Intialization begin-select L1.PAGE_NUM L1.OFF_CYCLE L1.LINE_NUM L1.TAX_CUR L1.SEPCHK L1.STATE L1.LOCALITY L3.DEPTID L1.TAX_CLASS

L3.DEPTID L4.H_HFM_ENTY L4.H_FIN_TIER L4.H_PAY_CODE L4.H_ENTITYCD

&dep1 &enty2 &tier2 &pycd2 &cd2

LET $TAX_CLASS_SEL=' ' LET $TAX_CLASS_SXS=' LET $H_HFM_ENTY=&enty2 LET $H_FIN_TIER=&tier2 LET $H_PAY_CODE=&pycd2 LET $H_ENTITYCD=&cd2 LET $DEPTID=&dep1 LET $TAX_CLASS_SEL=&L1.TAX_CLASS LET $LOCALITY=&L1.LOCALITY move &L1.TAX_CUR to #Amount move &L1.STATE to $EDTcd Let $Local_Tax_cd = &L1.LOCALITY Do Get-Tax-GLCd-USA Do Insert-GL FROM PS_PAY_TAX L1, PS_PAY_CHECK L2, PS_JOB L3, PS_H_HR_DP_FIN_TBL L4 where L1.COMPANY = $Company AND L1.PAYGROUP = $PayGroup AND L1.PAY_END_DT = $PayEndDt AND L1.TAX_CLAS IN ( D , E , F , H , L , M , N , P , Q , S , U , V ) AND L1.TAX_CUR <> 0 AND L2.COMPANY = L1.COMPANY AND L2.PAYGROUP = L1.PAYGROUP AND L2.PAY_END_DT = L1.PAY_END_DT AND L2.OFF_CYCLE = L1.OFF_CYCLE AND L2.PAGE_NUM = L1.PAGE_NUM AND L2.LINE_NUM = L1.LINE_NUM AND L2.SEPCHK = L1.SEPCHK and L2.PAYCHECK_STATUS IN ( F , R , A ) and L2.PAYCHECK_OPTION IN ( A , C , R ) AND L3.EMPLID = L2.EMPLID AND L3.EMPL_RCD = L2.EMPL_RCD AND L3.EFFDT = (SELECT MAX(L31.EFFDT) FROM PS_JOB L31 WHERE L31.EMPLID = L3.EMPLID AND L31.EMPL_RCD = L3.EMPL_RCD AND L31.EFFDT <= $PayEndDt) AND L3.EFFSEQ = (SELECT MAX(L32.EFFSEQ) FROM PS_JOB L32 WHERE L32.EMPLID = L3.EMPLID AND L32.EMPL_RCD = L3.EMPL_RCD and L32.EFFDT = L3.EFFDT) and L3.SETID_DEPT=L4.SETID and L4.DEPTID=L3.DEPTID and L4.EFFDT = (SELECT MAX(L41.EFFDT) FROM PS_H_HR_DP_FIN_TBL L41 WHERE L4.SETID =L41.SETID AND L41.DEPTID=L4.DEPTID AND L41.EFFDT<=$PayEndDt) [$hfmenty_list_L4] [$Fincocd_List_L4] END-SELECT End-Procedure Process-Taxes-USA !***************************************************************** !Processing CANADA Taxes! !*****************************************************************

begin-procedure Process-Taxes-CAN if isblank ($hfmenty_list_L6) display 'All Entity selected' let $hfmenty_list_L6 = '' else display 'Specified Entity:' $hfmenty_list_J6 End-If if isblank ($Fincocd_List_L6) display ' All Fincocd selected' let $Fincocd_List_L6 = '' else display ' Specified Fincocd:' $Fincocd_List_L6 End-If Let $pay_type='3' Do Intialization LET $TAX_CLASS_SEL=' ' begin-select L8.OFF_CYCLE L8.PAGE_NUM L8.LINE_NUM L8.SEPCHK L8.PROVINCE L8.TAX_CLASS_CAN Decimal(L8.TAX_CUR,8,2) L8.PROVINCE &state L8.WAGE_LOSS_PLAN L9.DEPTID LET LET LET LET LET LET LET

&L8.TAX_CUR

$H_HFM_ENTY $H_FIN_TIER $H_PAY_CODE $H_ENTITYCD $DEPTID $TAX_CLASS_SEL $TAX_CLASS_SXS

&enty26 = &tier26 = &pycd26 = &cd26 = &dep3 = ' = ' '

move &L8.TAX_CUR to #Amount move &L8.PROVINCE to $EDTcd Let $wageloss = &L8.WAGE_LOSS_PLAN LET $TAX_CLASS_SEL=&L8.TAX_CLASS_CAN If ( &TAX_CALSS_CAN = CPP ) or (&TAX_CALSS_CAN = QPP ) If #Amount >0

do Insert-Paytax-CAN end-if End-if Do Get-Tax-GLCd-CAN Do Insert-GL FROM PS_PAY_TAX _CAN L8, PS_PAY_CHECK L9, PS_JOB L7, PS_H_HR_DP_FIN_TBL L6 where L8.COMPANY = $Company AND L8.PAYGROUP = $PayGroup AND L8.PAY_END_DT = $PayEndDt AND L8.TAX_CLASS_CAN IN ( CIT , CPP , CPR , EIE , EIR , QIT , QPP , QPR , QIE , QIR , HTX , AND L8.TAX_CUR <> 0 AND L9.COMPANY = L8.COMPANY AND L9.PAYGROUP = L8.PAYGROUP AND L9.PAY_END_DT = L8.PAY_END_DT AND L9.OFF_CYCLE = L8.OFF_CYCLE

L9.PAGE_NUM = L8.PAGE_NUM L9.LINE_NUM = L8.LINE_NUM L9.SEPCHK = L8.SEPCHK L9.PAYCHECK_STATUS IN ( F , R , A ) L9.PAYCHECK_OPTION IN ( A , C , R ) L7.EMPLID = L9.EMPLID L7.EMPL_RCD = L9.EMPL_RCD L7.EFFDT = (SELECT MAX(L71.EFFDT) FROM PS_JOB L71 WHERE L71.EMPLID = L7.EMPLID AND L71.EMPL_RCD = L7.EMPL_RCD AND L71.EFFDT <= $PayEndDt) AND L7.EFFSEQ = (SELECT MAX(L72.EFFSEQ) FROM PS_JOB L72 WHERE L72.EMPLID = L7.EMPLID AND L72.EMPL_RCD = L7.EMPL_RCD and L72.EFFDT = L7.EFFDT) ! and L3.SETID_DEPT=L7.SETID and L6.DEPTID=L7.DEPTID and L6.EFFDT = (SELECT MAX(L61.EFFDT) FROM PS_H_HR_DP_FIN_TBL L61 WHERE L6.SETID =L61.SETID AND L61.DEPTID=L6.DEPTID AND L61.EFFDT<=$PayEndDt) [$hfmenty_list_L6] [$Fincocd_List_L6] END-SELECT End-Procedure Process-Taxes-CAN !*********************************************************************** !*********************************************************************** Begin-Procedure Get-Tax-GLCd-USA BEGIN-SELECT DS2.ACCT_CD Let $Exp_Lia=&DS2.ACCT_CD FROM PS_PAY_NA_DED_DIST DS2 WHERE DS2.COMPANY=$Company And DS2.PAYGROUP=$paygroup AND DS2.PAY_END_DT = $PayEndDt AND DS2.OFF_CYCLE = &L1.Off_Cycle AND DS2.PAGE_NUM = &L1.PAGE_NUM AND DS2.LINE_NUM = &L1.LINE_NUM AND DS2.SEPCHK = &L1.SepChk AND DS2.STATE=&L1.STATE AND DS2.LOCALITY=&L1.LOCALITY AND DS2.TAX_CLASS=&L1.TAX_CLASS END-SELECT End-Procedure Get-Tax-GLCd-USA !**************************************************************************** !Insert employer tax row in pay tax Canada! !**************************************************************************** Begin-Procedure Insert-Paytax-CAN if $tax_class='CPP' let $tax_class1='CPR' end-if if $tax_class='QPP' let $tax_class1='QPR' end-if

AND AND AND and and AND AND AND

begin-sql insert into PS_PAY_TAX_CAN (COMPANY, PAYGROUP, PAY_END_DT, OFF_CYCLE, PAGE_NUM, LINE_NUM, SEPCHK , PROVINCE, TAX_CLASS_CAN, WAGE_LOSS_PLAN, NLGRS_CUR, TXGRS_CUR, TAX_CUR, TAX_NOT_TAKEN, UI_ER_RT, AP_STATUS) ( select P.COMPANY, P.PAYGROUP, P.PAY_END_DT, P.OFF_CYCLE, P.PAGE_NUM, P.LINE_NUM, P.SEPCHK, P.PROVINCE, $tax_class1, P.WAGE_LOSS_PLAN, P.NLGRS_CUR, P.TXGRS_CUR, P.TAX_CUR, P.TAX_NOT_TAKEN, P.UI_ER_RT, P.AP_STATUS from PS_PAY_TAX_CAN P where P.COMPANY = $Company and P.PAYGROUP = $PayGroup and P.PAY_END_DT = $PayEndDt and P.TAX_CLASS_CAN =$tax_class and P.TAX_CUR <> 0 AND P.OFF_CYCLE = &L8.OFF_CYCLE AND P.PAGE_NUM = &L8.PAGE_NUM AND P.LINE_NUM = &L8.LINE_NUM AND P.SEPCHK = &L8.SEPCHK AND P.PROVINCE=&L8.PROVINCE And not exists(select 'X' from PS_PAY_TAX_CAN P1 where P1.COMPANY = $Company and P1.PAYGROUP = $PayGroup and P1.PAY_END_DT = $PayEndDt and P1.TAX_CLASS_CAN =$tax_class and P1.TAX_CUR <> 0 AND P1.OFF_CYCLE = &L8.OFF_CYCLE AND P1.PAGE_NUM = &L8.PAGE_NUM AND P1.LINE_NUM = &L8.LINE_NUM AND P1.SEPCHK = &L8.SEPCHK AND P1.PROVINCE=&L8.PROVINCE )) end-sql

End-Procedure Insert-Paytax-CAN begin-procedure Process-NetChk if isblank ($hfmenty_list_P4) display ' All Entity selected' let $hfmenty_list_P4 = '' else display ' Specified Entity:' $hfmenty_list_P4 End-If if isblank ($Fincocd_List_P4) display ' All Fincocd selected' let $Fincocd_List_P4 = '' else display ' Specified Fincocd:' $Fincocd_List_P4 End-If do Intialization let $paytype='4' begin-select P1.NET_PAY &P1.AMOUNT P1.COMPANY P1.PAYGROUP P1.PAY_END_DT P2.DEPTID P1.OFF_CYCLE P1.PAGE_NUM P1.LINE_NUM P1.SEPCHK P4.H_HFM_ENTY &enty265 P4.H_FIN_TIER &tier265 P4.H_PAY_CODE &pycd265 P4.H_ENTITYCD &cd265 LET $H_HFM_ENTY=&enty265 LET $H_FIN_TIER=&tier265 LET $H_PAY_CODE=&pycd265 LET $H_ENTITYCD=&cd265 LET $DEPTID=&P2.DEPTID do get-deposit LET #net_pay_a=&P1.AMOUNT-#dep_amnt_dir Move &net-pay to #Amount DO Get-NetDep-GLCd Do INSERT-GL FROM PS_PAY_CHECK P1, PS_JOB P2, PS_PAY_DISTRIBUTN P3, PS_H_HR_DP_FIN_TBL P4 WHERE P1.COMPANY = $Company AND P1.PAYGROUP = $PayGroup AND P1.PAY_END_DT = $PayEndDt AND P2.EMPLID = P1.EMPLID AND P2.EMPL_RCD = P1.EMPL_RCD AND P2.EFFDT = (SELECT MAX(P21.EFFDT) FROM PS_JOB P21 WHERE P21.EMPLID = P2.EMPLID AND P21.EMPL_RCD = P2.EMPL_RCD AND P21.EFFDT <= $PayEndDt) AND P2.EFFSEQ = (SELECT MAX(P22.EFFSEQ)

FROM PS_JOB P22 WHERE P22.EMPLID = P2.EMPLID AND P22.EMPL_RCD = P2.EMPL_RCD AND P22.EFFDT = P2.EFFDT) AND P3.COMPANY = P1.COMPANY AND P3.PAYGROUP = P1.PAYGROUP AND P3.PAY_END_DT = P1.PAY_END_DT AND P3.OFF_CYCLE = P1.OFF_CYCLE AND P3.PAGE_NUM = P1.PAGE_NUM AND P3.LINE_NUM = P1.LINE_NUM AND P3.SEPCHK = P1.SEPCHK and P1.PAYCHECK_STATUS IN ( F , R , A ) and P1.PAYCHECK_OPTION IN ( A , C , R ) ! AND P3.ACCOUNT_TYPE in ( C , S ) and P2.SETID_DEPT=P4.SETID and P2.DEPTID=P4.DEPTID and P4.EFFDT <=$PayEndDt) [$hfmenty_list_P4] [$Fincocd_List_P4] End-Select End-Procedure Process_Netchk !********************************************************* !********************************************************* begin-procedure Get-NetDep-GLCd begin-select G.GL_NET_PAY G.GL_DIR_DEPOSITS if $paytype='4' let $Exp_Lia= &G.GL_NET_PAY end-if if $paytype='5' let $Exp_Lia = &G.GL_DIR_DEPOSITS end-if from PS_COMPANY_TBL_GL G where G.COMPANY=$COMPANY and G.EFFDT=(select MAX(G1.EFF DT) from PS_COMPANY_TBL_GL G1 where G.COMPANY=G1.COMPANY and G1.EFFDT<=$PAYENDDT ) end-select end-procedure NetDep-GLCd !*********************************************************** !*********************************************************** begin-procedure Process-Deposit if isblank ($hfmenty_list_P14) display ' All Entity selected' let $hfmenty_list_P14 = '' else display ' Specified Entity:' $hfmenty_list_P14 End-If if isblank ($Fincocd_List_P14) display ' All Fincocd selected' let $Fincocd_List_P14 = '' else display ' Specified Fincocd:' $Fincocd_List_P14 End-If do Intialization let $paytype='5' begin-select P91.NET_PAY &P91.payee

P91.DEPTID P14.H_HFM_ENTY &enty14 P14.H_FIN_TIER &tier14 P14.H_PAY_CODE &pycd14 P14.H_ENTITYCD &cd14 P1.DEPTID let $H_HFM_ENTY=&enty14 LET $H_FIN_TIER=&tier14 LET $H_PAY_CODE=&pycd14 LET $H_ENTITYCD=&cd14 let $DEPTID=&P91.DEPTID Move &P91.payee to #Amount do Get-NetDep-GLCd do INSERT-GL FROM PS_PAY_CHECK P91, PS_JOB P12, PS_PAY_DISTRIBUTN P13, PS_H_HR_DP_FIN_TBL P14 WHERE P91.COMPANY = $Company AND P91.PAYGROUP = $PayGroup AND P91.PAY_END_DT = $PayEndDt AND P12.EMPLID = P91.EMPLID AND P12.EMPL_RCD = P91.EMPL_RCD AND P12.EFFDT = (SELECT MAX(P121.EFFDT) FROM PS_JOB P121 WHERE P121.EMPLID = P12.EMPLID AND P121.EMPL_RCD = P12.EMPL_RCD AND P121.EFFDT <= $PayEndDt) AND P12.EFFSEQ = (SELECT MAX(P122.EFFSEQ) FROM PS_JOB P122 WHERE P122.EMPLID = P12.EMPLID AND P122.EMPL_RCD = P12.EMPL_RCD AND P122.EFFDT = P12.EFFDT) AND P13.COMPANY = P91.COMPANY AND P13.PAYGROUP = P91.PAYGROUP AND P13.PAY_END_DT = P91.PAY_END_DT AND P13.OFF_CYCLE = P91.OFF_CYCLE AND P13.PAGE_NUM = P91.PAGE_NUM AND P13.LINE_NUM = P91.LINE_NUM AND P13.SEPCHK = P91.SEPCHK and P91.PAYCHECK_STATUS IN ( F , R , A ) and P91.PAYCHECK_OPTION IN ( A , C , R ) AND P13.ACCOUNT_TYPE in ( C , S ) and P12.SETID_DEPT=P14.SETID and P12.DEPTID=P14.DEPTID and P14.EFFDT = (SELECT MAX(P141.EFFDT) FROM PS_H_HR_DP_FIN_TBL P141 WHERE P141. SETID=P14.SETID AND P141.DEPTID=P14.DEPTID AND P141.EFFDT<=$PayEndDt) [$hfmenty_list_P14] [$Fincocd_List_P14] End-Select End-Procedure Process-Deposit !********************************************************************* !Break the file by entity code! !********************************************************************* begin-procedure Break-File let $prev_hfmenty=' ' Let $Prcsnm = 'HPY213I' !For Canada only if $country = 'USA' let $Ded_Slstx=' ' end-if

BEGIN-SELECT BED.PROCESS_INSTANCE BED.HFM_ENTY BED.H_PAY_CODE BED.DEPTID BED.H_ENTITYCD BED.H_FIN_TIER BED.PAY_TYPE BED.EDT_CD BED.COMBO_CD_DETAIL BED.CURRENCY_CD BED.TAX_CLASS_CAN BED.DED_SLSTX_CLASS BED.LOCALITY BED.PAY_END_DT BED.CHECK_DT BED.PAY_PERIOD sum(BED.AMOUNT) sum(BED.HOURS)

&bedamt &bedhrs

let $BED.hfm_enty = &BED.H_HFM_ENTY let $BED.pycode = &BED.H_PAY_CODE let $BED.deptid = &BED.DEPTID let $BED.entity = &BED.H_ENTITYCD let $BED.fn_tier = &BED.H_FIN_TIER let $BED.pytype = &BED.PAY_TYPE let $BED.edtcd = &BED.EDTCD let $BED.combo_cd = &BED.COMBO_CD_DETAIL let $BED.currency = &BED.CURRENCY_CD let $BED.tax = &BED.TAX_CLASS_can let $BED.slstx = &BED.DED_SLSTX_CLASS let $BED.locality = &BED.LOCALITY let #BED.amount = &BEDamt let #BED.hours = &BEDhrs let $BED.payenddate = &BED.PAY_END_DT let $BED.checkdt = &BED.CHECK_DT let $BED.pyperiod = &BED.PAY_PERIOD If $prev_hfmenty <> &BED.HFM_ENTY Do close_process Do Open-File End-if Let $prev_hfmenty=& BED.HFM_ENTY Do write-File FROM H_PY_GL_TMP BED where process_instance=#procs_Instance Group by BED.PROCESS_INSTANCE BED.H_HFM_ENTY , BED.H_PAY_CODE, BED.DEPTID , BED.H_ENTITYCD , BED.H_FIN_TIER , BED.PAY_TYPE, BED.EDTCD, BED.COMBO_CD_DETAIL, BED.CURRENCY_CD, BED.TAX_CLASS_CAN, BED.DED_SLSTX_CLASS, BED.LOCALITY, BED.PAY_END_DT, BED.CHECK_DT,

BED.PAY_PERIOD ORDER BY BED.HFM_ENTY ,BED.PAY_CODE ,BED. H_FIN_TIER, BED_ENTITYCD,BED.DEPTID,BED.COMBO_CD_DETAIL ,BED.EDTCD,BED.PAY_TYPE END-SELECT DO close_process End-Procedure BREAK-FILE !*************************************************************** !***************************************************************

begin-procedure Insert-GL if #amount > 0 let $amt_sign = '+' else let $amt_sign = '-' end-if if #hours>0 let $hrs_sign= '+' else let $hrs_sign= '-' end-if begin-sql insert into H_PY_GL_TMP (PRCSINSTANCE , H_HFM_ENTY , H_PAY_CODE ,DEPTID , H_ENTITYCD H_FIN_TIER PAY_TYPE , COMBO_CD_DETAIL ,CURRENCY_CD ,EDTCD TAX_CLASS_CAN, DED_SLSTX_CLASS, LOCALITY ,AMOUNT ,AMOUNT_IND ,HOURS HRS_SIGN, PAY_END_DT, CHECK_DT, PAY_PERIOD) VALUES (#prcs_process_instance ,$H_HFM_ENTY ,$H_PAY_CODE ,$DEPTID ,$H_ENTITYCD ,$H_FIN_TIER ,$PAY_TYPE ,$EXP_LIA ,$CURRENCT_CD ,$EDTCD ,$TAX_CALSS_SEL ,$TAX_CLASS_SXS ,$LOCALITY

,#AMOUNT ,$Amt_Sign ,#Hours ,$Hrs_Sign ,&payenddt ,$check_dt, $pay_period) end-sql end-procedure Insert-GL !**************************************************************** !**************************************************************** Begin-Procedure Open-File if $prev_hfmenty= '' LET $prev_hfmenty=&BED.H_HFM_ENTY end-if !let $data_dir = '{Interface_Dir}'||'/' let $data_dir = getenv('DATA_HOME') Let $delimiter= ',' move #prcs_process_instance to $pinstance '9999999999' let $pinstance= ltrim(rtrim($pinstance,' '),' ') Let $FileName = $data_dir || '/'|| 'out'||'/' || 'GL_' || $prev_hfmenty ||' _' || $pinstance|| '.csv' Let $Ftp_FileName = 'GL_' || $prev_hfmenty ||'_' || $pinstance|| '.csv' Let #filenum = #filenum + 1 show 'file num' show #filenum open $FileName as #filenum for-writing record=1200:fixed end-procedure Open-File

Begin-Procedure Close-File if $prev_hfmenty<> '' Close #filenum-1 End-if end-procedure Close-File begin-procedure Write-File if #BED.amount>=0 let $a_sign='+' else let $a_sign='-' end-if if #BED.hours>=0 let $h_sign='+' else let $h_sign='-' end-if move #BED.amount to $Amount '99999999.99' move #BED.hours to $hours '999999.99' let $pay_end_date1 = Edit($BED.payenddate,'YYYY-MM-DD') let $check_dt1 = Edit($BED.checkdt,'YYYY-MM-DD') write #filenum from $BED.hfm_enty:6 $delimiter $BED.pycode:1 $delimiter

$BED.deptid:10 $delimiter $BED.entity:4 $delimiter $BED.fn_tier:6 $delimiter $BED.pytype:1 $delimiter $BED.combo_cd:35 $delimiter $BED.currency:3 $delimiter $BED.edtcd:6 $delimiter $BED.tax:3 $delimiter $BED.slstx:1 $delimiter $BED.locality:7 $delimiter $Amount:11 $delimiter $a_sign:1 $delimiter $hours:9 $delimiter $h_sign:1 $delimiter $pay_end_date1:10 $delimiter $check_dt1:10 $delimiter End-Procedure Write-File

!Initialize all variables! begin-procedure Intialization Let $Exp_Lia= Let $EDTCd= End-Procedure Initialization

begin-procedure create-trigger-file Let $TriggerFileName = $data_dir || '/out/' || '/trigger.xml' let $cmdline1 = 'touch ' || $TriggerFileName display $cmdline1 CALL SYSTEM using $cmdline1 #status if #status != 0 display 'Trigger file not created' else let $sequence = '2' let $MODE = 'PUT' do SFTP($prcs_process_instance,$Prcsnm,$sequence,$MODE) end-if end-if End-Procedure create-trigger-file begin-procedure Update-FTP-Prcs-Defination BEGIN-SQL update ps_h_ftp_files set h_ftp_command = $Ftp_FileName where prcstype = 'SQR Report' and prcsname like '%HPY213I' and seq_nbr = 1 END-SQL End-procedure Update-FTP-Prcs-Dfination begin-procedure Update-FTP-Defination

let $Client_Path = '/NA/CAN/payroll/paydist/'||$prev_hfmenty ||'/' BEGIN-SQL update ps_h_ftp_address set h_client_path = $Client_Path where h_ftp_name like '%HPY213I' END-SQL End-procedure Update-FTP-Dfination

#include 'curdttim.sqc' !Get-Current-DateTime procedure ! #include 'datemath.sqc' !Routine for determining end of month #include 'datetime.sqc' !Routines for date and time formatting #include 'number.sqc' !Routines to format numbers #include 'stdapi.sqc' !Update Process API ! #include 'gpuspctl.sqc' !Get-Run-Control procedure ! #include 'sqrtrans.sqc' !Translate SQR strings to given language