Introduction:
This Post illustrates the steps required to fetch the Inventory Cycle Count Report.
Script to fetch the Inventory Cycle Count Details
SELECT TO_CHAR (cce.creation_date, ‘DD-MON-YYYY’) creation_date,
mp.organization_code org, cch.cycle_count_header_name cycle_count,
cce.count_list_sequence count_seq, msi.segment1 item,
msi.description item_description, cce.abc_class_name CLASS,
cce.subinventory subinventory,
mil.segment1 || ‘.’ || mil.segment2 || ‘.’ || mil.segment3 LOCATOR,
NVL (cce.count_uom_current, cce.count_uom_prior) uom,
NVL (cce.count_quantity_current, cce.count_quantity_prior) count_qty,
NVL (cce.system_quantity_current,
cce.system_quantity_prior
) system_qty,
cce.adjustment_quantity adjust_qty,
ROUND (cce.adjustment_amount, 2) adjust_value,
cce.neg_adjustment_quantity neg_adjust_qty,
ROUND (cce.neg_adjustment_amount, 2) neg_adjust_value,
NVL (cce.number_of_counts, 0) no_of_cnts,
TO_CHAR (cce.creation_date, ‘DD-MON-YYYY’) count_creation_date,
TO_CHAR (cce.count_date_first, ‘DD-MON-YYYY’) first_count_date,
cce.count_date_prior previous_count_date,
TO_CHAR (NVL (cce.count_date_current, cce.count_date_prior),
‘DD-MON-YYYY’
) last_count_date,
cce.count_status status,
TO_CHAR (cce.approval_date, ‘DD-MON-YYYY’) approval_date,
DECODE (cce.count_status,
‘Rejected’, cce.last_update_date,
NULL
) reject_date,
cce.item_unit_cost, cce.reference_first, cce.reference_current,
cce.reason_name, cce.reason_description, cce.cost_group_id,
cce.cost_group_name, cce.abc_class_name,
cce.inventory_adjustment_account, cce.locator_id,
cce.organization_id, papf1.full_name counted_by_employee_id_first,
papf2.full_name counted_by_employee_id_current,
msi.inventory_item_id
FROM mtl_cycle_count_entries_v cce,
mtl_system_items_b msi,
mtl_cycle_count_headers cch,
mtl_parameters mp,
mtl_item_locations mil,
per_all_people_f papf1,
per_all_people_f papf2,
org_organization_definitions org
WHERE 1 = 1
AND cce.inventory_item_id = msi.inventory_item_id
AND cce.organization_id = msi.organization_id
AND cce.cycle_count_header_id = cch.cycle_count_header_id
AND cch.organization_id = mp.organization_id
AND cce.locator_id = mil.inventory_location_id(+)
AND msi.organization_id = org.organization_id
AND org.operating_unit = NVL (:p_organization_id, org.operating_unit)
AND msi.segment1 = NVL (:p_item, msi.segment1)
AND TRUNC (cce.creation_date) BETWEEN TRUNC (:p_from_date)
AND TRUNC (:p_to_date)
AND papf1.person_id = counted_by_employee_id_first
AND papf2.person_id = counted_by_employee_id_current
ORDER BY 1, 2, 3
Got any queries?
Do drop a note by writing us at venkatesh.b@staging.doyensys.com or use the comment section below to ask your questions
