Oracle Sql Joins

A join combines two or more tables or views for querying. Table or View names will be specified in the from clause. Any columns from those tables can be specified in select clause.

Join Condition

Condition specified in the where clause to evaluate the joins is called Join Condition. Most of the queries contain Join Conditions that compare two or more columns.

Example :-

Consider the below join query

SQL> select deptname,empname from dept d,emp e where d.deptno = e.deptno;

Here “d.deptno = e.deptno” is the join condition.

For joins having three tables, Oracle joins first two of them based on the join condition and then joins with the third one based on the join condition containing third table. Oracle does this process until all tables are joined.

Cartesian Products

If two or more tables are joining without join condition will result into Cartesian products.

If table A has 2 rows and table B has 4 rows then Cartesian product between A and B will return 8 rows ( 2 multiply by 4 )

Example :-

SQL> select count(*) from dept;

COUNT(*)
———-
5

SQL>

SQL> select count(*) from emp;

COUNT(*)
———-
15

SQL>

SQL> select count(*) from dept e, emp e;

COUNT(*)
———-
75

Note :- So always remember to include a join condition. Generally for large tables cartesian products are expensive.

There are different types of Joins available in Oracle

Equi Joins

Equi join contains an equality operator.

Example :-

SQL> select deptname,empname from dept d,emp e where d.deptno = e.deptno
and e.salary > 10000 ;

Self Joins

Self join used to join the table with the same table.

Example :-

SQL> select e.empno,e.empname as employee,m.empname manager from emp e,emp m
where e.empno = m.manager;

Note that all the tables must be aliased properly.

Inner Joins

An inner join (or simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

Example :-

SQL> select deptname,empname from dept d,emp e where d.deptno = e.deptno;

Outer Joins

Outer Join is used to join tables with sparse data in one or more table for the columns used in the join condition. Outer join returns all rows which satisfies the join condition plus other records from one table and no records from other table. The column will be nullified for the records has no data.

Outer join can be further classified into left outer join, right outer join and full outer join

Left outer join

Left outer join of tables A and B and returns all rows satisfy the join condition plus all records from A which doesn’t have correspondent records in B.

SQL > select * from dept e,emp e where d.deptno = e.deptno(+);

Right outer join

Right outer join of tables A and B and returns all rows satisfy the join condition plus all records from B which doesn’t have correspondent records in A.

SQL > select * from dept e,emp e where d.deptno(+) = e.deptno;

Left outer join

Left outer join of tables A and B and returns all rows satisfy the join condition plus all records from A which doesn’t have correspondent records in B.

SQL > select * from dept e,emp e where d.deptno = e.deptno(+);

Full Outer Join

If you combine left and right outer join forms a full outer join.

Antijoins

Anti join used to find records from a table which doesn’t have a correspondent records in second table.

Example :-

select * from emp where deptno not in (select deptno from dept where deptno in (1,4));

Conclusion :-

SQL is the integral part of database programming and to write efficient SQLs you need to know how and where to use which joins. Out of the above joins, outer joins are more complex and we will examine it with more examples in another post.

PRAGMA RESTRICT_REFERENCES Oracle

PRAGMA RESTRICT_REFERENCES uses to control the side effects of PL/SQL Subprograms. Every PL/SQL Subprograms must follow some rules in terms of transaction control and security.     

What is PRAGMA

PRAGMA is an instruction or a hint or information to the compiler. Pragmas are processed at compile time, not at run time.

Syntax :-

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;    
PRAGMA restrict_references(get_emp_salary, RNDS, RNPS, WNDS, WNPS);
END pkg_salary;

RNDS – Read No Database State. Asserts that the function not to read or query tables
RNDS – Read No Package State. Asserts that the function not to read or reference package variables
WNDS – Write No Database State. Asserts that the function not modify database tables
WNPS – Write No Package State. Asserts that the function not modify package variables
TRUST – Asserts that the function can be trusted not to violate one or more rules. Used only when C or JAVA routines are called from PL/SQL.

Explanation :-

In the above example Function get_emp_salary is associated with PRAGMA restrict_references. Oracle conveying the compiler to follow four rules WNDS, WNPS, RNDS, RNPS. When the package body compiles and find any rules which violates any of the rules (RNDS, RNPS, WNDS, WNPS, TRUST) it will raise a compilation error. Note that if there is a PRAGMA derivative and violates any rules it will NOT raise any compiler error and it might raise run time error.

Consider the below examples

1) Function to raise employee salary 

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
END pkg_salary;
/

CREATE OR REPLACE PACKAGE body pkg_salary
IS

function get_emp_salary(p_empno integer) return number
is
n_sal number;
Begin
update emp set salary = salary + salary * 0.1 where empno = p_empno
returning salary into n_sal;
commit;
return n_sal;
End get_emp_salary;

END pkg_salary;
/

Compiling……..

SQL> ../pkg_salary.sql;

Package created.

Package body created.

Executing………

SQL> select pkg_salary.get_emp_salary(10) from dual;
select pkg_salary.get_emp_salary(10) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query

So package specification and body compiled properly but unable to use it as it raise error while executing.

2) Function to raise employee salary – With PRAGMA restrict_references

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
PRAGMA restrict_references(get_emp_salary, WNDS);
END pkg_salary;
/

CREATE OR REPLACE PACKAGE body pkg_salary
IS
function get_emp_salary(p_empno integer) return number
is
n_sal number;
Begin
update emp set salary = salary + salary * 0.1 where empno = p_empno
returning salary into n_sal;
commit;
return n_sal;
End get_emp_salary;

END pkg_salary;
/

Compiling……..

SQL> ../pkg_salary.sql;

Package created.

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY PKG_SALARY:

LINE/COL ERROR
——– —————————————————————–
4/1      PLS-00452: Subprogram ‘GET_EMP_SALARY’ violates its associated
pragma

When we use PRAGMA restrict_references it raises a compiler error which helps developer to re-write his code.
Generally PRAGMA restrict_references are used with functions.

Note:-

DEFAULT key word applies PRAGMA restrict_references to all of the sub programs in side the package
See below example

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
function get_emp_name(p_empno integer) return varchar2;
PRAGMA restrict_references(DEFAULT, WNDS);
END pkg_salary;
/

Package created.

Note:- Normally functions are not created for DML related processes and PRAGMA restrict_references are not necessary.

Triggers in Oracle

Oracle Trigger executes based on an event, say after a table update or before a login etc.
Oracle Database automatically executes a trigger when certain conditions occur.

Oracle triggers classified into

1. DML Triggers
2. DDL Triggers
3. Instead of Trigger

In this post we will examine DML triggers

DML Triggers

DML triggers are which associated with DML operations, say insert into a table, update a view, delete from a table etc
Mainly DML triggers are 2 types, row level and statement level.
Row level triggers are fired whenever a row changes and statement level fires whenever any statement ( for example a batch update ) executes. Both row level and statement level can be defined with 3 different DMLs, insert,delete and update

In all-together

DML Triggers

• Row Level

1. Before Insert  and After Insert 
2. Before Update and After Update
3. Before Delete and After Delete

• Statement Level

1. Before Insert  and After Insert
2. Before Update and After Update
3. Before Delete and After Delete

So you can create total 2*3*2 = 12 types of DML triggers in Oracle

Statement level DML trigger will fire once per batch, Say I have Statement-lelvel before update trigger and I am updating the whole table using just one update command, the trigger will fire only once.

Row-level trigger will always fire for each row.

“:new” and “:old”
“:new” and “:old” keywords can be used in row-level triggers only. “:old” is used to reference old column value and :new is used to reference new column. Only in Update triggers you can use “:new” and “:old” in same context. See below table for more information.

old and new reference

CREATE TRIGGER

CREATE TRIGGER [SCHEMA].TRIGGER_NAME
BEFORE INSERT
ON [SCHEMA].TABLE_NAME FOR EACH ROW
DECLARE
BEGIN
“:new”.colum_name1  := ‘some_value’;
“:new”.column_name2 := ‘some_value’;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END TRIGGER_NAME;

Here [SCHEMA] is optional. In above a row-level trigger after insert on a TABLE_NAME will be created.
Below another example which combines two different type of trigger together.

CREATE or REPLACE TRIGGER [SCHEMA].TRIGGER_NAME
BEFORE INSERT
ON [SCHEMA].TABLE_NAME FOR EACH ROW
DECLARE
BEGIN
if inserting then
if “:new”.recid is null then
— do something —
end if;
elsif updating then
— do something —
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END TRIGGER_NAME;
/

CREATE or REPLACE command creates the trigger if it doesn’t exists and replaces if it exists. Note that it replaces the trigger for the base table only. You cannot replace trigger name which is associated with another table.

DROP TRIGGER

DROP TRIGGER TRIGGER_NAME;

The above command will drop the trigger permanently.

ALTER TRIGGER

ALTER TRIGGER command can be used to rename, compile, disable and enable the trigger;

ALTER TRIGGER trg_dept compile; — recompiles the trigger

ALTER TRIGGER trg_dept disable; — disables the trigger

ALTER TRIGGER trg_dept enable; — disables the trigger

ALTER TRIGGER trg_dept rename to trg_emp; — rename the trigger

DDL Triggers

DDL Triggers are triggers which associated with DDL (Data Definition Language) such as Dropping a table, Altering a column  etc. DDL triggers execute every time a DDL statement is executed. Generally DBA’s create DDL triggers for auditing and enforcement purposes.

General Syntax

create or replace trigger
DDLTrigger_name
AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN

— code here —

END;
/

Example :-

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on DATABASE
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

Note :- ddl_table is a user defined table which is in DBA schema.

Within the database any DDL issued, details will be saved into ddl_table table.

similarly DDL trigger can be created within schema also.

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on SCHEMA
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

DDL Triggers for LOGON/LOGOFF

LOGON

create or replace trigger
ddl_trigger3
after LOGON on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

LOGOFF

create or replace trigger
ddl_trigger3
after LOGOFF on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/DDL Triggers are triggers which associated with DDL (Data Definition Language) such as Dropping a table, Altering a column  etc. DDL triggers execute every time a DDL statement is executed. Generally DBA’s create DDL triggers for auditing and enforcement purposes.

General Syntax

create or replace trigger
DDLTrigger_name
AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN

— code here —

END;
/

Example :-

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on DATABASE
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

Note :- ddl_table is a user defined table which is in DBA schema.

Within the database any DDL issued, details will be saved into ddl_table table.

similarly DDL trigger can be created within schema also.

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on SCHEMA
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

DDL Triggers for LOGON/LOGOFF

LOGON

create or replace trigger
ddl_trigger3
after LOGON on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

LOGOFF

create or replace trigger
ddl_trigger3
after LOGOFF on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

Ref Cursors Oracle

The REF CURSOR is a data type in the Oracle. REF CURSOR also referred as Cursor Variables.Cursor variables are like pointers to result sets. Cursor can be attached to only one query while REF CURSOR can be used to associate multiple queries at run time. 

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
refcur1  empcurtyp;
Begin
Open refcur1 for select * from emp;
Open refcur1 for select * from dept;
End;

REF CURSOR can be categorized into three

1. Strong Ref Cursor
Ref Cursors which has a return type is classified as Strong Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;

Here empcurtyp is a Strong Ref Cursor

2. Weak Ref Cursor
Ref Cursors which has no return type is classified as Weak Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR;
…..
End;

Here empcurtyp is a Weak Ref Cursor

3. System Ref Cursor
This is a system defined Ref Cursor. This also considered weak. System Ref Cursor need not declare explicitly.

Declare
empcurtyp SYS_REFCURSOR;
…..
End;

Advantages 
1. Ref Cursor it self is a data type and easy to declare
2. More flexible because it is not tied to a specific query
3. Easily pass as arguments from subroutine to subroutine.
4. Very handy in transferring data between multi-language application (ex:- Java and Oracle, Dot.net and Oracle, Oracle Forms and Oracle). Since it is a pointer to the result set any client and server program can use the pointer to access the data.
5. Cursor variables are bind variables 

Dis-advantages
1. Ref Cursors are not efficient as StatiC Cursor
2. Need additional code to print Ref Cursor values

In general Ref Cursors are only be used when static cursor cannot do the work. 

Prepayments in Oracle AP

Prepayments in Oracle Payables:
Normal Payables cycle is that you first create invoice when you get one from you supplier for the goods that you have bought and then make a payment of the amount in the invoice to the supplier.
But there are scenarios where the supplier requests for an advance when you order the goods. Supplier wants you to pay a certain amount at the time of order and then pay the remaining upon delivery of the goods.
To records such type of transactions, Payables has a concept called Prepayment.
Say you are buying $2000 worth of goods from supplier XYZ. XYZ requested you to pay $1000 immediately. When you pay an advance, you create a prepayment in AP module and make the payment for the same. Later when the supplier sends an invoice of $2000 at the time of delivery, you create a invoice in AP and you apply the prepayment to the invoice. This will get down the balance on the invoice to $1000 which means you will only have to pay $1000 to the supplier.

You can enter two types of prepayments: Temporary and Permanent.

Temporary prepayments can be applied to invoices or expense reports you receive. For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel’s invoice arrives, apply the prepayment to the invoice to reduce the invoice amount you pay.

Permanent prepayments cannot be applied to invoices. For example, you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.

Prepayment Cycle Steps:
1.Create Prepayment
2.Create Payment for the Prepayment
3.Create invoice when supplier sends one.
4.Apply the prepayment to the invoice.

1. Create a Prepayment
Navigation: Payables responsibility > Invoices > Entry > Invoices
Enter the below details:
Operating Unit: Choose the operating unit for which you want create the prepayment.
Type: Select the type as Prepayment
Trading Partner: Enter the supplier name to whom you want to make the advance payment.
Invoice Date: Enter the date of the prepayment
Invoice Number: Enter a unique number to represent this invoice.
Invoice Amount: Enter the amount that you are giving as advance payment.

Go to the Line tab and enter the below information
Amount: Enter the line amount.
Save the work.

Click on Calculate Tax button. This will calculate the tax as per the rules defined in Tax module. Tax amount has come up to $75 for this.

Now add this tax amount to the Invoice Amount field on the Invoice Header.

Save the work.
Validate the Prepayment by clicking on Actions button and select Validate Check box. Click on OK.
This will validate the information entered in the Invoice form and will display any variances in HOLDS tab.

2. Create a payment
Go to Scheduled Payments tab and click on Pay button

Click OK in the dialog box that opens up

Payment window opens up. Enter the below information:
Payment Process Profile: Enter a valid Payment Process profile. It is a setup that will tell the payment manager how to process the payment like what is the mode of payment – Check, wire transfer etc
Save the work.

Now if you query the prepayment, you will see that Amount paid field shows $1075. Earlier it was $0.

3. Create Invoice when the supplier sends one for the goods you bought from him.
Navigation: Payables responsibility > Invoices > Entry > Invoices
Enter the below details:
Operating Unit: Choose the operating unit for which you want create the prepayment.
Type: Select the type as Standard
Trading Partner: Enter the supplier name. Once you enter the supplier name, a dialog box appears reminding you that a prepayment exists for this supplier which can be applied to this invoice instead of making a new payment.

Invoice Date: Enter the date of the prepayment
Invoice Number: Enter a unique number to represent this invoice.
Invoice Amount: Enter the amount that the supplier has charged you.
Go to the Line tab and enter the below information
Amount: Enter the line amount.
Save the work.
Click on Calculate Tax button. This will calculate the tax as per the rules defined in Tax module. Tax amount has come upto $75
Now add this tax amount to the Invoice Amount field on the Invoice Header.
Save the work.
Validate the Prepayment by clicking on Actions button and select Validate Checkbox. Click on OK.
This will validate the information entered in the Invoice form and will display any variances in HOLDS tab.

4. Apply the prepayment to the invoice you have created above.
Query the invoice that you have created and click on Actions Tab and select “Apply/Unapply Prepayment” check box. Click OK.

This will pull up the prepayments that haven’t been used for the supplier.
Check Apply and save the work

You will now see this prepayment under Existing Prepayment Application section. If you wish to Unapply, check the box next to Unapply and save the work.

Go the View Prepayment Applications. You will see the prepayment details.

Also observe that the additional lines have been added to the invoice because of the prepayment application.

The sum of the line amounts in the invoice is $0 which means that after applying the prepayment, the invoice balance has come down to $0. Sine Line amount is 0, correct the invoice amount in the header also to $0 and then validate the invoice.

Points To Note:

1. Settlement date: The date after which the prepayment can be applied to an invoice.
2. On a prepayment, you can enter any number of distributions, either manually, or automatically by purchase order matching, distribution sets, or allocating.
3. You can take discounts on prepayments.
4. You can create Foreign Currency Prepayments.
5. You cannot partially pay a prepayment. you must fully pay it.
6. You can apply prepay on the invoice type is Standard, Mixed, or Expense Report.
7. You can apply only Item distributions from the prepayment.
8. You must fully pay a prepayment before you can apply the prepayment to an invoice.
9. If you want to apply Permanent type prepay then you can change the Prepayment Type to Temporary.
10. Supplier, invoice currency and payment currency must be same on prepay and to invoice.
11. If the prepayment has a value in the Prepayment PO Number field, then the invoice must be matched to the same purchase order.
12. GL Date on Apply/Unapply window, which is the accounting date for the new Prepayment distributions that Payables creates when you apply a prepayment. This date must be after the latest accounting date on any distribution for either prepayment or invoice and must be in an open period.

STEPS TO CREATE SUPPLIER, SUPPLIER SITE AND SUPPLIER BANKS : ORACLE EBS R12

Below are the steps to create Supplier and related sites and banks in Oracle EBS R12

1) Choose the responsibility “Purchasing Super User”. Navigate to “Suppliers” window using the path “Supply Base -> Suppliers”.

2) Enter the supplier name in the “Create Supplier” window. Press on the “Apply” button.

3) To create a supplier site, an “Address” should be created first.

 Click on the “Address Book” in the “Suppliers” window to navigate to the address creation window.

 Click on the “Create” button to create the “Address”.

 Enter the address details and click on “Continue” button.

 Associate the “Address Sites” with the necessary operating units. Click on the “Apply” button. Supplier site is now created for the supplier as shown in the below screen shot.

 4) Create contacts for the supplier site.

 Click on the “Contact Directory” in the Suppliers window.

 Click on the “Create” button.

 Enter the contact details as shown in the above screen shot. Associate the supplier site with the contact in the “Addresses For the Contact” section. Click on the “Apply” button.

 Contact is created as shown in the above screen shot.


5) Create or associate bank accounts to the supplier site.

 Click on “Banking Details” in the Suppliers window.

 Select the level at which you want to create or associate the bank for the supplier. We are choosing to create the bank account at the supplier site level. Click on the “Create” button.

 Select the Country, Bank and Bank Branch as shown in the above screen shot.

 Enter the bank account details and press on “Apply” button.

 Bank account is created for the supplier site.

What TCA contains

Customers
We sell our products/services & bill customers using Order Management & Receivables

2. Suppliers
They sell us their goods(PO & iProc) and we pay their Payables invoices in Oracle

3. Employees
These can be special customers, or pseudo vendor if they even sue you, as you make them payment.

4. Banks
Banks can be paying us interest, or charging penalties, or selling us products.

5. Insurance Company
This is Interesting. When you buy their policy, you become their customer. However when you make a claim, you become their customer(as you get paid).

6. Students
Even more interesting than 5 above. They are your customers, as you would bill them. They can become your staff latter. Or they can become your vendor if they become independent contractor latter by selling their services to you. Even more, they can become your student again(for post-grad), after resigning as an employee.