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