FA:MFG: SQL's to extract Details on Manage Work Area/Resources/Work Centers/Standard Operations

   --Manage work Area
   SELECT iop.organization_code
        , wwav.work_area_name
        , wwav.work_area_code
        , wwav.work_area_description
     FROM fusion.wis_work_areas_vl wwav
        , fusion.inv_org_parameters iop
    WHERE wwav.organization_id = iop.organization_id
      AND iop.organization_code = '<inv_org_code>';

   -- Manage Resources
   SELECT resource_name
        , resource_code
        , resource_description
        , resource_type
        , resource_class_code
        , uom_code
        , costed_flag
     FROM fusion.wis_resources_vl wrv
        , fusion.inv_org_parameters iop
    WHERE wrv.organization_id = iop.organization_id
      AND iop.organization_code = '<inv_org_code>'
    ORDER BY 1;

   --Manage Work Centers
   SELECT wwc.work_center_name
        , wwc.work_center_code
        , wwc.work_center_description
        , wwav.work_area_name
        , resource_name
        , wwr.resource_quantity
        , wwr.available_24_hours_flag
        , wwr.check_ctp_flag
        , wwr.utilization_percentage
        , wwr.efficiency_percentage
     FROM fusion.wis_work_centers_vl wwc
        , fusion.wis_work_areas_vl wwav
        , fusion.wis_wc_resources wwr
        , fusion.wis_resources_vl wrv
        , fusion.inv_org_parameters iop
    WHERE wwc.work_area_id = wwav.work_area_id
      AND wwc.work_center_id = wwr.work_center_id
      AND wwr.resource_id = wrv.resource_id
      AND wwc.organization_id = iop.organization_id
      AND iop.organization_code = '<inv_org_code>'
    ORDER BY 1;

-- Manage Standard Operations 
SELECT iop.organization_id
     , iop.organization_code
     , flv.meaning operation_type
     , wwo.standard_operation_name
     , wwo.standard_operation_code
     , wwo.standard_operation_description
     , wwc.work_center_name
     , wwc.work_center_code
     , wwc.work_center_description
     , wwo.count_point_flag
     , wwo.auto_transact_flag
     , wwo.inactive_date
     , wwo.used_in_auto_wd_flag
     , flvai.meaning addl_mtl_at_manual_issue
     , flvoi.meaning  op_compl_with_under_issue
     , flvoe.meaning  op_compl_with_open_exceptions
     , wsor.resource_seq_number
     , wwr.resource_name
     , wsor.assigned_units
     , flvb.meaning basis
     , wsor.usage_rate
     , wsor.inverse_usage_rate
     , wsor.uom_code
     , flvs.meaning schedule_type
     , wsor.principal_flag
     , flvc.meaning charge_type
  FROM fusion.wis_standard_operations_vl wwo
     , fusion.wis_std_operation_resources wsor
     , fusion.wis_resources_vl wwr
     , fusion.fnd_lookup_values_vl flvai     
     , fusion.fnd_lookup_values_vl flvoi     
     , fusion.fnd_lookup_values_vl flvoe
     , fusion.fnd_lookup_values_vl flvc     
     , fusion.fnd_lookup_values_vl flvs     
     , fusion.fnd_lookup_values_vl flvb
     , fusion.wis_work_centers_vl wwc
     , fusion.fnd_lookup_values_vl flv
     , fusion.inv_org_parameters iop
 WHERE wwo.standard_operation_id = wsor.standard_operation_id
   AND wsor.resource_id = wwr.resource_id
   AND flvc.enabled_flag = 'Y'
   AND flvc.lookup_code = wsor.charge_type
   AND flvc.lookup_type = 'ORA_WIS_CHARGE_TYPE'    
   AND flvs.enabled_flag = 'Y'
   AND flvs.lookup_code = wsor.schedule_type
   AND flvs.lookup_type = 'ORA_WIS_RESOURCE_SCHEDULE'   
   AND flvb.enabled_flag = 'Y'
   AND flvb.lookup_code = wsor.basis_type
   AND flvb.lookup_type = 'EGP_BOM_BASIS_TYPE'
   AND wwc.work_center_id = wwo.work_center_id
   AND flvai.enabled_flag = 'Y'
   AND flvai.lookup_code = wwo.addl_mtl_at_manual_issue
   AND flvai.lookup_type = 'ORA_WIS_ADDL_MANUAL_ISSUE_OPTS'
   AND flvoi.enabled_flag = 'Y'
   AND flvoi.lookup_code = wwo.op_compl_with_under_issue
   AND flvoi.lookup_type = 'ORA_WIS_OP_COMPLETION_VAL_OPTS'
   AND flvoe.enabled_flag = 'Y'
   AND flvoe.lookup_code = wwo.op_compl_with_open_exceptions
   AND flvoe.lookup_type = 'ORA_WIS_OP_COMPLETION_VAL_OPTS'   
   AND flv.enabled_flag = 'Y'
   AND flv.lookup_code = wwo.operation_type
   AND flv.lookup_type = 'ORA_WIS_OPERATION_TYPE'
   AND wwo.organization_id = iop.organization_id
   AND iop.organization_code = '<inv_org_code>';

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: