PK
+Aoa, mimetypeapplication/epub+zipPK +A iTunesMetadata.plist
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.
This chapter provides an overview of the Oracle Financial Operations Control Integration Pack for Oracle Retail Merchandise Operations Management and PeopleSoft Enterprise Financials. It also discusses key benefits and participating applications, and describes the retail sales financial, retail inventory financial, and retail procure to pay business process flows. Also included are solution assumptions and constraints.
This chapter includes the following sections:
Section 1.1, "Understanding the Oracle Financial Operations Control Integration Pack"
Section 1.5, "Retail Inventory Financial Business Process Flow"
Section 1.6, "Retail Merchandise Procure to Pay Business Process Flow"
The Oracle Financial Operations Control Integration Pack for Oracle Retail Merchandise Operations Management and PeopleSoft Enterprise Financials provides integration to a robust enterprise financial system that complements the Oracle Retail Merchandising system in a retail customer environment.
This process integration includes these four processes:
Life Cycle Data Management
This process enables users of Oracle Retail Merchandise Suite to perform functions with data that is shared with the PeopleSoft applications throughout the life cycle of the data that is created and updated. This process provides data synchronization for the initial load before implementation, and incremental data creation and maintenance after implementation. PeopleSoft Payables is the source of suppliers, payment terms, currency, and freight terms. This process synchronizes suppliers, payment terms, and currency exchange rates from PeopleSoft applications to the Oracle Retail Merchandising System (Oracle RMS). Freight terms are static in nature and their volume is very low; therefore, they must be manually synchronized between the two systems.
Inventory Valuation (Retail stock ledger)
This process enables users to post accounting entries generated from transactions that change the value of sellable products at retailers' inventory locations (stores and warehouses) from the Oracle Retail Merchandising stock ledger to PeopleSoft General Ledger (GL). Valuation of sellable inventory in stores and warehouses is based on the processing of transactions for movement, pricing, costing, and sale of the inventory. This valuation is captured and processed in the Oracle Retail stock ledger. These transactions include sales, shipments from warehouse to store, store receipts, store transfers, returns to vendors, price changes, stock counts, and shrinkage due to theft or damage.
Retail Revenue Recognition
This process enables users to post accounting entries generated from sales and returns transactions from retailer's stores for revenue and cash reconciliation to the appropriate ledgers. In this process, data flows from Oracle Retail Sales Audit (Oracle ReSA) to PeopleSoft GL. This process records the financial effect of sale and return, cash reconciliation, and void transactions from stores.
Note: Sales and returns can also come from the Oracle RMS stock ledger. |
The Revenue Recognition process begins when Oracle ReSA processes store transactions (sales and returns). For each store transaction, Oracle ReSA generates the appropriate accounting entries to be posted to PeopleSoft GL. Each accounting entry has a valid account code segment combination based on the transaction type, business unit, and location (store or warehouse).
Retail Merchandising Procure to Pay
This process begins with the Oracle Retail Invoice Matching (Oracle ReIM) application. 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 triggers 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 GL. Other accounting transactions are generated from Oracle ReIM to write off aged receipts that were never invoiced and to post accounting distribution for manually paid or prepaid invoices after receipt.
Figure 1-1 illustrates the Oracle Retail to PeopleSoft Financials process flow.
This PIP does not synchronize charts of accounts from PeopleSoft GL to Oracle Retail. Charts of accounts are combinations of account code segments. Because transaction types are defined and assigned combinations of code segments for proper handling of the financial effects in Oracle Retail, PeopleSoft GL provides a service that validates the code combinations. This service ensures that the accounting entries generated by the transactions are valid when they are posted to PeopleSoft GL.
The PIP also provides the drill back drill forward feature. This feature enables the PeopleSoft GL and Oracle Retail users to access information about the financial transactions from the integrated systems in a seamless and intuitive manner. It provides users an end-to-end traceability between PeopleSoft Payables and General Ledger back to Oracle RMS, Oracle ReSA, and Oracle ReIM. From PeopleSoft Enterprise Financials, users can drill back to query data from Oracle Retail applications. The drill back is initiated from the user interfaces (UI) of PeopleSoft Financials. Oracle Retail application users can drill forward to query integrated data from PeopleSoft Enterprise Financials. The drill back and drill forward feature provides traceability from retail transactions to journal entries, and back, and from invoices to vouchers, and back, between Oracle Retail and PeopleSoft Financials. This capability enables processes to support supplier servicing and financial auditing.
Here are the key benefits of this PIP:
This integration is not a point-to-point integration between the PeopleSoft Financials and Oracle Retail applications.
This PIP implementation is independent of the version of integrated applications. An Oracle AIA layer serves as an intermediate thin layer of application between PeopleSoft Financials and Oracle Retail. This integration remains synchronized with the new releases of the edge applications.
The system exports the sales audit data to the PeopleSoft Financial applications days before the traditional audit process permits.
The Financials applications can use this timely data proactively, which results in increased productivity and operational efficiencies.
This PIP reduces the total cost of ownership for Oracle and its customers.
Drill back and drill forward actions between the PeopleSoft Financials and Oracle Retail applications are seamless.
The Oracle Retail and PeopleSoft Financials users do not require any training or must inquire about specific data in either application.
This PIP supports cost or retail accounting methods.
This section discusses these topics:
Oracle RMS is an integrated solution for global retailing. This solution enables retailers to better manage, control, and perform crucial day-to-day merchandising activities. From new product introduction to inventory management, Oracle RMS provides retailers with a complete end-to-end solution and is the most comprehensive and integrated solution for global retailing.
For more information about Oracle RMS, see the OracleRetail Merchandising System User Guide.
Oracle ReSA provides retailers with a flexible tool that evaluates and ensures accuracy and completeness of point of sale (POS) data. Realtime access to this audited sales data ensures integrity of information throughout the retail enterprise. With a highly configurable sales audit application, the retailer can maintain existing business practices while providing for future options as the operations grow and change.
ReSA enables retailers to receive POS transaction data, cleanse it, and export the data to the Oracle Merchandising system and the Oracle Retail Data Warehouse. By providing corporate control and visibility to sales audit information, Oracle ReSA enables retailers to make better decisions to improve merchandise operations and transform the economics of their business.
For more information about Oracle ReSA, see the Oracle Retail Sales Audit User Guide.
Oracle ReIM is a market-leading solution for retailers who require an automated application to better manage reconciliation and payment of invoices. This advanced solution enables account payables teams to resolve discrepancies on invoices quickly before payments are made. A highly automated, multidimensional matching engine minimizes time spent on manual reviews. Automated routing provides an effective method to ensure that accurate information is delivered to the correct internal teams for resolution and compliance controls.
For more information about Oracle ReIM, see the Oracle Retail Invoice Matching User Guide.
PeopleSoft Payables provides automated invoice and payment processing to ensure timely and accurate payment for goods and services. Best-practice business processes match purchase orders, receipts, and invoices and provide online approvals to identify exceptions and increase control over disbursements.
PeopleSoft Payables delivers built-in controls to help an enterprise meet regulatory requirements, enforce compliance, reduce risk, and implement due-diligence best practices, thereby reducing cycle times and errors. Other features include a flexible, user-defined system setup; extensive vendor maintenance; digital signatures and financials sanction validation; and powerful inquiry and analytical capabilities.
For more information about PeopleSoft Payables, see the PeopleSoft Enterprise Payables PeopleBook.
PeopleSoft GL offers a fully automated close and consolidation solution for legal and management reporting, including support for Generally Accepted Accounting Principles (GAAP) and International Financial Reporting Standards (IFRS). Transactions are automatically processed and validated according to the best-practice business processes and control settings. In addition, an enterprise can proactively control expenditures by automatically checking spending requests against budget. With realtime reporting and information access, an enterprise can achieve complete visibility into financial results.
For more information about PeopleSoft GL, see the PeopleSoft Enterprise General Ledger PeopleBook.
The Retail Sales Financial business process consists of the Post Channel Sales, Cash, and Deposits from Oracle ReSA to PeopleSoft GL integration flow.
Figure 1-2 illustrates the Retail Sales Financial business process flow.
Oracle ReSA sends summarized sales audit information to PeopleSoft GL for the Sales Journal. The sales audit information includes channel sales, cash, and deposits. The Oracle ReSA Export processes select and format corrected and previously audited data from the Oracle ReSA database so that it can be sent to PeopleSoft Financials.
Oracle ReSA includes programs to automatically extract the required totals data and to format it to generic data files from a financial staging table for import into PeopleSoft GL. Sales audit data from Oracle ReSA is also posted directly to the Oracle RMS stock ledger and can be integrated into PeopleSoft GL through the stock ledger to the financial staging table and the accounting entry table. Before data is imported into PeopleSoft GL, a batch process writes balanced records to the financial staging table using the appropriate General Ledger account combinations (maintained in Cross Reference tables in Oracle ReSA and the Currency Exchange Rates.
The PeopleSoft Journal Generator loads the accounting entries from Oracle Retail to PeopleSoft GL journal entries. The accounting tables are referenced on the accounting entry definition defined for each type of accounting entry transaction. The Journal Generator uses the new accounting entry definitions to create PeopleSoft journal entries. After the journal entries are created by the PeopleSoft Journal Generator, they are edited and posted similarly to PeopleSoft subsystem journals.
The Retail Inventory Financial business process consists of the posting stock ledger from Oracle RMS to PeopleSoft GL. integration flow
Figure 1-3 illustrates the Retail Inventory Financial business process flow.
Note: This diagram is for illustration only. Stores, warehouses, and corporate write other inventory related transactions to the Oracle RMS stock ledger. |
The stock ledger in Oracle RMS records the financial results of the merchandising processes that occur in the Retail system, such as buying, selling, price changing, transferring, and so on. All of these transactions are recorded in the Oracle RMS stock ledger and rolled up to the subclass/location-level for days, weeks, and months. Daily and period-based financial information is scheduled to be loaded into PeopleSoft.
Oracle RMS sends three levels of stock ledger information to PeopleSoft GL:
Monthly - no access to detailed reference information.
Daily by subclass, class, or department - no access to detailed reference information.
Daily by transaction.
The stock ledger transactions to be loaded into PeopleSoft are placed on the financial staging table with table triggers or batch, by the appropriate General Ledger account combinations (maintained in the Oracle RMS cross-reference table in Oracle Retail) and the currency exchange rates.
The PeopleSoft Journal Generator loads the accounting entries from Oracle Retail and creates PeopleSoft GL journal entries. The accounting tables are referenced on the accounting entry definition defined for each type of accounting entry transaction. The Journal Generator uses new accounting entry definitions to create PeopleSoft journal entries. After the journal entries are created by the Journal Generator, they are edited and posted similarly to PeopleSoft subsystem journals.
The Retail Merchandise Procure to Pay business process consists of these integration flows:
Post matched prepaid invoices from Oracle ReIM to PeopleSoft GL.
Post manually matched paid invoices from Oracle ReIM to PeopleSoft GL.
Post receipt write-offs from Oracle ReIM to PeopleSoft GL.
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 1-4, Figure 1-5, and Figure 1-6 illustrate the Retail Merchandise Procure to Pay business process flow:
The Retail Merchandise Procure to Pay business process flow enables posting of matched invoices, matched credit notes, debit and credit memos, rebates, and unmatched invoices for prepayment from Oracle ReIM to PeopleSoft Payables. It also facilitates drill back from PeopleSoft Payables to Oracle ReIM and drill forward from Oracle ReIM to PeopleSoft Payables.
These are the assumptions and constraints for this PIP:
PeopleSoft GL
You must implement the PeopleSoft applications before you implement this PIP.
The set of books in Oracle Retail is equivalent to the PeopleSoft GL business unit, ledger group, assuming the default ledger group and setID.
You are not required to map the ledger group and setID on the Oracle Retail side. PeopleSoft publishes the associated business units for each transaction that is setID-driven. You are not required to persist with the structure in the middle layer. Each transaction message accounts for the setID business unit association.
Oracle Retail manually creates and stores valid segment (ChartField) combinations in the appropriate GL cross-reference tables (Oracle ReSA, Oracle RMS, and Oracle ReIM).
For PeopleSoft, 16 ChartFields are available and 4 additional inactive ChartFields can be activated by customers. Oracle Retail supports 20 segments structure.
Therefore, the PeopleSoft Chart of Accounts can be made of up to 20 ChartFields (segments) only.
PeopleSoft ChartFields are 30 characters in length.
The Oracle Retail Integration Bus (RIB) subscription format supports only 25 characters for segments (ChartFields). Therefore, the segment length for Oracle Retail customers is 25 characters.
Currency Exchange Rate
The Oracle Retail stock ledger supports multiple currencies.
All transaction-level information is stored in the local currency of the store or warehouse where the transaction occurred. In Oracle ReIM, the invoice currency on the invoice is often the currency of the supplier, which can be different than the local currency.
The currency amounts precision is limited to 3 digits.
Calendar
The PeopleSoft applications use multiple calendars.
In this PIP, both PeopleSoft Financials and Oracle Retail use a single calendar.
After a calendar is established, this integration does not support switching the calendar.
Sales Audit/Stock Ledger Transactions
Oracle Retail sends the accounting date and the transaction date with its transactions.
These dates must not be changed or manipulated in PeopleSoft.
Users handle accounting entry errors manually on both the Oracle Retail and the PeopleSoft Financials side.
The system passes use or sales tax accounting information as part of the accounting entries between Oracle Retail and PeopleSoft Financials.
Oracle Retail calculates value-added tax (VAT).
PeopleSoft Journal Generator does not recalculate VAT. The system passes the VAT calculation as part of the accounting entry.
Oracle Retail stock ledger determines the valuation of inventory for merchandise being directly procured.
It passes this information to PeopleSoft as the accounting entries.
Oracle RMS, through the Oracle Retail stock ledger, provides PeopleSoft Financials with the value of ending inventory at cost using the method that the retailer indicates (cost method or retail method of accounting) by an adjusting entry.
This information is provided through monthly feed.
You cannot load accounting entries twice into PeopleSoft Financials.
When journals are created, a status field called Distribution Status changes to D; therefore, you cannot create new journal vouchers for the same set of accounting entries.
Other
Both PeopleSoft and Oracle Retail support multiple organizations in one application instance.
If you rename, change the name but not the value, or create new ChartFields (segments), you have customized the middle layer.
Oracle RIB is required for all message-based integration to Oracle Retail.
Constraints
Oracle Retail and PeopleSoft applications do not support modifying date by time zones.
PeopleSoft Financials can handle only three decimal places in amount fields.
For this integration, no currency in Oracle Retail must contain more than three decimal places. Otherwise, the amounts are rounded from four decimals to three decimals that may cause out-of-balance issues.
Customers switching from one financials application to another must re-implement the integration pack.
Oracle AIA uses the location ID as a key and, when it changes, Oracle AIA cannot associate it with the former ID from the create operation.
Therefore, PeopleSoft users experience an issue when they change the location ID of an existing supplier location in an update operation.
Note: Additional assumptions and constraints exist for each of the process integration flows; they are documented in their respective chapters. |
Oracle Financial Operations Control Integration Pack Implementation Guide for Oracle Retail Merchandise Operations Management and PeopleSoft Enterprise Financials
Release 3.1
E20500-03
September 2012
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
E20500-03
Copyright © 2001, 2012, Oracle and/or its affiliates. All rights reserved.
Primary Author: Oracle Corporation
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate failsafe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
Part I includes the following chapters:
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.
Welcome to the Oracle Application Integration Architecture Oracle Financial Operations Control Integration Pack Implementation Guide for Oracle Retail Merchandise Operations Management and PeopleSoft Enterprise Financials for Oracle Retail Merchandise Operations Management and PeopleSoft Enterprise Financials.
Oracle Application Integration Architecture Pre-Built Integrations 11.1 includes the following guides shared by all products delivered with this release:
Oracle Application Integration Architecture Installation and Upgrade Guide for Pre-Built Integrations Release 11.1
This guide provides an overview of the installation process, including how to install, configure, and deploy your pre-built integrations. The steps required to upgrade your pre-built integrations to the latest release are also provided.
Oracle Application Integration Architecture Pre-Built Integrations 11.1: Utilities Guide
This guide describes:
How to work with and configure Session Pool Manager (SPM), which is a service in the Oracle SOA Suite web server whose primary function is to manage a pool of web server session tokens that can be reused by BPEL flows.
How to deploy and configure the AIACompositeScheduler. This is a utility component that is used by pre-built integrations to schedule a service-oriented architecture (SOA) composite to be invoked at the specified time interval.
Oracle Application Integration Architecture Pre-Built Integrations 11.1: Product-to-Guide Index
The product-to-Guide index lists the guides that provide information for each product delivered in this release.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc
.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info
or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs
if you are hearing impaired.
The following resources are also available:
Oracle Application Integration Architecture Foundation Pack:
Oracle AIA Pre-Built integrations require Foundation Pack 11.1.1.5.0 to be installed. Refer to the Foundation Pack documentation library on OTN to download the Foundation Pack guides at http://download.oracle.com/docs/cd/E21764_01/aia.htm
.
Oracle Application Integration Architecture: Product-to-Guide Index:
Oracle Technology Network: http://www.oracle.com/technetwork/index.html
Known Issues and Workarounds:
My Oracle Support: https://support.oracle.com/
Release Notes:
Oracle Technology Network: http://www.oracle.com/technetwork/index.html
Documentation updates:
Oracle Technology Network: http://www.oracle.com/technetwork/index.html