[Home] [Help]
PACKAGE BODY: APPS.PA_CAP_INT_PVT
Source
1 PACKAGE BODY PA_CAP_INT_PVT AS
2 -- $Header: PACINTTB.pls 120.6 2011/12/13 13:21:35 sukorrap ship $
3
4
5 /*
6 This package contains the procedures and functions required to process Capitalized
7 Interest. The functions performed are:
8
9 Generation of Capitalized Interest (concurrent request)
10 Generation and Auto-Release of Capitalized Interest (concurrent request)
11 Release of Capitalized Interest (from form button)
12 Purge of Capitalized Interest Source Details (concurrent request)
13
14 When the main procedure, GENERATE_CAP_INTEREST, is called from an Oracle Report, the
15 following parameters are used:
16
17 p_from_project_num => Low project to calculate (NULL if no lower bound)
18 p_to_project_num => High project to calculate (NULL if no higher bound)
19 p_gl_period => GL Period for the calculated interest (required)
20 p_exp_item_date => Expenditure Item Date for the calculated interest (required)
21 p_source_details => Y = create source details
22 p_autorelease => Y = auto-release each run batch
23
24 All interest rates setup for the submitter's M/O ORG_ID that exist in schedules associated
25 with projects between the parameter project numbers will be processed. Each rate processed
26 will create a single run row. If any problem are encountered on a project, no transactions
27 for that project will be created. A project can only be successfully processed once per
28 month. Summarized control numbers are reported on the log while the calling report shows
29 more detailed results.
30
31 If the release of a Capitalized Interest run is requested from the form, the run_id is
32 passed to the GENERATE_CAP_INTEREST procedure through a parameter.
33
34 When the main procedure, PURGE_SOURCE_DETAIL, is called from an Oracle Report, the
35 following parameters are used:
36
37 p_gl_period => GL Period for the source details (required)
38 p_from_project_num => Low project to calculate (NULL if no lower bound)
39 p_to_project_num => High project to calculate (NULL if no higher bound)
40
41 The source details from the earliest period through the parameter GL period that belong
42 to projects between the parameter project numbers will be purged. Summarized control
43 numbers are reported on the log while the calling report shows more detailed results.
44
45 */
46
47
48 ------------------------------------------------------------------
49 ------------------------------------------------------------------
50 -- G L O B A L V A R I A B L E S --
51 ------------------------------------------------------------------
52 ------------------------------------------------------------------
53
54
55 -- Debug mode
56 g_debug_mode VARCHAR2(1);
57
58
59 -- Standard Who columns
60 g_created_by NUMBER;
61 g_last_updated_by NUMBER;
62 g_last_update_login NUMBER;
63
64
65 -- Request ID
66 g_request_id NUMBER;
67
68
69 -- GL application ID
70 g_gl_app_id gl_period_statuses.application_id%TYPE := 101;
71
72
73 -- Alloc Rule ID for Capitalized Interest
74 g_cap_int_rule_id pa_alloc_rules_all.rule_id%TYPE := -1;
75
76
77 -- Globally used variables
78 g_gl_period gl_period_statuses.period_name%TYPE;
79 g_project_id pa_projects_all.project_id%TYPE;
80 g_rate_name pa_ind_cost_codes.ind_cost_code%TYPE;
81 g_period_end_date gl_period_statuses.end_date%TYPE;
82 g_period_start_date gl_period_statuses.end_date%TYPE;
83 g_exp_item_date gl_period_statuses.end_date%TYPE;
84 g_bdgt_entry_level_code varchar2(2) := 'L'; -- Lowest level task
85
86
87 ------------------------------------------------------------------
88 ------------------------------------------------------------------
89 -- E X T E R N A L F U N C T I O N S --
90 ------------------------------------------------------------------
91 ------------------------------------------------------------------
92
93
94 /* --------------------------------------
95 Returns the run period being processed
96 -------------------------------------- */
97 FUNCTION gl_period RETURN VARCHAR2 IS
98 BEGIN RETURN g_gl_period; END;
99
100
101
102 /* -------------------------------------------------------
103 Returns the end date for the run period being processed
104 ------------------------------------------------------- */
105 FUNCTION period_end_date RETURN DATE IS
106 BEGIN RETURN g_period_end_date; END;
107
108
109
110 /* ----------------------------------------------
111 Returns the current project id being processed
112 ---------------------------------------------- */
113 FUNCTION project_id RETURN NUMBER IS
114 BEGIN RETURN g_project_id; END;
115
116
117
118 /* ---------------------------------------------
119 Returns the current rate name being processed
120 --------------------------------------------- */
121 FUNCTION rate_name RETURN VARCHAR2 IS
122 BEGIN RETURN g_rate_name; END;
123
124
125 /* This API returns 'Y' if the transactions exists in pa_alloc_txn_details
126 * based on this the release of Transaction import process will be called
127 */
128 FUNCTION release_capint_txns_exists
129 (p_run_id IN NUMBER) RETURN VARCHAR2 IS
130
131 l_exists varchar2(1):= 'N';
132
133 BEGIN
134 IF p_run_id is NOT NULL then
135
136 SELECT 'Y'
137 INTO l_exists
138 FROM dual
139 WHERE EXISTS
140 (SELECT NULL
141 FROM PA_ALLOC_TXN_DETAILS
142 WHERE run_id = p_run_id);
143 End If;
144
145 IF g_debug_mode = 'Y' THEN
146 pa_debug.write_file('LOG','Inside release_capint_txns_exists['||l_exists||']');
147 End If;
148 RETURN l_exists;
149
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 IF g_debug_mode = 'Y' THEN
153 pa_debug.write_file('LOG','Inside release_capint_txns_exists['||l_exists||']');
154 End If;
155 RETURN l_exists;
156
157 WHEN OTHERS THEN
158 RETURN l_exists;
159
160 END release_capint_txns_exists;
161
162
163 /* Bug fix: 2972865 Task level Threshold should be calculated based on the
164 * budget entry method. This API returns the budget entry method for the
165 * given project,plan Id and budget type code
166 * Fin plan type id takes the precedence over the budget type code
167 */
168 FUNCTION Get_Bdgt_entry_level_code
169 (p_project_id IN NUMBER
170 ,p_threshold_amt_type IN VARCHAR2
171 ,p_budget_type_code IN VARCHAR2
172 ,p_fin_plan_type_id IN NUMBER
173 ) RETURN VARCHAR2 IS
174
175 cursor threshold_type IS
176 SELECT bv.budget_version_id
177 ,bv.budget_entry_method_code
178 ,bv.fin_plan_type_id
179 ,decode(bv.fin_plan_type_id, null,'BUDGET TYPE','PLAN TYPE') threshold_Type
180 FROM pa_budget_versions bv
181 WHERE bv.project_id = p_project_id
182 AND bv.current_flag = 'Y'
183 AND ( (bv.fin_plan_type_id is not NULL
184 and bv.version_type IN ('COST','ALL')
185 and bv.fin_plan_type_id = p_fin_plan_type_id
186 and bv.budget_type_code is null )
187 OR
188 (bv.fin_plan_type_id is NULL
189 and bv.budget_type_code = p_budget_type_code
190 and NOT EXISTS (select 'Y'
191 from pa_budget_versions bv1
192 where bv1.project_id = bv.project_id
193 and bv1.fin_plan_type_id = p_fin_plan_type_id)
194 )
195 );
196
197 cursor bdgt_entry_code(p_bdgt_entry_method varchar2) IS
198 SELECT distinct ENTRY_LEVEL_CODE
199 -- ,CATEGORIZATION_CODE
200 FROM pa_budget_entry_methods
201 WHERE BUDGET_ENTRY_METHOD_CODE = p_bdgt_entry_method ;
202
203 cursor plan_entry_code(p_plan_version_id Number) IS
204 SELECT nvl(decode(fin_plan_option_level_code
205 ,'PLAN_VERSION',decode(fin_plan_preference_code
206 ,'COST_ONLY'
207 ,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
208 ,COST_FIN_PLAN_LEVEL_CODE)
209 ,'COST_AND_REV_SEP'
210 ,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
211 ,COST_FIN_PLAN_LEVEL_CODE))
212 ,'PROJECT',decode(fin_plan_preference_code
213 ,'COST_ONLY'
214 ,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
215 ,COST_FIN_PLAN_LEVEL_CODE)
216 ,'COST_AND_REV_SEP'
217 ,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
218 ,COST_FIN_PLAN_LEVEL_CODE))
219 ,'PLAN_TYPE',decode(fin_plan_preference_code
220 ,'COST_ONLY'
221 ,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
222 ,COST_FIN_PLAN_LEVEL_CODE)
223 ,'COST_AND_REV_SEP'
224 ,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
225 ,COST_FIN_PLAN_LEVEL_CODE))
226 ),'~') entry_level_code
227 FROM pa_proj_fp_options
228 WHERE project_id = p_project_id
229 AND fin_plan_version_id = p_plan_version_id
230 AND fin_plan_type_id = p_fin_plan_type_id
231 AND fin_plan_preference_code in ('COST_ONLY','COST_AND_REV_SEP')
232 ORDER BY entry_level_code;
233
234
235 cur_thres_type threshold_type%ROWTYPE;
236 l_entry_method varchar2(1):= 'L'; -- Lowest task level
237
238
239 BEGIN
240 IF p_threshold_amt_type = 'BUDGET' Then
241 OPEN threshold_type;
242 FETCH threshold_type INTO cur_thres_type;
243 IF g_debug_mode = 'Y' THEN
244 PA_DEBUG.write_file('LOG','Inside get_bdgt_entry level Type['
245 ||cur_thres_type.threshold_Type||
246 ']entry method code['||cur_thres_type.budget_entry_method_code||
247 ']Bdgt Version['||cur_thres_type.budget_version_id||
248 ']');
249 END IF;
250 IF threshold_type%FOUND Then
251
252 IF cur_thres_type.threshold_Type = 'BUDGET TYPE' Then
253 OPEN bdgt_entry_code(cur_thres_type.budget_entry_method_code);
254 FETCH bdgt_entry_code INTO l_entry_method;
255 CLOSE bdgt_entry_code;
256
257 ELSIF cur_thres_type.threshold_Type = 'PLAN TYPE' Then
258 OPEN plan_entry_code(cur_thres_type.budget_version_id);
259 FETCH plan_entry_code INTO l_entry_method;
260 CLOSE plan_entry_code;
261
262 END IF;
263 END IF;
264 IF NVL(l_entry_method,'P') <> 'L' Then
265 l_entry_method := 'P'; -- project level
266 End If;
267 CLOSE threshold_type;
268
269 END IF;
270 IF g_debug_mode = 'Y' THEN
271 pa_debug.write_file('LOG','Budget Entry level code['||l_entry_method||']');
272 END IF;
273
274 Return l_entry_method;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 RAISE;
279
280 END Get_Bdgt_entry_level_code;
281
282
283
284 /* -------------------------------------------------------------
285 Determine if the cost distribution line is 'OPEN' or 'CLOSED'
286 ------------------------------------------------------------- */
287 FUNCTION cdl_status
288 (p_cutoff_date IN DATE
289 ,p_expenditure_item_id IN NUMBER
290 ,p_line_num IN NUMBER)
291 RETURN VARCHAR2
292 IS
293 lv_value VARCHAR2(30);
294 BEGIN
295 -- Test if the cdl row has already been placed in service
296 SELECT 'CLOSED'
297 INTO lv_value
298 FROM DUAL
299 WHERE EXISTS
300 (SELECT 'X'
301 FROM pa_project_asset_line_details ppald
302 WHERE ppald.reversed_flag = 'N'
303 AND ppald.line_num = p_line_num
304 AND ppald.expenditure_item_id = p_expenditure_item_id);
305
306 RETURN NVL(lv_value,'OPEN');
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 RETURN 'OPEN';
310 WHEN OTHERS THEN
311 RETURN 'CLOSED';
312 END;
313
314
315
316
317 /* -------------------------------------------------------
318 Determine if the associated exp type should be excluded
319 ------------------------------------------------------- */
320 FUNCTION exclude_expenditure_type
321 (p_exp_type IN VARCHAR2
322 ,p_rate_name IN VARCHAR2
323 ,p_interest_calc_method IN VARCHAR2)
324 RETURN VARCHAR2
325 IS
326 lv_exp_type pa_ind_cost_codes.expenditure_type%TYPE;
327 ln_count NUMBER;
328 BEGIN
329 -- Test for exclusion based on simple interest method
330 IF p_interest_calc_method = 'SIMPLE' THEN
331 SELECT picc.expenditure_type
332 INTO lv_exp_type
333 FROM pa_ind_cost_codes picc
334 WHERE picc.ind_cost_code = p_rate_name;
335
336 IF lv_exp_type = p_exp_type THEN
337 RETURN 'Y';
338 END IF;
339 END IF;
340
341
342 -- Test for specified exclusion
343 SELECT 1
344 INTO ln_count
345 FROM dual
346 WHERE EXISTS(
347 SELECT null
348 FROM pa_cint_exp_type_excl pcete
349 WHERE pcete.expenditure_type = p_exp_type
350 AND pcete.ind_cost_code = p_rate_name
351 );
352
353 IF ln_count <> 0 THEN
354 RETURN 'Y';
355 END IF;
356
357
358 -- If all tests passed, return no exclusion
359 RETURN 'N';
360 EXCEPTION
361 WHEN OTHERS THEN
362 RETURN 'N';
363 END;
364
365
366
367 /* ---------------------------------------------
368 Determine if the task is capitalizable or not
369 --------------------------------------------- */
370 FUNCTION task_capital_flag
371 (p_project_id IN NUMBER
372 ,p_task_id IN NUMBER
373 ,p_task_bill_flag IN VARCHAR2)
374 RETURN VARCHAR2
375 IS
376 lv_billable_flag VARCHAR2(1);
377 lv_work_type_id NUMBER(15);
378 BEGIN
379 -- Use the work type to determine capital status for the task if appropriate
380
381 lv_work_type_id := PA_UTILS4.get_work_type_id
382 ( p_project_id => p_project_id
383 ,p_task_id => p_task_id
384 ,p_assignment_id => 0
385 );
386
387 lv_billable_flag := PA_UTILS4.get_trxn_work_billabilty
388 (p_work_type_id => lv_work_type_id
389 ,p_tc_extn_bill_flag =>p_task_bill_flag );
390
391 RETURN NVL(lv_billable_flag,'N');
392 EXCEPTION
393 WHEN OTHERS THEN
394 RETURN 'N';
395 END;
396
397
398
399 ------------------------------------------------------------------
400 ------------------------------------------------------------------
401 -- U T I L I T Y P R O C E D U R E S --
402 ------------------------------------------------------------------
403 ------------------------------------------------------------------
404
405 /* -------------------------------------
406 Get the GL period start and end dates
407 ------------------------------------- */
408 PROCEDURE get_period_dates
409 (p_gl_period IN VARCHAR2
410 ,x_start_date OUT NOCOPY DATE
411 ,x_end_date OUT NOCOPY DATE
412 ,x_fiscal_year OUT NOCOPY NUMBER
413 ,x_quarter_num OUT NOCOPY NUMBER
414 ,x_period_num OUT NOCOPY NUMBER
415 ,x_return_status OUT NOCOPY VARCHAR2
416 ,x_error_msg_count OUT NOCOPY NUMBER
417 ,x_error_msg_code OUT NOCOPY VARCHAR2)
418 IS
419 BEGIN
420 -- Get information for the parameter GL period
421 SELECT gps.start_date
422 ,gps.end_date
423 ,gps.period_year
424 ,gps.quarter_num
425 ,gps.period_num
426 INTO x_start_date
427 ,x_end_date
428 ,x_fiscal_year
429 ,x_quarter_num
430 ,x_period_num
431 FROM gl_period_statuses gps
432 ,pa_implementations pi
433 WHERE gps.period_name = p_gl_period
434 AND gps.application_id = g_gl_app_id
435 AND gps.set_of_books_id = pi.set_of_books_id;
436
437 x_return_status := 'S';
438 x_error_msg_count := 0;
439 x_error_msg_code := NULL;
440 EXCEPTION
441 WHEN OTHERS THEN
442 x_return_status := 'U';
443 x_error_msg_count := 1;
444 x_error_msg_code := SQLERRM;
445 END;
446
447
448
449 /* -------------------------------------
450 Get the GL period start and end dates
451 ------------------------------------- */
452 PROCEDURE get_next_period
453 (p_fiscal_year IN NUMBER
454 ,p_period_num IN NUMBER
455 ,x_fiscal_year OUT NOCOPY NUMBER
456 ,x_period_num OUT NOCOPY NUMBER
457 ,x_return_status OUT NOCOPY VARCHAR2
458 ,x_error_msg_count OUT NOCOPY NUMBER
459 ,x_error_msg_code OUT NOCOPY VARCHAR2)
460 IS
461 ld_curr_date DATE;
462 ld_next_date DATE;
463 BEGIN
464 -- Get the next non-adjustment period after the parameter period
465 SELECT gps.period_year
466 ,gps.period_num
467 INTO x_fiscal_year
468 ,x_period_num
469 FROM gl_period_statuses gps
470 ,pa_implementations pi
471 WHERE gps.application_id = g_gl_app_id
472 AND gps.set_of_books_id = pi.set_of_books_id
473 AND gps.adjustment_period_flag = 'N'
474 AND gps.start_date =
475 (SELECT MIN(gps.start_date)
476 FROM gl_period_statuses gps
477 ,pa_implementations pi
478 WHERE gps.application_id = g_gl_app_id
479 AND gps.set_of_books_id = pi.set_of_books_id
480 AND gps.adjustment_period_flag = 'N'
481 AND gps.start_date >
482 (SELECT gps.end_date
483 FROM gl_period_statuses gps
484 ,pa_implementations pi
485 WHERE gps.application_id = g_gl_app_id
486 AND gps.set_of_books_id = pi.set_of_books_id
487 AND gps.period_year = p_fiscal_year
488 AND gps.period_num = p_period_num));
489
490 x_return_status := 'S';
491 x_error_msg_count := 0;
492 x_error_msg_code := NULL;
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 x_fiscal_year := NULL;
496 x_period_num := NULL;
497 x_return_status := 'S';
498 x_error_msg_count := 0;
499 x_error_msg_code := NULL;
500 WHEN OTHERS THEN
501 x_return_status := 'U';
502 x_error_msg_count := 1;
503 x_error_msg_code := SQLERRM;
504 END;
505
506
507
508 /* --------------------------------------------------------------
509 Return the number of non-adjustment periods in the fiscal year
510 -------------------------------------------------------------- */
511 FUNCTION num_of_periods
512 (p_fiscal_year IN NUMBER)
513 RETURN NUMBER
514 IS
515 ln_count NUMBER;
516 BEGIN
517 -- Test if the cdl row has already been placed in service
518 SELECT COUNT(*)
519 INTO ln_count
520 FROM gl_period_statuses gps
521 ,pa_implementations pi
522 WHERE gps.period_year = p_fiscal_year
523 AND gps.application_id = g_gl_app_id
524 AND gps.set_of_books_id = pi.set_of_books_id
525 AND adjustment_period_flag = 'N';
526
527 RETURN ln_count;
528 EXCEPTION
529 WHEN OTHERS THEN
530 RETURN 0;
531 END;
532
533
534
535 /* --------------------------------------------------------------------------------
536 Check for the existence of Cap Interest batches for the project, period and rate
537 -------------------------------------------------------------------------------- */
538 PROCEDURE check_project_batches
539 (p_project_id IN NUMBER
540 ,p_rule_id IN NUMBER
541 ,p_fiscal_year IN NUMBER
542 ,p_quarter_num IN NUMBER
543 ,p_period_num IN NUMBER
544 ,p_rate_name IN VARCHAR2
545 ,x_bypass OUT NOCOPY VARCHAR2
546 ,x_return_status OUT NOCOPY VARCHAR2
547 ,x_error_msg_count OUT NOCOPY NUMBER
548 ,x_error_msg_code OUT NOCOPY VARCHAR2)
549 IS
550 BEGIN
551 -- Look for interest transactions against the project, period and rate
552 SELECT 'Y'
553 INTO x_bypass
554 FROM DUAL
555 WHERE EXISTS
556 (SELECT 'X'
557 FROM pa_alloc_txn_details patd
558 ,pa_alloc_runs par
559 WHERE patd.project_id = p_project_id
560 AND patd.run_id = par.run_id
561 AND par.run_status <> 'RV'
562 AND par.cint_rate_name = p_rate_name
563 AND par.period_num = p_period_num
564 AND par.quarter = p_quarter_num
565 AND par.fiscal_year = p_fiscal_year
566 AND par.rule_id = p_rule_id)
567 /* This condition is added to check the CRL migrated trxns
568 * Since we are only migrating the rate name defaulted at the BG
569 * but the transactions includes the rate name defaulted at BG and
570 * overriding rate names.
571 * EX: projects p1,,,p5 are associated with rate1 are at BG and p7 - rate2 (overide)
572 * Before migration capint run for FEB-02 and we migrated p1 to p7
573 * again when user runs capint for FEB-02 after migration
574 * ideally p7 should not be picked up for processing. In order to avoid this
575 * the following condition is added : check run irresepective of rate name
576 * for the given project and period and the rate name doesnot exists in the
577 * pa_ind_cost_codes table
578 */
579 OR EXISTS (SELECT 'X'
580 FROM pa_alloc_txn_details patd
581 ,pa_alloc_runs par
582 WHERE patd.run_id = par.run_id
583 AND par.run_status <> 'RV'
584 AND patd.project_id = p_project_id
585 AND par.period_num = p_period_num
586 AND par.quarter = p_quarter_num
587 AND par.fiscal_year = p_fiscal_year
588 AND par.rule_id = p_rule_id
589 AND NOT EXISTS ( -- check for override rates which are not migrated
590 select null
591 from pa_ind_cost_codes icc
592 where icc.ind_cost_code = par.cint_rate_name
593 and icc.ind_cost_code_usage = 'CAPITALIZED_INTEREST'
594 )
595 );
596
597 x_return_status := 'S';
598 x_error_msg_count := 0;
599 x_error_msg_code := NULL;
600 EXCEPTION
601 WHEN NO_DATA_FOUND THEN
602 x_bypass := 'N';
603 x_return_status := 'S';
604 x_error_msg_count := 0;
605 x_error_msg_code := NULL;
606 WHEN OTHERS THEN
607 x_return_status := 'U';
608 x_error_msg_count := 1;
609 x_error_msg_code := SQLERRM;
610 END;
611
612
613
614
615 /* -----------------------------------------------------------------------------
616 Check for a valid compiled version for the project schedule and exp item date
617 ----------------------------------------------------------------------------- */
618 PROCEDURE check_project_schedule
619 (p_int_sch_id IN NUMBER
620 ,p_test_date IN DATE
621 ,x_sched_version_id OUT NOCOPY NUMBER
622 ,x_bypass OUT NOCOPY VARCHAR2
623 ,x_return_status OUT NOCOPY VARCHAR2
624 ,x_error_msg_count OUT NOCOPY NUMBER
625 ,x_error_msg_code OUT NOCOPY VARCHAR2)
626 IS
627 BEGIN
628 SELECT ind_rate_sch_revision_id
629 INTO x_sched_version_id
630 FROM pa_ind_rate_sch_revisions pirsr
631 WHERE TRUNC(p_test_date) BETWEEN
632 TRUNC(pirsr.start_date_active) AND TRUNC(NVL(pirsr.end_date_active, p_test_date))
633 AND pirsr.compiled_flag = 'Y'
634 AND pirsr.ind_rate_sch_id = p_int_sch_id;
635
636 x_bypass := 'N';
637 x_return_status := 'S';
638 x_error_msg_count := 0;
639 x_error_msg_code := NULL;
640 EXCEPTION
641 WHEN NO_DATA_FOUND THEN
642 x_bypass := 'Y';
643 x_return_status := 'S';
644 x_error_msg_count := 0;
645 x_error_msg_code := NULL;
646 WHEN OTHERS THEN
647 x_return_status := 'U';
648 x_error_msg_count := 1;
649 x_error_msg_code := SQLERRM;
650 END;
651
652 /* -----------------------------------------------------------------------------
653 Check the schedule contains this rate name or not
654 ----------------------------------------------------------------------------- */
655 PROCEDURE check_schedule_has_ratename
656 (p_sch_id IN Number
657 ,p_sch_rev_date IN date
658 ,p_sch_rev_id IN Number
659 ,p_rate_name IN Varchar2
660 ,x_bypass_project OUT NOCOPY Varchar2
661 ,x_return_status OUT NOCOPY Varchar2
662 ,x_error_msg_count OUT NOCOPY Number
663 ,x_error_msg_code OUT NOCOPY Varchar2 )
664 IS
665 l_rate_falg varchar2(10);
666
667 BEGIN
668 SELECT 'Y'
669 INTO l_rate_falg
670 FROM DUAL
671 WHERE EXISTS (
672 SELECT null
673 FROM pa_cint_rate_multipliers rate
674 WHERE rate.ind_rate_sch_id = p_sch_id
675 AND rate.ind_rate_sch_revision_id = p_sch_rev_id
676 AND rate.rate_name = p_rate_name
677 );
678 x_bypass_project := 'N';
679 x_return_status := 'S';
680 x_error_msg_count := 0;
681 x_error_msg_code := NULL;
682 EXCEPTION
683 WHEN NO_DATA_FOUND THEN
684 x_bypass_project:= 'Y';
685 x_return_status := 'S';
686 x_error_msg_count := 0;
687 x_error_msg_code := NULL;
688 WHEN OTHERS THEN
689 x_return_status := 'U';
690 x_error_msg_count := 1;
691 x_error_msg_code := SQLERRM;
692 END;
693
694 /* --------------------------------------------------------------------
695 Check the project or task against the duration and amount thresholds
696 -------------------------------------------------------------------- */
697 PROCEDURE check_thresholds
698 (p_project_id IN NUMBER
699 ,p_task_id IN NUMBER
700 ,p_rate_name IN VARCHAR2
701 ,p_start_date IN DATE
702 ,p_end_date IN DATE
703 ,p_threshold_amt_type IN VARCHAR2
704 ,p_budget_type IN VARCHAR2
705 ,p_fin_plan_type_id IN NUMBER
706 ,p_interest_calc_method IN VARCHAR2
707 ,p_cip_cost_type IN VARCHAR2
708 ,x_duration_threshold IN OUT NOCOPY NUMBER
709 ,x_amt_threshold IN OUT NOCOPY NUMBER
710 ,x_bypass OUT NOCOPY VARCHAR2
711 ,x_return_status OUT NOCOPY VARCHAR2
712 ,x_error_msg_count OUT NOCOPY NUMBER
713 ,x_error_msg_code OUT NOCOPY VARCHAR2)
714 IS
715 ln_duration NUMBER;
716 ln_amount NUMBER;
717 ln_task_amt NUMBER;
718 /**
719 CURSOR cur_non_cap_tasks IS
720 SELECT task_id
721 FROM pa_tasks
722 WHERE project_id = p_project_id
723 AND task_capital_flag(task_id) = 'N';
724
725 r_task cur_non_cap_tasks%ROWTYPE;
726 **/
727 BEGIN
728 IF g_debug_mode = 'Y' THEN
729 pa_debug.write_file('LOG','Inside Check Thresholds: Budget Type['||p_budget_type||
730 ']Finplan type['||p_fin_plan_type_id||']Amt Type['||p_threshold_amt_type||
731 ']Cost Type['||p_cip_cost_type||']Duration['||x_duration_threshold||
732 ']Threshold Amt['||x_amt_threshold||']p_start_date['||p_start_date||']');
733 END IF;
734
735 -- Call the client extension to reset the threshold values if desired
736 pa_client_extn_cap_int.check_thresholds
737 (p_project_id
738 ,p_task_id
739 ,p_rate_name
740 ,p_start_date
741 ,p_end_date
742 ,p_threshold_amt_type
743 ,p_budget_type
744 ,p_fin_plan_type_id
745 ,p_interest_calc_method
746 ,p_cip_cost_type
747 ,x_duration_threshold
748 ,x_amt_threshold
749 ,x_return_status
750 ,x_error_msg_count
751 ,x_error_msg_code);
752 IF g_debug_mode = 'Y' THEN
753 pa_debug.write_file('LOG','Threshold amt from Client Extn:Amt['||x_amt_threshold||
754 ']Duration['||x_duration_threshold||']x_return_status['||x_return_status||
755 ']g_bdgt_entry_level_code['||g_bdgt_entry_level_code||']');
756 End If;
757
758
759 -- If client extension returns an error, then return immediately
760 IF NVL(x_return_status,'S') <> 'S' THEN
761 x_return_status := 'U';
762 x_error_msg_count := 1;
763 x_error_msg_code := SQLERRM;
764 RETURN;
765 END IF;
766
767
768 -- Initialize the bypass variable for the delivered test
769 x_bypass := 'N';
770
771
772 -- Check the duration threshold
773 IF NVL(x_duration_threshold,0) <> 0 AND p_start_date IS NOT NULL THEN
774 ln_duration := (TRUNC(p_end_date) + 1) - TRUNC(p_start_date);
775
776 IF ln_duration < x_duration_threshold THEN
777 x_bypass := 'Y';
778 END IF;
779 END IF;
780
781
782 -- Check the amount threshold if set to 'budget'
783 IF x_bypass = 'N' AND NVL(x_amt_threshold,0) <> 0 AND
784 p_threshold_amt_type = 'BUDGET' THEN
785
786 -- Get the current budgeted amount for the project (and task id specified)
787 IF (p_task_id IS NULL OR g_bdgt_entry_level_code = 'L') Then /* added for bug fix:2972865 */
788 ln_amount := NVL(pa_fin_plan_utils.get_budgeted_amount
789 (p_project_id => p_project_id
790 ,p_task_id => p_task_id
791 ,p_fin_plan_type_id => p_fin_plan_type_id
792 ,p_budget_type_code => p_budget_type
793 ,p_amount_type => p_cip_cost_type)
794 ,0);
795
796 IF g_debug_mode = 'Y' THEN
797 pa_debug.write_file('LOG','Budget/Plan amt from Finplanutils API['||ln_amount||']');
798 End If;
799
800 /** This check is not required as the setup of budget should be ensured that
801 ** the budget is only for Capitalized projects and Tasks
802 -- Subtract budgeted amounts for non-capital tasks if checking at the project-level
803 IF p_task_id IS NULL THEN
804 FOR r_task in cur_non_cap_tasks LOOP
805 ln_task_amt := NVL(pa_fin_plan_utils.get_budgeted_amount
806 (p_project_id
807 ,r_task.task_id
808 ,p_fin_plan_type_id
809 ,p_budget_type
810 ,p_cip_cost_type)
811 ,0);
812
813 ln_amount := ln_amount - ln_task_amt;
814 END LOOP;
815 END IF;
816 ***/
817
818 IF NVL(ln_amount,0) < x_amt_threshold THEN
819 x_bypass := 'Y';
820 END IF;
821
822 END IF; -- end of Task level check
823
824 END IF; -- end of threshold amt type
825
826 x_return_status := 'S';
827 x_error_msg_count := 0;
828 x_error_msg_code := NULL;
829 EXCEPTION
830 WHEN NO_DATA_FOUND THEN
831 x_bypass := 'Y';
832 x_return_status := 'S';
833 x_error_msg_count := 0;
834 x_error_msg_code := NULL;
835 WHEN OTHERS THEN
836 x_return_status := 'U';
837 x_error_msg_count := 1;
838 x_error_msg_code := SQLERRM;
839 END;
840
841
842
843
844 /* -------------------------------------------
845 Validate the task for capitalized interest
846 ------------------------------------------- */
847 PROCEDURE validate_task
848 (p_project_id IN NUMBER
849 ,p_task_id IN NUMBER
850 ,p_exp_item_date IN DATE
851 ,p_period_end_date IN DATE
852 ,x_bypass OUT NOCOPY VARCHAR2
853 ,x_return_status OUT NOCOPY VARCHAR2
854 ,x_error_msg_count OUT NOCOPY NUMBER
855 ,x_error_msg_code OUT NOCOPY VARCHAR2)
856 IS
857 ln_task_id NUMBER;
858 lv_cap_flag VARCHAR2(1);
859 BEGIN
860 SELECT pt.task_id,nvl(pt.billable_flag,'N')
861 INTO ln_task_id,lv_cap_flag
862 FROM pa_tasks pt
863 WHERE TRUNC(p_period_end_date) <= TRUNC(NVL(pt.cint_stop_date,p_period_end_date))
864 AND NVL(pt.cint_eligible_flag,'Y') = 'Y'
865 AND TRUNC(p_exp_item_date) BETWEEN
866 TRUNC(NVL(pt.start_date,p_exp_item_date)) AND
867 TRUNC(NVL(pt.completion_date,p_exp_item_date))
868 --AND pt.chargeable_flag = 'Y'
869 AND pt.task_id = p_task_id
870 AND pt.project_id = p_project_id;
871
872
873 -- Perform a special capitalizable check to allow specific error returns
874 lv_cap_flag := task_capital_flag(p_project_id => p_project_id
875 ,p_task_id => p_task_id
876 ,p_task_bill_flag => 'Y'
877 -- capint EIs are always billable
878 -- irrespective of task billability lv_cap_flag );
879 );
880
881 IF lv_cap_flag = 'Y' THEN
882 x_bypass := 'N';
883 x_return_status := 'S';
884 x_error_msg_count := 0;
885 x_error_msg_code := NULL;
886 ELSE
887 x_bypass := 'Y';
888 x_return_status := 'E';
889 x_error_msg_count := 1;
890 x_error_msg_code := 'NON_CAPITAL';
891 END IF;
892 EXCEPTION
893 WHEN NO_DATA_FOUND THEN
894 x_bypass := 'Y';
895 x_return_status := 'S';
896 x_error_msg_count := 0;
897 x_error_msg_code := NULL;
898 WHEN OTHERS THEN
899 x_return_status := 'U';
900 x_error_msg_count := 1;
901 x_error_msg_code := SQLERRM;
902 END;
903
904
905
906
907 /* --------------------------------------------------
908 Get the interest rate multiplier from the schedule
909 -------------------------------------------------- */
910 PROCEDURE get_rate_multiplier
911 (p_rate_name IN VARCHAR2
912 ,p_sched_version_id IN NUMBER
913 ,p_task_owning_org_id IN NUMBER
914 ,p_proj_owning_org_id IN NUMBER
915 ,x_rate_mult OUT NOCOPY NUMBER
916 ,x_return_status OUT NOCOPY VARCHAR2
917 ,x_error_msg_count OUT NOCOPY NUMBER
918 ,x_error_msg_code OUT NOCOPY VARCHAR2)
919 IS
920 ln_organization_id hr_all_organization_units.organization_id%TYPE;
921
922 CURSOR cur_mult IS
923 SELECT multiplier
924 FROM pa_cint_rate_multipliers
925 WHERE rate_name = p_rate_name
926 AND organization_id = ln_organization_id
927 AND ind_rate_sch_revision_id = p_sched_version_id;
928
929 BEGIN
930 -- Retrieve the rate multiplier for the task owning org
931 ln_organization_id := p_task_owning_org_id;
932
933 OPEN cur_mult;
934 FETCH cur_mult INTO x_rate_mult;
935 CLOSE cur_mult;
936
937 -- If no rate found for the task org, try the project org
938 IF x_rate_mult IS NULL THEN
939 ln_organization_id := p_proj_owning_org_id;
940
941 OPEN cur_mult;
942 FETCH cur_mult INTO x_rate_mult;
943 CLOSE cur_mult;
944 END IF;
945
946
947 -- Set the return variables accordingly
948 IF x_rate_mult IS NULL THEN
949 x_return_status := 'E';
950 x_error_msg_count := 1;
951 x_error_msg_code := 'No rate value found for task or project organizations';
952 ELSE
953 x_return_status := 'S';
954 x_error_msg_count := 0;
955 x_error_msg_code := NULL;
956 END IF;
957 EXCEPTION
958 WHEN OTHERS THEN
959 x_return_status := 'U';
960 x_error_msg_count := 1;
961 x_error_msg_code := SQLERRM;
962 END;
963
964
965
966 ------------------------------------------------------------------
967 ------------------------------------------------------------------
968 -- O U T P U T P R O C E D U R E S --
969 ------------------------------------------------------------------
970 ------------------------------------------------------------------
971
972 /* -------------------------------------------
973 Write the Run row for the Cap Interest rate
974 ------------------------------------------- */
975 PROCEDURE write_run
976 (p_gl_period IN VARCHAR2
977 ,p_rule_id IN NUMBER
978 ,p_exp_type IN VARCHAR2
979 ,p_exp_item_date IN DATE
980 ,p_currency_code IN VARCHAR2
981 ,p_fiscal_year IN NUMBER
982 ,p_quarter_num IN NUMBER
983 ,p_period_num IN NUMBER
984 ,p_org_id IN NUMBER
985 ,p_rate_name IN VARCHAR2
986 ,p_autorelease IN VARCHAR2
987 ,x_run_id OUT NOCOPY NUMBER
988 ,x_return_status OUT NOCOPY VARCHAR2
989 ,x_error_msg_count OUT NOCOPY NUMBER
990 ,x_error_msg_code OUT NOCOPY VARCHAR2)
991 IS
992 BEGIN
993 SELECT pa_alloc_runs_s.nextval
994 INTO x_run_id
995 FROM DUAL;
996
997 pa_alloc_run.insert_alloc_runs
998 (x_run_id => x_run_id
999 ,p_rule_id => p_rule_id
1000 ,p_run_period => p_gl_period
1001 ,p_expnd_item_date => p_exp_item_date
1002 ,p_creation_date => SYSDATE
1003 ,p_created_by => g_created_by
1004 ,p_last_update_date => SYSDATE
1005 ,p_last_updated_by => g_last_updated_by
1006 ,p_last_update_login => g_last_update_login
1007 ,p_pool_percent => NULL
1008 ,p_period_type => NULL
1009 ,p_source_amount_type => NULL
1010 ,p_source_balance_category => NULL
1011 ,p_source_balance_type => NULL
1012 ,p_alloc_resource_list_id => NULL
1013 ,p_auto_release_flag => p_autorelease
1014 ,p_allocation_method => NULL
1015 ,p_imp_with_exception => NULL
1016 ,p_dup_targets_flag => NULL
1017 ,p_target_exp_type_class => 'PJ'
1018 ,p_target_exp_org_id => NULL
1019 ,p_target_exp_type => p_exp_type
1020 ,p_target_cost_type => NULL
1021 ,p_offset_exp_type_class => NULL
1022 ,p_offset_exp_org_id => NULL
1023 ,p_offset_exp_type => NULL
1024 ,p_offset_cost_type => NULL
1025 ,p_offset_method => NULL
1026 ,p_offset_project_id => NULL
1027 ,p_offset_task_id => NULL
1028 ,p_run_status => 'DS'
1029 ,p_basis_method => NULL
1030 ,p_basis_relative_period => NULL
1031 ,p_basis_amount_type => NULL
1032 ,p_basis_balance_category => NULL
1033 ,p_basis_budget_type_code => NULL
1034 ,p_basis_balance_type => NULL
1035 ,p_basis_resource_list_id => NULL
1036 ,p_fiscal_year => p_fiscal_year
1037 ,p_quarter => p_quarter_num
1038 ,p_period_num => p_period_num
1039 ,p_target_exp_group => p_rate_name
1040 ,p_offset_exp_group => NULL
1041 ,p_total_pool_amount => NULL
1042 ,p_allocated_amount => 0
1043 ,p_reversal_date => NULL
1044 ,p_draft_request_id => g_request_id
1045 ,p_draft_request_date => SYSDATE
1046 ,p_release_request_id => NULL
1047 ,p_release_request_date => NULL
1048 ,p_denom_currency_code => p_currency_code
1049 ,p_fixed_amount => NULL
1050 ,p_rev_target_exp_group => NULL
1051 ,p_rev_offset_exp_group => NULL
1052 ,p_org_id => p_org_id
1053 ,p_limit_target_projects_code => 'O'
1054 ,p_cint_rate_name => p_rate_name);
1055
1056 x_return_status := 'S';
1057 x_error_msg_count := 0;
1058 x_error_msg_code := NULL;
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061 x_return_status := 'U';
1062 x_error_msg_count := 1;
1063 x_error_msg_code := SQLERRM;
1064 END;
1065
1066
1067
1068 /* ----------------------------------------
1069 Write exception information to the table
1070 ---------------------------------------- */
1071 PROCEDURE write_exception
1072 (p_exception_code IN VARCHAR2
1073 ,p_task_id IN NUMBER
1074 ,p_project_id IN NUMBER
1075 ,p_rule_id IN NUMBER
1076 ,p_run_id IN NUMBER
1077 ,p_exception_type IN VARCHAR2
1078 ,x_return_status OUT NOCOPY VARCHAR2
1079 ,x_error_msg_count OUT NOCOPY NUMBER
1080 ,x_error_msg_code OUT NOCOPY VARCHAR2)
1081 IS
1082 BEGIN
1083
1084 pa_alloc_run.ins_alloc_exceptions
1085 (p_rule_id => p_rule_id
1086 ,p_run_id => p_run_id
1087 ,p_creation_date => SYSDATE
1088 ,p_created_by => g_created_by
1089 ,p_last_updated_date => SYSDATE
1090 ,p_last_updated_by => g_last_updated_by
1091 ,p_last_update_login => g_last_update_login
1092 ,p_level_code => 'T'
1093 ,p_exception_type => p_exception_type
1094 ,p_project_id => p_project_id
1095 ,p_task_id => p_task_id
1096 ,p_exception_code => p_exception_code);
1097
1098 x_return_status := 'S';
1099 x_error_msg_count := 0;
1100 x_error_msg_code := NULL;
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 x_return_status := 'U';
1104 x_error_msg_count := 1;
1105 x_error_msg_code := SQLERRM;
1106 END;
1107
1108
1109
1110 ------------------------------------------------------------------
1111 ------------------------------------------------------------------
1112 -- R E M O V A L P R O C E D U R E S --
1113 ------------------------------------------------------------------
1114 ------------------------------------------------------------------
1115
1116
1117 /* ------------------------------------------
1118 Remove the run row for the rate and period
1119 ------------------------------------------ */
1120 PROCEDURE remove_run
1121 (p_run_id IN NUMBER
1122 ,p_mode IN VARCHAR2
1123 ,x_return_status OUT NOCOPY VARCHAR2
1124 ,x_error_msg_count OUT NOCOPY NUMBER
1125 ,x_error_msg_code OUT NOCOPY VARCHAR2)
1126 IS
1127 l_exists varchar2(1) := 'N';
1128 BEGIN
1129 IF p_mode <> 'EXCEPTION' Then
1130 DELETE FROM pa_alloc_runs_all
1131 WHERE run_id = p_run_id;
1132 ELSE
1133 BEGIN
1134 /* Bug fix:3051131 if there is any un-expected error encounters and If there are no trxn or exceptions
1135 * created then delete the run created in the exception portion
1136 */
1137 IF g_debug_mode = 'Y' THEN
1138 pa_debug.write_file('LOG','Inside remove_run mode = EXCEPTION');
1139 End If;
1140 IF p_run_id is NOT NULL then
1141
1142 SELECT 'Y'
1143 INTO l_exists
1144 FROM dual
1145 WHERE EXISTS
1146 (SELECT NULL
1147 FROM PA_ALLOC_TXN_DETAILS det
1148 WHERE det.run_id = p_run_id)
1149 OR
1150 EXISTS (SELECT null
1151 FROM pa_alloc_exceptions exc
1152 where exc.run_id = p_run_id ) ;
1153
1154 IF l_exists = 'Y' Then
1155 UPDATE pa_alloc_runs_all run
1156 SET run.run_status = 'DF'
1157 WHERE run.run_id = p_run_id
1158 AND EXISTS (SELECT null
1159 FROM pa_alloc_exceptions exc
1160 WHERE exc.run_id = run.run_id ) ;
1161 Commit;
1162 End If;
1163
1164
1165 End If;
1166
1167
1168 EXCEPTION
1169 WHEN NO_DATA_FOUND THEN
1170 IF g_debug_mode = 'Y' THEN
1171 pa_debug.write_file('LOG',' No Trxn found, Removing the run');
1172 End If;
1173 IF l_exists = 'N' Then
1174 Delete from pa_alloc_runs_all
1175 where run_id = p_run_id;
1176 Commit;
1177 End If;
1178 WHEN OTHERS THEN
1179 RAISE;
1180 END;
1181 END IF; -- end of p_mode
1182
1183 x_return_status := 'S';
1184 x_error_msg_count := 0;
1185 x_error_msg_code := NULL;
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 x_return_status := 'U';
1189 x_error_msg_count := 1;
1190 x_error_msg_code := SQLERRM;
1191 END;
1192
1193
1194
1195 ----------------------------------------------------------
1196 ----------------------------------------------------------
1197 -- M A I N P R O C E D U R E S --
1198 ----------------------------------------------------------
1199 ----------------------------------------------------------
1200
1201 /* -------------------------------------
1202 Generate cap interest transactions
1203 ------------------------------------- */
1204 PROCEDURE generate_cap_interest
1205 (p_from_project_num IN VARCHAR2 DEFAULT NULL
1206 ,p_to_project_num IN VARCHAR2 DEFAULT NULL
1207 ,p_gl_period IN VARCHAR2
1208 ,p_exp_item_date IN DATE
1209 ,p_source_details IN VARCHAR2 DEFAULT 'N'
1210 ,p_autorelease IN VARCHAR2 DEFAULT 'N'
1211 ,p_mode IN VARCHAR2 DEFAULT 'G'
1212 ,x_run_id IN OUT NOCOPY NUMBER
1213 ,x_return_status OUT NOCOPY VARCHAR2
1214 ,x_error_msg_count OUT NOCOPY NUMBER
1215 ,x_error_msg_code OUT NOCOPY VARCHAR2)
1216 IS
1217
1218 -- Alloc Exception Codes
1219 c_proj_lock pa_alloc_exceptions.exception_code%TYPE :=
1220 'PA_CINT_PROJ_LOCK';
1221 c_proj_batches pa_alloc_exceptions.exception_code%TYPE :=
1222 'PA_CINT_PROJ_BATCHES';
1223 c_proj_sched pa_alloc_exceptions.exception_code%TYPE :=
1224 'PA_CINT_PROJ_SCHEDULES';
1225 c_proj_sch_no_rate pa_alloc_exceptions.exception_code%TYPE :=
1226 'PA_CINT_PROJ_SCH_NO_RATE';
1227 c_proj_threshold pa_alloc_exceptions.exception_code%TYPE :=
1228 'PA_CINT_PROJ_THRESHOLDS';
1229 c_proj_no_txns pa_alloc_exceptions.exception_code%TYPE :=
1230 'PA_CINT_PROJ_NO_TXNS';
1231 c_task_threshold pa_alloc_exceptions.exception_code%TYPE :=
1232 'PA_CINT_TASK_THRESHOLDS';
1233 c_task_null_target pa_alloc_exceptions.exception_code%TYPE :=
1234 'PA_CINT_TASK_NULL_TARGET';
1235 c_task_not_valid pa_alloc_exceptions.exception_code%TYPE :=
1236 'PA_CINT_TASK_NOT_VALID';
1237 c_rate_mult pa_alloc_exceptions.exception_code%TYPE :=
1238 'PA_CINT_RATE_MULTIPLIER';
1239
1240
1241 -- PLSQL table types and variables
1242
1243 TYPE Char4000TabTyp IS TABLE OF VARCHAR2(4000)
1244 INDEX BY BINARY_INTEGER;
1245
1246 lt_alloc_txn_id pa_plsql_datatypes.idtabtyp;
1247 lt_task_id pa_plsql_datatypes.idtabtyp;
1248 lt_task_num pa_plsql_datatypes.char30tabtyp;
1249 lt_task_owning_org_id pa_plsql_datatypes.idtabtyp;
1250 lt_task_start_date pa_plsql_datatypes.datetabtyp;
1251 lt_task_end_date pa_plsql_datatypes.datetabtyp;
1252 lt_exp_org_id pa_plsql_datatypes.idtabtyp;
1253 lt_rate_mult pa_plsql_datatypes.amttabtyp;
1254 lt_grouping_method char4000tabtyp;
1255 lt_cdl_status pa_plsql_datatypes.char30tabtyp;
1256 lt_prior_period_amt pa_plsql_datatypes.amttabtyp;
1257 lt_curr_period_amt pa_plsql_datatypes.amttabtyp;
1258 lt_target_task_id pa_plsql_datatypes.idtabtyp;
1259 lt_cap_int_amt pa_plsql_datatypes.amttabtyp;
1260 lt_attribute_category pa_plsql_datatypes.char30tabtyp;
1261 lt_attribute1 pa_plsql_datatypes.char150tabtyp;
1262 lt_attribute2 pa_plsql_datatypes.char150tabtyp;
1263 lt_attribute3 pa_plsql_datatypes.char150tabtyp;
1264 lt_attribute4 pa_plsql_datatypes.char150tabtyp;
1265 lt_attribute5 pa_plsql_datatypes.char150tabtyp;
1266 lt_attribute6 pa_plsql_datatypes.char150tabtyp;
1267 lt_attribute7 pa_plsql_datatypes.char150tabtyp;
1268 lt_attribute8 pa_plsql_datatypes.char150tabtyp;
1269 lt_attribute9 pa_plsql_datatypes.char150tabtyp;
1270 lt_attribute10 pa_plsql_datatypes.char150tabtyp;
1271 lt_process_task_flag pa_plsql_datatypes.char1tabtyp;
1272
1273
1274 -- Process control variables
1275
1276 lv_bypass_project VARCHAR2(1);
1277 lv_bypass_task VARCHAR2(1);
1278 lv_exception_code pa_alloc_exceptions.exception_code%TYPE;
1279
1280 ln_proj_processed NUMBER;
1281 ln_proj_written NUMBER;
1282 ln_trans_written NUMBER;
1283 ln_error_written NUMBER;
1284 ln_warning_written NUMBER;
1285 ln_proj_trans_count NUMBER;
1286 ln_proj_error_count NUMBER;
1287 ln_proj_warning_count NUMBER;
1288 ln_proj_detail_count NUMBER;
1289
1290 process_error EXCEPTION;
1291
1292
1293 -- Working storage variables
1294
1295 ln_org_id pa_implementations_all.org_id%TYPE;
1296 lv_currency_code gl_sets_of_books.currency_code%TYPE;
1297
1298 ld_period_start_date gl_period_statuses.start_date%TYPE;
1299
1300 ln_fiscal_year gl_period_statuses.period_year%TYPE;
1301 ln_quarter_num gl_period_statuses.quarter_num%TYPE;
1302 ln_period_num gl_period_statuses.period_num%TYPE;
1303
1304 ln_curr_period_mult NUMBER;
1305 ln_period_mult NUMBER;
1306 ln_rate_mult pa_ind_cost_multipliers.multiplier%TYPE;
1307
1308 ln_run_id pa_alloc_runs_all.run_id%TYPE;
1309 lv_exp_org_source pa_cint_rate_info_all.exp_org_source%TYPE;
1310 lv_interest_calc_method pa_cint_rate_info_all.interest_calculation_method%TYPE;
1311 lv_threshold_amt_type pa_cint_rate_info_all.threshold_amt_type%TYPE;
1312 ln_proj_duration_threshold pa_cint_rate_info_all.proj_duration_threshold%TYPE;
1313 ln_proj_amt_threshold pa_cint_rate_info_all.proj_amt_threshold%TYPE;
1314 ln_task_duration_threshold pa_cint_rate_info_all.task_duration_threshold%TYPE;
1315 ln_task_amt_threshold pa_cint_rate_info_all.task_amt_threshold%TYPE;
1316
1317 ln_proj_owning_org_id pa_projects_all.carrying_out_organization_id%TYPE;
1318 ln_sched_version_id pa_ind_rate_sch_revisions.ind_rate_sch_revision_id%TYPE;
1319 lv_cip_cost_type pa_project_types_all.capital_cost_type_code%TYPE;
1320 lv_burden_method pa_project_types_all.burden_amt_display_method%TYPE;
1321 lv_tot_burden_flag pa_project_types_all.total_burden_flag%TYPE;
1322
1323 ln_target_task_id pa_tasks.task_id%TYPE;
1324 ln_except_task_id pa_tasks.task_id%TYPE;
1325 lv_target_task_num pa_tasks.task_number%TYPE;
1326
1327 ln_curr_task_id pa_tasks.task_id%TYPE;
1328 lv_curr_task_num pa_tasks.task_number%TYPE;
1329 ln_rate_trans_amt NUMBER;
1330 lv_rate_status pa_alloc_runs.run_status%TYPE;
1331 ln_proj_tot_amt NUMBER;
1332 ln_proj_open_amt NUMBER;
1333 ln_task_tot_amt NUMBER;
1334 ln_task_open_amt NUMBER;
1335
1336 ln_task_start NUMBER;
1337 ln_task_last NUMBER;
1338
1339 lv_first_exp_flag VARCHAR2(1);
1340 lv_exception_type pa_alloc_exceptions.exception_type%TYPE;
1341
1342 ln_cap_int_amt NUMBER;
1343 v_success_flag NUMBER;
1344
1345 l_init_run_id NUMBER; -- R12 NOCOPY Mandate
1346
1347 -- Rate Cursor
1348
1349 /* Commented for Bug 6757697 End */
1350 /* CURSOR cur_rates IS
1351 SELECT picc.ind_cost_code rate_name
1352 ,picc.expenditure_type exp_type
1353 ,pcri.exp_org_source exp_org_source
1354 ,pcri.threshold_amt_type threshold_amt_type
1355 ,pcri.budget_type_code budget_type
1356 ,pcri.proj_amt_threshold proj_amt_threshold
1357 ,pcri.task_amt_threshold task_amt_threshold
1358 ,pcri.proj_duration_threshold proj_duration_threshold
1359 ,pcri.task_duration_threshold task_duration_threshold
1360 ,pcri.curr_period_convention curr_period_convention
1361 ,pcri.interest_calculation_method interest_calc_method
1362 ,pcri.period_rate_code period_rate_code
1363 ,pcri.fin_plan_type_id fin_plan_type_id
1364 FROM pa_cint_rate_info pcri
1365 ,pa_ind_cost_codes picc
1366 WHERE pcri.ind_cost_code = picc.ind_cost_code
1367 AND picc.ind_cost_code_usage = 'CAPITALIZED_INTEREST'
1368 /* Start Bug fix :3028240
1369 AND /** As discussed with murali the end date of the run period be between the
1370 * start date active and end date active, we need not take the partial effective
1371 * of the rate names
1372 --((trunc(g_period_start_date) BETWEEN trunc(picc.start_date_active)
1373 --AND trunc(nvl(picc.end_date_active,g_period_end_date)))
1374 --OR
1375 **
1376 (trunc(g_period_end_date) BETWEEN trunc(picc.start_date_active)
1377 AND trunc(nvl(picc.end_date_active,g_period_end_date)))
1378 --)
1379 /* End Bug fix :3028240
1380 AND EXISTS
1381 (SELECT 'X'
1382 FROM pa_projects pp
1383 ,pa_ind_rate_schedules_all_bg pirs
1384 ,pa_ind_rate_sch_revisions pirsv
1385 /* Bug fix:3208751 ,pa_cint_rate_multipliers pccm
1386 ,pa_ind_cost_multipliers pccm
1387 WHERE pp.cint_rate_sch_id = pirs.ind_rate_sch_id
1388 AND pirs.ind_rate_sch_usage = 'CAPITALIZED_INTEREST'
1389 AND pirs.ind_rate_sch_id = pirsv.ind_rate_sch_id
1390 AND pccm.ind_rate_sch_revision_id = pirsv.ind_rate_sch_revision_id
1391 /** Added this condtion for bug fix :2984441 *
1392 AND TRUNC(g_period_end_date) BETWEEN
1393 TRUNC(pirsv.start_date_active) AND TRUNC(NVL(pirsv.end_date_active,g_period_end_date))
1394 --AND NVL(pirsv.compiled_flag,'N') = 'Y'
1395 /** End of bug fix:2984441 **/
1396 /* Bug fix: 3208751 AND pccm.rate_name = picc.ind_cost_code
1397 AND pccm.ind_cost_code = picc.ind_cost_code
1398 AND pp.segment1 BETWEEN
1399 NVL(p_from_project_num,pp.segment1) AND NVL(p_to_project_num,pp.segment1)
1400 )
1401 ORDER BY picc.ind_cost_code; */
1402 /* Commented for Bug 6757697 End */
1403 /* Added for Bug 6757697 Start */
1404 CURSOR cur_rates IS
1405 SELECT distinct picc.ind_cost_code rate_name -- added distinct for bug 8876299
1406 ,picc.expenditure_type exp_type
1407 ,pcri.exp_org_source exp_org_source
1408 ,pcri.threshold_amt_type threshold_amt_type
1409 ,pcri.budget_type_code budget_type
1410 ,pcri.proj_amt_threshold proj_amt_threshold
1411 ,pcri.task_amt_threshold task_amt_threshold
1412 ,pcri.proj_duration_threshold proj_duration_threshold
1413 ,pcri.task_duration_threshold task_duration_threshold
1414 ,pcri.curr_period_convention curr_period_convention
1415 ,pcri.interest_calculation_method interest_calc_method
1416 ,pcri.period_rate_code period_rate_code
1417 ,pcri.fin_plan_type_id fin_plan_type_id
1418 ,pirs.ind_rate_sch_id interest_sch_id
1419 FROM pa_cint_rate_info pcri
1420 ,pa_ind_cost_codes picc
1421 ,pa_ind_rate_schedules_all_bg pirs
1422 ,pa_ind_rate_sch_revisions pirsv
1423 ,pa_ind_cost_multipliers pccm
1424 WHERE pcri.ind_cost_code = picc.ind_cost_code
1425 AND picc.ind_cost_code_usage = 'CAPITALIZED_INTEREST'
1426 AND (trunc(g_period_end_date) BETWEEN trunc(picc.start_date_active)
1427 AND trunc(nvl(picc.end_date_active,g_period_end_date)))
1428 AND pirs.ind_rate_sch_id = pirsv.ind_rate_sch_id
1429 /* Added the condition for bug 8334911 */
1430 AND (TRUNC(g_period_end_date) BETWEEN
1431 TRUNC(pirsv.start_date_active) AND TRUNC(NVL(pirsv.end_date_active,g_period_end_date)))
1432 AND pccm.ind_rate_sch_revision_id = pirsv.ind_rate_sch_revision_id
1433 AND pccm.ind_cost_code = picc.ind_cost_code
1434 -- AND pccm.ORGANIZATION_ID = pcri.org_id /* commented for bug 8625855 */
1435 AND EXISTS
1436 (SELECT 'X'
1437 FROM pa_projects pp
1438 WHERE pp.cint_rate_sch_id = pirs.ind_rate_sch_id
1439 AND pp.template_flag = 'N'
1440 AND pp.segment1 BETWEEN
1441 NVL(p_from_project_num,pp.segment1) AND NVL(p_to_project_num,pp.segment1))
1442 ORDER BY picc.ind_cost_code;
1443 /* Added for Bug 6757697 End */
1444
1445 r_rate cur_rates%ROWTYPE;
1446
1447
1448 -- Project Cursor
1449
1450 CURSOR cur_projects IS
1451 SELECT pp.project_id project_id
1452 ,pp.segment1 project_num
1453 ,pp.carrying_out_organization_id owning_org_id
1454 ,pp.cint_rate_sch_id interest_sch_id
1455 ,pp.start_date start_date
1456 ,ppt.capital_cost_type_code cip_cost_type
1457 ,ppt.burden_amt_display_method burden_method
1458 ,ppt.total_burden_flag tot_burden_flag
1459 FROM pa_project_types ppt
1460 ,pa_projects pp
1461 WHERE TRUNC(g_period_end_date) <= TRUNC(NVL(pp.cint_stop_date,g_period_end_date))
1462 AND pp.cint_rate_sch_id IS NOT NULL
1463 AND NVL(pp.cint_eligible_flag,'Y') = 'Y'
1464 AND TRUNC(g_exp_item_date) BETWEEN
1465 TRUNC(NVL(pp.start_date,g_exp_item_date)) AND TRUNC(NVL(pp.completion_date,g_exp_item_date))
1466 AND pa_project_utils.Check_prj_stus_action_allowed
1467 (pp.project_status_code
1468 ,'CAPITALIZED_INTEREST') = 'Y'
1469 AND pa_project_utils.Check_prj_stus_action_allowed
1470 (pp.project_status_code
1471 ,'NEW_TXNS') = 'Y'
1472 AND pp.project_status_code <> 'CLOSED'
1473 AND ppt.project_type_class_code = 'CAPITAL'
1474 AND ppt.project_type = pp.project_type
1475 AND pp.template_flag = 'N'
1476 AND pp.segment1 BETWEEN
1477 NVL(p_from_project_num,pp.segment1) AND NVL(p_to_project_num,pp.segment1)
1478 ORDER BY pp.segment1;
1479
1480 r_project cur_projects%ROWTYPE;
1481
1482
1483 -- Task / Cost Distribution Line Cursor
1484
1485 CURSOR cur_cdls IS
1486 SELECT pctd.task_id task_id
1487 ,pctd.task_number task_num
1488 ,pctd.task_owning_org_id task_owning_org_id
1489 ,pctd.task_start_date task_start_date
1490 ,pctd.task_completion_date task_end_date
1491 ,pctd.target_exp_organization_id exp_org_id
1492 ,pctd.rate_multiplier rate_mult
1493 ,pctd.cint_grouping_method grouping_method
1494 ,pctd.cint_cdl_status cdl_status
1495 ,SUM(DECODE(SIGN(ld_period_start_date - pctd.gl_date)
1496 ,1, pctd.amount
1497 , 0)) prior_period_amt
1498 ,SUM(DECODE(SIGN(ld_period_start_date - pctd.gl_date)
1499 ,1, 0
1500 , pctd.amount)) curr_period_amt
1501 ,'Y' process_task_flag
1502 --Bug fix:3051022 Added these columns to initialize collection tables for each element
1503 ,NULL alloc_txn_id
1504 ,NULL target_task_id
1505 ,NULL cap_int_amt
1506 ,NULL attribute_category
1507 ,NULL attribute1
1508 ,NULL attribute2
1509 ,NULL attribute3
1510 ,NULL attribute4
1511 ,NULL attribute5
1512 ,NULL attribute6
1513 ,NULL attribute7
1514 ,NULL attribute8
1515 ,NULL attribute9
1516 ,NULL attribute10
1517 FROM pa_cint_txn_details_v pctd
1518 WHERE /* Commented out this condition for performance issues
1519 --(
1520 -- (NVL(lv_threshold_amt_type,'TOTAL_CIP') = 'TOTAL_CIP')
1521 -- OR
1522 -- (NVL(lv_threshold_amt_type,'TOTAL_CIP') <> 'TOTAL_CIP'
1523 -- AND pctd.cint_cdl_status = 'OPEN')
1524 --)
1525 --AND
1526 **/
1527 pctd.gl_date <= TRUNC(g_period_end_date)
1528 AND TRUNC(g_period_end_date) <= TRUNC(NVL(pctd.task_cint_stop_date, g_period_end_date))
1529 AND TRUNC(g_exp_item_date) BETWEEN
1530 TRUNC(NVL(pctd.task_start_date, g_exp_item_date)) AND
1531 TRUNC(NVL(pctd.task_completion_date, g_exp_item_date))
1532 AND pctd.project_id = g_project_id
1533 AND pctd.cint_rate_name = g_rate_name
1534 AND pctd.period_name = g_gl_period
1535 GROUP BY pctd.task_id
1536 ,pctd.task_number
1537 ,pctd.task_owning_org_id
1538 ,pctd.task_start_date
1539 ,pctd.task_completion_date
1540 ,pctd.target_exp_organization_id
1541 ,pctd.rate_multiplier
1542 ,pctd.cint_grouping_method
1543 ,pctd.cint_cdl_status
1544 ,'Y'
1545 ORDER BY pctd.task_id
1546 ,pctd.target_exp_organization_id
1547 ,pctd.rate_multiplier
1548 ,pctd.cint_grouping_method;
1549
1550
1551 BEGIN
1552
1553 -- Initialize the out variables
1554 x_return_status := 'S';
1555 x_error_msg_count := 0;
1556 x_error_msg_code := NULL;
1557 l_init_run_id := x_run_id; -- store passed in value for when others.
1558
1559 -- Initialize the error stack
1560 pa_debug.init_err_stack ('PA_CAP_INT_PVT.GENERATE_CAP_INTEREST');
1561
1562 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
1563 g_debug_mode := NVL(g_debug_mode, 'N');
1564
1565 pa_debug.set_process
1566 (x_process => 'PLSQL'
1567 ,x_write_file => 'LOG'
1568 ,x_debug_mode => g_debug_mode);
1569
1570
1571 -- Clear the message stack
1572 fnd_msg_pub.initialize;
1573
1574 If g_debug_mode = 'Y' Then
1575 pa_debug.write_file('LOG',substr('INSIDE Generate Capint API IN PARAMS: p_from_project_num ['
1576 ||p_from_project_num||']p_to_project_num['||p_to_project_num||
1577 ']p_gl_period['||p_gl_period||']p_exp_item_date['||p_exp_item_date||
1578 ']p_source_details['||p_source_details||']p_autorelease['||p_autorelease||
1579 ']p_mode['||p_mode||']x_run_id['||x_run_id||']',1,250) );
1580 End If;
1581
1582
1583 -- Initialize process variable
1584 g_created_by := NVL(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
1585 g_last_update_login := NVL(TO_NUMBER(fnd_profile.value('LOGIN_ID')), -1);
1586 g_last_updated_by := g_created_by;
1587 g_request_id := fnd_global.conc_request_id;
1588
1589
1590 -- Execution section if this is run in 'Generate' mode
1591 IF p_mode = 'G' THEN
1592
1593 IF g_debug_mode = 'Y' THEN
1594 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||' Start program');
1595 END IF;
1596
1597
1598 -- Get the start and end dates for the parameter GL period
1599 get_period_dates
1600 (p_gl_period
1601 ,ld_period_start_date
1602 ,g_period_end_date
1603 ,ln_fiscal_year
1604 ,ln_quarter_num
1605 ,ln_period_num
1606 ,x_return_status
1607 ,x_error_msg_count
1608 ,x_error_msg_code);
1609
1610 IF x_return_status = 'U' THEN
1611 pa_debug.g_err_stage := 'Get_Period_Dates for Period '||p_gl_period||
1612 ']x-errmsg['||x_error_msg_code||']';
1613 RAISE process_error;
1614 END IF;
1615
1616
1617 -- Get currency code and ORG_ID
1618 pa_multi_currency.init;
1619 lv_currency_code := pa_multi_currency.g_accounting_currency_code;
1620 ln_org_id := pa_utils4.get_org_id;
1621
1622
1623 -- Set global variable for GL period
1624 g_gl_period := p_gl_period;
1625 g_period_start_date := ld_period_start_date;
1626
1627 -- Set global variable for Exp Item Date
1628 g_exp_item_date := NVL(p_exp_item_date,g_period_end_date);
1629
1630 IF g_debug_mode = 'Y' THEN
1631 pa_debug.write_file('LOG','Global Var:Currency['||lv_currency_code||']Org['||ln_org_id||
1632 ']g_gl_period['||g_gl_period||']g_period_start_date['||g_period_start_date||
1633 ']g_period_end_date['||g_period_end_date||']g_exp_item_date['||g_exp_item_date||
1634 ']');
1635 END IF;
1636
1637 --------------------------------------
1638 -- Loop through the Cap Interest rates
1639 --------------------------------------
1640 FOR r_rate IN cur_rates LOOP
1641
1642 IF g_debug_mode = 'Y' THEN
1643 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1644 ' Rate ['||r_rate.rate_name||']');
1645 END IF;
1646
1647 -- Initialize rate process variables
1648 ln_proj_processed := 0;
1649 ln_proj_written := 0;
1650 ln_trans_written := 0;
1651 ln_error_written := 0;
1652 ln_warning_written := 0;
1653 ln_rate_trans_amt := 0;
1654
1655 g_rate_name := r_rate.rate_name;
1656 lv_exp_org_source := r_rate.exp_org_source;
1657 lv_interest_calc_method := r_rate.interest_calc_method;
1658 lv_threshold_amt_type := r_rate.threshold_amt_type;
1659
1660 -- Determine the current period multiplier
1661 IF r_rate.curr_period_convention = 'FULL' THEN
1662 ln_curr_period_mult := 1;
1663 ELSIF r_rate.curr_period_convention = 'HALF' THEN
1664 ln_curr_period_mult := .5;
1665 ELSE
1666 ln_curr_period_mult := 0;
1667 END IF;
1668
1669 IF g_debug_mode = 'Y' THEN
1670 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1671 ' Current Period Multiplier['||TO_CHAR(ln_curr_period_mult)||']');
1672 END IF;
1673
1674
1675 -- Determine the monthly multiplier
1676 IF r_rate.period_rate_code = 'BY_NUMBER_OF_DAYS' THEN
1677 ln_period_mult := ((g_period_end_date + 1) - ld_period_start_date)/365;
1678 ELSE
1679 ln_period_mult := 1/num_of_periods(ln_fiscal_year);
1680 END IF;
1681
1682 IF g_debug_mode = 'Y' THEN
1683 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1684 ' Period Rate Multiplier['||SUBSTR(TO_CHAR(ln_period_mult),1,6)||']');
1685 END IF;
1686
1687
1688 -- Write the run row for this rate
1689 write_run
1690 (g_gl_period
1691 ,g_cap_int_rule_id
1692 ,r_rate.exp_type
1693 ,g_exp_item_date
1694 ,lv_currency_code
1695 ,ln_fiscal_year
1696 ,ln_quarter_num
1697 ,ln_period_num
1698 ,ln_org_id
1699 ,g_rate_name
1700 ,p_autorelease
1701 ,ln_run_id
1702 ,x_return_status
1703 ,x_error_msg_count
1704 ,x_error_msg_code);
1705
1706 IF x_return_status = 'U' THEN
1707 pa_debug.g_err_stage := 'Write_Run for Rate['||g_rate_name||
1708 'Error-msg['||x_error_msg_code;
1709 pa_debug.write_file('LOG',substr(pa_debug.g_err_stage,1,250));
1710 RAISE process_error;
1711 END IF;
1712
1713 COMMIT;
1714
1715 IF g_debug_mode = 'Y' THEN
1716 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1717 ' Run ID ['||TO_CHAR(ln_run_id)||']');
1718 END IF;
1719
1720
1721 ----------------------------
1722 -- Loop through the projects
1723 ----------------------------
1724 FOR r_project IN cur_projects LOOP
1725
1726 IF R_RATE.INTEREST_SCH_ID = R_PROJECT.INTEREST_SCH_ID THEN /* Added for Bug 6757697 */
1727
1728 IF g_debug_mode = 'Y' THEN
1729 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1730 ' Project Number ['||r_project.project_num||']' ||
1731 ' ID ['||TO_CHAR(r_project.project_id)||']');
1732 END IF;
1733
1734
1735 -- Acquire a lock on the project info
1736 IF pa_debug.acquire_user_lock('PA_CAP_INT_'||to_char(r_project.project_id))<>0 THEN
1737 IF g_debug_mode = 'Y' THEN
1738 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1739 ' Could not lock the project '||r_project.project_num);
1740 END IF;
1741
1742 lv_bypass_project := 'Y';
1743 pa_debug.g_err_stage := 'Lock for Project '||r_project.project_num;
1744 lv_exception_code := c_proj_lock;
1745 lv_exception_type := 'E';
1746 END IF;
1747
1748 -- Initialize project process variables
1749 g_project_id := r_project.project_id;
1750 ln_proj_owning_org_id := r_project.owning_org_id;
1751 lv_cip_cost_type := r_project.cip_cost_type;
1752 lv_burden_method := r_project.burden_method;
1753 lv_tot_burden_flag := r_project.tot_burden_flag;
1754
1755 ln_proj_duration_threshold := r_rate.proj_duration_threshold;
1756 ln_proj_amt_threshold := r_rate.proj_amt_threshold;
1757
1758 ln_proj_processed := ln_proj_processed + 1;
1759 lv_bypass_project := 'N';
1760 ln_proj_trans_count := 0;
1761 ln_proj_error_count := 0;
1762 ln_proj_warning_count := 0;
1763 ln_proj_detail_count := 0;
1764
1765 lv_first_exp_flag := 'Y';
1766 ln_curr_task_id := -99;
1767
1768
1769 -- Check whether the project exists in current batches
1770 IF lv_bypass_project = 'N' AND x_return_status = 'S' THEN
1771 IF g_debug_mode = 'Y' THEN
1772 pa_debug.write_file('LOG','Check Project batch exists for the period');
1773 END IF;
1774 check_project_batches
1775 (g_project_id
1776 ,g_cap_int_rule_id
1777 ,ln_fiscal_year
1778 ,ln_quarter_num
1779 ,ln_period_num
1780 ,g_rate_name
1781 ,lv_bypass_project
1782 ,x_return_status
1783 ,x_error_msg_count
1784 ,x_error_msg_code);
1785
1786 pa_debug.g_err_stage := 'Check_Project_Batches for Project '||
1787 r_project.project_num;
1788 IF g_debug_mode = 'Y' THEN
1789 pa_debug.write_file('LOG','lv_bypass_project['||lv_bypass_project||
1790 ']x_return_status['||x_return_status||
1791 ']x-errmsg['||x_error_msg_code||']' );
1792 END IF;
1793
1794 IF x_return_status = 'U' THEN
1795 RAISE process_error;
1796 END IF;
1797
1798 IF lv_bypass_project = 'Y' THEN
1799 lv_exception_code := c_proj_batches;
1800 lv_exception_type := 'W';
1801 END IF;
1802 END IF;
1803
1804
1805 -- Check whether the project has a compiled Cap Interest schedule for the exp item date
1806 IF lv_bypass_project = 'N' AND x_return_status = 'S' THEN
1807 IF g_debug_mode = 'Y' THEN
1808 pa_debug.write_file('LOG','Check Project Schedule exists for the period');
1809 END IF;
1810 check_project_schedule
1811 (r_project.interest_sch_id
1812 ,g_period_end_date
1813 ,ln_sched_version_id
1814 ,lv_bypass_project
1815 ,x_return_status
1816 ,x_error_msg_count
1817 ,x_error_msg_code);
1818
1819 pa_debug.g_err_stage := 'Check_Project_Schedule for Project '||
1820 r_project.project_num;
1821
1822 IF g_debug_mode = 'Y' THEN
1823 pa_debug.write_file('LOG','lv_bypass_project['||lv_bypass_project||
1824 ']x_return_status['||x_return_status||
1825 ']x-errmsg['||x_error_msg_code||']' );
1826 END IF;
1827
1828 IF x_return_status = 'U' THEN
1829 RAISE process_error;
1830 END IF;
1831
1832 IF lv_bypass_project = 'Y' THEN
1833 lv_exception_code := c_proj_sched;
1834 lv_exception_type := 'W';
1835 END IF;
1836 END IF;
1837
1838 /* Bug fix: 3227816 Starts here */
1839 --Check if the rate name is used for this schedule
1840 IF lv_bypass_project = 'N' AND x_return_status = 'S' THEN
1841 IF g_debug_mode = 'Y' THEN
1842 pa_debug.write_file('LOG','Check Schedule has Ratename ');
1843 END IF;
1844 check_schedule_has_ratename
1845 (p_sch_id => r_project.interest_sch_id
1846 ,p_sch_rev_date => g_period_end_date
1847 ,p_sch_rev_id => ln_sched_version_id
1848 ,p_rate_name => r_rate.rate_name
1849 ,x_bypass_project => lv_bypass_project
1850 ,x_return_status => x_return_status
1851 ,x_error_msg_count => x_error_msg_count
1852 ,x_error_msg_code => x_error_msg_code );
1853
1854 IF g_debug_mode = 'Y' THEN
1855 pa_debug.write_file('LOG','lv_bypass_project['||lv_bypass_project||
1856 ']x_return_status['||x_return_status||
1857 ']x-errmsg['||x_error_msg_code||']' );
1858 END IF;
1859
1860 IF x_return_status = 'U' THEN
1861 RAISE process_error;
1862 END IF;
1863
1864 IF lv_bypass_project = 'Y' THEN
1865 lv_exception_code := c_proj_sch_no_rate;
1866 lv_exception_type := 'W';
1867 END IF;
1868 END IF;
1869 /* Bug fix: 3227816 Ends here */
1870
1871
1872 -- Check whether the project has met the threshold values
1873 IF lv_bypass_project = 'N' AND x_return_status = 'S' Then
1874 IF g_debug_mode = 'Y' THEN
1875 pa_debug.write_file('LOG','Check Project Thresholds');
1876 END IF;
1877
1878 /* Bug fix:2972865 Set the Budget entry level code */
1879 g_bdgt_entry_level_code := Get_Bdgt_entry_level_code
1880 (p_project_id => g_project_id
1881 ,p_threshold_amt_type => lv_threshold_amt_type
1882 ,p_budget_type_code => r_rate.budget_type
1883 ,p_fin_plan_type_id =>r_rate.fin_plan_type_id
1884 );
1885
1886 check_thresholds
1887 (g_project_id
1888 ,NULL
1889 ,g_rate_name
1890 ,r_project.start_date
1891 ,g_period_end_date
1892 ,lv_threshold_amt_type
1893 ,r_rate.budget_type
1894 ,r_rate.fin_plan_type_id
1895 ,lv_interest_calc_method
1896 ,lv_cip_cost_type
1897 ,ln_proj_duration_threshold
1898 ,ln_proj_amt_threshold
1899 ,lv_bypass_project
1900 ,x_return_status
1901 ,x_error_msg_count
1902 ,x_error_msg_code);
1903
1904 pa_debug.g_err_stage := 'Check_Thresholds for Project '||r_project.project_num;
1905 IF g_debug_mode = 'Y' THEN
1906 pa_debug.write_file('LOG','lv_bypass_project['||lv_bypass_project||
1907 ']x_return_status['||x_return_status||
1908 ']x-errmsg['||x_error_msg_code||']');
1909 END IF;
1910 IF x_return_status = 'U' THEN
1911 RAISE process_error;
1912 END IF;
1913
1914 IF lv_bypass_project = 'Y' THEN
1915 lv_exception_code := c_proj_threshold;
1916 lv_exception_type := 'W';
1917 END IF;
1918 END IF;
1919
1920
1921
1922 ---------------------------------------------
1923 -- Process the task/cdl rows for the project
1924 ---------------------------------------------
1925 IF lv_bypass_project = 'N' THEN
1926
1927 -- Initialize the task/cdl plsql tables
1928 lt_alloc_txn_id.DELETE;
1929 lt_task_id.DELETE;
1930 lt_task_num.DELETE;
1931 lt_task_owning_org_id.DELETE;
1932 lt_task_start_date.DELETE;
1933 lt_task_end_date.DELETE;
1934 lt_exp_org_id.DELETE;
1935 lt_rate_mult.DELETE;
1936 lt_grouping_method.DELETE;
1937 lt_cdl_status.DELETE;
1938 lt_prior_period_amt.DELETE;
1939 lt_curr_period_amt.DELETE;
1940 lt_target_task_id.DELETE;
1941 lt_cap_int_amt.DELETE;
1942 lt_attribute_category.DELETE;
1943 lt_attribute1.DELETE;
1944 lt_attribute2.DELETE;
1945 lt_attribute3.DELETE;
1946 lt_attribute4.DELETE;
1947 lt_attribute5.DELETE;
1948 lt_attribute6.DELETE;
1949 lt_attribute7.DELETE;
1950 lt_attribute8.DELETE;
1951 lt_attribute9.DELETE;
1952 lt_attribute10.DELETE;
1953 lt_process_task_flag.DELETE;
1954
1955
1956 IF g_debug_mode = 'Y' THEN
1957 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1958 ' Load the CDL cursor rows');
1959 END IF;
1960
1961
1962 -- Retrieve the task/cdl rows for the current project
1963 OPEN cur_cdls;
1964 FETCH cur_cdls BULK COLLECT INTO
1965 lt_task_id
1966 ,lt_task_num
1967 ,lt_task_owning_org_id
1968 ,lt_task_start_date
1969 ,lt_task_end_date
1970 ,lt_exp_org_id
1971 ,lt_rate_mult
1972 ,lt_grouping_method
1973 ,lt_cdl_status
1974 ,lt_prior_period_amt
1975 ,lt_curr_period_amt
1976 ,lt_process_task_flag
1977 --Bug fix:3051022 Added these columns to initialize collection tables
1978 ,lt_alloc_txn_id
1979 ,lt_target_task_id
1980 ,lt_cap_int_amt
1981 ,lt_attribute_category
1982 ,lt_attribute1
1983 ,lt_attribute2
1984 ,lt_attribute3
1985 ,lt_attribute4
1986 ,lt_attribute5
1987 ,lt_attribute6
1988 ,lt_attribute7
1989 ,lt_attribute8
1990 ,lt_attribute9
1991 ,lt_attribute10;
1992 CLOSE cur_cdls;
1993
1994 IF g_debug_mode = 'Y' THEN
1995 pa_debug.write_file('LOG', 'After Load the CDL cursor Number of rows['||
1996 lt_process_task_flag.count||']');
1997 END IF;
1998
1999
2000 -- If no rows are retrieved, set the project bypass variable and exception
2001 IF lt_task_id.COUNT = 0 THEN
2002
2003 lv_bypass_project := 'Y';
2004 lv_exception_code := c_proj_no_txns;
2005 lv_exception_type := 'W';
2006 pa_debug.g_err_stage := 'No CDLs for Project '||r_project.project_num;
2007
2008
2009 -- Otherwise, process the cdl rows for this project
2010 ELSE
2011
2012 -----------------------------------
2013 -- Perform project threshold checks
2014 -----------------------------------
2015 IF NVL(ln_proj_amt_threshold,0) > 0 AND
2016 lv_threshold_amt_type IN ('TOTAL_CIP','OPEN_CIP') THEN
2017
2018 -- Intialize the project totals
2019 ln_proj_tot_amt := 0;
2020 ln_proj_open_amt := 0;
2021
2022 -- Loop through every row and accumulate the amounts
2023 FOR i IN lt_task_id.FIRST..lt_task_id.LAST LOOP
2024 ln_proj_tot_amt := ln_proj_tot_amt
2025 + lt_prior_period_amt(i)
2026 + lt_curr_period_amt(i);
2027
2028 IF lt_cdl_status(i) = 'OPEN' THEN
2029 ln_proj_open_amt := ln_proj_open_amt
2030 + lt_prior_period_amt(i)
2031 + lt_curr_period_amt(i);
2032 END IF;
2033 END LOOP;
2034
2035
2036 -- Check the project threshold
2037 IF (lv_threshold_amt_type = 'TOTAL_CIP' AND
2038 ln_proj_tot_amt < ln_proj_amt_threshold)
2039 OR
2040 (lv_threshold_amt_type = 'OPEN_CIP' AND
2041 ln_proj_open_amt < ln_proj_amt_threshold) THEN
2042
2043 lv_bypass_project := 'Y';
2044 lv_exception_code := c_proj_threshold;
2045 lv_exception_type := 'W';
2046
2047 pa_debug.g_err_stage := 'Check CIP Thresholds for Project '||
2048 r_project.project_num;
2049 END IF;
2050 END IF;
2051
2052
2053 -- If the project threshold is passed, loop through all rows again
2054 -- to perform task checks and create transactions accordingly
2055 IF lv_bypass_project = 'N' THEN
2056
2057 -- Loop through every row and accumulate the amounts
2058 FOR i IN lt_task_id.FIRST..lt_task_id.LAST LOOP
2059
2060 IF g_debug_mode = 'Y' THEN
2061 pa_debug.write_file('LOG','Loop index['||i||']lv_first_exp_flag['||
2062 lv_first_exp_flag||']lt_task_id['||
2063 lt_task_id(i)||']lv_bypass_task['||lv_bypass_task||']ln_curr_task_id['||
2064 ln_curr_task_id||']ln_task_tot_amt['||ln_task_tot_amt||
2065 ']lv_threshold_amt_type['||lv_threshold_amt_type||']' );
2066 End If;
2067
2068
2069 -------------------------------
2070 -- Perform one-time task checks
2071 -------------------------------
2072 IF lv_first_exp_flag = 'Y' OR
2073 ln_curr_task_id <> lt_task_id(i) THEN
2074
2075 -- Check CIP thresholds on prior task if appropriate /*bug12969263*/
2076 IF (lv_first_exp_flag = 'N' AND lv_bypass_task = 'N')
2077 AND
2078 ((lv_threshold_amt_type = 'TOTAL_CIP' AND
2079 ln_task_tot_amt < NVL(ln_task_amt_threshold,0) AND NVL(ln_task_amt_threshold,0) <> 0 )
2080 OR
2081 (lv_threshold_amt_type = 'OPEN_CIP' AND
2082 ln_task_open_amt < NVL(ln_task_amt_threshold,0) AND NVL(ln_task_amt_threshold,0) <> 0 )) THEN
2083
2084 FOR j IN ln_task_start..ln_task_last LOOP
2085 lt_process_task_flag(j) := 'N';
2086
2087 ln_proj_trans_count := ln_proj_trans_count - 1;
2088 ln_rate_trans_amt := ln_rate_trans_amt
2089 - lt_prior_period_amt(j)
2090 - lt_curr_period_amt(j);
2091 END LOOP;
2092
2093 -- Write the exception for the task if appropriate
2094 write_exception
2095 (c_task_threshold
2096 ,ln_curr_task_id
2097 ,g_project_id
2098 ,g_cap_int_rule_id
2099 ,ln_run_id
2100 ,'W'
2101 ,x_return_status
2102 ,x_error_msg_count
2103 ,x_error_msg_code);
2104
2105 IF g_debug_mode = 'Y' THEN
2106 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2107 ||' Task '||lv_curr_task_num
2108 ||' bypassed by Actual Task Threshold');
2109 END IF;
2110
2111 IF x_return_status = 'U' THEN
2112 pa_debug.g_err_stage := 'Write_Exception for Task '
2113 ||lv_curr_task_num||' in Project '
2114 ||r_project.project_num||
2115 ']x-errmsg['||x_error_msg_code||']';
2116 RAISE process_error;
2117 END IF;
2118
2119 ln_proj_warning_count := ln_proj_warning_count + 1;
2120 END IF;
2121
2122
2123 IF g_debug_mode = 'Y' THEN
2124 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
2125 ' Task Number ['||lt_task_num(i)||']' ||
2126 ' ID ['||TO_CHAR(lt_task_id(i))||']');
2127 END IF;
2128
2129
2130 -- Initialize task variables
2131 lv_first_exp_flag := 'N';
2132 ln_task_tot_amt := 0;
2133 ln_task_open_amt := 0;
2134 ln_task_start := i;
2135 ln_curr_task_id := lt_task_id(i);
2136 lv_curr_task_num := lt_task_num(i);
2137 lv_bypass_task := 'N';
2138
2139 ln_task_duration_threshold := r_rate.task_duration_threshold;
2140 ln_task_amt_threshold := r_rate.task_amt_threshold;
2141
2142
2143 -- Check whether the task has met the duration and
2144 -- budget threshold values
2145 IF lv_bypass_task = 'N' AND x_return_status = 'S' THEN
2146 IF g_debug_mode = 'Y' THEN
2147 pa_debug.write_file('LOG','Check Task Thresholds');
2148 End If;
2149 check_thresholds
2150 (g_project_id
2151 ,ln_curr_task_id
2152 ,g_rate_name
2153 ,lt_task_start_date(i)
2154 ,g_period_end_date
2155 ,lv_threshold_amt_type
2156 ,r_rate.budget_type
2157 ,r_rate.fin_plan_type_id
2158 ,lv_interest_calc_method
2159 ,lv_cip_cost_type
2160 ,ln_task_duration_threshold
2161 ,ln_task_amt_threshold
2162 ,lv_bypass_task
2163 ,x_return_status
2164 ,x_error_msg_count
2165 ,x_error_msg_code);
2166
2167 pa_debug.g_err_stage := 'Check_Thresholds for Task '
2168 ||lv_curr_task_num||' in Project '||r_project.project_num;
2169 IF g_debug_mode = 'Y' THEN
2170 pa_debug.write_file('LOG','lv_bypass_task['
2171 ||lv_bypass_task||']x_return_status['
2172 ||x_return_status||
2173 ']x-errmsg['||x_error_msg_code||']');
2174 END IF;
2175
2176 IF x_return_status = 'U' THEN
2177 RAISE process_error;
2178 END IF;
2179
2180 IF lv_bypass_task = 'Y' THEN
2181 ln_except_task_id := ln_curr_task_id;
2182 lv_exception_code := c_task_threshold;
2183 lv_exception_type := 'W';
2184 END IF;
2185 END IF;
2186
2187
2188 -- Get the target task
2189 IF lv_bypass_task = 'N' AND x_return_status = 'S' THEN
2190 IF g_debug_mode = 'Y' THEN
2191 pa_debug.write_file('LOG','get target Task');
2192 End If;
2193 pa_client_extn_cap_int.get_target_task
2194 (ln_curr_task_id
2195 ,lv_curr_task_num
2196 ,g_rate_name
2197 ,ln_target_task_id
2198 ,lv_target_task_num
2199 ,x_return_status
2200 ,x_error_msg_count
2201 ,x_error_msg_code);
2202
2203 pa_debug.g_err_stage := 'Get_Target_Task for Task '
2204 ||lv_curr_task_num||' in Project '||r_project.project_num
2205 ||']x-errmsg['||x_error_msg_code||']';
2206
2207 IF x_return_status = 'U' THEN
2208 RAISE process_error;
2209 END IF;
2210
2211 IF ln_target_task_id IS NULL THEN
2212 ln_except_task_id := ln_curr_task_id;
2213 lv_exception_code := c_task_null_target;
2214 lv_exception_type := 'E';
2215 lv_bypass_task := 'Y';
2216 END IF;
2217 END IF;
2218
2219
2220 -- Revalidate the target task
2221 IF lv_bypass_task = 'N' AND x_return_status = 'S' THEN
2222 IF g_debug_mode = 'Y' THEN
2223 pa_debug.write_file('LOG','Validate Target Task');
2224 End If;
2225 validate_task
2226 (g_project_id
2227 ,ln_target_task_id
2228 ,g_exp_item_date
2229 ,g_period_end_date
2230 ,lv_bypass_task
2231 ,x_return_status
2232 ,x_error_msg_count
2233 ,x_error_msg_code);
2234
2235 pa_debug.g_err_stage := 'Validate_Target_Task for Task '
2236 ||lv_target_task_num||' in Project '||r_project.project_num;
2237
2238 IF g_debug_mode = 'Y' THEN
2239 pa_debug.write_file('LOG','lv_bypass_task['
2240 ||lv_bypass_task||']x_return_status['
2241 ||x_return_status||
2242 ']x-errmsg['||x_error_msg_code||']');
2243 END IF;
2244
2245 IF x_return_status = 'U' THEN
2246 RAISE process_error;
2247 END IF;
2248
2249 IF lv_bypass_task = 'Y' THEN
2250 ln_except_task_id := ln_target_task_id;
2251 lv_exception_code := c_task_not_valid;
2252 lv_exception_type := 'E';
2253 END IF;
2254 END IF;
2255
2256
2257 -- Get the rate multiplier if source specified is task owning org
2258 IF lv_bypass_task = 'N' AND x_return_status = 'S' THEN
2259 IF g_debug_mode = 'Y' THEN
2260 pa_debug.write_file('LOG','Calling get_rate_multiplier');
2261 End If;
2262 get_rate_multiplier
2263 (g_rate_name
2264 ,ln_sched_version_id
2265 ,lt_task_owning_org_id(i)
2266 ,ln_proj_owning_org_id
2267 ,ln_rate_mult
2268 ,x_return_status
2269 ,x_error_msg_count
2270 ,x_error_msg_code);
2271
2272 pa_debug.g_err_stage := 'Get_Rate_Multiplier for Rate '
2273 ||g_rate_name||' and Task '||lv_curr_task_num
2274 ||' in Project['||r_project.project_num
2275 ||']x-errmsg['||x_error_msg_code||']';
2276
2277 IF x_return_status = 'U' THEN
2278 RAISE process_error;
2279 END IF;
2280
2281 IF ln_rate_mult IS NULL THEN
2282 ln_except_task_id := ln_curr_task_id;
2283 lv_exception_code := c_rate_mult;
2284 lv_exception_type := 'E';
2285 lv_bypass_task := 'Y';
2286 END IF;
2287 END IF;
2288
2289
2290
2291 IF lv_bypass_task = 'Y' THEN
2292
2293 IF g_debug_mode = 'Y' THEN
2294 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
2295 ' Task bypassed by '||pa_debug.g_err_stage);
2296 END IF;
2297
2298 -- Write the exception for the task if appropriate
2299 write_exception
2300 (lv_exception_code
2301 ,ln_except_task_id
2302 ,g_project_id
2303 ,g_cap_int_rule_id
2304 ,ln_run_id
2305 ,lv_exception_type
2306 ,x_return_status
2307 ,x_error_msg_count
2308 ,x_error_msg_code);
2309
2310 IF x_return_status = 'U' THEN
2311 pa_debug.g_err_stage := 'Write_Exception for Task '
2312 ||lv_curr_task_num||' in Project['
2313 ||r_project.project_num
2314 ||']x-errmsg['||x_error_msg_code||']';
2315 RAISE process_error;
2316 END IF;
2317
2318 IF lv_exception_type = 'E' THEN
2319 ln_proj_error_count := ln_proj_error_count + 1;
2320 ELSE
2321 ln_proj_warning_count := ln_proj_warning_count + 1;
2322 END IF;
2323 END IF;
2324
2325 END IF;
2326
2327
2328 ---------------------------------
2329 -- Perform transaction processing
2330 ---------------------------------
2331
2332 -- Update the accumulators for task-level threshold testing
2333 ln_task_tot_amt := ln_task_tot_amt
2334 + lt_prior_period_amt(i)
2335 + lt_curr_period_amt(i);
2336
2337 IF lt_cdl_status(i) = 'OPEN' THEN
2338 ln_task_open_amt := ln_task_open_amt
2339 + lt_prior_period_amt(i)
2340 + lt_curr_period_amt(i);
2341 END IF;
2342
2343
2344
2345 -- If the task should be bypassed, mark all of the cdl rows accordingly
2346 IF lv_bypass_task = 'Y' THEN
2347 lt_process_task_flag(i) := 'N';
2348
2349
2350 -- If the CIP costs are closed, mark the row to be excluded from interest
2351 ELSIF lt_cdl_status(i) = 'CLOSED' THEN
2352 lt_process_task_flag(i) := 'N';
2353
2354
2355 -- Otherwise, continue checking the transaction
2356 ELSE
2357 -- Set the rate from the proj/task if not set by the client extension
2358 IF lt_rate_mult(i) IS NULL THEN
2359 lt_rate_mult(i) := ln_rate_mult;
2360 END IF;
2361
2362
2363 -- If the rate is not zero, continue the calculation
2364 IF lt_rate_mult(i) = 0 THEN
2365 lt_process_task_flag(i) := 'N';
2366
2367 ELSE
2368 -- Calculate cap interest using the standard algorithm
2369 ln_cap_int_amt :=
2370 ROUND((lt_rate_mult(i) * ln_period_mult)
2371 * (lt_prior_period_amt(i)
2372 + (lt_curr_period_amt(i) * ln_curr_period_mult))
2373 ,2);
2374
2375 -- Perform a custom calculation if desired
2376 IF g_debug_mode = 'Y' THEN
2377 pa_debug.write_file('LOG','Calling Client Extn calculate_capInt');
2378 End if;
2379 pa_client_extn_cap_int.calculate_cap_interest
2380 (g_gl_period
2381 ,g_rate_name
2382 ,ln_curr_period_mult
2383 ,ln_period_mult
2384 ,g_project_id
2385 ,ln_curr_task_id
2386 ,ln_target_task_id
2387 ,lt_exp_org_id(i)
2388 ,g_exp_item_date
2389 ,lt_prior_period_amt(i)
2390 ,lt_curr_period_amt(i)
2391 ,lt_grouping_method(i)
2392 ,lt_rate_mult(i)
2393 ,ln_cap_int_amt
2394 ,x_return_status
2395 ,x_error_msg_count
2396 ,x_error_msg_code);
2397
2398 -- Check the results of the custom calculation
2399 IF x_return_status = 'U' THEN
2400 pa_debug.g_err_stage := 'Calculate_Cap_Interest client extension x-errmsg['||x_error_msg_code||']';
2401 RAISE process_error;
2402 END IF;
2403
2404
2405 -- Save the results of the calculation
2406 IF NVL(ln_cap_int_amt,0) = 0 THEN
2407 lt_process_task_flag(i) := 'N';
2408
2409 ELSE
2410
2411 -- Call the client extension to retrieve attribute values
2412 pa_client_extn_cap_int.get_txn_attributes
2413 (g_project_id
2414 ,ln_curr_task_id
2415 ,ln_target_task_id
2416 ,g_rate_name
2417 ,lt_grouping_method(i)
2418 ,lt_attribute_category(i)
2419 ,lt_attribute1(i)
2420 ,lt_attribute2(i)
2421 ,lt_attribute3(i)
2422 ,lt_attribute4(i)
2423 ,lt_attribute5(i)
2424 ,lt_attribute6(i)
2425 ,lt_attribute7(i)
2426 ,lt_attribute8(i)
2427 ,lt_attribute9(i)
2428 ,lt_attribute10(i)
2429 ,x_return_status
2430 ,x_error_msg_count
2431 ,x_error_msg_code);
2432
2433
2434 -- Check the results of the attribute retrieval
2435 IF x_return_status = 'U' THEN
2436 pa_debug.g_err_stage :=
2437 'Get_Txn_Attributes client extension'||
2438 ']x-errmsg['||x_error_msg_code||']';
2439 RAISE process_error;
2440 END IF;
2441
2442
2443 -- Store the cap interest information
2444 lt_cap_int_amt(i) := ln_cap_int_amt;
2445 lt_target_task_id(i) := ln_target_task_id;
2446
2447 BEGIN
2448 SELECT pa_alloc_txn_details_s.nextval
2449 INTO lt_alloc_txn_id(i)
2450 FROM DUAL;
2451 EXCEPTION
2452 WHEN OTHERS THEN
2453 x_return_status := 'U';
2454 x_error_msg_count := 1;
2455 x_error_msg_code := SQLERRM;
2456 pa_debug.g_err_stage := 'Get Alloc Txn ID'||
2457 ']x-errmsg['||x_error_msg_code||']';
2458 RAISE process_error;
2459 END;
2460
2461
2462 -- Update accumulators
2463 ln_rate_trans_amt := ln_rate_trans_amt + lt_cap_int_amt(i);
2464 ln_proj_trans_count := ln_proj_trans_count + 1;
2465 END IF;
2466 END IF;
2467 END IF;
2468
2469 ln_task_last := i;
2470 END LOOP;
2471
2472
2473 -----------------------------------------
2474 -- Perform threshold checks for last task
2475 -----------------------------------------
2476 -- Check CIP thresholds on prior task if appropriate /*bug12969263*/
2477 IF (lv_first_exp_flag = 'N' AND lv_bypass_task = 'N')
2478 AND
2479 ((lv_threshold_amt_type = 'TOTAL_CIP' AND
2480 ln_task_tot_amt < NVL(ln_task_amt_threshold,0) AND NVL(ln_task_amt_threshold,0) <> 0 )
2481 OR
2482 (lv_threshold_amt_type = 'OPEN_CIP' AND
2483 ln_task_open_amt < NVL(ln_task_amt_threshold,0) AND NVL(ln_task_amt_threshold,0) <> 0 )) THEN
2484
2485 FOR j IN ln_task_start..ln_task_last LOOP
2486 lt_process_task_flag(j) := 'N';
2487
2488 ln_proj_trans_count := ln_proj_trans_count - 1;
2489 ln_rate_trans_amt := ln_rate_trans_amt
2490 - lt_prior_period_amt(j)
2491 - lt_curr_period_amt(j);
2492 END LOOP;
2493
2494 IF g_debug_mode = 'Y' THEN
2495 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2496 ||' Task '||lv_curr_task_num
2497 ||' bypassed by Actual Task Threshold');
2498 END IF;
2499
2500 -- Write the exception for the task if appropriate
2501 write_exception
2502 (c_task_threshold
2503 ,ln_curr_task_id
2504 ,g_project_id
2505 ,g_cap_int_rule_id
2506 ,ln_run_id
2507 ,'W'
2508 ,x_return_status
2509 ,x_error_msg_count
2510 ,x_error_msg_code);
2511
2512 IF x_return_status = 'U' THEN
2513 pa_debug.g_err_stage := 'Write_Exception for Task '
2514 ||lv_curr_task_num||' in Project '||r_project.project_num||
2515 ']x-errmsg['||x_error_msg_code||']';
2516 RAISE process_error;
2517 END IF;
2518
2519 ln_proj_warning_count := ln_proj_warning_count + 1;
2520 END IF;
2521
2522
2523 -- Reset trans to create to zero if an errors encountered in order
2524 -- to prevent any transactions from being created for the project
2525 IF ln_proj_error_count > 0 THEN
2526 ln_proj_trans_count := 0;
2527 END IF;
2528
2529
2530 ---------------------------------------------------------------------------
2531 -- Bulk load the appropriate interest transactions from the current project
2532 ---------------------------------------------------------------------------
2533 IF ln_proj_trans_count > 0 THEN
2534 IF g_debug_mode = 'Y' THEN
2535 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
2536 ' Create the Interest Transactions');
2537 END IF;
2538
2539 BEGIN
2540 IF g_debug_mode = 'Y' THEN
2541 pa_debug.write_file('LOG','Insert pa_alloc_txn_details');
2542 pa_debug.write_file('LOG','Task_count'||lt_task_id.count||
2543 ']alloctxnid_count ['||lt_alloc_txn_id.count||
2544 ']target_task_count['||lt_target_task_id.count||
2545 ']capintamtcount[' ||lt_cap_int_amt.count||
2546 ']lt taskid count[' ||lt_task_id.count||
2547 ']orgidcount[' ||lt_exp_org_id.count||
2548 ']rate mult count[' ||lt_rate_mult.count||
2549 ']period amt count[' || lt_curr_period_amt.count||
2550 ']prior amt count[' ||lt_prior_period_amt.count||
2551 ']categry count[' ||lt_attribute_category.count||
2552 ']attribute1[' ||lt_attribute1.count||
2553 ']attribute2[' ||lt_attribute2.count||
2554 ']attribute3[' ||lt_attribute3.count||
2555 ']attribute4[' ||lt_attribute4.count||
2556 ']attribute5[' ||lt_attribute5.count||
2557 ']attribute6[' ||lt_attribute6.count||
2558 ']attribute7[' ||lt_attribute7.count||
2559 ']attribute8[' ||lt_attribute8.count||
2560 ']attribute9[' ||lt_attribute9.count||
2561 ']attribute10[' ||lt_attribute10.count||
2562 ']proc flag count[' ||lt_process_task_flag.count||
2563 ']' );
2564 End If;
2565 FORALL k IN lt_task_id.FIRST..lt_task_id.LAST
2566 INSERT INTO pa_alloc_txn_details
2567 (alloc_txn_id
2568 ,run_id
2569 ,rule_id
2570 ,transaction_type
2571 ,fiscal_year
2572 ,quarter_num
2573 ,period_num
2574 ,run_period
2575 ,line_num
2576 ,creation_date
2577 ,created_by
2578 ,last_update_date
2579 ,last_updated_by
2580 ,last_update_login
2581 ,project_id
2582 ,task_id
2583 ,expenditure_type
2584 ,current_allocation
2585 ,status_code
2586 ,cint_source_task_id
2587 ,cint_exp_org_id
2588 ,cint_rate_multiplier
2589 ,cint_current_basis_amt
2590 ,cint_prior_basis_amt
2591 ,attribute_category
2592 ,attribute1
2593 ,attribute2
2594 ,attribute3
2595 ,attribute4
2596 ,attribute5
2597 ,attribute6
2598 ,attribute7
2599 ,attribute8
2600 ,attribute9
2601 ,attribute10
2602 ,ind_rate_sch_revision_id)
2603 SELECT
2604 lt_alloc_txn_id(k)
2605 ,ln_run_id
2606 ,g_cap_int_rule_id
2607 ,'T'
2608 ,ln_fiscal_year
2609 ,ln_quarter_num
2610 ,ln_period_num
2611 ,g_gl_period
2612 ,-1
2613 ,SYSDATE
2614 ,g_created_by
2615 ,SYSDATE
2616 ,g_last_updated_by
2617 ,g_last_update_login
2618 ,g_project_id
2619 ,lt_target_task_id(k)
2620 ,r_rate.exp_type
2621 ,lt_cap_int_amt(k)
2622 ,'P'
2623 ,lt_task_id(k)
2624 ,lt_exp_org_id(k)
2625 ,lt_rate_mult(k)
2626 /* Bug fix:3038119 */
2627 ,NVL(lt_curr_period_amt(k),0)* NVL(ln_curr_period_mult,0)
2628 ,lt_prior_period_amt(k)
2629 ,lt_attribute_category(k)
2630 ,lt_attribute1(k)
2631 ,lt_attribute2(k)
2632 ,lt_attribute3(k)
2633 ,lt_attribute4(k)
2634 ,lt_attribute5(k)
2635 ,lt_attribute6(k)
2636 ,lt_attribute7(k)
2637 ,lt_attribute8(k)
2638 ,lt_attribute9(k)
2639 ,lt_attribute10(k)
2640 ,ln_sched_version_id
2641 FROM DUAL
2642 WHERE lt_process_task_flag(k) = 'Y';
2643 EXCEPTION
2644 WHEN OTHERS THEN
2645 x_return_status := 'U';
2646 x_error_msg_count := 1;
2647 x_error_msg_code := SQLERRM;
2648 pa_debug.g_err_stage :=
2649 'Insert Interest Transactions for Project '
2650 ||r_project.project_num||']x-errMsg['||x_error_msg_code
2651 ||']' ;
2652 RAISE process_error;
2653 END;
2654
2655
2656 ------------------------------------------------------
2657 -- Bulk load the associated source detail if requested
2658 ------------------------------------------------------
2659 IF p_source_details = 'Y' THEN
2660 IF g_debug_mode = 'Y' THEN
2661 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
2662 ' Create the Source Details');
2663 END IF;
2664
2665 BEGIN
2666 -- Loop through every row and accumulate the amounts
2667 FOR k IN lt_task_id.FIRST..lt_task_id.LAST LOOP
2668
2669 IF lt_process_task_flag(k) = 'Y' THEN
2670
2671 INSERT INTO pa_cint_source_details
2672 (alloc_txn_id
2673 ,run_period_end_date
2674 ,project_id
2675 ,expenditure_item_id
2676 ,line_num
2677 ,prior_amount
2678 ,current_amount
2679 ,fiscal_year
2680 ,period_num
2681 ,creation_date
2682 ,created_by
2683 ,last_update_date
2684 ,last_updated_by
2685 ,last_update_login)
2686 SELECT
2687 lt_alloc_txn_id(k)
2688 ,g_period_end_date
2689 ,pctd.project_id
2690 ,pctd.expenditure_item_id
2691 ,pctd.line_num
2692 ,DECODE(SIGN(ld_period_start_date - pctd.gl_date)
2693 ,1, pctd.amount
2694 , 0)
2695 ,DECODE(SIGN(ld_period_start_date - pctd.gl_date)
2696 ,1, 0
2697 , pctd.amount)
2698 ,ln_fiscal_year
2699 ,ln_period_num
2700 ,SYSDATE
2701 ,g_created_by
2702 ,SYSDATE
2703 ,g_last_updated_by
2704 ,g_last_update_login
2705 FROM pa_cint_txn_details_v pctd
2706 WHERE pctd.target_exp_organization_id = lt_exp_org_id(k)
2707 AND NVL(pctd.rate_multiplier, lt_rate_mult(k)) =
2708 lt_rate_mult(k)
2709 AND NVL(pctd.cint_grouping_method,'@#$') =
2710 NVL(lt_grouping_method(k),'@#$')
2711 AND pctd.task_id = lt_task_id(k)
2712 AND pctd.cint_cdl_status = 'OPEN'
2713 AND pctd.gl_date <= TRUNC(g_period_end_date)
2714 AND TRUNC(g_period_end_date) <=
2715 TRUNC(NVL(pctd.task_cint_stop_date, g_period_end_date))
2716 AND TRUNC(g_exp_item_date) BETWEEN
2717 TRUNC(NVL(pctd.task_start_date, g_exp_item_date)) AND
2718 TRUNC(NVL(pctd.task_completion_date, g_exp_item_date))
2719 AND pctd.project_id = g_project_id
2720 AND pctd.cint_rate_name = g_rate_name;
2721
2722 ln_proj_detail_count := ln_proj_detail_count + SQL%ROWCOUNT;
2723 END IF;
2724 END LOOP;
2725 EXCEPTION
2726 WHEN OTHERS THEN
2727 x_return_status := 'U';
2728 x_error_msg_count := 1;
2729 x_error_msg_code := SQLERRM;
2730 pa_debug.g_err_stage :=
2731 'Insert Source Details for Project'||r_project.project_num||
2732 ']x-errMsg['||x_error_msg_code||']';
2733 RAISE process_error;
2734 END;
2735 END IF; -- if source details to be written
2736
2737 END IF; -- if trans to be written
2738
2739 END IF; -- bypass project because of thresholds
2740
2741 END IF; -- task/cdl rows found
2742
2743 END IF; -- bypass project for various reasons
2744
2745
2746 IF lv_bypass_project = 'Y' THEN
2747
2748 -- Write the exception for the project if appropriate
2749 write_exception
2750 (lv_exception_code
2751 ,NULL
2752 ,g_project_id
2753 ,g_cap_int_rule_id
2754 ,ln_run_id
2755 ,lv_exception_type
2756 ,x_return_status
2757 ,x_error_msg_count
2758 ,x_error_msg_code);
2759
2760 IF g_debug_mode = 'Y' THEN
2761 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2762 ||' Project bypassed by '||pa_debug.g_err_stage);
2763 END IF;
2764
2765 IF x_return_status = 'U' THEN
2766 pa_debug.g_err_stage := 'Write_Exception for Project '||r_project.project_num
2767 ||']x-errMsg['||x_error_msg_code||']';
2768 RAISE process_error;
2769 END IF;
2770
2771 IF lv_exception_type = 'E' THEN
2772 ln_proj_error_count := ln_proj_error_count + 1;
2773 ELSE
2774 ln_proj_warning_count := ln_proj_warning_count + 1;
2775 END IF;
2776 END IF;
2777
2778
2779 ln_proj_written := ln_proj_written + 1;
2780 ln_trans_written := ln_trans_written + ln_proj_trans_count;
2781 ln_error_written := ln_error_written + ln_proj_error_count;
2782 ln_warning_written := ln_warning_written + ln_proj_warning_count;
2783
2784
2785 IF g_debug_mode = 'Y' THEN
2786 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2787 ||' Num of Trans '||TO_CHAR(ln_proj_trans_count));
2788 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2789 ||' Num of Details is '||TO_CHAR(ln_proj_detail_count));
2790 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2791 ||' Num of Errors '||TO_CHAR(ln_proj_error_count));
2792 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2793 ||' Num of Warnings '||TO_CHAR(ln_proj_warning_count));
2794 END IF;
2795
2796
2797 -- Release the project lock
2798 IF pa_debug.release_user_lock('PA_CAP_INT_'||to_char(r_project.project_id)) < 0 THEN
2799 pa_debug.g_err_stage := 'Lock Release for Project '||r_project.project_num;
2800 x_return_status := 'U';
2801 x_error_msg_count := 1;
2802 x_error_msg_code := NVL(fnd_message.get_string('PA', 'PA_CAP_CANNOT_RELS_LOCK'),
2803 'PA_CAP_CANNOT_RELS_LOCK');
2804 RAISE process_error;
2805 END IF;
2806
2807
2808 -- Commit all transactions for the project
2809 COMMIT;
2810
2811 END IF; -- Interest Sch ID /* Added for Bug 6757697 */
2812
2813 END LOOP; -- project
2814
2815
2816 -- If no trans or exceptions written, remove the run for the rate
2817 IF ln_trans_written = 0 AND
2818 ln_error_written = 0 AND
2819 ln_warning_written = 0 THEN
2820 remove_run
2821 (ln_run_id
2822 ,'INPROCESS'
2823 ,x_return_status
2824 ,x_error_msg_count
2825 ,x_error_msg_code);
2826
2827 IF x_return_status <> 'S' THEN
2828 pa_debug.g_err_stage := 'Remove_Run for Rate '||g_rate_name||
2829 ']x-errMsg['||x_error_msg_code||']';
2830 RAISE process_error;
2831 END IF;
2832
2833 COMMIT;
2834
2835 -- Otherwise, complete processing the run
2836 ELSE
2837 IF ln_error_written > 0 OR ln_warning_written > 0 THEN
2838 lv_rate_status := 'DF';
2839 ELSE
2840 lv_rate_status := 'DS';
2841 END IF;
2842
2843 BEGIN
2844 -- Update the total transaction amount for the run
2845 UPDATE pa_alloc_runs_all run
2846 SET run.allocated_amount = -- Bug fix:2959030 ln_rate_trans_amt
2847 (select sum(nvl(txn.current_allocation,0))
2848 from pa_alloc_txn_details txn
2849 where txn.run_id = run.run_id
2850 )
2851 ,run.run_status = lv_rate_status
2852 WHERE run.run_id = ln_run_id;
2853
2854 COMMIT;
2855 EXCEPTION
2856 WHEN OTHERS THEN
2857 x_return_status := 'U';
2858 x_error_msg_count := 1;
2859 x_error_msg_code := SQLERRM;
2860 pa_debug.g_err_stage := 'Updating Total Trans Amt for Rate '
2861 ||g_rate_name||']x-errMsg['||x_error_msg_code||']';
2862 RAISE process_error;
2863 END;
2864
2865 -- Auto-release if specified
2866 IF p_autorelease = 'Y' THEN
2867 /* Bug fix:3005559 : The release process should not be called if there
2868 * no successful transactions exists in pa_alloc_txn_details table
2869 */
2870 IF release_capint_txns_exists(ln_run_id) = 'Y' THEN
2871 IF g_debug_mode = 'Y' THEN
2872 pa_debug.write_file('LOG','Calling pa_alloc_run.release_capint_txns API');
2873 End If;
2874
2875 pa_alloc_run.release_capint_txns
2876 (ln_run_id
2877 ,x_return_status
2878 ,x_error_msg_count
2879 ,x_error_msg_code);
2880
2881 IF x_return_status = 'U' THEN
2882 pa_debug.g_err_stage := 'Release_Alloc_Txns for Rate '||g_rate_name||
2883 ']x-errMsg['||x_error_msg_code||']';
2884 RAISE process_error;
2885 END IF;
2886 END IF; -- End of txn_exists
2887 END IF; --end of p_autorelease
2888 END IF; -- end of successful run
2889 END LOOP; -- rate
2890
2891 IF g_debug_mode = 'Y' THEN
2892 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||' End program');
2893 END IF;
2894
2895 -- Execution section if called from the form button in 'Release' mode
2896 ELSE
2897
2898 /* Bug fix:3005559 : The release process should not be called if there
2899 * no successful transactions exists in pa_alloc_txn_details table
2900 */
2901 IF release_capint_txns_exists(x_run_id) = 'Y' THEN
2902 IF g_debug_mode = 'Y' THEN
2903 pa_debug.write_file('LOG','Calling pa_alloc_run.release_capint_txns API');
2904 End If;
2905
2906 pa_alloc_run.release_capint_txns
2907 (x_run_id
2908 ,x_return_status
2909 ,x_error_msg_count
2910 ,x_error_msg_code);
2911
2912 IF x_return_status = 'U' THEN
2913 pa_debug.g_err_stage := 'Release_Alloc_Txns for Run ID '||TO_CHAR(x_run_id)||
2914 ']x-errMsg['||x_error_msg_code||']';
2915 RAISE process_error;
2916 END IF;
2917 END IF ; -- end of txns_exists
2918 END IF;
2919
2920 pa_debug.reset_err_stack;
2921 EXCEPTION
2922 WHEN process_error THEN
2923 pa_debug.write_file('LOG',substr('EXCEPTION:'||pa_debug.g_err_stage||'X-errMsg['||
2924 x_error_msg_code||']X-retStats['||x_return_status||']'||sqlcode||sqlerrm,1,500));
2925 ROLLBACK; -- Added here as the releasing dbms lock always causes commit
2926 IF p_mode = 'G' and ln_run_id is NOT NULL Then
2927 remove_run
2928 (ln_run_id
2929 ,'EXCEPTION'
2930 ,x_return_status
2931 ,x_error_msg_count
2932 ,x_error_msg_code);
2933 End If;
2934 IF p_mode = 'G' and g_project_id is not NULL then
2935 v_success_flag :=pa_debug.release_user_lock('PA_CAP_INT_'||to_char(g_project_id));
2936 End If;
2937
2938 pa_debug.reset_err_stack;
2939 RAISE;
2940 -- R12 NOCOPY mandate - adding when others for param x_run_id
2941 WHEN OTHERS THEN
2942 pa_debug.write_file('LOG',substr('EXCEPTION:' || sqlcode
2943 ||sqlerrm,1,500));
2944 ROLLBACK; -- Added here as the releasing dbms lock always causes commit
2945 x_return_status := 'U';
2946 x_error_msg_count := x_error_msg_count + 1;
2947 x_error_msg_code := sqlerrm;
2948 -- Copy back the value that was passed in.
2949 x_run_id := l_init_run_id;
2950 END;
2951
2952
2953
2954 /* ----------------------------------------------------
2955 Purge Source Details through the parameter GL period
2956 ---------------------------------------------------- */
2957 PROCEDURE purge_source_detail
2958 (p_gl_period IN VARCHAR2
2959 ,p_from_project_num IN VARCHAR2 DEFAULT NULL
2960 ,p_to_project_num IN VARCHAR2 DEFAULT NULL
2961 ,x_return_status OUT NOCOPY VARCHAR2
2962 ,x_error_msg_count OUT NOCOPY NUMBER
2963 ,x_error_msg_code OUT NOCOPY VARCHAR2)
2964 IS
2965
2966 -- Process control variables
2967
2968 c_max_del_rows NUMBER := 10000; -- maximum rows to delete per statement
2969 process_error EXCEPTION;
2970
2971
2972 -- Working storage variables
2973
2974 ld_period_start_date gl_period_statuses.start_date%TYPE;
2975 ld_period_end_date gl_period_statuses.end_date%TYPE;
2976 ln_period_num gl_period_statuses.period_num%TYPE;
2977 ln_fiscal_year gl_period_statuses.period_year%TYPE;
2978 ln_quarter_num gl_period_statuses.quarter_num%TYPE;
2979
2980 ln_rows_deleted NUMBER;
2981 ln_tot_rows_deleted NUMBER;
2982
2983 BEGIN
2984
2985 -- Initialize the out variables
2986 x_return_status := 'S';
2987 x_error_msg_count := 0;
2988 x_error_msg_code := NULL;
2989
2990
2991 -- Initialize the error stack
2992 pa_debug.init_err_stack ('PA_CAP_INT_PVT.GENERATE_CAP_INTEREST');
2993
2994 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
2995 g_debug_mode := NVL(g_debug_mode, 'N');
2996
2997 pa_debug.set_process
2998 (x_process => 'PLSQL'
2999 ,x_write_file => 'LOG'
3000 ,x_debug_mode => g_debug_mode);
3001
3002
3003 -- Clear the message stack
3004 fnd_msg_pub.initialize;
3005
3006
3007 -- Initialize variables
3008 ln_tot_rows_deleted := 0;
3009 g_created_by := NVL(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
3010 g_last_update_login := NVL(TO_NUMBER(fnd_profile.value('LOGIN_ID')), -1);
3011 g_last_updated_by := g_created_by;
3012 g_request_id := fnd_global.conc_request_id;
3013
3014
3015 IF g_debug_mode = 'Y' THEN
3016 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||' Start program');
3017 END IF;
3018
3019
3020 -- Get information for the parameter GL period
3021 get_period_dates
3022 (p_gl_period
3023 ,ld_period_start_date
3024 ,ld_period_end_date
3025 ,ln_fiscal_year
3026 ,ln_quarter_num
3027 ,ln_period_num
3028 ,x_return_status
3029 ,x_error_msg_count
3030 ,x_error_msg_code);
3031
3032 IF x_return_status = 'U' THEN
3033 pa_debug.g_err_stage := 'Get_Period_Dates for Period '||p_gl_period;
3034 RAISE process_error;
3035 END IF;
3036
3037
3038 -- Delete rows in specified increments until no rows remain to be deleted
3039 LOOP
3040 BEGIN
3041 DELETE FROM pa_cint_source_details pcsd
3042 WHERE pcsd.run_period_end_date <= ld_period_end_date
3043 AND EXISTS
3044 (SELECT pp.project_id
3045 FROM pa_projects pp
3046 WHERE pp.segment1 BETWEEN
3047 NVL(p_from_project_num, pp.segment1) AND
3048 NVL(p_to_project_num, pp.segment1)
3049 AND pp.project_id = pcsd.project_id)
3050 AND rownum <= c_max_del_rows;
3051
3052 ln_rows_deleted := SQL%ROWCOUNT;
3053
3054 COMMIT;
3055
3056 IF g_debug_mode = 'Y' THEN
3057 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
3058 ||' Rows deleted ['||TO_CHAR(ln_rows_deleted)||']');
3059 END IF;
3060 EXCEPTION
3061 WHEN NO_DATA_FOUND THEN
3062 ln_rows_deleted := 0;
3063 WHEN OTHERS THEN
3064 x_return_status := 'U';
3065 x_error_msg_count := 1;
3066 x_error_msg_code := SQLERRM;
3067 pa_debug.g_err_stage := 'Delete_Source_Detail x-errMsg['||x_error_msg_code||']';
3068 RAISE process_error;
3069 END;
3070
3071
3072 -- Determine if all rows have been deleted
3073 IF ln_rows_deleted = 0 THEN
3074 EXIT;
3075 ELSE
3076 ln_tot_rows_deleted := ln_tot_rows_deleted + ln_rows_deleted;
3077 END IF;
3078 END LOOP;
3079
3080
3081 IF g_debug_mode = 'Y' THEN
3082 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
3083 ||' Total Rows deleted ['||TO_CHAR(ln_tot_rows_deleted)||']');
3084 END IF;
3085
3086 pa_debug.reset_err_stack;
3087 EXCEPTION
3088 WHEN process_error THEN
3089 pa_debug.write_file('LOG',substr('EXCEPTION IN PURGE_SOURCE_DETAIL:'||x_error_msg_code,1,500));
3090 pa_debug.reset_err_stack;
3091 RAISE;
3092 END;
3093
3094
3095 END PA_CAP_INT_PVT;