| Oracle® Invoice Matching Operations Guide Release 14.1 E57809-04 |
|
 Previous |
 Next |
This chapter describes the integration between Oracle Retail systems and PeopleSoft Financials (including PeopleSoft General Ledger and PeopleSoft Payables), as developed and supported by Oracle Retail Financial Integration (ORFI).
When the option to integrate is chosen, the selected information is shared between the systems. Integration and validation services are in place to ensure the shared data matches.
|
Note: This chapter addresses the points within Oracle Retail systems that are essential to integration. For more information about the entire integration process, including mapping to data and settings, see the ORFI document, Oracle Retail Financial Integration for Oracle Retail Merchandise Operations Management and Oracle Financials Implementation Guide. For more information about Web services, see the following chapters in the Oracle Retail Merchandising System Operations Guide, Volume 2: "Service Provider Implementations API Designs" and "Web Services." |
The following Oracle Retail applications are included in the integration covered by this chapter:
Oracle Retail Merchandising System (RMS)
Oracle Retail Sales Audit (ReSA)
Oracle Retail Invoice Matching (ReIM)
Oracle Retail Integration Bus (RIB)
The option to integrate must be selected during initial set up of the RMS system.
ReIM accesses RMS to determine if integration is active. Initial set up of RMS must occur prior to the integration of ReIM.
The URLs for the RFI Web services that are a necessary for this integration must be maintained in the RETAIL_SERVICE_REPORT_URL table and in the ReIM integration.properties file.
Real time account validation is done only when the financial integration with PeopleSoft Financials is ON.
Partners must be set up as suppliers in PeopleSoft. Then the partner must be manually set up in RMS using the RMS Supplier ID that was generated when the PeopleSoft supplier was interfaced to Oracle Retail. Partner functionality within RMS and ReIM can then proceed normally. The RMS supplier generated as part of this process is not used.
Payment terms are automatically maintained.
Freight terms are manually maintained.
The Location ID field is restricted to eight characters, to accommodate PeopleSoft Operating Unit, which has a maximum of eight characters.
The Ext_Doc_ID field is restricted to 30 characters, because the corresponding PeopleSoft field has only 30 characters. Characters beyond 30 are truncated.
RMS allows for four decimals, and PeopleSoft allows only three. Truncation may occur when data is passed to PeopleSoft Enterprise Financials.
ReIM values in the IM_CURRENCY_LOCALE are restricted to three decimals, because the corresponding PeopleSoft Enterprise Financials field can accept no more than three decimal positions.
Integration of Oracle Retail applications and PeopleSoft Financials relies on synchronization of essential data, such as currency exchange rates, suppliers, and payment terns. Through careful discussions, the users of both systems determine the common codes and descriptions that will best serve their business needs.
Once agreement is reached, this information is set up and maintained. Depending on the volume, some shared information is set up in Oracle Retail applications or in PeopleSoft and electronically transferred to the other systems. Otherwise, shared information is set up manually within each system, and the user of both systems must ensure that the code and the description match.
This section describes setup considerations for the RMS data.
This system_option indicates that the Oracle Retail system is integrated with a financial system:
FINANCIAL_AP=A
A value of A indicates that the financial system to which RMS is interfaced is PeopleSoft through Oracle Retail Financial Integration (ORFI).
GL_ROLL_UP can be D/S/C
SUPPLIER_SITE_IND = Y
ORG_UNIT_IND = Y
Use the Organizational Unit window (RMS Start Menu > Control > Setup > Org Unit >Edit) to define organizational units in RMS that match those being setup in PeopleSoft. When an organizational unit is entered in RMS, the valid organizational units are those associated with the Set Of Books (SOB) that is being used for the general ledger interface.
Currency exchange rate is used to translate the monetary value of one currency in terms of another. Depending on business needs, a Currency Exchange Rate Type of Operational or Consolidation is selected for use in all transactions.
This value is set up manually in RMS and mapped to PeopleSoft through the Currency Exchange Type mapping window. Currency Exchange Rate data is owned by PeopleSoft General Ledger and updates are sent to Oracle Retail applications.
Determine the Exchange Type being sent by PeopleSoft General Ledger (for example, Consolidation or Operational) that you want RMS to use. Update the
FIF_CURRENCY_XREF for mapping the external exchange type being sent by PeopleSoft General Ledger with RMS Exchange Type.
For example, for Consolidation and Operational exchange types, the
FIF_CURRENCY_XREF table holds the following entries:
Within RMS, supplier information (such as Order From and Remit To addresses) is used for generating the purchase orders. PeopleSoft Payables uses supplier information for payment generation. It is important that this information is synchronized.
Suppliers are created in PeopleSoft Payables and exported to RMS. When FINANCIAL_AP is set to A, suppliers cannot be created using the RMS forms. However, after the supplier exists in RMS, all data values for the supplier (except supplier name and status) continue to be updated using the RMS forms. The association of supplier sites to organization units is accessed only in view mode through RMS forms. One supplier site per supplier organization unit combination can be marked as primary payment site.
Where SYSTEM_OPTIONS.FINANCIAL_AP is A, disable auto generate supplier/partner numbers and associated check boxes.
|
Note: Supplier information is created, updated and inactivated only in PeopleSoft Payables. This information is transferred from PeopleSoft Payables to the participating Oracle Retail applications, where additional retail-specific attributes may be maintained. |
When country codes are defined and seeded in RMS, ensure that country codes are mapped to PeopleSoft country codes through RFI DVM mapping. The following is an example of RFI DVM Mapping (Table RFI_XREF_DVM, available in RFI schema in Retail.
The financial calendar within Oracle Retail systems is manually set up and maintained separately from the PeopleSoft General Ledger financial calendar.
A freight term is an agreement between the retailer and a supplier regarding transportation charges for goods delivered by the supplier. Freight terms are used by RMS as purchase orders are generated.
Within the RMS system, freight terms are set up and maintained manually. They also are maintained in PeopleSoft Payables.
Currency exchange rates are created and updated in PeopleSoft General Ledger and exported to RMS. Changes to Retail currency exchange rates are not propagated to PeopleSoft General Ledger. Payment terms set up in PeopleSoft are propagated to RMS but changes to payment terms in RMS are not propagated back to PeopleSoft.
The data concepts of Org Units and Site IDs in RMS mirror the data maintained in PeopleSoft. RMS forms are used to manage and view Oracle Org Units and Site IDs. The RMS windows for Store and Warehouse maintenance allows for the association of each store and warehouse with an Org Unit. The following is an example of the Organizational Unit form:
The organizational unit is found in the Store Maintenance and Warehouse forms, which allow the PeopleSoft operating unit to be associated with the Store or Warehouse. When RMS is set up for single-channel operation, the organizational unit is set at the physical warehouse level. When RMS is set up for multi-channel operation, the organizational unit is set up at the virtual warehouse level. Financial sales audit and inventory information can then be identified through interface routines and posted to the appropriate general ledger accounts. An organizational unit must be designated for each Store and Warehouse location in the RMS.
The following are examples of the Store Maintenance and Warehouse Maintenance forms:
For RMS and ReSA, manual setup is required for validating the chart of accounts. Valid chart of accounts are created and stored in general ledger cross reference tables. Once the validation is completed, transaction data can be assigned to specific account codes.
Ongoing maintenance of the chart of accounts information (such as adding, changing, or deleting chart of accounts) requires re-validation. In this regard, PeopleSoft General Ledger is the system of record, as it is used to verify the chart of accounts used by Oracle Retail applications. When these applications send a chart of accounts for validation, PeopleSoft General Ledgers issues a message with:
Valid or invalid status
Response date
Chart of accounts
The RMS table, FIF_GL_SETUP, stores the PeopleSoft Set of Books IDs to post financial information. This table must be setup manually after Set of Books IDs are determined. Where system indicator Multiple Set of Books ID is set to N, FIF_GL_SETUP must hold a single Set of Books (SOB) record.
The Set of Books IDs is associated with the chart of accounts when setting up general ledger cross reference.
Navigate: RMS Start Menu > Finance> GL Cross Reference. The General Ledger Search window opens. Map Chart of Accounts to department, Class, Subclass, Set Of Books, location, and transaction codes using the GL cross reference form in RMS.
Navigate: ReSA main menu > Action > Sales Audit > Control > Setup > GL Account Maintenance. The General Ledger Search Form window opens. Where SYSTEM_OPTIONS.FINANCIAL_AP is A, the form requires the entry of valid segment combinations.
Retail web services table, RETAIL_SERVICE_REPORT_URL, must be updated with appropriate URLs to integrate with PeopleSoft Enterprise Financials.
The records in the table for Services (indicated by RS_TYPE=S) for Account Validation (RAV) and Drill Forward (RDF), must be updated with the URL information from AIA where the services are hosted.
|
Note: If Web services are secure, then the SYS_ACCOUNT column must be populated with authentication information in the form of user name/password. |
The records in the table for Reports (indicated by RS_TYPE=R) for both RMS and ReIM reports , must be updated with the URL information from the BIP Server where the reports are hosted.
Because PeopleSoft Enterprise Financials uses only three decimals, the transactions generated by the Oracle Retail ReIM application must not include more than three decimals.
Update im_currency_locale set currency_cost_dec=3.
Prerequisite: The currency_rates table in RMS should be loaded initially by PeopleSoft Enterprise Financials.
The chart of accounts is set up manually in Oracle Retail applications and in PeopleSoft General Ledger. All account combinations are set up in each Set Of Books. The following is an example of the GL Cross Reference screen:
|
Note: The Chart of accounts is updated in Oracle Retail applications only after the account is validated through PeopleSoft General Ledger. |
The retailer determines how many segments are populated. Up to 20 account segments can be specified. The following is an example of how segments are mapped between the ReIM transaction table and PeopleSoft General Ledger:
Table 8-3
| ReIM Segments | PeopleSoft General Ledger Chart of Accounts |
|---|---|
|
Segment 1 |
ACCOUNT |
|
Segment 2 |
ALTACCT |
|
Segment 3 |
DEPTID |
|
Segment 4 |
OPERATING_UNIT |
|
Segment 5 |
PRODUCT |
|
Segment 6 |
FUND_CODE |
|
Segment 7 |
CLASS_FLD |
|
Segment 8 |
PROGRAM_CODE |
|
Segment 9 |
BUDGET_REF |
|
Segment 10 |
AFFILIATE |
|
Segment 11 |
AFFILIATE_INTRA1 |
|
Segment 12 |
AFFILIATE_INTRA2 |
|
Segment 13 |
CHARTFIELD1 |
|
Segment 14 |
CHARTFIELD2 |
|
Segment 15 |
CHARTFIELD3 |
|
Segment 16 |
RESOURCE_TYPE |
|
Segment 17 |
RESOURCE_CATEGORY |
|
Segment 18 |
RESOURCE_SUB_CAT |
|
Segment 19 |
BUSINESS_UNIT_PC |
|
Segment 20 |
PROJECT_ID |
If any one of the values in the 20 segments does not match the PeopleSoft General Ledger, the account combination is considered as invalid. The following error message is issued to the user: "Account combination is invalid in the financial system."
Segments 1 and 2 may be set up as dynamic at the Location level, or Segments 4 and 5 can be dynamic at the Department and Class level respectively. Segments defined as dynamic are allowed to be null for certain types of Basic Transaction or Reason Code cross-reference types. When a segment is null, the segment is assigned dynamically when transactions are posted. (Non-dynamic segments cannot be blank.) Validation applies to the segment combination, not to individual segments.
|
Note: For Tran code TAP, each segment must have a value regardless of whether the segment is dynamic. |
The initial load for ReIM is run by PeopleSoft and includes the following information:
Suppliers
Currency Rates
Payment Terms
|
Note: The view, mv_currency_conversion_rates should be refreshed once the initial loads of currencies from PeopleSoft General Ledger are loaded to ReIM. |
To accommodate integration, the integration.properties file within ReIM must be updated with the appropriate URLs for the account validation and drill forward Web services, as listed below:
|
Note: Drill forward functionality is applicable for PeopleSoft – RMS and ReIM integration. Drill backward functionality is only applicable for PeopleSoft – RMS integration. |
#webservice WSDL URL for drill forward webservice.financial.drill.forward.wsdl=@webservice.drill.forward.wsdl@ webservice.financial.drill.forward.url.targetnamespace= webservice.financial.drill.forward.targetsystem= #webservice WSDL URL for account validation webservice.financial.account.validation=@webservice.account.validation@ webservice.financial.account.validation.namespace=http://www.oracle.com/retail/fin/integration/services/GlAccountValidationService/v1 webservice.financial.account.validation.local.code=GlAccountValidationService #webservice username and password for account validation webservice.financial.account.validation.username=@webservice.account.validation.username@ webservice.financial.account.validation.password=@webservice.account.validation.password @
Reports are created by Business Intelligence Publisher for the following:
The URL for each report must be updated in the table, retail_service_report_url. The following table provides sample URLs.
Reports are created by Business Intelligence Publisher for the following:
Merchandise Invoice
Non-Merchandise Invoice
Credit Note
Credit Memo
Debit Memo
Receipt Write-Off
The URL for each report must be updated in the table, retail_service_report_url. The following table provides sample URLs:
Table 8-4
| Document Type | Report Name | Sample Report URL |
|---|---|---|
|
MRCHI |
Merchandise invoice document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/invreport.xdo |
|
NMRCHI |
Non-Merchandise invoice document Report |
http://hostname:portno /xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/invreport.xdo |
|
CRDNT |
Credit Note document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/crnreport.xdo |
|
CRDMEC |
Credit Memo cost document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/memoreport.xdo |
|
CRDMEQ |
Credit Memo quantity document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/memoreport.xdo |
|
DEBMEC |
Debit Memo cost document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/imemoreport.xdo |
|
DEBMEQ |
Debit Memo quantity document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/memoreport.xdo |
|
DEBMEV |
Debit Memo Tax document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/memoreport.xdo |
|
RWO |
Receipt Write Off document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/rworeport.xdo |
Integration to PeopleSoft General Ledger includes a number of transactions, as described below.
The TRANS_AMOUNT field in the im_financial_stage table stores the value of the journal entry to be posted to PeopleSoft General Ledger. (The currency for the calculated amount is the currency assigned to the transaction.) The TRANS_AMOUNT value is calculated as follows:
Table 8-5
| Row Description | DEBIT_CREDIT_IND | TRANS_AMOUNT Value |
|---|---|---|
|
Normal |
Debit |
Transaction Amount |
|
Normal |
Credit |
(-1) * Transaction Amount |
|
VAT |
Debit |
Transaction Amount * Tax Rate |
|
VAT |
Credit |
(-1) * Transaction Amount * Tax Rate |
|
Note: Transaction Amount is taken from the database column, IM_FINANCIALS_STAGE.AMOUNT. |
A staging table accommodates the outgoing transfer of data. The reference key assigned to each document or receipt is used to find data on this table.
Outgoing Data
Table 8-6
| From | To | Transactions |
|---|---|---|
|
ReIM |
PeopleSoft Accounts Payable |
Invoices Debit Memos Credit Memos Credit Notes |
|
ReIM |
PeopleSoft General Ledger |
General Ledger accounting entries resulting from the Invoice Matching process, including: Pre-paid invoices Receipt Write-offs |
|
RMS |
PeopleSoft General Ledger |
Accounting entry data (potentially very high volume) |
|
ReSA |
PeopleSoft General Ledger |
Accounting entry data (potentially very high volume) |
Valid accounts are stored in the ReIM table, IM_VALID_ACCOUNTS, which includes the Set of Books ID (sob_id) and 20 segments. An ORFI Web service validates accounts against the PeopleSoft Enterprise Financials system. Valid accounts are posted to IM_VALID_ACCOUNTS; invalid accounts are posted to IM_POSTING_DOC_ERROR. The following steps describe the validation process:
The ReIM system invokes the account validation Web service to validate the account. (A URL for the ORFI Web service is configured in the integration.properties file.)
The posting batch job checks the accounts to be posted against the IM_VALID_ACCOUNTS table.
If the account entries are in the table, the transaction is posted to the G/L or AP tables.
If the account does not exist in the table, a collection of accounts is built. These collected accounts are validated against the PeopleSoft Enterprise Financials system, and a status is returned.
If the status of the collected accounts is valid, the accounts are inserted in the IM_VALID_ACCOUNTS table, and the transactions are posted to the staging tables.
If the status of the accounts is NOT valid, the entire collection is flagged as errors, and transactions are postedto IM_POSTING_DOC_ERROR.
|
Note: ReIM completes the first level of account validation and posts the transaction to staging tables. It is assumed the second level of account validation is done at the end of the extraction process (where transactions are moved from ReIM staging tables to PeopleSoft). If account validation fails at this point, Oracle Data Integrator (ODI) or PeopleSoft must change the account information before transactions are loaded to PeopleSoft, and the account change must be communicated to ReIM . |
As account information is changed in the PeopleSoft system, the same changes are communicated to, and manually completed in, the ReIM system. After ReIM is updated accordingly, the AccountPurge Batch is run to clear the valid accounts maintained locally in ReIM.
The AccountPurge Batch can clear all valid accounts in the IM_VALID_ACCOUNTS table or only those that are considered updates in PeopleSoft.
Usage:
AccountPurge batch-alias-name PURGE [ALL | <Accounts>]
Where
The first argument is batch alias name.
The second argument is the word PURGE.
The third argument is either ALL or specific accounts to be deleted from the local table.
Drill back and drill forward functionality uses Reference ID to locate documents and receipts. A Reference ID is a combination of document type and document (or receipt) ID, as illustrated in the table below:
Table 8-7
| Type | Doc ID | Receipt ID | Reference ID |
|---|---|---|---|
|
Merchandise Invoice |
101 |
Null |
MRCHI#101 |
|
Non-Merchandise Invoice |
102 |
Null |
NMRCHI#102 |
|
Receipt |
Null |
103 |
RECEIPT#103 |
For documents, the Resolution Posting Batch program builds the Reference ID using the standard, Document Type + DeLimiter + Doc_id. For receipts, the program builds the Reference ID using the standard, Document Type + DeLimiter + Receipt_id.
To enable drill down functionality, Reference IDs are loaded to staging tables. FinancialsAPStageDao and FinancialsGLStageDao are populated, as are IM_RWO_SHIPMENT_HIST and IM_RWO_SHIPSKU_HIST.
Drilling back allows users to view the source of posted PeopleSoft transactions that originated in Oracle Retail systems (from a voucher to an invoice, for example).
When drilling back from PeopleSoft Enterprise Financials, users are not directed to an actual screen within RMS, ReIM or ReSA. Rather, a retail Web service generates and launches a URL to a BI Publisher report. The report contains the information that typically appears on the appropriate retail screen.
Depending on the information requested by the user, PeopleSoft invokes web service in the ORFI layer which in turn calls the report locator service available in RMS. This service returns back BIP report reference URL which is passed back to PeopleSoft and report gets launched in browser window.
Information from the reference key determines what kind of report URL to issue. For example, if the retail key has a prefix of ReIM, the ReIM_REPORT_URL function is called, else the RMS_REPORT_URL function is called to retrieve the appropriate report URL. If the key does not match any key in the retail systems, an error message is launched.
The following function determines which RMS report to return to the user:
RMS_REPORT_URL() -
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE
O_rpt_url IN OUT RETAIL_SERVICE_REPORT_URL.URL%TYPE
I_ref_key IN KEY_MAP_GL.REFERENCE_TRACE_ID%TYPE
The appropriate report URL is found and issued as follows:
The ref_trace_type is found on KEY_MAP_GL by matching I_ref_key with the KEY_MAP_GL.REFERENCE_TRACE_ID column.
When ref type is determined, the re_trace_type is used to find the appropriate report URL on the RETAIL_SERVICE_REPORT_URL table.
The value of I_ref_key is appended to the end of the URL retrieved from the table.
The URL is sent back to the calling function.
If I_ref_key does not exist on KEY_MAP_GL, an error message is sent back to the calling function.
The following drill back options are available for viewing information within the ReIM system:
Using Document ID, users can drill back to ReIM to view information related to a voucher or payment. The report includes information from im_doc_head and im_invoice_detail, the same data shown on the Document Maintenance Header screen within ReIM.
Using the Receipt ID, users can drill back to view information from the Receipt Write-off History screen. Receipt write-offs occur either when an open receipt is closed in ReIM or if a receipt is purged in RMS before it is fully matched. Details come from the IM_RWO_SHIPMENT_HIST and IM_RWO_SHIPSKU_HIST tables.
The function below determines which of the two ReIM reports to return to the user:
REIM_REPORT_URL() -
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE
O_rpt_url IN OUT RETAIL_SERVICE_REPORT_URL.URL%TYPE
I_ref_key IN KEY_MAP_GL.REFERENCE_TRACE_ID%TYPE
The I_ref_key contains the reference ID, which ultimately determines the type of report required. The appropriate BI Publisher report URL is found on the RETAIL_SERVICE_REPORT_URL table.
In general, if the reference ID has a prefix of RECEIPT, the report type (RS_CODE) is RCPT. Otherwise, the report type is DOC. For example:
The following is an example of a BI Publisher URL that is generated upon drilling back to PeopleSoft Enterprise Financials for information on an invoice in ReIM, using Document ID as the search parameter:
http://mspdev6970vip:7777/BIPublisher/Guest/ReIM/13.0.3/doc/tsf_det.xdo ?doc_id=101
Where
http://mspdev6970vip:7777/BIPublisher = the BI Publisher application server address and port
Guest/ReIM/13.0.3 = the directory/folder location
doc/tsf_det.xdo ? = report name (Document Report)
doc_id=101 = the parameter name and value (Document ID 101)
The following is an example of an Oracle Business Intelligence Publisher URL that is generated upon drilling back to PeopleSoft Enterprise Financials for information on an invoice in ReIM, using Receipt ID as the search parameter:
http://mspdev6970vip:7777/BIPublisher/Guest/ReIM/13.0.3/doc/tsf_det.xdo ?receipt_id=101
Where
http://mspdev6970vip:7777/BIPublisher = the BI Publisher application server address and port
Guest/ReIM/13.0.3 = the directory/folder location
doc/tsf_det.xdo ? = report name (Receipt Report)
receipt_id=101 = the parameter name and value (Receipt ID 101)
Drilling forward allows users to see detailed information about retail transactions that have been posted to PeopleSoft Enterprise Financials. When drilling forward, users are directed to selected "view-only" screens.
The following forms may be used to drill forward from RMS/ReSA:
RMS StartMenu->Finance->Transaction Data View (trandata.fmb)
RMS StartMenu->Ordering->Fixed Deals->Fixed Deal Transaction Data View (fdltrandata.fmb)
RMS StartMenu->Action->Sales Audit->Sales Audit Transaction Data View (satrandata.fmb)
For drilling forward, the ORFI Web service uses the Invoice ID and Accounting Entry parameters. The ReIM system uses these parameters together as the Reference ID.
From the Document Maintenance screen, users can drill forward to PeopleSoft Enterprise Financials accounts payable to view voucher and payment status. The information is displayed on a read-only Payment Doc Status Inquiry screen. Drill forward access to the accounts payable system is available only for pre-paid invoices (but not for manually pre-paid invoices).
To drill down to the payables/ledger screens, the user invokes the Web service as follows:
Invoice ID/Accounting Entry parameter = Reference ID
From the Receipt Write-off History screen in ReIM, users can drill forward to the PeopleSoft G/L system to view the status of related journal entries. Drill forward access to the G/L system is available only for pre-paid and manually pre-paid invoices.
|
Note: For more information on drilling forward, see the Oracle Retail Financial Integration for Oracle Retail Merchandise Operations Management and Oracle Financials Suite (E-Business Suite Financials or PeopleSoft Financials). |
