VIEW: APPS.AST_LM_COLLATERAL_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')