delete from rs_dm_pms.rs_dm_available_stock_di where static_date<current_date-90;
delete from rs_dm_pms.rs_dm_available_stock_di where static_date=current_date;
insert into rs_dm_pms.rs_dm_available_stock_di
select
coalesce(t1.fcty_code,t2.factory_code) 厂区编码
,t4.fcty_name 厂区名称
,'立体库' 仓库
,t3.two_class_name 二级分类
,coalesce(t1.pd_code,t2.product_code) 产品编码
,t3.pd_name 产品名称
,t3.pd_sis_name 规格
,coalesce(t1.elig_the_date,t2.manufac_month) 合格证日期
,coalesce(t1.status_desc,t2.material_type) 物料状态
,t2.order_no
,case when t1.pd_code='11100172' then coalesce(t1.num,0)*4
when t1.pd_code='11100136' then coalesce(t1.num,0)*200
when t1.pd_code='11100128' then coalesce(t1.num,0)*200
when t1.pd_code='11100173' then coalesce(t1.num,0)*4
when t1.pd_code='21100103' then coalesce(t1.num,0)*15
else floor(coalesce(coalesce(t1.weight,0)/t3.fet_wgt,0))
end 库存数量
,coalesce(t1.weight,0) 库存重量
,coalesce(t2.num,0) 占用数量
,coalesce(t2.weight,0) 占用重量
,coalesce(sum(t2.num) over(partition by coalesce(t1.fcty_code,t2.factory_code),coalesce(t1.pd_code,t2.product_code),coalesce(t1.elig_the_date,t2.manufac_month), coalesce(t1.status_desc,t2.material_type)),0) 累计占用数量
,coalesce(sum(t2.weight) over(partition by coalesce(t1.fcty_code,t2.factory_code),coalesce(t1.pd_code,t2.product_code),coalesce(t1.elig_the_date,t2. manufac_month),coalesce(t1.status_desc,t2.material_type)),0) 累计占用重量
,case when t1.pd_code='11100172' then coalesce(t1.num,0)*4
when t1.pd_code='11100136' then coalesce(t1.num,0)*200
when t1.pd_code='11100128' then coalesce(t1.num,0)*200
when t1.pd_code='11100173' then coalesce(t1.num,0)*4
when t1.pd_code='21100103' then coalesce(t1.num,0)*15
else floor(coalesce(coalesce(t1.weight,0)/t3.fet_wgt,0))
end-coalesce(sum(t2.num) over(partition by coalesce(t1.fcty_code,t2.factory_code),coalesce(t1.pd_code,t2.product_code),coalesce(t1.elig_the_date,t2.manufac_month),coalesce(t1.status_desc,t2.material_type)),0) as 可用数量
,coalesce(t1.weight,0)-coalesce(sum(t2.weight) over(partition by coalesce(t1.fcty_code,t2.factory_code),coalesce(t1.pd_code,t2.product_code),coalesce(t1.elig_the_date,t2.manufac_month),coalesce(t1.status_desc,t2.material_type)),0) 可用重量
,current_date
,now()
,'2' as date_type
from ( -- 实时库存
select t1.fcty_code
,t1.pd_code
,t1.elig_the_date
,t1.status_desc
,sum(t1.inventoty_qty) as num
,sum(t1.inventoty_wgt) as weight
from (
select t1.fcty_code
,t1.pd_code
,t1.elig_the_date
,t1.status_desc
,sum(t1.qty)+sum(coalesce(t2.out_in_wahse_qty,0)) as inventoty_qty
,sum(t1.weight)+sum(coalesce(t2.out_in_wahse_wgt,0)) as inventoty_wgt
from (
select t.fcty_code
,t.wahse_code
,t.pd_code
,t.status_desc as status_desc
,sum(t.qty) as qty
,sum(t.weight) as weight
,elig_the_date
from ( -- 期初库存
select fcty_code
,wahse_code
,pd_code
,status_desc
,qty
,weight
,replace(substring(cast(elig_the_time as text),1,7),'-','') as elig_the_date
from rs_dws_pms.rs_dws_pms_wh_frz_pot_day_inventory_di
where dt=replace(cast(current_date as text),'-','')
and wahse_code like '%CC-CK-02%'
union all -- 入库库存
select com_code
,warehouse_code
,prod_code
,coalesce(mel_status_name,'') as status_desc
,prod_num
,prod_wgt
,replace(substring(cast(certificates_date as text),1,7),'-','') as elig_the_date
from rs_dwd.dwd_pms_tml_in_out_dtl_rf
where out_in_store_time>= current_date
and out_in_store_time< current_date+1
and length(com_code)>1
and (warehouse_code like '%CC-CK-02%' -- 立体库
-- or warehouse_code like '%CC-CK-03%'
) -- 冷藏库
and out_in_type ='入库'
and is_deleted='0'
) t
group by t.fcty_code
,t.wahse_code
,t.pd_code
,t.status_desc
,t.elig_the_date
) t1
left join
( -- 出库库存
select com_code as fcty_code
,warehouse_code as wahse_code
,prod_code as pd_code
,mel_status_name as status_desc
,sum(-prod_num) as out_in_wahse_qty
,sum(-prod_wgt) as out_in_wahse_wgt
,replace(substring(cast(certificates_date as text),1,7),'-','') as elig_the_date
from rs_dwd.dwd_pms_tml_in_out_dtl_rf
where out_in_store_time>= current_date
and out_in_store_time< current_date+1
and (warehouse_code like '%CC-CK-02%' -- 立体库
-- or warehouse_code like '%CC-CK-03%'
) -- 冷藏库
and out_in_type ='出库'
and is_deleted='0'
and ( order_no in -- 已点确认发货的流水
(select t2.code order_no
from rs_ods_pms.rs_ods_my_deliver_order_detail_si as t1
left join rs_ods_pms.rs_ods_my_deliver_order_si as t2
on t2.id = t1.order_id
left join rs_ods_pms.rs_ods_my_deliver_transport_si as t3
on t3.id = t2.transport_id
where t3.plan_time=current_date
and t1.one_class_code <> '10'
and t2.delivery_mode_code <> '1105003'
and t3.confirm_status=1
group by t2.code
)
or out_in_bsn_type_code='1117011' -- 不过滤物料状态转换出库
or out_in_bln in
(select plan_no
from rs_ods_pms.rs_ods_my_stock_plan_si
where status=20
)
)
group by com_code
,warehouse_code
,prod_code
,mel_status_name
,replace(substring(cast(certificates_date as text),1,7),'-','')
) t2 on t1.fcty_code = t2.fcty_code
and t1.wahse_code = t2.wahse_code
and t1.elig_the_date = t2.elig_the_date
and t1.pd_code = t2.pd_code
and t1.status_desc = t2.status_desc
group by t1.fcty_code
,t1.pd_code
,t1.elig_the_date
,t1.status_desc
) t1
where 1=1
and t1.elig_the_date is not null
group by t1.fcty_code
,t1.pd_code
,t1.elig_the_date
,t1.status_desc
) t1
full join ( -- 库存占用记录
select t2.order_no
, t2.factory_code
, t2.product_code
, t2.manufac_month
, t2.material_type
, sum(t2.num) num
, sum(t2.weight) weight
from
(
select
t1.id
, factory_code
, t1.order_no
, t1.manufac_month
, case material_type when 0 then '合格品' when 5 then '样品' when 10 then '副产承包'
when 15 then '定向销售' when 20 then '待返工' when 25 then '半成品' when 30 then '三方产品'
when 35 then '调运产品' when 40 then '预警产品'
end as material_type
,(case
when type = 1 then t1.num
when type = 5 then -t1.num
end) num
,(case
when type = 1 then t1.weight
when type = 5 then -t1.weight
end) weight
, t1.status
, t1.product_code
from
rs_ods_pms.rs_ods_my_order_stock_allocate_record_si as t1
where t1.status = 0
and t1.finish_status=0
)t2
group by t2.order_no
, t2.factory_code
, t2.product_code
, t2.manufac_month
, t2.material_type
) t2
on t1.fcty_code=t2.factory_code
and t1.pd_code=t2.product_code
and t1.elig_the_date=t2.manufac_month
and t1.status_desc=t2.material_type
left join(
select pd_code
,case when coalesce(fet_wgt,0)=0 then 1 else fet_wgt end as fet_wgt
,pd_name
,pd_sis_name
,two_class_name
from rs_dim_pms.rs_dim_pms_product_info_da
group by pd_code
,case when coalesce(fet_wgt,0)=0 then 1 else fet_wgt end
,pd_name
,pd_sis_name
,two_class_name
)t3
on coalesce(t1.pd_code,t2.product_code) = t3.pd_code
left join (
select fcty_code ,fcty_name
from rs_dim_pms.rs_dim_pms_work_shop_storage_da
group by fcty_code ,fcty_name
) t4 on coalesce(t1.fcty_code,t2.factory_code)=t4.fcty_code
;
insert into rs_dm_pms.rs_dm_available_stock_di
select
coalesce(t1.fcty_code,t2.factory_code) 厂区编码
,t4.fcty_name 厂区名称
,'立体库' 仓库
,t3.two_class_name 二级分类
,coalesce(t1.pd_code,t2.product_code) 产品编码
,t3.pd_name 产品名称
,t3.pd_sis_name 规格
,coalesce(t1.elig_the_date,t2.manufac_month) 合格证日期
,coalesce(t1.status_desc,t2.material_type) 物料状态
--t1.fcty_code 厂区
--,t1.pd_code 产品
,'' as order_no
--,t1.elig_the_date 合格证日期
--,t1.status_desc 物料状态
,case when t1.pd_code='11100172' then coalesce(t1.num,0)*4
when t1.pd_code='11100136' then coalesce(t1.num,0)*200
when t1.pd_code='11100128' then coalesce(t1.num,0)*200
when t1.pd_code='11100173' then coalesce(t1.num,0)*4
when t1.pd_code='21100103' then coalesce(t1.num,0)*15
else floor(coalesce(coalesce(t1.weight,0)/t3.fet_wgt,0))
end 库存数量
,coalesce(t1.weight,0) 库存重量
,coalesce(t2.num,0) 占用数量
,coalesce(t2.weight,0) 占用重量
,coalesce(sum(t2.num) over(partition by coalesce(t1.fcty_code,t2.factory_code),coalesce(t1.pd_code,t2.product_code),coalesce(t1.elig_the_date,t2.manufac_month),coalesce(t1.status_desc,t2.material_type)),0) 累计占用数量
,coalesce(sum(t2.weight) over(partition by coalesce(t1.fcty_code,t2.factory_code),coalesce(t1.pd_code,t2.product_code),coalesce(t1.elig_the_date,t2.manufac_month),coalesce(t1.status_desc,t2.material_type)),0) 累计占用重量
,case when t1.pd_code='11100172' then coalesce(t1.num,0)*4
when t1.pd_code='11100136' then coalesce(t1.num,0)*200
when t1.pd_code='11100128' then coalesce(t1.num,0)*200
when t1.pd_code='11100173' then coalesce(t1.num,0)*4
when t1.pd_code='21100103' then coalesce(t1.num,0)*15
else floor(coalesce(coalesce(t1.weight,0)/t3.fet_wgt,0))
end-coalesce(sum(t2.num) over(partition by coalesce(t1.fcty_code,t2.factory_code),coalesce(t1.pd_code,t2.product_code),coalesce(t1.elig_the_date,t2.manufac_month),coalesce(t1.status_desc,t2.material_type)),0) as 可用数量
,coalesce(t1.weight,0)-coalesce(sum(t2.weight) over(partition by coalesce(t1.fcty_code,t2.factory_code),coalesce(t1.pd_code,t2.product_code),coalesce(t1.elig_the_date,t2.manufac_month),coalesce(t1.status_desc,t2.material_type)),0) 可用重量
,current_date
,now()
,'1' as date_type
from (
select t1.fcty_code
,t1.pd_code
,t1.elig_the_date
,t1.status_desc
,sum(t1.inventoty_qty) as num
,sum(t1.inventoty_wgt) as weight
from (
select t1.fcty_code
,t1.pd_code
,t1.elig_the_date
,t1.status_desc
,sum(t1.qty)+sum(coalesce(t2.out_in_wahse_qty,0)) as inventoty_qty
,sum(t1.weight)+sum(coalesce(t2.out_in_wahse_wgt,0)) as inventoty_wgt
from (
select t.fcty_code
,t.wahse_code
,t.pd_code
,t.status_desc as status_desc
,sum(t.qty) as qty
,sum(t.weight) as weight
,elig_the_date
from (
select fcty_code
,wahse_code
,pd_code
,status_desc
,qty
,weight
,replace(substring(cast(elig_the_time as text),1,7),'-','') as elig_the_date
from rs_dws_pms.rs_dws_pms_wh_frz_pot_day_inventory_di
where dt=replace(cast(current_date as text),'-','')
and wahse_code like '%CC-CK-02%'
union all
select com_code
,warehouse_code
,prod_code
,coalesce(mel_status_name,'') as status_desc
,prod_num
,prod_wgt
,replace(substring(cast(certificates_date as text),1,7),'-','') as elig_the_date
from rs_dwd.dwd_pms_tml_in_out_dtl_rf
where out_in_store_time>= current_date
and out_in_store_time< current_date+1
and length(com_code)>1
and (warehouse_code like '%CC-CK-02%' -- 立体库
-- or warehouse_code like '%CC-CK-03%'
) -- 冷藏库
and out_in_type ='入库'
and is_deleted='0'
) t
group by t.fcty_code
,t.wahse_code
,t.pd_code
,t.status_desc
,t.elig_the_date
) t1
left join
(
select com_code as fcty_code
,warehouse_code as wahse_code
,prod_code as pd_code
,mel_status_name as status_desc
,sum(-prod_num) as out_in_wahse_qty
,sum(-prod_wgt) as out_in_wahse_wgt
,replace(substring(cast(certificates_date as text),1,7),'-','') as elig_the_date
from rs_dwd.dwd_pms_tml_in_out_dtl_rf
where out_in_store_time>= current_date
and out_in_store_time< current_date+1
and (warehouse_code like '%CC-CK-02%' -- 立体库
-- or warehouse_code like '%CC-CK-03%'
) -- 冷藏库
and out_in_type ='出库'
and is_deleted='0'
and ( order_no in -- 已点确认发货的流水
(select t2.code order_no
from rs_ods_pms.rs_ods_my_deliver_order_detail_si as t1
left join rs_ods_pms.rs_ods_my_deliver_order_si as t2
on t2.id = t1.order_id
left join rs_ods_pms.rs_ods_my_deliver_transport_si as t3
on t3.id = t2.transport_id
where t3.plan_time=current_date
and t1.one_class_code <> '10'
and t2.delivery_mode_code <> '1105003'
and t3.confirm_status=1
--and t1.order_no like 'DP-2024042000278%'
group by t2.code
)
or out_in_bsn_type_code='1117011' -- 不过滤物料状态转换出库
or out_in_bln in
(select plan_no
from rs_ods_pms.rs_ods_my_stock_plan_si
where status=20
)
)
group by com_code
,warehouse_code
,prod_code
,mel_status_name
,replace(substring(cast(certificates_date as text),1,7),'-','')
) t2 on t1.fcty_code = t2.fcty_code
and t1.wahse_code = t2.wahse_code
and t1.elig_the_date = t2.elig_the_date
and t1.pd_code = t2.pd_code
and t1.status_desc = t2.status_desc
group by t1.fcty_code
,t1.pd_code
,t1.elig_the_date
,t1.status_desc
) t1
where 1=1
and t1.elig_the_date is not null
group by t1.fcty_code
,t1.pd_code
,t1.elig_the_date
,t1.status_desc
) t1
full join (
select t2.factory_code
-- , t2.order_no
, t2.product_code
, t2.manufac_month
, t2.material_type
, sum(t2.num) num
, sum(t2.weight) weight
from
(
select
t1.id
, factory_code
-- , t1.order_no
, t1.manufac_month
, case material_type when 0 then '合格品' when 5 then '样品' when 10 then '副产承包'
when 15 then '定向销售' when 20 then '待返工' when 25 then '半成品' when 30 then '三方产品'
when 35 then '调运产品' when 40 then '预警产品'
end as material_type
,(case
when type = 1 then t1.num
when type = 5 then -t1.num
end) num
,(case
when type = 1 then t1.weight
when type = 5 then -t1.weight
end) weight
, t1.status
, t1.product_code
-- , to_timestamp(gmt_create/1000) as tm
from
rs_ods_pms.rs_ods_my_order_stock_allocate_record_si as t1
where t1.status = 0
and t1.finish_status=0
)t2
group by t2.factory_code
-- , t2.order_no
, t2.product_code
, t2.manufac_month
, t2.material_type
) t2
on t1.fcty_code=t2.factory_code
and t1.pd_code=t2.product_code
and t1.elig_the_date=t2.manufac_month
and t1.status_desc=t2.material_type
left join(
select pd_code
,case when coalesce(fet_wgt,0)=0 then 1 else fet_wgt end as fet_wgt
,pd_name
,pd_sis_name
,two_class_name
from rs_dim_pms.rs_dim_pms_product_info_da
group by pd_code,case when coalesce(fet_wgt,0)=0 then 1 else fet_wgt end
,pd_name
,pd_sis_name
,two_class_name
)t3
on coalesce(t1.pd_code,t2.product_code) = t3.pd_code
left join (
select fcty_code ,fcty_name
from rs_dim_pms.rs_dim_pms_work_shop_storage_da
group by fcty_code ,fcty_name
) t4 on coalesce(t1.fcty_code,t2.factory_code)=t4.fcty_code
;解析一下
最新发布
解决Discuz安装后首页报错问题
扫一扫
5770
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
