[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIR_GROUPS_PVT
Source
1 PACKAGE BODY CSD_REPAIR_GROUPS_PVT AS
2 /* $Header: csdvrpgb.pls 115.12 2002/11/15 22:43:46 swai noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_REPAIR_GROUPS_PVT';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvrpgb.pls';
7 g_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
8
9 ----------------------------------------------------
10 -- procedure name: create_repair_groups
11 -- description : procedure used to create
12 -- group repair orders
13 --
14 ----------------------------------------------------
15 PROCEDURE CREATE_REPAIR_GROUPS(
16 p_api_version IN NUMBER,
17 p_commit IN VARCHAR2,
18 p_init_msg_list IN VARCHAR2,
19 p_validation_level IN NUMBER,
20 x_repair_order_group_rec IN OUT NOCOPY CSD_REPAIR_GROUPS_PVT.REPAIR_ORDER_GROUP_REC,
21 x_repair_group_id OUT NOCOPY NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2
25 )
26 IS
27
28 l_api_name CONSTANT VARCHAR2(30) := 'Create_Repair_Groups';
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(100);
32 l_msg_index NUMBER;
33 l_repair_group_id NUMBER;
34 l_repair_group_number VARCHAR2(30);
35 l_count NUMBER;
36
37 CURSOR C1 IS
38 SELECT CSD_REPAIR_ORDER_GROUPS_S1.NEXTVAL
39 FROM sys.dual;
40
41
42 BEGIN
43 -----------------------------------
44 --Standard Start of API savepoint
45 -----------------------------------
46 SAVEPOINT create_repair_order_group;
47
48 -----------------------------------------------------
49 -- Standard call to check for call compatibility.
50 -----------------------------------------------------
51 IF NOT FND_API.Compatible_API_Call (l_api_version,
52 p_api_version,
53 l_api_name ,
54 G_PKG_NAME)
55 THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58 ---------------------------------------------------------------
59 -- Initialize message list if p_init_msg_list is set to TRUE.
60 ---------------------------------------------------------------
61 IF FND_API.to_Boolean(p_init_msg_list)
62 THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66 --------------------------------------------
67 -- Initialize API return status to success
68 --------------------------------------------
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 --------------------
72 -- Api body starts
73 --------------------
74 if (g_debug > 0) then
75 csd_gen_utility_pvt.dump_api_info
76 ( p_pkg_name => G_PKG_NAME,
77 p_api_name => l_api_name );
78 end if;
79 ------------------------------------------
80 -- Dump the in parameters in the log file
81 ------------------------------------------
82 --IF fnd_profile.value('CSD_DEBUG_LEVEL') > 5 THEN
83 if (g_debug > 5) then
84 csd_gen_utility_pvt.dump_repair_order_group_rec
85 ( p_repair_order_group_rec => x_repair_order_group_rec);
86 END IF;
87
88 -----------------------------------
89 -- Check for required parameters
90 -----------------------------------
91
92 IF (g_debug > 0 ) THEN
93 csd_gen_utility_pvt.ADD('Check reqd parameter : Incident Id');
94 END IF;
95
96
97 CSD_REPAIRS_UTIL.check_reqd_param
98 (p_param_value => x_repair_order_group_rec.incident_id,
99 p_param_name => 'INCIDENT_ID',
100 p_api_name => l_api_name
101 );
102
103 IF (g_debug > 0 ) THEN
104 csd_gen_utility_pvt.ADD('Check reqd parameter : Inventory Item Id');
105 END IF;
106
107
108 CSD_REPAIRS_UTIL.check_reqd_param
109 (p_param_value => x_repair_order_group_rec.inventory_item_id,
110 p_param_name => 'INVENTORY_ITEM_ID',
111 p_api_name => l_api_name
112 );
113
114 IF (g_debug > 0 ) THEN
115 csd_gen_utility_pvt.ADD('Check reqd parameter : Unit of Measure');
116 END IF;
117
118
119 CSD_REPAIRS_UTIL.check_reqd_param
120 (p_param_value => x_repair_order_group_rec.unit_of_measure,
121 p_param_name => 'UNIT_OF_MEASURE',
122 p_api_name => l_api_name
123 );
124
125 IF (g_debug > 0 ) THEN
126 csd_gen_utility_pvt.ADD('Check reqd parameter : Repair Type Id');
127 END IF;
128
129
130 CSD_REPAIRS_UTIL.check_reqd_param
131 (p_param_value => x_repair_order_group_rec.repair_type_id,
132 p_param_name => 'REPAIR_TYPE_ID',
133 p_api_name => l_api_name
134 );
135
136 ---------------------------------
137 -- Validate the incident ID
138 ---------------------------------
139 IF (g_debug > 0 ) THEN
140 csd_gen_utility_pvt.ADD('Validate Incident id');
141 END IF;
142
143
144 IF NOT( CSD_PROCESS_UTIL.Validate_incident_id
145 ( p_incident_id => x_repair_order_group_rec.incident_id )) THEN
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148
149 ---------------------------------
150 -- Validate the repair type ID
151 ---------------------------------
152 IF (g_debug > 0 ) THEN
153 csd_gen_utility_pvt.ADD('Validate repair type id');
154 END IF;
155
156
157 IF NOT( CSD_PROCESS_UTIL.Validate_repair_type_id
158 ( p_repair_type_id => x_repair_order_group_rec.repair_type_id )) THEN
159 RAISE FND_API.G_EXC_ERROR;
160 END IF;
161
162 -----------------------------------
163 -- Validate the Inventory Item ID
164 -----------------------------------
165 IF (g_debug > 0 ) THEN
166 csd_gen_utility_pvt.ADD('Validate Inventory item id');
167 END IF;
168
169
170 IF NOT( CSD_PROCESS_UTIL.Validate_inventory_item_id
171 ( p_inventory_item_id => x_repair_order_group_rec.inventory_item_id )) THEN
172 RAISE FND_API.G_EXC_ERROR;
173 END IF;
174
175
176 ---------------------------------
177 -- Validate the UOM Code
178 ---------------------------------
179 IF (g_debug > 0 ) THEN
180 csd_gen_utility_pvt.ADD('Unit of Measure');
181 END IF;
182
183
184 IF NOT( CSD_PROCESS_UTIL.Validate_unit_of_measure
185 ( p_unit_of_measure => x_repair_order_group_rec.unit_of_measure )) THEN
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188
189 ---------------------------------
190 -- Validate Wip Entity Id
191 ---------------------------------
192 IF(x_repair_order_group_rec.wip_entity_id is not null and
193 x_repair_order_group_rec.wip_entity_id <> FND_API.G_MISS_NUM) then
194
195 IF (g_debug > 0 ) THEN
196 csd_gen_utility_pvt.ADD('Wip Entity Id');
197 END IF;
198
199
200 IF NOT( CSD_PROCESS_UTIL.Validate_wip_entity_id
201 ( p_wip_entity_id => x_repair_order_group_rec.wip_entity_id )) THEN
202 RAISE FND_API.G_EXC_ERROR;
203 END IF;
204 END IF;
205 --------------------------------------------
206 -- Assigning the group id and group number
207 --------------------------------------------
208 OPEN C1;
209 FETCH C1 INTO l_repair_group_id;
210 CLOSE C1;
211
212 l_repair_group_number := to_char(l_repair_group_id);
213
214 ---------------------------------------
215 -- Validate group id and group number
216 ---------------------------------------
217 IF (g_debug > 0 ) THEN
218 csd_gen_utility_pvt.ADD('Validate the Group ID and Number');
219 END IF;
220
221
222 if(l_repair_group_id is null) then
223 FND_MESSAGE.Set_Name('CSD', 'CSD_API_REPAIR_GROUP_ID');
224 FND_MESSAGE.Set_Token('REPAIR_GROUP_ID',l_repair_group_id);
225 FND_MSG_PUB.ADD;
226 RAISE FND_API.G_EXC_ERROR;
227 end if;
228 -----------------------
229 -- insert into table
230 -----------------------
231
232 x_repair_order_group_rec.object_version_number := 1;
233
234 IF (g_debug > 0 ) THEN
235 csd_gen_utility_pvt.ADD('Calling : CSD_REPAIR_ORDER_GROUPS_PKG.Insert_Row');
236 END IF;
237
238
239 CSD_REPAIR_ORDER_GROUPS_PKG.Insert_Row
240 (px_REPAIR_GROUP_ID => l_repair_group_id,
241 p_INCIDENT_ID => x_repair_order_group_rec.incident_id,
242 p_REPAIR_GROUP_NUMBER => l_repair_group_number,
243 p_REPAIR_TYPE_ID => x_repair_order_group_rec.repair_type_id,
244 p_WIP_ENTITY_ID => x_repair_order_group_rec.wip_entity_id,
245 p_INVENTORY_ITEM_ID => x_repair_order_group_rec.inventory_item_id,
246 p_UNIT_OF_MEASURE => x_repair_order_group_rec.unit_of_measure,
247 p_GROUP_QUANTITY => x_repair_order_group_rec.group_quantity,
248 p_REPAIR_ORDER_QUANTITY => x_repair_order_group_rec.repair_order_quantity,
249 p_RMA_QUANTITY => x_repair_order_group_rec.rma_quantity,
250 p_RECEIVED_QUANTITY => x_repair_order_group_rec.received_quantity,
251 p_APPROVED_QUANTITY => x_repair_order_group_rec.approved_quantity,
252 p_SUBMITTED_QUANTITY => x_repair_order_group_rec.submitted_quantity,
253 p_COMPLETED_QUANTITY => x_repair_order_group_rec.completed_quantity,
254 p_RELEASED_QUANTITY => x_repair_order_group_rec.released_quantity,
255 p_SHIPPED_QUANTITY => x_repair_order_group_rec.shipped_quantity,
256 p_CREATED_BY => FND_GLOBAL.USER_ID,
257 p_CREATION_DATE => SYSDATE,
258 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
259 p_LAST_UPDATE_DATE => SYSDATE,
260 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
261 p_CONTEXT => x_repair_order_group_rec.context,
262 p_ATTRIBUTE1 => x_repair_order_group_rec.attribute1,
263 p_ATTRIBUTE2 => x_repair_order_group_rec.attribute2,
264 p_ATTRIBUTE3 => x_repair_order_group_rec.attribute3,
265 p_ATTRIBUTE4 => x_repair_order_group_rec.attribute4,
266 p_ATTRIBUTE5 => x_repair_order_group_rec.attribute5,
267 p_ATTRIBUTE6 => x_repair_order_group_rec.attribute6,
268 p_ATTRIBUTE7 => x_repair_order_group_rec.attribute7,
269 p_ATTRIBUTE8 => x_repair_order_group_rec.attribute8,
270 p_ATTRIBUTE9 => x_repair_order_group_rec.attribute9,
271 p_ATTRIBUTE10 => x_repair_order_group_rec.attribute10,
272 p_ATTRIBUTE11 => x_repair_order_group_rec.attribute11,
273 p_ATTRIBUTE12 => x_repair_order_group_rec.attribute12,
274 p_ATTRIBUTE13 => x_repair_order_group_rec.attribute13,
275 p_ATTRIBUTE14 => x_repair_order_group_rec.attribute14,
276 p_ATTRIBUTE15 => x_repair_order_group_rec.attribute15,
277 p_OBJECT_VERSION_NUMBER => 1,
278 p_GROUP_TXN_STATUS => x_repair_order_group_rec.group_txn_status,
279 p_GROUP_APPROVAL_STATUS => x_repair_order_group_rec.group_approval_status,
280 p_REPAIR_MODE => x_repair_order_group_rec.repair_mode);
281
282 x_repair_order_group_rec.repair_group_id := l_repair_group_id;
283 x_repair_order_group_rec.repair_group_number := l_repair_group_number;
284 x_repair_group_id := l_repair_group_number;
285
286 -------------
287 -- API Ends
288 -------------
289
290 --------------------------------
291 -- Standard check for p_commit
292 --------------------------------
293
294 IF FND_API.to_Boolean(p_commit)
295 THEN
296 COMMIT;
297 END IF;
298
299 --------------------------------------------------------------------------
300 -- Standard call to get message count and if count is 1, get message info.
301 --------------------------------------------------------------------------
302 FND_MSG_PUB.Count_And_Get
303 (p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306
307 EXCEPTION
308 WHEN FND_API.G_EXC_ERROR THEN
309 x_return_status := FND_API.G_RET_STS_ERROR ;
310 ROLLBACK TO create_repair_order_group;
311 FND_MSG_PUB.Count_And_Get
312 (p_count => x_msg_count,
313 p_data => x_msg_data );
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
316 ROLLBACK TO create_repair_order_group;
317 FND_MSG_PUB.Count_And_Get
318 ( p_count => x_msg_count,
319 p_data => x_msg_data );
320 WHEN OTHERS THEN
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
322 ROLLBACK TO create_repair_order_group;
323 IF FND_MSG_PUB.Check_Msg_Level
324 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
325 THEN
326 FND_MSG_PUB.Add_Exc_Msg
327 (G_PKG_NAME ,
328 l_api_name );
329 END IF;
330 FND_MSG_PUB.Count_And_Get
331 (p_count => x_msg_count,
332 p_data => x_msg_data );
333
334 END CREATE_REPAIR_GROUPS;
335
336 ----------------------------------------------------
337 -- procedure name: update_repair_groups
338 -- description : procedure used to update
339 -- group repair orders
340 --
341 ----------------------------------------------------
342
343 PROCEDURE UPDATE_REPAIR_GROUPS
344 ( p_api_version IN NUMBER,
345 p_commit IN VARCHAR2,
346 p_init_msg_list IN VARCHAR2,
347 p_validation_level IN NUMBER,
348 x_repair_order_group_rec IN OUT NOCOPY CSD_REPAIR_GROUPS_PVT.REPAIR_ORDER_GROUP_REC,
349 x_return_status OUT NOCOPY VARCHAR2,
350 x_msg_count OUT NOCOPY NUMBER,
351 x_msg_data OUT NOCOPY VARCHAR2
352 )
353 IS
354
355 l_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Groups';
356 l_api_version CONSTANT NUMBER := 1.0;
357 l_msg_count NUMBER;
358 l_msg_data VARCHAR2(100);
359 l_msg_index NUMBER;
360 l_obj_ver_num NUMBER;
361 l_repair_group_id NUMBER;
362 l_dummy VARCHAR2(30);
363 l_count NUMBER;
364 l_repair_type_id NUMBER;
365 l_inventory_item_id NUMBER;
366 l_unit_of_measure VARCHAR2(3);
367 l_group_quantity NUMBER;
368
369 CURSOR repair_order_group(p_repair_group_id IN NUMBER) IS
370 SELECT
371 repair_group_id,
372 object_version_number
373 FROM csd_repair_order_groups
374 WHERE repair_group_id = p_repair_group_id;
375
376 BEGIN
377
378 -----------------------------------
379 -- Standard Start of API savepoint
380 -----------------------------------
381 SAVEPOINT update_repair_order_group;
382
383 --------------------------------------------------
384 -- Standard call to check for call compatibility.
385 --------------------------------------------------
386 IF NOT FND_API.Compatible_API_Call (l_api_version,
387 p_api_version,
388 l_api_name ,
389 G_PKG_NAME)
390 THEN
391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
392 END IF;
393
394 --------------------------------------------------------------
395 -- Initialize message list if p_init_msg_list is set to TRUE.
396 --------------------------------------------------------------
397 IF FND_API.to_Boolean(p_init_msg_list)
398 THEN
399 FND_MSG_PUB.initialize;
400 END IF;
401
402 --------------------------------------------
403 -- Initialize API return status to success
404 --------------------------------------------
405 x_return_status := FND_API.G_RET_STS_SUCCESS;
406
407 --------------------
408 -- Api body starts
409 --------------------
410 if (g_debug > 0) then
411
412 csd_gen_utility_pvt.dump_api_info
413 ( p_pkg_name => G_PKG_NAME,
414 p_api_name => l_api_name );
415 end if;
416 ------------------------------------------
417 -- Dump the in parameters in the log file
418 ------------------------------------------
419 --IF fnd_profile.value('CSD_DEBUG_LEVEL') > 5 THEN
420 if (g_debug > 5) then
421 csd_gen_utility_pvt.dump_repair_order_group_rec
422 ( p_repair_order_group_rec => x_repair_order_group_rec);
423 END IF;
424
425
426 ---------------------------------
427 -- Check the required parameter
428 ---------------------------------
429 IF (g_debug > 0 ) THEN
430 csd_gen_utility_pvt.ADD('Check reqd parameter : Repair Group Id');
431 END IF;
432
433
434 CSD_PROCESS_UTIL.Check_Reqd_Param
435 ( p_param_value => x_repair_order_group_rec.repair_group_id,
436 p_param_name => 'REPAIR_GROUP_ID',
437 p_api_name => l_api_name);
438
439 ---------------------------------
440 -- Validate Repair Group Id
441 ---------------------------------
442 IF (g_debug > 0 ) THEN
443 csd_gen_utility_pvt.ADD('Repair Group Id');
444 END IF;
445
446
447 IF NOT( CSD_PROCESS_UTIL.Validate_repair_group_id
448 ( p_repair_group_id => x_repair_order_group_rec.repair_group_id )) THEN
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451
452 ---------------------------------------
453 -- Fetch the Group info
454 ---------------------------------------
455 IF (g_debug > 0 ) THEN
456 csd_gen_utility_pvt.ADD('Fetch the Group Id for Update');
457 END IF;
458
459
460 IF NVL(x_repair_order_group_rec.repair_group_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
461
462 OPEN repair_order_group(x_repair_order_group_rec.repair_group_id);
463 FETCH repair_order_group
464 INTO l_repair_group_id,
465 l_obj_ver_num;
466
467 IF repair_order_group%NOTFOUND THEN
468 FND_MESSAGE.SET_NAME('CSD','CSD_API_RO_GROUP_MISSING');
469 FND_MESSAGE.SET_TOKEN('REPAIR_GROUP_ID',l_repair_group_id);
470 FND_MSG_PUB.ADD;
471 RAISE FND_API.G_EXC_ERROR;
472 END IF;
473
474 IF repair_order_group%ISOPEN THEN
475 CLOSE repair_order_group;
476 END IF;
477
478 END IF;
479
480 --------------------------------
481 -- Validate Obj Version Number
482 --------------------------------
483 IF (g_debug > 0 ) THEN
484 csd_gen_utility_pvt.ADD('Validate the Object Version Number');
485 END IF;
486
487
488 IF NVL(x_repair_order_group_rec.object_version_number,FND_API.G_MISS_NUM) <>l_obj_ver_num THEN
489
490 IF (g_debug > 0 ) THEN
491 csd_gen_utility_pvt.ADD('object version number does not match');
492 END IF;
493
494
495 FND_MESSAGE.SET_NAME('CSD','CSD_GRP_OBJ_VER_MISMATCH');
496 FND_MESSAGE.SET_TOKEN('REPAIR_GROUP_ID',l_repair_group_id);
497 FND_MSG_PUB.ADD;
498 RAISE FND_API.G_EXC_ERROR;
499
500 END IF;
501
502 -----------------------------
503 -- Validate if any RO have
504 -- been created
505 -----------------------------
506 IF (g_debug > 0 ) THEN
507 csd_gen_utility_pvt.ADD('Validate if any RO have been created');
508 END IF;
509
510
511 l_count := 0;
512
513 BEGIN
514 SELECT
515 count(*)
516 INTO l_count
517 FROM csd_repairs
518 WHERE repair_group_id = x_repair_order_group_rec.repair_group_id;
519
520 EXCEPTION
521 WHEN NO_DATA_FOUND THEN
522 null;
523 WHEN OTHERS THEN
524 null;
525 END;
526
527 IF ( l_count > 0 ) then
528
529 BEGIN
530 Select
531 inventory_item_id,
532 repair_type_id,
533 unit_of_measure,
534 group_quantity
535 into
536 l_inventory_item_id,
537 l_repair_type_id,
538 l_unit_of_measure,
539 l_group_quantity
540 from csd_repair_order_groups
541 where repair_group_id = x_repair_order_group_rec.repair_group_id;
542 END;
543 ------------------------------------------------
544 -- Update of item id, repair type,uom,grp qty
545 -- not allowed once repair orders are created
546 ------------------------------------------------
547 IF((x_repair_order_group_rec.inventory_item_id IS NOT NULL) OR
548 (x_repair_order_group_rec.repair_type_id IS NOT NULL) OR
549 (x_repair_order_group_rec.unit_of_measure IS NOT NULL) OR
550 (x_repair_order_group_rec.group_quantity IS NOT NULL)) THEN
551
552 IF( (l_inventory_item_id <> x_repair_order_group_rec.inventory_item_id) OR
553 (l_repair_type_id <> x_repair_order_group_rec.repair_type_id ) OR
554 (l_unit_of_measure <> x_repair_order_group_rec.unit_of_measure) OR
555 (l_group_quantity <> x_repair_order_group_rec.group_quantity)) THEN
556
557 FND_MESSAGE.SET_NAME('CSD','CSD_API_UPDATE_NOT_ALLOWED');
558 FND_MESSAGE.SET_TOKEN('REPAIR_GROUP_ID',x_repair_order_group_rec.repair_group_id);
559 FND_MSG_PUB.ADD;
560
561 RAISE FND_API.G_EXC_ERROR;
562
563 END IF;
564 END IF;
565 END IF;
566
567 -------------------
568 -- Update Call
569 -------------------
570 IF (g_debug > 0 ) THEN
571 csd_gen_utility_pvt.ADD('Calling : CSD_REPAIR_ORDER_GROUPS_PKG.Update_Row');
572 END IF;
573
574
575 CSD_REPAIR_ORDER_GROUPS_PKG.Update_Row(
576 p_REPAIR_GROUP_ID => x_repair_order_group_rec.repair_group_id,
577 p_INCIDENT_ID => x_repair_order_group_rec.incident_id,
578 p_REPAIR_GROUP_NUMBER => x_repair_order_group_rec.repair_group_number,
579 p_REPAIR_TYPE_ID => x_repair_order_group_rec.repair_type_id,
580 p_WIP_ENTITY_ID => x_repair_order_group_rec.wip_entity_id,
581 p_INVENTORY_ITEM_ID => x_repair_order_group_rec.inventory_item_id,
582 p_UNIT_OF_MEASURE => x_repair_order_group_rec.unit_of_measure,
583 p_GROUP_QUANTITY => x_repair_order_group_rec.group_quantity,
584 p_REPAIR_ORDER_QUANTITY => x_repair_order_group_rec.repair_order_quantity,
585 p_RMA_QUANTITY => x_repair_order_group_rec.rma_quantity,
586 p_RECEIVED_QUANTITY => x_repair_order_group_rec.received_quantity,
587 p_APPROVED_QUANTITY => x_repair_order_group_rec.approved_quantity,
588 p_SUBMITTED_QUANTITY => x_repair_order_group_rec.submitted_quantity,
589 p_COMPLETED_QUANTITY => x_repair_order_group_rec.completed_quantity,
590 p_RELEASED_QUANTITY => x_repair_order_group_rec.released_quantity,
591 p_SHIPPED_QUANTITY => x_repair_order_group_rec.shipped_quantity,
592 p_CREATED_BY => FND_GLOBAL.USER_ID,
593 p_CREATION_DATE => SYSDATE,
594 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
595 p_LAST_UPDATE_DATE => SYSDATE,
596 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
597 p_CONTEXT => x_repair_order_group_rec.context,
598 p_ATTRIBUTE1 => x_repair_order_group_rec.attribute1,
599 p_ATTRIBUTE2 => x_repair_order_group_rec.attribute2,
600 p_ATTRIBUTE3 => x_repair_order_group_rec.attribute3,
601 p_ATTRIBUTE4 => x_repair_order_group_rec.attribute4,
602 p_ATTRIBUTE5 => x_repair_order_group_rec.attribute5,
603 p_ATTRIBUTE6 => x_repair_order_group_rec.attribute6,
604 p_ATTRIBUTE7 => x_repair_order_group_rec.attribute7,
605 p_ATTRIBUTE8 => x_repair_order_group_rec.attribute8,
606 p_ATTRIBUTE9 => x_repair_order_group_rec.attribute9,
607 p_ATTRIBUTE10 => x_repair_order_group_rec.attribute10,
608 p_ATTRIBUTE11 => x_repair_order_group_rec.attribute11,
609 p_ATTRIBUTE12 => x_repair_order_group_rec.attribute12,
610 p_ATTRIBUTE13 => x_repair_order_group_rec.attribute13,
611 p_ATTRIBUTE14 => x_repair_order_group_rec.attribute14,
612 p_ATTRIBUTE15 => x_repair_order_group_rec.attribute15,
613 p_OBJECT_VERSION_NUMBER => l_obj_ver_num + 1,
614 p_GROUP_TXN_STATUS => x_repair_order_group_rec.group_txn_status,
615 p_GROUP_APPROVAL_STATUS => x_repair_order_group_rec.group_approval_status,
616 p_REPAIR_MODE => x_repair_order_group_rec.repair_mode);
617
618 x_repair_order_group_rec.object_version_number := l_obj_ver_num + 1;
619
620
621 --------------------------------
622 -- Standard check for p_commit
623 --------------------------------
624 IF FND_API.to_Boolean(p_commit)
625 THEN
626 COMMIT;
627 END IF;
628
629 --------------------------------------------------------------------------
630 -- Standard call to get message count and if count is 1, get message info.
631 --------------------------------------------------------------------------
632 FND_MSG_PUB.Count_And_Get
633 (p_count => x_msg_count,
634 p_data => x_msg_data
635 );
636
637 EXCEPTION
638 WHEN FND_API.G_EXC_ERROR THEN
639 x_return_status := FND_API.G_RET_STS_ERROR ;
640 ROLLBACK TO update_group_repair_order;
641 FND_MSG_PUB.Count_And_Get
642 (p_count => x_msg_count,
643 p_data => x_msg_data );
644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
646 ROLLBACK TO update_group_repair_order;
647 FND_MSG_PUB.Count_And_Get
648 ( p_count => x_msg_count,
649 p_data => x_msg_data );
650 WHEN OTHERS THEN
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
652 ROLLBACK TO update_group_repair_order;
653 IF FND_MSG_PUB.Check_Msg_Level
654 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
655 THEN
656 FND_MSG_PUB.Add_Exc_Msg
657 (G_PKG_NAME ,
658 l_api_name );
659 END IF;
660 FND_MSG_PUB.Count_And_Get
661 (p_count => x_msg_count,
662 p_data => x_msg_data );
663
664 END UPDATE_REPAIR_GROUPS;
665
666
667 ----------------------------------------------------
668 -- procedure name: delete_repair_groups
669 -- description : procedure used to delete
670 -- group repair orders
671 --
672 ----------------------------------------------------
673
674 PROCEDURE DELETE_REPAIR_GROUPS
675 ( p_api_version IN NUMBER,
676 p_commit IN VARCHAR2,
677 p_init_msg_list IN VARCHAR2,
678 p_validation_level IN NUMBER,
679 p_repair_group_id IN NUMBER,
680 x_return_status OUT NOCOPY VARCHAR2,
681 x_msg_count OUT NOCOPY NUMBER,
682 x_msg_data OUT NOCOPY VARCHAR2
683 )
684 IS
685
686 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Repair_Groups';
687 l_api_version CONSTANT NUMBER := 1.0;
688 l_msg_count NUMBER;
689 l_msg_data VARCHAR2(100);
690 l_msg_index NUMBER;
691 l_dummy VARCHAR2(30);
692 l_count NUMBER;
693
694 BEGIN
695
696 ------------------------------------
697 -- Standard Start of API savepoint
698 ------------------------------------
699 SAVEPOINT delete_repair_order_group;
700
701 -------------------------------------------------
702 -- Standard call to check for call compatibility.
703 -------------------------------------------------
704 IF NOT FND_API.Compatible_API_Call (l_api_version,
705 p_api_version,
706 l_api_name ,
707 G_PKG_NAME)
708 THEN
709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
710 END IF;
711
712 --------------------------------------------------------------
713 -- Initialize message list if p_init_msg_list is set to TRUE.
714 --------------------------------------------------------------
715 IF FND_API.to_Boolean(p_init_msg_list)
716 THEN
717 FND_MSG_PUB.initialize;
718 END IF;
719
720 --------------------------------------------
721 -- Initialize API return status to success
722 --------------------------------------------
723 x_return_status := FND_API.G_RET_STS_SUCCESS;
724
725 ---------------------------------
726 -- Check the required parameter
727 ---------------------------------
728 IF (g_debug > 0 ) THEN
729 csd_gen_utility_pvt.ADD('Check reqd parameter: Repair Group Id ');
730 END IF;
731
732
733 CSD_PROCESS_UTIL.Check_Reqd_Param
734 ( p_param_value => p_repair_group_id,
735 p_param_name => 'REPAIR_GROUP_ID',
736 p_api_name => l_api_name);
737
738 ----------------------------------
739 -- Validate the Repair Group Id
740 ----------------------------------
741 IF (g_debug > 0 ) THEN
742 csd_gen_utility_pvt.ADD('Repair Group Id');
743 END IF;
744
745
746 IF NOT( CSD_PROCESS_UTIL.Validate_repair_group_id
747 ( p_repair_group_id => p_repair_group_id )) THEN
748 RAISE FND_API.G_EXC_ERROR;
749 END IF;
750
751
752 --------------------------------
753 -- check if there are any Ro
754 -- for the Group
755 --------------------------------
756 IF (g_debug > 0 ) THEN
757 csd_gen_utility_pvt.ADD('Validate if any RO have been created');
758 END IF;
759
760
761 l_count := 0;
762
763 BEGIN
764 SELECT
765 count(*)
766 INTO l_count
767 FROM csd_repairs
768 WHERE repair_group_id = p_repair_group_id;
769
770 EXCEPTION
771 WHEN NO_DATA_FOUND THEN
772 null;
773 WHEN OTHERS THEN
774 null;
775 END;
776
777 IF ( l_count > 0 ) then
778 FND_MESSAGE.SET_NAME('CSD','CSD_API_REPAIR_ORDER_EXISTS');
779 FND_MESSAGE.SET_TOKEN('REPAIR_GROUP_ID',p_repair_group_id);
780 FND_MSG_PUB.ADD;
781
782 RAISE FND_API.G_EXC_ERROR;
783 END IF;
784 -----------------------------
785 -- Calling the Delete api
786 -----------------------------
787 IF (g_debug > 0 ) THEN
788 csd_gen_utility_pvt.ADD('Calling : CSD_REPAIR_ORDER_GROUPS_PKG.Delete_Row');
789 END IF;
790
791
792 CSD_REPAIR_ORDER_GROUPS_PKG.Delete_Row
793 ( p_REPAIR_GROUP_ID => p_repair_group_id);
794
795 -------------------------------
796 -- Standard check for p_commit
797 -------------------------------
798 IF FND_API.to_Boolean(p_commit)
799 THEN
800 COMMIT;
801 END IF;
802
803 ---------------------------------------------------------------------------
804 -- Standard call to get message count and if count is 1, get message info.
805 ---------------------------------------------------------------------------
806 FND_MSG_PUB.Count_And_Get
807 (p_count => x_msg_count,
808 p_data => x_msg_data
809 );
810
811 EXCEPTION
812 WHEN FND_API.G_EXC_ERROR THEN
813 x_return_status := FND_API.G_RET_STS_ERROR ;
814 ROLLBACK TO delete_repair_order_group;
815 FND_MSG_PUB.Count_And_Get
816 (p_count => x_msg_count,
817 p_data => x_msg_data );
818 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
820 ROLLBACK TO delete_repair_order_group;
821 FND_MSG_PUB.Count_And_Get
822 ( p_count => x_msg_count,
823 p_data => x_msg_data );
824 WHEN OTHERS THEN
825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
826 ROLLBACK TO delete_repair_order_group;
827 IF FND_MSG_PUB.Check_Msg_Level
828 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
829 THEN
830 FND_MSG_PUB.Add_Exc_Msg
831 (G_PKG_NAME ,
832 l_api_name );
833 END IF;
834 FND_MSG_PUB.Count_And_Get
835 (p_count => x_msg_count,
836 p_data => x_msg_data );
837
838 END DELETE_REPAIR_GROUPS;
839
840
841 ----------------------------------------------------
842 -- procedure name: lock_repair_groups
843 -- description : procedure used to lock
844 -- group repair orders
845 --
846 ----------------------------------------------------
847
848 PROCEDURE LOCK_REPAIR_GROUPS
849 ( p_api_version IN NUMBER,
850 p_commit IN VARCHAR2,
851 p_init_msg_list IN VARCHAR2,
852 p_validation_level IN NUMBER,
853 p_repair_order_group_rec IN REPAIR_ORDER_GROUP_REC,
854 x_return_status OUT NOCOPY VARCHAR2,
855 x_msg_count OUT NOCOPY NUMBER,
856 x_msg_data OUT NOCOPY VARCHAR2
857 )
858 IS
859
860 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Repair_Groups';
861 l_api_version CONSTANT NUMBER := 1.0;
862 l_msg_count NUMBER;
863 l_msg_data VARCHAR2(100);
864 l_msg_index NUMBER;
865
866 BEGIN
867
868 -----------------------------------
869 -- Standard Start of API savepoint
870 -----------------------------------
871 SAVEPOINT lock_repair_order_group;
872
873 -------------------------------------------------
874 -- Standard call to check for call compatibility.
875 -------------------------------------------------
876 IF NOT FND_API.Compatible_API_Call (l_api_version,
877 p_api_version,
878 l_api_name ,
879 G_PKG_NAME)
880 THEN
881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882 END IF;
883
884 --------------------------------------------------------------
885 -- Initialize message list if p_init_msg_list is set to TRUE.
886 --------------------------------------------------------------
887 IF FND_API.to_Boolean(p_init_msg_list)
888 THEN
889 FND_MSG_PUB.initialize;
890 END IF;
891
892 -------------------------------------------
893 -- Initialize API return status to success
894 -------------------------------------------
895 x_return_status := FND_API.G_RET_STS_SUCCESS;
896
897 ----------------------------
898 -- Calling Lock Row
899 ----------------------------
900 IF (g_debug > 0 ) THEN
901 csd_gen_utility_pvt.ADD('Calling : CSD_REPAIR_ORDER_GROUPS_PKG.Lock_Row ');
902 END IF;
903
904
905 CSD_REPAIR_ORDER_GROUPS_PKG.Lock_Row(
906 p_REPAIR_GROUP_ID => p_repair_order_group_rec.repair_group_id,
907 p_OBJECT_VERSION_NUMBER => p_repair_order_group_rec.object_version_number);
908
909 --------------------------------
910 -- Standard check for p_commit
911 --------------------------------
912 IF FND_API.to_Boolean(p_commit)
913 THEN
914 COMMIT;
915 END IF;
916
917 --------------------------------------------------------------------------
918 -- Standard call to get message count and if count is 1, get message info.
919 --------------------------------------------------------------------------
920 FND_MSG_PUB.Count_And_Get
921 (p_count => x_msg_count,
922 p_data => x_msg_data
923 );
924
925 EXCEPTION
926 WHEN FND_API.G_EXC_ERROR THEN
927 x_return_status := FND_API.G_RET_STS_ERROR ;
928 ROLLBACK TO lock_repair_order_group;
929 FND_MSG_PUB.Count_And_Get
930 (p_count => x_msg_count,
931 p_data => x_msg_data );
932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
934 ROLLBACK TO lock_repair_order_group;
935 FND_MSG_PUB.Count_And_Get
936 ( p_count => x_msg_count,
937 p_data => x_msg_data );
938 WHEN OTHERS THEN
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
940 ROLLBACK TO lock_repair_order_group;
941 IF FND_MSG_PUB.Check_Msg_Level
942 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
943 THEN
944 FND_MSG_PUB.Add_Exc_Msg
945 (G_PKG_NAME ,
946 l_api_name );
947 END IF;
948 FND_MSG_PUB.Count_And_Get
949 (p_count => x_msg_count,
950 p_data => x_msg_data );
951
952 END LOCK_REPAIR_GROUPS;
953
954 -----------------------------------------------------------
955 -- procedure name: apply_to_group
956 -- description : procedure used to update promise_date
957 -- approval_req_flag,resource for all the
958 -- repair orders of the group.
959 -----------------------------------------------------------
960
961 PROCEDURE APPLY_TO_GROUP
962 ( p_api_version IN NUMBER,
963 p_commit IN VARCHAR2,
964 p_init_msg_list IN VARCHAR2,
965 p_validation_level IN NUMBER,
966 p_repair_group_id IN NUMBER,
967 p_promise_date IN DATE,
968 p_resource_id IN NUMBER,
969 p_approval_required_flag IN VARCHAR2,
970 x_object_version_number OUT NOCOPY NUMBER,
971 x_return_status OUT NOCOPY VARCHAR2,
972 x_msg_count OUT NOCOPY NUMBER,
973 x_msg_data OUT NOCOPY VARCHAR2
974 )
975
976 IS
977
978 l_api_name CONSTANT VARCHAR2(30) := 'Apply_to_Group';
979 l_api_version CONSTANT NUMBER := 1.0;
980 l_msg_count NUMBER;
981 l_msg_data VARCHAR2(100);
982 l_msg_index NUMBER;
983 l_dummy VARCHAR2(10);
984 l_ro_rec csd_repairs_pub.repln_rec_type := csd_process_util.repair_order_rec;
985 l_object_version_number NUMBER;
986 l_group_object_version_number NUMBER;
987 l_group_ro_rec csd_repair_groups_pvt.repair_order_group_rec;
988 l_return_status Varchar2(10);
989 l_group_quantity NUMBER;
990 l_count NUMBER;
991 l_tot_approved NUMBER;
992 l_tot_rejected NUMBER;
993 l_tot_no_approval NUMBER;
994
995
996 Cursor Repair_Orders(p_repair_order_group in Number)
997 IS
998 SELECT repair_line_id,repair_number from csd_repairs
999 WHERE repair_group_id = p_repair_order_group
1000 FOR UPDATE NOWAIT;
1001
1002 BEGIN
1003
1004 -----------------------------------
1005 -- Standard Start of API savepoint
1006 -----------------------------------
1007 SAVEPOINT apply_to_group;
1008
1009 -------------------------------------------------
1010 -- Standard call to check for call compatibility.
1011 -------------------------------------------------
1012 IF NOT FND_API.Compatible_API_Call (l_api_version,
1013 p_api_version,
1014 l_api_name ,
1015 G_PKG_NAME)
1016 THEN
1017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1018 END IF;
1019
1020 --------------------------------------------------------------
1021 -- Initialize message list if p_init_msg_list is set to TRUE.
1022 --------------------------------------------------------------
1023 IF FND_API.to_Boolean(p_init_msg_list)
1024 THEN
1025 FND_MSG_PUB.initialize;
1026 END IF;
1027
1028 -------------------------------------------
1029 -- Initialize API return status to success
1030 -------------------------------------------
1031 x_return_status := FND_API.G_RET_STS_SUCCESS;
1032
1033 -----------------
1034 -- API Starts
1035 -----------------
1036 if (g_debug > 0) then
1037
1038 csd_gen_utility_pvt.dump_api_info
1039 ( p_pkg_name => G_PKG_NAME,
1040 p_api_name => l_api_name );
1041 end if;
1042 ------------------------------------------
1043 -- Dump the in parameters in the log file
1044 ------------------------------------------
1045 IF (g_debug > 5 ) THEN
1046 csd_gen_utility_pvt.ADD('Repair Group Id '||to_char(p_repair_group_id));
1047
1048 csd_gen_utility_pvt.ADD('Promise Date '||p_promise_date);
1049
1050 csd_gen_utility_pvt.ADD('Resource Id '||p_resource_id);
1051
1052 csd_gen_utility_pvt.ADD('Approval Required Flag '||p_approval_required_flag);
1053
1054 END IF;
1055
1056 ---------------------------------
1057 -- Check the required parameter
1058 ---------------------------------
1059 IF (g_debug > 0 ) THEN
1060 csd_gen_utility_pvt.ADD('Check reqd parameter : Repair Group Id');
1061 END IF;
1062
1063
1064 CSD_PROCESS_UTIL.Check_Reqd_Param
1065 ( p_param_value => p_repair_group_id,
1066 p_param_name => 'REPAIR_GROUP_ID',
1067 p_api_name => l_api_name);
1068
1069 ---------------------------------
1070 -- Validate Repair Group Id
1071 ---------------------------------
1072 IF (g_debug > 0 ) THEN
1073 csd_gen_utility_pvt.ADD('Validate Repair Group Id');
1074 END IF;
1075
1076
1077 IF NOT( CSD_PROCESS_UTIL.Validate_repair_group_id
1078 ( p_repair_group_id => p_repair_group_id )) THEN
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081
1082 -----------------------------
1083 -- Validate the Resource Id
1084 -----------------------------
1085 IF (g_debug > 0 ) THEN
1086 csd_gen_utility_pvt.ADD('Validate Resource Id');
1087 END IF;
1088
1089
1090 IF(nvl(p_resource_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)THEN
1091
1092 BEGIN
1093 SELECT
1094 'x'
1095 INTO l_dummy
1096 FROM jtf_rs_resource_extns
1097 WHERE resource_id = p_resource_id;
1098
1099 EXCEPTION
1100 WHEN NO_DATA_FOUND THEN
1101 FND_MESSAGE.SET_NAME('CSD','CSD_API_INVALID_RESOURCE');
1102 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',p_resource_id);
1103 FND_MSG_PUB.ADD;
1104
1105 RAISE FND_API.G_EXC_ERROR;
1106 WHEN OTHERS THEN
1107 null;
1108 END;
1109 END IF;
1110 -------------------------------
1111 -- Update the Repair Orders
1112 -------------------------------
1113 IF (g_debug > 0 ) THEN
1114 csd_gen_utility_pvt.ADD('Update the Repair Orders');
1115 END IF;
1116
1117
1118 l_ro_rec.promise_date := nvl(p_promise_date,FND_API.G_MISS_DATE);
1119 l_ro_rec.resource_id := nvl(p_resource_id,FND_API.G_MISS_NUM);
1120 l_ro_rec.approval_required_flag := nvl(p_approval_required_flag,FND_API.G_MISS_CHAR);
1121
1122 FOR RO_record IN Repair_Orders(p_repair_group_id)
1123 LOOP
1124
1125 Begin
1126
1127 l_object_version_number := 0;
1128
1129 select object_version_number
1130 into l_object_version_number
1131 from csd_repairs
1132 where repair_line_id = RO_record.repair_line_id;
1133
1134 Exception
1135 when others then
1136 null;
1137 End;
1138
1139 l_ro_rec.object_version_number := l_object_version_number;
1140
1141 CSD_REPAIRS_PVT.Update_Repair_Order
1142 (P_Api_Version_Number => 1.0,
1143 P_Init_Msg_List => 'T',
1144 P_Commit => 'F',
1145 p_validation_level => 0,
1146 p_REPAIR_LINE_ID => RO_record.repair_line_id,
1147 P_REPLN_Rec => l_ro_rec,
1148 X_Return_Status => l_return_status,
1149 X_Msg_Count => l_msg_count,
1150 X_Msg_Data => l_msg_data
1151 );
1152
1153 If ( l_return_status <> 'S') then
1154 FND_MESSAGE.SET_NAME('CSD','CSD_API_RO_ORDER_UPD_FAIL');
1155 FND_MSG_PUB.ADD;
1156
1157 RAISE FND_API.G_EXC_ERROR;
1158
1159 End if;
1160 END LOOP;
1161
1162
1163 ---------------------------------
1164 -- Update the Group Repair Order
1165 ---------------------------------
1166 IF (p_repair_group_id IS NOT NULL) THEN
1167
1168 l_group_ro_rec.repair_group_id := p_repair_group_id;
1169
1170 Begin
1171
1172 l_group_object_version_number := 0;
1173
1174 select object_version_number,
1175 group_quantity
1176 into l_group_object_version_number,
1177 l_group_quantity
1178 from csd_repair_order_groups_v
1179 where repair_group_id = p_repair_group_id;
1180
1181 Exception
1182 when others then
1183 null;
1184 End;
1185
1186 l_group_ro_rec.object_version_number := l_group_object_version_number;
1187
1188
1189 l_count := 0;
1190 l_tot_approved := 0;
1191 l_tot_rejected := 0;
1192 l_tot_no_approval := 0;
1193
1194 BEGIN
1195 SELECT COUNT(*)
1196 INTO l_tot_approved
1197 FROM csd_repairs
1198 WHERE repair_group_id = p_repair_group_id
1199 AND approval_status = 'A'
1200 AND approval_required_flag = 'Y';
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 IF (g_debug > 0 ) THEN
1204 csd_gen_utility_pvt.ADD(' OTHERS l_tot_approved ='||l_tot_approved);
1205 END IF;
1206
1207 END;
1208
1209
1210 BEGIN
1211 SELECT COUNT(*)
1212 INTO l_tot_rejected
1213 FROM csd_repairs
1214 WHERE repair_group_id = p_repair_group_id
1215 AND approval_status = 'R'
1216 AND approval_required_flag = 'Y';
1217 EXCEPTION
1218 WHEN OTHERS THEN
1219 IF (g_debug > 0 ) THEN
1220 csd_gen_utility_pvt.ADD(' OTHERS l_tot_rejected ='||l_tot_rejected);
1221 END IF;
1222
1223 END;
1224
1225 BEGIN
1226 SELECT COUNT(*)
1227 INTO l_tot_no_approval
1228 FROM csd_repairs
1229 WHERE repair_group_id = p_repair_group_id
1230 AND approval_required_flag = 'N';
1231 EXCEPTION
1232 WHEN OTHERS THEN
1233 IF (g_debug > 0 ) THEN
1234 csd_gen_utility_pvt.ADD(' OTHERS l_tot_no_approval ='||l_tot_no_approval);
1235 END IF;
1236
1237 END;
1238
1239 IF (g_debug > 0 ) THEN
1240 csd_gen_utility_pvt.ADD('l_tot_approved ='||l_tot_approved);
1241 csd_gen_utility_pvt.ADD('l_tot_rejected ='||l_tot_rejected);
1242 csd_gen_utility_pvt.ADD('l_tot_no_approval ='||l_tot_no_approval);
1243 END IF;
1244
1245
1246 --------------------------------------------------------------------------------------------
1247 -- Total of approved/rejected repairs with approval required = Y and approval required = N
1248 -- Assumption no approval are not allowed to be rejected
1249 --------------------------------------------------------------------------------------------
1250 l_count := NVL(l_tot_approved,0) + NVL(l_tot_rejected,0) + NVL(l_tot_no_approval,0);
1251
1252 -----------------------------------------------------
1253 -- check if all group qty have been approved/rejected
1254 -----------------------------------------------------
1255 IF (NVL(l_group_quantity,0) = NVL(l_tot_no_approval,0)) THEN
1256 IF (g_debug > 0 ) THEN
1257 csd_gen_utility_pvt.ADD('EST_NOT_REQD ');
1258 END IF;
1259
1260 l_group_ro_rec.group_approval_status := 'EST_NOT_REQD';
1261 l_group_ro_rec.approved_quantity := NVL(l_tot_no_approval,0) ;
1262 ELSIF (l_group_quantity > l_count and l_count <> 0 ) THEN
1263 IF (g_debug > 0 ) THEN
1264 csd_gen_utility_pvt.ADD('PARTIAL_APPRD ');
1265 END IF;
1266
1267 l_group_ro_rec.group_approval_status := 'PARTIAL_APPRD';
1268 l_group_ro_rec.approved_quantity := NVL(l_tot_approved,0) + NVL(l_tot_no_approval,0);
1269 ELSIF (l_group_quantity = NVL(l_tot_approved,0)+ NVL(l_tot_no_approval,0)) THEN
1270 IF (g_debug > 0 ) THEN
1271 csd_gen_utility_pvt.ADD('APPROVED ');
1272 END IF;
1273
1274 l_group_ro_rec.group_approval_status := 'APPROVED';
1275 l_group_ro_rec.approved_quantity := NVL(l_tot_approved,0)+ NVL(l_tot_no_approval,0);
1276 ELSIF (l_group_quantity = NVL(l_tot_rejected,0)) THEN
1277 IF (g_debug > 0 ) THEN
1278 csd_gen_utility_pvt.ADD('REJECTED ');
1279 END IF;
1280
1281 l_group_ro_rec.group_approval_status := 'REJECTED';
1282 l_group_ro_rec.approved_quantity := 0 ;
1283 ELSE
1284 l_group_ro_rec.group_approval_status := 'EST_REQD';
1285 l_group_ro_rec.approved_quantity := 0 ;
1286 END IF;
1287
1288 IF (g_debug > 0 ) THEN
1289 csd_gen_utility_pvt.ADD('CSD_REPAIR_ESTIMATE_PVT.UPDATE_RO_GROUP_ESTIMATE Update Group RO call');
1290 END IF;
1291
1292
1293 -----------------
1294 -- call the api
1295 -----------------
1296
1297 CSD_REPAIR_GROUPS_PVT.UPDATE_REPAIR_GROUPS
1298 ( p_api_version => 1.0,
1299 p_commit => 'F',
1300 p_init_msg_list => 'T',
1301 p_validation_level => fnd_api.g_valid_level_full,
1302 x_repair_order_group_rec => l_group_ro_rec,
1303 x_return_status => l_return_status,
1304 x_msg_count => l_msg_count,
1305 x_msg_data => l_msg_data );
1306
1307 IF (g_debug > 0 ) THEN
1308 csd_gen_utility_pvt.ADD('CSD_REPAIR_ESTIMATE_PVT.UPDATE_RO_GROUP_ESTIMATE UPDATE_REPAIR_GROUPS :'||x_return_status);
1309 END IF;
1310
1311
1312 IF l_return_status <> 'S' THEN
1313 x_return_status := FND_API.G_RET_STS_ERROR ;
1314 FND_MESSAGE.SET_NAME('CSD','CSD_API_RO_GROUP_EST_FAIL');
1315 FND_MSG_PUB.ADD;
1316 ELSIF l_return_status = 'S' THEN
1317 x_object_version_number := l_group_ro_rec.object_version_number;
1318 END IF;
1319
1320 END IF;
1321
1322 --------------------------------
1323 -- Standard check for p_commit
1324 --------------------------------
1325 IF FND_API.to_Boolean(p_commit)
1326 THEN
1327 COMMIT;
1328 END IF;
1329
1330 --------------------------------------------------------------------------
1331 -- Standard call to get message count and if count is 1, get message info.
1332 --------------------------------------------------------------------------
1333 FND_MSG_PUB.Count_And_Get
1334 (p_count => x_msg_count,
1335 p_data => x_msg_data
1336 );
1337
1338 EXCEPTION
1339 WHEN FND_API.G_EXC_ERROR THEN
1340 x_return_status := FND_API.G_RET_STS_ERROR ;
1341 ROLLBACK TO apply_to_group;
1342 FND_MSG_PUB.Count_And_Get
1343 (p_count => x_msg_count,
1344 p_data => x_msg_data );
1345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1347 ROLLBACK TO apply_to_group;
1348 FND_MSG_PUB.Count_And_Get
1349 ( p_count => x_msg_count,
1350 p_data => x_msg_data );
1351 WHEN OTHERS THEN
1352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1353 ROLLBACK TO apply_to_group;
1354 IF FND_MSG_PUB.Check_Msg_Level
1355 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1356 THEN
1357 FND_MSG_PUB.Add_Exc_Msg
1358 (G_PKG_NAME ,
1359 l_api_name );
1360 END IF;
1361 FND_MSG_PUB.Count_And_Get
1362 (p_count => x_msg_count,
1363 p_data => x_msg_data );
1364
1365 END APPLY_TO_GROUP;
1366
1367 END CSD_REPAIR_GROUPS_PVT;