Oracle EBS - SQL Query to Get Item Open Cycle Count Entries

 --1. Check for physical inventory adjustments where adjustment quantity is not 0

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"

FROM

    mtl_system_items_b   msib,

    mtl_item_categories  mic,

    mtl_categories       mc,

    mtl_parameters       mp,

    mtl_physical_adjustments    mpa

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 mc.disable_date IS NULL

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

    AND mpa.inventory_item_id = msib.inventory_item_id

    AND mpa.organization_id = msib.organization_id

    AND mpa.approval_status = 1

    AND mpa.adjustment_quantity <> 0

    AND msib.item_type='P'

ORDER BY

    mc.segment1;

--2. Check for cyclecount entries that are marked as uncounted, pre-approved (pending) or recount (See similar query exists in Note 1373918.1)

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"

FROM

    mtl_system_items_b   msib,

    mtl_item_categories  mic,

    mtl_categories       mc,

    mtl_parameters       mp,

    mtl_cycle_count_entries     mcce

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 --Not Lot Enabled Item

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

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

    AND mcce.inventory_item_id = msib.inventory_item_id

    AND mcce.organization_id = msib.organization_id

    AND mcce.entry_status_code IN ( 1, 2, 3 )

    AND msib.item_type='P'

ORDER BY

    mc.segment1;

--3. Alternatively, the following query provides the name of the cycle count

SELECT

    msib.segment1        "Item",

    mp.organization_code,

    msib.organization_id,

    ch.description       cycle_count_name,

    ch.cycle_count_header_id,

    ce.cycle_count_entry_id,

    ce.entry_status_code,

    lu1.meaning          entry_status_code_meaning,

    msib.purchasing_item_flag,

    msib.shippable_item_flag,

    msib.inventory_item_flag,

    msib.lot_control_code,

    decode(msib.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"

FROM

    apps.mtl_system_items_b             msib,

    apps.mtl_item_categories            mic,

    apps.mtl_categories                 mc,

    apps.mtl_parameters                 mp,

    apps.mtl_cycle_count_headers    ch,

    apps.mtl_cycle_count_entries        ce,

    apps.mtl_item_flexfields            mif,

    apps.mfg_lookups                    lu1

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 mc.disable_date IS NULL

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

    AND mif.inventory_item_id = msib.inventory_item_id

    AND mif.organization_id = msib.organization_id

    AND mp.organization_id = mif.organization_id

    AND mif.inventory_item_id = ce.inventory_item_id

    AND ce.cycle_count_header_id = ch.cycle_count_header_id

    AND 'MTL_CCEOI_STATUS_FLAG' = lu1.lookup_type (+)

    AND ce.entry_status_code = lu1.lookup_code (+)

    AND ce.entry_status_code IN ( 1, 2, 3 )

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

ORDER BY

    mc.segment1;