[Home] [Help]
PACKAGE BODY: APPS.CSF_DEBRIEF_UPDATE_PKG
Source
1 PACKAGE BODY CSF_DEBRIEF_UPDATE_PKG as
2 /* $Header: csfuppdb.pls 120.11.12010000.2 2008/08/06 09:04:46 syenduri ship $ */
3
4 -- Start of Comments
5 -- Package name : CSF_DEBRIEF_UPDATE_PKG
6 -- Purpose :
7 -- History :
8 -- NOTE :
9 -- End of Comments
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_DEBRIEF_UPDATE_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csfuppdb.pls';
13 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
14 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
15 G_UPDATE NUMBER := 1;
16 G_CREATE NUMBER := 2;
17
18 RECORD_LOCK_EXCEPTION EXCEPTION ;
19 PRAGMA EXCEPTION_INIT(RECORD_LOCK_EXCEPTION,-00054);
20
21 PROCEDURE Record_Lock
22 (p_debrief_line_id IN Number,
23 x_return_status OUT NOCOPY VARCHAR2
24 ) is
25 l_charge_upload_status Varchar2(50);
26 l_ib_update_status Varchar2(50);
27 l_spare_update_status Varchar2(50);
28 Begin
29 x_return_status := FND_API.G_RET_STS_SUCCESS;
30 select charge_upload_status,ib_update_status,spare_update_status
31 into l_charge_upload_status,l_ib_update_status,l_spare_update_status
32 from csf_debrief_lines
33 where debrief_line_id = p_debrief_line_id
34 for update nowait;
35 Exception
36 WHEN RECORD_LOCK_EXCEPTION THEN
37 x_return_status := FND_API.G_RET_STS_ERROR ;
38 When OTHERS then
39 x_return_status := FND_API.G_RET_STS_ERROR ;
40 End;
41
42 PROCEDURE main
43 (
44 errbuf OUT NOCOPY VARCHAR2,
45 retcode OUT NOCOPY NUMBER,
46 p_api_version IN NUMBER,
47 p_debrief_header_id IN NUMBER DEFAULT null,
48 p_incident_id IN NUMBER DEFAULT null
49 ) IS
50
51 l_api_name CONSTANT VARCHAR2(30) := 'main';
52 l_api_version CONSTANT NUMBER := 1.0;
53 l_transaction_type_id Number;
54 l_debrief_line_id number ;
55 l_debrief_header_id number ;
56 l_original_source_id number ;
57 l_original_source_code varchar2(10):= 'SR';
58 l_incident_id number ;
59 l_business_process_id number ;
60 l_line_category_code varchar2(10) ;
61 l_return_status varchar2(1) ;
62 l_msg_count number ;
63 l_msg_data varchar2(2000) ;
64 l_rec_status varchar2(10) ;
65 l_charges_interface_status varchar2(20) := null;
66 l_ib_interface_status varchar2(20) := null;
67 l_inv_interface_status varchar2(20) := null;
68 l_interface_status_meaning varchar2(20) ;
69 l_inventory_item_id number ;
70 l_currency_code varchar2(30) ;
71 l_uom_code varchar2(3) ;
72 l_quantity number := 0;
73 l_customer_id number ;
74 l_txn_billing_type number ;
75 l_installed_cp_return_by_date DATE ;
76 l_after_warranty_cost Number := 0;
77 counter Number ;
78 l_time_diff Number ;
79 l_profile_hours Varchar2(20) ;
80 l_return_reason_code Varchar2(30) := null;
81 l_qty Number ; --to_number(name_in('csf_debrief_labor_lines.quantity'));
82 l_revision varchar2(3) ;
83 l_source_type_code Varchar2(10) ;
84 l_repair_line_id Number ;
85 l_service_date Date ;
86 l_txn_billing_type_id Number ;
87 l_inv_transaction_type_id Number ;
88 l_organization_id Number ;
89 l_issuing_inventory_org_id Number ;
90 l_receiving_inventory_org_id Number ;
91 l_subinventory_code Varchar2(50) ;
92 l_issuing_sub_inventory_code Varchar2(50) ;
93 l_receiving_sub_inventory_code Varchar2(50) ;
94 l_locator_id Number ;
95 l_issuing_locator_id Number ;
96 l_receiving_locator_id Number ;
97 l_parent_product_id Number ;
98 l_item_serial_number Varchar2(30) ;
99 l_item_lotnumber Varchar2(120) ;
100 l_labor_start_date Date ;
101 l_labor_end_date Date ;
102 l_expense_amount Number ;
103 l_charge_upload_status Varchar2(50) := null ;
104 l_ib_update_status Varchar2(50) ;
105 l_spare_update_status Varchar2(50) ;
106 l_instance_id Number ;
107 l_removed_product_id Number ;
108 l_billing_type Varchar2(3) ;
109 l_debrief_number Varchar2(50) ;
110 l_inv_transaction_header_id Number ;
111 l_inv_transaction_id Number ;
112 l_mesg Varchar2(2000) ;
113 l_msg_dummy Number ;
114 l_transaction_type_id_csi Number ;
115 l_txn_sub_type_id Number ;
116 l_inv_master_organization_id Number :=fnd_api.g_miss_num;
117 l_customer_account_id Number ;
118 l_party_id Number ;
119 l_install_site_use_id Number ;
120 l_ship_site_use_id Number ;
121 l_in_out_flag Varchar2(10) ;
122 l_party_site_id Number ;
123 l_new_instance_id Number ;
124 l_message Varchar2(1000) ;
125 l1 Number ;
126 l_trackable Varchar2(1) ;
127 l_processed_flag Varchar2(50) ;
128 l_charges_instance_id Number ;
129 l_task_assignment_id Number ;
130 l_cancelled_flag Varchar2(1) ;
131 l_rejected_flag Varchar2(1) ;
132 l_completed_flag Varchar2(1) ;
133 l_closed_flag Varchar2(1) ;
134 l_cleanup_done Boolean := FALSE;
135 l_position Number;
136 l_instance_status Varchar(30) :=NULL; --added for bug 3192060
137 l_instance_status_id Number :=9.99E125 ;--fnd_api.g_miss_num --added for bug3192060
138 l_header_id Number; -- added for 3264030
139 l_record_lock Varchar2(1) := 'N'; -- added for bug 3142094
140 l_conc_result Boolean;
141 e_no_header_id Exception;
142 l_item_operational_status_code Varchar2(30);
143 l_create_charge_flag varchar2(1);
144 l_create_cost_flag varchar2(1);
145 l_Cost_Rec cs_cost_details_pub.Cost_Rec_Type ;
146 l_object_version number;
147 l_cost_id number;
148 l_estimate_detail_id number := null;
149
150 cursor c_charge_id(p_debrief_line_id number) is
151 select estimate_detail_id
152 from cs_estimate_details
153 where source_code = 'SD'
154 and source_id = p_debrief_line_id;
155
156 cursor c_lines (p_debrief_header_id Number) is
157 select cdl.debrief_line_id,
158 cdl.service_date,
159 --cdl.txn_billing_type_id,
160 cdl.transaction_type_id,
161 cdl.inventory_item_id,
162 cdl.issuing_inventory_org_id,
163 cdl.receiving_inventory_org_id,
164 cdl.issuing_sub_inventory_code,
165 cdl.receiving_sub_inventory_code,
166 cdl.issuing_locator_id,
167 cdl.receiving_locator_id,
168 cdl.parent_product_id,
169 cdl.removed_product_id,
170 cdl.item_serial_number,
171 cdl.item_revision,
172 cdl.item_lotnumber,
173 cdl.uom_code,
174 cdl.quantity,
175 cdl.labor_start_date,
176 cdl.labor_end_date,
177 cdl.expense_amount,
178 cdl.currency_code,
179 cdl.charge_upload_status,
180 cdl.ib_update_status,
181 cdl.spare_update_status,
182 cdl.business_process_id,
183 cdl.return_reason_code,
184 cdl.instance_id,
185 cdl.status_of_received_part, --added for bug 3192060
186 cdl.item_operational_status_code
187 from csf_debrief_lines cdl
188 where cdl.debrief_header_id = p_debrief_header_id
189 and nvl(cdl.quantity,-1) <> 0 ;
190
191 cursor c_header (p_header_id Number) is
192 SELECT cdh.debrief_header_id,
193 jtb.source_object_type_code source_type_code,
194 ciab.incident_id ,
195 to_number(null) repair_line_id,
196 ciab.customer_id,
197 ciab.account_id customer_account_id,
198 cdh.debrief_number ,
199 jta.task_assignment_id,
200 jtsb.cancelled_flag,
201 jtsb.rejected_flag,
202 jtsb.completed_flag,
203 jtsb.closed_flag
204 from
205 JTF_TASK_STATUSES_B jtsb,
206 CSF_DEBRIEF_HEADERS cdh,
207 JTF_TASKS_B jtb,
208 JTF_TASK_ASSIGNMENTS jta,
209 cs_incidents_all_b ciab
210 WHERE cdh.task_assignment_id = jta.task_assignment_id
211 and jta.task_id = jtb.task_id
212 and nvl(jtb.deleted_flag,'N') <> 'Y'
213 and jta.assignment_status_id = jtsb.task_status_id
214 and jta.assignee_role = 'ASSIGNEE'
215 and jtb.source_object_type_code = 'SR'
216 and ciab.incident_id = jtb.source_object_id
217 and cdh.debrief_header_id = p_header_id -- changed for the bug 3648213
218 union all
219 SELECT cdh.debrief_header_id,
220 jtb.source_object_type_code,
221 cr.incident_id,
222 cr.repair_line_id,
223 jtb.customer_id ,
224 jtb.cust_account_id, -- replaced -1 with jtb.cust_account_id for bug 3343984
225 cdh.debrief_number ,
226 jta.task_assignment_id,
227 jtsb.cancelled_flag,
228 jtsb.rejected_flag,
229 jtsb.completed_flag,
230 jtsb.closed_flag
231 from
232 JTF_TASK_STATUSES_B jtsb,
233 CSF_DEBRIEF_HEADERS cdh,
234 JTF_TASKS_B jtb,
235 JTF_TASK_ASSIGNMENTS jta,
236 csd_repairs cr
237 WHERE cdh.task_assignment_id = jta.task_assignment_id
238 and jta.task_id = jtb.task_id
239 and nvl(jtb.deleted_flag,'N') <> 'Y'
240 and jta.assignment_status_id = jtsb.task_status_id
241 and jta.assignee_role = 'ASSIGNEE'
242 and jtb.source_object_type_code = 'DR'
243 and jtb.source_object_id=cr.repair_line_id
244 and cdh.debrief_header_id = p_header_id ; -- changed for the bug 3648213
245
246 cursor c_header_inc (p_incident_id Number) is -- added for the bug 3648213
247 SELECT cdh.debrief_header_id,
248 jtb.source_object_type_code source_type_code,
249 ciab.incident_id ,
250 to_number(null) repair_line_id,
251 ciab.customer_id,
252 ciab.account_id customer_account_id,
253 cdh.debrief_number ,
254 jta.task_assignment_id,
255 jtsb.cancelled_flag,
256 jtsb.rejected_flag,
257 jtsb.completed_flag,
258 jtsb.closed_flag
259 from
260 JTF_TASK_STATUSES_B jtsb,
261 CSF_DEBRIEF_HEADERS cdh,
262 JTF_TASKS_B jtb,
263 JTF_TASK_ASSIGNMENTS jta,
264 cs_incidents_all_b ciab
265 WHERE cdh.task_assignment_id = jta.task_assignment_id
266 and jta.task_id = jtb.task_id
267 and nvl(jtb.deleted_flag,'N') <> 'Y'
268 and jta.assignment_status_id = jtsb.task_status_id
269 and jta.assignee_role = 'ASSIGNEE'
270 and jtb.source_object_type_code = 'SR'
271 and ciab.incident_id = jtb.source_object_id
272 and ciab.incident_id = p_incident_id
273 and cdh.debrief_header_id = nvl(p_debrief_header_id,cdh.debrief_header_id)
274 union all
275 SELECT cdh.debrief_header_id,
276 jtb.source_object_type_code,
277 cr.incident_id,
278 cr.repair_line_id,
279 jtb.customer_id ,
280 jtb.cust_account_id, -- replaced -1 with jtb.cust_account_id for bug 3343984
281 cdh.debrief_number ,
282 jta.task_assignment_id,
283 jtsb.cancelled_flag,
284 jtsb.rejected_flag,
285 jtsb.completed_flag,
286 jtsb.closed_flag
287 from
288 JTF_TASK_STATUSES_B jtsb,
289 CSF_DEBRIEF_HEADERS cdh,
290 JTF_TASKS_B jtb,
291 JTF_TASK_ASSIGNMENTS jta,
292 csd_repairs cr
293 WHERE cdh.task_assignment_id = jta.task_assignment_id
294 and jta.task_id = jtb.task_id
295 and nvl(jtb.deleted_flag,'N') <> 'Y'
296 and jta.assignment_status_id = jtsb.task_status_id
297 and jta.assignee_role = 'ASSIGNEE'
298 and jtb.source_object_type_code = 'DR'
299 and jtb.source_object_id=cr.repair_line_id
300 and cr.incident_id = p_incident_id
301 and cdh.debrief_header_id = nvl(p_debrief_header_id,cdh.debrief_header_id);
302
303 -- changed above cursor for the bug 3264030
304
305 Cursor c_headers is
306 Select debrief_header_id
307 From csf_debrief_headers
308 Where processed_flag is null or processed_flag <> 'COMPLETED';
309
310 /* cursor c_header is
311 select cdtv.debrief_header_id,
312 cdtv.source_type_code,
313 cdtv.incident_id,
314 cdtv.repair_line_id,
315 cdtv.customer_id,
316 cdtv.customer_account_id,
317 cdtv.debrief_number,
318 cdtv.task_assignment_id,
319 jtsv.cancelled_flag,
320 jtsv.rejected_flag,
321 jtsv.completed_flag,
322 jtsv.closed_flag
323 from csf_debrief_tasks_v cdtv,
324 jtf_task_assignments jta,
325 jtf_task_statuses_vl jtsv
326 where cdtv.debrief_header_id = nvl(p_debrief_header_id, cdtv.debrief_header_id)
327 and cdtv.task_assignment_id = jta.task_assignment_id
328 and jta.assignment_status_id = jtsv.task_status_id
329 and cdtv.incident_id = nvl(p_incident_id, cdtv.incident_id); */
330
331 /*cursor c_trans (p_txn_billing_type_id number) is
332 select cttv.line_order_category_code,
333 cttv.transaction_type_id ,
334 ctbt.billing_type,
335 ctst.sub_type_id ,
336 ctst.transaction_type_id transaction_type_id_csi
337 from cs_transaction_types_vl cttv,
338 cs_txn_billing_types ctbt,
339 csi_txn_sub_types ctst,
340 csi_txn_types ctt
341 where cttv.transaction_type_id = ctbt.transaction_type_id
342 and ctbt.txn_billing_type_id = p_txn_billing_type_id
343 and ctst.cs_transaction_type_id = cttv.transaction_type_id
344 and ctt.source_application_id=513
345 and ctt.transaction_type_id = ctst.transaction_type_id;*/
346 --hehxx added create_cost and create_charge flags
347 cursor c_cost_charge_flags (p_transaction_type_id number) is
348 select create_cost_flag,
349 create_charge_flag
350 from cs_transaction_types
351 where transaction_type_id = p_transaction_type_id;
352
353 cursor c_trans (p_transaction_type_id number,
354 p_inventory_item_id number,
355 p_inventory_org_id number) is
356 select cttv.line_order_category_code,
357 ctbt.txn_billing_type_id ,
358 cbtc.billing_category billing_type,
359 ctst.sub_type_id ,
360 ctst.transaction_type_id transaction_type_id_csi
361 from cs_transaction_types_vl cttv,
362 cs_txn_billing_types ctbt,
363 csi_txn_sub_types ctst,
364 csi_txn_types ctt,
365 mtl_system_items_b_kfv msibk,
366 cs_billing_type_categories cbtc
367 where cttv.transaction_type_id = p_transaction_type_id
368 and cttv.transaction_type_id = ctbt.transaction_type_id
369 and ctbt.billing_type = msibk.material_billable_flag
370 and msibk.material_billable_flag = cbtc.billing_type
371 and msibk.inventory_item_id = p_inventory_item_id
372 and msibk.organization_id = p_inventory_org_id
373 and ctst.cs_transaction_type_id(+) = cttv.transaction_type_id
374 and ctt.source_application_id(+) = 513
375 and ctt.transaction_type_id(+) = ctst.transaction_type_id;
376
377 Cursor c_internal_party_id Is
378 select internal_party_id
379 from csi_install_parameters;
380
381 cursor c_site (p_incident_id number) Is
382 select install_site_use_id,
383 ship_to_site_use_id
384 from cs_incidents_all
385 where incident_id = p_incident_id;
386
387 cursor c_party_site_id (p_install_site_id number) Is
388 select party_site_id
389 from hz_party_site_uses
390 where party_site_use_id = p_install_site_id;
391
392 cursor c_trackable (p_inventory_item_id Number, p_organization_id Number) is -- changed for bug 3897985
393 select comms_nl_trackable_flag
394 from mtl_system_items
395 where inventory_item_id = p_inventory_item_id
396 and organization_id = p_organization_id;
397
398 Cursor c_status_meaning(p_code Varchar2) Is
399 select meaning
400 from fnd_lookups
401 where lookup_type = 'CSF_INTERFACE_STATUS'
402 and lookup_code = p_code;
403
404 -----------------------------------BEGIN-----------------------------------------------------------
405
406 Begin
407
408 retcode := 0;
409 savepoint main;
410
411 if p_debrief_header_id is null and
412 p_incident_id is null and
413 p_api_version = 2.0 then
414 raise e_no_header_id;
415 end if;
416
417 if p_debrief_header_id is not null and p_incident_id is null then
418 open c_header(p_debrief_header_id);
419 elsif p_incident_id is not null then
420 open c_header_inc(p_incident_id);
421 else
422 open c_headers;
423 end if;
424
425 loop
426
427 l_processed_flag := 'COMPLETED';
428 l_cleanup_done := FALSE; -- moved inside loop for bug 3549864
429
430 if p_debrief_header_id is not null and p_incident_id is null then
431 fetch c_header into l_debrief_header_id,
432 l_source_type_code,
433 l_incident_id,
434 l_repair_line_id,
435 l_customer_id,
436 l_customer_account_id,
437 l_debrief_number,
438 l_task_assignment_id,
439 l_cancelled_flag ,
440 l_rejected_flag ,
441 l_completed_flag ,
442 l_closed_flag ;
443
444 exit when c_header%notfound;
445 elsif p_incident_id is not null then -- Changed for bug 3648213
446 fetch c_header_inc into l_debrief_header_id,
447 l_source_type_code,
448 l_incident_id,
449 l_repair_line_id,
450 l_customer_id,
451 l_customer_account_id,
452 l_debrief_number,
453 l_task_assignment_id,
454 l_cancelled_flag ,
455 l_rejected_flag ,
456 l_completed_flag ,
457 l_closed_flag ;
458
459 exit when c_header_inc%notfound;
460 else
461 l_debrief_header_id := Null ;
462 l_source_type_code := Null ;
463 l_incident_id := Null ;
464 l_repair_line_id := Null ;
465 l_customer_id := Null ;
466 l_customer_account_id := Null ;
467 l_debrief_number := Null ;
468 l_task_assignment_id := Null ;
469 l_cancelled_flag := Null ;
470 l_rejected_flag := Null ;
471 l_completed_flag := Null ;
472 l_closed_flag := Null ;
473 fetch c_headers into l_header_id ;
474 exit when c_headers%notfound;
475 open c_header(l_header_id) ;
476 fetch c_header into l_debrief_header_id,
477 l_source_type_code,
478 l_incident_id,
479 l_repair_line_id,
480 l_customer_id,
481 l_customer_account_id,
482 l_debrief_number,
483 l_task_assignment_id,
484 l_cancelled_flag ,
485 l_rejected_flag ,
486 l_completed_flag ,
487 l_closed_flag ;
488 close c_header;
489 end if;
490
491 relieve_reservations ( l_task_assignment_id ,
492 l_return_status,
493 l_msg_data ,
494 l_msg_count );
495 -------------------------------------------------------------------
496 --start cleanup in progress charge lines for the service request--
497 ------------------------------------------------------------------
498 If not l_cleanup_done Then
499 Cs_service_billing_engine_pvt.Delete_In_Progress_Charges(
500 P_Api_Version_Number => 1.0,
501 P_Init_Msg_List => FND_API.G_FALSE,
502 P_Commit => FND_API.G_FALSE,
503 p_incident_id => l_incident_id,
504 p_debrief_header_id => l_debrief_header_id, -- added for bug 3549864
505 x_return_status => l_return_status,
506 x_msg_count => l_msg_count,
507 x_msg_data => l_msg_data);
508
509 commit;
510 l_cleanup_done := TRUE;
511 End If; --cleanup
512 ----------------------------------------------------------------
513 --end cleanup in progress charge lines for the service request--
514 ----------------------------------------------------------------
515
516
517 l_msg_data := null;
518
519 if l_source_type_code ='SR' Then
520 l_original_source_id := l_incident_id;
521 l_original_source_code := 'SR';
522 end if;
523 if l_source_type_code ='DR' Then
524 l_original_source_id := l_repair_line_id;
525 l_original_source_code := 'DR';
526 end if;
527
528
529 open c_lines (l_debrief_header_id);
530 loop
531 savepoint before_fetch;
532
533 l_after_warranty_cost := null;
534
535 fetch c_lines into l_debrief_line_id,
536 l_service_date,
537 l_transaction_type_id,
538 l_inventory_item_id,
539 l_issuing_inventory_org_id,
540 l_receiving_inventory_org_id,
541 l_issuing_sub_inventory_code,
542 l_receiving_sub_inventory_code,
543 l_issuing_locator_id,
544 l_receiving_locator_id,
545 l_parent_product_id,
546 l_removed_product_id,
547 l_item_serial_number,
548 l_revision,
549 l_item_lotnumber,
550 l_uom_code,
551 l_quantity,
552 l_labor_start_date,
553 l_labor_end_date,
554 l_expense_amount,
555 l_currency_code,
556 l_charge_upload_status,
557 l_ib_update_status,
558 l_spare_update_status,
559 l_business_process_id,
560 l_return_reason_code,
561 l_instance_id,
562 l_instance_status, --added for bug 3192060
563 l_item_operational_status_code;
564
565 exit when c_lines%notfound;
566 --added the following for bug3142094
567 l_return_status := null;
568 l_record_lock := 'N';
569
570 Record_Lock(l_debrief_line_id, l_return_status) ;
571
572 If l_return_status = FND_API.G_RET_STS_ERROR THEN
573 rollback to before_fetch;
574 l_processed_flag := 'COMPLETED W/ERRORS';
575 l_record_lock := 'Y';
576 exit ;
577 end if;
578
579 --added the following for bug3246952
580
581 l_return_status := null;
582 l_charges_interface_status := null;
583 l_ib_interface_status := null;
584 l_inv_interface_status := null;
585 l_msg_data := null;
586 l_charges_instance_id := fnd_api.g_miss_num;
587 l_organization_id := nvl(l_receiving_inventory_org_id, nvl(l_issuing_inventory_org_id,cs_std.get_item_valdn_orgzn_id));
588
589
590 --we use c_trans cursor only when we have item number
591 --hehxx
592 open c_cost_charge_flags(l_transaction_type_id);
593 fetch c_cost_charge_flags into l_create_cost_flag, l_create_charge_flag;
594 close c_cost_charge_flags;
595
596 if (l_inventory_item_id is not null
597 and l_inventory_item_id <> fnd_api.g_miss_num) Then
598
599 open c_trans (l_transaction_type_id, l_inventory_item_id, l_organization_id);
600 fetch c_trans into l_line_category_code,
601 l_txn_billing_type_id,
602 l_billing_type,
603 l_txn_sub_type_id ,
604 l_transaction_type_id_csi;
605 close c_trans;
606 else --this is for labor lines without item number
607 -- l_line_category_code := 'ORDER'; Commented for bug 7208532
608 l_line_category_code := null;
609 l_txn_billing_type_id := null;
610 l_billing_type := null;
611 l_txn_sub_type_id := null;
612 l_transaction_type_id_csi := null;
613 -- added for bug 3456295
614 l_subinventory_code := null;
615 l_organization_id := null;
616 end if;
617
618
619
620 If (l_billing_type = 'M') Then
621 ----
622 l_installed_cp_return_by_date := sysdate;
623 l_party_id := l_customer_id;
624
625 if (l_line_category_code = 'RETURN') then --FS Recovery
626 l_inv_transaction_type_id := 94; --RECEIVING
627 l_subinventory_code := l_receiving_sub_inventory_code;
628 l_locator_id := l_receiving_locator_id;
629 l_organization_id := l_receiving_inventory_org_id;
630 l_in_out_flag:='IN';
631
632 open c_internal_party_id;
633 fetch c_internal_party_id into l_party_id;
634 close c_internal_party_id;
635
636 l_charges_instance_id := l_instance_id;
637
638 else --FS Usage
639 l_inv_transaction_type_id := 93; --ISSUING
640 l_subinventory_code := l_issuing_sub_inventory_code;
641 l_locator_id := l_issuing_locator_id;
642 l_organization_id := l_issuing_inventory_org_id;
643 l_in_out_flag:='OUT';
644
645 open c_site(l_incident_id);
646 fetch c_site into l_install_site_use_id, l_ship_site_use_id;
647 close c_site;
648
649 If l_install_site_use_id is not null Then l_party_site_id := l_install_site_use_id;
650 Else
651 open c_party_site_id (l_ship_site_use_id);
652 fetch c_party_site_id into l_party_site_id;
653 close c_party_site_id;
654 End If;
655 l_charges_instance_id := null;
656
657 end if;
658
659 open c_trackable (l_inventory_item_id, l_organization_id); -- changed for bug 3897985
660 fetch c_trackable into l_trackable;
661 close c_trackable ;
662
663 Elsif (l_billing_type = 'E') Then
664 l_installed_cp_return_by_date := NULL;
665
666 if l_expense_amount is null
667 then l_after_warranty_cost := null;
668 end if;
669 if l_quantity is null
670 then l_after_warranty_cost := l_expense_amount;
671 l_quantity :=1;
672 end if;
673 -- added for bug 3456295
674 l_subinventory_code := null;
675 l_organization_id := null;
676
677 -- do not pass NULL in case of l_line_category_code = 'RETURN'
678 -- bug # 6851448
679 if (l_line_category_code <> 'RETURN') then
680 l_return_reason_code := null;
681 end if;
682
683 Elsif (l_billing_type = 'L') Then
684 l_installed_cp_return_by_date := NULL;
685 if l_quantity is null Then
686 l_time_diff := to_date(l_labor_end_date,'dd-mm-rrrr hh24:mi:ss')
687 - to_date(l_labor_start_date,'dd-mm-rrrr hh24:mi:ss');
688
689 l_time_diff := l_time_diff * 24;
690 l_profile_hours := fnd_profile.value('CSF_UOM_HOURS');
691 l_quantity := inv_convert.inv_um_convert
692 (Null,
693 2,
694 l_time_diff,
695 l_profile_hours,
696 l_uom_code,
697 NULL,NULL);
698
699 End If;
700 -- added for bug 3456295
701 l_subinventory_code := null;
702 l_organization_id := null;
703
704 -- do not pass NULL in case of l_line_category_code = 'RETURN'
705 -- bug # 6851448
706 if (l_line_category_code <> 'RETURN') then
707 l_return_reason_code := null;
708 end if;
709
710 end if; --billing_type='M'
711
712 -------------------------------------------------------
713 --decide if we generate final or in progress charges---
714 -------------------------------------------------------
715 if (l_cancelled_flag ='Y' or l_rejected_flag='Y'
716 or l_completed_flag='Y' or l_closed_flag='Y')
717 Then
718 -- we have to generate final charges
719
720
721 ------------------------------------------------------------------------------------------------------
722 -- UPDATE CHARGES --
723 -------------------------------------------------------------------------------------------------------
724 if nvl(l_charge_upload_status, ' ') <> 'SUCCEEDED' and l_create_charge_flag = 'Y' Then
725
726 csf_debrief_charges.create_charges(
727 p_original_source_id => l_original_source_id ,
728 p_original_source_code => l_original_source_code ,
729 p_incident_id => l_incident_id ,
730 p_business_process_id => l_business_process_id ,
731 p_line_category_code => l_line_category_code ,
732 p_source_code => 'SD' ,
733 p_source_id => l_debrief_line_id ,
734 p_inventory_item_id => l_inventory_item_id ,
735 p_item_revision => l_revision ,
736 p_unit_of_measure_code => l_uom_code ,
737 p_quantity => l_quantity ,
738 p_txn_billing_type_id => l_txn_billing_type_id ,
739 p_transaction_type_id => l_transaction_type_id ,
740 p_customer_product_id => l_charges_instance_id ,
741 p_installed_cp_return_by_date => l_installed_cp_return_by_date,
742 p_after_warranty_cost => l_after_warranty_cost ,
743 p_currency_code => l_currency_code ,
744 p_return_reason_code => l_return_reason_code ,
745 p_inventory_org_id => l_organization_id ,
746 p_subinventory => l_subinventory_code ,
747 p_serial_number => l_item_serial_number ,
748 p_final_charge_flag => 'Y' ,
749 p_labor_start_date => l_labor_start_date ,
750 p_labor_end_date => l_labor_end_date ,
751 x_return_status => l_return_status ,
752 x_msg_count => l_msg_count ,
753 x_msg_data => l_msg_data );
754
755 if l_RETURN_STATUS = 'S' then -- success
756 l_msg_data := null; -- added for bug 3863950
757 l_charges_interface_status := 'SUCCEEDED';
758 elsif l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then --Expected Error
759 retcode := 1;
760
761 l_processed_flag := 'COMPLETED W/ERRORS';
762 if l_msg_count > 0 then
763 FOR counter IN REVERSE 1..l_msg_count
764 LOOP
765 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
766 FND_FILE.put_line(FND_FILE.log,l_msg_data);
767 end loop;
768 end if;
769 l_charges_interface_status := 'FAILED';
770
771 end if;
772 --dbms_output.put_line('l_return_status='||l_return_status);
773 --dbms_output.put_line('l_msg_data='||l_msg_data);
774 --dbms_output.put_line('l_charges_interface_status='||l_charges_interface_status);
775
776
777 --------------------------------------------------------------------------
778 -- START UPDATE CHARGES UPLOAD STATUS COLUMN ----------------------------
779 --------------------------------------------------------------------------
780
781 if l_charges_interface_status = 'FAILED' then
782 rollback to before_fetch;
783
784 if l_charges_interface_status <> 'FAILED' then
785 l_charges_interface_status := fnd_api.g_miss_char;
786 end if;
787 csf_debrief_lines_pkg.update_row(
788 p_debrief_line_id => l_debrief_line_id,
789 p_error_text => substr(l_msg_data,1,2000),
790 p_charge_upload_status => l_charges_interface_status,
791 p_last_updated_by => fnd_global.user_id,
792 p_last_update_date => sysdate,
793 p_last_update_login => fnd_global.login_id);
794 else
795
796 if l_charges_interface_status = 'SUCCEEDED' Then --it means we tried to update Charges and it was succ
797
798 csf_debrief_lines_pkg.update_row(
799 p_debrief_line_id => l_debrief_line_id,
800 p_error_text => null,
801 p_charge_upload_status => l_charges_interface_status,
802 p_last_updated_by => fnd_global.user_id,
803 p_last_update_date => sysdate,
804 p_last_update_login => fnd_global.login_id);
805 end if;
806 end if;
807 --------------------------------------------------------------------------
808 -- END UPDATE CHARGES UPLOAD STATUS COLUMN ----------------------------
809 -------------------------------------------------------------------------
810
811 end if; --charge_upload_status
812
813 -------------------------------------------------------------------------
814 --END UPDATE CHARGE------------------------------------------------------
815 -------------------------------------------------------------------------
816
817 If (l_billing_type = 'M') Then
818
819 if nvl(l_ib_update_status,' ') <> 'SUCCEEDED' and l_trackable ='Y'
820 and (l_charges_interface_status = 'SUCCEEDED' or l_charge_upload_status ='SUCCEEDED' ) --continue only if charges was suc.
821 Then
822 ------------------------------------------------------------------------------------------------------
823 -- UPDATE INSTALL BASE --
824 -------------------------------------------------------------------------------------------------------
825
826 --dbms_output.put_line('in IB');
827 l_instance_status_id :=to_number(l_instance_status); --added for bug 3192060
828 csf_ib.update_install_base(
829 p_api_version => 1.0,
830 p_init_msg_list => null,
831 p_commit => null,
832 p_validation_level => null,
833 x_return_status => l_return_status,
834 x_msg_count => l_msg_count,
835 x_msg_data => l_msg_data,
836 x_new_instance_id => l_new_instance_id, --
837 p_in_out_flag => l_in_out_flag, --
838 p_transaction_type_id => l_transaction_type_id_csi,
839 p_txn_sub_type_id => l_txn_sub_type_id,
840 p_instance_id => l_instance_id,
841 p_inventory_item_id => l_inventory_item_id,
842 p_inv_organization_id => l_organization_id,
843 p_inv_subinventory_name => l_subinventory_code,
844 p_inv_locator_id => l_locator_id,
845 p_quantity => l_quantity,
846 p_inv_master_organization_id => l_inv_master_organization_id,
847 p_mfg_serial_number_flag => 'N',
848 p_serial_number => l_item_serial_number,
849 p_lot_number => l_item_lotnumber,
850 p_revision => l_revision,
851 p_unit_of_measure => l_uom_code,
852 p_party_id => l_party_id,
853 p_party_account_id => l_customer_account_id,
854 p_party_site_id => l_party_site_id,
855 p_parent_instance_id => l_parent_product_id,
856 p_instance_status_id => l_instance_status_id, --added for bug 3192060
857 p_item_operational_status_code => l_item_operational_status_code);
858
859
860
861 if l_RETURN_STATUS = 'S' then -- success --3
862 l_msg_data := null; -- added for bug 3863950
863 l_ib_interface_status := 'SUCCEEDED';
864 elsif l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then --Expected Error --2
865 retcode := 1;
866 l_processed_flag := 'COMPLETED W/ERRORS';
867 if l_msg_count > 0 then --1
868 FOR counter IN REVERSE 1..l_msg_count
869 LOOP
870 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
871 FND_FILE.put_line(FND_FILE.log,l_msg_data);
872 end loop;
873 end if; --1
874 l_ib_interface_status := 'FAILED';
875 --dbms_output.put_line('l_ib_interface_status='||l_ib_interface_status);
876 end if; --3
877
878
879
880 end if; --4
881
882 if l_ib_interface_status = 'FAILED' or l_charges_interface_status = 'FAILED' then
883 rollback to before_fetch;
884
885 if l_charges_interface_status <> 'FAILED' then
886 l_charges_interface_status := fnd_api.g_miss_char;
887 end if;
888 if l_ib_interface_status <> 'FAILED' then
889 l_ib_interface_status := fnd_api.g_miss_char;
890 end if;
891 csf_debrief_lines_pkg.update_row(
892 p_debrief_line_id => l_debrief_line_id,
893 p_error_text => substr(l_msg_data,1,2000),
894 p_charge_upload_status => l_charges_interface_status,
895 p_ib_update_status => l_ib_interface_status,
896 p_last_updated_by => fnd_global.user_id,
897 p_last_update_date => sysdate,
898 p_last_update_login => fnd_global.login_id);
899 else
900 if nvl(l_ib_update_status,' ') <> 'SUCCEEDED' and l_billing_type='M' and l_trackable='Y'
901 and (l_charges_interface_status= 'SUCCEEDED' or l_charge_upload_status='SUCCEEDED') --we tried to update IB for this line
902 Then
903
904 if l_line_category_code <> 'RETURN' then
905 l_instance_id := l_new_instance_id;
906 end if;
907 csf_debrief_lines_pkg.update_row(
908 p_debrief_line_id => l_debrief_line_id,
909 p_error_text => null,
910 p_instance_id => l_instance_id,
911 p_ib_update_status => l_ib_interface_status,
912 p_last_updated_by => fnd_global.user_id,
913 p_last_update_date => sysdate,
914 p_last_update_login => fnd_global.login_id);
915 end if;
916 end if;
917
918 ------------------------------------------------------------------------------------------------------
919 -- UPDATE INVENTORY --
920 -------------------------------------------------------------------------------------------------------
921 if nvl(l_spare_update_status,' ') <> 'SUCCEEDED'
922 and (l_charges_interface_status = 'SUCCEEDED' or l_charge_upload_status ='SUCCEEDED' )
923 and ( (l_ib_interface_status = 'SUCCEEDED' and l_trackable='Y')
924 or (l_ib_update_status = 'SUCCEEDED' and l_trackable ='Y')
925 or l_trackable ='N' or l_trackable is null )
926 --ib_interface status must be = with Suceesed only when I tried to update Ib for this record
927 Then
928
929 --dbms_output.put_line('in update inv');
930 /*
931 dbms_output.put_line('p_transaction_type_id =' ||l_transaction_type_id_csi);
932 dbms_output.put_line('p_txn_sub_type_id =' ||l_txn_sub_type_id);
933 dbms_output.put_line('p_instance_id =' ||l_instance_id);
934 dbms_output.put_line('p_inventory_item_id =' ||l_inventory_item_id);
935 dbms_output.put_line('p_inv_organization_id =' ||l_organization_id);
936 dbms_output.put_line('p_inv_subinventory_name =' ||l_subinventory_code);
937 dbms_output.put_line('p_quantity =' ||l_quantity);
938 dbms_output.put_line('p_inv_master_organization_id =' ||l_inv_master_organization_id);
939 dbms_output.put_line('p_mfg_serial_number_flag =' );
940 dbms_output.put_line('p_serial_number =' ||l_item_serial_number);
941 dbms_output.put_line('p_lot_number =' ||l_item_lotnumber);
942 dbms_output.put_line('p_unit_of_measure =' ||l_uom_code);
943 dbms_output.put_line('p_party_id =' ||l_party_id);
944 dbms_output.put_line('p_party_account_id =' ||l_customer_account_id);
945 dbms_output.put_line('p_party_site_id =' ||l_party_site_id);
946 dbms_output.put_line('p_parent_instance_id =' ||l_parent_product_id) ;*/
947
948 l_return_status := fnd_api.g_ret_sts_success;
949 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_UPDATE_PKG','TRANSACT_MATERIAL','B','C') THEN
950 csf_debrief_update_pkg.g_debrief_line_id := l_debrief_line_id;
951 csf_debrief_update_pkg.g_account_id := null;
952 csf_debrief_pub.call_internal_hook('CSF_DEBRIEF_UPDATE_PKG','TRANSACT_MATERIAL','B',l_return_status);
953 end if;
954 if l_return_status = fnd_api.g_ret_sts_success then
955 CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL(
956 p_api_version => l_api_version,
957 x_return_status => l_RETURN_STATUS,
958 x_msg_count => l_MSG_COUNT,
959 x_msg_data => l_MSG_DATA,
960 p_init_msg_list => FND_API.G_TRUE,
961 p_commit => FND_API.G_FALSE,
962 p_inventory_item_id => l_inventory_item_id,
963 p_organization_id => l_organization_id,
964 p_subinventory_code => l_subinventory_code,
965 p_locator_id => l_locator_id,
966 p_serial_number => l_item_serial_number,
967 p_quantity => l_quantity,
968 p_uom => l_uom_code,
969 p_revision => l_revision,
970 p_lot_number => l_item_lotnumber,
971 p_transfer_to_subinventory => null,
972 p_transfer_to_locator => null,
973 p_transfer_to_organization => null,
974 p_source_id => null,
975 p_source_line_id => null,
976 p_transaction_type_id => l_inv_transaction_type_id,
977 p_account_id => csf_debrief_update_pkg.g_account_id,
978 px_transaction_header_id => l_inv_transaction_header_id,
979 px_transaction_id => l_inv_transaction_id,
980 p_transaction_source_id => l_debrief_header_id,
981 p_trx_source_line_id => l_debrief_line_id,
982 p_transaction_source_name => l_debrief_number,
983 p_transaction_date => l_service_date );
984 end if;
985 if l_RETURN_STATUS = 'S' then -- success
986 l_inv_interface_status := 'SUCCEEDED';
987 l_msg_data := null;
988 elsif l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then --Expected Error
989 retcode := 1;
990 l_processed_flag := 'COMPLETED W/ERRORS';
991 if l_msg_count > 0 then
992 FOR counter IN REVERSE 1..l_msg_count
993 LOOP
994 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
995 l_position := instr(l_msg_data, 'ERROR_EXPLANATION');
996 l_msg_data := substr(l_msg_data, l_position);
997 FND_FILE.put_line(FND_FILE.log,l_msg_data);
998 end loop;
999 end if;
1000 l_inv_interface_status := 'FAILED';
1001 --dbms_output.put_line('l_inv_interface_status='||l_inv_interface_status);
1002 --dbms_output.put_line('l_msg_data='||l_msg_data);
1003 end if;
1004 end if; --spares update status
1005 end if; --billing_type='M'
1006
1007 -- COSTING
1008 if (l_inventory_item_id is null and l_create_cost_flag = 'Y'
1009 and l_create_charge_flag = 'N'
1010 and nvl(l_charge_upload_status,' ') <> 'SUCCEEDED') or
1011 (l_inventory_item_id is not null and l_create_cost_flag = 'Y'
1012 and nvl(l_charge_upload_status,' ') <> 'SUCCEEDED') Then
1013 l_estimate_detail_id := null;
1014
1015 if l_create_charge_flag = 'Y' then
1016 open c_charge_id(l_debrief_line_id);
1017 fetch c_charge_id into l_estimate_detail_id;
1018 close c_charge_id;
1019 end if;
1020
1021 l_cost_rec.incident_id := l_incident_id;
1022 l_cost_rec.transaction_type_id := l_transaction_type_id;
1023 l_cost_rec.txn_billing_type_id := l_txn_billing_type_id;
1024 l_cost_rec.inventory_item_id := l_inventory_item_id;
1025 l_cost_rec.quantity := l_quantity;
1026 l_cost_rec.unit_of_measure_code := l_uom_code;
1027 l_cost_rec.currency_code := l_currency_code;
1028 l_cost_rec.source_id := l_debrief_line_id;
1029 l_cost_rec.source_code := 'SD';
1030 l_cost_rec.estimate_detail_id := l_estimate_detail_id;
1031 --l_cost_rec.org_id := 204;
1032 l_cost_rec.inventory_org_id := l_organization_id;
1033 l_cost_rec.transaction_date := sysdate;
1034 l_cost_rec.extended_cost := l_expense_amount;
1035
1036 cs_cost_details_pub.create_cost_details(
1037 p_api_version => 1.0,
1038 x_return_status => l_return_status,
1039 x_msg_count => l_msg_count,
1040 x_object_version_number => l_object_version,
1041 x_msg_data => l_msg_data,
1042 x_cost_id => l_cost_id,
1043 p_Cost_Rec => l_cost_rec);
1044 if l_RETURN_STATUS = 'S' then -- success
1045 l_charges_interface_status := 'SUCCEEDED';
1046 l_msg_data := null;
1047 csf_debrief_lines_pkg.update_row(
1048 p_debrief_line_id => l_debrief_line_id,
1049 p_error_text => substr(l_msg_data,1,2000),
1050 p_charge_upload_status => l_charges_interface_status,
1051 p_last_updated_by => fnd_global.user_id,
1052 p_last_update_date => sysdate,
1053 p_last_update_login => fnd_global.login_id);
1054 elsif l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then --Expected Error
1055 retcode := 1;
1056 l_processed_flag := 'COMPLETED W/ERRORS';
1057 if l_msg_count > 0 then
1058 FOR counter IN REVERSE 1..l_msg_count LOOP
1059 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
1060 l_position := instr(l_msg_data, 'ERROR_EXPLANATION');
1061 l_msg_data := substr(l_msg_data, l_position);
1062 FND_FILE.put_line(FND_FILE.log,l_msg_data);
1063 end loop;
1064 end if;
1065 l_inv_interface_status := 'FAILED';
1066 end if;
1067 end if;
1068
1069 -------- Added for bug 3608969
1070
1071 if l_ib_interface_status = 'FAILED' or l_charges_interface_status = 'FAILED' or
1072 l_inv_interface_status = 'FAILED' then
1073 rollback;
1074 if l_charges_interface_status <> 'FAILED' then
1075 l_charges_interface_status := fnd_api.g_miss_char;
1076 end if;
1077 if l_ib_interface_status <> 'FAILED' then
1078 l_ib_interface_status := fnd_api.g_miss_char;
1079 end if;
1080 if l_inv_interface_status <> 'FAILED' then
1081 l_inv_interface_status := fnd_api.g_miss_char;
1082 end if;
1083 csf_debrief_lines_pkg.update_row(
1084 p_debrief_line_id => l_debrief_line_id,
1085 p_error_text => substr(l_msg_data,1,2000),
1086 p_charge_upload_status => l_charges_interface_status,
1087 p_ib_update_status => l_ib_interface_status,
1088 p_spare_update_status => l_inv_interface_status,
1089 p_last_updated_by => fnd_global.user_id,
1090 p_last_update_date => sysdate,
1091 p_last_update_login => fnd_global.login_id);
1092
1093 else
1094 if nvl(l_spare_update_status,' ') <> 'SUCCEEDED' and l_billing_type='M'
1095 and (l_charges_interface_status= 'SUCCEEDED' or l_charge_upload_status='SUCCEEDED')
1096 and ( (l_ib_interface_status = 'SUCCEEDED' and l_trackable='Y')
1097 or (l_ib_update_status = 'SUCCEEDED' and l_trackable ='Y')
1098 or l_trackable ='N' or l_trackable is null ) then
1099 --we tried to update inventory
1100 csf_debrief_lines_pkg.update_row(
1101 p_debrief_line_id => l_debrief_line_id,
1102 p_error_text => null,
1103 p_spare_update_status => l_inv_interface_status,
1104 p_last_updated_by => fnd_global.user_id,
1105 p_last_update_date => sysdate,
1106 p_last_update_login => fnd_global.login_id);
1107
1108 end if;
1109 end if;
1110
1111 else --- assignment status is not completed => we have to generate in progress charges
1112
1113 l_processed_flag := 'UNPROCESSED';
1114
1115 ------------------------------------------------------------------------
1116 --GENERATING IN PROGRESS CHARGE LINES ----
1117 -------------------------------------------------------------------------
1118 if nvl(l_charge_upload_status, ' ') <> 'SUCCEEDED' and l_create_charge_flag = 'Y' Then -- added for bug 3538214
1119
1120 csf_debrief_charges.create_charges(
1121 p_original_source_id => l_original_source_id ,
1122 p_original_source_code => l_original_source_code ,
1123 p_incident_id => l_incident_id ,
1124 p_business_process_id => l_business_process_id ,
1125 p_line_category_code => l_line_category_code ,
1126 p_source_code => 'SD' ,
1127 p_source_id => l_debrief_line_id ,
1128 p_inventory_item_id => l_inventory_item_id ,
1129 p_item_revision => l_revision ,
1130 p_unit_of_measure_code => l_uom_code ,
1131 p_quantity => l_quantity ,
1132 p_txn_billing_type_id => l_txn_billing_type_id ,
1133 p_customer_product_id => l_charges_instance_id ,
1134 p_installed_cp_return_by_date => l_installed_cp_return_by_date,
1135 p_after_warranty_cost => l_after_warranty_cost ,
1136 p_currency_code => l_currency_code ,
1137 p_return_reason_code => l_return_reason_code ,
1138 p_inventory_org_id => l_organization_id ,
1139 p_serial_number => l_item_serial_number ,
1140 p_final_charge_flag => 'N' ,
1141 p_labor_start_date => l_labor_start_date ,
1142 p_labor_end_date => l_labor_end_date ,
1143 p_transaction_type_id => l_transaction_type_id,
1144 x_return_status => l_return_status ,
1145 x_msg_count => l_msg_count ,
1146 x_msg_data => l_msg_data );
1147
1148
1149 if l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then --Expected Error
1150 retcode := 1;
1151
1152 if l_msg_count > 0 then
1153 FOR counter IN REVERSE 1..l_msg_count LOOP
1154 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
1155 FND_FILE.put_line(FND_FILE.log,l_msg_data);
1156 end loop;
1157 end if;
1158 else
1159 l_msg_data := null;
1160 end if;
1161
1162 csf_debrief_lines_pkg.update_row(
1163 p_debrief_line_id => l_debrief_line_id,
1164 p_error_text => substr(l_msg_data,1,2000));
1165
1166 end if; -- end of charges status check
1167
1168 end if;--end of deciding if we have to generate in progress or final charges
1169
1170 end loop;
1171 close c_lines;
1172
1173 update csf_debrief_headers
1174 set processed_flag = nvl(l_processed_flag,'UNPROCESSED')
1175 where debrief_header_id = l_debrief_header_id;
1176 commit;
1177
1178 If l_return_status = FND_API.G_RET_STS_ERROR and l_record_lock = 'Y' THEN
1179 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_CONC_PROG_RECLOCK');
1180 FND_MESSAGE.Set_Token('DEBRIEF_NUMBER',l_debrief_number);
1181 FND_MSG_PUB.ADD;
1182 l_msg_data := FND_MSG_PUB.Get(p_msg_index => fnd_msg_pub.G_LAST,
1183 p_encoded => FND_API.G_FALSE);
1184 fnd_file.put_line(fnd_file.log, l_msg_data);
1185 l_conc_result := fnd_concurrent.set_completion_status('WARNING','Warning');
1186 exit;
1187 end if;
1188 end loop;
1189
1190 if p_debrief_header_id is not null and p_incident_id is null then
1191 close c_header;
1192 elsif p_incident_id is not null then -- Changed for bug 3648213
1193 close c_header_inc;
1194 else
1195 close c_headers;
1196 end if;
1197
1198 exception
1199 when e_no_header_id then
1200 fnd_message.set_name('CSF', 'CSF_DEBRIEF_MISSING_HEADER_ID');
1201 fnd_msg_pub.add;
1202 fnd_msg_pub.get(1,FND_API.G_FALSE,l_msg_data,l1);
1203 FND_FILE.put_line(FND_FILE.log,l_msg_data);
1204 retcode := 2;
1205 errbuf := l_msg_data;
1206
1207 when others then
1208 retcode := 2;
1209 errbuf := sqlerrm;
1210 l_message := errbuf;
1211 FND_FILE.put_line(FND_FILE.log,l_message);
1212
1213 end;
1214
1215 ------------------------------------------------------------------------------------------------------------------
1216 PROCEDURE Form_Call
1217 -----------------------------------------------------------------------------------------------------------------
1218 (
1219 p_api_version IN NUMBER,
1220 p_debrief_header_id IN NUMBER
1221 ) is
1222 l_request_id Number ;
1223 Begin
1224
1225 l_request_id := fnd_request.submit_request('CSF',
1226 'CSFUPDATE',
1227 'CSF:Update Debrief Lines',
1228 null,
1229 FALSE,
1230 2.0,
1231 p_debrief_header_id);
1232
1233 End;
1234
1235 ---------------------------------------------------------------------------------------------------
1236 procedure relieve_reservations(p_task_assignment_id IN NUMBER,
1237 ----------------------------------------------------------------------------------------------------
1238 x_return_status OUT NOCOPY varchar2,
1239 x_msg_data OUT NOCOPY varchar2,
1240 x_msg_count OUT NOCOPY varchar2) IS
1241 cursor reservations
1242 IS
1243 Select crld.source_id,crld.req_line_detail_id,crh.requirement_header_id
1244 from csp_requirement_headers crh,
1245 csp_requirement_lines crl,
1246 csp_req_line_details crld
1247 where crh.task_assignment_id = p_task_assignment_id
1248 and crl.requirement_header_id = crh.requirement_header_id
1249 and crld.requirement_line_id = crl.requirement_line_id
1250 and crld.source_type = 'RES';
1251
1252 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
1253 l_serial_number inv_reservation_global.serial_number_tbl_type;
1254 l_relieved_quantity NUMBER;
1255 l_remaining_quantity NUMBER;
1256 l_req_line_detail_id NUMBER;
1257 l_requirement_header_id number := null;
1258 l_req_header_id number := null;
1259 BEGIN
1260
1261 OPEN reservations;
1262 LOOP
1263 FETCH reservations INTO l_reservation_rec.reservation_id,l_req_line_detail_id,l_req_header_id;
1264 EXIT WHEN reservations % NOTFOUND;
1265 l_requirement_header_id := l_req_header_id;
1266 l_relieved_quantity := null;
1267 inv_reservation_pub.relieve_reservation
1268 (
1269 p_api_version_number => 1.0
1270 ,p_init_msg_lst =>
1271 fnd_api.g_false
1272 ,x_return_status =>
1273 x_return_status
1274 ,x_msg_count =>
1275 x_msg_count
1276 ,x_msg_data =>
1277 x_msg_data
1278 ,p_rsv_rec =>
1279 l_reservation_rec
1280 ,p_primary_relieved_quantity =>
1281 l_relieved_quantity
1282 ,p_relieve_all =>
1283 fnd_api.g_true
1284 ,p_original_serial_number =>
1285 l_serial_number
1286 ,p_validation_flag =>
1287 fnd_api.g_true
1288 ,x_primary_relieved_quantity =>
1289 l_relieved_quantity
1290 ,x_primary_remain_quantity =>
1291 l_remaining_quantity
1292 );
1293 If x_return_status <> FND_API.G_RET_STS_SUCCESS then
1294 exit;
1295 end if;
1296 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1297 CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_line_detail_id);
1298 END IF;
1299 END LOOP;
1300 CLOSE reservations;
1301 if l_requirement_header_id is not null then
1302 csp_requirement_headers_pkg.update_row(
1303 p_requirement_header_id => l_requirement_header_id,
1304 p_open_requirement => 'N');
1305 end if;
1306 END relieve_reservations;
1307
1308 PROCEDURE web_Call
1309 (
1310 p_api_version IN NUMBER,
1311 p_task_assignment_id IN NUMBER
1312 ) is
1313 cursor get_debrief_header is
1314 select debrief_header_id
1315 from csf_debrief_headers
1316 where task_assignment_id = p_task_assignment_id;
1317
1318 l_debrief_header_id NUMBER;
1319 l_request_id NUMBER;
1320
1321 begin
1322 for gdh In get_debrief_header LOOP
1323 l_debrief_header_id := gdh.debrief_header_id;
1324 --
1325 update csf_debrief_headers
1326 set processed_flag = 'PENDING'
1327 where debrief_header_id = l_debrief_header_id;
1328 --
1329 END LOOP;
1330 IF l_debrief_header_id IS NOT NULL THEN
1331 l_request_id := fnd_request.submit_request('CSF',
1332 'CSFUPDATE',
1333 'CSF:Update Debrief Lines',
1334 null,
1335 FALSE,
1336 1.0,
1337 l_debrief_header_id);
1338
1339 END IF;
1340 end web_Call;
1341
1342 PROCEDURE DEBRIEF_STATUS_CHECK (
1343 p_incident_id in number,
1344 p_api_version in number,
1345 p_validation_level in number,
1346 x_debrief_status out nocopy debrief_status_tbl_type,
1347 x_return_status out nocopy varchar2,
1348 x_msg_count out nocopy number,
1349 x_msg_data out nocopy varchar2) IS
1350
1351 l_processed_flag csf_debrief_headers.processed_flag%type;
1352 l_task_number jtf_tasks_b.task_number%type;
1353 l_debrief_status_rec debrief_status_rec_type;
1354 l_debrief_status_tbl debrief_status_tbl_type;
1355
1356 cursor get_debrief_status is
1357 select jtb.task_id,
1358 jtb.task_number,
1359 cdh.debrief_header_id,
1360 cdh.debrief_number,
1361 decode(processed_flag,'PENDING','P','E') debrief_status
1362 from csf_debrief_headers cdh,
1363 jtf_task_assignments jta,
1364 jtf_tasks_b jtb
1365 where processed_flag in ('PENDING','COMPLETED W/ERRORS')
1366 and jta.task_assignment_id = cdh.task_assignment_id
1367 and jtb.task_id = jta.task_id
1368 and jtb.source_object_type_code = 'SR'
1369 and jtb.source_object_id = p_incident_id
1370 union all
1371 select jtb.task_id,
1372 jtb.task_number,
1373 cdh.debrief_header_id,
1374 cdh.debrief_number,
1375 decode(processed_flag,'PENDING','P','E') debrief_status
1376 from csd_repairs cr,
1377 jtf_tasks_b jtb,
1378 jtf_task_assignments jta,
1379 csf_debrief_headers cdh
1380 where jtb.source_object_id = cr.repair_line_id
1381 and jtb.source_object_type_code = 'DR'
1382 and jta.task_id = jtb.task_id
1383 and cdh.task_assignment_id = jta.task_assignment_id
1384 and cdh.processed_flag in ('PENDING','COMPLETED W/ERRORS')
1385 and cr.incident_id = p_incident_id;
1386
1387 BEGIN
1388 x_return_status := FND_API.G_RET_STS_SUCCESS;
1389 for cr in get_debrief_status loop
1390 l_debrief_status_tbl(get_debrief_status%rowcount) := cr;
1391 if p_validation_level = 0 and get_debrief_status%rowcount = 1 then
1392 exit;
1393 end if;
1394 end loop;
1395 if l_debrief_status_tbl.count > 0 then
1396 x_debrief_status := l_debrief_status_tbl;
1397 x_return_status := fnd_api.g_ret_sts_error;
1398 end if;
1399 exception when others then
1400 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1401 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1402 fnd_msg_pub.add;
1403 x_return_status := fnd_api.g_ret_sts_unexp_error;
1404 END DEBRIEF_STATUS_CHECK ;
1405
1406 End Csf_Debrief_Update_pkg;
1407