Salim berbagi…. tempat belajar dan berinteraksi….


Function – Get Current Unit Cost
November 8, 2011, 6:26 AM
Filed under: ORACLE

Purpose: Custom Stored Function for getting current unit cost of certain item on each organization

Module: Inventory. Oracle EBS

 

CREATE OR REPLACE FUNCTION Get_current_unit_cost_func (
   v_period    VARCHAR2,
   v_item_id   NUMBER,
   v_org       NUMBER
)
   RETURN NUMBER
IS
   v_trx_id   NUMBER;
   v_cost     NUMBER;
BEGIN
   SELECT MAX (transaction_id)
     INTO v_trx_id
     FROM mtl_material_transactions
    WHERE TO_CHAR (transaction_date, ‘rrrrmm’) = v_period
      AND inventory_item_id = v_item_id
      AND organization_id = v_org;

   SELECT new_cost
     INTO v_cost
     FROM mtl_cst_actual_cost_details
    WHERE transaction_id = v_trx_id;

   RETURN v_cost;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 0;
END;


Leave a Comment so far
Leave a comment



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



%d bloggers like this: