背景
在制造业财务数据分析建设过程中，有时需要通过BOM汇总计算材料的单价，一般会有采购核价，库存成本，还有下阶材料单价按用量汇总得到的单价参与。
这些单价来源一般会根据优先级获取并在计算后作为最终的BOM 单价结果。参与财务三大报表中的损益表计算。
获取数据：采购核价、币种和汇率
采购核价
核价一般会包括成品和底层材料，而且优先级一般会比较高，所以这里直接使用核价的方式设置一个成品的单价，以用于测试。
库存成本
除了采购核价外，库存成本一般是覆盖范围比较广的单价，能够覆盖到最底层的大量材料料号。
这里我们在初始化时设置最底层材料成本时，将使用库存成本金额。
币种
为了兼容多个货币和地区，价格会有一个字段表示币种。金额则是此币种的单价金额。
同时需要带上一个汇率明细。以满足各个币种单价金额的汇算。
获取数据：BOM汇总结构
初始化时使用核价和成本单间仍然不能完全覆盖企业内使用的所有的材料，尤其是BOM结构复杂、材料料号过多的情况下。
所以一般情况下，企业会使用BOM关系，将材料的单价汇总计算到上阶材料。
本文将使用 bom 结构重新汇总后的BOM数据分析维度，具体可见：
数据仓库实践：使用SQL汇总BOM数据分析维度 http://t.csdnimg.cn/gZ1pS
部分 dim_bom 结构
初始化
使用BOM关联并计算核价和成本单价
insert into dw_mt_bom_unit_price_mview(material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate)
select *
from (with tmp_prod as (select material_code material_code_prod,
material_code,
null currency_cost,
null as price_cost_ori,
null as price_cost,
c.currency_verify,
c.price_verify as price_verify_ori,
d.exchange_rate * IFNULL(c.price_verify, 0) as price_verify,
null price_calculate
from (select distinct bom_subordinate.product_code as material_code
# BOM 中只在 product_code 字段存在 不在 material_code 存在代表 该物料没有上阶物料 推断出本身是成品
from (select product_code
from dim_bom) bom_subordinate
left join (select material_code
from dim_bom) bom_superior
on bom_subordinate.product_code = bom_superior.material_code
where bom_superior.material_code is null) a
# 采购核价
left join(SELECT material_code mat_code,
currency currency_verify,
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
# 汇率
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') d
on c.currency_verify = d.currency_transaction),
tmp_mat as (select product_code material_code_prod,
material_code,
currency_cost,
price_cost as price_cost_ori,
case when price_cost is null then 0 else price_cost * e.exchange_rate end as price_cost,
currency_verify,
price_verify as price_verify_ori,
case
when price_verify is null then 0
else price_verify * d.exchange_rate end as price_verify,
case
when price_cost is not null then price_cost * e.exchange_rate
when price_verify is not null then price_verify * d.exchange_rate
else 0 end price_calculate
from (select a.product_code,
a.material_code
from (select distinct bom_superior.product_code,
bom_superior.material_code
from (select product_code,
material_code
from dim_bom) bom_superior
left join (select product_code
from dim_bom) bom_subordinate
on bom_superior.material_code = bom_subordinate.product_code
where bom_subordinate.product_code is null) a
inner join(select distinct bom_subordinate.product_code prod_code
from (select product_code
from dim_bom) bom_subordinate
left join (select material_code
from dim_bom) bom_superior
on bom_subordinate.product_code = bom_superior.material_code
where bom_superior.material_code is null) b
on a.product_code = b.prod_code) a
left join(SELECT material_code mat_code,
currency currency_cost,
# 库存成本
amount_price price_cost
from clip_ods.price_cost) b
on a.material_code = b.mat_code
left join(SELECT material_code mat_code,
currency currency_verify,
# 核价
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') d
on c.currency_verify = d.currency_transaction
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') e
on b.currency_cost = e.currency_transaction),
tmp_semi as (select productid material_code_prod,
material_code,
currency_cost,
price_cost as price_cost_ori,
case when price_cost is null then 0 else price_cost * e.exchange_rate end as price_cost,
currency_verify,
price_verify as price_verify_ori,
case
when price_verify is null then 0
else price_verify * d.exchange_rate end as price_verify,
price_cost * e.exchange_rate price_calculate
from (select a.productid,
a.material_code
from (select b.productid,
b.material_code
from (select distinct a.productid
from (select product_code productid
from dim_bom) a
left join (select material_code subprodid
from dim_bom) b
on a.productid = b.subprodid
where b.subprodid is null) a
inner join (select distinct product_code productid,
material_code
from dim_bom) b
on a.productid = b.productid) a
left join (select material_code_prod,
material_code
from tmp_mat
where price_calculate is not null) b
on a.material_code = b.material_code
and a.productid = b.material_code_prod
where b.material_code is null) a
left join(SELECT material_code mat_code,
currency currency_cost,
# 库存成本
amount_price price_cost
from clip_ods.price_cost) b
on a.material_code = b.mat_code
left join(SELECT material_code mat_code,
currency currency_verify,
# 核价
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') d
on c.currency_verify = d.currency_transaction
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') e
on b.currency_cost = e.currency_transaction)
select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_prod
union
select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_mat
union
select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_semi) initial_tmp
按BOM结构汇总
从最底层材料向上汇总，这里需要保证所有的最底层材料都有初始化后的单价，即使单价值为0；
因为如果下阶材料单价为空（Null）, 系统将无法判断此空值（Null）是否有意义，
而且，系统和开发人员所处的角色自身没有权限决定【单价为空时设置为0】，因为有可能这个单价另有其他来源。
如果财务相关对接人员有确定为空时单价设置为0，则应该在初始化时设置为0；
同时【下阶材料单价为空时不汇总】这一操作会在递归汇总中作为判断【该材料的下阶材料是否在之前的递归过程中计算完成】的标准；
最底层材料为空则会导致此材料所有的上阶材料【等待此材料计算出单价值后参与上阶材料的计算】；
insert into dw_mt_bom_unit_price_mview(material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate)
select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from (select material_code_prod,
a.material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
a.price_calculate
from (select material_code,
sum(bom_cost) price_calculate
from (select material_code,
bom_cost,
avg(symbol_contrains) over (partition by material_code) a2
from (select b.material_code,
amount_base,
PRICE_CALCULATE,
amount_base * PRICE_CALCULATE bom_cost,
IF(PRICE_CALCULATE is not null, 1, 0) symbol_contrains
from (select product_code material_code,
material_code subprodid,
qpa amount_base
from dim_bom
where lvl = 1) a
inner join (select distinct material_code
from dw_mt_bom_unit_price_mview
where PRICE_CALCULATE is null) b
on a.material_code = b.material_code
left join (select distinct material_code,
price_calculate
from dw_mt_bom_unit_price_mview) c
on a.subprodid = c.material_code) a) a
where a2 = 1
group by material_code) a
inner join (select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify
from dw_mt_bom_unit_price_mview) b
on a.material_code = b.material_code) iter_result
on duplicate key update currency_cost = values(currency_cost),
price_cost_ori=values(price_cost_ori),
currency_verify= values(currency_verify),
price_verify_ori= values(price_verify_ori),
price_verify= values(price_verify),
price_calculate= values(price_calculate);
汇总部分结果
使用存储过程运行的结果
参与计算的表结构
create table currency_exchange
(
currency_transaction varchar(100) not null comment '交易币种',
currency_base varchar(100) not null comment '基础币种',
exchange_rate decimal(24, 8) null comment '汇率',
primary key (currency_transaction, currency_base)
)
comment '汇率明细';
create table price_cost
(
material_code varchar(100) not null comment '料号',
currency varchar(100) not null comment '交易币种',
amount_price decimal(24, 8) null comment '价格',
primary key (material_code, currency)
)
comment '成本单价明细';
create table price_verify
(
material_code varchar(100) not null comment '料号',
currency varchar(100) not null comment '交易币种',
amount_price decimal(24, 8) null comment '价格',
primary key (material_code, currency)
)
comment '核价单价明细';
create table clip_dwh.dim_bom
(
product_code varchar(100) not null comment '上阶料',
material_code varchar(100) not null comment '下阶料',
bom_hierarchy varchar(260) not null comment 'BOM 分层',
lvl int null comment '分层等级',
qpa double null comment '组成用量',
main varchar(100) null comment '主料',
enable varchar(100) null comment '启用',
primary key (product_code, material_code, bom_hierarchy)
) comment 'BOM 明细';
完整存储过程代码
create
definer = root@localhost procedure recursion_bom_unit_price()
BEGIN
DECLARE iter integer default 1;
truncate table clip_dwh.dw_mt_bom_unit_price_mview;
commit;
insert into dw_mt_bom_unit_price_mview(material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate)
select *
from (with tmp_prod as (select material_code material_code_prod,
material_code,
null currency_cost,
null as price_cost_ori,
null as price_cost,
c.currency_verify,
c.price_verify as price_verify_ori,
d.exchange_rate * IFNULL(c.price_verify, 0) as price_verify,
null price_calculate
from (select distinct bom_subordinate.product_code as material_code
# BOM 中只在 product_code 字段存在 不在 material_code 存在代表 该物料没有上阶物料 推断出本身是成品
from (select product_code
from dim_bom) bom_subordinate
left join (select material_code
from dim_bom) bom_superior
on bom_subordinate.product_code = bom_superior.material_code
where bom_superior.material_code is null) a
# 采购核价
left join(SELECT material_code mat_code,
currency currency_verify,
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
# 汇率
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') d
on c.currency_verify = d.currency_transaction),
tmp_mat as (select product_code material_code_prod,
material_code,
currency_cost,
price_cost as price_cost_ori,
case when price_cost is null then 0 else price_cost * e.exchange_rate end as price_cost,
currency_verify,
price_verify as price_verify_ori,
case
when price_verify is null then 0
else price_verify * d.exchange_rate end as price_verify,
case
when price_cost is not null then price_cost * e.exchange_rate
when price_verify is not null then price_verify * d.exchange_rate
else 0 end price_calculate
from (select a.product_code,
a.material_code
from (select distinct bom_superior.product_code,
bom_superior.material_code
from (select product_code,
material_code
from dim_bom) bom_superior
left join (select product_code
from dim_bom) bom_subordinate
on bom_superior.material_code = bom_subordinate.product_code
# BOM 中只在 material_code 字段存在 不在 product_code 存在代表 该物料没有下阶物料 推断出本身是最底层材料
where bom_subordinate.product_code is null) a
inner join(select distinct bom_subordinate.product_code prod_code
from (select product_code
from dim_bom) bom_subordinate
left join (select material_code
from dim_bom) bom_superior
on bom_subordinate.product_code = bom_superior.material_code
# 与 BOM中的最上层成品 join 保证不会有多余的材料料号参与
where bom_superior.material_code is null) b
on a.product_code = b.prod_code) a
left join(SELECT material_code mat_code,
currency currency_cost,
# 库存成本
amount_price price_cost
from clip_ods.price_cost) b
on a.material_code = b.mat_code
left join(SELECT material_code mat_code,
currency currency_verify,
# 核价
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') d
on c.currency_verify = d.currency_transaction
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') e
on b.currency_cost = e.currency_transaction),
tmp_semi as (select productid material_code_prod,
material_code,
currency_cost,
price_cost as price_cost_ori,
case when price_cost is null then 0 else price_cost * e.exchange_rate end as price_cost,
currency_verify,
price_verify as price_verify_ori,
case
when price_verify is null then 0
else price_verify * d.exchange_rate end as price_verify,
price_cost * e.exchange_rate price_calculate
from (select a.productid,
a.material_code
# 然后是 成品 和 材料 之间 的 半成品料号，使用join保证不会有多余料号参与
from (select b.productid,
b.material_code
from (select distinct a.productid
from (select product_code productid
from dim_bom) a
left join (select material_code subprodid
from dim_bom) b
on a.productid = b.subprodid
where b.subprodid is null) a
inner join (select distinct product_code productid,
material_code
from dim_bom) b
on a.productid = b.productid) a
left join (select material_code_prod,
material_code
from tmp_mat
where price_calculate is not null) b
on a.material_code = b.material_code
and a.productid = b.material_code_prod
where b.material_code is null) a
left join(SELECT material_code mat_code,
currency currency_cost,
# 库存成本
amount_price price_cost
from clip_ods.price_cost) b
on a.material_code = b.mat_code
left join(SELECT material_code mat_code,
currency currency_verify,
# 核价
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') d
on c.currency_verify = d.currency_transaction
left join (select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base = 'CNY') e
on b.currency_cost = e.currency_transaction)
# 成品、材料、半成品 结构相同，使用union拼接再插入
select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_prod
union
select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_mat
union
select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_semi) initial_tmp;
commit;
WHILE iter <= 10
DO
set iter = iter + 1;
insert into dw_mt_bom_unit_price_mview(material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate)
select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from (select material_code_prod,
a.material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
a.price_calculate
from (select material_code,
sum(bom_cost) price_calculate
from (select material_code,
bom_cost,
avg(symbol_contrains) over (partition by material_code) a2
# 使用 平均数 保证 此 材料 的所有下阶材料都存在，如果不存在计算出来的材料单价会有问题
from (select b.material_code,
amount_base,
PRICE_CALCULATE,
amount_base * PRICE_CALCULATE bom_cost,
IF(PRICE_CALCULATE is not null, 1, 0) symbol_contrains
from (select product_code material_code,
material_code subprodid,
qpa amount_base
from dim_bom
where lvl = 1) a
inner join (select distinct material_code
from dw_mt_bom_unit_price_mview
where PRICE_CALCULATE is null) b
on a.material_code = b.material_code
left join (select distinct material_code,
price_calculate
from dw_mt_bom_unit_price_mview) c
on a.subprodid = c.material_code) a) a
where a2 = 1
group by material_code) a
inner join (select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify
from dw_mt_bom_unit_price_mview) b
on a.material_code = b.material_code) iter_result
# 出现重复键时覆盖，因为我们一般在初始化时已写入此主键，覆盖重复键则是为了更新
on duplicate key update currency_cost = values(currency_cost),
price_cost_ori=values(price_cost_ori),
currency_verify= values(currency_verify),
price_verify_ori= values(price_verify_ori),
price_verify= values(price_verify),
price_calculate= values(price_calculate);
END WHILE;
commit;
END;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
