FA:RCV:SQL to get RMA Receipts Details in Oracle Fusion

 REM RMA Receipts Details
SELECT DHA.SOURCE_ORDER_NUMBER
     , HDR_LKP.MEANING ORDER_TYPE
     , DFLA.SOURCE_LINE_NUMBER
     , LINE_LKP.MEANING LINE_TYPE     
     , INV.ORGANIZATION_CODE
     , DSV.DISPLAY_NAME FUL_LINE_STATUS
     , (SELECT TO_CHAR(CREATION_DATE, 'DD-MON-YYYY') FROM INV_MATERIAL_TXNS WHERE RCV_TRANSACTION_ID = RMA_RECEIPT_TRANSACTION_ID) INV_TXN_CREATION_DATE
     , ESIB.ITEM_NUMBER
     , DLA.ORDERED_QTY LINE_ORDERED_QTY
     , DFLA.ORDERED_QTY FULFILL_LINE_ORDERED_QTY
     , DFLA.RMA_DELIVERED_QTY
     , DFLD.RMA_RECEIPT_NUMBER
     , DFLD.RMA_RECEIPT_LINE_NUMBER
     , DFLD.RMA_RECEIPT_DATE
     , DLA.DISPLAY_LINE_NUMBER
         || '.'
         || DFLA.FULFILL_LINE_NUMBER LINE_NUM
  FROM FUSION.INV_ORG_PARAMETERS INV
     , FUSION.DOO_FULFILL_LINES_ALL DFLA
     , FUSION.DOO_FULFILL_LINE_DETAILS DFLD
     , FUSION.FND_LOOKUP_VALUES_VL LINE_LKP     
     , FUSION.DOO_LINES_ALL DLA
     , FUSION.FND_LOOKUP_VALUES_VL HDR_LKP
     , FUSION.DOO_HEADERS_ALL DHA
     , FUSION.EGP_SYSTEM_ITEMS_B ESIB
     , FUSION.DOO_STATUSES_VL DSV
     , FUSION.DOO_ORCHESTRATION_APPLICATIONS DOA
 WHERE DHA.HEADER_ID = DFLA.HEADER_ID
   AND DHA.HEADER_ID = DLA.HEADER_ID
   AND DFLA.LINE_ID = DLA.LINE_ID
   AND DHA.SUBMITTED_FLAG = 'Y'
   AND DHA.ORDER_TYPE_CODE = HDR_LKP.LOOKUP_CODE
   AND HDR_LKP.LOOKUP_TYPE = 'ORA_DOO_ORDER_TYPES'
   AND HDR_LKP.ENABLED_FLAG = 'Y'   
   AND DFLA.LINE_TYPE_CODE = LINE_LKP.LOOKUP_CODE
   AND LINE_LKP.LOOKUP_TYPE IN ('ORA_DOO_LINE_TYPES', 'ORA_DOO_RETURN_LINE_TYPES')
   AND LINE_LKP.ENABLED_FLAG = 'Y'   
   AND DOA.ORCHESTRATION_APPLICATION_CODE = 'DOO'
   AND DOA.ORCHESTRATION_APPLICATION_ID = DSV.ORCHESTRATION_APPLICATION_ID
   AND DFLA.STATUS_CODE = DSV.STATUS_CODE
   AND DLA.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID
   AND DLA.INVENTORY_ORGANIZATION_ID = ESIB.ORGANIZATION_ID
   AND DFLA.FULFILL_ORG_ID = INV.ORGANIZATION_ID
   AND UPPER(DFLD.TASK_TYPE(+)) = 'RETURN'
   AND DFLA.FULFILL_LINE_ID = DFLD.FULFILL_LINE_ID(+)
   AND DHA.SOURCE_ORDER_NUMBER = :P_ORDER_NUMBER
   --AND ESIB.ITEM_NUMBER  = NVL(:P_ITEM_NUMBER, ESIB.ITEM_NUMBER)
  ORDER BY DHA.SOURCE_ORDER_NUMBER, LPAD(DLA.DISPLAY_LINE_NUMBER, 2) ASC;

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