ORACLE 存儲(chǔ)過(guò)程實(shí)戰(zhàn)

1、ORACLE 存儲(chǔ)過(guò)程實(shí)戰(zhàn) -定義獲取部門ID的函數(shù) create or replace function Get_Dept_Id(in_Dept_name in varchar2 return integer as v_dept_id integer := 1; begin sELECT dept_id into v_dept_id FROM dict_depts WHERE DEPT_NAME = in_Dept_name; return v_dept_id; exception when no_data_found then v_dept_id := 1; return v_dept_i

2、d; when others then v_dept_id := 1200; -糯扎渡項(xiàng)目部將返回2條記錄,在此處捕獲并重新賦值 return v_dept_id; end Get_Dept_Id; -定義獲取單位ID的函數(shù) create or replace function Get_Unit_Id(in_unit_name in varchar2 return integer as v_unit_id integer := 1; begin SELECT id into v_unit_id FROM ins_unit_dict WHERE NAME = in_unit_name; retu

3、rn v_unit_id; exception when no_data_found then v_unit_id := 1; return v_unit_id; end Get_Unit_Id; -存儲(chǔ)過(guò)程定義代碼 /*調(diào)用注意事項(xiàng): 目標(biāo)表的數(shù)據(jù)列的數(shù)據(jù)類型必須符合下述規(guī)定: 設(shè)備名稱 varchar2; 規(guī)格型號(hào) varchar2; 原值 number(22,4/ varchar2; 靜值 number(22,4/ varchar2; 使用單位 varchar2/ varchar2; 分類 varchar2; 單位 varchar2; */ /* 參數(shù)說(shuō)明: in_math_type:機(jī)

4、具類型 in_OWNER_NAME :產(chǎn)權(quán)單位 in_Self_Code_pre:自編號(hào)前綴 */ create or replace procedure sub_mach_trans(in_math_type in integer,in_OWNER_NAME in varchar2,in_Self_Code_pre in varchar2 is type t_target_Data IS REF CURSOR; -目標(biāo)數(shù)據(jù)類型 v_CursorVar t_target_Data; -定義類型游標(biāo) -可以從目標(biāo)表中獲取的變量 v_MACH_NAME 項(xiàng)目部機(jī)具.設(shè)備名稱%TYPE; -機(jī)具名稱

5、; v_MACH_SPEC 項(xiàng)目部機(jī)具.規(guī)格型號(hào)%TYPE; -規(guī)格型號(hào); v_ORIGINAL_VALUE 項(xiàng)目部機(jī)具.原值%TYPE; -機(jī)具原值 v_CUR_VALUE 項(xiàng)目部機(jī)具.凈值%TYPE; -機(jī)具凈值 v_CUR_USED_DEPTID_name 項(xiàng)目部機(jī)具.使用單位%TYPE;-使用單位名稱 v_VALUE_TYPE 項(xiàng)目部機(jī)具.分類%TYPE; -機(jī)具價(jià)值分類 v_MACH_UNIT_NAME 項(xiàng)目部機(jī)具.單位%TYPE; -計(jì)量單位名稱 v_ID 項(xiàng)目部機(jī)具.序號(hào)%TYPE; -序號(hào) v_COMMENTS 項(xiàng)目部機(jī)具.備注%TYPE; -備注信息 v_AMORTISE_P

6、RICE 項(xiàng)目部機(jī)具.十月攤銷%TYPE; -攤銷單價(jià) -需要計(jì)算或定義的變量 v_CUR_USED_DEPTID integer; -使用部門編號(hào) v_MACH_UNIT integer ; -計(jì)量單位編號(hào) v_MACH_BASE_TYPE integer := 2; -基礎(chǔ)設(shè)備類型 v_SELF_CODE varchar2(50 := '' -自編號(hào) v_CUR_STATE integer; -當(dāng)前狀態(tài) v_Value_Type_num integer := 0; -價(jià)值分類數(shù)值表述變量 v_Record_cur_index integer; -當(dāng)前記錄Id v_HANDO

7、VER_Counter integer := 1;-領(lǐng)用記錄計(jì)數(shù)器 -系統(tǒng)調(diào)試用變量 S_Process_Counter integer := 0; -系統(tǒng)異常對(duì)象 SYS_DEBUG_EXC EXCEPTION; v_ErrorCode NUMBER; - 出錯(cuò)的代碼 v_ErrorMsg VARCHAR2(200; - 錯(cuò)誤的消息顯示 v_CurrentUser VARCHAR2(8; - 當(dāng)前數(shù)據(jù)庫(kù)用戶 v_Information VARCHAR2(100; - 關(guān)于錯(cuò)誤的信息 begin -讀取數(shù)據(jù) OPEN v_CursorVar FOR SELECT 設(shè)備名稱,規(guī)格型號(hào),原值,凈值

8、,使用單位,分類,單位,序號(hào),備注,十月攤銷 FROM 項(xiàng)目部機(jī)具 order by 序號(hào); -遍歷數(shù)據(jù) LOOP -從游標(biāo)中提取記錄值賦予制定變量 FETCH v_CursorVar INTO v_MACH_NAME, v_MACH_SPEC,v_ORIGINAL_VALUE,v_CUR_VALUE,v_CUR_USED_DEPTID_name,v_VALUE_TYPE,v_MACH_UNIT_NAME,v_Id,v_COMMENTS,v_AMORTISE_PRICE; -退出條件 EXIT WHEN v_CursorVar %NOTFOUND; -工作代碼 -使用部門編號(hào) v_CUR_US

9、ED_DEPTID := Get_Dept_Id(v_CUR_USED_DEPTID_name; -計(jì)量單位編號(hào) v_MACH_UNIT := Get_Unit_Id(v_MACH_UNIT_NAME; -自編號(hào) v_SELF_CODE := in_Self_Code_pre|to_char(v_Id; -當(dāng)前狀態(tài) if v_CUR_USED_DEPTID = 1 then v_CUR_STATE := 0; else v_CUR_STATE := 1; end if; - 價(jià)值分類 if v_VALUE_TYPE = 'A' then v_Value_Type_num :=

10、1; ELSIF v_VALUE_TYPE = 'B' then v_Value_Type_num:= 2; ELSIF v_VALUE_TYPE = 'C' then v_Value_Type_num := 3; ELSIF v_VALUE_TYPE = 'D' then v_Value_Type_num := 4; else v_Value_Type_num := 0; end if; -執(zhí)行復(fù)制到mach_basic_info表 insert into mach_basic_info ( id, MACH_NAME , MACH_TYPE

11、, MACH_SPEC , OWNER_NAME, ORIGINAL_VALUE , CUR_VALUE,CUR_STATE , CUR_USED_DEPTID , VALUE_TYPE , MACH_UNIT , MACH_UNIT_NAME , MACH_BASE_TYPE, comments, self_code values ( seq_mach_basic_info.nextval, v_MACH_NAME , in_math_type , v_MACH_SPEC , in_OWNER_NAME , CAST(v_ORIGINAL_VALUE AS number(22,4 , CAS

12、T(v_CUR_VALUE AS number(22,4, v_CUR_STATE ,v_CUR_USED_DEPTID , v_Value_Type_num , v_MACH_UNIT , v_MACH_UNIT_NAME , v_MACH_BASE_TYPE, v_COMMENTS, v_SELF_CODE ; -記錄當(dāng)前記錄id select max(id into v_Record_cur_index from mach_basic_info; -插入記錄到MACH_AMOR_ATTACH_INFO表:攤銷價(jià)格表 insert into MACH_AMOR_ATTACH_INFO (

13、id, MACH_ID, AMORTISE_PRICE, COMMENTS values ( seq_MACH_AMOR_ATTACH_INFO.Nextval, v_Record_cur_index, cast( v_AMORTISE_PRICE AS number(22,6, '2006-11-18,程序自動(dòng)導(dǎo)入,憑據(jù):10月攤銷金額' ; -插入記錄到MACH_HANDOVER_RECORD表:領(lǐng)用記錄表 if v_CUR_STATE = 1 then insert into MACH_HANDOVER_RECORD ( id, MACH_ID, -設(shè)備序號(hào) ORDER_

14、CODE, -交接單號(hào) HANDOVER_DATE, -交接日期 HANDOVER_SPAN_CODE, -交接日期所在財(cái)務(wù)月 FROM_DEPTID, -移交單位 TO_DEPTID, -接收單位 HANDOVER_STATE, -交接單狀態(tài)（枚舉 0：未交接 1：已交接 2：已退庫(kù)） AMORTISE_MONEY, -攤銷單價(jià)（若為空自動(dòng)從臺(tái)帳中讀?。?COMMENTS values ( seq_MACH_HANDOVER_RECORD.Nextval, v_Record_cur_index, '領(lǐng)_2006_10_'|to_char(v_HANDOVER_Counter,

15、 to_date('2006-10-01','yyyy-mm-dd', '200610C', 1, v_CUR_USED_DEPTID, 1, cast( v_AMORTISE_PRICE AS number(22,6, '2006-11-18,程序自動(dòng)導(dǎo)入,憑據(jù):10月攤銷金額' ; -計(jì)數(shù)器加1 v_HANDOVER_Counter := v_HANDOVER_Counter + 1; end if; -輸出信息 dbms_output.put_line('當(dāng)前完成:'| v_MACH_NAME ; S_Pro

16、cess_Counter := S_Process_Counter + 1; dbms_output.put_line('累計(jì)完成:' | to_char(S_Process_Counter; -調(diào)試異常 /* if S_Process_Counter = 2 then Raise SYS_DEBUG_EXC; end if; */ END LOOP; -關(guān)閉游標(biāo) CLOSE v_CursorVar ; -提交事務(wù) commit; -異常處理 exception -自 定義異常 when SYS_DEBUG_EXC then -關(guān)閉游標(biāo) CLOSE v_CursorVar ;

17、dbms_output.put_line('異常調(diào)試,自動(dòng)回滾' -回滾事務(wù) rollback; -其他異常 when others then -關(guān)閉游標(biāo) CLOSE v_CursorVar ; v_ErrorCode := SQLCODE; v_ErrorMsg := SQLERRM; v_CurrentUser := USER; v_Information := '遇到了錯(cuò)誤 ' | TO_CHAR(SYSDATE | ' 數(shù)據(jù)庫(kù)用戶 ' | v_CurrentUser; dbms_output.put_line('執(zhí)行錯(cuò)誤,自動(dòng)回滾

18、' dbms_output.put_line('詳細(xì)信息:'|'錯(cuò)誤代碼:'|v_ErrorCode|',錯(cuò)誤消息:'|v_ErrorMsg|',日志信息:'|v_Information; -回滾事務(wù) rollback; end sub_mach_trans; -存儲(chǔ)過(guò)程調(diào)用代碼 set serverout on -開(kāi)啟系統(tǒng)輸出 execute sub_mach_trans(334 ,'自購(gòu)' ,'項(xiàng)目部-' -歷史鏡像 select max(id from mach_basic_info

19、t -檢查結(jié)果 select * from mach_basic_info where id >3217 select * from dict_depts where dept_name like '%物資部%' -id is 9 select * from dict_depts where dept_name like '%水工廠%' -id is 2418 select * from mater_stock_dict where name like '%鋼材%' -id is 885 select * from mater_stock_

20、dict where name like '%水工廠%' -id is 1226 select * from mater_out_stock_base where receive_dept_id = 2418 and out_stock_id = 885 and id = 23231 select * from mater_out_stock_detail where base_id = 23231 select * from mater_in_stock_base where in_stock_id = 1226 select * from mater_in_stock_de

21、tail where base_id = 14142 -輔助功能函數(shù) create or replace function GetSubStorageBillNo ( in_SubStorageBillNoPre in varchar2,-二級(jí)入庫(kù)單編號(hào)前綴,如'工礦-','電-','辦公-'等 in_BuyType in integer,-物資購(gòu)買類型(1：統(tǒng)供；2：自購(gòu) in_SubStorageId in integer,-二級(jí)庫(kù)ID號(hào) in_yearCode in varchar,-當(dāng)前記錄所在財(cái)務(wù)年 in_monthCode in va

22、rchar-當(dāng)前記錄所在財(cái)務(wù)月 return varchar as v_billNo varchar2(50; v_CurBillNo varchar2(50; v_returnBillNo varchar2(50; v_buyTypeChar varchar2(2; v_Increment_id integer; v_Query_No varchar2(50; v_RecordCounter integer; begin if in_BuyType = 1 then v_buyTypeChar := '統(tǒng)' else v_buyTypeChar := '自'

23、end if; -Demo: 水暖-自2006-11105 v_billNo := in_SubStorageBillNoPre | v_buyTypeChar | to_char(in_yearCode | '-' | to_char(in_monthCode ; v_Query_No := v_billNo | '%' v_Query_No := '''' | v_Query_No | '''' -獲取同類單據(jù)最大編號(hào) execute immediate 'select t.bill_n

24、o from mater_in_stock_base t where t.in_stock_id = '| to_char(in_SubStorageId |' and t.bill_no like '|v_Query_No |'and t.bill_no is not null and rownum = 1 order by t.create_time desc ' into v_CurBillNo; if v_CurBillNo = null then -首張單據(jù) v_returnBillNo := in_SubStorageBillNoPre |

25、v_buyTypeChar | to_char(in_yearCode | '-' | to_char(in_monthCode | '1' else dbms_output.put_line('the Sql values is:'|'select max(cast(replace(t.bill_no,'''|v_billNo|''',''''as number from mater_in_stock_base t where t.in_stock_id =

26、 '| to_char(in_SubStorageId |' and t.bill_no like '|v_Query_No |'' execute immediate 'select max(cast(replace(t.bill_no,'''|v_billNo|''',''''as number from mater_in_stock_base t where t.in_stock_id = '| to_char(in_SubStorageId |'

27、; and t.bill_no like '|v_Query_No |'' into v_CurBillNo; dbms_output.put_line('the max v_CurBillNo values is:'|v_CurBillNo; -鋼-自2006-071 -重新組合最終單據(jù)編號(hào) v_returnBillNo := v_billNo | to_char(cast( v_CurBillNo as number + 1; dbms_output.put_line('after rebuild v_returnBillNo values

28、is:'|v_returnBillNo; end if; dbms_output.put_line('v_returnBillNo values is:'|v_returnBillNo; return v_returnBillNo; Exception when no_data_found then -首張單據(jù) v_returnBillNo := in_SubStorageBillNoPre | v_buyTypeChar | to_char(in_yearCode | '-' | to_char(in_monthCode | '1' -

29、dbms_output.put_line('v_returnBillNo values is:'|v_returnBillNo; dbms_output.put_line('錯(cuò)誤內(nèi)容:' | SQLERRM; return v_returnBillNo; end GetSubStorageBillNo; -存儲(chǔ)過(guò)程主體 /* 自動(dòng)根據(jù)大庫(kù)出庫(kù)單形成二級(jí)庫(kù)入庫(kù)單,適用于某部門一直在大庫(kù)中領(lǐng)料但最近才開(kāi)啟二級(jí)倉(cāng)庫(kù)業(yè)務(wù) 適用時(shí)請(qǐng)注意修改OPEN v_Base_CursorVar FOR部分 */ create or replace procedure AutoTran

30、sSubStorage ( in_MainStorageId in integer, -大庫(kù)ID號(hào) in_SubStorageId in integer, -二級(jí)庫(kù)ID號(hào) in_SubDeptId in integer,-二級(jí)庫(kù)部門ID號(hào) in_MainStorageMgrDeptId in integer ,-大庫(kù)管理員所在部門ID號(hào) in_MainStorageMgrUserId in varchar2, -大庫(kù)管理員用戶ID in_SubStorageBillNoPre in varchar2-二級(jí)入庫(kù)單編號(hào)前綴,如'工礦-','電-','辦公-&

31、#39;等 is type t_target_Data IS REF CURSOR; -目標(biāo)數(shù)據(jù)類型 v_Base_CursorVar t_target_Data; -定義類型游標(biāo) v_Detail_CursorVar t_target_Data; -定義類型游標(biāo) -可以從目標(biāo)表中獲取的變量 v_m_o_s_b_id mater_out_stock_base.id%TYPE; -序列號(hào) v_m_o_s_b_bill_no mater_out_stock_base.bill_no%TYPE; -單據(jù)編碼 v_m_o_s_b_pact_code mater_out_stock_base.pact_

32、code%TYPE; -合同編碼 v_m_o_s_b_buy_type mater_out_stock_base.buy_type%TYPE; -物資購(gòu)買類 型(1：統(tǒng)供；2：自購(gòu) v_m_o_s_b_out_stock_id mater_out_stock_base.out_stock_id%TYPE;-出庫(kù)倉(cāng)庫(kù)編號(hào) v_m_o_s_b_receive_dept_id mater_out_stock_base.receive_dept_id%TYPE;-領(lǐng)料部門編號(hào) v_m_o_s_b_bill_type mater_out_stock_base.bill_type%TYPE;-單據(jù)類型(1

33、：調(diào)撥單；2：領(lǐng)料單；3：價(jià)撥單 v_m_o_s_b_action_type mater_out_stock_base.action_type%TYPE;-單據(jù)操作類型(1：一般單據(jù)；2：沖紅單據(jù)；3 ：庫(kù)存損耗沖帳單據(jù) v_m_o_s_b_produce_state mater_out_stock_duce_state%TYPE;-生成出庫(kù)記錄狀態(tài)(0：未生成；1：已經(jīng)生成 v_m_o_s_b_month_code mater_out_stock_base.MONTH_CODE%TYPE; v_m_o_s_b_month_start_date mater_out_stock_

34、base.month_start_date%TYPE; v_m_o_s_b_month_end_date mater_out_stock_base.month_end_date%TYPE; v_m_o_s_b_out_date mater_out_stock_base.out_date%TYPE;-出庫(kù)日期 v_m_o_s_b_bill_date mater_out_stock_base.bill_date%TYPE;-制單日期 v_m_o_s_b_audit_state mater_out_stock_base.audit_state%TYPE;-單據(jù)審核狀態(tài)(0：編輯狀態(tài)；1：審核通過(guò)；2

35、 ：審核中 v_m_o_s_b_audit_time mater_out_stock_base.audit_time%TYPE;-單據(jù)審核時(shí)間 v_m_o_s_b_wbs_parent_id mater_out_stock_base.wbs_parent_id%TYPE; v_m_o_s_b_wbs_child_id mater_out_stock_base.wbs_child_id%TYPE; v_m_o_s_b_out_man mater_out_stock_base.out_man%TYPE; v_m_o_s_b_record_man mater_out_stock_base.recor

36、d_man%TYPE;-制單人員姓名 v_m_o_s_b_create_userid mater_out_stock_base.create_userid%TYPE;-創(chuàng)建用戶ID v_m_o_s_b_create_deptid mater_out_stock_base.create_deptid%TYPE;-創(chuàng)建用戶所屬部門編號(hào) v_m_o_s_b_create_time mater_out_stock_base.create_time%TYPE;-創(chuàng)建時(shí)間 v_m_o_s_b_is_modify_dept_stock mater_out_stock_base.is_modify_dept_

37、stock%TYPE;-是否更新領(lǐng)料部門庫(kù)存數(shù)據(jù)(針對(duì)作業(yè)隊(duì)領(lǐng)料到旗下倉(cāng)庫(kù) v_m_o_s_b_below_pact_code mater_out_stock_base.below_pact_code%TYPE;-領(lǐng)料部門對(duì)應(yīng)的分包合同編碼 v_m_o_s_b_sign_state mater_out_stock_base.sign_state%TYPE;-單據(jù)簽字狀態(tài)。 v_m_o_s_b_is_work_dept_bill mater_out_stock_base.is_work_dept_bill%TYPE;-是否是作業(yè)隊(duì)單據(jù) v_m_o_s_d_id mater_out_stock_d

38、etail.id%TYPE ; v_m_o_s_d_base_id mater_out_stock_detail.base_id%TYPE; -出庫(kù)單編號(hào) v_m_o_s_d_batch_number mater_out_stock_detail.batch_number%TYPE;-物資系統(tǒng)批號(hào) v_m_o_s_d_mater_id mater_out_stock_detail.mater_id%TYPE;-物資編號(hào) v_m_o_s_d_out_amount mater_out_stock_detail.out_amount%TYPE;-出庫(kù)數(shù)量 v_m_o_s_d_out_price ma

39、ter_out_stock_detail.out_price%TYPE;-出庫(kù)價(jià)格 v_m_o_s_d_out_money mater_out_stock_detail.out_money%TYPE;-出庫(kù)金額 v_m_o_s_d_plan_price mater_out_stock_detail.plan_price%T YPE;-計(jì)劃價(jià)格 v_m_o_s_d_plan_money mater_out_stock_detail.plan_money%TYPE;-計(jì)劃金額 v_m_o_s_d_out_stock_price mater_out_stock_detail.out_stock_pr

40、ice%TYPE;-庫(kù)存價(jià)格 v_m_o_s_d_out_stock_money mater_out_stock_detail.out_stock_money%TYPE;-庫(kù)存金額 v_m_o_s_d_before_amount mater_out_stock_detail.BEFORE_AUDIT_AMOUNT%TYPE;-審核前庫(kù)存數(shù)量 v_m_o_s_d_befoue_audit_money mater_out_stock_detail.BEFORE_AUDIT_MONEY%TYPE;-審核前庫(kù)存金額 v_m_o_s_d_after_audit_amount mater_out_stoc

41、k_detail.after_audit_amount%TYPE;-審核后庫(kù)存數(shù)量 v_m_o_s_d_after_audit_money mater_out_stock_detail.after_audit_money%TYPE;-審核后庫(kù)存金額 v_m_o_s_d_commments mater_out_stock_detail.COMMENTS%TYPE; v_m_o_s_d_approve_amount mater_out_stock_detail.approve_amount%TYPE;-審批數(shù)量 v_m_o_s_d_request_amount mater_out_stock_de

42、tail.request_amount%TYPE;-請(qǐng)領(lǐng)數(shù)量 v_m_o_s_d_receive_price mater_out_stock_detail.receive_price%TYPE;-領(lǐng)料價(jià)格 v_m_o_s_d_receive_money mater_out_stock_detail.receive_money%TYPE;-領(lǐng)料金額 -需要計(jì)算或定義的變量 v_Record_cur_index integer; -當(dāng)前記錄Id -系統(tǒng)調(diào)試用變量 S_Process_Counter integer := 0; -系統(tǒng)異常對(duì)象 SYS_DEBUG_EXC EXCEPTION; beg

43、in -打開(kāi) mater_out_stock_base OPEN v_Base_CursorVar FOR select id,bill_no,pact_code,buy_type,out_stock_id,receive_dept_id,bill_type, action_type,produce_state,month_code,month_start_date,month_end_date,out_date, bill_date,audit_state,audit_time,wbs_parent_id,wbs_child_id,out_man,record_man, create_use

44、rid,create_deptid,create_time,is_modify_dept_stock,below_pact_code, sign_state,is_work_dept_bill from mater_out_stock_base where receive_dept_id = in_SubDeptId and out_stock_id = in_MainStorageId and id not in(23231,23665,24074; -排除部分已自動(dòng)生成的記錄; -遍歷數(shù)據(jù) LOOP -從游標(biāo)中提取記錄值賦予指定變量 FETCH v_Base_CursorVar INTO

45、v_m_o_s_b_id,v_m_o_s_b_bill_no,v_m_o_s_b_pact_code, v_m_o_s_b_buy_type,v_m_o_s_b_out_stock_id,v_m_o_s_b_receive_dept_id, v_m_o_s_b_bill_type,v_m_o_s_b_action_type,v_m_o_s_b_produce_state, v_m_o_s_b_month_code,v_m_o_s_b_month_start_date,v_m_o_s_b_month_end_date, v_m_o_s_b_out_date,v_m_o_s_b_bill_date

46、,v_m_o_s_b_audit_state, v_m_o_s_b_audit_time,v_m_o_s_b_wbs_parent_id,v_m_o_s_b_wbs_child_id, v_m_o_s_b_out_man,v_m_o_s_b_record_man,v_m_o_s_b_create_userid,v_m_o_s_b _create_deptid, v_m_o_s_b_create_time,v_m_o_s_b_is_modify_dept_stock,v_m_o_s_b_below_pact_code, v_m_o_s_b_sign_state,v_m_o_s_b_is_work

47、_dept_bill; -退出條件 EXIT WHEN v_Base_CursorVar %NOTFOUND; -插入mater_in_stock_base insert into mater_in_stock_base ( id, bill_no, -單據(jù)編碼 pact_code,-合同編碼 buy_type,-物資購(gòu)買類型(1：統(tǒng)供；2：自購(gòu) out_stock_id,-調(diào)撥出庫(kù)倉(cāng)庫(kù)編號(hào) in_stock_id,-驗(yàn)收入庫(kù)倉(cāng)庫(kù)編號(hào) bill_type,-單據(jù)類型(1：統(tǒng)供物資入庫(kù)單；2：自購(gòu)物資入庫(kù)單；3：調(diào)撥驗(yàn)收單 produce_state,-生成進(jìn)庫(kù)記錄狀態(tài)(0：未生成；1：已生成

48、month_code, month_start_date, month_end_date, bill_date,-制單日期 check_date,-驗(yàn)收日期 AUDIT_STATE,-單據(jù)入庫(kù)審核狀態(tài)(0：編輯狀態(tài)，未審核；1：審核通過(guò) comments, create_userid,-創(chuàng)建用戶ID create_deptid,-創(chuàng)建用戶所屬部門編號(hào) create_time,-創(chuàng)建時(shí)間 is_work_dept_bill,-是否是作業(yè)隊(duì)單據(jù)(1：是，0：否 out_bill_id -如果是自動(dòng)生成的,存儲(chǔ)出庫(kù)單ID values ( seq_mater_in_stock_base.nextva

49、l, GetSubStorageBillNo(- 調(diào)用自動(dòng)生成單據(jù)編碼函數(shù) in_SubStorageBillNoPre, v_m_o_s_b_buy_type, in_SubStorageId, SUBSTRB(v_m_o_s_b_month_code,1,4, SUBSTRB(v_m_o_s_b_month_code,5,2 , v_m_o_s_b_pact_code, v_m_o_s_b_buy_type, in_MainStorageId, in_SubStorageId, v_m_o_s_b_bill_type, 1, v_m_o_s_b_month_code, v_m_o_s_b_

50、month_start_date, v_m_o_s_b_month_end_date, v_m_o_s_b_out_date, -制單日期與大庫(kù)出庫(kù) 日期同步 v_m_o_s_b_out_date, -驗(yàn)收日期與大庫(kù)出庫(kù)日期同步 0,-默認(rèn)未審核入庫(kù) '本單據(jù)由出庫(kù)單' | '' | v_m_o_s_b_bill_no | '' | '自動(dòng)生成', in_MainStorageMgrUserId, in_MainStorageMgrDeptId , sysdate,-程序處理時(shí)刻 1,-廠隊(duì)單據(jù) v_m_o_s_b_id -對(duì)應(yīng)入

51、庫(kù)單編號(hào) ; -記錄當(dāng)前記錄id select max(id into v_Record_cur_index from mater_in_stock_base; -嵌套游標(biāo) OPEN v_Detail_CursorVar FOR select id,base_id,batch_number,mater_id,out_amount,out_price,out_money,plan_price,plan_money, out_stock_price,out_stock_money,BEFORE_AUDIT_AMOUNT,BEFORE_AUDIT_MONEY,after_audit_amount,after_audit_money, COMMENTS,approve_amount,request_amount,receive_price,receive_money from mater_out_stock_detail where base_id = v_m_o_s_b_id ; LOOP -遍歷數(shù)據(jù) FETCH v_Detail_Curs