View: AST_COLLTRL_HISTORY_V

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

View Text

SELECT OH.ORDER_NUMBER
, OL.REQUEST_DATE
, DECODE(OL.OPEN_FLAG||OL.BOOKED_FLAG||OL.CANCELLED_FLAG
, 'YNN'
, 'ENTERED'
, 'NYN'
, 'BOOKED'
, 'NNY'
, 'CANCELLED'
, 'UNKNOWN')
, CUST.PARTY_ID
, DECODE(CUST.PARTY_TYPE
, 'PERSON'
, CUST.PERSON_LAST_NAME||' '|| CUST.PERSON_FIRST_NAME
, CUST.PARTY_NAME)
, 'PHYSICAL'
, TO_CHAR(OH.MARKETING_SOURCE_CODE_ID)
, MTL.SEGMENT1
, MTL.DESCRIPTION
, OL.SHIPPED_QUANTITY
, OL.ORDERED_QUANTITY
, OL.ORDER_QUANTITY_UOM
, OL.SHIPPING_METHOD_CODE
, NULL
FROM MTL_SYSTEM_ITEMS MTL
, OE_ORDER_LINES_ALL OL
, HZ_PARTIES CUST
, HZ_CUST_ACCOUNTS ACCT
, OE_ORDER_HEADERS_ALL OH
WHERE MTL.ORGANIZATION_ID = OL.ORG_ID
AND MTL.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID
AND OL.HEADER_ID = OH.HEADER_ID
AND CUST.PARTY_ID = ACCT.PARTY_ID
AND ACCT.CUST_ACCOUNT_ID = OH.SOLD_TO_CONTACT_ID
AND OL.REQUEST_DATE IS NOT NULL UNION ( SELECT HIS.HIST_REQ_ID
, HIS.SUBMIT_DT_TM
, HIS.OUTCOME_CODE
, CON.PARTY_ID
, DECODE(CUST.PARTY_TYPE
, 'PERSON'
, CUST.PERSON_LAST_NAME||' '|| CUST.PERSON_FIRST_NAME
, CUST.PARTY_NAME)
, 'ELECTRONIC'
, HIS.SOURCE_CODE
, DELA.DELIVERABLE_NAME
, DELA.DESCRIPTION
, 0
, CON.QUANTITY
, NULL
, 'EMAIL'
, CON.DESTINATION_ADDR
FROM AMS_DELIVERABLES_ALL_TL DELA
, AMS_DELIVERABLES_ALL_B DELB
, JTF_FM_CONTENT_HISTORY CON
, JTF_FM_REQUEST_HISTORY HIS
, HZ_PARTIES CUST
WHERE DELA.DELIVERABLE_ID = DELB.DELIVERABLE_ID
AND DELB.JTF_AMV_ITEM_ID(+) = CON.MES_DOC_ID
AND HIS.HIST_REQ_ID = CON.HIST_REQ_ID
AND CON.CONTENT_TYPE = 10
AND CUST.PARTY_ID = CON.PARTY_ID )

Columns