oracle 存储过程

原创
2019/05/15 20:31
阅读数 178
PROCEDURE Create_Pick_Task(info_                 OUT VARCHAR2,
                           move_no_              IN OUT VARCHAR2,
                           order_no_             IN VARCHAR2,
                           line_no_              IN VARCHAR2,
                           release_no_           IN VARCHAR2,
                           line_item_no_         IN VARCHAR2,
                           user_id_              IN VARCHAR2,
                           order_type_           IN NUMBER) --  -- 1、库内移库 2、仓库间调拨 3、拣货 
IS 
    qty_to_move_              NUMBER;
    max_line_no_              NUMBER := 0;
    contract_                 VARCHAR2(5);
    component_part_           VARCHAR(25);
    backflush_loc_            VARCHAR2(35);
    warehouse_                VARCHAR2(15); 
    ean_no_                   VARCHAR2(35); 
    lot_batch_no_             VARCHAR2(20);
    serial_no_                VARCHAR2(50);
    qty_onhand_               NUMBER;
    sum_qty_onhand_           NUMBER;  
    
    --                        
    CURSOR get_purchase_order_line_comp IS
        SELECT (nvl(qty_required,0) - nvl(qty_issued,0)) ,contract,component_part,backflush_loc
           FROM  purchase_order_line_comp_tab
           WHERE order_no      = order_no_
           AND   line_no       = line_no_
           AND   release_no     = release_no_
           AND   line_item_no  = line_item_no_;
           
     --      
     CURSOR get_line_no IS
           SELECT  MAX(line_no)
                  FROM    m_move_order_line_tab
                  WHERE   move_no = move_no_;
     --
     CURSOR get_inventory_locaiton IS
            SELECT warehouse
            FROM   inventory_location_tab
            WHERE  contract = contract_
            AND    location_no =  backflush_loc_ ;    
     --
     CURSOR get_inventory_part IS
            SELECT ean_no
            FROM  inventory_part_tab
            WHERE part_no = component_part_
            AND   contract = contract_;      
     -- 
     CURSOR get_inventory_part_in_stock IS
            SELECT lot_batch_no , serial_no , qty_onhand  
            FROM inventory_part_in_stock_tab
            WHERE contract = contract_
            AND part_no = component_part_
            AND warehouse = warehouse_ ;
     --
     CURSOR get_sum_stock IS
            SELECT SUM(qty_onhand)
            FROM inventory_part_in_stock_tab
            WHERE contract = contract_
            AND part_no = component_part_
            AND warehouse = warehouse_ ;

      
BEGIN
     OPEN get_purchase_order_line_comp ;
     FETCH get_purchase_order_line_comp INTO qty_to_move_ ,contract_ , component_part_ , backflush_loc_;
     CLOSE get_purchase_order_line_comp;
     
     OPEN get_inventory_locaiton ;
     FETCH get_inventory_locaiton INTO warehouse_;
     CLOSE get_inventory_locaiton;
     
     OPEN get_inventory_part ;
     FETCH get_inventory_part INTO ean_no_;
     CLOSE get_inventory_part;
     
     OPEN get_sum_stock ;
     FETCH get_sum_stock INTO sum_qty_onhand_;
     CLOSE get_sum_stock;
     
     -- 拣货数量大于在库存
     IF sum_qty_onhand_ < qty_to_move_ THEN
        Error_Sys.Record_General(lu_name_, 'QTYNOTALLOW: 拣货数量 :P1 大于在库库存 :P2 创建委外出库拣货任务失败 !',
                                            qty_to_move_,
                                            sum_qty_onhand_);  
     END IF;
     
     IF qty_to_move_ >0 THEN 
       -- 创建移库单头
         IF  nvl(move_no_ ,'') = '' THEN 
          move_no_  := M_Move_Order_Api.Get_Move_No( SYSDATE );
          INSERT
              INTO m_move_order_tab (
                 move_no,
                 moved_date,
                 warehouse,
                 moved_user,
                 order_type,
                 to_warehouse,
                 rowstate,
                 created_date,
                 created_user,
                 updated_date,
                 updated_user,
                 rowversion)
              VALUES (
                 move_no_,
                 SYSDATE,
                 warehouse_,
                 NULL,
                 order_type_,            -- 1、库内移库 2、仓库间调拨 3、拣货 
                 warehouse_,
                 1,            -- 1、已创建 2、拣货中 3、已拣货 4、已完成
                 SYSDATE,
                 user_id_,
                 SYSDATE,
                 user_id_,
                 SYSDATE);
        END IF;
        
        --Fetch 循环 获取库存批次  
        OPEN get_inventory_part_in_stock;--必须要明确的打开和关闭游标  
          LOOP  
            FETCH get_inventory_part_in_stock   INTO lot_batch_no_ ,serial_no_ , qty_onhand_  ;  
            EXIT WHEN get_inventory_part_in_stock%NOTFOUND;  
            IF qty_onhand_ >= qty_to_move_ THEN
               -- 创建明细行
                OPEN  get_line_no;
                FETCH get_line_no INTO max_line_no_;
                CLOSE get_line_no;
                
                INSERT
                  INTO m_move_order_line_tab (
                     move_no,
                     line_no,
                     warehouse,
                     part_no,
                     ean_no,
                     qty_to_move,
                     qty_moved,
                     from_loca_no,
                     to_loca_no,
                     lot_batch_no,
                     barcode_no,
                     created_date,
                     created_user,
                     updated_date,
                     updated_user,
                     rowversion)
                  VALUES (
                     move_no_,
                     max_line_no_ + 1,
                     warehouse_,
                     component_part_,
                     ean_no_,
                     qty_to_move_,
                     0,
                     backflush_loc_,
                     '*',
                     lot_batch_no_,
                     '*',
                     sysdate,
                     user_id_,
                     sysdate,
                     user_id_,
                     sysdate)  ;
                   EXIT;
            ELSE 
                -- 创建明细行
                OPEN  get_line_no;
                FETCH get_line_no INTO max_line_no_;
                CLOSE get_line_no;
                
                INSERT
                  INTO m_move_order_line_tab (
                     move_no,
                     line_no,
                     warehouse,
                     part_no,
                     ean_no,
                     qty_to_move,
                     qty_moved,
                     from_loca_no,
                     to_loca_no,
                     lot_batch_no,
                     barcode_no,
                     created_date,
                     created_user,
                     updated_date,
                     updated_user,
                     rowversion)
                  VALUES (
                     move_no_,
                     max_line_no_ + 1,
                     warehouse_,
                     component_part_,
                     ean_no_,
                     qty_onhand_,
                     0,
                     backflush_loc_,
                     '*',
                     lot_batch_no_,
                     '*',
                     sysdate,
                     user_id_,
                     sysdate,
                     user_id_,
                     sysdate)  ;
                qty_to_move_ :=( qty_to_move_ -   qty_onhand_ );    
            END IF;
            
            
          END LOOP;  
        CLOSE get_inventory_part_in_stock; 
        
     
     END IF;           
END  Create_Pick_Task;

 

 

-- 完成委外叫料拣货
PROCEDURE Finished_Call_Out_Material(info_                 OUT VARCHAR2,
	                                  move_no_              IN VARCHAR2,
                                     department_id_        IN VARCHAR2,
                                     user_id_              IN VARCHAR2)
IS
   max_line_no_         NUMBER;
   barcode_rec_         m_barcode_status_tab%ROWTYPE;
   sum_barcode_qty_     NUMBER;
   qty_issue_           NUMBER := 0;
   qty_remain_          NUMBER := 0;
   contract_            VARCHAR2(5) := User_Allowed_Site_Api.Get_Default_Site;

   CURSOR get_m_move_order_line IS
      SELECT *
      FROM  M_MOVE_ORDER_LINE_TAB  m
      WHERE m.move_no = move_no_
      AND   m.qty_to_move > m.qty_moved ;

   CURSOR get_max_line_no IS
       SELECT MAX(m.line_no)
       FROM M_MOVE_ORDER_LINE_TAB m
       WHERE m.move_no = move_no_ ;

BEGIN
	--  获取移库明细行
	FOR move_line_rec_ IN get_m_move_order_line LOOP

      qty_remain_  := (move_line_rec_.qty_to_move - move_line_rec_.qty_moved) ;

      -- 判断标签库存是否足够
      DECLARE
          CURSOR get_sum_barcode_qty IS
            SELECT SUM(m.qty)
            FROM m_location_no_barcode_tab m
            WHERE m.part_no = move_line_rec_.part_no
            AND   m.location_no = move_line_rec_.from_loca_no
            AND   m.contract = contract_;
      BEGIN
            OPEN get_sum_barcode_qty ;
            FETCH get_sum_barcode_qty INTO sum_barcode_qty_ ;
            CLOSE get_sum_barcode_qty ;

            IF (move_line_rec_.qty_to_move - move_line_rec_.qty_moved) > sum_barcode_qty_ THEN
               Error_Sys.Record_General(lu_name_, 'NOTENOUGHTBARCODESTOCK: 标签库存 :P1 在库数量 :P2 小于移库数量 :P3 !',
                                            move_line_rec_.part_no||'-'||move_line_rec_.from_loca_no,
                                            sum_barcode_qty_,
                                            move_line_rec_.move_no||'-'||move_line_rec_.line_no||'-'||move_line_rec_.qty_to_move - move_line_rec_.qty_moved);
            END IF ;
      END;

      DECLARE
          CURSOR get_m_location_barcode_tab IS
               SELECT *
               FROM m_location_no_barcode_tab m
               WHERE m.part_no = move_line_rec_.part_no
               AND   m.location_no = move_line_rec_.from_loca_no
               AND   m.contract = contract_ ;
      BEGIN
         -- 获取标签库存行执行移库
         FOR loca_barcode_rec_ IN get_m_location_barcode_tab LOOP
            qty_issue_  := least(loca_barcode_rec_.qty , qty_remain_);
            qty_remain_ := qty_remain_ - qty_issue_ ;

            -- 库存下架
            m_inv_part_in_stock_util_api.Issue_Part(part_no_     => move_line_rec_.part_no,
                                              location_no_ => move_line_rec_.from_loca_no,
                                              qty_issued_  => qty_issue_,
                                              user_id_     => user_id_) ;

            -- 库存上架
            m_inv_part_in_stock_util_api.Receive_Part(part_no_      => move_line_rec_.part_no,
                                                      location_no_  => move_line_rec_.to_loca_no,
                                                      qty_received_ => qty_issue_,
                                                      user_id_      => user_id_);
            -- barcode 库存下架
            m_location_no_barcode_api.Issue_Barcode(barcode_no_       => loca_barcode_rec_.barcode_no,
                                                    from_loca_no_     => move_line_rec_.from_loca_no,
                                                    qty_issue_        => qty_issue_,
                                                    transaction_code_ => 'INVM-ISS',
                                                    user_id_          => user_id_ );

            -- barcode 库存上架
            m_location_no_barcode_api.Receive_Barcode(barcode_no_       => loca_barcode_rec_.barcode_no,
                                                      to_loca_no_       => move_line_rec_.to_loca_no,
                                                      qty_received_     => qty_issue_,
                                                      transaction_code_ => 'INVM-IN',
                                                      user_id_          => user_id_);
            OPEN get_max_line_no ;
            FETCH get_max_line_no INTO max_line_no_ ;
            CLOSE get_max_line_no;
            max_line_no_ := nvl(max_line_no_ , 0);

            DECLARE
                CURSOR get_m_barcode_status IS
                     SELECT *
                     FROM m_barcode_status_tab m
                     WHERE m.barcode_no = loca_barcode_rec_.barcode_no
                     AND m.contract = contract_;
            BEGIN
					      OPEN get_m_barcode_status ;
                     FETCH get_m_barcode_status INTO barcode_rec_ ;
                     CLOSE get_m_barcode_status ;
                     -- 插入新的移库行
                     INSERT
                             INTO m_move_order_line_tab (
                                move_no,
                                line_no,
                                warehouse,
                                part_no,
                                ean_no,
                                qty_to_move,
                                qty_moved,
                                from_loca_no,
                                to_loca_no,
                                to_warehouse,
                                lot_batch_no,
                                barcode_no,
                                created_date,
                                created_user,
                                updated_date,
                                updated_user,
                                rowversion,
                                co_order_no,
                                co_line_no,
                                co_release_no,
                                co_line_item_no
                                )
                             VALUES (
                                move_no_,
                                max_line_no_ + 1,
                                move_line_rec_.warehouse,
                                move_line_rec_.part_no,
                                move_line_rec_.ean_no,
                                qty_issue_,
                                qty_issue_,
                                move_line_rec_.from_loca_no,
                                move_line_rec_.to_loca_no,
                                move_line_rec_.to_warehouse,
                                barcode_rec_.lot_batch_no,
                                barcode_rec_.barcode_no,
                                sysdate,
                                user_id_,
                                sysdate,
                                user_id_,
                                sysdate,
                                move_line_rec_.co_order_no,
                                move_line_rec_.co_line_no,
                                move_line_rec_.co_release_no,
                                move_line_rec_.co_line_item_no);
                     -- 完成后,删除原来的行
                     IF qty_remain_ = 0  THEN
                        DELETE FROM m_move_order_line_tab
                        WHERE move_no = move_no_
                        AND line_no  =  move_line_rec_.line_no ;
                        EXIT ;
                     END IF ;
            END;

         END LOOP ;
      END;

   END LOOP ;

   -- 跟新移库单状态
   UPDATE m_move_order_tab m SET m.rowstate=4 WHERE m.move_no = move_no_ ;
END Finished_Call_Out_Material;

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部