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

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

Oracle EBS - Enable Item as Lot Control PLSQL Script

 DECLARE

    l_item_tbl_typ   ego_item_pub.item_tbl_type;

    x_item_tbl_typ   ego_item_pub.item_tbl_type;

    x_return_status  VARCHAR2(100);

    x_msg_count      NUMBER;

    x_message_list   error_handler.error_tbl_type;

   -- User Variables: Update for your environment ~~!

    l_resp_appl_id   NUMBER := 401; --FND_PROFILE.VALUE ('RESP_APPL_ID');

    l_resp_id        NUMBER := 20634; --FND_PROFILE.VALUE ('RESP_ID');

    l_user_id        NUMBER := -1; --26406 --FND_PROFILE.VALUE ('USER_ID');

BEGIN

    fnd_global.apps_initialize(user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_resp_appl_id);

--Calling Enable Item as Lot Control procedure--

    FOR i IN (

        SELECT

            msib.inventory_item_id,

            msib.organization_id,

            msib.segment1,

            msib.lot_control_code,

            msib.lot_split_enabled,

            msib.lot_merge_enabled,

            msib.lot_divisible_flag

        FROM

            mtl_system_items_b msib

        WHERE

                msib.organization_id = 85

            AND msib.segment1 = '320672'

            AND msib.last_update_date >= sysdate - 10

        ORDER BY

            msib.last_update_date DESC

    ) LOOP

        BEGIN

            l_item_tbl_typ(1).transaction_type := ego_item_pub.g_ttype_update;

            l_item_tbl_typ(1).inventory_item_id := i.inventory_item_id;

            l_item_tbl_typ(1).organization_id := i.organization_id;

            l_item_tbl_typ(1).lot_control_code := 2;

            l_item_tbl_typ(1).lot_split_enabled := 'Y';

            l_item_tbl_typ(1).lot_merge_enabled := 'Y';

--            l_item_tbl_typ(1).auto_lot_alpha_prefix := 'L';

--            l_item_tbl_typ(1).start_auto_lot_number := 1;

            ego_item_pub.process_items(p_api_version => 1.0, p_init_msg_list => fnd_api.g_false,

                                      p_commit => fnd_api.g_true,

                                      p_item_tbl => l_item_tbl_typ,

                                      x_item_tbl => x_item_tbl_typ,

                                      p_role_grant_tbl => ego_item_pub.g_miss_role_grant_tbl,

                                      x_return_status => x_return_status,

                                      x_msg_count => x_msg_count);

            dbms_output.put_line('x_return_status : ' || x_return_status);

            error_handler.get_message_list(x_message_list);

            FOR i IN 1..x_message_list.count LOOP

                dbms_output.put_line(x_message_list(i).message_text);

            END LOOP;

            COMMIT;

        END xxtlm_enable_item_lot;

    END LOOP;

END;