Main Differences Between OTBI and BIP Reports - Oracle Fusion

BIP Reports are less user-friendly as they require technical expertise to develop, whereas OTBI Reports are more user-friendly and do not require technical expertise. OTBI reports can be developed with just drag-and-drop functionality.

A BIP Report is a combination of two objects: Data Model and Report, whereas an OTBI Report consists of only one object: Analysis.

For BIP Reports, we need to write SQL queries to extract data from Oracle Fusion. In contrast, for OTBI Reports, there is no need to write SQL queries. Users can simply select the columns they want to include in the report using drag-and-drop functionality.

BIP Reports are better suited for complex reports where data must be extracted from multiple tables, while OTBI Reports are suitable for simpler data scenarios involving one or two tables.

In BIP Reports, there is greater flexibility in terms of data extraction, as SQL queries can be written to meet specific requirements. In OTBI Reports, there is limited flexibility, as data can only be extracted from predefined OTBI data sources, and no modifications can be made to data extraction.

In OTBI Reports, SQL queries are generated automatically based on column selection for data extraction. In contrast, for BIP Reports, SQL statements must be written manually as per the requirements.

OTBI Reports are more focused on simple data extraction, while BIP Reports are robust tools that allow for flexible report layout design based on user requirements.

In OTBI Reports, complex report layouts cannot be designed; they are limited to table-based layouts. However, BIP Reports allow the design of any report layout as per requirements.

Statuary reports cannot be designed in OTBI Reports. For statuary reports, the BIP Reports tool must be used.

From a user perspective, the GUI of BIP Reports is much better compared to OTBI Reports.

OTBI Reports can be created very quickly, whereas BIP Reports may take more time for development.

BIP Reports support the feature of report bursting, but OTBI Reports do not.

In BIP Reports, multiple templates can be designed, whereas OTBI Reports support only a single template layout.

OTBI Reports are tied to data roles assigned to the user account, meaning access to data is governed by these roles in Oracle Fusion. In BIP Reports, data access is not tied to user-assigned data roles, allowing users to access data irrespective of their assigned roles.

In terms of security, OTBI Reports are more secure compared to BIP Reports.

 

Oracle SQL + How to split a string in Oracle using substr/instr

 Table Name: GLCC

Column Name: criteria

Column Value: ALAM-**-98000-10301990-***-***************-*****-OPR**

SELECT

   criteria,INSTR(criteria,'-',1,1) AS first_comma,

   INSTR(criteria,'-',1,2) AS second_comma,

   INSTR(criteria,'-',1,3) AS third_comma

   ,INSTR(criteria,'-',1,4) AS fourth_comma,

   INSTR(criteria,'-',1,5) AS fifth_comma,

   SUBSTR(criteria,1,INSTR(criteria,'-',1,1)-1) column1

   ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,1) + 1

             ,INSTR(criteria,'-',1,2)

              - INSTR(criteria,'-',1,1)

              - 1)

       AS column2

         ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,2) + 1

             ,INSTR(criteria,'-',1,3)

              - INSTR(criteria,'-',1,2)

              - 1)

       AS column3       

                ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,3) + 1

             ,INSTR(criteria,'-',1,4)

              - INSTR(criteria,'-',1,3)

              - 1)

       AS column4  

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,4) + 1

             ,INSTR(criteria,'-',1,5)

              - INSTR(criteria,'-',1,4)

              - 1)

       AS column5 

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,5) + 1

             ,INSTR(criteria,'-',1,6)

              - INSTR(criteria,'-',1,5)

              - 1)

       AS column6

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,6) + 1

             ,INSTR(criteria,'-',1,7)

              - INSTR(criteria,'-',1,6)

              - 1)

       AS column7

             ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,7) + 1)

       AS column8

              FROM

    glcc;  

Oracle Fusion + Query to fetch Inventory On-hand Available to Reserve quantity

 select 

  NVL(TR_QTY, 0) - (

    NVL(TR_QTY_B, 0) + NVL(TR_QTY_C, 0)

  ) res_qty 

from 

  (

    SELECT 

      (

        SELECT 

          SUM(transaction_quantity) 

        FROM 

          INV_ONHAND_QUANTITIES_DETAIL 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348 

          AND subinventory_code IN (

            SELECT 

              SECONDARY_INVENTORY_NAME 

            FROM 

              INV_SECONDARY_INVENTORIES 

            WHERE 

              organization_id = 300000002138348 

              AND reservable_type = '2'

          )

      ) AS TR_QTY_C, 

      (

        SELECT 

          SUM(RESERVATION_QUANTITY) 

        FROM 

          INV_RESERVATIONS 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348

      ) AS TR_QTY_B, 

      (

        SELECT 

          SUM(transaction_quantity) 

        FROM 

          INV_ONHAND_QUANTITIES_DETAIL 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348

      ) AS TR_QTY 

    from 

      egp_system_items_b MB1 

    where 

      1 = 1 

      AND (

        mb1.organization_id = 300000002138348

      ) 

      AND (

        mb1.inventory_item_id = 100000000615408

      )

  );

How to find password of a User in Oracle Apps R12

--Package Specification

CREATE OR REPLACE PACKAGE get_pwd

AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

--Package Body

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;

/

Sample Script to Copy Responsibilities of one user account to another user account using API

fnd_user_pkg.addresp is an Oracle standard API to add responsibilities to a user account.

Below is the script to copy responsibilities of user “INTG_USER” to “IMPL_USER” 

DECLARE

  --
  resp_count NUMBER := 0;
  --
  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'INTG_USER'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
      -- AND furga.end_date IS NULL OR trunc(furga.end_date) > trunc(SYSDATE)
    AND furga.end_date IS NULL;
  --
  --
BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => 'IMPL_USER',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
      --
      resp_count := resp_count + 1;
      --
    EXCEPTION
    WHEN OTHERS THEN
      --
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
      --
    END;
  END LOOP;
  --
  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );
  --
  COMMIT;
END;

Thanks,

Amar

Oracle Apps + SQL Query to Get Oracle Menus, Sub Menus and Functions

 1. Query below will give tree structure as per menu_id, but I need to get the backend User Function Name as well (which was missing).

SELECT * FROM 
      (SELECT  menu_id, sub_menu_id, function_id,(select user_menu_name 
from fnd_menus_tl
where menu_id=fme.menu_id
AND language='US') menu_name,(select user_menu_name 
from fnd_menus_tl
where menu_id=fme.sub_menu_id
AND language='US') sub_menu,(select USER_FUNCTION_NAME from fnd_form_functions_vl 
where function_id=fme.function_id) fucntion_name,
         LPAD (' ', (LEVEL - 1) * 2)|| prompt prompt, entry_sequence
                FROM fnd_menu_entries_vl fme
                WHERE prompt IS NOT NULL
                CONNECT BY PRIOR sub_menu_id = menu_id
                START WITH menu_id = 79980
              AND menu_id =  79980
              AND prompt IS NOT NULL
              AND grant_flag = 'Y'
              ORDER BY entry_sequence) a
     CONNECT BY PRIOR sub_menu_id = menu_id
     START WITH menu_id =  79980  /*like INV_NAVIGATE*/
     AND menu_id =  79980
     AND prompt IS NOT NULL;

2).

SELECT LPAD(' ', 6*(LEVEL-1)) || menu_entry.entry_sequence sequence 
, LPAD(' ', 6*(LEVEL-1)) || menu.user_menu_name SubMenu_Descrition 
, LPAD(' ', 6*(LEVEL-1)) || func.user_function_name Function_Description 
, menu.menu_id 
, func.function_id 
, menu_entry.grant_flag Grant_Flag 
, DECODE( menu_entry.sub_menu_id 
, null, 'FUNCTION' 
, DECODE( menu_entry.function_id 
, null, 'SUBMENU' 
, 'BOTH') 
) Type 
FROM fnd_menu_entries menu_entry,
fnd_menus_tl menu,
fnd_form_functions_tl func
WHERE menu_entry.sub_menu_id = menu.menu_id(+)
AND menu_entry.function_id = func.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 = '&Parent_Menu_User_Name') 
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER SIBLINGS BY menu_entry.entry_sequence

3).

SELECT * FROM
     (SELECT  menu_id, sub_menu_id, function_id,
        ,(select function_name from fnd_form_functions_vl where function_id= fme.function_id) func,
        LPAD (' ', (LEVEL - 1) * 2)|| prompt prompt, entry_sequence
               FROM fnd_menu_entries_vl fme
               WHERE prompt IS NOT NULL
               CONNECT BY PRIOR sub_menu_id = menu_id
               START WITH menu_id = 67605
             AND menu_id =  67605
             AND prompt IS NOT NULL
             AND grant_flag = 'Y'
             ORDER BY entry_sequence) a
    CONNECT BY PRIOR sub_menu_id = menu_id
    START WITH menu_id =  67605  /*like INV_NAVIGATE*/
    AND menu_id =  67605
    AND prompt IS NOT NULL;

Query to fetch Purchase Receipt and its related Purchase order, Vendor details in Oracle Apps R12

 Key Tables:

RCV_TRANSACTIONS

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

PO_DISTRIBUTIONS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

PO_HEADERS_ALL

AP_SUPPLIERS

AP_SUPPLIER_SITES_ALL

Query:

SELECT hou.name ou_name, ph.segment1 po_num, ph.po_header_id, pol.po_line_id, pol.line_num, order_type.displayed_field line_type, COALESCE(pd.quantity_ordered,pll.quantity,pol.quantity) quantity, (pol.unit_price * (pd.quantity_ordered - pd.quantity_cancelled)) line_amount, rsh.receipt_source_code, flv_ship_source.meaning receipt_source_code, papf.full_name received_by, rsh.vendor_id, aps.vendor_name, aps.segment1 vendor_number, assa.vendor_site_id, assa.vendor_site_code, ph.org_id, rsh.shipment_num, rsh.receipt_num, rsh.ship_to_location_id, hl.location_code deliver_to, rsl.item_description, rsl.shipment_line_id, rsl.quantity_shipped, rsl.quantity_received, rsl.unit_of_measure, rsl.vendor_item_num, flv_shipment.meaning shipment_line_status_code, flv_inspection.meaning inspection_status_code, rsl.shipment_line_status_code, rct.inspection_status_code, flv_tran_type.meaning transaction_type, rct.transaction_type transaction_type_lookup_code, rct.transaction_id, nvl(rct.source_doc_quantity,0) transaction_qty, rct.transaction_date, assa.attribute1 vendor_global_code, assa.attribute2 entity_supplier_code, assa.attribute3 vendor_communication_language, pd.attribute1 company_specific_gl_code, pol.attribute2 sanction_number, pol.attribute3 inspection_required, pol.attribute4 end_user_details, rsh.attribute1 Invoice_number, rct.comments gre_comments, rsh.attribute2 exchange_rate_information, rsh.attribute3 gst_invoice_amt, rsh.attribute4 gst_exchange_rate, rsh.attribute5 custom_form_no, rct.attribute1 receipt_line_level_tax_rate from rcv_transactions rct, rcv_shipment_headers rsh, per_all_people_f papf, rcv_shipment_lines rsl, po_distributions_all pd, po_lines_all pol, po_line_locations_all pll, po_headers_all ph, ap_suppliers aps, ap_supplier_sites_all assa, hr_all_organization_units hou, hr_locations hl, fnd_lookup_values flv_shipment, fnd_lookup_values flv_inspection, fnd_lookup_values flv_tran_type, fnd_lookup_values flv_ship_source, po_lookup_codes order_type, po_line_types_b plt WHERE 1 = 1 and rct.po_header_id = ph.po_header_id and rct.po_line_location_id = pll.line_location_id AND rct.po_line_id = pol.po_line_id AND pol.po_line_id = pd.po_line_id and rct.shipment_line_id = rsl.shipment_line_id and rsl.shipment_header_id = rsh.shipment_header_id AND rct.po_distribution_id = pd.po_distribution_id AND rct.employee_id = papf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date and rsh.vendor_id = aps.vendor_id and ph.vendor_site_id = assa.vendor_site_id AND hou.organization_id = ph.org_id AND rct.deliver_to_location_id = hl.location_id AND rsl.shipment_line_status_code = flv_shipment.lookup_code AND flv_shipment.lookup_type = 'SHIPMENT LINE STATUS' AND flv_shipment.language = USERENV('LANG') AND rct.inspection_status_code = flv_inspection.lookup_code AND flv_inspection.lookup_type = 'INSPECTION STATUS' AND flv_inspection.language = USERENV('LANG') AND rct.transaction_type = flv_tran_type.lookup_code AND flv_tran_type.lookup_type = 'RCV TRANSACTION TYPE' AND flv_tran_type.language = USERENV('LANG') AND flv_ship_source.lookup_type = 'SHIPMENT SOURCE TYPE' AND flv_ship_source.language = USERENV('LANG') AND flv_ship_source.lookup_code = rsh.receipt_source_code AND order_type.lookup_type = 'ORDER TYPE' AND order_type.lookup_code = plt.order_type_lookup_code AND pol.line_type_id = plt.line_type_id;

Regards,

Amar Alam