I'm consulting at a national company.
According to auditors in the State of Washington, the QTD hours sent from headquarters are inaccurate. There are too many hours sent to the state. They are only interested in hours worked. They have determined that earnings sent to the state includes earnings other than regular ones (such as vacation, sick, etc.).
Altho I have been in the field for a while, I have not completely wrapped myself around payroll and earnings codes; therefore, you might need to ask more questions of me. Please feel free to do so.
It is expected that I will come up with an answer for them by Monday morning.
Please, please help! Enclosed is the SQR.
I appreciate you!!!!
!*****************************************************************
! LPY2650: QTD Hours and Gross for Washington Report *
!*****************************************************************
!*****************************************************************
! PROGRAM DESCRIPTION *
!*****************************************************************
! FUNCTION : This program produces a report that lists each *
! employee who has paid taxes in the state of *
! Washington during a given quarter. *
! *
! DB2 ACCESS: PS_LOCATION_TBL SELECT *
! PS_JOB SELECT *
! PS_JOBCODE_TBL SELECT *
! PS_EARNINGS_BAL SELECT *
! *
! FILES USED: NONE *
! *
! REPORTS : QTD Hours and Gross for Washington Report *
!*****************************************************************
!*****************************************************************
! MODIFICATION LOG *
!*****************************************************************
! DATE ANALYST DESCRIPTION OF MODIFICATIONS *
! ---------- ------------------ -------------------------------- *
!*****************************************************************
! END OF MODIFICATION LOG *
!*****************************************************************
#Include 'setenv.sqc' !Set environment
#Include 'setup31.sqc' !Printer and page-size initialization
!Portrait orientation
!*****************************************************************
!Begin Defines *
! This 'section' defines the locations of the columns that *
! are used later in the SQR. *
!*****************************************************************
!Report Column Definitions
#Define Company 001
#Define Location 012
#Define WkrCompCd 024
#Define TxGrsQTD 037
#Define HrsQTD 059
#Define EECount 080
!End Defines
!*****************************************************************
Begin-Report
! Driver procedure. *
!*****************************************************************
Do INITIALIZATION
Do PROCESS-MAIN
Do FINALIZATION
End-Report
!*****************************************************************
Begin-Heading 07
! This section defines what the headings on the report look *
! like. *
!*****************************************************************
#Include 'stdhdg01.sqc'
Print 'Quarter End Date:' (-1,{Company} )
Print $Qtr-End-Date ( ,+1 )
Print ' TAX' (+3,{Location} )
Print ' WORKERS' ( ,{WkrCompCd} )
Print '<-------------------- QUARTER TO DATE --------------------->'
( ,{TxGrsQTD} )
Print 'COMPANY' (+1,{Company} )
Print 'LOCATION' ( ,{Location} )
Print 'COMP CODE' ( ,{WkrCompCd} )
Print ' TAXABLE GROSS' ( ,{TxGrsQTD} )
Print ' HOURS WORKED' ( ,{HrsQTD} )
Print ' EMPLOYEE COUNT' ( ,{EECount} )
Print '<----->' (+1,{Company} )
Print '<------>' ( ,{Location} )
Print '<------->' ( ,{WkrCompCd} )
Print '<--------------->' ( ,{TxGrsQTD} )
Print '<--------------->' ( ,{HrsQTD} )
Print '<--------------->' ( ,{EECount} )
End-Heading
!*****************************************************************
Begin-Procedure PROCESS-MAIN
! This procedure selects all employees who are found on the *
! tables in the select that have work in stores in the state of*
! Washington during the respective quarter. *
! *
! Each record that is found in the select is then setup and *
! printed to the report. *
! *
! If a change in Company and/or Location is encountered the *
! appropriate breaks will occur. *
! *
! AND A.STATE = 'WA' a State row for Washington state *
! AND D.SPCL_BALANCE = 'N' a row that not a Special Accumulator*
! AND D.ERNCD IN ('REG','INV','TRA') a row that is a Regular *
! or Inventory or Training *
! Earnings Code *
!*****************************************************************
BEGIN-SELECT
B.COMPANY () ON-BREAK PRINT=NEVER LEVEL=1 AFTER=COMPANY-CHANGE
A.LOCATION () ON-BREAK PRINT=NEVER LEVEL=2 AFTER=LOCATION-CHANGE
C.WORKERS_COMP_CD
SUM(D.GRS_QTD) &GRS_QTD
SUM(D.HRS_QTD) &HRS_QTD
COUNT(DISTINCT B.EMPLID) &COUNT
Let $Company = &B.COMPANY
Let $Location = &A.LOCATION
Let $Workers-Comp-Code = &C.WORKERS_COMP_CD
Let #Gross-Erns-OT = 0 !CR 4536
Let #Hours-Worked-OT = 0 !CR 4536
Let #Hrs-Worked-QTD = 0 !CR 4536
Let #Std-Hours-SAL = 0 !CR 4536
Let #TxGrs-QTD = 0 !CR 4536
!CR 4536
Let $Earnings-Type = 'SAL' !CR 4536
Let $Where-Clause = $Where-For-Hrs-Wrkd-SAL !CR 4536
Do GET-HOURS !CR 4536
!CR 4536
Let $Earnings-Type = 'OT' !CR 4536
Let $Where-Clause = $Where-For-Hrs-Wrkd-OT !CR 4536
Do GET-HOURS !CR 4536
!CR 4536
Let #TxGrs-QTD = &GRS_QTD - #Gross-Erns-OT !CR 4536
Let $TxGrs-QTD = #TxGrs-QTD !CR 4536
!CR 4536
Let #Hrs-Worked-QTD = ( &HRS_QTD !CR 4536
- #Hours-Worked-OT !CR 4536
- #Std-Hours-SAL ) !CR 4536
Let $Hrs-Worked-QTD = #Hrs-Worked-QTD !CR 4724
Let $Count = &COUNT
Let #Location-Hours-QTD = #Location-Hours-QTD + #Hrs-Worked-QTD
Let #Location-TxGrs-QTD = #Location-TxGrs-QTD + #TxGrs-QTD
Let #Location-Count = #Location-Count + &Count
Let #Company-Hours-QTD = #Company-Hours-QTD + #Hrs-Worked-QTD
Let #Company-TxGrs-QTD = #Company-TxGrs-QTD + #TxGrs-QTD
Let #Company-Count = #Company-Count + &Count
Let #Grand-Hours-QTD = #Grand-Hours-QTD + #Hrs-Worked-QTD
Let #Grand-TxGrs-QTD = #Grand-TxGrs-QTD + #TxGrs-QTD
Let #Grand-Count = #Grand-Count + &Count
Do PRINT-REPORT
FROM PS_LOCATION_TBL A,
PS_JOB B,
PS_JOBCODE_TBL C,
PS_EARNINGS_BAL D
WHERE B.SETID_LOCATION = A.SETID
AND B.LOCATION = A.LOCATION
AND B.SETID_JOBCODE = C.SETID
AND B.JOBCODE = C.JOBCODE
AND B.EMPLID = D.EMPLID
AND B.COMPANY = D.COMPANY
AND A.EFFDT = (SELECT MAX(A1.EFFDT)
FROM PS_LOCATION_TBL A1
WHERE A1.SETID = A.SETID
AND A1.LOCATION = A.LOCATION
AND A1.EFFDT <= $Thru-Date)
AND B.EFFDT = (SELECT MAX(B1.EFFDT)
FROM PS_JOB B1
WHERE B1.EMPLID = B.EMPLID
AND B1.EMPL_RCD = B.EMPL_RCD
AND B1.EFFDT <= $Thru-Date)
AND B.EFFSEQ = (SELECT MAX(B2.EFFSEQ)
FROM PS_JOB B2
WHERE B2.EMPLID = B.EMPLID
AND B2.EMPL_RCD = B.EMPL_RCD
AND B2.EFFDT = B.EFFDT)
AND C.EFFDT = (SELECT MAX(C1.EFFDT)
FROM PS_JOBCODE_TBL C1
WHERE C1.SETID = C.SETID
AND C1.JOBCODE = C.JOBCODE
AND C1.EFFDT <= B.EFFDT)
AND D.BALANCE_PERIOD = (SELECT MAX(D1.BALANCE_PERIOD)
FROM PS_EARNINGS_BAL D1
WHERE D1.EMPLID = D.EMPLID
AND D1.COMPANY = D.COMPANY
AND D1.BALANCE_ID = D.BALANCE_ID
AND D1.BALANCE_YEAR = D.BALANCE_YEAR
AND D1.BALANCE_QTR = D.BALANCE_QTR
AND D1.EMPL_RCD = D.EMPL_RCD
AND D1.SPCL_BALANCE = D.SPCL_BALANCE
AND D1.ERNCD = D.ERNCD)
AND A.STATE = 'WA'
AND A.LOCATION NOT IN ('0889','0898') !CR 4536
AND D.BALANCE_ID = $Calendar_Year_ID
AND D.BALANCE_YEAR = &TX.BALANCE_YEAR
AND D.BALANCE_QTR = &TX.BALANCE_QTR
AND D.SPCL_BALANCE = 'N'
AND D.ERNCD IN ('REG','SAL','INV','TRA','RGS') !CR 4536
GROUP BY B.COMPANY, A.LOCATION, C.WORKERS_COMP_CD
ORDER BY B.COMPANY, A.LOCATION, C.WORKERS_COMP_CD
WITH UR
END-SELECT
End-Procedure PROCESS-MAIN
!*****************************************************************
Begin-Procedure GET-HOURS
!*****************************************************************
BEGIN-SELECT !CR 4536
SUM(H.GRS_QTD) &GRS_EARNINGS_QTD !CR 4536
SUM(H.HRS_QTD) &HOURS_QTD !CR 4536
Evaluate $Earnings-Type !CR 4536
When = 'SAL' !CR 4536
Let #Std-Hours-SAL = #Std-Hours-SAL + !CR 4536
&HOURS_QTD !CR 4536
Break !CR 4536
When = 'OT' !CR 4536
Let #Hours-Worked-OT = #Hours-Worked-OT + !CR 4536
&HOURS_QTD !CR 4536
Let #Gross-Erns-OT = #Gross-Erns-OT + !CR 4536
&GRS_EARNINGS_QTD !CR 4536
Break !CR 4536
When-Other !CR 4536
Break !CR 4536
End-Evaluate !CR 4536
FROM PS_LOCATION_TBL E, !CR 4536
PS_JOB F, !CR 4536
PS_JOBCODE_TBL G, !CR 4536
PS_EARNINGS_BAL H !CR 4536
WHERE F.SETID_JOBCODE = E.SETID !CR 4536
AND F.LOCATION = E.LOCATION !CR 4536
AND F.SETID_JOBCODE = G.SETID !CR 4536
AND F.JOBCODE = G.JOBCODE !CR 4536
AND F.EMPLID = H.EMPLID !CR 4536
AND F.COMPANY = H.COMPANY !CR 4536
AND E.EFFDT = !CR 4536
(SELECT MAX(E1.EFFDT) !CR 4536
FROM PS_LOCATION_TBL E1 !CR 4536
WHERE E1.SETID = E.SETID !CR 4536
AND E1.LOCATION = E.LOCATION !CR 4536
AND E1.EFFDT <= $Thru-Date) !CR 4536
AND F.EFFDT = !CR 4536
(SELECT MAX(F1.EFFDT) !CR 4536
FROM PS_JOB F1 !CR 4536
WHERE F1.EMPLID = F.EMPLID !CR 4536
AND F1.EMPL_RCD = F.EMPL_RCD !CR 4536
AND F1.EFFDT <= $Thru-Date) !CR 4536
AND F.EFFSEQ = !CR 4536
(SELECT MAX(F2.EFFSEQ) !CR 4536
FROM PS_JOB F2 !CR 4536
WHERE F2.EMPLID = F.EMPLID !CR 4536
AND F2.EMPL_RCD = F.EMPL_RCD !CR 4536
AND F2.EFFDT = F.EFFDT) !CR 4536
AND G.EFFDT = !CR 4536
(SELECT MAX(G1.EFFDT) !CR 4536
FROM PS_JOBCODE_TBL G1 !CR 4536
WHERE G1.SETID = G.SETID !CR 4536
AND G1.JOBCODE = G.JOBCODE !CR 4536
AND G1.EFFDT <= F.EFFDT) !CR 4536
AND H.BALANCE_PERIOD = !CR 4536
(SELECT MAX(H1.BALANCE_PERIOD) !CR 4536
FROM PS_EARNINGS_BAL H1 !CR 4536
WHERE H1.EMPLID = H.EMPLID !CR 4536
AND H1.COMPANY = H.COMPANY !CR 4536
AND H1.BALANCE_ID = H.BALANCE_ID !CR 4536
AND H1.BALANCE_YEAR = H.BALANCE_YEAR !CR 4536
AND H1.BALANCE_QTR = H.BALANCE_QTR !CR 4536
AND H1.EMPL_RCD = H.EMPL_RCD !CR 4536
AND H1.SPCL_BALANCE = H.SPCL_BALANCE !CR 4536
AND H1.ERNCD = H.ERNCD) !CR 4536
AND F.COMPANY = $Company !CR 4536
AND E.LOCATION = $Location !CR 4536
AND G.WORKERS_COMP_CD = $Workers-Comp-Code !CR 4536
AND E.STATE = 'WA' !CR 4536
AND H.BALANCE_ID = $Calendar_Year_ID !CR 4536
AND H.BALANCE_YEAR = &TX.BALANCE_YEAR !CR 4536
AND H.BALANCE_QTR = &TX.BALANCE_QTR !CR 4536
AND H.SPCL_BALANCE = 'N' !CR 4536
\$Where-Clause\ !CR 4536
GROUP BY F.COMPANY, E.LOCATION, G.WORKERS_COMP_CD !CR 4536
WITH UR !CR 4536
END-SELECT !CR 4536
End-Procedure GET-HOURS !CR 4536
!*****************************************************************
Begin-Procedure PRINT-REPORT
! This procedure prints the detail lines. *
!*****************************************************************
If $Location-Change = 'Y' And #Current-Line > 9
Print $Blank-Line (+2, )
Let $Location-Change = 'N'
Else
End-If
Print $Company (+1, {Company} )
Print $Location ( , {Location} )
Print $Workers-Comp-Code ( , {WkrCompCd})
Print $TxGrs-QTD ( , {TxGrsQTD} ) Edit 99,999,999,999.99
Print $Hrs-Worked-QTD ( , {HrsQTD} ) Edit 99,999,999,999.99
Print $Count ( , {EECount} ) Edit 9,999,999,999,999
Add 1 To #Lines-Printed
End-Procedure PRINT-REPORT
!*****************************************************************
Begin-Procedure LOCATION-CHANGE
! This procedure executes when a change in Location has been *
! detected by the PROCESS-MAIN procedure. *
!*****************************************************************
Let $Location-TxGrs-QTD = #Location-TxGrs-QTD
Let $Location-Hours-QTD = #Location-Hours-QTD
Let $Location-Count = #Location-Count
Let $Location = LTrim ($Location, ' ')
Let $Location = RTrim ($Location, ' ')
Print '<===============>' (+1, {TxGrsQTD} )
Print '<===============>' ( , {HrsQTD} )
Print '<===============>' ( , {EECount} )
Print $Location (+1, {Company} )
Print 'Location Totals' ( , +1 )
Print $Location-TxGrs-QTD ( , {TxGrsQTD} ) Edit 99,999,999,999.99
Print $Location-Hours-QTD ( , {HrsQTD} ) Edit 99,999,999,999.99
Print $Location-Count ( , {EECount} ) Edit 9,999,999,999,999
Let #Location-TxGrs-QTD = 0
Let #Location-Hours-QTD = 0
Let #Location-Count = 0
Let $Location-Change = 'Y'
End-Procedure LOCATION-CHANGE
!*****************************************************************
Begin-Procedure COMPANY-CHANGE
! This procedure executes when a change in Company has been *
! detected by the PROCESS-MAIN procedure. *
!*****************************************************************
Let $Company-TxGrs-QTD = #Company-TxGrs-QTD
Let $Company-Hours-QTD = #Company-Hours-QTD
Let $Company-Count = #Company-Count
Let $Company = LTrim ($Company, ' ')
Let $Company = RTrim ($Company, ' ')
Print $Company (+1, {Company} )
Print 'Company Totals' ( , +1 )
Print $Company-TxGrs-QTD ( , {TxGrsQTD} ) Edit 99,999,999,999.99
Print $Company-Hours-QTD ( , {HrsQTD} ) Edit 99,999,999,999.99
Print $Company-Count ( , {EECount} ) Edit 9,999,999,999,999
Let #Company-TxGrs-QTD = 0
Let #Company-Hours-QTD = 0
Let #Company-Count = 0
Let $Company-Change = 'N'
If #End-File
Else
New-Page
End-If
End-Procedure COMPANY-CHANGE
!*****************************************************************
Begin-Procedure INITIALIZATION
! This procedure initializes all of the variables needed to *
! process the data. *
!*****************************************************************
Do STDAPI-INIT
Do INIT-DATETIME
Do INIT-NUMBER
Do GET-CURRENT-DATETIME
Do GET-CALENDAR-YEAR-ID
Do GET-TAX-REPORTING-RUN-CONTROLS
Do BUILD-EARNINGS-CODE-SQL !CR 4536
Move &TX.BALANCE_YEAR To #Year
Move &TX.BALANCE_QTR To #Quarter
Let $ReportID = 'PSPR265A'
Let $ReportTitle = 'QTD HOURS AND GROSS FOR WASHINGTON'
Do FORMAT-NUMBER (#Year, $Out, '9999' )
Let $Year = LTrim($Out,' ')
Evaluate #Quarter
When = 1
Move '03-31-' To $Qtr-End-Date
Move '-03-31' To $Thru-Date
Break
When = 2
Move '06-30-' To $Qtr-End-Date
Move '-06-30' To $Thru-Date
Break
When = 3
Move '09-30-' To $Qtr-End-Date
Move '-09-30' To $Thru-Date
Break
When = 4
Move '12-31-' To $Qtr-End-Date
Move '-12-31' To $Thru-Date
Break
End-Evaluate
Let $Qtr-End-Date = $Qtr-End-Date || $Year
Let $Thru-Date = $Year || $Thru-Date
Let $Blank-Line = ' '
Show $Blank-Line
Show $ReportID ' - ' $ReportTitle
Show 'The ' $ReportID ' began at ' $AsOfNow ' on ' $AsOfToday '.'
Show 'The data on the report will be for the ' $Calendar_Year_ID
Show ' Quarter ending ' $Qtr-End-Date '.'
Show 'Processing balances for ' $RptQtrYr '.'
Show $Blank-Line
End-Procedure INITIALIZATION
!*****************************************************************
Begin-Procedure BUILD-EARNINGS-CODE-SQL
!*****************************************************************
Let $Where-For-Hrs-Wrkd-OT = 'AND H.ERNCD IN (' !CR 4536
Let $Where-For-Hrs-Wrkd-SAL = 'AND H.ERNCD IN (' || !CR 4536
'''' || !CR 4536
'SAL' || !CR 4536
'''' || !CR 4536
')' !CR 4536
!CR 4536
BEGIN-SELECT !CR 4536
I.ERNCD !CR 4536
Let $OT-Earnings-Code-Found = 'Y' !CR 4536
!CR 4536
Let $Where-For-Hrs-Wrkd-OT = $Where-For-Hrs-Wrkd-OT || !CR 4536
'''' || !CR 4536
&I.ERNCD || !CR 4536
'''' || !CR 4536
',' !CR 4536
FROM PS_EARNINGS_TBL I !CR 4536
WHERE I.EFFDT = (SELECT MAX(I1.EFFDT) !CR 4536
FROM PS_EARNINGS_TBL I1 !CR 4536
WHERE I.ERNCD = I1.ERNCD)!CR 4536
AND I.DESCR LIKE '%ime%' !CR 4536
ORDER BY I.ERNCD !CR 4536
WITH UR !CR 4536
END-SELECT !CR 4536
If $OT-Earnings-Code-Found = 'Y' !CR 4536
Let $Where-For-Hrs-Wrkd-OT = !CR 4536
RTrim($Where-For-Hrs-Wrkd-OT, ',') !CR 4536
Let $Where-For-Hrs-Wrkd-OT = $Where-For-Hrs-Wrkd-OT || !CR 4536
')' !CR 4536
Else !CR 4536
Let $Where-For-Hrs-Wrkd-OT = ' ' !CR 4536
End-If !CR 4536
End-Procedure BUILD-EARNINGS-CODE-SQL !CR 4536
!*****************************************************************
Begin-Procedure FINALIZATION
! This procedure displays all of the necessary data once the *
! SQR completes processing. *
!*****************************************************************
Let $Grand-TxGrs-QTD = #Grand-TxGrs-QTD
Let $Grand-Hours-QTD = #Grand-Hours-QTD
Let $Grand-Count = #Grand-Count
Print 'Report Totals' (+1, {Company} )
Print $Grand-TxGrs-QTD ( , {TxGrsQTD} ) Edit 99,999,999,999.99
Print $Grand-Hours-QTD ( , {HrsQTD} ) Edit 99,999,999,999.99
Print $Grand-Count ( , {EECount} ) Edit 9,999,999,999,999
If #Lines-Printed = 0
Print '***** There was no data to report *****' (+3,,) Center
Show ' '
Show ' '
Show '***** There was no data to report *****'
Else
Let $Lines-Printed = Edit (#Lines-Printed, '9999999')
Let $Lines-Printed = LTrim($Lines-Printed, ' ' )
Show ' '
Show 'There were ' $Lines-Printed ' lines printed on the report.'
End-If
Do GET-CURRENT-DATETIME
Show ' '
Show 'The ' $ReportID ' ended at ' $AsOfNow ' on ' $AsOfToday '.'
Do RESET
Do STDAPI-TERM
End-Procedure FINALIZATION
#Include 'curdttim.sqc' !GET-CURRENT-DATETIME procedure
#Include 'datetime.sqc' !Routines for date and time formatting
#Include 'getbalid.sqc' !GET-CALENDAR-YEAR-ID
#Include 'getcodta.sqc' !GET-COMPANY-NAME procedure
#Include 'number.sqc' !Routines to format numbers
#Include 'reset.sqc' !RESET Printer Procedure
#Include 'stdapi.sqc' !Update Process API
#Include 'taxrnctl.sqc' !GET-TAX-REPORTING-RUN-CONTROLS procedure
#Include 'txrnctl1.sqc' !Process Scheduler Run Controls
According to auditors in the State of Washington, the QTD hours sent from headquarters are inaccurate. There are too many hours sent to the state. They are only interested in hours worked. They have determined that earnings sent to the state includes earnings other than regular ones (such as vacation, sick, etc.).
Altho I have been in the field for a while, I have not completely wrapped myself around payroll and earnings codes; therefore, you might need to ask more questions of me. Please feel free to do so.
It is expected that I will come up with an answer for them by Monday morning.
Please, please help! Enclosed is the SQR.
I appreciate you!!!!
!*****************************************************************
! LPY2650: QTD Hours and Gross for Washington Report *
!*****************************************************************
!*****************************************************************
! PROGRAM DESCRIPTION *
!*****************************************************************
! FUNCTION : This program produces a report that lists each *
! employee who has paid taxes in the state of *
! Washington during a given quarter. *
! *
! DB2 ACCESS: PS_LOCATION_TBL SELECT *
! PS_JOB SELECT *
! PS_JOBCODE_TBL SELECT *
! PS_EARNINGS_BAL SELECT *
! *
! FILES USED: NONE *
! *
! REPORTS : QTD Hours and Gross for Washington Report *
!*****************************************************************
!*****************************************************************
! MODIFICATION LOG *
!*****************************************************************
! DATE ANALYST DESCRIPTION OF MODIFICATIONS *
! ---------- ------------------ -------------------------------- *
!*****************************************************************
! END OF MODIFICATION LOG *
!*****************************************************************
#Include 'setenv.sqc' !Set environment
#Include 'setup31.sqc' !Printer and page-size initialization
!Portrait orientation
!*****************************************************************
!Begin Defines *
! This 'section' defines the locations of the columns that *
! are used later in the SQR. *
!*****************************************************************
!Report Column Definitions
#Define Company 001
#Define Location 012
#Define WkrCompCd 024
#Define TxGrsQTD 037
#Define HrsQTD 059
#Define EECount 080
!End Defines
!*****************************************************************
Begin-Report
! Driver procedure. *
!*****************************************************************
Do INITIALIZATION
Do PROCESS-MAIN
Do FINALIZATION
End-Report
!*****************************************************************
Begin-Heading 07
! This section defines what the headings on the report look *
! like. *
!*****************************************************************
#Include 'stdhdg01.sqc'
Print 'Quarter End Date:' (-1,{Company} )
Print $Qtr-End-Date ( ,+1 )
Print ' TAX' (+3,{Location} )
Print ' WORKERS' ( ,{WkrCompCd} )
Print '<-------------------- QUARTER TO DATE --------------------->'
( ,{TxGrsQTD} )
Print 'COMPANY' (+1,{Company} )
Print 'LOCATION' ( ,{Location} )
Print 'COMP CODE' ( ,{WkrCompCd} )
Print ' TAXABLE GROSS' ( ,{TxGrsQTD} )
Print ' HOURS WORKED' ( ,{HrsQTD} )
Print ' EMPLOYEE COUNT' ( ,{EECount} )
Print '<----->' (+1,{Company} )
Print '<------>' ( ,{Location} )
Print '<------->' ( ,{WkrCompCd} )
Print '<--------------->' ( ,{TxGrsQTD} )
Print '<--------------->' ( ,{HrsQTD} )
Print '<--------------->' ( ,{EECount} )
End-Heading
!*****************************************************************
Begin-Procedure PROCESS-MAIN
! This procedure selects all employees who are found on the *
! tables in the select that have work in stores in the state of*
! Washington during the respective quarter. *
! *
! Each record that is found in the select is then setup and *
! printed to the report. *
! *
! If a change in Company and/or Location is encountered the *
! appropriate breaks will occur. *
! *
! AND A.STATE = 'WA' a State row for Washington state *
! AND D.SPCL_BALANCE = 'N' a row that not a Special Accumulator*
! AND D.ERNCD IN ('REG','INV','TRA') a row that is a Regular *
! or Inventory or Training *
! Earnings Code *
!*****************************************************************
BEGIN-SELECT
B.COMPANY () ON-BREAK PRINT=NEVER LEVEL=1 AFTER=COMPANY-CHANGE
A.LOCATION () ON-BREAK PRINT=NEVER LEVEL=2 AFTER=LOCATION-CHANGE
C.WORKERS_COMP_CD
SUM(D.GRS_QTD) &GRS_QTD
SUM(D.HRS_QTD) &HRS_QTD
COUNT(DISTINCT B.EMPLID) &COUNT
Let $Company = &B.COMPANY
Let $Location = &A.LOCATION
Let $Workers-Comp-Code = &C.WORKERS_COMP_CD
Let #Gross-Erns-OT = 0 !CR 4536
Let #Hours-Worked-OT = 0 !CR 4536
Let #Hrs-Worked-QTD = 0 !CR 4536
Let #Std-Hours-SAL = 0 !CR 4536
Let #TxGrs-QTD = 0 !CR 4536
!CR 4536
Let $Earnings-Type = 'SAL' !CR 4536
Let $Where-Clause = $Where-For-Hrs-Wrkd-SAL !CR 4536
Do GET-HOURS !CR 4536
!CR 4536
Let $Earnings-Type = 'OT' !CR 4536
Let $Where-Clause = $Where-For-Hrs-Wrkd-OT !CR 4536
Do GET-HOURS !CR 4536
!CR 4536
Let #TxGrs-QTD = &GRS_QTD - #Gross-Erns-OT !CR 4536
Let $TxGrs-QTD = #TxGrs-QTD !CR 4536
!CR 4536
Let #Hrs-Worked-QTD = ( &HRS_QTD !CR 4536
- #Hours-Worked-OT !CR 4536
- #Std-Hours-SAL ) !CR 4536
Let $Hrs-Worked-QTD = #Hrs-Worked-QTD !CR 4724
Let $Count = &COUNT
Let #Location-Hours-QTD = #Location-Hours-QTD + #Hrs-Worked-QTD
Let #Location-TxGrs-QTD = #Location-TxGrs-QTD + #TxGrs-QTD
Let #Location-Count = #Location-Count + &Count
Let #Company-Hours-QTD = #Company-Hours-QTD + #Hrs-Worked-QTD
Let #Company-TxGrs-QTD = #Company-TxGrs-QTD + #TxGrs-QTD
Let #Company-Count = #Company-Count + &Count
Let #Grand-Hours-QTD = #Grand-Hours-QTD + #Hrs-Worked-QTD
Let #Grand-TxGrs-QTD = #Grand-TxGrs-QTD + #TxGrs-QTD
Let #Grand-Count = #Grand-Count + &Count
Do PRINT-REPORT
FROM PS_LOCATION_TBL A,
PS_JOB B,
PS_JOBCODE_TBL C,
PS_EARNINGS_BAL D
WHERE B.SETID_LOCATION = A.SETID
AND B.LOCATION = A.LOCATION
AND B.SETID_JOBCODE = C.SETID
AND B.JOBCODE = C.JOBCODE
AND B.EMPLID = D.EMPLID
AND B.COMPANY = D.COMPANY
AND A.EFFDT = (SELECT MAX(A1.EFFDT)
FROM PS_LOCATION_TBL A1
WHERE A1.SETID = A.SETID
AND A1.LOCATION = A.LOCATION
AND A1.EFFDT <= $Thru-Date)
AND B.EFFDT = (SELECT MAX(B1.EFFDT)
FROM PS_JOB B1
WHERE B1.EMPLID = B.EMPLID
AND B1.EMPL_RCD = B.EMPL_RCD
AND B1.EFFDT <= $Thru-Date)
AND B.EFFSEQ = (SELECT MAX(B2.EFFSEQ)
FROM PS_JOB B2
WHERE B2.EMPLID = B.EMPLID
AND B2.EMPL_RCD = B.EMPL_RCD
AND B2.EFFDT = B.EFFDT)
AND C.EFFDT = (SELECT MAX(C1.EFFDT)
FROM PS_JOBCODE_TBL C1
WHERE C1.SETID = C.SETID
AND C1.JOBCODE = C.JOBCODE
AND C1.EFFDT <= B.EFFDT)
AND D.BALANCE_PERIOD = (SELECT MAX(D1.BALANCE_PERIOD)
FROM PS_EARNINGS_BAL D1
WHERE D1.EMPLID = D.EMPLID
AND D1.COMPANY = D.COMPANY
AND D1.BALANCE_ID = D.BALANCE_ID
AND D1.BALANCE_YEAR = D.BALANCE_YEAR
AND D1.BALANCE_QTR = D.BALANCE_QTR
AND D1.EMPL_RCD = D.EMPL_RCD
AND D1.SPCL_BALANCE = D.SPCL_BALANCE
AND D1.ERNCD = D.ERNCD)
AND A.STATE = 'WA'
AND A.LOCATION NOT IN ('0889','0898') !CR 4536
AND D.BALANCE_ID = $Calendar_Year_ID
AND D.BALANCE_YEAR = &TX.BALANCE_YEAR
AND D.BALANCE_QTR = &TX.BALANCE_QTR
AND D.SPCL_BALANCE = 'N'
AND D.ERNCD IN ('REG','SAL','INV','TRA','RGS') !CR 4536
GROUP BY B.COMPANY, A.LOCATION, C.WORKERS_COMP_CD
ORDER BY B.COMPANY, A.LOCATION, C.WORKERS_COMP_CD
WITH UR
END-SELECT
End-Procedure PROCESS-MAIN
!*****************************************************************
Begin-Procedure GET-HOURS
!*****************************************************************
BEGIN-SELECT !CR 4536
SUM(H.GRS_QTD) &GRS_EARNINGS_QTD !CR 4536
SUM(H.HRS_QTD) &HOURS_QTD !CR 4536
Evaluate $Earnings-Type !CR 4536
When = 'SAL' !CR 4536
Let #Std-Hours-SAL = #Std-Hours-SAL + !CR 4536
&HOURS_QTD !CR 4536
Break !CR 4536
When = 'OT' !CR 4536
Let #Hours-Worked-OT = #Hours-Worked-OT + !CR 4536
&HOURS_QTD !CR 4536
Let #Gross-Erns-OT = #Gross-Erns-OT + !CR 4536
&GRS_EARNINGS_QTD !CR 4536
Break !CR 4536
When-Other !CR 4536
Break !CR 4536
End-Evaluate !CR 4536
FROM PS_LOCATION_TBL E, !CR 4536
PS_JOB F, !CR 4536
PS_JOBCODE_TBL G, !CR 4536
PS_EARNINGS_BAL H !CR 4536
WHERE F.SETID_JOBCODE = E.SETID !CR 4536
AND F.LOCATION = E.LOCATION !CR 4536
AND F.SETID_JOBCODE = G.SETID !CR 4536
AND F.JOBCODE = G.JOBCODE !CR 4536
AND F.EMPLID = H.EMPLID !CR 4536
AND F.COMPANY = H.COMPANY !CR 4536
AND E.EFFDT = !CR 4536
(SELECT MAX(E1.EFFDT) !CR 4536
FROM PS_LOCATION_TBL E1 !CR 4536
WHERE E1.SETID = E.SETID !CR 4536
AND E1.LOCATION = E.LOCATION !CR 4536
AND E1.EFFDT <= $Thru-Date) !CR 4536
AND F.EFFDT = !CR 4536
(SELECT MAX(F1.EFFDT) !CR 4536
FROM PS_JOB F1 !CR 4536
WHERE F1.EMPLID = F.EMPLID !CR 4536
AND F1.EMPL_RCD = F.EMPL_RCD !CR 4536
AND F1.EFFDT <= $Thru-Date) !CR 4536
AND F.EFFSEQ = !CR 4536
(SELECT MAX(F2.EFFSEQ) !CR 4536
FROM PS_JOB F2 !CR 4536
WHERE F2.EMPLID = F.EMPLID !CR 4536
AND F2.EMPL_RCD = F.EMPL_RCD !CR 4536
AND F2.EFFDT = F.EFFDT) !CR 4536
AND G.EFFDT = !CR 4536
(SELECT MAX(G1.EFFDT) !CR 4536
FROM PS_JOBCODE_TBL G1 !CR 4536
WHERE G1.SETID = G.SETID !CR 4536
AND G1.JOBCODE = G.JOBCODE !CR 4536
AND G1.EFFDT <= F.EFFDT) !CR 4536
AND H.BALANCE_PERIOD = !CR 4536
(SELECT MAX(H1.BALANCE_PERIOD) !CR 4536
FROM PS_EARNINGS_BAL H1 !CR 4536
WHERE H1.EMPLID = H.EMPLID !CR 4536
AND H1.COMPANY = H.COMPANY !CR 4536
AND H1.BALANCE_ID = H.BALANCE_ID !CR 4536
AND H1.BALANCE_YEAR = H.BALANCE_YEAR !CR 4536
AND H1.BALANCE_QTR = H.BALANCE_QTR !CR 4536
AND H1.EMPL_RCD = H.EMPL_RCD !CR 4536
AND H1.SPCL_BALANCE = H.SPCL_BALANCE !CR 4536
AND H1.ERNCD = H.ERNCD) !CR 4536
AND F.COMPANY = $Company !CR 4536
AND E.LOCATION = $Location !CR 4536
AND G.WORKERS_COMP_CD = $Workers-Comp-Code !CR 4536
AND E.STATE = 'WA' !CR 4536
AND H.BALANCE_ID = $Calendar_Year_ID !CR 4536
AND H.BALANCE_YEAR = &TX.BALANCE_YEAR !CR 4536
AND H.BALANCE_QTR = &TX.BALANCE_QTR !CR 4536
AND H.SPCL_BALANCE = 'N' !CR 4536
\$Where-Clause\ !CR 4536
GROUP BY F.COMPANY, E.LOCATION, G.WORKERS_COMP_CD !CR 4536
WITH UR !CR 4536
END-SELECT !CR 4536
End-Procedure GET-HOURS !CR 4536
!*****************************************************************
Begin-Procedure PRINT-REPORT
! This procedure prints the detail lines. *
!*****************************************************************
If $Location-Change = 'Y' And #Current-Line > 9
Print $Blank-Line (+2, )
Let $Location-Change = 'N'
Else
End-If
Print $Company (+1, {Company} )
Print $Location ( , {Location} )
Print $Workers-Comp-Code ( , {WkrCompCd})
Print $TxGrs-QTD ( , {TxGrsQTD} ) Edit 99,999,999,999.99
Print $Hrs-Worked-QTD ( , {HrsQTD} ) Edit 99,999,999,999.99
Print $Count ( , {EECount} ) Edit 9,999,999,999,999
Add 1 To #Lines-Printed
End-Procedure PRINT-REPORT
!*****************************************************************
Begin-Procedure LOCATION-CHANGE
! This procedure executes when a change in Location has been *
! detected by the PROCESS-MAIN procedure. *
!*****************************************************************
Let $Location-TxGrs-QTD = #Location-TxGrs-QTD
Let $Location-Hours-QTD = #Location-Hours-QTD
Let $Location-Count = #Location-Count
Let $Location = LTrim ($Location, ' ')
Let $Location = RTrim ($Location, ' ')
Print '<===============>' (+1, {TxGrsQTD} )
Print '<===============>' ( , {HrsQTD} )
Print '<===============>' ( , {EECount} )
Print $Location (+1, {Company} )
Print 'Location Totals' ( , +1 )
Print $Location-TxGrs-QTD ( , {TxGrsQTD} ) Edit 99,999,999,999.99
Print $Location-Hours-QTD ( , {HrsQTD} ) Edit 99,999,999,999.99
Print $Location-Count ( , {EECount} ) Edit 9,999,999,999,999
Let #Location-TxGrs-QTD = 0
Let #Location-Hours-QTD = 0
Let #Location-Count = 0
Let $Location-Change = 'Y'
End-Procedure LOCATION-CHANGE
!*****************************************************************
Begin-Procedure COMPANY-CHANGE
! This procedure executes when a change in Company has been *
! detected by the PROCESS-MAIN procedure. *
!*****************************************************************
Let $Company-TxGrs-QTD = #Company-TxGrs-QTD
Let $Company-Hours-QTD = #Company-Hours-QTD
Let $Company-Count = #Company-Count
Let $Company = LTrim ($Company, ' ')
Let $Company = RTrim ($Company, ' ')
Print $Company (+1, {Company} )
Print 'Company Totals' ( , +1 )
Print $Company-TxGrs-QTD ( , {TxGrsQTD} ) Edit 99,999,999,999.99
Print $Company-Hours-QTD ( , {HrsQTD} ) Edit 99,999,999,999.99
Print $Company-Count ( , {EECount} ) Edit 9,999,999,999,999
Let #Company-TxGrs-QTD = 0
Let #Company-Hours-QTD = 0
Let #Company-Count = 0
Let $Company-Change = 'N'
If #End-File
Else
New-Page
End-If
End-Procedure COMPANY-CHANGE
!*****************************************************************
Begin-Procedure INITIALIZATION
! This procedure initializes all of the variables needed to *
! process the data. *
!*****************************************************************
Do STDAPI-INIT
Do INIT-DATETIME
Do INIT-NUMBER
Do GET-CURRENT-DATETIME
Do GET-CALENDAR-YEAR-ID
Do GET-TAX-REPORTING-RUN-CONTROLS
Do BUILD-EARNINGS-CODE-SQL !CR 4536
Move &TX.BALANCE_YEAR To #Year
Move &TX.BALANCE_QTR To #Quarter
Let $ReportID = 'PSPR265A'
Let $ReportTitle = 'QTD HOURS AND GROSS FOR WASHINGTON'
Do FORMAT-NUMBER (#Year, $Out, '9999' )
Let $Year = LTrim($Out,' ')
Evaluate #Quarter
When = 1
Move '03-31-' To $Qtr-End-Date
Move '-03-31' To $Thru-Date
Break
When = 2
Move '06-30-' To $Qtr-End-Date
Move '-06-30' To $Thru-Date
Break
When = 3
Move '09-30-' To $Qtr-End-Date
Move '-09-30' To $Thru-Date
Break
When = 4
Move '12-31-' To $Qtr-End-Date
Move '-12-31' To $Thru-Date
Break
End-Evaluate
Let $Qtr-End-Date = $Qtr-End-Date || $Year
Let $Thru-Date = $Year || $Thru-Date
Let $Blank-Line = ' '
Show $Blank-Line
Show $ReportID ' - ' $ReportTitle
Show 'The ' $ReportID ' began at ' $AsOfNow ' on ' $AsOfToday '.'
Show 'The data on the report will be for the ' $Calendar_Year_ID
Show ' Quarter ending ' $Qtr-End-Date '.'
Show 'Processing balances for ' $RptQtrYr '.'
Show $Blank-Line
End-Procedure INITIALIZATION
!*****************************************************************
Begin-Procedure BUILD-EARNINGS-CODE-SQL
!*****************************************************************
Let $Where-For-Hrs-Wrkd-OT = 'AND H.ERNCD IN (' !CR 4536
Let $Where-For-Hrs-Wrkd-SAL = 'AND H.ERNCD IN (' || !CR 4536
'''' || !CR 4536
'SAL' || !CR 4536
'''' || !CR 4536
')' !CR 4536
!CR 4536
BEGIN-SELECT !CR 4536
I.ERNCD !CR 4536
Let $OT-Earnings-Code-Found = 'Y' !CR 4536
!CR 4536
Let $Where-For-Hrs-Wrkd-OT = $Where-For-Hrs-Wrkd-OT || !CR 4536
'''' || !CR 4536
&I.ERNCD || !CR 4536
'''' || !CR 4536
',' !CR 4536
FROM PS_EARNINGS_TBL I !CR 4536
WHERE I.EFFDT = (SELECT MAX(I1.EFFDT) !CR 4536
FROM PS_EARNINGS_TBL I1 !CR 4536
WHERE I.ERNCD = I1.ERNCD)!CR 4536
AND I.DESCR LIKE '%ime%' !CR 4536
ORDER BY I.ERNCD !CR 4536
WITH UR !CR 4536
END-SELECT !CR 4536
If $OT-Earnings-Code-Found = 'Y' !CR 4536
Let $Where-For-Hrs-Wrkd-OT = !CR 4536
RTrim($Where-For-Hrs-Wrkd-OT, ',') !CR 4536
Let $Where-For-Hrs-Wrkd-OT = $Where-For-Hrs-Wrkd-OT || !CR 4536
')' !CR 4536
Else !CR 4536
Let $Where-For-Hrs-Wrkd-OT = ' ' !CR 4536
End-If !CR 4536
End-Procedure BUILD-EARNINGS-CODE-SQL !CR 4536
!*****************************************************************
Begin-Procedure FINALIZATION
! This procedure displays all of the necessary data once the *
! SQR completes processing. *
!*****************************************************************
Let $Grand-TxGrs-QTD = #Grand-TxGrs-QTD
Let $Grand-Hours-QTD = #Grand-Hours-QTD
Let $Grand-Count = #Grand-Count
Print 'Report Totals' (+1, {Company} )
Print $Grand-TxGrs-QTD ( , {TxGrsQTD} ) Edit 99,999,999,999.99
Print $Grand-Hours-QTD ( , {HrsQTD} ) Edit 99,999,999,999.99
Print $Grand-Count ( , {EECount} ) Edit 9,999,999,999,999
If #Lines-Printed = 0
Print '***** There was no data to report *****' (+3,,) Center
Show ' '
Show ' '
Show '***** There was no data to report *****'
Else
Let $Lines-Printed = Edit (#Lines-Printed, '9999999')
Let $Lines-Printed = LTrim($Lines-Printed, ' ' )
Show ' '
Show 'There were ' $Lines-Printed ' lines printed on the report.'
End-If
Do GET-CURRENT-DATETIME
Show ' '
Show 'The ' $ReportID ' ended at ' $AsOfNow ' on ' $AsOfToday '.'
Do RESET
Do STDAPI-TERM
End-Procedure FINALIZATION
#Include 'curdttim.sqc' !GET-CURRENT-DATETIME procedure
#Include 'datetime.sqc' !Routines for date and time formatting
#Include 'getbalid.sqc' !GET-CALENDAR-YEAR-ID
#Include 'getcodta.sqc' !GET-COMPANY-NAME procedure
#Include 'number.sqc' !Routines to format numbers
#Include 'reset.sqc' !RESET Printer Procedure
#Include 'stdapi.sqc' !Update Process API
#Include 'taxrnctl.sqc' !GET-TAX-REPORTING-RUN-CONTROLS procedure
#Include 'txrnctl1.sqc' !Process Scheduler Run Controls