Data Migration vs Data conversion

When we need to enter data into oracle Apps:

• The Data can be entered using the application Screens (for small amount of data, like creating PO, entering sales orders using Oracle Apps screens).
• The data can be entered using Oracle’s Open System Interface (for regular operations e.g. for moving data from one module to another).
• The data can be stored in the database table directly (Not recommended by oracle and can be very risky, because on any event data is going to be stored in many tables and data should be validated before inserting into tables that may cause data integrity and inconsistency problem, sometimes it may corrupt the data completely.).
• Using third party tools like data loader (It is also can be used when data is relatively small (25-200 records) because it captures the keystrokes and works like manually entering the data into Oracle form but much faster as process is automated).

Read Need of Migration/Conversion

Migration: Migration of data means moving the data from one system to another using Interface Programs/APIs where both the systems have same structure of data.
Process of Migrating of data:

• Identify the data to be imported to new system (Business requirement).
• Extract the data into flat file/Staging table
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface)

Conversion: Conversion of data means translating the data to suite target system (data should be formatted according to target system )  and then move the translated data using Interface Programs/APIs.

• Identify the data to be imported to new system (Business requirement).
• Extract into flat file/Staging table
• Translate/Convert/Format the data
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface) and then launch standard Interface concurrent program to load the data to Oracle Apps Base Tables
• If using API, fetch the data, validate it and then call API to import the data

Also Read Difference between Migration/Conversion and Interface

Read More »

Need of Data Migration or Data Conversion

Migration/Conversion are required when we are upgrading to one version to another (e.g. Oracle Apps 11.5.7 to Oracle 11.5.10) or moving data from some legacy system to Oracle Apps. There will be bulk  of data (sometimes millions or even more than that) that needs to be moved from one system to another  and  before moving the data it should be validated and only valid records should be entered into Oracle Apps.

If both the systems (Target and source) are not having same structure for data (Tables are not same/Table Structure is not same/The data is being stored in database is not same), it needs to be translated (e.g. upgrading from Oracle 11i to R12 where table structures are not same) then we say it as conversion (any kind of translation of data on Source data to make it suitable for Target system) otherwise migration.

Read More »

Difference between conversion/Migration and interface

Frequency

• Conversions/Migration are a one time event
• interfaces are ongoing

Occurrence in the project timeline

• conversions/Migration executed before production
• interfaces executed during production

Manner of execution

• Conversions/Migration are batch
• Interfaces may be batch or real time

Complexity

• Conversion/Migration does have very complex, it’s totally depends upon the data mapping activity.
• Coordinating with other systems make interfaces more complex

Maintenance

• Maintenance of interface is bit cost intensive task.

Read More »

PL/SQL Script of Purchase Order Interface

PL/SQL Script to upload data from staging table to interface table

CREATE OR REPLACE PROCEDURE xxx_po_api
AS
---To Import data from Interface to Base Tables - Import Standard Purchase Orders
-- please do the following: to see the errors
-- Run the program - Purchasing Interface Errors Report
-- choose parameter : PO_DOCS_OPEN_INTERFACE
-- po_interface_errors
l_currency_code fnd_currencies_vl.currency_code%type;
l_verify_flag Char(1);
l_error_message varchar2(5000);
l_vendor_id po_vendors.vendor_id%type;
l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
l_ship_to hr_locations.location_id%type;
l_bill_to hr_locations.location_id%type;
l_inventory_item_id mtl_system_items_b.inventory_item_id%type;
l_legacy_ponum number(20):=0;
l_batch_id number(3);
CURSOR C_PO_HEADER IS
            select distinct legacy_ponum,
            currency_code,
            vendor_name,
            vendor_site_code,
            ship_to,
            bill_to,
            status
            from xxx_purchase_order_stg;

CURSOR C_PO_LINES(l_legacy_ponum NUMBER) IS
            select *
            from xxx_purchase_order_stg
            where trim(legacy_ponum) = trim(l_legacy_ponum);
BEGIN
    FOR H1 IN C_PO_HEADER LOOP
        l_verify_flag := 'Y' ;
        l_error_message := NULL;
        BEGIN
            select currency_code
            into l_currency_code
            from fnd_currencies_vl
            where enabled_flag = 'Y'
            and currency_flag = 'Y'
            and upper(currency_code) = upper(trim(H1.currency_code));
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Currency Code is not Valid...';
        END;
        BEGIN
            select vendor_id
            into l_vendor_id
            from po_vendors
            where upper(vendor_name) = upper(trim(H1.vendor_name)) ;
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Vendor is not Existing...';
        END;
        BEGIN
            select vendor_site_id
            into l_vendor_site_id
            from po_vendor_sites_all
            where vendor_id = l_vendor_id
            and vendor_site_code = upper(trim(H1.vendor_site_code)) ;
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Vendor Site is not Existing...';
        END;
        BEGIN
            select location_id
            into l_ship_to
            from hr_locations
            where location_code = upper(trim(H1.ship_to));
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Ship To Location is not Existing...';
        END;
        BEGIN
            select location_id
            into l_bill_to
            from hr_locations
            where location_code = upper(trim(H1.bill_to));
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Bill To Location is not Existing...';
        END;
        If H1.status = 'Approved' then
        l_batch_id := 100 ;
        elsif H1.status = 'Incomplete' then
        l_batch_id := 101 ;
        else
        l_verify_flag := 'N' ;
        l_error_message := l_error_message||'Status is not valid...';
        end if;
l_legacy_ponum := trim(H1.legacy_ponum) ;
        IF l_verify_flag <> 'N' THEN
            insert into po_headers_interface
            (interface_header_id,
            batch_id,
            action,
            document_type_code,
            currency_code,
            agent_id,
            vendor_id,
            vendor_site_id,
            ship_to_location_id,
            bill_to_location_id
            )
            values
            (po_headers_interface_s.nextval,
            l_batch_id,
            'ORIGINAL',
            'STANDARD',
            l_currency_code,
            5479,
            l_vendor_id,
            l_vendor_site_id,
            l_ship_to,
            l_bill_to );
            update xxx_purchase_order_stg
            set h_verify_flag = 'Y'
            where legacy_ponum = l_legacy_ponum;
            COMMIT;
    FOR L1 IN C_PO_LINES(l_legacy_ponum) LOOP
        BEGIN
            select inventory_item_id
            into l_inventory_item_id
            from mtl_system_items_b
            where segment1||'.'||segment2||'.'||segment3||'.'||segment4=L1.item
            and organization_id = (select inventory_organization_id
            from hr_locations
            where location_id = l_ship_to ) ;
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message ||'Inventory Item is not Existing...';
        END;
        IF L1.unit_price IS NULL THEN
        l_verify_flag := 'N' ;
        l_error_message := l_error_message ||'Unit Price is not Existing...';
        ELSIF L1.quantity IS NULL THEN
        l_verify_flag := 'N' ;
        l_error_message := l_error_message ||'Quantity is not Existing...';
        ELSIF L1.need_by_date IS NULL THEN
        l_verify_flag := 'N' ;
        l_error_message := l_error_message ||'Need By Date is not Existing...';
        END IF;
        IF l_verify_flag <> 'N' THEN
            insert into po_lines_interface
            (interface_line_id,
            interface_header_id,
            action,
            line_num,
            item_id,
            unit_price,
            quantity,
            Need_By_Date)
            values
            (po_lines_interface_s.nextval,
            po_headers_interface_s.currval,
            'ORIGINAL',
            L1.line_num,
            l_inventory_item_id,
            L1.unit_price,
            L1.quantity,
            L1.need_by_date);
            update xxx_purchase_order_stg
            set l_verify_flag = 'Y'
            where legacy_ponum = L1.legacy_ponum
            and line_num = L1.line_num;
ELSE
            update xxx_purchase_order_stg
            set l_error_message = l_error_message,
            l_verify_flag = 'N'
            where legacy_ponum = L1.legacy_ponum
            and line_num = L1.line_num;
END IF;
COMMIT;
END LOOP;
ELSE
            update xxx_purchase_order_stg
            set h_error_message = l_error_message,
            h_verify_flag = 'N'
            where legacy_ponum = H1.legacy_ponum;
END IF;
COMMIT;
END LOOP;
end xxx_po_api;
/

Purchase Order Interface

Read More »

SQL*Loader Environment Part2

3) Input Datafiles:

• The data to be loaded is contained in one or more datafiles if it is not contained in the control file.
• The data in the datafile can be in the fixed length format, variable length format, or in the stream record format.

a) Fixed Length Format:

A file is in the fixed record format when all the records in the datafile have the same byte length. This format is not flexible but offers very good performance.then the syntax for the INFILE command is – INFILE student.dat “fix 15?

The syntax for letting SQL*Loader know that the data is in the fixed length format is:

INFILE datafile_name “fix n”

Here INFILE datafile_name refers to the file that contains the data to be loaded. “fix n” implies that each record in the datafile has a fixed byte length of n.

For example if the name of the following datafile is student.dat and the byte length of a record is 15 bytes

0001, —–Rina, 0002, —-Harry, 0003,—–Sudha

b) Variable Length Format:

A file is in the variable record format when the length of each record varies. The length of each record is included at the beginning of the record in the datafile. This format provides some added flexibility over the fixed record format and a performance advantage over the stream record format.

For example, you can specify a datafile that is to be interpreted as being in variable record format as follows:

INFILE “datafile_name” “var n”

Here n specifies the number of bytes in the record length field. If n is not specified, SQL*Loader assumes a length of 5 bytes. If n is specified larger than 40 it results in an error. The following datafile is random.dat and the value for n is 3.

009hello,cd,010world,im,

012my,name is,

SQL*Loader reads the first 3 bytes to gather the length of the record. Here the first record is 9 bytes long. After SQL*Loader has read 9 bytes, it reads the next 3 bytes to find the size of this record which is 10 bytes long. It reads the next 10 bytes of the record and then finds the third record is 12 bytes long and so on.

c) Stream Record Format:

A file is in the stream record format when the records are not specified by size; instead SQL*Loader forms records by scanning for the record terminator. Stream record format is the most flexible format, but there can be a negative effect on performance.

The syntax for specifying the stream record format is as follows:

INFILE datafile_name ["str terminator_string"]

The terminator_string can be a ‘char_string’  which is a string of characters enclosed in single or double quotation marks or a ‘hex_string’ which is a byte string in hexadecimal format.

4) The Log File:

The log file is a record of SQL*Loader’s activities during a load session. It contains information such as the following:

• The names of the control file, log file, bad file, discard file, and data file
• The values of several command-line parameters
• A detailed breakdown of the fields and datatypes in the data file that was loaded
• Error messages for records that cause errors
• Messages indicating when records have been discarded
• A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load

Always review the log file after a load to be sure that no errors occurred, or at least that no unexpected errors occurred. This type of information is written to the log file, but is not displayed on the terminal screen.

5) The Bad File:

Whenever you insert data into a database, you run the risk of that insert failing because of some types of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.

• If one or more records are rejected, the bad file is created and the rejected records are logged.
• If no records are rejected, then the bad file is not created.

6) The Discard File:

While SQL*Loader is being executed it creates a discard file for records that do not meet any of the loading criteria. The records contained in this file are called discarded records. Discarded records do not satisfy any of the WHEN clauses specified in the control file. These records differ from rejected records. Discarded records do not necessarily have any bad data. A discarded record is never inserted into the Oracle table.

A discard file is created according to the following rules:

• You have specified a discard filename and one or more records fail to satisfy all of the WHEN clauses specified in the control file. (If the discard file is created, it overwrites any existing file with the same name, so be sure that you do not overwrite any files that you want to retain.)
• If no records are discarded, then a discard file is not created.

<<<Previous

Read More »

Inventory On-hand quantity Interface

Inventory On-hand quantity Interface

This interface lets you import the on hand inventory into Oracle.

Interface tables:

• MTL_TRANSACTIONS_INTERFACE
• MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
• MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)

Concurrent Program:

• Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
• The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG.
• Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base Tables:

• MTL_ON_HAND_QUANTITIES
• MTL_LOT_NUMBERS
• MTL_SERIAL_NUMBERS

Validations:

• Validate organization_id
• Check if item is assigned to organization
• Validate disposition_id
• Check if the item for the org is lot controlled before inserting into the Lots interface table.
• Check if the item for the org is serial controlled before inserting into Serial interface table.
• Check if inventory already exists for that item in that org and for a lot.
• Validate organization_id, organization_code.
• Validate inventory item id.
• Transaction period must be open.

Important Columns:

Some important columns that need to be populated in the interface tables:

MTL_TRANSACTIONS_INTERFACE:

• TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
• TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
• TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
• TRANSACTION_DATE,
• TRANSACTION_TYPE_ID,
• PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
• TRANSACTION_MODE (2 = Concurrent – to launch a ded transaction worker to explicitly process a set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
• SOURCE_CODE,
• SOURCE_HEADER_ID,
• SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
• TRANSACTION_SOURCE_ID

Source Type Foreign Key Reference

• Account GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
• Account Alias MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
• Job or schedule WIP_ENTITIES.WIP_ENTITY_ID
• Sales Order MTL_SALES_ORDERS.SALES_ORDER_ID
• ITEM_SEGMENT1 TO 20,
• TRANSACTION_QTY,
• TRANSACTION_UOM,
• SUBINVENTORY_CODE,
• ORGANIZATION_ID,
• LOC_SEGMENT1 TO 20

MTL_TRANSACTION_LOTS_INTERFACE:

• TRANSACTION_INTERFACE_ID,
• LOT_NUMBER,
• LOT_EXPIRATION_DATE,
• TRANSACTION_QUANTITY,
• SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)

MTL_SERIAL_NUMBERS_INTERFACE:

• TRANSACTION_INTERFACE_ID,
• FM_SERIAL_NUMBER,
• TO_SERIAL_NUMBER,
• VENDOR_SERIAL_NUMBER

Read More »

Item import (Item conversion)

Item import (Item conversion)

The Item Interface lets you import items into Oracle Inventory.

Pre-requisites:

• Creating an Organization
• Code Combinations
• Templates
• Defining Item Status Codes
• Defining Item Types

Interface tables:

• MTL_SYSTEM_ITEMS_INTERFACE
• MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
• MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
• MTL_INTERFACE_ERRORS (View errors after import)

Concurrent Program:

• Item import

In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.

Base Tables:

• MTL_SYSTEM_ITEMS_B
• MTL_ITEM_REVISIONS_B
• MTL_CATEGORIES_B
• MTL_CATEGORY_SETS_B
• MTL_ITEM_STATUS
• MTL_ITEM_TEMPLATES

Validations:

• Check for valid item type.
• Check for valid part_id/segment of the source table.
• Validate part_id/segment1 for master org.
• Validate and translate template id of the source table.
• Check for valid template id. (Attributes are already set for items, default attributes for that template, i.e., purchasable, stockable, etc )
• Check for valid item status.
• Validate primary uom of the source table.
• Validate attribute values.
• Validate other UOMs of the source table.
• Check for unique item type. Discard the item, if part has non-unique item type.
• Check for description, inv_um uniqueness
• Validate organization id.
• Load master records and category records only if all
• Load child record if no error found.

Important Columns:

Some important columns that need to populated in the interface tables:

MTL_SYSTEM_ITEMS_INTERFACE:

• PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
• TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
• SET_PROCESS_ID = 1
• ORGANIZATION_ID
• DESCRIPTION
• ITEM_NUMBER and/or SEGMENT (n)
• MATERIAL_COST
• REVISION
• TEMPLATE_ID
• SUMMARY_FLAG
• ENABLED_FLAG
• PURCHASING_ITEM_FLAG
• SALES_ACCOUNT (defaulted from MTL_PARAMETERS.SALES_ACCOUNT)
• COST_OF_SALES_ACCOUNT(defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)

MTL_ITEM_CATEGORIES_INTERFACE:

• INVENTORY_ITEM_ID or ITEM_NUMBER.
• ORGANIZATION_ID or ORGANIZATION_CODE or both.
• TRANSACTION_TYPE = 'CREATE' ('UPDATE' or 'DELETE' is not possible through Item Import).
• CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
• CATEGORY_ID or CATEGORY_NAME or both.
• PROCESS_FLAG = 1
• SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if you are importing item and category assignment together)

MTL_ITEM_REVISIONS_INTERFACE:

• INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the ORGANIZATION_ID or ORGANIZATION_CODE or both)
• REVISION
• CHANGE_NOTICE
• ECN_INITIATION_DATE
• IMPLEMENTATION_DATE
• IMPLEMENTED_SERIAL_NUMBER
• EFFECTIVITY_DATE
• ATTRIBUTE_CATEGORY
• ATTRIBUTEn
• REVISED_ITEM_SEQUENCE_ID
• DESCRIPTION
• PROCESS_FLAG = 1
• TRANSACTION_TYPE = 'CREATE'
• SET_PROCESS_ID = 1

Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Read More »

Order Import Interface (Sales Order Conversion)

Order Import Interface (Sales Order Conversion)

Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.

Prerequisites:

• Order Type
• Line Type
• Items
• Customers
• Ship Method/ Freight Carrier
• Sales Person
• Sales Territories
• Customer Order Holds
• Sub Inventory/ Locations
• On hand Quantity

Interface tables:

• OE_HEADERS_IFACE_ALL
• OE_LINES_IFACE_ALL
• OE_ACTIONS_IFACE_ALL
• OE_ORDER_CUST_IFACE_ALL
• OE_PRICE_ADJS_IFACE_ALL
• OE_PRICE_ATTS_IFACE_ALL

Base tables:

• OE_ORDER_HEADERS_ALL
• OE_ORDER_LINES_ALL

Pricing tables:

• QP_PRICING_ATTRIBUTES

Concurrent Program:

• Order Import

Validations:

• Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
• Check for sales_rep_id. Should exist for a booked order.
• Ordered_date should exist (header level)
• Delivery_lead_time should exist (line level)
• Earliest_acceptable_date should exist.
• Freight_terms should exist

Notes:

During import of orders, shipping tables are not populated. If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.

Orders can be categorized based on their status:

1. Entered orders
2. Booked orders
3. Closed orders

Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders.

Important Columns:

Some important columns that need to populated in the interface tables:

OE_HEADERS_IFACE_ALL:

• ORIG_SYS_DOCUMENT_REF
• ORDER_SOURCE
• CONVERSION_RATE
• ORG_ID
• ORDER_TYPE_ID
• PRICE_LIST
• SOLD_FROM_ORG_ID
• SOLD_TO_ORG_ID
• SHIP_TO_ORG_ID
• SHIP_FROM_ORG_ID
• CUSTOMER_NAME
• INVOICE_TO_ORG_ID
• OPERATION_CODE
• OE_LINES_IFACE_ALL
• ORDER_SOURCE_ID
• ORIG_SYS_DOCUMENT_REF
• ORIG_SYS_LINE_REF
• ORIG_SYS_SHIPMENT_REF
• INVENTORY_ITEM_ID
• LINK_TO_LINE_REF
• REQUEST_DATE
• DELIVERY_LEAD_TIME
• DELIVERY_ID
• ORDERED_QUANTITY
• ORDER_QUANTITY_UOM
• SHIPPING_QUANTITY
• PRICING_QUANTITY
• PRICING_QUANTITY_UOM
• SOLD_FROM_ORG_ID
• SOLD_TO_ORG_ID
• INVOICE_TO_ ORG_ID
• SHIP_TO_ORG_ID
• PRICE_LIST_ID
• PAYMENT_TERM_ID

Read More »

Complete GL Interface Standard Program & Validation Columns

Complete GL Interface

Standard Program:

• Go to General Ledger Vision Operations(USA)
• Run =>Import
• Here Give the Source name and Save.

While Click on the save button  Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Otherwise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there...........

Validation Columns:

• Source
• period_name
• currency_code
• set_of_books_id
• je_source
• je_catregory
• accounting_date
• entered_dr
• entered_cr
• accounted_cr
• accounted_dr
• encumberance_type_id

Complete GL Interface

Read More »

GL Interface Package & Procedure

Complete GL Interface

GL Interface Package & Procedure

CREATE OR REPLACE package body APPS.XX_GL_INT_PKG is
procedure GL_INT_LOG(p_msg in varchar2) is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;
procedure GL_INT_MAIN(errbuf out varchar2, retcode out varchar2) is
 cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;
 v_gl_int    gl_interface%rowtype;
 v_process_flag    varchar2(10);
 v_error_msg   varchar2(100);
 v_tot_err_msg   varchar2(1000);
 begin
   GL_INT_LOG('before entering the loop');
 for i in c1 loop
                v_error_msg :=null;
                v_process_flag:='S';
                v_tot_err_msg:=null;
                v_gl_int:=null;
              --currency_code validation
                begin
                select  currency_code into v_gl_int.currency_code
                                      from fnd_currencies
                                     where currency_code=i.currency_code;
                 exception
                 when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid Currency Code =>'||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                 when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception at Currency Code =>'||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                end;    
     
                --user_je_source_name validation
             begin
                  select user_je_source_name into v_gl_int.user_je_source_name
                                             from gl_je_sources
                                            where user_je_source_name=i.user_je_source_name;
                  exception
                 when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid Sourec Name =>'||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                 when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception at Sourec Name =>'||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                end;    
             --category_name  validation
                begin
                     select user_je_category_name into v_gl_int.user_je_category_name
                     from gl_je_categories
                     where user_je_category_name=i.user_je_category_name;
                  exception
                  when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid category_name =>'||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                  when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception at category_name =>'||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;  
               end;
             --user id validation
               begin
                     select user_id into v_gl_int.created_by from fnd_user
                                   where  user_id = i.created_by;
                  exception
                  when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid user id =>'||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                  when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception at user id =>'||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                 end;
             -- set of books id validation
               begin
               SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id
                      FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;
                   exception
                  when no_data_found then
                    v_process_flag:='E';
                    v_error_msg  := 'Invalid set of books id =>'||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                  when others then
                    v_process_flag:='E';
                    v_error_msg   := ' Exception atset of books id =>'||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;
                end;
           v_gl_int.status                    :=i.status;
                        -- v_gl_int.set_of_books_id           :=i.set_of_books_id;
                         v_gl_int.accounting_date           :=i.accounting_date;
                        -- v_gl_int.currency_code             :=i.currency_code;
                         v_gl_int.date_created              :=i.date_created;
                         --v_gl_int.created_by                :=i.created_by;
                         v_gl_int.actual_flag               :=i.actual_flag ;
                         --v_gl_int.user_je_category_name     :=i.user_je_category_name;
                        --v_gl_int.user_je_source_name       :=i.user_je_source_name;
                         v_gl_int.segment1                  :=i.segment1;
                         v_gl_int.segment2                  :=i.segment2;
                         v_gl_int.segment3                  :=i.segment3;
                         v_gl_int.segment4                  :=i.segment4;
                         v_gl_int.segment5                  :=i.segment5 ;
                         v_gl_int.entered_dr                :=i.entered_dr;
                         v_gl_int.entered_cr                :=i.entered_cr;
                         v_gl_int.accounted_dr               :=i.accounted_dr;
                         v_gl_int.accounted_cr              :=i.accounted_cr;
                         v_gl_int.group_id                  :=i.group_id;
                       GL_INT_LOG('before inserting the loop');
               if v_process_flag = 'S' then    
             insert into gl_interface values v_gl_int;
               end if;
           update GL_INTERFACE_TEMP set process_flag=v_process_flag,
                                           error_message=v_tot_err_msg
                       where rowid=i.row_id;
               GL_INT_LOG('after inserting the loop');    
 end loop;
 exception
 when others then
 GL_INT_LOG('exception occured at GL_INT_MAIN loop');
 end GL_INT_MAIN;
 end XX_GL_INT_PKG;
 

Complete GL Interface

Read More »

Control file for GL Interface

Complete GL Interface

Control file for GL Interface

LOAD DATA
INFILE *
TRUNCATE INTO TABLE GL_INTERFACE_TEMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
                (STATUS,
                 SET_OF_BOOKS_ID,
                 ACCOUNTING_DATE,
                 CURRENCY_CODE,
                 DATE_CREATED,
                 CREATED_BY,
                 ACTUAL_FLAG,
                 USER_JE_CATEGORY_NAME,
                 USER_JE_SOURCE_NAME,
                 SEGMENT1,
                 SEGMENT2,
                 SEGMENT3,
                 SEGMENT4,
                 SEGMENT5,
                 ENTERED_DR,
                 ENTERED_CR,
                 ACCOUNTED_DR,
                 ACCOUNTED_CR,
                 GROUP_ID)
BEGIN DATA
"NEW","1","11-AUG-2002","USD","11-AUG-2002","1318","A","Inventory","JETFORMS","01","110","7730","0000","000","555","555","555","555","11"
"NEW","1","11-AUG-2002","USD","11-AUG-2002","1318","A","Inventory","JETFORMS","01","110","7730","0000","000","555","555","555","555","11"

Run below script from Command prompt:

sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/XX_GL.ctl'    log='/apps/aptest/visappl/xxcus/11.5.0/bin/XX_GL.log'

Complete GL Interface

Read More »

Interface Error Tables In Oracle Applications

This table stores the processing messages which will be generated during Order Entry concurrent program execution or UI process. This is a multi-lingual table.

SELECT OPT.*

FROM OE_PROCESSING_MSGS OPM, OE_PROCESSING_MSGS_TL OPT

WHERE     OPM.TRANSACTION_ID = OPT.TRANSACTION_ID

AND OPT.LANGUAGE = 'US'

AND ORIGINAL_SYS_DOCUMENT_REF = LV_DOC_REF

PO_INTERFACE_ERRORS contains error message information generated by the open interface programs in Oracle Purchasing. For every interface transaction row with errors, this table contains a row for each column that has an error.

SELECT *

FROM PO_INTERFACE_ERRORS

WHERE INTERFACE_LINE_ID = LN_INT_TRX_ID -- interface transaction id

MTL_TRANSACTIONS_INTERFACE allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory transaction module.

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, PROCESS_FLAG. Only records with TRANSACTION_MODE of '3', LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

SELECT ERROR_CODE,

            ERROR_EXPLANATION

FROM MTL_TRANSACTIONS_INTERFACE

WHERE TRANSACTION_REFERENCE = LN_TRX_REF

Interface data that failed in Auto Invoice validation are populated with the error details

SELECT *

FROM RA_INTERFACE_ERRORS_ALL

WHERE INTERFACE_LINE_ID = LN_INTF_ID

The HZ_PARTY_INTERFACE_ERRORS table contains the error messages that are generated while mapping data from the HZ_PARTY_INTERFACE table into other HZ tables.

SELECT *

FROM HZ_PARTY_INTERFACE_ERRORS

WHERE PARTY_INTERFACE_ID = LN_PARTY_INTEFACE_ID

MTL_INTERFACE_ERRORS stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

SELECT *

FROM MTL_INTERFACE_ERRORS

WHERE TRANSACTION_ID = LN_TRX_ID --Row identifier for row in MTL_SYSTEM_ITEMS_INTERFACE

WIP_INTERFACE_ERRORS stores the text of errors or warnings issued when loading information through the WIP Scheduling and Open Job and Schedule interfaces. It also stores information about the load requests that generate the errors. When the WIP Mass Load program loads records from the WIP Scheduling Interface, you can print a report showing these errors.

SELECT *

FROM WIP_INTERFACE_ERRORS

WHERE INTERFACE_ID = LN_INTF_ID

AP_INTERFACE_REJECTIONS stores information about invoice data from the AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables which could not be processed by Payables Open Interface Import.

SELECT * FROM AP_INTERFACE_REJECTIONS

Read More »

Difference between Conversion and Interface in Oracle Apps

Interfaces:

• It is schedule concurrent Process which will be executed multiple times
• We will not be knowing flat file Volume
• We need to handle all the expected exceptions
• Error Reports and sending mail alerts, If any error occurs
• Only in Enhancement or customization projects

Conversion:

• One time data transfer
• We will be know exact falt file Volume
• No need to know the exceptions
• Not required to upload all records
• Only in Implementation,Migration or up-gradation projects

Read More »

Oracle Apps Outbound Interface Process - part4

GOTO C/TEMP PATH WE WILL GET FILE

Part1                                         Part2                                             Part3                                     Part4

Read More »