Discoverer Report Registration in Oracle Applications

The following are the steps,to register a discoverer report in Oracle Apps. Make sure you followed each and every step correctly.

1. Login to Application Developer or System Adminstrator -->Function

2. Enter Function , User Function Name, Description(Optional) in Description Tab.

E.g
Function: UNPAID_INVOICES
User Function Name: Supplier Invoices Validated But not unpaid.
Description: Give meaningful meaning.

In Properties Tab:

Select Type “SSWA jsp function”
Maintenance Mode Support – None
Context Dependence - Responsibility


4. In Form Tab  Parameters --> Enter the Workbook name created previously

mode=DISCO&workbook= Supplier Invoices Validated But Unpaid Report(workbook name you created in discoverer desktop)

E.g
workbook=TEST_WORKBOOK
In case you want to give some parameters then you can use:
workbook=<workbook_name>&parameters=<Parameter_nam e1>~<value1>*<Parameter_name2>~<value2>*<Parameter _name3>~<value3>*Example
workbook=<workbook_name>&Parameters=age~26*salary~ 1000*

5. In Web HTML Tab

Enter HTML Call - OracleOasis.jsp

6. Now you can add this Function in any existing or new Menu

Also Note that Discoverer Report cannot be invoked when you’ve direct login into Oracle Applications through http://<url>:<port number>/dev60cgi/f60cgi
You need to access through logging in from the browser web page. Otherwise it will give an Error.

7. Now navigate to Responsibility which contain the Menu to which you added this function.

8. Click the Function Name to Display the report.

Challa.

Solution for Function not available to this responsibility.

If you get Function not available to this responsibility error while trying to use diagnostics -> Examine.Try the below steps as follows.

1. Login to Oracle Application and choose the System Administrator responsibility.
2. Navigate to Profile -> System.
3. Search on %diagnostic%
4. Select 'Utilities: Diagnostics'.
5. Set profile to 'Yes'.

challa.

Submiting XMLP Report From Backend

DECLARE

    V_REQUEST_ID NUMBER;
    l_boolean    BOOLEAN;
    l_boolean1  BOOLEAN;
BEGIN
    l_boolean := FND_REQUEST.ADD_DELIVERY_OPTION
 (TYPE         => 'E',
 -- this one to speciy the delivery option as Email
  p_argument1  => 'Testing the Email option from back end',
 -- subject for the mail
  p_argument2  => 'challa95@gmail.com',
-- from address
  p_argument3  => 'challa95@gmail.com',
-- to address
  p_argument4  => 'challa95@gmail.com',
-- cc address to be specified here.
  nls_language => '' -- language option
               );      
    IF l_boolean = TRUE
    THEN
        FND_GLOBAL.APPS_INITIALIZE(8511,51272,20003);
        l_boolean1 := FND_REQUEST.add_layout  
(template_appl_name   => 'XXAA'
 ,template_code       => 'XXAAPTOSUEMP'
 ,template_language   => 'en'
 ,template_territory  => 'US'
 ,output_format       => 'PDF'
            );                   

        V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST
                           (APPLICATION   => 'XXAA'
                            ,PROGRAM      => 'XXAAPTOSUEMP'
                           );

        COMMIT ;
        DBMS_OUTPUT.PUT_LINE('Request submitted. V_REQUEST_ID = '
|| V_REQUEST_ID);
    END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Request set submission failed - unknown error:' ||SQLERRM);

END;

Challa.

Query To Find Concurrent Program's Parameters and Value Sets

SELECT
fcp.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.srw_param,
ffvs.flex_value_set_name "Value Set Name",
flv.meaning "Default Type",
fdfcuv.DEFAULT_VALUE "Default Value"
FROM
fnd_concurrent_programs_vl fcp,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE 1=1
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
-- AND fcp.user_concurrent_program_name like 'XXAA%Report'
-- AND ffvs.flex_value_set_name LIKE '%JOB%'
ORDER BY
fcp.user_concurrent_program_name
,fdfcuv.column_seq_num;

Query to Extract AOL Menu Hierarchy Query

SELECT   lev "LEVEL", fm.user_menu_name "MASTER_MENU",
         entry_sequence "ENTRY_SEQ", a.prompt "PROMPT",
         fms.user_menu_name "CHILD_MENU",
         ffv.user_function_name "FUNCTION_NAME"
FROM (SELECT     LEVEL lev, fme.menu_id, fme.sub_menu_id, function_id,
                     entry_sequence, PRIOR entry_sequence prior_entry_seq,
                     fme.prompt
                FROM fnd_menu_entries_vl fme
               WHERE fme.grant_flag = 'Y'
          START WITH fme.menu_id = '69914'
          CONNECT BY PRIOR fme.sub_menu_id = fme.menu_id) a,
         fnd_menus_vl fm,
         fnd_menus_vl fms,
         fnd_form_functions_vl ffv
   WHERE a.menu_id = fm.menu_id AND fms.menu_id(+) = a.sub_menu_id
         AND ffv.function_id(+) = a.function_id
ORDER BY lev, prior_entry_seq, entry_sequence;