| Oracle® Retail Financial Integration Cloud Service for Oracle Retail Merchandise Operations Management and Oracle Financials Oracle® Retail Financial Integration Cloud Service for Oracle Retail Merchandise Operations Management and Oracle Financials Release 19.0.000 F25613-03 |
|
 Previous |
 Next |
This chapter discusses the following process integrations:
Currency exchange rate integration
Supplier information integration
This section provides an overview of the process integration for initial loading and incremental synchronization of currency exchange rates between Oracle General Ledger (GL) and the Oracle Retail Merchandising System (RMS) and discusses:
Currency exchange rate integration details
Data requirements
Extensions
|
Note: Currency Exchange Rates incremental synchronization from CFIN is not supported by RFI. |
The currency exchange rate is the reference information used in the translation of monetary values from one currency to another. The exchange rate expresses the value of one currency in terms of another. The process integration for currency exchange rates enables you to use Oracle Financials as an accounting engine and Oracle Retail for sales audit and stock ledger transactions.
The process integration for currency exchange rates supports the following integration flows:
Load initial currency exchange rate from Oracle GL to RMS: Enables the loading of all current and future effective dated currency exchange rates from Oracle GL to RMS for a new instance (logical or physical) of RMS.
Incremental creation and updates of currency exchange rates from Oracle GL to RMS: Enables the synchronization of incremental creation and updates of the current and future effective dated currency exchange rates from Oracle GL to RMS.
This integration is not a point-to-point integration between Oracle GL and RMS. A RFI layer serves as an intermediate thin layer of application between Oracle GL and RMS. As part of the currency exchange rates integration, Oracle GL sends the currency exchange rates to the RFI layer and the RFI layer delivers the information to RMS. The RFI layer performs message filtering, message transformation, and message routing.
EBS Financials application triggers the integration by invoking the Business Event Subscriber Java class CurrencyRateBES with corresponding Business Event parameters. If the program fails, then an error displays in the standard EBS notifications.
PeopleSoft Financials application triggers the integration by invoking the Business Service in RFI with corresponding service parameters. If the program fails, then error is displayed in PeopleSoft application.
This integration is not supported by RFI for CFIN. Instead, users are expected to upload any incremental changes to exchange rates using the RMS Foundation Data Load functionality.
Prerequisites exist for the process integration for currency exchange rate. The following DVM's are available in the RFI_XREF_DVM table:
CURR_CODE_DVM
CURR_CONV_TYPE_CODE_DVM
Currency codes and currency exchange rate types are manually maintained in both the Systems and mapped using DVM.
The RMS FIF_CURRENCY_XREF table should have the appropriate exchange rate type mapping entries between Finance application and RMS.
The integration design assumes that:
Financials application suite stores daily exchange rate for each day. For example, for the month of August 2009 for USD to Euro, there will be 31 entries with the specific conversion rates. Even if the same conversion rates apply for the entire month, there will still be an individual exchange rate for each day.
Oracle Retail does not support triangulation of currency exchange rates
The Retail RIB Error Hospital holds all the Oracle Retail side errors and handles any notification on their side. Deleted currency exchange rates are not passed to Retail and the sync is one-direction only.
The diagram illustrates the currency exchange rate integration flow with EBS:-
The integration flow uses the following services:
CurrencyRateBES - Business Event Subscriber
Currency EJB Service
IGS Currency Service
RIBforRMS CurRate Subscriber
When the process is initiated, the following steps would occur:
Oracle E-Business Suite invokes the Business Event Subscriber Java class CurrencyRateBES whenever a currency exchange rate is created or loaded into Oracle E-Business Suite.
The Business Event Subscriber Java class CurrencyRateBES extracts all the currency exchange rates from the Oracle E-Business Suite Business Event object and moves the currency exchange rates into RFI Database Staging table RFI_CURR_RATE_STAGE.
A Configured Timer for Currency service invokes the Currency EJB service.
The Currency EJB service picks up the records from the RFI Database Staging table RFI_CURR_RATE_STAGE and transforms the Currency Code and Currency Exchange type using the CURR_CODE_DVM and CURR_CONV_TYPE_CODE_DVM respectively.
Then IGS Currency Service is invoked with the transformed currency rate record.
If there are any errors in Currency EJB service, the errors are added to the logs file. The Currency logs can be viewed using the RFI Adapter Manager screens in RFI Administration GUI.
The diagram illustrates the currency exchange rate integration flow with PeopleSoft:-
The integration flow uses the following services:
CURRENCYRATEBUSINESSEVENTHANDL- Business Event Handler
Currency Web Service
IGS Currency Service
RIBforRMS CurRate Subscriber
When the process is initiated, the following steps would occur:
PeopleSoft invokes the Business Event Handler service whenever a currency exchange rate is created or loaded into PeopleSoft E-Business Suite.
The Business Event Handler service "CURRENCYRATEBUSINESSEVENTHANDL "extracts currency exchange rates created from the PeopleSoft invokes Currency Web Service in RFI.
The RFI Service moves the currency exchange rates into RFI Database Staging table RFI_CURR_RATE_STAGE and sends back a response to PeopleSoft
A Configured Timer for Currency service invokes the Currency EJB service.
The Currency EJB service picks up the records from the RFI Database Staging table RFI_CURR_RATE_STAGE and transforms the Currency Code and Currency Exchange type using the CURR_CODE_DVM and CURR_CONV_TYPE_CODE_DVM respectively.
Then IGS Currency Service is invoked with the transformed currency rate record.
If there are any errors in Currency EJB service, the errors are added to the logs file. The Currency logs can be viewed using the RFI Adapter Manager screens in RFI Administration GUI.
The purpose of this flow is to load the existing currency exchange rates from Oracle Financials (EBS or PeopleSoft) into Retail.
Before executing the following steps from Oracle Financials application (EBS or PeopleSoft), VDATE in RMS should be made to point to a date in past (for example, 01-JAN-2008). This is required, because any exchange rate older than VDATE will be filtered out by RIB and will not be interfaced to RMS. Therefore, to load the complete history of exchange rate information in RMS, it is imperative that VDATE is set to past, depending on retailer's cutover choice of effective date.
Use the following steps to load the currency exchange rates initially from Oracle Financials (EBS or PeopleSoft or CFIN) to Retail. Use the configured database link to load the currency data from the Oracle Financials (EBS or PeopleSoft) to the RFI database.
A wrapper script currency_initial_load.sh is provided to load the existing currency rates from E-Business Suite to the RFI Staging table. This file is available in the RFI pack, folder - $INSTALL_DIR/retail-financial-integration-solution/<$FINANCIAL>-integration/install/setup-data/dml. Here $ FINANCIAL should be replaced with value ebs-financial or psft-financial based on the Oracle financials application that is being integrated with RFI
Use the database link to load the currency data from Oracle Financials (EBS or PeopleSoft) to the RFI database. A database link either EBS_RFI_DBLINK or PSFT_RFI_DBLINK should be created between Financials Schema (E-Business Suite APPS schema or PeopleSoft SYSADM schema) and RFI staging schema in RFI Database (created as part of RFI installation step).
Skip this step if the database link is created with default (EBS_RFI_DBLINK or PSFT_RFI_DBLINK) name. If not , Open the sql script currency_initial_load.sql in a text editor and change the database link values created in the above step.
Run the currency_initial_load.sh (available $INSTALL_DIR/retail-financial-integration-solution/<$FINANCIAL>-integration/install/setup-data/dml) script to load data from Oracle Financials (EBS or PeopleSoft) to RFI staging area. This script considers the required environment variables (ORACLE_HOME, INSTALL_DIR) are present.
|
Note: $FINANCIAL should be replaced with ebs-financial or psft-financial based on your integration financial applications. |
The script takes two arguments on the command line: Financials system schema database login credentials and conversion_date (from when the currency rates have to be pushed to Retail system).
For example:
sh currency_initial_load.sh <username>/<password>@<sid> 02-AUG-2013
Once the initial currency rate data is moved to the RFI Staging table, RFI_CURR_RATE_STAGE, the records are sent to Retail similar to the Currency Incremental Loading process.
The RFI Application supports passing custom payload data to the Currency Integration Service.
In case of EBS Financials, the additional information that has to be passed should be captured in Attribute Columns (Attribute1 to Attribute15) in GL_DAILY_RATES table in case of EBS.
In case of PeopleSoft Financials, the additional information has to be passed along with existing payload mapping it to (Attribute 1 to Attribute 15) in RFI_CURR_RATE_STAGE
The Attribute columns are pushed to the RFI Currency staging table RFI_CURR_RATE_STAGE by the Business Event Subscriber Java class CurrencyRateBES or Business Service that is configured in PeopleSoft
A Java interface RfiCurRateCustomPayload is provided in the RFI Application.
A Java class needs to be written by implementing the above interface RfiCurRateCustomPayload and the additional custom payload attributes (Attribute1 to Attribute15) can be added to CurrRateDesc object. A sample implementation class RfiCurRateCustomPayloadImpl is provided in the RFI application.
In RfiSpringBeans.xml, an entry for the implementation class has to be provided.
<bean id="rfiCurRateCustomPayloadBean" class="<Implementation Class Name>"> </bean>
This section provides an overview of the process integration for initial loading and incremental synchronization of suppliers' information between Oracle Payables and Oracle Retail Merchandising System (RMS) and discusses:
Supplier integration details
Data requirements
Extensions
In the integrated environment, Oracle Payables acts as a payable, and RMS handles supplier payments, merchandise write-offs, and prepaid adjustments.
Merchandise suppliers are suppliers of goods and services that the retailer sells to customers. Oracle Payables and RMS require sharing of suppliers' information between them. RMS requires the supplier information for several key functions including creation and management of items and purchase orders. Oracle Payables requires suppliers' information for supplier payment. For end-to-end business integration, same supplier instance and related information must be shared between these two systems.
Between the two systems, Oracle Payables is the source of valid suppliers (vendors in Oracle Payables) and their Remit to Location and Order from addresses
The supplier integration synchronizes supplier's information from Oracle Payables to RMS through these integration flows:
Load initial suppliers from Oracle Payables to RMS: Enables the loading of all active merchandise suppliers, the current effective supplier locations and their current effective remit and order to addresses.
Incremental creation and updates of suppliers from Oracle Payables to RMS: Enables the synchronization of incremental creation and updates of the active suppliers from Oracle Payables to RMS.
|
Note: For incremental loads, Oracle Payables doesn't propagate deleted suppliers information to Retail. |
This integration is not a point-to-point integration between Oracle Payables and RMS. A RFI layer serves as an intermediate thin layer of application between Oracle Payables and RMS. As a part of the supplier integration, Oracle Payables sends the suppliers' information to the RFI layer and the RFI layer delivers the information to RMS. The RFI layer performs message filtering, message transformation, and message routing. Because this integration is not a point-to-point integration, the vendor number (ID) in Oracle Payables is not similar to the supplier number (ID) in Oracle Retail.
These are the prerequisites for this integration:
|
Note: For more information about initial load of the Org Id and Payment term cross reference table, see 'section Setting up Cross-References for Oracle Retail IDs and Oracle Entities. |
The following DVM's/XREFs are available in RFI_XREF_DVM table:
ADDRESS_COUNTRY_DVM
CURR_CODE_DVM
LANGUAGE_CODE_DVM
STATE_DVM
SUPPLIER_ADDRESS_TYPE_DVM
SUPPLIER_PRIMARY_SITE_FLAG_DVM
SUPPLIER_STATUS_CODE_DVM
FREIGHT_TERMS_XREF
PAYMENT_TERMS_XREF
ORG_ID_XREF
SUPPLIER_XREF
SUPPLIER_SITE_XREF
Freight terms synchronization between both the systems is a manual process. The Supplier and Supplier XREF data are populated by a Supplier Integration Adapter; the values are required during the Supplier Update flow.
The integration design assumes that:
Oracle Payables is the source system for merchandise suppliers, their contacts, locations, addresses and other attributes.
You can create suppliers and suppliers' locations in Oracle Payables.
You can maintain the relationship between suppliers, suppliers' locations in Oracle Payables. This integration is a one-way synchronization. Any update to supplier information in RMS is not synchronized with Oracle Payables. The volume of data that is handled by the process depends on the server configuration.
Oracle sends the suppliers in batches based on different criteria.
RFI subscribes to oracle.apps.ap.supplier.event raised by Oracle E-Business Suite. The Oracle E-Business Suite system sends all the information, related to this event, to RFI.
This diagram illustrates the supplier integration flow with EBS:
These services are specific to sync supplier integration flows:
SupplierBES - Business Event Subscriber
Supplier EJB Service
RMS Supplier Service
RIBforRMS Vendor Subscriber
When you initiate the process:
Oracle Payables invokes the SupplierBES Business Event Subscriber whenever a supplier is created or updated.
The Business Event Subscriber Java class SupplierBES extracts all the supplier, supplier sites and supplier contacts from the Oracle E-Business Suite Business tables and moves the supplier, supplier sites and supplier contacts into RFI Database Staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE, RFI_SUPP_CONTACTS_STAGE respectively.
A Configured Timer for RFI Supplier service invokes the Supplier EJB service.
The Supplier EJB service picks up the records from the RFI Database Staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE, RFI_SUPP_CONTACTS_STAGE and transforms the Organization id using ORG_ID_XREF, Currency Code using CURR_CODE_DVM, Language using LANGUAGE_DVM, Payment terms and Freight terms using PAYMENT_TERMS_XREF and FREIGHT_TERMS_XREF respectively.
Then RMS Supplier Service is invoked with the transformed supplier records. If there are any errors in Supplier EJB service, the errors are added to the logs file. The Supplier logs can be viewed using the RFI Adapter Manager screens in RFI Administration GUI.
If the transaction is successful, Retail Id cross references for supplier, supplier site are stored in the cross reference table RFI_XREF_DVM. The cross references updated is SUPPLIER_XREF, SUPPLIER_SITE_XREF respectively.
|
Note: Transformation applies the DVM and invokes create or update web service from RMS. It also updates the cross-reference table after the Retail web service call. |
This diagram illustrates the supplier integration flow with PeopleSoft:
These services are specific to sync supplier integration flows:
SUPPLIERBUSINESSEVENTHANDLERSE - Business Event Handler
Supplier EJB Service
RMS Supplier Service
RIBforRMS Vendor Subscriber
When you initiate the process:
Oracle Payables invokes the "SUPPLIERBUSINESSEVENTHANDLERSE" Business Event service whenever a supplier is created or updated.
The Business Event service extracts all the supplier, supplier sites and supplier contacts from the PeopleSoft Business tables and moves the supplier, supplier sites and supplier contacts into RFI Database Staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE, RFI_SUPP_CONTACTS_STAGE respectively.
A Configured Timer for RFI Supplier service invokes the Supplier EJB service.
The Supplier EJB service picks up the records from the RFI Database Staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE, RFI_SUPP_CONTACTS_STAGE and transforms the Organization id using ORG_ID_XREF, Currency Code using CURR_CODE_DVM, Language using LANGUAGE_DVM, Payment terms and Freight terms using PAYMENT_TERMS_XREF and FREIGHT_TERMS_XREF respectively.
Then RMS Supplier Service is invoked with the transformed supplier records. If there are any errors in Supplier EJB service, the errors are added to the logs file. The Supplier logs can be viewed using the RFI Adapter Manager screens in RFI Administration GUI.
If the transaction is successful, Retail Id cross references for supplier, supplier site are stored in the cross reference table RFI_XREF_DVM. The cross references updated is SUPPLIER_XREF, SUPPLIER_SITE_XREF respectively.
|
Note: Transformation applies the DVM and invokes create or update web service from RMS. It also updates the cross-reference table after the Retail web service call. |
This diagram illustrates the supplier integration flow with Financials Cloud:
These services are specific to sync supplier integration flows:
SUPPLIEREXPORTOUTBOUNDSERVICE - Supplier Export Outbound Service
Supplier EJB Service
RMS Supplier Service
RIBforRMS Vendor Subscriber
Supplier Sequence Incremental Load
When you initiate the process:
Oracle Payables invokes the " SUPPLIEREXPORTOUTBOUNDSERVICE " Business Event service whenever a supplier is created or updated.
The Business Event service extracts all the supplier, supplier sites and supplier contacts from the Cloud Financials message and moves the supplier, supplier sites and supplier contacts into RFI Database Staging tables RFI_SUPP_STAGE, RFI_ SUPP_SITES_STAGE, RFI_SUPP_CONTACTS_STAGE respectively.
A Configured Timer for RFI Supplier service invokes the Supplier EJB service.
The Supplier EJB service picks up the records from the RFI Database Staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE, RFI_SUPP_CONTACTS_STAGE and transforms the Organization id using ORG_ID_XREF, Currency Code using CURR_CODE_DVM, Language using LANGUAGE_DVM, Payment terms and Freight terms using PAYMENT_TERMS_XREF and FREIGHT_TERMS_XREF respectively.
Then RMS Supplier Service is invoked with the transformed supplier records. If there are any errors in Supplier EJB service, the errors are added to the logs file. The Supplier logs can be viewed using the RFI Adapter Manager screens in RFI Administration GUI.
If the transaction is successful, Retail Id cross references for supplier, supplier site are stored in the cross reference table RFI_XREF_DVM. The cross references updated is SUPPLIER_XREF, SUPPLIER_SITE_XREF respectively.
|
Note: Transformation applies the DVM and invokes create or update web service from RMS. It also updates the cross-reference table after the Retail web service call. |
The process integration for supplier information requires the following data:
Suppliers must be assigned to at least one GL business unit (set of books)
For this integration, Oracle suppliers must be created with these status attributes:
Classification of supplier. Other type of vendors such as attorneys, employees, and HCM are not synchronized with Oracle Retail. All suppliers except employee are synced.
The Open for ordering option is selected. If the supplier is approved but is not open for ordering, the RFI layer on the RMS side changes its status to inactive. CFIN and EBS do not have an approval status.
|
Note: Only contacts associated with supplier sites or addresses will be synchronized from Oracle Payables to RMS. |
For Retail to Oracle Financials integration, only certain suppliers with specific criteria are sent to Retail:
Supplier must have at least one or more address or site
Supplier address or site must be a purchasing and/or a payment purpose.
If a supplier has one address or site, it must be both a purchasing and payment purpose.
If a supplier has two or more addresses, one address may be a purchasing site and other address a payment site.
Supplier address or site without a purchasing or payment purpose will not be sent to Retail.
Only Supplier address or site(s) which are active, are interfaced to Retail. Deactivate date for the site should either be NULL or a future-date in Oracle Financials (EBS or PeopleSoft or CFIN).
In the contact details and purpose, the phone area code, fax number and email address are meant for general contact information such as a main operator number or a general company inquiry email address. This information should not be passed to Retail.
In EBS, Under Company Profile --> Contact directory, there is contact information for specific people and their specific contact information. For example, the Account Receivable person's phone #, name, and email address is listed here. This information is passed to Retail.
Supplier Recommendation
Data cleansing prior to Supplier Initial Load: It is recommended that for each supplier, you must select a supplier site as a primary pay site. The values on the primary pay site are used as the default values for any supplier attributes such as payment terms and freight terms that are missing during the integration.
Integration Supplier Logic: Payment/Freight terms and other attributes of parent supplier are defaulted from the primary Pay site. If the supplier does not have a primary pay site, then the first purchasing site is used. If no payment or freight terms are defined at supplier or any supplier purchasing site, and there is no primary pay site, then the supplier transfer fails or errors.
The following diagrams show supplier relation in E-Business Suite and Retail:
The purpose of this flow is to load the existing active Suppliers, Supplier Sites, and Supplier Contacts from Oracle Financials (EBS or PeopleSoft) into Retail. Database link is used to load the supplier data from Oracle Financials (EBS or PeopleSoft) Suite to RFI database and a WebService to load the supplier data from Financials Clouds to RFI database.
These are the steps to load the Supplier details initially from Oracle E-Business Suite to Retail.
A wrapper script supplier_initial_load.sh is provided to load the existing Suppliers, Supplier Sites, and Supplier Contacts from Oracle Financials (EBS or PeopleSoft) Suite to RFI Staging table. These files are available in the RFI pack, folder - - $INSTALL_DIR/retail-financial-integration-solution/<$FINANCIAL>-integration/install/setup-data/dml. Here $ FINANCIAL should be replaced with value ebs-financial or psft-financial based on the Oracle financials application that is being integrated with RFI.
Oracle Financials (EBS or PeopleSoft) to the RFI database. A database link either EBS_RFI_DBLINK or PSFT_RFI_DBLINK should be created between Financials Schema (E-Business Suite APPS schema or PeopleSoft SYSADM schema) and RFI staging schema in RFI Database (created as part of RFI installation step).
Skip this step if the database link is created with default (EBS_RFI_DBLINK or PSFT_RFI_DBLINK) name. If not , Open the sql script supplier_initial_load.sql in a text editor and change the database link values created in the above step.
Skip this step if the database link is created with default (EBS_RFI_DBLINK or PSFT_RFI_DBLINK) name. Open below list of SQL scripts in a text editor and change the database link values.
supplier_initial_load.sql
supplier_sites_initial_load.sql
supplier_contacts_initial_load.sql
Run the supplier_initial_load.sh (available in INSTALL_DIR/retail-financial-integration-solution/<$FINANCIAL>-integration/install/setup-data/dml. Here $ FINANCIAL should be replaced with value ebs-financial or psft-financial based on the Oracle financials application that is being integrated with RFI) script to load data from Oracle Financials (EBS or PeopleSoft) to RFI staging area. This script considers the required environment variables (ORACLE_HOME, INSTALL_DIR) are present.
The script takes two arguments on the command line: APPS schema db login credentials and conversion date. For example:
prompt$ sh supplier_initial_load.sh <username>/<password>@<sid> 10-APR-2008
Suppliers, Supplier Sites, Supplier Contacts records are pushed to RFI Staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE, and RFI_SUPP_CONTACTS_STAGE respectively.
Once the initial supplier details are moved to RFI Staging tables, the records are sent to Retail similar to the Supplier Incremental Loading process.
The purpose of this flow is to load the existing active Suppliers, Supplier Sites, and Supplier Contacts from CFIN into Retail.
These are the steps to load the Supplier details initially from Oracle CFIN to Retail.
Download supplier data from CFIN: The first step is to extract the supplier profile data from CFIN. This can be done as follows.
Login to CFIN. You need to have access to the Oracle ERP Cloud Procurement module and to the Oracle Business Intelligence module to perform this task.
On the left-hand side menu go to Tools and then Reports and Analysis.
There is no standard repost in CFIN to extract the required supplier data. Therefore you need to create a new report for this purpose.
The system will then open the Oracle Business Intelligence on the Create Report screen. Click Cancel on the wizard dialog.
|
Note: You can skip all the previous steps if you have direct access to Oracle Business Intelligence. |
You will need to extract at least two data entities. Supplier contacts ate optional:
Mandatory
Supplier data
Supplier Site data
Optional
Supplier Contacts data
|
Note: If there are no supplier contacts, RFI will generate a supplier contact with description NO CONTACTS as contacts as mandatory in Oracle Retail. |
|
Note: Supplier Site Invoice Currency, Supplier Site Payment Terms and Supplier Site Freight Terms, although not mandatory in CFIN, are required, as mandatory attributes in the extraction. |
To start the extraction process, click New on the Oracle Business Intelligence home page and then, on the drop-down menu Data Model. Please note you need to have the right access privileges to access the creation of new Data Models in Oracle Business Intelligence.
On the Data Model screen, tab Diagram, click SQL Query.
Then, give a name to your Data Model, choose the Data Source which in a standard implementation would be FUSIONDB. Please note that in your implementation this maybe have a different name so choose the Data Source where your Oracle Procurement Cloud module is implemented.
Then, on the SQL Query text area copy and paste the following SQL statement and click OK.
select "POZ_SUPPLIERS_V"."VENDOR_NAME" as "VENDOR_NAME",
"POZ_SUPPLIERS_V"."VENDOR_ID" as "VENDOR_ID",
"POZ_SUPPLIERS_V"."SEGMENT1" as "SUPPLIER_NUM",
"POZ_SUPPLIERS_V"."PARTY_ID" as "PARTY_ID",
"POZ_SUPPLIERS_V"."ENABLED_FLAG" as "ENABLED_FLAG",
"POZ_SUPPLIERS_V"."VENDOR_TYPE_LOOKUP_CODE" as "VENDOR_TYPE_LOOKUP_CODE",
"POZ_SUPPLIERS_V"."VENDOR_NAME_ALT" as "VENDOR_NAME_ALT",
"POZ_SUPPLIERS_V"."DUNS_NUMBER_C" as "DUNS_NUMBER_C",
"POZ_SUPPLIERS_V"."CUSTOMER_NUM" as "CUSTOMER_NUM",
"POZ_SUPPLIERS_V"."ONE_TIME_FLAG" as "ONE_TIME_FLAG",
"POZ_SUPPLIERS_V"."PARENT_PARTY_ID" as "PARENT_PARTY_ID",
"POZ_SUPPLIERS_V"."PARENT_VENDOR_ID" as "PARENT_VENDOR_ID",
"POZ_SUPPLIERS_V"."SET_OF_BOOKS_ID" as "SET_OF_BOOKS_ID",
"POZ_SUPPLIERS_V"."ORGANIZATION_TYPE_LOOKUP_CODE" as "ORGANIZATION_TYPE_LOOKUP_CODE",
"POZ_SUPPLIERS_V"."START_DATE_ACTIVE" as "START_DATE_ACTIVE",
"POZ_SUPPLIERS_V"."END_DATE_ACTIVE" as "END_DATE_ACTIVE",
"POZ_SUPPLIERS_V"."ALLOW_AWT_FLAG" as "ALLOW_AWT_FLAG",
"POZ_SUPPLIERS_V"."AWT_GROUP_ID" as "AWT_GROUP_ID",
"POZ_SUPPLIERS_V"."BANK_CHARGE_BEARER" as "BANK_CHARGE_BEARER",
"POZ_SUPPLIERS_V"."NI_NUMBER" as "NI_NUMBER",
"POZ_SUPPLIERS_V"."BUSINESS_RELATIONSHIP" as "BUSINESS_RELATIONSHIP",
"POZ_SUPPLIERS_V"."CORPORATE_WEBSITE" as "CORPORATE_WEBSITE",
"POZ_SUPPLIERS_V"."TAX_REPORTING_NAME" as "TAX_REPORTING_NAME"
from "FUSION"."POZ_SUPPLIERS_V" "POZ_SUPPLIERS_V"
where "POZ_SUPPLIERS_V"."BUSINESS_RELATIONSHIP" ='SPEND_AUTHORIZED'
This will extract all the Suppliers that are approved for transactions on the system (i.e. Business Relationship = "SPEND_AUTHORIZED").
|
Note: You need to replace the name of the schema on the SQL statement (i.e. "from"FUSION"."POZ_SUPPLIERS_V" "POZ_SUPPLIERS_V") with the name of the schema of your implementation. |
The system will generate the data model and display it on the screen. Save the data model to your personal folder.
Click on the Data tab, option View and the option Table View.
Save the sample data by clicking on Save As Sample Data.
Then, save the data model again and click the option Create Report.
Then, the system will display the Create Report screen. The data model you just created will appear in the Data Model field. Then click on the option Use Report Editor option and then Finish. At this point the system will ask you to save your report.
After you save the report, the system will display the Report Editor. Choose the option Generate RTF layout based on selected Data Model.
Generate this layout as your template (give it a name and click Generate).
The system will generate the report template. Click on the option View List.
On the Output Formats column tick the Data option and also the Apply Style Template option.
Save the report and then click on the View Report option. The system will display the report and the related data. Then export the data by clicking on the option Export and then Data.
The system will generate a XML file and will prompt you to save it to your localhost. For this extraction the procedure is concluded.
The next extraction is for Supplier Sites data. Execute steps 1. to 8.
On the SQL Query text area copy and paste the following SQL statement and click OK:
select "POZ_SUPPLIERS_V"."SEGMENT1" as "SUPPLIER_NUM",
"POZ_SUPPLIER_SITES_V"."VENDOR_SITE_ID" as "VENDOR_SITE_ID",
"POZ_SUPPLIER_SITES_V"."INACTIVE_DATE" as "INACTIVE_DATE",
"POZ_SUPPLIER_SITES_V"."VENDOR_ID" as "VENDOR_ID",
"POZ_SUPPLIER_SITES_V"."PRC_BU_ID" as "PRC_BU_ID",
"POZ_SUPPLIER_SITES_V"."PARTY_SITE_ID" as "PARTY_SITE_ID",
"POZ_SUPPLIER_SITES_V"."VENDOR_SITE_CODE" as "VENDOR_SITE_CODE",
"POZ_SUPPLIER_SITES_V"."PURCHASING_SITE_FLAG" as
"PURCHASING_SITE_FLAG",
"POZ_SUPPLIER_SITES_V"."RFQ_ONLY_SITE_FLAG" as "RFQ_ONLY_SITE_FLAG",
"POZ_SUPPLIER_SITES_V"."PAY_SITE_FLAG" as "PAY_SITE_FLAG",
"AP_TERMS_TL"."NAME" as "TERMS_ID",
"POZ_SUPPLIER_SITES_V"."EXCLUDE_FREIGHT_FROM_DISCOUNT" as
"EXCLUDE_FREIGHT_FROM_DISCOUNT",
"POZ_SUPPLIER_SITES_V"."PAY_ON_CODE" as "PAY_ON_CODE",
"POZ_SUPPLIER_SITES_V"."DEFAULT_PAY_SITE_ID" as
"DEFAULT_PAY_SITE_ID",
"POZ_SUPPLIER_SITES_V"."MATCH_OPTION" as "MATCH_OPTION",
"POZ_SUPPLIER_SITES_V"."PCARD_SITE_FLAG" as "PCARD_SITE_FLAG",
"POZ_SUPPLIER_SITES_V"."COUNTRY_OF_ORIGIN_CODE" as
"COUNTRY_OF_ORIGIN_CODE",
"POZ_SUPPLIER_SITES_V"."EMAIL_ADDRESS" as "EMAIL_ADDRESS",
"POZ_SUPPLIER_SITES_V"."PRIMARY_PAY_SITE_FLAG" as
"PRIMARY_PAY_SITE_FLAG",
"POZ_SUPPLIER_SITES_V"."PARTY_SITE_NAME" as "PARTY_SITE_NAME",
"POZ_SUPPLIER_SITES_V"."DUNS_NUMBER" as "DUNS_NUMBER",
"POZ_SUPPLIER_SITES_V"."LANGUAGE_CODE" as "LANGUAGE_CODE",
"POZ_SUPPLIER_SITES_V"."ADDRESS_LINE1" as "ADDRESS_LINE1",
"POZ_SUPPLIER_SITES_V"."ADDRESS_LINE2" as "ADDRESS_LINE2",
"POZ_SUPPLIER_SITES_V"."ADDRESS_LINE3" as "ADDRESS_LINE3",
"POZ_SUPPLIER_SITES_V"."ADDRESS_LINE4" as "ADDRESS_LINE4",
"POZ_SUPPLIER_SITES_V"."ADDRESS_LINES_ALT" as "ADDRESS_LINES_ALT",
"POZ_SUPPLIER_SITES_V"."CITY" as "CITY",
"POZ_SUPPLIER_SITES_V"."STATE" as "STATE",
"POZ_SUPPLIER_SITES_V"."COUNTY" as "COUNTY",
"POZ_SUPPLIER_SITES_V"."COUNTRY" as "COUNTRY",
"POZ_SUPPLIER_SITES_V"."ZIP" as "ZIP",
"POZ_SUPPLIER_SITES_V"."PROVINCE" as "PROVINCE",
"POZ_SUPPLIER_SITES_V"."TP_HEADER_ID" as "TP_HEADER_ID",
"POZ_SUPPLIER_SITES_V"."VENDOR_SITE_CODE_ALT" as
"VENDOR_SITE_CODE_ALT",
"POZ_SUPPLIER_SITES_V"."ATTENTION_AR_FLAG" as "ATTENTION_AR_FLAG",
"POZ_SUPPLIER_SITES_V"."AREA_CODE" as "AREA_CODE",
"POZ_SUPPLIER_SITES_V"."PHONE" as "PHONE",
"POZ_SUPPLIER_SITES_V"."CUSTOMER_NUM" as "CUSTOMER_NUM",
"POZ_SUPPLIER_SITES_V"."FREIGHT_TERMS_LOOKUP_CODE" as
"FREIGHT_TERMS_LOOKUP_CODE",
"POZ_SUPPLIER_SITES_V"."FOB_LOOKUP_CODE" as "FOB_LOOKUP_CODE",
"POZ_SUPPLIER_SITES_V"."FAX" as "FAX",
"POZ_SUPPLIER_SITES_V"."FAX_AREA_CODE" as "FAX_AREA_CODE",
"POZ_SUPPLIER_SITES_V"."TERMS_DATE_BASIS" as "TERMS_DATE_BASIS",
"POZ_SUPPLIER_SITES_V"."PAY_GROUP_LOOKUP_CODE" as
"PAY_GROUP_LOOKUP_CODE",
"POZ_SUPPLIER_SITES_V"."PAYMENT_PRIORITY" as "PAYMENT_PRIORITY",
"POZ_SUPPLIER_SITES_V"."PAY_DATE_BASIS_LOOKUP_CODE" as
"PAY_DATE_BASIS_LOOKUP_CODE",
"POZ_SUPPLIER_SITES_V"."INVOICE_CURRENCY_CODE" as
"INVOICE_CURRENCY_CODE",
"POZ_SUPPLIER_SITES_V"."PAYMENT_CURRENCY_CODE" as
"PAYMENT_CURRENCY_CODE",
"POZ_SUPPLIER_SITES_V"."HOLD_ALL_PAYMENTS_FLAG" as
"HOLD_ALL_PAYMENTS_FLAG",
"POZ_SUPPLIER_SITES_V"."HOLD_FUTURE_PAYMENTS_FLAG" as
"HOLD_FUTURE_PAYMENTS_FLAG",
"POZ_SUPPLIER_SITES_V"."HOLD_REASON" as "HOLD_REASON",
"POZ_SUPPLIER_SITES_V"."HOLD_UNMATCHED_INVOICES_FLAG" as
"HOLD_UNMATCHED_INVOICES_FLAG",
"POZ_SUPPLIER_SITES_V"."PHONE_COUNTRY_CODE" as "PHONE_COUNTRY_CODE",
"POZ_SUPPLIER_SITES_V"."PHONE_EXTENSION" as "PHONE_EXTENSION",
"POZ_SUPPLIER_SITES_V"."FAX_COUNTRY_CODE" as "FAX_COUNTRY_CODE",
"POZ_SUPPLIER_SITES_V"."TAX_COUNTRY_CODE" as "TAX_COUNTRY_CODE",
"POZ_SUPPLIER_SITES_V"."PAY_ON_USE_FLAG" as "PAY_ON_USE_FLAG",
"POZ_SUPPLIERS_V"."BUSINESS_RELATIONSHIP" as "BUSINESS_RELATIONSHIP",
"POZ_SUPPLIER_SITES_V"."LOCATION_ID" as "LOCATION_ID"
from "FUSION".POZ_SUPPLIERS_V" "POZ_SUPPLIERS_V",
"FUSION".POZ_SUPPLIER_SITES_V" "POZ_SUPPLIER_SITES_V",
"FUSION"."AP_TERMS_TL" "AP_TERMS_TL"
where "POZ_SUPPLIER_SITES_V"."VENDOR_ID"=POZ_SUPPLIERS_V.VENDOR_ID
and ("POZ_SUPPLIER_SITES_V"."PURCHASING_SITE_FLAG"='Y' or
"POZ_SUPPLIER_SITES_V"."PAY_SITE_FLAG"='Y')
and "POZ_SUPPLIER_SITES_V"."TERMS_ID" = "AP_TERMS_TL"."TERM_ID"
and "AP_TERMS_TL"."LANGUAGE"='US'
and "POZ_SUPPLIERS_V"."BUSINESS_RELATIONSHIP" ='SPEND_AUTHORIZED'
This will extract only Purchasing and Payment Supplier Sites.
|
Note: You need to replace the name of the schema on the SQL statement (i.e. "from"FUSION"."POZ_SUPPLIERS_V" "POZ_SUPPLIERS_V" ) by the name of the schema of your implementation. |
Execute steps 10. to 16. Once you perform those steps the system will show the Supplier Sites report. Then execute steps 17 and 18.
The next extraction is for Supplier Contacts data, which is optional. Execute steps 1 to 8.
On the SQL Query text area copy & paste the following SQL statement and click OK:
select "POZ_SUPPLIER_CONTACTS_V"."PARTY_NAME" as "PARTY_NAME",
"POZ_SUPPLIER_CONTACTS_V"."FULL_NAME" as "FULL_NAME",
"POZ_SUPPLIER_CONTACTS_V"."VENDOR_CONTACT_ID" as "VENDOR_CONTACT_ID",
"POZ_SUPPLIER_CONTACTS_V"."VENDOR_SITE_ID" as "VENDOR_SITE_ID",
"POZ_SUPPLIER_CONTACTS_V"."VENDOR_ID" as "VENDOR_ID",
"POZ_SUPPLIER_CONTACTS_V"."FIRST_NAME" as "FIRST_NAME",
"POZ_SUPPLIER_CONTACTS_V"."MIDDLE_NAME" as "MIDDLE_NAME",
"POZ_SUPPLIER_CONTACTS_V"."LAST_NAME" as "LAST_NAME",
"POZ_SUPPLIER_CONTACTS_V"."PREFIX" as "PREFIX",
"POZ_SUPPLIER_CONTACTS_V"."TITLE" as "TITLE",
"POZ_SUPPLIER_CONTACTS_V"."AREA_CODE" as "AREA_CODE",
"POZ_SUPPLIER_CONTACTS_V"."PHONE" as "PHONE",
"POZ_SUPPLIER_CONTACTS_V"."PHONE_COUNTRY_CODE" as "PHONE_COUNTRY_CODE",
"POZ_SUPPLIER_CONTACTS_V"."PHONE_EXTENSION" as "PHONE_EXTENSION",
"POZ_SUPPLIER_CONTACTS_V"."MAIL_STOP" as "MAIL_STOP",
"POZ_SUPPLIER_CONTACTS_V"."CONTACT_NAME_ALT" as "CONTACT_NAME_ALT",
"POZ_SUPPLIER_CONTACTS_V"."FIRST_NAME_ALT" as "FIRST_NAME_ALT",
"POZ_SUPPLIER_CONTACTS_V"."LAST_NAME_ALT" as "LAST_NAME_ALT",
"POZ_SUPPLIER_CONTACTS_V"."DEPARTMENT" as "DEPARTMENT",
"POZ_SUPPLIER_CONTACTS_V"."JOB_TITLE" as "JOB_TITLE",
"POZ_SUPPLIER_CONTACTS_V"."PERSON_NAME_SUFFIX" as "PERSON_NAME_SUFFIX",
"POZ_SUPPLIER_CONTACTS_V"."ADMINISTRATIVE_CONTACT" as "ADMINISTRATIVE_CONTACT",
"POZ_SUPPLIER_CONTACTS_V"."STATUS" as "STATUS",
"POZ_SUPPLIER_CONTACTS_V"."EMAIL_ADDRESS" as "EMAIL_ADDRESS",
"POZ_SUPPLIER_CONTACTS_V"."ALT_AREA_CODE" as "ALT_AREA_CODE",
"POZ_SUPPLIER_CONTACTS_V"."ALT_PHONE" as "ALT_PHONE",
"POZ_SUPPLIER_CONTACTS_V"."FAX_AREA_CODE" as "FAX_AREA_CODE",
"POZ_SUPPLIER_CONTACTS_V"."FAX" as "FAX",
"POZ_SUPPLIER_CONTACTS_V"."FAX_COUNTRY_CODE" as "FAX_COUNTRY_CODE",
"POZ_SUPPLIER_CONTACTS_V"."FAX_EXTENSION" as "FAX_EXTENSION",
"POZ_SUPPLIER_CONTACTS_V"."MOBILE_COUNTRY_CODE" as "MOBILE_COUNTRY_CODE",
"POZ_SUPPLIER_CONTACTS_V"."MOBILE_AREA_CODE" as "MOBILE_AREA_CODE",
"POZ_SUPPLIER_CONTACTS_V"."MOBILE" as "MOBILE",
"POZ_SUPPLIER_CONTACTS_V"."INACTIVE_DATE" as "INACTIVE_DATE",
"POZ_SUPPLIER_CONTACTS_V"."ORG_PARTY_SITE_ID" as "ORG_PARTY_SITE_ID",
"POZ_SUPPLIER_CONTACTS_V"."PARTY_SITE_ID" as "PARTY_SITE_ID",
"POZ_SUPPLIER_SITES_V"."PURCHASING_SITE_FLAG" as "PURCHASING_SITE_FLAG",
"POZ_SUPPLIER_SITES_V"."VENDOR_SITE_CODE" as "VENDOR_SITE_CODE",
"POZ_SUPPLIER_SITES_V"."PAY_SITE_FLAG" as "PAY_SITE_FLAG",
"POZ_SUPPLIERS_V"."SEGMENT1" as "SUPPLIER_NUM",
"POZ_SUPPLIERS_V"."BUSINESS_RELATIONSHIP" as "BUSINESS_RELATIONSHIP"
from "FUSION"."POZ_SUPPLIERS_V" "POZ_SUPPLIERS_V",
"FUSION"."POZ_SUPPLIER_SITES_V" "POZ_SUPPLIER_SITES_V",
"FUSION"."POZ_SUPPLIER_CONTACTS_V" "POZ_SUPPLIER_CONTACTS_V"
where "POZ_SUPPLIER_CONTACTS_V"."VENDOR_SITE_ID" =POZ_SUPPLIER_SITES_V.VENDOR_SITE_ID
and "POZ_SUPPLIER_CONTACTS_V"."VENDOR_ID" =POZ_SUPPLIERS_V.VENDOR_ID
and ("POZ_SUPPLIER_SITES_V"."PURCHASING_SITE_FLAG" ='Y'
or "POZ_SUPPLIER_SITES_V"."PAY_SITE_FLAG" ='Y')
and "POZ_SUPPLIERS_V"."BUSINESS_RELATIONSHIP" ='SPEND_AUTHORIZED'
Execute steps 10. to 16. Once you perform those steps the system will show the Supplier Contacts report. Then execute steps 17 and 18.
Upload Suppliers data to RFI: A wrapper script supplier_initial_load.sh is provided to load the existing Suppliers, Suppliers Sites and Contacts from flat files previously extracted from Cloud Financials to RFI staging tables. This file is available in the RFI pack, folder $INSTALL_DIR/etl-based-integration/retail-to-cfin-etl-flows/setup-data/dml.
Before running the supplier_initial_laod.sh script you should take into consideration the following notes:
If the previous version of supplier_inital_load.sh have been executed perform the following commands into your RFI database:
o ALTER TABLE RFI_SUPP_STAGE MODIFY VENDOR_ID VARCHAR2(50); o ALTER TABLE RFI_SUPP_SITES_STAGE MODIFY VENDOR_ID VARCHAR2(50);
The staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE and RFI_SUPP_CONTACTS_STAGE should be present in the RFI database and empty.
INSTALL_DIR and JAVA_HOME (JDK 1.8 or above) environment variables needs to be set.
Ex. INSTALL_DIR=/home/unix_user/staging_dir/retail-financial-integration-solution
This script is meant to be executed one time and one time only, if for some reason the loading process into RFI fails, the following steps need to be performed to run the script again:
Drop all temporary tables that are created during script runtime.
Truncate the staging tables if for some reason only part of the data were loaded.
Correct any errors in data set that you may encounter during load.
As previously mentioned, this script will load Suppliers, Suppliers Sites and Contacts into RFI being Supplier Sites Contacts the only data set optional to be imported. Since some Site Contacts information is mandatory in Oracle Retail, if you choose to do not import them into RFI, default ones will be created for each site.
Run supplier_initial_load.sh (./supplier_inital_load.sh).
The script will request some information to perform correctly:
Oracle RFI database server hostname (rfi_db_server)
Oracle RFI database port (1521)
Oracle RFI database service name (rfipdb)
Oracle RFI database username (rfi_db_user)
Oracle RFI database password
File path for Supplier data extracted from CFIN (No white spaces should be present in the file path)
File path for Supplier Sites data extracted from CFIN (No white spaces should be present in the file path)
If you chose to integrate Contacts, file path for Supplier Site Contacts data extracted from CFIN (No white spaces should be present in the file path)
Temporary tables will be created.
Data will be loaded into Temporary tables.
Suppliers, Supplier Sites, Supplier Contacts records are pushed to RFI Staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE, and RFI_SUPP_CONTACTS_STAGE respectively and temporary tables dropped.
The number of rows inserted into Temporary tables should be the same for staging tables.
Once the initial supplier details are moved to RFI Staging tables, the records are sent to Retail similar to the Supplier Incremental Loading process.
The RFI Application supports passing custom payload data to the Supplier Integration Service.
The additional information that has to be passed from E-Business suite should be captured in Attribute Columns (Attribute1 to Attribute15) in AP_SUPPLIERS, AP_SUPPLIER_SITES_ALL, AP_SUPPLIER_CONTACTS table.
The Attribute columns are pushed to the RFI Supplier staging tables RFI_SUPP_STAGE, RFI_SUPP_SITES_STAGE, RFI_SUPP_CONTACTS_STAGE by the Business Event Subscriber Java class SupplierBES.
A Java interface RfiSupplierCustomPayload is provided in the RFI application.
A Java class needs to be written by implementing the above interface RfiSupplierCustomPayload and the additional custom payload attributes (Attribute1 to Attribute15) can be added to SupplierDesc object. A sample implementation class RfiSupplierCustomPayloadImpl is provided in the RFI application
In RfiSpringBeans.xml, an entry for the implementation class has to be provided.
<bean id="rfiSupplierCustomPayloadBean" class="<Implementation Class Name>"> </bean>
This section provides an overview of the process integration for initial loading and incremental synchronization of payment terms between Oracle General Ledger (GL) and the Oracle Retail Merchandising System (RMS) and discusses:
Payment Terms integration details
Data requirements
Extensions
This section is applicable only if your Oracle GL system is PeopleSoft and not when it is Oracle E-Business Suite.
This integration is not supported by RFI for CFIN. Instead, users are expected to upload any incremental changes to exchange rates using the RMS Foundation Data Load functionality.
In the integrated environment, PeopleSoft Enterprise Financials acts as a payable and accounting engine with RMS for supplier payment, merchandise write-offs, and prepaid adjustments. It eliminates the need for manual reentry of the reference data from PeopleSoft Payables to the RMS. This benefits retailers by reducing the labor cost of double entry and providing more accurate and effective payment of invoices, payment adjustments, and accounting records.
PeopleSoft Payables is the source of valid payment terms. RMS uses payment terms to apply the correct payment terms to a supplier or purchase order and ensures correct timing of payment and application of payment term discounts.
The payment terms integration synchronizes payment terms information from PeopleSoft Payables to RMS through these integration flows:
Load initial payment term from PeopleSoft Payables to RMS: Enables the loading of all current effective dated payment terms from PeopleSoft Payables to RMS for a new instance (logical or physical) of RMS.
Incremental creation and updates of current effective dated payment term from PeopleSoft Payables to RMS: Enables the synchronization of incremental creation and updates of the payment terms from PeopleSoft Payables to RMS.
For more information about payment terms, see PeopleSoft Enterprise Source to Settle Common Information 9.2 PeopleBook, "Defining Procurement Options," Defining Payment Terms
This integration is not a point-to-point integration between PeopleSoft Payables and RMS. An RFI layer serves as an intermediate thin layer of application between PeopleSoft Payables and RMS. As a part of the payment term integration, PeopleSoft Payables sends the payment term to the RFI layer and the RFI layer delivers the information to RMS. The RFI layer performs message filtering, message transformation, and message routing.
The integration design assumes that:
Oracle Retail can handle only single tier payment terms.
PeopleSoft Payables supports multiple tier payment terms for installment payments.
If the PeopleSoft Payables inactivates a payment term and the end date is not before the current system date or vdate in Oracle Retail, it is rejected by Oracle Retail.
All Oracle Retail business units have the same set of payment terms.
Only the Sync operation accepts a list (or collection) message. The Create and Update operations only accept a single message for new rows created or updated in PeopleSoft Payables.
PeopleSoft Payables can have the same payment terms code in different setIDs. However, Oracle Retail only supports global payment terms. PeopleSoft sends a common value that represents the setID/payment terms code combination. It is recommended that the same payment terms code value not be set up under multiple setID when using this integration
PeopleSoft payment terms have a record in the Oracle Retail language prior to the Sync operation being performed. The last update to a payment term in PeopleSoft application should be in the Oracle Retail language, so that the translatable fields appear correct in Oracle Retail.
Oracle Retail does not allow users to create and update payment terms in RMS.
Payment term integration occurs prior to supplier initial load and manual setup of freight term in RMS.
Oracle Retail does not support triangulation of currency exchange rates
Payment Terms changes in Retail are not passed to PeopleSoft and the sync is one-direction only.
The diagram illustrates the Payment terms integration flow:-
The integration flow uses the following services:
PAYTERMBUSINESSEVENTHANDLERSER- Business Service Handler
Payment Terms EJB Service
RMS Payment Terms Service
RIBforRMS Payterms Subscriber
When the process is initiated, the following steps would occur:
PeopleSoft invokes the Business Event service "PAYTERMBUSINESSEVENTHANDLERSER" whenever a payment terms is created or loaded into PeopleSoft which in turn calls the service in RFI.
The Business Event service extracts all the payment terms details from PeopleSoft tables and moves the details into RFI Database Staging table RFI_TERMS_HEAD and RFI_TERMS_DETAIL.
A Configured Timer for payment terms service invokes the Payment Terms EJB service.
The Payment Terms EJB service picks up the records from the RFI Database Staging table RFI_TERMS_HEAD & RFI_TERMS_DETAIL.
Then RMS Pay terms Service is invoked with the payment terms record.
If there are any errors in Payment terms EJB service, the errors are added to the logs file. The Payment terms logs can be viewed using the RFI Adapter Manager screens in RFI Administration GUI.
The purpose of this flow is to load the existing payment terms from PeopleSoft into Retail.
Use the following steps to load the payment terms rates initially from Oracle Financials (only for PeopleSoft integration) to Retail. Use the configured database link to load the payment terms data from the Oracle Financials (Only for PeopleSoft) to the RFI database.
A wrapper script payterms_initial_load.sh is provided to load the existing payment terms from PeopleSoft to the RFI Staging table. This file is available in the RFI pack, folder - $INSTALL_DIR/retail-financial-integration-solution/ psft-financial /install/setup-data/dml.
Use the database link to load the payment terms data from PeopleSoft to the RFI database. A database link is PSFT_RFI_DBLINK should be created between PeopleSoft SYSADM schema) and RFI staging schema in RFI Database (created as part of RFI installation step).
Skip this step if the database link is created with default PSFT_RFI_DBLINK name. If not, Open the sql script payterms_initial_load.sql in a text editor and change the database link values created in the above step.
Run the payterms_initial_load.sh (available $INSTALL_DIR/retail-financial-integration-solution/psft-financial /install/setup-data/dml) script to load data from PeopleSoft)to RFI staging area. This script considers the required environment variables (ORACLE_HOME, INSTALL_DIR) are present.
The script takes two arguments on the command line: Financials system schema database login credentials and conversion_date (from when the payment terms have to be pushed to Retail system). For example:
prompt$ sh payterms_initial_load.sh <username>/<password>@<sid> 02-AUG-2013 sh payterms_initial_load.sh <username>/<password>@<sid> 02-AUG-2013
Once the initial payment terms data is moved to the RFI Staging table, RFI_TERMS_HEAD and RFI_TERMS_DETAIL, the records are sent to Retail similar to the payment terms Incremental Loading process.
This section provides an overview Drill back and drill forward integration. This integration helps the PeopleSoft and Oracle Retail applications' users to access information about the financial transactions from the integrated systems in a seamless and intuitive manner..
This section is applicable only if your Oracle financials application is PeopleSoft and not when it is Oracle E-Business Suite.
In the integrated environment, drill forward and drill backward provides the users end-to-end traceability between PeopleSoft Payables and General Ledger back to Oracle Retail merchandising System (RMS), oracle Retail Sales Audit (ReSA), and Oracle Retail Invoice Matching (ReIM). Drill back and drill forward supports navigation at the User Interface level from the PeopleSoft applications to the Oracle Retail applications and vice versa for the transactions that are integrated between the two systems.
The drill back and drill forward feature enables the PeopleSoft and Oracle Retail applications' users to trace and investigate the financial transactions by:
Drilling back from a journal entry in PeopleSoft General Ledger (GL) to RMS, ReSA, and ReIM to find the source of the journal or from a voucher in PeopleSoft Payables to an invoice in ReIM.
Drilling forward from RMS, ReSA, or ReIM to PeopleSoft GL and Payables for detailed information about posted transactions for a source transaction.
These Oracle Retail and PeopleSoft applications participate in the integrated environment.
RMS Stock Ledger: This application is the source of transactions for the valuation of sellable inventory located in stores, warehouses, and distribution centers.ReSA: This application is the source of transactions for recognition of revenue and cash posting.
ReIM: This application is the source of invoices to be paid, credit adjustments, debit memos, write-offs, and invoice adjustments.
PeopleSoft GL: This application posts the accounting entries from the Oracle Retail applications (RMS, ReSA, and ReIM) to designated ledgers.
PeopleSoft Payables: This application processes invoices for payment and posts the results in PeopleSoft GL based on the templates and account coding.
The drill back and drill forward actions support these flows:
Drill back from ledger entry to revenue recognition transactions for information about revenue recognition transactions using the ledger entry.
Drill back from ledger entry to inventory valuation transactions for information about inventory valuation transactions using the ledger entry.
Drill back from ledger entry to aged open receipt write-of transactions for information about aged open-receipt write-off transactions using the ledger entry.
Drill back from ledger entry to matched prepaid invoice transactions for information about matched pre-paid invoice transactions using the ledger entry.
Drill back from payment voucher to matched invoice, credit notes, debit memos, and rebates for details of matched invoice, credit notes, debit memos, and rebates from the related payment voucher.
Drill forward from sales audit transaction to ledger for details of a particular ledger entry using the sales audit transaction information.
Drill forward from stock ledger transaction to ledger for details of a particular ledger entry using the stock ledger transaction information.
Drill forward from matched invoice to payment voucher for details of a particular payment voucher from the related matched invoice.
Drill forward from credit note, debit memo, or rebate to payment voucher for details of a particular payment voucher from the related credit note, debit memo, or rebate.
Drill forward from aged open receipt write-off transactions to ledger for details of a particular ledger entry from the aged open receipt write-off transaction information.
Drill forward from matched prepaid invoice to ledger for details of a particular ledger entry from the matched prepaid invoice information.
Before performing this integration, ensure that:
PeopleSoft GL and Payables and Oracle Retail implementations are live with all setup and configurations.
Accounting entries and invoice data have been integrated from Oracle Retail to the PeopleSoft applications.
BI Publisher has been installed and configured for Oracle Retail.
Invoice Oracle Data Integrator (ODI) flow is run.
The integration design assumes that:
The services are properly configured during RFI installation
Two systems are supported as target systems for forming the URL: Oracle Retail and PeopleSoft. PeopleSoft configuration is done in the RFI configuration file.
The diagram illustrates the drill forward and drill backward flow:-
The integration discusses uses the following services:
Drill Back Service
Drill Forward Service
Overall Flow
The drill back enables the user to:
Query ReIM using the payment voucher ID from the PeopleSoft Payables user interface (UI) to find invoices, credit notes, and debit memos that are used to generate the payment voucher.
Query RMS and ReSA from the PeopleSoft GL UI to find the source transactions that generated the journal entry in the ledger for revenue recognition and cash deposit postings.
Query RMS stock ledger from the PeopleSoft GL UI to find the source transactions for inventory valuation postings.
Query ReIM from the PeopleSoft GL UI to find the source transactions for aged receipt write-offs, manually paid invoices, and matched pre-paid invoices.
This integration uses the DrillBackForwardURLService.
The drill forward enables the user to:
Query PeopleSoft Payables from the ReIM UI using the invoice, credit note, debit memo, or document ID to query the resulting payment voucher.
Query the PeopleSoft GL from ReIM UI for aged receipt write-offs and prepaid invoices to audit the impact on the ledgers. For aged receipt write-offs, the receipt ID is used to access the PeopleSoft GL transaction. For prepaid invoices, the document ID is used.
Query PeopleSoft GL from the ReSA UI using the sales transaction document ID to audit the journal entry impact of the financials.
Query PeopleSoft GL from RMS UI using the stock ledger document ID or fixed deal reference ID to audit the journal entry impact of the financials.
This integration uses the DrillBackForwardURLService.
When the drill back or drill forward process in initiated:
The source application calls the DrillBackForwardURLService web service to create a URL for the target system.
The source application passes an array of parameter name and value pairs and the name of the web service requestor (RETAIL or PSFT).
The web service invokes the DrillBackForwardURL Service hosted in RFI
The RFI Service based on the input parameter name and the web service requestor name looks up the RFI configuration properties file to construct the URL
If the target system is PSFT, the client implementation-specific information (host, port) file is fetched. A URL with retrieved information is constructed.
If the target system is RETAIL, then Oracle Retail web service is called to obtain the URL. The URL is returned to the caller of the service.
If an error occurs, a fault message is generated.
This table contains the service solution components that are used in the process integration:
Table 2-1 Service Solution Components
| DrillBackForwardURLService | RFI | Oracle Retail or PeopleSoft | INVOICE_ID OR ACCOUNTING ENTRY |
|---|---|---|---|
|
Invoice Document Report |
Retail BI Publisher |
PeopleSoft Payables UI |
BI Publisher URL |
|
Aged Receipts Transaction Report |
Retail BI Publisher |
PeopleSoft GL UI |
BI Publisher URL |
|
Matched Pre Paid Transaction Report |
Retail BI Publisher |
PeopleSoft GL UI |
BI Publisher URL |
|
Sales Audit Transaction Report |
Retail BI Publisher |
PeopleSoft GL UI |
BI Publisher URL |
|
Stock Ledger Transaction Report |
Retail BI Publisher |
PeopleSoft GL UI |
BI Publisher URL |
|
Stock Ledger Line |
Retail BI Publisher |
PeopleSoft GL UI |
BI Publisher URL |
DrillBackForwardURLService: This web service enables users to navigate from the PeopleSoft applications to Oracle Retail (drill back) and Oracle Retail to PeopleSoft applications (drill forward).The source application (Oracle Retail or PeopleSoft application) sends a synchronous request with source system name and an array of parameter name value pairs to this service. This service returns a URL based on the given scenario (drill back or drill forward). The source application launches the returned URL to open the target application in a web browser. If an error occurs, a fault message is generated.
Invoice Document Report: PeopleSoft Payables invokes the DrillBackForwardURLService in RFI for requesting a report on the invoices, credit notes, debit memos, and rebates from ReIM that made up the payment voucher. The RFI layer passes the URL to PeopleSoft Payables UI. PeopleSoft Payables UI initiates a report on the BIP server using the URL created by RFI. BIP generates a report for the requesting user.
Invoice Document Report: PeopleSoft Payables invokes the DrillBackForwardURLService in RFI for requesting a report on the invoices, credit notes, debit memos, and rebates from ReIM that made up the payment voucher. The RFI layer passes the URL to PeopleSoft Payables UI. PeopleSoft Payables UI initiates a report on the BIP server using the URL created by RFI.BIP generate a report for the requesting user.
Aged Receipts Transaction Report: PeopleSoft GL invokes the DrillBackForwardURLService for requesting a report on the aged receipt write-off transactions generated from ReIM that made up the journal entry. The RFI layer passes the URL to PeopleSoft GL UI. PeopleSoft GL UI initiates a report on the BIP server using the URL created by RFI. BIP generates a report for the requesting user.
Matched Pre-Paid Transaction Report: PeopleSoft GL invokes the DrillBackForwardURLService for requesting a report on the matched pre-paid transactions generated from ReIM that made up the journal entry. The RFI layer passes the URL to PeopleSoft GL UI. PeopleSoft GL UI initiates a report on the BIP server using the URL created by RFI. BIP generates a report for the requesting user
Sales Audit Transaction Report: PeopleSoft GL invokes the DrillBackForwardURLService for requesting a report on transactions generated from ReSA that made up the journal entry. The RFI layer passes the URL to PeopleSoft GL. PeopleSoft GL UI initiates a report on the BIP server using the URL created by RFI. BIP generates a report for the requesting user.
Stock Ledger Transaction Report: PeopleSoft GL invokes the DrillBackForwardURLService for requesting a report on inventory valuation transaction generated from RMS stock ledger that made up the journal entry. The RFI layer passes the URL to PeopleSoft GL UI. PeopleSoft GL UI initiates a report on the BIP server using the URL created by RFI. BIP generates a report for the requesting user.
Fixed Deal Transaction Report: PeopleSoft GL invokes the DrillBackForwardURLService for requesting a report on fixed deal transaction generated from RMS stock ledger that made up the journal entry. The RFI layer passes the URL to PeopleSoft GL UI. PeopleSoft GL UI initiates a report on the BIP server using the URL created by RFI. BIP generates a report for the requesting user.
