[Home] [Help]
PACKAGE BODY: APPS.CSF_DEBRIEF_UPDATE_PKG
Source
1 PACKAGE BODY CSF_DEBRIEF_UPDATE_PKG as
2 /* $Header: csfuppdb.pls 120.34.12020000.9 2013/04/09 10:35:49 shadas 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 log(p_procedure in varchar2,p_message in varchar2) as
22 begin
23 -- dbms_output.put_line(p_procedure||' - '||p_message);
24 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
25 fnd_log.string(fnd_log.level_statement,
26 'csf.plsql.csf_debrief_update_pkg.'||p_procedure,
27 p_message);
28 end if;
29 end;
30
31 PROCEDURE Record_Lock
32 (p_debrief_line_id IN Number,
33 x_return_status OUT NOCOPY VARCHAR2
34 ) is
35 l_charge_upload_status Varchar2(50);
36 l_ib_update_status Varchar2(50);
37 l_spare_update_status Varchar2(50);
38 Begin
39 x_return_status := FND_API.G_RET_STS_SUCCESS;
40 select charge_upload_status,ib_update_status,spare_update_status
41 into l_charge_upload_status,l_ib_update_status,l_spare_update_status
42 from csf_debrief_lines
43 where debrief_line_id = p_debrief_line_id
44 for update nowait;
45 Exception
46 WHEN RECORD_LOCK_EXCEPTION THEN
47 x_return_status := FND_API.G_RET_STS_ERROR ;
48 When OTHERS then
49 x_return_status := FND_API.G_RET_STS_ERROR ;
50 End;
51
52 PROCEDURE main
53 (
54 errbuf OUT NOCOPY VARCHAR2,
55 retcode OUT NOCOPY NUMBER,
56 p_api_version IN NUMBER,
57 p_debrief_header_id IN NUMBER DEFAULT null,
58 p_incident_id IN NUMBER DEFAULT null,
59 p_debrief_line_id IN NUMBER DEFAULT null
60 ) IS
61
62 l_api_name CONSTANT VARCHAR2(30) := 'main';
63 l_api_version CONSTANT NUMBER := 1.0;
64 l_transaction_type_id Number;
65 l_debrief_line_id number ;
66 l_debrief_header_id number ;
67 l_original_source_id number ;
68 l_original_source_code varchar2(10):= 'SR';
69 l_incident_id number ;
70 l_business_process_id number ;
71 l_line_category_code varchar2(10) ;
72 l_return_status varchar2(1) ;
73 l_msg_count number ;
74 l_msg_data varchar2(2000) ;
75 l_rec_status varchar2(10) ;
76 l_charges_interface_status varchar2(20) := null;
77 l_ib_interface_status varchar2(20) := null;
78 l_inv_interface_status varchar2(20) := null;
79 l_interface_status_meaning varchar2(20) ;
80 l_inventory_item_id number ;
81 l_currency_code varchar2(30) ;
82 l_uom_code varchar2(3) ;
83 l_quantity number := 0;
84 l_customer_id number ;
85 l_txn_billing_type number ;
86 l_installed_cp_return_by_date DATE ;
87 l_after_warranty_cost Number := 0;
88 counter Number ;
89 l_time_diff Number ;
90 l_profile_hours Varchar2(20) ;
91 l_return_reason_code Varchar2(30) := null;
92 l_qty Number ;
93 l_revision varchar2(3) ;
94 l_source_type_code Varchar2(10) ;
95 l_repair_line_id Number ;
96 l_service_date Date ;
97 l_date_type varchar2(30) :=
98 fnd_profile.value('CSF_DEBRIEF_TRANSACTION_DATE');
99 l_inv_transaction_type_id Number ;
100 l_organization_id Number ;
101 l_issuing_inventory_org_id Number ;
102 l_receiving_inventory_org_id Number ;
103 l_subinventory_code Varchar2(50) ;
104 l_issuing_sub_inventory_code Varchar2(50) ;
105 l_receiving_sub_inventory_code Varchar2(50) ;
106 l_locator_id Number ;
107 l_issuing_locator_id Number ;
108 l_receiving_locator_id Number ;
109 l_parent_product_id Number ;
110 l_item_serial_number Varchar2(30) ;
111 l_item_lotnumber Varchar2(120) ;
112 l_labor_start_date Date ;
113 l_labor_end_date Date ;
114 l_expense_amount Number ;
115 l_charge_upload_status Varchar2(50) := null ;
116 l_ib_update_status Varchar2(50) ;
117 l_spare_update_status Varchar2(50) ;
118 l_instance_id Number ;
119 l_removed_product_id Number ;
120 l_billing_type Varchar2(3) ;
121 l_debrief_number Varchar2(50) ;
122 l_inv_transaction_header_id Number ;
123 l_inv_transaction_id Number ;
124 l_mesg Varchar2(2000) ;
125 l_msg_dummy Number ;
126 l_transaction_type_id_csi Number ;
127 l_txn_sub_type_id Number ;
128 l_inv_master_organization_id Number :=fnd_api.g_miss_num;
129 l_customer_account_id Number ;
130 l_party_id Number ;
131 l_install_site_use_id Number ;
132 l_ship_site_use_id Number ;
133 l_in_out_flag Varchar2(10) ;
134 l_party_site_id Number ;
135 l_new_instance_id Number ;
136 l_message Varchar2(1000) ;
137 l1 Number ;
138 l_trackable Varchar2(1) ;
139 l_processed_flag Varchar2(50) ;
140 l_charges_instance_id Number ;
141 l_task_assignment_id Number ;
142 l_cancelled_flag Varchar2(1) ;
143 l_rejected_flag Varchar2(1) ;
144 l_completed_flag Varchar2(1) ;
145 l_closed_flag Varchar2(1) ;
146 l_cleanup_done Boolean := FALSE;
147 l_position Number;
148 l_instance_status Varchar(30) :=NULL; --added for bug 3192060
149 l_instance_status_id Number :=9.99E125 ;
150 l_header_id Number; -- added for 3264030
151 l_record_lock Varchar2(1) := 'N'; -- added for bug 3142094
152 l_conc_result Boolean;
153 e_no_header_id Exception;
154 l_item_operational_status_code Varchar2(30);
155 l_create_charge_flag varchar2(1);
156 l_create_cost_flag varchar2(1);
157 l_travel_flag varchar2(1); -- FOR TRAVEL LINES
158 l_header_start_time Date;
159 l_header_end_time Date;
160 l_header_distance_in_km Number;
161
162 l_Cost_Rec cs_cost_details_pub.Cost_Rec_Type ;
163 l_object_version number;
164 l_cost_id number;
165 l_estimate_detail_id number := null;
166 l_programs Number := null;
167 l_assignment_status_id number;
168 l_object_version_number number;
169 l_task_object_version_number number;
170 l_task_status_id number;
171 l_usage_type varchar2(10);
172 l_return_organization_id number;
173 l_return_subinventory_name varchar2(10);
174 l_carrier_code varchar2(25);
175 l_shipping_method varchar2(60);
176 l_shipping_number varchar2(60);
177 l_waybill varchar2(60);
178 l_is_intransit boolean;
179 l_intransit number;
180 l_dest_organization_id number;
181 l_dest_subinventory_name varchar2(10);
182 l_loop number;
183 l_trans_items csp_transactions_pub.Trans_Items_Tbl_Type;
184 l_debrief_error_status number;
185 l_system_id number;
186 l_recovered_instance_id number;
187
188 l_err_item_id number;
189 l_err_item_uom varchar2(3);
190 l_err_item_qty number;
191 l_res_item_id number;
192 l_res_item_uom varchar2(3);
193 l_res_item_qty number;
194 l_qty_to_relieve number;
195
196 l_expenditure_org_id number;
197 l_project_id number;
198 l_project_task_id number;
199
200 cursor c_charge_id(p_debrief_line_id number) is
201 select estimate_detail_id
202 from cs_estimate_details
203 where source_code = 'SD'
204 and source_id = p_debrief_line_id;
205
206 cursor c_debrief_error is
207 select fnd_profile.value('CSF_DEBRIEF_ERROR_STATUS'),
208 jta.assignment_status_id,
209 jta.object_version_number
210 from jtf_task_assignments jta
211 where jta.task_assignment_id = l_task_assignment_id;
212
213 cursor c_lines (p_debrief_header_id Number) is
214 select cdl.debrief_line_id,
215 cdl.service_date,
216 cdl.transaction_type_id,
217 cdl.inventory_item_id,
218 cdl.issuing_inventory_org_id,
219 cdl.receiving_inventory_org_id,
220 cdl.issuing_sub_inventory_code,
221 cdl.receiving_sub_inventory_code,
222 cdl.issuing_locator_id,
223 cdl.receiving_locator_id,
224 cdl.parent_product_id,
225 cdl.removed_product_id,
226 cdl.item_serial_number,
227 cdl.item_revision,
228 cdl.item_lotnumber,
229 cdl.uom_code,
230 cdl.quantity,
231 cdl.labor_start_date,
232 cdl.labor_end_date,
233 cdl.expense_amount,
234 cdl.currency_code,
235 cdl.charge_upload_status,
236 cdl.ib_update_status,
237 cdl.spare_update_status,
238 cdl.business_process_id,
239 cdl.return_reason_code,
240 cdl.instance_id,
241 cdl.status_of_received_part, --added for bug 3192060
242 cdl.item_operational_status_code,
243 cdl.usage_type,
244 cdl.return_organization_id,
245 cdl.return_subinventory_name,
246 cdl.carrier_code,
247 cdl.shipping_method,
248 cdl.shipping_number,
249 cdl.waybill,
250 cdl.removed_product_id,
251 cdl.expenditure_org_id,
252 cdl.project_id,
253 cdl.project_task_id
254 from csf_debrief_lines cdl
255 where cdl.debrief_header_id = p_debrief_header_id
256 and nvl(cdl.quantity,-1) <> 0
257 and nvl(cdl.inventory_item_id,-1) <> 9999999
258 and nvl(p_debrief_line_id,cdl.debrief_line_id)=cdl.debrief_line_id;
259
260 cursor c_header (p_header_id Number) is
261 SELECT cdh.debrief_header_id,
262 jtb.source_object_type_code source_type_code,
263 ciab.incident_id ,
264 to_number(null) repair_line_id,
265 ciab.customer_id,
266 ciab.account_id customer_account_id,
267 cdh.debrief_number ,
268 jta.task_assignment_id,
269 jtsb.cancelled_flag,
270 jtsb.rejected_flag,
271 jtsb.completed_flag,
272 jtsb.closed_flag,
273 cdh.travel_start_time, --FOR TRAVEL LINES
274 cdh.travel_end_time, ciab.system_id system_id
275 from
276 JTF_TASK_STATUSES_B jtsb,
277 CSF_DEBRIEF_HEADERS cdh,
278 JTF_TASKS_B jtb,
279 JTF_TASK_ASSIGNMENTS jta,
280 cs_incidents_all_b ciab
281 WHERE cdh.task_assignment_id = jta.task_assignment_id
282 and jta.task_id = jtb.task_id
283 and nvl(jtb.deleted_flag,'N') <> 'Y'
284 and jta.assignment_status_id = jtsb.task_status_id
285 and jta.assignee_role = 'ASSIGNEE'
286 and jtb.source_object_type_code = 'SR'
287 and ciab.incident_id = jtb.source_object_id
288 and cdh.debrief_header_id = p_header_id
289 union all
290 SELECT cdh.debrief_header_id,
291 jtb.source_object_type_code,
292 cr.incident_id,
293 cr.repair_line_id,
294 jtb.customer_id ,
295 jtb.cust_account_id,
296 cdh.debrief_number ,
297 jta.task_assignment_id,
298 jtsb.cancelled_flag,
299 jtsb.rejected_flag,
300 jtsb.completed_flag,
301 jtsb.closed_flag,
302 cdh.travel_start_time, --FOR TRAVEL LINES
303 cdh.travel_end_time, to_number(null) system_id
304 from
305 JTF_TASK_STATUSES_B jtsb,
306 CSF_DEBRIEF_HEADERS cdh,
307 JTF_TASKS_B jtb,
308 JTF_TASK_ASSIGNMENTS jta,
309 csd_repairs cr
310 WHERE cdh.task_assignment_id = jta.task_assignment_id
311 and jta.task_id = jtb.task_id
312 and nvl(jtb.deleted_flag,'N') <> 'Y'
313 and jta.assignment_status_id = jtsb.task_status_id
314 and jta.assignee_role = 'ASSIGNEE'
315 and jtb.source_object_type_code = 'DR'
316 and jtb.source_object_id=cr.repair_line_id
317 and cdh.debrief_header_id = p_header_id ;
318
319 cursor c_header_inc (p_incident_id Number) is
320 SELECT cdh.debrief_header_id,
321 jtb.source_object_type_code source_type_code,
322 ciab.incident_id ,
323 to_number(null) repair_line_id,
324 ciab.customer_id,
325 ciab.account_id customer_account_id,
326 cdh.debrief_number ,
327 jta.task_assignment_id,
328 jtsb.cancelled_flag,
329 jtsb.rejected_flag,
330 jtsb.completed_flag,
331 jtsb.closed_flag,
332 cdh.travel_start_time, --FOR TRAVEL LINES
333 cdh.travel_end_time, ciab.system_id system_id
334 from
335 JTF_TASK_STATUSES_B jtsb,
336 CSF_DEBRIEF_HEADERS cdh,
337 JTF_TASKS_B jtb,
338 JTF_TASK_ASSIGNMENTS jta,
339 cs_incidents_all_b ciab
340 WHERE cdh.task_assignment_id = jta.task_assignment_id
341 and jta.task_id = jtb.task_id
342 and nvl(jtb.deleted_flag,'N') <> 'Y'
343 and jta.assignment_status_id = jtsb.task_status_id
344 and jta.assignee_role = 'ASSIGNEE'
345 and jtb.source_object_type_code = 'SR'
346 and ciab.incident_id = jtb.source_object_id
347 and ciab.incident_id = p_incident_id
348 and cdh.debrief_header_id =
349 nvl(p_debrief_header_id,cdh.debrief_header_id)
350 union all
351 SELECT cdh.debrief_header_id,
352 jtb.source_object_type_code,
353 cr.incident_id,
354 cr.repair_line_id,
355 jtb.customer_id ,
356 jtb.cust_account_id,
357 cdh.debrief_number ,
358 jta.task_assignment_id,
359 jtsb.cancelled_flag,
360 jtsb.rejected_flag,
361 jtsb.completed_flag,
362 jtsb.closed_flag,
363 cdh.travel_start_time, --FOR TRAVEL LINES
364 cdh.travel_end_time, to_number(null) system_id
365 from
366 JTF_TASK_STATUSES_B jtsb,
367 CSF_DEBRIEF_HEADERS cdh,
368 JTF_TASKS_B jtb,
369 JTF_TASK_ASSIGNMENTS jta,
370 csd_repairs cr
371 WHERE cdh.task_assignment_id = jta.task_assignment_id
372 and jta.task_id = jtb.task_id
373 and nvl(jtb.deleted_flag,'N') <> 'Y'
374 and jta.assignment_status_id = jtsb.task_status_id
375 and jta.assignee_role = 'ASSIGNEE'
376 and jtb.source_object_type_code = 'DR'
377 and jtb.source_object_id=cr.repair_line_id
378 and cr.incident_id = p_incident_id
379 and cdh.debrief_header_id =
380 nvl(p_debrief_header_id,cdh.debrief_header_id);
381
382 -- changed above cursor for the bug 3264030
383
384 Cursor c_headers is
385 Select debrief_header_id
386 From csf_debrief_headers
387 Where processed_flag is null or processed_flag <> 'COMPLETED';
388
389 cursor c_cost_charge_flags (p_transaction_type_id number) is
390 select create_cost_flag,
391 create_charge_flag,
392 travel_flag --FOR TRAVEL LINES TO BE REPLACED WITH TRAVEL
393 FLAG
394 from cs_transaction_types
395 where transaction_type_id = p_transaction_type_id;
396
397 cursor c_trans (p_transaction_type_id number,
398 p_inventory_item_id number,
399 p_inventory_org_id number) is
400 select cttv.line_order_category_code,
401 cbtc.billing_category billing_type,
402 ctst.sub_type_id ,
403 ctst.transaction_type_id transaction_type_id_csi
404 from cs_transaction_types_vl cttv,
405 csi_txn_sub_types ctst,
406 csi_txn_types ctt,
407 mtl_system_items_b_kfv msibk,
408 cs_billing_type_categories cbtc
409 where cttv.transaction_type_id = p_transaction_type_id
410 and msibk.material_billable_flag = cbtc.billing_type
411 and msibk.inventory_item_id = p_inventory_item_id
412 and msibk.organization_id = p_inventory_org_id
413 and ctst.cs_transaction_type_id(+) = cttv.transaction_type_id
414 and ctt.source_application_id(+) = 513
415 and ctt.transaction_type_id(+) = ctst.transaction_type_id;
416
417 Cursor c_internal_party_id Is
418 select internal_party_id
419 from csi_install_parameters;
420
421 cursor c_site (p_incident_id number) Is
422 select install_site_use_id,
423 ship_to_site_use_id
424 from cs_incidents_all
425 where incident_id = p_incident_id;
426
427 cursor c_party_site_id (p_install_site_id number) Is
428 select party_site_id
429 from hz_party_site_uses
430 where party_site_use_id = p_install_site_id;
431
432 cursor c_trackable (p_inventory_item_id Number, p_organization_id Number) is
433 select comms_nl_trackable_flag
434 from mtl_system_items
435 where inventory_item_id = p_inventory_item_id
436 and organization_id = p_organization_id;
437
438 Cursor c_status_meaning(p_code Varchar2) Is
439 select meaning
440 from fnd_lookups
441 where lookup_type = 'CSF_INTERFACE_STATUS'
442 and lookup_code = p_code;
443
444 cursor c_programs is
445 select 1
446 from fnd_concurrent_requests fcr,
447 fnd_concurrent_programs fcp
448 where fcr.concurrent_program_id = fcp.concurrent_program_id
449 and fcp.application_id = 513
450 and fcp.concurrent_program_name = 'CSFUPDATE'
451 and fcr.request_id < fnd_global.conc_request_id
452 and fcr.argument2 = to_char(p_debrief_header_id)
453 and fcr.phase_code in ('P','R');
454
455 cursor c_intransit (v_frm_org_id number, v_to_org_id number) is
456 select intransit_type
457 from mtl_interorg_parameters
458 where from_organization_id = v_frm_org_id
459 and to_organization_id = v_to_org_id;
460
461 cursor c_default_defective is
462 select cila.organization_id,cila.subinventory_code
463 from csp_inv_loc_assignments cila,
464 jtf_task_assignments jta
465 where cila.default_code = 'OUT'
466 and cila.resource_type = jta.resource_type_code
467 and cila.resource_id = jta.resource_id
468 and jta.task_assignment_id = l_task_assignment_id;
469
470 cursor c_error_dbf_items (p_debrief_header_id number, p_inventory_item_id number) is
471 SELECT inventory_item_id,
472 uom_code,
473 SUM(quantity) sum_qty
474 FROM csf_debrief_lines
475 WHERE debrief_header_id = p_debrief_header_id
476 AND inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
477 AND NVL(spare_update_status, 'FAILED') <> 'SUCCEEDED'
478 GROUP BY inventory_item_id, uom_code;
479
480 cursor c_item_reservations (p_task_assignment_id number, p_inventory_item_id number) is
481 SELECT mr.inventory_item_id,
482 mr.reservation_uom_code,
483 SUM(mr.reservation_quantity)
484 FROM csp_requirement_headers crh,
485 csp_requirement_lines crl,
486 csp_req_line_details crld,
487 mtl_reservations mr
488 WHERE crh.task_assignment_id = p_task_assignment_id
489 AND crl.requirement_header_id = crh.requirement_header_id
490 AND crld.requirement_line_id = crl.requirement_line_id
491 AND crld.source_type = 'RES'
492 AND mr.reservation_id = crld.source_id
493 AND mr.inventory_item_id = nvl(p_inventory_item_id, mr.inventory_item_id)
494 GROUP BY mr.inventory_item_id, mr.reservation_uom_code;
495
496
497 -----------------------------------BEGIN--------------------------
498
499 begin
500 log('main','Begin');
501 loop
502 l_programs := null;
503 open c_programs;
504 fetch c_programs into l_programs;
505 close c_programs;
506 log('main','l_programs:'||l_programs);
507 if l_programs is null then
508 exit;
509 else
510 dbms_lock.sleep(1);
511 end if;
512 end loop;
513
514 retcode := 0;
515 savepoint main;
516
517 log('main','p_debrief_header_id:'||p_debrief_header_id||' p_incident_id:'||p_incident_id||' p_api_version:'||p_api_version);
518 if p_debrief_header_id is null and
519 p_incident_id is null and
520 p_api_version = 2.0 then
521 raise e_no_header_id;
522 end if;
523
524 if p_debrief_header_id is not null and p_incident_id is null then
525 log('main','open c_header');
526 open c_header(p_debrief_header_id);
527 elsif p_incident_id is not null then
528 log('main','open c_header_inc');
529 open c_header_inc(p_incident_id);
530 else
531 log('main','open c_headers');
532 open c_headers;
533 end if;
534
535 l_processed_flag := 'COMPLETED';
536 loop
537 log('main','in loop');
538 l_cleanup_done := FALSE; -- moved inside loop for bug 3549864
539 if p_debrief_header_id is not null and p_incident_id is null then
540 log('main','fetch c_header');
541 fetch c_header into l_debrief_header_id,
542 l_source_type_code,
543 l_incident_id,
544 l_repair_line_id,
545 l_customer_id,
546 l_customer_account_id,
547 l_debrief_number,
548 l_task_assignment_id,
549 l_cancelled_flag ,
550 l_rejected_flag ,
551 l_completed_flag ,
552 l_closed_flag ,
553 l_header_start_time,
554 l_header_end_time, l_system_id; --FOR TRAVEL LINES
555
556 exit when c_header%notfound;
557 elsif p_incident_id is not null then -- Changed for bug 3648213
558 log('main','fetch c_header_inc');
559 fetch c_header_inc into l_debrief_header_id,
560 l_source_type_code,
561 l_incident_id,
562 l_repair_line_id,
563 l_customer_id,
564 l_customer_account_id,
565 l_debrief_number,
566 l_task_assignment_id,
567 l_cancelled_flag ,
568 l_rejected_flag ,
569 l_completed_flag ,
570 l_closed_flag ,
571 l_header_start_time,
572 l_header_end_time, l_system_id; --FOR TRAVEL LINES
573
574 exit when c_header_inc%notfound;
575 else
576 log('main','fetch c_headers');
577 l_debrief_header_id := Null ;
578 l_source_type_code := Null ;
579 l_incident_id := Null ;
580 l_repair_line_id := Null ;
581 l_customer_id := Null ;
582 l_customer_account_id := Null ;
583 l_debrief_number := Null ;
584 l_task_assignment_id := Null ;
585 l_cancelled_flag := Null ;
586 l_rejected_flag := Null ;
587 l_completed_flag := Null ;
588 l_closed_flag := Null ;
589 l_header_start_time :=Null;
590 l_header_end_time :=Null; l_system_id:=Null;
591 fetch c_headers into l_header_id ;
592 exit when c_headers%notfound;
593 open c_header(l_header_id) ;
594 log('main','fetch c_header2');
595 fetch c_header into l_debrief_header_id,
596 l_source_type_code,
597 l_incident_id,
598 l_repair_line_id,
599 l_customer_id,
600 l_customer_account_id,
601 l_debrief_number,
602 l_task_assignment_id,
603 l_cancelled_flag ,
604 l_rejected_flag ,
605 l_completed_flag ,
606 l_closed_flag ,
607 l_header_start_time ,
608 l_header_end_time, l_system_id;
609 close c_header;
610 end if;
611 log('main','p_debrief_line_id');
612 if p_debrief_line_id is not null then
613 l_completed_flag := 'Y';
614 end if;
615 l_header_distance_in_km :=0; --For Travel lines
616
617 /*
618 -- Bug 14511964. All reservations will be relieved at line level only
619 if p_debrief_line_id is null then
620 log('main','relieve_reservations');
621 relieve_reservations ( l_task_assignment_id ,
622 l_return_status,
623 l_msg_data ,
624 l_msg_count );
625 log('main','l_return_status:'||l_return_status);
626 end if;
627 */
628
629 -------------------------------------------------------------------
630 --start cleanup in progress charge lines for the service request--
631 ------------------------------------------------------------------
632 if not l_cleanup_done then
633 log('main','calling delete_in_progress_charges');
634 Cs_service_billing_engine_pvt.Delete_In_Progress_Charges(
635 P_Api_Version_Number => 1.0,
636 P_Init_Msg_List => FND_API.G_FALSE,
637 P_Commit => FND_API.G_FALSE,
638 p_incident_id => l_incident_id,
639 p_debrief_header_id => l_debrief_header_id, -- added for bug 3549864
640 x_return_status => l_return_status,
641 x_msg_count => l_msg_count,
642 x_msg_data => l_msg_data);
643 log('main','l_return_status:'||l_return_status);
644 commit;
645 l_cleanup_done := TRUE;
646 End If; --cleanup
647 ----------------------------------------------------------------
648 --end cleanup in progress charge lines for the service request--
649 ----------------------------------------------------------------
650
651
652 l_msg_data := null;
653 log('main','l_source_type_code:'||l_source_type_code);
654 if l_source_type_code ='SR' Then
655 l_original_source_id := l_incident_id;
656 l_original_source_code := 'SR';
657 end if;
658 if l_source_type_code ='DR' Then
659 l_original_source_id := l_repair_line_id;
660 l_original_source_code := 'DR';
661 end if;
662 log('main','l_original_source_id:'||l_original_source_id||' l_original_source_code:'||l_original_source_code);
663
664 log('main','open c_lines');
665 open c_lines (l_debrief_header_id);
666 loop
667 log('main','in c_lines loop');
668 savepoint before_fetch;
669
670 l_after_warranty_cost := null;
671 fetch c_lines into l_debrief_line_id,
672 l_service_date,
673 l_transaction_type_id,
674 l_inventory_item_id,
675 l_issuing_inventory_org_id,
676 l_receiving_inventory_org_id,
677 l_issuing_sub_inventory_code,
678 l_receiving_sub_inventory_code,
679 l_issuing_locator_id,
680 l_receiving_locator_id,
681 l_parent_product_id,
682 l_removed_product_id,
683 l_item_serial_number,
684 l_revision,
685 l_item_lotnumber,
686 l_uom_code,
687 l_quantity,
688 l_labor_start_date,
689 l_labor_end_date,
690 l_expense_amount,
691 l_currency_code,
692 l_charge_upload_status,
693 l_ib_update_status,
694 l_spare_update_status,
695 l_business_process_id,
696 l_return_reason_code,
697 l_instance_id,
698 l_instance_status, --added for bug 3192060
699 l_item_operational_status_code,
700 l_usage_type,
701 l_return_organization_id,
702 l_return_subinventory_name,
703 l_carrier_code,
704 l_shipping_method,
705 l_shipping_number,
706 l_waybill,
707 l_recovered_instance_id,
708 l_expenditure_org_id,
709 l_project_id,
710 l_project_task_id;
711
712 exit when c_lines%notfound;
713 --added the following for bug3142094
714 l_return_status := null;
715 l_record_lock := 'N';
716 log('main','calling record lock:'||l_debrief_line_id);
717 record_lock(l_debrief_line_id, l_return_status) ;
718 log('main','l_return_status:'||l_return_status);
719
720 If l_return_status = FND_API.G_RET_STS_ERROR THEN
721 rollback to before_fetch;
722 l_processed_flag := 'COMPLETED W/ERRORS';
723 l_record_lock := 'Y';
724 exit ;
725 end if;
726
727 --added the following for bug3246952
728 l_return_status := null;
729 l_charges_interface_status := null;
730 l_ib_interface_status := null;
731 l_inv_interface_status := null;
732 l_msg_data := null;
733 l_charges_instance_id := fnd_api.g_miss_num;
734 l_organization_id := nvl(l_receiving_inventory_org_id,
735 nvl(l_issuing_inventory_org_id,
736 cs_std.get_item_valdn_orgzn_id));
737 l_subinventory_code := nvl(l_issuing_sub_inventory_code,
738 l_receiving_sub_inventory_code);
739
740 log('main','l_usage_type:'||l_usage_type);
741 if nvl(l_usage_type,'USED') in ('UNUSED','DOA') then -- Unused or DOA
742 log('main','l_cancelled_flag:'||l_cancelled_flag||'l_rejected_flag:'||l_rejected_flag||'l_completed_flag:'||l_completed_flag||'l_closed_flag:'||l_closed_flag);
743 log('main','l_spare_update_status:'||l_spare_update_status);
744 if (l_cancelled_flag ='Y' or l_rejected_flag='Y'
745 or l_completed_flag='Y' or l_closed_flag='Y')
746 and nvl(l_spare_update_status,'N') <> 'SUCCEEDED' then
747 l_loop := 1;
748 if nvl(l_usage_type,'USED') = 'DOA' then
749 l_loop := 2; -- Two transactions required for DOA
750 end if;
751 -- bug # 14087063
752 log('main','relieve_reservations');
753 relieve_reservations ( l_task_assignment_id ,
754 l_inventory_item_id,
755 l_quantity,
756 l_uom_code,
757 l_return_status,
758 l_msg_data ,
759 l_msg_count );
760 log('main','l_return_status:'||l_return_status);
761 -- end of bug # 14087063
762 for i in 1..l_loop loop
763 if l_usage_type = 'DOA' and i = 1 then
764 open c_default_defective;
765 fetch c_default_defective into l_dest_organization_id,
766 l_dest_subinventory_name;
767 close c_default_defective;
768 log('main','l_dest_organization_id:'||l_dest_organization_id||' l_dest_subinventory_name:'||l_dest_subinventory_name);
769 else
770 log('main','l_usage_type:'||l_usage_type);
771 if l_usage_type = 'DOA' then
772 l_organization_id := l_dest_organization_id;
773 l_subinventory_code := l_dest_subinventory_name;
774 end if;
775 l_dest_organization_id := l_return_organization_id;
776 l_dest_subinventory_name := l_return_subinventory_name;
777 log('main','l_dest_organization_id:'||l_dest_organization_id);
778 if l_dest_organization_id is null then
779 l_spare_update_status := 'SUCCEEDED';
780 exit; --no return to specified
781 end if;
782 end if;
783 l_trans_items(1).inventory_item_id := l_inventory_item_id;
784 l_trans_items(1).revision := l_revision;
785 l_trans_items(1).quantity := l_quantity;
786 l_trans_items(1).uom_code := l_uom_code;
787 l_trans_items(1).lot_number := l_item_lotnumber;
788 l_trans_items(1).serial_number := l_item_serial_number;
789 l_trans_items(1).frm_organization_id := l_organization_id;
790 l_trans_items(1).frm_subinventory_code := l_subinventory_code;
791 l_trans_items(1).frm_locator_id :=
792 nvl(l_issuing_locator_id,l_receiving_locator_id);
793 l_trans_items(1).to_organization_id := l_dest_organization_id;
794 l_trans_items(1).to_subinventory_code := l_dest_subinventory_name;
795 l_trans_items(1).to_locator_id := null;
796 l_trans_items(1).to_serial_number := l_item_serial_number;
797 log('main','l_organization_id:'||l_organization_id||' l_dest_organization_id:'||l_dest_organization_id);
798 if l_organization_id <> l_dest_organization_id then
799 log('main','open c_intransit');
800 open c_intransit(l_organization_id, l_dest_organization_id);
801 fetch c_intransit into l_intransit;
802 close c_intransit;
803 log('main','l_intransit:'||l_intransit);
804 if l_intransit = 1 then
805 l_is_intransit := FALSE;
806 elsif l_intransit = 2 then
807 l_is_intransit := TRUE;
808 end if;
809 l_trans_items(1).waybill_airbill := l_waybill;
810 l_trans_items(1).freight_code := l_shipping_method;
811 l_trans_items(1).shipment_number := l_shipping_number;
812 log('main','calling transact_intorg_transfer');
813 csp_transactions_pub.transact_intorg_transfer(
814 1.0,
815 fnd_api.g_false,
816 fnd_api.g_false,
817 l_trans_items,
818 l_is_intransit,
819 l_return_status,
820 l_msg_count,
821 l_msg_data);
822 log('main','l_return_status:'||l_return_status);
823 if l_return_status = 'S' then
824 l_spare_update_status := 'SUCCEEDED';
825 else
826 l_spare_update_status := 'FAILED';
827 end if;
828 log('main','l_spare_update_status:'||l_spare_update_status);
829 elsif l_organization_id = l_dest_organization_id then
830 log('main','calling transact_subinv_transfer');
831 csp_transactions_pub.transact_subinv_transfer(
832 1.0,
833 fnd_api.g_false,
834 fnd_api.g_false,
835 l_trans_items,
836 l_return_status,
837 l_msg_count,
838 l_msg_data);
839 log('main','l_return_status:'||l_return_status);
840 if l_return_status = 'S' then
841 l_spare_update_status := 'SUCCEEDED';
842 else
843 l_spare_update_status := 'FAILED';
844 end if;
845 end if;
846 end loop;
847 log('main','l_spare_update_status:'||l_spare_update_status);
848 -- DOA loop can change proepr value of l_organization_id
849 l_organization_id := nvl(l_receiving_inventory_org_id,
850 nvl(l_issuing_inventory_org_id,
851 cs_std.get_item_valdn_orgzn_id));
852 l_subinventory_code := nvl(l_issuing_sub_inventory_code,
853 l_receiving_sub_inventory_code);
854
855 csf_debrief_lines_pkg.update_row(
856 p_debrief_line_id => l_debrief_line_id,
857 p_error_text => null,
858 p_charge_upload_status => l_spare_update_status,
859 p_spare_update_status => l_spare_update_status,
860 p_last_updated_by => fnd_global.user_id,
861 p_last_update_date => sysdate,
862 p_last_update_login => fnd_global.login_id);
863 end if;
864 else
865 --we use c_trans cursor only when we have item number
866 --hehxx
867
868 open c_cost_charge_flags(l_transaction_type_id);
869 fetch c_cost_charge_flags into l_create_cost_flag,
870 l_create_charge_flag,
871 l_travel_flag;
872 close c_cost_charge_flags;
873 log('main','l_create_cost_flag:'||l_create_cost_flag||' l_create_charge_flag:'||l_create_charge_flag||' l_travel_flag:'||l_travel_flag);
874 if nvl(l_create_charge_flag,'Y') = 'N' then
875 l_charge_upload_status := 'SUCCEEDED';
876 end if;
877 log('main','l_charge_upload_status:'||l_charge_upload_status);
878 log('main','l_billing_type:'||l_billing_type);
879 --FOR TRAVEL LINES START
880 if (nvl(l_travel_flag,'N')='Y' and nvl(l_create_charge_flag,'N')='Y') then
881 if l_billing_type ='L' then
882 if l_header_start_time is not null and l_labor_start_date is not null
883 and l_header_start_time > l_labor_start_date then
884 l_header_start_time := l_labor_start_date;
885 end if;
886 if l_header_end_time is not null and l_labor_end_date is not null
887 and l_header_end_time < l_labor_end_date then
888 l_header_end_time := l_labor_end_date;
889 end if;
890 end if;
891 if l_billing_type='E' then
892
893 l_header_distance_in_km := l_header_distance_in_km +
894 inv_convert.inv_um_convert
895 (Null,
896 2,
897 l_quantity,
898 l_uom_code,
899 'KM',
900 NULL, NULL);
901 update csf_debrief_headers
902 set travel_distance_in_km = l_header_distance_in_km,
903 travel_start_time = l_header_start_time,
904 travel_end_time = l_header_end_time
905 where debrief_header_id = l_debrief_header_id;
906 end if;
907 end if;
908 --FOR TRAVEL LINES END
909
910 if (l_inventory_item_id is not null
911 and l_inventory_item_id <> fnd_api.g_miss_num) Then
912
913 open c_trans (l_transaction_type_id,
914 l_inventory_item_id,
915 l_organization_id);
916 fetch c_trans into l_line_category_code,
917 l_billing_type,
918 l_txn_sub_type_id ,
919 l_transaction_type_id_csi;
920 close c_trans;
921 else --this is for labor lines without item number
922 -- l_line_category_code := 'ORDER'; Commented for bug 7208532
923 l_line_category_code := null;
924 l_billing_type := null;
925 l_txn_sub_type_id := null;
926 l_transaction_type_id_csi := null;
927 -- added for bug 3456295
928 l_subinventory_code := null;
929 l_organization_id := null;
930 end if;
931
932
933
934 If (l_billing_type = 'M') Then
935 ----
936 l_installed_cp_return_by_date := sysdate;
937 l_party_id := l_customer_id;
938 log('main','l_line_category_code:'||l_line_category_code);
939 if (l_line_category_code = 'RETURN') then --FS Recovery
940 l_inv_transaction_type_id := 94; --RECEIVING
941 l_subinventory_code := l_receiving_sub_inventory_code;
942 l_locator_id := l_receiving_locator_id;
943 l_organization_id := l_receiving_inventory_org_id;
944 l_in_out_flag:='IN';
945
946 open c_internal_party_id;
947 fetch c_internal_party_id into l_party_id;
948 close c_internal_party_id;
949
950 l_charges_instance_id := l_instance_id;
951
952 else --FS Usage
953 l_inv_transaction_type_id := 93; --ISSUING
954 l_subinventory_code := l_issuing_sub_inventory_code;
955 l_locator_id := l_issuing_locator_id;
956 l_organization_id := l_issuing_inventory_org_id;
957 l_in_out_flag:='OUT';
958
959 open c_site(l_incident_id);
960 fetch c_site into l_install_site_use_id, l_ship_site_use_id;
961 close c_site;
962
963 If l_install_site_use_id is not null Then
964 l_party_site_id := l_install_site_use_id;
965 Else
966 open c_party_site_id (l_ship_site_use_id);
967 fetch c_party_site_id into l_party_site_id;
968 close c_party_site_id;
969 End If;
970 l_charges_instance_id := null;
971
972 end if;
973
974 open c_trackable (l_inventory_item_id, l_organization_id);
975 fetch c_trackable into l_trackable;
976 close c_trackable ;
977 log('main','l_trackable:'||l_trackable);
978 Elsif (l_billing_type = 'E') Then
979 l_installed_cp_return_by_date := NULL;
980
981 if l_expense_amount is null
982 then l_after_warranty_cost := null;
983 end if;
984 if l_quantity is null
985 then l_after_warranty_cost := l_expense_amount;
986 l_quantity :=1;
987 end if;
988 -- added for bug 3456295
989 l_subinventory_code := null;
990 l_organization_id := null;
991
992 -- do not pass NULL in case of l_line_category_code = 'RETURN'
993 -- bug # 6851448
994 if (l_line_category_code <> 'RETURN') then
995 l_return_reason_code := null;
996 end if;
997
998 Elsif (l_billing_type = 'L') Then
999 l_installed_cp_return_by_date := NULL;
1000 if l_quantity is null Then
1001 l_time_diff := to_date(l_labor_end_date,'dd-mm-rrrr hh24:mi:ss')
1002 - to_date(l_labor_start_date,'dd-mm-rrrr hh24:mi:ss');
1003
1004 l_time_diff := l_time_diff * 24;
1005 l_profile_hours := fnd_profile.value('CSF_UOM_HOURS');
1006 l_quantity := inv_convert.inv_um_convert
1007 (Null,
1008 2,
1009 l_time_diff,
1010 l_profile_hours,
1011 l_uom_code,
1012 NULL,NULL);
1013
1014 End If;
1015 -- added for bug 3456295
1016 l_subinventory_code := null;
1017 l_organization_id := null;
1018
1019 -- do not pass NULL in case of l_line_category_code = 'RETURN'
1020 -- bug # 6851448
1021 if (l_line_category_code <> 'RETURN') then
1022 l_return_reason_code := null;
1023 end if;
1024
1025 end if; --billing_type='M'
1026
1027 -------------------------------------------------------
1028 --decide if we generate final or in progress charges---
1029 -------------------------------------------------------
1030 if (l_cancelled_flag ='Y' or l_rejected_flag='Y'
1031 or l_completed_flag='Y' or l_closed_flag='Y')
1032 Then
1033 -- we have to generate final charges
1034
1035
1036 ------------------------------------------------------------------------------
1037 -- UPDATE CHARGES --
1038 ----------------------------------------------------------------------------
1039 log('main','l_charge_upload_status:'||l_charge_upload_status||' l_create_charge_flag:'||l_create_charge_flag);
1040 if nvl(l_charge_upload_status, ' ') <> 'SUCCEEDED'
1041 and l_create_charge_flag = 'Y' then
1042 log('main','calling create_charges');
1043 csf_debrief_charges.create_charges(
1044 p_original_source_id => l_original_source_id ,
1045 p_original_source_code => l_original_source_code ,
1046 p_incident_id => l_incident_id ,
1047 p_business_process_id => l_business_process_id ,
1048 p_line_category_code => l_line_category_code ,
1049 p_source_code => 'SD' ,
1050 p_source_id => l_debrief_line_id ,
1051 p_inventory_item_id => l_inventory_item_id ,
1052 p_item_revision => l_revision ,
1053 p_unit_of_measure_code => l_uom_code ,
1054 p_quantity => l_quantity ,
1055 p_transaction_type_id => l_transaction_type_id ,
1056 p_customer_product_id => l_charges_instance_id ,
1057 p_installed_cp_return_by_date => l_installed_cp_return_by_date,
1058 p_after_warranty_cost => l_after_warranty_cost ,
1059 p_currency_code => l_currency_code ,
1060 p_return_reason_code => l_return_reason_code ,
1061 p_inventory_org_id => l_organization_id ,
1062 p_subinventory => l_subinventory_code ,
1063 p_serial_number => l_item_serial_number ,
1064 p_final_charge_flag => 'Y' ,
1065 p_labor_start_date => l_labor_start_date ,
1066 p_labor_end_date => l_labor_end_date ,
1067 p_expenditure_org_id => l_expenditure_org_id ,
1068 p_project_id => l_project_id ,
1069 p_project_task_id => l_project_task_id ,
1070 x_return_status => l_return_status ,
1071 x_msg_count => l_msg_count ,
1072 x_msg_data => l_msg_data );
1073 log('main','l_return_status:'||l_return_status);
1074 if l_RETURN_STATUS = 'S' then -- success
1075 l_msg_data := null; -- added for bug 3863950
1076 l_charges_interface_status := 'SUCCEEDED';
1077 elsif l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then
1078 retcode := 1;
1079
1080 l_processed_flag := 'COMPLETED W/ERRORS';
1081 if l_msg_count > 0 then
1082 FOR counter IN REVERSE 1..l_msg_count
1083 LOOP
1084 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
1085 FND_FILE.put_line(FND_FILE.log,l_msg_data);
1086 end loop;
1087 end if;
1088 l_charges_interface_status := 'FAILED';
1089
1090 end if;
1091
1092 --------------------------------------------------------------------------
1093 -- START UPDATE CHARGES UPLOAD STATUS COLUMN ----------------------------
1094 --------------------------------------------------------------------------
1095 log('main','l_charges_interface_status:'||l_charges_interface_status);
1096 if l_charges_interface_status = 'FAILED' then
1097 rollback to before_fetch;
1098
1099 if l_charges_interface_status <> 'FAILED' then
1100 l_charges_interface_status := fnd_api.g_miss_char;
1101 end if;
1102 log('main','calling update_row when failed');
1103 csf_debrief_lines_pkg.update_row(
1104 p_debrief_line_id => l_debrief_line_id,
1105 p_error_text => substr(l_msg_data,1,2000),
1106 p_charge_upload_status => l_charges_interface_status,
1107 p_last_updated_by => fnd_global.user_id,
1108 p_last_update_date => sysdate,
1109 p_last_update_login => fnd_global.login_id);
1110 commit; -- for bug 13979512
1111 savepoint before_fetch;
1112 else
1113
1114 if l_charges_interface_status = 'SUCCEEDED' then
1115 log('main','calling update_row when succeeded');
1116 csf_debrief_lines_pkg.update_row(
1117 p_debrief_line_id => l_debrief_line_id,
1118 p_error_text => null,
1119 p_charge_upload_status => l_charges_interface_status,
1120 p_last_updated_by => fnd_global.user_id,
1121 p_last_update_date => sysdate,
1122 p_last_update_login => fnd_global.login_id);
1123 end if;
1124 end if;
1125 --------------------------------------------------------------------------
1126 -- END UPDATE CHARGES UPLOAD STATUS COLUMN ----------------------------
1127 -------------------------------------------------------------------------
1128 elsif l_create_charge_flag = 'N' then
1129 l_charges_interface_status := 'SUCCEEDED';
1130 l_charge_upload_status := 'SUCCEEDED';
1131 log('main','calling update_row when create_charge_flag = N');
1132 csf_debrief_lines_pkg.update_row(
1133 p_debrief_line_id => l_debrief_line_id,
1134 p_error_text => null,
1135 p_charge_upload_status => l_charges_interface_status,
1136 p_last_updated_by => fnd_global.user_id,
1137 p_last_update_date => sysdate,
1138 p_last_update_login => fnd_global.login_id);
1139 end if; --charge_upload_status
1140
1141 -------------------------------------------------------------------------
1142 --END UPDATE CHARGE------------------------------------------------------
1143 -------------------------------------------------------------------------
1144
1145 if (l_billing_type = 'M') then
1146 log('main','l_ib_update_status:'||l_ib_update_status||' l_trackable:'||l_trackable||' l_charges_interface_status:'||l_charges_interface_status||'l_charge_upload_status:'||l_charge_upload_status);
1147 if nvl(l_ib_update_status,' ') <> 'SUCCEEDED'
1148 and l_trackable ='Y'
1149 and (l_charges_interface_status = 'SUCCEEDED'
1150 or l_charge_upload_status ='SUCCEEDED' )
1151 Then
1152 ---------------------------------------------------------------------------
1153 -- UPDATE INSTALL BASE --
1154 ---------------------------------------------------------------------------
1155 l_instance_status_id :=to_number(l_instance_status); --added for bug 3192060
1156 log('main','l_instance_status_id:'||l_instance_status_id);
1157 log('main','calling csf_ib.update_install_base');
1158 csf_ib.update_install_base(
1159 p_api_version => 1.0,
1160 p_init_msg_list => null,
1161 p_commit => null,
1162 p_validation_level => null,
1163 x_return_status => l_return_status,
1164 x_msg_count => l_msg_count,
1165 x_msg_data => l_msg_data,
1166 x_new_instance_id => l_new_instance_id, --
1167 p_in_out_flag => l_in_out_flag, --
1168 p_transaction_type_id => l_transaction_type_id_csi,
1169 p_txn_sub_type_id => l_txn_sub_type_id,
1170 p_instance_id => l_instance_id,
1171 p_inventory_item_id => l_inventory_item_id,
1172 p_inv_organization_id => l_organization_id,
1173 p_inv_subinventory_name => l_subinventory_code,
1174 p_inv_locator_id => l_locator_id,
1175 p_quantity => l_quantity,
1176 p_inv_master_organization_id => l_inv_master_organization_id,
1177 p_mfg_serial_number_flag => 'N',
1178 p_serial_number => l_item_serial_number,
1179 p_lot_number => l_item_lotnumber,
1180 p_revision => l_revision,
1181 p_unit_of_measure => l_uom_code,
1182 p_party_id => l_party_id,
1183 p_party_account_id => l_customer_account_id,
1184 p_party_site_id => l_party_site_id,
1185 p_parent_instance_id => l_parent_product_id,
1186 p_instance_status_id => l_instance_status_id, --added for bug 3192060
1187 p_item_operational_status_code => l_item_operational_status_code,
1188 p_system_id => l_system_id, --added for bug 11936065
1189 p_recovered_instance_id => l_recovered_instance_id); --added for bug 12640969;
1190
1191
1192 log('main','l_return_status:'||l_return_status);
1193 if l_RETURN_STATUS = 'S' then -- success --3
1194 l_msg_data := null; -- added for bug 3863950
1195 l_ib_interface_status := 'SUCCEEDED';
1196 elsif l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then
1197 retcode := 1;
1198 l_processed_flag := 'COMPLETED W/ERRORS';
1199 if l_msg_count > 0 then --1
1200 FOR counter IN REVERSE 1..l_msg_count
1201 LOOP
1202 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
1203 fnd_file.put_line(fnd_file.log,l_msg_data);
1204 log('main','error1:'||l_msg_data);
1205 end loop;
1206 end if; --1
1207 l_ib_interface_status := 'FAILED';
1208 end if; --3
1209
1210
1211
1212 end if; --4
1213 log('main','l_ib_interface_status:'||l_ib_interface_status||' l_charges_interface_status:'||l_charges_interface_status);
1214 if l_ib_interface_status = 'FAILED'
1215 or l_charges_interface_status = 'FAILED' then
1216 rollback to before_fetch;
1217
1218 if l_charges_interface_status <> 'FAILED' then
1219 l_charges_interface_status := fnd_api.g_miss_char;
1220 end if;
1221 if l_ib_interface_status <> 'FAILED' then
1222 l_ib_interface_status := fnd_api.g_miss_char;
1223 end if;
1224 log('main','update_row a');
1225 csf_debrief_lines_pkg.update_row(
1226 p_debrief_line_id => l_debrief_line_id,
1227 p_error_text => substr(l_msg_data,1,2000),
1228 p_charge_upload_status => l_charges_interface_status,
1229 p_ib_update_status => l_ib_interface_status,
1230 p_last_updated_by => fnd_global.user_id,
1231 p_last_update_date => sysdate,
1232 p_last_update_login => fnd_global.login_id);
1233 commit; -- for bug 13979512
1234 savepoint before_fetch;
1235 else
1236 if nvl(l_ib_update_status,' ') <> 'SUCCEEDED'
1237 and l_billing_type='M' and l_trackable='Y'
1238 and (l_charges_interface_status= 'SUCCEEDED'
1239 or l_charge_upload_status='SUCCEEDED')
1240 Then
1241
1242 if l_line_category_code <> 'RETURN' then
1243 l_instance_id := l_new_instance_id;
1244 end if;
1245 log('main','update_row b');
1246 csf_debrief_lines_pkg.update_row(
1247 p_debrief_line_id => l_debrief_line_id,
1248 p_error_text => null,
1249 p_instance_id => l_instance_id,
1250 p_ib_update_status => l_ib_interface_status,
1251 p_last_updated_by => fnd_global.user_id,
1252 p_last_update_date => sysdate,
1253 p_last_update_login => fnd_global.login_id);
1254 end if;
1255 end if;
1256
1257 ------------------------------------------------------------------------
1258 -- UPDATE INVENTORY --
1259 -------------------------------------------------------------------------
1260 log('main','l_spare_update_status:'||l_spare_update_status||' l_charges_interface_status:'||l_charges_interface_status||' l_charge_upload_status:'||l_charge_upload_status);
1261 log('main','l_ib_interface_status:'||l_ib_interface_status||' l_ib_update_status:'||l_ib_update_status||' l_trackable:'||l_trackable);
1262 if nvl(l_spare_update_status,' ') <> 'SUCCEEDED'
1263 and (l_charges_interface_status = 'SUCCEEDED'
1264 or l_charge_upload_status ='SUCCEEDED' )
1265 and ( (l_ib_interface_status = 'SUCCEEDED' and l_trackable='Y')
1266 or (l_ib_update_status = 'SUCCEEDED' and l_trackable ='Y')
1267 or l_trackable ='N' or l_trackable is null ) Then
1268
1269 l_return_status := fnd_api.g_ret_sts_success;
1270 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_UPDATE_PKG',
1271 'TRANSACT_MATERIAL','B','C') THEN
1272 csf_debrief_update_pkg.g_debrief_line_id := l_debrief_line_id;
1273 csf_debrief_update_pkg.g_account_id := null;
1274 csf_debrief_pub.call_internal_hook('CSF_DEBRIEF_UPDATE_PKG',
1275 'TRANSACT_MATERIAL','B',
1276 l_return_status);
1277 end if;
1278 log('main','after hook l_return_status:'||l_return_status);
1279 if l_return_status = fnd_api.g_ret_sts_success then
1280 if l_date_type = 'Y' then
1281 l_service_date := sysdate;
1282 end if;
1283
1284 -- Bug 14511964. All reservations will be relieved at line level only
1285 --if p_debrief_line_id is not null then
1286 log('main','relieve_reservations');
1287 relieve_reservations ( l_task_assignment_id ,
1288 l_inventory_item_id,
1289 l_quantity,
1290 l_uom_code,
1291 l_return_status,
1292 l_msg_data ,
1293 l_msg_count );
1294 log('main','l_return_status:'||l_return_status);
1295 --end if;
1296 l_inv_transaction_id := null;
1297 l_inv_transaction_header_id := null;
1298 log('main','calling transact_material');
1299 CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL(
1300 p_api_version => l_api_version,
1301 x_return_status => l_RETURN_STATUS,
1302 x_msg_count => l_MSG_COUNT,
1303 x_msg_data => l_MSG_DATA,
1304 p_init_msg_list => FND_API.G_TRUE,
1305 p_commit => FND_API.G_FALSE,
1306 p_inventory_item_id => l_inventory_item_id,
1307 p_organization_id => l_organization_id,
1308 p_subinventory_code => l_subinventory_code,
1309 p_locator_id => l_locator_id,
1310 p_serial_number => l_item_serial_number,
1311 p_quantity => l_quantity,
1312 p_uom => l_uom_code,
1313 p_revision => l_revision,
1314 p_lot_number => l_item_lotnumber,
1315 p_transfer_to_subinventory => null,
1316 p_transfer_to_locator => null,
1317 p_transfer_to_organization => null,
1318 p_source_id => null,
1319 p_source_line_id => null,
1320 p_transaction_type_id => l_inv_transaction_type_id,
1321 p_account_id => csf_debrief_update_pkg.g_account_id,
1322 px_transaction_header_id => l_inv_transaction_header_id,
1323 px_transaction_id => l_inv_transaction_id,
1324 p_transaction_source_id => l_debrief_header_id,
1325 p_trx_source_line_id => l_debrief_line_id,
1326 p_transaction_source_name => l_debrief_number,
1327 p_transaction_date => l_service_date );
1328
1329 update csf_debrief_lines
1330 set material_transaction_id = l_inv_transaction_id
1331 where debrief_line_id = l_debrief_line_id;
1332
1333 log('main','l_return_status:'||l_return_status);
1334 end if;
1335 if l_RETURN_STATUS = 'S' then -- success
1336 l_inv_interface_status := 'SUCCEEDED';
1337 l_msg_data := null;
1338 log('main','calling update_row c');
1339 csf_debrief_lines_pkg.update_row(
1340 p_debrief_line_id => l_debrief_line_id,
1341 p_error_text => null,
1342 p_spare_update_status => l_inv_interface_status,
1343 p_last_updated_by => fnd_global.user_id,
1344 p_last_update_date => sysdate,
1345 p_last_update_login => fnd_global.login_id);
1346
1347 elsif l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then
1348 retcode := 1;
1349 l_processed_flag := 'COMPLETED W/ERRORS';
1350 if l_msg_count > 0 then
1351 FOR counter IN REVERSE 1..l_msg_count
1352 LOOP
1353 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
1354 l_position := instr(l_msg_data, 'ERROR_EXPLANATION');
1355 l_msg_data := substr(l_msg_data, l_position);
1356 fnd_file.put_line(fnd_file.log,l_msg_data);
1357 log('main','error'||l_msg_data);
1358 end loop;
1359 end if;
1360 l_inv_interface_status := 'FAILED';
1361 end if;
1362 log('main','l_inv_transaction_type_id:'||l_inv_transaction_type_id||' l_return_organization_id:'||l_return_organization_id);
1363 -- Handle return of recovered part from debrief
1364 if l_inv_transaction_type_id = 94
1365 and l_return_organization_id is not null then
1366 l_trans_items(1).inventory_item_id := l_inventory_item_id;
1367 l_trans_items(1).revision := l_revision;
1368 l_trans_items(1).quantity := l_quantity;
1369 l_trans_items(1).uom_code := l_uom_code;
1370 l_trans_items(1).lot_number := l_item_lotnumber;
1371 l_trans_items(1).serial_number := l_item_serial_number;
1372 l_trans_items(1).frm_organization_id := l_organization_id;
1373 l_trans_items(1).frm_subinventory_code := l_subinventory_code;
1374 l_trans_items(1).frm_locator_id :=
1375 nvl(l_issuing_locator_id,l_receiving_locator_id);
1376 l_trans_items(1).to_organization_id := l_return_organization_id;
1377 l_trans_items(1).to_subinventory_code := l_return_subinventory_name;
1378 l_trans_items(1).to_locator_id := null;
1379 l_trans_items(1).to_serial_number := l_item_serial_number;
1380 if l_organization_id <> l_return_organization_id then
1381 log('main','l_organization_id <> l_return_organization_id');
1382 open c_intransit(l_organization_id, l_return_organization_id);
1383 fetch c_intransit into l_intransit;
1384 close c_intransit;
1385 log('main','l_intransit:'||l_intransit);
1386 if l_intransit = 1 then
1387 l_is_intransit := FALSE;
1388 elsif l_intransit = 2 then
1389 l_is_intransit := TRUE;
1390 end if;
1391 l_trans_items(1).waybill_airbill := l_waybill;
1392 l_trans_items(1).freight_code := l_shipping_method;
1393 l_trans_items(1).shipment_number := l_shipping_number;
1394 log('main','calling transact_intorg_transfer');
1395 csp_transactions_pub.transact_intorg_transfer(
1396 1.0,
1397 fnd_api.g_false,
1398 fnd_api.g_false,
1399 l_trans_items,
1400 l_is_intransit,
1401 l_return_status,
1402 l_msg_count,
1403 l_msg_data);
1404 log('main','l_return_status:'||l_return_status);
1405 if l_return_status = 'S' then
1406 l_spare_update_status := 'SUCCEEDED';
1407 else
1408 l_spare_update_status := 'FAILED';
1409 end if;
1410 elsif l_organization_id = l_return_organization_id then
1411 log('main','calling transact_subinv_transfer');
1412 csp_transactions_pub.transact_subinv_transfer(
1413 1.0,
1414 fnd_api.g_false,
1415 fnd_api.g_false,
1416 l_trans_items,
1417 l_return_status,
1418 l_msg_count,
1419 l_msg_data);
1420 log('main','l_return_status:'||l_return_status);
1421 if l_return_status = 'S' then
1422 l_spare_update_status := 'SUCCEEDED';
1423 else
1424 l_spare_update_status := 'FAILED';
1425 end if;
1426 end if;
1427 end if;
1428 end if; --spares update status
1429 end if; --billing_type='M'
1430
1431 -- COSTING
1432 if (l_inventory_item_id is null and l_create_cost_flag = 'Y'
1433 and l_create_charge_flag = 'N'
1434 and nvl(l_charge_upload_status,' ') <> 'SUCCEEDED') or
1435 (l_inventory_item_id is not null and l_create_cost_flag = 'Y'
1436 and nvl(l_charge_upload_status,' ') <> 'SUCCEEDED') Then
1437 l_estimate_detail_id := null;
1438
1439 if l_create_charge_flag = 'Y' then
1440 open c_charge_id(l_debrief_line_id);
1441 fetch c_charge_id into l_estimate_detail_id;
1442 close c_charge_id;
1443 end if;
1444
1445 l_cost_rec.incident_id := l_incident_id;
1446 l_cost_rec.transaction_type_id := l_transaction_type_id;
1447 l_cost_rec.inventory_item_id := l_inventory_item_id;
1448 l_cost_rec.quantity := l_quantity;
1449 l_cost_rec.unit_of_measure_code := l_uom_code;
1450 l_cost_rec.currency_code := l_currency_code;
1451 l_cost_rec.source_id := l_debrief_line_id;
1452 l_cost_rec.source_code := 'SD';
1453 l_cost_rec.estimate_detail_id := l_estimate_detail_id;
1454 --l_cost_rec.org_id := 204;
1455 l_cost_rec.inventory_org_id := l_organization_id;
1456 l_cost_rec.transaction_date := sysdate;
1457 l_cost_rec.extended_cost := l_expense_amount;
1458 log('main','calling create_cost_details');
1459 cs_cost_details_pub.create_cost_details(
1460 p_api_version => 1.0,
1461 x_return_status => l_return_status,
1462 x_msg_count => l_msg_count,
1463 x_object_version_number => l_object_version,
1464 x_msg_data => l_msg_data,
1465 x_cost_id => l_cost_id,
1466 p_cost_rec => l_cost_rec);
1467 log('main','l_return_status:'||l_return_status);
1468 if l_RETURN_STATUS = 'S' then -- success
1469 l_charges_interface_status := 'SUCCEEDED';
1470 l_msg_data := null;
1471 log('main','calling update_row d');
1472 csf_debrief_lines_pkg.update_row(
1473 p_debrief_line_id => l_debrief_line_id,
1474 p_error_text => substr(l_msg_data,1,2000),
1475 p_charge_upload_status => l_charges_interface_status,
1476 p_last_updated_by => fnd_global.user_id,
1477 p_last_update_date => sysdate,
1478 p_last_update_login => fnd_global.login_id);
1479 elsif l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then
1480 retcode := 1;
1481 l_processed_flag := 'COMPLETED W/ERRORS';
1482 if l_msg_count > 0 then
1483 FOR counter IN REVERSE 1..l_msg_count LOOP
1484 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
1485 l_position := instr(l_msg_data, 'ERROR_EXPLANATION');
1486 l_msg_data := substr(l_msg_data, l_position);
1487 fnd_file.put_line(fnd_file.log,l_msg_data);
1488 log('main','l_msg_data:'||l_msg_data);
1489 end loop;
1490 end if;
1491 l_inv_interface_status := 'FAILED';
1492 end if;
1493 end if;
1494
1495 -------- Added for bug 3608969
1496 log('main','l_ib_interface_status:'||l_ib_interface_status||'l_charges_interface_status:'||l_charges_interface_status||' l_inv_interface_status:'||l_inv_interface_status);
1497 if l_ib_interface_status = 'FAILED'
1498 or l_charges_interface_status = 'FAILED'
1499 or l_inv_interface_status = 'FAILED' then
1500 rollback;
1501 if l_charges_interface_status <> 'FAILED' then
1502 l_charges_interface_status := fnd_api.g_miss_char;
1503 end if;
1504 if l_ib_interface_status <> 'FAILED' then
1505 l_ib_interface_status := fnd_api.g_miss_char;
1506 end if;
1507 if l_inv_interface_status <> 'FAILED' then
1508 l_inv_interface_status := fnd_api.g_miss_char;
1509 end if;
1510 log('main','calling update_row e');
1511 csf_debrief_lines_pkg.update_row(
1512 p_debrief_line_id => l_debrief_line_id,
1513 p_error_text => substr(l_msg_data,1,2000),
1514 p_charge_upload_status => l_charges_interface_status,
1515 p_ib_update_status => l_ib_interface_status,
1516 p_spare_update_status => l_inv_interface_status,
1517 p_last_updated_by => fnd_global.user_id,
1518 p_last_update_date => sysdate,
1519 p_last_update_login => fnd_global.login_id);
1520 commit; -- for bug 13979512
1521 savepoint before_fetch;
1522
1523 else
1524 if nvl(l_spare_update_status,' ') <> 'SUCCEEDED' and l_billing_type='M'
1525 and (l_charges_interface_status= 'SUCCEEDED'
1526 or l_charge_upload_status='SUCCEEDED')
1527 and ( (l_ib_interface_status = 'SUCCEEDED' and l_trackable='Y')
1528 or (l_ib_update_status = 'SUCCEEDED' and l_trackable ='Y')
1529 or l_trackable ='N' or l_trackable is null ) then
1530 --we tried to update inventory
1531 log('main','calling update_row f l_inv_interface_status:'||l_inv_interface_status);
1532 csf_debrief_lines_pkg.update_row(
1533 p_debrief_line_id => l_debrief_line_id,
1534 p_error_text => null,
1535 p_spare_update_status => l_inv_interface_status,
1536 p_last_updated_by => fnd_global.user_id,
1537 p_last_update_date => sysdate,
1538 p_last_update_login => fnd_global.login_id);
1539
1540 end if;
1541 end if;
1542
1543 else --- assignment status is not completed => we generate in progress charges
1544
1545 l_processed_flag := 'UNPROCESSED';
1546
1547 ------------------------------------------------------------------------
1548 --GENERATING IN PROGRESS CHARGE LINES ----
1549 -------------------------------------------------------------------------
1550 log('main','l_charge_upload_status:'||l_charge_upload_status||' l_create_charge_flag:'||l_create_charge_flag);
1551 if nvl(l_charge_upload_status, ' ') <> 'SUCCEEDED'
1552 and l_create_charge_flag = 'Y' then -- added for bug 3538214
1553 log('main','calling create_charges for in progress');
1554 csf_debrief_charges.create_charges(
1555 p_original_source_id => l_original_source_id ,
1556 p_original_source_code => l_original_source_code ,
1557 p_incident_id => l_incident_id ,
1558 p_business_process_id => l_business_process_id ,
1559 p_line_category_code => l_line_category_code ,
1560 p_source_code => 'SD' ,
1561 p_source_id => l_debrief_line_id ,
1562 p_inventory_item_id => l_inventory_item_id ,
1563 p_item_revision => l_revision ,
1564 p_unit_of_measure_code => l_uom_code ,
1565 p_quantity => l_quantity ,
1566 p_customer_product_id => l_charges_instance_id ,
1567 p_installed_cp_return_by_date => l_installed_cp_return_by_date,
1568 p_after_warranty_cost => l_after_warranty_cost ,
1569 p_currency_code => l_currency_code ,
1570 p_return_reason_code => l_return_reason_code ,
1571 p_inventory_org_id => l_organization_id ,
1572 p_serial_number => l_item_serial_number ,
1573 p_final_charge_flag => 'N' ,
1574 p_labor_start_date => l_labor_start_date ,
1575 p_labor_end_date => l_labor_end_date ,
1576 p_transaction_type_id => l_transaction_type_id,
1577 p_expenditure_org_id => l_expenditure_org_id ,
1578 p_project_id => l_project_id ,
1579 p_project_task_id => l_project_task_id ,
1580 x_return_status => l_return_status ,
1581 x_msg_count => l_msg_count ,
1582 x_msg_data => l_msg_data );
1583
1584
1585 if l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then
1586 retcode := 1;
1587
1588 if l_msg_count > 0 then
1589 FOR counter IN REVERSE 1..l_msg_count LOOP
1590 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
1591 fnd_file.put_line(fnd_file.log,l_msg_data);
1592 log('main','error2:'||l_msg_data);
1593 end loop;
1594 end if;
1595 else
1596 l_msg_data := null;
1597 end if;
1598
1599 csf_debrief_lines_pkg.update_row(
1600 p_debrief_line_id => l_debrief_line_id,
1601 p_error_text => substr(l_msg_data,1,2000));
1602
1603 end if; -- end of charges status check
1604 end if; -- end of DOA and Unused parts check
1605 end if;--end of deciding if we have to generate in progress or final charges
1606 commit; -- for bug 13979512
1607 savepoint before_fetch;
1608 end loop;
1609 close c_lines;
1610
1611 -- Bug 14511964.
1612 if p_debrief_line_id is null then
1613 log('main', 'Now relieving extra and partial reservations. Reservations will still be kept for error items.');
1614 log('main','p_debrief_header_id:'||p_debrief_header_id);
1615 log('main','l_task_assignment_id:'||l_task_assignment_id);
1616
1617 open c_item_reservations(l_task_assignment_id, null);
1618 loop
1619 -- loop through each reservation
1620 fetch c_item_reservations into l_res_item_id, l_res_item_uom, l_res_item_qty;
1621 exit when c_item_reservations % notfound;
1622 log('main', 'l_res_item_id: '||l_res_item_id);
1623 log('main', 'l_res_item_uom: '||l_res_item_uom);
1624 log('main', 'l_res_item_qty: '||l_res_item_qty);
1625
1626 -- fetch error debrief lines for the item
1627 open c_error_dbf_items(p_debrief_header_id, l_res_item_id);
1628 fetch c_error_dbf_items into l_err_item_id, l_err_item_uom, l_err_item_qty;
1629 if c_error_dbf_items % notfound then
1630 log('main', 'No error lines found, relieve all quantities.');
1631 l_qty_to_relieve := l_res_item_qty;
1632 else
1633 log('main', 'Error lines found, relieve non-error quantities.');
1634 log('main', 'l_err_item_id: '||l_err_item_id);
1635 log('main', 'l_err_item_uom: '||l_err_item_uom);
1636 log('main', 'l_err_item_qty: '||l_err_item_qty);
1637 l_qty_to_relieve := l_res_item_qty - l_err_item_qty;
1638 end if;
1639
1640 -- relieve the reservations
1641 if l_qty_to_relieve > 0 then
1642 log('main', 'relieve_reservations for item: '||l_res_item_id);
1643 csf_debrief_update_pkg.relieve_reservations ( l_task_assignment_id ,
1644 l_res_item_id,
1645 l_qty_to_relieve,
1646 l_res_item_uom,
1647 l_return_status,
1648 l_msg_data ,
1649 l_msg_count );
1650 log('main', 'l_return_status:'||l_return_status);
1651 log('main', 'l_msg_data:'||l_msg_data);
1652 end if;
1653 close c_error_dbf_items;
1654 end loop;
1655 close c_item_reservations;
1656 log('main', 'End of relieving extra and partial reservations.');
1657 end if;
1658
1659 FND_FILE.put_line(FND_FILE.log,'l_processed_flag: '||l_processed_flag);
1660 fnd_file.put_line(fnd_file.log,'l_debrief_error_status: '||l_debrief_error_status);
1661 log('main','l_processed_flag:'||l_processed_flag||' l_debrief_error_status:'||l_debrief_error_status);
1662 if nvl(l_processed_flag,'UNPROCESSED') <> 'UNPROCESSED' then
1663 if l_processed_flag = 'COMPLETED W/ERRORS' then
1664 open c_debrief_error;
1665 fetch c_debrief_error into l_debrief_error_status,
1666 l_assignment_status_id,
1667 l_object_version_number;
1668 close c_debrief_error;
1669 FND_FILE.put_line(FND_FILE.log,'l_debrief_error_status: '||l_debrief_error_status);
1670 FND_FILE.put_line(FND_FILE.log,'l_assignment_status_id: '||l_assignment_status_id);
1671 fnd_file.put_line(fnd_file.log,'l_object_version_number: '||l_object_version_number);
1672 log('main','l_debrief_error_status:'||l_debrief_error_status||' l_assignment_status_id:'||l_assignment_status_id||'l_object_version_number:'||l_object_version_number);
1673 end if;
1674
1675 if nvl(l_debrief_error_status,-1) <> -1 then
1676 fnd_file.put_line(fnd_file.log,'Calling update_assignment_status: ');
1677 log('main','calling update_assignment_status');
1678 csf_task_assignments_pub.update_assignment_status
1679 ( p_api_version => 1.0
1680 , p_init_msg_list => null
1681 , p_commit => null
1682 , p_validation_level => fnd_api.g_valid_level_none
1683 , p_task_assignment_id => l_task_assignment_id
1684 , p_object_version_number => l_object_version_number
1685 , p_assignment_status_id => l_debrief_error_status
1686 , p_update_task => 'T'
1687 , x_return_status => l_return_status
1688 , x_msg_count => l_msg_count
1689 , x_msg_data => l_msg_data
1690 , x_task_object_version_number => l_task_object_version_number
1691 , x_task_status_id => l_task_status_id
1692 ) ;
1693 fnd_file.put_line(fnd_file.log,'l_return_status: '||l_return_status);
1694 log('main','l_return_status:'||l_return_status);
1695 if l_RETURN_STATUS ='E' or l_RETURN_STATUS = 'U' then
1696 retcode := 1;
1697
1698 if l_msg_count > 0 then
1699 FOR counter IN REVERSE 1..l_msg_count LOOP
1700 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
1701 fnd_file.put_line(fnd_file.log,l_msg_data);
1702 log('main','error3:'||l_msg_data);
1703 end loop;
1704 end if;
1705 else
1706 l_msg_data := null;
1707 end if;
1708 end if;
1709
1710 end if;
1711 log('main','l_processed_flag:'||l_processed_flag);
1712 update csf_debrief_headers
1713 set processed_flag = nvl(l_processed_flag,'UNPROCESSED')
1714 where debrief_header_id = l_debrief_header_id;
1715 commit;
1716
1717 log('main','l_return_status:'||l_return_status||' l_record_lock:'||l_record_lock);
1718 If l_return_status = FND_API.G_RET_STS_ERROR and l_record_lock = 'Y' THEN
1719 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_CONC_PROG_RECLOCK');
1720 FND_MESSAGE.Set_Token('DEBRIEF_NUMBER',l_debrief_number);
1721 FND_MSG_PUB.ADD;
1722 l_msg_data := FND_MSG_PUB.Get(p_msg_index => fnd_msg_pub.G_LAST,
1723 p_encoded => FND_API.G_FALSE);
1724 fnd_file.put_line(fnd_file.log, l_msg_data);
1725 l_conc_result := fnd_concurrent.set_completion_status('WARNING','Warning');
1726 exit;
1727 end if;
1728 end loop;
1729
1730 if p_debrief_header_id is not null and p_incident_id is null then
1731 close c_header;
1732 elsif p_incident_id is not null then
1733 close c_header_inc;
1734 else
1735 close c_headers;
1736 end if;
1737
1738 exception
1739 when e_no_header_id then
1740 fnd_message.set_name('CSF', 'CSF_DEBRIEF_MISSING_HEADER_ID');
1741 fnd_msg_pub.add;
1742 fnd_msg_pub.get(1,FND_API.G_FALSE,l_msg_data,l1);
1743 fnd_file.put_line(fnd_file.log,l_msg_data);
1744 log('main','error4:'||l_msg_data);
1745 retcode := 2;
1746 errbuf := l_msg_data;
1747
1748 when others then
1749 retcode := 2;
1750 errbuf := sqlerrm;
1751 l_message := errbuf;
1752 fnd_file.put_line(fnd_file.log,l_message);
1753 log('main','error5:'||l_msg_data);
1754 end;
1755
1756 ---------------------------------------------------------------------------
1757 PROCEDURE Form_Call
1758 ---------------------------------------------------------------------------
1759 (
1760 p_api_version IN NUMBER,
1761 p_debrief_header_id IN NUMBER
1762 ) is
1763 l_request_id Number ;
1764 Begin
1765
1766 l_request_id := fnd_request.submit_request('CSF',
1767 'CSFUPDATE',
1768 'CSF:Update Debrief Lines',
1769 null,
1770 FALSE,
1771 2.0,
1772 p_debrief_header_id);
1773
1774 End;
1775
1776 ---------------------------------------------------------------------------
1777 procedure relieve_reservations(p_task_assignment_id IN NUMBER,
1778 p_inventory_item_id IN NUMBER,
1779 p_qty IN NUMBER,
1780 p_uom IN varchar2,
1781 ---------------------------------------------------------------------------
1782 x_return_status OUT NOCOPY varchar2,
1783 x_msg_data OUT NOCOPY varchar2,
1784 x_msg_count OUT NOCOPY varchar2) IS
1785 cursor reservations
1786 is
1787 Select crld.source_id,crld.req_line_detail_id,crh.requirement_header_id,mr.reservation_quantity
1788 from csp_requirement_headers crh,
1789 csp_requirement_lines crl,
1790 csp_req_line_details crld,
1791 mtl_reservations mr
1792 where crh.task_assignment_id = p_task_assignment_id
1793 and crl.requirement_header_id = crh.requirement_header_id
1794 and crld.requirement_line_id = crl.requirement_line_id
1795 and crld.source_type = 'RES'
1796 and mr.reservation_id = crld.source_id
1797 and mr.inventory_item_id = nvl(p_inventory_item_id,mr.inventory_item_id);
1798
1799 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
1800 l_serial_number inv_reservation_global.serial_number_tbl_type;
1801 l_relieved_quantity NUMBER;
1802 l_relieved_quantity1 NUMBER;
1803 l_relieve_all varchar2(1) := fnd_api.g_false;
1804 l_remaining_quantity NUMBER;
1805 l_req_line_detail_id NUMBER;
1806 l_requirement_header_id number := null;
1807 l_req_header_id number := null;
1808 l_max_reservation_qty number := null;
1809 l_qty_to_relieve number := null;
1810 BEGIN
1811
1812
1813 log('relieve_reservations', 'p_inventory_item_id=' || p_inventory_item_id);
1814 log('relieve_reservations', 'p_qty=' || p_qty);
1815 log('relieve_reservations', 'p_uom=' || p_uom);
1816
1817 l_relieved_quantity := p_qty;
1818 OPEN reservations;
1819 LOOP
1820 FETCH reservations INTO l_reservation_rec.reservation_id,
1821 l_req_line_detail_id,
1822 l_req_header_id,
1823 l_max_reservation_qty;
1824 exit when reservations % notfound;
1825 log('relieve_reservations', 'reservation_id=' || l_reservation_rec.reservation_id);
1826 log('relieve_reservations', 'l_req_line_detail_id=' || l_req_line_detail_id);
1827 log('relieve_reservations', 'l_req_header_id=' || l_req_header_id);
1828 log('relieve_reservations', 'l_relieved_quantity=' || l_relieved_quantity);
1829 log('relieve_reservations', 'l_max_reservation_qty=' || l_max_reservation_qty);
1830
1831 l_requirement_header_id := l_req_header_id;
1832
1833 /*
1834 Bug 14511964. Modified to handle condition when multiple reservations are
1835 there for a single item.
1836 Example:
1837 2 reservations exist such as: Res1: Item1 - 2qty & Res2: Item1 - 3qty
1838 1 debrief line: Item1 - 4qty
1839 In this scenario we should be able to relieve 4 qty. 2 from Res1 and 2 from Res2
1840 */
1841 if l_relieved_quantity is null or l_relieved_quantity > l_max_reservation_qty then
1842 l_relieve_all := fnd_api.g_true;
1843 l_qty_to_relieve := l_max_reservation_qty;
1844 else
1845 l_relieve_all := fnd_api.g_false;
1846 l_qty_to_relieve := l_relieved_quantity;
1847 end if;
1848 log('relieve_reservations', 'l_qty_to_relieve=' || l_qty_to_relieve);
1849
1850 inv_reservation_pub.relieve_reservation
1851 (
1852 p_api_version_number => 1.0
1853 ,p_init_msg_lst =>
1854 fnd_api.g_false
1855 ,x_return_status =>
1856 x_return_status
1857 ,x_msg_count =>
1858 x_msg_count
1859 ,x_msg_data =>
1860 x_msg_data
1861 ,p_rsv_rec =>
1862 l_reservation_rec
1863 ,p_primary_relieved_quantity =>
1864 l_qty_to_relieve
1865 ,p_relieve_all =>
1866 l_relieve_all
1867 ,p_original_serial_number =>
1868 l_serial_number
1869 ,p_validation_flag =>
1870 fnd_api.g_true
1871 ,x_primary_relieved_quantity =>
1872 l_relieved_quantity1
1873 ,x_primary_remain_quantity =>
1874 l_remaining_quantity
1875 );
1876
1877 log('relieve_reservations', 'l_relieved_quantity1=' || l_relieved_quantity1);
1878 log('relieve_reservations', 'l_remaining_quantity=' || l_remaining_quantity);
1879
1880 If x_return_status <> FND_API.G_RET_STS_SUCCESS then
1881 exit;
1882 end if;
1883 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1884 if nvl(l_remaining_quantity, 0) <= 0 then
1885 CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_line_detail_id);
1886 end if;
1887
1888 log('relieve_reservations', 'reducing relieved quantity now');
1889 log('relieve_reservations', 'current l_relieved_quantity=' || l_relieved_quantity);
1890 if l_relieved_quantity > l_max_reservation_qty then
1891 l_relieved_quantity := l_relieved_quantity - l_max_reservation_qty;
1892 end if;
1893 log('relieve_reservations', 'new l_relieved_quantity=' || l_relieved_quantity);
1894
1895 END IF;
1896 END LOOP;
1897 CLOSE reservations;
1898 if l_requirement_header_id is not null then
1899 csp_requirement_headers_pkg.update_row(
1900 p_requirement_header_id => l_requirement_header_id,
1901 p_open_requirement => 'N');
1902 end if;
1903 END relieve_reservations;
1904
1905 ---------------------------------------------------------------------------
1906 procedure relieve_reservations(p_task_assignment_id IN NUMBER,
1907 ---------------------------------------------------------------------------
1908 x_return_status OUT NOCOPY varchar2,
1909 x_msg_data OUT NOCOPY varchar2,
1910 x_msg_count OUT NOCOPY varchar2) IS
1911 begin
1912 relieve_reservations(p_task_assignment_id,
1913 null,
1914 null,
1915 null,
1916 x_return_status,
1917 x_msg_data,
1918 x_msg_count);
1919 end;
1920
1921 PROCEDURE web_Call
1922 (
1923 p_api_version IN NUMBER,
1924 p_task_assignment_id IN NUMBER
1925 ) is
1926 cursor get_debrief_header is
1927 select debrief_header_id
1928 from csf_debrief_headers
1929 where task_assignment_id = p_task_assignment_id;
1930
1931 l_debrief_header_id NUMBER;
1932 l_request_id NUMBER;
1933
1934 begin
1935 for gdh In get_debrief_header LOOP
1936 l_debrief_header_id := gdh.debrief_header_id;
1937 --
1938 update csf_debrief_headers
1939 set processed_flag = 'PENDING'
1940 where debrief_header_id = l_debrief_header_id;
1941 --
1942 END LOOP;
1943 IF l_debrief_header_id IS NOT NULL THEN
1944 l_request_id := fnd_request.submit_request('CSF',
1945 'CSFUPDATE',
1946 'CSF:Update Debrief Lines',
1947 null,
1948 FALSE,
1949 1.0,
1950 l_debrief_header_id);
1951
1952 END IF;
1953 end web_Call;
1954
1955 PROCEDURE DEBRIEF_STATUS_CHECK (
1956 p_incident_id in number,
1957 p_api_version in number,
1958 p_validation_level in number,
1959 x_debrief_status out nocopy debrief_status_tbl_type,
1960 x_return_status out nocopy varchar2,
1961 x_msg_count out nocopy number,
1962 x_msg_data out nocopy varchar2) IS
1963
1964 l_processed_flag csf_debrief_headers.processed_flag%type;
1965 l_task_number jtf_tasks_b.task_number%type;
1966 l_debrief_status_rec debrief_status_rec_type;
1967 l_debrief_status_tbl debrief_status_tbl_type;
1968
1969 cursor get_debrief_status is
1970 select jtb.task_id,
1971 jtb.task_number,
1972 cdh.debrief_header_id,
1973 cdh.debrief_number,
1974 decode(processed_flag,'PENDING','P','E') debrief_status
1975 from csf_debrief_headers cdh,
1976 jtf_task_assignments jta,
1977 jtf_tasks_b jtb
1978 where processed_flag in ('PENDING','COMPLETED W/ERRORS')
1979 and jta.task_assignment_id = cdh.task_assignment_id
1980 and jtb.task_id = jta.task_id
1981 and jtb.source_object_type_code = 'SR'
1982 and jtb.source_object_id = p_incident_id
1983 union all
1984 select jtb.task_id,
1985 jtb.task_number,
1986 cdh.debrief_header_id,
1987 cdh.debrief_number,
1988 decode(processed_flag,'PENDING','P','E') debrief_status
1989 from csd_repairs cr,
1990 jtf_tasks_b jtb,
1991 jtf_task_assignments jta,
1992 csf_debrief_headers cdh
1993 where jtb.source_object_id = cr.repair_line_id
1994 and jtb.source_object_type_code = 'DR'
1995 and jta.task_id = jtb.task_id
1996 and cdh.task_assignment_id = jta.task_assignment_id
1997 and cdh.processed_flag in ('PENDING','COMPLETED W/ERRORS')
1998 and cr.incident_id = p_incident_id;
1999
2000 BEGIN
2001 x_return_status := FND_API.G_RET_STS_SUCCESS;
2002 for cr in get_debrief_status loop
2003 l_debrief_status_tbl(get_debrief_status%rowcount) := cr;
2004 if p_validation_level = 0 and get_debrief_status%rowcount = 1 then
2005 exit;
2006 end if;
2007 end loop;
2008 if l_debrief_status_tbl.count > 0 then
2009 x_debrief_status := l_debrief_status_tbl;
2010 x_return_status := fnd_api.g_ret_sts_error;
2011 end if;
2012 exception when others then
2013 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
2014 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
2015 fnd_msg_pub.add;
2016 x_return_status := fnd_api.g_ret_sts_unexp_error;
2017 END DEBRIEF_STATUS_CHECK ;
2018
2019 End Csf_Debrief_Update_pkg;
2020