Implementing Zoom

In the custom.pll, modify the below given function and procedure:

function zoom_available return boolean is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (form_name = 'POXPOEPO' and block_name = 'PO_LINES')
return TRUE;
else
return FALSE;
end if;
end zoom_available;


procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
param_to_pass1 VARCHAR2(10) := ' ';
param_to_pass2 VARCHAR2(10) := ' ';
Begin
if (event_name = 'ZOOM') then
if ((form_name = 'POXPOEPO' and block_name = 'PO_LINES')) then
FND_PROFILE.GET('MFG_ORGANIZATION_ID',param_to_pass1);
param_to_pass2 := name_in('PO_LINES.item_id');
fnd_function.execute
(
FUNCTION_NAME=>'XXONHAND',
OPEN_FLAG=>'Y',
SESSION_FLAG=>'Y',
OTHER_PARAMS=>'ORGANIZATION_ID="'||param_to_pass1||'"INVENTORY_ITEM_ID="'||param_to_pass2||'"'
);
end if;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
END;


Attach the form function 'XXONHAND' to the menu which is attached to the responsibility to which
we want the zoom function to be available.

Once this is done. we can see the zoom functionality on the form_name = 'POXPOEPO' and block_name = 'PO_LINES'


In the custom form XXONHAND, create a datablock using a view named, MTL_ONHAND_SUB_V
In the form, add two parameters: INVENTORY_ITEM_ID and ORGANIZATION_ID
and in the where clause of the datablock put the below string:
WHERE (:parameter.INVENTORY_ITEM_ID = MTL_ONHAND_SUB_V.INVENTORY_ITEM_ID)

By this we will be able to see the onhand availability of the item on the XXONHAND form.

Cheers...
Khwaja Hassan

Hierarchical Query Example

Hierarchical query:
This query lists the employee_details with their manager in hierarchical manner from employees table.

SELECT a.employee_id, a.first_name, a.last_name, a.manager_id,
b.first_name mgr_first_name, b.last_name mgr_last_name
FROM
(
select employee_id, first_name, last_name, manager_id
from employees
start with employee_id = 100
connect by prior employee_id = manager_id
) a,
employees b
WHERE a.manager_id = b.employee_id(+)


Regards,
Khwaja

Top-n and nth Least query

Top-n query: Query to find the nth highest salary and the
associated employees from employees table

select a.last_name,a.salary
from employees a
where n = (select count(distinct(b.salary))
from employees b
where a.salary <= b.salary)

nth least query: Query to find the nth least or say 2nd least salary and
the associated employees from employees table

select a.last_name,a.salary
from employees a
where n = (select count(distinct(b.salary))
from employees b
where a.salary >= b.salary)