What is

This process facilitates bulk TRADE entry for new quotes either as a take-on procedure for new companies or for regular imports.

QUOTEIMP imports TRADE 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.  QUOTEIMP currently allows import of new quotes (phys01 records), commissions (phys02), costs (phys03), options (phys04), price fixations (phys05), Columbia trade registrations (phys01af records) and traffic data. The file to import is a text TAB delimited file with an extension of  ‘.Txt’, 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 .txt 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, phys05, phys14, phys01af or traffic.

The 2nd row must contain the ITAS database names, as selected in S01 e.g. phys01_contype phys01_takeonref

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.  TRADEIMP will reject the file from processing, if there are 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).

An OPRINT report is also 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_Batchnnnnnn.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

phys14 records is:  YYYYMMDD_SpecificationsImport_HHMMSS.IRP

phys01af records is:  YYYYMMDD_TradeRegistrations_HHMMSS.IRP

traffic records is:  YYYYMMDD_TrafficImport_HHMMSS.IRP

Where:

YYYYMMDD  is the date

nnnnnn          is the batch number and

HHMMSS      is the 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 the same as the OPRINT files except with an extension of ‘.Txt’

There is a process to remove (undo) the IMPORT of phys01 records using the batch reference. When an import has been completed satisfactorily, the contract references can not be re-used. Action right click…Un-Import. Any contracts which have been amended may not be reversed. The Un-import procedure creates an OPRINT report named  YYYYMMDD_DeleteBatchnnnnnn.IRP and is also record in the log.

Data Field Names (Row 2 of the file)

Phys01

The fields which are permitted on the phys01 import must be those 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. 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)  or‘S’ (Sale). QUOTEIMP can apply its own range of quote references (ctrl30_applyquotebase = Y) otherwise it will use the standard P and S ranges.

Phys02

The phys02 column list is pre-defined and can 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 pre-defined and can 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 pre-defined and can 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

Phys05 / Phys52

The phys05 column list is pre-defined and can have the following content:

Column A - phys05_contract or phys01_takeonref  (MANDATORY)

Column B - phys05_date, format is YYYYMMDD e.g. 20090513 (MANDATORY)

Column C - phys05_lots, numeric  (MANDATORY)

Column D -  phys05_commod, TCCM code (MANDATORY)

Column E - phys05_prompt, MMMYY or YYYYMMDD format e.g. MAY09 or 20090532

Column F - phys05_price, numeric max 6 decimal places (MANDATORY)

Column G - phys05_code, TERMCODES/Trade code (MANDATORY)

Column H – phys05_memo

Column I – blank (or phys01_memo2 if ctrl30_pfixstyle = C )

Column J – phys05_reassignid

Column K – phys05_summaryid

Column L – phys05_invref

Column M – phys05_use

Column N - phys05_flatprice

Column O - phys05_mktprice

Phys14 ( Quality Specifications)

The phys14 column list is pre-defined and must have the following content:

Column A - phys14_contract or phys01_takeonref  (MANDATORY)

Column B -phys14_quality (MANDATORY)

Column C -,phys14_targetpc

Column D - phys14_maxpc

Column E - phys14_option

Column F - phys14_qualifier

Column G - phys14_loadportresult

Column H - phys14_dischargeresult

Column I - phys14_avgminpc

Column J - phys14_avgmaxpc

Column K - phys14_ourminpc

Column L - phys14_ourmaxpc

Column M - phys14_ouravgminpc

Column N - phys14_ouravgmaxpc

Column O - phys14_ouroption

Column P - phys14_minpcreq

Column Q - phys14_maxpcall

Column R - phys14_ratename

Column S - phys14_rate

Column T - phys14_mixpc

Column U - phys14_groupid

Column V - phys14_rate2

Phys01af

The phys01af column list is pre-defined and must have the following content:

Column A - phys01_contract or phys01_takeonref   (MANDATORY)

Column B - phys01_finalbuyer

Column C - phys01_finalbuyerctry

Column D - phys01_registered

Column E - phys01_regshipmonth

Column F - phys01_regdate

Column G - phys01_regplace

Column H - phys01_regcode

Column I - phys01_fncalloccode

Column J - phys01_fncrevisioncode

Column K - phys01_serialdex

Column L - phys01_liqdate

Column M - phys01_reintegro

Column N - phys01_regtax

Column O - phys01_reintegroccy

Column P - phys01_reintegropcode

Column Q - phys01_regtaxccy

Column R - phys01_regtaxpcode

Phys28 (Cotton Pricing Master)

Column A	Phys28_contract	Must be a valid contract that is head of a price fixing family - Contract number = phys01_pricingsuffix
Column B	phys28_classtable	Must be a valid ccc41 class table
Column C	Phys28_mictable	Must be a valid ccc42 mic table
Column D	Phys28_strengthtable	Must be a valid ccc43 strength table
Column E	Phys28_unitable	Must be a valid ccc44 uniformity table
Column F	Phys28_extraneoustable	Must be a valid ccc45 ext matter table
Column G	Phys28_leaftable	Must be a valid ccc46 leaf table
Column H	Phys28_basetable	Date – Must be valid pd_base table
Column I	Phys28_invcutoffdate	Must be valid date
Column J	Phys28_invbcrate	Numeric
Column K	phys28_oldpenalty	Numeric
Column L	phys28_olddays	Integer
Column M	Phys28_olddaily	Y/N
Column N	phys28_accweight1,	Integer
Column O	phys28_accweight2	Integer
Column P	Phys28_cottonboard	Y/N
Column Q	phys28_firsthandler	Y/N
Column R	phys28_accoldcrop	Y/N
Column S	phys28_spottables	Y/N – If Y base date must be present
Column T	phys28_basedate	Must be valid date – Only valid if phys28_spottables = Y
Column U	phys28_minavebalewt	Integer
Column V	Phys28_classbase
Column W	Phys28_warehousetable	Must be a valid ccc48 warehouse table
Column X	Phys28_classpremtable	Must be a valid ccc41 class table

Phys4x tables – Pricing tables for the contract will be generated from the CCC table matching the imported name from columns B to H.

Phys30

The phys30 column list is pre-defined and must have the following content:

Column A - phys30_contract, Valid Open Purchase contract (DATA MANDATORY)

Column B - phys30_loan, Numeric, max 8 characters (DATA MANDATORY)

Column C - phys30_producer, Max 30 characters

Column D - phys30_agent, Valid Open Client Account code marked as Agent. The code entered will be compared against an existing ITAS client code beginning with ‘F’ and ending with ‘.P’. For example a valid entry for this column would be ‘01001’ where there exists an ITAS client code ‘F01001.P’

Column E - phys30_disburse, Format is YYYYMMDD e.g. 20100215

Column F - phys30_maturity, Format is YYYYMMDD e.g. 20100215

Column G - phys30_ascs, Format is YYYYMMDD e.g. 200100215

Column H - phys30_over, Format is ‘Y’ to indicate value exceeds $500,000

Column I - phys30_docs, Max 10 characters

Phys31 – Contract Options

The phys31 column list is pre-defined and must have the following content:

Column A - phys31_contract

Column B - phys31_type - DE = Destination, CT = Contract Terms, PT = Pay Terms , SW = Ship weights , OR = Origin, CY = Crop Year, GR = growth, TP = To Purchase , ME = Make Equity

Column C - phys31_code – the option code e.g. The port code , the contract terms code

Column D - phys31_option – B or S

Column E - phys31_takenup – Y or blank

Column F - phys31_termsport – Port for contract terms options

Column G - phys31_pricecode

Column H - phys31_premdisc

Phys40 – Premium Ranges

The phys40 column list is pre-defined and must have the following content:

Column A - phys40_contract, Valid Open Purchase contract (DATA MANDATORY)

Column B - phys40_type, C = Class , S = Strength, M = Mic, U = uniformity (DATA MANDATORY)

Column C - phys40_micfrom,

Column D - phys40_micto,

Column E - phys40_strengthfrom

Column F - phys40_strengthto

Column G - phys40_uniformityfrom

Column H - phys40_uniformityto

Column I - phys40_staplefrom

Column J - phy40_stapleto

Column K - phys40_gradelist

Column L - phys40_leaflist

Column M - phys40_reduced Y or Blank

Column N - phys40_belowgrade

Column O - phys40_staple100from

Column P - phys40_staple100to

Phys75 – Payment Splits

The phys75 column list is pre-defined and must have the following content:

Column A – phys75_contract

Column B – phys75_account – Client account code

Column C – phys75_percentage

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 date must be present in the file as it would be visible in the TRADE screen. The exceptions are:

phys01_fixtype (Type of Fix): AA for against actuals, BC for buyers calls etc.

phys02_payrec (Pay/rec): P for Pay, R for Rec

CCC41 – Cotton Class Pricing Table

Column A ccc41_name

Column B ccc41_up U = Upland , P= Pima

Column C ccc41_staple Integer 2 characters

Column D ccc41_grade Grade Code e.g. 11, 12

Column E ccc41_leaf Leaf code ( 1 to 7)

Column F ccc41_cropyear

Column G ccc41_pd Numeric – Units (CLB / PTS) depend on S01 setting

CCC42 – Cotton Micronaire Pricing Table

Column A ccc42_name

Column B ccc42_up U = Upland , P= Pima

Column C ccc42_micmin Numeric , 1 decimal place

Column D ccc42_micmax Numeric , 1 decimal place

Column E ccc42_staplemin Integer

Column F ccc42_staplemax Integer

Column G ccc42_cropyear

Column H ccc42_pd Numeric – Units (CLB / PTS) depend on S01 setting

CCC43 – Cotton Strength Pricing Table

Column A ccc43_name

Column B ccc43_up U = Upland , P= Pima

Column C ccc43_strengthmin Numeric , 1 decimal place

Column D ccc43_strengthmax Numeric , 1 decimal place

Column E ccc43_cropyear

Column F ccc43_pd Numeric – Units (CLB / PTS) depend on S01 setting

CCC44 – Cotton Uniformity Pricing Table

Column A ccc44_name

Column B ccc44_up U = Upland , P= Pima

Column C ccc44_uniformitymin Numeric , 1 - 100

Column D ccc44_uniformitymax Numeric , 1 – 100

Column E ccc44_cropyear

Column F ccc44_pd Numeric – Units (CLB / PTS) depend on S01 setting

CCC45 – Cotton Extraneous Pricing Table

Column A ccc45_name

Column B ccc45_up U = Upland , P= Pima

Column C ccc45_location T = Texas type , F = Other

Column D ccc45_matter Numeric Code

Column E ccc45_cropyear

Column F ccc45_pd Numeric – Units (CLB / PTS) depend on S01 setting

CCC46 – Cotton Leaf pricing table

Column A ccc46_name

Column B ccc46_up U = Upland , P= Pima

Column C ccc46_leaf Leaf code ( 1 – 7 )

Column D ccc46_grade Numeric Grade Code

Column E ccc46_cropyear

Column F ccc46_pd Numeric – Units (CLB / PTS) depend on S01 setting

CCC48 – Cotton Warehouse pricing table

Column A ccc48_name

Column B ccc48_warehouse Warehouse account

Column C ccc48_cropyear

Column D ccc48_pd Numeric – Units (CLB / PTS) depend on S01 setting

NOTE: QUOTEIMP is not a full functioning process available to all ITAS companies^.