 |
 |
Archives of the TeradataForum
Message Posted: Mon, 30 Jun 2003 @ 17:29:31 GMT
| 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
| |
