Reviewing Life Cycle Management for Reference Data

2 Reviewing Life Cycle Management for Reference Data

This chapter discusses the following process integrations:

Supplier information integration

Note:

Currency Exchange Rates synchronization from CFIN is not supported by RFI.

Suppliers Information Integration

This section provides an overview of the process integration for initial loading and incremental synchronization of suppliers' information between Oracle Payables/Procurement and Oracle Retail Merchandising System (RMS) and discusses:

Supplier integration details
Data requirements

Supplier Integration Overview

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/Procurement is the source of valid suppliers 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/Procurement 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.

Prerequisites

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
SUPPLIER_SITE_NAME_XREF

Freight terms synchronization between both the systems is a manual process. The Supplier XREF, Supplier Site XREF and Supplier Site Name XREF data are populated by a Supplier Integration Adapter; the values are required during the Supplier Update flow.

Solution Assumptions and Constraints

The integration design assumes that:

Oracle Payables/Procurement is the source system for merchandise suppliers, their contacts, locations, addresses and other attributes.
You can create suppliers and suppliers' locations in Oracle Payables/Procurement.
You can maintain the relationship between suppliers, suppliers' locations in Oracle Payables/Procurement. This integration is a one-way synchronization. Any update to supplier information in RMS is not synchronized with Oracle Payables/Procurement. 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.
The CFIN system sends all the information, related to supplier using RFI SupplierExportOutbound API.

Financials Cloud:-Supplier Integration Details

This diagram illustrates the supplier integration flow with Financials Cloud:

Figure 2-1 RMS CFIN Supplier Integration Flow

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 with Supplier payload message whenever a supplier is created or updated (by passing complete supplier extract for updated/new supplier). Any errors during service call could be found on notification section on CFIN UI. This RFI Export Service URL needs to be configured in CFIN side so that CFIN can invoke it.
The export outbound service extracts all the supplier, supplier sites and supplier contacts from incoming 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. Any error during stage data load could be found on RFI_ODI_LOGS table (rfi app schema).
A Configured Timer for RFI Supplier service invokes the Supplier EJB service. By Default this service runs at every 10 mins.
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. Any error for missing DVM/XREF will be reported in RFI supplier.log. Check Adapter log for such details. Only when Supplier fails with any data error then 'is_data_processed ind' of that supplier turns to 'E'.

For valid data, RFI then invokes the RMS Supplier Service is invoked -with the transformed supplier records. This RMS service creates the actual supplier in RMS. If there are any errors in Supplier EJB service, the errors are added to the logs file and also to the RFI_ODI_LOGS table. The Supplier logs can be viewed using the RFI Adapter Manager screens in RFI Administration GUI and Logs table can be viewed from View Logs screen.
If the transaction from RMS is successful, Retail Id cross references for supplier, supplier site and supplier site name are stored in the cross reference table RFI_XREF_DVM. The cross references updated is SUPPLIER_XREF, SUPPLIER_SITE_XREF and SUPPLIER_SITE_NAME_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 RMS service URL needs to be configured in RFI side.

Data Requirements (CFIN)

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. Multiple contacts can be associated with one address in CFIN.

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 (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 CFIN implementation some contact details are mandatory to create/update a supplier in Retail, a dummy contact is created if no contact information is passed by Oracle Cloud Financials.
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.

Supplier Filter

RFI provides a feature to filter and reject the incoming supplier data which is not intended for RMS. This filter works at supplier staging data and will restrict configured supplier data to flow toward RMS.

Supplier filter Setup and Assumptions:

DVM 'VENDOR_FILTER_XREF' is added to configure the supplier filter option.
Customers can view/edit/create supplier filter criteria using manage DVM screen or access RFI_XREF_DVM table directly (cloud - via AMS support route or DDS).
Customer can select any field from RFI_SUPP_STAGE table as filter column. Only one fil-ter column is allowed to be selected. Multiple columns selection as filter is not allowed.
Customer can configure any string value (provided it's under the selected filter column length) as filter value. Multiple filter values (<1000) can be selected by creating multiple records in RFI_XREF_DVM table with same filter column.
Logging captures the filter column and filter value(s) for analysis.
Category type for filter is 'VENDOR_FILTER_XREF'. Customer can't change this.
If no record is entered for this catgory type in RFI_XREF_DVM table then no records will get filtered and everything will get processed (provided they pass the validations).
This functionality works for CFIN financial source point.
Filtered supplier will be ignored from processing and will remain on RFI SUPP staging tables till it is manually deleted.
When customer removes or changes the filter criteria, filtered stage record can be processed again without any issue.
Records matching the filter criteria will get excluded from processing.
EX:

Insert into rfi_xref_dvm (CATEGO-RY_TYPE,CATEGORY_TYPE_DESC,COMMON_ID,RETL_ID,EXT_SYSTEM_ID,EXT_SYSTEM)

values ('VENDOR_FILTER_XREF','Supplier Filter Criteria', '1001', 'VENDOR_TYPE_LOOKUP_CODE', 'TAX AUTHORITY', 'CFIN');

Insert into rfi_xref_dvm (CATEGO-RY_TYPE,CATEGORY_TYPE_DESC,COMMON_ID,RETL_ID,EXT_SYSTEM_ID,EXT_SYSTEM)

values ('VENDOR_FILTER_XREF','Supplier Filter Criteria', '1002', 'VENDOR_TYPE_LOOKUP_CODE', 'Any other value', 'CFIN');

Supplier Error Handling

All the error related to supplier stage table load via export outbound service/event subscription is recorded in RFI_ODI_LOGS table. Refer this table to find incremental or initial upload related errors.

RFI then process the staged records via supplier adapter. Any error found during the conversion of stage record to payload creation for RMS will be logged into RFI logs (supplier.log). There are two types of validation in RFI while processing stage supplier data:

Data compatibility validation (If any of purchasingSiteFlag & paySiteFlag values are not set for any of the sites for that supplier it will be errored out, not a fit for RMS).
DVM xref validation for data (If any of the required DVMs are missing then RMS supplier service will not be called until that missing DVM is fixed).

Assumptions/Conditions:

If any stage supplier data fails at data compatibility validation then it means data needs to be corrected manually or sent again to RFI STAGE table through financial systems. IS_PROCESSED status of that stage supplier record will be marked as 'E' in RFI_SUPP_STAGE table. These record will then be ignored from next run of supplier service. These records will stay in stage table until customer gets it deleted manually. Logs will capture IS_PROCESSED indicator change for that supplier for further analysis.

Records failed due to DVM xref validation will not go through status change as DVM data can be corrected and same supplier data can be processed without any change. All the missing DVM errors will be logged in RFI logs.

Processed supplier data will be auto purged from stage table during next execution cycle.
Any supplier failed due to missing DVM/XREF will stay in stage table and will get reprocessed in next execution cycle again. User needs to check Error log for failure details.
In one execution cycle only latest 1000 supplier records will be processed. Other pending records will be picked on next subsequent cycles.

Retail: - Class Diagram

Figure 2-2 Supplier Relation in Retail

Start or Run Initial Load of Supplier Parties from CFIN

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 are 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"."PER_PARTY_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: CFIN initial supplier data can be uploaded in RFI either by:

Running the scripts (By AMS via SR or by on premise customers having full permission and access) or
By using the RFI UI screen - Supplier Upload

Supplier upload by Scripts: 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.

RFI Supplier upload by UI screen: Follow below steps to upload the supplier through UI-

Login to the RFI UI with valid credentials. Navigate to the Load Suppliers tab.
Create a compressed file (.zip) with Supplier xmls exported from CFIN. It is mandatory to follow below naming convention for suppliers to be loaded successfully. Make sure the CSV files doesn’t have any control m characters in them, else it would fail upload. Keep all the required files inside a folder and then zip them.
Read the Pre-Requisite section carefully before uploading files.
Click the Browse button under the File upload section. Browse for the .zip file created in the above step.
Click the Override existing supplier Details check box to override existing suppliers (if found) with the details specified in the xml. A confirmation window appears asking to confirm selections.
Click OK to continue with the current selection or Cancel to reselect.

Note:

RFI does not support partial update of supplier details.
If the supplier ID that you are trying to upload is already available in RFI stage tables and the Override existing supplier Details check box is checked, then all details about that supplier are deleted from the RFI stage tables and the new details specified in the xml are loaded into the stage table.
If the supplier ID that you are trying to upload is already available in RFI stage tables and the Override existing supplier Details check box is NOT checked, then the supplier load process is terminated with an exception and no data gets loaded into the RFI stage tables.
The status of the supplier upload process is displayed in the Supplier Upload Status section. Navigate to the RFI Application Logs section for detailed logs.
Note:
- A single .zip file can be uploaded at once. Multiple file upload is not supported.
- No other file formats except .xml are supported.
- Ensure that the zip file has single entry for Supplier Data, Supplier Site and Supplier Contact

Payment Terms Integration

This integration is not supported by RFI for CFIN/EBS. Instead, users are expected to upload any incremental changes to exchange rates using the RMS Foundation Data Load functionality.