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.

Creation Of Locations

Setup --> Organizations --> Locations

Name                : SR_Loc1
Address Style    : India
Address            : Enter Proper Address   ----> Save

1. 

Multi Org Structure Table

Name :Business Group
Table :HRFV_BUSINESS_GROUPS
Profile:HR:Business Group ID
Column: BUSINESS_GROUP_ID

Name :Set Of Books
Table :GL_SET_OF_BOOKS
Profile:GL: Set of books name
Column: Set_of_book_id

Name :Legal Entity
Table :HR_LEGAL_ENTITIES


Name :Operating Unit
Table :HR_OPERATING_UNITS
Profile:MO:Operating Unit
Column:ORG_ID

Name:Inventroy Organization
Table:ORG_ORGANIZATION_DEFINITIONS
Column:ORGANIZATION_ID

Link Between Purchase Order and Requisition

SELECT prh.segment1 req_number
      ,prh.authorization_status
      ,prl.line_num req_line_num
      ,prl.item_description req_item_description
      ,prl.unit_price req_unit_price
      ,prl.quantity req_quantity
      ,pd.req_header_reference_num
      ,pd.req_line_reference_num
      ,pl.line_num
      ,pl.item_description
      ,pl.quantity
      ,pl.amount
      ,ph.segment1 po_number
      ,prd.distribution_id
      ,pd.req_distribution_id
  FROM po_requisition_headers_all prh
      ,po_requisition_lines_all   prl
      ,po_req_distributions_all   prd
      ,po_distributions_all       pd
      ,po_line_locations_all      pll
      ,po_lines_all           pl
      ,po_headers_all             ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)