Script to Extract the Onhand Qty Details for Non-Serial Items
Introduction:
Script to Extract the Onhand Qty Details for Non-Serial Items.
Cause of the issue:
In PO Migration from one unit to another unit and to validate the data, the script needs to create to check the onhand Qty for the Migrated Data.
How do we solve:
The below is the Script to Extract the Onhand Qty Details for Non-Serial Items.
Script:
SELECT ‘Miscellaneous receipt’ AS transaction_type, b.inventory_item_id,
mp.organization_name AS old_organization_name,
mp.organization_code AS old_organization_code, b.segment1 AS item_code,b.description,
a.subinventory_code AS sub_inventory,substr(b.segment1,1,2) item_product_type,
mil.concatenated_segments AS locators, b.primary_uom_code AS uom_code,
a.primary_transaction_quantity quantity,
/* CASE
WHEN mmt.actual_cost = 0
THEN b.list_price_per_unit
ELSE mmt.actual_cost
END AS unit_cost,mmt.new_cost,*/mmt.actual_cost,
(SELECT concatenated_segments
FROM gl_code_combinations_kfv gcc
WHERE gcc.code_combination_id =
mmt.distribution_account_id)
AS gl_account,
mp.organization_name LOCATION,
(SELECT mtr.reason_name
FROM mtl_transaction_reasons mtr
WHERE mtr.reason_id = mmt.reason_id) reason,
CAST (NULL AS CHAR (200)) AS trans_type_ref,
CAST (NULL AS CHAR (200)) AS serial_no,
(SELECT meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_type =
‘MTL_SERIAL_NUMBER’
AND flv.lookup_code = b.serial_number_control_code)
serial_number_control_value,
mmt.attribute1 AS parent_item, mmt.attribute3 AS oem_supplier,
mmt.attribute4 AS cost_centre,
mmt.attribute5 AS capex_deployment_category,
CAST (NULL AS CHAR (200)) asset_transfer_value,
CAST (NULL AS CHAR (200)) old_po_number,
CAST (NULL AS CHAR (200)) old_mrn_number,
CAST (NULL AS CHAR (200)) old_mrn_date,
CAST (NULL AS CHAR (200)) old_cost_center,
CAST (NULL AS CHAR (200)) customer_name,
(SELECT DISTINCT poh.attribute9
FROM po_headers_all poh,
rcv_transactions rcv,
rcv_shipment_lines rsl
WHERE rsl.po_header_id = poh.po_header_id
AND mmt.rcv_transaction_id = rcv.transaction_id
AND rsl.shipment_line_id = rcv.shipment_line_id) bu_code,
(SELECT DISTINCT poh.attribute10
FROM po_headers_all poh,
rcv_transactions rcv,
rcv_shipment_lines rsl
WHERE rsl.po_header_id = poh.po_header_id
AND mmt.rcv_transaction_id = rcv.transaction_id
AND rsl.shipment_line_id = rcv.shipment_line_id) cc_name,
TRUNC (mmt.transaction_date),b.inventory_asset_flag
FROM mtl_onhand_quantities_detail a,
mtl_system_items_b b,
org_organization_definitions mp,
mtl_item_locations_kfv mil,
mtl_material_transactions mmt,
mtl_secondary_inventories msi
WHERE 1 = 1
AND mp.organization_id = b.organization_id
AND a.organization_id = b.organization_id
AND a.inventory_item_id = b.inventory_item_id
AND mil.inventory_location_id = a.locator_id
AND mmt.transaction_id = a.update_transaction_id
AND mmt.organization_id = b.organization_id
AND mmt.inventory_item_id = b.inventory_item_id
AND b.serial_number_control_code != 5
AND b.inventory_item_status_code = ‘Active’
AND NVL (mp.operating_unit, 100) = 125
AND a.subinventory_code = msi.secondary_inventory_name
AND b.organization_id = msi.organization_id
AND TRUNC (mmt.transaction_date) > ’01-JAN-2021′
