[Home] [Help]
PACKAGE BODY: APPS.CSD_DEPOT_REPAIR_CNTR
Source
1 package body csd_depot_repair_cntr as
2 /* $Header: csddrclb.pls 115.36 2003/05/01 23:00:05 sangigup ship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables
6 -- ---------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_DEPOT_REPAIR_CNTR';
9 G_FILE_NAME CONSTANT VARCHAR2(30) := 'csddrclb.pls';
10 g_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
11 -------------------------------------------
12 -- Get txn billing type
13 -------------------------------------------
14 PROCEDURE get_txn_billing_type
15 (p_line_id IN NUMBER,
16 p_header_id IN NUMBER,
17 x_repair_number OUT NOCOPY VARCHAR2,
18 x_repair_line_id OUT NOCOPY NUMBER,
19 x_txn_billing_type_id OUT NOCOPY NUMBER,
20 x_quantity OUT NOCOPY NUMBER
21 ) IS
22
23 l_quit_flag BOOLEAN := FALSE;
24 l_line_id NUMBER := p_line_id;
25 l_header_id NUMBER := p_header_id;
26
27 BEGIN
28
29 WHILE NOT(l_quit_flag) LOOP
30
31 BEGIN
32 SELECT a.txn_billing_type_id,
33 b.repair_number,
34 b.repair_line_id,
35 b.quantity
36 INTO x_txn_billing_type_id,
37 x_repair_number ,
38 x_repair_line_id,
39 x_quantity
40 FROM cs_estimate_details a,
41 csd_repairs b
42 WHERE ((a.original_source_id = b.repair_line_id
43 AND a.original_source_code = 'DR') OR
44 (a.source_id = b.repair_line_id
45 AND a.source_code = 'DR'))
46 AND a.order_header_id = l_header_id
47 AND a.order_line_id = l_line_id;
48 l_quit_flag := TRUE;
49 EXCEPTION
50 WHEN NO_DATA_FOUND THEN
51 BEGIN
52 SELECT
53 split_from_line_id
54 INTO l_line_id
55 FROM oe_order_lines_all
56 WHERE line_id = l_line_id
57 AND header_id = l_header_id;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 x_txn_billing_type_id := -1;
61 x_repair_number := '';
62 x_repair_line_id := -1;
63 IF (g_debug > 0 ) THEN
64 csd_gen_utility_pvt.add('Could not find the split_from_line_id for line_id ='||TO_CHAR(p_line_id));
65 END IF;
66
67 l_quit_flag := TRUE;
68 END;
69
70 WHEN OTHERS THEN
71 IF (g_debug > 0 ) THEN
72 csd_gen_utility_pvt.add('Found more than one row in cs_estimate_details for line_id ='||TO_CHAR(p_line_id));
73 END IF;
74
75 l_quit_flag := TRUE;
76 END;
77 END LOOP; -- end of while loop
78
79 END;
80 -----------------------------------
81 -- Convert to primary uom
82 -----------------------------------
83 procedure convert_to_primary_uom
84 (p_item_id in number,
85 p_organization_id in number,
86 p_from_uom in varchar2,
87 p_from_quantity in number,
88 p_result_quantity OUT NOCOPY number)
89 is
90
91 v_primary_uom_code varchar2(30);
92 p_from_uom_code varchar2(3);
93
94 Begin
95
96 Begin
97 select uom_code
98 into p_from_uom_code
99 from mtl_units_of_measure
100 where unit_of_measure = p_from_uom;
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 IF (g_debug > 0 ) THEN
104 csd_gen_utility_pvt.add('no_data_found error for unit_of_measure ='||p_from_uom);
105 END IF;
106
107 WHEN OTHERS THEN
108 IF (g_debug > 0 ) THEN
109 csd_gen_utility_pvt.add('More than one row found for unit_of_measure ='||p_from_uom);
110 END IF;
111
112 End;
113
114 Begin
115 select primary_uom_code
116 into v_primary_uom_code
117 from mtl_system_items
118 where organization_id = p_organization_id
119 and inventory_item_id = p_item_id;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 IF (g_debug > 0 ) THEN
123 csd_gen_utility_pvt.add('no_data_found error(primary UOM) for inventory_item_id ='||TO_CHAR(p_item_id));
124 END IF;
125
126 WHEN OTHERS THEN
127 IF (g_debug > 0 ) THEN
128 csd_gen_utility_pvt.add('More than one row found(Primary UOM) for inventory_item_id ='||TO_CHAR(p_item_id));
129 END IF;
130
131 End;
132
133 BEGIN
134 p_result_quantity :=inv_convert.inv_um_convert(
135 p_item_id ,2,
136 p_from_quantity,p_from_uom_code,v_primary_uom_code,null,null);
137 EXCEPTION
138 WHEN OTHERS THEN
139 IF (g_debug > 0 ) THEN
140 csd_gen_utility_pvt.add('inv_convert returned with error message');
141 END IF;
142
143 END;
144
145 End;
146
147 ------------------------------------
148 -- Depot RMA Receipts
149 ------------------------------------
150
151 procedure depot_rma_receipts
152 (errbuf OUT NOCOPY varchar2,
153 retcode OUT NOCOPY number,
154 p_repair_line_id in number)
155
156 is
157 v_total_records number;
158 /**** Added Organization name in Select columns to fix bug 2760279
159 **** Added hr_organization_units in from clause
160 **** Added one joing with hr_organization_units ******************/
161
162 Cursor RMA_RECEIPT_LINES( p_repair_line_id number ) is
163 SELECT /*+ CHOOSE */ oeh.order_number rma_number,
164 oeh.header_id rma_header_id,
165 oel.line_id ,
166 oel.split_from_line_id,
167 oel.line_number rma_line_number,
168 oel.inventory_item_id,
169 rcvt.organization_id,
170 rcvt.unit_of_measure,
171 oel.line_type_id,
172 rcvt.quantity received_quantity,
173 rcvt.subinventory received_subinventory,
174 rcvt.transaction_date received_date,
175 rcvt.transaction_id,
176 rcvt.last_updated_by who_col,
177 rcvt.subinventory,
178 hou.name organization_name
179 FROM rcv_transactions rcvt,
180 oe_order_headers_all oeh,
181 oe_order_lines_all oel,
182 hr_organization_units hou
183 WHERE oel.header_id = oeh.header_id
184 AND rcvt.oe_order_line_id = oel.line_id
185 AND rcvt.transaction_type = 'RECEIVE'
186 AND rcvt.source_document_code = 'RMA'
187 And rcvt.organization_id = hou.organization_id
188 AND rcvt.transaction_id NOT IN
189 (SELECT paramn1
190 FROM csd_Repair_history crh,
191 csd_repairs cra
192 WHERE crh.repair_line_id = cra.repair_line_id
193 AND event_code='RR'
194 AND cra.repair_line_id = nvl(p_repair_line_id,cra.repair_line_id)) -- travi 020903 change
195 AND EXISTS (SELECT ced.order_header_id
196 FROM csd_repairs cra,
197 cs_estimate_details ced
198 WHERE ((cra.repair_line_id = ced.original_source_id
199 AND ced.original_source_code = 'DR') OR
200 (cra.repair_line_id = ced.source_id
201 AND ced.source_code = 'DR'))
202 AND oeh.header_id = ced.order_header_id
203 AND cra.repair_line_id = nvl(p_repair_line_id,cra.repair_line_id)); -- travi 020903 change
204
205 v_repair_history_id number;
206
207 l_return_status varchar2(1);
208 l_msg_count number;
209 l_msg_data varchar2(2000);
210 p_rep_hist_id number;
211 p_result_quantity number;
212 v_skip_record boolean;
213 v_depot_Repair_flag varchar2(1);
214 v_transaction_type_id number;
215 l_repair_number VARCHAR2(30);
216 l_repair_line_id NUMBER;
217 l_txn_billing_type_id NUMBER;
218 l_quantity NUMBER;
219
220 -- travi 012502
221 Cursor c_prd_txn_serial_num ( p_rep_line_id number ) is
222 select cpt.serial_number pt_sl_number
223 , dra.serial_number dr_sl_number
224 , cpt.inventory_item_id pt_item_id
225 , dra.inventory_item_id dr_item_id
226 from csd_product_txns_v cpt
227 , csd_repairs dra
228 where action_type = 'RMA'
229 and dra.repair_line_id = p_rep_line_id
230 and cpt.repair_line_id = dra.repair_line_id
231 and nvl(cpt.serial_number_control_code,1) > 1;
232
233 Cursor c_rcv_slnum_txn ( p_txn_id number ) is
234 select transaction_id
235 from rcv_transactions
236 where parent_transaction_id = p_txn_id;
237
238 Cursor c_rcv_txn_serial_num ( p_txn_id number ) is
239 select serial_num
240 from rcv_serial_transactions
241 where transaction_id = p_txn_id;
242
243 Cursor c_prod_txn_stat_upd ( p_rep_line_id number) is
244 Select product_transaction_id
245 from csd_product_txns_v
246 where repair_line_id = p_rep_line_id
247 and action_type in ( 'RMA','WALK_IN_RECEIPT')
248 and repair_quantity = quantity_rcvd;
249
250 l_pt_serial_num varchar2(30);
251 l_st_serial_num varchar2(30);
252 l_dr_serial_num varchar2(30);
253 l_pt_item_id number;
254 l_dr_item_id number;
255 l_sl_txn_id number;
256 l_prod_txn_stat varchar2(30) := 'RECEIVED';
257
258 Begin
259
260 v_total_records := 0;
261 IF (g_debug > 0 ) THEN
262 csd_gen_utility_pvt.add('At the Begin of Depot RMA receipt update');
263 END IF;
264
265
266 For I in rma_receipt_lines( p_repair_line_id )
267 loop
268
269 v_skip_record := false;
270 v_transaction_type_id := null;
271 v_depot_Repair_flag := null;
272 l_repair_number := '';
273 l_repair_line_id := NULL ;
274 l_txn_billing_type_id := NULL;
275
276 IF (g_debug > 0 ) THEN
277 csd_gen_utility_pvt.add('----------------------------------------------' );
278 csd_gen_utility_pvt.add('Rma Number ='||I.rma_number );
279 END IF;
280 -- travi comment to concurrent log
281 fnd_file.put_line(fnd_file.log, 'Selecting RMA Number : '||I.rma_number );
282
283 IF (g_debug > 0 ) THEN
284 csd_gen_utility_pvt.add('Rma Header Id ='||TO_CHAR(I.rma_header_id) );
285 csd_gen_utility_pvt.add('Rma Line Id ='||TO_CHAR(I.Line_id ));
286 csd_gen_utility_pvt.add('Split from Line Id ='||TO_CHAR(I.split_from_line_id));
287 END IF;
288
289
290 get_txn_billing_type
291 (p_line_id => i.line_id,
292 p_header_id => i.rma_header_id,
293 x_repair_number => l_repair_number,
294 x_repair_line_id => l_repair_line_id,
295 x_txn_billing_type_id => l_txn_billing_type_id,
296 x_quantity => l_quantity);
297
298 IF (g_debug > 0 ) THEN
299 csd_gen_utility_pvt.add('l_txn_billing_type_id='||TO_CHAR(l_txn_billing_type_id));
300 csd_gen_utility_pvt.add('l_repair_number='||l_repair_number);
301 csd_gen_utility_pvt.add('l_repair_line_id='||TO_CHAR(l_repair_line_id));
302 END IF;
303
304
305
306 Begin
307 Select transaction_type_id
308 into v_transaction_type_id
309 from cs_txn_billing_types
310 where txn_billing_type_id = l_txn_billing_type_id;
311 Exception
312 When no_data_found then
313 v_transaction_type_id := null;
314 v_skip_record := true;
315 IF (g_debug > 0 ) THEN
316 csd_gen_utility_pvt.add('No Row found for the txn_billing_type_id='||TO_CHAR(l_txn_billing_type_id));
317 END IF;
318
319 when others then
320 IF (g_debug > 0 ) THEN
321 csd_gen_utility_pvt.add('When others exception at - Transaction type id');
322 END IF;
323
324 End;
325
326 if v_transaction_type_id is not null then
327 Begin
328 Select depot_Repair_flag
329 into v_depot_repair_flag
330 from cs_transaction_types_b
331 where transaction_type_id = v_transaction_type_id;
332
333 Exception
334 when no_Data_found then
335 V_skip_record := true;
336 IF (g_debug > 0 ) THEN
337 csd_gen_utility_pvt.add('No row found for the transaction_type_id ='||TO_CHAR(v_transaction_type_id));
338 END IF;
339
340 End;
341 End if;
342
343
344 if v_depot_repair_flag = 'Y' then
345 v_skip_record := false;
346 else
347 v_skip_record := true;
348 End if;
349
350
351 if not v_skip_record then
352
353 BEGIN
354
355 IF (g_debug > 0 ) THEN
356 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - prd_txn_serial_num : repair_line_id '||to_char(l_repair_line_id));
357 END IF;
358
359
360 if (l_quantity = 1) then
361 IF (g_debug > 0 ) THEN
362 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - prd_txn_serial_num : qty '||to_char(l_quantity));
363 END IF;
364
365
366 open c_prd_txn_serial_num ( l_repair_line_id );
367
368 fetch c_prd_txn_serial_num into l_pt_serial_num, l_dr_serial_num, l_pt_item_id, l_dr_item_id;
369
370 if (c_prd_txn_serial_num%FOUND) then
371
372 IF (g_debug > 0 ) THEN
373 csd_gen_utility_pvt.add('pt_serial_num '||l_dr_serial_num);
374 csd_gen_utility_pvt.add('pt_Item_id '||to_char(l_dr_item_id));
375 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - prd_txn_serial_num : pt_serial_num '||l_pt_serial_num||' dr_serial_num '||l_dr_serial_num);
376 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - prd_txn_item_id : pt_item_id '||to_char(l_pt_item_id)||' dr_item_id '||to_char(l_dr_item_id));
377 END IF;
378
379
380 if (l_pt_item_id <> l_dr_item_id) then
381
382 IF (g_debug > 0 ) THEN
383 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - l_pt_item_id <> l_dr_item_id ');
384 END IF;
385
386 l_pt_serial_num := l_dr_serial_num;
387
388 end if;
389
390 IF (g_debug > 0 ) THEN
391 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - transaction_id '||to_char(i.transaction_id));
392 END IF;
393
394
395 open c_rcv_slnum_txn ( i.transaction_id );
396
397 fetch c_rcv_slnum_txn into l_sl_txn_id;
398
399 IF (g_debug > 0 ) THEN
400 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - parent_transaction_id '||to_char(l_sl_txn_id));
401 END IF;
402
403
404 if (l_sl_txn_id is not null) then
405 open c_rcv_txn_serial_num ( l_sl_txn_id );
406
407 fetch c_rcv_txn_serial_num into l_st_serial_num;
408
409 if (c_rcv_txn_serial_num%FOUND) then
410 IF (g_debug > 0 ) THEN
411 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - rcv_txn_serial_num'||l_st_serial_num);
412 END IF;
413
414
415 IF (g_debug > 0 ) THEN
416 csd_gen_utility_pvt.add('l_st_serial_num '||l_st_serial_num);
417 csd_gen_utility_pvt.add('l_pt_serial_num '||l_pt_serial_num);
418 END IF;
419
420
421
422 if(l_pt_serial_num <> l_st_serial_num) then
423 IF (g_debug > 0 ) THEN
424 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - l_pt_serial_num <> l_st_serial_num ');
425 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for RSC event');
426 csd_gen_utility_pvt.add('Calling CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write ');
427 END IF;
428
429
430 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write (
431 P_Api_Version_Number => 1.0,
432 P_Init_Msg_List => 'F',
433 P_Commit => 'F',
434 p_validation_level => null,
435 p_action_code => 0 ,
436 px_REPAIR_HISTORY_ID => p_rep_hist_id,
437 p_OBJECT_VERSION_NUMBER => null, -- travi ovn validation
438 p_REQUEST_ID => null,
439 p_PROGRAM_ID => null,
440 p_PROGRAM_APPLICATION_ID => null,
441 p_PROGRAM_UPDATE_DATE => null,
442 p_CREATED_BY => -1,
443 p_CREATION_DATE => sysdate,
444 p_LAST_UPDATED_BY => -1,
445 p_LAST_UPDATE_DATE => sysdate,
446 p_REPAIR_LINE_ID => l_repair_line_id,
447 p_EVENT_CODE => 'RSC', -- RMA serial number changed
448 p_EVENT_DATE => I.received_date,
449 p_QUANTITY => I.received_quantity,
450 p_PARAMN1 => i.transaction_id,
451 p_PARAMN2 => i.rma_line_number,
452 p_PARAMN3 => i.line_type_id,
453 p_PARAMN4 => l_txn_billing_type_id,
454 p_PARAMN5 => i.who_col,
455 p_PARAMN6 => i.rma_header_id,
456 p_PARAMN7 => null,
457 p_PARAMN8 => null,
458 p_PARAMN9 => null,
459 p_PARAMN10 => null,
460 p_PARAMC1 => i.subinventory,
461 p_PARAMC2 => i.rma_number,
462 p_PARAMC3 => l_pt_serial_num, -- prd txn ser num
463 p_PARAMC4 => l_st_serial_num, -- rcv ser txn ser num
464 p_PARAMC5 => null,
465 p_PARAMC6 => null,
466 p_PARAMC7 => null,
467 p_PARAMC8 => null,
468 p_PARAMC9 => null,
469 p_PARAMC10 => null,
470 p_PARAMD1 => null,
471 p_PARAMD2 => null,
472 p_PARAMD3 => null,
473 p_PARAMD4 => null,
474 p_PARAMD5 => null,
475 p_PARAMD6 => null,
476 p_PARAMD7 => null,
477 p_PARAMD8 => null,
478 p_PARAMD9 => null,
479 p_PARAMD10 => null,
480 p_ATTRIBUTE_CATEGORY => null,
481 p_ATTRIBUTE1 => null,
482 p_ATTRIBUTE2 => null,
483 p_ATTRIBUTE3 => null,
484 p_ATTRIBUTE4 => null,
485 p_ATTRIBUTE5 => null,
486 p_ATTRIBUTE6 => null,
487 p_ATTRIBUTE7 => null,
488 p_ATTRIBUTE8 => null,
489 p_ATTRIBUTE9 => null,
490 p_ATTRIBUTE10 => null,
491 p_ATTRIBUTE11 => null,
492 p_ATTRIBUTE12 =>null,
493 p_ATTRIBUTE13 => null,
494 p_ATTRIBUTE14 => null,
495 p_ATTRIBUTE15 => null,
496 p_LAST_UPDATE_LOGIN => null,
497 X_Return_Status => l_return_status,
498 X_Msg_Count => l_msg_count,
499 X_Msg_Data => l_msg_data
500 );
501
502 IF (g_debug > 0 ) THEN
503 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
504 END IF;
505
506 v_total_records := v_total_records + 1;
507
508 IF (g_debug > 0 ) THEN
509 csd_gen_utility_pvt.add('Successfully updated the history');
510 END IF;
511
512 -- travi comment to concurrent log
513 fnd_file.put_line(fnd_file.log, 'Successfully updated the history');
514
515 IF (g_debug > 0 ) THEN
516 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS - Repair Line ID : '||to_char(l_repair_line_id));
517 csd_gen_utility_pvt.add('Inserted into Repairs History Table for Serial number Change');
518 csd_gen_utility_pvt.add('Product Txn Serial number : '||l_pt_serial_num||' Recv Ser Txn Serial number : '||l_st_serial_num);
519 END IF;
520
521
522 end if;
523
524 end if;
525
526 end if; -- l_sl_txn_id is not null
527
528 end if;
529
530 end if;
531
532 Exception
533 When no_data_found then
534 IF (g_debug > 0 ) THEN
535 csd_gen_utility_pvt.add('No data found exception,in check for serial number change');
536 END IF;
537
538 When others then
539 IF (g_debug > 0 ) THEN
540 csd_gen_utility_pvt.add('When others exception,in check for serial number change');
541 END IF;
542
543 END;
544
545 End if;
546
547 -- travi 012502
548
549 if not v_skip_record then
550
551 csd_depot_repair_cntr.convert_to_primary_uom
552 (i.inventory_item_id,
553 i.organization_id,
554 i.unit_of_measure,
555 i.received_quantity,
556 p_result_quantity);
557
558 IF (g_debug > 0 ) THEN
559 csd_gen_utility_pvt.add('p_result_quantity='|| TO_CHAR(p_result_quantity));
560 END IF;
561
562
563 update csd_repairs
564 set quantity_rcvd = nvl(quantity_rcvd,0)+nvl(p_result_quantity,0)
565 where repair_line_id = l_repair_line_id;
566
567 For P in c_prod_txn_stat_upd ( l_repair_line_id )
568 Loop
569
570 Update csd_product_transactions
571 set prod_txn_status = l_prod_txn_stat
572 where product_transaction_id = P.product_transaction_id;
573
574 End Loop;
575
576 fnd_message.set_name('CSD','CSD_DRC_RMA_RECEIPT');
577 fnd_message.set_token('RMA_NO',i.rma_number);
578 fnd_message.set_token('REP_NO',l_repair_number);
579 fnd_message.set_token('QTY_RCVD',to_char(i.received_quantity));
580 IF (g_debug > 0 ) THEN
581 csd_gen_utility_pvt.add(fnd_message.get);
582 END IF;
583
584
585 IF (g_debug > 0 ) THEN
586 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for RR event');
587 END IF;
588
589
590 IF (g_debug > 0 ) THEN
591 csd_gen_utility_pvt.add('Calling CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write ');
592 END IF;
593
594
595 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write (
596 P_Api_Version_Number => 1.0,
597 P_Init_Msg_List => 'F',
598 P_Commit => 'F',
599 p_validation_level => null,
600 p_action_code => 0 ,
601 px_REPAIR_HISTORY_ID => p_rep_hist_id,
602 p_OBJECT_VERSION_NUMBER => null, -- travi ovn validation
603 p_REQUEST_ID => null,
604 p_PROGRAM_ID => null,
605 p_PROGRAM_APPLICATION_ID => null,
606 p_PROGRAM_UPDATE_DATE => null,
607 p_CREATED_BY => -1,
608 p_CREATION_DATE => sysdate,
609 p_LAST_UPDATED_BY => -1,
610 p_LAST_UPDATE_DATE => sysdate,
611 p_REPAIR_LINE_ID => l_repair_line_id,
612 p_EVENT_CODE => 'RR',
613 p_EVENT_DATE => I.received_date,
614 p_QUANTITY => I.received_quantity,
615 p_PARAMN1 => i.transaction_id,
616 p_PARAMN2 => i.rma_line_number,
617 p_PARAMN3 => i.line_type_id,
618 p_PARAMN4 => l_txn_billing_type_id,
619 p_PARAMN5 => i.who_col,
620 p_PARAMN6 => i.rma_header_id,
621 p_PARAMN7 => null,
622 p_PARAMN8 => null,
623 p_PARAMN9 => null,
624 p_PARAMN10 => null,
625 p_PARAMC1 => i.subinventory,
626 p_PARAMC2 => i.rma_number,
627 p_PARAMC3 => i.Organization_Name, -- Bug No 2760279
628 p_PARAMC4 => null,
629 p_PARAMC5 => null,
630 p_PARAMC6 => null,
631 p_PARAMC7 => null,
632 p_PARAMC8 => null,
633 p_PARAMC9 => null,
634 p_PARAMC10 => null,
635 p_PARAMD1 => null,
636 p_PARAMD2 => null,
637 p_PARAMD3 => null,
638 p_PARAMD4 => null,
639 p_PARAMD5 => null,
640 p_PARAMD6 => null,
641 p_PARAMD7 => null,
642 p_PARAMD8 => null,
643 p_PARAMD9 => null,
644 p_PARAMD10 => null,
645 p_ATTRIBUTE_CATEGORY => null,
646 p_ATTRIBUTE1 => null,
647 p_ATTRIBUTE2 => null,
648 p_ATTRIBUTE3 => null,
649 p_ATTRIBUTE4 => null,
650 p_ATTRIBUTE5 => null,
651 p_ATTRIBUTE6 => null,
652 p_ATTRIBUTE7 => null,
653 p_ATTRIBUTE8 => null,
654 p_ATTRIBUTE9 => null,
655 p_ATTRIBUTE10 => null,
656 p_ATTRIBUTE11 => null,
657 p_ATTRIBUTE12 => null,
658 p_ATTRIBUTE13 => null,
659 p_ATTRIBUTE14 => null,
660 p_ATTRIBUTE15 => null,
661 p_LAST_UPDATE_LOGIN => null,
662 X_Return_Status => l_return_status ,
663 X_Msg_Count => l_msg_count,
664 X_Msg_Data => l_msg_data
665 );
666
667
668 IF (g_debug > 0 ) THEN
669 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
670 END IF;
671
672
673 IF (g_debug > 0 ) THEN
674 csd_gen_utility_pvt.add('Successfully completed Depot RMA receipt update ');
675 END IF;
676
677 -- travi comment to concurrent log
678 fnd_file.put_line(fnd_file.log, 'Successfully completed Depot RMA receipt update');
679
680 commit;
681
682 v_total_records := v_total_records + 1;
683
684 End if;
685
686 End loop;
687
688 fnd_message.set_name('CSD','CSD_DRC_WIP_TOT_REC_PROC');
689 fnd_message.set_token('TOT_REC',to_char(v_total_records));
690 IF (g_debug > 0 ) THEN
691 csd_gen_utility_pvt.add(fnd_message.get);
692 END IF;
693
694
695 -- travi check for call from tools
696 if ( p_repair_line_id is not null ) then
697 IF (g_debug > 0 ) THEN
698 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_RMA_RECEIPTS : Call from Depot Repair Form Tools Menu');
699 END IF;
700
701 IF (g_debug > 0 ) THEN
702 csd_gen_utility_pvt.add('Repair Line ID ='||to_char(p_repair_line_id));
703 END IF;
704
705 IF (g_debug > 0 ) THEN
706 csd_gen_utility_pvt.add('Total Records inserted into Repairs History Table ='||to_char(v_total_records));
707 END IF;
708
709 end if;
710
711 End;
712
713 -------------------------------------------
714 -- Get WIP Job Completed Quantity
715 -------------------------------------------
716
717 procedure get_wip_job_completed_quantity(p_wip_entity_id in number,
718 x_wip_completed_qty OUT NOCOPY number,
719 x_COMPLETION_SUBINVENTORY OUT NOCOPY varchar2,
720 x_DATE_COMPLETED OUT NOCOPY date,
721 x_ORGANIZATION_ID OUT NOCOPY number,
722 x_routing_reference_id OUT NOCOPY number,
723 x_LAST_UPDATED_BY OUT NOCOPY number)
724
725 is
726
727 v_quantity_completed number;
728 p_old_complete number;
729 v_wip_entity_id number;
730
731
732 Begin
733
734 Begin
735 Select WIP_ENTITY_ID,
736 QUANTITY_COMPLETED,
737 COMPLETION_SUBINVENTORY,
738 DATE_COMPLETED,
739 ORGANIZATION_ID,
740 routing_reference_id,
741 LAST_UPDATED_BY
742 into v_wip_entity_id,
743 x_wip_completed_qty,
744 x_COMPLETION_SUBINVENTORY,
745 x_DATE_COMPLETED,
746 x_ORGANIZATION_ID,
747 x_routing_reference_id,
748 x_LAST_UPDATED_BY
749 from WIP_DISCRETE_JOBS
750 where WIP_ENTITY_ID=p_WIP_ENTITY_ID;
751 Exception
752 When no_data_found then
753 IF (g_debug > 0 ) THEN
754 csd_gen_utility_pvt.add('No WIP Job found for the wip_entity_id '||TO_CHAR(p_WIP_ENTITY_ID));
755 END IF;
756
757 when others then
758 IF (g_debug > 0 ) THEN
759 csd_gen_utility_pvt.add('when other exception at - get_wip_job_completed_quantity');
760 END IF;
761
762 End;
763
764
765 End;
766
767
768
769 --------------------------------------
770 -- Depot WIP Update
771 --------------------------------------
772 procedure depot_wip_update
773 (errbuf OUT NOCOPY varchar2,
774 retcode OUT NOCOPY varchar2,
775 p_repair_line_id in number)
776 is
777 v_total_rec number;
778 p_rep_hist_id number;
779 v_remaining_qty number;
780 v_transaction_quantity number;
781 v_old_wip_entity_id number;
782 v_wip_entity_name varchar2(100);
783
784 v_wei number; -- travi new
785 v_wen varchar2(100); -- travi new
786
787 p_wip_entity_id number;
788 x_wip_completed_qty number;
789 x_COMPLETION_SUBINVENTORY varchar2(30);
790 x_DATE_COMPLETED date;
791 x_ORGANIZATION_ID number;
792 x_routing_reference_id number;
793 x_LAST_UPDATED_BY number;
794
795 l_return_status varchar2(1);
796 l_msg_count number;
797 l_msg_data varchar2(2000);
798 v_new_completion_quantity number;
799 SumOfROCompQty number;
800 v_completed_qty number;
801
802 -- travi change get the group_id
803 Cursor REPAIR_JOBS( p_repair_line_id number )
804 is
805 SELECT CRJ.REPAIR_JOB_XREF_ID,
806 CRJ.WIP_ENTITY_ID,
807 CRJ.GROUP_ID,
808 CRJ.REPAIR_LINE_ID,
809 csr.repair_number,
810 crj.quantity_completed,
811 crj.quantity,
812 csr.promise_date,
813 crj.organization_id
814 from CSD_REPAIR_JOB_XREF CRJ
815 ,CSD_REPAIRS csr
816 where
817 csr.repair_line_id = crj.repair_line_id
818 and
819 nvl(crj.quantity_completed,0) < crj.quantity
820 and csr.repair_line_id = nvl(p_repair_line_id, csr.repair_line_id) -- travi 121801 change
821 order by crj.wip_entity_id, csr.promise_date;
822 --,csr.promise_date; -- travi change
823 -- travi change for update;
824 -- if you do for update then your update should be where current of cursor
825
826 Begin
827
828 v_total_rec := 0;
829 v_old_wip_entity_id := -1000;
830
831 IF (g_debug > 0 ) THEN
832 csd_gen_utility_pvt.add('At the begin of Depot Repair WIP Job update');
833 END IF;
834
835
836 -- travi code to update wip_entity_id for the repair_job_xref
837 For K in Repair_Jobs( p_repair_line_id )
838 loop
839
840 if(K.WIP_ENTITY_ID = K.GROUP_ID) then
841
842 v_wen := 'CSD'||K.GROUP_ID;
843
844 Begin
845 Select wip_entity_id
846 into v_wei
847 from wip_entities
848 where wip_entity_name = v_wen
849 and wip_entities.organization_id = K.organization_id;---- 0430 bug number- sangita to fix duplicate wip name problem.
850 Exception
851 When no_data_found then
852 v_wei := NULL;
853 IF (g_debug > 0 ) THEN
854 csd_gen_utility_pvt.add('Invalid WIP_ENTITY_NAME : '||v_wen);
855 END IF;
856
857 when others then
858 IF (g_debug > 0 ) THEN
859 csd_gen_utility_pvt.add('Others exception WIP_ENTITY_NAME : '||v_wen);
860 END IF;
861
862 End;
863
864 IF (g_debug > 0 ) THEN
865 csd_gen_utility_pvt.add('Updating csd_repair_job_xref for wip_entity_name : '||v_wen);
866 END IF;
867
868 IF (g_debug > 0 ) THEN
869 csd_gen_utility_pvt.add('wip_entity_id ='||TO_CHAR(K.wip_entity_id));
870 END IF;
871
872 -- Updating Null Value for v_wei when WIP Mass load program is not completed
873 -- so should not update when v_wei is null bug# 2770713 saupadhy
874 Begin
875 update csd_repair_job_xref
876 set wip_entity_id = v_wei
877 where repair_line_id = K.repair_line_id
878 and repair_job_xref_id = K.repair_job_xref_id
879 and v_wei is not null;
880 Exception
881 when others then
882 null;
883 End;
884
885 end if;
886
887 end loop;
888 -- end of travi code
889
890 For I in Repair_Jobs( p_repair_line_id )
891 loop
892
893 IF (g_debug > 0 ) THEN
894 csd_gen_utility_pvt.add('-------------------------------------------');
895 csd_gen_utility_pvt.add('wip_entity_id ='||TO_CHAR(i.wip_entity_id));
896 csd_gen_utility_pvt.add('repair_line_id ='||TO_CHAR(i.repair_line_id));
897 csd_gen_utility_pvt.add('quantity_completed ='||TO_CHAR(i.quantity_completed));
898 csd_gen_utility_pvt.add('quantity ='||TO_CHAR(i.quantity));
899 END IF;
900
901 if i.wip_entity_id <> v_old_wip_entity_id then
902 -- get wip_comp_qty for the wip_entity_id
903 get_wip_job_completed_quantity(i.wip_entity_id,x_wip_completed_qty,x_completion_subinventory,
904 x_date_completed,x_organization_id,x_routing_reference_id,x_last_updated_by);
905
906
907 IF (g_debug > 0 ) THEN
908 csd_gen_utility_pvt.add('x_wip_completed_qty ='||TO_CHAR(x_wip_completed_qty));
909 END IF;
910
911 -- get SIGMA ro_completed_qty
912 Select nvl(sum(quantity_completed),0) into SumOfROCompQty from csd_repair_job_xref where wip_entity_id = i.wip_entity_id;
913
914 v_transaction_quantity := nvl(x_wip_completed_qty,0) - nvl(SumOfROCompQty,0);
915 if (v_transaction_quantity + nvl(i.quantity_completed,0)) > nvl(i.quantity,0) then
916 v_transaction_quantity := nvl(i.quantity,0) - nvl(i.quantity_completed,0);
917 end if;
918 IF (g_debug > 0 ) THEN
919 csd_gen_utility_pvt.add(' i.quantity_complted ='||TO_CHAR( i.quantity_completed));
920 END IF;
921 IF (g_debug > 0 ) THEN
922 csd_gen_utility_pvt.add(' v_transaction_quantity ='||TO_CHAR( v_transaction_quantity));
923 END IF;
924 v_completed_qty := nvl(i.quantity_completed,0) + v_transaction_quantity;
925 if (v_completed_qty > i.quantity) then
926 v_completed_qty := i.quantity;
927 END if;
928
929 Begin
930 Select wip_entity_name
931 into v_wip_entity_name
932 from wip_entities
933 where wip_entity_id = i.wip_entity_id;
934 Exception
935 When no_data_found then
936 fnd_message.set_name('CSD','CSD_INVALID_WIP_ENTITY');
937 fnd_message.set_token('REP_NO',i.repair_number);
938 fnd_message.set_token('WIP_JOB_ID',i.wip_entity_id);
939 if (g_debug > 0) then
940 csd_gen_utility_pvt.add(fnd_message.get);
941 end if;
942 v_completed_qty := 0;
943 when others then
944 if (g_debug > 0) then
945 csd_gen_utility_pvt.add('When others exception at - Wip entity name');
946 end if;
947 End;
948 end if;
949 /*
950 -- get SIGMA ro_completed_qty
951 Select nvl(sum(quantity_completed),0) into SumOfROCompQty from csd_repair_job_xref where wip_entity_id = i.wip_entity_id;
952
953 v_transaction_quantity := nvl(x_wip_completed_qty,0) - nvl(SumOfROCompQty,0);
954 if (v_transaction_quantity + nvl(i.quantity_completed,0)) > nvl(i.quantity,0) then
955 v_transaction_quantity := nvl(i.quantity,0) - nvl(i.quantity_completed,0);
956 end if;
957 IF (g_debug > 0 ) THEN
958 csd_gen_utility_pvt.add(' i.quantity_complted ='||TO_CHAR( i.quantity_completed));
959
960 END IF;
961 IF (g_debug > 0 ) THEN
962 csd_gen_utility_pvt.add(' v_transaction_quantity ='||TO_CHAR( v_transaction_quantity));
963 END IF;
964 v_completed_qty := nvl(i.quantity_completed,0) + v_transaction_quantity;
965 if (v_completed_qty > i.quantity) then
966 v_completed_qty := i.quantity;
967 END if;
968 */
969 if (v_transaction_quantity > 0) then --0430
970 update csd_repair_job_xref
971 set quantity_completed =v_completed_qty
972 where repair_line_id = i.repair_line_id
973 and repair_job_xref_id = i.repair_job_xref_id;
974
975 fnd_message.set_name('CSD','CSD_DRC_WIP_JOB_UPDATE');
976 fnd_message.set_token('REP_NO',i.repair_number);
977 fnd_message.set_token('WIP_JOB',v_wip_entity_name);
978 fnd_message.set_token('QTY_COMPLETE',to_char(v_transaction_quantity));
979 IF (g_debug > 0 ) THEN
980 csd_gen_utility_pvt.add(fnd_message.get);
981 END IF;
982
983
984 v_total_rec := v_total_rec + 1;
985
986 IF (g_debug > 0 ) THEN
987 csd_gen_utility_pvt.add('Calling CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write');
988 END IF;
989
990
991 IF (g_debug > 0 ) THEN
992 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_wip_update before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for JC event');
993 END IF;
994
995
996 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write (
997 P_Api_Version_Number => 1.0,
998 P_Init_Msg_List => 'F',
999 P_Commit => 'F',
1000 p_validation_level => null,
1001 p_action_code => 0 ,
1002 px_REPAIR_HISTORY_ID => p_rep_hist_id,
1003 p_OBJECT_VERSION_NUMBER => null, -- travi ovn validation
1004 p_REQUEST_ID => null,
1005 p_PROGRAM_ID => null,
1006 p_PROGRAM_APPLICATION_ID => null,
1007 p_PROGRAM_UPDATE_DATE => null,
1008 p_CREATED_BY => -1,
1009 p_CREATION_DATE => sysdate,
1010 p_LAST_UPDATED_BY => -1,
1011 p_LAST_UPDATE_DATE => sysdate,
1012 p_REPAIR_LINE_ID => I.repair_line_id,
1013 p_EVENT_CODE => 'JC',
1014 p_EVENT_DATE => nvl(x_date_completed,sysdate),
1015 p_QUANTITY => v_transaction_quantity,
1016 p_PARAMN1 => x_organization_id,
1017 p_PARAMN2 => x_routing_reference_id,
1018 p_PARAMN3 => null,
1019 p_PARAMN4 => i.wip_entity_id,
1020 p_PARAMN5 => null,
1021 p_PARAMN6 => null,
1022 p_PARAMN7 => null,
1023 p_PARAMN8 => null,
1024 p_PARAMN9 => null,
1025 p_PARAMN10 => null,
1026 p_PARAMC1 => x_completion_subinventory,
1027 p_PARAMC2 => v_wip_entity_name,
1028 p_PARAMC3 => null,
1029 p_PARAMC4 => null,
1030 p_PARAMC5 => null,
1031 p_PARAMC6 => null,
1032 p_PARAMC7 => null,
1033 p_PARAMC8 => null,
1034 p_PARAMC9 => null,
1035 p_PARAMC10 => null,
1036 p_PARAMD1 => x_date_completed,
1037 p_PARAMD2 => null,
1038 p_PARAMD3 => null,
1039 p_PARAMD4 => null,
1040 p_PARAMD5 => null,
1041 p_PARAMD6 => null,
1042 p_PARAMD7 => null,
1043 p_PARAMD8 => null,
1044 p_PARAMD9 => null,
1045 p_PARAMD10 => null,
1046 p_ATTRIBUTE_CATEGORY => null,
1047 p_ATTRIBUTE1 => null,
1048 p_ATTRIBUTE2 => null,
1049 p_ATTRIBUTE3 => null,
1050 p_ATTRIBUTE4 => null,
1051 p_ATTRIBUTE5 => null,
1052 p_ATTRIBUTE6 => null,
1053 p_ATTRIBUTE7 => null,
1054 p_ATTRIBUTE8 => null,
1055 p_ATTRIBUTE9 => null,
1056 p_ATTRIBUTE10 => null,
1057 p_ATTRIBUTE11 => null,
1058 p_ATTRIBUTE12 => null,
1059 p_ATTRIBUTE13 => null,
1060 p_ATTRIBUTE14 => null,
1061 p_ATTRIBUTE15 => null,
1062 p_LAST_UPDATE_LOGIN => null,
1063 X_Return_Status => l_return_status ,
1064 X_Msg_Count => l_msg_count,
1065 X_Msg_Data => l_msg_data
1066 );
1067
1068 IF (g_debug > 0 ) THEN
1069 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_wip_update after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
1070 END IF;
1071
1072
1073
1074 IF (g_debug > 0 ) THEN
1075 csd_gen_utility_pvt.add('Successfully completed Depot Repair WIP Job Update');
1076 END IF;
1077
1078
1079 End if;
1080
1081
1082 v_old_wip_entity_id := i.wip_entity_id;
1083
1084 End loop;
1085
1086 commit;
1087
1088 fnd_message.set_name('CSD','CSD_DRC_WIP_REC_PROC');
1089 fnd_message.set_token('TOT_REC',to_char(v_total_rec));
1090 IF (g_debug > 0 ) THEN
1091 csd_gen_utility_pvt.add(fnd_message.get);
1092 END IF;
1093
1094
1095 if ( p_repair_line_id is not null ) then
1096 IF (g_debug > 0 ) THEN
1097 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_WIP_UPDATE : Call from Depot Repair Form Tools Menu');
1098 END IF;
1099
1100 IF (g_debug > 0 ) THEN
1101 csd_gen_utility_pvt.add('Repair Line ID ='||to_char(p_repair_line_id));
1102 END IF;
1103
1104 IF (g_debug > 0 ) THEN
1105 csd_gen_utility_pvt.add('Total Records inserted into Repairs History Table ='||to_char(v_total_rec));
1106 END IF;
1107
1108 end if;
1109
1110 End;
1111
1112 ---------------------------------------
1113
1114 ---------------------------------------
1115 -- Depot Shipment Update
1116 ---------------------------------------
1117
1118 procedure depot_shipment_update
1119 (errbuf OUT NOCOPY varchar2,
1120 retcode OUT NOCOPY varchar2,
1121 p_repair_line_id in number)
1122 is
1123
1124 v_total_records number;
1125
1126 Cursor DEPOT_SHIPMENT_LINES ( p_repair_line_id number) is
1127 select /*+ index(cra CSD_REPAIRS_U1) */
1128 dd.serial_number sl_number, -- travi 012502
1129 cra.quantity qty, -- travi 012502
1130 dd.lot_number lot_number, --vijay 02/03/2003
1131 dd.revision revision, --vijay 02/03/2003
1132 dd.subinventory subinv, --vijay 02/03/2003
1133 oeh.order_number order_number,
1134 oeh.header_id sales_order_header,
1135 oel.line_number order_line_number,
1136 oel.line_type_id,
1137 cra.repair_number,
1138 cra.repair_line_id,
1139 ced.txn_billing_type_id,
1140 dd.requested_quantity,
1141 dd.shipped_quantity,
1142 dl.initial_pickup_date date_shipped,
1143 dd.delivery_detail_id,
1144 dd.requested_quantity_uom shipped_uom_code,
1145 mtlu.unit_of_measure shipped_uom,
1146 dd.inventory_item_id ,
1147 dd.organization_id
1148 from
1149 wsh_new_deliveries dl,
1150 wsh_delivery_assignments da,
1151 wsh_delivery_details dd ,
1152 oe_order_headers_all oeh,
1153 oe_order_lines_all oel,
1154 csd_Repairs cra,
1155 cs_estimate_Details ced,
1156 mtl_units_of_measure mtlu
1157 Where ((cra.repair_line_id = ced.original_source_id
1158 and ced.original_source_code = 'DR') OR
1159 (cra.repair_line_id = ced.source_id
1160 and ced.source_code = 'DR'))
1161 and dd.delivery_detail_id = da.delivery_detail_id
1162 and da.delivery_id = dl.delivery_id(+)
1163 and ced.order_header_id = oeh.header_id
1164 and ced.order_line_id = oel.line_id
1165 and ced.order_header_id = oel.header_id
1166 and dd.source_header_id = ced.order_header_id
1167 and dd.source_line_id = ced.order_line_id
1168 and dd.released_status = 'C' -- travi 022002
1169 and dd.delivery_detail_id not in
1170 (select paramn1
1171 from csd_Repair_history
1172 where repair_line_id = cra.repair_line_id
1173 and event_code='PS')
1174 and mtlu.uom_code = dd.requested_quantity_uom
1175 and cra.repair_line_id = nvl(p_repair_line_id, cra.repair_line_id);
1176
1177
1178 v_repair_history_id number;
1179 l_return_status varchar2(1);
1180 l_msg_count number;
1181 l_msg_data varchar2(2000);
1182 l_rep_hist_id number;
1183 v_skip_record boolean;
1184 v_depot_Repair_flag varchar2(1);
1185 v_transaction_type_id number;
1186 p_result_ship_quantity number;
1187
1188 l_pt_serial_num varchar2(30);
1189
1190 Cursor c_prd_txn_serial_num ( p_rep_line_id number ) is
1191 select nvl(cpt.serial_number, dra.serial_number) serial_number
1192 from csd_product_txns_v cpt
1193 , csd_repairs dra
1194 where action_type = 'SHIP'
1195 and dra.repair_line_id = p_rep_line_id
1196 and cpt.repair_line_id = dra.repair_line_id
1197 and nvl(cpt.serial_number_control_code,1) > 1;
1198
1199 Begin
1200
1201 v_total_records := 0;
1202
1203 IF (g_debug > 0 ) THEN
1204 csd_gen_utility_pvt.add('At the begin of Depot repair Shipping Update');
1205 END IF;
1206
1207
1208 For I in depot_shipment_lines(p_repair_line_id)
1209 loop
1210
1211
1212 IF (g_debug > 0 ) THEN
1213 csd_gen_utility_pvt.add('-------------------------------------------');
1214 csd_gen_utility_pvt.add('Order number ='||TO_CHAR(I.order_number));
1215 csd_gen_utility_pvt.add('Repair number ='||I.repair_number);
1216 csd_gen_utility_pvt.add('Txn billing type id ='||TO_CHAR(I.txn_billing_type_id));
1217 csd_gen_utility_pvt.add('Shipped quantity ='||TO_CHAR(I.shipped_quantity));
1218 csd_gen_utility_pvt.add('Inventory item id ='||TO_CHAR(I.inventory_item_id));
1219 csd_gen_utility_pvt.add('Organization id ='||TO_CHAR(I.Organization_id));
1220 END IF;
1221
1222
1223 v_skip_record := false;
1224 v_transaction_type_id := null;
1225 v_depot_Repair_flag := null;
1226
1227 Begin
1228
1229 Select transaction_type_id
1230 into v_transaction_type_id
1231 from cs_txn_billing_types
1232 where txn_billing_type_id = i.txn_billing_type_id;
1233
1234 Exception
1235 When no_data_found then
1236 v_transaction_type_id := null;
1237 v_skip_record := true;
1238 IF (g_debug > 0 ) THEN
1239 csd_gen_utility_pvt.add('Transaction type id not found for billing type id ='||TO_CHAR(i.txn_billing_type_id));
1240 END IF;
1241
1242 when others then
1243 IF (g_debug > 0 ) THEN
1244 csd_gen_utility_pvt.add('When others exception at - Transaction type id');
1245 END IF;
1246
1247 End;
1248
1249
1250 if v_transaction_type_id is not null then
1251 Begin
1252 Select depot_Repair_flag
1253 into v_depot_repair_flag
1254 from cs_transaction_types_b
1255 where transaction_type_id = v_transaction_type_id;
1256 Exception
1257 when no_Data_found then
1258 V_skip_record := true;
1259 IF (g_debug > 0 ) THEN
1260 csd_gen_utility_pvt.add('Depot repair flag is not Y ');
1261 END IF;
1262
1263 when others then
1264 IF (g_debug > 0 ) THEN
1265 csd_gen_utility_pvt.add('When others exception at - depot repair flag');
1266 END IF;
1267
1268 End;
1269 End if;
1270
1271 if v_depot_repair_flag = 'Y' then
1272 v_skip_record := false;
1273 else
1274 v_skip_record := true;
1275 End if;
1276
1277 -- Added jkuruvil to skip,display records with null shipped date
1278 IF I.date_shipped is null then
1279 fnd_message.set_name('CSD','CSD_DRC_SHIP_PICKUP_DATE_PROC');
1280 fnd_message.set_token('ORDER_NO',I.order_number);
1281 fnd_message.set_token('REP_NO',I.repair_number);
1282 fnd_message.set_token('QTY_SHIP',to_char(I.shipped_quantity));
1283 fnd_message.set_token('DT_SHIP',to_char(I.date_shipped));
1284 IF (g_debug > 0 ) THEN
1285 csd_gen_utility_pvt.add(fnd_message.get);
1286 END IF;
1287
1288 fnd_message.clear;
1289 v_skip_record := true;
1290 End if;
1291
1292 if not v_skip_record then
1293
1294 BEGIN
1295
1296 if (i.qty = 1) then
1297
1298 open c_prd_txn_serial_num ( i.repair_line_id );
1299
1300 fetch c_prd_txn_serial_num into l_pt_serial_num;
1301
1302 if (c_prd_txn_serial_num%FOUND) then
1303
1304 -- check if serial numbers are different
1305 IF (g_debug > 0 ) THEN
1306 csd_gen_utility_pvt.add('Checking whether serial numbers are changed');
1307 END IF;
1308
1309
1310 if(l_pt_serial_num <> i.sl_number) then
1311
1312
1313 IF (g_debug > 0 ) THEN
1314 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_shipment_update before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for SSC event');
1315 csd_gen_utility_pvt.add('Calling CSD_TO_FORM_REPAIR_HISTORY');
1316 END IF;
1317
1318
1319 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write (
1320 P_Api_Version_Number => 1.0,
1321 P_Init_Msg_List => 'F',
1322 P_Commit => 'F',
1323 p_validation_level => null,
1324 p_action_code => 0 ,
1325 px_REPAIR_HISTORY_ID => l_rep_hist_id,
1326 p_OBJECT_VERSION_NUMBER => null, -- travi ovn validation
1327 p_REQUEST_ID => null,
1328 p_PROGRAM_ID => null,
1329 p_PROGRAM_APPLICATION_ID => null,
1330 p_PROGRAM_UPDATE_DATE => null,
1331 p_CREATED_BY => -1,
1332 p_CREATION_DATE => sysdate,
1333 p_LAST_UPDATED_BY => -1,
1334 p_LAST_UPDATE_DATE => sysdate,
1335 p_REPAIR_LINE_ID => I.repair_line_id,
1336 p_EVENT_CODE => 'SSC', -- Ship serial number changed
1337 p_EVENT_DATE => I.date_shipped,
1338 p_QUANTITY => p_result_ship_quantity,
1339 p_PARAMN1 => i.delivery_detail_id,
1340 p_PARAMN2 => i.order_line_number,
1341 p_PARAMN3 => i.line_type_id,
1342 p_PARAMN4 => i.txn_billing_type_id,
1343 p_PARAMN5 => null,
1344 p_PARAMN6 => null,
1345 p_PARAMN7 => null,
1346 p_PARAMN8 => null,
1347 p_PARAMN9 => null,
1348 p_PARAMN10 => null,
1349 p_PARAMC1 => null,
1350 p_PARAMC2 => i.order_number,
1351 p_PARAMC3 => l_pt_serial_num, -- prd txn ser num
1352 p_PARAMC4 => i.sl_number, -- WDD ship ser num
1353 p_PARAMC5 => null,
1354 p_PARAMC6 => null,
1355 p_PARAMC7 => null,
1356 p_PARAMC8 => null,
1357 p_PARAMC9 => null,
1358 p_PARAMC10 => null,
1359 p_PARAMD1 => null,
1360 p_PARAMD2 => null,
1361 p_PARAMD3 => null,
1362 p_PARAMD4 => null,
1363 p_PARAMD5 => null,
1364 p_PARAMD6 => null,
1365 p_PARAMD7 => null,
1366 p_PARAMD8 => null,
1367 p_PARAMD9 => null,
1368 p_PARAMD10 => null,
1369 p_ATTRIBUTE_CATEGORY => null,
1370 p_ATTRIBUTE1 => null,
1371 p_ATTRIBUTE2 => null,
1372 p_ATTRIBUTE3 => null,
1373 p_ATTRIBUTE4 => null,
1374 p_ATTRIBUTE5 => null,
1375 p_ATTRIBUTE6 => null,
1376 p_ATTRIBUTE7 => null,
1377 p_ATTRIBUTE8 => null,
1378 p_ATTRIBUTE9 => null,
1379 p_ATTRIBUTE10 => null,
1380 p_ATTRIBUTE11 => null,
1381 p_ATTRIBUTE12 => null,
1382 p_ATTRIBUTE13 => null,
1383 p_ATTRIBUTE14 => null,
1384 p_ATTRIBUTE15 => null,
1385 p_LAST_UPDATE_LOGIN => null,
1386 X_Return_Status => l_return_status,
1387 X_Msg_Count => l_msg_count,
1388 X_Msg_Data => l_msg_data
1389 );
1390
1391 IF (g_debug > 0 ) THEN
1392 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_shipment_update after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
1393 END IF;
1394
1395 v_total_records := v_total_records + 1;
1396
1397 IF (g_debug > 0 ) THEN
1398 csd_gen_utility_pvt.add('Successfully updated the history');
1399 END IF;
1400
1401
1402 IF (g_debug > 0 ) THEN
1403 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_SHIPMENT_UPDATE - Repair Line ID : '||to_char(i.repair_line_id));
1404 csd_gen_utility_pvt.add('Inserted into Repairs History Table for Serial number Change');
1405 csd_gen_utility_pvt.add('Product Txn Serial number : '||l_pt_serial_num||' Ship Serial number : '||i.sl_number);
1406 END IF;
1407
1408
1409 end if;
1410 end if;
1411 end if;
1412
1413 Exception
1414 When no_data_found then
1415 IF (g_debug > 0 ) THEN
1416 csd_gen_utility_pvt.add('No data found exception,in check for serial number change');
1417 END IF;
1418
1419 When others then
1420 IF (g_debug > 0 ) THEN
1421 csd_gen_utility_pvt.add('When others exception,in check for serial number change');
1422 END IF;
1423
1424 END;
1425
1426 end if;
1427 -- travi 012502
1428
1429 if not v_skip_record then
1430
1431 IF (g_debug > 0 ) THEN
1432 csd_gen_utility_pvt.add('Calling the convert to primary uom ');
1433 END IF;
1434
1435
1436 csd_depot_repair_cntr.convert_to_primary_uom
1437 (i.inventory_item_id,
1438 i.organization_id,
1439 i.shipped_uom,
1440 i.shipped_quantity,
1441 p_result_ship_quantity);
1442
1443 update csd_repairs
1444 set quantity_shipped = nvl(quantity_shipped,0)+nvl(p_result_ship_quantity,0)
1445 where repair_line_id = I.repair_line_id;
1446
1447 --Vijay 2/3/03 Begin
1448 update csd_product_transactions
1449 set sub_inventory = i.subinv,
1450 lot_number = i.lot_number
1451 where
1452 repair_line_id = i.repair_line_id;
1453
1454 --Vijay 2/3/03 End
1455
1456 IF (g_debug > 0 ) THEN
1457 csd_gen_utility_pvt.add('Updated csd_repairs table');
1458 END IF;
1459
1460
1461 fnd_message.set_name('CSD','CSD_DRC_QTY_SHIPPED');
1462 fnd_message.set_token('ORDER_NO',i.order_number);
1463 fnd_message.set_token('REP_NO',i.repair_number);
1464 fnd_message.set_token('QTY_SHIP',to_char(p_result_ship_quantity));
1465 IF (g_debug > 0 ) THEN
1466 csd_gen_utility_pvt.add(fnd_message.get);
1467 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_shipment_update before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for PS event');
1468 csd_gen_utility_pvt.add('Calling CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write');
1469 END IF;
1470
1471
1472 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write (
1473 P_Api_Version_Number => 1.0,
1474 P_Init_Msg_List => 'F',
1475 P_Commit => 'F',
1476 p_validation_level => null,
1477 p_action_code => 0 ,
1478 px_REPAIR_HISTORY_ID => l_rep_hist_id,
1479 p_OBJECT_VERSION_NUMBER => null, -- travi ovn validation
1480 p_REQUEST_ID => null,
1481 p_PROGRAM_ID => null,
1482 p_PROGRAM_APPLICATION_ID => null,
1483 p_PROGRAM_UPDATE_DATE => null,
1484 p_CREATED_BY => -1,
1485 p_CREATION_DATE => sysdate,
1486 p_LAST_UPDATED_BY => -1,
1487 p_LAST_UPDATE_DATE => sysdate,
1488 p_REPAIR_LINE_ID => I.repair_line_id,
1489 p_EVENT_CODE => 'PS',
1490 p_EVENT_DATE => I.date_shipped,
1491 p_QUANTITY => p_result_ship_quantity,
1492 p_PARAMN1 => i.delivery_detail_id,
1493 p_PARAMN2 => i.order_line_number,
1494 p_PARAMN3 => i.line_type_id,
1495 p_PARAMN4 => i.txn_billing_type_id,
1496 p_PARAMN5 => null,
1497 p_PARAMN6 => null,
1498 p_PARAMN7 => null,
1499 p_PARAMN8 => null,
1500 p_PARAMN9 => null,
1501 p_PARAMN10 => null,
1502 p_PARAMC1 => null,
1503 p_PARAMC2 => i.order_number,
1504 p_PARAMC3 => null,
1505 p_PARAMC4 => null,
1506 p_PARAMC5 => null,
1507 p_PARAMC6 => null,
1508 p_PARAMC7 => null,
1509 p_PARAMC8 => null,
1510 p_PARAMC9 => null,
1511 p_PARAMC10 => null,
1512 p_PARAMD1 => null,
1513 p_PARAMD2 => null,
1514 p_PARAMD3 => null,
1515 p_PARAMD4 => null,
1516 p_PARAMD5 => null,
1517 p_PARAMD6 => null,
1518 p_PARAMD7 => null,
1519 p_PARAMD8 => null,
1520 p_PARAMD9 => null,
1521 p_PARAMD10 => null,
1522 p_ATTRIBUTE_CATEGORY => null,
1523 p_ATTRIBUTE1 => null,
1524 p_ATTRIBUTE2 => null,
1525 p_ATTRIBUTE3 => null,
1526 p_ATTRIBUTE4 => null,
1527 p_ATTRIBUTE5 => null,
1528 p_ATTRIBUTE6 => null,
1529 p_ATTRIBUTE7 => null,
1530 p_ATTRIBUTE8 => null,
1531 p_ATTRIBUTE9 => null,
1532 p_ATTRIBUTE10 => null,
1533 p_ATTRIBUTE11 => null,
1534 p_ATTRIBUTE12 =>null,
1535 p_ATTRIBUTE13 => null,
1536 p_ATTRIBUTE14 => null,
1537 p_ATTRIBUTE15 => null,
1538 p_LAST_UPDATE_LOGIN => null,
1539 X_Return_Status => l_return_status ,
1540 X_Msg_Count => l_msg_count,
1541 X_Msg_Data => l_msg_data
1542 );
1543
1544 IF (g_debug > 0 ) THEN
1545 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_shipment_update after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
1546 csd_gen_utility_pvt.add('Successfully completed Depot repair Shipping Update');
1547 END IF;
1548
1549 commit;
1550
1551 v_total_records := v_total_records + 1;
1552
1553 End if;
1554
1555 End loop;
1556
1557 fnd_message.set_name('CSD','CSD_DRC_SHIP_TOTAL_REC_PROC');
1558 fnd_message.set_token('TOT_REC',to_char(v_total_records));
1559 IF (g_debug > 0 ) THEN
1560 csd_gen_utility_pvt.add(fnd_message.get);
1561 END IF;
1562
1563
1564 if ( p_repair_line_id is not null ) then
1565 IF (g_debug > 0 ) THEN
1566 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_SHIPMENT_UPDATE : Call from Depot Repair Form Tools Menu');
1567 csd_gen_utility_pvt.add('Repair Line ID ='||to_char(p_repair_line_id));
1568 csd_gen_utility_pvt.add('Total Records inserted into Repairs History Table ='||to_char(v_total_records));
1569 END IF;
1570
1571 end if;
1572
1573
1574 End;
1575
1576 -- travi changes
1577 ------------------------------------------------------------------
1578 -- procedure name: depot_update_task_hist
1579 -- description : procedure used to Update Repair Order history
1580 -- for task creation from concurrent program
1581 ------------------------------------------------------------------
1582 PROCEDURE depot_update_task_hist
1583 (
1584 errbuf OUT NOCOPY varchar2,
1585 retcode OUT NOCOPY number,
1586 p_repair_line_id in number
1587 )
1588 is
1589 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_AND_WRITE';
1590 l_api_version CONSTANT NUMBER := 1.0;
1591 l_msg_count NUMBER;
1592 l_msg_data VARCHAR2(2000);
1593 l_msg_index NUMBER;
1594
1595 x_return_status VARCHAR2(1);
1596 x_msg_count NUMBER;
1597 x_msg_data VARCHAR2(2000);
1598
1599 l_return_status VARCHAR2(1);
1600 l_repair_history_id NUMBER;
1601
1602 v_total_records number;
1603 l_event_code VARCHAR2(30) := '';
1604
1605 l_task_id number;
1606 l_repair_line_id number;
1607 l_rep_hist_id number;
1608
1609 l_paramn1 NUMBER;
1610 l_paramn2 NUMBER;
1611 l_paramn3 NUMBER;
1612 l_paramn4 NUMBER;
1613 l_paramn5 NUMBER;
1614 l_paramc1 VARCHAR2(240);
1615 l_paramc2 VARCHAR2(240);
1616 l_paramc3 VARCHAR2(240);
1617 l_paramc4 VARCHAR2(240);
1618 l_paramc5 VARCHAR2(240);
1619 l_paramc6 VARCHAR2(240);
1620 l_paramd1 DATE;
1621 l_paramd2 DATE;
1622 l_paramd3 DATE;
1623 l_paramd4 DATE;
1624 l_owner VARCHAR2(240);
1625 l_task_status VARCHAR2(240);
1626
1627 CURSOR c_updated_tasks( p_repair_line_id number ) is
1628 select tsk.task_id
1629 ,rep.repair_line_id
1630 --,max(hist.repair_history_id) repair_history_id
1631 from csd_repair_tasks_v tsk
1632 ,csd_repair_history hist
1633 ,csd_repairs rep
1634 where rep.repair_line_id = tsk.source_object_id
1635 and tsk.source_object_id = hist.repair_line_id
1636 and tsk.task_id = hist.paramn1
1637 and ( tsk.task_status_id <> hist.paramn5 or tsk.owner_id <> hist.paramn3)
1638 and rep.repair_line_id = nvl(p_repair_line_id, rep.repair_line_id) -- travi 181201 change
1639 group by tsk.task_id, rep.repair_line_id;
1640
1641 CURSOR c_tasks_to_updt(l_task_id number, l_repair_line_id number, l_rep_hist_id number) is
1642 Select tsk.task_id, -- hist.paramn1
1643 tsk.last_updated_by, -- hist.paramn2
1644 tsk.owner_id, -- hist.paramn3
1645 tsk.assigned_by_id, -- hist.paramn4
1646 tsk.task_status_id, -- hist.paramn5
1647 tsk.task_number, -- hist.paramc1
1648 tsk.owner_type, -- hist.paramc2
1649 tsk.owner, -- hist.paramc3
1650 null assignee_type, -- hist.paramc4
1651 null assignee_name, -- hist.paramc5
1652 tsk.task_status, -- hist.paramc6
1653 tsk.planned_start_date, -- hist.paramd1
1654 tsk.actual_start_date, -- hist.paramd2
1655 tsk.actual_end_date, -- hist.paramd3
1656 tsk.last_update_date, -- hist.paramd4
1657 hist.paramc3, -- tsk.owner
1658 hist.paramc6 -- tsk.task_status
1659 from CSD_REPAIR_TASKS_V tsk
1660 ,csd_repair_history hist
1661 where tsk.source_object_type_code = 'DR'
1662 and tsk.task_id = l_task_id
1663 and tsk.source_object_id = l_repair_line_id
1664 and hist.repair_history_id = l_rep_hist_id
1665 and hist.paramn1 = tsk.task_id
1666 and hist.repair_line_id = tsk.source_object_id
1667 and ( tsk.task_status_id <> hist.paramn5 or tsk.owner_id <> hist.paramn3);
1668
1669 -- travi 020402 commented out old code
1670 -- tsk.assignee_id, -- hist.paramn4
1671 -- tsk.assignee_type, -- hist.paramc4
1672 -- tsk.assignee_name, -- hist.paramc5
1673
1674 BEGIN
1675
1676 v_total_records := 0;
1677 -- travi added p_repair_line_id
1678 FOR R in c_updated_tasks( p_repair_line_id )
1679 loop
1680
1681 l_event_code := '';
1682 l_task_id := '';
1683 l_repair_line_id := '';
1684 l_rep_hist_id := '';
1685 l_paramn1 := ''; -- task id
1686 l_paramn2 := ''; -- last updated by
1687 l_paramn3 := ''; -- owner id
1688 l_paramn4 := ''; -- assigned by id
1689 l_paramn5 := ''; -- status id
1690 l_paramc1 := ''; -- task number
1691 l_paramc2 := ''; -- owner type
1692 l_paramc3 := ''; -- owner name
1693 l_paramc4 := ''; -- null assignee type
1694 l_paramc5 := ''; -- null assignee name
1695 l_paramc6 := ''; -- status
1696 l_paramd1 := ''; -- planned start date
1697 l_paramd2 := ''; -- actual start date
1698 l_paramd3 := ''; -- actual end date
1699 l_paramd4 := ''; -- last updated date
1700 l_owner := ''; -- tsk.owner
1701 l_task_status := ''; -- tsk.task_status
1702
1703 select max(hist2.repair_history_id)
1704 into l_rep_hist_id
1705 from CSD_REPAIR_HISTORY hist2
1706 where hist2.repair_line_id = R.repair_line_id
1707 and hist2.paramn1 = R.task_id;
1708
1709 l_task_id := R.task_id;
1710 l_repair_line_id := R.repair_line_id;
1711
1712
1713 IF (l_rep_hist_id is not null) then
1714
1715 OPEN c_tasks_to_updt(l_task_id, l_repair_line_id, l_rep_hist_id);
1716
1717 FETCH c_tasks_to_updt
1718 INTO l_paramn1, -- task id
1719 l_paramn2, -- last updated by
1720 l_paramn3, -- owner id
1721 l_paramn4, -- assigned by id
1722 l_paramn5, -- status id
1723 l_paramc1, -- task number
1724 l_paramc2, -- owner type
1725 l_paramc3, -- owner name
1726 l_paramc4, -- null assignee type
1727 l_paramc5, -- null assignee name
1728 l_paramc6, -- status
1729 l_paramd1, -- planned start date
1730 l_paramd2, -- actual start date
1731 l_paramd3, -- actual end date
1732 l_paramd4, -- last updated date
1733 l_owner, -- tsk.owner
1734 l_task_status; -- -- tsk.task_status
1735
1736 CLOSE c_tasks_to_updt;
1737
1738 if (l_task_status <> l_paramc6) then
1739 l_event_code := 'TSC';
1740 elsif (l_owner <> l_paramc3) then
1741 l_event_code := 'TOC';
1742 end if;
1743
1744 -- ---------------------------------------------------------
1745 -- Repair history row inserted for TOC or TSC only
1746 -- ---------------------------------------------------------
1747 if (l_event_code in ('TOC', 'TSC')) then
1748
1749 -- --------------------------------
1750 -- Begin Update repair task history
1751 -- --------------------------------
1752 -- Standard Start of API savepoint
1753 SAVEPOINT Update_rep_task_hist;
1754
1755 x_return_status := FND_API.G_RET_STS_SUCCESS;
1756
1757 -- ---------------
1758 -- Api body starts
1759 -- ---------------
1760 IF (g_debug > 0 ) THEN
1761 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_update_task_hist before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for TOC or TSC event');
1762 END IF;
1763
1764
1765 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write
1766 (p_Api_Version_Number => 1.0 ,
1767 p_init_msg_list => 'F',
1768 p_commit => 'F',
1769 p_validation_level => NULL,
1770 p_action_code => 0,
1771 px_REPAIR_HISTORY_ID => l_repair_history_id,
1772 p_OBJECT_VERSION_NUMBER => null, -- travi ovn validation
1773 p_REQUEST_ID => null,
1774 p_PROGRAM_ID => null,
1775 p_PROGRAM_APPLICATION_ID => null,
1776 p_PROGRAM_UPDATE_DATE => null,
1777 p_CREATED_BY => FND_GLOBAL.USER_ID,
1778 p_CREATION_DATE => sysdate,
1779 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1780 p_LAST_UPDATE_DATE => sysdate,
1781 p_repair_line_id => l_repair_line_id,
1782 p_EVENT_CODE => l_event_code,
1783 p_EVENT_DATE => sysdate,
1784 p_QUANTITY => null,
1785 p_PARAMN1 => l_paramn1,
1786 p_PARAMN2 => l_paramn2,
1787 p_PARAMN3 => l_paramn3,
1788 p_PARAMN4 => l_paramn4,
1789 p_PARAMN5 => l_paramn5,
1790 p_PARAMN6 => null,
1791 p_PARAMN7 => null,
1792 p_PARAMN8 => null,
1793 p_PARAMN9 => null,
1794 p_PARAMN10 => FND_GLOBAL.USER_ID,
1795 p_PARAMC1 => l_paramc1,
1796 p_PARAMC2 => l_paramc2,
1797 p_PARAMC3 => l_paramc3,
1798 p_PARAMC4 => l_paramc4,
1799 p_PARAMC5 => l_paramc5,
1800 p_PARAMC6 => l_paramc6,
1801 p_PARAMC7 => null,
1802 p_PARAMC8 => null,
1803 p_PARAMC9 => null,
1804 p_PARAMC10 => null,
1805 p_PARAMD1 => l_paramd1,
1806 p_PARAMD2 => l_paramd1,
1807 p_PARAMD3 => l_paramd1,
1808 p_PARAMD4 => l_paramd1,
1809 p_PARAMD5 => null,
1810 p_PARAMD6 => null,
1811 p_PARAMD7 => null,
1812 p_PARAMD8 => null,
1813 p_PARAMD9 => null,
1814 p_PARAMD10 => null,
1815 p_ATTRIBUTE_CATEGORY => null,
1816 p_ATTRIBUTE1 => null,
1817 p_ATTRIBUTE2 => null,
1818 p_ATTRIBUTE3 => null,
1819 p_ATTRIBUTE4 => null,
1820 p_ATTRIBUTE5 => null,
1821 p_ATTRIBUTE6 => null,
1822 p_ATTRIBUTE7 => null,
1823 p_ATTRIBUTE8 => null,
1824 p_ATTRIBUTE9 => null,
1825 p_ATTRIBUTE10 => null,
1826 p_ATTRIBUTE11 => null,
1827 p_ATTRIBUTE12 => null,
1828 p_ATTRIBUTE13 => null,
1829 p_ATTRIBUTE14 => null,
1830 p_ATTRIBUTE15 => null,
1831 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1832 X_Return_Status => x_return_status,
1833 X_Msg_Count => x_msg_count,
1834 X_Msg_Data => x_msg_data
1835 );
1836 --
1837 IF (g_debug > 0 ) THEN
1838 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_update_task_hist after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write x_return_status'||x_return_status);
1839 END IF;
1840
1841 -- -------------------
1842 -- Api body ends here
1843 -- -------------------
1844
1845 -- Standard check of p_commit.
1846 IF FND_API.To_Boolean( 'F' ) THEN
1847 COMMIT WORK;
1848 END IF;
1849
1850 -- Standard call to get message count and IF count is get message info.
1851 FND_MSG_PUB.Count_And_Get
1852 (p_count => x_msg_count,
1853 p_data => x_msg_data );
1854
1855 v_total_records := v_total_records + 1;
1856
1857 end if; -- End of TOC/TSC check
1858
1859 commit;
1860
1861 end if; -- End of check for l_rep_hist_id
1862
1863 end loop;
1864
1865 IF (g_debug > 0 ) THEN
1866 csd_gen_utility_pvt.add('Completed depot_update_task_hist with Success..');
1867 END IF;
1868
1869 IF (g_debug > 0 ) THEN
1870 csd_gen_utility_pvt.add('Inserted into CSD_REPAIR_HISTORY table '||to_char(v_total_records)||' Records');
1871 END IF;
1872
1873
1874 -- travi check for call from tools
1875 if ( p_repair_line_id is not null ) then
1876 IF (g_debug > 0 ) THEN
1877 csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_UPDATE_TASK_HIST : Call from Depot Repair Form Tools Menu');
1878 END IF;
1879
1880 IF (g_debug > 0 ) THEN
1881 csd_gen_utility_pvt.add('Repair Line ID ='||to_char(p_repair_line_id));
1882 END IF;
1883
1884 IF (g_debug > 0 ) THEN
1885 csd_gen_utility_pvt.add('Total Records inserted into Repairs History Table ='||to_char(v_total_records));
1886 END IF;
1887
1888 end if;
1889
1890 EXCEPTION
1891 WHEN FND_API.G_EXC_ERROR THEN
1892 ROLLBACK TO Update_rep_task_hist;
1893 x_return_status := FND_API.G_RET_STS_ERROR ;
1894 FND_MSG_PUB.Count_And_Get
1895 (p_count => x_msg_count,
1896 p_data => x_msg_data
1897 );
1898 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1900 ROLLBACK TO Update_rep_task_hist;
1901 FND_MSG_PUB.Count_And_Get
1902 ( p_count => x_msg_count,
1903 p_data => x_msg_data
1904 );
1905 when no_data_found then
1906 IF (g_debug > 0 ) THEN
1907 csd_gen_utility_pvt.add('ndf');
1908 END IF;
1909
1910 when too_many_rows then
1911 IF (g_debug > 0 ) THEN
1912 csd_gen_utility_pvt.add('tmf');
1913 END IF;
1914
1915 when value_error then
1916 IF (g_debug > 0 ) THEN
1917 csd_gen_utility_pvt.add('ve');
1918 END IF;
1919
1920
1921 WHEN OTHERS THEN
1922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1923 ROLLBACK TO Update_rep_task_hist;
1924 IF FND_MSG_PUB.Check_Msg_Level
1925 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1926 THEN
1927 FND_MSG_PUB.Add_Exc_Msg
1928 (G_PKG_NAME ,
1929 l_api_name );
1930 END IF;
1931 FND_MSG_PUB.Count_And_Get
1932 (p_count => x_msg_count,
1933 p_data => x_msg_data );
1934 IF (g_debug > 0 ) THEN
1935 csd_gen_utility_pvt.add('others '||sqlerrm||to_char(sqlcode));
1936 END IF;
1937
1938
1939 END depot_update_task_hist;
1940
1941 end CSD_DEPOT_REPAIR_CNTR;