[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Help with Dup records
- Subject: Help with Dup records
- From: Aboukr Sadikh <bsadikh@YAHOO.COM>
- Date: Sat, 1 May 1999 09:08:02 -0700
Hi Gurus,
Platform: Oracle 7.3.4 on UNIX
PeopleSoft: Public sector v.7.01
I am working on a paysheet load program, and I am having some troubles
with my program.
I cloned the delivered payudpt and tried to modify it to meet our
requirements. But,
everytime I run it I have it, the following problems occur:
- Page#, Line# and Addl# are always 0'
- I have a duplicate record error during insert in Pay_Earnings
and Pay_Oth_Earns.
- Nothing is inserted into Pay_Line.
I've put displays everywhere to see when I have a missing column or
null value, but cannot
find any.
Following is the SQR.log, and I've attached the program for any hints
on why I'm having
the duplicate errors.
Thanks in advance for your help.
ABS.
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
!************************************************************************
! PayUpdt: This program reads the Pay Data Input file and updates *
! PaySheets. *
!************************************************************************
!***********************************************************************
! *
! Confidentiality Information: *
! *
! This module is the confidential and proprietary information of *
! PeopleSoft, Inc.; it is not to be copied, reproduced, or transmitted *
! in any form, by any means, in whole or in part, nor is it to be used *
! for any purpose other than that for which it is expressly provided *
! without the written permission of PeopleSoft. *
! *
! Copyright (c) 1988-1997 PeopleSoft, Inc. All Rights Reserved *
! *
!***********************************************************************
! *
! $Date:: 17/04/99 10:30p $ *
! $Revision:: 21 $ *
! $Workfile:: PAYUPDT.SQR $ *
! *
!***********************************************************************
! This program has been modified for HRMS for Public Sector. *
!***********************************************************************
#include 'setenv.sqc' !Set environment
#Include 'setup01.sqc'
begin-report
do Init-DateTime
do Init-Number
do stdapi-init
move 'PayUpdt' to $ReportID
move 'Update PaySheets with Pay Input Data' to $ReportTitle
display $ReportTitle
do Get-Current-DateTime
do Init-Report
do Commit-Transaction
date-time () hh:mi:ss &timeEnded
display 'Report Ended: ' noline
display &timeEnded
do stdapi-term
do Reset
end-report
!---------------------------------------------------------------------------------------
begin-procedure Init-Report
!---------------------------------------------------------------------------------------
display 'Beginning procedure report'
while 1 = 1
if $prcs_process_instance = ''
input $Run_ID 'Enter Payroll Run ID' type=char
uppercase $Run_ID
do Edit-Run
if RTRIM($Run_ID, ' ') <> ''
if RTRIM($Company, ' ') = ''
display 'You must identify the Pay Calendar.'
input $Company maxlen=3 'Enter Company ID (e.g. ccb)' type=char
uppercase $Company
input $PayGroup maxlen=3 'Enter Pay Group (e.g. sm1)' type=char
uppercase $PayGroup
input $Pay_End_Dt 'Enter Pay End Date (e.g. 15-Nov-1989)'
type=date format='dd-mon-yyyy'
end-if
end-if
else
do Select-Parameters
move $RC_PAYINIT.run_ID to $Run_ID
move $RC_PAYINIT.company to $Company
move $RC_PAYINIT.Paygroup to $Paygroup
move $RC_PAYINIT.Pay_End_DT to $Pay_End_dt
if $Run_Id <> ''
do Edit-Run
end-if
end-if
do Edit-Company
if RTRIM($Company, ' ') <> ''
do Edit-PayGroup
if RTRIM($PayGroup, ' ') <> ''
do Edit-PayCalendar
if RTRIM($Pay_End_Dt, ' ') <> ''
break
end-if
end-if
end-if
if $prcs_process_instance <> ''
and $Found = 'N'
break
!goto Report-Exit
end-if
end-while
date-time () hh:mi:ss &timeBegan
display 'Report Began: ' noline
display &timeBegan
do Get-Last-Page
move #Page# to #Page#Save
add 1 to #Page#
display 'New Page is: ' noline
display #page#
move 1 to #line#
display 'New line is: ' noline
display #line#
do Report
do Commit-Transaction
end-procedure
!---------------------------------------------------------------------------------------
begin-heading 7
!---------------------------------------------------------------------------------------
#Include 'stdhdg01.sqc'
print 'Run ID=' (+2,1)
print $Run_ID ()
print ' Company=' ()
print $Company ()
print ' PayGroup=' ()
print $PayGroup ()
print ' Pay End Date=' ()
do Format-DateTime($Pay_End_Dt, $out, {DEFDATE}, '', '')
print $out ()
print 'Tran# EmplID Message' (+2,1)
end-heading
!---------------------------------------------------------------------------------------
begin-procedure Report
!---------------------------------------------------------------------------------------
!add 1 to #page#
BEGIN-SELECT
PSL.COMPANY !(+1,1)
PSL.PAYGROUP !(0,5)
PSL.RUN_ID !(0,10)
PSL.EMPLID !(0,14)
PSL.EMPL_RCD# !(0,24)
PSL.DEPTID !(0,26)
PSL.LINE# !(0,35)
PSL.ERNCD !(0,45)
PSL.HOURLY_RT !(,50)
sum(PSL.HOURS) &HOURS !(0,60)
add 1 to #InputTran
move &PSL.EMPLID TO $EMPLID
move &PSL.EMPL_RCD# TO #EMPL_RCDNO
move &PSL.ERNCD TO $ERNCD
move &PSL.DEPTID TO $deptid
move &HOURS to #hours
move &PSL.HOURLY_RT to #HrlyRt
move ' ' TO $AMOUNT
move 'A' TO $TAXMETHOD
display '********************************************'
display 'From COB_TIME_ENTRY3 table'
display $emplid
display #empl_RecdNo
display &PSL.COMPANY
display &PSL.PAYGROUP
display $erncd
dislplay #HOURS
display #HrlyRt
display '********************************************'
if $Emplid = $EmplIDSave and #Empl_RcdNo = #Empl_RcdSave
display 'Emplid equal to last emplid'
display $emplidsave
display #empl_rcdsave
do Edit-Validate-Data
do Get-Addl
if $erncd = 'REG'
move #hours to #reg_hrs
do add-pay-earnings ! Process REG earnings code
do Pay-Status-Update
move 0 to #hours
else
do Process-Erncd ! Process other earnings code
end-if
else
!do Add-New-Line
do Edit-Validate-Data
do Get-Employee-Data
do Get-Page-Line
if $erncd = 'REG'
move #hours to #reg_hrs
do add-pay-earnings ! Process REG earnings code
move 0 to #hours
else
do Process-Erncd ! Process other earnings code
end-if
move $emplid to $emplidsave
move #Empl_RcdNo to #Empl_RcdSave
end-if
add 1 to #InputDisp
if #InputDisp = 50
move 0 to #InputDisp
end-if
FROM PS_INPUT_DATA PSL
WHERE PSL.COMPANY = $COMPANY
AND PSL.PAYGROUP = $PAYGROUP
AND PSL.RUN_ID = $RUN_ID
group BY PSL.COMPANY,
PSL.PAYGROUP,
PSL.RUN_ID,
PSL.EMPLID,
PSL.EMPL_RCD#,
PSL.DEPTID,
PSL.LINE#,
PSL.ERNCD,
ORDER BY PSL.COMPANY,
PSL.PAYGROUP,
PSL.RUN_ID,
PSL.EMPLID,
PSL.EMPL_RCD#,
PSL.DEPTID,
PSL.LINE#,
PSL.ERNCD
END-SELECT
print 'Total Transactions=' (+2,1)
do Format-Number(#InputTran, $out, '99999')
print $out ()
print ' Error Transactions=' ()
do Format-Number(#ErrorCount, $out, '99999')
print $out ()
!Report-Exit:
end-procedure
!--------------------------------------------------------------------------------------------
begin-procedure Edit-Validate-Data
!--------------------------------------------------------------------------------------------
if RTRIM($ErnCd, ' ') = ''
do Error-Found
print 'No Earnings Code' (,21)
else
do Edit-ErnCd
end-if
let #Amount = RTRIM($Amount, ' ')
move 'A' to $TaxMethod
move 'TAX_METHOD' to $FieldName
move 'Tax Method' to $FieldPrint
move $TaxMethod to $FieldValue
do Edit-Xlat
move &H.Pay_Frequency to $Freq
if RTRIM($Freq, ' ') <> ''
move 'PAY_FREQUENCY' to $FieldName
move 'Pay Frequency' to $FieldPrint
move $Freq to $FieldValue
do Edit-Xlat
end-if
if rtrim($periods,' ') = ''
move 1 to #PeriodS
else
let #Periods = To_Number($Periods)
end-if
move 0 to #sepchk
let $DedSubsetId = ' '
move 'N' to $GrossUp
move 'N' to $DisDir
let $DisDir = RTRIM($DisDir, ' ')
let $Reason = 'N'
let $fica_status_ee = 'N'
let #paid_periods = 0
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Edit-Run
!---------------------------------------------------------------------------------------
display 'beginning procedure edit-run'
move 0 to #Found
begin-select
N.COMPANY
N.PAYGROUP
N.PAY_END_DT
N.PAY_BEGIN_DT
add 1 to #Found
FROM PS_PAY_CALENDAR N
WHERE N.RUN_ID = $Run_ID
end-select
if #Found = 0
display 'Error -- Run ID not valid for any Pay Calendar.'
move ' ' to $Run_ID
else
if #Found = 1
move &N.Company to $Company
move &N.PayGroup to $PayGroup
move &N.Pay_End_Dt to $Pay_End_Dt
move &N.Pay_Begin_Dt to $Pay_Begin_Dt
else
display 'There is more than on Pay Calendar for the Run ID.'
if $prcs_process_instance = ''
move ' ' to $Company
move ' ' to $PayGroup
move '' to $Pay_End_Dt
move '' to $Pay_Begin_Dt
end-if
end-if
end-if
end-procedure
begin-procedure Edit-Company
display 'beginning procedure edit-company'
move 'N' to $Found
begin-select
'f'
move 'Y' to $Found
FROM PS_COMPANY_TBL F
WHERE F.COMPANY = $Company
AND F.EFFDT =
(SELECT MAX(G.EFFDT)
FROM PS_COMPANY_TBL G
WHERE G.COMPANY = $Company
AND G.EFFDT <= $Pay_End_Dt)
end-SELECT
if $Found = 'N'
display 'Error -- Company not found.'
display $Company
move ' ' to $Company
end-if
end-procedure
begin-procedure Edit-PayGroup
display 'beginning procedure edit-paygroup'
move 'N' to $Found
begin-SELECT
H.PAY_FREQUENCY
H.ERNCD_REG_HRS
H.ERNCD_OT_HRS
H.ERNCD_REG_EARNS
move 'Y' to $Found
FROM PS_PAYGROUP_TBL H
WHERE H.COMPANY = $Company
AND H.PAYGROUP = $PayGroup
AND H.EFFDT =
(SELECT MAX(I.EFFDT)
FROM PS_PAYGROUP_TBL I
WHERE I.COMPANY = $Company
AND I.PAYGROUP = $PayGroup
AND I.EFFDT <= $Pay_End_Dt)
end-SELECT
if $Found = 'N'
display 'Error -- Pay Group not found.'
move ' ' to $PayGroup
end-if
end-procedure
begin-procedure Edit-PayCalendar
display 'beginning procedure edit-paycalendar'
move 'N' to $Found
begin-SELECT
J.PAY_END_DT
J.PAY_CONFIRM_START
J.RUN_ID
J.PAY_BEGIN_DT
move 'Y' to $Found
move &J.Pay_End_Dt to $Pay_End_Dt
move &J.Pay_Begin_Dt to $Pay_Begin_Dt
FROM PS_PAY_CALENDAR J
WHERE J.COMPANY = $Company
AND J.PAYGROUP = $PayGroup
AND J.PAY_END_DT = $Pay_End_Dt
end-SELECT
if $Found = 'N'
display 'Error -- Pay Calendar not found.'
move '' to $Pay_End_Dt
end-if
if &J.Pay_Confirm_Start = 'Y'
move 'N' to $Found
display 'Error -- Pay Calendar has been confirmed.'
move '' to $Pay_End_Dt
end-if
if $Run_id <> ''
if &J.Run_ID <> $Run_ID
display 'Error -- Pay Calendar not setup for Run ID=' noline
display $Run_ID
move 'N' to $Found
move '' to $Pay_End_Dt
end-if
end-if
end-procedure
begin-procedure Get-Last-Page
display 'beginning procedure get-last-page'
begin-select
MAX(PAGE#) &Page
move &Page to #Page#
FROM PS_PAY_PAGE
WHERE PAY_END_DT = $Pay_End_Dt
AND COMPANY = $Company
AND PAYGROUP = $PayGroup
AND OFF_CYCLE = 'N'
end-select
end-procedure
begin-procedure Get-Page-Line
display 'beginning procedure get-page-line'
move 'N' to $Found
begin-SELECT
K.PAGE#
K.LINE#
move 'Y' to $Found
EXIT-SELECT
FROM PS_PAY_LINE K, PS_PAY_EARNINGS L
WHERE K.EMPLID = $EmplID
AND K.EMPL_RCD# = #Empl_RcdNo
AND K.COMPANY = $Company
AND K.PAYGROUP = $PayGroup
AND K.PAY_END_DT = $Pay_End_Dt
AND L.COMPANY=K.COMPANY
AND L.PAYGROUP=K.PAYGROUP
AND L.PAY_END_DT=K.PAY_END_DT
AND L.OFF_CYCLE=K.OFF_CYCLE
AND L.PAGE#=K.PAGE#
AND L.LINE#=K.LINE#
AND K.MANUAL_CHECK = 'N'
end-select
if $Found = 'Y'
display 'I am where the duplicate insert is commented out'
move &K.Page# to #Page#Oth
move &K.Line# to #Line#Oth
do Get-Addl
if $ErrorFound = 'N'
!display 'Add-Pay-Oth-Earns 2'
!do Add-Pay-Oth-Earns
!do Pay-Status-Update
end-if
else
display 'Did not find any pay line, so add a new pay line'
do Add-New-Line
!if $erncd <> &H.erncd_reg_hrs
!display 'Loading earnings on the pay other earns tbl'
do Process-Erncd
!do Add-Pay-Oth-Earns
!end-if
end-if
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Process-ErnCd
!---------------------------------------------------------------------------------------
evaluate $Erncd
when = 'O15'
let #hours = #hours * 1.5
move #hours TO #oe_hrs
display 'Emplid: ' noline
display $emplid
display 'Earning Code: ' noline
display $erncd
display 'Hours to load: ' noline
display #oe_hrs
do Add-Pay-Oth-Earns
do Pay-Status-Update
move 0 TO #hours
break
when = 'ACP'
let #hours = #hours * 1.5
move #hours TO #oe_hrs
display 'Emplid: ' noline
display $emplid
display 'Earning Code: ' noline
display $erncd
display 'Hours to load: ' noline
display #oe_hrs
do Add-Pay-Oth-Earns
do Pay-Status-Update
move 0 TO #oe_hrs
break
when = 'TCT'
let #hours = #hours * 1.5
move #hours TO #oe_hrs
display 'Emplid: ' noline
display $emplid
display 'Earning Code: ' noline
display $erncd
display 'Hours to load: ' noline
display #poe_hrs
do Add-Pay-Oth-Earns
do Pay-Status-Update
move 0 TO #oe_hrs
break
when = 'OTP'
let #hours = #hours * 1.5
move #hours TO #poe_hrs
display 'Emplid: ' noline
display $emplid
display 'Earning Code: ' noline
display $erncd
display 'Hours to load: ' noline
display #oe_hrs
do Add-Pay-Oth-Earns
do Pay-Status-Update
move 0 TO #oe_hrs
break
when = 'OT2'
let #hours = #hours * 2
move #hours TO #poe_hrs
display 'Emplid: ' noline
display $emplid
display 'Earning Code: ' noline
display $erncd
display 'Hours to load: ' noline
display #oe_hrs
do Add-Pay-Oth-Earns
do Pay-Status-Update
move 0 TO #oe_hrs
break
when = 'OH2'
let #hours = #hours * 2
move #hours TO #oe_hrs
display 'Emplid: ' noline
display $emplid
display 'Earning Code: ' noline
display $erncd
display 'Hours to load: ' noline
display #oe_hrs
do Add-Pay-Oth-Earns
do Pay-Status-Update
move 0 TO #oe_hrs
break
when-other
if $erncd <> 'REG'
move #hours to #oe_hrs
display 'Emplid: ' noline
display $emplid
display 'Earning Code: ' noline
display $erncd
display 'Hours to load: ' noline
display #oe_hrs
do Add-Pay-Oth-Earns
do Pay-Status-Update
move 0 TO #oe_hrs
end-if
break
end-evaluate
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Get-Addl
!---------------------------------------------------------------------------------------
display 'CCCCCCCCCCCCCCCCCCCCCCCCCCCC'
display 'beginning procedure get-addl'
move 'N' to $Found
display #Page#Oth
display #Line#Oth
display #hrlyrt
display $shift
display $Earns_Begin_Dt
display $Earns_End_Dt
display 'CCCCCCCCCCCCCCCCCCCCCCCCCCCC'
begin-SELECT
L.COMPANY
L.PAYGROUP
L.PAY_END_DT
L.PAGE#
L.LINE#
L.ADDL#
L.HOURLY_RT
L.SHIFT
L.OK_TO_PAY
L.REG_PAY_HRS
display 'PBBBBBBBBBBBBBBBBBB'
display 'Debug Get-Addl'
display &L.PAGE#
display &L.LINE#
display &L.ADDL#
display &L.HOURLY_RT
display &L.OK_TO_PAY
display 'PBBBBBBBBBBBBBBBBBB'
if RTRIM($HrlyRt, ' ') = '' or #HrlyRt = &L.Hourly_Rt
move 'Y' to $Found
EXIT-SELECT
end-if
FROM PS_PAY_EARNINGS L
where L.COMPANY = $Company
and L.PAYGROUP = $PayGroup
and L.PAY_END_DT = $Pay_End_Dt
and L.OFF_CYCLE = 'N'
and L.PAGE# = #Page#Oth
and L.LINE# = #Line#Oth
and L.SEPCHK = #SepChk
and L.TAX_METHOD = $TaxMethod
and L.TAX_PERIODS = #Periods
and L.PAY_FREQUENCY = $Freq
and not EXISTS
(SELECT 'x'
from PS_PAY_OTH_EARNS P
where P.COMPANY = $Company
and P.PAYGROUP = $PayGroup
and P.PAY_END_DT = $Pay_End_Dt
and P.OFF_CYCLE = 'N'
and P.PAGE# = #Page#Oth
and P.LINE# = #Line#Oth
and P.ADDL# = L.ADDL#
and P.ERNCD = $ErnCd)
ORDER BY L.COMPANY,
L.PAYGROUP,
L.PAY_END_DT,
L.PAGE#,
L.LINE#,
L.ADDL#
end-select
if $Found = 'Y'
move &L.Addl# to #Addl#Oth
do Ok-To-Pay-Update
else
do Get-Employee-Data
if $ErrorFound = 'N'
do Get-Addl-Max
move &Addl#Oth to #Addl#Oth
add 1 to #Addl#Oth
display 'Add-Pay-Earnings 1'
do Add-Pay-Earnings
end-if
end-if
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Get-Addl-Max
!---------------------------------------------------------------------------------------
display 'beginning procedure get-addl-max'
begin-SELECT
MAX(L.ADDL#) &Addl#Oth
FROM PS_PAY_EARNINGS L
where L.COMPANY = $Company
and L.PAYGROUP = $PayGroup
and L.PAY_END_DT = $Pay_End_Dt
and L.OFF_CYCLE = 'N'
and L.PAGE# = #Page#Oth
and L.LINE# = #Line#Oth
end-SELECT
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Add-Pay-Oth-Earns
!---------------------------------------------------------------------------------------
let #Total_Dist_Pct = 0
let #Original_Amount = #Amount
let #Cum_Amount = 0
let #Original_Hours = #Hours
let #Cum_Hours = 0
move 'N' to $Multiple_States
move '**' to $D.State
move ' ' to $D.Locality
begin-select
D.STATE
D.LOCALITY
D.DIST_PCT
let $D.State = rtrim(&D.State, ' ')
if isnull(&D.Locality) = 1
let $D.Locality = ' '
else
let $D.Locality = &D.Locality
end-if
let #Total_Dist_Pct = #Total_Dist_Pct + &D.Dist_Pct
if #Total_Dist_Pct < 100
if $Multiple_States = 'Y'
add 1 to #Addl#Oth
display 'Add-Pay-Earnings 2'
do Add-Pay-Earnings
end-if
move 'Y' to $Multiple_States
let #Pct_Decimal = &D.Dist_Pct / 100
let #Amount = #Original_Amount * #Pct_Decimal
let #Hours = #Original_Hours * #Pct_Decimal
end-if
if $Multiple_States = 'Y' and #Total_Dist_Pct = 100
add 1 to #Addl#Oth
display 'Add-Pay-Earnings 3'
do Add-Pay-Earnings
let #Amount = #Original_Amount - #Cum_Amount
let #Hours = #Original_Hours - #Cum_Hours
else
let #Cum_Amount = #Cum_Amount + #Amount
let #Cum_Hours = #Cum_Hours + #Hours
end-if
do Insert-Pay-Oth-Earns
FROM PS_TAX_DISTRIB D
where D.EMPLID = $Emplid
and D.EMPL_RCD# = #Empl_RcdNo
and D.EFFDT =
(SELECT MAX(X.EFFDT)
from PS_TAX_DISTRIB X
where X.EMPLID = D.EMPLID
and X.EMPL_RCD# = D.EMPL_RCD#
and X.EFFDT <= $Pay_End_Dt)
end-select
if $D.State = '**'
do Insert-Pay-Oth-Earns
end-if
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Insert-Pay-Oth-Earns
!---------------------------------------------------------------------------------------
move 'PS_PAY_OTH_EARNS ' TO $ERR_MESSAGE
display 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ'
display 'Verifying Pay_Oth_Earns values'
display 'COMPANY: ' noline
display $Company
display 'PAYGROUP: ' noline
display $paygroup
display 'PAY_END_DT: ' noline
display $pay_end_dt
display 'OFF_CYCLE: ' noline
display 'N'
display 'PAGE#: ' noline
display #Page#Oth
display 'LINE#: ' noline
display #Line#Oth
display 'ADDL#: ' noline
display #Addl#Oth
display 'ERNCD: ' noline
display $ErnCd
display 'SEPCHK: ' noline
display #Sepchk
display 'JOB_PAY: ' noline
display 'N'
display 'OTH_HRS: ' noline
display #poe_Hrs
display 'OTH_PAY: ' noline
display #Amount
display 'OTH_EARNS: ' noline
display '0'
display 'ADD_GROSS: ' noline
display 'N'
display 'TAX_METHOD: ' noline
display $TaxMethod
display 'ADDL_SEQ: ' noline
display '01'
display 'TL_SOURCE: ' noline
display 'space'
display 'RATE_USED: ' noline
display 'H'
display 'BAS_CREDIT_SW: ' noline
display 'N'
display 'HRS_DIST_SW: ' noline
display 'N'
display 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ'
begin-SQL ON-ERROR=SQL_ERROR_TRAP
INSERT INTO PS_PAY_OTH_EARNS (COMPANY,
PAYGROUP,
PAY_END_DT,
OFF_CYCLE,
PAGE#,
LINE#,
ADDL#,
ERNCD,
SEPCHK,
JOB_PAY,
OTH_HRS,
OTH_PAY,
OTH_EARNS,
ADD_GROSS,
TAX_METHOD,
ADDL_SEQ,
TL_SOURCE,
RATE_USED,
BAS_CREDIT_SW,
HRS_DIST_SW)
VALUES ( $Company,
$paygroup,
$pay_end_dt,
'N',
#Page#Oth,
#Line#Oth,
#Addl#Oth,
$ErnCd,
#Sepchk,
'N',
#oe_Hrs,
#Amount,
#HrlyRt,
'N',
$TaxMethod,
01,
' ',
'H',
'N',
'N')
end-SQL
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Ok-To-Pay-Update
!---------------------------------------------------------------------------------------
begin-SQL
UPDATE PS_PAY_EARNINGS
SET OK_TO_PAY='Y'
WHERE COMPANY = $company
AND PAYGROUP = $paygroup
AND PAY_END_DT = $Pay_End_Dt
AND OFF_CYCLE = 'N'
AND PAGE# = #Page#Oth
AND LINE# = #Line#Oth
AND ADDL# = #Addl#Oth
end-SQL
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Pay-Status-Update
!---------------------------------------------------------------------------------------
begin-SQL
DELETE FROM PS_TMP_WK_ERNS_TBL
end-SQL
begin-SQL
INSERT INTO PS_TMP_WK_ERNS_TBL (
COMPANY,
PAYGROUP,
PAY_END_DT,
OFF_CYCLE,
PAGE#,
LINE#,
ADDL#,
SEPCHK,
EMPLID,
EMPL_RCD#,
EARNS_END_DT,
EARNS_BEGIN_DT,
ADDLPAY_REASON,
DISABLE_DIR_DEP,
GROSSUP,
PAY_LINE_STATUS,
OK_TO_PAY,
JOB_PAY,
ACCT_CD,
GL_PAY_TYPE,
DEPTID,
JOBCODE,
POSITION_NBR,
SHIFT,
SHIFT_RT,
HOURLY_RT,
FLSA_RT,
RATE_USED,
FLSA_ELIGIBLE,
ERNCD_REG_HRS,
ERNCD_OT_HRS,
REG_PAY_HRS,
REG_HRS,
OT_HRS,
REG_HRLY_EARNS,
OT_HRLY_EARNS,
ERNCD_REG_EARNS,
REG_PAY,
REG_EARNS,
REG_EARN_HRS,
DED_TAKEN,
DED_SUBSET_ID,
STATE,
LOCALITY,
PAY_FREQUENCY,
TAX_PERIODS,
TAX_METHOD,
ADDL_TAXES,
OVERRIDE_HOURLY_RT,
BENEFIT_RCD#,
SINGLE_CHECK_USE,
TL_SOURCE,
PAY_SHEET_SRC,
EI_PRIOR_PD_CORR,
FICA_STATUS_EE,
PAID_PRDS_PER_YEAR)
select COMPANY,
PAYGROUP,
PAY_END_DT,
OFF_CYCLE,
PAGE#,
LINE#,
ADDL#,
SEPCHK,
EMPLID,
EMPL_RCD#,
EARNS_END_DT,
EARNS_BEGIN_DT,
ADDLPAY_REASON,
DISABLE_DIR_DEP,
GROSSUP,
PAY_LINE_STATUS,
OK_TO_PAY,
JOB_PAY,
ACCT_CD,
GL_PAY_TYPE,
DEPTID,
JOBCODE,
POSITION_NBR,
SHIFT,
SHIFT_RT,
HOURLY_RT,
0,
'H',
FLSA_ELIGIBLE,
ERNCD_REG_HRS,
ERNCD_OT_HRS,
REG_PAY_HRS,
REG_HRS,
OT_HRS,
REG_HRLY_EARNS,
OT_HRLY_EARNS,
ERNCD_REG_EARNS,
REG_PAY,
REG_EARNS,
REG_EARN_HRS,
DED_TAKEN,
DED_SUBSET_ID,
STATE,
LOCALITY,
PAY_FREQUENCY,
TAX_PERIODS,
TAX_METHOD,
ADDL_TAXES,
OVERRIDE_HOURLY_RT,
BENEFIT_RCD#,
SINGLE_CHECK_USE,
TL_SOURCE,
PAY_SHEET_SRC,
EI_PRIOR_PD_CORR,
FICA_STATUS_EE,
PAID_PRDS_PER_YEAR
from PS_PAY_EARNINGS
where COMPANY = $company
and PAYGROUP = $paygroup
and PAY_END_DT = $Pay_End_Dt
and OFF_CYCLE = 'N'
and PAGE# = #Page#Oth
and LINE# = #Line#Oth
end-sql
begin-sql
UPDATE PS_PAY_EARNINGS
SET PAY_LINE_STATUS='U'
where COMPANY = $company
and PAYGROUP = $paygroup
and PAY_END_DT = $Pay_End_Dt
and OFF_CYCLE = 'N'
and PAGE# = #Page#Oth
and LINE# = #Line#Oth
and EXISTS
(SELECT 'x'
from PS_COB_WK_ERNS_TBL
where COMPANY = $company
and PAYGROUP = $paygroup
and PAY_END_DT = $Pay_End_Dt
and OFF_CYCLE = 'N'
and PAGE# = #Page#Oth
and LINE# = #Line#Oth
and PAY_LINE_STATUS = 'C')
end-sql
begin-sql
DELETE from PS_TMP_WK_ERNS_TBL
end-sql
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Add-New-Line
!---------------------------------------------------------------------------------------
display 'Inside Add New Line Procedure'
do Get-Employee-Data
if $ErrorFound = 'N'
if #Page# <> #Page#Save
move #Page# to #Page#Save
do Add-Page
move 1 to #line#
end-if
move #Line# to #Line#Save
do Add-Line
move #Page#Save to #Page#Oth
move #Line#Save to #Line#Oth
move 0 to #Addl#Oth
display 'Add-Pay-Earnings 4'
do Add-Pay-Earnings
if #line# = 7
add 1 to #Page#
move 1 to #line#
else
add 1 to #line#
end-if
end-if
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Get-Employee-Data
!---------------------------------------------------------------------------------------
display 'beginning procedure get-employee-data'
move 'N' to $Found
begin-select
A.EMPLID
A.NAME
B.DED_TAKEN
B.DED_SUBSET_ID
B.BENEFIT_RCD#
C.JOBCODE
C.DEPTID
C.SAL_ADMIN_PLAN
C.GRADE
C.STEP
C.GL_PAY_TYPE
C.POSITION_NBR
C.SHIFT
C.EMPL_TYPE
C.LOCATION
C.HOURLY_RT
C.EFFDT
C.FICA_STATUS_EE
move 'Y' to $Found
do Get-Compensation-RateGet-First-Work-State
display $found
display $emplid
display #empl_rcdno
display $pay_end_dt
FROM PS_PERSONAL_DATA A,
PS_EMPLOYMENT B,
PS_JOB C
WHERE A.EMPLID = $EmplID
AND B.EMPLID = A.EMPLID
AND C.EMPLID = A.EMPLID
AND B.EMPL_RCD# = C.EMPL_RCD#
AND B.EMPL_RCD# = #Empl_RcdNo
AND C.EFFDT =
(SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID = $EmplID
AND EMPL_RCD# = #Empl_RcdNo
AND EFFDT <= $Pay_End_Dt)
AND C.EFFSEQ =
(SELECT MAX(EFFSEQ)
FROM PS_JOB
WHERE EMPLID = $EmplID
AND EMPL_RCD# = #Empl_RcdNo
AND EFFDT = C.EFFDT)
end-select
move &B.Benefit_Rcd# to $Benefit_Rcd#
if RTRIM($DedTaken, ' ') = ''
move &B.Ded_Taken to $DedTaken
move &B.Ded_subset_id to $DedSubsetId
end-if
if RTRIM($HrlyRt, ' ') = ''
move &C.Hourly_Rt to #HrlyRt
end-if
display $found
if $Found = 'N'
do Error-Found
add 1 to #ErrorCount
print 'EmplID Not Found(Personal_Data, Employment and Job)' (,35)
end-if
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Get-First-Work-State
!---------------------------------------------------------------------------------------
move '**' to $D.State
move ' ' to $D.Locality
move 'N' to $Read-First
begin-select
T.STATE
T.LOCALITY
if $Read-First = 'N'
let $D.State = rtrim(&T.State, ' ')
if isnull(&T.Locality) = 1
let $D.Locality = ' '
else
let $D.Locality = &T.Locality
end-if
move 'Y' to $Read-First
end-if
FROM PS_TAX_DISTRIB T
where T.EMPLID = $Emplid
and T.EMPL_RCD# = #Empl_RcdNo
and T.EFFDT =
(SELECT MAX(Y.EFFDT)
from PS_TAX_DISTRIB Y
where Y.EMPLID = T.EMPLID
and Y.EMPL_RCD# = T.EMPL_RCD#
and Y.EFFDT <= $Pay_End_Dt)
end-select
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Add-Page
!---------------------------------------------------------------------------------------
move 'PS_PAY_PAGE ' TO $ERR_MESSAGE
begin-SQL ON-ERROR=SQL_ERROR_TRAP
INSERT INTO PS_PAY_PAGE ( COMPANY,
PAYGROUP,
PAY_END_DT,
OFF_CYCLE,
PAGE#,
CONFIRMED,
BALNC_LINES,
BALNC_LINES_OK,
BALNC_REG_PAY_HRS,
BALNC_OT_HRS,
BALNC_OTH_HRS,
BALNC_REG_PAY,
BALNC_OTH_EARNS,
BALNC_HOURLY_RT,
SINGLE_CHECK_USE,
PAY_SHEET_SRC)
VALUES ( $Company,
$paygroup,
$pay_end_dt,
'N',
#page#,
'N',
0,
0,
0,
0,
0,
0,
0,
0,
'N',
'S')
end-SQL
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Add-Line
!---------------------------------------------------------------------------------------
move 'PS_PAY_LINES ' TO $ERR_MESSAGE
begin-SQL on-error=SQL_ERROR_TRAP
INSERT INTO PS_PAY_LINE ( COMPANY,
PAYGROUP,
PAY_END_DT,
OFF_CYCLE,
PAGE#,
LINE#,
EMPLID,
EMPL_RCD#,
BENEFIT_RCD#,
NAME,
JOB_CHANGE,
PARTIAL_PERIOD,
CONFIRMED,
EMPL_TYPE,
LOCATION,
DEPTID,
BENEFIT_PROGRAM,
MANUAL_CHECK,
FORM_ID,
CHECK#,
CHECK_DT,
TOTAL_GROSS,
NET_PAY,
PAYCHECK_ADJUST,
SINGLE_CHECK_USE,
TL_SOURCE,
PAY_SHEET_SRC)
VALUES ( $company,
$paygroup,
$Pay_End_Dt,
'N',
#page#,
#line#,
$emplid,
#Empl_RcdNo,
&B.Benefit_Rcd#,
&A.name,
'N',
'N',
'N',
&C.empl_type,
&C.location,
&C.deptid,
' ',
'N',
' ',
0,
NULL,
0,
0,
'N',
'N',
' ',
'S')
end-SQL
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Add-Pay-Earnings
!---------------------------------------------------------------------------------------
move 'Y' to $Addl_Taxes ! default, take additional taxes
! change to 'N' to not take addl taxes
move 'PS_PAY_EARNINGS ' TO $ERR_MESSAGE
display 'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV'
display 'Verifying Pay_Earnings values'
display 'COMPANY: ' noline
display $Company
display 'PAYGROUP: ' noline
display $paygroup
display 'PAY_END_DT: ' noline
display $pay_end_dt
display 'OFF_CYCLE: ' noline
display 'N'
display 'PAGE#: ' noline
display #Page#Oth
display 'LINE#: ' noline
display #Line#Oth
display 'ADDL#: ' noline
display #Addl#Oth
display 'SEPCHK: ' noline
display #SepChk
display 'EMPLID: ' noline
display $emplid
display 'EMPL_RCD#: ' noline
display #Empl_RcdNo
display 'BENEFIT_RCD#: ' noline
display &B.Benefit_Rcd#
display 'EARNS_BEGIN_DT: ' noline
display $Pay_Begin_Dt
display 'EARNS_END_DT: ' noline
display $pay_end_dt
display 'ADDLPAY_REASON: ' noline
display $Reason
display 'DISABLE_DIR_DEP: ' noline
display $DisDir
display 'GROSSUP: ' noline
display $GrossUp
display 'PAY_LINE_STATUS: ' noline
display 'I'
display 'OK_TO_PAY: ' noline
display 'Y'
display 'JOB_PAY: ' noline
display 'N'
display 'SINGLE_CHECK_USE: ' noline
display 'N'
display 'ACCT_CD: ' noline
display 'space' !RC 1999-03-16
display 'GL_PAY_TYPE: ' noline
display &C.gl_pay_type
display 'DEPTID: ' noline
display &C.deptid
display 'JOBCODE: ' noline
display &C.jobcode
display 'POSITION_NBR: ' noline
display &C.Position_Nbr
display 'SHIFT: ' noline
display &C.shift
display 'SHIFT_RT: ' noline
display '0'
display 'HOURLY_RT: ' noline
display #HrlyRt
display 'FLSA_RT: ' noline
display '0'
display 'RATE_USED: ' noline
display 'H'
display 'FLSA_ELIGIBLE: ' noline
display 'N'
display 'ERNCD_REG_HRS: ' noline
display &H.erncd_reg_hrs
display 'ERNCD_OT_HRS: ' noline
display &H.erncd_ot_hrs
display 'REG_PAY_HRS: ' noline
display #Reg_hrs
display 'REG_HRS: ' noline
display #Reg_hrs
display 'OT_HRS: ' noline
display '0'
display 'REG_HRLY_EARNS: ' noline
display '0'
display 'OT_HRLY_EARNS: ' noline
display '0'
display 'ERNCD_REG_EARNS: ' noline
display &H.erncd_reg_earns
display 'REG_PAY: ' noline
display '0'
display 'REG_EARNS: ' noline
display '0'
display 'REG_EARN_HRS: ' noline
display '0'
display 'DED_TAKEN: ' noline
display $DedTaken
display 'DED_SUBSET_ID: ' noline
display $DedSubsetId
display 'STATE: ' noline
display $D.State
display 'LOCALITY: ' noline
display $D.Locality
display 'PAY_FREQUENCY: ' noline
display $Freq
display 'TAX_PERIODS: ' noline
display #Periods
display 'TAX_METHOD: ' noline
display $TaxMethod
display 'ADDL_TAXES: ' noline
display $Addl_Taxes
display 'OVERRIDE_HOURLY_RT: ' noline
display 'N'
display 'TL_SOURCE: ' noline
display 'space'
display 'PAY_SHEET_SRC: ' noline
display 'S'
display 'EI_PRIOR_PD_CORR: ' noline
display 'N'
display 'FICA_STATUS_EE: ' noline
display $fica_status_ee
display 'PAID_PRDS_PER_YEAR: ' noline
display #paid_periods
display 'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV'
begin-SQL on-error=SQL_ERROR_TRAP
INSERT INTO PS_PAY_EARNINGS ( COMPANY,
PAYGROUP,
PAY_END_DT,
OFF_CYCLE,
PAGE#,
LINE#,
ADDL#,
SEPCHK,
EMPLID,
EMPL_RCD#,
BENEFIT_RCD#,
EARNS_BEGIN_DT,
EARNS_END_DT,
ADDLPAY_REASON,
DISABLE_DIR_DEP,
GROSSUP,
PAY_LINE_STATUS,
OK_TO_PAY,
JOB_PAY,
SINGLE_CHECK_USE,
ACCT_CD,
GL_PAY_TYPE,
DEPTID,
JOBCODE,
POSITION_NBR,
SHIFT,
SHIFT_RT,
HOURLY_RT,
FLSA_RT,
RATE_USED,
FLSA_ELIGIBLE,
ERNCD_REG_HRS,
ERNCD_OT_HRS,
REG_PAY_HRS,
REG_HRS,
OT_HRS,
REG_HRLY_EARNS,
OT_HRLY_EARNS,
ERNCD_REG_EARNS,
REG_PAY,
REG_EARNS,
REG_EARN_HRS,
DED_TAKEN,
DED_SUBSET_ID,
STATE,
LOCALITY,
PAY_FREQUENCY,
TAX_PERIODS,
TAX_METHOD,
ADDL_TAXES,
OVERRIDE_HOURLY_RT,
TL_SOURCE,
PAY_SHEET_SRC,
EI_PRIOR_PD_CORR,
FICA_STATUS_EE,
PAID_PRDS_PER_YEAR)
VALUES ( $Company,
$paygroup,
$pay_end_dt,
'N',
#Page#Oth,
#Line#Oth,
#Addl#Oth,
#SepChk,
$emplid,
#Empl_RcdNo,
&B.Benefit_Rcd#,
$Pay_Begin_Dt,
$pay_end_dt,
$Reason,
$DisDir,
$GrossUp,
'I',
'Y',
'N',
'N',
' ', !RC 1999-03-16
&C.gl_pay_type,
&C.deptid,
&C.jobcode,
&C.Position_Nbr,
&C.shift,
0,
#HrlyRt,
0,
'H',
'N',
&H.erncd_reg_hrs,
&H.erncd_ot_hrs,
#Reg_hrs,
#Reg_hrs,
0,
0,
0,
&H.erncd_reg_earns,
0,
0,
0,
$DedTaken,
$DedSubsetId,
$D.State,
$D.Locality,
$Freq,
#Periods,
$TaxMethod,
$Addl_Taxes,
'N',
' ',
'S',
'N',
$fica_status_ee,
#paid_periods)
end-sql
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Edit-EmplID
!---------------------------------------------------------------------------------------
move 'N' to $Found
begin-select
'x'
move 'Y' to $Found
FROM PS_PERSONAL_DATA A, PS_EMPLOYMENT B, PS_JOB C
WHERE A.EMPLID = B.EMPLID
AND A.EMPLID = $EmplID
AND A.EMPLID = C.EMPLID
AND B.EMPL_RCD# = $Empl_RcdNo
AND B.EMPL_RCD# = C.EMPL_RCD#
AND C.EFFDT =
(SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID = $EmplID
AND EMPL_RCD# = B.EMPL_RCD#
AND EFFDT <= $Pay_End_Dt)
AND C.EFFSEQ =
(SELECT MAX(EFFSEQ)
FROM PS_JOB
WHERE EMPLID = $EmplID
AND EMPL_RCD# = B.EMPL_RCD#
AND EFFDT = C.EFFDT)
end-select
display $found
display $emplid
display $empl_rcdno
display $pay_end_dt
if $Found = 'N'
do Error-Found
print 'EmplID Not Found(Personal_Data, Employment and Job)' (,21)
end-if
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Edit-ErnCd
!---------------------------------------------------------------------------------------
move 'N' to $Found
begin-select
A.EFF_STATUS
move 'Y' to $Found
FROM PS_EARNINGS_TBL A
WHERE A.ERNCD = $ErnCd
AND A.EFFDT =
(SELECT MAX(B.EFFDT)
FROM PS_EARNINGS_TBL B
WHERE B.ERNCD = $ErnCd
AND B.EFFDT <= $Pay_End_Dt)
end-select
if $Found = 'N'
do Error-Found
print 'Invalid Earnings Code=' (,21)
print $ErnCd ()
else
if &A.Eff_Status = 'I'
do Error-Found
print 'Inactive Earnings Code=' (,21)
print $ErnCd ()
end-if
end-if
end-procedure
!---------------------------------------------------------------------------------------
begin-procedure Edit-Xlat
!---------------------------------------------------------------------------------------
move 'N' to $Found
begin-select
C.EFF_STATUS
move 'Y' to $Found
FROM XLATTABLE C
where C.FIELDNAME = $FieldName
and C.FIELDVALUE = $FieldValue
and C.EFFDT =
(SELECT MAX(B.EFFDT)
from XLATTABLE B
where B.FIELDNAME = $FieldName
and B.FIELDVALUE = $FieldValue
and B.EFFDT <= $Pay_End_Dt)
end-select
if $Found = 'N'
do Error-Found
print 'Invalid ' (,21)
print $FieldPrint ()
print '=' ()
print $FieldValue ()
else
if &A.Eff_Status = 'I'
do Error-Found
print 'Inactive ' (,21)
print $FieldPrint ()
print '=' ()
print $FieldValue ()
end-if
end-if
end-procedure
!---------------------------------------------------------------------------------------
BEGIN-PROCEDURE SQL_ERROR_TRAP
!---------------------------------------------------------------------------------------
SHOW $sql-error 'occurred on employee: ' $emplid $ERR_MESSAGE
print $emplid (+1,1)
print '- error occurred at: ' (0,10)
print $err_message (0,40)
END-PROCEDURE
!---------------------------------------------------------------------------------------
begin-procedure Error-Found
!---------------------------------------------------------------------------------------
move 'Y' to $ErrorFound
do Format-Number(#InputTran, $out, '99999')
print $out (+1,1)
print $EmplID (,8)
end-procedure
#Include 'tranctrl.sqc' !Common Transaction Control Procedures
#Include 'reset.sqc' !Reset printer procedure
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'datetime.sqc' !Routines for date and time formatting
#Include 'datemath.sqc' !Routines date calculations
#Include 'number.sqc' !Routines to format numbers
#Include 'payrnctl.sqc' !Select-Parameters
#Include 'stdapi.sqc' !Get_Run_Control Procedure