10 Invoice Matching

Merchandising and Sales Audit stage invoice records to be integrated to the Invoice Matching solution for returns to vendor (RTV), consignment orders and returns, deals, importing partners, obligations, and customs entry. The programs described in this chapter ensure that open transactions are closed and old data is purged related to this integration.

In addition to the programs listed below, there are two integration programs related to this functional area:

• edidlinv (Download of Invoice For Invoice Matching)

• saexpim (Export DSD and Escheatment from Sales Audit to Invoice Matching)

These are both described in Merchandising Inbound and Outbound Integration Guide.

Program Summary

The following batch designs are included in this functional area:

• Close Aged Shipments to Prevent them from Matching Open Invoices (invc_ship_close_job) - background job

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

• fdrmanifest_job

• Fiscal Document Upload into FDG (fdg_reim_job)

• Purge Aged Invoices (invoice_purge_job)

• Purge Aged Invoices (invprg) - background job

• Purge Obsoleted Documents in FDG (fdg_purge_job) - background job

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

Module Name	invc_ship_close_job
Description	Close Aged Shipments to Prevent them from Matching Open Invoices
Functional Area	Invoice Matching
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from order-shipment and order header tables based on its purge criteria. The Close Open Ship Days parameter will determine number of days that all shipment records that have remained opened and not associated with any open invoices. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will update the records from order-shipment table by setting the invoice match status to 'C'losed. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 10-1 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_INVC_CLOSE_SHIP_STG	Yes	Yes	No	Yes
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

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

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
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

fdrmanifest_job

Module Name	fdrmanifest_job
Description	The script identifies invoices that should be sent to SEFAZ for manifest.
Functional Area	Rfm
Module Type	Admin – Ad hoc
Module Technology	Background Processing
Catalog ID
Wrapper Script	fdrmanifest.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch calls the FDR process to identify invoices that need to be sent to the government for receipt manifests. The batch also accounts for the necessary steps to complete the manifest process, such as verifying the accuracy of the data, executing the necessary validations, and applying the changes to the system. The batch execution is complete once the manifest process is complete, whether it is successful or unsuccessful.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
RFM_CODE_DETAIL	Yes	No	No	No
RFM_CODE_DETAIL_TL	Yes	No	No	No
RFM_WFLW_SET	Yes	No	No	No
RFM_WFLW_EVE_LOG	Yes	No	No	No
RFM_WFLW_STA_LOG	Yes	Yes	No	No
RFM_FLX_DOC_FDR	Yes	Yes	Yes	No
RFM_FLX_DOC_FDR_DATA_XML	Yes	Yes	Yes	Yes
RFM_WFLW_SET_VAR	Yes	No	No	No
RFM_WFLW_EXEC_VAR	Yes	No	No	No
RFM_WFLW_SET_ACTION	Yes	No	No	No
RFM_WFLW_ERR	No	Yes	No	No
RFM_WFLW_EVE_LOG	No	Yes	No	No
RFM_FLX_SET_CONTENT	Yes	No	No	No
RFM_FLX_DOC_FDR_HIST	Yes	Yes	Yes	No
RFM_FLX_DOC_FDR_DATA_JSON	No	Yes	Yes	No
RFM_FLX_DOC_FDR_DATA_XML_HIST	No	Yes	Yes	No
RFM_FLX_DOC_FDR_KEYS	Yes	Yes	Yes	No

Design Assumptions

N/A

Fiscal Document Upload into FDG (fdg_reim_job)

Module Name	fdg_reim_job
Description	The script calls the FDG to migrate data between ReIM and FDG.
Functional Area	Rfm
Module Type	Admin – Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	fdg_reim_batch_sql.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The purpose of this process is to migrate data from ReIM tables to FDG tables and then follow the existing process in the FDG module to finish the migration. ReIM will be the owner of the data to be stored in their staging tables, which will be consumed by batch, and will begin the migration process to FDG.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
IM_FDG_DOC_HEAD	Yes	No	Yes	Yes
IM_FDG_DOC_ETT	Yes	No	No	Yes
IM_FDG_DOC_DTL	Yes	No	No	Yes
IM_FDG_DOC_DTL_PACK_COMP	Yes	No	No	Yes
IM_FDG_DOC_REF	Yes	No	No	Yes
IM_FDG_DOC_NON_MERCH	Yes	No	No	Yes
IM_FDG_DOC_TAX	Yes	No	No	Yes
IM_FDG_DOC_TEXT	Yes	No	No	Yes
IM_FDG_DOC_EXT	Yes	No	No	Yes
SVC_FDG_HDR	Yes	Yes	Yes	Yes
SVC_FDG_ETT	Yes	Yes	No	Yes
SVC_FDG_DTL	Yes	Yes	No	Yes
SVC_FDG_DTL_PACK	Yes	Yes	No	Yes
SVC_FDG_REF	Yes	Yes	No	Yes
SVC_FDG_NON_MERCH	Yes	Yes	No	Yes
SVC_FDG_TAX	Yes	Yes	No	Yes
SVC_FDG_TEXT	Yes	Yes	No	Yes
SVC_FDG_EXT	Yes	Yes	No	Yes
FDG_HDR	No	Yes	No	No
FDG_ETT	No	Yes	No	No
FDG_DTL	No	Yes	No	No
FDG_REF	No	Yes	No	No
FDG_NON_MERCH	No	Yes	No	No
FDG_TAX	No	Yes	No	No
FDG_DTL_PACK	No	Yes	No	No
FDG_TEXT	No	Yes	No	No
FDG_EXT	No	Yes	No	No
FDG_ERROR	No	Yes	No	No

Design Assumptions

N/A

Purge Aged Invoices (invoice_purge_job)

Module Name	invoice_purge_job
Description	Purge Aged Invoices
Functional Area	Invoice Matching
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from invoice header table based on its purge criteria from system parameter settings. The Order History Months parameter will determine the number of months older than month ages between current date and invoice match date, invoice date (if match date is not available). These old posted invoices that have not already been purged by Order Purge Job (invoices associated to an order) will be included for deletion. 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 order purge job but will be deleted by this job if they still exist in the system. This program deletes only from the RMFCS invoice tables preceded with 'INVC'. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the records from invoice-related tables by calling INVC_SQL.DELETE_INVC. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 10-2 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
PURGE_CONFIG_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_INVOICE_PURGE_STG	Yes	Yes	No	Yes
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

Purge Aged Invoices (invprg)

Module Name	Invprg.pc
Description	Purge Aged Invoices
Functional Area	Invoice Matching
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS253
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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 the order purge batch (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 Merchandising invoice tables preceded with ‘INVC'.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Obsoleted Documents in FDG (fdg_purge_job)

Module Name	fdg_purge_job
Description	Purge obsoleted documents
Functional Area	Financial
Module Type	Admin – Ad hoc
Module Technology	Background Processing
Catalog ID
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job finds all fiscal document records on FDG tables that have passed retention days based on FDG system option FDG_SYSTEM_OPTIONS.DOCUMENT_PURGE_DAYS and purges the records.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
B8D_PROCESS_CONFIG	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
FDG_SYSTEM_OPTIONS	Yes	No	No	No
B8D_FDG_PURGE_STG	Yes	No	Yes	Yes
FDG_DTL_PACK	Yes	No	No	Yes
FDG_ERROR	Yes	No	No	Yes
FDG_EXT	Yes	No	No	Yes
FDG_TAX	Yes	No	No	Yes
FDG_REF	Yes	No	No	Yes
FDG_NON_MERCH	Yes	No	No	Yes
FDG_ETT	Yes	No	No	Yes
FDG_DTL	Yes	No	No	Yes
FDG_HDR	Yes	No	No	Yes
FDG_DTL_PACK	Yes	No	No	No
FDG_ERROR_PRG_HIST	Yes	No	No	No
FDG_EXT_PRG_HIST	Yes	No	No	No
FDG_TAX_PRG_HIST	Yes	No	No	No
FDG_REF_PRG_HIST	Yes	No	No	No
FDG_TEXT_PRG_HIST	Yes	No	No	No
FDG_NON_MERCH_PRG_HIST	Yes	No	No	No
FDG_ETT_PRG_HIST	Yes	No	No	No
FDG_DTL_PRG_HIST	Yes	No	No	No
FDG_HDR_PRG_HIST	Yes	No	No	No