Interfaces Basics

Interfaces Basics

 

Interfaces are used to integrate external systems and data conversion in Oracle Applications.

è  These can be used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.

è Used extensively at the time of data conversion from legacy/old systems to a fresh implementation of Oracle Applications.

è  Used also at regular intervals when data transfer is from live systems if the system is not represented in Oracle Applications implementation.

 

TYPES OF INTERFACES

 

 Two major types of Interfaces

·         Inbound: To transfer data from external systems to Oracle Applications.

·         Outbound: To transfer data from Oracle Applications to external systems.

 

  Two other distinctions of Interfaces

·         Open Interface: If interface logic is provided by Oracle Applications, it is called an Open Interface.

·         Custom Interface: If the interface logic needs to be developed by the implementation team it is called Custom Interface.

 

OPEN INTERFACE LOGIC

 

  The data from source application is loaded into a database table (called Interface Table).

  The provided program logic validates the records.

  Any errors are transferred into another table (called Error Table).

  The correct records are then transferred through a process into destination application table.

 

INTERFACE COMPONENTS

 

Picture

 

  Source Application

·         You obtain data from a source application to pass on to a destination application for further processing and/or storage.

 

  Source Data Issues

·         Type of file, Size, Frequency of upload, Record length (variable or fixed), Delimiter, Data type of each field, Any unwanted data, Naming convention, Unique ness of file, Location of file and access on the file.

 

  Destination Application

·         You send data to destination application so that the application can perform further processing and/or storage.

 

  Interface Table

·         For inbound interfaces, the interface table is the intermediary table where the data temporarily resides until it is validated and processed into the destination application.

  Identifier Columns

·         Uniquely identify rows in the interface table and provide foreign key reference to both the source and destination applications.

 

  Control Columns

·         Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.

·         WHO Columns are also called control columns.

 

  Data Columns

·         Stores the data that is being converted.

 

  Required Columns

·         Required Columns store the minimum information needed by the destination application to successfully process the interface row.

 

  Derived Columns

·         Derived columns are created by the destination application from information in the required columns

Oracle Cash Management: Internal Bank Creation API's

Oracle Cash Management: Internal Bank Creation

Oracle Internal Banks Conversions

Validations:
1)Country code
2)Operating Units
3)Currency
4)Different Code Combination id

API used

CE_BANK_PUB.CREATE_BANK
              (p_init_msg_list            => p_init_msg_list,
               p_country_code             => c2.country_code,
               p_bank_name                => c2.bank_name,
              -- p_bank_number              => '10202',
               x_bank_id                  => x_bank_id,
               x_return_status            => x_return_status,
               x_msg_count                => x_msg_count,
               x_msg_data                 => x_msg_data
              );
once bank is created bank_id will be returned as X_BANK_ID

we have to use this BANK_ID for creating bracnch

 CE_BANK_PUB.CREATE_BANK_BRANCH
            (p_init_msg_list              => p_init_msg_list,
             p_bank_id                    => p_branch_id,
             p_branch_name                => c2.branch_name,
             p_branch_type                => c2.acc_type,
             p_description                => c2.acc_desc,
             x_branch_id                  => x_branch_id,
             x_return_status              => x_return_status,
             x_msg_count                  => x_msg_count,
             x_msg_data                   => x_msg_data
            );

We will get branch_id as out paramter and this branch_id will be used
in creating bank accounts

   ce_bank_pub.create_bank_acct (p_init_msg_list      => p_init_msg_list,
                                 p_acct_rec           => p_acct_rec,
                                 x_acct_id            => x_acct_id,
                                 x_return_status      => x_return_status,
                                 x_msg_count          => x_msg_count,
                                 x_msg_data           => x_msg_data
                                );
here acct_id will be return as out paramter

Hope you understood the Internal bank increaion using API


Note:This document can be used only for refernece

Oracle APPS: Interfaces , Conversion Basics

Oracle APPS: Interfaces , Conversion Basics

Overview: Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transnational data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications. This article briefs you about some of the major interface programs.
Conversion/Interface Strategy:

1. Data Mapping 
During the data mapping process, list of all the data sets and data elements that will need to be moved into the Oracle tables as part of conversion are identified. Data mapping tables are prepared as part of this activity that show what are the data elements that are needed by the target system to meet the business requirements and from where they will be extracted in the old system.

2. Download Programs
After the conversion data mapping is complete, download programs are developed that are used to extract the identified conversion data elements from the current systems in the form of an ASCII flat file.  The structure of the flat file must match the structure of the Oracle standard interface tables. These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.

3. Upload Program
Once the data has been extracted to a flat file, it is then moved to the target file system and the data from the file is loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities. Then programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables.    4. Interface Program             Once the interface tables are populated, the respective interface program (each data element interface has a specific interface program to run) is submitted.  The interface programs validate the data, derive and assign the default values and ultimately populate the production base tables.

Interface/Conversion examples with details:
            The below list of interfaces/conversions are few examples  Details like pre- requisites required, interface tables, interface program, base tables, validations that need to be performed after inserting the details into the interface tables and required columns that need to be populated in the interface table are discussed for each interface.

----------------GL----------------

1.    Journal import          

2.    Budget import          

3.    Daily Conversion Rates  

----------------AP----------------

4.    AP Invoices        

5.    Vendor          

----------------PO----------------

6.    Purchase Orders          

7.    Requisition         

8.    Receiving        

----------------AR----------------

9.    AR Receipts         

10. Customer conversion          

11. Auto Invoice Interface 

12. Lockbox Interface          

----------------OM----------------

13. Order Import Interface (Sales Order Conversion)        

14. Item import (Item conversion)          

15. Inventory On-hand quantity Interface          

Interfaces Basics

Interfaces Basics

 

Interfaces are used to integrate external systems and data conversion in Oracle Applications.

è  These can be used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.

è Used extensively at the time of data conversion from legacy/old systems to a fresh implementation of Oracle Applications.

è  Used also at regular intervals when data transfer is from live systems if the system is not represented in Oracle Applications implementation.

 

TYPES OF INTERFACES

 

 Two major types of Interfaces

·         Inbound: To transfer data from external systems to Oracle Applications.

·         Outbound: To transfer data from Oracle Applications to external systems.

 

  Two other distinctions of Interfaces

·         Open Interface: If interface logic is provided by Oracle Applications, it is called an Open Interface.

·         Custom Interface: If the interface logic needs to be developed by the implementation team it is called Custom Interface.

 

OPEN INTERFACE LOGIC

 

  The data from source application is loaded into a database table (called Interface Table).

  The provided program logic validates the records.

  Any errors are transferred into another table (called Error Table).

  The correct records are then transferred through a process into destination application table.

 

INTERFACE COMPONENTS

 

Picture

 

  Source Application

·         You obtain data from a source application to pass on to a destination application for further processing and/or storage.

 

  Source Data Issues

·         Type of file, Size, Frequency of upload, Record length (variable or fixed), Delimiter, Data type of each field, Any unwanted data, Naming convention, Unique ness of file, Location of file and access on the file.

 

  Destination Application

·         You send data to destination application so that the application can perform further processing and/or storage.

 

  Interface Table

·         For inbound interfaces, the interface table is the intermediary table where the data temporarily resides until it is validated and processed into the destination application.

  Identifier Columns

·         Uniquely identify rows in the interface table and provide foreign key reference to both the source and destination applications.

 

  Control Columns

·         Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.

·         WHO Columns are also called control columns.

 

  Data Columns

·         Stores the data that is being converted.

 

  Required Columns

·         Required Columns store the minimum information needed by the destination application to successfully process the interface row.

 

  Derived Columns

·         Derived columns are created by the destination application from information in the required columns

 

 

Oracle apps Interfaces API Basics

    

⦁    What is API?
⦁    What is the difference between Client Side API’s and Server Side API’s?
⦁    What is the use of FND_GLOBAL.APPS_INITIALIZE procedure?
⦁    Brief the usage of FND_GLOBAL Package?
⦁    How can you find the application user id in Oracle Application?
⦁    What are all the WHO columns provided by Oracle? Which API used to update the WHO Columns

1)    API stands for Application Programming Interfaces.

2)    APIs those are all stored in the ORACLE database are called Server side API’s. APIs those are in front end libraries are called Client Side APIs.

3)    Usage of FND_GLOBAL.APPS_INITIALIZE procedure are
⦁    It sets the global values & Profile Values in the database session.
⦁    You can use it for routines such as java , PL/SQL or other programs which are not integrated with the Oracle application concurrent processing or Oracle Forms ( Both of which already do similar initialization for database session ) .
⦁    You can use this for manually testing application using SQL*Plus

4)    Usage of FND_GLOBAL package
It is a server–side package returns the values of system global, such as the login/sign on or “session” type of values. You can use the values to set who columns for inserts and updates from stored procedures.

5)    FND_GLOBAL.USER_ID API is used to find the application user id.

6)    Created_by, Created_date, Last_modified_by, Last_modified_date are the WHO columns  in all transaction table provided by Oracle to find who has created or modified the row of the transaction tables.

FND_STANDARD.SET_WHO API is used to Update WHO columns.

 

Oracle Apps Interfaces API basics-2

 

1. How can you retrieve system date from database? Which API having the same functionality in Oracle Apps?

Ans) SELECT sysdate FROM DUAL used to find the system date in Oracle Database. FND_STANDARD.SYSTEM_DATE API having the similar functionality.

2. Can you invoke web browser through any API’s? If so  Which API can do this?

Ans) Yes, you can invoke web browser through API. FND_UTILITIES.OPER_URL API can do this.


3. What is the use of FND_UTILITIES.PARAM_EXISTS API?

Ans) FND_UTILITIES.PARAM_EXISTS API is used to check the given parameter is Exist in the Open Oracle Form or not . If exists it will return True other wise it will return False.

4. FND_FILE support maximum buffer line size _____KB.
Ans)  FND_FILE support maximum buffer line size    32    KB.

5. Shall we use FND_FILE.PUT_NAME & FND_FILE.CLOSE in concurrent programs? If not Why ?

Ans) We should not use FND_FILE.PUT_NAME & FND_FILE.CLOSE in concurrent programs. Because these two operations are automatically done by Oracle Apps we should not override this default operations.

6. What is the difference between FND_FILE.PUT & FND_FILE.PUT_LINE?
Ans) FND_FILE.PUT API is used to write text to a file (without a new line character). Multiple calls to FND_FILE.PUT will produce concatenated text.
FND_FILE.PUT_LINE API is used to write a line of text to a file (followed by a new line character).

7. What are the two types of files we can deliver with the help of FND_FILE Package?
Ans)  FND_FILE.OUT – Oracle Apps Output  File.
       FND_FILE.LOG – Oracle Apps Log File.

8. How can you see the status of the concurrent program through backend?
Ans) With the help of FND_CONCURRENT.GET_REQUEST_STATUS API we can see the status of the concurrent program through backend.

9. What is the use of FND_CONCURRENT.WAIT_FOR_REQUEST API?
Ans)  FND_CONCURRENT.WAIT_FOR_REQUEST API is used to implement the parent-child concurrent programs. Waits for request completion, then returns the request phase/status and completion message to the caller. Goes to sleep between checks for request completion.

10. What is the usage of FND_PROGRAM package?

Ans) The FND_PROGRAM package includes procedures for creating concurrent program executables, concurrent programs with parameters and incompatibility rules, request sets, and request groups.
The FND_PROGRAM package also contains functions you can use to check for the existence of concurrent programs, executables, parameters, and incompatibility rules.

11. What is the usage of FND_CURRENCY Package?
Ans) This package is used to format the currency . We can use it in Client side as well as Server Side .

12. Is it possible to Delete Executable through FND_PROGRAM API’s?
Ans)  Yes , It is Possible to delete executable through FND_PROGRAM.DELETE_EXECUTABLE if it is not attached to the Concurrent program Otherwise not possible to delete.

13. What is the usage of FND_PROGRAM.ENABLE_PROGRAM API?

Ans)      Use this procedure to enable or disable a concurrent program.
  Syntax is:

Procedure FND_PROGRAM.ENABLE_PROGRAM
(Short name IN VARCHAR2,
Application IN VARCHAR2,
ENABLED IN VARCHAR2)

 

14. Can you submit the request from Backend triggers? Which API helps to do that?
Ans)  Yes, You can summit the request with in the backend triggers. Before submitting the request you have to use the FND_REQUEST.SET_MODE API to set the mode. With out using FND_REQUEST.SET_MODE we can’t submit the request from Database Triggers.

15. How can you submit a request from Backend?
Ans) With the help of FND_REQUEST.SUBMIT_REQUEST API we can submit the request in Backend.

16. Why we have to register the tables & columns in AOL?
Ans)  You have to register the table & columns in AOL before you create new flex field or alert on a table. Otherwise not possible to create flex field or alert on it .

17. How can you register a table in AOL?
Ans)  With the help of AD_DD.REGISTER_TABLE API we can register the table in Application Object Library.

18. How can you launch a workflow through backend?
Ans)  With the help of WF_ENGINE.STARTPROCESS we can launch the workflow from backend .

19. What are the two default parameters you have to give when you create stored procedures & functions as a concurrent program?

Ans)  errbuf  - VARCHAR2

      retcode – NUMBER  - 0 for success
                                     1 for success with warning
                                     2 for error