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