10 Invoice Matching

Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

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

• Invoice Matching Accounts Payable Publish (fif_ap_publish)

• Invoice Matching Financial Data Publish (fif_im_gl_publish)

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)

invclshp.pc

Module Name

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 Description Functional Area Module Type Module Technology Catalog ID Wrapper Script

fdg_reim_job The script calls the FDG to migrate data between ReIM and FDG.

Rfm Admin – Ad hoc Background Processing N/A 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_HE AD	Yes	No	Yes	Yes
IM_FDG_DOC_ET T	Yes	No	No	Yes
IM_FDG_DOC_DT L	Yes	No	No	Yes
IM_FDG_DOC_DT L_PACK_COMP	Yes	No	No	Yes
IM_FDG_DOC_RE F	Yes	No	No	Yes
IM_FDG_DOC_NO N_MERCH	Yes	No	No	Yes
IM_FDG_DOC_TA X	Yes	No	No	Yes
IM_FDG_DOC_TE XT	Yes	No	No	Yes
IM_FDG_DOC_EX T	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_P ACK	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

Table	Select	Insert	Update	Delete
FDG_REF	No	Yes	No	No
FDG_NON_MERC	No	Yes	No	No
H
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-nonmerchandise, 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

Module Name	ff_ap_publish.ksh
Description	Invoice Matching Accounts Payable Publish
Functional Area	General Ledger
Module Type	Business Processing
Module Technology	ksh
Catalog ID
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will first call FIF_AP_PUBLISH_SQL.PROCESS_HEAD to load records from IM_AP_STAGE_HEAD for the current date to the FIF_AP_HEAD_PUBLISH table. Then, it will call FIF_AP_PUBLISH_SQL.PROCESS_DETAIL to load records from IM_AP_STAGE_DETAIL for the current date to the FIF_AP_DETAIL_PUBLISH table.

If an external reference ID is not defined for the following fields in the corresponding RMS table, the program will raise an error:

• Vendor (SUPS table)

• Third Party Payee (SUPS table)

• Best Terms (TERMS_HEAD table)

• Org Unit (ORG_UNIT table)

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_PLSQL_BATCH_CONFIG	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
IM_AP_STAGE_HEAD	Yes	No	No	No
IM_AP_STAGE_DETAIL	Yes	No	No	No
FIF_AP_HEAD_PUBLISH	Yes	Yes	No	Yes
FIF_AP_DETAIL_PUBLISH	Yes	Yes	No	Yes
FOUNDATION_UNIT_OPTIONS	Yes	No	No	No
SUPS	Yes	No	No	No
TERMS_HEAD	Yes	No	No	No

Invoice Matching Financial Data Publish (fif_im_gl_publish)

Module Name	ff_im_gl_publish.ksh
Description	Invoice Matching Financial Data Publish
Functional Area	General Ledger
Module Type	Business Processing
Module Technology	ksh
Catalog ID
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will call a program FIF_GL_PUBLISH_SQL.PROCESS_IM that will load records from IM_FINANCIALS_STAGE for the current date to the FIF_GL_PUBLISH table.

If the set of books from IM_FINANCIALS_STAGE does not have an external reference ID defined in FIF_GL_SETUP, this program will raise an error.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_PLSQL_BATCH_CONFIG	Yes	No	No	No
IM_FINANCIALS_STAGE	Yes	No	No	No
FIF_GL_PUBLISH	Yes	Yes	No	Yes
FIF_GL_SETUP	Yes	No	No	No

---

In this guide

• Guide: Batch Operations Guide
• Previous: 1 Introduction
• Next: 11 Replenishment

Related chapters

• 6 Scheduled Integration — Inbound and Outbound Integration Guide · shares CODE_DETAIL, FDG_DTL, FDG_DTL_PACK, FDG_ERROR
• 18 Franchise Management — Batch Operations Guide · shares RFM_WFLW_ERR, RFM_WFLW_EVE_LOG, RFM_WFLW_EXEC_VAR, RFM_WFLW_SET
• 5 ReSTful Web Services — Inbound and Outbound Integration Guide · shares CODE_DETAIL, FDG_DTL, FDG_DTL_PACK, FDG_ERROR
• 13 Transfers, Allocation, and RTV — Batch Operations Guide · shares INVC_DETAIL, INVC_DETAIL_VAT, INVC_DISCOUNT, INVC_HEAD