Product: | AS - Sales Foundation |
---|---|
Description: | Mail blitzes view |
Implementation/DBA Data: | Not implemented in this database |
SELECT P.ROWID
, P.PROMOTION_ID
, P.LAST_UPDATE_DATE
, P.LAST_UPDATED_BY
, P.CREATION_DATE
, P.CREATED_BY
, P.LAST_UPDATE_LOGIN
, CR.ROWID
, CR.COLLATERAL_REQUEST_ID
, CR.LAST_UPDATE_DATE
, CR.LAST_UPDATED_BY
, CR.CREATION_DATE
, CR.CREATED_BY
, CR.LAST_UPDATE_LOGIN
, CR.COLLATERAL_REQUEST_ID
, CR.PROGRAM_APPLICATION_ID
, CR.PROGRAM_ID
, CR.PROGRAM_UPDATE_DATE
, P.CODE
, P.NAME
, P.TYPE
, P.ENABLED_FLAG
, P.STATUS
, P.COUNTRY
, P.LANGUAGE
, P.CURRENCY_CODE
, P.EXPECTED_PROMOTION_COST
, P.ACTUAL_PROMOTION_COST
, P.EXPECTED_NUM_OF_RESPONSES
, P.ACTUAL_NUM_OF_RESPONSES
, P.EXPECTED_REACH
, P.DESCRIPTION
, P.BLITZ_TYPE
, P.MAILBLITZ_DONOTMAIL_FLAG
, CR.STATUS
, CR.PARTIAL_SHIP_FLAG
, CR.SCHEDULED_DATE
, CR.LIST_ID
, CR.LIST_ENTRY_ID
, CR.LETTER_ID
, CR.MAIL_BLITZ_ID
, CR.COMPLETION_DATE
, CR.SHIPMENT_INSTRUCTIONS
, CR.SHIPMENT_METHOD
, P.ATTRIBUTE_CATEGORY
, P.ATTRIBUTE1
, P.ATTRIBUTE2
, P.ATTRIBUTE3
, P.ATTRIBUTE4
, P.ATTRIBUTE5
, P.ATTRIBUTE6
, P.ATTRIBUTE7
, P.ATTRIBUTE8
, P.ATTRIBUTE9
, P.ATTRIBUTE10
, P.ATTRIBUTE11
, P.ATTRIBUTE12
, P.ATTRIBUTE13
, P.ATTRIBUTE14
, P.ATTRIBUTE15
, ASLKP1.MEANING
, TERR.TERRITORY_SHORT_NAME
, ASLKP2.MEANING
, LIST.NAME
, LIST.LIST_ONLINE_FLAG
, LET.CODE
, LET.NAME
, LET.EXTERNAL_FILE_NAME
, WP.PATH
, WP.STARTUP_COMMAND
, WP.LETTER_LOCATION_PATH
, ASLKP3.MEANING
, P.PRICE_LIST_ID
FROM AS_LOOKUPS ASLKP1
, AS_LOOKUPS ASLKP2
, AS_LOOKUPS ASLKP3
, FND_TERRITORIES_TL TERR
, AS_LISTS LIST
, AS_WORD_PROCESSORS WP
, AS_COLLATERAL_REQ CR
, AS_PROMOTIONS LET
, AS_PROMOTIONS P
WHERE P.PROMOTION_ID = CR.MAIL_BLITZ_ID
AND P.TYPE = 'B'
AND P.BLITZ_TYPE = 'MAIL'
AND P.STATUS = ASLKP1.LOOKUP_CODE
AND ASLKP1.LOOKUP_TYPE = 'BLITZ_STATUS'
AND LET.WORD_PROCESSOR_ID = WP.WORD_PROCESSOR_ID(+)
AND P.COUNTRY = TERR.TERRITORY_CODE(+)
AND TERR.LANGUAGE(+) = USERENV('LANG')
AND P.LANGUAGE = ASLKP2.LOOKUP_CODE(+)
AND ( (P.LANGUAGE IS NULL) OR (ASLKP2.LOOKUP_TYPE = 'LANGUAGE'))
AND CR.LIST_ID = LIST.LIST_ID
AND CR.LETTER_ID = LET.PROMOTION_ID(+)
AND CR.SHIPMENT_METHOD = ASLKP3.LOOKUP_CODE(+)
AND ( (CR.SHIPMENT_METHOD IS NULL) OR (ASLKP3.LOOKUP_TYPE = 'FREIGHT_CARRIER'))