OAF MVC Architecture

OAF is a java based application framework to develop web based applications that link to Oracle Applications instance while maintaining all the security features of that apps instance. A framework is a specialized set of related classes designed to make application development easier.  In effect, a framework implements part of an application so developers don’t have to write all of its code from scratch; they can use the framework as the basis for their work and while focusing on the additional code required to implement their specific application requirements.

OA Framework follows Model, View and Controller (MVC) Architecture as described below:

1] Model

Model contains the components which handles data directly from Database. It Includes Business Components for Java (BC4J Objects) which mainly are:

Entity Objects (EO):

Entity Objects are generally based on one table which encapsulate the business rules. These objects are used by OAF page to perform update/insert/delete operations. You can join two EOs using Entity Associations.

View Objects (VO):

These objects contain a SQL query that queries the data from database and present it in the OAF page. VOs can be based on one or many EOs or a SQL query. Two VO can be linked together through a View Link.

Application Module (AM):

It is a container for related BC4J objects and provides the database connection. It also provides Transaction Context (OADBTransaction) or Transaction Management. An AM can have more nested AM contained in it along with other BC4J components. But it is mandatory to have an AM for an OAF page.

2] View

View contains the actual page items on page which user can see. The view in OAF comprises of various page level items like text fields, buttons, regions, links etc. These items are visible on any OAF page. These items can either be tied to VO attribute or having a constant value or populated at run time based through controller logic.

Please note that- View Layer is altogether different than View Object!!!!!

3] Controller

Controller handles all the user actions done on the page. OAF requires a java controller class to be defined for a page/region which handles various page level actions. The important methods in this class are:

A] ProcessRequest

     This request is called when page is rendered. Any logic to be executed during page initialization is kept here.

B] ProcessFormRequest

Any page submit action causes ProcessFormRequest to be executed. The logic put here typically is that which needs to be executed after actions like button click or any other page submit action.

The Controller class is mostly used to put logic for actions on page such as button clicks, navigation to other pages. The two objects that are passed to controller methods are OAPageContext and OAWebBean. OAPageContext provides access to objects like AM class, page parameters, session values, navigation methods. OAWebBean is generally used to get a handle of page items.

In coming posts, I will try to write more details about these components and how they works….so stay tuned!

AR Invoice Interface

AR Invoice Interface

The main three steps for AR Invoice Interface are:

1] Put the data into your staging tables.

2] Calls your package to validate the data and load into AR Interface tables (RA_INTERFACE_LINES_ALL & RA_INTERFACE_DISTRIBUTIONS_ALL).

3] Then submits a concurrent request for AutoInvoice.

If any errors occur it can be found in ra_interface_errors_all table. The concurrent program has 2 stages. First the Master program fires which intern kicks of the Import Program. Once this is completed data is inserted into the following tables.

1) ra_customer_trx_all (Invoice Header Info)

2) ra_customer_trx_lines_all (Invoice Line Level Info)

3) ra_cust_trx_line_gl_dist_all (Accounting Info. One record for each Account Type is inserted into this… ex. Receivable Revenue Tax Freight etc)

4) ar_payment_schedules_all (All Payment related info)

Validations:

Validation are generally done on the below columns.

• Batch_source_name
• Set_of_books_id
• Orig_sys_batch_name
• orig_system_bill_customer_ref
• orig_system_bill_address_ref
• Line_Type
• Currency_Code
• Term_name
• Transaction_type
• Interface_line_attribute1-7
• Account_class
• Accounting Flexfields segments

1- AR Transaction Type Validation: Check if the Transaction type provided in data file is defined in AR transaction types (RA_CUST_TRX_TYPES_ALL)

2- Transaction Batch Source Validation: Check if the source provided in data file is defined in AR transaction Batch source (RA_BATCH_SOURCES_ALL).

3- Invoice Currency Validation: Check if the currency provided in data file is defined in AR Currency (FND_CURRENCIES).

4- Customer Validation: Check if the Bill to Customer Number, Ship to Customer Number, Bill to Custom Location, Ship to Customer Location provided in the data file is defined in AR Customer (RA_CUSTOMERS).

5- Primary Sales Representative Validation: Sales representative number to be hardcode to “-3” for “No Sales Credit.”

6- Term Name: Check if the Term name provided in the data file is defined in Payment terms (RA_TERMS)

7- Inventory Item Validation: Check if the Item provided in data file is defined in Inventory Items (MTL_SYSTEM_ITEMS).

8- Unit of Measurement validation: Check if the UOM provided is defined in MTL_UNITS_OF_MEASURE Table

9- Invoice Tax Code Validation: Check if the Tax Code provided in data file is defined in AR_VAT_TAX_ALL_B Table.

10- Invoice GL Date Validation: Check if the GL Data of provided invoices is in open period.

For MOAC:

You need to add the below columns and need to do validations if your application supports MOAC.

• conversion_type
• conversion_rate
• conversion_date

Sample Code to run Autoinvoice Master Program:

DECLARE
v_phase 	VARCHAR2(100);
v_dev_phase 	VARCHAR2(100);
v_status 	VARCHAR2(100);
v_dev_status 	VARCHAR2(100);
v_message 	VARCHAR2(100);
v_reqid 	NUMBER(15);
v_pid 		BOOLEAN;
v_user_id  	NUMBER(30);
v_batch_source_id NUMBER;
v_order    	NUMBER;
v_org_id   	NUMBER;
v_resp_id  	number;
v_resp_appl_id 	number;
v_appl_short_name fnd_application.application_short_name%TYPE;

CURSOR c1 IS
select fcr.responsibility_id
,fr.application_id
from fnd_concurrent_requests fcr
,fnd_responsibility fr
where fcr.request_id = '${4}'
and   fcr.responsibility_id = fr.responsibility_id;

CURSOR c2 IS
select fa.application_short_name
from fnd_concurrent_programs fcp, fnd_application fa
where fcp.concurrent_program_name = v_program_short_name
and fcp.application_id = fa.application_id;

CURSOR c_batch_id IS
SELECT 1, batch_source_id, name
FROM apps.ra_batch_sources_all
WHERE name IN (SELECT distinct a.batch_source_name
FROM xxfin.xxfin_ar_ol_invoices a
WHERE a.batch_source_name like '%DEBIT'
AND filename = '${file1}')
UNION
SELECT 2, batch_source_id, name
FROM apps.ra_batch_sources_all
WHERE name IN (SELECT distinct a.batch_source_name
FROM xxfin.xxfin_ar_ol_invoices a
WHERE a.batch_source_name like '%CREDIT'
AND filename = '${file1}')
order by 1;

BEGIN
open c1;
fetch c1 into v_resp_id,v_resp_appl_id;
close c1;

open c2;
fetch c2 into v_appl_short_name;
close c2;

FOR v_batch_data IN c_batch_id LOOP

fnd_global.apps_initialize('${FCP_USERID}',v_resp_id,v_resp_appl_id);

v_reqid := fnd_request.submit_request('AR',
'RAXMTR',
NULL,
to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),
FALSE,
'1',
-99,
v_batch_data.batch_source_id,
v_batch_data.name,
to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),
NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,
NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,
'Y',
NULL);
commit;
v_pid := fnd_concurrent.wait_for_request(v_reqid,
3,
0,
v_phase,
v_status,
v_dev_phase,
v_dev_status,
v_message);
END LOOP;
END;

FAQs in Account Payable

FAQs in Account Payable

 

How many types of Invoices we can create in Oracle Payables?
A. Standard
B. Debit Memo
C. Credit Memo
D. Pre-Payment
E. Expense Report
F. Withholding Tax Invoice
G. Miscellaneous Invoice

Describe the 4-Way Matching?
2 – Way Matching: The Invoice Amount Should match with PO, (Invoice with PO)
3 – Way Matching: The Receipts Should match with Invoice, (Invoice, PO with Receipts)
4 – Way Match: The Goods Should match with Inspection/Accepted (Invoice, PO, Inspections & Receipts)

2-Way (Invoice to Purchase Order)
Quantity billed vs. quantity ordered on shipment line
Invoice unit price vs. purchase order line unit price

3-Way (Invoice to Purchase Order and Receipt)
2-Way match criteria AND
Quantity billed vs. quantity received
Item = Receipt Required

4-Way (Invoice to Purchase Order and Receipt and Inspection)
3-Way match criteria AND
Quantity billed vs. quantity accepted
Note: Quantity accepted = (Quantity received – quantity rejected)
Item = Inspection Required

How many Holds we have?
System Holds: Tax, Quantity Match, Po amount with Invoice Amount
Manual Holds: Invoice Limit, Hold on Invoice

Can you Release Manual Holds? If Yes, How?
Yes. Holds – Release Holds

How many ways you can pay the Invoice Amount?
Apply in Full
Schedule Payments
Installments

What is the difference between Debit and Credit Memo?
Debit Memo will raise the Customer
Credit Memo will raise the Vendor

Can you create a Duplicate Vendor ?
No

Vendor Number Should be Generate Automatic or Manually?
Both, Manual And Automatic

How many key flexfields are there in Payables?
No key flexfields in PO,AP

What are Payment Terms?
Payables uses payment terms to automatically calculate due dates, discount dates, and discount amounts for each invoice you enter. Payment terms will default from the supplier site. If you need to change the payment terms and the terms you want to use are not on the list of values, you can define additional terms in the Payment Terms window.

What are the Payment Methods available?
• Check – You can pay with a manual payment, a Quick payment, or in a payment batch.
• Clearing – Used for recording invoice payments to internal suppliers.
• Electronic – You generate an electronic payment file that you deliver to your bank to create payments. Use Electronic if the invoice will be paid using EFT or EDI.
• Wire – Used to manually record a wire transfer of funds between your bank and your supplier’s bank.

What are the line types of an Invoice?
Item: Item lines capture the details of the goods and services billed on your invoice.
Freight: Freight lines capture the details of your freight charges.
Miscellaneous: Miscellaneous lines capture the details of other charges on your invoices such as installation or service.
Tax: Payables integrates with Oracle E-Business Tax to automatically determine and calculate the applicable tax lines for your invoices.

What are the Invoice Distribution Types?
Item
Freight
Miscellaneous
Tax
Withholding
Prepayment

What are Holds in AP?
Holds can be placed by the system and some holds can be placed manually. Holds prevent payment and, sometimes, accounting of an invoice. Payables applies holds to invoices that fail the Invoice Validation process. Release holds from invoices to allow payment and accounting entry creation for the invoices.

What are the Interface Tables in AP?
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_INTERFACE_CONTROLS
————————————–
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIER_INT_REJECTIONS

What is the API to cancel single AP Invoice?
AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE

What is the API to find invoice status?
AP_INVOICES_PKG.GET_APPROVAL_STATUS

Give some of the Oracle Payables interface?
a) Credit Card Transaction Interface
b) Invoice Import Interface
c) Payables Open Interface
d) Purchase Order Matching

What are the key tables where supplier bank information are stored?
IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL

What is meant by with-holding tax invoice?
A separate tax invoice generated for withholding tax amount. Later this invoice will be knocked of when making the payment made to the authority.

What are all the Modules Interacting with AP?
Cash Management
Oracle iExpenses
General Ledger
Oracle Assets
Subledger Accounting (R12)
HRMS
Project Accounting
Purchasing/iprocurement
Global Accounting Engine (11i)

What are the mandatory setups in AP?
1- Financial Options
2- Define Suppliers
3- Define Payment Terms
4- Define Payment Methods
5- Define Banks and Banks Accounts And Banks Accounts Documents
6- Open AP Accounts Periods

What is pay date basis?
The Pay Date Basis for a supplier determines the pay date for a supplier’s invoices.
• Due
• Discount

What is the difference between quick payment and manual payment?

Quick Payment: It allows you to make a single payment against one or more invoices at a time to one supplier through payables.

Manual Payment: This is the process of entering the check details which has been paid manually in some emergency requirements into the payment form and selecting the invoices of the concerned supplier and check whether the total of the invoices and the paid amount at the header are same and save.

What is terms date basis?

Terms Date Basis is to calculate due date.

Due date is calculated 4way. Eg: payment term is 30days

• Due date = Sysdate + 30days
• Due date = Invoice date + 30days
• Due date = Goods Receive Date + 30days
• Due date = Invoice Received date + 30days

How you will transfer payables to general ledger?
When the payable accounting entries are created, then run the program called ‘Payables Transfer to GL’ Program, which sends the invoice entries and payable entries to GL interface. Then submit a request called Journal import to import journal entries to GL.

What are Aging Periods?
Aging periods are nothing but the periods that we setup to control and maintain the supplier outstanding bill towards the invoice. From this we can able to study the due date of the supplier form the generation of invoice.

What is a “Mixed” Invoice and how do I enter one?
Mixed Invoices are invoices or credit/debit memos for which you can perform both positive and negative matching to purchase orders and to other invoices.
For example, you can enter an invoice for -$100 with Invoice Type Mixed. You can match to an invoice for $-200, and match to a purchase order for $100.
To enter a Mixed invoice:
1. Enter the invoice or credit/debit memo in the Invoices Summary, and enter Mixed as the invoice Type. You can enter either a positive or negative invoice amount.
2. Match to purchase orders, and/or invoices.

GL Tables

GL Tables

General Ledger tables can be grossly classified into following 5 categories. Here are few important tables in each category.

Ledgers Tables:

GL_LEDGERS: Stores information about the ledgers defined in the Accounting Setup Manager and the ledger sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description, ledger currency, calendar, period type, chart of accounts, and other information.

GL_CODE_COMBINATIONS: Stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application.

Period Tables:

GL_PERIODS: Stores information about the accounting periods you define using the Accounting Calendar form.

GL_PERIOD_SETS: Stores the calendars you define using the Accounting Calendar form.

GL_PERIOD_TYPES: Stores the period types you define using the Period Types form. Each row includes the period type name, the number of periods per fiscal year, and other information.

Journal Tables:

GL_JE_BATCHES: Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information.

GL_JE_HEADERS: Stores journal entries. There is a one-to-many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry.

GL_JE_LINES: Stores the journal entry lines that you enter in the Enter Journals form. There is a one-to-many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line.

GL_JE_SOURCES: Stores journal entry source names and descriptions. Each journal entry in your Oracle General Ledger application is assigned a source name to indicate how it was created. This table corresponds to the Journal Sources form.

GL_JE_CATEGORIES: Stores journal entry categories. Each row includes the category name and description.

Conversion and consolidation tables:

GL_CONSOLIDATION: Stores information about your consolidation mappings. Each row includes a mapping’s ID, name, description, and other information. This table corresponds to the first window of the Consolidation Mappings form. You need one row for each consolidation mapping you define.

GL_CONSOLIDATION_ACCOUNTS: Stores the account ranges that you enter when you consolidate balances using the Transfer Consolidation Data form. This table corresponds to the Account Ranges window of the Transfer Consolidation Data form.

GL_DAILY_RATES: Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type.

GL_DAILY_BALANCES: Stores daily aggregate balances for detail and summary balance sheet accounts in sets of books with average balances enabled.

Budgeting tables:

GL_BUDGET_TYPES: Stores information about budget types. Oracle General Ledger supports only one budget type, ‘STANDARD’. Therefore, this table always contains only one row.

GL_BUDGET_ASSIGNMENTS: Stores the accounts that are assigned to each budget organization. Each row includes the currency assigned to the account and the entry code for the account. The entry code is either ‘E’ for entered or ‘C’ for calculated. This table corresponds to the Account Assignments window of the Define Budget Organization form.

GL_BUDGET_INTERIM: It is used internally by Oracle General Ledger applications to post budget balances to the GL_BALANCES table. Rows are added to this table whenever you run the budget posting program. The budget posting program updates the appropriate budget balances in GL_BALANCES based on the rows in this table, and then deletes the rows in this table that it used.

Interface Tables:

GL_INTERFACE: It is used to import journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals window to create journal batches.

GL_INTERFACE_CONTROL: It is used to control Journal Import execution. Whenever you start Journal Import from the Import Journals form, a row is inserted into this table for each source and group id that you specified. When Journal Import completes, it deletes these rows from the table.

GL_BUDGET_INTERFACE: It is used to upload budget data into your Oracle General Ledger application from a spreadsheet program or other external source. Each row includes one fiscal year’s worth of budget amounts for an account.

What’s New in R12 Financials?

What’s New in R12 Financials?

1] Ledgers and Ledger Sets:

The ledger is a new fundamental concept in Release 12.  The ledger replaces the 11i concept of a set of books.  It represents an accounting representation for one or more legal entities or for a business need such as consolidation or management reporting. 

11i & Prior = Sets of Books (3 C’s)

• Chart of Accounts
• Accounting Calendar
• Currency

R12 = Ledgers (4 C’s)

• Chart of accounts
• Ledger currency
• Accounting calendar
• Accounting method – new 4th

While a set of books is defined by 3 C’s, chart of accounts, functional currency, and accounting calendar, the ledger is defined by a 4th C: the accounting method.  This 4th C allows you to assign and manage a specific accounting method for each ledger.  Therefore, when a legal entity is subject to multiple reporting requirements, separate ledgers can be used to record the accounting information.

Primary Ledger:

• The main “Activity” Ledger
• Usually in the local currency
• For Operational reporting

Secondary Ledger:

• Differs from Primary Ledger by Chart of Account, Calendar, and/or Accounting Method
• For Statutory, Tax or Consolidated reporting

Reporting Currency Ledger:

• Differs from Primary Ledger by Currency ONLY
• Just a translation of the Primary Ledger – no rules required
• For Consolidated reporting

LEDGER SETS:

• Grouping of ledgers with the same chart of accounts and calendar/period type combination
• Essentially treats multiple ledgers as one

2] Subledger Accounting:

You can consider SLA as a bridge or an Intermediate platform that talks to Subledger products (these are other applications or modules) and the General ledger. All Accounting entries for your modules (like AP, AR, Projects, Inventory, etc) are treated as Sub-Ledgers and they first sent to the SLA engine. The SLA applies its rules (some or these rules are pre-configured and also you can configure as many rules as you want) and then sends the necessary journal entries to the General ledger.

In a nutshell, the following services are provided by Oracle SLA

• Rule based Generation and  storing of accounting entries
• Storing subledger balances
• Subledger or SLA accounting entries
• Subledger reporting (some examples could be Open Account Balances Listing and Subledger Journal Reports, etc )

3] Multi-Org Access Control (MOAC):

‘Multi-Org Access Control’ popularly known as ‘MOAC’ in short form is an enhanced feature in Release 12. MOAC will enable users to access secured data in one or more Operating Units from a single responsibility.

End-Users can access/transact data within several operating units based on Security Profile attached to a responsibility. i.e. End-Users can access/transact data on multiple Operating units by accessing one operating unit at a time without changing a responsibility. This Provides flexibility for end-users to work conveniently with multiple Operating Units in shared service Environments with single responsibility.

4] Advanced Global Intercompany System (AGIS):

Advanced Global Intercompany System (AGIS) enables you to create, settle and reconcile intercompany transactions. Intercompany transactions are transactions that occur between two related legal entities in an enterprise or between groups in the same legal entity. The balances of the intercompany transactions must be eliminated or adjusted when preparing the consolidated financial statement, or it might result in overstated financial results, which in turn might lead to legal repercussions against the enterprise. Intercompany transactions can be identified and eliminated by the use of specific accounts to book these transactions.

5] Tax Engine:

It Centrally manage tax transactions across entire E-Business Suite.

• Single Repository of transactions for global business insight
• Centralized rules applied to transactions to manage globally and reduce risk
• Automation of tax processes on transactions to improve operational efficiency
• Improved Reporting
• Effective Date Setup
• Extensible architecture that supports additions, e.g. Self-assessed Use Tax

6] Bank Model:

Because of changing business need and high demand of global partners, the R12 release witness great changes ever into the bank model. Bank account is now associated with Legal entity rather than Operating Unit and hence single bank account serves multiple Operating Units. This makes bank with strong capability to pay across operating units. More over banks accounts can be shared by applications and can be designed for use by Payables, Receivables and Payroll.

The new bank account model allow you to define and keep track of all bank accounts in the e-Business Suite in one place and explicitly grant account access to multiple operating units/functions and users. The new model reduces the number of access points to manage bank accounts by providing a centralized user interface where all internal bank accounts can be set up.

Supplier Conversion in R12

Supplier Conversion in R12 

Vendor conversion program will load the Supplier Master, Sites and Contacts data from data files to the staging tables, validate the data and then load the data into Interface tables, finally Validated data will import into Oracle Supplier Standard Tables by using Oracle Standard Supplier Import Programs.

Pre-requisites setup’s are: Payment terms, Pay Groups, CCID, Supplier classifications, Bank Accounts , Employees (if employees have to set up as vendors).

The Interface Tables are:

• AP_SUPPLIERS_INT
• AP_SUPPLIER_SITES_INT
• AP_SUP_SITE_CONTACT_INT

AP_SUPPLIERS_INT:

This is the open interface table for AP Suppliers. It holds Supplier information which is loaded by the user for import. The columns in the table map to corresponding columns in the PO_VENDORS table. The Oracle Payables application uses this information to create a new Supplier record when the Supplier Open Interface Import program is submitted. Each row in the table will be identified by a unique identifier, the VENDOR_INTERFACE_ID.

Mandatory Columns:

• VENDOR_INTERFACE_ID (ap_suppliers_int_s.NEXTVAL)- Supplier interface record unique identifier
• VENDOR_NAME – Supplier name

Other important columns:

• SEGMENT1 – Supplier Number
• VENDOR_TYPE_LOOKUP_CODE – Supplier type
• SHIP_TO_LOCATION_CODE – Default ship-to-location name
• BILL_TO_LOCATION_CODE – Default bill-to-location name
• TERMS_NAME – Payment terms name
• TAX_VERIFICATION_DATE – Tax verification date(1099)
• VAT_REGISTRATION_NUM – Tax registration number
• ATTRIBUTE1 -15 – Descriptive Flexfield Segments
• PAY_GROUP_LOOKUP_CODE – Payment group type
• INVOICE_CURRENCY_CODE – Default currency unique identifier
• PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
• NUM_1099 – Tax identification number (1099)
• VAT_CODE – Default invoice tax code
• HOLD_FLAG – Indicates whether or not the supplier is on purchasing hold
• SUMMARY_FLAG – Key flexfield summary flag
• ENABLED_FLAG – Key flexfield enable flag
• EMPLOYEE_ID – Employee unique identifier if supplier is an employee

AP_SUPPLIER_SITES_INT:

This is the open interface table for AP Supplier Sites. It holds Supplier Site information which is loaded by the user for import. The columns in the table map to corresponding columns in PO_VENDOR_SITES_ALL table. The Oracle Payables application uses this information to create a new Supplier Site record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier using the VENDOR_ID column.

Mandatory Columns:

• VENDOR_SITE_INTERFACE_ID (ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique identifier
• VENDOR_SITE_CODE – Supplier Site name

Other important columns:

• ADDRESS_LINE1 – First line of supplier address
• ADDRESS_LINE2 – Second line of supplier address
• ADDRESS_LINE3 – Third line of supplier address
• CITY – City name
• STATE – State name or abbreviation
• ZIP – Postal code
• COUNTRY – Country name
• PHONE – Phone number
• FAX – Supplier site facsimile number
• SHIP_TO_LOCATION_CODE – Default ship-to-location name
• BILL_TO_LOCATION_CODE – Default bill-to-location name
• PAYMENT_METHOD_LOOKUP_CODE – Default payment method type
• VAT_CODE – Invoice default tax code
• PAY_GROUP_LOOKUP_CODE – Payment group type
• TERMS_NAME – Payment terms name
• INVOICE_CURRENCY_CODE – Default currency unique identifier
• PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
• EMAIL_ADDRESS – E-mail address of the supplier contact
• PURCHASING_SITE_FLAG – Indicates whether purchasing is allowed from this site
• AUTO_TAX_CALC_FLAG – Level of automatic tax calculation for supplier
• HOLD_ALL_PAYMENTS_FLAG – Indicates if Oracle Payables should place payments for this supplier on hold

AP_SUP_SITE_CONTACT_INT:

This is the open interface table for AP Supplier Site Contacts. It holds Supplier contact data. The columns in the table map to corresponding columns in PO_VENDOR_CONTACTS table. The Oracle Payables application uses this information to create a new Supplier Contact record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier Site using the VENDOR_SITE_CODE  and ORG_ID.

Mandatory Columns:

• VENDOR_INTERFACE_ID – Supplier interface record unique identifier
• VENDOR_CONTACT_INTERFACE_ID (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) – Vendor Contact Interface Identifier
• VENDOR_SITE_CODE – Supplier Site name

Other important columns:

• FIRST_NAME – Contact First name
• LAST_NAME – Contact last name
• AREA_CODE – Area code of contact phone number
• PHONE – Contact phone number
• FIRST_NAME_ALT – Alternate Supplier contact first name
• LAST_NAME_ALT – Alternate Supplier contact last name
• EMAIL_ADDRESS – Email address for the Supplier Site contact
• FAX – Facsimile number for the Supplier Site contact
• VENDOR_ID – Supplier unique identifier

Validations:

• Vendor Number (Check for duplicate records in ap_suppliers table)
• Vendor Name (Check for duplicate records in staging as well as in ap_suppliers table)
• Terms Name (Check for proper record in ap_terms_tl table)
• Pay Group (Check for proper record in fnd_lookup_values_vl table where lookup_type = ‘PAY GROUP’)
• Employee Id (Check for proper employee record in per_all_people_f table)
• Vendor Type (Check for proper record in po_lookup_codes table where lookup_type = ‘VENDOR TYPE’)
• Vendor Site Code (Check for duplicate records in ap_supplier_sites_all table)
• Country Code ( Check for proper country code in fnd_territories_vl table)
• Payment Method (Check for proper payment method in iby_payment_methods_vl table)

Interface programs:

1. Supplier Open Interface Import
2. Supplier Sites Open Interface Import
3. Supplier Site Contacts Open Interface Import

The data inserted via these interfaces are automatically populated into TCA tables.

Note: AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.

Alerts in Oracle Application

Alerts in Oracle Application

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

Event Alert:

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.

• A SQL Select statement that retrieves specific database information as a result of the database event.

• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

Periodic Alert:

A periodic alert, on the other hand, checks the database for information according to a schedule you define. In a periodic alert specify the following:

• A SQL Select statement that retrieves specific database information.

• The frequency that you want the periodic alert to run the SQL statement.

• Actions that  Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. We include all the actions we want Oracle Alert to perform, in an action set.

Navigation in Oracle Apps to define an alert:

Go to “Alert Manager” Responsibility
Alert >> Define

Transfer Alert from one instance/database to other:

Go to “Alert Manager” Responsibility
Alert >> Define
Go to “Tools” Menu on top
Click on “Transfer Alert”
Enter source and destination fields and click Transfer.

How to define an periodic alert:

1. Go to Alert Manager > Alert > Define.
2. Select the ‘Periodic’ Tab.
3. Enter the name of the application that owns the alert in the Application field.
4. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
5. Check Enabled to enable your periodic alert.
6. Set the frequency for the periodic alert to any of the following:

• On Demand
• On Day of the Month
• On Day of the Week
• Every N Calendar Days
• Every Day
• Every Other Day
• Every N Business Days
• Every Business Day
• Every Other Business Day

Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define. Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.

Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in :THRESHOLD_DAYS.:

SELECT user_name,
password_date,
:THRESHOLD_DAYS
INTO &USER,
&LASTDATE,
&NUMDAYS
FROM fnd_user
WHERE sysdate = NVL(password_date,
sysdate) + :THRESHOLD_DAYS
ORDER BY user_name

Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function.

You can verify the accuracy and effectiveness of your Select statement. Choose Verify to parse your Select statement and display the result in a Note window.

Choose Run to execute the Select statement in one of your application’s Oracle IDs, and display the number of rows returned in a Note window.

Once you are satisfied with the SQL statement, save your work.

Specifying Alert Details:

Once you define an event or periodic alert in the Alerts window, you need to display to the Alert Details window to complete the alert definition. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

Creating Alert Actions:

After you define your alert you need to create the actions you want your alert to perform. There are four types of actions you can create:

• message actions

• concurrent program actions

• operating script actions

• SQL statement script actions

Choose Actions

Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.

Select a level for your action: Detail, Summary, or No Exception.

Choose Action Details to display the Action Details window.

Select the type of action you want to create in the Action Type field

Creating an Event Alert:

Specify the name of the application and the database table that you want Oracle Alert to monitor.

Note: You cannot use a view as the event table for your alert.

Check After Insert and/or After Update if you want to run your event alert when an application user inserts and/or updates a row in the database table.

Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.

Specify a value in the End Date field if you want to disable your alert by a certain date.

Important Alert Tables:

• ALR_ALERTS
• ALR_ACTIONS
• ALR_ACTION_SETS
• ALR_ACTION_SET_INPUTS
• ALR_ACTION_SET_OUTPUTS
• ALR_ACTION_SET_MEMBERS
• ALR_ALERT_CHECKS
• ALR_ALERT_INPUTS
• ALR_ALERT_OUTPUTS
• ALR_ACTION_SET_CHECKS
• ALR_RESPONSE_SETS
• ALR_RESPONSE_ACTIONS
• ALR_VALID_RESONSES

Oracle Alert uses the following internal views:

•  ALR_ALERT_ACTIONS_VIEW
•  ALR_ALERT_HISTORY_VIEW
•  ALR_CHECK_ACTION_HISTORY_VIEW
•  ALR_INSTALLATIONS_VIEW
•  ALR_PERIODIC_ALERTS_VIEW
•  ALR_RESPONSE_ACTIONS_VIEW
•  ALR_SCHEDULED_PROGRAMS
•  ALR_VARIABLES_AND_OUTPUTS

For complete details you can refer Oracle Alert User’s Guide

KEY FLEX FIELDS (KFF)

KEY FLEX FIELDS (KFF)

KFF are used to capture mandatory or Key Business information of the Organization. Each Key Flex Field is having its won base Table.

A key flexfield is a field made up of segments, each of which has both a value and a meaning. You can think of a key flexfield as an “intelligent” field that your business can use to store information represented as codes.

Most organizations use codes to identify general ledger accounts, part numbers, and other business entities. Each segment in the code represents a characteristic of the entity. A combination of segment values, also known as a key flexfield code combination, uniquely describes a business entity stored in a key flexfield.

The organization decides the following four basic information for each key flexfield:

1. How many segments an entity has?
2. What each segment means?
3. What values each segment can have?
4. What each segment value means?

Few Key Flexfields in different oracle modules are:

Module: GL

1. Accounting

Module: HRMS

1. Job
2. Position
3. Grade
4. Personal Analysis   

Module: Inventory

1. Account Aliases
2. Item Catalogs
3. Item Categories
4. Sales Orders
5. Stock Locations
6. System Items

Module: Fixed Assets

1. Asset
2. Category
3. Location

Module: AR

1. Sales Tax Location
2. Territory

Note:

Flexfields consists of Structures > Structures consists of Segments > Segments consists of Value Set >Value Set consists of Parameters.

Main Tables:

FND_ID_FLEXS:

This table captures the information of all the Key FlexFields. The main columns in this table are:

• APPLICATION_ID ‐ Column consists of Application ID
• ID_FLEX_CODE ‐ Column KFF Code (like ‘GL#’, ‘AR#’ etc.)
• ID_FLEX_NAME  –  KFF Name (like ‘Accounting Flexfield’, ‘Category Flexfield’..etc.)
• APPLICATION_TABLE_NAME – Name of combination table (like ‘GL_CODE_COMBINATIONS’ , ‘FA_LOCATIONS’ etc.)

FND_ID_FLEX_STRUCTURES:

This table stores structure information about key Flexfields. Each Structure is uniquely identified by

• APPLICATION_ID – Module Code
• ID_FLEX_CODE  – Code of KFF
• ID_FLEX_NUM – Number of a Structure

FND_ID_FLEX_SEGMENTS:

It captures the information of Segments. Each Segment is Uniquely identified by

• APPLICATION_ID – Module Code
• ID_FLEX_CODE – Key Flexfield code
• ID_FLEX_NUM – Key flexfield structure number
• SEG_NUM – Segment number
• FLEX_VALUE_SET_ID – Flexfield value set identifier

FND_FLEX_VALUE_SETS:

This table captures the information of each Segment’s Value Set. Each Value Set is Uniquely identified by FLEX_VALUE_SET_ID as Foreign Key of FND_ID_FLEX_SEGMENTS Table.

FND_FLEX_VALUES:

This table captures the information each Value codes of a Value Set of a Segment. Each Value Code is uniquely identified by

• FLEX_VALUE_SET_ID
• FLEX_VALUE_ID

FND_FLEX_VALUES_TL:

This table captures the information of each Value Description of a Value Set of a Segment. Each Value Description is uniquely identified by FLEX_VALUE_ID.

Query:

select  FIF.APPLICATION_ID  ,
        FIF.ID_FLEX_CODE    ,
        FIF.ID_FLEX_NAME    ,
        FIF.APPLICATION_TABLE_NAME ,
        FIF.DESCRIPTION     ,
        FIFS.ID_FLEX_NUM    ,
        FIFS.ID_FLEX_STRUCTURE_CODE  ,
        FIFSE.SEGMENT_NAME,
        FIFSE.SEGMENT_NUM,
        FIFSE.FLEX_VALUE_SET_ID
from    FND_ID_FLEXS FIF    ,
        FND_ID_FLEX_STRUCTURES FIFS ,
        FND_ID_FLEX_SEGMENTS FIFSE
where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID
and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE
and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
and     FIF.ID_FLEX_CODE LIKE 'GL#'
and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';

Steps required to register a New Key Flexfield (KFF):

1] Create a KFF Table in Module Specific Schema.

2] Create a Public Synonym in APPS Schema.

3] Register the Table with AOL Module.

Go to Application Developer > Functions > Application > Database > Table

We can also register a Table using Application DBA Data Definitions (AD_DD) Package from the Back End.

procedure register_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

procedure register_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables.

4] Register the KFF with AOL Module.

Go to Application Developer > FlexField > Key > Register

AP Invoice Types

AP Invoice Types

1] Standard

An invoice from a supplier representing an amount due for goods or services purchased. Standard invoices can be either matched to a purchase order or not matched.

2] Credit Memo

A memo from a supplier representing a credit amount toward goods or services.

3] Debit Memo

An invoice you enter to record a credit for a supplier who does not send you a credit memo.

4] Mixed

An invoice type you enter for matching to both purchase orders and invoices. You can enter either a positive or a negative amount for a Mixed invoice type.

5] Prepayment

A type of invoice you enter to pay an advance payment for expenses to a supplier or employee.

6] Expense Report

An invoice representing an amount due to an employee for business-related expenses.

7] Withholding Tax

An invoice you enter to remit taxes withheld to the appropriate tax authority.

8] Interest

If you allow interest invoices, payables will automatically calculate interest for overdue invoices and create interest invoices for selected suppliers.

9] Retainage Release

Invoices created for complex work and advance contract financing.

10] Transportation Invoices

Invoices from freight payment

Query to find accounting flexfield structure

Query to find accounting flexfield structure

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name
, ifs.segment_name
, ifs.application_column_name column_name
, sav1.attribute_value BALANCING
, sav2.attribute_value COST_CENTER
, sav3.attribute_value NATURAL_ACCOUNT
, sav4.attribute_value INTERCOMPANY
, sav5.attribute_value SECONDARY_TRACKING
, sav6.attribute_value GLOBAL
, ffvs.flex_value_set_name
, ffvs.flex_value_set_id
from fnd_id_flex_structures fifs
, fnd_id_flex_structures_tl fifst
, fnd_segment_attribute_values sav1
, fnd_segment_attribute_values sav2
, fnd_segment_attribute_values sav3
, fnd_segment_attribute_values sav4
, fnd_segment_attribute_values sav5
, fnd_segment_attribute_values sav6
, fnd_id_flex_segments ifs
, fnd_flex_value_sets ffvs
, gl_ledgers sob
where 1=1
and fifs.id_flex_code = 'GL#'
and fifs.application_id = fifst.application_id
and fifs.id_flex_code = fifst.id_flex_code
and fifs.id_flex_num = fifst.id_flex_num
and fifs.application_id = ifs.application_id
and fifs.id_flex_code = ifs.id_flex_code
and fifs.id_flex_num = ifs.id_flex_num
and sav1.application_id = ifs.application_id
and sav1.id_flex_code = ifs.id_flex_code
and sav1.id_flex_num = ifs.id_flex_num
and sav1.application_column_name = ifs.application_column_name
and sav2.application_id = ifs.application_id
and sav2.id_flex_code = ifs.id_flex_code
and sav2.id_flex_num = ifs.id_flex_num
and sav2.application_column_name = ifs.application_column_name
and sav3.application_id = ifs.application_id
and sav3.id_flex_code = ifs.id_flex_code
and sav3.id_flex_num = ifs.id_flex_num
and sav3.application_column_name = ifs.application_column_name
and sav4.application_id = ifs.application_id
and sav4.id_flex_code = ifs.id_flex_code
and sav4.id_flex_num = ifs.id_flex_num
and sav4.application_column_name = ifs.application_column_name
and sav5.application_id = ifs.application_id
and sav5.id_flex_code = ifs.id_flex_code
and sav5.id_flex_num = ifs.id_flex_num
and sav5.application_column_name = ifs.application_column_name
and sav6.application_id = ifs.application_id
and sav6.id_flex_code = ifs.id_flex_code
and sav6.id_flex_num = ifs.id_flex_num
and sav6.application_column_name = ifs.application_column_name
and sav1.segment_attribute_type = 'GL_BALANCING'
and sav2.segment_attribute_type = 'FA_COST_CTR'
and sav3.segment_attribute_type = 'GL_ACCOUNT'
and sav4.segment_attribute_type = 'GL_INTERCOMPANY'
and sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
and sav6.segment_attribute_type = 'GL_GLOBAL'
and ifs.id_flex_num = sob.chart_of_accounts_id
and ifs.flex_value_set_id = ffvs.flex_value_set_id
and sob.ledger_id =
nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.ledger_id)
order by sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

Note: The query is tested in R12 Environment.