Oracle Fusion: Anonymous blocks (Procedural Call) data model in Oracle BI Publisher (Oracle Fusion)

 BI Publisher supports executing PL/SQL anonymous blocks. You can perform calculations in the PL/SQL block and return the result set. BI Publisher uses callable statements to execute anonymous blocks.

The requirements are:

• The PL/SQL block must return a result set of type REF cursor
• You must declare the out variable with the name, xdo_cursor;. This needs to be the first parameter in position in Data model. 
• If you do not declare the name properly, the first bind variable is treated as an out variable type and binds with REF cursor.
• Declare the data model parameter with name xdo_cursor. This name is reserved for out variable type for procedure/anonymous blocks.

Sample Script:

DECLARE

    TYPE refcursor IS REF CURSOR;

    xdo_cursor refcursor;

BEGIN

    OPEN :xdo_cursor FOR SELECT gl.name

                              , fnd_flex_ext.get_segs('GL', 'GL#', fnd.id_flex_num, gc.code_combination_id) concatenated_segments

                              , decode(nvl(gb.translated_flag, 'X'), 'R',(nvl(gb.begin_balance_dr, 0) - nvl(gb.begin_balance_cr, 0))

                                       , 'X',(nvl(gb.begin_balance_dr_beq, 0) - nvl(gb.begin_balance_cr_beq, 0)), - 99) begin_balance

                              , decode(nvl(gb.translated_flag, 'X'), 'R', 'No', 'X', 'Yes', 'XXX') is_functional_currency

                           FROM gl_ledgers gl

                              , gl_balances gb

                              , gl_code_combinations gc

                              , fnd_id_flex_structures_tl fnd

                          WHERE gc.chart_of_accounts_id = gl.chart_of_accounts_id

                            AND gl.ledger_id = gb.ledger_id

                            AND gc.code_combination_id = gb.code_combination_id

                            AND fnd.id_flex_num = gc.chart_of_accounts_id

                            AND fnd.application_id = 101

                            AND fnd.id_flex_code = 'GL#'

                            AND fnd.language = userenv('LANG')

                            AND gb.period_name = :p_period_name

                            AND gc.segment1 = :p_segment1

                            AND gc.segment3 = :p_segment3

                            AND gl.ledger_id = :p_pedger_id;

END;

Data Model:

Parameter Window:

Sample Output:

How to Enable Audit Trail On Tables

 Example to enable Audit Trail on HZ_PARTIES and HZ_CUST_ACCOUNTS tables:

a. Responsibility: System Administrator
    Navigation:   Profile > System
    Query Profile: 'AuditTrail:Activate'. Click FIND
    Set it to 'Yes' at Site level.

b) Enable Audit Installations (AR)
    Navigation: System Admin > Security > Audit Trail >Install
   Enable Audit Installation for AR

c) Define Audit tables and desired columns.
    Navigation: System Admin > Security > Audit Trail > Tables
   Query for user table name 'HZ_PARTIES' and add columns on which you want to enable trail
   do the same for table HZ_CUST_ACCOUNTS

d) Define an Audit Group and associated tables
    Navigation:  System Admin > Security > Audit Trail >Groups
    Create Audit group for table defined in 'C'

e) Run Concurrent program 'AuditTrail Report for Audit Group Validation' with parameter as your
    Audit Group.

f) Define an Industry Template contain Audit Group
    Navigation:  System Admin >Security > Audit Trail Reporting >Audit Industry Template

g) Run concurrent program "AuditTrail Update Tables".

h) Confirm existence of Audit tables (_A).

i) Run Audit report from SysAdmin menus.
   Navigation: Security > AuditTrai > Audit Trail Reporting > Audit Report
Above request will fire a concurrent request whose output can be used for Audit reporting.


Oracle Metalink Ref ID : 848039.1
Oracle Receivables - Version 11.5.10.0 and later
Oracle Application Object Library - Version 12.1.3 and later

ads.txt

google.com, pub-9147708292038568, DIRECT, f08c47fec0942fa0

Script to Get Application user password from backend

--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;
/

--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
WHERE usr.user_name = :p_user_name;

How to Use an API to Delete An Order Level Modifier From a Sales Order

How to Use an API to Delete An Order Level Modifier From a Sales Order (Doc ID 2191611.1)


SET SERVEROUTPUT ON;

DECLARE
   /* Initialize the proper Context */
   l_org_id                                     NUMBER := 204; --ENTER VALUE
   l_application_id                             NUMBER := 660;
   l_responsibility_id                          NUMBER := 21623; --ENTER VALUE
   l_user_id                                    NUMBER := 1013419; --ENTER VALUE

   /* Initialize the record to G_MISS to enable defaulting */
   l_header_rec                                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
   l_old_header_rec                             oe_order_pub.header_rec_type;

   l_header_adj_tbl                             oe_order_pub.header_adj_tbl_type;

   l_line_tbl                                   oe_order_pub.line_tbl_type;
   l_old_line_tbl                               oe_order_pub.line_tbl_type;

   l_action_request_tbl                         oe_order_pub.request_tbl_type;

   x_header_rec                                 oe_order_pub.header_rec_type;
   x_header_val_rec                             oe_order_pub.header_val_rec_type;
   x_header_adj_tbl                             oe_order_pub.header_adj_tbl_type;
   x_header_adj_val_tbl                         oe_order_pub.header_adj_val_tbl_type;
   x_header_price_att_tbl                       oe_order_pub.header_price_att_tbl_type;
   x_header_adj_att_tbl                         oe_order_pub.header_adj_att_tbl_type;
   x_header_adj_assoc_tbl                       oe_order_pub.header_adj_assoc_tbl_type;
   x_header_scredit_tbl                         oe_order_pub.header_scredit_tbl_type;
   x_header_scredit_val_tbl                     oe_order_pub.header_scredit_val_tbl_type;
   x_line_tbl                                   oe_order_pub.line_tbl_type;
   x_line_val_tbl                               oe_order_pub.line_val_tbl_type;
   x_line_adj_tbl                               oe_order_pub.line_adj_tbl_type;
   x_line_adj_val_tbl                           oe_order_pub.line_adj_val_tbl_type;
   x_line_price_att_tbl                         oe_order_pub.line_price_att_tbl_type;
   x_line_adj_att_tbl                           oe_order_pub.line_adj_att_tbl_type;
   x_line_adj_assoc_tbl                         oe_order_pub.line_adj_assoc_tbl_type;
   x_line_scredit_tbl                           oe_order_pub.line_scredit_tbl_type;
   x_line_scredit_val_tbl                       oe_order_pub.line_scredit_val_tbl_type;
   x_lot_serial_tbl                             oe_order_pub.lot_serial_tbl_type;
   x_lot_serial_val_tbl                         oe_order_pub.lot_serial_val_tbl_type;
   x_action_request_tbl                         oe_order_pub.request_tbl_type;

   l_return_status                              VARCHAR2(2000);
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2(2000);

   l_line_cnt                                   NUMBER := 0;
   l_top_model_line_index                       NUMBER;
   l_link_to_line_index                         NUMBER;
   x_debug_file                                 VARCHAR2(100);
BEGIN
   /* Turn on DBMS Output */
   DBMS_OUTPUT.enable(1000000);

   DBMS_OUTPUT.put_line('Executing Process Order API for R12');

   mo_global.init('ONT');
   mo_global.set_policy_context('S'
                              , l_org_id);
   fnd_global.apps_initialize(l_user_id
                            , l_responsibility_id
                            , l_application_id
                            , NULL);

   oe_msg_pub.initialize;
   oe_debug_pub.initialize;
   oe_debug_pub.setdebuglevel(5);
   x_debug_file                                                                 := oe_debug_pub.set_debug_mode('FILE');
   DBMS_OUTPUT.put_line('START OF NEW DEBUG');

   l_header_rec                                                                 := oe_order_pub.g_miss_header_rec;
   l_header_rec.header_id                                                       := 431776; --ENTER VALUE
   l_header_rec.operation                                                       := oe_globals.g_opr_update;
   l_header_adj_tbl(1)                                                          := oe_order_pub.g_miss_header_adj_rec;
   l_header_adj_tbl(1).operation                                                := oe_globals.g_opr_delete;
   l_header_adj_tbl(1).header_id                                                := 431776; --ENTER VALUE
   l_header_adj_tbl(1).price_adjustment_id                                      := 3994447; --ENTER VALUE

   /* Call the Process Order API with Header Rec and Line Tbl */
   oe_order_pub.process_order(p_api_version_number                      => 1
                            , p_org_id                                  => l_org_id
                            , -- For R12
                             p_init_msg_list                            => fnd_api.g_true
                            , p_return_values                           => fnd_api.g_true
                            , p_action_commit                           => fnd_api.g_true
                            , x_return_status                           => l_return_status
                            , x_msg_count                               => l_msg_count
                            , x_msg_data                                => l_msg_data
                            , p_action_request_tbl                      => l_action_request_tbl
                            , p_header_rec                              => l_header_rec
                            , p_old_header_rec                          => l_old_header_rec
                            , p_header_adj_tbl                          => l_header_adj_tbl
                            , p_line_tbl                                => l_line_tbl
                            , p_old_line_tbl                            => l_old_line_tbl
                            , x_header_rec                              => x_header_rec
                            , x_header_val_rec                          => x_header_val_rec
                            , x_header_adj_tbl                          => x_header_adj_tbl
                            , x_header_adj_val_tbl                      => x_header_adj_val_tbl
                            , x_header_price_att_tbl                    => x_header_price_att_tbl
                            , x_header_adj_att_tbl                      => x_header_adj_att_tbl
                            , x_header_adj_assoc_tbl                    => x_header_adj_assoc_tbl
                            , x_header_scredit_tbl                      => x_header_scredit_tbl
                            , x_header_scredit_val_tbl                  => x_header_scredit_val_tbl
                            , x_line_tbl                                => x_line_tbl
                            , x_line_val_tbl                            => x_line_val_tbl
                            , x_line_adj_tbl                            => x_line_adj_tbl
                            , x_line_adj_val_tbl                        => x_line_adj_val_tbl
                            , x_line_price_att_tbl                      => x_line_price_att_tbl
                            , x_line_adj_att_tbl                        => x_line_adj_att_tbl
                            , x_line_adj_assoc_tbl                      => x_line_adj_assoc_tbl
                            , x_line_scredit_tbl                        => x_line_scredit_tbl
                            , x_line_scredit_val_tbl                    => x_line_scredit_val_tbl
                            , x_lot_serial_tbl                          => x_lot_serial_tbl
                            , x_lot_serial_val_tbl                      => x_lot_serial_val_tbl
                            , x_action_request_tbl                      => x_action_request_tbl);
   DBMS_OUTPUT.put_line(   'OM Debug file:'
                        || oe_debug_pub.g_dir
                        || '/'
                        || oe_debug_pub.g_file);

   IF l_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line('CONGRATULATIONS! !');
      DBMS_OUTPUT.put_line('Commit Issued ... !');
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line('Hard Luck! Error(s) while attempting to delete Order Level Modifier');
   END IF;

   FOR i IN 1 .. oe_msg_pub.g_msg_tbl.COUNT
   LOOP
      DBMS_OUTPUT.put_line(apps.oe_msg_pub.get(i
                                             , 'F'));
   END LOOP;

   oe_msg_pub.g_msg_tbl.delete;

   /* Turn off OM Debug */
   oe_debug_pub.debug_off;
END;
/