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)

Reports Using XML Publisher

XML Publisher Concurrent Program - XMLP

Written by Anil Passi -- One of the Author I like. He writes very well.

Friday, 24 November 2006

In this article I have listed a step by step demo of XML Publisher as a Concurrent Program in Oracle Apps. I think it is very important that you get a feel of XML Publisher, as this will become the reporting tool in Fusion [assuming XMLP can become as powerful as Oracle Report in the given time]. Before I explain the steps in details with screenshots, let me brief them:- a. Create a report and register it as Concurrent Program of type XML b. Build a Data Definition & XML Template using XML Publisher. c. Create a relation between XML Template & Concurrent Program. Sounds simple, it is indeed, but devil lies in detail. Hence read the below carefully. I have provided the source code with installation instructions for you to try this. Please note to try this you need 11.5.10 environment. Now lets get into the details….Step by Step….. 1. Create a table xx_emp_demo and insert two records into it. create table xx_managers ( manager_no integer, manager_name varchar2(200) ) ; insert into xx_managers ( manager_no, manager_name ) values ( 1000, 'Anil Passi') ; insert into xx_managers ( manager_no, manager_name ) values ( 1001, 'Martin') ; commit; 2. Next you need a concurrent program that spit out an XML file to the output of the concurrent program. Such concurrent program can be of type SQL or PL/SQL or Oracle Report or any other supportable type, provided it can produce a XML output. Hence in this case, we will develop a Report that has all the columns from table xx_managers Create a plain default Oracle Report using wizard in Oracle Reports 6i. This can be done using wizard with SQL “select * from xx_managers” Above picture shows the report output in Reports 6i preview Do not change anything in DESFORMAT, as Concurrent Manager will take care of passing XML to this. Hence XML Publisher will be fed an XML output by the concurrent manager itself. 3. Add three user parameters P_CONC_REQUEST_ID, P_RESPONSIBILITY_ID, P_USER_ID These parameters will make it possible to run a report as concurrent program. Save the above RDF as XX_MANAGERS.rdf 4. FTP XX_MANAGERS.rdf to $AR_TOP/reports/US Silly it is, but for demo I will use standard Application [forgive me, but I am making it easier for you to run the source code]. 5. Create Concurrent program executable for XMLP report, as if it is a normal report 6. Create a concurrent program for the executable. Note that I have kept the shortname and the program name both as XX_MANAGERS. IMPORTANT:- Note that format is XML 7. Register this program with Receivables request group. DECLARE BEGIN FND_PROGRAM.add_to_group ( PROGRAM_SHORT_NAME =>'XX_MANAGERS' ,PROGRAM_APPLICATION =>'AR' ,REQUEST_GROUP => 'Receivables All' ,GROUP_APPLICATION =>'AR' ) ; commit; exception when others then dbms_output.put_line('Object already exists'); END ; / 8. Run the report and you will see an xml output appearing. Save the output as XX_MANAGERS_241106.xml on your computer. You will need to use menu /Tool/Copy to open this XML output in browser, and then save it as XX_MANAGERS_241106.xml on your PC. NOW THE TIME TO DO XML PUBLISHER BIT… 9. Install this XML Publisher Desktop software on your pc, ensuring that you have MS-Word installed(for this demo) The XML Builder Desktop Patch for Word is Patch Number: 4561835 This software can be downloaded from http://updates.oracle.com/download/4561835.html This is a tool given by Oracle[Desktop XML Publisher] that will read any XML file, will create the fields automatically. 10. Open MS Word after installing as per step 9, and you will now see something similar to below in Microsoft Word Now in MS Word, click on “Load XML Data” and select file XX_MANAGERS_241106.xml that was created in Step 8. 11. Now, click on Insert/Table Form And then drag and drop “List G Manager No” within the template section and click on OK. Effectively by doing these steps, you have just created a Layout for the report in XML Publisher. The layout will look like this [as below]. Save this MSWord file as MANAGER_XML_TEMPLATE.rtf By clicking on Preview, you will see the output as below 12. Now, lets do the real bit in attaching the Concurrent Program definition to XML Publisher. Logon to responsibility “XML Publisher Administrator”. Click on “Data Definition” menu as above…so that we can create the XMLs source data Definition. Effectively we are saying that output of the Concurrent program becomes the data definition for XMLP. Lets define the value in below fields Name : XX_MANAGERS Application : Receivables Code : XX_MANAGERS [Note: This is the short name of the Concurrent program] Start Date : Leave it to default Sysdate Click on “Apply” 13. Now lets define a template as below for XML Publisher. For doing so, click on Templates Tab. We need to create a Template for the data-definition of previous step. Name : XX_MANAGERS [this is the name of the template] Code : XX_MANAGERS [short name of conc program] Application : Receivables Data Definition : XX_MANAGERS [the one that we created in prior step 12] Start Date: : Leave this default. Type : RTF Click on browse and select the RTF file[MANAGER_XML_TEMPLATE.rtf] that you had saved in Step 11. xmlp_A_14 Click on Apply, and you will get the below record. 14. Navigate to “Receivables Manager” and submit report XX_MANAGERS. Click on Output button to see the report as pasted below.

Image File Attachment in Oracle 11i.

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.

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;

Lock a folder without any software

Hey Buddies..
Here is a trick to lock the folders without using any software:

• Lock a folder without any software

Suppose you want to lock the folder games in d: which has the path D:\Games.
Follow the below steps:

1. In the same drive create a text file and type
ren games games.{21EC2020-3AEA-1069-A2DD-08002B30309D}
Now save this text file as loc.bat

2. create another text file and type in it
ren games.{21EC2020-3AEA-1069-A2DD-08002B30309D} games
Now save this text file as key.bat

Now you can see 2 batch files loc.bat and key.bat.
Double Click loc.bat and the folder games will change to control panel and you cannot view its contents.
Double Click key and you will get back your original folder.

try it out!!!!!!!

Cheers,
Khwaja
www.khwaja.tk

External Table feature of Oracle 10g on Windows

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