Oracle Retail Trade Management

Simplified RTM Configuration

Simplified RTM is a simplified version of the Oracle Retail product suite targeted at mid-tier retailers. The Simplified Oracle Retail Merchandising Operations Management applications support basic retail processes needed by a mid-tier retailer. Advanced features are turned-off through system parameters, with the goal to reduce implementation complexity and enabling faster implementation and lower total cost of ownership.

The Simplified RTM Indicator is set in the system_options table during the installation of RMS. If the system_option parameter is enabled, then the following RTM functionality is not available in the application:

Setting up RTM specific Freight Type, Freight Size and Standard Carrier Alpha Codes (SCAC)
Letter of Credit functionality
Transportation functionality
Customs Entry functionality
Obligation Maintenance
Actual Landed Costs

If both the Simplified RTM indicator and the Import indicator are enabled, then some import related functionality is available in RMS. With this setup, the retailer has the option to setup HTS data for classification of merchandise and for the calculation of duties, fee and taxes.

The retailer can also choose Letter of Credit as a payment option at the Purchase Order header level, but all other related LC functionality is not available. It is assumed that the retailer is using some other external system for LC processing.

If the import indicator is not enabled then no RTM functionality is available in the application. See the RMS Installation Guide for additional information on setting the value of the system_options table.

Simplified RTM Batch Program Notes

When Simplified RTM is enabled (RTM Simplified Indicator is enabled) then the following batch programs need to be turned off from the integrated batch schedule.

lcadnld
lcupld
lcup798
lcmdnld
cednld
tranupld

The following Perl scripts should also be turned off from the integrated batch schedule.

lcmt700
lcmt707
lcmt730
lcmt798

When both the RTM simplified indicator and import indicator is enabled then the following batch program needs to be turned on in the integrated batch schedule.

htsupld

Batch Design Summary

The following batch designs are included in this functional area:

cednld.pc (Download of Customs Entry Transactions to Brokers)
htsupld.pc (Harmonized Tariff Schedule Upload)
tranupld.pc (Transportation Upload)
lcadnld.pc (Letter of Credit Application Download)
lcmt700 Perl (SWIFT File Conversion – Letter of Credit Application)
lcupld.pc (Letter of Credit Confirmation Upload)
lcmt730 (SWIFT File Conversion – Letter of Credit Confirmation)
lcmdnld.pc (Letter of Credit Amendment Download)
lcmt707 Perl (SWIFT File Conversion – Letter of Credit Amendment)
lcup798.pc (Letter of Credit Drawdowns and Charges)
lcmt798 (SWIFT File Conversion – Letter of Credit Charges and Drawdowns)

cednld (Download of Customs Entry Transactions to Brokers)

Module Name	cednld.pc
Description	Download of Customs Entry Transactions to Brokers
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS53
Runtime Parameters	NA

Design Overview

This program is used to download custom entry information from the RMS database to brokers. Each night, this program reads all custom entry (CE) transactions that are in "S" Sent status for a broker ID. These transactions are written to a flat file and the status is changed to "D"ownloaded. One flat file is written per broker.

Scheduling Constraints

Table 19-1 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This batch is not scheduled to run when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Single Threaded, however multiple processes can be run at the same time, each downloading customer entry information for a different broker

Restart/Recovery

The Logical Unit of Work for the program is a single row from the CE_HEAD table. Restart/Recovery will be used for init and commit.

Table based restart/recovery must be used. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The recommended commit counter setting is 1000 records (subject to change based on implementation).

Key Tables Affected

Table 19-2 Key Tables Affected

Table	Select	Insert	Update	Delete
CE_HEAD	Yes	No	Yes	No
CE_SHIPMENT	Yes	No	No	No
CE_ORD_ITEM	Yes	No	No	No
ORDHEAD	Yes	No	No	No
SUP_IMPORT_ATTR	Yes	No	No	No
TRANSPORTATION	Yes	No	No	No
CE_LIC_VISA	Yes	No	No	No
CE_CHARGES	Yes	No	No	No
MISSING_DOC	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000050

File Layout

Table 19-3 File Layout

Record Name	Field Name	Field Type	Default Value	Description
File Header	File Type Descriptor	Char(5)	FHEAD	Identifies file record type
	File Line Identifier	Number(10)	Nine leading zeroes: 0000000001	ID of current line being processed by input file
	File Type Definition	Char(4)	CEDN	Identifies file as &rsquor;Customs Entry download'
	File Create Date	Date	Create date	Vdate in YYYYMMDDHH24MISS format
THEAD	File Type Descriptor	Char(5)	THEAD	Identifies file record type
THEAD	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file
	CE ID	Number(10)	ce_head.ce_id	NA
	Entry No	Char (15)	ce_head.entry_no	NA
	Entry Date	Char(14)	ce_head.entry_date	YYYYMMDDHH24MISS format
	Entry Status	Char(6)	ce_head.entry_status	NA
	Entry Type	Char(6)	ce_head.entry_type	NA
	Entry Port	Char(5)	ce_head.entry_port	NA
	Summary Date	Char(14)	ce_head.summary date	YYYYMMDDHH24MISS format
	Broker ID	Char(10)	ce_head.broker_id	NA
	Broker Ref. ID	Char(18)	ce_head.broker_ref_id	NA
	File Number	Char(18)	ce_head.file_no	NA
	Importer ID	Char(10)	ce_head.importer_id	NA
	Import Country	Char(3)	ce_head.import_country_id	NA
	Currency Code	Char(3)	ce_head.currency_code	NA
	Exchange Rate	Number(20,10)	ce_head.exchange_rate*10000000000 (with 10 implied decimal places)	NA
	Bond Number	Char(18)	ce_head.bond_no	NA
	Bond Type	Char(6)	ce_head.bond_type	NA
	Surety Code	Char(6)	ce_head.surety_code	NA
	Consignee ID	Char(10)	ce_head.consignee_id	NA
	Live Indicator	Char(1)	ce_head.live_ind	NA
	Batch Number	Char(20)	ce_head.batch_no	NA
	Entry Team	Char(3)	ce_head.entry_team	NA
	Liquidation Amount	Number(20,4)	ce_head.liquidation_amt*10000 (4 implied decimal places)	NA
	Liquidation Date	Date	ce_head.liquidation_date	YYYYMMDDHH24MISS format
	Reliquidation Amount	Number(20,4)	ce_head.reliquidation_amt*10000 (4 implied decimal places)	NA
	Reliquidation Date	Date	ce_head.reliquidation_date	YYYYMMDDHH24MISS format
	Merchandise Loc	Char(40)	ce_head.merchandise_loc	NA
	Location Code	Char(4)	ce_head.location_code	NA
TSHIP	File Type Descriptor	Char(5)	TSHIP	Identifies file record type
	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file
	Vessel ID	Char(20)	ce_shipment.vessel_id	NA
	Voyage Flt ID	Char(10)	ce_shipment.voyage_flt_id	NA
	Estimated Departure Date	Date	ce_shipment.estimated_depart_date	YYYYMMDDHH24MISS format
	Vessel SCAC Code	Char(6)	ce_shipment.vessel_scac_code	NA
	Lading Port	Char(5)	ce_shipment.lading_port	NA
	Discharge Port	Char(5)	ce_shipment.discharge_port	NA
	Tran Mode ID	Char(6)	ce_shipment.tran_mode_id	NA
	Export Date	Date	ce_shipment.export_date	YYYYMMDDHH24MISS
	Import Date	Date	ce_shipment.import_date	YYYYMMDDHH24MISS
	Arrival Date	Date	ce_shipment.arrival_date	YYYYMMDDHH24MISS
	Export Country	Char(3)	ce_shipment.export_country_id	NA
	Shipment Number	Number(10)	ce_shipment.shipment_no	NA
TORDI	File Type Descriptor	Char(5)	TORDI	Identifies file record type
TORDI	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file
	Order Number	Number(8)	ce_ord_item.order_no	NA
	Item	Char (25)	ce_ord_item.item	NA
	BL AWB ID	Char(30)	ce_ord_item.bl_awb_id	&rsquor;MULTI' – means multiple airway bills (otherwise a single airway bill will be retrieved)
	Invoice ID	Char(30)	ce_ord_item.invoice_id	NA
	Invoice Date	Date	ce_ord_item.invoice_date	YYYYMMDDHH24MISS format
	Invoice Amount	Number(20,4)	ce_ord_item.invoice_amt*10000 (4 implied decimal places)	NA
	Currency Code	Char(3)	ce_ord_item.currency_code	NA
	Exchange Rate	Number(20,10)	ce_ord_item.exchange_rate*10000000000 (10 implied decimal places)	NA
	Manifest Item Quantity	Number(12,4)	ce_ord_item.manifest_item_qty*10000 (4 implied decimal places)	NA
	Manifest Item Quantity UOM	Char(4)	ce_ord_item.manifest_item_qty_uom	NA
	Carton Quantity	Number (12,4)	ce_ord_item.carton_qty*10000 (4 implied decimal places)	NA
	Carton Quantity UOM	Char(4)	ce_ord_item.carton_qty_uom	NA
	Gross Weight	Number(12,4)	ce_ord_item.gross_wt*10000 (4 implied decimal places)	NA
	Gross Weight UOM	Char(4)	ce_ord_item.gross_wt_uom	NA
	Net Weight	Number(12,4)	ce_ord_item.net_wt*10000 (4 implied decimal places)	NA
	Net Weight UOM	Char(4)	ce_ord_item.net_wt_uom	NA
	Cubic	Number(12,4)	ce_ord_item.cubic*10000 (4 implied decimal places)	NA
	Cubic UOM	Char(4)	ce_ord_item.cubic_uom	NA
	Cleared Quantity	Number(12,4)	ce_ord_item.cleared_qty*10000 (4 implied decimal places)	NA
	Cleared Quantity UOM	Char(4)	ce_ord_item.cleared_qty_uom	NA
	In Transit Number	Char(15)	ce_ord_item.in_transit_no	NA
	In Transit Date	Date	ce_ord_item.in_transit_date	YYYYMMDDHH24MISS format
	Rush Indicator	Char(1)	ce_ord_item.rush_ind	NA
	Related Indicator	Char(1)	ce_ord_item.related_ind	NA
	Tariff Treatment	Char(10)	ce_ord_item.tariff_treatment	NA
	Ruling Number	Char(10)	ce_ord_item.ruling_no	NA
	Do Number	Char(10)	ce_ord_item.do_no	NA
	Do Date	Date	ce_ord_item.do_date	YYYYMMDDHH24MISS format
	Manufacture ID	Char(18)	sup_import_attr.mfg_id	NA
TBLAW	File Type Descriptor	Char(5)	TBLAW	Identifies file record type
	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file
	BL AWB ID	Char(30)	Transportation.bl_awb_id	NA
TCONT	File Type Descriptor	Char(5)	TCONT	Identifies file record type
	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file
	Container ID	Char(20)	Transportation.container_id	NA
	Container SCAC Code	Char(6)	Transportation.container_scac_code	NA
TLICV	File Type Descriptor	Char(5)	TLICV	Identifies file record type
	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file
	License/Visa Type	Char(6)	ce_lic_visa.license_visa_type	NA
	License/Visa ID	Char(30)	ce_lic_visa.license_visa_id	NA
	License/Visa Quantity	Number(12,4)	ce_lic_visa.license_visa_qty*10000 (4 implied decimal places)	NA
	License/Visa Quantity UOM	Char(4)	ce_lic_visa.license_visa_qty_uom	NA
	Quota Category	Char (6)	ce_lic_visa.quota_category	NA
	Net Weight	Number(12,4)	ce_lic_visa.net_weight*10000 (4 implied decimal places)	NA
	Net Weight UOM	Char(4)	ce_lic_visa.net_weight_uom	NA
	Holder ID	Char(18)	ce_lic_visa.holder_id	NA
TCHRG	File Type Descriptor	Char(5)	TCHRG	Identifies file record type
	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file
	Sequence Number	Number(6)	ce_charges.seq_no	NA
	Pack Item	Char(25)	ce_charges.pack_item	NA
	HTS	Char(10)	ce_charges.hts	NA
	Effect From Date	Date	ce_charges.effect_from	YYYYMMDDHH24MISS format
	Effect To Date	Char(14)	ce_charges.effect_to	YYYYMMDDHH24MISS format
	Component ID	Date	ce_charges.comp_id	NA
	Component Rate	Number(20,4)	ce_charges.comp_rate*10000 (4 implied decimal places)	NA
	Per Count UOM	Char(3)	ce_charges.per_count_uom	NA
	Component Value	Number(20,4)	ce_charges.comp_value * 10000 (4 implied decimal places)	NA
TMDOC	File Type Descriptor	Char(5)	TMDOC	Identifies file record type
	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file
	Doc_id	Number(6)	Missing_doc.doc_id	NA
	Received_date	Date	Missing_doc.received_date	YYYYMMDDHH24MISS format
FTAIL	File Type Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Identifier	Number(10)	Incremented internally	ID of current line being processed by input file.
	File Record Counter	Number(10)	Determined internally	Number of records/transactions processed in current file (only records between head & tail)

htsupld (Harmonized Tariff Schedule Upload)

Module Name	htsupld.pc
Description	Harmonized Tariff Schedule Upload
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS41
Runtime Parameters	NA

Design Overview

The harmonized tariff schedule module processes a file containing the most recent United States Customs tariff schedule to RMS tables. The module uploads both the initial entry of the schedule and all the updates, as they become available.

Scheduling Constraints

Table 19-4 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	When import_ind from SYSTEM_OPTIONS table is &rsquor;Y', then this batch program need to be turned on in integrated batch schedule.
Pre-Processing	Hts240_to_2400 (perl script to convert the original US government HTS file of 240-char lines to 2400-char lines) Ushts2rms (perl script to convert the HTS file of 2400- char lines to standard Oracle Retail file format) prepost.pc with HTSUPLD_PRE() function
Post-Processing	NA
Threading Scheme	The number of threads will be based on the number of input files

Restart/Recovery

Recommended commit counter is 2000. Input file names must end in a ”.1” for the restart mechanism to properly parse the file name. Because there is only 1 input file to be uploaded, only 1 thread is used.

A reject file is used to hold records that have failed processing. The user can fix the rejected records and process the reject file again.

Key Tables Affected

Table 19-5 Key Tables Affected

Table	Select	Insert	Update	Delete
HTS	Yes	Yes	Yes	Yes
HTS_TL	No	No	No	Yes
HTS_TARIFF_TREATMENT	Yes	Yes	Yes	Yes
ITEM_HTS	Yes	Yes	Yes	Yes
MOD_ORDER_ITEM_HTS	No	Yes	No	No
HTS_OGA	No	Yes	Yes	Yes
ORDSKU_HTS	Yes	Yes	Yes	Yes
HTS_TT_EXCLUSIONS	No	Yes	Yes	Yes
HTS_TAX	No	Yes	Yes	Yes
HTS_FEE	No	Yes	Yes	Yes
CE_CHARGES	Yes	Yes	Yes	Yes
HTS_CHAPTER	Yes	Yes	No	No
QUOTA_CATEGORY	Yes	Yes	No	No
ITEM_HTS_ASSESS	No	Yes	Yes	Yes
HTS_AD	No	No	Yes	No
HTS_CVD	No	No	Yes	No
HTS_REFERENCE	No	No	Yes	No
ORDHEAD	Yes	No	Yes	No
ITEM_EXP_DETAIL	No	No	Yes	No
ORDLOC_EXP	No	No	Yes	No
ORDSKU_HTS_ASSESS	No	No	Yes	Yes
ORDSKU_TEMP	Yes	No	No	Yes
ORDLOC_TEMP	No	No	No	Yes
ALLOC_CHRG_TEMP	No	No	No	Yes
ALLOC_DETAIL_TEMP	No	No	No	Yes
ALLOC_HEADER_TEMP	No	No	No	Yes
ORDLOC_EXP_TEMP	No	No	No	Yes
ORDSKU_HTS_ASSESS_TEMP	No	No	No	Yes
ORDSKU_HTS_TEMP	No	No	No	Yes
ORDLOC_DISCOUNT_TEMP	No	No	No	Yes
TIMELINE_TEMP	No	No	No	Yes
REQ_DOC_TEMP	No	No	No	Yes
WO_DETAIL_TEMP	No	No	No	Yes
WO_HEAD_TEMP	No	No	No	Yes
REPL_RESULTS_TEMP	No	No	No	Yes

Integration Contract

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000051

Input File Layout

Table 19-6 Input File Layout

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	Record Descriptor	Char(5)	FHEAD	Describes file line type
	Line number	Number(10)	0000000001	Sequential file line number
	Retek file ID	Char(5)	HTSUP	Describes file type
THEAD	Record Descriptor	Char(5)	THEAD	Describes file line type
	Line number	Number(10)	NA	Sequential file line number
	Transaction id	Number(14)	NA	Unique transaction id
	HTS Line	Char(358)	NA	V1 through V4 records from the customs HTS file concatenated together
TDETL	Record Descriptor	Char(5)	TDETL	Describes file line type
	Line number	Number(10)	NA	Sequential file line number
	Transaction id	Number(10)	NA	Unique transaction id
	Tax/fee line	Char(80)	NA	V5 through VC records from the customs HTS file, each on a separate TDETL line
TTAIL	Record Descriptor	Char(5)	TTAIL	Describes file line type
	Line number	Number(10)	NA	Sequential file line number
	Detail lines	Number(6)	NA	Number of lines between THEAD and TTAIL
FTAIL	Record Descriptor	Char(5)	FTAIL	Describes file line type
	Line number	Number(10)	NA	Sequential file line number
	Transaction Lines	Number(10)	NA	Number of lines between FHEAD and FTAIL

Original Input File

Note:

The input file contains lines of 2400 characters (that is, the newline character occurs only after every 2400 characters). Each 2400-character line consists of thirty 80-character records. Each 80-character record starts with &rsquor;V1' or &rsquor;V2' … or &rsquor;VD' or blank if the record is completely empty. For each tariff, records V1 and V2 are mandatory; records V3 through VD are optional, which means they can be all blank. Record V4 is not currently used in RMS/RTM. Records V5 through VC contain the tax/fee information for the tariff, and all have the same structure. The lower-case letters in the record name block are as a convenience to cross-reference with the US Customs file description.

Table 19-7 Input File Layout

Record Name	Field Name	Field Type	Default Value	Description
V1 a	Control identifier	Char(1)	V	Identifies start of record
b	Record type	Char(1)	1	Identifies record type
c	Tariff number	Number(10)	NA	A code located in the Harmonized Tariff Schedule of the United States Annotated (HTS) representing the tariff number. If this number is less than 10 positions, it is left justified
d	Transaction code	Char(1)	A,D,R	A code representing the type of transaction. Valid Transaction Codes are: A = Add D = Delete R = Replace
e	Beginn effective date	char(6)	NA	A numeric date in MMDDYY (month, day, year) format representing the record begin effective date. This date indicates when the record becomes effective
f	End effective date	char(6)	NA	A numeric date in MMDDYY (month, day, year) format representing the record end effective date. This date indicates the last date the record is effective
g	number of reporting units	number(1)	0,1, or 2 or 3	The number of reporting units required by the Bureau of the Census. In a few instances, units not required by Census may be required to compute duty. In these cases, the Census reporting units are always first, followed by any additional units required to compute the duty
h	1st reporting unit of measure	char(4)	NA	A code representing the first unit of measure. If the reporting unit is X, no unit of measure is required except for certain tariff numbers in Chapter 99. Valid unit of measure codes are listed in Appendix C
I	2nd reporting unit of measure	char(4)	NA	A code representing the second unit of measure. Valid unit of measure codes are listed in Appendix C
j	3rd reporting unit of measure	char(4)	NA	A code representing the third unit of measure. Valid unit of measure codes are listed in Appendix C
k	duty computation code	char(1)	NA	A code indicating the formula to be used to compute the duty. Valid Duty Computation Codes are listed in Appendix F
l	commodity description	char(30)	NA	A condensed version of the commodity description that appears in the HTS
m	column 1 specific rate of duty	Number(12)	NA	The rate of duty that appears in the General column of the HTS. Eight decimal places are implied
n	base rate indicator	char(1)	'B' or blank	A code indicating if the rate contains a base rate. If the base rate indicator is B, the duty rate is a base rate; otherwise, space fill. Not Used in RMS
o	space fill	char(1)	blank	Space fill. Not used in RMS
V2 a	Control identifier	char(1)	V	Identifies start of record
b	Record type	char(1)	2	Identifies record type
c	tariff number	Number (10)	NA	A code located in the Harmonized Tariff Schedule of the United States Annotated (HTS) representing the tariff number. If this number is less than 10 positions, it is left justified. This number is the same as that in Record Identifier V1
d	general column 1 ad valorem percentage	Number (12)	NA	The ad valorem rate of duty that appears in the General column of the HTS. Eight decimal places are implied
e	column 1 other	Number (12)	NA	The rate of duty that appears in the General column of the HTS that is not an ad valorem rate. Eight decimal places are implied
f	Column 2 specific rate	Num(12)	NA	The specific rate of duty that appears in Column 2 of the HTS. Eight decimal places are implied
g	Column 2 ad valorem percentage	Num(12)	NA	The ad valorem rate of duty that appears in Column 2 of the HTS. Eight decimal places are implied
h	Column 2 other rate	Num(12)	NA	The rate of duty that appears in Column 2 of the HTS that is not an ad valorem rate or a specific rate. Eight decimal places are implied
i	countervailing duty flag	char(1)	blank or 1	A code of 1 indicating the tariff number is subject to countervailing duty; otherwise, space fill
j	additional tariff indicator	char(1)	blank or 'R'	A code indicating if an additional tariff number may be required with this tariff number. Refer to the Harmonized Tariff Schedule of the United States Annotated (HTS) for more specific information on which HTS numbers require additional HTS numbers to be reported. This indicator is R when an additional tariff number may be required; otherwise, space fill
k	Miscellaneous Permit/ License Indicator	char(2)	NA	A code indicating if a tariff number may be subject to a miscellaneous permit/license number
l	space fill	char(4)	blanks	Not used in RMS
V3 a	Control identifier	char(1)	V	identifies start of record
b	Record type	char(1)	3	identifies record type
c	tariff number	Number(10)	NA	A code located in the Harmonized Tariff Schedule of the United States Annotated (HTS) representing the tariff number. If this number is less than 10 positions, it is left justified. This number is the same as the number in Record Identifier V1
d	GSP excluded countries	char(20)	NA	The International Organization for Standardization (ISO) country code that indicates countries not eligible for preferential treatment under GSP. Up to ten 2 position country codes can be reported. If countries are excluded from GSP, the Special Programs Indicator (SPI) Code contained in this record (positions 53 64) is A*. Valid ISO country codes are listed in Appendix B
e	OGA codes	char(15)	NA	Codes that indicate special requirements by other Federal Government agencies must or may apply. Up to five 3 position OGA codes can be provided
f	anti-dumping flag	char(1)	1 or blank	A code of 1 indicating the tariff number is subject to an antidumping duty; otherwise, space fill
g	quota indicator	char(1)	1 or blank	A code of 1 indicating the tariff number may be subject to quota. If the tariff number is not subject to quota, space fill
h	category number	char(6)	NA	A code located in the HTS indicating the textile category assigned to the tariff number. If there is no textile category number, space fill
I	special program indicators	char(28)	NA	A code indicating if a tariff number is subject to a special program. Up to fourteen 2 position codes can be reported. Left justify. The SPI codes are not reported in any particular sequence. If more than fourteen 2-position codes are required, they are reported on the VD record
NEWLINE			\n
V4 a	Control identifier	char(1)	V	identifies start of record. Entire V4 record not used in RMS
b	Record type	char(1)	4	identifies record type
c	tariff number	Number (10)	NA	A code located in the Harmonized Tariff Schedule of the United States Annotated (HTS) representing the tariff number. If this number is less than 10 positions, it is left justified. This number is the same as the number reported in Record Identifier V1
d	value edit code	char(3)	NA	A code representing the value edit
e	value low bounds	Number (10)	NA	A value representing the minimum value edit. Five decimal places are implied. If this record contains date edits (positions 36 53), space fill
f	value high bounds	Number (10)	NA	A value representing the maximum value edit. Five decimal places are implied. If this record contains date edits (positions 36 53), space fill
g	entry date restriction	Number (1)	0,1, or 2	A code representing the first entry date restriction code
h	beginning restriction date	char(4)	NA	A numeric date in MMDD (month and day) format representing the first begin restriction date used in the edit. If this record contains a value edit (positions 13 35), space fil
l	end restriction date	char(4)	NA	A numeric date in MMDD (month and day) format representing the first end restriction date used in the edit. If this record contains a value edit (positions 13 35), space fill
j	entry date restriction 2	number(1)	0,1 or 2	A code representing the second entry date restriction code
k	beginning restriction date 2	char(4)	NA	A numeric date in MMDD (month and day) format representing the second begin restriction date used in the edit. If this record contains a value edit (positions 13 35), space fill
l	end restriction date 2	char(4)	NA	A code located in the Harmonized Tariff Schedule of the United States Annotated (HTS) representing the tariff number. If this number is less than 10 positions, it is left justified. This number is the same as the number reported in Record Identifier V1
m	country of origin	char(2)	NA	A code representing the value edit
n	space filler	char(2)	blanks	A value representing the minimum value edit. Five decimal places are implied. If this record contains date edits (positions 36 53), space fill
o	quantity edit code	char(3)	NA	A value representing the maximum value edit. Five decimal places are implied. If this record contains date edits (positions 36 53), space fill
p	low quantity	Number (10)	NA	A code representing the first entry date restriction code
q	high quantity	Number (10)	NA	A numeric date in MMDD (month and day) format representing the first begin restriction date used in the edit. If this record contains a value edit (positions 13 35), space fill
V5 a	Control identifier	char(1)	V	Identifies start of record
b	Record type	char(1)	5,6,7,8,9,A,B,C	Identifies record type
c	tariff number	Number (10)	NA	A code located in the Harmonized Tariff Schedule of the United States Annotated (HTS) representing the tariff number. If this number contains less than 10 positions, it is left justified. This number is the same as the number reported in Record Identifier V1
d	Country code	char(2)	NA	A code representing the country. Valid ISO country codes are listed in Appendix B. E followed by a space (Caribbean Basin Initiative), and J followed by a space (Andian Trade Preference Act), and R followed by a space (Caribbean Trade Partnership Act), are also valid codes for special rates. Countries eligible for E and J are indicated in the ACS country code file and the Harmonized Tariff Schedule of the United States Annotated (HTS)
e	specific rate	Number (12)	NA	The specific rate of duty listed in the Special column of the HTS. Eight decimal places are implied
f	ad valorem rate	Number (12)	NA	The ad valorem rate of duty listed in the Special column of the HTS. Eight decimal places are implied
g	Other rate	Number (12)	NA	The rate of duty listed in the Special column of the HTS that is not a specific or ad valorem rate. Eight decimal places are implied
h	tax/fee class code	char(3)	NA	A code representing the tax/fee class. Valid tax/fee class codes are listed in Appendix B
I	tax/fee comp code	char(1)	NA	A code indicating the first tax/fee computation formula. Computation formulas are presented in Appendix F
j	tax/fee flag	number(1	NA	A code indicating a tax/fee is required. Valid Tax/Fee Flag Codes are: 1 = Tax/fee required 2 = Tax/fee may be required. Not used in RMS
k	tax/fee specific rate	Number (12)	blank if no value	The specific rate of duty required to compute taxes and/or fees. Eight decimal places are implied
l	tax/fee ad valorem	Number (12)	blank if no value	The ad valorem rate of duty required to compute taxes and/or fees. Eight decimal places are implied
m	space fill	char(1)	blank	Space fill
VD a	Control identifier	char(1)	V	identifies start of record
b	Record type	char(1)	D	identifies record type
c	tariff number	Number (10)	NA	unique tariff number
d	Special Program Indicator (SPI) Code	char(32)	NA	A code indicating if a tariff number is subject to a special program. Up to sixteen additional 2-position codes can be reported. Left justify. The SPI codes are not reported in any particular sequence
e	Filler	char(36)	NA	Space fill

Note:

V6 through VC records have the same fields as the V5 record.

tranupld (Transportation Upload)

Module Name	tranupld.pc
Description	Transportation Upload
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS140
Runtime Parameters	NA

Design Overview

This program uploads data from trading partners about the transportation of merchandise from the manufacturing site through customs clearance.

Scheduling Constraints

Table 19-8 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This batch does not need to be scheduled when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

The logical unit of work is a valid DTRAN record. The program reads each DTRAN record from the upload file, validates it and processes it. The recommended commit max counter value for this program is 1000 (this value depends on the implementation).

Key Tables Affected

Table 19-9 Key Tables Affected

Table	Select	Insert	Update	Delete
TRANSPORTATION	Yes	Yes	Yes	Yes
IF_ERRORS	No	Yes	No	No
PARTNER	Yes	No	No	No
FREIGHT_TYPE	Yes	No	No	No
FREIGHT_SIZE	Yes	No	No	No
CURRENCIES	Yes	No	No	No
ORDHEAD	Yes	No	No	No
ORDSKU	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
OUTLOC	Yes	No	No	No
SCAC	Yes	No	No	No
COUNTRY	Yes	No	No	No
UOM_CLASS	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No

Integration Contract

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000177

Input File Layout

Table 19-10 Input File Layout

Record Name	Field Name	Field Type	Default Value	Description
FTRAN	Record descriptor	Char(5)	FTRAN	File head marker
	Line id	Number(10)	0000000001	Unique line id
	File type definition	Char(4)	TRUP	Identifies program as tranupld
	File create date	Char(14)	Current date	YYYYMMDDHHMISS format
DTRAN	Record descriptor	Char(5)	DTRAN	Vessel, Voyage, ETD, Container, BL, Invoice File head
	Line id	Number(10)	NA	Unique line id
	Partner Type	Char(6)	NA	Identifies the partner type
	Partner ID	Char(10)	NA	Identifies the partner id
	Vessel ID	Char(20)	NA	Identifies the Vessel
	Voyage ID	Char(10)	NA	Identifies the Voyage or Flight ID
	Estimated Depart Date	Char(8)	NA	YYYYMMDD format
	Shipment Number	Char (20)	NA	Identifies an outside Shipment number
	Actual Arrival Date	Char(8)	NA	YYYYMMDD format
	Trans Mode	Char(6)	NA	Identifies the type of transportation being used. Valid values are found in the TRMO Code Type on the CODE_DETAIL table
	Vessel SCAC Code	Char(6)	NA	Customs defined ID for the Vessel. Validated against SCAC table
	Estimated Arrival Date	Char(8)	NA	YYYYMMDD format
	Lading Port	Char(5)	NA	Identifies the Lading Port. Validated against OUTLOC with type = &rsquor;LP'
	Discharge Port	Char(5)	NA	Identifies the Discharge Port. Validated against OUTLOC with type = &rsquor;DP'
	Service Contract Number	Char(15)	NA	Identifies the outside Service Contract Number
	Container id	Char(20)	NA	Identifies the Container
	Container SCAC code	Char(6)	NA	Customs defined id for the container. Validated against SCAC table
	Delivery Date	Char(8)	NA	YYYYMMDD format
	Seal id	Char(15)	NA	Customs defined id for the container's seal
	Freight Type	Char(6)	NA	Code that identifies the container type. Validated against the FREIGHT_TYPE table
	Freight Size	Char(6)	NA	Code that identifies the container size. Validated against the FREIGHT_SIZE table
	In Transit No.	Char(15)	NA	External transit number
	In Transit Date	Char(8)	NA	YYYYMMDD format
	BL/AWB id	Char(30)	NA	Identifies the Bill of Lading or Air Way Bill
	Candidate Ind	Char(1)	Defaulted to 'N'	Identifies a complete Transportation record. Valid values are &rsquor;Y' and &rsquor;N'
DPOIT	Record descriptor	Char(5)	DPOIT	Order/Item detail info
	Line id	Number(10)	NA	Unique file line id
	ACD_Code	Char(1)	NA	Determines which process to perform &rsquor;A'dd, &rsquor;C'hange, &rsquor;D'elete.
	Rush Ind	Char(1)	Defaulted to 'N'	Identifies whether or not the item should be on a &rsquor;Rush' delivery. Valid values are &rsquor;Y' and &rsquor;N'
	Order number	Number(8)	NA	RMS order no
	Item	Char(25)	NA	RMS Item
	Invoice id	Char(30)	NA	Identifies the Commercial Invoice
	Invoice date	Char(8)	NA	YYYYMMDD format
	Currency Code	Char(3)	NA	Currency that the Currency Amount is reported in. Validated against CURRENCIES table.
	Exchange Rate	Char (20)	NA	The exchange rate back to the primary currency (10 implied decimals)
	Invoice amt	Char (20)	NA	Invoice amt*10000 (with 4 implied decimal places), amount charged by supplier for the PO/Item
	Origin Country id	Char(3)	NA	Identifies where the PO/Item was made
	Consolidation Country id	Char(3)	NA	Identifies where the PO/Items were consolidated
	Export Country id	Char(3)	NA	Identifies where the PO/Items where shipped from
	Status	Char(6)	NA	Identifies the PO/Item status. Valid values are found in the TRCO Code Type on CODE_DETAIL
	Receipt ID	Char(30)	NA	Identifies the external receipt number
	FCR id	Char(15)	NA	Identifies the Freight Cargo Receipt id
	FCR date	Char(8)	NA	YYYYMMDD format
	Packing Method	Char(6)	NA	Identifies the Packing Type (Hanging or Flat). Valid values are &rsquor;HANG' or &rsquor;FLAT'
	Lot Number	Char(15)	NA	Identifies the Lot Number of the PO/Item
	Item Qty	Number(12)	NA	Item Qty*10000(with 4 implied decimals), qty of Items
	Item QTY UOM	Char(4)	NA	Identifies the UOM associated with the item quantity
	Carton QTY	Number(12)	NA	Carton QTY*10000 (with 4 implied decimals), qty of Cartons
	Carton QTY UOM	Char(4)	NA	Identifies the UOM associated with the carton quantity
	Gross WT	Number(12)	NA	Gross WT*10000 (with 4 implied decimals), Gross weight
	Gross WT UOM	Char(4)	NA	Identifies the UOM associated with the gross weight
	Net WT	Number(12)	NA	Net WT*10000 (with 4 implied decimals), Net Weight
	Net WT UOM	Char(4)	NA	Identifies the UOM associated with the net weight
	Cubic	Number(12)	NA	Cubic*10000 (with 4 implied decimals), cubic size
	Cubic UOM	Char(4)	NA	Identifies the UOM associated with the cubic size
	Comments	Char(256)	NA	User Comments
FTAIL	Record type	Char(5)	FTAIL	NA
	Line id	Number(10)	NA	Unique file line id
	No. of lines	Number(10)	NA	Total number of transaction lines in file (not including FHEAD and FTAIL)

lcadnld (Letter of Credit Application Download)

Module Name	Lcadnld.pc
Description	Letter of Credit Application Download
Functional Area	Retail Trade Management
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS57
Runtime Parameters	NA

Design Overview

Lcadnld sends letter of credit (LC) applications to partner banks. Online user actions flag LCs for download by writing to the LC_DOWNLOAD table.

Scheduling Constraints

Table 19-11 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Run lcadnld before the lcmt700 Perl script. This batch is not scheduled to run when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	NA
Post-Processing	LCMT700 Perl script
Threading Scheme	No threading due to low volume

Restart/Recovery

Restart/recovery for this program is set up at the lc_ref_id level. The recommended commit counter setting is 10000 records (subject to change based on experimentation).

Key Tables Affected

Table 19-12 Key Tables Affected

Table	Select	Insert	Update	Delete
LC_HEAD	Yes	No	Yes	No
LC_DETAIL	Yes	No	No	No
LC_DOWNLOAD	Yes	No	No	Yes
OUTLOC	Yes	No	No	No
ADDR	Yes	No	No	No
SUP_IMPORT_ATTR	Yes	No	No	No
SUPS	Yes	No	No	No
PARTNER	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
DOC	Yes	No	No	No
REQ_DOC	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000052

Table 19-13 File Layout

Record Name	Field Name	Field Type	Default Value	Description
File Header	File Type Record Descriptor	Char(5)	FHEAD	Identifies file record type
	File Line Identifier	Number(10)	line number in file	ID of current line being created for output file
	File Type Definition	Char(4)	LCAP	Identifies file as &rsquor;Letter of Credit Application'
	File Create Date	Char(14)	create date	Current date, formatted to &rsquor;YYYYMMDDHH24MISS'
File Detail	File Type Record Descriptor	Char(5)	THEAD	Identifies file record type
	File Line Sequence Number	Number(10)	line number in file	ID of current line being created for output file.
	Transaction Set Control Number	Number(10)	sequence number	Used to force unique file check
	Issuing Bank	Char(10)	lc_head.issuing_bank	Used to sort the LCs into individualized bank SWIFT formatted files (using another program) - bank where LC application is headed
	Issuing Bank Name	Char(240)	partner.partner_desc	The description from the partner table where partner_id = issuing_bank and partner_type = 'BK'
	Issuing Bank Address 1	Char(240)	addr.add_1	Mandatory line of address
	Issuing Bank Address 2	Char(240)	addr.add_2	Non-mandatory line of address (can be null)
	Issuing Bank Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Issuing Bank City	Char(120)	addr.city	City bank located in
	Issuing Bank State	Char(3)	addr.state	State, if applicable, where bank located in
	Issuing Bank Post Code	Char(30)	addr.post	Post code, if applicable, where bank located in
	Issuing Bank Country	Char(3)	addr.country_id	Country bank located in
	Advising Bank	Char(10)	lc_head.advising_bank	Used to sort the LCs into individualized bank SWIFT formatted files (using another program) - bank where LC application is headed
	Advising Bank Name	Char(240)	Partner.partner_desc	The description from the partner table where partner_id = advising_bank and partner_type = 'BK'
	Advising Bank Address 1	Char(240)	Addr.add_1	Mandatory line of address
	Advising Bank Address 2	Char(240)	Addr.add_2	Non-mandatory line of address (can be null)
	Advising Bank Address 3	Char(240)	Addr.add_3	Non-mandatory line of address (can be null)
	Advising Bank City	Char(120)	Addr.city	City bank located in
	Advising Bank State	Char(3)	Addr.state	State, if applicable, where bank located in
	Advising Bank Post Code	Char(30)	Addr.post	Post code, if applicable, where bank located in
	Advising Bank Country	Char(3)	Addr.country_id	Country bank located in
	Letter of Credit	Number(8)	lc_head.lc_ref_id	The LC_REF_ID off the LC_HEAD table
	Form Type	Char(6)	lc_head.form_type	The level of detail that the LC will send to the issuing bank
	Form Type Description	Char(40)	code_detail.code_desc	Describes the form type: Long or Short
	Letter of Credit Type	Char(6)	lc_head.lc_type	Describes the form type: Long or Short
	Letter of Credit Type Description	Char(40)	code_detail.code_desc	Describes the LC type: Master, Normal, Revolving
	Form of Letter of Credit – I	Char(1)	sup_import_attr.revocable_ind	The REVOCABLE_IND from the SUP_IMPORT_ATTR table
	Form of Letter of Credit – II	Char(1)	lc_head.transferable_ind	Indicates if LC transferable
	Application Date	Char(14)	lc_head.application_date	Date the LC is created within RTM/RMS, formatted to 'YYYYMMDD HH24MISS'
	Expiration Date	Char(14)	lc_head.expiration_date	The date the LC expires, formatted to 'YYYYMMDD HH24MISS'
	Place of Expiry	Char(6)	lc_head.place_of_expiry	Code for the place the LC will expire
	Place of Expiry Description	Char(40)	desc is retrieved through a decode	The description of the place the LC will expire
	Applicant	Char(10)	lc_head.applicant	Party on whose behalf the LC is being issued
	Applicant Name	Char(240)	partner.partner_desc	The description from the partner table where partner_id = applicant and partner_type = 'AP'
	Applicant Address 1	Char(240)	addr.add_1	Mandatory line of address
	Applicant Address 2	Char(240)	addr.add_2	Non-mandatory line of address (can be null)
	Applicant Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Applicant City	Char(120)	addr.city	City applicant located in
	Applicant State	Char(3)	addr.state	State, if applicable, where applicant located in
	Applicant Post Code	Char(10)	addr.post	Post code, if applicable, where applicant located in
	Applicant Country	Char(3)	addr.country_id	Country applicant located in
	Beneficiary	Number(10)	lc.head.beneficiary	Party in favor of which the LC is being issued
	Beneficiary Name	Char(240)	sups.sup_name	Beneficiary (supplier) name from the SUPS table
	Beneficiary Address 1	Char(240)	addr.add_1	Mandatory line of address
	Beneficiary Address 2	Char(240)	addr.add_2	Non-mandatory line of address (can be null)
	Beneficiary Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Beneficiary City	Char(120)	addr.city	City beneficiary located in
	Beneficiary State	Char(3)	addr.state	State, if applicable, where beneficiary located in
	Beneficiary Post Code	Char(30)	addr.post	Post code, if applicable, where beneficiary located in
	Beneficiary Country	Char(3)	addr.country_id	Country beneficiary located in
	Currency Code	Char(3)	lc_head.currency_code	The country of origin for the orders on the LC
	Exchange Rate	Number (20,10)	lc_head.exchange_rate	Exchange_rate to convert LC currency to RMS currency
	Origin Country ID	Char(3)	lc_head.origin_country_id	Origin country of the orders associated with the LC
	Presentation Terms	Char(6)	lc_head.presentation_terms	Code for the terms of presentation
	Presentation Terms Description	Char(40)	desc is retrieved through a decode	Description of the terms of presentation
	Purchase Type	Char(6)	lc_head.purchase_type	Code for the purchase type
	Purchase Type Description	Char(40)	desc is retrieved through a decode	Description of the purchase type
	Advice Method	Char(6)	lc_head.advice_method	Code for the advice method
	Advice Method Description	Char(40)	desc is retrieved through a decode	Description of the advice method (eg. Full Wire, Mail, and so on)
	Issuance	Char(6)	lc_head.issuance	Code for the issuance
	Issuance Description	Char(40)	desc is retrieved through a decode	Description of the issuance (for example Cable, Telex, and so on)
	Amount Type	Char(6)	lc_head.amount_type	If 'E'xact, then amount must be exat, if 'A'pproximate then amount can be within variance percent
	Amount Type Description	Char(40)	desc is retrieved through a decode	Description of amount_type
	Amount	Number (20,4)	lc_head.amount	The total amt of the Letter of Credit
	Variance Percent	Number (12,4)	lc_head.variance_pct	Allowed currency variance percent for the LC
	Specification	Char(6)	lc_head.specification	Code for any condition for the credit, such as,. "maximum", and so on
	Specification Description	Char(40)	desc is retrieved through a decode	Description of condition for the credit, such as,. "maximum", etc
	Credit Available With	Char(10)	lc_head.credit_avail_with	Code for bank with which credit is available
	Credit With Bank Name	Char(40)	partner.partner_desc	The description from the partner table where partner_id = credit_avail_with and partner_type = 'BK'
	Credit With Address 1	Char(240)	addr.add_1	Mandatory line of address
	Credit With Address 2	Char(240)	addr.add_2	Non-mandatory line of address (can be null)
	Credit With Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Credit With City	Char(120)	addr.city	City creditor located in
	Credit With State	Char(3)	addr.state	State, if applicable, where creditor located in
	Credit With Post Code	Char(30)	addr.post	Post code, if applicable, where creditor located in
	Credit With Country	Char(3)	addr.country_id	Country creditor located in
	Drafts At	Char(6)	lc_head.drafts_at	Specifies the terms of the drafts to be drawn under the LC
	Drafts At Description	Char(40)	desc is retrieved through a decode	Description of the terms of the drafts to be drawn under the LC
	Drawee	Char(10)	lc_head.paying_bank	Identifies drawee of drafts to be drawn under LC (paying bank)
	Drawee Name	Char(240)	partner.partner_desc	The description from the partner table where partner_id = paying_bank and partner_type = 'BK
	Drawee Address 1	Char(240)	addr.add_1	Mandatory line of address
	Drawee Address 2	Char(240)	addr.add_2	Non-mandatory line of address (can be null)
	Drawee Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Drawee City	Char(120)	addr.city	City bank located in
	Drawee State	Char(3)	addr.state	State, if applicable, where bank located in
	Drawee Post Code	Char(30)	addr.post	Post code, if applicable, where bank located in
	Drawee Country	Char(3)	addr.country_id	Country bank located in
	Negotiating Bank	Char(10)	lc_head.negotiating_bank	Identifies the negotiating bank
	Negotiating Bank Name	Char(240)	partner.partner_desc	The description from the partner table where partner_id = negotiating_bank and partner_type = 'BK'
	Negotiating Bank Address 1	Char(240)	addr.add_1	Mandatory line of address
	Negotiating Bank Address 2	Char(240)	addr.add_2	Non-mandatory line of address (can be null)
	Negotiating Bank Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Negotiating Bank City	Char(120)	addr.city	City bank located in
	Negotiating Bank State	Char(3)	addr.state	State, if applicable, where bank located in
	Negotiating Bank Post Code	Char(30)	addr.post	Post code, if applicable, where bank located in
	Negotiating Bank Country	Char(3)	addr.country_id	Country bank located in
	Confirming Bank	Char(10)	lc_head.confirming_bank
	Confirming Bank Name	Char(240)	partner.partner_desc	Identifies the confirming bank
	Confirming Bank Address 1	Char(240)	addr.add_1	The description from the partner table where partner_id = confirming_bank and partner_type = 'BK'
	Confirming Bank Address 2	Char(240)	addr.add_2	Mandatory line of address
	Confirming Bank Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Confirming Bank City	Char(120)	addr.city	Non-mandatory line of address (can be null)
	Confirming Bank State	Char(3)	addr.state	City bank located in
	Confirming Bank Post Code	Char(30)	addr.post	State, if applicable, where bank located in
	Confirming Bank Country	Char(3)	addr.country_id	Post code, if applicable, where bank located in
	Transferring Bank	Char(10)	lc_head.transferring_bank	Country bank located in
	Transferring Bank Name	Char(240)	partner.partner_desc	Identifies the transferring bank
	Transferring Bank Address 1	Char(240)	addr.add_1	The description from the partner table where partner_id = transferring_bank and partner_type = 'BK'
	Transferring Bank Address 2	Char(240)	addr.add_2	Mandatory line of address
	Transferring Bank Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Transferring Bank City	Char(120)	addr.city	Non-mandatory line of address (can be null)
	Transferring Bank State	Char(3)	addr.state	City bank located in
	Transferring Bank Post Code	Char(30)	addr.post	State, if applicable, where bank located in
	Transferring Bank Country	Char(3)	addr.country_id	Post code, if applicable, where bank located in
	Partial Shipment Indicator	Char(1)	lc_head.partial_ship_ind	Country bank located in
	Transshipment Indicator	Char(1)	lc_head.transshipment_ind	Indicates whether goods covered by LC can be partially shipped or not
	Fob Title Pass	Char(6)	lc_head.fob_title_pass	Indicates whether goods can be transferred to another vessel midway through the voyage
	Fob Title Pass Decode	Char(40)	desc is retrieved through a decode	Indicates where the title for goods is passed from the vendor to the purchaser
	Fob Title Pass Description	Char(250)	lc_head.ob_title_pass_desc	Decode of where the title for goods is passed from the vendor to the purchaser
	Transportation to	Char(5)	lc_head.transportation_to	Describes the FOB_TITLE_PASS - could be city name and so on
	Transportation to description	Char(150)	outloc.outloc_desc	Transportation to location
	With Recourse Indicator	Char(1)	lc_head.with_recourse_ind	Description of transportation to location
	Latest Shipment Date	Char(14)	lc_head.latest_ship_date	Indicates conditional payment on the part of the bank as instructed by the buyer
	Earliest Shipment Date	Char(14)	lc_head.earliest_ship_date	Latest ship date for all Pos included in the LC, formatted to 'YYYYMMDD HH24MISS'
	Letter of Credit Negotiation Days	Number(3) replaces x in the string ”DOCUMENTS TO BE PRESENTED WITHIN x DAYS AFTER ISSUANCE OF THE SHIPPING DOCUMENTS BUT WITHIN THE VALIDITY OF THIS CREDIT”	lc.head.lc_neg_days	The number of days to negotiate documents
	Bank's LC reference id	Number(8)	lc_head.bank_lc_id	Bank's LC ref id
	File Type Record Descriptor	Char(5)	THDCM	Identifies file record type
	File Line Sequence Number	Number(10)	line number in file	ID of current line being created for output file
	Transaction Set Control Number	Number(10)	sequence number	Used to force unique file check
	Header Level Comments	Char(2000)	lc_head.comments	Holds any comments that the user has added to the Letter of Credit.
	File Type Record Descriptor	Char(5)	TDOCS	Identifies file record type
	File Line Sequence Number	Number(10)	line number in file	ID of current line being created for output file
	Transaction Set Control Number	Number(10)	sequence number	Used to force unique file check
	Swift Tag	Char(6)	doc.swift_tag	Identifies individual document types that can be associated with an LC
	Document ID	Number(6)	req_doc.doc_id	Uniquely identifies the individual documents associated with an LC
	Body Text	Char(2000)	req_doc.doc_text	Documents associated with a given LC Description of Goods and Services OR Documents Required OR Additional Conditions OR Narrative
	File Type Record Descriptor	Char(5)	TDETL	Identifies file record type
	File Line Sequence Number	Number(10)	line number in file	ID of current line being created for output file
	Transaction Set Control Number	Number(10)	sequence number	Used to force unique file check
	Order Number	Number(8)	lc_detail.order_no	PO associated with the LC
	Item	Char(25)	lc_detail.item	Item on the PO - item is rolled up to the item_level of 1, if possible
	Cost	Number (20,4)	lc_detail.cost	If form_type = 'S'hort then cost is the total cost of the order; if the form_type = 'L'ong then the cost is the unit cost of the item
	Quantity	Number (12,4)	lc_detail.qty	Total qty of the item for the order on the LC
	Standard UOM	Char(4)	Item_master.standard_uom	Standard unit of measure of the quantity of the item for the order on the LC
	Earliest Ship Date	Char(14)	lc_detail.earliest_ship_date	The earliest date an order on the LC can be shipped, formatted to 'YYYYMMDDHH24MISS
	Latest Ship Date	Char(14)	lc_detail.latest_ship_date	The latest date an order on the LC can be shipped, formatted to 'YYYYMMDD HH24MISS'
	item description	Char(250)	Item_master.desc_up	Item's description
	File Type Record Descriptor	Char(5)	TMERC	Identifies file record type
	File Line Sequence Number	Number(10)	line number in file	ID of current line being created for output file
	Transaction Set Control Number	Number(10)	sequence number	Used to force unique file check
	Merchandise Description	Char(2000)	lc_detail.merch_desc	Contains the merchandise description of the field.
	File Type Record Descriptor	Char(5)	TDTCM	Identifies file record type
	File Line Sequence Number	Number(10)	line number in file	ID of current line being created for output file
	Transaction Set Control Number	Number(10)	sequence number	Used to force unique file check
	Detail Level Comments	Char(2000)	lc_detail.comments	Holds any comments that the user has added to the Letter of Credit detail record.
File Trailer	File Type Record Descriptor	Char(5)	TTAIL	Identifies file record type
	File Line Sequence Number	Number(10)	line number in file	ID of current line being created for output file
	Transaction Set Control Number	Number(10)	sequence number	Used to force unique file check
	Transaction detail line count	Number(10)	ID of current line being created for output file	Sum of the detail lines within a transaction
File Trailer	File Type Record Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Identifier	Number(10)	Sequential number Created by program.	ID of current line being created for output file.
	File Record Counter	Number(10)	NA	Number of records/transactions processed in current file (only records between head & tail)

lcmt700 (SWIFT File Conversion - Letter of Credit Application)

Module Name	lcmt700
Description	SWIFT File Conversion – Letter of Credit Application
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	Perl
Catalog ID	RMS136
Runtime Parameters	NA

Design Overview

This Perl script will convert the standard RMS flat file into the bank specific S.W.I.F.T. MT 700 output files. The input file for this Perl script is the output of the lcadnld.pc RMS batch. One output file will be created for each issuing bank in the lcadnld.pc output file.

Scheduling Constraints

Table 19-14 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	cmt700 should run after Letter of Credit application download program (LCADNLD.PC) This script does not need to be scheduled to run when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000052 (input) IntCon000137 (output)

Output

All files layouts input and output the SWIFT MT 700. The output file should be in the following format:

Most output fields are contained in their own line (or 3-4 line for addresses).
Each application consists of four parts, one MT 700 and three MT 701s, which are ordered through the Sequence of Total field: for example, ':27:1/4 MT 700' is the first (MT 700) part of the application.
MT 700 and MT 701s will be mingled in the same file.
Each record starts with a colon and a SWIFT field identifier, followed by another colon: for example, ':40A:'-
Each application is separated by a line with only the ASCII 3 symbol (a heart) on it.

Examples of how individual lines of the MT 700 or MT 701 should look:

:27:1/4
:40A:IRREVOCABLE
:20:29893098
:23:NOREF
:31C:910906
:31D:911022DALLAS
:51D:NORTHERN TRUST INT'L BANKING CORP.
       ONE WORLD TRADE CENTER
SUITE 3941
NY, NY 10048 USA

The layout of the S.W.I.F.T MT 700 (Issue of a Documentary Credit) file is as follows:

SWIFT I.D. DATA TYPE CODES (refer to SWIFT User Handbook - Standards general Information - October 1998 release for formatting information):

Note:

There is always a new line (nl) after every individual SWIFT ID (and there may be more than one line within an individual field [for example, 59 – Beneficiary, four lines to hold address information]).

In some situations, certain fields will be blank. These fields should be skipped over. In other words, no blank line or tag should be printed indicating the field is blank. Simply ignore it.

lcupld (Letter of Credit Confirmation Upload)

Module Name	lcupld.pc
Description	Letter of Credit Confirmation Upload
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS55
Runtime Parameters	NA

Design Overview

The LCUPLD program is used to upload LC (Letter of Credit) confirmations from bank partners.

After this program has processed a confirmation, the appropriate tables will be updated; a confirmation will update the LC to confirm status and it will write the appropriate records to the LC_ACTIVITY table.

Scheduling Constraints

Table 19-15 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This batch does not need to be scheduled when rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	LCMT 730 Perl script
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

Restart/recovery for this program is set up at the individual FDETL record. Although there may be more than one FDETL record for a given LC, they will each be processed as a separate entity.

File based restart/recovery must be used. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The recommended commit counter setting is 10000 records.

Key Tables Affected

Table 19-16 Key Tables Affected

Table	Select	Insert	Update	Delete
LC_HEAD	Yes	No	Yes	No
LC_ACTIVITY	No	Yes	No	No

Integration Contract

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000054

Input File Layout

Table 19-17 Input File Layout

Record Name	Field Name	Field Type	Default Value	Description
File Header	File Type Record Descriptor	Char(5)	FHEAD	Identifies file record type
	File Line Sequence Number	Number(10)	0000000001	Line number of the current file
	File Type Definition	Char(4)	LCUP	Identifies file as &rsquor;Letter of Credit Upload'
	File Create Date	Char (14)	vdate	Date file was written by external system &rsquor;YYYYMMDDHH24MISS' format
File Detail	File Type Record Descriptor	Char(5)	FDETL	Identifies file record type
	File Line Sequence Number	Number(10)		Line number of the current file
	Sender's Reference	Char(16)	lc_head.bank_lc_id	The LC number that the bank assigns to a Letter of Credit
	Receiver's Reference	Number(8)	lc_activity.lc_ref_id	The LC number that Retek assigned to the Letter of Credit
	Date of Message Being Acknowledged	Char(14)	lc_activity.activity_date	YYYYMMDDHH24MISS format
	Comments	Char(2000)	lc_activity.comments	This field is a concatenation of the following SWIFT fields: 71B – Charges, 72 – Sender information
File Trailer	File Type Record Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Sequence	Number(10)	NA	Line number of the current file
	Total number lines	Number(10)	NA	Total number of lines in file not including FHEAD and FTAIL

lcmt730 (SWIFT File Conversion - Letter of Credit Confirmation)

Module Name	lcmt730
Description	SWIFT File Conversion – Letter of Credit Confirmation
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	Perl
Catalog ID	RMS138
Runtime Parameters	NA

Design Overview

The lcmt730 Perl script converts letter of credit confirmations from a S.W.I.F.T. format (MT730) to a RMS flat file format. The output file from this script will be the input file for the lcupld.pc.

Scheduling Constraints

Table 19-18 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	LCMT730 should run prior to Letter of Credit upload program (lcupld.pc) This script does not need to be scheduled when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	NA
Post-Processing	lcupld.pc
Threading Scheme	NA

Integration Contract

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000054 (output) IntCon000139 (input)

Input File Layout

Table 19-19 Input File Layout

SWIFT I.D. and Description	Data Type	Description	How MT 730 fields are put into the RMS standard file format and what should be the size of RMS to be dealt with	Comments
20 - Sender's Reference	16x	LC number. The one assigned by the Sender (issuing bank)	FDETL - Sender's reference, Char(16)	This field maps to RTM's Bank LC Ref ID.
21 - Receiver's Reference	16x	LC number assigned by the Receiver (retailer)	FDETL - Receiver's reference, Number(8) (NOREF used if unknown)	This field maps to RTM's LC Ref ID. If this field has 'NOREF', the record must be rejected since this field is used to indicate the LC within RTM to which this record applies.
25 - Account Identification	35x	Identifies the number of the account, which has been used for the settlement of charges, on the books of the Sender.	NA	RTM currently does not have fields that map directly to this. Current position - will be included in the input file. However, it will be ignored during the upload process.
30 - Date of Message Being Acknowledged	6!n	When a message is acknowledging a MT700, this field specifies the date of issue. In all other cases, this field specifies the date on which the message being acknowledged was sent.	FDETL - Date of message Being Acknowledged, Date	This field maps to the LC activity date. As well, if this in confirming an LC application, it will be mapped to the LC's confirmation date. Year interpretation: If YY>79 then YYMMDD = 19YYMMDD Else YYMMDD = 20YYMMDD.
32a - Amount of Charges	Option B - 3!a15d Option D - 6!n3!a15d	Contains the currency code and total amount of charges claimed by the sender of the message. When charges have been debited, D is used (:32D) and when reimbursement for charges is needed, B is used (:32B).	FDETL -Upload_type = 'C'onfirmation	Current position - Because the 730 will only be used for confirmations, this field will not contain any values. The upload type should be set equal to 'C'onfirmation.
57a - Account With Bank	Option A - [/1!a][/34x] 4!a2!a2!c[ 3!c] Option D - [/1!a][/34x] 4*35x	This field specifies the bank to which the amount of charges is to be remitted in favor of the Sender.	FDETL - Account With Bank, Char(10)	Current position - will be added to the input file however will be ignored in the upload process. Because RTM has no facilities to maintain BICs or party identifiers, option D will always be used for this field (that is, 57D) without [/1!a][/34x] party identifier.
71B - Charges	6*35x	Specification of the charges claimed.	FDETL - Comments, Char(2000)	This field maps to RTM's activity comments field. Sender to Receiver information (72) will be concatenated to this.
72 - Sender to Receiver Information	6*35x	Text explanation if wanted.	FDETL - Comments, Char(2000)	This field maps to RTM's activity comments field. Charges (71B) will be concatenated to this.

Output File Layout

Table 19-20 Output File Layout

Record Name	Field Name	Field Type	Default Value	Description
File Header	File Type Record Descriptor	Char(5)	FHEAD	Identifies file record type
	File Line Sequence Number	Number(10)	specified by external system	Line number of the current file
	File Type Definition	Char(4)	LCUP	Identifies file as &rsquor;Letter of Credit Upload'
	File Create Date	Char (14)	vdate	date file was written by external system &rsquor;YYYYMMDD HH24MISS' format
File Detail	File Type Record Descriptor	Char(5)	FDETL	Identifies file record type
	File Line Sequence Number	Number(10)	specified by external system	Line number of the current file
	Sender's Reference	Char(16)	lc_head.bank_ld_id	The LC number that the bank assigns to a Letter of Credit
	Receiver's Reference	Number(8)	lc_activity.lc_ref_id	The LC number that RMS assigned to the Letter of Credit
	Date of Message Being Acknowledged	Date (char 8)	lc_activity.activity_date	If the upload type is &rsquor;L' then this date will match the date MT 700 date of issue (which we have not resolved between being the vdate or the lc_head.application_date) &rsquor;YYYYMMDD' format
	Comments	Char(2000)	lc_activity.comments	Need to truncate? This field will probably be a concatenation of the following SWIFT fields: 71B – Charges, 72 – Sender information
File Trailer	File Type Record Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Sequence	Number(10)	Specified by external system	Line number of the current file
	Total number of lines	Number(10)	Specified by external system	Total number lines in file

lcmdnld (Letter of Credit Amendment Download)

Module Name	lcmdnld.pc
Description	Letter of Credit Amendment Download
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS56
Runtime Parameters	N/A

Design Overview

lcmdnld.pc downloads amended letter of credit information to a bank, in the S.W.I.F.T. format.

Online user actions flag LCs for download by writing to the LC_DOWNLOAD table.

Scheduling Constraints

Table 19-21 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This batch does not need to be scheduled when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y N/A
Pre-Processing	NA
Post-Processing	lcmt707 perl script
Threading Scheme	No threading due to low volume

Restart/Recovery

Restart/recovery for this program is set up at the lc_ref_id level. The recommended commit counter setting is 1000 records (subject to change based on experimentation).

Key Tables Affected

Table 19-22 Key Tables Affected

Table	Select	Insert	Update	Delete
LC_AMENDMENTS	Yes	No	Yes	No
LC_HEAD	Yes	No	No	No
LC_DOWNLOAD	Yes	No	No	Yes
ADDR	Yes	No	No	No
PARTNER	Yes	No	No	No
SUPS	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
DOC	Yes	No	No	No
REQ_DOC	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000053

Input File Layout

Table 19-23 File Layout

Record Name	Field Name	Field Type	Default Value	Description
File Header	File Type Record Descriptor	Char(5)	FHEAD	Identifies file record type
	File Line Sequence Number	Number(10)	Line number in file	Keeps track of the record's position in the file by line number
	File Type Definition	Char(4)	LCAM	Identifies file as &rsquor;Letter of Credit Amendment'
	File Create Date	Char(14)	Create date	Current date, formatted to &rsquor;YYYYMMDDHH24MISS'
Transaction Header	Filetype Record descriptor	Char(5)	THEAD	Identifies file record type
	File Line Sequence Number	Number (10)	Line number in file	Keeps track of the record's position in the file by line number
	Transaction Set Control Number	Number (10)	Sequence number	Used to force unique file check
	Issuing Bank	Char(10)	lc_head.issuing_bank	Used to sort the LCs into individualized bank SWIFT formatted files (using another program) – bank where LC application is headed
	Issuing Bank Name	Char(240)	partner.partner_desc	The description from the partner table where partner_id = issuing_bank and partner_type = &rsquor;BK'
	Issuing Bank Address 1	Char(240)	addr.add_1	Mandatory line of address
	Issuing Bank Address 2	Char(240)	addr.add_2	Non-mandatory line of address (can be null)
	Issuing Bank Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Issuing Bank City	Char(120)	addr.city	City bank located in
	Issuing Bank State	Char(3)	addr.state	State, if applicable, where bank located in
	Issuing Bank Post Code	Char(30)	addr.post	Post code, if applicable, where bank located in
	Issuing Bank Country	Char(3)	addr.country_id	Country bank located in
	Letter of Credit	Number (8)	lc_detail.lc_ref_id	The LC_REF_ID off the LC_DETAIL table
	Bank Letter of Credit ID	Char(16)	lc_head.bank_lc_id	The BANK_LC_ID off the LC_HEAD table
	Currency Code	Char(3)	lc_head.currency_code	The CURRENCY_CODE off the LC_HEAD table
	Date of Issue/ Transfer of the Credit	Char(14)	lc_head.confirmed_date	Date the Issuing Bank thinks is the date of issue–when it was officially confirmed, formatted to &rsquor;YYYYMMDDHH24MISS'
	Current Amount of LC	Number (20,4)	NA	This amount will be calculated in the get_current_amount() function and will be the net amount of the LC calculated only using amendments that have been downloaded. Normally, the net amount is calculated using amendments in the &rsquor;D'ownloaded status
	Beneficiary	Number (10)	lc.head.beneficiary	Party in favor of which the LC is being issued
	Beneficiary Name	Char(240)	sups.sup_name	Beneficiary (supplier) name from the SUPS table
	Beneficiary Address 1	Char(240)	addr.add_1	Mandatory line of address
	Beneficiary Address 2	Char(240)	addr.add_2	Non-mandatory line of address (can be null)
	Beneficiary Address 3	Char(240)	addr.add_3	Non-mandatory line of address (can be null)
	Beneficiary City	Char(120)	addr.city	City beneficiary located in
	Beneficiary State	Char(3)	addr.state	State, if applicable, where beneficiary located in
	Beneficiary Post Code	Char(30)	addr.post	Post code, if applicable, where beneficiary located in
	Beneficiary Country	Char(3)	addr.country_id	Country beneficiary located in
Transaction Detail	File Type Record Descriptor	Char(5)	TDETL	Identifies file record type
	File Line Sequence Number	Number (10)	line number in file	Keeps track of the record's position in the file by line number
	Transaction Set Control Number	Number (10)	sequence number	Used to force unique file check
	Amendment Number	Number (8)	lc_amendments.amend_no	Holds the amendment number for the amendment
	Order_no	Number (8)	lc_amendments.order_no	Order_no, if applicable, that is attached to the LC that is being amended
	Item	Char(25)	lc_amendments.item	Item being amended, either a Style or Staple sku
	Value Being Amended	Char(6)	lc_amendments.amended_value	LC Field being amended. Can be any of the following code_types: CODE CODE_DESC AI Add Item AO Add PO ARQD Add Reqd Doc. C Cost ED Expiration Date ESD Earliest Ship Date LSD Latest Ship Date NA Net Amount ND Negotiation Days OC Origin Country OQ Order Quantity PE Place of Expiry PRT Presentation Terms PSF Partial Ship Flag RI Remove Item RO Remove PO RRQD Remove Reqd Doc TFF Transferable Flag TSF Transshipment Flag
	Value Being Amended Description	Char(40)	code_detail.code_desc	The Value Being Amended decoded (see the above list). Will possibly be used when printing to the SWIFT file MT 707 for clarity
	Original Value of Amended Field	Char(45)	lc_amendments.original_value	Current value of field that is being amended
	New Value of Amended Field	Char (2000)	lc_amendments.new_value	New value of the field that is being amended
	Description of New Value	Char(40)	code_detail.code_desc	The new value decoded (or fetched from a table, as in the origin_country case)– only applicable to the following amended values: place of expiry, title_pass_location, origin_country, presentation terms, purchase type
	Sign	Char(1)	NA	If the effect is negative it will be ”-” if the effect is positive it will be ” ”
	Effect	Number (20,4)	lc.amendments.effect	Effect that amendment will have on LC if amendment to change qty or cost of a PO or amount of LC itself
	Date of Amendment	Char(14)	Lc_amendments.accept_date	Date on which Issuing Bank (or issuing party, in this case the retailer) considers the credit as being amended, formatted to &rsquor;YYYYMMDD HH24MISS'
Transaction Text	File Type Record Descriptor	Char(5)	TTEXT	Identifies file record type
	File Line Sequence Number	Number (10)	line number in file	Keeps track of the record's position in the file by line number
	Transaction Set Control Number	Number (10)	sequence number	Used to force unique file check
	Amendment Text	Char (2000)	text description	A text description of the individual amendment (for each TDETL line of the output file) built by the package LC_AMEND_SQL. AMEND_TEXT.
Transaction Trailer	File Type Record Descriptor	Char (5)	TTAIL	Identifies File Record Type
	File Line Sequence Number	Number (10)	Line Number in file	ID of current line being created for output file
	Transaction set control number	Number (10)	Sequence number	Used to force unique file check
	Transaction detail line count	Number (10)	ID of current line being created for output file	Sume of the detail lines within a transaction
File Trailer	File Type Record Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Sequence Number	Number (10)	line number in file	Keeps track of the record's position in the file by line number
	Control Number File Line Count	Number (10)	total detail lines	Sum of all transaction lines, not including the file header and trailer

lcmt707 (SWIFT File Conversion – Letter of Credit Amendment)

Module Name	lcmt707
Description	SWIFT File Conversion – Letter of Credit Amendment
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	Perl
Catalog ID	RMS137
Runtime Parameters	N/A

Design Overview

This Perl script converts the Oracle retail standard interface file format for Amendments to Letters of Credit download to the corresponding S.W.I.F.T file format (MT 707). The input file for this Perl script is the output of the lcmdnld.pc RMS batch.

Scheduling Constraints

Table 19-24 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	LCMT707 should run after Letter of Credit amendment download program (lcmdnld.pc) This script is not scheduled to run when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	lcmdnld.pc
Post-Processing	NA
Threading Scheme	NA

Integration Contract

Integration Type	Download to RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000053 (input) IntCon000138 (output)

Output

The SWIFT MT 707 output file should be in the following format:

Most output fields are contained in their own line (or 3-4 line for addresses).
Each amendment consists of only one part, the MT 707. There may be several MT 707s at any given time associated to an LC because they are grouped by amendment number at the time of creation. All TDETL records with the same amend_no will be grouped together in one MT 707.
Each record starts with a colon and a SWIFT field identifier, followed by another colon: for example, &rsquor;:40A:'-
Each amendment is separated by a line with only the ASCII 3 symbol (a heart) on it.

Logic Setup:

The input file will be in standard RMS file format. It will potentially have numerous TDETL lines per each THEAD line. There may be numerous TDETL records for one amendment. MT 707 will write one record for each amendment, so if there are multiple TDETL records they need to be combined. There is one TTEXT for each TDETL.

There are three values that need to be calculated. 32B, 33B, 34B. 32B is the total increment or the sum of the positive effect values for each amendment. 33B is the total decrement or the sum of all the negative effect values for each amendment. 32B and 33B are separate totals for each amendment. 34B is the total difference, so it is the sum of the total increment and total decrement. 34B is not just for one amendment though; it is for all amendments of a THEAD record, so this total will run through each TDETL in a THEAD.

For example: if the input file contains:

THEAD
TDETL amendment 1, effect +1000
TTEXT
TDETL amendment 1, effect +500
TTEXT
TDETL amendment 2, effect -2500
TTEXT
TDETL amendment 3, effect +4000
TTEXT
TDETL amendment 3, effect -1000
TTEXT
TDETL amendment 3, effect +500
TTEXT
TDETL amendment 4, effect -1000
TTEXT
TDETL amendment 4 , effect –2500
TTEXT
TTAIL

32B for amendment 1 = 1500
33B for amendment 1 = 0
34B for amendemnt 1 = 1500
 
32B for amendment 2 = 0
33B for amendment 2 = 2500
34B for amendemnt 2 = -1000
 
32B for amendment 3 = 4500
33B for amendment 3 = 1000
34B for amendemnt 3 = 4500
 
32B for amendment 4 = 0
33B for amendment 4 = 3500
34B for amendemnt 4 = 1000

Examples of how individual lines of the M T 707 should look:

APPLICANT: 
OPERATOR: 
OPERATION DATE: 
OPERATION TIME: 
TEST KEY: 
BATCH TOTAL: 
SEGMENT TOTAL: 
MT/PRIORITY:707 02
:27:1/1
:20:10001981        
:21:1981            
:52D:Bank One                           
100 Bank One Way                   
Columbus        ,OH  41984      US  
:31C:990204
:30:990204
:26E:1       
:59:David Fashion Creations P/L Pack   
Wholesale Division                 
109 Ackland St.                    
St. Kilda      ,VA  30280-1234 US  
:32B:USD500,0
:33B:USD0,0
:34B:USD500,0
:79:Letter of Credit:  has been changed from 25 to 30 
for Style 10049369, resulting in an effect of 500 
(USD).

The layout of the S.W.I.F.T MT 707 (Amendment to a Documentary Credit) file is as follows:

SWIFT I.D. DATA TYPE CODES (refer to SWIFT User Handbook – Standards General Information – October 1998 release for formatting information):

Note:

The field lengths and types in the Oracle Retail Standard Download Format of the MT 707 are important because sometimes they are different from the information that is being placed in them and the fields may have to be truncated, rounded, and so on.

There is always a new line (nl) after every individual SWIFT ID (and there may be more than one line within an individual field (example 59 - Beneficiary, four lines to hold address information).

In some situations, certain fields will be blank. These fields should be skipped over. In other words, no blank line or tag should be printed indicating the field is blank. Simply ignore it.

lcup798 (Letter of Credit Drawdowns and Charges)

Module Name	lcup798.pc
Description	Letter of Credit Drawdowns and Charges
Functional Area	Oracle Retail Trade Management
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS54
Runtime Parameters	NA

Design Overview

This program reads data from an input file containing letter of credit charges and drawings (in standard Oracle Retail format, modified from the SWIFT 798 format by the lcmt798 Perl script), validates it, and inserts it into the LC_ACTIVITY table. If a record fails validation, it will be written to a reject file. These rejected records can be reprocessed by lcup798 after errors have been corrected.

Scheduling Constraints

Table 19-25 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Should be run after the lcmt798 Perl script This batch does not need to be scheduled when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	lcmt798
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This program will be restartable but not threadable.

Restart/recovery logic for file-based processing is used. Records will be committed to the database when commit_max_ctr defined in the RESTART_CONTROL table is reached.

Key Tables Affected

Table 19-26 Key Tables Affected

Table	Select	Insert	Update	Delete
LC_HEAD	Yes	No	No	No
LC_DETAIL	Yes	No	No	No
LC_ACTIVITY	No	Yes	No	No
LC_AMENDMENTS	Yes	No	No	No
CURRENCIES	Yes	No	No	No
CURRENCY_RATES	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No

Integration Contract

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000055

The input file for this batch program is the output from the lcmt798 Perl script.

Input File Layout

Table 19-27 Input File Layout

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	File head descriptor	Char(5)	FHEAD	Describes file line type
	Line id	Number (10)	0000000001	Sequential file line number
	File Type Definition	Char(4)	&rsquor;LCCH'	Identifies as an LC 798 file-Letter of Credit Charges
	Current date	Date	NA	File date in YYYYMMDDHH24MISS format
FDETL	File record descriptor	Char(5)	FDETL	Describes file line type
	Line id	Number (10)		Sequential file line number
	Bank letter of credit reference ID	Char (16)	SWIFT tag 20	Bank's LC ref ID
	Order number	Number(8)	SWIFT tag 21	Order number attached to LC.May be blank
	Invoice number	Number (15)	SWIFT tag 23	NOT a RMS invoice number, just a reference invoice number from the issuing bank. May be blank
	Transaction number	Number (10)	NA	Amendment number or transaction number assigned by bank.May be null
	Transaction code	Char(6)	B or D	&rsquor;B'ank charge or&rsquor;D'rawdown
	Amount	Number(21)	SWIFT tag 33A,71A	(This is a 20-digit number with a leading – sign or blank and 4 implied decimal places.) Amount of charge or drawdown
	Currency code	Char(3)	SWIFT 33A,71A	Currency that the amount is in
	Activity date	Date	SWIFT 33A,32C,32D	Activity date(formatted as 'YYYYMMDD')
	Comments	Char(2000)	SWIFT tag 72	Any comments associated with activity.May be null
FTAIL	File record descriptor	Char(5)	FTAIL	Marks end of file
	Line id	Char(10)	NA	Sequential file line number
	Number of lines	Number(10)	NA	Number of lines in file not counting FHEAD and FTAIL

lcmt798 (SWIFT File Conversion – Letter of Credit Charges and Drawdowns)

Module Name	lcmt798
Description	SWIFT File Conversion – Letter of Credit Drawdowns and Charges
Functional Area	Retail Trade Management - Letter of Credit Interfaces
Module Type	Integration
Module Technology	Perl
Catalog ID	RMS139
Runtime Parameters	NA

Design Overview

This Perl script converts letter of credit (L/C) activity data for charges and drawdowns from a S.W.I.F.T. format input file to a RMS format file.

Scheduling Constraints

Table 19-28 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	LCMT798 should be run prior to the Letter of Credit charges and drawings upload program (LCUP798.PC) This script does not need to be scheduled when the rtm_simplified_ind in SYSTEM_OPTIONS table is set to Y
Pre-Processing	NA
Post-Processing	lcup798.pc
Threading Scheme	NA

Integration Contract

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000139 (input)

Input File Layout

Table 19-29 Input File Layout

Swift Tag	Description	Regd?	Datatype	RMS Field
20 - Transaction Reference Number	The sender's unambiguous identification of the transaction. Its detailed form and content are at the discretion of the sender.	Yes	16x - Transaction Reference Number	Bank L/C ID Lc_head.bank_lc_id Varchar2(16)
12 - Type of Financial Instrument	This field classifies the financial instrument by a description or proprietary code.	Yes	Option A- :4!c/[8c]/30x :4!c - Qualifier / - Delimiter [8c] - Issuer Code / - Delimiter 30x - Type	This field will contain a constant identifier - '798'
77E - Proprietary Message	This field contains the proprietary message in a format agreed to by the Sender and the Receiver.	Yes	Option E- 73x [n*78x]	This field will contain the information below (fields 21, 23, 32C, 32D, 71A, 33A, 72) Carriage return, Line feed, Colon 'CrLf:' will be used to separate fields included in this 77E For example: :77E:'CrLf' :21:10004321:CrLf' :32C:990121USD1045 etc… There may be multiple 77Es in one file
21 - Related Reference	This field specifies, in an unambiguous way, a message or transaction identifier which is normally included as part of the information supplied with the message or transaction itself, and can subsequently be used to distinguish the message or transaction identified from other messages or transactions.	No	16x	P/O Number Lc_activity.order_no Number(8)
23 - Further identification	This field specifies the type of transaction being confirmed, as well as the settlement method used.	No	16x	Invoice Number Lc_activity.invoice_no Varchar2(15)
32C - Date and Amount	This field specifies the currency code and amount in a transaction and a corresponding date.	No	Option A- :4!c/[8c]/30x :4!c - Qualifier / - Delimiter [8c] - Issuer Code / - Delimiter 30x - Type	Charges Credited (this is interpreted as a positive amount) Date will be in format YYMMDD The integer part of the Amount must contain at least one digit. A decimal comma ',' is mandatory and is included in the maximum length Lc_activity.amount Number(20,4) Lc_activity.currency_code Varchar2(3) Lc_activity.activity_date Date
32D - Date and Amount	This field specifies the currency code and amount in a transaction and a corresponding date.	No	Option D- 6!n3!a15d 6!n - Date 3!a - Currency 15d - Amount	Charges Debited (this is interpreted as a negative amount) Date will be in format YYMMDD The integer part of the Amount must contain at least one digit. A decimal comma ',' is mandatory and is included in the maximum length Lc_activity.amount Number(20,4) Lc_activity.currency_code Varchar2(3) Lc_activity.activity_date Date
33A - Date and Amount	This field specifies the currency code and amount in a transaction and a corresponding date.	No	Option A- 6!n3!a15d 6!n - Date 3!a - Currency 15d - Amoun	Date, currency, amount of drawing (this is interpreted as a positive amount) Date will be in format YYMMDD The integer part of the Amount must contain at least one digit. A decimal comma ',' is mandatory and is included in the maximum length Lc_activity.amount Number(20,4) Lc_activity.currency_code Varchar2(3) Lc_activity.activity_date Date
33C - Date and Amount	This field specifies the currency code and amount in a transaction and a corresponding date.	No	Option A- 6!n3!a15d 6!n - Date 3!a - Currency 15d - Amount	Date, currency, amount of drawing (this is interpreted as a negative amount) Date will be in format YYMMDD The integer part of the Amount must contain at least one digit. A decimal comma ',' is mandatory and is included in the maximum length. Lc_activity.amount Number(20,4) Lc_activity.currency_code Varchar2(3) Lc_activity.activity_date Date
72 - Sender to Receiver Information	This field specifies instructions or additional information for the Receiver, Intermediary, Account with Institution or Beneficiary Institution.	No	6*35x	Comments Lc_activity.comment Varchar2(2000)
18A - Number of Repetitive Parts	This field specifies the number of times the repetitive part(s)/sequence(s)directly before or after this field appears in the message.	No	Option A- 5n - Number of Repetitive Parts.	Number of 77E's contained within the file.

Integration Contract

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000055 (input)

Output File Layout

Table 19-30 Output File Layout

Record Name	Field Name	Field Type	Default Value	Description
File Header	File Type Record Descriptor	Char(5)	FHEAD	Identifies file record type
	File Line Identifier	Number (10)	Line number in file	ID of current line being created for output file
	File Type Definition	Char(4)	LCCH	Identifies file as &rsquor;Letter of Credit Changes'
	File Create Date	Char(14)	Create date	Current date, formatted to &rsquor;YYYYMMDDHH24MISS'
File Detail	File Type Record Descriptor	Char(5)	FDETL	Identifies file record type
	File Line Sequence Number	Number (10)	Line number in file	ID of current line being created for output file
	Bank Letter of Credit Reference ID	Char(16)	SWIFT tag 20	Bank L/C ID
	Order Number	Number (8)	SWIFT tag 21	Contains the order number that is attached to the letter of credit
	Invoice Number	Char (15)	SWIFT tag 23	Identifies the Issuing Bank's invoice number to which the drawdown refers. This field does not correspond to a RMS invoice number
	Transaction Number	Char (10)	Null	Identifies the amendment number or actual transaction number assigned by the bank
	Transaction Code	Char (6)	If the transaction is a Bank Charge – &rsquor;B' f the transaction is a Drawdown – &rsquor;D'	Identifies the type of transaction that occurred The type is determined by what detail fields are received for the record. If the record contains a 33A this field will get a 'D'. If the record contains either a 32C or 32D this field will get a 'B'
	Amount Sign	Char (1)	SWIFT 33A, 33C SWIFT 32C, 32D	If the record contains a 33A field leave a blank space in this field If the record contains a 33C filed this field should contain a '-' If the record contains a 32C field leave a blank space in this field If the record contains a 32D field this field should contain a '-'
	Amount	Number (20)	SWIFT 33A, 33C SWIFT 32C, 32D	Holds the amount of the activity. This field will have 4 implied decimal places If SWIFT 32C or 32D (Bank Charge) contains a value, use the amount from this field If SWIFT 33A or 33C (Drawdown) contains a value, use the amount from this field
	Currency Code	Char (3)	SWIFT 33A, SWIFT 32C, 32D	Contains the activity's currency code If SWIFT 32C or 32D (Bank Charge) contains a value, use the currency from this field If SWIFT 33A (Drawdown) contains a value, use the currency from this field
	Activity Date	Char (8)	SWIFT 33A, SWIFT 32C, 32D	Holds the date that the activity took place. Formatted to 'YYYYMMDD' If SWIFT 32C or 32D (Bank Charge) contains a value, use the date from this field If SWIFT 33A (Drawdown) contains a value, use the date from this field
	Comments	Char (2000)	SWIFT tag 72	Holds any comments for the activity
File Trailer	File Type Record Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Identifier	Number (10)	Sequential number Created by program.	ID of current line being created for output file
	File Record Counter	Number (10)	NA	This will contain the number of FDETL lines processed

19 Oracle Retail Trade Management

Simplified RTM Configuration

Simplified RTM Batch Program Notes

Batch Design Summary

cednld (Download of Customs Entry Transactions to Brokers)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

File Layout

htsupld (Harmonized Tariff Schedule Upload)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Input File Layout

Original Input File

tranupld (Transportation Upload)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Input File Layout

lcadnld (Letter of Credit Application Download)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

lcmt700 (SWIFT File Conversion - Letter of Credit Application)

Design Overview

Scheduling Constraints

Integration Contract

Output

lcupld (Letter of Credit Confirmation Upload)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Input File Layout

lcmt730 (SWIFT File Conversion - Letter of Credit Confirmation)

Design Overview

Scheduling Constraints

Integration Contract

Input File Layout

Output File Layout

lcmdnld (Letter of Credit Amendment Download)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Input File Layout

lcmt707 (SWIFT File Conversion – Letter of Credit Amendment)

Design Overview

Scheduling Constraints

Integration Contract

Output

lcup798 (Letter of Credit Drawdowns and Charges)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Input File Layout

lcmt798 (SWIFT File Conversion – Letter of Credit Charges and Drawdowns)

Design Overview

Scheduling Constraints

Integration Contract

Input File Layout

Integration Contract

Output File Layout