需求说明：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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
