INV ORGIN REPORT

INV ORGIN REPORT

SELECT  msib.inventory_item_id
,msib.organization_id              "Warehouse_Code"
,msib.segment1                     "Item"
,msib.description                  "Item_Desc"
,mtln.lot_number
,msib.primary_uom_code             "UOM"
,msib.primary_unit_of_measure
,msib.inventory_item_status_code
,msib.item_type
,msib.default_so_source_type
,mmt.transaction_id
,mmt.subinventory_code
,mmt.transfer_subinventory
,mmt.attribute1                    "Ship_to_warehouse_id"
,(select od.organization_name from org_organization_definitions od
where od.organization_id=mmt.attribute1 ) ship_to_warehouse
,mmt.attribute2 shipment_reference
,ood.organization_code
,ood.organization_name                     Warehouse
,TO_CHAR(TO_DATE(mmt.attribute3 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') shipment_date
,ABS(mmt.transaction_quantity) quantity
,mmt.transaction_uom
--,mmt.actual_cost
--,mmt.prior_cost
--,mmt.new_cost
,( hou.address_line_1 ||''||
address_line_2||''||
address_line_3||''||
region_1||''||
region_2||''||
region_3||''||
telephone_number_1||''||
telephone_number_2||''||
telephone_number_3||''||
postal_code||''||
town_or_city||''||
country)ship_to_adderss
--,mmt.locator_id
,mmt.currency_code
FROM    mtl_system_items_b              msib
,mtl_material_transactions       mmt
,mtl_transaction_lot_numbers     mtln
,hr_organization_units_v         hou
,org_organization_definitions    ood
WHERE   msib.inventory_item_id=mmt.inventory_item_id
AND     msib.organization_id=mmt.organization_id
AND     msib.organization_id=ood.organization_id
AND     mmt.attribute1=hou.organization_id(+)
--AND     mmt.transaction_id=185011--162022--178029
AND     ood.organization_code=:inventory_org--parametors
--AND     mmt.attribute3 BETWEEN :from_date AND :to_date--parametors
AND     TO_CHAR(TO_DATE(mmt.attribute3 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY')
BETWEEN NVL(:from_date,TO_CHAR(TO_DATE(mmt.attribute3 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') )
AND NVL(:to_date,TO_CHAR(TO_DATE(mmt.attribute3 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') )
AND     mmt.transaction_id=mtln.transaction_id
AND     mmt.subinventory_code like 'IWM'