chenwu/MarketAnalysis-ETL
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
MarketAnalysis-ETL/UNIONALL/DM_TD_EXT_UNIONALL_PACKINFO.sql
chenwu 832c7bcd69 new file: 01 dm_tf_external_sales.sql 
new file:   02 dm_td_external_keycompatitor.sql
	new file:   03 dm_td_external_brand_market.sql
	new file:   04 dm_td_external_calendar.sql
	new file:   05 dm_td_external_exchangerate.sql
	new file:   07 dm_td_external_packinfo.sql
	new file:   08 dm_td_external_corp.sql
	new file:   09 dm_td_external_geo_type.sql
	new file:   11 DM_TD_EXTERNAL_MARKET_NEW.sql
	new file:   12 dm_td_external_org.sql
	new file:   13 external auth.sql
	new file:   14 dm_tf_external_retail_special_bkp.sql
	new file:   AIA/01 dm_aia_pack_property.sql
	new file:   AIA/02 dm_ext_aia_sales.sql
	new file:   AIA/02 dm_ext_aia_sales_bakup_20230327.sql
	new file:   AIA/03 dm_aia_flag.sql
	new file:   AIA/04 dm_aia_provided_flag.sql
	new file:   AIA/06 DM_TD_EXT_AIA_PACK_PROPERTY.sql
	new file:   AIA/07 DM_TD_EXT_AIA_PACK2MARKET.sql
	new file:   AIA/08 DM_TD_EXT_AIA_MARKET_RATIO.sql
	new file:   AIA/09 DM_TD_EXT_AIA_MARKET_BRAND_RATIO.sql
	new file:   AIA/10 DM_TD_EXT_AIA_MARKET_PACK_MAPPING.sql
	new file:   AIA/11 DM_TD_EXT_AIA_TARGET_INST.sql
	new file:   AIA/11 dm_aia_targethp_flag.sql
	new file:   "AIA/z_03 dm_aia_flag_\345\244\207\344\273\275\347\224\250.sql"
	new file:   CHC/01 dm_chc_pack_property.sql
	new file:   CHC/02 DM_TF_EXT_CHC_SALES.sql
	new file:   CHC/03 DM_TD_EXT_CHC_PACK_PROPERTY.sql
	new file:   CHC/04 DM_TD_EXT_CHC_PACK2MARKET.sql
	new file:   CHC/05 DM_TD_EXT_CHC_MARKET_RATIO.sql
	new file:   CHC/06 DM_TD_EXT_CHC_MARKET_BRAND_RATIO.sql
	new file:   CHC/07 DM_TD_EXT_CHC_MARKET_PACK_MAPPING.sql
	new file:   CHPA/01 dwd_ims_atc_hierarchy.sql
	new file:   CHPA/01 dwd_ims_nfc_hierarchy.sql
	new file:   CHPA/01 dwd_ims_td_manufacturer_corp.sql
	new file:   CHPA/01 dwd_ims_td_pack_property.sql
	new file:   CHPA/01 dwd_update.sql
	new file:   CHPA/01_FB_BLOB_TO_DWD.sql
	new file:   CHPA/02 DWS_IMS_TD_GEO.sql
	new file:   CHPA/02 dws_ims_td_atc_cn.sql
	new file:   CHPA/02 dws_ims_td_corp_cn.sql
	new file:   CHPA/02 dws_ims_td_date.sql
	new file:   CHPA/02 dws_ims_td_manu_cn.sql
	new file:   CHPA/02 dws_ims_td_market.sql
	new file:   CHPA/02 dws_ims_td_market_ta.sql
	new file:   CHPA/02 dws_ims_td_nfc_cn.sql
	new file:   CHPA/02 dws_ims_td_prod_cn.sql
	new file:   CHPA/02 tmp_ims_td_prod_tmp.sql
	new file:   CHPA/02 tmp_ims_tf_fact_sales.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_BRAND_RATIO.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_PACK_MAPPING.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_RATIO.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_PACK2MARKET.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_PACK_PROPERTY.sql
	new file:   CHPA/03 DM_TF_EXT_CHPA_SALES.sql
	new file:   CHPA/03 dm_ims_td_calendar.sql
	new file:   CHPA/03 dm_ims_td_geo.sql
	new file:   CHPA/03 dm_ims_td_market_property.sql
	new file:   CHPA/03 dm_ims_td_org.sql
	new file:   CHPA/03 dm_ims_td_org_hvh.sql
	new file:   CHPA/03 dm_ims_td_pack_property.sql
	new file:   CHPA/03 dm_ims_tf_sales.sql
	new file:   CHPA/03 dm_td_chpa_market_definition.sql
	new file:   CHPA/03 dm_td_ims_city_mapping.sql
	new file:   EC/03 ec_load_data.sql
	new file:   EC/04 DM_TD_EXT_EC_PACK_PROPERTY.sql
	new file:   EC/05 DM_TF_EXT_EC_SALES.sql
	new file:   EC/06 DM_TD_EXT_EC_PACK2MARKET.sql
	new file:   EC/07 DM_TD_EXT_EC_MARKET_RATIO.sql
	new file:   EC/08 DM_TD_EXT_EC_MARKET_BRAND_RATIO.sql
	new file:   EC/09 DM_TD_EXT_EC_MARKET_PACK_MAPPING.sql
	new file:   EC/1 (ec)blob_to_dwd.sql
	new file:   EC/2 dwd_inc_gnd_ext_ec_nationnal_pack_union_all.py
	new file:   Merged_Data/Merged_Data_Config_table_bkp.sql
	new file:   Merged_Data/Merged_Data_Config_table_bymonth.sql
	new file:   Merged_Data/dm_tf_exteranl_sales_merged_data_dtp_others_bkp.sql
	new file:   Merged_Data/dm_tf_exteranl_sales_merged_data_dtp_others_bymonth_bkp.sql
	new file:   ORG/DM_TD_EXT_AIA_ORG.sql
	new file:   ORG/DM_TD_EXT_CHC_ORG.sql
	new file:   ORG/DM_TD_EXT_CHPA_ORG.sql
	new file:   ORG/DM_TD_EXT_COUNTY_ORG.sql
	new file:   ORG/DM_TD_EXT_EC_ORG.sql
	new file:   ORG/DM_TD_EXT_RETAIL_ORG.sql
	new file:   ORG/DM_TD_EXT_THC_ORG.sql
	new file:   ORG/DM_TD_EXT_XIEHE_ORG.sql
	new file:   OTHERS/01 dm_td_report_url.sql
	new file:   OTHERS/02 dws_ext_email_warning.sql
	new file:   OTHERS/external_triggered_email.py
	new file:   Retail/01 load_tmp_data.py
	new file:   Retail/02 split_brand_data.py
	new file:   Retail/03 split_pack_data.py
	new file:   Retail/04 map_to_dws_table.py
	new file:   Retail/05 load_dtp_temp_data.py
	new file:   Retail/06 split_dtp_brand_data.py
	new file:   Retail/07 split_dtp_pack_data.py
	new file:   Retail/08 map_to_dtp_dws_table.py
	new file:   Retail/09 dwd_inc_gnd_ext_retail_nataional.py
	new file:   Retail/10 map_to_retail_dm_table.py
	new file:   Retail/11 map_to_overview_dm_table.py
	new file:   Retail/12 dws_tf_external_retail_dtp_special.sql
	new file:   Retail/13 DM_TF_EXT_RETAIL_SALES.sql
	new file:   Retail/14 DM_TF_EXT_RETAIL_DTP_SALES.sql
	new file:   Retail/15 DM_TD_EXT_RETAIL_PACK_PROPERTY.sql
	new file:   Retail/16 DM_TD_EXT_RETAIL_DTP_PACK_PROPERTY.sql
	new file:   Retail/17 DM_TD_EXT_DTP_PACK2MARKET.sql
	new file:   Retail/17 DM_TD_EXT_RETAIL_PACK2MARKET.sql
	new file:   Retail/18 DM_TD_EXT_DTP_MARKET_RATIO.sql
	new file:   Retail/18 DM_TD_EXT_RETAIL_MARKET_RATIO.sql
	new file:   Retail/19 DM_TD_EXT_DTP_MARKET_BRAND_RATIO.sql
	new file:   Retail/19 DM_TD_EXT_RETAIL_MARKET_BRAND_RATIO.sql
	new file:   Retail/20 DM_TD_EXT_DTP_MARKET_PACK_MAPPING.sql
	new file:   Retail/20 DM_TD_EXT_RETAIL_MARKET_PACK_MAPPING.sql
	new file:   "Retail/z1 dwd_inc_gnd_ext_retail_nataional_\344\275\234\345\272\237.py"
	new file:   "Retail/z2 retail_load_data_\344\275\234\345\272\237.sql"
	new file:   "Retail/z3 retail_overview_data_\344\275\234\345\272\237.sql"
	new file:   THC/01 dm_thc_pack_property.sql
	new file:   THC/02 dm_ext_thc_sales.sql
	new file:   THC/02 dm_ext_thc_sales_bakup_20260327.sql
	new file:   THC/03 DM_TF_EXT_THC_MARKET_SALES_CHT.sql
	new file:   THC/04 dm_tf_external_sales_thc.sql
	new file:   THC/05 DM_TD_EXT_THC_PACK_PROPERTY.sql
	new file:   THC/06 DM_TD_EXT_THC_PACK2MARKET.sql
	new file:   THC/07 DM_TD_EXT_THC_MARKET_RATIO.sql
	new file:   THC/08 DM_TD_EXT_THC_MARKET_BRAND_RATIO.sql
	new file:   THC/09 DM_TD_EXT_THC_MARKET_PACK_MAPPING.sql
	new file:   UNIONALL/DM_TD_EXT_UNIONALL_MARKET_PACK_MAPPING.sql
	new file:   UNIONALL/DM_TD_EXT_UNIONALL_PACKINFO.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_MARKET_SALES.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_MARKET_SALES_v1.0.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES_MAPPING.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES_MAPPING_NIAD.sql
	new file:   XIEHE/01 xiehe_blob_to_dwd.py
	new file:   XIEHE/02 dm_xiehe_pack_property.sql
	new file:   XIEHE/03 dm_ext_xiehe_geo.sql
	new file:   XIEHE/04 dm_ext_xiehe_sales.sql
	new file:   XIEHE/05 dm_td_xiehe_core_dept.sql
	new file:   XIEHE/06 DM_TF_EXT_XIEHE_SALES.sql
	new file:   XIEHE/07 DM_TD_EXT_XIEHE_PACK_PROPERTY.sql
	new file:   XIEHE/08 DM_TD_EXT_XIEHE_PACK2MARKET.sql
	new file:   XIEHE/09 DM_TD_EXT_XIEHE_MARKET_RATIO.sql
	new file:   XIEHE/10 DM_TD_EXT_XIEHE_MARKET_BRAND_RATIO.sql
	new file:   XIEHE/11 DM_TD_EXT_XIEHE_MARKET_PACK_MAPPING.sql
	new file:   XIEHE/bkp_01 xiehe_blob2dwd.py
	new file:   XIEHE/bkp_02 dm_ext_xiehe_sales.sql
	new file:   XIEHE/bkp_03 dm_ext_xiehe_pack_property.sql
	new file:   county/01 tmp_ims_county_fact_sales_sum.sql
	new file:   county/02 tmp_imscounty_Result.sql
	new file:   county/03 dm_ims_td_county_geo.sql
	new file:   county/04 dws_ext_county_tf_sales.sql
	new file:   county/05 dm_ext_county_td_pack_property.sql
	new file:   county/06 dm_td_county_pack_region.sql
	new file:   county/07 dm_ext_county_tf_sales_region.sql
	new file:   county/08 DM_TD_EXT_COUNTY_PACK_PROPERTY.sql
	new file:   county/09 DM_TF_EXT_COUNTY_SALES.sql
	new file:   county/10 DM_TD_EXT_COUNTY_PACK2MARKET.sql
	new file:   county/11 DM_TD_EXT_COUNTY_MARKET_RATIO.sql
	new file:   county/12 DM_TD_EXT_COUNTY_MARKET_BRAND_RATIO.sql
	new file:   county/13 DM_TD_EXT_COUNTY_MARKET_PACK_MAPPING.sql
	new file:   for_AIA_Dashboard/01 dm_td_aia_inst_mkt.sql
	new file:   for_AIA_Dashboard/02 dm_td_aia_auth_sales.sql
	new file:   for_AIA_Dashboard/03 dm_td_aia_original_col.sql
	new file:   for_AIA_Dashboard/04 dm_td_aia_nosales_inst.sql
	new file:   for_AIA_Dashboard/05 dm_td_aia_is_eagle_flag.sql
	new file:   for_AIA_Dashboard/06 dm_td_aia_rank.sql
	new file:   for_AIA_Dashboard/07 dm_ext_aia_data_remove_flag.sql
	new file:   for_AIA_Dashboard/07 dm_td_aia_remove_special_ins_bkp.py
	new file:   for_AIA_Dashboard/08 dm_ext_aia_data_quality_flag.sql
	new file:   z 01 dm_tf_external_sales.sql
	new file:   "z 10 dm_td_external_market_pack_mapping_\344\275\234\345\272\237.sql"
	new file:   "z 11 dm_td_external_market_\344\275\234\345\272\237.sql"
	new file:   "\344\270\212\347\272\277\350\204\232\346\234\254.sql"
	new file:   "\346\225\260\346\215\256\351\252\214\350\257\201.sql"
2026-04-27 15:48:38 +08:00

1837 lines
41 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- Databricks notebook source
-- CREATE OR REPLACE TABLE DM.DM_TD_EXT_UNIONALL_PACKINFO (
-- PACK_CODE STRING COMMENT '包装编码',
-- PACK_DESC STRING COMMENT '包装描述',
-- PACK_LCH STRING COMMENT '包装上市时间，示例"Y2024M05"',
-- PROD_CODE STRING COMMENT '产品编码',
-- PROD_DESC STRING COMMENT '产品描述',
-- PROD_DESC_C STRING COMMENT '产品中文描述',
-- CMPS_CODE STRING COMMENT '成分代码',
-- CMPS_DESC STRING COMMENT '成分描述',
-- CMPS_DESC_C STRING COMMENT '成分中文描述',
-- VBP_BATCH STRING COMMENT '带量采购批次号',
-- VBP_IMPLEMENTING_TIME STRING COMMENT '带量采购实施时间',
-- STGH_DESC STRING COMMENT '储存方式描述',
-- BIO_DESC STRING COMMENT '生物制药描述',
-- ETH_OTC_DESC STRING COMMENT '处方/非处方药描述',
-- NRDL_DESC STRING COMMENT '国家医保目录状态描述',
-- NRDL_ENTRY_DATE STRING COMMENT '国家医保目录纳入日期',
-- EDL_DESC STRING COMMENT '基本药物目录状态描述',
-- TCM_DESC STRING COMMENT '中医药分类描述',
-- PAED_DESC STRING COMMENT '儿童用药标志描述',
-- GQCE_DESC STRING COMMENT '绿色/质量/认证/应急标志描述',
-- MANU_CODE STRING COMMENT '生产企业编码',
-- MANU_DESC STRING COMMENT '生产企业名称描述',
-- MANU_DESC_C STRING COMMENT '生产企业名称中文描述',
-- MNFL_CODE STRING COMMENT '跨国/本地企业标志编码',
-- ATC1_CODE STRING COMMENT '解剖学治疗学化学分类1编码',
-- ATC1_DESC STRING COMMENT '解剖学治疗学化学分类1描述',
-- ATC1_DESC_C STRING COMMENT '解剖学治疗学化学分类1中文描述',
-- ATC2_CODE STRING COMMENT '解剖学治疗学化学分类2编码',
-- ATC2_DESC STRING COMMENT '解剖学治疗学化学分类2描述',
-- ATC2_DESC_C STRING COMMENT '解剖学治疗学化学分类2中文描述',
-- ATC3_CODE STRING COMMENT '解剖学治疗学化学分类3编码',
-- ATC3_DESC STRING COMMENT '解剖学治疗学化学分类3描述',
-- ATC3_DESC_C STRING COMMENT '解剖学治疗学化学分类3中文描述',
-- ATC4_CODE STRING COMMENT '解剖学治疗学化学分类4编码',
-- ATC4_DESC STRING COMMENT '解剖学治疗学化学分类4描述',
-- ATC4_DESC_C STRING COMMENT '解剖学治疗学化学分类4中文描述',
-- APP1_CODE STRING COMMENT '审批/申请分类(3个层级)',
-- APP1_DESC STRING COMMENT '',
-- APP1_DESC_C STRING COMMENT '',
-- APP2_CODE STRING COMMENT '',
-- APP2_DESC STRING COMMENT '',
-- APP2_DESC_C STRING COMMENT '',
-- APP3_CODE STRING COMMENT '',
-- APP3_DESC STRING COMMENT '',
-- APP3_DESC_C STRING COMMENT '',
-- GENE_ORIG_DESC STRING COMMENT '原研/仿制/中药药物来源描述',
-- VBP_DESC STRING COMMENT '带量采购状态(VBP-IN/VBP-OUT/Non VBP)描述',
-- CORP_CODE STRING COMMENT '公司编码',
-- GEN_RN INT COMMENT '原研/仿制排名(1-5)',
-- CORP_DESC STRING COMMENT '公司名称描述',
-- CORP_DESC_C STRING COMMENT '公司名称中文描述',
-- PACK_RN INT COMMENT '包装排名(按销售量)',
-- PROD_RN INT COMMENT '产品排名(按销售量)',
-- CMPS_RN INT COMMENT '成分排名(按销售量)',
-- DATA_SOURCE STRING COMMENT '数据来源渠道',
-- ATC STRING COMMENT 'ATC分类代码',
-- NEW_CODE STRING COMMENT '新编码',
-- COMMON_NAME STRING COMMENT '通用名称',
-- PRODUCT_NAME STRING COMMENT '产品名称',
-- MANU_DES_XIEHE STRING COMMENT '协和渠道生产企业名称',
-- PACK_DES_XIEHE STRING COMMENT '协和渠道包装描述',
-- DRUG_DELIVERY_ROUTE STRING COMMENT '给药途径',
-- NFC STRING COMMENT '国家处方集代码',
-- CORP_TYPE STRING COMMENT '企业类型(本地/跨国)',
-- LAUNCHTIME STRING COMMENT '产品上市时间',
-- VBP_BRAND STRING COMMENT '品牌带量采购状态汇总',
-- REPLENISH_FALG STRING COMMENT '后续补充标志',
-- ETL_INSERT_DT TIMESTAMP,
-- ETL_UPDATE_DT TIMESTAMP
-- )
-- USING delta
-- -- LOCATION 'abfss://master@azcdatalakeprd.dfs.core.chinacloudapi.cn/DM/dm_td_ext_unionall_packinfo';
-- -- 上面是生产环境location，下面是测试环境location
-- LOCATION 'abfss://master@retaildlstoragetest.dfs.core.chinacloudapi.cn/DM/dm_td_ext_unionall_packinfo';
-- COMMAND ----------
-- 数据源1 CHPA
CREATE OR REPLACE TEMPORARY VIEW VIEW_CHPA_PACK_INFO
AS
SELECT
PACK_CODE as PACK_CODE,
MAX(PACK_DESC) as PACK_DESC,
MAX(PACK_LCH) as PACK_LCH,
MAX(PROD_CODE) as PROD_CODE,
MAX(PROD_DESC) as PROD_DESC,
MAX(PROD_DESC_C) as PROD_DESC_C,
MAX(CMPS_CODE) as CMPS_CODE,
MAX(CMPS_DESC) as CMPS_DESC,
MAX(CMPS_DESC_C) as CMPS_DESC_C,
NULL as VBP_BATCH,
NULL as VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) as STGH_DESC,
MAX(BIO_DESC) as BIO_DESC,
MAX(ETH_OTC_DESC) as ETH_OTC_DESC,
MAX(NRDL_DESC) as NRDL_DESC,
MAX(NRDL_ENTRY_DATE) as NRDL_ENTRY_DATE,
MAX(EDL_DESC) as EDL_DESC,
MAX(TCM_DESC) as TCM_DESC,
MAX(PAED_DESC) as PAED_DESC,
MAX(GQCE_DESC) as GQCE_DESC,
MAX(MANU_CODE) as MANU_CODE,
MAX(MANU_DESC) as MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) as MNFL_CODE,
MAX(ATC1_CODE) as ATC1_CODE,
MAX(ATC1_DESC) as ATC1_DESC,
MAX(ATC1_DESC_C) as ATC1_DESC_C,
MAX(ATC2_CODE) as ATC2_CODE,
MAX(ATC2_DESC) as ATC2_DESC,
MAX(ATC2_DESC_C) as ATC2_DESC_C,
MAX(ATC3_CODE) as ATC3_CODE,
MAX(ATC3_DESC) as ATC3_DESC,
MAX(ATC3_DESC_C) as ATC3_DESC_C,
MAX(ATC4_CODE) as ATC4_CODE,
MAX(ATC4_DESC) as ATC4_DESC,
MAX(ATC4_DESC_C) as ATC4_DESC_C,
MAX(APP1_CODE) as APP1_CODE,
MAX(APP1_DESC) as APP1_DESC,
MAX(APP1_DESC_C) as APP1_DESC_C,
MAX(APP2_CODE) as APP2_CODE,
MAX(APP2_DESC) as APP2_DESC,
MAX(APP2_DESC_C) as APP2_DESC_C,
MAX(APP3_CODE) as APP3_CODE,
MAX(APP3_DESC) as APP3_DESC,
MAX(APP3_DESC_C) as APP3_DESC_C,
MAX(GENE_ORIG_DESC) as GENE_ORIG_DESC,
CASE
WHEN MAX(VBP_DESC_V) = 'VBP-IN' THEN 'VBP-IN'
WHEN MAX(VBP_DESC_V) LIKE '%VBP%' THEN 'VBP-OUT'
WHEN MAX(VBP_DESC_V) IS NOT NULL THEN 'Non VBP'
ELSE NULL END AS VBP_DESC,
MAX(CORP_CODE) as CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END as GEN_RN,
-- 后面数据都还没有，暂时不搞
NULL as CORP_DESC,
NULL as CORP_DESC_C,
NULL as PACK_RN,
NULL as PROD_RN,
NULL as CMPS_RN,
NULL as DATA_SOURCE,
NULL as ATC,
NULL as NEW_CODE,
NULL as COMMON_NAME,
NULL as PRODUCT_NAME,
NULL as MANU_DES_XIEHE,
NULL as PACK_DES_XIEHE,
NULL as DRUG_DELIVERY_ROUTE,
NULL as NFC,
NULL as CORP_TYPE,
NULL as LAUNCHTIME,
NULL as VBP_BRAND
from DM.DM_TD_EXT_CHPA_PACK_PROPERTY
where NVL(PACK_CODE, '') <> ''
GROUP BY PACK_CODE
-- COMMAND ----------
-- 数据源2 COUNTY
CREATE OR REPLACE TEMPORARY VIEW VIEW_COUNTY_PACK_INFO
AS
SELECT
PACK_CODE AS PACK_CODE,
MAX(PACK_DESC) AS PACK_DESC,
MAX(PACK_LCH) AS PACK_LCH,
MAX(PROD_CODE) AS PROD_CODE,
MAX(PROD_DESC) AS PROD_DESC,
MAX(PROD_DESC_C) AS PROD_DESC_C,
MAX(CMPS_CODE) AS CMPS_CODE,
MAX(CMPS_DESC) AS CMPS_DESC,
MAX(CMPS_DESC_C) AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) AS STGH_DESC,
MAX(BIO_DESC) AS BIO_DESC,
MAX(ETH_OTC_DESC) AS ETH_OTC_DESC,
MAX(NRDL_DESC) AS NRDL_DESC,
MAX(NRDL_ENTRY_DATE) AS NRDL_ENTRY_DATE,
MAX(EDL_DESC) AS EDL_DESC,
MAX(TCM_DESC) AS TCM_DESC,
MAX(PAED_DESC) AS PAED_DESC,
MAX(GQCE_DESC) AS GQCE_DESC,
MAX(MANU_CODE) AS MANU_CODE,
MAX(MANU_DESC) AS MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) AS MNFL_CODE,
MAX(ATC1_CODE) AS ATC1_CODE,
MAX(ATC1_DESC) AS ATC1_DESC,
MAX(ATC1_DESC_C) AS ATC1_DESC_C,
MAX(ATC2_CODE) AS ATC2_CODE,
MAX(ATC2_DESC) AS ATC2_DESC,
MAX(ATC2_DESC_C) AS ATC2_DESC_C,
MAX(ATC3_CODE) AS ATC3_CODE,
MAX(ATC3_DESC) AS ATC3_DESC,
MAX(ATC3_DESC_C) AS ATC3_DESC_C,
MAX(ATC4_CODE) AS ATC4_CODE,
MAX(ATC4_DESC) AS ATC4_DESC,
MAX(ATC4_DESC_C) AS ATC4_DESC_C,
MAX(APP1_CODE) AS APP1_CODE,
MAX(APP1_DESC) AS APP1_DESC,
MAX(APP1_DESC_C) AS APP1_DESC_C,
MAX(APP2_CODE) AS APP2_CODE,
MAX(APP2_DESC) AS APP2_DESC,
MAX(APP2_DESC_C) AS APP2_DESC_C,
MAX(APP3_CODE) AS APP3_CODE,
MAX(APP3_DESC) AS APP3_DESC,
MAX(APP3_DESC_C) AS APP3_DESC_C,
MAX(GENE_ORIG_DESC) AS GENE_ORIG_DESC,
CASE
WHEN MAX(VBP_DESC) = 'VBP-IN' THEN 'VBP-IN'
WHEN MAX(VBP_DESC) LIKE '%VBP%' THEN 'VBP-OUT'
WHEN MAX(VBP_DESC) IS NOT NULL THEN 'Non VBP'
ELSE NULL END AS VBP_DESC,
MAX(CORP_CODE) AS CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END AS GEN_RN,
MAX(CORP_DESC) AS CORP_DESC,
MAX(CORP_DESC_C) AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND
FROM dm.DM_TD_EXT_COUNTY_PACK_PROPERTY b
GROUP BY PACK_CODE
-- COMMAND ----------
-- 数据源3 THC
CREATE OR REPLACE TEMPORARY VIEW VIEW_THC_PACK_INFO
AS
SELECT
PACK_CODE AS PACK_CODE,
MAX(PACK_DESC) AS PACK_DESC,
MAX(PACK_LCH) AS PACK_LCH,
MAX(PROD_CODE) AS PROD_CODE,
MAX(PROD_DESC) AS PROD_DESC,
MAX(PROD_DESC_C) AS PROD_DESC_C,
MAX(CMPS_CODE) AS CMPS_CODE,
MAX(CMPS_DESC) AS CMPS_DESC,
MAX(CMPS_DESC_C) AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) AS STGH_DESC,
MAX(BIO_DESC) AS BIO_DESC,
MAX(ETH_OTC_DESC) AS ETH_OTC_DESC,
MAX(NRDL_DESC) AS NRDL_DESC,
NULL AS NRDL_ENTRY_DATE,
MAX(EDL_DESC) AS EDL_DESC,
MAX(TCM_DESC) AS TCM_DESC,
MAX(PAED_DESC) AS PAED_DESC,
MAX(GQCE_DESC) AS GQCE_DESC,
MAX(MANU_CODE) AS MANU_CODE,
MAX(MANU_DESC) AS MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) AS MNFL_CODE,
MAX(ATC1_CODE) AS ATC1_CODE,
MAX(ATC1_DESC) AS ATC1_DESC,
MAX(ATC1_DESC_C) AS ATC1_DESC_C,
MAX(ATC2_CODE) AS ATC2_CODE,
MAX(ATC2_DESC) AS ATC2_DESC,
MAX(ATC2_DESC_C) AS ATC2_DESC_C,
MAX(ATC3_CODE) AS ATC3_CODE,
MAX(ATC3_DESC) AS ATC3_DESC,
MAX(ATC3_DESC_C) AS ATC3_DESC_C,
MAX(ATC4_CODE) AS ATC4_CODE,
MAX(ATC4_DESC) AS ATC4_DESC,
MAX(ATC4_DESC_C) AS ATC4_DESC_C,
MAX(APP1_CODE) AS APP1_CODE,
MAX(APP1_DESC) AS APP1_DESC,
MAX(APP1_DESC_C) AS APP1_DESC_C,
MAX(APP2_CODE) AS APP2_CODE,
MAX(APP2_DESC) AS APP2_DESC,
MAX(APP2_DESC_C) AS APP2_DESC_C,
MAX(APP3_CODE) AS APP3_CODE,
MAX(APP3_DESC) AS APP3_DESC,
MAX(APP3_DESC_C) AS APP3_DESC_C,
MAX(GENE_ORIG_DESC) AS GENE_ORIG_DESC,
MAX(VBP_DESC) AS VBP_DESC,
MAX(CORP_CODE) AS CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END AS GEN_RN,
MAX(CORP_DESC) AS CORP_DESC,
MAX(CORP_DESC_C) AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND
FROM DM.DM_TD_EXT_THC_PACK_PROPERTY
GROUP BY PACK_CODE
-- COMMAND ----------
-- 数据源4 CHC
CREATE OR REPLACE TEMPORARY VIEW VIEW_CHC_PACK_INFO
AS
SELECT
PACK_CODE AS PACK_CODE,
MAX(PACK_DESC) AS PACK_DESC,
MAX(PACK_LCH) AS PACK_LCH,
MAX(PROD_CODE) AS PROD_CODE,
MAX(PROD_DESC) AS PROD_DESC,
MAX(PROD_DESC_C) AS PROD_DESC_C,
MAX(CMPS_CODE) AS CMPS_CODE,
MAX(CMPS_DESC) AS CMPS_DESC,
MAX(CMPS_DESC_C) AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) AS STGH_DESC,
MAX(BIO_DESC) AS BIO_DESC,
MAX(ETH_OTC_DESC) AS ETH_OTC_DESC,
MAX(NRDL_DESC) AS NRDL_DESC,
NULL AS NRDL_ENTRY_DATE,
MAX(EDL_DESC) AS EDL_DESC,
MAX(TCM_DESC) AS TCM_DESC,
MAX(PAED_DESC) AS PAED_DESC,
MAX(GQCE_DESC) AS GQCE_DESC,
MAX(MANU_CODE) AS MANU_CODE,
MAX(MANU_DESC) AS MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) AS MNFL_CODE,
MAX(ATC1_CODE) AS ATC1_CODE,
MAX(ATC1_DESC) AS ATC1_DESC,
MAX(ATC1_DESC_C) AS ATC1_DESC_C,
MAX(ATC2_CODE) AS ATC2_CODE,
MAX(ATC2_DESC) AS ATC2_DESC,
MAX(ATC2_DESC_C) AS ATC2_DESC_C,
MAX(ATC3_CODE) AS ATC3_CODE,
MAX(ATC3_DESC) AS ATC3_DESC,
MAX(ATC3_DESC_C) AS ATC3_DESC_C,
MAX(ATC4_CODE) AS ATC4_CODE,
MAX(ATC4_DESC) AS ATC4_DESC,
MAX(ATC4_DESC_C) AS ATC4_DESC_C,
MAX(APP1_CODE) AS APP1_CODE,
MAX(APP1_DESC) AS APP1_DESC,
MAX(APP1_DESC_C) AS APP1_DESC_C,
MAX(APP2_CODE) AS APP2_CODE,
MAX(APP2_DESC) AS APP2_DESC,
MAX(APP2_DESC_C) AS APP2_DESC_C,
MAX(APP3_CODE) AS APP3_CODE,
MAX(APP3_DESC) AS APP3_DESC,
MAX(APP3_DESC_C) AS APP3_DESC_C,
MAX(GENE_ORIG_DESC) AS GENE_ORIG_DESC,
MAX(VBP_DESC) AS VBP_DESC,
MAX(CORP_CODE) AS CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END AS GEN_RN,
MAX(CORP_DESC) AS CORP_DESC,
MAX(CORP_DESC_C) AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND
FROM DM.DM_TD_EXT_CHC_PACK_PROPERTY
GROUP BY PACK_CODE
-- COMMAND ----------
-- 数据源5 retail
CREATE OR REPLACE TEMPORARY VIEW VIEW_RETAIL_PACK_INFO
AS
SELECT
PACK_CODE AS PACK_CODE,
MAX(PACK_DESC) AS PACK_DESC,
MAX(PACK_LCH) AS PACK_LCH,
MAX(PROD_CODE) AS PROD_CODE,
MAX(PROD_DESC) AS PROD_DESC,
MAX(PROD_DESC_C) AS PROD_DESC_C,
MAX(CMPS_CODE) AS CMPS_CODE,
MAX(CMPS_DESC) AS CMPS_DESC,
MAX(CMPS_DESC_C) AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) AS STGH_DESC,
MAX(BIO_DESC) AS BIO_DESC,
MAX(ETH_OTC_DESC) AS ETH_OTC_DESC,
MAX(NRDL_DESC) AS NRDL_DESC,
MAX(NRDL_ENTRY_DATE) AS NRDL_ENTRY_DATE,
MAX(EDL_DESC) AS EDL_DESC,
MAX(TCM_DESC) AS TCM_DESC,
MAX(PAED_DESC) AS PAED_DESC,
MAX(GQCE_DESC) AS GQCE_DESC,
MAX(MANU_CODE) AS MANU_CODE,
MAX(MANU_DESC) AS MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) AS MNFL_CODE,
MAX(ATC1_CODE) AS ATC1_CODE,
MAX(ATC1_DESC) AS ATC1_DESC,
MAX(ATC1_DESC_C) AS ATC1_DESC_C,
MAX(ATC2_CODE) AS ATC2_CODE,
MAX(ATC2_DESC) AS ATC2_DESC,
MAX(ATC2_DESC_C) AS ATC2_DESC_C,
MAX(ATC3_CODE) AS ATC3_CODE,
MAX(ATC3_DESC) AS ATC3_DESC,
MAX(ATC3_DESC_C) AS ATC3_DESC_C,
MAX(ATC4_CODE) AS ATC4_CODE,
MAX(ATC4_DESC) AS ATC4_DESC,
MAX(ATC4_DESC_C) AS ATC4_DESC_C,
MAX(APP1_CODE) AS APP1_CODE,
MAX(APP1_DESC) AS APP1_DESC,
MAX(APP1_DESC_C) AS APP1_DESC_C,
MAX(APP2_CODE) AS APP2_CODE,
MAX(APP2_DESC) AS APP2_DESC,
MAX(APP2_DESC_C) AS APP2_DESC_C,
MAX(APP3_CODE) AS APP3_CODE,
MAX(APP3_DESC) AS APP3_DESC,
MAX(APP3_DESC_C) AS APP3_DESC_C,
MAX(GENE_ORIG_DESC) AS GENE_ORIG_DESC,
CASE
WHEN MAX(VBP_DESC) = 'VBP-IN' then 'VBP-IN'
WHEN MAX(VBP_DESC) LIKE '%VBP%' THEN 'VBP-OUT'
WHEN MAX(VBP_DESC) IS NOT NULL THEN 'Non VBP'
ELSE NULL END AS VBP_DESC,
MAX(CORP_CODE) AS CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END GEN_RN,
MAX(CORP_DESC) AS CORP_DESC,
MAX(CORP_DESC_C) AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND
FROM dm.DM_TD_EXT_RETAIL_PACK_PROPERTY
GROUP BY PACK_CODE
UNION
SELECT
PACK_CODE AS PACK_CODE,
MAX(PACK_DESC) AS PACK_DESC,
MAX(PACK_LCH) AS PACK_LCH,
MAX(PROD_CODE) AS PROD_CODE,
MAX(PROD_DESC) AS PROD_DESC,
MAX(PROD_DESC_C) AS PROD_DESC_C,
MAX(CMPS_CODE) AS CMPS_CODE,
MAX(CMPS_DESC) AS CMPS_DESC,
MAX(CMPS_DESC_C) AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) AS STGH_DESC,
MAX(BIO_DESC) AS BIO_DESC,
MAX(ETH_OTC_DESC) AS ETH_OTC_DESC,
MAX(NRDL_DESC) AS NRDL_DESC,
MAX(NRDL_ENTRY_DATE) AS NRDL_ENTRY_DATE,
MAX(EDL_DESC) AS EDL_DESC,
MAX(TCM_DESC) AS TCM_DESC,
MAX(PAED_DESC) AS PAED_DESC,
MAX(GQCE_DESC) AS GQCE_DESC,
MAX(MANU_CODE) AS MANU_CODE,
MAX(MANU_DESC) AS MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) AS MNFL_CODE,
MAX(ATC1_CODE) AS ATC1_CODE,
MAX(ATC1_DESC) AS ATC1_DESC,
MAX(ATC1_DESC_C) AS ATC1_DESC_C,
MAX(ATC2_CODE) AS ATC2_CODE,
MAX(ATC2_DESC) AS ATC2_DESC,
MAX(ATC2_DESC_C) AS ATC2_DESC_C,
MAX(ATC3_CODE) AS ATC3_CODE,
MAX(ATC3_DESC) AS ATC3_DESC,
MAX(ATC3_DESC_C) AS ATC3_DESC_C,
MAX(ATC4_CODE) AS ATC4_CODE,
MAX(ATC4_DESC) AS ATC4_DESC,
MAX(ATC4_DESC_C) AS ATC4_DESC_C,
MAX(APP1_CODE) AS APP1_CODE,
MAX(APP1_DESC) AS APP1_DESC,
MAX(APP1_DESC_C) AS APP1_DESC_C,
MAX(APP2_CODE) AS APP2_CODE,
MAX(APP2_DESC) AS APP2_DESC,
MAX(APP2_DESC_C) AS APP2_DESC_C,
MAX(APP3_CODE) AS APP3_CODE,
MAX(APP3_DESC) AS APP3_DESC,
MAX(APP3_DESC_C) AS APP3_DESC_C,
MAX(GENE_ORIG_DESC) AS GENE_ORIG_DESC,
CASE
WHEN MAX(VBP_DESC) = 'VBP-IN' then 'VBP-IN'
WHEN MAX(VBP_DESC) LIKE '%VBP%' THEN 'VBP-OUT'
WHEN MAX(VBP_DESC) IS NOT NULL THEN 'Non VBP'
ELSE NULL END as VBP_DESC,
MAX(CORP_CODE) AS CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END AS GEN_RN,
MAX(CORP_DESC) AS CORP_DESC,
MAX(CORP_DESC_C) AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND
FROM dm.DM_TD_EXT_RETAIL_DTP_PACK_PROPERTY
GROUP BY PACK_CODE
-- COMMAND ----------
-- 数据源6 EC
CREATE OR REPLACE TEMPORARY VIEW VIEW_EC_PACK_INFO
AS
SELECT
PACK_CODE AS PACK_CODE,
MAX(PACK_DESC) AS PACK_DESC,
MAX(PACK_LCH) AS PACK_LCH,
MAX(PROD_CODE) AS PROD_CODE,
MAX(PROD_DESC) AS PROD_DESC,
MAX(PROD_DESC_C) AS PROD_DESC_C,
MAX(CMPS_CODE) AS CMPS_CODE,
MAX(CMPS_DESC) AS CMPS_DESC,
MAX(CMPS_DESC_C) AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) AS STGH_DESC,
MAX(BIO_DESC) AS BIO_DESC,
MAX(ETH_OTC_DESC) AS ETH_OTC_DESC,
MAX(NRDL_DESC) AS NRDL_DESC,
MAX(NRDL_ENTRY_DATE) AS NRDL_ENTRY_DATE,
MAX(EDL_DESC) AS EDL_DESC,
MAX(TCM_DESC) AS TCM_DESC,
MAX(PAED_DESC) AS PAED_DESC,
MAX(GQCE_DESC) AS GQCE_DESC,
MAX(MANU_CODE) AS MANU_CODE,
MAX(MANU_DESC) AS MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) AS MNFL_CODE,
MAX(ATC1_CODE) AS ATC1_CODE,
MAX(ATC1_DESC) AS ATC1_DESC,
MAX(ATC1_DESC_C) AS ATC1_DESC_C,
MAX(ATC2_CODE) AS ATC2_CODE,
MAX(ATC2_DESC) AS ATC2_DESC,
MAX(ATC2_DESC_C) AS ATC2_DESC_C,
MAX(ATC3_CODE) AS ATC3_CODE,
MAX(ATC3_DESC) AS ATC3_DESC,
MAX(ATC3_DESC_C) AS ATC3_DESC_C,
MAX(ATC4_CODE) AS ATC4_CODE,
MAX(ATC4_DESC) AS ATC4_DESC,
MAX(ATC4_DESC_C) AS ATC4_DESC_C,
MAX(APP1_CODE) AS APP1_CODE,
MAX(APP1_DESC) AS APP1_DESC,
MAX(APP1_DESC_C) AS APP1_DESC_C,
MAX(APP2_CODE) AS APP2_CODE,
MAX(APP2_DESC) AS APP2_DESC,
MAX(APP2_DESC_C) AS APP2_DESC_C,
MAX(APP3_CODE) AS APP3_CODE,
MAX(APP3_DESC) AS APP3_DESC,
MAX(APP3_DESC_C) AS APP3_DESC_C,
MAX(GENE_ORIG_DESC) AS GENE_ORIG_DESC,
CASE
WHEN MAX(VBP_DESC) = 'VBP-IN' then 'VBP-IN'
WHEN MAX(VBP_DESC) LIKE '%VBP%' THEN 'VBP-OUT'
WHEN MAX(VBP_DESC) IS NOT NULL THEN 'Non VBP'
ELSE NULL END AS VBP_DESC,
MAX(CORP_CODE) AS CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END AS GEN_RN,
MAX(CORP_DESC) AS CORP_DESC,
MAX(CORP_DESC_C) AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND
FROM dm.DM_TD_EXT_EC_PACK_PROPERTY
GROUP BY PACK_CODE
-- COMMAND ----------
-- 数据源7 AIA
CREATE OR REPLACE TEMPORARY VIEW VIEW_AIA_PACK_INFO
AS
SELECT
CASE
WHEN PACK_CODE like '%OTHERS%'
OR length(PACK_CODE) >= 12
OR (not PACK_CODE REGEXP '^[0-9]') THEN PACK_CODE
ELSE right(concat('000000000000', PACK_CODE), 12) END AS PACK_CODE,
MAX(PACK_DESC) AS PACK_DESC,
MAX(PACK_LCH) AS PACK_LCH,
MAX(PROD_CODE) AS PROD_CODE,
MAX(PROD_DESC) AS PROD_DESC,
MAX(PROD_DESC_C) AS PROD_DESC_C,
MAX(CMPS_CODE) AS CMPS_CODE,
MAX(CMPS_DESC) AS CMPS_DESC,
MAX(CMPS_DESC_C) AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) AS STGH_DESC,
MAX(BIO_DESC) AS BIO_DESC,
MAX(ETH_OTC_DESC) AS ETH_OTC_DESC,
MAX(NRDL_DESC) AS NRDL_DESC,
MAX(NRDL_ENTRY_DATE) AS NRDL_ENTRY_DATE,
MAX(EDL_DESC) AS EDL_DESC,
MAX(TCM_DESC) AS TCM_DESC,
MAX(PAED_DESC) AS PAED_DESC,
MAX(GQCE_DESC) AS GQCE_DESC,
MAX(MANU_CODE) AS MANU_CODE,
MAX(MANU_DESC) AS MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) AS MNFL_CODE,
MAX(ATC1_CODE) AS ATC1_CODE,
MAX(ATC1_DESC) AS ATC1_DESC,
MAX(ATC1_DESC_C) AS ATC1_DESC_C,
MAX(ATC2_CODE) AS ATC2_CODE,
MAX(ATC2_DESC) AS ATC2_DESC,
MAX(ATC2_DESC_C) AS ATC2_DESC_C,
MAX(ATC3_CODE) AS ATC3_CODE,
MAX(ATC3_DESC) AS ATC3_DESC,
MAX(ATC3_DESC_C) AS ATC3_DESC_C,
MAX(ATC4_CODE) AS ATC4_CODE,
MAX(ATC4_DESC) AS ATC4_DESC,
MAX(ATC4_DESC_C) AS ATC4_DESC_C,
MAX(APP1_CODE) AS APP1_CODE,
MAX(APP1_DESC) AS APP1_DESC,
MAX(APP1_DESC_C) AS APP1_DESC_C,
MAX(APP2_CODE) AS APP2_CODE,
MAX(APP2_DESC) AS APP2_DESC,
MAX(APP2_DESC_C) AS APP2_DESC_C,
MAX(APP3_CODE) AS APP3_CODE,
MAX(APP3_DESC) AS APP3_DESC,
MAX(APP3_DESC_C) AS APP3_DESC_C,
MAX(GENE_ORIG_DESC) AS GENE_ORIG_DESC,
NULL AS VBP_DESC,
MAX(CORP_CODE) AS CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END AS GEN_RN,
MAX(CORP_DESC) AS CORP_DESC,
MAX(CORP_DESC_C) AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND
FROM dm.DM_TD_EXT_AIA_PACK_PROPERTY
GROUP BY PACK_CODE
-- COMMAND ----------
-- 数据源8 XIEHE
CREATE OR REPLACE TEMPORARY VIEW VIEW_XIEHE_PACK_INFO
AS
SELECT
PACK_CODE AS PACK_CODE,
MAX(PACK_DESC) AS PACK_DESC,
MAX(PACK_LCH) AS PACK_LCH,
MAX(PROD_CODE) AS PROD_CODE,
MAX(PROD_DESC) AS PROD_DESC,
MAX(PROD_DESC_C) AS PROD_DESC_C,
MAX(CMPS_CODE) AS CMPS_CODE,
MAX(CMPS_DESC) AS CMPS_DESC,
MAX(CMPS_DESC_C) AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
MAX(STGH_DESC) AS STGH_DESC,
MAX(BIO_DESC) AS BIO_DESC,
MAX(ETH_OTC_DESC) AS ETH_OTC_DESC,
MAX(NRDL_DESC) AS NRDL_DESC,
MAX(NRDL_ENTRY_DATE) AS NRDL_ENTRY_DATE,
MAX(EDL_DESC) AS EDL_DESC,
MAX(TCM_DESC) AS TCM_DESC,
MAX(PAED_DESC) AS PAED_DESC,
MAX(GQCE_DESC) AS GQCE_DESC,
MAX(MANU_CODE) AS MANU_CODE,
MAX(MANU_DESC) AS MANU_DESC,
MAX(MANU_DESC_C) as MANU_DESC_C,
MAX(MNFL_CODE) AS MNFL_CODE,
MAX(ATC1_CODE) AS ATC1_CODE,
MAX(ATC1_DESC) AS ATC1_DESC,
MAX(ATC1_DESC_C) AS ATC1_DESC_C,
MAX(ATC2_CODE) AS ATC2_CODE,
MAX(ATC2_DESC) AS ATC2_DESC,
MAX(ATC2_DESC_C) AS ATC2_DESC_C,
MAX(ATC3_CODE) AS ATC3_CODE,
MAX(ATC3_DESC) AS ATC3_DESC,
MAX(ATC3_DESC_C) AS ATC3_DESC_C,
MAX(ATC4_CODE) AS ATC4_CODE,
MAX(ATC4_DESC) AS ATC4_DESC,
MAX(ATC4_DESC_C) AS ATC4_DESC_C,
MAX(APP1_CODE) AS APP1_CODE,
MAX(APP1_DESC) AS APP1_DESC,
MAX(APP1_DESC_C) AS APP1_DESC_C,
MAX(APP2_CODE) AS APP2_CODE,
MAX(APP2_DESC) AS APP2_DESC,
MAX(APP2_DESC_C) AS APP2_DESC_C,
MAX(APP3_CODE) AS APP3_CODE,
MAX(APP3_DESC) AS APP3_DESC,
MAX(APP3_DESC_C) AS APP3_DESC_C,
MAX(GENE_ORIG_DESC) AS GENE_ORIG_DESC,
NULL AS VBP_DESC,
MAX(CORP_CODE) AS CORP_CODE,
CASE
WHEN MAX(GENE_ORIG_DESC) = 'ORIG' THEN 1
WHEN MAX(GENE_ORIG_DESC) = 'Branded Gen' THEN 2
WHEN MAX(GENE_ORIG_DESC) = 'Unbranded Gen' THEN 3
WHEN MAX(GENE_ORIG_DESC) = 'TCM' THEN 4
ELSE 5 END as GEN_RN,
MAX(CORP_DESC) AS CORP_DESC,
MAX(CORP_DESC_C) AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND
FROM dm.DM_TD_EXT_XIEHE_PACK_PROPERTY
GROUP BY PACK_CODE
-- COMMAND ----------
-- 获取所有的PACK_CODE,以CHPA为主，其他数据源不存在与CHPA一样的，再取。
CREATE OR REPLACE TEMPORARY VIEW VIEW_PACK_INFO_TEMP1
AS
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM VIEW_CHPA_PACK_INFO AS T1
UNION ALL
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM VIEW_COUNTY_PACK_INFO AS T2
WHERE PACK_CODE not in (select PACK_CODE from VIEW_CHPA_PACK_INFO)
UNION ALL
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM VIEW_THC_PACK_INFO AS T3
WHERE PACK_CODE not in (select PACK_CODE from VIEW_CHPA_PACK_INFO)
UNION ALL
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM VIEW_CHC_PACK_INFO AS T4
WHERE PACK_CODE not in (select PACK_CODE from VIEW_CHPA_PACK_INFO)
UNION ALL
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM VIEW_RETAIL_PACK_INFO AS T5
WHERE PACK_CODE not in (select PACK_CODE from VIEW_CHPA_PACK_INFO)
UNION ALL
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM VIEW_EC_PACK_INFO AS T6
WHERE PACK_CODE not in (select PACK_CODE from VIEW_CHPA_PACK_INFO)
UNION ALL
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM VIEW_AIA_PACK_INFO AS T7
WHERE PACK_CODE not in (select PACK_CODE from VIEW_CHPA_PACK_INFO)
UNION ALL
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM VIEW_XIEHE_PACK_INFO AS T8
WHERE PACK_CODE not in (select PACK_CODE from VIEW_CHPA_PACK_INFO)
-- COMMAND ----------
-- 4. 如果PACK_CODE在2~8多个表都有，任选一个（row_number()取第一个）
CREATE OR REPLACE TEMPORARY VIEW VIEW_PACK_INFO_TEMP2
AS
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND
FROM (
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND,
row_number() over (partition by PACK_CODE order by PACK_DESC) as rn
FROM VIEW_PACK_INFO_TEMP1
) t
where rn = 1
-- COMMAND ----------
CREATE OR REPLACE TEMPORARY VIEW VIEW_PACK_INFO_TEMP3
AS
SELECT
T1.PACK_CODE,
T1.PACK_DESC,
T1.PACK_LCH,
T1.PROD_CODE,
T1.PROD_DESC,
T1.PROD_DESC_C,
T1.CMPS_CODE,
T1.CMPS_DESC,
CASE
WHEN UPPER(T1.CMPS_DESC) = 'TRADITIONAL CHINESE MEDICINE' THEN '传统中药'
ELSE T1.CMPS_DESC_C END AS CMPS_DESC_C,
NVL(T2.VBP_BATCH, '') VBP_BATCH,
NVL(T2.VBP_IMPLEMENTING_TIME, '') VBP_IMPLEMENTING_TIME,
T1.STGH_DESC,
T1.BIO_DESC,
T1.ETH_OTC_DESC,
T1.NRDL_DESC,
T1.NRDL_ENTRY_DATE,
T1.EDL_DESC,
T1.TCM_DESC,
T1.PAED_DESC,
T1.GQCE_DESC,
T1.MANU_CODE,
T1.MANU_DESC,
T1.MANU_DESC_C,
T1.MNFL_CODE,
T1.ATC1_CODE,
T1.ATC1_DESC,
T1.ATC1_DESC_C,
T1.ATC2_CODE,
T1.ATC2_DESC,
T1.ATC2_DESC_C,
T1.ATC3_CODE,
T1.ATC3_DESC,
T1.ATC3_DESC_C,
T1.ATC4_CODE,
T1.ATC4_DESC,
T1.ATC4_DESC_C,
T1.APP1_CODE,
T1.APP1_DESC,
T1.APP1_DESC_C,
T1.APP2_CODE,
T1.APP2_DESC,
T1.APP2_DESC_C,
T1.APP3_CODE,
T1.APP3_DESC,
T1.APP3_DESC_C,
T1.GENE_ORIG_DESC,
NVL(T1.VBP_DESC, 'Non VBP') AS VBP_DESC,
T1.CORP_CODE,
T1.GEN_RN,
T1.CORP_DESC,
T1.CORP_DESC_C,
T1.PACK_RN,
T1.PROD_RN,
T1.CMPS_RN,
T1.DATA_SOURCE,
T1.ATC,
T1.NEW_CODE,
T1.COMMON_NAME,
T1.PRODUCT_NAME,
T1.MANU_DES_XIEHE,
T1.PACK_DES_XIEHE,
T1.DRUG_DELIVERY_ROUTE,
T1.NFC,
T1.CORP_TYPE,
T1.LAUNCHTIME,
T1.VBP_BRAND
FROM VIEW_PACK_INFO_TEMP2 AS T1
LEFT JOIN dwd.dwd_gnd_ims_vbp_information AS T2 ON T1.PACK_CODE = T2.PACK_CODE
-- COMMAND ----------
CREATE OR REPLACE TEMPORARY VIEW FINAL
AS
SELECT
PACK_CODE AS PACK_CODE,
NULL AS PACK_DESC,
NULL AS PACK_LCH,
NULL AS PROD_CODE,
NULL AS PROD_DESC,
NULL AS PROD_DESC_C,
NULL AS CMPS_CODE,
NULL AS CMPS_DESC,
NULL AS CMPS_DESC_C,
NULL AS VBP_BATCH,
NULL AS VBP_IMPLEMENTING_TIME,
NULL AS STGH_DESC,
NULL AS BIO_DESC,
NULL AS ETH_OTC_DESC,
NULL AS NRDL_DESC,
NULL AS NRDL_ENTRY_DATE,
NULL AS EDL_DESC,
NULL AS TCM_DESC,
NULL AS PAED_DESC,
NULL AS GQCE_DESC,
NULL AS MANU_CODE,
NULL AS MANU_DESC,
NULL AS MANU_DESC_C,
NULL AS MNFL_CODE,
NULL AS ATC1_CODE,
NULL AS ATC1_DESC,
NULL AS ATC1_DESC_C,
NULL AS ATC2_CODE,
NULL AS ATC2_DESC,
NULL AS ATC2_DESC_C,
NULL AS ATC3_CODE,
NULL AS ATC3_DESC,
NULL AS ATC3_DESC_C,
NULL AS ATC4_CODE,
NULL AS ATC4_DESC,
NULL AS ATC4_DESC_C,
NULL AS APP1_CODE,
NULL AS APP1_DESC,
NULL AS APP1_DESC_C,
NULL AS APP2_CODE,
NULL AS APP2_DESC,
NULL AS APP2_DESC_C,
NULL AS APP3_CODE,
NULL AS APP3_DESC,
NULL AS APP3_DESC_C,
NULL AS GENE_ORIG_DESC,
NULL AS VBP_DESC,
NULL AS CORP_CODE,
NULL AS GEN_RN,
NULL AS CORP_DESC,
NULL AS CORP_DESC_C,
NULL AS PACK_RN,
NULL AS PROD_RN,
NULL AS CMPS_RN,
NULL AS DATA_SOURCE,
NULL AS ATC,
NULL AS NEW_CODE,
NULL AS COMMON_NAME,
NULL AS PRODUCT_NAME,
NULL AS MANU_DES_XIEHE,
NULL AS PACK_DES_XIEHE,
NULL AS DRUG_DELIVERY_ROUTE,
NULL AS NFC,
NULL AS CORP_TYPE,
NULL AS LAUNCHTIME,
NULL AS VBP_BRAND,
1 AS REPLENISH_FALG
FROM
(
SELECT
PACK_CODE
FROM DM.DM_TF_EXT_UNIONALL_SALES
GROUP BY
PACK_CODE
) AS T1
WHERE PACK_CODE NOT IN (SELECT PACK_CODE FROM VIEW_PACK_INFO_TEMP3)
UNION ALL
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
PROD_DESC,
PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CORP_DESC,
CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND,
0 AS REPLENISH_FALG
FROM VIEW_PACK_INFO_TEMP3
-- COMMAND ----------
INSERT OVERWRITE TABLE DM.DM_TD_EXT_UNIONALL_PACKINFO
SELECT
PACK_CODE,
PACK_DESC,
PACK_LCH,
PROD_CODE,
CASE
WHEN PROD_DESC IS NULL THEN 'OTHERS'
ELSE PROD_DESC END AS PROD_DESC,
CASE
WHEN PROD_DESC_C IS NULL THEN 'OTHERS'
ELSE PROD_DESC_C END AS PROD_DESC_C,
CMPS_CODE,
CMPS_DESC,
CMPS_DESC_C,
VBP_BATCH,
VBP_IMPLEMENTING_TIME,
STGH_DESC,
BIO_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_ENTRY_DATE,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
MANU_CODE,
MANU_DESC,
MANU_DESC_C,
MNFL_CODE,
ATC1_CODE,
ATC1_DESC,
ATC1_DESC_C,
ATC2_CODE,
ATC2_DESC,
ATC2_DESC_C,
ATC3_CODE,
ATC3_DESC,
ATC3_DESC_C,
ATC4_CODE,
ATC4_DESC,
ATC4_DESC_C,
APP1_CODE,
APP1_DESC,
APP1_DESC_C,
APP2_CODE,
APP2_DESC,
APP2_DESC_C,
APP3_CODE,
APP3_DESC,
APP3_DESC_C,
GENE_ORIG_DESC,
VBP_DESC,
CORP_CODE,
GEN_RN,
CASE
WHEN CORP_DESC IS NULL THEN 'OTHERS'
ELSE CORP_DESC END AS CORP_DESC,
CASE
WHEN CORP_DESC_C IS NULL THEN 'OTHERS'
ELSE CORP_DESC_C END AS CORP_DESC_C,
PACK_RN,
PROD_RN,
CMPS_RN,
DATA_SOURCE,
ATC,
NEW_CODE,
COMMON_NAME,
PRODUCT_NAME,
MANU_DES_XIEHE,
PACK_DES_XIEHE,
DRUG_DELIVERY_ROUTE,
NFC,
CORP_TYPE,
LAUNCHTIME,
VBP_BRAND,
REPLENISH_FALG,
FROM_UTC_TIMESTAMP(CURRENT_TIMESTAMP(),'UTC+8') AS ETL_INSERT_DT,
FROM_UTC_TIMESTAMP(CURRENT_TIMESTAMP(),'UTC+8') AS ETL_UPDATE_DT
FROM FINAL
Reference in New Issue View Git Blame Copy Permalink