SQL to get Material Transaction Details (Oracle Fusion Inventory Management)

 SQL to get Material Transaction Details

SELECT iop.organization_code warehouse_code
     , ittv.transaction_type_name
     , flva.meaning transaction_action
     , itst.transaction_source_type_name
     , esi.item_number
     , imt.subinventory_code
     , imt.transaction_quantity
     , imt.transaction_uom
     , imt.primary_quantity
     , imt.transaction_date
     , imt.transaction_source_id
     , imt.transaction_source_name
     , imt.rcv_transaction_id     
     , imt.distribution_account_id
     , imt.trx_source_line_id
     , imt.trx_source_delivery_id
     , imt.rma_line_id
     , imt.transfer_transaction_id
     , imt.transaction_Set_id
     , imt.source_code
     , imt.source_line_id
     , imt.transfer_organization_id
     , imt.transfer_subinventory
     , imt.shipment_number
  FROM inv_material_txns imt
     , inv_org_parameters iop
     , egp_system_items_b esi
     , inv_transaction_types_vl ittv
     , fnd_lookup_values_vl flva
     , inv_txn_source_types_vl itst
 WHERE imt.organization_id = iop.organization_id
   AND imt.organization_id = esi.organization_id
   AND imt.inventory_item_id = esi.inventory_item_id
   AND imt.transaction_type_id = ittv.transaction_type_id
   AND ittv.transaction_action_id = flva.lookup_code
   AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
   AND flva.enabled_flag = 'Y'
   AND ittv.transaction_source_type_id = itst.transaction_source_type_id
   AND ittv.transaction_type_name = 'RMA Receipt';

/

Sales Order Issue Materail Transaction Report. 

SELECT iop.organization_code warehouse_code
     , ittv.transaction_type_name
     , flva.meaning transaction_action
     , itst.transaction_source_type_name
     , esi.item_number
     , imt.subinventory_code
     , imt.transaction_quantity
     , imt.transaction_uom
     , imt.primary_quantity
     , imt.transaction_date
     , imt.transaction_source_id
     , imt.transaction_source_name
     , imt.rcv_transaction_id
     , imt.distribution_account_id
     , imt.trx_source_line_id
     , imt.trx_source_delivery_id
     , imt.rma_line_id
     , imt.transfer_transaction_id
     , imt.transaction_set_id
     , imt.source_code
     , imt.source_line_id
     , imt.transfer_organization_id
     , imt.transfer_subinventory
     , imt.shipment_number
     , dha.source_order_number
     , dha.order_number
     , dfla.status_code
     , dfla.source_line_number
  FROM inv_material_txns imt
     , inv_org_parameters iop
     , egp_system_items_b esi
     , inv_transaction_types_vl ittv
     , fnd_lookup_values_vl flva
     , inv_txn_source_types_vl itst
     , fusion.doo_headers_all dha
     , fusion.doo_fulfill_lines_all dfla
     , fusion.wsh_delivery_assignments wda
     , fusion.wsh_new_deliveries wnd
     , fusion.wsh_delivery_details wdd
 WHERE imt.organization_id = iop.organization_id
   AND imt.organization_id = esi.organization_id
   AND imt.inventory_item_id = esi.inventory_item_id
   AND imt.transaction_type_id = ittv.transaction_type_id
   AND ittv.transaction_action_id = flva.lookup_code
   AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
   AND flva.enabled_flag = 'Y'
   AND ittv.transaction_source_type_id = itst.transaction_source_type_id
   AND ittv.transaction_type_name LIKE 'Sales Order Issue'
   AND dha.header_id = dfla.header_id
   AND dfla.fulfill_line_id = wdd.source_shipment_id
   AND wdd.delivery_detail_id = wda.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id (+)
   AND wda.delivery_detail_id = imt.trx_source_line_id (+)
   AND dha.submitted_flag = 'Y'
/






 

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;