Onhand Quantity On a Given Date

Recentlry i have worked on one report, The main purpose of the report is , based on item, oraganizarion and subiventory it need to display the onhand qty for a gievn date. i used below code in formula column of report to calculate onhand qty on a given perticular date.

FUNCTION CF_LAST_BALFormula RETURN NUMBER IS
   v_onhand_qty   NUMBER;
   v_last_qty     NUMBER;
   v_target_qty   NUMBER;
BEGIN
   SELECT SUM(transaction_quantity)
   INTO   v_onhand_qty
   FROM   mtl_onhand_quantities_detail
   WHERE  inventory_item_id = :inventory_item_id
   AND    organization_id   = :organization_id
   AND    subinventory_code = :Subinventory;
  
   SELECT NVL(SUM(Transaction_quantity),0)
   INTO   v_last_qty
   FROM   mtl_material_transactions
   WHERE  inventory_item_id = :inventory_item_id
   AND    organization_id   = :organization_id
   AND    subinventory_code = :Subinventory
   AND trunc(NVL(transaction_date,creation_date))>= TRUNC(TO_DATE(:p_last_date,'RRRR/MM/DD'));

      v_target_qty:=(v_onhand_qty)-(v_last_qty);

   RETURN(v_target_qty);
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

Creation Of Master Organization

Today we will see creation of master organization.

Inventory Responsibility --> setup --> Organizations -->Organizations

1. 

Click on New

Enter Organization name and location and save your work.


Enter Name as "Inventory Organization" under organization classification and check the "Enabled" check box.

1. 

 Click On others button and select "Accounting Information"and OK.

1. 

Enter ledger ,legal entity and operating nit information and click on OK -- OK --Save.

1. 

Click On others button and select "Inventory Information"and OK.

1. 

Enter Proper information in "Organization Parameters" window . Its a 6 tabbed region window.

In Inventory Parameter tab -- > Master organization Name = Your newly created organization name.

1. 

Costing Information Tab --> costing method = Standard

1. 

Revision,Lot,Serial tab --> Starting serial number = 0009

1. 

Other Accounts Tab --> Enter accounts information. you will get these accounts information from your finance team.

1. 

Click on save . You will get one pop up message. read thoroughly and click on OK.


In later session we will discuss all the options available in inventory parameters window in detail.


Provide me comments if you like my post.