CREATE OR REPLACE PACKAGE xx_crossref_pkg IS
g_package_name VARCHAR2(200) := 'XX_CROSSREF_PKG';
-- Validate item xref
PROCEDURE validate_item_xref;
-- Create item cross references
PROCEDURE create_item_xref;
-- Get item quantity
FUNCTION get_item_quantity(p_inventory_item_id NUMBER,
p_organization_id NUMBER,
p_quantity_type VARCHAR2)
RETURN NUMBER;
END;
/
================================================================================
CREATE OR REPLACE PACKAGE BODY xx_crossref_pkg IS
---------------------------------------------------
-- Get item id
---------------------------------------------------
FUNCTION get_inventory_item_id(p_item_number VARCHAR2) RETURN NUMBER IS
l_inventory_item_id NUMBER;
CURSOR item_cur IS
SELECT inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = p_item_number
AND organization_id = 41;
BEGIN
OPEN item_cur;
FETCH item_cur INTO l_inventory_item_id;
CLOSE item_cur;
RETURN l_inventory_item_id;
END get_inventory_item_id;
---------------------------------------------------
-- Validate data
---------------------------------------------------
PROCEDURE validate_item_xref IS
l_inventory_item_id NUMBER;
CURSOR item_cur IS
SELECT *
FROM xx_stg_item_xref
WHERE inventory_item_id IS NULL
FOR UPDATE;
BEGIN
FOR item_rec IN item_cur LOOP
-- Set item id for valid items
l_inventory_item_id := get_inventory_item_id(item_rec.item_number);
IF (l_inventory_item_id IS NOT NULL) THEN
UPDATE xx_stg_item_xref
SET inventory_item_id = l_inventory_item_id
WHERE CURRENT OF item_cur;
END IF;
END LOOP;
END validate_item_xref;
---------------------------------------------------
-- Create cross references
---------------------------------------------------
PROCEDURE create_item_xref IS
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
l_xref_tbl MTL_CROSS_REFERENCES_PUB.XREF_TBL_TYPE;
x_message_list ERROR_HANDLER.ERROR_TBL_TYPE;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_user_name VARCHAR2(30) := 'SYSADMIN';
l_resp_name VARCHAR2(30) := 'INVENTORY';
l_index INTEGER := 0;
CURSOR item_cur IS
SELECT *
FROM xx_stg_item_xref
WHERE inventory_item_id IS NOT NULL
AND product IS NOT NULL
AND category IS NOT NULL
AND color IS NOT NULL
AND NVL(status_code, 'N') = 'N'
FOR UPDATE;
BEGIN
-- Validate
validate_item_xref;
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
-- Initialize context
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);
-- Load cross references from staging table
l_xref_tbl.delete;
FOR item_rec IN item_cur LOOP
-- Populate record
l_index := l_index + 1;
l_xref_tbl(l_index).transaction_type := 'CREATE';
l_xref_tbl(l_index).cross_reference_type := 'CROSSREF';
l_xref_tbl(l_index).organization_id := 41;
l_xref_tbl(l_index).inventory_item_id := item_rec.inventory_item_id;
l_xref_tbl(l_index).cross_reference := item_rec.item_number;
l_xref_tbl(l_index).description := item_rec.description;
l_xref_tbl(l_index).attribute1 := item_rec.product;
l_xref_tbl(l_index).attribute2 := item_rec.category;
l_xref_tbl(l_index).attribute3 := item_rec.subcategory;
l_xref_tbl(l_index).attribute4 := item_rec.color;
-- Update status code
UPDATE xx_stg_item_xref
SET status_code = 'P'
WHERE CURRENT OF item_cur;
END LOOP;
-- Create cross reference
mtl_cross_references_pub.process_xref(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_xref_tbl => l_xref_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_message_list => x_message_list);
-- Check status
IF x_return_status != FND_API.G_RET_STS_SUCCESS THEN
DBMS_OUTPUT.PUT_LINE('Error Message Count :' || x_message_list.count);
FOR i IN 1..x_message_list.count LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(i)||'. Err Rec No : ' || x_message_list(i).entity_index || ' Table Name: ' || x_message_list(i).table_name);
DBMS_OUTPUT.PUT_LINE('Err Message: ' || x_message_list(i).message_text);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: ' || SQLERRM);
END create_item_xref;
---------------------------------------------------
-- Get item quantities
---------------------------------------------------
FUNCTION get_item_quantity(p_inventory_item_id NUMBER,
p_organization_id NUMBER,
p_quantity_type VARCHAR2)
RETURN NUMBER IS
x_return_status VARCHAR2 (50);
x_msg_count VARCHAR2 (50);
x_msg_data VARCHAR2 (50);
l_item_id NUMBER;
l_organization_id NUMBER;
l_qty_on_hand NUMBER;
l_res_qty_on_hand NUMBER;
l_avail_to_tnsct NUMBER;
l_avail_to_reserve NUMBER;
l_qty_reserved NUMBER;
l_qty_suggested NUMBER;
l_lot_control_code BOOLEAN;
l_serial_control_code BOOLEAN;
l_requested_quantity NUMBER;
BEGIN
inv_quantity_tree_grp.clear_quantity_cache;
l_lot_control_code := FALSE;
l_serial_control_code := FALSE;
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0
,p_init_msg_lst => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_organization_id
,p_inventory_item_id => p_inventory_item_id
,p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode
,p_is_revision_control => FALSE
,p_is_lot_control => l_lot_control_code-- is_lot_control,
,p_is_serial_control => l_serial_control_code
,p_revision => NULL -- p_revision,
,p_lot_number => NULL -- p_lot_number,
,p_lot_expiration_date => SYSDATE
,p_subinventory_code => NULL -- p_subinventory_code,
,p_locator_id => NULL -- p_locator_id,
--,p_cost_group_id => NULL
--,p_onhand_source => NULL
,x_qoh => l_qty_on_hand -- Quantity on-hand
,x_rqoh => l_res_qty_on_hand --reservable quantity on-hand
,x_qr => l_qty_reserved
,x_qs => l_qty_suggested
,x_att => l_avail_to_tnsct -- available to transact
,x_atr => l_avail_to_reserve-- available to reserve
);
IF (p_quantity_type = 'QOH') THEN
l_requested_quantity := l_qty_on_hand;
ELSIF (p_quantity_type = 'RES') THEN
l_requested_quantity := l_qty_reserved;
ELSIF (p_quantity_type = 'SUG') THEN
l_requested_quantity := l_qty_suggested;
ELSIF (p_quantity_type = 'ATT') THEN
l_requested_quantity := l_avail_to_tnsct;
ELSIF (p_quantity_type = 'ATR') THEN
l_requested_quantity := l_avail_to_reserve;
END IF;
RETURN l_requested_quantity;
END get_item_quantity;
END;
/
No comments:
Post a Comment