VIEW: APPS.ASO_PVT_RELATED_ITEMS_BALI_V

Source

View Text - Preformatted

SELECT A.ORGANIZATION_ID , A.INVENTORY_ITEM_ID , A.RELATED_ITEM_ID , A.RELATIONSHIP_TYPE_ID , C.MEANING relationship_type , A.RECIPROCAL_FLAG , B.CONCATENATED_SEGMENTS , B.DESCRIPTION , B.PRIMARY_UOM_CODE , B.LIST_PRICE_PER_UNIT , SYSDATE , NULL , NULL , NULL , B.BOM_ITEM_TYPE , B.SERVICE_ITEM_FLAG , B.VENDOR_WARRANTY_FLAG , B.CUSTOMER_ORDER_ENABLED_FLAG , B.RETURNABLE_FLAG , B.PADDED_CONCATENATED_SEGMENTS , Decode(b.bom_item_type,1,'MDL',2,'OPT',3,'PLN',4,decode(b.service_item_flag,'Y','SRV',decode(b.serviceable_product_flag,'Y','SVA','STD'))) item_type_code, B.SERVICE_DURATION, B.SERVICE_DURATION_PERIOD_CODE from MTL_RELATED_ITEMS_VIEW A, MTL_SYSTEM_ITEMS_VL B, MFG_LOOKUPS C WHERE a.related_item_id = b.inventory_item_id and a.organization_id = b.organization_id and c.lookup_type = 'MTL_RELATIONSHIP_TYPES' and a.relationship_type_id = c.lookup_code and (b.bom_item_type = 1 OR (b.bom_item_type = 4 AND NOT (b.service_item_flag = 'Y' AND b.vendor_warranty_flag = 'Y' ))) and b.primary_uom_code <> 'ENR'

View Text - HTML Formatted

SELECT A.ORGANIZATION_ID
, A.INVENTORY_ITEM_ID
, A.RELATED_ITEM_ID
, A.RELATIONSHIP_TYPE_ID
, C.MEANING RELATIONSHIP_TYPE
, A.RECIPROCAL_FLAG
, B.CONCATENATED_SEGMENTS
, B.DESCRIPTION
, B.PRIMARY_UOM_CODE
, B.LIST_PRICE_PER_UNIT
, SYSDATE
, NULL
, NULL
, NULL
, B.BOM_ITEM_TYPE
, B.SERVICE_ITEM_FLAG
, B.VENDOR_WARRANTY_FLAG
, B.CUSTOMER_ORDER_ENABLED_FLAG
, B.RETURNABLE_FLAG
, B.PADDED_CONCATENATED_SEGMENTS
, DECODE(B.BOM_ITEM_TYPE
, 1
, 'MDL'
, 2
, 'OPT'
, 3
, 'PLN'
, 4
, DECODE(B.SERVICE_ITEM_FLAG
, 'Y'
, 'SRV'
, DECODE(B.SERVICEABLE_PRODUCT_FLAG
, 'Y'
, 'SVA'
, 'STD'))) ITEM_TYPE_CODE
, B.SERVICE_DURATION
, B.SERVICE_DURATION_PERIOD_CODE
FROM MTL_RELATED_ITEMS_VIEW A
, MTL_SYSTEM_ITEMS_VL B
, MFG_LOOKUPS C
WHERE A.RELATED_ITEM_ID = B.INVENTORY_ITEM_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND C.LOOKUP_TYPE = 'MTL_RELATIONSHIP_TYPES'
AND A.RELATIONSHIP_TYPE_ID = C.LOOKUP_CODE
AND (B.BOM_ITEM_TYPE = 1 OR (B.BOM_ITEM_TYPE = 4
AND NOT (B.SERVICE_ITEM_FLAG = 'Y'
AND B.VENDOR_WARRANTY_FLAG = 'Y' )))
AND B.PRIMARY_UOM_CODE <> 'ENR'