In this post , I will share you the PLSQL code for BOM Routing Interface.
I am using XX_BOM_ROUTING_STG staging table where I have uploaded Routing data through SQL loader. I am sharing you two procedures.
In the First procedure , I will share you the validation script so that you can validate the Date for your Staging table like the values you are uploading is available in Oracle apps or not.
In the Second Procedure I will share your the Execution script to upload validated date in Routing Interface Tables.
First Procedure (Validation Script)
PROCEDURE xx_validate_routing_date
IS
error_message VARCHAR2 (32767);
final_error_msg VARCHAR2 (32767);
resource_null VARCHAR2 (32767);
operation_null VARCHAR2 (32767);
dept_null VARCHAR2 (32767);
dept_id NUMBER;
attribut_null VARCHAR2 (32767);
item_null VARCHAR2 (32767);
item_count NUMBER;
v_org_count NUMBER;
v_altenate_null VARCHAR2 (2000);
v_organization_id NUMBER;
l_sub_loc_code NUMBER;
l_expense_to_asset_transfer NUMBER;
v_org_null VARCHAR2 (2000);
assembly_item_id NUMBER;
CURSOR l_xxdata_loading_routings
IS
SELECT organization_code, item_number, completion_subinventory,
completion_locator_id, process_revision, alternate_name,
OPERATION_CODE, DEPARTMENT_CODE, RESOURCE_CODE, ENTRY_ID
FROM XX_BOM_ROUTING_STG
WHERE NVL(VAILIDATE_FLAG_PROCESS,'NULL') ='NULL' AND NVL(FLAG_PROCESS, 'E') <> 'P';
----------Cusor for Altername_name
CURSOR l_alternate_csr (p_orgid NUMBER, p_alt VARCHAR2)
IS
SELECT 1 dummy
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM bom_alternate_designators
WHERE organization_id = p_orgid
AND alternate_designator_code = p_alt);
----------Cursor check primary routings-------
CURSOR l_checkprimary_csr (
p_orgid NUMBER,
p_assyid NUMBER,
p_rtgtype NUMBER
)
IS
SELECT 1 dummy
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM bom_operational_routings
WHERE organization_id = p_orgid
AND assembly_item_id = p_assyid
AND alternate_routing_designator IS NULL
AND (routing_type = p_rtgtype OR routing_type = 1));
----------cursor for attribute ----------
CURSOR l_checkattributes_csr (
p_orgid NUMBER,
p_assyid NUMBER,
p_rtgtype NUMBER
)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM mtl_system_items
WHERE organization_id = p_orgid
AND inventory_item_id = p_assyid
AND bom_item_type <> 3
AND bom_enabled_flag = 'Y'
AND pick_components_flag = 'N'
AND eng_item_flag =
DECODE (p_rtgtype,
2, eng_item_flag,
'N'
));
--------Cursor to check sub inventory --------------
CURSOR l_subinvflags_csr (p_itemid NUMBER, p_orgid NUMBER)
IS
SELECT msi.inventory_asset_flag, msi.restrict_subinventories_code,
msi.restrict_locators_code, msi.location_control_code,
mp.stock_locator_control_code
FROM mtl_system_items msi, mtl_parameters mp
WHERE msi.inventory_item_id = p_itemid
AND msi.organization_id = p_orgid
AND mp.organization_id = msi.organization_id;
CURSOR l_nonrestrictedsubinv_csr (
p_subinv VARCHAR2,
p_orgid NUMBER,
p_asset NUMBER,
p_inv_asst VARCHAR2
)
IS
SELECT locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinv
AND organization_id = p_orgid
AND NVL (disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE)
AND ( (p_asset = 1 AND quantity_tracked = 1)
OR ( NVL (p_asset, 0) <> 1
AND ( ( p_inv_asst = 'Y'
AND asset_inventory = 1
AND quantity_tracked = 1
)
OR (p_inv_asst = 'N')
)
)
);
CURSOR l_restrictedsubinv_csr (
p_subinv VARCHAR2,
p_orgid NUMBER,
p_itemid NUMBER,
p_asset NUMBER,
p_inv_asst VARCHAR2
)
IS
SELECT locator_type
FROM mtl_secondary_inventories sub, mtl_item_sub_inventories item
WHERE item.organization_id = sub.organization_id
AND item.secondary_inventory = sub.secondary_inventory_name
AND item.inventory_item_id = p_itemid
AND sub.secondary_inventory_name = p_subinv
AND sub.organization_id = p_orgid
AND NVL (sub.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE)
AND ( (p_asset = 1 AND sub.quantity_tracked = 1)
OR ( NVL (p_asset, 0) <> 1
AND ( ( p_inv_asst = 'Y'
AND sub.asset_inventory = 1
AND sub.quantity_tracked = 1
)
OR (p_inv_asst = 'N')
)
)
);
CURSOR l_nonrestrictedlocators_csr (
p_location NUMBER,
p_orgid NUMBER,
p_subinventory VARCHAR2
)
IS
SELECT 1 dummy
FROM SYS.DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM mtl_item_locations
WHERE inventory_location_id = p_location
AND organization_id = p_orgid
AND subinventory_code = p_subinventory
AND NVL (disable_date, TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE));
CURSOR l_restrictedlocators_csr (
p_location NUMBER,
p_orgid NUMBER,
p_subinventory VARCHAR2,
p_itemid NUMBER
)
IS
SELECT 1 dummy
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM mtl_item_locations loc, mtl_secondary_locators item
WHERE loc.inventory_location_id = p_location
AND loc.organization_id = p_orgid
AND loc.subinventory_code = p_subinventory
AND NVL (loc.disable_date, TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND loc.inventory_location_id = item.secondary_locator
AND loc.organization_id = item.organization_id
AND item.inventory_item_id = p_itemid);
---------end Subinventory cursor
-- cussor to check department ---------
CURSOR l_department_exist (org_id NUMBER, dept_code VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM bom_departments
WHERE organization_id = org_id
AND department_code = dept_code);
-- cussor to check department ---------
CURSOR l_operation_exist (org_id NUMBER, dept_id NUMBER, op_code VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM bom_standard_operations
WHERE organization_id = org_id
AND department_id = dept_id
AND operation_code = op_code);
-- cussor to check resources ---------
CURSOR l_resources (org_id NUMBER, rs_code VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM bom_resources
WHERE organization_id = org_id
AND resource_code = rs_code);
BEGIN
FOR l_routing_rec IN l_xxdata_loading_routings
LOOP
-----------Referesh vairiable ----------------------
BEGIN
error_message := NULL;
resource_null := NULL;
operation_null := NULL;
dept_null := NULL;
dept_id := NULL;
attribut_null := NULL;
item_null := NULL;
item_count := NULL;
v_org_count := NULL;
v_altenate_null := NULL;
v_organization_id := NULL;
l_sub_loc_code := NULL;
l_expense_to_asset_transfer := NULL;
v_org_null := NULL;
assembly_item_id := NULL;
END;
-------------check valid org code---------------------
BEGIN
IF l_routing_rec.organization_code IS NOT NULL
THEN
BEGIN
SELECT COUNT (*)
INTO v_org_count
FROM mtl_parameters a, org_organization_definitions b
WHERE a.master_organization_id = b.organization_id
AND a.organization_code = l_routing_rec.organization_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_org_count := 0;
WHEN OTHERS
THEN
v_org_count := 0;
END;
BEGIN
SELECT a.organization_id
INTO v_organization_id
FROM mtl_parameters a, org_organization_definitions b
WHERE a.master_organization_id = b.organization_id
AND a.organization_code = l_routing_rec.organization_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_organization_id := NULL;
WHEN OTHERS
THEN
v_organization_id := NULL;
END;
IF v_org_count = 0
THEN
v_org_null :=
l_routing_rec.organization_code
|| ' Oragansation Code is invalid';
END IF;
ELSE
v_org_null := 'Oragansation Code can not be null';
v_org_count := NULL;
END IF;
END;
-------------Check valid Item_sgemnt-----------------
BEGIN
IF l_routing_rec.item_number IS NOT NULL
THEN
IF v_organization_id IS NOT NULL
THEN
BEGIN
SELECT COUNT (*)
INTO item_count
FROM mtl_system_items_b
WHERE organization_id = v_organization_id
AND segment1 = l_routing_rec.item_number;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
item_count := 0;
WHEN OTHERS
THEN
item_count := 0;
END;
END IF;
IF item_count = 0
THEN
item_null :=
l_routing_rec.item_number
|| ' is not defined in Organization';
ELSE
----------------------For inventory_item_id------------------
BEGIN
SELECT inventory_item_id
INTO assembly_item_id
FROM mtl_system_items_b
WHERE organization_id = v_organization_id
AND segment1 = l_routing_rec.item_number;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
assembly_item_id := NULL;
WHEN OTHERS
THEN
assembly_item_id := NULL;
END;
END IF;
ELSIF l_routing_rec.item_number IS NULL
THEN
item_null := 'item_Code Can not be Null.';
END IF;
END;
-------------Check vailid altenate designator--------
BEGIN
IF l_routing_rec.alternate_name IS NOT NULL
THEN
FOR l_alternate_rec IN
l_alternate_csr (p_orgid => v_organization_id,
p_alt => l_routing_rec.alternate_name
)
LOOP
v_altenate_null :=
l_routing_rec.alternate_name
|| ' invalid alternate designator for Organization';
END LOOP; -- invalid alternate
END IF;
END;
-------------Check item attributes for BOM
BEGIN
FOR l_item_rec IN
l_checkattributes_csr (p_orgid => v_organization_id,
p_assyid => assembly_item_id,
p_rtgtype => 1
)
LOOP
attribut_null := 'Item attributes are wrong for BOM.......';
END LOOP;
END;
-------------check valid subinventory and Locator----
BEGIN
IF l_routing_rec.completion_locator_id IS NOT NULL
AND l_routing_rec.completion_subinventory IS NULL
THEN
error_message := 'BOM_LOCATOR_INVALID';
END IF; -- locator without subinventory
IF l_routing_rec.completion_subinventory IS NOT NULL
THEN
FOR l_flags_rec IN
l_subinvflags_csr (p_itemid => assembly_item_id,
p_orgid => v_organization_id
)
LOOP
-- if item locator control is null, set to 1 (no loc control)
IF l_flags_rec.location_control_code IS NULL
THEN
l_flags_rec.location_control_code := 1;
END IF;
-- if subinv is not restricted and locator is, then make
-- locator unrestricted
IF l_flags_rec.restrict_subinventories_code = 2
AND l_flags_rec.restrict_locators_code = 1
THEN
l_flags_rec.restrict_locators_code := 2;
END IF;
-- Check if subinventory is valid
l_expense_to_asset_transfer :=
TO_NUMBER (fnd_profile.VALUE ( 'INV'
|| ':'
|| 'EXPENSE_TO_ASSET_TRANSFER'
)
);
l_sub_loc_code := NULL;
IF l_flags_rec.restrict_subinventories_code = 2
THEN
-- non-restricted subinventory
FOR l_subinv_rec IN
l_nonrestrictedsubinv_csr
(p_subinv => l_routing_rec.completion_subinventory,
p_orgid => v_organization_id,
p_asset => l_expense_to_asset_transfer,
p_inv_asst => l_flags_rec.inventory_asset_flag
)
LOOP
l_sub_loc_code := l_subinv_rec.locator_type;
END LOOP; -- get sublocator code
ELSE -- restricted subinventory
FOR l_subinv_rec IN
l_restrictedsubinv_csr
(p_subinv => l_routing_rec.completion_subinventory,
p_orgid => v_organization_id,
p_itemid => assembly_item_id,
p_asset => l_expense_to_asset_transfer,
p_inv_asst => l_flags_rec.inventory_asset_flag
)
LOOP
l_sub_loc_code := l_subinv_rec.locator_type;
END LOOP; -- get sublocator code
END IF; -- restricted or nonrestricted subinventory
IF l_sub_loc_code IS NULL
THEN
error_message := 'BOM_SUBINV_INVALID1';
END IF;
-- Validate locator
-- Org level
IF l_flags_rec.stock_locator_control_code = 1
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
error_message := error_message || 'BOM_SUBINV_INVALID1';
END IF;
IF l_flags_rec.stock_locator_control_code IN (2, 3)
AND l_routing_rec.completion_locator_id IS NULL
THEN
error_message := error_message || 'BOM_SUBINV_INVALID2';
END IF;
IF l_flags_rec.stock_locator_control_code IN (2, 3)
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
IF l_flags_rec.restrict_locators_code = 2
THEN
-- non-restricted locator
FOR l_locator_rec IN
l_nonrestrictedlocators_csr
(p_location => l_routing_rec.completion_locator_id,
p_orgid => v_organization_id,
p_subinventory => l_routing_rec.completion_subinventory
)
LOOP
error_message :=
error_message || 'BOM_SUBINV_INVALID3';
END LOOP;
ELSE -- restricted locator
FOR l_locator_rec IN
l_restrictedlocators_csr
(p_location => l_routing_rec.completion_locator_id,
p_orgid => v_organization_id,
p_subinventory => l_routing_rec.completion_subinventory,
p_itemid => assembly_item_id
)
LOOP
error_message :=
error_message || 'BOM_LOCATOR_INVALID6';
END LOOP;
END IF; -- restricted or non-restricted locator
END IF; -- check if item location exists
IF l_flags_rec.stock_locator_control_code NOT IN
(1, 2, 3, 4)
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
error_message := error_message || 'BOM_LOCATOR_INVALID7';
END IF;
-- Subinventory level
IF l_flags_rec.stock_locator_control_code = 4
AND l_sub_loc_code = 1
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
error_message := error_message || 'BOM_LOCATOR_INVALID8';
END IF;
IF l_flags_rec.stock_locator_control_code = 4
THEN
IF l_sub_loc_code IN (2, 3)
AND l_routing_rec.completion_locator_id IS NULL
THEN
error_message :=
error_message || 'BOM_LOCATOR_INVALID9';
END IF;
IF l_sub_loc_code IN (2, 3)
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
IF l_flags_rec.restrict_locators_code = 2
THEN
-- non-restricted locator
FOR x_location IN
l_nonrestrictedlocators_csr
(p_location => l_routing_rec.completion_locator_id,
p_orgid => v_organization_id,
p_subinventory => l_routing_rec.completion_subinventory
)
LOOP
error_message :=
error_message || 'BOM_LOCATOR_INVALID10';
END LOOP;
ELSE -- restricted locator
FOR l_location_rec IN
l_restrictedlocators_csr
(p_location => l_routing_rec.completion_locator_id,
p_orgid => v_organization_id,
p_subinventory => l_routing_rec.completion_subinventory,
p_itemid => assembly_item_id
)
LOOP
error_message :=
error_message || 'BOM_LOCATOR_INVALID1';
END LOOP;
END IF; -- locator exists?
END IF; -- subinventory required locator
IF l_sub_loc_code NOT IN (1, 2, 3, 5)
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
error_message :=
error_message || 'BOM_LOCATOR_INVALID2';
END IF;
END IF; -- org locator = 4
-- Item level
IF l_flags_rec.stock_locator_control_code = 4
AND l_sub_loc_code = 5
AND l_flags_rec.location_control_code = 1
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
error_message := error_message || 'BOM_LOCATOR_INVALID3';
END IF;
IF l_flags_rec.location_control_code IN (2, 3)
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
IF l_flags_rec.restrict_locators_code = 2
THEN
-- non-restricted locator
FOR l_location_rec IN
l_nonrestrictedlocators_csr
(p_location => l_routing_rec.completion_locator_id,
p_orgid => v_organization_id,
p_subinventory => l_routing_rec.completion_subinventory
)
LOOP
error_message :=
error_message || 'BOM_LOCATOR_INVALID4';
END LOOP;
ELSE -- restricted locator
FOR l_location_rec IN
l_restrictedlocators_csr
(p_location => l_routing_rec.completion_locator_id,
p_orgid => v_organization_id,
p_subinventory => l_routing_rec.completion_subinventory,
p_itemid => assembly_item_id
)
LOOP
error_message :=
error_message || 'BOM_LOCATOR_INVALID5';
END LOOP;
END IF; -- locator exists?
END IF; -- locator control in (2, 3)
IF l_flags_rec.location_control_code NOT IN (1, 2, 3)
AND l_routing_rec.completion_locator_id IS NOT NULL
THEN
error_message := error_message || 'BOM_LOCATOR_INVALID6';
END IF;
END LOOP; -- SubInvFlags
END IF; -- Completion SubInventory specified
END;
--------------------------For Operations -----------------
BEGIN
IF l_routing_rec.department_code IS NOT NULL
AND v_organization_id IS NOT NULL
THEN
FOR l_rec IN l_department_exist (v_organization_id,
l_routing_rec.department_code
)
LOOP
dept_null :=
l_routing_rec.department_code
|| ' Department code is invalid for this organization.';
END LOOP;
IF dept_null IS NULL
THEN
BEGIN
SELECT department_id
INTO dept_id
FROM bom_departments
WHERE organization_id = v_organization_id
AND department_code = l_routing_rec.department_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
dept_id := NULL;
WHEN OTHERS
THEN
dept_id := NULL;
END;
END IF;
END IF;
END;
BEGIN
IF l_routing_rec.operation_code IS NOT NULL
THEN
IF dept_id IS NOT NULL AND v_organization_id IS NOT NULL
THEN
FOR l_op_rec IN
l_operation_exist (v_organization_id,
dept_id,
l_routing_rec.operation_code
)
LOOP
operation_null :=
l_routing_rec.operation_code
|| ' Operation Code is invalid for this organization ';
END LOOP;
END IF;
ELSE
operation_null := 'Operation Code can not be null ';
END IF;
END;
-----------------------For Resource ---------------
BEGIN
IF l_routing_rec.resource_code IS NOT NULL
THEN
FOR l_res_rec IN l_resources (v_organization_id,
l_routing_rec.resource_code
)
LOOP
resource_null := 'Resource code is invalid ...';
END LOOP;
END IF;
END;
IF resource_null IS NOT NULL
OR operation_null IS NOT NULL
OR dept_null IS NOT NULL
OR error_message IS NOT NULL
OR attribut_null IS NOT NULL
OR item_null IS NOT NULL
OR v_org_null IS NOT NULL
THEN
final_error_msg :=
v_org_null
|| ';'
|| item_null
|| ';'
|| attribut_null
|| ';'
|| error_message
|| ' ;'
|| dept_null
|| ';'
|| operation_null
|| ';'
|| resource_null;
UPDATE xx_bom_routing
SET vailidate_flag_process = 'E',
vailidate_error_message = final_error_msg
WHERE entry_id = l_routing_rec.entry_id;
ELSE
UPDATE xx_bom_routing
SET vailidate_flag_process = 'P'
WHERE entry_id = l_routing_rec.entry_id;
END IF;
COMMIT;
END LOOP;
END xx_validate_routing_date;
2 comments:
Thanks for sharing This informative article. It would be helpful to all ,Keep sharing.
Oracle Fusion HCM Online Training
Good Blog thanks for sharing this informative article. It would be helpful for improving their knowledge.
Oracle Fusion SCM Online Training
Post a Comment