create or replace package C_ISCpkg is
  --FUNCTION C_ISIF(segment1 VARCHAR2, p_transaction_id NUMBER) RETURN NUMBER;
  procedure C_ISprocedure(beginqty            out number,
                          beginamount         out number,
                          currentperiodqty    out number,
                          currentperiodamount out number,
                          p_Consumptionqty    out number,
                          endqty              out number,
                          endamount           out number,
                          /*QUANTITY            IN NUMBER,*/
                          ARTNO IN VARCHAR2,
                          transfer_subinventory    IN varchar2,
                          startdate in date,
                          enddate   in date);
end C_ISCpkg;

 

 

create or replace package body C_ISCpkg is
/*  FUNCTION C_ISIF(segment1 VARCHAR2, p_transaction_id NUMBER) RETURN NUMBER IS
    v1 NUMBER;
  BEGIN
    select count(*)
      INTO v1
      from C_ISTableTEMP CT
     WHERE  CT.TARTNO=segment1 and CT.TRANSACTION_ID=p_transaction_id;
    RETURN v1;
END C_ISIF;*/

procedure C_ISprocedure(  beginqty            out number,
                          beginamount         out number,
                          currentperiodqty    out number,
                          currentperiodamount out number,
                          p_Consumptionqty    out number,
                          endqty              out number,
                          endamount           out number,
                          /* QUANTITY            IN NUMBER,*/
                          ARTNO IN VARCHAR2,
                          transfer_subinventory    IN varchar2,
                          startdate in date,
                          enddate   in date) IS
    p_Consumptionamount number;
    CUT                 INT;
    CUT2                INT;
    cibperiodCUT        INT;
    p_junqty            number;
    p_junamount         number;
    p_endamount         number;--上月金额
    p_price             number;--平均单价
    pmmt_date varchar2(4000);
    p_enqty number;
    juncut   int;
    CIBCUT   INT;
    CIBCUT2  int;
    a  varchar2(4000);
cursor rec_Current is --------------每月本期购进总数量、总金额
 select SUM(mmt.primary_quantity) AS beginqty,
        SUM(mmt.primary_quantity *
            DECODE(MTA.PRIMARY_QUANTITY,
                   0,
                   NULL,
                   NULL,
                   NULL,
                   decode(MTA.ACCOUNTING_LINE_TYPE,
                          1,
                          MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                          14,
                          MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                          3,
                          MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                          ABS(MTA.BASE_TRANSACTION_VALUE /
                              MTA.PRIMARY_QUANTITY)))) AS beginamount,                          
        to_char(MMT.transaction_date, 'YYYY') ||
        to_char(MMT.transaction_date, 'MM') AS Currentdate
   from MTL_MATERIAL_TRANSACTIONS mmt,
        mtl_system_items_b        msib,
        mtl_transaction_types     mtt,
        MTL_TRANSACTION_ACCOUNTS  MTA
  WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
    AND MSIB.ORGANIZATION_ID = 83
    and msib.segment1 = ARTNO
    and mmt.transaction_type_id = mtt.transaction_type_id
    AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
    AND MTA.ACCOUNTING_LINE_TYPE = 5
    and mtt.transaction_type_name in ('PO Receipt', 'Return to Vendor','期初开帐')
    and mmt.transaction_date between to_date('2010-07-01', 'yyyy-mm-dd') and
        enddate + 1
  group by to_char(mmt.transaction_date, 'YYYY') ||
           to_char(mmt.transaction_date, 'MM')
  ORDER BY to_char(mmt.transaction_date, 'YYYY') ||
           to_char(mmt.transaction_date, 'MM');

 
cursor rec_Consumption is-------------------每月耗用
 select sum(MMT.PRIMARY_QUANTITY) AS Consumptionqty,
       to_char(mmt.transaction_date, 'YYYY') ||
       to_char(mmt.transaction_date, 'MM') AS ConsumptionDate
  from MTL_MATERIAL_TRANSACTIONS mmt,
       mtl_system_items_b        msib,
       mtl_transaction_types     mtt
 WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = 83
   and msib.segment1 =ARTNO
   and mmt.transaction_type_id = mtt.transaction_type_id
   and mtt.transaction_type_name in
       ('WIP Issue',
        'WIP Return',
        'Account alias issue',
        'Account alias receipt')
   and mmt.transaction_date between to_date('2010-07-01', 'yyyy-mm-dd') and enddate+1
 group by to_char(mmt.transaction_date, 'YYYY') ||
          to_char(mmt.transaction_date, 'MM')
          ORDER BY to_char(mmt.transaction_date, 'YYYY') ||
          to_char(mmt.transaction_date, 'MM');
     
  begin
 
                 
--------------------期初开帐 购进 

select count(*)     into juncut
  from MTL_MATERIAL_TRANSACTIONS mmt,
       mtl_system_items_b        msib,
       mtl_transaction_types     mtt
 WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = 83
   and msib.segment1 =ARTNO
   and mmt.transaction_type_id = mtt.transaction_type_id
   and mmt.transaction_source_id=12
  -- and mtt.transaction_type_name='Account alias receipt'
   and mtt.transaction_type_name in ('Account alias receipt','Account alias issue')
   and to_char(MMT.transaction_date, 'YYYY')||to_char(MMT.transaction_date, 'MM')='201006';
  
if juncut>0 /*and  juncut<=1*/then
select SUM(mmt.primary_quantity) AS p_junqty,
       SUM(mmt.primary_quantity * mmt.Actual_Cost) AS p_junamount,
       to_char(MMT.transaction_date, 'YYYY') ||
       to_char(MMT.transaction_date, 'MM') mmt_date
        into p_junqty, p_junamount,pmmt_date
  from MTL_MATERIAL_TRANSACTIONS mmt,
       mtl_system_items_b        msib,
       mtl_transaction_types     mtt
 WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = 83
   and msib.segment1 =ARTNO
   and mmt.transaction_type_id = mtt.transaction_type_id
   and mmt.transaction_source_id=12
--   and mtt.transaction_type_name='Account alias receipt'
   and mtt.transaction_type_name in ('Account alias receipt','Account alias issue')
   and to_char(MMT.transaction_date, 'YYYY')||to_char(MMT.transaction_date, 'MM')='201006'
group by  to_char(MMT.transaction_date, 'YYYY') ||
       to_char(MMT.transaction_date, 'MM');  
      
select count(*) INTO cibperiodCUT from C_ISTableBEGINTEMP  cib where  cib.ttype='期初' and cib.tartno=artno;
  if cibperiodCUT <=0 THEN
  insert into C_ISTableBEGINTEMP
  values
    (pmmt_date,
     artno,
     p_junqty,
     p_junamount,
     '期初');
     commit;
  END IF;
          
elsif juncut<=0 then
      p_junqty:=0;
      p_junamount:=0;     
end if; 
 
-------------------------------循环insert每月期初至临时表C_ISTableBEGINTEMP
endqty:=p_junqty;
dbms_output.put_line('6期初：'||endqty);

for rec in (select period_start_date,open_flag from org_acct_periods where period_start_date between to_date('2010-07-01','yyyy-mm-dd') and  startdate ORDER BY period_start_date) loop

/**判断当前月份是否为空**/
select count(*) into cut
  from MTL_MATERIAL_TRANSACTIONS mmt,
       mtl_system_items_b        msib,
       mtl_transaction_types     mtt
 WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = 83
   and msib.segment1 =ARTNO
   and mmt.transaction_type_id = mtt.transaction_type_id
   and mtt.transaction_type_name in('PO Receipt','Return to Vendor','期初开帐')
   and to_char(mmt.transaction_date, 'YYYY') ||
       to_char(mmt.transaction_date, 'MM')=to_char(rec.period_start_date, 'YYYY') || to_char(rec.period_start_date, 'MM');  
if cut>0 then   --当前月份不为空时则查询 本期购进数量 和 本期金额

select SUM(mmt.primary_quantity) AS benginqty,
       SUM(mmt.primary_quantity *
           DECODE(MTA.PRIMARY_QUANTITY,
                  0,
                  NULL,
                  NULL,
                  NULL,
                  decode(MTA.ACCOUNTING_LINE_TYPE,
                         1,
                         MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                         14,
                         MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                         3,
                         MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                         ABS(MTA.BASE_TRANSACTION_VALUE /
                             MTA.PRIMARY_QUANTITY)))) AS beginamount
  into currentperiodqty, currentperiodamount/*,p_typename*/
  from MTL_MATERIAL_TRANSACTIONS mmt,
       mtl_system_items_b        msib,
       mtl_transaction_types     mtt,
       MTL_TRANSACTION_ACCOUNTS  MTA
 WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = 83
   AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
   AND MTA.ACCOUNTING_LINE_TYPE = 5
   and msib.segment1 = ARTNO
   and mmt.transaction_type_id = mtt.transaction_type_id
   and mtt.transaction_type_name in ('PO Receipt', 'Return to Vendor','期初开帐')
   and to_char(mmt.transaction_date, 'YYYY') ||
       to_char(mmt.transaction_date, 'MM') =
       to_char(rec.period_start_date, 'YYYY') ||
       to_char(rec.period_start_date, 'MM')
 group by to_char(mmt.transaction_date, 'YYYY') ||
          to_char(mmt.transaction_date, 'MM')
 ORDER BY to_char(mmt.transaction_date, 'YYYY') ||
          to_char(mmt.transaction_date, 'MM');
         
       dbms_output.put_line('日期：'|| to_char(rec.period_start_date, 'YYYY') ||
       to_char(rec.period_start_date, 'MM')); 
       dbms_output.put_line('本期进货数量：'||currentperiodqty); 
elsif cut<=0 then--当前月份无数据则赋值 本期数量 和 本期金额 为0
  currentperiodqty:=0;
  currentperiodamount:=0;
end if; 

---------------查询当前月份耗用是否为空 
select count(*) into cut2
  from MTL_MATERIAL_TRANSACTIONS mmt,
       mtl_system_items_b        msib,
       mtl_transaction_types     mtt
 WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = 83
   and msib.segment1 =ARTNO
   and mmt.transaction_type_id = mtt.transaction_type_id
   and mtt.transaction_type_name in
       ('WIP Issue',
        'WIP Return',
        'Account alias issue',
        'Account alias receipt')
   and to_char(mmt.transaction_date, 'YYYY') ||
       to_char(mmt.transaction_date, 'MM') =to_char(rec.period_start_date, 'YYYY') || to_char(rec.period_start_date, 'MM');
if cut2>0 then --当前月份不为空则查询 本期耗用数量    
 select sum(MMT.PRIMARY_QUANTITY) AS Consumptionqty
   into p_Consumptionqty
   from MTL_MATERIAL_TRANSACTIONS mmt,
        mtl_system_items_b        msib,
        mtl_transaction_types     mtt
  WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
    AND MSIB.ORGANIZATION_ID = 83
    and msib.segment1 = ARTNO
    and mmt.transaction_type_id = mtt.transaction_type_id
    and mtt.transaction_type_name in
        ('WIP Issue',
         'WIP Return',
         'Account alias issue',
         'Account alias receipt')
    and to_char(mmt.transaction_date, 'YYYY') ||
        to_char(mmt.transaction_date, 'MM') =
        to_char(rec.period_start_date, 'YYYY') ||
        to_char(rec.period_start_date, 'MM')
  group by to_char(mmt.transaction_date, 'YYYY') ||
           to_char(mmt.transaction_date, 'MM')
  ORDER BY to_char(mmt.transaction_date, 'YYYY') ||
           to_char(mmt.transaction_date, 'MM');
          dbms_output.put_line('日期2：'|| to_char(rec.period_start_date, 'YYYY') ||
       to_char(rec.period_start_date, 'MM')); 
          dbms_output.put_line('本期耗用数量：'||p_Consumptionqty);           
elsif cut2<=0then--无数据则赋值 本期耗用数量 为0
  p_Consumptionqty:=0;
end if;     
   
  dbms_output.put_line('日期：'||to_char(rec.period_start_date,'yyyy-mm-dd'));
 select COUNT(*) INTO CIBCUT
   from C_ISTableBEGINTEMP cib
  where cib.tartno = artno
    and cib.period =to_char(add_months(rec.period_start_date, -1), 'yyyymm');
   
IF CIBCUT>0 THEN
 select cib.tendqty, cib.tendamount
   into p_enqty, p_endamount
   from C_ISTableBEGINTEMP cib
  where cib.tartno = artno
    and cib.period =
        to_char(add_months(rec.period_start_date, -1), 'yyyymm');

ELSIF CIBCUT<=0 THEN
p_enqty:=p_junqty;
p_endamount:=p_junamount;
END IF;   
    dbms_output.put_line('6月期末金额：'||p_junamount);
  dbms_output.put_line('上月金额：'||p_endamount);
  dbms_output.put_line(' 本期金额：'||currentperiodamount);
  dbms_output.put_line('上月数量：'||p_enqty);
  dbms_output.put_line('本期数量：'||currentperiodqty);

--------------------------期末数量--------------------------------
if endamount<=0 or endamount is null then
endamount:=0;
end if; 

if currentperiodqty>0  and  p_Consumptionqty is not null then
  --上月期末数量+当前数量-本期耗用数量=期末数量
 endqty:=endqty+currentperiodqty+p_Consumptionqty;-- +因为负数
   dbms_output.put_line('期末数量：'||endqty);
   
       /**平均单价**//* 平均单价=上月金额+本期金额/(上月数量+本期数量) **/
 p_price:=(endamount+currentperiodamount)/(p_enqty+currentperiodqty);
      dbms_output.put_line(' 本月耗用：'||p_Consumptionqty);
      dbms_output.put_line('平均单价：'||p_price);
    
      /**本期耗用金额**//* 耗用金额=本期耗用数量*平均单价 **/
 p_Consumptionamount:=-(p_Consumptionqty)*p_price;
    -- dbms_output.put_line('本期耗用金额：'||p_Consumptionamount);
    
      /**期末金额**//*期末金额=上月金额+本期金额-耗用金额**/
 endamount:=endamount+currentperiodamount-p_Consumptionamount;
 /* dbms_output.put_line('不等于空！'||currentperiodqty);*/
 
else
if p_enqty>0  or  currentperiodqty>0 then
dbms_output.put_line('大于O！！！！！！：'||endamount);
  endqty:=endqty+currentperiodqty+p_Consumptionqty;
  p_price:=(endamount+currentperiodamount)/(p_enqty+currentperiodqty);
/*      dbms_output.put_line(' 本月耗用：'||p_Consumptionqty);
      dbms_output.put_line('平均单价：'||p_price);*/
    
      /**本期耗用金额**//* 耗用金额=本期耗用数量*平均单价 **/
 p_Consumptionamount:=-(p_Consumptionqty)*p_price;
    -- dbms_output.put_line('本期耗用金额：'||p_Consumptionamount);
    
      /**期末金额**//*期末金额=上月金额+本期金额-耗用金额**/
 endamount:=endamount+currentperiodamount-p_Consumptionamount;
 dbms_output.put_line('期末金额！！！！：'||endamount);
end if;

end if;
    
  
  
   select count(*)
     into cut
     from C_ISTableBEGINTEMP cib
    where cib.tartno = artno
      and cib.period =to_char(rec.period_start_date, 'YYYY') || to_char(rec.period_start_date, 'MM');
   if cut <=0 then--判断表中是否已经存在
     if to_char(rec.period_start_date, 'YYYY') || to_char(rec.period_start_date, 'MM')!=to_char(startdate, 'YYYY') || to_char(startdate,'MM') THEN
    insert into C_ISTableBEGINTEMP values
      (to_char(rec.period_start_date, 'YYYY') || to_char(rec.period_start_date, 'MM'),
       artno,
       endqty,
       endamount,
       '期初');
       commit;
       END IF;
   end if;
  
--end if;    
end loop;

select  count(*) into CIBCUT2
    from C_ISTableBEGINTEMP cib
   where cib.tartno = ARTNO
     and cib.period =
         to_char(startdate, 'YYYY') || to_char(startdate, 'MM')-1;
if CIBCUT2>0 then
  select cib.tendqty, cib.tendamount--返回期末数量、金额
    into beginqty, beginamount
    from C_ISTableBEGINTEMP cib
   where cib.tartno = ARTNO
     and cib.period =
         to_char(startdate, 'YYYY') || to_char(startdate, 'MM')-1;
elsif CIBCUT2<=0 then
beginqty:=0;
beginamount:=0;
end if; 
    
  end C_ISprocedure;
end C_ISCpkg;

 

update ing....

 

 

 

 

-----调用---------
declare
  beginqty               number;
  beginamount            number;
  endqty                 number;
  endamount              number;
  currentperiodqty       number;
  currentperiodamountout number;
  p_Consumptionqty       number;
begin
  C_ISCPKG.C_ISprocedure(beginqty,
                         beginamount,
                         currentperiodqty,
                         currentperiodamountout,
                         p_Consumptionqty,
                         endqty,
                         endamount,
                    /*      1200,*/
                         货号,
                         /*TO_DATE('2011-04-08 13:40:00','YYYY-MM-DD HH24:MI:SS'),*/
                         TO_DATE('2011-05-01', 'YYYY-MM-DD'),
                         TO_DATE('2011-05-31', 'YYYY-MM-DD'));

  dbms_output.put_line('期初库存:'||beginqty);
  dbms_output.put_line('本期进货:'||currentperiodqty);
  dbms_output.put_line('本期耗用'||p_Consumptionqty);
--  dbms_output.put_line(beginamount);
  dbms_output.put_line('期末数量：'||endqty);
  dbms_output.put_line('期末金额'||endamount);
end;