TICKETIMP - Tickets/Quotes Import
What is
This process facilitates bulk TICKET entry for new business to be subsequently confirmed by the TICKETS process.
Note: There is an alternative ITAS process that utilises IGI and ITASWebServices to import this data; the end product is exactly the same as described below.
TICKETIMP imports records from a file located on ITAS server directly into ITAS. The format of the file is strictly controlled and any deviation from the format will result is rejection. TICKETIMP currently allows import of new trades (phys01 records), commissions (phys02), costs (phys03), options (phys04) and traffic data.
The file to import is a Comma Delimited file with an extension of ‘.csv’ (can be created from an Excel worksheet).
S01 is used to define (View/Data Import/Export Fields / phys01 – [xx_imp01]) which TRADE fields can be included in the import file (Hivedome maintained list e.g. afi, warehouse, destination, port of origin) The list is maintained automatically with the mandatory fields needed by ITAS e.g. CounterParty, contract type, contract terms and also with the list of fields nominated in S01/View/Physicals/page 5/Mandatories. The System Manager ticks which fields can be included on the import file. The import file only needs the mandatory fields whereby any of the other selected fields can be included e.g. a warehouse/stock import would need Warehouse, Rotation ref etc whereas new business probably would not need those particular fields.
Cell A1 must contain the file type, i.e. phys01, phys02, phys03, phys04 or traffic.
Row 2 must contain the ITAS database names, as selected in S01 e.g. phys01_contype, phys01_counterparty
Row 3 onwards contains the data records to import. The last row is determined by the completely empty line.
To execute the import, select the drive, folder and filename of the file to import and click the Import button. The drive and folder are defaulted from a prior use of this application.
In the case of importing phys01 records, a batch no. is created which is unique to that import process. The batch no is stored in phys01_batchref on successfully imported records. TICKETIMP will reject the entire file from processing, if there are configuration/format errors i.e. missing or extra fields to the master list. A log of failure rows will be produced for the user to re-enter. The failed entries will be due to the subsequent detailed TRADE validation e.g. invalid codes, inconsistent cross-referenced data.
There is a procedure to un-do an import by selecting from the Previous Imports grid and right-click for Delete import. The process ensures that the data is still virgin before allowing removal and a report is produced of the actions taken (failure and success). The CMP/Privilege '' is needed for the user to apply the Un-Do process.
When an import has been completed satisfactorily, the contract references can not be re-used. Any contracts which have been amended subsequent to import may not be reversed. The Un-import procedure creates an OPRINT report named YYYYMMDD_DeleteBatchnnnnnn.IRP and is also records in the log.
An OPRINT report is created listing each line of the import file, including an indicator if the record was rejected. The name of the OPRINT report for
phys01 records is: YYYYMMDD_Ticketimpnnnnnn.IRP
phys02 records is: YYYYMMDD_ComsnImport_HHMMSS.IRP
phys03 records is: YYYYMMDD_CostsImport_HHMMSS.IRP
phys04 records is: YYYYMMDD_CmyOptionsImport_HHMMSS.IRP
phys05 records is: YYYYMMDD_FixationsImport_HHMMSS.IRP
traffic records is: YYYYMMDD_TrafficImport_HHMMSS.IRP
Where:
YYYYMMDD is the run date
nnnnnn is the batch number
HHMMSS is the run time
After import the file is moved to the \Old folder and renamed. If the folder holding the file does not have a \Old sub folder, it is created. If the file already resides in a \Old folder it is not moved or renamed. The name of the new file is the same as the OPRINT files except with an extension of ‘.csv’
Rows with the same External ID will be created as tranches of a single contract
Data Field Names (Row 2 of the file)
Phys01
The fields which are permitted on the phys01 import are nominated in S01…View…Data Import Fields. The sequence of the columns is not important. The data fields indicated as mandatory in S01 must be present in the file and with content. Non mandatory items need not have a column in the file and if present may have a blank content. For example phys01_contype must be present and phys01_portorig does not need content. Other items are company specific, for example if the company has pack code nominated as mandatory in S01, then it must be present in the file. The phys01_contype determines ‘P’ (Purchase), ‘S’ (Sale). TICKETIMP can apply its' own range of Quote References (ctrl30_applyquotebase =Y) else will use standard P and S ranges.
Analysis Codes
Valid analysis codes can be entered on row 2 . If the data row has a value a specification record ( phys25 ) will be created for the quote .
Phys02
The phys02 column list is fixed and must have the following content:
Column A - phys02_contract or phys01_takeonref (MANDATORY)
Column B – phys02_agent (MANDATORY)
Column C - phys02_ccy (MANDATORY)
Column D- phys02_ratetype (MANDATORY)
Column E - phys02_pricecode
Column F - phys02_payrec (MANDATORY)
Column G - phys02_comrate (MANDATORY)
Column H - phys02_agref
Column I - phys02_pl
Phys03
The phys03 column list is fixed and must have the following content:
Column A - phys03_contract or phys01_takeonref (MANDATORY)
Column B – phys03_code (MANDATORY)
Column C – phys03_supplier
Column D – phys03_ccy (MANDATORY)
Column E – phys03_ratetype (MANDATORY)
Column F – phys03_pricecode
Column G – phys03_payrec (MANDATORY)
Column H – phys03_rate (MANDATORY)
Column I – phys03_surcharge
Column J – phys03_pl
Column K – phys03_pole
Column L – phys03_roetype
Column M – phys03_roe
Column N – phys03_narrative
Column O – phys03_inccomm
Column P – phys03_notactualised
Column Q – phys01_memo2
Column R – phys03_containers (Only valid on ocean freight costs when pricecode is CONT – Per Container)
Column S – phys01_matrix
Phys04 (Commodity Options)
The phys04 column list is fixed and must have the following content:
Column A - phys04_contract or phys01_takeonref (MANDATORY)
Column B - phys04_commod (MANDATORY)
Column C - phys04_origin
Column D - phys04_grade
Column E - phys04_cmycode4
Column F - phys04_cmycode5
Column G - phys04_cmyctry
Column H - phys04_ccy
Column I - phys04_pricecode
Column J - phys04_conprice
Column K - phys04_quality
Column L – phys04_packcode
Column M - phys04_conterms
Column N - phys04_contermsport
Column O - phys04_arbit
Column P - phys04_cropyear
Column Q - phys04_portorig
Column R - phys04_shipweights
Column S - phys04_fclref
Column T - phys04_narrative
Traffic
Column A must contain either phys01_contract or phys01_takeonref.
Subsequent columns may be any of the permitted traffic fields. The permitted traffic fields are:
General:
phys01_afi,phys01_afidate,phys01_claim,phys01_trafficinit
Shipping:
phys01_afi,phys01_afidate,phys01_claim,phys01_vessel,
phys01_transvessel,phys01_blref,phys01_shippingline,phys01_consignee,phys01_notify,
phys01_portorig,phys01_portvia,phys01_portdest,phys01_portoption,phys01_container,
phys01_shipbuilt,phys01_bookingclose,phys01_journey,phys01_loadeta,
phys01_loaddate,phys01_eta,phys01_arrivaldate,
phys01_shipflag,phys01_bldate,phys01_planshipdate,phys01_bookingno,phys01_loadarrivaldate,
phys01_departuredate,phys01_etd,phys01_deliverydate,phys01_destdecdate,
phys01_marks,phys01_fcl,phys01_fclref, phys01_frtforwarder, phys01_frtforwarderref
Warehouse:
phys01_warehouse,phys01_warehouseloc,phys01_shedid,
phys01_warrant,phys01_lotref,phys01_intakecode,phys01_warehousedate,
phys01_bank,phys01_rentus,phys01_rentstart,phys01_rentend,phys01_bankref,
phys01_warehousetype,phys01_rhdinvdate,phys01_dom,phys01_factoryno,
phys01_releasedate,phys01_releasecode
Data Rows
In general the data must be maintained in the file as it would be entered/visible in the TRADE screen.
The exceptions are:
Dates – Entered numerically so 12th June 2007 would be in the file as 20070612, except shipping period which is entered as a date, period or range (e.g. JUN07, 01/09/07, 01se[07, -1/07/07-30/09/07)
Periods - Entered numerically so June 2007 would be in the file as 20070632
Phys01_fix (pricing Method: N for priced and F for F&O pricing
Phys01_contype (Contract Type): P for purchases and S for sales
Phys01_fixtype (Type of Fix): AA for against actuals, BC for buyers calls etc.
Phys01_posntype (Position qualifier): 'Start', 'End'
Phys02_ratetype (Rate type): R for Rate, A for Amount, % for Percent etc.
Phys02_payrec (Pay/rec): P for Pay, R for Rec