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