Enterprise Invoice Export

Introduction

Enterprise requires a compilation of all invoices that have been generated within a specified number of past days. The format and characteristics of the exported data are defined by Enterprise.

Purpose

Enterprise will import the generated Invoice file into their accounting system for further processing.

File format

Excel - *.xslx

Fields

Column Position	Header Name	Description	Datatype	Validation	Max length	Mandatory	Default Value	Example
A	TEMPLATE ID	Vendor name and date when the export file was created	string	A-Z, 0-9 and all special characters	100	Yes		DigitalInnk_02/11/2022
B	VENDOR NAME	Supplier's group Name	string	A-Z, 0-9 and all special characters	50	Yes		Grand Repair
C	VENDOR ID	Supplier Id in ViSN	string	A-Z, 0-9 and all special characters	10	Yes		0000006789
		This is the supplier Id assigned to the supplier in ViSN. Refer to below image This value is expected to be 10 characters in length. hence if the supplier id is less than 10 characters, adding a prefix with zero is performed so it will be consistent Ex: GRAUTO shall become 0000GRAUTO
D	VENDOR SET ID	Vendor Id that should be mapped to Enterprise's account	String	A-Z characters	6	Yes	APGBR	APGBR
		Enterprise needs a predefined identifier for their processing logic, data is maintained in Firebase for the same at document: /leasecompany/[LeaseId]/config/exportsConfig attribute: vendorSetId If a value exists, then it will be used as vendor-set-id for the export If the document or the attribute doesn't exist, then the field will be left blank in the export See Appendix 1 for the document sample
E	VENDOR LOCATION	Postcode/EirCode	String	A-Z & 0-9	20	Yes		OX144SA
		Enterprise needs a custom location for a few supplier groups, the data is maintained in Firebase for the same at document: /leasecompany/[LeaseId]/config/exportsConfig attribute: vendorBillingEntityLocations The postcode will be replaced based on the vendorBillingEntityLocations using the following methodology Get Vendor billing locations that are pre-configured, and received in sets of account-id and postcode If a value exists for the account to which the enquiry belongs, use the corresponding postcode for the export If the value doesn't exist for the account, use the postcode of the supplier See Appendix 1 for the document sample
F	STATEMENT DATE	Export generated date	date	nn/nn/nnnn in dd/MM/yyyy format	10	Yes		16/04/2023
G	INVOICE NUMBER	Invoice number	string	A-Z & 0-9 and '/'	20	Yes		23456/1
		This will be the supplier invoice number with a slash and version number for each line or group of invoiced transaction. Ex: Supplier Invoice Number NT/124881000 Line 1: NT/124881000/1 Line 2: NT/124881000/2 Line 3: NT/124881000/3 Refer to below image
H	REPAIR DATE	4 days prior check‑in date	date	nn/nn/nnnn in dd/MM/yyyy format	10	Yes		16/04/2023
		4 calendar days before the date the vehicle was checked in to the garage. Refer to below image
I	VEHICLE DESCRIPTION	Make Model of the vehicle	string	A-Z & 0-9, space, Dash, hyphen and underscore	150	No		CITROEN C5 AIRCROSS
		This is the make and model of the vehicle the enquiry has raised against. Refer to below image
J	UNIT NUMBER	BLANK
K	LICENSE	Vehicle Registration number	string	A-Z & 0-9 Dash, hyphen	12	Yes		MH12JBS
		This is the registration number of the vehicle the enquiry has raised against. Refer to below image
L	VIN	Chassis number of vehicle	string	A-Z & 0-9	17	No		HXGKEAAMJ9K361461
		This is the Chassis number of the vehicle the enquiry has raised against. Refer to below image
M	CLAIM NUMBER	Recharge Notes	string	A-Z and 0-9 & All Special Characters	150	No		NXTORD001
		Enterprise classifies the job lines in a jobSheet based on the claim number, these classifications are entered in the recharge notes against the jobLine Classification is done as per below methodology Non Recharge Line(s): The lines that don't have recharge enabled should be classified and grouped as 'Other' Recharge Line(s) without claim number: The lines that have the recharge enabled, but no mention of recharge notes should be classified and grouped as 'Other' as well Recharge Line(s) with claim number: The lines that have the recharge enabled, and a recharge note is added then they should be classified and grouped together based on the recharge note. If there were more than one recharge notes added for a job sheet, there shall be multiple groups of lines to add in the export
N	INVOICE TOTAL	Invoice Total Cost	number	nnnn.nn	6	Yes		280.08
		This is the total sum of the job line or the group of job lines grouped based on the claim number that is entered in the recharge notes field (col-S + col-AA),
O	ACCOUNT	a custom value based on claim number	string	A-Z and 0-9 & All Special Characters	12	Y		CLM001
		Enterprise uses a static account for all the job lines that are classified as 'Other' When claim number is 'Other' then populate with 1681 - 500300 When the claim number is not 'Other' then leave the field blank
P	MILEAGE	Job sheet mileage	number	0-9 numbers	10	No		20120
Q	DESCRIPTION TYPE	BLANK
R	DESCRIPTION	BLANK
S	DESCRIPTION AMOUNT	number	nnnn.nn	0-9 numbers	6	Yes		304.21
		This is the sum of the pre-vat total of a job line or the group of job lines grouped based on the claim number that is entered in the recharge notes field
T	LABOR HOURS	BLANK
U	LABOR AMOUNT	BLANK
V	PARTS AMOUNT	Net Invoice Cost	number	nnnn.nn	6	Yes		125.38
W	MATERIAL AMOUNT	BLANK
X	TOWING	BLANK
Y	STORAGE	BLANK
Z	APPRAISAL/TEARDOWN	BLANK
AA	TAX	Invoice VAT	number	nnnn.nn	6	No		94.82
		This is the sum of the VAT amount of a job line or the group of job lines grouped based on the claim number that is entered in the recharge notes field
AB	GPBR	Branch	string	A-Z and 0-9 & All Special Characters	20	No	BLANK	U8T9
		This is a custom value formulated based on the branch, repair date and branch history branch history A chronological trace of stock import where the modification reason is a change to the branch field of the respective stock repair date Column H of record being exported branch The branch is determined based on the location of the vehicle on a specific repair date, which is ascertained by examining the branchHistory array of the stock document. The repair must fall within the startDate and endDate parameters or be the most recent record without an end date. If there is no match for a given date range, use blank value as branch. click on this link link Branch Starts With Example(s) GPBR formulated U1 U1H3 U1S8 U2 U2UM U2S9 U3 U338 U3SB U8 U804 U8T9 SC SC16 SCS9 UK UKKEO UK99 UM UM65 UM99 UZ UZVS UZ99 I1 I196 I101 I125 I118 I1S1 GPBR is left same as branch code if the conditions mentioned in above table are not satisfied GPBR will be left blank when branchCode doesn't exist (or is blank) , to know how GPBR is formulated

Data source

At the time of invoicing the enquiry the data is generated and kept ready for export

The generated data is maintained in Firebase at

doc: /accounts/[AccountId]/serviceUnits/[ServiceUnitId]/enquiries/[EnquiryId]/invoices/groupedByClaimNumber

attribute: records

Setup Required

A Scheduler should be created cloud scheduler with the following parameters

• Name: GenerateEnterpriseExportXLS
• Region: europe-west2
• Frequency: 59 23 * * 7
• TimeZone: GMT London
• Topic: projects/biddirect-2/topics/generateEnterpriseExportXLS

• Message body: a JSON object including the following parameters

  • dateTimeStamp: in format yyyy-MMM-dd ex 2022-Sep-30 , when skipped it will default to current date

  • leaseCompanyId: id that needs to be considered for enterprise lease

  • forNoOfPreviousDays: No.of.days the export should go back from the date mentioned, when skipped defaults to 7 i. e., a week

  • destinationFolder: folder which the export file must be placed in, it's optional

Appendix 1: export config document sample

{
  "vendorBillingEntityLocations": [
    {
      "accountId": "4eyYUszi3iYZzdw189rb",
      "locationCode": "OX144SA"
    },
    {
      "accountId": "ALRbwQqIVhr0wBjWu0Wp",
      "locationCode": "B980DA"
    }
  ],
  "vendorSetId": "APGBR"
}

Status: Accepted
Category: Protected
Authored By: Rama on May 10, 2023

Revisions
24 Dec, 2023 - Revise to include GPBR formulation based on branch code and locale, VN-10657
11 Nov, 2023 - Revise Repair Date column, VN-10291
3 Aug, 2023 - Revise to add claim number grouping, VN-7372
11 May, 2023 - Add Enterprise Export Documentation, VN-6517