Monday, 23 May 2022

Oracle EBS - SQL Query to get Item Reserved Qty Details

 SELECT

    segment1 item,

    organization_code,

    reserved_qty,

    order_number,

    line_number,

    locator_id,

    location

FROM

    (

        SELECT

            msib.segment1,

            mp.organization_code,

            msib.organization_id,

            msib.purchasing_item_flag,

            msib.shippable_item_flag,

            msib.inventory_item_flag,

            lot_control_code,

            decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

                   5,

                   'At Receipt',

                   6,

                   'At Issue')         "Serial Control",

            mc.segment1                "PPL",

            mc.segment2                "SPL",

            mc.segment3                "ITEM CATEGORY",

            mr.reservation_quantity    reserved_qty,

            mr.demand_source_header_id,

            h.order_number,

            l.line_number,

            mr.locator_id,

            milk.CONCATENATED_SEGMENTS location

--            (

--                SELECT

--                    nvl(SUM(reservation_quantity), 0)

--                FROM

--                    mtl_reservations

--                WHERE

--                        inventory_item_id = msib.inventory_item_id

--                    AND organization_id = msib.organization_id

--            )                    reserved_qty

        FROM

            mtl_system_items_b    msib,

            mtl_item_categories   mic,

            mtl_categories        mc,

            mtl_parameters        mp,

            mtl_reservations      mr,

            oe_order_headers_all  h,

            oe_order_lines_all    l,

            apps.mtl_item_locations_kfv milk

        WHERE

                msib.inventory_item_id = mic.inventory_item_id

            AND msib.organization_id = mic.organization_id

            AND msib.organization_id = mp.organization_id

            AND mic.category_id = mc.category_id

            AND mic.category_set_id = 1

            AND mc.structure_id = 101

            AND msib.enabled_flag = 'Y'

            AND msib.lot_control_code = 1

--            AND msib.segment1='NTTC90ABE6'

            AND nvl(msib.end_date_active, sysdate) >= sysdate

            AND mc.disable_date IS NULL

            AND mp.organization_code IN ('MAS')  --Org CODE

            AND mr.inventory_item_id = msib.inventory_item_id

            AND mr.organization_id = msib.organization_id

            AND h.header_id = l.header_id

            AND nvl(l.cancelled_flag, 'N') <> 'Y'

            AND l.line_id = mr.demand_source_line_id

            AND mr.locator_id=milk.INVENTORY_LOCATION_ID(+)

            AND mr.organization_id = milk.organization_id(+)

            AND msib.item_type='P'

        ORDER BY

            mc.segment1

    )

WHERE

    reserved_qty <> 0

No comments:

Post a Comment