[Home] [Help]
| Product: | XNC - Sales for Communications (Obsolete) |
|---|---|
| Description: | XNC_RELITEM_PL_V retrieves items for all relationship types for an item. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT DISTINCT QLA.ROW_ID
, MR.INVENTORY_ITEM_ID
, MFG.MEANING
, MR.RELATED_ITEM_ID
, MSI.PADDED_CONCATENATED_SEGMENTS
, MR.RELATED_ITEM_DESCRIPTION
, QLA.LIST_HEADER_ID
, QLA.LIST_LINE_ID
, QLA.NAME
, QLA.OPERAND
, QLA.PRODUCT_UOM_CODE
, QLA.CURRENCY_CODE
, MR.RECIPROCAL_FLAG
FROM MFG_LOOKUPS MFG
, MTL_RELATED_ITEMS_ALL_V MR
, QP_LIST_ATTRIBUTES_V QLA
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE MFG.LOOKUP_CODE = MR.RELATIONSHIP_TYPE_ID
AND MFG.LOOKUP_TYPE = 'MTL_RELATIONSHIP_TYPES'
AND MR.RELATED_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND TO_CHAR(MR.RELATED_ITEM_ID)=QLA.PRODUCT_ATTR_VALUE
AND QLA.LIST_TYPE_CODE = 'PRL'
AND QLA.LIST_LINE_TYPE_CODE = 'PLL'
AND QLA.PRODUCT_ATTRIBUTE_CONTEXT='ITEM'
AND QLA.ACTIVE_FLAG='Y'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(QLA.QPL_START_DATE_ACTIVE)
, SYSDATE-1)
AND NVL(QLA.QPL_END_DATE_ACTIVE-1
, SYSDATE+1) UNION ALL SELECT DISTINCT QLA.ROW_ID
, MR.RELATED_ITEM_ID INVENTORY_ITEM_ID
, MFG.MEANING
, MR.INVENTORY_ITEM_ID RELATED_ITEM_ID
, MSI.PADDED_CONCATENATED_SEGMENTS RELATED_ITEM_NAME
, MR.INVENTORY_ITEM_DESCRIPTION RELATED_ITEM_DESCRIPTION
, QLA.LIST_HEADER_ID
, QLA.LIST_LINE_ID
, QLA.NAME
, QLA.OPERAND
, QLA.PRODUCT_UOM_CODE
, QLA.CURRENCY_CODE
, MR.RECIPROCAL_FLAG
FROM MFG_LOOKUPS MFG
, MTL_RELATED_ITEMS_ALL_V MR
, QP_LIST_ATTRIBUTES_V QLA
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE MFG.LOOKUP_CODE = MR.RELATIONSHIP_TYPE_ID
AND MFG.LOOKUP_TYPE = 'MTL_RELATIONSHIP_TYPES'
AND MR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MR.RECIPROCAL_FLAG = 'Y'
AND TO_CHAR(MR.INVENTORY_ITEM_ID) = QLA.PRODUCT_ATTR_VALUE
AND QLA.LIST_TYPE_CODE = 'PRL'
AND QLA.LIST_LINE_TYPE_CODE = 'PLL'
AND QLA.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND QLA.ACTIVE_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(QLA.QPL_START_DATE_ACTIVE)
, SYSDATE-1)
AND NVL(QLA.QPL_END_DATE_ACTIVE-1
, SYSDATE+1)