-- Databricks notebook source
-- CREATE OR REPLACE TABLE DM.DM_TD_EXT_EC_PACK_PROPERTY
-- (
--   MARKET_PACK_KEY STRING,
--   PACK_CODE STRING,
--   PACK_DESC STRING,
--   STGH_DESC STRING,
--   PACK_LCH STRING,
--   FAMILY_CODE STRING,
--   FAMILY_NAME STRING,
--   PROD_CODE STRING,
--   PROD_DESC STRING,
--   PROD_DESC_C STRING,
--   CMPS_CODE STRING,
--   CMPS_DESC STRING,
--   CMPS_DESC_C STRING,
--   ATC1_CODE STRING,
--   ATC2_CODE STRING,
--   ATC3_CODE STRING,
--   ATC4_CODE STRING,
--   APP1_CODE STRING,
--   APP2_CODE STRING,
--   APP3_CODE STRING,
--   BIO_DESC STRING,
--   GENE_ORIG_DESC STRING,
--   ETH_OTC_DESC STRING,
--   NRDL_DESC STRING,
--   NRDL_ENTRY_DATE STRING,
--   EDL_DESC STRING,
--   TCM_DESC STRING,
--   PAED_DESC STRING,
--   GQCE_DESC STRING,
--   VBP_DESC_V STRING,
--   VBP_DESC STRING,
--   MANU_CODE STRING,
--   MANU_DESC STRING,
--   MANU_DESC_C STRING,
--   MNFL_CODE STRING,
--   MNFL_DESC STRING,
--   CORP_CODE STRING,
--   CORP_DESC STRING,
--   CORP_DESC_C STRING,
--   BRANDTYPE STRING,
--   MARKET STRING,
--   KEY_COMPETITOR STRING,
--   IS_AZ STRING,
--   AZ_MAIN STRING,
--   AZ_RELATED STRING,
--   ATC1_DESC STRING,
--   ATC1_DESC_C STRING,
--   ATC2_DESC STRING,
--   ATC2_DESC_C STRING,
--   ATC3_DESC STRING,
--   ATC3_DESC_C STRING,
--   ATC4_DESC STRING,
--   ATC4_DESC_C STRING,
--   APP1_DESC STRING,
--   APP1_DESC_C STRING,
--   APP2_DESC STRING,
--   APP2_DESC_C STRING,
--   APP3_DESC STRING,
--   APP3_DESC_C STRING,
--   CLASS STRING,
--   MARKET_RATIO STRING,
--   COUNTINGUNIT STRING,
--   VBP_BRAND STRING,
--   REPLENISH_FALG STRING,
--   ETL_INSERT_DT TIMESTAMP,
--   ETL_UPDATE_DT TIMESTAMP
-- )
-- USING delta
-- -- LOCATION 'abfss://master@azcdatalakeprd.dfs.core.chinacloudapi.cn/DM/dm_td_ext_ec_pack_property';
-- -- 上面是生产环境location，下面是测试环境location
-- LOCATION 'abfss://master@retaildlstoragetest.dfs.core.chinacloudapi.cn/DM/dm_td_ext_ec_pack_property';

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

INSERT OVERWRITE TABLE DM.DM_TD_EXT_EC_PACK_PROPERTY
SELECT
  MARKET_PACK_KEY,
  iqvia_pack_code AS PACK_CODE,
  pack_des AS PACK_DESC,
  stgh_des AS STGH_DESC,
  pack_lch AS PACK_LCH,
  Family_Code AS FAMILY_CODE,
  Family_Name AS FAMILY_NAME,
  prod_cod AS PROD_CODE,
  prod_des AS PROD_DESC,
  prod_des_c AS PROD_DESC_C,
  cmps_cod AS CMPS_CODE,
  cmps_des AS CMPS_DESC,
  cmps_des_c AS CMPS_DESC_C,
  atc1_cod AS ATC1_CODE,
  atc2_cod AS ATC2_CODE,
  atc3_cod AS ATC3_CODE,
  atc4_cod AS ATC4_CODE,
  app1_cod AS APP1_CODE,
  app2_cod AS APP2_CODE,
  app3_cod AS APP3_CODE,
  bio_desc AS BIO_DESC,
  gene_orig_desc AS GENE_ORIG_DESC,
  eth_otc_desc AS ETH_OTC_DESC,
  nrdl_desc AS NRDL_DESC,
  NRDL_ENTRY_DATE,
  edl_desc AS EDL_DESC,
  tcm_desc AS TCM_DESC,
  paed_desc AS PAED_DESC,
  gqce_desc AS GQCE_DESC,
  NULL AS VBP_DESC_V,
  vbp_desc AS VBP_DESC,
  manu_cod AS MANU_CODE,
  manu_des AS MANU_DESC,
  manu_des_c AS MANU_DESC_C,
  mnfl_cod AS MNFL_CODE,
  mnfl_des AS MNFL_DESC,
  corp_cod AS CORP_CODE,
  corp_des AS CORP_DESC,
  CORP_DES_C AS CORP_DESC_C,
  BrandType AS BRANDTYPE,
  market AS MARKET,
  KEY_COMPETITOR,
  is_az AS IS_AZ,
  AZ_MAIN,
  AZ_Related AS AZ_RELATED,
  atc1_des AS ATC1_DESC,
  atc1_des_c AS ATC1_DESC_C,
  atc2_des AS ATC2_DESC,
  atc2_des_c AS ATC2_DESC_C,
  atc3_des AS ATC3_DESC,
  atc3_des_c AS ATC3_DESC_C,
  atc4_des AS ATC4_DESC,
  atc4_des_c AS ATC4_DESC_C,
  app1_des AS APP1_DESC,
  app1_des_c AS APP1_DESC_C,
  app2_des AS APP2_DESC,
  app2_des_c AS APP2_DESC_C,
  app3_des AS APP3_DESC,
  app3_des_c AS APP3_DESC_C,
  class AS CLASS,
  extend_market_ratio AS MARKET_RATIO,
  counting_unit AS COUNTINGUNIT,
  NULL AS VBP_BRAND,
  NULL AS 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 DM.DM_ZK_EC_PACK_PROPERTY;