Oracle Left outer join, right outer join

 

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

2 Tables

Access to  Oracle database (obviously we know you have one Smile )

Execute the following as script (SCOTT/TIGER)

Create table authors
(auth_id number,
auth_name varchar2(60)
);
/
Create table book_titles
(auth_id number,
book_title varchar2(240)
);
/

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(+);

image

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(+) ;

image

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 ‘=’

Regards,

Admin

Oracle database, port list???

 

How often you need to know the port numbers associated with your Oracle database? Not many times. However, keep this post at a safe place, you may need it

 

image

Most of the HTTP related port references are stored with “portlist.ini” file (default location may change according to your install preferences)

 

Regards,

Admin

Oracle imp VS Quest Toad’s import utility Wizard

 

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

image

Prior importing objects, please make sure you have created the following with fresh installed database

  1. Tablespaces
  2. Users
  3. Grants to the users (CONNECT, RESOURCE, DBA etc)
  4. 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)
  5. image(Sample parameter file)

Once you created the above objects, you may proceed towards importing the user objects (tables, views, functions, packages, procedures etc)

Now open command prompt and type the following

C:\> imp system/<<password>> PARFILE=paramtext.txt LOG=C:\19052012imp.log

Now sit back while Oracle does the import job for you!

 

Regards,

Admin

Oracle Inventory, Historical Inventory Values

Update: 11.Jan.2018

We’ve rectified the minor inventory value differences between the API generated values & the standard report “Inventory Value Report – by Subinventory” as due to the item costs those are used.

For average costing organizations, the report always generates the cut off date values again the current costs, instead of the costs those are populated to the intermediate tables.

IC_TOTAL_COST, the column that shows the cost as on cut off date is using the following formula

NVL(CIQT.rollback_qty,0) * DECODE(NVL(SEC.asset_inventory,1), 1, NVL(CIC.item_cost,0), 0) *:P_EXCHANGE_RATE IC_TOTAL_COST

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 

Update: 06.May.2013

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

 CST_Inventory_PUB.Calculate_InventoryValue(
      p_api_version => 1.0,
      p_init_msg_list => CST_Utility_PUB.get_true,
      p_organization_id => :P_ORG_ID,
      p_onhand_value => 1,
      p_intransit_value => 1,
      p_receiving_value => 0,
      p_valuation_date => to_date(:P_AS_OF_DATE,'YYYY/MM/DD HH24:MI:SS'),
      p_cost_type_id => :P_COST_TYPE_ID,
      p_item_from => :P_ITEM_FROM,
      p_item_to => :P_ITEM_TO,
      p_category_set_id => :P_CATEGORY_SET,
      p_category_from => :P_CAT_FROM,
      p_category_to => :P_CAT_TO,
      p_cost_group_from => NULL,
      p_cost_group_to => NULL,
      p_subinventory_from => :P_SUBINV_FROM,
      p_subinventory_to => :P_SUBINV_TO,
      p_qty_by_revision => :P_ITEM_REVISION,
      p_zero_cost_only => :P_ZERO_COST,
      p_zero_qty => :P_ZERO_QTY,
      p_expense_item => :P_EXP_ITEM,
      p_expense_sub => l_exp_sub,
      p_unvalued_txns => :P_UNCOSTED_TXN,
      p_receipt => 1,
      p_shipment => 1,
      x_return_status => l_return_status,
      x_msg_count => l_msg_count,
      x_msg_data => l_msg_data
    );

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

CST_Inventory_PUB.Calculate_InventoryValue(
     p_api_version => 1.0,
     p_init_msg_list => CST_Utility_PUB.get_true,
     p_organization_id => :P_org_id ,
     p_onhand_value => 1,
     p_intransit_value => NULL,
     p_receiving_value => 0,
     p_valuation_date => l_hist_date,
     p_cost_type_id => NULL,
     p_item_from => :p_item_lo ,
     p_item_to => :p_item_hi ,
     p_category_set_id => :p_cat_set_id ,
     p_category_from => :p_cat_lo,
     p_category_to => :p_cat_hi,
     p_cost_group_from => :p_cg_lo,
     p_cost_group_to => :p_cg_hi,
     p_subinventory_from => :p_subinv_lo,
     p_subinventory_to => :p_subinv_hi ,
     p_qty_by_revision => NULL,
     p_zero_cost_only => NULL,
     p_zero_qty => NULL,
     p_expense_item => NULL,
     p_expense_sub => NULL,
     p_unvalued_txns => 0,
     p_receipt => NULL,
     p_shipment => NULL,
     x_return_status => l_return_status,
     x_msg_count => l_msg_count,
     x_msg_data => l_msg_data
  );

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

image

The entire solution could be downloaded from here

Regards,

Admin

Sharing EPSON LQ Series Dot Matrix Printer between Windows 7 & Windows XP

 

We got the tip from following link and all we do here is to structure the instructions in a better format

http://windows7forums.com/windows-7-support/39605-printer-driver-network-printer.html

1. Open Devices and Printers

image

2. Select add printer near the top menu bar

3. Select add local printer

image

4. Choose Create a new port and local port from drop down arrow Click Next

image

5. In the enter a port name do one of the following

1. \\MachineNameHostingPrinter\Printer’sShareName or

2. \\ipaddressOfMachineHostingPrinter\Printer’sShareName

image

6. In the install printer driver dialog box do one of the following

image

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!

Regards,

Admin

Windows Live Writer (on Windows 7)

 

* 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

 

Regards,

Admin