[Home] [Help]
PACKAGE BODY: APPS.GMD_VERSION_CONTROL
Source
1 PACKAGE BODY gmd_version_control AS
2 /* $Header: GMDVCTLB.pls 120.15 2009/03/23 17:40:56 rnalla ship $ */
3
4 G_PKG_NAME VARCHAR2(32);
5
6 /*======================================================================
7 -- PROCEDURE :
8 -- populate_temp_text
9 --
10 -- DESCRIPTION:
11 -- This PL/SQL procedure is responsible for versioning of Edit Text
12 -- data.
13 --
14 -- REQUIREMENTS
15 --
16 -- SYNOPSIS:
17 -- populate_temp_text(p_text_code ,flag)
18 --
19 -- HISTORY
20 -- 25-Nov-2003 Vipul Vaish BUG#3258592
21 -- Created populate_temp_text so that the edit text versioning
22 -- takes place properly.
23 -- 04-Dec-2003 Vipul Vaish BUG#3258592
24 -- Removed Commit from populate_temp_text procedure.
25 -- 02-Jan-2004 Vipul Vaish BUG#3258592
26 -- Modified code that is being executed when flag = 2.
27 -- Added code for additional two flag values 3 and 4.
28 --===================================================================== */
29 PROCEDURE populate_temp_text(p_text_code IN number,flag IN number) IS
30 x_row NUMBER := 0;
31 l_rowid VARCHAR2(100);
32 CURSOR Cur_edit_text(p_text_code number) IS
33 SELECT *
34 FROM FM_TEXT_TBL_TL
35 WHERE text_code = p_text_code;
36 l_insert varchar2(1);
37 BEGIN
38
39 IF flag = 1 THEN
40
41 FOR i IN 1..edit_text_tbl.count LOOP
42 IF edit_text_tbl(i).text_code = p_text_code THEN
43 RETURN;
44 END IF;
45 END LOOP;
46
47 X_row := edit_text_tbl.count;
48 FOR get_rec IN Cur_edit_text(p_text_code) LOOP
49 X_row := X_row + 1;
50 edit_text_tbl(X_row) := get_rec;
51 END LOOP;
52
53 ELSIF flag = 2 THEN
54 l_insert := 'F';
55 FOR i IN 1..edit_text_tbl.count LOOP
56 IF edit_text_tbl(i).text_code = p_text_code THEN
57 DELETE FROM FM_TEXT_TBL_TL
58 WHERE text_code = p_text_code;
59 l_insert := 'T';
60 EXIT;
61 END IF;
62 END LOOP;
63
64 IF l_insert = 'T' THEN
65 FOR i IN 1..edit_text_tbl.count LOOP
66 IF edit_text_tbl(i).text_code = p_text_code THEN
67 INSERT INTO FM_TEXT_TBL_TL(
68 TEXT_CODE,
69 LANG_CODE,
70 PARAGRAPH_CODE,
71 SUB_PARACODE,
72 LINE_NO,
73 TEXT,
74 LANGUAGE,
75 SOURCE_LANG,
76 LAST_UPDATED_BY,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 CREATION_DATE,
80 LAST_UPDATE_LOGIN )
81 VALUES (
82 edit_text_tbl(i).text_code,
83 edit_text_tbl(i).lang_code,
84 edit_text_tbl(i).paragraph_code,
85 edit_text_tbl(i).sub_paracode,
86 edit_text_tbl(i).line_no,
87 edit_text_tbl(i).text,
88 edit_text_tbl(i).language,
89 edit_text_tbl(i).source_lang,
90 edit_text_tbl(i).last_updated_by,
91 edit_text_tbl(i).created_by,
92 edit_text_tbl(i).last_update_date,
93 edit_text_tbl(i).creation_date,
94 edit_text_tbl(i).last_update_login);
95
96 edit_text_tbl(i).text_code := NULL;
97
98 END IF;
99 END LOOP;
100 l_insert := 'F';
101 END IF;
102
103 ELSIF flag = 3 THEN
104 FOR i IN 1..edit_text_tbl.count LOOP
105 IF edit_text_tbl(i).text_code IS NOT NULL THEN
106 edit_text_tbl(i).text_code := NULL;
107 END IF;
108 END LOOP;
109
110 ELSIF flag = 4 THEN
111 FOR i IN 1..edit_text_tbl.count LOOP
112 IF edit_text_tbl(i).text_code IS NOT NULL THEN
113 DELETE FROM FM_TEXT_TBL_TL
114 WHERE TEXT_CODE = edit_text_tbl(i).text_code;
115 END IF;
116 END LOOP;
117 FOR i IN 1..edit_text_tbl.count LOOP
118 IF edit_text_tbl(i).text_code IS NOT NULL THEN
119 INSERT INTO FM_TEXT_TBL_TL(
120 TEXT_CODE,
121 LANG_CODE,
122 PARAGRAPH_CODE,
123 SUB_PARACODE,
124 LINE_NO,
125 TEXT,
126 LANGUAGE,
127 SOURCE_LANG,
128 LAST_UPDATED_BY,
129 CREATED_BY,
130 LAST_UPDATE_DATE,
131 CREATION_DATE,
132 LAST_UPDATE_LOGIN )
133 VALUES (
134 edit_text_tbl(i).text_code,
135 edit_text_tbl(i).lang_code,
136 edit_text_tbl(i).paragraph_code,
137 edit_text_tbl(i).sub_paracode,
138 edit_text_tbl(i).line_no,
139 edit_text_tbl(i).text,
140 edit_text_tbl(i).language,
141 edit_text_tbl(i).source_lang,
142 edit_text_tbl(i).last_updated_by,
143 edit_text_tbl(i).created_by,
144 edit_text_tbl(i).last_update_date,
145 edit_text_tbl(i).creation_date,
146 edit_text_tbl(i).last_update_login);
147
148 edit_text_tbl(i).text_code := NULL;
149 END IF;
150 END LOOP;
151 COMMIT;
152 END IF;
153 END populate_temp_text;
154
155 /*======================================================================
156 -- PROCEDURE :
157 -- create_routing
158 --
159 -- DESCRIPTION:
160 -- This PL/SQL procedure is responsible for saving the
161 -- new routing while versioning.
162 --
163 -- REQUIREMENTS
164 --
165 -- SYNOPSIS:
166 -- create_routing(P_routing_id, X_routing_id);
167 --
168 -- HISTORY
169 -- 31-Jan-2003 Jeff Baird Bug #2673008 Added call to copy_text API.
170 -- 25-Nov-2003 Vipul Vaish BUG#3258592
171 -- Added call to procedure populate_temp_text after GMA_EDITTEXT_PKG.Copy_Text
172 -- function.
173 --
174 --===================================================================== */
175 PROCEDURE create_routing(p_routing_id IN NUMBER, x_routing_id OUT NOCOPY NUMBER) IS
176
177 X_routing_vers NUMBER;
178 X_row NUMBER := 0;
179 l_rowid VARCHAR2(32);
180 l_text_code NUMBER;
181 -- Bug #2673008 (JKB) Added l_text_code above.
182
183 CURSOR Cur_routing_id IS
184 SELECT gem5_routing_id_s.NEXTVAL
185 FROM FND_DUAL;
186 CURSOR Cur_get_hdr IS
187 SELECT *
188 FROM fm_rout_hdr
189 WHERE routing_id = p_routing_id;
190 X_hdr_rec Cur_get_hdr%ROWTYPE;
191 CURSOR Cur_rout_vers IS
192 SELECT MAX(routing_vers) + 1
193 FROM fm_rout_hdr
194 WHERE routing_no = X_hdr_rec.routing_no;
195
196 CURSOR Cur_get_dtl IS
197 SELECT *
198 FROM fm_rout_dtl
199 WHERE routing_id = p_routing_id;
200 TYPE detail_tab IS TABLE OF Cur_get_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
201 X_dtl_tbl detail_tab;
202
203 l_msg_count NUMBER;
204 l_msg_data VARCHAR2(2000);
205 l_return_status VARCHAR2(10);
206 l_gmo_enabled VARCHAR2(1);
207 l_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
208 l_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
209 l_target_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
210 l_target_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
211 BEGIN
212 OPEN Cur_get_hdr;
213 FETCH Cur_get_hdr INTO X_hdr_rec;
214 CLOSE Cur_get_hdr;
215
216 FOR get_rec IN Cur_get_dtl LOOP
217 X_row := X_row + 1;
218 X_dtl_tbl(X_row) := get_rec;
219 END LOOP;
220
221 /* Check if GMO is enabled */
222 l_gmo_enabled := gmo_setup_grp.is_gmo_enabled;
223 IF l_gmo_enabled = 'Y' THEN
224 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'ROUTING',
225 p_entity_id => p_routing_id,
226 x_name_array => l_source_name_array,
227 x_key_array => l_source_key_array,
228 x_return_status => l_return_status);
229 END IF;
230
231 ROLLBACK;
232
233 OPEN Cur_rout_vers;
234 FETCH Cur_rout_vers INTO X_routing_vers;
235 CLOSE Cur_rout_vers;
236
237 OPEN Cur_routing_id;
238 FETCH Cur_routing_id INTO x_routing_id;
239 CLOSE Cur_routing_id;
240 /* Insert header record */
241
242
243 IF X_hdr_rec.TEXT_CODE IS NOT NULL THEN
244 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_hdr_rec.TEXT_CODE,
245 'FM_TEXT_TBL_TL',
246 'FM_TEXT_TBL_TL');
247 populate_temp_text(X_hdr_rec.TEXT_CODE,2);--BUG#3258592
248 ELSE
249 l_text_code := NULL;
250 END IF;
251 -- Bug #2673008 (JKB) Added call to copy_text above.
252
253 GMD_ROUTINGS_PKG.INSERT_ROW(
254 X_ROWID => l_ROWID,
255 X_ROUTING_ID => X_ROUTING_ID,
256 X_OWNER_ORGANIZATION_ID => X_HDR_rec.OWNER_ORGANIZATION_ID,
257 X_ROUTING_NO => X_HDR_rec.ROUTING_NO,
258 X_ROUTING_VERS => X_ROUTING_VERS,
259 X_ROUTING_CLASS => X_HDR_rec.ROUTING_CLASS,
260 X_ENFORCE_STEP_DEPENDENCY => X_hdr_rec.enforce_step_dependency,
261 X_CONTIGUOUS_IND => X_hdr_rec.contiguous_ind,
262 X_ROUTING_QTY => X_HDR_rec.ROUTING_QTY,
263 X_ROUTING_UOM => X_HDR_rec.ROUTING_UOM,
264 X_DELETE_MARK => 0,
265 X_TEXT_CODE => l_text_code,
266 X_INACTIVE_IND => 0,
267 X_IN_USE => 0,
268 X_ATTRIBUTE1 => X_HDR_rec.ATTRIBUTE1,
269 X_ATTRIBUTE2 => X_HDR_rec.ATTRIBUTE2,
270 X_ATTRIBUTE3 => X_HDR_rec.ATTRIBUTE3,
271 X_ATTRIBUTE4 => X_HDR_rec.ATTRIBUTE4,
272 X_ATTRIBUTE5 => X_HDR_rec.ATTRIBUTE5,
273 X_ATTRIBUTE6 => X_HDR_rec.ATTRIBUTE6,
274 X_ATTRIBUTE7 => X_HDR_rec.ATTRIBUTE7,
275 X_ATTRIBUTE8 => X_HDR_rec.ATTRIBUTE8,
276 X_ATTRIBUTE9 => X_HDR_rec.ATTRIBUTE9,
277 X_ATTRIBUTE10 => X_HDR_rec.ATTRIBUTE10,
278 X_ATTRIBUTE11 => X_HDR_rec.ATTRIBUTE11,
279 X_ATTRIBUTE12 => X_HDR_rec.ATTRIBUTE12,
280 X_ATTRIBUTE13 => X_HDR_rec.ATTRIBUTE13,
281 X_ATTRIBUTE14 => X_HDR_rec.ATTRIBUTE14,
282 X_ATTRIBUTE15 => X_HDR_rec.ATTRIBUTE15,
283 X_ATTRIBUTE16 => X_HDR_rec.ATTRIBUTE16,
284 X_ATTRIBUTE17 => X_HDR_rec.ATTRIBUTE17,
285 X_ATTRIBUTE18 => X_HDR_rec.ATTRIBUTE18,
286 X_ATTRIBUTE19 => X_HDR_rec.ATTRIBUTE19,
287 X_ATTRIBUTE20 => X_HDR_rec.ATTRIBUTE20,
288 X_ATTRIBUTE21 => X_HDR_rec.ATTRIBUTE21,
289 X_ATTRIBUTE22 => X_HDR_rec.ATTRIBUTE22,
290 X_ATTRIBUTE23 => X_HDR_rec.ATTRIBUTE23,
291 X_ATTRIBUTE24 => X_HDR_rec.ATTRIBUTE24,
292 X_ATTRIBUTE25 => X_HDR_rec.ATTRIBUTE25,
293 X_ATTRIBUTE26 => X_HDR_rec.ATTRIBUTE26,
294 X_ATTRIBUTE27 => X_HDR_rec.ATTRIBUTE27,
295 X_ATTRIBUTE28 => X_HDR_rec.ATTRIBUTE28,
296 X_ATTRIBUTE29 => X_HDR_rec.ATTRIBUTE29,
297 X_ATTRIBUTE30 => X_HDR_rec.ATTRIBUTE30,
298 X_ATTRIBUTE_CATEGORY => X_HDR_rec.ATTRIBUTE_CATEGORY,
299 X_EFFECTIVE_START_DATE => X_HDR_rec.EFFECTIVE_START_DATE,
300 X_EFFECTIVE_END_DATE => X_HDR_rec.EFFECTIVE_END_DATE,
301 X_OWNER_ID => X_HDR_rec.OWNER_ID,
302 X_PROJECT_ID => X_HDR_rec.PROJECT_ID,
303 X_PROCESS_LOSS => X_HDR_rec.PROCESS_LOSS,
304 X_ROUTING_STATUS => 100,
305 X_ROUTING_DESC => X_HDR_rec.ROUTING_DESC,
306 X_CREATION_DATE => SYSDATE,
307 X_CREATED_BY => P_created_by,
308 X_LAST_UPDATE_DATE => SYSDATE,
309 X_LAST_UPDATED_BY => P_created_by,
310 X_LAST_UPDATE_LOGIN => P_login_id);
311
312
313 /* Insert detail records */
314 FOR i IN 1..X_dtl_tbl.count LOOP
315 IF X_dtl_tbl(i).text_code IS NOT NULL THEN
316 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_dtl_tbl(i).text_code,
317 'FM_TEXT_TBL_TL',
318 'FM_TEXT_TBL_TL');
319 populate_temp_text(X_dtl_tbl(i).text_code,2);--BUG#3258592
320 ELSE
321 l_text_code := NULL;
322 END IF;
323 -- Bug #2673008 (JKB) Added call to copy_text above.
324 INSERT INTO fm_rout_dtl
325 (routing_id, routingstep_no, routingstep_id, oprn_id,
326 step_qty, steprelease_type, text_code, creation_date,
327 created_by, last_update_login, last_update_date,
328 last_updated_by, attribute1, attribute2, attribute3, attribute4,
329 attribute5, attribute6, attribute7, attribute8, attribute9,
330 attribute10, attribute11, attribute12, attribute13, attribute14,
331 attribute15, attribute16, attribute17, attribute18, attribute19,
332 attribute20, attribute21, attribute22, attribute23, attribute24,
333 attribute25, attribute26, attribute27, attribute28, attribute29,
334 attribute30, attribute_category)
335 VALUES (x_routing_id, X_dtl_tbl(i).routingstep_no,
336 gem5_routingstep_id_s.NEXTVAL, X_dtl_tbl(i).oprn_id,
337 X_dtl_tbl(i).step_qty, X_dtl_tbl(i).steprelease_type,
338 l_text_code, SYSDATE, P_created_by, P_login_id, SYSDATE,
339 -- Bug #2673008 (JKB) Changed above.
340 P_created_by, X_dtl_tbl(i).attribute1, X_dtl_tbl(i).attribute2,
341 X_dtl_tbl(i).attribute3, X_dtl_tbl(i).attribute4,
342 X_dtl_tbl(i).attribute5, X_dtl_tbl(i).attribute6,
343 X_dtl_tbl(i).attribute7, X_dtl_tbl(i).attribute8,
344 X_dtl_tbl(i).attribute9, X_dtl_tbl(i).attribute10,
345 X_dtl_tbl(i).attribute11, X_dtl_tbl(i).attribute12,
346 X_dtl_tbl(i).attribute13, X_dtl_tbl(i).attribute14,
347 X_dtl_tbl(i).attribute15, X_dtl_tbl(i).attribute16,
348 X_dtl_tbl(i).attribute17, X_dtl_tbl(i).attribute18,
349 X_dtl_tbl(i).attribute19, X_dtl_tbl(i).attribute20,
350 X_dtl_tbl(i).attribute21, X_dtl_tbl(i).attribute22,
351 X_dtl_tbl(i).attribute23, X_dtl_tbl(i).attribute24,
352 X_dtl_tbl(i).attribute25, X_dtl_tbl(i).attribute26,
353 X_dtl_tbl(i).attribute27, X_dtl_tbl(i).attribute28,
354 X_dtl_tbl(i).attribute29, X_dtl_tbl(i).attribute30,
355 X_dtl_tbl(i).attribute_category);
356 END LOOP;
357
358 -- If GMO is enabled, copy the new PI's from old entity to new entity
359 IF l_gmo_enabled = 'Y' THEN
360 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'ROUTING',
361 p_entity_id => x_routing_id,
362 x_name_array => l_target_name_array,
363 x_key_array => l_target_key_array,
364 x_return_status => l_return_status);
365
366 GMD_PROCESS_INSTR_UTILS.Copy_Process_Instructions (
367 p_source_name_array => l_source_name_array,
368 p_source_key_array => l_source_key_array,
369 p_target_name_array => l_target_name_array,
370 p_target_key_array => l_target_key_array,
371 x_return_status => l_return_status);
372 END IF;
373
374 END create_routing;
375
376 /*======================================================================
377 -- PROCEDURE :
378 -- create_operation
379 --
380 -- DESCRIPTION:
381 -- This PL/SQL procedure is responsible for saving the
382 -- new operation while versioning.
383 --
384 -- REQUIREMENTS
385 --
386 -- SYNOPSIS:
387 -- create_operation(P_oprn_id, X_oprn_id);
388 --
389 -- HISTORY
390 -- 31-Jan-2003 Jeff Baird Bug #2673008 Added call to copy_text API.
391 -- 25-Nov-2003 Vipul Vaish BUG#3258592
392 -- Added call to procedure populate_temp_text after GMA_EDITTEXT_PKG.Copy_Text
393 -- function.
394 -- 01-Jun-2006 TDaniel Bug # 5260696 Added Order by oprn_line_id to Cur_get_actv
395 -- cursor.
396 --============================================================================================ */
397 PROCEDURE create_operation(p_oprn_id IN NUMBER, x_oprn_id OUT NOCOPY NUMBER) IS
398 CURSOR Cur_gen_oprn_id IS
399 SELECT GEM5_OPRN_ID_S.NEXTVAL
400 FROM FND_DUAL;
401 CURSOR Cur_gen_oprnline_id IS
402 SELECT GEM5_OPRNLINE_ID_S.NEXTVAL
403 FROM FND_DUAL;
404
405 CURSOR Cur_get_hdr IS
406 SELECT *
407 FROM gmd_operations_vl
408 WHERE oprn_id = p_oprn_id;
409 X_hdr_rec Cur_get_hdr%ROWTYPE;
410 CURSOR Cur_get_vers IS
411 SELECT MAX(oprn_vers) + 1
412 FROM gmd_operations_vl
413 WHERE oprn_no = X_hdr_rec.oprn_no;
414
415 /* Bug 5260696 - Added order by oprn_line_id */
416 CURSOR Cur_get_actv IS
417 SELECT *
418 FROM gmd_operation_activities
419 WHERE oprn_id = p_oprn_id
420 ORDER BY oprn_line_id;
421 TYPE actv_tab IS TABLE OF Cur_get_actv%ROWTYPE INDEX BY BINARY_INTEGER;
422 X_actv_tbl actv_tab;
423
424 CURSOR Cur_get_rsrc(V_oprn_line_id NUMBER) IS
425 SELECT *
426 FROM gmd_operation_resources
427 WHERE oprn_line_id = V_oprn_line_id;
428
429
430 -- KSHUKLA Added this as per as bug 4186561
431 -- To insert the values of the process parameters
432 -- In the table.
433 -- KSHUKLA added Order by clause
434 CURSOR Cur_get_process_param(V_oprn_line_id NUMBER) IS
435 SELECT *
436 FROM GMD_OPRN_PROCESS_PARAMETERS_V1
437 where oprn_line_id = V_oprn_line_id
438 order by oprn_line_id;
439
440 TYPE rsrc_tab IS TABLE OF Cur_get_rsrc%ROWTYPE INDEX BY BINARY_INTEGER;
441 X_rsrc_tbl rsrc_tab;
442
443 TYPE parm_tab IS TABLE OF Cur_get_process_param%ROWTYPE INDEX BY BINARY_INTEGER;
444 X_parm_tbl parm_tab; --bug 4186561
445
446 X_oprn_vers NUMBER;
447 X_oprn_line_id NUMBER;
448 X_row NUMBER := 0;
449 X_rsrc_cnt NUMBER := 0;
450 X_prcs_cnt NUMBER := 0;
451 l_rowid VARCHAR2(32);
452 l_text_code NUMBER;
453
454 -- Bug #2673008 (JKB) Added l_text_code above.
455
456 l_msg_count NUMBER;
457 l_msg_data VARCHAR2(2000);
458 l_return_status VARCHAR2(10);
459 l_gmo_enabled VARCHAR2(1);
460 l_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
461 l_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
462 l_target_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
463 l_target_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
464 BEGIN
465 OPEN Cur_get_hdr;
466 FETCH Cur_get_hdr INTO X_hdr_rec;
467 CLOSE Cur_get_hdr;
468
469 FOR get_actv IN Cur_get_actv LOOP
470 X_row := X_row + 1;
471 X_actv_tbl(X_row) := get_actv;
472 FOR get_rsrc IN Cur_get_rsrc(get_actv.oprn_line_id) LOOP
473 X_rsrc_cnt := X_rsrc_cnt + 1;
474 X_rsrc_tbl(X_rsrc_cnt) := get_rsrc;
475 END LOOP;
476 -- KSHUKLA changed moved the record fetch out of the get_rsrc loop
477 FOR get_prcs IN Cur_get_process_param(get_actv.oprn_line_id) LOOP
478 X_prcs_cnt := X_prcs_cnt+1;
479 X_parm_tbl(X_prcs_cnt) := get_prcs;
480 END LOOP;
481 END LOOP;
482
483 /* Check if GMO is enabled */
484 l_gmo_enabled := gmo_setup_grp.is_gmo_enabled;
485 IF l_gmo_enabled = 'Y' THEN
486 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'OPERATION',
487 p_entity_id => p_oprn_id,
488 x_name_array => l_source_name_array,
489 x_key_array => l_source_key_array,
490 x_return_status => l_return_status);
491 END IF;
492
493 ROLLBACK;
494
495 OPEN Cur_get_vers;
496 FETCH Cur_get_vers INTO X_oprn_vers;
497 CLOSE Cur_get_vers;
498 OPEN Cur_gen_oprn_id;
499 FETCH Cur_gen_oprn_id INTO x_oprn_id;
500 CLOSE Cur_gen_oprn_id;
501
502
503 /* Insert header record */
504
505
506 IF X_hdr_rec.TEXT_CODE IS NOT NULL THEN
507 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_hdr_rec.TEXT_CODE,
508 'FM_TEXT_TBL_TL',
509 'FM_TEXT_TBL_TL');
510 populate_temp_text(X_hdr_rec.TEXT_CODE,2);--BUG#3258592
511 ELSE
512 l_text_code := NULL;
513 END IF;
514 -- Bug #2673008 (JKB) Added call to copy_text above.
515
516 GMD_OPERATIONS_PKG.INSERT_ROW(
517 X_ROWID => l_rowid ,
518 X_OPRN_ID => x_oprn_id,
519 X_ATTRIBUTE30 => X_HDR_rec.ATTRIBUTE30,
520 X_ATTRIBUTE_CATEGORY => X_HDR_rec.ATTRIBUTE_CATEGORY,
521 X_ATTRIBUTE25 => X_HDR_rec.ATTRIBUTE25,
522 X_ATTRIBUTE26 => X_HDR_rec.ATTRIBUTE26,
523 X_ATTRIBUTE27 => X_HDR_rec.ATTRIBUTE27,
524 X_ATTRIBUTE28 => X_HDR_rec.ATTRIBUTE28,
525 X_ATTRIBUTE29 => X_HDR_rec.ATTRIBUTE29,
526 X_ATTRIBUTE22 => X_HDR_rec.ATTRIBUTE22,
527 X_ATTRIBUTE23 => X_HDR_rec.ATTRIBUTE23,
528 X_ATTRIBUTE24 => X_HDR_rec.ATTRIBUTE24,
529 X_OPRN_NO => X_HDR_rec.OPRN_NO,
530 X_OPRN_VERS => X_OPRN_VERS,
531 X_PROCESS_QTY_UOM => X_HDR_rec.PROCESS_QTY_UOM,
532 X_OPRN_CLASS => X_HDR_rec.OPRN_CLASS,
533 X_INACTIVE_IND => 0,
534 X_EFFECTIVE_START_DATE => X_HDR_rec.EFFECTIVE_START_DATE,
535 X_EFFECTIVE_END_DATE => X_HDR_rec.EFFECTIVE_END_DATE,
536 X_DELETE_MARK => 0,
537 X_TEXT_CODE => l_text_code,
538 -- Bug #2673008 (JKB) Changed above.
539 X_ATTRIBUTE1 => X_HDR_rec.ATTRIBUTE1,
540 X_ATTRIBUTE2 => X_HDR_rec.ATTRIBUTE2,
541 X_ATTRIBUTE3 => X_HDR_rec.ATTRIBUTE3,
542 X_ATTRIBUTE4 => X_HDR_rec.ATTRIBUTE4,
543 X_ATTRIBUTE5 => X_HDR_rec.ATTRIBUTE5,
544 X_ATTRIBUTE6 => X_HDR_rec.ATTRIBUTE6,
545 X_ATTRIBUTE7 => X_HDR_rec.ATTRIBUTE7,
546 X_ATTRIBUTE8 => X_HDR_rec.ATTRIBUTE8,
547 X_ATTRIBUTE9 => X_HDR_rec.ATTRIBUTE9,
548 X_ATTRIBUTE10 => X_HDR_rec.ATTRIBUTE10,
549 X_ATTRIBUTE11 => X_HDR_rec.ATTRIBUTE11,
550 X_ATTRIBUTE12 => X_HDR_rec.ATTRIBUTE12,
551 X_ATTRIBUTE13 => X_HDR_rec.ATTRIBUTE13,
552 X_ATTRIBUTE14 => X_HDR_rec.ATTRIBUTE14,
553 X_ATTRIBUTE15 => X_HDR_rec.ATTRIBUTE15,
554 X_ATTRIBUTE16 => X_HDR_rec.ATTRIBUTE16,
555 X_ATTRIBUTE17 => X_HDR_rec.ATTRIBUTE17,
556 X_ATTRIBUTE18 => X_HDR_rec.ATTRIBUTE18,
557 X_ATTRIBUTE19 => X_HDR_rec.ATTRIBUTE19,
558 X_ATTRIBUTE20 => X_HDR_rec.ATTRIBUTE20,
559 X_ATTRIBUTE21 => X_HDR_rec.ATTRIBUTE21,
560 X_OPERATION_STATUS => 100,
561 X_OWNER_ORGANIZATION_ID => X_HDR_rec.OWNER_ORGANIZATION_ID,
562 X_OPRN_DESC => X_HDR_rec.OPRN_DESC,
563 X_CREATION_DATE => SYSDATE,
564 X_CREATED_BY => P_CREATED_BY,
565 X_LAST_UPDATE_DATE => SYSDATE,
566 X_LAST_UPDATED_BY => P_CREATED_BY,
567 X_LAST_UPDATE_LOGIN => P_login_id);
568
569
570 /* Insert Activities */
571 X_rsrc_cnt := 0;
572 X_prcs_cnt := 0; --bug 4186561
573 FOR i IN 1..X_actv_tbl.count LOOP
574 -- Regulating the text code here
575 IF X_actv_tbl(i).text_code IS NOT NULL THEN
576 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_actv_tbl(i).text_code,
577 'FM_TEXT_TBL_TL',
578 'FM_TEXT_TBL_TL');
579 populate_temp_text(X_actv_tbl(i).text_code,2);--BUG#3258592
580 ELSE
581 l_text_code := NULL;
582 END IF;
583
584 -- Bug #2673008 (JKB) Added call to copy_text above.
585 OPEN Cur_gen_oprnline_id;
586 FETCH Cur_gen_oprnline_id INTO X_oprn_line_id;
587 CLOSE Cur_gen_oprnline_id;
588
589 INSERT INTO gmd_operation_activities (oprn_id, oprn_line_id, activity,
590 activity_factor, delete_mark, text_code, last_updated_by,
591 created_by, last_update_date, creation_date,
592 last_update_login, attribute1, attribute2, attribute3,
593 attribute4, attribute5, attribute6, attribute7, attribute8,
594 attribute9, attribute10, attribute11, attribute12,
595 attribute13, attribute14, attribute15, attribute16,
596 attribute17, attribute18, attribute19, attribute20,
597 attribute21, attribute22, attribute23, attribute24,
598 attribute25, attribute26, attribute27, attribute28,
599 attribute29, attribute30, attribute_category,
600 offset_interval, break_ind, max_break)
601 VALUES (x_oprn_id, X_oprn_line_id, X_actv_tbl(i).activity,
602 X_actv_tbl(i).activity_factor, 0, l_text_code, P_created_by,
603 -- Bug #2673008 (JKB) Changed above.
604 P_created_by, SYSDATE, SYSDATE, P_login_id,
605 X_actv_tbl(i).attribute1, X_actv_tbl(i).attribute2,
606 X_actv_tbl(i).attribute3, X_actv_tbl(i).attribute4,
607 X_actv_tbl(i).attribute5, X_actv_tbl(i).attribute6,
608 X_actv_tbl(i).attribute7, X_actv_tbl(i).attribute8,
609 X_actv_tbl(i).attribute9, X_actv_tbl(i).attribute10,
610 X_actv_tbl(i).attribute11, X_actv_tbl(i).attribute12,
611 X_actv_tbl(i).attribute13, X_actv_tbl(i).attribute14,
612 X_actv_tbl(i).attribute15, X_actv_tbl(i).attribute16,
613 X_actv_tbl(i).attribute17, X_actv_tbl(i).attribute18,
614 X_actv_tbl(i).attribute19, X_actv_tbl(i).attribute20,
615 X_actv_tbl(i).attribute21, X_actv_tbl(i).attribute22,
616 X_actv_tbl(i).attribute23, X_actv_tbl(i).attribute24,
617 X_actv_tbl(i).attribute25, X_actv_tbl(i).attribute26,
618 X_actv_tbl(i).attribute27, X_actv_tbl(i).attribute28,
619 X_actv_tbl(i).attribute29, X_actv_tbl(i).attribute30,
620 X_actv_tbl(i).attribute_category,
621 X_actv_tbl(i).offset_interval, X_actv_tbl(i).break_ind,
622 X_actv_tbl(i).max_break);
623 LOOP
624 X_rsrc_cnt := X_rsrc_cnt + 1;
625 IF (X_rsrc_cnt > X_rsrc_tbl.count) THEN
626 EXIT;
627 END IF;
628 IF (X_actv_tbl(i).oprn_line_id = X_rsrc_tbl(X_rsrc_cnt).oprn_line_id) THEN
629 IF X_rsrc_tbl(X_rsrc_cnt).text_code IS NOT NULL THEN
630 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_rsrc_tbl(X_rsrc_cnt).text_code,
631 'FM_TEXT_TBL_TL',
632 'FM_TEXT_TBL_TL');
633 populate_temp_text(X_rsrc_tbl(X_rsrc_cnt).text_code,2);--BUG#3258592
634 ELSE
635 l_text_code := NULL;
636 END IF;
637 -- Bug #2673008 (JKB) Added call to copy_text above.
638 INSERT INTO gmd_operation_resources (oprn_line_id, resources,
639 resource_usage, resource_count, process_qty,
640 prim_rsrc_ind, scale_type, cost_analysis_code,
641 cost_cmpntcls_id, usage_um, offset_interval,
642 delete_mark, text_code, created_by, last_updated_by,
643 last_update_date, creation_date, last_update_login,
644 attribute1, attribute2, attribute3, attribute4,
645 attribute5, attribute6, attribute7, attribute8,
646 attribute9, attribute10, attribute11, attribute12,
647 attribute13, attribute14, attribute15, attribute16,
648 attribute17, attribute18, attribute19, attribute20,
649 attribute21, attribute22, attribute23, attribute24,
650 attribute25, attribute26, attribute27, attribute28,
651 attribute29, attribute30, attribute_category,
652 process_uom, min_capacity, max_capacity, capacity_uom,
653 process_parameter_1, process_parameter_2,
654 process_parameter_3, process_parameter_4,
655 process_parameter_5,RESOURCE_USAGE_UOM,
656 RESOURCE_PROCESS_UOM ,RESOURCE_CAPACITY_UOM)--bug 4186561
657 VALUES (X_oprn_line_id, X_rsrc_tbl(X_rsrc_cnt).resources,
658 X_rsrc_tbl(X_rsrc_cnt).resource_usage,
659 X_rsrc_tbl(X_rsrc_cnt).resource_count,
660 X_rsrc_tbl(X_rsrc_cnt).process_qty,
661 X_rsrc_tbl(X_rsrc_cnt).prim_rsrc_ind,
662 X_rsrc_tbl(X_rsrc_cnt).scale_type,
663 X_rsrc_tbl(X_rsrc_cnt).cost_analysis_code,
664 X_rsrc_tbl(X_rsrc_cnt).cost_cmpntcls_id,
665 X_rsrc_tbl(X_rsrc_cnt).usage_um,
666 X_rsrc_tbl(X_rsrc_cnt).offset_interval,
667 X_rsrc_tbl(X_rsrc_cnt).delete_mark, l_text_code,
668 -- Bug #2673008 (JKB) Changed above.
669 P_created_by, P_created_by, SYSDATE, SYSDATE,
670 P_login_id, X_rsrc_tbl(X_rsrc_cnt).attribute1,
671 X_rsrc_tbl(X_rsrc_cnt).attribute2,
672 X_rsrc_tbl(X_rsrc_cnt).attribute3,
673 X_rsrc_tbl(X_rsrc_cnt).attribute4,
674 X_rsrc_tbl(X_rsrc_cnt).attribute5,
675 X_rsrc_tbl(X_rsrc_cnt).attribute6,
676 X_rsrc_tbl(X_rsrc_cnt).attribute7,
677 X_rsrc_tbl(X_rsrc_cnt).attribute8,
678 X_rsrc_tbl(X_rsrc_cnt).attribute9,
679 X_rsrc_tbl(X_rsrc_cnt).attribute10,
680 X_rsrc_tbl(X_rsrc_cnt).attribute11,
681 X_rsrc_tbl(X_rsrc_cnt).attribute12,
682 X_rsrc_tbl(X_rsrc_cnt).attribute13,
683 X_rsrc_tbl(X_rsrc_cnt).attribute14,
684 X_rsrc_tbl(X_rsrc_cnt).attribute15,
685 X_rsrc_tbl(X_rsrc_cnt).attribute16,
686 X_rsrc_tbl(X_rsrc_cnt).attribute17,
687 X_rsrc_tbl(X_rsrc_cnt).attribute18,
688 X_rsrc_tbl(X_rsrc_cnt).attribute19,
689 X_rsrc_tbl(X_rsrc_cnt).attribute20,
690 X_rsrc_tbl(X_rsrc_cnt).attribute21,
691 X_rsrc_tbl(X_rsrc_cnt).attribute22,
692 X_rsrc_tbl(X_rsrc_cnt).attribute23,
693 X_rsrc_tbl(X_rsrc_cnt).attribute24,
694 X_rsrc_tbl(X_rsrc_cnt).attribute25,
695 X_rsrc_tbl(X_rsrc_cnt).attribute26,
696 X_rsrc_tbl(X_rsrc_cnt).attribute27,
697 X_rsrc_tbl(X_rsrc_cnt).attribute28,
698 X_rsrc_tbl(X_rsrc_cnt).attribute29,
699 X_rsrc_tbl(X_rsrc_cnt).attribute30,
700 X_rsrc_tbl(X_rsrc_cnt).attribute_category,
701 X_rsrc_tbl(X_rsrc_cnt).process_uom,
702 X_rsrc_tbl(X_rsrc_cnt).min_capacity,
703 X_rsrc_tbl(X_rsrc_cnt).max_capacity,
704 X_rsrc_tbl(X_rsrc_cnt).capacity_uom,
705 X_rsrc_tbl(X_rsrc_cnt).process_parameter_1,
706 X_rsrc_tbl(X_rsrc_cnt).process_parameter_2,
707 X_rsrc_tbl(X_rsrc_cnt).process_parameter_3,
708 X_rsrc_tbl(X_rsrc_cnt).process_parameter_4,
709 X_rsrc_tbl(X_rsrc_cnt).process_parameter_5,
710 X_rsrc_tbl(X_rsrc_cnt).RESOURCE_USAGE_UOM,
711 X_rsrc_tbl(X_rsrc_cnt).RESOURCE_PROCESS_UOM,
712 X_rsrc_tbl(X_rsrc_cnt).RESOURCE_CAPACITY_UOM);--bug 4186561
713 ELSE
714 X_rsrc_cnt := X_rsrc_cnt - 1;
715 EXIT;
716 END IF;
717 END LOOP;
718 -- Once all the resources are entered
719 -- Inserting the process parameters
720
721 FOR j IN 1..X_parm_tbl.COUNT LOOP
722 IF (X_actv_tbl(i).oprn_line_id = X_parm_tbl(j).oprn_line_id) THEN
723 INSERT INTO GMD_OPRN_PROCESS_PARAMETERS(OPRN_LINE_ID ,
724 RESOURCES ,
725 PARAMETER_ID ,
726 TARGET_VALUE ,
727 MINIMUM_VALUE ,
728 MAXIMUM_VALUE ,
729 CREATION_DATE ,
730 LAST_UPDATE_LOGIN ,
731 CREATED_BY ,
732 LAST_UPDATE_DATE ,
733 LAST_UPDATED_BY)
734 VALUES (X_oprn_line_id,
735 X_parm_tbl(j).resources,
736 x_parm_tbl(j).parameter_id,
737 x_parm_tbl(j).target_value,
738 x_parm_tbl(j).minimum_value,
739 x_parm_tbl(j).maximum_value,
740 SYSDATE,
741 P_created_by,
742 P_created_by,
743 SYSDATE,
744 P_login_id);
745 ELSE
746 EXIT;
747 END IF;
748 END LOOP;
749
750 END LOOP;
751
752 -- If GMO is enabled, copy the new PI's from old entity to new entity
753 IF l_gmo_enabled = 'Y' THEN
754 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'OPERATION',
755 p_entity_id => x_oprn_id,
756 x_name_array => l_target_name_array,
757 x_key_array => l_target_key_array,
758 x_return_status => l_return_status);
759
760 GMD_PROCESS_INSTR_UTILS.Copy_Process_Instructions (
761 p_source_name_array => l_source_name_array,
762 p_source_key_array => l_source_key_array,
763 p_target_name_array => l_target_name_array,
764 p_target_key_array => l_target_key_array,
765 x_return_status => l_return_status);
766 END IF;
767
768 END create_operation;
769
770 /*======================================================================
771 -- PROCEDURE :
772 -- create_recipe
773 --
774 -- DESCRIPTION:
775 -- This PL/SQL procedure is responsible for saving the
776 -- new recipe while versioning.
777 --
778 -- REQUIREMENTS
779 --
780 -- SYNOPSIS:
781 -- create_recipe(P_recipe_id, X_recipe_id);
782 --
783 -- HISTORY
784 -- 31-Jan-2003 Jeff Baird Bug #2673008 Added call to copy_text API.
785 -- 25-Nov-2003 Vipul Vaish BUG#3258592
786 -- Added call to procedure populate_temp_text after GMA_EDITTEXT_PKG.Copy_Text
787 -- function.
788 --
789 --===================================================================== */
790 PROCEDURE create_recipe(p_recipe_id IN NUMBER, x_recipe_id OUT NOCOPY NUMBER) IS
791 l_rowid VARCHAR2(18);
792 X_recipe_vers NUMBER;
793 X_row NUMBER;
794 l_text_code NUMBER;
795 -- Bug #2673008 (JKB) Added l_text_code above.
796
797 CURSOR Cur_get_hdr IS
798 SELECT *
799 FROM gmd_recipes
800 WHERE recipe_id = p_recipe_id;
801 X_hdr_rec Cur_get_hdr%ROWTYPE;
802
803 CURSOR Cur_process_loss IS
804 SELECT *
805 FROM gmd_recipe_process_loss
806 WHERE recipe_id = p_recipe_id;
807 TYPE proc_loss IS TABLE OF Cur_process_loss%ROWTYPE INDEX BY BINARY_INTEGER;
808 X_proc_loss_tbl proc_loss;
809
810 CURSOR Cur_get_cust IS
811 SELECT *
812 FROM gmd_recipe_customers
813 WHERE recipe_id = p_recipe_id;
814 TYPE rcp_cust IS TABLE OF Cur_get_cust%ROWTYPE INDEX BY BINARY_INTEGER;
815 X_cust_tbl rcp_cust;
816
817 CURSOR Cur_get_steps IS
818 SELECT *
819 FROM gmd_recipe_routing_steps
820 WHERE recipe_id = p_recipe_id;
821 TYPE rcp_steps IS TABLE OF Cur_get_steps%ROWTYPE INDEX BY BINARY_INTEGER;
822 X_step_tbl rcp_steps;
823
824 CURSOR Cur_get_vr IS
825 SELECT *
826 FROM gmd_recipe_validity_rules
827 WHERE recipe_id = p_recipe_id;
828 TYPE rcp_vr IS TABLE OF Cur_get_vr%ROWTYPE INDEX BY BINARY_INTEGER;
829 X_vr_tbl rcp_vr;
830
831 CURSOR Cur_get_actv IS
832 SELECT *
833 FROM gmd_recipe_orgn_activities
834 WHERE recipe_id = p_recipe_id;
835 TYPE rcp_actv IS TABLE OF Cur_get_actv%ROWTYPE INDEX BY BINARY_INTEGER;
836 X_actv_tbl rcp_actv;
837
838 CURSOR Cur_get_rsrc IS
839 SELECT *
840 FROM gmd_recipe_orgn_resources
841 WHERE recipe_id = p_recipe_id;
842 TYPE rcp_rsrc IS TABLE OF Cur_get_rsrc%ROWTYPE INDEX BY BINARY_INTEGER;
843 X_rsrc_tbl rcp_rsrc;
844
845 CURSOR Cur_step_mtl IS
846 SELECT *
847 FROM gmd_recipe_step_materials
848 WHERE recipe_id = p_recipe_id;
849 TYPE step_mtl IS TABLE OF Cur_step_mtl%ROWTYPE INDEX BY BINARY_INTEGER;
850 X_stepmtl_tbl step_mtl;
851
852 CURSOR Cur_recipe_vers IS
853 SELECT MAX(recipe_version) + 1
854 FROM gmd_recipes
855 WHERE recipe_no = X_hdr_rec.recipe_no;
856 CURSOR Cur_recipe_id IS
857 SELECT gmd_recipe_id_s.NEXTVAL
858 FROM FND_DUAL;
859
860 CURSOR Cur_get_old_recipe_det (V_recipe_id NUMBER) IS
861 SELECT formula_id, routing_id
862 FROM gmd_recipes_b
863 WHERE recipe_id = v_recipe_id;
864
865 l_msg_count NUMBER;
866 l_msg_data VARCHAR2(2000);
867 l_return_status VARCHAR2(10);
868
869 l_formula_id NUMBER(15);
870 l_routing_id NUMBER(15);
871 l_form_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
872 l_form_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
873 l_rout_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
874 l_rout_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
875 l_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
876 l_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
877 l_target_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
878 l_target_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
879
880 j PLS_INTEGER;
881
882 BEGIN
883 OPEN Cur_get_hdr;
884 FETCH Cur_get_hdr INTO X_hdr_rec;
885 CLOSE Cur_get_hdr;
886
887 X_row := 0;
888 FOR get_loss IN Cur_process_loss LOOP
889 X_row := X_row + 1;
890 X_proc_loss_tbl(X_row) := get_loss;
891 END LOOP;
892
893 X_row := 0;
894 FOR get_cust IN Cur_get_cust LOOP
895 X_row := X_row + 1;
896 X_cust_tbl(X_row) := get_cust;
897 END LOOP;
898
899 X_row := 0;
900 FOR get_steps IN Cur_get_steps LOOP
901 X_row := X_row + 1;
902 X_step_tbl(X_row) := get_steps;
903 END LOOP;
904
905 X_row := 0;
906 FOR get_vr IN Cur_get_vr LOOP
907 X_row := X_row + 1;
908 X_vr_tbl(X_row) := get_vr;
909 END LOOP;
910
911 X_row := 0;
912 FOR get_actv IN Cur_get_actv LOOP
913 X_row := X_row + 1;
914 X_actv_tbl(X_row) := get_actv;
915 END LOOP;
916
917 X_row := 0;
918 FOR get_rsrc IN Cur_get_rsrc LOOP
919 X_row := X_row + 1;
920 X_rsrc_tbl(X_row) := get_rsrc;
921 END LOOP;
922
923 X_row := 0;
924 FOR get_step_mtl IN Cur_step_mtl LOOP
925 X_row := X_row + 1;
926 X_stepmtl_tbl(X_row) := get_step_mtl;
927 END LOOP;
928
929 ROLLBACK;
930 OPEN Cur_recipe_vers;
931 FETCH Cur_recipe_vers INTO X_recipe_vers;
932 CLOSE Cur_recipe_vers;
933 OPEN Cur_recipe_id;
934 FETCH Cur_recipe_id INTO x_recipe_id;
935 CLOSE Cur_recipe_id;
936 IF X_hdr_rec.TEXT_CODE IS NOT NULL THEN
937 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_hdr_rec.TEXT_CODE,
938 'FM_TEXT_TBL_TL',
939 'FM_TEXT_TBL_TL');
940 populate_temp_text(X_hdr_rec.TEXT_CODE,2);--BUG#3258592
941 ELSE
942 l_text_code := NULL;
943 END IF;
944 -- Bug #2673008 (JKB) Added call to copy_text above.
945 gmd_recipes_mls.insert_row(X_ROWID => l_rowid,
946 X_RECIPE_ID => x_recipe_id,
947 X_OWNER_ID => X_hdr_rec.owner_id,
948 X_OWNER_LAB_TYPE => X_hdr_rec.owner_lab_type,
949 X_DELETE_MARK => 0,
950 X_TEXT_CODE => l_text_code,
951 -- Bug #2673008 (JKB) Changed above.
952 X_RECIPE_NO => X_hdr_rec.recipe_no,
953 X_RECIPE_VERSION => X_recipe_vers,
954 X_OWNER_ORGANIZATION_ID => X_hdr_rec.OWNER_ORGANIZATION_ID,
955 X_CREATION_ORGANIZATION_ID => X_hdr_rec.creation_ORGANIZATION_ID,
956 X_FORMULA_ID => X_hdr_rec.formula_id,
957 X_ROUTING_ID => X_hdr_rec.routing_id,
958 X_PROJECT_ID => X_hdr_rec.project_id,
959 X_RECIPE_STATUS => 100,
960 X_CALCULATE_STEP_QUANTITY => X_hdr_rec.calculate_step_quantity,
961 X_CONTIGUOUS_IND => X_hdr_rec.contiguous_ind,
962 X_PLANNED_PROCESS_LOSS => X_hdr_rec.planned_process_loss,
963 X_RECIPE_DESCRIPTION => X_hdr_rec.recipe_description,
964 X_ENHANCED_PI_IND => X_hdr_rec.enhanced_pi_ind,
965 X_RECIPE_TYPE => X_hdr_rec.recipe_type,
966 X_ATTRIBUTE_CATEGORY => X_hdr_rec.attribute_category,
967 X_ATTRIBUTE1 => X_hdr_rec.attribute1,
968 X_ATTRIBUTE2 => X_hdr_rec.attribute2,
969 X_ATTRIBUTE3 => X_hdr_rec.attribute3,
970 X_ATTRIBUTE4 => X_hdr_rec.attribute4,
971 X_ATTRIBUTE5 => X_hdr_rec.attribute5,
972 X_ATTRIBUTE6 => X_hdr_rec.attribute6,
973 X_ATTRIBUTE7 => X_hdr_rec.attribute7,
974 X_ATTRIBUTE8 => X_hdr_rec.attribute8,
975 X_ATTRIBUTE9 => X_hdr_rec.attribute9,
976 X_ATTRIBUTE10 => X_hdr_rec.attribute10,
977 X_ATTRIBUTE11 => X_hdr_rec.attribute11,
978 X_ATTRIBUTE12 => X_hdr_rec.attribute12,
979 X_ATTRIBUTE13 => X_hdr_rec.attribute13,
980 X_ATTRIBUTE14 => X_hdr_rec.attribute14,
981 X_ATTRIBUTE15 => X_hdr_rec.attribute15,
982 X_ATTRIBUTE16 => X_hdr_rec.attribute16,
983 X_ATTRIBUTE17 => X_hdr_rec.attribute17,
984 X_ATTRIBUTE18 => X_hdr_rec.attribute18,
985 X_ATTRIBUTE19 => X_hdr_rec.attribute19,
986 X_ATTRIBUTE20 => X_hdr_rec.attribute20,
987 X_ATTRIBUTE21 => X_hdr_rec.attribute21,
988 X_ATTRIBUTE22 => X_hdr_rec.attribute22,
989 X_ATTRIBUTE23 => X_hdr_rec.attribute23,
990 X_ATTRIBUTE24 => X_hdr_rec.attribute24,
991 X_ATTRIBUTE25 => X_hdr_rec.attribute25,
992 X_ATTRIBUTE26 => X_hdr_rec.attribute26,
993 X_ATTRIBUTE27 => X_hdr_rec.attribute27,
994 X_ATTRIBUTE28 => X_hdr_rec.attribute28,
995 X_ATTRIBUTE29 => X_hdr_rec.attribute29,
996 X_ATTRIBUTE30 => X_hdr_rec.attribute30,
997 X_CREATION_DATE => SYSDATE,
998 X_CREATED_BY => P_created_by,
999 X_LAST_UPDATE_DATE => SYSDATE,
1000 X_LAST_UPDATED_BY => P_created_by,
1001 X_LAST_UPDATE_LOGIN => P_login_id,
1002 X_FIXED_PROCESS_LOSS => X_hdr_rec.fixed_process_loss, /* B6811759 */
1003 X_FIXED_PROCESS_LOSS_UOM => X_hdr_rec.fixed_process_loss_uom
1004 );
1005 FOR i IN 1..X_proc_loss_tbl.count LOOP
1006 IF X_proc_loss_tbl(i).text_code IS NOT NULL THEN
1007 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_proc_loss_tbl(i).text_code,
1008 'FM_TEXT_TBL_TL',
1009 'FM_TEXT_TBL_TL');
1010 populate_temp_text(X_proc_loss_tbl(i).text_code,2);--BUG#3258592
1011 ELSE
1012 l_text_code := NULL;
1013 END IF;
1014 -- Bug #2673008 (JKB) Added call to copy_text above.
1015 /* B6811759 */
1016 INSERT INTO gmd_recipe_process_loss(recipe_id, organization_id, process_loss,
1017 creation_date, created_by,
1018 last_updated_by, last_update_date, last_update_login,
1019 recipe_process_loss_id, text_code,fixed_process_loss,fixed_process_loss_uom)
1020 VALUES (x_recipe_id, X_proc_loss_tbl(i).organization_id,
1021 X_proc_loss_tbl(i).process_loss,
1022 SYSDATE, P_created_by, P_created_by, SYSDATE, P_login_id,
1023 gmd_recipe_process_loss_id_s.NEXTVAL, l_text_code,
1024 X_proc_loss_tbl(i).fixed_process_loss,
1025 X_proc_loss_tbl(i).fixed_process_loss_uom);
1026 -- Bug #2673008 (JKB) Changed above.
1027 END LOOP;
1028 FOR i IN 1..X_cust_tbl.count LOOP
1029 IF X_cust_tbl(i).text_code IS NOT NULL THEN
1030 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_cust_tbl(i).text_code,
1031 'FM_TEXT_TBL_TL',
1032 'FM_TEXT_TBL_TL');
1033 populate_temp_text(X_cust_tbl(i).text_code,2);--BUG#3258592
1034 ELSE
1035 l_text_code := NULL;
1036 END IF;
1037 -- Bug #2673008 (JKB) Added call to copy_text above.
1038 INSERT INTO gmd_recipe_customers(recipe_id, customer_id,org_id,site_id, created_by,
1039 creation_date, last_updated_by,
1040 last_update_login, text_code, last_update_date)
1041 VALUES (x_recipe_id, X_cust_tbl(i).customer_id,X_cust_tbl(i).org_id,
1042 X_cust_tbl(i).site_id,P_created_by,
1043 SYSDATE, P_created_by, P_login_id, l_text_code, SYSDATE);
1044 -- Bug #2673008 (JKB) Changed above.
1045 END LOOP;
1046 FOR i IN 1..X_step_tbl.count LOOP
1047 IF X_step_tbl(i).text_code IS NOT NULL THEN
1048 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_step_tbl(i).text_code,
1049 'FM_TEXT_TBL_TL',
1050 'FM_TEXT_TBL_TL');
1051 populate_temp_text(X_step_tbl(i).text_code,2);--BUG#3258592
1052 ELSE
1053 l_text_code := NULL;
1054 END IF;
1055 -- Bug #2673008 (JKB) Added call to copy_text above.
1056 INSERT INTO gmd_recipe_routing_steps(recipe_id, routingstep_id, step_qty,
1057 created_by, creation_date,
1058 last_update_date, last_update_login, text_code,
1059 last_updated_by, attribute1, attribute2, attribute3,
1060 attribute4, attribute5, attribute6, attribute7, attribute8,
1061 attribute9, attribute10, attribute11, attribute12,
1062 attribute13, attribute14, attribute15, attribute16,
1063 attribute17, attribute18, attribute19, attribute20,
1064 attribute21, attribute22, attribute23, attribute24,
1065 attribute25, attribute26, attribute27, attribute28,
1066 attribute29, attribute30, attribute_category, mass_std_uom,
1067 volume_std_uom, volume_qty, mass_qty)
1068 VALUES (x_recipe_id, X_step_tbl(i).routingstep_id,
1069 X_step_tbl(i).step_qty, P_created_by, SYSDATE, SYSDATE,
1070 P_login_id, l_text_code, P_created_by,
1071 -- Bug #2673008 (JKB) Changed above.
1072 X_step_tbl(i).attribute1, X_step_tbl(i).attribute2,
1073 X_step_tbl(i).attribute3, X_step_tbl(i).attribute4,
1074 X_step_tbl(i).attribute5, X_step_tbl(i).attribute6,
1075 X_step_tbl(i).attribute7, X_step_tbl(i).attribute8,
1076 X_step_tbl(i).attribute9, X_step_tbl(i).attribute10,
1077 X_step_tbl(i).attribute11, X_step_tbl(i).attribute12,
1078 X_step_tbl(i).attribute13, X_step_tbl(i).attribute14,
1079 X_step_tbl(i).attribute15, X_step_tbl(i).attribute16,
1080 X_step_tbl(i).attribute17, X_step_tbl(i).attribute18,
1081 X_step_tbl(i).attribute19, X_step_tbl(i).attribute20,
1082 X_step_tbl(i).attribute21, X_step_tbl(i).attribute22,
1083 X_step_tbl(i).attribute23, X_step_tbl(i).attribute24,
1084 X_step_tbl(i).attribute25, X_step_tbl(i).attribute26,
1085 X_step_tbl(i).attribute27, X_step_tbl(i).attribute28,
1086 X_step_tbl(i).attribute29, X_step_tbl(i).attribute30,
1087 X_step_tbl(i).attribute_category,
1088 X_step_tbl(i).mass_std_uom, X_step_tbl(i).volume_std_uom,
1089 X_step_tbl(i).volume_qty, X_step_tbl(i).mass_qty);
1090 END LOOP;
1091 FOR i IN 1..X_vr_tbl.count LOOP
1092 IF X_vr_tbl(i).text_code IS NOT NULL THEN
1093 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_vr_tbl(i).text_code,
1094 'FM_TEXT_TBL_TL',
1095 'FM_TEXT_TBL_TL');
1096 populate_temp_text(X_vr_tbl(i).text_code,2);--BUG#3258592
1097 ELSE
1098 l_text_code := NULL;
1099 END IF;
1100 -- Bug #2673008 (JKB) Added call to copy_text above.
1101 INSERT INTO gmd_recipe_validity_rules(recipe_validity_rule_id, recipe_id,
1102 organization_id, inventory_item_id, revision, recipe_use, preference,
1103 start_date, end_date, min_qty, max_qty, std_qty, detail_uom,
1104 inv_min_qty, inv_max_qty, text_code, attribute_category,
1105 attribute1, attribute2, attribute3, attribute4, attribute5,
1106 attribute6, attribute7, attribute8, attribute9,
1107 attribute10, attribute11, attribute12, attribute13,
1108 attribute14, attribute15, attribute16, attribute17,
1109 attribute18, attribute19, attribute20, attribute21,
1110 attribute22, attribute23, attribute24, attribute25,
1111 attribute26, attribute27, attribute28, attribute29,
1112 attribute30, created_by, creation_date,
1113 last_updated_by, last_update_date, last_update_login,
1114 delete_mark, lab_type, validity_rule_status)
1115 VALUES (gmd_recipe_validity_id_s.NEXTVAL, x_recipe_id,
1116 X_vr_tbl(i).organization_id, X_vr_tbl(i).inventory_item_id,
1117 X_vr_tbl(i).revision,X_vr_tbl(i).recipe_use, X_vr_tbl(i).preference,
1118 X_vr_tbl(i).start_date, X_vr_tbl(i).end_date,
1119 X_vr_tbl(i).min_qty, X_vr_tbl(i).max_qty,
1120 X_vr_tbl(i).std_qty, X_vr_tbl(i).detail_uom,
1121 X_vr_tbl(i).inv_min_qty, X_vr_tbl(i).inv_max_qty,
1122 l_text_code, X_vr_tbl(i).attribute_category,
1123 -- Bug #2673008 (JKB) Changed above.
1124 X_vr_tbl(i).attribute1, X_vr_tbl(i).attribute2,
1125 X_vr_tbl(i).attribute3, X_vr_tbl(i).attribute4,
1126 X_vr_tbl(i).attribute5, X_vr_tbl(i).attribute6,
1127 X_vr_tbl(i).attribute7, X_vr_tbl(i).attribute8,
1128 X_vr_tbl(i).attribute9, X_vr_tbl(i).attribute10,
1129 X_vr_tbl(i).attribute11, X_vr_tbl(i).attribute12,
1130 X_vr_tbl(i).attribute13, X_vr_tbl(i).attribute14,
1131 X_vr_tbl(i).attribute15, X_vr_tbl(i).attribute16,
1132 X_vr_tbl(i).attribute17, X_vr_tbl(i).attribute18,
1133 X_vr_tbl(i).attribute19, X_vr_tbl(i).attribute20,
1134 X_vr_tbl(i).attribute21, X_vr_tbl(i).attribute22,
1135 X_vr_tbl(i).attribute23, X_vr_tbl(i).attribute24,
1136 X_vr_tbl(i).attribute25, X_vr_tbl(i).attribute26,
1137 X_vr_tbl(i).attribute27, X_vr_tbl(i).attribute28,
1138 X_vr_tbl(i).attribute29, X_vr_tbl(i).attribute30,
1139 P_created_by, SYSDATE, P_created_by, SYSDATE,
1140 P_login_id, 0, X_vr_tbl(i).lab_type, 100);
1141 END LOOP;
1142 FOR i IN 1..X_actv_tbl.count LOOP
1143 IF X_actv_tbl(i).text_code IS NOT NULL THEN
1144 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_actv_tbl(i).text_code,
1145 'FM_TEXT_TBL_TL',
1146 'FM_TEXT_TBL_TL');
1147 populate_temp_text(X_actv_tbl(i).text_code,2);--BUG#3258592
1148 ELSE
1149 l_text_code := NULL;
1150 END IF;
1151 -- Bug #2673008 (JKB) Added call to copy_text above.
1152 INSERT INTO gmd_recipe_orgn_activities(recipe_id, routingstep_id,
1153 activity_factor, attribute_category, attribute1,
1154 created_by, creation_date, last_updated_by,
1155 last_update_date, last_update_login, organization_id, attribute2,
1156 attribute3, attribute4, attribute5, attribute6, attribute7,
1157 attribute8, attribute9, attribute10, attribute11,
1158 attribute12, attribute13, attribute14, attribute15,
1159 attribute16, attribute17, attribute18, attribute19,
1160 attribute20, attribute21, attribute22, attribute23,
1161 attribute24, attribute25, attribute26, attribute27,
1162 attribute28, attribute29, attribute30, text_code,
1163 oprn_line_id)
1164 VALUES (x_recipe_id, X_actv_tbl(i).routingstep_id,
1165 X_actv_tbl(i).activity_factor,
1166 X_actv_tbl(i).attribute_category, X_actv_tbl(i).attribute1,
1167 P_created_by, SYSDATE, P_created_by, SYSDATE, P_login_id,
1168 X_actv_tbl(i).organization_id, X_actv_tbl(i).attribute2,
1169 X_actv_tbl(i).attribute3, X_actv_tbl(i).attribute4,
1170 X_actv_tbl(i).attribute5, X_actv_tbl(i).attribute6,
1171 X_actv_tbl(i).attribute7, X_actv_tbl(i).attribute8,
1172 X_actv_tbl(i).attribute9, X_actv_tbl(i).attribute10,
1173 X_actv_tbl(i).attribute11, X_actv_tbl(i).attribute12,
1174 X_actv_tbl(i).attribute13, X_actv_tbl(i).attribute14,
1175 X_actv_tbl(i).attribute15, X_actv_tbl(i).attribute16,
1176 X_actv_tbl(i).attribute17, X_actv_tbl(i).attribute18,
1177 X_actv_tbl(i).attribute19, X_actv_tbl(i).attribute20,
1178 X_actv_tbl(i).attribute21, X_actv_tbl(i).attribute22,
1179 X_actv_tbl(i).attribute23, X_actv_tbl(i).attribute24,
1180 X_actv_tbl(i).attribute25, X_actv_tbl(i).attribute26,
1181 X_actv_tbl(i).attribute27, X_actv_tbl(i).attribute28,
1182 X_actv_tbl(i).attribute29, X_actv_tbl(i).attribute30,
1183 l_text_code, X_actv_tbl(i).oprn_line_id);
1184 -- Bug #2673008 (JKB) Changed above.
1185 END LOOP;
1186 FOR i IN 1..X_rsrc_tbl.count LOOP
1187 IF X_rsrc_tbl(i).text_code IS NOT NULL THEN
1188 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_rsrc_tbl(i).text_code,
1189 'FM_TEXT_TBL_TL',
1190 'FM_TEXT_TBL_TL');
1191 populate_temp_text(X_rsrc_tbl(i).text_code,2);--BUG#3258592
1192 ELSE
1193 l_text_code := NULL;
1194 END IF;
1195 -- Bug #2673008 (JKB) Added call to copy_text above.
1196 INSERT INTO gmd_recipe_orgn_resources(recipe_id, organization_id, routingstep_id,
1197 oprn_line_id, resources, creation_date, created_by,
1198 last_updated_by, last_update_date, min_capacity,
1199 max_capacity, last_update_login, text_code, attribute1,
1200 attribute2, attribute3, attribute4, attribute5, attribute6,
1201 attribute7, attribute8, attribute9, attribute10,
1202 attribute11, attribute12, attribute13, attribute14,
1203 attribute15, attribute16, attribute17, attribute18,
1204 attribute19, attribute20, attribute21, attribute22,
1205 attribute23, attribute24, attribute25, attribute26,
1206 attribute27, attribute28, attribute29, attribute30,
1207 attribute_category, process_parameter_5,
1208 process_parameter_4, process_parameter_3,
1209 process_parameter_2, process_parameter_1, process_um,
1210 usage_uom, resource_usage, process_qty)
1211 VALUES (x_recipe_id, X_rsrc_tbl(i).organization_id,
1212 X_rsrc_tbl(i).routingstep_id, X_rsrc_tbl(i).oprn_line_id,
1213 X_rsrc_tbl(i).resources, SYSDATE, P_created_by,
1214 P_created_by, SYSDATE, X_rsrc_tbl(i).min_capacity,
1215 X_rsrc_tbl(i).max_capacity, P_login_id, l_text_code,
1216 -- Bug #2673008 (JKB) Changed above.
1217 X_rsrc_tbl(i).attribute1, X_rsrc_tbl(i).attribute2,
1218 X_rsrc_tbl(i).attribute3, X_rsrc_tbl(i).attribute4,
1219 X_rsrc_tbl(i).attribute5, X_rsrc_tbl(i).attribute6,
1220 X_rsrc_tbl(i).attribute7, X_rsrc_tbl(i).attribute8,
1221 X_rsrc_tbl(i).attribute9, X_rsrc_tbl(i).attribute10,
1222 X_rsrc_tbl(i).attribute11, X_rsrc_tbl(i).attribute12,
1223 X_rsrc_tbl(i).attribute13, X_rsrc_tbl(i).attribute14,
1224 X_rsrc_tbl(i).attribute15, X_rsrc_tbl(i).attribute16,
1225 X_rsrc_tbl(i).attribute17, X_rsrc_tbl(i).attribute18,
1226 X_rsrc_tbl(i).attribute19, X_rsrc_tbl(i).attribute20,
1227 X_rsrc_tbl(i).attribute21, X_rsrc_tbl(i).attribute22,
1228 X_rsrc_tbl(i).attribute23, X_rsrc_tbl(i).attribute24,
1229 X_rsrc_tbl(i).attribute25, X_rsrc_tbl(i).attribute26,
1230 X_rsrc_tbl(i).attribute27, X_rsrc_tbl(i).attribute28,
1231 X_rsrc_tbl(i).attribute29, X_rsrc_tbl(i).attribute30,
1232 X_rsrc_tbl(i).attribute_category,
1233 X_rsrc_tbl(i).process_parameter_5,
1234 X_rsrc_tbl(i).process_parameter_4,
1235 X_rsrc_tbl(i).process_parameter_3,
1236 X_rsrc_tbl(i).process_parameter_2,
1237 X_rsrc_tbl(i).process_parameter_1,
1238 X_rsrc_tbl(i).process_um, X_rsrc_tbl(i).usage_uom,
1239 X_rsrc_tbl(i).resource_usage, X_rsrc_tbl(i).process_qty);
1240 END LOOP;
1241 FOR i IN 1..X_stepmtl_tbl.count LOOP
1242 IF X_stepmtl_tbl(i).text_code IS NOT NULL THEN
1243 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_stepmtl_tbl(i).text_code,
1244 'FM_TEXT_TBL_TL',
1245 'FM_TEXT_TBL_TL');
1246 populate_temp_text(X_stepmtl_tbl(i).text_code,2);--BUG#3258592
1247 ELSE
1248 l_text_code := NULL;
1249 END IF;
1250 -- Bug #2673008 (JKB) Added call to copy_text above.
1251 INSERT INTO gmd_recipe_step_materials(recipe_id, formulaline_id,
1252 routingstep_id, text_code, creation_date, created_by,
1253 -- Bug #2673008 (JKB) Changed above.
1254 last_updated_by, last_update_date, last_update_login)
1255 VALUES (x_recipe_id, X_stepmtl_tbl(i).formulaline_id,
1256 X_stepmtl_tbl(i).routingstep_id, l_text_code, SYSDATE,
1257 P_created_by, P_created_by, SYSDATE, P_login_id);
1258 END LOOP;
1259
1260 IF gmo_setup_grp.is_gmo_enabled = 'Y' THEN
1261 -- If GMO is enabled, copy the new PI's from old entity to new entity
1262 OPEN Cur_get_old_recipe_det(p_recipe_id);
1263 FETCH Cur_get_old_recipe_det INTO l_formula_id, l_routing_id;
1264 CLOSE Cur_get_old_recipe_det;
1265
1266 IF X_hdr_rec.formula_id = l_formula_id THEN
1267 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'FORMULA',
1268 p_entity_id => l_formula_id,
1269 x_name_array => l_form_source_name_array,
1270 x_key_array => l_form_source_key_array,
1271 x_return_status => l_return_status);
1272
1273 FOR i IN 1..l_form_source_key_array.COUNT
1274 LOOP
1275 l_source_name_array(i) := l_form_source_name_array(i);
1276 l_source_key_array(i) := p_recipe_id|| '$' ||l_form_source_key_array(i);
1277 l_target_name_array(i) := l_form_source_name_array(i);
1278 l_target_key_array(i) := x_recipe_id|| '$' ||l_form_source_key_array(i);
1279 END LOOP;
1280 END IF;
1281
1282 IF (X_hdr_rec.routing_id IS NOT NULL) AND
1283 (X_hdr_rec.routing_id = l_routing_id) THEN
1284 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'ROUTING',
1285 p_entity_id => l_routing_id,
1286 x_name_array => l_rout_source_name_array,
1287 x_key_array => l_rout_source_key_array,
1288 x_return_status => l_return_status);
1289
1290 j := l_source_name_array.COUNT;
1291 FOR i IN 1..l_rout_source_key_array.COUNT
1292 LOOP
1293 j := j + 1;
1294 l_source_name_array(j) := l_rout_source_name_array(i);
1295 l_source_key_array(j) := p_recipe_id|| '$' ||l_rout_source_key_array(i);
1296 l_target_name_array(j) := l_rout_source_name_array(i);
1297 l_target_key_array(j) := x_recipe_id|| '$' ||l_rout_source_key_array(i);
1298 END LOOP;
1299 END IF;
1300
1301 GMD_PROCESS_INSTR_UTILS.Copy_Process_Instructions (
1302 p_source_name_array => l_source_name_array,
1303 p_source_key_array => l_source_key_array,
1304 p_target_name_array => l_target_name_array,
1305 p_target_key_array => l_target_key_array,
1306 x_return_status => l_return_status);
1307 END IF;
1308
1309 END create_recipe;
1310
1311 /*======================================================================
1312 -- PROCEDURE :
1313 -- create_formula
1314 --
1315 -- DESCRIPTION:
1316 -- This PL/SQL procedure is responsible for saving the
1317 -- new formula while versioning.
1318 --
1319 -- REQUIREMENTS
1320 --
1321 -- SYNOPSIS:
1322 -- create_formula(P_formula_id, X_formula_id);
1323 --
1324 -- HISTORY
1325 -- 31-Jan-2003 Jeff Baird Bug #2673008 Added call to copy_text API.
1326 -- 28-AUG-2003 Rameshwar BUG#3077938
1327 -- Added a call to procedure GMD_COMMON_VAL.calculate_total_qty to calculate the total quantity,
1328 -- and update the formula master with the total output quantity.
1329 -- 25-Nov-2003 Vipul Vaish BUG#3258592
1330 -- Added call to procedure populate_temp_text after GMA_EDITTEXT_PKG.Copy_Text
1331 -- function.
1332 --
1333 -- 07-07-2004 kkillams Bug 3738941, added new validation to copy the attachments.
1334 -- 02-20-2007 Thomas Added Auto_Product_Calc to be passed to the insert_row.
1335 --===================================================================== */
1336 PROCEDURE create_formula(p_formula_id IN NUMBER, x_formula_id OUT NOCOPY NUMBER) IS
1337 X_formula_vers NUMBER;
1338 X_row NUMBER := 0;
1339 l_rowid VARCHAR2(18);
1340 l_text_code NUMBER;
1341 --BEGIN BUG#3077938
1342 --Created new variables to retrieve the output quantity from GMD_COMMON_VAL.calculate_total_qty procedure
1343 x_return_status VARCHAR2(20);
1344 X_msg_cnt NUMBER;
1345 X_msg_dat VARCHAR2(100);
1346 X_status VARCHAR2(1);
1347 l_product_qty NUMBER;
1348 l_ing_qty NUMBER;
1349 l_uom mtl_units_of_measure.unit_of_measure%TYPE;
1350 --END BUG#3077938
1351
1352 -- Bug #2673008 (JKB) Added l_text_code above.
1353 CURSOR Cur_formula_id IS
1354 SELECT gem5_formula_id_s.NEXTVAL
1355 FROM FND_DUAL;
1356 CURSOR Cur_get_hdr IS
1357 SELECT *
1358 FROM fm_form_mst
1359 WHERE formula_id = p_formula_id;
1360 X_hdr_rec Cur_get_hdr%ROWTYPE;
1361 CURSOR Cur_formula_vers IS
1362 SELECT MAX(formula_vers) + 1
1363 FROM fm_form_mst
1364 WHERE formula_no = X_hdr_rec.formula_no;
1365
1366 CURSOR Cur_get_dtl IS
1367 SELECT *
1368 FROM fm_matl_dtl
1369 WHERE formula_id = p_formula_id;
1370
1371 --kkillams,bug 3738941
1372 CURSOR cur_form_att(cp_entity_name fnd_attached_documents.entity_name%TYPE,
1373 cp_pk1_value fnd_attached_documents.pk1_value%TYPE) IS
1374 SELECT 1
1375 FROM fnd_attached_documents fad
1376 WHERE fad.entity_name = cp_entity_name
1377 AND fad.pk1_value = cp_pk1_value;
1378
1379 l_formulaline_id fm_matl_dtl.formulaline_id%TYPE;
1380 l_dummy NUMBER;
1381
1382 TYPE detail_tab IS TABLE OF Cur_get_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
1383 X_dtl_tbl detail_tab;
1384
1385 l_msg_count NUMBER;
1386 l_msg_data VARCHAR2(2000);
1387 l_return_status VARCHAR2(10);
1388 l_gmo_enabled VARCHAR2(1);
1389 l_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1390 l_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1391 l_target_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1392 l_target_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1393
1394 BEGIN
1395 OPEN Cur_get_hdr;
1396 FETCH Cur_get_hdr INTO X_hdr_rec;
1397 CLOSE Cur_get_hdr;
1398
1399 FOR get_rec IN Cur_get_dtl LOOP
1400 X_row := X_row + 1;
1401 X_dtl_tbl(X_row) := get_rec;
1402 END LOOP;
1403
1404 /* Check if GMO is enabled */
1405 l_gmo_enabled := gmo_setup_grp.is_gmo_enabled;
1406 IF l_gmo_enabled = 'Y' THEN
1407 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'FORMULA',
1408 p_entity_id => p_formula_id,
1409 x_name_array => l_source_name_array,
1410 x_key_array => l_source_key_array,
1411 x_return_status => l_return_status);
1412 END IF;
1413
1414 ROLLBACK;
1415
1416 OPEN Cur_formula_vers;
1417 FETCH Cur_formula_vers INTO X_formula_vers;
1418 CLOSE Cur_formula_vers;
1419
1420 OPEN Cur_formula_id;
1421 FETCH Cur_formula_id INTO x_formula_id;
1422 CLOSE Cur_formula_id;
1423 IF X_hdr_rec.TEXT_CODE IS NOT NULL THEN
1424 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_hdr_rec.TEXT_CODE,
1425 'FM_TEXT_TBL_TL',
1426 'FM_TEXT_TBL_TL');
1427 populate_temp_text(X_hdr_rec.TEXT_CODE,2);--BUG#3258592
1428 ELSE
1429 l_text_code := NULL;
1430 END IF;
1431 -- Bug #2673008 (JKB) Added call to copy_text above.
1432 FM_FORM_MST_MLS.INSERT_ROW(
1433 X_ROWID => l_rowid,
1434 X_FORMULA_ID => X_formula_id,
1435 X_MASTER_FORMULA_ID => Null,
1436 X_OWNER_ORGANIZATION_ID => x_hdr_rec.OWNER_ORGANIZATION_ID,
1437 X_TOTAL_INPUT_QTY => 0,
1438 X_TOTAL_OUTPUT_QTY => 0,
1439 X_YIELD_UOM => NULL,
1440 X_FORMULA_STATUS => 100,
1441 X_OWNER_ID => X_hdr_rec.created_by,
1442 X_PROJECT_ID => NULL,
1443 X_TEXT_CODE => l_text_code,
1444 X_DELETE_MARK => X_hdr_rec.DELETE_MARK,
1445 X_FORMULA_NO => X_hdr_rec.formula_no,
1446 X_FORMULA_VERS => X_formula_vers,
1447 X_FORMULA_TYPE => X_hdr_rec.FORMULA_TYPE,
1448 X_IN_USE => 0,
1449 X_INACTIVE_IND => 0,
1450 X_SCALE_TYPE => X_hdr_rec.SCALE_TYPE,
1451 X_FORMULA_CLASS => X_hdr_rec.FORMULA_CLASS,
1452 X_FMCONTROL_CLASS => X_hdr_rec.FMCONTROL_CLASS,
1453 X_ATTRIBUTE_CATEGORY => X_hdr_rec.ATTRIBUTE_CATEGORY,
1454 X_ATTRIBUTE1 => X_hdr_rec.ATTRIBUTE1,
1455 X_ATTRIBUTE2 => X_hdr_rec.ATTRIBUTE2,
1456 X_ATTRIBUTE3 => X_hdr_rec.ATTRIBUTE3,
1457 X_ATTRIBUTE4 => X_hdr_rec.ATTRIBUTE4,
1458 X_ATTRIBUTE5 => X_hdr_rec.ATTRIBUTE5,
1459 X_ATTRIBUTE6 => X_hdr_rec.ATTRIBUTE6,
1460 X_ATTRIBUTE7 => X_hdr_rec.ATTRIBUTE7,
1461 X_ATTRIBUTE8 => X_hdr_rec.ATTRIBUTE8,
1462 X_ATTRIBUTE9 => X_hdr_rec.ATTRIBUTE9,
1463 X_ATTRIBUTE10 => X_hdr_rec.ATTRIBUTE10,
1464 X_ATTRIBUTE11 => X_hdr_rec.ATTRIBUTE11,
1465 X_ATTRIBUTE12 => X_hdr_rec.ATTRIBUTE12,
1466 X_ATTRIBUTE13 => X_hdr_rec.ATTRIBUTE13,
1467 X_ATTRIBUTE14 => X_hdr_rec.ATTRIBUTE14,
1468 X_ATTRIBUTE15 => X_hdr_rec.ATTRIBUTE15,
1469 X_ATTRIBUTE16 => X_hdr_rec.ATTRIBUTE16,
1470 X_ATTRIBUTE17 => X_hdr_rec.ATTRIBUTE17,
1471 X_ATTRIBUTE18 => X_hdr_rec.ATTRIBUTE18,
1472 X_ATTRIBUTE19 => X_hdr_rec.ATTRIBUTE19,
1473 X_ATTRIBUTE20 => X_hdr_rec.ATTRIBUTE20,
1474 X_ATTRIBUTE21 => X_hdr_rec.ATTRIBUTE21,
1475 X_ATTRIBUTE22 => X_hdr_rec.ATTRIBUTE22,
1476 X_ATTRIBUTE23 => X_hdr_rec.ATTRIBUTE23,
1477 X_ATTRIBUTE24 => X_hdr_rec.ATTRIBUTE24,
1478 X_ATTRIBUTE25 => X_hdr_rec.ATTRIBUTE25,
1479 X_ATTRIBUTE26 => X_hdr_rec.ATTRIBUTE26,
1480 X_ATTRIBUTE27 => X_hdr_rec.ATTRIBUTE27,
1481 X_ATTRIBUTE28 => X_hdr_rec.ATTRIBUTE28,
1482 X_ATTRIBUTE29 => X_hdr_rec.ATTRIBUTE29,
1483 X_ATTRIBUTE30 => X_hdr_rec.ATTRIBUTE30,
1484 X_FORMULA_DESC1 => X_hdr_rec.FORMULA_DESC1,
1485 X_FORMULA_DESC2 => X_hdr_rec.FORMULA_DESC2,
1486 X_CREATION_DATE => SYSDATE,
1487 X_CREATED_BY => P_created_by,
1488 X_LAST_UPDATE_DATE => SYSDATE,
1489 X_LAST_UPDATED_BY => P_created_by,
1490 X_LAST_UPDATE_LOGIN => P_login_id,
1491 X_AUTO_PRODUCT_CALC => X_hdr_rec.auto_product_calc);
1492
1493 --kkillams, Bug 3738941
1494 --Added following validation to copy the attachments
1495 OPEN cur_form_att('FM_FORM_MST_B',to_char(p_formula_id));
1496 FETCH cur_form_att INTO l_dummy;
1497 IF cur_form_att%FOUND THEN
1498 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
1499 X_from_entity_name => 'FM_FORM_MST_B',
1500 X_from_pk1_value => TO_CHAR(p_formula_id),
1501 X_from_pk2_value => NULL,
1502 X_from_pk3_value => NULL,
1503 X_from_pk4_value => NULL,
1504 X_from_pk5_value => NULL,
1505 X_to_entity_name => 'FM_FORM_MST_B',
1506 X_to_pk1_value => TO_CHAR(X_formula_id),
1507 X_to_pk2_value => NULL,
1508 X_to_pk3_value => NULL,
1509 X_to_pk4_value => NULL,
1510 X_to_pk5_value => NULL,
1511 X_created_by => FND_GLOBAL.USER_ID,
1512 X_last_update_login => FND_GLOBAL.LOGIN_ID,
1513 X_program_application_id => NULL,
1514 X_program_id => NULL,
1515 X_request_id => NULL );
1516 END IF;
1517 CLOSE cur_form_att;
1518
1519
1520 FOR i IN 1..X_dtl_tbl.count LOOP
1521 IF X_dtl_tbl(i).text_code IS NOT NULL THEN
1522 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_dtl_tbl(i).text_code,
1523 'FM_TEXT_TBL_TL',
1524 'FM_TEXT_TBL_TL');
1525 populate_temp_text(X_dtl_tbl(i).text_code,2);--BUG#3258592
1526 ELSE
1527 l_text_code := NULL;
1528 END IF;
1529 --
1530 -- Bug #2673008 (JKB) Added call to copy_text above.
1531 INSERT INTO fm_matl_dtl(formulaline_id, formula_id, line_type, line_no,
1532 inventory_item_id, qty, detail_uom,revision, release_type, scrap_factor,
1533 scale_type, cost_alloc, phantom_type, rework_type, text_code,
1534 organization_id,last_updated_by, created_by, last_update_date, creation_date,
1535 last_update_login, attribute1, attribute2, attribute3,
1536 attribute4, attribute5, attribute6, attribute7, attribute8,
1537 attribute9, attribute10, attribute11, attribute12,
1538 attribute13, attribute14, attribute15, attribute16,
1539 attribute17, attribute18, attribute19, attribute20,
1540 attribute21, attribute22, attribute23, attribute24,
1541 attribute25, attribute26, attribute27, attribute28,
1542 attribute29, attribute30, attribute_category, tpformula_id,
1543 scale_multiple, contribute_yield_ind, scale_uom,
1544 contribute_step_qty_ind, scale_rounding_variance)
1545 VALUES (gem5_formulaline_id_s.NEXTVAL, X_formula_id,
1546 X_dtl_tbl(i).line_type, X_dtl_tbl(i).line_no,
1547 X_dtl_tbl(i).inventory_item_id, X_dtl_tbl(i).qty,
1548 X_dtl_tbl(i).detail_uom, X_dtl_tbl(i).revision, X_dtl_tbl(i).release_type,
1549 X_dtl_tbl(i).scrap_factor, X_dtl_tbl(i).scale_type,
1550 X_dtl_tbl(i).cost_alloc, X_dtl_tbl(i).phantom_type,
1551 X_dtl_tbl(i).rework_type, l_text_code,X_dtl_tbl(i).organization_id,
1552 -- Bug #2673008 (JKB) Changed above.
1553 P_created_by, P_created_by, SYSDATE, SYSDATE, P_login_id,
1554 X_dtl_tbl(i).attribute1, X_dtl_tbl(i).attribute2,
1555 X_dtl_tbl(i).attribute3, X_dtl_tbl(i).attribute4,
1556 X_dtl_tbl(i).attribute5, X_dtl_tbl(i).attribute6,
1557 X_dtl_tbl(i).attribute7, X_dtl_tbl(i).attribute8,
1558 X_dtl_tbl(i).attribute9, X_dtl_tbl(i).attribute10,
1559 X_dtl_tbl(i).attribute11, X_dtl_tbl(i).attribute12,
1560 X_dtl_tbl(i).attribute13, X_dtl_tbl(i).attribute14,
1561 X_dtl_tbl(i).attribute15, X_dtl_tbl(i).attribute16,
1562 X_dtl_tbl(i).attribute17, X_dtl_tbl(i).attribute18,
1563 X_dtl_tbl(i).attribute19, X_dtl_tbl(i).attribute20,
1564 X_dtl_tbl(i).attribute21, X_dtl_tbl(i).attribute22,
1565 X_dtl_tbl(i).attribute23, X_dtl_tbl(i).attribute24,
1566 X_dtl_tbl(i).attribute25, X_dtl_tbl(i).attribute26,
1567 X_dtl_tbl(i).attribute27, X_dtl_tbl(i).attribute28,
1568 X_dtl_tbl(i).attribute29, X_dtl_tbl(i).attribute30,
1569 X_dtl_tbl(i).attribute_category, X_dtl_tbl(i).tpformula_id,
1570 X_dtl_tbl(i).scale_multiple,
1571 X_dtl_tbl(i).contribute_yield_ind, X_dtl_tbl(i).scale_uom,
1572 X_dtl_tbl(i).contribute_step_qty_ind,
1573 X_dtl_tbl(i).scale_rounding_variance)
1574 RETURNING formulaline_id INTO l_formulaline_id;
1575 --BEGIN BUG#3077938
1576 /* Added a new procedure to calculate the total quantity and update the
1577 formula master with the total output quantity. */
1578
1579 GMD_COMMON_VAL.calculate_total_qty(
1580 formula_id => X_formula_id,
1581 x_product_qty => l_product_qty ,
1582 x_ingredient_qty => l_ing_qty ,
1583 x_uom => l_uom ,
1584 x_return_status => x_return_status ,
1585 x_msg_count => X_msg_cnt ,
1586 x_msg_data => x_msg_dat );
1587
1588
1589 /* Update formula header table with TOQ and TIQ */
1590 UPDATE fm_form_mst_b
1591 SET total_output_qty = l_product_qty,
1592 total_input_qty = l_ing_qty,
1593 yield_uom = l_uom
1594 WHERE formula_id = X_formula_id;
1595 --END BUG#3077938
1596 --kkillams, Bug 3738941
1597 --Added following validation to copy the attachments
1598 OPEN cur_form_att('FM_MATL_DTL',to_char(X_dtl_tbl(i).formulaline_id));
1599 FETCH cur_form_att INTO l_dummy;
1600 IF cur_form_att%FOUND THEN
1601 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
1602 X_from_entity_name => 'FM_MATL_DTL',
1603 X_from_pk1_value => TO_CHAR(X_dtl_tbl(i).formulaline_id),
1604 X_from_pk2_value => NULL,
1605 X_from_pk3_value => NULL,
1606 X_from_pk4_value => NULL,
1607 X_from_pk5_value => NULL,
1608 X_to_entity_name => 'FM_MATL_DTL',
1609 X_to_pk1_value => TO_CHAR(l_formulaline_id),
1610 X_to_pk2_value => NULL,
1611 X_to_pk3_value => NULL,
1612 X_to_pk4_value => NULL,
1613 X_to_pk5_value => NULL,
1614 X_created_by => FND_GLOBAL.USER_ID,
1615 X_last_update_login => FND_GLOBAL.LOGIN_ID,
1616 X_program_application_id => NULL,
1617 X_program_id => NULL,
1618 X_request_id => NULL );
1619 END IF;
1620 CLOSE cur_form_att;
1621 END LOOP;
1622
1623 -- If GMO is enabled, copy the new PI's from old entity to new entity
1624 IF l_gmo_enabled = 'Y' THEN
1625 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'FORMULA',
1626 p_entity_id => x_formula_id,
1627 x_name_array => l_target_name_array,
1628 x_key_array => l_target_key_array,
1629 x_return_status => l_return_status);
1630
1631 GMD_PROCESS_INSTR_UTILS.Copy_Process_Instructions (
1632 p_source_name_array => l_source_name_array,
1633 p_source_key_array => l_source_key_array,
1634 p_target_name_array => l_target_name_array,
1635 p_target_key_array => l_target_key_array,
1636 x_return_status => l_return_status);
1637 END IF;
1638
1639 END create_formula;
1640
1641 PROCEDURE create_substitution(p_substitution_id IN NUMBER, x_substitution_id OUT NOCOPY NUMBER) AS
1642 -- Bug number 4252212
1643 /*======================================================================
1644 -- PROCEDURE :
1645 -- create_substitution
1646 --
1647 -- DESCRIPTION:
1648 -- This PL/SQL procedure is responsible for saving the
1649 -- new substitution while versioning.
1650 --
1651 -- REQUIREMENTS
1652 --
1653 -- SYNOPSIS:
1654 -- create_substitution(p_substitution_id, x_substitution_id);
1655 --
1656 -- HISTORY
1657 --
1658 --===================================================================== */
1659 l_rowid VARCHAR2(18);
1660 X_recipe_vers NUMBER;
1661 X_row NUMBER;
1662 l_substitution_line_id gmd_item_substitution_dtl.substitution_line_id%type;
1663 l_formula_substitution_id gmd_formula_substitution.formula_substitution_id%type;
1664
1665 CURSOR cur_hdr_s IS
1666 SELECT gmd_item_substitution_hdr_s.nextval
1667 FROM DUAL;
1668 CURSOR cur_dtl_s IS
1669 SELECT gmd_item_substitution_dtl_s.nextval
1670 FROM DUAL;
1671 CURSOR cur_frsb_s IS
1672 SELECT gmd_formula_substitution_s.nextval
1673 FROM DUAL;
1674
1675 CURSOR Cur_get_hdr IS
1676 SELECT *
1677 FROM GMD_ITEM_SUBSTITUTION_HDR_VL
1678 WHERE substitution_id = p_substitution_id;
1679 X_hdr_rec Cur_get_hdr%ROWTYPE;
1680
1681 CURSOR cur_hdr_ver IS
1682 SELECT MAX(SUBSTITUTION_VERSION) + 1 FROM GMD_ITEM_SUBSTITUTION_HDR_B
1683 WHERE SUBSTITUTION_NAME = X_hdr_rec.SUBSTITUTION_NAME;
1684 CURSOR cur_hdr_per IS
1685 SELECT MAX(preference) + 1 FROM GMD_ITEM_SUBSTITUTION_HDR_B
1686 WHERE ORIGINAL_INVENTORY_ITEM_ID = X_hdr_rec.ORIGINAL_INVENTORY_ITEM_ID
1687 AND OWNER_ORGANIZATION_ID = X_hdr_rec.OWNER_ORGANIZATION_ID;
1688
1689 CURSOR Cur_get_det IS
1690 SELECT *
1691 FROM GMD_ITEM_SUBSTITUTION_DTL
1692 WHERE substitution_id = p_substitution_id;
1693
1694 TYPE get_det IS TABLE OF Cur_get_det%ROWTYPE INDEX BY BINARY_INTEGER;
1695 X_get_det_tbl get_det;
1696
1697 CURSOR Cur_formula_sub IS
1698 SELECT *
1699 FROM GMD_FORMULA_SUBSTITUTION
1700 WHERE substitution_id = p_substitution_id;
1701
1702 TYPE formula_sub IS TABLE OF Cur_formula_sub%ROWTYPE INDEX BY BINARY_INTEGER;
1703 X_formula_sub_tbl formula_sub;
1704 BEGIN
1705 OPEN Cur_get_hdr;
1706 FETCH Cur_get_hdr into X_hdr_rec;
1707 CLOSE Cur_get_hdr;
1708
1709 X_row := 0;
1710 FOR get_rec IN Cur_get_det LOOP
1711 X_row := X_row + 1;
1712 X_get_det_tbl(X_row) := get_rec;
1713 END LOOP;
1714
1715 X_row := 0;
1716 FOR get_form IN Cur_formula_sub LOOP
1717 X_row := X_row + 1;
1718 X_formula_sub_tbl(X_row) := get_form;
1719 END LOOP;
1720
1721 ROLLBACK;
1722
1723 l_rowid :=NULL;
1724 OPEN cur_hdr_s;
1725 FETCH cur_hdr_s INTO x_substitution_id;
1726 CLOSE cur_hdr_s;
1727 OPEN cur_hdr_ver;
1728 FETCH cur_hdr_ver INTO X_hdr_rec.substitution_version;
1729 CLOSE cur_hdr_ver;
1730 OPEN cur_hdr_per;
1731 FETCH cur_hdr_per INTO X_hdr_rec.PREFERENCE;
1732 CLOSE cur_hdr_per;
1733
1734 GMD_ITEM_SUBSTITUTION_HDR_PKG.INSERT_ROW(
1735 X_ROWID => l_rowid,
1736 X_SUBSTITUTION_ID => x_substitution_id,
1737 X_SUBSTITUTION_NAME => X_hdr_rec.SUBSTITUTION_NAME,
1738 X_SUBSTITUTION_VERSION => X_hdr_rec.SUBSTITUTION_VERSION,
1739 X_SUBSTITUTION_STATUS => 100,
1740 X_ORIGINAL_INVENTORY_ITEM_ID => X_hdr_rec.ORIGINAL_INVENTORY_ITEM_ID,
1741 X_ORIGINAL_UOM => X_hdr_rec.ORIGINAL_UOM,
1742 X_ORIGINAL_QTY => X_hdr_rec.ORIGINAL_QTY,
1743 X_PREFERENCE => X_hdr_rec.PREFERENCE,
1744 X_START_DATE => X_hdr_rec.START_DATE,
1745 X_END_DATE => X_hdr_rec.END_DATE,
1746 X_OWNER_ORGANIZATION_ID => X_hdr_rec.OWNER_ORGANIZATION_ID,
1747 X_REPLACEMENT_UOM_TYPE => X_hdr_rec.REPLACEMENT_UOM_TYPE,
1748 X_ATTRIBUTE_CATEGORY => X_hdr_rec.ATTRIBUTE_CATEGORY,
1749 X_ATTRIBUTE1 => X_hdr_rec.ATTRIBUTE1,
1750 X_ATTRIBUTE2 => X_hdr_rec.ATTRIBUTE2,
1751 X_ATTRIBUTE3 => X_hdr_rec.ATTRIBUTE3,
1752 X_ATTRIBUTE4 => X_hdr_rec.ATTRIBUTE4,
1753 X_ATTRIBUTE5 => X_hdr_rec.ATTRIBUTE5,
1754 X_ATTRIBUTE6 => X_hdr_rec.ATTRIBUTE6,
1755 X_ATTRIBUTE7 => X_hdr_rec.ATTRIBUTE7,
1756 X_ATTRIBUTE8 => X_hdr_rec.ATTRIBUTE8,
1757 X_ATTRIBUTE9 => X_hdr_rec.ATTRIBUTE9,
1758 X_ATTRIBUTE10 => X_hdr_rec.ATTRIBUTE10,
1759 X_ATTRIBUTE11 => X_hdr_rec.ATTRIBUTE11,
1760 X_ATTRIBUTE12 => X_hdr_rec.ATTRIBUTE12,
1761 X_ATTRIBUTE13 => X_hdr_rec.ATTRIBUTE13,
1762 X_ATTRIBUTE14 => X_hdr_rec.ATTRIBUTE14,
1763 X_ATTRIBUTE15 => X_hdr_rec.ATTRIBUTE15,
1764 X_ATTRIBUTE16 => X_hdr_rec.ATTRIBUTE16,
1765 X_ATTRIBUTE17 => X_hdr_rec.ATTRIBUTE17,
1766 X_ATTRIBUTE18 => X_hdr_rec.ATTRIBUTE18,
1767 X_ATTRIBUTE19 => X_hdr_rec.ATTRIBUTE19,
1768 X_ATTRIBUTE20 => X_hdr_rec.ATTRIBUTE20,
1769 X_ATTRIBUTE21 => X_hdr_rec.ATTRIBUTE21,
1770 X_ATTRIBUTE22 => X_hdr_rec.ATTRIBUTE22,
1771 X_ATTRIBUTE23 => X_hdr_rec.ATTRIBUTE23,
1772 X_ATTRIBUTE24 => X_hdr_rec.ATTRIBUTE24,
1773 X_ATTRIBUTE25 => X_hdr_rec.ATTRIBUTE25,
1774 X_ATTRIBUTE26 => X_hdr_rec.ATTRIBUTE26,
1775 X_ATTRIBUTE27 => X_hdr_rec.ATTRIBUTE27,
1776 X_ATTRIBUTE28 => X_hdr_rec.ATTRIBUTE28,
1777 X_ATTRIBUTE29 => X_hdr_rec.ATTRIBUTE29,
1778 X_ATTRIBUTE30 => X_hdr_rec.ATTRIBUTE30,
1779 X_SUBSTITUTION_DESCRIPTION => X_hdr_rec.SUBSTITUTION_DESCRIPTION,
1780 X_CREATION_DATE => sysdate,
1781 X_CREATED_BY => P_created_by,
1782 X_LAST_UPDATE_DATE => sysdate,
1783 X_LAST_UPDATED_BY => P_created_by,
1784 X_LAST_UPDATE_LOGIN => P_login_id);
1785 IF X_formula_sub_tbl.last IS NOT NULL THEN
1786 FOR I IN 1 .. X_formula_sub_tbl.last
1787 LOOP
1788 OPEN cur_frsb_s;
1789 FETCH cur_frsb_s INTO l_formula_substitution_id;
1790 CLOSE cur_frsb_s;
1791 l_rowid :=NULL;
1792 GMD_FORMULA_SUBSTITUTION_PKG.INSERT_ROW(
1793 X_ROWID => l_rowid,
1794 X_FORMULA_SUBSTITUTION_ID => l_formula_substitution_id,
1795 X_SUBSTITUTION_ID => x_substitution_id,
1796 X_FORMULA_ID => X_formula_sub_tbl(i).FORMULA_ID,
1797 X_ASSOCIATED_FLAG => 'N',
1798 X_CREATION_DATE => sysdate,
1799 X_CREATED_BY => P_created_by,
1800 X_LAST_UPDATE_DATE => sysdate,
1801 X_LAST_UPDATED_BY => P_created_by,
1802 X_LAST_UPDATE_LOGIN => P_login_id);
1803 END LOOP;
1804 END IF;
1805 FOR I IN 1 .. X_get_det_tbl.last
1806 LOOP
1807 l_rowid :=NULL;
1808 OPEN cur_dtl_s;
1809 FETCH cur_dtl_s INTO l_substitution_line_id;
1810 CLOSE cur_dtl_s;
1811 GMD_ITEM_SUBSTITUTION_DTL_PKG.INSERT_ROW(
1812 X_ROWID => l_rowid,
1813 X_SUBSTITUTION_LINE_ID => l_substitution_line_id,
1814 X_SUBSTITUTION_ID => x_substitution_id,
1815 X_INVENTORY_ITEM_ID => X_get_det_tbl(i).INVENTORY_ITEM_ID,
1816 X_UNIT_QTY => X_get_det_tbl(i).UNIT_QTY,
1817 X_DETAIL_UOM => X_get_det_tbl(i).DETAIL_UOM,
1818 X_CREATION_DATE => sysdate,
1819 X_CREATED_BY => P_created_by,
1820 X_LAST_UPDATE_DATE => sysdate,
1821 X_LAST_UPDATED_BY => P_created_by,
1822 X_LAST_UPDATE_LOGIN => P_login_id);
1823 END LOOP;
1824 END create_substitution;
1825
1826 END gmd_version_control;