Tuesday, December 14, 2021

BOM Routing Interface in Oracle Apps (Validation Script)

 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;

No comments:

Post a Comment

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...