FA:SQL: Query to get inventory organization/inventory organization location and business unit and Legal entity

SELECT iod.organization_code,
       iod.organization_name,
       iod.business_unit_name,
       iod.business_unit_id,
       iod.legal_entity,
       xep.NAME legal_entity_name,
       hla.location_code,
       hla.location_name,
       hla.internal_location_code ,
              Concat(Concat(Concat(Concat(Concat(Concat( Concat(Concat(hla.address_line_1
              || ', ', Nvl2(hla.address_line_2, hla.address_line_2
              || ', ', hla.address_line_2)), Nvl2(hla.address_line_3, hla.address_line_3
              || ', ', hla.address_line_3)), Nvl2(hla.address_line_4, hla.address_line_4
              || ', ', hla.address_line_4)), Nvl2(hla.town_or_city, hla.town_or_city
              || ', ', hla.town_or_city)), Nvl2(hla.region_1, hla.region_1
              || ', ', hla.region_1)), Nvl2(hla.region_2, hla.region_2
              || ', ', hla.region_2)), Nvl2(hla.country, country
              || ', ', hla.country)), hla.postal_code) concatenated_address,
       iod.inventory_enabled_flag,
       iod.inventory_flag,
       iod.distributed_organization_flag,
       iod.profit_center_bu_id,
       iod.mfg_plant_flag,
       iod.contract_mfg_flag,
       iod.eam_enabled_flag,
       iod.timezone_code
 FROM inv_organization_definitions_v iod,
      hr_locations_all hla,
      xle_entity_profiles xep
WHERE iod.location_id = hla.location_id
  AND iod.legal_entity = xep.legal_entity_id
  AND NVL(iod.disable_date, sysdate+1) > sysdate;

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