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