plsqlexample

CREATE OR REPLACE PROCEDURE P_GetandCheck_material_record( t_start_time DATE, --(必须要有)
t_end_time DATE, -- sysdate
v_mo_number VARCHAR2, --(必须要有) '110008895SCVCD-SASA'
v_line_name VARCHAR2, --(必须要有) 'S1'
v_Bubble_number VARCHAR2, --(必须要有) for route
v_location NUMBER --没有分料则为0
) IS
/*
t_start_time 出错的时间
t_end_time 恢复到什么时间
v_mo_number 工单号
v_line_name LINE名称
v_Bubble_number BOM中的PN所在的途程
v_location 没有分料则为0,有分料写location number

v_Error_KP 途程中有问题的物料
v_Standard_KP MODEL下正确的，并且用料数为1的KP
v_replaced_pn_count BOM下可以替换物料的总个数
v_pn_have_many_locatin 是否有分料
v_kp_count 需用料件总数
*/

--DEFINE the variant
v_Error_KP VARCHAR2(18);
v_Standard_KP VARCHAR2(40);
v_replaced_pn_count NUMBER;
v_model_name VARCHAR2(40);
v_kp_count NUMBER;
v_group_name VARCHAR2(40);
v_location_number NUMBER;
v_numerator NUMBER;
v_denominator NUMBER;

Begin
-------Step0 得到相关变量
--得到 Model --
SELECT Parent,group_name,kp_count,key_part_no into v_model_name,v_group_name,v_kp_count,v_Error_KP
FROM sfis1.c_mo_bom_keypart_t mbs
WHERE mbs.mo_number = v_mo_number
AND mbs.bubble_number = v_Bubble_number;

--得到替代料的总数
SELECT count(*) into v_replaced_pn_count from sfis1.c_bom_structure_t bs
where bs.parent =v_model_name
and bs.bubble_number = v_Bubble_number;

if v_replaced_pn_count = 1 then --如果此PN没有可替代料
begin
--Step1 查找LOCATION_NUMBER

if v_location = 0 then --v_location = 0为不考虑分料情况
SELECT lg.location into v_location_number
FROM sfism4.r_smt_msl_log_t lg
WHERE lg.line_name = v_line_name
and lg.model_name = v_model_name
-- and lg.rownum<2
and lg.in_time > t_start_time
and trim('#' from substr(this_kp_no,1,18)) = v_Error_KP;
elsif v_location > 0 then
SELECT lg.location into v_location_number FROM sfism4.r_smt_msl_log_t lg
WHERE lg.line_name = v_line_name
and lg.model_name = v_model_name
and lg.location = v_location
-- and lg.rownum<2
and lg.in_time > t_start_time
and trim('#' from substr(this_kp_no,1,18)) = v_Error_KP;
end if;

--删除有数据的sfism4.r_work_tmp_t临时表
--step2
delete from sfism4.r_work_tmp_t;
COMMIT;

--step3 插入需要消耗物料的条码到临时表中
insert into sfism4.r_work_tmp_t
select v_location_number,mmqty.sn1,mmtime.mtime,mmqty,0,0 from
( select sn1,sum(qty) mmqty from
( select sn1, qty-lala.use_qty qty
from SFISM4.r_Material_Tracking_t lala
where sn1 in
(select distinct this_kp_no
from (select *
from SFISM4.r_Smt_Msl_Log_t lot
where lot.line_name = v_line_name
and lot.model_name = v_model_name
and lot.location = v_location_number
and lot.in_time > t_start_time
and trim('#' from substr(this_kp_no,1,18)) = v_Error_KP

) masn)

union all
select sn1, sum(kp_used_qty) qty
from SFISM4.r_Mr_Wip_Keyparts_t mrkt
where mrkt.transaction_date > t_start_time
and mrkt.sn1 in
(select distinct this_kp_no
from (select *
from SFISM4.r_Smt_Msl_Log_t lot
where lot.line_name = v_line_name
and lot.location = v_location_number
and lot.model_name = v_model_name
and lot.in_time > t_start_time
and trim('#' from substr(this_kp_no,1,18)) = v_Error_KP

) )
group by sn1
) group by sn1
)mmqty,(select this_kp_no,max(in_time) mtime from
(SELECT DISTINCT lg.location, lg.in_time, lg.this_kp_no, lg.cmd,lg.line_name
FROM sfism4.r_smt_msl_log_t lg
WHERE lg.line_name = v_line_name
and lg.location = v_location_number
and lg.model_name = v_model_name
and lg.in_time > t_start_time
and (trim('#' from substr(this_kp_no,1,18)) = v_Error_KP )

ORDER BY lg.location, lg.in_time) getmaxtime where this_kp_no<>'N/A'
Group by this_kp_no) mmtime
Where mmqty.sn1 = mmtime.this_kp_no;
COMMIT;
--step4
-- 删除SFISM4.TP_R_MR_WIP_KEYPARTS_T表中记录前,先做保存
INSERT INTO SFISM4.TP_R_MR_WIP_KEYPARTS_T
Select * FROM sfism4.r_mr_wip_keyparts_t wk
WHERE EXISTS (SELECT *
FROM sfism4.r_sn_parent_detail_t sn
WHERE sn.serial_number = sn.serial_number
AND sn.group_name = v_group_name
and sn.line_name =v_line_name)
AND wk.transaction_date >= t_start_time
and wk.mo_number = v_mo_number
AND (trim('#' from substr(SN1,1,18)) = v_Error_KP);
COMMIT;
--step5
-- 删除SFISM4.TP_R_MR_WIP_KEYPARTS_T表中记录
DELETE FROM sfism4.r_mr_wip_keyparts_t wk
WHERE EXISTS (SELECT *
FROM sfism4.r_sn_parent_detail_t sn
WHERE sn.serial_number = sn.serial_number
AND sn.group_name = v_group_name
and sn.line_name =v_line_name)
AND wk.transaction_date >= t_start_time
and wk.mo_number = v_mo_number
AND (trim('#' from substr(SN1,1,18)) = v_Error_KP);
COMMIT;

end;
elsif v_replaced_pn_count >1 then --如果此PN有可替代料
begin --建立临时表,为了解决有替代料的问题

Delete from sfism4.tp_r_material_tracking_t;

insert into sfism4.tp_r_material_tracking_t(KEY_PART_NO)
select t.child from c_bom_structure_t t
where t.parent =v_model_name
and t.bubble_number = v_Bubble_number;
commit;

--Step1 查找LOCATION_NUMBER
if v_location = 0 then --location为0表示不管分料
SELECT lg.location into v_location_number FROM sfism4.r_smt_msl_log_t lg
WHERE lg.line_name = v_line_name
and lg.model_name = v_model_name
and rownum<2
and lg.in_time > t_start_time
and (trim('#' from substr(this_kp_no,1,18)) = v_Error_KP
);
elsif v_location > 0 then
begin
SELECT lg.location into v_location_number
FROM sfism4.r_smt_msl_log_t lg
WHERE lg.line_name = v_line_name
and lg.model_name = v_model_name
and lg.location = v_location
and rownum<2
and lg.in_time > t_start_time
and trim('#' from substr(this_kp_no,1,18)) = v_Error_KP;
end;
end if;

--DELETE all data and INSERT NEW DATA INTO sfism4.r_work_tmp_t
--step2
delete from sfism4.r_work_tmp_t;
COMMIT;
--step3 插入需要消耗物料的条码到临时表中
insert into sfism4.r_work_tmp_t
select v_location_number,mmqty.sn1,mmtime.mtime,mmqty,0,0 from
( select sn1,sum(qty) mmqty from
( select sn1, qty-lala.use_qty qty
from SFISM4.r_Material_Tracking_t lala
where sn1 in
(select distinct this_kp_no
from (select *
from SFISM4.r_Smt_Msl_Log_t lot, sfism4.tp_r_material_tracking_t
where lot.line_name = v_line_name
and lot.model_name = v_model_name
and lot.location = v_location_number
and lot.in_time > t_start_time
and trim('#' from substr(lot.this_kp_no,1,18)) = sfism4.tp_r_material_tracking_t.KEY_PART_NO
) masn)

union all
select sn1, sum(kp_used_qty) qty
from SFISM4.r_Mr_Wip_Keyparts_t mrkt
where mrkt.transaction_date > t_start_time
and mrkt.sn1 in
(select distinct this_kp_no
from (select *
from SFISM4.r_Smt_Msl_Log_t lot,sfism4.tp_r_material_tracking_t
where lot.line_name = v_line_name
and lot.location = v_location_number
and lot.model_name = v_model_name
and lot.in_time > t_start_time
and trim('#' from substr(this_kp_no,1,18)) = sfism4.tp_r_material_tracking_t.KEY_PART_NO

) )
group by sn1
) group by sn1
)mmqty,(select this_kp_no,max(in_time) mtime from
(SELECT DISTINCT lg.location, lg.in_time, lg.this_kp_no, lg.cmd,lg.line_name
FROM sfism4.r_smt_msl_log_t lg, sfism4.tp_r_material_tracking_t
WHERE lg.line_name = v_line_name
and lg.location = v_location_number
and lg.model_name = v_model_name
and lg.in_time > t_start_time
and (trim('#' from substr(this_kp_no,1,18)) = sfism4.tp_r_material_tracking_t.KEY_PART_NO )

ORDER BY lg.location, lg.in_time) getmaxtime where this_kp_no<>'N/A'
Group by this_kp_no) mmtime
Where mmqty.sn1 = mmtime.this_kp_no;
COMMIT;
--Step4
-- 删除SFISM4.TP_R_MR_WIP_KEYPARTS_T表中记录前,先做保存
INSERT INTO SFISM4.TP_R_MR_WIP_KEYPARTS_T
Select wk.* FROM sfism4.r_mr_wip_keyparts_t wk, sfism4.tp_r_material_tracking_t tp
WHERE EXISTS (SELECT *
FROM sfism4.r_sn_parent_detail_t sn
WHERE sn.serial_number = sn.serial_number
AND sn.group_name = v_group_name
and sn.line_name =v_line_name)
AND wk.transaction_date >= t_start_time
and wk.mo_number = v_mo_number
-- AND wk.SN1 = tp.KEY_PART_NO;
AND trim('#' from substr(wk.SN1,1,18)) = tp.KEY_PART_NO;
COMMIT;
--Step5
-- 删除SFISM4.TP_R_MR_WIP_KEYPARTS_T表中记录
DELETE FROM sfism4.r_mr_wip_keyparts_t wk --,TP_mo_bom_keypart_t
WHERE EXISTS (SELECT *
FROM sfism4.r_sn_parent_detail_t sn --
WHERE sn.serial_number = sn.serial_number
AND sn.group_name = v_group_name
and sn.line_name =v_line_name)
AND wk.transaction_date >= t_start_time
and wk.mo_number = v_mo_number
AND trim('#' from substr(SN1,1,18)) in (select KEY_PART_NO from sfism4.tp_r_material_tracking_t );
-- AND (trim('#' from substr(SN1,1,18)) = TP_mo_bom_keypart_t.KEY_PART_NO);
COMMIT;

end;
END IF;

--step6
--如果有分料,在这取得分料的分子和分母
if v_location = 0 then
begin
v_numerator := v_kp_count;
v_denominator := 1;
end;
elsif v_location > 0 then
begin
--对于有些分料中kp_qty_desc栏位没有"/" 的,用以下这两条
v_numerator := v_kp_count;
v_denominator := 1;

--在这里设计到分料时,如果kp_qty_desc栏位有"/",做以下处理
select TO_NUMBER(SUBSTR(kp_qty_desc,1,INSTR(kp_qty_desc,'/',1,1)-1)) D1 ,
TO_NUMBER(SUBSTR(kp_qty_desc,INSTR(kp_qty_desc,'/',1,1)+1,LENGTH(kp_qty_desc)-INSTR(kp_qty_desc,'/',1,1))) d2
into v_numerator, v_denominator
from sfism4.r_smt_msl_detail_t dt,
(select r_seq from ( select R_SEQ from sfism4.r_smt_msl_master_t dt
where dt.model_name = v_model_name and line_name=v_line_name
order by msl_ver desc) tt where rownum <3) mt
where dt.r_seq=mt.r_seq and kp_no=v_Error_KP
and INSTR(dt.kp_qty_desc,'/',1,1)>0
and dt.location=v_location_number;

end;
end if;

--step7
--执行真正扣料的存储过程
SFIS1.P_modify_material_record(v_location_number,t_start_time,t_end_time,v_Error_KP,v_Standard_KP,v_mo_number,v_model_name,v_line_name,v_group_name,v_kp_count,v_numerator,v_denominator );
--step8
--删除已经用完的
insert into sfism4.tp_r_mr_wip_keyparts_t select * from sfism4.r_mr_wip_keyparts_t t where sn1 in (select sn1 from SFISM4.r_work_tmp_t where is_up=1);
COMMIT;
Delete from SFISM4.r_Material_Tracking_t where sn1 in (select sn1 from SFISM4.r_work_tmp_t where is_up=1);
COMMIT;

end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/93029/viewspace-1027998/，如需转载，请注明出处，否则将追究法律责任。