Oracle EBS - SQL Query to get consigned Item not having blanket PO

 SELECT

    mp.organization_code,

    msib.organization_id,

    upper(mc.segment1)       type,

    msib.inventory_item_id,

    msib.segment1            item_code,

    msib.primary_uom_code,

    mic.segment1             ppl,

    mic.segment2             spl

FROM

    apps.mtl_system_items_b              msib,

    apps.mtl_parameters                  mp,

    apps.mtl_onhand_quantities_detail    moqd,

    apps.mtl_item_locations_kfv          milk,

    apps.mtl_item_categories_v           mic,

    apps.mtl_categories                  mc,

    apps.mtl_serial_numbers              msn

WHERE

        1 = 1

--            AND MSIB.SEGMENT1 = 'NT7E70AASIF' -- '6029PKGN406'

    AND msib.organization_id = mp.organization_id

    AND msib.inventory_item_id = moqd.inventory_item_id

    AND msib.organization_id = moqd.organization_id

    AND msib.organization_id = milk.organization_id

    AND moqd.locator_id = milk.inventory_location_id

    AND msib.inventory_item_id = mic.inventory_item_id

    AND msib.organization_id = mic.organization_id

    AND msib.organization_id = msn.current_organization_id (+)

    AND moqd.subinventory_code = msn.current_subinventory_code (+)

    AND nvl(msn.current_status, 3) = 3

    AND msib.inventory_item_id = msn.inventory_item_id (+)

    AND moqd.lot_number = msn.lot_number (+)

    AND mic.category_id = mc.category_id

    AND mic.category_set_id = 1

    AND lot_control_code != 2

    AND mc.structure_id = 101

    AND msib.enabled_flag = 'Y'

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

    AND moqd.is_consigned = 1  -- consiged inv

    AND mc.disable_date IS NULL

    AND mp.organization_code IN ('MAS') --Add ORGANIZATION CODE

    AND msib.item_type='P'

    AND NOT EXISTS (

        SELECT

            1

        FROM

            apps.po_headers_all        pha,

            apps.po_lines_all          pla,

            apps.hr_operating_units    hou,

            apps.mtl_system_items_b    msi

        WHERE

                1 = 1

            AND pha.po_header_id = pla.po_header_id

            AND pha.org_id = hou.organization_id

            AND pla.item_id = msi.inventory_item_id

            AND msi.organization_id IN ( 85, 97 ) --Organization ID

            AND pha.type_lookup_code = 'BLANKET'

            AND nvl(pha.closed_code, 'OPEN') = 'OPEN'

            AND pha.authorization_status = 'APPROVED'

            AND ( pha.cancel_flag <> 'Y'

                  OR pha.cancel_flag IS NULL )

            AND ( pla.cancel_flag <> 'Y'

                  OR pla.cancel_flag IS NULL )

            AND trunc(sysdate) <= trunc(nvl(pha.end_date, sysdate))

            AND trunc(sysdate) <= trunc(nvl(pla.expiration_date, sysdate))

            AND msi.inventory_item_id = msib.inventory_item_id --item id link

            AND msi.organization_id = msib.organization_id --org id link

    )

GROUP BY

    mp.organization_code,

    msib.organization_id,

    upper(mc.segment1),

    msib.inventory_item_id,

    msib.segment1,

    msib.primary_uom_code,

    mic.segment1,

    mic.segment2

ORDER BY

    msib.segment1