[Home] [Help]
PACKAGE BODY: APPS.GMD_CONC_REPLACE_PKG
Source
1 PACKAGE BODY gmd_conc_replace_pkg AS
2 /* $Header: GMDROPRB.pls 120.21 2006/09/19 03:39:42 kamanda noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_CONC_REPLACE_PKG';
5
6 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
7 --Forward declaration.
8 FUNCTION set_debug_flag RETURN VARCHAR2;
9 l_debug VARCHAR2(1) := set_debug_flag;
10
11 FUNCTION set_debug_flag RETURN VARCHAR2 IS
12 l_debug VARCHAR2(1):= 'N';
13 BEGIN
14 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
15 l_debug := 'Y';
16 END IF;
17 RETURN l_debug;
18 END set_debug_flag;
19 --Bug 3222090, NSRIVAST 20-FEB-2004, END
20
21 /*#############################################################
22 # NAME
23 # delete_recipe_dependencies
24 # SYNOPSIS
25 # delete_recipe_dependencies
26 # DESCRIPTION
27 # Deletes Recipe Dependencies when either its formula or Recipe
28 # information is changed.
29 ###############################################################*/
30 PROCEDURE delete_recipe_dependencies(precipe_id NUMBER,
31 update_item VARCHAR2) IS
32 l_api_name VARCHAR2(100) := 'DELETE_RECIPE_DEPENDENCIES';
33 BEGIN
34 IF (update_item = 'FORMULA') THEN
35 /* Bug 3037410 Appended the where clause to check for the
36 validity rule's Product */
37 DELETE FROM gmd_recipe_validity_rules
38 WHERE recipe_id = precipe_id AND
39 inventory_item_id NOT IN (SELECT inventory_item_id FROM fm_matl_dtl
40 WHERE formula_id = (SELECT formula_id FROM gmd_recipes_b
41 WHERE recipe_id = precipe_id)
42 AND line_type = 1);
43
44 fnd_message.set_name('GMD', 'GMD_DELETE_RECIPE_FM_DEP');
45 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
46 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
47
48 ELSIF(update_item = 'ROUTING') THEN
49 DELETE FROM gmd_recipe_routing_steps
50 WHERE recipe_id = precipe_id;
51
52 DELETE FROM gmd_recipe_orgn_activities
53 WHERE recipe_id = precipe_id;
54
55 DELETE FROM gmd_recipe_orgn_resources
56 WHERE recipe_id = precipe_id;
57
58 fnd_message.set_name('GMD', 'GMD_DELETE_RECIPE_RT_DEP');
59 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
60 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
61 END IF;
62
63 DELETE FROM gmd_recipe_step_materials
64 WHERE recipe_id = precipe_id;
65
66 fnd_message.set_name('GMD', 'GMD_DELETE_STEP_MAT_ASSOC');
67 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
68 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
73 END delete_recipe_dependencies;
74
75 /*###############################################################
76 # NAME
77 # Populate_search_table
78 # SYNOPSIS
79 # Populate_search_table
80 # DESCRIPTION
81 # Performs populates a PL/SQL table with the search query.
82 ###############################################################*/
83 Procedure Populate_search_table(X_search_tbl OUT NOCOPY Search_Result_Tbl) IS
84 l_api_name VARCHAR2(100) := 'POPULATE_SEARCH_TABLE';
85 l_dsql_text VARCHAR2(2000);
86 l_cursor_id int;
87 l_num_of_rows NUMBER;
88 l_value NUMBER;
89 l_row_cnt NUMBER := 0;
90 l_error VARCHAR2(2000);
91 l_Object_id NUMBER;
92 l_object_name VARCHAR2(240);
93 l_object_vers NUMBER;
94 l_object_desc VARCHAR2(240);
95 l_object_status_desc VARCHAR2(240);
96 l_object_select_ind NUMBER;
97 l_object_status_code VARCHAR2(240);
98
99 l_debug_text VARCHAR2(2000);
100
101 BEGIN
102 -- Delete rows from previous searches
103 DELETE FROM gmd_msnr_results
104 WHERE concurrent_id IS NULL;
105
106 l_cursor_id := dbms_sql.open_cursor;
107 fnd_dsql.set_cursor(l_cursor_id);
108 l_dsql_text := fnd_dsql.get_text(FALSE);
109
110 l_debug_text := fnd_dsql.get_text(TRUE);
111 --insert into shy_text values (l_debug_text); commit;
112
113 dbms_sql.parse(l_cursor_id, l_dsql_text, dbms_sql.native);
114 fnd_dsql.do_binds;
115
116 dbms_sql.define_column(l_cursor_id, 1, l_Object_id );
117 dbms_sql.define_column(l_cursor_id, 2, l_object_name, 240 );
118 dbms_sql.define_column(l_cursor_id, 3, l_object_vers );
119 dbms_sql.define_column(l_cursor_id, 4, l_object_desc, 240 );
120 dbms_sql.define_column(l_cursor_id, 5, l_object_status_desc, 240 );
121 dbms_sql.define_column(l_cursor_id, 6, l_object_select_ind );
122 dbms_sql.define_column(l_cursor_id, 7, l_object_status_code, 240 );
123
124 l_num_of_rows := dbms_sql.execute(l_cursor_id);
125
126 LOOP
127 IF dbms_sql.fetch_rows(l_cursor_id) > 0 then
128 l_row_cnt := l_row_cnt + 1;
129
130 dbms_sql.column_value(l_cursor_id, 1, l_Object_id );
131 dbms_sql.column_value(l_cursor_id, 2, l_object_name );
132 dbms_sql.column_value(l_cursor_id, 3, l_object_vers );
133 dbms_sql.column_value(l_cursor_id, 4, l_object_desc );
134 dbms_sql.column_value(l_cursor_id, 5, l_object_status_desc );
135 dbms_sql.column_value(l_cursor_id, 6, l_object_select_ind );
136 dbms_sql.column_value(l_cursor_id, 7, l_object_status_code );
137
138 IF (l_object_status_code IN ('200','500','800','1000')) THEN
139 l_object_select_ind := 0;
140 END IF;
141
142 -- Populate the pl/sql table
143 -- This should go away soon !!!!!!
144 X_search_tbl(l_row_cnt).Object_id := l_object_id ;
145 X_search_tbl(l_row_cnt).object_name := l_object_name ;
146 X_search_tbl(l_row_cnt).object_vers := l_object_vers ;
147 X_search_tbl(l_row_cnt).object_desc := l_object_desc ;
148 X_search_tbl(l_row_cnt).object_status_desc := l_object_status_desc ;
149 X_search_tbl(l_row_cnt).object_select_ind := l_object_select_ind ;
150 X_search_tbl(l_row_cnt).object_status_code := l_object_status_code ;
151
152 -- Save the set of details in work table
153 INSERT INTO gmd_msnr_results
154 ( concurrent_id
155 ,object_id
156 ,object_name
157 ,object_vers
158 ,object_desc
159 ,object_status_code
160 ,object_status_desc
161 ,object_select_ind
162 )
163 VALUES
164 ( Null
165 ,l_object_id
166 ,l_object_name
167 ,l_object_vers
168 ,l_object_desc
169 ,l_object_status_code
170 ,l_object_status_desc
171 ,l_object_select_ind
172 );
173 ELSE
174 EXIT;
175 END IF;
176 END LOOP;
177
178 dbms_sql.close_cursor(l_cursor_id);
179 -- Commit all data populated
180 --Commit; Bug 4479488 Commented the commit
181 EXCEPTION
182 WHEN OTHERS THEN
183 IF (dbms_sql.is_open(l_cursor_id)) THEN
184 dbms_sql.close_cursor(l_cursor_id);
185 END IF;
186 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
187 END Populate_search_table;
188
189 /* ************************************************************************ */
190 /* API name : Mass_Replace_Operation */
191 /* Type : Private */
192 /* Function : */
193 /* Pre-reqs : None. */
194 /* Parameters : */
195 /* IN : prequest_id IN NUMBER (Required) */
196 /* Notes : Performs replace of one or more instance of entities like */
197 /* formula, routing, recipe, operation, Validity Rules. */
198 /* HISTORY */
199 /* 20-Feb-2003 Shyam Sitaraman Initial Implementation */
200 /* 17-MAR-2206 Kapil M Made changes for better log details */
201 /* 20-JUN-2006 Kapil M Changes made to get the recipe type */
202 /* value */
203 /* 19-SEP-2006 Kalyani Display orgn code in log file. */
204 /* 19-SEP-2006 Kalyani Check item access for override orgn */
205 /* 19-SEP-2006 Kalyani Removed unwanted fnd_msg_pub.add calls. */
206 /* ************************************************************************ */
207 PROCEDURE Mass_Replace_Operation ( err_buf OUT NOCOPY VARCHAR2,
208 ret_code OUT NOCOPY VARCHAR2,
209 pConcurrent_id IN NUMBER DEFAULT NULL,
210 pObject_type IN VARCHAR2,
211 pReplace_type IN VARCHAR2,
212 pOld_Name IN VARCHAR2,
213 pNew_Name IN VARCHAR2,
214 pOld_Version IN VARCHAR2 DEFAULT NULL,
215 pNew_Version IN VARCHAR2 DEFAULT NULL,
216 pScale_factor IN VARCHAR2 DEFAULT '1',
217 pVersion_flag IN VARCHAR2 DEFAULT 'N',
218 pCreate_Recipe IN NUMBER
219 ) IS
220 l_api_name VARCHAR2(100) := 'MASS_REPLACE_OPERATION';
221 l_mesg_count NUMBER;
222 l_mesg_data VARCHAR2(2000);
223 l_return_status VARCHAR2(1);
224 l_action_flag VARCHAR2(1) := 'U';
225 l_status_type GMD_STATUS_B.status_type%TYPE;
226
227 l_item_no VARCHAR2(2000);
228 l_formula_class FM_FORM_MST_B.formula_class%TYPE;
229 l_new_ingredient FM_MATL_DTL.inventory_item_id%TYPE;
230 l_old_ingredient FM_MATL_DTL.inventory_item_id%TYPE;
231 l_owner_id FM_FORM_MST_B.owner_id%TYPE;
232 l_formula_id FM_FORM_MST_B.formula_id%TYPE;
233 l_form_id FM_FORM_MST_B.formula_id%TYPE;
234 l_scale_factor NUMBER;
235
236 l_start_date VARCHAR2(32);
237 l_end_date VARCHAR2(32);
238 l_old_end_date VARCHAR2(32); -- Kapil
239
240 l_old_oprn GMD_OPERATIONS_B.oprn_no%TYPE;
241 l_new_oprn GMD_OPERATIONS_B.oprn_no%TYPE;
242 l_routing_class GMD_ROUTINGS_B.routing_class%TYPE;
243 l_routing_id GMD_ROUTINGS_B.routing_id%TYPE;
244 l_routingStep_id FM_ROUT_DTL.RoutingStep_id%TYPE;
245
246 l_old_actv GMD_OPERATION_ACTIVITIES.activity%TYPE;
247 l_new_actv GMD_OPERATION_ACTIVITIES.activity%TYPE;
248 l_oprn_class GMD_OPERATIONS_B.oprn_class%TYPE;
249 l_old_resource GMD_OPERATION_RESOURCES.resources%TYPE;
250 l_new_resource GMD_OPERATION_RESOURCES.resources%TYPE;
251 l_oprn_id GMD_OPERATIONS_B.oprn_id%TYPE;
252 l_oprn_line_id GMD_OPERATION_ACTIVITIES.oprn_line_id%TYPE;
253
254
255 l_owner_org GMD_RECIPES_B.owner_orgn_code%TYPE;
256 l_organization_id GMD_RECIPES_B.owner_organization_id%TYPE;
257 l_recipe_id GMD_RECIPES_B.recipe_id%TYPE;
258 l_recipe_type GMD_RECIPES_B.recipe_type%TYPE;
259
260 l_user_id NUMBER := FND_GLOBAL.USER_ID;
261
262 l_dummy_cnt NUMBER;
263 l_error_text VARCHAR2(2000);
264 l_rowcount NUMBER := 0;
265 l_object_version NUMBER;
266 l_orgn_id NUMBER;
267 l_vers_cntrl VARCHAR2(3);
268 l_return_stat VARCHAR2(10);
269
270 l_text VARCHAR2(100);
271 l_dependent_val BOOLEAN := FALSE;
272 l_object_name_vers VARCHAR2(200);
273
274 l_retval BOOLEAN;
275 l_version_enabled VARCHAR2(1);
276 l_status VARCHAR2(10);
277
278 -- Define different table types
279 p_rout_update_table GMD_ROUTINGS_PUB.update_tbl_type;
280 p_oprn_update_table GMD_OPERATIONS_PUB.update_tbl_type;
281 p_oprn_activity_table GMD_OPERATION_ACTIVITIES_PUB.update_tbl_type;
282 p_oprn_resources_table GMD_OPERATION_RESOURCES_PUB.update_tbl_type;
283 p_validity_rules_table GMD_VALIDITY_RULES_PVT.update_tbl_type;
284
285 CURSOR get_object_info IS
286 SElECT Upper(pObject_type) Object_type -- e.g 'FORMULA', 'RECIPE' etc
287 ,Upper(pReplace_type) Replace_type -- e.g 'FORMULA_CLASS'
288 ,pOld_Name Old_Name -- e.g 'SHY-TEST-FMCLS'
289 ,pNew_Name New_Name -- e.g 'TDAN-TEST-FMCLS'
290 ,pOld_Version Old_Version -- Applicable only for formula
291 ,pNew_Version New_version -- Routing and Operation
292 ,pScale_factor Scale_factor -- defaults to 1
293 ,pVersion_flag Version_flag -- defaults to 'N'
294 ,object_id -- e.g formula_id = 100
295 ,object_name -- e.g formula_no = 'SHY-TEST'
296 ,object_vers -- e.g formula_vers = 2
297 ,object_desc
298 ,object_status_code -- e.g formula_status = '100'
299 ,concurrent_id
300 FROM gmd_msnr_results
301 WHERE object_select_ind = 1 AND
302 concurrent_id = pConcurrent_id;
303
304 CURSOR Check_version_enabled(vStatus VARCHAR2) IS
305 SELECT version_enabled
306 FROM gmd_status_b
307 WHERE status_type = vStatus;
308
309 -- Cursor to get formula_id when recipe_id is passed
310 CURSOR get_formula_id(v_recp_id NUMBER) IS
311 SELECT formula_id
312 FROM gmd_recipes_b
313 WHERE recipe_id = v_recp_id;
314
315 -- Cursor to get formula_id when Validity Rule Id is passed
316 CURSOR get_recp_formula_id(v_vr_id NUMBER) IS
317 SELECT r.formula_id
318 FROM gmd_recipes_b r, gmd_recipe_validity_rules vr
319 WHERE vr.recipe_validity_rule_id = v_vr_id
320 AND r.recipe_id = vr.recipe_id;
321
322 CURSOR Cur_get_validity (V_rcp_vldty_rule_id NUMBER) IS
323 SELECT recipe_id
324 FROM gmd_recipe_validity_rules
325 WHERE recipe_validity_rule_id = V_rcp_vldty_rule_id;
326
327 CURSOR Cur_check_item (V_form_id NUMBER, V_item_id NUMBER) IS
328 SELECT 1
329 FROM mtl_system_items_b
330 WHERE inventory_item_id = V_item_id
331 AND recipe_enabled_flag = 'Y'
332 AND organization_id = (SELECT owner_organization_id
333 FROM fm_form_mst_b
334 WHERE formula_id = V_form_id);
335 /* BEGIN Declaration KSHUKLA bug 5198213 */
336 -- Cursor to check if the item revision is enabled
337 CURSOR cur_ITEM_VER_ENABLED(v_item_id NUMBER) IS
338 select REVISION_QTY_CONTROL_CODE
339 from mtl_system_items_b
340 where inventory_item_id = v_item_id;
341 CURSOR cur_item_ver_access(v_form_id NUMBER, v_item_id NUMBER) IS
342 select 1
343 from mtl_item_revisions
344 where inventory_item_id = v_item_id
345 and organization_id =(SELECT owner_organization_id
346 FROM fm_form_mst_b
347 WHERE formula_id = V_form_id)
348 and REVISION = pNew_Version ;
349 l_item_rev_ctl NUMBER;
350 l_rev_access NUMBER := 0;
351 /* END Declaration KSHUKLA bug 5198213 */
352
353 CURSOR Cur_get_recipe_org (V_recipe_id NUMBER) IS
354 SELECT owner_organization_id
355 FROM gmd_recipes_b
356 WHERE recipe_id = V_recipe_id;
357
358 -- Bug 5531717 Added
359 CURSOR Cur_get_recipe_override_org (V_recipe_id NUMBER) IS
360 SELECT organization_id
361 FROM gmd_recipe_process_loss
362 WHERE recipe_id = V_recipe_id;
363
364 l_recipe_override_orgn Cur_get_recipe_override_org%ROWTYPE;
365
366 -- Item substitution related change, BUG 4479101
367 CURSOR Cur_get_substitute_id(vOriginal_item_id NUMBER, V_form_id NUMBER) IS
368 SELECT substitution_id
369 FROM gmd_item_substitution_hdr_b
370 WHERE original_inventory_item_id = vOriginal_item_id
371 AND owner_organization_id = (SELECT owner_organization_id
372 FROM fm_form_mst_b
373 WHERE formula_id = V_form_id) ;
374
375 -- Cursor to chk if item is an expr item in formula owning orgn
376 CURSOR Cur_experimental_items(V_form_id NUMBER, V_item_id NUMBER) IS
377 SELECT COUNT(i.inventory_item_id)
378 FROM fm_form_mst f, mtl_system_items i
379 WHERE f.formula_id = V_form_id
380 AND i.organization_id = f.owner_organization_id
381 AND i.inventory_item_id = V_item_id
382 AND i.eng_item_flag = 'Y';
383
384
385 l_obj_id NUMBER;
386 l_org_id NUMBER;
387 l_itm_exists NUMBER := 0;
388 l_item_txt VARCHAR2(100);
389 l_expr_items_found NUMBER;
390
391 -- Exception declare
392 NO_UPDATE_EXCEPTION EXCEPTION;
393 NO_REPLACE_EXCEPTION EXCEPTION;
394
395 -- Internal Functions
396 FUNCTION get_recipe_use(vRecipe_use VARCHAR2) Return VARCHAR2 IS
397 CURSOR recipe_use_meaning(vRecipe_use VARCHAR2) IS
398 SELECT meaning
399 FROM Gem_lookups
400 WHERE lookup_type = 'GMD_FORMULA_USE'
401 AND lookup_code = vRecipe_use;
402
403 l_recipe_use VARCHAR2(100);
404 BEGIN
405 OPEN recipe_use_meaning(vRecipe_use);
406 FETCH recipe_use_meaning INTO l_recipe_use;
407 CLOSE recipe_use_meaning;
408
409 RETURN l_recipe_use;
410 END get_recipe_use;
411
412 FUNCTION get_item_no(vItem_id VARCHAR2) Return VARCHAR2 IS
413
414 CURSOR get_item(vItem_id VARCHAR2) IS
415 SELECT concatenated_segments
416 FROM mtl_system_items_kfv
417 WHERE inventory_item_id = vItem_id;
418
419 l_item_no VARCHAR2(2000);
420 BEGIN
421 OPEN get_item(vItem_id);
422 FETCH get_item INTO l_item_no;
423 CLOSE get_item;
424
425 RETURN l_item_no;
426 END get_item_no;
427
428
429
430 FUNCTION get_owner_name(vOwner_id VARCHAR2) Return VARCHAR2 IS
431 CURSOR get_owner(vOwner_id VARCHAR2) IS
432 SELECT user_name
433 FROM fnd_user
434 WHERE user_id = vOwner_id;
435 l_owner VARCHAR2(100);
436 BEGIN
437 OPEN get_owner(vOwner_id);
438 FETCH get_owner INTO l_owner;
439 CLOSE get_owner;
440
441 RETURN l_owner;
442 END get_owner_name;
443
444 FUNCTION get_status_meaning(P_status_code IN VARCHAR2) RETURN VARCHAR2 IS
445 CURSOR Cur_get IS
446 SELECT meaning
447 FROM gmd_status
448 WHERE status_code = P_status_code;
449
450 l_meaning gmd_status.meaning%TYPE;
451 BEGIN
452 OPEN Cur_get;
453 FETCH Cur_get INTO l_meaning;
454 CLOSE Cur_get;
455
456 RETURN l_meaning;
457 END get_status_meaning;
458
459 FUNCTION get_orgn_code(p_orgn_id IN NUMBER) RETURN VARCHAR2 IS
460 CURSOR Cur_get IS
461 SELECT organization_code
462 FROM mtl_parameters
463 WHERE organization_id = p_orgn_id;
464
465 l_orgn_code VARCHAR2(4);
466 BEGIN
467 OPEN Cur_get;
468 FETCH Cur_get INTO l_orgn_code;
469 CLOSE Cur_get;
470
471 RETURN l_orgn_code;
472 END get_orgn_code;
473
474 -- Bug# 5234792 Kapil M.
475 -- To retrieve the recipe_type
476 FUNCTION get_recipe_type(vRecipe_type VARCHAR2) Return VARCHAR2 IS
477 CURSOR recipe_type_meaning(vRecipe_type VARCHAR2) IS
478 SELECT meaning
479 FROM Gem_lookups
480 WHERE lookup_type = 'GMD_RECIPE_TYPE'
481 AND lookup_code = vRecipe_type;
482
483 l_recipe_type VARCHAR2(100);
484 BEGIN
485 OPEN recipe_type_meaning(vRecipe_type);
486 FETCH recipe_type_meaning INTO l_recipe_type;
487 CLOSE recipe_type_meaning;
488 RETURN l_recipe_type;
489 END get_recipe_type;
490
491 BEGIN
492 -- gmd_debug.log_initialize('MSNR');
493
494 IF (l_debug = 'Y') THEN
495 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : 1st line ');
496 END IF;
497
498 -- Using concurrent_id/request_id we get the details on the object and column that
499 -- is being replaced.
500 -- Please Note : Each request id can have multiple replace rows.
501 FOR get_object_rec IN get_object_info LOOP
502 -- Initialize the following variables
503 l_error_text := '';
504 l_return_status := 'S';
505
506 BEGIN
507 IF (get_object_rec.object_vers IS NULL) THEN
508 get_object_rec.object_name
509 := GMD_API_GRP.get_object_name_version
510 (get_object_rec.object_type
511 ,get_object_rec.object_id
512 ,'NAME');
513 get_object_rec.object_vers
514 := GMD_API_GRP.get_object_name_version
515 (get_object_rec.object_type
516 ,get_object_rec.object_id
517 ,'VERSION');
518 l_object_name_vers := get_object_rec.object_name||' - '||
519 get_object_rec.object_vers;
520 END IF;
521
522 -- Making new line entry and prompting users about MSNR request
523 -- Bug# 5008299 Kapil M
524 -- Moved the code for log file so that it is shown for every replace
525 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
526 FND_MESSAGE.SET_NAME('GMD','GMD_MSNR_REPLACE_MESG');
527 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
528 FND_MESSAGE.SET_TOKEN('NAME',get_object_rec.object_name);
529 FND_MESSAGE.SET_TOKEN('VERSION',get_object_rec.object_vers);
530 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
531 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
532 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
533
534 -- NPD Conv.
535 -- Check if the Entity Owning organization is available for the responsibility
536 IF NOT gmd_api_grp.Check_orgn_access( Entity => get_object_rec.object_type,
537 Entity_id => get_object_rec.object_id) THEN
538 RAISE NO_UPDATE_EXCEPTION;
539 END IF;
540
541 IF (l_debug = 'Y') THEN
542
543 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
544 ||' : Call Sts API - object type, replace_type, '
545 ||'object id , old_name/version and new_name = '
546 ||get_object_rec.object_type||' - '
547 ||get_object_rec.replace_type||' - '
548 ||get_object_rec.object_id||' - '
549 ||get_object_rec.old_name||' / '||get_object_rec.object_vers||' - '
550 ||get_object_rec.new_name);
551 END IF;
552
553 -- Check if the replaceable column is Status
554 -- If so then call the Change status API
555 IF get_object_rec.replace_type = 'STATUS' THEN
556
557 -- Call the change status API
558 GMD_STATUS_PUB.modify_status
559 ( p_entity_name => get_object_rec.object_type
560 , p_entity_id => get_object_rec.object_id
561 , p_to_status => get_object_rec.new_name
562 , p_ignore_flag => TRUE
563 , x_message_count => l_mesg_count
564 , x_message_list => l_mesg_data
565 , x_return_status => l_return_status
566 );
567
568 IF (l_debug = 'Y') THEN
569 gmd_debug.put_line
570 ('In MSNR replace : After Sts API - return status = '
571 ||l_return_status);
572 END IF;
573
574 -- Can return pending or success status
575 IF (l_return_status <> 'S') AND (l_return_status <> 'P') THEN
576 RAISE No_Update_Exception;
577 END IF;
578 ELSE -- for all other object-replace types.
579 -- Get the status type for this object
580 l_status_type := GMD_API_GRP.get_object_status_type
581 ( get_object_rec.object_type
582 , get_object_rec.object_id);
583
584 OPEN Check_version_enabled(l_status_type);
585 FETCH Check_version_enabled INTO l_version_enabled;
586 IF (Check_version_enabled%NOTFOUND) THEN
587 l_version_enabled := 'N';
588 END IF;
589 CLOSE Check_version_enabled;
590
591 gmd_debug.put_line('In MSNR Replace : The status type = '||l_status_type||
592 ' and the version flag API passes = '||get_object_rec.version_flag||
593 ' and version enabled check = '||l_version_enabled);
594
595 IF (get_object_rec.object_type <> 'VALIDITY') THEN
596 GMD_API_GRP.Validate_with_dep_entities
597 (get_object_rec.object_type,
598 get_object_rec.object_id,
599 l_dependent_val);
600 IF l_dependent_val THEN
601 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
602 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
603 END IF;
604 END IF;
605
606 -- Work thro' each object and call its appropriate APIs
607 IF (get_object_rec.object_type = 'FORMULA') THEN
608 -- Get the action flag to decide on update or insert(version control)
609
610 SELECT fm.owner_organization_id
611 INTO l_orgn_id
612 FROM fm_form_mst_b fm
613 WHERE fm.formula_id = get_object_rec.object_id;
614
615 -- Get the Formula version control for the entity orgn.
616 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => l_orgn_id,
617 P_parm_name => 'GMD_FORMULA_VERSION_CONTROL',
618 P_parm_value => l_vers_cntrl,
619 x_return_status => l_return_stat);
620
621 IF (l_vers_cntrl IN ('Y','O')) AND
622 (l_version_enabled = 'Y' ) AND (get_object_rec.Version_flag = 'Y') THEN
623 -- NPD Conv. added 100 here
624 -- bug #4758484
625 -- Enabling frozen formulas to be replaced if version enabled flag is Y
626 -- Bug # 5005145 .Changed the Condition for new creating vew version
627 IF (l_status_type IN ('100','300','400','600','700','900')) THEN
628 l_action_flag := 'I';
629 ELSE
630 l_action_flag := 'N';
631 END IF;
632 ELSE -- version flag is off
633 IF l_status_type IN ('100','300','400','600','700') THEN
634 l_action_flag := 'U';
635 ELSE
636 l_action_flag := 'N';
637 END IF;
638 END IF;
639
640 IF (l_debug = 'Y') THEN
641 gmd_debug.put_line
642 ('In MSNR replace : For formula Action flag = '
643 ||l_action_flag);
644 END IF;
645
646 IF (l_action_flag = 'N') THEN
647 FND_MESSAGE.SET_NAME('GMD', 'GMD_REPLACE_NOT_ALLOWED');
648 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
649 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
650 FND_MESSAGE.SET_TOKEN('STATUS',get_status_meaning(l_status_type) );
651 FND_MSG_PUB.ADD;
652 RAISE NO_UPDATE_EXCEPTION;
653 END IF;
654
655 -- Convert scale factor to number - applicable in non US number formats
656 l_scale_factor := fnd_number.canonical_to_number(get_object_rec.scale_factor);
657
658 -- Construct a PL/SQL table only if action is
659 -- either insert ('I') or update ('U')
660 -- l_action_flag is I, we need to create PLSQL table for
661 -- for both formula header and details
662
663
664 -- NPD Conv.
665 -- Check if the new item is available in the formula owning orgn.
666 IF (get_object_rec.replace_type = 'INGREDIENT') THEN
667 OPEN Cur_check_item(get_object_rec.object_id, get_object_rec.new_name);
668 FETCH Cur_check_item INTO l_itm_exists;
669 CLOSE Cur_check_item;
670 IF l_itm_exists <> 1 THEN
671 FND_MESSAGE.SET_NAME('gmd', 'GMD_FORMULA_ITMORG_NOT_FOUND');
672 FND_MESSAGE.SET_TOKEN('ITEM',get_item_no(get_object_rec.new_name));
673 FND_MESSAGE.SET_TOKEN('ORGN',get_orgn_code(l_orgn_id));
674 FND_MSG_PUB.ADD;
675 RAISE NO_UPDATE_EXCEPTION;
676 END IF;
677
678 -- Check if the Revision is enabled for the item.
679 -- KSHUKLA if the revision control is enabled
680 IF pNew_Version is not NULL THEN
681 OPEN cur_ITEM_VER_ENABLED(get_object_rec.new_name);
682 FETCH cur_ITEM_VER_ENABLED INTO l_item_rev_ctl;
683 CLOSE cur_ITEM_VER_ENABLED;
684 IF l_item_rev_ctl = 2 THEN
685 -- Rev control is enabled now check for the rev access.
686 OPEN cur_item_ver_access(get_object_rec.object_id, get_object_rec.new_name);
687 FETCH cur_item_ver_access INTO l_rev_access;
688 CLOSE cur_item_ver_access;
689 IF l_rev_access <> 1 THEN
690 -- Bug# 5198213 New message shown for items with revision
691 FND_MESSAGE.SET_NAME('GMD', 'GMD_FORMULA_ITMREV_NOT_FOUND');
692 FND_MESSAGE.SET_TOKEN('ITEM',get_item_no(get_object_rec.new_name));
693 FND_MESSAGE.set_token('REV',get_object_rec.new_version );
694 FND_MESSAGE.SET_TOKEN('ORGN',get_orgn_code(l_orgn_id));
695 FND_MSG_PUB.ADD;
696 RAISE NO_UPDATE_EXCEPTION;
697 END IF;
698 END IF;
699 END IF;
700 -- END KSHUKLA bug 5198213
701
702 -- Chk if we are trying to substitute an experimental item in Apfgu formula
703 IF l_status_type BETWEEN 700 AND 799 THEN
704 OPEN Cur_experimental_items(get_object_rec.object_id, get_object_rec.new_name);
705 FETCH Cur_experimental_items INTO l_expr_items_found;
706 CLOSE Cur_experimental_items;
707 IF l_expr_items_found > 0 THEN
708 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXPR_ITEMS_FOUND');
709 FND_MSG_PUB.ADD;
710 RAISE NO_UPDATE_EXCEPTION;
711 END IF;
712 END IF;
713 END IF;
714
715 IF (l_action_flag IN ('I')) THEN
716 -- Create formula header table
717 SELECT DECODE(get_object_rec.replace_type,'FORMULA_CLASS'
718 ,get_object_rec.new_name
719 ,NULL),
720 DECODE(get_object_rec.replace_type,'INGREDIENT'
721 ,get_object_rec.new_name
722 ,NULL),
723 DECODE(get_object_rec.replace_type,'INGREDIENT'
724 ,get_object_rec.old_name
725 ,NULL),
726 DECODE(get_object_rec.replace_type,'OWNER_ID'
727 ,get_object_rec.new_name
728 ,NULL)
729 INTO l_formula_class, l_new_ingredient, l_old_ingredient, l_owner_id
730 FROM dual;
731
732 IF (l_debug = 'Y') THEN
733 gmd_debug.put_line
734 ('In MSNR replace : In Insert mode '
735 ||' l_fm_class = '||l_formula_class
736 ||' l_new_ingredient = '||l_new_ingredient
737 ||' l_old_ingredient = '||l_old_ingredient
738 ||' l_owner_id = '||l_owner_id);
739 END IF;
740 -- Create a new formula version
741 gmd_search_replace_vers.create_new_formula
742 (p_formula_id => get_object_rec.object_id
743 ,p_formula_class => l_formula_class
744 ,p_new_ingredient => l_new_ingredient
745 ,p_old_ingredient => l_old_ingredient
746 ,p_old_ingr_revision => get_object_rec.old_version
747 ,p_new_ingr_revision => get_object_rec.new_version
748 ,p_inactive_ind => NULL
749 ,p_owner_id => l_owner_id
750 ,x_formula_id => l_formula_id
751 ,x_scale_factor => l_scale_factor
752 ,pCreate_Recipe => pCreate_Recipe
753 );
754 IF l_formula_id IS NULL THEN
755 RAISE No_Update_Exception;
756 ELSE
757 SELECT formula_vers
758 INTO l_object_version
759 FROM fm_form_mst_b
760 WHERE formula_id = l_formula_id;
761 -- Setup message to indicate that a new version
762 -- has been created
763 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_NEW_OBJECT_VERSION');
764 FND_MESSAGE.SET_TOKEN('VERSION',l_object_version);
765 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',get_object_rec.object_name);
766 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
767
768 IF (get_object_rec.replace_type = 'INGREDIENT') THEN
769 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','LM_INGREDIENT',true);
770 FND_MESSAGE.SET_TOKEN('NEW_ITEM',
771 get_item_no(get_object_rec.new_name) );
772 ELSIF (get_object_rec.replace_type = 'OWNER_ID') THEN
773 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OWNER',true);
774 FND_MESSAGE.SET_TOKEN('NEW_ITEM',
775 get_owner_name(get_object_rec.new_name) );
776 ELSE
777 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
778 FND_MESSAGE.SET_TOKEN('NEW_ITEM',get_object_rec.new_name);
779 END IF;
780
781 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
782 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
783 END IF;
784
785 ELSIF (l_action_flag IN ('U')) THEN
786 IF (get_object_rec.replace_type IN ('FORMULA_CLASS','OWNER_ID')) THEN
787 UPDATE fm_form_mst_b
788 SET formula_class = DECODE(get_object_rec.replace_type,'FORMULA_CLASS'
789 ,get_object_rec.new_name
790 ,formula_class),
791 owner_id = DECODE(get_object_rec.replace_type,'OWNER_ID'
792 ,get_object_rec.new_name
793 ,owner_id),
794 last_update_date = P_last_update_date,
795 last_updated_by = p_last_updated_by,
796 last_update_login = p_last_update_login
797 WHERE formula_id = get_object_rec.object_id;
798 ELSIF (get_object_rec.replace_type = 'INGREDIENT') THEN
799 IF (l_debug = 'Y') THEN
800 gmd_debug.put_line
801 ('In MSNR replace for formula : action flag = '
802 ||l_action_flag
803 ||' and replace type = '
804 ||get_object_rec.replace_type
805 ||' and scale factor = '
806 ||l_scale_factor
807 ||' and new item id = '
808 ||get_object_rec.new_name
809 ||' and user id '
810 ||l_user_id);
811 END IF;
812 --Bug 5237351 Validate item access
813 GMD_COMMON_VAL.CHECK_FORMULA_ITEM_ACCESS(get_object_rec.object_id,
814 get_object_rec.new_name,
815 l_return_status ,
816 get_object_rec.new_version);
817 IF l_return_status <> 'S' THEN
818 RAISE NO_UPDATE_EXCEPTION;
819 END IF;
820
821 UPDATE fm_matl_dtl
822 SET inventory_item_id = get_object_rec.new_name,
823 revision = get_object_rec.new_version,
824 qty = qty * l_scale_factor,
825 ingredient_end_date = Null, --bug 4479101
826 last_update_date = SYSDATE,
827 last_updated_by = l_user_id
828 WHERE formula_id = get_object_rec.object_id
829 AND line_type = -1
830 AND inventory_item_id = get_object_rec.old_name
831 AND NVL(revision, -1) = NVL(get_object_rec.old_version, -1);
832 -- Raise and exception is replace was not performed
833 IF (SQL%NOTFOUND) THEN
834 FND_MESSAGE.SET_NAME('GMD', 'GMD_FORM_UPD_NO_ACCESS');
835 FND_MSG_PUB.ADD;
836 RAISE NO_UPDATE_EXCEPTION;
837 END IF;
838 -- Item substitution realted fix, Bug 4479101
839 FOR my_subs_rec IN Cur_get_substitute_id(get_object_rec.old_name, get_object_rec.object_id) LOOP --bug 4479101
840 DELETE from gmd_formula_substitution
841 WHERE formula_id = get_object_rec.object_id
842 AND substitution_id = my_subs_rec.substitution_id;
843 END LOOP;
844 END IF;
845 END IF; -- when action_flag is either 'U' or 'I'
846 ELSIF (get_object_rec.object_type = 'RECIPE') THEN
847 -- Get the action flag to decide on update or insert(version control)
848 SELECT rcp.owner_organization_id
849 INTO l_orgn_id
850 FROM gmd_recipes_b rcp
851 WHERE rcp.recipe_id = get_object_rec.object_id;
852
853 -- Get the Recipe version control for the entity orgn.
854 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => l_orgn_id,
855 P_parm_name => 'GMD_RECIPE_VERSION_CONTROL',
856 P_parm_value => l_vers_cntrl,
857 x_return_status => l_return_stat);
858
859 IF (l_vers_cntrl IN ('Y','O')) AND
860 (l_version_enabled = 'Y' ) AND (get_object_rec.Version_flag = 'Y') THEN
861 -- Bug # 5005145 .Changed the Condition for new creating vew version
862 IF (l_status_type IN ('300','400')) THEN
863 IF (get_object_rec.replace_type <> 'FORMULA_ID') THEN
864 l_action_flag := 'I';
865 ELSE
866 l_action_flag := 'N';
867 END IF;
868 ELSIF (l_status_type IN ('600','700') ) THEN
869 IF (get_object_rec.replace_type = 'FORMULA_ID') THEN
870 l_action_flag := 'I'; -- kkillams, 'N' is replaced with th 'I' w.r.t. bug 4013844
871 -- Should allow to update the formula as version control is set YES.
872 ELSIF (get_object_rec.replace_type = 'ROUTING_ID') THEN
873 IF ((get_object_rec.old_name IS NULL) AND
874 (get_object_rec.new_name IS NOT NULL) ) THEN
875 l_action_flag := 'I';
876 ELSE
877 l_action_flag := 'N';
878 END IF;
879 ELSE -- other columns
880 l_action_flag := 'I';
881 END IF;
882 ELSE -- for all other status types
883 l_action_flag := 'N';
884 END IF;
885 ELSE -- When version control is off
886 IF (l_debug = 'Y') THEN
887 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
888 ||' : About check if Recipe update is allowed, Recipe Id = '
889 ||get_object_rec.object_id);
890 END IF;
891
892 IF NOT GMD_API_GRP.Check_orgn_access
893 (Entity => 'RECIPE'
894 ,Entity_id => get_object_rec.object_id) THEN
895 RAISE NO_UPDATE_EXCEPTION;
896 ELSE
897 IF (l_status_type IN ('200','500','800','900','1000')) THEN
898 l_action_flag := 'N';
899 ELSIF (l_status_type IN ('300','400','600','700')) THEN
900 IF (get_object_rec.replace_type = 'FORMULA_ID') THEN
901 l_action_flag := 'N';
902 ELSIF (get_object_rec.replace_type = 'ROUTING_ID') THEN
903 IF (l_status_type IN ('300','400','600') ) THEN
904 l_action_flag := 'U';
905 ELSIF ((get_object_rec.old_name IS NULL) AND
906 (get_object_rec.new_name IS NOT NULL) AND
907 (l_status_type = '700')) THEN
908 l_action_flag := 'U';
909 ELSE
910 l_action_flag := 'N';
911 END IF;
912 END IF; -- check for recipe columns end here
913 END IF; -- check for status type ends here
914 END IF; -- check for GMD_API_GRP.Check_orgn_access
915 END IF; -- check for version control on / off
916
917 IF (l_debug = 'Y') THEN
918 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : For Recipe Action flag = '
919 ||l_action_flag);
920 END IF;
921
922 IF (l_action_flag = 'N') THEN
923 FND_MESSAGE.SET_NAME('GMD', 'GMD_REPLACE_NOT_ALLOWED');
924 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
925 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
926 FND_MESSAGE.SET_TOKEN('STATUS',get_status_meaning(l_status_type) );
927 FND_MSG_PUB.ADD;
928 RAISE NO_UPDATE_EXCEPTION;
929 END IF;
930
931 SELECT DECODE(get_object_rec.replace_type,'FORMULA_ID'
932 ,get_object_rec.new_name
933 ,NULL),
934 DECODE(get_object_rec.replace_type,'ROUTING_ID'
935 ,get_object_rec.new_name
936 ,NULL),
937 DECODE(get_object_rec.replace_type,'OWNER_ORGN_CODE'
938 ,get_object_rec.new_name
939 ,NULL),
940 DECODE(get_object_rec.replace_type,'RECIPE_TYPE'
941 ,get_object_rec.new_name
942 ,NULL),
943 DECODE(get_object_rec.replace_type,'OWNER_ID'
944 ,get_object_rec.new_name
945 ,NULL)
946 INTO l_formula_id, l_routing_id, l_organization_id, l_recipe_type, l_owner_id
947 FROM dual;
948
949 -- NPD Conv
950 IF (get_object_rec.replace_type = 'OWNER_ORGN_CODE') THEN
951
952 -- Get formula associated with recipe
953 OPEN get_formula_id (get_object_rec.object_id);
954 FETCH get_formula_id INTO l_form_id;
955 CLOSE get_formula_id;
956
957 -- Check if formula items belong to the new recipe organization
958 GMD_API_GRP.check_item_exists
959 (p_formula_id => l_form_id
960 ,p_organization_id => l_organization_id
961 ,x_return_status => l_status );
962
963 IF l_status <> FND_API.g_ret_sts_success THEN
964 -- Bug 5531791 Removed FND_MSG_PUB.GET and FND_MSG_PUB.ADD as the message
965 -- is already added by GMD_API_GRP.check_item_exists
966 RAISE NO_UPDATE_EXCEPTION;
967 END IF;
968
969 END IF;
970
971 -- NPD Conv
972 IF (get_object_rec.replace_type = 'FORMULA_ID') THEN
973
974 -- Get the recipe owning organization
975 OPEN Cur_get_recipe_org(get_object_rec.object_id);
976 FETCH Cur_get_recipe_org INTO l_org_id;
977 CLOSE Cur_get_recipe_org;
978
979 -- Check if new formula's items belong to the recipe organization
980 GMD_API_GRP.check_item_exists
981 (p_formula_id => l_formula_id
982 ,p_organization_id => l_org_id
983 ,x_return_status => l_status );
984
985 IF l_status <> FND_API.g_ret_sts_success THEN
986 -- Bug 5531791 Removed FND_MSG_PUB.GET and FND_MSG_PUB.ADD as the message
987 -- is already added by GMD_API_GRP.check_item_exists
988 RAISE NO_UPDATE_EXCEPTION;
989 END IF;
990 -- Bug 5531717 add code to check for ovverride orgn
991 FOR l_recipe_override_orgn IN Cur_get_recipe_override_org(get_object_rec.object_id)
992 LOOP
993 -- Check if new formula's items belong to the recipe organization
994 GMD_API_GRP.check_item_exists
995 (p_formula_id => l_formula_id
996 ,p_organization_id => l_recipe_override_orgn.organization_id
997 ,x_return_status => l_status );
998
999 IF l_status <> FND_API.g_ret_sts_success THEN
1000 RAISE NO_UPDATE_EXCEPTION;
1001 END IF;
1002 END LOOP;
1003 END IF;
1004
1005 IF (l_action_flag IN ('I')) THEN
1006
1007 GMD_SEARCH_REPLACE_VERS.create_new_recipe
1008 (p_recipe_id => get_object_rec.object_id
1009 ,p_routing_id => l_routing_id
1010 ,p_formula_id => l_formula_id
1011 ,powner_id => l_owner_id
1012 ,powner_orgn_code => NULL
1013 ,p_Organization_Id => l_organization_id
1014 ,p_recipe_type => l_recipe_type
1015 ,x_recipe_id => l_recipe_id);
1016
1017 IF (l_recipe_id IS NULL) THEN
1018 RAISE No_Update_Exception;
1019 ELSE
1020 SELECT recipe_version
1021 INTO l_object_version
1022 FROM gmd_recipes_b
1023 WHERE recipe_id = l_recipe_id;
1024
1025 -- Setup message to indicate that a new version
1026 -- has been created
1027 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_NEW_OBJECT_VERSION');
1028 FND_MESSAGE.SET_TOKEN('VERSION',l_object_version);
1029 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',get_object_rec.object_name);
1030 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
1031
1032 IF (get_object_rec.replace_type = 'FORMULA_ID' ) THEN
1033 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_FORMULA',true);
1034 FND_MESSAGE.SET_TOKEN('NEW_ITEM',
1035 GMD_API_GRP.get_object_name_version
1036 ('FORMULA'
1037 ,get_object_rec.new_name)
1038 );
1039 ELSIF (get_object_rec.replace_type = 'ROUTING_ID' ) THEN
1040 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_ROUTING',true);
1041 FND_MESSAGE.SET_TOKEN('NEW_ITEM',
1042 GMD_API_GRP.get_object_name_version
1043 ('ROUTING'
1044 ,get_object_rec.new_name)
1045 );
1046 ELSIF (get_object_rec.replace_type = 'OWNER_ID') THEN
1047 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OWNER',true);
1048 FND_MESSAGE.SET_TOKEN('NEW_ITEM',
1049 get_owner_name(get_object_rec.new_name) );
1050 ELSIF (get_object_rec.replace_type = 'RECIPE_TYPE') THEN
1051 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_RECIPE_TYPE',true);
1052 FND_MESSAGE.SET_TOKEN('NEW_ITEM',
1053 get_recipe_type(get_object_rec.new_name) );
1054 -- Bug# 5234792 To get the recipe type value.
1055 ELSE
1056 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
1057 FND_MESSAGE.SET_TOKEN('NEW_ITEM',get_object_rec.new_name);
1058 END IF;
1059
1060
1061 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1062 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1063 END IF;
1064
1065 ELSIF (l_action_flag IN ('U')) THEN
1066 -- Perform updates
1067 UPDATE gmd_recipes_b
1068 SET formula_id = DECODE(get_object_rec.replace_type,'FORMULA_ID'
1069 ,get_object_rec.new_name
1070 ,formula_id),
1071 routing_id = DECODE(get_object_rec.replace_type,'ROUTING_ID'
1072 ,get_object_rec.new_name
1073 ,routing_id),
1074 owner_id = DECODE(get_object_rec.replace_type,'OWNER_ID'
1075 ,get_object_rec.new_name
1076 ,owner_id),
1077 owner_organization_id = DECODE(get_object_rec.replace_type,'OWNER_ORGN_CODE'
1078 ,get_object_rec.new_name
1079 ,owner_organization_id),
1080 recipe_type = DECODE(get_object_rec.replace_type,'RECIPE_TYPE'
1081 ,get_object_rec.new_name
1082 ,recipe_type),
1083 last_update_date = P_last_update_date,
1084 last_updated_by = p_last_updated_by,
1085 last_update_login = p_last_update_login
1086 WHERE recipe_id = get_object_rec.object_id;
1087
1088 IF (sql%notfound) THEN
1089 FND_MESSAGE.SET_NAME('GMD', 'GMD_RCP_UPD_NO_ACCESS');
1090 FND_MSG_PUB.ADD;
1091 RAISE NO_UPDATE_EXCEPTION;
1092 END IF;
1093 -- Delete all recipe dependencies if either formula or
1094 -- routing is replaced.
1095 IF (l_debug = 'Y') THEN
1096 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||
1097 ': About check fm and rout dependency with Recipe = '||
1098 get_object_rec.object_id||
1099 ' Replace column = '||get_object_rec.replace_type||
1100 ' Replace value = '||get_object_rec.new_name);
1101 END IF;
1102
1103 IF (get_object_rec.replace_type = 'FORMULA_ID') THEN
1104 delete_recipe_dependencies(get_object_rec.object_id,'FORMULA');
1105 ELSIF (get_object_rec.replace_type = 'ROUTING_ID') THEN
1106 IF (get_object_rec.new_name IS NOT NULL) THEN
1107 delete_recipe_dependencies(get_object_rec.object_id,'ROUTING');
1108 END IF;
1109 END IF;
1110
1111 IF (l_debug = 'Y') THEN
1112 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||
1113 ' After checking the fm and rout dep with Recipe ');
1114 END IF;
1115
1116 END IF; -- When action flag is either 'U' or 'I'
1117
1118 ELSIF (get_object_rec.object_type = 'ROUTING') THEN
1119 -- Get the action flag to decide on update or insert(version control)
1120 SELECT rot.owner_organization_id , rot.effective_end_date
1121 INTO l_orgn_id, l_old_end_date
1122 FROM gmd_routings_b rot
1123 WHERE rot.routing_id = get_object_rec.object_id;
1124
1125 -- Get the Routing version control for the entity orgn.
1126 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => l_orgn_id,
1127 P_parm_name => 'GMD_ROUTING_VERSION_CONTROL',
1128 P_parm_value => l_vers_cntrl,
1129 x_return_status => l_return_stat);
1130
1131 IF (l_vers_cntrl IN ('Y','O')) AND
1132 (l_version_enabled = 'Y') AND (get_object_rec.Version_flag = 'Y') THEN
1133 -- Bug # 5005145 .Changed the Condition for new creating vew version
1134 IF (l_status_type IN ('400', '700')) THEN
1135 l_action_flag := 'I';
1136 ELSIF ((l_status_type = '900') AND
1137 (get_object_rec.replace_type = 'END_DATE') )THEN
1138 l_action_flag := 'I';
1139 ELSE
1140 l_action_flag := 'N';
1141 END IF;
1142 ELSE
1143 IF(l_status_type IN ('100','300','400','600','700') ) OR
1144 (l_status_type = '900' AND get_object_rec.replace_type = 'END_DATE')THEN
1145 l_action_flag := 'U';
1146 ELSE
1147 l_action_flag := 'N';
1148 END IF;
1149 END IF;
1150
1151 IF (l_debug = 'Y') THEN
1152 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
1153 ||' : For Routing Action flag = '||l_action_flag);
1154 END IF;
1155
1156 IF (l_action_flag = 'N') THEN
1157 FND_MESSAGE.SET_NAME('GMD', 'GMD_REPLACE_NOT_ALLOWED');
1158 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
1159 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
1160 FND_MESSAGE.SET_TOKEN('STATUS',get_status_meaning(l_status_type) );
1161 FND_MSG_PUB.ADD;
1162 RAISE NO_UPDATE_EXCEPTION;
1163 END IF;
1164
1165 IF (l_action_flag IN ('I')) THEN
1166 SELECT DECODE(get_object_rec.replace_type,'START_DATE'
1167 ,get_object_rec.new_name
1168 ,NULL),
1169 DECODE(get_object_rec.replace_type,'END_DATE'
1170 ,get_object_rec.new_name
1171 ,NULL),
1172 DECODE(get_object_rec.replace_type,'OPRN_ID'
1173 ,get_object_rec.old_name
1174 ,NULL),
1175 DECODE(get_object_rec.replace_type,'OPRN_ID'
1176 ,get_object_rec.new_name
1177 ,NULL),
1178 DECODE(get_object_rec.replace_type,'ROUTING_CLASS'
1179 ,get_object_rec.new_name
1180 ,NULL),
1181 DECODE(get_object_rec.replace_type,'OWNER_ID'
1182 ,get_object_rec.new_name
1183 ,NULL)
1184 INTO l_start_date, l_end_date, l_old_oprn, l_new_oprn,
1185 l_routing_class, l_owner_id
1186 FROM dual;
1187 -- Bug# 5493773 Kapil M Pass the old End date
1188 IF NOT get_object_rec.replace_type = 'END_DATE' THEN
1189 l_end_date := FND_DATE.date_to_canonical(l_old_end_date);
1190 END IF;
1191
1192 -- Call the insert API
1193 gmd_search_replace_vers.create_new_routing
1194 (p_routing_id => get_object_rec.object_id
1195 ,p_effective_start_date => l_start_date
1196 ,p_effective_end_date => l_end_date
1197 ,p_inactive_ind => NULL
1198 ,p_owner => l_owner_id
1199 ,p_old_operation => l_old_oprn
1200 ,p_new_operation => l_new_oprn
1201 ,p_routing_class => l_routing_class
1202 ,x_routing_id => l_routing_id
1203 );
1204
1205 IF (l_routing_id IS NULL) THEN
1206 RAISE No_Update_Exception;
1207 ELSE
1208 SELECT routing_vers
1209 INTO l_object_version
1210 FROM gmd_routings_b
1211 WHERE routing_id = l_routing_id;
1212
1213 -- Setup message to indicate that a new version
1214 -- has been created
1215 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_NEW_OBJECT_VERSION');
1216 FND_MESSAGE.SET_TOKEN('VERSION',l_object_version);
1217 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',get_object_rec.object_name);
1218 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
1219
1220 IF (get_object_rec.replace_type = 'OPRN_ID') THEN
1221 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OPERATION',true);
1222 FND_MESSAGE.SET_TOKEN('NEW_ITEM',
1223 GMD_API_GRP.get_object_name_version
1224 ('OPERATION'
1225 ,get_object_rec.new_name)
1226 );
1227 ELSIF (get_object_rec.replace_type = 'OWNER_ID') THEN
1228 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OWNER',true);
1229 FND_MESSAGE.SET_TOKEN('NEW_ITEM',
1230 get_owner_name(get_object_rec.new_name) );
1231 ELSE
1232 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
1233 FND_MESSAGE.SET_TOKEN('NEW_ITEM',get_object_rec.new_name);
1234 END IF;
1235
1236 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1237 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1238 END IF;
1239
1240 ELSIF (l_action_flag IN ('U')) THEN
1241 p_rout_update_table(1).p_col_to_update := get_object_rec.replace_type;
1242
1243 IF (l_debug = 'Y') THEN
1244 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : About update Routing '
1245 ||' Replace type = '
1246 ||get_object_rec.replace_type
1247 ||' new_name = '
1248 ||get_object_rec.new_name
1249 );
1250 END IF;
1251
1252 p_rout_update_table(1).p_value := get_object_rec.new_name;
1253
1254 IF (get_object_rec.replace_type = 'OPRN_ID') THEN
1255 -- Get the routingstep id
1256 SELECT RoutingStep_id
1257 INTO l_routingStep_id
1258 FROM fm_rout_dtl
1259 WHERE routing_id = get_object_rec.object_id
1260 AND oprn_id = get_object_rec.old_name;
1261
1262 IF (l_debug = 'Y') THEN
1263 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
1264 ||' : About update Routing - Operation'
1265 ||' Routing Step id = '
1266 ||l_routingStep_id
1267 ||' Routing id = '
1268 ||get_object_rec.object_id
1269 ||' old_name = '
1270 ||get_object_rec.old_name
1271 );
1272 END IF;
1273
1274 GMD_ROUTING_STEPS_PUB.update_routing_steps
1275 (p_routingstep_id => l_routingStep_id
1276 ,p_routing_id => get_object_rec.object_id
1277 ,p_update_table => p_rout_update_table
1278 ,x_return_status => l_return_status
1279 ,x_message_count => l_mesg_count
1280 ,x_message_list => l_mesg_data
1281 );
1282
1283 IF (l_debug = 'Y') THEN
1284 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
1285 ||' : After calling Rt Step API '
1286 ||' l_return_status = '
1287 ||l_return_status
1288 );
1289 END IF;
1290 ELSE
1291 IF (l_debug = 'Y') THEN
1292 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
1293 ||' : About to call Update Routing API ');
1294 END IF;
1295
1296 -- Call the routing API
1297 GMD_ROUTINGS_PUB.update_routing
1298 ( p_routing_id => get_object_rec.object_id
1299 , p_update_table => p_rout_update_table
1300 , x_message_count => l_mesg_count
1301 , x_message_list => l_mesg_data
1302 , x_return_status => l_return_status
1303 );
1304 END IF;
1305 IF (l_return_status <> 'S') THEN
1306 RAISE No_Update_Exception;
1307 END IF;
1308 END IF;-- when action_flag is either 'U' or 'I'
1309
1310 ELSIF (get_object_rec.object_type = 'OPERATION') THEN
1311 -- Get the action flag to decide on update or insert(version control)
1312 SELECT opr.owner_organization_id
1313 INTO l_orgn_id
1314 FROM gmd_operations_b opr
1315 WHERE opr.oprn_id = get_object_rec.object_id;
1316
1317 -- Get the Operation version control for the entity orgn.
1318 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => l_orgn_id,
1319 P_parm_name => 'GMD_OPERATION_VERSION_CONTROL',
1320 P_parm_value => l_vers_cntrl,
1321 x_return_status => l_return_stat);
1322
1323 IF (l_vers_cntrl IN ('Y','O')) AND
1324 (l_version_enabled = 'Y' ) AND (get_object_rec.Version_flag = 'Y') THEN
1325 -- Bug # 5005145 .Changed the Condition for new creating vew version
1326 IF (l_status_type IN ('300','400','600','700')) THEN
1327 l_action_flag := 'I';
1328 ELSIF ((l_status_type = 900) AND
1329 (get_object_rec.replace_type like '%END_DATE%')) THEN
1330 l_action_flag := 'I';
1331 ELSE
1332 l_action_flag := 'N';
1333 END IF;
1334 ELSE
1335 IF(l_status_type IN ('100','300','400','600','700') ) OR
1336 ((l_status_type = '900') AND
1337 (get_object_rec.replace_type like '%END_DATE%') )THEN
1338 l_action_flag := 'U';
1339 ELSE
1340 l_action_flag := 'N';
1341 END IF;
1342 END IF;
1343
1344 IF (l_debug = 'Y') THEN
1345 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
1346 ||' Version Flag = '
1347 ||get_object_rec.version_flag
1348 ||' Replace Column = '
1349 ||get_object_rec.replace_type
1350 ||' Status Type = '
1351 ||l_status_type
1352 ||' : Action Flag = '
1353 ||l_action_flag);
1354 END IF;
1355
1356 IF (l_action_flag = 'N') THEN
1357 FND_MESSAGE.SET_NAME('GMD', 'GMD_REPLACE_NOT_ALLOWED');
1358 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
1359 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
1360 FND_MESSAGE.SET_TOKEN('STATUS',get_status_meaning(l_status_type) );
1361 FND_MSG_PUB.ADD;
1362 RAISE NO_UPDATE_EXCEPTION;
1363 END IF;
1364
1365 IF (l_action_flag IN ('I')) THEN
1366 SELECT DECODE(get_object_rec.replace_type,'START_DATE'
1367 ,get_object_rec.new_name
1368 ,NULL),
1369 DECODE(get_object_rec.replace_type,'END_DATE'
1370 ,get_object_rec.new_name
1371 ,NULL),
1372 DECODE(get_object_rec.replace_type,'ACTIVITY'
1373 ,get_object_rec.old_name
1374 ,NULL),
1375 DECODE(get_object_rec.replace_type,'ACTIVITY'
1376 ,get_object_rec.new_name
1377 ,NULL),
1378 DECODE(get_object_rec.replace_type,'OPRN_CLASS'
1379 ,get_object_rec.new_name
1380 ,NULL),
1381 DECODE(get_object_rec.replace_type,'RESOURCES'
1382 ,get_object_rec.old_name
1383 ,NULL),
1384 DECODE(get_object_rec.replace_type,'RESOURCES'
1385 ,get_object_rec.new_name
1386 ,NULL),
1387 DECODE(get_object_rec.replace_type,'OWNER_ORGN_CODE'
1388 ,get_object_rec.new_name
1389 ,NULL)
1390 INTO l_start_date, l_end_date, l_old_actv, l_new_actv,
1391 l_oprn_class, l_old_resource, l_new_resource, l_organization_id
1392 FROM dual;
1393
1394 IF (l_debug = 'Y') THEN
1395 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
1396 ||' : About to call create_new_operation - End Date = '
1397 ||l_end_date);
1398 END IF;
1399
1400 -- Call the Operation Insert API
1401 GMD_SEARCH_REPLACE_VERS.create_new_operation
1402 ( p_oprn_id => get_object_rec.object_id
1403 , p_old_activity => l_old_actv
1404 , p_activity => l_new_actv
1405 , p_effective_start_date => l_start_date
1406 , p_effective_end_date => l_end_date
1407 , p_operation_class => l_oprn_class
1408 , p_inactive_ind => NULL
1409 , p_old_resource => l_old_resource
1410 , p_resource => l_new_resource
1411 , x_oprn_id => l_oprn_id
1412 );
1413
1414 IF l_oprn_id IS NULL THEN
1415 RAISE No_Update_Exception;
1416 ELSE
1417 SELECT oprn_vers
1418 INTO l_object_version
1419 FROM gmd_operations_b
1420 WHERE oprn_id = l_oprn_id;
1421
1422 -- Setup message to indicate that a new version
1423 -- has been created
1424 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_NEW_OBJECT_VERSION');
1425 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
1426 FND_MESSAGE.SET_TOKEN('VERSION',l_object_version);
1427 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',get_object_rec.object_name);
1428 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
1429 FND_MESSAGE.SET_TOKEN('NEW_ITEM',get_object_rec.new_name);
1430 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1431 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1432 END IF;
1433
1434 ELSIF (l_action_flag IN ('U')) THEN
1435 IF (get_object_rec.replace_type
1436 IN ('OPRN_CLASS','START_DATE','END_DATE')) THEN
1437 p_oprn_update_table(1).P_COL_TO_UPDATE := get_object_rec.replace_type;
1438 p_oprn_update_table(1).P_VALUE := get_object_rec.new_name;
1439
1440 IF (l_debug = 'Y') THEN
1441 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : Update of Operation '||
1442 ' Replace type = '||p_oprn_update_table(1).P_COL_TO_UPDATE||
1443 ' and value = '||p_oprn_update_table(1).P_VALUE);
1444 END IF;
1445
1446 -- Call the operation API
1447 GMD_OPERATIONS_PUB.update_operation
1448 ( p_oprn_id => get_object_rec.object_id
1449 , p_update_table => p_oprn_update_table
1450 , x_message_count => l_mesg_count
1451 , x_message_list => l_mesg_data
1452 , x_return_status => l_return_status
1453 );
1454 ELSIF (get_object_rec.replace_type = 'ACTIVITY') THEN
1455 -- Get the oprn_line_id based on the old oprn_id (object_id)
1456 -- and old activity (old_name).
1457 SELECT oprn_line_id
1458 INTO l_oprn_line_id
1459 FROM gmd_operation_activities
1460 WHERE oprn_id = get_object_rec.object_id
1461 AND activity = get_object_rec.old_name;
1462
1463 p_oprn_activity_table(1).P_COL_TO_UPDATE := get_object_rec.replace_type;
1464 p_oprn_activity_table(1).P_VALUE := get_object_rec.new_name;
1465
1466 GMD_OPERATION_ACTIVITIES_PUB.update_operation_activity
1467 ( p_oprn_line_id => l_oprn_line_id
1468 , p_update_table => p_oprn_activity_table
1469 , X_RETURN_STATUS => l_return_status --Return Status
1470 , X_MESSAGE_COUNT => l_mesg_count --Message Count
1471 , X_MESSAGE_LIST => l_mesg_data --Message Data
1472 );
1473 ELSIF (get_object_rec.replace_type = 'RESOURCES') THEN
1474 -- Get the oprn_line_id based on the old oprn_id (object_id)
1475 -- and old resource (old_name).
1476 IF (l_debug = 'Y') THEN
1477 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : About replace a resource '
1478 ||' in a operation. The oprn_id = '||get_object_rec.object_id||' and'
1479 ||' old resource = '||get_object_rec.old_name);
1480 END IF;
1481
1482 SELECT r.oprn_line_id
1483 INTO l_oprn_line_id
1484 FROM gmd_operation_resources r, gmd_operation_activities a
1485 WHERE a.oprn_id = get_object_rec.object_id
1486 AND r.resources = get_object_rec.old_name
1487 AND a.oprn_line_id = r.oprn_line_id;
1488
1489 IF (l_debug = 'Y') THEN
1490 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : The oprn_line_id = '
1491 ||l_oprn_line_id||' and'||' new resource = '||get_object_rec.new_name);
1492 END IF;
1493
1494 p_oprn_resources_table(1).P_COL_TO_UPDATE := get_object_rec.replace_type;
1495 p_oprn_resources_table(1).P_VALUE := get_object_rec.new_name;
1496
1497 GMD_OPERATION_RESOURCES_PUB.update_operation_resources
1498 ( p_oprn_line_id => l_oprn_line_id
1499 , p_resources => get_object_rec.old_name
1500 , p_update_table => p_oprn_resources_table
1501 , X_RETURN_STATUS => l_return_status --Return Status
1502 , X_MESSAGE_COUNT => l_mesg_count --Message Count
1503 , X_MESSAGE_LIST => l_mesg_data --Message Data
1504 );
1505 END IF;
1506
1507 IF (l_return_status <> 'S') THEN
1508 RAISE No_Update_Exception;
1509 END IF;
1510
1511 END IF;
1512 ELSIF (get_object_rec.object_type = 'VALIDITY') THEN
1513 IF (l_debug = 'Y') THEN
1514 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : The Vr status = '||l_status_type
1515 ||' and replace type = '||get_object_rec.replace_type);
1516 END IF;
1517 IF (get_object_rec.replace_type <> 'STATUS') THEN
1518 -- If status code is On-Hold or obsolete no update is allowed
1519 IF (l_status_type IN ('200','500','800','1000')) THEN
1520 l_action_flag := 'N';
1521 -- If status code is Frozen, no update except End Date is allowed
1522 ELSIF (l_status_type IN ('900')) AND
1523 (get_object_rec.replace_type <> 'END_DATE') THEN
1524 l_action_flag := 'N';
1525 END IF;
1526 END IF;
1527 IF (l_debug = 'Y') THEN
1528 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : The Vr action_flag = '
1529 ||l_action_flag);
1530 END IF;
1531
1532 IF (l_action_flag = 'U') THEN
1533 OPEN Cur_get_validity(get_object_rec.object_id);
1534 FETCH Cur_get_validity INTO l_obj_id;
1535 CLOSE Cur_get_validity;
1536 IF NOT GMD_API_GRP.Check_orgn_access
1537 (Entity => 'RECIPE'
1538 ,Entity_id => l_obj_id) THEN
1539 RAISE NO_UPDATE_EXCEPTION;
1540 END IF;
1541
1542 -- Currently for Validity Rules we do not provide version
1543 -- control. So there would not be any creation of Validity Rules
1544 If (l_debug = 'Y') THEN
1545 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : The update clumn = '||
1546 get_object_rec.replace_type||
1547 ' and update value = '||
1548 get_object_rec.new_name);
1549 END IF;
1550
1551 -- NPD Conv.
1552 IF get_object_rec.replace_type = 'ORGN_CODE' THEN
1553 p_validity_rules_table(1).p_col_to_update := 'ORGANIZATION_ID';
1554 p_validity_rules_table(1).p_value := get_object_rec.new_name;
1555
1556 -- Get formula associated with recipe for the VR
1557 OPEN get_recp_formula_id (get_object_rec.object_id);
1558 FETCH get_recp_formula_id INTO l_form_id;
1559 CLOSE get_recp_formula_id;
1560
1561 -- Check if formula items belong to the new organization
1562 GMD_API_GRP.check_item_exists
1563 (p_formula_id => l_form_id
1564 ,p_organization_id => get_object_rec.new_name
1565 ,x_return_status => l_status );
1566 IF l_status <> FND_API.g_ret_sts_success THEN
1567 -- Bug 5531791 Removed FND_MSG_PUB.GET and FND_MSG_PUB.ADD as the message
1568 -- is already added by GMD_API_GRP.check_item_exists
1569 RAISE NO_UPDATE_EXCEPTION;
1570 END IF;
1571 ELSE
1572 p_validity_rules_table(1).p_col_to_update := get_object_rec.replace_type;
1573 p_validity_rules_table(1).p_value := get_object_rec.new_name;
1574 END IF;
1575
1576
1577 GMD_VALIDITY_RULES_PVT.update_validity_rules
1578 ( p_validity_rule_id => get_object_rec.object_id
1579 , p_update_table => p_validity_rules_table
1580 , x_message_count => l_mesg_count
1581 , x_message_list => l_mesg_data
1582 , x_return_status => l_return_status
1583 );
1584 -- check the return status from vr aPI
1585 IF (l_return_status <> 'S') THEN
1586 RAISE No_Update_Exception;
1587 END IF;
1588 ELSIF (l_action_flag = 'N') THEN
1589 FND_MESSAGE.SET_NAME('GMD', 'GMD_REPLACE_NOT_ALLOWED');
1590 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
1591 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
1592 FND_MESSAGE.SET_TOKEN('STATUS',get_status_meaning(l_status_type) );
1593 FND_MSG_PUB.ADD;
1594 RAISE NO_UPDATE_EXCEPTION;
1595 END IF; -- for action flags for VRs
1596 END IF; -- After working thro' every object type condition
1597 END IF; -- replace type is checked for 'STATUS'
1598
1599 -- Provide a log entry after any entity instance is successfully replaced
1600 -- Bug# 5234792 Kapil M
1601 -- Log for Update of existing version only
1602 IF ((l_return_status = 'S')AND (l_action_flag = 'U')) THEN
1603 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT');
1604 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',get_object_rec.object_name);
1605
1606 IF (get_object_rec.replace_type = 'INGREDIENT') THEN
1607 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','LM_INGREDIENT',true);
1608 IF get_object_rec.new_version IS NULL THEN
1609 l_item_txt := get_item_no(get_object_rec.new_name);
1610 ELSE
1611 l_item_txt := get_item_no(get_object_rec.new_name) ||' , '|| fnd_message.GET_STRING('INV', 'REVISION') || ' : '||get_object_rec.new_version;
1612 END IF;
1613 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE', l_item_txt );
1614 ELSIF (get_object_rec.replace_type = 'STATUS') THEN
1615 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_STATUS',true);
1616 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1617 get_status_meaning(
1618 GMD_API_GRP.get_object_status_type
1619 (get_object_rec.object_type
1620 , get_object_rec.object_id)
1621 ) );
1622 ELSIF (get_object_rec.replace_type = 'ROUTING_ID') THEN
1623 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_ROUTING',true);
1624 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1625 GMD_API_GRP.get_object_name_version
1626 ('ROUTING',get_object_rec.new_name)
1627 );
1628 ELSIF (get_object_rec.replace_type = 'OPRN_ID') THEN
1629 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OPERATION',true);
1630 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1631 GMD_API_GRP.get_object_name_version
1632 ('OPERATION',get_object_rec.new_name)
1633 );
1634 ELSIF (get_object_rec.replace_type = 'FORMULA_ID') THEN
1635 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_FORMULA',true);
1636 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1637 GMD_API_GRP.get_object_name_version
1638 ('FORMULA',get_object_rec.new_name)
1639 );
1640 ELSIF (get_object_rec.replace_type = 'OWNER_ID') THEN
1641 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OWNER',true);
1642 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1643 get_owner_name(get_object_rec.new_name) );
1644 ELSIF (get_object_rec.replace_type = 'RECIPE_USE') THEN
1645 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1646 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1647 get_recipe_use(get_object_rec.new_name) );
1648 -- Bug# 5234792 Kapil M
1649 -- To get the recipe type value
1650 ELSIF (get_object_rec.replace_type = 'RECIPE_TYPE') THEN
1651 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1652 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1653 get_recipe_type(get_object_rec.new_name) );
1654 ELSIF ((get_object_rec.replace_type like '%START_DATE%') OR
1655 (get_object_rec.replace_type like '%END_DATE%') ) THEN
1656 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1657 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1658 FND_DATE.canonical_to_date(get_object_rec.new_name) );
1659 -- 5532058 If replace_type is 'OWNER_ORGN_CODE', display orgn code
1660 ELSIF (get_object_rec.replace_type = 'OWNER_ORGN_CODE') THEN
1661 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1662 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1663 get_orgn_code(get_object_rec.new_name) );
1664
1665 ELSE
1666 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1667 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',get_object_rec.new_name);
1668 END IF;
1669
1670 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',pObject_type);
1671 FND_MESSAGE.SET_TOKEN('OBJECT_VERS',get_object_rec.object_vers);
1672 -- to be removed
1673 -- fnd_msg_pub.add;
1674 END IF;
1675
1676 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1677 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1678
1679 -- Set the row counter
1680 l_rowcount := l_rowcount + 1;
1681 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : Within loop for Row # '
1682 ||l_rowcount);
1683 EXCEPTION
1684 WHEN NO_UPDATE_EXCEPTION THEN
1685 -- Bug# 5008299 Kapil M
1686 -- Passing fnd_msg_pub.Count_Msg to get the top most message
1687 fnd_msg_pub.get
1688 (p_msg_index => fnd_msg_pub.Count_Msg
1689 ,p_data => l_error_text
1690 ,p_encoded => 'F'
1691 ,p_msg_index_out => l_dummy_cnt
1692 );
1693
1694 ret_code := 2;
1695 err_buf := NULL;
1696 l_retval := fnd_concurrent.set_completion_status('WARNING',l_error_text);
1697
1698 IF (l_debug = 'Y') THEN
1699 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
1700 ||' : In the No_update_exception section '
1701 ||' Error text is '||l_error_text);
1702 END IF;
1703 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
1704
1705 IF (get_object_rec.replace_type = 'INGREDIENT') THEN
1706 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','LM_INGREDIENT',true);
1707 IF get_object_rec.new_version IS NULL THEN
1708 l_item_txt := get_item_no(get_object_rec.new_name);
1709 ELSE
1710 l_item_txt := get_item_no(get_object_rec.new_name) ||' , '|| fnd_message.GET_STRING('INV', 'REVISION') || ' : '||get_object_rec.new_version;
1711 END IF;
1712 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE', l_item_txt );
1713 ELSIF (get_object_rec.replace_type = 'STATUS') THEN
1714 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_STATUS',true);
1715 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1716 get_status_meaning(get_object_rec.new_name) );
1717 ELSIF (get_object_rec.replace_type = 'ROUTING_ID') THEN
1718 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_ROUTING',true);
1719 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1720 GMD_API_GRP.get_object_name_version
1721 ('ROUTING'
1722 ,get_object_rec.new_name)
1723 );
1724 ELSIF (get_object_rec.replace_type = 'OPRN_ID') THEN
1725 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OPERATION',true);
1726 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1727 GMD_API_GRP.get_object_name_version
1728 ('OPERATION'
1729 ,get_object_rec.new_name)
1730 );
1731 ELSIF (get_object_rec.replace_type = 'FORMULA_ID') THEN
1732 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_FORMULA',true);
1733 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1734 GMD_API_GRP.get_object_name_version
1735 ('FORMULA'
1736 ,get_object_rec.new_name)
1737 );
1738 ELSIF (get_object_rec.replace_type = 'OWNER_ID') THEN
1739 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OWNER',true);
1740 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1741 get_owner_name(get_object_rec.new_name) );
1742 ELSIF (get_object_rec.replace_type = 'RECIPE_USE') THEN
1743 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1744 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1745 get_recipe_use(get_object_rec.new_name) );
1746 ELSIF ((get_object_rec.replace_type like '%START_DATE%') OR
1747 (get_object_rec.replace_type like '%END_DATE%') ) THEN
1748 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1749 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1750 FND_DATE.canonical_to_date(get_object_rec.new_name) );
1751 ELSIF (get_object_rec.replace_type IN ('ORGN_CODE', 'OWNER_ORGN_CODE')) THEN
1752 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1753 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1754 get_orgn_code(get_object_rec.new_name) );
1755
1756 ELSE
1757 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
1758 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',get_object_rec.new_name);
1759 END IF;
1760
1761 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',get_object_rec.object_name);
1762 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
1763 FND_MESSAGE.SET_TOKEN('OBJECT_VERS',get_object_rec.object_vers);
1764 FND_MESSAGE.SET_TOKEN('ERRMSG',l_error_text);
1765 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1766 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1767 WHEN OTHERS THEN
1768
1769 ret_code := 2;
1770 err_buf := NULL;
1771 l_retval := fnd_concurrent.set_completion_status('WARNING',sqlerrm);
1772
1773 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1774 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
1775
1776 IF (get_object_rec.replace_type = 'INGREDIENT') THEN
1777 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','LM_INGREDIENT',true);
1778 IF get_object_rec.new_version IS NULL THEN
1779 l_item_txt := get_item_no(get_object_rec.new_name);
1780 ELSE
1781 l_item_txt := get_item_no(get_object_rec.new_name) ||' , '|| fnd_message.GET_STRING('INV', 'REVISION') || ' : '||get_object_rec.new_version;
1782 END IF;
1783 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE', l_item_txt );
1784 ELSIF (get_object_rec.replace_type = 'STATUS') THEN
1785 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_STATUS',true);
1786 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1787 get_status_meaning(get_object_rec.new_name) );
1788 ELSIF (get_object_rec.replace_type = 'ROUTING_ID') THEN
1789 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_ROUTING',true);
1790 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1791 GMD_API_GRP.get_object_name_version
1792 ('ROUTING'
1793 ,get_object_rec.new_name)
1794 );
1795 ELSIF (get_object_rec.replace_type = 'OPRN_ID') THEN
1796 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OPERATION',true);
1797 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1798 GMD_API_GRP.get_object_name_version
1799 ('OPERATION'
1800 ,get_object_rec.new_name)
1801 );
1802 ELSIF (get_object_rec.replace_type = 'FORMULA_ID') THEN
1803 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_FORMULA',true);
1804 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1805 GMD_API_GRP.get_object_name_version
1806 ('FORMULA'
1807 ,get_object_rec.new_name)
1808 );
1809 ELSIF (get_object_rec.replace_type = 'OWNER_ID') THEN
1810 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','GMD_OWNER',true);
1811 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1812 get_owner_name(get_object_rec.new_name) );
1813 ELSIF (get_object_rec.replace_type = 'RECIPE_USE') THEN
1814 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1815 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1816 get_recipe_use(get_object_rec.new_name) );
1817 ELSIF ((get_object_rec.replace_type like '%START_DATE%') OR
1818 (get_object_rec.replace_type like '%END_DATE%') ) THEN
1819 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1820 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1821 FND_DATE.canonical_to_date(get_object_rec.new_name) );
1822 ELSIF (get_object_rec.replace_type IN ('ORGN_CODE', 'OWNER_ORGN_CODE')) THEN
1823 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1824 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1825 get_orgn_code(get_object_rec.new_name) );
1826 ELSE
1827 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',get_object_rec.replace_type);
1828 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',get_object_rec.new_name);
1829 END IF;
1830
1831 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',get_object_rec.object_name);
1832 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',get_object_rec.object_type);
1833 FND_MESSAGE.SET_TOKEN('OBJECT_VERS',get_object_rec.object_vers);
1834 FND_MESSAGE.SET_TOKEN('ERRMSG',SQLERRM);
1835 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1836 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1837 END; -- End created to handle exception for each record
1838
1839
1840 END LOOP; -- For all rows that needs to be replaced
1841
1842 -- If MSNR was successful until here then
1843 -- Delete rows specific to this concurrent id
1844 IF (pConcurrent_id IS NOT NULL) THEN
1845 DELETE
1846 FROM gmd_msnr_results
1847 WHERE concurrent_id = pconcurrent_id;
1848 COMMIT;
1849 END IF;
1850
1851 -- There were no row selected for replace raise an error
1852 IF (l_rowcount = 0) THEN
1853 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_NO_ROW_FOUND');
1854 Raise No_Replace_Exception;
1855 END IF;
1856
1857 IF (l_debug = 'Y') THEN
1858 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||'Completed '||l_api_name ||' at '
1859 ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1860 END IF;
1861
1862 EXCEPTION
1863 -- this exception occurs when no rows were selected for update.
1864 WHEN NO_REPLACE_EXCEPTION THEN
1865 fnd_msg_pub.get
1866 (p_msg_index => 1
1867 ,p_data => l_error_text
1868 ,p_encoded => 'F'
1869 ,p_msg_index_out => l_dummy_cnt
1870 );
1871
1872 ret_code := 2;
1873 err_buf := NULL;
1874 l_retval := fnd_concurrent.set_completion_status('WARNING',l_error_text);
1875
1876 IF (l_debug = 'Y') THEN
1877 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
1878 ||' : In the No_replace_exception section '
1879 ||' Error text is '||l_error_text);
1880 END IF;
1881 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1882 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1883 -- outer excepption handles all error that occur prior to or after
1884 -- Mass updates (or within LOOP above)
1885
1886 WHEN OTHERS THEN
1887
1888 ret_code := 2;
1889 err_buf := NULL;
1890 l_retval := fnd_concurrent.set_completion_status('WARNING',sqlerrm);
1891
1892 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1893 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
1894 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',pReplace_type);
1895 IF (pReplace_type = 'INGREDIENT') THEN
1896 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE', get_item_no(pNew_name) );
1897 ELSIF (preplace_type = 'STATUS') THEN
1898 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',get_status_meaning(pnew_name) );
1899 ELSIF (pReplace_type IN ('ORGN_CODE', 'OWNER_ORGN_CODE')) THEN
1900 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',
1901 get_orgn_code(pnew_name) );
1902 ELSE
1903 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',pnew_name);
1904 END IF;
1905 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',pObject_type);
1906 FND_MESSAGE.SET_TOKEN('ERRMSG',SQLERRM);
1907 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1908 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1909
1910 END Mass_Replace_Operation;
1911
1912
1913 /*###############################################################
1914 # NAME
1915 # Validate_All_Replace_Rows
1916 # SYNOPSIS
1917 # Validate_All_Replace_Rows
1918 # DESCRIPTION
1919 # Validates each row that has been choosen for replace.
1920 # Called by forms prior to submiting request for Mass replace
1921 ###############################################################*/
1922 PROCEDURE Validate_All_Replace_Rows(pObject_type IN VARCHAR2,
1923 pReplace_type IN VARCHAR2,
1924 pOld_Name IN VARCHAR2,
1925 pRows_Processed OUT NOCOPY NUMBER,
1926 x_return_status OUT NOCOPY VARCHAR2) IS
1927 -- all related to dynamic sql
1928 l_api_name VARCHAR2(100) := 'Validate_All_Replace_Rows';
1929
1930 l_replace_type VARCHAR2(100);
1931 l_cursor_id int;
1932 l_Row_count NUMBER;
1933 l_rows_processed NUMBER;
1934 l_dynamic_select VARCHAR2(2000);
1935 l_dsql_debug VARCHAR2(2000);
1936 l_table_name VARCHAR2(100);
1937 l_primary_key VARCHAR2(100);
1938
1939 CURSOR get_select_id IS
1940 Select object_id
1941 From Gmd_MSNR_Results
1942 Where concurrent_id IS NULL
1943 And object_select_ind = 1;
1944
1945 VALIDATION_FAILED_EXCEPTION EXCEPTION;
1946 BEGIN
1947
1948 SAVEPOINT validate_all_rows;
1949 x_return_status := 'S';
1950
1951 SELECT count(*)
1952 INTO l_row_count
1953 FROM Gmd_MSNR_Results
1954 WHERE object_select_ind = 1
1955 AND Concurrent_id IS NULL;
1956
1957 SELECT DECODE(pObject_type,
1958 'FORMULA','FM_FORM_MST_B',
1959 'RECIPE','GMD_RECIPES_B',
1960 'OPERATION','GMD_OPERATIONS_B',
1961 'ROUTING','GMD_ROUTINGS_B',
1962 'VALIDITY','GMD_RECIPE_VALIDITY_RULES'),
1963 DECODE(pObject_type,
1964 'FORMULA','FORMULA_ID',
1965 'RECIPE','RECIPE_ID',
1966 'OPERATION','OPRN_ID',
1967 'ROUTING','ROUTING_ID',
1968 'VALIDITY','RECIPE_VALIDITY_RULE_ID')
1969 INTO l_table_name, l_primary_key
1970 FROM sys.dual;
1971
1972 l_replace_type := pReplace_type;
1973
1974 IF (l_debug = 'Y') THEN
1975 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : l_replace_type = '||l_replace_type||
1976 ' and l_table_name = '||l_table_name);
1977 END IF;
1978
1979 IF (pObject_type = 'FORMULA') THEN
1980 IF (pReplace_type = 'STATUS') THEN
1981 l_replace_type := 'FORMULA_STATUS';
1982 ELSIF (pReplace_type = 'INGREDIENT') THEN
1983 l_replace_type := 'FORMULA_ID';
1984 END IF;
1985
1986 ELSIF (pObject_type = 'RECIPE') THEN
1987 IF (pReplace_type = 'STATUS') THEN
1988 l_replace_type := 'RECIPE_STATUS';
1989 ELSIF (pReplace_type = 'OWNER_ORGN_CODE') THEN
1990 l_replace_type := 'OWNER_ORGANIZATION_ID';
1991 END IF;
1992
1993 ELSIF (pObject_type = 'ROUTING') THEN
1994 IF (pReplace_type = 'STATUS') THEN
1995 l_replace_type := 'ROUTING_STATUS';
1996 ELSIF (pReplace_type = 'START_DATE') THEN
1997 l_replace_type := 'EFFECTIVE_START_DATE';
1998 ELSIF (pReplace_type = 'END_DATE') THEN
1999 l_replace_type := 'EFFECTIVE_END_DATE';
2000 ELSIF (pReplace_type = 'OPRN_ID') THEN
2001 l_replace_type := 'ROUTING_ID';
2002 END IF;
2003
2004 ELSIF (pObject_type = 'OPERATION') THEN
2005 IF (pReplace_type = 'STATUS') THEN
2006 l_replace_type := 'OPERATION_STATUS';
2007 ELSIF (pReplace_type = 'ACTIVITY') THEN
2008 l_replace_type := 'OPRN_ID';
2009 ELSIF (pReplace_type = 'RESOURCES') THEN
2010 l_replace_type := 'OPRN_ID';
2011 ELSIF (pReplace_type = 'START_DATE') THEN
2012 l_replace_type := 'EFFECTIVE_START_DATE';
2013 ELSIF (pReplace_type = 'END_DATE') THEN
2014 l_replace_type := 'EFFECTIVE_END_DATE';
2015 END IF;
2016
2017 ELSIF (pObject_type = 'VALIDITY') THEN
2018 IF (pReplace_type = 'STATUS') THEN
2019 l_replace_type := 'VALIDITY_RULE_STATUS';
2020 ELSIF (pReplace_type = 'ORGN_CODE') THEN
2021 l_replace_type := 'ORGANIZATION_ID';
2022 END IF;
2023
2024 END IF;
2025
2026 fnd_dsql.init;
2027 fnd_dsql.add_text( ' Update Gmd_MSNR_Results '||
2028 ' Set Object_select_ind = 0 '||
2029 ' Where concurrent_id IS NULL '||
2030 ' And Object_select_ind = 1 '||
2031 ' And object_id NOT IN ( Select object_id '||
2032 ' From Gmd_MSNR_Results, '||l_table_name||
2033 ' Where '||l_primary_key||' = object_id'||
2034 ' And concurrent_id IS NULL '||
2035 ' And Object_select_ind = 1' );
2036 IF (pOld_Name = ' ' or pOld_name IS NULL) THEN
2037 IF l_Replace_type IN ('END_DATE'
2038 ,'EFFECTIVE_END_DATE'
2039 ,'PLANNED_PROCESS_LOSS'
2040 ,'ORGANIZATION_ID'
2041 ,'OWNER_ORGANIZATION_ID'
2042 ,'OPRN_CLASS'
2043 ,'ROUTING_CLASS'
2044 ,'ROUTING_ID'
2045 ,'FORMULA_CLASS') THEN
2046 fnd_dsql.add_text( ' And '||l_replace_type||' IS NULL )');
2047 ELSE
2048 -- pOld_Name cannot be null
2049 FND_MESSAGE.SET_NAME('GMD','GMD_NO_ASSIGN_VALUE_EXCEPTION');
2050 FND_MSG_PUB.ADD;
2051 RAISE VALIDATION_FAILED_EXCEPTION;
2052 END IF;
2053 ELSE
2054 fnd_dsql.add_text( ' And '||l_replace_type||' IN (');
2055
2056 IF (pReplace_type = 'INGREDIENT') THEN
2057 fnd_dsql.add_text(' SELECT FORMULA_ID FROM FM_MATL_DTL
2058 WHERE LINE_TYPE = -1
2059 AND INVENTORY_ITEM_ID = ');
2060 fnd_dsql.add_bind(pOld_name);
2061 fnd_dsql.add_text(' ))');
2062 ELSIF (pReplace_type = 'ACTIVITY') THEN
2063 fnd_dsql.add_text(' SELECT OPRN_ID FROM GMD_OPERATION_ACTIVITIES
2064 WHERE ACTIVITY = ');
2065 fnd_dsql.add_bind(pOld_name);
2066 fnd_dsql.add_text(' ))');
2067 ELSIF (pReplace_type = 'RESOURCES') THEN
2068 fnd_dsql.add_text(' SELECT OPRN_ID
2069 FROM GMD_OPERATION_ACTIVITIES a, GMD_OPERATION_RESOURCES r
2070 WHERE a.OPRN_LINE_ID = r.OPRN_LINE_ID
2071 AND r.RESOURCES = ');
2072 fnd_dsql.add_bind(pOld_name);
2073 fnd_dsql.add_text(' ))');
2074 ELSIF ((pObject_type = 'ROUTING') AND (pReplace_type = 'OPRN_ID'))THEN
2075 fnd_dsql.add_text(' SELECT ROUTING_ID
2076 FROM FM_ROUT_DTL
2077 WHERE OPRN_ID = ');
2078 fnd_dsql.add_bind(pOld_name);
2079 fnd_dsql.add_text(' ))');
2080 ELSIF ((pObject_type = 'RECIPE') AND (pReplace_type = 'OWNER_ORGANIZATION_ID'))THEN
2081 fnd_dsql.add_text(' SELECT RECIPE_ID
2082 FROM GMD_RECIPES_B
2083 WHERE OWNER_ORGANIZATION_ID = ');
2084 fnd_dsql.add_bind(pOld_name);
2085 fnd_dsql.add_text(' ))');
2086 ELSIF ((pObject_type = 'VALIDITY') AND (pReplace_type = 'ORGANIZATION_ID'))THEN
2087 fnd_dsql.add_text(' SELECT recipe_validity_rule_id
2088 FROM GMD_RECIPE_VALIDITY_RULES
2089 WHERE ORGANIZATION_ID = ');
2090 fnd_dsql.add_bind(pOld_name);
2091 fnd_dsql.add_text(' ))');
2092 ELSIF (pReplace_type IN ('START_DATE','END_DATE')) THEN
2093 fnd_dsql.add_bind(fnd_date.displaydt_to_date(pOld_name));
2094 fnd_dsql.add_text(' ))');
2095 ELSE
2096 fnd_dsql.add_bind(pOld_name);
2097 fnd_dsql.add_text(' ))');
2098 END IF;
2099
2100 END IF;
2101
2102 l_cursor_id := dbms_sql.open_cursor;
2103 fnd_dsql.set_cursor(l_cursor_id);
2104 l_dynamic_select := fnd_dsql.get_text(FALSE);
2105
2106 l_dsql_debug := fnd_dsql.get_text(TRUE);
2107 IF (l_debug = 'Y') THEN
2108 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : l_dsql_debug = '||l_dsql_debug);
2109 END IF;
2110
2111 dbms_sql.parse(l_cursor_id, l_dynamic_select, dbms_sql.native);
2112 fnd_dsql.do_binds;
2113
2114 pRows_Processed := dbms_sql.execute(l_cursor_id);
2115
2116 IF (l_debug = 'Y') THEN
2117 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : Rows fetched = '||pRows_Processed||
2118 ' and Original select cnt = '||l_row_count);
2119 END IF;
2120
2121 dbms_sql.close_cursor(l_cursor_id); -- close cursor
2122
2123 IF (l_debug = 'Y') THEN
2124 gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : pRows_Processed = '||pRows_Processed);
2125 END IF;
2126
2127 -- If all rows processed it actually means that none of the
2128 -- rows selected met the initial criteria, so we rollback all changes
2129 IF (l_row_count = pRows_Processed) THEN
2130 ROLLBACK to SAVEPOINT validate_all_rows;
2131 END IF;
2132
2133 EXCEPTION
2134 WHEN VALIDATION_FAILED_EXCEPTION THEN
2135 x_return_status := 'E';
2136 WHEN OTHERS THEN
2137 ROLLBACK to SAVEPOINT validate_all_rows;
2138 IF (l_debug = 'Y') THEN
2139 gmd_debug.put_line(g_pkg_name||'.'||l_api_name
2140 ||' : When Others for Validate_all_rep_rows, Error '||
2141 sqlerrm);
2142 END IF;
2143 x_return_status := 'U';
2144 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
2145
2146 END Validate_All_Replace_rows;
2147
2148 END;