📄 insproc_insert_a1a1.sql
字号:
CREATE OR REPLACE PROCEDURE INSPROC_INSERT_A1A1(data_object DataObject,in_rowid in rowid) as
/*===================================================================================
PHILIPS DISPLAY COMPONENTS EINDHOVEN
PTE-CAM LARGE/JUMBO PROJECT TEAM
-----------------------------------------------------------------------------------
--
-- !!!!!! ATTENTION !!!!!!
--
-- THIS IS AN AUTOMATICALLY GENERATED FILE
--
-- !!!!!! ATTENTION !!!!!!
--
-----------------------------------------------------------------------------------
Object :Stored Procedure
Name :INSPROC_INSERT_RALU
Function :insert data into PRC_RALU and triggers insproc_update_count
Author :wang yong
Date :May, 2001
====================================================================================*/
ws_sqlerrm varchar2(512);
ws_sqlcode number(38);
ws_repcnt pls_integer;
update_rowid_load rowid;
update_rowid_unload rowid;
double_message_rowid rowid;
ws_rowid rowid;
where_am_i varchar2(25);
this_proc varchar2(30);
insert_object DataObject := data_object;
HOOK_rowid rowid;
A1_BUF_RECORD BUFPRC_TVTA1000%ROWTYPE;
LSERNO VARCHAR2(8);
BEGIN
this_proc := 'INSPROC_INSERT_A1A1';
insert_object.process_id :='PAGE';
insert_object.product_type :='29'; -- data received is 90
ws_rowid := in_rowid;
INSERT_OBJECT.NO_BARCODE_STRING :='99999998';
where_am_i:='11';
commit;
--------------------------------------------------------------------
--1. get the barcode.
SELECT * INTO A1_BUF_RECORD FROM BUFPRC_TVTA1000 WHERE ROWID=ws_rowid;
BEGIN
select serno INTO LSERNO from prc_HOOK
where HOOK = A1_BUF_RECORD.FILTEST_CARRIERNR and
line = A1_BUF_RECORD.HF_PROD_LINE;
exception
when NO_DATA_FOUND then
LSERNO :='99999998';
END;
insert_object.product_serial := lserno;
----------------------------------------------------------------------------
-- 2. if there is duplicate barcode, get its rowid.
where_am_i:='12';
if insert_object.product_serial <> insert_object.no_barcode_string then
begin
execute immediate ' select /*+(parallel, default, default)*/ rowid '||
' from PRC_PAGO '||
' where product_type = :a '||
' and product_serial = :b '||
' and '||insert_object.date_column||'>=:c'||
' and reprocess_count = 0'
into double_message_rowid
using insert_object.product_type,
insert_object.product_serial,
insert_object.date_extended-1/12;
exception
when NO_DATA_FOUND then
double_message_rowid := NULL;
end;
else
begin
execute immediate ' select /*+(parallel, default, default)*/ rowid '||
' from PRC_PAGO '||
' where '||insert_object.date_column||' = :a '||
' and factory_id = :b'||
' and line_id = :c '||
' and track_id = :d '||
' and product_serial = :e'||
' and rownum = 1'
into double_message_rowid
using insert_object.date_extended,
insert_object.factory_id,
insert_object.line_id,
insert_object.track_id,
insert_object.product_serial ;
exception
when NO_DATA_FOUND then
double_message_rowid := NULL;
end;
end if;
------------------------------------------------------------------
-- 3. if the msg has no info in the prc_hook table.(9999998) insert into prc_pag0 if without duplicate,
-- or update prc_pag0 if it has duplicate.
if insert_object.product_serial ='99999998' and A1_BUF_RECORD.FILAMENTTESTTUBEPRESENT=1 then
where_am_i:='15';
begin
--if double_message_rowid is NULL then
--BEGIN
INSERT_OBJECT.ERROR:='0000';
INSERT_OBJECT.OPERATOR_ID:='??????';
insert_object.destination_id :='??';
insert_object.btube := '0';
insert into prc_HOOK
values
(A1_BUF_RECORD.FILTEST_CARRIERNR,
A1_BUF_RECORD.PRODUCT_TYPE,
'99999999',
A1_BUF_RECORD.HF_PROD_LINE,
A1_BUF_RECORD.PROCESS_DATE,
A1_BUF_RECORD.PROCESS_TIME
);
commit;
insproc_update_count(insert_object,1);
where_am_i:='a';
EXECUTE IMMEDIATE 'INSERT /*+(parallel, default, default)*/ INTO PRC_PAGO( '||
' PRODUCT_TYPE, '||
' PRODUCT_SERIAL, '||
' FACTORY_ID, '||
' LINE_ID, '||
' TRACK_ID, '||
' PROCESS_ID, '||
' STARTTIME, '||
' PROCESS_DATE, '||
' PROCESS_TIME, '||
' ENDTIME, '||
' IDCARRIER, '||
' MODULETESTCARRIERDEFECT, '||
' MODULETESTCONTINUITY, '||
' MODULETESTINSULATION, '||
' MODULETESTFILAMENT, '||
' MODULETESTFUSEDEFECT, '||
' FILAMENTTESTTUBEPRESENT, '||
' FILAMENTTESTSHORTCIRCUIT, '||
' FILAMENTTESTFUSEDEFECT, '||
' FILAMENTTESTBADTRANSFORMER, '||
' FILAMENTTESTTUBENOTCONNECTED, '||
' OPERATOR_ID, '||
' REPROCESS_COUNT ) '||
'SELECT /*+(parallel, default, default)*/'||
' :a, '||
' PRODUCT_SERIAL, '||
' :b, '||
' :c || to_char(HF_PROD_LINE), '||
' :d, '||
' :e, '||
' oracle_date, '||
' PROCESS_DATE, '||
' PROCESS_TIME, '||
' oracle_date, '||
' to_char(FILTEST_CARRIERNR), '||
' MODULETESTCARRIERDEFECT, '||
' MODULETESTCONTINUITY, '||
' MODULETESTINSULATION, '||
' MODULETESTFILAMENT, '||
' MODULETESTFUSEDEFECT, '||
' FILAMENTTESTTUBEPRESENT, '||
' FILAMENTTESTSHORTCIRCUIT, '||
' FILAMENTTESTFUSEDEFECT, '||
' FILAMENTTESTBADTRANSFORMER, '||
' FILAMENTTESTTUBENOTCONNECTED, '||
' :f, '||
' 0 '||
' FROM BUFPRC_TVTA1000 '||
' WHERE rowid = :g '
USING insert_object.product_type,'02','0','00',insert_object.process_id,insert_object.operator_id, ws_rowid;
where_am_i := 'p';
EXECUTE IMMEDIATE ' DELETE FROM BUFPRC_TVTA1000 '||
' WHERE rowid = :a ' USING ws_rowid;
commit;
END;
----12345678
ELSIF double_message_rowid is NULL and A1_BUF_RECORD.FILAMENTTESTTUBEPRESENT=1 then
if insert_object.product_serial <> insert_object.no_barcode_string then
select /*+(parallel, default, default)*/ count(*)into ws_repcnt from PRC_PAGO
where product_type = insert_object.product_type
and product_serial = insert_object.product_serial;
if ws_repcnt != 0 then
if ws_repcnt >= 255 then
ws_repcnt := 255;
end if;
update /*+(parallel, default, default)*/ PRC_PAGO
set reprocess_count = ws_repcnt
where product_type = insert_object.product_type
and product_serial = insert_object.product_serial
and reprocess_count = 0;
if (ws_repcnt > 3) then
insproc_sfc_overprocess (insert_object,ws_repcnt);
end if;
end if;
end if;
INSERT_OBJECT.ERROR:='0000';
INSERT_OBJECT.OPERATOR_ID:='??????';
insert_object.destination_id :='??';
insert_object.btube := '0';
insproc_update_count(insert_object,1);
where_am_i:='a';
EXECUTE IMMEDIATE 'INSERT /*+(parallel, default, default)*/ INTO PRC_PAGO( '||
' PRODUCT_TYPE, '||
' PRODUCT_SERIAL, '||
' FACTORY_ID, '||
' LINE_ID, '||
' TRACK_ID, '||
' PROCESS_ID, '||
' STARTTIME, '||
' PROCESS_DATE, '||
' PROCESS_TIME, '||
' ENDTIME, '||
' IDCARRIER, '||
' MODULETESTCARRIERDEFECT, '||
' MODULETESTCONTINUITY, '||
' MODULETESTINSULATION, '||
' MODULETESTFILAMENT, '||
' MODULETESTFUSEDEFECT, '||
' FILAMENTTESTTUBEPRESENT, '||
' FILAMENTTESTSHORTCIRCUIT, '||
' FILAMENTTESTFUSEDEFECT, '||
' FILAMENTTESTBADTRANSFORMER, '||
' FILAMENTTESTTUBENOTCONNECTED, '||
' OPERATOR_ID, '||
' REPROCESS_COUNT ) '||
'SELECT /*+(parallel, default, default)*/'||
' :a, '||
' :b, '||
' :c, '||
' :d || to_char(HF_PROD_LINE), '||
' :e, '||
' :f, '||
' oracle_date, '||
' PROCESS_DATE, '||
' PROCESS_TIME, '||
' oracle_date, '||
' to_char(FILTEST_CARRIERNR), '||
' MODULETESTCARRIERDEFECT, '||
' MODULETESTCONTINUITY, '||
' MODULETESTINSULATION, '||
' MODULETESTFILAMENT, '||
' MODULETESTFUSEDEFECT, '||
' FILAMENTTESTTUBEPRESENT, '||
' FILAMENTTESTSHORTCIRCUIT, '||
' FILAMENTTESTFUSEDEFECT, '||
' FILAMENTTESTBADTRANSFORMER, '||
' FILAMENTTESTTUBENOTCONNECTED, '||
' :g, '||
' 0 '||
' FROM BUFPRC_TVTA1000 '||
' WHERE rowid = :g '
USING insert_object.product_type,insert_object.product_serial,'02','0','00',insert_object.process_id,insert_object.operator_id, ws_rowid;
where_am_i := 'p';
EXECUTE IMMEDIATE ' DELETE FROM BUFPRC_TVTA1000 '||
' WHERE rowid = :a ' USING ws_rowid;
commit;
ELSE
where_am_i:='16';
EXECUTE IMMEDIATE ' UPDATE /*+(parallel, default, default)*/ PRC_PAGO '||
' SET ( '||
' MODULETESTCARRIERDEFECT, '||
' MODULETESTCONTINUITY, '||
' MODULETESTINSULATION, '||
' MODULETESTFILAMENT, '||
' MODULETESTFUSEDEFECT, '||
' FILAMENTTESTTUBEPRESENT, '||
' FILAMENTTESTSHORTCIRCUIT, '||
' FILAMENTTESTFUSEDEFECT, '||
' FILAMENTTESTBADTRANSFORMER, '||
' FILAMENTTESTTUBENOTCONNECTED) = '||
' ( SELECT /*+(parallel, default, default)*/'||
' MODULETESTCARRIERDEFECT, '||
' MODULETESTCONTINUITY, '||
' MODULETESTINSULATION, '||
' MODULETESTFILAMENT, '||
' MODULETESTFUSEDEFECT, '||
' FILAMENTTESTTUBEPRESENT, '||
' FILAMENTTESTSHORTCIRCUIT, '||
' FILAMENTTESTFUSEDEFECT, '||
' FILAMENTTESTBADTRANSFORMER, '||
' FILAMENTTESTTUBENOTCONNECTED '||
' FROM BUFPRC_TVTA1000 WHERE rowid = :a ) '||
' WHERE rowid = :b ' USING ws_rowid,double_message_rowid;
EXECUTE IMMEDIATE ' DELETE FROM BUFPRC_TVTA1000 '||
' WHERE rowid = :a ' USING ws_rowid;
commit;
END IF;
-- END IF;
-----------------------------------------------------------------------------------------------------
exception
when others then
rollback;
execute immediate 'update /*+(parallel, default, default)*/ '||insert_object.buffer_table||
'set read_error = 1 '||
'where rowid = :a ' using ws_rowid;
ws_sqlerrm := rpad(sqlerrm,512);
ws_sqlcode := sqlcode;
insert /*+(parallel, default, default)*/ into prc_errorlog values(sysdate, insert_object.process_id, where_am_i||','||this_proc, ws_sqlerrm, ws_sqlcode);
commit;
end;
/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -