INTERFACE OR CONVERSION PROCESS

INTERFACE OR CONVERSION PROCESS:

-----------------------------------                                
My PL will be   assigning the interface/conversion which i have to do.for example items conversion i have to do in
 next 15 days.

Before getting the flatfile i will create item in the 

front end to get the 

functional knowledge of the object.
Once i get the complete fucntional knowlege ie,creation of the item then i will 

try to process the similar record 
from the back end.

i will find out the columns in the base table which are 

storing the item information.

I will try to find out corresponding columns in the interface table.Mostly base 

table and inteface table column name
will be same ,if not i will try to find out the corresponding columns.
for example segment1 it will store the item name.so we need to populate the 

item name in 
mtl_system_items_interface.segment1


so like this i will do single insert(inserting one record manually into interface table with out taking the package)
 to get he complete technical knowledge of the interface.
i dont know the validations we need to do.so if we do the single insert it will 

show the errors if i am processing
invalid data.so that i can know what are the validations have to do.

Once the single insertion is done then i will start designing the package with the mandatory columns and 
important columns.As per my company standards we need to follow some starndards 

while developing the package.

we will be having 2 staging tables _ld and _stg.
LD table will contain only the data given by the client itemname,description and

organziation_name

STG table will contain the columns required for conversion.Any staging table will contain
status,error_message,transaction_id columns
Status: for the status of the record
              N new record
             MM mandatory column missing
            X duplicate records
            VE validated error
             BE failed to insert into base tables from interface tables
            V validated
             LE load error
             S success
Error message: to know the error why record got failed
transaction_id : unique record number
For the columns which client has given we will prefix
with LD(legacy data) just for recognization purpose.

for example itemname,description will be prefix as these data will be given by the client.
trasaction_type,process_flag wont contain as these data is not given by the client.

package should contain MAP,VALIDATE,LOAD,CALL PROD,STATUS CHECK AND ERROR REPORT.

MAP:i will be updating default values required for conversion example 

transaction_type,process_flag.

validate: i will be validating the data

Duplicate records: X      duplicate records

 mandatory columns are existed or not if any thing misses i will update the status to MM the error_message to
    mandatory columns missing

organization                                       ve         
item should not be existed
should be a master organization

load

updating the status to V for the valid records
and insert these records into interface tables ie,mtl_system_items_interface

call prod:
its difficult to call all the procedures so we will call all the procedures

(map,validate and load) in the call prod.

running the standard program: import items

check status

if records got successufully inserted intobase tables updating status to S else BE
We should never get BE it we get our program is wrong.

error report.

 Generating the error report to show about the errors to the client.


**********we will be doing all the above process with in 4 to 5 days with the 

mandatory and some other important columns***************

we will sending the mandatory columns list to the functional consutant to ensure 

that datafile has all the
mandatory columns.

once we get the datafile we will be modifing the package as per the file as we 

built the package only for
mandatory columns (file may contain extra columns also)

1)By using SQL loader we will be sending data into ld table
and inserting data into stg table

2)by using plsql package we will sending data from staging table to interface 

table

3) By using Sandard program' import items' we will sending data from interface

table to base tables.

we will be testing the object in aroung 5 instance

1 instance:             
----------------------
company instance


2 instance
--------------------
client instance


3,4,5 instance-CRP(Customer Room Pilot) -exact replica of the production instance
----------------------
if production is in next 10 days. for 3 days daily we will be getting once CRP 

instance.

if our object is successfull the we will be moving these objects to production 

instance by creating the LDT files.

In each and every stage ,after developing the objects or documents it should be 

approved the QA(quality Analist)

While developing the object we will be creating different documents.

CV 40: Functional Document   functional consultant
CV 60 : Technical Document   Developer
TE10 : unit testcases                   Developer
TE20 : system integration testing  QA
CV120: Installation document. Developer but executed by the DBA

(Data Base Administrator)