252883
252883 SW-7GZJKZ Add Additional Aramco Document Number 49
Copyright OBS Logistics © 2009
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
FUNCTIONAL OVERVIEW
Client Requirement
Add additional Aramco Document Number 49 in the Documents and label.
Add the additional data field 1 into the collection / delivery and labels for the Saudi Aramco contract as detailed within the specification document attached. Some details from Spec attached to RIO:
• Amendment to the collection note to show the 49 number after the remarks tick box for each Booking Reference.
• Amendment to the delivery note to show the 49 number within the Requester / Tel / Account box for each Booking Reference.
• Modification of label to show 49 number below company logo area.
A workshop was held on the 28th July 2008 at which it was decided on the location of the 49 number on each of the documents. The data will be obtained from the Additional data 1 field already provided by Aramco within the interface to MTS.
Solution
Update Reports COLLECTION_NOTE.rep and DELIVERY_NOTE_BAR.rep to include field ‘Additional Data 1’ in the position requested, which has been shown on the jpeg’s within the Spec attached to the RIO request. It will be displayed in format ‘Doc No.’ followed by the 49xxxxxxxx number. (e.g. Doc No. 4907931933)
The document number will only be displayed on the Collection Note and Delivery Note if it begins with 49, as many booking refs have the text ‘Additional field 1’ stored in column BKG_ADD_DATA_1. When writing the document number to the label outbound file, it too will only be written to the file if it begins with 49; otherwise it will be left blank.
The included P.O. label is not produced directly from MTS, this appears to be an externally produced label driven by an outbound ‘Label’ dataflow. For the ‘Doc No’ to be included on the P.O Label, procedure process_label_outbound within package INT_MSG will have to be updated to pick up this field and write it to the outbound file.
File Label naming format will remain unchanged – currently this is Label || trip_id || DDMMYYYYHH24MISS.csv e.g. ‘LabelMAN-0033892630072008111340.csv’
The current file produced has 32 columns of data, but none of these reference the newly requested ‘Additional Data 1’ field. The new field will be picked up from column BKG_ADD_DATA_1 in table BKG_PROD_SUM_REC, and placed at the end of the file, in position 33.
The file generation and delivery mechanisms will remain unchanged - currently placed in folder /webint/SARLV/interface/HHT/OUTBOUND for collection.
ESI may have to be informed of the additional column so that they can map the file correctly. Outside of the scope of this change there will be a requirement for creating and positioning the new data on the externally produced P.O Label.
Scope
This change will be applied to system version 10.4.7 on SARTST and once approved SARPRD.
FUNCTIONAL DESCRIPTION
Sample Layout
The Aramco 49 Number relates to BKG_PROD_SUM_REC.BKG_ADD_DATA_1 in the database. Within MTS, this relates to field ‘BKG Additional Data 1’ on tab ‘SAP Detail’ within the ORDERS form. As an example in SARPRD, Booking Ref 20020694030001100 has Aramco No of ‘4908071374’, as you can see in MTS below:
If you run the Collection Note from the Reports screen with the following parameters, Page 11 should bring back data corresponding to the order on which the above trip is scheduled:
Below is an example of what the new COLLECTION_NOTE.rep will look like with the additional Aramco 49 number added. The example includes the Booking Ref from the example above. Note that the test of the new fields will match the current test of all the other fields:
Below is an example of the DELIVERY_NOTE_BAR.rep run for the same parameters, and detailing the same trip with booking refs. It has been amended to include the Aramco No:
Code Amendments
Within each report, there will be a function added that will be used to retrieve the Aramco number for the corresponding Booking Ref. The procedure will also check if the Aramco No begins with ‘49’ otherwise it will not be displayed. This is because for many booking refs, field BKG_ADD_DATA_1 has the text ‘Additional Field 1’. The code for this function is as follows:
function CF_Aramco_NumberFormula return VARCHAR2 is
CURSOR aramco_no IS select bkg_add_data_1 from bkg_prod_sum_rec where booking_ref = :booking_ref;
v_aramco_no NUMBER;
begin OPEN aramco_no; FETCH aramco_no into v_aramco_no; CLOSE aramco_no; IF v_aramco_no IS NOT NULL AND v_aramco_no LIKE '49%' THEN RETURN v_aramco_no; ELSE RETURN NULL; END IF; end;
The P.O. label is not produced directly from MTS. This appears to be an externally produced label driven by an outbound ‘Label’ dataflow. For the ‘Doc No’ to be included on the P.O Label, procedure process_label_outbound within package INT_MSG will have to be updated to pick up this field and write it to the outbound file. The following pieces of code will need to be added to the procedure in order to pick up this data. Again, it will only be displayed if it begins with ‘49’.
Used to pick up the Aramco 49 Number for the passed in Booking Ref as long as it starts with 49:
CURSOR c_aramco_no (cp_booking_ref sch_product_summary.booking_ref%TYPE) IS SELECT bkg_add_data_1 FROM bkg_prod_sum_rec WHERE booking_ref = cp_booking_ref AND bkg_add_data_1 LIKE ‘49%’;;
Used to hold the Aramco number retrieved using the cursor above:
OPEN c_aramco_no (v_sch_ord.booking_ref); FETCH c_aramco_no INTO v_aramco_no; CLOSE c_aramco_no;
There are currently 32 columns of data in the outbound ‘Label’ dataflow. The Aramco number will be added to the end column of this file, in position 33. Below is a piece of code used to write all the retrieved data to each line. The last line highlighted in red is used to write the Aramco No:
v_string :=
v_sch_ord.booking_ref || ',' ||
v_to_loc || ',' ||
v_from_loc || ',' ||
v_route_code || ',' ||
SUBSTR(v_sch_ord.booking_ref, 1, 10) || ',' ||
v_bkg_detail.original_qty || ',' ||
v_asn_quantity || ',' ||
v_bkg_prod_sum_rec.comments || ',' ||
v_bkg_prod_sum_rec.req_room || ',' ||
v_bkg_prod_sum_rec.old_mat_no || ',' ||
NVL(v_sps_product_desc, v_product_item_desc) || ',' ||
SUBSTR(v_sch_ord.booking_ref, 11, 5) || ',' ||
v_sch_ord.warehouse_code || ',' ||
v_bkg_prod_sum_rec.req_build || ',' ||
v_sch_ord.storage_code || ',' ||
v_bkg_prod_sum_rec.temp_cond || ',' ||
v_bkg_prod_sum_rec.req_name || ',' ||
v_bkg_prod_sum_rec.req_phone || ',' ||
v_bkg_prod_sum_rec.req_acc_no || ',' ||
v_sch_ord.del_priority || ',' ||
v_sch_ord.handling_code1 || ',' ||
v_sch_ord.handling_code2 || ',' ||
v_sch_ord.handling_code3 || ',' ||
v_bkg_prod_sum_rec.chbno || ',' ||
v_sch_ord.insp_req || ',' ||
v_sch_ord.test_req || ',' ||
v_bkg_prod_sum_rec.min_rem_shelf || ',' ||
v_sch_ord.sap_product_item || ',' ||
v_du_type || ',' ||
'0' || ',' ||
v_bkg_prod_sum_rec.shelf_life_ind || ',' ||
v_bkg_prod_sum_rec.lab_test || ',' ||
v_aramco_no.bkg_add_data_1;
As specified in the Estimate, ESI will have to be informed of the additional column so that they can map the file correctly. Outside of the scope of this change there will be a requirement for creating and positioning the new data on the externally produced P.O Label. The team responsible for this will also have to be informed of the new data, and where to position it.
References
| 49number Spec_V0.1.doc | |||
| EST-252883 SW-7GZJKZ Add Additional Aramco Document Number 49 v1.doc | 1 | 31/07/2008 |
Document History
| Initial version | ||||
| Reviewed and Issued |
Authorised By
| Dave Meir | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
