VIEW: APPS.AST_COLLTRL_HISTORY_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )