Product: | AST - TeleSales |
---|---|
Description: | |
Implementation/DBA Data: | APPS.AST_EVENT_DELIVERABLES_V |
SELECT ADAT.DELIVERABLE_ID
, ADAT.JTF_AMV_ITEM_ID
, EVT.EVENT_OFFER_ID
, ADAT.INVENTORY_ITEM_ID
, ITM.ORGANIZATION_ID INVENTORY_ITEM_ORG_ID
, ADAT.DELIVERABLE_NAME
, NULL CONCATENATED_SEGMENTS
, ADAT.DESCRIPTION
, NULL NON_INV_QUANTITY_ON_HAND
, NULL PRIMARY_UOM_CODE
, NULL PRIMARY_UNIT_OF_MEASURE
, NULL NON_INV_QUANTITY_ON_ORDER
, ADAT.ACTUAL_AVAIL_FROM_DATE
, ADAT.ACTUAL_AVAIL_TO_DATE
, ADAT.CAN_FULFILL_ELECTRONIC_FLAG
, ADAT.CAN_FULFILL_PHYSICAL_FLAG
FROM AMS_OBJECT_ASSOCIATIONS AOA
, AMS_EVENT_OFFERS_ALL_B EVT
, AMS_DELIVERABLES_VL ADAT
, MTL_SYSTEM_ITEMS_B ITM
WHERE AOA.MASTER_OBJECT_TYPE IN ('EVEO'
, 'EONE')
AND AOA.MASTER_OBJECT_ID = EVT.EVENT_OFFER_ID
AND AOA.USING_OBJECT_TYPE = 'DELV'
AND AOA.FULFILL_ON_TYPE_CODE IN ('ON_ENROLL'
, 'ON_REG')
AND AOA.USING_OBJECT_ID = ADAT.DELIVERABLE_ID
AND ADAT.CAN_FULFILL_PHYSICAL_FLAG = 'Y'
AND ITM.INVENTORY_ITEM_ID = ADAT.INVENTORY_ITEM_ID
AND ITM.CUSTOMER_ORDER_FLAG = 'Y'
AND ITM.CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(ADAT.ACTUAL_AVAIL_FROM_DATE
, TRUNC(SYSDATE))
AND NVL(ADAT.ACTUAL_AVAIL_TO_DATE
, TRUNC(SYSDATE)) UNION ( SELECT ADAT.DELIVERABLE_ID
, ADAT.JTF_AMV_ITEM_ID
, EVT.EVENT_OFFER_ID
, ADAT.INVENTORY_ITEM_ID
, TO_NUMBER(NULL) INVENTORY_ITEM_ORG_ID
, ADAT.DELIVERABLE_NAME
, NULL CONCATENATED_SEGMENTS
, ADAT.DESCRIPTION
, NULL NON_INV_QUANTITY_ON_HAND
, NULL PRIMARY_UOM_CODE
, NULL PRIMARY_UNIT_OF_MEASURE
, NULL NON_INV_QUANTITY_ON_ORDER
, ADAT.ACTUAL_AVAIL_FROM_DATE
, ADAT.ACTUAL_AVAIL_TO_DATE
, ADAT.CAN_FULFILL_ELECTRONIC_FLAG
, ADAT.CAN_FULFILL_PHYSICAL_FLAG
FROM AMS_OBJECT_ASSOCIATIONS AOA
, AMS_EVENT_OFFERS_ALL_B EVT
, AMS_DELIVERABLES_VL ADAT
WHERE AOA.MASTER_OBJECT_TYPE IN ('EVEO'
, 'EONE')
AND AOA.MASTER_OBJECT_ID = EVT.EVENT_OFFER_ID
AND AOA.USING_OBJECT_TYPE = 'DELV'
AND AOA.FULFILL_ON_TYPE_CODE IN ('ON_ENROLL'
, 'ON_REG')
AND AOA.USING_OBJECT_ID=ADAT.DELIVERABLE_ID
AND ADAT.CAN_FULFILL_ELECTRONIC_FLAG = 'Y'
AND ADAT.JTF_AMV_ITEM_ID IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN NVL(ADAT.ACTUAL_AVAIL_FROM_DATE
, TRUNC(SYSDATE))AND NVL(ADAT.ACTUAL_AVAIL_TO_DATE
, TRUNC(SYSDATE)))