|
Quantum VS Help: Purchasing |
One method of 'bulk-generating' Purchase Orders (see note) is to extract Product data to a .csv file, then retrieve the file and manually review and update the listed products, suppliers, quantities and other details. On saving the file and placing it in the specified directory on the server, Purchase Orders are automatically generated in Quantum VS - across multiple suppliers if required. You may optionally generate Parked and Suggested Purchase Orders as well as 'actual' Purchase Orders. This method allows for great flexibility in terms of specifying product reorder quantities based on your own specialist knowledge of the market.
Note: For further details see Generating Purchase Orders In Bulk. An alternative method of 'bulk-generating' Purchase Orders is to run the Suggested Orders Report.
Briefly, the steps required are as follows:
1. Extract current product data from your database to a .csv file. See Extracting Product Data To A .CSV File.
2. Use Excel to open the file, manually review and update the listed products, suppliers, quantities and other details, then save the file. This file essentially specifies, for a particular Branch, which Products are being ordered, from which Supplier, and in what Quantity. See Reviewing And Editing Product Data In The .CSV File.
3. Place the file in the required directory on the server. Purchase Orders are then automatically generated in Quantum VS. See Server File Placement And Order Generation.
You may use the pgAdmin applicationto export data to a .csv file. This is done by running a SQL query against one of the available standard views - edp_bpo_import (i.e. 'bulk Purchase Order import'). The procedure is:
1. Open pgAdmin and, using the Object Browser in the left-hand panel, select the required server and database (quantumvs).
2. Click the SQL button on the toolbar to execute a SQL query.
3. In the SQL Editor tab/pane, enter the following query:
select * from std_views.edp_bpo_import
Note: You may potentially add limit 10 to limit the query to 10 rows, for example.
Note: You may wish to build up a more detailed SQL query if you have sufficient expertise. For example, adding where bpo_m_b_branch = '1' will return data only for Branch 1.
4. Click Execute Query (or select Query > Execute). Data is displayed in the Output Pane below.
5. Select File > Export.
6. In the Export Data To File window:
make sure the selected Column separator is 'comma' (although you may optionally change this in Excel);
enter/select the required Filename (e.g. C:\Users\matt\Desktop\productdata.csv); then
click OK.
7. Click OK to the 'Data export completed successfully' message. The file is created in the specified location.
The next stage is to open the .csv file using Excel, then manually review and update the listed products, suppliers, quantities and other details based on your own specialist knowledge of the market.
When you open the file in Excel you will see approximately 90 columns of data, headed bpo_m_a_company, bpo_m_b_branch, bpo_m_c_supplier_account, and so on. These correspond to fields in the Product stock record.
Hint: To quickly expand all columns to their optimum width: (1) click Ctrl + A to select all cells, then (2) double-click any one of the column partition lines.
The first 20 columns (A to T) are prefixed 'bpo_' (i.e. Bulk Purchase Order). Some of these columns are mandatory, while others are optional, as described below.
Important: You must not change the order of any column prefixed 'bpo_'. However, you may delete the optional columns if you wish, as described below.
Each row represents a single product. You may delete rows corresponding to Products you do not wish to generate Purchase Orders for.
The first nine columns - A to I - are prefixed 'bpo_m_', where 'm' means 'mandatory'. These are the columns used by the import routine in generating Purchase Orders from the 'final draft' .csv file.
Important: You must not delete, or change the order of, any column prefixed 'bpo_m_'.
In the empty mandatory columns you must manually enter data before saving the 'final draft' .csv file. This includes the Quantity of each Product to be ordered, and the Staff IDs of the User raising the Purchase Order and making the request.
Some mandatory columns will contain default values, which you may leave as they are or overwrite as required. For example, to order the specified Product from a different Supplier you would replaced the listed Supplier Account Code with the required Supplier Account Code.
Summary of the mandatory columns:
|
Column |
Definition (* = mandatory field)
|
|
A: bpo_m_a_company
|
The Company number for which Purchase Orders will be generated. This will typically be 1 but you may overwrite values as necessary.
|
|
B: bpo_m_b_branch
|
The Branch number for which Purchase Orders will be generated (0, 1, 2, 3 etc.). You may overwrite values as necessary.
Notes: (1) You may limit the data extraction to only return data for a specific Branch. (2) You may not generate Purchase Orders for Branch 0. |
|
C: bpo_m_c_supplier_account
|
The account number of the Supplier for which Purchase Orders will be generated (for the corresponding Product). You may overwrite values as necessary.
|
|
D: bpo_m_d_site_code
|
The Site Code of the Supplier for which Purchase Orders will be generated. This will typically be 0 but you may overwrite values as necessary.
|
|
E: bpo_m_e_raised_by
|
Enter the Staff ID Code of the User raising the Purchase Order.
|
|
F: bpo_m_f_author_by
|
Enter the Staff ID Code of the User editing the current line/file.
|
|
G: bpo_m_g_doc_type
|
Enter a 3-digit code corresponding to the type of Purchase Order to be created, from the following:
|
|
H: bpo_m_h_product_code
|
The Product Code of the Product for which Purchase Orders will be generated. Delete the line if you do not wish to generate Purchase Orders for this product.
|
|
I: bpo_m_i_qty
|
Enter the quantity of this Product to be ordered (from the corresponding Supplier).
|
The next 11 columns - J to T - are prefixed 'bpo_o_', where 'o' means optional. These include fields such as Unit of Measure, Cost Price, Cost Price Unit of Measure, and Required by Date.
You may delete the non-mandatory columns J to T if you wish. Alternatively you may keep and add data to/overwrite data in these columns if you wish, but you must not change their order.
You must delete the remaining columns not prefixed 'bpo_' before saving the 'final draft' .csv file.
The final draft of the import file should be saved in Excel in .csv format. Filenames with spaces are NOT permitted.
The final stage involves placing the final draft of the .csv file in the required directory on the server: /usr/bct/data/BPO/INQ
A server script will automatically generate the Purchase Orders (Suggested, Parked or Confirmed as specified) for the specified Branch(es) and Supplier(s), with the specified products and quantities. The import routine will validate and sort the data on Company, Branch, Supplier, Site Code, PO Document Type, Raised By and Authorised By and attempt to create a Purchase Order in Quantum for each unique group of these.
Notes: (1) The required directory is the INQ directory beneath the directory specified by the dd_BPO environment variable - called BPO - beneath the COBDATA environment variable, which is usually set to /usr/bct/data. (2) Professional Services will set up a script which runs on the server looking for the presence of files in the/usr/bct/data/BPO/INQ directory - and when one is detected to call the COBOL routine to process that file.
You may now retrieve the generated Purchase Orders and amend and Complete these as necessary. See Completing And Printing Purchase Orders.
If any of the generated Purchase Orders were:
Parked (i.e. have Parked Status): You will need to retrieve the Parked Purchase Order and review/amend/Complete it as necessary. See Parking A Purchase Order.
Suggested (i.e. have Suggested Status): You will need to retrieve the Suggested Purchase Order and review/amend/Convert it to an 'actual' Purchase Order. See Converting Suggested Purchase Orders.
The import routine generates an audit indicating orders which have been successfully imported. A separate audit may be produced indicating failed orders which could not be imported. If a row belongs to a valid group but the product code is invalid, for example, the row may be added to a valid order created for that group but as a text line rather than an actual product line. This will be indicated in the audit.
The audit may be printed or emailed by setting parameters in the quantumvs.ini file:
#BULK PO FIELDS
bpo_orders_in_branch_audit=on
bpo_orders_in_audit_prn_0002=137
bpo_orders_in_audit_from=quantumvs@edp.co.uk
#bpo_orders_in_audit_to_0001=GEdwards@edp.co.uk
bpo_orders_in_audit_to=GEdwards@edp.co.uk
You can specify if separate branch audits will be produced by entering: bpo_orders_in_branch_audit = on
You can choose to print or email branch audits to different destinations by suffixing bpo_orders_in_audit_prn and/or bpo_order_in_audit_to with _nnnn (where nnnn is the 4-digit branch suffix).
Note: The above procedure must be performed by Quantum VS Professional Services.
NEXT: Reviewing Purchase Order Progress And Supplier Delivery/Shipping Targets