ITEM_COST最后一次采购单价获取

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

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博客 ” ，链接：http://blog.itpub.net/15218514/viewspace-674111/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15218514/viewspace-674111/