FA:SCM:CST: SQL to Get Item Standard Cost

 SELECT houft.name cost_organization_name
       , esi.item_number
       , csc.total_cost
       , uom.uom_code
       , cvu.val_unit_code
       , cvs.val_structure_code
       , cvs.val_structure_type_code
       , ccb.cost_book_code
  FROM cst_std_costs csc
       , cst_val_units_b cvu
       , cst_val_structures_b cvs
       , egp_system_items_vl esi
       , cst_cost_org_parameters ccop
       , hr_organization_units_f_tl houft
       , cst_cost_books_b ccb
       , inv_units_of_measure_vl uom
 WHERE csc.cost_book_id = ccb.cost_book_id
   AND csc.cost_org_id = houft.organization_id
   AND ccop.cost_org_id = houft.organization_id
   AND cvu.val_structure_id = cvs.val_structure_id
   AND csc.val_unit_id = cvu.val_unit_id
   AND esi.inventory_item_id = csc.inventory_item_id
   AND esi.organization_id = ccop.master_organization_id
   AND houft.LANGUAGE = Userenv('LANG')
   AND csc.uom_code = uom.uom_code
   AND csc.status_code = 'PUBLISHED'
   AND esi.item_number = :p_item_number
   AND houft.name = :p_cost_org_name
   AND trunc(nvl(csc.effective_end_date,sysdate+1)) > trunc(sysdate);

Fusion:SCM:MFG: SQL to Validat the Componenet Issue and WO Completion transactions against Work Order

SELECT imt.transaction_id,iop.organization_code warehouse_code
     , TO_CHAR(imt.creation_date,'DD-MON-YYYY') txn_creation_date
     , 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.transaction_date
     , imt.transaction_source_name     
     , iut.serial_number   
  FROM fusion.inv_material_txns imt
     , fusion.inv_org_parameters iop
     , fusion.egp_system_items_b esi
     , fusion.inv_transaction_types_vl ittv
     , fusion.fnd_lookup_values_vl flva
     , fusion.inv_txn_source_types_vl itst
     , fusion.inv_unit_transactions iut
 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 imt.transaction_id = iut.transaction_id(+)
   AND UPPER(ittv.transaction_type_name) IN ('WORK IN PROCESS MATERIAL ISSUE', 'WORK IN PROCESS PRODUCT COMPLETION')
   AND UPPER(itst.transaction_source_type_name) = 'WORK ORDER'
   AND esi.item_number = NVL(:p_item_number,esi.item_number)
   AND iop.organization_code = NVL(:p_org_code,iop.organization_code)
   AND imt.transaction_source_name = NVL(:p_wo_number, imt.transaction_source_name)
   AND esi.item_number = NVL(:p_item_number, esi.item_number)
   ORDER BY imt.creation_date desc;

Fusion: SCM: SQL to get the RMA receipts and order number details

 SELECT imt.transaction_id
       , iop.organization_code warehouse_code
       , To_char(imt.creation_date, 'DD-MON-YYYY') txn_creation_date
       , 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
       , iut.serial_number
       , rsh.receipt_num rma_receipt_number
  FROM fusion.inv_material_txns imt
       , fusion.inv_org_parameters iop
       , fusion.egp_system_items_b esi
       , fusion.inv_transaction_types_vl ittv
       , fusion.fnd_lookup_values_vl flva
       , fusion.inv_txn_source_types_vl itst
       , fusion.inv_unit_transactions iut
       , fusion.rcv_shipment_headers rsh
       , fusion.rcv_shipment_lines rsl
       , fusion.rcv_transactions rt       
       , fusion.DOO_FULFILL_LINE_DETAILS dfld
       , fusion.DOO_FULFILL_LINES_ALL    dfla
       , fusion.DOO_HEADERS_ALL dha
 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 imt.transaction_id = iut.transaction_id(+)
   AND ittv.transaction_type_name =  'RMA Receipt'
   AND itst.transaction_source_type_name = 'RMA'
   AND rsh.shipment_header_id = rsl.shipment_header_id
   AND rsh.shipment_header_id = rt.shipment_header_id
   AND rsl.shipment_header_id = rt.shipment_header_id
   AND rsl.shipment_line_id = rt.shipment_line_id
   AND rt.transaction_id = imt.rcv_transaction_id
   AND imt.rcv_transaction_id = dfld.RMA_RECEIPT_TRANSACTION_ID
   AND dfld.fulfill_line_id = dfla.fulfill_line_id
   AND dfla.header_id = dha.header_id
   and dha.submitted_flag = 'Y'
   AND dha.order_number = :p_order_number
 ORDER BY imt.creation_date DESC

Oracle Fusion SQL: SQL to Extract the Sales order Orchestraction Steps with each Step Status details

    SELECT  h.source_order_number
     , h.header_id
     , f.fulfill_line_id
     , f.source_line_number
     , f.source_line_id
     , pd.process_name
     , psvl.step_id
     , psvl.step_number
     , psvl.parent_step_number
     , psvlt.step_name
     , psi.step_instance_id
     , psi.group_id
     , psvl.step_type
     , psi.step_active
     , psi.step_status
     , psi.task_instance_id
     , nvl(to_char(psi.actual_start_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "actual_start_Date"
     , nvl(to_char(psi.actual_completion_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "actual_completion_Date"
     , nvl(to_char(psi.planned_start_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "planned_start_Date"
     , nvl(to_char(psi.planned_completion_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "planned_completion_Date"
     , nvl(to_char(psi.required_start_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "required_start_Date"
     , nvl(to_char(psi.required_completion_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "required_completion_Date"
     , psi.last_updated_by
  FROM fusion.doo_headers_all h
     , fusion.doo_fulfill_lines_all f
     , fusion.doo_orchestration_groups g
     , fusion.doo_process_steps_b psvl
     , fusion.doo_process_definitions_b pd
     , fusion.doo_process_steps_tl psvlt
     , fusion.doo_process_step_instances psi
 WHERE 1 = 1  
   AND h.submitted_flag = 'Y'
   AND h.header_id = f.header_id
   AND f.header_id = g.header_id
   AND g.status = 'ACTIVE'
   AND f.fulfill_line_id = g.fulfillment_line_id
   AND g.doo_process_instance_id = psi.doo_process_instance_id
   AND psi.step_id = psvl.step_id
   AND psvl.step_id = psvlt.step_id
   AND psvl.doo_process_id = pd.doo_process_id
   AND psvlt.language = userenv('LANG')
   AND H.ORDER_NUMBER = :p_order_number
 ORDER BY h.source_order_number
        , LPAD(f.source_line_number, 2)
        , f.fulfill_line_id
        , psvl.doo_process_id
        , psvl.step_number;

Oracle Fusion SQL : SQL to Extract Transfer Order details

SELECT d.header_number
       , e.line_number
       , so.organization_code source_inv_org
       , do.organization_code dest_inv_org
       , sku.item_number
       , e.requested_qty
       , e.shipped_qty
       , e.received_qty
       , e.delivered_qty
       , e.source_subinventory_code source_subinventory
       , e.destination_subinventory_code dest_subinventory
       , d.fulfill_orchestration_required
       , d.source_type_lookup  hdr_source_type_lookup
       , e.source_organization_id
       , e.destination_organization_id
       , e.line_id
       , e.interface_status_lookup
       , e.source_type_lookup
       , e.destination_type_lookup
       , e.destination_location_id
       , e.status_lookup
       , e.need_by_date
       , e.scheduled_ship_date
       , e.qty_uom_code
       , e.unit_price
       , e.req_bu_id
       , e.supply_order_reference_number
       , e.supply_order_ref_line_number
       , d.header_id
       , sku.inventory_item_id
       , d.creation_date
  FROM fusion.inv_transfer_order_headers d
       , fusion.inv_transfer_order_lines e
       , fusion.inv_org_parameters so
       , fusion.inv_org_parameters do
       , fusion.egp_system_items_b sku
 WHERE 1 = 1
   AND sku.inventory_item_id = e.inventory_item_id
   AND sku.organization_id = e.source_organization_id
   AND do.organization_id = e.destination_organization_id
   AND so.organization_id = e.source_organization_id
   AND d.header_id = e.header_id
   AND so.organization_code = :p_from_organization
   AND do.organization_code = :p_to_organization
   AND sku.item_number = NVL(:p_part_number, sku.item_number)
   AND e.supply_order_reference_number = Nvl(:p_supply_order, e.supply_order_reference_number)
 ORDER BY d.creation_date DESC
          , 1
          , Lpad(e.line_number, 2);