Oracle Apps

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:
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

Technical Details of Image File Attachment in Oracle Applications 11i.

Attaching an image file in Oracle Apps say, you attach an image of an Item.
For this you will click on the attachments icon(Paperclip Icon) of the toolbar in Apps Window and this will take you to Attachment Window.
Here You can specify Category, Description,Datatype.
If you select "File" as datatype, you will be taken to a webpage asking for uploading the image.
Once you upload the image successfully, You can view the image using the "Open Document" button on the "Attachment Window".

This is how you will be uploading the image.

Q. Now where did the image go and how can you access it??
A. When you attach a file say, "A380.jpg", you can find the document_id of that image using
query:

SELECT document_id
FROM fnd_documents_tl
WHERE file_name = 'A380.jpg'
output: document_id = 11807

Use this document_id to get the media_id of the file. Media_id is the actual file_id.
SELECT media_id
FROM fnd_documents_tl
WHERE document_id = 11807
output: media_id = 318949

Use the media_id in below query to get the file details.
SELECT file_id, file_name,file_content_type,file_data
FROM fnd_lobs
WHERE file_id = 318949

In this table, "FND_LOBS" you can find the BLOB column, "FILE_DATA" which stores the actual file.

If you want to show this file on a webpage, ie. if the user wants to see the image of the item
from a custom form for items,he can click a button on the form which will take him to a webpage
where he will be able to see the image.

For this, you can use the below query to get the url for the image:

SELECT fnd_gfm.construct_download_URL(fnd_web_config.gfm_agent,318929)
FROM dual

You can use this url say, "l_url" in fnd_utilities.open_url to open the webpage containing the image.
fnd_utilities.open_url (l_url)

If you want to show the image on the form itself then we need to add the file_data column of fnd_lobs table to the datablock in the form. and use it for displaying in the Form.

Cheers,
Khwaja

Returning a Table from a PL/SQL function
1. Create an object type for the record

create or replace type emp_rec_t as object
(
employee_id number,
first_name varchar2(25),
salary number,
manager_id number,
department_id number
);

2. create a SQL table type object
create or replace type emp_tab as table of emp_rec_t;

3. Define a function in the following way.
create or replace
function demo_proc( p_start_row in number,
p_end_row in number )
return emp_tab
as
l_data emp_tab := emp_tab();
l_cnt number default 0;
begin
for x in ( select * from employee order by salary desc )
loop
l_cnt := l_cnt + 1;
if ( l_cnt >= p_start_row )
then
l_data.extend;
l_data(l_data.count) :=
emp_rec_t( x.employee_id,
x.first_name,
x.salary,
x.manager_id,
x.department_id);
end if;
exit when l_cnt = p_end_row;
end loop;

return l_data;
end;

4. you can query the data returned from the function as follows:
select * from the( select cast(demo_proc(2,6) as emp_tab ) from dual ) a;

Using External Table feature of Oracle 10g on Windows:

External Tables are used for dataloading to oracle 10g Database.

1. Create a Directory C:\wkdir

2. connect to sqlplus as system or sysdba

3. Execute the below statement
create or replace directory ext_tab_dir as 'c:\wkdir';
grant read,write on directory ext_tab_dir to scott;

Here scott is the schema from where you will be creating external table
and loading the data to it from the flat file located in 'c:\wkdir' directory.

4. disconnect from sqlplus

5. connect to sqlplus as scott

6. execute the below statements

drop table order_items_ext; --If this table already exists with some other columns.

create table order_items_ext
( order_id number(12)
, line_id NUMBER(3)
, product_id number(6)
, unit_price number(8,2)
, quantity number(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('order_items.dat')
)
REJECT LIMIT UNLIMITED;

for our testing we can create order_items.dat file with below records:
123,12,465,14.50,2
124,13,466,15.50,5

The file order_items.dat should be placed in c:\wkdir directory.

7. You will alter the table to set the PROJECT COLUMN attribute to ALL. This is the default.
Then you can query the external table. Execute the following command:

alter table order_items_ext project column all;
select count(order_id)from order_items_ext;
select * from order_items_ext;

8. If you find some records missing then you can check the below files in the 'c:\wkdir'
ORDER_ITEMS_EXT_#####.log
ORDER_ITEMS_EXT_#####.bad

Cheers,

Khwaja