根据MRP建议对采购订单需求日期的建议修改采购订单需求日期 (续)

根据MRP建议对采购订单需求日期进行系统更改,但是出现了一个问题:更改需求日期的时候发现采购单价被改了。

出现单价被自动更改有以下前提条件:

1、此采购单价是由来源单据带出的,比方说报价单;

2、当采购订单审批之后,报价单的价格被更改,此时对此采购订单行做更改就会自动更新成报价单的新价格。

但是采购员认为报价单的价格是以后新的价格,之前的单子不应该更改价格。

如是修改代码如下:

在更新前检查此采购单价是否由报价单带出,如果是,再检查采购单价与报价单价格是否相同,如果不同,

则在更改需求日期的同时修改采购单价为采购单的价格,也就是更新采购单价为原来的价格。

code:

CREATE OR REPLACE Package Body JWPOP002 Is
/*created by jam huang 20151201 根据MRP自动更新采购订单的需求日期*/
 procedure auto_update_need_date(Errbuf        Out Varchar2,
                                 Errcode       Out Varchar2,
                                 P_ORG_ID NUMBER) is
   
   CURSOR C_HEADER IS 
    SELECT DISTINCT  p.organization_id,p.po_number,p.userid
      FROM jw_update_po_date_temp P;
      
   cursor c_line(v_po_number varchar2) is
    select t.rowid row_id,
           t.organization_id,
           t.po_number,
           t.revision_num,
           t.line_num,
           t.postprocessing_lead_time,
           t.new_need_date
      from jw_update_po_date_temp t
     where t.po_number = v_po_number;
    
   excep exception;
   v_num_1 number := 0 ;
   v_num_2 number := 0 ;
   v_num_3 number := 0 ;
   v_price number;
   i_1 number;
   j number;
   v_APPROVALS varchar2(1);
   v_rev_num number;
   v_resp_id number;
   v_error_num number := 0;
   
   v_conc_num number;
   v_conc_num_1 number;
   
   RESULT       NUMBER ;
   v_api_errors po_api_errors_rec_type;
 begin
   -------防呆--------------
   select count(*)   ---只能在周二三五六运行
     into v_num_1
     from dual
    where to_char(sysdate, 'd') not in ('3','4','6','7');
   if v_num_1 > 0 then
     raise excep;
   end if;
   
   select count(*)
     into v_num_2  ----一天只能跑一次
     from jw_run_update_po_date_flag j
    where j.run_date = trunc(sysdate)
      and j.flag = 'Y';
   if v_num_2 > 0 then
     raise excep;
   end if;
   
   insert into jw_update_po_date_temp_his   ------备份
   select  p.*,sysdate from jw_update_po_date_temp p;
   commit;
   delete from jw_update_po_date_temp;  ------清除
   commit;
   
    INSERT INTO jw_update_po_date_temp
         (organization_id,
          po_number,
          revision_num,
          line_num,
          postprocessing_lead_time,
          new_need_date,
          creation_date,
          userid) 
    select mr.organization_id,
         mipo.po_number,
         pha.revision_num,
         pla.line_num,
         msib.postprocessing_lead_time,
         min(mr.new_schedule_date) new_need_date,
         SYSDATE,
         (select fu.user_id from fnd_user fu where fu.employee_id = pha.agent_id and rownum = 1) userid
    from MRP_ITEM_PURCHASE_ORDERS mipo,
         MRP_RECOMMENDATIONS      mr,
         po_lines_all             pla,
         po_headers_all           pha,
         mtl_system_items_b       msib
    where mipo.transaction_id = mr.disposition_id
     and mr.disposition_status_type = 1
     and trunc(mr.new_schedule_date) <> trunc(mr.old_schedule_date)
     and mr.organization_id = msib.organization_id
     and mr.inventory_item_id = msib.inventory_item_id
     and mipo.line_id = pla.po_line_id
     and pla.po_header_id = pha.po_header_id
     and mipo.po_number = pha.segment1
     AND MR.ORGANIZATION_ID = P_ORG_ID
     and nvl(pla.cancel_flag,'N') <> 'Y'
     AND pla.closed_date is null
     --and mipo.po_number = '20151008014'
     and pha.authorization_status in ('REQUIRES REAPPROVAL','APPROVED')
    group by mr.organization_id,
            mipo.po_number,
            pha.revision_num,
            pla.line_num,
            msib.postprocessing_lead_time,
            pha.agent_id;
    COMMIT;
   
    for v_header in C_HEADER loop
    BEGIN
       select decode(v_header.organization_id,85,50643,86,50647,50643) into v_resp_id from dual;
       fnd_global.apps_initialize(user_id      => v_header.userid,
                             resp_id      => v_resp_id,
                             resp_appl_id => 201);
       MO_GLOBAL.init('SQLAP');
       i_1 := 1;                      
       select count(*) into i_1 from jw_update_po_date_temp t where t.po_number = v_header.po_number;
       j := 0 ;    
       v_APPROVALS := 'N'; 
       v_conc_num_1 := 0;
       LOOP
           v_conc_num_1 := v_conc_num_1 + 1;
         select count(*)
           into v_conc_num
           from FND_CONCURRENT_REQUESTS F
          WHERE F.CONCURRENT_PROGRAM_ID = 45109
            AND F.PHASE_CODE <> 'C';
          exit when (v_conc_num = 0) or (v_conc_num_1 = 20);
          dbms_lock.sleep(3);
       END LOOP;
       if v_conc_num_1 >= 20 then
         raise excep;
       end if;
       for v_line in c_line(v_header.po_number) loop
          j := j + 1;
          if j = i_1 then
            v_APPROVALS := 'Y';
          ELSE
            v_APPROVALS := 'N'; 
          end if;
          SELECT PHA.REVISION_NUM INTO v_rev_num FROM PO_HEADERS_ALL PHA WHERE PHA.SEGMENT1 = v_header.po_number;  
          <span style="color:#ff0000;">--------------add by jam 20160406  出现更改报价单之后采购单价同时也更改的情况
          begin
          select count(*)
            into v_num_3
            from po_headers_all pha, po_lines_all pla1,po_lines_all pla2
           where pha.po_header_id = pla1.po_header_id
             and pla1.line_num = v_line.line_num
             and pla1.from_line_id = pla2.po_line_id
             and pla1.unit_price <> pla2.unit_price
             and pha.segment1 = v_header.po_number;
          if v_num_3 > 0 then
            select pla.unit_price
              into V_PRICE
              from po_headers_all pha, po_lines_all pla
             where pha.po_header_id = pla.po_header_id
               and pla.line_num = v_line.line_num
               and pha.segment1 = v_header.po_number;
          else
            V_PRICE := NULL;
          end if;
          exception
            when others then
              V_PRICE := NULL;
          end;
          ---------------add by jam 20160406  出现更改报价单之后采购单价同时也更改的情况</span>
          RESULT := 0;
          RESULT := PO_CHANGE_API1_S.UPDATE_PO(X_PO_NUMBER           => v_header.po_number,
                                       X_RELEASE_NUMBER      => NULL,
                                       X_REVISION_NUMBER     => v_rev_num,
                                       X_LINE_NUMBER         => v_line.line_num,
                                       X_SHIPMENT_NUMBER     => 1,
                                       NEW_QUANTITY          => NULL,
                                       NEW_PRICE             => <span style="color:#ff0000;">V_PRICE,</span>
                                       NEW_PROMISED_DATE     => NULL,
                                       NEW_NEED_BY_DATE      => v_line.new_need_date - v_line.postprocessing_lead_time,
                                       LAUNCH_APPROVALS_FLAG => v_APPROVALS,
                                       UPDATE_SOURCE         => NULL,
                                       VERSION               => '1.0',
                                       X_OVERRIDE_DATE       =>  NULL,
                                       X_API_ERRORS          => v_api_errors,
                                       p_BUYER_NAME          => NULL, /* Bug:2986718 */
                                       -- <INVCONV R12 START>
                                       p_secondary_quantity => NULL,
                                       p_preferred_grade    => NULL/*,
                                       -- <INVCONV R12 END>
                                       p_org_id => 81*/);
         IF (RESULT = 1) THEN
           update jw_update_po_date_temp d
              set d.flag = 'Y'
             where d.rowid = v_line.row_id;
           commit;
         else
            ROLLBACK;
           update jw_update_po_date_temp d
              set d.flag = 'E'
             where d.rowid = v_line.row_id;
           commit;
         end if;
         IF v_APPROVALS = 'Y' THEN
            dbms_lock.sleep(7);--休息7s让PO通信输出跑完
         END IF;
       end loop;
    EXCEPTION
      WHEN OTHERS THEN
        v_error_num := v_error_num + 1;
    END;
    end loop;
    if v_error_num > 0 then
       INSERT INTO jw_run_update_po_date_flag(run_date,flag,userid)
       VALUES(TRUNC(SYSDATE),'E',FND_PROFILE.VALUE('USER_ID'));
       COMMIT;
    else
       INSERT INTO jw_run_update_po_date_flag(run_date,flag,userid)
       VALUES(TRUNC(SYSDATE),'Y',FND_PROFILE.VALUE('USER_ID'));
       COMMIT;
    end if;
 exception
   when excep THEN
      ROLLBACK;
      INSERT INTO jw_run_update_po_date_flag(run_date,flag,userid)
      VALUES(TRUNC(SYSDATE),'E',FND_PROFILE.VALUE('USER_ID'));
      COMMIT;
      --RAISE_APPLICATION_ERROR(-20001,'运行失败!');
      Errcode := Sqlcode;
      Errbuf  := 'Error->' ||  Sqlerrm(Errcode);
   when others then
      rollback;
      INSERT INTO jw_run_update_po_date_flag(run_date,flag,userid)
      VALUES(TRUNC(SYSDATE),'E',FND_PROFILE.VALUE('USER_ID'));
      COMMIT;
      Errcode := Sqlcode;
      Errbuf  := 'Error->' ||  Sqlerrm(Errcode);
 end;

End JWPOP002;


 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页