[Home] [Help]
PACKAGE BODY: APPS.CSE_FAC_INSERVICE_PKG
Source
1 PACKAGE BODY cse_fac_inservice_pkg AS
2 /* $Header: CSEFPISB.pls 120.31 2011/12/29 09:11:41 jrkumar ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('CSE_DEBUG_OPTION'),'N');
5 g_request_id NUMBER := NVL(FND_GLOBAL.conc_request_id,-1);
6
7 PROCEDURE debug(
8 p_message IN varchar2)
9 IS
10 BEGIN
11 IF l_debug = 'Y' THEN
12 cse_debug_pub.add(p_message);
13 IF nvl(fnd_global.conc_request_id,-1) <> -1 THEN
14 fnd_file.put_line(fnd_file.log,p_message);
15 END IF;
16 END IF;
17 EXCEPTION
18 WHEN others THEN
19 null;
20 END debug;
21
22 PROCEDURE complete_csi_txn(
23 p_csi_txn_id IN number,
24 x_return_status OUT nocopy varchar2,
25 x_error_message OUT nocopy varchar2)
26 IS
27 l_txn_rec csi_datastructures_pub.transaction_rec;
28 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
29 l_msg_count number;
30 l_msg_data varchar2(2000);
31 BEGIN
32
33 x_return_status := fnd_api.g_ret_sts_success;
34
35 l_txn_rec.transaction_id := p_csi_txn_id;
36 l_txn_rec.source_group_ref_id := fnd_global.conc_request_id;
37 l_txn_rec.transaction_status_code := cse_datastructures_pub.g_complete ;
38
39 SELECT object_version_number
40 INTO l_txn_rec.object_version_number
41 FROM csi_transactions
42 WHERE transaction_id = l_txn_rec.transaction_id;
43
44 csi_transactions_pvt.update_transactions(
45 p_api_version => 1.0,
46 p_init_msg_list => fnd_api.g_true,
47 p_commit => fnd_api.g_false,
48 p_validation_level => fnd_api.g_valid_level_full,
49 p_transaction_rec => l_txn_rec,
50 x_return_status => l_return_status,
51 x_msg_count => l_msg_count,
52 x_msg_data => l_msg_data);
53
54 IF l_return_status <> fnd_api.g_ret_sts_success THEN
55 RAISE fnd_api.g_exc_error;
56 END IF;
57
58 EXCEPTION
59 WHEN fnd_api.g_exc_error THEN
60 x_return_status := fnd_api.g_ret_sts_error;
61 END complete_csi_txn;
62
63 PROCEDURE create_expitem(
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_error_message OUT NOCOPY VARCHAR2,
66 p_project_num IN VARCHAR2 ,
67 p_task_num IN VARCHAR2 ,
68 p_conc_request_id IN NUMBER )
69 IS
70 l_Api_Version NUMBER :=1;
71 l_Commit VARCHAR2(1) := FND_API.G_False;
72 l_Validation_Level NUMBER := FND_API.G_Valid_Level_Full;
73 l_Init_Msg_List VARCHAR2(1) := FND_API.G_True;
74 l_Msg_Data VARCHAR2(2000);
75 l_Msg_Count NUMBER;
76 l_Return_Status VARCHAR2(1);
77 l_Error_Message VARCHAR2(2000);
78 l_Msg_Text VARCHAR2(4000);
79 l_Msg_Index NUMBER;
80 l_org_id number;
81
82 l_project_id NUMBER;
83 l_task_id NUMBER;
84
85 l_Project_Id_in NUMBER;
86 l_Task_Id_in NUMBER;
87
88 l_depreciable VARCHAR2(1);
89 l_txn_error_id number;
90 l_txn_error_rec csi_datastructures_pub.transaction_error_rec;
91 l_in_srv_pa_attr_rec cse_datastructures_pub.proj_itm_insv_pa_attr_rec_type;
92
93 CURSOR project_id_cur(p_project_num IN VARCHAR2) IS
94 SELECT project_id
95 FROM pa_projects_all
96 WHERE segment1 = p_project_num;
97
98 CURSOR task_id_cur(p_project_id in number, p_task_num in varchar2) IS
99 SELECT task_id
100 FROM pa_tasks
101 WHERE project_id = p_project_id
102 AND task_number = p_task_num;
103
104 CURSOR inservice_txn_cur(p_project_id in number, p_task_id in number) IS
105 SELECT transaction_id,
106 transaction_date,
107 transacted_by,
108 transaction_quantity,
109 source_transaction_date,
110 object_version_number,
111 message_id,
112 source_header_ref_id project_id,
113 source_line_ref_id task_id
114 FROM csi_transactions
115 WHERE transaction_type_id = 108
116 AND transaction_status_code = cse_datastructures_pub.g_pending
117 AND source_header_ref_id = nvl(p_project_id, source_header_ref_id)
118 AND source_line_ref_id = nvl(p_task_id, source_line_ref_id);
119
120 CURSOR inservice_inst_cur(p_csi_txn_id IN number) IS
121 SELECT ciih.instance_id,
122 cii.inventory_item_id,
123 cii.last_vld_organization_id,
124 cii.lot_number,
125 cii.serial_number,
126 cii.inventory_revision,
127 cii.last_pa_project_id,
128 cii.last_pa_task_id,
129 cii.quantity,
130 cii.location_type_code,
131 cii.location_id,
132 cii.operational_status_code
133 FROM csi_item_instances_h ciih,
134 csi_item_instances cii
135 WHERE ciih.transaction_id = p_csi_txn_id
136 AND cii.instance_id = ciih.instance_id
137 AND (cii.operational_status_code = 'IN_SERVICE' OR ciih.new_operational_status_code = 'IN_SERVICE');
138
139 BEGIN
140
141 cse_util_pkg.set_debug;
142
143 debug('Inside API cse_fac_inservice_pkg.create_expitem');
144
145 x_return_status := g_ret_sts_success;
146 x_error_message := null;
147
148 debug(' param.project_number : '||p_project_num);
149 debug(' param.task_number : '||p_task_num);
150
151 l_project_id_in := NULL;
152 l_task_id_in := NULL;
153
154 IF NOT p_project_num IS NULL THEN
155
156 OPEN project_id_cur(p_project_num);
157 FETCH project_id_cur INTO l_project_id_in;
158 CLOSE project_id_cur;
159
160 IF p_task_num is not null THEN
161 OPEN task_id_cur(l_project_id_in,p_task_num);
162 FETCH task_id_cur INTO l_task_id_in;
163 CLOSE task_id_cur;
164 END IF;
165
166 END IF;
167
168 FOR inservice_txn_rec IN inservice_txn_cur(l_project_id_in, l_task_id_in)
169 LOOP
170
171 debug(' transaction_id : '||inservice_txn_rec.transaction_id);
172 debug(' transaction_qty : '||inservice_txn_rec.transaction_quantity);
173 debug(' source_txn_date : '||inservice_txn_rec.source_transaction_date);
174 debug(' transacted_by : '||inservice_txn_rec.transacted_by);
175
176 l_project_id := inservice_txn_rec.project_id;
177 l_task_id := inservice_txn_rec.task_id;
178
179 BEGIN
180
181 savepoint start_csi_transaction;
182
183 FOR inservice_inst_rec IN inservice_inst_cur(inservice_txn_rec.transaction_id)
184 LOOP
185
186 debug(' instance_id : '||inservice_inst_rec.instance_id);
187 debug(' inventory_item_id : '||inservice_inst_rec.inventory_item_id);
188 debug(' serial_number : '||inservice_inst_rec.serial_number);
189 debug(' organization_id : '||inservice_inst_rec.last_vld_organization_id);
190 debug(' last_pa_project_id : '||inservice_inst_rec.last_pa_project_id);
191 debug(' last_pa_task_id : '||inservice_inst_rec.last_pa_task_id);
192 debug(' location_type_code : '||inservice_inst_rec.location_type_code);
193 debug(' location_id : '||inservice_inst_rec.location_id);
194 debug(' operation_status_code: '||inservice_inst_rec.operational_status_code);
195
196 cse_util_pkg.check_depreciable(inservice_inst_rec.inventory_item_id, l_depreciable);
197
198
199 --Added for Bug 9326077
200 IF p_project_num is null THEN
201 l_project_id := NVL(inservice_inst_rec.last_pa_project_id,inservice_txn_rec.project_id); --Added NVL for Bug 9588315
202 END IF;
203 --End addition for Bug 9326077
204
205 --Added for Bug 9209549
206 IF p_task_num is null THEN
207 l_task_id := NVL(inservice_inst_rec.last_pa_task_id,inservice_txn_rec.task_id); --Added NVL for Bug 9588315
208 END IF;
209 --End addition for Bug 9209549
210
211 debug(' depreciable_flag : '||l_depreciable);
212
213 IF l_depreciable = 'N' THEN
214
215 SELECT org_id
216 INTO l_in_srv_pa_attr_rec.org_id
217 FROM pa_projects_all
218 WHERE project_id = l_project_id;
219
220 l_in_srv_pa_attr_rec.item_id := inservice_inst_rec.inventory_item_id;
221 l_in_srv_pa_attr_rec.inv_master_org_id := inservice_inst_rec.last_vld_organization_id;
222 l_in_srv_pa_attr_rec.serial_number := inservice_inst_rec.serial_number;
223 l_in_srv_pa_attr_rec.quantity := inservice_txn_rec.transaction_quantity;
224 l_in_srv_pa_attr_rec.location_id := inservice_inst_rec.location_id;
225 l_in_srv_pa_attr_rec.location_type := inservice_inst_rec.location_type_code;
226 l_in_srv_pa_attr_rec.project_id := l_project_id;
227 l_in_srv_pa_attr_rec.task_id := l_task_id;
228 l_in_srv_pa_attr_rec.transaction_date := inservice_txn_rec.transaction_date;
229 l_in_srv_pa_attr_rec.transacted_by := inservice_txn_rec.transacted_by;
230 l_in_srv_pa_attr_rec.message_id := inservice_txn_rec.message_id;
231 l_in_srv_pa_attr_rec.transaction_id := inservice_txn_rec.transaction_id;
232 l_in_srv_pa_attr_rec.instance_id := inservice_inst_rec.instance_id;
233 l_in_srv_pa_attr_rec.object_version_number := inservice_txn_rec.object_version_number;
234
235 cse_proj_item_in_srv_pkg.interface_nl_to_pa(
236 P_in_srv_pa_attr_rec => l_in_srv_pa_attr_rec,
237 p_conc_request_id => p_conc_request_id,
238 x_return_status => l_return_status,
239 x_error_message => l_error_message);
240
241 IF NOT l_Return_Status = g_ret_sts_success THEN
242 debug('error interfacing nl_to_pa '||substr(l_error_message,1,200));
243 RAISE fnd_api.g_exc_error;
244 END IF;
245
246 ELSE
247
248 complete_csi_txn(
249 p_csi_txn_id => inservice_txn_rec.transaction_id,
250 x_return_status => l_return_status,
251 x_error_message => l_error_message);
252
253 IF l_return_status <> fnd_api.g_ret_sts_success THEN
254 RAISE fnd_api.g_exc_error;
255 END IF;
256
257 END IF;
258
259 END LOOP;
260
261
262 EXCEPTION
263 WHEN fnd_api.g_exc_error THEN
264 rollback to start_csi_transaction;
265
266 x_return_status := g_ret_sts_error;
267
268 BEGIN
269 SELECT transaction_error_id
270 INTO l_txn_error_id
271 FROM csi_txn_errors
272 WHERE transaction_id = inservice_txn_rec.transaction_id
273 AND source_type = 'CSENIISEI'
274 AND rownum = 1;
275
276 UPDATE csi_txn_errors
277 SET error_text = l_error_message,
278 last_update_date = sysdate,
279 last_updated_by = fnd_global.user_id,
280 last_update_login = fnd_global.login_id
281 WHERE transaction_error_id = l_txn_error_id;
282
283 EXCEPTION
284 WHEN no_data_found THEN
285
286 l_txn_error_rec := cse_util_pkg.init_txn_error_rec;
287 l_txn_error_rec.error_text := l_error_message;
288 l_txn_error_rec.source_group_ref_id := NVL(p_conc_request_id,g_request_id);
289 l_txn_error_rec.transaction_id := inservice_txn_rec.transaction_id;
290 l_txn_error_rec.source_type := 'CSENIISEI';
291 l_txn_error_rec.source_id := inservice_txn_rec.transaction_id;
292 l_txn_error_rec.processed_flag := 'N';
293
294 csi_transactions_pvt.create_txn_Error(
295 P_api_version => l_api_version,
296 P_Init_Msg_List => l_init_msg_list,
297 P_Commit => l_commit,
298 p_validation_level => l_validation_level,
299 p_txn_error_rec => l_txn_error_rec,
300 X_Return_Status => l_return_status,
301 X_Msg_Count => l_msg_count,
302 X_Msg_Data => l_msg_data,
303 X_Transaction_Error_Id => l_txn_error_id);
304 END;
305
306 WHEN OTHERS THEN
307
308 rollback to start_csi_transaction;
309
310 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
311 fnd_message.set_token('ERR_MSG',SQLERRM);
312
313 x_return_status := g_ret_sts_unexp_error;
314 x_error_message := fnd_message.get;
315
316 BEGIN
317 SELECT transaction_error_id
318 INTO l_txn_error_id
319 FROM csi_txn_errors
320 WHERE transaction_id = inservice_txn_rec.transaction_id
321 AND source_type = 'CSENIISEI'
322 AND rownum = 1;
323
324 UPDATE csi_txn_errors
325 SET error_text = l_error_message,
326 last_update_date = sysdate,
327 last_updated_by = fnd_global.user_id,
328 last_update_login = fnd_global.login_id
329 WHERE transaction_error_id = l_txn_error_id;
330 EXCEPTION
331 WHEN no_data_found THEN
332 l_txn_error_rec := cse_util_pkg.init_txn_error_rec;
333 l_txn_error_rec.error_text := x_error_message;
334 l_txn_error_rec.Transaction_id := inservice_txn_rec.transaction_id;
335 l_txn_error_rec.source_group_ref_id := nvl(p_conc_request_id,g_request_id);
336 l_txn_error_rec.source_type := 'NORMAL_ITEM_EXP_ITEM';
337 l_txn_error_rec.source_id := null;
338 l_txn_error_rec.processed_flag := 'N';
339
340 CSI_Transactions_Pvt.Create_Txn_Error(
341 P_api_version => l_Api_Version,
342 P_Init_Msg_List => l_Init_Msg_List,
343 P_Commit => l_Commit,
344 p_validation_level => l_Validation_Level,
345 p_txn_error_rec => l_txn_error_rec,
346 X_Return_Status => l_Return_Status,
347 X_Msg_Count => l_Msg_Count,
348 X_Msg_Data => l_Msg_Data,
349 X_Transaction_Error_Id => l_txn_Error_Id);
350 END;
351 END;
352
353 COMMIT;
354 END LOOP;
355
356 debug('cse_fac_inservice_pkg.create_expitem completed successfully');
357
358 END Create_ExpItem;
359
360 PROCEDURE create_project_asset(
361 p_csi_txn_id IN number,
362 p_project_id IN number,
363 p_task_id IN number,
364 p_instance_id IN number,
365 p_serial_number IN varchar2,
366 p_date_placed_in_service IN date,
367 x_project_asset_id OUT nocopy number,
368 x_processed_flag OUT nocopy varchar2,
369 x_return_status OUT nocopy varchar2,
370 x_error_message OUT nocopy varchar2,
371 P_conc_request_id IN NUMBER)
372 IS
373
374 l_org_id number;
375 l_project_num varchar2(80);
376 l_project_name varchar2(240);
377 l_task_id number;
378 l_task_num varchar2(80);
379 l_task_name varchar2(240);
380 l_task_attribute10 varchar2(80);
381
382 l_date_placed_in_service date;
383 l_asset_units number;
384 l_asset_attrib_rec CSE_DATASTRUCTURES_PUB.asset_attrib_rec;
385
386 -- asset naming convention variables
387 l_anc_name varchar2(30);
388 l_anc_desc1 varchar2(30);
389 l_anc_desc2 varchar2(30);
390 l_anc_desc3 varchar2(30);
391 l_anc_sep varchar2(30);
392 l_anc_loc varchar2(30);
393 l_anc_cat varchar2(30);
394
395 l_skip_create boolean;
396
397 l_asset_name varchar2(240);
398 l_asset_description varchar2(300); -- Bug 5897139
399 l_asset_category varchar2(300);
400 l_asset_category_id number;
401 l_asset_location varchar2(300);
402 l_asset_location_id number;
403 l_book_type_code varchar2(15);
404 l_acc_flex_structure number;
405 l_deprn_expense_ccid number;
406 l_suffix number;
407
408 -- out variables
409 l_pa_project_id number;
410 l_pa_project_number varchar2(80);
411 l_pa_project_asset_id number;
412 l_pm_asset_reference varchar2(80);
413 l_source_ref varchar2(30);
414 l_asset_key_required varchar2(1);
415 l_asset_key_ccid number;
416
417
418 -- status and error handling variables
419 l_err_stack varchar2(2000);
420 l_err_stage varchar2(640);
421 l_err_code varchar2(640);
422 l_rejection_code varchar2(640);
423 l_error_message varchar2(2000);
424 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
425 l_msg_count number;
426 l_msg_data varchar2(4000);
427 l_hook_used number;
428
429 l_Api_Version NUMBER :=1;
430 l_Commit VARCHAR2(1) := FND_API.G_False;
431 l_Validation_Level NUMBER := FND_API.G_Valid_Level_Full;
432 l_Init_Msg_List VARCHAR2(1) := FND_API.G_True;
433 l_Msg_Text VARCHAR2(4000);
434 l_Transaction_Error_Id NUMBER;
435 l_txn_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_Error_Rec;
436
437 CURSOR exp_line_cur(p_project_id IN number, p_task_id IN number, p_instance_id IN number) IS
438 SELECT pei.expenditure_item_id expenditure_item_id,
439 pei.quantity quantity,
440 pei.Task_Id task_id,
441 pei.attribute6 attribute6,
442 pei.attribute7 attribute7,
443 pei.attribute8 attribute8,
444 pei.attribute9 attribute9,
445 pei.attribute10 attribute10
446 FROM pa_expenditure_items_all pei
447 WHERE pei.project_id = p_project_id
448 AND pei.task_Id = p_task_id
449 AND pei.transaction_source IN ('CSE_PO_RECEIPT', 'CSE_INV_ISSUE')
450 AND substr(pei.orig_transaction_reference,1,
451 instr(pei.orig_transaction_reference,'-') -1) = to_char(p_instance_id)
452 AND (pei.Attribute8 IS NOT NULL AND pei.Attribute9 IS NOT NULL)
453 AND pei.billable_flag ='Y'
454 AND nvl(pei.crl_asset_creation_status_code,'N') <> 'Y'
455 AND not exists (
456 SELECT 'This CDL was summarized before'
457 FROM pa_project_asset_line_details pald,
458 pa_project_asset_lines pal
459 WHERE pald.expenditure_item_id = pei.expenditure_item_id
460 AND pald.project_asset_line_detail_id = pal.project_asset_line_detail_id
461 AND pal.project_asset_id >= 1);
462
463 BEGIN
464
465 x_return_status := fnd_api.g_ret_sts_success;
466 x_processed_flag := 'N';
467
468 debug('Inside API create_project_asset');
469
470 SELECT segment1,
471 name,
472 org_id
473 INTO l_project_num,
474 l_project_name,
475 l_org_id
476 FROM pa_projects_all
477 WHERE project_id = p_project_id;
478
479 debug(' project_name : '||l_project_name);
480
481 SELECT task_number,
482 task_name,
483 attribute10
484 INTO l_task_num,
485 l_task_name,
486 l_task_attribute10
487 FROM pa_tasks
488 WHERE project_Id = p_project_id
489 AND task_id = p_task_id;
490
491 debug(' task_name : '||l_task_name);
492 debug(' org_id : '||l_org_id);
493
494 mo_global.set_policy_context('S',l_org_id);
495
496 SELECT asset_name,
497 asset_description1,
498 asset_description2,
499 asset_description3,
500 asset_desc_separator,
501 asset_location,
502 asset_category
503 INTO l_anc_name,
504 l_anc_desc1,
505 l_anc_desc2,
506 l_anc_desc3,
507 l_anc_sep,
508 l_anc_loc,
509 l_anc_cat
510 FROM ipa_asset_naming_convents_all
511 WHERE org_id = l_org_id;
512
513 debug('l_anc_name :'||l_anc_name);
514
515
516 FOR exp_line_rec IN exp_line_cur(p_project_id, p_task_id, p_instance_id)
517 LOOP
518 l_date_placed_in_service := p_date_placed_in_service ;
519 debug(' expenditure_item_id : '||exp_line_rec.expenditure_item_id);
520
521 BEGIN
522 SELECT ppa.project_asset_id,
523 ppa.date_placed_in_service,
524 ppa.asset_units
525 INTO l_pa_project_asset_id,
526 l_date_placed_in_service,
527 l_asset_units
528 FROM pa_project_asset_assignments ppaa,
529 pa_project_assets_all ppa
530 WHERE ppaa.project_id = p_project_id
531 AND ppaa.task_Id = p_task_id
532 AND ppaa.project_asset_id = ppa.project_asset_id
533 AND nvl(ppaa.Attribute6, '**##**') = nvl(exp_line_rec.attribute6, '**##**')
534 AND nvl(ppaa.Attribute7, '**##**') = nvl(exp_line_rec.attribute7, '**##**')
535 AND nvl(ppaa.Attribute8, '**##**') = nvl(exp_line_rec.attribute8, '**##**')
536 AND nvl(ppaa.Attribute9, '**##**') = nvl(exp_line_rec.attribute9, '**##**')
537 AND nvl(ppaa.Attribute10,'**##**') = nvl(exp_line_rec.attribute10,'**##**');
538 l_skip_create := TRUE;
539 EXCEPTION
540 WHEN no_data_found THEN
541 l_skip_create := FALSE;
542 WHEN too_many_rows THEN
543 l_skip_create := TRUE;
544 END;
545
546 IF NOT(l_skip_create) THEN
547
548 debug('processing_mode : CREATE');
549
550 l_asset_attrib_rec.Transaction_ID :=p_csi_txn_id;
551
552 l_error_message := fnd_api.g_miss_char;
553
554 cse_asset_client_ext_stub.get_asset_name(
555 p_asset_attrib_rec => l_asset_attrib_rec,
556 x_asset_name => l_asset_name,
557 x_hook_used => l_hook_used,
558 x_error_msg => l_error_message);
559
560 IF nvl(l_error_message,fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
561 x_error_message := l_error_message; --- Added for bug 6030501
562 RAISE fnd_api.g_exc_error;
563 END IF;
564
565 SELECT csi_pa_interface_s.nextval
566 INTO l_suffix
567 FROM sys.dual;
568
569 IF l_hook_used = 0 THEN
570 IF l_anc_name = 'ANT' THEN
571 l_asset_name := l_task_name;
572 ELSIF l_anc_name = 'ANP' THEN
573 l_asset_name := l_project_num;
574 ELSIF l_anc_name = 'ANGE1' THEN
575 l_asset_name := exp_line_rec.attribute8;
576 ELSIF l_anc_name = 'ANGE2' THEN
577 l_asset_name := exp_line_rec.attribute9;
578 ELSIF l_anc_name = 'ANGE3' THEN
579 l_asset_name := exp_line_rec.attribute10;
580 END IF;
581
582 l_asset_name := l_asset_name||'-'||l_suffix;
583
584 END IF;
585
586 cse_asset_client_ext_stub.get_asset_description(
587 p_asset_attrib_rec => l_asset_attrib_rec,
588 x_description => l_asset_description,
589 x_hook_used => l_hook_used,
590 x_error_msg => l_error_message);
591
592 IF l_hook_used = 0 THEN
593 SELECT decode(l_anc_desc1,
594 'ADT',l_task_name,
595 'ADP',l_project_name,
596 'ADGE1',exp_line_rec.attribute8,
597 'ADGE2',exp_line_rec.attribute9,
598 'ADGE3',exp_line_rec.attribute10)||
599 decode(l_anc_desc2,'None',null,l_anc_sep)||
600 decode(l_anc_desc2,
601 'ADT',l_task_name,
602 'ADP',l_project_name,
603 'ADGE1',exp_line_rec.attribute8,
604 'ADGE2',exp_line_rec.attribute9,
605 'ADGE3',exp_line_rec.attribute10)||
606 decode(l_anc_desc3,'None',null,l_anc_sep)||
607 decode(l_anc_desc3,
608 'ADT',l_task_name,
609 'ADP',l_project_name,
610 'ADGE1',exp_line_rec.attribute8,
611 'ADGE2',exp_line_rec.attribute9,
612 'ADGE3',exp_line_rec.attribute10)||
613 decode(exp_line_rec.attribute6,null,null,l_anc_sep||exp_line_rec.attribute6)||
614 decode(exp_line_rec.attribute7,null,null,l_anc_sep||exp_line_rec.attribute7)
615 INTO l_asset_description
616 FROM SYS.dual;
617
618 l_asset_description := substr(l_asset_description, 1, 80);
619
620 END IF;
621 IF l_anc_cat = 'ACT' THEN
622 l_asset_category := l_task_name;
623 ELSIF l_anc_cat = 'ACDF' THEN
624 l_asset_category := l_task_attribute10;
625 ELSIF l_anc_cat = 'ACGE1' THEN
626 l_asset_category := exp_line_rec.attribute8;
627 ELSIF l_anc_cat = 'ACGE2' THEN
628 l_asset_category := exp_line_rec.attribute9;
629 ELSIF l_anc_cat = 'ACGE3' THEN
630 l_asset_category := exp_line_rec.attribute10;
631 END IF;
632
633 SELECT category_id
634 INTO l_asset_category_id
635 FROM fa_categories
636 WHERE upper(segment1||segment2||segment3||segment4||segment5||segment6||segment7) =
637 upper(l_asset_category);
638
639 IF l_anc_loc = 'ALGE1' THEN
640 l_asset_location := exp_line_rec.attribute8;
641 ELSIF l_anc_loc = 'ALGE2' then
642 l_asset_location := exp_line_rec.attribute9;
643 ELSIF l_anc_loc = 'ALGE3' then
644 l_asset_location := exp_line_rec.attribute10;
645 END IF;
646
647 SELECT location_id
648 INTO l_asset_location_id
649 FROM fa_locations
650 WHERE upper(segment1||segment2||segment3||segment4||segment5||segment6||segment7) =
651 upper(l_asset_location);
652
653 --l_book_type_code := fnd_profile.value('cse_fa_book_type_code');
654
655 l_asset_attrib_rec.instance_id := p_instance_id; -- Bug 6492235, added for multiple FA book support
656
657 l_book_type_code := cse_asset_util_pkg.book_type(
658 p_asset_attrib_rec => l_asset_attrib_rec,
659 x_error_msg => l_error_message,
660 x_return_status => l_return_status);
661
662 IF l_return_status <> fnd_api.g_ret_sts_success THEN
663 x_error_message := l_error_message; --- Added for bug 6030501
664 RAISE fnd_api.g_exc_error;
665 END IF;
666
667 debug(' book_type_code : '||l_book_type_code);
668
669
670 IF l_return_status <> fnd_api.g_ret_sts_success THEN
671 RAISE fnd_api.g_exc_error;
672 END IF;
673
674 debug(' book_type_code : '||l_book_type_code);
675
676 l_error_message := fnd_api.g_miss_char;
677 cse_asset_client_ext_stub.get_deprn_expense_ccid(
678 p_asset_attrib_rec => l_asset_attrib_rec,
679 x_deprn_expense_ccid => l_deprn_expense_ccid,
680 x_hook_used => l_hook_used,
681 x_error_msg => l_error_message);
682
683 IF nvl(l_error_message,fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
684 x_error_message := l_error_message; --- Added for bug 6030501
685 RAISE fnd_api.g_exc_error;
686 END IF;
687
688 IF l_hook_used = 0 THEN
689 ipa_client_extension_pkg.get_default_deprn_expense(
690 p_book_type_code => l_book_type_code,
691 p_asset_category_id => l_asset_category_id,
692 p_location_id => l_asset_location_id ,
693 p_expenditure_item_id => exp_line_rec.expenditure_item_id ,
694 p_expense_ccid_out => l_deprn_expense_ccid,
695 p_err_stack => l_err_stack ,
696 p_err_stage => l_err_stage ,
697 p_err_code => l_err_code);
698
699 IF l_err_code <> '0' THEN
700 l_rejection_code := substr(l_err_code,1,30);
701 END IF;
702 END IF;
703
704 IF nvl(l_deprn_expense_ccid,0) > 0 THEN
705
706 SELECT accounting_flex_structure
707 INTO l_acc_flex_structure
708 FROM fa_book_controls
709 WHERE book_type_code = l_book_type_code;
710
711 IF NOT (FND_FLEX_KEYVAL.validate_ccid(
712 appl_short_name => 'SQLGL',
713 key_flex_code => 'GL#',
714 structure_number => l_acc_flex_structure,
715 combination_id => l_deprn_expense_ccid,
716 vrule => 'GL_ACCOUNT\\nGL_ACCOUNT_TYPE\\nI\\n' ||
717 'APPL=''OFA'';NAME=FA_SHARED_NOT_EXPENSE_ACCOUNT\\nE' ||
718 '\\0GL_GLOBAL\\nDETAIL_POSTING_ALLOWED\\nI\\n' ||
719 'APPL=''SQLGL'';NAME=GL Detail Posting Not Allowed\\nY' ||
720 '\\0\\nSUMMARY_FLAG\\nI\\n' ||
721 'APPL=''SQLGL'';NAME=GL summary credit debit\\nN'))
722 THEN
723 l_rejection_code := 'IFA_INVALID_DEPR_CCID';
724 END IF;
725 END IF;
726
727 l_source_ref := 'OAT-'||p_csi_txn_id||'-'||l_suffix;
728
729 --- Start of Fix for Bug 5887759
730
731 debug('Calling cse_asset_util_pkg.validate_ccid_required');
732 cse_asset_util_pkg.validate_ccid_required (l_asset_key_required);
733 debug('l_asset_key_required: '||l_asset_key_required);
734
735 IF l_asset_key_required = 'Y' THEN
736 debug('Before calling cse_asset_util_pkg.asset_key');
737 l_asset_attrib_rec.Transaction_ID := p_csi_txn_id;
738 l_asset_attrib_rec.Instance_ID := p_instance_id;
739 l_asset_key_ccid := cse_asset_util_pkg.asset_key(
740 l_asset_attrib_rec,
741 x_error_msg => l_msg_data,
742 x_return_status => l_return_status);
743
744 debug('After calling cse_asset_util_pkg.asset_key');
745 debug('l_asset_key_ccid: '||l_asset_key_ccid);
746
747 l_msg_count := 1;
748
749 debug('l_return_status : '||l_return_status);
750 debug('l_msg_data : '||l_msg_data);
751 debug('l_msg_count : '||l_msg_count);
752
753 l_msg_data := fnd_message.get;
754 debug('l_msg_data : '||l_msg_data);
755
756 IF l_return_status <> fnd_api.g_ret_sts_success THEN
757 x_error_message := l_msg_data; --- Added for bug 6030501
758 RAISE fnd_api.g_exc_error;
759 END IF;
760
761 END IF; -- l_asset_key_required
762
763 --- End of Fix for Bug 5887759
764
765
766 debug('Inside API pa_project_assets_pub.add_project_asset');
767
768 pa_project_assets_pub.add_project_asset(
769 p_api_version_number => 1.0,
770 p_init_msg_list => fnd_api.g_true,
771 p_pm_product_code => 'CSE',
772 p_pm_project_reference => l_project_num,
773 p_pa_project_id => p_project_id,
774 p_pa_asset_name => l_asset_name,
775 p_pm_asset_reference => l_source_ref,
776 p_asset_description => l_asset_description,
777 p_project_asset_type => 'AS-BUILT',
778 p_location_id => l_asset_location_id,
779 p_date_placed_in_service => l_date_placed_in_service,
780 p_asset_category_id => l_asset_category_id,
781 p_book_type_code => l_book_type_code,
782 p_asset_units => exp_line_rec.quantity,
783 p_depreciate_flag => 'Y',
784 p_depreciation_expense_ccid => l_deprn_expense_ccid,
785 p_amortize_flag => 'N',
786 p_attribute6 => exp_line_rec.attribute6,
787 p_attribute7 => exp_line_rec.attribute7,
788 p_attribute8 => exp_line_rec.attribute8,
789 p_attribute9 => exp_line_rec.attribute9,
790 p_attribute10 => exp_line_rec.attribute10,
791 p_serial_number => NVL(p_serial_number,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR), --Added for bug 9348689
792 p_pa_project_id_out => l_pa_project_id,
793 p_pa_project_number_out => l_pa_project_number,
794 p_pa_project_asset_id_out => l_pa_project_asset_id,
795 p_pm_asset_reference_out => l_pm_asset_reference,
796 p_return_status => l_return_status,
797 p_msg_count => l_msg_count,
798 p_msg_data => l_msg_data,
799 p_asset_key_ccid => l_asset_key_ccid);
800
801 l_msg_data := fnd_message.get;
802 debug('l_msg_data : '||l_msg_data);
803
804 IF l_return_status <> fnd_api.g_ret_sts_success THEN
805 debug('Error in API pa_project_assets_pub.add_project_asset'); -- Added for Bug 6152305
806 -- x_error_message := l_msg_data; --- Added for bug 6030501
807 --debug('l_msg_data : '||l_msg_data);
808 RAISE fnd_api.g_exc_error;
809 END IF;
810
811 debug(' pa_project_asset_id : '||l_pa_project_asset_id);
812
813 debug('Inside API pa_project_assets_pub.add_asset_assignment');
814
815 pa_project_assets_pub.add_asset_assignment(
816 p_api_version_number => 1.0,
817 p_init_msg_list => fnd_api.g_true,
818 p_pm_product_code => 'CSE',
819 p_pm_project_reference => l_project_num,
820 p_pa_project_id => p_project_id,
821 p_pm_task_reference => l_task_num,
822 p_pa_task_id => p_task_id,
823 p_pm_asset_reference => l_source_ref,
824 p_pa_project_asset_id => l_pa_project_asset_id,
825 p_attribute6 => exp_line_rec.attribute6,
826 p_attribute7 => exp_line_rec.attribute7,
827 p_attribute8 => exp_line_rec.attribute8,
828 p_attribute9 => exp_line_rec.attribute9,
829 p_attribute10 => exp_line_rec.attribute10,
830 p_pa_task_id_out => l_task_id,
831 p_pa_project_asset_id_out => l_pa_project_asset_id,
832 p_return_status => l_return_status,
833 p_msg_count => l_msg_count,
834 p_msg_data => l_msg_data);
835
836 IF l_return_status <> fnd_api.g_ret_sts_success THEN
837 debug('Error in API pa_project_assets_pub.add_asset_assignment'); -- Added for Bug 6152305
838 --x_error_message := l_msg_data; --- Added for bug 6030501
839 RAISE fnd_api.g_exc_error;
840 END IF;
841
842 x_processed_flag := 'Y';
843
844 ELSE
845
846 debug('processing_mode : UPDATE');
847 debug(' pa_project_asset_id : '||l_pa_project_asset_id);
848 debug(' asset_units : '||l_asset_units);
849 debug(' adjusted_units : '||exp_line_rec.quantity);
850
851 UPDATE pa_project_assets_all
852 SET asset_units = asset_units + exp_line_rec.quantity,
853 date_placed_in_service = nvl(date_placed_in_service, l_date_placed_in_service),
854 project_asset_type = 'AS-BUILT'
855 WHERE project_asset_id = l_pa_project_asset_id;
856
857 x_processed_flag := 'Y';
858
859 END IF;
860
861 UPDATE pa_expenditure_items_all
862 SET crl_asset_creation_status_code = 'Y'
863 WHERE expenditure_item_id = exp_line_rec.expenditure_item_id;
864
865 END LOOP;
866
867 x_project_asset_id := l_pa_project_asset_id;
868
869 EXCEPTION
870 WHEN fnd_api.g_exc_error THEN
871 ----- Code start for bug 6030501
872 IF x_error_message IS NULL then
873 x_error_message := nvl(cse_util_pkg.dump_error_stack, l_error_message);
874 END IF;
875 x_return_status := fnd_api.g_ret_sts_error;
876
877 debug(' Error Message '||x_error_message);
878 l_txn_error_rec := CSE_UTIL_PKG.Init_Txn_Error_Rec;
879 l_txn_error_rec.ERROR_TEXT := x_error_message;
880 l_txn_error_rec.source_group_ref_id := NVL(p_conc_request_id,g_request_id);
881 l_txn_error_rec.SOURCE_TYPE := 'NORMAL_ITEM_ASSET_UNITS';
882 l_txn_error_rec.SOURCE_ID := NULL;
883 l_txn_error_rec.PROCESSED_FLAG := 'N';
884
885 csi_transactions_pvt.create_txn_error(
886 P_api_version => l_Api_Version,
887 P_Init_Msg_List => l_Init_Msg_List,
888 P_Commit => l_Commit,
889 p_validation_level => l_Validation_Level,
890 p_txn_error_rec => l_txn_error_rec,
891 x_return_status => l_Return_Status,
892 x_msg_count => l_Msg_Count,
893 x_msg_data => l_Msg_Data,
894 x_transaction_error_id => l_Transaction_Error_Id);
895
896 -- x_error_message := nvl(cse_util_pkg.dump_error_stack, l_error_message);
897 x_return_status := fnd_api.g_ret_sts_error;
898
899 ---- Code end for bug 6030501
900 END create_project_asset ;
901
902
903 PROCEDURE create_pa_asset_headers(
904 errbuf OUT nocopy varchar2,
905 retcode OUT nocopy number,
906 p_project_id IN number,
907 p_task_id IN number,
908 P_conc_request_id IN NUMBER)
909 IS
910
911 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
912 l_error_message varchar2(2000);
913 l_project_asset_id number;
914 l_fa_group_by varchar2(30);
915 l_serial_number varchar2(200);
916
917 l_dpis date;
918 l_book_type_code varchar2(30) := fnd_profile.value('CSE_FA_BOOK_TYPE_CODE');
919
920 l_processed_flag varchar2(1) := 'N';
921 l_process_flag boolean := FALSE;
922 l_project_id NUMBER; -- Added for Bug 9326077
923 l_task_id NUMBER; -- Added for Bug 9326077
924
925 CURSOR insrv_txn_cur IS
926 SELECT transaction_id,
927 transacted_by,
928 transaction_quantity,
929 source_transaction_date dpis,
930 source_header_ref_id project_id,
931 source_line_ref_id task_id,
932 object_version_number csi_txn_ovn
933 FROM csi_transactions
934 WHERE transaction_type_id = 108 -- PROJECT_ITEM_IN_SERVICE
935 AND transaction_status_code = 'INTERFACED_TO_PA'
936 AND source_header_ref_id = nvl(p_project_id, source_header_ref_id)
937 AND source_line_ref_id = nvl(p_task_id, source_line_ref_id) ;
938
939 CURSOR insrv_inst_cur(p_csi_txn_id IN number) IS
940 SELECT cii.instance_id,
941 cii.serial_number,
942 cii.last_pa_project_id, --Added for Bug 9326077
943 cii.last_pa_task_id --Added for Bug 9326077
944 FROM csi_item_instances_h ciih,
945 csi_item_instances cii
946 WHERE ciih.transaction_id = p_csi_txn_id
947 AND cii.instance_id = ciih.instance_id
948 AND (ciih.new_operational_status_code = 'IN_SERVICE' OR cii.operational_status_code = 'IN_SERVICE');
949
950 l_Api_Version NUMBER :=1;
951 l_Commit VARCHAR2(1) := FND_API.G_False;
952 l_Validation_Level NUMBER := FND_API.G_Valid_Level_Full;
953 l_Init_Msg_List VARCHAR2(1) := FND_API.G_True;
954 l_Msg_Text VARCHAR2(4000);
955 l_Transaction_Error_Id NUMBER;
956 l_txn_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_Error_Rec;
957 l_msg_count number;
958 l_msg_data varchar2(4000);
959 l_hook_used number;
960 l_transaction_id NUMBER;
961
962 BEGIN
963
964 cse_util_pkg.set_debug;
965
966 debug('Inside API cse_fac_inservice_pkg.create_pa_asset_header');
967 debug('param.project_id : '||p_project_id);
968 debug('param.task_id : '||p_task_id);
969
970 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
971 csi_gen_utility_pvt.populate_install_param_rec;
972 END IF;
973
974 l_fa_group_by := csi_datastructures_pub.g_install_param_rec.fa_creation_group_by;
975
976 FOR insrv_txn_rec IN insrv_txn_cur
977 LOOP
978 l_transaction_id := insrv_txn_rec.transaction_id ;
979 l_process_flag := FALSE;
980
981 debug('transaction_rec # '||insrv_txn_cur%rowcount);
982 debug(' transaction_id : '||insrv_txn_rec.transaction_id);
983 debug(' transaction_qty : '||insrv_txn_rec.transaction_quantity);
984 debug(' src_transaction_date : '||insrv_txn_rec.dpis);
985
986 BEGIN
987 savepoint insrv_txn;
988
989 FOR insrv_inst_rec IN insrv_inst_cur(insrv_txn_rec.transaction_id)
990 LOOP
991
992 debug(' instance_id : '||insrv_inst_rec.instance_id);
993 debug(' serial_number : '||insrv_inst_rec.serial_number);
994
995 l_serial_number := insrv_inst_rec.serial_number;
996
997 IF l_fa_group_by = 'ITEM' THEN
998 l_serial_number := null;
999 END IF;
1000
1001 IF l_serial_number is null THEN
1002 BEGIN
1003 SELECT start_date
1004 INTO l_dpis
1005 FROM fa_book_controls fbc,
1006 fa_calendar_periods fcp
1007 WHERE fbc.book_type_code = l_book_type_code
1008 AND fcp.calendar_type = fbc.deprn_calendar
1009 AND trunc(insrv_txn_rec.dpis,'DDD') BETWEEN fcp.start_date AND fcp.end_date;
1010 EXCEPTION
1011 WHEN no_data_found THEN
1012 RAISE fnd_api.g_exc_error;
1013 END;
1014 ELSE
1015 l_dpis := insrv_txn_rec.dpis;
1016 END IF;
1017
1018 IF NVL(p_project_id,0) = 0 THEN --Added for Bug 9326077
1019 l_project_id := NVL(insrv_inst_rec.last_pa_project_id,insrv_txn_rec.project_id); --Added NVL for Bug 9654875
1020 ELSE
1021 l_project_id := insrv_txn_rec.project_id;
1022 END IF; --Added for Bug 9326077
1023 IF NVL(p_task_id,0) = 0 THEN --Added for Bug 9326077
1024 l_task_id := NVL(insrv_inst_rec.last_pa_task_id,insrv_txn_rec.task_id); --Added NVL for Bug 9654875
1025 ELSE
1026 l_task_id := insrv_txn_rec.task_id;
1027 END IF; --Added for Bug 9326077
1028
1029
1030 create_project_asset(
1031 p_csi_txn_id => insrv_txn_rec.transaction_id,
1032 p_project_id => l_project_id, --Added for Bug 9326077
1033 p_task_id => l_task_id, --Added for Bug 9326077
1034 p_instance_id => insrv_inst_rec.instance_id,
1035 p_serial_number => l_serial_number,
1036 p_date_placed_in_service => l_dpis,
1037 x_project_asset_id => l_project_asset_id,
1038 x_processed_flag => l_processed_flag,
1039 x_return_status => l_return_status,
1040 x_error_message => l_error_message,
1041 P_conc_request_id => P_conc_request_id);
1042
1043 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1044 RAISE fnd_api.g_exc_error;
1045 END IF;
1046
1047 IF l_processed_flag = 'Y' THEN
1048 l_process_flag := TRUE;
1049 END IF;
1050
1051 END LOOP;
1052
1053 IF l_process_flag THEN
1054
1055 complete_csi_txn(
1056 p_csi_txn_id => insrv_txn_rec.transaction_id,
1057 x_return_status => l_return_status,
1058 x_error_message => l_error_message);
1059
1060 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1061 RAISE fnd_api.g_exc_error;
1062 END IF;
1063
1064 debug('csi transaction interfaced to project asset successfully.');
1065
1066 commit;
1067
1068 END IF;
1069
1070 EXCEPTION
1071 WHEN fnd_api.g_exc_error THEN
1072 debug(l_error_message);
1073 rollback to insrv_txn;
1074 debug(' Error Message '||l_error_message);
1075 l_txn_error_rec := CSE_UTIL_PKG.Init_Txn_Error_Rec;
1076 l_txn_error_rec.ERROR_TEXT := l_error_message;
1077 l_txn_error_rec.source_group_ref_id := NVL(p_conc_request_id,g_request_id);
1078 l_txn_error_rec.SOURCE_TYPE := 'NORMAL_ITEM_ASSET_UNITS';
1079 l_txn_error_rec.SOURCE_ID := NULL;
1080 l_txn_error_rec.PROCESSED_FLAG := 'N';
1081 l_txn_error_rec.transaction_id := l_transaction_id;
1082
1083 csi_transactions_pvt.create_txn_error(
1084 P_api_version => l_Api_Version,
1085 P_Init_Msg_List => l_Init_Msg_List,
1086 P_Commit => l_Commit,
1087 p_validation_level => l_Validation_Level,
1088 p_txn_error_rec => l_txn_error_rec,
1089 x_return_status => l_Return_Status,
1090 x_msg_count => l_Msg_Count,
1091 x_msg_data => l_Msg_Data,
1092 x_transaction_error_id => l_Transaction_Error_Id);
1093 commit;
1094
1095 -- log error;
1096
1097 WHEN others THEN -- Added for Bug 7361370
1098 rollback to insrv_txn;
1099 debug(' Error Message '||sqlerrm);
1100 END;
1101
1102 END LOOP;
1103 EXCEPTION
1104 WHEN others THEN
1105 --x_return_status := fnd_api.g_ret_sts_error;
1106 retcode := -1;
1107 errbuf := sqlerrm;
1108 END create_pa_asset_headers;
1109
1110 PROCEDURE update_units(
1111 x_return_status OUT nocopy varchar2,
1112 x_error_message OUT nocopy varchar2,
1113 p_conc_request_id IN number)
1114 IS
1115 l_asset_id number;
1116 l_book_type_code varchar2(30);
1117 l_units number;
1118 l_location_id number;
1119 l_expense_ccid number;
1120 l_employee_id number;
1121 l_new_dist_id number;
1122
1123 l_msg_data varchar2(2000);
1124 l_msg_count number;
1125 l_return_status varchar2(1);
1126 l_error_message varchar2(4000);
1127
1128 l_instance_id number;
1129 l_instance_asset_id number;
1130 l_asset_quantity number;
1131 l_object_version_number number;
1132
1133 l_csi_txn_rec csi_datastructures_pub.Transaction_Rec;
1134 l_inst_asset_rec csi_datastructures_pub.Instance_Asset_Rec;
1135 l_asset_count_rec csi_asset_pvt.asset_count_rec ;
1136 l_asset_id_tbl csi_asset_pvt.asset_id_tbl ;
1137 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl ;
1138 l_lookup_tbl csi_asset_pvt.lookup_tbl ;
1139
1140 CURSOR txn_cur IS
1141 SELECT transaction_error_id,
1142 transaction_type_id,
1143 message_string
1144 FROM csi_txn_errors
1145 WHERE processed_flag = 'B'
1146 AND error_stage = 'FA_UPDATE'
1147 AND source_type = 'FA_UNIT_ADJUSTMENT_NORMAL';
1148
1149 BEGIN
1150
1151 x_return_status := fnd_api.g_ret_sts_success;
1152 x_error_message := NULL;
1153
1154 debug('inside api cse_fac_inservice_pkg.update_units');
1155
1156 FOR txn_rec IN txn_cur
1157 LOOP
1158
1159 BEGIN
1160
1161 savepoint eachtxn;
1162
1163 debug('unit adjustment record # '||txn_cur%rowcount);
1164
1165 l_asset_id := null;
1166
1167 cse_util_pkg.get_string_value(
1168 p_string => txn_rec.message_string,
1169 p_attribute => 'ASSET_ID',
1170 x_value => l_asset_id);
1171
1172 l_book_type_code := null;
1173
1174 cse_util_pkg.get_string_value(
1175 p_string => txn_rec.message_string,
1176 p_attribute => 'BOOK_TYPE_CODE',
1177 x_value => l_book_type_code);
1178
1179 l_units := null;
1180
1181 cse_util_pkg.get_string_value(
1182 p_string => txn_rec.message_string,
1183 p_attribute => 'UNITS',
1184 x_value => l_units);
1185
1186 l_location_id := null;
1187
1188 cse_util_pkg.get_string_value(
1189 p_string => txn_rec.message_string,
1190 p_attribute => 'LOCATION_ID',
1191 x_value => l_location_id);
1192
1193 l_expense_ccid := null;
1194
1195 cse_util_pkg.get_string_value(
1196 p_string => txn_rec.message_string,
1197 p_attribute => 'DEPRN_EXPENSE_CCID',
1198 x_value => l_expense_ccid);
1199
1200 l_employee_id := null;
1201
1202 cse_util_pkg.get_string_value(
1203 p_string => txn_rec.message_string,
1204 p_attribute => 'EMPLOYEE_ID',
1205 x_value => l_employee_id);
1206
1207 l_instance_id := null;
1208
1209 cse_util_pkg.get_string_value(
1210 p_string => txn_rec.message_string,
1211 p_attribute => 'INSTANCE_ID',
1212 x_value => l_instance_id);
1213
1214 debug(' asset_id : '||l_asset_id);
1215 debug(' book_type_code : '||l_book_type_code);
1216 debug(' units : '||l_units);
1217 debug(' location_id : '||l_location_id);
1218 debug(' expense_ccid : '||l_expense_ccid);
1219 debug(' employee_id : '||l_employee_id);
1220 debug(' instance_id : '||l_instance_id);
1221
1222 IF l_instance_id is not null THEN
1223
1224 SELECT instance_asset_id,
1225 asset_quantity,
1226 object_version_number
1227 INTO l_instance_asset_id,
1228 l_asset_quantity,
1229 l_object_version_number
1230 FROM csi_i_assets
1231 WHERE instance_id = l_instance_id
1232 AND fa_asset_id = l_asset_id
1233 AND sysdate between nvl(active_start_date, sysdate-1) and nvl(active_end_date, sysdate+1);
1234
1235 l_csi_txn_rec := cse_util_pkg.init_txn_rec;
1236 l_csi_txn_rec.transaction_date := sysdate;
1237 l_csi_txn_rec.source_transaction_date := sysdate;
1238 l_csi_txn_rec.transaction_quantity := l_units;
1239 l_csi_txn_rec.source_header_ref := 'ASSET_ID';
1240 l_csi_txn_rec.source_header_ref_id := l_asset_id;
1241 l_csi_txn_rec.transaction_status_code := cse_datastructures_pub.g_complete;
1242 l_csi_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
1243
1244 l_inst_asset_rec := cse_util_pkg.init_instance_asset_rec;
1245 l_inst_asset_rec.asset_quantity := l_asset_quantity + l_units;
1246 l_inst_asset_rec.instance_asset_id := l_instance_asset_id;
1247 l_inst_asset_rec.object_version_number := l_object_version_number;
1248 l_inst_asset_rec.update_status := cse_datastructures_pub.g_in_service;
1249 l_inst_asset_rec.active_end_date := null;
1250 l_inst_asset_rec.check_for_instance_expiry := fnd_api.g_false;
1251
1252 debug(' instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
1253 debug(' asset_quantity : '||l_inst_asset_rec.asset_quantity);
1254
1255 debug('calling csi_asset_pvt.update_instance_asset');
1256
1257 csi_asset_pvt.update_instance_asset(
1258 p_api_version => 1.0,
1259 p_commit => fnd_api.g_false,
1260 p_init_msg_list => fnd_api.g_true,
1261 p_validation_level => fnd_api.g_valid_level_full,
1262 p_instance_asset_rec => l_inst_asset_rec,
1263 p_txn_rec => l_csi_txn_rec,
1264 x_return_status => l_return_status,
1265 x_msg_count => l_msg_count,
1266 x_msg_data => l_msg_data,
1267 p_lookup_tbl => l_lookup_tbl,
1268 p_asset_count_rec => l_asset_count_rec,
1269 p_asset_id_tbl => l_asset_id_tbl,
1270 p_asset_loc_tbl => l_asset_loc_tbl);
1271
1272 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1273 RAISE fnd_api.g_exc_error;
1274 END IF;
1275
1276 END IF;
1277
1278 cse_ifa_trans_pkg.adjust_fa_distribution(
1279 p_asset_id => l_asset_id,
1280 p_book_type_code => l_book_type_code,
1281 p_units => l_units,
1282 p_location_id => l_location_id,
1283 p_expense_ccid => l_expense_ccid,
1284 p_employee_id => l_employee_id,
1285 x_new_dist_id => l_new_dist_id,
1286 x_return_status => l_return_status,
1287 x_error_msg => l_error_message);
1288
1289 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1290 RAISE fnd_api.g_exc_error;
1291 END IF;
1292
1293 UPDATE csi_txn_errors
1294 SET processed_flag = 'S',
1295 source_group_ref_id = fnd_global.conc_request_id,
1296 last_update_date = sysdate,
1297 last_updated_by = fnd_global.user_id
1298 WHERE transaction_error_id = txn_rec.transaction_error_id;
1299
1300 commit work;
1301
1302 EXCEPTION
1303 WHEN fnd_api.g_exc_error THEN
1304
1305 rollback to eachtxn;
1306
1307 UPDATE csi_txn_errors
1308 SET error_text = x_error_message,
1309 source_group_ref_id = fnd_global.conc_request_id,
1310 last_update_date = sysdate,
1311 last_updated_by = fnd_global.user_id
1312 WHERE transaction_error_id = txn_rec.transaction_error_id;
1313
1314 commit work;
1315
1316 END;
1317 END LOOP;
1318
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 x_return_status := fnd_api.g_ret_sts_unexp_error;
1322 x_error_message := sqlerrm;
1323 debug(' ERROR : '||x_error_message);
1324 END Update_Units;
1325
1326 END cse_fac_inservice_pkg;