[Home] [Help]
PACKAGE BODY: APPS.GMD_SPEC_VRS_PUB
Source
1 PACKAGE BODY GMD_SPEC_VRS_PUB AS
2 /* $Header: GMDPSVRB.pls 120.0 2005/05/25 19:03:59 appldev noship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | File Name : GMDPSVRB.pls |
9 | Package Name : GMD_SPEC_VRS_PUB |
10 | Type : PUBLIC |
11 | |
12 | Contents: |
13 | |
14 | DESCRIPTION |
15 | This package contains public definitions for processing |
16 | SPEC Validity Rules |
17 | |
18 | |
19 | HISTORY |
20 | 03-AUG-2002 K.Y.Hunt |
21 | 02-May-2005 Convergence Changes |
22 +=========================================================================+
23 API Name : GMD_SPEC_VRS_PUB
24 Type : Public
25 Function : This package contains public procedures used to process
26 spec validity rules.
27 Pre-reqs : N/A
28 Parameters: Per function
29
30
31 Current Vers : 1.0
32
33 Previous Vers : 1.0
34
35 Initial Vers : 1.0
36 Notes
37 END of Notes */
38
39
40 /* Global variables */
41
42 G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_SPEC_VRS_PUB';
43
44 /*
45 +=========================================================================+
46 | Name : CREATE_INVENTORY_SPEC_VRS |
47 | Type : PUBLIC |
48 | |
49 | |
50 | DESCRIPTION |
51 | Accepts a table of inventory_spec_vrs definitions. Validates |
52 | each table entry and where valid, inserts a corresponding row |
53 | into gmd_inventory_spec_vrs |
54 | In the case of any failure a rollback is instigated.
55 | |
56 | HISTORY |
57 | 03-AUG-2002 K.Y.Hunt |
58 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
59 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
60 | GMD_SPEC_GRP.get_who() procedure |
61 | |
62 +=========================================================================+
63 */
64
65 PROCEDURE CREATE_INVENTORY_SPEC_VRS
66 ( p_api_version IN NUMBER
67 , p_init_msg_list IN VARCHAR2
68 , p_commit IN VARCHAR2
69 , p_validation_level IN VARCHAR2
70 , p_inventory_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.inventory_spec_vrs_tbl
71 , p_user_name IN VARCHAR2
72 , x_inventory_spec_vrs_tbl OUT NOCOPY GMD_SPEC_VRS_PUB.inventory_spec_vrs_tbl
73 , x_return_status OUT NOCOPY VARCHAR2
74 , x_msg_count OUT NOCOPY NUMBER
75 , x_msg_data OUT NOCOPY VARCHAR2
76 )
77 IS
78 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_INVENTORY_SPEC_VRS';
79 l_api_version CONSTANT NUMBER := 1.0;
80 l_msg_count NUMBER :=0;
81 l_msg_data VARCHAR2(2000);
82 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
83 l_spec GMD_SPECIFICATIONS%ROWTYPE;
84 l_inventory_spec_vrs GMD_INVENTORY_SPEC_VRS%ROWTYPE;
85 l_inventory_spec_vrs_out GMD_INVENTORY_SPEC_VRS%ROWTYPE;
86 l_inventory_spec_vrs_tbl GMD_SPEC_VRS_PUB.inventory_spec_vrs_tbl;
87 l_rowid ROWID;
88 l_user_id NUMBER(15);
89
90 BEGIN
91
92 -- Standard Start OF API savepoint
93 -- ===============================
94 SAVEPOINT Create_Inventory_Spec_VRS;
95
96 -- Standard call to check for call compatibility
97 -- =============================================
98 IF NOT FND_API.Compatible_API_CALL
99 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
100 THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END IF;
103
104 -- Initialize message list if p_int_msg_list is set TRUE.
105 -- ======================================================
106 IF FND_API.to_boolean(p_init_msg_list)
107 THEN
108 FND_MSG_PUB.Initialize;
109 END IF;
110
111 -- Initialize API return Parameters
112 -- ================================
113 l_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 -- Validate User Name Parameter
116 -- ============================
117 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
118 ,x_user_id => l_user_id);
119
120 IF NVL(l_user_id, -1) < 0
121 THEN
122 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
123 'l_user_name', p_user_name);
124 RAISE FND_API.G_EXC_ERROR;
125 END IF;
126
127 -- Loop through the inventory spec validity rules validating and creating
128 -- ======================================================================
129 FOR i in 1..p_inventory_spec_vrs_tbl.COUNT LOOP
130
131 l_inventory_spec_vrs := p_inventory_spec_vrs_tbl(i);
132
133 -- Set Who columns ahead of Validation
134 -- ===================================
135 l_inventory_spec_vrs.created_by := l_user_id;
136 l_inventory_spec_vrs.last_updated_by := l_user_id;
137 l_inventory_spec_vrs.creation_date := sysdate;
138 l_inventory_spec_vrs.last_update_date:= sysdate;
139
140 -- Set spec_vr_id to NULL and delete_mark to zero
141 -- ==============================================
142 l_inventory_spec_vrs.spec_vr_id := NULL;
143 l_inventory_spec_vrs.delete_mark := 0;
144
145 -- Set spec_vr_status to NEW
146 -- =========================
147 l_inventory_spec_vrs.spec_vr_status := 100;
148
149 -- Validate Inventory Spec Validity Rule
150 -- =====================================
151 -- BUG 2691994 - signature change for validation routine
152 GMD_SPEC_VRS_GRP.Validate_INV_VR(
153 p_inv_vr => l_inventory_spec_vrs,
154 p_called_from => 'API',
155 p_operation => 'INSERT',
156 x_inv_vr => l_inventory_spec_vrs_out,
157 x_return_status => l_return_status
158 );
159
160 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
161 -- Diagnostic message is already on the stack
162 RAISE FND_API.G_EXC_ERROR;
163 END IF;
164
165 l_inventory_spec_vrs := l_inventory_spec_vrs_out;
166
167 IF NOT GMD_INVENTORY_SPEC_VRS_PVT.Insert_Row(l_inventory_spec_vrs, l_inventory_spec_vrs_out)
168 THEN
169 -- Diagnostic message is already on the stack
170 RAISE FND_API.G_EXC_ERROR;
171 END IF;
172
173 -- Update Return Parameter Tbl
174 -- ===========================
175 l_inventory_spec_vrs_tbl(i) := l_inventory_spec_vrs_out;
176
177 END LOOP;
178
179 -- Standard Check of p_commit.
180 -- ==========================
181 IF FND_API.to_boolean(p_commit)
182 THEN
183 COMMIT WORK;
184 END IF;
185
186 x_return_status := l_return_status;
187 x_inventory_spec_vrs_tbl := l_inventory_spec_vrs_tbl;
188
189 EXCEPTION
190 WHEN FND_API.G_EXC_ERROR THEN
191 ROLLBACK TO Create_Inventory_Spec_VRS;
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
194 , p_count => x_msg_count
195 , p_data => x_msg_data
196 );
197
198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
199 ROLLBACK TO Create_Inventory_Spec_VRS;
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
202 , p_count => x_msg_count
203 , p_data => x_msg_data
204 );
205
206
207
208 WHEN OTHERS THEN
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 ROLLBACK TO Create_Inventory_Spec_VRS;
211 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
212 , l_api_name
213 );
214
215 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
216 , p_count => x_msg_count
217 , p_data => x_msg_data
218 );
219
220 END CREATE_INVENTORY_SPEC_VRS;
221
222 /*
223 +=========================================================================+
224 | Name : CREATE_WIP_SPEC_VRS |
225 | Type : PUBLIC |
226 | |
227 | |
228 | DESCRIPTION |
229 | Accepts a table of wip_spec_vrs definitions. Validates |
230 | each table entry and where valid, inserts a corresponding row |
231 | into gmd_wip_spec_vrs |
232 | In the case of any failure a rollback is instigated. |
233 | |
234 | HISTORY |
235 | 03-AUG-2002 K.Y.Hunt |
236 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
237 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
238 | GMD_SPEC_GRP.get_who() procedure |
239 | |
240 +=========================================================================+
241 */
242
243 PROCEDURE CREATE_WIP_SPEC_VRS
244 ( p_api_version IN NUMBER
245 , p_init_msg_list IN VARCHAR2
246 , p_commit IN VARCHAR2
247 , p_validation_level IN VARCHAR2
248 , p_wip_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.wip_spec_vrs_tbl
249 , p_user_name IN VARCHAR2
250 , x_wip_spec_vrs_tbl OUT NOCOPY GMD_SPEC_VRS_PUB.wip_spec_vrs_tbl
251 , x_return_status OUT NOCOPY VARCHAR2
252 , x_msg_count OUT NOCOPY NUMBER
253 , x_msg_data OUT NOCOPY VARCHAR2
254 )
255 IS
256 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_WIP_SPEC_VRS';
257 l_api_version CONSTANT NUMBER := 1.0;
258 l_msg_count NUMBER :=0;
259 l_msg_data VARCHAR2(2000);
260 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
261 l_spec GMD_SPECIFICATIONS%ROWTYPE;
262 l_wip_spec_vrs GMD_WIP_SPEC_VRS%ROWTYPE;
263 l_wip_spec_vrs_out GMD_WIP_SPEC_VRS%ROWTYPE;
264 l_wip_spec_vrs_tbl GMD_SPEC_VRS_PUB.wip_spec_vrs_tbl;
265 l_rowid ROWID;
266 l_user_id NUMBER(15);
267
268 BEGIN
269
270
271 -- Standard Start OF API savepoint
272 -- ===============================
273 SAVEPOINT Create_WIP_Spec_VRS;
274
275 -- Standard call to check for call compatibility
276 -- =============================================
277 IF NOT FND_API.Compatible_API_CALL
278 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
279 THEN
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282
283 -- Initialize message list if p_int_msg_list is set TRUE.
284 -- ======================================================
285 IF FND_API.to_boolean(p_init_msg_list)
286 THEN
287 FND_MSG_PUB.Initialize;
288 END IF;
289
290 -- Initialize API return Parameters
291 -- ================================
292 l_return_status := FND_API.G_RET_STS_SUCCESS;
293
294 -- Validate User Name Parameter
295 -- ============================
296 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
297 ,x_user_id => l_user_id);
298
299 IF NVL(l_user_id, -1) < 0
300 THEN
301 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
302 'l_user_name', p_user_name);
303 RAISE FND_API.G_EXC_ERROR;
304 END IF;
305
306 -- Loop through the WIP spec validity rules validating and creating
307 -- ================================================================
308 FOR i in 1..p_wip_spec_vrs_tbl.COUNT LOOP
309
310 l_wip_spec_vrs := p_wip_spec_vrs_tbl(i);
311
312 -- Set Who columns ahead of Validation
313 -- ===================================
314 l_wip_spec_vrs.created_by := l_user_id;
315 l_wip_spec_vrs.last_updated_by := l_user_id;
316 l_wip_spec_vrs.creation_date := sysdate;
317 l_wip_spec_vrs.last_update_date:= sysdate;
318
319 -- Set spec_vr_id to NULL and delete_mark to zero
320 -- ==============================================
321 l_wip_spec_vrs.spec_vr_id := NULL;
322 l_wip_spec_vrs.delete_mark := 0;
323
324 -- Set spec_vr_status to NEW
325 -- =========================
326 l_wip_spec_vrs.spec_vr_status := 100;
327
328 -- Validate WIP Spec Validity Rule
329 -- ===============================
330 -- BUG 2691994 - signature change for validation routine
331 GMD_SPEC_VRS_GRP.Validate_WIP_VR(
332 p_wip_vr => l_wip_spec_vrs,
333 p_called_from => 'API',
334 p_operation => 'INSERT',
335 x_wip_vr => l_wip_spec_vrs_out,
336 x_return_status => l_return_status
337 );
338
339 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
340 -- Diagnostic message is already on the stack
341 RAISE FND_API.G_EXC_ERROR;
342 END IF;
343
344 l_wip_spec_vrs := l_wip_spec_vrs_out;
345
346 IF NOT GMD_WIP_SPEC_VRS_PVT.Insert_Row(l_wip_spec_vrs, l_wip_spec_vrs_out)
347 THEN
348 -- Diagnostic message is already on the stack
349 RAISE FND_API.G_EXC_ERROR;
350 END IF;
351
352 -- Update Return Parameter Tbl
353 -- ===========================
354 l_wip_spec_vrs_tbl(i) := l_wip_spec_vrs_out;
355
356 END LOOP;
357
358 -- Standard Check of p_commit.
359 -- ==========================
360 IF FND_API.to_boolean(p_commit)
361 THEN
362 COMMIT WORK;
363 END IF;
364
365 x_return_status := l_return_status;
366 x_wip_spec_vrs_tbl := l_wip_spec_vrs_tbl;
367
368 EXCEPTION
369 WHEN FND_API.G_EXC_ERROR THEN
370 ROLLBACK TO Create_WIP_Spec_VRS;
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
373 , p_count => x_msg_count
374 , p_data => x_msg_data
375 );
376
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378 ROLLBACK TO Create_WIP_Spec_VRS;
379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
380 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
381 , p_count => x_msg_count
382 , p_data => x_msg_data
383 );
384
385 WHEN OTHERS THEN
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 ROLLBACK TO Create_WIP_Spec_VRS;
388 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
389 , l_api_name
390 );
391
392 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
393 , p_count => x_msg_count
394 , p_data => x_msg_data
395 );
396
397 END CREATE_WIP_SPEC_VRS;
398
399 /*
400 +=========================================================================+
401 | Name : CREATE_CUSTOMER_SPEC_VRS |
402 | Type : PUBLIC |
403 | |
404 | |
405 | DESCRIPTION |
406 | Accepts a table of customer_spec_vrs definitions. Validates |
407 | each table entry and where valid, inserts a corresponding row |
408 | into gmd_customer_spec_vrs |
409 | In the case of any failure a rollback is instigated. |
410 | |
411 | HISTORY |
412 | 03-AUG-2002 K.Y.Hunt |
413 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
414 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
415 | GMD_SPEC_GRP.get_who() procedure |
416 | |
417 +=========================================================================+
418 */
419
420 PROCEDURE CREATE_CUSTOMER_SPEC_VRS
421 ( p_api_version IN NUMBER
422 , p_init_msg_list IN VARCHAR2
423 , p_commit IN VARCHAR2
424 , p_validation_level IN VARCHAR2
425 , p_customer_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl
426 , p_user_name IN VARCHAR2
427 , x_customer_spec_vrs_tbl OUT NOCOPY GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl
428 , x_return_status OUT NOCOPY VARCHAR2
429 , x_msg_count OUT NOCOPY NUMBER
430 , x_msg_data OUT NOCOPY VARCHAR2
431 )
432 IS
433 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_CUSTOMER_SPEC_VRS';
434 l_api_version CONSTANT NUMBER := 1.0;
435 l_msg_count NUMBER :=0;
436 l_msg_data VARCHAR2(2000);
437 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
438 l_customer_spec_vrs GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
439 l_customer_spec_vrs_out GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
440 l_customer_spec_vrs_tbl GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl;
441 l_user_id NUMBER(15);
442
443 BEGIN
444
445
446 -- Standard Start OF API savepoint
447 -- ===============================
448 SAVEPOINT Create_Customer_Spec_VRS;
449
450 -- Standard call to check for call compatibility
451 -- =============================================
452 IF NOT FND_API.Compatible_API_CALL
453 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
454 THEN
455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
456 END IF;
457
458 -- Initialize message list if p_int_msg_list is set TRUE.
459 -- ======================================================
460 IF FND_API.to_boolean(p_init_msg_list)
461 THEN
462 FND_MSG_PUB.Initialize;
463 END IF;
464
465 -- Initialize API return Parameters
466 -- ================================
467 l_return_status := FND_API.G_RET_STS_SUCCESS;
468
469 -- Validate User Name Parameter
470 -- ============================
471 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
472 ,x_user_id => l_user_id);
473
474 IF NVL(l_user_id, -1) < 0
475 THEN
476 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
477 'l_user_name', p_user_name);
478 RAISE FND_API.G_EXC_ERROR;
479 END IF;
480
481 -- Loop through the Customer spec validity rules validating and creating
482 -- =====================================================================
483 FOR i in 1..p_customer_spec_vrs_tbl.COUNT LOOP
484
485 l_customer_spec_vrs := p_customer_spec_vrs_tbl(i);
486
487 -- Set Who columns ahead of Validation
488 -- ===================================
489 l_customer_spec_vrs.created_by := l_user_id;
490 l_customer_spec_vrs.last_updated_by := l_user_id;
491 l_customer_spec_vrs.creation_date := sysdate;
492 l_customer_spec_vrs.last_update_date:= sysdate;
493
494 -- Set spec_vr_id to NULL and delete_mark to zero
495 -- ==============================================
496 l_customer_spec_vrs.spec_vr_id := NULL;
497 l_customer_spec_vrs.delete_mark := 0;
498
499 -- Set spec_vr_status to NEW
500 -- =========================
501 l_customer_spec_vrs.spec_vr_status := 100;
502
503 -- Validate Customer Spec Validity Rule
504 -- ====================================
505 GMD_SPEC_VRS_GRP.Validate_Cust_VR(
506 p_cust_vr => l_customer_spec_vrs,
507 p_called_from => 'API',
508 p_operation => 'INSERT',
509 x_return_status => l_return_status
510 );
511
512 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
513 -- Diagnostic message is already on the stack
514 RAISE FND_API.G_EXC_ERROR;
515 END IF;
516
517 IF NOT GMD_CUSTOMER_SPEC_VRS_PVT.Insert_Row(l_customer_spec_vrs, l_customer_spec_vrs_out)
518 THEN
519 -- Diagnostic message is already on the stack
520 RAISE FND_API.G_EXC_ERROR;
521 END IF;
522
523 -- Update Return Parameter Tbl
524 -- ===========================
525 l_customer_spec_vrs_tbl(i) := l_customer_spec_vrs_out;
526
527 END LOOP;
528
529 -- Standard Check of p_commit.
530 -- ==========================
531 IF FND_API.to_boolean(p_commit)
532 THEN
533 COMMIT WORK;
534 END IF;
535
536 x_return_status := l_return_status;
537 x_customer_spec_vrs_tbl := l_customer_spec_vrs_tbl;
538
539 EXCEPTION
540 WHEN FND_API.G_EXC_ERROR THEN
541 ROLLBACK TO Create_Customer_Spec_VRS;
542 x_return_status := FND_API.G_RET_STS_ERROR;
543 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
544 , p_count => x_msg_count
545 , p_data => x_msg_data
546 );
547
548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
549 ROLLBACK TO Create_Customer_Spec_VRS;
550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
551 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
552 , p_count => x_msg_count
553 , p_data => x_msg_data
554 );
555
556 WHEN OTHERS THEN
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558 ROLLBACK TO Create_Customer_Spec_VRS;
559 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
560 , l_api_name
561 );
562
563 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
564 , p_count => x_msg_count
565 , p_data => x_msg_data
566 );
567
568 END CREATE_CUSTOMER_SPEC_VRS;
569
570 /*
571 +=========================================================================+
572 | Name : CREATE_SUPPLIER_SPEC_VRS |
573 | Type : PUBLIC |
574 | |
575 | |
576 | DESCRIPTION |
577 | Accepts a table of supplier_spec_vrs definitions. Validates |
578 | each table entry and where valid, inserts a corresponding row |
579 | into gmd_supplier_spec_vrs |
580 | In the case of any failure a rollback is instigated. |
581 | |
582 | HISTORY |
583 | 03-AUG-2002 K.Y.Hunt |
584 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
585 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
586 | GMD_SPEC_GRP.get_who() procedure |
587 | |
588 +=========================================================================+
589 */
590
591 PROCEDURE CREATE_SUPPLIER_SPEC_VRS
592 ( p_api_version IN NUMBER
593 , p_init_msg_list IN VARCHAR2
594 , p_commit IN VARCHAR2
595 , p_validation_level IN VARCHAR2
596 , p_supplier_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.supplier_spec_vrs_tbl
597 , p_user_name IN VARCHAR2
598 , x_supplier_spec_vrs_tbl OUT NOCOPY GMD_SPEC_VRS_PUB.supplier_spec_vrs_tbl
599 , x_return_status OUT NOCOPY VARCHAR2
600 , x_msg_count OUT NOCOPY NUMBER
601 , x_msg_data OUT NOCOPY VARCHAR2
602 )
603 IS
604 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_SUPPLIER_SPEC_VRS';
605 l_api_version CONSTANT NUMBER := 1.0;
606 l_msg_count NUMBER :=0;
607 l_msg_data VARCHAR2(2000);
608 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
609 l_supplier_spec_vrs GMD_SUPPLIER_SPEC_VRS%ROWTYPE;
610 l_supplier_spec_vrs_out GMD_SUPPLIER_SPEC_VRS%ROWTYPE;
611 l_supplier_spec_vrs_tbl GMD_SPEC_VRS_PUB.supplier_spec_vrs_tbl;
612 l_user_id NUMBER(15);
613
614 BEGIN
615
616 -- Standard Start OF API savepoint
617 -- ===============================
618 SAVEPOINT Create_Supplier_Spec_VRS;
619
620 -- Standard call to check for call compatibility
621 -- =============================================
622 IF NOT FND_API.Compatible_API_CALL
623 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
624 THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END IF;
627
628 -- Initialize message list if p_int_msg_list is set TRUE.
629 -- ======================================================
630 IF FND_API.to_boolean(p_init_msg_list)
631 THEN
632 FND_MSG_PUB.Initialize;
633 END IF;
634
635 -- Initialize API return Parameters
636 -- ================================
637 l_return_status := FND_API.G_RET_STS_SUCCESS;
638
639 -- Validate User Name Parameter
640 -- ============================
641 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
642 ,x_user_id => l_user_id);
643
644 IF NVL(l_user_id, -1) < 0
645 THEN
646 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
647 'l_user_name', p_user_name);
648 RAISE FND_API.G_EXC_ERROR;
649 END IF;
650
651 -- Loop through the Supplier spec validity rules validating and creating
652 -- =====================================================================
653 FOR i in 1..p_supplier_spec_vrs_tbl.COUNT LOOP
654
655 l_supplier_spec_vrs := p_supplier_spec_vrs_tbl(i);
656
657 -- Set Who columns ahead of Validation
658 -- ===================================
659 l_supplier_spec_vrs.created_by := l_user_id;
660 l_supplier_spec_vrs.last_updated_by := l_user_id;
661 l_supplier_spec_vrs.creation_date := sysdate;
662 l_supplier_spec_vrs.last_update_date:= sysdate;
663
664 -- Set spec_vr_id to NULL and delete_mark to zero
665 -- ==============================================
666 l_supplier_spec_vrs.spec_vr_id := NULL;
667 l_supplier_spec_vrs.delete_mark := 0;
668
669 -- Set spec_vr_status to NEW
670 -- =========================
671 l_supplier_spec_vrs.spec_vr_status := 100;
672
673 -- Validate Supplier Spec Validity Rule
674 -- ====================================
675 GMD_SPEC_VRS_GRP.Validate_Supp_VR(
676 p_supp_vr => l_supplier_spec_vrs,
677 p_called_from => 'API',
678 p_operation => 'INSERT',
679 x_return_status => l_return_status
680 );
681
682 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
683 -- Diagnostic message is already on the stack
684 RAISE FND_API.G_EXC_ERROR;
685 END IF;
686
687 IF NOT GMD_SUPPLIER_SPEC_VRS_PVT.Insert_Row(l_supplier_spec_vrs, l_supplier_spec_vrs_out)
688 THEN
689 -- Diagnostic message is already on the stack
690 RAISE FND_API.G_EXC_ERROR;
691 END IF;
692
693 -- Update Return Parameter Tbl
694 -- ===========================
695 l_supplier_spec_vrs_tbl(i) := l_supplier_spec_vrs_out;
696
697 END LOOP;
698
699 -- Standard Check of p_commit.
700 -- ==========================
701 IF FND_API.to_boolean(p_commit)
702 THEN
703 COMMIT WORK;
704 END IF;
705
706 x_return_status := l_return_status;
707 x_supplier_spec_vrs_tbl := l_supplier_spec_vrs_tbl;
708
709 EXCEPTION
710 WHEN FND_API.G_EXC_ERROR THEN
711 ROLLBACK TO Create_Supplier_Spec_VRS;
712 x_return_status := FND_API.G_RET_STS_ERROR;
713 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
714 , p_count => x_msg_count
715 , p_data => x_msg_data
716 );
717
718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
719 ROLLBACK TO Create_Supplier_Spec_VRS;
720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
721 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
722 , p_count => x_msg_count
723 , p_data => x_msg_data
724 );
725
726 WHEN OTHERS THEN
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
728 ROLLBACK TO Create_Supplier_Spec_VRS;
729 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
730 , l_api_name
731 );
732
733 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
734 , p_count => x_msg_count
735 , p_data => x_msg_data
736 );
737
738 END CREATE_SUPPLIER_SPEC_VRS;
739
740
741
742
743
744 /*
745 +=========================================================================+
746 | Name : CREATE_MONITORING_SPEC_VRS |
747 | Type : PUBLIC |
748 | |
749 | |
750 | DESCRIPTION |
751 | Accepts a table of monitoring_spec_vrs definitions. Validates |
752 | each table entry and where valid, inserts a corresponding row |
753 | into gmd_supplier_spec_vrs |
754 | In the case of any failure a rollback is instigated. |
755 | |
756 | HISTORY |
757 | 26-Jan-2004 Manish Gupta |
758 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
759 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
760 | GMD_SPEC_GRP.get_who() procedure |
761 | |
762 +=========================================================================+
763 */
764
765 PROCEDURE CREATE_MONITORING_SPEC_VRS
766 ( p_api_version IN NUMBER
767 , p_init_msg_list IN VARCHAR2
768 , p_commit IN VARCHAR2
769 , p_validation_level IN VARCHAR2
770 , p_monitoring_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.monitoring_spec_vrs_tbl
771 , p_user_name IN VARCHAR2
772 , x_monitoring_spec_vrs_tbl OUT NOCOPY GMD_SPEC_VRS_PUB.monitoring_spec_vrs_tbl
773 , x_return_status OUT NOCOPY VARCHAR2
774 , x_msg_count OUT NOCOPY NUMBER
775 , x_msg_data OUT NOCOPY VARCHAR2
776 )
777 IS
778 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_MONITORING_SPEC_VRS';
779 l_api_version CONSTANT NUMBER := 1.0;
780 l_msg_count NUMBER :=0;
781 l_msg_data VARCHAR2(2000);
782 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
783 l_monitoring_spec_vrs GMD_MONITORING_SPEC_VRS%ROWTYPE;
784 l_monitoring_spec_vrs_out GMD_MONITORING_SPEC_VRS%ROWTYPE;
785 l_monitoring_spec_vrs_tbl GMD_SPEC_VRS_PUB.monitoring_spec_vrs_tbl;
786 l_user_id NUMBER(15);
787
788 BEGIN
789
790
791 -- Standard Start OF API savepoint
792 -- ===============================
793 SAVEPOINT Create_monitoring_Spec_VRS;
794
795 -- Standard call to check for call compatibility
796 -- =============================================
797 IF NOT FND_API.Compatible_API_CALL
798 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
799 THEN
800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
801 END IF;
802
803 -- Initialize message list if p_int_msg_list is set TRUE.
804 -- ======================================================
805 IF FND_API.to_boolean(p_init_msg_list)
806 THEN
807 FND_MSG_PUB.Initialize;
808 END IF;
809
810 -- Initialize API return Parameters
811 -- ================================
812 l_return_status := FND_API.G_RET_STS_SUCCESS;
813
814 -- Validate User Name Parameter
815 -- ============================
816 GMD_SPEC_GRP.Get_Who ( p_user_name => p_user_name
817 ,x_user_id => l_user_id);
818
819 IF NVL(l_user_id, -1) < 0
820 THEN
821 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
822 'l_user_name', p_user_name);
823 RAISE FND_API.G_EXC_ERROR;
824 END IF;
825
826 -- Loop through the Monitoring spec validity rules validating and creating
827 -- =====================================================================
828 FOR i in 1..p_monitoring_spec_vrs_tbl.COUNT LOOP
829
830 l_monitoring_spec_vrs := p_monitoring_spec_vrs_tbl(i);
831
832 -- Set Who columns ahead of Validation
833 -- ===================================
834 l_monitoring_spec_vrs.created_by := l_user_id;
835 l_monitoring_spec_vrs.last_updated_by := l_user_id;
836 l_monitoring_spec_vrs.creation_date := sysdate;
837 l_monitoring_spec_vrs.last_update_date:= sysdate;
838
839 -- Set spec_vr_id to NULL and delete_mark to zero
840 -- ==============================================
841 l_monitoring_spec_vrs.spec_vr_id := NULL;
842 l_monitoring_spec_vrs.delete_mark := 0;
843
844 -- Set spec_vr_status to NEW
845 -- =========================
846 l_monitoring_spec_vrs.spec_vr_status := 100;
847
848
849 -- Bug 3451798
850 -- In case rule type is location, all resource-related info should be nulled
851 -- In case rule type is resource, all location-related info should be nulled
852 if (l_monitoring_spec_vrs.rule_type = 'R') then
853 l_monitoring_spec_vrs.locator_id := NULL;
854 l_monitoring_spec_vrs.locator_organization_id := NULL;
855 l_monitoring_spec_vrs.subinventory := NULL;
856 elsif (l_monitoring_spec_vrs.rule_type = 'L') then
857 l_monitoring_spec_vrs.resources := NULL;
858 l_monitoring_spec_vrs.resource_organization_id := NULL;
859 l_monitoring_spec_vrs.resource_instance_id := NULL;
860 end if;
861
862 -- Validate Supplier Spec Validity Rule
863 -- ====================================
864 GMD_SPEC_VRS_GRP.Validate_Mon_VR(
865 p_mon_vr => l_monitoring_spec_vrs,
866 p_called_from => 'API',
867 p_operation => 'INSERT',
868 x_mon_vr => l_monitoring_spec_vrs_out,
869 x_return_status => l_return_status
870 );
871
872
873 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
874 -- Diagnostic message is already on the stack
875 RAISE FND_API.G_EXC_ERROR;
876 END IF;
877
878 IF NOT GMD_MONITORING_SPEC_VRS_PVT.Insert_Row(l_monitoring_spec_vrs, l_monitoring_spec_vrs_out)
879 THEN
880 -- Diagnostic message is already on the stack
881 RAISE FND_API.G_EXC_ERROR;
882 END IF;
883
884 -- Update Return Parameter Tbl
885 -- ===========================
886 l_monitoring_spec_vrs_tbl(i) := l_monitoring_spec_vrs_out;
887
888 END LOOP;
889
890 -- Standard Check of p_commit.
891 -- ==========================
892 IF FND_API.to_boolean(p_commit)
893 THEN
894 COMMIT WORK;
895 END IF;
896
897 x_return_status := l_return_status;
898 x_monitoring_spec_vrs_tbl := l_monitoring_spec_vrs_tbl;
899
900 EXCEPTION
901 WHEN FND_API.G_EXC_ERROR THEN
902 ROLLBACK TO Create_monitoring_Spec_VRS;
903 x_return_status := FND_API.G_RET_STS_ERROR;
904 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
905 , p_count => x_msg_count
906 , p_data => x_msg_data
907 );
908
909 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
910 ROLLBACK TO Create_monitoring_Spec_VRS;
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
913 , p_count => x_msg_count
914 , p_data => x_msg_data
915 );
916
917 WHEN OTHERS THEN
918 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
919 ROLLBACK TO Create_monitoring_Spec_VRS;
920 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
921 , l_api_name
922 );
923
924 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
925 , p_count => x_msg_count
926 , p_data => x_msg_data
927 );
928
929 END CREATE_MONITORING_SPEC_VRS;
930 /*
931 +=========================================================================+
932 | Name : DELETE_INVENTORY_SPEC_VRS |
933 | Type : PUBLIC |
934 | |
935 | |
936 | DESCRIPTION |
937 | Accepts a table of inventory_spec_vrs definitions. Validates |
938 | each table entry to ensure the corresponding row is not already |
939 | delete marked. Where validation is successful, a logical delete |
940 | is performed setting delete_mark=1 |
941 | In the case of any failure a rollback is instigated. |
942 | |
943 | HISTORY |
944 | 03-AUG-2002 K.Y.Hunt |
945 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
946 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
947 | GMD_SPEC_GRP.get_who() procedure |
948 | |
949 +=========================================================================+
950 */
951
952 PROCEDURE DELETE_INVENTORY_SPEC_VRS
953 ( p_api_version IN NUMBER
954 , p_init_msg_list IN VARCHAR2
955 , p_commit IN VARCHAR2
956 , p_validation_level IN VARCHAR2
957 , p_inventory_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.inventory_spec_vrs_tbl
958 , p_user_name IN VARCHAR2
959 , x_deleted_rows OUT NOCOPY NUMBER
960 , x_return_status OUT NOCOPY VARCHAR2
961 , x_msg_count OUT NOCOPY NUMBER
962 , x_msg_data OUT NOCOPY VARCHAR2
963 )
964 IS
965 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_INVENTORY_SPEC_VRS';
966 l_api_version CONSTANT NUMBER := 1.0;
967 l_msg_count NUMBER :=0;
968 l_msg_data VARCHAR2(2000);
969 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
970 l_spec GMD_SPECIFICATIONS%ROWTYPE;
971 l_inventory_spec_vrs GMD_INVENTORY_SPEC_VRS%ROWTYPE;
972 l_deleted_rows NUMBER :=0;
973
974 BEGIN
975
976
977 -- Standard Start OF API savepoint
978 -- ===============================
979 SAVEPOINT Delete_Inventory_Spec_VRS;
980
981 -- Standard call to check for call compatibility.
982 -- ==============================================
983
984 IF NOT FND_API.Compatible_API_CALL
985 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
986 THEN
987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
988 END IF;
989
990 -- Initialize message list if p_int_msg_list is set TRUE.
991 -- ======================================================
992 IF FND_API.to_boolean(p_init_msg_list)
993 THEN
994 FND_MSG_PUB.Initialize;
995 END IF;
996
997 -- Initialize API return Parameters
998 -- ================================
999 x_return_status := FND_API.G_RET_STS_SUCCESS;
1000
1001 -- Initialize Local Variables
1002 -- ==========================
1003 l_spec.spec_id := 0;
1004
1005 -- Validate user_name
1006 -- ==================
1007 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
1008 ,x_user_id => l_spec.last_updated_by);
1009
1010 IF NVL(l_spec.last_updated_by, -1) < 0
1011 THEN
1012 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
1013 'l_user_name', p_user_name);
1014 RAISE FND_API.G_EXC_ERROR;
1015 END IF;
1016
1017 -- Process each of the inventory spec validity rules
1018 -- =================================================
1019 FOR i in 1..p_inventory_spec_vrs_tbl.COUNT LOOP
1020 l_inventory_spec_vrs := p_inventory_spec_vrs_tbl(i);
1021 -- Ensure the owning spec_id is supplied
1022 -- =====================================
1023 IF ( l_inventory_spec_vrs.spec_id IS NULL )
1024 THEN
1025 -- raise validation error
1026 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
1027 RAISE FND_API.G_EXC_ERROR;
1028 END IF;
1029
1030 -- Retrieve and validate the owning SPEC if it is not already retrieved/validated
1031 -- ==============================================================================
1032 IF l_spec.spec_id <> l_inventory_spec_vrs.spec_id
1033 THEN
1034 -- Validate to ensure spec is in a suitable state to delete mark
1035 -- ==============================================================
1036 GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => l_inventory_spec_vrs.spec_id
1037 , x_return_status => l_return_status
1038 , x_message_data => l_msg_data
1039 );
1040 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1041 RAISE FND_API.G_EXC_ERROR;
1042 END IF;
1043
1044 -- Lock the SPEC ahead of manipulating INVENTORY_SPEC_VRS
1045 -- ======================================================
1046 IF NOT GMD_Specifications_PVT.Lock_Row(l_inventory_spec_vrs.spec_id)
1047 THEN
1048 -- Report Failure to obtain locks
1049 -- ==============================
1050 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1051 'l_table_name', 'GMD_SPECIFICATIONS',
1052 'l_column_name', 'SPEC_ID',
1053 'l_key_value', l_inventory_spec_vrs.spec_id);
1054 RAISE FND_API.G_EXC_ERROR;
1055 END IF;
1056 END IF; -- end of spec validation
1057
1058 -- Validate to ensure validity_rule exists and is not already delete marked
1059 -- ========================================================================
1060 GMD_SPEC_VRS_GRP.VALIDATE_BEFORE_DELETE_INV_VRS
1061 ( p_spec_id => l_inventory_spec_vrs.spec_id
1062 , p_spec_vr_id => l_inventory_spec_vrs.spec_vr_id
1063 , x_return_status => l_return_status
1064 , x_message_data => l_msg_data
1065 );
1066 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1067 -- Diagnostic message already on the stack
1068 RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070
1071
1072 -- Lock the validity rule ahead of deleting
1073 -- ========================================
1074 IF NOT GMD_INVENTORY_SPEC_VRS_PVT.Lock_Row( l_inventory_spec_vrs.spec_vr_id)
1075 THEN
1076 -- Report Failure to obtain locks
1077 -- ==============================
1078 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1079 'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
1080 'l_column_name', 'SPEC_VR_ID',
1081 'l_key_value', l_inventory_spec_vrs.spec_vr_id);
1082 RAISE FND_API.G_EXC_ERROR;
1083 END IF;
1084
1085 IF NOT GMD_INVENTORY_SPEC_VRS_PVT.Delete_Row
1086 ( p_spec_vr_id => l_inventory_spec_vrs.spec_vr_id
1087 , p_last_update_date => sysdate
1088 , p_last_updated_by => l_spec.last_updated_by
1089 )
1090 THEN
1091 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
1092 'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
1093 'l_column_name', 'SPEC_VR_ID',
1094 'l_key_value', l_inventory_spec_vrs.spec_vr_id);
1095 fnd_msg_pub.ADD;
1096 RAISE FND_API.G_EXC_ERROR;
1097 ELSE
1098 x_deleted_rows := i;
1099 END IF;
1100
1101 END LOOP;
1102
1103 -- Standard Check of p_commit.
1104 IF FND_API.to_boolean(p_commit)
1105 THEN
1106 COMMIT WORK;
1107 END IF;
1108
1109 x_return_status := l_return_status;
1110
1111 EXCEPTION
1112 WHEN FND_API.G_EXC_ERROR THEN
1113 ROLLBACK TO Delete_Inventory_Spec_VRS;
1114 x_deleted_rows := 0;
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1117 , p_count => x_msg_count
1118 , p_data => x_msg_data
1119 );
1120
1121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1122 ROLLBACK TO Delete_Inventory_Spec_VRS;
1123 x_deleted_rows := 0;
1124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1125 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1126 , p_count => x_msg_count
1127 , p_data => x_msg_data
1128 );
1129
1130
1131
1132 WHEN OTHERS THEN
1133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1134 ROLLBACK TO Delete_Inventory_Spec_VRS;
1135 x_deleted_rows := 0;
1136 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1137 , l_api_name
1138 );
1139
1140 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1141 , p_count => x_msg_count
1142 , p_data => x_msg_data
1143 );
1144
1145 END DELETE_INVENTORY_SPEC_VRS;
1146
1147 /*
1148 +=========================================================================+
1149 | Name : DELETE_WIP_SPEC_VRS |
1150 | Type : PUBLIC |
1151 | |
1152 | |
1153 | DESCRIPTION |
1154 | Accepts a table of wip_spec_vrs definitions. Validates |
1155 | each table entry to ensure the corresponding row is not already |
1156 | delete marked. Where validation is successful, a logical delete |
1157 | is performed setting delete_mark=1 |
1158 | In the case of any failure a rollback is instigated. |
1159 | |
1160 | HISTORY |
1161 | 03-AUG-2002 K.Y.Hunt |
1162 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
1163 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
1164 | GMD_SPEC_GRP.get_who() procedure |
1165 | |
1166 +=========================================================================+
1167 */
1168
1169 PROCEDURE DELETE_WIP_SPEC_VRS
1170 ( p_api_version IN NUMBER
1171 , p_init_msg_list IN VARCHAR2
1172 , p_commit IN VARCHAR2
1173 , p_validation_level IN VARCHAR2
1174 , p_wip_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.wip_spec_vrs_tbl
1175 , p_user_name IN VARCHAR2
1176 , x_deleted_rows OUT NOCOPY NUMBER
1177 , x_return_status OUT NOCOPY VARCHAR2
1178 , x_msg_count OUT NOCOPY NUMBER
1179 , x_msg_data OUT NOCOPY VARCHAR2
1180 )
1181 IS
1182 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_WIP_SPEC_VRS';
1183 l_api_version CONSTANT NUMBER := 1.0;
1184 l_msg_count NUMBER :=0;
1185 l_msg_data VARCHAR2(2000);
1186 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1187 l_spec GMD_SPECIFICATIONS%ROWTYPE;
1188 l_wip_spec_vrs GMD_WIP_SPEC_VRS%ROWTYPE;
1189 l_deleted_rows NUMBER :=0;
1190
1191 BEGIN
1192
1193
1194 -- Standard Start OF API savepoint
1195 -- ===============================
1196 SAVEPOINT Delete_WIP_Spec_VRS;
1197
1198 -- Standard call to check for call compatibility.
1199 -- ==============================================
1200
1201 IF NOT FND_API.Compatible_API_CALL
1202 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
1203 THEN
1204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1205 END IF;
1206
1207 -- Initialize message list if p_int_msg_list is set TRUE.
1208 -- ======================================================
1209 IF FND_API.to_boolean(p_init_msg_list)
1210 THEN
1211 FND_MSG_PUB.Initialize;
1212 END IF;
1213
1214 -- Initialize API return Parameters
1215 -- ================================
1216 x_return_status := FND_API.G_RET_STS_SUCCESS;
1217
1218 -- Initialize Local Variables
1219 -- ==========================
1220 l_spec.spec_id := 0;
1221
1222 -- Validate user_name
1223 -- ==================
1224 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
1225 ,x_user_id => l_spec.last_updated_by);
1226
1227 IF NVL(l_spec.last_updated_by, -1) < 0
1228 THEN
1229 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
1230 'l_user_name', p_user_name);
1231 RAISE FND_API.G_EXC_ERROR;
1232 END IF;
1233
1234 -- Process each of the WIP spec validity rules
1235 -- ===========================================
1236 FOR i in 1..p_wip_spec_vrs_tbl.COUNT LOOP
1237 l_wip_spec_vrs := p_wip_spec_vrs_tbl(i);
1238 -- Ensure the owning spec_id is supplied
1239 -- =====================================
1240 IF ( l_wip_spec_vrs.spec_id IS NULL )
1241 THEN
1242 -- raise validation error
1243 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
1244 RAISE FND_API.G_EXC_ERROR;
1245 END IF;
1246
1247 -- Retrieve and validate the owning SPEC if it is not already retrieved/validated
1248 -- ==============================================================================
1249 IF l_spec.spec_id <> l_wip_spec_vrs.spec_id
1250 THEN
1251 -- Validate to ensure spec is in a suitable state to delete mark
1252 -- ==============================================================
1253 GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => l_wip_spec_vrs.spec_id
1254 , x_return_status => l_return_status
1255 , x_message_data => l_msg_data
1256 );
1257 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1258 RAISE FND_API.G_EXC_ERROR;
1259 END IF;
1260
1261 -- Lock the SPEC ahead of manipulating WIP_SPEC_VRS
1262 -- ======================================================
1263 IF NOT GMD_Specifications_PVT.Lock_Row(l_wip_spec_vrs.spec_id)
1264 THEN
1265 -- Report Failure to obtain locks
1266 -- ==============================
1267 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1268 'l_table_name', 'GMD_SPECIFICATIONS',
1269 'l_column_name', 'SPEC_ID',
1270 'l_key_value', l_wip_spec_vrs.spec_id);
1271 RAISE FND_API.G_EXC_ERROR;
1272 END IF;
1273 END IF; -- end of spec validation
1274
1275 -- Validate to ensure validity_rule exists and is not already delete marked
1276 -- ========================================================================
1277 GMD_SPEC_VRS_GRP.VALIDATE_BEFORE_DELETE_WIP_VRS
1278 ( p_spec_id => l_wip_spec_vrs.spec_id
1279 , p_spec_vr_id => l_wip_spec_vrs.spec_vr_id
1280 , x_return_status => l_return_status
1281 , x_message_data => l_msg_data
1282 );
1283 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1284 RAISE FND_API.G_EXC_ERROR;
1285 END IF;
1286
1287
1288 -- Lock the validity rule ahead of deleting
1289 -- ========================================
1290 IF NOT GMD_WIP_SPEC_VRS_PVT.Lock_Row( l_wip_spec_vrs.spec_vr_id)
1291 THEN
1292 -- Report Failure to obtain locks
1293 -- ==============================
1294 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1295 'l_table_name', 'GMD_WIP_SPEC_VRS',
1296 'l_column_name', 'SPEC_VR_ID',
1297 'l_key_value', l_wip_spec_vrs.spec_vr_id);
1298 RAISE FND_API.G_EXC_ERROR;
1299 END IF;
1300
1301 IF NOT GMD_WIP_SPEC_VRS_PVT.Delete_Row ( p_spec_vr_id => l_wip_spec_vrs.spec_vr_id
1302 , p_last_update_date => sysdate
1303 , p_last_updated_by => l_spec.last_updated_by
1304 )
1305 THEN
1306 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
1307 'l_table_name', 'GMD_WIP_SPEC_VRS',
1308 'l_column_name', 'SPEC_VR_ID',
1309 'l_key_value', l_wip_spec_vrs.spec_vr_id);
1310 RAISE FND_API.G_EXC_ERROR;
1311 ELSE
1312 x_deleted_rows := i;
1313 END IF;
1314
1315 END LOOP;
1316
1317 -- Standard Check of p_commit.
1318 IF FND_API.to_boolean(p_commit)
1319 THEN
1320 COMMIT WORK;
1321 END IF;
1322
1323 x_return_status := l_return_status;
1324
1325 EXCEPTION
1326 WHEN FND_API.G_EXC_ERROR THEN
1327 ROLLBACK TO Delete_WIP_Spec_VRS;
1328 x_deleted_rows := 0;
1329 x_return_status := FND_API.G_RET_STS_ERROR;
1330 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1331 , p_count => x_msg_count
1332 , p_data => x_msg_data
1333 );
1334
1335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1336 ROLLBACK TO Delete_WIP_Spec_VRS;
1337 x_deleted_rows := 0;
1338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1339 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1340 , p_count => x_msg_count
1341 , p_data => x_msg_data
1342 );
1343
1344
1345
1346 WHEN OTHERS THEN
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 ROLLBACK TO Delete_WIP_Spec_VRS;
1349 x_deleted_rows := 0;
1350 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1351 , l_api_name
1352 );
1353
1354 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1355 , p_count => x_msg_count
1356 , p_data => x_msg_data
1357 );
1358
1359 END DELETE_WIP_SPEC_VRS;
1360
1361 /*
1362 +=========================================================================+
1363 | Name : DELETE_CUSTOMER_SPEC_VRS |
1364 | Type : PUBLIC |
1365 | |
1366 | |
1367 | DESCRIPTION |
1368 | Accepts a table of customer_spec_vrs definitions. Validates |
1369 | each table entry to ensure the corresponding row is not already |
1370 | delete marked. Where validation is successful, a logical delete |
1371 | is performed setting delete_mark=1 |
1372 | In the case of any failure a rollback is instigated. |
1373 | |
1374 | HISTORY |
1375 | 03-AUG-2002 K.Y.Hunt |
1376 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
1377 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
1378 | GMD_SPEC_GRP.get_who() procedure |
1379 | |
1380 +=========================================================================+
1381 */
1382
1383 PROCEDURE DELETE_CUSTOMER_SPEC_VRS
1384 ( p_api_version IN NUMBER
1385 , p_init_msg_list IN VARCHAR2
1386 , p_commit IN VARCHAR2
1387 , p_validation_level IN VARCHAR2
1388 , p_customer_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl
1389 , p_user_name IN VARCHAR2
1390 , x_deleted_rows OUT NOCOPY NUMBER
1391 , x_return_status OUT NOCOPY VARCHAR2
1392 , x_msg_count OUT NOCOPY NUMBER
1393 , x_msg_data OUT NOCOPY VARCHAR2
1394 )
1395 IS
1396 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_CUSTOMER_SPEC_VRS';
1397 l_api_version CONSTANT NUMBER := 1.0;
1398 l_msg_count NUMBER :=0;
1399 l_msg_data VARCHAR2(2000);
1400 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1401 l_spec GMD_SPECIFICATIONS%ROWTYPE;
1402 l_customer_spec_vrs GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
1403 l_deleted_rows NUMBER :=0;
1404
1405 BEGIN
1406
1407
1408 -- Standard Start OF API savepoint
1409 -- ===============================
1410 SAVEPOINT Delete_Customer_Spec_VRS;
1411
1412 -- Standard call to check for call compatibility.
1413 -- ==============================================
1414
1415 IF NOT FND_API.Compatible_API_CALL
1416 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
1417 THEN
1418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1419 END IF;
1420
1421 -- Initialize message list if p_int_msg_list is set TRUE.
1422 -- ======================================================
1423 IF FND_API.to_boolean(p_init_msg_list)
1424 THEN
1425 FND_MSG_PUB.Initialize;
1426 END IF;
1427
1428 -- Initialize API return Parameters
1429 -- ================================
1430 x_return_status := FND_API.G_RET_STS_SUCCESS;
1431
1432 -- Initialize Local Variables
1433 -- ==========================
1434 l_spec.spec_id := 0;
1435
1436 -- Validate user_name
1437 -- ==================
1438 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
1439 ,x_user_id => l_spec.last_updated_by);
1440
1441 IF NVL(l_spec.last_updated_by, -1) < 0
1442 THEN
1443 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
1444 'l_user_name', p_user_name);
1445 RAISE FND_API.G_EXC_ERROR;
1446 END IF;
1447
1448 -- Process each of the WIP spec validity rules
1449 -- ===========================================
1450 FOR i in 1..p_customer_spec_vrs_tbl.COUNT LOOP
1451 l_customer_spec_vrs := p_customer_spec_vrs_tbl(i);
1452 -- Ensure the owning spec_id is supplied
1453 -- =====================================
1454 IF ( l_customer_spec_vrs.spec_id IS NULL )
1455 THEN
1456 -- raise validation error
1457 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
1458 RAISE FND_API.G_EXC_ERROR;
1459 END IF;
1460
1461 -- Retrieve and validate the owning SPEC if it is not already retrieved/validated
1462 -- ==============================================================================
1463 IF l_spec.spec_id <> l_customer_spec_vrs.spec_id
1464 THEN
1465 -- Validate to ensure spec is in a suitable state to delete mark
1466 -- ==============================================================
1467 GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => l_customer_spec_vrs.spec_id
1468 , x_return_status => l_return_status
1469 , x_message_data => l_msg_data
1470 );
1471 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1472 RAISE FND_API.G_EXC_ERROR;
1473 END IF;
1474
1475 -- Lock the SPEC ahead of manipulating CUSTOMER_SPEC_VRS
1476 -- ======================================================
1477 IF NOT GMD_Specifications_PVT.Lock_Row(l_customer_spec_vrs.spec_id)
1478 THEN
1479 -- Report Failure to obtain locks
1480 -- ==============================
1481 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1482 'l_table_name', 'GMD_SPECIFICATIONS',
1483 'l_column_name', 'SPEC_ID',
1484 'l_key_value', l_customer_spec_vrs.spec_id);
1485 RAISE FND_API.G_EXC_ERROR;
1486 END IF;
1487 END IF; -- end of spec validation
1488
1489 -- Validate to ensure validity_rule exists and is not already delete marked
1490 -- ========================================================================
1491 GMD_SPEC_VRS_GRP.VALIDATE_BEFORE_DELETE_CST_VRS
1492 ( p_spec_id => l_customer_spec_vrs.spec_id
1493 , p_spec_vr_id => l_customer_spec_vrs.spec_vr_id
1494 , x_return_status => l_return_status
1495 , x_message_data => l_msg_data
1496 );
1497 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1498 RAISE FND_API.G_EXC_ERROR;
1499 END IF;
1500
1501
1502 -- Lock the validity rule ahead of deleting
1503 -- ========================================
1504 IF NOT GMD_CUSTOMER_SPEC_VRS_PVT.Lock_Row( l_customer_spec_vrs.spec_vr_id)
1505 THEN
1506 -- Report Failure to obtain locks
1507 -- ==============================
1508 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1509 'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
1510 'l_column_name', 'SPEC_VR_ID',
1511 'l_key_value', l_customer_spec_vrs.spec_vr_id);
1512 RAISE FND_API.G_EXC_ERROR;
1513 END IF;
1514
1515 IF NOT GMD_CUSTOMER_SPEC_VRS_PVT.Delete_Row
1516 ( p_spec_vr_id => l_customer_spec_vrs.spec_vr_id
1517 , p_last_update_date => sysdate
1518 , p_last_updated_by => l_spec.last_updated_by
1519 )
1520 THEN
1521 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
1522 'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
1523 'l_column_name', 'SPEC_VR_ID',
1524 'l_key_value', l_customer_spec_vrs.spec_vr_id);
1525 RAISE FND_API.G_EXC_ERROR;
1526 ELSE
1527 x_deleted_rows := i;
1528 END IF;
1529
1530 END LOOP;
1531
1532 -- Standard Check of p_commit.
1533 IF FND_API.to_boolean(p_commit)
1534 THEN
1535 COMMIT WORK;
1536 END IF;
1537
1538 x_return_status := l_return_status;
1539
1540 EXCEPTION
1541 WHEN FND_API.G_EXC_ERROR THEN
1542 ROLLBACK TO Delete_Customer_Spec_VRS;
1543 x_deleted_rows := 0;
1544 x_return_status := FND_API.G_RET_STS_ERROR;
1545 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1546 , p_count => x_msg_count
1547 , p_data => x_msg_data
1548 );
1549
1550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1551 ROLLBACK TO Delete_Customer_Spec_VRS;
1552 x_deleted_rows := 0;
1553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1554 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1555 , p_count => x_msg_count
1556 , p_data => x_msg_data
1557 );
1558
1559
1560
1561 WHEN OTHERS THEN
1562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1563 ROLLBACK TO Delete_Customer_Spec_VRS;
1564 x_deleted_rows := 0;
1565 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1566 , l_api_name
1567 );
1568
1569 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1570 , p_count => x_msg_count
1571 , p_data => x_msg_data
1572 );
1573
1574 END DELETE_CUSTOMER_SPEC_VRS;
1575
1576 /*
1577 +=========================================================================+
1578 | Name : DELETE_SUPPLIER_SPEC_VRS |
1579 | Type : PUBLIC |
1580 | |
1581 | |
1582 | DESCRIPTION |
1583 | Accepts a table of supplier_spec_vrs definitions. Validates |
1584 | each table entry to ensure the corresponding row is not already |
1585 | delete marked. Where validation is successful, a logical delete |
1586 | is performed setting delete_mark=1 |
1587 | In the case of any failure a rollback is instigated. |
1588 | |
1589 | HISTORY |
1590 | 03-AUG-2002 K.Y.Hunt |
1591 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
1592 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
1593 | GMD_SPEC_GRP.get_who() procedure |
1594 | |
1595 +=========================================================================+
1596 */
1597 PROCEDURE DELETE_SUPPLIER_SPEC_VRS
1598 ( p_api_version IN NUMBER
1599 , p_init_msg_list IN VARCHAR2
1600 , p_commit IN VARCHAR2
1601 , p_validation_level IN VARCHAR2
1602 , p_supplier_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.supplier_spec_vrs_tbl
1603 , p_user_name IN VARCHAR2
1604 , x_deleted_rows OUT NOCOPY NUMBER
1605 , x_return_status OUT NOCOPY VARCHAR2
1606 , x_msg_count OUT NOCOPY NUMBER
1607 , x_msg_data OUT NOCOPY VARCHAR2
1608 )
1609 IS
1610 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SUPPLIER_SPEC_VRS';
1611 l_api_version CONSTANT NUMBER := 1.0;
1612 l_msg_count NUMBER :=0;
1613 l_msg_data VARCHAR2(2000);
1614 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1615 l_spec GMD_SPECIFICATIONS%ROWTYPE;
1616 l_supplier_spec_vrs GMD_SUPPLIER_SPEC_VRS%ROWTYPE;
1617 l_deleted_rows NUMBER :=0;
1618
1619 BEGIN
1620
1621
1622 -- Standard Start OF API savepoint
1623 -- ===============================
1624 SAVEPOINT Delete_Supplier_Spec_VRS;
1625
1626 -- Standard call to check for call compatibility.
1627 -- ==============================================
1628
1629 IF NOT FND_API.Compatible_API_CALL
1630 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
1631 THEN
1632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1633 END IF;
1634
1635 -- Initialize message list if p_int_msg_list is set TRUE.
1636 -- ======================================================
1637 IF FND_API.to_boolean(p_init_msg_list)
1638 THEN
1639 FND_MSG_PUB.Initialize;
1640 END IF;
1641
1642 -- Initialize API return Parameters
1643 -- ================================
1644 x_return_status := FND_API.G_RET_STS_SUCCESS;
1645
1646 -- Initialize Local Variables
1647 -- ==========================
1648 l_spec.spec_id := 0;
1649
1650 -- Validate user_name
1651 -- ==================
1652 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
1653 ,x_user_id => l_spec.last_updated_by);
1654
1655 IF NVL(l_spec.last_updated_by, -1) < 0
1656 THEN
1657 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
1658 'l_user_name', p_user_name);
1659 RAISE FND_API.G_EXC_ERROR;
1660 END IF;
1661
1662 -- Process each of the WIP spec validity rules
1663 -- ===========================================
1664 FOR i in 1..p_supplier_spec_vrs_tbl.COUNT LOOP
1665 l_supplier_spec_vrs := p_supplier_spec_vrs_tbl(i);
1666 -- Ensure the owning spec_id is supplied
1667 -- =====================================
1668 IF ( l_supplier_spec_vrs.spec_id IS NULL )
1669 THEN
1670 -- raise validation error
1671 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
1672 RAISE FND_API.G_EXC_ERROR;
1673 END IF;
1674
1675 -- Retrieve and validate the owning SPEC if it is not already retrieved/validated
1676 -- ==============================================================================
1677 IF l_spec.spec_id <> l_supplier_spec_vrs.spec_id
1678 THEN
1679 -- Validate to ensure spec is in a suitable state to delete mark
1680 -- ==============================================================
1681 GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => l_supplier_spec_vrs.spec_id
1682 , x_return_status => l_return_status
1683 , x_message_data => l_msg_data
1684 );
1685 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1686 RAISE FND_API.G_EXC_ERROR;
1687 END IF;
1688
1689 -- Lock the SPEC ahead of manipulating SUPPLIER_SPEC_VRS
1690 -- ======================================================
1691 IF NOT GMD_Specifications_PVT.Lock_Row(l_supplier_spec_vrs.spec_id)
1692 THEN
1693 -- Report Failure to obtain locks
1694 -- ==============================
1695 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1696 'l_table_name', 'GMD_SPECIFICATIONS',
1697 'l_column_name', 'SPEC_ID',
1698 'l_key_value', l_supplier_spec_vrs.spec_id);
1699 RAISE FND_API.G_EXC_ERROR;
1700 END IF;
1701 END IF; -- end of spec validation
1702
1703 -- Validate to ensure validity_rule exists and is not already delete marked
1704 -- ========================================================================
1705 GMD_SPEC_VRS_GRP.VALIDATE_BEFORE_DELETE_SUP_VRS
1706 ( p_spec_id => l_supplier_spec_vrs.spec_id
1707 , p_spec_vr_id => l_supplier_spec_vrs.spec_vr_id
1708 , x_return_status => l_return_status
1709 , x_message_data => l_msg_data
1710 );
1711 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1712 RAISE FND_API.G_EXC_ERROR;
1713 END IF;
1714
1715
1716 -- Lock the validity rule ahead of deleting
1717 -- ========================================
1718 IF NOT GMD_SUPPLIER_SPEC_VRS_PVT.Lock_Row( l_supplier_spec_vrs.spec_vr_id)
1719 THEN
1720 -- Report Failure to obtain locks
1721 -- ==============================
1722 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1723 'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
1724 'l_column_name', 'SPEC_VR_ID',
1725 'l_key_value', l_supplier_spec_vrs.spec_vr_id);
1726 RAISE FND_API.G_EXC_ERROR;
1727 END IF;
1728
1729 IF NOT GMD_SUPPLIER_SPEC_VRS_PVT.Delete_Row
1730 ( p_spec_vr_id => l_supplier_spec_vrs.spec_vr_id
1731 , p_last_update_date => sysdate
1732 , p_last_updated_by => l_spec.last_updated_by
1733 )
1734 THEN
1735 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
1736 'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
1737 'l_column_name', 'SPEC_VR_ID',
1738 'l_key_value', l_supplier_spec_vrs.spec_vr_id);
1739 RAISE FND_API.G_EXC_ERROR;
1740 ELSE
1741 x_deleted_rows := i;
1742 END IF;
1743
1744 END LOOP;
1745
1746 -- Standard Check of p_commit.
1747 IF FND_API.to_boolean(p_commit)
1748 THEN
1749 COMMIT WORK;
1750 END IF;
1751
1752 x_return_status := l_return_status;
1753
1754 EXCEPTION
1755 WHEN FND_API.G_EXC_ERROR THEN
1756 ROLLBACK TO Delete_Supplier_Spec_VRS;
1757 x_deleted_rows := 0;
1758 x_return_status := FND_API.G_RET_STS_ERROR;
1759 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1760 , p_count => x_msg_count
1761 , p_data => x_msg_data
1762 );
1763
1764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1765 ROLLBACK TO Delete_Supplier_Spec_VRS;
1766 x_deleted_rows := 0;
1767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1768 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1769 , p_count => x_msg_count
1770 , p_data => x_msg_data
1771 );
1772
1773
1774
1775 WHEN OTHERS THEN
1776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1777 ROLLBACK TO Delete_Supplier_Spec_VRS;
1778 x_deleted_rows := 0;
1779 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1780 , l_api_name
1781 );
1782
1783 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1784 , p_count => x_msg_count
1785 , p_data => x_msg_data
1786 );
1787
1788 END DELETE_SUPPLIER_SPEC_VRS;
1789
1790
1791 /*
1792 +=========================================================================+
1793 | Name : DELETE_MONITORING_SPEC_VRS |
1794 | Type : PUBLIC |
1795 | |
1796 | |
1797 | DESCRIPTION |
1798 | Accepts a table of monitoring_spec_vrs definitions. Validates |
1799 | each table entry to ensure the corresponding row is not already |
1800 | delete marked. Where validation is successful, a logical delete |
1801 | is performed setting delete_mark=1 |
1802 | In the case of any failure a rollback is instigated. |
1803 | |
1804 | HISTORY |
1805 | 26-Jan-2004 Manish Gupta |
1806 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
1807 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
1808 | GMD_SPEC_GRP.get_who() procedure |
1809 | |
1810 +=========================================================================+
1811 */
1812
1813 PROCEDURE DELETE_MONITORING_SPEC_VRS
1814 ( p_api_version IN NUMBER
1815 , p_init_msg_list IN VARCHAR2
1816 , p_commit IN VARCHAR2
1817 , p_validation_level IN VARCHAR2
1818 , p_monitoring_spec_vrs_tbl IN GMD_SPEC_VRS_PUB.MONITORING_spec_vrs_tbl
1819 , p_user_name IN VARCHAR2
1820 , x_deleted_rows OUT NOCOPY NUMBER
1821 , x_return_status OUT NOCOPY VARCHAR2
1822 , x_msg_count OUT NOCOPY NUMBER
1823 , x_msg_data OUT NOCOPY VARCHAR2
1824 )
1825 IS
1826 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_MONITORING_SPEC_VRS';
1827 l_api_version CONSTANT NUMBER := 1.0;
1828 l_msg_count NUMBER :=0;
1829 l_msg_data VARCHAR2(2000);
1830 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1831 l_spec GMD_SPECIFICATIONS%ROWTYPE;
1832 l_monitoring_spec_vrs GMD_MONITORING_SPEC_VRS%ROWTYPE;
1833 l_deleted_rows NUMBER :=0;
1834
1835 BEGIN
1836
1837
1838 -- Standard Start OF API savepoint
1839 -- ===============================
1840 SAVEPOINT Delete_Monitoring_Spec_VRS;
1841
1842 -- Standard call to check for call compatibility.
1843 -- ==============================================
1844
1845 IF NOT FND_API.Compatible_API_CALL
1846 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
1847 THEN
1848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1849 END IF;
1850
1851 -- Initialize message list if p_int_msg_list is set TRUE.
1852 -- ======================================================
1853 IF FND_API.to_boolean(p_init_msg_list)
1854 THEN
1855 FND_MSG_PUB.Initialize;
1856 END IF;
1857
1858 -- Initialize API return Parameters
1859 -- ================================
1860 x_return_status := FND_API.G_RET_STS_SUCCESS;
1861
1862 -- Initialize Local Variables
1863 -- ==========================
1864 l_spec.spec_id := 0;
1865
1866 -- Validate user_name
1867 -- ==================
1868 GMD_SPEC_GRP.GET_WHO ( p_user_name => p_user_name
1869 ,x_user_id => l_spec.last_updated_by);
1870
1871 IF NVL(l_spec.last_updated_by, -1) < 0
1872 THEN
1873 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
1874 'l_user_name', p_user_name);
1875 RAISE FND_API.G_EXC_ERROR;
1876 END IF;
1877
1878 -- Process each of the WIP spec validity rules
1879 -- ===========================================
1880 FOR i in 1..p_monitoring_spec_vrs_tbl.COUNT LOOP
1881 l_monitoring_spec_vrs := p_monitoring_spec_vrs_tbl(i);
1882 -- Ensure the owning spec_id is supplied
1883 -- =====================================
1884 IF ( l_monitoring_spec_vrs.spec_id IS NULL )
1885 THEN
1886 -- raise validation error
1887 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
1888 RAISE FND_API.G_EXC_ERROR;
1889 END IF;
1890
1891 -- Retrieve and validate the owning SPEC if it is not already retrieved/validated
1892 -- ==============================================================================
1893 IF l_spec.spec_id <> l_monitoring_spec_vrs.spec_id
1894 THEN
1895 -- Validate to ensure spec is in a suitable state to delete mark
1896 -- ==============================================================
1897 GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => l_monitoring_spec_vrs.spec_id
1898 , x_return_status => l_return_status
1899 , x_message_data => l_msg_data
1900 );
1901 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1902 RAISE FND_API.G_EXC_ERROR;
1903 END IF;
1904
1905 -- Lock the SPEC ahead of manipulating MONITORING_SPEC_VRS
1906 -- ======================================================
1907 IF NOT GMD_Specifications_PVT.Lock_Row(l_monitoring_spec_vrs.spec_id)
1908 THEN
1909 -- Report Failure to obtain locks
1910 -- ==============================
1911 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1912 'l_table_name', 'GMD_SPECIFICATIONS',
1913 'l_column_name', 'SPEC_ID',
1914 'l_key_value', l_monitoring_spec_vrs.spec_id);
1915 RAISE FND_API.G_EXC_ERROR;
1916 END IF;
1917 END IF; -- end of spec validation
1918
1919 -- Validate to ensure validity_rule exists and is not already delete marked
1920 -- To be added later as the group layer is locked by Sierra.
1921 -- ========================================================================
1922 /*GMD_SPEC_VRS_GRP.VALIDATE_BEFORE_DELETE_MON_VRS
1923 ( p_spec_id => l_monitoring_spec_vrs.spec_id
1924 , p_spec_vr_id => l_monitoring_spec_vrs.spec_vr_id
1925 , x_return_status => l_return_status
1926 , x_message_data => l_msg_data
1927 );
1928 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1929 RAISE FND_API.G_EXC_ERROR;
1930 END IF;*/
1931
1932
1933 -- Lock the validity rule ahead of deleting
1934 -- ========================================
1935 IF NOT GMD_MONITORING_SPEC_VRS_PVT.Lock_Row( l_monitoring_spec_vrs.spec_vr_id)
1936 THEN
1937 -- Report Failure to obtain locks
1938 -- ==============================
1939 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1940 'l_table_name', 'GMD_monitoring_SPEC_VRS',
1941 'l_column_name', 'SPEC_VR_ID',
1942 'l_key_value', l_monitoring_spec_vrs.spec_vr_id);
1943 RAISE FND_API.G_EXC_ERROR;
1944 END IF;
1945
1946 IF NOT GMD_MONITORING_SPEC_VRS_PVT.Delete_Row
1947 ( p_spec_vr_id => l_monitoring_spec_vrs.spec_vr_id
1948 , p_last_update_date => sysdate
1949 , p_last_updated_by => l_spec.last_updated_by
1950 )
1951 THEN
1952 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
1953 'l_table_name', 'GMD_MONITORING_SPEC_VRS',
1954 'l_column_name', 'SPEC_VR_ID',
1955 'l_key_value', l_monitoring_spec_vrs.spec_vr_id);
1956 RAISE FND_API.G_EXC_ERROR;
1957 ELSE
1958 x_deleted_rows := i;
1959 END IF;
1960
1961 END LOOP;
1962
1963 -- Standard Check of p_commit.
1964 IF FND_API.to_boolean(p_commit)
1965 THEN
1966 COMMIT WORK;
1967 END IF;
1968
1969 x_return_status := l_return_status;
1970
1971 EXCEPTION
1972 WHEN FND_API.G_EXC_ERROR THEN
1973 ROLLBACK TO Delete_monitoring_Spec_VRS;
1974 x_deleted_rows := 0;
1975 x_return_status := FND_API.G_RET_STS_ERROR;
1976 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1977 , p_count => x_msg_count
1978 , p_data => x_msg_data
1979 );
1980
1981 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1982 ROLLBACK TO Delete_monitoring_Spec_VRS;
1983 x_deleted_rows := 0;
1984 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1985 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1986 , p_count => x_msg_count
1987 , p_data => x_msg_data
1988 );
1989
1990
1991
1992 WHEN OTHERS THEN
1993 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1994 ROLLBACK TO Delete_monitoring_Spec_VRS;
1995 x_deleted_rows := 0;
1996 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1997 , l_api_name
1998 );
1999
2000 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
2001 , p_count => x_msg_count
2002 , p_data => x_msg_data
2003 );
2004
2005 END DELETE_MONITORING_SPEC_VRS;
2006 END GMD_SPEC_VRS_PUB;