| Oracle® Application Integration Architecture Oracle Financial Operations Control Integration Pack Implementation Guide for Oracle Retail Merchandise Operations Management and PeopleSoft Enterprise Financials Release 3.1 Part Number E20500-03 |
|
|
PDF · Mobi · ePub |
This chapter provides an overview of the process integration for Retail Merchandise Procure to Pay between Oracle Retail Invoice Matching (Oracle ReIM) and PeopleSoft Payables and discusses how to perform setup tasks, configure and generate data in Oracle Retail, pick up and transform the data, and configure and run the process integration for retail merchandise procure to pay.
This chapter includes the following sections:
Section 5.1, "Process Integration for Retail Merchandise Procure to Pay Overview"
Section 5.3, "Configuring and Generating Data in Oracle Retail"
Section 5.5, "Configuring and Running the Process Integration for Retail Merchandise Procure to Pay"
Oracle ReIM is the source of matched invoices, credit notes, debit memos, and rebates. PeopleSoft Payables requires these details for payment to suppliers. Invoices from suppliers for retail merchandise are matched to the original purchase order (PO) for the merchandise and the receipt of the merchandise by the retailer. A proper match of invoice, PO, and receipt trigger the payment authorization of the supplier's invoice. Invoices may be authorized for payment before receipt of goods for which prepayment is required. When the authorization for payment is generated, the appropriate accounting distribution is also generated to support the payment authorization. The Retail Merchandise Procure to Pay integration automates the processing of invoice payments, adjustments, and write-offs from Oracle ReIM to PeopleSoft Payables and General Ledger. Other accounting transactions are generated from Oracle ReIM to write off aged receipts that were never invoiced and to post accounting distributions for manually paid or prepaid invoices after goods or services are received.
This integration uses the Oracle Data Integrator (ODI) application to transfer the data from Oracle ReIM to PeopleSoft Payables.
The process integration between Oracle ReIM and PeopleSoft Payables supports these integration flows:
Post matched invoices for payment from Oracle ReIM to PeopleSoft Payables.
Post credit notes (matched or unmatched) for payment adjustment from Oracle ReIM to PeopleSoft Payables.
Post debit or credit memos for payment adjustment from Oracle ReIM to PeopleSoft Payables.
Post rebates for payment adjustment from Oracle ReIM to PeopleSoft Payables.
Post unmatched invoices for prepayment from Oracle ReIM to PeopleSoft Payables.
Figure 5-1 illustrates the process flow for Retail Merchandise Procure to Pay integration.
Table 5-1 describes the overall process.
Table 5-1 Retail Merchandise Procure to Pay Overall Process
| Work Location | Step |
|---|---|
|
Oracle Retail (ReIM) |
1. Configure and generate outgoing data from Oracle ReIM. |
|
Integration process |
2. The integration process checks for data in the Oracle ReIM Invoice Header and Invoice Lines interface tables. 3. Populate the Invoice cross-reference with the Retail key and common key. 4. Load and transform the Oracle Retail data into the PeopleSoft Voucher Header and Voucher Lines Staging tables. 5. Delete the data from the source tables (Oracle ReIM Invoice Header and Invoice Lines interface tables) after a successful load. |
|
PeopleSoft Payables |
6. Run the Voucher Build process and import the vouchers into the PeopleSoft Payables system for the pay cycle. 7. Invoke a process to populate the new PeopleSoft Invoice cross-reference table with the PSFT key and RETL key. |
The integration design assumes that:
Before the system imports the invoices from Oracle Retail to PeopleSoft Payables, these integrations are completed successfully:
Supplier synchronization and supplier cross-reference.
Supplier address synchronization and supplier address cross-reference.
Payment terms synchronization and payment terms cross-reference.
Currency code domain value map (DVM) and currency exchange rate synchronization.
SetID, business unit, and ChartFields or segment DVM.
Chart of accounts combinations are manually entered into the Oracle Retail and PeopleSoft applications.
The DVM data is set up in the Service Oriented Architecture (SOA) suite and exported from the SOA suite that is used as input in the ODI interfaces.
The data moves from one instance of source database to one instance of target database.
If the user loads the same data twice in the Oracle Retail interface table, then the data is transported again to the PeopleSoft interface tables.
In the ODI interface, no business validation is applied to check whether any data is transported.
The chart of accounts combinations are valid for invoice lines.
Invoice lines with invalid combinations are rejected by the PeopleSoft Voucher Build process, and the correction of the same is a manual process in PeopleSoft applications.
PeopleSoft GL does not delete the invoices with source as RETL.
Oracle Retail modifies the IM_AP_STAGE_HEAD and IM_AP_STAGE_DETAIL tables and adds segment 11 through segment 20 for the dynamic mapping to PeopleSoft ChartFields.
The monetary amount fields in the PeopleSoft and Oracle Retail systems have a mismatch in the number of digits after the decimal.
The PeopleSoft system allows only 3 digits after a decimal, and Oracle Retail allows 4 digits. Therefore, some data may be rounded while moving from Oracle Retail to PeopleSoft.
This section discusses the following topics:
Section 5.2.2, "Setup Tasks Specific to PeopleSoft Payables"
Section 5.2.3, "Setup Tasks Specific to the Integration Layer"
For more information about the setup tasks specific to Oracle ReIM, see the ReIM Operations Guide, "PeopleSoft Enterprise Financials Integration."
Perform these tasks:
Activate appropriate service operations, handlers, routings, and queues and define the appropriate security groups for the service operations.
Populate DVMs with appropriate PeopleSoft and common values.
Define the end points for outbound services.
Define recurring run controls for repetitively scheduled processes.
For more information about these tasks, see Section 7.4, "Setting Up PeopleSoft GL."
Perform these tasks:
Synchronize suppliers and their locations.
Synchronize payment terms.
Map the chart of accounts segment between the Oracle Retail and PeopleSoft applications.
Validate the chart of accounts code combination.
Synchronize the currency exchange rates.
Modify these DVMs:
CURRENCY_CODE DVM
BUSINESS_UNIT DVM
These DVMs are exported during installation to the directory specified in Oracle Universal Installer (OUI).
Generate IM_AP_STAGE_DETAIL_VW:
Modify the COA_SEGMENT_MAP DVM in Oracle JDeveloper.
Export the DVM and save the XML file.
Verify the IM_AP_STAGE_DETAIL_VW.ini file for the view creation.
Run the CreateViewText.java program and pass the parameters as IM_AP_STAGE_DETAIL_VW.ini COASegmentMapDVM.xml IM_AP_STAGE_DETAIL_VW.sql 'RETL_01' 'PSFT_01'.
Verify the IM_AP_STAGE_DETAIL_VW.sql file and create the view in the Oracle Retail database.
Modify the AIAConfigurationProperties.XML file with the parameters shown in Example 5-1.
Example 5-1 AIAConfigurationProperties.XML Parameters
<ModuleConfiguration moduleName="RetailToPeopleSoftInvoiceIntegration">
<Property name="ToMailAddress">john.doe@example.com</Property>
<Property name="MailServerName">mail.oracle.com</Property>
<!-- *-*-*-*-*-*-*-*-*-*-*-*-**-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
The process waits for the tables to be populated, polling at x milliseconds intervals.
*-*-*-*-*-*-*-*-*-*-*-*-**-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -->
<!-- The period in milliseconds to wait between data polls. -->
<Property name="PollingInterval">60000</Property>
<!-- Path to exported DVMs -->
<Property name="DVMPath">C:\YourDVMLocation</Property>
<!-- Name of the DVM Column and XREF_DATA.ColumnName for Retail values. -->
<Property name="SenderID">RETL_01</Property>
<!-- Name of the DVM Column and XREF_DATA.ColumnName for PeopleSoft values. -->
<Property name="TargetID">PSFT_01</Property>
<!-- Name to use for Table Name in the XREF_DATA. -->
<Property name="XRefTableName">PAYABLEINVOICE_PAYABLEINVOICEID</Property>
</ModuleConfiguration>
This section discusses the following topics:
Before running any transaction data, complete these tasks in Oracle ReIM:
Set up General ledger (GL) account cross-reference.
Define GL options.
For more information, see the ReIM User Guide, "System Administration," General Ledger Accounts.
In Oracle ReIM, the batch process engages in these high-level steps:
Performs any resolution actions (for example, instigates the creation of payment documents).
Calls the posting process to write applicable financial accounting transactions to the financials staging table, IM_FINANCIALS_STAGE.
The processing occurs after resolution documents resolve discrepancies for documents. After all of the resolution documents for a matched invoice are built and all of the RCA and RUA external processing is confirmed, the process inserts financial accounting transactions into the financials staging table to represent the resolution and consequent posting of the invoice. The process also inserts financial accounting transactions for the approved documents that are being handled.
After all of the transactions are written, the process switches the status of the current invoices or documents to Posted and then moves on to the next invoice or document. If a segment lookup fails, the system writes the failed record to a financials error table.
For more information, see the ReIM Operations Guide, "Batch Processes," Resolution posting action rollup batch design.
The process integration for Retail Merchandise Procure to Pay uses ODI to pick up the data from Oracle ReIM, transform the data, and load the transformed data into the PSFT staging tables for the Voucher Build process.
Figure 5-2 illustrates the process.
Complete these tasks as part of the Retail Merchandise Procure to Pay integration:
The Oracle Retail system polls the IM_AP_STAGE_HEAD table until the table is populated with data.
The system loads the AIAConfigurationProperties.XML file into the ODI system.
The system loads all user-defined properties and variables from the AIAConfigurationProperties.XML file into Oracle ReIM.
ODI loads the BUSINESS_UNIT.xml and CURRENCY_CODE.XML DVM files into Oracle ReIM for mapping.
An ODI interface object is run to transform and map the data from the Retail IM_AP_STAGE_HEAD table to the PeopleSoft PS_VCHR_HDR_AIA table.
A variable is set to capture the number of records that were inserted for the header interface.
This variable is used in the email confirmation.
An ODI interface object is run to transform and map the data from the Retail IM_AP_STAGE_DETAIL table to the PeopleSoft PS_VCHR_LINE_AIA table.
A variable is set to capture the number of records that were inserted for the detail interface.
This variable is also used in the email confirmation.
Two procedures are run. The first updates the Retail IM_AP_STAGE_DETAIL table with the ODI session number.
The second deletes the records from both the Retail header and detail tables based on this ODI session number. The ODI session is automatically populated in the header table by the header interface.
Note:
If any of these steps fails, an error message is sent to the AIAAsyncErrorHandling BPEL process, and the data is rolled back in the Retail tables, saving it for another run after the error is fixed.
This section discusses the following topics:
Section 5.5.1, "Configuring the Process Integration for Retail Merchandise Procure to Pay"
Section 5.5.3, "Running the Process Integration for Retail Merchandise Procure to Pay"
After you have installed the process integration for Retail Merchandise Procure to Pay, you must configure several parameters. Open the AIAConfigurationProperties.XML file that is available in the AIA_HOME>/aia_instances/$INSTANCE_NAME/AIAMetaData/config folder.
For more information about requirements for working with AIAConfigurationProperties.xml, see Oracle Fusion Middleware Developer's Guide for Oracle Application Integration Architecture Foundation Pack, "Building AIA Integration Flows," How to Set Up AIA Workstation.
For more information, see Oracle Fusion Middleware Developer's Guide for Oracle Application Integration Architecture Foundation Pack, "Configuring and Using Oracle Enterprise Repository as the Oracle AIA SOA Repository."
When the process integration for Retail Merchandise Procure to Pay completes successfully, the system sends an email confirmation containing the number of successfully loaded records. You can configure the email properties in the AIAConfigurationProperties.XML file.
Under the moduleName RetailToPeopleSoftInvoiceIntegration, update these properties:
<Property name="FromMailAddress">emailFrom</Property> <Property name="ToMailAddress">emailTo</Property> <Property name="MailServerName">mail.oracle.com</Property>
For the FromMailAddress property, configure an email address to be used as the sender's email address for email notification.
For the ToMailAddress property, configure an email address where the notification is sent.
For the MailServerName property, configure the valid mail server that is used to send email notifications.
For this process integration, the system polls the staging tables until the tables are populated with the data. You can configure the rate of polling property in the AIAConfigurationProperties.XML file.
If desired, update the PollingInterval properties:
<Property name="PollingInterval">60000</Property> <Property name="PSFTUrl.ProtocolHostPort"></Property>
For this process integration, the system requires a database sequence to uniquely number entries in the PS_ORT_ACCT_ENTRY table in PeopleSoft applications.
If desired, update the CommonIDSeq property:
<Property name="CommonIDSeq">PS_AIA_COMMON_ID_SEQ</Property>
During the installation process, DVMs are exported automatically to the location specified in OUI. However, you must modify these DVMs with the correct data:
BUSINESS_UNIT
CHARTOFACCOUNTS_GLELEMENT
CURRENCY_CODE
Whenever a set of books ID (business unit) is created in the Oracle Retail and PeopleSoft systems, you must regenerate the views to include the new set of book IDs.
Complete these steps to create views in the Oracle Retail database:
Set up the cross-reference between segments and ChartFields.
A. Update the DVM:
Log in to the Oracle Fusion Middleware (FMW) server using SSH.
Open the WSLT console. The commands to start the console are as follows.
cd <SOA_HOME>/common/bin
./wlst.sh
Connect to the SOA server using the connect command: connect('<SOA_USER_NAME>','<SOA_USER_PASSWORD>','t3://<SOA_HOST>:<SOA_ADMIN_PORT>')
Example: connect('weblogic','weblogic#1','t3://sdc60011sems.us.yourcompany.com:7044')
Export the required DVM using the exportMetadata command: exportMetadata(application='<APPLICATION_NAME>',server='<SOA_SERVER>',toLocation='<TARGET_PATH>',docs='<DVM_PATH>')
Example: exportMetadata(application='soa-infra',server='soa_server1',toLocation='/slot/ems5343/oracle/export',docs='/apps/AIAMetaData/dvm/ITEM_INDICATOR.dvm')
Exit the WSLT console using the exit() command.
B. Export CHARTOFACCOUNTS_GLELEMNT DVM into the <AIA_HOME>/apps/services/core/BulkDataProcess/OracleRetailToPeopleSoft/Financials/CreateRetailViewSQL.
Run the Java program to generate the SQL scripts.
From the command line, navigate to the CreateRetailViewSQL folder.
Call the ANT script CreateRetailView.xml.
This ANT script generates these SQL scripts: STG_FIF_GL_DATA_VW.sql, IM_FINANCIALS_STG_VW.sql, and IM_AP_STAGE_DETAIL_VW.sql.
Run these SQL scripts in the Oracle Retail database to create the views: STG_FIF_GL_DATA_VW.sql, IM_FINANCIALS_STG_VW.sql, and IM_AP_STAGE_DETAIL_VW.sql.
A package is the main processing unit in ODI. A package is made of a sequence of steps organized in an execution diagram. This integration includes these packages:
If PeopleSoft applications are on an Oracle database: Load RETL Invoices To PSFT Package
If PeopleSoft applications are on an IBM DB2 database: Load RETL Invoices To PSFT DB2
If the PeopleSoft system is running on the IBM DB2 database, set up the Datatype conversion to VARGRAPHIC for the Oracle and XML technology, as illustrated in Figure 5-3, before generating the scenario:
For each package, users must generate a scenario and then schedule it. A scenario is the execution unit for production that you can schedule.
For more information about creating schedules and scenarios, see the Oracle Data Integrator User Guide and the Oracle Data Integrator Installation Guide. These documents are available in <odi install location?>\oracledi\doc subdirectory.
This process runs based on schedules you create for it. You can schedule the integration between Oracle Retail and PeopleSoft Payables to occur automatically.
Note:
You can start or stop the scheduler agent whenever you want, which enables you to control the integration process.
For more information about starting and stopping the scheduler agent, see Oracle Data Integrator User Guide, "Launching a Scheduler Agent" and "Stopping an Agent." This document is available in <odi install location?>\oracledi\doc subdirectory.
|
 Copyright © 2001, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
