ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ITEM_COST最后一次采购单价获取

ITEM_COST最后一次采购单价获取

原创 Linux操作系统 作者:gsslww 时间:2010-09-16 10:26:40 0 删除 编辑

此问题已经不是第一次遗忘了,以示警戒,有此日志,加油……

select cstplpop.po_price(organization_id ,inventory_item_id) from dual

--cstplpop package body as follows

CREATE OR REPLACE PACKAGE BODY CSTPLPOP AS
/* $Header: CSTLPOPB.pls 115.5 2003/06/10 22:53:59 htu ship $ */
  FUNCTION po_price(l_org_id NUMBER, l_item_id NUMBER) RETURN NUMBER IS
    po_price_tmp NUMBER ;
    max_transaction_date_tmp DATE;
    transaction_id_tmp       NUMBER;

    BEGIN
      SELECT
      Max(Trunc(transaction_date)) transaction_date
      INTO
      max_transaction_date_tmp
      FROM
      mtl_material_transactions mmt3
      WHERE
      mmt3.organization_id = l_org_id AND
      mmt3.inventory_item_id = l_item_id AND
      mmt3.transaction_source_type_id = 1 AND
      mmt3.transaction_type_id = 18;

      if (max_transaction_date_tmp is null) then
 return 0;
      end if;

      SELECT
      To_number(Substr(MAX(To_char(mmt2.creation_date,
      'YYYY-MM-DD-HH24-MI-SS:') ||
      To_char(mmt2.transaction_id)), 21))
      INTO
      transaction_id_tmp
      FROM
      mtl_material_transactions mmt2
      WHERE
      mmt2.organization_id = l_org_id AND
      mmt2.inventory_item_id = l_item_id AND
      mmt2.transaction_type_id = 18 AND
      mmt2.transaction_source_type_id = 1 AND
      mmt2.transaction_date BETWEEN max_transaction_date_tmp
      AND max_transaction_date_tmp + 1;

      SELECT
      mmt.transaction_cost
      INTO
      po_price_tmp
      FROM
      mtl_material_transactions mmt
      WHERE
      mmt.transaction_id = transaction_id_tmp;

      RETURN po_price_tmp;
    END;
END CSTPLPOP;

 

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/15218514/viewspace-674111/ ,如需转载,请注明出处,否则将追究法律责任。

上一篇: Workflow学习
下一篇: EBS-FA-STUDY 100916-01
请登录后发表评论 登录
全部评论

注册时间:2009-09-03

  • 博文量
    18
  • 访问量
    26806