dsdsaaaa

create or replace procedure p_edi_verify_bsg051
/**************************************************************************************************
**    Name:       p_edi_verify_bsg051
**    Purpose:    海关端审核BSG051报文
**    Revisions:
**    Ver         Date        Author                Description
**    ----------  ----------  --------------------  ---------------------------------------
**    V1.1        2011-12-22 王进宝                1.Create the Procedure
**    Notes:      <1>海关端审核BSG051报文
**
**************************************************************************************************/
(
    str_in_flag       in  varchar2,    --审核标记  Y 审核通过; E 审核不通过
    str_in_opuser     in  varchar2,    --操作人
    str_o_result      out varchar2,    --是否成功  Y 成功;否则返回错误信息
    str_o_msg         out varchar2     --是否成功  Y 成功;否则返回错误信息
)
is

    --报文类型为BSG051的所有待入库报文信息
    cursor cur_headinfo is
           select seq_id,
           ems_no,
           goods_id,
           code_ts,
           new_code_ts,
           unit_1,
           stock_num,
           change_mark,
           verify_flag
      from hscode_bsg051 a
     where a.verify_flag = 'M';

    i_l_row                pls_integer;                                              --记录数数
    str_l_sendid           bws_hgdsc.edi_o_headinfo.senderid%type := 'SZCUSTOMS';    --发送id
    str_l_receiverid       bws_hgdsc.edi_o_headinfo.receiverid%type := 'SZCPORT';    --接收方
    str_l_funccode         bws_hgdsc.edi_o_headinfo.functioncode%type := '2';        --功能代码
    str_l_bsg015           bws_hgdsc.edi_o_headinfo.messagetype%type := 'BSG015';    --报文类型
    str_l_bsg052           bws_hgdsc.edi_o_headinfo.messagetype%type := 'BSG052';    --报文类型
    str_l_errmsg           varchar2(3000);                 --错误信息
    i_l_seqediout          pls_integer;                    --流水号
    str_back               varchar2(1);                    --未核扣的出入库单标记
    str_l_err_flag         varchar2(1);                    --错误标记
    str_l_formid           varchar2(4000);                 --出入库单字符串
    str_l_seq_id           varchar2(4000);                 --不通过商品流水号

    str_l_stock_unit       ems_list.stock_unit%type;
    ---------------
    TYPE cur_type IS REF CURSOR;
    v_sql   varchar2(4000);
    var_cur_type cur_type;
    rec  warehouse_bill_head%ROWTYPE;
    --发送bsg052审核回执报文
    function f_send_bsg052
    (
        str_in_messagetype   in varchar2,  --报文类型
        str_in_feedback_flag in varchar2,  --审核标记 Y 通过; N 不通过
        str_in_dataflag      in varchar    --数据处理标记 当报文为BSG015时，此时为 出入库单号
    )
    return integer
    as
        i_l_count           pls_integer;                     -- 计算个数
    begin
        


        if str_in_messagetype = str_l_bsg052 then
           for whbill_sd in (select a.seq_id from hscode_bsg051 a where a.verify_flag = str_in_dataflag) loop
               select bws_hgdsc.seq_ediout.nextval into i_l_seqediout from dual;
               insert into bws_hgdsc.edi_o_headinfo
                     (functioncode,senderid,sendername,receiverid,seq_edinum,send_time,messagetype,sendtime,
                      messageid)
               select str_l_funccode,str_l_sendid,str_l_sendid,str_l_receiverid,i_l_seqediout,sysdate,str_in_messagetype,
                      to_char(sysdate, 'yyyyMMddHH24miss') ||LPAD(TO_CHAR(SEQ_MSGIDTIME.NEXTVAL), 3, '0'),
                      str_l_sendid || '_' || to_char(sysdate, 'yyyyMMddHH24miss') ||LPAD(TO_CHAR(SEQ_MSGIDTIME.NEXTVAL), 3, '0')
                 from dual;
               --添加出入库单接收回执处理
               insert into bws_hgdsc.edi_o_rec_hscode
                     (seq_id,feedback_date,feedback_time,feedback_flag,feedback_mess,null_field,seq_edinum)
               select seq_id,to_number(to_char(sysdate, 'yyyyMMdd')),to_number(to_char(sysdate, 'HH24miss')),
                      str_in_feedback_flag,decode(str_in_feedback_flag,'Y','海关接受申报。'),null,i_l_seqediout
                 from hscode_bsg051 a
                where a.verify_flag = whbill_sd.seq_id;
           end loop; 
        end if;
        --退单回执
        if str_in_messagetype = str_l_bsg015 then
           select bws_hgdsc.seq_ediout.nextval into i_l_seqediout from dual;
           insert into bws_hgdsc.edi_o_headinfo
                 (functioncode,senderid,sendername,receiverid,seq_edinum,send_time,messagetype,sendtime,
                  messageid)
           select str_l_funccode,str_l_sendid,str_l_sendid,str_l_receiverid,i_l_seqediout,sysdate,str_in_messagetype,
                  to_char(sysdate, 'yyyyMMddHH24miss') ||LPAD(TO_CHAR(SEQ_MSGIDTIME.NEXTVAL), 3, '0'),
                  str_l_sendid || '_' || to_char(sysdate, 'yyyyMMddHH24miss') ||LPAD(TO_CHAR(SEQ_MSGIDTIME.NEXTVAL), 3, '0')
             from dual;
             
           insert into bws_hgdsc.edi_o_wh_recbill
                 (form_id,feedback_date,feedback_time,feedback_flag,feedback_mess,null_field,seq_edinum)
           select str_in_dataflag,to_number(to_char(sysdate, 'yyyyMMdd')),to_number(to_char(sysdate, 'HH24miss')),
                  str_in_feedback_flag,null,null,i_l_seqediout
             from dual;
        end if;
        return 1;
    exception when others then
        str_l_errmsg := sqlcode || ' ' || sqlerrm;
        return 0 ;
    end;
    function f_write_logs
    (
     str_in_emsno   in varchar2,  --载货单编码
     str_in_goodsid in varchar2   --料件号
    )
    return integer
    as
        i_l_count           pls_integer;                     -- 计算个数
    begin
        /*insert into ems_list_hscode_log
              (ems_no, goods_id, stock_num, stock_unit, amount_to_add, amount_to_reduce,
               g_name_en, g_model, decl_price, curr, code_ts, g_name_cn, cus_code, cus_name,
               new_code_ts, new_stock_unit, new_stock_num, operator, op_time
               )*/

        return 1;
    exception when others then
        str_l_errmsg := sqlcode || ' ' || sqlerrm;
        return 0 ;
    end;
begin

    update hscode_bsg051 a
       set a.verify_flag = 'M'   --临时状态
     where a.verify_flag = 'D';  --待审核
    --审核不通过
    if str_in_flag = 'E' then
       for rec_e in cur_headinfo loop
           --料件号不为空
           if rec_e.goods_id is not null then
              
               for whbill_e in (
                                select b.form_id
                                  from warehouse_bill_list a,warehouse_bill_head b,ems_list c
                                 where a.form_id = b.form_id
                                   and b.ems_no = c.ems_no
                                   and a.goods_id = c.goods_id
                                   and (c.amount_to_add >0 or c.amount_to_reduce > 0)
                                   and c.ems_no = rec_e.ems_no
                                   and c.goods_id = rec_e.goods_id
                                   group by b.form_id   
                                ) loop
                                
                   str_l_formid := str_l_formid || ''''|| whbill_e.form_id || ''''||',';                
                  
               end loop;
           else
               --当料件号为空，商品编码不为空时
               if rec_e.code_ts is not null then
                  for whbill_e2 in (
                                    select c.form_id from warehouse_bill_list b,warehouse_bill_head c,  
                                           (select a.ems_no,a.goods_id,a.code_ts from ems_list a
                                             where (a.amount_to_add >0 or a.amount_to_reduce > 0)
                                               and a.code_ts = rec_e.code_ts
                                               and a.ems_no = rec_e.ems_no
                                               and rownum =1
                                            ) d
                                     where b.form_id = c.form_id
                                       and c.ems_no = d.ems_no
                                       and b.code_ts = d.code_ts
                                       group by c.form_id
                                   ) loop
                      str_l_formid := str_l_formid || ''''|| whbill_e2.form_id || ''''||','; 
                  end loop;
               end if;      
           end if;
       end loop;
       
       --发送bsg015 退单回执
       str_l_formid := '('||substr(str_l_formid,1,length(str_l_formid)-1)||')';
       v_sql :='select * from warehouse_bill_head where form_id in '|| str_l_formid;
       open var_cur_type for v_sql;
       loop
          fetch  var_cur_type into rec ;
          exit when  var_cur_type %notfound;
               --发送退单回执 bsg015
               i_l_row := f_send_bsg052(str_l_bsg015,'N',rec.form_id);
       end loop;
       close var_cur_type;
       --发送bsg052审核回执报文
       i_l_row := f_send_bsg052(str_l_bsg052,'N','M');

       if i_l_row >0 then
          update hscode_bsg051 a
             set a.verify_flag = 'E'   --审核不通过
           where a.verify_flag = 'M';  --临时状态
       end if;
    end if;
    --审核通过
    if str_in_flag = 'Y' then

       for rec_hi in cur_headinfo loop
           str_back := 'N';
           --1)报文BSG051中旧商品HS编码不为空
           if rec_hi.code_ts is not null then
              --电子账册号和旧商品HS编码查询状态为未核扣出入库单是否存在，如果存在，则执行退操作，发送BSG015报文，同时发送审核不通过回执（BSG052报文）
              for whbill_hi in (
                                select c.form_id from warehouse_bill_list b,warehouse_bill_head c,  
                                           (select a.ems_no,a.goods_id,a.code_ts from ems_list a
                                             where (a.amount_to_add >0 or a.amount_to_reduce > 0)
                                               and a.code_ts = rec_hi.code_ts
                                               and a.ems_no = rec_hi.ems_no
                                               and rownum =1
                                            ) d
                                     where b.form_id = c.form_id
                                       and c.ems_no = d.ems_no
                                       and b.code_ts = d.code_ts
                                       group by c.form_id
                                ) loop

                   str_l_formid := str_l_formid || whbill_hi.form_id || ',';
                   str_back := 'Y';
               end loop;

               if str_back = 'Y' then
                  str_l_err_flag := 'Y';
                  goto l_EndOfCheck;
               end if;
           end if;
           --2)如果调整标记为“仅单位互换”
           if rec_hi.change_mark = '12' then
              --如果商品料件号为空，发送审核不通过回执（BSG052报文）
              if rec_hi.goods_id is null then
                 str_l_err_flag := 'Y';
              else
                  select max(a.stock_unit) into str_l_stock_unit
                    from ems_list a
                   where a.ems_no = rec_hi.ems_no
                     and a.goods_id = rec_hi.goods_id;
                  --更新电子底账 库存计量单位
                  if str_l_stock_unit is not null then
                     update ems_list a
                        set a.stock_unit = rec_hi.unit_1
                      where a.ems_no = rec_hi.ems_no
                        and a.goods_id = rec_hi.goods_id;

                     --写日志
                     insert into ems_list_hscode_log
                           (ems_no, goods_id, stock_num, stock_unit, amount_to_add, amount_to_reduce,
                            g_name_en, g_model, decl_price, curr, code_ts, g_name_cn, cus_code, cus_name,
                            new_code_ts, new_stock_unit, new_stock_num, operator, op_time
                            )
                     select ems_no, goods_id, stock_num, str_l_stock_unit, amount_to_add, amount_to_reduce,
                            g_name_en, g_model, decl_price, curr, code_ts, g_name_cn, cus_code, cus_name,
                            null, stock_unit, null, str_in_opuser, sysdate
                       from ems_list a
                      where a.ems_no = rec_hi.ems_no
                        and a.goods_id = rec_hi.goods_id;
                  else
                     str_l_err_flag := 'Y';
                  end if;
              end if;
              goto l_EndOfCheck;
           end if;
           --3)如果调整标记为“只修改商品编码”
           if rec_hi.change_mark = 'NEW' then
              --旧商品HS编码或者新商品HS编码其中一个为空，发送审核不通过回执（BSG052报文）
              if rec_hi.code_ts is null or rec_hi.new_code_ts is null then
                 str_l_err_flag := 'Y';
                 goto l_EndOfCheck;
              end if;
              --报文BSG051中商品料件号不为空
              if rec_hi.goods_id is not null then
                 select count(1) into i_l_row
                   from ems_list a
                  where a.ems_no = rec_hi.ems_no
                    and a.code_ts = rec_hi.code_ts
                    and a.goods_id = rec_hi.goods_id;
                 --比对旧商品HS编码是否一致，一致，更新商品编码为新商品HS编码
                 if i_l_row >0 then
                    update ems_list a
                       set a.cus_code = rec_hi.new_code_ts
                     where a.ems_no = rec_hi.ems_no
                       and a.goods_id = rec_hi.goods_id;

                    --写日志
                    insert into ems_list_hscode_log
                          (ems_no, goods_id, stock_num, stock_unit, amount_to_add, amount_to_reduce,
                           g_name_en, g_model, decl_price, curr, code_ts, g_name_cn, cus_code, cus_name,
                           new_code_ts, new_stock_unit, new_stock_num, operator, op_time
                           )
                    select ems_no, goods_id, stock_num, stock_unit, amount_to_add, amount_to_reduce,
                           g_name_en, g_model, decl_price, curr, rec_hi.code_ts, g_name_cn, cus_code, cus_name,
                           cus_code, null, null, str_in_opuser, sysdate
                      from ems_list a
                     where a.ems_no = rec_hi.ems_no
                       and a.goods_id = rec_hi.goods_id;
                 else
                    --不一致，审核不通过
                    str_l_err_flag := 'Y';
                 end if;
                 goto l_EndOfCheck;
              else
              --商品料件号为空,更新集合中所有商品旧HS编码为新商品HS编码
                update ems_list a
                   set a.cus_code = rec_hi.new_code_ts
                 where a.code_ts = rec_hi.code_ts
                   and a.ems_no = rec_hi.ems_no;
                --未找到
                if sql%rowcount = 0 then
                   str_l_err_flag := 'Y';
                else
                   null;
                end if;
                goto l_EndOfCheck;
              end if;
           end if;
           --4)如果调整标记为“只修改计量单位”
           if rec_hi.change_mark = 'U' then
              --中法定第一计量单位为空，发送审核不通过回执（BSG052报文）
              if rec_hi.unit_1 is null then
                 str_l_err_flag := 'Y';
                 goto l_EndOfCheck;
              end if;
              --报文BSG051中商品料件号不为空
              if rec_hi.goods_id is not null then
                 --更新电子底账 库存计量单位
                 update ems_list a
                    set a.stock_unit = rec_hi.unit_1
                  where a.ems_no = rec_hi.ems_no
                    and a.goods_id = rec_hi.goods_id;
                 --未找到
                 if sql%rowcount = 0 then
                    str_l_err_flag := 'Y';
                 end if;

                 goto l_EndOfCheck;
              else
                 --旧商品HS编码为空，发送审核不通过回执（BSG052报文）
                 if rec_hi.code_ts is null then
                    str_l_err_flag := 'Y';
                 else
                 --旧商品HS编码为空
                 --更新电子底账 库存计量单位
                    update ems_list a
                       set a.stock_unit = rec_hi.unit_1
                     where a.ems_no = rec_hi.ems_no
                       and a.goods_id = rec_hi.goods_id;
                    --未找到
                    if sql%rowcount = 0 then
                       str_l_err_flag := 'Y';
                    end if;
                 end if;
                 goto l_EndOfCheck;
              end if;
           end if;
           --5)如果调整标记为空
           if rec_hi.change_mark is null then
              if rec_hi.goods_id is not null and rec_hi.code_ts is not null and rec_hi.new_code_ts is not null
                 and rec_hi.unit_1 is not null and rec_hi.stock_num is not null and rec_hi.change_mark is not null
                 and rec_hi.change_mark is not null and rec_hi.verify_flag is not null then

                 select count(1) into i_l_row
                   from ems_list a
                  where a.stock_num != 0
                    and a.ems_no = rec_hi.ems_no
                    and a.goods_id = rec_hi.goods_id;
                 --库存数量不等于0
                 if i_l_row > 0 then
                    update ems_list a
                       set a.stock_unit = rec_hi.unit_1,a.code_ts = rec_hi.new_code_ts,
                           a.stock_num = rec_hi.stock_num
                     where a.code_ts = rec_hi.code_ts
                       and a.ems_no = rec_hi.ems_no
                       and a.goods_id = rec_hi.goods_id;
                    --比对旧商品HS编码是否一致，一致，更新商品编码为新商品HS编码，同时更新法定第一计量单位和库存数量，不一致，发送审核不通过回执（BSG052报文）。   
                    if sql%rowcount = 0 then
                       if sql%rowcount = 0 then
                          str_l_err_flag := 'Y';
                       end if;
                    end if;    
                 end if;
                /* --未找到
                 if sql%rowcount = 0 then
                    str_l_err_flag := 'Y';
                 end if;*/
              else
                    str_l_err_flag := 'Y';
              end if;
              goto l_EndOfCheck;
           end if;
       <<l_EndOfCheck>>
       --审核不通过的商品
       if str_l_err_flag = 'Y' then
          update hscode_bsg051 a
             set a.verify_flag = 'L'   --审核不通过的临时状态
           where a.verify_flag = 'M'   --临时状态
             and a.seq_id = rec_hi.seq_id;
          
          --审核不通过的流水号
          str_l_seq_id := str_l_seq_id || rec_hi.seq_id || ',';   
       end if;
       end loop;

       --发送bsg052 回执
       --通过回执
       select count(1) into i_l_row from hscode_bsg051 a where a.verify_flag = 'M';
       if i_l_row > 0 then
          i_l_row := f_send_bsg052(str_l_bsg052,'Y','M');

          if i_l_row >0 then
             update hscode_bsg051 a
                set a.verify_flag = 'Y'   --审核通过
              where a.verify_flag = 'M';  --临时状态
          end if;

       end if;
       --审核不通过的不处理
       if str_l_err_flag = 'Y' then
          --i_l_row := f_send_bsg052(str_l_bsg052,'N','M');
          update hscode_bsg051 a
             set a.verify_flag = 'N'   --审核不通过
           where a.verify_flag = 'L';  --审核不通过临时状态
       end if;
       
       --不通过单的提示信息
       if str_l_seq_id is not null then
          str_l_seq_id := substr(str_l_seq_id,1,length(str_l_seq_id)-1);
          str_o_msg := '序列号为:'|| str_l_seq_id ||'无法满足审核通过条件!';
       end if;
    end if;
    --成功执行
    str_o_result := 'Y';

exception when others then
    str_l_errmsg := sqlcode || ' ' || sqlerrm;
    str_l_errmsg := substr(str_l_errmsg, 1, 254);
end p_edi_verify_bsg051;