FA:SQL: Oralce Fusion Manufacturing Manage Scheduels/Schedule Exceptions/ Work Order Definition

 REM Manage Schedules
SELECT DISTINCT cal.schedule_name
              , zssp.schedule_id calendar_code
              , zssv.shift_id shift_num
              , zssv.shift_name
              , zssv.shift_desc description
              , zssv.start_time_ms_num from_time
              , zssv.end_time_ms_num to_time
              , zssv.shift_type_code
  FROM fusion.zmm_sr_shifts_vl zssv
     , fusion.zmm_sr_pattern_dtls zspd
     , fusion.zmm_sr_schedule_patterns zssp
     , fusion.zmm_sr_schedules_vl cal
 WHERE zspd.pattern_id (+) = zssp.pattern_id
   AND zspd.child_shift_id = zssv.shift_id
   AND zssp.schedule_id = cal.schedule_id;
   
 REM Manage Schedule exceptions
SELECT cal.schedule_name
     , zssae.schedule_id calendar_code
     , zssae.avl_exception_id
     , zsaev.avl_excp_name
     , zsaev.avl_excp_desc
     , zsaev.start_date_time
     , zsaev.end_date_time
     , zsaev.short_txt
     , zsaev.category_code
     , zsaev.whole_day_flag
     , zsaev.availability_code
  FROM fusion.zmm_sr_sched_avl_excps zssae
     , fusion.zmm_sr_avl_exceptions_vl zsaev
     , fusion.zmm_sr_schedules_vl cal
 WHERE zsaev.avl_exception_id (+) = zssae.avl_exception_id
   AND zssae.schedule_id = cal.schedule_id
   AND cal.schedule_name = 'US Planning Schedules';

REM Work Order Definitions
SELECT a.organization_id
     , iop.organization_code
     , b.inventory_item_id
     , b.item_number
     , f.alternate_bom_designator
     , d.work_definition_name_id
     , d.work_def_name
     , d.work_def_name_description
     , d.work_definition_code
     , d.work_definition_type
     , e.meaning
     , a.object_version_number
     , a.production_priority
     , a.costing_priority
     , a.work_definition_id
     , a.status_code
     , a.work_method_id
     , c.work_method_name
     , c.work_method_code
  FROM fusion.wis_work_definitions a
     , fusion.egp_system_items_b b
     , fusion.wis_work_methods_vl c
     , fusion.wis_work_definition_names_vl d
     , fusion.FND_LOOKUP_VALUES_VL e
     , fusion.egp_structures_b f
     , fusion.inv_org_parameters iop
 WHERE a.organization_id = b.organization_id
   AND a.inventory_item_id = b.inventory_item_id
   AND a.work_method_id = c.work_method_id
   AND a.organization_id = iop.organization_id
   AND iop.organization_code = 'FHK'
   and a.work_definition_name_id = d.work_definition_name_id
   and e.lookup_type = 'ORA_WIS_WORK_DEFINITION_TYPE'
   and e.lookup_code = d.work_definition_type
   and e.enabled_flag = 'Y'
   and a.bill_sequence_id = f.bill_sequence_id;

SQL to get the RMA Details In Oracle Fusion (Inventory/Order Management)

SELECT DISTINCT iop.organization_code warehouse_code
              , haotl.name warehouse_name
              , rt.subinventory
              , rah.ra_document_number AS rma_number
              , dla.display_line_number line_number
              , dla.display_line_number
                  || '.'
                  || dfla.fulfill_line_number rma_line_number
              , rsh.receipt_num rma_receipt_number
              , esi.item_number sku
              , nvl2(dlsn.item_serial_number_from, 1, rsl.quantity_received) quantity_received
              , dlsn.item_serial_number_from
              , dlsn.item_serial_number_to
              , rt.transaction_id
              , rt.transaction_type
              , rt.quantity
              , rt.uom_code
              , ral.receipt_advice_line_number AS doo_fulfil_line_id
              , dfld.rma_receipt_date AS rma_receipt_date
              , rt.creation_date
              , rt.transaction_date
              , dha.order_type_code
              , DECODE(dha.order_type_code, 'B2BRMA', NVL(substr(dfla.source_line_number,0,instr(dfla.source_line_number,'.')-1),dfla.source_line_number),1) source_line_number
  FROM rcv_shipment_headers rah
     , rcv_shipment_lines ral
     , rcv_shipment_headers rsh
     , rcv_shipment_lines rsl
     , rcv_transactions rt
     , inv_org_parameters iop
     , fusion.hr_organization_units_f_tl haotl
     , fusion.hr_org_unit_classifications_f houc
     , doo_fulfill_lines_all dfla
     , doo_fulfill_line_details dfld
     , doo_lot_serial_numbers dlsn
     , egp_system_items_b esi
     , doo_lines_all dla
     , doo_headers_all dha
 WHERE 1 = 1
--   AND rah.ra_document_number = '1595254717339306'
   AND rt.transaction_type = 'DELIVER'
   AND rt.source_document_code = 'RMA'
   AND rt.shipment_header_id = rsh.shipment_header_id
   AND rt.shipment_line_id = rsl.shipment_line_id
   AND rsh.shipment_header_id = rsl.shipment_header_id
   AND rt.receipt_advice_header_id = rah.shipment_header_id
   AND rt.receipt_advice_line_id = ral.shipment_line_id
   AND iop.organization_id = rsl.to_organization_id
   AND haotl.organization_id = rsl.to_organization_id
   AND haotl.language = userenv('LANG')
   AND houc.organization_id = haotl.organization_id
   AND houc.classification_code = 'INV'
   AND rt.transaction_id = dfld.rma_receipt_transaction_id
   AND dfla.fulfill_line_id = dfld.fulfill_line_id
   AND upper(dfld.task_type) = 'RETURN'
   AND dfla.fulfill_line_id = dlsn.fulfill_line_id (+)
   AND dfla.inventory_item_id = esi.inventory_item_id
   AND dfla.inventory_organization_id = esi.organization_id
   AND dfla.line_id = dla.line_id
   AND dla.header_id = dha.header_id
  ORDER BY 4
        , 5
        , 6

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