[Home] [Help]
PACKAGE BODY: APPS.PA_FAXFACE
Source
1 PACKAGE BODY PA_FAXFACE AS
2 /* $Header: PAFAXB.pls 120.49.12010000.10 2008/10/23 10:38:25 rmandali ship $ */
3
4 TYPE t_rec is record (group_level_task_id NUMBER(15),
5 asset_category_id NUMBER(15),
6 system_linkage_function VARCHAR2(3),
7 document_header_id NUMBER(15),
8 document_line_number NUMBER(15),
9 document_distribution_id NUMBER(15),
10 expenditure_category VARCHAR2(30),
11 expenditure_type VARCHAR2(30),
12 non_labor_resource VARCHAR2(20),
13 cip_ccid NUMBER(15),
14 cip_cost NUMBER(15),
15 expenditure_item_id NUMBER(15),
16 line_num NUMBER(15),
17 cip_grouping_method_code VARCHAR2(30),
18 grouping_method VARCHAR2(2000),
19 override_asset_assignment_flag VARCHAR2(1),
20 vendor_invoice_grouping_code VARCHAR2(1),
21 project_id NUMBER(15),
22 task_id NUMBER(15),
23 top_task NUMBER(15),
24 expenditure_id NUMBER(15),
25 organization_id NUMBER(15),
26 ATTRIBUTE1 VARCHAR2(150),
27 ATTRIBUTE2 VARCHAR2(150),
28 ATTRIBUTE3 VARCHAR2(150),
29 ATTRIBUTE4 VARCHAR2(150),
30 ATTRIBUTE5 VARCHAR2(150),
31 ATTRIBUTE6 VARCHAR2(150),
32 ATTRIBUTE7 VARCHAR2(150),
33 ATTRIBUTE8 VARCHAR2(150),
34 ATTRIBUTE9 VARCHAR2(150),
35 ATTRIBUTE10 VARCHAR2(150),
36 ATTRIBUTE_CATEGORY VARCHAR2(30),
37 capital_event_id number);
38
39 TYPE OpenCursorType is REF CURSOR;
40
41 l_mrc_flag VARCHAR2(1) := paapimp_pkg.get_mrc_flag;
42
43 FUNCTION check_required_segment (structnum number) RETURN varchar2 IS
44 fftype fnd_flex_key_api.flexfield_type;
45 numstruct number;
46 liststruct fnd_flex_key_api.structure_list;
47 thestruct fnd_flex_key_api.structure_type;
48 numsegs number;
49 listsegs fnd_flex_key_api.segment_list;
50 segtype fnd_flex_key_api.segment_type;
51 segname fnd_id_flex_segments.segment_name%TYPE;
52 BEGIN
53 fnd_flex_key_api.set_session_mode('seed_data');
54 fftype := fnd_flex_key_api.find_flexfield(appl_short_name =>'OFA',
55 flex_code =>'KEY#');
56 thestruct := fnd_flex_key_api.find_structure(fftype,structnum);
57 fnd_flex_key_api.get_segments(fftype,thestruct,TRUE,numsegs,listsegs);
58 for i in 1 .. numsegs loop
59 segtype := fnd_flex_key_api.find_segment(fftype,thestruct,listsegs(i));
60 if (segtype.required_flag = 'Y' and segtype.enabled_flag = 'Y') then
61 return('Y');
62 end if;
63 end loop;
64 return('N');
65 END;
66
67
68 PROCEDURE set_in_service_thru_date(x_passed_thru_date IN DATE) IS
69 BEGIN
70 x_in_service_thru_date := x_passed_thru_date;
71 END set_in_service_thru_date;
72
73 FUNCTION get_in_service_thru_date RETURN DATE is
74 BEGIN
75 return(x_in_service_thru_date);
76 END get_in_service_thru_date;
77
78 -- Initialize function
79 FUNCTION initialize RETURN NUMBER IS
80 x_err_code NUMBER:=0;
81 BEGIN
82
83 RETURN 0;
84 EXCEPTION
85 WHEN OTHERS THEN
86 x_err_code := SQLCODE;
87 RETURN x_err_code;
88 END initialize;
89
90 FUNCTION get_group_level_task_id
91 ( x_task_id IN NUMBER,
92 x_top_task_id IN NUMBER,
93 x_project_id IN NUMBER)
94 RETURN NUMBER
95 IS
96 dummy VARCHAR2(10);
97 group_level_task_id NUMBER;
98 cursor c_asset_on_task is
99 select task_id
100 from pa_project_asset_assignments
101 where project_id = x_project_id
102 and task_id = x_task_id ;
103 cursor c_asset_on_top_task is
104 select task_id
105 from pa_project_asset_assignments
106 where project_id = x_project_id
107 and task_id = x_top_task_id ;
108 cursor c_asset_on_project is
109 select task_id
110 from pa_project_asset_assignments
111 where project_id = x_project_id ;
112 BEGIN
113 OPEN c_asset_on_task ;
114 FETCH c_asset_on_task into group_level_task_id ;
115 IF c_asset_on_task%NOTFOUND THEN
116 OPEN c_asset_on_top_task ;
117 FETCH c_asset_on_top_task into group_level_task_id ;
118 IF c_asset_on_top_task%NOTFOUND THEN
119 OPEN c_asset_on_project ;
120 FETCH c_asset_on_project into group_level_task_id ;
121 IF c_asset_on_project%NOTFOUND THEN
122 group_level_task_id := NULL ;
123 END IF ;
124 CLOSE c_asset_on_project ;
125 END IF ;
126 CLOSE c_asset_on_top_task ;
127 END IF ;
128 CLOSE c_asset_on_task ;
129 RETURN group_level_task_id ;
130 EXCEPTION
131 WHEN OTHERS THEN
132 RAISE;
133 END get_group_level_task_id;
134
135 /* R12 changes: Parameters will have following values :
136 For AP : x_doc_header_id -> invoice_id
137 x_doc_line_num -> invoice_line_num
138 x_doc_dist_id -> invoice_distribution_id
139
140 For PO: x_doc_header_id -> po_header_id
141 x_doc_line_num -> po_distribution_id
142 x_doc_dist_id -> rcv_transaction_id
143 */
144
145 FUNCTION get_asset_category_id
146 ( x_doc_header_id IN NUMBER,
147 x_doc_line_num IN NUMBER,
148 x_doc_dist_id IN NUMBER,
149 x_transaction_source IN VARCHAR2)
150 RETURN NUMBER
151 IS
152 asset_category_id NUMBER;
153 -- Fix for bug : 4878878
154 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
155 BEGIN
156
157 --Bug 3250512: With receipt accruals the cdl.system_reference2 and cdl.system_reference3 corresponds to
158 --po_header_id and po_distribution_id. Hence need to add another SQL to fetch asset category for
159 --receipt accrual items.
160 IF G_debug_mode = 'Y' THEN
161 pa_debug.debug('Inside get_asset_category_id, x_doc_header_id = ' || x_doc_header_id ||
162 ' x_doc_line_num = '|| x_doc_line_num ||
163 ' x_doc_dist_id = '|| x_doc_dist_id ||
164 ' x_transaction_source = ' || x_transaction_source);
165 END IF;
166 -- Added 'CSE_IPV_ADJUSTMENT_DEPR','CSE_IPV_ADJUSTMENT' for bug: 5036815
167 If x_transaction_source in ('AP INVOICE', 'AP EXPENSE', 'AP NRTAX', 'AP DISCOUNTS', 'AP VARIANCE', 'AP ERV', /* Bug 5284323 */
168 'CSE_IPV_ADJUSTMENT_DEPR','CSE_IPV_ADJUSTMENT') Then
169
170 SELECT
171 mtlsi.asset_category_id
172 INTO
173 asset_category_id
174 FROM
175 ap_invoice_distributions_all apid,
176 po_distributions pod,
177 po_lines pol,
178 financials_system_parameters fsp,
179 mtl_system_items mtlsi
180 WHERE
181 apid.invoice_id = x_doc_header_id
182 AND apid.invoice_distribution_id = x_doc_dist_id
183 AND apid.po_distribution_id = pod.po_distribution_id
184 AND pod.po_line_id = pol.po_line_id
185 AND pol.item_id = mtlsi.inventory_item_id
186 AND mtlsi.organization_id = fsp.inventory_organization_id;
187 -- Added 'CSE_PO_RECEIPT_DEPR','CSE_PO_RECEIPT' for bug 5036815
188 ElsIf x_transaction_source in ('PO RECEIPT', 'PO RECEIPT NRTAX',
189 'PO RECEIPT NRTAX PRICE ADJ', 'PO RECEIPT PRICE ADJ','CSE_PO_RECEIPT_DEPR','CSE_PO_RECEIPT') Then
190
191 SELECT
192 mtlsi.asset_category_id
193 INTO
194 asset_category_id
195 FROM
196 po_distributions pod,
197 po_lines pol,
198 financials_system_parameters fsp,
199 mtl_system_items mtlsi
200 WHERE
201 pod.po_header_id = x_doc_header_id
202 AND pod.po_distribution_id = x_doc_line_num
203 AND pod.po_line_id = pol.po_line_id
204 AND pol.item_id = mtlsi.inventory_item_id
205 AND mtlsi.organization_id = fsp.inventory_organization_id;
206
207 End If;
208
209 IF G_debug_mode = 'Y' THEN
210 pa_debug.debug('asset_category_id = ' || asset_category_id);
211 END IF;
212
213 RETURN asset_category_id;
214
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 RETURN NULL;
218 WHEN OTHERS THEN
219 RAISE;
220 END get_asset_category_id;
221
222 PROCEDURE get_asset_id
223 ( x_project_id IN NUMBER,
224 x_system_linkage_function IN VARCHAR2,
225 x_grp_level_task_id IN NUMBER,
226 x_asset_category_id IN NUMBER,
227 x_line_type IN VARCHAR2,
228 x_capital_event_id IN NUMBER,
229 x_asset_id OUT NOCOPY NUMBER,
230 x_num_asset_assigned OUT NOCOPY NUMBER)
231 IS
232 CURSOR selassets IS
233 SELECT
234 paa.project_asset_id,
235 ppa.asset_category_id
236 FROM
237 pa_project_asset_assignments paa,
238 pa_project_assets ppa
239 WHERE
240 paa.project_id = x_project_id
241 AND ppa.project_asset_type(+) = DECODE(x_line_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
242 AND ppa.capital_event_id(+) IS NOT NULL
243 AND ppa.capital_event_id(+) = x_capital_event_id
244 AND ppa.capital_hold_flag(+) = 'N'
245 AND ppa.project_asset_id(+) = paa.project_asset_id
246 AND paa.task_id = x_grp_level_task_id;
247
248 assetrec selassets%ROWTYPE;
249 num_asset_assignments NUMBER;
250 asset_id_matched NUMBER;
251 num_of_asset_matched NUMBER; --- Number of assets with matched categ.
252 l_asset_id NUMBER;
253
254 BEGIN
255
256 num_asset_assignments := 0;
257 num_of_asset_matched := 0;
258
259 FOR assetrec IN selassets LOOP
260
261 num_asset_assignments := num_asset_assignments + 1;
262 x_num_asset_assigned:=num_asset_assignments;
263 l_asset_id := assetrec.project_asset_id;
264
265 IF x_asset_category_id IS NULL AND num_asset_assignments > 1 THEN
266 x_asset_id:=0 ;
267 RETURN ;
268 END IF;
269
270 IF ( x_asset_category_id IS NOT NULL AND x_system_linkage_function = 'VI') THEN
271
272 -- try to match the asset category
273
274 IF ( x_asset_category_id = assetrec.asset_category_id ) THEN
275
276 num_of_asset_matched := num_of_asset_matched + 1;
277 asset_id_matched := assetrec.project_asset_id;
278
279 IF num_of_asset_matched > 1 THEN
280 x_asset_id:=0;
281 RETURN ;
282 END IF;
283
284 END IF;
285 END IF;
286 END LOOP;
287
288 IF ( num_of_asset_matched = 1 ) THEN
289 x_asset_id:=asset_id_matched;
290 RETURN ;
291 END IF;
292
293 IF ( num_asset_assignments = 1 ) THEN
294 x_asset_id:= l_asset_id;
295 RETURN ;
296
297 END IF;
298
299 x_asset_id:=0;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 RAISE;
304 END get_asset_id;
305
306 PROCEDURE get_asset_attributes
307 ( x_project_asset_id IN NUMBER,
308 x_depreciation_expense_ccid IN OUT NOCOPY NUMBER,
309 x_err_stage IN OUT NOCOPY VARCHAR2,
310 x_err_code IN OUT NOCOPY NUMBER)
311 IS
312 BEGIN
313
314 x_err_code := 0;
315 x_err_stage := 'Getting asset attributes';
316
317 SELECT
318 depreciation_expense_ccid
319 INTO
320 x_depreciation_expense_ccid
321 FROM
322 pa_project_assets
323 WHERE
324 project_asset_id = x_project_asset_id;
325
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328 NULL;
329 WHEN OTHERS THEN
330 x_err_code := SQLCODE;
331 RAISE;
332 END get_asset_attributes;
333
334 -- This procedure updates the REVERSE_FLAG column for the given
335 -- Project Id for the assets with reverse_flag = 'Y'
336
337 PROCEDURE find_assets_to_be_reversed
338 (x_project_id IN NUMBER,
339 x_asset_found IN OUT NOCOPY BOOLEAN,
340 x_capital_event_id IN NUMBER,
341 x_err_stage IN OUT NOCOPY VARCHAR2,
342 x_err_code IN OUT NOCOPY NUMBER)
343 IS
344 BEGIN
345
346 x_err_code := 0;
347 x_err_stage := 'Finding the assets to be reversed';
348 x_asset_found := TRUE;
349
350 UPDATE
351 pa_project_assets ppa
352 SET
353 ppa.reverse_flag = 'S'
354 WHERE
355 ppa.project_id = x_project_id
356 AND ppa.capital_event_id = NVL(x_capital_event_id, ppa.capital_event_id)
357 AND ppa.reverse_flag = 'Y';
358
359 IF ( SQL%ROWCOUNT = 0 ) THEN
360 x_asset_found := FALSE;
361 END IF;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 x_err_code := SQLCODE;
366 RAISE;
367 END find_assets_to_be_reversed;
368
369 -- This procedure checks for the given detail_id
370 -- that all the lines are to be reversed are in the
371 -- current batch or not
372
373
374 PROCEDURE check_asset_to_be_reversed
375 (x_proj_asset_line_detail_id IN NUMBER,
376 x_asset_found IN OUT NOCOPY BOOLEAN,
377 x_err_stage IN OUT NOCOPY VARCHAR2,
378 x_err_code IN OUT NOCOPY NUMBER)
379 IS
380 dummy VARCHAR2(10);
381 BEGIN
382
383 x_err_code := 0;
384 x_err_stage := 'Checking the assets to be reversed';
385 x_asset_found := TRUE;
386
387 SELECT
388 'Exist'
389 INTO
390 dummy
391 FROM sys.dual
392 WHERE exists
393 (SELECT
394 'Yes'
395 FROM
396 pa_project_asset_lines pal
397 WHERE
398 pal.project_asset_line_detail_id = x_proj_asset_line_detail_id
399 AND NOT EXISTS
400 ( SELECT
401 'This Line was adjusted before'
402 FROM
403 pa_project_asset_lines ppal
404 WHERE
405 ppal.rev_proj_asset_line_id = pal.project_asset_line_id
406 )
407 AND pal.project_asset_id NOT IN
408 ( SELECT
409 project_asset_id
410 FROM
411 pa_project_assets pas
412 WHERE
413 pas.reverse_flag = 'S'
414 AND pas.project_id = pal.project_id
415 )
416 UNION
417 SELECT
418 'Yes'
419 FROM
420 pa_project_asset_lines pal
421 WHERE
422 pal.project_asset_line_detail_id = x_proj_asset_line_detail_id
423 AND pal.transfer_status_code <> 'T'
424 AND pal.rev_proj_asset_line_id IS NULL
425 AND pal.project_asset_id IN
426 ( SELECT
427 project_asset_id
428 FROM
429 pa_project_assets pas
430 WHERE
431 pas.reverse_flag = 'S'
432 AND pas.project_id = pal.project_id
433 )
434 );
435
436 EXCEPTION
437 WHEN NO_DATA_FOUND THEN
438 x_asset_found := FALSE;
439 WHEN OTHERS THEN
440 x_err_code := SQLCODE;
441 RAISE;
442 END check_asset_to_be_reversed;
443
444 PROCEDURE check_proj_asset_lines
445 (x_proj_asset_line_detail_id IN NUMBER,
446 x_line_found IN OUT NOCOPY BOOLEAN,
447 x_err_stage IN OUT NOCOPY VARCHAR2,
448 x_err_code IN OUT NOCOPY NUMBER)
449 IS
450 dummy VARCHAR2(10);
451 BEGIN
452
453 x_err_code := 0;
454 x_err_stage := 'Checking the asset lines to be regenerated';
455 x_line_found := TRUE;
456
457 SELECT
458 'Exist'
459 INTO
460 dummy
461 FROM sys.dual
462 WHERE exists
463 (SELECT
464 'Yes'
465 FROM
466 pa_project_asset_lines pal
467 WHERE
468 pal.project_asset_line_detail_id = x_proj_asset_line_detail_id
469 AND pal.transfer_status_code <> 'P'
470 );
471
472 EXCEPTION
473 WHEN NO_DATA_FOUND THEN
474 x_line_found := FALSE;
475 WHEN OTHERS THEN
476 x_err_code := SQLCODE;
477 RAISE;
478 END check_proj_asset_lines;
479
480 -- This procedure updates the pa_project_asset_line_details
481 -- when all the project asset lines for a detail_line_id
482 -- are reversed then the detail itself are marked as reversed
483
484 PROCEDURE update_line_details
485 (x_proj_asset_line_detail_id IN NUMBER,
486 x_err_stage IN OUT NOCOPY VARCHAR2,
487 x_err_code IN OUT NOCOPY NUMBER)
488 IS
489 BEGIN
490
491 x_err_code := 0;
492 x_err_stage := 'Updating the detail lines';
493
494 UPDATE
495 pa_project_asset_line_details
496 SET
497 reversed_flag = 'Y',
498 last_update_date = sysdate,
499 last_updated_by = x_last_updated_by,
500 last_update_login = x_last_update_login,
501 request_id = x_request_id,
502 program_application_id = x_program_application_id,
503 program_id = x_program_id,
504 program_update_date = sysdate
505 WHERE
506 project_asset_line_detail_id =
507 x_proj_asset_line_detail_id;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 x_err_code := SQLCODE;
512 RAISE;
513 END update_line_details;
514
515 PROCEDURE update_expenditure_items
516 (x_proj_asset_line_detail_id IN NUMBER,
517 x_revenue_distributed_flag IN VARCHAR2,
518 x_err_stage IN OUT NOCOPY VARCHAR2,
519 x_err_code IN OUT NOCOPY NUMBER)
520 IS
521 BEGIN
522
523 x_err_code := 0;
524 x_err_stage := 'Updating the expenditure_items';
525
526 UPDATE
527 pa_expenditure_items_all pei
528 SET
529 revenue_distributed_flag = x_revenue_distributed_flag,
530 last_update_date = sysdate,
531 last_updated_by = x_last_updated_by,
532 last_update_login = x_last_update_login,
533 request_id = x_request_id,
534 program_application_id = x_program_application_id,
535 program_id = x_program_id,
536 program_update_date = sysdate
537 WHERE
538 pei.expenditure_item_id IN
539 ( SELECT
540 expenditure_item_id
541 FROM
542 pa_project_asset_line_details pald
543 WHERE
544 project_asset_line_detail_id =
545 x_proj_asset_line_detail_id
546 GROUP BY expenditure_item_id
547 );
548
549 EXCEPTION
550 WHEN OTHERS THEN
551 x_err_code := SQLCODE;
552 RAISE;
553 END update_expenditure_items;
554
555 -- procedure to update asset costs
556
557 PROCEDURE update_asset_cost
558 (x_project_asset_id IN NUMBER,
559 x_grouped_cip_cost IN NUMBER,
560 x_capitalized_cost IN NUMBER,
561 x_err_stage IN OUT NOCOPY VARCHAR2,
562 x_err_code IN OUT NOCOPY NUMBER)
563 IS
564 BEGIN
565
566 x_err_code := 0;
567 x_err_stage := 'Updating the asset cost';
568
569 -- Check if the project_asset_id is Valid
570
571 IF ( x_project_asset_id > 0 ) THEN
572
573 UPDATE
574 pa_project_assets ppa
575 SET
576 ppa.grouped_cip_cost = NVL(ppa.grouped_cip_cost,0) +
577 x_grouped_cip_cost,
578 ppa.capitalized_cost = NVL(ppa.capitalized_cost,0) +
579 x_capitalized_cost
580 WHERE
581 ppa.project_asset_id = x_project_asset_id;
582
583 END IF;
584
585 EXCEPTION
586 WHEN OTHERS THEN
587 x_err_code := SQLCODE;
588 RAISE;
589 END update_asset_cost;
590
591 -- procedure to create project_asset_lines
592
593 PROCEDURE create_project_asset_lines
594 (x_description IN VARCHAR2,
595 x_project_asset_id IN NUMBER,
596 x_project_id IN NUMBER,
597 x_task_id IN NUMBER,
598 x_cip_ccid IN NUMBER,
599 x_asset_cost_ccid IN NUMBER,
600 x_original_asset_cost IN NUMBER,
601 x_current_asset_cost IN NUMBER,
602 x_project_asset_line_detail_id IN NUMBER,
603 x_gl_date IN DATE,
604 x_transfer_status_code IN VARCHAR2,
605 x_transfer_rejection_reason IN VARCHAR2,
606 x_amortize_flag IN VARCHAR2,
607 x_asset_category_id IN NUMBER,
608 x_rev_proj_asset_line_id IN NUMBER,
609 x_rev_from_proj_asset_line_id IN NUMBER,
610 x_invoice_number IN VARCHAR2,
611 x_vendor_number IN VARCHAR2,
612 x_po_vendor_id IN NUMBER,
613 x_po_number IN VARCHAR2,
614 x_invoice_date IN DATE,
615 x_invoice_created_by IN NUMBER,
616 x_invoice_updated_by IN NUMBER,
617 x_invoice_id IN NUMBER,
618 x_payables_batch_name IN VARCHAR2,
619 x_ap_dist_line_number IN Number,
620 x_invoice_distribution_id IN Number, -- R12 new
621 x_orig_asset_id IN Number,
622 x_line_type IN VARCHAR2,
623 x_capital_event_id IN NUMBER,
624 x_retirement_cost_type IN VARCHAR2,
625 x_err_stage IN OUT NOCOPY VARCHAR2,
626 x_err_code IN OUT NOCOPY NUMBER)
627 IS
628 project_asset_line_id NUMBER;
629 L_org_id NUMBER:= PA_MOAC_UTILS.get_current_org_id ;
630 BEGIN
631 x_err_code := 0;
632 x_err_stage := 'Create Project Asset Line';
633
634 SELECT pa_project_asset_lines_s.nextval
635 INTO project_asset_line_id
636 FROM sys.dual;
637
638 INSERT INTO pa_project_asset_lines(
639 project_asset_line_id,
640 description,
641 project_asset_id,
642 project_id,
643 task_id,
644 cip_ccid,
645 asset_cost_ccid,
646 original_asset_cost,
647 current_asset_cost,
648 project_asset_line_detail_id,
649 gl_date,
650 transfer_status_code,
651 transfer_rejection_reason,
652 amortize_flag,
653 asset_category_id,
654 last_update_date,
655 last_updated_by,
656 created_by,
657 creation_date,
658 last_update_login,
659 request_id,
660 program_application_id,
661 program_id,
662 rev_proj_asset_line_id,
663 rev_from_proj_asset_line_id,
664 invoice_number,
665 vendor_number,
666 po_vendor_id,
667 po_number,
668 invoice_date,
669 invoice_created_by,
670 invoice_updated_by,
671 invoice_id,
672 payables_batch_name,
673 ap_distribution_line_number,
674 invoice_distribution_id, -- R12 new
675 original_asset_id
676 ,line_type
677 ,capital_event_id
678 ,retirement_cost_type
679 ,org_id
680 )
681 SELECT
682 project_asset_line_id,
683 UPPER(x_description),
684 x_project_asset_id,
685 x_project_id,
686 x_task_id,
687 x_cip_ccid,
688 x_asset_cost_ccid,
689 x_original_asset_cost,
690 x_current_asset_cost,
691 x_project_asset_line_detail_id,
692 x_gl_date,
693 x_transfer_status_code,
694 x_transfer_rejection_reason,
695 x_amortize_flag,
696 x_asset_category_id,
697 sysdate,
698 x_last_updated_by,
699 x_created_by,
700 sysdate,
701 x_last_update_login,
702 x_request_id,
703 x_program_application_id,
704 x_program_id,
705 x_rev_proj_asset_line_id,
706 x_rev_from_proj_asset_line_id,
707 x_invoice_number,
708 x_vendor_number,
709 x_po_vendor_id,
710 x_po_number,
711 x_invoice_date,
712 x_invoice_created_by,
713 x_invoice_updated_by,
714 x_invoice_id,
715 x_payables_batch_name,
716 x_ap_dist_line_number,
717 x_invoice_distribution_id, -- R12 new
718 x_orig_asset_id
719 ,x_line_type
720 ,x_capital_event_id
721 ,x_retirement_cost_type
722 ,L_org_id
723 FROM
724 sys.dual;
725
726 IF l_mrc_flag = 'Y' THEN
727 create_alc_proj_asset_lines (project_asset_line_id,
728 x_project_asset_line_detail_id,
729 x_rev_proj_asset_line_id,
730 x_original_asset_cost,
731 x_current_asset_cost,
732 x_err_stage,
733 x_err_code);
734
735 END IF;
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 x_err_code := SQLCODE;
740 RAISE;
741 END create_project_asset_lines;
742
743 PROCEDURE reverse_asset_lines
744 (x_project_id IN NUMBER,
745 x_capital_event_id IN NUMBER,
746 x_err_stage IN OUT NOCOPY VARCHAR2,
747 x_err_code IN OUT NOCOPY NUMBER)
748 IS
749
750 -- procedure for getting the project_asset_line_detail_id
751 -- for all the project asset line which need to be reversed
752
753 CURSOR seldetailids IS
754 SELECT
755 pal.project_asset_line_detail_id
756 FROM
757 pa_project_asset_lines pal
758 WHERE
759 pal.project_id+0 = x_project_id
760 AND pal.capital_event_id = NVL(x_capital_event_id, pal.capital_event_id)
761 AND pal.project_asset_id IN
762 ( SELECT
763 project_asset_id
764 FROM
765 pa_project_assets pas
766 WHERE
767 pas.reverse_flag = 'S'
768 AND pas.project_id = pal.project_id
769 )
770 AND pal.transfer_status_code||'' = 'T'
771 AND pal.rev_proj_asset_line_id is NULL
772 AND NOT EXISTS
773 ( SELECT
774 'This Line was adjusted before'
775 FROM
776 pa_project_asset_lines ppal
777 WHERE
778 ppal.rev_proj_asset_line_id = pal.project_asset_line_id
779 )
780 GROUP by project_asset_line_detail_id;
781
782 -- Cursor for selecting all the project_asset_lines
783 -- which are candidates for reversal for the given
784 -- project_asset_line_detail_id for all the assets
785
786 CURSOR selprojassetlines(proj_asset_line_detail_id NUMBER) IS
787 SELECT
788 project_asset_line_id,
789 description,
790 project_asset_id,
791 project_id,
792 task_id,
793 cip_ccid,
794 asset_cost_ccid,
795 original_asset_cost,
796 current_asset_cost,
797 project_asset_line_detail_id,
798 gl_date,
799 transfer_status_code,
800 amortize_flag,
801 asset_category_id,
802 request_id,
803 rev_from_proj_asset_line_id,
804 invoice_number,
805 vendor_number,
806 po_vendor_id,
807 po_number,
808 invoice_date,
809 invoice_created_by,
810 invoice_updated_by,
811 invoice_id,
812 payables_batch_name,
813 ap_distribution_line_number,
814 invoice_distribution_id, -- R12 new
815 original_asset_id
816 ,line_type
817 ,capital_event_id
818 ,retirement_cost_type
819 FROM
820 pa_project_asset_lines pal
821 WHERE
822 pal.project_id+0 = x_project_id
823 AND pal.rev_proj_asset_line_id is NULL /* Added this for the bug 3989536 */
824 AND pal.capital_event_id = NVL(x_capital_event_id, pal.capital_event_id)
825 AND pal.project_asset_line_detail_id = proj_asset_line_detail_id
826 AND pal.transfer_status_code||'' = 'T'
827 AND pal.project_asset_id IN
828 ( SELECT
829 project_asset_id
830 FROM
831 pa_project_assets pas
832 WHERE
833 pas.reverse_flag = 'S'
834 AND pas.project_id = pal.project_id
835 )
836 AND NOT EXISTS
837 ( SELECT
838 'This Line was adjusted before'
839 FROM
840 pa_project_asset_lines ppal
841 WHERE
842 ppal.rev_proj_asset_line_id = pal.project_asset_line_id
843 );
844
845 assetlinerec selprojassetlines%ROWTYPE;
846 detailidrec seldetailids%ROWTYPE;
847 asset_found BOOLEAN;
848 project_asset_line_id NUMBER;
849 cdl_fully_reversible BOOLEAN; -- This flag will indicate if the
850 -- project asset line is fully reversed
851 x_translated_reversal VARCHAR2(80);
852 -- Fix for bug : 4878878
853 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
854 BEGIN
855
856 x_err_code := 0;
857 x_err_stage := 'Reversing the Asset Lines';
858
859 find_assets_to_be_reversed
860 ( x_project_id,
861 asset_found,
862 x_capital_event_id,
863 x_err_stage,
864 x_err_code);
865
866 IF ( NOT asset_found ) THEN
867 IF G_debug_mode = 'Y' THEN
868 pa_debug.debug('reverse_asset_lines: ' || '. No assets to be reversed');
869 END IF;
870 RETURN;
871 END IF;
872
873 BEGIN
874 SELECT meaning
875 INTO x_translated_reversal
876 FROM pa_lookups
877 WHERE lookup_type = 'TRANSLATION' and
878 lookup_code = 'REVERSAL';
879 EXCEPTION
880 WHEN NO_DATA_FOUND THEN
881 x_translated_reversal := 'REVERSAL:';
882 WHEN OTHERS THEN
883 x_err_code := SQLCODE;
884 RAISE;
885 END;
886
887
888 FOR detailidrec IN seldetailids LOOP
889
890 cdl_fully_reversible := TRUE;
891
892 IF G_debug_mode = 'Y' THEN
893 pa_debug.debug('reverse_asset_lines: ' || '. Detail Line Id=' ||
894 to_char(detailidrec.project_asset_line_detail_id));
895 END IF;
896 -- Check if the project_asset_id on the line is same as one
897 -- of the asset being reversed in this batch
898
899 check_asset_to_be_reversed
900 (detailidrec.project_asset_line_detail_id,
901 asset_found,
902 x_err_stage,
903 x_err_code);
904
905 IF ( asset_found ) THEN
906 -- one of the project asset line has been assigned to an
907 -- asset not being reversed in the current process
908
909 IF G_debug_mode = 'Y' THEN
910 pa_debug.debug('reverse_asset_lines: ' || '. This line is not fully reversible');
911 END IF;
912 cdl_fully_reversible := FALSE;
913
914 END IF;
915
916 FOR assetlinerec IN
917 selprojassetlines(detailidrec.project_asset_line_detail_id) LOOP
918
919 IF G_debug_mode = 'Y' THEN
920 pa_debug.debug('reverse_asset_lines: ' || '. Line ID=' ||
921 to_char(assetlinerec.project_asset_line_id));
922 END IF;
923
924
925 -- create a project_asset_line which will reverse the current
926 -- line with a -ve amount and transfer_status_code for pending
927 -- also populate the rev_project_asset_line_id as the
928 -- project_asset_line_id
929
930 -- also track the amount being currently reversed. Basically
931 -- we are creating a -ve grouped cost for this asset
932
933 create_project_asset_lines
934 (x_translated_reversal||' '||assetlinerec.description,
935 assetlinerec.project_asset_id,
936 assetlinerec.project_id,
937 assetlinerec.task_id,
938 assetlinerec.cip_ccid,
939 assetlinerec.asset_cost_ccid,
940 assetlinerec.original_asset_cost,
941 -assetlinerec.current_asset_cost,
942 assetlinerec.project_asset_line_detail_id,
943 assetlinerec.gl_date,
944 'P', --- Transfer_status_code
945 NULL, --- transfer rejection reason
946 NULL, --- amortize_flag,
947 assetlinerec.asset_category_id,
948 assetlinerec.project_asset_line_id,
949 assetlinerec.rev_from_proj_asset_line_id,
950 assetlinerec.invoice_number,
951 assetlinerec.vendor_number,
952 assetlinerec.po_vendor_id,
953 assetlinerec.po_number,
954 assetlinerec.invoice_date,
955 assetlinerec.invoice_created_by,
956 assetlinerec.invoice_updated_by,
957 assetlinerec.invoice_id,
958 assetlinerec.payables_batch_name,
959 assetlinerec.ap_distribution_line_number,
960 assetlinerec.invoice_distribution_id, --R12 new
961 assetlinerec.original_asset_id,
962 assetlinerec.line_type,
963 assetlinerec.capital_event_id,
964 assetlinerec.retirement_cost_type,
965 x_err_stage,
966 x_err_code);
967
968 IF ( cdl_fully_reversible ) THEN
969
970 -- Now maintain the asset grouped_cost figure
971 -- when we create an adjustment we will subtract the
972 -- adjustment amount from the grouped_cip_cost column
973
974 update_asset_cost
975 (assetlinerec.project_asset_id,
976 -assetlinerec.current_asset_cost,
977 0, --- capitalized_cost
978 x_err_stage,
979 x_err_code);
980
981 ELSE
982 -- create new project asset line
983 create_project_asset_lines
984 (assetlinerec.description,
985 0, --New lines are created as UNASSIGNED, rather than assigned to the Reversed asset
986 assetlinerec.project_id,
987 assetlinerec.task_id,
988 assetlinerec.cip_ccid,
989 assetlinerec.asset_cost_ccid,
990 assetlinerec.original_asset_cost,
991 assetlinerec.current_asset_cost,
992 assetlinerec.project_asset_line_detail_id,
993 assetlinerec.gl_date,
994 'P', --- Transfer_status_code
995 NULL, --- transfer rejection reason
996 NULL, --- amortize_flag,
997 assetlinerec.asset_category_id,
998 NULL, --- rev_proj_asset_line_id
999 assetlinerec.project_asset_line_id,
1000 assetlinerec.invoice_number,
1001 assetlinerec.vendor_number,
1002 assetlinerec.po_vendor_id,
1003 assetlinerec.po_number,
1004 assetlinerec.invoice_date,
1005 assetlinerec.invoice_created_by,
1006 assetlinerec.invoice_updated_by,
1007 assetlinerec.invoice_id,
1008 assetlinerec.payables_batch_name,
1009 assetlinerec.ap_distribution_line_number,
1010 assetlinerec.invoice_distribution_id, --R12 new
1011 assetlinerec.original_asset_id,
1012 assetlinerec.line_type,
1013 assetlinerec.capital_event_id,
1014 assetlinerec.retirement_cost_type,
1015 x_err_stage,
1016 x_err_code);
1017
1018 END IF;
1019
1020 END LOOP; --- FOR assetlinerec IN selprojassetlines .....
1021
1022 -- If the cdl_fully_reversible is true then mark all the
1023 -- pa_project_asset_line_details as reversed
1024 -- and update the cip_cost to Zero
1025
1026 IF ( cdl_fully_reversible ) THEN
1027
1028 update_line_details
1029 (detailidrec.project_asset_line_detail_id,
1030 x_err_stage,
1031 x_err_code);
1032 -- Mark the expenditure item revenue distributed flag
1033 update_expenditure_items
1034 (detailidrec.project_asset_line_detail_id,
1035 'N',
1036 x_err_stage,
1037 x_err_code);
1038
1039 END IF;
1040
1041 END LOOP; --- FOR detailidrec IN seldetailids LOOP
1042
1043 -- At this time Project_asset_lines are reversed
1044 -- we need to interface the adjustment lines to FA now
1045
1046 UPDATE
1047 pa_project_assets
1048 SET
1049 reverse_flag = 'N',
1050 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
1051 --Commenting out this line since it is counter to business flow and erases key data (DPIS)
1052 --The CAPITAL_HOLD_FLAG will now be used to prevent new asset line generation
1053 --date_placed_in_service = NULL,
1054 /* End of Automatic asset capitalization changes */
1055 reversal_date = sysdate,
1056 last_update_date = sysdate,
1057 last_updated_by = x_last_updated_by,
1058 last_update_login = x_last_update_login,
1059 request_id = x_request_id,
1060 program_application_id = x_program_application_id,
1061 program_id = x_program_id,
1062 program_update_date = sysdate
1063 WHERE
1064 reverse_flag = 'S';
1065
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 x_err_code := SQLCODE;
1069 RAISE;
1070 END reverse_asset_lines;
1071
1072 PROCEDURE get_proj_asset_id
1073 (x_project_id IN NUMBER,
1074 x_task_id IN NUMBER,
1075 x_project_asset_id IN OUT NOCOPY NUMBER,
1076 x_err_stage IN OUT NOCOPY VARCHAR2,
1077 x_err_code IN OUT NOCOPY NUMBER)
1078 IS
1079 BEGIN
1080 x_err_code := 0;
1081 x_err_stage := 'Getting the Asset Id';
1082 x_project_asset_id := 0;
1083
1084 BEGIN
1085
1086 -- First get if there is any asset for project
1087
1088 SELECT
1089 paa.project_asset_id
1090 INTO
1091 x_project_asset_id
1092 FROM
1093 pa_project_asset_assignments paa
1094 WHERE
1095 paa.project_id = x_project_id
1096 AND paa.task_id = 0;
1097
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND THEN
1100 NULL;
1101 WHEN OTHERS THEN
1102 x_err_code := SQLCODE;
1103 RAISE;
1104 END;
1105
1106 --- Now get the top task level asset
1107
1108 SELECT
1109 paa.project_asset_id
1110 INTO
1111 x_project_asset_id
1112 FROM
1113 pa_project_asset_assignments paa
1114 WHERE
1115 paa.project_id = x_project_id
1116 AND paa.task_id =
1117 (SELECT
1118 task_id
1119 FROM
1120 pa_tasks
1121 WHERE
1122 parent_task_id is null --- top task
1123 CONNECT BY task_id = PRIOR parent_task_id
1124 START WITH task_id = x_task_id
1125 );
1126
1127 EXCEPTION
1128 WHEN NO_DATA_FOUND THEN
1129 NULL;
1130 WHEN OTHERS THEN
1131 x_err_code := SQLCODE;
1132 RAISE;
1133 END get_proj_asset_id;
1134
1135 --
1136
1137 PROCEDURE delete_proj_asset_line
1138 (x_project_asset_line_id IN NUMBER,
1139 x_err_stage IN OUT NOCOPY VARCHAR2,
1140 x_err_code IN OUT NOCOPY NUMBER)
1141 IS
1142 BEGIN
1143 x_err_code := 0;
1144 x_err_stage := 'Deleting the project asset line';
1145
1146 DELETE
1147 pa_project_asset_lines
1148 WHERE
1149 project_asset_line_id = x_project_asset_line_id;
1150
1151 IF l_mrc_flag = 'Y' THEN
1152 x_err_stage := 'Deleting the ALC project asset line';
1153
1154 DELETE PA_MC_PRJ_AST_LINES_ALL
1155 WHERE project_asset_line_id = x_project_asset_line_id;
1156 END IF;
1157
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 x_err_code := SQLCODE;
1161 RAISE;
1162 END delete_proj_asset_line;
1163
1164 PROCEDURE delete_proj_asset_line_details
1165 (x_project_asset_line_detail_id IN NUMBER,
1166 x_err_stage IN OUT NOCOPY VARCHAR2,
1167 x_err_code IN OUT NOCOPY NUMBER)
1168 IS
1169 BEGIN
1170 x_err_code := 0;
1171 x_err_stage := 'Deleting the project asset line details';
1172
1173 DELETE
1174 pa_project_asset_line_details
1175 WHERE
1176 project_asset_line_detail_id = x_project_asset_line_detail_id;
1177
1178 IF l_mrc_flag = 'Y' THEN
1179 x_err_stage := 'Deleting the ALC project asset line details';
1180
1181 DELETE PA_MC_PRJ_AST_LINE_DTLS
1182 WHERE PROJ_ASSET_LINE_DTL_UNIQ_ID = x_project_asset_line_detail_id;
1183 END IF;
1184
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187 x_err_code := SQLCODE;
1188 RAISE;
1189 END delete_proj_asset_line_details;
1190
1191 PROCEDURE delete_asset_lines
1192 (x_project_id IN NUMBER,
1193 x_capital_event_id IN NUMBER,
1194 x_err_stage IN OUT NOCOPY VARCHAR2,
1195 x_err_code IN OUT NOCOPY NUMBER)
1196 IS
1197
1198 -- This cursor will return all the project asset line which are eligible
1199 -- for deletion
1200
1201 CURSOR selassetlines IS
1202 SELECT
1203 ppal.project_asset_line_detail_id,
1204 ppal.project_asset_line_id,
1205 ppal.project_asset_id,
1206 ppal.current_asset_cost
1207 FROM
1208 pa_project_asset_lines ppal
1209 WHERE
1210 ppal.rev_proj_asset_line_id IS NULL
1211 AND ppal.transfer_status_code <> 'T'
1212 AND ppal.capital_event_id = NVL(x_capital_event_id, ppal.capital_event_id)
1213 AND ppal.project_asset_line_detail_id IN
1214 (SELECT
1215 /*+ INDEX (pal PA_PROJECT_ASSET_LINES_N2) */pal.project_asset_line_detail_id
1216 FROM
1217 pa_project_asset_lines pal
1218 WHERE
1219 pal.project_id = x_project_id
1220 AND pal.rev_proj_asset_line_id IS NULL -- This line is not an adjustment
1221 AND pal.transfer_status_code <> 'T'
1222 GROUP BY pal.project_asset_line_detail_id
1223 HAVING SUM(current_asset_cost) =
1224 ( SELECT
1225 SUM(cip_cost)
1226 FROM pa_project_asset_line_details pald
1227 WHERE pald.project_asset_line_detail_id = pal.project_asset_line_detail_id
1228 )
1229 )
1230 ORDER BY ppal.project_asset_line_detail_id;
1231
1232 assetlinerec selassetlines%ROWTYPE;
1233 curr_asset_line_detail_id NUMBER;
1234 update_detail_lines BOOLEAN;
1235 -- Fix for bug : 4878878
1236 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1237
1238 BEGIN
1239
1240 x_err_code := 0;
1241 x_err_stage := 'Deleting the Asset Lines';
1242
1243 curr_asset_line_detail_id := 0;
1244
1245 FOR assetlinerec IN selassetlines LOOP
1246
1247 IF (curr_asset_line_detail_id <>
1248 assetlinerec.project_asset_line_detail_id ) THEN
1249
1250 check_proj_asset_lines
1251 (assetlinerec.project_asset_line_detail_id,
1252 update_detail_lines,
1253 x_err_stage,
1254 x_err_code);
1255
1256 -- Mark the expenditure item revenue distributed flag
1257 update_expenditure_items
1258 (assetlinerec.project_asset_line_detail_id,
1259 'N',
1260 x_err_stage,
1261 x_err_code);
1262
1263 IF (update_detail_lines) THEN
1264 -- update the reversed_flag = 'Y'
1265 update_line_details
1266 (assetlinerec.project_asset_line_detail_id,
1267 x_err_stage,
1268 x_err_code);
1269 ELSE
1270
1271 IF G_debug_mode = 'Y' THEN
1272 pa_debug.debug('delete_asset_lines: ' || '. Deleting detail for detail line id = ' ||
1273 to_char(assetlinerec.project_asset_line_detail_id));
1274 END IF;
1275
1276 -- delete all the details
1277 delete_proj_asset_line_details
1278 (assetlinerec.project_asset_line_detail_id,
1279 x_err_stage,
1280 x_err_code);
1281
1282 END IF;
1283
1284 curr_asset_line_detail_id := assetlinerec.project_asset_line_detail_id;
1285 END IF;
1286
1287 -- now delete the projec asset line
1288
1289 delete_proj_asset_line
1290 (assetlinerec.project_asset_line_id,
1291 x_err_stage,
1292 x_err_code);
1293
1294 update_asset_cost
1295 (assetlinerec.project_asset_id,
1296 -assetlinerec.current_asset_cost,
1297 0, --- capitalized_cost
1298 x_err_stage,
1299 x_err_code);
1300 END LOOP;
1301
1302 EXCEPTION
1303 WHEN OTHERS THEN
1304 x_err_code := SQLCODE;
1305 RAISE;
1306 END delete_asset_lines;
1307
1308 -- procedure to create project_asset_lines_details
1309
1310 PROCEDURE create_proj_asset_line_details
1311 (x_expenditure_item_id IN NUMBER,
1312 x_line_num IN NUMBER,
1313 x_project_asset_line_detail_id IN NUMBER,
1314 x_cip_cost IN NUMBER,
1315 x_reversed_flag IN VARCHAR2,
1316 x_err_stage IN OUT NOCOPY VARCHAR2,
1317 x_err_code IN OUT NOCOPY NUMBER)
1318 IS
1319 proj_asset_line_detail_id NUMBER;
1320 l_proj_asset_line_dtl_uniq_id NUMBER := 0;
1321
1322 BEGIN
1323
1324 x_err_code := 0;
1325 x_err_stage := 'Create Project Asset Line details';
1326
1327 SELECT pa_proj_asset_line_dtls_uniq_s.nextval
1328 INTO l_proj_asset_line_dtl_uniq_id
1329 FROM sys.dual;
1330
1331 INSERT INTO pa_project_asset_line_details(
1332 proj_asset_line_dtl_uniq_id,
1333 expenditure_item_id,
1334 line_num,
1335 project_asset_line_detail_id,
1336 cip_cost,
1337 reversed_flag,
1338 last_update_date,
1339 last_updated_by,
1340 created_by,
1341 creation_date,
1342 last_update_login,
1343 request_id,
1344 program_application_id,
1345 program_id
1346 )
1347 SELECT
1348 l_proj_asset_line_dtl_uniq_id,
1349 x_expenditure_item_id,
1350 x_line_num,
1351 x_project_asset_line_detail_id,
1352 x_cip_cost,
1353 x_reversed_flag,
1354 sysdate,
1355 x_last_updated_by,
1356 x_created_by,
1357 sysdate,
1358 x_last_update_login,
1359 x_request_id,
1360 x_program_application_id,
1361 x_program_id
1362 FROM
1363 sys.dual;
1364
1365 IF l_mrc_flag = 'Y' THEN
1366 create_alc_asset_line_details (l_proj_asset_line_dtl_uniq_id,
1367 x_expenditure_item_id,
1368 x_line_num,
1369 x_project_asset_line_detail_id,
1370 x_err_stage,
1371 x_err_code);
1372 END IF;
1373
1374 EXCEPTION
1375 WHEN OTHERS THEN
1376 x_err_code := SQLCODE;
1377 RAISE;
1378 END create_proj_asset_line_details;
1379
1380 -- Procedure to generate summary asset lines
1381 Procedure fetch_vi_info ( x_invoice_id IN Number,
1382 x_ap_inv_dist_id IN Number,
1383 x_employee_id OUT NOCOPY Number,
1384 x_invoice_num OUT NOCOPY VARCHAR2,
1385 x_vendor_number OUT NOCOPY VARCHAR2,
1386 x_po_vendor_id OUT NOCOPY NUMBER,
1387 x_po_number OUT NOCOPY VARCHAR2,
1388 x_invoice_date OUT NOCOPY DATE,
1389 x_invoice_created_by OUT NOCOPY NUMBER,
1390 x_invoice_updated_by OUT NOCOPY NUMBER,
1391 x_payables_batch_name OUT NOCOPY VARCHAR2,
1392 x_err_stage IN OUT NOCOPY VARCHAR2,
1393 x_err_code IN OUT NOCOPY NUMBER) IS
1394 ------bring VI info
1395 begin
1396
1397 x_err_code := 0;
1398 x_err_stage := 'Fetching Vendor Invoice related information';
1399
1400 select rtrim(API.invoice_num),rtrim(POV.segment1),
1401 POV.employee_id,
1402 API.vendor_id,rtrim(upper(POH.segment1)),
1403 API.invoice_date,API.created_by,
1404 API.last_updated_by,
1405 APB.batch_name
1406 into x_invoice_num, x_vendor_number,
1407 x_employee_id,
1408 x_po_vendor_id, x_po_number,
1409 x_invoice_date, x_invoice_created_by,
1410 x_invoice_updated_by,
1411 x_payables_batch_name
1412 from ap_invoices API, ap_invoice_distributions APID, ap_batches APB,
1413 po_vendors POV, po_headers POH ,po_distributions POD
1414 where API.invoice_id = x_invoice_id
1415 and APID.invoice_distribution_id = x_ap_inv_dist_id
1416 and APID.invoice_id = API.invoice_id
1417 and APID.po_distribution_id = POD.po_distribution_id(+)
1418 and POD.po_header_id = POH.po_header_id(+)
1419 and POV.vendor_id = API.vendor_id
1420 and API.batch_id = APB.batch_id(+);
1421 exception when others then
1422 x_err_code := SQLCODE;
1423 end fetch_vi_info;
1424
1425 PROCEDURE generate_proj_asset_lines
1426 ( x_project_id IN NUMBER,
1427 x_in_service_date_through IN DATE,
1428 x_common_tasks_flag IN VARCHAR2,
1429 x_pa_date IN DATE,
1430 x_capital_cost_type_code IN VARCHAR2 ,
1431 x_cip_grouping_method_code IN VARCHAR2 ,
1432 x_OVERRIDE_ASSET_ASSIGNMENT IN VARCHAR2,
1433 x_VENDOR_INVOICE_GROUPING_CODE IN VARCHAR2,
1434 x_capital_event_id IN NUMBER,
1435 x_line_type IN VARCHAR2,
1436 x_ledger_id IN NUMBER,
1437 x_err_stage IN OUT NOCOPY VARCHAR2,
1438 x_err_code IN OUT NOCOPY NUMBER)
1439
1440 IS
1441
1442 /* For manually entered burdened transactions, amount id 0 for CDL 'R'
1443 line, hence ignore those lines */
1444
1445 CURSOR selcdls (p_project_id NUMBER,
1446 p_capital_cost_type_code VARCHAR2,
1447 p_cip_grouping_method_code VARCHAR2,
1448 p_override_asset_assignment VARCHAR2,
1449 p_vendor_invoice_grouping_code VARCHAR2,
1450 p_pa_through_date DATE,
1451 p_common_tasks_flag VARCHAR2,
1452 p_in_service_date_through DATE,
1453 p_ledger_id NUMBER,
1454 p_amount_type varchar2 ) IS
1455 SELECT /*+ INDEX(pcdl PA_COST_DISTRIBUTION_LINES_U1) */ /* bug 5194567 added hint */
1456 pa_faxface.get_group_level_task_id(pei.task_id,pt.top_task_id,
1457 p_project_id) group_level_task_id,
1458 pa_faxface.get_asset_category_id(pei.document_header_id, -- R12 new
1459 pei.document_line_number, -- R12 new
1460 pei.document_distribution_id, -- R12 new
1461 pei.transaction_source) asset_category_id,
1462 pei.system_linkage_function,
1463 pei.document_header_id, -- R12 new
1464 pei.document_line_number, -- R12 new
1465 pei.document_distribution_id, -- R12 new
1466 pet.expenditure_category,
1467 pei.expenditure_type,
1468 pei.non_labor_resource,
1469 PA_CLIENT_EXTN_CIP_ACCT_OVR.CIP_ACCT_OVERRIDE
1470 (decode(pcdl.acct_source_code,
1471 'UPG', pcdl.dr_code_combination_id,
1472 'EXT', pcdl.dr_code_combination_id,
1473 'NA', pcdl.dr_code_combination_id,
1474 NULL, decode(nvl(pei.historical_flag, 'Y'),
1475 'Y', decode(pcdl.line_type,
1476 'R', pcdl.dr_code_combination_id,
1477 'D', pcdl.dr_code_combination_id,
1478 'I', pa_xla_interface_pkg.get_post_acc_sla_ccid
1479 (
1480 pcdl.acct_event_id,
1481 pcdl.transfer_status_code,
1482 pei.transaction_source,
1483 nvl(pei.historical_flag, 'Y'),
1484 pcdl.expenditure_item_id,
1485 pcdl.parent_line_num,
1486 pcdl.line_type,
1487 pcdl.dr_code_combination_id,
1488 'DEBIT',
1489 p_ledger_id
1490 )
1491 ),
1492 'N', decode(pcdl.line_type,
1493 'R', decode(pa_xla_interface_pkg.get_source(pei.transaction_source,
1494 pei.document_payment_id),
1495 'EXT', pcdl.dr_code_combination_id,
1496 pa_xla_interface_pkg.get_post_acc_sla_ccid
1497 (
1498 pcdl.acct_event_id,
1499 pcdl.transfer_status_code,
1500 pei.transaction_source,
1501 nvl(pei.historical_flag, 'Y'),
1502 decode(pa_xla_interface_pkg.get_source(pei.Transaction_Source,
1503 pei.document_payment_id),
1504 'PA', pcdl.expenditure_item_id,
1505 'AP_PAY', pcdl.system_reference5,
1506 'AP_INV', pei.document_distribution_id,
1507 'RCV', pcdl.system_reference5,
1508 'INV', pcdl.system_reference5,
1509 'WIP', pcdl.system_reference5), -- distribution_id1
1510 decode(pa_xla_interface_pkg.get_source(pei.Transaction_Source,
1511 pei.document_payment_id),
1512 'PA', pcdl.line_num,
1513 'AP_PAY', pei.document_distribution_id,
1514 'AP_INV', NULL,
1515 'RCV', NULL,
1516 'INV', NULL,
1517 'WIP', NULL), -- distribution_id2
1518 pcdl.line_type,
1519 pcdl.dr_code_combination_id,
1520 'DEBIT',
1521 p_ledger_id
1522 )
1523 ),
1524 pa_xla_interface_pkg.get_post_acc_sla_ccid
1525 (
1526 pcdl.acct_event_id,
1527 pcdl.transfer_status_code,
1528 pei.transaction_source,
1529 nvl(pei.historical_flag, 'Y'),
1530 pcdl.expenditure_item_id,
1531 decode(pcdl.line_type,
1532 'D', pcdl.line_num,
1533 'I', pcdl.parent_line_num),
1534 pcdl.line_type,
1535 pcdl.dr_code_combination_id,
1536 'DEBIT',
1537 p_ledger_id
1538 ) -- for I and D.
1539 )
1540 ), -- acct_source_code NULL
1541 pa_xla_interface_pkg.get_post_acc_sla_ccid /* for all other acct source codes */
1542 (
1543 pcdl.acct_event_id,
1544 pcdl.transfer_status_code,
1545 pei.transaction_source,
1546 nvl(pei.historical_flag, 'Y'),
1547 pcdl.expenditure_item_id,
1548 pcdl.line_num,
1549 pcdl.line_type,
1550 pcdl.dr_code_combination_id,
1551 'DEBIT',
1552 p_ledger_id
1553 )
1554 ),
1555 pcdl.expenditure_item_id,
1556 pcdl.line_num
1557 ) cip_ccid,
1558 decode(p_amount_type,
1559 'R', pcdl.amount,
1560 decode(pcdl.line_type,
1561 'D', pcdl.amount,
1562 pcdl.burdened_cost)) cip_cost,
1563 pcdl.expenditure_item_id,
1564 pcdl.line_num,
1565 NVL(p_cip_grouping_method_code,'ALL') cip_grouping_method_code,
1566 DECODE(x_line_type,
1567 'R', PA_CLIENT_EXTN_RET_COST_TYPE.RETIREMENT_COST_TYPE
1568 (pcdl.expenditure_item_id,
1569 pcdl.line_num,
1570 pei.expenditure_type),
1571 DECODE(DECODE(p_vendor_invoice_grouping_code,'G','G','E')||pei.system_linkage_function,
1572 'EVI', DECODE(p_cip_grouping_method_code,
1573 'EC', pet.expenditure_category,
1574 'ECNLR',pet.expenditure_category||'+'||pei.non_labor_resource,
1575 'ET',pei.expenditure_type,
1576 'ETNLR',pei.expenditure_type||'+'||pei.non_labor_resource,
1577 'CIPGCE',PA_CLIENT_EXTEN_CIP_GROUPING.CLIENT_GROUPING_METHOD(
1578 p_project_id,
1579 pei.task_id ,
1580 pei.expenditure_item_id ,
1581 pei.expenditure_id ,
1582 pei.expenditure_type ,
1583 pet.expenditure_category ,
1584 pei.attribute1 ,
1585 pei.attribute2 ,
1586 pei.attribute3 ,
1587 pei.attribute4 ,
1588 pei.attribute5 ,
1589 pei.attribute6 ,
1590 pei.attribute7 ,
1591 pei.attribute8 ,
1592 pei.attribute9 ,
1593 pei.attribute10 ,
1594 pei.attribute_category ,
1595 pei.transaction_source,
1596 pcdl.system_reference2, /*bug 5523708- passing ref2,3,4*/
1597 pcdl.system_reference3,
1598 pcdl.system_reference4),
1599 'ALL')||'+'||pei.document_header_id||'+'||pei.document_distribution_id,
1600 DECODE(p_cip_grouping_method_code,
1601 'EC',pet.expenditure_category,
1602 'ECNLR',pet.expenditure_category||'+'||pei.non_labor_resource,
1603 'ET',pei.expenditure_type,
1604 'ETNLR',pei.expenditure_type||'+'||pei.non_labor_resource,
1605 'CIPGCE',PA_CLIENT_EXTEN_CIP_GROUPING.CLIENT_GROUPING_METHOD(
1606 p_project_id,
1607 pei.task_id ,
1608 pei.expenditure_item_id ,
1609 pei.expenditure_id ,
1610 pei.expenditure_type ,
1611 pet.expenditure_category ,
1612 pei.attribute1 ,
1613 pei.attribute2 ,
1614 pei.attribute3 ,
1615 pei.attribute4 ,
1616 pei.attribute5 ,
1617 pei.attribute6 ,
1618 pei.attribute7 ,
1619 pei.attribute8 ,
1620 pei.attribute9 ,
1621 pei.attribute10 ,
1622 pei.attribute_category ,
1623 pei.transaction_source,
1624 pcdl.system_reference2, /*bug 5454123- passing ref2,3,4*/
1625 pcdl.system_reference3,
1626 pcdl.system_reference4),
1627 'ALL' ))) GROUPING_METHOD,
1628 p_override_asset_assignment override_asset_assignment_flag,
1629 p_vendor_invoice_grouping_code vendor_invoice_grouping_code,
1630 p_project_id project_id,
1631 pei.task_id,
1632 pei.expenditure_id,
1633 pei.organization_id,
1634 pei.attribute1,
1635 pei.attribute2,
1636 pei.attribute3,
1637 pei.attribute4,
1638 pei.attribute5,
1639 pei.attribute6,
1640 pei.attribute7,
1641 pei.attribute8,
1642 pei.attribute9,
1643 pei.attribute10,
1644 pei.attribute_category,
1645 pei.capital_event_id,
1646 -- Added for bug : 4768332
1647 pcdl.acct_event_id,
1648 NVL(pei.transaction_source,'Projects') transaction_source
1649 --pa_xla_interface_pkg.get_source(pei.transaction_source, pei.document_payment_id) transaction_source
1650
1651 FROM
1652 pa_cost_distribution_lines_all pcdl,
1653 pa_expenditure_items_all pei,
1654 pa_expenditure_types pet,
1655 pa_tasks pt
1656 WHERE
1657 pcdl.expenditure_item_id = pei.expenditure_item_id
1658 AND pei.revenue_distributed_flag||'' = 'N'
1659 AND pei.cost_distributed_flag ='Y'
1660 AND pcdl.transfer_status_code in ('P','A','V','T','R','G','B') /*bug5672624*/ /*Modified for bug 7142829 */
1661 AND (pcdl.line_type = p_capital_cost_type_code
1662 OR pcdl.line_type = DECODE(p_capital_cost_type_code,
1663 'R', 'I',
1664 p_capital_cost_type_code))
1665 AND ((pcdl.line_type = 'I' and pcdl.transfer_status_code = 'G') OR
1666 pcdl.line_type in ('R', 'D') and pcdl.transfer_status_code in ('V', 'A'))
1667 AND ((pcdl.billable_flag = 'Y' and x_line_type = 'C') OR x_line_type = 'R') -- Bug 4860800
1668 --AND pei.billable_flag = DECODE(x_line_type, 'C', 'Y', 'N') -- Bug 4860800
1669 AND pei.capital_event_id IS NOT NULL
1670 AND pei.capital_event_id = NVL(x_capital_event_id, pei.capital_event_id)
1671 AND NVL(pt.retirement_cost_flag,'N') = DECODE(x_line_type,'R','Y','N')
1672 /* Added the below code as part of bug 7274814
1673 Fix is to prevent net-zeroing CDLs from being processed */
1674 AND NOT EXISTS
1675 /* Reversed CDL*/
1676 (SELECT NULL
1677 FROM PA_COST_DISTRIBUTION_LINES_ALL CDL1,
1678 PA_COST_DISTRIBUTION_LINES_ALL CDL2
1679 WHERE CDL1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
1680 AND CDL1.LINE_NUM = PCDL.LINE_NUM
1681 AND CDL1.REVERSED_FLAG = 'Y'
1682 AND CDL1.BILLABLE_FLAG = 'Y'
1683 AND CDL2.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
1684 AND CDL2.LINE_NUM_REVERSED = CDL1.LINE_NUM
1685 AND CDL2.BILLABLE_FLAG = 'Y'
1686 AND TRUNC(CDL1.PA_DATE) <= TRUNC(P_PA_THROUGH_DATE) /* Added for Bug 7445771 */
1687 UNION ALL
1688 /* Reversal CDL*/
1689 SELECT NULL
1690 FROM PA_COST_DISTRIBUTION_LINES_ALL CDL1,
1691 PA_COST_DISTRIBUTION_LINES_ALL CDL2
1692 WHERE CDL1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
1693 AND CDL1.REVERSED_FLAG = 'Y'
1694 AND CDL1.BILLABLE_FLAG = 'Y'
1695 AND CDL2.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
1696 AND CDL2.LINE_NUM_REVERSED = CDL1.LINE_NUM
1697 AND CDL2.LINE_NUM = PCDL.LINE_NUM
1698 AND CDL2.BILLABLE_FLAG = 'Y'
1699 AND TRUNC(CDL1.PA_DATE) <= TRUNC(P_PA_THROUGH_DATE) /* Added for Bug 7445771 */
1700 /* To check if the reversed CDL has been capitalized*/
1701 AND NOT EXISTS (SELECT NULL
1702 FROM PA_PROJECT_ASSET_LINE_DETAILS PALD1
1703 WHERE PALD1.EXPENDITURE_ITEM_ID = CDL1.EXPENDITURE_ITEM_ID
1704 AND PALD1.LINE_NUM = CDL1.LINE_NUM
1705 AND PALD1.REVERSED_FLAG ||'' = 'N')
1706 UNION ALL
1707 /* For ei adjustment e.g. transfer from cap to expense task
1708 SELECT NULL
1709 FROM PA_EXPENDITURE_ITEMS_ALL EI1
1710 WHERE EI1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
1711 AND EI1.NET_ZERO_ADJUSTMENT_FLAG = 'Y'
1712 /* checking if the adjusted ei has already been capitalized
1713 AND NOT EXISTS (SELECT NULL
1714 FROM PA_PROJECT_ASSET_LINE_DETAILS PALD2
1715 WHERE PALD2.EXPENDITURE_ITEM_ID =
1716 DECODE(EI1.ADJUSTED_EXPENDITURE_ITEM_ID,NULL,- 99,
1717 EI1.ADJUSTED_EXPENDITURE_ITEM_ID)
1718 AND PALD2.REVERSED_FLAG ||'' = 'N')
1719 Commented the above code and added below two selects for Bug 7445771 */
1720 SELECT NULL
1721 FROM PA_EXPENDITURE_ITEMS_ALL EI1
1722 WHERE EI1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
1723 AND EI1.NET_ZERO_ADJUSTMENT_FLAG = 'Y'
1724 AND EI1.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
1725 AND NOT EXISTS (SELECT NULL
1726 FROM PA_PROJECT_ASSET_LINE_DETAILS PALD2,
1727 PA_EXPENDITURE_ITEMS_ALL EI2
1728 WHERE EI2.ADJUSTED_EXPENDITURE_ITEM_ID = EI1.EXPENDITURE_ITEM_ID
1729 AND PALD2.EXPENDITURE_ITEM_ID = EI2.ADJUSTED_EXPENDITURE_ITEM_ID
1730 AND PALD2.REVERSED_FLAG||'' = 'N')
1731 AND EXISTS (SELECT 1
1732 FROM PA_EXPENDITURE_ITEMS_ALL EI3,
1733 PA_COST_DISTRIBUTION_LINES_ALL PCDL2
1734 WHERE EI3.ADJUSTED_EXPENDITURE_ITEM_ID = EI1.EXPENDITURE_ITEM_ID
1735 AND EI3.EXPENDITURE_ITEM_ID = PCDL2.EXPENDITURE_ITEM_ID
1736 AND TRUNC(PCDL2.PA_DATE) <= TRUNC(P_PA_THROUGH_DATE))
1737 UNION ALL
1738 SELECT NULL
1739 FROM PA_EXPENDITURE_ITEMS_ALL EI1
1740 WHERE EI1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
1741 AND EI1.NET_ZERO_ADJUSTMENT_FLAG = 'Y'
1742 AND EI1.ADJUSTED_EXPENDITURE_ITEM_ID IS NOT NULL
1743 AND NOT EXISTS (SELECT NULL
1744 FROM PA_PROJECT_ASSET_LINE_DETAILS PALD2
1745 WHERE PALD2.EXPENDITURE_ITEM_ID = EI1.ADJUSTED_EXPENDITURE_ITEM_ID
1746 AND PALD2.REVERSED_FLAG||'' = 'N')
1747 AND EXISTS (SELECT 1
1748 FROM PA_COST_DISTRIBUTION_LINES_ALL PCDL2
1749 WHERE PCDL2.EXPENDITURE_ITEM_ID = EI1.ADJUSTED_EXPENDITURE_ITEM_ID
1750 AND TRUNC(PCDL2.PA_DATE) <= TRUNC(P_PA_THROUGH_DATE))
1751 /* End of code changes for Bug 7445771 */
1752 )
1753 /* End of code changes done as part of bug 7274814 */
1754 AND TRUNC(pcdl.pa_date) <= TRUNC(p_pa_through_date)
1755 AND decode(p_amount_type,
1756 'R', pcdl.amount,
1757 decode(pcdl.line_type,
1758 'D', pcdl.amount,
1759 pcdl.burdened_cost)) <> 0
1760 AND pei.task_id = pt.task_id
1761 AND pei.expenditure_type = pet.expenditure_type
1762 AND pt.project_id =p_project_id
1763 /*start of change for bug 6037734*/
1764 AND (pa_nl_installed.is_nl_installed='N'
1765 OR (pa_nl_installed.is_nl_installed='Y'
1766 AND EXISTS(select NULL from pa_expenditure_items_all ei1
1767 where ei1.expenditure_item_id = pei.expenditure_item_id
1768 and (ei1.attribute6 is not null OR ei1.attribute7 is not null OR ei1.attribute8 is not null
1769 OR ei1.attribute9 is not null OR ei1.attribute10 is not null)
1770 )
1771 AND NOT EXISTS (select null from pa_expenditure_items_all ei2
1772 where ei2.expenditure_item_id = pei.expenditure_item_id
1773 and ei2.attribute8 is NULL
1774 and ei2.attribute9 is NULL
1775 )
1776 )
1777 OR (pa_nl_installed.is_nl_installed='Y'
1778 AND EXISTS(select null from pa_expenditure_items_all ei3
1779 where ei3.expenditure_item_id = pei.expenditure_item_id
1780 and ei3.attribute6 is NULL
1781 and ei3.attribute7 is NULL
1782 and ei3.attribute8 is NULL
1783 and ei3.attribute9 is NULL
1784 and ei3.attribute10 is NULL
1785 )
1786 )
1787 )
1788 /* end of change for bug 6037734*/
1789 AND EXISTS ( SELECT '1' -- Check for task_id to have asset assignment
1790 FROM pa_project_assets ppa,
1791 pa_project_asset_assignments paa
1792 WHERE paa.project_id = p_project_id
1793 AND paa.task_id = pt.task_id
1794 AND ppa.capital_event_id IS NOT NULL
1795 AND ppa.capital_event_id = NVL(x_capital_event_id, pei.capital_event_id)
1796 AND ppa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
1797 AND ppa.capital_hold_flag = 'N'
1798 AND ppa.project_asset_id = paa.project_asset_id
1799 AND TRUNC(ppa.date_placed_in_service) <= TRUNC(p_in_service_date_through)
1800 UNION ALL
1801 SELECT '1' -- Check for top_task_id to have asset assignment
1802 FROM pa_project_assets ppa,
1803 pa_project_asset_assignments paa
1804 WHERE paa.project_id = p_project_id
1805 AND paa.task_id = pt.top_task_id
1806 AND ppa.capital_event_id IS NOT NULL
1807 AND ppa.capital_event_id = NVL(x_capital_event_id, pei.capital_event_id)
1808 AND ppa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
1809 AND ppa.capital_hold_flag = 'N'
1810 AND ppa.project_asset_id = paa.project_asset_id
1811 AND TRUNC(ppa.date_placed_in_service) <= TRUNC(p_in_service_date_through)
1812 UNION ALL
1813 SELECT '1' -- Check for project level Asset Assignments
1814 FROM pa_project_assets ppa,
1815 pa_project_asset_assignments paa
1816 WHERE paa.project_id = p_project_id
1817 AND nvl(paa.task_id,0) = 0
1818 AND ppa.capital_event_id IS NOT NULL
1819 AND ppa.capital_event_id = NVL(x_capital_event_id, pei.capital_event_id)
1820 AND ppa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
1821 AND ppa.capital_hold_flag = 'N'
1822 AND ppa.project_asset_id = paa.project_asset_id
1823 AND TRUNC(ppa.date_placed_in_service) <= TRUNC(p_in_service_date_through)
1824 UNION ALL
1825 SELECT '1' -- Check task_id is a common cost task
1826 FROM pa_project_asset_assignments paa
1827 WHERE paa.project_id = p_project_id
1828 AND task_id = pt.task_id
1829 AND DECODE(paa.project_asset_id, 0,p_common_tasks_flag, 'N') = 'Y'
1830 UNION ALL
1831 SELECT '1' -- Check top_task_id is a common cost task
1832 FROM pa_project_asset_assignments paa
1833 WHERE paa.project_id = p_project_id
1834 AND task_id = pt.top_task_id
1835 AND DECODE(paa.project_asset_id, 0,p_common_tasks_flag, 'N') = 'Y'
1836 UNION ALL
1837 SELECT '1' -- Check project is a common cost project
1838 FROM pa_project_asset_assignments paa
1839 WHERE paa.project_id = p_project_id
1840 AND nvl(task_id,0) = 0
1841 AND DECODE(paa.project_asset_id, 0,p_common_tasks_flag, 'N') = 'Y'
1842 )
1843
1844 AND NOT EXISTS
1845 (
1846 SELECT
1847 'This CDL was summarized before'
1848 FROM
1849 pa_project_asset_line_details pald
1850 WHERE
1851 pald.expenditure_item_id = pcdl.expenditure_item_id
1852 AND pald.line_num = pcdl.line_num
1853 AND pald.reversed_flag||'' = 'N'
1854 )
1855 ORDER BY
1856 1, -- group level task Id
1857 2, -- asset category id
1858 cip_ccid,
1859 pei.capital_event_id,
1860 GROUPING_METHOD;
1861
1862 cursor c_proj_asset(p_asset_id in number) is
1863 select project_id, date_placed_in_service, capital_event_id,
1864 capital_hold_flag, project_asset_type
1865 from pa_project_assets
1866 where project_asset_id = nvl(p_asset_id,0);
1867
1868 l_amount_type varchar2(1) := Null;
1869 l_tbc_flag varchar2(1);
1870 l_capitalization varchar2(30);
1871 cdlrec selcdls%ROWTYPE;
1872 first_rec BOOLEAN;
1873 curr_asset_id NUMBER;
1874 prev_asset_id NUMBER;
1875 l_asset_id NUMBER;
1876 l_num_asset_assignment NUMBER;
1877 curr_ccid NUMBER;
1878 prev_ccid NUMBER;
1879 curr_nlr pa_expenditure_items_all.non_labor_resource%TYPE;
1880 prev_nlr pa_expenditure_items_all.non_labor_resource%TYPE;
1881 curr_expend_type VARCHAR2(100);
1882 prev_expend_type VARCHAR2(100);
1883 curr_expend_cat VARCHAR2(100);
1884 curr_grouping_method VARCHAR2(255);
1885 prev_expend_cat VARCHAR2(100);
1886 curr_grp_task_id NUMBER:=-99;
1887 prev_grp_task_id NUMBER:=-99;
1888 curr_asset_category_id NUMBER;
1889 prev_asset_category_id NUMBER;
1890 curr_asset_cost NUMBER;
1891 prev_asset_cost NUMBER;
1892 proj_asset_line_detail_id NUMBER;
1893 description pa_project_asset_lines.description%TYPE;
1894 depreciation_expense_ccid NUMBER;
1895 prev_sys_link_fun pa_expenditure_items_all.system_linkage_function%TYPE;
1896
1897 curr_employee_id po_vendors.employee_id%type;
1898 curr_invoice_num ap_invoices.invoice_num%TYPE;
1899 curr_vendor_number po_vendors.segment1%TYPE;
1900 curr_po_vendor_id ap_invoices.vendor_id%TYPE;
1901 curr_po_number po_headers.segment1%TYPE;
1902 curr_invoice_date ap_invoices.invoice_date%TYPE;
1903 curr_invoice_created_by ap_invoices.created_by%TYPE;
1904 curr_invoice_updated_by ap_invoices.last_updated_by%TYPE;
1905 curr_invoice_id ap_invoices.invoice_id%TYPE;
1906 curr_payables_batch_name ap_batches.batch_name%TYPE;
1907 curr_ap_dist_line_number ap_invoice_distributions.distribution_line_number%TYPE;
1908 curr_ap_inv_dist_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1909 curr_inv_line_number ap_invoice_distributions.invoice_line_number%TYPE;
1910 prev_employee_id po_vendors.employee_id%type;
1911 prev_invoice_num ap_invoices.invoice_num%TYPE;
1912 prev_vendor_number po_vendors.segment1%TYPE;
1913 prev_po_vendor_id ap_invoices.vendor_id%TYPE;
1914 prev_po_number po_headers.segment1%TYPE;
1915 prev_invoice_date ap_invoices.invoice_date%TYPE;
1916 prev_invoice_created_by ap_invoices.created_by%TYPE;
1917 prev_invoice_updated_by ap_invoices.last_updated_by%TYPE;
1918 prev_invoice_id ap_invoices.invoice_id%TYPE;
1919 prev_payables_batch_name ap_batches.batch_name%TYPE;
1920 prev_ap_dist_line_number ap_invoice_distributions.distribution_line_number%TYPE;
1921 prev_ap_inv_dist_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1922 prev_inv_line_number ap_invoice_distributions.invoice_line_number%TYPE;
1923
1924 curr_capital_event_id pa_capital_events.capital_event_id%TYPE;
1925 prev_capital_event_id pa_capital_events.capital_event_id%TYPE;
1926 v_retirement_cost_type pa_project_asset_lines_all.retirement_cost_type%TYPE := NULL;
1927 extn_capital_event_id pa_capital_events.capital_event_id%TYPE;
1928 extn_capital_hold_flag pa_project_assets_all.capital_hold_flag%TYPE;
1929 extn_project_asset_type pa_project_assets_all.project_asset_type%TYPE;
1930
1931 extn_project_id number(15);
1932 extn_date_in_service date;
1933 extn_error_code varchar2(1);
1934 asset_valid boolean;
1935 l_asset_v boolean;
1936 l_profile_no_dpis varchar2(1); /* Added for bug 6788325 */
1937 orig_asset_id number(15);
1938 client_asset_id number(15);
1939 v_crl_profile varchar2(1) := NVL(FND_PROFILE.value('PA_CRL_LICENSED'), 'N'); /*bug5523708*/
1940
1941 -- Fix for bug : 4878878
1942 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1943
1944 cdlrec t_rec;
1945
1946 BEGIN
1947 x_err_stage := 'Generating project asset lines';
1948 x_err_code := 0;
1949
1950 first_rec := TRUE;
1951 curr_asset_cost := 0;
1952 l_asset_v :=FALSE;
1953 l_profile_no_dpis := NVL(UPPER(SUBSTR(fnd_profile.value_specific('PA_ASSIGN_ASSET_LINE_WITH_NO_DPIS'), 1, 1)), 'Y');
1954 /* Added for bug 6788325 */
1955
1956 SELECT pt.capital_cost_type_code
1957 INTO l_amount_type
1958 FROM pa_projects ppr,
1959 pa_project_types pt
1960 WHERE ppr.project_type=pt.project_type
1961 AND project_id=x_project_id;
1962
1963 FOR cdlrec IN selcdls(x_project_id,
1964 x_capital_cost_type_code,
1965 x_cip_grouping_method_code,
1966 x_OVERRIDE_ASSET_ASSIGNMENT,
1967 x_VENDOR_INVOICE_GROUPING_CODE ,
1968 x_pa_date,
1969 x_common_tasks_flag,
1970 x_in_service_date_through,
1971 x_ledger_id,
1972 l_amount_type ) LOOP
1973
1974 IF G_debug_mode = 'Y' THEN
1975 pa_debug.debug('generate_proj_asset_lines: ' || '. Processing Expenditure Item Id= '||
1976 to_char(cdlrec.expenditure_item_id)||
1977 ' CDL num = '||to_char(cdlrec.line_num));
1978 END IF;
1979 orig_asset_id := l_asset_id;
1980
1981 -- R12 changes start..if ccid is null, reject the cdl
1982 if cdlrec.cip_ccid is null then
1983 goto SKIP_NO_ACCOUNTS;
1984 end if;
1985
1986 -- R12 changes end..get the account and call client extn to override
1987
1988 /* added condition prev_sys_link_fun = 'VI' to following if condition.
1989 This is done so that in case a VI is just processed and some asset
1990 is assigned to it then same asset doesn't get assigned to the
1991 subsequenct cdls. */
1992
1993 if (cdlrec.group_level_task_id <> curr_grp_task_id) or
1994 ( cdlrec.system_linkage_function ='VI') or
1995 (prev_sys_link_fun = 'VI') then
1996 get_asset_id
1997 ( x_project_id => x_project_id,
1998 x_system_linkage_function => cdlrec.system_linkage_function,
1999 x_grp_level_task_id => cdlrec.group_level_task_id,
2000 x_asset_category_id => cdlrec.asset_category_id,
2001 x_line_type => x_line_type,
2002 x_capital_event_id => cdlrec.capital_event_id,
2003 x_asset_id => l_asset_id ,
2004 x_num_asset_assigned => l_num_asset_assignment
2005 );
2006 commit ;
2007 end if;
2008 curr_asset_id := l_asset_id;
2009 client_asset_id := l_asset_id;
2010
2011 /*bug5523708 - an eIB ct should be able to use fetch_vi_info for obtaining PO/Supplier info for non-eIB trxns
2012 added from crl_profile*/
2013 if (cdlrec.vendor_invoice_grouping_code <> 'G' and cdlrec.system_linkage_function='VI')
2014 OR
2015 (v_crl_profile ='Y' AND
2016 cdlrec.vendor_invoice_grouping_code = 'G' AND
2017 cdlrec.system_linkage_function='VI' AND
2018 cdlrec.transaction_source in ('AP INVOICE', 'AP EXPENSE', 'AP NRTAX', 'AP DISCOUNTS', 'AP VARIANCE',
2019 'PO RECEIPT', 'PO RECEIPT NRTAX', 'PO RECEIPT NRTAX PRICE ADJ', 'PO RECEIPT PRICE ADJ',
2020 'CSE_IPV_ADJUSTMENT','CSE_PO_RECEIPT')
2021 ) THEN
2022 -- R12 changes..these variables are not used anywhere. do we need these at all ?
2023 -- curr_nlr := cdlrec.non_labor_resource||cdlrec.system_reference2||cdlrec.system_reference3;
2024 -- curr_expend_type := cdlrec.expenditure_type||cdlrec.system_reference2||cdlrec.system_reference3;
2025 -- curr_expend_cat := cdlrec.expenditure_category||cdlrec.system_reference2||cdlrec.system_reference3;
2026
2027 -- curr_ap_dist_line_number := cdlrec.system_reference3;
2028 curr_ap_inv_dist_id := cdlrec.document_distribution_id;
2029
2030 -- if (cdlrec.system_reference2 <> nvl(curr_invoice_id,-99)) then
2031 if ((cdlrec.document_header_id <> nvl(curr_invoice_id,-99)) and (cdlrec.vendor_invoice_grouping_code <> 'G')) then /*Added for bug:7139484*/
2032 --curr_invoice_id := cdlrec.system_reference2;
2033 curr_invoice_id := cdlrec.document_header_id;
2034 fetch_vi_info ( x_invoice_id => curr_invoice_id,
2035 --x_ap_dist_line_number => curr_ap_dist_line_number,
2036 x_ap_inv_dist_id => curr_ap_inv_dist_id,
2037 x_employee_id => curr_employee_id,
2038 x_invoice_num => curr_invoice_num,
2039 x_vendor_number => curr_vendor_number,
2040 x_po_vendor_id => curr_po_vendor_id,
2041 x_po_number => curr_po_number,
2042 x_invoice_date => curr_invoice_date,
2043 x_invoice_created_by => curr_invoice_created_by,
2044 x_invoice_updated_by => curr_invoice_updated_by,
2045 x_payables_batch_name => curr_payables_batch_name,
2046 x_err_stage => x_err_stage,
2047 x_err_code => x_err_code);
2048 end if;
2049 else
2050 curr_invoice_num := null;
2051 curr_vendor_number := null;
2052 curr_po_vendor_id:= null;
2053 curr_po_number := null;
2054 curr_invoice_date := null;
2055 curr_invoice_created_by := null;
2056 curr_invoice_updated_by := null;
2057 curr_invoice_id := null;
2058 curr_payables_batch_name := null;
2059 curr_ap_dist_line_number:=null;
2060 curr_employee_id := null;
2061 --curr_nlr := cdlrec.non_labor_resource;
2062 --curr_expend_type := cdlrec.expenditure_type;
2063 --curr_expend_cat := cdlrec.expenditure_category;
2064 end if;
2065
2066 -------------------------call client extension
2067 asset_valid := TRUE;
2068 if (curr_asset_id = 0 OR cdlrec.override_asset_assignment_flag = 'Y') then
2069 PA_CLIENT_EXTN_GEN_ASSET_LINES.CLIENT_ASSET_ASSIGNMENT(cdlrec.project_id,
2070 cdlrec.task_id,
2071 cdlrec.expenditure_item_id,
2072 cdlrec.expenditure_id,
2073 cdlrec.expenditure_type,
2074 cdlrec.expenditure_category,
2075 cdlrec.system_linkage_function,
2076 cdlrec.organization_id,
2077 cdlrec.non_labor_resource,
2078 curr_invoice_id,
2079 --curr_ap_dist_line_number, -- R12 change
2080 curr_ap_inv_dist_id, -- R12 change
2081 curr_po_vendor_id,
2082 curr_employee_id,
2083 cdlrec.attribute1,
2084 cdlrec.attribute2,
2085 cdlrec.attribute3,
2086 cdlrec.attribute4,
2087 cdlrec.attribute5,
2088 cdlrec.attribute6,
2089 cdlrec.attribute7,
2090 cdlrec.attribute8,
2091 cdlrec.attribute9,
2092 cdlrec.attribute10,
2093 cdlrec.attribute_category,
2094 x_in_service_date_through,
2095 client_asset_id);
2096
2097 l_asset_v:=FALSE;
2098 if (client_asset_id > 0) then
2099 if (nvl(client_asset_id,0) <> curr_asset_id) then
2100 open c_proj_asset(client_asset_id);
2101 fetch c_proj_asset into extn_project_id,extn_date_in_service,
2102 extn_capital_event_id,extn_capital_hold_flag,extn_project_asset_type;
2103 if extn_project_id is null then
2104 extn_error_code :='I';
2105 asset_valid :=FALSE;
2106 end if;
2107 if (nvl(extn_project_id,0) <> x_project_id) then
2108 extn_error_code := 'P';
2109 asset_valid := FALSE;
2110 elsif (extn_date_in_service is null) then
2111 extn_error_code := 'D';
2112 /* Start: Added the following for bug 6788325 */
2113 if (l_profile_no_dpis = 'N') then
2114 asset_valid := FALSE;
2115 else
2116 asset_valid := TRUE;
2117 end if;
2118 /* End: Added the following for bug 6788325 */
2119 l_asset_v := TRUE;
2120 elsif trunc(nvl(extn_date_in_service,'')) > trunc(x_in_service_date_through) then
2121 extn_error_code := 'B';
2122 asset_valid := FALSE;
2123 ELSIF x_line_type = 'C' AND extn_project_asset_type <> 'AS-BUILT' THEN
2124 extn_error_code := 'T';
2125 asset_valid := FALSE;
2126 ELSIF x_line_type = 'R' AND extn_project_asset_type <> 'RETIREMENT_ADJUSTMENT' THEN
2127 extn_error_code := 'T';
2128 asset_valid := FALSE;
2129 ELSIF extn_capital_hold_flag = 'Y' THEN
2130 extn_error_code := 'G';
2131 asset_valid := FALSE;
2132 ELSIF NVL(extn_capital_event_id,-1) <> cdlrec.capital_event_id THEN
2133 extn_error_code := 'E';
2134 asset_valid := FALSE;
2135 else
2136 asset_valid := TRUE;
2137 end if;
2138 close c_proj_asset;
2139
2140 /* Modified the following condition for bug 6788325 inorder to build the logic in such a way that
2141 the capital exceptions should record all the exceptions when asset is not valid except for the
2142 asset without date place in services when the profile option "PA_ASSIGN_ASSET_LINE_WITH_NO_DPIS"
2143 is set to "No"/"N"(for Cingular)*/
2144
2145 if (((asset_valid = FALSE) AND (l_asset_v = FALSE)) OR ((asset_valid = TRUE) AND (l_asset_v = TRUE))) then
2146 begin
2147 insert into pa_capital_exceptions(request_id,
2148 module,
2149 record_type,
2150 project_id,
2151 project_asset_id,
2152 error_code,
2153 created_by,
2154 creation_date)
2155 values (x_request_id,
2156 'CAPITAL',
2157 'E',
2158 x_project_id,
2159 nvl(client_asset_id,l_asset_id),
2160 extn_error_code,
2161 x_created_by,
2162 fnd_date.date_to_canonical(sysdate));
2163 exception when others then
2164 x_err_code := SQLCODE;
2165 RAISE;
2166 end;
2167 end if;
2168 /* Commenting out this logic for bug 6788325 as this logic is already
2169 being handled outside the if loop for client_asset_id > 0
2170 IF (asset_valid = TRUE) OR (l_asset_v = TRUE) then
2171 curr_asset_id := client_asset_id;
2172 end if;
2173 */
2174 end if;
2175 end if; /* if (client_asset_id > 0) */
2176
2177 /* Fix for bug : 4962096 */
2178 IF (asset_valid = TRUE) then
2179 curr_asset_id := client_asset_id;
2180 END IF;
2181
2182 end if;
2183 ------------------------end client extension
2184 if (asset_valid = TRUE) then
2185 -- Process the CDL here
2186
2187 curr_grp_task_id := cdlrec.group_level_task_id;
2188 curr_ccid := cdlrec.cip_ccid;
2189 curr_asset_category_id := cdlrec.asset_category_id;
2190 curr_capital_event_id := cdlrec.capital_event_id;
2191
2192 if cdlrec.system_linkage_function ='VI' and l_num_asset_assignment=1 then
2193 curr_asset_category_id:=NULL;
2194 end if;
2195
2196 IF FIRST_REC OR (((NVL(curr_asset_id,-1) = NVL(prev_asset_id,-1)) AND
2197 (NVL(curr_grp_task_id,-1) = NVL(prev_grp_task_id,-1)) AND
2198 (NVL(curr_asset_category_id,-1) = NVL(prev_asset_category_id,-1)) AND
2199 (NVL(curr_ccid,-1) = NVL(prev_ccid,-1)) AND
2200 (NVL(curr_grouping_method,'-1') = NVL(cdlrec.grouping_method,'-1')) AND
2201 (NVL(curr_capital_event_id,-1) = NVL(prev_capital_event_id,-1))
2202 )) then
2203 -- This cdl is candidate for summarization
2204
2205 if (first_rec) then
2206 get_asset_attributes
2207 (curr_asset_id,
2208 depreciation_expense_ccid,
2209 x_err_stage,
2210 x_err_code);
2211
2212 SELECT pa_project_asset_line_det_s.nextval
2213 INTO proj_asset_line_detail_id
2214 FROM sys.dual;
2215 curr_grouping_method:=cdlrec.grouping_method;
2216 description:= curr_grouping_method ;
2217
2218 --Get Meaning from pa_lookups for Retirement Cost Type, populate Retirement Cost Type
2219 IF (x_line_type = 'R') THEN
2220
2221 IF cdlrec.grouping_method = 'POS' THEN
2222 v_retirement_cost_type := 'POS';
2223 ELSE
2224 v_retirement_cost_type := 'COR';
2225 END IF;
2226
2227 SELECT meaning
2228 INTO description
2229 FROM pa_lookups
2230 WHERE lookup_type = 'RETIREMENT_COST_TYPE'
2231 AND lookup_code = cdlrec.grouping_method;
2232
2233 END IF;
2234
2235 first_rec := FALSE;
2236
2237 end if;
2238 curr_asset_cost := NVL(cdlrec.cip_cost,0) + curr_asset_cost;
2239
2240
2241 -- create a detail line here
2242 create_proj_asset_line_details
2243 (cdlrec.expenditure_item_id,
2244 cdlrec.line_num,
2245 proj_asset_line_detail_id,
2246 cdlrec.cip_cost,
2247 'N', -- Reversed_flag
2248 x_err_stage,
2249 x_err_code);
2250
2251 ELSE
2252 update_expenditure_items
2253 (proj_asset_line_detail_id,
2254 'Y',
2255 x_err_stage,
2256 x_err_code);
2257
2258 -- we must create a new project asset line here
2259
2260 create_project_asset_lines
2261 (description, --x_description,
2262 prev_asset_id,
2263 x_project_id,
2264 prev_grp_task_id,
2265 prev_ccid,
2266 depreciation_expense_ccid, --x_asset_cost_ccid
2267 prev_asset_cost, -- original_asset_cost
2268 prev_asset_cost, -- current_asset_cost
2269 proj_asset_line_detail_id,
2270 SYSDATE, --x_gl_date,
2271 'P', --x_transfer_status_code,
2272 NULL, --x_transfer_rejection_reason,
2273 NULL, -- amortize_flag,
2274 prev_asset_category_id, --x_asset_category_id,
2275 NULL, --x_rev_proj_asset_line_id,
2276 NULL, --x_rev_from_proj_asset_line_id,
2277 prev_invoice_num,
2278 prev_vendor_number,
2279 prev_po_vendor_id,
2280 prev_po_number,
2281 prev_invoice_date,
2282 prev_invoice_created_by,
2283 prev_invoice_updated_by,
2284 prev_invoice_id,
2285 prev_payables_batch_name,
2286 prev_ap_dist_line_number,
2287 prev_ap_inv_dist_id, -- R12 new
2288 orig_asset_id,
2289 x_line_type, --line_type,
2290 prev_capital_event_id,
2291 v_retirement_cost_type,
2292 x_err_stage,
2293 x_err_code);
2294
2295 update_asset_cost
2296 (prev_asset_id,
2297 prev_asset_cost,
2298 0, --- capitalized_cost
2299 x_err_stage,
2300 x_err_code);
2301
2302 -- also initialize the curr values here
2303
2304 curr_asset_cost := nvl(cdlrec.cip_cost,0);
2305 curr_grouping_method:=cdlrec.grouping_method;
2306 description:= curr_grouping_method ;
2307
2308
2309 --Get Meaning from pa_lookups for Retirement Cost Type
2310 IF (x_line_type = 'R') THEN
2311
2312 IF cdlrec.grouping_method = 'POS' THEN
2313 v_retirement_cost_type := 'POS';
2314 ELSE
2315 v_retirement_cost_type := 'COR';
2316 END IF;
2317
2318 SELECT meaning
2319 INTO description
2320 FROM pa_lookups
2321 WHERE lookup_type = 'RETIREMENT_COST_TYPE'
2322 AND lookup_code = cdlrec.grouping_method;
2323
2324 END IF;
2325
2326 -- get the attributes of the assets for which the
2327 -- lines are to be created
2328
2329 get_asset_attributes
2330 (curr_asset_id,
2331 depreciation_expense_ccid,
2332 x_err_stage,
2333 x_err_code);
2334
2335 SELECT pa_project_asset_line_det_s.nextval
2336 INTO proj_asset_line_detail_id
2337 FROM sys.dual;
2338 -- create a detail line here
2339
2340 create_proj_asset_line_details
2341 (cdlrec.expenditure_item_id,
2342 cdlrec.line_num,
2343 proj_asset_line_detail_id,
2344 cdlrec.cip_cost,
2345 'N', -- Reversed_flag
2346 x_err_stage,
2347 x_err_code);
2348
2349 END IF;
2350 -----------------------setting previous values
2351 prev_asset_id := curr_asset_id;
2352 prev_asset_cost := curr_asset_cost;
2353 prev_asset_category_id := curr_asset_category_id;
2354 prev_ccid := curr_ccid;
2355 prev_grp_task_id := curr_grp_task_id;
2356 prev_expend_cat := curr_expend_cat;
2357 prev_expend_type := curr_expend_type;
2358 prev_nlr := curr_nlr;
2359 prev_invoice_num := curr_invoice_num;
2360 prev_vendor_number := curr_vendor_number;
2361 prev_po_vendor_id := curr_po_vendor_id;
2362 prev_po_number := curr_po_number;
2363 prev_invoice_date := curr_invoice_date;
2364 prev_invoice_created_by := curr_invoice_created_by;
2365 prev_invoice_updated_by := curr_invoice_updated_by;
2366 prev_invoice_id := curr_invoice_id;
2367 prev_payables_batch_name := curr_payables_batch_name;
2368 prev_ap_dist_line_number := curr_ap_dist_line_number;
2369 prev_inv_line_number := curr_inv_line_number; -- R12 new
2370 prev_ap_inv_dist_id := curr_ap_inv_dist_id; -- R12 new
2371 prev_employee_id := curr_employee_id;
2372 prev_sys_link_fun := cdlrec.system_linkage_function;
2373 prev_capital_event_id := curr_capital_event_id;
2374 --------------------------
2375
2376 end if;
2377
2378
2379 <<SKIP_NO_ACCOUNTS>>
2380 -- Added for bug : 4768332
2381 INSERT INTO pa_reporting_exceptions
2382 (request_id,
2383 context,
2384 sub_context,
2385 attribute1, --project_id
2386 attribute2, --task_id
2387 attribute3, --expenditure_item_id
2388 attribute4, --expenditure_type
2389 attribute5, --line_num
2390 attribute6, --event_id
2391 attribute7, --amount
2392 attribute8, --rejection_code
2393 attribute20 -- transaction_source
2394 )
2395 VALUES
2396 (x_request_id,
2397 'GENERATE_PROJ_ASSET_LINES',
2398 'NO_CC_ID',
2399 cdlrec.project_id,
2400 cdlrec.task_id,
2401 cdlrec.expenditure_item_id,
2402 cdlrec.expenditure_type,
2403 cdlrec.line_num,
2404 cdlrec.ACCT_EVENT_ID,
2405 cdlrec.cip_cost,
2406 'PROJECT EVENT ERRORS',
2407 cdlrec.transaction_source
2408 );
2409
2410 -- null;
2411 END LOOP;
2412
2413 -- Create the asset line for the last cdl here
2414
2415 IF ( NOT first_rec ) THEN
2416 -- mark the expenditure items revenue distributed_flag to 'Y'
2417 update_expenditure_items
2418 (proj_asset_line_detail_id,
2419 'Y',
2420 x_err_stage,
2421 x_err_code);
2422
2423 create_project_asset_lines
2424 (description, --description,
2425 prev_asset_id,
2426 x_project_id,
2427 prev_grp_task_id,
2428 prev_ccid,
2429 depreciation_expense_ccid, --x_asset_cost_ccid,
2430 prev_asset_cost, -- original_asset_cost
2431 prev_asset_cost, -- current_asset_cost
2432 proj_asset_line_detail_id,
2433 SYSDATE, --x_gl_date,
2434 'P', --x_transfer_status_code,
2435 NULL, --x_transfer_rejection_reason,
2436 NULL, -- amortize_flag,
2437 prev_asset_category_id, --x_asset_category_id,
2438 NULL, --x_rev_proj_asset_line_id,
2439 NULL, --x_rev_from_proj_asset_line_id,
2440 prev_invoice_num,
2441 prev_vendor_number,
2442 prev_po_vendor_id,
2443 prev_po_number,
2444 prev_invoice_date,
2445 prev_invoice_created_by,
2446 prev_invoice_updated_by,
2447 prev_invoice_id,
2448 prev_payables_batch_name,
2449 prev_ap_dist_line_number,
2450 prev_ap_inv_dist_id, -- R12 new
2451 orig_asset_id,
2452 x_line_type, --line_type,
2453 prev_capital_event_id,
2454 v_retirement_cost_type,
2455 x_err_stage,
2456 x_err_code);
2457
2458 update_asset_cost
2459 (prev_asset_id,
2460 prev_asset_cost,
2461 0, --- capitalized_cost
2462 x_err_stage,
2463 x_err_code);
2464 END IF;
2465
2466 EXCEPTION
2467 WHEN OTHERS THEN
2468 x_err_code := SQLCODE;
2469 RAISE;
2470 END generate_proj_asset_lines;
2471
2472 -- The procedure given below marks the asset lines to be transferred
2473 -- to FA
2474
2475 PROCEDURE mark_asset_lines_for_xfer
2476 ( x_project_id IN NUMBER,
2477 x_in_service_date_through IN DATE,
2478 x_line_type IN VARCHAR2,
2479 x_rowcount IN OUT NOCOPY NUMBER,
2480 x_err_stage IN OUT NOCOPY VARCHAR2,
2481 x_err_code IN OUT NOCOPY NUMBER)
2482 IS
2483 fa_install_status VARCHAR2(1);
2484 x_rows_rejected NUMBER;
2485 BEGIN
2486
2487 x_err_code := 0;
2488 x_err_stage := 'Marking project asset lines for transfer';
2489
2490 UPDATE
2491 pa_project_asset_lines pal
2492 SET
2493 pal.transfer_status_code = 'X'
2494 WHERE
2495 pal.transfer_status_code||'' IN ('P','R')
2496 AND pal.project_id = x_project_id
2497 AND pal.line_type = x_line_type
2498 AND exists
2499 (SELECT null -- Update project asset lines belonging to new assets
2500 FROM
2501 pa_project_assets ppa
2502 WHERE
2503 ppa.project_id = pal.project_id
2504 AND ppa.project_asset_id = pal.project_asset_id
2505 AND
2506 (
2507 (ppa.capitalized_flag = 'N'
2508 AND ppa.reverse_flag||'' = 'N'
2509 AND TRUNC(ppa.date_placed_in_service) <=
2510 TRUNC(NVL(x_in_service_date_through,
2511 ppa.date_placed_in_service))
2512 )
2513 OR
2514 (-- Update project asset lines for the assets which were
2515 -- transferred to FA previously
2516 ppa.capitalized_flag = 'Y'
2517 AND ppa.reverse_flag||'' = 'N'
2518 AND TRUNC(ppa.date_placed_in_service) <=
2519 TRUNC(NVL(x_in_service_date_through,
2520 ppa.date_placed_in_service))
2521 AND pal.rev_proj_asset_line_id is null
2522 )
2523 OR
2524 ( ppa.capitalized_flag = 'Y'
2525 AND pal.rev_proj_asset_line_id is not null
2526 )
2527 )
2528 );
2529
2530 x_rowcount := SQL%ROWCOUNT;
2531
2532
2533
2534 --obtain fa installation status
2535 fa_install_status := pa_asset_utils.fa_implementation_status;
2536
2537 IF fa_install_status <> 'N' then
2538 -- perform check for if any asset belongs to future FA period
2539
2540 reject_lines_check1 (x_rows_rejected,x_err_stage, x_err_code);
2541
2542 x_rowcount := x_rowcount - x_rows_rejected;
2543 END IF;
2544 EXCEPTION
2545 WHEN OTHERS THEN
2546 x_err_code := SQLCODE;
2547 RAISE;
2548 END mark_asset_lines_for_xfer;
2549
2550 PROCEDURE mark_reversing_lines(x_project_id IN NUMBER,
2551 x_capital_event_id IN NUMBER,
2552 x_line_type IN VARCHAR2,
2553 x_err_stage IN OUT NOCOPY VARCHAR2,
2554 x_err_code IN OUT NOCOPY NUMBER)
2555 IS
2556 BEGIN
2557
2558 x_err_code := 0;
2559 x_err_stage := 'Marking reversing lines ';
2560
2561 UPDATE
2562 pa_project_asset_lines pal
2563 SET
2564 pal.transfer_status_code = 'X'
2565 WHERE pal.transfer_status_code||'' IN ('P','R')
2566 AND rev_proj_asset_line_id is not null
2567 AND pal.project_id = x_project_id
2568 AND pal.capital_event_id = NVL(x_capital_event_id, pal.capital_event_id)
2569 AND pal.line_type = x_line_type
2570 AND pal.project_asset_id IN
2571 (SELECT
2572 ppa.project_asset_id
2573 FROM
2574 pa_project_assets ppa
2575 WHERE
2576 ppa.project_id = pal.project_id
2577 AND ppa.capitalized_flag = 'Y'
2578 );
2579
2580 EXCEPTION
2581 WHEN OTHERS THEN
2582 x_err_code := SQLCODE;
2583 RAISE;
2584 END mark_reversing_lines;
2585
2586 PROCEDURE update_asset_capitalized_flag
2587 (x_project_asset_id IN NUMBER,
2588 x_capitalized_flag IN VARCHAR2,
2589 x_err_stage IN OUT NOCOPY VARCHAR2,
2590 x_err_code IN OUT NOCOPY NUMBER)
2591 IS
2592 BEGIN
2593
2594 x_err_code := 0;
2595 x_err_stage := 'Updating the asset status';
2596
2597 UPDATE
2598 pa_project_assets
2599 SET
2600 capitalized_flag = x_capitalized_flag,
2601 capitalized_date = sysdate,
2602 last_update_date = sysdate,
2603 last_updated_by = x_last_updated_by,
2604 last_update_login = x_last_update_login,
2605 request_id = x_request_id,
2606 program_application_id = x_program_application_id,
2607 program_id = x_program_id,
2608 program_update_date = sysdate
2609 WHERE
2610 project_asset_id = x_project_asset_id;
2611
2612 EXCEPTION
2613 WHEN OTHERS THEN
2614 x_err_code := SQLCODE;
2615 RAISE;
2616 END update_asset_capitalized_flag;
2617
2618 PROCEDURE update_asset_adjustment_flag
2619 (x_project_asset_id IN NUMBER,
2620 x_adjustment_flag IN VARCHAR2,
2621 x_adjustment_type IN VARCHAR2,
2622 x_err_stage IN OUT NOCOPY VARCHAR2,
2623 x_err_code IN OUT NOCOPY NUMBER)
2624 IS
2625 BEGIN
2626
2627 x_err_code := 0;
2628 x_err_stage := 'Updating the asset status';
2629
2630 IF x_adjustment_type = 'R' THEN
2631 --this is a reversing adjustment so do NOT update capitalized_date
2632 UPDATE
2633 pa_project_assets
2634 SET
2635 cost_adjustment_flag = x_adjustment_flag,
2636 last_update_date = sysdate,
2637 last_updated_by = x_last_updated_by,
2638 last_update_login = x_last_update_login,
2639 request_id = x_request_id,
2640 program_application_id = x_program_application_id,
2641 program_id = x_program_id,
2642 program_update_date = sysdate
2643 WHERE
2644 project_asset_id = x_project_asset_id;
2645 ELSE
2646 --this is a non-reversing adjustment. Update capitalized_date
2647 UPDATE
2648 pa_project_assets
2649 SET
2650 cost_adjustment_flag = x_adjustment_flag,
2651 capitalized_date = sysdate,
2652 last_update_date = sysdate,
2653 last_updated_by = x_last_updated_by,
2654 last_update_login = x_last_update_login,
2655 request_id = x_request_id,
2656 program_application_id = x_program_application_id,
2657 program_id = x_program_id,
2658 program_update_date = sysdate
2659 WHERE
2660 project_asset_id = x_project_asset_id;
2661 END IF;
2662
2663
2664 EXCEPTION
2665 WHEN OTHERS THEN
2666 x_err_code := SQLCODE;
2667 RAISE;
2668 END update_asset_adjustment_flag;
2669
2670 -- x_num_asset_found will return the number of assets found
2671 -- x_asset_id_in_FA is the asset_id in FA when the x_num_asset_found = 1
2672 -- otherwise it will be returned as NULL
2673
2674 PROCEDURE check_asset_id_in_FA
2675 (x_project_asset_id IN NUMBER,
2676 x_asset_id_in_FA IN OUT NOCOPY NUMBER,
2677 x_num_asset_found IN OUT NOCOPY NUMBER,
2678 x_book_type_code IN VARCHAR2,
2679 x_date_placed_in_service IN OUT NOCOPY DATE,
2680 x_err_stage IN OUT NOCOPY VARCHAR2,
2681 x_err_code IN OUT NOCOPY NUMBER)
2682 IS
2683 CURSOR selfaassets IS
2684 SELECT
2685 fai.asset_id
2686 FROM
2687 fa_asset_invoices fai,
2688 pa_project_asset_lines pal
2689 WHERE
2690 fai.project_asset_line_id = pal.project_asset_line_id
2691 AND pal.project_asset_id = x_project_asset_id
2692 AND pal.transfer_status_code||'' = 'T'
2693 GROUP BY
2694 fai.asset_id;
2695
2696 faassetrec selfaassets%ROWTYPE;
2697 -- Fix for bug : 4878878
2698 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2699
2700 v_fa_asset_id PA_PROJECT_ASSETS_ALL.fa_asset_id%TYPE;
2701
2702
2703 BEGIN
2704
2705 x_err_code := 0;
2706 x_err_stage := 'Finding the asset id in FA';
2707
2708 x_asset_id_in_FA := NULL;
2709 x_num_asset_found := 0;
2710
2711 --The following code has been inserted to leverage the FA Tieback process.
2712 --This code looks for the FA_ASSET_ID on the Project Asset row, which is populated
2713 --if the project asset has been Tied Back.
2714
2715 SELECT fa_asset_id
2716 INTO v_fa_asset_id
2717 FROM pa_project_assets_all
2718 WHERE project_asset_id = x_project_asset_id;
2719
2720 IF v_fa_asset_id IS NOT NULL THEN
2721
2722 x_asset_id_in_FA := v_fa_asset_id;
2723 --Bug 3057423
2724 x_num_asset_found := 1;
2725
2726 ELSE
2727
2728 --The following original code can be executed to determine the asset_id, since the
2729 --project asset has not been tied back
2730
2731
2732
2733 FOR faassetrec IN selfaassets LOOP
2734
2735 x_num_asset_found := x_num_asset_found + 1;
2736 x_asset_id_in_FA := faassetrec.asset_id;
2737
2738
2739 IF ( x_num_asset_found > 1 ) THEN
2740
2741 -- If more than one asset is found then return NULL x_asset_id_in_FA
2742 x_asset_id_in_FA := NULL;
2743 x_date_placed_in_service := NULL;
2744 return;
2745 END IF;
2746
2747 END LOOP;
2748
2749 -- If no assets found then return
2750 if ( x_num_asset_found = 0 ) then
2751 return;
2752 end if;
2753
2754 END IF;
2755
2756
2757 -- lookup the fa date placed in service
2758 -- Populate the Date placed in Service with the FA Open period Date
2759
2760 BEGIN
2761
2762 select GREATEST(fdp.calendar_period_open_date,
2763 LEAST(sysdate,fdp.calendar_period_close_date))
2764 into x_date_placed_in_service
2765 from fa_deprn_periods fdp
2766 where fdp.book_type_code = x_book_type_code
2767 and fdp.period_close_date is null;
2768
2769 EXCEPTION
2770 WHEN NO_DATA_FOUND THEN
2771 IF G_debug_mode = 'Y' THEN
2772 pa_debug.debug('check_asset_id_in_FA: ' || 'Current Deprn. Period not found for book : '||x_book_type_code);
2773 END IF;
2774 -- Initialised x_date_placed_in_service as NULL when deprn.
2775 -- period is not found for book
2776 x_date_placed_in_service:=NULL;
2777 END;
2778
2779 EXCEPTION
2780 WHEN OTHERS THEN
2781 x_err_code := SQLCODE;
2782 RAISE;
2783 END check_asset_id_in_FA;
2784
2785
2786 -- The procedure will reject the asset lines for the asset having
2787 -- the date_placed_in_service corresponding to a future date in FA
2788 -- This check need to be applied for new assets being transferred
2789 -- to FA
2790
2791 PROCEDURE reject_lines_check1
2792 (x_rows_rejected IN OUT NOCOPY NUMBER,
2793 x_err_stage IN OUT NOCOPY VARCHAR2,
2794 x_err_code IN OUT NOCOPY NUMBER)
2795 IS
2796
2797 transfer_rejection_reason pa_lookups.meaning%TYPE;
2798
2799 BEGIN
2800
2801 x_err_code := 0;
2802 x_err_stage := 'Applying Check1';
2803
2804 -- Update project asset lines belonging to new assets
2805
2806 UPDATE
2807 pa_project_asset_lines pal
2808 SET
2809 pal.transfer_status_code = 'R',
2810 pal.transfer_rejection_reason = 'FUTURE_DPIS',
2811 pal.last_updated_by = x_last_updated_by,
2812 pal.last_update_date= sysdate,
2813 pal.created_by = x_created_by,
2814 pal.last_update_login = x_last_update_login,
2815 pal.request_id = x_request_id,
2816 pal.program_application_id = x_program_application_id,
2817 pal.program_id = x_program_id
2818 WHERE
2819 pal.transfer_status_code = 'X'
2820 AND exists
2821 (SELECT
2822 'Yes'
2823 FROM
2824 pa_project_assets ppa
2825 WHERE
2826 ppa.project_asset_id = pal.project_asset_id
2827 AND ppa.capitalized_flag = 'N'
2828 AND ppa.reverse_flag||'' = 'N'
2829 AND fa_mass_add_validate.valid_date_in_service(ppa.date_placed_in_service,ppa.book_type_code) = 0
2830 );
2831
2832 x_rows_rejected := SQL%ROWCOUNT;
2833 EXCEPTION
2834 WHEN OTHERS THEN
2835 x_err_code := SQLCODE;
2836 RAISE;
2837 END reject_lines_check1;
2838
2839 PROCEDURE update_asset_lines
2840 (x_proj_asset_line_id IN NUMBER,
2841 x_transfer_rejection_reason IN VARCHAR2,
2842 x_transfer_status_code IN VARCHAR2,
2843 x_amortize_flag IN VARCHAR2,
2844 x_err_stage IN OUT NOCOPY VARCHAR2,
2845 x_err_code IN OUT NOCOPY NUMBER)
2846 IS
2847 BEGIN
2848
2849 x_err_code := 0;
2850 x_err_stage := 'Updating the project asset line';
2851
2852 UPDATE
2853 pa_project_asset_lines
2854 SET
2855 transfer_rejection_reason = x_transfer_rejection_reason,
2856 transfer_status_code = x_transfer_status_code,
2857 amortize_flag = x_amortize_flag,
2858 last_update_date = sysdate,
2859 last_updated_by = x_last_updated_by,
2860 last_update_login = x_last_update_login,
2861 request_id = x_request_id,
2862 program_application_id = x_program_application_id,
2863 program_id = x_program_id,
2864 program_update_date = sysdate
2865 WHERE
2866 project_asset_line_id = x_proj_asset_line_id;
2867
2868 EXCEPTION
2869 WHEN OTHERS THEN
2870 x_err_code := SQLCODE;
2871 RAISE;
2872 END update_asset_lines;
2873
2874 -- Procedure to create mass addition lines
2875
2876 PROCEDURE create_fa_mass_additions
2877 (x_accounting_date IN DATE,
2878 x_add_to_asset_id IN NUMBER,
2879 x_amortize_flag IN VARCHAR2,
2880 x_asset_category_id IN NUMBER,
2881 x_asset_key_ccid IN NUMBER,
2882 x_asset_number IN VARCHAR2,
2883 x_asset_type IN VARCHAR2,
2884 x_assigned_to IN NUMBER,
2885 x_book_type_code IN VARCHAR2,
2886 x_create_batch_date IN DATE,
2887 x_create_batch_id IN NUMBER,
2888 x_date_placed_in_service IN DATE,
2889 x_depreciate_flag IN VARCHAR2,
2890 x_description IN VARCHAR2,
2891 x_expense_code_combination_id IN NUMBER,
2892 x_feeder_system_name IN VARCHAR2,
2893 x_fixed_assets_cost IN NUMBER,
2894 x_fixed_assets_units IN NUMBER,
2895 x_location_id IN NUMBER,
2896 x_mass_addition_id IN OUT NOCOPY NUMBER,
2897 x_merged_code IN VARCHAR2,
2898 x_merge_prnt_mass_additions_id IN NUMBER,
2899 x_new_master_flag IN VARCHAR2,
2900 x_parent_mass_addition_id IN NUMBER,
2901 x_payables_code_combination_id IN NUMBER,
2902 x_payables_cost IN NUMBER,
2903 x_payables_units IN NUMBER,
2904 x_posting_status IN VARCHAR2,
2905 x_project_asset_line_id IN NUMBER,
2906 x_project_id IN NUMBER,
2907 x_queue_name IN VARCHAR2,
2908 x_split_code IN VARCHAR2,
2909 x_split_merged_code IN VARCHAR2,
2910 x_split_prnt_mass_additions_id IN NUMBER,
2911 x_task_id IN NUMBER,
2912 x_invoice_number IN VARCHAR2,
2913 x_vendor_number IN VARCHAR2,
2914 x_po_vendor_id IN NUMBER,
2915 x_po_number IN VARCHAR2,
2916 x_invoice_date IN DATE,
2917 x_invoice_created_by IN NUMBER,
2918 x_invoice_updated_by IN NUMBER,
2919 x_invoice_id IN NUMBER,
2920 x_payables_batch_name IN VARCHAR2,
2921 x_ap_dist_line_number IN Number,
2922 x_invoice_distribution_id IN Number, -- R12 new
2923 x_parent_asset_id IN NUMBER,
2924 x_manufacturer_name IN VARCHAR2,
2925 x_model_number IN VARCHAR2,
2926 x_serial_number IN VARCHAR2,
2927 x_tag_number IN VARCHAR2,
2928 x_err_stage IN OUT NOCOPY VARCHAR2,
2929 x_err_code IN OUT NOCOPY NUMBER
2930 )
2931 IS
2932 x_inventorial_flag VARCHAR2(3);
2933 l_amortization_start_date DATE;
2934 -- Fix for bug : 4878878
2935 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2936 x_period_of_addition VARCHAR2(1);
2937 x_amort_flag VARCHAR2(3);
2938
2939 BEGIN
2940
2941 x_err_code := 0;
2942 x_err_stage := 'Creating Mass addition Lines';
2943 x_amort_flag := x_amortize_flag;
2944
2945
2946
2947 BEGIN
2948 -- Inventorial Flag is taken from FA_CATEGOIRES
2949 -- if the asset line is associated with asset category
2950 -- else insert 'YES' inot inventorial column of fa_mass_additions
2951
2952 SELECT inventorial
2953 INTO x_inventorial_flag
2954 FROM fa_categories
2955 WHERE category_id = x_asset_category_id;
2956
2957 EXCEPTION
2958 WHEN NO_DATA_FOUND THEN
2959 x_inventorial_flag := 'YES';
2960 END ;
2961
2962 -- Get the mass_additions_id
2963
2964 SELECT
2965 fa_mass_additions_s.nextval
2966 INTO
2967 x_mass_addition_id
2968 FROM
2969 SYS.DUAL;
2970
2971 /*If this is a new asset */
2972 IF x_add_to_asset_id is NULL THEN
2973 x_amort_flag := Null;
2974 l_amortization_start_date := NULL;
2975 /* If asset is in period of addition */
2976 ELSIF FA_ASSET_VAL_PVT.validate_period_of_addition
2977 (p_asset_id => x_add_to_asset_id,
2978 p_book => x_book_type_code,
2979 p_mode => 'ABSOLUTE',
2980 px_period_of_addition => x_period_of_addition) THEN
2981 IF nvl(x_period_of_addition,'N') = 'Y' THEN
2982 x_amort_flag := Null;
2983 l_amortization_start_date := NULL;
2984 END IF;
2985 ELSE
2986 /* populating Amort flag based on the following logic */
2987 l_amortization_start_date := NULL;
2988 IF nvl(x_amort_flag,'NO')='YES' THEN /*changed x_amortize_flag to x_amort_flag -bug4349574*/
2989 BEGIN
2990 SELECT
2991 GREATEST(fdp.calendar_period_open_date,LEAST(sysdate,fdp.calendar_period_close_date))
2992 INTO
2993 l_amortization_start_date
2994 FROM
2995 fa_deprn_periods fdp
2996 WHERE
2997 fdp.book_type_code = x_book_type_code
2998 and fdp.period_close_date is null;
2999 EXCEPTION
3000 WHEN NO_DATA_FOUND THEN
3001 IF G_debug_mode = 'Y' THEN
3002 pa_debug.debug('create_fa_mass_additions: ' || 'Current Deprn. Period not found for book = '||x_book_type_code);
3003 END IF;
3004 l_amortization_start_date:= NULL;
3005 END;
3006 END IF;
3007
3008 END IF; /* bug 4349574 -End*/
3009
3010 -- Added by tiwang for CRL Projects
3011 /* Bug 3224283. Restructured the code because of the data corruption that
3012 might occur if the profile option is incorrectly set.
3013 The strategy is to insert the records into fa_mass_additions irrespective
3014 of whether the profile option PA: Licensed to Use CRL Projects is set or not.
3015 And if it is set, update the CRL specific columns in IPAFAXB.pls
3016 Refer 3224283 and 3224294 */
3017 /* Commented the following code for the above reason and added it after the
3018 insert into fa_mass_additions statement
3019 if (PA_INSTALL.is_product_installed('IPA')) then --CRL Installed
3020 PA_CRL_FAXFACE.create_crl_fa_mass_additions
3021 (x_accounting_date ,
3022 x_add_to_asset_id ,
3023 x_amortize_flag ,
3024 x_asset_category_id ,
3025 x_asset_key_ccid ,
3026 x_asset_number ,
3027 x_asset_type ,
3028 x_assigned_to ,
3029 x_book_type_code ,
3030 x_create_batch_date ,
3031 x_create_batch_id ,
3032 x_date_placed_in_service ,
3033 x_depreciate_flag ,
3034 x_description ,
3035 x_expense_code_combination_id ,
3036 x_feeder_system_name ,
3037 x_fixed_assets_cost ,
3038 x_fixed_assets_units ,
3039 x_location_id ,
3040 x_mass_addition_id ,
3041 x_merged_code ,
3042 x_merge_prnt_mass_additions_id ,
3043 x_new_master_flag ,
3044 x_parent_mass_addition_id ,
3045 x_payables_code_combination_id ,
3046 x_payables_cost ,
3047 x_payables_units ,
3048 x_posting_status ,
3049 x_project_asset_line_id ,
3050 x_project_id ,
3051 x_queue_name ,
3052 x_split_code ,
3053 x_split_merged_code ,
3054 x_split_prnt_mass_additions_id ,
3055 x_task_id ,
3056 x_inventorial_flag ,
3057 x_invoice_number ,
3058 x_vendor_number ,
3059 x_po_vendor_id ,
3060 x_po_number ,
3061 x_invoice_date ,
3062 x_invoice_created_by ,
3063 x_invoice_updated_by ,
3064 x_invoice_id ,
3065 x_payables_batch_name ,
3066 x_ap_dist_line_number ,
3067 x_invoice_distribution_id , -- R12 new
3068 x_err_stage ,
3069 x_err_code );
3070
3071 else
3072 Commented code for bug#3224283 ends here */
3073 INSERT INTO FA_MASS_ADDITIONS(
3074 ACCOUNTING_DATE,
3075 ADD_TO_ASSET_ID,
3076 AMORTIZE_FLAG,
3077 AMORTIZATION_START_DATE, /* added for bug # 2540723 */
3078 ASSET_CATEGORY_ID,
3079 ASSET_KEY_CCID,
3080 ASSET_NUMBER,
3081 ASSET_TYPE,
3082 ASSIGNED_TO,
3083 BOOK_TYPE_CODE,
3084 CREATED_BY,
3085 CREATE_BATCH_DATE,
3086 CREATE_BATCH_ID,
3087 CREATION_DATE,
3088 DATE_PLACED_IN_SERVICE,
3089 DEPRECIATE_FLAG,
3090 DESCRIPTION,
3091 EXPENSE_CODE_COMBINATION_ID,
3092 FEEDER_SYSTEM_NAME,
3093 FIXED_ASSETS_COST,
3094 FIXED_ASSETS_UNITS,
3095 LAST_UPDATED_BY,
3096 LAST_UPDATE_DATE,
3097 LAST_UPDATE_LOGIN,
3098 LOCATION_ID,
3099 MASS_ADDITION_ID,
3100 MERGED_CODE,
3101 MERGE_PARENT_MASS_ADDITIONS_ID,
3102 NEW_MASTER_FLAG,
3103 PARENT_MASS_ADDITION_ID,
3104 PAYABLES_CODE_COMBINATION_ID,
3105 PAYABLES_COST,
3106 PAYABLES_UNITS,
3107 POSTING_STATUS,
3108 PROJECT_ASSET_LINE_ID,
3109 PROJECT_ID,
3110 QUEUE_NAME,
3111 SPLIT_CODE,
3112 SPLIT_MERGED_CODE,
3113 SPLIT_PARENT_MASS_ADDITIONS_ID,
3114 TASK_ID,
3115 INVENTORIAL,
3116 invoice_number,
3117 vendor_number,
3118 po_vendor_id,
3119 po_number,
3120 invoice_date,
3121 invoice_created_by,
3122 invoice_updated_by,
3123 invoice_id,
3124 payables_batch_name,
3125 ap_distribution_line_number,
3126 invoice_line_number,
3127 invoice_distribution_id, -- R12 new
3128 parent_asset_id,
3129 manufacturer_name,
3130 model_number,
3131 serial_number,
3132 tag_number
3133 )
3134 SELECT
3135 x_accounting_date,
3136 x_add_to_asset_id,
3137 /* x_amortize_flag, */
3138 x_amort_flag, /*bug 4349574*/
3139 l_amortization_start_date, /* bug#2540723 */
3140 x_asset_category_id,
3141 x_asset_key_ccid,
3142 x_asset_number,
3143 x_asset_type,
3144 x_assigned_to,
3145 x_book_type_code,
3146 x_created_by,
3147 x_create_batch_date,
3148 x_create_batch_id,
3149 SYSDATE,
3150 x_date_placed_in_service,
3151 x_depreciate_flag,
3152 -- Commented for bug 5408702 substr(x_description,1,80), Substr included for bug# 1607527
3153 rtrim(substrb(x_description,1,80)), -- rtrim and Substrb included for bug# 5408702
3154 x_expense_code_combination_id,
3155 x_feeder_system_name,
3156 x_fixed_assets_cost,
3157 x_fixed_assets_units,
3158 x_last_updated_by,
3159 SYSDATE,
3160 x_last_update_login,
3161 x_location_id,
3162 x_mass_addition_id,
3163 x_merged_code,
3164 x_merge_prnt_mass_additions_id,
3165 x_new_master_flag,
3166 x_parent_mass_addition_id,
3167 x_payables_code_combination_id,
3168 x_payables_cost,
3169 x_payables_units,
3170 x_posting_status,
3171 x_project_asset_line_id,
3172 x_project_id,
3173 x_queue_name,
3174 x_split_code,
3175 x_split_merged_code,
3176 x_split_prnt_mass_additions_id,
3177 x_task_id,
3178 x_inventorial_flag,
3179 x_invoice_number,
3180 x_vendor_number,
3181 x_po_vendor_id,
3182 x_po_number,
3183 x_invoice_date,
3184 x_invoice_created_by,
3185 x_invoice_updated_by,
3186 x_invoice_id,
3187 x_payables_batch_name,
3188 x_ap_dist_line_number,
3189 NULL, -- invoice_line_number,
3190 x_invoice_distribution_id, -- R12 new
3191 x_parent_asset_id,
3192 x_manufacturer_name,
3193 x_model_number,
3194 x_serial_number,
3195 x_tag_number
3196 FROM SYS.DUAL;
3197
3198 /* Bug#3224283. Added Call to pa_crl_faxface after inserting the
3199 record into fa_mass_additions */
3200 if (PA_INSTALL.is_product_installed('IPA')) then --CRL Installed
3201 PA_CRL_FAXFACE.create_crl_fa_mass_additions
3202 (x_accounting_date ,
3203 x_add_to_asset_id ,
3204 /* x_amortize_flag , */
3205 x_amort_flag , /*bug 4349574 */
3206 x_asset_category_id ,
3207 x_asset_key_ccid ,
3208 x_asset_number ,
3209 x_asset_type ,
3210 x_assigned_to ,
3211 x_book_type_code ,
3212 x_create_batch_date ,
3213 x_create_batch_id ,
3214 x_date_placed_in_service ,
3215 x_depreciate_flag ,
3216 rtrim(substrb(x_description,1,80)) , /* Added for bug 5408702 */
3217 x_expense_code_combination_id ,
3218 x_feeder_system_name ,
3219 x_fixed_assets_cost ,
3220 x_fixed_assets_units ,
3221 x_location_id ,
3222 x_mass_addition_id ,
3223 x_merged_code ,
3224 x_merge_prnt_mass_additions_id ,
3225 x_new_master_flag ,
3226 x_parent_mass_addition_id ,
3227 x_payables_code_combination_id ,
3228 x_payables_cost ,
3229 x_payables_units ,
3230 x_posting_status ,
3231 x_project_asset_line_id ,
3232 x_project_id ,
3233 x_queue_name ,
3234 x_split_code ,
3235 x_split_merged_code ,
3236 x_split_prnt_mass_additions_id ,
3237 x_task_id ,
3238 x_inventorial_flag ,
3239 x_invoice_number ,
3240 x_vendor_number ,
3241 x_po_vendor_id ,
3242 x_po_number ,
3243 x_invoice_date ,
3244 x_invoice_created_by ,
3245 x_invoice_updated_by ,
3246 x_invoice_id ,
3247 x_payables_batch_name ,
3248 --x_ap_dist_line_number , Send invoice_distribution_id instead.
3249 x_invoice_distribution_id , -- R12 new.
3250 x_err_stage ,
3251 x_err_code );
3252 end if; /* If CRL Installed */
3253
3254 IF l_mrc_flag = 'Y' THEN
3255 create_alc_fa_mass_additions (x_project_asset_line_id => x_project_asset_line_id,
3256 x_mass_addition_id => x_mass_addition_id,
3257 x_parent_mass_addition_id => x_parent_mass_addition_id,
3258 x_fixed_assets_cost => x_fixed_assets_cost);
3259 END IF;
3260
3261 EXCEPTION
3262 WHEN OTHERS THEN
3263 x_err_code := SQLCODE;
3264 RAISE;
3265 END create_fa_mass_additions;
3266
3267 -- Procedure for transferring asset lines
3268 -- The paramater x_asset_type can have the following values:
3269 -- 'N' -- New asset being transferred for the first time
3270 -- 'O' -- An old asset for which lines were already sent to FA
3271 -- and send cost adjustment for these assets
3272 -- 'R' -- An asset being reversed
3273
3274 -- x_reversed_line_flag is not required when x_asset_type = 'N'
3275
3276 PROCEDURE interface_asset_lines
3277 ( x_project_id IN NUMBER,
3278 x_asset_type IN VARCHAR2,
3279 x_in_service_date_through IN DATE,
3280 x_reversed_line_flag IN VARCHAR2,
3281 x_err_stage IN OUT NOCOPY VARCHAR2,
3282 x_err_code IN OUT NOCOPY NUMBER)
3283 IS
3284 CURSOR selassetlines IS
3285 SELECT
3286 ppa.project_id,
3287 ppa.project_asset_id,
3288 pal.cip_ccid,
3289 pal.asset_cost_ccid,
3290 ppa.asset_number,
3291 ppa.asset_name,
3292 ppa.asset_description,
3293 ppa.location_id,
3294 ppa.assigned_to_person_id,
3295 ppa.date_placed_in_service,
3296 ppa.asset_category_id,
3297 ppa.asset_key_ccid,
3298 ppa.book_type_code,
3299 ppa.asset_units,
3300 decode(ppa.depreciate_flag,'Y','YES','N','NO') depreciate_flag,
3301 ppa.depreciation_expense_ccid,
3302 decode(ppa.amortize_flag, 'Y','YES','N','NO') amortize_flag,
3303 ppa.amortize_flag single_char_amortize_flag,
3304 ppa.cost_adjustment_flag,
3305 ppa.capitalized_flag,
3306 ppa.reverse_flag,
3307 decode(nvl(ppa.new_master_flag,'N'),'Y','YES','N','NO') new_master_flag, -- Bug 5435551
3308 pal.project_asset_line_id,
3309 pal.project_asset_line_detail_id detail_id,
3310 pal.rev_proj_asset_line_id,
3311 pal.description,
3312 pal.task_id,
3313 pal.current_asset_cost,
3314 pal.gl_date,
3315 ppt.interface_complete_asset_flag,
3316 ppt.vendor_invoice_grouping_code,
3317 pal.invoice_number,
3318 pal.vendor_number,
3319 pal.po_vendor_id,
3320 pal.po_number,
3321 pal.invoice_date,
3322 pal.invoice_created_by,
3323 pal.invoice_updated_by,
3324 pal.invoice_id,
3325 pal.payables_batch_name,
3326 pal.ap_distribution_line_number,
3327 pal.invoice_distribution_id, -- R12 new
3328 ppa.parent_asset_id,
3329 ppa.manufacturer_name,
3330 ppa.model_number,
3331 ppa.serial_number,
3332 ppa.tag_number
3333 FROM
3334 pa_project_asset_lines pal,
3335 pa_project_assets ppa,
3336 pa_projects pp,
3337 pa_project_types ppt
3338 WHERE
3339 pal.project_asset_id = ppa.project_asset_id
3340 AND ppa.project_id = pp.project_id
3341 AND ppa.project_id = x_project_id
3342 AND pp.project_type = ppt.project_type
3343 AND pal.transfer_status_code = 'X'
3344 AND pal.line_type = 'C'
3345 ORDER BY --- These order by is very important
3346 DECODE(ppt.vendor_invoice_grouping_code,'N',ppa.project_asset_id||pal.ap_distribution_line_number,ppa.project_asset_id),
3347 pal.cip_ccid,
3348 pal.asset_cost_ccid;
3349
3350 assetlinerec selassetlines%ROWTYPE;
3351 -- R12 changes. Table pa_gl_interface is obsolete. This check is left for
3352 -- asset lines which are not yet interfaced to FA before uptake of R12.
3353 CURSOR un_gl_xferred_cdls (x_detail_id IN NUMBER) IS
3354 select 'X'
3355 from pa_project_asset_line_details d,
3356 pa_cost_distribution_lines_all cdl
3357 where d.PROJECT_ASSET_LINE_DETAIL_ID = x_detail_id and
3358 d.expenditure_item_id = cdl.expenditure_item_id and
3359 d.line_num = cdl.line_num and
3360 (
3361 cdl.transfer_status_code in ('P','R','X','T')
3362 /* -- R12 change..pa_gl_interface is obsolete.
3363 OR
3364 Exists ( Select 'X' From Pa_Gl_Interface GL
3365 Where GL.Reference26 = cdl.batch_name
3366 And Cdl.transfer_status_code = 'A'
3367 )
3368 */
3369 );
3370
3371
3372 un_gl_rec un_gl_xferred_cdls%ROWTYPE;
3373
3374 --Table used by to cache the results of queries
3375 --against the pa_cost_distribution_line table.
3376 Type NumTabType IS
3377 Table of Number
3378 Index by binary_integer;
3379
3380 detail_cdl_xfer_cache NumTabType;
3381
3382 x_rowcount NUMBER;
3383 x_rows_rejected NUMBER;
3384 l_asset_type VARCHAR2(1);
3385 curr_project_asset_id NUMBER;
3386 curr_asset_cost_ccid NUMBER;
3387 curr_cip_ccid NUMBER;
3388 curr_add_to_asset_id NUMBER;
3389 curr_add_to_asset_flag BOOLEAN;
3390
3391 num_asset_found_in_fa NUMBER;
3392 new_asset_flag BOOLEAN;
3393 line_okay_for_interface BOOLEAN;
3394 asset_okay_for_interface BOOLEAN;
3395 curr_new_master_flag VARCHAR2(3);
3396 fa_install_status VARCHAR2(1);
3397 fa_posted_count NUMBER;
3398 fa_date_placed_in_service DATE;
3399
3400 -- columns for FA_MASS_ADDITIONS
3401
3402 mass_addition_id NUMBER;
3403 parent_mass_addition_id NUMBER;
3404 posting_status fa_mass_additions.posting_status%TYPE;
3405 queue_name fa_mass_additions.queue_name%TYPE;
3406 create_merged_line BOOLEAN;
3407 merge_code VARCHAR2(2);
3408 merged_cost pa_project_asset_lines.current_asset_cost%type;
3409 req_flag varchar2(1);
3410 keynumber fa_system_controls.asset_key_flex_structure%type;
3411
3412 l_amortize_flag varchar2(10); -- bug 4508689
3413 l_depreciation_expense_ccid NUMBER;
3414
3415
3416 -- Fix for bug : 4878878
3417 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
3418
3419 BEGIN
3420
3421 begin
3422 Select ASSET_KEY_FLEX_STRUCTURE into keynumber
3423 from FA_SYSTEM_CONTROLS;
3424 exception when no_data_found then
3425 null;
3426 end;
3427 req_flag := check_required_segment(keynumber);
3428
3429 x_err_code := 0;
3430 x_err_stage := 'Interfacing project asset lines';
3431
3432 curr_project_asset_id := -1;
3433
3434
3435 FOR assetlinerec IN selassetlines LOOP
3436
3437 /* Automatic asset capitalization changes JPULTORAK 04-FEB-2003 */
3438 --Initialize this value to prepare for override of the ccid via client extension
3439 l_depreciation_expense_ccid := assetlinerec.depreciation_expense_ccid;
3440 /* End of Automatic asset capitalization changes */
3441
3442 /*dbms_output.put_line('Asset Id = ' ||
3443 to_char(assetlinerec.project_asset_id) || ' Line Id = ' ||
3444 to_char(assetlinerec.project_asset_line_id)); */
3445
3446 --this section of code checks that cip cost has been transferred
3447 --to GL for the asset line. It is not in a separate procedure
3448 --because placing it there kept causing a signal 11 error.
3449 /************************************************************/
3450 BEGIN
3451 --this will raise a no_data_found exception if the
3452 --detail id hasn't been checked and results cached before.
3453 --The cache is most useful when lines have been split
3454 IF detail_cdl_xfer_cache(assetlinerec.detail_id) = 1 THEN
3455 line_okay_for_interface := TRUE;
3456 ELSE
3457 line_okay_for_interface := FALSE;
3458 END IF;
3459 EXCEPTION
3460 WHEN no_data_found THEN
3461 open un_gl_xferred_cdls (assetlinerec.detail_id);
3462 fetch un_gl_xferred_cdls into un_gl_rec;
3463 IF un_gl_xferred_cdls%NOTFOUND THEN
3464 line_okay_for_interface := TRUE;
3465 detail_cdl_xfer_cache(assetlinerec.detail_id) := 1;
3466 ELSE
3467 line_okay_for_interface := FALSE;
3468 detail_cdl_xfer_cache(assetlinerec.detail_id) := 0;
3469 END IF;
3470 close un_gl_xferred_cdls;
3471 END;
3472
3473 IF (line_okay_for_interface = FALSE) then
3474 update_asset_lines
3475 (assetlinerec.project_asset_line_id,
3476 'CIP_NOT_XFERD_TO_GL',
3477 'R',
3478 NULL,
3479 x_err_stage,
3480 x_err_code);
3481
3482 goto next_line;
3483 END IF;
3484 /*************************************************************/
3485
3486
3487 create_merged_line := TRUE;
3488 IF ( curr_project_asset_id <> assetlinerec.project_asset_id OR (assetlinerec.vendor_invoice_grouping_code = 'N' and assetlinerec.invoice_id is not null)) THEN
3489
3490 -- new project_asset_id
3491 new_asset_flag := TRUE;
3492 curr_project_asset_id := assetlinerec.project_asset_id;
3493
3494 ELSE
3495 new_asset_flag := FALSE;
3496 END IF;
3497 IF (assetlinerec.vendor_invoice_grouping_code = 'N' and assetlinerec.invoice_id is not null) then
3498 create_merged_line := FALSE;
3499 end if;
3500
3501 IF ( assetlinerec.capitalized_flag = 'N' AND assetlinerec.reverse_flag = 'N'
3502 AND TRUNC(assetlinerec.date_placed_in_service) <=
3503 TRUNC(NVL(x_in_service_date_through,assetlinerec.date_placed_in_service))) THEN
3504
3505 l_asset_type := 'N';
3506
3507 ELSIF ( assetlinerec.capitalized_flag = 'Y' AND assetlinerec.reverse_flag = 'N'
3508 AND assetlinerec.rev_proj_asset_line_id is null
3509 AND TRUNC(assetlinerec.date_placed_in_service) <=
3510 TRUNC(NVL(x_in_service_date_through,assetlinerec.date_placed_in_service))) THEN
3511
3512 l_asset_type := 'O';
3513
3514 ELSIF (assetlinerec.capitalized_flag = 'Y' AND assetlinerec.rev_proj_asset_line_id is not null) THEN
3515
3516 l_asset_type := 'R';
3517
3518 END IF;
3519 ---------------------------------------
3520 If (l_asset_type = 'O' and assetlinerec.vendor_invoice_grouping_code = 'N' and assetlinerec.invoice_id is not null) then
3521 l_asset_type := 'N';
3522 END IF;
3523 ----------------------------------------
3524 IF (l_asset_type = 'N') THEN
3525
3526 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
3527
3528 -- Call procedure to derive/default Depreciation Expense CCID via Client Extension.
3529 -- This procedure is ONLY called if Book Type Code and Asset Category ID are populated.
3530 -- The procedure will either return the current depreciation expense ccid on assetlinerec,
3531 -- or it will return a new value if it has been successfully overridden by the client
3532 -- extension with a valid value. The project asset is updated with the new CCID value
3533 -- when a successful override occurs.
3534
3535 IF assetlinerec.book_type_code IS NOT NULL AND assetlinerec.asset_category_id IS NOT NULL THEN
3536
3537 get_depreciation_expense(x_project_asset_id => assetlinerec.project_asset_id, /* 3368494 */
3538 x_book_type_code => assetlinerec.book_type_code,
3539 x_asset_category_id => assetlinerec.asset_category_id,
3540 x_date_placed_in_service => assetlinerec.date_placed_in_service,
3541 x_in_deprn_expense_ccid => assetlinerec.depreciation_expense_ccid,
3542 x_out_deprn_expense_ccid => l_depreciation_expense_ccid,
3543 x_err_stage => x_err_stage,
3544 x_err_code => x_err_code);
3545
3546 IF G_debug_mode = 'Y' THEN
3547 pa_debug.debug('interface assets: ' || 'In CCID = '||assetlinerec.depreciation_expense_ccid ||
3548 ' Out CCID = ' || l_depreciation_expense_ccid);
3549 END IF;
3550
3551 --Downstream processing will now be based on l_depreciation_expense_ccid instead of
3552 --assetlinerec.depreciation_expense_ccid
3553
3554 END IF;
3555
3556 /* End of Automatic asset capitalization changes */
3557
3558
3559
3560 -- Process the new asset lines
3561
3562 IF (new_asset_flag = TRUE) THEN
3563
3564 -- perform the check for complete asset information
3565 asset_okay_for_interface := TRUE;
3566
3567 IF ( assetlinerec.book_type_code IS NULL
3568 OR
3569 assetlinerec.asset_category_id IS NULL
3570 OR
3571 assetlinerec.asset_units IS NULL
3572 OR
3573 assetlinerec.location_id IS NULL
3574 OR
3575 assetlinerec.depreciate_flag IS NULL
3576 OR
3577 (assetlinerec.asset_key_ccid is null and req_flag='Y')
3578 OR
3579 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
3580 -- replacing this line with l_depreciation_expense_ccid in order to process the override value
3581 -- assetlinerec.depreciation_expense_ccid IS NULL)
3582 l_depreciation_expense_ccid IS NULL)
3583 /* End of Automatic asset capitalization changes */
3584 THEN
3585
3586 posting_status := 'NEW';
3587 queue_name := 'NEW';
3588 IF (assetlinerec.interface_complete_asset_flag = 'Y') THEN
3589 asset_okay_for_interface := FALSE;
3590 END IF;
3591 ELSE
3592 posting_status := 'POST';
3593 queue_name := 'POST';
3594 END IF;
3595
3596 IF (asset_okay_for_interface) THEN
3597 -- also update the assets status as capitalized
3598 update_asset_capitalized_flag
3599 (curr_project_asset_id,
3600 'Y',
3601 x_err_stage,
3602 x_err_code);
3603
3604 -- Send a header line for the asset
3605 ------saima
3606 if create_merged_line = TRUE then
3607 merged_cost := 0;
3608 merge_code := 'MP';
3609 else
3610 merged_cost := assetlinerec.current_asset_cost;
3611 merge_code := NULL;
3612 end if;
3613 ------endsaima
3614
3615 create_fa_mass_additions
3616 (sysdate, --x_accounting_date,
3617 NULL, --x_add_to_asset_id,
3618 assetlinerec.amortize_flag,
3619 assetlinerec.asset_category_id,
3620 assetlinerec.asset_key_ccid,
3621 assetlinerec.asset_number,
3622 'CAPITALIZED', --x_asset_type,
3623 assetlinerec.assigned_to_person_id,
3624 assetlinerec.book_type_code,
3625 NULL, --x_create_batch_date,
3626 NULL, --x_create_batch_id,
3627 assetlinerec.date_placed_in_service,
3628 assetlinerec.depreciate_flag,
3629 assetlinerec.asset_description,
3630 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
3631 -- replacing this line with l_depreciation_expense_ccid in order to process the override value
3632 --assetlinerec.depreciation_expense_ccid,
3633 l_depreciation_expense_ccid,
3634 /* End of Automatic asset capitalization changes */
3635 'ORACLE PROJECTS', -- feeder_system_name
3636 merged_cost, -- x_fixed_assets_cost,
3637 assetlinerec.asset_units, -- x_fixed_assets_units,
3638 assetlinerec.location_id,
3639 mass_addition_id, -- x_mass_addition_id
3640 merge_code, -- x_merged_code,
3641 NULL, -- x_merge_prnt_mass_additions_id,
3642 NULL, -- x_new_master_flag,
3643 NULL, -- x_parent_mass_addition_id,
3644 assetlinerec.cip_ccid, -- payables_code_combination_id,
3645 merged_cost, -- x_payables_cost Bug 1709239
3646 assetlinerec.asset_units, -- x_payables_units,
3647 posting_status, -- x_posting_status,
3648 assetlinerec.project_asset_line_id,
3649 assetlinerec.project_id,
3650 queue_name, -- x_queue_name,
3651 NULL, -- x_split_code,
3652 merge_code, -- x_split_merged_code,
3653 NULL, -- x_split_prnt_mass_additions_id,
3654 assetlinerec.task_id,
3655 assetlinerec.invoice_number,
3656 assetlinerec.vendor_number,
3657 assetlinerec.po_vendor_id,
3658 assetlinerec.po_number,
3659 assetlinerec.invoice_date,
3660 assetlinerec.invoice_created_by,
3661 assetlinerec.invoice_updated_by,
3662 assetlinerec.invoice_id,
3663 assetlinerec.payables_batch_name,
3664 assetlinerec.ap_distribution_line_number,
3665 assetlinerec.invoice_distribution_id, -- R12 new
3666 assetlinerec.parent_asset_id,
3667 assetlinerec.manufacturer_name,
3668 assetlinerec.model_number,
3669 assetlinerec.serial_number,
3670 assetlinerec.tag_number,
3671 x_err_stage,
3672 x_err_code);
3673 parent_mass_addition_id := mass_addition_id;
3674
3675 END IF;
3676
3677 END IF;
3678
3679 --create_fa_mass_addition for new line
3680
3681 IF(asset_okay_for_interface and create_merged_line = TRUE) THEN
3682
3683 create_fa_mass_additions
3684 (sysdate, --x_accounting_date,
3685 NULL, --x_add_to_asset_id,
3686 assetlinerec.amortize_flag,
3687 assetlinerec.asset_category_id,
3688 assetlinerec.asset_key_ccid,
3689 NULL, --asset_number,
3690 'CAPITALIZED', --x_asset_type,
3691 assetlinerec.assigned_to_person_id,
3692 assetlinerec.book_type_code,
3693 NULL, --x_create_batch_date,
3694 NULL, --x_create_batch_id,
3695 assetlinerec.date_placed_in_service,
3696 assetlinerec.depreciate_flag,
3697 assetlinerec.description,
3698 l_depreciation_expense_ccid,
3699 'ORACLE PROJECTS', -- feeder_system_name
3700 assetlinerec.current_asset_cost, -- x_fixed_assets_cost,
3701 assetlinerec.asset_units, -- x_fixed_assets_units,
3702 assetlinerec.location_id,
3703 mass_addition_id, -- x_mass_addition_id
3704 'MC', -- x_merged_code,
3705 parent_mass_addition_id, -- x_merge_prnt_mass_additions_id,
3706 NULL, -- x_new_master_flag,
3707 parent_mass_addition_id, -- x_parent_mass_addition_id,
3708 assetlinerec.cip_ccid, -- payables_code_combination_id,
3709 assetlinerec.current_asset_cost, -- x_payables_cost,
3710 0, -- x_payables_units,
3711 'MERGED', -- x_posting_status,
3712 assetlinerec.project_asset_line_id,
3713 assetlinerec.project_id,
3714 'NEW', -- x_queue_name,
3715 NULL, -- x_split_code,
3716 'MC', -- x_split_merged_code,
3717 NULL, -- x_split_prnt_mass_additions_id,
3718 assetlinerec.task_id,
3719 assetlinerec.invoice_number,
3720 assetlinerec.vendor_number,
3721 assetlinerec.po_vendor_id,
3722 assetlinerec.po_number,
3723 assetlinerec.invoice_date,
3724 assetlinerec.invoice_created_by,
3725 assetlinerec.invoice_updated_by,
3726 assetlinerec.invoice_id,
3727 assetlinerec.payables_batch_name,
3728 assetlinerec.ap_distribution_line_number,
3729 assetlinerec.invoice_distribution_id, -- R12 new
3730 assetlinerec.parent_asset_id,
3731 assetlinerec.manufacturer_name,
3732 assetlinerec.model_number,
3733 assetlinerec.serial_number,
3734 assetlinerec.tag_number,
3735 /* End of Automatic asset capitalization changes */
3736 x_err_stage,
3737 x_err_code);
3738 END IF;
3739
3740 -- complete information about the Asset could not be found in PA
3741 -- reject the line
3742 IF (not(asset_okay_for_interface)) then
3743 update_asset_lines
3744 (assetlinerec.project_asset_line_id,
3745 'ASSET_NOT_COMPLETE',
3746 'R',
3747 NULL,
3748 x_err_stage,
3749 x_err_code);
3750
3751 goto next_line;
3752
3753 END IF;
3754
3755 ELSIF (l_asset_type IN ('O','R') OR x_asset_type = 'R') THEN
3756
3757 -- Process the adjustments
3758 IF (new_asset_flag = TRUE) THEN
3759
3760 l_amortize_flag := assetlinerec.amortize_flag; -- bug 4508689
3761 fa_date_placed_in_service := assetlinerec.date_placed_in_service;
3762 --obtain fa installation status
3763 fa_install_status := pa_asset_utils.fa_implementation_status;
3764 IF fa_install_status = 'N' THEN
3765 curr_add_to_asset_id := NULL;
3766 BEGIN
3767 SELECT '1'
3768 INTO fa_posted_count
3769 FROM DUAL
3770 WHERE EXISTS (SELECT mass_addition_id
3771 FROM fa_mass_additions
3772 WHERE asset_number = assetlinerec.asset_number AND
3773 posting_status = 'POSTED');
3774 asset_okay_for_interface := TRUE;
3775 EXCEPTION
3776 WHEN NO_DATA_FOUND THEN
3777 asset_okay_for_interface := FALSE;
3778 END;
3779 ELSE
3780 -- find out the asset_id in FA for the this asset_id
3781 check_asset_id_in_FA
3782 (curr_project_asset_id,
3783 curr_add_to_asset_id,
3784 num_asset_found_in_fa,
3785 assetlinerec.book_type_code,
3786 fa_date_placed_in_service,
3787 x_err_stage,
3788 x_err_code);
3789
3790 IF (num_asset_found_in_fa = 0 ) THEN
3791 asset_okay_for_interface := FALSE;
3792 ELSIF(num_asset_found_in_fa = 1) AND (fa_date_placed_in_service IS NULL) THEN /*Bug#1701857*/
3793 asset_okay_for_interface := FALSE; /*Bug#1701857*/
3794 ELSE
3795 asset_okay_for_interface := TRUE;
3796 END IF;
3797 END IF;
3798
3799 IF ( asset_okay_for_interface ) THEN
3800
3801 -- By default the adjustments will be placed in the NEW queue
3802
3803 posting_status := 'NEW';
3804 queue_name := 'NEW';
3805
3806 -- Also check if this asset is eligible for taking
3807 -- cost adjustment
3808 IF ( curr_add_to_asset_id IS NOT NULL ) THEN
3809
3810 IF(fa_mass_add_validate.can_add_to_asset
3811 (curr_add_to_asset_id,assetlinerec.book_type_code) = 0 ) THEN
3812
3813 asset_okay_for_interface := FALSE;
3814 curr_add_to_asset_flag := FALSE;
3815 ELSE
3816 curr_add_to_asset_flag := TRUE;
3817 asset_okay_for_interface := TRUE;
3818 -- Put the adjustments in the COST ADJUSTMENT queue
3819 posting_status := 'POST';
3820 queue_name := 'ADD TO ASSET';
3821 END IF;
3822
3823 /* Added code for Bug#3552809 -- Begin */
3824 /* If asset in FA cannot be expensed and it allows amortization of adjustments
3825 then set the amortize_flag to 'Y' even if the corresponding asset in
3826 PA does not have 'amortize adjustments' checked. Else pass the value what
3827 ever is set at the asset level in PA */
3828 IF NOT FA_ASSET_VAL_PVT.validate_exp_after_amort(curr_add_to_asset_id,
3829 assetlinerec.book_type_code) THEN
3830 pa_debug.debug('Asset in FA cannot be expensed '||curr_add_to_asset_id);
3831 assetlinerec.amortize_flag := 'YES';
3832 l_amortize_flag := 'YES'; -- bug 4508689
3833 END IF;
3834 /* Added code for Bug#3552809 -- End */
3835
3836 END IF;
3837
3838 END IF;
3839
3840 curr_new_master_flag := 'YES';
3841
3842 IF (asset_okay_for_interface ) THEN
3843
3844 update_asset_adjustment_flag
3845 (curr_project_asset_id,
3846 'Y',
3847 x_asset_type,
3848 x_err_stage,
3849 x_err_code);
3850
3851 END IF;
3852
3853 /* Bug #4508689: Added the following ELSE and stamping the amortize flag on other lines
3854 also even if the new_asset_flag is FALSE.
3855 Derivation of the amortize flag is being done when new_asset_flag is TRUE as this needs
3856 to be done only once for an asset. But we will have to stamp it on all the Cost Adjusting
3857 Lines which are being interfaced for this Asset. */
3858
3859 ELSE
3860
3861 /* Also check if this asset is eligible for taking cost adjustment */
3862 IF ( curr_add_to_asset_id IS NOT NULL ) THEN
3863 assetlinerec.amortize_flag := l_amortize_flag;
3864 END IF;
3865
3866 END IF;
3867
3868 IF (asset_okay_for_interface) THEN
3869
3870 -- Send the new master flag only on one line per asset
3871
3872 IF (NOT(new_asset_flag AND assetlinerec.new_master_flag = 'YES'))
3873 THEN
3874 curr_new_master_flag := 'NO';
3875 END IF;
3876
3877 -- Send this project asset line to FA as an adjustment
3878 create_fa_mass_additions
3879 (sysdate, --x_accounting_date,
3880 curr_add_to_asset_id, --x_add_to_asset_id,
3881 assetlinerec.amortize_flag,
3882 assetlinerec.asset_category_id,
3883 assetlinerec.asset_key_ccid,
3884 NULL, --assetlinerec.asset_number,
3885 'CAPITALIZED', --x_asset_type,
3886 assetlinerec.assigned_to_person_id,
3887 assetlinerec.book_type_code,
3888 NULL, --x_create_batch_date,
3889 NULL, --x_create_batch_id,
3890 fa_date_placed_in_service,
3891 assetlinerec.depreciate_flag,
3892 assetlinerec.description,
3893 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
3894 -- replacing this line with l_depreciation_expense_ccid in order to process the override value
3895 --assetlinerec.depreciation_expense_ccid,
3896 l_depreciation_expense_ccid,
3897 /* End of Automatic asset capitalization changes */
3898 'ORACLE PROJECTS', -- feeder_system_name
3899 assetlinerec.current_asset_cost, -- x_fixed_assets_cost,
3900 assetlinerec.asset_units, -- x_fixed_assets_units,
3901 assetlinerec.location_id,
3902 mass_addition_id, -- x_mass_addition_id
3903 NULL, -- x_merged_code,
3904 NULL, -- x_merge_prnt_mass_additions_id,
3905 curr_new_master_flag, -- x_new_master_flag,
3906 NULL, -- x_parent_mass_addition_id,
3907 assetlinerec.cip_ccid, -- payables_code_combination_id,
3908 assetlinerec.current_asset_cost, -- x_payables_cost,
3909 assetlinerec.asset_units, -- x_payables_units,
3910 posting_status, -- x_posting_status,
3911 assetlinerec.project_asset_line_id,
3912 assetlinerec.project_id,
3913 queue_name, -- x_queue_name,
3914 NULL, -- x_split_code,
3915 NULL, -- x_split_merged_code,
3916 NULL, -- x_split_prnt_mass_additions_id,
3917 assetlinerec.task_id,
3918 assetlinerec.invoice_number,
3919 assetlinerec.vendor_number,
3920 assetlinerec.po_vendor_id,
3921 assetlinerec.po_number,
3922 assetlinerec.invoice_date,
3923 assetlinerec.invoice_created_by,
3924 assetlinerec.invoice_updated_by,
3925 assetlinerec.invoice_id,
3926 assetlinerec.payables_batch_name,
3927 assetlinerec.ap_distribution_line_number,
3928 assetlinerec.invoice_distribution_id, -- R12 new
3929 assetlinerec.parent_asset_id,
3930 assetlinerec.manufacturer_name,
3931 assetlinerec.model_number,
3932 assetlinerec.serial_number,
3933 assetlinerec.tag_number,
3934 x_err_stage,
3935 x_err_code);
3936
3937 ELSE
3938
3939 -- reject this line
3940 IF (num_asset_found_in_fa = 0 ) THEN
3941
3942 -- Asset could not be found in FA for the asset in PA
3943 update_asset_lines
3944 (assetlinerec.project_asset_line_id,
3945 'ASSET_NOT_POSTED',
3946 'R',
3947 NULL,
3948 x_err_stage,
3949 x_err_code);
3950 /*For Bug# 1701857:To raise exception when fa_date_placed_in_service is NULL)*/
3951 ELSIF(num_asset_found_in_fa =1) AND (fa_date_placed_in_service IS NULL) THEN
3952 update_asset_lines
3953 (assetlinerec.project_asset_line_id,
3954 'DEPRN_NOT_FOUND',
3955 'R',
3956 NULL,
3957 x_err_stage,
3958 x_err_code);
3959 /*Changes over for Bug# 1701857*/
3960 ELSIF ( NOT curr_add_to_asset_flag ) THEN
3961 -- this asset can not be adjusted any more in FA
3962 update_asset_lines
3963 (assetlinerec.project_asset_line_id,
3964 'ASSET_NOT_ADJUSTABLE',
3965 'R',
3966 NULL,
3967 x_err_stage,
3968 x_err_code);
3969
3970
3971 END IF;
3972
3973 goto next_line; -- skip to next line
3974
3975 END IF;
3976
3977 END IF;
3978
3979 --Put the code which is common for new and adjustment lines here
3980
3981 --Update the line as transferred
3982
3983 update_asset_lines
3984 (assetlinerec.project_asset_line_id,
3985 NULL,
3986 'T',
3987 assetlinerec.single_char_amortize_flag,
3988 x_err_stage,
3989 x_err_code);
3990
3991 -- Update the asset capitalized_cost
3992
3993 pa_faxface.update_asset_cost
3994 (assetlinerec.project_asset_id,
3995 0, --- grouped_cip_cost
3996 assetlinerec.current_asset_cost, --- capitalized_cost
3997 x_err_stage,
3998 x_err_code);
3999
4000 <<next_line>>
4001 NULL;
4002 END LOOP;
4003
4004
4005 EXCEPTION
4006 WHEN OTHERS THEN
4007 x_err_code := SQLCODE;
4008 RAISE;
4009 END interface_asset_lines;
4010
4011 -- The procedure given below is used for submitting the concurrent
4012 -- request. most of the validation for the various parameters are done
4013 -- at the time the parameters are entered.
4014
4015 -- project_num_from : start project # : mandatory
4016 -- project_num_to : end project # : mandatory
4017 -- x_in_service_date_through : optional
4018 -- x_common_tasks_flag : Y/N : mandatory
4019
4020 PROCEDURE summarize_proj
4021 ( errbuf IN OUT NOCOPY VARCHAR2,
4022 retcode IN OUT NOCOPY VARCHAR2,
4023 x_project_num_from IN VARCHAR2,
4024 x_project_num_to IN VARCHAR2,
4025 x_in_service_date_through IN DATE,
4026 x_common_tasks_flag IN VARCHAR2,
4027 x_pa_date IN DATE
4028 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4029 ,x_capital_event_id IN NUMBER DEFAULT NULL
4030 , x_debug_mode IN VARCHAR2 -- Fix for bug : 4878878
4031 /* End of Automatic asset capitalization changes */
4032 )
4033 IS
4034 L_current_org_id NUMBER := PA_MOAC_UTILS.get_current_org_id ;
4035 -- Declare cursor for Projects
4036
4037 CURSOR selprjs IS
4038 SELECT
4039 ppr.project_id,
4040 ppr.segment1,
4041 pt.capital_cost_type_code,
4042 pt.cip_grouping_method_code,
4043 pt.OVERRIDE_ASSET_ASSIGNMENT_FLAG,
4044 pt.VENDOR_INVOICE_GROUPING_CODE,
4045 NVL(pt.total_burden_flag, DECODE(pt.burden_amt_display_method,
4046 'S','Y',
4047 'D','N',
4048 'Y')) total_burden_flag,
4049 pt.Burden_amt_display_method
4050 FROM
4051 pa_projects ppr,
4052 pa_project_types pt
4053 WHERE
4054 ppr.segment1 between x_project_num_from and x_project_num_to and
4055 ppr.template_flag <> 'Y' and
4056 PA_PROJECT_UTILS.Check_prj_stus_action_allowed(ppr.project_status_code,
4057 'CAPITALIZE') = 'Y' and
4058 ppr.project_type = pt.project_type and
4059 pt.project_type_class_code = 'CAPITAL'
4060 AND NOT EXISTS (SELECT 'X' /* Modified the selprjs cursor for Bug 7128863 start */
4061 FROM PA_PROJECT_ASSET_ASSIGNMENTS PPAA1,
4062 PA_PROJECT_ASSET_ASSIGNMENTS PPAA2
4063 WHERE PPAA1.PROJECT_ID = PPR.PROJECT_ID
4064 AND PPAA1.PROJECT_ID = PPAA2.PROJECT_ID
4065 AND ((PPAA1.PROJECT_ID <> 0
4066 AND PPAA1.TASK_ID = 0)
4067 AND (PPAA2.PROJECT_ID <> 0
4068 AND PPAA2.TASK_ID <> 0))); /* Modified the selprjs cursor for Bug 7128863 end */
4069
4070
4071 /* removed this logic from this cursor and added function is_project_eligible for this logic
4072 Change done for bug 1280252.
4073 and ( exists (select 'x' -- project has costed,uncapitalized expenditure items
4074 from pa_cost_distribution_lines_all pcdl,
4075 pa_expenditure_items_all pei,
4076 pa_tasks pat
4077 where pcdl.expenditure_item_id = pei.expenditure_item_id
4078 and pei.revenue_distributed_flag||'' = 'N'
4079 and pei.cost_distributed_flag ='Y'
4080 and pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R','B','D','R')
4081 and pcdl.billable_flag = 'Y'
4082 and pei.task_id = pat.task_id
4083 and pat.project_id = ppr.project_id)
4084 or exists (select 'x' -- Untransferred asset lines exist
4085 from pa_project_asset_lines pal
4086 where pal.project_id = ppr.project_id
4087 and pal.rev_proj_asset_line_id IS NULL -- This line is not an adjustment
4088 and pal.transfer_status_code <> 'T')
4089 or exists ( select 'x' -- project has assets to be reverse capitalized
4090 from pa_project_assets ppa
4091 where ppa.project_id+0 = ppr.project_id
4092 and ppa.reverse_flag = 'Y'));
4093 */
4094
4095 projrec selprjs%ROWTYPE;
4096
4097
4098 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4099
4100 CURSOR unassigned_lines_cur(x_project_id NUMBER) IS
4101 SELECT pal.project_asset_line_id,
4102 pal.capital_event_id,
4103 pal.project_id,
4104 pal.task_id,
4105 NVL(pal.line_type,'C') line_type,
4106 p.segment1 project_number,
4107 pce.capital_event_number,
4108 pce.event_name,
4109 NVL(pce.asset_allocation_method, NVL(p.asset_allocation_method,'N')) asset_allocation_method,
4110 pal.asset_category_id /* Added for bug#3211946 */
4111 FROM pa_project_asset_lines_all pal,
4112 pa_projects p,
4113 pa_capital_events pce
4114 WHERE pal.project_id = p.project_id
4115 AND p.project_id = x_project_id
4116 AND pal.capital_event_id = NVL(x_capital_event_id, pal.capital_event_id)
4117 AND pal.capital_event_id = pce.capital_event_id (+)
4118 AND pal.project_asset_id = 0
4119 AND NVL(pce.asset_allocation_method, NVL(p.asset_allocation_method,'N')) <> 'N'
4120 ORDER BY pal.project_id, pal.capital_event_id, pal.task_id; --This order by is critical for cache purposes
4121
4122 unassigned_lines_rec unassigned_lines_cur%ROWTYPE;
4123
4124
4125 v_return_status VARCHAR2(1) := 'S';
4126 v_msg_count NUMBER := 0;
4127 v_msg_data VARCHAR2(2000):= NULL;
4128 v_asset_or_project_err VARCHAR2(1);
4129 v_error_code VARCHAR2(30);
4130 v_err_asset_id NUMBER;
4131 v_ret_cost_tasks_exist VARCHAR2(1) := 'N';
4132
4133 /* End of Automatic asset capitalization changes */
4134
4135
4136
4137 x_err_stage VARCHAR2(120);
4138 x_err_code NUMBER;
4139 x_capital_cost_type_code varchar2(1) ;
4140 x_projects_cnt number;
4141 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); Fix for bug : 4878878
4142
4143 l_ledger_id number;
4144
4145 BEGIN
4146 -- Validate Parameters
4147 x_err_code := 0;
4148 x_err_stage := 'Generate Project Asset Lines';
4149 -- Fix for bug : 4878878
4150 G_debug_mode := nvl(x_debug_mode,'N');
4151 IF G_debug_mode = 'Y' THEN
4152 pa_debug.enable_debug;
4153 End if;
4154 -- End of fix for bug : 4878878
4155
4156 IF (x_project_num_from IS NULL OR x_project_num_to IS NULL) THEN
4157
4158 errbuf := 'Project Numbers must be entered';
4159 retcode := 1;
4160 return;
4161 END IF;
4162
4163 -- assume the process does not return an error
4164 retcode :=0;
4165
4166
4167 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4168 --Add logic to set the Capital Event ID to -1 for assets and EIs on projects
4169 --with a Capital Event Processing method of 'None'. This logic is not executed
4170 --when run for a specific Event.
4171 /*bug 5642019 - this piece of code moved inside projrec loop.In case when PA_CLIENT_EXTN_ASSET_CREATION
4172 is used to create project assets procedure no_event_projects will fail to update
4173 pa_project_assets table since the client code has not been fired at this point.
4174 IF x_capital_event_id IS NULL THEN
4175
4176 PA_FAXFACE.NO_EVENT_PROJECTS
4177 ( x_project_num_from => x_project_num_from,
4178 x_project_num_to => x_project_num_to,
4179 x_in_service_date_through => x_in_service_date_through,
4180 x_err_stage => x_err_stage,
4181 x_err_code => x_err_code);
4182
4183 END IF;
4184 */
4185 /* End of Automatic asset capitalization changes */
4186
4187
4188 select set_of_books_id
4189 into l_ledger_id
4190 from pa_implementations;
4191
4192
4193 FOR projrec IN selprjs LOOP
4194
4195 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4196 IF G_debug_mode = 'Y' THEN
4197 pa_debug.debug('summarize_proj: ' || 'Calling CREATE_PROJECT_ASSETS Client Extension for project...'||projrec.project_id);
4198 END IF;
4199
4200 PA_CLIENT_EXTN_ASSET_CREATION.create_project_assets
4201 (p_project_id => projrec.project_id,
4202 p_asset_date_through => x_in_service_date_through,
4203 p_pa_date_through => x_pa_date,
4204 p_capital_event_id => x_capital_event_id,
4205 x_return_status => v_return_status,
4206 x_msg_data => v_msg_data);
4207
4208
4209 IF v_return_status = 'E' THEN
4210
4211 BEGIN
4212 IF G_debug_mode = 'Y' THEN
4213 pa_debug.debug('summarize_proj: ' || 'Error in CREATE_PROJECT_ASSETS Client Extension...'||v_return_status||' - '||v_msg_data);
4214 END IF;
4215
4216 INSERT INTO pa_capital_exceptions
4217 (request_id,
4218 module,
4219 record_type,
4220 project_id,
4221 error_code,
4222 created_by,
4223 creation_date)
4224 VALUES
4225 (x_request_id,
4226 'CAPITAL',
4227 'E',
4228 projrec.project_id,
4229 'C', --Asset Creation Extension Error
4230 x_created_by,
4231 fnd_date.date_to_canonical(sysdate));
4232
4233 EXCEPTION
4234 WHEN OTHERS THEN
4235 errbuf := SQLERRM;
4236 retcode := SQLCODE;
4237 ROLLBACK WORK;
4238 RAISE;
4239 END;
4240
4241 ELSIF v_return_status = 'U' THEN
4242
4243 IF G_debug_mode = 'Y' THEN
4244 pa_debug.debug('summarize_proj: ' || 'Unexpected Error in CREATE_PROJECT_ASSETS Client Extension...'||v_return_status||' - '||v_msg_data);
4245 END IF;
4246
4247 errbuf := v_msg_data;
4248 retcode := SQLCODE;
4249 ROLLBACK WORK;
4250 RETURN;
4251 END IF;
4252
4253 /* End of Automatic asset capitalization changes */
4254
4255 /*bug5758490*/
4256 IF x_capital_event_id IS NULL THEN
4257
4258 PA_FAXFACE.NO_EVENT_PROJECTS
4259 ( x_project_id => projrec.project_id,
4260 x_in_service_date_through => x_in_service_date_through,
4261 x_err_stage => x_err_stage,
4262 x_err_code => x_err_code);
4263
4264 END IF;
4265
4266 /* Added this if condition. Change done for bug 1280252 */
4267 IF (is_project_eligible(projrec.project_id
4268 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4269 ,x_capital_event_id
4270 /* End of Automatic asset capitalization changes*/
4271 )) then
4272
4273
4274 BEGIN
4275
4276 IF G_debug_mode = 'Y' THEN
4277 pa_debug.debug('summarize_proj: ' || 'Processing project id...'||projrec.project_id);
4278 END IF;
4279
4280 -- Acquire a lock before modifying project data
4281 IF G_debug_mode = 'Y' THEN
4282 pa_debug.debug('summarize_proj: ' || 'Lock the project: '||to_char(projrec.project_id));
4283 END IF;
4284 If pa_debug.Acquire_user_lock( 'PA_CAP_'||to_char(projrec.project_id))<>0 then
4285 IF G_debug_mode = 'Y' THEN
4286 pa_debug.debug('summarize_proj: ' || 'Could not lock the project: '||projrec.segment1);
4287 END IF;
4288 else
4289
4290 -- First reverse all the assets for this project
4291 IF G_debug_mode = 'Y' THEN
4292 pa_debug.debug('summarize_proj: ' || '. Generating reversing lines');
4293 END IF;
4294 reverse_asset_lines
4295 (projrec.project_id,
4296 x_capital_event_id,
4297 x_err_stage,
4298 x_err_code);
4299
4300 IF G_debug_mode = 'Y' THEN
4301 pa_debug.debug('summarize_proj: ' || '. Deleting untransferred lines');
4302 END IF;
4303 -- now delete all the asset lines which could be deleted
4304 delete_asset_lines
4305 (projrec.project_id,
4306 x_capital_event_id,
4307 x_err_stage,
4308 x_err_code);
4309 commit ; -- Introducing commit for perf. reasons. -sesivara 868857
4310 -- Now summarize the cdls and create asset lines
4311 IF G_debug_mode = 'Y' THEN
4312 pa_debug.debug('summarize_proj: ' || '. Generating new asset lines');
4313 END IF;
4314 if (projrec.capital_cost_type_code = 'B' and
4315 NVL(projrec.total_burden_flag,'N') = 'Y') THEN
4316 x_capital_cost_type_code := 'D' ;
4317 else
4318 x_capital_cost_type_code := 'R' ;
4319 end if ;
4320
4321 generate_proj_asset_lines
4322 (projrec.project_id,
4323 x_in_service_date_through,
4324 x_common_tasks_flag,
4325 x_pa_date,
4326 x_capital_cost_type_code,
4327 projrec.cip_grouping_method_code,
4328 projrec.OVERRIDE_ASSET_ASSIGNMENT_FLAG,
4329 projrec.VENDOR_INVOICE_GROUPING_CODE,
4330 x_capital_event_id,
4331 'C', --x_line_type for generating Capital lines
4332 l_ledger_id, -- R12 new
4333 x_err_stage,
4334 x_err_code);
4335
4336
4337 -- Now mark reversed lines to interface cost adjustments
4338 IF G_debug_mode = 'Y' THEN
4339 pa_debug.debug('summarize_proj: ' || 'Marking reversing lines');
4340 END IF;
4341 mark_reversing_lines(projrec.project_id,
4342 x_capital_event_id,
4343 'C', --x_line_type
4344 x_err_stage,
4345 x_err_code);
4346
4347 -- Now interface the cost adjustments for the reversed lines
4348 IF G_debug_mode = 'Y' THEN
4349 pa_debug.debug('summarize_proj: ' || '. Interfacing reversing lines');
4350 END IF;
4351 interface_asset_lines
4352 ( projrec.project_id,
4353 'R', -- Reversing lines
4354 x_in_service_date_through,
4355 'Y',
4356 x_err_stage,
4357 x_err_code);
4358
4359
4360 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4361 --This code will process Retirement Costs. It will only be executed if the project has
4362 --at least one Retirement Cost task.
4363
4364 -- Now summarize the cdls and create asset lines for Retirement Cost Adjustments
4365 IF G_debug_mode = 'Y' THEN
4366 pa_debug.debug('summarize_proj: ' || '. Determine if Retirement Cost Tasks exist');
4367 END IF;
4368
4369 v_ret_cost_tasks_exist := 'N';
4370
4371 BEGIN
4372 SELECT 'Y'
4373 INTO v_ret_cost_tasks_exist
4374 FROM sys.dual
4375 WHERE EXISTS
4376 (SELECT task_id
4377 FROM pa_tasks
4378 WHERE project_id = projrec.project_id
4379 AND retirement_cost_flag = 'Y');
4380
4381 EXCEPTION
4382 WHEN NO_DATA_FOUND THEN
4383 NULL;
4384 END;
4385
4386
4387 IF v_ret_cost_tasks_exist = 'Y' THEN
4388
4389 -- Now summarize the cdls and create asset lines for Retirement Cost Adjustments
4390 IF G_debug_mode = 'Y' THEN
4391 pa_debug.debug('summarize_proj: ' || '. Generating new Retirement Cost Adjustment asset lines');
4392 END IF;
4393
4394 GENERATE_PROJ_ASSET_LINES
4395 (projrec.project_id,
4396 x_in_service_date_through,
4397 x_common_tasks_flag,
4398 x_pa_date,
4399 x_capital_cost_type_code,
4400 projrec.cip_grouping_method_code,
4401 projrec.OVERRIDE_ASSET_ASSIGNMENT_FLAG,
4402 projrec.VENDOR_INVOICE_GROUPING_CODE,
4403 x_capital_event_id,
4404 'R', --x_line_type for generating Retirement Cost Adjustment lines
4405 l_ledger_id, -- R12 new
4406 x_err_stage,
4407 x_err_code);
4408
4409
4410 -- Now mark reversed lines to interface cost adjustments
4411 IF G_debug_mode = 'Y' THEN
4412 pa_debug.debug('summarize_proj: ' || 'Marking reversing retirement cost lines');
4413 END IF;
4414
4415 MARK_REVERSING_LINES
4416 (projrec.project_id,
4417 x_capital_event_id,
4418 'R', --x_line_type
4419 x_err_stage,
4420 x_err_code);
4421
4422 -- Now interface the cost adjustments for the reversed lines
4423 IF G_debug_mode = 'Y' THEN
4424 pa_debug.debug('summarize_proj: ' || '. Interfacing reversing retirement cost lines');
4425 END IF;
4426
4427 INTERFACE_RET_ASSET_LINES
4428 (projrec.project_id,
4429 x_err_stage,
4430 x_err_code);
4431
4432 END IF; --Retirement Cost Tasks exist for project
4433
4434 /* End of Automatic asset capitalization changes */
4435
4436
4437
4438 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4439
4440 x_err_stage := 'Allocate Unassigned Project Asset Lines';
4441 IF G_debug_mode = 'Y' THEN
4442 pa_debug.debug('summarize_proj: ' || '. Allocate Unassigned Asset Lines');
4443 END IF;
4444
4445 --Add logic to allocate any unassigned asset lines for the project being processed
4446 FOR unassigned_lines_rec IN unassigned_lines_cur(projrec.project_id) LOOP
4447
4448 PA_ASSET_ALLOCATION_PVT.ALLOCATE_UNASSIGNED
4449 (p_project_asset_line_id => unassigned_lines_rec.project_asset_line_id,
4450 p_line_type => unassigned_lines_rec.line_type,
4451 p_capital_event_id => unassigned_lines_rec.capital_event_id,
4452 p_project_id => unassigned_lines_rec.project_id,
4453 p_task_id => unassigned_lines_rec.task_id,
4454 p_asset_allocation_method => unassigned_lines_rec.asset_allocation_method,
4455 p_asset_category_id => unassigned_lines_rec.asset_category_id, /* Added for bug#3211946 */
4456 x_asset_or_project_err => v_asset_or_project_err,
4457 x_error_code => v_error_code,
4458 x_err_asset_id => v_err_asset_id,
4459 x_return_status => v_return_status,
4460 x_msg_count => v_msg_count,
4461 x_msg_data => v_msg_data);
4462
4463
4464 IF v_return_status = 'E' THEN
4465
4466 BEGIN
4467
4468 --Print warning message in control report
4469 INSERT INTO pa_reporting_exceptions
4470 (request_id,
4471 context,
4472 sub_context,
4473 module,
4474 record_type,
4475 org_id,
4476 attribute1, --project_id
4477 attribute2, --project_number
4478 attribute3, --task_id
4479 attribute4, --project_asset_line_id
4480 attribute5, --capital_event_id
4481 attribute6, --capital_event_number
4482 attribute7, --event_name
4483 attribute8, --asset_allocation_method
4484 attribute9, --asset_id
4485 attribute10, --error_code
4486 attribute20, --error message
4487 user_id,
4488 attribute_date1)
4489 VALUES
4490 (x_request_id,
4491 'PA_ASSET_ALLOCATION_PVT',
4492 v_asset_or_project_err,
4493 'ALLOCATE_UNASSIGNED',
4494 v_return_status,
4495 --NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1), ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99),
4496 L_current_org_id,
4497 unassigned_lines_rec.project_id,
4498 unassigned_lines_rec.project_number,
4499 unassigned_lines_rec.task_id,
4500 unassigned_lines_rec.project_asset_line_id,
4501 unassigned_lines_rec.capital_event_id,
4502 unassigned_lines_rec.capital_event_number,
4503 unassigned_lines_rec.event_name,
4504 unassigned_lines_rec.asset_allocation_method,
4505 v_err_asset_id,
4506 v_error_code,
4507 v_msg_data,
4508 x_created_by,
4509 -- fnd_date.date_to_canonical(sysdate));
4510 SYSDATE);
4511
4512 EXCEPTION
4513 WHEN OTHERS THEN
4514 errbuf := SQLERRM;
4515 retcode := SQLCODE;
4516 ROLLBACK WORK;
4517 RAISE;
4518 END;
4519
4520 ELSIF v_return_status = 'U' THEN
4521
4522
4523 IF G_debug_mode = 'Y' THEN
4524 pa_debug.debug('summarize_proj: ' || 'Unexpected Error in ALLOCATE_UNASSIGNED procedure...'||v_return_status||' - '||v_msg_data);
4525 END IF;
4526
4527 errbuf := v_msg_data;
4528 retcode := SQLCODE;
4529 ROLLBACK WORK;
4530 RETURN;
4531 END IF;
4532
4533 END LOOP;
4534
4535 x_err_stage := 'Asset Allocation Complete';
4536
4537 /* End of Automatic asset capitalization changes */
4538
4539
4540
4541
4542 COMMIT; -- we are done with this project now
4543
4544 -- Release the project lock
4545 IF G_debug_mode = 'Y' THEN
4546 pa_debug.debug('summarize_proj: ' || 'Unlock the project: '||to_char(projrec.project_id));
4547 END IF;
4548 If pa_debug.Release_user_lock('PA_CAP_'||to_char(projrec.project_id)) < 0 then
4549 errbuf := NVL(fnd_message.get_string('PA', 'PA_CAP_CANNOT_RELS_LOCK'),
4550 'PA_CAP_CANNOT_RELS_LOCK');
4551 retcode:=1;
4552 return;
4553 End if;
4554
4555 x_projects_cnt := x_projects_cnt +1 ; -- number of projects processed
4556 IF G_debug_mode = 'Y' THEN
4557 pa_debug.debug('summarize_proj: ' || 'Successfully processed project '||
4558 to_char(projrec.project_id));
4559 END IF;
4560 END if; -- End if for locking project
4561
4562 EXCEPTION
4563 WHEN OTHERS THEN
4564 x_err_code := SQLCODE;
4565 retcode := x_err_code;
4566 errbuf := SQLERRM(SQLCODE);
4567 IF G_debug_mode = 'Y' THEN
4568 pa_debug.debug('summarize_proj: ' || 'Exception Generated By Oracle Error:' || errbuf );
4569 pa_debug.debug('summarize_proj: ' || 'Skipping This project......');
4570 END IF;
4571 ROLLBACK WORK;
4572 END;
4573 END IF;
4574 END LOOP;
4575
4576
4577 return;
4578
4579 EXCEPTION
4580
4581 WHEN OTHERS THEN
4582 errbuf := SQLERRM(SQLCODE);
4583 retcode := SQLCODE;
4584 ROLLBACK WORK;
4585 return;
4586 END summarize_proj;
4587
4588 PROCEDURE interface_assets
4589 ( errbuf IN OUT NOCOPY VARCHAR2,
4590 retcode IN OUT NOCOPY VARCHAR2,
4591 x_project_num_from IN VARCHAR2,
4592 x_project_num_to IN VARCHAR2,
4593 x_in_service_date_through IN DATE
4594 )
4595 IS
4596
4597 -- Declare cursor for Projects
4598
4599 CURSOR selprjs IS
4600 SELECT distinct
4601 ppr.project_id,
4602 ppr.segment1
4603 FROM
4604 pa_projects ppr,
4605 pa_project_types pt,
4606 pa_project_asset_lines pal
4607 WHERE
4608 ppr.segment1 between x_project_num_from and x_project_num_to and
4609 ppr.template_flag <> 'Y' and
4610 PA_PROJECT_UTILS.Check_prj_stus_action_allowed(ppr.project_status_code,
4611 'CAPITALIZE') = 'Y' and
4612 ppr.project_type = pt.project_type and
4613 pt.project_type_class_code = 'CAPITAL' and
4614 pt.interface_asset_cost_code = 'F' and
4615 ppr.project_id = pal.project_id and
4616 pal.transfer_status_code||'' IN ('P','R')
4617 and (exists
4618 (SELECT 'x'
4619 FROM
4620 pa_project_assets ppa
4621 WHERE ppa.project_id = ppr.project_id
4622 AND ppa.reverse_flag||'' = 'N'
4623 AND TRUNC(ppa.date_placed_in_service) <=
4624 TRUNC(NVL(x_in_service_date_through,ppa.date_placed_in_service))
4625 AND ppa.capitalized_flag = 'N'
4626 AND pal.project_asset_id = ppa.project_asset_id
4627 )
4628 or exists
4629 (SELECT 'x'
4630 FROM
4631 pa_project_assets ppa
4632 WHERE ppa.project_id = ppr.project_id
4633 AND ppa.reverse_flag||'' = 'N'
4634 AND ppa.capitalized_flag ='Y'
4635 AND TRUNC(ppa.date_placed_in_service) <=
4636 TRUNC(NVL(x_in_service_date_through,ppa.date_placed_in_service))
4637 AND pal.rev_proj_asset_line_id is null
4638 AND pal.project_asset_id = ppa.project_asset_id
4639 )
4640 or exists
4641 (SELECT 'x'
4642 FROM
4643 pa_project_assets ppa
4644 WHERE ppa.project_id = ppr.project_id
4645 AND ppa.capitalized_flag = 'Y'
4646 AND pal.rev_proj_asset_line_id is not null
4647 AND pal.project_asset_id = ppa.project_asset_id
4648 )
4649 );
4650
4651
4652 projrec selprjs%ROWTYPE;
4653 x_err_stage VARCHAR2(120);
4654 x_err_code NUMBER;
4655 x_rowcount NUMBER;
4656 x_projects_cnt number;
4657
4658 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4659 x_ret_rowcount NUMBER;
4660 /* End of Automatic asset capitalization changes */
4661 -- Fix for bug : 4878878
4662 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4663
4664
4665 BEGIN
4666
4667 -- Validate Parameters
4668 x_err_code := 0;
4669 x_err_stage := 'Interface Project Asset Lines';
4670
4671 IF (x_project_num_from IS NULL OR x_project_num_to IS NULL) THEN
4672
4673 errbuf := 'Project Numbers must be entered';
4674 retcode := 1;
4675 return;
4676 END IF;
4677
4678 -- assume the process does not return an error
4679
4680 retcode :=0;
4681
4682 FOR projrec IN selprjs LOOP
4683
4684 BEGIN
4685 -- Now interface the asset lines to FA
4686
4687 -- Acquire a lock before modifying project data
4688 IF G_debug_mode = 'Y' THEN
4689 pa_debug.debug('interface_assets: ' || 'Lock the project: '||to_char(projrec.project_id));
4690 END IF;
4691 If pa_debug.Acquire_user_lock('PA_CAP_'||to_char(projrec.project_id))<>0 then
4692 IF G_debug_mode = 'Y' THEN
4693 pa_debug.debug('interface_assets: ' || 'Could not lock the project: '||projrec.segment1);
4694 END IF;
4695 ELSE
4696
4697 mark_asset_lines_for_xfer
4698 ( projrec.project_id,
4699 x_in_service_date_through,
4700 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4701 'C', --x_line_type
4702 /* End of Automatic asset capitalization changes */
4703 x_rowcount,
4704 x_err_stage,
4705 x_err_code);
4706
4707 --dbms_output.put_line('mark count is '||to_char(x_rowcount));
4708 IF ( x_rowcount = 0 ) THEN
4709 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4710 --Skip to processing Retirement Lines, instead of skipping to next project
4711 --goto next_proj; -- No asset lines are selected
4712 GOTO process_ret_lines;
4713 /* End of Automatic asset capitalization changes */
4714 END IF;
4715
4716 IF G_debug_mode = 'Y' THEN
4717 pa_debug.debug('interface_assets: ' || 'Lines Found = ' || to_char(x_rowcount));
4718 pa_debug.debug('interface_assets: ' || 'Processing Project '||to_char(projrec.project_id));
4719 pa_debug.debug('interface_assets: ' || 'Interfacing Lines');
4720 END IF;
4721
4722 interface_asset_lines
4723 ( projrec.project_id,
4724 NULL,
4725 x_in_service_date_through,
4726 NULL,
4727 x_err_stage,
4728 x_err_code);
4729
4730
4731 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4732 --Process Retirement Asset Lines
4733 <<process_ret_lines>>
4734 NULL;
4735
4736 mark_asset_lines_for_xfer
4737 ( projrec.project_id,
4738 x_in_service_date_through,
4739 'R', --x_line_type
4740 x_ret_rowcount,
4741 x_err_stage,
4742 x_err_code);
4743
4744 --dbms_output.put_line('mark count is '||to_char(x_rowcount));
4745 IF ( x_rowcount = 0 ) AND (x_ret_rowcount = 0) THEN
4746 GOTO next_proj; -- No asset lines are selected for entire project
4747 ELSIF ( x_rowcount <> 0 ) AND (x_ret_rowcount = 0) THEN
4748 GOTO skip_ret_lines; --No retirement asset lines are selected
4749 END IF;
4750
4751 IF G_debug_mode = 'Y' THEN
4752 pa_debug.debug('interface_assets: ' || 'Retirement Lines Found = ' || to_char(x_ret_rowcount));
4753 pa_debug.debug('interface_assets: ' || 'Processing Project '||to_char(projrec.project_id));
4754 pa_debug.debug('interface_assets: ' || 'Interfacing Retirement Lines');
4755 END IF;
4756
4757 interface_ret_asset_lines
4758 (projrec.project_id,
4759 x_err_stage,
4760 x_err_code);
4761
4762 <<skip_ret_lines>>
4763 NULL;
4764
4765 /* End of Automatic asset capitalization changes */
4766
4767
4768 COMMIT; -- we are done with this project now
4769 -- Release the project lock
4770 IF G_debug_mode = 'Y' THEN
4771 pa_debug.debug('interface_assets: ' || 'Unlock the project: '||to_char(projrec.project_id));
4772 END IF;
4773 If pa_debug.Release_user_lock('PA_CAP_'||to_char(projrec.project_id)) < 0 then
4774 errbuf := NVL(fnd_message.get_string('PA', 'PA_CAP_CANNOT_RELS_LOCK'),
4775 'PA_CAP_CANNOT_RELS_LOCK');
4776 retcode:=1;
4777 return;
4778 End if;
4779
4780 x_projects_cnt := x_projects_cnt +1 ;
4781 IF G_debug_mode = 'Y' THEN
4782 pa_debug.debug('interface_assets: ' || 'Successfully processed project '||
4783 to_char(projrec.project_id));
4784 END IF;
4785 END IF; -- End if for locking project
4786 EXCEPTION
4787 WHEN OTHERS THEN
4788 x_err_code := SQLCODE;
4789 retcode := x_err_code;
4790 errbuf := SQLERRM(SQLCODE);
4791 IF G_debug_mode = 'Y' THEN
4792 pa_debug.debug('interface_assets: ' || 'Exception Generated By Oracle Error:' || errbuf );
4793 pa_debug.debug('interface_assets: ' || 'Skipping This project......');
4794 END IF;
4795 ROLLBACK WORK;
4796 END;
4797 <<next_proj>>
4798 NULL;
4799 END LOOP;
4800
4801 return;
4802
4803 EXCEPTION
4804
4805 WHEN OTHERS THEN
4806 errbuf := SQLERRM(SQLCODE);
4807 retcode := SQLCODE;
4808 ROLLBACK WORK;
4809 return;
4810 END interface_assets;
4811
4812 -- process to summarize and interface
4813
4814 --- summarize_xface can be compiled, but will NOT WORK .
4815 --- This procedure is NOT called in any 11.0 code and hence not modified
4816 --- for performance improvements 10/16/97 lalmaula
4817
4818 PROCEDURE summarize_xface
4819 ( errbuf IN OUT NOCOPY VARCHAR2,
4820 retcode IN OUT NOCOPY VARCHAR2,
4821 x_project_num_from IN VARCHAR2,
4822 x_project_num_to IN VARCHAR2,
4823 x_in_service_date_through IN DATE ,
4824 x_pa_date IN DATE
4825 )
4826 IS
4827
4828 -- Declare cursor for Projects
4829
4830 CURSOR selprjs IS
4831 SELECT
4832 ppr.project_id
4833 FROM
4834 pa_projects ppr,
4835 pa_project_types pt
4836 WHERE
4837 ppr.segment1 between x_project_num_from and x_project_num_to and
4838 ppr.template_flag <> 'Y' and
4839 PA_PROJECT_UTILS.Check_prj_stus_action_allowed(ppr.project_status_code,
4840 'CAPITALIZE') = 'Y' and
4841 ppr.project_type = pt.project_type and
4842 pt.project_type_class_code = 'CAPITAL' and
4843 pt.interface_asset_cost_code = 'F';
4844
4845
4846 projrec selprjs%ROWTYPE;
4847 x_err_stage VARCHAR2(120);
4848 x_err_code NUMBER;
4849 -- Fix for bug : 4878878
4850 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4851 l_ledger_id NUMBER;
4852
4853 BEGIN
4854
4855 -- Validate Parameters
4856 x_err_code := 0;
4857 x_err_stage := 'Generate Project Asset Lines';
4858
4859 IF (x_project_num_from IS NULL OR x_project_num_to IS NULL) THEN
4860
4861 errbuf := 'Project Numbers must be entered';
4862 retcode := 1;
4863 return;
4864 END IF;
4865
4866 -- assume the process does not return an error
4867
4868 retcode :=0;
4869
4870 select set_of_books_id
4871 into l_ledger_id
4872 from pa_implementations;
4873
4874 FOR projrec IN selprjs LOOP
4875
4876 BEGIN
4877 -- First reverse all the assets for this project
4878
4879 reverse_asset_lines
4880 (projrec.project_id,
4881 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4882 NULL, --x_capital_event_id, *This line added just to allow SUMMARIZE_XFACE to compile
4883 /* End of Automatic asset capitalization changes */
4884 x_err_stage,
4885 x_err_code);
4886
4887 -- now delete all the asset lines which could be deleted
4888
4889 delete_asset_lines
4890 (projrec.project_id,
4891 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4892 NULL, --x_capital_event_id, *This line added just to allow SUMMARIZE_XFACE to compile
4893 /* End of Automatic asset capitalization changes */
4894 x_err_stage,
4895 x_err_code);
4896
4897 -- Now summarize the cdls and create asset lines
4898
4899 generate_proj_asset_lines
4900 (projrec.project_id,
4901 x_in_service_date_through,
4902 'N',
4903 x_pa_date,
4904 NULL,
4905 NULL,
4906 NULL,
4907 NULL,
4908 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4909 NULL, --x_capital_event_id, *This line added just to allow SUMMARIZE_XFACE to compile
4910 NULL, --x_line_type, *This line added just to allow SUMMARIZE_XFACE to compile
4911 /* End of Automatic asset capitalization changes */
4912 l_ledger_id, -- R12 new
4913 x_err_stage,
4914 x_err_code);
4915
4916 -- Now interface the cost adjustments for the reversed lines
4917
4918 interface_asset_lines
4919 ( projrec.project_id,
4920 'R', -- Reversed assets
4921 x_in_service_date_through,
4922 'Y',
4923 x_err_stage,
4924 x_err_code);
4925
4926 COMMIT; -- we are done with this project now
4927
4928 -- Now interface the new asset lines to FA
4929
4930 interface_asset_lines
4931 ( projrec.project_id,
4932 'N', -- New assets
4933 x_in_service_date_through,
4934 NULL,
4935 x_err_stage,
4936 x_err_code);
4937 COMMIT;
4938
4939 -- Now send the cost adjustments
4940
4941 interface_asset_lines
4942 ( projrec.project_id,
4943 'O', -- Old assets
4944 x_in_service_date_through,
4945 'N',
4946 x_err_stage,
4947 x_err_code);
4948
4949 COMMIT; -- we are done with this project now
4950 EXCEPTION
4951 WHEN OTHERS THEN
4952 x_err_code := SQLCODE;
4953 retcode := x_err_code;
4954 errbuf := SQLERRM(SQLCODE);
4955 IF G_debug_mode = 'Y' THEN
4956 pa_debug.debug('summarize_xface: ' || 'Exception Generated By Oracle Error:' || errbuf );
4957 pa_debug.debug('summarize_xface: ' || 'Skipping This project......');
4958 END IF;
4959 ROLLBACK WORK;
4960 END;
4961 END LOOP;
4962
4963 return;
4964
4965 EXCEPTION
4966
4967 WHEN OTHERS THEN
4968 errbuf := SQLERRM(SQLCODE);
4969 retcode := SQLCODE;
4970 ROLLBACK WORK;
4971 return;
4972 END summarize_xface;
4973
4974 /* Function is_project_eligible added for Bug 1280252 */
4975 FUNCTION is_project_eligible(p_project_id IN NUMBER
4976 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
4977 , p_capital_event_id IN NUMBER
4978 /* End of Automatic asset capitalization changes */
4979 ) RETURN BOOLEAN IS
4980 dummy number;
4981 v_errbuf varchar2(250);
4982 -- Fix for bug : 4878878
4983 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4984
4985 BEGIN
4986
4987 /* ********
4988 Commented the Select statement for Bug#2540426 and
4989 split this statement into three separate selects
4990
4991 SELECT 1 INTO dummy
4992 FROM dual
4993 WHERE EXISTS (SELECT 'x' -- project has costed,uncapitalized expenditure items
4994 FROM pa_cost_distribution_lines_all pcdl,
4995 pa_expenditure_items_all pei,
4996 pa_tasks pat,
4997 pa_projects pp,
4998 pa_project_types pt
4999 WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
5000 AND pp.project_id = p_project_id
5001 AND pp.project_type = pt.project_type
5002 AND pei.revenue_distributed_flag||'' = 'N'
5003 AND pei.cost_distributed_flag ='Y'
5004 AND pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
5005 'B',decode(pt.burden_amt_display_method,'S','D','R'),
5006 'R') -- * Added decode for bug 1309745*
5007 AND pcdl.billable_flag = 'Y'
5008 AND pei.task_id = pat.task_id
5009 AND pat.project_id = p_project_id)
5010
5011 OR EXISTS (SELECT 'x' -- Untransferred assetlines exist
5012 FROM pa_project_asset_lines pal
5013 WHERE pal.project_id = p_project_id
5014 AND pal.rev_proj_asset_line_id IS NULL -- This line is not an adjustment
5015 AND pal.transfer_status_code <> 'T')
5016 OR EXISTS (SELECT 'x' -- project has assets to be reverse capitalized
5017 FROM pa_project_assets ppa
5018 WHERE ppa.project_id+0 = p_project_id
5019 AND ppa.reverse_flag = 'Y');
5020 RETURN TRUE;
5021
5022 ****** Commented code for Bug2540426 Ends here */
5023 /*
5024 Added Code for Bug#2540426 to replace the above commented code for this bug
5025 If fisrt statement raises no_data_found exception then let second statement
5026 execute. Similarly, if the second statement raises this exception, let the
5027 third statement get executed.
5028 */
5029
5030 BEGIN
5031
5032 SELECT 1 INTO dummy
5033 FROM DUAL
5034 WHERE EXISTS (SELECT 'x' -- project has assets to be reverse capitalized
5035 FROM pa_project_assets ppa
5036 WHERE ppa.project_id+0 = p_project_id
5037 AND ppa.reverse_flag = 'Y'
5038 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
5039 AND capital_event_id = NVL(p_capital_event_id, capital_event_id)
5040 AND capital_event_id IS NOT NULL
5041 /* End of Automatic asset capitalization changes */
5042 );
5043
5044 RETURN TRUE;
5045
5046 EXCEPTION
5047 WHEN NO_DATA_FOUND THEN
5048 NULL;
5049 END;
5050
5051
5052 BEGIN
5053
5054 SELECT 1 INTO dummy
5055 FROM DUAL
5056 WHERE EXISTS (SELECT 'x' -- Untransferred assetlines exist
5057 FROM pa_project_asset_lines pal
5058 WHERE pal.project_id = p_project_id
5059 AND pal.rev_proj_asset_line_id IS NULL -- This line is not an adjustment
5060 AND pal.transfer_status_code <> 'T'
5061 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
5062 AND pal.capital_event_id = NVL(p_capital_event_id, pal.capital_event_id)
5063 /* End of Automatic asset capitalization changes */
5064 );
5065
5066 RETURN TRUE;
5067
5068 EXCEPTION
5069 WHEN NO_DATA_FOUND THEN
5070 NULL;
5071 END;
5072
5073
5074 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
5075 --Add Retirement Cost Processing check for costed, uncapitalized Retirement Cost Items
5076
5077 BEGIN
5078
5079 SELECT 1 INTO dummy
5080 FROM dual
5081 WHERE EXISTS (SELECT 'x' -- project has costed,uncapitalized Retirement Cost expenditure items
5082 FROM pa_cost_distribution_lines_all pcdl,
5083 pa_expenditure_items_all pei,
5084 pa_tasks pat,
5085 pa_projects pp,
5086 pa_project_types pt
5087 WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
5088 AND pp.project_id = p_project_id
5089 AND pp.project_type = pt.project_type
5090 AND pei.revenue_distributed_flag||'' = 'N'
5091 AND pei.cost_distributed_flag ='Y'
5092 /* PA.L Code change to allow 'R' or 'I' line types in cases when previously only 'R' was used. JPULTORAK 20-MAY-2003 */
5093 --In addition, PA.L allows 'D' lines to exist in all cases when Total Burden Flag is 'Y'
5094 --The following section was changed into an OR condition to allow 'R' or 'I' lines as introduced by PA.L
5095 --AND pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
5096 AND (pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
5097 /* Fix for Enable TBC Accounting Option JPULTORAK 14-FEB-2003 */
5098 -- We can only select 'D' CDLs when TBC Accounting is ENABLED and Burden Amt Display Method is 'S'
5099 -- 'B',decode(pt.burden_amt_display_method,'S','D','R'),
5100 -- 'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y',decode(pt.burden_amt_display_method,'S','D','R'),'R'),
5101 -- The line above was commented out since PA.L allows 'D' lines in all cases where Total Burden Flag is 'Y'
5102 'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y','D','R'),
5103 /* End of Fix for Enable TBC Accounting Option */
5104 'R') --Added decode for bug 1309745
5105 OR
5106 pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','I',
5107 'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y','D','I'),
5108 'I'))
5109 /* End of PA.L code change section */
5110 AND pcdl.billable_flag = 'N'
5111 AND pei.task_id = pat.task_id
5112 AND pat.project_id = pp.project_id --Bug 3057423 added to avoid merge join cartesian
5113 AND pat.project_id = p_project_id
5114 AND pei.capital_event_id = NVL(p_capital_event_id, pei.capital_event_id)
5115 AND pei.capital_event_id IS NOT NULL
5116 AND pat.retirement_cost_flag = 'Y');
5117
5118 RETURN TRUE;
5119
5120 EXCEPTION
5121 WHEN NO_DATA_FOUND THEN
5122 NULL;
5123 END;
5124
5125 /* End of Automatic asset capitalization changes */
5126
5127
5128 BEGIN
5129
5130 SELECT 1 INTO dummy
5131 FROM dual
5132 WHERE EXISTS (SELECT 'x' -- project has costed,uncapitalized expenditure items
5133 FROM pa_cost_distribution_lines_all pcdl,
5134 pa_expenditure_items_all pei,
5135 --pa_tasks pat, /* bug fix :2830211 task_id is not reqd */
5136 pa_projects_all pp,
5137 pa_project_types pt
5138 WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
5139 AND pp.project_id = p_project_id
5140 AND pei.project_id = pp.project_id /* added for bug fix :2830211 */
5141 AND pp.project_type = pt.project_type
5142 AND pei.revenue_distributed_flag||'' = 'N'
5143 AND pei.cost_distributed_flag ='Y'
5144 /* PA.L Code change to allow 'R' or 'I' line types in cases when previously only 'R' was used. JPULTORAK 20-MAY-2003 */
5145 --In addition, PA.L allows 'D' lines to exist in all cases when Total Burden Flag is 'Y'
5146 --The following section was changed into an OR condition to allow 'R' or 'I' lines as introduced by PA.L
5147 --AND pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
5148 AND (pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
5149 /* Fix for Enable TBC Accounting Option JPULTORAK 14-FEB-2003 */
5150 -- We can only select 'D' CDLs when TBC Accounting is ENABLED and Burden Amt Display Method is 'S'
5151 -- 'B',decode(pt.burden_amt_display_method,'S','D','R'),
5152 -- 'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y',decode(pt.burden_amt_display_method,'S','D','R'),'R'),
5153 -- The line above was commented out since PA.L allows 'D' lines in all cases where Total Burden Flag is 'Y'
5154 'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y','D','R'),
5155 /* End of Fix for Enable TBC Accounting Option */
5156 'R')/* Added decode for bug 1309745*/
5157 OR
5158 pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','I',
5159 'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y','D','I'),
5160 'I'))
5161 /* End of PA.L code change section */
5162 AND pcdl.billable_flag = 'Y'
5163 /* bug fix :2830211 */
5164 --AND pei.task_id = pat.task_id
5165 --AND pat.project_id = p_project_id
5166 --AND pat.project_id = pp.project_id missing join causing cartesion
5167 /* End of bug fix :2830211 */
5168 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
5169 AND pei.capital_event_id = NVL(p_capital_event_id, pei.capital_event_id)
5170 AND pei.capital_event_id IS NOT NULL
5171 /* End of Automatic asset capitalization changes */
5172 );
5173
5174 RETURN TRUE;
5175
5176 EXCEPTION
5177 WHEN NO_DATA_FOUND THEN
5178 RETURN FALSE; /* Return False only when there is no data for all the three selects */
5179 END;
5180
5181 /* Added code for Bug2540426 Ends here */
5182
5183 EXCEPTION
5184 WHEN OTHERS THEN
5185 v_errbuf := substr(SQLERRM(SQLCODE),1.200);
5186 IF G_debug_mode = 'Y' THEN
5187 pa_debug.debug('is_project_eligible: ' || 'Exception Generated By Oracle Error:' || v_errbuf );
5188 pa_debug.debug('is_project_eligible: ' || 'Skipping This project......');
5189 END IF;
5190 return FALSE;
5191 END is_project_eligible;
5192
5193
5194 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
5195 PROCEDURE get_depreciation_expense
5196 (x_project_asset_id IN NUMBER,
5197 x_book_type_code IN VARCHAR2,
5198 x_asset_category_id IN NUMBER,
5199 x_date_placed_in_service IN DATE,
5200 x_in_deprn_expense_ccid IN NUMBER,
5201 x_out_deprn_expense_ccid IN OUT NOCOPY NUMBER,
5202 x_err_stage IN OUT NOCOPY VARCHAR2,
5203 x_err_code IN OUT NOCOPY NUMBER
5204 ) IS
5205
5206
5207 l_new_deprn_expense_ccid NUMBER := 0;
5208
5209 /* Bug 3057423 Below cursor not used, instead see function Pa_Capital_Project_Utils.IsValidExpCCID
5210 --Used to determine if the Depreciation Expense CCID is valid for the current COA
5211 CURSOR deprn_expense_cur IS
5212 SELECT 'Deprn Expense Acct code combination is valid'
5213 FROM gl_code_combinations gcc,
5214 gl_sets_of_books gsob,
5215 pa_implementations pi
5216 WHERE gcc.code_combination_id = l_new_deprn_expense_ccid
5217 AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
5218 AND gsob.set_of_books_id = pi.set_of_books_id
5219 AND gcc.account_type = 'E';
5220
5221 deprn_expense_rec deprn_expense_cur%ROWTYPE;
5222 */
5223
5224 BEGIN
5225
5226 x_err_stage := 'get_depreciation_expense';
5227
5228 l_new_deprn_expense_ccid := PA_CLIENT_EXTN_DEPRN_EXP_OVR.DEPRN_EXPENSE_ACCT_OVERRIDE
5229 (p_project_asset_id => x_project_asset_id,
5230 p_book_type_code => x_book_type_code,
5231 p_asset_category_id => x_asset_category_id,
5232 p_date_placed_in_service => x_date_placed_in_service,
5233 p_deprn_expense_acct_ccid => x_in_deprn_expense_ccid);
5234
5235 IF NVL(x_in_deprn_expense_ccid,-999) <> NVL(l_new_deprn_expense_ccid,-999) THEN
5236
5237 --Return NULL if the client extension has returned a NULL CCID
5238 IF l_new_deprn_expense_ccid IS NULL THEN
5239 x_out_deprn_expense_ccid := NULL;
5240 RETURN;
5241 END IF;
5242
5243 --Validate the new ccid against the current Set of Books
5244
5245 /* Bug 3057423: Call cached function to check if CCID is valid expense account
5246 OPEN deprn_expense_cur;
5247 FETCH deprn_expense_cur INTO deprn_expense_rec;
5248
5249 IF deprn_expense_cur%NOTFOUND THEN
5250 --Value returned by client extension is invalid, return original CCID
5251 x_out_deprn_expense_ccid := x_in_deprn_expense_ccid;
5252 ELSE
5253 --Value is valid, return new CCID
5254 x_out_deprn_expense_ccid := l_new_deprn_expense_ccid;
5255 END IF;
5256
5257 CLOSE deprn_expense_cur;
5258 */
5259
5260 --Start Bug 3057423
5261 If Pa_Capital_Project_Utils.IsValidExpCCID(l_new_deprn_expense_ccid) = 'Y' Then
5262 --Value is valid, return new CCID
5263 x_out_deprn_expense_ccid := l_new_deprn_expense_ccid;
5264 Else
5265 --Value returned by client extension is invalid, return original CCID
5266 x_out_deprn_expense_ccid := x_in_deprn_expense_ccid;
5267 End If;
5268 --End Bug 3057423
5269
5270 --If CCID value has changed, update Project Asset row
5271 IF NVL(x_out_deprn_expense_ccid,-999) <> NVL(x_in_deprn_expense_ccid,-999) THEN
5272
5273 UPDATE pa_project_assets
5274 SET depreciation_expense_ccid = x_out_deprn_expense_ccid,
5275 last_update_date = SYSDATE,
5276 last_updated_by = x_last_updated_by,
5277 last_update_login = x_last_update_login,
5278 request_id = x_request_id,
5279 program_application_id = x_program_application_id,
5280 program_id = x_program_id,
5281 program_update_date = SYSDATE
5282 WHERE project_asset_id = x_project_asset_id;
5283
5284 END IF; --Value has changed, perform update
5285 ELSE
5286 x_out_deprn_expense_ccid := x_in_deprn_expense_ccid;
5287 END IF;
5288
5289
5290 EXCEPTION
5291 WHEN OTHERS THEN
5292 x_err_code := SQLCODE;
5293 RAISE;
5294 END get_depreciation_expense;
5295 /* End of Automatic asset capitalization changes */
5296
5297
5298 /* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
5299 -- Procedure for transferring retirement cost asset lines
5300
5301 PROCEDURE interface_ret_asset_lines
5302 ( x_project_id IN NUMBER,
5303 x_err_stage IN OUT NOCOPY VARCHAR2,
5304 x_err_code IN OUT NOCOPY NUMBER) IS
5305
5306
5307 CURSOR selassetlines IS
5308 SELECT
5309 ppa.project_id,
5310 ppa.project_asset_id,
5311 pal.cip_ccid,
5312 ppa.asset_number,
5313 ppa.asset_name,
5314 ppa.asset_description,
5315 ppa.date_placed_in_service,
5316 ppa.book_type_code,
5317 ppa.ret_target_asset_id,
5318 decode(ppa.amortize_flag, 'Y','YES','N','NO') amortize_flag,
5319 ppa.amortize_flag single_char_amortize_flag,
5320 ppa.cost_adjustment_flag,
5321 ppa.capitalized_flag,
5322 ppa.reverse_flag,
5323 decode(nvl(ppa.new_master_flag,'N'),'Y','YES','N','NO') new_master_flag, -- Bug 5435551
5324 pal.project_asset_line_id,
5325 pal.project_asset_line_detail_id detail_id,
5326 pal.rev_proj_asset_line_id,
5327 pal.description,
5328 pal.task_id,
5329 pal.current_asset_cost,
5330 pal.gl_date
5331 FROM
5332 pa_project_asset_lines pal,
5333 pa_project_assets ppa,
5334 pa_projects pp,
5335 pa_project_types ppt
5336 WHERE
5337 pal.project_asset_id = ppa.project_asset_id
5338 AND ppa.project_id = pp.project_id
5339 AND ppa.project_id = x_project_id
5340 AND pp.project_type = ppt.project_type
5341 AND pal.transfer_status_code = 'X'
5342 AND pal.line_type = 'R'
5343 ORDER BY ppa.project_asset_id;
5344
5345 assetlinerec selassetlines%ROWTYPE;
5346
5347
5348 -- R12 changes. Table pa_gl_interface is obsolete. This check is left for
5349 -- asset lines which are not yet interfaced to FA before uptake of R12.
5350 CURSOR un_gl_xferred_cdls (x_detail_id IN NUMBER) IS
5351 select 'X'
5352 from pa_project_asset_line_details d,
5353 pa_cost_distribution_lines_all cdl
5354 where d.PROJECT_ASSET_LINE_DETAIL_ID = x_detail_id and
5355 d.expenditure_item_id = cdl.expenditure_item_id and
5356 d.line_num = cdl.line_num and
5357 (
5358 cdl.transfer_status_code in ('P','R','X','T')
5359 /* -- R12 change..pa_gl_interface is obsolete.
5360 OR
5361 Exists ( Select 'X' From Pa_Gl_Interface GL
5362 Where GL.Reference26 = cdl.batch_name
5363 And Cdl.transfer_status_code = 'A'
5364 )
5365 */
5366 )
5367 ;
5368
5369 un_gl_rec un_gl_xferred_cdls%ROWTYPE;
5370
5371
5372 --Used to determine if the book type code is valid for the current SOB
5373 CURSOR book_type_code_cur (x_book_type_code VARCHAR2) IS
5374 SELECT fb.set_of_books_id
5375 FROM fa_book_controls fb,
5376 pa_implementations pi
5377 WHERE fb.set_of_books_id = pi.set_of_books_id
5378 AND fb.book_type_code = x_book_type_code;
5379
5380 book_type_code_rec book_type_code_cur%ROWTYPE;
5381
5382
5383 --Used to determine if the Ret Target Asset ID is a valid GROUP asset in the book
5384 CURSOR ret_target_cur (x_ret_target_asset_id NUMBER,
5385 x_book_type_code VARCHAR2) IS
5386 SELECT fa.asset_category_id
5387 FROM fa_books fb,
5388 fa_additions fa
5389 WHERE fa.asset_id = x_ret_target_asset_id
5390 AND fa.asset_type = 'GROUP'
5391 AND fa.asset_id = fb.asset_id
5392 AND fb.book_type_code = x_book_type_code
5393 AND fb.date_ineffective IS NULL;
5394
5395 ret_target_rec ret_target_cur%ROWTYPE;
5396
5397
5398 --Table used by to cache the results of queries
5399 --against the pa_cost_distribution_line table.
5400 Type NumTabType IS
5401 Table of Number
5402 Index by binary_integer;
5403
5404 detail_cdl_xfer_cache NumTabType;
5405
5406
5407 v_msg_data VARCHAR2(2000) := NULL;
5408 line_okay_for_interface BOOLEAN;
5409
5410
5411
5412
5413 BEGIN
5414
5415 x_err_code := 0;
5416 x_err_stage := 'Interfacing retirement cost asset lines';
5417
5418
5419 FOR assetlinerec IN selassetlines LOOP
5420
5421
5422 /*dbms_output.put_line('Asset Id = ' ||
5423 to_char(assetlinerec.project_asset_id) || ' Line Id = ' ||
5424 to_char(assetlinerec.project_asset_line_id)); */
5425
5426 --this section of code checks that cip cost has been transferred
5427 --to GL for the asset line. It is not in a separate procedure
5428 --because placing it there kept causing a signal 11 error.
5429 /************************************************************/
5430 BEGIN
5431 --this will raise a no_data_found exception if the
5432 --detail id hasn't been checked and results cached before.
5433 --The cache is most useful when lines have been split
5434
5435 IF detail_cdl_xfer_cache(assetlinerec.detail_id) = 1 THEN
5436 line_okay_for_interface := TRUE;
5437 ELSE
5438 line_okay_for_interface := FALSE;
5439 END IF;
5440
5441 EXCEPTION
5442 WHEN no_data_found THEN
5443 OPEN un_gl_xferred_cdls (assetlinerec.detail_id);
5444 FETCH un_gl_xferred_cdls into un_gl_rec;
5445 IF un_gl_xferred_cdls%NOTFOUND THEN
5446 line_okay_for_interface := TRUE;
5447 detail_cdl_xfer_cache(assetlinerec.detail_id) := 1;
5448 ELSE
5449 line_okay_for_interface := FALSE;
5450 detail_cdl_xfer_cache(assetlinerec.detail_id) := 0;
5451 END IF;
5452 CLOSE un_gl_xferred_cdls;
5453 END;
5454
5455
5456 IF (line_okay_for_interface = FALSE) THEN
5457
5458 update_asset_lines
5459 (assetlinerec.project_asset_line_id,
5460 'CIP_NOT_XFERD_TO_GL',
5461 'R',
5462 NULL,
5463 x_err_stage,
5464 x_err_code);
5465
5466 GOTO next_line;
5467 END IF;
5468 /*************************************************************/
5469
5470
5471
5472 IF assetlinerec.book_type_code IS NULL OR assetlinerec.ret_target_asset_id IS NULL THEN
5473
5474 --Required information (Book Type Code, Retirement Target Asset ID) not found in PA
5475 update_asset_lines
5476 (assetlinerec.project_asset_line_id,
5477 'TARGET_NOT_COMPLETE',
5478 'R',
5479 NULL,
5480 x_err_stage,
5481 x_err_code);
5482
5483 GOTO next_line;
5484
5485 ELSE
5486
5487 --Validate Book Type Code is valid for the current SOB
5488 OPEN book_type_code_cur(assetlinerec.book_type_code);
5489 FETCH book_type_code_cur INTO book_type_code_rec;
5490 IF book_type_code_cur%NOTFOUND THEN
5491
5492 CLOSE book_type_code_cur;
5493 -- The book_type_code is not valid. Return error
5494 update_asset_lines
5495 (assetlinerec.project_asset_line_id,
5496 'TARGET_NOT_COMPLETE',
5497 'R',
5498 NULL,
5499 x_err_stage,
5500 x_err_code);
5501
5502 GOTO next_line;
5503
5504 END IF;
5505 CLOSE book_type_code_cur;
5506
5507
5508 --Validate that Ret Target Asset ID is a valid Group Asset for the Book
5509 OPEN ret_target_cur(assetlinerec.ret_target_asset_id, assetlinerec.book_type_code);
5510 FETCH ret_target_cur INTO ret_target_rec;
5511 IF ret_target_cur%NOTFOUND THEN
5512
5513 CLOSE ret_target_cur;
5514 -- The ret_target_asset_id is not valid. Reject Line with "Asset Not Adjustable" message
5515 update_asset_lines
5516 (assetlinerec.project_asset_line_id,
5517 'TARGET_NOT_ADJUSTABLE',
5518 'R',
5519 NULL,
5520 x_err_stage,
5521 x_err_code);
5522
5523 GOTO next_line;
5524
5525 END IF;
5526 CLOSE ret_target_cur;
5527
5528
5529 --Call procedure to execute the API call for Interface to Oracle Assets
5530 PA_FAXFACE_RET_PVT.INTERFACE_RET_COST_ADJ_LINE
5531 (x_project_asset_line_id => assetlinerec.project_asset_line_id,
5532 x_msg_data => v_msg_data,
5533 x_err_stage => x_err_stage,
5534 x_err_code => x_err_code);
5535
5536
5537 IF x_err_code = 0 THEN
5538
5539 /*bug 4401557*/
5540 -- Update the assets status as capitalized
5541 update_asset_capitalized_flag
5542 (assetlinerec.project_asset_id,
5543 'Y',
5544 x_err_stage,
5545 x_err_code);
5546 /*Bug 4401557*/
5547
5548 --Update the line as transferred
5549 update_asset_lines
5550 (assetlinerec.project_asset_line_id,
5551 NULL,
5552 'T',
5553 assetlinerec.single_char_amortize_flag,
5554 x_err_stage,
5555 x_err_code);
5556
5557 -- Update the asset capitalized_cost
5558 pa_faxface.update_asset_cost
5559 (assetlinerec.project_asset_id,
5560 0, --- grouped_cip_cost
5561 assetlinerec.current_asset_cost, --- capitalized_cost
5562 x_err_stage,
5563 x_err_code);
5564
5565 ELSE
5566
5567 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in PA_FAXFACE_RET_PVT.interface_ret_cost_adj_line for asset line: '||assetlinerec.project_asset_line_id);
5568 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Code: '||x_err_code||' Error Stage:'||x_err_stage);
5569 FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Data: '||v_msg_data);
5570
5571 -- Reject Line with message returned by API
5572 update_asset_lines
5573 (assetlinerec.project_asset_line_id,
5574 'ERROR_IN_GROUP_RET_ADJ', --v_msg_data,
5575 'R',
5576 NULL,
5577 x_err_stage,
5578 x_err_code);
5579 END IF;
5580 END IF;
5581
5582 <<next_line>>
5583 NULL;
5584 END LOOP;
5585
5586
5587 EXCEPTION
5588 WHEN OTHERS THEN
5589 x_err_code := SQLCODE;
5590 RAISE;
5591 END interface_ret_asset_lines;
5592
5593
5594 PROCEDURE no_event_projects
5595 ( x_project_id IN number, /*bug5758490*/
5596 x_in_service_date_through IN DATE,
5597 x_err_stage IN OUT NOCOPY VARCHAR2,
5598 x_err_code IN OUT NOCOPY NUMBER) IS
5599
5600 /* Commented for bug 5758490
5601 CURSOR no_event_proj_cur IS
5602 SELECT p.project_id
5603 FROM pa_projects p,
5604 pa_project_types pt
5605 WHERE p.segment1
5606 BETWEEN x_project_num_from AND x_project_num_to
5607 AND p.template_flag <> 'Y'
5608 AND PA_PROJECT_UTILS.Check_prj_stus_action_allowed(p.project_status_code,'CAPITALIZE') = 'Y'
5609 AND p.project_type = pt.project_type
5610 AND pt.project_type_class_code = 'CAPITAL'
5611 AND NVL(p.capital_event_processing,'N') = 'N';
5612
5613 no_event_proj_rec no_event_proj_cur%ROWTYPE;*/
5614
5615 -- Fix for bug : 4878878
5616 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5617 is_no_event_proj VARCHAR2(1) := 'N'; -- Added for bug 5758490
5618 BEGIN
5619
5620 x_err_code := 0;
5621 x_err_stage := 'Updating assets and events for No Event Processing projects';
5622
5623
5624 IF G_debug_mode = 'Y' THEN
5625 pa_debug.debug('summarize_proj: ' || 'Set Capital Event ID = -1 for No Event Processing projects.');
5626 END IF;
5627
5628 /* Commented for bug 5758490
5629 FOR no_event_proj_rec IN no_event_proj_cur LOOP */
5630
5631 begin
5632 select 'Y' into is_no_event_proj
5633 from pa_projects_all p
5634 where p.project_id = x_project_id
5635 and NVL(p.capital_event_processing,'N') = 'N';
5636 exception -- Added exception handling for bug 5909337
5637 when no_data_found then
5638 is_no_event_proj := 'N';
5639 end;
5640
5641 IF is_no_event_proj = 'Y' then
5642
5643 --Update all project assets that have a DPIS (AS-BUILT or RETIREMENT_ADJUSTMENT)
5644 UPDATE pa_project_assets_all
5645 SET capital_event_id = -1,
5646 last_update_date = SYSDATE,
5647 last_updated_by = x_last_updated_by,
5648 last_update_login = x_last_update_login,
5649 request_id = x_request_id,
5650 program_application_id = x_program_application_id,
5651 program_id = x_program_id,
5652 program_update_date = SYSDATE
5653 WHERE project_id = x_project_id /*Bug 5758490*/
5654 AND project_asset_type IN ('AS-BUILT','RETIREMENT_ADJUSTMENT')
5655 AND date_placed_in_service IS NOT NULL
5656 AND date_placed_in_service <= x_in_service_date_through
5657 AND capital_event_id IS NULL
5658 AND capital_hold_flag = 'N';
5659
5660 --Update all capital Expenditure Items
5661 UPDATE pa_expenditure_items_all
5662 SET capital_event_id = -1,
5663 last_update_date = SYSDATE,
5664 last_updated_by = x_last_updated_by,
5665 last_update_login = x_last_update_login,
5666 request_id = x_request_id,
5667 program_application_id = x_program_application_id,
5668 program_id = x_program_id,
5669 program_update_date = SYSDATE
5670 WHERE project_id = x_project_id /*Bug 5758490*/
5671 AND billable_flag||'' = 'Y'
5672 AND capital_event_id IS NULL;
5673
5674 --Update all retirement cost Expenditure Items
5675 UPDATE pa_expenditure_items_all peia
5676 SET capital_event_id = -1,
5677 last_update_date = SYSDATE,
5678 last_updated_by = x_last_updated_by,
5679 last_update_login = x_last_update_login,
5680 request_id = x_request_id,
5681 program_application_id = x_program_application_id,
5682 program_id = x_program_id,
5683 program_update_date = SYSDATE
5684 WHERE project_id = x_project_id /*Bug 5758490*/
5685 AND billable_flag||'' = 'N'
5686 AND capital_event_id IS NULL
5687 AND EXISTS
5688 (SELECT t.task_id
5689 FROM pa_tasks t
5690 WHERE t.task_id = peia.task_id
5691 AND t.retirement_cost_flag = 'Y');
5692
5693 COMMIT;
5694 END IF; /*IF is_no_event_proj*/
5695
5696 /* END LOOP; bug 5758490*/
5697
5698
5699 EXCEPTION
5700 WHEN NO_DATA_FOUND THEN /* Added for bug:7319246*/
5701 NULL;
5702 WHEN OTHERS THEN
5703 x_err_code := SQLCODE;
5704 RAISE;
5705 END no_event_projects;
5706
5707
5708 /* End of Automatic asset capitalization changes */
5709
5710 ----------------------------------------------------------------------------------------------------------
5711
5712 PROCEDURE create_alc_asset_line_details (x_proj_asset_line_dtl_uniq_id IN NUMBER,
5713 x_expenditure_item_id IN NUMBER,
5714 x_line_num IN NUMBER,
5715 x_project_asset_line_detail_id IN NUMBER,
5716 x_err_stage IN OUT NOCOPY VARCHAR2,
5717 x_err_code IN OUT NOCOPY NUMBER)
5718 IS
5719
5720 CURSOR c_alc_ledger IS
5721 SELECT gl.ledger_id, gl.currency_code, imp.set_of_books_id, imp.org_id, gld.sla_ledger_cash_basis_flag
5722 FROM gl_alc_ledger_rships_v gl,
5723 pa_implementations imp,
5724 gl_ledgers gld
5725 WHERE gl.source_ledger_id = imp.set_of_books_id
5726 AND gl.application_id = 275
5727 AND gl.org_id = imp.org_id
5728 AND gld.ledger_id = imp.set_of_books_id
5729 AND gl.relationship_enabled_flag = 'Y';
5730
5731 l_exchange_rate_type VARCHAR2(30);
5732 l_exchange_rate NUMBER :=0;
5733 l_numerator_rate NUMBER;
5734 l_denominator_rate NUMBER;
5735 l_result_code VARCHAR2(30);
5736
5737 l_line_type VARCHAR2(1);
5738 l_transfer_status_code VARCHAR2(1);
5739 l_acct_event_id NUMBER;
5740 l_denom_raw_cost NUMBER;
5741 l_acct_source_code VARCHAR2(30);
5742 l_transaction_source VARCHAR2(30);
5743 l_gl_date DATE;
5744 l_denom_currency_code VARCHAR2(30);
5745 l_predefined_flag VARCHAR2(1);
5746 l_cip_cost NUMBER;
5747 l_system_reference5 NUMBER;
5748 l_document_distribution_id NUMBER;
5749 l_document_payment_id NUMBER;
5750 l_historical_flag VARCHAR2(1);
5751 l_capital_cost_type VARCHAR2(1);
5752 l_alc_exists VARCHAR2(1);
5753
5754 begin
5755
5756 x_err_code := 0;
5757 x_err_stage := 'Create ALC Project Asset Line details';
5758
5759 SELECT cdl.line_type, cdl.transfer_status_code, cdl.acct_event_id, cdl.denom_raw_cost, cdl.acct_rate_type, cdl.acct_exchange_rate,
5760 pts.acct_source_code, pts.transaction_source, cdl.gl_date, cdl.denom_currency_code, nvl(ei.historical_flag, 'Y'),
5761 nvl(pts.predefined_flag,'Y'), cdl.system_reference5, ei.document_distribution_id, ei.document_payment_id
5762 INTO l_line_type, l_transfer_status_code, l_acct_event_id, l_denom_raw_cost, l_exchange_rate_type, l_exchange_rate,
5763 l_acct_source_code, l_transaction_source, l_gl_date, l_denom_currency_code, l_historical_flag,
5764 l_predefined_flag , l_system_reference5, l_document_distribution_id, l_document_payment_id
5765 FROM pa_expenditure_items_all ei,
5766 pa_cost_distribution_lines_all cdl,
5767 pa_transaction_sources pts
5768 WHERE cdl.expenditure_item_id = x_expenditure_item_id
5769 AND cdl.line_num = x_line_num
5770 AND cdl.expenditure_item_id = ei.expenditure_item_id
5771 AND ei.transaction_source = pts.transaction_source(+) ;
5772
5773
5774 FOR v_alc_ledger IN c_alc_ledger
5775 LOOP
5776
5777 l_alc_exists := 'Y';
5778
5779 /* Bug 5254487 */
5780 If l_transaction_source = 'AP ERV' Then
5781 l_cip_cost := 0;
5782 End If;
5783
5784 IF (l_acct_event_id is not null and NVL(l_transaction_source,'NULL') <> 'AP ERV' ) or
5785 (l_transaction_source is not null and l_transfer_status_code = 'V' and l_predefined_flag = 'Y'
5786 and NVL(l_transaction_source,'NULL') <> 'AP ERV' and l_historical_flag ='N' ) THEN
5787
5788 /* Transaction accounted in SLA by PA
5789 and Transaction accounted in SLA by Oracle Subledgers other than PA */
5790
5791
5792
5793 begin
5794
5795 x_err_stage := 'Create ALC Project Asset Line details by PA SLA or by External SLA';
5796
5797 select nvl(xdl.unrounded_accounted_dr, -1 * xdl.unrounded_accounted_cr)
5798 into l_cip_cost
5799 from xla_distribution_links xdl,
5800 xla_ae_lines xal,
5801 xla_ae_headers xah,
5802 xla_acct_class_assgns xaca,
5803 xla_assignment_defns_b xad,
5804 xla_post_acct_progs_b xpap
5805 where xpap.program_code = 'PA_POSTACCOUNTING_DEBIT'
5806 and xpap.program_owner_code = 'S'
5807 and xpap.application_id = 275
5808 and xpap.program_owner_code = xad.program_owner_code
5809 and xpap.program_code = xad.program_code
5810 and xad.enabled_flag = 'Y'
5811 and (xad.ledger_id IS NULL OR xad.ledger_id = v_alc_ledger.ledger_id)
5812 and xaca.assignment_owner_code = xad.assignment_owner_code
5813 and xaca.assignment_code = xad.assignment_code
5814 and xaca.program_owner_code = xad.program_owner_code
5815 and xaca.program_code = xad.program_code
5816 and xal.accounting_class_code = xaca.accounting_class_code
5817 and xal.ae_header_id = xah.ae_header_id
5818 and xah.ledger_id = v_alc_ledger.ledger_id
5819 and xah.balance_type_code = 'A'
5820 and xah.accounting_entry_status_code = 'F'
5821 and xal.ae_header_id = xdl.ae_header_id
5822 and xal.ae_line_num = xdl.ae_line_num
5823 and decode ( xaca.accounting_class_code ,'DISCOUNT', decode (v_alc_ledger.sla_ledger_cash_basis_flag , 'Y', 2 , 1) , 1 ) = 1
5824 /*
5825 Bug 5039683 For Cash Basis : Hard coded acc class 'Discount'
5826 Restricted 'Discount' acc class code to be fetched from debit side of Post Acc Program
5827 'PA_POSTACCOUNITNG_DEBIT' for cash basis txns, as for cash basis txn, 'Discount' should
5828 be on credit side of Post Acc Programs.
5829
5830 For R12+, this need be reverted out and create seperate post acc program
5831 for cash basis include 'Discount' in credit side and remove from Debit.
5832 */
5833 and xdl.application_id = DECODE(l_transfer_status_code, 'V',
5834 decode( l_Acct_Source_Code, 'AP_INV', 200,
5835 'AP_PAY', 200,
5836 'AP_APP', 200,
5837 'INV', 707,
5838 'WIP', 707,
5839 'RCV', 707),
5840 275)
5841 and NVL(xdl.source_distribution_id_num_2, -99) = DECODE(l_transfer_status_code,
5842 'V',nvl(xdl.source_distribution_id_num_2,-99),
5843 x_line_num
5844 )
5845 and xdl.source_distribution_id_num_1 = DECODE(l_transfer_status_code, 'V',
5846 DECODE(l_Acct_Source_Code, 'AP_INV',
5847 DECODE ( l_document_payment_id ,
5848 NULL , l_document_distribution_id,
5849 l_system_reference5
5850 ) ,
5851 'AP_PAY', l_System_Reference5,
5852 'AP_APP', l_System_Reference5,
5853 'INV', l_System_Reference5,
5854 'WIP', l_System_Reference5,
5855 'RCV', l_System_Reference5),
5856 x_expenditure_item_id)
5857 and xdl.source_distribution_type = DECODE(l_transfer_status_code,
5858 'V', DECODE (l_Acct_Source_Code,
5859 'AP_PAY','AP_PMT_DIST',
5860 'AP_INV', DECODE ( l_document_payment_id ,
5861 NULL , 'AP_INV_DIST',
5862 'AP_PMT_DIST'
5863 ),
5864 'AP_APP','AP_PREPAY',
5865 'RCV','RCV_RECEIVING_SUB_LEDGER',
5866 'INV','MTL_TRANSACTION_ACCOUNTS',
5867 'WIP','WIP_TRANSACTION_ACCOUNTS')
5868 , l_line_type );
5869
5870
5871 exception
5872 when NO_DATA_FOUND then
5873
5874 SELECT 'N'
5875 into l_alc_exists
5876 from dual
5877 WHERE NOT EXISTS (
5878 SELECT gl.ledger_id
5879 FROM gl_alc_ledger_rships_v gl
5880 WHERE gl.source_ledger_id = v_alc_ledger.set_of_books_id
5881 AND gl.application_id = decode( l_Acct_Source_Code, 'AP_INV', 200,
5882 'AP_PAY', 200,
5883 'AP_APP', 200,
5884 'INV', 707,
5885 'WIP', 707,
5886 'RCV', 707,
5887 0)
5888 AND gl.currency_code = v_alc_ledger.currency_code
5889 AND gl.relationship_enabled_flag = 'Y') ;
5890
5891 end;
5892
5893
5894 Elsif (l_historical_flag = 'Y') Then
5895
5896 SELECT p_type.capital_cost_type_code
5897 INTO l_capital_cost_type
5898 FROM pa_project_types_all p_type,
5899 pa_projects_all proj,
5900 pa_cost_distribution_lines_all pa_cdl
5901 WHERE p_type.project_type = proj.project_type
5902 AND proj.project_id = pa_cdl.project_id
5903 AND pa_cdl.expenditure_item_id = x_expenditure_item_id
5904 AND pa_cdl.line_num = x_line_num
5905 AND p_type.org_id = v_alc_ledger.org_id;
5906
5907 BEGIN
5908
5909 IF (l_capital_cost_type = 'B') THEN
5910 SELECT nvl(burdened_cost,amount),
5911 exchange_rate,
5912 conversion_date
5913 INTO l_cip_cost,
5914 l_exchange_rate,
5915 l_gl_date
5916 FROM PA_MC_COST_DIST_LINES_ALL
5917 WHERE set_of_books_id = v_alc_ledger.ledger_id
5918 AND expenditure_item_id = x_expenditure_item_id
5919 AND line_num = x_line_num;
5920 ELSE
5921 SELECT amount,
5922 exchange_rate,
5923 conversion_date
5924 INTO l_cip_cost,
5925 l_exchange_rate,
5926 l_gl_date
5927 FROM PA_MC_COST_DIST_LINES_ALL
5928 WHERE set_of_books_id = v_alc_ledger.ledger_id
5929 AND expenditure_item_id = x_expenditure_item_id
5930 AND line_num = x_line_num;
5931 END IF;
5932
5933
5934 exception
5935 when NO_DATA_FOUND then
5936 l_alc_exists := 'N';
5937
5938 End;
5939
5940 END IF;
5941
5942 IF ((l_transaction_source is not null and l_predefined_flag = 'N' and l_transfer_status_code = 'V')
5943 OR (l_line_type = 'I')
5944 OR (l_acct_event_id is null and l_transfer_status_code = 'A' and l_historical_flag = 'N')
5945 OR (l_alc_exists = 'N')) THEN
5946
5947 /* Externally accounted transactions from user defined sources
5948 or 'I' cdls
5949 or interface to GL implementation option is set to No
5950 or the Oracle Subledger where the txn originated does not have this alc enabled */
5951
5952
5953
5954
5955
5956 x_err_stage := 'Create ALC Project Asset Line details by GL rate';
5957
5958 gl_mc_currency_pkg.get_rate(
5959 p_primary_set_of_books_id => v_alc_ledger.set_of_books_id,
5960 p_reporting_set_of_books_id => v_alc_ledger.ledger_id,
5961 p_trans_date => l_gl_date,
5962 p_trans_currency_code => l_denom_currency_code,
5963 p_trans_conversion_type => l_exchange_rate_type,
5964 p_trans_conversion_date => l_gl_date,
5965 p_trans_conversion_rate => l_exchange_rate,
5966 p_application_id => 275,
5967 p_org_id => v_alc_ledger.org_id,
5968 p_fa_book_type_code => NULL,
5969 p_je_source_name => NULL,
5970 p_je_category_name => NULL,
5971 p_result_code => l_result_code,
5972 p_denominator_rate => l_denominator_rate,
5973 p_numerator_rate => l_numerator_rate);
5974
5975
5976 IF l_exchange_rate_type = 'User' THEN
5977 l_cip_cost := pa_mc_currency_pkg.CurrRound ((l_denom_raw_cost*l_exchange_rate), v_alc_ledger.currency_code);
5978
5979 ELSE l_cip_cost := pa_mc_currency_pkg.CurrRound (((l_denom_raw_cost/l_denominator_rate)*l_numerator_rate),
5980 v_alc_ledger.currency_code);
5981 l_exchange_rate := l_numerator_rate/l_denominator_rate;
5982 END IF;
5983
5984
5985
5986 END IF;
5987
5988
5989
5990 INSERT into PA_MC_PRJ_AST_LINE_DTLS
5991 (
5992 SET_OF_BOOKS_ID,
5993 PROJ_ASSET_LINE_DTL_UNIQ_ID,
5994 PROJECT_ASSET_LINE_DETAIL_ID,
5995 CIP_COST,
5996 CURRENCY_CODE,
5997 EXCHANGE_RATE,
5998 CONVERSION_DATE
5999 )
6000 VALUES
6001 (
6002 v_alc_ledger.ledger_id,
6003 x_proj_asset_line_dtl_uniq_id,
6004 x_project_asset_line_detail_id,
6005 l_cip_cost,
6006 v_alc_ledger.currency_code,
6007 l_exchange_rate,
6008 l_gl_date
6009 );
6010
6011 END LOOP;
6012
6013 exception
6014 when others then
6015 x_err_code := SQLCODE;
6016 RAISE;
6017
6018 end create_alc_asset_line_details;
6019
6020 ----------------------------------------------------------------------------------------------------------
6021
6022 PROCEDURE create_alc_proj_asset_lines (x_project_asset_line_id IN NUMBER,
6023 x_project_asset_line_detail_id IN NUMBER,
6024 x_rev_proj_asset_line_id IN NUMBER,
6025 x_original_asset_cost IN NUMBER,
6026 x_current_asset_cost IN NUMBER,
6027 x_err_stage IN OUT NOCOPY VARCHAR2,
6028 x_err_code IN OUT NOCOPY NUMBER)
6029
6030 IS
6031
6032 CURSOR c_alc_ledger IS
6033 SELECT gl.ledger_id, gl.currency_code
6034 FROM gl_alc_ledger_rships_v gl,
6035 pa_implementations imp
6036 WHERE gl.source_ledger_id = imp.set_of_books_id
6037 AND gl.application_id = 275
6038 AND gl.org_id = imp.org_id
6039 AND gl.relationship_enabled_flag = 'Y';
6040
6041 l_orig_asset_cost NUMBER :=0;
6042 l_curr_asset_cost NUMBER :=0;
6043 l_cost_ratio NUMBER :=0;
6044
6045
6046 begin
6047
6048 x_err_code := 0;
6049 x_err_stage := 'Create ALC Project Asset Line details';
6050
6051 FOR v_alc_ledger IN c_alc_ledger LOOP
6052
6053 IF (x_rev_proj_asset_line_id IS NOT NULL) THEN
6054
6055 x_err_stage := 'Create ALC Project Asset Line details for rev line';
6056
6057 SELECT original_asset_cost,
6058 current_asset_cost
6059 INTO l_orig_asset_cost,
6060 l_curr_asset_cost
6061 FROM PA_MC_PRJ_AST_LINES_ALL
6062 WHERE set_of_books_id = v_alc_ledger.ledger_id
6063 AND project_asset_line_id = x_rev_proj_asset_line_id;
6064
6065 ELSIF (x_original_asset_cost <> x_current_asset_cost) THEN
6066
6067 l_cost_ratio := x_current_asset_cost/x_original_asset_cost;
6068
6069 x_err_stage := 'Create ALC Project Asset Line details for split line';
6070
6071 SELECT original_asset_cost,
6072 sum(current_asset_cost)
6073 INTO l_orig_asset_cost,
6074 l_curr_asset_cost
6075 FROM PA_MC_PRJ_AST_LINES_ALL
6076 WHERE set_of_books_id = v_alc_ledger.ledger_id
6077 AND project_asset_line_detail_id = x_project_asset_line_detail_id
6078 GROUP BY original_asset_cost;
6079
6080 l_curr_asset_cost := pa_mc_currency_pkg.CurrRound((l_cost_ratio * l_orig_asset_cost),
6081 v_alc_ledger.currency_code);
6082
6083 ELSE
6084
6085 x_err_stage := 'Create ALC Project Asset Line details for new line';
6086
6087 SELECT nvl(sum(cip_cost),0)
6088 INTO l_orig_asset_cost
6089 FROM PA_MC_PRJ_AST_LINE_DTLS
6090 WHERE set_of_books_id = v_alc_ledger.ledger_id
6091 AND project_asset_line_detail_id = x_project_asset_line_detail_id;
6092
6093 l_curr_asset_cost := l_orig_asset_cost;
6094
6095 END IF;
6096
6097 INSERT into PA_MC_PRJ_AST_LINES_ALL
6098 (
6099 SET_OF_BOOKS_ID,
6100 PROJECT_ASSET_LINE_ID,
6101 ORIGINAL_ASSET_COST,
6102 CURRENT_ASSET_COST,
6103 PROJECT_ASSET_LINE_DETAIL_ID
6104 )
6105 VALUES
6106 (
6107 v_alc_ledger.ledger_id,
6108 x_project_asset_line_id,
6109 l_orig_asset_cost,
6110 l_curr_asset_cost,
6111 x_project_asset_line_detail_id
6112 );
6113
6114 END LOOP;
6115
6116 exception
6117 when others then
6118 x_err_code := SQLCODE;
6119 RAISE;
6120
6121 end create_alc_proj_asset_lines;
6122
6123 ----------------------------------------------------------------------------------------------------------
6124
6125 PROCEDURE update_alc_proj_asset_lines (x_project_asset_line_id IN NUMBER,
6126 x_original_asset_cost IN NUMBER,
6127 x_current_asset_cost IN NUMBER)
6128 IS
6129
6130 CURSOR c_alc_ledger IS
6131 SELECT gl.ledger_id, gl.currency_code
6132 FROM gl_alc_ledger_rships_v gl,
6133 pa_implementations imp
6134 WHERE gl.source_ledger_id = imp.set_of_books_id
6135 AND gl.application_id = 275
6136 AND gl.org_id = imp.org_id
6137 AND gl.relationship_enabled_flag = 'Y';
6138
6139 l_cost_ratio NUMBER := x_current_asset_cost/x_original_asset_cost;
6140
6141 begin
6142
6143 FOR v_alc_ledger IN c_alc_ledger LOOP
6144
6145 UPDATE PA_MC_PRJ_AST_LINES_ALL
6146 SET current_asset_cost = pa_mc_currency_pkg.CurrRound((l_cost_ratio*original_asset_cost),
6147 v_alc_ledger.currency_code)
6148 WHERE project_asset_line_id = x_project_asset_line_id
6149 AND set_of_books_id = v_alc_ledger.ledger_id;
6150
6151 END LOOP;
6152
6153 exception
6154 when others then
6155 RAISE;
6156
6157 end update_alc_proj_asset_lines;
6158
6159 ----------------------------------------------------------------------------------------------------------
6160
6161 PROCEDURE create_alc_fa_mass_additions (x_project_asset_line_id IN NUMBER,
6162 x_mass_addition_id IN NUMBER,
6163 x_parent_mass_addition_id IN NUMBER,
6164 x_fixed_assets_cost IN NUMBER)
6165 IS
6166
6167 CURSOR c_alc_ledger IS
6168 SELECT gl.ledger_id, gl.currency_code
6169 FROM gl_alc_ledger_rships_v gl,
6170 pa_implementations imp
6171 WHERE gl.source_ledger_id = imp.set_of_books_id
6172 AND gl.application_id = 275
6173 AND gl.org_id = imp.org_id
6174 AND gl.relationship_enabled_flag = 'Y';
6175
6176 l_fixed_assets_alc_cost NUMBER;
6177 l_exchange_rate NUMBER;
6178
6179 begin
6180
6181 FOR v_alc_ledger IN c_alc_ledger LOOP
6182
6183 IF x_fixed_assets_cost = 0 THEN /* To handle the case when merge code is 'MP' */
6184 l_fixed_assets_alc_cost := 0;
6185 l_exchange_rate := 0;
6186 ELSE
6187 SELECT current_asset_cost
6188 INTO l_fixed_assets_alc_cost
6189 FROM PA_MC_PRJ_AST_LINES_ALL
6190 WHERE project_asset_line_id = x_project_asset_line_id
6191 AND set_of_books_id = v_alc_ledger.ledger_id;
6192
6193 l_exchange_rate := l_fixed_assets_alc_cost/x_fixed_assets_cost;
6194
6195 END IF;
6196
6197 INSERT into FA_MC_MASS_RATES
6198 (
6199 SET_OF_BOOKS_ID,
6200 MASS_ADDITION_ID,
6201 PARENT_MASS_ADDITION_ID,
6202 FIXED_ASSETS_COST,
6203 EXCHANGE_RATE,
6204 CREATED_BY,
6205 CREATION_DATE,
6206 LAST_UPDATED_BY,
6207 LAST_UPDATE_DATE,
6208 LAST_UPDATE_LOGIN
6209 )
6210 VALUES
6211 (
6212 v_alc_ledger.ledger_id,
6213 x_mass_addition_id,
6214 x_parent_mass_addition_id,
6215 l_fixed_assets_alc_cost,
6216 l_exchange_rate,
6217 x_created_by,
6218 sysdate,
6219 x_created_by,
6220 sysdate,
6221 x_last_update_login
6222 );
6223
6224 END LOOP;
6225
6226 exception
6227 when others then
6228 RAISE;
6229
6230 end create_alc_fa_mass_additions;
6231
6232 ----------------------------------------------------------------------------------------------------------
6233
6234 -- Added procedure for bug 5401326
6235 PROCEDURE set_request_id(x_passed_request_id IN NUMBER) IS
6236 BEGIN
6237 x_request_id := x_passed_request_id;
6238 END set_request_id;
6239
6240 END PA_FAXFACE;