R12 - Supplier Conversion

Title
Supplier
Conversion
Pratima Sharma
Date : 11 Nov 2008
Prepared By
:
Confidential to CSC
Team-4
(Supplier)
01/05/17 03:03
Title
Agenda
Objective
Overview
of Conversion Process
Overview
of P2P Cycle
Extraction
and Loading
Validation
Rules considered
Error
Codes and Messages during Validation
Transfer
of data into INTERFACE tables
IMPORTING
Error
data into Base tables
Report/Log File
Reconciliation
Concurrent
Future
Report
Program User Constraint
Enhancements
Confidential to CSC
01/05/17 03:03
Title
Objective
The Conversion of Suppliers information from Legacy
system(11i) to R12 version system.
This presentation will help developer/implementer to develop

a reusable component for any Conversion
Confidential to CSC
01/05/17 03:03
Title
General Overview of Conversion Process
Flat Files
Staging Tables
Legacy
Database
Mapping Files
Mapping
Enrichment
Legacy System
Option
al
Proces
s
Validate
Error Report
Error
NO
NO
Is
Valid?
YES
Interface Tables
Target
Database
R12
Confidential to CSC
YES
Is
Valid?
Call Standard API
01/05/17 03:03
Title
Overview of P2P Cycle
Requisition
Requisition
RFQ
RFQ
Quotation
Quotation
Quotation
Quotation
Analysis
Analysis
General
General
Ledger
Ledger
AP
AP Invoice
Invoice
Receipt
Receipt
PO
PO
Oracle Apps R12 (Supplier)

TCA Concept
Base Table name changed
Bank accounts is attached to legal
entity level
Confidential to CSC
01/05/17 03:03
Title
TCA Concept and Bank Data Model (R12)
TCA Registry, which is the single source of trading community

information
TCA is a data model that allows you to manage complex information
TCA
about the parties, or customers, who belong to your commercial

community, including organizations, locations, and the network of
hierarchical relationships among them.
Supplier & HZtables
In R12 SUPPLIERS have been moved into TCA Data Model

In R12 Bank accounts is attached to your legal entity level rather than
Bank Data
Model(R12)
Operating Unit , which makes bank with strong capability to pay across
operating units
Confidential to CSC
01/05/17 03:03
Title
Pre-Requisites to start the conversion process
CV-40 ,CV-60 documents needs to be prepared which emphasizes on functional & technical
aspects are the basic building blocks for the Conversion process
CV-040
CV.060
At the target database the staging tables will be created with the help of CV-40, CV-60
documents.
XXCSC_AP_SUPPLIER_HEADERS
XXCSC_AP_SUPPLIER_SITES
XXCSC_AP_SUPPLIER_CONTACTS
Along with the staging tables we need to create other tables which will be useful for the
Generic conversion package
XXCSC_CMN_METADATA_ALL
XXCSC_CMN_MAPPING_ALL
XXCSC_CMN_VALIDATIONS_ALL
Confidential to CSC
01/05/17 03:03
Title
Information in Generic Tables

Meta Data Table: XXCSC_CMN_METADATA_ALL
OBJ_CODE
OBJ_DEP_SEQUENCE
OBJ_NAME
CTL_FILE
DATA_FILE
STG_TABLE
DATA_DIR_PATH
APPSUPP
SUPPLIER
XXCSC_SUPP.ctl
XXCSC_SUPP.csv
XXCSC_SPP_HDR
/tmp
Validation Table: XXCSC_CMN_VALIDATIONS_ALL

OBJ_CODE
VALID_CODE
VALID_DESC
SQL_QUERY
VALID_TYPE
ENABLE_FLAG
APPSUPP
SUPHDR001
Supplier Number is
NOT NULL
Select count(1) from XXCSC_SUPP_HDR where

vendor number is NULL
APPSUPP
SUPSIT001
Supplier Site Code

Number is NOT NULL
Select count(1) from XXCSC_SUPP_SIT where

vendor _site_code is NULL
Mapping Table: XXCSC_CMN_MAPPING_ALL

OBJ_CODE
MAPPING_CO
LUMN
LEGACY_CODE
LEGACY_DESC
TARGET_CODE
TARGET_DESC
APPSUPP
TERMS_NAME
NET-30
NET-30
NET-30/1/15
NET-30/1/15
APPSUPP
BILL_TO_LOC
ATION_CODE
NY
New York
V1- New York
V- New York
Confidential to CSC
01/05/17 03:03
Title
Supplier Conversion Process Overview

SUPPLIER STAGING TABLES
PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS
11i DATABASE TABLES
Extracting
data
Data comes in
the form of the
CSV files for
Supplier,
Supplier sites,
Supplier
contacts
XXCSC_AP_SUPPLIER_CONTACT
S
VALIDATION PROGRAM
(Validate Supplier, Sites, contacts)
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS
R12 DATABASE TABLES
Confidential to CSC
Supplier open interface import,

Supplier site open interface import,
Supplier Site contacts Open interfaceimport.
Standard import
programs
AP_SUPPLIER S_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
SUPPLIER INTERFACE TABLES
01/05/17 03:03
Title
Extracting & Loading Data
The Extract query will fetch the data from the legacy system(11i)
The extracted data can be captured as data files(.csv/.xls) with a delimiter(^)
Populate the data into Supplier staging tables from legacy system Using SQL
Loader Script in the custom schema XXHR
Correspondingly each and every record should contain the flag which will be updated with its
current Process flag , Status flag (LOAD at this stage)
After Loading is done, the corresponding report should be generated
S UPPLIER_HEADER
S _DATA
XXCSC_AP_S UPPLIER_HEADERS.ctl
EXTRACT
EXTRACT FILE
Confidential to CSC
S UPPLIER_S ITES _D
ATA
SUPPLIER_CONTAC
TS_DATA
DATA FILES
XXCSC_AP_SUPPLIER_S ITES.ctl
XXCSC_AP_SUPPLIER_CONTACT
S
XXCSC_AP_SUPPLIER_CONTACTS.ctl
CTL FILES
STAGING TABLE
01/05/17 03:03
Title
Validation Rules
Staging tables will be validated with setup, dependency, data-format rules.

Each record will be flagged with the corresponding Process and status flags,
Error_Codes and Error Messages in the staging table.
V
-- Validated perfectly
-- Error Record
And the corresponding Report will be generated with the error details.
Sample set of Error_code and Error_Message is as below.
Validation Rule
Error_code
Error_Message
SELECT COUNT (1) FROM XXHR.xxcsc_ap_supplier_headers mt

WHERE mt.customer_num IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM APPS.ra_customers rc WHERE
mt.customer_num = rc.customer_number)
ERRHDR028
CUSTOMER NUMBER is Invalid.

Not available in Customer Table
Confidential to CSC
01/05/17 03:03
Title
Transferring Data into Interface tables
Cleansed records can be transferred to the INTERFACE tables, leaving behind

the error records.
Transfers only records which are validated without error (status flag as V)
The summarized and detailed report will be generated, which mentions details for each
table
Importing data into Base tables
With the help of the following Oracle Import Programs we can import the
data into Base Tables from Interface tables
Supplier open interface import
Supplier site open interface import
Supplier Site contacts Open interface import.
Supplier Open Interface Request Set
Confidential to CSC
01/05/17 03:03
Title
Executable
A Unix shell script will be used as executable which takes care of all the activities of
conversion with help of custom Oracle package (XXCSC_AP_CONV_PKG.pkb).
LOADING
MAPPING
VALIDATION
TRANSFER
CONVERSION REPORT
Pseudo Code of Unix Script
PS EUDO_CODE
Confidential to CSC
01/05/17 03:03
Title
Confidential to CSC
Concurrent Program
01/05/17 03:03
Title
Concurrent Program User Constraint
The user cant submit the same concurrent program by parallel for particular object.
The latest concurrent program will end with error and gives the existing concurrent
program ID and Submitted date.
The system wont accept the concurrent program with existing stage, If we need to
execute the previous/existing stage for particular object then we should submit the
concurrent program with Forcefully Submit flag as Y.
The ALL value in STAGE parameter will be used as a single concurrent submission
and it will take care of LOADING, MAPPING, VALIDATION and TRANSFER stages.
End of the every Stage, System will generate SUMMARY report and In VALIDATION/
ALL stage the system will generate Error Report
LOADING, MAPPING, VALIDATION, Summary Report and Error Report are dynamic
and object Independent.
Confidential to CSC
01/05/17 03:03
Title
Confidential to CSC
ERROR REPORT/ LOG FILE
01/05/17 03:03
Title
Confidential to CSC
RECONCILIATION REPORT
01/05/17 03:03
Title
Future Enhancement
Developing a form which can display and update the data of Generic tables
Converting the Transfer procedure into more Generic procedure
Avoiding the import of already imported records during the iterations , by which we can
reduce execution time to an extent
Confidential to CSC
01/05/17 03:03
Title
Pratima Sharma
Thank You!
Date : 11 Nov 2008
Confidential to CSC
01/05/17 03:03

R12 - Supplier Conversion

Uploaded by

Copyright:

Available Formats

R12 - Supplier Conversion

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

R12 - Supplier Conversion

Uploaded by

Copyright:

Available Formats

Title

Codes and Messages during Validation

of data into INTERFACE tables

data into Base tables

Program User Constraint

This presentation will help developer/implementer to develop

General Overview of Conversion Process

Call Standard API

Overview of P2P Cycle

Oracle Apps R12 (Supplier)

TCA Concept and Bank Data Model (R12)

TCA Registry, which is the single source of trading community

about the parties, or customers, who belong to your commercial

Supplier & HZtables

In R12 SUPPLIERS have been moved into TCA Data Model

Pre-Requisites to start the conversion process

Information in Generic Tables

Validation Table: XXCSC_CMN_VALIDATIONS_ALL

Select count(1) from XXCSC_SUPP_HDR where

Supplier Site Code

Select count(1) from XXCSC_SUPP_SIT where

Mapping Table: XXCSC_CMN_MAPPING_ALL

V1- New York

Supplier Conversion Process Overview

11i DATABASE TABLES

(Validate Supplier, Sites, contacts)

R12 DATABASE TABLES

Supplier open interface import,

SUPPLIER INTERFACE TABLES

Extracting & Loading Data

Staging tables will be validated with setup, dependency, data-format rules.

SELECT COUNT (1) FROM XXHR.xxcsc_ap_supplier_headers mt

CUSTOMER NUMBER is Invalid.

Transferring Data into Interface tables

Cleansed records can be transferred to the INTERFACE tables, leaving behind

Importing data into Base tables

Concurrent Program User Constraint

ERROR REPORT/ LOG FILE

You might also like