CREATE OR REPLACE PROCEDURE xjp_sp_autohold_greencard1AS v_TARGETVALUE NUMBER; v_val NVARCHAR2(40);BEGIN SELECT TARGETVALUE INTO v_TARGETVALUE FROM characteristic c WHERE c.characteristic='Therehold_AutoHold_GreenCard' AND c.ACTIVE=1; UPDATE LOT_NO L SET L.OBJECTCLASS='N' WHERE l.expirationdate =trunc(SYSDATE)+v_TARGETVALUE AND l.expirationdate >SYSDATE ; INSERT INTO xjp_t_audit_admin(PERFORMEDBY,TYPE,PERFORMEDON,OLDVALUE,NEWVALUE,CREATEDON,CREATEDBY) SELECT 'SYSTEM','QUAL STATUS'||P.PRODUCTNO||L.LOTNO,sysdate,'release','hold',sysdate,'SYSTEM' FROM lot_no l,PRODUCT p WHERE l.expirationdate <=SYSDATE+v_TARGETVALUE AND l.expirationdate>SYSDATE AND l.productid=p.id; END;CREATE OR REPLACE PROCEDURE xjp_sp_autohold_greencardAS v_TARGETVALUE NUMBER;BEGIN SELECT TARGETVALUE INTO v_TARGETVALUE FROM characteristic c WHERE c.characteristic='Therehold_AutoHold_GreenCard' AND c.ACTIVE=1; DECLARE v_val NVARCHAR2(40); v_lotno NVARCHAR2(40); v_productid NVARCHAR2(40); v_productno NVARCHAR2(40); CURSOR c_cursor IS select l.lotno, l.productid, l.expirationdate ,p.productno from lot_no l INNER JOIN product p on l.productid=p.id; BEGIN OPEN c_cursor; LOOP FETCH c_cursor INTO v_lotno,v_productid,v_val,v_productno; EXIT WHEN c_cursor%NOTFOUND; IF v_val = trunc(sysdate) + v_TARGETVALUE THEN UPDATE LOT_NO L SET L.OBJECTCLASS='N' where lotno=v_lotno and productid=v_productid; INSERT INTO xjp_t_audit_admin(PERFORMEDBY,TYPE,PERFORMEDON,OLDVALUE,NEWVALUE,CREATEDON,CREATEDBY)values('SYSTEM','QUAL STATUS'||v_productno|| v_lotno,sysdate,'release','hold',sysdate,'SYSTEM' ); END IF; END loop; CLOSE c_cursor; END; END;
