Useful SQL Queries - Oracle EBS

Query to find Runtime, Status and Argument of a Concurrent Request

  SELECT fcp.user_concurrent_program_name, fcr.requested_by, fcr.request_id,

         fcr.request_date, fcr.phase_code, fcr.status_code,fcr.requested_start_date,

          fcr.actual_start_date,fcr.actual_completion_date,

         ROUND((nvl(fcr.actual_completion_date,sysdate) -fcr.actual_start_date) * 1440, 2)  

         "Runtime (in Minutes)"  ,argument_text

    FROM fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp

   WHERE fcr.concurrent_program_id = fcp.concurrent_program_id

     AND fcp.user_concurrent_program_name LIKE ('%Accrual Detail Report%')

     AND fcr.requested_by='12345' --User id

ORDER BY fcr.request_date DESC

 

Query to find Responsibility/Request group attached to a Concurrent Program

SELECT DISTINCT fcpl.user_concurrent_program_name,

       fcp.concurrent_program_name,fapp.application_name,

       frg.request_group_name,fnrtl.responsibility_name

  FROM apps.fnd_request_groups frg,

       apps.fnd_application_tl fapp,

       apps.fnd_request_group_units frgu,

       apps.fnd_concurrent_programs fcp,

       apps.fnd_concurrent_programs_tl fcpl,

       apps.fnd_responsibility fnr,

       apps.fnd_responsibility_tl fnrtl

 WHERE frg.application_id = fapp.application_id

   AND frg.application_id = frgu.application_id

   AND frg.request_group_id = frgu.request_group_id

   AND frg.request_group_id = fnr.request_group_id

   AND frg.application_id = fnr.application_id

   AND fnr.responsibility_id = fnrtl.responsibility_id

   AND frgu.request_unit_id = fcp.concurrent_program_id

   AND frgu.unit_application_id = fcp.application_id

   AND fcp.concurrent_program_id = fcpl.concurrent_program_id

   AND fcp.concurrent_program_name LIKE 'XX%'

   AND fapp.application_name IN 'Receivables'

   AND fnrtl.LANGUAGE = 'US'

   AND fapp.LANGUAGE = 'US'

 

Query to find Responsibility attached to a Form

 

SELECT fff.function_name, frv.responsibility_name

  FROM fnd_responsibility_vl frv, fnd_form_functions fff

 WHERE fff.function_name LIKE 'XX%'      --Form Function Name

   AND frv.menu_id IN (SELECT me.menu_id

                         FROM fnd_menu_entries me

                   START WITH me.function_id = fff.function_id

             CONNECT BY PRIOR me.menu_id = me.sub_menu_id)

 

Excel Template for BI Publisher Report

This blog explains the process and steps to develop BI data templates with excel.
 

Steps in Brief:

1. Create xml data template.
2. Create concurrent program with executable XDODTEXE.
3. Create Data definition and load the data template.
4. Obtain XML data from your data model.
5. Design layout in excel by using XML data.
6. Create the data template and load the designed excel template.
7. Submit your concurrent program and check the excel output.

About Data Template

A data template is a simple xml file with a defined number of elements.  (Refer the below Image 0.1 for sample data template structure.)

A basic data template contains the data Template element where you define the name of your template and a description.

The next element set is the parameters section. In this section you define the names, data types and possible default values for parameters used in your query. You define these parameters later also in the concurrent program definition.

The data Query block contains the sql Statement block where you define the SQL query (in conjunction with any parameters). Optionally you are able to run some trigger at a stage of the data generation (quite handy sometimes :-)).

The data Structure block defines the output columns of the data. When defining the xml file always make sure you close every element block.
 

Data Template Structure

     Image 0.1

Once the data template is prepared save it with .xml format (ex:  BI_excel_demo2.xml).

Concurrent Program

Create the concurrent program with executable XDODTEXE.

What is XDODTEXE?

XDODTEXE is java concurrent program used as a BI Publisher Data template Executable.

The main purpose of this executable is to identify data template file (.xml) and execute the data template to generate the raw (output) xml data, which is later used by BI Publisher formatting engine to format as per the layout (RTF, PDF, etc).

 This executable will be used by all the BI Publisher reports (Concurrent Program) which are using Data Template to generate the xml data.

  

Data Definition

Data definition code should be same as your concurrent program short name. Load the data template xml file by clicking on the upload option next to the Data template.

 

About Excel Templates

An Excel template is a report layout designed in Microsoft Excel for formatting your enterprise reporting data in Excel spreadsheets. Excel templates provide a set of special features for mapping data to worksheets and for performing additional processing to control how the data is output to excel workbooks.

The BI Publisher Tab in Excel

Features of Excel Templates

With Excel templates you can:

• Define the structure for the data in Excel output
• Split hierarchical data across multiple sheets and dynamically name the sheets
• Create sheets of data that have master-detail relationships
• Use native XSL functions in the data to manipulate it prior to rendering
• Use native Excel functionality

Steps to Develop Excel templates

1.     Obtain sample XML data from your data model.
2.     Open the BlankExcelTemplate.xls file and save as your template name.
3.     Design the layout in Excel.
4.     Assign the BI Publisher defined names.
5.     Prepare the XDO_METADATA sheet.
6.     Test the template using the desktop Excel Template Builder.

Creating the XDO_METADATA Sheet

Each Excel template requires a sheet within the template workbook called "XDO_METADATA". Use this sheet to identify your template to BI Publisher as an Excel template.

This sheet is also used to specify calculations and processing instructions to perform on fields or groups in the template. BI Publisher provides a set of functions to provide specific report features. Other formatting and calculations can be expressed in XSLT.

Create a new sheet in your Excel Workbook and name it "XDO_METADATA".

Create the header section by entering the following variable names in column A, one per row, starting with row 1:

1. Version
2. ARU-dbdrv
3. Extractor Version
4. Template Code
5. Template Type
6. Preprocess XSLT File
7. Last Modified Date
8. Last Modified By

• Skip a row and enter "Data Constraints" in column A of row 10.
• In the header region, for the variable "Template Type" enter the value: TYPE_EXCEL_TEMPLATE

Design the excel layout using xml generated tags. See below for example.

Data Template

Create the data template and load the excel layout.

 

Finally submit your concurrent program and you should able to see the excel output file as below.

 

Min-Max Planning Method in Oracle Apps - Planning Level: Organization, Source Type: Inventory, Item: Buy

Planning Level: Organization, Source Type: Inventory, Item: Buy

Responsibility: Inventory          Navigation: Items > Master Items.

Create a item in Item Master and assign it to Source and Destination Organizations.

Change organization to destination organization and enter the List price to item in "Purchasing" TAB,

In "General Planning" TAB, set the Planning method as "Min-Max" and enter the Min-Max quantities and select the source type as "Inventory" (D1 Singapore Distribution Center) from where the items will be procured.

Now run the Min-max report by selecting the Planning level as “Organization” for your Item by changing Restock as YES.

Now find the reorder quantity as per the Min-max report.

 

Then run the "Requisition Import" in Purchasing module. An Approved “Internal Requisition” will be generated. Then, Complete the IR-ISO Cycle to do the replenishment.