Introduction/ Issue: This code will help us to Create the safety stock in oracle inventory.
Why we need to do / Cause of the issue: When business requires to create safety stock in bulk, then we can make use of this code to create the safety stock in oracle.
How do we solve: The below piece of code can be executed to create the safety stock.
Load the data in staging table before executing the script. Here the sample staging table is xx_stg table.
Declare
l_org NUMBER;
l_itemid NUMBER;
l_userid NUMBER := 0;
l_error_message VARCHAR2(2000);
l_flag VARCHAR2(1) := ‘Y’;
l_message VARCHAR2(2000) := NULL;
l_count NUMBER := 0 ;
l_diff_qty_count NUMBER :=0;
l_dup_count NUMBER := 0;
l_dual_cnt NUMBER := 0;
l_exist_qty NUMBER :=0;
CURSOR c_val
IS
SELECT *
FROM xx_stg
WHERE 1=1
AND status_flag IS NULL;
CURSOR c1
IS
SELECT item, org_code, start_date, stock_quantity
FROM xx_stg
WHERE 1=1
AND status_flag = ‘Y’;
BEGIN
mo_global.set_policy_context(‘S’,3);
fnd_global.APPS_INITIALIZE(user_id=>l_userid,
resp_id=> —Pass responsibility ID,
resp_appl_id=> –Pass responsibility application id
);
—————–
— VALIDATIONS
—————–
FOR rec IN c_val LOOP
l_flag := ‘Y’;
l_message := NULL;
l_count :=0;
l_diff_qty_count := 0;
l_dup_count := 0;
l_dual_cnt :=0;
l_exist_qty :=0;
— Validate Organization
BEGIN
SELECT organization_id
INTO l_org
FROM org_organization_definitions
WHERE organization_code = rec.org_code;
EXCEPTION
WHEN OTHERS THEN
l_flag := ‘E’;
l_message := ‘Invalid Organization’;
END;
— Validate Item
BEGIN
SELECT inventory_item_id
INTO l_itemid
FROM mtl_system_items_b
WHERE segment1 = rec.item
AND organization_id = l_org;
EXCEPTION
WHEN OTHERS THEN
l_flag := ‘E’;
l_message := l_message || ‘-Invalid Item’;
END;
— Validate Quantity
IF rec.stock_quantity IS NULL OR rec.stock_quantity < 0 THEN
l_flag := ‘E’;
l_message := l_message || ‘-Invalid Stock Quantity’;
END IF;
— Existing record in BASE TABLE
SELECT COUNT(*)
INTO l_count
FROM mtl_safety_stocks
WHERE organization_id = l_org
AND inventory_item_id = l_itemid
AND effectivity_date = rec.start_date;
IF l_count > 0 THEN
l_flag := ‘E’;
l_message := l_message || ‘-Safety stock already exists for same effective date’;
END IF;
— Duplicate in STAGING with different quantity
BEGIN
SELECT stock_quantity
INTO l_exist_qty
FROM xx_stg
WHERE item = rec.item
AND org_code = rec.org_code
AND start_date = rec.start_date
AND stock_quantity = rec.stock_quantity
AND status_flag IS NULL
;
IF l_exist_qty <> rec.stock_quantity THEN
l_flag := ‘E’;
l_message := l_message || ‘-Duplicate record with different stock quantity’;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
— Same key but different quantities
SELECT COUNT(DISTINCT stock_quantity)
INTO l_diff_qty_count
FROM xx_stg
WHERE item = rec.item
AND org_code = rec.org_code
AND start_date = rec.start_date
AND status_flag IS NULL;
IF l_diff_qty_count > 1 THEN
l_flag := ‘E’;
l_message := l_message || ‘-Same item/org/date with different stock quantities’;
END IF;
— Exact duplicate records
SELECT COUNT(*)
INTO l_dup_count
FROM xx_stg
WHERE item = rec.item
AND org_code = rec.org_code
AND start_date = rec.start_date
AND stock_quantity = rec.stock_quantity
AND status_flag IS NULL;
IF l_dup_count > 1 THEN
l_flag := ‘E’;
l_message := l_message || ‘-Duplicate records found’;
END IF;
— Inconsistent records
SELECT COUNT(*)
INTO l_dual_cnt
FROM xx_stg s1
WHERE s1.item = rec.item
AND s1.org_code = rec.org_code
AND s1.status_flag IS NULL
AND (
(s1.start_date = rec.start_date
AND s1.stock_quantity <> rec.stock_quantity)
OR
(s1.stock_quantity = rec.stock_quantity
AND s1.start_date <> rec.start_date)
OR — Different date AND different qty (new condition)
(s1.start_date <> rec.start_date
AND s1.stock_quantity <> rec.stock_quantity)
);
IF l_dual_cnt > 0 THEN
l_flag := ‘E’;
l_message := l_message || ‘-Inconsistent records: same item/org with different date or quantity’;
END IF;
— Date validation
IF trunc(rec.start_date) >= trunc(sysdate) THEN
l_flag := ‘E’;
l_message := l_message || ‘-Effective date should be greater than or equal to today’;
END IF;
— Final Update
UPDATE xx_stg
SET status_flag = l_flag,
message = l_message
WHERE item = rec.item
AND org_code = rec.org_code;
END LOOP;
COMMIT;
—————–
— INSERT LOGIC
—————–
FOR i IN c1 LOOP
BEGIN
SELECT organization_id
INTO l_org
FROM org_organization_definitions
WHERE organization_code = i.org_code;
SELECT inventory_item_id
INTO l_itemid
FROM mtl_system_items_b
WHERE segment1 = i.item
AND organization_id = l_org;
mtl_safety_stocks_pkg.insert_safety_stocks
(l_org,
l_itemid,
1,
NULL,
NULL,
NULL,
i.start_date,
ROUND(i.stock_quantity,0),
NULL,
l_userid);
DBMS_OUTPUT.put_line (‘Updated: ‘ || i.item);
UPDATE xx_stg
SET status_flag = ‘S’
WHERE item = i.item
AND org_code = i.org_code;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SUBSTR(SQLERRM, 1, 2000);
UPDATE xx_stg
SET status_flag = ‘X’,
message = l_error_message
WHERE item = i.item
AND org_code = i.org_code;
DBMS_OUTPUT.put_line (‘Error for item ‘
|| i.item
|| ‘ – ‘
|| SQLCODE
|| ‘ – ‘
|| SQLERRM);
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
Conclusion: This code will help in creating the safety stock in oracle inventory.
