INVENTORY STOCK LEDGER QUERY

Inventory Stock Ledger query
------------------------------------

SELECT DISTINCT msib.segment1 item_code, msib.segment2 unit_code,

SUBSTR (msib.description, 1, 20) item_description,msib.attribute20 pack_code, msib.attribute25 pack_description,xx_item_open_bal (mmt.inventory_item_id,mmt.organization_id,TO_DATE (:p_from)) open_bal,(SELECT SUM ( transaction_quantity*
(SELECT conversion_rate
FROM mtl_uom_conversions_view
WHERE inventory_item_id = a.inventory_item_id
AND organization_id = a.organization_id
AND uom_code = a.transaction_uom)) ddFROM mtl_material_transactions a,mtl_transaction_types mtt
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = mmt.organization_id
AND a.transaction_type_id = mtt.transaction_type_id
AND a.transaction_quantity > 0AND TO_DATE (a.transaction_date) BETWEEN TO_DATE (:p_from)AND TO_DATE(:p_todate))receipt,(SELECT SUM ( transaction_quantity*
(SELECT conversion_rate
FROM mtl_uom_conversions_view
WHERE inventory_item_id = a.inventory_item_id
AND organization_id = a.organization_id
AND uom_code = a.transaction_uom)) ddFROM mtl_material_transactions a,mtl_transaction_types mtt
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = mmt.organization_id
AND a.transaction_type_id = mtt.transaction_type_id
AND a.transaction_quantity < 1AND TO_DATE (a.transaction_date) BETWEEN TO_DATE (:p_from)AND TO_DATE(:p_todate))issue,xx_item_close_bal (mmt.inventory_item_id,mmt.organization_id,TO_DATE (:p_from),TO_DATE (:p_todate)) clos_bal,(SELECT SUM (reservation_quantity)FROM mtl_reservations
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = mmt.organization_id) unconfmrldn,NVL(xx_item_close_bal (mmt.inventory_item_id,mmt.organization_id,TO_DATE (:p_from),TO_DATE (:p_todate)),0)- NVL ((SELECT SUM (reservation_quantity)FROM mtl_reservations
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = mmt.organization_id),0) phy_clg_stock,(SELECT SUM ( NVL (plla.quantity, 0)- NVL (plla.quantity_received, 0))FROM po_headers_all pha,po_lines_all pla,po_line_locations_all plla
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.po_header_id = plla.po_header_id
AND pha.authorization_status = 'APPROVED'AND pla.item_id = mmt.inventory_item_id
AND plla.ship_to_organization_id = mmt.organization_id
--and (plla.QUANTITY_RECEIVED is not null or plla.QUANTITY_RECEIVED not in (0))AND TO_DATE (pha.creation_date) BETWEEN TO_DATE (:p_from)AND TO_DATE (:p_todate))outstndsuplord,msib.organization_id, msib.inventory_item_id,mp.organization_code
FROM mtl_system_items_b msib,mtl_parameters mp,mtl_material_transactions mmt
WHERE msib.organization_id = mp.organization_id
AND msib.inventory_item_id = mmt.inventory_item_id
AND msib.organization_id = mmt.organization_id
AND mp.organization_id = mmt.organization_id
AND mp.organization_id =nvl(:P_INV_ORG_ID,mp.organization_id)AND mp.organization_code = 'MAF'order by 1
Function Script
---------------

CREATE OR REPLACE FUNCTION APPS.xx_item_open_bal (p_item_id IN NUMBER,p_org_id IN NUMBER,p_from_date IN DATE)RETURN NUMBERISl_open_bal NUMBER;l_recipt NUMBER;l_issues NUMBER;BEGIN
BEGINSELECT SUM ( transaction_quantity*
(SELECT conversion_rate
FROM mtl_uom_conversions_view
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = mmt.organization_id
AND uom_code = mmt.transaction_uom))INTO l_open_bal
FROM mtl_material_transactions mmt, mtl_transaction_types mtt
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND TO_DATE (mmt.transaction_date) < TO_DATE (p_from_date);EXCEPTIONWHEN OTHERSTHENl_open_bal := 0;END;/*BEGINSELECT SUM (NVL (transaction_quantity, 0) )
INTO l_recipt
FROM mtl_material_transactions
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND TO_DATE (transaction_date) < TO_DATE (p_from_date)
AND transaction_action_id = 27;
EXCEPTION
WHEN OTHERS
THEN
l_recipt := 0;
END;*/
/* BEGINSELECT SUM (NVL (transaction_quantity, 0) )
INTO l_issues
FROM mtl_material_transactions
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND TO_DATE (transaction_date) < TO_DATE (p_from_date)
AND transaction_action_id = 1;
EXCEPTION
WHEN OTHERS
THEN
l_issues := 0;
END;*/
--l_open_bal := NVL (l_recipt, 0) + NVL (l_issues, 0);RETURN nvl(l_open_bal,0);END
xx_item_open_bal;/

Close Bal fun
-------------

CREATE OR REPLACE FUNCTION APPS.xx_item_close_bal (p_item_id IN NUMBER,p_org_id IN NUMBER,p_from_date IN DATE,p_todate IN DATE)RETURN NUMBERISl_open_bal NUMBER;l_clos_bal NUMBER;l_recipt NUMBER;l_issues NUMBER;BEGIN
BEGINSELECT SUM ( transaction_quantity*
(SELECT conversion_rate
FROM mtl_uom_conversions_view
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = mmt.organization_id
AND uom_code = mmt.transaction_uom))INTO l_recipt
FROM mtl_material_transactions mmt, mtl_transaction_types mtt
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.transaction_quantity > 0AND TO_DATE (mmt.transaction_date) BETWEEN TO_DATE (p_from_date)AND TO_DATE (p_todate);/*SELECT SUM (NVL (transaction_quantity, 0) )INTO l_recipt
FROM mtl_material_transactions
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND TO_DATE (transaction_date) BETWEEN TO_DATE (p_from_date)
AND TO_DATE (p_todate)
AND transaction_action_id = 27;*/
EXCEPTIONWHEN OTHERSTHENl_recipt := 0;END;BEGINSELECT SUM ( transaction_quantity*
(SELECT conversion_rate
FROM mtl_uom_conversions_view
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = mmt.organization_id
AND uom_code = mmt.transaction_uom))INTO l_issues
FROM mtl_material_transactions mmt, mtl_transaction_types mtt
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.transaction_quantity < 1AND TO_DATE (mmt.transaction_date) BETWEEN TO_DATE (p_from_date)AND TO_DATE (p_todate);/*SELECT SUM (NVL (transaction_quantity, 0))INTO l_issues
FROM mtl_material_transactions
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND TO_DATE (transaction_date) BETWEEN TO_DATE (p_from_date)
AND TO_DATE (p_todate)
AND transaction_action_id = 1;*/
EXCEPTIONWHEN OTHERSTHENl_issues := 0;END;BEGINSELECT xx_item_open_bal (p_item_id, p_org_id, TO_DATE (p_from_date))INTO l_open_bal
FROM DUAL;EXCEPTIONWHEN OTHERSTHENl_open_bal := 0;END;l_clos_bal := (NVL (l_open_bal, 0) + NVL (l_recipt, 0)) + NVL (l_issues, 0);RETURN l_clos_bal;END
xx_item_close_bal;/