Introduction
This Query will help to Fetch the On hand Inventory by Revision Report Data in Oracle Apps.
SQL Query:
/* Formatted on 2021/09/06 15:26 (Formatter Plus v4.8.8) */
SELECT ood.organization_code, msib.inventory_item_id, msib.organization_id,
micv.segment2, micv.segment3, micv1.segment5 brand, msib.segment1 item,
msib.description description, msib.inventory_item_status_code status,
DECODE (msib.planning_make_buy_code, ‘2’, ‘BUY’, ‘1’, ‘MAKE’) SOURCE,
msib.minimum_order_quantity moq, conversion_rate CASE,
ROUND (cic.item_cost, 2) std_cost, dirv.revision,
(SELECT mir1.revision
FROM mtl_item_revisions mir1
WHERE mir1.revision_id IN (
SELECT MAX (mir.revision_id)
FROM mtl_item_revisions mir
WHERE mir.organization_id = msib.organization_id
AND mir.inventory_item_id = msib.inventory_item_id))
it_rev,
(SELECT SUM (NVL (pla.quantity, 0) – NVL (plla.quantity_received, 0)
)
FROM po_headers_all pha,
po_vendors pv,
po_lines_all pla,
po_line_locations_all plla,
mtl_system_items_b msi,
po_vendor_sites_all pvs
WHERE pha.vendor_id = pv.vendor_id
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pla.item_id = msi.inventory_item_id
AND msi.organization_id = plla.ship_to_organization_id
AND pvs.vendor_site_id = pha.vendor_site_id
AND pha.org_id = 3
AND msi.inventory_item_id = msib.inventory_item_id
AND pla.quantity <> plla.quantity_received
AND (pha.closed_code = ‘OPEN’ OR NVL (pha.closed_code, ‘X’) = ‘X’)
AND ( pla.closed_code NOT LIKE ‘%CLOSED’
OR NVL (pla.closed_code, ‘X’) = ‘X’
)
AND ( plla.closed_code NOT LIKE ‘%CLOSED’
OR NVL (plla.closed_code, ‘X’) = ‘X’
)) vendor_po,
xx_inventory_rpt_pkg.xdmc_onhand_qty (msib.inventory_item_id,
msib.organization_id,
‘OHQ’,
NULL
) total_oh,
( xx_inventory_rpt_pkg.xdmc_onhand_qty (msib.inventory_item_id,
msib.organization_id,
‘OHQ’,
NULL
)
– NVL ((SELECT SUM (moqd.primary_transaction_quantity)
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.inventory_item_id = msib.inventory_item_id
AND moqd.organization_id = msib.organization_id
AND moqd.revision IS NOT NULL
GROUP BY moqd.inventory_item_id, moqd.organization_id),
0
)
) diff,
dirv.primary_transaction_quantity,
(( xx_inventory_rpt_pkg.xdmc_onhand_qty (msib.inventory_item_id,
msib.organization_id,
‘OHQ’,
NULL
)
– NVL ((SELECT SUM (moqd.primary_transaction_quantity)
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.inventory_item_id = msib.inventory_item_id
AND moqd.organization_id = msib.organization_id
–AND moqd.revision IS NOT NULL
GROUP BY moqd.inventory_item_id, moqd.organization_id),
0
)
)
) res
FROM mtl_system_items_b msib,
org_organization_definitions ood,
mtl_item_categories_v micv,
mtl_item_categories_v micv1,
cst_item_costs cic,
(SELECT inventory_item_id, organization_id,
SUM
(primary_transaction_quantity
) primary_transaction_quantity,
revision
FROM mtl_onhand_quantities_detail
GROUP BY inventory_item_id, organization_id, revision) dirv,
mtl_uom_conversions muc
WHERE msib.organization_id = ood.organization_id(+)
AND micv.category_set_name(+) = ‘Inventory’
AND msib.inventory_item_id = micv.inventory_item_id(+)
AND msib.organization_id = micv.organization_id(+)
AND micv1.category_set_name(+) = ‘APP Brand’
AND micv1.control_level_disp(+) = ‘Master’
AND msib.inventory_item_id = micv1.inventory_item_id(+)
AND msib.organization_id = micv1.organization_id(+)
AND cic.cost_type_id(+) = 1
AND msib.inventory_item_id = cic.inventory_item_id(+)
AND msib.organization_id = cic.organization_id(+)
AND msib.inventory_item_id = dirv.inventory_item_id
AND msib.organization_id = dirv.organization_id
AND muc.unit_of_measure(+) = ‘CASE’
AND msib.inventory_item_id = muc.inventory_item_id(+);
Got any queries?
Do drop a note by writing us at Venkatesh.b@doyensys.com or use the comment section below to ask your question
