ERP - About Oracle Payable Invoice

An Invoice is an itemized list of goods shipped or service rendered, with an account of all costs. 

Oracle payables lets us to capture all the attributes of the real life invoice documents we receive from our suppliers. When we enter invoice in Payables , the invoice information is divided between the Invoice header and the Invoice Lines.

Invoice structures:

• Invoice Header: The invoice header defines the common information about the invoice: invoice number and date, supplier information, remittance information, and payment terms. Information specified at the invoice header level defaults down to the line level. We can override the header level information for individual lines, as required.
• Invoice Lines : The invoice lines define the details of the goods and services as well as the tax, freight, and miscellaneous charges invoiced by the supplier. There can be multiple invoice lines for each invoice header. The Lines tab of the Invoice Workbench captures all of the details for the invoice line necessary for accounting, as well as for cross-product integration with other Oracle E-Business Suite applications, such as Assets, Grants Accounting, Inventory, Projects, Purchasing, Property Manager, and Receivables.
• Item Lines: Item lines capture the details of the goods and services billed on your invoice.
• Freight and Miscellaneous Lines: Freight lines capture the details of your freight charges. Freight charges can be allocated to Item lines as required.

  Miscellaneous lines capture the details of other charges on your invoices such as installation or service. Like Freight lines, Miscellaneous lines can be allocated to Item lines.

• Tax lines : Payables integrates with Oracle E-Business Tax to automatically determine and calculate the applicable tax lines for your invoices. E-Business Tax uses your tax setup, plus fields on the invoice header and lines, known as tax drivers, to determine which taxes should be applied to the invoice, to calculate the tax using the appropriate tax rates, and to determine whether or not the tax is recoverable or non-recoverable. E-Business tax creates the necessary tax lines and distributions for the invoice and allocates tax. If your tax setup permits, you can update the tax lines or manually enter tax lines
• Invoice Types
• Standard
• Credit Memo
• Debit Memo
• Mixed
• Prepayment
• Expense Report
• Withholding Tax
• Retainage Release
• Distribution : Distribution details include invoice accounting details, the GL date, charge accounts, and project information. An invoice line can have one or more invoice distributions.

Reference:

https://docs.oracle.com/cd/E18727_01/doc.121/e12797/T295436T366808.htm

ERP - Fix for SQL query time out error in BI reports | How to increase SQL timeout for BI Reports?

The default limit set at BI server lebel for time out is 500 sec. How ever we can override this time out limit setting for individual reports at the data model level:

Solution steps:

Navigation >> Tools >> Reports & Analytics >> Btowse Catalog >> Open the data model from BI catalog >> Click on Properties tab:

Set the Query Time out to 14400 (4 hrs) - it will crease the time out for this specific report only.

Also we can enable SQL Pruning to "On" - SQL pruning enhances performances by fetching only the columns that are used in the report layout/template. Columns that are defined in the query but are not used in the report , are not fetched.

PL/SQL - Large objects

VARCHAR2 can store max 4000 bytes data then what next? LOB's or Large object comes into to overcome the Varchar2 limit.

LOB's:

• LOB's or large objects help to store large data.
• It can also store unstructured data such as video, audio, photo images, etc within the database.

LOB's are catwgorized into 2 types:

• Intetnal LOB's
• External LOB's

Internal LOB's:

Internal LOBs are stored withon the database as columns in a table and participate the transaction mode of the server.

• ClOB:  A character LOB, used to store single byte character data. Max size: 8TB to 128 TB.
• BLOB: A binary LOB, used to store binary  raw data. Max size: 8 TB to 128 TB.
• NCLOB: a LOB used to store multi byte character data. Max size: 8TB to 128 TB.

External LOB's:

External LOB's are stored outside of the database as operating system files, only a reference to the actual OS file is stored in the db. External LOBs do not participate in transactions.

BFILE:

BFILE refers to Binary file. The BFILE LOB holds reference to large binary data stored as physical files in the OS outside the db.

Max size: 4 GB

Max size of file name: 255 characters.

Max size of a directory name: 30 characters.

LOB's Restrictions:

• LOBs cant be a part of a clustered table.
• LOBs cant be used in the following part of a SQL statement.
• GROUP BY
• ORDER BY
• SELECT DISTINCT
• JOINS
• AGGREGATE functions
• LOBs cant be analyzed using the Analyze cmd 
• LOBs cant be included in a partitioned index organized table
• LOBs cant be used in VARRAYs.

LOB Locator:

• Every LOB is associated with a lob locator.
• A lob locator is a pointet to the actual location of the LOB value.
• The locator associated with internal LOBs is called a LOB locator while the locator associated with external LOBs os called a BFILE locator.
• When storing data in a LOB column, we are also storing a LOB locator with it.
• This LOB locator is what is returned to us when we select the LOB column.
• The actual LOB valur can then be retrieved using this locator.

Character Large Object (CLOB):

Create a table job_resumes

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

resume clob

);

INSERT INTO JOB_RESUMES values(1, 'Sri' 'Das', ' resume details....');

We can use varius SQL functions on CLOBs. We should use normal functions only on relatively small CLOBs, upto about 100K in size. When we are querying larger LOBs, we sould use the DBMS_LOB package functions for better performances:

Select substr(reume, 1, 30) from job_resumes;

Select DBMS_LOB.substr(reume, 1, 30) from job_resumes;

Select length(resume), dbms_lob.getlength(resume) from job_reumes;

Binary Large Object(BLOB):

• CLOBs can store only character data but BLOBs can store any type of data.
• They are used to hold non-traditional data such as videos, voice, images, pdf, documents etc.
• Even we can insert character data into a BLOB , oracle will store it but it will be in raw data. That means we cant use SQL functions like substr to handle BLOB data.

Create a table job_resumes1

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

resume blob

);

Storing images/videos in BLOBs:

Create a table job_resumes2

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

Profile_picture blob

);

CREATE DIRECTORY MYIMAGES AS 'C:\MYIMAGES';

DECLARE

SRC BFILE := BFILENAME('MYIMAGES','SRI.JPG,');

DEST BLOB;

BEGIN

INSERT INTO JOB_RESUMES2 VALUES(1, 'SRI' ,'M', EMPTY_BLOB()) RETURNING PROFILE_PICTURE INTO DEST;

Dbms_lob.open(src,dbms_lob.lob_readonly);

Dbms_lob.loadfromfile(dest,src,dbms_lob.getlength(src));

Dbms_lob.close(src);

Commit;

End;

/

Binary File(BFILE):

BFile refers to Binary file. This BFILE LOB holds references to large binary data stored as physical files in the OS outside db. They do not participate in the transactions.

Create a table job_resumes3

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

Profile_picture BFILE

);

INSERT INTO JOB_RESUMES values(1, 'Sri' 'Das', BFILENAME('MYIMAGES','SRI.JPG'));

PL/SQL - Create an Object | create table function

Object Creation:

CREATE OR REPLACE TYPE SALES_ROW AS OBJECT

(

S_DATE DATE,

S_ORDERID NUMBER,

S_PRODUCTID NUMBER,

S_CUSTOMERID NUMBER,

S_TOTALAMOUNT NUMBER

);

CREATE a table of  type  SALES_ROW

CREATE TYPE SALES_TABLE IS TABLE OF SALES_ROW;

Table function:

CREATE OR REPLACE FUNCTION FETCH_SALES_TABLE (S_ORDERID NUMBER)

RETURN SALES_TABLE

IS

L_TAB SALES_TABLE := SALES_TABLE();

BEGIN

FOR C IN 

(

SELECT SALES_DATE,ORDER_ID,PRODUCT_ID, CUSTOMER_ID, TOTAL_AMOUNT FROM SALES WHERE ORDER_ID = S_ORDERED

)

LOOP

L_TAB.EXTEND;

L_TAB(L_TAB.LAST) := SALES_ROW(C.SALES_DATE,C.ORDER_ID,C.PRODUCT_ID,C.CUSTOMER_ID,C.TOTAL_AMOUNT);

END LOOP;

RETURN L_TAB;

END;

NOW we can run this function just like querying from a regular table:

Select * from table (fetch_sales_table(1267));

Pipelined functions:

Pipeline negates the need to build huge collections by piping rows out of the function as they are created  saving memory and allowing subsequent processing to start before all the rows are generated.

CREATE OR REPLACE FUNCTION FETCH_SALES_TABLE (S_ORDERID NUMBER)

RETURN SALES_TABLE

PIPELINED

IS

BEGIN

FOR C IN 

(

SELECT SALES_DATE,ORDER_ID,PRODUCT_ID, CUSTOMER_ID, TOTAL_AMOUNT FROM SALES WHERE ORDER_ID = S_ORDERED

)

LOOP

PIPE ROW( SALES_ROW(C.SALES_DATE,C.ORDER_ID,C.PRODUCT_ID,C.CUSTOMER_ID,C.TOTAL_AMOUNT));

END LOOP;

END;

To execute:

Select * from table(fetch_sales_table(1221));

PL/SQL - Bulk Processing

Context Switch:

Every PL/SQL code includes both PL/SQL and SQL statements. PL/SQL statements are run by PL/SQL statement executor. SQL statements are run by the SQL statement executor.

When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine.

The transfer of control is called a Context Switch and each of these switches incurs ovethead that slows down the overall performance of our programs.

Bulk Processing concepts:

Bulk Collect:

SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval. We will use BULK COLLECT functionality to fetch all the ORDER_IDs and store it in collection.

FOR ALL:

INSERTs, UPDATEs and DELETEs that use collections to change multiple rows of data very quickly. We will use FOR ALL functionality to update all the rows stored in the collection. FORALL "bundles up" all the updates and passes them to the SQL engine with a single contet switch.

Example:

CREATE OR REPLACE PROCEDURE UPDATE_TAX(TAX_RATE IN NUMBER)

AS

L_ELIGIBLE BOOLEAN;

-- Associative array

TYPE ORDERID_TYPE IS TABLE OF SALES.ORDERID%TYPE INDEX BY PLS_INTEGER

L_ORDER_IDS ORDERID_TYPE;

BEGIN

-- bulk collect the order ids into a collection

SELECT DISTINCT ORDER_ID BULK COLLECT INTO L_ORDER_IDS FROM SALES;

-- for all to update all the rows as a bundle.

FORALL INDX IN 1 .. L_ORDER_IDS.COUNT

UPDATE SALES S

SET S.TAX_AMOUNT = S.TOTAL_AMOUNT *TAX_RATE WHERE S.ORDER_ID = L_ORDER_IDS(INDX);

END;

Note: suppose we have 10 orders, if we fetch the order one by one then it will have 10 context switch but if we use above code to select bulk data and update forall then it will have 1 context switch thus reducing the overhead and peformances.

Bulk Collect with Limit:

PL/SQL collections are arrays in memory, so massive collections can have a detrimental effect on system performance due to the amount of memory they require. In some situations, it may be necessary to split the data being processed into chunks to make the code mote memory friendly. The chunking can be achieved using the LIMIT clause of the BULK collect.

CREATE OR REPLACE PROCEDURE UPDATE_TAX(TAX_RATE IN NUMBER)

AS

L_ELIGIBLE BOOLEAN;

-- Associative array

TYPE ORDERID_TYPE IS TABLE OF SALES.ORDERID%TYPE INDEX BY PLS_INTEGER

L_ORDER_IDS ORDERID_TYPE;

L_ELIGIBLE_ORDERS  ORDERID_TYPE;

CURSOR SALES_CURSOR

IS

SELECT DISTINCT ORDER_ID FROM SALES;

BEGIN

OPEN SALES_CURSOR;

LOOP

FETCH SALES_CURSOR

BULK COLLECT INTO L_ORDER_IDS

LIMIT 100;

FOR INDX IN 1 .. L_ORDER_IDS.COUNT

LOOP

CHECK_ELIGIBLE(L_ORDER_IDS(INDX), L_ELIGIBLE);

IF L_ELIGIBLE

THEN

L_ELIGIBLE_ORDERS(L_ELIGIBLE_ORDERS.COUNT+1):= L_ORDER_IDS(INDX);

END IF;

END LOOP;

EXIT WHEN L_ORDER_IDS.COUNT =0;

END LOOP;

-- for all to update all the rows as a bundle.

FORALL INDX IN 1 .. L_ELIGIBLE_ORDER.COUNT

UPDATE SALES S

SET S.TAX_AMOUNT = S.TOTAL_AMOUNT *TAX_RATE WHERE S.ORDER_ID = L_ELIGIBLE_ORDERS(INDX);

COMMIT;

CLOSE SALES_CURSOR;

END;

BULK collect with Rowtype:

CREATE OR REPLACE PROCEDURE FETCH_SALES_CUR(S_DATE DATE)

AS

CURSOR SALE_CURSOR

IS

SELECT SALES_DAYE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT

FROM SALES

WHERE SALES_DATE =S_DATE;

TYPE L_SALESTABLE IS TABLE OF SALES%ROWTYPE;

L_SALES L_SALESTABLE;

BEGIN

OPEN SALE_CUTSOR;

LOOP

OPEN FETCH SALE_CURSOR BULK COLLECT INTO L_SALES LIMIT 100;

FOR INDX IN 1 .. L_SALES.COUNT

Loop

Dbms_output.put_line(L_SALES(INDX).SALES_DATE);

Dbms_output.put_line(L_SALES(INDX).ORDER_ID);

Dbms_output.put_line(L_SALES(INDX).PRODUCT_ID);

Dbms_output.put_line(L_SALES(INDX).customer_id);

...

END LOOP;

EXIT WHEN SALE_CURSOR%NOTFOUND;

END LOOP;

CLOSE SALE_CURSOR;

END;

/