数据仓库实践：使用 SQL 计算材料BOM成本单价

背景

在制造业财务数据分析建设过程中，有时需要通过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