[Home] [Help]
PACKAGE BODY: APPS.PA_CAP_INT_PVT
Source
1 PACKAGE BODY PA_CAP_INT_PVT AS
2 -- $Header: PACINTTB.pls 120.1.12010000.2 2008/08/22 16:08:17 mumohan 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 picc.ind_cost_code rate_name
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 AND pccm.ind_rate_sch_revision_id = pirsv.ind_rate_sch_revision_id
1430 AND pccm.ind_cost_code = picc.ind_cost_code
1431 AND pccm.ORGANIZATION_ID = pcri.org_id
1432 AND EXISTS
1433 (SELECT 'X'
1434 FROM pa_projects pp
1435 WHERE pp.cint_rate_sch_id = pirs.ind_rate_sch_id
1436 AND pp.template_flag = 'N'
1437 AND pp.segment1 BETWEEN
1438 NVL(p_from_project_num,pp.segment1) AND NVL(p_to_project_num,pp.segment1))
1439 ORDER BY picc.ind_cost_code;
1440 /* Added for Bug 6757697 End */
1441
1442 r_rate cur_rates%ROWTYPE;
1443
1444
1445 -- Project Cursor
1446
1447 CURSOR cur_projects IS
1448 SELECT pp.project_id project_id
1449 ,pp.segment1 project_num
1450 ,pp.carrying_out_organization_id owning_org_id
1451 ,pp.cint_rate_sch_id interest_sch_id
1452 ,pp.start_date start_date
1453 ,ppt.capital_cost_type_code cip_cost_type
1454 ,ppt.burden_amt_display_method burden_method
1455 ,ppt.total_burden_flag tot_burden_flag
1456 FROM pa_project_types ppt
1457 ,pa_projects pp
1458 WHERE TRUNC(g_period_end_date) <= TRUNC(NVL(pp.cint_stop_date,g_period_end_date))
1459 AND pp.cint_rate_sch_id IS NOT NULL
1460 AND NVL(pp.cint_eligible_flag,'Y') = 'Y'
1461 AND TRUNC(g_exp_item_date) BETWEEN
1462 TRUNC(NVL(pp.start_date,g_exp_item_date)) AND TRUNC(NVL(pp.completion_date,g_exp_item_date))
1463 AND pa_project_utils.Check_prj_stus_action_allowed
1464 (pp.project_status_code
1465 ,'CAPITALIZED_INTEREST') = 'Y'
1466 AND pa_project_utils.Check_prj_stus_action_allowed
1467 (pp.project_status_code
1468 ,'NEW_TXNS') = 'Y'
1469 AND pp.project_status_code <> 'CLOSED'
1470 AND ppt.project_type_class_code = 'CAPITAL'
1471 AND ppt.project_type = pp.project_type
1472 AND pp.template_flag = 'N'
1473 AND pp.segment1 BETWEEN
1474 NVL(p_from_project_num,pp.segment1) AND NVL(p_to_project_num,pp.segment1)
1475 ORDER BY pp.segment1;
1476
1477 r_project cur_projects%ROWTYPE;
1478
1479
1480 -- Task / Cost Distribution Line Cursor
1481
1482 CURSOR cur_cdls IS
1483 SELECT pctd.task_id task_id
1484 ,pctd.task_number task_num
1485 ,pctd.task_owning_org_id task_owning_org_id
1486 ,pctd.task_start_date task_start_date
1487 ,pctd.task_completion_date task_end_date
1488 ,pctd.target_exp_organization_id exp_org_id
1489 ,pctd.rate_multiplier rate_mult
1490 ,pctd.cint_grouping_method grouping_method
1491 ,pctd.cint_cdl_status cdl_status
1492 ,SUM(DECODE(SIGN(ld_period_start_date - pctd.gl_date)
1493 ,1, pctd.amount
1494 , 0)) prior_period_amt
1495 ,SUM(DECODE(SIGN(ld_period_start_date - pctd.gl_date)
1496 ,1, 0
1497 , pctd.amount)) curr_period_amt
1498 ,'Y' process_task_flag
1499 --Bug fix:3051022 Added these columns to initialize collection tables for each element
1500 ,NULL alloc_txn_id
1501 ,NULL target_task_id
1502 ,NULL cap_int_amt
1503 ,NULL attribute_category
1504 ,NULL attribute1
1505 ,NULL attribute2
1506 ,NULL attribute3
1507 ,NULL attribute4
1508 ,NULL attribute5
1509 ,NULL attribute6
1510 ,NULL attribute7
1511 ,NULL attribute8
1512 ,NULL attribute9
1513 ,NULL attribute10
1514 FROM pa_cint_txn_details_v pctd
1515 WHERE /* Commented out this condition for performance issues
1516 --(
1517 -- (NVL(lv_threshold_amt_type,'TOTAL_CIP') = 'TOTAL_CIP')
1518 -- OR
1519 -- (NVL(lv_threshold_amt_type,'TOTAL_CIP') <> 'TOTAL_CIP'
1520 -- AND pctd.cint_cdl_status = 'OPEN')
1521 --)
1522 --AND
1523 **/
1524 pctd.gl_date <= TRUNC(g_period_end_date)
1525 AND TRUNC(g_period_end_date) <= TRUNC(NVL(pctd.task_cint_stop_date, g_period_end_date))
1526 AND TRUNC(g_exp_item_date) BETWEEN
1527 TRUNC(NVL(pctd.task_start_date, g_exp_item_date)) AND
1528 TRUNC(NVL(pctd.task_completion_date, g_exp_item_date))
1529 AND pctd.project_id = g_project_id
1530 AND pctd.cint_rate_name = g_rate_name
1531 AND pctd.period_name = g_gl_period
1532 GROUP BY pctd.task_id
1533 ,pctd.task_number
1534 ,pctd.task_owning_org_id
1535 ,pctd.task_start_date
1536 ,pctd.task_completion_date
1537 ,pctd.target_exp_organization_id
1538 ,pctd.rate_multiplier
1539 ,pctd.cint_grouping_method
1540 ,pctd.cint_cdl_status
1541 ,'Y'
1542 ORDER BY pctd.task_id
1543 ,pctd.target_exp_organization_id
1544 ,pctd.rate_multiplier
1545 ,pctd.cint_grouping_method;
1546
1547
1548 BEGIN
1549
1550 -- Initialize the out variables
1551 x_return_status := 'S';
1552 x_error_msg_count := 0;
1553 x_error_msg_code := NULL;
1554 l_init_run_id := x_run_id; -- store passed in value for when others.
1555
1556 -- Initialize the error stack
1557 pa_debug.init_err_stack ('PA_CAP_INT_PVT.GENERATE_CAP_INTEREST');
1558
1559 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
1560 g_debug_mode := NVL(g_debug_mode, 'N');
1561
1562 pa_debug.set_process
1563 (x_process => 'PLSQL'
1564 ,x_write_file => 'LOG'
1565 ,x_debug_mode => g_debug_mode);
1566
1567
1568 -- Clear the message stack
1569 fnd_msg_pub.initialize;
1570
1571 If g_debug_mode = 'Y' Then
1572 pa_debug.write_file('LOG',substr('INSIDE Generate Capint API IN PARAMS: p_from_project_num ['
1573 ||p_from_project_num||']p_to_project_num['||p_to_project_num||
1574 ']p_gl_period['||p_gl_period||']p_exp_item_date['||p_exp_item_date||
1575 ']p_source_details['||p_source_details||']p_autorelease['||p_autorelease||
1576 ']p_mode['||p_mode||']x_run_id['||x_run_id||']',1,250) );
1577 End If;
1578
1579
1580 -- Initialize process variable
1581 g_created_by := NVL(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
1582 g_last_update_login := NVL(TO_NUMBER(fnd_profile.value('LOGIN_ID')), -1);
1583 g_last_updated_by := g_created_by;
1584 g_request_id := fnd_global.conc_request_id;
1585
1586
1587 -- Execution section if this is run in 'Generate' mode
1588 IF p_mode = 'G' THEN
1589
1590 IF g_debug_mode = 'Y' THEN
1591 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||' Start program');
1592 END IF;
1593
1594
1595 -- Get the start and end dates for the parameter GL period
1596 get_period_dates
1597 (p_gl_period
1598 ,ld_period_start_date
1599 ,g_period_end_date
1600 ,ln_fiscal_year
1601 ,ln_quarter_num
1602 ,ln_period_num
1603 ,x_return_status
1604 ,x_error_msg_count
1605 ,x_error_msg_code);
1606
1607 IF x_return_status = 'U' THEN
1608 pa_debug.g_err_stage := 'Get_Period_Dates for Period '||p_gl_period||
1609 ']x-errmsg['||x_error_msg_code||']';
1610 RAISE process_error;
1611 END IF;
1612
1613
1614 -- Get currency code and ORG_ID
1615 pa_multi_currency.init;
1616 lv_currency_code := pa_multi_currency.g_accounting_currency_code;
1617 ln_org_id := pa_utils4.get_org_id;
1618
1619
1620 -- Set global variable for GL period
1621 g_gl_period := p_gl_period;
1622 g_period_start_date := ld_period_start_date;
1623
1624 -- Set global variable for Exp Item Date
1625 g_exp_item_date := NVL(p_exp_item_date,g_period_end_date);
1626
1627 IF g_debug_mode = 'Y' THEN
1628 pa_debug.write_file('LOG','Global Var:Currency['||lv_currency_code||']Org['||ln_org_id||
1629 ']g_gl_period['||g_gl_period||']g_period_start_date['||g_period_start_date||
1630 ']g_period_end_date['||g_period_end_date||']g_exp_item_date['||g_exp_item_date||
1631 ']');
1632 END IF;
1633
1634 --------------------------------------
1635 -- Loop through the Cap Interest rates
1636 --------------------------------------
1637 FOR r_rate IN cur_rates LOOP
1638
1639 IF g_debug_mode = 'Y' THEN
1640 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1641 ' Rate ['||r_rate.rate_name||']');
1642 END IF;
1643
1644 -- Initialize rate process variables
1645 ln_proj_processed := 0;
1646 ln_proj_written := 0;
1647 ln_trans_written := 0;
1648 ln_error_written := 0;
1649 ln_warning_written := 0;
1650 ln_rate_trans_amt := 0;
1651
1652 g_rate_name := r_rate.rate_name;
1653 lv_exp_org_source := r_rate.exp_org_source;
1654 lv_interest_calc_method := r_rate.interest_calc_method;
1655 lv_threshold_amt_type := r_rate.threshold_amt_type;
1656
1657 -- Determine the current period multiplier
1658 IF r_rate.curr_period_convention = 'FULL' THEN
1659 ln_curr_period_mult := 1;
1660 ELSIF r_rate.curr_period_convention = 'HALF' THEN
1661 ln_curr_period_mult := .5;
1662 ELSE
1663 ln_curr_period_mult := 0;
1664 END IF;
1665
1666 IF g_debug_mode = 'Y' THEN
1667 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1668 ' Current Period Multiplier['||TO_CHAR(ln_curr_period_mult)||']');
1669 END IF;
1670
1671
1672 -- Determine the monthly multiplier
1673 IF r_rate.period_rate_code = 'BY_NUMBER_OF_DAYS' THEN
1674 ln_period_mult := ((g_period_end_date + 1) - ld_period_start_date)/365;
1675 ELSE
1676 ln_period_mult := 1/num_of_periods(ln_fiscal_year);
1677 END IF;
1678
1679 IF g_debug_mode = 'Y' THEN
1680 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1681 ' Period Rate Multiplier['||SUBSTR(TO_CHAR(ln_period_mult),1,6)||']');
1682 END IF;
1683
1684
1685 -- Write the run row for this rate
1686 write_run
1687 (g_gl_period
1688 ,g_cap_int_rule_id
1689 ,r_rate.exp_type
1690 ,g_exp_item_date
1691 ,lv_currency_code
1692 ,ln_fiscal_year
1693 ,ln_quarter_num
1694 ,ln_period_num
1695 ,ln_org_id
1696 ,g_rate_name
1697 ,p_autorelease
1698 ,ln_run_id
1699 ,x_return_status
1700 ,x_error_msg_count
1701 ,x_error_msg_code);
1702
1703 IF x_return_status = 'U' THEN
1704 pa_debug.g_err_stage := 'Write_Run for Rate['||g_rate_name||
1705 'Error-msg['||x_error_msg_code;
1706 pa_debug.write_file('LOG',substr(pa_debug.g_err_stage,1,250));
1707 RAISE process_error;
1708 END IF;
1709
1710 COMMIT;
1711
1712 IF g_debug_mode = 'Y' THEN
1713 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1714 ' Run ID ['||TO_CHAR(ln_run_id)||']');
1715 END IF;
1716
1717
1718 ----------------------------
1719 -- Loop through the projects
1720 ----------------------------
1721 FOR r_project IN cur_projects LOOP
1722
1723 IF R_RATE.INTEREST_SCH_ID = R_PROJECT.INTEREST_SCH_ID THEN /* Added for Bug 6757697 */
1724
1725 IF g_debug_mode = 'Y' THEN
1726 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1727 ' Project Number ['||r_project.project_num||']' ||
1728 ' ID ['||TO_CHAR(r_project.project_id)||']');
1729 END IF;
1730
1731
1732 -- Acquire a lock on the project info
1733 IF pa_debug.acquire_user_lock('PA_CAP_INT_'||to_char(r_project.project_id))<>0 THEN
1734 IF g_debug_mode = 'Y' THEN
1735 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1736 ' Could not lock the project '||r_project.project_num);
1737 END IF;
1738
1739 lv_bypass_project := 'Y';
1740 pa_debug.g_err_stage := 'Lock for Project '||r_project.project_num;
1741 lv_exception_code := c_proj_lock;
1742 lv_exception_type := 'E';
1743 END IF;
1744
1745 -- Initialize project process variables
1746 g_project_id := r_project.project_id;
1747 ln_proj_owning_org_id := r_project.owning_org_id;
1748 lv_cip_cost_type := r_project.cip_cost_type;
1749 lv_burden_method := r_project.burden_method;
1750 lv_tot_burden_flag := r_project.tot_burden_flag;
1751
1752 ln_proj_duration_threshold := r_rate.proj_duration_threshold;
1753 ln_proj_amt_threshold := r_rate.proj_amt_threshold;
1754
1755 ln_proj_processed := ln_proj_processed + 1;
1756 lv_bypass_project := 'N';
1757 ln_proj_trans_count := 0;
1758 ln_proj_error_count := 0;
1759 ln_proj_warning_count := 0;
1760 ln_proj_detail_count := 0;
1761
1762 lv_first_exp_flag := 'Y';
1763 ln_curr_task_id := -99;
1764
1765
1766 -- Check whether the project exists in current batches
1767 IF lv_bypass_project = 'N' AND x_return_status = 'S' THEN
1768 IF g_debug_mode = 'Y' THEN
1769 pa_debug.write_file('LOG','Check Project batch exists for the period');
1770 END IF;
1771 check_project_batches
1772 (g_project_id
1773 ,g_cap_int_rule_id
1774 ,ln_fiscal_year
1775 ,ln_quarter_num
1776 ,ln_period_num
1777 ,g_rate_name
1778 ,lv_bypass_project
1779 ,x_return_status
1780 ,x_error_msg_count
1781 ,x_error_msg_code);
1782
1783 pa_debug.g_err_stage := 'Check_Project_Batches for Project '||
1784 r_project.project_num;
1785 IF g_debug_mode = 'Y' THEN
1786 pa_debug.write_file('LOG','lv_bypass_project['||lv_bypass_project||
1787 ']x_return_status['||x_return_status||
1788 ']x-errmsg['||x_error_msg_code||']' );
1789 END IF;
1790
1791 IF x_return_status = 'U' THEN
1792 RAISE process_error;
1793 END IF;
1794
1795 IF lv_bypass_project = 'Y' THEN
1796 lv_exception_code := c_proj_batches;
1797 lv_exception_type := 'W';
1798 END IF;
1799 END IF;
1800
1801
1802 -- Check whether the project has a compiled Cap Interest schedule for the exp item date
1803 IF lv_bypass_project = 'N' AND x_return_status = 'S' THEN
1804 IF g_debug_mode = 'Y' THEN
1805 pa_debug.write_file('LOG','Check Project Schedule exists for the period');
1806 END IF;
1807 check_project_schedule
1808 (r_project.interest_sch_id
1809 ,g_period_end_date
1810 ,ln_sched_version_id
1811 ,lv_bypass_project
1812 ,x_return_status
1813 ,x_error_msg_count
1814 ,x_error_msg_code);
1815
1816 pa_debug.g_err_stage := 'Check_Project_Schedule for Project '||
1817 r_project.project_num;
1818
1819 IF g_debug_mode = 'Y' THEN
1820 pa_debug.write_file('LOG','lv_bypass_project['||lv_bypass_project||
1821 ']x_return_status['||x_return_status||
1822 ']x-errmsg['||x_error_msg_code||']' );
1823 END IF;
1824
1825 IF x_return_status = 'U' THEN
1826 RAISE process_error;
1827 END IF;
1828
1829 IF lv_bypass_project = 'Y' THEN
1830 lv_exception_code := c_proj_sched;
1831 lv_exception_type := 'W';
1832 END IF;
1833 END IF;
1834
1835 /* Bug fix: 3227816 Starts here */
1836 --Check if the rate name is used for this schedule
1837 IF lv_bypass_project = 'N' AND x_return_status = 'S' THEN
1838 IF g_debug_mode = 'Y' THEN
1839 pa_debug.write_file('LOG','Check Schedule has Ratename ');
1840 END IF;
1841 check_schedule_has_ratename
1842 (p_sch_id => r_project.interest_sch_id
1843 ,p_sch_rev_date => g_period_end_date
1844 ,p_sch_rev_id => ln_sched_version_id
1845 ,p_rate_name => r_rate.rate_name
1846 ,x_bypass_project => lv_bypass_project
1847 ,x_return_status => x_return_status
1848 ,x_error_msg_count => x_error_msg_count
1849 ,x_error_msg_code => x_error_msg_code );
1850
1851 IF g_debug_mode = 'Y' THEN
1852 pa_debug.write_file('LOG','lv_bypass_project['||lv_bypass_project||
1853 ']x_return_status['||x_return_status||
1854 ']x-errmsg['||x_error_msg_code||']' );
1855 END IF;
1856
1857 IF x_return_status = 'U' THEN
1858 RAISE process_error;
1859 END IF;
1860
1861 IF lv_bypass_project = 'Y' THEN
1862 lv_exception_code := c_proj_sch_no_rate;
1863 lv_exception_type := 'W';
1864 END IF;
1865 END IF;
1866 /* Bug fix: 3227816 Ends here */
1867
1868
1869 -- Check whether the project has met the threshold values
1870 IF lv_bypass_project = 'N' AND x_return_status = 'S' Then
1871 IF g_debug_mode = 'Y' THEN
1872 pa_debug.write_file('LOG','Check Project Thresholds');
1873 END IF;
1874
1875 /* Bug fix:2972865 Set the Budget entry level code */
1876 g_bdgt_entry_level_code := Get_Bdgt_entry_level_code
1877 (p_project_id => g_project_id
1878 ,p_threshold_amt_type => lv_threshold_amt_type
1879 ,p_budget_type_code => r_rate.budget_type
1880 ,p_fin_plan_type_id =>r_rate.fin_plan_type_id
1881 );
1882
1883 check_thresholds
1884 (g_project_id
1885 ,NULL
1886 ,g_rate_name
1887 ,r_project.start_date
1888 ,g_period_end_date
1889 ,lv_threshold_amt_type
1890 ,r_rate.budget_type
1891 ,r_rate.fin_plan_type_id
1892 ,lv_interest_calc_method
1893 ,lv_cip_cost_type
1894 ,ln_proj_duration_threshold
1895 ,ln_proj_amt_threshold
1896 ,lv_bypass_project
1897 ,x_return_status
1898 ,x_error_msg_count
1899 ,x_error_msg_code);
1900
1901 pa_debug.g_err_stage := 'Check_Thresholds for Project '||r_project.project_num;
1902 IF g_debug_mode = 'Y' THEN
1903 pa_debug.write_file('LOG','lv_bypass_project['||lv_bypass_project||
1904 ']x_return_status['||x_return_status||
1905 ']x-errmsg['||x_error_msg_code||']');
1906 END IF;
1907 IF x_return_status = 'U' THEN
1908 RAISE process_error;
1909 END IF;
1910
1911 IF lv_bypass_project = 'Y' THEN
1912 lv_exception_code := c_proj_threshold;
1913 lv_exception_type := 'W';
1914 END IF;
1915 END IF;
1916
1917
1918
1919 ---------------------------------------------
1920 -- Process the task/cdl rows for the project
1921 ---------------------------------------------
1922 IF lv_bypass_project = 'N' THEN
1923
1924 -- Initialize the task/cdl plsql tables
1925 lt_alloc_txn_id.DELETE;
1926 lt_task_id.DELETE;
1927 lt_task_num.DELETE;
1928 lt_task_owning_org_id.DELETE;
1929 lt_task_start_date.DELETE;
1930 lt_task_end_date.DELETE;
1931 lt_exp_org_id.DELETE;
1932 lt_rate_mult.DELETE;
1933 lt_grouping_method.DELETE;
1934 lt_cdl_status.DELETE;
1935 lt_prior_period_amt.DELETE;
1936 lt_curr_period_amt.DELETE;
1937 lt_target_task_id.DELETE;
1938 lt_cap_int_amt.DELETE;
1939 lt_attribute_category.DELETE;
1940 lt_attribute1.DELETE;
1941 lt_attribute2.DELETE;
1942 lt_attribute3.DELETE;
1943 lt_attribute4.DELETE;
1944 lt_attribute5.DELETE;
1945 lt_attribute6.DELETE;
1946 lt_attribute7.DELETE;
1947 lt_attribute8.DELETE;
1948 lt_attribute9.DELETE;
1949 lt_attribute10.DELETE;
1950 lt_process_task_flag.DELETE;
1951
1952
1953 IF g_debug_mode = 'Y' THEN
1954 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
1955 ' Load the CDL cursor rows');
1956 END IF;
1957
1958
1959 -- Retrieve the task/cdl rows for the current project
1960 OPEN cur_cdls;
1961 FETCH cur_cdls BULK COLLECT INTO
1962 lt_task_id
1963 ,lt_task_num
1964 ,lt_task_owning_org_id
1965 ,lt_task_start_date
1966 ,lt_task_end_date
1967 ,lt_exp_org_id
1968 ,lt_rate_mult
1969 ,lt_grouping_method
1970 ,lt_cdl_status
1971 ,lt_prior_period_amt
1972 ,lt_curr_period_amt
1973 ,lt_process_task_flag
1974 --Bug fix:3051022 Added these columns to initialize collection tables
1975 ,lt_alloc_txn_id
1976 ,lt_target_task_id
1977 ,lt_cap_int_amt
1978 ,lt_attribute_category
1979 ,lt_attribute1
1980 ,lt_attribute2
1981 ,lt_attribute3
1982 ,lt_attribute4
1983 ,lt_attribute5
1984 ,lt_attribute6
1985 ,lt_attribute7
1986 ,lt_attribute8
1987 ,lt_attribute9
1988 ,lt_attribute10;
1989 CLOSE cur_cdls;
1990
1991 IF g_debug_mode = 'Y' THEN
1992 pa_debug.write_file('LOG', 'After Load the CDL cursor Number of rows['||
1993 lt_process_task_flag.count||']');
1994 END IF;
1995
1996
1997 -- If no rows are retrieved, set the project bypass variable and exception
1998 IF lt_task_id.COUNT = 0 THEN
1999
2000 lv_bypass_project := 'Y';
2001 lv_exception_code := c_proj_no_txns;
2002 lv_exception_type := 'W';
2003 pa_debug.g_err_stage := 'No CDLs for Project '||r_project.project_num;
2004
2005
2006 -- Otherwise, process the cdl rows for this project
2007 ELSE
2008
2009 -----------------------------------
2010 -- Perform project threshold checks
2011 -----------------------------------
2012 IF NVL(ln_proj_amt_threshold,0) > 0 AND
2013 lv_threshold_amt_type IN ('TOTAL_CIP','OPEN_CIP') THEN
2014
2015 -- Intialize the project totals
2016 ln_proj_tot_amt := 0;
2017 ln_proj_open_amt := 0;
2018
2019 -- Loop through every row and accumulate the amounts
2020 FOR i IN lt_task_id.FIRST..lt_task_id.LAST LOOP
2021 ln_proj_tot_amt := ln_proj_tot_amt
2022 + lt_prior_period_amt(i)
2023 + lt_curr_period_amt(i);
2024
2025 IF lt_cdl_status(i) = 'OPEN' THEN
2026 ln_proj_open_amt := ln_proj_open_amt
2027 + lt_prior_period_amt(i)
2028 + lt_curr_period_amt(i);
2029 END IF;
2030 END LOOP;
2031
2032
2033 -- Check the project threshold
2034 IF (lv_threshold_amt_type = 'TOTAL_CIP' AND
2035 ln_proj_tot_amt < ln_proj_amt_threshold)
2036 OR
2037 (lv_threshold_amt_type = 'OPEN_CIP' AND
2038 ln_proj_open_amt < ln_proj_amt_threshold) THEN
2039
2040 lv_bypass_project := 'Y';
2041 lv_exception_code := c_proj_threshold;
2042 lv_exception_type := 'W';
2043
2044 pa_debug.g_err_stage := 'Check CIP Thresholds for Project '||
2045 r_project.project_num;
2046 END IF;
2047 END IF;
2048
2049
2050 -- If the project threshold is passed, loop through all rows again
2051 -- to perform task checks and create transactions accordingly
2052 IF lv_bypass_project = 'N' THEN
2053
2054 -- Loop through every row and accumulate the amounts
2055 FOR i IN lt_task_id.FIRST..lt_task_id.LAST LOOP
2056
2057 IF g_debug_mode = 'Y' THEN
2058 pa_debug.write_file('LOG','Loop index['||i||']lv_first_exp_flag['||
2059 lv_first_exp_flag||']lt_task_id['||
2060 lt_task_id(i)||']lv_bypass_task['||lv_bypass_task||']ln_curr_task_id['||
2061 ln_curr_task_id||']ln_task_tot_amt['||ln_task_tot_amt||
2062 ']lv_threshold_amt_type['||lv_threshold_amt_type||']' );
2063 End If;
2064
2065
2066 -------------------------------
2067 -- Perform one-time task checks
2068 -------------------------------
2069 IF lv_first_exp_flag = 'Y' OR
2070 ln_curr_task_id <> lt_task_id(i) THEN
2071
2072 -- Check CIP thresholds on prior task if appropriate
2073 IF (lv_first_exp_flag = 'N' AND lv_bypass_task = 'N')
2074 AND
2075 ((lv_threshold_amt_type = 'TOTAL_CIP' AND
2076 ln_task_tot_amt < NVL(ln_task_amt_threshold,0))
2077 OR
2078 (lv_threshold_amt_type = 'OPEN_CIP' AND
2079 ln_task_open_amt < NVL(ln_task_amt_threshold,0))) THEN
2080
2081 FOR j IN ln_task_start..ln_task_last LOOP
2082 lt_process_task_flag(j) := 'N';
2083
2084 ln_proj_trans_count := ln_proj_trans_count - 1;
2085 ln_rate_trans_amt := ln_rate_trans_amt
2086 - lt_prior_period_amt(j)
2087 - lt_curr_period_amt(j);
2088 END LOOP;
2089
2090 -- Write the exception for the task if appropriate
2091 write_exception
2092 (c_task_threshold
2093 ,ln_curr_task_id
2094 ,g_project_id
2095 ,g_cap_int_rule_id
2096 ,ln_run_id
2097 ,'W'
2098 ,x_return_status
2099 ,x_error_msg_count
2100 ,x_error_msg_code);
2101
2102 IF g_debug_mode = 'Y' THEN
2103 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2104 ||' Task '||lv_curr_task_num
2105 ||' bypassed by Actual Task Threshold');
2106 END IF;
2107
2108 IF x_return_status = 'U' THEN
2109 pa_debug.g_err_stage := 'Write_Exception for Task '
2110 ||lv_curr_task_num||' in Project '
2111 ||r_project.project_num||
2112 ']x-errmsg['||x_error_msg_code||']';
2113 RAISE process_error;
2114 END IF;
2115
2116 ln_proj_warning_count := ln_proj_warning_count + 1;
2117 END IF;
2118
2119
2120 IF g_debug_mode = 'Y' THEN
2121 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
2122 ' Task Number ['||lt_task_num(i)||']' ||
2123 ' ID ['||TO_CHAR(lt_task_id(i))||']');
2124 END IF;
2125
2126
2127 -- Initialize task variables
2128 lv_first_exp_flag := 'N';
2129 ln_task_tot_amt := 0;
2130 ln_task_open_amt := 0;
2131 ln_task_start := i;
2132 ln_curr_task_id := lt_task_id(i);
2133 lv_curr_task_num := lt_task_num(i);
2134 lv_bypass_task := 'N';
2135
2136 ln_task_duration_threshold := r_rate.task_duration_threshold;
2137 ln_task_amt_threshold := r_rate.task_amt_threshold;
2138
2139
2140 -- Check whether the task has met the duration and
2141 -- budget threshold values
2142 IF lv_bypass_task = 'N' AND x_return_status = 'S' THEN
2143 IF g_debug_mode = 'Y' THEN
2144 pa_debug.write_file('LOG','Check Task Thresholds');
2145 End If;
2146 check_thresholds
2147 (g_project_id
2148 ,ln_curr_task_id
2149 ,g_rate_name
2150 ,lt_task_start_date(i)
2151 ,g_period_end_date
2152 ,lv_threshold_amt_type
2153 ,r_rate.budget_type
2154 ,r_rate.fin_plan_type_id
2155 ,lv_interest_calc_method
2156 ,lv_cip_cost_type
2157 ,ln_task_duration_threshold
2158 ,ln_task_amt_threshold
2159 ,lv_bypass_task
2160 ,x_return_status
2161 ,x_error_msg_count
2162 ,x_error_msg_code);
2163
2164 pa_debug.g_err_stage := 'Check_Thresholds for Task '
2165 ||lv_curr_task_num||' in Project '||r_project.project_num;
2166 IF g_debug_mode = 'Y' THEN
2167 pa_debug.write_file('LOG','lv_bypass_task['
2168 ||lv_bypass_task||']x_return_status['
2169 ||x_return_status||
2170 ']x-errmsg['||x_error_msg_code||']');
2171 END IF;
2172
2173 IF x_return_status = 'U' THEN
2174 RAISE process_error;
2175 END IF;
2176
2177 IF lv_bypass_task = 'Y' THEN
2178 ln_except_task_id := ln_curr_task_id;
2179 lv_exception_code := c_task_threshold;
2180 lv_exception_type := 'W';
2181 END IF;
2182 END IF;
2183
2184
2185 -- Get the target task
2186 IF lv_bypass_task = 'N' AND x_return_status = 'S' THEN
2187 IF g_debug_mode = 'Y' THEN
2188 pa_debug.write_file('LOG','get target Task');
2189 End If;
2190 pa_client_extn_cap_int.get_target_task
2191 (ln_curr_task_id
2192 ,lv_curr_task_num
2193 ,g_rate_name
2194 ,ln_target_task_id
2195 ,lv_target_task_num
2196 ,x_return_status
2197 ,x_error_msg_count
2198 ,x_error_msg_code);
2199
2200 pa_debug.g_err_stage := 'Get_Target_Task for Task '
2201 ||lv_curr_task_num||' in Project '||r_project.project_num
2202 ||']x-errmsg['||x_error_msg_code||']';
2203
2204 IF x_return_status = 'U' THEN
2205 RAISE process_error;
2206 END IF;
2207
2208 IF ln_target_task_id IS NULL THEN
2209 ln_except_task_id := ln_curr_task_id;
2210 lv_exception_code := c_task_null_target;
2211 lv_exception_type := 'E';
2212 lv_bypass_task := 'Y';
2213 END IF;
2214 END IF;
2215
2216
2217 -- Revalidate the target task
2218 IF lv_bypass_task = 'N' AND x_return_status = 'S' THEN
2219 IF g_debug_mode = 'Y' THEN
2220 pa_debug.write_file('LOG','Validate Target Task');
2221 End If;
2222 validate_task
2223 (g_project_id
2224 ,ln_target_task_id
2225 ,g_exp_item_date
2226 ,g_period_end_date
2227 ,lv_bypass_task
2228 ,x_return_status
2229 ,x_error_msg_count
2230 ,x_error_msg_code);
2231
2232 pa_debug.g_err_stage := 'Validate_Target_Task for Task '
2233 ||lv_target_task_num||' in Project '||r_project.project_num;
2234
2235 IF g_debug_mode = 'Y' THEN
2236 pa_debug.write_file('LOG','lv_bypass_task['
2237 ||lv_bypass_task||']x_return_status['
2238 ||x_return_status||
2239 ']x-errmsg['||x_error_msg_code||']');
2240 END IF;
2241
2242 IF x_return_status = 'U' THEN
2243 RAISE process_error;
2244 END IF;
2245
2246 IF lv_bypass_task = 'Y' THEN
2247 ln_except_task_id := ln_target_task_id;
2248 lv_exception_code := c_task_not_valid;
2249 lv_exception_type := 'E';
2250 END IF;
2251 END IF;
2252
2253
2254 -- Get the rate multiplier if source specified is task owning org
2255 IF lv_bypass_task = 'N' AND x_return_status = 'S' THEN
2256 IF g_debug_mode = 'Y' THEN
2257 pa_debug.write_file('LOG','Calling get_rate_multiplier');
2258 End If;
2259 get_rate_multiplier
2260 (g_rate_name
2261 ,ln_sched_version_id
2262 ,lt_task_owning_org_id(i)
2263 ,ln_proj_owning_org_id
2264 ,ln_rate_mult
2265 ,x_return_status
2266 ,x_error_msg_count
2267 ,x_error_msg_code);
2268
2269 pa_debug.g_err_stage := 'Get_Rate_Multiplier for Rate '
2270 ||g_rate_name||' and Task '||lv_curr_task_num
2271 ||' in Project['||r_project.project_num
2272 ||']x-errmsg['||x_error_msg_code||']';
2273
2274 IF x_return_status = 'U' THEN
2275 RAISE process_error;
2276 END IF;
2277
2278 IF ln_rate_mult IS NULL THEN
2279 ln_except_task_id := ln_curr_task_id;
2280 lv_exception_code := c_rate_mult;
2281 lv_exception_type := 'E';
2282 lv_bypass_task := 'Y';
2283 END IF;
2284 END IF;
2285
2286
2287
2288 IF lv_bypass_task = 'Y' THEN
2289
2290 IF g_debug_mode = 'Y' THEN
2291 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
2292 ' Task bypassed by '||pa_debug.g_err_stage);
2293 END IF;
2294
2295 -- Write the exception for the task if appropriate
2296 write_exception
2297 (lv_exception_code
2298 ,ln_except_task_id
2299 ,g_project_id
2300 ,g_cap_int_rule_id
2301 ,ln_run_id
2302 ,lv_exception_type
2303 ,x_return_status
2304 ,x_error_msg_count
2305 ,x_error_msg_code);
2306
2307 IF x_return_status = 'U' THEN
2308 pa_debug.g_err_stage := 'Write_Exception for Task '
2309 ||lv_curr_task_num||' in Project['
2310 ||r_project.project_num
2311 ||']x-errmsg['||x_error_msg_code||']';
2312 RAISE process_error;
2313 END IF;
2314
2315 IF lv_exception_type = 'E' THEN
2316 ln_proj_error_count := ln_proj_error_count + 1;
2317 ELSE
2318 ln_proj_warning_count := ln_proj_warning_count + 1;
2319 END IF;
2320 END IF;
2321
2322 END IF;
2323
2324
2325 ---------------------------------
2326 -- Perform transaction processing
2327 ---------------------------------
2328
2329 -- Update the accumulators for task-level threshold testing
2330 ln_task_tot_amt := ln_task_tot_amt
2331 + lt_prior_period_amt(i)
2332 + lt_curr_period_amt(i);
2333
2334 IF lt_cdl_status(i) = 'OPEN' THEN
2335 ln_task_open_amt := ln_task_open_amt
2336 + lt_prior_period_amt(i)
2337 + lt_curr_period_amt(i);
2338 END IF;
2339
2340
2341
2342 -- If the task should be bypassed, mark all of the cdl rows accordingly
2343 IF lv_bypass_task = 'Y' THEN
2344 lt_process_task_flag(i) := 'N';
2345
2346
2347 -- If the CIP costs are closed, mark the row to be excluded from interest
2348 ELSIF lt_cdl_status(i) = 'CLOSED' THEN
2349 lt_process_task_flag(i) := 'N';
2350
2351
2352 -- Otherwise, continue checking the transaction
2353 ELSE
2354 -- Set the rate from the proj/task if not set by the client extension
2355 IF lt_rate_mult(i) IS NULL THEN
2356 lt_rate_mult(i) := ln_rate_mult;
2357 END IF;
2358
2359
2360 -- If the rate is not zero, continue the calculation
2361 IF lt_rate_mult(i) = 0 THEN
2362 lt_process_task_flag(i) := 'N';
2363
2364 ELSE
2365 -- Calculate cap interest using the standard algorithm
2366 ln_cap_int_amt :=
2367 ROUND((lt_rate_mult(i) * ln_period_mult)
2368 * (lt_prior_period_amt(i)
2369 + (lt_curr_period_amt(i) * ln_curr_period_mult))
2370 ,2);
2371
2372 -- Perform a custom calculation if desired
2373 IF g_debug_mode = 'Y' THEN
2374 pa_debug.write_file('LOG','Calling Client Extn calculate_capInt');
2375 End if;
2376 pa_client_extn_cap_int.calculate_cap_interest
2377 (g_gl_period
2378 ,g_rate_name
2379 ,ln_curr_period_mult
2380 ,ln_period_mult
2381 ,g_project_id
2382 ,ln_curr_task_id
2383 ,ln_target_task_id
2384 ,lt_exp_org_id(i)
2385 ,g_exp_item_date
2386 ,lt_prior_period_amt(i)
2387 ,lt_curr_period_amt(i)
2388 ,lt_grouping_method(i)
2389 ,lt_rate_mult(i)
2390 ,ln_cap_int_amt
2391 ,x_return_status
2392 ,x_error_msg_count
2393 ,x_error_msg_code);
2394
2395 -- Check the results of the custom calculation
2396 IF x_return_status = 'U' THEN
2397 pa_debug.g_err_stage := 'Calculate_Cap_Interest client extension x-errmsg['||x_error_msg_code||']';
2398 RAISE process_error;
2399 END IF;
2400
2401
2402 -- Save the results of the calculation
2403 IF NVL(ln_cap_int_amt,0) = 0 THEN
2404 lt_process_task_flag(i) := 'N';
2405
2406 ELSE
2407
2408 -- Call the client extension to retrieve attribute values
2409 pa_client_extn_cap_int.get_txn_attributes
2410 (g_project_id
2411 ,ln_curr_task_id
2412 ,ln_target_task_id
2413 ,g_rate_name
2414 ,lt_grouping_method(i)
2415 ,lt_attribute_category(i)
2416 ,lt_attribute1(i)
2417 ,lt_attribute2(i)
2418 ,lt_attribute3(i)
2419 ,lt_attribute4(i)
2420 ,lt_attribute5(i)
2421 ,lt_attribute6(i)
2422 ,lt_attribute7(i)
2423 ,lt_attribute8(i)
2424 ,lt_attribute9(i)
2425 ,lt_attribute10(i)
2426 ,x_return_status
2427 ,x_error_msg_count
2428 ,x_error_msg_code);
2429
2430
2431 -- Check the results of the attribute retrieval
2432 IF x_return_status = 'U' THEN
2433 pa_debug.g_err_stage :=
2434 'Get_Txn_Attributes client extension'||
2435 ']x-errmsg['||x_error_msg_code||']';
2436 RAISE process_error;
2437 END IF;
2438
2439
2440 -- Store the cap interest information
2441 lt_cap_int_amt(i) := ln_cap_int_amt;
2442 lt_target_task_id(i) := ln_target_task_id;
2443
2444 BEGIN
2445 SELECT pa_alloc_txn_details_s.nextval
2446 INTO lt_alloc_txn_id(i)
2447 FROM DUAL;
2448 EXCEPTION
2449 WHEN OTHERS THEN
2450 x_return_status := 'U';
2451 x_error_msg_count := 1;
2452 x_error_msg_code := SQLERRM;
2453 pa_debug.g_err_stage := 'Get Alloc Txn ID'||
2454 ']x-errmsg['||x_error_msg_code||']';
2455 RAISE process_error;
2456 END;
2457
2458
2459 -- Update accumulators
2460 ln_rate_trans_amt := ln_rate_trans_amt + lt_cap_int_amt(i);
2461 ln_proj_trans_count := ln_proj_trans_count + 1;
2462 END IF;
2463 END IF;
2464 END IF;
2465
2466 ln_task_last := i;
2467 END LOOP;
2468
2469
2470 -----------------------------------------
2471 -- Perform threshold checks for last task
2472 -----------------------------------------
2473 -- Check CIP thresholds on prior task if appropriate
2474 IF (lv_first_exp_flag = 'N' AND lv_bypass_task = 'N')
2475 AND
2476 ((lv_threshold_amt_type = 'TOTAL_CIP' AND
2477 ln_task_tot_amt < NVL(ln_task_amt_threshold,0))
2478 OR
2479 (lv_threshold_amt_type = 'OPEN_CIP' AND
2480 ln_task_open_amt < NVL(ln_task_amt_threshold,0))) THEN
2481
2482 FOR j IN ln_task_start..ln_task_last LOOP
2483 lt_process_task_flag(j) := 'N';
2484
2485 ln_proj_trans_count := ln_proj_trans_count - 1;
2486 ln_rate_trans_amt := ln_rate_trans_amt
2487 - lt_prior_period_amt(j)
2488 - lt_curr_period_amt(j);
2489 END LOOP;
2490
2491 IF g_debug_mode = 'Y' THEN
2492 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2493 ||' Task '||lv_curr_task_num
2494 ||' bypassed by Actual Task Threshold');
2495 END IF;
2496
2497 -- Write the exception for the task if appropriate
2498 write_exception
2499 (c_task_threshold
2500 ,ln_curr_task_id
2501 ,g_project_id
2502 ,g_cap_int_rule_id
2503 ,ln_run_id
2504 ,'W'
2505 ,x_return_status
2506 ,x_error_msg_count
2507 ,x_error_msg_code);
2508
2509 IF x_return_status = 'U' THEN
2510 pa_debug.g_err_stage := 'Write_Exception for Task '
2511 ||lv_curr_task_num||' in Project '||r_project.project_num||
2512 ']x-errmsg['||x_error_msg_code||']';
2513 RAISE process_error;
2514 END IF;
2515
2516 ln_proj_warning_count := ln_proj_warning_count + 1;
2517 END IF;
2518
2519
2520 -- Reset trans to create to zero if an errors encountered in order
2521 -- to prevent any transactions from being created for the project
2522 IF ln_proj_error_count > 0 THEN
2523 ln_proj_trans_count := 0;
2524 END IF;
2525
2526
2527 ---------------------------------------------------------------------------
2528 -- Bulk load the appropriate interest transactions from the current project
2529 ---------------------------------------------------------------------------
2530 IF ln_proj_trans_count > 0 THEN
2531 IF g_debug_mode = 'Y' THEN
2532 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
2533 ' Create the Interest Transactions');
2534 END IF;
2535
2536 BEGIN
2537 IF g_debug_mode = 'Y' THEN
2538 pa_debug.write_file('LOG','Insert pa_alloc_txn_details');
2539 pa_debug.write_file('LOG','Task_count'||lt_task_id.count||
2540 ']alloctxnid_count ['||lt_alloc_txn_id.count||
2541 ']target_task_count['||lt_target_task_id.count||
2542 ']capintamtcount[' ||lt_cap_int_amt.count||
2543 ']lt taskid count[' ||lt_task_id.count||
2544 ']orgidcount[' ||lt_exp_org_id.count||
2545 ']rate mult count[' ||lt_rate_mult.count||
2546 ']period amt count[' || lt_curr_period_amt.count||
2547 ']prior amt count[' ||lt_prior_period_amt.count||
2548 ']categry count[' ||lt_attribute_category.count||
2549 ']attribute1[' ||lt_attribute1.count||
2550 ']attribute2[' ||lt_attribute2.count||
2551 ']attribute3[' ||lt_attribute3.count||
2552 ']attribute4[' ||lt_attribute4.count||
2553 ']attribute5[' ||lt_attribute5.count||
2554 ']attribute6[' ||lt_attribute6.count||
2555 ']attribute7[' ||lt_attribute7.count||
2556 ']attribute8[' ||lt_attribute8.count||
2557 ']attribute9[' ||lt_attribute9.count||
2558 ']attribute10[' ||lt_attribute10.count||
2559 ']proc flag count[' ||lt_process_task_flag.count||
2560 ']' );
2561 End If;
2562 FORALL k IN lt_task_id.FIRST..lt_task_id.LAST
2563 INSERT INTO pa_alloc_txn_details
2564 (alloc_txn_id
2565 ,run_id
2566 ,rule_id
2567 ,transaction_type
2568 ,fiscal_year
2569 ,quarter_num
2570 ,period_num
2571 ,run_period
2572 ,line_num
2573 ,creation_date
2574 ,created_by
2575 ,last_update_date
2576 ,last_updated_by
2577 ,last_update_login
2578 ,project_id
2579 ,task_id
2580 ,expenditure_type
2581 ,current_allocation
2582 ,status_code
2583 ,cint_source_task_id
2584 ,cint_exp_org_id
2585 ,cint_rate_multiplier
2586 ,cint_current_basis_amt
2587 ,cint_prior_basis_amt
2588 ,attribute_category
2589 ,attribute1
2590 ,attribute2
2591 ,attribute3
2592 ,attribute4
2593 ,attribute5
2594 ,attribute6
2595 ,attribute7
2596 ,attribute8
2597 ,attribute9
2598 ,attribute10
2599 ,ind_rate_sch_revision_id)
2600 SELECT
2601 lt_alloc_txn_id(k)
2602 ,ln_run_id
2603 ,g_cap_int_rule_id
2604 ,'T'
2605 ,ln_fiscal_year
2606 ,ln_quarter_num
2607 ,ln_period_num
2608 ,g_gl_period
2609 ,-1
2610 ,SYSDATE
2611 ,g_created_by
2612 ,SYSDATE
2613 ,g_last_updated_by
2614 ,g_last_update_login
2615 ,g_project_id
2616 ,lt_target_task_id(k)
2617 ,r_rate.exp_type
2618 ,lt_cap_int_amt(k)
2619 ,'P'
2620 ,lt_task_id(k)
2621 ,lt_exp_org_id(k)
2622 ,lt_rate_mult(k)
2623 /* Bug fix:3038119 */
2624 ,NVL(lt_curr_period_amt(k),0)* NVL(ln_curr_period_mult,0)
2625 ,lt_prior_period_amt(k)
2626 ,lt_attribute_category(k)
2627 ,lt_attribute1(k)
2628 ,lt_attribute2(k)
2629 ,lt_attribute3(k)
2630 ,lt_attribute4(k)
2631 ,lt_attribute5(k)
2632 ,lt_attribute6(k)
2633 ,lt_attribute7(k)
2634 ,lt_attribute8(k)
2635 ,lt_attribute9(k)
2636 ,lt_attribute10(k)
2637 ,ln_sched_version_id
2638 FROM DUAL
2639 WHERE lt_process_task_flag(k) = 'Y';
2640 EXCEPTION
2641 WHEN OTHERS THEN
2642 x_return_status := 'U';
2643 x_error_msg_count := 1;
2644 x_error_msg_code := SQLERRM;
2645 pa_debug.g_err_stage :=
2646 'Insert Interest Transactions for Project '
2647 ||r_project.project_num||']x-errMsg['||x_error_msg_code
2648 ||']' ;
2649 RAISE process_error;
2650 END;
2651
2652
2653 ------------------------------------------------------
2654 -- Bulk load the associated source detail if requested
2655 ------------------------------------------------------
2656 IF p_source_details = 'Y' THEN
2657 IF g_debug_mode = 'Y' THEN
2658 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||
2659 ' Create the Source Details');
2660 END IF;
2661
2662 BEGIN
2663 -- Loop through every row and accumulate the amounts
2664 FOR k IN lt_task_id.FIRST..lt_task_id.LAST LOOP
2665
2666 IF lt_process_task_flag(k) = 'Y' THEN
2667
2668 INSERT INTO pa_cint_source_details
2669 (alloc_txn_id
2670 ,run_period_end_date
2671 ,project_id
2672 ,expenditure_item_id
2673 ,line_num
2674 ,prior_amount
2675 ,current_amount
2676 ,fiscal_year
2677 ,period_num
2678 ,creation_date
2679 ,created_by
2680 ,last_update_date
2681 ,last_updated_by
2682 ,last_update_login)
2683 SELECT
2684 lt_alloc_txn_id(k)
2685 ,g_period_end_date
2686 ,pctd.project_id
2687 ,pctd.expenditure_item_id
2688 ,pctd.line_num
2689 ,DECODE(SIGN(ld_period_start_date - pctd.gl_date)
2690 ,1, pctd.amount
2691 , 0)
2692 ,DECODE(SIGN(ld_period_start_date - pctd.gl_date)
2693 ,1, 0
2694 , pctd.amount)
2695 ,ln_fiscal_year
2696 ,ln_period_num
2697 ,SYSDATE
2698 ,g_created_by
2699 ,SYSDATE
2700 ,g_last_updated_by
2701 ,g_last_update_login
2702 FROM pa_cint_txn_details_v pctd
2703 WHERE pctd.target_exp_organization_id = lt_exp_org_id(k)
2704 AND NVL(pctd.rate_multiplier, lt_rate_mult(k)) =
2705 lt_rate_mult(k)
2706 AND NVL(pctd.cint_grouping_method,'@#$') =
2707 NVL(lt_grouping_method(k),'@#$')
2708 AND pctd.task_id = lt_task_id(k)
2709 AND pctd.cint_cdl_status = 'OPEN'
2710 AND pctd.gl_date <= TRUNC(g_period_end_date)
2711 AND TRUNC(g_period_end_date) <=
2712 TRUNC(NVL(pctd.task_cint_stop_date, g_period_end_date))
2713 AND TRUNC(g_exp_item_date) BETWEEN
2714 TRUNC(NVL(pctd.task_start_date, g_exp_item_date)) AND
2715 TRUNC(NVL(pctd.task_completion_date, g_exp_item_date))
2716 AND pctd.project_id = g_project_id
2717 AND pctd.cint_rate_name = g_rate_name;
2718
2719 ln_proj_detail_count := ln_proj_detail_count + SQL%ROWCOUNT;
2720 END IF;
2721 END LOOP;
2722 EXCEPTION
2723 WHEN OTHERS THEN
2724 x_return_status := 'U';
2725 x_error_msg_count := 1;
2726 x_error_msg_code := SQLERRM;
2727 pa_debug.g_err_stage :=
2728 'Insert Source Details for Project'||r_project.project_num||
2729 ']x-errMsg['||x_error_msg_code||']';
2730 RAISE process_error;
2731 END;
2732 END IF; -- if source details to be written
2733
2734 END IF; -- if trans to be written
2735
2736 END IF; -- bypass project because of thresholds
2737
2738 END IF; -- task/cdl rows found
2739
2740 END IF; -- bypass project for various reasons
2741
2742
2743 IF lv_bypass_project = 'Y' THEN
2744
2745 -- Write the exception for the project if appropriate
2746 write_exception
2747 (lv_exception_code
2748 ,NULL
2749 ,g_project_id
2750 ,g_cap_int_rule_id
2751 ,ln_run_id
2752 ,lv_exception_type
2753 ,x_return_status
2754 ,x_error_msg_count
2755 ,x_error_msg_code);
2756
2757 IF g_debug_mode = 'Y' THEN
2758 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2759 ||' Project bypassed by '||pa_debug.g_err_stage);
2760 END IF;
2761
2762 IF x_return_status = 'U' THEN
2763 pa_debug.g_err_stage := 'Write_Exception for Project '||r_project.project_num
2764 ||']x-errMsg['||x_error_msg_code||']';
2765 RAISE process_error;
2766 END IF;
2767
2768 IF lv_exception_type = 'E' THEN
2769 ln_proj_error_count := ln_proj_error_count + 1;
2770 ELSE
2771 ln_proj_warning_count := ln_proj_warning_count + 1;
2772 END IF;
2773 END IF;
2774
2775
2776 ln_proj_written := ln_proj_written + 1;
2777 ln_trans_written := ln_trans_written + ln_proj_trans_count;
2778 ln_error_written := ln_error_written + ln_proj_error_count;
2779 ln_warning_written := ln_warning_written + ln_proj_warning_count;
2780
2781
2782 IF g_debug_mode = 'Y' THEN
2783 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2784 ||' Num of Trans '||TO_CHAR(ln_proj_trans_count));
2785 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2786 ||' Num of Details is '||TO_CHAR(ln_proj_detail_count));
2787 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2788 ||' Num of Errors '||TO_CHAR(ln_proj_error_count));
2789 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
2790 ||' Num of Warnings '||TO_CHAR(ln_proj_warning_count));
2791 END IF;
2792
2793
2794 -- Release the project lock
2795 IF pa_debug.release_user_lock('PA_CAP_INT_'||to_char(r_project.project_id)) < 0 THEN
2796 pa_debug.g_err_stage := 'Lock Release for Project '||r_project.project_num;
2797 x_return_status := 'U';
2798 x_error_msg_count := 1;
2799 x_error_msg_code := NVL(fnd_message.get_string('PA', 'PA_CAP_CANNOT_RELS_LOCK'),
2800 'PA_CAP_CANNOT_RELS_LOCK');
2801 RAISE process_error;
2802 END IF;
2803
2804
2805 -- Commit all transactions for the project
2806 COMMIT;
2807
2808 END IF; -- Interest Sch ID /* Added for Bug 6757697 */
2809
2810 END LOOP; -- project
2811
2812
2813 -- If no trans or exceptions written, remove the run for the rate
2814 IF ln_trans_written = 0 AND
2815 ln_error_written = 0 AND
2816 ln_warning_written = 0 THEN
2817 remove_run
2818 (ln_run_id
2819 ,'INPROCESS'
2820 ,x_return_status
2821 ,x_error_msg_count
2822 ,x_error_msg_code);
2823
2824 IF x_return_status <> 'S' THEN
2825 pa_debug.g_err_stage := 'Remove_Run for Rate '||g_rate_name||
2826 ']x-errMsg['||x_error_msg_code||']';
2827 RAISE process_error;
2828 END IF;
2829
2830 COMMIT;
2831
2832 -- Otherwise, complete processing the run
2833 ELSE
2834 IF ln_error_written > 0 OR ln_warning_written > 0 THEN
2835 lv_rate_status := 'DF';
2836 ELSE
2837 lv_rate_status := 'DS';
2838 END IF;
2839
2840 BEGIN
2841 -- Update the total transaction amount for the run
2842 UPDATE pa_alloc_runs_all run
2843 SET run.allocated_amount = -- Bug fix:2959030 ln_rate_trans_amt
2844 (select sum(nvl(txn.current_allocation,0))
2845 from pa_alloc_txn_details txn
2846 where txn.run_id = run.run_id
2847 )
2848 ,run.run_status = lv_rate_status
2849 WHERE run.run_id = ln_run_id;
2850
2851 COMMIT;
2852 EXCEPTION
2853 WHEN OTHERS THEN
2854 x_return_status := 'U';
2855 x_error_msg_count := 1;
2856 x_error_msg_code := SQLERRM;
2857 pa_debug.g_err_stage := 'Updating Total Trans Amt for Rate '
2858 ||g_rate_name||']x-errMsg['||x_error_msg_code||']';
2859 RAISE process_error;
2860 END;
2861
2862 -- Auto-release if specified
2863 IF p_autorelease = 'Y' THEN
2864 /* Bug fix:3005559 : The release process should not be called if there
2865 * no successful transactions exists in pa_alloc_txn_details table
2866 */
2867 IF release_capint_txns_exists(ln_run_id) = 'Y' THEN
2868 IF g_debug_mode = 'Y' THEN
2869 pa_debug.write_file('LOG','Calling pa_alloc_run.release_capint_txns API');
2870 End If;
2871
2872 pa_alloc_run.release_capint_txns
2873 (ln_run_id
2874 ,x_return_status
2875 ,x_error_msg_count
2876 ,x_error_msg_code);
2877
2878 IF x_return_status = 'U' THEN
2879 pa_debug.g_err_stage := 'Release_Alloc_Txns for Rate '||g_rate_name||
2880 ']x-errMsg['||x_error_msg_code||']';
2881 RAISE process_error;
2882 END IF;
2883 END IF; -- End of txn_exists
2884 END IF; --end of p_autorelease
2885 END IF; -- end of successful run
2886 END LOOP; -- rate
2887
2888 IF g_debug_mode = 'Y' THEN
2889 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||' End program');
2890 END IF;
2891
2892 -- Execution section if called from the form button in 'Release' mode
2893 ELSE
2894
2895 /* Bug fix:3005559 : The release process should not be called if there
2896 * no successful transactions exists in pa_alloc_txn_details table
2897 */
2898 IF release_capint_txns_exists(x_run_id) = 'Y' THEN
2899 IF g_debug_mode = 'Y' THEN
2900 pa_debug.write_file('LOG','Calling pa_alloc_run.release_capint_txns API');
2901 End If;
2902
2903 pa_alloc_run.release_capint_txns
2904 (x_run_id
2905 ,x_return_status
2906 ,x_error_msg_count
2907 ,x_error_msg_code);
2908
2909 IF x_return_status = 'U' THEN
2910 pa_debug.g_err_stage := 'Release_Alloc_Txns for Run ID '||TO_CHAR(x_run_id)||
2911 ']x-errMsg['||x_error_msg_code||']';
2912 RAISE process_error;
2913 END IF;
2914 END IF ; -- end of txns_exists
2915 END IF;
2916
2917 pa_debug.reset_err_stack;
2918 EXCEPTION
2919 WHEN process_error THEN
2920 pa_debug.write_file('LOG',substr('EXCEPTION:'||pa_debug.g_err_stage||'X-errMsg['||
2921 x_error_msg_code||']X-retStats['||x_return_status||']'||sqlcode||sqlerrm,1,500));
2922 ROLLBACK; -- Added here as the releasing dbms lock always causes commit
2923 IF p_mode = 'G' and ln_run_id is NOT NULL Then
2924 remove_run
2925 (ln_run_id
2926 ,'EXCEPTION'
2927 ,x_return_status
2928 ,x_error_msg_count
2929 ,x_error_msg_code);
2930 End If;
2931 IF p_mode = 'G' and g_project_id is not NULL then
2932 v_success_flag :=pa_debug.release_user_lock('PA_CAP_INT_'||to_char(g_project_id));
2933 End If;
2934
2935 pa_debug.reset_err_stack;
2936 RAISE;
2937 -- R12 NOCOPY mandate - adding when others for param x_run_id
2938 WHEN OTHERS THEN
2939 pa_debug.write_file('LOG',substr('EXCEPTION:' || sqlcode
2940 ||sqlerrm,1,500));
2941 ROLLBACK; -- Added here as the releasing dbms lock always causes commit
2942 x_return_status := 'U';
2943 x_error_msg_count := x_error_msg_count + 1;
2944 x_error_msg_code := sqlerrm;
2945 -- Copy back the value that was passed in.
2946 x_run_id := l_init_run_id;
2947 END;
2948
2949
2950
2951 /* ----------------------------------------------------
2952 Purge Source Details through the parameter GL period
2953 ---------------------------------------------------- */
2954 PROCEDURE purge_source_detail
2955 (p_gl_period IN VARCHAR2
2956 ,p_from_project_num IN VARCHAR2 DEFAULT NULL
2957 ,p_to_project_num IN VARCHAR2 DEFAULT NULL
2958 ,x_return_status OUT NOCOPY VARCHAR2
2959 ,x_error_msg_count OUT NOCOPY NUMBER
2960 ,x_error_msg_code OUT NOCOPY VARCHAR2)
2961 IS
2962
2963 -- Process control variables
2964
2965 c_max_del_rows NUMBER := 10000; -- maximum rows to delete per statement
2966 process_error EXCEPTION;
2967
2968
2969 -- Working storage variables
2970
2971 ld_period_start_date gl_period_statuses.start_date%TYPE;
2972 ld_period_end_date gl_period_statuses.end_date%TYPE;
2973 ln_period_num gl_period_statuses.period_num%TYPE;
2974 ln_fiscal_year gl_period_statuses.period_year%TYPE;
2975 ln_quarter_num gl_period_statuses.quarter_num%TYPE;
2976
2977 ln_rows_deleted NUMBER;
2978 ln_tot_rows_deleted NUMBER;
2979
2980 BEGIN
2981
2982 -- Initialize the out variables
2983 x_return_status := 'S';
2984 x_error_msg_count := 0;
2985 x_error_msg_code := NULL;
2986
2987
2988 -- Initialize the error stack
2989 pa_debug.init_err_stack ('PA_CAP_INT_PVT.GENERATE_CAP_INTEREST');
2990
2991 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
2992 g_debug_mode := NVL(g_debug_mode, 'N');
2993
2994 pa_debug.set_process
2995 (x_process => 'PLSQL'
2996 ,x_write_file => 'LOG'
2997 ,x_debug_mode => g_debug_mode);
2998
2999
3000 -- Clear the message stack
3001 fnd_msg_pub.initialize;
3002
3003
3004 -- Initialize variables
3005 ln_tot_rows_deleted := 0;
3006 g_created_by := NVL(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
3007 g_last_update_login := NVL(TO_NUMBER(fnd_profile.value('LOGIN_ID')), -1);
3008 g_last_updated_by := g_created_by;
3009 g_request_id := fnd_global.conc_request_id;
3010
3011
3012 IF g_debug_mode = 'Y' THEN
3013 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:') ||' Start program');
3014 END IF;
3015
3016
3017 -- Get information for the parameter GL period
3018 get_period_dates
3019 (p_gl_period
3020 ,ld_period_start_date
3021 ,ld_period_end_date
3022 ,ln_fiscal_year
3023 ,ln_quarter_num
3024 ,ln_period_num
3025 ,x_return_status
3026 ,x_error_msg_count
3027 ,x_error_msg_code);
3028
3029 IF x_return_status = 'U' THEN
3030 pa_debug.g_err_stage := 'Get_Period_Dates for Period '||p_gl_period;
3031 RAISE process_error;
3032 END IF;
3033
3034
3035 -- Delete rows in specified increments until no rows remain to be deleted
3036 LOOP
3037 BEGIN
3038 DELETE FROM pa_cint_source_details pcsd
3039 WHERE pcsd.run_period_end_date <= ld_period_end_date
3040 AND EXISTS
3041 (SELECT pp.project_id
3042 FROM pa_projects pp
3043 WHERE pp.segment1 BETWEEN
3044 NVL(p_from_project_num, pp.segment1) AND
3045 NVL(p_to_project_num, pp.segment1)
3046 AND pp.project_id = pcsd.project_id)
3047 AND rownum <= c_max_del_rows;
3048
3049 ln_rows_deleted := SQL%ROWCOUNT;
3050
3051 COMMIT;
3052
3053 IF g_debug_mode = 'Y' THEN
3054 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
3055 ||' Rows deleted ['||TO_CHAR(ln_rows_deleted)||']');
3056 END IF;
3057 EXCEPTION
3058 WHEN NO_DATA_FOUND THEN
3059 ln_rows_deleted := 0;
3060 WHEN OTHERS THEN
3061 x_return_status := 'U';
3062 x_error_msg_count := 1;
3063 x_error_msg_code := SQLERRM;
3064 pa_debug.g_err_stage := 'Delete_Source_Detail x-errMsg['||x_error_msg_code||']';
3065 RAISE process_error;
3066 END;
3067
3068
3069 -- Determine if all rows have been deleted
3070 IF ln_rows_deleted = 0 THEN
3071 EXIT;
3072 ELSE
3073 ln_tot_rows_deleted := ln_tot_rows_deleted + ln_rows_deleted;
3074 END IF;
3075 END LOOP;
3076
3077
3078 IF g_debug_mode = 'Y' THEN
3079 pa_debug.write_file('LOG', TO_CHAR(SYSDATE,'HH24:MI:SS:')
3080 ||' Total Rows deleted ['||TO_CHAR(ln_tot_rows_deleted)||']');
3081 END IF;
3082
3083 pa_debug.reset_err_stack;
3084 EXCEPTION
3085 WHEN process_error THEN
3086 pa_debug.write_file('LOG',substr('EXCEPTION IN PURGE_SOURCE_DETAIL:'||x_error_msg_code,1,500));
3087 pa_debug.reset_err_stack;
3088 RAISE;
3089 END;
3090
3091
3092 END PA_CAP_INT_PVT;