{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Local Variables","INSTRUCTIONS","Data","ErrorCodes","Errors","Print View","Validation","Templates"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":20,"comments":1,"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":"Local Variables","index":0,"n_rows":14,"n_cols":2,"rows_kept":14,"cut":false,"hidden":true,"merged":[]}
["User Information:"]
["Uniqname:","=Env(\"USERNAME\")"]
["Phone Number:"]
[]
[]
["File Information:"]
["Import File Location:","\\\\its-users.m.storage.umich.edu\\its-users\\HomeFolders\\jeffgree\\Desktop\\billing_upload.dat"]
[]
["Total Amounts",{"$f":"SUM(BillAmounts)","v":0}]
["Header Count",{"$f":"COUNTIF(Data!$A$3:$A$10000,\"Y\")","v":1}]
["Line Count",{"$f":"COUNT(Data!$M$3:$M$10000)","v":0}]
["Accounting Entries",{"$f":"COUNT(BillAmounts)","v":0}]
[]
[null,"Nice idea, this, but I couldn't get it to work consistently, so I'm using Global Variables."]

{"sheet":"INSTRUCTIONS","index":1,"n_rows":33,"n_cols":3,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
[null,"INSTRUCTIONS FOR VARIOUS SPONSOR BILLING UPLOAD TEMPLATE"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"COLUMN","DESCRIPTION"]
[null,"A","  Enter a Y for each separate invoice - do NOT use lower case y "]
[null,"B","  Choose SPNON from the drop down menu"]
[null,"C","  Enter the N99xxxxx customer number for the customer you are billing"]
[null,"D","  If the customer requires a PO number/reference to be printed on the invoice, fill in this column"]
[null,"E","  If your department has a unique reference number/name, fill in this column"]
[null,"F","  Enter your UM department name"]
[null,"G","  Enter a department contact unique name (This can be a group name) "]
[null,"H","The Header note will print at the top of the Invoice. Use this column if you have a note that pertains to the entire invoice."]
[null,"I","  Provide a description of what is being billed on this bill line"]
[null,"J","Choose the unit of measurement from the drop down menu (for services it is recommended that the unit of measure of each be chosen)"]
[null,"K","Enter the quantity being billed on this bill line (for services it is recommended that quantity of 1 be chosen)"]
[null,"L","  Enter the per unit price"]
[null,"M","Auto populates"]
[null,"N","  Enter info if a note is required for this particular billing line"]
[null,"O","  Enter info if a second note is required for this particular billing line"]
[null,"P","  Auto populates. Verify the Amount being billed on this accounting line"]
[null,"Q-W","  Short Code and Account Code or Fund/Dept/Prog/Class/Proj should be entered"]
[null,"X-AR","Use if billing line is being split between 2 ChartFields"]

{"sheet":"Data","index":2,"n_rows":3,"n_cols":31,"rows_kept":3,"cut":false,"hidden":false,"merged":[[0,15,0,22],[0,0,0,7],[0,8,0,14],[0,23,0,30]]}
["Last Modified: 10/26/2018\nHEADER INFO",null,null,null,null,null,null,null,"LINE INFO",null,null,null,null,null,null,"ACCOUNTING ENTRIES 1",null,null,null,null,null,null,null,"ACCOUNTING ENTRIES 2"]
["Create New Bill \n(Y or leave blank)","Business Unit \n(GNREC)","CUSTOMER\n(7 CHAR)","PO Reference\n(Max 30 char)","Dept Ref No\n(Max 30 char)","UM Dept Name\n(Max 30 char)","UM Dept Contact Uniquname\n(Max 30 char)","Header Note\n(Max 254 char)","Description\n(Max 30 char)","Unit of Measure","Quantity","Unit Price","Gross Extended","Line Note 1\n(Max 254 char)","Line Note 2\n(Max 254 char)","Amount","Short Code","Account","Fund","Department","Program","Class","Project/Grant","Amount","Short Code","Account","Fund","Department","Program","Class","Project/Grant"]
["Y","SPNON",""]

{"sheet":"ErrorCodes","index":3,"n_rows":41,"n_cols":5,"rows_kept":41,"cut":false,"hidden":true,"merged":[]}
["Error Code","Error Description","ErrorType","Tip","* Note:  Not possible if template not tampered with"]
[110,"Invalid Create New Bill field","Critical","Create New Bill field may only be \"Y\" or blank.","*"]
[115,"Data below end of obvious data","Critical","Data follows a row with blank customer.  It will not be processed."]
[120,"Not Flagged as New Bill","Critical","Row not flagged as new Bill, but data present."]
[130,"Invalid Business Unit","Critical","Business Unit may only be GNREC, GNPAY.","*"]
[140,"Invalid Customer","Critical","The Customer must be exactly 7 characters long.","*"]
[150,"Invalid Field Length","Critical","The Department Contact email must be between 3 and 30 characters long.","*"]
[155,"Email may not be sent externally","Critical","A domain may not be specified for the UM Department Contact email.  (It is for umich.edu only.)"]
[160,"Excess Field Length","Critical","Field length limited to 30 characters.","*"]
[170,"Excess Field Length","Critical","Field length limited to 254 characters.","*"]
[180,"Field missing","Critical","Required Billing Header field missing."]
[210,"UOM not Valid","Critical","The Unit Of Measure may only be one of the values specified in the dropdown.","*"]
[220,"Quantity may not be 0","Critical","Quantity on the Bill Line may not be 0."]
[230,"Price may not be 0","Critical","Unit Price on the Bill Line may not be 0."]
[240,"Gross is not Qty * Price","Critical","The Gross Extended amount does not equal the Quantity times the Price.","*"]
[310,"Fund missing","Critical","If the ShortCode field is blank, the Fund must be provided."]
[320,"Department missing","Critical","If the ShortCode field is blank, the Department must be provided."]
[330,"Shortcode not allowed","Critical","If there are any chartfields present, the ShortCode field should be blank."]
[340,"Account is required","Critical","The Account value must be provided on each accounting row and must be 6 numeric characters."]
[350,"Fund not 5 numeric characters","Critical","The Fund code must be 5 numeric characters."]
[360,"Dept not 6 numeric characters","Critical","The Department must be 6 numeric characters."]
[370,"ShortCode not 6 numeric characters","Critical","The ShortCode must be 6 numeric characters."]
[380,"Program must be 5 characters","Critical","The Program Code must be 5 characters long exactly."]
[390,"Class must be 5 characters","Critical","The Class Code must be 5 characters long exactly."]
[400,"Project/Grant must be 7 - 15 characters.","Critical","The Project/Grant must be 7 through 15 characters long."]
[410,"Amount cannot be zero","Critical","The first Accounting row cannot have a zero dollar amount."]
[420,"Amounts don’t sum to gross","Critical","The sum of the two Accounting Entry Amounts must equal the Gross Extended Amount.","*"]
[430,"Accounting Amount $0 but CFs exist","Critical","Chartfields are populated for this accounting entry, but the Accounting Amount is $0."]
[500,"Special character removed from cell","Warning","This cell contained a carriage return, line feed, or tab.  It has been removed."]
[610,"File type of upload file - CSV","Information","The file you opened is a .csv file."]
[620,"File type of upload file - DAT","Information","The file you opened is a .dat file."]
[630,"File type of upload file - Other","Information","The file you opened is neither a .csv nor a .dat file."]
[640,"File name doesn't match contents","Information","Not a CSV file nor a DAT file -OR- the contents don't match the name."]
[650,"Unexpected data in input file","Information","A row in the input file had a \"Create New Bill\" flag that was not a \"Y\" or blank ."]
[660,"Row has incorrect type, not loaded","Information","Row not loaded -- does not start with \"Y\" or \" \""]
[670,"Gross amount incorrect","Warning","Gross extended amount is not the multiple of the Price times the Quantity."]
[680,"Amount 2 is not Gross - Amount 1","Warning","The two Amounts do not add up to the Gross Extended Amount for the line."]
[710,"Too many columns in CSV file","Information","There are too many columns represented in the CSV file.  Extra columns ignored."]
[720,"Empty line found in CSV file","Information","Empty line found in CSV file.  No more lines loaded."]
[730,"Only 9998 rows may be loaded","Critical","Too many rows found in input file.  Only 9998 rows may be inserted to the Template."]
[810,"Import File Failed","Critical","An error occurred and your CSV or DAT file was not imported correctly."]

{"sheet":"Errors","index":4,"n_rows":2,"n_cols":6,"rows_kept":2,"cut":false,"hidden":false,"merged":[]}
[null,"Validation Results",null,null,null,{"$f":"NOW()","v":"2024-04-01T11:11:57.740"}]
[null,"Error Code","Error Description","Error Type","Cell Value","Cell Address"]

{"sheet":"Print View","index":5,"n_rows":18,"n_cols":13,"rows_kept":18,"cut":false,"hidden":false,"merged":[[14,3,14,13],[13,3,13,13],[7,8,7,9],[12,3,12,8],[7,4,7,5],[7,12,7,13],[8,4,8,13],[9,2,9,13]]}
[]
[null,"Control Record:"]
[null,null,null,null,"Batch Date:",null,null,null,null,null,null,null,"Bill Author:"]
[null,null,null,null,null,null,null,"Bill lines",null,"Total Amount"]
[null,"Current Bill Information:"]
[]
[null,null,null,"Business Unit:",null,null,null,"Customer: ",null,null,null,"Preparer Contact Uniqname:"]
[null,null,null,"PO Reference:",null,null,null,"Dept Ref No:",null,null,null,"UM Dept Name:"]
[null,null,null,"Header Note:"]
[null,null,"This document represents the data that wil be loaded into M-Pathways."]
[null,"Billing Line:"]
[null,null,"Line No.","Description",null,null,null,null,null,"Unit of Measure","Quantity","Unit Price","Gross Extended"]
[]
[null,null,"Note 1:"]
[null,null,"Note 2:"]
[]
[null,"Accounting Entries:"]
[null,null,null,"Shortcode","Account","Department","Fund Code","Class Field","Program Code","Project / Grant","Amount"]

{"sheet":"Validation","index":6,"n_rows":32,"n_cols":7,"rows_kept":32,"cut":false,"hidden":true,"merged":[]}
[]
["If the Allowable Values change, modify the lists, and verify that the Formulas: Defined Names: Name Manager list reflects the entire new range."]
[]
["Lookup for Business Unit  (BU_Lookup)",null,null,null,"Lookup for Create New Bill (Y)"]
[null,"GNPAY","General Receivables - Payroll",null,null,"Y","This row starts a new bill"]
[null,"GNREC","General Receivables"]
[null,"SPNON","Various Sponsors",null,null,null,"Y_Only is just the Y above.  (Started as Y/N choice)"]
[]
["gnmer removed from the above list 8/13/2013"]
["spnon added to the above list 1/14/2014"]
[]
[]
["Lookup for Unit of Measure"]
["len(disp)","Display","Pass"]
[{"$f":"LEN(B15)","v":5},"Books","BK"]
[3,"Box","BOX"]
[4,"Days","DAY"]
[4,"Each","EA"]
[4,"Hour","HR"]
[5,"Hours","HRS"]
[3,"Job","JA"]
[5,"Miles","MIL"]
[7,"Minutes","MIN"]
[5,"Month","MO"]
[5,"Pound","LBS"]
[7,"Seconds","SEC"]
[5,"Sheet","SH"]
[4,"Unit","UN"]
[9,"US Dollar","USD"]
[4,"Week","WK"]
[4,"Year","YR"]
[{"$f":"\"Max = \" & MAX(A15:A31)","v":"Max = 9"},"UOM_Lookup above","UOM_Lookup_Pass both cols."]

{"sheet":"Templates","index":7,"n_rows":40,"n_cols":15,"rows_kept":40,"cut":false,"hidden":true,"merged":[[23,3,23,14],[24,3,24,14],[5,3,5,8],[6,3,6,13],[7,3,7,13]]}
[]
[null,null,null,"Template in case there are multiple lines in the current Bill"]
[]
[null,"Billing Line:"]
[null,null,"Line No.","Description",null,null,null,null,null,"Unit of Measure","Quantity","Unit Price","Gross Extended"]
[]
[null,null,"Note 1:"]
[null,null,"Note 2:"]
[]
[null,"Accounting Entries:"]
[null,null,null,"Shortcode","Account","Department","Fund Code","Class Field","Program Code","Project / Grant","Amount"]
[]
[]
[null,null,null,"The above is BillLineTemplate"]
[]
[null,null,"Bill area skips:"]
[null,null,"The Gross Extended column in M:",null,null,null,null,"=IF(K3=\"\",\"\",IF(L3=\"\",\"\",+K3*L3))"]
[null,null,"The Accounting Entry 2 amount in column X:",null,null,null,null,"=IF(M3=\"\",\"\",IF(P3=\"\",\"\",IF(M3=P3,\"\",+M3-P3)))"]
[null,null,"=Data!$A$3:$L$1048576,Data!$N$3:$W$1048576,Data!$Y$3:$AE$1048576"]
[]
[null,"Billing Line:"]
[null,null,"Line No.","Description",null,null,"Unit of Measure","Quantity","Unit Price",null,"Gross Extended","PO Reference","Dept Ref No","UM Dept Name","Preparer Contact Uniqname"]
[]
[null,null,"Note 1:"]
[null,null,"Note 2:"]
[]
[null,"Accounting Entries:"]
[null,null,null,"Shortcode","Account","Department","Fund Code","Class Field","Program Code",null,"Project / Grant","Amount"]
[]
[]
[null,null,null,"The above is OLD BillLineTemplate which was invalidated 9/12/2013"]
["Old Column Widths"]
["0.75",null,null,"14.57","9.71","9.43","10.86","8.43","10.43","0.58",null,"14.43","11.71","15.57","15.86"]
[null,"1.43",null,null,null,null,null,null,null,null,"16.86"]
[null,null,"6.29"]
[]
["New/Current column Widths (09/12/2013)"]
["0.75",null,null,"14.57","9.71","10.86","9.57","8.43","10.29","16.86","16.86","14.43","16","15.57","N/A"]
[null,"1.43"]
[null,null,"6.29"]