Skip to content Skip to main navigation Skip to footer

Sales Order Processing

Purpose: To process all orders either raised externally or in the Submitter company
Confirmed order to be automatically imported to Prelude Desktop for further processing and stock control.

Process:
In \Automation\ Pending\, Prelude Desktop will scan for CSV files name YYYYMMDDHHMMSSMMM-CC-TTT.CSV” where:

  • CC – Company Number – the Submitter company two-alphanumeric code
  • TTT – “SOP” – Sales Order Processing
  • YYYYMMDDHHMMSSMMM – Date/Time – date/time stamp
  • CODE – Char(8) – Customer code in Prelude Desktop (validated)
  • NAME – Char(30) – Customer Name
  • ADD1 – Char(30) – Address line 1
  • ADD2 – Char(30) – Address line 2
  • ADD3 – Char(30) – Address line 3
  • ADD4 – Char(30) – Address line 4
  • POST – Char(10) – Postcode
  • TEL – Char(16) – Telephone
  • CONT – Char(30) – Contact
  • NUMBER – Integer – Order number (validated)
  • ORDER DATE – Date – Original order date (validated)
  • REF – Char(16) – Optional reference (for YSP, this is the unique Flightdeck ID)
  • PMT_AMT – Number – Total cumulative amount paid (if any) (>=0)
  • PMT_AMT >= PSOFA
  • PMT_DATE – Date – Date of most recent payment for this order (validated)
  • PMT_METHOD – Char(10) – “Credit”, “Cash”, “Cheque”, “Card” (if any) (validated)
  • REF2 – Char(16) – Optional reference (for YSP, this is Customer’s PO Ref)
  • FRANCHISEE – Char(2) – Company ID of the franchisee for this order (validated)
  • B2B – Char(6) – ”MASTER” – for master/slave of b2b (if any)
  • PROD – Char(16) – Stock code (validated)
  • LOCATION – Char(2) – Stock location code (validated, ignored if disabled)
  • QTY – Number – Quantity (>=0)
  • NETT – Number – Total Nett Amount for order line item
  • VAT_CODE – Char(1) – VAT Code for order line item (validated)
  • DELIVERED – Number – Cumulative delivered quantity (validated)
  • QTY >= DELIVERED >= DSOFA
  • INVOICED – Number – Cumulative invoiced quantity (validated)
  • QTY >= INVOICED >= ISOFA
  • DELIVERY DATE – Date – Date of most recent delivery for this order (validated)
  • INVOICE DATE – Date – Date of most recent invoice for this order (validated)
  • MATCH DATE – Date – Date invoice is matched to payment for accounting
  • UID – Char(38) – System-generated field to identify B2B transactions
  • LINE – Char(16) – To uniquely identify line item for order amendments
  • VAT_AMOUNT – Number – Optional manual VAT amount (same sign as NETT)

Address is delivery address
DD will assemble order header from details repeated in each line item. PROD is first line-item field
DD will calculate individual item Nett price (if required) from QTY and NETT
DELIVERED/INVOICED and DELIVERY DATE/INVOICE DATE apply to the particular line item and will trigger the standard delivery/invoice stages in DD, which will assign delivery note and invoice numbers
When DELIVERED is more than the same number in Prelude Desktop, a delivery will be processed for the change in cumulative delivered quantity on the DELIVERY DATE
When INVOICED is more than the same number in Prelude Desktop, an invoice will be processed for the change in cumulative invoiced quantity on the INVOICE DATE
When DELIVERED = INVOICED = QTY for all line items, the order is completed
In practice for YSP, these will always be either 0 or QTY
Nominal accounts to which Credit, Cash, Cheque, Card receipts will be posted will be set in a Control mapping file SOPReceiptMapping.csv
If customer invoice email address exists, DD will email invoice automatically. Otherwise manually
Process the following subsequent transactions individually and independently based on the trigger criteria
If PMT_AMT > PSOFA (before this order) and PMT_METHOD validated in SOPReceiptMapping.csv

Generate YYYYMMDDHHMMSSMMM-CC-TTT.CSV to Submitter to process SL receipt
CC – Company Number – the Submitter company code 01
TTT – “SLR” – Sales Ledger Receipt
YYYYMMDDHHMMSSMMM – Date/Time stamp
CODE – Char(8) – Customer Account code in Prelude Desktop
DATE – Date – PMT_DATE from order file
REF – Char(16) – REF from order file
REF2 – Char(16) – REF2 from order file
B2B – Char(6) – ”MASTER”
AMOUNT – Number – Change in PMT_AMT per DD records
BANK – NL Code – From SOPReceiptMapping.csv
MATCH_DATE – Date – Empty
Post as unallocated


Update PSOFA to new PMT_AMT value
If FRANCHISEE company is validated in DATA.000 Automation parameters, generate/update the b2b SOP/POP orders between franchisee and franchisor (Specific to YSP)
If this is a new customer order
Generate MASTER CSV to create SOP order in Parent company
Generate SLAVE CSV to create POP order in franchisor company
Name these b2b files YYYYMMDDHHMMSSMMM-CC-SOP/POP-GUID.csv
Where GUID is saved with the customer, MASTER and SLAVE orders to uniquely link them for subsequent identification and processing.
GUID is created when the customer order is processed. If the generated CSVs can’t be processed immediately because of locked tables or for whatever reason, the GUID saved with the customer order would otherwise be lost.
For all other processes, GUID is not used and/or is ignored.


If this is an existing customer order
Identify the b2b orders in 01 and RECEIVER companies from GUID in the existing customer order.
Process the change in the customer order and generate CSVs to process the change in the b2b orders. GUID is not needed in these CSV file names.
The generated YYYYMMDDHHMMSSMMM-CC-SOP-GUID.CSV data is as follows:
CC – Company Nr Receiver Company
TTT – “SOP” Sales Order Processing
YYYYMMDDHHMMSSMMM – Date/Time stamp
CODE – Char(8) – SL Code in FRANCHISEE company which is linked to 01 company
NAME – Char(30) – Blank – populate new order with SL details
ADD1 – Char(30) – Blank – populate new order with SL details
ADD2 – Char(30) – Blank – populate new order with SL details
ADD3 – Char(30) – Blank – populate new order with SL details
ADD4 – Char(30) – Blank – populate new order with SL details
POST – Char(10) – Blank – populate new order with SL details
TEL – Char(16) – Blank – populate new order with SL details
CONT – Char(30) – Blank – populate new order with SL details
NUMBER – Integer – Blank, assign next Prelude Desktop SO number
ORDER DATE – Date – ORDER DATE from customer order file
REF – Char(16) – REF from order file
PMT_AMT – Number – Blank
PMT_DATE – Date – Blank
PMT_METHOD – Char(10) – Blank
REF2 – Char(16) – REF2 from order file
FRANCHISEE – Char(2) – Blank
B2B – Char(6) – ”MASTER”
PROD – Char(16) – Stock code
QTY – Number – Quantity
NETT – Number – Total Nett Amount for order line item
VAT_CODE – Char(1) – VAT Code for order line item
DELIVERED – Number – DELIVERED from order file
INVOICED – Number – INVOICED from order file
DELIVERY DATE – Date – DELIVERY DATE from order file
INVOICE DATE – Date – INVOICE DATE from order file
MATCH DATE – Date – MATCH DATE from order file
UID – Char(38) – System field to identify B2B transactions
LINE – Char(16) – LINE from order file


The generated YYYYMMDDHHMMSSMMM-CC-POP-GUID.CSV data is as follows:
CC – Company Nr 01
TTT – “POP” – Purchase Order Processing
YYYYMMDDHHMMSSMMM – Date/Time stamp
CODE – Char(8) – PL Code in 01 company which is linked to FRANCHISEE company
NAME – Char(30) – Blank – populate new order with PL details
ADD1 – Char(30) – Blank – populate new order with PL details
ADD2 – Char(30) – Blank – populate new order with PL details
ADD3 – Char(30) – Blank – populate new order with PL details
ADD4 – Char(30) – Blank – populate new order with PL details
POST – Char(10) – Blank – populate new order with PL details
TEL – Char(16) – Blank – populate new order with PL details
CONT – Char(30) – Blank – populate new order with PL details
NUMBER – Integer – Blank, assign next Prelude Desktop PO number
ORDER DATE – Date – ORDER DATE from customer order file
REF – Char(16) – REF from order file
PMT_AMT – Number – Blank
PMT_DATE – Date – Blank
PMT_METHOD – Char(10) – Blank
REF2 – Char(16) – REF2 from order file
FRANCHISEE – Char(2) – Blank
B2B – Char(6) – ”SLAVE”
PROD – Char(16) – Stock code
QTY – Number – Quantity
NETT – Number – Total Nett Amount for order line item
VAT_CODE – Char(1) – VAT Code for order line item
DELIVERED – Number – DELIVERED from order file
INVOICED – Number – INVOICED from order file
DELIVERY DATE – Date – DELIVERY DATE from order file
INVOICE DATE – Date – INVOICE DATE from order file
MATCH DATE – Date – MATCH DATE from order file
UID – Char(38) – System field to identify B2B transactions
LINE – Char(16) – LINE from order file
When these B2B SOP/POP CSV files are processed, they in turn will themselves generate B2B SOP/POP DELIVERY/RECEIPT/INVOICE CSV files as required.

The standard criteria by which to determine whether an order is new or existing are
NUMBER is blank is a new order
NUMBER is populated is an existing order with this order number
If no order exists with order number NUMBER, fail with an “order not found” message
For YSP, ignore the standard criteria and apply the following specific criteria
An order file is a new order if NUMBER is blank AND there are no existing orders with reference = REF, or if NUMBER and REF are both blank
An order file is for an existing order if NUMBER is populated
If no order exists with order number NUMBER, fail with “order not found”
An order file is for an existing order if NUMBER is blank AND there is one order in the database with reference = REF
If there is more than one order, fail with a “more than one order reference REF”
Overview of the general SOP/POP automation process to illustrate the stages of MASTER/SLAVE CSV generation and processing.

CSV file records details are specified above.
Processed in this way, we eliminate issues that may otherwise arise if the destination databases are locked for access and we ensure that no duplicate SLAVES are produced from re-processed MASTERS.

Open and process the SOP/POP file. If the destination database is locked, stop here. Otherwise:
If this is a new order or a change to the order QTY of an existing order, create the new order or amend the existing order quantities and update order with ORDER DATE
If this is a new order with non-zero DELIVERED quantity or a change to the DELIVERED quantity of an existing order, process the delivery/receipt of the change quantity on the DELIVERY DATE
If this is a new order with non-zero INVOICED quantity or a change to the INVOICED quantity of an existing order, process the invoice of the change quantity on the INVOICE DATE
If this is a new order with non-zero PMT_AMT or a change to PMT_AMT of an existing order, generate the CSV that will process the customer receipt on ORDER DATE
(1) YYYYMMDDHHMMSSMMM-CC-SLR/PLP.CSV (MASTER)
If SOP/POP is a MASTER CSV and the SL/PL account has a linked Prelude Desktop company, generate the POP/SOP SLAVE CSV file.
If originating file is a new SOP/POP order
Generate GUID and update originating Prelude Desktop SOP/POP order with this GUID
(2) YYYYMMDDHHMMSSMMM-CC-POP/SOP-GUID.CSV (SLAVE)
If originating file is an existing SOP/POP order
Identify the POP/SOP order in the destination company by GUID in the existing customer order
(2) YYYYMMDDHHMMSSMMM-CC-POP/SOP.CSV (SLAVE)
GUID suffix in file name is not necessary as this is already recorded in existing orders
GUID is required to link the POP/SOP orders for subsequent processing
If FRANCHISEE company is validated in DATA.000 Automation parameters
This applies only to YSP SOP CSVs
If originating file is a new customer order
generate the SOP MASTER CSV to process the YSP internal b2b SOP order in the franchisee company
Generate GUID and update originating Prelude Desktop SOP order with GUID
Theoretically, GUID could already be populated from the above process, in which case use this GUID. In practice, this won’t happen.
(4) YYYYMMDDHHMMSSMMM-CC-SOP-GUID.CSV (MASTER)
If originating file is an existing customer order
Identify the SOP order in SUBMITTER company by GUID in the existing customer order
generate the SOP MASTER CSV to process the RECEIVER internal b2b SOP order in the franchisee company
(4) YYYYMMDDHHMMSSMMM-CC-SOP.CSV (MASTER)
GUID suffix in file name is not necessary as this is already recorded in existing orders

Then process the CSV files generated by the above process
(1) YYYYMMDDHHMMSSMMM-CC-SLR/PLP.CSV (MASTER)
If the SL/PL account has a linked Prelude Desktop company, generate the SLAVE CSV file
(1) YYYYMMDDHHMMSSMMM-CC-PLP/SLR.CSV (SLAVE)
(2) YYYYMMDDHHMMSSMMM-CC-POP/SOP-GUID.CSV (SLAVE)
“-GUID” is omitted or ignored from this file name if an existing order
(3) YYYYMMDDHHMMSSMMM-CC-SLI.CSV (MASTER)
If the SL/PL account has a linked Prelude Desktop company, generate the SLAVE CSV file
(2) YYYYMMDDHHMMSSMMM-CC-PLI/SLI.CSV (SLAVE)
(4) YYYYMMDDHHMMSSMMM-CC-SOP-GUID.CSV (MASTER)
If the SL/PL account has a linked Prelude Desktop company, generate the SLAVE CSV file
(3) YYYYMMDDHHMMSSMMM-CC-POP-GUID.CSV (SLAVE)
“-GUID” is omitted or ignored from these file names if an existing order

Then process the CSV files generated by the above process
(1) YYYYMMDDHHMMSSMMM-CC-PLP/SLR.CSV (SLAVE)
(2) YYYYMMDDHHMMSSMMM-CC-PLI/SLI.CSV (SLAVE)
(3) YYYYMMDDHHMMSSMMM-CC-POP-GUID.CSV (SLAVE)
“-GUID” is omitted or ignored from these file names if an existing order

The criteria that determine whether an incoming order is new or existing will probably be specific to each Automation platform.