--定义获取部门ID的函数

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_id;

         when others then

         v_dept_id := 1200; --糯扎渡项目部将返回2条记录,在此处捕获并重新赋值

          return v_dept_id;

end Get_Dept_Id;

--定义获取单位ID的函数

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;

     return v_unit_id;

     exception

         when no_data_found then

         v_unit_id := 1;

         return v_unit_id;

        

end Get_Unit_Id;

--存储过程定义代码

/*调用注意事项:

目标表的数据列的数据类型必须符合下述规定:

设备名称 varchar2;

规格型号 varchar2;

原值 number(22,4)/ varchar2;

静值 number(22,4)/ varchar2;

使用单位 varchar2/ varchar2;

分类 varchar2;

单位 varchar2;

*/

/*

参数说明:

in_math_type:机具类型

in_OWNER_NAME :产权单位

in_Self_Code_pre:自编号前缀

*/

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; --目标数据类型

v_CursorVar t_target_Data; --定义类型游标

--可以从目标表中获取的变量

v_MACH_NAME 项目部机具.设备名称%TYPE;           --机具名称;

v_MACH_SPEC 项目部机具.规格型号%TYPE;           --规格型号;

v_ORIGINAL_VALUE  项目部机具.原值%TYPE;         --机具原值

v_CUR_VALUE 项目部机具.净值%TYPE;               --机具净值

v_CUR_USED_DEPTID_name 项目部机具.使用单位%TYPE;--使用单位名称

v_VALUE_TYPE 项目部机具.分类%TYPE;              --机具价值分类

v_MACH_UNIT_NAME 项目部机具.单位%TYPE;          --计量单位名称

v_ID 项目部机具.序号%TYPE;                      --序号

v_COMMENTS 项目部机具.备注%TYPE;                --备注信息

v_AMORTISE_PRICE 项目部机具.十月摊销%TYPE;      --摊销单价

--需要计算或定义的变量

v_CUR_USED_DEPTID integer;      --使用部门编号

v_MACH_UNIT integer ;           --计量单位编号

v_MACH_BASE_TYPE integer := 2;  --基础设备类型

v_SELF_CODE varchar2(50) := ''; --自编号

v_CUR_STATE integer;            --当前状态

v_Value_Type_num integer := 0;  --价值分类数值表述变量

v_Record_cur_index integer;     --当前记录Id

v_HANDOVER_Counter integer := 1;--领用记录计数器

--系统调试用变量

S_Process_Counter integer := 0;

--系统异常对象

SYS_DEBUG_EXC EXCEPTION;

v_ErrorCode NUMBER;          -- 出错的代码

v_ErrorMsg  VARCHAR2(200);   -- 错误的消息显示

v_CurrentUser VARCHAR2(8);   -- 当前数据库用户

v_Information VARCHAR2(100); -- 关于错误的信息

  begin

      --读取数据

      OPEN v_CursorVar  FOR

           SELECT 设备名称,规格型号,原值,净值,使用单位,分类,单位,序号,备注,十月摊销 FROM 项目部机具 order by 序号;

      --遍历数据

      LOOP

           --从游标中提取记录值赋予制定变量

           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;

              --工作代码

              --使用部门编号

              v_CUR_USED_DEPTID := Get_Dept_Id(v_CUR_USED_DEPTID_name);

              --计量单位编号

              v_MACH_UNIT := Get_Unit_Id(v_MACH_UNIT_NAME);

                --自编号

                v_SELF_CODE := in_Self_Code_pre||to_char(v_Id);

                --当前状态

                if v_CUR_USED_DEPTID = 1 then

                   v_CUR_STATE := 0;

                else

                   v_CUR_STATE := 1;

                end if;

                -- 价值分类

                if v_VALUE_TYPE = 'A' then

                   v_Value_Type_num := 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;               

                --执行复制到mach_basic_info表

              insert into mach_basic_info

                (

                     id,

                     MACH_NAME ,

                     MACH_TYPE ,

                     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))  ,

                     CAST(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

                );

                --记录当前记录id

                select max(id) into v_Record_cur_index from mach_basic_info;

                --插入记录到MACH_AMOR_ATTACH_INFO表:摊销价格表

                insert into MACH_AMOR_ATTACH_INFO

                (

                    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,程序自动导入,凭据:10月摊销金额'

                 );

                --插入记录到MACH_HANDOVER_RECORD表:领用记录表

                 if v_CUR_STATE = 1 then

                     insert into MACH_HANDOVER_RECORD

                     (

                         id,

                         MACH_ID,             --设备序号

                         ORDER_CODE,          --交接单号

                         HANDOVER_DATE,       --交接日期

                         HANDOVER_SPAN_CODE,  --交接日期所在财务月

                         FROM_DEPTID,         --移交单位

                         TO_DEPTID,           --接收单位

                         HANDOVER_STATE,      --交接单状态(枚举 0:未交接 1:已交接 2:已退库)

                         AMORTISE_MONEY,      --摊销单价(若为空自动从台帐中读取)

                         COMMENTS

                      )

                      values

                      (

                         seq_MACH_HANDOVER_RECORD.Nextval,

                         v_Record_cur_index,

                         '领_2006_10_'||to_char(v_HANDOVER_Counter),

                         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,程序自动导入,凭据:10月摊销金额'

                      );

                      --计数器加1

                      v_HANDOVER_Counter := v_HANDOVER_Counter + 1;

                end if;

                --输出信息

                dbms_output.put_line('当前完成:'|| v_MACH_NAME  );

                S_Process_Counter := S_Process_Counter + 1;

                dbms_output.put_line('累计完成:' || to_char(S_Process_Counter));

                --调试异常

                /*

                if S_Process_Counter = 2 then

                   Raise SYS_DEBUG_EXC;

                end if;

                */

      END LOOP;

      --关闭游标

      CLOSE v_CursorVar ;

      --提交事务

      commit;

             --异常处理

             exception

               --自定义异常

               when SYS_DEBUG_EXC then

                   --关闭游标

                   CLOSE v_CursorVar ;

                   dbms_output.put_line('异常调试,自动回滚');

                   --回滚事务

                   rollback;

               --其他异常

               when others then

                    --关闭游标

                   CLOSE v_CursorVar ;

                   v_ErrorCode := SQLCODE;

                   v_ErrorMsg := SQLERRM;

                   v_CurrentUser := USER;

                   v_Information := '遇到了错误 ' || TO_CHAR(SYSDATE) || ' 数据库用户 ' || v_CurrentUser;

                   dbms_output.put_line('执行错误,自动回滚');

                   dbms_output.put_line('详细信息:'||'错误代码:'||v_ErrorCode||',错误消息:'||v_ErrorMsg||',日志信息:'||v_Information);

                   --回滚事务

                   rollback;

end sub_mach_trans;

 

--存储过程调用代码

set serverout on  --开启系统输出

execute sub_mach_trans(334 ,'自购' ,'项目部-');

--历史镜像

select max(id) from mach_basic_info t

--检查结果

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_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_detail where base_id = 14142

--辅助功能函数

create or replace function GetSubStorageBillNo

(

 in_SubStorageBillNoPre in varchar2,--二级入库单编号前缀,如'工矿-','电-','办公-'等

 in_BuyType in integer,----物资购买类型(1:统供;2:自购)

 in_SubStorageId in integer,--二级库ID号

 in_yearCode in varchar,--当前记录所在财务年

 in_monthCode in varchar--当前记录所在财务月

)

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 := '统';

         else

            v_buyTypeChar := '自';

         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 || '''';

         --获取同类单据最大编号

         execute immediate 'select t.bill_no 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

            --首张单据

            v_returnBillNo := in_SubStorageBillNoPre || 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 = '|| 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) ||' 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

             --重新组合最终单据编号

             v_returnBillNo := v_billNo || to_char(cast( v_CurBillNo as number) + 1);

             dbms_output.put_line('after rebuild v_returnBillNo values is:'||v_returnBillNo);

         end if;            

         dbms_output.put_line('v_returnBillNo values is:'||v_returnBillNo); 

         return v_returnBillNo;

       Exception

               when no_data_found then

               --首张单据

               v_returnBillNo := in_SubStorageBillNoPre || v_buyTypeChar || to_char(in_yearCode) || '-' || to_char(in_monthCode) || '1';

               --dbms_output.put_line('v_returnBillNo values is:'||v_returnBillNo);               

               dbms_output.put_line('错误内容:' || SQLERRM);

               return v_returnBillNo;

end GetSubStorageBillNo;


--存储过程主体

/*

自动根据大库出库单形成二级库入库单,适用于某部门一直在大库中领料但最近才开启二级仓库业务

适用时请注意修改OPEN v_Base_CursorVar  FOR部分

*/

create or replace procedure AutoTransSubStorage

(

in_MainStorageId in integer, --大库ID号

in_SubStorageId in integer, --二级库ID号

in_SubDeptId in integer,--二级库部门ID号

in_MainStorageMgrDeptId in integer ,--大库管理员所在部门ID号

in_MainStorageMgrUserId in varchar2, --大库管理员用户ID

in_SubStorageBillNoPre in varchar2--二级入库单编号前缀,如'工矿-','电-','办公-'等

)

is

          type t_target_Data IS REF CURSOR; --目标数据类型

          v_Base_CursorVar t_target_Data;   --定义类型游标

          v_Detail_CursorVar t_target_Data; --定义类型游标

          --可以从目标表中获取的变量

          v_m_o_s_b_id  mater_out_stock_base.id%TYPE;         --序列号

          v_m_o_s_b_bill_no mater_out_stock_base.bill_no%TYPE;    --单据编码

          v_m_o_s_b_pact_code mater_out_stock_base.pact_code%TYPE;  --合同编码

          v_m_o_s_b_buy_type mater_out_stock_base.buy_type%TYPE;   --物资购买类型(1:统供;2:自购)

          v_m_o_s_b_out_stock_id mater_out_stock_base.out_stock_id%TYPE;--出库仓库编号

          v_m_o_s_b_receive_dept_id mater_out_stock_base.receive_dept_id%TYPE;--领料部门编号

          v_m_o_s_b_bill_type mater_out_stock_base.bill_type%TYPE;--单据类型(1:调拨单;2:领料单;3:价拨单)

          v_m_o_s_b_action_type mater_out_stock_base.action_type%TYPE;--单据操作类型(1:一般单据;2:冲红单据;3 :库存损耗冲帐单据)

          v_m_o_s_b_produce_state mater_out_stock_base.produce_state%TYPE;--生成出库记录状态(0:未生成;1:已经生成)

          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_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;--出库日期

          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;--单据审核状态(0:编辑状态;1:审核通过;2 :审核中)

          v_m_o_s_b_audit_time mater_out_stock_base.audit_time%TYPE;--单据审核时间

          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.record_man%TYPE;--制单人员姓名

          v_m_o_s_b_create_userid mater_out_stock_base.create_userid%TYPE;--创建用户ID

          v_m_o_s_b_create_deptid mater_out_stock_base.create_deptid%TYPE;--创建用户所属部门编号

          v_m_o_s_b_create_time mater_out_stock_base.create_time%TYPE;--创建时间

          v_m_o_s_b_is_modify_dept_stock mater_out_stock_base.is_modify_dept_stock%TYPE;--是否更新领料部门库存数据(针对作业队领料到旗下仓库)

          v_m_o_s_b_below_pact_code mater_out_stock_base.below_pact_code%TYPE;--领料部门对应的分包合同编码

          v_m_o_s_b_sign_state mater_out_stock_base.sign_state%TYPE;--单据签字状态。

          v_m_o_s_b_is_work_dept_bill mater_out_stock_base.is_work_dept_bill%TYPE;--是否是作业队单据

          v_m_o_s_d_id mater_out_stock_detail.id%TYPE ;

          v_m_o_s_d_base_id mater_out_stock_detail.base_id%TYPE; --出库单编号

          v_m_o_s_d_batch_number mater_out_stock_detail.batch_number%TYPE;--物资系统批号

          v_m_o_s_d_mater_id mater_out_stock_detail.mater_id%TYPE;--物资编号

          v_m_o_s_d_out_amount mater_out_stock_detail.out_amount%TYPE;--出库数量

          v_m_o_s_d_out_price mater_out_stock_detail.out_price%TYPE;--出库价格

          v_m_o_s_d_out_money mater_out_stock_detail.out_money%TYPE;--出库金额

          v_m_o_s_d_plan_price mater_out_stock_detail.plan_price%TYPE;--计划价格

          v_m_o_s_d_plan_money mater_out_stock_detail.plan_money%TYPE;--计划金额

          v_m_o_s_d_out_stock_price mater_out_stock_detail.out_stock_price%TYPE;--库存价格

          v_m_o_s_d_out_stock_money mater_out_stock_detail.out_stock_money%TYPE;--库存金额

          v_m_o_s_d_before_amount mater_out_stock_detail.BEFORE_AUDIT_AMOUNT%TYPE;--审核前库存数量

          v_m_o_s_d_befoue_audit_money mater_out_stock_detail.BEFORE_AUDIT_MONEY%TYPE;--审核前库存金额

          v_m_o_s_d_after_audit_amount mater_out_stock_detail.after_audit_amount%TYPE;--审核后库存数量

          v_m_o_s_d_after_audit_money mater_out_stock_detail.after_audit_money%TYPE;--审核后库存金额

          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;--审批数量

          v_m_o_s_d_request_amount mater_out_stock_detail.request_amount%TYPE;--请领数量

          v_m_o_s_d_receive_price mater_out_stock_detail.receive_price%TYPE;--领料价格

          v_m_o_s_d_receive_money mater_out_stock_detail.receive_money%TYPE;--领料金额

          --需要计算或定义的变量

          v_Record_cur_index integer;     --当前记录Id

          --系统调试用变量

          S_Process_Counter integer := 0;

          --系统异常对象

          SYS_DEBUG_EXC EXCEPTION;

            begin

                --打开 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_userid,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); --排除部分已自动生成的记录;

                --遍历数据

                LOOP

                     --从游标中提取记录值赋予指定变量

                     FETCH v_Base_CursorVar  INTO 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,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_dept_bill;

                     --退出条件

                     EXIT WHEN v_Base_CursorVar %NOTFOUND;

                          --插入mater_in_stock_base

                          insert into mater_in_stock_base

                          (

                             id,

                             bill_no,  --单据编码

                             pact_code,--合同编码

                             buy_type,--物资购买类型(1:统供;2:自购)

                             out_stock_id,--调拨出库仓库编号

                             in_stock_id,--验收入库仓库编号

                             bill_type,--单据类型(1:统供物资入库单;2:自购物资入库单;3:调拨验收单)

                             produce_state,--生成进库记录状态(0:未生成;1:已生成)

                             month_code,

                             month_start_date,

                             month_end_date,

                             bill_date,--制单日期

                             check_date,--验收日期

                             AUDIT_STATE,--单据入库审核状态(0:编辑状态,未审核;1:审核通过)

                             comments,

                             create_userid,--创建用户ID

                             create_deptid,--创建用户所属部门编号

                             create_time,--创建时间

                             is_work_dept_bill,--是否是作业队单据(1:是,0:否)

                             out_bill_id --如果是自动生成的,存储出库单ID

                          )

                          values

                          (

                            seq_mater_in_stock_base.nextval,

                            GetSubStorageBillNo(-- 调用自动生成单据编码函数

                                                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_month_start_date,

                            v_m_o_s_b_month_end_date,

                            v_m_o_s_b_out_date, --制单日期与大库出库日期同步

                            v_m_o_s_b_out_date, --验收日期与大库出库日期同步

                            0,--默认未审核入库

                            '本单据由出库单' || '‘' || v_m_o_s_b_bill_no || '’' || '自动生成',

                            in_MainStorageMgrUserId,

                            in_MainStorageMgrDeptId ,

                            sysdate,--程序处理时刻

                            1,--厂队单据

                            v_m_o_s_b_id --对应入库单编号

                          );

                          --记录当前记录id

                          select max(id) into v_Record_cur_index from mater_in_stock_base;

                        --嵌套游标

                                    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

                                         --遍历数据

                                         FETCH v_Detail_CursorVar  INTO  v_m_o_s_d_id ,v_m_o_s_d_base_id,

                                      v_m_o_s_d_batch_number ,v_m_o_s_d_mater_id ,v_m_o_s_d_out_amount,

                                      v_m_o_s_d_out_price,v_m_o_s_d_out_money,v_m_o_s_d_plan_price,

                                      v_m_o_s_d_plan_money,v_m_o_s_d_out_stock_price,

                                      v_m_o_s_d_out_stock_money,v_m_o_s_d_before_amount ,

                                      v_m_o_s_d_befoue_audit_money,v_m_o_s_d_after_audit_amount,

                                      v_m_o_s_d_after_audit_money ,v_m_o_s_d_commments,

                                      v_m_o_s_d_approve_amount,v_m_o_s_d_request_amount,

                                      v_m_o_s_d_receive_price ,v_m_o_s_d_receive_money;

                                       --退出条件

                                       EXIT WHEN v_Detail_CursorVar %NOTFOUND;

                                          --插入mater_in_stock_detail

                                 insert into mater_in_stock_detail

                                 (

                                     id,

                                     base_id,--入库单编号

                                     batch_number,--系统批号,由系统自动生成

                                     mater_id,--物资编号

                                     purchase_amount,--进货数量

                                     purchase_price,--进货价格

                                     purchase_money,--进货金额

                                     check_amount,--验收数量

                                     check_price,--验收价格

                                     check_money,--验收金额

                                     plan_price,--计划价格

                                     plan_money,--计划金额

                                     in_stock_price,--入库库存价格

                                     in_stock_money,--入库库存金额

                                     should_amount--应收数量

                                 )

                                 values

                                 (

                                      seq_mater_in_stock_detail.nextval,

                                      v_Record_cur_index,

                                      v_m_o_s_d_batch_number,

                                           v_m_o_s_d_mater_id,

                                           v_m_o_s_d_approve_amount,

                                      v_m_o_s_d_receive_price,

                                           v_m_o_s_d_receive_money,

                                           v_m_o_s_d_approve_amount,

                                      v_m_o_s_d_receive_price,

                                           v_m_o_s_d_receive_money,

                                           v_m_o_s_d_plan_price,

                                           v_m_o_s_d_plan_money,

                                      v_m_o_s_d_receive_price,

                                           v_m_o_s_d_receive_money,

                                      v_m_o_s_d_approve_amount

                                 );

                                    END LOOP;

                                    --关闭游标

                                    CLOSE v_Detail_CursorVar ;

                          --调试信息

                          dbms_output.put_line('当前完成:'|| v_m_o_s_b_bill_no  );

                          S_Process_Counter := S_Process_Counter + 1;

                          dbms_output.put_line('累计完成:' || to_char(S_Process_Counter));

                END LOOP;

                --关闭游标

                CLOSE v_Base_CursorVar ;

                --提交事务

                commit;

                       --异常处理

                       exception

                         --自定义异常

                         when SYS_DEBUG_EXC then

                             --关闭游标

                             CLOSE v_Base_CursorVar ;

                             dbms_output.put_line('异常调试,自动会滚');

                             --回滚事务

                             rollback;

                         --其他异常

                         when others then

                              --关闭游标

                             CLOSE v_Base_CursorVar ;

                             dbms_output.put_line('执行错误,自动会滚');

                             dbms_output.put_line('错误内容:' || SQLERRM);

                             --回滚事务

                             rollback;

end AutoTransSubStorage;

--检查处理之前

select  count(* ) from mater_in_stock_base where in_stock_id = 1226

exec AutoTransSubStorage(885,1226,2418,9,'licuiping','钢-');

--检查处理之后

select  count(* ) from mater_in_stock_base where in_stock_id = 1226

--检查数据

select * from mater_in_stock_base where in_stock_id = 1226

delete from mater_in_stock_base where in_stock_id = 1226

 

--利用临时表获取自增序列函数

create or replace function GetSeqNumber return number

is

v_num number;

begin

        --动态创建临时表

        select count(*) into v_num from user_tables where table_name= 'T_TEMP';

        if v_num < 1 then

            --创建临时表

            execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (COL1 number(4)) ON COMMIT delete ROWS';

            ---初始记录

            execute immediate 'insert into t_temp(COL1) values(1)';

        end if;

        --记录获取

        execute  immediate 'select col1 from t_temp' into v_num;

        --记录修正

        execute immediate 'update t_temp set col1 = col1 + 1 ';

        return v_num;

end GetSeqNumber;