VIEW: APPS.CLN_INVENTORY_REPORT_V

Source

View Text - Preformatted

SELECT distinct msib.start_date_active ,msib.inventory_item_status_code ,msib.item_type ,msib.primary_uom_code ,msib.inventory_item_id ,msib.attribute_category ,msib.attribute1 ,msib.attribute2 ,msib.attribute3 ,msib.attribute4 ,msib.attribute5 ,msib.attribute6 ,msib.attribute7 ,msib.attribute8 ,msib.attribute9 ,msib.attribute10 ,msib.attribute11 ,msib.attribute12 ,msib.attribute13 ,msib.attribute14 ,msib.attribute15 ,msib.end_date_active ,msit.description ,mir.revision_id ,mir.revision ,msib.organization_id ,haou.name ,hla.ece_tp_location_code ,hla.address_line_1 ,hla.address_line_2 ,hla.address_line_3 ,hla.town_or_city ,hla.country ,hla.postal_code ,msibk.concatenated_segments ,msib.REVISION_QTY_CONTROL_CODE ,msib.LOT_CONTROL_CODE FROM mtl_system_items_b msib ,mtl_system_items_tl msit ,mtl_item_revisions mir ,hr_all_organization_units haou ,hr_locations_all hla ,mtl_system_items_vl msibk WHERE ( msib.inventory_item_id = msit.inventory_item_id and msib.organization_id = msit.organization_id ) and ( msib.inventory_item_id = msibk.inventory_item_id and msib.organization_id = msibk.organization_id ) and ( decode( msib.REVISION_QTY_CONTROL_CODE,1,null,msib.inventory_item_id) = mir.inventory_item_id(+) and msib.organization_id = mir.organization_id(+) ) and (haou.location_id = hla.location_id) and (msib.organization_id = haou.organization_id)

View Text - HTML Formatted

SELECT DISTINCT MSIB.START_DATE_ACTIVE
, MSIB.INVENTORY_ITEM_STATUS_CODE
, MSIB.ITEM_TYPE
, MSIB.PRIMARY_UOM_CODE
, MSIB.INVENTORY_ITEM_ID
, MSIB.ATTRIBUTE_CATEGORY
, MSIB.ATTRIBUTE1
, MSIB.ATTRIBUTE2
, MSIB.ATTRIBUTE3
, MSIB.ATTRIBUTE4
, MSIB.ATTRIBUTE5
, MSIB.ATTRIBUTE6
, MSIB.ATTRIBUTE7
, MSIB.ATTRIBUTE8
, MSIB.ATTRIBUTE9
, MSIB.ATTRIBUTE10
, MSIB.ATTRIBUTE11
, MSIB.ATTRIBUTE12
, MSIB.ATTRIBUTE13
, MSIB.ATTRIBUTE14
, MSIB.ATTRIBUTE15
, MSIB.END_DATE_ACTIVE
, MSIT.DESCRIPTION
, MIR.REVISION_ID
, MIR.REVISION
, MSIB.ORGANIZATION_ID
, HAOU.NAME
, HLA.ECE_TP_LOCATION_CODE
, HLA.ADDRESS_LINE_1
, HLA.ADDRESS_LINE_2
, HLA.ADDRESS_LINE_3
, HLA.TOWN_OR_CITY
, HLA.COUNTRY
, HLA.POSTAL_CODE
, MSIBK.CONCATENATED_SEGMENTS
, MSIB.REVISION_QTY_CONTROL_CODE
, MSIB.LOT_CONTROL_CODE
FROM MTL_SYSTEM_ITEMS_B MSIB
, MTL_SYSTEM_ITEMS_TL MSIT
, MTL_ITEM_REVISIONS MIR
, HR_ALL_ORGANIZATION_UNITS HAOU
, HR_LOCATIONS_ALL HLA
, MTL_SYSTEM_ITEMS_VL MSIBK
WHERE ( MSIB.INVENTORY_ITEM_ID = MSIT.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MSIT.ORGANIZATION_ID )
AND ( MSIB.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID )
AND ( DECODE( MSIB.REVISION_QTY_CONTROL_CODE
, 1
, NULL
, MSIB.INVENTORY_ITEM_ID) = MIR.INVENTORY_ITEM_ID(+)
AND MSIB.ORGANIZATION_ID = MIR.ORGANIZATION_ID(+) )
AND (HAOU.LOCATION_ID = HLA.LOCATION_ID)
AND (MSIB.ORGANIZATION_ID = HAOU.ORGANIZATION_ID)