Fusion: SQL: Query to Extract the Business Units and its related Inventory Organizations, Business Units and Costing Organizations

 

 

SELECT hou.organization_id
       , hou.name organization_name
       , iop.organization_code organization_code
       , lgr.ledger_id set_of_book_id
       , lgr.chart_of_accounts_id chart_of_account_id
       , lgr.currency_code
       , lgr.period_set_name
       , Decode(hoi.status, 'A', 'Y', 'N') inv_enabled_flag
       , bu.bu_name business_unit_name
       , iop.business_unit_id business_unit_id
       , iop.legal_entity_id legal_entity
       , hou.type organization_type
  FROM hr_all_organization_units_x hou
       , hr_org_unit_classifications_x hoi
       , inv_org_parameters iop
       , gl_ledgers lgr
       , fun_all_business_units_v bu
 WHERE hou.organization_id = hoi.organization_id
   AND hou.organization_id = iop.organization_id
   AND hoi.classification_code = 'INV'
   AND bu.primary_ledger_id = lgr.ledger_id(+)
   AND lgr.object_type_code(+) = 'L'
   AND NVL(lgr.complete_flag, 'Y') = 'Y'
   AND bu.bu_id(+) = iop.business_unit_id;  

SQL : Get Business Unit Details

===================================

 SELECT hauft.NAME BusinessUnit, hauft.organization_id
  FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date    AND houcf.effective_end_date
   AND hauft.language = 'US'
   AND hauft.effective_start_date = haouf.effective_start_date
   AND hauft.effective_end_date = haouf.effective_end_date
   AND houcf.classification_code = 'FUN_BUSINESS_UNIT'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
 ORDER BY hauft.NAME ASC nulls first ; 

SQL : Get Costing Organization Details

===================================

 SELECT hauft.NAME BusinessUnit
  FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date AND houcf.effective_end_date
   AND hauft.language = 'US'
   AND hauft.effective_start_date = haouf.effective_start_date
   AND hauft.effective_end_date = haouf.effective_end_date
   AND houcf.classification_code = 'CST'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
 ORDER BY hauft.NAME ASC nulls first; 

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;