{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Overview","Timecard Only","Timecard & Pay Calcs","Payroll Entry Form","Payroll Startup","Grid Sample","misc"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":24,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Overview","index":0,"n_rows":83,"n_cols":7,"rows_kept":83,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,"Version: 2025-03-28"]
[]
[null,"Payroll - 5-2-2 Payroll Records"]
[null,"The following template should be used to import Payroll Records into Sage 100 Contractor.  "]
[]
[null,"File Layout Specification"]
[]
[null,"Header Layout"]
[]
[null,"Column","Reference","Data Type","Tab","Example","Description"]
[null,"COL1","Required","String (3)",null,"PRR","(File Type Code)"]
[null,"COL2","Required","String (1)",null,"H","(Header Record)"]
[null,"COL3","Required (1) **","Numeric","Header",null,"Employee No"]
[null,"COL4","Required","Date","Header","yyyy-mm-dd","Period Start"]
[null,"COL5","Required (1)","Date","Header","yyyy-mm-dd","Period End"]
[null,"COL6","Required (1)","String (20)","Header","If no check enter '0000'","Check No"]
[null,"COL7","Required (1)","Date","Header","yyyy-mm-dd","Check Date"]
[null,"COL8","Required **","Numeric","Header","1=Regular; 2=Bonus; 3=Hand Computed; 4=Startup; 5=Advance; 6=Third Party","Payroll Type"]
[null,"COL9","Required **","Numeric","Header","1,2,3 or 4","Quarter"]
[null,"COL10","Required","String (2)","Header","Blank = Emp Setup","State"]
[null,"COL11","(2)","Numeric","Header","1=True;0=False;Blank=Emp Setup","Direct Deposit"]
[null,"COL12",null,"Numeric (10,2)","Header","Blank = Emp Setup","Salary"]
[null,"COL13",null,"Numeric (6,4)","Header","Blank = Emp Setup","Reg Hrly Rate"]
[null,"COL14",null,"Numeric (6,4)","Header","Blank = Emp Setup","OT Hrly Rate"]
[null,"COL15",null,"Numeric (6,4)","Header","Blank = Emp Setup","Premium Hrly Rate"]
[null,"COL16",null,"Text","Header",null,"Notes"]
[]
[null,"** Validated Field - value must exist in the respective lookup table in Sage 100 Contractor"]
[null,"COL: a placeholder must be provided for each column even if no value is provided"]
[null,"(1) Employee Number, Pay Period, Check Number and Check Date must be unique within a single file"]
[null,"(2) If marked as True then Direct Deposits must be activated for the respective employee, otherwise the entry will be rejected, If left \"Blank\" the direct deposit flag will be derived from the Employee Setup Record"]
[]
[]
[null,"Detail Layout (Timecard)"]
[null,"COL1","Required","String (3)",null,"PRR","(File Type Code)"]
[null,"COL2","Required","String (1)",null,"D","(Detail Record)"]
[null,"COL3","Required","Numeric","Header",null,"Employee No"]
[null,"COL4","Required","Date","Timecard",null,"Work Date"]
[null,"COL5",null,"String (50)","Timecard",null,"Line Description"]
[null,"COL6","** (2)","String (20)","Timecard",null,"Service WO No"]
[null,"COL7","(1) (2) **","Numeric","Timecard","Blank = derived from Service Order No","Job No"]
[null,"COL8","(1) **","Numeric","Timecard",null,"Equip Repaired No"]
[null,"COL9","**","Numeric","Timecard","Blank = derived from Employee","Local Payroll Tax"]
[null,"COL10",null,"String (1)","Timecard","Y=yes; N=no; Blank=derived from Job","Certified Payroll"]
[null,"COL11","**","Numeric","Timecard",null,"Phase No"]
[null,"COL12","(2) **","Numeric (12,3)","Timecard","Blank = Derived from Service Order No type's corresponding Cost Code","Cost Code"]
[null,"COL13","Required (3) (4) **","Numeric","Timecard",null,"Pay Type"]
[null,"COL14","**","Numeric","Timecard","Blank = derived from Employee","Pay Group"]
[null,"COL15",null,"Numeric (6,4)","Timecard","Blank = derived from Pay Group first if exists, then Employee second, based on pay type","Pay Rate"]
[null,"COL16","(3)","Numeric (5,2)","Timecard","Required if System Pay Type is 1 through 6","Hours Worked"]
[null,"COL17","(4)","Numeric (6,4)","Timecard","Required if System Pay Type = 7","Pieces Rate"]
[null,"COL18","(4)","Numeric (8,2)","Timecard","Required if System Pay Type = 7","Pieces Units"]
[null,"COL19","Required **","Numeric",null,"Blank = derived from Cost Code first if Employee 'Always use employee comp code' is unchecked, otherwise derived from Employee","Workers Comp Code"]
[null,"COL20","**","Numeric",null,"Blank = derived from Job first, Employee Position second","Department"]
[null,"COL21","**","Numeric","Timecard",null,"Absence"]
[null,"COL22",null,"String (50)","Timecard",null,"Userdefined"]
[null,"COL23",null,"Text","Timecard",null,"Notes"]
[]
[null,"** Validated Field - value must exist in the respective lookup table in Sage 100 Contractor"]
[null,"COL: a placeholder must be provided for each column even if no value is provided"]
[null,"(1) Either a Job No or Equip Repaired No may be entered but not both"]
[null,"(2) Cost Code is Required if a Job Number or Service Order is specified. If Service Order is specified and the cost code is blank the cost code will be derived from the Service Order"]
[null,"(3) Hours Worked is required if System Pay Type is 1 through 6"]
[null,"(4) Pieces Rate and Pieces Units are required if System Pay Type = 7"]
[]
[]
[null,"Calculations Layout"]
[null,"COL1","Required","String (3)",null,"PRR","(File Type Code)"]
[null,"COL2","Required","String (1)",null,"C","(Calc Record)"]
[null,"COL3","Required","Numeric","Header",null,"Employee No"]
[null,"COL4","Required (1) **","Numeric","Calculations",null,"Payroll Calc No"]
[null,"COL5","Required","Numeric (10,2)","Calculations",null,"Amount"]
[]
[null,"** Validated Field - value must exist in the respective lookup table in Sage 100 Contractor"]
[null,"COL: a placeholder must be provided for each column even if no value is provided"]
[null,"Note: This section is for importing calculation \"override\" amounts"]
[null,"(1) Payroll Calc No must be unique across Employee No (empnum)"]
[]
[]
[null,"File Example:"]
[null,"PRR,H,\"10\",9/15/2019,\"9/27/2019\",\"0000\",\"9/27/2019\",1,\"4\",CA,,,,,\"\""]
[null,"PRR,D,\"10\",\"9/16/2019\",\"Day1\",,\"2017008\",\"\",\"\",,,2120.000,\"1\",,25,5,,,8810,,,,,,\"\",\"\""]
[null,"PRR,D,\"10\",\"9/17/2019\",\"Day2\",,\"2017008\",\"\",\"\",,,2120.000,\"1\",,32,3,,,8810,,,,,,\"\",\"\""]

{"sheet":"Timecard Only","index":1,"n_rows":16,"n_cols":35,"rows_kept":16,"cut":false,"hidden":false,"merged":[[6,1,6,14],[6,15,6,34]]}
["Timecard Only Flat Import Template"]
[]
[]
[]
[null,"Note: even though the Header information can be repeated for an employee on each line, only the first employee line will be used"]
[]
[null,"Header Information",null,null,null,null,null,null,null,null,null,null,null,null,null,"Time Card Line Item Details"]
[null,"empnum","strprd","payprd","chknum","chkdte","paytyp","qtrnum","taxste","dirdep","salary","payrt1","payrt2","payrt3","ntetxt","dtewrk","dscrpt","wrkord","jobnum","eqpnum","loctax","crtfid","phsnum","cstcde","paytyp","paygrp","payrte","hrswrk","pcerte","pieces","cmpcde","dptmnt","absnce","usrdf1","ntetxt"]
[null,"Employee No","Pay Period Start","Pay Period End","Check No","Check Dt","Pay Type","Quarter","Tax State","Direct Deposit","Salary","Pay Rate 1","Pay Rate 2","Pay Rate 2","Notes","Work Date","Work Line Description","Service WO No","Job No","Equip No","Local Tax ID","Certified Payroll","Phase No","Cost Code","Pay Type Code","Pay Group Code","Pay Rate","Hours Worked","Piece Rate","No of Pieces","Workers Comp Code","Department Code","Absence Code","User Def 1","Line item notes"]
[null,"Numeric","Date","Date","String (20)","Date","Numeric","Numeric","String (2)","Numeric","Numeric (10,2)","Numeric (6,4)","Numeric (6,4)","Numeric (6,4)","Text","Date","String (50)","String (20)","Numeric","Numeric","Numeric","String (1)","Numeric","Numeric (12,3)","Numeric","Numeric","Numeric (6,4)","Numeric (5,2)","Numeric (6,4)","Numeric (8,2)","Numeric","Numeric","Numeric","String (50)","Text"]
[]
[null,10,"2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-16","Day1",null,2017008,null,null,null,null,"2120.000",1,null,25,8,null,null,8810,null,null,null,"zz"]
[null,10,"2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-17","Day2",null,2017008,null,null,null,null,"2120.000",1,null,32,8,null,null,8810,null,null,null,"zz"]
[null,"11","2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-16","Day1",null,2017008,null,null,null,null,"2120.000",1,null,25,8,null,null,8810,null,null,null,"zz"]
[null,"11","2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-17","Day2",null,2017008,null,null,null,null,"2120.000",1,null,32,8,null,null,8810,null,null,null,"zz"]
[null,"12","2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-17","Day2",null,2017008,null,null,null,null,"2120.000",1,null,32,8,null,null,8810,null,null,null,"zz"]

{"sheet":"Timecard & Pay Calcs","index":2,"n_rows":18,"n_cols":37,"rows_kept":18,"cut":false,"hidden":false,"merged":[[8,1,8,14],[8,15,8,34],[8,35,8,36]]}
["Time + Pay Calcs Flat Import Template"]
[]
[]
[]
[null,"Note: even though the Header information can be repeated for an employee on each line, only the first employee line will be used"]
[]
[null,"Payroll Calculations:  Only one payroll calc line can exist per employee, also no other data can exist on a payroll calc row other than the payroll calc number and amount"]
[]
[null,"Header Information",null,null,null,null,null,null,null,null,null,null,null,null,null,"Time Card Line Item Details",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Payroll Calc"]
[null,"empnum","strprd","payprd","chknum","chkdte","paytyp","qtrnum","taxste","dirdep","salary","payrt1","payrt2","payrt3","ntetxt","dtewrk","dscrpt","wrkord","jobnum","eqpnum","loctax","crtfid","phsnum","cstcde","paytyp","paygrp","payrte","hrswrk","pcerte","pieces","cmpcde","dptmnt","absnce","usrdf1","ntetxt","clcnum","amount"]
[null,"Employee No","Pay Period Start","Pay Period End","Check No","Check Dt","Pay Type","Quarter","Tax State","Direct Deposit","Salary","Pay Rate 1","Pay Rate 2","Pay Rate 2","Notes","Work Date","Work Line Description","Service WO No","Job No","Equip No","Local Tax ID","Certified Payroll","Phase No","Cost Code","Pay Type Code","Pay Group Code","Pay Rate","Hours Worked","Piece Rate","No of Pieces","Workers Comp Code","Department Code","Absence Code","User Def 1","Line item notes","Calc No","Amount"]
[null,"Numeric","Date","Date","String (20)","Date","Numeric","Numeric","String (2)","Numeric","Numeric (10,2)","Numeric (6,4)","Numeric (6,4)","Numeric (6,4)","Text","Date","String (50)","String (20)","Numeric","Numeric","Numeric","String (1)","Numeric","Numeric (12,3)","Numeric","Numeric","Numeric (6,4)","Numeric (5,2)","Numeric (6,4)","Numeric (8,2)","Numeric","Numeric","Numeric","String (50)","Text","Numeric","Numeric (10,2)"]
[null,10,"2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-16","Day1",null,2017008,null,null,null,null,"2120.000",1,null,25,8,null,null,8810,null,null,null,"zz"]
[null,10,"2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-17","Day2",null,2017008,null,null,null,null,"2120.000",1,null,32,8,null,null,8810,null,null,null,"zz"]
[null,"11","2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-16","Day1",null,2017008,null,null,null,null,"2120.000",1,null,25,8,null,null,8810,null,null,null,"zz"]
[null,"11","2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-17","Day2",null,2017008,null,null,null,null,"2120.000",1,null,32,8,null,null,8810,null,null,null,"zz"]
[null,"12","2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,"2019-09-17","Day2",null,2017008,null,null,null,null,"2120.000",1,null,32,8,null,null,8810,null,null,null,"zz"]
[null,"12","2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,11,56.97]

{"sheet":"Payroll Entry Form","index":3,"n_rows":17,"n_cols":22,"rows_kept":17,"cut":false,"hidden":false,"merged":[[10,2,10,3]]}
["Sample Payroll Records Form"]
[]
[]
[null,"Employee No: *",10,"Payroll Type: *",1,null,"Regular Hrly Rate:  "]
[null,"Period Start: *","2019-09-15","Quarter: *",4,null,"Overtime Hrly Rate:  "]
[null,"Period End: *","2019-09-27","State: *","CA",null,"Permium Hrly Rate:  "]
[null,"Check Date: *","2019-09-27","Salary:  "]
[null,"Check No: *","0000"]
[null,null,null,false]
[]
[null,"Notes:",null,null,null,null,false]
[]
[]
[]
[null,null,"Work Date","Line Description","Service\nOrder No","Job No","Equip \nRepaired No (1)","Local\nPayroll Tax","Certified\nPayroll","Phase No","Cost Code","Pay \nType (2)","Pay Group","Pay Rate","Hours\nWorked","Pieces\nRate","Pieces\nUnits","Workers\nComp  Code","Department","Absence","Userdefined","Notes"]
[null,null,"2019-09-16","Day1",null,2017008,null,null,null,null,"2120.000","1",null,null,5]
[null,null,"2019-09-17","Day2",null,2017008,null,null,null,null,"2120.000","1",null,null,3]

{"sheet":"Payroll Startup","index":4,"n_rows":12,"n_cols":34,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Sample Payroll Startup Form"]
[]
[null,"Period Start: *","2017-05-15"]
[null,"Period End: *","2017-05-27"]
[null,"Check Date: *","2017-05-27","<--Qtr is derived from the check date"]
[null,"Check No: *","abd"]
[]
[null,null,null,null,null,{"$f":"COUNT(H9:BU9)","v":27}]
[null,null,null,null,null,"Calc No",null,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,30,31,32,40,41,42,43]
[null,null,"Employee\nrecnum","Employee Name","State","Calc Name","Salary","Employee Social Security","Employee Medicare","Federal Income Tax","CA State Income Tax","CA State Disability","Payroll Advance","CA Workers Comp Ins","Employer Social Security","Employer Medicare","CA State Unemployment","Federal Unemployment Tax","Garnishment-Child Support","Garnishment-Tax Levy","Employee 401K","Employer 401k Contributn","Employer Pd. Health","Employee Pd. Health","Small Tool Use","Liability Insurance","PW Benefit-Cash","NV State Income Tax","NV State Unemployment","NV Workers Comp","Union 20-H & W Benefit","Union 20-Pension","Union 20-Vacation","Union 20-Dues"]
[null,null,10,"John Doe","CA",null,10000,200,null,1500,null,null,null,null,null,null,null,850]
[null,null,11,"Jane Smith","UT",null,32000,225,null,3200,null,null,null,null,null,null,null,1200]

{"sheet":"Grid Sample","index":5,"n_rows":16,"n_cols":24,"rows_kept":16,"cut":false,"hidden":true,"merged":[]}
["Sample Grid Template"]
[]
[]
[null,"COL1","COL2","COL3","COL4","COL5","COL6","COL7","COL8","COL9","COL10","COL11","COL12","COL13","COL14","COL15","COL16","COL17","COL18","COL19","COL20","COL21","COL22","COL23"]
[null,"PRR","H","empnum","strprd","payprd","chknum","chkdte","paytyp","qtrnum","taxste","dirdep","salary","payrt1","payrt2","payrt3","ntetxt"]
[null,null,null,"Numeric","Date","Date","String (20)","Date","Numeric","Numeric","String (2)","Numeric","Numeric (10,2)","Numeric (6,4)","Numeric (6,4)","Numeric (6,4)","Text"]
[null,"PRR","D","empnum","dtewrk","dscrpt","wrkord","jobnum","eqpnum","loctax","crtfid","phsnum","cstcde","paytyp","paygrp","payrte","hrswrk","pcerte","pieces","cmpcde","dptmnt","absnce","usrdf1","ntetxt"]
[null,null,null,"Numeric","Date","String (50)","String (20)","Numeric","Numeric","Numeric","String (1)","Numeric","Numeric (12,3)","Numeric","Numeric","Numeric (6,4)","Numeric (5,2)","Numeric (6,4)","Numeric (8,2)","Numeric","Numeric","Numeric","String (50)","Text"]
[null,"PRR","C","empnum","clcnum","amount"]
[null,null,null,"Numeric","Numeric","Numeric (10,2)"]
[]
[]
[null,"PRR","H",10,"2019-09-28","2019-10-05","0000","2019-09-27",1,4,"CA",0,null,null,null,11,"zz"]
[null,"PRR","D",10,"2019-09-16","Day1",null,2017008,null,null,null,null,"2120.000",1,null,25,8,null,null,8810,null,null,null,"zz"]
[null,"PRR","D",10,"2019-09-17","Day2",null,2017008,null,null,null,null,"2120.000",1,null,32,8,null,null,8810,null,null,null,"zz"]
[null,"PRR","C","10","12",150]

{"sheet":"misc","index":6,"n_rows":6,"n_cols":1,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
["1 - Regular"]
["2 - Bonus"]
["3 - Hand Computed"]
["4 - Startup"]
["5 - Advance"]
["6 - Third Party"]