Product: | ONT - Order Management |
---|---|
Description: | This view is a view built on oe_processing_msgs and oe_processing_msgs_tl for de-normalization to resolve multilingual issue and names for certain ids. |
Implementation/DBA Data: | APPS.OE_PROCESSING_MSGS_VL |
SELECT MSG.TRANSACTION_ID
, MSG.REQUEST_ID
, NVL(MSG.MESSAGE_TEXT
, MT.MESSAGE_TEXT)
, MSG.ENTITY_CODE
, MSG.ENTITY_ID
, MSG.HEADER_ID
, MSG.LINE_ID
, MSG.ORDER_SOURCE_ID
, MSG.ORIGINAL_SYS_DOCUMENT_REF
, MSG.ORIGINAL_SYS_DOCUMENT_LINE_REF
, MSG.SOURCE_DOCUMENT_TYPE_ID
, MSG.SOURCE_DOCUMENT_ID
, MSG.SOURCE_DOCUMENT_LINE_ID
, MSG.ATTRIBUTE_CODE
, MSG.PROGRAM_UPDATE_DATE
, MSG.PROGRAM_APPLICATION_ID
, MSG.PROGRAM_ID
, MSG.LAST_UPDATE_LOGIN
, MSG.LAST_UPDATED_BY
, MSG.LAST_UPDATE_DATE
, MSG.CREATED_BY
, MSG.CREATION_DATE
, MSG.CONSTRAINT_ID
, MSG.PROCESS_ACTIVITY
, MSG.NOTIFICATION_FLAG
, MSG.ENTITY_REF
, MSG.CHANGE_SEQUENCE
, MSG.ORIG_SYS_SHIPMENT_REF
, MSG.TYPE
, MSG.MESSAGE_SOURCE_CODE
, HDR.ORDER_NUMBER
, OT.TRANSACTION_TYPE_ID
, OT.NAME
, LIN.LINE_NUMBER
, LIN.SHIPMENT_NUMBER
, LIN.OPTION_NUMBER
, LIN.ORDERED_ITEM
, P.USER_CONCURRENT_PROGRAM_NAME
, P.CONCURRENT_PROGRAM_ID
, U.USER_NAME
, WA.DISPLAY_NAME
, SO.NAME
, SO.CUSTOMER_NUMBER
, SO.ORGANIZATION_ID
, MSG.ORG_ID
, MSG.MESSAGE_STATUS_CODE
, HDR.CREATION_DATE
FROM OE_PROCESSING_MSGS MSG
, OE_ORDER_HEADERS HDR
, OE_ORDER_LINES LIN
, OE_TRANSACTION_TYPES_TL OT
, FND_CONCURRENT_PROGRAMS_TL P
, FND_CONCURRENT_REQUESTS R
, FND_USER U
, WF_PROCESS_ACTIVITIES WPA
, WF_ACTIVITIES_TL WA
, OE_SOLD_TO_ORGS_V SO
, OE_PROCESSING_MSGS_TL MT
WHERE HDR.HEADER_ID(+) = MSG.HEADER_ID
AND HDR.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID (+)
AND LIN.LINE_ID(+) = MSG.LINE_ID
AND R.REQUEST_ID(+) = MSG.REQUEST_ID
AND R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID(+)
AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID(+)
AND U.USER_ID(+) = MSG.CREATED_BY
AND WPA.INSTANCE_ID(+) = MSG.PROCESS_ACTIVITY
AND WPA.ACTIVITY_NAME = WA.NAME(+)
AND WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE(+)
AND (WA.VERSION IS NULL OR WA.VERSION = ( SELECT MAX(W.VERSION)
FROM WF_ACTIVITIES_TL W
WHERE W.ITEM_TYPE = WA.ITEM_TYPE
AND W.NAME=WA.NAME ))
AND HDR.SOLD_TO_ORG_ID = SO.ORGANIZATION_ID(+)
AND MSG.TRANSACTION_ID = MT.TRANSACTION_ID(+)
AND MT.SOURCE_LANG(+) = USERENV('LANG')
AND WA.LANGUAGE(+) = USERENV('LANG')
AND P.LANGUAGE(+) = USERENV('LANG')
AND NVL(MSG.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND OT.LANGUAGE(+) = USERENV('LANG')