create or replace procedure proc_auto_assign(p_admin VARCHAR2,
p_planCode VARCHAR2,
p_materialTypes VARCHAR2,
p_contractCxdwms VARCHAR2,
p_createDate_begin VARCHAR2,
p_createDate_end VARCHAR2,
p_tenderBatch VARCHAR2) is
--定义动态游标
TYPE ref_cur_type IS REF CURSOR;
cur_query ref_cur_type;
cur_query2 ref_cur_type;
cur_query3 ref_cur_type;
cur_query4 ref_cur_type;
--定义存放语句的变量
v_sqlStmt_update string(10000);
v_sqlStmt string(10000);
v_sqlStmt2 string(10000);
v_sqlStmt3 string(10000);
v_sqlStmt4 string(10000);
--定义变量用来接收游标的值
V_TENDER_BATCH VARCHAR2(40); --招标批次
V_PACKET_NUMBER VARCHAR2(40); --包号
V_MATERIAL_TYPES VARCHAR2(30); --物料大类
V_SXH NUMBER(5); --顺序号
V_RATE NUMBER(16, 6); --比例(一个包的已使用总金额/包总金额)
V_BZBH VARCHAR2(40); --报装编号
V_XH_A VARCHAR2(10); --序号
V_ID VARCHAR2(36); --主键
V_ZXH_A VARCHAR2(40); --标识key,报装编号+物料大类+日期（YYYYMMDD）
V_ZFPXH VARCHAR2(40); --分配序号
V_PLAN_CODE VARCHAR2(10); --计划编号
V_PLAN_LINE_NUMBER NUMBER(5); --计划行号
V_SIPPLIES_NUMBER NUMBER(13, 3); --数量
V_ZPICI1 VARCHAR2(40);
V_ZPICI2 VARCHAR2(40);
V_ZPICI3 VARCHAR2(40);
V_ZPICI4 VARCHAR2(40);
V_ZPICI5 VARCHAR2(40);
V_ZPICI6 VARCHAR2(40);
V_ZXH_F VARCHAR2(50); --标识(当前登陆者账号+招标批次+物料大类+包号+序号+日期)
V_PROTOCOL_KCH VARCHAR2(10); --协议库存编号
V_PROTOCOL_LINE_KCH NUMBER(5); --协议库存行号
V_XH_F VARCHAR2(10); --序号
V_TAX_UNIT_PRICE NUMBER(16, 6); --含税单价
V_TAX_SUM_PRICE NUMBER(16, 6); --含税总价
V_ALLOT_THRESHODL VARCHAR2(255); --分配阈值
V_JE_SUM NUMBER(16, 6); --包总金额
V_YSYZJE NUMBER(16, 6); --已使用总金额
V_SUPPLIER_CODE VARCHAR2(40); --供应单位编码
V_SUPPLIER_NAME VARCHAR2(200); --供应单位名称
V_YSYZJE_F NUMBER(16, 6); --已使用总金额
V_PDJE NUMBER(16, 6); --判断金额
V_BATCH NUMBER(16); --批次
begin
select SEQ_PLAN_SUPPLIES_DEMAND_BATCH.Nextval into V_BATCH from dual;
--设置批次
v_sqlStmt_update := ' update t_whxzp_plan_supplies_demand t set BATCH='||V_BATCH||
'where t.alflag=''N'' and delete_mark=''N''';
if p_planCode is not null then
v_sqlStmt_update := v_sqlStmt_update || ' and t.plan_code = ' || chr(39) ||
p_planCode || chr(39);
end if;
if p_materialTypes is not null then
v_sqlStmt_update := v_sqlStmt_update || ' and t.material_types = ' || chr(39) ||
p_materialTypes || chr(39); --存储过程中单引号处理用chr(39)
end if;
if p_contractCxdwms is not null then
v_sqlStmt_update := v_sqlStmt_update || ' and t.contract_cxdwms = ' || chr(39) ||
p_contractCxdwms || chr(39);
end if;
if p_createDate_begin is not null then
v_sqlStmt_update := v_sqlStmt_update || ' and t.create_date>=to_date(' || chr(39) ||
p_createDate_begin || chr(39) || ',''yyyy-mm-dd'')';
end if;
if p_createDate_end is not null then
v_sqlStmt_update := v_sqlStmt_update || ' and t.create_date<=to_date(' || chr(39) ||
p_createDate_end || chr(39) || ',''yyyy-mm-dd'')';
end if;
execute immediate(v_sqlStmt_update);
--找到有几个报装编号
v_sqlStmt := 'SELECT a.bzbh FROM T_PROTOCOL_INVENTORY_ALLOTH a where 1=1 ';
if p_planCode is not null then
v_sqlStmt := v_sqlStmt || ' and a.plan_code = ' || chr(39) ||
p_planCode || chr(39);
end if;
if p_materialTypes is not null then
v_sqlStmt := v_sqlStmt || ' and a.material_types = ' || chr(39) ||
p_materialTypes || chr(39); --存储过程中单引号处理用chr(39)
end if;
if p_contractCxdwms is not null then
v_sqlStmt := v_sqlStmt || ' and a.contract_cxdwms = ' || chr(39) ||
p_contractCxdwms || chr(39);
end if;
v_sqlStmt := v_sqlStmt || ' group by a.bzbh ';
--将所有可用的标识改为找不到，之后找到再改回来
update T_PROTOCOL_INVENTORY_ALLOTH
set ZBZ = '本次分配在既定分配原则中找不到满足条件的协议库存，无法分配'
WHERE isassign = 'N'
and zuser = p_admin;
open cur_query for v_sqlStmt;
LOOP
--将游标的值给变量
FETCH cur_query
INTO V_BZBH;
exit when cur_query%notfound;
--根据时间和登陆人获得最新的F表,并根据招标批次，物料大类，包号分组，再根据招标批次顺序号，比例排序
v_sqlStmt2 := 'select f.tender_batch,
f.material_types,
f.packet_number,
b.sxh,
f.ysyzje / je_sum as rate
from t_protocol_inventory_fplsb f
left join t_protocol_inventory_batch b on f.tender_batch = b.tender_pch
where 1 = 1';
if p_tenderBatch is not null then
v_sqlStmt2 := v_sqlStmt2 || ' and f.tender_batch = ' || chr(39) ||
p_tenderBatch || chr(39);
end if;
v_sqlStmt2 := v_sqlStmt2 || 'group by f.tender_batch,
f.material_types,
f.packet_number,
b.sxh， f.ysyzje / je_sum
order by b.sxh, f.ysyzje / je_sum';
open cur_query2 for v_sqlStmt2;
LOOP
--将游标的值给变量
FETCH cur_query2
INTO V_TENDER_BATCH, V_MATERIAL_TYPES, V_PACKET_NUMBER, V_SXH, V_RATE;
exit when cur_query2%notfound;
--计算每个包需要加入的的金额(假设可以分的情况下)
select sum(a.SIPPLIES_NUMBER * f.TAX_UNIT_PRICE)
into V_PDJE
FROM T_PROTOCOL_INVENTORY_ALLOTH a, t_protocol_inventory_fplsb F
WHERE a.xh = f.xh
and a.BZBH = V_BZBH
AND a.MATERIAL_TYPES = V_MATERIAL_TYPES
and f.PACKET_NUMBER = V_PACKET_NUMBER
and a.isassign = 'N'
and a.zuser = p_admin
and instr(f.zxh, to_char(sysdate, 'yyyymmdd')) > 0 --今天生成的数据
and instr(f.zxh, p_admin) > 0 --当前登陆人生成的数据
and (INSTR(f.zxh, a.zpici1) > 0 or INSTR(f.zxh, a.zpici2) > 0 or
INSTR(f.zxh, a.zpici3) > 0 or INSTR(f.zxh, a.zpici4) > 0 or
INSTR(f.zxh, a.zpici5) > 0 or INSTR(f.zxh, a.zpici5) > 0);
V_YSYZJE_F := -1;
--循环遍历A
v_sqlStmt3 := 'SELECT a.id,a.zxh,a.zfpxh,a.PLAN_CODE,a.PLAN_LINE_NUMBER,a.XH,a.sipplies_number,a.zpici1,a.zpici2,a.zpici3,a.zpici4,a.zpici5,a.zpici6 FROM T_PROTOCOL_INVENTORY_ALLOTH a where a.BZBH=' ||
chr(39) || V_BZBH || chr(39) ||
' and a.material_types=' || chr(39) || V_MATERIAL_TYPES ||
chr(39) || ' and a.isassign=''N'' and zuser=' ||
chr(39) || p_admin || chr(39);
if p_planCode is not null then
v_sqlStmt3 := v_sqlStmt3 || ' and a.plan_code = ' || chr(39) ||
p_planCode || chr(39);
end if;
if p_materialTypes is not null then
v_sqlStmt3 := v_sqlStmt3 || ' and a.material_types = ' || chr(39) ||
p_materialTypes || chr(39); --存储过程中单引号处理用chr(39)
end if;
if p_contractCxdwms is not null then
v_sqlStmt3 := v_sqlStmt3 || ' and a.contract_cxdwms = ' ||
chr(39) || p_contractCxdwms || chr(39);
end if;
v_sqlStmt3 := v_sqlStmt3 || ' order by a.plan_code';
open cur_query3 for v_sqlStmt3;
LOOP
--将游标的值给变量
FETCH cur_query3
INTO V_ID, V_ZXH_A, V_ZFPXH, V_PLAN_CODE, V_PLAN_LINE_NUMBER, V_XH_A, V_SIPPLIES_NUMBER, V_ZPICI1, V_ZPICI2, V_ZPICI3, V_ZPICI4, V_ZPICI5, V_ZPICI6;
exit when cur_query3%notfound;
--根据招标批次，大类和包号对f表分类，并且这个f表与a表相关联,
v_sqlStmt4 := 'select f.zxh,f.protocol_kch,f.protocol_line_kch,f.xh,f.tax_unit_price, f.tax_sum_price,f.allot_threshodl,f.je_sum,f.ysyzje,f.supplier_code,f.supplier_name
from t_protocol_inventory_fplsb f
where f.STOP_MARK=''N'' and f.valid_date>sysdate and f.tender_batch=' ||
chr(39) || V_TENDER_BATCH || chr(39) ||
' and f.packet_number=' || chr(39) || V_PACKET_NUMBER ||
chr(39) || ' and f.material_types=' || chr(39) ||
V_MATERIAL_TYPES || chr(39) || ' and EXISTS (select 1
from t_PROTOCOL_INVENTORY_AllOTH a
where a.BZBH=' || chr(39) || V_BZBH ||
chr(39) || ' and a.material_types=' || chr(39) ||
V_MATERIAL_TYPES || chr(39) ||
' and a.isassign=''N'' and zuser=' || chr(39) ||
p_admin || chr(39) || ' and (instr(f.zxh, ' ||
chr(39) || V_ZPICI1 || chr(39) ||
') > 0
or instr(f.zxh, ' || chr(39) || V_ZPICI2 ||
chr(39) || ') > 0
or instr(f.zxh, ' || chr(39) || V_ZPICI3 ||
chr(39) || ') > 0
or instr(f.zxh, ' || chr(39) || V_ZPICI4 ||
chr(39) || ') > 0
or instr(f.zxh, ' || chr(39) || V_ZPICI5 ||
chr(39) || ') > 0
or instr(f.zxh, ' || chr(39) || V_ZPICI6 ||
chr(39) || ') > 0))';
open cur_query4 for v_sqlStmt4;
LOOP
--将游标的值给变量
FETCH cur_query4
INTO V_ZXH_F, V_PROTOCOL_KCH, V_PROTOCOL_LINE_KCH, V_XH_F, V_TAX_UNIT_PRICE, V_TAX_SUM_PRICE, v_allot_threshodl, V_JE_SUM, V_YSYZJE, V_SUPPLIER_CODE, V_SUPPLIER_NAME;
exit when cur_query4%notfound;
--若无值则将已使用金额赋值给它，由于V_YSYZJE会改变所以用另一个值来接受它
if V_YSYZJE_F = -1 then
V_YSYZJE_F := V_YSYZJE;
end if;
--若序号相等
if V_XH_F = V_XH_A then
--dbms_output.put_line('V_ZXH_A:' || V_ZXH_A || ',zxh:' ||V_ZXH_F);
--dbms_output.put_line('rate:' ||(V_PDJE + V_YSYZJE_F) / V_JE_SUM * 100);
--dbms_output.put_line('V_PDJE:' ||V_PDJE);
--dbms_output.put_line('V_YSYZJE_F:' ||V_YSYZJE_F);
--dbms_output.put_line('V_JE_SUM:' ||V_JE_SUM);
--dbms_output.put_line('V_AllOT_THRESHODL:' ||V_AllOT_THRESHODL);
--若新的已使用比例<=阀值，将F的数据分配给A,跳出当前循环，执行上一层循环
if (V_PDJE + V_YSYZJE_F) / V_JE_SUM * 100 <= V_AllOT_THRESHODL then
--将协议库存编号和行号赋值给A表
update T_PROTOCOL_INVENTORY_ALLOTH
set PROTOCOL_KCH = V_PROTOCOL_KCH,
PROTOCOL_LINE_KCH = V_PROTOCOL_LINE_KCH,
ISASSIGN = 'Y',
ZBZ = '',
ZDATE = sysdate
WHERE ID = V_ID;
--更新D表的PROTOCOL_KCH、行号PROTOCOL_LINE_KCH、并赋值ALFLAG=X
UPDATE T_WHXZP_PLAN_SUPPLIES_DEMAND
set ALFLAG = 'Y',
PROTOCOL_KCH = V_PROTOCOL_KCH,
PROTOCOL_LINE_KCH = V_PROTOCOL_LINE_KCH,
SUPPLIER_CODE = V_SUPPLIER_CODE,
SUPPLIER_NAME = V_SUPPLIER_NAME
WHERE PLAN_CODE = V_PLAN_CODE
and PLAN_LINE_NUMBER = V_PLAN_LINE_NUMBER;
--并更新协议库存信息表(PROTOCOL_INVENTORY_INFO)的已使用数量AllOT_Q（之前的已使用数量+本次分配数量）
UPDATE T_PROTOCOL_INVENTORY_INFO
SET AllOT_Q = AllOT_Q + V_SIPPLIES_NUMBER
WHERE PROTOCOL_KCH = V_PROTOCOL_KCH
AND PROTOCOL_LINE_KCH = V_PROTOCOL_LINE_KCH;
--更改T_PROTOCOL_INVENTORY_FPLSB表的最新已使用数量
update T_PROTOCOL_INVENTORY_FPLSB
set ALLOT_Q = ALLOT_Q + V_SIPPLIES_NUMBER
where ZXH = V_ZXH_F
AND PROTOCOL_KCH = V_PROTOCOL_KCH
AND PROTOCOL_LINE_KCH = V_PROTOCOL_LINE_KCH;
--更改这个包里中T_PROTOCOL_INVENTORY_FPLSB的已使用总金额和比例(招标批次，物料大类和包号为纬度)
update T_PROTOCOL_INVENTORY_FPLSB
set YSYZJE = YSYZJE + V_SIPPLIES_NUMBER * V_TAX_UNIT_PRICE,
rate = ((V_SIPPLIES_NUMBER * V_TAX_UNIT_PRICE) +
V_YSYZJE) / V_JE_SUM
where TENDER_BATCH = V_TENDER_BATCH
AND PACKET_NUMBER = V_PACKET_NUMBER
AND MATERIAL_TYPES = V_MATERIAL_TYPES;
end if;
end if;
END LOOP;
CLOSE cur_query4;
commit;
END LOOP;
CLOSE cur_query3;
END LOOP;
CLOSE cur_query2;
END LOOP;
CLOSE cur_query;
end;
扫一扫
1384
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
