-- Databricks notebook source

-------使用新的DIM表，生成PackProfile，保证后续的逻辑
-- 20260119 依赖前面 dwd.dwd_ims_nfc_hierarchy、dwd.dwd_ims_atc_hierarchy、dwd.dwd_ims_td_manufacturer_corp
-- 20260119 依赖上面补0 dwd.dwd_gnd_ims_tblbrandtype

insert overwrite table dwd.dwd_ims_td_pack_property 
select DISTINCT
if( PACK.Pack_Code REGEXP '^[0-9]',right(concat('000000000000',PACK.Pack_Code),12),PACK.Pack_Code) as PACK_COD, --RIGHT(concat('0000000',PACK.Pack_Code),7) 
PACK.Pack_Description AS PACK_DES,
PACK.STRENGTH AS STGH_DES,
concat('Y',LEFT(PACK.LAUNCHTIME,4),'M',RIGHT(PACK.LAUNCHTIME,2)) AS PACK_LCH,

RIGHT(concat('000000000',PROD.Product_Code),9) AS PROD_COD, --RIGHT(concat('00000',PROD.Product_Code),5)
RIGHT(concat('000000',MOLE.MoleCompCode),6) AS CMPS_COD,
MOLE.MoleCompDesc AS CMPS_DES,

ATCH.ATC1_CODE AS ATC1_COD,
ATCH.ATC2_CODE AS ATC2_COD,
ATCH.ATC3_CODE AS ATC3_COD,
ATCH.ATC4_CODE AS ATC4_COD,

NFCH.NFC1_CODE AS APP1_COD,
NFCH.NFC2_CODE AS APP2_COD,
NFCH.NFC3_CODE AS APP3_COD,

MOLE.BIO AS BIO_DESC, 
MOLE.Gene_Orig AS GENE_ORIG_DESC,
MOLE.Rx_Flag AS ETH_OTC_DESC,
MOLE.NRDL AS NRDL_DESC,
MOLE.NRDL_Entry_Date,
MOLE.EDL AS EDL_DESC,
MOLE.TCMEX AS TCM_DESC,
MOLE.PAED AS PAED_DESC,
MOLE.GQCE AS GQCE_DESC,
MOLE.VBP AS VBP_DESC,
MANU.Manufacturer_Abbr AS MANU_COD,
MANU.Manufacturer_Name AS MANU_DES,
MANUT.ManufacturerType_CODE AS MNFL_COD,
MANUT.ManufacturerType_Name AS MNFL_DES,
MANU.CORP_ABBR AS CORP_COD,
MANU.CORP_DES AS CORP_DES,
BRANDTYPE.Brand_Type AS BrandType
from dwd.dwd_ims_td_pack PACK
LEFT JOIN dwd.dwd_ims_td_product PROD ON PACK.Product_ID = PROD.Product_ID  
LEFT JOIN dwd.dwd_ims_td_new_form_class NFC ON PACK.NewFormClass_ID = NFC.NewFormClass_ID 
LEFT JOIN dwd.dwd_ims_nfc_hierarchy NFCH ON NFC.NewFormClass_Code = NFCH.NFC3_CODE
LEFT JOIN dwd.dwd_ims_td_therapeutic_class ATC ON PACK.Therapeutic_ID = ATC.Therapeutic_ID 
LEFT JOIN dwd.dwd_ims_atc_hierarchy ATCH ON ATC.Therapeutic_Code = ATCH.ATC4_CODE
LEFT JOIN dwd.dwd_ims_td_pack_additional_attribute MOLE ON PACK.Pack_ID = MOLE.Pack_ID
LEFT JOIN dwd.dwd_ims_td_manufacturer_corp MANU ON  PROD.Manufacturer_ID = MANU.Manufacturer_ID 
LEFT JOIN dwd.dwd_ims_td_manufacturertype MANUT ON  MANU.ManufacturerType_ID = MANUT.ManufacturerType_ID 
LEFT JOIN dwd.dwd_gnd_ims_tblbrandtype BRANDTYPE ON PACK.Pack_Code = BRANDTYPE.PACK_COD
;

update dwd.dwd_ims_td_pack_property set CORP_COD ='' where CORP_COD is null ;
update dwd.dwd_ims_td_pack_property set STGH_DES ='' where STGH_DES is null ;

-- COMMAND ----------

-- 20260119 修改 dwd.dwd_ims_td_pack_property 我感觉可以拉到前面去
-- 要调研下，为什么前面没有写，而在后面写。
--Update AZ BRANDS SCOPE---MANU的页面显示
---by Product&Pack add in 'Fluimucil, Broncho-vaxom, Ai Rui Zhuo', by AC 202103
update dwd.dwd_ims_td_pack_property
set CORP_COD = 'A5Z', CORP_DES = 'ASTRAZENECA GROUP' 
where PROD_COD in(
	select 
		right(concat('0000000000',PROD_COD),9) as PROD_COD --right(concat('000000',PROD_COD),5)
	from dwd.dwd_gnd_tbl_corp_change where corp_cod='A5Z')
or PACK_COD in(
	select 
		if( PACK_COD REGEXP '^[0-9]',right(concat('000000000000',PACK_COD),12),PACK_COD) as PACK_COD --right(concat('000000',PACK_COD),7)
	from dwd.dwd_gnd_tbl_corp_change where corp_cod='A5Z')
-- where PROD_COD in ('97336','73148') OR PACK_COD = '0795918'
;
---by Product&Pack remove out tblAZScope.xlsx', by AC 202103
update dwd.dwd_ims_td_pack_property
set CORP_COD = 'A5ZD', CORP_DES = 'AZDealed',MANU_COD = 'A5ZD' ,MANU_DES = 'AZDealed'
where PROD_COD in(
	select 
	right(concat('0000000000',PROD_COD),9) as PROD_COD 
	from dwd.dwd_gnd_tbl_corp_change where corp_cod='A5ZD')
or PACK_COD in(
	select 
		if( PACK_COD REGEXP '^[0-9]',right(concat('000000000000',PACK_COD),12),PACK_COD) as PACK_COD
	from dwd.dwd_gnd_tbl_corp_change where corp_cod='A5ZD')
-- where PROD_COD in ('02267','14691','58423','12754','06482','97266','14164')
;