Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 30 Jun 2003 @ 17:29:31 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: 3707 Error Message
 
From:   Fuller, Joe

Here is the DDL for the view creation (told ya it was ugly!). To run the SQL I just copy the SELECT statements and paste it into the utilities (queryman, etc.). It runs fine.

Joe


/*
************************************************************************
 *                      DDL for  view creation
*
 *
************************************************************************
/
REPLACE VIEW BI_DMV.Snapshot_Revenue_By_Bill_Cycle AS
SELECT

--------------------------------------------------------------------
       -- BILL CYCLE COLUMNS, UNIQUELY IDENTIFY WHICH BILLING CYCLE

--------------------------------------------------------------------
        BSH.BILLING_CYCLE_ID                         AS Billing_Cycle_ID
       ,BSH.BILLING_CYCLE_RUN_MONTH                  AS Billing_Cycle_Run_Month
       ,BSH.BILLING_CYCLE_RUN_YEAR                   AS Billing_Cycle_Run_Year

--------------------------------------------------------------------
       -- Various dimension ids

--------------------------------------------------------------------
       ,BSS.SUBSCRIPTION_ID                          AS Dim_Subscriber_ID
       ,SSH.Subscription_Status_Type_Cd              AS Dim_Subscriber_Status_ID
       ,ESCSH.Equipment_Id                           AS Dim_Device_Id
       ,PSPH.Product_Id                              AS Dim_Rate_Plan_Id
       ,MKT2.Dim_Market_Id                           AS Subscriber_Market_ID
       ,BSH.BILLING_STATEMENT_ID                     AS Added_Service_List_ID

---------------------------------------------------------------------
       -- Initial activation info

---------------------------------------------------------------------
       ,(COALESCE(PSH1.Party_ID,-1))                 AS Subscriber_Activate_Dealer_ID
       ,SMKT.Dim_Market_ID                           AS Subscriber_Init_Market_Id
       ,PSH1.Party_Subscription_Start_Dt             AS Subscriber_Activate_Date_ID

--------------------------------------------------------------------
       -- The amounts, due when

--------------------------------------------------------------------
       ,BSH.BILL_STMT_DUE_DT                         AS Bill_Due_Date_ID

--------------------------------------------------------------------
       -- Bill cycle revenue amount

--------------------------------------------------------------------
       ,BSH.BILL_STMT_TOT_NET_REVENUE_AMT            AS Actual_Revenue_Bill_Cycle_Amt

--------------------------------------------------------------------
       -- Bill cycle DATA revenue amounts

--------------------------------------------------------------------
       ,BCS.Data_Rate_Plan_Chrg_Amt
       ,BCS.Data_Added_Services_Chrg_Amt
       ,BCS.Data_Overage_Chrg_Amt
       ,BCS.Data_Roam_Chrg_Amt
       ,BCS.Data_One_Time_Chrg_Amt

--------------------------------------------------------------------
       -- Bill cycle VOICE revenue amounts

--------------------------------------------------------------------
       ,BCS.Voice_Rate_Plan_Chrg_Amt
       ,BCS.Voice_Added_Services_Chrge_Amt
       ,BCS.Voice_Overage_Chrg_Amt
       ,BCS.Voice_Toll_Chrg_Amt
       ,BCS.Voice_Roam_Chrg_Amt
       ,BCS.Voice_One_Time_Chrg_Amt

--------------------------------------------------------------------
       -- Bill cycle OTHER revenue amounts

--------------------------------------------------------------------
       ,BCS.Other_Rate_Plan_Chrg_Amt
       ,BCS.Other_Added_Services_Chrge_Amt
       ,BCS.Other_Overage_Chrg_Amt
       ,BCS.Other_Toll_Chrg_Amt
       ,BCS.Other_Roam_Chrg_Amt
       ,BCS.Other_One_Time_Chrg_Amt

--------------------------------------------------------------------
       -- Discounts and adjustments

--------------------------------------------------------------------
       ,0                                            AS Adjustment_Bill_Cycle_Amt
       ,BSH.BILL_STMT_TOTAL_DISCOUNT_AMT             AS Discount_Bill_Cycle_Amt
       ,0                                            AS Credit_Bill_Cycle_Amt
       ,0                                            AS Normalized_Recurring_Chrg_Amt
       ,USGC.Usage_Charge_Amt                        AS Additional_Chrg_Amt

--------------------------------------------------------------------
       -- The various exclusions

--------------------------------------------------------------------
       ,(COALESCE(MKT2.Sub_Market_Exclude_IN,'N'))   AS Sub_Market_Exclude_IN
       ,(COALESCE(A.Ignore_IND,'N'))                 AS Test_Account_Exclude_IN
       ,(COALESCE(P.Product_Rate_Plan_Exclude_IND,'N'))
                                                     AS Rate_Plan_Exclude_IN
       ,(COALESCE(PSPH.SOURCE_CONFIG_ERROR_IND,'N')) AS Source_Configuration_Error_IN
       ,CASE
           WHEN SSH.Subscription_Status_Start_Dt > CURRENT_DATE AND
                SSH.Subsctn_Status_Activity_Cd = 'A'
                THEN 'Y'
                ELSE 'N'
        END                                          AS Post_Dated_Exclude_IN

----------------------------------------------------------------
           -- If ANY of the above indicators are Y, then set this to Y

----------------------------------------------------------------
        ,CASE
           WHEN MKT2.Sub_Market_Exclude_IN      = 'Y'
             OR A.Ignore_IND                    = 'Y'
             OR PSPH.Source_Config_Error_IND    = 'Y'
             OR P.Product_Rate_Plan_Exclude_IND = 'Y'
             OR Post_Dated_Exclude_IN           = 'Y'
                THEN 'Y'
                ELSE 'N'
        END                                          AS Exclude_IN

--------------------------------------------------------------------
       -- Insert / update dates

--------------------------------------------------------------------
       --,BSH.DW_INSERT_DTTM        (DATE)             AS Record_Load_Date
       --,BSH.DW_UPDATE_DTTM        (DATE)             AS Record_Update_Date

--------------------------------------------------------------------
       -- TABLES

--------------------------------------------------------------------
  FROM BI_DWV.BILLING_STATEMENT_HIST                                 AS BSH

--------------------------------------------------------------------
       -- Have to get the billing cycle info, the BILL_STMT_START_DT is not
       -- populated by the ELT process.

--------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.BILLING_CYCLE AS BC
          ON BC.BILLING_CYCLE_ID        = BSH.BILLING_CYCLE_ID
         AND BC.BILLING_CYCLE_RUN_MONTH = BSH.BILLING_CYCLE_RUN_MONTH
         AND BC.BILLING_CYCLE_RUN_YEAR  = BSH.BILLING_CYCLE_RUN_YEAR

--------------------------------------------------------------------
       -- CREATE A DERIVED TABLE WITH THE CHARGES SUMMARIZED

--------------------------------------------------------------------
       LEFT OUTER JOIN (SELECT  BSCH.BILLING_STATEMENT_ID
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD = 'GG'
                                     AND PGA.PARENT_GROUP_CD = 7
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Data_Rate_Plan_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD = 'GG'
                                     AND PGA.PARENT_GROUP_CD = 1
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Data_Added_Services_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD = 'GG'
                                     AND BSCH.CHARGE_CLASSIFICATION_CD
IN('801','802','803','805','806','807')
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Data_Overage_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD = 'GG'
                                     AND BSCH.CHARGE_CLASSIFICATION_CD
IN('601','602','603','605','606','607')
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Data_Roam_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD = 'GG'
                                     AND BSCH.CHARGE_CLASS_CATEGORY_CD = '100'
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Data_One_Time_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD IS NULL
                                     AND PGA.PARENT_GROUP_CD = 7
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Voice_Rate_Plan_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD IS NULL
                                     AND PGA.PARENT_GROUP_CD = 1
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Voice_Added_Services_Chrge_Amt --
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD IS NULL
                                     AND BSCH.CHARGE_CLASSIFICATION_CD
IN('801','802','803','805','806','807')
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Voice_Overage_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD IS NULL
                                     AND BSCH.CHARGE_CLASSIFICATION_CD
IN('104','204','304','404','504','604''704','804')
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Voice_Toll_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD IS NULL
                                     AND BSCH.CHARGE_CLASSIFICATION_CD
IN('601','602','603','605','606','607')
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Voice_Roam_Chrg_Amt
--
                          ,SUM(CASE WHEN P.PRODUCT_TYPE_CD IS NULL
                                     AND BSCH.CHARGE_CLASS_CATEGORY_CD = '100'
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Voice_One_Time_Chrg_Amt
--
                          ,SUM(CASE WHEN (P.PRODUCT_TYPE_CD = 'AG'
                                          OR P.SOURCE_PRODUCT_ID = 'SMSPLUS')
                                     AND PGA.PARENT_GROUP_CD = 7
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Other_Rate_Plan_Chrg_Amt
--
                          ,SUM(CASE WHEN (P.PRODUCT_TYPE_CD = 'AG'
                                          OR P.SOURCE_PRODUCT_ID = 'SMSPLUS')
                                     AND PGA.PARENT_GROUP_CD = 1
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Other_Added_Services_Chrge_Amt --
                          ,SUM(CASE WHEN (P.PRODUCT_TYPE_CD = 'AG'
                                          OR P.SOURCE_PRODUCT_ID = 'SMSPLUS')
                                     AND BSCH.CHARGE_CLASSIFICATION_CD
IN('801','802','803','805','806','807')
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Other_Overage_Chrg_Amt
--
                          ,SUM(CASE WHEN (P.PRODUCT_TYPE_CD = 'AG'
                                          OR P.SOURCE_PRODUCT_ID = 'SMSPLUS')
                                     AND BSCH.CHARGE_CLASSIFICATION_CD
IN('104','204','304','404','504','604''704','804')
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Other_Toll_Chrg_Amt
--
                          ,SUM(CASE WHEN (P.PRODUCT_TYPE_CD = 'AG'
                                          OR P.SOURCE_PRODUCT_ID = 'SMSPLUS')
                                     AND BSCH.CHARGE_CLASSIFICATION_CD
IN('601','602','603','605','606','607')
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Other_Roam_Chrg_Amt
--
                          ,SUM(CASE WHEN (P.PRODUCT_TYPE_CD = 'AG'
                                          OR P.SOURCE_PRODUCT_ID = 'SMSPLUS')
                                     AND BSCH.CHARGE_CLASS_CATEGORY_CD = '100'
                                         THEN BSCH.BILL_STATEMENT_CHARGE_AMT
                                         ELSE 0
                               END)     AS Other_One_Time_Chrg_Amt
--

-------------------------------------------------
                          -- TABLES

-------------------------------------------------
                    FROM  BI_DWV.Billing_Statement_Chg_Hist   AS BSCH

-------------------------------------------------
                          -- NEED THE PRODUCT

-------------------------------------------------
                          LEFT OUTER JOIN BI_DWV.PRODUCT           AS P
                             ON P.PRODUCT_ID = BSCH.PRODUCT_ID

-------------------------------------------------
                          -- NEED THE PRODUCT_GROUP_ASSOCIATION

-------------------------------------------------
                          LEFT OUTER JOIN BI_DWV.PRODUCT_GROUP_ASSOCIATION AS PGA
                             ON PGA.PRODUCT_ID = P.PRODUCT_ID

-------------------------------------------------
                          -- GROUP BY

-------------------------------------------------
                          GROUP BY BSCH.BILLING_STATEMENT_ID) AS BCS
          ON BCS.BILLING_STATEMENT_ID = BSH.BILLING_STATEMENT_ID

--------------------------------------------------------------------
       -- The subscriber(s) from this bill statement.  This fact is at the
       -- subscriber level.

--------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.BILLING_STATEMENT_SUBSCRIPTION AS BSS
          ON BSS.BILLING_STATEMENT_ID = BSH.BILLING_STATEMENT_ID

--------------------------------------------------------------------
       -- Join to the SUBSCRIPTION table

--------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.SUBSCRIPTION AS S
          ON S.SUBSCRIPTION_ID = BSS.SUBSCRIPTION_ID

--------------------------------------------------------------------
       -- Get the initial activation market

--------------------------------------------------------------------
       LEFT OUTER JOIN BI_DMV.Market_NPANXX_SUPPL AS SMKT
         ON SMKT.Sub_Market_Desc = COALESCE(S.Activation_Loc_SubMarket_Cd,'UNKNOWN')

--------------------------------------------------------------------
       -- Join to the ACCT table

--------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.ACCT AS A
          ON A.ACCOUNT_ID = BSH.ACCOUNT_ID

--------------------------------------------------------------------
       -- Get the usage charges for this billing statement from the
       -- ACCS_METH_PROD_ITEM_CHG_HIST table.

--------------------------------------------------------------------
       LEFT OUTER JOIN (SELECT AMPICH.ACCESS_METHOD_ID,
                               AMPICH.BILLING_CYCLE_ID,
                               AMPICH.BILLING_CYCLE_RUN_MONTH,
                               AMPICH.BILLING_CYCLE_RUN_YEAR,

SUM((COALESCE(AMPICH.USAGE_CHARGE_AMT,0))) AS USAGE_CHARGE_AMT
                          FROM BI_DWV.ACCS_METH_PROD_ITEM_CHG_HIST AS AMPICH
                         GROUP BY 1,2,3,4) AS USGC
         ON USGC.ACCESS_METHOD_ID        = BSH.ACCESS_METHOD_ID
        AND USGC.BILLING_CYCLE_ID        = BSH.BILLING_CYCLE_ID
        AND USGC.BILLING_CYCLE_RUN_MONTH = BSH.BILLING_CYCLE_RUN_MONTH
        AND USGC.BILLING_CYCLE_RUN_YEAR  = BSH.BILLING_CYCLE_RUN_YEAR

--------------------------------------------------------------------
       -- Join to the SUBSCRIPTION_STATUS_HIST table

--------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.SUBSCRIPTION_STATUS_HIST  AS SSH
          ON S.SUBSCRIPTION_ID = BSS.SUBSCRIPTION_ID
         AND BC.BILLING_CYCLE_START_DT BETWEEN
             SSH.Subscription_Status_Start_Dt AND
             COALESCE(SSH.Subscription_Status_End_Dt,CURRENT_DATE)

--------------------------------------------------------------------
       -- get the current SIM card(s) from within the given time period

--------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.Equip_SIM_Card_Substcn_Hist AS ESCSH
         ON ESCSH.Subscription_Id = S.Subscription_Id
        AND BC.BILLING_CYCLE_START_DT BETWEEN
            ESCSH.Equip_SIM_Sub_Start_Dt  (DATE)     AND
            COALESCE(CAST(ESCSH.Equip_SIM_Sub_End_Dt AS DATE)
,CURRENT_DATE)

---------------------------------------------------------------------
       -- Get the rate plan for this account

---------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.Product_Subscription_Hist AS PSPH
         ON PSPH.Subscription_ID = S.Subscription_ID
        AND BC.BILLING_CYCLE_START_DT BETWEEN
            PSPH.Product_Sub_Start_Dt AND
            COALESCE(PSPH.Product_Sub_End_Dt, CURRENT_DATE)
            LEFT OUTER JOIN BI_DWV.Product AS P
              ON P.Product_ID = PSPH.Product_ID
            LEFT OUTER JOIN BI_DWV.Product_Group_Association AS PGA
              ON PGA.Product_ID = PSPH.Product_ID
             AND PGA.Parent_Group_Cd = 7  -- Rate plan product type

-------------------------------------------------------------------
       -- Now, get the current market by joining the geography id of the
       -- submarket to the coverage_area_sub_mkt_geo_id of the
       -- COVERAGE_AREA table.

-------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.Geography_Subscription_Hist AS GSH3
         ON GSH3.Subscription_ID = S.Subscription_ID
       LEFT OUTER JOIN BI_DMV.Market_NPANXX_SUPPL AS MKT2
         ON MKT2.Sub_Market_ID = GSH3.Geography_ID

----------------------------------------------------------------------
       -- Get the initial activation dealer

----------------------------------------------------------------------
       LEFT OUTER JOIN BI_DWV.Party_Subscription_Hist         AS PSH1
         ON PSH1.Subscription_Id = S.Subscription_Id
             AND PSH1.Party_Subscription_Role_Cd = 'INITDEALER'
;

/*
************************************************************************
*
   *                  END OF SCRIPT

************************************************************************
*/

Best Regards,

Joe Fuller
Sr Data Warehouse Consultant, DBA
Teradata Professional Services

Advanced Teradata Certified Professional
Teradata Certified Designer



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023