REG Conversions

[vamsioracle]

Hi all


I am new to conversions, I have to transfer data from flat file to 3 interface tables, po_lines_interface, po_headers_interface and po_distributions_interface.

I used SQL LOADER Script and its working fine. now i need to transfer to base tables. How to do that. Is there any Concurrent program in Apps, that transfers all the three interface table data into base tables or do i need to do individually

thanks in advance

[amitpatel66]

Hi all


I am new to conversions, I have to transfer data from flat file to 3 interface tables, po_lines_interface, po_headers_interface and po_distributions_interface.

I used SQL LOADER Script and its working fine. now i need to transfer to base tables. How to do that. Is there any Concurrent program in Apps, that transfers all the three interface table data into base tables or do i need to do individually

thanks in advance

You have the standard Concurrent program that you need to use to load the data from po_interfaces to the base tables. It something named as "Purchasing ......" Use that standard program and check for errors if any while loading data in to base table.

[vamsioracle]

Thank u
I got it , Its Purchasing Documents Open Interface (PDOI. But i don't to where to find the concurrent program. I searched in all the Purchasing responsibilities. But i could not find.

Please help me

thanku

[amitpatel66]

Try This:

 
-- this query will give you concurrent program id for your program 
 
select concurrent_program_id from fnd_concurrent_programs fcp where UPPER(fcp.user_concurrent_program_name) LIKE '%PURCHASING%' 
 
-- this query will give you responsibility_id for the program that was submitted from which responsibility
 
select responsibility_id from fnd_concurrent_requests where concurrent_program_id = <submit here from above query>
 
-- this query will give you responsibility name
 
select responsibility_name from fnd_responsibility where responsibility_id = <submit here from above query>
 
 

[vamsioracle]

Thank u very much


I have a doubt regarding sql loader. I had written the following code with validations, are these validations correct, where can i find all the oracle stnadard validations

 
LOAD DATA
INFILE 'XXBIO_PO_HEADERS_INTERFACE.csv'
APPEND
INTO TABLE PO_HEADERS_INTERFACE FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULL values
(
    INTERFACE_HEADER_ID NUMBER NOT NULL ,---------
    PO_HEADER_ID NUMBER NOT NULL, ------
    AGENT_NAME CHAR,
    VENDOR_NAME,
    VENDOR_SITE_CODE,
    VENDOR_CONTACT,
    SHIP_TO_LOCATION,
    BILL_TO_LOCATION,
    PAYMENT_TERMS,
    FREIGHT_CARRIER,
    FOB,
    FREIGHT_TERMS,
    APPROVAL_STATUS,
    REVISION_NUM NUMBER UNIQUE , ----is this correct
    COMMENTS,
    CREATION_DATE        DATE     "DD-MON-YYYY HH24:MI:SS"
 
 
 

and what is that position(x,y), why to take two parameters.

Thanks in advance

[amitpatel66]

This is not the right way to perform interfacing task.

You need to do the following:

1. Create a staging table (similar to po_headers_interface,lines,distributions)
2. Use SQL Loader and LOAD that data in to your staging table
3. Write a package (to validate the data in the staging table. Check if Item exists in inventory, whether vendor is valid vendor, etc)
4. Once validated using step 3, move the data into interface tables.
5. Run the standard program to move data from interface to base tables
6. Repeat step 3 - 5 if required

SQL LOADER validation is just to validate the data in flat file before insert. If it does not satisfy your consitions in CTL file then the record will be rejected and placed in BAD file

Position(x,y) is used for fixed length column values.
x - starting position of the column value in the flat file
y - ending position of the column value