1.

create or replace procedure PROC_CHECK(Insert_by VARCHAR) ISv_OID VARCHAR2(36);begin  declare       --类型定义       cursor c_check       is       select dep_oid,oms_user_oid       from dcjetframework.v_oms_user;       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型       c_row c_check%rowtype;begin       for c_row in c_check loop        -- dbms_output.put_line(c_row.dep_oid||'-'||c_row.oms_user_oid);        v_OID:=sys_guid();        INSERT INTO T_OMS_DEVICE_CHECK(OID,DEPT_OID,User_Oid,INSERT_BY)        VALUES(v_OID,c_row.dep_oid,c_row.oms_user_oid,Insert_by);               INSERT INTO t_oms_device_check_detail(check_oid,device_oid,insert_by)              SELECT v_OID,OID,Insert_by                 FROM v_oms_check WHERE Dep_Oid=c_row.dep_oid AND APPLICANT=c_row.oms_user_oid ;                               end loop;       COMMIT;end;

2.

create or replace procedure proc_approve(P_OID IN Varchar2, P_Insert_By IN Varchar2,P_operation_type VARCHAR2) is v_stg Varchar2(50);BEGIN  IF P_operation_type='11' THEN       BEGIN            SELECT t.WAREHOUSE_OID INTO v_stg FROM T_OMS_DEVICE_REGIST_LOG t WHERE t.DEVICE_OID=P_OID AND t.operation_type='13' AND ROWNUM=1            ORDER BY t.INSERT_time DESC;                 UPDATE  T_OMS_DEVICE_REGIST t SET t.scrapping_status='2',t.warehouse_oid=v_stg,t.status='9',t.SCRAPED_TIME=SYSDATE WHERE t.OID=P_OID;            INSERT INTO T_OMS_DEVICE_REGIST_LOG VALUES( sys_guid(),P_OID,P_operation_type,'报废审批通过',v_stg,Sysdate,'',P_Insert_By,Sysdate);            COMMIT;         END;  ELSIF P_operation_type='12' THEN    BEGIN       UPDATE  T_OMS_DEVICE_REGIST t SET t.scrapping_status='3' WHERE t.OID=P_OID;       INSERT INTO T_OMS_DEVICE_REGIST_LOG VALUES( sys_guid(),P_OID,P_operation_type,'报废审批不通过','',Sysdate,'',P_Insert_By,Sysdate);        COMMIT;      END;   END IF;      end proc_approve;

3.

create or replace procedure proc_check_confirm(check_oid VARCHAR2) isbegin  UPDATE t_oms_device_check SET confirm_satus='1',CONFIRM_TIME=SYSDATE WHERE OID=check_oid;end proc_check_confirm;

4.

create or replace procedure PROC_DESTROY_DELETE(p_headOid VARCHAR2,p_result OUT VARCHAR2) ISBEGIN  SELECT status INTO p_result  FROM  t_oms_device_destroy WHERE OID=p_headOid AND rownum=1;  IF p_result='2' THEN    BEGIN      RETURN;    END;  END IF;  p_result:='1';  DELETE FROM t_oms_device_destroy WHERE OID=p_headOid;  UPDATE t_oms_device_regist SET STATUS='9' , DESTROY_OID='' WHERE destroy_oid=p_headOid;  DELETE FROM t_oms_device_regist_attachment WHERE device_oid=p_headOid;  COMMIT;end PROC_DESTROY_DELETE;

5.

create or replace function get_Destroy_no return varchar2 is  Result varchar2(50);begin  SELECT max(destroy_no) INTO RESULT FROM t_oms_device_destroy WHERE   substr(destroy_no,0,8)= to_char( SYSDATE  ,'yyyymmdd'); IF (RESULT =''OR RESULT IS NULL) THEN            RESULT:=(to_char( SYSDATE  ,'yyyymmdd')||'001');                else   RESULT:=to_number(RESULT)+1;END IF;        RETURN( Result);  end get_Destroy_no;

 6.

create or replace procedure Proc_BillSummary_Cal_Operate(V_Owner IN VARCHAR2,V_STARDATE VARCHAR2,V_ENDDATE  VARCHAR2) is v_stg Varchar2(50);BEGINdelete from T_XMLC_THE_LIBRARY_SUMMARY where INSERTER=V_owner;  insert into T_XMLC_THE_LIBRARY_SUMMARY(  LIST_NO,  G_NO ,  G_EXG_NO ,  HS_CODE ,  G_NAME_CN ,  QTY ,  G_CURR ,  EMS_NO ,  OUT_LIST_NO ,  BILL_TYPE ,  OUT_G_NO,  OUT_G_EXG_NO ,  OUT_QTY ,  OUT_G_CURR,  INSERTER  )select t1.list_no,t1.g_no,t1.G_EXG_NO,t1.hs_code,t1.g_name_cn,t1.qty,t1.g_curr,(select ems_no from T_XMLC_BILL_head_BAK where list_no=t1.list_no) EMS_NO, B.LIST_NO out_list_no,A.BILL_TYPE,b.g_no out_g_no,b.g_exg_no out_g_exg_no,b.qty out_qty,b.g_curr out_g_curr,V_Owner from T_XMLC_BILL_LIST_BAK t1,T_XMLC_BILL_HEAD_BAK A, T_XMLC_BILL_LIST_BAK B where  A.LIST_NO = B.LIST_NO AND A.BILL_TYPE='1' and t1.list_no=b.i_list_no and t1.g_no=b.g_noand A.APPROVAL_DATE >=TO_DATE(V_STARDATE,'yyyy-mm-dd hh24:mi:ss') and A.APPROVAL_DATE <=TO_DATE(V_ENDDATE,'yyyy-mm-dd hh24:mi:ss')union allselect t1.list_no,t1.g_no,t1.G_EXG_NO,t1.hs_code,t1.g_name_cn,t1.qty,t1.g_curr,(select ems_no from T_XMLC_BILL_head_BAK where list_no=t1.list_no) EMS_NO, B.LIST_NO  out_list_no,'4' BILL_TYPE,b.g_no out_g_no,b.g_exg_no out_g_exg_no,b.qty out_qty,b.g_curr out_g_curr,V_Owner from T_XMLC_BILL_LIST_BAK t1,T_XMLC_PROCESS_HEAD_BAK A, T_XMLC_PROCESS_LIST_BAK B where  A.LIST_NO = B.LIST_NO  and t1.list_no=b.i_list_no and t1.g_no=b.g_noand A.APPROVAL_DATE >=TO_DATE(V_STARDATE,'yyyy-mm-dd hh24:mi:ss') and A.APPROVAL_DATE <=TO_DATE(V_ENDDATE,'yyyy-mm-dd hh24:mi:ss')union allselect t1.list_no,t1.g_no,t1.G_EXG_NO,t1.hs_code,t1.g_name_cn,t1.qty,t1.g_curr,(select ems_no from T_XMLC_BILL_head_BAK where list_no=t1.list_no) EMS_NO,B.LIST_NO  out_list_no,'5' BILL_TYPE,c.g_no out_g_no,b.g_exg_no out_g_exg_no,b.qty out_qty,b.g_curr out_g_curr,V_Owner from T_XMLC_BILL_LIST_BAK t1,T_XMLC_STORAGE_FOCUS_HEAD_BAK A, T_XMLC_STORAGE_FOCUS_LIST_BAK B,t_xmlc_batch_out_list_bak C,t_xmlc_batch_out_head_bak D where C.SEND_LIST_NO=D.LIST_NO AND C.ST_OUT_LIST_NO=C.SEND_LIST_NO and A.LIST_NO = B.LIST_NO  and t1.list_no= C.I_LIST_NO and t1.g_no=c.g_noand A.APPROVAL_DATE >=TO_DATE(V_STARDATE,'yyyy-mm-dd hh24:mi:ss') and A.APPROVAL_DATE <=TO_DATE(V_ENDDATE,'yyyy-mm-dd hh24:mi:ss');commit;end Proc_BillSummary_Cal_Operate;

 或者：

CREATE OR REPLACE PROCEDURE PROC_BILLSUMMARY_CAL_OPERATE(  V_OWNER    IN VARCHAR2,  V_STARDATE VARCHAR2,  V_ENDDATE  VARCHAR2) IS  V_STG VARCHAR2(50);BEGIN  DELETE FROM T_XMLC_THE_LIBRARY_SUMMARY   WHERE INSERTER = V_OWNER;  INSERT INTO T_XMLC_THE_LIBRARY_SUMMARY    (OID, LIST_NO, G_NO, G_EXG_NO, HS_CODE, G_NAME_CN, QTY, G_CURR, EMS_NO,     c_OID, c_LIST_NO, BILL_TYPE,c_G_NO, c_G_EXG_NO, c_QTY,     c_G_CURR, INSERTER)     --进境     select         T1.OID, T1.LIST_NO, t2.G_NO, t2.G_EXG_NO, t2.HS_CODE,           t2.G_NAME_CN, t2.QTY, t2.G_CURR,           (SELECT EMS_NO               FROM T_XMLC_BILL_HEAD_PRE              WHERE LIST_NO = T1.LIST_NO) EMS_NO,t3.OID,           t3.LIST_NO , '4' BILL_TYPE, t3.G_NO ,           t3.G_EXG_NO , t3.QTY , t3.G_CURR ,           V_OWNER          from  T_XMLC_BILL_HEAD_PRE t1                  inner join T_XMLC_BILL_LIST_PRE t2  on t1.list_no=t2.list_no  and t1.bill_type='0' and t1.chk_mark='2'                  inner join (    --出境   select  A1.OID,           B1.LIST_NO , '1' BILL_TYPE, B1.G_NO ,           B1.G_EXG_NO , B1.QTY , B1.G_CURR ,b1.i_list_no from  T_XMLC_BILL_HEAD_PRE a1, T_XMLC_BILL_LIST_PRE b1           where a1.chk_mark='1' and a1.list_no=b1.list_no and ( a1.bill_type='1' or a1.bill_type='2') and a1.APPROVAL_DATE >= TO_DATE(V_STARDATE, 'YYYY-MM-DD HH24:MI:SS')       AND a1.APPROVAL_DATE <= TO_DATE(V_ENDDATE, 'YYYY-MM-DD HH24:MI:SS')  union all      --简单加工   select  a2.OID,           b2.LIST_NO , '4' BILL_TYPE, b2.G_NO ,           b2.G_EXG_NO , b2.QTY , b2.G_CURR ,b2.i_list_no from  T_XMLC_PROCESS_HEAD_PRE a2, T_XMLC_PROCESS_LIST_PRE b2           where a2.chk_mark='2' and a2.list_no=b2.list_no  and a2.APPROVAL_DATE >= TO_DATE(V_STARDATE, 'YYYY-MM-DD HH24:MI:SS')       AND a2.APPROVAL_DATE <= TO_DATE(V_ENDDATE, 'YYYY-MM-DD HH24:MI:SS')union all       --出库集报    select  a3.OID,           b3.LIST_NO , '5' BILL_TYPE, b3.G_NO ,           b3.G_EXG_NO , b3.QTY , b3.G_CURR ,a32.i_list_no from  T_XMLC_STORAGE_FOCUS_HEAD_PRE a3, T_XMLC_STORAGE_FOCUS_LIST_PRE b3,            T_XMLC_BATCH_OUT_LIST_PRE a32,T_XMLC_BATCH_OUT_HEAD_PRE b32           where a3.chk_mark='2' and b32.chk_mark='2' and a3.list_no=b3.list_no and a32.st_out_list_no=b32.list_no and           a3.APPROVAL_DATE >= TO_DATE(V_STARDATE, 'YYYY-MM-DD HH24:MI:SS')       AND a3.APPROVAL_DATE <= TO_DATE(V_ENDDATE, 'YYYY-MM-DD HH24:MI:SS') and a32.ST_OUT_OID=b3.oid            ) t3  on t3.i_list_no=t1.list_no and t3.G_NO=t2.g_no ;  COMMIT;END PROC_BILLSUMMARY_CAL_OPERATE;