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;

/

PL/SQL - Triggers

Triggers are stored program , which are automatically executed or fired when some events occur.

Triggers are infact executed in response of the following events:

• DML operation - delete, insert and update
• DDL operation - create, alter or drop
• Other db operations - servererror, logon, logoff, shutdown

Triggers could be defined on table, view, schema or db with which the event is associated.

Benefits of Triggers:

• Generating some derived column values automatically
• Enforcing referencial integrity
• Event logging and storing info on table access
• Auditing
• Imposing security authorization
• Preventing invalid transactions

Trigger types:

• Row level Triggers
• Statement level triggers
• Before triggers
• After triggers

Row level Triggers:

Row level triggers execute once for each row in a transaction. For example, if an update statement updates multiple rows of a table, a row level trigger is fired once for each row affected by the update statement.

Example, an update statement updates 3 rows in a table, then 3 triggers will be fired.

Statement level Triggers:

Statement level triggers are triggered only once for each transaction. For example, when an update commands update 2 rows, the commands contained in the trigger are executed only once.

Before Triggers:

Before trigger execute the trigger action before the triggering event(insert, update or delete). 

• Before triggers are used to drive specific column values before completing a triggering insert, update operation.
• Before triggers are used when the trigger action should determine whether or not the triggering statement should be allowed to complete.

After Triggers:

After trigger executes the trigger action after the triggering event(insert, update or delete).

Example, 

To perform cascade delete operation, it means that user delete the record from one table but the corresponding records in other tables are deleted automatically by a trigger which fired after the execution of delete statement issued by the user.

Example 1 - statment level trigger:

When we will update the customet table, before the update, the following trigger will be fired and put an audit entry into the audit table.

CREATE TRIGGER customer_before_update

BEFORE UPDATE

ON CUSTOMER

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'BEFORE UPDATE OPERATION');

END;

Example 2 - staement level with more events

CREATE TRIGGER customer_after_action

AFTER INSERT OR DELETE OR UPDATE

ON CUSTOMER

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

IF INSERTING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION');

ELSIF DELETING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'DELETE OPERATION');

ELSIF UPDATING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'UPDATE OPERATION');

END IF;

END;

Example row level trigger:

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION ROW LEVEL');

END;

OLD and NEW pseudo records with an example:

When a row level trigger fires, the PL/SQL runtime system creates and populates the two pseudo records OLD and NEW.

• For an Insert trigger, OLD : none, NEW: nee value
• For an Update trigger, OLD: old value, NEW: New value
• For a Delete trigger, OLD: old value, NEW: none

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Row level Triggers Example with WHEN clause:

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

WHEN (OLD.REGION = 'SOUTH')

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Row level triggers with OF clause

CREATE TRIGGER customer_after_update

AFTER UPDATE

OF CUSTOMER_ID

ON CUSTOMER

FOR EACH ROW

WHEN (OLD.REGION = 'SOUTH')

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Disable and enable triggers:

Disable/Enable:

ALTER TRIGGER <TRIGGERNAME> DISABLE;

ALTER TRIGGER <TRIGGERNAME> ENABLE;

Disable / enable all the triggers present in the table:

ALTER TABLE <TABLE NAME> DISABLE ALL TRIGGERS;

ALTER TABLE <TABLE NAME> ENABLE ALL TRIGGERS;

Drop a trigger:

DROP  TRIGGER <TRIGGER NAME>;

Rename a trigger:

ALTER TRIGGER <OLD TRIGGER NAME> RENAME TO <NEW TRIGGER NAME>

PL/SQL - Collections

Collection:

• Collection is a composite datatype like Record.
• An Oracle PL/SQL collection is a single dimensional array
• It consist of one more elements accessible through an index value.
• All the elements have the same data type.

Advantages:

Collections are used in some of the important performance optimization features og PL/SQL,

• BULK collect : select statements that retrieve multiple rows with a single fetch.
• FORALL : inserts, updates and deletes that use collections to change multiple rows of data very quickly
• Table functions : functions that return collections and can be called in the FORM clause of a select statement.

Sparse vs dense collection:

If a collection contains all the elements is called dense. And a collection is sparse if there is atleast one index value between the lowest and highest defined index values that is not defined.

Collection methods:

A collection method is a procedure or function that either provide information about the collection or changes the contents of the collection. Methods are attached to the collection variable with dot notation, for example, my_collection.FIRST.

Types of collections:

• Associative Array or index by tables
• Nested tables
• VARRAY

Associative array:

This type of collection is indexed using BINARY_INTEGER values or VARCHAR2 values, which dont need to be consecutive. This were originally called as PL/SQL tables.

DECLARE

TYPE customer_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

customer_table customer_type;

v_idx number;

BEGIN

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

customer_table(6):= 'Sri';

--delete 3rd element

customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

Nested Tables:

• Nested table can be stored in a database.
• It can be sparse but are almost always dense.
• They can be indexed only by integer.
• We can use the MULTISET operator to perfoem set operations and to perform equality comparisons on nested tables.
• The elements entry must be sequential.

DECLARE

TYPE customer_type IS TABLE OF VARCHAR2(100);

-- initialize the collection

customer_table customer_type := customer_type();

v_idx number;

BEGIN

--you have to extend before using the table

customer_table.extend(4);

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

--customer_table(6):= 'Sri'; -- throws an error

customer_table(4):='Sri'; -- it must be sequential.

--delete 3rd element

customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

/

VARRAY:

• A VARRAY is similar to a nested table except we must specify an uppet bound in the declaration.
• Like nested table, we can store in the tadabase.
• Unlike nested tables, individual elements cant be deleted so they remain same.

DECLARE

TYPE customer_type IS VARRAY(4) OF VARCHAR2(100);

-- initialize the collection

customer_table customer_type := customer_type();

v_idx number;

BEGIN

--you have to extend before using the table

customer_table.extend(4);

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

--customer_table(6):= 'Sri'; -- throws an error

customer_table(4):='Sri'; -- it must be sequential.

--can not delete an item. 

--customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

/

Collection methods:

Multiset operator:

We can do following operations

• MULTISET UNION
• MULTISET UNION DISTINCT
• MULTISET EXCEPT
• MULTISET INTERSECT

DECLARE

TYPE t_tab IS TABLE OF NUMBER;

l_tab1 t_tab := t_tab(1,2,3,4,5,6);

l_tab2 t_tab := t_tab(5,6,7,8,9,10);

BEGIN

l_tab1 := l_tab1 MULTISET INTETSET l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP

Dbms_output.put_line(l_tab1(i));

END LOOP;

END;

/