Reporting and Analytics for SCM Cloud

Get Involved. Join the Conversation.

Topic

    Viswanadha Reddy
    Need info on inventory adjustment for all material...
    Topic posted July 20, 2018 by Viswanadha ReddyBlue Ribbon: 750+ Points, tagged BI Publisher, BI Publisher Reports, Inventory, Manufacturing, OTBI, Product Development, Reporting and Analytics, SCM, SQL 
    100 Views, 1 Comment
    Title:
    Need info on inventory adjustment for all material transactions
    Summary:
    Need info on inventory adjustment for all material transactions
    Content:

    Hi Experts,

    I am looking for any BI or OTBI reports to get inventory adjustment for all material transactions (PO receipts, sales order issues, subinventory transfers, replenishment transactions, etc.).

    If you have any SQL or Catalog files please share.

     

    Thanks

    Viswa

     

     

     

    Comment

     

    • Viswanadha Reddy

      select iod.organization_name, iod.organization_code,
      imt.subinventory_code,
      item.item_number,
      item.description,
       item_rel.cross_reference cross_reference_item,
      imt.transaction_quantity    ,
      iuomtl.unit_of_measure         ,
      to_char(imt.transaction_date,'mm-dd-yy hh:mi:ss') transaction_date ,
      itst.transaction_source_type_name ,
      itt.transaction_type_name,
      iil.segment1||'.'||iil.segment2||'.'||iil.segment3 locator_name ,
      (select poh.segment1 from po_headers_all poh
      where poh.po_header_id=imt.transaction_source_id ) source_reference ,
      imt.transaction_id   
       from inv_material_txns imt,
       inv_txn_source_types_tl itst,
       inv_transaction_types_tl itt,
       inv_organization_definitions_v iod,
       inv_units_of_measure_b iuomb ,
      inv_units_of_measure_tl iuomtl ,
      egp_system_items_vl item,
       egp_item_relationships_b  item_rel,
       inv_item_locations iil
      where imt.transaction_source_type_id=itst.transaction_source_type_id
      and imt.transaction_type_id=itt.transaction_type_id
      and imt.organization_id=iod.organization_id
      and imt.transaction_uom=iuomb.uom_code 
      and iuomb.unit_of_measure_id = iuomtl.unit_of_measure_id 
      and imt.inventory_item_id=item.inventory_item_id
      and imt.organization_id=item.organization_id
      and item.inventory_item_id=item_rel.inventory_item_id(+) 
      and item_rel.item_relationship_type(+) = 'item_xref'
      and imt.locator_id=iil.inventory_location_id(+)
      and imt.subinventory_code=iil.subinventory_code(+)
      and imt.organization_id=iil.organization_id(+)
      and to_char(imt.transaction_date,'yyyy-mm-dd hh:mi:ss') between nvl(:p_from_date,to_char(imt.transaction_date,'yyyy-mm-dd hh:mi:ss'))
      and nvl(:p_to_date,to_char(imt.transaction_date,'yyyy-mm-dd hh:mi:ss'))