需求说明:ERP系统中计算货龄,采用SQL方式实现,使用PostgreSQL
前提:假设要计算t时刻某item的货龄,记t时刻其库存量为n
create or replace function cargoAge(coding varchar) returns integer as
$body$
declare
rawMaterials integer:= 0;
semiFinishedProducts integer:= 0;
product integer:= 0;
cargoAge float;
begin
-- 计算 当前的库存量
select sum(quantity) into rawMaterials from isc_raw_material_warehouse where material_code = coding;
select sum(quantity) into semiFinishedProducts from isc_semi_finished_product_warehouse where material_code = coding;
select sum(quantity) into product from isc_finished_product_warehouse where material_code = coding;
-- 物料
IF rawMaterials > 0 THEN
-- 临时表 函数运行完成后自动删除
-- 按照时间倒排对 数量进行累加
-- 在使用过程中 发现日期相同时,如下情形 (会出现一个错误的临界点)
-- 故先试用 row_number 进行排序
-- M01040200000150 4268.00 2021-10-23 00:00:00 10000 4268.00
-- M01040200000150 4497.00 2021-10-20 00:00:00 10000 17149.00
-- M01040200000150 4344.00 2021-10-20 00:00:00 10000 17149.00
-- M01040200000150 4040.00 2021-10-20 00:00:00 10000 17149.00
-- M01040200000150 1692.00 2021-10-08 00:00:00 10000 18841.00
-- M01040200000150 4345.00 2021-09-22 00:00:00 10000 23186.00
-- M01040200000150 3174.00 2021-09-08 00:00:00 10000 29360.00
-- M01040200000150 3000.00 2021-09-08 00:00:00 10000 29360.00
-- M01040200000150 4000.00 2021-08-19 00:00:00 10000 33628.00
-- M01040200000150 268.00 2021-08-19 00:00:00 10000 33628.00
-- M01040200000150 6000.00 2021-07-10 00:00:00 10000 40793.00
-- M01040200000150 1165.00 2021-07-10 00:00:00 10000 40793.00
CREATE TEMP TABLE temp_cargoAge ON COMMIT DROP as
select
material_code,
invoice_inventory_quantity,
receipt_date,
inventory,
rank,
sum(invoice_inventory_quantity) over(partition by material_code order by rank ) sum_quantity
from (
select
material_code,
invoice_inventory_quantity,
receipt_date,
rawMaterials as inventory,
row_number() over(partition by material_code order by receipt_date desc) rank
from (
select isc_purchase_receipt_order_no, receipt_date
from isc_purchase_receipt_order_header
) a
inner join (
select inbound_order_number,invoice_inventory_quantity, material_code
from isc_purchase_receipt_order_row
where material_code = coding
) b
on a.isc_purchase_receipt_order_no = b.inbound_order_number
) c ;
-- 临界条件是 inventory > sum_quantity 但是要加上去临界条件后的第一条数据
-- 故使用 union 进行拼接数据,此处使用 postgresql 可使用 limit 1 ,
-- 其他数据库 未知
select
sum(
case when inventory >= sum_quantity then invoice_inventory_quantity*(date_part('day', current_date - receipt_date ))/inventory
when inventory < sum_quantity then (invoice_inventory_quantity + inventory - sum_quantity)*(date_part('day', current_date - receipt_date ))/inventory end
) into cargoAge
from (
select material_code, invoice_inventory_quantity, receipt_date, inventory, sum_quantity from temp_cargoAge where inventory > sum_quantity
union
(select material_code, invoice_inventory_quantity, receipt_date, inventory, sum_quantity from temp_cargoAge where inventory <= sum_quantity limit 1 )
) c ;
return cargoAge;
END IF;
-- 半成品
IF rawMaterials > 0 THEN
CREATE TEMP TABLE temp_rawMaterials ON COMMIT DROP as
select
material_code,
tracking_date,
storage_quantity ,
inventory,
rank,
sum(storage_quantity) over(partition by material_code order by rank ) sum_quantity
from (
SELECT
material_code,
tracking_date,
storage_quantity ,
rawMaterials as inventory,
row_number() over(partition by material_code order by tracking_date desc) rank
FROM(
SELECT isc_production_receipt_order_header_id, tracking_date
FROM isc_production_receipt_order_header
) A
INNER JOIN (
SELECT material_code, storage_quantity, isc_production_receipt_order_header_id
FROM isc_production_receipt_order_row
WHERE material_code = coding
) b ON A.isc_production_receipt_order_header_id = b.isc_production_receipt_order_header_id
) c ;
select
sum(
case when inventory >= sum_quantity then storage_quantity*(date_part('day', current_date - tracking_date ))/inventory
when inventory < sum_quantity then (storage_quantity + inventory - sum_quantity)*(date_part('day', current_date - tracking_date ))/inventory end
) into cargoAge
from (
select material_code,tracking_date,storage_quantity ,inventory,rank,sum_quantity from temp_rawMaterials where inventory > sum_quantity
union
(select material_code,tracking_date,storage_quantity ,inventory,rank,sum_quantity from temp_rawMaterials where inventory <= sum_quantity limit 1 )
) c ;
return cargoAge;
END IF;
-- 半成品
IF product > 0 THEN
CREATE TEMP TABLE temp_product ON COMMIT DROP as
select
material_code,
tracking_date,
storage_quantity ,
inventory,
rank,
sum(storage_quantity) over(partition by material_code order by rank ) sum_quantity
from (
SELECT
material_code,
tracking_date,
storage_quantity ,
product as inventory,
row_number() over(partition by material_code order by tracking_date desc) rank
FROM(
SELECT isc_production_receipt_order_header_id, tracking_date
FROM isc_production_receipt_order_header
) A
INNER JOIN (
SELECT material_code, storage_quantity, isc_production_receipt_order_header_id
FROM isc_production_receipt_order_row
WHERE material_code = coding
) b ON A.isc_production_receipt_order_header_id = b.isc_production_receipt_order_header_id
) c ;
select
sum(
case when inventory >= sum_quantity then storage_quantity*(date_part('day', current_date - tracking_date ))/inventory
when inventory < sum_quantity then (storage_quantity + inventory - sum_quantity)*(date_part('day', current_date - tracking_date ))/inventory end
) into cargoAge
from (
select material_code,tracking_date,storage_quantity ,inventory,rank,sum_quantity from temp_product where inventory > sum_quantity
union
(select material_code,tracking_date,storage_quantity ,inventory,rank,sum_quantity from temp_product where inventory <= sum_quantity limit 1 )
) c ;
return cargoAge;
END IF;
return cargoAge;
end
$body$
language plpgsql;