| 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 accounting entries from Oracle Retail Sales Audit (Oracle ReSA), Oracle Retail Merchandising System (Oracle RMS), and Oracle Retail Invoice Matching (Oracle ReIM) to PeopleSoft General Ledger (GL). It 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 accounting entries.
This chapter includes the following sections:
Section 4.3, "Configuring and Generating Data in Oracle Retail"
Section 4.5, "Configuring and Running the Process Integration for Accounting Entries"
The process integration for accounting entries enables you to record the financial effect of changes to sellable store and warehouse inventory. It also records the financial effect of sales and returns, cash reconciliation, and void transactions from stores.
The system sends sales audit and stock ledger data in Oracle Retail to PeopleSoft General Ledger (GL) through accounting entries. Similarly, the system sends the prepaid invoice reversals and write-offs of aged receipts to PeopleSoft GL. Oracle Retail Merchandising System (Oracle RMS), Oracle Retail Invoice Matching (Oracle ReIM), and Oracle Retail Sales Audit (Oracle ReSA) are the sources for accounting entry. After successful completion of data transfer, the system deletes the data from the source interface tables.
This integration uses the Oracle Data Integrator (ODI) application to transfer the data from the Oracle Retail applications to the PeopleSoft GL application.
The process integration between Oracle Retail (Oracle RMS, Oracle ReSA, and Oracle ReIM) and PeopleSoft GL supports these integration flows:
Post stock ledger from Oracle RMS to PeopleSoft GL.
Post channel sales, cash, and deposits from Oracle ReSA to PeopleSoft GL.
Post prepaid invoice reversals after receipt from Oracle ReIM to PeopleSoft GL.
Figure 4-1 illustrates the process integration for accounting entries.
This table describes the overall process:
Table 4-1 Integration of Accounting Entries Overall Process
|
Work Location |
Step |
|
Oracle Retail |
1. Configure, generate, and load accounting entries into interface table in Oracle Retail. |
|
Integration process |
2. The integration process waits for the data to arrive in the Oracle Retail interface (staging) table. 3. Load the Oracle Retail data into PeopleSoft GL and transform it. 4. Delete the data from the interface table after a successful load. |
|
PeopleSoft GL |
5. Import from accounting entries interface table to journal entries and then post to the GL accounts. |
Oracle Retail
Oracle RMS, Oracle ReSA, and Oracle ReIM are the source of the accounting entry flow.
Oracle Retail populates a staging table, STG_FIF_GL_DATA or IM_FINANCIALS_STAGE (depending on whether the Oracle RMS or Oracle ReIM package is running) on a scheduled basis.
Data in the Oracle Retail staging tables is deleted after the ODI job completes successfully.
PeopleSoft GL
PeopleSoft GL has one table to receive data: PS_ORT_ACCT_ENTRY.
The PeopleSoft Journal Generator process is run using the table to create the accounting entries in PeopleSoft GL.
PeopleSoft GL supports both databases; however, this process integration pack (PIP) only supports Oracle and IBM UDB DB2 databases.
Data in the PeopleSoft table is flagged as processed by PeopleSoft GL.
Constraints
The monetary amount fields in the PeopleSoft GL and Oracle Retail systems have a mismatch in the number of digits after the decimal. The PeopleSoft system allows 3 digits after the decimal, and Oracle Retail allows 4 digits. Therefore, some data may be rounded while being transferred from Oracle Retail to PeopleSoft GL.
No specific setup is required for PeopleSoft GL and Oracle Retail. However, you must perform these setup tasks specific to the integration layer:
Modify the required domain value maps (DVMs) with valid data.
The installation process exports DVMs to the location specified during installation.
Create these views in the Oracle Retail database: IM_FINANCIALS_STG_VW and FIF_STG_GL_DATA_VW.
Set up the AIAConfigurationProperties.XML file.
Set up only the email properties and data polling time.
This section discusses these topics:
Before running any accounting data, perform these tasks in Oracle RMS and Oracle ReIM:
Set up a GL account cross-reference.
Define GL options.
For more information, see the RMS User Guide, "Financial Management," "Stock Ledger," "Maintain general ledger cross-reference" and the ReIM User Guide, "System Administration," "General Ledger Accounts."
Oracle RMS stages GL data for subsequent upload into the integrated financial system. A set of batch processes gather and organize the data before using it to populate the related staging table.
These batch designs are included in this functional area:
FIFGLDN1.PC
FIFGLDN1.PC
FIFGLDN3.PC
For more information, see the RMS Operations Guide, "General Ledger (GL) Batch."
In Oracle ReIM, the batch process engages in these high-level steps:
Performs any resolution actions (for example, initiates 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 discrepancies for documents have been resolved by resolution documents. After all of the resolution documents for a matched invoice are built and all of the RCA and RUA external processing has been confirmed, the process inserts financial accounting transactions to 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 have been 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 accounting entries uses ODI to pick up the data from Oracle RMS, Oracle ReSA, and Oracle ReIM, transform it, and load it into the PeopleSoft staging table for the GL Generator.
Figure 4-2 illustrates the process.
Perform these tasks as part of the accounting entries load process:
This process loads the AIAConfigurationProperties.XML file into the ODI system.
The system polls the STG_FIF_GL_DATA or IM_FINANCIALS_STG tables (depending on whether the Oracle RMS or Oracle ReIM package is running) until the data has been loaded in the tables.
The rate of polling depends upon a property set in the AIAConfigurationProperties.XML file.
ODI loads the BUSINESS_UNIT.XML and CURRENCY_CODE. XML DVM files into the system for mapping purposes.
The system loads all user-defined properties and variables from the AIAConfigurationProperties.XML file.
The system creates a database sequence to uniquely number entries in the PS_ORT_ACCT_ENTRY table in PeopleSoft, if the database is not created.
An ODI interface object is run to transform and map the data from the Retail table to the PSFT table.
If the package is successful, an email confirmation is sent to the administrator indicating the number of successfully loaded records.
Note:
If any of these steps fails, an error message is sent to the AIAAsyncErrorHandlingBPELProcess, and the data is rolled back into the Retail table, saving it for another run after the error is fixed.
This section discusses these topics:
Section 4.5.1, "Configuring the Process Integration for Accounting Entries"
Section 4.5.3, "Running the Process Integration for Accounting Entries"
After you have installed the process integration for accounting entries, 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 how to update MDS, see Oracle Fusion Middleware Developer's Guide for Oracle Application Integration Architecture Foundation Pack, "Building AIA Integration Flows," Updating MDS.
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 accounting entries 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 module name RetailToPeopleSoft_AccountingEntry, 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 for accounting entries, 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 property:
<Property name="PollingInterval">60000</Property> <Property name="FromMailAddress">john.doe@example.com</Property>
For this process integration for accounting entries, the system requires a database sequence to uniquely number entries in the PS_ORT_ACCT_ENTRY table in PeopleSoft.
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 Oracle Universal Installer (OUI). However, you must modify these DVM with the correct data:
BUSINESS_UNIT
CURRENCY_CODE
Set up and export the CHARTOFACCOUNTS_GLELEMENT DVM to <AIA_HOME>/services/core/BulkDataProcess/OracleRetailToPeopleSoft/Financials/CreateRetailViewSQL.
Whenever a set of books ID (business unit) is created in the Oracle Retail and PeopleSoft systems, you must regenerate the views for including the new set of books ID.
To create the views in the Oracle Retail database, complete these steps:
Set up the cross-reference between segments and ChartFields.
A. Update the DVM:
Navigate to SOA_HOME/common/bin: /slot/ems3344/oracle/Middleware/Oracle_SOA1/common/bin
Open the WebLogic scripting tool: ./wlst.sh
Connect to your server using this command: connect('weblogic','weblogic#1','t3://sdc60012sems.us.yourcompany.com:7045')
To export a single DVM, use this command: exportMetadata(application='soa-infra',server='soa_server1',toLocation='/slot/ems3344/oracle/ODI/ExportedDVMs',docs='/apps/AIAMetaData/dvm/SUPPLIERPARTY_STATUSCODE.dvm')
To export all DVMs, use this command: exportMetadata(application='soa-infra',server='soa_server1',toLocation='/slot/ems3344/oracle/ODI/ExportedDVMs',docs='/apps/AIAMetaData/dvm/**')
To leave WLST, use this command: exit()
If you put your toLocation as /slot/ems3344/oracle/ODI/ExportedDVMs, it is appended as follows: /slot/ems3344/oracle/ODI/ExportedDVMs/apps/AIAMetaData/dvm
To export your AIAConfigurationProperties file, use this command: exportMetadata(application='soainfra',server='soa_server1',toLocation='/slot/ems3344/oracle/ODI/ExportedDVMs',docs='/apps/AIAMetaData/config/AIAConfigurationProperties.xml')
change .dvm file to .xml
B. Export the CHARTOFACCOUNTS_GLELEMNT DVM into the <AIA_HOME>/services/core/BulkDataProcess/OracleRetailToPeopleSoft/Financials/CreateRetailViewSQL folder.
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 IM_AP_STAGE_DETAIL_VW.sql
Run these SQL scripts into 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 the PeopleSoft application is on an Oracle database:
Load RMS To PSFT Acct Entry Package
Load ReIM To PSFT Acct Entry Package
If the PeopleSoft application is on an IBM DB2 database:
Load RMS To PSFT DB2 Package
Load ReIM To PSFT DB2 Package
If the PeopleSoft system is running on the IBM DB2 database, set up the Datatype conversion VARGRAPHIC for the Oracle and XML technology, as illustrated in Figure 4-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 can be scheduled.
For more information about creating schedules and scenarios, see Oracle Data Integrator User Guide and Oracle Data Integrator Installation Guide. These documents are available in <odi install location?>\oracledi\doc subdirectory.
This process runs based on the schedules created for it. You can schedule the integration between Oracle Retail and PeopleSoft GL 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 the <odi install location?>\oracledi\doc subdirectory.
|
 Copyright © 2001, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
