[Home] [Help]
PACKAGE BODY: APPS.CSD_DEPOT_UPDATE_PVT
Source
1 package body CSD_DEPOT_UPDATE_PVT as
2 /* $Header: csddrupb.pls 115.10 2002/11/12 21:27:49 sangigup noship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables
6 -- ---------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_DEPOT_UPDATE_PVT';
9 G_FILE_NAME CONSTANT VARCHAR2(30) := 'csddrupb.pls';
10 g_debug number := csd_gen_utility_pvt.g_debug_level;
11 -----------------------------------
12 -- Convert to primary uom
13 -----------------------------------
14 procedure convert_to_primary_uom
15 (p_item_id in number,
16 p_organization_id in number,
17 p_from_uom in varchar2,
18 p_from_quantity in number,
19 p_result_quantity OUT NOCOPY number)
20 is
21
22 v_primary_uom_code varchar2(30);
23 p_from_uom_code varchar2(3);
24
25 Begin
26
27 Begin
28 select uom_code
29 into p_from_uom_code
30 from mtl_units_of_measure
31 where unit_of_measure = p_from_uom;
32 EXCEPTION
33 WHEN NO_DATA_FOUND THEN
34 IF (g_debug > 0 ) THEN
35 csd_gen_utility_pvt.add('no_data_found error for unit_of_measure ='||p_from_uom);
36 END IF;
37
38 WHEN OTHERS THEN
39 IF (g_debug > 0 ) THEN
40 csd_gen_utility_pvt.add('More than one row found for unit_of_measure ='||p_from_uom);
41 END IF;
42
43 End;
44
45 Begin
46 select primary_uom_code
47 into v_primary_uom_code
48 from mtl_system_items
49 where organization_id = p_organization_id
50 and inventory_item_id = p_item_id;
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 IF (g_debug > 0 ) THEN
54 csd_gen_utility_pvt.add('no_data_found error(primary UOM) for inventory_item_id ='||TO_CHAR(p_item_id));
55 END IF;
56
57 WHEN OTHERS THEN
58 IF (g_debug > 0 ) THEN
59 csd_gen_utility_pvt.add('More than one row found(Primary UOM) for inventory_item_id ='||TO_CHAR(p_item_id));
60 END IF;
61
62 End;
63
64 BEGIN
65 p_result_quantity :=inv_convert.inv_um_convert(
66 p_item_id ,2,
67 p_from_quantity,p_from_uom_code,v_primary_uom_code,null,null);
68 EXCEPTION
69 WHEN OTHERS THEN
70 IF (g_debug > 0 ) THEN
71 csd_gen_utility_pvt.add('inv_convert returned with error message');
72 END IF;
73
74 END;
75 End convert_to_primary_uom;
76
77 -- ---------------------------------------------------------
78 -- procedure name: Group_wip_update --
79 -- description : procedure that updates depot --
80 -- with qty once wip job is complete --
81 -- --
82 -- ---------------------------------------------------------
83
84 PROCEDURE group_wip_update
85 ( p_api_version IN NUMBER,
86 p_commit IN VARCHAR2 := fnd_api.g_false,
87 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
88 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
89 p_incident_id IN NUMBER,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2
93 )
94 IS
95 l_api_name CONSTANT VARCHAR2(30) := 'group_wip_update';
96 l_api_version CONSTANT NUMBER := 1.0;
97 l_msg_count NUMBER;
98 l_msg_data VARCHAR2(100);
99 l_msg_index NUMBER;
100 l_validate_flag BOOLEAN;
101 v_total_rec NUMBER;
102 p_rep_hist_id NUMBER;
103 v_remaining_qty NUMBER;
104 v_transaction_quantity NUMBER;
105 v_old_wip_entity_id NUMBER;
106 v_wip_entity_name VARCHAR2(100);
107 p_wip_entity_id NUMBER;
108 p_quantity_completed NUMBER;
109 p_completion_subinventory VARCHAR2(30);
110 p_date_completed DATE;
111 p_organization_id NUMBER;
112 p_routing_reference_id NUMBER;
113 p_last_updated_by NUMBER;
114 l_return_status VARCHAR2(1);
115 v_new_completion_quantity NUMBER;
116
117 v_quantity_completed number;
118 p_old_complete number;
119 v_wip_entity_id number;
120
121 -- travi new 2501113
122 v_weid number;
123 CURSOR get_grp_id (p_inc_id in number) IS
124 SELECT repair_group_id
125 FROM csd_repair_order_groups
126 WHERE incident_id = p_inc_id;
127
128 CURSOR get_xref_id (p_rep_grp_id in number) IS
129 SELECT x.repair_job_xref_id, x.group_id, x.object_version_number
130 FROM csd_repair_job_xref x
131 WHERE x.repair_line_id in ( select r.repair_line_id
132 from csd_repairs r
133 where r.repair_group_id = p_rep_grp_id)
134 AND x.wip_entity_id = x.group_id;
135 -- end travi new 2501113
136
137 -- Cursor to get wip entity id
138 CURSOR get_wip_entity (p_inc_id in number) IS
139 SELECT distinct crog.wip_entity_id
140 FROM csd_repair_order_groups crog,
141 wip_discrete_jobs wdj
142 WHERE crog.wip_entity_id = wdj.wip_entity_id
143 AND wdj.status_type in ( 4,12,5)
144 AND crog.incident_id = p_inc_id;
145
146 -------------------------
147 -- WIP Job Statuses
148 -- Complete Status : 4
149 -- Closed : 12
150 -- Complete No Charge : 5
151 -------------------------
152
153 -- Cursor to get repair group id
154 CURSOR get_rep_group (p_wip_ent_id in number) IS
155 SELECT crog.repair_group_id
156 FROM csd_repair_order_groups crog
157 WHERE crog.wip_entity_id = p_wip_ent_id;
158
159 -- Cursor to get repair line id
160 CURSOR get_repair_lines(p_rep_group_id in number,
161 p_wip_ent_id in number ) IS
162 SELECT
163 crj.repair_job_xref_id,
164 crj.wip_entity_id,
165 crj.repair_line_id,
166 csr.repair_number,
167 crj.quantity_completed,
168 crj.quantity,
169 csr.promise_date
170 FROM csd_repair_job_xref crj,
171 csd_repairs csr
172 WHERE repair_group_id = p_rep_group_id
173 AND csr.repair_line_id = crj.repair_line_id
174 AND nvl(crj.quantity_completed,0) < crj.quantity
175 AND crj.wip_entity_id = p_wip_ent_id;
176
177 BEGIN
178
179 -- Standard Start of API savepoint
180 SAVEPOINT group_wip_update;
181
182 -- Standard call to check for call compatibility.
183 IF NOT FND_API.Compatible_API_Call (l_api_version,
184 p_api_version,
185 l_api_name ,
186 G_PKG_NAME )
187 THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191 -- Initialize message list if p_init_msg_list is set to TRUE.
192 IF FND_API.to_Boolean( p_init_msg_list ) THEN
193 FND_MSG_PUB.initialize;
194 END IF;
195
196 -- Initialize API return status to success
197 x_return_status := FND_API.G_RET_STS_SUCCESS;
198
199 -- Api body starts
200 if (g_debug > 0) then
201 csd_gen_utility_pvt.dump_api_info
202 ( p_pkg_name => G_PKG_NAME,
203 p_api_name => l_api_name );
204 end if;
205 -- Validate the incident_id
206
207 IF (g_debug > 0 ) THEN
208 csd_gen_utility_pvt.add('Incident Id ='||p_incident_id);
209 END IF;
210
211 l_validate_flag := csd_process_util.validate_incident_id(p_incident_id );
212
213 IF NOT(l_validate_flag) then
214 IF (g_debug > 0 ) THEN
215 csd_gen_utility_pvt.add('The Incident Id is invalid ');
216 END IF;
217
218 Raise FND_API.G_EXC_ERROR;
219 END IF;
220
221 -- travi new code 2501113 update with wip_entity_id
222 -- BEGIN LOOPS
223 -- Get the groups for wip_entity_id update
224 FOR C5 in get_grp_id (p_incident_id )
225 LOOP
226
227 -- Get the xref for wip_entity_id update
228 FOR C6 in get_xref_id (C5.repair_group_id)
229 LOOP
230 IF (g_debug > 0 ) THEN
231 csd_gen_utility_pvt.ADD('In C6 get_xref_id');
232 END IF;
233
234
235 BEGIN
236 SELECT wip_entity_id
237 INTO v_weid
238 FROM wip_entities
239 WHERE wip_entity_name = 'CSD'||C6.group_id;
240 Exception
241 When no_data_found then
242 IF (g_debug > 0 ) THEN
243 csd_gen_utility_pvt.add('Invalid WIP_ENTITY_NAME : CSD'||C6.group_id);
244 END IF;
245
246 when others then
247 IF (g_debug > 0 ) THEN
248 csd_gen_utility_pvt.add('Others exception WIP_ENTITY_NAME : CSD'||C6.group_id);
249 END IF;
250
251 End;
252
253 IF (g_debug > 0 ) THEN
254 csd_gen_utility_pvt.ADD('Updating the xref table repair_job_xref_id = '|| C6.repair_job_xref_id);
255 END IF;
256
257
258 if( v_weid is not null) then
259 Begin
260 IF (g_debug > 0 ) THEN
261 csd_gen_utility_pvt.ADD('In C6 updating xref : '||C6.repair_job_xref_id||' for wip_entity_id');
262 END IF;
263
264 -- updating xref for wip_entity_id
265 UPDATE csd_repair_job_xref
266 SET wip_entity_id = v_weid
267 WHERE repair_job_xref_id = C6.repair_job_xref_id
268 AND group_id = C6.group_id
269 AND object_version_number = C6.object_version_number;
270 Exception
271 when others then
272 IF (g_debug > 0 ) THEN
273 csd_gen_utility_pvt.add('Others exception repair_job_xref_id : '||C6.repair_job_xref_id);
274 END IF;
275
276 End;
277 end if;
278
279 END LOOP; -- END LOOP FOR C6
280
281 if( v_weid is not null) then
282 Begin
283 IF (g_debug > 0 ) THEN
284 csd_gen_utility_pvt.ADD('In C5 updating group : '||C5.repair_group_id||' for wip_entity_id');
285 END IF;
286
287 -- update group level csd_repair_order_groups
288 UPDATE csd_repair_order_groups
289 SET wip_entity_id = v_weid
290 WHERE repair_group_id = C5.repair_group_id;
291 Exception
292 when others then
293 IF (g_debug > 0 ) THEN
294 csd_gen_utility_pvt.add('Others exception repair_group_id : '||C5.repair_group_id);
295 END IF;
296
297 End;
298 end if;
299
300
301 END LOOP; -- END LOOP FOR C5
302 -- end travi code
303
304 -- BEGIN LOOPS
305 FOR C1 in get_wip_entity(p_incident_id )
306 LOOP
307
308 -- Get the actual quantity completed
309 BEGIN
310 -- Get the qty completed from wip_discrete_jobs based on the wip_entity_id
311 -- Only if the wip job that is completed will be processed
312 -- and the partial completed qty will not be processed
313
314 SELECT wip_entity_id,
315 quantity_completed,
316 completion_subinventory,
317 date_completed,
318 organization_id,
319 routing_reference_id,
320 last_updated_by
321 INTO v_wip_entity_id,
322 v_quantity_completed,
323 p_completion_subinventory,
324 p_date_completed,
325 p_organization_id,
326 p_routing_reference_id,
327 p_last_updated_by
328 FROM wip_discrete_jobs
329 WHERE wip_entity_id = C1.wip_entity_id
330 AND status_type in ( 4,12,5);
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 IF (g_debug > 0 ) THEN
334 csd_gen_utility_pvt.add('No WIP Job found for the wip_entity_id '||TO_CHAR(p_WIP_ENTITY_ID));
335 END IF;
336
337 v_quantity_completed := 0;
338 -- Raise FND_API.G_EXC_ERROR;
339 WHEN OTHERS THEN
340 IF (g_debug > 0 ) THEN
341 csd_gen_utility_pvt.add('when other exception at - get_wip_job_completed_qty_gr');
342 END IF;
343
344 Raise FND_API.G_EXC_ERROR;
345 END;
346
347 -- Get the original qty completed from csd_repair_job_xref based on the wip_entity_id
348 SELECT nvl(sum(quantity_completed),0)
349 INTO p_old_complete
350 FROM csd_repair_job_xref
351 WHERE wip_entity_id = C1.wip_entity_id;
352
353 -- Get the actual qty completed, (qty completed - old qty completed)
354 p_quantity_completed := nvl(v_quantity_completed,0) - nvl(p_old_complete,0);
355
356 IF p_quantity_completed <> 0 THEN
357
358 -- Getting all the repair_group_id for the wip_entity_id
359 FOR C2 in get_rep_group(C1.wip_entity_id)
360 LOOP
361
362 -- Getting all the repair_lines for the repair_group_id
363 FOR C3 in get_repair_lines(C2.repair_group_id,C1.wip_entity_id)
364 LOOP
365
366 -- Update csd_repair_job_xref
367 update csd_repair_job_xref
368 set quantity_completed = quantity
369 where repair_line_id = C3.repair_line_id;
370
371 IF (g_debug > 0 ) THEN
372 csd_gen_utility_pvt.add('Updated qty completed in csd_repair_job_xref for :'||C1.wip_entity_id);
373 END IF;
374
375
376 -- Update csd_repairs
377 update csd_repairs
378 set ro_txn_status = 'WIP_COMPLETED'
379 where repair_line_id = C3.repair_line_id;
380
381 IF (g_debug > 0 ) THEN
382 csd_gen_utility_pvt.add('Updated txn status in csd_repairs :'||C3.repair_line_id);
383 END IF;
384
385
386 v_total_rec := v_total_rec + 1;
387
388 -- Call API to Validate and Write to history
389 IF (g_debug > 0 ) THEN
390 csd_gen_utility_pvt.add('Calling CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write');
391 END IF;
392
393 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write (
394 P_Api_Version_Number => 1.0,
395 P_Init_Msg_List => 'T',
396 P_Commit => 'F',
397 p_validation_level => null,
398 p_action_code => 0 ,
399 px_REPAIR_HISTORY_ID => p_rep_hist_id,
400 p_OBJECT_VERSION_NUMBER => null,
401 p_REQUEST_ID => null,
402 p_PROGRAM_ID => null,
403 p_PROGRAM_APPLICATION_ID => null,
404 p_PROGRAM_UPDATE_DATE => null,
405 p_CREATED_BY => FND_GLOBAL.USER_ID,
406 p_CREATION_DATE => sysdate,
407 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
408 p_LAST_UPDATE_DATE => sysdate,
409 p_REPAIR_LINE_ID => C3.repair_line_id,
410 p_EVENT_CODE => 'JC',
411 p_EVENT_DATE => nvl(p_date_completed,sysdate),
412 p_QUANTITY => v_transaction_quantity,
413 p_PARAMN1 => p_organization_id,
414 p_PARAMN2 => p_routing_reference_id,
415 p_PARAMN3 => null,
416 p_PARAMN4 => C3.wip_entity_id,
417 p_PARAMN5 => null,
418 p_PARAMN6 => null,
419 p_PARAMN7 => null,
420 p_PARAMN8 => null,
421 p_PARAMN9 => null,
422 p_PARAMN10 => null,
423 p_PARAMC1 => p_completion_subinventory,
424 p_PARAMC2 => v_wip_entity_name,
425 p_PARAMC3 => null,
426 p_PARAMC4 => null,
427 p_PARAMC5 => null,
428 p_PARAMC6 => null,
429 p_PARAMC7 => null,
430 p_PARAMC8 => null,
431 p_PARAMC9 => null,
432 p_PARAMC10 => null,
433 p_PARAMD1 => p_date_completed,
434 p_PARAMD2 => null,
435 p_PARAMD3 => null,
436 p_PARAMD4 => null,
437 p_PARAMD5 => null,
438 p_PARAMD6 => null,
439 p_PARAMD7 => null,
440 p_PARAMD8 => null,
441 p_PARAMD9 => null,
442 p_PARAMD10 => null,
443 p_ATTRIBUTE_CATEGORY => null,
444 p_ATTRIBUTE1 => null,
445 p_ATTRIBUTE2 => null,
446 p_ATTRIBUTE3 => null,
447 p_ATTRIBUTE4 => null,
448 p_ATTRIBUTE5 => null,
449 p_ATTRIBUTE6 => null,
450 p_ATTRIBUTE7 => null,
451 p_ATTRIBUTE8 => null,
452 p_ATTRIBUTE9 => null,
453 p_ATTRIBUTE10 => null,
454 p_ATTRIBUTE11 => null,
455 p_ATTRIBUTE12 => null,
456 p_ATTRIBUTE13 => null,
457 p_ATTRIBUTE14 => null,
458 p_ATTRIBUTE15 => null,
459 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
460 X_Return_Status => l_return_status ,
461 X_Msg_Count => l_msg_count,
462 X_Msg_Data => l_msg_data );
463
464 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
465 IF (g_debug > 0 ) THEN
466 csd_gen_utility_pvt.ADD('validate_and_write failed ');
467 END IF;
468
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471
472 END LOOP; -- END LOOP FOR C3
473
474 -- update group level csd_repair_order_groups
475 UPDATE csd_repair_order_groups
476 SET group_txn_status = 'WIP_COMPLETED',
477 completed_quantity = submitted_quantity
478 WHERE repair_group_id = C2.repair_group_id;
479
480 END LOOP; -- END LOOP FOR C2
481 IF (g_debug > 0 ) THEN
482 csd_gen_utility_pvt.add('Successfully completed Depot Repair WIP Job Update');
483 END IF;
484
485
486 END IF; -- END IF for p_quantity_completed <> 0 T
487
488 END LOOP; -- END LOOP FOR C1
489
490 -- Standard check of p_commit.
491 IF FND_API.To_Boolean( p_commit ) THEN
492 COMMIT WORK;
493 END IF;
494
495 -- Standard call to get message count and IF count is get message info.
496 FND_MSG_PUB.Count_And_Get
497 (p_count => x_msg_count,
498 p_data => x_msg_data );
499
500 EXCEPTION
501 WHEN FND_API.G_EXC_ERROR THEN
502 ROLLBACK TO Group_Wip_update;
503 x_return_status := FND_API.G_RET_STS_ERROR ;
504 FND_MSG_PUB.Count_And_Get
505 (p_count => x_msg_count,
506 p_data => x_msg_data );
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
509 ROLLBACK TO Group_Wip_update;
510 FND_MSG_PUB.Count_And_Get
511 ( p_count => x_msg_count,
512 p_data => x_msg_data );
513 WHEN OTHERS THEN
514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
515 ROLLBACK TO Group_Wip_update ;
516 IF FND_MSG_PUB.Check_Msg_Level
517 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
518 THEN
519 FND_MSG_PUB.Add_Exc_Msg
520 (G_PKG_NAME ,
521 l_api_name );
522 END IF;
523 FND_MSG_PUB.Count_And_Get
524 (p_count => x_msg_count,
525 p_data => x_msg_data );
526
527 END Group_Wip_update;
528
529 /*-------------------------------------------------------*/
530 /* procedure name: Pre_process_update */
531 /* description : procedure that updates the depot table*/
532 /* once the pre-process is completed */
533 /*-------------------------------------------------------*/
534
535 procedure Pre_process_update
536 ( p_api_version IN NUMBER,
537 p_commit IN VARCHAR2 := fnd_api.g_false,
538 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
539 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
540 p_incident_id IN number,
541 x_return_status OUT NOCOPY VARCHAR2,
542 x_msg_count OUT NOCOPY NUMBER,
543 x_msg_data OUT NOCOPY VARCHAR2
544 ) IS
545
546 l_api_name CONSTANT VARCHAR2(30) := 'Pre_process_update';
547 l_api_version CONSTANT NUMBER := 1.0;
548 l_msg_count NUMBER;
549 l_msg_data VARCHAR2(100);
550 l_msg_index NUMBER;
551 l_validate_flag Boolean;
552 x_update_count Number;
553
554 CURSOR get_rep_group (p_inc_id in number) IS
555 Select crog.repair_group_id,
556 crt.repair_type_ref,
557 crog.group_txn_status,
558 crog.repair_order_quantity,
559 crog.received_quantity,
560 crog.shipped_quantity
561 from csd_repair_order_groups crog,
562 csd_repair_types_vl crt
563 where crog.repair_type_id = crt.repair_type_id
564 and crog.incident_id = p_inc_id;
565
566 BEGIN
567
568 -- Standard Start of API savepoint
569 SAVEPOINT Pre_process_update;
570
571 -- Standard call to check for call compatibility.
572 IF NOT FND_API.Compatible_API_Call (l_api_version,
573 p_api_version,
574 l_api_name ,
575 G_PKG_NAME )
576 THEN
577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
578 END IF;
579
580 -- Initialize message list if p_init_msg_list is set to TRUE.
581 IF FND_API.to_Boolean( p_init_msg_list ) THEN
582 FND_MSG_PUB.initialize;
583 END IF;
584
585 -- Initialize API return status to success
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587
588 -- Api body starts
589 if (g_debug > 0) then
590 csd_gen_utility_pvt.dump_api_info
591 ( p_pkg_name => G_PKG_NAME,
592 p_api_name => l_api_name );
593 end if;
594 -- Validate the incident_id
595 IF (g_debug > 0 ) THEN
596 csd_gen_utility_pvt.add('Incident Id ='||p_incident_id);
597 END IF;
598
599
600 l_validate_flag := csd_process_util.validate_incident_id(p_incident_id );
601
602 IF NOT(l_validate_flag) then
603 IF (g_debug > 0 ) THEN
604 csd_gen_utility_pvt.add('The Incident Id is invalid ');
605 END IF;
606
607 Raise FND_API.G_EXC_ERROR;
608 END IF;
609
610 FOR grp in get_rep_group (p_incident_id )
611 LOOP
612
613 IF (g_debug > 0 ) THEN
614 csd_gen_utility_pvt.add('grp.repair_type_ref ='||grp.repair_type_ref );
615 END IF;
616
617 IF (g_debug > 0 ) THEN
618 csd_gen_utility_pvt.add('grp.group_txn_status ='||grp.group_txn_status);
619 END IF;
620
621
622 IF grp.repair_type_ref in ('RR','E','WR') THEN
623
624 --IF grp.group_txn_status = 'OM_BOOKED' then
625
626 IF (g_debug > 0 ) THEN
627 csd_gen_utility_pvt.add('grp.recd qty ='||grp.received_quantity );
628 END IF;
629
630 -- Calling Group_Rma_Update to update
631 -- all the RO that have been recd
632 Group_Rma_Update
633 ( p_api_version => p_api_version,
634 p_commit => fnd_api.g_false,
635 p_init_msg_list => fnd_api.g_true,
636 p_validation_level => fnd_api.g_valid_level_full,
637 p_repair_group_id => grp.repair_group_id,
638 x_update_count => x_update_count,
639 x_return_status => x_return_status,
640 x_msg_count => x_msg_count,
641 x_msg_data => x_msg_data );
642
643 IF (g_debug > 0 ) THEN
644 csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
645 END IF;
646
647
648 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
649 IF (g_debug > 0 ) THEN
650 csd_gen_utility_pvt.ADD('Group_ship_update failed ');
651 END IF;
652
653 RAISE FND_API.G_EXC_ERROR;
654 END IF;
655
656 IF (nvl(grp.received_quantity,0) + nvl(x_update_count,0) = nvl(grp.repair_order_quantity,0)) then
657
658 IF (g_debug > 0 ) THEN
659 csd_gen_utility_pvt.add('Updating group txn status and qty ');
660 END IF;
661
662 -- Update txn_status and rcvd qty
663 Update csd_repair_order_groups
664 set received_quantity = nvl(repair_order_quantity,0) ,
665 group_txn_status = 'OM_RECEIVED'
666 where repair_group_id = grp.repair_group_id ;
667 ELSIF nvl(x_update_count,0) > 0 then
668 IF (g_debug > 0 ) THEN
669 csd_gen_utility_pvt.add('Updating recd qty');
670 END IF;
671
672 -- Update only the rcvd qty
673 Update csd_repair_order_groups
674 set received_quantity = nvl(received_quantity,0)+ x_update_count
675 where repair_group_id = grp.repair_group_id ;
676
677 END IF;
678 --END IF;
679
680 ELSIF grp.repair_type_ref in ('ARR','WRL') THEN
681
682 --IF grp.group_txn_status = 'OM_BOOKED' then
683
684 -- Calling Group_Rma_Update to update
685 -- all the RO that have been recd
686 Group_Rma_Update
687 ( p_api_version => p_api_version,
688 p_commit => fnd_api.g_false,
689 p_init_msg_list => fnd_api.g_true,
690 p_validation_level => fnd_api.g_valid_level_full,
691 p_repair_group_id => grp.repair_group_id,
692 x_update_count => x_update_count,
693 x_return_status => x_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data );
696
697 IF (g_debug > 0 ) THEN
698 csd_gen_utility_pvt.add('grp.recd qty ='||grp.received_quantity );
699 END IF;
700
701 IF (g_debug > 0 ) THEN
702 csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
703 END IF;
704
705
706 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
707 IF (g_debug > 0 ) THEN
708 csd_gen_utility_pvt.ADD('Group_rma_update failed ');
709 END IF;
710
711 RAISE FND_API.G_EXC_ERROR;
712 END IF;
713
714 IF (nvl(grp.received_quantity,0) + nvl(x_update_count,0) = nvl(grp.repair_order_quantity,0)) then
715
716 IF (g_debug > 0 ) THEN
717 csd_gen_utility_pvt.add('Updating group txn status and qty ');
718 END IF;
719
720 -- Update txn_status and rcvd qty
721 Update csd_repair_order_groups
722 set received_quantity = nvl(repair_order_quantity,0) ,
723 group_txn_status = 'OM_RECEIVED'
724 where repair_group_id = grp.repair_group_id ;
725 ELSIF nvl(x_update_count,0) > 0 then
726 IF (g_debug > 0 ) THEN
727 csd_gen_utility_pvt.add('Updating recd qty');
728 END IF;
729
730 -- Update only the rcvd qty
731 Update csd_repair_order_groups
732 set received_quantity = nvl(received_quantity,0)+ x_update_count
733 where repair_group_id = grp.repair_group_id ;
734
735 END IF;
736
737 -- Calling Group_ship_update to update
738 -- all the loaner that have been shipped
739 Group_ship_update
740 ( p_api_version => p_api_version,
741 p_commit => fnd_api.g_false,
742 p_init_msg_list => fnd_api.g_true,
743 p_validation_level => fnd_api.g_valid_level_full,
744 p_repair_group_id => grp.repair_group_id,
745 x_update_count => x_update_count,
746 x_return_status => x_return_status,
747 x_msg_count => x_msg_count,
748 x_msg_data => x_msg_data );
749
750 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
751 IF (g_debug > 0 ) THEN
752 csd_gen_utility_pvt.ADD('Group_ship_update failed ');
753 END IF;
754
755 RAISE FND_API.G_EXC_ERROR;
756 END IF;
757
758 --END IF;
759
760 ELSIF grp.repair_type_ref in ('AE','AL','R') THEN
761
762 --IF grp.group_txn_status = 'OM_RELEASED' then
763
764 -- Calling Group_ship_update to update
765 -- all the RO that have been shipped
766 Group_ship_update
767 ( p_api_version => p_api_version,
768 p_commit => fnd_api.g_false,
769 p_init_msg_list => fnd_api.g_true,
770 p_validation_level => fnd_api.g_valid_level_full,
771 p_repair_group_id => grp.repair_group_id,
772 x_update_count => x_update_count,
773 x_return_status => x_return_status,
774 x_msg_count => x_msg_count,
775 x_msg_data => x_msg_data );
776
777 IF (g_debug > 0 ) THEN
778 csd_gen_utility_pvt.add('grp.shipped qty ='||grp.shipped_quantity );
779 END IF;
780
781 IF (g_debug > 0 ) THEN
782 csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
783 END IF;
784
785
786 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
787 IF (g_debug > 0 ) THEN
788 csd_gen_utility_pvt.ADD('Group_ship_update failed ');
789 END IF;
790
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793
794 IF (nvl(grp.shipped_quantity,0) + nvl(x_update_count,0)) = nvl(grp.repair_order_quantity,0) then
795 IF (g_debug > 0 ) THEN
796 csd_gen_utility_pvt.add('Updating group status and qty ');
797 END IF;
798
799 -- Update txn_status and rcvd qty
800 Update csd_repair_order_groups
801 set shipped_quantity = nvl(repair_order_quantity,0) ,
802 group_txn_status = 'OM_SHIPPED'
803 where repair_group_id = grp.repair_group_id ;
804 ELSIF nvl(x_update_count,0) > 0 then
805 IF (g_debug > 0 ) THEN
806 csd_gen_utility_pvt.add('updating shipped qty ='||x_update_count );
807 END IF;
808
809 -- Update only rcvd qty
810 Update csd_repair_order_groups
811 set shipped_quantity = nvl(shipped_quantity,0)+x_update_count
812 where repair_group_id = grp.repair_group_id ;
813
814 END IF;
815
816 --END IF;
817
818 END IF; -- end if repair_type_ref
819
820 END LOOP; -- end of all groups
821 EXCEPTION
822 WHEN FND_API.G_EXC_ERROR THEN
823 ROLLBACK TO Pre_process_update;
824 x_return_status := FND_API.G_RET_STS_ERROR ;
825 FND_MSG_PUB.Count_And_Get
826 (p_count => x_msg_count,
827 p_data => x_msg_data );
828 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
830 ROLLBACK TO Pre_process_update ;
831 FND_MSG_PUB.Count_And_Get
832 ( p_count => x_msg_count,
833 p_data => x_msg_data );
834 WHEN OTHERS THEN
835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
836 ROLLBACK TO Pre_process_update ;
837 IF FND_MSG_PUB.Check_Msg_Level
838 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
839 THEN
840 FND_MSG_PUB.Add_Exc_Msg
841 (G_PKG_NAME ,
842 l_api_name );
843 END IF;
844 FND_MSG_PUB.Count_And_Get
845 (p_count => x_msg_count,
846 p_data => x_msg_data );
847 END Pre_process_update;
848
849 /*--------------------------------------------------*/
850 /* procedure name: Post_process_update */
851 /* description : procedure that updates depot */
852 /* after post-process is complete */
853 /*--------------------------------------------------*/
854
855 procedure Post_process_update
856 ( p_api_version IN NUMBER,
857 p_commit IN VARCHAR2 := fnd_api.g_false,
858 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
859 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
860 p_incident_id IN number,
861 x_return_status OUT NOCOPY VARCHAR2,
862 x_msg_count OUT NOCOPY NUMBER,
863 x_msg_data OUT NOCOPY VARCHAR2
864 ) IS
865
866 l_api_name CONSTANT VARCHAR2(30) := 'Post_process_update';
867 l_api_version CONSTANT NUMBER := 1.0;
868 l_msg_count NUMBER;
869 l_msg_data VARCHAR2(100);
870 l_msg_index NUMBER;
871 l_validate_flag Boolean;
872 x_update_count number;
873
874 CURSOR get_rep_group (p_inc_id in number) IS
875 Select crog.repair_group_id,
876 crt.repair_type_ref,
877 crog.group_txn_status,
878 crog.repair_order_quantity,
879 crog.received_quantity,
880 crog.shipped_quantity
881 from csd_repair_order_groups crog,
882 csd_repair_types_vl crt
883 where crog.repair_type_id = crt.repair_type_id
884 and crog.incident_id = p_inc_id;
885
886 BEGIN
887
888 -- Standard Start of API savepoint
889 SAVEPOINT Post_process_update;
890
891 -- Standard call to check for call compatibility.
892 IF NOT FND_API.Compatible_API_Call (l_api_version,
893 p_api_version,
894 l_api_name ,
895 G_PKG_NAME )
896 THEN
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END IF;
899
900 -- Initialize message list if p_init_msg_list is set to TRUE.
901 IF FND_API.to_Boolean( p_init_msg_list ) THEN
902 FND_MSG_PUB.initialize;
903 END IF;
904
905 -- Initialize API return status to success
906 x_return_status := FND_API.G_RET_STS_SUCCESS;
907
908 -- Api body starts
909 if (g_debug > 0 ) then
910 csd_gen_utility_pvt.dump_api_info
911 ( p_pkg_name => G_PKG_NAME,
912 p_api_name => l_api_name );
913 end if;
914 -- Validate the incident_id
915 IF (g_debug > 0 ) THEN
916 csd_gen_utility_pvt.add('Incident Id ='||p_incident_id);
917 END IF;
918
919 l_validate_flag := csd_process_util.validate_incident_id(p_incident_id );
920
921 IF NOT(l_validate_flag) then
922 IF (g_debug > 0 ) THEN
923 csd_gen_utility_pvt.add('The Incident Id is invalid ');
924 END IF;
925
926 Raise FND_API.G_EXC_ERROR ;
927 END IF;
928
929 FOR grp in get_rep_group (p_incident_id )
930 LOOP
931
932 IF (g_debug > 0 ) THEN
933 csd_gen_utility_pvt.add('grp.repair_type_ref ='||grp.repair_type_ref );
934 END IF;
935
936 IF (g_debug > 0 ) THEN
937 csd_gen_utility_pvt.add('grp.group_txn_status ='||grp.group_txn_status);
938 END IF;
939
940
941 IF grp.repair_type_ref in ('AE','AL') THEN
942
943 --IF grp.group_txn_status = 'OM_BOOKED' then
944
945 -- Calling Group_Rma_Update to update
946 -- all the RO that have been recd
947 Group_Rma_Update
948 ( p_api_version => p_api_version,
949 p_commit => fnd_api.g_false,
950 p_init_msg_list => fnd_api.g_true,
951 p_validation_level => fnd_api.g_valid_level_full,
952 p_repair_group_id => grp.repair_group_id,
953 x_update_count => x_update_count,
954 x_return_status => x_return_status,
955 x_msg_count => x_msg_count,
956 x_msg_data => x_msg_data );
957
958 IF (g_debug > 0 ) THEN
959 csd_gen_utility_pvt.add('grp.recd qty ='||grp.received_quantity );
960 END IF;
961
962 IF (g_debug > 0 ) THEN
963 csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
964 END IF;
965
966
967 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
968 IF (g_debug > 0 ) THEN
969 csd_gen_utility_pvt.ADD('Group_ship_update failed ');
970 END IF;
971
972 RAISE FND_API.G_EXC_ERROR;
973 END IF;
974
975 IF nvl(grp.received_quantity,0)+nvl(x_update_count,0) = nvl(grp.repair_order_quantity,0) then
976 IF (g_debug > 0 ) THEN
977 csd_gen_utility_pvt.add('Updating the group txn status ');
978 END IF;
979
980 -- Update txn_status and rcvd qty
981 Update csd_repair_order_groups
982 set received_quantity = nvl(received_quantity ,0) + nvl(x_update_count,0),
983 group_txn_status = 'OM_RECEIVED'
984 where repair_group_id = grp.repair_group_id ;
985 ELSIF nvl(x_update_count,0) > 0 then
986 IF (g_debug > 0 ) THEN
987 csd_gen_utility_pvt.add('Updating the recd qty ');
988 END IF;
989
990 -- Update only rcvd qty
991 Update csd_repair_order_groups
992 set received_quantity = nvl(received_quantity ,0) + nvl(x_update_count,0)
993 where repair_group_id = grp.repair_group_id ;
994
995 END IF;
996
997 --END IF;
998
999 ELSIF grp.repair_type_ref in ('RR','E','WR') THEN
1000
1001 --IF grp.group_txn_status = 'OM_RELEASED' then
1002
1003 -- Calling Group_ship_update to update
1004 -- all the RO that have been recd
1005 Group_ship_update
1006 ( p_api_version => p_api_version,
1007 p_commit => fnd_api.g_false,
1008 p_init_msg_list => fnd_api.g_true,
1009 p_validation_level => fnd_api.g_valid_level_full,
1010 p_repair_group_id => grp.repair_group_id,
1011 x_update_count => x_update_count,
1012 x_return_status => x_return_status,
1013 x_msg_count => x_msg_count,
1014 x_msg_data => x_msg_data );
1015
1016 IF (g_debug > 0 ) THEN
1017 csd_gen_utility_pvt.add('grp.shipped qty ='||grp.shipped_quantity );
1018 END IF;
1019
1020 IF (g_debug > 0 ) THEN
1021 csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
1022 END IF;
1023
1024
1025 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1026 IF (g_debug > 0 ) THEN
1027 csd_gen_utility_pvt.ADD('Group_ship_update failed ');
1028 END IF;
1029
1030 RAISE FND_API.G_EXC_ERROR;
1031 END IF;
1032
1033 IF nvl(grp.shipped_quantity,0) + nvl(x_update_count,0) = grp.repair_order_quantity then
1034 IF (g_debug > 0 ) THEN
1035 csd_gen_utility_pvt.add('updating group txn status');
1036 END IF;
1037
1038
1039 -- Update txn_status and rcvd qty
1040 Update csd_repair_order_groups
1041 set shipped_quantity = nvl(shipped_quantity ,0) + nvl(x_update_count,0),
1042 group_txn_status = 'OM_SHIPPED'
1043 where repair_group_id = grp.repair_group_id ;
1044
1045 ELSIF nvl(x_update_count,0) > 0 THEN
1046 IF (g_debug > 0 ) THEN
1047 csd_gen_utility_pvt.add('updating only shipped qty');
1048 END IF;
1049
1050 -- Update txn_status and rcvd qty
1051 Update csd_repair_order_groups
1052 set shipped_quantity = nvl(shipped_quantity ,0) + nvl(x_update_count,0)
1053 where repair_group_id = grp.repair_group_id ;
1054 END IF;
1055
1056 --END IF;
1057
1058 ELSIF grp.repair_type_ref in ('ARR','WRL') THEN
1059
1060 -- IF grp.group_txn_status = 'OM_RELEASED' then
1061
1062 -- Calling Group_ship_update to update
1063 -- all the RO that have been recd
1064 Group_ship_update
1065 ( p_api_version => p_api_version,
1066 p_commit => fnd_api.g_false,
1067 p_init_msg_list => fnd_api.g_true,
1068 p_validation_level => fnd_api.g_valid_level_full,
1069 p_repair_group_id => grp.repair_group_id,
1070 x_update_count => x_update_count,
1071 x_return_status => x_return_status,
1072 x_msg_count => x_msg_count,
1073 x_msg_data => x_msg_data );
1074
1075 IF (g_debug > 0 ) THEN
1076 csd_gen_utility_pvt.add('grp.shipped qty ='||grp.shipped_quantity );
1077 END IF;
1078
1079 IF (g_debug > 0 ) THEN
1080 csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
1081 END IF;
1082
1083
1084 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1085 IF (g_debug > 0 ) THEN
1086 csd_gen_utility_pvt.ADD('Group_ship_update failed ');
1087 END IF;
1088
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF;
1091
1092 IF nvl(grp.shipped_quantity,0) + nvl(x_update_count,0) = grp.repair_order_quantity then
1093
1094 IF (g_debug > 0 ) THEN
1095 csd_gen_utility_pvt.add('updating group txn status');
1096 END IF;
1097
1098 -- Update txn_status and rcvd qty
1099 Update csd_repair_order_groups
1100 set shipped_quantity = nvl(shipped_quantity ,0) + nvl(x_update_count,0),
1101 group_txn_status = 'OM_SHIPPED'
1102 where repair_group_id = grp.repair_group_id ;
1103
1104 ELSIF nvl(x_update_count,0) > 0 THEN
1105 IF (g_debug > 0 ) THEN
1106 csd_gen_utility_pvt.add('updating only shipped qty');
1107 END IF;
1108
1109 -- Update txn_status and rcvd qty
1110 Update csd_repair_order_groups
1111 set shipped_quantity = nvl(shipped_quantity ,0) + nvl(x_update_count,0)
1112 where repair_group_id = grp.repair_group_id ;
1113
1114 END IF;
1115
1116 -- Calling Group_Rma_Update to update
1117 -- all the loaner that have been recd
1118 Group_Rma_Update
1119 ( p_api_version => p_api_version,
1120 p_commit => fnd_api.g_false,
1121 p_init_msg_list => fnd_api.g_true,
1122 p_validation_level => fnd_api.g_valid_level_full,
1123 p_repair_group_id => grp.repair_group_id,
1124 x_update_count => x_update_count,
1125 x_return_status => x_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data );
1128
1129 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1130 IF (g_debug > 0 ) THEN
1131 csd_gen_utility_pvt.ADD('Group_rma_update failed ');
1132 END IF;
1133
1134 RAISE FND_API.G_EXC_ERROR;
1135 END IF;
1136
1137 -- END IF;
1138
1139 END IF; -- end if repair_type_ref
1140
1141 END LOOP; -- end of all groups
1142
1143 EXCEPTION
1144 WHEN FND_API.G_EXC_ERROR THEN
1145 ROLLBACK TO Post_process_update;
1146 x_return_status := FND_API.G_RET_STS_ERROR ;
1147 FND_MSG_PUB.Count_And_Get
1148 (p_count => x_msg_count,
1149 p_data => x_msg_data );
1150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1152 ROLLBACK TO Post_process_update ;
1153 FND_MSG_PUB.Count_And_Get
1154 ( p_count => x_msg_count,
1155 p_data => x_msg_data );
1156 WHEN OTHERS THEN
1157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1158 ROLLBACK TO Post_process_update ;
1159 IF FND_MSG_PUB.Check_Msg_Level
1160 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1161 THEN
1162 FND_MSG_PUB.Add_Exc_Msg
1163 (G_PKG_NAME ,
1164 l_api_name );
1165 END IF;
1166 FND_MSG_PUB.Count_And_Get
1167 (p_count => x_msg_count,
1168 p_data => x_msg_data );
1169 END Post_process_update;
1170
1171 /*--------------------------------------------------*/
1172 /* procedure name: Group_Rma_Update */
1173 /* description : procedure used to apply contract */
1174 /* */
1175 /*--------------------------------------------------*/
1176
1177 procedure Group_Rma_Update
1178 ( p_api_version IN NUMBER,
1179 p_commit IN VARCHAR2 := fnd_api.g_false,
1180 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1181 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1182 p_repair_group_id IN NUMBER,
1183 x_update_count OUT NOCOPY NUMBER,
1184 x_return_status OUT NOCOPY VARCHAR2,
1185 x_msg_count OUT NOCOPY NUMBER,
1186 x_msg_data OUT NOCOPY VARCHAR2
1187 ) IS
1188
1189 l_api_name CONSTANT VARCHAR2(30) := 'Group_Rma_Update';
1190 l_api_version CONSTANT NUMBER := 1.0;
1191 l_msg_count NUMBER;
1192 l_msg_data VARCHAR2(100);
1193 l_msg_index NUMBER;
1194
1195 v_repair_history_id number;
1196 l_return_status varchar2(1);
1197 p_rep_hist_id number;
1198 p_result_quantity number;
1199 l_repair_number VARCHAR2(30);
1200 l_repair_line_id NUMBER;
1201 l_txn_billing_type_id NUMBER;
1202 v_total_records number :=0;
1203 l_ib_flag varchar2(1);
1204 l_instance_id number := null;
1205
1206 -- travi fix
1207 l_incident_id number;
1208 l_account_id number;
1209 l_customer_id number;
1210
1211 CURSOR get_rma_lines (p_rep_group_id in number) IS
1212 Select
1213 cr.incident_id, -- travi
1214 cr.repair_group_id,
1215 ced.order_header_id,
1216 ced.order_line_id,
1217 ced.txn_billing_type_id,
1218 cpt.product_transaction_id,
1219 cpt.action_code,
1220 ooh.order_number rma_number,
1221 ool.line_number rma_line_number,
1222 ool.line_type_id,
1223 cr.repair_line_id,
1224 cr.repair_number,
1225 rcv.organization_id,
1226 cr.inventory_item_id,
1227 rcv.unit_of_measure,
1228 rcv.transaction_date received_date,
1229 rcv.transaction_id transaction_id,
1230 rcv.quantity received_quantity,
1231 rcv.subinventory,
1232 rcv.last_updated_by who_col,
1233 rcv.oe_order_header_id rma_header_id,
1234 rst.serial_num serial_number
1235 from csd_repairs cr,
1236 csd_product_transactions cpt,
1237 cs_estimate_details ced,
1238 rcv_transactions rcv,
1239 rcv_serial_transactions rst,
1240 oe_order_headers_all ooh,
1241 oe_order_lines_all ool,
1242 cs_txn_billing_types ctbt,
1243 cs_transaction_types_b ctt
1244 where cr.repair_line_id = cpt.repair_line_id
1245 and cpt.estimate_detail_id = ced.estimate_detail_id
1246 and ced.txn_billing_type_id = ctbt.txn_billing_type_id
1247 and ctbt.transaction_type_id = ctt.transaction_type_id
1248 and ctt.depot_Repair_flag = 'Y'
1249 and cpt.action_type in ('RMA','WALK_IN_RECEIPT')
1250 and ced.original_source_code = 'DR'
1251 and cr.repair_group_id = p_rep_group_id
1252 and rcv.oe_order_line_id = ced.order_line_id
1253 and rcv.transaction_id = rst.transaction_id(+)
1254 and rcv.oe_order_line_id = ool.line_id
1255 and ool.header_id = ooh.header_id
1256 and rcv.transaction_type = 'DELIVER'
1257 and rcv.source_document_code = 'RMA'
1258 and rcv.transaction_id NOT IN
1259 (SELECT paramn1
1260 FROM csd_Repair_history crh,
1261 csd_repairs cra
1262 WHERE crh.repair_line_id = cra.repair_line_id
1263 AND crh.event_code = 'RR'
1264 AND cra.repair_group_id = p_rep_group_id );
1265
1266 BEGIN
1267
1268 -- Standard Start of API savepoint
1269 SAVEPOINT Group_Rma_Update;
1270
1271 -- Standard call to check for call compatibility.
1272 IF NOT FND_API.Compatible_API_Call (l_api_version,
1273 p_api_version,
1274 l_api_name ,
1275 G_PKG_NAME )
1276 THEN
1277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1278 END IF;
1279
1280 -- Initialize message list if p_init_msg_list is set to TRUE.
1281 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1282 FND_MSG_PUB.initialize;
1283 END IF;
1284
1285 -- Initialize API return status to success
1286 x_return_status := FND_API.G_RET_STS_SUCCESS;
1287
1288 -- Api body starts
1289 if (g_debug > 0) then
1290 csd_gen_utility_pvt.dump_api_info
1291 ( p_pkg_name => G_PKG_NAME,
1292 p_api_name => l_api_name );
1293 end if;
1294 v_total_records := 0;
1295
1296 FOR C1 in get_rma_lines (p_repair_group_id )
1297 LOOP
1298
1299 -- convert to primary UOM
1300 csd_depot_repair_cntr.convert_to_primary_uom
1301 (C1.inventory_item_id,
1302 C1.organization_id,
1303 C1.unit_of_measure,
1304 C1.received_quantity,
1305 p_result_quantity);
1306
1307 IF (g_debug > 0 ) THEN
1308 csd_gen_utility_pvt.add('p_result_quantity='|| p_result_quantity);
1309 END IF;
1310
1311
1312 IF C1.action_code <> 'LOANER' THEN
1313
1314 -- Update csd_repairs with txn_status
1315 -- and the rscd_qty
1316 IF (g_debug > 0 ) THEN
1317 csd_gen_utility_pvt.add('Before update csd_repairs');
1318 END IF;
1319
1320
1321 update csd_repairs
1322 set quantity_rcvd = nvl(quantity_rcvd,0)+ nvl(p_result_quantity,0),
1323 ro_txn_status = 'OM_RECEIVED'
1324 where repair_line_id = C1.repair_line_id;
1325
1326 IF (g_debug > 0 ) THEN
1327 csd_gen_utility_pvt.add('After update csd_repairs');
1328 END IF;
1329
1330 END IF;
1331
1332 IF C1.serial_number is not null then
1333
1334 Begin
1335
1336 IF (g_debug > 0 ) THEN
1337 csd_gen_utility_pvt.add('Before select ib_flag');
1338 END IF;
1339
1340
1341 select
1342 comms_nl_trackable_flag
1343 into l_ib_flag
1344 from mtl_system_items
1345 where inventory_item_id = C1.inventory_item_id
1346 and organization_id = C1.organization_id
1347 and rownum < 2; -- travi
1348
1349 IF (g_debug > 0 ) THEN
1350 csd_gen_utility_pvt.add('After select ib_flag');
1351 END IF;
1352
1353
1354 Exception
1355 When No_data_found then
1356 IF (g_debug > 0 ) THEN
1357 csd_gen_utility_pvt.ADD('Invalid Inv item Id ');
1358 END IF;
1359
1360 fnd_message.set_name('CSD','CSD_INVALID_ITEM_ID');
1361 fnd_message.set_token('INVENTORY_ITEM_ID',C1.inventory_item_id);
1362 fnd_msg_pub.add;
1363 RAISE FND_API.G_EXC_ERROR;
1364 End;
1365
1366 IF l_ib_flag = 'Y' THEN
1367
1368 Begin
1369
1370 IF (g_debug > 0 ) THEN
1371 csd_gen_utility_pvt.add('Serial number : '||C1.serial_number);
1372 END IF;
1373
1374 IF (g_debug > 0 ) THEN
1375 csd_gen_utility_pvt.add('inventory_item_id : '||C1.inventory_item_id);
1376 END IF;
1377
1378
1379 -- travi fix for muliple rows picked for inctance id problem
1380 IF (g_debug > 0 ) THEN
1381 csd_gen_utility_pvt.add('Before select account_id, customer_id');
1382 END IF;
1383
1384 select account_id, customer_id
1385 into l_account_id, l_customer_id
1386 from csd_incidents_v
1387 where incident_id = C1.incident_id;
1388
1389 IF (g_debug > 0 ) THEN
1390 csd_gen_utility_pvt.add('After select account_id, customer_id');
1391 END IF;
1392
1393 -- travi fix for muliple rows picked for inctance id problem
1394 Exception
1395 When No_data_found then
1396 IF (g_debug > 0 ) THEN
1397 csd_gen_utility_pvt.ADD('No data found for the incident id');
1398 END IF;
1399
1400 End;
1401
1402 Begin
1403
1404 IF (g_debug > 0 ) THEN
1405 csd_gen_utility_pvt.add('Before select instance_id');
1406 END IF;
1407
1408
1409 Select instance_id
1410 into l_instance_id
1411 from csi_item_instances
1412 where serial_number = C1.serial_number
1413 and inventory_item_id = C1.inventory_item_id
1414 and trunc(sysdate) between trunc(nvl(active_start_date,sysdate))
1415 and trunc(nvl(active_end_date,sysdate))
1416 and owner_party_account_id = nvl(l_account_id, owner_party_account_id) -- sr.account_id
1417 and owner_party_id = l_customer_id; -- sr.customer_id
1418
1419 IF (g_debug > 0 ) THEN
1420 csd_gen_utility_pvt.add('After select instance_id');
1421 END IF;
1422
1423
1424 Exception
1425 When No_data_found then
1426 IF (g_debug > 0 ) THEN
1427 csd_gen_utility_pvt.ADD('Invalid Serial Number ');
1428 END IF;
1429
1430 fnd_message.set_name('CSD','CSD_INVALID_SERIAL_NUMBER');
1431 fnd_message.set_token('SERIAL_NUMBER',C1.serial_number );
1432 fnd_msg_pub.add;
1433 RAISE FND_API.G_EXC_ERROR;
1434 End;
1435
1436 -- Update csd_repairs with txn_status
1437 -- and the rscd_qty
1438 update csd_repairs
1439 set serial_number = C1.serial_number ,
1440 customer_product_id = l_instance_id
1441 where repair_line_id = C1.repair_line_id;
1442
1443 Else
1444
1445 -- Update csd_repairs with txn_status
1446 -- and the rscd_qty
1447 update csd_repairs
1448 set serial_number = C1.serial_number
1449 where repair_line_id = C1.repair_line_id;
1450
1451 End If;
1452
1453 END IF;
1454
1455 -- Update the prod txns withe the status
1456 Update csd_product_transactions
1457 set prod_txn_status = 'RECEIVED'
1458 where product_transaction_id = C1.product_transaction_id;
1459
1460 fnd_message.set_name('CSD','CSD_DRC_RMA_RECEIPT');
1461 fnd_message.set_token('RMA_NO',C1.rma_number);
1462 fnd_message.set_token('REP_NO',C1.repair_number);
1463 fnd_message.set_token('QTY_RCVD',to_char(C1.received_quantity));
1464 IF (g_debug > 0 ) THEN
1465 csd_gen_utility_pvt.add(fnd_message.get);
1466 END IF;
1467
1468
1469 IF (g_debug > 0 ) THEN
1470 csd_gen_utility_pvt.add('Calling CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write ');
1471 END IF;
1472
1473
1474 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write (
1475 P_Api_Version_Number => 1.0,
1476 P_Init_Msg_List => 'T',
1477 P_Commit => 'F',
1478 p_validation_level => null,
1479 p_action_code => 0 ,
1480 px_REPAIR_HISTORY_ID => p_rep_hist_id,
1481 p_OBJECT_VERSION_NUMBER => null,
1482 p_REQUEST_ID => null,
1483 p_PROGRAM_ID => null,
1484 p_PROGRAM_APPLICATION_ID => null,
1485 p_PROGRAM_UPDATE_DATE => null,
1486 p_CREATED_BY => FND_GLOBAL.USER_ID,
1487 p_CREATION_DATE => sysdate,
1488 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1489 p_LAST_UPDATE_DATE => sysdate,
1490 p_REPAIR_LINE_ID => C1.repair_line_id,
1491 p_EVENT_CODE => 'RR',
1492 p_EVENT_DATE => C1.received_date,
1493 p_QUANTITY => C1.received_quantity,
1494 p_PARAMN1 => C1.transaction_id,
1495 p_PARAMN2 => C1.rma_line_number,
1496 p_PARAMN3 => C1.line_type_id,
1497 p_PARAMN4 => C1.txn_billing_type_id,
1498 p_PARAMN5 => C1.who_col,
1499 p_PARAMN6 => C1.rma_header_id,
1500 p_PARAMN7 => null,
1501 p_PARAMN8 => null,
1502 p_PARAMN9 => null,
1503 p_PARAMN10 => null,
1504 p_PARAMC1 => C1.subinventory,
1505 p_PARAMC2 => C1.rma_number,
1506 p_PARAMC3 => null,
1507 p_PARAMC4 => null,
1508 p_PARAMC5 => null,
1509 p_PARAMC6 => null,
1510 p_PARAMC7 => null,
1511 p_PARAMC8 => null,
1512 p_PARAMC9 => null,
1513 p_PARAMC10 => null,
1514 p_PARAMD1 => null,
1515 p_PARAMD2 => null,
1516 p_PARAMD3 => null,
1517 p_PARAMD4 => null,
1518 p_PARAMD5 => null,
1519 p_PARAMD6 => null,
1520 p_PARAMD7 => null,
1521 p_PARAMD8 => null,
1522 p_PARAMD9 => null,
1523 p_PARAMD10 => null,
1524 p_ATTRIBUTE_CATEGORY => null,
1525 p_ATTRIBUTE1 => null,
1526 p_ATTRIBUTE2 => null,
1527 p_ATTRIBUTE3 => null,
1528 p_ATTRIBUTE4 => null,
1529 p_ATTRIBUTE5 => null,
1530 p_ATTRIBUTE6 => null,
1531 p_ATTRIBUTE7 => null,
1532 p_ATTRIBUTE8 => null,
1533 p_ATTRIBUTE9 => null,
1534 p_ATTRIBUTE10 => null,
1535 p_ATTRIBUTE11 => null,
1536 p_ATTRIBUTE12 => null,
1537 p_ATTRIBUTE13 => null,
1538 p_ATTRIBUTE14 => null,
1539 p_ATTRIBUTE15 => null,
1540 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1541 X_Return_Status => l_return_status ,
1542 X_Msg_Count => l_msg_count,
1543 X_Msg_Data => l_msg_data );
1544
1545 IF (g_debug > 0 ) THEN
1546 csd_gen_utility_pvt.add('CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
1547 END IF;
1548
1549 IF (g_debug > 0 ) THEN
1550 csd_gen_utility_pvt.add('Successfully completed Depot RMA receipt update ');
1551 END IF;
1552
1553
1554 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1555 IF (g_debug > 0 ) THEN
1556 csd_gen_utility_pvt.ADD('validate_and_write failed ');
1557 END IF;
1558
1559 RAISE FND_API.G_EXC_ERROR;
1560 END IF;
1561
1562 v_total_records := v_total_records + 1;
1563
1564 End loop;
1565
1566 fnd_message.set_name('CSD','CSD_DRC_WIP_TOT_REC_PROC');
1567 fnd_message.set_token('TOT_REC',to_char(v_total_records));
1568 IF (g_debug > 0 ) THEN
1569 csd_gen_utility_pvt.add(fnd_message.get);
1570 END IF;
1571
1572
1573 -- Return the count of number of records updated
1574 x_update_count := v_total_records ;
1575
1576 EXCEPTION
1577 WHEN FND_API.G_EXC_ERROR THEN
1578 ROLLBACK TO Group_Rma_Update;
1579 x_return_status := FND_API.G_RET_STS_ERROR ;
1580 FND_MSG_PUB.Count_And_Get
1581 (p_count => x_msg_count,
1582 p_data => x_msg_data );
1583 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1585 ROLLBACK TO Group_Rma_Update ;
1586 FND_MSG_PUB.Count_And_Get
1587 ( p_count => x_msg_count,
1588 p_data => x_msg_data );
1589 WHEN OTHERS THEN
1590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1591 ROLLBACK TO Group_Rma_Update ;
1592 IF FND_MSG_PUB.Check_Msg_Level
1593 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1594 THEN
1595 FND_MSG_PUB.Add_Exc_Msg
1596 (G_PKG_NAME ,
1597 l_api_name );
1598 END IF;
1599 FND_MSG_PUB.Count_And_Get
1600 (p_count => x_msg_count,
1601 p_data => x_msg_data );
1602 END Group_Rma_Update;
1603
1604 /*--------------------------------------------------*/
1605 /* procedure name: Group_ship_update */
1606 /* description : procedure used to apply contract */
1607 /* */
1608 /*--------------------------------------------------*/
1609
1610 procedure Group_ship_update
1611 ( p_api_version IN NUMBER,
1612 p_commit IN VARCHAR2 := fnd_api.g_false,
1613 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1614 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1615 p_repair_group_id IN number,
1616 x_update_count 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
1623 l_api_name CONSTANT VARCHAR2(30) := 'Group_ship_update';
1624 l_api_version CONSTANT NUMBER := 1.0;
1625 l_msg_count NUMBER;
1626 l_msg_data VARCHAR2(100);
1627 l_msg_index NUMBER;
1628
1629 v_total_records number;
1630 v_repair_history_id number;
1631 l_return_status varchar2(1);
1632 l_rep_hist_id number;
1633 p_result_ship_quantity number;
1634 l_pt_serial_num varchar2(30);
1635
1636
1637 Cursor DEPOT_SHIPMENT_LINES ( p_rep_group_id number) is
1638 Select
1639 dd.serial_number sl_number,
1640 cra.quantity qty,
1641 cpt.product_transaction_id,
1642 cpt.action_code,
1643 oeh.order_number order_number,
1644 oeh.header_id sales_order_header,
1645 oel.line_number order_line_number,
1646 oel.line_type_id,
1647 cra.repair_number,
1648 cra.repair_line_id,
1649 ced.txn_billing_type_id,
1650 dd.requested_quantity,
1651 dd.shipped_quantity,
1652 dl.initial_pickup_date date_shipped,
1653 dd.delivery_detail_id,
1654 dd.requested_quantity_uom shipped_uom_code,
1655 mtlu.unit_of_measure shipped_uom,
1656 dd.inventory_item_id ,
1657 dd.organization_id
1658 from
1659 csd_Repairs cra,
1660 csd_product_transactions cpt,
1661 cs_estimate_details ced,
1662 wsh_new_deliveries dl,
1663 wsh_delivery_assignments da,
1664 wsh_delivery_details dd ,
1665 oe_order_headers_all oeh,
1666 oe_order_lines_all oel,
1667 mtl_units_of_measure mtlu
1668 Where cra.repair_group_id = p_rep_group_id
1669 and cra.repair_line_id = cpt.repair_line_id
1670 and cpt.estimate_detail_id = ced.estimate_detail_id
1671 and ced.original_source_code = 'DR'
1672 and dd.delivery_detail_id = da.delivery_detail_id
1673 and da.delivery_id = dl.delivery_id(+)
1674 and ced.order_line_id = oel.line_id
1675 and oel.header_id = oeh.header_id
1676 and dd.source_header_id = ced.order_header_id
1677 and dd.source_line_id = ced.order_line_id
1678 and dd.released_status = 'C'
1679 and dd.delivery_detail_id not in
1680 (select paramn1
1681 from csd_Repair_history
1682 where repair_line_id = cra.repair_line_id
1683 and event_code='PS')
1684 and mtlu.uom_code = dd.requested_quantity_uom;
1685
1686 Begin
1687
1688 -- Standard Start of API savepoint
1689 SAVEPOINT Group_ship_update;
1690
1691 -- Standard call to check for call compatibility.
1692 IF NOT FND_API.Compatible_API_Call (l_api_version,
1693 p_api_version,
1694 l_api_name ,
1695 G_PKG_NAME )
1696 THEN
1697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1698 END IF;
1699
1700 -- Initialize message list if p_init_msg_list is set to TRUE.
1701 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1702 FND_MSG_PUB.initialize;
1703 END IF;
1704
1705 -- Initialize API return status to success
1706 x_return_status := FND_API.G_RET_STS_SUCCESS;
1707
1708 IF (g_debug > 0 ) THEN
1709 csd_gen_utility_pvt.add('at the begin Group_ship_update');
1710 END IF;
1711
1712
1713 -- Api body starts
1714 if (g_debug > 0) then
1715 csd_gen_utility_pvt.dump_api_info
1716 ( p_pkg_name => G_PKG_NAME,
1717 p_api_name => l_api_name );
1718 end if;
1719 v_total_records := 0;
1720
1721 For I in depot_shipment_lines(p_repair_group_id)
1722 LOOP
1723
1724 IF (g_debug > 0 ) THEN
1725 csd_gen_utility_pvt.add('Calling the convert to primary uom ');
1726 END IF;
1727
1728
1729 csd_depot_repair_cntr.convert_to_primary_uom
1730 (i.inventory_item_id,
1731 i.organization_id,
1732 i.shipped_uom,
1733 i.shipped_quantity,
1734 p_result_ship_quantity);
1735
1736 IF (g_debug > 0 ) THEN
1737 csd_gen_utility_pvt.add(' p_result_ship_quantity= '|| p_result_ship_quantity);
1738 END IF;
1739
1740
1741 IF I.action_code <> 'LOANER' THEN
1742
1743 -- Update the csd_repairs table
1744 update csd_repairs
1745 set quantity_shipped = nvl(quantity_shipped,0)+nvl(p_result_ship_quantity,0),
1746 ro_txn_status = 'RO_SHIPPED'
1747 where repair_line_id = I.repair_line_id;
1748 END IF;
1749
1750 -- Update csd_product_transactions table with the status
1751 update csd_product_transactions
1752 set prod_txn_status= 'SHIPPED'
1753 where product_transaction_id = I.product_transaction_id;
1754
1755 IF (g_debug > 0 ) THEN
1756 csd_gen_utility_pvt.add('Updated csd_repairs table');
1757 END IF;
1758
1759
1760 fnd_message.set_name('CSD','CSD_DRC_QTY_SHIPPED');
1761 fnd_message.set_token('ORDER_NO',i.order_number);
1762 fnd_message.set_token('REP_NO',i.repair_number);
1763 fnd_message.set_token('QTY_SHIP',to_char(p_result_ship_quantity));
1764 IF (g_debug > 0 ) THEN
1765 csd_gen_utility_pvt.add(fnd_message.get);
1766 END IF;
1767
1768
1769 IF (g_debug > 0 ) THEN
1770 csd_gen_utility_pvt.add('Calling CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write');
1771 END IF;
1772
1773
1774 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write (
1775 P_Api_Version_Number => 1.0,
1776 P_Init_Msg_List => 'T',
1777 P_Commit => 'F',
1778 p_validation_level => null,
1779 p_action_code => 0 ,
1780 px_REPAIR_HISTORY_ID => l_rep_hist_id,
1781 p_OBJECT_VERSION_NUMBER => null,
1782 p_REQUEST_ID => null,
1783 p_PROGRAM_ID => null,
1784 p_PROGRAM_APPLICATION_ID => null,
1785 p_PROGRAM_UPDATE_DATE => null,
1786 p_CREATED_BY => FND_GLOBAL.USER_ID,
1787 p_CREATION_DATE => sysdate,
1788 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1789 p_LAST_UPDATE_DATE => sysdate,
1790 p_REPAIR_LINE_ID => I.repair_line_id,
1791 p_EVENT_CODE => 'PS',
1792 p_EVENT_DATE => I.date_shipped,
1793 p_QUANTITY => p_result_ship_quantity,
1794 p_PARAMN1 => i.delivery_detail_id,
1795 p_PARAMN2 => i.order_line_number,
1796 p_PARAMN3 => i.line_type_id,
1797 p_PARAMN4 => i.txn_billing_type_id,
1798 p_PARAMN5 => null,
1799 p_PARAMN6 => null,
1800 p_PARAMN7 => null,
1801 p_PARAMN8 => null,
1802 p_PARAMN9 => null,
1803 p_PARAMN10 => null,
1804 p_PARAMC1 => null,
1805 p_PARAMC2 => i.order_number,
1806 p_PARAMC3 => null,
1807 p_PARAMC4 => null,
1808 p_PARAMC5 => null,
1809 p_PARAMC6 => null,
1810 p_PARAMC7 => null,
1811 p_PARAMC8 => null,
1812 p_PARAMC9 => null,
1813 p_PARAMC10 => null,
1814 p_PARAMD1 => null,
1815 p_PARAMD2 => null,
1816 p_PARAMD3 => null,
1817 p_PARAMD4 => null,
1818 p_PARAMD5 => null,
1819 p_PARAMD6 => null,
1820 p_PARAMD7 => null,
1821 p_PARAMD8 => null,
1822 p_PARAMD9 => null,
1823 p_PARAMD10 => null,
1824 p_ATTRIBUTE_CATEGORY => null,
1825 p_ATTRIBUTE1 => null,
1826 p_ATTRIBUTE2 => null,
1827 p_ATTRIBUTE3 => null,
1828 p_ATTRIBUTE4 => null,
1829 p_ATTRIBUTE5 => null,
1830 p_ATTRIBUTE6 => null,
1831 p_ATTRIBUTE7 => null,
1832 p_ATTRIBUTE8 => null,
1833 p_ATTRIBUTE9 => null,
1834 p_ATTRIBUTE10 => null,
1835 p_ATTRIBUTE11 => null,
1836 p_ATTRIBUTE12 =>null,
1837 p_ATTRIBUTE13 => null,
1838 p_ATTRIBUTE14 => null,
1839 p_ATTRIBUTE15 => null,
1840 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1841 X_Return_Status => l_return_status ,
1842 X_Msg_Count => l_msg_count,
1843 X_Msg_Data => l_msg_data );
1844
1845 IF (g_debug > 0 ) THEN
1846 csd_gen_utility_pvt.add('after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
1847 END IF;
1848
1849 IF (g_debug > 0 ) THEN
1850 csd_gen_utility_pvt.add('Successfully completed Depot repair Shipping Update');
1851 END IF;
1852
1853
1854 v_total_records := v_total_records + 1;
1855 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1856 IF (g_debug > 0 ) THEN
1857 csd_gen_utility_pvt.ADD('Validate_and_write failed ');
1858 END IF;
1859
1860 RAISE FND_API.G_EXC_ERROR;
1861 END IF;
1862
1863 End loop;
1864
1865 fnd_message.set_name('CSD','CSD_DRC_SHIP_TOTAL_REC_PROC');
1866 fnd_message.set_token('TOT_REC',to_char(v_total_records));
1867 IF (g_debug > 0 ) THEN
1868 csd_gen_utility_pvt.add(fnd_message.get);
1869 END IF;
1870
1871
1872 x_update_count := v_total_records;
1873
1874 EXCEPTION
1875 WHEN FND_API.G_EXC_ERROR THEN
1876 ROLLBACK TO Group_ship_update;
1877 x_return_status := FND_API.G_RET_STS_ERROR ;
1878 FND_MSG_PUB.Count_And_Get
1879 (p_count => x_msg_count,
1880 p_data => x_msg_data );
1881 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1883 ROLLBACK TO Group_ship_update ;
1884 FND_MSG_PUB.Count_And_Get
1885 ( p_count => x_msg_count,
1886 p_data => x_msg_data );
1887 WHEN OTHERS THEN
1888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1889 ROLLBACK TO Group_ship_update ;
1890 IF FND_MSG_PUB.Check_Msg_Level
1891 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1892 THEN
1893 FND_MSG_PUB.Add_Exc_Msg
1894 (G_PKG_NAME ,
1895 l_api_name );
1896 END IF;
1897 FND_MSG_PUB.Count_And_Get
1898 (p_count => x_msg_count,
1899 p_data => x_msg_data );
1900
1901 End Group_ship_update;
1902
1903 End CSD_DEPOT_UPDATE_PVT;