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;
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
