View: AST_LM_COLLATERAL_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_LM_COLLATERAL_V

View Text

SELECT DISTINCT B.DELIVERABLE_ID
, B.DELIVERABLE_NAME
, B.DESCRIPTION
, B.ACTUAL_AVAIL_FROM_DATE
, B.ACTUAL_AVAIL_TO_DATE
, B.USER_STATUS_ID
, E.NAME USER_STATUS
, B.STATUS_CODE
, B.NON_INV_QUANTITY_ON_HAND
, B.NON_INV_QUANTITY_ON_ORDER
, B.NON_INV_QUANTITY_ON_RESERVE
, B.INVENTORY_ITEM_ID
, A.ORGANIZATION_ID
, NULL
, NULL
, NULL
, NULL
, B.CHARGEBACK_UOM
, D.SOURCE_CODE_ID CAMPAIGN_ID
, D.NAME CAMPAIGN_NAME
, C.SOURCE_CODE
, B.CAN_FULFILL_PHYSICAL_FLAG
, B.CAN_FULFILL_ELECTRONIC_FLAG
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
FROM MTL_SYSTEM_ITEMS_VL A
, AST_CAMP_DELV_ASSOCIATIONS_V C
, AMS_DELIVERABLES_VL B
, AMS_USER_STATUSES_VL E
, AMS_P_SOURCE_CODES_V D
WHERE C.DELIVERABLE_ID(+) = B.DELIVERABLE_ID
AND (A.CUSTOMER_ORDER_ENABLED_FLAG = 'Y' OR B.INVENTORY_ITEM_ID IS NULL)
AND A.INVENTORY_ITEM_ID(+) = B.INVENTORY_ITEM_ID
AND D.SOURCE_CODE_ID (+) = C.SOURCE_CODE_ID
AND B.ACTIVE_FLAG = 'Y'
AND ((NVL(B.CAN_FULFILL_PHYSICAL_FLAG
, 'X') = 'Y'
AND (B.INVENTORY_ITEM_ID IS NOT NULL OR NVL(B.KIT_FLAG
, 'N')='Y')) OR (NVL(B.CAN_FULFILL_ELECTRONIC_FLAG
, 'X') = 'Y'
AND B.JTF_AMV_ITEM_ID IS NOT NULL ))
AND TRUNC(SYSDATE) BETWEEN NVL(B.ACTUAL_AVAIL_FROM_DATE
, TRUNC(SYSDATE))
AND DECODE(B.STATUS_CODE
, 'EXPIRED'
, SYSDATE
, NVL(B.ACTUAL_AVAIL_TO_DATE
, TRUNC(SYSDATE)))
AND E.USER_STATUS_ID=B.USER_STATUS_ID
AND B.STATUS_CODE IN ('AVAILABLE'
, 'EXPIRED')

Columns