{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Overview","AP Invoice Form","Grid Sample","misc"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":25,"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":105,"n_cols":8,"rows_kept":105,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,"version: 2023-12-05"]
[]
[null,"Accounts Payable - 4-2 Payable Invoices/Credits"]
[]
[null,"The following template should be used to import AP invoices into Sage 100 Contractor.  "]
[]
[null,"File Layout Specification"]
[]
[null,"Header"]
[null,"Header","Field","Reference","Data Type","Tab","Example","Description"]
[null,"COL1","filetype","Required","String (5)","Import","APINV","(File Type Code)"]
[null,"COL2","linetype","Required","String (1)","Import","H","(Line Type Code)"]
[null,"COL3","invnum","Required","String (20)","Header",null,"Invoice No"]
[null,"COL4","vndnum","Required **","Numeric","Header",null,"Vendor No"]
[null,"COL5","dscrpt","Required","String (50)","Header",null,"Invoice Desc"]
[null,"COL6","invdte","Required","Date","Header","yyyy-mm-dd","Invoice Date"]
[null,"COL7","duedte","Required (1)","Date","Header","yyyy-mm-dd","Due Date"]
[null,"COL8","dscdte","Required (1)","Date","Header","yyyy-mm-dd","Discount Date"]
[null,"COL9","status","Required ** (2)","Numeric","Header","1-Open, 2-Review, 3-Dispute, 4-Paid","Status"]
[null,"COL10","invtyp","Required **","Numeric","Header","1-Contract,2-Memo","Invoice Type"]
[null,"COL11","pchord","** (3)","String (20)","Header",null,"Purchase Order No"]
[null,"COL12","ctcnum","** (3)","String (20)","Header",null,"Subcontract No"]
[null,"COL13","jobnum","** (4)","Numeric","Header",null,"Job No"]
[null,"COL14","phsnum","** (4)","Numeric","Header",null,"Phase No"]
[null,"COL15","shpnum",null,"String (20)","Header",null,"Ship No"]
[null,"COL16","refnum",null,"String (20)","Header",null,"Reference No"]
[null,"COL17","usrdf1",null,"String (50)","Header",null,"User Def 1"]
[null,"COL18","usrdf2",null,"String (50)","Header",null,"User Def 2"]
[null,"COL19","dscavl","(5)","Numeric (10,2)","Header",null,"Discount Amt"]
[null,"COL20","retain",null,"Numeric (10,2)","Header",null,"Retainage Amt"]
[null,"COL21","setpay",null,"Numeric (10,2)","Header",null,"Set to Pay Amt"]
[null,"COL22","hotlst",null,"Numeric","Header","1=Yes;0=No","Hot List"]
[null,"COL23","ntetxt",null,"Text","Header",null,"Notes"]
[null,"COL24","poclose","(6)","Numeric",null,"Default=0; 1=Yes; 0=No","Auto close Purchase Order"]
[null,"COL25","postprd","(7)","Date",null,null,"Posting Period"]
[]
[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) If the Due Date or Discount Date are left blank, then they will be derived from the terms in the Vendor Setup relative to the invoice date. However, if there are no terms in the Vendor Setup then they will be set to the Invoice Date."]
[null,"(2) If a Status of 4-Paid is entered no ledger transactions will be created for the invoice"]
[null,"(3) Auto Detail: If no \"D\" records are passed into the file for the respective subcontract or purchase order then the detail line items will be automatically populated based on the existing line items for the PO or Subcontract"]
[null,"(4) If a contract or purchase order number are entered jobnum, phsnum should not be entered and will be ignored"]
[null,"(5) If the Discount Amt is left blank, then it will be derived from the Vendor Setup and applied relative to the invoice amount. Please note if a value of 0 is desired then use 0, as using blank for 0 will not be handled the same."]
[null,"(6) If the Invoice is coded to a purchase order and the invoice causes the balance of the PO to be less than or equal to zero the purchase order status will be changed automatically to closed"]
[null,"(7) If posting period is left blank then the invoice date will be used as the posting period"]
[]
[]
[null,"Invoice Detail"]
[null,"COL1","filetype","Required","String (5)","Import","APINV","File Type"]
[null,"COL2","linetype","Required","String (1)","Import","D","Detail Record"]
[null,"COL3","invnum","Required","String (20)","Header","(Same as header)","Invoice No"]
[null,"COL4","vndnum","Required **","Numeric","Header","(Same as header)","Vendor No"]
[null,"COL5","prtdsc","Required","String (75)","Detail",null,"Line Description"]
[null,"COL6","ctclinnum","(1)","Numeric","Detail",null,"Contract\\PO Line No"]
[null,"COL7","prtnum","**","Numeric","Detail",null,"Part No"]
[null,"COL8","untdsc",null,"String (10)","Detail",null,"Unit of Measure"]
[null,"COL9","linqty","Required","Numeric (12,4)","Detail",null,"Quantity"]
[null,"COL10","linprc","Required (2)","Numeric (15,6)","Detail",null,"Price"]
[null,"COL11","actnum","Required **","Numeric","Detail",null,"Exp Account"]
[null,"COL12","subact","**","Numeric","Detail",null,"Exp Sub Account"]
[null,"COL13","invloc","**","Numeric","Detail",null,"Inventory Location"]
[null,"COL14","sernum",null,"String (30)","Detail",null,"Part Serial No"]
[null,"COL15","usrdf1",null,"String (50)","Detail",null,"User Def 1"]
[]
[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) If a contract or purchase order number entered the contract line no is required for all lines"]
[null,"(2) Price may not be a negative value. If the line amount is negative you must make the \"Quantity\" negative"]
[]
[null,"Job Cost Details (Required if a \"WIP Asset\" or \"Direct Expense\" account is used on any invoice line item)"]
[null,"COL1","filetype","Required","String (5)","Import","APINV","File Type"]
[null,"COL2","linetype","Required","String (1)","Import","J","Detail Record"]
[null,"COL3","invnum","Required","String (20)","Header","(Same as header)","Invoice No"]
[null,"COL4","vndnum","Required **","Numeric","Header","(Same as header)","Vendor No"]
[null,"COL5","jobnum","Required **","Numeric","Job Cost",null,"Job No"]
[null,"COL6","wrkord","**","String (20)","Job Cost",null,"Work Order No"]
[null,"COL7","dscrpt","Required","String (50)","Job Cost",null,"Description"]
[null,"COL8","phsnum","**","Numeric","Job Cost",null,"Phase No"]
[null,"COL9","cstcde","Required **","Numeric (15,3)","Job Cost",null,"Cost Code"]
[null,"COL10","csttyp","Required **","Numeric","Job Cost","1=Material; 2=Labor; 3=Equipment; 4=Subcontract; 5=Other; 6=Utilities; 7=Purchase Order; 8=Work Order; 9=User Def Type 9","Cost Type"]
[null,"COL11","cstamt","Required","Numeric (12,2)","Job Cost",null,"Cost Amount"]
[null,"COL12","ntetxt",null,"Text","Job Cost",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,"Equipment Cost Details (Required if a \"Equipment Expense\" account is used on any invoice line item)"]
[null,"COL1","filetype","Required","String (5)","Import","APINV","File Type"]
[null,"COL2","linetype","Required","String (1)","Import","E","Detail Record"]
[null,"COL3","invnum","Required","String (20)","Header","(Same as header)","Invoice No"]
[null,"COL4","vndnum","Required **","Numeric","Header","(Same as header)","Vendor No"]
[null,"COL5","eqpnum","Required **","Numeric","Equip Cost",null,"Equip No"]
[null,"COL6","eqdscrpt","Required","String (50)","Equip Cost",null,"Description"]
[null,"COL7","eqcstcde","Required **","Numeric (15,3)","Equip Cost",null,"Cost Code"]
[null,"COL8","eqcsttyp","Required **","Numeric","Equip Cost","1=Material; 2=Labor; 3=Equipment; 4=Subcontract; 5=Other; 6=Utilities; 7=Purchase Order; 8=Work Order; 9=User Def Type 9","Cost Type"]
[null,"COL9","cstamt","Required","Numeric (12,2)","Equip Cost",null,"Cost Amount"]
[null,"COL10","eqnotes",null,"Text","Equip Cost",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,"File Example:"]
[null,"APINV,H,\"ABC 246\",231,\"Test AP Inv\",\"7/20/2019\",\"7/20/2019\",\"7/20/2019\",1,1,\"\",\"\",,,\"\",\"\",\"\",\"\",,,,0,\"\",,"]
[null,"APINV,D,\"ABC 246\",231,\"Line 1 AP\",,,\"\",1,100,6040,,,\"\",\"\""]
[null,"APINV,J,\"ABC 246\",231,2017008,\"\",\"line 1\",,2010.000,1,100,\"\""]

{"sheet":"AP Invoice Form","index":1,"n_rows":38,"n_cols":13,"rows_kept":38,"cut":false,"hidden":false,"merged":[[11,2,11,3]]}
["Sample AP Invoice Form"]
[]
[]
[null,"Invoice No: *","AP Test 3",null,"Invoice Date: *","2017-05-25","Status: *",1]
[null,"Vendor: *",231,null,"Due Date: *","2017-05-25","Type: * ",1]
[null,"Description: *","AP Test 3",null,"Discount Date: *","2017-05-25"]
[null,"Subcontract #: ",null,null,"Shipping No: ",null,"User Def 1: "]
[null,"PO #: ",null,null,"Reference No: ",null,"User Def 2: "]
[null,"Job: ",null,null,false,null,"Discount Amt:"]
[null,"Phase:  ",null,null,null,null,"Retainage Amt:"]
[null,null,null,null,"","","Set To Pay Amt:"]
[null,"Notes:","test notes",null,null,null,false]
[]
[]
[null,null,"AP Invoice Details"]
[null,null,"Line Description *","Contract Line No","Part No","Unit of Measure","Quantity *","Price *","Exp Account *","Exp Sub Account","Inventory Location","Part Serial No","User Def 1"]
[null,null,"AP Line 1",null,null,null,1,50,"6000"]
[null,null,"AP Line 2",null,null,null,1,75,"6003"]
[null,null,"JC Line 1",null,null,null,2,25,"5001"]
[null,null,"JC Line 2",null,null,null,2,50,"5003"]
[null,null,"EQ Line 1",null,null,null,3,20,"5601"]
[null,null,"EQ Line 2",null,null,null,3,40,"5602"]
[]
[]
[]
[null,null,"Job Cost Details (Required only if a \"Direct Expense\" account is used on any AP Invoice line)"]
[null,null,"Job No *","Work Order No","Description *","Phase No","Cost Code *","Cost Type *","Cost\nAmount *","Notes"]
[null,null,2017008,null,"Test AP Invoice",null,"2120.000",4,150]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Equipment Cost Details (Required only if an \"Equipment Expense\" account is used on any AP Invoice line)"]
[null,null,"Equip No *","Description *","Cost Code *","Cost Type *","Cost Amount *","Notes"]
[null,null,1,"Test AP Invoice","2010.000",3,180]

{"sheet":"Grid Sample","index":2,"n_rows":14,"n_cols":26,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["Sample 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","COL24","COL25"]
[null,"APINV","H","Invoice No","Vendor No","Invoice Desc","Invoice Date","Due Date","Discount Date","Status","Invoice Type","Purchase Order No","Subcontract No","Job No","Phase No","Ship No","Reference No","User Def 1","User Def 2","Discount Amt","Retainage Amt","Set to Pay Amt","Hot List","Notes","Auto Close PO","Posting Period"]
[null,"APINV","D","Invoice No","Vendor No","Line Description","Contract Line No","Part No","Unit of Measure","Quantity","Price","Exp Account","Exp Sub Account","Inventory Location","Part Serial No","User Def 1"]
[null,"APINV","J","Invoice No","Vendor No","Job No","Work Order No","Description","Phase No","Cost Code","Cost Type","Cost Amount","Notes"]
[null,"APINV","E","Invoice No","Vendor No","Equip No","Description","Cost Code","Cost Type","Cost Amount","Notes"]
[]
[]
[null,"APINV","H","Inv25",231,"Inv 23 Test Invoice","2017-05-20","2017-05-20","2017-05-20",1,1,"PO 1",null,null,null,null,null,null,null,null,null,null,null,null,1]
[null,"APINV","D","Inv 25",231,"AP Line 1","ts",null,null,1,200,6002]
[null,"APINV","D","Inv 25",231,"AP Line 2",null,null,null,1,300,6003]
[null,"APINV","J","Inv 25",231,4000,null,"Materials",null,"1250.000",1,265.52999999999997]

{"sheet":"misc","index":3,"n_rows":3,"n_cols":4,"rows_kept":3,"cut":false,"hidden":false,"merged":[]}
["1 - Open",null,null,"1 - Contract"]
["2 - Review",null,null,"2 - Memo"]
["3 - Dispute"]