create or replace package C_standingcropspkg is
  procedure C_standingcropspprocedure(artno     in varchar2,
                                      lotnumber in varchar2,
                                      STOCK     in  varchar2,
                                      NO        OUT VARCHAR2);
end C_standingcropspkg;

create or replace package body C_standingcropspkg is
  procedure C_standingcropspprocedure(artno     in varchar2,
                                      lotnumber in varchar2,
                                      STOCK    in   varchar2,
                                      NO        OUT VARCHAR2) is
    p_artno     varchar2(4000);
    p_lotnumber varchar2(4000);
    p_stock     varchar2(4000);
  --  P_NO        varchar2(4000);
    p_STRALL    varchar2(4000);
    TMAX_NO     varchar2(4000);
    P_NUM       varchar2(4000);
    cut  int;
  begin

    --------------货号----------------------
    SELECT distinct msib.segment1
      into p_artno
      FROM MTL_SYSTEM_ITEMS_b    msib,
           MTL_ONHAND_QUANTITIES moq,
           MTL_ITEM_CATEGORIES   MIC,
           MTL_CATEGORIES_B      MCB
     where msib.inventory_item_id = moq.INVENTORY_ITEM_ID
       and msib.organization_id = moq.ORGANIZATION_ID
       and mic.inventory_item_id = msib.inventory_item_id
       and mic.category_set_id = '1100000041'
       AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
          --   AND MCB.SEGMENT1 = '成品'
       and mic.organization_id = msib.organization_id
       and moq.SUBINVENTORY_CODE in ('子库存')
       and msib.organization_id = :org
       and msib.segment1 = artno
       and moq.LOT_NUMBER = lotnumber
       and moq.SUBINVENTORY_CODE = stock;
       dbms_output.put_line('货号:' || p_artno);
    -----------------批号----------------------
    SELECT distinct moq.LOT_NUMBER
      into p_lotnumber
      FROM MTL_SYSTEM_ITEMS_b    msib,
           MTL_ONHAND_QUANTITIES moq,
           MTL_ITEM_CATEGORIES   MIC,
           MTL_CATEGORIES_B      MCB
     where msib.inventory_item_id = moq.INVENTORY_ITEM_ID
       and msib.organization_id = moq.ORGANIZATION_ID
       and mic.inventory_item_id = msib.inventory_item_id
       and mic.category_set_id = '1100000041'
       AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
       and mic.organization_id = msib.organization_id
       and moq.SUBINVENTORY_CODE in ('子库存')
       and msib.organization_id = :org
       and msib.segment1 = artno
       and moq.LOT_NUMBER = lotnumber
       and moq.SUBINVENTORY_CODE = stock;
        dbms_output.put_line('批号:' || p_lotnumber);
    -----------------子库存----------------------
    SELECT distinct moq.SUBINVENTORY_CODE
      into p_stock
      FROM MTL_SYSTEM_ITEMS_b    msib,
           MTL_ONHAND_QUANTITIES moq,
           MTL_ITEM_CATEGORIES   MIC,
           MTL_CATEGORIES_B      MCB
     where msib.inventory_item_id = moq.INVENTORY_ITEM_ID
       and msib.organization_id = moq.ORGANIZATION_ID
       and mic.inventory_item_id = msib.inventory_item_id
       and mic.category_set_id = '1100000041'
       AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
          --   AND MCB.SEGMENT1 = '成品'
       and mic.organization_id = msib.organization_id
       and moq.SUBINVENTORY_CODE in ('子库存')
       and msib.organization_id =:org
       and msib.segment1 = artno
       and moq.LOT_NUMBER = lotnumber
       and moq.SUBINVENTORY_CODE = stock;
  dbms_output.put_line('子库存:' || p_stock);
    -------------临时表------------------------
    select COUNT(*)
      into cut
      from c_standingcropsTemp cst
     where cst.artno = p_artno
       and cst.lotnumber = p_lotnumber
       and cst.stock = p_stock;
    dbms_output.put_line('临时表NO,判断是否为空时:' || CUT);
    IF cut<=0 THEN
      SELECT MAX(CST.NO) INTO TMAX_NO from c_standingcropsTemp cst;
       dbms_output.put_line('TMAX_NO,判断是否为空时:' || TMAX_NO);
      IF TMAX_NO IS NULL THEN
        ---当临时表无最大序列值时
        P_NUM    := 1;
        p_STRALL := LPAD(1, 5, '0');
        -- RETURN p_STRALL;
        dbms_output.put_line('TMAX_NO为空:' || p_STRALL);
      ELSIF TMAX_NO IS NOT NULL THEN
        P_NUM    := TMAX_NO + 1;
        p_STRALL := LPAD(P_NUM, 5, '0');
        -- RETURN p_STRALL;
        dbms_output.put_line('TMAX_NO不为空:' || p_STRALL);
      END IF;
      INSERT INTO c_standingcropsTemp
      VALUES
        (P_ARTNO, P_LOTNUMBER, P_STOCK, p_STRALL);
      COMMIT;
      ------------返回序列值------------
      select CST.NO
        into NO
        from c_standingcropsTemp cst
       where cst.artno = p_artno
         and cst.lotnumber = p_lotnumber
         and cst.stock = p_stock;
     -- NO := P_NO;
      dbms_output.put_line('返回序列后NO:' || NO);

    ELSIF cut>0 THEN
      --如果已经存在
       select CST.NO
        into NO
        from c_standingcropsTemp cst
       where cst.artno = p_artno
         and cst.lotnumber = p_lotnumber
         and cst.stock = p_stock;
    --  NO := P_NO;
      dbms_output.put_line('返回序列后NO:' || NO);
    END IF;

  end C_standingcropspprocedure;
end C_standingcropspkg;

/*
创建临时表
create  table c_standingcropsTemp
(
--ID    NUMBER,
artno varchar2(4000),
lotnumber varchar2(4000),
stock varchar2(4000),
NO   varchar2(4000)
)
select *  from c_standingcropsTemp*/

-----调用---------
/*
declare
  p_NO               varchar2(4000);
begin
  C_standingcropspkg.C_standingcropspprocedure

  ('货号','批号','子库存',p_NO);

  dbms_output.put_line('sssssssss:'||p_NO);
end;
*/