oracleapps world

Wednesday, 5 August 2015

Payables Open Interface Import

Required and optional columns of Payables open interface import of PO matched invoices (R12)

In this post, I tried to explain the required and optional columns in the payables invoice import tables for importing PO Matched Invoices.

TABLE: AP_INVOICE_INTERFACE

Required Columns

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
INVOICE_NUM	Must be unique to the supplier
PO_NUMBER	An approved, not cancelled, not closed or final closed PO
VENDOR_ID or VENDOR_NUM or VENDOR_NAME	An active vendor. Validated against PO_VENDORS
VENDOR_SITE_ID or VENDOR_SITE_CODE	An active pay site. Validated against PO_VENDOR_SITES
INVOICE_AMOUNT	Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type
ORG_ID	Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
SOURCE	Must be in SELECT lookup_code FROM ap_lookup_codes WHERE lookup_type = 'SOURCE';

Optional Columns

Column Name	Validation
INVOICE_DATE	Defaulted to SYSDATE
INVOICE_TYPE_LOOKUP_CODE	Defaulted to 'STANDARD'. It can be 'STANDARD' or 'CREDIT'
INVOICE_CURRENCY_CODE	Defaulted from PO_VENDOR_SITES. INVOICE_CURRENCY_CODE
EXCHANGE_RATE_TYPE	Defaulted from AP_SYSTEM_PARAMETERS. DEFAULT_EXCHANGE_RATE_TYPE
TERMS_ID or TERMS_NAME	Defaulted from PO_VENDOR_SITES.TERMS_ID
DOC_CATEGORY_CODE	Only populated if using automatic voucher number
PAYMENT_METHOD_LOOKUP_CODE	Defaulted from PO_VENDOR_SITES .PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE	Defaulted from PO_VENDOR_SITES. PAY_GROUP_LOOKUP_CODE
ACCTS_PAY_CODE_COMBINATION_ID	Defaulted from PO_VENDOR_SITES. ACCTS_PAY_CODE_COMBINAITON_ID
GROUP_ID	Group identifier. Suggest to use it
STATUS	DO NOT POPULATE IT

Table: AP_INVOICE_LINES_INTERFACE
Required Columns

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID	Populated from AP_INVOICE_LINES_INTERFACE_S. NEXTVAL
LINE_NUMBER	A unique number to the invoice
TAX_CODE or TAX_CODE_ID	Validated against AP_TAX_CODES_ALL
LINE_TYPE_LOOKUP_CODE	'ITEM'
AMOUNT	Should be QUANTITY_INVOICED * UNIT_PRICE
If MATCH_OPTION is 'P', then populate
RELEASE_NUM or PO_RELEASE_ID	For Blanket Release only, validated against PO_RELEASES_ALL
PO_NUMBER or PO_HEADER_ID	Validated against PO_HEADER_ALL
PO_LINE_NUMBER or PO_LINE_ID	Validated against PO_LINES_ALL
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID	Validated against PO_LINE_LOCATIONS_ALL
If MATCH_OPTION is 'R', then populate
RECEIPT_NUMBER	Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM
RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID	Validated against RCV_TRANSACTIONS

Optional Columns

Column Name	Validation
QUANTITY_INVOICED	Populated if different from PO shipment
UNIT_PRICE	Populated if different from PO shipment
MATCH_OPTION	'P' or 'R' or Defaulted from PO_VENDOR_SITES.MATCH_OPTION
ACCOUNTING_DATE	Defaulted from INVOICE_DATE or SYSDATE
FINAL_MATCH_FLAG	Populated 'Y' if it is final matching
INVENTORY_ITEM_ID	Validated against PO_LINES.INVENTORY_ITEM_ID
INVENTORY_DESCRIPTION	Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION
SHIP_TO_LOCATION_CODE	Populated if different from PO shipment
PRICE_CORRECTION_FLAG	Populated 'Y' if it is price correction

=======

Base Tables:

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

(&)

Payment Tables

PAYABLES OPEN INTERFACE IMPORT PARAMETERS

In this below post, I had published information about the parameters related to “Payables Open Interface Import” Program. Info from R12.1.1 instance.

Mandatory Parameters:

1. Source: It can be any of the below list

Ø Invoice Gateway - Invoice records you entered in the Invoice Gateway window

Ø E-Commerce Gateway- Supplier invoices transferred through EDI

Ø Credit Card- Procurement cards transactions from the credit card issuer

Ø Oracle Property Manager- Lease invoices from Oracle Property Manager.

Ø Oracle Assets- Lease payments from Oracle Assets.

Ø User-defined- For invoice loaded from legacy Systems

2. Invoice Batch Name

Ø Use Batch Control Payables Profile option need to be enabled to use this parameter

Ø Payables groups the invoices created from the invoice records you import and creates an invoice batch with the batch name you enter

Ø While Re-importing the error invoices make sure the batch name remains the same

Optional Parameters:

3. Operating Unit: Filtering predicate when null import program process invoice from all operating units.

4. Hold Name: When this parameter is provided with the hold name, then the import program creates the invoices and blocks them with mentioned hold.

5. Hold Reason: Corresponds to Hold Name

6. GL Date: This value is used as a GL date for the invoices whose GL_DATE columns in interface tables are not populated.

7. Purge. Enter Yes if you want Payables to delete from the Payables Open Interface tables all successfully imported invoice records that match the Source and Group ID of this import

8. Summarize Report: Provides a Summarize report when ‘YES’

Basics in Bulk Binding

What is the need for Using Bulk Binding concepts in programs?
To answer this question, we must first understand how a data is fetched in a PL/SQL program. Every program which fetches data uses SQL at the back end, hence the need for a SQL engine to fetch data’s. Whenever a PL/SQL block calls a SQL statement, the control passes to SQL engine, this in-turn fetches the data from the database and transfers the data and the control back to PL/SQL engine for further processing.

If we code a program which uses a lot of data fetching and loops, then it causes a lot of Context Switching between PL/SQL and SQL engines, thereby decreasing the performance considerably. It is here where Bulk Binding comes in handy. When we use Bulk Binding concepts in our programs, it eliminates the multiple context switching between the two engines and considerably increases the performance of the program.

How Bulk Binding works?

As discussed earlier, Bulk Binding reduces the context switching between PL/SQL and SQL engines in a very simple manner. Here is how it achieves this:
Lets take a normal cursor which has a set of rows and we would like to manipulate the data’s fetched from this cursor in our program. This obviously needs a lot of context switching when we use a Cursor For Loop, but when we incorporate Bulk binding in the program, what it does is, it will fetch all the data’s from the database and store it in a temporary table in PL/SQL (The temporary table can be a VARRAY, PL/SQL TABLE, RECORD TYPE, %ROWTYPE, CURSOR TYPE). Once it is stored in these temporary tables, then we just need to use these tables to manipulate further in our programs as the data is now in PL/SQL engine. This eliminates a lot of context switching when a large data is concerned.

What are the types of Bulk Binding?
With SELECT or FETCH statements
BULK COLLECT INTO clause

In-Bind binding. (INSERT or UPDATE)
FORALL Clause
Out-Bind binding.
RETURNING clause
What are the attributes of Bulk Binding?

The main attribute of bulk Binding is SQL%BULK_ROWCOUNT(i)This gives the number of rows affected when using a Bulk Binding concept.
The other attributes are associated with error handling. They are
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
SQL%BULK_EXCEPTIONS(i).ERROR_CODE
What are the typical usage of Bulk Binding?
There are two important places where we can use Bulk Binding and they are discussed as below:

In a SELECT or FETCH statement. Whenever we need to fetch data’s from the database we need to use the BULK COLLECT INTO clause, hence this comes in handy in the SELECT and the FETCH statements.

While manipulating, DELETING, UPDATING or INSERTING. Whenever there is a data manipulation in bulk amounts, we can use the FORALLoption. It performs all the manipulation at one go.

What are the limitations in Bulk Binding?

Bulk binding also has its share of limitations and some of them are

a) FORALL clause must precede only a DML statement.
b) Cannot use the DBMS_OUTPUT statement after FORALL clause.

Is there a way to limit the data that is inserted using the Bulk Binding concept?
Yes, there is a way in which you can restrict the number of rows to be fetched in a BULK COLLECT. What you can do is, you can specify the LIMIT clause followed by any number. BULK COLLECT will fetch only those many records which satisfies the number following the LIMIT Clause.

Example: FETCH cursor_name BULK COLLECT INTO collections_name LIMIT x;

Here x stands for a numeric value restricting BULK COLLECT records.

What is the use of SAVE EXCPTIONS in Bulk Binding?
Whenever a PL/SQL program encounters the keyword SAVE EXCEPTIONS after a FORALL clause, it saves the exception in the SQL%BULK_EXCEPTIONS collection and does not interrupt the operation.
To explain in detail, lets say we are manipulating a thousand records, FORALL clause encounters an error in the 500th record, if SAVE EXCEPTIONS clause is not specified, then the operation gets interrupted causing a ROLLBACK of the entire operation and when SAVE EXCEPTIONS is specified then the exception which gets encountered midway through the operation is stored in the collection of errors called as BULK_EXCEPTION and the operation continues without interruption. Later we can view the exceptions caused in the operation by querying the error collection using SQL%BULK_EXCEPTIONS(i).ERROR_INDEX and SQL%BULK_EXCEPTIONS(i).ERROR_CODE.

What are the possible errors that may occur while using BULK BINDING?

The only error I can foresee getting with FORALL is this one:
ORA-22160: Element at index does not exist
And you will only get that if you are somehow binding to an index that does not exist.You may also get complaints if you use the index in an expression, which is not supported:
PLS-00430: FORALL iteration variable i is not allowed in this context
Now, lets see some of the sample programs using BULK BINDING concepts.

Sample BULK COLLECT program:
DECLARE
TYPE Cust_tab IS TABLE OF Customers_Active%ROWTYPE;
Custs Cust_tab;
BEGIN
SELECT Customer_Account_Id, Effective_Date, Expired_Date
BULK COLLECT INTO Custs
FROM Customers_Active
WHERE Effective_date BETWEEN
TO_DATE(’01-JAN-2004’ , ‘DD-MON-RRRR’) AND TRUNC(SYSDATE);
END;

Sample FORALL Program:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10,30,70);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal + 100 WHERE deptno = depts(i);
END;

So this should probably give you some insight on the basic concepts of BULK BINDING.
Feel free to post your comments and queries in the comments section.

REF Cursors

What are Ref Cursors?
Ref cursor is a Data type. A variable created using this data type is usually called as a Cursor Variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.)

It is of two types:
Strong: With a Return Type
Weak: Without a Return Type

Advantages of Ref Cursors:
* Uses the same memory area for all the active sets created by different queries.
* Can be used to pass result sets between sub programs.
* Ability to change the query based on a certain criterion.

Difference between Static and Ref Cursors:

Static Cursors cannot be passed to sub programs whereas ref cursors can be passed between sub programs.
Static Cursors as the name suggests are Static and decided at the design time itself whereas Ref Cursors are changed during the execution time as per certain criterion.

Important Note: Ensure that any open cursor is closed before attempting to open the next cursor.

Examples of Ref Cursors:
A simple Ref Cursor:

declare type ref_cursor is REF CURSOR;
var_emp ref_cursor;
var emp.ename%type;
begin
open var_emp for select ename from emp;
loop
fetch var_emp into var;
exit when var_emp%notfound;
dbms_output.put_line(var);
end loop;
close var_emp;
end;

%ROWTYPE with Ref Cursor:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename ' - ' er.sal);
end loop;
close c_emp;
end;

RECORDS with Ref Cursor:

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record ( name varchar2(20), sal number(6) );
er rec_emp;
begin
open c_emp for select ename,sal from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
close c_emp;
end;
Multiple queries using Ref Cursor:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
( name varchar2(20),
sal number(6) );
er rec_emp;
begin
open c_emp for select ename,sal from emp where deptno = 10;
dbms_output.put_line('Department: 10');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
close c_emp;
open c_emp for select ename,sal from emp where deptno = 20;
dbms_output.put_line('Department: 20');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
close c_emp;
end;

Ref Cursor used as Parameters between Sub Programs:

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record ( name varchar2(20),
sal number(6) );
procedure PrintEmployeeDetails(p_emp r_cursor) is
er rec_emp;
begin
loop
fetch p_emp into er;
exit when p_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
end;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
PrintEmployeeDetails(c_emp);
close c_emp;
end loop;
end;
Thus, these are some of the uses of Ref Cursors. Feel free to post any comments on this article.

Wednesday, 5 August 2015

TABLE: AP_INVOICE_INTERFACE

PAYABLES OPEN INTERFACE IMPORT PARAMETERS

Basics in Bulk Binding

REF Cursors

In how many ways we can link the requisition to purchase order in oracle ebs