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