with ads_inventory_now as(
select
"在库" as inventory_type,
snpsht_date ,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
case when warhs_lgort_name like "%线边%" then "在制"
else "在库" end warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
is_oem_flag,
is_rd_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
case when mat_products_1_category_name in ("电池片") and cell_width_code is not null then cell_width_code
when mat_products_1_category_name in ("电池片") and cell_width_code is null then SUBSTRING (cell_size_code, LOCATE('*',cell_size_code)+1,3)
when mat_products_1_category_name in ("硅片") then siliw_size_simplify_code
else "" end cell_size_code,
siliw_size_code,
cell_size_code as cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
case when '${单位}' = 'PCS' then full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*full_pc_qty/1000000
else full_pc_qty end full_num,
full_amt,
material_cost_amt,
case when '${单位}' = 'PCS' then last_year_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*last_year_full_pc_qty/1000000
else last_year_full_pc_qty end last_year_full_pc_qty,
last_year_full_amt,
last_year_dead_amt,
last_year_inventory_turnover_days_qty,
last_year_material_cost_amt,
case when '${单位}' = 'PCS' then last_half_year_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*last_half_year_full_pc_qty/1000000
else last_half_year_full_pc_qty end last_half_year_full_pc_qty,
last_half_year_full_amt,
last_half_year_dead_amt,
last_half_year_inventory_turnover_days_qty,
last_half_year_material_cost_amt,
case when '${单位}' = 'PCS' then before_last_m_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*before_last_m_full_pc_qty/1000000
else before_last_m_full_pc_qty end before_last_m_full_pc_qty,
before_last_m_full_amt,
before_last_m_dead_amt,
before_last_m_inventory_turnover_days_qty,
before_last_m_material_cost_amt,
case when '${单位}' = 'PCS' then last_m_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*last_m_full_pc_qty/1000000
else last_m_full_pc_qty end last_m_full_pc_qty,
last_m_full_amt,
last_m_dead_amt,
last_m_inventory_turnover_days_qty,
last_m_material_cost_amt,
case when '${单位}' = 'PCS' then this_m_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*this_m_full_pc_qty/1000000
else this_m_full_pc_qty end this_m_full_pc_qty,
this_m_full_amt,
this_m_dead_amt,
this_m_inventory_turnover_days_qty,
this_m_material_cost_amt,
case when '${单位}' = 'PCS' then last_7_d_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*last_7_d_full_pc_qty/1000000
else last_7_d_full_pc_qty end last_7_d_full_pc_qty,
last_7_d_full_amt,
last_7_d_dead_amt,
last_7_d_inventory_turnover_days_qty,
last_7_d_material_cost_amt,
case when '${单位}' = 'PCS' then last_2_w_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*last_2_w_full_pc_qty/1000000
else last_2_w_full_pc_qty end last_2_w_full_pc_qty,
last_2_w_full_amt,
last_2_w_dead_amt,
last_2_w_inventory_turnover_days_qty,
last_2_w_material_cost_amt,
case when '${单位}' = 'PCS' then last_3_w_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*last_3_w_full_pc_qty/1000000
else last_3_w_full_pc_qty end last_3_w_full_pc_qty,
last_3_w_full_amt,
last_3_w_dead_amt,
last_3_w_inventory_turnover_days_qty,
last_3_w_material_cost_amt,
case when '${单位}' = 'PCS' then last_4_w_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(cell_power_amt,0)*last_4_w_full_pc_qty/1000000
else last_4_w_full_pc_qty end last_4_w_full_pc_qty,
last_4_w_full_amt,
last_4_w_dead_amt,
last_4_w_inventory_turnover_days_qty,
last_4_w_material_cost_amt
from
dws_full_inventory_rgalfudy
where
snpsht_date between date_add(CURDATE(),interval -6 day) and CURDATE()
and mat_products_1_category_name in ("组件成品","电池片","硅片")
union all
select
"在途至客户" as inventory_type,
dt,
mat_code,
long_desc_txt,
"",
faty_code,
warhs_lgort_code,
warhs_lgort_name,
"在途至客户" warhs_lgort_name_2,
mat_products_1_category_name,
"主流",
is_regular_flag,
is_oem_flag,
is_rd_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_qty_txt,
case when mat_products_1_category_name in ("电池片") and cell_width_code is not null then cell_width_code
when mat_products_1_category_name in ("电池片") and cell_width_code is null then SUBSTRING (cell_size_code, LOCATE('*',cell_size_code)+1,3)
else "" end cell_size_code,
cell_size_code as cell_size_code_raw,
"在途至客户",
0,
module_levl_code,
0,
case when '${单位}' = 'PCS' then in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then ifnull(in_transit_inventory_watt_qty,0)/1000000
else in_transit_inventory_pc_qty/10000 end full_num,
in_transit_inventory_amt,
0,
case when '${单位}' = 'PCS' then last_year_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_year_in_transit_inventory_watt_qty/1000000
else last_year_in_transit_inventory_pc_qty/10000 end last_year_in_transit_inventory_pc_qty,
last_year_in_transit_inventory_amt,
0 ,
0,
0,
case when '${单位}' = 'PCS' then last_half_year_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_half_year_in_transit_inventory_watt_qty/1000000
else last_half_year_in_transit_inventory_pc_qty/10000 end last_half_year_in_transit_inventory_pc_qty,
last_half_year_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then before_last_m_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then before_last_m_in_transit_inventory_watt_qty/1000000
else before_last_m_in_transit_inventory_pc_qty/10000 end before_last_m_in_transit_inventory_pc_qty,
before_last_m_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_m_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_m_in_transit_inventory_watt_qty/1000000
else last_m_in_transit_inventory_pc_qty/10000 end last_m_in_transit_inventory_pc_qty,
last_m_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then this_m_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then this_m_in_transit_inventory_watt_qty/1000000
else this_m_in_transit_inventory_pc_qty/10000 end this_m_in_transit_inventory_pc_qty,
this_m_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_7_d_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_7_d_in_transit_inventory_watt_qty/1000000
else last_7_d_in_transit_inventory_pc_qty/10000 end last_7_d_in_transit_inventory_pc_qty,
last_7_d_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_2_w_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_2_w_in_transit_inventory_watt_qty/1000000
else last_2_w_in_transit_inventory_pc_qty/10000 end last_2_w_in_transit_inventory_pc_qty,
last_2_w_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_3_w_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_3_w_in_transit_inventory_watt_qty/1000000
else last_3_w_in_transit_inventory_pc_qty/10000 end last_3_w_in_transit_inventory_pc_qty,
last_3_w_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_4_w_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_4_w_in_transit_inventory_watt_qty/1000000
else last_4_w_in_transit_inventory_pc_qty/10000 end last_4_w_in_transit_inventory_pc_qty,
last_4_w_in_transit_inventory_amt,
0,
0,
0
from
aiko_dm_isc.dwd_in_transit_inventory_rgalfudy
where dt between date_add(CURDATE(),interval -6 day) and CURDATE()
and mat_products_1_category_name = "电池片"
and case when pro_products_1_category_name = "ABC" and pro_products_2_category_name = "备品备件及其他" then 1
else 0 end = 0
union all
select
"在制" as inventory_type,
dt,
mat_code,
"在制无描述",
"",
faty_code,
"在制无库位",
"在制无库位",
"在制无库位" warhs_lgort_name_2,
case when mat_products_1_category_name = "电池片" then "电池片在制"
else mat_products_1_category_name end mat_products_1_category_name,
"主流",
"常规",
is_oem_flag,
is_rd_flag,
case when faty_code = 2000 then "义乌七厂"
when faty_code = 3000 then "天津"
when faty_code = 1000 then "佛山"
when faty_code = 8020 then "滁州"
when faty_code = 5000 then "珠海"
when faty_code = 8010 then "济南" else "其他" end base_name,
pro_products_1_category_name,
"在制无工艺",
main_grid_qty_txt,
case when mat_products_1_category_name in ("电池片") and cell_width_code is not null then cell_width_code
when mat_products_1_category_name in ("电池片") and cell_width_code is null then SUBSTRING (cell_size_code, LOCATE('*',cell_size_code)+1,3)
else "" end cell_size_code,
cell_size_code as cell_size_code_raw,
"在制",
0,
"在制无等级",
0,
case when '${单位}' = 'WPCS' then work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else work_in_progress_pc_qty end full_num,
work_in_progress_amt,
case when '${库存类型}' = "在制" then raw_material_cost_amt else 0 end raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_year_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_year_work_in_progress_pc_qty/10000 end last_year_work_in_progress_pc_qty,
last_year_work_in_progress_amt,
0 ,
0,
case when '${库存类型}' = "在制" then last_year_raw_material_cost_amt else 0 end last_year_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_half_year_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_half_year_work_in_progress_pc_qty/10000 end last_half_year_work_in_progress_pc_qty,
last_half_year_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_half_year_raw_material_cost_amt else 0 end last_half_year_raw_material_cost_amt,
case when '${单位}' = 'PCS' then before_last_m_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else before_last_m_work_in_progress_pc_qty/10000 end before_last_m_work_in_progress_pc_qty,
before_last_m_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then before_last_m_raw_material_cost_amt else 0 end before_last_m_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_m_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_m_work_in_progress_pc_qty/10000 end last_m_work_in_progress_pc_qty,
last_m_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_m_raw_material_cost_amt else 0 end last_m_raw_material_cost_amt,
case when '${单位}' = 'PCS' then this_m_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else this_m_work_in_progress_pc_qty/10000 end this_m_work_in_progress_pc_qty,
this_m_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then this_m_raw_material_cost_amt else 0 end this_m_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_7_d_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_7_d_work_in_progress_pc_qty/10000 end last_7_d_work_in_progress_pc_qty,
last_7_d_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_7_d_raw_material_cost_amt else 0 end last_7_d_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_2_w_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_2_w_work_in_progress_pc_qty/10000 end last_2_w_work_in_progress_pc_qty,
last_2_w_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_2_w_raw_material_cost_amt else 0 end last_2_w_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_3_w_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_3_w_work_in_progress_pc_qty/10000 end last_3_w_work_in_progress_pc_qty,
last_3_w_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_3_w_raw_material_cost_amt else 0 end last_3_w_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_4_w_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_4_w_work_in_progress_pc_qty/10000 end last_4_w_work_in_progress_pc_qty,
last_4_w_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_4_w_raw_material_cost_amt else 0 end last_4_w_raw_material_cost_amt
from
aiko_dm_isc.dws_work_in_progress_rgalfudy
where dt between date_add(CURDATE(),interval -6 day) and CURDATE()
and mat_products_1_category_name = "电池片"
),
aiko_wk as (select aiko_week,CAST(SUBSTRING(aiko_week,3,4) as int) num from aiko_dm_qms.dim_date_dimension
where date_all between STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') and CURRENT_DATE()
group by
aiko_week
order by
num desc
limit 4),
ads_inventory_trend as (
select
inventory_type,
"近七天" trend_lable,
DATE_FORMAT(snpsht_date, '%m/%d') trend_tm,
cast(concat(200,DATE_FORMAT(snpsht_date, '%m%d')) as int) trend_num,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
full_num,
full_amt,
material_cost_amt
from ads_inventory_now
union all
select
inventory_type,
"近四周" trend_lable,
(select aiko_week from aiko_wk order by num desc limit 1 offset 3),
121,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
last_4_w_full_pc_qty,
last_4_w_full_amt,
last_4_w_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval 0 day)
union all
select
inventory_type,
"近四周" trend_lable,
(select aiko_week from aiko_wk order by num desc limit 1 offset 2),
122,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
last_3_w_full_pc_qty,
last_3_w_full_amt,
last_3_w_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval 0 day)
union all
select
inventory_type,
"近四周" trend_lable,
(select aiko_week from aiko_wk order by num desc limit 1 offset 1),
123,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
last_2_w_full_pc_qty,
last_2_w_full_amt,
last_2_w_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval 0 day)
union all
select
inventory_type,
"近四周" trend_lable,
(select aiko_week from aiko_wk order by num desc limit 1 ),
124,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
last_7_d_full_pc_qty,
last_7_d_full_amt,
last_7_d_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval 0 day)
union all
select
inventory_type,
"近三月" trend_lable,
CONCAT(MONTH(CURRENT_DATE()),"月"),
4,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
this_m_full_pc_qty,
this_m_full_amt,
this_m_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval 0 day)
union all
select
inventory_type,
"近三月" trend_lable,
CONCAT(MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)),"月"),
3,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
last_m_full_pc_qty,
last_m_full_amt,
last_m_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval 0 day)
union all
select
inventory_type,
"近三月" trend_lable,
CONCAT(MONTH(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)),"月"),
2,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
before_last_m_full_pc_qty,
before_last_m_full_amt,
before_last_m_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval 0 day)
union all
select
inventory_type,
"近半年" trend_lable,
CONCAT(YEAR(CURRENT_DATE()), "上半年"),
1,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
last_half_year_full_pc_qty,
last_half_year_full_amt,
last_half_year_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval 0 day)
union all
select
inventory_type,
"近一年" trend_lable,
CONCAT(YEAR(CURRENT_DATE())-1, "年"),
0,
is_oem_flag,
is_rd_flag,
mat_code,
long_desc_txt,
mat_batch_code,
faty_code,
warhs_lgort_code,
warhs_lgort_name,
warhs_lgort_name_2,
mat_products_1_category_name,
main_product_type_name,
is_regular_flag,
base_name,
pro_products_1_category_name,
process_name,
main_grid_txt,
cell_size_code,
siliw_size_code,
cell_size_code_raw,
cell_conversion_rate,
cell_grade_code,
inventory_age_qty,
last_year_full_pc_qty,
last_year_full_amt,
last_year_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
)
select
inventory_type as "在库/在途/在制",
is_rd_flag as "是否研发",
is_oem_flag as "是否代工",
trend_lable as "时间趋势",
trend_tm as "趋势时间",
trend_num as "趋势排序",
mat_code as "物料编码",
long_desc_txt as "物料描述",
mat_batch_code as "批次",
faty_code as "工厂编码",
warhs_lgort_code as "库位编码",
warhs_lgort_name as "库位名称",
warhs_lgort_name_2 as "非在制/在制",
t1.mat_products_1_category_name as "资材类型",
main_product_type_name as "主流/非主流",
is_regular_flag as "常规/非常规",
base_name as "基地名称",
t1.pro_products_1_category_name as "产品类别",
process_name as "工艺",
main_grid_txt as "电池片主栅",
cell_size_code as "电池片尺寸",
siliw_size_code as "硅片尺寸",
cell_size_code_raw as "电池片原尺寸",
cell_conversion_rate as "电池片效率",
cell_grade_code as "电池片等级",
inventory_age_qty as "库龄",
full_num as "全库存(单位)",
full_amt as "全库存(金额)",
material_cost_amt as "物料成本",
case when '${业务线}' = "" then t2.full_turnover_days_goal
when '${业务线}' <> "" then t2.pro_turnover_days_goal
else t2.full_turnover_days_goal end "动态周转目标",
case
when '${业务线mw}' = "" and '${单位}' = "MW" then t2.full_watt_qty_goal
when '${业务线mw}' <> "" and '${单位}' = "MW" then t2.pro_watt_qty_goal
when '${业务线mw}' = "" and '${单位}' = "PCS" then t2.full_pc_qty_goal
when '${业务线mw}' <> "" and '${单位}' = "PCS" then t2.pro_pc_qty_goal
else t2.full_watt_qty_goal end "动态周转库存量目标",
case
when '${单位}' = "MW" then t2.size_watt_qty_goal
when '${单位}' = "PCS" then t2.size_pc_qty_goal
else 0 end "尺寸维度库存量目标",
case when '${业务线硅片}' = "" then t2.full_turnover_days_goal
when '${业务线硅片}' <> "" then t2.pro_turnover_days_goal
else t2.full_turnover_days_goal end "硅片动态周转目标",
case when '${业务线硅片mw}' = "" then t2.full_pc_qty_goal
when '${业务线硅片mw}' <> "" then t2.pro_pc_qty_goal
else t2.full_pc_qty_goal end "硅片动态周转库存量目标",
sum(full_amt) over (partition by trend_tm,t1.mat_products_1_category_name,inventory_type ) as "单位之间总金额",
sum(case when mat_products_1_category_name = "硅片" then 0 else material_cost_amt end ) over (partition by trend_tm) as "单位之间总成本",
sum(full_num) over (partition by trend_tm,t1.mat_products_1_category_name,inventory_type ) as "单位之间总库存"
from
ads_inventory_trend t1
left join
aiko_dm_isc.dim_complete_inventory_cell_compo_goal t2
on
t1.mat_products_1_category_name = t2.mat_products_1_category_name
and t1.pro_products_1_category_name = t2.pro_products_1_category_name
and t1.cell_size_code = t2.size_code