1.
create or replace procedure PROC_CHECK(Insert_by VARCHAR) IS
v_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)
is
begin
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) IS
BEGIN
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;
