[Home] [Help]
PACKAGE BODY: APPS.GMD_SUBSTITUTION_PUB
Source
1 PACKAGE BODY GMD_SUBSTITUTION_PUB AS
2 /* $Header: GMDPSUBB.pls 120.0.12000000.1 2007/01/31 16:16:43 appldev noship $ */
3
4 -- Common cursors used
5 CURSOR get_substitution_id(vSubstitution_name VARCHAR2
6 ,vSubstitution_version NUMBER) IS
7 SELECT substitution_id
8 FROM gmd_item_substitution_hdr_b
9 WHERE substitution_name = vSubstitution_name
10 AND substitution_version = vSubstitution_version;
11
12 CURSOR validate_formula_item(vFormula_id NUMBER,
13 vOriginal_item_id NUMBER) IS
14 SELECT 1
15 FROM fm_matl_dtl
16 WHERE formula_id = vformula_id
17 AND inventory_item_id = vOriginal_item_id
18 AND line_type = -1
19 AND rownum = 1;
20
21 CURSOR validate_formula(vFormula_id NUMBER) IS
22 SELECT 1
23 FROM fm_form_mst_b
24 WHERE formula_id = vformula_id
25 AND delete_mark = 0
26 AND formula_status <> 1000;
27
28 CURSOR get_formula_no_vers(vFormula_id NUMBER) IS
29 SELECT formula_no, Formula_vers
30 FRom fm_form_mst_b
31 WHERE FORMULA_id = vFormula_id;
32
33 Cursor get_item_info(vItem_id NUMBER) IS
34 SELECT concatenated_segments, primary_uom_code
35 FROM mtl_system_items_kfv
36 WHERE inventory_item_id = vItem_id;
37
38
39 /* =============================================================== */
40 /* Procedure: */
41 /* is_update_allowed */
42 /* */
43 /* DESCRIPTION: Private function */
44 /* */
45 /* */
46 /* History : */
47 /* Raju 09-OCT-06 Initial implementation */
48 /* =============================================================== */
49
50 FUNCTION is_update_allowed(p_substitution_id IN NUMBER)
51 RETURN BOOLEAN IS
52 CURSOR get_subs_info(vSubstitution_id NUMBER) IS
53 SELECT substitution_status
54 FROM gmd_item_substitution_hdr_b
55 WHERE substitution_id = p_substitution_id;
56
57 l_status_code GMD_STATUS.Status_Code%TYPE;
58 l_delete_mark NUMBER := 0;
59 BEGIN
60 OPEN get_subs_info(p_substitution_id);
61 FETCH get_subs_info INTO l_status_code;
62 CLOSE get_subs_info;
63
64 IF ((l_status_code between 200 and 299) OR
65 (l_status_code >= 800) OR
66 (l_status_code between 500 and 599)) THEN
67 FND_MESSAGE.SET_NAME('GMD','GMD_SUBS_CANNOT_UPD');
68 FND_MSG_PUB.ADD;
69 Return FALSE;
70 END IF;
71
72 Return TRUE;
73 END is_update_allowed;
74
75
76 /* =============================================================== */
77 /* Procedure: */
78 /* Create_substitution */
79 /* */
80 /* DESCRIPTION: */
81 /* */
82 /* */
83 /* History : */
84 /* Raju 09-OCT-06 Initial implementation */
85 /* */
86 /* Description */
87 /* Creates substitution header, details and formulas associated */
88 /* to the item substitution */
89 /* */
90 /* =============================================================== */
91 PROCEDURE Create_substitution
92 (
93 p_api_version IN NUMBER
94 , p_init_msg_list IN VARCHAR2
95 , p_commit IN VARCHAR2
96 , p_substitution_hdr_rec IN gmd_substitution_hdr_rec_type
97 , p_substitution_dtl_rec IN gmd_substitution_dtl_rec_type
98 , p_formula_substitution_tbl IN gmd_formula_substitution_tab
99 , x_message_count OUT NOCOPY NUMBER
100 , x_message_list OUT NOCOPY VARCHAR2
101 , x_return_status OUT NOCOPY VARCHAR2
102 ) IS
103
104 Cursor get_item_info1(vItem_id NUMBER) IS
105 SELECT inventory_item_id, primary_uom_code
106 FROM mtl_system_items_kfv
107 WHERE inventory_item_id = vItem_id;
108
109 Cursor get_item_info2(vItem_no VARCHAR2) IS
110 SELECT inventory_item_id, primary_uom_code
111 FROM mtl_system_items_kfv
112 WHERE concatenated_segments = vItem_no;
113
114 Cursor get_item_orgn(vOrgn_id NUMBER) IS
115 SELECT organization_code
116 FROM org_organization_definitions
117 WHERE organization_id = vOrgn_id;
118
119 CURSOR check_for_date_overlap(vSubstitution_id NUMBER
120 ,vOriginal_item_id NUMBER
121 ,vPreference NUMBER
122 ,vStart_date DATE
123 ,vEnd_date DATE) IS
124 SELECT 1
125 FROM gmd_item_substitution_hdr_b
126 WHERE substitution_id <> vSubstitution_id
127 AND original_inventory_item_id = vOriginal_item_id
128 AND preference = vPreference
129 AND vStart_date >= start_date
130 AND substitution_status < 1000
131 AND (end_date IS NULL OR vEnd_date <= end_date);
132
133 CURSOR Cur_check_item(v_organization_id IN NUMBER DEFAULT NULL,
134 v_inventory_item_id IN NUMBER DEFAULT NULL) IS
135 SELECT INVENTORY_ITEM_ID
136 FROM mtl_system_items_b
137 WHERE inventory_item_id = v_inventory_item_id
138 AND organization_id = v_organization_id
139 AND recipe_enabled_flag = 'Y';
140
141 -- local variables
142 l_item_id NUMBER;
143 l_original_prim_item_um VARCHAR2(3);
144 l_substitute_prim_item_um VARCHAR2(3);
145 l_dummy NUMBER := 0;
146 l_ret NUMBER := NULL;
147 l_organization_code VARCHAR2(3);
148 l_api_name CONSTANT VARCHAR2(30) := 'Create_substitution';
149
150 l_substitution_id NUMBER;
151 l_substitution_line_id NUMBER;
152 l_formula_substitution_id NUMBER;
153
154 -- get a record type
155 l_substitution_hdr_rec gmd_substitution_hdr_rec_type;
156 l_substitution_dtl_rec gmd_substitution_dtl_rec_type;
157 l_formula_substitution_rec gmd_fmsubstitution_rec_type;
158 l_formula_substitution_tbl gmd_formula_substitution_tab;
159
160 -- Exception declaration
161 substitution_creation_failure EXCEPTION;
162 invalid_version EXCEPTION;
163 setup_failure EXCEPTION;
164 BEGIN
165 SAVEPOINT substitution_api;
166
167 -- Set the return status to success initially
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169 -- Assigning local record types for manipulation of data values
170 l_substitution_hdr_rec := p_substitution_hdr_rec;
171 l_substitution_dtl_rec := p_substitution_dtl_rec;
172
173 /* Initialize message list and count if needed */
174 IF (p_init_msg_list = FND_API.g_true) THEN
175 fnd_msg_pub.initialize;
176 END IF;
177
178 /* Intialize the setup fields */
179 IF NOT gmd_api_grp.setup_done THEN
180 gmd_api_grp.setup_done := gmd_api_grp.setup;
181 END IF;
182 IF NOT gmd_api_grp.setup_done THEN
183 RAISE setup_failure;
184 END IF;
185
186 /* Make sure we are call compatible */
187 IF NOT FND_API.compatible_api_call ( 1.0
188 ,p_api_version
189 ,'Create_substitution'
190 ,gmd_substitution_pub.m_pkg_name) THEN
191 RAISE invalid_version;
192 END IF;
193
194 /* Required fields at header level */
195 -- substitution_name and Substitution_version
196 IF l_substitution_hdr_rec.substitution_name IS NULL THEN
197 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
198 FND_MESSAGE.SET_TOKEN ('MISSING', 'SUBSTITUTION_NAME');
199 FND_MSG_PUB.ADD;
200 RAISE substitution_creation_failure;
201 END IF;
202
203 IF l_substitution_hdr_rec.substitution_version IS NULL THEN
204 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
205 FND_MESSAGE.SET_TOKEN ('MISSING', 'SUBSTITUTION_VERSION');
206 FND_MSG_PUB.ADD;
207 RAISE substitution_creation_failure;
208 ELSIF (l_substitution_hdr_rec.substitution_version < 0 ) THEN
209 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
210 FND_MESSAGE.SET_TOKEN ('FIELD', 'SUBSTITUTION_VERSION');
211 FND_MSG_PUB.ADD;
212 RAISE substitution_creation_failure;
213 END IF;
214
215 -- get the substitution_id from sequence
216 select gmd_item_substitution_hdr_s.nextval
217 into l_substitution_id
218 from dual;
219
220 -- substitution_description
221 IF l_substitution_hdr_rec.substitution_description IS NULL THEN
222 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
223 FND_MESSAGE.SET_TOKEN ('MISSING', 'SUBSTITUTION_DESCRIPTION');
224 FND_MSG_PUB.ADD;
225 RAISE substitution_creation_failure;
226 END IF;
227
228 -- original_item_id
229 IF ((l_substitution_hdr_rec.original_inventory_item_id IS NULL) AND
230 (l_substitution_hdr_rec.original_item_no IS NULL)) THEN
231 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
232 FND_MESSAGE.SET_TOKEN ('MISSING', 'ORIGINAL_ITEM_ID');
233 FND_MSG_PUB.ADD;
234 RAISE substitution_creation_failure;
235 ELSIF (l_substitution_hdr_rec.original_inventory_item_id IS NULL) THEN
236 OPEN get_item_info2(l_substitution_hdr_rec.original_item_no);
237 FETCH get_item_info2
238 INTO l_substitution_hdr_rec.original_inventory_item_id,
239 l_original_prim_item_um;
240 CLOSE get_item_info2;
241 ELSIF (l_substitution_hdr_rec.original_item_no IS NULL) THEN
242 OPEN get_item_info1(l_substitution_hdr_rec.original_inventory_item_id);
243 FETCH get_item_info1
244 INTO l_substitution_hdr_rec.original_item_no,
245 l_original_prim_item_um;
246 CLOSE get_item_info1;
247 END IF;
248 IF (l_original_prim_item_um IS NULL) THEN
249 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_ORIGINAL_ITEM');
250 FND_MESSAGE.SET_TOKEN('ORIGINAL_ITEM_NO', l_substitution_hdr_rec.original_item_no);
251 FND_MSG_PUB.ADD;
252 RAISE substitution_creation_failure;
253 END IF;
254
255 -- original_qty
256 IF l_substitution_hdr_rec.original_qty IS NULL THEN
257 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
258 FND_MESSAGE.SET_TOKEN ('MISSING', 'ORIGINAL_QTY');
259 FND_MSG_PUB.ADD;
260 RAISE substitution_creation_failure;
261 ELSIF (l_substitution_hdr_rec.original_qty < 0 ) THEN
262 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
263 FND_MESSAGE.SET_TOKEN ('FIELD', 'ORIGINAL_QTY');
264 FND_MSG_PUB.ADD;
265 RAISE substitution_creation_failure;
266 END IF;
267
268 -- preference
269 IF l_substitution_hdr_rec.preference IS NULL THEN
270 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
271 FND_MESSAGE.SET_TOKEN ('MISSING', 'PREFERENCE');
272 FND_MSG_PUB.ADD;
273 RAISE substitution_creation_failure;
274 ELSIF (l_substitution_hdr_rec.preference < 0 ) THEN
275 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
276 FND_MESSAGE.SET_TOKEN ('FIELD', 'PREFERENCE');
277 FND_MSG_PUB.ADD;
278 RAISE substitution_creation_failure;
279 END IF;
280
281 -- replacement_uom_type -- Default it to value = 1 (original Item uom)
282 IF l_substitution_hdr_rec.replacement_uom_type IS NULL THEN
283 l_substitution_hdr_rec.replacement_uom_type := 1;
284 ELSIF (l_substitution_hdr_rec.replacement_uom_type < 0) THEN
285 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
286 FND_MESSAGE.SET_TOKEN ('FIELD', 'REPLACEMENT_UOM_TYPE');
287 FND_MSG_PUB.ADD;
288 RAISE substitution_creation_failure;
289 ELSIF (l_substitution_hdr_rec.replacement_uom_type > 2) THEN
290 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INV_REPLACEMENT_TYPE');
291 FND_MSG_PUB.ADD;
292 RAISE substitution_creation_failure;
293 END IF;
294
295 --Check that organization id is not null if raise an error message
296 IF (l_substitution_hdr_rec.owner_organization_id IS NULL) THEN
297 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_ORGANIZATION_ID');
298 FND_MSG_PUB.Add;
299 RAISE substitution_creation_failure;
300 ELSE
301 --Check the organization id passed is process enabled if not raise an error message
302 IF NOT (gmd_api_grp.check_orgn_status(l_substitution_hdr_rec.owner_organization_id)) THEN
303 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ORGANIZATION_ID');
304 FND_MESSAGE.SET_TOKEN('ORGN_ID', l_substitution_hdr_rec.owner_organization_id);
305 FND_MSG_PUB.Add;
306 RAISE substitution_creation_failure;
307 END IF;
308 OPEN get_item_orgn(l_substitution_hdr_rec.owner_organization_id);
309 FETCH get_item_orgn INTO l_organization_code;
310 CLOSE get_item_orgn;
311 END IF;
312
313 -- Set the standard who columns
314 l_substitution_hdr_rec.creation_date := sysdate;
315 l_substitution_hdr_rec.created_by := gmd_api_grp.user_id;
316 l_substitution_hdr_rec.last_update_date := sysdate;
317 l_substitution_hdr_rec.last_updated_by := gmd_api_grp.user_id;
318 l_substitution_hdr_rec.last_update_login := gmd_api_grp.login_id;
319
320 /* Business Rules at header level */
321 -- Validation 1
322 -- Check if the substitution exists
323 -- substitution_name and Substitution_version should be unique
324 OPEN get_substitution_id (l_substitution_hdr_rec.substitution_name
325 ,l_substitution_hdr_rec.substitution_version);
326 FETCH get_substitution_id INTO l_dummy;
327 CLOSE get_substitution_id;
328
329 IF (l_dummy > 0) THEN
330 FND_MESSAGE.SET_NAME('GMD', 'GMD_ITSUB_UNIQUE_SUBS_VER');
331 FND_MESSAGE.SET_TOKEN('SUBSNAM',l_substitution_hdr_rec.substitution_name);
332 FND_MESSAGE.SET_TOKEN('VERNAME',l_substitution_hdr_rec.substitution_version);
333 FND_MSG_PUB.ADD;
334 RAISE substitution_creation_failure;
335 END IF;
336
337 -- Validation 2
338 -- Combination of item, date range and preference would be considered for
339 -- uniquess of a list.
340 OPEN check_for_date_overlap( l_substitution_id
341 ,l_substitution_hdr_rec.Original_inventory_item_id
342 ,l_substitution_hdr_rec.Preference
343 ,l_substitution_hdr_rec.Start_date
344 ,l_substitution_hdr_rec.End_date);
345 FETCH check_for_date_overlap INTO l_dummy;
346 CLOSE check_for_date_overlap;
347
348 IF (l_dummy > 0) THEN
349 FND_MESSAGE.SET_NAME('GMD','GMD_ITSUB_DATE_PRE_OVERLAP');
350 FND_MSG_PUB.ADD;
351 RAISE substitution_creation_failure;
352 END IF;
353
354 -- Validation 3
355 -- Start date should be less than End date
356 IF l_substitution_hdr_rec.end_date IS NOT NULL THEN
357 /* End date must be greater than start date, otherwise give error */
358 IF l_substitution_hdr_rec.end_date < l_substitution_hdr_rec.start_date THEN
359 FND_MESSAGE.SET_NAME('GMD', 'QC_MIN_MAX_DATE');
360 FND_MSG_PUB.ADD;
361 RAISE substitution_creation_failure;
362 END IF;
363 END IF;
364
365 -- Validation 4
366 -- Check the Organization Access to the responsibility
367 IF NOT (GMD_API_GRP.orgnaccessible (l_substitution_hdr_rec.owner_organization_id)) THEN
368 RAISE substitution_creation_failure;
369 END IF;
370
371 --Validation 5
372 -- Check that organization has the access to item passed in the header.
373 OPEN Cur_check_item(l_substitution_hdr_rec.owner_organization_id,
374 l_substitution_hdr_rec.Original_inventory_item_id);
375 FETCH Cur_check_item INTO l_ret;
376 IF L_RET IS NULL THEN
377 FND_MESSAGE.SET_NAME('GMD', 'GMD_ITEM_ORG_NOT_FOUND');
378 FND_MESSAGE.SET_TOKEN('ORGN',l_organization_code);
379 FND_MESSAGE.SET_TOKEN('ITEM',l_substitution_hdr_rec.original_item_no);
380 FND_MSG_PUB.ADD;
381 CLOSE cur_check_item;
382 RAISE substitution_creation_failure;
383 END IF;
384 CLOSE cur_check_item;
385
386 -- Call the item substitution header Pvt API
387 GMD_SUBSTITUTION_PVT.Create_substitution_header
388 ( p_substitution_id => l_substitution_id
389 , p_substitution_hdr_rec => l_substitution_hdr_rec
390 , x_message_count => x_message_count
391 , x_message_list => x_message_list
392 , x_return_status => x_return_status
393 );
394
395 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
396 RAISE substitution_creation_failure;
397 END IF;
398
399 /* Required fields at detail level */
400 -- set the primary key
401 select gmd_item_substitution_dtl_s.nextval
402 into l_substitution_line_id
403 from dual;
404
405 -- substitute item_id
406 IF ((l_substitution_dtl_rec.inventory_item_id IS NULL) AND
407 (l_substitution_dtl_rec.item_no IS NULL)) THEN
408 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
409 FND_MESSAGE.SET_TOKEN ('MISSING', 'SUBSTITUTE_ITEM_ID');
410 FND_MSG_PUB.ADD;
411 RAISE substitution_creation_failure;
412 ELSIF (l_substitution_dtl_rec.inventory_item_id IS NULL) THEN
413 OPEN get_item_info2(l_substitution_dtl_rec.item_no);
414 FETCH get_item_info2
415 INTO l_substitution_dtl_rec.inventory_item_id,
416 l_substitute_prim_item_um;
417 CLOSE get_item_info2;
418 ELSIF (l_substitution_dtl_rec.item_no IS NULL) THEN
419 OPEN get_item_info1(l_substitution_dtl_rec.inventory_item_id);
420 FETCH get_item_info1
421 INTO l_substitution_dtl_rec.item_no,
422 l_substitute_prim_item_um;
423 CLOSE get_item_info1;
424 END IF;
425
426 IF (l_substitute_prim_item_um IS NULL) THEN
427 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_SUBSTITUTE_ITEM');
428 FND_MESSAGE.SET_TOKEN('SUBSTITUTE_ITEM_NO',l_substitution_dtl_rec.item_no);
429 FND_MSG_PUB.ADD;
430 RAISE substitution_creation_failure;
431 END IF;
432
433 -- unit_qty
434 IF l_substitution_dtl_rec.unit_qty IS NULL THEN
435 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
436 FND_MESSAGE.SET_TOKEN ('MISSING', 'UNIT_QTY');
437 FND_MSG_PUB.ADD;
438 RAISE substitution_creation_failure;
439 ELSIF (l_substitution_dtl_rec.unit_qty < 0 ) THEN
440 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
441 FND_MESSAGE.SET_TOKEN ('FIELD', 'UNIT_QTY');
442 FND_MSG_PUB.ADD;
443 RAISE substitution_creation_failure;
444 END IF;
445
446 -- item_uom
447 IF l_substitution_dtl_rec.detail_uom IS NULL THEN
448 l_substitution_dtl_rec.detail_uom := l_substitute_prim_item_um;
449 ELSE
450 IF (NOT(gma_valid_grp.validate_um(l_substitution_dtl_rec.detail_uom))) THEN
451 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
452 FND_MSG_PUB.ADD;
453 RAISE substitution_creation_failure;
454 END IF;
455 END IF;
456
457 -- Set the standard who columns
458 l_substitution_dtl_rec.creation_date := sysdate;
459 l_substitution_dtl_rec.created_by := gmd_api_grp.user_id;
460 l_substitution_dtl_rec.last_update_date := sysdate;
461 l_substitution_dtl_rec.last_updated_by := gmd_api_grp.user_id;
462 l_substitution_dtl_rec.last_update_login := gmd_api_grp.login_id;
463
464 /* Business Rules at Detail level */
465 -- Validation 1
466
467 -- Detail item uom validation
468 l_dummy := INV_CONVERT.inv_um_convert (item_id => l_substitution_dtl_rec.inventory_item_id
469 ,precision => 5
470 ,from_quantity => 100
471 ,from_unit => l_substitution_dtl_rec.detail_uom
472 ,to_unit => l_substitute_prim_item_um
473 ,from_name => NULL
474 ,to_name => NULL);
475 IF l_dummy < 0 THEN
476 FND_MESSAGE.SET_NAME('GMD','FM_SCALE_BAD_ITEM_UOM');
477 FND_MESSAGE.SET_TOKEN('FROM_UOM', l_substitution_dtl_rec.detail_uom);
478 FND_MESSAGE.SET_TOKEN('TO_UOM', l_substitute_prim_item_um);
479 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_substitution_dtl_rec.item_no);
480 FND_MSG_PUB.ADD;
481 RAISE substitution_creation_failure;
482 END IF;
483
484 IF (l_substitution_hdr_rec.replacement_uom_type = 2) THEN
485 l_dummy :=
486 INV_CONVERT.inv_um_convert (item_id => l_substitution_dtl_rec.inventory_item_id
487 ,precision => 5
488 ,from_quantity => 100
489 ,from_unit => l_substitution_dtl_rec.detail_uom
490 ,to_unit => l_original_prim_item_um
491 ,from_name => NULL
492 ,to_name => NULL);
493 IF l_dummy < 0 THEN
494 FND_MESSAGE.SET_NAME('GMD','FM_SCALE_BAD_ITEM_UOM');
495 FND_MESSAGE.SET_TOKEN('FROM_UOM', l_substitution_dtl_rec.detail_uom);
496 FND_MESSAGE.SET_TOKEN('TO_UOM', l_original_prim_item_um);
497 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_substitution_dtl_rec.item_no);
498 FND_MSG_PUB.ADD;
499 RAISE substitution_creation_failure;
500 END IF;
501 END IF;
502 -- reset l_dummy
503 l_dummy := 0;
504
505 -- Check that organization has the access to item passed in the detail.
506 OPEN Cur_check_item(l_substitution_hdr_rec.owner_organization_id,
507 l_substitution_dtl_rec.inventory_item_id);
508 FETCH Cur_check_item INTO l_ret;
509 IF L_RET IS NULL THEN
510 FND_MESSAGE.SET_NAME('GMD', 'GMD_ITEM_ORG_NOT_FOUND');
511 FND_MESSAGE.SET_TOKEN('ORGN',l_organization_code);
512 FND_MESSAGE.SET_TOKEN('ITEM',l_substitution_dtl_rec.item_no);
513 FND_MSG_PUB.ADD;
514 CLOSE cur_check_item;
515 RAISE substitution_creation_failure;
516 END IF;
517 CLOSE cur_check_item;
518
519 -- call the item substitution dtl pvt API
520 GMD_SUBSTITUTION_PVT.Create_substitution_detail
521 ( p_substitution_line_id => l_substitution_line_id
522 , p_substitution_id => l_substitution_id
523 , p_substitution_dtl_rec => l_substitution_dtl_rec
524 , x_message_count => x_message_count
525 , x_message_list => x_message_list
526 , x_return_status => x_return_status
527 );
528
529 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
530 RAISE substitution_creation_failure;
531 END IF;
532
533 FOR i in 1 .. p_formula_substitution_tbl.count LOOP
534 -- each row to a local record for manipulation of data
535 l_formula_substitution_rec := p_formula_substitution_tbl(i);
536
537 -- formula_id or formula_no/formule_vers combination
538 IF (l_formula_substitution_rec.formula_id IS NULL) AND
539 (l_formula_substitution_rec.formula_no IS NULL OR
540 l_formula_substitution_rec.formula_vers IS NULL) THEN
541 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
542 FND_MESSAGE.SET_TOKEN ('MISSING', 'FORMULA_ID');
543 FND_MSG_PUB.ADD;
544 RAISE substitution_creation_failure;
545 ELSIF (l_formula_substitution_rec.formula_id IS NULL) THEN
546 -- Get the formula id
547 GMDFMVAL_PUB.get_formula_id
548 (pformula_no => l_formula_substitution_rec.formula_no
549 ,pversion => l_formula_substitution_rec.formula_vers
550 ,xvalue => l_formula_substitution_rec.formula_id
551 ,xreturn_code => l_dummy);
552 IF (l_dummy < 0) THEN
553 FND_MESSAGE.SET_NAME ('GMD', 'QC_INVALID_FORMULA');
554 FND_MSG_PUB.ADD;
555 RAISE substitution_creation_failure;
556 END IF;
557 ELSE
558 -- get formula no and version
559 OPEN get_formula_no_vers(l_formula_substitution_rec.formula_id);
560 FETCH get_formula_no_vers INTO l_formula_substitution_rec.formula_no,
561 l_formula_substitution_rec.formula_vers;
562 CLOSE get_formula_no_vers;
563 END IF;
564
565 -- This formula should remain active (not deleted) and not obsoleted
566 OPEN validate_formula(l_formula_substitution_rec.formula_id);
567 FETCH validate_formula INTO l_dummy;
568 CLOSE validate_formula;
569
570 IF (l_dummy <> 1) THEN
571 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INACTIVE_FMSUB');
572 FND_MESSAGE.SET_TOKEN('FORMULA_VERSION',l_formula_substitution_rec.formula_vers);
573 FND_MESSAGE.SET_TOKEN('FORMULA_NO',l_formula_substitution_rec.formula_no);
574 FND_MSG_PUB.ADD;
575 RAISE substitution_creation_failure;
576 END IF;
577 -- reset l_dummy
578 l_dummy := 0;
579
580 /* Business Rules at formula substitution association level */
581 -- Validation 1
582 -- There formula the substitution is associated to should have the
583 -- original item as its ingredient.
584 OPEN validate_formula_item(l_formula_substitution_rec.formula_id
585 ,l_substitution_hdr_rec.original_inventory_item_id);
586 FETCH validate_formula_item INTO l_dummy;
587 CLOSE validate_formula_item;
588
589 IF (l_dummy <> 1) THEN
590 FND_MESSAGE.SET_NAME ('GMD', 'GMD_FMSUB_INGR_MISSING');
591 FND_MESSAGE.SET_TOKEN('FORMULA_VERSION',l_formula_substitution_rec.formula_vers);
592 FND_MESSAGE.SET_TOKEN('FORMULA_NO',l_formula_substitution_rec.formula_no);
593 FND_MSG_PUB.ADD;
594 RAISE substitution_creation_failure;
595 END IF;
596 -- reset l_dummy
597 l_dummy := 0;
598
599 -- Set the standard who columns
600 l_formula_substitution_rec.creation_date := sysdate;
601 l_formula_substitution_rec.created_by := gmd_api_grp.user_id;
602 l_formula_substitution_rec.last_update_date := sysdate;
603 l_formula_substitution_rec.last_updated_by := gmd_api_grp.user_id;
604 l_formula_substitution_rec.last_update_login := gmd_api_grp.login_id;
605
606 l_formula_substitution_tbl(i) := l_formula_substitution_rec;
607 END LOOP;
608
609 -- Call the insert formula subtitution association Pvt API
610 GMD_SUBSTITUTION_PVT.Create_formula_association
611 ( p_substitution_id => l_substitution_id
612 , p_formula_substitution_tbl => l_formula_substitution_tbl
613 , x_message_count => x_message_count
614 , x_message_list => x_message_list
615 , x_return_status => x_return_status
616 );
617
618 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
619 RAISE substitution_creation_failure;
620 END IF;
621
622
623 IF (p_commit = FND_API.g_true) THEN
624 Commit;
625 END IF;
626 EXCEPTION
627 WHEN substitution_creation_failure OR invalid_version OR setup_failure THEN
628 x_return_status := FND_API.g_ret_sts_error;
629 fnd_msg_pub.count_and_get (
630 p_count => x_message_count
631 ,p_encoded => FND_API.g_false
632 ,p_data => x_message_list);
633 ROLLBACK TO SAVEPOINT substitution_api;
634 WHEN OTHERS THEN
635 x_return_status := FND_API.g_ret_sts_unexp_error;
636 fnd_msg_pub.add_exc_msg (gmd_substitution_pub.m_pkg_name, l_api_name);
637 fnd_msg_pub.count_and_get (
638 p_count => x_message_count
639 ,p_encoded => FND_API.g_false
640 ,p_data => x_message_list);
641 ROLLBACK TO SAVEPOINT substitution_api;
642 END Create_substitution;
643
644 /* =============================================================== */
645 /* Procedure: */
646 /* Create_formula_association */
647 /* */
648 /* DESCRIPTION: */
649 /* */
650 /* */
651 /* History : */
652 /* Rajender Nalla 09-OCT-06 Initial implementation. */
653 /* =============================================================== */
654 PROCEDURE Create_formula_association
655 ( p_api_version IN NUMBER
656 , p_init_msg_list IN VARCHAR2
657 , p_commit IN VARCHAR2
658 , p_substitution_id IN NUMBER Default NULL
659 , p_substitution_name IN VARCHAR2 Default NULL
660 , p_substitution_version IN NUMBER Default NULL
661 , p_formula_substitution_tbl IN gmd_formula_substitution_tab
662 , x_message_count OUT NOCOPY NUMBER
663 , x_message_list OUT NOCOPY VARCHAR2
664 , x_return_status OUT NOCOPY VARCHAR2
665 ) IS
666 -- Cursor definition
667 CURSOR validate_formula_substitution(vSubstitution_id NUMBER
668 ,vFormula_id NUMBER) IS
669 Select 1
670 From gmd_formula_substitution
671 Where formula_id = vformula_id
672 AND substitution_id = vSubstitution_id;
673
674 -- local variable
675 l_dummy NUMBER := 0;
676 l_formula_substitution_rec gmd_fmsubstitution_rec_type;
677 l_api_name CONSTANT VARCHAR2(30) := 'Create_formula_association';
678
679 l_substitution_id NUMBER;
680 l_formula_substitution_tbl gmd_formula_substitution_tab;
681
682 -- Exception declaration
683 substitution_creation_failure EXCEPTION;
684 invalid_version EXCEPTION;
685 setup_failure EXCEPTION;
686
687 -- internal function
688 FUNCTION get_original_item_id(vSubstitution_id NUMBER)
689 RETURN NUMBER IS
690 l_item_id NUMBER := 0;
691 BEGIN
692 SELECT original_inventory_item_id INTO l_item_id
693 FROM gmd_item_substitution_hdr_b
694 Where substitution_id = vSubstitution_id;
695
696 RETURN l_item_id;
697 END get_original_item_id;
698 BEGIN
699 SAVEPOINT substitution_api;
700
701 /* Set the return status to success initially */
702 x_return_status := FND_API.G_RET_STS_SUCCESS;
703
704 /* Initialize message list and count if needed */
705 IF (p_init_msg_list = FND_API.G_True) THEN
706 fnd_msg_pub.initialize;
707 END IF;
708
709 /* Intialize the setup fields */
710 IF NOT gmd_api_grp.setup_done THEN
711 gmd_api_grp.setup_done := gmd_api_grp.setup;
712 END IF;
713 IF NOT gmd_api_grp.setup_done THEN
714 RAISE setup_failure;
715 END IF;
716
717 /* Make sure we are call compatible */
718 IF NOT FND_API.compatible_api_call ( 1.0
719 ,p_api_version
720 ,'Create_formula_association'
721 ,gmd_substitution_pub.m_pkg_name) THEN
722 RAISE invalid_version;
723 END IF;
724
725 -- Substitution id or (substitution_name and Substitution_version)
726 IF ((p_substitution_id IS NULL) AND
727 (p_substitution_name IS NULL OR
728 p_substitution_version IS NULL)) THEN
729 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
730 FND_MESSAGE.SET_TOKEN ('MISSING', 'SUBSTITUTION_ID');
731 FND_MSG_PUB.ADD;
732 RAISE substitution_creation_failure;
733 ELSIF (p_substitution_id IS NULL) THEN
734 -- Get the subsitution id
735 OPEN get_substitution_id(p_substitution_name
736 ,p_substitution_version);
737 FETCH get_substitution_id INTO l_substitution_id;
738 IF (get_substitution_id%NOTFOUND) THEN
739 CLOSE get_substitution_id;
740 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_SUBSTITUTION');
741 FND_MSG_PUB.ADD;
742 RAISE substitution_creation_failure;
743 END IF;
744 CLOSE get_substitution_id;
745 ELSE
746 l_substitution_id := p_substitution_id;
747 END IF;
748
749 -- prevent updates or modification of pending obsolete status
750 IF NOT is_update_allowed(l_substitution_id) THEN
751 RAISE substitution_creation_failure;
752 END IF;
753
754 FOR i in 1 .. p_formula_substitution_tbl.count LOOP
755 -- Assign each table row to temp local record
756 l_formula_substitution_rec := p_formula_substitution_tbl(i);
757
758 -- formula_id or (formula_no and formula_version combination
759 IF ((l_formula_substitution_rec.formula_id IS NULL) AND
760 (l_formula_substitution_rec.formula_no IS NULL OR
761 l_formula_substitution_rec.formula_vers IS NULL)) THEN
762 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
763 FND_MESSAGE.SET_TOKEN ('MISSING', 'FORMULA_ID');
764 FND_MSG_PUB.ADD;
765 RAISE substitution_creation_failure;
766 ELSIF (l_formula_substitution_rec.formula_id IS NULL) THEN
767 -- Get the formula id
768 GMDFMVAL_PUB.get_formula_id
769 (pformula_no => l_formula_substitution_rec.formula_no
770 ,pversion => l_formula_substitution_rec.formula_vers
771 ,xvalue => l_formula_substitution_rec.formula_id
772 ,xreturn_code => l_dummy);
773 IF (l_dummy < 0) THEN
774 FND_MESSAGE.SET_NAME ('GMD', 'QC_INVALID_FORMULA');
775 FND_MSG_PUB.ADD;
776 RAISE substitution_creation_failure;
777 END IF;
778 -- reset
779 l_dummy := 0;
780 ELSE
781 -- get formula no and version
782 OPEN get_formula_no_vers(l_formula_substitution_rec.formula_id);
783 FETCH get_formula_no_vers INTO l_formula_substitution_rec.formula_no,
784 l_formula_substitution_rec.formula_vers;
785 IF (get_formula_no_vers%NOTFOUND) THEN
786 CLOSE get_formula_no_vers;
787 FND_MESSAGE.SET_NAME ('GMD', 'QC_INVALID_FORMULA');
788 FND_MSG_PUB.ADD;
789 RAISE substitution_creation_failure;
790 END IF;
791 CLOSE get_formula_no_vers;
792 END IF;
793
794 /* Business Rules at formula substitution association level */
795 -- Validation 1
796 -- The formula the substitution is associated to should have the
797 -- original item as its ingredient.
798 OPEN validate_formula_item(
799 l_formula_substitution_rec.formula_id,
800 get_original_item_id(l_substitution_id)
801 );
802 FETCH validate_formula_item INTO l_dummy;
803 CLOSE validate_formula_item;
804
805 IF (l_dummy <> 1) THEN
806 FND_MESSAGE.SET_NAME ('GMD', 'GMD_FMSUB_INGR_MISSING');
807 FND_MESSAGE.SET_TOKEN('FORMULA_VERSION',l_formula_substitution_rec.formula_vers);
808 FND_MESSAGE.SET_TOKEN('FORMULA_NO',l_formula_substitution_rec.formula_no);
809 FND_MSG_PUB.ADD;
810 RAISE substitution_creation_failure;
811 END IF;
812 -- reset l_dummy
813 l_dummy := 0;
814
815 -- This formula should remain active (not deleted) and not obsoleted
816 OPEN validate_formula(l_formula_substitution_rec.formula_id);
817 FETCH validate_formula INTO l_dummy;
818 CLOSE validate_formula;
819
820 IF (l_dummy <> 1) THEN
821 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INACTIVE_FMSUB');
822 FND_MESSAGE.SET_TOKEN('FORMULA_VERSION',l_formula_substitution_rec.formula_vers);
823 FND_MESSAGE.SET_TOKEN('FORMULA_NO',l_formula_substitution_rec.formula_no);
824 FND_MSG_PUB.ADD;
825 RAISE substitution_creation_failure;
826 END IF;
827 -- reset l_dummy
828 l_dummy := 0;
829
830 -- Validation 2
831 -- If the formula has been already associated - error out.
832 OPEN validate_formula_substitution(l_substitution_id
833 ,l_formula_substitution_rec.formula_id);
834 FETCH validate_formula_substitution INTO l_dummy;
835 CLOSE validate_formula_substitution;
836
837 IF (l_dummy = 1) THEN
838 FND_MESSAGE.SET_NAME ('GMD', 'GMD_FMSUB_ASSN_EXISTS');
839 FND_MESSAGE.SET_TOKEN('FORMULA_VERSION',l_formula_substitution_rec.formula_vers);
840 FND_MESSAGE.SET_TOKEN('FORMULA_NO',l_formula_substitution_rec.formula_no);
841 FND_MSG_PUB.ADD;
842 RAISE substitution_creation_failure;
843 END IF;
844
845 -- reset l_dummy
846 l_dummy := 0;
847
848 -- Set the standard who columns
849 l_formula_substitution_rec.creation_date := sysdate;
850 l_formula_substitution_rec.created_by := gmd_api_grp.user_id;
851 l_formula_substitution_rec.last_update_date := sysdate;
852 l_formula_substitution_rec.last_updated_by := gmd_api_grp.user_id;
853 l_formula_substitution_rec.last_update_login := gmd_api_grp.login_id;
854
855 l_formula_substitution_tbl(i) := l_formula_substitution_rec;
856 END LOOP;
857
858 -- Call the insert formula subtitution association Pvt API
859 GMD_SUBSTITUTION_PVT.Create_formula_association
860 ( p_substitution_id => l_substitution_id
861 , p_formula_substitution_tbl => l_formula_substitution_tbl
862 , x_message_count => x_message_count
863 , x_message_list => x_message_list
864 , x_return_status => x_return_status
865 );
866
867 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
868 RAISE substitution_creation_failure;
869 END IF;
870
871 IF (p_commit = FND_API.g_true) THEN
872 Commit;
873 END IF;
874
875 EXCEPTION
876 WHEN substitution_creation_failure OR invalid_version OR setup_failure THEN
877 x_return_status := FND_API.G_RET_STS_ERROR;
878 fnd_msg_pub.count_and_get (
879 p_count => x_message_count
880 ,p_encoded => FND_API.g_false
881 ,p_data => x_message_list);
882 ROLLBACK TO SAVEPOINT substitution_api;
883 WHEN OTHERS THEN
884 x_return_status := FND_API.g_ret_sts_unexp_error;
885 fnd_msg_pub.add_exc_msg (gmd_substitution_pub.m_pkg_name, l_api_name);
886 fnd_msg_pub.count_and_get (
887 p_count => x_message_count
888 ,p_encoded => FND_API.g_false
889 ,p_data => x_message_list);
890 ROLLBACK TO SAVEPOINT substitution_api;
891 END Create_formula_association;
892
893 /* =============================================================== */
894 /* Procedure: */
895 /* Update_substitution_header */
896 /* */
897 /* DESCRIPTION: */
898 /* */
899 /* */
900 /* History : */
901 /* Rajender Nalla 09-OCT-06 Initial implementation. */
902 /* =============================================================== */
903 PROCEDURE Update_substitution_header
904 ( p_api_version IN NUMBER
905 , p_init_msg_list IN VARCHAR2
906 , p_commit IN VARCHAR2
907 , p_substitution_id IN NUMBER Default NULL
908 , p_substitution_name IN VARCHAR2 Default NULL
909 , p_substitution_version IN NUMBER Default NULL
910 , p_update_table IN update_tbl_type
911 , x_message_count OUT NOCOPY NUMBER
912 , x_message_list OUT NOCOPY VARCHAR2
913 , x_return_status OUT NOCOPY VARCHAR2
914 ) IS
915
916 -- Cursor definition
917 CURSOR get_old_substitution_rec(vSubstitution_id NUMBER) IS
918 Select *
919 From gmd_item_substitution_hdr
920 Where substitution_id = vSubstitution_id;
921
922 -- local variables
923 l_substitution_id NUMBER;
924 l_owner_orgn_id NUMBER;
925 l_api_name CONSTANT VARCHAR2(30) := 'Update_substitution_header';
926
927 -- get a record type
928 l_substitution_hdr_rec gmd_item_substitution_hdr%ROWTYPE;
929
930 -- Exception declaration
931 substitution_update_failure EXCEPTION;
932 invalid_version EXCEPTION;
933 setup_failure EXCEPTION;
934 BEGIN
935 SAVEPOINT substitution_api;
936
937 /* Set the return status to success initially */
938 x_return_status := FND_API.G_RET_STS_SUCCESS;
939
940 /* Initialize message list and count if needed */
941 IF (p_init_msg_list = FND_API.G_true) THEN
942 fnd_msg_pub.initialize;
943 END IF;
944
945 /* Intialize the setup fields */
946 IF NOT gmd_api_grp.setup_done THEN
947 gmd_api_grp.setup_done := gmd_api_grp.setup;
948 END IF;
949 IF NOT gmd_api_grp.setup_done THEN
950 RAISE setup_failure;
951 END IF;
952
953 /* Make sure we are call compatible */
954 IF NOT FND_API.compatible_api_call ( 1.0
955 ,p_api_version
956 ,'Update_substitution_header'
957 ,gmd_substitution_pub.m_pkg_name) THEN
958 RAISE invalid_version;
959 END IF;
960
961 /* Required fields at header level */
962 -- Substitution id or (substitution_name and Substitution_version)
963 IF (p_substitution_id IS NULL) THEN
964 IF (p_substitution_name IS NULL) OR (p_substitution_version IS NULL) THEN
965 -- Raise a exception
966 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
967 FND_MESSAGE.SET_TOKEN ('MISSING', 'SUBSTITUTION_ID');
968 FND_MSG_PUB.ADD;
969 RAISE substitution_update_failure;
970 ELSE
971 OPEN get_substitution_id(p_substitution_name, p_substitution_version);
972 FETCH get_substitution_id INTO l_substitution_id;
973 IF (get_substitution_id%NOTFOUND) THEN
974 CLOSE get_substitution_id;
975 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_SUBSTITUTION');
976 FND_MSG_PUB.ADD;
977 RAISE substitution_update_failure;
978 END IF;
979 CLOSE get_substitution_id;
980 END IF;
981 ELSE
982 l_substitution_id := p_substitution_id;
983 END IF;
984
985 -- prevent updates or modification of pending obsolete status
986 IF NOT is_update_allowed(l_substitution_id) THEN
987 RAISE substitution_update_failure;
988 END IF;
989
990 -- Retrieve the old susbtitution record
991 OPEN get_old_substitution_rec(l_substitution_id);
992 FETCH get_old_substitution_rec INTO l_substitution_hdr_rec;
993 IF (get_old_substitution_rec%NOTFOUND) THEN
994 CLOSE get_old_substitution_rec;
995 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_SUBSTITUTION');
996 FND_MSG_PUB.ADD;
997 RAISE substitution_update_failure;
998 END IF;
999 CLOSE get_old_substitution_rec;
1000
1001 /* Business Rules at header level */
1002 FOR i in 1 .. p_update_table.count LOOP
1003 -- Start date should be less than End date
1004 -- Convert the date from canonical format
1005 IF (Upper(p_update_table(i).p_col_to_update) = 'START_DATE') THEN
1006 l_substitution_hdr_rec.start_date :=
1007 FND_DATE.canonical_to_date(p_update_table(i).p_value);
1008 IF (l_substitution_hdr_rec.end_date IS NOT NULL) THEN
1009 IF (l_substitution_hdr_rec.end_date < l_substitution_hdr_rec.start_date) THEN
1010 FND_MESSAGE.SET_NAME('GMD', 'QC_MIN_MAX_DATE');
1011 FND_MSG_PUB.ADD;
1012 RAISE substitution_update_failure;
1013 END IF;
1014 END IF;
1015 ELSIF (Upper(p_update_table(i).p_col_to_update) = 'SUBSTITUTION_DESCRIPTION') THEN
1016 l_substitution_hdr_rec.substitution_description := p_update_table(i).p_value;
1017 ELSIF (Upper(p_update_table(i).p_col_to_update) = 'PREFERENCE') THEN
1018 IF (p_update_table(i).p_value < 0 ) THEN
1019 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
1020 FND_MESSAGE.SET_TOKEN ('FIELD', 'PREFERENCE');
1021 FND_MSG_PUB.ADD;
1022 RAISE substitution_update_failure;
1023 END IF;
1024 l_substitution_hdr_rec.preference := p_update_table(i).p_value;
1025 ELSIF (Upper(p_update_table(i).p_col_to_update) = 'END_DATE') THEN
1026 l_substitution_hdr_rec.end_date :=
1027 FND_DATE.canonical_to_date(p_update_table(i).p_value);
1028 IF (p_update_table(i).p_value IS NOT NULL) THEN
1029 IF (l_substitution_hdr_rec.start_date > l_substitution_hdr_rec.end_date) THEN
1030 FND_MESSAGE.SET_NAME('GMD', 'QC_MIN_MAX_DATE');
1031 FND_MSG_PUB.ADD;
1032 RAISE substitution_update_failure;
1033 END IF;
1034 END IF;
1035 ELSIF (Upper(p_update_table(i).p_col_to_update) = 'ORIGINAL_QTY') THEN
1036 IF (p_update_table(i).p_value < 0) THEN
1037 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
1038 FND_MESSAGE.SET_TOKEN ('FIELD', 'ORIGINAL_QTY');
1039 FND_MSG_PUB.ADD;
1040 RAISE substitution_update_failure;
1041 END IF;
1042 l_substitution_hdr_rec.original_qty := p_update_table(i).p_value;
1043 ELSIF (Upper(p_update_table(i).p_col_to_update) = 'REPLACEMENT_UOM_TYPE') THEN
1044 -- replacement_uom_type -- Default it to value = 1 (original Item uom)
1045 IF (p_update_table(i).p_value < 0) THEN
1046 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
1047 FND_MESSAGE.SET_TOKEN ('FIELD', 'REPLACEMENT_UOM_TYPE');
1048 FND_MSG_PUB.ADD;
1049 RAISE substitution_update_failure;
1050 ELSIF (p_update_table(i).p_value > 2) THEN
1051 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INV_REPLACEMENT_TYPE');
1052 FND_MSG_PUB.ADD;
1053 RAISE substitution_update_failure;
1054 END IF;
1055 l_substitution_hdr_rec.replacement_uom_type := p_update_table(i).p_value;
1056 -- Cannot change the orginal item in substitution.
1057 -- cannot change its original item uom
1058 -- Status cannot be changed - need to use Change Status API
1059 ELSIF (Upper(p_update_table(i).p_col_to_update) IN
1060 ('ORIGINAL_UOM'
1061 ,'OWNER_ORGANIZATION_ID'
1062 ,'ORIGINAL_INVENTORY_ITEM_ID')) THEN
1063 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_COL_UPDATES');
1064 FND_MESSAGE.SET_TOKEN('NAME',p_update_table(i).p_col_to_update);
1065 FND_MSG_PUB.ADD;
1066 RAISE substitution_update_failure;
1067 ELSIF (Upper(p_update_table(i).p_col_to_update) =
1068 ('SUBSTITUTION_STATUS')) THEN
1069 FND_MESSAGE.SET_NAME('GMD', 'GMD_NOT_USE_API_UPD_STATUS');
1070 FND_MSG_PUB.ADD;
1071 RAISE substitution_update_failure;
1072 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE1') THEN
1073 l_substitution_hdr_rec.ATTRIBUTE1 := p_update_table(i).p_value;
1074 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE2') THEN
1075 l_substitution_hdr_rec.ATTRIBUTE2 := p_update_table(i).p_value;
1076 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE3') THEN
1077 l_substitution_hdr_rec.ATTRIBUTE3 := p_update_table(i).p_value;
1078 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE4') THEN
1079 l_substitution_hdr_rec.ATTRIBUTE4 := p_update_table(i).p_value;
1080 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE5') THEN
1081 l_substitution_hdr_rec.ATTRIBUTE5 := p_update_table(i).p_value;
1082 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE6') THEN
1083 l_substitution_hdr_rec.ATTRIBUTE6 := p_update_table(i).p_value;
1084 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE7') THEN
1085 l_substitution_hdr_rec.ATTRIBUTE7 := p_update_table(i).p_value;
1086 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE8') THEN
1087 l_substitution_hdr_rec.ATTRIBUTE8 := p_update_table(i).p_value;
1088 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE9') THEN
1089 l_substitution_hdr_rec.ATTRIBUTE9 := p_update_table(i).p_value;
1090 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE10') THEN
1091 l_substitution_hdr_rec.ATTRIBUTE10 := p_update_table(i).p_value;
1092 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE11') THEN
1093 l_substitution_hdr_rec.ATTRIBUTE11 := p_update_table(i).p_value;
1094 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE12') THEN
1095 l_substitution_hdr_rec.ATTRIBUTE12 := p_update_table(i).p_value;
1096 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE13') THEN
1097 l_substitution_hdr_rec.ATTRIBUTE13 := p_update_table(i).p_value;
1098 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE14') THEN
1099 l_substitution_hdr_rec.ATTRIBUTE14 := p_update_table(i).p_value;
1100 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE15') THEN
1101 l_substitution_hdr_rec.ATTRIBUTE15 := p_update_table(i).p_value;
1102 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE16') THEN
1103 l_substitution_hdr_rec.ATTRIBUTE16 := p_update_table(i).p_value;
1104 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE17') THEN
1105 l_substitution_hdr_rec.ATTRIBUTE17 := p_update_table(i).p_value;
1106 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE18') THEN
1107 l_substitution_hdr_rec.ATTRIBUTE18 := p_update_table(i).p_value;
1108 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE19') THEN
1109 l_substitution_hdr_rec.ATTRIBUTE19 := p_update_table(i).p_value;
1110 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE20') THEN
1111 l_substitution_hdr_rec.ATTRIBUTE20 := p_update_table(i).p_value;
1112 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE21') THEN
1113 l_substitution_hdr_rec.ATTRIBUTE21 := p_update_table(i).p_value;
1114 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE22') THEN
1115 l_substitution_hdr_rec.ATTRIBUTE22 := p_update_table(i).p_value;
1116 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE23') THEN
1117 l_substitution_hdr_rec.ATTRIBUTE23 := p_update_table(i).p_value;
1118 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE24') THEN
1119 l_substitution_hdr_rec.ATTRIBUTE24 := p_update_table(i).p_value;
1120 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE25') THEN
1121 l_substitution_hdr_rec.ATTRIBUTE25 := p_update_table(i).p_value;
1122 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE26') THEN
1123 l_substitution_hdr_rec.ATTRIBUTE26 := p_update_table(i).p_value;
1124 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE27') THEN
1125 l_substitution_hdr_rec.ATTRIBUTE27 := p_update_table(i).p_value;
1126 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE28') THEN
1127 l_substitution_hdr_rec.ATTRIBUTE28 := p_update_table(i).p_value;
1128 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE29') THEN
1129 l_substitution_hdr_rec.ATTRIBUTE29 := p_update_table(i).p_value;
1130 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE30') THEN
1131 l_substitution_hdr_rec.ATTRIBUTE30 := p_update_table(i).p_value;
1132 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE_CATEGORY') THEN
1133 l_substitution_hdr_rec.ATTRIBUTE_CATEGORY := p_update_table(i).p_value;
1134 ELSE
1135 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_UPDCOL_NAME');
1136 FND_MESSAGE.SET_TOKEN('NAME', p_update_table(i).p_col_to_update);
1137 FND_MSG_PUB.ADD;
1138 RAISE substitution_update_failure;
1139 END IF;
1140
1141 -- Assign values
1142 l_substitution_hdr_rec.last_update_date := SYSDATE;
1143 l_substitution_hdr_rec.last_updated_by := gmd_api_grp.user_id;
1144 l_substitution_hdr_rec.last_update_login := gmd_api_grp.login_id;
1145 END LOOP;
1146
1147 --Call the Pvt Substitution header API
1148 GMD_SUBSTITUTION_PVT.Update_substitution_header
1149 ( p_substitution_hdr_rec => l_substitution_hdr_rec
1150 , x_message_count => x_message_count
1151 , x_message_list => x_message_list
1152 , x_return_status => x_return_status
1153 );
1154
1155 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1156 RAISE substitution_update_failure;
1157 END IF;
1158
1159 IF (p_commit = FND_API.g_true) THEN
1160 Commit;
1161 END IF;
1162
1163 EXCEPTION
1164 WHEN substitution_update_failure OR invalid_version OR setup_failure THEN
1165 fnd_msg_pub.count_and_get (
1166 p_count => x_message_count
1167 ,p_encoded => FND_API.g_false
1168 ,p_data => x_message_list);
1169 x_return_status := FND_API.G_RET_STS_ERROR;
1170 ROLLBACK TO SAVEPOINT substitution_api;
1171 WHEN OTHERS THEN
1172 x_return_status := FND_API.g_ret_sts_unexp_error;
1173 fnd_msg_pub.add_exc_msg (gmd_substitution_pub.m_pkg_name, l_api_name);
1174 fnd_msg_pub.count_and_get (
1175 p_count => x_message_count
1176 ,p_encoded => FND_API.g_false
1177 ,p_data => x_message_list);
1178 ROLLBACK TO SAVEPOINT substitution_api;
1179 END Update_substitution_header;
1180
1181
1182 /* =============================================================== */
1183 /* Procedure: */
1184 /* Update_substitution_detail */
1185 /* */
1186 /* DESCRIPTION: */
1187 /* */
1188 /* */
1189 /* History : */
1190 /* Rajender Nalla 09-OCT-06 Initial implementation. */
1191 /* =============================================================== */
1192 PROCEDURE Update_substitution_detail
1193 ( p_api_version IN NUMBER
1194 , p_init_msg_list IN VARCHAR2
1195 , p_commit IN VARCHAR2
1196 , p_substitution_line_id IN NUMBER Default NULL
1197 , p_substitution_id IN NUMBER Default NULL
1198 , p_substitution_name IN VARCHAR2 Default NULL
1199 , p_substitution_version IN NUMBER Default NULL
1200 , p_update_table IN update_tbl_type
1201 , x_message_count OUT NOCOPY NUMBER
1202 , x_message_list OUT NOCOPY VARCHAR2
1203 , x_return_status OUT NOCOPY VARCHAR2
1204 ) IS
1205 CURSOR get_subsdtl_rec_using_line_id(vSubstitution_line_id NUMBER) IS
1206 Select *
1207 From gmd_item_substitution_dtl
1208 Where substitution_line_id = vSubstitution_line_id;
1209
1210 CURSOR get_subsdtl_rec_using_hdr_id(vSubstitution_id NUMBER) IS
1211 Select *
1212 From gmd_item_substitution_dtl
1213 Where substitution_id = vSubstitution_id;
1214
1215 CURSOR get_substitution_hdr_dtl(vSubstitution_id NUMBER) IS
1216 Select original_inventory_item_id, replacement_uom_type
1217 From gmd_item_substitution_hdr_b
1218 Where substitution_id = vSubstitution_id;
1219
1220
1221 -- local variables
1222 l_substitution_id NUMBER;
1223 l_substitution_line_id NUMBER;
1224 l_api_name CONSTANT VARCHAR2(30) := 'Update_substitution_detail';
1225 l_original_prim_item_um VARCHAR2(3);
1226 l_substitute_prim_item_um VARCHAR2(3);
1227 l_original_item_id NUMBER;
1228 l_original_item_no VARCHAR2(1000);
1229 l_substitute_item_no VARCHAR2(1000);
1230 l_replacement_uom_type NUMBER;
1231 l_dummy NUMBER := 0;
1232
1233 l_substitution_dtl_rec gmd_item_substitution_dtl%ROWTYPE;
1234
1235 -- Exception declaration
1236 substitution_update_failure EXCEPTION;
1237 invalid_version EXCEPTION;
1238 setup_failure EXCEPTION;
1239 BEGIN
1240 SAVEPOINT substitution_api;
1241
1242 /* Set the return status to success initially */
1243 x_return_status := FND_API.G_RET_STS_SUCCESS;
1244
1245 /* Initialize message list and count if needed */
1246 IF (p_init_msg_list = FND_API.G_True) THEN
1247 fnd_msg_pub.initialize;
1248 END IF;
1249
1250 /* Intialize the setup fields */
1251 IF NOT gmd_api_grp.setup_done THEN
1252 gmd_api_grp.setup_done := gmd_api_grp.setup;
1253 END IF;
1254 IF NOT gmd_api_grp.setup_done THEN
1255 RAISE setup_failure;
1256 END IF;
1257
1258 /* Make sure we are call compatible */
1259 IF NOT FND_API.compatible_api_call ( 1.0
1260 ,p_api_version
1261 ,'Update_substitution_header'
1262 ,gmd_substitution_pub.m_pkg_name) THEN
1263 RAISE invalid_version;
1264 END IF;
1265
1266 /* Required fields at detail level */
1267 -- Substitution id or (substitution_name and Substitution_version)
1268 IF (p_substitution_line_id IS NULL) THEN
1269 -- get the substitution_id
1270 -- since master detail is one - to - one
1271 -- substitution id can be used to derive unique substitution line
1272 -- Substitution id or (substitution_name and Substitution_version)
1273 IF (p_substitution_id IS NULL) THEN
1274 IF (p_substitution_name IS NULL) OR (p_substitution_version IS NULL) THEN
1275 -- Raise a exception
1276 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1277 FND_MESSAGE.SET_TOKEN ('MISSING', 'SUBSTITUTION_ID');
1278 FND_MSG_PUB.ADD;
1279 RAISE substitution_update_failure;
1280 ELSE
1281 OPEN get_substitution_id(p_substitution_name, p_substitution_version);
1282 FETCH get_substitution_id INTO l_substitution_id;
1283 IF (get_substitution_id%NOTFOUND) THEN
1284 CLOSE get_substitution_id;
1285 -- raise no record found exception
1286 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_SUBSTITUTION');
1287 FND_MSG_PUB.ADD;
1288 RAISE substitution_update_failure;
1289 END IF;
1290 CLOSE get_substitution_id;
1291 END IF;
1292 ELSE
1293 l_substitution_id := p_substitution_id;
1294 END IF;
1295 ELSE
1296 l_substitution_line_id := p_substitution_line_id;
1297 END IF;
1298
1299 IF (l_substitution_line_id IS NOT NULL) THEN
1300 OPEN get_subsdtl_rec_using_line_id(l_substitution_line_id);
1301 FETCH get_subsdtl_rec_using_line_id INTO l_substitution_dtl_rec;
1302 CLOSE get_subsdtl_rec_using_line_id;
1303 ELSIF (l_substitution_id IS NOT NULL) THEN
1304 OPEN get_subsdtl_rec_using_hdr_id(l_substitution_id);
1305 FETCH get_subsdtl_rec_using_hdr_id INTO l_substitution_dtl_rec;
1306 CLOSE get_subsdtl_rec_using_hdr_id;
1307 ELSE
1308 -- raise no record found exception
1309 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_SUBSTITUTION');
1310 FND_MSG_PUB.ADD;
1311 RAISE substitution_update_failure;
1312 END IF;
1313
1314 -- prevent updates or modification of pending obsolete status
1315 IF NOT is_update_allowed(l_substitution_dtl_rec.substitution_id) THEN
1316 RAISE substitution_update_failure;
1317 END IF;
1318
1319 FOR i in 1 .. p_update_table.count LOOP
1320 -- If substitute item uom is being changed - check if it is convertible
1321 -- to the original item uom.
1322 IF UPPER(p_update_table(i).p_col_to_update) = 'DETAIL_UOM' THEN
1323 IF p_update_table(i).p_value IS NOT NULL THEN
1324 IF (NOT(gma_valid_grp.validate_um(p_update_table(i).p_value))) THEN
1325 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
1326 FND_MSG_PUB.ADD;
1327 RAISE substitution_update_failure;
1328 END IF;
1329 END IF;
1330
1331 -- Detail item uom validation
1332 --Get the original item id
1333 OPEN get_substitution_hdr_dtl(l_substitution_dtl_rec.substitution_id);
1334 FETCH get_substitution_hdr_dtl INTO l_original_item_id, l_replacement_uom_type;
1335 CLOSE get_substitution_hdr_dtl;
1336
1337 -- Get the original items primary uom
1338 OPEN get_item_info(l_original_item_id);
1339 FETCH get_item_info INTO l_original_item_no, l_original_prim_item_um;
1340 CLOSE get_item_info;
1341
1342 -- get the substitute items primary uom and item no
1343 OPEN get_item_info(l_substitution_dtl_rec.inventory_item_id);
1344 FETCH get_item_info INTO l_substitute_item_no, l_substitute_prim_item_um;
1345 CLOSE get_item_info;
1346
1347 l_dummy := INV_CONVERT.inv_um_convert (item_id => l_substitution_dtl_rec.inventory_item_id
1348 ,precision => 5
1349 ,from_quantity => 100
1350 ,from_unit => p_update_table(i).p_value
1351 ,to_unit => l_substitute_prim_item_um
1352 ,from_name => NULL
1353 ,to_name => NULL);
1354 IF l_dummy < 0 THEN
1355 FND_MESSAGE.SET_NAME('GMD','FM_SCALE_BAD_ITEM_UOM');
1356 FND_MESSAGE.SET_TOKEN('FROM_UOM', p_update_table(i).p_value);
1357 FND_MESSAGE.SET_TOKEN('TO_UOM', l_substitute_prim_item_um);
1358 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_substitute_item_no);
1359 FND_MSG_PUB.ADD;
1360 RAISE substitution_update_failure;
1361 END IF;
1362 -- reset l_dummy
1363 l_dummy := 0;
1364
1365 IF (l_replacement_uom_type = 2) THEN
1366 l_dummy :=
1367 INV_CONVERT.inv_um_convert (item_id => l_substitution_dtl_rec.inventory_item_id
1368 ,precision => 5
1369 ,from_quantity => 100
1370 ,from_unit => p_update_table(i).p_value
1371 ,to_unit => l_original_prim_item_um
1372 ,from_name => NULL
1373 ,to_name => NULL);
1374 IF l_dummy < 0 THEN
1375 FND_MESSAGE.SET_NAME('GMD','FM_SCALE_BAD_ITEM_UOM');
1376 FND_MESSAGE.SET_TOKEN('FROM_UOM', p_update_table(i).p_value);
1377 FND_MESSAGE.SET_TOKEN('TO_UOM', l_original_prim_item_um);
1378 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_substitute_item_no);
1379 FND_MSG_PUB.ADD;
1380 RAISE substitution_update_failure;
1381 END IF;
1382 END IF;
1383 -- reset l_dummy
1384 l_dummy := 0;
1385
1386 l_substitution_dtl_rec.detail_uom := p_update_table(i).p_value;
1387
1388 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'UNIT_QTY' THEN
1389 IF p_update_table(i).p_value IS NULL THEN
1390 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1391 FND_MESSAGE.SET_TOKEN ('MISSING', 'UNIT_QTY');
1392 FND_MSG_PUB.ADD;
1393 RAISE substitution_update_failure;
1394 ELSIF (p_update_table(i).p_value < 0 ) THEN
1395 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
1396 FND_MESSAGE.SET_TOKEN ('FIELD', 'UNIT_QTY');
1397 FND_MSG_PUB.ADD;
1398 RAISE substitution_update_failure;
1399 END IF;
1400 l_substitution_dtl_rec.unit_qty := p_update_table(i).p_value;
1401 -- Cannot change the Substitute item for the list
1402 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'INVENTORY_ITEM_ID' THEN
1403 -- raise exception
1404 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_COL_UPDATES');
1405 FND_MESSAGE.SET_TOKEN('NAME',p_update_table(i).p_col_to_update);
1406 FND_MSG_PUB.ADD;
1407 RAISE substitution_update_failure;
1408 ELSE
1409 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_UPDCOL_NAME');
1410 FND_MESSAGE.SET_TOKEN('NAME', p_update_table(i).p_col_to_update);
1411 FND_MSG_PUB.ADD;
1412 RAISE substitution_update_failure;
1413 END IF;
1414
1415 -- Assign values
1416 l_substitution_dtl_rec.last_update_date := SYSDATE;
1417 l_substitution_dtl_rec.last_updated_by := gmd_api_grp.user_id;
1418 l_substitution_dtl_rec.last_update_login := gmd_api_grp.login_id;
1419 END LOOP;
1420
1421 -- call the pvt API
1422 GMD_SUBSTITUTION_PVT.Update_substitution_detail
1423 ( p_substitution_dtl_rec => l_substitution_dtl_rec
1424 , x_message_count => x_message_count
1425 , x_message_list => x_message_list
1426 , x_return_status => x_return_status
1427 );
1428
1429 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1430 RAISE substitution_update_failure;
1431 END IF;
1432
1433 IF (p_commit = FND_API.g_true) THEN
1434 Commit;
1435 END IF;
1436 EXCEPTION
1437 WHEN substitution_update_failure OR invalid_version OR setup_failure THEN
1438 fnd_msg_pub.count_and_get (
1439 p_count => x_message_count
1440 ,p_encoded => FND_API.g_false
1441 ,p_data => x_message_list);
1442 x_return_status := FND_API.G_RET_STS_ERROR;
1443 ROLLBACK TO SAVEPOINT substitution_api;
1444 WHEN OTHERS THEN
1445 x_return_status := FND_API.g_ret_sts_unexp_error;
1446 fnd_msg_pub.add_exc_msg (gmd_substitution_pub.m_pkg_name, l_api_name);
1447 fnd_msg_pub.count_and_get (
1448 p_count => x_message_count
1449 ,p_encoded => FND_API.g_false
1450 ,p_data => x_message_list);
1451 ROLLBACK TO SAVEPOINT substitution_api;
1452 END Update_substitution_detail;
1453
1454 /* =============================================================== */
1455 /* Procedure: */
1456 /* Delete_formula_association */
1457 /* */
1458 /* DESCRIPTION: */
1459 /* */
1460 /* */
1461 /* History : */
1462 /* Rajender Nalla 09-OCT-06 Initial implementation. */
1463 /* =============================================================== */
1464 PROCEDURE Delete_formula_association
1465 ( p_api_version IN NUMBER
1466 , p_init_msg_list IN VARCHAR2
1467 , p_commit IN VARCHAR2
1468 , p_formula_substitution_id IN NUMBER Default NULL
1469 , p_substitution_id IN NUMBER Default NULL
1470 , p_substitution_name IN VARCHAR2 Default NULL
1471 , p_substitution_version IN NUMBER Default NULL
1472 , p_formula_id IN NUMBER Default NULL
1473 , p_formula_no IN VARCHAR2 Default NULL
1474 , p_formula_vers IN NUMBER Default NULL
1475 , x_message_count OUT NOCOPY NUMBER
1476 , x_message_list OUT NOCOPY VARCHAR2
1477 , x_return_status OUT NOCOPY VARCHAR2
1478 ) IS
1479
1480 CURSOR get_formula_substitution_id(vSubstitution_id NUMBER
1481 ,vformula_id NUMBER) IS
1482 SELECT formula_substitution_id
1483 FROM gmd_formula_substitution
1484 WHERE substitution_id = vSubstitution_id
1485 AND formula_id = vformula_id;
1486
1487 CURSOR get_formula_subs_info(vformula_Substitution_id NUMBER) IS
1488 SELECT substitution_id
1489 FROM gmd_formula_substitution
1490 WHERE formula_substitution_id = vformula_Substitution_id;
1491
1492 l_formula_substitution_id NUMBER;
1493 l_substitution_id NUMBER;
1494 l_formula_id NUMBER;
1495 l_dummy NUMBER := 0;
1496 l_api_name CONSTANT VARCHAR2(30) := 'Delete_formula_association';
1497
1498 -- Exception declaration
1499 substitution_delete_failure EXCEPTION;
1500 invalid_version EXCEPTION;
1501 setup_failure EXCEPTION;
1502 BEGIN
1503 SAVEPOINT substitution_api;
1504
1505 /* Set the return status to success initially */
1506 x_return_status := FND_API.G_RET_STS_SUCCESS;
1507
1508 /* Initialize message list and count if needed */
1509 IF (p_init_msg_list = FND_API.G_True) THEN
1510 fnd_msg_pub.initialize;
1511 END IF;
1512
1513 /* Intialize the setup fields */
1514 IF NOT gmd_api_grp.setup_done THEN
1515 gmd_api_grp.setup_done := gmd_api_grp.setup;
1516 END IF;
1517 IF NOT gmd_api_grp.setup_done THEN
1518 RAISE setup_failure;
1519 END IF;
1520
1521 /* Make sure we are call compatible */
1522 IF NOT FND_API.compatible_api_call ( 1.0
1523 ,p_api_version
1524 ,'Delete_formula_association'
1525 ,gmd_substitution_pub.m_pkg_name) THEN
1526 RAISE invalid_version;
1527 END IF;
1528
1529 /* Required fields */
1530 -- Substitution id or (substitution_name and Substitution_version)
1531 -- p_formula_substitution_id
1532 IF (p_formula_substitution_id IS NULL) THEN
1533 -- Substitution id or (substitution_name and Substitution_version)
1534 IF ((p_substitution_id IS NULL) AND
1535 (p_substitution_name IS NULL OR
1536 p_substitution_version IS NULL)) THEN
1537 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1538 FND_MESSAGE.SET_TOKEN ('MISSING', 'SUBSTITUTION_ID');
1539 FND_MSG_PUB.ADD;
1540 RAISE substitution_delete_failure;
1541 ELSIF (p_substitution_id IS NULL) THEN
1542 -- Get the subsitution id
1543 OPEN get_substitution_id(p_substitution_name
1544 ,p_substitution_version);
1545 FETCH get_substitution_id INTO l_substitution_id;
1546 IF (get_substitution_id%NOTFOUND) THEN
1547 CLOSE get_substitution_id;
1548 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INVALID_SUBSTITUTION');
1549 FND_MSG_PUB.ADD;
1550 RAISE substitution_delete_failure;
1551 END IF;
1552 CLOSE get_substitution_id;
1553 ELSE
1554 l_substitution_id := p_substitution_id;
1555 END IF;
1556
1557 -- formula_id or (formula_no and formula_version combination
1558 IF ((p_formula_id IS NULL) AND
1559 (p_formula_no IS NULL OR p_formula_vers IS NULL)) THEN
1560 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1561 FND_MESSAGE.SET_TOKEN ('MISSING', 'FORMULA_ID');
1562 FND_MSG_PUB.ADD;
1563 RAISE substitution_delete_failure;
1564 ELSIF (p_formula_id IS NULL) THEN
1565 -- Get the formula id
1566 GMDFMVAL_PUB.get_formula_id
1567 (pformula_no => p_formula_no
1568 ,pversion => p_formula_vers
1569 ,xvalue => l_formula_id
1570 ,xreturn_code => l_dummy);
1571 IF (l_dummy < 0) THEN
1572 FND_MESSAGE.SET_NAME ('GMD', 'QC_INVALID_FORMULA');
1573 FND_MSG_PUB.ADD;
1574 RAISE substitution_delete_failure;
1575 END IF;
1576 ELSE
1577 l_formula_id := p_formula_id;
1578 END IF;
1579
1580 -- Get the formula substitution id
1581 OPEN get_formula_substitution_id(l_substitution_id, l_formula_id);
1582 FETCH get_formula_substitution_id INTO l_formula_substitution_id;
1583 IF (get_formula_substitution_id%NOTFOUND) THEN
1584 CLOSE get_formula_substitution_id;
1585 FND_MESSAGE.SET_NAME ('GMD', 'GMD_FMSUB_ASSN_MISSING');
1586 FND_MESSAGE.SET_TOKEN('FORMULA_VERSION',p_formula_vers);
1587 FND_MESSAGE.SET_TOKEN('FORMULA_NO',p_formula_no);
1588 FND_MSG_PUB.ADD;
1589 RAISE substitution_delete_failure;
1590 END IF;
1591 CLOSE get_formula_substitution_id;
1592 ELSE
1593 OPEN get_formula_subs_info(p_formula_Substitution_id);
1594 FETCH get_formula_subs_info INTO l_substitution_id;
1595 CLOSE get_formula_subs_info;
1596
1597 IF (l_substitution_id IS NULL) THEN
1598 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1599 FND_MESSAGE.SET_TOKEN ('MISSING', 'FORMULA_SUBSTITUTION_ID');
1600 FND_MSG_PUB.ADD;
1601 RAISE substitution_delete_failure;
1602 ELSE
1603 l_formula_substitution_id := p_formula_Substitution_id;
1604 END IF;
1605 END IF;
1606
1607 -- prevent updates or modification of pending obsolete status
1608 IF NOT is_update_allowed(l_substitution_id) THEN
1609 RAISE substitution_delete_failure;
1610 END IF;
1611
1612 -- Call the pvt API
1613 GMD_SUBSTITUTION_PVT.Delete_formula_association
1614 ( p_formula_substitution_id => l_formula_substitution_id
1615 , x_message_count => x_message_count
1616 , x_message_list => x_message_list
1617 , x_return_status => x_return_status
1618 );
1619
1620 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1621 RAISE substitution_delete_failure;
1622 END IF;
1623
1624 IF (p_commit = FND_API.g_true) THEN
1625 Commit;
1626 END IF;
1627
1628 EXCEPTION
1629 WHEN substitution_delete_failure OR invalid_version OR setup_failure THEN
1630 fnd_msg_pub.count_and_get (
1631 p_count => x_message_count
1632 ,p_encoded => FND_API.g_false
1633 ,p_data => x_message_list);
1634 x_return_status := FND_API.G_RET_STS_ERROR;
1635 ROLLBACK TO SAVEPOINT substitution_api;
1636 WHEN OTHERS THEN
1637 x_return_status := FND_API.g_ret_sts_unexp_error;
1638 fnd_msg_pub.add_exc_msg (gmd_substitution_pub.m_pkg_name, l_api_name);
1639 fnd_msg_pub.count_and_get (
1640 p_count => x_message_count
1641 ,p_encoded => FND_API.g_false
1642 ,p_data => x_message_list);
1643 ROLLBACK TO SAVEPOINT substitution_api;
1644 END Delete_formula_association;
1645
1646 END GMD_SUBSTITUTION_PUB;