12 Invoice Matching

RMS stages invoice records to be integrated into the Oracle Retail Invoice Matching (ReIM) product. It stages invoice records for Return To Vendor (RTV), Consignment, Deals, Trade Management, Obligations, and Customs Entry.

Batch Design Summary

The following batch designs are included in this functional area:

• edidlinv (Download of Invoice For ReIM)

• invclshp (Close Aged Shipments to Prevent them from Matching Open Invoices)

• invprg (Purge Aged Invoices)

Note: The batch program, saexpim.pc, has a functional connection to this chapter.

edidlinv (Download of Invoice For ReIM)

Module Name	edidlinv.pc
Description	Download of Invoice For ReIM
Functional Area	Invoice Matching
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS127
Runtime Parameters	NA

Design Overview

The EDIDLINV program extracts invoice information from RMS invoice tables (INVC_HEAD, INVC_DETAIL) to a flat file. This flat file is used by ReIM to upload invoice data into tables such as IM_DOC_HEAD, IM_INVOICE_DETAIL and IM_DOC_NON_MERCH. This batch program is run daily, extracting invoice records whose invoice date falls on the current vdate.

Scheduling Constraints

Table 12-1 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Multi-threaded by location

Restart/Recovery

Restart/recovery for this program is set up at the invoice ID and line sequence level. The program resumes writing to file starting on the next line where the previous process ended.

Key Tables Affected

Table 12-2 Key Tables Affected

Table	Select	Insert	Update	Delete
INVC_HEAD	Yes	No	Yes	No
INVC_DETAIL	Yes	No	No	No
INVC_XREF	Yes	No	No	No
INVC_MERCH_VAT	Yes	No	No	No
INVC_NON_MERCH	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
SUPS	Yes	No	No	No
PARTNER	Yes	No	No	No
VAT_CODE_RATES	Yes	No	No	No
WH	Yes	No	No	No
STORE	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No

Integration Contract

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

Output File Layout

Table 12-3 edidlinv.pc - Output File Layout

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	Record descriptor	Char(5)	FHEAD	Describes file record type. Valid value is FHEAD.k
	Line id	Number(10)	0000000001	Sequential file line number.
	Gentran ID	Char(5)	UPINV	The type of transaction this file represents. Valid value is UPINV
	Current date	Char(14)	NA	Vdate in YYYYMMDDHH24MISS format.
THEAD	Record descriptor	Char(5)	NA	Describes file record type. Valid value is THEAD.
	Line id	Number (10)	NA	Sequential file line number.
	Transaction number	Number(10)	NA	Sequential transaction number. All records within this transaction will also have this transaction number.
	Document Type	Char(6)	NA	Describes the type of document being uploaded. The document type will determine the types of detail information that are valid for the document upload. Invoice types are held on the codes table under a code type of 'IMIT'.
	Vendor Document Number	Char (50)	NA	Vendor's document number.
	Group ID	Char(10)	NULL	The Group ID is an informational field, which can be used to identify groups of invoices that were transmitted to ReIM together. This is not populated by RMS.
	Vendor Type	Char(6)	NA	Type of vendor (either supplier or partner) for this document. Valid values include Bank 'BK', Agent 'AG', Freight Forwarder 'FF', Importer 'IM', Broker 'BR', Factory 'FA', Applicant 'AP', Consolidator 'CO', Consignee 'CN', Supplier Hierarchy Level 1 'S1', Supplier Hierarchy Level 2 'S2', and Supplier Hierarchy Level 3 'S3'. These partner types will be held on the codes table under the code_type 'PTAL'.
	Vendor ID	Char(10)	NA	Vendor for this document.
	Vendor Document Date	Char(14)	NA	Date document was issued by the vendor (in YYYYMMDD24MISSformat).
	Order Number / RTV order number	Number(12)	NA	Merchandising system order number for this document. Required for merchandise invoices and optional for others. This field can also contain the RTV order number if the RTV flag is ’Y'
	Location	Number(10)	NA	Merchandising system location for this document.
	Location Type	Char(1)	NA	Merchandising system location type (either ’S'tore or ’W'arehouse) for this document. Required for merchandise invoices and optional for others.
	Terms	Char(15)	NA	Terms of this document. If terms are not provided, the vendor's default terms will be associated with this record.
	Due Date	Char(14)	NA	Terms of this document. If terms are not provided, the vendor's default terms will be associated with this record.
	Payment method	Char(6)	NA	Method for paying this document.
	Currency code	Char(3)	NA	Currency code for all monetary amounts on this document.
	Exchange rate	Number(12,4)	NA	Exchange rate *10000 (implied 4 decimal places) for conversion of document currency to the primary currency.
	Sign Indicator	Char(1)	NA	Indicates either a positive (+) or a negative (-) total cost amount.
	Total Cost	Number(20,4)	NA	Total document cost *10000 (implied 4 decimal places), including all items and costs on this document. This value is in the document currency.
	Sign Indicator	Char(1)	NA	Indicates either a positive (+) or a negative (-) total vat amount.
	Total VAT Amount	Number(20,4)	NA	Total VAT amount *10000 (implied 4 decimal places), including all items and costs on this document. This value is in the document currency.
	Sign Indicator	Char(1)	NA	Indicates either a positive (+) or a negative (-) total quantity amount.
	Total Quantity	Number(12,4)	NA	Total quantity of items *10000 (implied 4 decimal places) on this document. This value is in EACHES (no other units of measure are supported in ReIM).
	Sign Indicator	Char(1)	NA	Indicates either a positive (+) or a negative (-) total discount amount.
	Total Discount	Number(12,4)	NA	Total discount *10000 (implied 4 decimal places) applied to this document. This value is in the document currency.
	Freight Type	Char(6)	NULL	The freight method for this document. Always blank.
	Paid Ind	Char(1)	NA	Indicates if this document has been paid.
	Multi-Location	Char(1)	NA	Indicates if this invoice goes to multiple locations.
	Merchandise Type	Char(1)	NA	Indicates if this invoice is a consignment invoice.
	Deal Id	Number(10)	NULL	Deal Id from RMS if this invoice is a deal bill back invoice. Always blank
	Deal Detail Id	Char(10)	NULL	Complex Deal Component Id. Always blank from RMS.
	Ref CNR Ext Doc Id	Char(50)	NULL	Reference to the External Id of Credit Note Request associated with this document. Always blank from RMS.
	Ref INV Ext Doc Id	Char(50)	NULL	Reference to the External Id of Invoice associated with this document. Always blank from RMS.
	Deal Approval Indicator	Char(1)	NULL	Indicates if the document on IM_DOC_HEAD is to be created in Approved or Submitted status. Always blank from RMS.
	RTV indicator	Char(1)	NA	Indicates if this invoice is a RTV invoice.
	Custom Document Reference 1	Char(30)	NULL	This optional field is included in the upload file for client customization. No validation will be performed on this field. Always blank from RMS.
	Custom Document Reference 2	Char(30)	NULL	This optional field is included in the upload file for client customization. No validation will be performed on this field. Always blank from RMS.
	Custom Document Reference 3	Char(30)	NULL	This optional field is included in the upload file for client customization. No validation will be performed on this field. Always blank from RMS.
	Custom Document Reference 4	Char(30)	NULL	This optional field is included in the upload file for client customization. No validation will be performed on this field. Always blank from RMS.
	Cross-reference document number	Number(10)	NA	Document that a credit note is for. Blank for all document types other than merchandise invoices.
TDETL	Record descriptor	Char(5)	NA	Describes file record type. Valid value is TDETL
	Line id	Number(10)	NA	Sequential file line number.
	Transaction number	Number(10)	NA	Transaction number for this item detail record.
	UPC	Char(25)	NULL	UPC for this detail record. Valid item number will be retrieved for the UPC. Always blank from RMS.
	UPC Supplement	Number(5)	NULL	Supplement for the UPC. Always blank from RMS.
	Item	Char(25)	NA	Item for this detail record.
	VPN	Char(30)	NULL	Vendor Product Number which can (optionally) be used instead of the Oracle Retail Item Number.
	Sign Indicator	Char(1)	NA	Indicates either a positive (+) or a negative (-) Original Document Quantity amount.
	Original Document Quantity	Number(12,4)	NA	Quantity *10000 (implied 4 decimal places), in EACHES, of the item on this detail record.
	Sign Indicator	Char(1)	NA	Indicates either a positive (+) or a negative (-) Original Unit Cost amount.
	Original Unit cost	Number(20,4)	NA	Unit cost *10000 (implied 4 decimal places), in document currency, of the item on this detail record
	Original VAT Code	Char (6)	NA	VAT code for item.
	Original VAT rate	Number (20,10)	NA	VAT Rate for the VAT code/item.
	Sign Indicator	Char(1)	NA	Indicates either a positive (+) or a negative (-) total allowance. Default is ”+” if no allowances exist for this detail record.
	Total Allowance	Number(20,4)	NA	Sum of allowance details for this item detail record *10000 (implied 4 decimal places). If no allowances exist for this item detail record, value will be 0.
TNMRC	Record descriptor	Char(5)	NA	Describes file record type.
	Line id	Number (10)	NA	Sequential file line number.
	Transaction number	Number(10)	NA	Transaction number for this non- merchandise record.
	Non Merchandise Code	Char(6)	NA	Non-Merchandise code that describes this cost.
	Sign Indicator	Char(1)	NA	Indicates either a positive (+) or a negative (-) Non Merchandise Amt.
	Non Merchandise Amt	Number(20,4)	NA	Cost *10000 (implied 4 decimal places) in the document currency.
	Non Merch VAT Code	Char (6)	NA	VAT Code for Non-Merchandise.
	Non Merch Vat Rate at this VAT code	Number (20, 10)	NA	VAT Rate corresponding to the VAT code.
	Service Performed Indicator	Char(1)	NA	Indicates if a service has actually been performed.
	Store	Number(10)	NA	Store at which the service was performed.
TVATS	File record descriptor	Char(5)		Marks costs at VAT rate line. Valid value is TVATS.
	Line id	Char(10)		Sequential file line number.
	Transaction number	Number(10)		Transaction number for this vat detail record.
	VAT code	Char(6)		VAT code that applies to cost.
	VAT rate	Number (20,10)		VAT Rate corresponding to the VAT code.
	Sign Indicator	Char(1)		Indicates either a positive (+) or a negative (-) Original Document Quantity amount.
	Cost at this VAT code	Number (20,4)		Total amount *10000 (implied 4 decimal places) that must be taxed at the above VAT code.
TTAIL	Record descriptor	Char(5)	NA	Describes file record type. Default value is TTAIL.
	Line id	Number(10)	NA	Sequential file line number.
	Transaction number	Number(10)	NA	Transaction number for the transaction that this record is closing.
	Transaction lines	Number(6)	NA	Total number of detail lines within this transaction.
FTAIL	Record descriptor	Char(5)	NA	Describes file record type.
	Line id	Number(10)	NA	Sequential file line number.
	Number of lines	Number(10)	NA	Total number of lines within this file excluding FHEAD and FTAIL.

Design Assumptions

NA

invclshp (Close Aged Shipments to Prevent them from Matching Open Invoices)

Module Name	invclshp.pc
Description	Close Aged Shipments to Prevent them from Matching Open Invoices
Functional Area	Invoice Matching
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS252
Runtime Parameters	NA

Design Overview

This batch program will close all shipments that have remained open for a specified number of days as defined by the ’Close Open Ship Days' system parameter and are not associated with any open invoices. This will be accomplished by setting the invc_match_status on the SHIPMENT table to ’C'losed.

Scheduling Constraints

Table 12-4 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 12-5 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
ORDHEAD	Yes	No	No	No
SHIPMENT	Yes	No	Yes	No
SHIPSKU	Yes	No	No	No
INVC_HEAD	Yes	No	No	No
INVC_XREF	Yes	No	No	No

Design Assumptions

NA

invprg (Purge Aged Invoices)

Module Name	Invprg.pc
Description	Purge Aged Invoices
Functional Area	Invoice Matching
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS253
Runtime Parameters	NA

Design Overview

This program will purge old posted invoices that have not already been purged by ordprg.pc (which purges invoices associated with an order). This includes all types of invoices—non-merchandise, credit notes, credit note requests, debit memos, and consignment invoices. Regular merchandise invoices will primarily be deleted through ordprg.pc but will be deleted by invprg.pc if they still exist in the system.

The invoices considered are those older than the number of months defined in the purge_config_options.ORDER_HISTORY_MONTHS column.

The age of the invoices will be determined from the match date; if there is no match date, the invoice date will be used.

Note: This program deletes only from the RMS invoice tables preceded with ’INVC'.

Scheduling Constraints

Table 12-6 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly
Scheduling Considerations	The program should run after ordprg.pc
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 12-7 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
PURGE_CONFIG_OPTIONS	Yes	No	No	No
INVC_HEAD	Yes	No	No	Yes
SA_TRAN_HEAD	Yes	No	No	No
SHIPSKU	Yes	No	No	No
INVC_DETAIL	No	No	No	Yes
INVC_NON_MECH	No	No	No	Yes
INVC_MERCH_VAT	No	No	No	Yes
INVC_DETAIL_VAT	No	No	No	Yes
INVC_DISCOUNT	No	No	No	Yes
INVC_TOLERANCE	No	No	No	Yes
ORDLOC_INVC_COST	No	No	Yes	No
INVC_MATCH_QUEUE	No	No	No	Yes

Design Assumptions

NA