Monday, 23 May 2022

Oracle EBS - SQL Query to Get Item having End Dated Buyer

 SELECT

    msib.segment1        "Item",

    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",

    (

        SELECT

            full_name

        FROM

            per_all_people_f

        WHERE

                person_id = fu.employee_id

            AND ROWNUM = 1

    )                    buyer

FROM

    mtl_system_items_b   msib,

    mtl_item_categories  mic,

    mtl_categories       mc,

    mtl_parameters       mp,

    fnd_user             fu

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 nvl(msib.end_date_active, sysdate) >= sysdate

    AND msib.buyer_id = fu.employee_id (+)

    AND trunc(fu.end_date) < trunc(sysdate) --end dated buyer

    AND mc.disable_date IS NULL

    AND mp.organization_code IN ('MAS')--Add Organization Code

    AND msib.item_type='P' --Component Item

ORDER BY

    mc.segment1

No comments:

Post a Comment