VIEW: APPS.AST_LS_CAMPAIGNS_V

Source

View Text - Preformatted

SELECT distinct sc.source_code_id, SC.NAME, sch.SCHEDULE_NAME, NULL, NULL, CAM.ROLLUP_TYPE, CAM.STATUS_CODE, CAM.STATUS_DATE, SC.SOURCE_CODE, CAM.OWNER_USER_ID, CAM.CASCADE_SOURCE_CODE_FLAG, CAM.PARTNER_FLAG, CAM.ACTIVE_FLAG, CAM.PARENT_CAMPAIGN_ID, CAM.PRIORITY, CAM.APPLICATION_ID, CAM.FUND_SOURCE_TYPE, CAM.FUND_SOURCE_ID, CAM.ACTUAL_EXEC_START_DATE, CAM.ACTUAL_EXEC_END_DATE, SCH.ACTIVITY_ID MEDIA_ID, MED.MEDIA_NAME, MED.MEDIA_TYPE_CODE, MED.MEDIA_TYPE_NAME, SCH.MARKETING_MEDIUM_ID CHANNEL_ID, CHL.CHANNEL_NAME, LKUP.meaning CHANNEL_TYPE, CAM.INBOUND_URL, CAM.INBOUND_EMAIL_ID, CAM.INBOUND_PHONE_NO, CAM.FF_PRIORITY, CAM.FF_OVERRIDE_COVER_LETTER, CAM.FF_SHIPPING_METHOD, CAM.CONTENT_SOURCE, CAM.CC_CALL_STRATEGY, CAM.CC_MANAGER_USER_ID, NULL, NULL, NULL, SC.SOURCE_CODE_ID, CAM.ATTRIBUTE1, CAM.ATTRIBUTE2, CAM.ATTRIBUTE3, CAM.ATTRIBUTE4, CAM.ATTRIBUTE5, CAM.ATTRIBUTE6, CAM.ATTRIBUTE7, CAM.ATTRIBUTE8, CAM.ATTRIBUTE9, CAM.ATTRIBUTE10, CAM.ATTRIBUTE11, CAM.ATTRIBUTE12, CAM.ATTRIBUTE14, CAM.ATTRIBUTE13, CAM.ATTRIBUTE15, nvl(cat.category_desc,c.category_concat_segs), prd.CATEGORY_ID , cset.category_set_id, tl.campaign_name FROM ams_p_source_codes_v SC, AMS_ACT_OFFERS F, ams_p_source_codes_v OFR , ams_lookups lkup, AMS_CHANNELS_VL CHL, AMS_MEDIA_VL MED, ams_campaigns_all_b cam, ams_campaign_schedules_vl sch , ams_act_products prd, ENI_PROD_DEN_HRCHY_PARENTS_V cat, mtl_category_sets_vl cset, mtl_categories_v c, ams_campaigns_vl vl, ams_campaigns_all_tl tl WHERE SC.OBJECT_ID = F.ACT_OFFER_USED_BY_ID(+) AND F.QP_LIST_HEADER_ID = OFR.object_id(+) AND OFR.SOURCE_TYPE (+) = 'OFFR' AND OFR.STATUS (+) = 'ACTIVE' AND SC.STATUS IN ('ACTIVE','ONHOLD','COMPLETED') AND SC.SOURCE_TYPE != 'OFFR' AND sc.source_type = lkup.lookup_code(+) AND lkup.lookup_type(+) = 'AMS_SYS_ARC_QUALIFIER' AND sch.marketing_medium_id=CHL.CHANNEL_ID(+) AND sch.activity_id=MED.MEDIA_ID(+) AND cam.campaign_id(+)=sc.object_id AND cam.campaign_id=sch.campaign_id(+) AND prd.ACT_PRODUCT_USED_BY_ID(+) = sc.object_id AND prd.category_id = cat.category_id(+) AND prd.category_id = c.category_id(+) AND cset.category_set_id(+) = prd.category_set_id AND prd.ARC_ACT_PRODUCT_USED_BY(+) = 'CAMP' AND prd.level_type_code(+) = 'FAMILY' AND (vl.campaign_id(+) = sc.object_id AND vl.parent_campaign_id = tl.campaign_id(+) AND tl.language(+) = USERENV('LANG') )

View Text - HTML Formatted

SELECT DISTINCT SC.SOURCE_CODE_ID
, SC.NAME
, SCH.SCHEDULE_NAME
, NULL
, NULL
, CAM.ROLLUP_TYPE
, CAM.STATUS_CODE
, CAM.STATUS_DATE
, SC.SOURCE_CODE
, CAM.OWNER_USER_ID
, CAM.CASCADE_SOURCE_CODE_FLAG
, CAM.PARTNER_FLAG
, CAM.ACTIVE_FLAG
, CAM.PARENT_CAMPAIGN_ID
, CAM.PRIORITY
, CAM.APPLICATION_ID
, CAM.FUND_SOURCE_TYPE
, CAM.FUND_SOURCE_ID
, CAM.ACTUAL_EXEC_START_DATE
, CAM.ACTUAL_EXEC_END_DATE
, SCH.ACTIVITY_ID MEDIA_ID
, MED.MEDIA_NAME
, MED.MEDIA_TYPE_CODE
, MED.MEDIA_TYPE_NAME
, SCH.MARKETING_MEDIUM_ID CHANNEL_ID
, CHL.CHANNEL_NAME
, LKUP.MEANING CHANNEL_TYPE
, CAM.INBOUND_URL
, CAM.INBOUND_EMAIL_ID
, CAM.INBOUND_PHONE_NO
, CAM.FF_PRIORITY
, CAM.FF_OVERRIDE_COVER_LETTER
, CAM.FF_SHIPPING_METHOD
, CAM.CONTENT_SOURCE
, CAM.CC_CALL_STRATEGY
, CAM.CC_MANAGER_USER_ID
, NULL
, NULL
, NULL
, SC.SOURCE_CODE_ID
, CAM.ATTRIBUTE1
, CAM.ATTRIBUTE2
, CAM.ATTRIBUTE3
, CAM.ATTRIBUTE4
, CAM.ATTRIBUTE5
, CAM.ATTRIBUTE6
, CAM.ATTRIBUTE7
, CAM.ATTRIBUTE8
, CAM.ATTRIBUTE9
, CAM.ATTRIBUTE10
, CAM.ATTRIBUTE11
, CAM.ATTRIBUTE12
, CAM.ATTRIBUTE14
, CAM.ATTRIBUTE13
, CAM.ATTRIBUTE15
, NVL(CAT.CATEGORY_DESC
, C.CATEGORY_CONCAT_SEGS)
, PRD.CATEGORY_ID
, CSET.CATEGORY_SET_ID
, TL.CAMPAIGN_NAME
FROM AMS_P_SOURCE_CODES_V SC
, AMS_ACT_OFFERS F
, AMS_P_SOURCE_CODES_V OFR
, AMS_LOOKUPS LKUP
, AMS_CHANNELS_VL CHL
, AMS_MEDIA_VL MED
, AMS_CAMPAIGNS_ALL_B CAM
, AMS_CAMPAIGN_SCHEDULES_VL SCH
, AMS_ACT_PRODUCTS PRD
, ENI_PROD_DEN_HRCHY_PARENTS_V CAT
, MTL_CATEGORY_SETS_VL CSET
, MTL_CATEGORIES_V C
, AMS_CAMPAIGNS_VL VL
, AMS_CAMPAIGNS_ALL_TL TL
WHERE SC.OBJECT_ID = F.ACT_OFFER_USED_BY_ID(+)
AND F.QP_LIST_HEADER_ID = OFR.OBJECT_ID(+)
AND OFR.SOURCE_TYPE (+) = 'OFFR'
AND OFR.STATUS (+) = 'ACTIVE'
AND SC.STATUS IN ('ACTIVE'
, 'ONHOLD'
, 'COMPLETED')
AND SC.SOURCE_TYPE != 'OFFR'
AND SC.SOURCE_TYPE = LKUP.LOOKUP_CODE(+)
AND LKUP.LOOKUP_TYPE(+) = 'AMS_SYS_ARC_QUALIFIER'
AND SCH.MARKETING_MEDIUM_ID=CHL.CHANNEL_ID(+)
AND SCH.ACTIVITY_ID=MED.MEDIA_ID(+)
AND CAM.CAMPAIGN_ID(+)=SC.OBJECT_ID
AND CAM.CAMPAIGN_ID=SCH.CAMPAIGN_ID(+)
AND PRD.ACT_PRODUCT_USED_BY_ID(+) = SC.OBJECT_ID
AND PRD.CATEGORY_ID = CAT.CATEGORY_ID(+)
AND PRD.CATEGORY_ID = C.CATEGORY_ID(+)
AND CSET.CATEGORY_SET_ID(+) = PRD.CATEGORY_SET_ID
AND PRD.ARC_ACT_PRODUCT_USED_BY(+) = 'CAMP'
AND PRD.LEVEL_TYPE_CODE(+) = 'FAMILY'
AND (VL.CAMPAIGN_ID(+) = SC.OBJECT_ID
AND VL.PARENT_CAMPAIGN_ID = TL.CAMPAIGN_ID(+)
AND TL.LANGUAGE(+) = USERENV('LANG') )