[Home] [Help]
PACKAGE BODY: APPS.GMD_SUBSTITUTION_PVT
Source
1 PACKAGE BODY GMD_SUBSTITUTION_PVT AS
2 /* $Header: GMDVSUBB.pls 120.0.12000000.1 2007/01/31 16:17:23 appldev noship $ */
3
4 -- common cursors
5 CURSOR Cur_get_substitution(vSubstitution_id NUMBER) IS
6 SELECT i.original_inventory_item_id, i.start_date, i.substitution_status, f.formula_id
7 FROM gmd_item_substitution_hdr_b i, gmd_formula_substitution f
8 WHERE i.substitution_id = vSubstitution_id
9 AND i.substitution_id = f.substitution_id;
10
11 CURSOR Cur_check_substitute (V_formula_id NUMBER
12 , V_item_id NUMBER
13 , p_substitution_id NUMBER) IS
14 SELECT MIN(i.start_date)
15 FROM gmd_item_substitution_hdr_b i, gmd_formula_substitution f
16 WHERE f.formula_id = V_formula_id
17 AND i.original_inventory_item_id = V_item_id
18 AND i.substitution_id <> p_substitution_id
19 AND i.substitution_id = f.substitution_id
20 AND i.substitution_status BETWEEN 700 AND 799;
21
22
23 /* =============================================================== */
24 /* Procedure: */
25 /* Create_new_substitution_vers */
26 /* */
27 /* DESCRIPTION: Private function */
28 /* */
29 /* */
30 /* History : */
31 /* Rajender Nalla 09-OCT-06 Initial implementation. */
32 /* =============================================================== */
33 PROCEDURE Create_new_substitution_vers
34 ( p_substitution_id IN NUMBER
35 , x_message_count OUT NOCOPY NUMBER
36 , x_message_list OUT NOCOPY VARCHAR2
37 , x_return_status OUT NOCOPY VARCHAR2
38 ) IS
39
40 l_new_substitution_version NUMBER;
41 l_old_substitution_name gmd_item_substitution_hdr_b.substitution_name%TYPE;
42 l_old_substitution_version NUMBER;
43
44 CURSOR get_substitution_info(vSubstitution_id NUMBER) IS
45 SELECT substitution_name, substitution_version
46 FROM gmd_item_substitution_hdr_b
47 WHERE substitution_id = vSubstitution_id;
48 BEGIN
49 /* Set the return status to success initially */
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51
52 -- If version control is turned ON - copy the existing formula substitution
53 -- details and its association and create a new substitution header with
54 -- these details.
55 IF (GMD_COMMON_VAL.version_control_state('SUBSTITUTION'
56 ,p_substitution_id) = 'Y') THEN
57
58 OPEN get_substitution_info(p_substitution_id);
59 FETCH get_substitution_info INTO l_old_substitution_name,
60 l_old_substitution_version;
61 CLOSE get_substitution_info;
62
63 Copy_substitution
64 ( p_old_substitution_id => p_substitution_id
65 , x_new_substitution_version => l_new_substitution_version
66 , x_message_count => x_message_count
67 , x_message_list => x_message_list
68 , x_return_status => x_return_status
69 );
70
71 IF (x_return_status = 'S') THEN
72 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_NEW_OBJECT_VERSION');
73 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE','SUBSTITUTION');
74 FND_MESSAGE.SET_TOKEN('VERSION',l_new_substitution_version);
75 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',l_old_substitution_name);
76 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE','SUBSTITUTION');
77 FND_MESSAGE.SET_TOKEN('NEW_ITEM',l_old_substitution_name||' - '||
78 l_old_substitution_version);
79 FND_MSG_PUB.ADD;
80 END IF;
81 END IF;
82
83 /* Get the messgae list and count generated by this API */
84 fnd_msg_pub.count_and_get (
85 p_count => x_message_count
86 ,p_encoded => FND_API.g_false
87 ,p_data => x_message_list);
88 END Create_new_substitution_vers;
89
90 /* =============================================================== */
91 /* Procedure: */
92 /* Create_substitution_header */
93 /* */
94 /* DESCRIPTION: */
95 /* */
96 /* */
97 /* History : */
98 /* Rajender Nalla 09-OCT-06 Initial implementation. */
99 /* =============================================================== */
100 PROCEDURE Create_substitution_header
101 ( p_substitution_id IN gmd_item_substitution_hdr.substitution_id%TYPE
102 , p_substitution_hdr_rec IN gmd_substitution_pub.gmd_substitution_hdr_rec_type
103 , x_message_count OUT NOCOPY NUMBER
104 , x_message_list OUT NOCOPY VARCHAR2
105 , x_return_status OUT NOCOPY VARCHAR2
106 ) IS
107
108 l_api_name CONSTANT VARCHAR2(30) := 'Create_substitution_header';
109 l_row_id ROWID;
110 l_original_prim_item_um VARCHAR2(3);
111
112 Cursor get_item_info(vItem_id NUMBER) IS
113 SELECT primary_uom_code
114 FROM mtl_system_items_b
115 WHERE inventory_item_id = vItem_id;
116
117 /* Define Exceptions */
118 substitution_creation_failure EXCEPTION;
119 BEGIN
120 /* Set the return status to success initially */
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122
123 OPEN get_item_info(p_substitution_hdr_rec.original_inventory_item_id);
124 FETCH get_item_info INTO l_original_prim_item_um;
125 CLOSE get_item_info;
126
127 IF (l_original_prim_item_um IS NULL) THEN
128 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_ORIGINAL_ITEM');
129 FND_MSG_PUB.ADD;
130 RAISE substitution_creation_failure;
131 END IF;
132
133 -- Call the table handler for insert of substitution header
134 GMD_ITEM_SUBSTITUTION_HDR_PKG.INSERT_ROW(
135 X_ROWID => l_row_id,
136 X_SUBSTITUTION_ID => p_substitution_id,
137 X_SUBSTITUTION_NAME => p_substitution_hdr_rec.substitution_name,
138 X_SUBSTITUTION_VERSION => p_substitution_hdr_rec.substitution_version,
139 X_SUBSTITUTION_STATUS => 100,
140 X_ORIGINAL_INVENTORY_ITEM_ID => p_substitution_hdr_rec.original_inventory_item_id,
141 X_ORIGINAL_UOM => l_original_prim_item_um,
142 X_ORIGINAL_QTY => p_substitution_hdr_rec.original_qty,
143 X_PREFERENCE => p_substitution_hdr_rec.preference,
144 X_START_DATE => p_substitution_hdr_rec.start_date,
145 X_END_DATE => p_substitution_hdr_rec.end_date,
146 X_OWNER_ORGANIZATION_ID => p_substitution_hdr_rec.owner_organization_id,
147 X_REPLACEMENT_UOM_TYPE => p_substitution_hdr_rec.replacement_uom_type,
148 X_ATTRIBUTE_CATEGORY => p_substitution_hdr_rec.attribute_category,
149 X_ATTRIBUTE1 => p_substitution_hdr_rec.attribute1,
150 X_ATTRIBUTE2 => p_substitution_hdr_rec.attribute2,
151 X_ATTRIBUTE3 => p_substitution_hdr_rec.attribute3,
152 X_ATTRIBUTE4 => p_substitution_hdr_rec.attribute4,
153 X_ATTRIBUTE5 => p_substitution_hdr_rec.attribute5,
154 X_ATTRIBUTE6 => p_substitution_hdr_rec.attribute6,
155 X_ATTRIBUTE7 => p_substitution_hdr_rec.attribute7,
156 X_ATTRIBUTE8 => p_substitution_hdr_rec.attribute8,
157 X_ATTRIBUTE9 => p_substitution_hdr_rec.attribute9,
158 X_ATTRIBUTE10 => p_substitution_hdr_rec.attribute10,
159 X_ATTRIBUTE11 => p_substitution_hdr_rec.attribute11,
160 X_ATTRIBUTE12 => p_substitution_hdr_rec.attribute12,
161 X_ATTRIBUTE13 => p_substitution_hdr_rec.attribute13,
162 X_ATTRIBUTE14 => p_substitution_hdr_rec.attribute14,
163 X_ATTRIBUTE15 => p_substitution_hdr_rec.attribute15,
164 X_ATTRIBUTE16 => p_substitution_hdr_rec.attribute16,
165 X_ATTRIBUTE17 => p_substitution_hdr_rec.attribute17,
166 X_ATTRIBUTE18 => p_substitution_hdr_rec.attribute18,
167 X_ATTRIBUTE19 => p_substitution_hdr_rec.attribute19,
168 X_ATTRIBUTE20 => p_substitution_hdr_rec.attribute20,
169 X_ATTRIBUTE21 => p_substitution_hdr_rec.attribute21,
170 X_ATTRIBUTE22 => p_substitution_hdr_rec.attribute22,
171 X_ATTRIBUTE23 => p_substitution_hdr_rec.attribute23,
172 X_ATTRIBUTE24 => p_substitution_hdr_rec.attribute24,
173 X_ATTRIBUTE25 => p_substitution_hdr_rec.attribute25,
174 X_ATTRIBUTE26 => p_substitution_hdr_rec.attribute26,
175 X_ATTRIBUTE27 => p_substitution_hdr_rec.attribute27,
176 X_ATTRIBUTE28 => p_substitution_hdr_rec.attribute28,
177 X_ATTRIBUTE29 => p_substitution_hdr_rec.attribute29,
178 X_ATTRIBUTE30 => p_substitution_hdr_rec.attribute30,
179 X_SUBSTITUTION_DESCRIPTION => p_substitution_hdr_rec.substitution_description,
180 X_CREATION_DATE => p_substitution_hdr_rec.creation_date,
181 X_CREATED_BY => p_substitution_hdr_rec.created_by,
182 X_LAST_UPDATE_DATE => p_substitution_hdr_rec.last_update_date,
183 X_LAST_UPDATED_BY => p_substitution_hdr_rec.last_updated_by,
184 X_LAST_UPDATE_LOGIN => p_substitution_hdr_rec.last_update_login);
185
186 IF (l_row_id IS NULL) THEN
187 Raise substitution_creation_failure;
188 END IF;
189
190 /* Get the messgae list and count generated by this API */
191 fnd_msg_pub.count_and_get (
192 p_count => x_message_count
193 ,p_encoded => FND_API.g_false
194 ,p_data => x_message_list);
195
196 EXCEPTION
197 WHEN substitution_creation_failure THEN
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 fnd_msg_pub.count_and_get (
200 p_count => x_message_count
201 ,p_encoded => FND_API.g_false
202 ,p_data => x_message_list);
203 WHEN OTHERS THEN
204 fnd_msg_pub.add_exc_msg (GMD_SUBSTITUTION_PVT.m_pkg_name, l_api_name);
205 x_return_status := FND_API.g_ret_sts_unexp_error;
206 fnd_msg_pub.count_and_get (
207 p_count => x_message_count
208 ,p_encoded => FND_API.g_false
209 ,p_data => x_message_list);
210 END Create_substitution_header;
211
212 /* =============================================================== */
213 /* Procedure: */
214 /* Create_substitution_detail */
215 /* */
216 /* DESCRIPTION: */
217 /* */
218 /* */
219 /* History : */
220 /* Rajender Nalla 09-OCT-06 Initial implementation. */
221 /* =============================================================== */
222 PROCEDURE Create_substitution_detail
223 ( p_substitution_line_id IN gmd_item_substitution_dtl.substitution_line_id%TYPE
224 , p_substitution_id IN gmd_item_substitution_dtl.substitution_id%TYPE
225 , p_substitution_dtl_rec IN gmd_substitution_pub.gmd_substitution_dtl_rec_type
226 , x_message_count OUT NOCOPY NUMBER
227 , x_message_list OUT NOCOPY VARCHAR2
228 , x_return_status OUT NOCOPY VARCHAR2
229 ) IS
230 l_api_name CONSTANT VARCHAR2(30) := 'Create_substitution_detail';
231 l_row_id ROWID;
232
233 /* Define Exceptions */
234 substitution_creation_failure EXCEPTION;
235 BEGIN
236 /* Set the return status to success initially */
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 -- Call the table handler for insert of substitution details
240 GMD_ITEM_SUBSTITUTION_DTL_PKG.INSERT_ROW(
241 X_ROWID => l_row_id,
242 X_SUBSTITUTION_LINE_ID => p_substitution_line_id,
243 X_SUBSTITUTION_ID => p_substitution_id,
244 X_INVENTORY_ITEM_ID => p_substitution_dtl_rec.inventory_item_id,
245 X_UNIT_QTY => p_substitution_dtl_rec.unit_qty,
246 X_DETAIL_UOM => p_substitution_dtl_rec.detail_uom,
247 X_CREATION_DATE => p_substitution_dtl_rec.creation_date,
248 X_CREATED_BY => p_substitution_dtl_rec.created_by,
249 X_LAST_UPDATE_DATE => p_substitution_dtl_rec.last_update_date,
250 X_LAST_UPDATED_BY => p_substitution_dtl_rec.last_updated_by,
251 X_LAST_UPDATE_LOGIN => p_substitution_dtl_rec.last_update_login);
252
253 IF (l_row_id IS NULL) THEN
254 Raise substitution_creation_failure;
255 END IF;
256
257 /* Get the messgae list and count generated by this API */
258 fnd_msg_pub.count_and_get (
259 p_count => x_message_count
260 ,p_encoded => FND_API.g_false
261 ,p_data => x_message_list);
262 EXCEPTION
263 WHEN substitution_creation_failure THEN
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 fnd_msg_pub.count_and_get (
266 p_count => x_message_count
267 ,p_encoded => FND_API.g_false
268 ,p_data => x_message_list);
269 WHEN OTHERS THEN
270 fnd_msg_pub.add_exc_msg (GMD_SUBSTITUTION_PVT.m_pkg_name, l_api_name);
271 x_return_status := FND_API.g_ret_sts_unexp_error;
272 fnd_msg_pub.count_and_get (
273 p_count => x_message_count
274 ,p_encoded => FND_API.g_false
275 ,p_data => x_message_list);
276 END Create_substitution_detail;
277
278
279 /* =============================================================== */
280 /* Procedure: */
281 /* Create_formula_association */
282 /* */
283 /* DESCRIPTION: */
284 /* */
285 /* */
286 /* History : */
287 /* Rajender Nalla 09-OCT-06 Initial implementation. */
288 /* =============================================================== */
289 PROCEDURE Create_formula_association
290 ( p_substitution_id IN gmd_formula_substitution.substitution_id%TYPE
291 , p_formula_substitution_tbl IN gmd_substitution_pub.gmd_formula_substitution_tab
292 , x_message_count OUT NOCOPY NUMBER
293 , x_message_list OUT NOCOPY VARCHAR2
294 , x_return_status OUT NOCOPY VARCHAR2
295 ) IS
296
297 l_api_name CONSTANT VARCHAR2(30) := 'Create_formula_association';
298 l_row_id ROWID;
299 l_associated_flag VARCHAR2(1) := 'N';
300 l_original_item_id NUMBER;
301 l_status NUMBER;
302 l_is_new_version_created boolean := FALSE;
303 l_subs_start_date Date;
304 l_start_date Date;
305 l_formula_substitution_id NUMBER;
306
307 CURSOR get_substitution_info(vSubstitution_id NUMBER) IS
308 Select substitution_status, start_date, original_inventory_item_id
309 From gmd_item_substitution_hdr_b
310 Where substitution_id = vSubstitution_id;
311
312 /* Define Exceptions */
313 substitution_creation_failure EXCEPTION;
314 BEGIN
315 /* Set the return status to success initially */
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 -- Calculation 1
319 -- List status is Appr for Gen Use and formulas are associated to this list
320 -- set the associated flag to 'Y'
321 OPEN get_substitution_info(p_substitution_id);
322 FETCH get_substitution_info INTO l_status, l_subs_start_date, l_original_item_id;
323 CLOSE get_substitution_info;
324
325 IF (l_status between 700 and 799) THEN
326 l_associated_flag := 'Y';
327 END IF;
328
329 FOR i in 1 .. p_formula_substitution_tbl.count LOOP
330 -- get the formula substitution id
331 select gmd_formula_substitution_s.nextval
332 into l_formula_substitution_id
333 from dual;
334
335 -- Call the table handler for insert of formula substitution
336 GMD_FORMULA_SUBSTITUTION_PKG.insert_row
337 (
338 X_ROWID => l_row_id
339 , X_FORMULA_SUBSTITUTION_ID => l_formula_substitution_id
340 , X_SUBSTITUTION_ID => p_substitution_id
341 , X_FORMULA_ID => p_formula_substitution_tbl(i).formula_id
342 , X_ASSOCIATED_FLAG => l_associated_flag
343 , X_CREATION_DATE => p_formula_substitution_tbl(i).creation_date
344 , X_CREATED_BY => p_formula_substitution_tbl(i).created_by
345 , X_LAST_UPDATE_DATE => p_formula_substitution_tbl(i).last_update_date
346 , X_LAST_UPDATED_BY => p_formula_substitution_tbl(i).last_updated_by
347 , X_LAST_UPDATE_LOGIN => p_formula_substitution_tbl(i).last_update_login
348 );
349
350 IF (l_row_id IS NULL) THEN
351 Raise substitution_creation_failure;
352 END IF;
353
354 IF (l_status between 700 and 799) THEN
355 OPEN Cur_check_substitute (p_formula_substitution_tbl(i).formula_id
356 ,l_original_item_id
357 ,p_substitution_id);
358 FETCH Cur_check_substitute INTO l_start_date;
359 CLOSE Cur_check_substitute;
360
361 IF (NVL(l_start_date,l_subs_start_date) >= l_subs_start_date) THEN
362 UPDATE fm_matl_dtl
363 SET ingredient_end_date = l_subs_start_date
364 WHERE formula_id = p_formula_substitution_tbl(i).formula_id
365 AND line_type = -1
366 AND inventory_item_id = l_original_item_id;
367 END IF;
368 END IF;
369 END LOOP;
370
371 Create_new_substitution_vers
372 ( p_substitution_id => p_substitution_id
373 , x_message_count => x_message_count
374 , x_message_list => x_message_list
375 , x_return_status => x_return_status
376 );
377
378 /* Get the messgae list and count generated by this API */
379 fnd_msg_pub.count_and_get (
380 p_count => x_message_count
381 ,p_encoded => FND_API.g_false
382 ,p_data => x_message_list);
383 EXCEPTION
384 WHEN substitution_creation_failure THEN
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 fnd_msg_pub.count_and_get (
387 p_count => x_message_count
388 ,p_encoded => FND_API.g_false
389 ,p_data => x_message_list);
390 WHEN OTHERS THEN
391 fnd_msg_pub.add_exc_msg (GMD_SUBSTITUTION_PVT.m_pkg_name, l_api_name);
392 x_return_status := FND_API.g_ret_sts_unexp_error;
393 fnd_msg_pub.count_and_get (
394 p_count => x_message_count
395 ,p_encoded => FND_API.g_false
396 ,p_data => x_message_list);
397 END Create_formula_association;
398
399 /* =============================================================== */
400 /* Procedure: */
401 /* Update_substitution_header */
402 /* */
403 /* DESCRIPTION: */
404 /* */
405 /* */
406 /* History : */
407 /* Rajender Nalla 09-OCT-06 Initial implementation. */
408 /* =============================================================== */
409 PROCEDURE Update_substitution_header
410 ( p_substitution_hdr_rec IN gmd_item_substitution_hdr%ROWTYPE
411 , x_message_count OUT NOCOPY NUMBER
412 , x_message_list OUT NOCOPY VARCHAR2
413 , x_return_status OUT NOCOPY VARCHAR2
414 ) IS
415
416 /* Local variable section */
417 l_api_name CONSTANT VARCHAR2(30) := 'Update_substitution_header';
418 l_dummy NUMBER := 0;
419 l_start_date DATE;
420 l_old_substitution_hdr_rec gmd_item_substitution_hdr_b%ROWTYPE;
421
422 CURSOR get_old_subs_rec(vSubstitution_id NUMBER) IS
423 Select *
424 From gmd_item_substitution_hdr_b
425 Where substitution_id = vSubstitution_id;
426
427 CURSOR check_for_date_overlap(vSubstitution_id NUMBER
428 ,vOriginal_item_id NUMBER
429 ,vPreference NUMBER
430 ,vStart_date DATE
431 ,vEnd_date DATE) IS
432 SELECT 1
433 FROM gmd_item_substitution_hdr_b
434 WHERE substitution_id <> vSubstitution_id
435 AND original_inventory_item_id = vOriginal_item_id
436 AND preference = vPreference
437 AND vStart_date >= start_date
438 AND substitution_status < 1000
439 AND (end_date IS NULL OR vEnd_date <= end_date);
440
441 /* Define Exceptions */
442 substitution_update_failure EXCEPTION;
443 BEGIN
444 /* Set the return status to success initially */
445 x_return_status := FND_API.G_RET_STS_SUCCESS;
446
447 OPEN get_old_subs_rec(p_substitution_hdr_rec.substitution_id);
448 FETCH get_old_subs_rec INTO l_old_substitution_hdr_rec;
449 CLOSE get_old_subs_rec;
450
451 -- Validation 2
452 -- Combination of item, date range and preference would be considered for
453 -- uniquess of a list.
454 OPEN check_for_date_overlap(p_substitution_hdr_rec.substitution_id
455 ,p_substitution_hdr_rec.Original_inventory_item_id
456 ,p_substitution_hdr_rec.Preference
457 ,p_substitution_hdr_rec.Start_date
458 ,p_substitution_hdr_rec.End_date);
459 FETCH check_for_date_overlap into l_dummy;
460 CLOSE check_for_date_overlap;
461
462 IF (l_dummy > 0) THEN
463 FND_MESSAGE.SET_NAME('GMD','GMD_ITSUB_DATE_PRE_OVERLAP');
464 FND_MSG_PUB.ADD;
465 RAISE substitution_update_failure;
466 END IF;
467
468 -- Call the table handler for update of substitution header
469 GMD_ITEM_SUBSTITUTION_HDR_PKG.UPDATE_ROW(
470 X_SUBSTITUTION_ID => p_substitution_hdr_rec.substitution_id,
471 X_SUBSTITUTION_NAME => p_substitution_hdr_rec.substitution_name,
472 X_SUBSTITUTION_VERSION => p_substitution_hdr_rec.substitution_version,
473 X_SUBSTITUTION_STATUS => p_substitution_hdr_rec.substitution_status,
474 X_ORIGINAL_INVENTORY_ITEM_ID => p_substitution_hdr_rec.original_inventory_item_id,
475 X_ORIGINAL_UOM => p_substitution_hdr_rec.original_uom,
476 X_ORIGINAL_QTY => p_substitution_hdr_rec.original_qty,
477 X_PREFERENCE => p_substitution_hdr_rec.preference,
478 X_START_DATE => p_substitution_hdr_rec.start_date,
479 X_END_DATE => p_substitution_hdr_rec.end_date,
480 X_OWNER_ORGANIZATION_ID => p_substitution_hdr_rec.owner_organization_id,
481 X_REPLACEMENT_UOM_TYPE => p_substitution_hdr_rec.replacement_uom_type,
482 X_ATTRIBUTE_CATEGORY => p_substitution_hdr_rec.attribute_category,
483 X_ATTRIBUTE1 => p_substitution_hdr_rec.attribute1,
484 X_ATTRIBUTE2 => p_substitution_hdr_rec.attribute2,
485 X_ATTRIBUTE3 => p_substitution_hdr_rec.attribute3,
486 X_ATTRIBUTE4 => p_substitution_hdr_rec.attribute4,
487 X_ATTRIBUTE5 => p_substitution_hdr_rec.attribute5,
488 X_ATTRIBUTE6 => p_substitution_hdr_rec.attribute6,
489 X_ATTRIBUTE7 => p_substitution_hdr_rec.attribute7,
490 X_ATTRIBUTE8 => p_substitution_hdr_rec.attribute8,
491 X_ATTRIBUTE9 => p_substitution_hdr_rec.attribute9,
492 X_ATTRIBUTE10 => p_substitution_hdr_rec.attribute10,
493 X_ATTRIBUTE11 => p_substitution_hdr_rec.attribute11,
494 X_ATTRIBUTE12 => p_substitution_hdr_rec.attribute12,
495 X_ATTRIBUTE13 => p_substitution_hdr_rec.attribute13,
496 X_ATTRIBUTE14 => p_substitution_hdr_rec.attribute14,
497 X_ATTRIBUTE15 => p_substitution_hdr_rec.attribute15,
498 X_ATTRIBUTE16 => p_substitution_hdr_rec.attribute16,
499 X_ATTRIBUTE17 => p_substitution_hdr_rec.attribute17,
500 X_ATTRIBUTE18 => p_substitution_hdr_rec.attribute18,
501 X_ATTRIBUTE19 => p_substitution_hdr_rec.attribute19,
502 X_ATTRIBUTE20 => p_substitution_hdr_rec.attribute20,
503 X_ATTRIBUTE21 => p_substitution_hdr_rec.attribute21,
504 X_ATTRIBUTE22 => p_substitution_hdr_rec.attribute22,
505 X_ATTRIBUTE23 => p_substitution_hdr_rec.attribute23,
506 X_ATTRIBUTE24 => p_substitution_hdr_rec.attribute24,
507 X_ATTRIBUTE25 => p_substitution_hdr_rec.attribute25,
508 X_ATTRIBUTE26 => p_substitution_hdr_rec.attribute26,
509 X_ATTRIBUTE27 => p_substitution_hdr_rec.attribute27,
510 X_ATTRIBUTE28 => p_substitution_hdr_rec.attribute28,
511 X_ATTRIBUTE29 => p_substitution_hdr_rec.attribute29,
512 X_ATTRIBUTE30 => p_substitution_hdr_rec.attribute30,
513 X_SUBSTITUTION_DESCRIPTION => p_substitution_hdr_rec.substitution_description,
514 X_LAST_UPDATE_DATE => p_substitution_hdr_rec.last_update_date,
515 X_LAST_UPDATED_BY => p_substitution_hdr_rec.last_updated_by,
516 X_LAST_UPDATE_LOGIN => p_substitution_hdr_rec.last_update_login);
517
518 -- Calculation 1
519 -- List status is Appr for Gen Use and formulas are associated to this list
520 -- a) If Start Date is changed - Re-derive the ingredient effective end date
521 -- and modify the effective substitute date for ingredient whose formulas
522 -- are associated.
523 IF (l_old_substitution_hdr_rec.start_date
524 <> p_substitution_hdr_rec.start_date) THEN
525 FOR l_rec IN Cur_get_substitution(p_substitution_hdr_rec.substitution_id) LOOP
526 OPEN Cur_check_substitute (l_rec.formula_id
527 , l_rec.original_inventory_item_id
528 , p_substitution_hdr_rec.substitution_id);
529 FETCH Cur_check_substitute INTO l_start_date;
530 CLOSE Cur_check_substitute;
531
532 IF (NVL(l_start_date,p_substitution_hdr_rec.start_date) >=
533 p_substitution_hdr_rec.start_date) THEN
534 UPDATE fm_matl_dtl
535 SET ingredient_end_date = p_substitution_hdr_rec.start_date
536 WHERE formula_id = l_rec.formula_id
537 AND line_type = -1
538 AND inventory_item_id = l_rec.original_inventory_item_id;
539 END IF;
540 END LOOP;
541 END IF;
542
543 -- If version control is turned ON - copy the existing formula substitution
544 -- details and its association and create a new substitution header with
545 -- these details.
546 Create_new_substitution_vers
547 ( p_substitution_id => p_substitution_hdr_rec.substitution_id
548 , x_message_count => x_message_count
549 , x_message_list => x_message_list
550 , x_return_status => x_return_status
551 );
552
553 /* Get the messgae list and count generated by this API */
554 fnd_msg_pub.count_and_get (
555 p_count => x_message_count
556 ,p_encoded => FND_API.g_false
557 ,p_data => x_message_list);
558 EXCEPTION
559 WHEN substitution_update_failure THEN
560 x_return_status := FND_API.G_RET_STS_ERROR;
561 fnd_msg_pub.count_and_get (
562 p_count => x_message_count
563 ,p_encoded => FND_API.g_false
564 ,p_data => x_message_list);
565 WHEN OTHERS THEN
566 fnd_msg_pub.add_exc_msg (GMD_SUBSTITUTION_PVT.m_pkg_name, l_api_name);
567 x_return_status := FND_API.g_ret_sts_unexp_error;
568 fnd_msg_pub.count_and_get (
569 p_count => x_message_count
570 ,p_encoded => FND_API.g_false
571 ,p_data => x_message_list);
572 END Update_substitution_header;
573
574 /* =============================================================== */
575 /* Procedure: */
576 /* Update_substitution_detail */
577 /* */
578 /* DESCRIPTION: */
579 /* */
580 /* */
581 /* History : */
582 /* Rajender Nalla 09-OCT-06 Initial implementation. */
583 /* =============================================================== */
584 PROCEDURE Update_substitution_detail
585 ( p_substitution_dtl_rec IN gmd_item_substitution_dtl%ROWTYPE
586 , x_message_count OUT NOCOPY NUMBER
587 , x_message_list OUT NOCOPY VARCHAR2
588 , x_return_status OUT NOCOPY VARCHAR2
589 ) IS
590 /* Local variable section */
591 l_api_name CONSTANT VARCHAR2(30) := 'Update_substitution_detail';
592 l_substitution_dtl_rec gmd_item_substitution_dtl%ROWTYPE;
593 BEGIN
594 /* Set the return status to success initially */
595 x_return_status := FND_API.G_RET_STS_SUCCESS;
596
597 -- Call the table handler for update of substitution details
598 GMD_ITEM_SUBSTITUTION_DTL_PKG.UPDATE_ROW(
599 X_SUBSTITUTION_LINE_ID => p_substitution_dtl_rec.substitution_line_id,
600 X_SUBSTITUTION_ID => p_substitution_dtl_rec.substitution_id,
601 X_INVENTORY_ITEM_ID => p_substitution_dtl_rec.inventory_item_id,
602 X_UNIT_QTY => p_substitution_dtl_rec.unit_qty,
603 X_DETAIL_UOM => p_substitution_dtl_rec.detail_uom,
604 X_LAST_UPDATE_DATE => p_substitution_dtl_rec.last_update_date,
605 X_LAST_UPDATED_BY => p_substitution_dtl_rec.last_updated_by,
606 X_LAST_UPDATE_LOGIN => p_substitution_dtl_rec.last_update_login);
607
608 -- If version control is turned ON - copy the existing formula substitution
609 -- details and its association and create a new substitution header with
610 -- these details.
611 Create_new_substitution_vers
612 ( p_substitution_id => p_substitution_dtl_rec.substitution_id
613 , x_message_count => x_message_count
614 , x_message_list => x_message_list
615 , x_return_status => x_return_status
616 );
617
618 /* Get the messgae list and count generated by this API */
619 fnd_msg_pub.count_and_get (
620 p_count => x_message_count
621 ,p_encoded => FND_API.g_false
622 ,p_data => x_message_list);
623 EXCEPTION
624 WHEN OTHERS THEN
625 fnd_msg_pub.add_exc_msg (gmd_substitution_pvt.m_pkg_name, l_api_name);
626 x_return_status := FND_API.g_ret_sts_unexp_error;
627 fnd_msg_pub.count_and_get (
628 p_count => x_message_count
629 ,p_encoded => FND_API.g_false
630 ,p_data => x_message_list);
631 END Update_substitution_detail;
632
633 /* =============================================================== */
634 /* Procedure: */
635 /* Delete_formula_association */
636 /* */
637 /* DESCRIPTION: */
638 /* */
639 /* */
640 /* History : */
641 /* Rajender Nalla 09-OCT-06 Initial implementation. */
642 /* =============================================================== */
643 PROCEDURE Delete_formula_association
644 ( p_formula_substitution_id IN NUMBER
645 , x_message_count OUT NOCOPY NUMBER
646 , x_message_list OUT NOCOPY VARCHAR2
647 , x_return_status OUT NOCOPY VARCHAR2
648 ) IS
649 l_api_name CONSTANT VARCHAR2(30) := 'Delete_formula_association';
650 l_end_date DATE;
651 l_formula_id NUMBER;
652 l_original_item_id NUMBER;
653 l_substitution_id NUMBER;
654
655 CURSOR get_fm_and_orig_item(vformula_substitution_id NUMBER) IS
656 Select formula_id, original_inventory_item_id, i.substitution_id
657 From gmd_item_substitution_hdr_b i, gmd_formula_substitution f
658 Where f.formula_substitution_id = vformula_substitution_id
659 AND i.substitution_id = f.substitution_id;
660
661 CURSOR Cur_get_sub_end_date(vformula_id NUMBER,
662 vOriginal_item_id NUMBER,
663 vSubstitution_id NUMBER) IS
664 SELECT MIN(z.start_date)
665 FROM gmd_item_substitution_hdr_b z,
666 gmd_formula_substitution y
667 WHERE y.substitution_id <> vSubstitution_id
668 AND z.substitution_id = y.substitution_id
669 AND z.original_inventory_item_id = vOriginal_item_id
670 AND y.formula_id = vformula_id
671 AND z.substitution_status BETWEEN 700 AND 799;
672 BEGIN
673 /* Set the return status to success initially */
674 x_return_status := FND_API.G_RET_STS_SUCCESS;
675
676 OPEN get_fm_and_orig_item(p_formula_substitution_id);
677 FETCH get_fm_and_orig_item INTO l_formula_id
678 , l_original_item_id
679 , l_substitution_id;
680 CLOSE get_fm_and_orig_item;
681
682 -- Call the table handler for delete of formula substitution association
683 GMD_FORMULA_SUBSTITUTION_PKG.DELETE_ROW
684 (
685 X_FORMULA_SUBSTITUTION_ID => p_formula_substitution_id
686 );
687
688 IF ((l_formula_id IS NOT NULL) AND (l_original_item_id IS NOT NULL)) THEN
689 OPEN cur_get_sub_end_date(l_formula_id,
690 l_original_item_id,
691 l_substitution_id);
692 FETCH cur_get_sub_end_date into l_end_date;
693 CLOSE cur_get_sub_end_date;
694
695 UPDATE fm_matl_dtl
696 SET ingredient_end_date = l_end_date
697 WHERE formula_id = l_formula_id
698 AND inventory_item_id = l_original_item_id
699 AND line_type = -1;
700 END IF;
701
702 -- If version control is turned ON - copy the existing formula substitution
703 -- details and its association and create a new substitution header with
704 -- these details.
705 Create_new_substitution_vers
706 ( p_substitution_id => l_substitution_id
707 , x_message_count => x_message_count
708 , x_message_list => x_message_list
709 , x_return_status => x_return_status
710 );
711
712 /* Get the messgae list and count generated by this API */
713 fnd_msg_pub.count_and_get (
714 p_count => x_message_count
715 ,p_encoded => FND_API.g_false
716 ,p_data => x_message_list);
717 EXCEPTION
718 WHEN OTHERS THEN
719 fnd_msg_pub.add_exc_msg (gmd_substitution_pvt.m_pkg_name, l_api_name);
720 x_return_status := FND_API.g_ret_sts_unexp_error;
721 fnd_msg_pub.count_and_get (
722 p_count => x_message_count
723 ,p_encoded => FND_API.g_false
724 ,p_data => x_message_list);
725 END Delete_formula_association;
726
727 /* =============================================================== */
728 /* Procedure: */
729 /* Copy_substitution */
730 /* */
731 /* DESCRIPTION: */
732 /* */
733 /* */
734 /* History : */
735 /* Rajender Nalla 09-OCT-06 Initial implementation. */
736 /* =============================================================== */
737 PROCEDURE Copy_substitution
738 ( p_old_substitution_id IN NUMBER
739 , x_new_substitution_version OUT NOCOPY NUMBER
740 , x_message_count OUT NOCOPY NUMBER
741 , x_message_list OUT NOCOPY VARCHAR2
742 , x_return_status OUT NOCOPY VARCHAR2
743 ) IS
744
745 -- local record structure
746 l_substitution_hdr_rec gmd_substitution_pub.gmd_substitution_hdr_rec_type;
747 l_substitution_dtl_rec gmd_substitution_pub.gmd_substitution_dtl_rec_type;
748 l_formula_substitution_tbl gmd_substitution_pub.gmd_formula_substitution_tab;
749
750 -- local variables
751 l_substitution_id NUMBER;
752 l_substitution_line_id NUMBER;
753 l_formula_substitution_id NUMBER;
754 l_row_count NUMBER := 0;
755
756 l_api_name CONSTANT VARCHAR2(30) := 'Copy_substitution';
757
758 Cursor get_max_vers_plus_one(vSubstitution_name VARCHAR2) IS
759 SELECT max(substitution_version) + 1
760 FROM gmd_item_substitution_hdr_b
761 WHERE substitution_name = vSubstitution_name;
762
763 Cursor get_max_pref_plus_one(vOriginal_item_id NUMBER) IS
764 SELECT max(preference) + 1
765 FROM gmd_item_substitution_hdr_b
766 WHERE original_inventory_item_id = vOriginal_item_id;
767
768 Cursor get_substitution_hdr_rec(vSubstitution_id NUMBER) IS
769 SELECT substitution_name
770 , substitution_descriptiOn
771 , substitution_version
772 , original_inventory_item_id
773 , ' ' original_item_no
774 , original_qty
775 , preference
776 , start_date
777 , end_date
778 , owner_organization_id
779 , replacement_uom_type
780 , attribute_category
781 , attribute1
782 , attribute2
783 , attribute3
784 , attribute4
785 , attribute5
786 , attribute6
787 , attribute7
788 , attribute8
789 , attribute9
790 , attribute10
791 , attribute11
792 , attribute12
793 , attribute13
794 , attribute14
795 , attribute15
796 , attribute16
797 , attribute17
798 , attribute18
799 , attribute19
800 , attribute20
801 , attribute21
802 , attribute22
803 , attribute23
804 , attribute24
805 , attribute25
806 , attribute26
807 , attribute27
808 , attribute28
809 , attribute29
810 , attribute30
811 , creation_date
812 , created_by
813 , last_update_date
814 , last_updated_by
815 , last_update_login
816 From gmd_item_substitution_hdr
817 Where substitution_id = vSubstitution_id;
818
819 Cursor get_substitution_dtl_rec(vSubstitution_id NUMBER) IS
820 SELECT inventory_item_id
821 , ' ' item_no
822 , unit_qty
823 , detail_uom
824 , creation_date
825 , created_by
826 , last_update_date
827 , last_updated_by
828 , last_update_login
829 FROM gmd_item_substitution_dtl
830 WHERE substitution_id = vSubstitution_id;
831
832 Cursor get_fm_substitution_tbl(vSubstitution_id NUMBER) IS
833 SELECT formula_id
834 , ' ' formula_no
835 , 1 formula_vers
836 , creation_date
837 , created_by
838 , last_update_date
839 , last_updated_by
840 , last_update_login
841 FROM gmd_formula_substitution
842 WHERE substitution_id = vSubstitution_id;
843
844 -- Exception declaration
845 substitution_creation_failure EXCEPTION;
846 BEGIN
847 /* Set the return status to success initially */
848 x_return_status := FND_API.G_RET_STS_SUCCESS;
849
850 /* Populate the record */
851 -- Populate header
852 OPEN get_substitution_hdr_rec(p_old_substitution_id);
853 FETCH get_substitution_hdr_rec INTO l_substitution_hdr_rec;
854 CLOSE get_substitution_hdr_rec;
855
856 -- Populate detail
857 OPEN get_substitution_dtl_rec(p_old_substitution_id);
858 FETCH get_substitution_dtl_rec INTO l_substitution_dtl_rec;
859 CLOSE get_substitution_dtl_rec;
860
861 -- Populate formula substitution table
862 FOR get_fmsub_rec IN get_fm_substitution_tbl(p_old_substitution_id) LOOP
863 l_row_count := l_row_count + 1;
864 l_formula_substitution_tbl(l_row_count) := get_fmsub_rec;
865 END LOOP;
866
867 /* Rollback changes */
868 ROLLBACK;
869
870 /* call individual private APIs */
871 -- Insert substitution header
872 -- Get the substitution_id
873 select gmd_item_substitution_hdr_s.nextval
874 into l_substitution_id
875 from dual;
876
877 -- Get the substitution max version
878 OPEN get_max_vers_plus_one(l_substitution_hdr_rec.substitution_name);
879 FETCH get_max_vers_plus_one INTO l_substitution_hdr_rec.substitution_version;
880 CLOSE get_max_vers_plus_one;
881
882 -- Get the substitution max priority
883 OPEN get_max_pref_plus_one(l_substitution_hdr_rec.original_inventory_item_id);
884 FETCH get_max_pref_plus_one INTO l_substitution_hdr_rec.preference;
885 CLOSE get_max_pref_plus_one;
886
887 Create_substitution_header
888 ( p_substitution_id => l_substitution_id
889 , p_substitution_hdr_rec => l_substitution_hdr_rec
890 , x_message_count => x_message_count
891 , x_message_list => x_message_list
892 , x_return_status => x_return_status
893 );
894
895 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
896 RAISE substitution_creation_failure;
897 END IF;
898
899 -- Insert substitution detail
900 -- set the primary key
901 select gmd_item_substitution_dtl_s.nextval
902 into l_substitution_line_id
903 from dual;
904
905 Create_substitution_detail
906 ( p_substitution_line_id => l_substitution_line_id
907 , p_substitution_id => l_substitution_id
908 , p_substitution_dtl_rec => l_substitution_dtl_rec
909 , x_message_count => x_message_count
910 , x_message_list => x_message_list
911 , x_return_status => x_return_status
912 );
913
914 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
915 RAISE substitution_creation_failure;
916 END IF;
917
918 Create_formula_association
919 ( p_substitution_id => l_substitution_id
920 , p_formula_substitution_tbl => l_formula_substitution_tbl
921 , x_message_count => x_message_count
922 , x_message_list => x_message_list
923 , x_return_status => x_return_status
924 );
925
926 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
927 RAISE substitution_creation_failure;
928 END IF;
929
930 -- Assign the new substitution version
931 x_new_substitution_version := l_substitution_hdr_rec.substitution_version;
932
933 /* Get the messgae list and count generated by this API */
934 fnd_msg_pub.count_and_get (
935 p_count => x_message_count
936 ,p_encoded => FND_API.g_false
937 ,p_data => x_message_list);
938 EXCEPTION
939 WHEN substitution_creation_failure THEN
940 x_return_status := FND_API.G_RET_STS_ERROR;
941 fnd_msg_pub.count_and_get (
942 p_count => x_message_count
943 ,p_encoded => FND_API.g_false
944 ,p_data => x_message_list);
945 WHEN OTHERS THEN
946 fnd_msg_pub.add_exc_msg (gmd_substitution_pvt.m_pkg_name, l_api_name);
947 x_return_status := FND_API.g_ret_sts_unexp_error;
948 fnd_msg_pub.count_and_get (
949 p_count => x_message_count
950 ,p_encoded => FND_API.g_false
951 ,p_data => x_message_list);
952 END Copy_substitution;
953
954 END GMD_SUBSTITUTION_PVT;