We are trying to explain Oracle’s “left outer join”, “right outer join” scenarios with simple examples for beginners. If experts find wrongs with the explanations provided, please forward us correct explanations and we will be more than glad to amend this thread
What you need
Access to Oracle database (obviously we know you have one )
insert into authors values(1002,'Abdul Aziz Marafi'); /
insert into authors values(1100,'Rajesh Thampi'); /
insert into book_titles values(1006, 'I.T Policies, 2011 Approaches'); / insert into book_titles values(1002, 'ASP.3 Switch from ASP'); / insert into book_titles values(1112, 'PL/SQL Tricks and Tips'); / COMMIT; / Select 'There are total '||to_char(count(*))||' records in authors table' no_recs from authors; /
Select 'There are total '||to_char(count(*))||' records in book_titles table' no_recs from book_titles; /
Once you created tables and inserted rows, let us move to trying out the “left out join” and “right outer join” against our new tables
Right Outer Join Example
/*Here authors table becomes left side table, book_title becomes right side table and the join condition is trying to fetch all the records which satisfy auth_id column available in both tables and then all records from left table even though corresponding condition with table right fails*/
Select a.*, b.book_title from authors a, book_titles b where --b.auth_id(+) = a.auth_id --Uncomment and comment next line to TEST a.auth_id = b.auth_id(+);
Here table A (authors) is joined with table B (book_titles) against column auth_id and all rows satisfying the condition are fetched first then balance records from Table A are fetched
This scenario could be once again explained as bring everything from both tables A,B matching specific condition, then everything from Table A what do not satisfy the join condition Thus right outer join is could be explained by “us” as “bring everything from left table (A) that are not joinable with a MERE relation expression: eg ‘=’
Left Outer Join Example
Select a.*, b.book_title from authors a, book_titles b where b.auth_id = a.auth_id(+) ;
Left outer join matches the condition first, then brings all rows from table B (book_titles) immediately after the condition satisfied rows. Thus left outer join could be explained by “us” as “bring everything from right table (B) that are not joinable with a MERE relative expression: eg ‘=’
Quest Toad may the best Oracle database tool developed by a third party. However, this costly tool may not be the best one for importing objects when you are in a process of migrating from an older version of database to relatively new one! (Or minimum our experiences prove this multiple occasions)
Oracle’s import tool (imp.exe on windows) is a console based utility and could be used without worrying the headaches usually Toad creates.
Below we are providing a “kind” of instructions about importing objects from a full database backup “.dmp” file which is created using Oracle’s export console utility
Prior importing objects, please make sure you have created the following with fresh installed database
Grants to the users (CONNECT, RESOURCE, DBA etc)
A parameter file, call it “myparam.txt” (preferably in the same directory where you store the “.dmp” file. We prefer to keep both the files with root of a partition itself)
(Sample parameter file)
Once you created the above objects, you may proceed towards importing the user objects (tables, views, functions, packages, procedures etc)
following the same formula with the private API function has generated the same value as per the inventory report.
The updated API is packaged once again and available to download from here
Recently we received few comments about the inventory values generated using the below hack not matching the transaction historical summary report values (R12). We had gone through a series of exercises to evaluate such claims and confirmed that all those comments were valid. Below are the few possible explanations.
Though both the above said reports call the same API to populate inventory details, parameters passed into the API are slightly different.
For the report “Inventory Value By Subinventory” the API is called like following
Parameter “p_intransit_value => 1” makes the entire scenario different from “Transaction Historical Summary” as the inventory values are calculated against the quantities including the quantities in transit also. Further, for Average cost organizations, the report calculates the values against the current date item costs (We are trying to get an explanation for the same from multiple oracle communities, including communities.oracle.com)
Further, the rollback date column, unless entered as cutoff date 23:59:59 (eg: 31-dec-2012 23:59:59) always rolls back to 31-dec-2012 00:00:00, thus not picking up lines whichever were processed later for the entered date.
On the other hand, Transaction Historical Summary report calls the API like following
Here the parameter “p_intransit_value => NULL” is set as NULL, thus the quantities in transit are not calculated. Further, the historical average costs are picked up for the transactions (material costs)
Further the rollback date is always expected to be entered like “31-dec-2012 23:59:59” in order to include the all the material transactions happened on the date.
So the in-transit quantity differences, item costs and the material transactions happened within the cutoff date time frame creates the variances what the user see with both the reports.
We had done the exercises more than few dozen times to reach to these unconfirmed conclusions. If as an experienced Oracle application user, dealing with Oracle inventory has any other explanations, Please, come ahead and we will amend the post with your valued inputs.
Requirement details: Oracle provides multiple standard reports to generate Inventories values on specified dates and depending upon the volume of transactions all these reports generate thousands of lines details to reach to such cut off date inventory values. Our requirement was to provide the auditors a quick view to inventory values as on end of each month, thus the entire development of below provided solution started.
How it works
Folder view supported form module developed by us calls a stored procedure, generates the rows into GLOBAL TEMPORARY tables and a stored function sum ups the material value and inserts into a local table (Base table with the form module)
For all the closed inventory periods, an insert statement picks up the values for the cut off dates from the view“cst_period_summary_v” and the months which are not already in the “cst_period_summary_v” view are populated by calling PVT APIs what we have heavily customized.
We achieved our goal by disabling the gather table statistics which are called from the PUBLIC API, then exclusively calling a COMMIT from the primary loop initiated by “populate history values” button press, thus indirectly flushing out the GLOBAL TEMPORARY TABLES for next run.
We hope this solution will be useful for organizations around the world who are running Oracle ERP 12.x.xx suites.
Oracle clearly states the API CST_Inventory_PUB is private and shouldn’t be called by the users exclusively from any other procedures or packages. Please refer to :“Using Oracle API CST_Inventory_PUB Package ID 847101.1]”
Hence you are going to use the solution provided by us at your own risk (Ironically, this PVT API is nothing more than few select statements based on different parameters passed in)
Here we are providing a solution to populate inventory values until “last month” by a mere mouse click
6. In the install printer driver dialog box do one of the following
1. In the left panel select your printer manufacturer and in the right panel select your printer model (to use Win7 native drivers) if you’ve already had the Epson attached and installed on the Windows 7 machine, this step should work for you.
2. Select the have disk button and direct the install process to the folder where you have download and extracted the Win 7 drivers for your printer. Unfortunately if Epson doesn’t provide any Windows 7 drivers this step will not work for you.
We hope the above instructions relieve some kind of stress for hardware teams!
* If you are an employee and you are getting connected to internet through regulated access, based on your access level, you may not able to use this application to get connected or post new entries.
We tried to use this application provided as a part of Windows live essentials and always failed behind Microsoft ISA firewall and hardly could remember any settings being provided to setup the proxy details until recent times.
So trying out Windows live writer! Stay tuned we have much for you