SQL: Menu hierarchy

SELECT     LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
           LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
           menu_entry.grant_flag grant_flag,
           DECODE (menu_entry.sub_menu_id,
                   NULL, 'FUNCTION',
                   DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
                  ) TYPE,
           menu2.user_menu_name, func2.user_function_name
      FROM fnd_menu_entries_vl menu_entry,
           fnd_menus_tl menu,
           fnd_form_functions_tl func,
           fnd_form_functions_tl func2,
           fnd_menus_tl menu2
     WHERE menu_entry.sub_menu_id = menu.menu_id(+)
       AND menu_entry.function_id = func.function_id(+)
       AND menu_entry.sub_menu_id = menu2.menu_id(+)
       AND menu_entry.function_id = func2.function_id(+)
       --AND grant_flag = 'Y'
START WITH menu_entry.menu_id =
                     (SELECT menu_id
                        FROM fnd_menus_tl menu2
                       WHERE menu2.user_menu_name = 'AR_NAVIGATE_GUI')
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
  ORDER SIBLINGS BY menu_entry.entry_sequence

XMLP: How to Display Leading Zeros in XMLP Report – Excel Output

Microsoft Excel has a tendency to display number format based columns with no prefix of ZEROs. For example, if there is a value ’007′ excel displays it as ’7′, excel display it as ’007′ only if the column is set in text format. You can see the difference in the below screenshot

Please go through the below URL for an example to generate an XMLP report:

In the example mentioned in the above URL you could see the output as shown in the below screenshot

But if you observe the data in the emp table, you could see three rows have leading zeros for employee number(Please note, I have updated the data of seeded emp table with prefix of 00 and also changed the data type of empno column to varchar2(6) for the sake of example.)

As the output is of excel type the empno column is considered as Number column (as the entire column consists of number format data) and the leading zeros are removed in display.

We have multiple ways to resolve this, I have listed them below.

Method 1

1. Open the RTF Template in MS Word.
2. Go to Data -> Load XML Data.

3. Once the data is loaded successfully, double click on that field.

4. Under field properties window set the field formatting type as “Regular Text” and set the check box “Force LTR”.

After following the above said steps, save the template and preview the output which is as shown below

Method 2

Add Ctrl+Shift+Space after/before emplyee number on template to create a non-breaking space. The non-breakable space converts the number column to string column

Disadvantage of this method is that the non-breakable space is visible in the output as well, you can see in the screenshot below

Method 3

This is the best method I have found, In this method we need to use an equal-to symbol before the field and enclose the field in double quotes for example:- =”ENUM”

This works only in excel however it will allow you to cut and paste (ie to use the value to search in Oracle) and also to do vlookups.

The theory is that excel will concatenate the values together because it has quotes around it, it will treat it as a string rather than simply a value.

For any other formats this method will not work.

You can see in the above screenshot that Enum column values are turned to blue color as the content is explicit converted to text format from number format.

Hope this article is useful for those who are in need to show leading zeros for number columns in XMLP reports. If you have any best solution, please leave a comment and share with the readers.

Excel Output From BI Publisher or XML Publisher is Trimming Leading Zeros [ID 417811.1]  0xA0

DB: Generate Trace Files for Concurrent Programs

The main use of enabling trace for a concurrent program comes during performance tuning.

By examining a trace file, we come to know which query/queries is/are taking the longest

time to execute, there by letting us to concentrate on tuning them in order to improve the

overall performance of the program.

The following is an illustration of how to Enable and View a trace file for a Concurrent  Program.

Navigation: Application Developer–>Concurrent–>Program 

Check the Enable Trace Check box. After that go to that particular Responsibility and run the Concurrent Program. 

Check that the Concurrent Program has been completed successfully. 

The trace file by default is post fixed with oracle Process_id which helps us to identify which trace file belongs to which concurrent request. The below SQL Query returns the process_id of the concurrent request:

Select oracle_process_id from fnd_concurrent_requests where request_id=’2768335′

(This query displays Process Id)

The path to the trace file can be found in 2 ways

1.Run the below query in the database: 

1	SELECT * FROM V$PARAMETER WHERE NAME='user_dump_dest'

(This Query displays the path of trace file)

2. Login As sysadmin

Navigation> OAM->OAM Setup->Site MAP->Monitoring->System Configuration-> Database Init.ora Setting.

Check out the value for Parameter user_dump_dest which is nothing but the trace file location.

The Trace File generated will not be in the readable format. We have to use TKPROF utility to convert the file into a readable format.

Run the below tkprof command at the command prompt.

TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

A readable file will be generated from the original trace file which can be further

analyzed to improve the performance. This file has the information about the

parsing, execution and fetch times of various queries used in the program.

Forms: Finding Data Source of Control Blocks

FND: Submitting Oracle Reports/XMLP Reports using Forms Personalisation

Submit a concurrent request from menu item AND EXECUTE PROCEDURE

• IF THE REPORT TYPE IS ORACLE REPORTS

1-create procedure to submit it when I called it

CREATE OR REPLACE procedure APPS.CALL_XXCSD_VEH_CHKIN(XX_INCIDENT_NUMBER IN VARCHAR2,XX_INCIDENT_NUMBER2 IN VARCHAR2,RELEASE IN VARCHAR2) AS

v_request_id VARCHAR2(100) ;

BEGIN

-- First set the environment of the user submitting the request by submitting

-- fnd_global.apps_initialize().

-- The procedure requires three parameters

-- Fnd_Global.apps_initialize(userId,responsibilityId,applicationId)

-- replace the following code with correct value as get from sql above

Fnd_Global.apps_initialize(1134,50019,512);

v_request_id := FND_REQUEST.SUBMIT_REQUEST (

'PO', -- Application short name

'XX_CUSTOM_PURCHASE_ORDER', -- Program short name

'', -- description (Not require)

'', -- start_time (start immediately)

FALSE,-- sub_request (default FALSE)

-- Next is the parameters list of ARACCPB

XX_INCIDENT_NUMBER,

XX_INCIDENT_NUMBER2,

release

);

dbms_output.put_line('Request submitted. ID = ' || v_request_id);

commit ;

exception

when others then

dbms_output.put_line('Request set submission failed - unknown error: ' || sqlerrm);

END;

2-create menu item and call the proc from it

-After selecting execute_procedure

-the argument:

='DECLARE

BEGIN

CALL_XXCSD_VEH_CHKIN('''||${item.INCIDENT_TRACKING.INCIDENT_ID.VALUE}||'''

);

END'

Note:

To get value from item from the canvas using personalization will be like this :

('''||${item.block_name.item_name.VALUE}||''')

• IF THE REPORT TYPE IS XML PUBLISHER (ADD LAYOUT TO IT)

CREATE OR REPLACE procedure APPS.CALL_XXCSD_VEH_CHKIN(XX_INCIDENT_NUMBER IN VARCHAR2) AS

v_request_id VARCHAR2(100) ;

xml_layout boolean;

BEGIN

Fnd_Global.apps_initialize(1134,50019,512);

xml_layout := FND_REQUEST.ADD_LAYOUT('CSD','XXCSD_VEH_CHKIN_T','en','US','PDF');

v_request_id := FND_REQUEST.SUBMIT_REQUEST (

'CSD', -- Application short name

'XXCSD_VEH_CHKIN', -- Program short name

'', -- description (Not require)

'', -- start_time (start immediately)

FALSE,-- sub_request (default FALSE)

-- Next is the parameters list of ARACCPB

'402'

);

dbms_output.put_line('Request submitted. ID = ' || v_request_id);

commit ;

exception

when others then

dbms_output.put_line('Request set submission failed - unknown error: ' || sqlerrm);

END;

******************end**********************

Forms: Hidden methods to Query in Forms

#  between 1 and 90000

# is null

# >=90000

# in (10001,1)

# like 'AB%'

FND: Calling Reports from Menu similar to Forms

The method to call reports from the menu like forms:

1-Create Function
Description Tab

Properties Tab

Form Tab

In parameters Field put:

CONCURRENT_PROGRAM_NAME="FAMCP" PROGRAM_APPL_SHORT_NAME="OFA" SUBMIT_ONCE="N"

Note: FAMCPè is the name of concurrent program short name

-add the function to the specified menu then run it