Oracle Apps 4 Beginners: Introduction to Oracle Interfaces

Oracle Apps 4 Beginners: Introduction to Oracle Interfaces - 2

Project / Context

In an ever changing business environment, the need for adopting and building more robust processes in an ERP implementation projects is not only essential but also very crucial for the success of the implementations. The requirement was to seamlessly integrate the Oracle R12 Applications with various other systems. The Interface needs to handle both history data migration as well as replication of ongoing data. Since the volume of history data was expected as very high, the performance of the Interface program needed to be at the optimum level. Since the data needs to flow into Oracle R12 system through various Interfaces from various systems, the business cannot afford to take any failures at Oracle end.

Purpose

For the requirement of building various interfaces in order to integrate seamlessly between Oracle R12 ERP system and various sub systems as well as external systems, we have developed a robust interface design solution which can be adopted by others as well.

By following these guidelines, it would make the application maintenance smoother, flexible, productive, and also ensures seamless integration in an Oracle ERP Applications environment. Suggested solution includes various other functionalities such as usage of lookups for data mapping, error-handling, audit trails, parameter driven debug mode, etc.

Interface Approach

There are two interface development approaches: Open Interface Tables and Application Programming interfaces (APIs).

Open Interface Table Approach

This is the traditional approach where Oracle provides an Open Interface table(s), module wise and entity wise to bring the data from external systems into Oracle Applications. In this approach, the Open Interface table(s) needs to populated, then the respective standard concurrent program need to run to import the data into Oracle base tables. A detailed flow chart of this approach can be found in the subsequent slides.

Application Programming Interface [API]

In this approach, Oracle provides module wise and entity wise published PL/SQL packages called APIs to import the data into Oracle base tables. This provides a flexibility to back out the transaction at any time programmatically prior to issuing commit command. A detailed flow chart of this approach can be found in the subsequent slides.

1 Fail All Fail Concept

This approach, works only for API based Interfaces as it provides the program level control on saving or backing out the data. The only exception to this is GL Interface, as it works with Group Id concept, means the Batch of Journals are submitted as one Batch with a

Group Id, so by naturally as per the Journal Import concurrent program, the way it’s designed, it either imports all the Journals or none of a given Batch. This is explained again in details in a separate section below.

Sample UTL_FILE Package Scripts

Sample UTL_FILE Package Scripts

UTL_FILE package can be used to read and write files that are located on the server. PL/SQL programs can be used to read and write operating system text files with the UTL_FILE package.

UTL_FILE package provides a restricted version of operating system stream file I/O.I/O capabilities provided by UTL_FILE package are similar to standard operating system stream file I/O (OPEN, GET, PUT, CLOSE) capabilities, but it has certain limitations.

The directories from which the files are accessed for reading and writing should be owned by Oracle with permissions set for read, write and execute for everyone.

Following are some sample scripts of UTL_FIILE package:

Example 1:

Step1: create a directory create or replace directory ExampleDir as 'F:\Example' Directory created.

Step 2: Program to open an existing file in write mode and put line

DECLARE

   exfile   UTL_FILE.file_type;

BEGIN

   exfile :=

      UTL_FILE.fopen ('ExampleDir',

                      'file1.txt',

                      'w',

                      32767);

   UTL_FILE.put_line (exfile, 'The new line is inserted in the file');

   UTL_FILE.fclose (exfile);

END;

/

File file1.txt will be created in F:\Example directory.

Example 2:

Step1: create a directory create or replace directory ExampleDir1 as 'F:\Example' Directory created.

Step 2:

CREATE OR REPLACE PROCEDURE tes_utl_file

IS

   Inputfile    UTL_FILE.file_type;

   Outputfile   UTL_FILE.file_type;

   newline      VARCHAR2 (5000);

   x            PLS_INTEGER := 0;

   sflag        BOOLEAN := TRUE;

BEGIN                                                -- open inputfile to read

   Inputfile := UTL_FILE.fopen (‘ExampleDir1’, 'infile.txt', 'r'); -- open outputfile to write

   Outputfile := UTL_FILE.fopen (' ExampleDir1', 'outfile.txt', 'w'); -- if the file to read was opened 

   IF UTL_FILE.is_open (Inputfile)

   THEN                                  -- loop through each line in the file

      LOOP

         BEGIN

            UTL_FILE.get_line (Inputfile, newline); --newline is the output buffer

            x := UTL_FILE.fgetpos (Inputfile); -- x stores the relative offset position for an i/p, in bytes.

            DBMS_OUTPUT.put_line (TO_CHAR (x));        --prints the value of x

            UTL_FILE.put_line (Outputfile, newline, FALSE); --puts the data in newline buffer into o/p

            UTL_FILE.fflush (Outputfile); --forces the data in buffer to outputfile

            IF sflag = TRUE

            THEN

               UTL_FILE.fseek (Inputfile, NULL, -20); -- adjusts file ptr in back direction i/p by 20 bytes.

               sflag := FALSE;

            END IF;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               EXIT;

         END;

      END LOOP;

      COMMIT;

   END IF;

   UTL_FILE.fclose (Inputfile);                        --closing the inputfile

   UTL_FILE.fclose (Outputfile);                      --closing the outputfile

EXCEPTION

   WHEN OTHERS

   THEN

      raise_application_error (-20088, 'Error in Utl_file package');

END;

Run Order import with multiple OPERATION_CODE in

Run Order import with multiple OPERATION_CODE in OE_ACTIONS_IFACE_ALL

How does one run order import with multiple rows in OE_ACTIONS_IFACE_ALL for 2 different value of OPERATION_CODE?

You can populate CHANGE_SEQUENCE field.

For example, to book and put order on hold, populate the interface table with:

#1. To book the order:

OE_HEADERS_IFACE_ALL
ORDER_SOURCE_ID=1
ORIG_SYS_DOCUMENT_REF=1
CHANGE_SEQUENCE=1

OE_ACTIONS_IFACE_ALL
ORDER_SOURCE_ID=1
ORIG_SYS_DOCUMENT_REF=1
CHANGE_SEQUENCE=1
OPERATION_CODE=BOOK_ORDER

#2. To put order on hold:

OE_HEADERS_IFACE_ALL
ORDER_SOURCE_ID=1
ORIG_SYS_DOCUMENT_REF=1
CHANGE_SEQUENCE=2

OE_ACTIONS_IFACE_ALL
ORDER_SOURCE_ID=1
ORIG_SYS_DOCUMENT_REF=1
CHANGE_SEQUENCE=2
OPERATION_CODE=APPLY_HOLD

Note:
When you run the order import, one of the parameter is 'Number of Instances', and you have to pass value of 1, otherwise order import will not run correctly.

Import an Order with its customer account and address using Order Import Process

Import an Order with its customer account and address using Order Import Process

Import an order, the customer account, and the customer address using Order Import process

In this example, the address is categorized as Bill_To and Ship_To address.

1. Populate the order import interface tables.

INSERT INTO oe_headers_iface_all (order_source_id,

                                  orig_sys_document_ref,

                                  operation_code,

                                  ordered_date,

                                  order_type_id,

                                  price_list_id,

                                  orig_sys_customer_ref,

                                  orig_bill_address_ref,

                                  created_by,

                                  creation_date,

                                  last_updated_by,

                                  last_update_date)

     VALUES (1046,                                          -- order_source_id

             'IL070524-01',                           -- orig_sys_document_ref

             'INSERT',                                       -- operation_code

             SYSDATE,                                          -- ordered_date

             1430,                                            -- order_type_id

             1000,                                            -- price_list_id

             'IL CUST ACT 01',                        -- orig_sys_customer_ref

             'IL CUST ADD 01',                        -- orig_bill_address_ref

             1007880,                                            -- created_by

             SYSDATE,                                         -- creation_date

             1007880,                                       -- last_updated_by

             SYSDATE                                       -- last_update_date

                    );

INSERT INTO OE_CUSTOMER_INFO_IFACE_ALL (CREATED_BY,

                                        CREATION_DATE,

                                        CUSTOMER_INFO_REF,

                                        CUSTOMER_INFO_TYPE_CODE,

                                        CUSTOMER_NUMBER,

                                        CUSTOMER_TYPE,

                                        LAST_UPDATE_DATE,

                                        LAST_UPDATED_BY,

                                        ORGANIZATION_NAME,

                                        EMAIL_ADDRESS)

     VALUES (1007880,                                            -- created_by

             SYSDATE,                                         -- creation_date

             'IL CUST ACT 01',                            -- customer_info_ref

             'ACCOUNT',                             -- customer_info_type_code

             'IL CUST 001',                                 -- customer_number

             'ORGANIZATION',                                  -- customer_type

             SYSDATE,                                      -- last_update_date

             1007880,                                       -- last_updated_by

             'IL NEW ORG 01',                             -- organization_name

             'one@one.com'                                    -- email_address

                          );

INSERT INTO OE_CUSTOMER_INFO_IFACE_ALL (ADDRESS1,

                                        ADDRESS2,

                                        ADDRESS3,

                                        ADDRESS4,

                                        CITY,

                                        COUNTRY,

                                        CREATED_BY,

                                        CREATION_DATE,

                                        CUSTOMER_INFO_REF,

                                        CUSTOMER_INFO_TYPE_CODE,

                                        IS_BILL_TO_ADDRESS,

                                        IS_SHIP_TO_ADDRESS,

                                        LAST_UPDATE_DATE,

                                        LAST_UPDATED_BY,

                                        PARENT_CUSTOMER_REF,

                                        POSTAL_CODE,

                                        STATE)

     VALUES ('III rd Floorb',                                      -- address1

             '200 Rhode Island',                                   -- address2

             'San Francisco',                                      -- address3

             'CA 94103',                                           -- address4

             'San Francisco',                                          -- city

             'US',                                                  -- country

             1007880,                                            -- created_by

             SYSDATE,                                         -- creation_date

             'IL CUST ADD 01',                            -- customer_info_ref

             'ADDRESS',                             -- customer_info_type_code

             'Y',                                        -- is_bill_to_address

             'Y',                                        -- is_ship_to_address

             SYSDATE,                                      -- last_update_date

             1007880,                                       -- last_updated_by

             'IL CUST ACT 01',                          -- parent_customer_ref

             '94103',                                           -- postal_code

             'CA'                                                     -- state

                 );

Note:

You only need to have one record with customer_info_type_code = 'ADDRESS' in OE_CUSTOMER_INFO_IFACE_ALL if the address has Ship-To and Bill-To Business Purpose.

2. Run Order Import process

It should import new order and new customer account, customer address.

How to create a new customer during Order Import

How to create a new customer during Order Import

Order Management provides the capability to add a new customer, the address and contacts using the Order Import feature.

The table OE_CUSTOMER_INFO_IFACE_ALL needs to be populated for this to occur. 
Based on the data available in the OE_HEADERS_IFACE_ALL the data from the table OE_CUSTOMER_INFO_IFACE_ALL will be processed to add a new customer.
This table can also be used to import a new address only or contact information of an existing Customer.

The link between the header interface record and the customer interface record is made through the column CUSTOMER_INFO_REF of the table OE_CUSTOMER_INFO_IFACE_ALL. The link depends on the CUSTOMER_INFO_TYPE_CODE. When the order import is used to create a new customer (new account/address/contact etc), the CUSTOMER_INFO_TYPE_CODE should be either ('ACCOUNT', 'ADDRESS', or 'CONTACT') to denote the type of information in the record. Hence a new Account (CUSTOMER_INFO_TYPE_CODE -> ACCOUNT) can be added to the current Customer (CURRENT_CUSTOMER_ID).

The OE_CUSTOMER_INFO_IFACE_ALL table needs to be populated. The number of records being populated will depend on what is being imported. For example while importing only the customer account only one record will be populated and while importing account, address and contacts three or more records will be populated.

If the customer addresses are to be different for ship, bill, and deliver, then three different records need to be populated. Whether an address is for ship, bill, or deliver, is decided by the following columns of the OE_CUSTOMER_INFO_IFACE_ALL table:

* IS_SHIP_TO_ADDRESS,
* IS_BILL_TO_ADDRESS,
* IS_DELIVER_TO_ADDRESS.

These columns take the values 'Y'™, 'N'™ or null. So if the particular record needs to be only a bill to address, then the IS_BILL_TO_ADDRESS should be set as 'Y'™ and the rest as 'N' or null. If the same address needs to be Bill to, ship to and deliver to Address then the flag should be set as 'Y'™ against all the three columns.

The link between the Account record and the address or contact record is through the column PARENT_CUSTOMER_REF. This should be populated in the address or contact record with the CUSTOMER_INFO_REF of the account record.

Note, the OE_CUSTOMER_INFO_IFACE_ALL table (used for adding Customer/Address/Contact information) is the only table from above that does not have an ORDER_SOURCE_ID column. A link between the order header record is made through the column CUSTOMER_INFO_REF of the table OE_CUSTOMER_INFO_IFACE_ALL and a column in the OE_HEADERS_IFACE_ALL table.

Also notice that to create a new Customer and Party within the Trading Community Architecture, the profile option 'HZ: Generate Party Number' should be set to 'YES' (updateable at Site, Application and responsibility levels).

XML Publisher report

XML Publisher report
1)Develop the Report(.rdf) Only DataModel
2)Save the report and Compoile and Generate the data in .xml Format File=>Generate to File => XML
3)Open the Ms-WOrd document Design the Layout include the follwing Tool Bar options called Form and Templete builder
4)Define the Table and Specify the Titles and Labels whatever we would like to print
5)Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax : Here G_USER_ID is Group name from .rdf Data Model Note: It means Indirectley Repeating Frame starting
6)Define the Fields which we would like to display Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax ?
Note: Define all the fields like this
7)We have to close the Repeating Frame (For each Loop) Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax
8)Save the document in .rtf(Rich Text File format)
9)Load the .xml file data and generate the Output in pdf Format Data=>Load XMl Data=>Load the .xml File=>Once Data is Loaded succesfully
10)Generate the Preview as per this Preview=>PDF

Registering in Apps
1)Develop the .rdf file and .rtf file
2)Register both .rdf and .rtf file
.rdf File
registration
1)Move the .rdf file into CUS_TOP\11.5.0\Reports\US
2)Create Execuiteable
Concurrent Program Output = XML
Request Group
Responsibility
User
SRS
3)Copy the Concurrent Program Short name .rtf File
Regitration:
1)Goto the XML Publisher Administrator Responsibility
2)Select Data Definations form Enter Data Defination
Name = Enter Any Name
Code = Concurrent Program Short Name
Application = Any application
startdate = Current Date
3)Copy the DataDefination name we have to add this to the Templete
4)Open the Templete Form
Enter Templete Name : Any Name
DataDefination : enter data defination whatever we have created
Code : Concurrent Program Short Name
Type : rtf
application : any Application
File : Upload the .rtf file
Laguage : English
Territory : United States
5)Select Apply button
After register the .rtf file submit concurrent Program from SRS window system will automatically pick the .rtf file and generates the Output.
If we want to customze the .rtf file we have to download by using templete , Customize thenupload the .rtf templete

Oracle/PLSQL: Insert multiple rows with a single INSERT statement

---

Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?
Answer: You can insert multiple rows using the following syntax:

INSERT ALL
   INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
   INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
   INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;

Example #1

If you wanted to insert 3 rows into the suppliers table, you could run the following SQL statement:

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

Example #2

You can also insert multiple rows into multiple tables. For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This example will insert 2 rows into the suppliers table and 1 row into the customers table.