Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

EBS – Integration/Interface options

Posted on September 7th, 2007 by Sanjit Anand ||Email This Post Email This Post

integration

 

Some time back in last post we have already seen the definition of interfaces and there types.Just to summarize here are the high points for interface :

  • Import data from any environment , including own system or old system or from data collection device or external system or any others in the easier way possible,
  • This consist of review the result of import run which as whole include verification ,validation, review, reimport and error identification

Guess the next question in your mind will be what are the options available for interfacing with oracle ERP or any other ERP? The way integration and interfacing works in typical commercial application have almost similar in nature. Lets emphasize some options that technology offer in Oracle application. But the option will be drived on the basis of nature of system and process how its works , moreover Cost is yet another driving factors. From the integration and interfacing these important factors are :

  • Frequency
  • Mode
  • Volume
  • Technology
  • Security ( accessing data)

Is this really driving the integration decisions for choice.? …The answer is YES, on the top of other factors like cost, support etc…etc.But how…lets understand what is really means and how they really affect in :

  • Frequency – The frequency is important in term of running the interface. Many times a interface need to run during month end processing, many times it runs in nightly basis as most of airlines industry updations happen in night only, or may be weekly only.
  • Batch – It should be real time, one time in day or one in batch …somehow it is linked with Frequency.
  • Volume – This is yet another important to understand what volume data need to interface. This is very very important which is affect how technology is helpful in handling such a big volume.
  • Technology : This is based out of all above three factors ,
  • Accessing data :What access is there to the data? Typically points considered here are like;
    • One way sync (Read Only)
    • vs. bi-directional (Read Write)
    • vs. access in place

Under these set of schema , a developer and designer have to take a call which options should he/she can go for interfacing?? Lets take some of the options :

Before exploring the some options, note that integration is broadly categorize into two types:

  • Data Integration
  • Application Integration

We are not going to discuss details her, lets take some of the options as for integration and interfacing in EBS.

Options 1: Open Interface Table (OIT)?

InterfaceAPI's
This is the the most standard way for doing interfacing in oracle application till day.This consist of developed interface table and programs. The underline interface table allow you to load data and then call concurrent program if any else use the standard AOL API’s and have a custom concurrent program. The standard/custom concurrent program will take data from the interface table validate it and the data into application base products table.

This kind of Open Interface Table satisfy in most of time, as design and development does not requires much efforts.In oracle each product has number of OIT available to cater most of interfacing/integration need.

Options 2 : Application Programming Interface(API’s)APIS

Many time we need a tighter integration to oracle application that can be provided with interface table. In those cases, Oracle Application API’s are indeed a good Options .APIs allow for tight integration into oracle application product. There are also 3rd party products that need to bolt-on into oracle apps with its integration capability. A typical API’s works as figure in the right.

Options 3 : Enterprise Application Integration Tools (EAI) /Middleware Tools

When there is no standard Oracle process (no interface tables) as well no API’s to import data.In that case, you have to write a program that inserts data directly into the Oracle Base Tables.Though this is not recommended by oracle as it any changes from oracle standard functionality may malfunction during patch or upgrade version. So there is another options where we can use some EAI or middle ware tool . EAI/Middle ware tools typically works as adaptor or connector which then take care of any insert /update in oracle base table without any risk.There are number of adapter available to integrate with oracle application.

These tools normally provide robust visual development GUI as well as have strong functionality to provide integration monitoring and alerting tools. I personally know Taviz( How good this is..i really like this ..sorry guys i am not sells person but Still i will recommode). Normally these tools are higher-quality than a options discussed in OIT or API’s.

There are EAI vendors which has capability with interface and integrate with oracle application. Some of them are oracle cerified partners.

  • Cast Iron
  • WebMethods
  • Tibco
  • Informatica
  • Blue Wolf
  • Taviz(formerly SmartDB)
  • Crossroads
  • SeeBeyond (formerly STC)
  • Vitria

Normally most of EAI products provide a tool called “Studio” which uses a graphical environment for you to design integration process flows called orchestrations (integrations). Integrations are deployed to a provided ‘Application Router’, which is an integration appliance pre-configured before it is installed at the customer’s site.Provides native ‘Connector’ to Oracle E-Business Suite.

Options 4 : EDI

EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO’s, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.This is also a good options. More details can be found in my last post.1.2

A bit on other options

Options 5 : BPEL Process Manager

  • BPEL stands for Business Process Execution Language (This was developed by IBM, BEA, and MSFT)
  • Development tools and server acquired with Oracle’s purchase of Collaxa Inc.
  • Requires extra licenses beyond Oracle application licenses
  • Uses industry standards, web services, and business process design to orchestrate integration development
  • Marketed very heavily by Oracle as their current and future integration standard

It is typically as similar to EAI but i personally found bit simpler. It Provides “Dashboard” for server maintenance and monitoring and moreover this is exposes Oracle APIs as web services. Only drawback is extra license and relatively higher learning curve may lead to higher initial costs

Options 6 : other Oracle Technology

By means of making a either PL/sql or stored procedure or hybrid process.

  • PL / SQL
    • Oracle Corporation’s proprietary server-based procedural extension to the SQL database language
    • Relatively sophisticated procedural programming language
    • Compiled to DB; allows for syntax checking and high-performance DB integration
    • Myriad of purposes in Oracle applications: application logic, integration logic, exception handling
    • Exposes an API layer to Oracle application logic
  • Stored Procedures
    • Can be implemented as server-side Java
    • Capable of calling PL/SQL using JDBC; capable of being called by PL/SQL
    • Higher computational performance than PL/SQL
    • Allows for use of an industry standard technology instead of proprietary PL/SQL
    • Often coupled with PL/SQL as a hybrid approach to Oracle application integration
  • Oracle Advanced Queuing (AQ)
    • Database Integrated Messaging
    • Integrated data, message, and message history
    • Recoverability(any point in time recovery)
    • Auditing,Tracking, Non-repudiation
    • Business Intelligence on message store
    • High Performance
    • Integrated with MQSeries via Oracle Messaging Gateway

Will take some deep drive for options 4 and options 5 in some other post.

Posted in API Integration | 5 Comments »

Oracle API Availability – Input/Output

Posted on August 11th, 2007 by Sanjit Anand ||Email This Post Email This Post

What is FND_FILE

An API that contains procedures to write text to log and output files which can be viewed once the Concurrent program get completed in output or log . These procedures can only be used within PL/SQL Procedures which, are concurrent programs.

Internally its uses the oracle supplied database package UTL_FILE

  • utl_file_dir parameter must be set to include the log and out directories for the applications that will run concurrent requests using FND_FILE
  • Database owner must have write privileges on those directories
  • Take a note that FND_FILE supports a maximum buffer line size of 32K for both log and output files.

Here are the list of Options available within API.

  • FND_FILE.PUT

Useful for writing text to a file without a new line character. Multiple calls will concatenate the text .
Does not include new line character

FND_FILE.PUT (which IN NUMBER, buff IN VARCHAR2)
Which – Means LOG file or OUTPUT file.

  • FND_FILE.LOG
  • FND_FILE.OUTPUT

Buff – Means text to write.

Usage
FND_File.Put(FND_File.Log, ‘This text written to Log file’);

FND_File.Put(FND_File.Output, ‘This text written to Log file’);

  • FND_FILE.PUT_LINE

This is used to write a line of text to a file followed by a new line character

FND_FILE.PUT_LINE (which IN NUMBER, buff IN VARCHAR2)

Usage
FND_File.Put_Line(FND_File.Out, ‘This text written to Out file’);

  • FND_FILE.NEW_LINE

FND_FILE.NEW_LINE( which IN NUMBER, LINES IN NATURAL:= 1)
Writes line terminators (new line characters) to a file.
Eg. FND_FILE.NEW_LINE(FND_FILE.LOG,2)

  • FND_File.PUT_NAMES

This is used to sets temporary log and out file names and directory, which normally used during test and debug phase.

FND_FILE.PUT_NAMES(p_log in varchar2,
p_out in varchar2,
p_dir in varchar2)
Usage
FND_File.Put_Names(‘Order123.log’, ‘Order123.out’, ‘/usr/temp/outfiles’);

Example
BEGIN
fnd_file.put_names(‘test.log’, ‘test.out’,’‘/usr/temp/outfiles’’);
fnd_file.put_line(fnd_file.output,’Called stored procedure’);
/* add logic, etc… */

fnd_file.put_line(fnd_file.output, ‘transaction locked at point A’);
/* More logic, etc… */

  • FND_FILE.CLOSE
    This will closes open files.

This is normally used only during test and debug phase

Usage:
FND_File.Close;

Posted in API Integration | 2 Comments »

Oracle API Availability – Concurrent Program

Posted on August 7th, 2007 by Sanjit Anand ||Email This Post Email This Post

Many times, we need to submit concurrent requests and request sets from non SRS window like operating system or PL/sql . In Oracle Applications there are API’s to take these options. Here are some of the API’s function and usage..

  • FND_REQUEST
    • This API is used Submit concurrent requests from backend. As we know there are couple of options avaible in oracle application to submit a concurrent requset like SRS, CONSCUB and FND’s API. This is API offers some great functionality to use via PL/sql or any other technology like , Host Program or java program.
  • FND_CONCURRENT
    • Retrieve information about concurrent requests
    • Set information about concurrent requests
  • Set_Options ·
    • This is a function of boolen type
    • Set request options
    • This is Call before submitting the request

usage:

FND_REQUEST.SET_OPTIONS(IMPLICIT = > ‘ERROR’);

  • Set_Repeat_Options ·
    • Function, returns TRUE or FALSE
    • Set repeat options
    • Call before submitting the request
  • Set_Print_Options ·
    • Function, returns TRUE or FALSE
    • Set print options (note, some options can not be over-ridden)
    • Call before submitting the request

Called before submitting request if the printing of output has to be controlled with specific printer/style/copies etc., Optionally call for each program in the request set.

Usage :

fnd_submit.set_print_options(NULL,NULL,NULL,TRUE,’N’);

or

  • fnd_request.set_print_options (printer =>'<printer name>’,
    style =>’LANDSCAPE’,
    copies=>1,
    save_output => TRUE,
    print_together => ‘N’)
  • Add_printer
    • Called after set print options to add a printer to the print list.Optionally call for each program in the request set.
    • Returns TRUE on successful completion, and FALSE otherwise

Usage

Function FND_SUBMIT.ADD_PRINTER (printer =>’Printer Name’,
copies => null) ;

  • Submit_Request
    • Function, returns Request Id or 0·
    • If submitting from a form, all Arguments must be specified
  • Set_Mode (server only)
    • Function, returns TRUE or FALSE
    • Must use if request is submitted from a database trigger.
    • Call before submitting the request
    • Failure in the database trigger call of FND_SUBMIT.SUBMIT_SET does not rollback changes
  • Get_Request_Status
    • Function returns TRUE or FALSE
    • Returns status information in function output parameters
  • Wait_For_Request

When submitting concurrent requests using PL/SQL, it is often desired to have the parent process wait until all the child processes have completed before completing itself.

This function Wait for the request completion, then return the request phase/status and completion message to the caller. Also call sleep between database checks.

  • Function, returns TRUE or FALSE
  • Returns status information in function output parameters

FUNCTION WAIT_FOR_REQUEST (request_id IN number default NULL,
interval IN number default 60,
max_wait IN number default 0,
phase OUT varchar2,
status OUT varchar2,
dev_phase OUT varchar2,
dev_status OUT varchar2,
message OUT varchar2) return Boolean;

This API Wait for the request completion, then return the request phase/status and completion message to the caller. Also call sleep between database checks.

  • Arguments (input)
    • request_id :The request ID of the program to wait on.
    • interval :Time to wait between checks. This is the number of seconds to sleep. The default is 60 seconds.
    • max_wait :The maximum time in seconds to wait for the requests completion.
  • Arguments (output) :
    • phase :The user friendly request phase from FND_LOOKUPS.
    • status :The user friendly request status from FND_LOOKUPS.
    • dev_phase :The request phase as a constant string that can be used for program logic comparisons.
    • dev_status :The request status as a constant string that can be used for program logic comparisons.
    • message :The completion message supplied if the request has completed.

Usage

IF request_id = 0 THEN
fnd_file.put_line(fnd_file.log, ‘Request Not Submitted.’);
ELSE

call_status := fnd_concurrent.wait_for_request(request_id, v_interval, v_max_wait, request_phase, request_status, dev_request_phase, dev_request_status, request_status_mesg);

END IF;

IF call_status = TRUE THEN
IF dev_request_phase!= ‘Completed’ OR
dev_request_status IN (‘Cancelled’,’Error’,’Terminated’) THEN
DBMS_OUTPUT.PUT_LINE(‘STATUS=JOB FAILED’);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(‘WAIT FOR REQUEST FAILED – STATUS UNKNOWN’);
DBMS_OUTPUT.PUT_LINE(‘STATUS=JOB FAILED’);
END IF;

  • Set_Complete_Status (server only)
    • Function, returns TRUE or FALSE
    • Called from a concurrent program to set its own completion status
    • These have three status
      • NORMAL
      • WARNING
      • ERROR
  • Add_notification
    • Called before submission to add a user to the notify list.
    • Optionally call for each program in the reques set.
    • ReturnsTRUE on sucessful completion, and FALSE otherwise.

Usage
FND_SUBMIT.ADD_NOTIFICATION (user ==>’ANAND’) ;

Make sure the name should be from fnd_user table

  • SET_NLS_OPTIONS
    • Called before submitting request to set request attributes.
    • Optionally call for each program in the request set.
    • Returns TRUE on successful completion, and FALSE otherwise.

Usage
FND_SUBMIT.SET_NLS_OPTIONS (language =>’US’,
territory => NULL);

Posted in API Integration | 6 Comments »

Oracle API Availability – Profile

Posted on July 25th, 2007 by Sanjit Anand ||Email This Post Email This Post

In oracle user Profile functionality is provided in the FND_PROFILE package and the FNDSQF library.

What is inside this API:

  • Retrieve user profile values for the current run-time environment
  • Set user profile values for the current run-time environment

There are various Objects that can be used with this API’s. These are discussed below:

1. Put :This can be used to put a value to the specified user profile option.

Usage:

  • FND_Profile.Put(‘PROFILE_NAME’,’New_Value’)
  • FND_Profile.Put(‘USERNAME’, Usr_Name)
  • FND_Profile.Put(‘RESP_ID’, Resp_ID)
  • FND_Profile.Put(‘RESP_APPL_ID’, Resp_App_ID)
  • FND_Profile.Put(‘USER_ID’, User_ID)

2.DEFINED : this is function returns TRUE if a value has been assigned to the specified profile option.

Usage:

  • SELECT fnd_profile.defined(‘ACCOUNT_GENERATOR:DEBUG_MODE’) ACC_GEN_DEBUG_SESSION_MODE FROM DUAL;

3.GET :This is used to retrieve the current value of the specified user profile option

Usage :

Different type of options can be retrieved like

  • FND_Profile.Get(‘PROFILENAME’, Profile_name);
  • FND_Profile.Get(‘CONC_LOGIN_ID’, Conc_login_id);
  • FND_Profile.Get(‘LOGIN_ID’, loginid);

4.VALUE : This is function which returns a character string. Used to retrieve the current value of the specified user profile option.

Usage:

  • fnd_profile.value(‘PROFILEOPTION’)
  • fnd_profile.value(‘MFG_ORGANIZATION_ID’)
  • fnd_profile.value(‘login_ID’)
  • fnd_profile.value(‘USER_ID’)
  • fnd_profile.value(‘USERNAME’)
  • fnd_profile.value(‘CONCURRENT_REQUEST_ID’)
  • fnd_profile.value(‘GL_SET_OF_BKS_ID’)
  • fnd_profile.value(‘ORG_ID’)
  • fnd_profile.value(‘SO_ORGANIZATION_ID’)
  • fnd_profile.value(‘APPL_SHRT_NAME’)
  • fnd_profile.value(‘RESP_NAME’)
  • fnd_profile.value(‘RESP_ID’)

5.VALUE_WNPS: This is a function, returns a character string. This is Used to retrieve the current value of the specified user profile option without caching it.

6.SAVE_USER :This is function used to save a value for a profile option permanently to the database, for the current user level. It is necessary to explicitly issue a commit when using this function. Returns TRUE if profile option is successfully saved, otherwise FALSE.

7.SAVE :This is function used to save a value for a profile option permanently to the database, for a specified level. It is necessary to explicitly issue a commit when using this function. Returns TRUE if profile option is successfully saved, otherwise FALSE.

Usage

  • fnd_profile.save(‘GUEST_USER_PWD’, ‘GUEST/ORACLE’, ‘SITE’);

8.INITIALIZE :This is used by internal Applications Object Library to initialize the internal profile information at the level context.
The cache is first cleared of all database options.

Usage:

  • fnd_profile.initialize(user_id);

9.PUTMULTIPLE :This is used by internal Applications Object Library to set multiple pairs of profile options and values.

Posted in API Integration | 5 Comments »

Oracle API Availability – Messaging

Posted on July 23rd, 2007 by Sanjit Anand ||Email This Post Email This Post

In Oracle application messaging is typically handled in different way. If you are working in Oracle application you may notice different types of alert window you will get depending upon the condition. In oracle application this would be typical handled by whole set of bundle API , which is called as FND_MessageAPI.

Then the very next question is what is FND_MESSAGE

In simple word , Messaging functionality controlled by FND_MESSAGE package and in the FNDSQF library.

What this API’s do

-Manipulate messages which normally defined in the Message Dictionary under application developer responsibility.
-Client and Server-side procedures place messages on the message stack or in the global area.
-Client-side procedures retrieve messages from the stack and display them from the currently running form

Depending upon the development need , lets understand the various function in three different way.

  1. Server side
  2. client Side

Here are the list of Procedure and Functions which can be used for retrieving and Buffer Messages at Client Side.

  1. Set_Name :Retrieves message from Message Dictionary and sets it on the stack
  2. Set_String :Procedure, used to place the passed string on the message stack.
  3. Set_Token :Substitutes token with specified value
  4. Retrieve :Retrieves a message from the server-side buffer, translates and substitutes tokens, and sets message on the stack
  5. Get (function) :Retrieves a message from the stack. Returns the retrieved message
  6. Clear : Clears the message stack
  7. Get_Encoded :This is a function, returns an encoded message from the message stack.
  8. Parse_encoded :This is a procedure used to parse the Application Short Name and the message Name out of an encoded message

For the retrieving and Buffer Messages at Server Side here are some standard procedure:

  1. Set_Name Sets a message name in the global area without retrieving it from the Message Dictionary
  2. Set_Token Adds a token/value pair to the global area without doing the substitution
  3. Clear Clears the message stack
  4. Get_Number :This is function, returns number of the specified message. Function returns 0 if the message has no number, or the message number is zero. If the message can not be found, function returns NULL
  5. Raise_error :This is Procedure that can raises an application error, returning the message information contained in the package variables

In fact for displaying purpose these are the standard procedures and Functions we can use from API, these are mostly used for displaying message at Client Side.

  • Error :Displays an error message in a forms modal window.
  • Show :Displays an informational message in a forms modal window.:
  • Warn (function) :Displays a warning message in a forms modal window. Allows user to accept or cancel the operation. Returns TRUE if user accepts.:
  • Question :Displays a message and up to three buttons in a forms modal window. Similar to a Forms Alert.
  • Hint :Displays a message in the forms status line.
  • Erase :Clears the forms status line.
  • Debug :Used for debugging. Display the specified string.
    Example: FND_Message.Debug(‘here is error…’);

Apart from this you may also used few other like:

  • WORKING :This is used to display a message in a dialog box without any buttons. The dialog box must be closed explicitly with WORKING_CLOSE.
  • WORKING_CLOSE This can used to close the dialog box invoked by the WORKING procedure.
  • HISTORY :used to display a message in a dialog box, with the History button. If the user presses this button they see all the
    messages on the stack

Is there any way to displaying Messages from Concurrent Programs

Yes, there are two procedure which can be used :

  • Show :Displays top message on the stack in the out file. Does not print message number
  • Error :Displays top message on the stack in the log file. Prints the message number if it is not zero

Hope this would be great help in understanding and utilizing the functionality. Next will discuss how to implement these.

Posted in API Integration | No Comments »

Oracle API Availability – Cash Management

Posted on July 14th, 2007 by Sanjit Anand ||Email This Post Email This Post

In the Oracle API Availability, here is note for Availability in Oracle cash Management.

handBank Statement

Bank Statements Open Interface: Before reconciliation of transactions against a bank statement, information about the statement needs to be entered into the Cash Management system. If the bank provides this information in a flat file, the open interface is used to load the data. Data is loaded by programs written in SQL loader; one program for each type of file provided. The Oracle interface tables to be populated are CE_STATEMENT_HEADERS_INT_ALL, containing header information about the bank statement, and CE_STATEMENT_LINES_INTERFACE, containing the statement line information. The header file contains one record for every account in the bank statement.
handReconciliation
Receipts and payments originating in non-Oracle systems can be reconciled in the Cash Management system using the Reconciliation open interface. However, the open interface view, CE_999_INTERFACE_ V, and open interface package, CE_999_PKG, provided by Oracle, need to be customized first. The Cash Management program automatically retrieves the necessary information from the open interface view for matching statement lines. (The Reconciliation Open Interface is not run as a separate program.)

Normally, the CE_999_INTERFACE_V is defined as a view to the legacy application’s database, and implemented to show all open interface transactions and their status. The view should include available transactions as well as reconciled transactions to bank statements that have not been purged or archived. The CE_999_PKG needs to be customized to access the non-oracle database, lock all transactions and perform any clearing functions so that the open transactions are in sync in both the Oracle and legacy databases. Once the view and package are modified and complete, the Reconciliation program is ready to be run. The reconciliation can be done automatically or manually, as required.

Auto reconciliation: The auto reconciliation program is used to automatically reconcile any bank statement in Oracle Cash Management, assuming the bank statement has been imported into the Cash Management system (a different version of this program does the import of the bank statement, if required). Once the reconciliation program is run, the auto reconciliation Execution Report is reviewed to identify any reconciliation errors that need to be corrected. This report is produced automatically, or can be run, whenever needed.

Manual reconciliation: Manual reconciliation can be used to reconcile any bank statement, whether imported or entered manually. In addition, new bank statement lines can be created while reconciling transactions, as well as update the reconciliation information for a previously manually-or automatically-reconciled statement

handCash Forecasting
Cash forecasting is a planning tool that helps in anticipating the flow of cash in and out of the business, allowing the projection of cash needs and evaluating the company’s liquidity. The Cash Management Forecasting Open Interface allows the utilization of external sources of data as cash inflow and outflow data for flexible cash forecasting.

Two cash forecast source transaction types, Open Interface Inflow and Open Interface Outflow, allow inclusion of external application transaction sources for cash forecasting. Having two separate source transaction types for external source transactions gives an easy way to indicate whether the transaction source consists of cash receipts (inflow) or disbursements (outflow). The Forecasting Open Interface collects cash flow amounts from the external systems, and the Cash Forecasting module summarizes and calculates the data to be included in the cash forecasts along with other Oracle Applications data.

Posted in API Integration, Finance | 2 Comments »

Auto lockbox – Let’s connect with Bank

Posted on June 29th, 2007 by Sanjit Anand ||Email This Post Email This Post

Overview
Auto Lockbox is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing. Auto Lockbox eliminates manual data entry by automatically processing receipts that are sent directly to your bank. You can also use Auto Lockbox for historical data conversion. For example, you can use Auto Lockbox to transfer receipts from your previous accounting system into Receivables. Auto Lockbox ensures that the receipts are accurate and valid before transferring them into Receivables.

Benefit of auto lockbox

  • Eliminates manual data entry
  • Streamlines the application of receipt to outstanding transactions
  • Effectively manages cash flow by reducing turnover for converting checks into cash

When can I use lockbox

  • apply receipt to outstanding invoice
  • import historical receipt data
  • auto lockbox report , such as the post quick cash Execution Report, are a good tool to reconcile auto lockbox receipt with the bank transmission

What is inside the lockbox process

This consist of 3 steps process, viz

1. Import: During this step, Lockbox reads and formats the data from your bank file into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader script.

2. Validation: The validation program checks data in this interface table for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL) . At this point, you can optionally query your receipts in the QuickCash window and change how they will be applied before submitting the final step, Post QuickCash.

3. Post QuickCash: This step applies the receipts and updates your customer’s balances.

Process Flow
Here is schematic data flow and process flow of lockbox process

lockbox

Set Up
These are the Normal Set Required for Auto lockbox. This is part of AR Set up:

  • Define Banks
    • Define Remittance Bank with Account use Internal where checks from customer are deposited.
  • Define Receipt Class
    • Define a Receipt class to determine the required processing steps for receipts to which you assign payment methods with this class.
  • Define Payment Methods
    • Define a payment method with all receipt accounts.
  • Define Receipt Source
    • Define Receipt Batch Source and attach receipt class, payment method and remittance bank account information to the Receipt Source.
  • Define Lockbox
    • Define Lockboxes to use the Receivables Autolockbox program.
  • Define Transmission Format
    • Define the Transmission Format which Auto Lockbox uses when importing data into Receivables.
  • Define AutoCash Rule Set
    • autocash rule sets determine how a quickcash receipt is applied to open debit items.
    • enter a sequence for automatiocally applying receipts
    • select one or more autocash rules for receipt application from the list of autocash Rule options
    • Define AutoCash Rule Sets to determine the sequence of rules that Post
    • QuickCash uses to update Customer’s account balances.
  • Control file
    • Create a control file which is used by SQL * Loader to import the data into interface table and it is placed in the directory $AR_TOP/bin
  • Data file
    • data are kept into directory $AR_TOP/bin

Posted in API Integration, Oracle Receivable | 9 Comments »

Oracle API Availability – Oracle Payables (AP)

Posted on June 25th, 2007 by Sanjit Anand ||Email This Post Email This Post

This is in continuation to API’s availability,Here are the API’s availability in AP.

handCredit Card Transaction Processing

  • Manual Credit Card Entry: A form is available for entry through the Oracle Payables application.
  • Credit Card Transaction Interface Table: This process loads transaction data from the credit card issuer (American Express, Diner’s Club, MasterCard and Visa) into Oracles AP module. Oracle Payable then uses this data to confirm transactions with employees. Once the transactions have been approved the credit card transaction lines are imported into Payables Open Interface using the Credit Card Invoice Interface Summary program. The credit card transactions are then available for final import to Oracle Payables where the transactions become invoices.

handCreating Invoices
In Oracle Payables four methods are available for entering an invoice into the system.

  • Manual Invoice Entry: Two methods for manual invoice entry exist. Using Oracle Payables system Invoice Workbench which includes the Invoice Batches window and Distribution window users can enter complex invoices, and invoices requiring online validation. In addition this functionality is useful in processing invoices that require special attention such as immediate payment.
  • The second method of manual invoice entry, Invoice Gateway, is used when entering high volumes of invoices. Generally these invoices do not require online validation or defaulting of accounting information. After entering invoices using the gateway, the invoice import must be run, at that time validation and defaulting is performed. This method of entry can be used in lieu of invoice interfaces. Although, using the gateway can accomplish similar results to that found with an interface process, manual entry is no substitute.
  • Automatic Invoice Creation: Oracle Payables provides functionality to produce periodically recurring invoices. This functionality may be used to substitute manual invoice entry or the payables open interface. In the case of recurring invoices that would normally come from an external system choosing to setup a recurring invoice formula may be an alternative.
  • Using Payables Open Interface: Choose this method for processing large volumes of invoices that originate in external system. Additionally, this process is useful in handling employee expense reports generated through self-service applications, expense reports entered by the Payables Department, invoices for employee credit card expenses, Oracle Projects billing and time and expenses, and EDI invoices transferred from the Oracle EDI gateway.

handPurchase Order Matching

Oracle Payables can be implemented without Oracle Purchasing, to allow this several purchasing tables are installed initially which must be populated using custom interfaces. Only two-way matching is available when data is loaded using the interface method. Two-way approval verifies that purchase order and invoice information match within your tolerances as follows: Quantity billed on the purchase order shipment is less than or equal to Quantity ordered on the purchase order shipment. Invoice price on the purchase order shipment is less than or equal to Purchase order price on the purchase order shipment.
Receipts can only be matched in an install that also includes Oracle Purchasing.
The only method for interfacing data into the purchasing tables described in this section is through a customized interface from an external source. Note: Interfacing this information is not supported by Oracle.

  • Loading PO_HEADERS: Each record in this table represents a purchase order, which is an order for goods or services from a single supplier. Each purchase order may have multiple lines (PO_LINES). In addition, each blanket purchase order may have multiple blanket releases (PO_RELEASES), which release an amount from the blanket.
  • Loading PO_LINES: Each record in this table represents a purchase order line, which identifies the items and unit price for the goods ordered on a purchase order. Each purchase order line may have multiple shipments (PO_LINE_LOCATIONS).
  • Loading PO_LINE_LOCATIONS: Each record in this table represents a purchase order line, which identifies the items and unit price for the goods ordered on a purchase order. Each purchase order line may have multiple shipments (PO_LINE_LOCATIONS).
  • Loading PO_DISTRIBUTIONS/PO_DISTRIBUTIONS_AP_V: Each record in this table/view represents a purchase order distribution, which identifies the account charged for the items on a purchase order shipment.
  • Loading PO_RELEASES: Each record in this table represents a blanket release for a purchase order. A blanket release may create multiple shipments.
  • Loading AP_INVOICES/AP_INVOICE_DISTRIBUTIONS: Each purchase order shipment can be matched to multiple invoices(AP_INVOICES), and a single invoice may be matched to multiple purchase order shipments. When you match an invoice to a purchase order shipment, Payables creates an invoice distribution AP_INVOICE_DISTRIBUTIONS) from each purchase order distribution on the shipment. When you match an invoice to a single purchase order distribution, Payables creates a single invoice distribution from the purchase order distribution.

handVendor Interface

Existing and new vendors must be loaded into the Oracle Payables application to support payables activities. Two methods may be used to enter vendors into Oracle Applications, manual entry and direct loading using a customized loader program.

  • Manual Vendor Loading: Use the Enter Vendor window. Using this functionality vendor header information as well as vendor site information can be entered.
  • Vendor Interface: When interfacing vendors into Oracle Applications two tables must be loaded the PO_VENDORS and PO_VENDOR_SITES_ALL. The vendor table contains all header information and the site table contains information about each the vendor locations such as “ship to” and “bill to”. Using this method makes sense when the volume of new vendors is large. Note: Interfacing this information is not supported by Oracle.
  • Those who are in 11.5.10 they will find yet another method ie by interface.
    You have to Load data into the staging tables first ie.AP_SUPPLIERS_INT – Supplier Information
    AP_SUPPLIER_SITES_INT – Supplier Sites Information ,AP_SUP_SITE_CONTACT_INT – supplier Contact details This uses Vendor ID, Vendor Site Code to relate the contacts to specific vendor. Once data get loaded three interface program should be kicked out which is as

    • Supplier Open Interface Import.
    • Supplier Sites Open Interface Import
    • Supplier Site Contacts Open Interface Import

Posted in API Integration, Oracle Payable | 9 Comments »

Oracle API Availability – Oracle Receivables (AR)

Posted on June 23rd, 2007 by Sanjit Anand ||Email This Post Email This Post

This is in continuation to API’s availability,Here are the API’s availability in AR.

handInvoices

  • Manual Entry and Updating: Manual entry or update of invoices requires the user to have access to the Oracle Application’s Accounts Receivable module and specifically the Enter Invoices form. You can enter and update invoices for valid customers. AR uses AutoAccounting to create the revenue accounting combination based on the invoice line information.
  • AutoInvoice Interface: AutoInvoice interface provides a mechanism for transferring large volumes of invoices, credit memos, debit memos, and on-account credits from third-party and legacy systems into Receivables. Information is loaded into the RA_INTERFACE_LINES_ALL (which stores information about each interface line imported) and RA_INTERFACE_SALESCREDITS_ALL (containing the sales credit information) tables then imported into the Oracle Applications by the AutoInvoice Import process.You can also refer my old post for autoinvoice details.

handReceipts

  • Manual Entry and Updating: Manual entry or update of receipts requires the user to have access to the Oracle Application’s Accounts Receivable module and specifically the Enter Receipts form. You can enter and update receipts for open and future accounting periods.
  • AutoLockbox Interface: AutoLockbox interface provides a mechanism for transferring large volumes of payments from bank files or third party systems into Receivables. Information is loaded into the AR_PAYMENTS_INTERFACE_ALL table then imported into the Oracle Applications by the AutoLockbox Import process.
    The AR_PAYMENTS_INTERFACE_ALL table stores imported lockbox information that has not been validated. AutoLockbox creates one row in this table for each record in a transmission. When the validation step of AutoLockbox is run, Oracle Receivables transfers the information from the AR_PAYMENTS_INTERFACE_ALL tables to the AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL tables.

handCustomers

  • Manual Entry and Updating: Manual entry or update of customers requires the user to have access to the Oracle Application’s Accounts Receivable module and specifically the Enter Customers form. You can enter and update customers, multiple customer addresses (sites), and multiple contact(s) for each address.
  • Customer Interface: The customer interface provides a mechanism for transferring large volumes of customers from third party and legacy systems into Receivables. Information is loaded into the RA_CUSTOMER_INTERFACE, RA_CONTACT_PHONES_INTERFACE, RA_CUSTOMER_PROFILES_INTERFACE, RA_CUSTOMER_BANKS_INTERFACE, and RA_CUST_PAY_METHOD_INTERFACE tables then imported into the Oracle Applications by the Customer Import process.
  • TCA API’s: The customer information can also be loaded into application by TCA API’s.

handSales Tax Rates

  • Manual Entry and Updating: Manual entry or update of sales tax data requires the user to have access to the Oracle Application’s Accounts Receivable module and specifically the Tax Locations and Rates form. You can enter and update location and rate information through this form.
  • Sales Tax Rate Interface: The Sales Tax Rate Interface lets you load sales tax records into your Oracle Receivables application from your sales tax feeder system. Information is loaded into the AR_TAX_INTERFACE table then imported into the Oracle Applications by the Sales Tax Rate Interface process.
    The AR_TAX_INTERFACE table is used to import location, postal code and sales tax rate information into Oracle Receivables. Rows are inserted in this table and then the Sales Tax Interface Program is run to create records in AR_LOCATION_VALUES and AR_LOCATION_RATES. Each row can define a new location and assign to it multiple postal code and effectively date ranges, and each range may have an optional sales tax rate.

handTax Vendor Extension

Oracle provides a Tax Vendor Extension to integrate external tax calculation programs with the Oracle Application’s Accounts Receivable module. This extension allows you to provide for complex tax calculation requirements while retaining the full power of Receivables to create and store other tax data. The tax extension is called whenever a tax rate is calculated by the Receivables Tax Engine.

 

Posted in API Integration, Oracle Receivable | 12 Comments »

Oracle API Availability -Oracle General Ledger (GL)

Posted on June 23rd, 2007 by Sanjit Anand ||Email This Post Email This Post

This is in continuation to my last post API’s availability. Here are the some keynotes on the API’s availability in GL.

handBudget Upload
Oracle Applications provides four methods for adding budgeting transactions, Application Desktop Integrator (ADI) Budget Wizard, manual entry through the application forms, journal import and budget import.

  • ADI: ADI is a spreadsheet-based application that allows the user to format data inside a Microsoft Excel spreadsheet and then upload into Oracle Applications. ADI uses Budget Wizards and Budget Templates to simplify the data entry. In addition, ADI is delivered as standard functionality, as opposed to a custom interface that would require additional development, maintenance and upgrade consideration.
  • Manual Entry and Updating: Manual entry requires the user to have access to the Oracle Application’s General Ledger module and specifically the enter budget amounts forms for your accounts to replace any existing budget balances. You can enter budget amounts for each account in the budget organization one-by-one, or you can use worksheet mode to enter budgets for several accounts at once. Budget rules enable you to distribute budget amounts for all periods.
  • Budget Interface: Budget interface provides a mechanism for transferring large numbers of budget data from a third-party budget source system to your General Ledger application Budget Interface table. Once you load your budget information into the Budget Interface table, you can run Budget Upload to post your budget data into your General Ledger application. Budget Upload uses the Budget Interface table GL_BUDGET_INTERFACE to upload budget information. The Budget Interface table is organized in such a manner that provides a column for each budget period in your current open fiscal year in addition to account and other relevant information on a single budget line.
  • GL Interface: GL Interface is another option for uploading large numbers of budget data from a third-party budget source. With this option the source must provide a file that mimics the layout found in the GL_INTERFACE table. The process for uploading a budget through the journal import process is similar to that found using budget interface functionality. The most significant difference is found in the format of the budget record. Here the record is formatted in such a way that each budget line provides an amount for a single budget period within the open fiscal year. Consequently, assuming a fiscal year has twelve periods, a full fiscal year budget for one account would require twelve individual lines to be processed to produce a budget across the full year.

handImporting Journals

  • Manual Entry and Updating: Manual entry requires the user to have access to the Oracle Application’s General Ledger module. After accessing enter journals form the user is presented with a choice of either entering a new journal or searching an existing journal for update. On a new journal the user can choose to overwrite default information found in header by selecting choices from the drop-down list, in some cells the data is free form and others the default information cannot be changed. The bottom area of the form also known as the journal line detail is used to enter amount, account information and journal line description.
  • ADI: Journal Wizard is functionality provided with ADI that allows you to create journal entries using Excel, then upload them into Oracle GL. With ADI the Wizard generates the journal entry template to match the systems configuration. Therefore, in certain cases where the volume is not excessively large this method may be used to format and upload journal entries to Oracle GL. In addition, ADI is delivered as standard functionality, as opposed to a custom interface that would require additional development, maintenance and upgrade consideration.
  • GL Interface: Use journal import to load large numbers of journal transactions from third-party systems such as payroll, accounts payable and accounts receivables systems. When using the journal import functionality standard and statistical type journals can be processed. In cases where migration from an older system to Oracle GL is taking place, GL Interface can be used to facilitate the process.

handLoading Daily Rates

When using General Ledger’s Multiple Reporting Currencies feature, your daily rates are used to convert your primary set of books’ journals to the appropriate reporting currencies when the journals are copied to your reporting sets of books. Your daily rates must be defined before you post journals in your primary set of books. To load daily currency rate information two options are available, manual entry using the Daily Rates form or direct loading through the GL_DAILY_RATES_INTERFACE table.
You should take a note, normally one currency exchange rate is used for all set of books throughout the organization therefore preventing out-of-balance issues especially in the case of intercompany transactions.

  • Manual Currency Rate Input: This option is used primarily when data volumes are small and updates occur on a monthly intervals. In most cases the data is extracted from the internet or another source and someone is required to calculate a month average then enter the values into the system, using the daily rates form in the Oracle GL application.
  • Automatic Currency Rate Loading: Depending on the frequency and number of currencies used throughout the business, the volume may become too much to handle through a manual entry process. Additionally, choosing this option makes daily updates more practical and accurate. Several services provide daily currency rate information that can be transferred to Oracle GL. Once the file is received the data is upload into the GL_DAILY_RATES_INTERFACE and subsequently a trigger moves the data into a production table.

The GL_DAILY_RATES_INTERFACE is the interface table used to create, update, and delete daily conversion rates. This table should be used to load rates into the GL_DAILY_RATES table.

handGenerating Intercompany Transactions

The Global Intercompany System (GIS) provides a central location for subsidiaries to enter intercompany transactions, those occurring between set of books. Furthermore, the process is built in such a manner that an entry requires approval from the sender and receiver prior to the transaction posting. With this process transaction amounts, dates, and currencies are confirmed by both subsidiaries and subsequently producing more accurate results.

The process provides three mechanisms for entering transactions, manual, automatic transaction line generation and import transactions through the GIS open interface table GL_IEA_INTERFACE. The main factor for choosing which option to use is mainly based on data volume and integration with external systems.

  • Manual Intercompany Transaction Input: Use the Enter Intercompany Transactions window to enter, update, approve, reject or mark transactions for deletion. This process is convenient for quick entry and correction of entries that may have been interfaced in larger batches.
  • Automatic Transaction Line Generation: Using predefined AutoAccounting rules establishing chart of account relationships between GIS senders and receivers, GIS can automatically generate code combinations and amounts for any code combination of the following: sender clearing, receiver distribution, and receiver clearing transaction lines.
  • Intercompany Transaction Import: GIS provides the Open Interface to handle high volumes of intercompany transactions and integration with external systems. The AutoAccounting rules are applicable to transactions entering GIS through the open interface….[]..

Posted in API Integration, Oracle General Ledger | 9 Comments »

« Previous Entries Next Entries »