API to Update External Payee

Whenever a supplier/supplier site is created oracle system will automatically create a payee for the supplier and supplier site. Once the Payee is created the user may want to update the payee details.
The API to update the payee is :

IBY_DISBURSEMENT_SETUP_PUB.update_external_vendor

iby_disbursement_setup_pub.update_external_payee 
 (p_api_version => 1.0, 
   p_init_msg_list => fnd_api.g_true, 
p_ext_payee_tab => p_external_payee_tab_type, 
p_ext_payee_id_tab => p_ext_payee_id_tab_type, 
x_return_status => x_return_status, 
x_msg_count => x_msg_count, 
x_msg_data => x_msg_data, 
x_ext_payee_status_tab => l_payee_upd_status 
);

p_external_payee_tab_type is a table type variable of external payees which will take in a number of fields of the table.

p_ext_payee_id_tab_type is a table type variable which will take in only variable : ext_payee_id

When updating an external payee the developer needs to consider the below:
   the api will update all the columns which defined in the record type variable which is passed to the API , thus the developer when assigning values to the record type variable needs to assigne the new values to be updated as well as the old values to the API rec type.

The data updated will be reflected in the table iby_external_payees_all

Difference between Value sets and Lookups

Value sets and Lookups might look similar and confuse some people but there are some basic differences between the two.

Difference 1
Value sets can be attached to parameters of a concurrent program, whereas Lookups can't.

Difference 2
Certain types of Lookups are maintainable by the users too, for example HR Users will maintain "Ethnic Minority" lookups. Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators.

Difference 3
Value sets can contain values that are a result of an SQL Statement.
Hence it is possible to make Value Set list of values dynamic.
On the contrary, Lookup Codes are Static list of values.

Lookups in Oracle

Lookups in Oracle are a collection of values. Lookups are a static collection of codes which are used by oracle for working.

For Example in a form/table if there is a field for Gender rather than storing the values , Male/Female/Unknown the system may prefer to store codes M/F/U . Thus when storing the values rather than storing the description oracle will store the codes M/F/U and display the complete menaning to the user.

Each lookup when defined will have a lookup code and a corresponding meaning for that code. Oracle will internally use codes for working and use meaning when communicating to the user.

Lookup codes are mainly used when working with forms and handling data in the tables.

When ever a lookup is created and entry will be created in the table FND_LOOKUP_TYPES ,

For each lookup type when the value is entered for the lookup entries will be created in the table FND_LOOKUP_VALUES

For the lookups already defined in Oracle each is associated with an application , thus when searching for application specific lookups user should search with the specific application.

Lookups in Oracle are of 3 types:

1. System: These are lookups which are used internally by the system. Users can see and use the lookup but they cannot disable the existing values or add new values.
2. Extensible : These are system provided lookups. Users can add their values if they want , but Seeded values by Oracle cannot be changed 
3. User Defined : User Defined lookups are client specific which are created to meet the business specific customization's. These are created by the users and the users can add new values and disable the older ones.

Value Sets in Oracle Apps

Value Set as the name specifies refers to a set of values. Theses vales can be used to validate the values entered in a felx-field segment, concurrent program parameters and report parameters.

For Creating Value sets the user needs to navigate to a value set and specify the details of the value set.

The user needs to specify the name of the value set.

The main details that need to be entered are:

Name : The name of the value set

Type: The type of value set

Pop-List : When the number of items is between 1-10
List of Values : When the number of items is between 10-100
Long List of Values : When the number of items is more then 1000

Security: Security type can be applied to a value set

No Securtiy: No security is enabled for the value set
Hierarchical Security : Security rule applied to the parent is automatically applied to the child values
Non-Hierarchical Security : Security rule applied to the parent is not applied to the child values
Segment : Value entered for another parameter of the program is the default value

Format Type : Specifies the type of values that can be entered : Number/char/Date

Maximum size : Representes the Longest possible value

Precision : For numberical valu sets , the number of digits after decimal

Validation Type: Refers to the type of validation applied when values are entered for the fields associated with the value set

• None : The values enteres is not validated against any pre-defined set of values , the values is only validated against the format type specified.
• Independent : The value entered is validated against a pre-defined set of values
• Dependent : The value is validated against a set of values which depend on an indipendent value set .
• Table: The value entered is validated against against a list of values which are dependent on a table
• Special and Pair : The value is validated against a set of values in another flex field
• Translatable Independent and dependent : The value is validated against a previously defined list of values.

Values sets are mainly used when specifying the list of data which can be entered as parameters when running a concurrent program.

Profile Options in Oracle

Profile Options are options provided by Oracle which helps the user to customize the application. Profile options act as global variables which help in customization of Oracle Applications.

Profile options make the application flexible and helps the business to achieve the desired functionality with minimum hard-coding.

There are many oracle provided profile options which facilitate the working of oracle applications in the desired manner , we will look at it with the help of an example.In a business, the business will be placing orders to procure stationery items , now for a large scale business there will be numerous users placing the request. In order to minimize wasteful purchase the business might want to put a cap on the number of units that can be requested by each employee the business might keep a cap that clerical level employee can request at the max 2 units of any item , managers 5 units and so on.

For this function the business has a custom form , now to implement this functionality the business will have to include all the complex logic in the form to check for this , and in future if any changes are needed the form needs to be changed. To prevent this type of hardcoding and increase flexibility we can use profile options.

Define a profile options : XXPRO_STAT_REQ.In the form we will have a simple piece of code:

IF :oe_line_block.requesed_qty> fnd_profile.value('XXPRO_STAT_REQ') THENmessage( 'You can’t give discount more than ' || fnd_profile.value('XXPRO_STAT_REQ') || '%' ) ;raise form_trigger_failure ;


Thus with the help of profile options the functionality can be easily implemented , now if there are 1000 users in the system , defining the profile option for each user will be an issue , thus the system can assign the profile option at responsibility level and assign this responsibility for requisition to the users.In future when needed to change the value , the user can query the profile option from the front end and make the changes.Oracle provides the functionality to define the profile options at different levels

1. Site: This field displays the value if set to all the users in the current installation site

1. Application : This value if set will be applicable to all the users working under the responsibilities defined under this application

1. Responsibility : This value if defined will be applicable to all the users currently working under this responsibility

1. User : this value if defined will apply to the current user

Thus if a profile option value is defined at multiple levels then the user level will override the values defined at the responsibility/application/site level and so on

Journals in Oracle

Journal entries are the entering/recording of financial transactions and their monetary value in the transaction accounts of the accounting journal as debits or credits.

Journal entry is strating point of the accounting cycle , Journals are first created for any accounting entry and are then take forward.

For a business any transaction that affects the finances of the business needs to be recorded in the general ledger with proper accounting entries. Oracle creates journal entries for these transactions. Each transaction will have minimum 2 entries one debit and other credit to keep a track of the transaction and its financial impact.

In a complete EBS setup Oracle will receive journal transaction details from various modules:

• Purchasing
• Payables
• Assets
• Receivables
• Work In Progress
• Inventory
• Payroll
• Projects

Using the functionalities provided in EBS we can perform a number of actions on journals

• Creating journal entries
• uploading journals
• import journals
• Reverse journals
• Post journals
• Journal inquiry
• drill down to subledgers
• Run Reports

Types of Journal Entires:

1. Basic Journal Entries: This type of journal entry is used for most journal transactions to record revenues,expenses,accruals , adjustsments and re-classifications. 
2. Reversal Entries: These journals are created for reversing an error, revaluation or encumberance journals. The reverse journals are created either by switching debits and credits or by changing the signs. The reverse entires can be posted in the same or a future acccouting period
3. Recurring Entries : There might be scenarios where some transaction journals will be created on a recurring basis. Recurring journals can be defined for automatic consolidation and elimination of such journals
4. Mass Allocation: Journal entries that utlize a single journal entry to allocate revenues,expenses across a set of centers,departments etc.

Journal Creation Methods :

Journal Import: This is one of the commonest method of Journal creation in which the data is populated into the GL Interface by the different modules or third part systems and then journal is created by running the journal import process.

Web ADI : Another method of journal creation is to upload the data via Web ADI through spreadsheets which will create journals.

Manual: As the name suggests is the journal creation method in which Journals are manually created by the users using the oracle screen

Journal parts:

When Journals are created in the system it will be affecting 3 tables: gl_batches , gl_je_headers and gl_je_lines

When ever a journal is created the system will create a journal batch in which this journal will be included..

Each journal will have 1 journal header and 2 or more journal lines.

The journal header will have details of the journal header and the lines will have details of the journal line ie which accounts have been affected by the journal by which amounts and the debit  or credit amounts.

Interfaces in Oracle

Any business implementing oracle will generally make use of the available interfaces for integration.
Interfaces are the points in the system from where the data generally enters the system.
Oracle EBS consists of a number of modules each of which interact with each other sharing data and other business information. All the data flow between the modules is done via the interfaces provided by Oracle. For Eg: Data Between Order Management and Account Receivable is shared via the AR Interface , between AR and GL via the GL Interface etc.

Interfaces are tables which can be provided in the oracle package or created as a part of some customization in the system.

Interfaces are extensively used:

•  in times of data conversion
• daily interfacing of data from non-oracle and feeder systems
• seamless integration between different oracle modules

Types of Interfaces on the basis of Data Flow:

1. Inbound Interface : An inbound interface in the system is the gateway from where data comes into an Oracle module from other oracle modules or from non-oracle systems. The data in this interface is then processed and included into the oracle system. These interfaces might be standard or custom
2. Outbound Interface : The way oracle system receives data from non-oracle system , similarly the business might also be sending out data out  , for this the system will generate data files which are sent across to other systems. These are outbound interfaces which are again standard of custom.

Interfaces on the basis of standards

1. Standard Interface/Open Interface: These are the standard interfaces provided by oracle  for data import
2. Custom Interface: These are custom programs developed as a part of business requirements for data integration and import

                      

Open Interface: 

Oracle Open Interfaces are the standard interfaces provided by oracle for integrating the oracle system to other oracle modules and to interface it with other data sources. Each of the Open interface will be processing the incoming data.

There are some steps in which the data will be processed

1. Source System Creates Data :  The source system will create the data , this might be in the form of flat file.
2. Data Load: The flat file having the data needs to be loaded into the open interface tables
3. Data Processing : The business might have some custom program to pre-process the incoming data and see if all the mandatory fields are there or not
4. Call the Open interface program : Once the pre-processing if required is done , the open interface program is called , this program will perform all the validations which have been defined by oracle , derive the related fields required on the basis of the data provided process the data and load into the oracle base tables or error them out
5. Correction: If the record is errored out during the validation by the Open interface, the data needs to be corrected and re-processed

Custom Interface

For business requirements the system might have custom interfaces which take in data from third party systems and process them as per the business customization's

There are some steps in which the data will be processed

1. Source System Creates Data :  The source system will create the data , this might be in the form of flat file.
2. Data Load: The flat file having the data needs to be loaded into the open interface tables
3. Data Processing : The business might have some custom program to pre-process the incoming data and see if all the mandatory fields are there or not
4. Call the interface program : Once the pre-processing if required is done , the interface program is called , this program will perform all the validations which have been defined by oracle , derive the related fields required on the basis of the data provided process the data and load into the oracle base tables or error them out
5. Correction: If the record is errored out during the validation by the Open interface, the data needs to be corrected and re-processed

Overview of Oracle General Ledger

Oracle General ledger is the core module concerned with the financial reporting of information .All the financial information will mostly end up in General Ledger from where final reporting will be done. General ledger module will Record , Analyze , Manipulate and Report the information.

General Ledger will receive information from all the oracle modules

1. Order Management
2. Purchasing
3. Receivables
4. Payables
5. HRMS
6. Projects
7. CRM etc.

All the information is recorded , analyzed and then reported by General Ledger

The Basic Accounting cycle of General Ledger is 

1. Open the period
2. Receive data
3. Create Journals/Reverse Journals
4. Post Journals
5. Review
6. Revalue
7. Consolidate
8. Reconcile
9. Run Reports
10. Close Accounting Period

General Ledger Module will record all the financial information in the form of journal entries

It will create journal entries for all the information coming in an then post it to the final set of books.

General Ledger System can receive information from Oracle Systems and Non-Oracle Systems.

Form Oracle Systems:

Data Processing Cycle

From Non Oracle Systems

1. Data Will Be uploaded via Spreadsheets
2. Journals Will be created
3. Journals will be posted to General Ledger

AR Invoice Correction

When Invoices are created in the system there might be scenarios that invoice get created with incorrect amount , thus the business will need to take corrective action to correct the invoices.

There are various circumstances under which invoice correction  might be required

1. Under Invoicing
2. Over Invoicing
3. Invalid Transactions

Under Invoicing: Under Invoicing as the name suggests refers to the phenomenon in which an invoice is created for an amount less than the actual amount. In such cases since the customer is underbilled we need to have a correction mechanism to bill the customer accurately , the different mechanisms used to correct under invoicing might be

• Update the Invoice
• Create a Debit Memo

Over Invoicing:  Over invoicing is the opposite of under invoicing , here we have billed the customer more that the required amount, to correct this phenomenon the strategy that may be adopted is

• Updating the invoice
• Create a credit memo
• Refunds
• On Account Credit

Invalid Transactions:  There might be a situation in which an invoice is created for a wrong customer or with incorrect information , these type of transactions can be corrected by

• Voiding the invoice
• Deleting the invoice

Accounting Rules for AR Invoice

Oracle Allows to create invoices with certain rules, the rules applied to the invoice are classified as

Accounting Rules: Accounting rules determine when to recognize the revenue for the invoice.Each invoice line can have a different accounting rule

Invoice Rules: Invoice rule determines when to bill the customer

Accounting Rules:

1. Fixed Schedule: Recognize revenues over a specific number of periods. Revenue can be spread evenly or a percentage can be specified for each period
2. Variable Schedule: Revenue is recognized by percentage for the first period. The remaining revenues is evenly spread across the number of periods the user specified when entering the AR transaction
3. Daily Revenue Rate: 
1. All Periods: Calculates the revenue distributions across all accounting periods including both full and partial periods
2. Partial Periods: Used to accurately calculate revenue only for partial periods

Oracle Receivables Cycle

Oracle Receivable cycle is concerned with the management of  all the receivables of the business. This module keeps a track of all the business receivables and all the receipts in the business.


The basic operations performed in Oracle receivables can be summarized as below

1. Create AR Transactions
2. Check for Validity of AR transactions
3. Complete the AR Transactions
4. Print the Completed AR Invoices
5. Create Receipts 
6. Apply the receipts to AR Transactions
7. Transfer the details to General Ledger
8. For the Invalid Invoices , void or delete them

• Create AR Transactions: This step is concerned with the creation of AR transactions in the system. The AR Invoices can either be created manually form the AR transactions screen or can be created by the Auto Invoice Program by importing the data from the AR Interface. When the AR Invoice is created we have the invoice header , invoice lines are created and customer details are entered.
• AR transactions Validity: Once the transaction is created the user checks for the validity of the AR transaction , are all the details on the invoice correct.
• Complete the AR Transaction: Once the AR Invoice is created the Invoice needs to be completed. When the Invoice is completed Oracle will perform a set of validations too to check for the validity of the AR transaction.

1. Check the invoice has atleast one line
2. GL Date must be in an open or future GL period
3. Sum of Distributions must be equal to the header amount
4. If Calculate TAX is enabled tax must be calculated for each line
5. If freight is entered it must reflect to the freight account  

         Once the invoice is completed the below activities can be performed

1. Payment schedules are created
2. Invoice can be transferred to General Ledger
3. Transaction can be printed          

•  Print the Completed AR Invoices : The business can print the AR Invoice if required for  internal tracking or sending to customer
• Create Receipts: On receiving a payment from the customer a receipt is create in the system to keep a record of it
• Apply Receipts: Once the Receipts are created in the system we need to apply the receipt under an invoice to mark the invoice as paid. else even if receipts are received but not applied to an invoice the Invoice will always remain in an open status and will always be reflected as unreceived.
• Transfer the details to General Ledger: In order to generated all the financial statements all information must be transferred to General Ledger .
• Void / Delete transactions: If any invoice is found to be incorrect by the user , they are either voided or marked for deletion from the system

Auto Invoice Import Process

Auto Invoice Import is a standard utility provided by Oracle to create Receivable Invoices. The utility is used to create invoices,credit memos and debit memos in Oracle.

Invoices can be created from:

1. Order Management
2. Projects 
3. Loans
4. Services 
5. Lease Management
6. Property Manager
7. Non Oracle Systems

When Invoices need to be created the Invoice information is entered into the AR Interface tables:

1. RA_INTERFACE_LINES_ALL
2. RA_INTERFACE_DISTRIBUTIONS_ALL
3. RA_INTERFACE_SALESCREDITS_ALL

The Autoinvoice import program will pick up the eligible records from the table and invoices are created for the eligible records

When the program is run to create the invoices the program will

1. Order and group the lines --> Specified in the frouping rules setup of AR
2. Contingencies are assigned
3. Legal entity is grouped
4. Tax Engine is called to caluclate the tax
5. Sales Credits is calculatted
6. GL Data is determined
7. Accounts are assigned
8. Transactions are batched
9. Valid records are successfully processed to create invoices

When creating invoices via the autoinvoice import program some records might error out in processing due to errors in data or during processing. All the records that error out in processed need to be corrected and re-processed again

Oracle has provided standard screens to see all the records erroring out in interface lines , via the Interface Errors screen. This screen shows all the errored records , the user needs to correct these data and re-process them

For the records which are successfully processed Invoices are created in the system which can be seen from the transactions window and from the backend via the tables:

1. ra_customer_trx_all
2. ra_customer_trx_lines_all
3. ra_Salescredits_all