Oracle EBS + Query to Find List of XML Publisher Reports

 SELECT fe.executable_name,'Data Template (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'K'

AND fe.executable_name = 'XDODTEXE'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%')

UNION

SELECT fe.executable_name,'PLSQL (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'I'

AND fcp.OUTPUT_FILE_TYPE = 'XML'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%')

UNION

SELECT fe.executable_name,'RDF (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'P'

AND fcp.OUTPUT_FILE_TYPE = 'XML'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%');

Regards,

Amar Alam

Oracle Fusion + Sales Order Audit Report Query + Sales Order Fulfillment Lines EFF Query

 SELECT * FROM 

(SELECT --dla.line_id,

       --dha.header_id,

   dha.order_number "Order Number",

       hp.party_name "customer",

       hp.country "country",

   (NVL(dla.ordered_qty,0) * NVL(dla.unit_selling_price,0)) amount,

  (SELECT attribute_char3

   FROM doo_headers_eff_b dheb

   WHERE dheb.header_id = dha.header_id

     AND CONTEXT_CODE='Subscription')  region,

       Revenue_Management.attribute_char8 "workday contract name",

       Subscription.attribute_char1 "billing_offset",

       ACCOUNTS_RECEIVABLES.attribute_char1 "invoice group by number",

       ACCOUNTS_RECEIVABLES.attribute_char2 "invoice group by description",

       ACCOUNTS_RECEIVABLES.attribute_char3 "management group",

        SF_SUBSCRIPTION.ATTRIBUTE_CHAR10 "align billing from",

            SF_SUBSCRIPTION.attribute_char11 "custom billing",

                 Conversion.ATTRIBUTE_CHAR4 "Misc info",

                 Conversion.attribute_char2 "PS billed amount",

                 Conversion.attribute_char3 "PS unbilled amount",

                 Conversion.attribute_char5 "supplier purchase order",

                 TO_CHAR(SNOW.attribute_timestamp1,'MM/DD/YY HH:MI') "cloud provision date",

                 TO_CHAR(SNOW.attribute_timestamp2,'MM/DD/YY HH:MI') "on-perm provision date",

                 IB.attribute_char2 "asset line identifer",

                 IB.attribute_char3 "life cycle pid ",

                 SFDC.attribute_char1 "PO requied",

 SFDC.attribute_char2 "PO Number",

 SFDC.attribute_char3 "PO Line Number",

                 SFDC.attribute_char4 "invoice trigger event",

                 SFDC.attribute_char5 "site id",

                 SFDC.attribute_char6 "bundle id",

                 SFDC.attribute_char7 "advanced billing flag",

                 SFDC.attribute_char10 "used inventory flag",

                 SFDC.attribute_char11 "GSS validation flag",

                 SFDC.attribute_char16 "suppress fulfillment",

                 Procurement.attribute_char1 "cabinet position",

                 Procurement.attribute_char2 "PO vendor cost/transfer price",

                 Procurement.attribute_char3 "related manual order",

                 Tax.attribute_char2 "0% tax  rate flag",

                 BRAZIL.attribute_char1 "CFOP-line order",

                 CSP.attribute_char1 "CSP flag",

                 CSP.attribute_char3 "private order flag",

     esib.item_number,

 esib.description item_description,

 bill_to_party.party_name bill_to_customer,

 ship_to_party.party_name ship_to_customer,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR5 sf_subscription_number,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR2  "SF Line Identifier (Model)",

 revenue_management.attribute_char6 "Rev Contract Grouping" ,

 (SELECT header_curr_duration_ext_amt

 FROM doo_order_pricing_details_v dopdv

 WHERE dopdv.fulfill_line_id = dfla.fulfill_line_id

                 AND dopdv.PRICE_ELEMENT_CODE='QP_NET_PRICE'

                 AND dopdv.ROLLUP_FLAG ='Y' ) Total_Amount_for_Duration

FROM doo_headers_all dha,

     doo_lines_all dla,

     doo_fulfill_lines_all dfla,

     doo_fulfill_lines_eff_b ACCOUNTS_RECEIVABLES,

     doo_fulfill_lines_eff_b Revenue_Management,

     doo_fulfill_lines_eff_b Subscription,

     doo_fulfill_lines_eff_b CONVERSION,

     doo_fulfill_lines_eff_b SFDC,

 doo_fulfill_lines_eff_b SNOW,

     doo_fulfill_lines_eff_b IB,

     doo_fulfill_lines_eff_b Procurement,

     doo_fulfill_lines_eff_b Tax,

     doo_fulfill_lines_eff_b BRAZIL,

     doo_fulfill_lines_eff_b CSP,

 doo_fulfill_lines_eff_b SF_SUBSCRIPTION,

     hz_parties hp,

 egp_system_items esib,

     doo_order_addresses bill_to,

 doo_order_addresses ship_to,

 hz_cust_accounts bill_to_cust,

 hz_parties bill_to_party,

 hz_parties ship_to_party

WHERE 1=1

  ---AND dha.order_number='00026264'

  AND dha.header_id=dla.header_id

  AND dla.line_id= dfla.line_id

  AND dfla.fulfill_line_id = accounts_receivables.fulfill_line_id (+)

  AND accounts_receivables.context_code (+)='ACCOUNTS RECEIVABLES'

  AND dfla.fulfill_line_id = Revenue_Management.fulfill_line_id (+)

  AND Revenue_Management.context_code (+)='Revenue_Management_Information_Line'

  AND dfla.fulfill_line_id = Subscription.fulfill_line_id (+)

  AND Subscription.context_code (+)='Subscription'

  AND dfla.fulfill_line_id = Conversion.fulfill_line_id (+)

  AND Conversion.context_code (+)='Conversion'

  AND dfla.fulfill_line_id = SFDC.fulfill_line_id (+)

  AND SFDC.context_code (+)='SFDC'

  AND dfla.fulfill_line_id = SNOW.fulfill_line_id (+)

  AND SNOW.context_code (+)='SNOW'

  AND dfla.fulfill_line_id = IB.fulfill_line_id (+)

  AND IB.context_code (+)='IB - Asset Line Identifier Model'

  AND dfla.fulfill_line_id = Procurement.fulfill_line_id (+)

  AND Procurement.context_code (+)='Procurement'

  AND dfla.fulfill_line_id = Tax.fulfill_line_id (+)

  AND Tax.context_code (+)='Tax'

  AND dfla.fulfill_line_id = BRAZIL.fulfill_line_id (+)

  AND BRAZIL.context_code (+)='BRAZIL-Localization'

  AND dfla.fulfill_line_id = CSP.fulfill_line_id (+)

  AND CSP.context_code (+)='CSP Details'

  AND dfla.fulfill_line_id = SF_SUBSCRIPTION.fulfill_line_id (+)

  AND SF_SUBSCRIPTION.context_code (+)='SF Subscription - SF Line Identifier Model'

  AND dha.sold_to_party_id = hp.party_id 

  AND dla.inventory_organization_id =esib.organization_id

  and dla.inventory_item_id = esib.inventory_item_id

  AND dha.header_id = bill_to.header_id (+)

  AND bill_to.address_use_type (+)= 'BILL_TO'

  AND bill_to.cust_acct_id = bill_to_cust.cust_account_id (+)

  AND bill_to_cust.party_id = bill_to_party.party_id

  AND dha.header_id = ship_to.header_id (+)

  AND ship_to.address_use_type (+) = 'SHIP_TO'

  AND ship_to.party_id = ship_to_party.party_id (+)

 ) 

  WHERE region = NVL(:p_region,region);

Regards,

Amar Alam

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