nonlotcrn_1.sql

SET SERVEROUTPUT ON SIZE 99999; DECLARE   ERRMSG VARCHAR2(300); BEGIN

begin insert into nonlotdata@MISC(eqpid,dcspecid,variable,operatorid,time,    schedulecode,commentcode,cmt,data,datecode) select eqpid,dcspecid,variable,operatorid,time,     schedulecode,commentcode,cmt,data,to_char(time,'yyyymmdd') from nonlotdata where (sysdate-time) < 0.5; commit; exception     when others then         ERRMSG := 'ERROR = ' ||TO_CHAR(SQLCODE)||'  '||SUBSTR(SQLERRM,1,200);         insert into maillog (sender,sendto,contenttype,subject,body) values ('DREAMS_DB','PQMES@pro-q.com.cn','text','NONLOT DATACOLL CRON : insert MISC nonlotdata exception.',ERRMSG);         commit; end;

begin insert into newap.tmp_nonlotdata(eqpid,dcspecid,variable,operatorid,time,    schedulecode,commentcode,cmt,data) select eqpid,dcspecid,variable,operatorid,time,     schedulecode,commentcode,cmt,data from nonlotdata where (sysdate-time) < 0.5; commit;

delete NonLotData where (sysdate - Time) > 0.5; commit; end;

begin update nonlotdcspec set islast = 'T' where islast = 'Y';

update nonlotdcspec set islast = 'Y' where islast = 'N';

update nonlotdcspec set islast = 'N', Status   = 'UNDO', OperatorId = '', CommentCode = '', Cmt  = '', Time  = NULL where islast = 'T'; commit; end;

begin insert into nonlotdcspec@MISC(DATECODE,EQPID,DCSPECID,ISLAST,SHIFT,SPECDESC,       STARTTIME,PERIOD,STATUS,OPERATORID,COMMENTCODE,CMT,TIME,TA_SHIFT) select to_char(sysdate-1,'yyyymmdd'),eqpid,DCSPECID,ISLAST,SHIFT,SPECDESC,       STARTTIME,PERIOD,STATUS,OPERATORID,COMMENTCODE,CMT,TIME,'N' from nonlotdcspec where status in ('NO-PASS','UNDO') and islast='Y'       and ( mod((sysdate+0.1-starttime),period)*24 > decode(period,0.5,0,12) )       and ( mod((sysdate+0.1-starttime),period)*24 < decode(period,0.5,12,24) ); commit; exception     when others then         ERRMSG := 'ERROR = ' ||TO_CHAR(SQLCODE)||'  '||SUBSTR(SQLERRM,1,200);         insert into maillog (sender,sendto,contenttype,subject,body) values ('DREAMS_DB','PQMES@pro-q.com.cn','text','NONLOT DATACOLL CRON : insert MISC nonlotdcspec exception.',ERRMSG);         commit; end;

begin insert into newap.tmp_nonlotdcspec(EQPID,DCSPECID,ISLAST,SHIFT,SPECDESC,       STARTTIME,PERIOD,STATUS,OPERATORID,COMMENTCODE,CMT,TIME) select eqpid,DCSPECID,ISLAST,SHIFT,SPECDESC,       STARTTIME,PERIOD,STATUS,OPERATORID,COMMENTCODE,CMT,TIME from nonlotdcspec where status in ('NO-PASS','UNDO') and islast='Y'       and ( mod((sysdate+0.1-starttime),period)*24 > decode(period,0.5,0,12) )       and ( mod((sysdate+0.1-starttime),period)*24 < decode(period,0.5,12,24) ); commit; end;

sp_update_ta_shift(sysdate - 0.1 ); commit;

END;

/

exit;