[Home] [Help]
PACKAGE BODY: APPS.PA_CAPITAL_EVENTS_PVT
Source
1 PACKAGE BODY PA_CAPITAL_EVENTS_PVT AS
2 /* $Header: PACACCBB.pls 120.1 2005/06/10 03:22:22 avajain noship $ */
3
4
5 PROCEDURE CREATE_PERIODIC_EVENTS
6 (errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2,
8 p_event_period_name IN VARCHAR2,
9 p_asset_date_through_arg IN VARCHAR2,
10 p_ei_date_through_arg IN VARCHAR2 DEFAULT NULL,
11 p_project_id IN NUMBER DEFAULT NULL) IS
12
13
14 p_asset_date_through DATE;
15 p_ei_date_through DATE;
16
17 CURSOR ac_projects_cur IS
18 SELECT p.project_id,
19 p.segment1 project_number,
20 p.name project_name,
21 p.asset_allocation_method
22 FROM pa_projects p,
23 pa_project_types pt
24 WHERE p.project_type = pt.project_type
25 AND pt.project_type_class_code = 'CAPITAL'
26 AND NVL(p.capital_event_processing,'N') = 'P'
27 AND p.project_id = NVL(p_project_id, p.project_id)
28 AND p.template_flag = 'N'
29 ORDER BY p.segment1;
30
31 ac_projects_rec ac_projects_cur%ROWTYPE;
32
33 v_user_id NUMBER := FND_GLOBAL.user_id;
34 v_login_id NUMBER := FND_GLOBAL.login_id;
35 v_request_id NUMBER := FND_GLOBAL.conc_request_id;
36 v_program_application_id NUMBER := FND_GLOBAL.prog_appl_id;
37 v_program_id NUMBER := FND_GLOBAL.conc_program_id;
38 v_null_rowid ROWID := NULL;
39 -- v_org_id NUMBER := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
40 v_org_id NUMBER := PA_MOAC_UTILS.get_current_org_id ;
41
42 v_project_number pa_projects_all.segment1%TYPE;
43 v_ret_assets_count NUMBER := 0;
44 v_ret_tasks_count NUMBER := 0;
45 v_return_status VARCHAR2(1);
46 v_msg_data VARCHAR2(2000);
47
48 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
49
50 p_event_period_name_missing EXCEPTION;
51 p_asset_date_through_missing EXCEPTION;
52 unexp_error_in_client_extn EXCEPTION;
53 error_in_client_extn EXCEPTION;
54
55 --This local procedure is used to print the control report and associated
56 --messages regarding the creation of periodic events for each project processed.
57 PROCEDURE print_report IS
58
59 CURSOR report_cur (x_conc_request_id NUMBER) IS
60 SELECT pe.project_number,
61 pe.project_id,
62 p.name project_name,
63 pe.capital_type,
64 pl2.meaning capital_type_desc,
65 pe.context,
66 pe.sub_context,
67 pe.capital_event_id,
68 DECODE(sub_context,'A',UPPER(asset_name)||' ',
69 'AT',UPPER(asset_name)||' ',
70 NULL)||
71 pl.meaning||' '||
72 DECODE(sub_context,'E',capital_event_number||' '||event_name,
73 'AE',capital_event_number||' '||event_name,
74 'CE',capital_event_number||' '||event_name,
75 'P',project_number,
76 'T',task_number,
77 'AT',task_number) formatted_message
78 FROM pa_cap_event_creation_v pe,
79 pa_lookups pl,
80 pa_lookups pl2,
81 pa_projects p
82 WHERE pe.request_id = x_conc_request_id
83 AND pl.lookup_type = 'PERIODIC_EVENT_CREATION'
84 AND pl.lookup_code = pe.message_code
85 AND pl2.lookup_type = 'CAPITAL_TYPE'
86 AND pl2.lookup_code = pe.capital_type
87 AND pe.project_id = p.project_id (+)
88 ORDER BY pe.project_id,
89 pe.capital_type,
90 pe.context;
91
92 report_rec report_cur%ROWTYPE;
93
94
95 CURSOR assets_added_cur (x_project_id NUMBER, x_capital_event_id NUMBER) IS
96 SELECT asset_name,
97 asset_description
98 FROM pa_project_assets_all
99 WHERE project_id = x_project_id
100 AND capital_event_id = x_capital_event_id
101 AND request_id = v_request_id;
102
103 assets_added_rec assets_added_cur%ROWTYPE;
104
105
106 CURSOR costs_added_cur (x_project_id NUMBER, x_capital_event_id NUMBER, x_cost_type VARCHAR2) IS
107 SELECT SUM(DECODE(x_cost_type,'R',NVL(raw_cost,0),NVL(burden_cost,0))) total_cost
108 FROM pa_expenditure_items_all
109 WHERE project_id = x_project_id
110 AND capital_event_id = x_capital_event_id
111 AND request_id = v_request_id;
112
113 costs_added_rec costs_added_cur%ROWTYPE;
114
115
116 CURSOR current_costs_cur (x_project_id NUMBER, x_capital_event_id NUMBER, x_cost_type VARCHAR2) IS
117 SELECT SUM(DECODE(x_cost_type,'R',NVL(raw_cost,0),NVL(burden_cost,0))) total_cost
118 FROM pa_expenditure_items_all
119 WHERE project_id = x_project_id
120 AND capital_event_id = x_capital_event_id;
121
122 current_costs_rec current_costs_cur%ROWTYPE;
123
124
125 curr_project_id NUMBER;
126 curr_capital_type PA_REPORTING_EXCEPTIONS.record_type%TYPE;
127 curr_context PA_REPORTING_EXCEPTIONS.context%TYPE;
128 v_cost_type PA_PROJECT_TYPES.capital_cost_type_code%TYPE;
129 v_report_title PA_LOOKUPS.meaning%TYPE;
130 v_proj_heading1 PA_LOOKUPS.meaning%TYPE;
131 v_proj_heading2 PA_LOOKUPS.meaning%TYPE;
132 v_event_information PA_LOOKUPS.meaning%TYPE;
133 v_assets_included PA_LOOKUPS.meaning%TYPE;
134 v_cost_included PA_LOOKUPS.meaning%TYPE;
135 v_assets_added PA_LOOKUPS.meaning%TYPE;
136 v_cost_added PA_LOOKUPS.meaning%TYPE;
137 v_total_cost PA_LOOKUPS.meaning%TYPE;
138
139 BEGIN
140
141 --Get translated Report Title
142 SELECT meaning
143 INTO v_report_title
144 FROM pa_lookups
145 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
146 AND lookup_code = 'REPORT_TITLE';
147
148 --Get translated Report Heading 1
149 SELECT meaning
150 INTO v_proj_heading1
151 FROM pa_lookups
152 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
153 AND lookup_code = 'PROJ_HEADING_1';
154
155 --Get translated Report Heading 2
156 SELECT meaning
157 INTO v_proj_heading2
158 FROM pa_lookups
159 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
160 AND lookup_code = 'PROJ_HEADING_2';
161
162
163 --Get translated "Event Information" literal
164 SELECT meaning
165 INTO v_event_information
166 FROM pa_lookups
167 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
168 AND lookup_code = 'EVENT_INFO';
169
170 --Get translated "Assets Included" heading
171 SELECT meaning
172 INTO v_assets_included
173 FROM pa_lookups
174 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
175 AND lookup_code = 'ASSETS_INCLUDED';
176
177 --Get translated "Cost Included" heading
178 SELECT meaning
179 INTO v_cost_included
180 FROM pa_lookups
181 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
182 AND lookup_code = 'COST_INCLUDED';
183
184 --Get translated "Assets Added" heading
185 SELECT meaning
186 INTO v_assets_added
187 FROM pa_lookups
188 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
189 AND lookup_code = 'ASSETS_ADDED';
190
191 --Get translated "Cost Added" heading
192 SELECT meaning
193 INTO v_cost_added
194 FROM pa_lookups
195 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
196 AND lookup_code = 'COST_ADDED';
197
198 --Get translated "Total Cost" heading
199 SELECT meaning
200 INTO v_total_cost
201 FROM pa_lookups
202 WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
203 AND lookup_code = 'TOTAL_COST';
204
205
206 --Print Report Title
207 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,TO_CHAR(sysdate,'DD-MON-YYYY')||
208 ' '||v_report_title);
209 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
210
211
212
213 FOR report_rec IN report_cur(v_request_id) LOOP
214
215 IF NVL(curr_project_id,-1) <> report_rec.project_id THEN
216
217 --Print Project Header
218
219 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
220 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
221 -- Replaced with translated lookup values for headings
222 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('Project Number',25,' ')||RPAD('Project Name',30,' '));
223 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('==============',25,' ')||RPAD('============',30,' '));
224 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,v_proj_heading1);
225 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,v_proj_heading2);
226 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(report_rec.project_number,25,' ')||RPAD(report_rec.project_name,30,' '));
227
228 curr_project_id := report_rec.project_id;
229 curr_capital_type := 'X';
230 curr_context := report_rec.context;
231
232 --Get Cost Type
233 SELECT pt.capital_cost_type_code
234 INTO v_cost_type
235 FROM pa_projects p,
236 pa_project_types pt
237 WHERE p.project_type = pt.project_type
238 AND p.project_id = report_rec.project_id;
239
240 END IF;
241
242
243
244 IF NVL(curr_capital_type,'X') <> report_rec.capital_type THEN
245
246 --Print Project Header
247 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
248 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,report_rec.capital_type_desc||' '||v_event_information);
249 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------------------------------------');
250
251 curr_capital_type := report_rec.capital_type;
252 curr_context := report_rec.context;
253 END IF;
254
255
256 --Print blank line for report formatting during control break
257 IF report_rec.context = '3' THEN
258
259 --Print Blank Line
260 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
261
262 END IF;
263
264
265 --Print blank line for report formatting during control break
266 IF NVL(curr_context,report_rec.context) <> report_rec.context
267 AND NVL(curr_context,'X') = '1' THEN
268
269 --Print Blank Line
270 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
271
272 curr_context := report_rec.context;
273 END IF;
274
275
276 --Print message line
277 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,report_rec.formatted_message);
278
279
280 --Print listing of Assets Added to New Events along with Event Cost Total
281 IF report_rec.sub_context = 'E' THEN
282
283 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||v_assets_included);
284
285 FOR assets_added_rec IN assets_added_cur(report_rec.project_id, report_rec.capital_event_id) LOOP
286 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||assets_added_rec.asset_name||' - '||assets_added_rec.asset_description);
287 END LOOP;
288
289 FOR costs_added_rec IN costs_added_cur(report_rec.project_id, report_rec.capital_event_id, v_cost_type) LOOP
290 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
291 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||v_cost_included||' '||TO_CHAR(costs_added_rec.total_cost,pa_currency.currency_fmt_mask(15)));
292 END LOOP;
293
294 END IF;
295
296
297 --Print listing of Assets Added to Existing Event
298 IF report_rec.sub_context = 'AE' THEN
299
300 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||v_assets_added);
301
302 FOR assets_added_rec IN assets_added_cur(report_rec.project_id, report_rec.capital_event_id) LOOP
303 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||assets_added_rec.asset_name||' - '||assets_added_rec.asset_description);
304 END LOOP;
305
306 END IF;
307
308
309 --Print Total of Costs Added to Existing Event
310 IF report_rec.sub_context = 'CE' THEN
311
312 FOR costs_added_rec IN costs_added_cur(report_rec.project_id, report_rec.capital_event_id, v_cost_type) LOOP
313 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||v_cost_added||' '||TO_CHAR(costs_added_rec.total_cost,pa_currency.currency_fmt_mask(15)));
314 END LOOP;
315
316
317 --Print current Cost Total for Existing Event
318
319 FOR current_costs_rec IN current_costs_cur(report_rec.project_id, report_rec.capital_event_id, v_cost_type) LOOP
320 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
321 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||v_total_cost||' '||TO_CHAR(current_costs_rec.total_cost,pa_currency.currency_fmt_mask(15)));
322 END LOOP;
323
324 END IF;
325
326 END LOOP;
327
328 END;
329
330
331
332 BEGIN
333 --Initialize variables
334 retcode := 0;
335 errbuf := NULL;
336 PA_DEBUG.SET_PROCESS(x_process => 'PLSQL',
337 x_debug_mode => PG_DEBUG);
338
339 PA_DEBUG.WRITE_FILE('LOG', TO_CHAR(SYSDATE,'HH:MI:SS')||': PA_DEBUG_MODE: '||PG_DEBUG);
340
341 --CHANGING date arguments from VARCHAR2 TO DATE
342 p_asset_date_through := fnd_date.canonical_to_date(p_asset_date_through_arg);
343 p_ei_date_through := fnd_date.canonical_to_date(p_ei_date_through_arg);
344
345 --Validate the required parameters
346 IF p_event_period_name IS NULL THEN
347 RAISE p_event_period_name_missing;
348 END IF;
349
350 IF p_asset_date_through IS NULL THEN
351 RAISE p_asset_date_through_missing;
352 END IF;
353
354
355 IF PG_DEBUG = 'Y' THEN
356 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Opening ac_projects_cur');
357 END IF;
358
359
360 --Verify that project(s) exist to process
361 OPEN ac_projects_cur;
362 FETCH ac_projects_cur INTO ac_projects_rec;
363
364 IF (ac_projects_cur%NOTFOUND) THEN
365
366 IF p_project_id IS NOT NULL THEN
367
368 SELECT segment1
369 INTO v_project_number
370 FROM pa_projects_all
371 WHERE project_id = p_project_id;
372
373
374 INSERT INTO pa_cap_event_creation_v
375 (request_id,
376 module,
377 context,
378 sub_context,
379 capital_type,
380 project_id,
381 project_number,
382 project_asset_id,
383 asset_name,
384 task_id,
385 task_number,
386 capital_event_id,
387 capital_event_number,
388 event_name,
389 event_type,
390 message_code,
391 created_by,
392 creation_date,
393 org_id
394 )
395 VALUES
396 (v_request_id,
397 'PERIODIC_EVENT_CREATION', --module
398 '1', --context (1 = Message)
399 'N', --sub_context
400 'C', --capital_type,
401 p_project_id, --project_id,
402 v_project_number, --project_number,
403 NULL, --project_asset_id,
404 NULL, --asset_name,
405 NULL, --task_id,
406 NULL, --task_number,
407 NULL, --capital_event_id,
408 NULL, --capital_event_number,
409 NULL, --event_name,
410 NULL, --event_type,
411 'PROJECT_NOT_FOUND',
412 v_user_id, --created_by,
413 SYSDATE, --creation_date,
414 v_org_id --org_id
415 );
416
417 ELSE
418 INSERT INTO pa_cap_event_creation_v
419 (request_id,
420 module,
421 context,
422 sub_context,
423 capital_type,
424 project_id,
425 project_number,
426 project_asset_id,
427 asset_name,
428 task_id,
429 task_number,
430 capital_event_id,
431 capital_event_number,
432 event_name,
433 event_type,
434 message_code,
435 created_by,
436 creation_date,
437 org_id
438 )
439 VALUES
440 (v_request_id,
441 'PERIODIC_EVENT_CREATION', --module
442 '1', --context (1 = Message)
443 'N', --sub_context
444 'C', --capital_type,
445 NULL, --project_id,
446 NULL, --project_number,
447 NULL, --project_asset_id,
448 NULL, --asset_name,
449 NULL, --task_id,
450 NULL, --task_number,
451 NULL, --capital_event_id,
452 NULL, --capital_event_number,
453 NULL, --event_name,
454 NULL, --event_type,
455 'NO_PROJECTS_FOUND',
456 v_user_id, --created_by,
457 SYSDATE, --creation_date,
458 v_org_id --org_id
459 );
460 END IF;
461 END IF;
462 CLOSE ac_projects_cur;
463
464
465 --Loop through all "Periodic Event Creation" projects
466 FOR ac_projects_rec IN ac_projects_cur LOOP
467
468 IF PG_DEBUG = 'Y' THEN
469 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Calling pre_capital_event client extension for project id: '||ac_projects_rec.project_id);
470 END IF;
471
472 --Call the PRE_CAPITAL_EVENT client extension
473 PA_CLIENT_EXTN_PRE_CAP_EVENT.PRE_CAPITAL_EVENT
474 (p_project_id => ac_projects_rec.project_id,
475 p_event_period_name => p_event_period_name,
476 p_asset_date_through => p_asset_date_through,
477 p_ei_date_through => p_ei_date_through,
478 x_return_status => v_return_status,
479 x_msg_data => v_msg_data);
480
481 IF v_return_status = 'E' THEN
482 IF PG_DEBUG = 'Y' THEN
483 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Error in pre_capital_event client extension for project id: '||ac_projects_rec.project_id);
484 END IF;
485
486 RAISE error_in_client_extn;
487 ELSIF v_return_status = 'U' THEN
488 IF PG_DEBUG = 'Y' THEN
489 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Unexpected Error in pre_capital_event client extension for project id: '||ac_projects_rec.project_id);
490 END IF;
491
492 RAISE unexp_error_in_client_extn;
493 END IF;
494
495
496
497 PA_CAPITAL_EVENTS_PVT.CREATE_EVENT_FOR_PROJECT
498 (errbuf => errbuf,
499 retcode => retcode,
500 p_event_period_name => p_event_period_name,
501 p_asset_date_through => p_asset_date_through,
502 p_ei_date_through => p_ei_date_through,
503 p_project_id => ac_projects_rec.project_id,
504 p_event_type => 'C',
505 p_project_number => ac_projects_rec.project_number,
506 p_asset_allocation_method => ac_projects_rec.asset_allocation_method);
507
508
509 --Determine if any Retirement Cost Tasks exist for the current project
510 SELECT COUNT(*)
511 INTO v_ret_tasks_count
512 FROM pa_tasks
513 WHERE project_id = ac_projects_rec.project_id
514 AND NVL(retirement_cost_flag,'N') = 'Y';
515
516 --Determine if any Retirement Adjustment Assets assets exist for the current project
517 SELECT COUNT(*)
518 INTO v_ret_assets_count
519 FROM pa_project_assets
520 WHERE project_id = ac_projects_rec.project_id
521 AND project_asset_type = 'RETIREMENT_ADJUSTMENT';
522
523
524 IF (v_ret_tasks_count > 0) OR (v_ret_assets_count > 0) THEN
525
526 PA_CAPITAL_EVENTS_PVT.CREATE_EVENT_FOR_PROJECT
527 (errbuf => errbuf,
528 retcode => retcode,
529 p_event_period_name => p_event_period_name,
530 p_asset_date_through => p_asset_date_through,
531 p_ei_date_through => p_ei_date_through,
532 p_project_id => ac_projects_rec.project_id,
533 p_event_type => 'R',
534 p_project_number => ac_projects_rec.project_number,
535 p_asset_allocation_method => ac_projects_rec.asset_allocation_method);
536
537 END IF;
538
539 COMMIT;
540
541 END LOOP; --Periodic Event Creation projects
542
543
544 /* Print control report */
545
546 print_report;
547
548
549
550
551 EXCEPTION
552 WHEN p_event_period_name_missing THEN
553 retcode := -10;
554 errbuf := 'Parameter p_event_period_name is required.';
555 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,errbuf);
556 FND_FILE.PUT_LINE(FND_FILE.LOG,errbuf);
557 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CAPITAL_EVENTS_PVT',
558 p_procedure_name => 'CREATE_PERIODIC_EVENT',
559 p_error_text => SUBSTRB(errbuf,1,240));
560 ROLLBACK;
561 RAISE;
562
563
564 WHEN p_asset_date_through_missing THEN
565 retcode := -20;
566 errbuf := 'Parameter p_asset_through_date is required.';
567 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,errbuf);
568 FND_FILE.PUT_LINE(FND_FILE.LOG,errbuf);
569 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CAPITAL_EVENTS_PVT',
570 p_procedure_name => 'CREATE_PERIODIC_EVENT',
571 p_error_text => SUBSTRB(errbuf,1,240));
572 ROLLBACK;
573 RAISE;
574
575
576 WHEN error_in_client_extn THEN
577 retcode := -40;
578 errbuf := 'Error in PRE_CAPITAL_EVENT client extension for project id '||ac_projects_rec.project_id||' '||v_msg_data;
579 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,errbuf);
580 FND_FILE.PUT_LINE(FND_FILE.LOG,errbuf);
581 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_PRE_CAP_EVENT',
582 p_procedure_name => 'PRE_CAPITAL_EVENT',
583 p_error_text => SUBSTRB(v_msg_data,1,240));
584 ROLLBACK;
585 RAISE;
586
587
588 WHEN unexp_error_in_client_extn THEN
589 retcode := -50;
590 errbuf := 'Unexpected error in PRE_CAPITAL_EVENT client extn for project id '||ac_projects_rec.project_id||' '||v_msg_data;
591 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,errbuf);
592 FND_FILE.PUT_LINE(FND_FILE.LOG,errbuf);
593 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_PRE_CAP_EVENT',
594 p_procedure_name => 'PRE_CAPITAL_EVENT',
595 p_error_text => SUBSTRB(v_msg_data,1,240));
596 ROLLBACK;
597 RAISE;
598
599 WHEN OTHERS THEN
600 retcode := SQLCODE;
601 errbuf := 'Unexpected error for project id '||
602 ac_projects_rec.project_id||': '||SQLCODE||' '||SQLERRM;
603 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,errbuf);
604 FND_FILE.PUT_LINE(FND_FILE.LOG,errbuf);
605 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CAPITAL_EVENTS_PVT',
606 p_procedure_name => 'CREATE_PERIODIC_EVENT',
607 p_error_text => SUBSTRB(errbuf,1,240));
608 ROLLBACK;
609 RAISE;
610
611
612 END CREATE_PERIODIC_EVENTS;
613
614
615
616 PROCEDURE CREATE_EVENT_FOR_PROJECT
617 (errbuf OUT NOCOPY VARCHAR2,
618 retcode OUT NOCOPY VARCHAR2,
619 p_event_period_name IN VARCHAR2,
620 p_asset_date_through IN DATE,
621 p_ei_date_through IN DATE,
622 p_project_id IN NUMBER,
623 p_event_type IN VARCHAR2,
624 p_project_number IN VARCHAR2,
625 p_asset_allocation_method IN VARCHAR2) IS
626
627
628
629 CURSOR capital_event_cur(x_project_id NUMBER,
630 x_event_type VARCHAR2) IS
631 SELECT capital_event_id,
632 capital_event_number,
633 event_name
634 FROM pa_capital_events
635 WHERE project_id = x_project_id
636 AND event_period = p_event_period_name
637 AND event_type = x_event_type
638 ORDER BY capital_event_id;
639
640 capital_event_rec capital_event_cur%ROWTYPE;
641
642
643 CURSOR add_ei_tasks_cur(x_project_id NUMBER,
644 x_event_type VARCHAR2) IS
645 SELECT task_id
646 FROM pa_tasks
647 WHERE project_id = x_project_id
648 AND NVL(retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
649 ORDER BY task_id;
650
651 add_ei_tasks_rec add_ei_tasks_cur%ROWTYPE;
652
653
654 CURSOR new_assets_cur(x_project_id NUMBER,
655 x_event_type VARCHAR2) IS
656 SELECT pa.project_asset_id,
657 paa.task_id, --Grouping Level: If = 0, asset is assigned to project
658 t.retirement_cost_flag task_retirement_cost_flag --Will be NULL if Task ID = 0
659 FROM pa_project_assets_all pa,
660 pa_project_asset_assignments paa,
661 pa_tasks t
662 WHERE pa.project_id = x_project_id
663 AND pa.project_asset_id = paa.project_asset_id
664 AND pa.capital_event_id IS NULL
665 AND pa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
666 AND pa.date_placed_in_service IS NOT NULL
667 AND pa.date_placed_in_service <= p_asset_date_through
668 AND paa.task_id = t.task_id (+)
669 ORDER BY pa.project_asset_id, paa.task_id;
670
671 new_assets_rec new_assets_cur%ROWTYPE;
672
673
674 CURSOR common_tasks_cur(x_project_id NUMBER,
675 x_event_type VARCHAR2) IS
676 SELECT t.task_id,
677 paa.task_id assignment_task_id,
678 t.top_task_id,
679 t.parent_task_id
680 FROM pa_project_asset_assignments paa,
681 pa_tasks t
682 WHERE paa.project_id = x_project_id
683 AND NVL(t.retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
684 AND paa.project_asset_id = 0
685 AND (paa.task_id = t.task_id
686 OR paa.task_id = t.top_task_id);
687
688 common_tasks_rec common_tasks_cur%ROWTYPE;
689
690
691 CURSOR remaining_costs_cur(x_project_id NUMBER,
692 x_event_type VARCHAR2) IS
693 SELECT peia.task_id
694 FROM pa_expenditure_items_all peia,
695 pa_tasks t
696 WHERE peia.project_id = x_project_id
697 AND peia.task_id = t.task_id
698 AND t.project_id = x_project_id
699 AND NVL(t.retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
700 AND peia.billable_flag = DECODE(x_event_type,'C','Y','N')
701 AND peia.capital_event_id IS NULL
702 AND peia.expenditure_item_date <= NVL(p_ei_date_through, peia.expenditure_item_date)
703 AND peia.revenue_distributed_flag = 'N'
704 AND peia.cost_distributed_flag = 'Y'
705 GROUP BY peia.task_id;
706
707 remaining_costs_rec remaining_costs_cur%ROWTYPE;
708
709
710 CURSOR remaining_assets_cur(x_project_id NUMBER,
711 x_event_type VARCHAR2) IS
712 SELECT pa.project_asset_id,
713 pa.asset_name,
714 paa.task_id --Grouping Level: If = 0, asset is assigned to project
715 FROM pa_project_assets_all pa,
716 pa_project_asset_assignments paa
717 WHERE pa.project_id = x_project_id
718 AND pa.project_asset_id = paa.project_asset_id (+)
719 AND pa.capital_event_id IS NULL
720 AND pa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
721 AND pa.date_placed_in_service IS NOT NULL
722 AND pa.date_placed_in_service <= p_asset_date_through
723 ORDER BY pa.project_asset_id, paa.task_id;
724
725 remaining_assets_rec remaining_assets_cur%ROWTYPE;
726
727
728 CURSOR print_events_cur(x_request_id NUMBER,
729 x_project_id NUMBER,
730 x_event_type VARCHAR2) IS
731 SELECT p.segment1 project_number,
732 p.name project_name,
733 c.capital_event_id,
734 c.capital_event_number,
735 c.event_name,
736 c.event_type
737 FROM pa_capital_events c,
738 pa_projects p
739 WHERE c.project_id = x_project_id
740 AND c.event_type = x_event_type
741 AND c.request_id = x_request_id
742 AND p.project_id = x_project_id
743 ORDER BY p.segment1, c.capital_event_number;
744
745 print_events_rec print_events_cur%ROWTYPE;
746
747
748 CURSOR existing_events_cur(x_request_id NUMBER,
749 x_project_id NUMBER,
750 x_event_type VARCHAR2) IS
751 SELECT p.segment1 project_number,
752 p.name project_name,
753 c.project_id,
754 c.capital_event_id,
755 c.capital_event_number,
756 c.event_name,
757 c.event_type
758 FROM pa_capital_events c,
759 pa_projects p
760 WHERE c.project_id = p.project_id
761 AND c.project_id = x_project_id
762 AND c.event_period = p_event_period_name
763 AND c.event_type = x_event_type
764 AND c.request_id <> x_request_id
765 ORDER BY p.segment1, c.capital_event_number;
766
767 existing_events_rec existing_events_cur%ROWTYPE;
768
769
770 CURSOR addtl_costs_cur( x_project_id NUMBER,
771 x_capital_event_id NUMBER,
772 x_request_id NUMBER) IS
773 SELECT 'Additional Costs Added'
774 FROM SYS.DUAL
775 WHERE EXISTS
776 (SELECT 'X'
777 FROM pa_expenditure_items_all
778 WHERE project_id = x_project_id
779 AND capital_event_id = x_capital_event_id
780 AND request_id = x_request_id);
781
782 addtl_costs_rec addtl_costs_cur%ROWTYPE;
783
784
785 CURSOR addtl_assets_cur( x_project_id NUMBER,
786 x_capital_event_id NUMBER,
787 x_request_id NUMBER) IS
788 SELECT 'Additional Assets Added'
789 FROM SYS.DUAL
790 WHERE EXISTS
791 (SELECT 'X'
792 FROM pa_project_assets_all
793 WHERE project_id = x_project_id
794 AND capital_event_id = x_capital_event_id
795 AND request_id = x_request_id);
796
797 addtl_assets_rec addtl_assets_cur%ROWTYPE;
798
799
800 CURSOR wbs_branch_tasks_cur(x_parent_task_id NUMBER,
801 x_current_task_id NUMBER,
802 x_event_type VARCHAR2) IS
803 SELECT task_id,
804 task_number
805 FROM pa_tasks
806 WHERE task_id <> x_parent_task_id
807 AND task_id <> x_current_task_id
808 AND NVL(retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
809 CONNECT BY parent_task_id = PRIOR task_id
810 START WITH task_id = x_parent_task_id;
811
812 wbs_branch_tasks_rec wbs_branch_tasks_cur%ROWTYPE;
813
814
815 CURSOR task_asgn_assets_cur(x_project_id NUMBER,
816 x_capital_event_id NUMBER,
817 x_task_id NUMBER,
818 x_event_type VARCHAR2) IS
819 SELECT paa.project_asset_id
820 FROM pa_project_assets_all pa,
821 pa_project_asset_assignments paa
822 WHERE pa.project_asset_id = paa.project_asset_id
823 AND pa.project_id = x_project_id
824 AND paa.project_id = x_project_id
825 AND pa.capital_event_id = x_capital_event_id
826 AND paa.task_id = x_task_id
827 AND pa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT');
828
829 task_asgn_assets_rec task_asgn_assets_cur%ROWTYPE;
830
831
832 v_user_id NUMBER := FND_GLOBAL.user_id;
833 v_login_id NUMBER := FND_GLOBAL.login_id;
834 v_request_id NUMBER := FND_GLOBAL.conc_request_id;
835 v_program_application_id NUMBER := FND_GLOBAL.prog_appl_id;
836 v_program_id NUMBER := FND_GLOBAL.conc_program_id;
837 v_null_rowid ROWID := NULL;
838 -- v_org_id NUMBER := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
839 v_org_id NUMBER := PA_MOAC_UTILS.get_current_org_id ;
840
841 v_common_project VARCHAR2(1);
842 v_project_number pa_projects_all.segment1%TYPE;
843 v_print_project_number pa_projects_all.segment1%TYPE;
844 v_addtl_costs_or_assets VARCHAR2(1);
845 v_first_addtl VARCHAR2(1);
846 v_event_number pa_capital_events.capital_event_number%TYPE;
847 v_new_event_id pa_capital_events.capital_event_id%TYPE := NULL;
848 v_task_number pa_tasks.task_number%TYPE;
849 v_event_exists VARCHAR2(1);
850 v_asset_count NUMBER := 0;
851 v_ei_count NUMBER := 0;
852 v_top_task_id NUMBER := 0;
853 v_return_status VARCHAR2(1);
854 v_msg_count NUMBER := 0;
855 v_msg_data VARCHAR2(2000);
856 v_capital_type pa_lookups.meaning%TYPE;
857 v_wbs_branch_assets_exist VARCHAR2(1) := 'N';
858
859
860 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
861
862 empty_cursor_error EXCEPTION;
863
864 BEGIN
865 --Initialize variables
866 retcode := 0;
867 errbuf := NULL;
868
869
870 --Determine if entire project has a 'Common' Asset Assignment
871 SELECT DECODE(COUNT(*),0,'N','Y')
872 INTO v_common_project
873 FROM pa_project_asset_assignments
874 WHERE project_id = p_project_id
875 AND task_id = 0
876 AND project_asset_id = 0;
877
878
879 --Process events for Common Asset Assignment projects
880 IF v_common_project = 'Y' THEN
881
882 IF PG_DEBUG = 'Y' THEN
883 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Project has a project-level Common Assignment');
884 END IF;
885
886 --Determine if a event exists for the event period specified
887 OPEN capital_event_cur(p_project_id, p_event_type);
888 FETCH capital_event_cur INTO capital_event_rec;
889 IF (capital_event_cur%NOTFOUND) THEN
890 v_event_exists := 'N';
891 ELSE
892 v_event_exists := 'Y';
893 END IF;
894 CLOSE capital_event_cur;
895
896 --Process projects where the event exists for the event period specified
897 IF v_event_exists = 'Y' THEN
898
899 IF PG_DEBUG = 'Y' THEN
900 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'An Event already exists for the period specified, Event ID: '||capital_event_rec.capital_event_id);
901 END IF;
902
903
904 --Assign all new assets to the existing event
905 UPDATE pa_project_assets_all
906 SET capital_event_id = capital_event_rec.capital_event_id,
907 last_update_date = SYSDATE,
908 last_updated_by = v_user_id,
909 request_id = v_request_id,
910 program_application_id = v_program_application_id,
911 program_id = v_program_id,
912 program_update_date = SYSDATE
913 WHERE project_id = p_project_id
914 AND capital_event_id IS NULL
915 AND project_asset_type = DECODE(p_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
916 AND date_placed_in_service <= p_asset_date_through;
917
918
919 --Assign all new costs to the existing event
920 FOR add_ei_tasks_rec IN add_ei_tasks_cur(p_project_id, p_event_type) LOOP
921
922 --Assign all eligible EIs for that Task to the event
923 UPDATE pa_expenditure_items_all
924 SET capital_event_id = capital_event_rec.capital_event_id,
925 last_update_date = SYSDATE,
926 last_updated_by = v_user_id,
927 request_id = v_request_id,
928 program_application_id = v_program_application_id,
929 program_id = v_program_id,
930 program_update_date = SYSDATE
931 WHERE project_id = p_project_id
932 AND billable_flag = DECODE(p_event_type,'C','Y','N')
933 AND capital_event_id IS NULL
934 AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
935 AND revenue_distributed_flag = 'N'
936 AND cost_distributed_flag = 'Y'
937 AND task_id = add_ei_tasks_rec.task_id;
938
939 END LOOP;
940
941
942 ELSE --No event exists for the current event period
943
944 IF PG_DEBUG = 'Y' THEN
945 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'No Event currently exists for the period specified');
946 END IF;
947
948 --Possibly create a new event, if both new assets and new costs exist
949
950 --Count the number of new assets
951 SELECT COUNT(*)
952 INTO v_asset_count
953 FROM pa_project_assets_all
954 WHERE project_id = p_project_id
955 AND capital_event_id IS NULL
956 AND project_asset_type = DECODE(p_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
957 AND date_placed_in_service <= p_asset_date_through;
958
959 --Count the number of new costs (expenditure items)
960
961 /* Commented for the bug 3961059 :
962 SELECT COUNT(*)
963 INTO v_ei_count
964 FROM pa_expenditure_items_all ei,
965 pa_tasks t
966 WHERE ei.project_id = p_project_id
967 AND t.task_id = ei.task_id
968 AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
969 AND ei.billable_flag = DECODE(p_event_type,'C','Y','N')
970 AND ei.capital_event_id IS NULL
971 AND ei.expenditure_item_date <= NVL(p_ei_date_through, ei.expenditure_item_date)
972 AND ei.revenue_distributed_flag = 'N'
973 AND ei.cost_distributed_flag = 'Y';
974 */
975
976
977 /* Added below for bug 3961059 : Use of Exists clause */
978
979 SELECT COUNT(*)
980 INTO v_ei_count
981 From Dual Where Exists
982 (Select 1
983 FROM pa_expenditure_items_all ei,
984 pa_tasks t
985 WHERE ei.project_id = p_project_id
986 AND t.task_id = ei.task_id
987 AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
988 AND ei.billable_flag = DECODE(p_event_type,'C','Y','N')
989 AND ei.capital_event_id IS NULL
990 AND ei.expenditure_item_date <= NVL(p_ei_date_through, ei.expenditure_item_date)
991 AND ei.revenue_distributed_flag = 'N'
992 AND ei.cost_distributed_flag = 'Y'
993 );
994
995
996
997
998 IF (v_asset_count > 0) AND (v_ei_count > 0) THEN
999
1000 --Create a new event, and assign the new costs and assets to it
1001
1002 IF PG_DEBUG = 'Y' THEN
1003 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||v_ei_count||' costs and '||v_asset_count||' assets exist, a new event will be created');
1004 END IF;
1005
1006 --Determine the highest existing event number
1007 SELECT NVL(MAX(capital_event_number),0)
1008 INTO v_event_number
1009 FROM pa_capital_events
1010 WHERE project_id = p_project_id;
1011
1012 --Get the Capital Type meaning
1013 SELECT meaning
1014 INTO v_capital_type
1015 FROM pa_lookups
1016 WHERE lookup_type = 'CAPITAL_TYPE'
1017 AND lookup_code = p_event_type;
1018
1019 --Add one to get the next event number
1020 v_event_number := v_event_number + 1;
1021
1022 --Initialize new event id
1023 v_new_event_id := NULL;
1024
1025 PA_CAPITAL_EVENTS_PKG.INSERT_ROW
1026 (x_rowid => v_null_rowid,
1027 x_capital_event_id => v_new_event_id,
1028 x_project_id => p_project_id,
1029 x_capital_event_number => v_event_number,
1030 x_event_type => p_event_type,
1031 x_event_name => p_event_period_name||' '||v_capital_type,
1032 x_asset_allocation_method => p_asset_allocation_method,
1033 x_event_period => p_event_period_name,
1034 x_last_update_date => SYSDATE,
1035 x_last_updated_by => v_user_id,
1036 x_creation_date => SYSDATE,
1037 x_created_by => v_user_id,
1038 x_last_update_login => v_login_id,
1039 x_request_id => v_request_id,
1040 x_program_application_id => v_program_application_id,
1041 x_program_id => v_program_id,
1042 x_program_update_date => SYSDATE);
1043
1044
1045 --Retrieve the newly created capital event id
1046 OPEN capital_event_cur(p_project_id, p_event_type);
1047 FETCH capital_event_cur INTO capital_event_rec;
1048 IF (capital_event_cur%NOTFOUND) THEN
1049 CLOSE capital_event_cur;
1050 RAISE empty_cursor_error;
1051 END IF;
1052 CLOSE capital_event_cur;
1053
1054 --Assign all new assets to the existing event
1055 UPDATE pa_project_assets_all
1056 SET capital_event_id = capital_event_rec.capital_event_id,
1057 last_update_date = SYSDATE,
1058 last_updated_by = v_user_id,
1059 request_id = v_request_id,
1060 program_application_id = v_program_application_id,
1061 program_id = v_program_id,
1062 program_update_date = SYSDATE
1063 WHERE project_id = p_project_id
1064 AND capital_event_id IS NULL
1065 AND project_asset_type = DECODE(p_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
1066 AND date_placed_in_service <= p_asset_date_through;
1067
1068 --Assign all new costs to the existing event
1069 FOR add_ei_tasks_rec IN add_ei_tasks_cur(p_project_id, p_event_type) LOOP
1070
1071 --Assign all eligible EIs for that Task to the event
1072 UPDATE pa_expenditure_items_all
1073 SET capital_event_id = capital_event_rec.capital_event_id,
1074 last_update_date = SYSDATE,
1075 last_updated_by = v_user_id,
1076 request_id = v_request_id,
1077 program_application_id = v_program_application_id,
1078 program_id = v_program_id,
1079 program_update_date = SYSDATE
1080 WHERE project_id = p_project_id
1081 AND billable_flag = DECODE(p_event_type,'C','Y','N')
1082 AND capital_event_id IS NULL
1083 AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
1084 AND revenue_distributed_flag = 'N'
1085 AND cost_distributed_flag = 'Y'
1086 AND task_id = add_ei_tasks_rec.task_id;
1087
1088 END LOOP;
1089
1090 IF PG_DEBUG = 'Y' THEN
1091 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Event number '||v_event_number||' successfully created for Project: '||p_project_number);
1092 END IF;
1093
1094
1095 ELSIF (v_asset_count = 0) AND (v_ei_count = 0) THEN
1096
1097 --Print warning message
1098 INSERT INTO pa_cap_event_creation_v
1099 (request_id,
1100 module,
1101 context,
1102 sub_context,
1103 capital_type,
1104 project_id,
1105 project_number,
1106 project_asset_id,
1107 asset_name,
1108 task_id,
1109 task_number,
1110 capital_event_id,
1111 capital_event_number,
1112 event_name,
1113 event_type,
1114 message_code,
1115 created_by,
1116 creation_date,
1117 org_id
1118 )
1119 VALUES
1120 (v_request_id,
1121 'PERIODIC_EVENT_CREATION', --module
1122 '1', --context (1 = Message)
1123 'P', --sub_context
1124 p_event_type, --capital_type,
1125 p_project_id, --project_id,
1126 p_project_number, --project_number,
1127 NULL, --project_asset_id,
1128 NULL, --asset_name,
1129 NULL, --task_id,
1130 NULL, --task_number,
1131 NULL, --capital_event_id,
1132 NULL, --capital_event_number,
1133 NULL, --event_name,
1134 NULL, --event_type,
1135 'NO_ASSETS_OR_COSTS_PROJ',
1136 v_user_id, --created_by,
1137 SYSDATE, --creation_date,
1138 v_org_id --org_id
1139 );
1140
1141
1142 IF PG_DEBUG = 'Y' THEN
1143 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||v_ei_count||' costs and '||v_asset_count||' assets exist, no new event will be created');
1144 END IF;
1145
1146 ELSIF (v_asset_count > 0) AND (v_ei_count = 0) THEN
1147
1148 --Print warning message
1149 INSERT INTO pa_cap_event_creation_v
1150 (request_id,
1151 module,
1152 context,
1153 sub_context,
1154 capital_type,
1155 project_id,
1156 project_number,
1157 project_asset_id,
1158 asset_name,
1159 task_id,
1160 task_number,
1161 capital_event_id,
1162 capital_event_number,
1163 event_name,
1164 event_type,
1165 message_code,
1166 created_by,
1167 creation_date,
1168 org_id
1169 )
1170 VALUES
1171 (v_request_id,
1172 'PERIODIC_EVENT_CREATION', --module
1173 '1', --context (1 = Message)
1174 'P', --sub_context
1175 p_event_type, --capital_type,
1176 p_project_id, --project_id,
1177 p_project_number, --project_number,
1178 NULL, --project_asset_id,
1179 NULL, --asset_name,
1180 NULL, --task_id,
1181 NULL, --task_number,
1182 NULL, --capital_event_id,
1183 NULL, --capital_event_number,
1184 NULL, --event_name,
1185 NULL, --event_type,
1186 'ASSETS_BUT_NO_COSTS_PROJ',
1187 v_user_id, --created_by,
1188 SYSDATE, --creation_date,
1189 v_org_id --org_id
1190 );
1191
1192 IF PG_DEBUG = 'Y' THEN
1193 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||v_ei_count||' costs and '||v_asset_count||' assets exist, no new event will be created');
1194 END IF;
1195
1196 ELSIF (v_asset_count = 0) AND (v_ei_count > 0) THEN
1197
1198 --Print warning message
1199 INSERT INTO pa_cap_event_creation_v
1200 (request_id,
1201 module,
1202 context,
1203 sub_context,
1204 capital_type,
1205 project_id,
1206 project_number,
1207 project_asset_id,
1208 asset_name,
1209 task_id,
1210 task_number,
1211 capital_event_id,
1212 capital_event_number,
1213 event_name,
1214 event_type,
1215 message_code,
1216 created_by,
1217 creation_date,
1218 org_id
1219 )
1220 VALUES
1221 (v_request_id,
1222 'PERIODIC_EVENT_CREATION', --module
1223 '1', --context (1 = Message)
1224 'P', --sub_context
1225 p_event_type, --capital_type,
1226 p_project_id, --project_id,
1227 p_project_number, --project_number,
1228 NULL, --project_asset_id,
1229 NULL, --asset_name,
1230 NULL, --task_id,
1231 NULL, --task_number,
1232 NULL, --capital_event_id,
1233 NULL, --capital_event_number,
1234 NULL, --event_name,
1235 NULL, --event_type,
1236 'COSTS_BUT_NO_ASSETS_PROJ',
1237 v_user_id, --created_by,
1238 SYSDATE, --creation_date,
1239 v_org_id --org_id
1240 );
1241
1242
1243 IF PG_DEBUG = 'Y' THEN
1244 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||v_ei_count||' costs and '||v_asset_count||' assets exist, no new event will be created');
1245 END IF;
1246
1247 END IF; --Processing based on asset and cost item counts
1248
1249 END IF; --Event existence test for 'Common' projects
1250
1251 ELSE --Process events for projects that do not have 'Common' Asset Assignments
1252
1253
1254 IF PG_DEBUG = 'Y' THEN
1255 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Project does not have a project-level Common Assignment');
1256 END IF;
1257
1258 --Loop through all new assets and their asset assignments for the project
1259 FOR new_assets_rec IN new_assets_cur(p_project_id, p_event_type) LOOP
1260
1261 --Determine if a event exists for the event period specified
1262 OPEN capital_event_cur(p_project_id, p_event_type);
1263 FETCH capital_event_cur INTO capital_event_rec;
1264 IF (capital_event_cur%NOTFOUND) THEN
1265 v_event_exists := 'N';
1266 ELSE
1267 v_event_exists := 'Y';
1268 END IF;
1269 CLOSE capital_event_cur;
1270
1271 --Process projects where the event exists for the event period specified
1272 IF v_event_exists = 'Y' THEN
1273
1274 IF PG_DEBUG = 'Y' THEN
1275 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'An Event already exists for the period specified, Event ID: '||capital_event_rec.capital_event_id);
1276 END IF;
1277
1278 --Look for costs in the event at the asset grouping level
1279 IF new_assets_rec.task_id = 0 THEN
1280
1281 --Look for costs in the event at the PROJECT grouping level (i.e., ANY expenditure items)
1282 /* Commented for bug 3961059
1283 SELECT COUNT(*)
1284 INTO v_ei_count
1285 FROM pa_expenditure_items_all
1286 WHERE capital_event_id = capital_event_rec.capital_event_id
1287 AND project_id = p_project_id;
1288 */
1289
1290 /* Added below for bug 3961059 : Use of Exists clause */
1291
1292 SELECT COUNT(*)
1293 INTO v_ei_count
1294 From Dual Where Exists
1295 (Select 1
1296 FROM pa_expenditure_items_all
1297 WHERE capital_event_id = capital_event_rec.capital_event_id
1298 AND project_id = p_project_id
1299 );
1300
1301
1302
1303 ELSE --new_assets_rec.task_id <> 0
1304
1305 --Look for costs in the event at the TASK or TOP TASK grouping level
1306 /* Commented for bug 3961059
1307 SELECT COUNT(*)
1308 INTO v_ei_count
1309 FROM pa_expenditure_items_all peia,
1310 pa_tasks t
1311 WHERE peia.task_id = t.task_id
1312 AND peia.capital_event_id = capital_event_rec.capital_event_id
1313 AND peia.project_id = p_project_id
1314 AND (new_assets_rec.task_id = t.task_id
1315 OR new_assets_rec.task_id = t.top_task_id);
1316 */
1317
1318 /* Added Below for bug 3961059 : Use of Exists clause */
1319
1320 SELECT COUNT(*)
1321 INTO v_ei_count
1322 From Dual Where Exists
1323 ( Select 1
1324 FROM pa_expenditure_items_all peia,
1325 pa_tasks t
1326 WHERE peia.task_id = t.task_id
1327 AND peia.capital_event_id = capital_event_rec.capital_event_id
1328 AND peia.project_id = p_project_id
1329 AND (new_assets_rec.task_id = t.task_id
1330 OR new_assets_rec.task_id = t.top_task_id)
1331 );
1332
1333
1334 END IF;
1335
1336 IF v_ei_count > 0 THEN --costs already exist on the event
1337
1338 IF PG_DEBUG = 'Y' THEN
1339 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Costs already exists on the Event, add asset id: '||new_assets_rec.project_asset_id);
1340 END IF;
1341
1342 --Add the asset to the event
1343 UPDATE pa_project_assets_all
1344 SET capital_event_id = capital_event_rec.capital_event_id,
1345 last_update_date = SYSDATE,
1346 last_updated_by = v_user_id,
1347 request_id = v_request_id,
1348 program_application_id = v_program_application_id,
1349 program_id = v_program_id,
1350 program_update_date = SYSDATE
1351 WHERE project_asset_id = new_assets_rec.project_asset_id
1352 AND capital_event_id IS NULL;
1353
1354 ELSE
1355 --Look for NEW costs at the asset assignment grouping level
1356 IF new_assets_rec.task_id = 0 THEN
1357
1358 --Look for new costs at the PROJECT grouping level (i.e., ANY expenditure items)
1359 SELECT COUNT(*)
1360 INTO v_ei_count
1361 FROM pa_expenditure_items_all peia,
1362 pa_tasks t
1363 WHERE peia.project_id = p_project_id
1364 AND peia.task_id = t.task_id
1365 AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
1366 AND peia.billable_flag = DECODE(p_event_type,'C','Y','N')
1367 AND peia.capital_event_id IS NULL
1368 AND peia.expenditure_item_date <= NVL(p_ei_date_through, peia.expenditure_item_date)
1369 AND peia.revenue_distributed_flag = 'N'
1370 AND peia.cost_distributed_flag = 'Y';
1371
1372 ELSE --new_assets_rec.task_id <> 0
1373
1374 --Look for new costs at the TASK or TOP TASK grouping level
1375 SELECT COUNT(*)
1376 INTO v_ei_count
1377 FROM pa_expenditure_items_all peia,
1378 pa_tasks t
1379 WHERE peia.task_id = t.task_id
1380 AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
1381 AND peia.project_id = p_project_id
1382 AND peia.billable_flag = DECODE(p_event_type,'C','Y','N')
1383 AND peia.capital_event_id IS NULL
1384 AND peia.expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
1385 AND peia.revenue_distributed_flag = 'N'
1386 AND peia.cost_distributed_flag = 'Y'
1387 AND (new_assets_rec.task_id = t.task_id
1388 OR new_assets_rec.task_id = t.top_task_id);
1389 END IF;
1390
1391
1392 IF v_ei_count > 0 THEN
1393
1394 IF PG_DEBUG = 'Y' THEN
1395 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'New costs and assets exist for the Event, add asset id: '||new_assets_rec.project_asset_id);
1396 END IF;
1397
1398
1399 --Add the asset to the event
1400 UPDATE pa_project_assets_all
1401 SET capital_event_id = capital_event_rec.capital_event_id,
1402 last_update_date = SYSDATE,
1403 last_updated_by = v_user_id,
1404 request_id = v_request_id,
1405 program_application_id = v_program_application_id,
1406 program_id = v_program_id,
1407 program_update_date = SYSDATE
1408 WHERE project_asset_id = new_assets_rec.project_asset_id
1409 AND capital_event_id IS NULL;
1410
1411 --Add the new costs to the event
1412 UPDATE pa_expenditure_items_all
1413 SET capital_event_id = capital_event_rec.capital_event_id,
1414 last_update_date = SYSDATE,
1415 last_updated_by = v_user_id,
1416 request_id = v_request_id,
1417 program_application_id = v_program_application_id,
1418 program_id = v_program_id,
1419 program_update_date = SYSDATE
1420 WHERE project_id = p_project_id
1421 AND capital_event_id IS NULL
1422 AND billable_flag = DECODE(p_event_type,'C','Y','N')
1423 AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
1424 AND revenue_distributed_flag = 'N'
1425 AND cost_distributed_flag = 'Y'
1426 AND (
1427 ((new_assets_rec.task_id = 0) AND task_id IN
1428 (SELECT task_id
1429 FROM pa_tasks
1430 WHERE project_id = p_project_id
1431 AND NVL(retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')))
1432 OR (task_id = new_assets_rec.task_id
1433 AND NVL(new_assets_rec.task_retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N'))
1434 OR task_id IN
1435 (SELECT task_id
1436 FROM pa_tasks
1437 WHERE top_task_id = new_assets_rec.task_id
1438 AND NVL(retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N'))
1439 );
1440 END IF; --New costs exist
1441
1442 END IF; --Processing for when the event already exists
1443
1444 ELSE --No event exists as yet
1445
1446 IF PG_DEBUG = 'Y' THEN
1447 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'No Event currently exists for period specified');
1448 END IF;
1449
1450
1451 --Look for new costs at the asset assignment grouping level
1452 IF new_assets_rec.task_id = 0 THEN
1453
1454 --Look for new costs at the PROJECT grouping level (i.e., ANY expenditure items)
1455 SELECT COUNT(*)
1456 INTO v_ei_count
1457 FROM pa_expenditure_items_all peia,
1458 pa_tasks t
1459 WHERE peia.project_id = p_project_id
1460 AND peia.task_id = t.task_id
1461 AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
1462 AND peia.billable_flag = DECODE(p_event_type,'C','Y','N')
1463 AND peia.capital_event_id IS NULL
1464 AND peia.expenditure_item_date <= NVL(p_ei_date_through, peia.expenditure_item_date)
1465 AND peia.revenue_distributed_flag = 'N'
1466 AND peia.cost_distributed_flag = 'Y';
1467
1468 ELSE --new_assets_rec.task_id <> 0
1469
1470 --Look for new costs at the TASK or TOP TASK grouping level
1471 SELECT COUNT(*)
1472 INTO v_ei_count
1473 FROM pa_expenditure_items_all peia,
1474 pa_tasks t
1475 WHERE peia.task_id = t.task_id
1476 AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
1477 AND peia.project_id = p_project_id
1478 AND peia.billable_flag = DECODE(p_event_type,'C','Y','N')
1479 AND peia.capital_event_id IS NULL
1480 AND peia.expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
1481 AND peia.revenue_distributed_flag = 'N'
1482 AND peia.cost_distributed_flag = 'Y'
1483 AND (new_assets_rec.task_id = t.task_id
1484 OR new_assets_rec.task_id = t.top_task_id);
1485 END IF;
1486
1487
1488 IF v_ei_count > 0 THEN
1489
1490 --Create a new event, and assign the new costs and assets to it
1491 IF PG_DEBUG = 'Y' THEN
1492 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'New costs and assets exist, create a new Event');
1493 END IF;
1494
1495
1496 --Determine the highest existing event number
1497 SELECT NVL(MAX(capital_event_number),0)
1498 INTO v_event_number
1499 FROM pa_capital_events
1500 WHERE project_id = p_project_id;
1501
1502 --Get the Capital Type meaning
1503 SELECT meaning
1504 INTO v_capital_type
1505 FROM pa_lookups
1506 WHERE lookup_type = 'CAPITAL_TYPE'
1507 AND lookup_code = p_event_type;
1508
1509 --Add one to get the next event number
1510 v_event_number := v_event_number + 1;
1511
1512 --Initialize new event id
1513 v_new_event_id := NULL;
1514
1515 PA_CAPITAL_EVENTS_PKG.INSERT_ROW
1516 (x_rowid => v_null_rowid,
1517 x_capital_event_id => v_new_event_id,
1518 x_project_id => p_project_id,
1519 x_capital_event_number => v_event_number,
1520 x_event_type => p_event_type,
1521 x_event_name => p_event_period_name||' '||v_capital_type,
1522 x_asset_allocation_method => p_asset_allocation_method,
1523 x_event_period => p_event_period_name,
1524 x_last_update_date => SYSDATE,
1525 x_last_updated_by => v_user_id,
1526 x_creation_date => SYSDATE,
1527 x_created_by => v_user_id,
1528 x_last_update_login => v_login_id,
1529 x_request_id => v_request_id,
1530 x_program_application_id => v_program_application_id,
1531 x_program_id => v_program_id,
1532 x_program_update_date => SYSDATE);
1533
1534
1535 --Retrieve the newly created capital event id
1536 OPEN capital_event_cur(p_project_id, p_event_type);
1537 FETCH capital_event_cur INTO capital_event_rec;
1538 IF (capital_event_cur%NOTFOUND) THEN
1539 CLOSE capital_event_cur;
1540 RAISE empty_cursor_error;
1541 END IF;
1542 CLOSE capital_event_cur;
1543
1544
1545 --Add the asset to the event
1546 UPDATE pa_project_assets_all
1547 SET capital_event_id = capital_event_rec.capital_event_id,
1548 last_update_date = SYSDATE,
1549 last_updated_by = v_user_id,
1550 request_id = v_request_id,
1551 program_application_id = v_program_application_id,
1552 program_id = v_program_id,
1553 program_update_date = SYSDATE
1554 WHERE project_asset_id = new_assets_rec.project_asset_id
1555 AND capital_event_id IS NULL;
1556
1557
1558 --Add the new costs to the event
1559 UPDATE pa_expenditure_items_all
1560 SET capital_event_id = capital_event_rec.capital_event_id,
1561 last_update_date = SYSDATE,
1562 last_updated_by = v_user_id,
1563 request_id = v_request_id,
1564 program_application_id = v_program_application_id,
1565 program_id = v_program_id,
1566 program_update_date = SYSDATE
1567 WHERE project_id = p_project_id
1568 AND capital_event_id IS NULL
1569 AND billable_flag = DECODE(p_event_type,'C','Y','N')
1570 AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
1571 AND revenue_distributed_flag = 'N'
1572 AND cost_distributed_flag = 'Y'
1573 AND (
1574 ((new_assets_rec.task_id = 0) AND task_id IN
1575 (SELECT task_id
1576 FROM pa_tasks
1577 WHERE project_id = p_project_id
1578 AND NVL(retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')))
1579 OR (task_id = new_assets_rec.task_id
1580 AND NVL(new_assets_rec.task_retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N'))
1581 OR task_id IN
1582 (SELECT task_id
1583 FROM pa_tasks
1584 WHERE top_task_id = new_assets_rec.task_id
1585 AND NVL(retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N'))
1586 );
1587
1588 IF PG_DEBUG = 'Y' THEN
1589 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Event number '||v_event_number||' successfully created for Project: '||p_project_number);
1590 END IF;
1591
1592 END IF; --New costs existed for the asset and event was created
1593
1594 END IF; --No event existed for the asset
1595
1596 END LOOP; --New Assets
1597
1598
1599 --Check to see if a event now exists, and add appropriate new costs to it
1600 OPEN capital_event_cur(p_project_id, p_event_type);
1601 FETCH capital_event_cur INTO capital_event_rec;
1602 IF (capital_event_cur%NOTFOUND) THEN
1603 v_event_exists := 'N';
1604 ELSE
1605 v_event_exists := 'Y';
1606 END IF;
1607 CLOSE capital_event_cur;
1608
1609
1610 IF v_event_exists = 'Y' THEN
1611
1612 --Any Top Task Level Common Assignment costs can be added to the event
1613 --Lowest Level Common Assignment costs can be added to the event only if at
1614 --least one asset exists in the event that is assigned to a task beneath the
1615 --same parent task as the common assignment (i.e., assigned within the same
1616 --branch of the WBS). For instance, if a common assignment is made to task 2.1,
1617 --then the costs can be included if there is an asset assigned to task 2.2, 2.3,
1618 --2.4.1, 2.4.2, 2.5, and so on. But not if there are only assets assigned to task 3.0,
1619 --4.1, 4.2, since those reside outside of the WBS branch of the common assignment,
1620 --since they are not beneath the parent task of task 2.1, which is 2.0.
1621 FOR common_tasks_rec IN common_tasks_cur(p_project_id, p_event_type) LOOP
1622
1623 --Test if the Common Assignment is made at the Top Task Level
1624 IF common_tasks_rec.assignment_task_id = common_tasks_rec.top_task_id THEN
1625
1626 IF PG_DEBUG = 'Y' THEN
1627 PA_DEBUG.DEBUG('Common Task assignment made at Top Task Level for Task ID '||common_tasks_rec.task_id);
1628 END IF;
1629
1630 --Attach all common costs to the event associated with a Top Task Assignment
1631 UPDATE pa_expenditure_items_all
1632 SET capital_event_id = capital_event_rec.capital_event_id,
1633 last_update_date = SYSDATE,
1634 last_updated_by = v_user_id,
1635 request_id = v_request_id,
1636 program_application_id = v_program_application_id,
1637 program_id = v_program_id,
1638 program_update_date = SYSDATE
1639 WHERE project_id = p_project_id
1640 AND capital_event_id IS NULL
1641 AND billable_flag = DECODE(p_event_type,'C','Y','N')
1642 AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
1643 AND revenue_distributed_flag = 'N'
1644 AND cost_distributed_flag = 'Y'
1645 AND task_id = common_tasks_rec.task_id;
1646
1647 IF SQL%ROWCOUNT > 0 THEN
1648 IF PG_DEBUG = 'Y' THEN
1649 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'New common costs added to Event number '||v_event_number);
1650 END IF;
1651 END IF;
1652 ELSE --Common Task is assigned at the Lowest Level
1653
1654 IF PG_DEBUG = 'Y' THEN
1655 PA_DEBUG.DEBUG('Common Task assignment made at Lowest Task Level for Task ID '||common_tasks_rec.task_id
1656 ||' under Parent Task ID '||common_tasks_rec.parent_task_id);
1657 END IF;
1658
1659 v_wbs_branch_assets_exist := 'N';
1660
1661 --Attach all costs where asset assignment(s) exist at or beneath the Parent (not Top) Task
1662 FOR wbs_branch_tasks_rec IN wbs_branch_tasks_cur(common_tasks_rec.parent_task_id,
1663 common_tasks_rec.task_id,
1664 p_event_type) LOOP
1665
1666 IF PG_DEBUG = 'Y' THEN
1667 PA_DEBUG.DEBUG('Task Number '||wbs_branch_tasks_rec.task_number||' exists beneath Parent Task ID '||common_tasks_rec.parent_task_id);
1668 END IF;
1669
1670 --Check for existence of asset assignments in current event on current task
1671 OPEN task_asgn_assets_cur(p_project_id,
1672 capital_event_rec.capital_event_id,
1673 wbs_branch_tasks_rec.task_id,
1674 p_event_type);
1675 FETCH task_asgn_assets_cur INTO task_asgn_assets_rec;
1676 IF task_asgn_assets_cur%NOTFOUND THEN
1677 IF PG_DEBUG = 'Y' THEN
1678 PA_DEBUG.DEBUG('No assignments exist for current event for Task Number '||wbs_branch_tasks_rec.task_number);
1679 END IF;
1680 ELSE
1681 v_wbs_branch_assets_exist := 'Y';
1682 IF PG_DEBUG = 'Y' THEN
1683 PA_DEBUG.DEBUG('Assignments exist for current event for Task Number '||wbs_branch_tasks_rec.task_number);
1684 END IF;
1685 END IF;
1686
1687 CLOSE task_asgn_assets_cur;
1688
1689 END LOOP; --WBS Branch Tasks
1690
1691 IF v_wbs_branch_assets_exist = 'Y' THEN
1692
1693 --Attach all common costs to the event for the current task
1694 UPDATE pa_expenditure_items_all
1695 SET capital_event_id = capital_event_rec.capital_event_id,
1696 last_update_date = SYSDATE,
1697 last_updated_by = v_user_id,
1698 request_id = v_request_id,
1699 program_application_id = v_program_application_id,
1700 program_id = v_program_id,
1701 program_update_date = SYSDATE
1702 WHERE project_id = p_project_id
1703 AND capital_event_id IS NULL
1704 AND billable_flag = DECODE(p_event_type,'C','Y','N')
1705 AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
1706 AND revenue_distributed_flag = 'N'
1707 AND cost_distributed_flag = 'Y'
1708 AND task_id = common_tasks_rec.task_id; --NOTE: We are attaching the costs under the original task,
1709 --based on the existence of asset assignments on OTHER tasks beneath the same parent (within
1710 --the same WBS branch)
1711 END IF; --WBS Branch Asset Assignments exist for Task in current Event
1712
1713 END IF; --Test for Top or Lowest Task Assignment
1714
1715 END LOOP; --Common Tasks
1716
1717
1718 --Any new costs that match any existing asset grouping levels can be added to the event
1719 FOR remaining_costs_rec IN remaining_costs_cur(p_project_id, p_event_type) LOOP
1720
1721 --Get the Top Task ID
1722 SELECT top_task_id
1723 INTO v_top_task_id
1724 FROM pa_tasks
1725 WHERE task_id = remaining_costs_rec.task_id;
1726
1727 --Look for assets in the event at the grouping level of the new costs
1728
1729 /* Commented for bug 3961059 */
1730 SELECT COUNT(*)
1731 INTO v_asset_count
1732 FROM pa_project_assets_all pa,
1733 pa_project_asset_assignments paa
1734 WHERE pa.project_asset_id = paa.project_asset_id
1735 AND pa.capital_event_id = capital_event_rec.capital_event_id
1736 AND (paa.task_id = remaining_costs_rec.task_id
1737 OR paa.task_id = v_top_task_id
1738 OR paa.task_id = 0);
1739
1740 /* Added below for bug 3961059 :Use of Exists clause */
1741
1742 SELECT COUNT(*)
1743 INTO v_asset_count
1744 From Dual Where Exists
1745 (Select 1
1746 FROM pa_project_assets_all pa,
1747 pa_project_asset_assignments paa
1748 WHERE pa.project_asset_id = paa.project_asset_id
1749 AND pa.capital_event_id = capital_event_rec.capital_event_id
1750 AND (paa.task_id = remaining_costs_rec.task_id
1751 OR paa.task_id = v_top_task_id
1752 OR paa.task_id = 0)
1753 );
1754
1755 IF v_asset_count > 0 THEN
1756
1757 --Add new costs to the event for the current task
1758 UPDATE pa_expenditure_items_all
1759 SET capital_event_id = capital_event_rec.capital_event_id,
1760 last_update_date = SYSDATE,
1761 last_updated_by = v_user_id,
1762 request_id = v_request_id,
1763 program_application_id = v_program_application_id,
1764 program_id = v_program_id,
1765 program_update_date = SYSDATE
1766 WHERE project_id = p_project_id
1767 AND capital_event_id IS NULL
1768 AND billable_flag = DECODE(p_event_type,'C','Y','N')
1769 AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
1770 AND revenue_distributed_flag = 'N'
1771 AND cost_distributed_flag = 'Y'
1772 AND task_id = remaining_costs_rec.task_id;
1773
1774 IF PG_DEBUG = 'Y' THEN
1775 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'New costs added to Event number '||v_event_number||' from task id '||remaining_costs_rec.task_id);
1776 END IF;
1777
1778 END IF; --Remaining costs match an existing asset grouping level for the event
1779
1780 END LOOP; --Remaining Costs Tasks
1781
1782
1783 END IF; --Event now exists for project
1784
1785
1786 --Loop through any tasks that still have new costs, and print a warning message for each task
1787 FOR remaining_costs_rec IN remaining_costs_cur(p_project_id, p_event_type) LOOP
1788
1789 --Get the task number
1790 SELECT task_number
1791 INTO v_task_number
1792 FROM pa_tasks
1793 WHERE task_id = remaining_costs_rec.task_id;
1794
1795 --Print warning message
1796 INSERT INTO pa_cap_event_creation_v
1797 (request_id,
1798 module,
1799 context,
1800 sub_context,
1801 capital_type,
1802 project_id,
1803 project_number,
1804 project_asset_id,
1805 asset_name,
1806 task_id,
1807 task_number,
1808 capital_event_id,
1809 capital_event_number,
1810 event_name,
1811 event_type,
1812 message_code,
1813 created_by,
1814 creation_date,
1815 org_id
1816 )
1817 VALUES
1818 (v_request_id,
1819 'PERIODIC_EVENT_CREATION', --module
1820 '1', --context (1 = Message)
1821 'T', --sub_context
1822 p_event_type, --capital_type,
1823 p_project_id, --project_id,
1824 p_project_number, --project_number,
1825 NULL, --project_asset_id,
1826 NULL, --asset_name,
1827 remaining_costs_rec.task_id, --task_id,
1828 v_task_number, --task_number,
1829 NULL, --capital_event_id,
1830 NULL, --capital_event_number,
1831 NULL, --event_name,
1832 NULL, --event_type,
1833 'TASK_COSTS_BUT_NO_ASSETS',
1834 v_user_id, --created_by,
1835 SYSDATE, --creation_date,
1836 v_org_id --org_id
1837 );
1838
1839 IF PG_DEBUG = 'Y' THEN
1840 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Costs but no corresponding assets exist on Task Number '||v_task_number
1841 ||' for Project: '||p_project_number);
1842 END IF;
1843
1844 END LOOP; --Remaining Costs Tasks
1845
1846
1847 --Loop through any new assets that remain for the project, and print a warning message for each asset
1848 FOR remaining_assets_rec IN remaining_assets_cur(p_project_id, p_event_type) LOOP
1849
1850 IF remaining_assets_rec.task_id IS NULL THEN
1851
1852 --Print warning message
1853 INSERT INTO pa_cap_event_creation_v
1854 (request_id,
1855 module,
1856 context,
1857 sub_context,
1858 capital_type,
1859 project_id,
1860 project_number,
1861 project_asset_id,
1862 asset_name,
1863 task_id,
1864 task_number,
1865 capital_event_id,
1866 capital_event_number,
1867 event_name,
1868 event_type,
1869 message_code,
1870 created_by,
1871 creation_date,
1872 org_id
1873 )
1874 VALUES
1875 (v_request_id,
1876 'PERIODIC_EVENT_CREATION', --module
1877 '1', --context (1 = Message)
1878 'A', --sub_context
1879 p_event_type, --capital_type,
1880 p_project_id, --project_id,
1881 p_project_number, --project_number,
1882 remaining_assets_rec.project_asset_id, --project_asset_id,
1883 remaining_assets_rec.asset_name, --asset_name,
1884 NULL, --task_id,
1885 NULL, --task_number,
1886 NULL, --capital_event_id,
1887 NULL, --capital_event_number,
1888 NULL, --event_name,
1889 NULL, --event_type,
1890 'ASSET_WITH_NO_ASSIGNMENT',
1891 v_user_id, --created_by,
1892 SYSDATE, --creation_date,
1893 v_org_id --org_id
1894 );
1895
1896 IF PG_DEBUG = 'Y' THEN
1897 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': Asset '||remaining_assets_rec.asset_name
1898 ||' found with no asset assignment(s) on Project: '||p_project_number);
1899 END IF;
1900
1901 ELSIF remaining_assets_rec.task_id = 0 THEN
1902
1903 --Print warning message
1904 INSERT INTO pa_cap_event_creation_v
1905 (request_id,
1906 module,
1907 context,
1908 sub_context,
1909 capital_type,
1910 project_id,
1911 project_number,
1912 project_asset_id,
1913 asset_name,
1914 task_id,
1915 task_number,
1916 capital_event_id,
1917 capital_event_number,
1918 event_name,
1919 event_type,
1920 message_code,
1921 created_by,
1922 creation_date,
1923 org_id
1924 )
1925 VALUES
1926 (v_request_id,
1927 'PERIODIC_EVENT_CREATION', --module
1928 '1', --context (1 = Message)
1929 'A', --sub_context
1930 p_event_type, --capital_type,
1931 p_project_id, --project_id,
1932 p_project_number, --project_number,
1933 remaining_assets_rec.project_asset_id, --project_asset_id,
1934 remaining_assets_rec.asset_name, --asset_name,
1935 NULL, --task_id,
1936 NULL, --task_number,
1937 NULL, --capital_event_id,
1938 NULL, --capital_event_number,
1939 NULL, --event_name,
1940 NULL, --event_type,
1941 'ASSET_WITH_NO_COSTS',
1942 v_user_id, --created_by,
1943 SYSDATE, --creation_date,
1944 v_org_id --org_id
1945 );
1946
1947 IF PG_DEBUG = 'Y' THEN
1948 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': Asset '||remaining_assets_rec.asset_name
1949 ||' found but no costs exist on Project: '||p_project_number);
1950 END IF;
1951
1952 ELSIF remaining_assets_rec.task_id <> 0 THEN
1953
1954 --Get the task number
1955 SELECT task_number
1956 INTO v_task_number
1957 FROM pa_tasks
1958 WHERE task_id = remaining_assets_rec.task_id;
1959
1960 --Print warning message
1961 INSERT INTO pa_cap_event_creation_v
1962 (request_id,
1963 module,
1964 context,
1965 sub_context,
1966 capital_type,
1967 project_id,
1968 project_number,
1969 project_asset_id,
1970 asset_name,
1971 task_id,
1972 task_number,
1973 capital_event_id,
1974 capital_event_number,
1975 event_name,
1976 event_type,
1977 message_code,
1978 created_by,
1979 creation_date,
1980 org_id
1981 )
1982 VALUES
1983 (v_request_id,
1984 'PERIODIC_EVENT_CREATION', --module
1985 '1', --context (1 = Message)
1986 'AT', --sub_context
1987 p_event_type, --capital_type,
1988 p_project_id, --project_id,
1989 p_project_number, --project_number,
1990 remaining_assets_rec.project_asset_id, --project_asset_id,
1991 remaining_assets_rec.asset_name, --asset_name,
1992 remaining_assets_rec.task_id, --task_id,
1993 v_task_number, --task_number,
1994 NULL, --capital_event_id,
1995 NULL, --capital_event_number,
1996 NULL, --event_name,
1997 NULL, --event_type,
1998 'ASSET_WITH_NO_COSTS_FOR_TASK',
1999 v_user_id, --created_by,
2000 SYSDATE, --creation_date,
2001 v_org_id --org_id
2002 );
2003
2004 IF PG_DEBUG = 'Y' THEN
2005 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': Asset '||remaining_assets_rec.asset_name
2006 ||' found but no costs exist beneath Task Number '||v_task_number
2007 ||' for Project: '||p_project_number);
2008 END IF;
2009
2010 END IF; --Remaining asset task assignments
2011
2012 END LOOP; --Remaining Assets
2013
2014
2015 END IF; --Projects with Common vs. not Common project-level asset assignments
2016
2017
2018
2019 --Print results in control report
2020
2021 --Check to see if any new events were created
2022 OPEN print_events_cur(v_request_id, p_project_id, p_event_type);
2023 FETCH print_events_cur INTO print_events_rec;
2024 IF (print_events_cur%NOTFOUND) THEN
2025
2026 --No new events were created
2027 INSERT INTO pa_cap_event_creation_v
2028 (request_id,
2029 module,
2030 context,
2031 sub_context,
2032 capital_type,
2033 project_id,
2034 project_number,
2035 project_asset_id,
2036 asset_name,
2037 task_id,
2038 task_number,
2039 capital_event_id,
2040 capital_event_number,
2041 event_name,
2042 event_type,
2043 message_code,
2044 created_by,
2045 creation_date,
2046 org_id
2047 )
2048 VALUES
2049 (v_request_id,
2050 'PERIODIC_EVENT_CREATION', --module
2051 '2', --context (2 = New Event Creation)
2052 'NE', --sub_context
2053 p_event_type, --capital_type,
2054 p_project_id, --project_id,
2055 p_project_number, --project_number,
2056 NULL, --project_asset_id,
2057 NULL, --asset_name,
2058 NULL, --task_id,
2059 NULL, --task_number,
2060 NULL, --capital_event_id,
2061 NULL, --capital_event_number,
2062 NULL, --event_name,
2063 NULL, --event_type,
2064 'NO_EVENTS_CREATED',
2065 v_user_id, --created_by,
2066 SYSDATE, --creation_date,
2067 v_org_id --org_id
2068 );
2069
2070 END IF;
2071 CLOSE print_events_cur;
2072
2073
2074 --Print successful event cost totals and asset listings
2075 FOR print_events_rec IN print_events_cur(v_request_id, p_project_id, p_event_type) LOOP
2076
2077 --Print Event info
2078 INSERT INTO pa_cap_event_creation_v
2079 (request_id,
2080 module,
2081 context,
2082 sub_context,
2083 capital_type,
2084 project_id,
2085 project_number,
2086 project_asset_id,
2087 asset_name,
2088 task_id,
2089 task_number,
2090 capital_event_id,
2091 capital_event_number,
2092 event_name,
2093 event_type,
2094 message_code,
2095 created_by,
2096 creation_date,
2097 org_id
2098 )
2099 VALUES
2100 (v_request_id,
2101 'PERIODIC_EVENT_CREATION', --module
2102 '2', --context (2 = New Event Creation)
2103 'E', --sub_context
2104 p_event_type, --capital_type,
2105 p_project_id, --project_id,
2106 p_project_number, --project_number,
2107 NULL, --project_asset_id,
2108 NULL, --asset_name,
2109 NULL, --task_id,
2110 NULL, --task_number,
2111 print_events_rec.capital_event_id, --capital_event_id,
2112 print_events_rec.capital_event_number, --capital_event_number,
2113 print_events_rec.event_name, --event_name,
2114 print_events_rec.event_type, --event_type,
2115 'EVENT_CREATED',
2116 v_user_id, --created_by,
2117 SYSDATE, --creation_date,
2118 v_org_id --org_id
2119 );
2120
2121 END LOOP;
2122
2123
2124 v_addtl_costs_or_assets := 'N';
2125 v_first_addtl := 'Y';
2126
2127 --Check to see if any existing events may have had costs or assets added
2128 OPEN existing_events_cur(v_request_id, p_project_id, p_event_type);
2129 FETCH existing_events_cur INTO existing_events_rec;
2130 IF (existing_events_cur%NOTFOUND) THEN
2131
2132 --No existing events could have had costs or assets added
2133 INSERT INTO pa_cap_event_creation_v
2134 (request_id,
2135 module,
2136 context,
2137 sub_context,
2138 capital_type,
2139 project_id,
2140 project_number,
2141 project_asset_id,
2142 asset_name,
2143 task_id,
2144 task_number,
2145 capital_event_id,
2146 capital_event_number,
2147 event_name,
2148 event_type,
2149 message_code,
2150 created_by,
2151 creation_date,
2152 org_id
2153 )
2154 VALUES
2155 (v_request_id,
2156 'PERIODIC_EVENT_CREATION', --module
2157 '3', --context (2 = Additions to Existing Events)
2158 'NE', --sub_context
2159 p_event_type, --capital_type,
2160 p_project_id, --project_id,
2161 p_project_number, --project_number,
2162 NULL, --project_asset_id,
2163 NULL, --asset_name,
2164 NULL, --task_id,
2165 NULL, --task_number,
2166 NULL, --capital_event_id,
2167 NULL, --capital_event_number,
2168 NULL, --event_name,
2169 NULL, --event_type,
2170 'NO_EVENT_ITEMS_ADDED',
2171 v_user_id, --created_by,
2172 SYSDATE, --creation_date,
2173 v_org_id --org_id
2174 );
2175
2176 CLOSE existing_events_cur;
2177 ELSE
2178
2179 CLOSE existing_events_cur;
2180
2181 FOR existing_events_rec IN existing_events_cur(v_request_id, p_project_id, p_event_type) LOOP
2182
2183 --Check for additional assets
2184 OPEN addtl_assets_cur(existing_events_rec.project_id,
2185 existing_events_rec.capital_event_id,
2186 v_request_id);
2187 FETCH addtl_assets_cur INTO addtl_assets_rec;
2188 IF (addtl_assets_cur%FOUND) THEN
2189
2190 v_addtl_costs_or_assets := 'Y';
2191
2192 --Print Assets added to Event info
2193 INSERT INTO pa_cap_event_creation_v
2194 (request_id,
2195 module,
2196 context,
2197 sub_context,
2198 capital_type,
2199 project_id,
2200 project_number,
2201 project_asset_id,
2202 asset_name,
2203 task_id,
2204 task_number,
2205 capital_event_id,
2206 capital_event_number,
2207 event_name,
2208 event_type,
2209 message_code,
2210 created_by,
2211 creation_date,
2212 org_id
2213 )
2214 VALUES
2215 (v_request_id,
2216 'PERIODIC_EVENT_CREATION', --module
2217 '3', --context (3 = Additions to Existing Events)
2218 'AE', --sub_context
2219 p_event_type, --capital_type,
2220 p_project_id, --project_id,
2221 p_project_number, --project_number,
2222 NULL, --project_asset_id,
2223 NULL, --asset_name,
2224 NULL, --task_id,
2225 NULL, --task_number,
2226 existing_events_rec.capital_event_id, --capital_event_id,
2227 existing_events_rec.capital_event_number, --capital_event_number,
2228 existing_events_rec.event_name, --event_name,
2229 existing_events_rec.event_type, --event_type,
2230 'EVENT_ASSETS_ADDED',
2231 v_user_id, --created_by,
2232 SYSDATE, --creation_date,
2233 v_org_id --org_id
2234 );
2235
2236 END IF; --Additional assets added
2237 CLOSE addtl_assets_cur;
2238
2239
2240 --Check for additional costs
2241 OPEN addtl_costs_cur(existing_events_rec.project_id,
2242 existing_events_rec.capital_event_id,
2243 v_request_id);
2244 FETCH addtl_costs_cur INTO addtl_costs_rec;
2245 IF (addtl_costs_cur%FOUND) THEN
2246
2247 v_addtl_costs_or_assets := 'Y';
2248
2249 --Print Costs added to Event info
2250 INSERT INTO pa_cap_event_creation_v
2251 (request_id,
2252 module,
2253 context,
2254 sub_context,
2255 capital_type,
2256 project_id,
2257 project_number,
2258 project_asset_id,
2259 asset_name,
2260 task_id,
2261 task_number,
2262 capital_event_id,
2263 capital_event_number,
2264 event_name,
2265 event_type,
2266 message_code,
2267 created_by,
2268 creation_date,
2269 org_id
2270 )
2271 VALUES
2272 (v_request_id,
2273 'PERIODIC_EVENT_CREATION', --module
2274 '3', --context (3 = Additions to Existing Events)
2275 'CE', --sub_context
2276 p_event_type, --capital_type,
2277 p_project_id, --project_id,
2278 p_project_number, --project_number,
2279 NULL, --project_asset_id,
2280 NULL, --asset_name,
2281 NULL, --task_id,
2282 NULL, --task_number,
2283 existing_events_rec.capital_event_id, --capital_event_id,
2284 existing_events_rec.capital_event_number, --capital_event_number,
2285 existing_events_rec.event_name, --event_name,
2286 existing_events_rec.event_type, --event_type,
2287 'EVENT_COSTS_ADDED',
2288 v_user_id, --created_by,
2289 SYSDATE, --creation_date,
2290 v_org_id --org_id
2291 );
2292
2293
2294 END IF; --Additional costs added
2295 CLOSE addtl_costs_cur;
2296
2297 END LOOP; --Look for additional costs and assets in existing events
2298
2299
2300 IF v_addtl_costs_or_assets = 'N' THEN
2301
2302 --No existing events have had costs or assets added
2303 INSERT INTO pa_cap_event_creation_v
2304 (request_id,
2305 module,
2306 context,
2307 sub_context,
2308 capital_type,
2309 project_id,
2310 project_number,
2311 project_asset_id,
2312 asset_name,
2313 task_id,
2314 task_number,
2315 capital_event_id,
2316 capital_event_number,
2317 event_name,
2318 event_type,
2319 message_code,
2320 created_by,
2321 creation_date,
2322 org_id
2323 )
2324 VALUES
2325 (v_request_id,
2326 'PERIODIC_EVENT_CREATION', --module
2327 '3', --context (3 = Additions to Existing Events)
2328 'NE', --sub_context
2329 p_event_type, --capital_type,
2330 p_project_id, --project_id,
2331 p_project_number, --project_number,
2332 NULL, --project_asset_id,
2333 NULL, --asset_name,
2334 NULL, --task_id,
2335 NULL, --task_number,
2336 NULL, --capital_event_id,
2337 NULL, --capital_event_number,
2338 NULL, --event_name,
2339 NULL, --event_type,
2340 'NO_EVENT_ITEMS_ADDED',
2341 v_user_id, --created_by,
2342 SYSDATE, --creation_date,
2343 v_org_id --org_id
2344 );
2345 END IF;
2346
2347 END IF; --Check for existing events
2348
2349
2350 EXCEPTION
2351
2352 WHEN empty_cursor_error THEN
2353 retcode := -30;
2354 errbuf := 'Cannot find newly created Capital Event for project id '||p_project_id;
2355 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,errbuf);
2356 FND_FILE.PUT_LINE(FND_FILE.LOG,errbuf);
2357 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CAPITAL_EVENTS_PVT',
2358 p_procedure_name => 'CREATE_EVENT_FOR_PROJECT',
2359 p_error_text => SUBSTRB(errbuf,1,240));
2360 ROLLBACK;
2361 RAISE;
2362
2363
2364
2365 WHEN OTHERS THEN
2366 retcode := SQLCODE;
2367 errbuf := 'Unexpected error for project id '||
2368 p_project_id||': '||SQLCODE||' '||SQLERRM;
2369 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,errbuf);
2370 FND_FILE.PUT_LINE(FND_FILE.LOG,errbuf);
2371 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CAPITAL_EVENTS_PVT',
2372 p_procedure_name => 'CREATE_EVENT_FOR_PROJECT',
2373 p_error_text => SUBSTRB(errbuf,1,240));
2374 ROLLBACK;
2375 RAISE;
2376
2377
2378 END CREATE_EVENT_FOR_PROJECT;
2379
2380
2381
2382
2383
2384 PROCEDURE ATTACH_ASSETS
2385 (p_project_id IN NUMBER,
2386 p_capital_event_id IN NUMBER,
2387 p_book_type_code IN VARCHAR2 DEFAULT NULL,
2388 p_asset_name IN VARCHAR2 DEFAULT NULL,
2389 p_asset_category_id IN NUMBER DEFAULT NULL,
2390 p_location_id IN NUMBER DEFAULT NULL,
2391 p_asset_date_from IN DATE DEFAULT NULL,
2392 p_asset_date_to IN DATE DEFAULT NULL,
2393 p_task_number_from IN VARCHAR2 DEFAULT NULL,
2394 p_task_number_to IN VARCHAR2 DEFAULT NULL,
2395 p_ret_target_asset_id IN NUMBER DEFAULT NULL,
2396 x_assets_attached_count OUT NOCOPY NUMBER,
2397 x_return_status OUT NOCOPY VARCHAR2,
2398 x_msg_data OUT NOCOPY VARCHAR2) IS
2399
2400
2401 CURSOR assets_cur (x_event_type VARCHAR2) IS
2402 SELECT ppa.project_asset_id
2403 FROM pa_project_assets_all ppa
2404 WHERE ppa.project_id = p_project_id
2405 AND ppa.capital_event_id IS NULL
2406 AND ppa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','X')
2407 AND ppa.date_placed_in_service IS NOT NULL
2408 AND ppa.date_placed_in_service
2409 BETWEEN NVL(p_asset_date_from, ppa.date_placed_in_service)
2410 AND NVL(p_asset_date_to, ppa.date_placed_in_service)
2411 AND NVL(ppa.book_type_code,'X') = NVL(p_book_type_code,NVL(ppa.book_type_code,'X'))
2412 AND ppa.asset_name = NVL(p_asset_name,ppa.asset_name)
2413 AND NVL(ppa.asset_category_id,-99) = NVL(p_asset_category_id,NVL(ppa.asset_category_id,-99))
2414 AND NVL(ppa.location_id,-99) = NVL(p_location_id,NVL(ppa.location_id,-99))
2415 AND NVL(ppa.ret_target_asset_id,-99) = NVL(p_ret_target_asset_id,NVL(ppa.ret_target_asset_id,-99))
2416 AND EXISTS
2417 (SELECT 'Assignment Exists'
2418 FROM pa_project_asset_assignments paa
2419 WHERE paa.project_id = p_project_id
2420 AND (
2421 (paa.project_asset_id = ppa.project_asset_id) --Asset is specifically assigned to project or task(s)
2422 OR
2423 (paa.project_asset_id = 0 AND paa.task_id = 0) --There is a Project-Level Common Assignment
2424 )
2425 );
2426
2427 assets_rec assets_cur%ROWTYPE;
2428
2429
2430 CURSOR task_assignments_cur (x_project_asset_id NUMBER) IS
2431 SELECT paa.project_asset_id,
2432 paa.task_id
2433 FROM pa_project_asset_assignments paa,
2434 pa_tasks pt
2435 WHERE pt.project_id = p_project_id
2436 AND paa.project_id = p_project_id
2437 AND paa.project_asset_id = x_project_asset_id
2438 AND pt.task_id = paa.task_id
2439 AND pt.task_number
2440 BETWEEN NVL(p_task_number_from, pt.task_number)
2441 AND NVL(p_task_number_to, pt.task_number);
2442
2443 task_assignments_rec task_assignments_cur%ROWTYPE;
2444
2445
2446
2447 v_event_type PA_CAPITAL_EVENTS.event_type%TYPE;
2448 v_user_id NUMBER := FND_GLOBAL.user_id;
2449 v_login_id NUMBER := FND_GLOBAL.login_id;
2450
2451
2452 BEGIN
2453 x_return_status := 'S';
2454 x_assets_attached_count := 0;
2455
2456 --Get capital event type
2457 SELECT event_type
2458 INTO v_event_type
2459 FROM pa_capital_events
2460 WHERE capital_event_id = p_capital_event_id;
2461
2462
2463 --Verify that assets exist to be attached
2464 OPEN assets_cur(v_event_type);
2465 FETCH assets_cur INTO assets_rec;
2466 IF assets_cur%NOTFOUND THEN
2467 CLOSE assets_cur;
2468 x_assets_attached_count := 0;
2469 RETURN;
2470 END IF;
2471 CLOSE assets_cur;
2472
2473
2474 --Attach assets to capital event
2475 FOR assets_rec IN assets_cur(v_event_type) LOOP
2476
2477 --Test for Task Assignments, if Task Number From or To parameters have been specified
2478 IF p_task_number_from IS NOT NULL OR p_task_number_to IS NOT NULL THEN
2479
2480 OPEN task_assignments_cur(assets_rec.project_asset_id);
2481 FETCH task_assignments_cur INTO task_assignments_rec;
2482 IF task_assignments_cur%FOUND THEN
2483
2484 --Update NULL capital_event_id with parameter value
2485 UPDATE pa_project_assets_all
2486 SET capital_event_id = p_capital_event_id,
2487 last_update_date = SYSDATE,
2488 last_updated_by = v_user_id,
2489 last_update_login = v_login_id
2490 WHERE project_asset_id = assets_rec.project_asset_id
2491 AND capital_event_id IS NULL;
2492
2493 x_assets_attached_count := x_assets_attached_count + 1;
2494
2495 END IF;
2496 CLOSE task_assignments_cur;
2497
2498 ELSE
2499
2500 --Update NULL capital_event_id with parameter value
2501 UPDATE pa_project_assets_all
2502 SET capital_event_id = p_capital_event_id,
2503 last_update_date = SYSDATE,
2504 last_updated_by = v_user_id,
2505 last_update_login = v_login_id
2506 WHERE project_asset_id = assets_rec.project_asset_id
2507 AND capital_event_id IS NULL;
2508
2509 x_assets_attached_count := x_assets_attached_count + 1;
2510
2511 END IF;
2512
2513 END LOOP; --Attach Assets
2514
2515
2516 EXCEPTION
2517
2518 WHEN OTHERS THEN
2519 x_return_status := 'U';
2520 x_msg_data := 'Unexpected error for capital event id '||
2521 p_capital_event_id||': '||SQLCODE||' '||SQLERRM;
2522 RAISE;
2523
2524 END ATTACH_ASSETS;
2525
2526
2527 PROCEDURE ATTACH_COSTS
2528 (p_project_id IN NUMBER,
2529 p_capital_event_id IN NUMBER,
2530 p_task_number_from IN VARCHAR2 DEFAULT NULL,
2531 p_task_number_to IN VARCHAR2 DEFAULT NULL,
2532 p_ei_date_from IN DATE DEFAULT NULL,
2533 p_ei_date_to IN DATE DEFAULT NULL,
2534 p_expenditure_type IN VARCHAR2 DEFAULT NULL,
2535 p_transaction_source IN VARCHAR2 DEFAULT NULL,
2536 x_costs_attached_count OUT NOCOPY NUMBER,
2537 x_return_status OUT NOCOPY VARCHAR2,
2538 x_msg_data OUT NOCOPY VARCHAR2) IS
2539
2540
2541 CURSOR costs_cur (x_event_type VARCHAR2) IS
2542 SELECT peia.expenditure_item_id,
2543 peia.task_id,
2544 t.top_task_id,
2545 t.parent_task_id
2546 FROM pa_expenditure_items_all peia,
2547 pa_tasks t
2548 WHERE t.project_id = p_project_id
2549 AND peia.task_id = t.task_id
2550 AND t.task_number
2551 BETWEEN NVL(p_task_number_from, t.task_number)
2552 AND NVL(p_task_number_to, t.task_number)
2553 AND peia.capital_event_id IS NULL
2554 AND peia.billable_flag = DECODE(x_event_type,'C','Y','N')
2555 AND peia.revenue_distributed_flag = 'N'
2556 AND peia.cost_distributed_flag = 'Y'
2557 AND NVL(t.retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
2558 AND peia.expenditure_type = NVL(p_expenditure_type, peia.expenditure_type)
2559 AND NVL(peia.transaction_source,'X') = NVL(p_transaction_source, NVL(peia.transaction_source,'X'))
2560 AND peia.expenditure_item_date
2561 BETWEEN NVL(p_ei_date_from, peia.expenditure_item_date)
2562 AND NVL(p_ei_date_to, peia.expenditure_item_date)
2563 ORDER BY peia.task_id;
2564
2565 costs_rec costs_cur%ROWTYPE;
2566
2567
2568 CURSOR common_task_cur (x_task_id NUMBER, x_top_task_id NUMBER) IS
2569 SELECT paa.task_id
2570 FROM pa_project_asset_assignments paa
2571 WHERE paa.project_id = p_project_id
2572 AND paa.task_id IN (x_task_id,x_top_task_id)
2573 AND paa.project_asset_id = 0;
2574
2575 common_task_rec common_task_cur%ROWTYPE;
2576
2577
2578 CURSOR wbs_branch_tasks_cur(x_parent_task_id NUMBER,
2579 x_current_task_id NUMBER,
2580 x_event_type VARCHAR2) IS
2581 SELECT task_id,
2582 task_number
2583 FROM pa_tasks
2584 WHERE task_id <> x_parent_task_id
2585 AND task_id <> x_current_task_id
2586 AND NVL(retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
2587 CONNECT BY parent_task_id = PRIOR task_id
2588 START WITH task_id = x_parent_task_id;
2589
2590 wbs_branch_tasks_rec wbs_branch_tasks_cur%ROWTYPE;
2591
2592
2593 CURSOR task_asgn_assets_cur(x_project_id NUMBER,
2594 x_capital_event_id NUMBER,
2595 x_task_id NUMBER,
2596 x_event_type VARCHAR2) IS
2597 SELECT paa.project_asset_id
2598 FROM pa_project_assets_all pa,
2599 pa_project_asset_assignments paa
2600 WHERE pa.project_asset_id = paa.project_asset_id
2601 AND pa.project_id = x_project_id
2602 AND paa.project_id = x_project_id
2603 AND pa.capital_event_id = x_capital_event_id
2604 AND paa.task_id = x_task_id
2605 AND pa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT');
2606
2607 task_asgn_assets_rec task_asgn_assets_cur%ROWTYPE;
2608
2609
2610
2611 v_event_type PA_CAPITAL_EVENTS.event_type%TYPE;
2612 v_user_id NUMBER := FND_GLOBAL.user_id;
2613 v_login_id NUMBER := FND_GLOBAL.login_id;
2614 v_attach_ei VARCHAR2(1) := 'N';
2615 v_project_assignment VARCHAR2(1) := 'N';
2616 v_task_assignment VARCHAR2(1) := 'N';
2617 v_common_asgn_exists VARCHAR2(1) := 'N';
2618 v_task_id NUMBER := 0;
2619
2620
2621
2622 no_costs_to_attach EXCEPTION;
2623
2624 BEGIN
2625 x_return_status := 'S';
2626 x_costs_attached_count := 0;
2627
2628 --Get capital event type
2629 SELECT event_type
2630 INTO v_event_type
2631 FROM pa_capital_events
2632 WHERE capital_event_id = p_capital_event_id;
2633
2634
2635 --Verify that costs exist to be attached
2636 OPEN costs_cur(v_event_type);
2637 FETCH costs_cur INTO costs_rec;
2638 IF costs_cur%NOTFOUND THEN
2639 CLOSE costs_cur;
2640 x_costs_attached_count := 0;
2641 RETURN;
2642 END IF;
2643 CLOSE costs_cur;
2644
2645
2646 --Determine project-level Asset Assignments exist (Specific or Common)
2647 SELECT DECODE(COUNT(*),0,'N','Y')
2648 INTO v_project_assignment
2649 FROM pa_project_asset_assignments
2650 WHERE project_id = p_project_id
2651 AND task_id = 0;
2652
2653 --If so, all eligible costs can be attached
2654 IF v_project_assignment = 'Y' THEN
2655 v_attach_ei := 'Y';
2656 END IF;
2657
2658
2659 --Attach costs to capital event
2660 FOR costs_rec IN costs_cur(v_event_type) LOOP
2661
2662 IF v_project_assignment = 'N' THEN
2663
2664 --Perform logic whenever task break occurs
2665 IF NVL(v_task_id,0) <> costs_rec.task_id THEN
2666
2667 v_task_id := costs_rec.task_id;
2668
2669 --Determine if current task (or its Top Task) has a specific asset assignment
2670 --for an asset in the current event
2671 SELECT DECODE(COUNT(*),0,'N','Y')
2672 INTO v_task_assignment
2673 FROM pa_project_asset_assignments paa,
2674 pa_project_assets_all ppa
2675 WHERE paa.project_id = p_project_id
2676 AND paa.project_asset_id = ppa.project_asset_id
2677 AND ppa.capital_event_id = p_capital_event_id
2678 AND paa.task_id IN (costs_rec.task_id,costs_rec.top_task_id);
2679
2680 --If so, all eligible costs can be attached
2681 IF v_task_assignment = 'Y' THEN
2682 v_attach_ei := 'Y';
2683 ELSE
2684 --Determine if current task (or its Top Task) has a Common assignment
2685 OPEN common_task_cur(costs_rec.task_id,costs_rec.top_task_id);
2686 FETCH common_task_cur INTO common_task_rec;
2687 IF common_task_cur%NOTFOUND THEN
2688 --Task has no common or specific assignment, do not attach costs
2689 v_common_asgn_exists := 'N';
2690 v_attach_ei := 'N';
2691 ELSE
2692 v_common_asgn_exists := 'Y';
2693 END IF;
2694 CLOSE common_task_cur;
2695
2696
2697 --If the assignment task ID is also the top task, then all eligible costs can be attached
2698 IF v_common_asgn_exists = 'Y' AND common_task_rec.task_id = costs_rec.top_task_id THEN
2699
2700 v_attach_ei := 'Y';
2701
2702 ELSIF v_common_asgn_exists = 'Y' AND common_task_rec.task_id <> costs_rec.top_task_id THEN
2703
2704 --Only attach costs if an asset assignment exists at or beneath the parent task
2705 v_attach_ei := 'N';
2706
2707 --Attach all costs where asset assignment(s) exist at or beneath the Parent (not Top) Task
2708 FOR wbs_branch_tasks_rec IN wbs_branch_tasks_cur(costs_rec.parent_task_id,
2709 costs_rec.task_id,
2710 v_event_type) LOOP
2711
2712 --Check for existence of asset assignments in current event on current task
2713 OPEN task_asgn_assets_cur(p_project_id,
2714 p_capital_event_id,
2715 wbs_branch_tasks_rec.task_id,
2716 v_event_type);
2717 FETCH task_asgn_assets_cur INTO task_asgn_assets_rec;
2718 IF task_asgn_assets_cur%FOUND THEN
2719 v_attach_ei := 'Y';
2720 END IF;
2721 CLOSE task_asgn_assets_cur;
2722
2723 END LOOP; --WBS Branch Tasks
2724
2725 END IF; --Common Assignment exists
2726 END IF; --Task Asset Assignment exists
2727 END IF; --Task ID control break
2728 END IF; --No Project Assignment exists
2729
2730 IF v_attach_ei = 'Y' THEN
2731
2732 --Update NULL capital_event_id with parameter value
2733 UPDATE pa_expenditure_items_all
2734 SET capital_event_id = p_capital_event_id,
2735 last_update_date = SYSDATE,
2736 last_updated_by = v_user_id,
2737 last_update_login = v_login_id
2738 WHERE expenditure_item_id = costs_rec.expenditure_item_id
2739 AND capital_event_id IS NULL;
2740
2741 x_costs_attached_count := x_costs_attached_count + 1;
2742 END IF;
2743
2744 END LOOP; --Attach Costs
2745
2746
2747
2748 EXCEPTION
2749
2750 WHEN OTHERS THEN
2751 x_return_status := 'U';
2752 x_msg_data := 'Unexpected error for capital event id '||
2753 p_capital_event_id||': '||SQLCODE||' '||SQLERRM;
2754 RAISE;
2755
2756 END ATTACH_COSTS;
2757
2758
2759 END PA_CAPITAL_EVENTS_PVT;