API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 9th, 2018
315
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 85.88 KB | None | 0 0
raw download clone embed print report
  1.  
  2.  
  3. %let ww_table = P5P_CPGN_TBLS.LMK_UPG_APR;
  4. %let ww_target = TARGET_FNL;
  5.  
  6. proc SQL stimer;
  7.     CONNECT TO teradata  (USER = &td_userid
  8.                          password = &td_password
  9.                          tdpid = tdpc
  10.                          fastload=yes
  11.                          mode=teradata);
  12.  
  13.  
  14. EXECUTE(DROP TABLE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER ) BY teradata;
  15.  
  16. EXECUTE( CREATE SET TABLE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER ,NO FALLBACK ,
  17.      NO BEFORE JOURNAL,
  18.      NO AFTER JOURNAL,
  19.      CHECKSUM = DEFAULT
  20.      (
  21.   SBSCR_NBR               VARCHAR(20)
  22.  ,CUST_SYS_CD             CHAR(2)
  23.  ,ACCT_NBR                VARCHAR(20)
  24.  ,AAL_FLAG                CHAR(1) DEFAULT 'N'
  25.  ,ACCT_CR_CLASS_CD        CHAR(5)
  26.  ,ACCT_CR_CLASS_PRIME_CD  CHAR(1)
  27.  ,ACCT_SIZE_QTY           INTEGER
  28.  ,ACCT_TENURE_STRT_DT     DATE
  29.  ,ADR_LINE_1_TXT          VARCHAR(100)
  30.  ,ADR_LINE_2_TXT          VARCHAR(100)
  31.  ,ATST                    CHAR(2)
  32.  ,BILL_LANG_CD            CHAR(3)
  33.  ,BUS_NME                 VARCHAR(90)
  34.  ,CAMPAIGN_CD             CHAR(10)
  35.  ,CAMPAIGN_CD_NBA         CHAR(10)
  36.  ,CAMPAIGN_CD_SMS         CHAR(10)
  37.  ,CAMPAIGN_DESC           VARCHAR(62)
  38.  ,_SRT                    SMALLINT
  39.  ,CAMPAIGN_DESC_NBA       VARCHAR(62)
  40.  ,_SRT_NBA                SMALLINT
  41.  ,CAMPAIGN_DESC_SMS       VARCHAR(62)
  42.  ,_SRT_SMS                SMALLINT  
  43.  ,CAS_APRV_SBSCR_QTY      SMALLINT
  44.  ,CHURN_DECILE_SUB        SMALLINT
  45.  ,CHURN_DECILE_BAN        SMALLINT
  46.  ,CHURN_REF_DATE          DATE
  47.   ,CONTROL_GROUP_FLAG      CHAR(1) DEFAULT 'N'
  48.   ,CTY_NME                 VARCHAR(50)
  49.  ,BAN_RANK                BYTEINT DEFAULT 0
  50.  ,BAN_RANKs                BYTEINT DEFAULT 0 /* For SMS */
  51.  ,BAN_MIXED_NE_FLAG       CHAR(1) DEFAULT 'N'
  52.  ,BAN_MIXED_NES_FLAG      CHAR(1) DEFAULT 'N'
  53.  ,DCSN_MAKER_FLAG         BYTEINT DEFAULT 3
  54.  ,DMAKER_MIN_NBR          CHAR(10)
  55.  ,DMAKER_SBSCR_NBR        CHAR(20)
  56.  ,DMAKER_DEVICE           VARCHAR(30)
  57.  ,DEV_MODEL_TYPE_NME      VARCHAR(30)
  58.  ,DEV_MFR_NME             VARCHAR(15)
  59.  ,DEV_SKU_NBR             VARCHAR(24)
  60.  ,DM_ELIGIBLE_FLAG        CHAR(1) DEFAULT 'N'
  61.  ,ELIGIBLE_FLAG           CHAR(1) DEFAULT 'N'
  62.  ,EM_ELIGIBLE_FLAG        CHAR(1) DEFAULT 'N'
  63.  ,EM_ELIGIBLE_BAN_FLAG    CHAR(1) DEFAULT 'N'
  64.  ,EMAIL_ADR               VARCHAR(120)
  65.  ,EMAIL_ENG_LVL           CHAR(1)
  66.  ,EMAIL_TYPE              CHAR(1) /* (B)an or (S)ub */
  67. /* ,EMAIL_OPEN_FLAG         DEFAULT 'N'*/
  68.  ,FRST_NME                VARCHAR(32)
  69.  ,LST_NME                 VARCHAR(60)
  70.  ,ACCEL_FLAG              CHAR(1) DEFAULT 'N'
  71.  ,IPHONE_FOREVER_FLAG     CHAR(1) DEFAULT 'N'
  72.  ,IPHONE_FOREVER_GEN_FLAG CHAR(1) DEFAULT 'N'
  73.  ,IPHONE_FORLIFE_FLAG     CHAR(1) DEFAULT 'N'
  74.  ,LGCY_LEASE_PRICE_CD     CHAR(1)
  75.  ,LIAB_CD                 CHAR(2)
  76.  ,COMMIT_STRT_DT          DATE
  77.  ,COMMIT_END_DT           DATE
  78.  ,CNTRC_PRXMT_LVL_0_DES   VARCHAR(30)
  79.  ,CNTRC_XPIR_MO_QTY       INTEGER
  80.  ,DEV_PURCH_TYPE          VARCHAR(30)
  81.  ,HULU_FLAG               CHAR(1) DEFAULT 'N'
  82. /* ,IFL_ACCEL_FLAG          CHAR(1) DEFAULT 'N'*/
  83.  ,LEASE_TYPE_DESC         VARCHAR(50)
  84.  ,LOAN_TERM_QTY           SMALLINT
  85. /* ,MIDWEST_FLAG            CHAR(1) DEFAULT 'N'*/
  86.  ,MIN_NBR                 CHAR(10)
  87.  ,MTV                     BYTEINT
  88.  ,NBA_OFFER_ID            VARCHAR(10)
  89.  ,PRICE_PLAN_FMLY_DES     VARCHAR(60)
  90.  ,PROGRAM_CD              CHAR(10) DEFAULT '13YG7QKBA '
  91. /* ,RESPONDER_FLAG          CHAR(1) DEFAULT 'N'*/
  92.  ,RLOF                    INTEGER
  93.  ,SBSCR_STRT_DT           DATE
  94.  ,STATE_CD                CHAR(2)
  95.  ,STORE_FLAG              CHAR(1) DEFAULT 'N'
  96.  ,STORE_WUF_FLAG          CHAR(1) DEFAULT 'N'
  97.  ,STORE_ADR               VARCHAR(100)
  98.  ,STORE_CITY              VARCHAR(50)
  99.  ,STORE_NBR               CHAR(5)
  100.  ,STORE_STATE_ZIP         CHAR(10)
  101.  ,STORE_PHN_NBR           CHAR(12)
  102.  ,TARGET_GRP              CHAR(10)
  103.  ,OFFER_GRP               VARCHAR(30)
  104.  ,OFFER_VERSION           CHAR(15)
  105.  ,TARGET_PRIORITY         SMALLINT DEFAULT 99
  106.  ,RACK_RATE_FLAG         VARCHAR(30) DEFAULT 'N'
  107.  ,TIER1_DT                DATE
  108.  ,TIER2_DT                DATE
  109.  ,SMS_ELIGIBLE_FLAG       CHAR(1) DEFAULT 'Y'
  110.  ,SMS_TEST_FLAG           CHAR(1) DEFAULT 'N'
  111.  ,TREATMENT_ID            CHAR(10)
  112.  ,TREATMENT_ID_SMS        CHAR(10)
  113.  ,TREATMENT_PLAN          SMALLINT
  114.  ,TREATMENT_PLAN_DATE     DATE
  115.  ,TREATMENT_PLAN_MODEL    INTEGER compress (8029,NULL)
  116.  ,ZIP_CD                  CHAR(5)
  117.  ,ZIP_PLUS_4_CD           CHAR(4)
  118.  ,X_BAD_ADR               BYTEINT DEFAULT 0
  119.  ,X_CAMPAIGN              BYTEINT DEFAULT 0
  120.  ,X_CANADA                BYTEINT DEFAULT 0  /* EM and SMS Only  */
  121. /* ,X_CHURN                 BYTEINT DEFAULT 1*/
  122.  ,X_COLLECTIONS           BYTEINT DEFAULT 0
  123.  ,X_COLLECTIONS30         BYTEINT DEFAULT 0
  124.   ,X_COLLECTIONS2           BYTEINT DEFAULT 0
  125.  ,X_CONTACT_STRATEGY_EM      BYTEINT DEFAULT 0
  126.  ,X_CONTACT_STRATEGY_SMS      BYTEINT DEFAULT 0
  127.  ,X_CORP                  BYTEINT DEFAULT 0
  128.  ,X_DO_NOT_SELL           BYTEINT DEFAULT 0
  129.  ,X_DNM                   BYTEINT DEFAULT 0
  130.  ,X_EMAIL                 BYTEINT DEFAULT 0
  131.  ,X_EMAIL_UNENGAGE        BYTEINT DEFAULT 0
  132.  ,X_FREE_PLAN             BYTEINT DEFAULT 0
  133. /* ,X_HARVEY_ZIPS           BYTEINT DEFAULT 0*/
  134.  ,X_IB_AT_CC              BYTEINT DEFAULT 1
  135.  ,X_IB                    BYTEINT DEFAULT 0
  136.  ,X_IPFG8                 BYTEINT DEFAULT 0
  137. /* ,X_LEASE                 BYTEINT DEFAULT 0 */
  138.  ,X_MILITARY_SUSPEND      BYTEINT DEFAULT 0
  139.  ,X_MINOR                 BYTEINT DEFAULT 0
  140. /* ,X_PREORDER              BYTEINT DEFAULT 0*/
  141.  ,X_SEASONAL_SUSPEND      BYTEINT DEFAULT 0
  142.  ,X_SMS_BLACKLIST         BYTEINT DEFAULT 0
  143.  ,X_SMS_CAPABLE           BYTEINT DEFAULT 0
  144.  ,X_SMS_OPT_OUT           BYTEINT DEFAULT 0
  145.  ,X_SMS_SPRINT_DNC        BYTEINT DEFAULT 0
  146.  ,X_SMS_STATES            BYTEINT DEFAULT 0
  147. /* ,X_SMS_SPANISH           BYTEINT DEFAULT 0*/
  148.  ,X_SPANISH               BYTEINT DEFAULT 0
  149.  ,X_TP_BLANK              BYTEINT DEFAULT 1
  150.  ,X_TRADE                 BYTEINT DEFAULT 0
  151.  ,X_UNIVERSAL_CONTROL     BYTEINT DEFAULT 0
  152.  ,X_UPGRADE                 BYTEINT DEFAULT 0
  153.  ,X_VERSION_EXCLUDE_EM       BYTEINT DEFAULT 0
  154.  ,X_VERSION_EXCLUDE_SMS       BYTEINT DEFAULT 0
  155. /* ,X_WI_SUBSIDY            BYTEINT DEFAULT 0*/
  156. )
  157. UNIQUE PRIMARY INDEX(SBSCR_NBR,CUST_SYS_CD) ) BY teradata;
  158.  
  159.  
  160. EXECUTE(INSERT INTO P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER (
  161.            SBSCR_NBR
  162.           ,CUST_SYS_CD
  163.           ,ACCT_NBR
  164.           ,AAL_FLAG
  165. /*        ,ACCEL_FLAG*/
  166.           ,ACCT_CR_CLASS_CD
  167.           ,ACCT_CR_CLASS_PRIME_CD
  168.           ,ACCT_SIZE_QTY
  169.           ,ACCT_TENURE_STRT_DT
  170.           ,ATST
  171.           ,BILL_LANG_CD
  172.           ,CAS_APRV_SBSCR_QTY
  173.           ,DCSN_MAKER_FLAG
  174. /*        ,DCSN_MAKER_SBSCR_NBR*/
  175.           ,DEV_MFR_NME
  176.           ,DEV_MODEL_TYPE_NME
  177.           ,DEV_SKU_NBR
  178.           ,IPHONE_FOREVER_FLAG
  179.           ,IPHONE_FOREVER_GEN_FLAG
  180.           ,IPHONE_FORLIFE_FLAG
  181.           ,LGCY_LEASE_PRICE_CD
  182.           ,LIAB_CD  
  183.           ,CNTRC_PRXMT_LVL_0_DES
  184.           ,CNTRC_XPIR_MO_QTY
  185.           ,COMMIT_STRT_DT
  186.           ,COMMIT_END_DT
  187.           ,DEV_PURCH_TYPE
  188.           ,LEASE_TYPE_DESC
  189.           ,LOAN_TERM_QTY
  190.           ,MIN_NBR
  191.           ,PRICE_PLAN_FMLY_DES
  192.           ,RLOF
  193.           ,SBSCR_STRT_DT
  194.           ,TARGET_GRP
  195.           ,OFFER_GRP
  196.           ,TARGET_PRIORITY
  197.           ,RACK_RATE_FLAG
  198.           ,TIER1_DT
  199.           ,TIER2_DT
  200.           ,X_CORP
  201.           ,X_FREE_PLAN
  202.           ,X_IPFG8
  203. /*        ,X_DATA*/
  204.           ,X_MILITARY_SUSPEND
  205.           ,X_SEASONAL_SUSPEND
  206.           ,X_SMS_CAPABLE
  207.           ,X_SMS_STATES
  208.           ,X_SPANISH
  209.           ,X_TRADE
  210.           ,X_UPGRADE
  211.           )
  212.  
  213.        SELECT VM33.SBSCR_NBR
  214.               ,VM33.CUST_SYS_CD
  215.               ,VM33.ACCT_NBR
  216.               ,CASE WHEN CAS_APRV_SBSCR_QTY - ACCT_SIZE_QTY  > 0 THEN 'Y' ELSE 'N' END AAL_FLAG
  217. /*            ,CASE WHEN VM33.SBSCR_NBR = IPA.SBSCR_NBR THEN 'Y' ELSE 'N' end ACCEL_FLAG*/
  218. /*            ,CASE WHEN ( PRICE_PLAN_FMLY_CD <> 5600 AND ELGBL_LINE_QTY > 0 ) */
  219. /*                   or (PRICE_PLAN_FMLY_CD = 5600 and ACCT_SIZE_QTY < 5 AND ELGBL_LINE_QTY > 0 ) THEN 'Y' ELSE 'N' END AAL_FLAG*/
  220.               ,VM33.ACCT_CR_CLASS_CD
  221.               ,ACCT_CR_CLASS_PRIME_CD
  222.               ,ACCT_SIZE_QTY
  223.               ,ACCT_TENURE_STRT_DT
  224.               ,VM33.ACCT_TYPE_CD||VM33.ACCT_SUB_TYPE_CD AS ATST
  225.               ,VM33.BILL_LANG_CD
  226.               ,CAS_APRV_SBSCR_QTY
  227.               ,CASE WHEN VM33.SBSCR_NBR = DCSN_MAKER_SBSCR_NBR THEN 2
  228.                     WHEN VM33.SBSCR_NBR = OLDST_ACTV_SBSCR_NBR THEN 3 ELSE 4 END DCSN_MAKER_FLAG
  229. /*            ,DCSN_MAKER_SBSCR_NBR*/
  230.               ,DEV_MFR_NME
  231.               ,DEV_MODEL_TYPE_NME
  232.               ,DEV_SKU_NBR
  233.               ,CASE WHEN DP.LEASE_TYPE_DESC LIKE ( 'iPhone Forever%' ) THEN 'Y' ELSE 'N' END IPHONE_FOREVER_FLAG
  234.               ,CASE WHEN DP.LEASE_TYPE_DESC = 'iPhone Forever - Generational' THEN 'Y' ELSE 'N' END IPHONE_FOREVER_GEN_FLAG
  235.               ,CASE WHEN DP.LEASE_TYPE_DESC = 'iPhone for Life' THEN 'Y' ELSE 'N' END IPHONE_FORLIFE_FLAG
  236.               ,LGCY_LEASE_PRICE_CD
  237.               ,VM33.LIAB_CD
  238.               ,VLF0.CNTRC_PRXMT_LVL_0_DES
  239.               ,VLF0.CNTRC_XPIR_MO_QTY            
  240.               ,DP.COMMIT_STRT_DT
  241.               ,DP.COMMIT_END_DT
  242.               ,DP.DEV_PURCH_TYPE
  243.               ,DP.LEASE_TYPE_DESC
  244.               ,DP.LOAN_TERM_QTY
  245.               ,VM33.MIN_NBR
  246.               ,PRICE_PLAN_FMLY_DES
  247.               ,VMSR.LIFE_ON_FILE_MO_CNT AS RLOF
  248.               ,SBSCR_STRT_DT
  249.               ,TRIM(UPPER(&ww_target)) AS TARGET_GRP
  250.               ,WW.OFFER_GRP_FNL AS OFFER_GRP
  251.               ,CASE WHEN TARGET_GRP IN ('109C') THEN 1
  252.                     WHEN TARGET_GRP IN ('106B') THEN 2
  253.                     WHEN TARGET_GRP LIKE ('110%') THEN 3
  254.                     WHEN TARGET_GRP IN ('102') THEN 4
  255.                     WHEN TARGET_GRP IN ('103') THEN 5
  256.                     WHEN TARGET_GRP IN ('101') THEN 6
  257.                     WHEN TARGET_GRP IN ('100') THEN 7
  258.                ELSE 99 END AS TARGET_PRIORITY
  259.               ,CASE WHEN TARGET_GRP IN ('100','101','102','103','102C','103C') THEN 'Y' ELSE 'N' END RACK_RATE_FLAG
  260.               ,L.ELIG_DATE AS TIER1_DT
  261.               ,SB.ELIG_DATE AS TIER2_DT
  262.               ,CORP_XCLUD_CD AS X_CORP
  263.               ,CASE WHEN VM33.PRICE_PLAN_CD IN ('PDSA0582','LPDSA0507') THEN 1 ELSE 0 END X_FREE_PLAN
  264.               ,CASE WHEN DP.LEASE_TYPE_DESC = 'iPhone Forever - Generational' AND
  265.                          DEV_MODEL_TYPE_NME LIKE '%iPhone 8%' THEN 1 ELSE 0 END X_IPFG8
  266. /*            ,CASE WHEN DATA_CD_QTY = 1 THEN 0 ELSE 1 END X_DATA*/
  267.               ,CASE WHEN MLTRY_SPND_CD = 1  THEN 1 ELSE 0 END X_MILITARY_SUSPEND
  268.               ,CASE WHEN SEASONAL_PLAN_CD_QTY = 1 THEN 1 ELSE 0 END X_SEASONAL_SUSPEND
  269.               ,CASE WHEN VM33.MIN_NBR IS NULL OR VM33.MIN_NBR = '' OR
  270.                     SMS_DEV_CPBL_CD = 'N' OR SMS_NCMG_BLK_CD = 1 THEN 1 ELSE 0 END X_SMS_CAPABLE /* Not Capable */
  271.               ,CASE WHEN VM33.STATE_CD = 'HI' THEN 1 ELSE 0 END X_SMS_STATES           
  272.               ,CASE WHEN BILL_LANG_CD = 'SPA' THEN 1 ELSE 0 END X_SPANISH
  273.               ,CASE WHEN vm33.ACCT_TYPE_CD||vm33.ACCT_SUB_TYPE_CD IN ('I4','I5') THEN 0
  274.                     WHEN TRADE_PRICE_PLAN_CD = 'Y' THEN 1 ELSE 0 END X_TRADE
  275. /*            ,case WHEN EMPL_CD = 'Y' or SWAC_PRICE_PLAN_CD = 'Y' or DEMO_PRICE_PLAN_CD = 'Y' THEN 1 ELSE 0 END X_TRADE*/
  276.               ,CASE WHEN ( (SB.ELIG_DATE IS NOT NULL AND SB.ELIG_DATE <= DATE )
  277.                             OR (L.ELIG_DATE IS NOT NULL AND L.ELIG_DATE <= DATE ) ) THEN 0 ELSE 1 END X_UPGRADE
  278.        FROM P5P_MEDP_VIEW.VM33_CSLD_CPGN VM33
  279.          LEFT OUTER JOIN
  280.             P7P_PROD_VIEW.VLE0_DEV VLE0
  281.               ON VM33.DEV_SKU_NBR = VLE0.WRLS_DEV_SKU_NBR
  282.          AND VLE0.SYS_STUS_CD = 'A'
  283.         LEFT OUTER JOIN
  284.            P7P_PROD_VIEW.VMSR_SBSCR_REBATE_ELGBL_SNPSHT VMSR
  285.          ON VM33.SBSCR_NBR = VMSR.SBSCR_NBR
  286.          AND VM33.CUST_SYS_CD = VMSR.CUST_SYS_CD
  287.          AND VMSR.YR_MO_NBR = ( SELECT MAX(yr_mo_nbr) FROM P7P_PROD_VIEW.VMSR_SBSCR_REBATE_ELGBL_SNPSHT
  288.                                 WHERE yr_mo_nbr >= (sel EXTRACT(YEAR FROM date-60) *100 + EXTRACT(MONTH FROM date-60)) )
  289.             LEFT OUTER JOIN
  290.            P5P_MEDP_TBLS.VMSRX_REBATE_ELGBL_SNPSHT L
  291.          ON VM33.SBSCR_NBR = L.SBSCR_NBR
  292.          AND L.REBATE_TYPE = 'LEASE'
  293.          AND L.YR_MO_NBR = ( SELECT MAX(yr_mo_nbr) FROM P5P_MEDP_TBLS.VMSRX_REBATE_ELGBL_SNPSHT WHERE REBATE_TYPE = 'LEASE'
  294.                              AND yr_mo_nbr >= (sel EXTRACT(YEAR FROM date-60) *100 + EXTRACT(MONTH FROM date-60)) )
  295.         LEFT OUTER JOIN
  296.             P5P_MEDP_TBLS.VMSRX_REBATE_ELGBL_SNPSHT SB
  297.          ON VM33.SBSCR_NBR = SB.SBSCR_NBR
  298.          AND SB.REBATE_TYPE = 'SBSDY'
  299.          AND SB.YR_MO_NBR = ( SELECT MAX(yr_mo_nbr) FROM P5P_MEDP_TBLS.VMSRX_REBATE_ELGBL_SNPSHT WHERE REBATE_TYPE = 'SBSDY'
  300.                               AND yr_mo_nbr >= (sel EXTRACT(YEAR FROM date-60) *100 + EXTRACT(MONTH FROM date-60)) )
  301.          LEFT OUTER JOIN
  302.           P7P_PROD_VIEW.VLE7_PRICE_PLAN VLE7
  303.           ON VM33.PRICE_PLAN_CD = VLE7.PRICE_PLAN_CD
  304.           AND VM33.CUST_SYS_CD = VLE7.CUST_SYS_CD
  305.                   /* AND PRICE_PLAN_FMLY_DES IN ( 'Individual Unlimited','Sprint Family Share Pack')*/
  306.           LEFT OUTER JOIN
  307.              P5P_MEDP_VIEW.SBSCR_DEV_PURCH_SNPSHT DP
  308.           ON VM33.SBSCR_NBR = DP.SBSCR_NBR
  309.           AND dp.YR_MO_NBR = ( SELECT MAX(YR_MO_NBR) FROM P5P_MEDP_VIEW.SBSCR_DEV_PURCH_SNPSHT
  310.                             WHERE yr_mo_nbr >= (sel EXTRACT(YEAR FROM date-60) *100 + EXTRACT(MONTH FROM date-60)) )
  311.           LEFT OUTER JOIN  
  312.             P7P_PROD_VIEW.VLF0_CNTRC_PRXMT VLF0
  313.               ON DP.CURRENT_PRXMT_CD = VLF0.CNTRC_PRXMT_CD
  314.           LEFT OUTER JOIN
  315.           ( SELECT SBSCR_NBR, OFFER_GRP_FNL, TARGET_FNL FROM &ww_table WHERE device_grp = 'IPHONE' ) WW
  316.           ON VM33.SBSCR_NBR = WW.SBSCR_NBR
  317. /*        LEFT OUTER JOIN*/
  318. /*         P5P_CPGN_TBLS.LMK_IP_ACCEL IPA*/
  319. /*         ON VM33.SBSCR_NBR = IPA.SBSCR_NBR*/
  320.       WHERE ACT_CD = 'Y'
  321.         AND VM33.NW_CD = 'C'
  322. /*        and VM33.LIAB_CD = 'IL' */
  323.         AND VM33.PNDG_PORT_CD = 0
  324.         AND VM33.SALS_CHURN_IS_REVNU_CD = 1
  325.         AND VM33.SBSCR_NBR = WW.SBSCR_NBR
  326.         AND VM33.DEV_MODEL_TYPE_NME LIKE '%iPhone%'
  327.         AND VM33.DEV_MODEL_TYPE_NME NOT LIKE '%iPhone x%'
  328.         AND VM33.SBSCR_NBR IN (SELECT sbscr_nbr FROM CRP_PROD_VIEW.VN10_PEGA_SPINE_NIGHTLY
  329.                              WHERE cust_type_nme  IN ( 'Postpaid','SmallBiz')
  330.                              AND cloned_sbscr_cd  = 'N' AND bill_zero_bal_cd  = 'N' )
  331. ) BY teradata;
  332.  
  333. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  334. /* set TARGET_PRIORITY = 9 ) BY TERADATA;*/
  335. /**/
  336. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  337. /* set TARGET_PRIORITY = 1*/
  338. /* where target_grp = '106') BY TERADATA;*/
  339.  
  340.  
  341. * Populate Decision Maker sub AND PTN FOR SMS test
  342.     - ONLY population WHERE SMS eligible;
  343.  
  344.  
  345. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  346.    (SELECT B.ACCT_NBR _acct_nbr, B.MIN_NBR, B.SBSCR_NBR, B.DEV_MODEL_TYPE_NME
  347.     FROM P7P_PROD_VIEW.VMUL_ACCT_DCSN_MAKER a,
  348.          P5P_MEDP_VIEW.VM34_CSLD_DLY B
  349.     WHERE A.DCSN_MAKER_SBSCR_NBR = B.SBSCR_NBR
  350.     AND A.ACCT_NBR = B.ACCT_NBR
  351.     AND ACT_CD = 'Y'
  352.     AND PROMO_DNSMS_CD = 0 ) T1
  353.  SET DMAKER_MIN_NBR = T1.MIN_NBR
  354.     ,DMAKER_SBSCR_NBR = T1.SBSCR_NBR
  355.     ,DMAKER_DEVICE = T1.DEV_MODEL_TYPE_NME
  356.  WHERE ACCT_NBR = T1._ACCT_NBR ) BY teradata;
  357.  
  358.        * THESE GUYS ARE ACCELERATED ;
  359.  
  360. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  361.  SET ACCEL_FLAG = 'Y'
  362.  WHERE sbscr_nbr IN (SELECT sbscr_nbr FROM P7P_PROD_VIEW.VMGM_ARM_EXT_REBATE
  363.                      WHERE EXT_REBATE_INDCR_CD = 'Y')  ) BY teradata;
  364.  
  365. * HULU FLAG ;
  366.  
  367. *
  368. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  369. /*  set HULU_FLAG = 'Y'*/
  370. /*  where sbscr_nbr in (SELECT SBSCR_NBR FROM xx_Tst0_tbls.usc974n_monthly_qoe2 */
  371. /*                      where yr_mo_nbr = 201711 and qoe_grouping = 'B.GOOD' GROUP BY 1)*/
  372. /*  and sbscr_nbr in ( SELECT SBSCR_NBR FROM  &ww_table*/
  373. /*                       where /*churn_risk = '1 HIGH'*/
  374. /*                       and*/ DATA_TYPE_RLLUP = '1 UNL') ) BY teradata;*/
  375. /**/
  376. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  377. /*  set HULU_FLAG = 'N'*/
  378. /*  where ACCT_NBR IN ( SELECT ACCT_NBR FROM P5P_MEDP_VIEW.FINAL_SPRINT_HULU_MATCHES*/
  379. /*                      WHERE unlmtd_datA = 'ineligible' GROUP BY 1)*/
  380. /*  OR SBSCR_NBR IN ( SELECT SBSCR_NBR FROM P7P_PROD_VIEW.VMTJ_SBSCR_PLAN_TRAN*/
  381. /*                    WHERE SRVC_CD = 'HULUVOD01' AND SRVC_TRMTN_DT IS NULL GROUP BY 1) ) by teradata;*/
  382.  
  383. /* Eligible for Hulu:
  384. • Good QOE score (avg 3.1) – Column:QOE_GROUPING = ‘B.GOOD’= qoe_score >= 3.1
  385. • On Unlimited plan
  386. Exclude:  
  387. Sprint SOC (have it) - HULUVOD01
  388. Exclude ineligible:
  389. P5P_MEDP_VIEW.FINAL_SPRINT_HULU_MATCHES
  390. UNLMTD_DATA = ineligible
  391. */
  392.  
  393.  
  394. * Exclude BANs FROM Courtneys TMO program LAST 30 days - EM/SMS exclusion ONLY ;
  395.  
  396. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  397.  SET X_CAMPAIGN = 1
  398.  WHERE ACCT_NBR IN (SELECT ACCT_NBR FROM P7P_PROD_VIEW.VMTL_CRM_WRLS_CPGN_HIST WHERE CPGN_TRK_CD IN ('13ZTDAY1A','13X3WKCDA','14110SPIA') AND CPGN_EXECN_DT >= DATE-30 GROUP BY 1 )
  399.   OR SBSCR_NBR IN  (SELECT SBSCR_NBR FROM P7P_PROD_VIEW.VMTL_CRM_WRLS_CPGN_HIST WHERE CPGN_TRK_CD IN ('13ZTDAY1A','13X3WKCDA','14110SPIA') AND CPGN_EXECN_DT >= DATE-30 GROUP BY 1 )
  400. ) BY teradata; 
  401.  
  402. * Exclude abandoned cart leads FOR 7 days ;
  403.  
  404. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  405.  SET X_CAMPAIGN = 1
  406.  WHERE ACCT_NBR IN (SELECT ACCT_NBR FROM P7P_PROD_VIEW.VMTL_CRM_WRLS_CPGN_HIST WHERE CPGN_TRK_CD IN ('13JWD5DBA') AND CPGN_EXECN_DT >= DATE-7 GROUP BY 1 )
  407.   OR SBSCR_NBR IN  (SELECT SBSCR_NBR FROM P7P_PROD_VIEW.VMTL_CRM_WRLS_CPGN_HIST WHERE CPGN_TRK_CD IN ('13JWD5DBA') AND CPGN_EXECN_DT >= DATE-7 GROUP BY 1 )
  408. ) BY teradata; 
  409.                  
  410.                      
  411. * Exclude McKinsey collections GROUP ;
  412.  
  413. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  414.  SET X_COLLECTIONS2 = 1
  415.  WHERE ACCT_NBR IN (SELECT A.ACCT_NBR
  416.                     FROM P5P_ADHO_TBLS.USG6896_COLLECTIONS_LK A
  417. /*                    ,P7P_PROD_VIEW.VMTT_INCR_SEG VMTT*/
  418. /*                     where A.ACCT_NBR = VMTT.ACCT_NBR*/
  419. /*                      AND SCORE_MODEL_ID = 8029*/
  420. /*                      AND ACCT_SEG_ID BETWEEN 1 AND 90*/
  421.                     GROUP BY 1 ) ) BY teradata;    
  422.  
  423.  
  424.  
  425. * Override AAL Flag FOR this price plan family WITH LIMIT OF 5 LINES ;
  426.  
  427. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  428.  SET AAL_FLAG = 'N'
  429.  WHERE ACCT_NBR IN ( SELECT ACCT_NBR
  430.                       FROM P5P_MEDP_VIEW.VM33_CSLD_CPGN VM33,
  431.                            P7P_PROD_VIEW.VLE7_PRICE_PLAN VLE7
  432.                       WHERE VM33.PRICE_PLAN_CD = VLE7.PRICE_PLAN_CD
  433.                       AND VM33.CUST_SYS_CD = VLE7.CUST_SYS_CD
  434.                       AND VM33.ACT_CD = 'Y' AND VM33.LIAB_CD = 'IL'
  435.                       AND PRICE_PLAN_FMLY_CD = 5600
  436.                       GROUP BY 1
  437.                       HAVING COUNT(*) >= 5 ) ) BY teradata;
  438.  
  439. * Exclude IF ON an OPEN IB ;
  440.  
  441. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  442.  SET X_IB = 1
  443.  WHERE SBSCR_NBR IN ( SELECT SBSCR_NBR FROM p7p_prod_view.VSZ2_LOAN
  444.                     WHERE CNTRC_TYPE_CD = 'I'
  445.                     AND loan_stus_cd = 'O' GROUP BY 1)) BY teradata;
  446.  
  447.  
  448. /*
  449.   * Exclude if on an open lease ;
  450.  
  451.  execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  452.  set X_LEASE = 1
  453.  where SBSCR_NBR IN ( SELECT SBSCR_NBR FROM p7p_prod_view.VSZ2_LOAN
  454.                     WHERE CNTRC_TYPE_CD = 'L'
  455.                     and loan_stus_cd = 'O' GROUP BY 1)) by teradata;
  456.  
  457.    * include if on a paid off lease ;
  458.  
  459.  execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  460.  set X_LEASE = 0
  461.  where X_LEASE = 1
  462.   and SBSCR_NBR NOT IN ( SELECT SBSCR_NBR FROM p7p_prod_view.VSZ2_LOAN
  463.                          WHERE CNTRC_TYPE_CD = 'L'
  464.                          and loan_stus_cd = 'O'
  465.                          and NET_LOAN_BAL_DUE_AMT > 0 GROUP BY 1) ) by teradata;
  466.  
  467.    * include if paying for Annual upgrade ;
  468.  
  469.  execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  470.  set X_LEASE = 0
  471.  where X_LEASE = 1
  472.   and RLOF >= 12
  473.   and SBSCR_NBR IN ( SELECT SBSCR_NBR FROM P7P_PROD_VIEW.VMTJ_SBSCR_PLAN_TRAN
  474.                      WHERE SRVC_CD IN ('ANUPGRADE','ANUPGRD10')
  475.                      AND (SRVC_TRMTN_DT IS NULL OR SRVC_TRMTN_DT > DATE) GROUP BY 1) ) by teradata;
  476.  
  477.  
  478. */
  479.  
  480.        
  481.  
  482. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  483.     ( SELECT ACCT_NBR _ACCT_NBR
  484.           ,CUST_SYS_CD _CUST_SYS_CD
  485.           ,ADR_LINE_1_TXT
  486.           ,ADR_LINE_2_TXT
  487. /*        ,CUST_ADR_NME*/
  488.           ,CTY_NME
  489.           ,STATE_CD
  490.           ,BUS_NME
  491.           ,FRST_NME
  492.           ,LST_NME
  493.           ,ZIP_CD
  494.           ,ZIP_PLUS_4_CD
  495.           ,CASE WHEN BAD_ADR_CD = 'Y' OR RTRN_MAIL_INDCR_CD = 'Y'
  496.            OR cty_nme = '' OR cty_nme IS NULL
  497.            OR state_cd IS NULL OR state_cd = ''
  498.            OR zip_cd IS NULL OR zip_cd = '' THEN 1 ELSE 0 END X_BAD_ADR
  499.           ,CASE WHEN BAD_ADR_CD = 'Y' OR RTRN_MAIL_INDCR_CD = 'Y'
  500.            OR cty_nme = '' OR cty_nme IS NULL
  501.            OR state_cd IS NULL OR state_cd = ''
  502.            OR zip_cd IS NULL OR zip_cd = '' THEN 'N' ELSE 'Y' END DM_ELIGIBLE_FLAG
  503.    FROM P7P_CPGN_VIEW.VM03A_ACCT_PRIM_CNTCT )  t1
  504.     SET FRST_NME = T1.FRST_NME
  505.           ,LST_NME = T1.LST_NME
  506.           ,ADR_LINE_1_TXT = T1.ADR_LINE_1_TXT
  507.           ,ADR_LINE_2_TXT = T1.ADR_LINE_2_TXT
  508.           ,BUS_NME = T1.BUS_NME
  509. /*        ,CUST_ADR_NME = T1.CUST_ADR_NME*/
  510.           ,CTY_NME = T1.CTY_NME
  511.           ,DM_ELIGIBLE_FLAG = T1.DM_ELIGIBLE_FLAG
  512.           ,STATE_CD = T1.STATE_CD
  513.           ,ZIP_CD = T1.ZIP_CD
  514.           ,ZIP_PLUS_4_CD = T1.ZIP_PLUS_4_CD
  515.           ,X_BAD_ADR = T1.X_BAD_ADR
  516.          WHERE ACCT_NBR = T1._ACCT_NBR
  517.      AND CUST_SYS_CD = T1._CUST_SYS_CD ) BY teradata;
  518.  
  519. * Do NOT mail - this IS the logic the checker uses ;
  520.  
  521.  EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  522.     SET X_DNM = 1
  523.        ,DM_ELIGIBLE_FLAG = 'N'
  524.     WHERE (ACCT_NBR, CUST_SYS_CD) IN
  525.       ( SELECT ACCT_NBR, CUST_SYS_CD FROM P7P_PROD_VIEW.VM16A_ACCT_DNM_SPR
  526.         WHERE BRND_CD = 'Sprint' )  ) BY teradata;
  527.  
  528. * P360 Store ;
  529.  
  530.  
  531. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  532.    (  SELECT A.acct_nbr _acct_nbr, A.zip_cd AS _zip_cd, A.zip_plus_4_cd AS _zip_plus_4_cd            
  533.       ,C.store_nbr,b.DIST_QTY                                          
  534.       ,c.adr_line_1_txt AS adr_line_1_txt
  535.       ,c.Cty_nme AS cty_nme
  536.       ,c.state_cd AS state_cd
  537.       ,c.zip_cd AS Store_zip_cd,
  538.       MAIN_STORE_PHN_NBR
  539.       ,'C' AS STORE_TYPE
  540.       ,Rank () OVER (partition BY acct_nbr ORDER BY sbscr_nbr, rank_nbr, dist_qty,  dlr_scid_cd DESC) ranking
  541. FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER A                                                            
  542.    INNER JOIN P7P_PROD_VIEW.VLDT_SPRINT_STORE_ZIP_DIST B                
  543.       ON A.ZIP_CD = B.ZIP_CD AND A.ZIP_PLUS_4_CD = B.ZIP_PLUS_4_CD      
  544.    INNER JOIN P7P_PROD_VIEW.VLDUB_SPRINT_STORE_OPEN C                  
  545.       ON B.STORE_nbr = C.STORE_nbr  
  546.    INNER JOIN P5P_CPGN_TBLS.LMK_p360_lu D
  547.       ON C.STORE_NBR = D.STORE_NBR
  548.  WHERE  b.dist_qty le 10  
  549.  AND MAIN_STORE_PHN_NBR IS NOT NULL
  550.  AND MAIN_STORE_PHN_NBR <> ' '
  551.  AND MAIN_STORE_PHN_NBR <> '--'
  552.  AND C.STORE_NBR = D.STORE_NBR
  553.  AND RANK_NBR = 1
  554.  AND LENGTH(oreplace (MAIN_STORE_PHN_NBR,'-','') ) = 10
  555.  qualify ranking = 1 ) t1
  556.      SET STORE_ADR = T1.adr_line_1_txt
  557.         ,STORE_CITY = T1.Cty_nme
  558.         ,STORE_NBR = T1.STORE_NBR
  559.         ,STORE_STATE_ZIP = T1.state_cd||' '||T1._zip_cd
  560.         ,STORE_PHN_NBR = SUBSTR(T1.MAIN_STORE_PHN_NBR,1,3)||'-'||SUBSTR(T1.MAIN_STORE_PHN_NBR,4,3)||'-'||SUBSTR(T1.MAIN_STORE_PHN_NBR,7,4)
  561.         ,STORE_FLAG = 'Y'
  562.      WHERE acct_nbr  = T1._acct_nbr  ) BY teradata;
  563.  
  564. * P360 WUF;
  565.  
  566. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  567. SET STORE_WUF_FLAG = 'Y'
  568. WHERE STORE_FLAG = 'Y'
  569. AND STORE_NBR IS NOT NULL
  570. AND STORE_NBR IN ( SELECT STORE_NBR FROM P5P_CPGN_TBLS.LMK_p360_WUF GROUP BY 1) ) BY TERADATA;
  571.  
  572.  
  573.  
  574. * Closest store - Midwest Region ONLY ;
  575. /*
  576. execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER from
  577.    (  select VLDT.ZIP_CD _ZIP_CD, VLDT.ZIP_PLUS_4_CD _ZIP_PLUS_4_CD, ADR_TXT, CTY_ADR, STT_ADR, VN45.ZIP_CD as store_zip, vldub.MAIN_STORE_PHN_NBR
  578.       from P7P_PROD_VIEW.VN45_STORE_LOCN_HIERARCHY VN45,
  579.            P7P_PROD_VIEW.VLDT_SPRINT_STORE_ZIP_DIST VLDT,
  580.            P7P_PROD_VIEW.VLDUB_SPRINT_STORE_OPEN VLDUB
  581.       where VN45.STORE_NBR = VLDT.STORE_NBR
  582.       AND VN45.STORE_NBR = VLDUB.STORE_NBR
  583.       and store_type_cd = 'corporate'
  584.       and store_stus_cd = 'Open'
  585.       and rgn_nme = 'Midwest'
  586.       AND DIST_QTY <= 10
  587.       and DLR_SCID_CD = 'S'
  588.       AND RANK_NBR = 1 ) t1
  589.      SET STORE_ADR = T1.ADR_TXT
  590.         ,STORE_CITY = T1.CTY_ADR
  591.         ,STORE_STATE_ZIP = T1.STT_ADR||' '||T1.STORE_ZIP
  592.         ,STORE_PHN_NBR = SUBSTR(T1.MAIN_STORE_PHN_NBR,1,3)||'-'||SUBSTR(T1.MAIN_STORE_PHN_NBR,4,3)||'-'||SUBSTR(T1.MAIN_STORE_PHN_NBR,7,4)
  593.         ,MIDWEST_FLAG = 'Y'
  594.      WHERE ZIP_CD  = t1._ZIP_CD
  595.       AND  ZIP_PLUS_4_CD  = T1._ZIP_PLUS_4_CD
  596.       AND BILL_LANG_CD = 'ENG' ) by teradata;
  597.  
  598.  
  599. execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  600.  SET STORE_ADR = '2540 Broadway Bluffs Dr Ste'
  601.  WHERE STORE_ADR = '2540 Broadway Bluffs Dr Ste 105-107' ) by teradata;
  602. */
  603.  /*
  604.  select * from connection to teradata
  605.  ( select max(length(store_adr))
  606.          ,max(length(store_city))
  607.          ,max(length(STORE_STATE_ZIP))
  608.          ,max(length(STORE_PHN_NBR))
  609.    from P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER );
  610. */
  611. * RADIOSHACK Store ;
  612.  
  613. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER from */
  614. /*   ( select t1.ZIP_CD _ZIP_CD*/
  615. /*           ,t1.ZIP_PLUS_4_CD _ZIP_PLUS_4_CD*/
  616. /*         ,t1.STORE_NBR*/
  617. /*         ,Rank () over (partition by t1.ZIP_CD, t1.ZIP_PLUS_4_CD order by rank_nbr) ranking*/
  618. /*     from P7P_PROD_VIEW.VLDT_SPRINT_STORE_ZIP_DIST  t1,*/
  619. /*        P7P_PROD_VIEW.VLDUB_SPRINT_STORE_OPEN T2,*/
  620. /*        P7P_PROD_VIEW.VLDY_CHNL_HIER VLDY ,*/
  621. /*        P5P_CPGN_TBLS.LMK_&PTS_NBR._LU A*/
  622. /*   where T1.STORE_NBR = T2.STORE_NBR   */
  623. /*    AND T2.sals_chnl_nme = VLDY.chnl_lvl_0_des  */
  624. /*       AND T2.DLR_SCID_CD = 'S'          */
  625. /*       AND STORE_OWNER_CD IN ( 'RS-S','RS-D','RS-T')*/
  626. /*       AND T2.STORE_NBR = A.STORE_NBR*/
  627. /*       AND T2.PRE_MERGER_STORE_NBR = A.RS_STORE_NBR*/
  628. /*        and t1.DIST_QTY  < 6*/
  629. /*      qualify ranking = 1  ) t1*/
  630. /*     SET RS_FLAG = 'Y'*/
  631. /*        ,RS_STORE = t1.STORE_NBR*/
  632. /*   WHERE ZIP_CD  = t1._ZIP_CD */
  633. /*      AND  ZIP_PLUS_4_CD  = T1._ZIP_PLUS_4_CD   ) by teradata;*/
  634.  
  635. /**/
  636. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER from*/
  637. /*    ( SELECT TMX2_ACCT_NBR _ACCT_NBR*/
  638. /*            ,TMX2_CUST_SYS_CD _CUST_SYS_CD*/
  639. /*          ,SUBSTR(TMX2_RPT_VALU_CMNT_47_NME,1,4) AS RS_STORE*/
  640. /*          ,SUBSTR(TMX2_RPT_VALU_CMNT_47_NME,5,100) AS RS_ADDRESS*/
  641. /*    FROM P5P_STGE_TBLS.TMX2_SIEBEL_NRFC_ACCT A,*/
  642. /*         P5P_CPGN_TBLS.LMK_t313661_925_RS_LU  B*/
  643. /*    WHERE TMX2_RPT_VALU_CMNT_47_NME IS NOT NULL*/
  644. /*        AND SUBSTR(TMX2_RPT_VALU_CMNT_47_NME,1,4) = B.RS_STORE_NBR ) T1*/
  645. /*   SET RS_STORE = T1.RS_STORE*/
  646. /*      ,RS_ADDRESS = T1.RS_ADDRESS*/
  647. /*      ,RS_FLAG = 'Y'*/
  648. /*   WHERE ACCT_NBR = T1._ACCT_NBR*/
  649. /*      AND CUST_SYS_CD = T1._CUST_SYS_CD ) by teradata;*/
  650.  
  651.  
  652.  
  653. * Exclude IN Collections - PROACTIVE SCRUB;
  654.  
  655. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  656.  SET X_COLLECTIONS = 1
  657.  WHERE ACCT_NBR IN ( SELECT ACCT_NBR FROM p7p_prod_View.VM42_ACCT_CLCTN_DAILY
  658.                      WHERE IN_CLCTN_CD = 'Y'
  659.                      GROUP BY 1) ) BY teradata;
  660.  
  661. * Exclude IN Collections - Reactive SCRUB;
  662.  
  663. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  664.  SET X_COLLECTIONS30 = 1
  665.  WHERE ACCT_NBR IN ( SELECT ACCT_NBR FROM p7p_prod_View.VM42_ACCT_CLCTN_DAILY
  666.                      WHERE (past_due_31_60_amt + past_due_61_90_amt + past_due_91_plus_amt) > 0
  667.                      GROUP BY 1) ) BY teradata;
  668.  
  669. * Exclude Do NOT Sell - added 20170627 ;
  670.  
  671. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  672.  SET X_DO_NOT_SELL = 1
  673.    WHERE acct_nbr IN ( SELECT BAN FROM P7P_PROD_VIEW.VMYP_DONOTSELL_BAN_LIST GROUP BY 1)) BY teradata;
  674.  
  675. ******************************************************************
  676. * Contact Strategy
  677. ******************************************************************;
  678.  
  679.  EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  680.  SET X_CONTACT_STRATEGY_EM = 1
  681.  WHERE acct_nbr IN ( SELECT acct_nbr FROM P5P_MEDP_TBLS.CNTCT_STRTGY_XCLUD A
  682.                         INNER JOIN (
  683.                           SELECT DISTINCT LINE_NBR, CNTCT_MTHD_TYPE_CD
  684.                           FROM P7P_PROD_VIEW.VMXA_CPGN_CNTCT_STRTG VMXA
  685.                            INNER JOIN P7P_PROD_VIEW.VLDP_CPGN VLDP
  686.                             ON VMXA.CPGN_TRK_CD = VLDP.CPGN_TRK_CD
  687.                      WHERE VLDP.valu_pposn_TRK_CD = '141XTOVGAB'
  688.                         AND VLDP.TMNT_ID = '1-1YYE8JM' ) MY_CAMPAIGN
  689.                     ON a.CNTCT_MTHD_TYPE_cD = MY_CAMPAIGN.CNTCT_MTHD_TYPE_CD
  690.                      WHERE MY_CAMPAIGN.LINE_NBR >= A.LINE_NBR_START
  691.                     AND MY_CAMPAIGN.LINE_NBR <= A.LINE_NBR_END GROUP BY 1) ) BY teradata;
  692.                    
  693. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  694.  SET X_CONTACT_STRATEGY_SMS = 1
  695.  WHERE acct_nbr IN ( SELECT acct_nbr FROM P5P_MEDP_TBLS.CNTCT_STRTGY_XCLUD A
  696.                         INNER JOIN (
  697.                           SELECT DISTINCT LINE_NBR, CNTCT_MTHD_TYPE_CD
  698.                           FROM P7P_PROD_VIEW.VMXA_CPGN_CNTCT_STRTG VMXA
  699.                            INNER JOIN P7P_PROD_VIEW.VLDP_CPGN VLDP
  700.                             ON VMXA.CPGN_TRK_CD = VLDP.CPGN_TRK_CD
  701.                      WHERE VLDP.valu_pposn_TRK_CD = '141XTOZ1AB'
  702.                        AND VLDP.TMNT_ID = '1-1CAA4XE'  ) MY_CAMPAIGN
  703.                     ON a.CNTCT_MTHD_TYPE_cD = MY_CAMPAIGN.CNTCT_MTHD_TYPE_CD
  704.                      WHERE MY_CAMPAIGN.LINE_NBR >= A.LINE_NBR_START
  705.                     AND MY_CAMPAIGN.LINE_NBR <= A.LINE_NBR_END GROUP BY 1) ) BY teradata;  
  706.  
  707.  
  708.  
  709. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  710.  SET X_CONTACT_STRATEGY_EM = 0
  711.  WHERE TARGET_GRP IN ('106B')) BY teradata;
  712.  
  713.  EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  714.  SET X_CONTACT_STRATEGY_SMS = 0
  715.  WHERE TARGET_GRP IN ('106B')) BY teradata;
  716.  
  717.  * Samsung ;
  718.  
  719. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  720.  SET X_CONTACT_STRATEGY_EM = 1
  721.     ,X_CONTACT_STRATEGY_SMS = 1
  722.  WHERE ACCT_NBR IN ( SELECT ACCT_NBR FROM P5P_CPGN_TBLS.LMK_SAMSUNG_WRKE GROUP BY 1) ) BY teradata;
  723.  
  724. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  725. /* set X_CONTACT_STRATEGY = 1*/
  726. /* where (ACCT_NBR,CUST_SYS_CD) in ( select acct_nbr, CUST_SYS_CD */
  727. /*                         from P7P_CPGN_VIEW.VMTL_CRM_WRLS_CPGN_HIST VMTL */
  728. /*                         INNER JOIN P7P_PROD_VIEW.VMXA_CPGN_CNTCT_STRTG EXCLUDE */
  729. /*                         ON VMTL.CPGN_tRK_cD = EXCLUDE.CPGN_TRK_CD  */
  730. /*                          WHERE EXCLUDE.line_nbr <= 201 */
  731. /*                          AND  ( (exclude.CPGN_EXECN_MAX_DT IS NOT NULL */
  732. /*                          AND  date < EXCLUDE.CPGN_EXECN_MAX_DT) */
  733. /*                                  OR */
  734. /*                             ( EXCLUDE.CPGN_DAY_CNT IS NOT NULL */
  735. /*                               AND VMTL.CPGN_eXECN_DT >= (DATE - EXCLUDE.CPGN_DAY_CNT))) */
  736. /*                           GROUP BY 1,2 ) ) by teradata;*/
  737.  
  738. * Exclude Minors FROM proactive;
  739.  
  740. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  741. SET X_MINOR = 1
  742. WHERE SBSCR_NBR IN ( SELECT SBSCR_NBR FROM P7P_PROD_VIEW.VMHX_SBSCR_DMGPHC
  743.                      WHERE CHILD_SPRS_CD = 'Y' GROUP BY 1 ) ) BY teradata;
  744.  
  745. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  746. SET X_MINOR = 1
  747. WHERE SBSCR_NBR IN ( SELECT SBSCR_NBR FROM p5p_medp_Tbls.USG8314_SUBS_TO_STRIP GROUP BY 1 ) ) BY teradata;
  748.  
  749.  
  750. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  751. SET X_MINOR = 1
  752. WHERE MIN_NBR IN ( SELECT MIN_NBR FROM p7p_prd1_view.vm11_sbscr vm11
  753.                        INNER JOIN P7P_PROD_VIEW.VMHX_SBSCR_DMGPHC vmhx
  754.                        ON vm11.sbscr_nbr = vmhx.sbscr_nbr
  755.                        AND vm11.cust_sys_Cd = vmhx.cust_sys_Cd
  756.                        WHERE CHILD_SPRS_CD = 'Y' GROUP BY 1 ) ) BY teradata;
  757.  
  758. * USING Doreens TABLE now;
  759.                                        
  760. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  761.  SET X_IB_AT_CC = 0
  762.  WHERE (ACCT_CR_CLASS_CD, LIAB_CD) IN ( SELECT CR_CLASS_CD, LIAB_CD
  763.                                        FROM p5p_medp_tbls.ib_cred_class
  764.                                        WHERE IB_ELIG_IND = 'Y' GROUP BY 1,2) ) BY teradata;
  765.  
  766. * USING Doreens TABLE, was USING P5P_CPGN_TBLS.LMK_IB_ATST ;
  767.  
  768. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  769.  SET X_IB_AT_CC = 1
  770.  WHERE (ATST,LIAB_CD) NOT IN ( SELECT ACCT_TYPE_CD||ACCT_SUB_TYPE_CD AS ATST, LIAB_CD
  771.                      FROM p5p_medp_tbls.arm_atst_codes
  772.                      WHERE /*LIAB_CD = 'IL'
  773.                      AND */ LOAN_ELIG_CD_JRULES = 'Y'
  774.                      GROUP BY 1,2 ) ) BY teradata;
  775.                      
  776.  
  777. *************************************************************************
  778.   Models AND Segmentation
  779. *************************************************************************;
  780.  
  781. * Treatment Plan - USE TP 8116 FOR Q1 2018.  1-90 treated AND 91-100 control.;
  782.  
  783.  EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  784.     ( SELECT ACCT_NBR _ACCT_NBR, ACCT_SEG_ID, SCORE_MODEL_ID, SCORE_DT,
  785.              CASE WHEN ACCT_SEG_ID BETWEEN 1 AND 90 THEN 'N'
  786.                   WHEN ACCT_SEG_ID BETWEEN 91 AND 100 THEN 'Y' END CONTROL_GROUP_FLAG
  787.              ,CASE WHEN ACCT_SEG_ID IS NOT NULL THEN 0 ELSE 1 END X_TP_BLANK
  788.       FROM P7P_PROD_VIEW.VMTT_INCR_SEG
  789.       WHERE SCORE_MODEL_ID = 8116 ) T1
  790.      SET TREATMENT_PLAN = T1.ACCT_SEG_ID
  791.         ,TREATMENT_PLAN_DATE = T1.SCORE_DT
  792.         ,TREATMENT_PLAN_MODEL = T1.SCORE_MODEL_ID
  793.         ,CONTROL_GROUP_FLAG = T1.CONTROL_GROUP_FLAG
  794.         ,X_TP_BLANK = T1.X_TP_BLANK  
  795. WHERE ACCT_NBR = T1._ACCT_NBR  ) BY teradata;
  796.  
  797.  
  798. * Universal Control - Model ID 8100;
  799.  
  800.  
  801. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  802.  SET X_UNIVERSAL_CONTROL = 1
  803.  WHERE ACCT_NBR IN ( SELECT ACCT_NBR FROM P7P_PROD_VIEW.VMTT_INCR_SEG
  804.                      WHERE SCORE_MODEL_ID = 8100
  805.                      AND ACCT_SEG_ID IN (2,3)
  806.                      GROUP BY 1 ) ) BY teradata;
  807.  
  808. * VALUE Scores / MTV;
  809.  
  810.  EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  811.     ( SELECT ACCT_NBR _ACCT_NBR,
  812.              ACCT_SEG_ID
  813.       FROM P7P_PROD_VIEW.VMTX_SEG_HIST m
  814.             INNER JOIN
  815.           (SELECT h.SCORE_MODEL_ID,
  816.                   h.REFR_DT
  817.            FROM p7p_prod_view.vmu0_model_load_hist h
  818.                   INNER JOIN p7p_prod_view.VLEB_SCORE_MODEL b
  819.                 ON h.SCORE_MODEL_ID = b.SCORE_MODEL_ID
  820.                WHERE h.actn_cd = 'A'
  821.                AND b.score_model_stus_cd = 'A'
  822.                qualify (rank() OVER (partition BY h.SCORE_MODEL_ID ORDER BY h.ACTN_TMST DESC) = 1)  ) r
  823.            ON m.score_model_id = r.score_model_id
  824.            AND m.refr_dt = r.refr_dt
  825.            WHERE m.score_model_id = 12
  826.       GROUP BY 1,2) T1
  827.      SET MTV = T1.ACCT_SEG_ID
  828.      WHERE ACCT_NBR = T1._ACCT_NBR  ) BY teradata;
  829.  
  830.  
  831.      * Churn Deciles;
  832.  
  833.  
  834. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  835.     ( SELECT SBSCR_NBR _SBSCR_NBR
  836.             ,SBSCR_DECILE_SCORE_NBR
  837.             ,ACCT_DECILE_SCORE_NBR
  838.             ,m.REFR_DT
  839.             ,CASE WHEN SBSCR_DECILE_SCORE_NBR BETWEEN 1 AND 2 THEN 0 ELSE 1 END X_DM_CHURN_DECILE
  840.       FROM P7P_PROD_VIEW.VMTZ_PRNST_HIST m
  841.             INNER JOIN
  842.           (SELECT h.SCORE_MODEL_ID,
  843.                   h.REFR_DT
  844.            FROM p7p_prod_view.vmu0_model_load_hist h
  845.                   INNER JOIN p7p_prod_view.VLEB_SCORE_MODEL b
  846.                 ON h.SCORE_MODEL_ID = b.SCORE_MODEL_ID
  847.                WHERE h.actn_cd = 'A'
  848.                AND b.score_model_stus_cd = 'A'
  849.                qualify (rank() OVER (partition BY h.SCORE_MODEL_ID ORDER BY h.ACTN_TMST DESC) = 1)  ) r
  850.            ON m.score_model_id = r.score_model_id
  851.            AND m.refr_dt = r.refr_dt
  852.            WHERE m.score_model_id = 586
  853.       GROUP BY 1,2,3,4) T1
  854.      SET CHURN_DECILE_SUB = T1.SBSCR_DECILE_SCORE_NBR
  855.         ,CHURN_DECILE_BAN = T1.ACCT_DECILE_SCORE_NBR
  856.         ,CHURN_REF_DATE = T1.REFR_DT
  857. /*      ,X_DM_CHURN_DECILE =T1.X_DM_CHURN_DECILE*/
  858.      WHERE SBSCR_NBR = T1._SBSCR_NBR  ) BY teradata;
  859.  
  860.  
  861. ****************************************************************************
  862.  Email Eligibility
  863. ****************************************************************************;
  864.  
  865.   * Opt IN email addresses - Ban priority;
  866.  
  867. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  868.     ( SELECT _ACCT_NBR
  869.             ,_CUST_SYS_CD
  870.             ,ACCT_MARCOM_EMAIL_ADR
  871.             ,ACCT_EMAIL_ENG_LVL
  872.       FROM ( SELECT ACCT_NBR _ACCT_NBR
  873.             ,CUST_SYS_CD _CUST_SYS_CD
  874.             ,ACCT_MARCOM_EMAIL_ADR
  875.             ,ACCT_EMAIL_ENG_LVL
  876.             ,UPPER(SUBSTR(ACCT_MARCOM_EMAIL_ADR ,POSITION('@' IN ACCT_MARCOM_EMAIL_ADR )+1,CHARACTERS(ACCT_MARCOM_EMAIL_ADR )-1) )AS EM_DOMAIN
  877.             ,Rank () OVER (partition BY ACCT_NBR ORDER BY (CASE WHEN ACCT_MARCOM_EMAIL_SRC = 'MARCOM' THEN 1 ELSE 2 END) ASC, ACCT_MARCOM_EMAIL_SRC DESC) ranking
  878.       FROM P5P_CPGN_TBLS.VM26A_SBSCR_ACCT_EML_PP_PROMO
  879.       WHERE ACCT_MARCOM_EMAIL_ADR IS NOT NULL
  880.       AND ACCT_MARCOM_EMAIL_ADR IS NOT IN (SELECT EMAIL_ADR FROM P5P_CPGN_TBLS.LMK_BAD_EMAIL )
  881.        AND INDEX(ACCT_MARCOM_EMAIL_ADR,'@') > 0
  882.       AND ACCT_MARCOM_EMAIL_ADR ne ' '  
  883.       AND INDEX(ACCT_MARCOM_EMAIL_ADR,'.@') = 0
  884.       AND INDEX(ACCT_MARCOM_EMAIL_ADR,'@.') = 0
  885.       AND INDEX(ACCT_MARCOM_EMAIL_ADR,'--') = 0
  886.       AND substr(ACCT_MARCOM_EMAIL_ADR,1,1) ne '0'
  887.       AND substr(ACCT_MARCOM_EMAIL_ADR,1,1) ne '@'
  888.       AND EM_DOMAIN NOT IN ( SELECT EMAIL_DOMAIN FROM P5P_CPGN_TBLS.LMK_LU_FCC_DOMAIN_EXCLUDE)
  889.       QUALIFY RANKING = 1 ) A GROUP BY 1,2,3,4 ) t1
  890.      SET EMAIL_ADR = T1.ACCT_MARCOM_EMAIL_ADR
  891.         ,EM_ELIGIBLE_FLAG = 'Y'
  892.         ,EMAIL_ENG_LVL = T1.ACCT_EMAIL_ENG_LVL
  893.         ,EMAIL_TYPE = 'B'
  894.      WHERE ACCT_NBR = T1._ACCT_NBR
  895.      AND CUST_SYS_CD = T1._CUST_SYS_CD ) BY teradata;
  896.  
  897.      * Pick up a sub level email address IF there isnt a BAN email address;
  898.  
  899. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  900.     ( SELECT SBSCR_NBR _SBSCR_NBR
  901.             ,CUST_SYS_CD _CUST_SYS_CD
  902.             ,SBSCR_MARCOM_EMAIL_ADR
  903.             ,SBSCR_EMAIL_ENG_LVL
  904.             ,UPPER(SUBSTR(SBSCR_MARCOM_EMAIL_ADR ,POSITION('@' IN SBSCR_MARCOM_EMAIL_ADR )+1,CHARACTERS(SBSCR_MARCOM_EMAIL_ADR )-1) )AS EM_DOMAIN
  905.       FROM P5P_CPGN_TBLS.VM26A_SBSCR_ACCT_EML_PP_PROMO
  906.       WHERE SBSCR_MARCOM_EMAIL_ADR IS NOT NULL
  907.       AND SBSCR_MARCOM_EMAIL_ADR IS NOT IN (SELECT EMAIL_ADR FROM P5P_CPGN_TBLS.LMK_BAD_EMAIL)
  908.       AND INDEX(SBSCR_MARCOM_EMAIL_ADR,'@') > 0
  909.       AND SBSCR_MARCOM_EMAIL_ADR ne ' '  
  910.       AND INDEX(SBSCR_MARCOM_EMAIL_ADR,'.@') = 0
  911.       AND INDEX(SBSCR_MARCOM_EMAIL_ADR,'@.') = 0
  912.       AND INDEX(SBSCR_MARCOM_EMAIL_ADR,'--') = 0
  913.       AND substr(SBSCR_MARCOM_EMAIL_ADR,1,1) ne '0'
  914.       AND substr(SBSCR_MARCOM_EMAIL_ADR,1,1) ne '@'
  915.       AND EM_DOMAIN NOT IN ( SELECT EMAIL_DOMAIN FROM P5P_CPGN_TBLS.LMK_LU_FCC_DOMAIN_EXCLUDE)
  916.       ) t1
  917.      SET EMAIL_ADR = T1.SBSCR_MARCOM_EMAIL_ADR
  918.         ,EM_ELIGIBLE_FLAG = 'Y'
  919.         ,EMAIL_ENG_LVL = T1.SBSCR_EMAIL_ENG_LVL
  920.         ,EMAIL_TYPE = 'S'
  921.      WHERE SBSCR_NBR = T1._SBSCR_NBR
  922. /*   and DCSN_MAKER_SBSCR_NBR = T1._SBSCR_NBR*/
  923.      AND CUST_SYS_CD = T1._CUST_SYS_CD  
  924.      AND (EMAIL_ADR IS NULL OR EMAIL_ENG_LVL = 'N')
  925.      AND X_MINOR = 0 ) BY teradata;
  926.  
  927.  
  928. * Exclude un-engaged email ;
  929.  
  930. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  931.      SET X_EMAIL_UNENGAGE = 1
  932.         ,EM_ELIGIBLE_FLAG = 'N'
  933.      WHERE EMAIL_ENG_LVL = 'N' ) BY teradata;
  934.  
  935. * Exclude UNTIL FURTHER NOTICE ;
  936.  
  937. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  938.      SET X_EMAIL = 1
  939.         ,EM_ELIGIBLE_FLAG = 'N'
  940.      WHERE EMAIL_ADR IN ( SELECT EMAIL_ADR FROM P5P_MEDP_TBLS.USG2769_SUPPRESSION_EMAILS GROUP BY 1 ) ) BY teradata;
  941.  
  942. * Exclude Canada - CASL ;
  943.  
  944. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  945. SET X_CANADA = 1
  946.    ,EM_ELIGIBLE_FLAG = 'N'
  947. WHERE ACCT_NBR IN ( SELECT ACCT_NBR FROM P5P_CPGN_TBLS.CASL_SCRUB_ACCTS GROUP BY 1 ) ) BY teradata;
  948.  
  949.  
  950.  
  951. * fLAG THE ban  ;
  952.  
  953. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  954.      SET EM_ELIGIBLE_BAN_FLAG = 'Y'
  955.      WHERE ACCT_NBR IN ( SELECT ACCT_NBR FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  956.                           WHERE EM_ELIGIBLE_FLAG = 'Y' AND X_CONTACT_STRATEGY_EM = 0 GROUP BY 1 ) ) BY teradata;
  957.  
  958.  
  959. * Responder ;
  960.  
  961. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER */
  962. /*   SET RESPONDER_FLAG = 'Y'*/
  963. /*   WHERE EMAIL_ADR in ( SELECT EMAIL_ADR FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._CTL*/
  964. /*                        WHERE SPAM_QTY = 0 AND DELIVER_QTY > 0*/
  965. /*                           and  ( OPEN_QTY > 0 OR CLICK_QTY > 0 )*/
  966. /*                       GROUP BY 1 ) ) by teradata;*/
  967.  
  968.  
  969. * Spam ;
  970.  
  971. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER */
  972. /*   SET X_CTL_SPAM = 1*/
  973. /*   WHERE EMAIL_ADR in ( SELECT EMAIL_ADR FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._CTL*/
  974. /*                        WHERE SPAM_QTY > 0 GROUP BY 1 ) ) by teradata;*/
  975.  
  976.  
  977. ***********************************************************
  978.   SMS Logic
  979. ***********************************************************;
  980.  
  981. *****************************************************************************************************
  982.   SMS Scrubs
  983.     Exclude SMS opt outs
  984.     Exclude Sprint DNC
  985.       Exclude IF NPA OF MIN IN AZ, LA, TX
  986.     - added back IN ON 10/10, Zeta IS managing this -- Exclude HI due to calling hour restrictions
  987.     - added back IN/removed exclusion: ID, WI, Exclude IN State DNC
  988.     - added exclusion ID, WI
  989.  
  990. ****************************************************************************************************;
  991.  
  992. * Exclude AZ NPA ;
  993.  
  994. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  995.          SET X_SMS_STATES  = 1
  996.          WHERE substr(MIN_NBR,1,3) IN
  997.          (SELECT NPA_NBR
  998.           FROM P7P_PROD_VIEW.VLF8_NPA_ZIP_MAPNG
  999.           WHERE STATE_CD = 'AZ') ) BY teradata;
  1000.  
  1001. * Exclude ID NPA ;
  1002.  
  1003. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1004.          SET X_SMS_STATES  = 1
  1005.          WHERE substr(MIN_NBR,1,3) IN
  1006.          (SELECT NPA_NBR
  1007.           FROM P7P_PROD_VIEW.VLF8_NPA_ZIP_MAPNG
  1008.           WHERE STATE_CD = 'ID') ) BY teradata;
  1009.  
  1010.  
  1011. * Exclude LA NPA;
  1012.  
  1013. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1014.          SET X_SMS_STATES  = 1
  1015.          WHERE substr(MIN_NBR,1,3) IN
  1016.          (SELECT NPA_NBR
  1017.           FROM P7P_PROD_VIEW.VLF8_NPA_ZIP_MAPNG
  1018.           WHERE STATE_CD = 'LA') ) BY teradata;
  1019.  
  1020. * Exclude TX NPA ;
  1021.  
  1022. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1023.          SET X_SMS_STATES  = 1
  1024.          WHERE substr(MIN_NBR,1,3) IN
  1025.          (SELECT NPA_NBR
  1026.           FROM P7P_PROD_VIEW.VLF8_NPA_ZIP_MAPNG
  1027.           WHERE STATE_CD = 'TX') ) BY teradata;
  1028.  
  1029. * Exclude WI NPA ;
  1030.  
  1031. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1032.          SET X_SMS_STATES  = 1
  1033.          WHERE substr(MIN_NBR,1,3) IN
  1034.          (SELECT NPA_NBR
  1035.           FROM P7P_PROD_VIEW.VLF8_NPA_ZIP_MAPNG
  1036.           WHERE STATE_CD = 'WI') ) BY teradata;
  1037.  
  1038. * Exclude HI NPA AND STATE - STATE IS DONE EARLIER ;
  1039.  
  1040. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  1041. /*       set X_SMS_STATES  = 1*/
  1042. /*       where substr(MIN_NBR,1,3) in*/
  1043. /*         (select NPA_NBR*/
  1044. /*          from P7P_PROD_VIEW.VLF8_NPA_ZIP_MAPNG*/
  1045. /*          where STATE_CD = 'HI') ) by teradata;*/
  1046.          
  1047.  
  1048.  * Sprint DNC AND Indiana State DNC;
  1049.  
  1050.  EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1051.   SET X_SMS_SPRINT_DNC = 1
  1052.   WHERE MIN_NBR IN
  1053.  (  SELECT PHN_NBR
  1054.     FROM P7P_PROD_VIEW.VLFM_PHN_DO_NOT_CALL_OPT
  1055.     WHERE SPRINT_DO_NOT_CALL_CD >= 1
  1056.      /*OR STATE_IN_DO_NOT_CALL_CD >= 1*/ GROUP BY 1  )  ) BY teradata;
  1057.  
  1058.  
  1059. * SMS Opt Outs ;
  1060.  
  1061. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1062.  SET X_SMS_OPT_OUT = 1
  1063.  WHERE MIN_NBR IN (SELECT a.phn_nbr
  1064.                    FROM P7P_PROD_VIEW.VMHR_DO_NOT_SMS_OPT a,
  1065.                    P7P_PROD_VIEW.VLMP_SMS_GRNLTY b
  1066.                    WHERE a.GRNLTY_CD = b.GRNLTY_CD
  1067.                    AND  b.BRND_CD = 'SPRINT'
  1068.                    AND  b.PGM_DES = 'GENERAL PROMOTION'
  1069.                    AND SMS_OPT_CD = 'O' GROUP BY 1 ) ) BY teradata;
  1070.  
  1071.  
  1072. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1073.  SET X_SMS_BLACKLIST = 1
  1074.  WHERE MIN_NBR IN ( SELECT MIN_NBR FROM P5P_CPGN_TBLS.SMS_BLACKLIST GROUP BY 1) )BY teradata;
  1075.  
  1076.  
  1077.  
  1078. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1079.  SET SMS_ELIGIBLE_FLAG = 'N'
  1080.  WHERE X_SMS_CAPABLE = 1
  1081.   OR X_SMS_BLACKLIST = 1
  1082.   OR X_SMS_OPT_OUT = 1
  1083.   OR X_SMS_SPRINT_DNC = 1
  1084.   OR X_SMS_STATES  = 1
  1085.   OR X_CANADA = 1
  1086.   OR X_MINOR = 1
  1087.   OR X_CONTACT_STRATEGY_SMS = 1  ) BY teradata;
  1088.  
  1089.  
  1090.   * SMS TEST ;
  1091.  
  1092. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1093.  SET SMS_ELIGIBLE_FLAG = 'Y'
  1094.  WHERE BILL_LANG_CD = 'ENG'
  1095.    AND ACCT_SIZE_QTY > 1
  1096.    AND treatment_plan BETWEEN 46 AND 90
  1097.    AND DMAKER_MIN_NBR IS NOT NULL
  1098.    AND TARGET_GRP IN ('100','101')
  1099.    AND X_CANADA = 0
  1100.    AND X_MINOR = 0
  1101.    AND X_CONTACT_STRATEGY_SMS = 0) BY teradata;
  1102.  
  1103. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1104.  SET SMS_ELIGIBLE_FLAG = 'N'
  1105.  WHERE BILL_LANG_CD = 'ENG'
  1106.    AND ACCT_SIZE_QTY > 1
  1107.    AND treatment_plan BETWEEN 46 AND 90
  1108.    AND TARGET_GRP IN ('100','101')
  1109.    AND (DMAKER_MIN_NBR IS NULL
  1110.          OR X_CANADA = 1)  ) BY teradata;
  1111.  
  1112.  
  1113. /* execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  1114. /* set X_SMS_SPANISH = 1*/
  1115. /*    ,SMS_ELIGIBLE_FLAG = 'N'*/
  1116. /* WHERE BILL_LANG_CD = 'SPA' AND SMS_ELIGIBLE_FLAG = 'Y' )by teradata;*/
  1117.  
  1118.  
  1119.  * Exclude PR / VI zips ;
  1120. /**/
  1121. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  1122. /* set X_HARVEY_ZIPS = 1*/
  1123. /* WHERE state_cd in ('PR','VI') ) by teradata;*/
  1124.  
  1125. * Exclude WI FROM Subsidy offer ;
  1126.  
  1127. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  1128. /* set X_WI_SUBSIDY = 1*/
  1129. /* WHERE TARGET_GRP = '32'*/
  1130. /* and (state_cd = ('WI')*/
  1131. /* OR */
  1132. /* substr(MIN_NBR,1,3) in*/
  1133. /*         (select NPA_NBR*/
  1134. /*          from P7P_PROD_VIEW.VLF8_NPA_ZIP_MAPNG*/
  1135. /*          where STATE_CD = 'WI') ) ) by teradata;*/
  1136.  
  1137.  
  1138. * VERSIONS WE ARE NOT SENDING THIS WEEK;
  1139.  
  1140. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1141. SET X_VERSION_EXCLUDE_EM = 1
  1142. WHERE TARGET_GRP IN ('102C','103C')  ) BY teradata;
  1143.  
  1144.  
  1145.   * SMS VERSIONS NOT SENDING THIS WEEK ;
  1146.  
  1147. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1148. SET X_VERSION_EXCLUDE_SMS = 1
  1149. WHERE TARGET_GRP IN ('102C','103C')  ) BY teradata;
  1150.  
  1151.  
  1152. ** ELIGIBILITY GROUPS **;
  1153.  
  1154.  
  1155.  EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1156.   SET ELIGIBLE_FLAG = 'Y'
  1157.   WHERE X_UPGRADE = 0
  1158.   AND X_CANADA = 0
  1159.   AND X_IB_AT_CC = 0
  1160.    AND X_SEASONAL_SUSPEND = 0
  1161.   AND X_MILITARY_SUSPEND = 0
  1162. /*  and X_MINOR = 0 */  /* Will do this at the tactic level */
  1163.   AND X_COLLECTIONS = 0
  1164.   AND X_CORP = 0
  1165.   AND X_TRADE = 0
  1166.   AND X_IB = 0
  1167.   AND X_UNIVERSAL_CONTROL = 0
  1168.   /*and X_CONTACT_STRATEGY = 0 -- since it's at the tactic level */
  1169.   AND x_tp_blank = 0
  1170.   AND X_COLLECTIONS2 = 0
  1171.   AND X_SPANISH = 0
  1172.   AND LIAB_CD = 'IL'
  1173.   AND target_grp IS NOT NULL
  1174. /*  and target_grp not in ('5 LEGACY','6')*/
  1175.   AND x_campaign = 0
  1176.   AND x_do_not_sell = 0
  1177.   AND X_FREE_PLAN = 0
  1178. /*  and x_preorder = 0*/
  1179. /*  AND X_HARVEY_ZIPS = 0*/
  1180. /*  and x_wi_subsidy = 0*/
  1181.   AND X_IPFG8 = 0 /* not sending a proactive msg */
  1182. /*  AND RACK_RATE_FLAG = 'N'*/
  1183. /*  and STORE_FLAG = 'Y'*/
  1184. ) BY teradata;
  1185.  
  1186.  
  1187. * HAVE TO BE LOADED IN NBA;
  1188. /**/
  1189. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER */
  1190. /*set ELIGIBLE_FLAG = 'N' */
  1191. /*WHERE SBSCR_NBR NOT IN ( SELECT SBSCR_NBR FROM P5P_CPGN_TBLS.LMK_t327419_IB GROUP BY 1) )by teradata;*/
  1192.  
  1193. * Exclude P360 FROM Hulu versions ;
  1194.  
  1195. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER */
  1196. /*set HULU_FLAG = 'N'*/
  1197. /*where STORE_FLAG = 'Y'  ) by teradata;*/
  1198. /**/
  1199. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER */
  1200. /*set HULU_FLAG = 'N'*/
  1201. /*where acct_nbr in (select acct_nbr from P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER where store_flag = 'Y')  ) by teradata;*/
  1202.  
  1203.  
  1204. ****************************************************************
  1205.  Email Targeting
  1206. ****************************************************************;
  1207.  
  1208.  
  1209.  
  1210. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  1211.     ( SELECT *
  1212.       FROM P5P_CPGN_TBLS.LMK_UPG_LU
  1213.       WHERE TACTIC_CD = 'EM'
  1214.       AND _LGCY_LEASE_PRICE_CD = 'X'
  1215.       AND DEVICE_GRP = 'IPHONE' ) t1
  1216.      SET CAMPAIGN_CD = T1.CAMPAIGN_CD
  1217.         ,CAMPAIGN_DESC = T1.CAMPAIGN_DESC
  1218.         ,_SRT = T1._SRT
  1219.         ,PROGRAM_CD = T1.PROGRAM_CD
  1220.      WHERE TARGET_GRP = T1.GRP
  1221.      AND BILL_LANG_CD = T1._BILL_LANG_CD
  1222.      AND ELIGIBLE_FLAG = 'Y'
  1223.      AND EM_ELIGIBLE_BAN_FLAG = 'Y'
  1224.      ) BY teradata;
  1225.  
  1226. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  1227.     ( SELECT *
  1228.       FROM P5P_CPGN_TBLS.LMK_UPG_LU
  1229.       WHERE TACTIC_CD = 'EM'
  1230.       AND _LGCY_LEASE_PRICE_CD <> 'X'
  1231.       AND DEVICE_GRP = 'IPHONE'  ) t1
  1232.      SET CAMPAIGN_CD = T1.CAMPAIGN_CD
  1233.         ,CAMPAIGN_DESC = T1.CAMPAIGN_DESC
  1234.         ,_SRT = T1._SRT
  1235.         ,PROGRAM_CD = T1.PROGRAM_CD
  1236.      WHERE TARGET_GRP = T1.GRP
  1237.      AND BILL_LANG_CD = T1._BILL_LANG_CD
  1238.      AND LGCY_LEASE_PRICE_CD = t1._LGCY_LEASE_PRICE_CD
  1239.      AND ELIGIBLE_FLAG = 'Y'
  1240.      AND EM_ELIGIBLE_BAN_FLAG = 'Y' ) BY teradata;
  1241.  
  1242.  
  1243. ****************************************************************
  1244.  SMS Targeting
  1245. ****************************************************************;
  1246.    
  1247. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  1248.     ( SELECT *
  1249.       FROM P5P_CPGN_TBLS.LMK_UPG_LU
  1250.       WHERE TACTIC_CD = 'TM'
  1251.       AND _LGCY_LEASE_PRICE_CD = 'X'
  1252.       AND DEVICE_GRP = 'IPHONE'
  1253.       AND WUF_FLAG = 'N' ) t1
  1254.      SET CAMPAIGN_CD_SMS = T1.CAMPAIGN_CD
  1255.         ,CAMPAIGN_DESC_SMS = T1.CAMPAIGN_DESC
  1256.         ,_SRT_SMS = T1._SRT
  1257.         ,PROGRAM_CD = T1.PROGRAM_CD
  1258.      WHERE TARGET_GRP = T1.GRP
  1259.      AND BILL_LANG_CD = T1._BILL_LANG_CD
  1260. /*   AND STORE_WUF_FLAG = T1.WUF_FLAG*/
  1261.      AND ELIGIBLE_FLAG = 'Y'
  1262.      AND SMS_ELIGIBLE_FLAG = 'Y' ) BY teradata;
  1263.  
  1264. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  1265.     ( SELECT *
  1266.       FROM P5P_CPGN_TBLS.LMK_UPG_LU
  1267.       WHERE TACTIC_CD = 'TM'
  1268.       AND _LGCY_LEASE_PRICE_CD IN ('Y','N')
  1269.       AND DEVICE_GRP = 'IPHONE'
  1270.       AND WUF_FLAG = 'N') t1
  1271.      SET CAMPAIGN_CD_SMS = T1.CAMPAIGN_CD
  1272.         ,CAMPAIGN_DESC_SMS = T1.CAMPAIGN_DESC
  1273.         ,_SRT_SMS = T1._SRT
  1274.         ,PROGRAM_CD = T1.PROGRAM_CD
  1275.      WHERE TARGET_GRP = T1.GRP
  1276.      AND BILL_LANG_CD = T1._BILL_LANG_CD
  1277.      AND LGCY_LEASE_PRICE_CD = t1._LGCY_LEASE_PRICE_CD
  1278. /*   AND STORE_WUF_FLAG = T1.WUF_FLAG*/
  1279.      AND ELIGIBLE_FLAG = 'Y'
  1280.      AND SMS_ELIGIBLE_FLAG = 'Y' ) BY teradata;
  1281.  
  1282.  
  1283.    * SMS test TO decision maker ON multi line accounts ONLY ;
  1284.  
  1285. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1286.   SET CAMPAIGN_CD_SMS = '142UNU46AB'
  1287.      ,CAMPAIGN_DESC_SMS = '2018 iPhone Rack Rate Regular DM TEST 100 SMS ENG'
  1288.      ,SMS_TEST_FLAG = 'Y'
  1289.  WHERE TARGET_GRP IN ('100')
  1290.      AND BILL_LANG_CD = 'ENG'
  1291.      AND ELIGIBLE_FLAG = 'Y'
  1292.      AND LGCY_LEASE_PRICE_CD = 'N'
  1293.      AND SMS_ELIGIBLE_FLAG = 'Y'
  1294.      AND ACCT_SIZE_QTY > 1
  1295.      AND treatment_plan BETWEEN 46 AND 90) BY teradata;
  1296.  
  1297.      * WUF TEST;
  1298. /**/
  1299. /*execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  1300. /*  set CAMPAIGN_CD_SMS = '142DSE23AB'*/
  1301. /*   ,CAMPAIGN_DESC_SMS = '2018 iPhone Rack Rate Regular DM TEST 100 SMS ENG'*/
  1302. /*   ,SMS_TEST_FLAG = 'Y'*/
  1303. /* where TARGET_GRP in ('100')*/
  1304. /*   AND BILL_LANG_CD = 'ENG'*/
  1305. /*   AND ELIGIBLE_FLAG = 'Y'*/
  1306. /*   and LGCY_LEASE_PRICE_CD = 'N'*/
  1307. /*   AND SMS_ELIGIBLE_FLAG = 'Y'*/
  1308. /*   AND ACCT_SIZE_QTY > 1*/
  1309. /*   AND STORE_WUF_FLAG = 'Y'*/
  1310. /*   and treatment_plan between 46 and 90) by teradata;*/
  1311.  
  1312.   * SMS test TO decision maker ON multi line accounts ONLY ;
  1313.  
  1314. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1315.   SET CAMPAIGN_CD_SMS = '142UNU4MAB'
  1316.      ,CAMPAIGN_DESC_SMS = '2018 iPhone Rack Rate Loyalty DM TEST 101 SMS ENG'
  1317.      ,SMS_TEST_FLAG = 'Y'
  1318.  WHERE TARGET_GRP IN ('101')
  1319.      AND BILL_LANG_CD = 'ENG'
  1320.      AND ELIGIBLE_FLAG = 'Y'
  1321.      AND LGCY_LEASE_PRICE_CD = 'Y'
  1322.      AND SMS_ELIGIBLE_FLAG = 'Y'
  1323.      AND ACCT_SIZE_QTY > 1
  1324.      AND treatment_plan BETWEEN 46 AND 90) BY teradata;
  1325.  
  1326.      * WUF TEST ;
  1327.  
  1328. /*  execute(update P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  1329. /*  set CAMPAIGN_CD_SMS = '141XTP1FAB'*/
  1330. /*   ,CAMPAIGN_DESC_SMS = '2018 iPhone Rack Rate Loyalty DM TEST 101 WUF SMS ENG'*/
  1331. /*   ,SMS_TEST_FLAG = 'Y'*/
  1332. /* where TARGET_GRP in ('101')*/
  1333. /*   AND BILL_LANG_CD = 'ENG'*/
  1334. /*   AND ELIGIBLE_FLAG = 'Y'*/
  1335. /*   and LGCY_LEASE_PRICE_CD = 'Y'*/
  1336. /*   AND SMS_ELIGIBLE_FLAG = 'Y'*/
  1337. /*   AND ACCT_SIZE_QTY > 1*/
  1338. /*   AND STORE_WUF_FLAG = 'Y'*/
  1339. /*   and treatment_plan between 46 and 90) by teradata;*/
  1340.  
  1341. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  1342.     ( SELECT SBSCR_NBR _SBSCR_NBR, ACCT_NBR, CAMPAIGN_CD_SMS,
  1343.       RANK() OVER(PARTITION BY ACCT_NBR ORDER BY TARGET_PRIORITY, DCSN_MAKER_FLAG, SBSCR_STRT_DT, SBSCR_NBR) ranking
  1344.       FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1345.       WHERE ELIGIBLE_FLAG = 'Y'
  1346.        AND SMS_ELIGIBLE_FLAG = 'Y'
  1347.        AND CAMPAIGN_CD_SMS IS NOT NULL
  1348.        AND x_version_exclude_SMS = 0
  1349. /*       and (SMS_TEST_FLAG = 'Y' OR DMAKER_SBSCR_NBR = SBSCR_NBR)*/
  1350.       QUALIFY RANKING = 1 ) t1
  1351.      SET BAN_RANKs = RANKING
  1352.      WHERE SBSCR_NBR = T1._SBSCR_NBR ) BY teradata;
  1353.  
  1354.  
  1355. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1356.   SET CAMPAIGN_CD_SMS = NULL
  1357.      ,CAMPAIGN_DESC_SMS = NULL
  1358.      , BAN_MIXED_NEs_FLAG = 'X'  /* JUST SO I CAN SEE THESE FOR QC */
  1359.   WHERE SMS_TEST_FLAG = 'Y'
  1360.    AND (ACCT_NBR,TARGET_GRP,CAMPAIGN_CD_SMS) NOT IN ( SELECT ACCT_NBR, TARGET_GRP,CAMPAIGN_CD_SMS
  1361.                                         FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1362.                                         WHERE BAN_RANKs = 1 )  ) BY teradata;
  1363.  
  1364. /*
  1365.                                         select ACCT_NBR, count(distinct campaign_cd_sms)
  1366.   from P5P_CPGN_TBLS.LMK_t326739_DRIVER
  1367.  where campaign_cd_sms is not null
  1368.  and acct_nbr in ( select acct_nbr from  P5P_CPGN_TBLS.LMK_t326739_DRIVER
  1369.                                      where sms_test_flag = 'Y' and sms_eligible_flag = 'Y' and eligible_flag = 'Y'
  1370.                                       and campaign_cd_sms is not null group by 1)
  1371. group by 1
  1372. having count(distinct campaign_cd_sms) > 1
  1373.                                         */
  1374.  
  1375. ****************************************************************
  1376.  NBA
  1377. ****************************************************************;
  1378.  
  1379.  EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1380.  SET CAMPAIGN_CD_NBA = '13YG7QD7AB'
  1381.     ,CAMPAIGN_DESC_NBA = 'NBA - iPhone Rack Rate - Offer 19975 100/102/102C'
  1382.     ,NBA_OFFER_ID = '19975'
  1383.  WHERE TARGET_GRP IN ('100','102','102C','101','103','103C')
  1384.   AND LGCY_LEASE_PRICE_CD = 'N') BY teradata;
  1385.  
  1386.   EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1387.  SET CAMPAIGN_CD_NBA = '13YG7QDUAB'
  1388.     ,CAMPAIGN_DESC_NBA = 'NBA - iPhone Rack Rate Loyalty - Offer 19976 101/103/103C'
  1389.     ,NBA_OFFER_ID = '19976'
  1390.  WHERE TARGET_GRP IN ('100','102','102C','101','103','103C')
  1391.   AND LGCY_LEASE_PRICE_CD = 'Y') BY teradata;
  1392.  
  1393.  
  1394. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1395.  SET CAMPAIGN_CD_NBA = '13YG7QFMAB'
  1396.     ,CAMPAIGN_DESC_NBA = 'NBA - iPhone - $200 Offer - Red Zone- Group 106B'
  1397.     ,NBA_OFFER_ID = '61019'
  1398.  WHERE TARGET_GRP = '106B') BY teradata;
  1399.  
  1400. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1401.  SET CAMPAIGN_CD_NBA = '13YG7QEBAB'
  1402.     ,CAMPAIGN_DESC_NBA = 'NBA - iPhone - $200 OOL - Group 109C'
  1403.     ,NBA_OFFER_ID = '61106'
  1404.  WHERE TARGET_GRP = '109C') BY teradata;
  1405.  
  1406. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1407.  SET CAMPAIGN_CD_NBA = '13YG7QIDAB'
  1408.     ,CAMPAIGN_DESC_NBA = 'NBA - iPhone - $100 Visa 110A-D'
  1409.     ,NBA_OFFER_ID = '61193'
  1410.  WHERE TARGET_GRP LIKE '110%') BY teradata;
  1411.  
  1412.  
  1413. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER FROM
  1414.     ( SELECT SBSCR_NBR _SBSCR_NBR, ACCT_NBR, CAMPAIGN_CD, CAMPAIGN_DESC,
  1415.       RANK() OVER(PARTITION BY ACCT_NBR ORDER BY TARGET_PRIORITY, DCSN_MAKER_FLAG, SBSCR_STRT_DT, SBSCR_NBR) ranking
  1416.       FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1417.       WHERE ELIGIBLE_FLAG = 'Y'
  1418.        AND EM_ELIGIBLE_BAN_FLAG = 'Y'
  1419.        AND CAMPAIGN_CD IS NOT NULL
  1420.        AND x_version_exclude_em = 0
  1421. /*     and EMAIL_ADR IS NOT NULL*/
  1422. /*     and EMAIL_ADR <> ''*/
  1423.       QUALIFY RANKING = 1 ) t1
  1424.      SET BAN_RANK = RANKING
  1425.      WHERE SBSCR_NBR = T1._SBSCR_NBR ) BY teradata;
  1426.  
  1427.  
  1428. EXECUTE(UPDATE P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1429.   SET CAMPAIGN_CD = NULL
  1430.      ,CAMPAIGN_DESC = NULL
  1431.      ,BAN_MIXED_NE_FLAG = 'X'  /* JUST SO I CAN SEE THESE FOR QC */
  1432.   WHERE (ACCT_NBR,TARGET_GRP,CAMPAIGN_DESC) NOT IN ( SELECT ACCT_NBR, TARGET_GRP,CAMPAIGN_DESC
  1433.                                         FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1434.                                         WHERE BAN_RANK = 1 )  ) BY teradata;
  1435.  
  1436.  
  1437. * Now ALL campaign descriptions ON the ban should be the same OR NULL ;
  1438.  
  1439. SELECT * FROM connection TO teradata
  1440. (SELECT acct_nbr, COUNT(target_grp||campaign_desc)
  1441. FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1442. WHERE campaign_desc IS NOT NULL
  1443. GROUP BY 1
  1444. HAVING COUNT(DISTINCT target_grp||campaign_desc) > 1);
  1445.  
  1446.  
  1447. SELECT * FROM connection TO teradata
  1448. ( SELECT
  1449.          SUM(X_IB_AT_CC)
  1450.          ,SUM(X_CANADA)
  1451.          ,SUM(X_CORP)
  1452.          ,SUM(X_COLLECTIONS)
  1453.          ,SUM(X_CONTACT_STRATEGY_EM)
  1454.          ,SUM(X_CONTACT_STRATEGY_sms)
  1455.          ,SUM(X_IB)
  1456.          ,SUM(X_MILITARY_SUSPEND )
  1457.          ,SUM(X_MINOR)
  1458.          ,SUM(X_SEASONAL_SUSPEND )
  1459.          ,SUM(X_upgrade)
  1460.          ,SUM(X_TP_BLANK)
  1461.          ,SUM(X_TRADE)
  1462.          ,SUM(X_UNIVERSAL_CONTROL)
  1463.          ,SUM(X_COLLECTIONS2)
  1464.          ,SUM(X_CAMPAIGN)
  1465.          ,SUM(x_do_not_sell)
  1466.          ,SUM(X_VERSION_EXCLUDE_EM)
  1467.          ,SUM(X_VERSION_EXCLUDE_SMS)
  1468.          ,SUM(X_SPANISH)
  1469.  FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER );
  1470.  
  1471. *******************************************************************
  1472.   WATERFALLLLLLLLLLL
  1473. *******************************************************************;
  1474.  
  1475.   EXECUTE(CREATE volatile TABLE waterfall
  1476.         (
  1477.           x_code         CHAR (03)              
  1478.          ,descrp         CHAR (60)              
  1479.          ,record_count   INTEGER  format '9(9)'  
  1480.         )
  1481.         PRIMARY INDEX(x_code) ON commit preserve ROWS ) BY teradata;
  1482.  
  1483.   EXECUTE(INSERT INTO waterfall    
  1484.    SELECT                                      
  1485.           '00'                              
  1486.          ,'Active Subs IL+CL'                        
  1487.          ,COUNT(*)
  1488.   FROM P5P_MEDP_VIEW.VM33_CSLD_CPGN
  1489.   WHERE ACT_CD = 'Y'
  1490.    GROUP BY 1   ) BY teradata;
  1491.  
  1492.     EXECUTE(INSERT INTO waterfall    
  1493.    SELECT                                      
  1494.           '01'                              
  1495.          ,'Active iPhone Subs'                        
  1496.          ,COUNT(*)
  1497.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1498.    GROUP BY 1   ) BY teradata;
  1499.  
  1500.  EXECUTE(INSERT INTO waterfall    
  1501.    SELECT                                      
  1502.           '03'                              
  1503.          ,'Keep if Upgrade Eligible'                        
  1504.          ,COUNT(*)
  1505.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1506.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  1507.    GROUP BY 1   ) BY teradata;
  1508.  
  1509.  
  1510.   EXECUTE(INSERT INTO waterfall    
  1511.    SELECT                                      
  1512.           '04'                              
  1513.          ,'Exclude AT/ST/ credit classes'                        
  1514.          ,COUNT(*)
  1515.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1516.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  1517.    AND X_IB_AT_CC = 0
  1518.    GROUP BY 1   ) BY teradata;
  1519.  
  1520. EXECUTE(INSERT INTO waterfall    
  1521.    SELECT                                      
  1522.           '05'                              
  1523.          ,'Exclude Vacation/Military Suspend'                        
  1524.          ,COUNT(*)
  1525.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1526.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  1527.    AND X_IB_AT_CC = 0
  1528.    AND X_SEASONAL_SUSPEND = 0
  1529.   AND X_MILITARY_SUSPEND = 0
  1530.    GROUP BY 1   ) BY teradata;
  1531.  
  1532. EXECUTE(INSERT INTO waterfall    
  1533.    SELECT                                      
  1534.           '06'                              
  1535.          ,'Exclude Collections 30+ days (reactive scrub)'                        
  1536.          ,COUNT(*)
  1537.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1538.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  1539.    AND X_IB_AT_CC = 0
  1540.    AND X_SEASONAL_SUSPEND = 0
  1541.   AND X_MILITARY_SUSPEND = 0
  1542.   AND X_COLLECTIONS30 = 0
  1543.    GROUP BY 1   ) BY teradata;
  1544.  
  1545.  EXECUTE(INSERT INTO waterfall    
  1546.    SELECT                                      
  1547.           '07'                              
  1548.          ,'Exclude TRADE (include SWAC I4,I5)'                        
  1549.          ,COUNT(*)
  1550.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1551.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  1552.    AND X_IB_AT_CC = 0
  1553.    AND X_SEASONAL_SUSPEND = 0
  1554.   AND X_MILITARY_SUSPEND = 0
  1555.   AND X_COLLECTIONS30 = 0
  1556.   AND X_TRADE = 0
  1557.    GROUP BY 1   ) BY teradata;
  1558.  
  1559.   EXECUTE(INSERT INTO waterfall    
  1560.    SELECT                                      
  1561.           '08'                              
  1562.          ,'Exclude if open IB'                        
  1563.          ,COUNT(*)
  1564.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1565.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  1566.    AND X_IB_AT_CC = 0
  1567.    AND X_SEASONAL_SUSPEND = 0
  1568.   AND X_MILITARY_SUSPEND = 0
  1569.   AND X_COLLECTIONS30 = 0
  1570.   AND X_TRADE = 0
  1571.   AND X_IB = 0
  1572.    GROUP BY 1   ) BY teradata;
  1573.  
  1574.  
  1575. EXECUTE(INSERT INTO waterfall    
  1576.    SELECT                                      
  1577.           '09'                              
  1578.          ,'Exclude Universal Control'                        
  1579.          ,COUNT(*)
  1580.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1581.   WHERE TARGET_GRP IS NOT NULL
  1582.    AND X_UPGRADE = 0
  1583.    AND X_IB_AT_CC = 0
  1584.    AND X_SEASONAL_SUSPEND = 0
  1585.   AND X_MILITARY_SUSPEND = 0
  1586.   AND X_COLLECTIONS30 = 0
  1587.   AND X_TRADE = 0
  1588.   AND X_IB = 0
  1589.   AND x_UNIVERSAL_CONTROL = 0
  1590.    GROUP BY 1   ) BY teradata;
  1591.  
  1592.  EXECUTE(INSERT INTO waterfall    
  1593.    SELECT                                      
  1594.           '10'                              
  1595.          ,'Exclude Do Not Sell BANs (probable fraud)'                        
  1596.          ,COUNT(*)
  1597.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1598.   WHERE TARGET_GRP IS NOT NULL
  1599.    AND X_UPGRADE = 0
  1600.    AND X_IB_AT_CC = 0
  1601.    AND X_SEASONAL_SUSPEND = 0
  1602.   AND X_MILITARY_SUSPEND = 0
  1603.   AND X_COLLECTIONS30 = 0
  1604.   AND X_TRADE = 0
  1605.   AND X_IB = 0
  1606.   AND X_UNIVERSAL_CONTROL = 0
  1607.   AND x_do_not_sell = 0
  1608.    GROUP BY 1   ) BY teradata;
  1609.  
  1610.  EXECUTE(INSERT INTO waterfall    
  1611.    SELECT                                      
  1612.           '11'                              
  1613.          ,'Exclude subs on 1 yr free plan'                        
  1614.          ,COUNT(*)
  1615.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1616.   WHERE TARGET_GRP IS NOT NULL
  1617.    AND X_UPGRADE = 0
  1618.    AND X_IB_AT_CC = 0
  1619.    AND X_SEASONAL_SUSPEND = 0
  1620.   AND X_MILITARY_SUSPEND = 0
  1621.   AND X_COLLECTIONS30 = 0
  1622.   AND X_TRADE = 0
  1623.   AND X_IB = 0
  1624.   AND X_UNIVERSAL_CONTROL = 0
  1625.   AND x_do_not_sell = 0
  1626.   AND x_free_plan = 0
  1627.    GROUP BY 1   ) BY teradata;
  1628.  
  1629.  
  1630.  
  1631. /*execute(INSERT INTO waterfall    */
  1632. /*   select                                       */
  1633. /*          '20'                               */
  1634. /*         ,'Exclude Contact Strategy (Except 104/106)'                         */
  1635. /*         ,count(*)*/
  1636. /*  from P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  1637. /*  where TARGET_GRP IS NOT NULL */
  1638. /*   AND X_UPGRADE = 0*/
  1639. /*   and X_IB_AT_CC = 0*/
  1640. /*   and X_SEASONAL_SUSPEND = 0*/
  1641. /*  AND X_MILITARY_SUSPEND = 0*/
  1642. /*  and X_COLLECTIONS30 = 0*/
  1643. /*  AND X_TRADE = 0*/
  1644. /*  and X_IB = 0*/
  1645. /*  and X_UNIVERSAL_CONTROL = 0*/
  1646. /*  and x_do_not_sell = 0*/
  1647. /*  and x_free_plan = 0*/
  1648. /*  and X_CONTACT_STRATEGY = 0*/
  1649. /*   group by 1   ) by teradata;*/
  1650.  
  1651.  
  1652.  EXECUTE(INSERT INTO waterfall    
  1653.    SELECT                                      
  1654.           '21'                              
  1655.          ,'Exclude TMO trigger leads/Abandoned Cart from Proactive'                        
  1656.          ,COUNT(*)
  1657.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1658.   WHERE TARGET_GRP IS NOT NULL
  1659.    AND X_UPGRADE = 0
  1660.    AND X_IB_AT_CC = 0
  1661.    AND X_SEASONAL_SUSPEND = 0
  1662.   AND X_MILITARY_SUSPEND = 0
  1663.   AND X_COLLECTIONS30 = 0
  1664.   AND X_TRADE = 0
  1665.   AND X_IB = 0
  1666.   AND X_UNIVERSAL_CONTROL = 0
  1667.   AND x_do_not_sell = 0
  1668.   AND x_free_plan = 0
  1669.   AND x_campaign = 0
  1670.    GROUP BY 1   ) BY teradata;
  1671.  
  1672.  
  1673.  
  1674.  
  1675. EXECUTE(INSERT INTO waterfall    
  1676.    SELECT                                      
  1677.           '22'                              
  1678.          ,'Exclude Collections - proactive scrub'                        
  1679.          ,COUNT(*)
  1680.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1681.   WHERE TARGET_GRP IS NOT NULL
  1682.    AND X_UPGRADE = 0
  1683.    AND X_IB_AT_CC = 0
  1684.    AND X_SEASONAL_SUSPEND = 0
  1685.   AND X_MILITARY_SUSPEND = 0
  1686.   AND X_COLLECTIONS30 = 0
  1687.   AND X_TRADE = 0
  1688.   AND X_IB = 0
  1689.   AND X_UNIVERSAL_CONTROL = 0
  1690.   AND x_do_not_sell = 0
  1691.   AND x_free_plan = 0
  1692.   AND x_campaign = 0
  1693.   AND X_COLLECTIONS = 0
  1694.    GROUP BY 1   ) BY teradata;
  1695.  
  1696.  
  1697.   EXECUTE(INSERT INTO waterfall    
  1698.    SELECT                                      
  1699.           '23'                              
  1700.          ,'Exclude Corp Exclusions'                        
  1701.          ,COUNT(*)
  1702.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1703.   WHERE TARGET_GRP IS NOT NULL
  1704.    AND X_UPGRADE = 0
  1705.    AND X_IB_AT_CC = 0
  1706.    AND X_SEASONAL_SUSPEND = 0
  1707.   AND X_MILITARY_SUSPEND = 0
  1708.   AND X_COLLECTIONS30 = 0
  1709.   AND X_TRADE = 0
  1710.   AND X_IB = 0
  1711.   AND X_UNIVERSAL_CONTROL = 0
  1712.   AND x_do_not_sell = 0
  1713.   AND x_free_plan = 0
  1714.   AND x_campaign = 0
  1715.   AND X_COLLECTIONS = 0
  1716.   AND X_CORP = 0
  1717.    GROUP BY 1   ) BY teradata;
  1718.  
  1719.   EXECUTE(INSERT INTO waterfall    
  1720.    SELECT                                      
  1721.           '24'                              
  1722.          ,'Exclude Canada'                        
  1723.          ,COUNT(*)
  1724.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1725.   WHERE TARGET_GRP IS NOT NULL
  1726.    AND X_UPGRADE = 0
  1727.    AND X_IB_AT_CC = 0
  1728.    AND X_SEASONAL_SUSPEND = 0
  1729.   AND X_MILITARY_SUSPEND = 0
  1730.   AND X_COLLECTIONS30 = 0
  1731.   AND X_TRADE = 0
  1732.   AND X_IB = 0
  1733.   AND X_UNIVERSAL_CONTROL = 0
  1734.   AND x_do_not_sell = 0
  1735.   AND x_free_plan = 0
  1736.   AND x_campaign = 0
  1737.   AND X_COLLECTIONS = 0
  1738.   AND X_CORP = 0
  1739.   AND X_CANADA = 0
  1740.    GROUP BY 1   ) BY teradata;
  1741.  
  1742.  
  1743. EXECUTE(INSERT INTO waterfall    
  1744.    SELECT                                      
  1745.           '25'                              
  1746.          ,'Exclude if Treatment Plan is Blank'                        
  1747.          ,COUNT(*)
  1748.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1749.   WHERE TARGET_GRP IS NOT NULL
  1750.    AND X_UPGRADE = 0
  1751.    AND X_IB_AT_CC = 0
  1752.    AND X_SEASONAL_SUSPEND = 0
  1753.   AND X_MILITARY_SUSPEND = 0
  1754.   AND X_COLLECTIONS30 = 0
  1755.   AND X_TRADE = 0
  1756.   AND X_IB = 0
  1757.   AND X_UNIVERSAL_CONTROL = 0
  1758.   AND x_do_not_sell = 0
  1759.   AND x_free_plan = 0
  1760.   AND x_campaign = 0
  1761.   AND X_COLLECTIONS = 0
  1762.   AND X_CORP = 0
  1763.   AND X_CANADA = 0
  1764.   AND X_TP_BLANK = 0
  1765.    GROUP BY 1   ) BY teradata;
  1766.  
  1767.  
  1768.  
  1769. EXECUTE(INSERT INTO waterfall    
  1770.    SELECT                                      
  1771.           '26'                              
  1772.          ,'Exclude if in McKinsey Collections group'                        
  1773.          ,COUNT(*)
  1774.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1775.   WHERE TARGET_GRP IS NOT NULL
  1776.    AND X_UPGRADE = 0
  1777.    AND X_IB_AT_CC = 0
  1778.    AND X_SEASONAL_SUSPEND = 0
  1779.   AND X_MILITARY_SUSPEND = 0
  1780.   AND X_COLLECTIONS30 = 0
  1781.   AND X_TRADE = 0
  1782.   AND X_IB = 0
  1783.   AND X_UNIVERSAL_CONTROL = 0
  1784.   AND x_do_not_sell = 0
  1785.   AND x_free_plan = 0
  1786.   AND x_campaign = 0
  1787.   AND X_COLLECTIONS = 0
  1788.   AND X_CORP = 0
  1789.   AND X_CANADA = 0
  1790.   AND X_TP_BLANK = 0
  1791.   AND X_UNIVERSAL_CONTROL = 0
  1792.   AND X_COLLECTIONS2 = 0
  1793.    GROUP BY 1   ) BY teradata;
  1794.  
  1795.  EXECUTE(INSERT INTO waterfall    
  1796.    SELECT                                      
  1797.           '27'                              
  1798.          ,'Exclude CL from Proactive'                        
  1799.          ,COUNT(*)
  1800.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1801.   WHERE TARGET_GRP IS NOT NULL
  1802.    AND X_UPGRADE = 0
  1803.    AND X_IB_AT_CC = 0
  1804.    AND X_SEASONAL_SUSPEND = 0
  1805.   AND X_MILITARY_SUSPEND = 0
  1806.   AND X_COLLECTIONS30 = 0
  1807.   AND X_TRADE = 0
  1808.   AND X_IB = 0
  1809.   AND X_UNIVERSAL_CONTROL = 0
  1810.   AND x_do_not_sell = 0
  1811.   AND x_free_plan = 0
  1812.   AND x_campaign = 0
  1813.   AND X_COLLECTIONS = 0
  1814.   AND X_CORP = 0
  1815.   AND X_CANADA = 0
  1816.   AND X_TP_BLANK = 0
  1817.   AND X_UNIVERSAL_CONTROL = 0
  1818.   AND X_COLLECTIONS2 = 0
  1819.   AND LIAB_CD = 'IL'
  1820.    GROUP BY 1   ) BY teradata;
  1821.  
  1822.  
  1823.  
  1824.    EXECUTE(INSERT INTO waterfall    
  1825.    SELECT                                      
  1826.           '28'                              
  1827.          ,'Exclude iPhone Forever Gen w/8 from proactive'                        
  1828.          ,COUNT(*)
  1829.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1830.   WHERE TARGET_GRP IS NOT NULL
  1831.    AND X_UPGRADE = 0
  1832.    AND X_IB_AT_CC = 0
  1833.    AND X_SEASONAL_SUSPEND = 0
  1834.   AND X_MILITARY_SUSPEND = 0
  1835.   AND X_COLLECTIONS30 = 0
  1836.   AND X_TRADE = 0
  1837.   AND X_IB = 0
  1838.   AND X_UNIVERSAL_CONTROL = 0
  1839.   AND x_do_not_sell = 0
  1840.   AND x_free_plan = 0
  1841.   AND x_campaign = 0
  1842.   AND X_COLLECTIONS = 0
  1843.   AND X_CORP = 0
  1844.   AND X_CANADA = 0
  1845.   AND X_TP_BLANK = 0
  1846.   AND X_UNIVERSAL_CONTROL = 0
  1847.   AND X_COLLECTIONS2 = 0
  1848.   AND LIAB_CD = 'IL'
  1849.   AND X_IPFG8 = 0
  1850.    GROUP BY 1   ) BY teradata;
  1851.  
  1852. /*   execute(INSERT INTO waterfall    */
  1853. /*   select                                       */
  1854. /*          '29'                               */
  1855. /*         ,'Within 10 miles of a P360 store'                         */
  1856. /*         ,count(*)*/
  1857. /*  from P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER*/
  1858. /*  where TARGET_GRP IS NOT NULL */
  1859. /*   AND X_UPGRADE = 0*/
  1860. /*   and X_IB_AT_CC = 0*/
  1861. /*   and X_SEASONAL_SUSPEND = 0*/
  1862. /*  AND X_MILITARY_SUSPEND = 0*/
  1863. /*  and X_COLLECTIONS30 = 0*/
  1864. /*  AND X_TRADE = 0*/
  1865. /*  and X_IB = 0*/
  1866. /*  and X_UNIVERSAL_CONTROL = 0*/
  1867. /*  and x_do_not_sell = 0*/
  1868. /*  and x_free_plan = 0*/
  1869. /*  and x_campaign = 0*/
  1870. /*  and X_COLLECTIONS = 0*/
  1871. /*  and X_CORP = 0*/
  1872. /*  and X_CANADA = 0*/
  1873. /*  and X_TP_BLANK = 0*/
  1874. /*  and X_UNIVERSAL_CONTROL = 0*/
  1875. /*  and X_COLLECTIONS2 = 0*/
  1876. /*  and LIAB_CD = 'IL'*/
  1877. /*  and X_IPFG8 = 0*/
  1878. /*  and STORE_FLAG = 'Y'*/
  1879. /*   group by 1   ) by teradata;*/
  1880.  
  1881.  
  1882.  EXECUTE(INSERT INTO waterfall    
  1883.    SELECT                                      
  1884.           '30'                              
  1885.          ,'Exclude Spanish'                        
  1886.          ,COUNT(*)
  1887.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1888.   WHERE TARGET_GRP IS NOT NULL
  1889.    AND X_UPGRADE = 0
  1890.    AND X_IB_AT_CC = 0
  1891.    AND X_SEASONAL_SUSPEND = 0
  1892.   AND X_MILITARY_SUSPEND = 0
  1893.   AND X_COLLECTIONS30 = 0
  1894.   AND X_TRADE = 0
  1895.   AND X_IB = 0
  1896.   AND X_UNIVERSAL_CONTROL = 0
  1897.   AND x_do_not_sell = 0
  1898.   AND x_free_plan = 0
  1899.   AND x_campaign = 0
  1900.   AND X_COLLECTIONS = 0
  1901.   AND X_CORP = 0
  1902.   AND X_CANADA = 0
  1903.   AND X_TP_BLANK = 0
  1904.   AND X_UNIVERSAL_CONTROL = 0
  1905.   AND X_COLLECTIONS2 = 0
  1906.   AND LIAB_CD = 'IL'
  1907.   AND X_IPFG8 = 0
  1908.   AND X_SPANISH = 0
  1909.    GROUP BY 1   ) BY teradata;
  1910.  
  1911.    EXECUTE(INSERT INTO waterfall    
  1912.    SELECT                                      
  1913.           '39'                              
  1914.          ,'Exclude Contact Strategy EM'                        
  1915.          ,COUNT(*)
  1916.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1917.   WHERE TARGET_GRP IS NOT NULL
  1918.    AND X_UPGRADE = 0
  1919.    AND X_IB_AT_CC = 0
  1920.    AND X_SEASONAL_SUSPEND = 0
  1921.   AND X_MILITARY_SUSPEND = 0
  1922.   AND X_COLLECTIONS30 = 0
  1923.   AND X_TRADE = 0
  1924.   AND X_IB = 0
  1925.   AND X_UNIVERSAL_CONTROL = 0
  1926.   AND x_do_not_sell = 0
  1927.   AND x_free_plan = 0
  1928.   AND x_campaign = 0
  1929.   AND X_COLLECTIONS = 0
  1930.   AND X_CORP = 0
  1931.   AND X_CANADA = 0
  1932.   AND X_TP_BLANK = 0
  1933.   AND X_UNIVERSAL_CONTROL = 0
  1934.   AND X_COLLECTIONS2 = 0
  1935.   AND LIAB_CD = 'IL'
  1936.   AND X_IPFG8 = 0
  1937. /*  and STORE_FLAG = 'Y'*/
  1938.   AND X_SPANISH = 0
  1939.   AND X_CONTACT_STRATEGY_EM = 0
  1940.    GROUP BY 1   ) BY teradata;
  1941.  
  1942.  
  1943.  
  1944. EXECUTE(INSERT INTO waterfall    
  1945.    SELECT                                      
  1946.           '40'                              
  1947.          ,'Keep Email Opt Ins'                        
  1948.          ,COUNT(*)
  1949.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1950.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  1951.    AND X_IB_AT_CC = 0
  1952.    AND X_SEASONAL_SUSPEND = 0
  1953.   AND X_MILITARY_SUSPEND = 0
  1954.   AND X_COLLECTIONS = 0
  1955.   AND X_TRADE = 0
  1956.   AND X_IB = 0
  1957. /*  and X_MINOR = 0*/  /* Excluded in EM eligible flag */
  1958.   AND X_CORP = 0
  1959.   AND X_CANADA = 0
  1960.   AND X_TP_BLANK = 0
  1961.   AND X_UNIVERSAL_CONTROL = 0
  1962.   AND X_COLLECTIONS2 = 0
  1963. /* and x_spanish = 0*/
  1964.   AND LIAB_CD = 'IL'
  1965.   AND X_CAMPAIGN = 0
  1966.   AND x_do_not_sell = 0
  1967.   AND x_free_plan = 0
  1968.   AND X_IPFG8 = 0
  1969.   AND ELIGIBLE_FLAG = 'Y'
  1970.   AND X_CONTACT_STRATEGY_EM = 0
  1971.   AND email_adr IS NOT NULL
  1972.    GROUP BY 1   ) BY teradata;
  1973.  
  1974.  EXECUTE(INSERT INTO waterfall    
  1975.    SELECT                                      
  1976.           '41'                              
  1977.          ,'Exclude SpamHaus Emails'                        
  1978.          ,COUNT(*)
  1979.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  1980.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  1981.    AND X_IB_AT_CC = 0
  1982.    AND X_SEASONAL_SUSPEND = 0
  1983.   AND X_MILITARY_SUSPEND = 0
  1984.   AND X_COLLECTIONS = 0
  1985.   AND X_TRADE = 0
  1986.   AND X_IB = 0
  1987.   AND X_CORP = 0
  1988.   AND X_CANADA = 0
  1989.   AND X_TP_BLANK = 0
  1990.   AND X_UNIVERSAL_CONTROL = 0
  1991.   AND X_COLLECTIONS2 = 0
  1992. /*and x_spanish = 0*/
  1993.   AND LIAB_CD = 'IL'
  1994.   AND X_CAMPAIGN = 0
  1995.   AND x_do_not_sell = 0
  1996.  AND x_free_plan = 0
  1997.   AND X_IPFG8 = 0
  1998.   AND ELIGIBLE_FLAG = 'Y'
  1999.   AND X_CONTACT_STRATEGY_EM = 0
  2000.   AND email_adr IS NOT NULL
  2001.   AND X_EMAIL = 0
  2002.    GROUP BY 1   ) BY teradata;
  2003.  
  2004.  EXECUTE(INSERT INTO waterfall    
  2005.    SELECT                                      
  2006.           '42'                              
  2007.          ,'Exclude Unengaged Emails'                        
  2008.          ,COUNT(*)
  2009.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2010.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2011.    AND X_IB_AT_CC = 0
  2012.    AND X_SEASONAL_SUSPEND = 0
  2013.   AND X_MILITARY_SUSPEND = 0
  2014.   AND X_COLLECTIONS = 0
  2015.   AND X_TRADE = 0
  2016.   AND X_IB = 0
  2017.   AND X_CORP = 0
  2018.   AND X_CANADA = 0
  2019.   AND X_TP_BLANK = 0
  2020.   AND X_UNIVERSAL_CONTROL = 0
  2021.   AND X_COLLECTIONS2 = 0
  2022.   AND LIAB_CD = 'IL'
  2023.   AND X_CAMPAIGN = 0
  2024.   AND x_do_not_sell = 0
  2025.    AND x_free_plan = 0
  2026.   AND X_IPFG8 = 0
  2027.   AND ELIGIBLE_FLAG = 'Y'
  2028.   AND X_CONTACT_STRATEGY_EM = 0
  2029.   AND email_adr IS NOT NULL
  2030.   AND X_EMAIL = 0
  2031.   AND X_EMAIL_UNENGAGE = 0
  2032.    GROUP BY 1   ) BY teradata;
  2033.  
  2034. EXECUTE(INSERT INTO waterfall    
  2035.    SELECT                                      
  2036.           '43'                              
  2037.          ,'Exclude versions not sending'                        
  2038.          ,COUNT(*)
  2039.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2040.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2041.    AND X_IB_AT_CC = 0
  2042.    AND X_SEASONAL_SUSPEND = 0
  2043.   AND X_MILITARY_SUSPEND = 0
  2044.   AND X_COLLECTIONS = 0
  2045.   AND X_TRADE = 0
  2046.   AND X_IB = 0
  2047.   AND X_CORP = 0
  2048.   AND X_CANADA = 0
  2049.   AND X_TP_BLANK = 0
  2050.   AND X_UNIVERSAL_CONTROL = 0
  2051.   AND X_COLLECTIONS2 = 0
  2052.   AND LIAB_CD = 'IL'
  2053.   AND X_CAMPAIGN = 0
  2054.   AND x_do_not_sell = 0
  2055.    AND x_free_plan = 0
  2056.   AND X_IPFG8 = 0
  2057.   AND x_version_exclude_em = 0
  2058.   AND ELIGIBLE_FLAG = 'Y'
  2059.   AND X_CONTACT_STRATEGY_EM = 0
  2060.   AND email_adr IS NOT NULL
  2061.   AND X_EMAIL = 0
  2062.   AND X_EMAIL_UNENGAGE = 0
  2063.    GROUP BY 1   ) BY teradata;
  2064.  
  2065.   EXECUTE(INSERT INTO waterfall    
  2066.    SELECT                                      
  2067.           '44'                              
  2068.          ,'Send one offer per email, exclude rest'                        
  2069.          ,COUNT(*)
  2070.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2071.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2072.    AND X_IB_AT_CC = 0
  2073.    AND X_SEASONAL_SUSPEND = 0
  2074.   AND X_MILITARY_SUSPEND = 0
  2075.   AND X_COLLECTIONS = 0
  2076.   AND X_TRADE = 0
  2077.   AND X_IB = 0
  2078.   AND X_CORP = 0
  2079.   AND X_CANADA = 0
  2080.   AND X_TP_BLANK = 0
  2081.   AND X_UNIVERSAL_CONTROL = 0
  2082.   AND X_COLLECTIONS2 = 0
  2083.   AND LIAB_CD = 'IL'
  2084.   AND X_CAMPAIGN = 0
  2085.   AND x_do_not_sell = 0
  2086.    AND x_free_plan = 0
  2087.   AND X_IPFG8 = 0
  2088.   AND x_version_exclude_em = 0
  2089.   AND ELIGIBLE_FLAG = 'Y'
  2090.   AND X_CONTACT_STRATEGY_EM = 0
  2091.   AND email_adr IS NOT NULL
  2092.   AND X_EMAIL = 0
  2093.   AND X_EMAIL_UNENGAGE = 0
  2094.   AND BAN_MIXED_NE_FLAG  = 'N'
  2095.    GROUP BY 1   ) BY teradata;
  2096.  
  2097.   EXECUTE(INSERT INTO waterfall    
  2098.    SELECT                                      
  2099.           '45'                              
  2100.          ,'De-dupe by email address'                        
  2101.          ,COUNT(DISTINCT email_adr)
  2102.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2103.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2104.    AND X_IB_AT_CC = 0
  2105.    AND X_SEASONAL_SUSPEND = 0
  2106.   AND X_MILITARY_SUSPEND = 0
  2107.   AND X_COLLECTIONS = 0
  2108.   AND X_TRADE = 0
  2109.   AND X_IB = 0
  2110.   AND X_CORP = 0
  2111.   AND X_CANADA = 0
  2112.   AND X_TP_BLANK = 0
  2113.   AND X_UNIVERSAL_CONTROL = 0
  2114.   AND X_COLLECTIONS2 = 0
  2115.   AND LIAB_CD = 'IL'
  2116.   AND X_CAMPAIGN = 0
  2117.   AND x_do_not_sell = 0
  2118.   AND x_free_plan = 0
  2119.   AND X_IPFG8 = 0
  2120.   AND x_version_exclude_em = 0
  2121.   AND ELIGIBLE_FLAG = 'Y'
  2122.   AND X_CONTACT_STRATEGY_EM = 0
  2123.   AND email_adr IS NOT NULL
  2124.   AND X_EMAIL = 0
  2125.   AND X_EMAIL_UNENGAGE = 0
  2126.   AND BAN_MIXED_NE_FLAG  = 'N'
  2127.    GROUP BY 1   ) BY teradata;
  2128.  
  2129.  
  2130. EXECUTE(INSERT INTO waterfall    
  2131.    SELECT                                      
  2132.           '49'                              
  2133.          ,'Exclude Contact Strategy SMS'                        
  2134.          ,COUNT(*)
  2135.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2136.   WHERE  TARGET_GRP IS NOT NULL
  2137.    AND X_UPGRADE = 0
  2138.    AND X_IB_AT_CC = 0
  2139.    AND X_SEASONAL_SUSPEND = 0
  2140.   AND X_MILITARY_SUSPEND = 0
  2141.   AND X_COLLECTIONS30 = 0
  2142.   AND X_TRADE = 0
  2143.   AND X_CONTACT_STRATEGY_SMS = 0
  2144.   AND X_IB = 0
  2145.   AND X_UNIVERSAL_CONTROL = 0
  2146.   AND x_do_not_sell = 0
  2147.   AND x_campaign = 0
  2148.   AND X_COLLECTIONS = 0
  2149.   AND X_CORP = 0
  2150.   AND X_CANADA = 0
  2151.   AND X_TP_BLANK = 0
  2152.   AND X_UNIVERSAL_CONTROL = 0
  2153.   AND X_COLLECTIONS2 = 0
  2154.   AND LIAB_CD = 'IL'
  2155.   AND x_free_plan = 0
  2156.   AND X_IPFG8 = 0
  2157.   AND ELIGIBLE_FLAG = 'Y'
  2158.    GROUP BY 1   ) BY teradata;
  2159.  
  2160.  EXECUTE(INSERT INTO waterfall    
  2161.    SELECT                                      
  2162.           '50'                              
  2163.          ,'SMS Capable Device'                        
  2164.          ,COUNT(*)
  2165.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2166.   WHERE  TARGET_GRP IS NOT NULL
  2167.    AND X_UPGRADE = 0
  2168.    AND X_IB_AT_CC = 0
  2169.    AND X_SEASONAL_SUSPEND = 0
  2170.   AND X_MILITARY_SUSPEND = 0
  2171.   AND X_COLLECTIONS30 = 0
  2172.   AND X_TRADE = 0
  2173.   AND X_CONTACT_STRATEGY_SMS = 0
  2174.   AND X_IB = 0
  2175.   AND X_UNIVERSAL_CONTROL = 0
  2176.   AND x_do_not_sell = 0
  2177.   AND x_campaign = 0
  2178.   AND X_COLLECTIONS = 0
  2179.   AND X_CORP = 0
  2180.   AND X_CANADA = 0
  2181.   AND X_TP_BLANK = 0
  2182.   AND X_UNIVERSAL_CONTROL = 0
  2183.   AND X_COLLECTIONS2 = 0
  2184.   AND LIAB_CD = 'IL'
  2185.   AND x_free_plan = 0
  2186.   AND X_IPFG8 = 0
  2187.   AND ELIGIBLE_FLAG = 'Y'
  2188.   AND X_SMS_CAPABLE = 0
  2189.    GROUP BY 1   ) BY teradata;
  2190.  
  2191.  EXECUTE(INSERT INTO waterfall    
  2192.    SELECT                                      
  2193.           '51'                              
  2194.          ,'SMS Exclude SMS Opt Outs'                        
  2195.          ,COUNT(*)
  2196.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2197.   WHERE  TARGET_GRP IS NOT NULL
  2198.    AND X_UPGRADE = 0
  2199.    AND X_IB_AT_CC = 0
  2200.    AND X_SEASONAL_SUSPEND = 0
  2201.   AND X_MILITARY_SUSPEND = 0
  2202.   AND X_COLLECTIONS30 = 0
  2203.   AND X_TRADE = 0
  2204.   AND X_CONTACT_STRATEGY_SMS = 0
  2205.   AND X_IB = 0
  2206.   AND X_UNIVERSAL_CONTROL = 0
  2207.   AND x_do_not_sell = 0
  2208.   AND x_campaign = 0
  2209.   AND X_COLLECTIONS = 0
  2210.   AND X_CORP = 0
  2211.   AND X_CANADA = 0
  2212.   AND X_TP_BLANK = 0
  2213.   AND X_UNIVERSAL_CONTROL = 0
  2214.   AND X_COLLECTIONS2 = 0
  2215.   AND LIAB_CD = 'IL'
  2216.    AND x_free_plan = 0
  2217.   AND X_IPFG8 = 0
  2218.   AND ELIGIBLE_FLAG = 'Y'
  2219.   AND X_SMS_CAPABLE = 0
  2220.   AND X_SMS_OPT_OUT = 0
  2221.    GROUP BY 1   ) BY teradata;
  2222.  
  2223.  EXECUTE(INSERT INTO waterfall    
  2224.    SELECT                                      
  2225.           '52'                              
  2226.          ,'SMS Exclude Sprint DNC'                        
  2227.          ,COUNT(*)
  2228.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2229.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2230.    AND X_IB_AT_CC = 0
  2231.    AND X_SEASONAL_SUSPEND = 0
  2232.   AND X_MILITARY_SUSPEND = 0
  2233.   AND X_COLLECTIONS = 0
  2234.   AND X_TRADE = 0
  2235.   AND X_CONTACT_STRATEGY_SMS = 0
  2236.   AND X_IB = 0
  2237.   AND X_CORP = 0
  2238.   AND X_CANADA = 0
  2239.   AND X_TP_BLANK = 0
  2240.   AND X_UNIVERSAL_CONTROL = 0
  2241.   AND X_COLLECTIONS2 = 0
  2242. /*and x_spanish = 0*/
  2243.   AND LIAB_CD = 'IL'
  2244.   AND X_CAMPAIGN = 0
  2245.   AND x_do_not_sell = 0
  2246.   AND x_free_plan = 0
  2247.   AND X_IPFG8 = 0
  2248.   AND ELIGIBLE_FLAG = 'Y'
  2249.   AND X_SMS_CAPABLE = 0
  2250.   AND X_SMS_OPT_OUT = 0
  2251.   AND X_SMS_SPRINT_DNC = 0
  2252.    GROUP BY 1   ) BY teradata;
  2253.  
  2254.  EXECUTE(INSERT INTO waterfall    
  2255.    SELECT                                      
  2256.           '53'                              
  2257.          ,'SMS Exclude SMS Blacklist'                        
  2258.          ,COUNT(*)
  2259.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2260.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2261.    AND X_IB_AT_CC = 0
  2262.    AND X_SEASONAL_SUSPEND = 0
  2263.   AND X_MILITARY_SUSPEND = 0
  2264.   AND X_COLLECTIONS = 0
  2265.   AND X_TRADE = 0
  2266.   AND X_CONTACT_STRATEGY_SMS = 0
  2267.   AND X_IB = 0
  2268.   AND X_CORP = 0
  2269.   AND X_CANADA = 0
  2270.   AND X_TP_BLANK = 0
  2271.   AND X_UNIVERSAL_CONTROL = 0
  2272.   AND X_COLLECTIONS2 = 0
  2273. /*and x_spanish = 0*/
  2274.   AND LIAB_CD = 'IL'
  2275.   AND X_CAMPAIGN = 0
  2276.   AND x_do_not_sell = 0
  2277.    AND x_free_plan = 0
  2278.   AND X_IPFG8 = 0
  2279.   AND ELIGIBLE_FLAG = 'Y'
  2280.   AND X_SMS_CAPABLE = 0
  2281.   AND X_SMS_OPT_OUT = 0
  2282.   AND X_SMS_SPRINT_DNC = 0
  2283.   AND X_SMS_BLACKLIST = 0
  2284.    GROUP BY 1   ) BY teradata;
  2285.  
  2286.   EXECUTE(INSERT INTO waterfall    
  2287.    SELECT                                      
  2288.           '54'                              
  2289.          ,'SMS Exclude Minors'                        
  2290.          ,COUNT(*)
  2291.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2292.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2293.    AND X_IB_AT_CC = 0
  2294.    AND X_SEASONAL_SUSPEND = 0
  2295.   AND X_MILITARY_SUSPEND = 0
  2296.   AND X_COLLECTIONS = 0
  2297.   AND X_TRADE = 0
  2298.   AND X_CONTACT_STRATEGY_SMS = 0
  2299.   AND X_IB = 0
  2300.   AND X_CORP = 0
  2301.   AND X_CANADA = 0
  2302.   AND X_TP_BLANK = 0
  2303.   AND X_UNIVERSAL_CONTROL = 0
  2304.   AND X_COLLECTIONS2 = 0
  2305. /*and x_spanish = 0*/
  2306.   AND LIAB_CD = 'IL'
  2307.   AND X_CAMPAIGN = 0
  2308.   AND x_do_not_sell = 0
  2309.    AND x_free_plan = 0
  2310.   AND X_IPFG8 = 0
  2311.   AND ELIGIBLE_FLAG = 'Y'
  2312.   AND X_SMS_CAPABLE = 0
  2313.   AND X_SMS_OPT_OUT = 0
  2314.   AND X_SMS_SPRINT_DNC = 0
  2315.   AND X_SMS_BLACKLIST = 0
  2316.   AND X_MINOR = 0
  2317.    GROUP BY 1   ) BY teradata;
  2318.  
  2319.  EXECUTE(INSERT INTO waterfall    
  2320.    SELECT                                      
  2321.           '55'                              
  2322.          ,'SMS Exclude SMS AZ, LA, TX NPAs'                        
  2323.          ,COUNT(*)
  2324.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2325.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2326.    AND X_IB_AT_CC = 0
  2327.    AND X_SEASONAL_SUSPEND = 0
  2328.   AND X_MILITARY_SUSPEND = 0
  2329.   AND X_COLLECTIONS = 0
  2330.   AND X_TRADE = 0
  2331.   AND X_CONTACT_STRATEGY_SMS = 0
  2332.   AND X_IB = 0
  2333.   AND X_CORP = 0
  2334.   AND X_CANADA = 0
  2335.   AND X_TP_BLANK = 0
  2336.   AND X_UNIVERSAL_CONTROL = 0
  2337.   AND X_COLLECTIONS2 = 0
  2338. /*and x_spanish = 0*/
  2339.   AND LIAB_CD = 'IL'
  2340.   AND X_CAMPAIGN = 0
  2341.   AND x_do_not_sell = 0
  2342.   AND x_free_plan = 0
  2343.   AND X_IPFG8 = 0
  2344.   AND ELIGIBLE_FLAG = 'Y'
  2345.   AND X_SMS_CAPABLE = 0
  2346.   AND X_SMS_OPT_OUT = 0
  2347.   AND X_SMS_SPRINT_DNC = 0
  2348.   AND X_SMS_BLACKLIST = 0
  2349.   AND X_MINOR = 0
  2350.   AND X_SMS_STATES = 0
  2351.   AND SMS_ELIGIBLE_FLAG = 'Y'
  2352.    GROUP BY 1   ) BY teradata;
  2353.  
  2354.  EXECUTE(INSERT INTO waterfall    
  2355.    SELECT                                      
  2356.           '56'                              
  2357.          ,'SMS Exclude versions not sending'                        
  2358.          ,COUNT(*)
  2359.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2360.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2361.    AND X_IB_AT_CC = 0
  2362.    AND X_SEASONAL_SUSPEND = 0
  2363.   AND X_MILITARY_SUSPEND = 0
  2364.   AND X_COLLECTIONS = 0
  2365.   AND X_TRADE = 0
  2366.   AND X_CONTACT_STRATEGY_SMS = 0
  2367.   AND X_IB = 0
  2368.   AND X_CORP = 0
  2369.   AND X_CANADA = 0
  2370.   AND X_TP_BLANK = 0
  2371.   AND X_UNIVERSAL_CONTROL = 0
  2372.   AND X_COLLECTIONS2 = 0
  2373. /*and x_spanish = 0*/
  2374.   AND LIAB_CD = 'IL'
  2375.   AND X_CAMPAIGN = 0
  2376.   AND x_do_not_sell = 0
  2377.   AND x_free_plan = 0
  2378.   AND X_IPFG8 = 0
  2379.   AND ELIGIBLE_FLAG = 'Y'
  2380.   AND x_version_exclude_sms = 0
  2381.   AND X_SMS_CAPABLE = 0
  2382.   AND X_SMS_OPT_OUT = 0
  2383.   AND X_SMS_SPRINT_DNC = 0
  2384.   AND X_SMS_BLACKLIST = 0
  2385.   AND X_MINOR = 0
  2386.   AND X_SMS_STATES = 0
  2387.   AND SMS_ELIGIBLE_FLAG = 'Y'
  2388.    GROUP BY 1   ) BY teradata;
  2389.  
  2390. EXECUTE(INSERT INTO waterfall    
  2391.    SELECT                                      
  2392.           '57'                              
  2393.          ,'SMS Decision Maker Test, exclude subs with other offers on BAN'                        
  2394.          ,COUNT(*)
  2395.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2396.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2397.    AND X_IB_AT_CC = 0
  2398.    AND X_SEASONAL_SUSPEND = 0
  2399.   AND X_MILITARY_SUSPEND = 0
  2400.   AND X_COLLECTIONS = 0
  2401.   AND X_TRADE = 0
  2402.   AND X_CONTACT_STRATEGY_SMS = 0
  2403.   AND X_IB = 0
  2404.   AND X_CORP = 0
  2405.   AND X_CANADA = 0
  2406.   AND X_TP_BLANK = 0
  2407.   AND X_UNIVERSAL_CONTROL = 0
  2408.   AND X_COLLECTIONS2 = 0
  2409. /*and x_spanish = 0*/
  2410.   AND LIAB_CD = 'IL'
  2411.   AND X_CAMPAIGN = 0
  2412.   AND x_do_not_sell = 0
  2413.   AND x_free_plan = 0
  2414.   AND X_IPFG8 = 0
  2415.   AND ELIGIBLE_FLAG = 'Y'
  2416.   AND x_version_exclude_sms = 0
  2417.   AND X_SMS_CAPABLE = 0
  2418.   AND X_SMS_OPT_OUT = 0
  2419.   AND X_SMS_SPRINT_DNC = 0
  2420.   AND X_SMS_BLACKLIST = 0
  2421.   AND X_MINOR = 0
  2422.   AND X_SMS_STATES = 0
  2423.   AND BAN_MIXED_NEs_FLAG <> 'X'
  2424.   AND SMS_ELIGIBLE_FLAG = 'Y'
  2425.    GROUP BY 1   ) BY teradata;
  2426.  
  2427.  
  2428.  
  2429.  EXECUTE(INSERT INTO waterfall    
  2430.    SELECT                                      
  2431.           '58'                              
  2432.          ,'Exclude if EM Eligible'                        
  2433.          ,COUNT(*)
  2434.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2435.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2436.    AND X_IB_AT_CC = 0
  2437.    AND X_SEASONAL_SUSPEND = 0
  2438.   AND X_MILITARY_SUSPEND = 0
  2439.   AND X_COLLECTIONS = 0
  2440.   AND X_TRADE = 0
  2441.   AND X_CONTACT_STRATEGY_SMS = 0
  2442.   AND X_IB = 0
  2443.   AND X_MINOR = 0
  2444.   AND X_CORP = 0
  2445.   AND X_CANADA = 0
  2446.   AND X_TP_BLANK = 0
  2447.   AND X_UNIVERSAL_CONTROL = 0
  2448.   AND X_COLLECTIONS2 = 0
  2449. /*and x_spanish = 0*/
  2450.   AND LIAB_CD = 'IL'
  2451.   AND X_CAMPAIGN = 0
  2452.   AND x_do_not_sell = 0
  2453.    AND x_free_plan = 0
  2454.   AND X_IPFG8 = 0
  2455.   AND x_version_exclude_sms = 0
  2456.   AND ELIGIBLE_FLAG = 'Y'
  2457.   AND X_SMS_CAPABLE = 0
  2458.   AND X_SMS_OPT_OUT = 0
  2459.   AND X_SMS_SPRINT_DNC = 0
  2460.   AND X_SMS_BLACKLIST = 0
  2461.   AND X_SMS_STATES = 0
  2462. /*  and X_SMS_SPANISH = 0*/
  2463.   AND SMS_ELIGIBLE_FLAG = 'Y'
  2464.   AND BAN_MIXED_NEs_FLAG <> 'X'
  2465.   AND EM_ELIGIBLE_BAN_FLAG = 'N'
  2466.    GROUP BY 1   ) BY teradata;
  2467.  
  2468.  ;
  2469. SELECT * FROM connection TO teradata
  2470. ( SELECT *
  2471.   FROM waterfall
  2472.   ORDER BY 1);
  2473.  
  2474.  
  2475. CREATE TABLE IB AS
  2476. SELECT * FROM CONNECTION TO TERADATA
  2477. ( SELECT *
  2478.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2479.   WHERE X_UPGRADE = 0
  2480.    AND X_IB_AT_CC = 0
  2481.    AND X_SEASONAL_SUSPEND = 0
  2482.   AND X_MILITARY_SUSPEND = 0
  2483.   AND X_COLLECTIONS30 = 0
  2484.   AND X_TRADE = 0
  2485. /*  and X_CONTACT_STRATEGY = 0*/
  2486.   AND X_IB = 0
  2487. /*  and X_MINOR = 0*/
  2488. /*  and X_CORP = 0*/
  2489. /*  and X_CANADA = 0*/
  2490. /*  and X_TP_BLANK = 0*/
  2491.   AND X_UNIVERSAL_CONTROL = 0
  2492.   AND x_do_not_sell = 0
  2493.   AND x_free_plan = 0
  2494. /*  and x_campaign = 0*/
  2495.   AND campaign_cd_nba IS NOT NULL );
  2496.  
  2497.  
  2498. CREATE TABLE EM AS
  2499. SELECT * FROM CONNECTION TO TERADATA
  2500. ( SELECT *
  2501.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2502.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2503.    AND X_IB_AT_CC = 0
  2504.    AND X_SEASONAL_SUSPEND = 0
  2505.   AND X_MILITARY_SUSPEND = 0
  2506.   AND X_COLLECTIONS = 0
  2507.   AND X_TRADE = 0
  2508.   AND X_CONTACT_STRATEGY_EM = 0
  2509.   AND X_IB = 0
  2510.   AND X_CORP = 0
  2511.   AND X_CANADA = 0
  2512.   AND X_TP_BLANK = 0
  2513.   AND X_UNIVERSAL_CONTROL = 0
  2514.   AND X_COLLECTIONS2 = 0
  2515.   AND LIAB_CD = 'IL'
  2516.   AND X_CAMPAIGN = 0
  2517.   AND x_do_not_sell = 0
  2518.   AND x_free_plan = 0
  2519.   AND X_IPFG8 = 0
  2520.   AND x_version_exclude_em = 0
  2521.   AND ELIGIBLE_FLAG = 'Y'
  2522.   AND email_adr IS NOT NULL
  2523.   AND X_EMAIL = 0
  2524.   AND X_EMAIL_UNENGAGE = 0
  2525.   AND EM_ELIGIBLE_BAN_FLAG = 'Y'
  2526.   AND CAMPAIGN_CD IS NOT NULL
  2527.   AND campaign_cd <> '' );
  2528.  
  2529.  
  2530. CREATE TABLE SMS AS
  2531. SELECT * FROM CONNECTION TO TERADATA
  2532. ( SELECT *
  2533.   FROM P5P_CPGN_TBLS.LMK_&PTS_NBR._DRIVER
  2534.   WHERE TARGET_GRP IS NOT NULL AND X_UPGRADE = 0
  2535.    AND X_IB_AT_CC = 0
  2536.    AND X_SEASONAL_SUSPEND = 0
  2537.   AND X_MILITARY_SUSPEND = 0
  2538.   AND X_COLLECTIONS = 0
  2539.   AND X_TRADE = 0
  2540.   AND X_CONTACT_STRATEGY_SMS = 0
  2541.   AND X_IB = 0
  2542.   AND X_MINOR = 0
  2543.   AND X_CORP = 0
  2544.   AND X_CANADA = 0
  2545.   AND X_TP_BLANK = 0
  2546.   AND X_UNIVERSAL_CONTROL = 0
  2547.   AND X_COLLECTIONS2 = 0
  2548. /*and x_spanish = 0*/
  2549.   AND LIAB_CD = 'IL'
  2550.   AND X_CAMPAIGN = 0
  2551.   AND x_do_not_sell = 0
  2552.    AND x_free_plan = 0
  2553.   AND X_IPFG8 = 0
  2554.   AND x_version_exclude_sms = 0
  2555.   AND ELIGIBLE_FLAG = 'Y'
  2556.   AND X_SMS_CAPABLE = 0
  2557.   AND X_SMS_OPT_OUT = 0
  2558.   AND X_SMS_SPRINT_DNC = 0
  2559.   AND X_SMS_BLACKLIST = 0
  2560.   AND X_SMS_STATES = 0
  2561.   AND BAN_MIXED_NEs_FLAG <> 'X'
  2562. /*  and X_SMS_SPANISH = 0*/
  2563.   AND SMS_ELIGIBLE_FLAG = 'Y'
  2564.   AND EM_ELIGIBLE_BAN_FLAG = 'N'
  2565.   AND CAMPAIGN_CD_SMS IS NOT NULL );
  2566.  
  2567.  
  2568.  
  2569. disconnect FROM teradata;
  2570. quit;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!