[Home] [Help]
PACKAGE BODY: APPS.GMD_SEARCH_REPLACE_VERS
Source
1 PACKAGE BODY gmd_search_replace_vers AS
2 /* $Header: GMDSREPB.pls 120.10 2009/03/23 17:36:49 rnalla ship $ */
3
4 G_PKG_NAME VARCHAR2(32);
5
6 /*======================================================================
7 -- PROCEDURE :
8 -- create_new_routing
9 --
10 -- DESCRIPTION:
11 -- This PL/SQL procedure is responsible for saving the
12 -- new routing while versioning.
13 --
14 -- REQUIREMENTS
15 --
16 -- SYNOPSIS:
17 -- create_new_routing( p_routing_id IN NUMBER,
18 p_Organization_id IN NUMBER,
19 p_effective_start_date IN VARCHAR2,
20 p_effective_end_date IN VARCHAR2,
21 p_inactive_ind IN NUMBER,
22 p_owner IN NUMBER,
23 p_old_operation IN NUMBER,
24 p_new_operation IN NUMBER,
25 p_routing_class IN VARCHAR2,
26 x_routing_id OUT NOCOPY NUMBER)
27 --
28 --
29 -- BUG 5197863 Validate the routing start and end dates
30 -- Bug 5493773 Removed NVL for the end date as the end date can be NULL
31 --===================================================================== */
32
33 PROCEDURE create_new_routing(p_routing_id IN NUMBER,
34 p_effective_start_date IN VARCHAR2,
35 p_effective_end_date IN VARCHAR2,
36 p_inactive_ind IN NUMBER,
37 p_owner IN NUMBER,
38 p_old_operation IN NUMBER,
39 p_new_operation IN NUMBER,
40 p_routing_class IN VARCHAR2,
41 x_routing_id OUT NOCOPY NUMBER) IS
42
43 X_routing_vers NUMBER;
44 X_row NUMBER := 0;
45
46 CURSOR Cur_routing_id IS
47 SELECT gem5_routing_id_s.NEXTVAL
48 FROM FND_DUAL;
49 CURSOR Cur_get_hdr IS
50 SELECT *
51 FROM fm_rout_hdr
52 WHERE routing_id = p_routing_id;
53 X_hdr_rec Cur_get_hdr%ROWTYPE;
54 CURSOR Cur_rout_vers IS
55 SELECT MAX(routing_vers) + 1
56 FROM fm_rout_hdr
57 WHERE routing_no = X_hdr_rec.routing_no;
58
59 CURSOR Cur_get_dtl IS
60 SELECT *
61 FROM fm_rout_dtl
62 WHERE routing_id = p_routing_id;
63 TYPE detail_tab IS TABLE OF Cur_get_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
64 X_dtl_tbl detail_tab;
65 X_return_status VARCHAR2(1);
66 X_message_count NUMBER;
67 X_message_list VARCHAR2(2000);
68 l_entity_status gmd_api_grp.status_rec_type;
69
70 --BUG 5197863 Added l_ret and VALIDATION_FAILURE
71 l_ret NUMBER;
72 VALIDATION_FAILURE EXCEPTION;
73
74 l_gmo_enabled VARCHAR2(1);
75 l_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
76 l_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
77 l_target_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
78 l_target_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
79
80 PRAGMA AUTONOMOUS_TRANSACTION;
81 BEGIN
82 OPEN Cur_get_hdr;
83 FETCH Cur_get_hdr INTO X_hdr_rec;
84 CLOSE Cur_get_hdr;
85
86 -- BUG 5197863 GMD_ROUTINGS_PVT.Validate_dates validates the start and end dates
87 -- and also validates the dates with operation dates.
88
89 -- Bug# 5493773 Removed NVL for the end date as the end date can be NULL
90 l_ret := GMD_ROUTINGS_PVT.Validate_dates(p_routing_id,
91 NVL( FND_DATE.canonical_to_date(p_effective_start_date) ,X_hdr_rec.effective_start_date ),
92 FND_DATE.canonical_to_date(p_effective_end_date) );
93
94 IF l_ret < 0 THEN
95 RAISE VALIDATION_FAILURE ;
96 END IF;
97
98 FOR get_rec IN Cur_get_dtl LOOP
99 X_row := X_row + 1;
100 X_dtl_tbl(X_row) := get_rec;
101 END LOOP;
102
103 OPEN Cur_rout_vers;
104 FETCH Cur_rout_vers INTO X_routing_vers;
105 CLOSE Cur_rout_vers;
106
107 OPEN Cur_routing_id;
108 FETCH Cur_routing_id INTO x_routing_id;
109 CLOSE Cur_routing_id;
110
111
112 /* Check if GMO is enabled */
113 l_gmo_enabled := gmo_setup_grp.is_gmo_enabled;
114 IF l_gmo_enabled = 'Y' THEN
115 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'ROUTING',
116 p_entity_id => p_routing_id,
117 x_name_array => l_source_name_array,
118 x_key_array => l_source_key_array,
119 x_return_status => x_return_status);
120 END IF;
121
122 /* Insert header record */
123 -- BUG 5197863 Added NVL to effective end date
124 GMD_ROUTINGS_PKG.INSERT_ROW(
125 X_ROWID => x_hdr_rec.ROW_ID,
126 X_ROUTING_ID => x_routing_id,
127 X_owner_organization_id => x_hdr_rec.owner_organization_id,
128 X_ROUTING_NO => x_hdr_rec.ROUTING_NO,
129 X_ROUTING_VERS => X_routing_vers,
130 X_ROUTING_CLASS => NVL(p_routing_class, x_hdr_rec.routing_class), -- 4116557
131 X_ROUTING_QTY => x_hdr_rec.ROUTING_QTY,
132 X_ROUTING_UOM => x_hdr_rec.ROUTING_UOM,
133 X_DELETE_MARK => 0,
134 X_TEXT_CODE => x_hdr_rec.TEXT_CODE,
135 X_INACTIVE_IND => 0,
136 X_ENFORCE_STEP_DEPENDENCY => x_hdr_rec.enforce_step_dependency,
137 X_IN_USE => 0,
138 X_CONTIGUOUS_IND => x_hdr_rec.CONTIGUOUS_IND,
139 X_ATTRIBUTE1 => x_hdr_rec.ATTRIBUTE1,
140 X_ATTRIBUTE2 => x_hdr_rec.ATTRIBUTE2,
141 X_ATTRIBUTE3 => x_hdr_rec.ATTRIBUTE3,
142 X_ATTRIBUTE4 => x_hdr_rec.ATTRIBUTE4,
143 X_ATTRIBUTE5 => x_hdr_rec.ATTRIBUTE5,
144 X_ATTRIBUTE6 => x_hdr_rec.ATTRIBUTE6,
145 X_ATTRIBUTE7 => x_hdr_rec.ATTRIBUTE7,
146 X_ATTRIBUTE8 => x_hdr_rec.ATTRIBUTE8,
147 X_ATTRIBUTE9 => x_hdr_rec.ATTRIBUTE9,
148 X_ATTRIBUTE10 => x_hdr_rec.ATTRIBUTE10,
149 X_ATTRIBUTE11 => x_hdr_rec.ATTRIBUTE11,
150 X_ATTRIBUTE12 => x_hdr_rec.ATTRIBUTE12,
151 X_ATTRIBUTE13 => x_hdr_rec.ATTRIBUTE13,
152 X_ATTRIBUTE14 => x_hdr_rec.ATTRIBUTE14,
153 X_ATTRIBUTE15 => x_hdr_rec.ATTRIBUTE15,
154 X_ATTRIBUTE16 => x_hdr_rec.ATTRIBUTE16,
155 X_ATTRIBUTE17 => x_hdr_rec.ATTRIBUTE17,
156 X_ATTRIBUTE18 => x_hdr_rec.ATTRIBUTE18,
157 X_ATTRIBUTE19 => x_hdr_rec.ATTRIBUTE19,
158 X_ATTRIBUTE20 => x_hdr_rec.ATTRIBUTE20,
159 X_ATTRIBUTE21 => x_hdr_rec.ATTRIBUTE21,
160 X_ATTRIBUTE22 => x_hdr_rec.ATTRIBUTE22,
161 X_ATTRIBUTE23 => x_hdr_rec.ATTRIBUTE23,
162 X_ATTRIBUTE24 => x_hdr_rec.ATTRIBUTE24,
163 X_ATTRIBUTE25 => x_hdr_rec.ATTRIBUTE25,
164 X_ATTRIBUTE26 => x_hdr_rec.ATTRIBUTE26,
165 X_ATTRIBUTE27 => x_hdr_rec.ATTRIBUTE27,
166 X_ATTRIBUTE28 => x_hdr_rec.ATTRIBUTE28,
167 X_ATTRIBUTE29 => x_hdr_rec.ATTRIBUTE29,
168 X_ATTRIBUTE30 => x_hdr_rec.ATTRIBUTE30,
169 X_ATTRIBUTE_CATEGORY => x_hdr_rec.ATTRIBUTE_CATEGORY,
170 X_EFFECTIVE_START_DATE => NVL( FND_DATE.canonical_to_date(p_effective_start_date) ,X_hdr_rec.effective_start_date ),
171 X_EFFECTIVE_END_DATE => NVL(FND_DATE.canonical_to_date (p_effective_end_date) ,X_hdr_rec.effective_end_date ),
172 X_OWNER_ID => NVL(p_owner,X_hdr_rec.owner_id),
173 X_PROJECT_ID => x_hdr_rec.PROJECT_ID,
174 X_PROCESS_LOSS => x_hdr_rec.PROCESS_LOSS,
175 X_ROUTING_STATUS => 100,
176 X_ROUTING_DESC => x_hdr_rec.ROUTING_DESC,
177 X_CREATION_DATE => SYSDATE,
178 X_CREATED_BY => P_created_by,
179 X_LAST_UPDATE_DATE => SYSDATE,
180 X_LAST_UPDATED_BY => P_login_id,
181 X_LAST_UPDATE_LOGIN => P_login_id);
182
183
184 /* Insert detail records */
185 FOR i IN 1..X_dtl_tbl.count LOOP
186 INSERT INTO fm_rout_dtl
187 (routing_id, routingstep_no, routingstep_id, oprn_id, step_qty, steprelease_type,
188 text_code, creation_date, created_by, last_update_login, last_update_date,
189 last_updated_by, attribute1, attribute2, attribute3, attribute4, attribute5,
190 attribute6, attribute7, attribute8, attribute9, attribute10,
191 attribute11, attribute12, attribute13, attribute14, attribute15,
192 attribute16, attribute17, attribute18, attribute19, attribute20,
193 attribute21, attribute22, attribute23, attribute24, attribute25,
194 attribute26, attribute27, attribute28, attribute29, attribute30,
195 attribute_category)
196 VALUES (x_routing_id, X_dtl_tbl(i).routingstep_no, gem5_routingstep_id_s.NEXTVAL,
197 DECODE(X_dtl_tbl(i).oprn_id,p_old_operation,p_new_operation,X_dtl_tbl(i).oprn_id),
198 X_dtl_tbl(i).step_qty, X_dtl_tbl(i).steprelease_type,
199 X_dtl_tbl(i).text_code, SYSDATE, P_created_by, P_login_id, SYSDATE, P_created_by,
200 X_dtl_tbl(i).attribute1, X_dtl_tbl(i).attribute2, X_dtl_tbl(i).attribute3,
201 X_dtl_tbl(i).attribute4, X_dtl_tbl(i).attribute5, X_dtl_tbl(i).attribute6,
202 X_dtl_tbl(i).attribute7, X_dtl_tbl(i).attribute8, X_dtl_tbl(i).attribute9,
203 X_dtl_tbl(i).attribute10, X_dtl_tbl(i).attribute11, X_dtl_tbl(i).attribute12,
204 X_dtl_tbl(i).attribute13, X_dtl_tbl(i).attribute14, X_dtl_tbl(i).attribute15,
205 X_dtl_tbl(i).attribute16, X_dtl_tbl(i).attribute17, X_dtl_tbl(i).attribute18,
206 X_dtl_tbl(i).attribute19, X_dtl_tbl(i).attribute20, X_dtl_tbl(i).attribute21,
207 X_dtl_tbl(i).attribute22, X_dtl_tbl(i).attribute23, X_dtl_tbl(i).attribute24,
208 X_dtl_tbl(i).attribute25, X_dtl_tbl(i).attribute26, X_dtl_tbl(i).attribute27,
209 X_dtl_tbl(i).attribute28, X_dtl_tbl(i).attribute29, X_dtl_tbl(i).attribute30,
210 X_dtl_tbl(i).attribute_category);
211 END LOOP;
212
213 -- If GMO is enabled, copy the new PI's from old entity to new entity
214 IF l_gmo_enabled = 'Y' THEN
215 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'ROUTING',
216 p_entity_id => x_routing_id,
217 x_name_array => l_target_name_array,
218 x_key_array => l_target_key_array,
219 x_return_status => x_return_status);
220
221 GMD_PROCESS_INSTR_UTILS.Copy_Process_Instructions (
222 p_source_name_array => l_source_name_array,
223 p_source_key_array => l_source_key_array,
224 p_target_name_array => l_target_name_array,
225 p_target_key_array => l_target_key_array,
226 x_return_status => x_return_status);
227 END IF;
228
229 COMMIT;
230
231 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
232 gmd_api_grp.get_status_details (V_entity_type => 'ROUTING',
233 V_orgn_id => x_hdr_rec.owner_organization_id,
234 X_entity_status => l_entity_status);
235 --Add this code after the call to gmd_routings_pkg.insert_row.
236 IF (l_entity_status.entity_status <> 100) THEN
237 Gmd_status_pub.modify_status ( p_api_version => 1
238 , p_init_msg_list => TRUE
239 , p_entity_name =>'ROUTING'
240 , p_entity_id => x_routing_id
241 , p_entity_no => NULL
242 , p_entity_version => NULL
243 , p_to_status => l_entity_status.entity_status
244 , p_ignore_flag => FALSE
245 , x_message_count => x_message_count
246 , x_message_list => x_message_list
247 , x_return_status => X_return_status);
248 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
249 FND_MSG_PUB.count_and_get (p_count => x_message_count
250 ,p_encoded => FND_API.g_false
251 ,p_data => x_message_list);
252 X_message_list := FND_MSG_PUB.get (p_msg_index => X_message_count
253 ,p_encoded => 'F');
254 FND_FILE.PUT(FND_FILE.LOG,X_message_list);
255 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
256 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
257 END IF; --l_entity_status.entity_status <> 100
258 COMMIT;
259 -- BUG 5197863 handle the exception.
260 EXCEPTION WHEN
261 VALIDATION_FAILURE THEN
262 x_routing_id := NULL;
263 END create_new_routing;
264
265 /*======================================================================
266 -- PROCEDURE :
267 -- create_new_operation
268 --
269 -- DESCRIPTION:
270 -- This PL/SQL procedure is responsible for saving the
271 -- new operation while versioning.
272 --
273 -- REQUIREMENTS
274 --
275 -- SYNOPSIS:
276 -- create_new_operation(p_oprn_id IN NUMBER,
277 p_old_activity IN VARCHAR2,
278 p_activity IN VARCHAR2,
279 p_effective_start_date IN VARCHAR2,
280 p_effective_end_date IN VARCHAR2,
281 p_operation_class IN VARCHAR2,
282 p_inactive_ind IN NUMBER,
283 p_old_resource IN VARCHAR2,
284 p_resource IN VARCHAR2,
285 x_oprn_id OUT NOCOPY NUMBER) IS
286 --
287 --
288 --===================================================================== */
289 PROCEDURE create_new_operation(p_oprn_id IN NUMBER,
290 p_old_activity IN VARCHAR2,
291 p_activity IN VARCHAR2,
292 p_effective_start_date IN VARCHAR2,
293 p_effective_end_date IN VARCHAR2,
294 p_operation_class IN VARCHAR2,
295 p_inactive_ind IN NUMBER,
296 p_old_resource IN VARCHAR2,
297 p_resource IN VARCHAR2,
298 x_oprn_id OUT NOCOPY NUMBER) IS
299 CURSOR Cur_gen_oprn_id IS
300 SELECT GEM5_OPRN_ID_S.NEXTVAL
301 FROM FND_DUAL;
302 CURSOR Cur_gen_oprnline_id IS
303 SELECT GEM5_OPRNLINE_ID_S.NEXTVAL
304 FROM FND_DUAL;
305
306 CURSOR Cur_get_hdr IS
307 SELECT *
308 FROM gmd_operations_vl
309 WHERE oprn_id = p_oprn_id;
310 X_hdr_rec Cur_get_hdr%ROWTYPE;
311 CURSOR Cur_get_vers IS
312 SELECT MAX(oprn_vers) + 1
313 FROM gmd_operations_vl
314 WHERE oprn_no = X_hdr_rec.oprn_no;
315
316 CURSOR Cur_get_actv IS
317 SELECT *
318 FROM gmd_operation_activities
319 WHERE oprn_id = p_oprn_id;
320 TYPE actv_tab IS TABLE OF Cur_get_actv%ROWTYPE INDEX BY BINARY_INTEGER;
321 X_actv_tbl actv_tab;
322
323 CURSOR Cur_get_rsrc(V_oprn_line_id NUMBER) IS
324 SELECT *
325 FROM gmd_operation_resources
326 WHERE oprn_line_id = V_oprn_line_id;
327 TYPE rsrc_tab IS TABLE OF Cur_get_rsrc%ROWTYPE INDEX BY BINARY_INTEGER;
328 X_rsrc_tbl rsrc_tab;
329
330 X_oprn_vers NUMBER;
331 X_oprn_line_id NUMBER;
332 X_row NUMBER := 0;
333 X_rsrc_cnt NUMBER := 0;
334
335 X_return_status VARCHAR2(1);
336 X_message_count NUMBER;
337 X_message_list VARCHAR2(2000);
338 l_entity_status gmd_api_grp.status_rec_type;
339
340 l_gmo_enabled VARCHAR2(1);
341 l_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
342 l_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
343 l_target_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
344 l_target_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
345
346 PRAGMA AUTONOMOUS_TRANSACTION;
347
348 BEGIN
349 OPEN Cur_get_hdr;
350 FETCH Cur_get_hdr INTO X_hdr_rec;
351 CLOSE Cur_get_hdr;
352
353 FOR get_actv IN Cur_get_actv LOOP
354 X_row := X_row + 1;
355 X_actv_tbl(X_row) := get_actv;
356 FOR get_rsrc IN Cur_get_rsrc(get_actv.oprn_line_id) LOOP
357 X_rsrc_cnt := X_rsrc_cnt + 1;
358 X_rsrc_tbl(X_rsrc_cnt) := get_rsrc;
359 END LOOP;
360 END LOOP;
361
362 OPEN Cur_get_vers;
363 FETCH Cur_get_vers INTO X_oprn_vers;
364 CLOSE Cur_get_vers;
365 OPEN Cur_gen_oprn_id;
366 FETCH Cur_gen_oprn_id INTO x_oprn_id;
367 CLOSE Cur_gen_oprn_id;
368
369 /* Check if GMO is enabled */
370 l_gmo_enabled := gmo_setup_grp.is_gmo_enabled;
371 IF l_gmo_enabled = 'Y' THEN
372 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'OPERATION',
373 p_entity_id => p_oprn_id,
374 x_name_array => l_source_name_array,
375 x_key_array => l_source_key_array,
376 x_return_status => x_return_status);
377 END IF;
378
379
380 /* Insert header record */
381 GMD_OPERATIONS_PKG.INSERT_ROW(
382 X_ROWID => x_hdr_rec.ROW_ID,
383 X_OPRN_ID => x_oprn_id,
384 X_ATTRIBUTE30 => x_hdr_rec.ATTRIBUTE30,
385 X_ATTRIBUTE_CATEGORY => x_hdr_rec.ATTRIBUTE_CATEGORY,
386 X_ATTRIBUTE25 => x_hdr_rec.ATTRIBUTE25,
387 X_ATTRIBUTE26 => x_hdr_rec.ATTRIBUTE26,
388 X_ATTRIBUTE27 => x_hdr_rec.ATTRIBUTE27,
389 X_ATTRIBUTE28 => x_hdr_rec.ATTRIBUTE28,
390 X_ATTRIBUTE29 => x_hdr_rec.ATTRIBUTE29,
391 X_ATTRIBUTE22 => x_hdr_rec.ATTRIBUTE22,
392 X_ATTRIBUTE23 => x_hdr_rec.ATTRIBUTE23,
393 X_ATTRIBUTE24 => x_hdr_rec.ATTRIBUTE24,
394 X_OPRN_NO => x_hdr_rec.OPRN_NO,
395 X_OPRN_VERS => X_oprn_vers,
396 X_PROCESS_QTY_UOM => x_hdr_rec.PROCESS_QTY_UOM,
397 X_OPRN_CLASS => NVL(p_operation_class,x_hdr_rec.oprn_class), -- 4116557
398 X_INACTIVE_IND => NVL(p_inactive_ind,0),
399 X_EFFECTIVE_START_DATE => NVL(FND_DATE.canonical_to_date(p_effective_start_date) ,X_hdr_rec.effective_start_date ),
400 X_EFFECTIVE_END_DATE => FND_DATE.canonical_to_date( p_effective_end_date ),
401 X_DELETE_MARK => 0,
402 X_TEXT_CODE => x_hdr_rec.TEXT_CODE,
403 X_ATTRIBUTE1 => x_hdr_rec.ATTRIBUTE1,
404 X_ATTRIBUTE2 => x_hdr_rec.ATTRIBUTE2,
405 X_ATTRIBUTE3 => x_hdr_rec.ATTRIBUTE3,
406 X_ATTRIBUTE4 => x_hdr_rec.ATTRIBUTE4,
407 X_ATTRIBUTE5 => x_hdr_rec.ATTRIBUTE5,
408 X_ATTRIBUTE6 => x_hdr_rec.ATTRIBUTE6,
409 X_ATTRIBUTE7 => x_hdr_rec.ATTRIBUTE7,
410 X_ATTRIBUTE8 => x_hdr_rec.ATTRIBUTE8,
411 X_ATTRIBUTE9 => x_hdr_rec.ATTRIBUTE9,
412 X_ATTRIBUTE10 => x_hdr_rec.ATTRIBUTE10,
413 X_ATTRIBUTE11 => x_hdr_rec.ATTRIBUTE11,
414 X_ATTRIBUTE12 => x_hdr_rec.ATTRIBUTE12,
415 X_ATTRIBUTE13 => x_hdr_rec.ATTRIBUTE13,
416 X_ATTRIBUTE14 => x_hdr_rec.ATTRIBUTE14,
417 X_ATTRIBUTE15 => x_hdr_rec.ATTRIBUTE15,
418 X_ATTRIBUTE16 => x_hdr_rec.ATTRIBUTE16,
419 X_ATTRIBUTE17 => x_hdr_rec.ATTRIBUTE17,
420 X_ATTRIBUTE18 => x_hdr_rec.ATTRIBUTE18,
421 X_ATTRIBUTE19 => x_hdr_rec.ATTRIBUTE19,
422 X_ATTRIBUTE20 => x_hdr_rec.ATTRIBUTE20,
423 X_ATTRIBUTE21 => x_hdr_rec.ATTRIBUTE21,
424 X_OPERATION_STATUS => 100,
425 X_owner_organization_id => x_hdr_rec.owner_organization_id,
426 X_OPRN_DESC => x_hdr_rec.OPRN_DESC,
427 X_CREATION_DATE => SYSDATE,
428 X_CREATED_BY => P_created_by,
429 X_LAST_UPDATE_DATE => SYSDATE,
430 X_LAST_UPDATED_BY => P_created_by,
431 X_LAST_UPDATE_LOGIN => P_login_id);
432
433
434 /* Insert Activities */
435 X_rsrc_cnt := 0;
436 FOR i IN 1..X_actv_tbl.count LOOP
437 OPEN Cur_gen_oprnline_id;
438 FETCH Cur_gen_oprnline_id INTO X_oprn_line_id;
439 CLOSE Cur_gen_oprnline_id;
440 INSERT INTO gmd_operation_activities
441 (oprn_id, oprn_line_id, activity, activity_factor, delete_mark, text_code, last_updated_by,
442 created_by, last_update_date, creation_date, last_update_login, attribute1, attribute2,
443 attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,
444 attribute10, attribute11, attribute12, attribute13, attribute14, attribute15, attribute16,
445 attribute17, attribute18, attribute19, attribute20, attribute21, attribute22, attribute23,
446 attribute24, attribute25, attribute26, attribute27, attribute28, attribute29, attribute30,
447 attribute_category, offset_interval, break_ind, max_break)
448 VALUES (x_oprn_id, X_oprn_line_id, DECODE(X_actv_tbl(i).activity,p_old_activity,p_activity,X_actv_tbl(i).activity)
449 , X_actv_tbl(i).activity_factor, 0,
450 X_actv_tbl(i).text_code, P_created_by, P_created_by, SYSDATE, SYSDATE, P_login_id,
451 X_actv_tbl(i).attribute1, X_actv_tbl(i).attribute2, X_actv_tbl(i).attribute3,
452 X_actv_tbl(i).attribute4, X_actv_tbl(i).attribute5, X_actv_tbl(i).attribute6,
453 X_actv_tbl(i).attribute7, X_actv_tbl(i).attribute8, X_actv_tbl(i).attribute9,
454 X_actv_tbl(i).attribute10, X_actv_tbl(i).attribute11, X_actv_tbl(i).attribute12,
455 X_actv_tbl(i).attribute13, X_actv_tbl(i).attribute14, X_actv_tbl(i).attribute15,
456 X_actv_tbl(i).attribute16, X_actv_tbl(i).attribute17, X_actv_tbl(i).attribute18,
457 X_actv_tbl(i).attribute19, X_actv_tbl(i).attribute20, X_actv_tbl(i).attribute21,
458 X_actv_tbl(i).attribute22, X_actv_tbl(i).attribute23, X_actv_tbl(i).attribute24,
459 X_actv_tbl(i).attribute25, X_actv_tbl(i).attribute26, X_actv_tbl(i).attribute27,
460 X_actv_tbl(i).attribute28, X_actv_tbl(i).attribute29, X_actv_tbl(i).attribute30,
461 X_actv_tbl(i).attribute_category, X_actv_tbl(i).offset_interval, x_actv_tbl(i).break_ind, x_actv_tbl(i).max_break);
462 LOOP
463 X_rsrc_cnt := X_rsrc_cnt + 1;
464 IF (X_rsrc_cnt > X_rsrc_tbl.count) THEN
465 EXIT;
466 END IF;
467 IF (X_actv_tbl(i).oprn_line_id = X_rsrc_tbl(X_rsrc_cnt).oprn_line_id) THEN
468 INSERT INTO gmd_operation_resources
469 (oprn_line_id, resources, resource_usage, resource_count, process_qty,
470 prim_rsrc_ind, scale_type, cost_analysis_code, cost_cmpntcls_id, resource_usage_uom,
471 offset_interval, delete_mark, text_code, created_by, last_updated_by,
472 last_update_date, creation_date, last_update_login, attribute1, attribute2,
473 attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
474 attribute9, attribute10, attribute11, attribute12, attribute13, attribute14,
475 attribute15, attribute16, attribute17, attribute18, attribute19, attribute20,
476 attribute21, attribute22, attribute23, attribute24, attribute25, attribute26,
477 attribute27, attribute28, attribute29, attribute30, attribute_category,
478 resource_process_uom, min_capacity, max_capacity, resource_capacity_uom, process_parameter_1,
479 process_parameter_2, process_parameter_3, process_parameter_4, process_parameter_5)
480 VALUES (X_oprn_line_id, DECODE(X_rsrc_tbl(X_rsrc_cnt).resources,p_old_resource,p_resource,X_rsrc_tbl(X_rsrc_cnt).resources),
481 X_rsrc_tbl(X_rsrc_cnt).resource_usage,
482 X_rsrc_tbl(X_rsrc_cnt).resource_count, X_rsrc_tbl(X_rsrc_cnt).process_qty,
483 X_rsrc_tbl(X_rsrc_cnt).prim_rsrc_ind, X_rsrc_tbl(X_rsrc_cnt).scale_type,
484 X_rsrc_tbl(X_rsrc_cnt).cost_analysis_code, X_rsrc_tbl(X_rsrc_cnt).cost_cmpntcls_id,
485 X_rsrc_tbl(X_rsrc_cnt).resource_usage_uom, X_rsrc_tbl(X_rsrc_cnt).offset_interval, X_rsrc_tbl(X_rsrc_cnt).delete_mark,
486 X_rsrc_tbl(X_rsrc_cnt).text_code, P_created_by, P_created_by, SYSDATE, SYSDATE, P_login_id,
487 X_rsrc_tbl(X_rsrc_cnt).attribute1, X_rsrc_tbl(X_rsrc_cnt).attribute2,
488 X_rsrc_tbl(X_rsrc_cnt).attribute3, X_rsrc_tbl(X_rsrc_cnt).attribute4,
489 X_rsrc_tbl(X_rsrc_cnt).attribute5, X_rsrc_tbl(X_rsrc_cnt).attribute6,
490 X_rsrc_tbl(X_rsrc_cnt).attribute7, X_rsrc_tbl(X_rsrc_cnt).attribute8,
491 X_rsrc_tbl(X_rsrc_cnt).attribute9, X_rsrc_tbl(X_rsrc_cnt).attribute10,
492 X_rsrc_tbl(X_rsrc_cnt).attribute11, X_rsrc_tbl(X_rsrc_cnt).attribute12,
493 X_rsrc_tbl(X_rsrc_cnt).attribute13, X_rsrc_tbl(X_rsrc_cnt).attribute14,
494 X_rsrc_tbl(X_rsrc_cnt).attribute15, X_rsrc_tbl(X_rsrc_cnt).attribute16,
495 X_rsrc_tbl(X_rsrc_cnt).attribute17, X_rsrc_tbl(X_rsrc_cnt).attribute18,
496 X_rsrc_tbl(X_rsrc_cnt).attribute19, X_rsrc_tbl(X_rsrc_cnt).attribute20,
497 X_rsrc_tbl(X_rsrc_cnt).attribute21, X_rsrc_tbl(X_rsrc_cnt).attribute22,
498 X_rsrc_tbl(X_rsrc_cnt).attribute23, X_rsrc_tbl(X_rsrc_cnt).attribute24,
499 X_rsrc_tbl(X_rsrc_cnt).attribute25, X_rsrc_tbl(X_rsrc_cnt).attribute26,
500 X_rsrc_tbl(X_rsrc_cnt).attribute27, X_rsrc_tbl(X_rsrc_cnt).attribute28,
501 X_rsrc_tbl(X_rsrc_cnt).attribute29, X_rsrc_tbl(X_rsrc_cnt).attribute30,
502 X_rsrc_tbl(X_rsrc_cnt).attribute_category, X_rsrc_tbl(X_rsrc_cnt).resource_process_uom,
503 X_rsrc_tbl(X_rsrc_cnt).min_capacity, X_rsrc_tbl(X_rsrc_cnt).max_capacity,
504 X_rsrc_tbl(X_rsrc_cnt).resource_capacity_uom, X_rsrc_tbl(X_rsrc_cnt).process_parameter_1,
505 X_rsrc_tbl(X_rsrc_cnt).process_parameter_2, X_rsrc_tbl(X_rsrc_cnt).process_parameter_3,
506 X_rsrc_tbl(X_rsrc_cnt).process_parameter_4, X_rsrc_tbl(X_rsrc_cnt).process_parameter_5);
507 ELSE
508 X_rsrc_cnt := X_rsrc_cnt - 1;
509 EXIT;
510 END IF;
511 END LOOP;
512 END LOOP;
513
514 -- If GMO is enabled, copy the new PI's from old entity to new entity
515 IF l_gmo_enabled = 'Y' THEN
516 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'OPERATION',
517 p_entity_id => x_oprn_id,
518 x_name_array => l_target_name_array,
519 x_key_array => l_target_key_array,
520 x_return_status => x_return_status);
521
522 GMD_PROCESS_INSTR_UTILS.Copy_Process_Instructions (
523 p_source_name_array => l_source_name_array,
524 p_source_key_array => l_source_key_array,
525 p_target_name_array => l_target_name_array,
526 p_target_key_array => l_target_key_array,
527 x_return_status => x_return_status);
528 END IF;
529
530 COMMIT;
531
532 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
533 gmd_api_grp.get_status_details (V_entity_type => 'OPERATION',
534 V_orgn_id => x_hdr_rec.owner_organization_id,
535 X_entity_status => l_entity_status);
536 --Add this code after the call to gmd_routings_pkg.insert_row.
537 IF (l_entity_status.entity_status <> 100) THEN
538 Gmd_status_pub.modify_status ( p_api_version => 1
539 , p_init_msg_list => TRUE
540 , p_entity_name =>'OPERATION'
541 , p_entity_id => x_oprn_id
542 , p_entity_no => NULL
543 , p_entity_version => NULL
544 , p_to_status => l_entity_status.entity_status
545 , p_ignore_flag => FALSE
546 , x_message_count => x_message_count
547 , x_message_list => x_message_list
548 , x_return_status => X_return_status);
549 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
550 FND_MSG_PUB.count_and_get (p_count => x_message_count
551 ,p_encoded => FND_API.g_false
552 ,p_data => x_message_list);
553 X_message_list := FND_MSG_PUB.get (p_msg_index => X_message_count
554 ,p_encoded => 'F');
555 FND_FILE.PUT(FND_FILE.LOG,X_message_list);
556 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
557 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
558 END IF; --l_entity_status.entity_status <> 100
559 COMMIT;
560
561 END create_new_operation;
562
563 /*======================================================================
564 -- PROCEDURE :
565 -- create_new_recipe
566 --
567 -- DESCRIPTION:
568 -- This PL/SQL procedure is responsible for saving the
569 -- new recipe while versioning.
570 --
571 -- REQUIREMENTS
572 --
573 -- SYNOPSIS:
574 -- create_new_recipe(p_recipe_id IN NUMBER,
575 p_routing_id IN NUMBER,
576 p_formula_id IN NUMBER,
577 powner_id IN NUMBER,
578 powner_orgn_code IN VARCHAR2,
579 x_recipe_id OUT NOCOPY NUMBER) IS
580 --
581 -- Mohit Kapoor 24-Jul-2003 Bug 3037410 Added/Modified code in
582 -- order to restrict insertion of dependent records if
583 -- Formula / Routing is replaced in a recipe.
584 -- RLNAGARA Fixed Process Loss ME
585 --===================================================================== */
586 PROCEDURE create_new_recipe(p_recipe_id IN NUMBER,
587 p_routing_id IN NUMBER,
588 p_formula_id IN NUMBER,
589 powner_id IN NUMBER,
590 powner_orgn_code IN VARCHAR2,
591 p_Organization_id IN NUMBER,
592 p_recipe_type IN NUMBER,
593 x_recipe_id OUT NOCOPY NUMBER) IS
594
595 l_rowid VARCHAR2(18);
596 X_recipe_vers NUMBER;
597 X_row NUMBER;
598 X_cnt NUMBER; --Bug 3037410 Mohit Kapoor
599
600 CURSOR Cur_get_hdr IS
601 SELECT *
602 FROM gmd_recipes
603 WHERE recipe_id = p_recipe_id;
604 X_hdr_rec Cur_get_hdr%ROWTYPE;
605
606 CURSOR Cur_process_loss IS
607 SELECT *
608 FROM gmd_recipe_process_loss
609 WHERE recipe_id = p_recipe_id;
610 TYPE proc_loss IS TABLE OF Cur_process_loss%ROWTYPE INDEX BY BINARY_INTEGER;
611 X_proc_loss_tbl proc_loss;
612
613 CURSOR Cur_get_cust IS
614 SELECT *
615 FROM gmd_recipe_customers
616 WHERE recipe_id = p_recipe_id;
617 TYPE rcp_cust IS TABLE OF Cur_get_cust%ROWTYPE INDEX BY BINARY_INTEGER;
618 X_cust_tbl rcp_cust;
619
620 CURSOR Cur_get_steps IS
621 SELECT *
622 FROM gmd_recipe_routing_steps
623 WHERE recipe_id = p_recipe_id;
624
625 TYPE rcp_steps IS TABLE OF Cur_get_steps%ROWTYPE INDEX BY BINARY_INTEGER;
626 X_step_tbl rcp_steps;
627
628 CURSOR Cur_get_vr IS
629 SELECT *
630 FROM gmd_recipe_validity_rules
631 WHERE recipe_id = p_recipe_id;
632 TYPE rcp_vr IS TABLE OF Cur_get_vr%ROWTYPE INDEX BY BINARY_INTEGER;
633 X_vr_tbl rcp_vr;
634
635 CURSOR Cur_get_actv IS
636 SELECT *
637 FROM gmd_recipe_orgn_activities
638 WHERE recipe_id = p_recipe_id;
639 TYPE rcp_actv IS TABLE OF Cur_get_actv%ROWTYPE INDEX BY BINARY_INTEGER;
640 X_actv_tbl rcp_actv;
641
642 CURSOR Cur_get_rsrc IS
643 SELECT *
644 FROM gmd_recipe_orgn_resources
645 WHERE recipe_id = p_recipe_id;
646 TYPE rcp_rsrc IS TABLE OF Cur_get_rsrc%ROWTYPE INDEX BY BINARY_INTEGER;
647 X_rsrc_tbl rcp_rsrc;
648
649 CURSOR Cur_step_mtl IS
650 SELECT *
651 FROM gmd_recipe_step_materials
652 WHERE recipe_id = p_recipe_id;
653 TYPE step_mtl IS TABLE OF Cur_step_mtl%ROWTYPE INDEX BY BINARY_INTEGER;
654 X_stepmtl_tbl step_mtl;
655
656 CURSOR Cur_recipe_vers IS
657 SELECT MAX(recipe_version) + 1
658 FROM gmd_recipes
659 WHERE recipe_no = X_hdr_rec.recipe_no;
660 CURSOR Cur_recipe_id IS
661 SELECT gmd_recipe_id_s.NEXTVAL
662 FROM FND_DUAL;
663
664 X_return_status VARCHAR2(1);
665 X_message_count NUMBER;
666 X_message_list VARCHAR2(2000);
667 l_entity_status gmd_api_grp.status_rec_type;
668
669 l_form_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
670 l_form_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
671 l_rout_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
672 l_rout_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
673 l_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
674 l_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
675 l_target_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
676 l_target_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
677
678 j PLS_INTEGER;
679
680 PRAGMA AUTONOMOUS_TRANSACTION;
681 BEGIN
682 OPEN Cur_get_hdr;
683 FETCH Cur_get_hdr INTO X_hdr_rec;
684 CLOSE Cur_get_hdr;
685
686 X_row := 0;
687 FOR get_loss IN Cur_process_loss LOOP
688 X_row := X_row + 1;
689 X_proc_loss_tbl(X_row) := get_loss;
690 END LOOP;
691
692 X_row := 0;
693 FOR get_cust IN Cur_get_cust LOOP
694 X_row := X_row + 1;
695 X_cust_tbl(X_row) := get_cust;
696 END LOOP;
697
698 X_row := 0;
699 FOR get_steps IN Cur_get_steps LOOP
700 X_row := X_row + 1;
701 X_step_tbl(X_row) := get_steps;
702 END LOOP;
703
704 X_row := 0;
705 FOR get_vr IN Cur_get_vr LOOP
706 X_row := X_row + 1;
707 X_vr_tbl(X_row) := get_vr;
708 END LOOP;
709
710 X_row := 0;
711 FOR get_actv IN Cur_get_actv LOOP
712 X_row := X_row + 1;
713 X_actv_tbl(X_row) := get_actv;
714 END LOOP;
715
716 X_row := 0;
717 FOR get_rsrc IN Cur_get_rsrc LOOP
718 X_row := X_row + 1;
719 X_rsrc_tbl(X_row) := get_rsrc;
720 END LOOP;
721
722 X_row := 0;
723 FOR get_step_mtl IN Cur_step_mtl LOOP
724 X_row := X_row + 1;
725 X_stepmtl_tbl(X_row) := get_step_mtl;
726 END LOOP;
727
728 OPEN Cur_recipe_vers;
729 FETCH Cur_recipe_vers INTO X_recipe_vers;
730 CLOSE Cur_recipe_vers;
731
732 OPEN Cur_recipe_id;
733 FETCH Cur_recipe_id INTO x_recipe_id;
734 CLOSE Cur_recipe_id;
735 gmd_recipes_mls.insert_row(X_ROWID => l_rowid,
736 X_RECIPE_ID => x_recipe_id,
737 X_OWNER_ID => NVL(powner_id,X_hdr_rec.owner_id),
738 X_OWNER_LAB_TYPE => X_hdr_rec.owner_lab_type,
739 X_DELETE_MARK => 0,
740 X_TEXT_CODE => X_hdr_rec.text_code,
741 X_RECIPE_NO => X_hdr_rec.recipe_no,
742 X_RECIPE_VERSION => X_recipe_vers,
743 X_RECIPE_TYPE => NVL(p_recipe_type, X_hdr_rec.recipe_type),
744 X_OWNER_ORGANIZATION_ID => NVL(p_Organization_id,X_hdr_rec.owner_organization_id),
745 X_CREATION_ORGANIZATION_ID=> NVL(p_Organization_id,X_hdr_rec.owner_organization_id),
746 X_FORMULA_ID => NVL(p_formula_id,x_hdr_rec.formula_id),
747 -- Bug 4116557 added NVL for Roution_id.
748 X_ROUTING_ID => NVL( p_routing_id,x_hdr_rec.routing_id),
749 X_PROJECT_ID => X_hdr_rec.project_id,
750 X_RECIPE_STATUS => 100,
751 X_CALCULATE_STEP_QUANTITY => X_hdr_rec.calculate_step_quantity,
752 X_PLANNED_PROCESS_LOSS => X_hdr_rec.planned_process_loss,
753 X_CONTIGUOUS_IND => X_hdr_rec.contiguous_ind,
754 X_ENHANCED_PI_IND => X_hdr_rec.enhanced_pi_ind,
755 X_RECIPE_DESCRIPTION => X_hdr_rec.recipe_description,
756 X_ATTRIBUTE_CATEGORY => X_hdr_rec.attribute_category,
757 X_ATTRIBUTE1 => X_hdr_rec.attribute1,
758 X_ATTRIBUTE2 => X_hdr_rec.attribute2,
759 X_ATTRIBUTE3 => X_hdr_rec.attribute3,
760 X_ATTRIBUTE4 => X_hdr_rec.attribute4,
761 X_ATTRIBUTE5 => X_hdr_rec.attribute5,
762 X_ATTRIBUTE6 => X_hdr_rec.attribute6,
763 X_ATTRIBUTE7 => X_hdr_rec.attribute7,
764 X_ATTRIBUTE8 => X_hdr_rec.attribute8,
765 X_ATTRIBUTE9 => X_hdr_rec.attribute9,
766 X_ATTRIBUTE10 => X_hdr_rec.attribute10,
767 X_ATTRIBUTE11 => X_hdr_rec.attribute11,
768 X_ATTRIBUTE12 => X_hdr_rec.attribute12,
769 X_ATTRIBUTE13 => X_hdr_rec.attribute13,
770 X_ATTRIBUTE14 => X_hdr_rec.attribute14,
771 X_ATTRIBUTE15 => X_hdr_rec.attribute15,
772 X_ATTRIBUTE16 => X_hdr_rec.attribute16,
773 X_ATTRIBUTE17 => X_hdr_rec.attribute17,
774 X_ATTRIBUTE18 => X_hdr_rec.attribute18,
775 X_ATTRIBUTE19 => X_hdr_rec.attribute19,
776 X_ATTRIBUTE20 => X_hdr_rec.attribute20,
777 X_ATTRIBUTE21 => X_hdr_rec.attribute21,
778 X_ATTRIBUTE22 => X_hdr_rec.attribute22,
779 X_ATTRIBUTE23 => X_hdr_rec.attribute23,
780 X_ATTRIBUTE24 => X_hdr_rec.attribute24,
781 X_ATTRIBUTE25 => X_hdr_rec.attribute25,
782 X_ATTRIBUTE26 => X_hdr_rec.attribute26,
783 X_ATTRIBUTE27 => X_hdr_rec.attribute27,
784 X_ATTRIBUTE28 => X_hdr_rec.attribute28,
785 X_ATTRIBUTE29 => X_hdr_rec.attribute29,
786 X_ATTRIBUTE30 => X_hdr_rec.attribute30,
787 X_CREATION_DATE => SYSDATE,
788 X_CREATED_BY => P_created_by,
789 X_LAST_UPDATE_DATE => SYSDATE,
790 X_LAST_UPDATED_BY => P_created_by,
791 X_LAST_UPDATE_LOGIN => P_login_id,
792 X_FIXED_PROCESS_LOSS => X_hdr_rec.fixed_process_loss, /*RLNAGARA 6811759*/
793 X_FIXED_PROCESS_LOSS_UOM => X_hdr_rec.fixed_process_loss_uom);
794
795 FOR i IN 1..X_proc_loss_tbl.count LOOP
796 INSERT INTO gmd_recipe_process_loss(recipe_id, organization_id, process_loss, contiguous_ind, creation_date, created_by,
797 last_updated_by, last_update_date, last_update_login,
798 recipe_process_loss_id, text_code)
799 VALUES (x_recipe_id, X_proc_loss_tbl(i).organization_id, X_proc_loss_tbl(i).process_loss,
800 X_proc_loss_tbl(i).contiguous_ind, SYSDATE, P_created_by, P_created_by, SYSDATE, P_login_id,
801 gmd_recipe_process_loss_id_s.NEXTVAL, X_proc_loss_tbl(i).text_code);
802 END LOOP;
803 FOR i IN 1..X_cust_tbl.count LOOP
804 INSERT INTO gmd_recipe_customers(recipe_id, customer_id, created_by, creation_date, last_updated_by,
805 last_update_login, text_code, last_update_date)
806 VALUES (x_recipe_id, X_cust_tbl(i).customer_id, P_created_by, SYSDATE,
807 P_created_by, P_login_id, X_cust_tbl(i).text_code, SYSDATE);
808 END LOOP;
809
810 -- Bug 3037410 Mohit Kapoor Added the if statement
811 IF p_routing_id is NULL THEN
812 FOR i IN 1..X_step_tbl.count LOOP
813 INSERT INTO gmd_recipe_routing_steps(recipe_id, routingstep_id, step_qty, created_by, creation_date,
814 last_update_date, last_update_login, text_code, last_updated_by,
815 attribute1, attribute2, attribute3, attribute4, attribute5, attribute6,
816 attribute7, attribute8, attribute9, attribute10, attribute11, attribute12,
817 attribute13, attribute14, attribute15, attribute16, attribute17,
818 attribute18, attribute19, attribute20, attribute21, attribute22,
819 attribute23, attribute24, attribute25, attribute26, attribute27, attribute28,
820 attribute29, attribute30, attribute_category, MASS_STD_UOM, VOLUME_STD_UOM,
821 volume_qty, mass_qty)
822 VALUES (x_recipe_id, X_step_tbl(i).routingstep_id, X_step_tbl(i).step_qty,
823 P_created_by, SYSDATE, SYSDATE, P_login_id, X_step_tbl(i).text_code,
824 P_created_by, X_step_tbl(i).attribute1, X_step_tbl(i).attribute2,
825 X_step_tbl(i).attribute3, X_step_tbl(i).attribute4, X_step_tbl(i).attribute5,
826 X_step_tbl(i).attribute6, X_step_tbl(i).attribute7, X_step_tbl(i).attribute8,
827 X_step_tbl(i).attribute9, X_step_tbl(i).attribute10, X_step_tbl(i).attribute11,
828 X_step_tbl(i).attribute12, X_step_tbl(i).attribute13, X_step_tbl(i).attribute14,
829 X_step_tbl(i).attribute15, X_step_tbl(i).attribute16, X_step_tbl(i).attribute17,
830 X_step_tbl(i).attribute18, X_step_tbl(i).attribute19, X_step_tbl(i).attribute20,
831 X_step_tbl(i).attribute21, X_step_tbl(i).attribute22, X_step_tbl(i).attribute23,
832 X_step_tbl(i).attribute24, X_step_tbl(i).attribute25, X_step_tbl(i).attribute26,
833 X_step_tbl(i).attribute27, X_step_tbl(i).attribute28, X_step_tbl(i).attribute29,
834 X_step_tbl(i).attribute30, X_step_tbl(i).attribute_category,
835 X_step_tbl(i).MASS_STD_UOM, X_step_tbl(i).VOLUME_STD_UOM,
836 X_step_tbl(i).volume_qty, X_step_tbl(i).mass_qty);
837 END LOOP;
838 END IF; -- Bug 3037410
839
840 FOR i IN 1..X_vr_tbl.count LOOP
841 --Begin 3037410 Mohit Kapoor
842 X_cnt := 1;
843 IF p_formula_id IS NOT NULL THEN
844 SELECT count(*) INTO X_cnt FROM fm_matl_dtl
845 WHERE item_id = X_vr_tbl(i).item_id AND
846 formula_id = p_formula_id AND
847 line_type = 1;
848 END IF;
849
850 IF X_cnt > 0 THEN
851 --End 3037410
852 INSERT INTO gmd_recipe_validity_rules(recipe_validity_rule_id, recipe_id, organization_id, inventory_item_id, revision, recipe_use, preference,
853 start_date, end_date, min_qty, max_qty, std_qty, detail_uom, inv_min_qty,
854 inv_max_qty, text_code, attribute_category, attribute1, attribute2, attribute3,
855 attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,
856 attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
857 attribute16, attribute17, attribute18, attribute19, attribute20, attribute21,
858 attribute22, attribute23, attribute24, attribute25, attribute26, attribute27,
859 attribute28, attribute29, attribute30, created_by, creation_date,
860 last_updated_by, last_update_date, last_update_login, delete_mark,
861 lab_type, validity_rule_status)
862 VALUES (gmd_recipe_validity_id_s.NEXTVAL, x_recipe_id, X_vr_tbl(i).organization_id,
863 X_vr_tbl(i).inventory_item_id,X_vr_tbl(i).revision, X_vr_tbl(i).recipe_use, X_vr_tbl(i).preference,
864 X_vr_tbl(i).start_date, X_vr_tbl(i).end_date, X_vr_tbl(i).min_qty,
865 X_vr_tbl(i).max_qty, X_vr_tbl(i).std_qty, X_vr_tbl(i).detail_uom,
866 X_vr_tbl(i).inv_min_qty, X_vr_tbl(i).inv_max_qty, X_vr_tbl(i).text_code,
867 X_vr_tbl(i).attribute_category, X_vr_tbl(i).attribute1, X_vr_tbl(i).attribute2,
868 X_vr_tbl(i).attribute3, X_vr_tbl(i).attribute4, X_vr_tbl(i).attribute5,
869 X_vr_tbl(i).attribute6, X_vr_tbl(i).attribute7, X_vr_tbl(i).attribute8,
870 X_vr_tbl(i).attribute9, X_vr_tbl(i).attribute10, X_vr_tbl(i).attribute11,
871 X_vr_tbl(i).attribute12, X_vr_tbl(i).attribute13, X_vr_tbl(i).attribute14,
872 X_vr_tbl(i).attribute15, X_vr_tbl(i).attribute16, X_vr_tbl(i).attribute17,
873 X_vr_tbl(i).attribute18, X_vr_tbl(i).attribute19, X_vr_tbl(i).attribute20,
874 X_vr_tbl(i).attribute21, X_vr_tbl(i).attribute22, X_vr_tbl(i).attribute23,
875 X_vr_tbl(i).attribute24, X_vr_tbl(i).attribute25, X_vr_tbl(i).attribute26,
876 X_vr_tbl(i).attribute27, X_vr_tbl(i).attribute28, X_vr_tbl(i).attribute29,
877 X_vr_tbl(i).attribute30, P_created_by, SYSDATE, P_created_by, SYSDATE,
878 P_login_id, 0, X_vr_tbl(i).lab_type, 100);
879 END IF; -- Bug 3037410
880 END LOOP;
881
882 -- Bug 3037410 Mohit Kapoor Added the if statement
883 IF p_routing_id is NULL THEN
884 FOR i IN 1..X_actv_tbl.count LOOP
885 INSERT INTO gmd_recipe_orgn_activities(recipe_id, routingstep_id, activity_factor, attribute_category, attribute1,
886 created_by, creation_date, last_updated_by, last_update_date,
887 last_update_login, orgn_code, attribute2, attribute3, attribute4, attribute5,
888 attribute6, attribute7, attribute8, attribute9, attribute10, attribute11,
889 attribute12, attribute13, attribute14, attribute15, attribute16, attribute17,
890 attribute18, attribute19, attribute20, attribute21, attribute22, attribute23,
891 attribute24, attribute25, attribute26, attribute27, attribute28, attribute29,
892 attribute30, text_code, oprn_line_id)
893 VALUES (x_recipe_id, X_actv_tbl(i).routingstep_id, X_actv_tbl(i).activity_factor,
894 X_actv_tbl(i).attribute_category, X_actv_tbl(i).attribute1,
895 P_created_by, SYSDATE, P_created_by, SYSDATE, P_login_id,
896 X_actv_tbl(i).orgn_code, X_actv_tbl(i).attribute2, X_actv_tbl(i).attribute3,
897 X_actv_tbl(i).attribute4, X_actv_tbl(i).attribute5, X_actv_tbl(i).attribute6,
898 X_actv_tbl(i).attribute7, X_actv_tbl(i).attribute8, X_actv_tbl(i).attribute9,
899 X_actv_tbl(i).attribute10, X_actv_tbl(i).attribute11, X_actv_tbl(i).attribute12,
900 X_actv_tbl(i).attribute13, X_actv_tbl(i).attribute14, X_actv_tbl(i).attribute15,
901 X_actv_tbl(i).attribute16, X_actv_tbl(i).attribute17, X_actv_tbl(i).attribute18,
902 X_actv_tbl(i).attribute19, X_actv_tbl(i).attribute20, X_actv_tbl(i).attribute21,
903 X_actv_tbl(i).attribute22, X_actv_tbl(i).attribute23, X_actv_tbl(i).attribute24,
904 X_actv_tbl(i).attribute25, X_actv_tbl(i).attribute26, X_actv_tbl(i).attribute27,
905 X_actv_tbl(i).attribute28, X_actv_tbl(i).attribute29, X_actv_tbl(i).attribute30,
906 X_actv_tbl(i).text_code, X_actv_tbl(i).oprn_line_id
907 );
908 END LOOP;
909
910 FOR i IN 1..X_rsrc_tbl.count LOOP
911 INSERT INTO gmd_recipe_orgn_resources(recipe_id, organization_id, routingstep_id, oprn_line_id, resources, creation_date,
912 created_by, last_updated_by, last_update_date, min_capacity, max_capacity,
913 last_update_login, text_code,
914 attribute1, attribute2, attribute3, attribute4, attribute5, attribute6,
915 attribute7, attribute8, attribute9, attribute10, attribute11, attribute12,
916 attribute13, attribute14, attribute15, attribute16, attribute17, attribute18,
917 attribute19, attribute20, attribute21, attribute22, attribute23, attribute24,
918 attribute25, attribute26, attribute27, attribute28, attribute29, attribute30,
919 attribute_category, process_parameter_5, process_parameter_4,
920 process_parameter_3, process_parameter_2, process_parameter_1, process_um,
921 usage_uom, resource_usage, process_qty)
922 VALUES (x_recipe_id, X_rsrc_tbl(i).organization_id, X_rsrc_tbl(i).routingstep_id,
923 X_rsrc_tbl(i).oprn_line_id, X_rsrc_tbl(i).resources, SYSDATE, P_created_by,
924 P_created_by, SYSDATE, X_rsrc_tbl(i).min_capacity, X_rsrc_tbl(i).max_capacity,
925 P_login_id,
926 X_rsrc_tbl(i).text_code, X_rsrc_tbl(i).attribute1, X_rsrc_tbl(i).attribute2,
927 X_rsrc_tbl(i).attribute3, X_rsrc_tbl(i).attribute4, X_rsrc_tbl(i).attribute5,
928 X_rsrc_tbl(i).attribute6, X_rsrc_tbl(i).attribute7, X_rsrc_tbl(i).attribute8,
929 X_rsrc_tbl(i).attribute9, X_rsrc_tbl(i).attribute10, X_rsrc_tbl(i).attribute11,
930 X_rsrc_tbl(i).attribute12, X_rsrc_tbl(i).attribute13, X_rsrc_tbl(i).attribute14,
931 X_rsrc_tbl(i).attribute15, X_rsrc_tbl(i).attribute16, X_rsrc_tbl(i).attribute17,
932 X_rsrc_tbl(i).attribute18, X_rsrc_tbl(i).attribute19, X_rsrc_tbl(i).attribute20,
933 X_rsrc_tbl(i).attribute21, X_rsrc_tbl(i).attribute22, X_rsrc_tbl(i).attribute23,
934 X_rsrc_tbl(i).attribute24, X_rsrc_tbl(i).attribute25, X_rsrc_tbl(i).attribute26,
935 X_rsrc_tbl(i).attribute27, X_rsrc_tbl(i).attribute28, X_rsrc_tbl(i).attribute29,
936 X_rsrc_tbl(i).attribute30, X_rsrc_tbl(i).attribute_category,
937 X_rsrc_tbl(i).process_parameter_5, X_rsrc_tbl(i).process_parameter_4,
938 X_rsrc_tbl(i).process_parameter_3, X_rsrc_tbl(i).process_parameter_2,
939 X_rsrc_tbl(i).process_parameter_1, X_rsrc_tbl(i).process_um,
940 X_rsrc_tbl(i).usage_uom, X_rsrc_tbl(i).resource_usage, X_rsrc_tbl(i).process_qty);
941 END LOOP;
942 END IF; -- Bug 3037410
943
944 -- Bug 3037410 Mohit Kapoor Added the if statement
945 IF p_routing_id is NULL AND p_formula_id is NULL THEN
946 FOR i IN 1..X_stepmtl_tbl.count LOOP
947 INSERT INTO gmd_recipe_step_materials(recipe_id, formulaline_id, routingstep_id, text_code, creation_date,
948 created_by, last_updated_by, last_update_date, last_update_login)
949 VALUES (x_recipe_id, X_stepmtl_tbl(i).formulaline_id, X_stepmtl_tbl(i).routingstep_id,
950 X_stepmtl_tbl(i).text_code, SYSDATE, P_created_by, P_created_by, SYSDATE, P_login_id);
951 END LOOP;
952 END IF; -- Bug 3037410
953
954
955 IF gmo_setup_grp.is_gmo_enabled = 'Y' THEN
956 -- If GMO is enabled, copy the new PI's from old entity to new entity
957
958 IF X_hdr_rec.formula_id = NVL(p_formula_id, X_hdr_rec.formula_id) THEN
959 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'FORMULA',
960 p_entity_id => X_hdr_rec.formula_id,
961 x_name_array => l_form_source_name_array,
962 x_key_array => l_form_source_key_array,
963 x_return_status => x_return_status);
964
965 FOR i IN 1..l_form_source_key_array.COUNT
966 LOOP
967 l_source_name_array(i) := l_form_source_name_array(i);
968 l_source_key_array(i) := p_recipe_id|| '$' ||l_form_source_key_array(i);
969 l_target_name_array(i) := l_form_source_name_array(i);
970 l_target_key_array(i) := x_recipe_id|| '$' ||l_form_source_key_array(i);
971 END LOOP;
972 END IF;
973
974 IF (X_hdr_rec.routing_id IS NOT NULL) AND
975 (X_hdr_rec.routing_id = NVL(p_routing_id, X_hdr_rec.routing_id)) THEN
976 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'ROUTING',
977 p_entity_id => X_hdr_rec.routing_id,
978 x_name_array => l_rout_source_name_array,
979 x_key_array => l_rout_source_key_array,
980 x_return_status => x_return_status);
981
982 j := l_source_name_array.COUNT;
983 FOR i IN 1..l_rout_source_key_array.COUNT
984 LOOP
985 j := j + 1;
986 l_source_name_array(j) := l_rout_source_name_array(i);
987 l_source_key_array(j) := p_recipe_id|| '$' ||l_rout_source_key_array(i);
988 l_target_name_array(j) := l_rout_source_name_array(i);
989 l_target_key_array(j) := x_recipe_id|| '$' ||l_rout_source_key_array(i);
990 END LOOP;
991 END IF;
992
993 GMD_PROCESS_INSTR_UTILS.Copy_Process_Instructions (
994 p_source_name_array => l_source_name_array,
995 p_source_key_array => l_source_key_array,
996 p_target_name_array => l_target_name_array,
997 p_target_key_array => l_target_key_array,
998 x_return_status => x_return_status);
999 END IF;
1000
1001 COMMIT;
1002
1003 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
1004 gmd_api_grp.get_status_details (V_entity_type => 'RECIPE',
1005 V_orgn_id => NVL(p_Organization_id, X_hdr_rec.owner_organization_id),
1006 X_entity_status => l_entity_status);
1007 --Add this code after the call to gmd_routings_pkg.insert_row.
1008 IF (l_entity_status.entity_status <> 100) THEN
1009 Gmd_status_pub.modify_status ( p_api_version => 1
1010 , p_init_msg_list => TRUE
1011 , p_entity_name =>'RECIPE'
1012 , p_entity_id => x_recipe_id
1013 , p_entity_no => NULL
1014 , p_entity_version => NULL
1015 , p_to_status => l_entity_status.entity_status
1016 , p_ignore_flag => FALSE
1017 , x_message_count => x_message_count
1018 , x_message_list => x_message_list
1019 , x_return_status => X_return_status);
1020 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
1021 FND_MSG_PUB.count_and_get (p_count => x_message_count
1022 ,p_encoded => FND_API.g_false
1023 ,p_data => x_message_list);
1024 X_message_list := FND_MSG_PUB.get (p_msg_index => X_message_count
1025 ,p_encoded => 'F');
1026 FND_FILE.PUT(FND_FILE.LOG,X_message_list);
1027 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1028 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
1029 END IF; --l_entity_status.entity_status <> 100
1030 COMMIT;
1031
1032 END create_new_recipe;
1033
1034 /*======================================================================
1035 -- PROCEDURE :
1036 -- create_formula
1037 --
1038 -- DESCRIPTION:
1039 -- This PL/SQL procedure is responsible for saving the
1040 -- new formula while versioning.
1041 --
1042 -- REQUIREMENTS
1043 --
1044 -- SYNOPSIS:
1045 -- create_formula(P_formula_id, X_formula_id);
1046 --
1047 --
1048 -- 07-07-2004 kkillams Bug 3738941, added new validation to copy the attachments.
1049 --===================================================================== */
1050 PROCEDURE create_new_formula(p_formula_id IN NUMBER,
1051 p_formula_class IN VARCHAR2,
1052 p_inactive_ind IN NUMBER,
1053 p_new_ingredient IN NUMBER,
1054 p_old_ingredient IN NUMBER,
1055 p_old_ingr_revision IN VARCHAR2,
1056 p_new_ingr_revision IN VARCHAR2,
1057 p_owner_id IN NUMBER,
1058 x_formula_id OUT NOCOPY NUMBER,
1059 x_scale_factor NUMBER,
1060 pCreate_Recipe IN NUMBER) IS
1061
1062 X_formula_vers NUMBER;
1063 X_row NUMBER := 0;
1064 l_rowid VARCHAR2(18);
1065 CURSOR Cur_formula_id IS
1066 SELECT gem5_formula_id_s.NEXTVAL
1067 FROM FND_DUAL;
1068 CURSOR Cur_get_hdr IS
1069 SELECT *
1070 FROM fm_form_mst
1071 WHERE formula_id = p_formula_id;
1072 X_hdr_rec Cur_get_hdr%ROWTYPE;
1073 CURSOR Cur_formula_vers IS
1074 SELECT MAX(formula_vers) + 1
1075 FROM fm_form_mst
1076 WHERE formula_no = X_hdr_rec.formula_no;
1077
1078 CURSOR Cur_get_dtl IS
1079 SELECT *
1080 FROM fm_matl_dtl
1081 WHERE formula_id = p_formula_id;
1082
1083 CURSOR Cur_get_status (V_formula_id IN NUMBER) IS
1084 SELECT formula_status
1085 FROM fm_form_mst_b
1086 WHERE formula_id = V_formula_id;
1087
1088 CURSOR Cur_get_auto_recipe(V_orgn_id fm_form_mst_b.owner_organization_id%TYPE) IS
1089 SELECT creation_type
1090 FROM gmd_recipe_generation
1091 WHERE organization_id = V_orgn_id
1092 AND creation_type IN (1,2)
1093 UNION
1094 SELECT creation_type
1095 FROM gmd_recipe_generation
1096 WHERE organization_id IS NULL
1097 AND creation_type IN (1,2)
1098 AND NOT EXISTS (SELECT 1
1099 FROM gmd_recipe_generation
1100 WHERE organization_id = V_orgn_id);
1101 --kkillams,bug 3738941
1102 CURSOR cur_form_att(cp_entity_name fnd_attached_documents.entity_name%TYPE,
1103 cp_pk1_value fnd_attached_documents.pk1_value%TYPE) IS
1104 SELECT 1
1105 FROM fnd_attached_documents fad
1106 WHERE fad.entity_name = cp_entity_name
1107 AND fad.pk1_value = cp_pk1_value;
1108
1109 l_formulaline_id fm_matl_dtl.formulaline_id%TYPE;
1110 l_ingr_id fm_matl_dtl.inventory_item_id%TYPE;
1111 l_ingr_revision fm_matl_dtl.revision%TYPE;
1112 l_dummy NUMBER;
1113
1114 TYPE detail_tab IS TABLE OF Cur_get_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
1115 X_dtl_tbl detail_tab;
1116 l_entity_status gmd_api_grp.status_rec_type;
1117 X_return_status VARCHAR2(1);
1118 X_message_count NUMBER;
1119 X_message_list VARCHAR2(2000);
1120 FORM_STATUS_ERR EXCEPTION;
1121 X_formula_status VARCHAR2(40);
1122 X_recipe_no VARCHAR2(32);
1123 X_recipe_version NUMBER;
1124 X_creation_type NUMBER(5);
1125
1126 l_gmo_enabled VARCHAR2(1);
1127 l_source_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1128 l_source_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1129 l_target_name_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1130 l_target_key_array GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1131
1132 -- Bug No.5985327 - Start
1133 --Created new variables to retrieve the output quantity from GMD_COMMON_VAL.calculate_total_qty procedure
1134
1135 x_return_status_for_calc VARCHAR2(20);
1136 X_msg_cnt NUMBER;
1137 X_msg_dat VARCHAR2(100);
1138 X_status VARCHAR2(1);
1139 l_output_qty NUMBER;
1140 l_input_qty NUMBER;
1141 l_uom VARCHAR2(4);
1142 --Bug No.5985327 - End
1143
1144 PRAGMA AUTONOMOUS_TRANSACTION;
1145 BEGIN
1146 OPEN Cur_get_hdr;
1147 FETCH Cur_get_hdr INTO X_hdr_rec;
1148 CLOSE Cur_get_hdr;
1149
1150 FOR get_rec IN Cur_get_dtl LOOP
1151 X_row := X_row + 1;
1152 X_dtl_tbl(X_row) := get_rec;
1153 IF (get_rec.inventory_item_id = p_old_ingredient) THEN
1154 x_dtl_tbl(x_row).qty := x_dtl_tbl(x_row).qty * nvl(x_scale_factor,1);
1155 END IF;
1156 END LOOP;
1157
1158 OPEN Cur_formula_vers;
1159 FETCH Cur_formula_vers INTO X_formula_vers;
1160 CLOSE Cur_formula_vers;
1161
1162 OPEN Cur_formula_id;
1163 FETCH Cur_formula_id INTO x_formula_id;
1164 CLOSE Cur_formula_id;
1165
1166 /* Check if GMO is enabled */
1167 l_gmo_enabled := gmo_setup_grp.is_gmo_enabled;
1168 IF l_gmo_enabled = 'Y' THEN
1169 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'FORMULA',
1170 p_entity_id => p_formula_id,
1171 x_name_array => l_source_name_array,
1172 x_key_array => l_source_key_array,
1173 x_return_status => x_return_status);
1174 END IF;
1175
1176 /* Insert header record */
1177 fm_form_mst_mls.insert_row(X_ROWID => l_rowid,
1178 X_FORMULA_ID => X_formula_id,
1179 X_MASTER_FORMULA_ID => NULL,
1180 X_OWNER_ORGANIZATION_ID => X_hdr_rec.owner_organization_id,
1181 X_TEXT_CODE => X_hdr_rec.text_code,
1182 X_DELETE_MARK => 0,
1183 X_TOTAL_INPUT_QTY => 0,
1184 X_PROJECT_ID => X_hdr_rec.project_id,
1185 X_TOTAL_OUTPUT_QTY => 0,
1186 X_FORMULA_STATUS => 100,
1187 X_OWNER_ID => NVL(p_owner_id,X_hdr_rec.owner_id),
1188 X_FORMULA_NO => X_hdr_rec.formula_no,
1189 X_FORMULA_VERS => X_formula_vers,
1190 X_FORMULA_TYPE => X_hdr_rec.formula_type,
1191 X_IN_USE => 0,
1192 X_INACTIVE_IND => NVL(p_inactive_ind,0),
1193 X_SCALE_TYPE => X_hdr_rec.scale_type,
1194 X_FORMULA_CLASS => p_formula_class,
1195 X_FMCONTROL_CLASS => X_hdr_rec.fmcontrol_class,
1196 X_FORMULA_DESC1 => X_hdr_rec.formula_desc1,
1197 X_FORMULA_DESC2 => X_hdr_rec.formula_desc2,
1198 X_CREATION_DATE => SYSDATE,
1199 X_CREATED_BY => P_created_by,
1200 X_LAST_UPDATE_DATE => SYSDATE,
1201 X_LAST_UPDATED_BY => P_created_by,
1202 X_LAST_UPDATE_LOGIN => P_login_id,
1203 X_ATTRIBUTE_CATEGORY => X_hdr_rec.attribute_category,
1204 X_ATTRIBUTE1 => X_hdr_rec.attribute1,
1205 X_ATTRIBUTE2 => X_hdr_rec.attribute2,
1206 X_ATTRIBUTE3 => X_hdr_rec.attribute3,
1207 X_ATTRIBUTE4 => X_hdr_rec.attribute4,
1208 X_ATTRIBUTE5 => X_hdr_rec.attribute5,
1209 X_ATTRIBUTE6 => X_hdr_rec.attribute6,
1210 X_ATTRIBUTE7 => X_hdr_rec.attribute7,
1211 X_ATTRIBUTE8 => X_hdr_rec.attribute8,
1212 X_ATTRIBUTE9 => X_hdr_rec.attribute9,
1213 X_ATTRIBUTE10 => X_hdr_rec.attribute10,
1214 X_ATTRIBUTE11 => X_hdr_rec.attribute11,
1215 X_ATTRIBUTE12 => X_hdr_rec.attribute12,
1216 X_ATTRIBUTE13 => X_hdr_rec.attribute13,
1217 X_ATTRIBUTE14 => X_hdr_rec.attribute14,
1218 X_ATTRIBUTE15 => X_hdr_rec.attribute15,
1219 X_ATTRIBUTE16 => X_hdr_rec.attribute16,
1220 X_ATTRIBUTE17 => X_hdr_rec.attribute17,
1221 X_ATTRIBUTE18 => X_hdr_rec.attribute18,
1222 X_ATTRIBUTE19 => X_hdr_rec.attribute19,
1223 X_ATTRIBUTE20 => X_hdr_rec.attribute20,
1224 X_ATTRIBUTE21 => X_hdr_rec.attribute21,
1225 X_ATTRIBUTE22 => X_hdr_rec.attribute22,
1226 X_ATTRIBUTE23 => X_hdr_rec.attribute23,
1227 X_ATTRIBUTE24 => X_hdr_rec.attribute24,
1228 X_ATTRIBUTE25 => X_hdr_rec.attribute25,
1229 X_ATTRIBUTE26 => X_hdr_rec.attribute26,
1230 X_ATTRIBUTE27 => X_hdr_rec.attribute27,
1231 X_ATTRIBUTE28 => X_hdr_rec.attribute28,
1232 X_ATTRIBUTE29 => X_hdr_rec.attribute29,
1233 X_ATTRIBUTE30 => X_hdr_rec.attribute30,
1234 X_YIELD_UOM => x_hdr_rec.yield_uom,
1235 X_AUTO_PRODUCT_CALC => x_hdr_rec.auto_product_calc);
1236 --kkillams, Bug 3738941
1237 --Added following validation to copy the attachments
1238 OPEN cur_form_att('FM_FORM_MST_B',to_char(p_formula_id));
1239 FETCH cur_form_att INTO l_dummy;
1240 IF cur_form_att%FOUND THEN
1241 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
1242 X_from_entity_name => 'FM_FORM_MST_B',
1243 X_from_pk1_value => TO_CHAR(p_formula_id),
1244 X_from_pk2_value => NULL,
1245 X_from_pk3_value => NULL,
1246 X_from_pk4_value => NULL,
1247 X_from_pk5_value => NULL,
1248 X_to_entity_name => 'FM_FORM_MST_B',
1249 X_to_pk1_value => TO_CHAR(X_formula_id),
1250 X_to_pk2_value => NULL,
1251 X_to_pk3_value => NULL,
1252 X_to_pk4_value => NULL,
1253 X_to_pk5_value => NULL,
1254 X_created_by => FND_GLOBAL.USER_ID,
1255 X_last_update_login => FND_GLOBAL.LOGIN_ID,
1256 X_program_application_id => NULL,
1257 X_program_id => NULL,
1258 X_request_id => NULL );
1259 END IF;
1260 CLOSE cur_form_att;
1261 FOR i IN 1..X_dtl_tbl.count LOOP
1262
1263 IF ((X_dtl_tbl(i).inventory_item_id = p_old_ingredient) AND (NVL(X_dtl_tbl(i).revision, -1) = NVL(p_old_ingr_revision, -1)) ) THEN
1264 l_ingr_id := p_new_ingredient;
1265 l_ingr_revision := p_new_ingr_revision;
1266 ELSE
1267 l_ingr_id := X_dtl_tbl(i).inventory_item_id;
1268 l_ingr_revision := X_dtl_tbl(i).revision;
1269 END IF;
1270
1271 INSERT INTO fm_matl_dtl(formulaline_id, formula_id, line_type, line_no, inventory_item_id, revision, organization_id,
1272 qty, detail_uom, release_type, scrap_factor, scale_type, cost_alloc,
1273 phantom_type, rework_type, text_code, last_updated_by, created_by,
1274 last_update_date, creation_date, last_update_login, attribute1,
1275 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
1276 attribute8, attribute9, attribute10, attribute11, attribute12,
1277 attribute13, attribute14, attribute15, attribute16, attribute17,
1278 attribute18, attribute19, attribute20, attribute21, attribute22,
1279 attribute23, attribute24, attribute25, attribute26, attribute27,
1280 attribute28, attribute29, attribute30, attribute_category, tpformula_id,
1281 scale_multiple, contribute_yield_ind, scale_uom, contribute_step_qty_ind,
1282 scale_rounding_variance)
1283 VALUES (gem5_formulaline_id_s.NEXTVAL, X_formula_id, X_dtl_tbl(i).line_type, X_dtl_tbl(i).line_no,
1284 l_ingr_id, l_ingr_revision, X_dtl_tbl(i).organization_id,
1285 X_dtl_tbl(i).qty, X_dtl_tbl(i).detail_uom, X_dtl_tbl(i).release_type,
1286 X_dtl_tbl(i).scrap_factor, X_dtl_tbl(i).scale_type, X_dtl_tbl(i).cost_alloc,
1287 X_dtl_tbl(i).phantom_type, X_dtl_tbl(i).rework_type, X_dtl_tbl(i).text_code,
1288 P_created_by, P_created_by, SYSDATE, SYSDATE, P_login_id, X_dtl_tbl(i).attribute1,
1289 X_dtl_tbl(i).attribute2, X_dtl_tbl(i).attribute3, X_dtl_tbl(i).attribute4,
1290 X_dtl_tbl(i).attribute5, X_dtl_tbl(i).attribute6, X_dtl_tbl(i).attribute7,
1291 X_dtl_tbl(i).attribute8, X_dtl_tbl(i).attribute9, X_dtl_tbl(i).attribute10,
1292 X_dtl_tbl(i).attribute11, X_dtl_tbl(i).attribute12, X_dtl_tbl(i).attribute13,
1293 X_dtl_tbl(i).attribute14, X_dtl_tbl(i).attribute15, X_dtl_tbl(i).attribute16,
1294 X_dtl_tbl(i).attribute17, X_dtl_tbl(i).attribute18, X_dtl_tbl(i).attribute19,
1295 X_dtl_tbl(i).attribute20, X_dtl_tbl(i).attribute21, X_dtl_tbl(i).attribute22,
1296 X_dtl_tbl(i).attribute23, X_dtl_tbl(i).attribute24, X_dtl_tbl(i).attribute25,
1297 X_dtl_tbl(i).attribute26, X_dtl_tbl(i).attribute27, X_dtl_tbl(i).attribute28,
1298 X_dtl_tbl(i).attribute29, X_dtl_tbl(i).attribute30, X_dtl_tbl(i).attribute_category,
1299 X_dtl_tbl(i).tpformula_id, X_dtl_tbl(i).scale_multiple, X_dtl_tbl(i).contribute_yield_ind,
1300 X_dtl_tbl(i).scale_uom, X_dtl_tbl(i).contribute_step_qty_ind,
1301 X_dtl_tbl(i).scale_rounding_variance) RETURNING formulaline_id INTO l_formulaline_id;
1302 --kkillams, Bug 3738941
1303 --Added following validation to copy the attachments
1304 OPEN cur_form_att('FM_MATL_DTL',to_char(X_dtl_tbl(i).formulaline_id));
1305 FETCH cur_form_att INTO l_dummy;
1306 IF cur_form_att%FOUND THEN
1307 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
1308 X_from_entity_name => 'FM_MATL_DTL',
1309 X_from_pk1_value => TO_CHAR(X_dtl_tbl(i).formulaline_id),
1310 X_from_pk2_value => NULL,
1311 X_from_pk3_value => NULL,
1312 X_from_pk4_value => NULL,
1313 X_from_pk5_value => NULL,
1314 X_to_entity_name => 'FM_MATL_DTL',
1315 X_to_pk1_value => TO_CHAR(l_formulaline_id),
1316 X_to_pk2_value => NULL,
1317 X_to_pk3_value => NULL,
1318 X_to_pk4_value => NULL,
1319 X_to_pk5_value => NULL,
1320 X_created_by => FND_GLOBAL.USER_ID,
1321 X_last_update_login => FND_GLOBAL.LOGIN_ID,
1322 X_program_application_id => NULL,
1323 X_program_id => NULL,
1324 X_request_id => NULL );
1325 END IF;
1326 CLOSE cur_form_att;
1327 END LOOP;
1328
1329 -- If GMO is enabled, copy the new PI's from old entity to new entity
1330 IF l_gmo_enabled = 'Y' THEN
1331 GMD_PROCESS_INSTR_UTILS.build_array (p_entity_name => 'FORMULA',
1332 p_entity_id => x_formula_id,
1333 x_name_array => l_target_name_array,
1334 x_key_array => l_target_key_array,
1335 x_return_status => x_return_status);
1336
1337 GMD_PROCESS_INSTR_UTILS.Copy_Process_Instructions (
1338 p_source_name_array => l_source_name_array,
1339 p_source_key_array => l_source_key_array,
1340 p_target_name_array => l_target_name_array,
1341 p_target_key_array => l_target_key_array,
1342 x_return_status => x_return_status);
1343 END IF;
1344
1345 COMMIT;
1346
1347 --Bug No.5985327 - Start
1348
1349 GMD_COMMON_VAL.calculate_total_qty(formula_id => x_formula_id,
1350 x_product_qty => l_output_qty ,
1351 x_ingredient_qty => l_input_qty ,
1352 x_uom => l_uom ,
1353 x_return_status => x_return_status_for_calc ,
1354 x_msg_count => X_msg_cnt ,
1355 x_msg_data => x_msg_dat );
1356
1357 UPDATE fm_form_mst_b
1358 SET total_output_qty = l_output_qty,
1359 total_input_qty = l_input_qty,
1360 formula_uom = l_uom
1361 WHERE formula_id = X_formula_id;
1362
1363 COMMIT;
1364 --Bug No.5985327 - End
1365
1366 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
1367 gmd_api_grp.get_status_details (V_entity_type => 'FORMULA',
1368 V_orgn_id => X_hdr_rec.owner_organization_id,
1369 X_entity_status => l_entity_status);
1370
1371 IF (l_entity_status.entity_status <> 100) THEN
1372 gmd_status_pub.modify_status ( p_api_version => 1
1373 , p_init_msg_list => TRUE
1374 , p_entity_name => 'FORMULA'
1375 , p_entity_id => X_formula_id
1376 , p_entity_no => NULL
1377 , p_entity_version => NULL
1378 , p_to_status => l_entity_status.entity_status
1379 , p_ignore_flag => FALSE
1380 , x_message_count => x_message_count
1381 , x_message_list => x_message_list
1382 , x_return_status => X_return_status);
1383 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
1384 RAISE form_status_err;
1385 END IF; --x_return_status
1386 END IF; --l_entity_status.entity_status <> 100
1387
1388 OPEN Cur_get_status (X_formula_id);
1389 FETCH Cur_get_status INTO X_formula_status;
1390 CLOSE Cur_get_status;
1391
1392 IF (X_formula_status = l_entity_status.entity_status) THEN
1393 OPEN Cur_get_auto_recipe (X_hdr_rec.owner_organization_id);
1394 FETCH Cur_get_auto_recipe INTO X_creation_type;
1395 CLOSE Cur_get_auto_recipe;
1396 IF (NVL(X_creation_type, 0) = 1) OR
1397 (NVL(X_creation_type, 0) = 2 AND pCreate_Recipe = 1) THEN
1398 GMD_RECIPE_GENERATE.recipe_generate(X_hdr_rec.owner_organization_id, X_formula_id,
1399 X_return_status, X_recipe_no, X_recipe_version);
1400 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1401 RAISE form_status_err;
1402 ELSIF (X_recipe_no IS NOT NULL) AND
1403 (X_recipe_version IS NOT NULL) THEN
1404 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_AUTOMATIC');
1405 FND_MESSAGE.SET_TOKEN('NAME', x_recipe_no);
1406 FND_MESSAGE.SET_TOKEN('VERSION', x_recipe_version);
1407 FND_FILE.PUT(FND_FILE.LOG, FND_MESSAGE.GET);
1408 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1409 END IF;
1410 END IF; /*IF (NVL(X_creation_type, 0) = 1) OR*/
1411 END IF;/* IF (X_formula_status = l_entity_status.entity_status) */
1412 COMMIT;
1413 EXCEPTION
1414 WHEN form_status_err THEN
1415 FND_MSG_PUB.count_and_get (p_count => x_message_count
1416 ,p_encoded => FND_API.g_false
1417 ,p_data => x_message_list);
1418 X_message_list := FND_MSG_PUB.get (p_msg_index => X_message_count
1419 ,p_encoded => 'F');
1420 FND_FILE.PUT(FND_FILE.LOG,X_message_list);
1421 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1422 ROLLBACK;
1423 END create_new_formula;
1424
1425 END gmd_search_replace_vers;