[Home] [Help]
1: PACKAGE BODY PA_ALLOC_RUN AS
2: /* $Header: PAXALRNB.pls 120.12.12020000.3 2013/03/06 09:51:42 admarath ship $ */
3: P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4: G_creation_date DATE ;
5: G_last_update_date DATE ;
6: G_created_by NUMBER ;
7: G_last_updated_by NUMBER ;
145: v_dummy varchar2(1) := NULL ;
146: v_pazspa varchar2(1) := NULL ;
147: completion_status boolean; -- Added for 2841843
148: BEGIN
149: pa_debug.Init_err_stack ( 'Allocation Run');
150: v_debug_mode := NVL(p_debug_mode, 'Y');
151: v_process_mode := NVL(p_process_mode, 'SQL');
152: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
153: pa_debug.G_err_code := '0';
148: BEGIN
149: pa_debug.Init_err_stack ( 'Allocation Run');
150: v_debug_mode := NVL(p_debug_mode, 'Y');
151: v_process_mode := NVL(p_process_mode, 'SQL');
152: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
153: pa_debug.G_err_code := '0';
154: x_retcode := pa_debug.G_err_code;
155: x_errbuf := NULL;
156: G_rule_id := p_rule_id ;
149: pa_debug.Init_err_stack ( 'Allocation Run');
150: v_debug_mode := NVL(p_debug_mode, 'Y');
151: v_process_mode := NVL(p_process_mode, 'SQL');
152: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
153: pa_debug.G_err_code := '0';
154: x_retcode := pa_debug.G_err_code;
155: x_errbuf := NULL;
156: G_rule_id := p_rule_id ;
157: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
150: v_debug_mode := NVL(p_debug_mode, 'Y');
151: v_process_mode := NVL(p_process_mode, 'SQL');
152: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
153: pa_debug.G_err_code := '0';
154: x_retcode := pa_debug.G_err_code;
155: x_errbuf := NULL;
156: G_rule_id := p_rule_id ;
157: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
158: IF P_DEBUG_MODE = 'Y' THEN
153: pa_debug.G_err_code := '0';
154: x_retcode := pa_debug.G_err_code;
155: x_errbuf := NULL;
156: G_rule_id := p_rule_id ;
157: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
158: IF P_DEBUG_MODE = 'Y' THEN
159: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
160: END IF;
161: SELECT pa_alloc_runs_s.nextval
155: x_errbuf := NULL;
156: G_rule_id := p_rule_id ;
157: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
158: IF P_DEBUG_MODE = 'Y' THEN
159: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
160: END IF;
161: SELECT pa_alloc_runs_s.nextval
162: INTO v_run_id
163: FROM dual;
162: INTO v_run_id
163: FROM dual;
164: G_alloc_run_id := v_run_id;
165: x_run_id := v_run_id;
166: pa_debug.G_err_stage:= 'INITIALIZING WHO COLUMNS';
167: IF P_DEBUG_MODE = 'Y' THEN
168: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
169: END IF;
170: init_who_cols();
164: G_alloc_run_id := v_run_id;
165: x_run_id := v_run_id;
166: pa_debug.G_err_stage:= 'INITIALIZING WHO COLUMNS';
167: IF P_DEBUG_MODE = 'Y' THEN
168: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
169: END IF;
170: init_who_cols();
171: -- Initialize the global variables for currency information
172: pa_currency.SET_CURRENCY_INFO ;
169: END IF;
170: init_who_cols();
171: -- Initialize the global variables for currency information
172: pa_currency.SET_CURRENCY_INFO ;
173: pa_debug.G_err_stage:= 'GETTING CURENCY CODE';
174: IF P_DEBUG_MODE = 'Y' THEN
175: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
176: END IF;
177: G_denom_currency_code := pa_currency.get_currency_code();
171: -- Initialize the global variables for currency information
172: pa_currency.SET_CURRENCY_INFO ;
173: pa_debug.G_err_stage:= 'GETTING CURENCY CODE';
174: IF P_DEBUG_MODE = 'Y' THEN
175: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
176: END IF;
177: G_denom_currency_code := pa_currency.get_currency_code();
178: -- G_lock_name := 'PA_AL-'||to_char(p_rule_id);
179: -- pa_debug.G_err_stage := 'ACQUIRING LOCK';
175: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
176: END IF;
177: G_denom_currency_code := pa_currency.get_currency_code();
178: -- G_lock_name := 'PA_AL-'||to_char(p_rule_id);
179: -- pa_debug.G_err_stage := 'ACQUIRING LOCK';
180: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
181: -- IF (pa_debug.Acquire_User_Lock(G_lock_name) <> 0 ) THEN
182: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
183: -- 'PA_AL_CANT_ACQUIRE_LOCK', TRUE );
176: END IF;
177: G_denom_currency_code := pa_currency.get_currency_code();
178: -- G_lock_name := 'PA_AL-'||to_char(p_rule_id);
179: -- pa_debug.G_err_stage := 'ACQUIRING LOCK';
180: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
181: -- IF (pa_debug.Acquire_User_Lock(G_lock_name) <> 0 ) THEN
182: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
183: -- 'PA_AL_CANT_ACQUIRE_LOCK', TRUE );
184: -- END IF;
177: G_denom_currency_code := pa_currency.get_currency_code();
178: -- G_lock_name := 'PA_AL-'||to_char(p_rule_id);
179: -- pa_debug.G_err_stage := 'ACQUIRING LOCK';
180: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
181: -- IF (pa_debug.Acquire_User_Lock(G_lock_name) <> 0 ) THEN
182: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
183: -- 'PA_AL_CANT_ACQUIRE_LOCK', TRUE );
184: -- END IF;
185: lock_rule ( p_rule_id , x_run_id ) ;
182: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
183: -- 'PA_AL_CANT_ACQUIRE_LOCK', TRUE );
184: -- END IF;
185: lock_rule ( p_rule_id , x_run_id ) ;
186: pa_debug.G_err_stage := 'GETTING RULE DEFINITION';
187: IF P_DEBUG_MODE = 'Y' THEN
188: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
189: END IF;
190: OPEN C_get_rule;
184: -- END IF;
185: lock_rule ( p_rule_id , x_run_id ) ;
186: pa_debug.G_err_stage := 'GETTING RULE DEFINITION';
187: IF P_DEBUG_MODE = 'Y' THEN
188: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
189: END IF;
190: OPEN C_get_rule;
191: FETCH C_get_rule INTO v_alloc_rule_rec;
192: IF C_get_rule%NOTFOUND THEN
200: pa_client_extn_alloc.check_dependency(p_rule_id, v_status, v_err_message) ;
201: IF nvl(v_status,0) <> 0 then
202: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
203: IF P_DEBUG_MODE = 'Y' THEN
204: pa_debug.write_file('allocation_run: ' || 'LOG',v_err_message);
205: END IF;
206: alloc_errors(p_rule_id, x_run_id, 'R', 'E',v_err_message, TRUE) ;
207: END IF ;
208: pa_debug.G_err_stage := 'CHECKING LAST RUN STATUS';
204: pa_debug.write_file('allocation_run: ' || 'LOG',v_err_message);
205: END IF;
206: alloc_errors(p_rule_id, x_run_id, 'R', 'E',v_err_message, TRUE) ;
207: END IF ;
208: pa_debug.G_err_stage := 'CHECKING LAST RUN STATUS';
209: IF P_DEBUG_MODE = 'Y' THEN
210: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
211: END IF;
212: check_last_run_status (p_rule_id, v_run_id, v_mode );
206: alloc_errors(p_rule_id, x_run_id, 'R', 'E',v_err_message, TRUE) ;
207: END IF ;
208: pa_debug.G_err_stage := 'CHECKING LAST RUN STATUS';
209: IF P_DEBUG_MODE = 'Y' THEN
210: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
211: END IF;
212: check_last_run_status (p_rule_id, v_run_id, v_mode );
213: G_alloc_run_id := v_run_id;
214: x_run_id := v_run_id;
222: ELSE
223: G_alloc_run_id := v_run_id;
224: x_run_id := v_run_id;
225: END IF;
226: pa_debug.G_err_stage := 'GET CONCURRENT REQUEST_ID FOR DRAFT';
227: IF P_DEBUG_MODE = 'Y' THEN
228: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
229: END IF;
230: v_request_id:= fnd_global.conc_request_id ;
224: x_run_id := v_run_id;
225: END IF;
226: pa_debug.G_err_stage := 'GET CONCURRENT REQUEST_ID FOR DRAFT';
227: IF P_DEBUG_MODE = 'Y' THEN
228: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
229: END IF;
230: v_request_id:= fnd_global.conc_request_id ;
231: -- 923184 : Getting rule name and request id into a global variable.
232: G_rule_name := v_alloc_rule_rec.rule_name ;
231: -- 923184 : Getting rule name and request id into a global variable.
232: G_rule_name := v_alloc_rule_rec.rule_name ;
233: G_request_id := v_request_id ;
234: IF (v_mode = 'DRAFT' OR v_mode = 'DELETE') THEN /* for 2176096 */
235: pa_debug.G_err_stage := 'GET FISCAL YEAR QUARTER for '||p_run_period;
236: IF P_DEBUG_MODE = 'Y' THEN
237: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
238: END IF;
239: get_fiscalyear_quarter( v_alloc_rule_rec.period_type
233: G_request_id := v_request_id ;
234: IF (v_mode = 'DRAFT' OR v_mode = 'DELETE') THEN /* for 2176096 */
235: pa_debug.G_err_stage := 'GET FISCAL YEAR QUARTER for '||p_run_period;
236: IF P_DEBUG_MODE = 'Y' THEN
237: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
238: END IF;
239: get_fiscalyear_quarter( v_alloc_rule_rec.period_type
240: , p_run_period
241: , v_period_type
243: , v_period_year
244: , v_quarter
245: , v_period_num
246: , v_run_period_end_date );
247: pa_debug.G_err_stage := 'INSERTING ALLOC RUNS';
248: IF P_DEBUG_MODE = 'Y' THEN
249: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
250: END IF;
251: insert_alloc_runs( x_run_id
245: , v_period_num
246: , v_run_period_end_date );
247: pa_debug.G_err_stage := 'INSERTING ALLOC RUNS';
248: IF P_DEBUG_MODE = 'Y' THEN
249: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
250: END IF;
251: insert_alloc_runs( x_run_id
252: , p_rule_id
253: , p_run_period
316: G_tgt_expnd_type:=v_alloc_rule_rec.target_exp_type;
317: G_offset_expnd_org:=pa_utils.GetOrgName( v_alloc_rule_rec.offset_exp_org_id);
318: G_offset_expnd_type_class:= v_alloc_rule_rec.offset_exp_type_class;
319: G_offset_expnd_type:=v_alloc_rule_rec.offset_exp_type;
320: pa_debug.G_err_stage := 'VALIDATING RULE';
321: IF P_DEBUG_MODE = 'Y' THEN
322: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
323: END IF;
324: validate_rule( p_rule_id
318: G_offset_expnd_type_class:= v_alloc_rule_rec.offset_exp_type_class;
319: G_offset_expnd_type:=v_alloc_rule_rec.offset_exp_type;
320: pa_debug.G_err_stage := 'VALIDATING RULE';
321: IF P_DEBUG_MODE = 'Y' THEN
322: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
323: END IF;
324: validate_rule( p_rule_id
325: , x_run_id
326: , v_alloc_rule_rec.start_date_active
344: , v_alloc_rule_rec.basis_balance_type
345: , v_alloc_rule_rec.ORG_ID
346: , v_alloc_rule_rec.fixed_amount
347: , p_expnd_item_date );
348: pa_debug.G_err_stage := 'POPULATING RUN SOURCES';
349: IF P_DEBUG_MODE = 'Y' THEN
350: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
351: END IF;
352: populate_run_sources( p_rule_id
346: , v_alloc_rule_rec.fixed_amount
347: , p_expnd_item_date );
348: pa_debug.G_err_stage := 'POPULATING RUN SOURCES';
349: IF P_DEBUG_MODE = 'Y' THEN
350: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
351: END IF;
352: populate_run_sources( p_rule_id
353: , x_run_id
354: , v_alloc_rule_rec.alloc_resource_list_id
356: /* FP.M : Allocation Impact */
357: , v_alloc_rule_rec.alloc_resource_struct_type
358: , v_alloc_rule_rec.alloc_rbs_version
359: );
360: pa_debug.G_err_stage := 'POPULATING RUN TARGETS';
361: IF P_DEBUG_MODE = 'Y' THEN
362: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
363: END IF;
364: populate_run_targets( p_rule_id
358: , v_alloc_rule_rec.alloc_rbs_version
359: );
360: pa_debug.G_err_stage := 'POPULATING RUN TARGETS';
361: IF P_DEBUG_MODE = 'Y' THEN
362: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
363: END IF;
364: populate_run_targets( p_rule_id
365: , x_run_id
366: , v_alloc_rule_rec.basis_method
387: alloc_errors( p_rule_id, x_run_id, 'T', 'E',
388: 'PA_AL_NO_TARGET_DETAILS', TRUE,'Y' );
389: end if ;
390: /* raise error NOW if validation failed at any point */
391: pa_debug.G_err_stage := 'CHECKING FOR FATAL ERRORS';
392: IF P_DEBUG_MODE = 'Y' THEN
393: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
394: END IF;
395: IF (G_fatal_err_found) THEN
389: end if ;
390: /* raise error NOW if validation failed at any point */
391: pa_debug.G_err_stage := 'CHECKING FOR FATAL ERRORS';
392: IF P_DEBUG_MODE = 'Y' THEN
393: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
394: END IF;
395: IF (G_fatal_err_found) THEN
396: G_fatal_err_found:= FALSE;
397: COMMIT;
398: alloc_errors( p_rule_id, x_run_id, 'R', 'E',
399: 'PA_AL_FATAL_ERROR_FOUND', TRUE,'N' );
400: END IF;
401: COMMIT ; /* Introduced as part of commit cycle changes */
402: pa_debug.G_err_stage := 'CALCULATE SRC PROJECT AMOUNTS';
403: IF P_DEBUG_MODE = 'Y' THEN
404: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
405: END IF;
406: cal_amounts_from_projects( p_rule_id
400: END IF;
401: COMMIT ; /* Introduced as part of commit cycle changes */
402: pa_debug.G_err_stage := 'CALCULATE SRC PROJECT AMOUNTS';
403: IF P_DEBUG_MODE = 'Y' THEN
404: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
405: END IF;
406: cal_amounts_from_projects( p_rule_id
407: , x_run_id
408: , v_alloc_rule_rec.period_type
416: /* FP.M : Allocation Impact : Bug # 3512552 */
417: , v_alloc_rule_rec.ALLOC_RESOURCE_STRUCT_TYPE
418: , v_alloc_rule_rec.ALLOC_RBS_VERSION
419: );
420: pa_debug.G_err_stage := 'CALCULATE SRC GL AMOUNTS';
421: IF P_DEBUG_MODE = 'Y' THEN
422: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
423: END IF;
424: calculate_src_GL_amounts( p_rule_id
418: , v_alloc_rule_rec.ALLOC_RBS_VERSION
419: );
420: pa_debug.G_err_stage := 'CALCULATE SRC GL AMOUNTS';
421: IF P_DEBUG_MODE = 'Y' THEN
422: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
423: END IF;
424: calculate_src_GL_amounts( p_rule_id
425: , x_run_id
426: , p_run_period
426: , p_run_period
427: , v_alloc_rule_rec.source_amount_type
428: , v_src_amount_from_GL );
429: IF P_DEBUG_MODE = 'Y' THEN
430: pa_debug.write_file('allocation_run: ' || 'LOG', 'v_src_amount_from_GL: '||to_char(v_src_amount_from_GL) );
431: END IF;
432: If v_alloc_rule_rec.allocation_method = 'F' then
433: If (nvl(v_src_amount_from_proj,0) + nvl(v_src_amount_from_GL,0)) = 0 then
434: v_pazspa :='1' ;
436: 'PA_AL_ZERO_SOURCE_POOL_AMOUNT', TRUE );
437: /*Return; reverting changes done in 5598267 for bug 9789612*/
438: End if ;
439: End If ;
440: pa_debug.G_err_stage := 'CALCULATE BASIS PROJECT AMOUNTS';
441: IF P_DEBUG_MODE = 'Y' THEN
442: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
443: END IF;
444: /* 2182563 - v_basis_method is a out variable now. If no records are found
438: End if ;
439: End If ;
440: pa_debug.G_err_stage := 'CALCULATE BASIS PROJECT AMOUNTS';
441: IF P_DEBUG_MODE = 'Y' THEN
442: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
443: END IF;
444: /* 2182563 - v_basis_method is a out variable now. If no records are found
445: for basis method 'P' or 'C' then it is changed to 'S'. */
446: cal_proj_basis_amounts( p_rule_id
459: , v_alloc_rule_rec.BASIS_RESOURCE_STRUCT_TYPE
460: , v_alloc_rule_rec.BASIS_RBS_VERSION
461: );
462: COMMIT ; /* Introduced as part of commit cycle changes */
463: pa_debug.G_err_stage := 'CREATING TARGET TRANSACTIONS';
464: IF P_DEBUG_MODE = 'Y' THEN
465: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
466: END IF;
467: create_target_txns( p_rule_id
461: );
462: COMMIT ; /* Introduced as part of commit cycle changes */
463: pa_debug.G_err_stage := 'CREATING TARGET TRANSACTIONS';
464: IF P_DEBUG_MODE = 'Y' THEN
465: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
466: END IF;
467: create_target_txns( p_rule_id
468: , x_run_id
469: , v_alloc_rule_rec.period_type
477: , v_alloc_rule_rec.source_amount_type
478: , nvl(v_src_amount_from_proj,0) + nvl(v_src_amount_from_GL,0)
479: , v_curr_alloc_amount );
480: IF (v_alloc_rule_rec.offset_method <> 'N') THEN
481: pa_debug.G_err_stage := 'CREATING OFFSET TRANSACTIONS';
482: IF P_DEBUG_MODE = 'Y' THEN
483: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
484: END IF;
485: create_offset_txns( p_rule_id
479: , v_curr_alloc_amount );
480: IF (v_alloc_rule_rec.offset_method <> 'N') THEN
481: pa_debug.G_err_stage := 'CREATING OFFSET TRANSACTIONS';
482: IF P_DEBUG_MODE = 'Y' THEN
483: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
484: END IF;
485: create_offset_txns( p_rule_id
486: , x_run_id
487: , v_alloc_rule_rec.period_type
501: END IF; /* v_mode = draft */
502: COMMIT ; /* Introduced as part of commit cycle changes */
503: IF (v_mode = 'RELEASE' OR v_alloc_rule_rec.auto_release_flag ='Y') THEN
504: v_mode := 'RELEASE' ;
505: pa_debug.G_err_stage := 'RELEASING THE RUN';
506: IF P_DEBUG_MODE = 'Y' THEN
507: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
508: END IF;
509: pa_alloc_run.release_alloc_txns( p_rule_id, x_run_id ,x_retcode,
503: IF (v_mode = 'RELEASE' OR v_alloc_rule_rec.auto_release_flag ='Y') THEN
504: v_mode := 'RELEASE' ;
505: pa_debug.G_err_stage := 'RELEASING THE RUN';
506: IF P_DEBUG_MODE = 'Y' THEN
507: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
508: END IF;
509: pa_alloc_run.release_alloc_txns( p_rule_id, x_run_id ,x_retcode,
510: x_errbuf );
511: END IF; /* v_mode = release */
520: --
521: IF (G_num_txns = 0 ) THEN
522: v_mode := 'RELEASE' ;
523: IF P_DEBUG_MODE = 'Y' THEN
524: pa_debug.write_file('allocation_run: ' || 'LOG', 'All tasks received zero amounts. No transactions created');
525: END IF;
526: alloc_errors( p_rule_id, x_run_id,'R','W','PA_AL_ALL_TASKS_RECD_ZERO_AMT', FALSE) ;
527: END IF ;
528:
532: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
533: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
534: WHERE run_id = x_run_id;
535:
536: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS SUCCESS';
537:
538: IF P_DEBUG_MODE = 'Y' THEN
539: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
540: END IF;
535:
536: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS SUCCESS';
537:
538: IF P_DEBUG_MODE = 'Y' THEN
539: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
540: END IF;
541:
542: /* added for 6243121 */
543: else -- if any error/exception is encountered while releasing
546: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
547: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
548: WHERE run_id = x_run_id;
549:
550: pa_debug.G_err_stage := 'UPDATED RUN STATUS AS FAILURE';
551: IF P_DEBUG_MODE = 'Y' THEN
552: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
553: END IF;
554: end if;
548: WHERE run_id = x_run_id;
549:
550: pa_debug.G_err_stage := 'UPDATED RUN STATUS AS FAILURE';
551: IF P_DEBUG_MODE = 'Y' THEN
552: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
553: END IF;
554: end if;
555: /* added for 6243121 */
556:
553: END IF;
554: end if;
555: /* added for 6243121 */
556:
557: pa_debug.G_err_stage := 'RELEASING LOCK';
558: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
559: -- IF (pa_debug.Release_User_Lock(G_lock_name) <> 0 ) THEN
560: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
561: -- 'PA_AL_LOCK_RELEASE_FAILED', TRUE );
554: end if;
555: /* added for 6243121 */
556:
557: pa_debug.G_err_stage := 'RELEASING LOCK';
558: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
559: -- IF (pa_debug.Release_User_Lock(G_lock_name) <> 0 ) THEN
560: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
561: -- 'PA_AL_LOCK_RELEASE_FAILED', TRUE );
562: -- END IF;
555: /* added for 6243121 */
556:
557: pa_debug.G_err_stage := 'RELEASING LOCK';
558: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
559: -- IF (pa_debug.Release_User_Lock(G_lock_name) <> 0 ) THEN
560: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
561: -- 'PA_AL_LOCK_RELEASE_FAILED', TRUE );
562: -- END IF;
563: unlock_rule(p_rule_id, x_run_id) ;
562: -- END IF;
563: unlock_rule(p_rule_id, x_run_id) ;
564: COMMIT;
565: /* restore the old stack */
566: pa_debug.reset_err_stack;
567: EXCEPTION
568: WHEN OTHERS THEN
569: x_errbuf:= pa_debug.G_err_stage|| ': '||sqlerrm;
570: x_run_id:= G_alloc_run_id;
565: /* restore the old stack */
566: pa_debug.reset_err_stack;
567: EXCEPTION
568: WHEN OTHERS THEN
569: x_errbuf:= pa_debug.G_err_stage|| ': '||sqlerrm;
570: x_run_id:= G_alloc_run_id;
571: IF P_DEBUG_MODE = 'Y' THEN
572: pa_debug.write_file('allocation_run: ' || 'LOG', x_errbuf);
573: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
568: WHEN OTHERS THEN
569: x_errbuf:= pa_debug.G_err_stage|| ': '||sqlerrm;
570: x_run_id:= G_alloc_run_id;
571: IF P_DEBUG_MODE = 'Y' THEN
572: pa_debug.write_file('allocation_run: ' || 'LOG', x_errbuf);
573: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
574: END IF;
575: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
576: IF P_DEBUG_MODE = 'Y' THEN
569: x_errbuf:= pa_debug.G_err_stage|| ': '||sqlerrm;
570: x_run_id:= G_alloc_run_id;
571: IF P_DEBUG_MODE = 'Y' THEN
572: pa_debug.write_file('allocation_run: ' || 'LOG', x_errbuf);
573: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
574: END IF;
575: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
576: IF P_DEBUG_MODE = 'Y' THEN
577: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
571: IF P_DEBUG_MODE = 'Y' THEN
572: pa_debug.write_file('allocation_run: ' || 'LOG', x_errbuf);
573: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
574: END IF;
575: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
576: IF P_DEBUG_MODE = 'Y' THEN
577: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
578: END IF;
579: UPDATE pa_alloc_runs
573: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
574: END IF;
575: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
576: IF P_DEBUG_MODE = 'Y' THEN
577: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
578: END IF;
579: UPDATE pa_alloc_runs
580: SET run_status = DECODE( v_mode, 'RELEASE', 'RF',
581: 'DRAFT', 'DF',
583: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
584: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
585: WHERE run_id = x_run_id;
586: IF P_DEBUG_MODE = 'Y' THEN
587: pa_debug.write_file('allocation_run: ' || 'LOG', 'x_run_id in when others of Main: '||to_char( x_run_id) );
588: END IF;
589: COMMIT;
590: /* 2841843 Marking Conc request as completed failure
591: Explicitely not raising the exception as all debug messages
613: WHERE run_id = p_prev_run_id ;
614: v_run_status VARCHAR2(5) := NULL;
615: v_prev_run_id NUMBER;
616: BEGIN
617: pa_debug.set_err_stack('check_last_run_status');
618: pa_debug.G_err_code := 0;
619: OPEN C_run_id;
620: FETCH C_run_id INTO v_prev_run_id;
621: CLOSE C_run_id;
614: v_run_status VARCHAR2(5) := NULL;
615: v_prev_run_id NUMBER;
616: BEGIN
617: pa_debug.set_err_stack('check_last_run_status');
618: pa_debug.G_err_code := 0;
619: OPEN C_run_id;
620: FETCH C_run_id INTO v_prev_run_id;
621: CLOSE C_run_id;
622: IF v_prev_run_id IS NULL THEN
646: x_run_id := v_prev_run_id;
647: END IF ;
648: */
649: /* restore the old stack */
650: pa_debug.reset_err_stack;
651: EXCEPTION
652: WHEN OTHERS THEN
653: pa_debug.G_err_code := SQLCODE;
654: RAISE;
649: /* restore the old stack */
650: pa_debug.reset_err_stack;
651: EXCEPTION
652: WHEN OTHERS THEN
653: pa_debug.G_err_code := SQLCODE;
654: RAISE;
655: END check_last_run_status;
656: -- ------------------------------------------------------------
657: -- ins_alloc_exceptions
668: , p_project_id IN NUMBER
669: , p_task_id IN NUMBER
670: , p_exception_code IN VARCHAR2 ) IS
671: BEGIN
672: pa_debug.set_err_stack('ins_alloc_exceptions');
673: INSERT INTO pa_alloc_exceptions (
674: RUN_ID
675: , RULE_ID
676: , LEVEL_CODE
696: , p_project_id
697: , p_task_id
698: , p_exception_code );
699: /* restore the old stack */
700: pa_debug.reset_err_stack;
701: EXCEPTION
702: WHEN OTHERS THEN
703: pa_debug.G_err_code := SQLCODE;
704: RAISE;
699: /* restore the old stack */
700: pa_debug.reset_err_stack;
701: EXCEPTION
702: WHEN OTHERS THEN
703: pa_debug.G_err_code := SQLCODE;
704: RAISE;
705: END ins_alloc_exceptions;
706: -- ------------------------------------------------------------
707: -- alloc_errors:
717: , p_task_id IN NUMBER DEFAULT NULL ) IS
718: v_mesg_code VARCHAR2(30);
719: BEGIN
720: v_mesg_code := SUBSTR( p_mesg_code,1,30);
721: pa_debug.set_err_stack('alloc_errors');
722: IF ( p_insert_flag = 'Y') THEN
723: ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
724: G_created_by, G_last_update_date,
725: G_last_updated_by, G_last_update_login,
726: p_level, p_type, p_project_id, p_task_id, v_mesg_code );
727: END IF;
728: IF (p_fatal_err) THEN
729: /*Return; reverting changes done in 5598267 for bug 9789612 */
730: pa_debug.raise_error( -20010, p_mesg_code );
731: END IF;
732: pa_debug.reset_err_stack;
733: EXCEPTION
734: WHEN OTHERS THEN
728: IF (p_fatal_err) THEN
729: /*Return; reverting changes done in 5598267 for bug 9789612 */
730: pa_debug.raise_error( -20010, p_mesg_code );
731: END IF;
732: pa_debug.reset_err_stack;
733: EXCEPTION
734: WHEN OTHERS THEN
735: pa_debug.G_err_code := SQLCODE;
736: RAISE;
731: END IF;
732: pa_debug.reset_err_stack;
733: EXCEPTION
734: WHEN OTHERS THEN
735: pa_debug.G_err_code := SQLCODE;
736: RAISE;
737: END alloc_errors;
738: -- ----------------------------------------------------------------------
739: -- Validate_rule
810: v_dummy NUMBER;
811: -- v_task_id NUMBER;
812: -- v_billable_only_flag VARCHAR2(1);
813: BEGIN
814: pa_debug.set_err_stack('validate_rule');
815: pa_debug.G_err_code:= 0;
816: pa_debug.G_err_stage:= 'CHECKING DATE EFFECTIVITY OF RULE';
817: IF P_DEBUG_MODE = 'Y' THEN
818: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
811: -- v_task_id NUMBER;
812: -- v_billable_only_flag VARCHAR2(1);
813: BEGIN
814: pa_debug.set_err_stack('validate_rule');
815: pa_debug.G_err_code:= 0;
816: pa_debug.G_err_stage:= 'CHECKING DATE EFFECTIVITY OF RULE';
817: IF P_DEBUG_MODE = 'Y' THEN
818: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
819: END IF;
812: -- v_billable_only_flag VARCHAR2(1);
813: BEGIN
814: pa_debug.set_err_stack('validate_rule');
815: pa_debug.G_err_code:= 0;
816: pa_debug.G_err_stage:= 'CHECKING DATE EFFECTIVITY OF RULE';
817: IF P_DEBUG_MODE = 'Y' THEN
818: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
819: END IF;
820: IF ( TRUNC(p_start_date_active) > TRUNC(G_sysdate) OR
814: pa_debug.set_err_stack('validate_rule');
815: pa_debug.G_err_code:= 0;
816: pa_debug.G_err_stage:= 'CHECKING DATE EFFECTIVITY OF RULE';
817: IF P_DEBUG_MODE = 'Y' THEN
818: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
819: END IF;
820: IF ( TRUNC(p_start_date_active) > TRUNC(G_sysdate) OR
821: TRUNC(p_end_date_active)
822: G_fatal_err_found := TRUE;
823: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
824: 'PA_AL_RULE_INACTIVE');
825: END IF;
826: pa_debug.G_err_stage:= 'CHECKING SRC_LINES DEFINITON';
827: IF P_DEBUG_MODE = 'Y' THEN
828: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
829: END IF;
830: IF(p_fixed_amount IS NULL) THEN
824: 'PA_AL_RULE_INACTIVE');
825: END IF;
826: pa_debug.G_err_stage:= 'CHECKING SRC_LINES DEFINITON';
827: IF P_DEBUG_MODE = 'Y' THEN
828: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
829: END IF;
830: IF(p_fixed_amount IS NULL) THEN
831: IF (p_source_extn_flag = 'N') THEN
832: OPEN C_pa_src_exists;
849: 'PA_AL_NO_SOURCE_LINES');
850: END IF; /* if v_gl_err AND v_src_err */
851: END IF; /* if p_source_extn_flag */
852: END IF; /* if p_fixed_amount */
853: pa_debug.G_err_stage:= 'CHECKING TARGET LINES';
854: IF P_DEBUG_MODE = 'Y' THEN
855: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
856: END IF;
857: IF( p_target_extn_flag = 'N') THEN
851: END IF; /* if p_source_extn_flag */
852: END IF; /* if p_fixed_amount */
853: pa_debug.G_err_stage:= 'CHECKING TARGET LINES';
854: IF P_DEBUG_MODE = 'Y' THEN
855: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
856: END IF;
857: IF( p_target_extn_flag = 'N') THEN
858: v_dummy:= 0;
859: OPEN C_pa_trg_exists ;
909: CLOSE C_billable_task ;
910: END IF; /* billable_flag */
911: END IF; /* task_id IS NOT NULL */
912: END LOOP; /* C_target_lines */
913: pa_debug.G_err_stage:= 'VALIDATING OFFSET METHOD';
914: IF P_DEBUG_MODE = 'Y' THEN
915: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
916: END IF;
917: IF(p_offset_method IN ( 'A', 'B')) THEN
911: END IF; /* task_id IS NOT NULL */
912: END LOOP; /* C_target_lines */
913: pa_debug.G_err_stage:= 'VALIDATING OFFSET METHOD';
914: IF P_DEBUG_MODE = 'Y' THEN
915: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
916: END IF;
917: IF(p_offset_method IN ( 'A', 'B')) THEN
918: /* if GL_source_lines exists or p_fixed_amount exists
919: then error out */
923: 'PA_AL_INVALID_OFFSET_METHOD');
924: END IF;
925: END IF; /* if p_offset_method */
926: IF (p_offset_method <> 'N') THEN
927: pa_debug.G_err_stage:= 'CHECKING OFFSET LINES';
928: IF P_DEBUG_MODE = 'Y' THEN
929: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
930: END IF;
931: IF(p_offset_method = 'C') THEN /* specific offset proj/task */
925: END IF; /* if p_offset_method */
926: IF (p_offset_method <> 'N') THEN
927: pa_debug.G_err_stage:= 'CHECKING OFFSET LINES';
928: IF P_DEBUG_MODE = 'Y' THEN
929: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
930: END IF;
931: IF(p_offset_method = 'C') THEN /* specific offset proj/task */
932: OPEN C_check_task(p_offset_project_id, p_offset_task_id, p_expnd_item_date);
933: FETCH C_check_task INTO v_dummy;
939: CLOSE C_check_task;
940: END IF; /* endif offset_method */
941: END IF; /* p_offset_method <> */
942: IF(p_basis_method NOT IN ( 'S', 'C')) THEN
943: pa_debug.G_err_stage:= 'CHECKING BASIS LINES';
944: IF P_DEBUG_MODE = 'Y' THEN
945: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
946: END IF;
947: IF (p_basis_method IN( 'P', 'FP')) THEN
941: END IF; /* p_offset_method <> */
942: IF(p_basis_method NOT IN ( 'S', 'C')) THEN
943: pa_debug.G_err_stage:= 'CHECKING BASIS LINES';
944: IF P_DEBUG_MODE = 'Y' THEN
945: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
946: END IF;
947: IF (p_basis_method IN( 'P', 'FP')) THEN
948: IF (p_basis_balance_category IS NULL) THEN
949: G_fatal_err_found := TRUE;
975: END IF;
976: END IF; /* if p_basis_balance_category */
977: END IF; /* if p_basis_method = 'P' or 'FP' */
978: END IF; /* if basis_method NOT IN */
979: pa_debug.G_err_stage:= 'VALIDATING TARGET EXP ORGANIZATION';
980: IF P_DEBUG_MODE = 'Y' THEN
981: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
982: END IF;
983: IF( p_target_exp_org_id IS NULL OR
977: END IF; /* if p_basis_method = 'P' or 'FP' */
978: END IF; /* if basis_method NOT IN */
979: pa_debug.G_err_stage:= 'VALIDATING TARGET EXP ORGANIZATION';
980: IF P_DEBUG_MODE = 'Y' THEN
981: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
982: END IF;
983: IF( p_target_exp_org_id IS NULL OR
984: pa_utils2.CheckExpOrg( p_target_exp_org_id) = 'N') THEN
985: G_fatal_err_found := TRUE;
985: G_fatal_err_found := TRUE;
986: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
987: 'PA_AL_INVALID_TARGET_EXP_ORG') ;
988: END IF;
989: pa_debug.G_err_stage:= 'VALIDATING TARGET EXPENDITURE TYPE';
990: IF P_DEBUG_MODE = 'Y' THEN
991: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
992: END IF;
993: IF(p_target_exp_type IS NOT NULL) THEN
987: 'PA_AL_INVALID_TARGET_EXP_ORG') ;
988: END IF;
989: pa_debug.G_err_stage:= 'VALIDATING TARGET EXPENDITURE TYPE';
990: IF P_DEBUG_MODE = 'Y' THEN
991: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
992: END IF;
993: IF(p_target_exp_type IS NOT NULL) THEN
994: OPEN C_exptype_exists(p_target_exp_type);
995: FETCH C_exptype_exists INTO v_dummy;
1004: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
1005: 'PA_AL_INVALID_TARGET_EXP_TYPE') ;
1006: END IF; /* if p_target_exp_type */
1007: IF (p_offset_method <> 'N') THEN
1008: pa_debug.G_err_stage:= 'VALIDATING OFFSET EXP ORGANIZATION';
1009: IF P_DEBUG_MODE = 'Y' THEN
1010: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
1011: END IF;
1012: IF( p_offset_exp_org_id IS NULL ) THEN
1006: END IF; /* if p_target_exp_type */
1007: IF (p_offset_method <> 'N') THEN
1008: pa_debug.G_err_stage:= 'VALIDATING OFFSET EXP ORGANIZATION';
1009: IF P_DEBUG_MODE = 'Y' THEN
1010: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
1011: END IF;
1012: IF( p_offset_exp_org_id IS NULL ) THEN
1013: G_fatal_err_found := TRUE;
1014: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
1017: G_fatal_err_found := TRUE;
1018: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
1019: 'PA_AL_INVALID_OFFSET_EXP_ORG') ;
1020: END IF;
1021: pa_debug.G_err_stage:= 'VALIDATING OFFSET EXPENDITURE TYPE';
1022: IF P_DEBUG_MODE = 'Y' THEN
1023: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
1024: END IF;
1025: IF ( p_offset_exp_type IS NOT NULL) THEN
1019: 'PA_AL_INVALID_OFFSET_EXP_ORG') ;
1020: END IF;
1021: pa_debug.G_err_stage:= 'VALIDATING OFFSET EXPENDITURE TYPE';
1022: IF P_DEBUG_MODE = 'Y' THEN
1023: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
1024: END IF;
1025: IF ( p_offset_exp_type IS NOT NULL) THEN
1026: OPEN C_exptype_exists(p_offset_exp_type);
1027: FETCH C_exptype_exists INTO v_dummy;
1037: 'PA_AL_UNDEFINED_OFFSET_EXP_TYPE');
1038: END IF; /* if p_offset_exp_type */
1039: END IF; /* p_offset_method */
1040: /* restore the old G_err_stack */
1041: pa_debug.reset_err_stack;
1042: EXCEPTION
1043: WHEN OTHERS THEN
1044: pa_debug.G_err_code := SQLCODE;
1045: RAISE;
1040: /* restore the old G_err_stack */
1041: pa_debug.reset_err_stack;
1042: EXCEPTION
1043: WHEN OTHERS THEN
1044: pa_debug.G_err_code := SQLCODE;
1045: RAISE;
1046: END validate_rule;
1047: -- ------------------------------------------------------------
1048: -- insert_alloc_run_sources
1066: AND task_id = p_task_id;
1067: v_dummy NUMBER;
1068: allow_insert_flag VARCHAR2(1);
1069: BEGIN
1070: pa_debug.set_err_stack('ins_alloc_run_sources');
1071: pa_debug.G_err_code:= 0;
1072: OPEN source_exists;
1073: FETCH source_exists INTO v_dummy;
1074: IF source_exists%NOTFOUND THEN
1067: v_dummy NUMBER;
1068: allow_insert_flag VARCHAR2(1);
1069: BEGIN
1070: pa_debug.set_err_stack('ins_alloc_run_sources');
1071: pa_debug.G_err_code:= 0;
1072: OPEN source_exists;
1073: FETCH source_exists INTO v_dummy;
1074: IF source_exists%NOTFOUND THEN
1075: /* allow insert if current proj-task not exists */
1107: , p_last_update_login
1108: , p_task_id );
1109: END IF;
1110: /* restore the old stack */
1111: pa_debug.reset_err_stack;
1112: EXCEPTION
1113: WHEN OTHERS THEN
1114: pa_debug.G_err_code := SQLCODE;
1115: RAISE;
1110: /* restore the old stack */
1111: pa_debug.reset_err_stack;
1112: EXCEPTION
1113: WHEN OTHERS THEN
1114: pa_debug.G_err_code := SQLCODE;
1115: RAISE;
1116: END insert_alloc_run_sources;
1117: -- ------------------------------------------------------------
1118: -- exclude_curr_proj_task
1138: v_dummy1 NUMBER;
1139: v_dummy2 NUMBER;
1140: v_status NUMBER:= 0;
1141: BEGIN
1142: pa_debug.set_err_stack('exclude_curr_proj_task');
1143: pa_debug.G_err_code:= 0;
1144: pa_debug.G_err_stage := 'DETERMINE EXCL_CURR_PROJ_TASK';
1145: IF P_DEBUG_MODE = 'Y' THEN
1146: pa_debug.write_file('exclude_curr_proj_task: ' || 'LOG', pa_debug.G_err_stage);
1139: v_dummy2 NUMBER;
1140: v_status NUMBER:= 0;
1141: BEGIN
1142: pa_debug.set_err_stack('exclude_curr_proj_task');
1143: pa_debug.G_err_code:= 0;
1144: pa_debug.G_err_stage := 'DETERMINE EXCL_CURR_PROJ_TASK';
1145: IF P_DEBUG_MODE = 'Y' THEN
1146: pa_debug.write_file('exclude_curr_proj_task: ' || 'LOG', pa_debug.G_err_stage);
1147: END IF;
1140: v_status NUMBER:= 0;
1141: BEGIN
1142: pa_debug.set_err_stack('exclude_curr_proj_task');
1143: pa_debug.G_err_code:= 0;
1144: pa_debug.G_err_stage := 'DETERMINE EXCL_CURR_PROJ_TASK';
1145: IF P_DEBUG_MODE = 'Y' THEN
1146: pa_debug.write_file('exclude_curr_proj_task: ' || 'LOG', pa_debug.G_err_stage);
1147: END IF;
1148: IF (p_type = 'SRC') THEN
1142: pa_debug.set_err_stack('exclude_curr_proj_task');
1143: pa_debug.G_err_code:= 0;
1144: pa_debug.G_err_stage := 'DETERMINE EXCL_CURR_PROJ_TASK';
1145: IF P_DEBUG_MODE = 'Y' THEN
1146: pa_debug.write_file('exclude_curr_proj_task: ' || 'LOG', pa_debug.G_err_stage);
1147: END IF;
1148: IF (p_type = 'SRC') THEN
1149: OPEN C_excl_src_proj_task;
1150: FETCH C_excl_src_proj_task INTO v_dummy1, v_dummy2;
1164: END IF;
1165: CLOSE C_excl_trg_proj_task;
1166: END IF;
1167: /* restore the old stack */
1168: pa_debug.reset_err_stack;
1169: return v_status;
1170: EXCEPTION
1171: WHEN OTHERS THEN
1172: return (SQLCODE);
1327: v_src_task_id NUMBER ;
1328: v_src_top_task_id NUMBER ;
1329: v_cx_err_flag VARCHAR2(1) ;
1330: BEGIN
1331: pa_debug.set_err_stack('populate_run_sources');
1332: pa_debug.G_err_code:= 0;
1333: IF p_source_clnt_extn_flag = 'Y' THEN
1334: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR EXCLUDES';
1335: IF P_DEBUG_MODE = 'Y' THEN
1328: v_src_top_task_id NUMBER ;
1329: v_cx_err_flag VARCHAR2(1) ;
1330: BEGIN
1331: pa_debug.set_err_stack('populate_run_sources');
1332: pa_debug.G_err_code:= 0;
1333: IF p_source_clnt_extn_flag = 'Y' THEN
1334: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR EXCLUDES';
1335: IF P_DEBUG_MODE = 'Y' THEN
1336: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1330: BEGIN
1331: pa_debug.set_err_stack('populate_run_sources');
1332: pa_debug.G_err_code:= 0;
1333: IF p_source_clnt_extn_flag = 'Y' THEN
1334: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR EXCLUDES';
1335: IF P_DEBUG_MODE = 'Y' THEN
1336: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1337: END IF;
1338: /* reset the error_flag */
1332: pa_debug.G_err_code:= 0;
1333: IF p_source_clnt_extn_flag = 'Y' THEN
1334: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR EXCLUDES';
1335: IF P_DEBUG_MODE = 'Y' THEN
1336: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1337: END IF;
1338: /* reset the error_flag */
1339: G_fatal_err_found := FALSE;
1340: pa_client_extn_alloc.source_extn(p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1338: /* reset the error_flag */
1339: G_fatal_err_found := FALSE;
1340: pa_client_extn_alloc.source_extn(p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1341: IF P_DEBUG_MODE = 'Y' THEN
1342: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client return message: '|| v_err_message);
1343: END IF;
1344: IF nvl(v_status,0) <> 0 then
1345: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
1346: alloc_errors(p_rule_id, p_run_id, 'S', 'E', v_err_message, TRUE) ;
1356: G_fatal_err_found:= TRUE;
1357: alloc_errors( p_rule_id, p_run_id, 'S', 'E',
1358: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id);
1359: IF P_DEBUG_MODE = 'Y' THEN
1360: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client Extension returned an invalid source project: '
1361: || to_char(v_cx_project_id));
1362: END IF;
1363: v_cx_err_flag := 'Y' ;
1364: END IF;
1367: G_fatal_err_found:= TRUE;
1368: alloc_errors( p_rule_id, p_run_id, 'S', 'E',
1369: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id, v_cx_task_id);
1370: IF P_DEBUG_MODE = 'Y' THEN
1371: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client Extension returned an invalid source task: '
1372: ||to_char( v_cx_task_id) );
1373: END IF;
1374: v_cx_err_flag := 'Y' ;
1375: END IF;
1453: END IF; /* end exclude_flag = 'Y' */
1454: END LOOP;
1455: END IF; /* end cound>0 */
1456: END IF; /* end src_extn_flag = 'Y' */
1457: pa_debug.G_err_stage:= 'READING SRC LINES FOR EXCLUDES';
1458: IF P_DEBUG_MODE = 'Y' THEN
1459: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1460: END IF;
1461: v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; -- added for bug 3799389
1455: END IF; /* end cound>0 */
1456: END IF; /* end src_extn_flag = 'Y' */
1457: pa_debug.G_err_stage:= 'READING SRC LINES FOR EXCLUDES';
1458: IF P_DEBUG_MODE = 'Y' THEN
1459: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1460: END IF;
1461: v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; -- added for bug 3799389
1462:
1463: FOR source_lines_rec IN c_alloc_source_lines LOOP
1471: , source_lines_rec.project_id
1472: , source_lines_rec.task_id
1473: , v_src_sql_str ) ;
1474: IF P_DEBUG_MODE = 'Y' THEN
1475: pa_debug.write_file('populate_run_sources: ' || 'LOG',v_src_sql_str);
1476: END IF;
1477: /* v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
1478: DBMS_SQL.PARSE(v_source_csr_id, v_src_sql_str, DBMS_SQL.V7) ;
1479: IF source_lines_rec.project_org_id is NOT NULL THEN
1511: dbms_sql.column_value(v_source_csr_id,1,v_src_project_id) ;
1512: dbms_sql.column_value(v_source_csr_id,2,v_src_task_id ) ;
1513: dbms_sql.column_value(v_source_csr_id,3,v_src_top_task_id ) ;
1514: IF P_DEBUG_MODE = 'Y' THEN
1515: pa_debug.write_file('populate_run_sources: ' || 'LOG',to_char(v_src_project_id) || to_char(v_src_task_id) ||
1516: to_char(v_src_top_task_id));
1517: END IF;
1518: insert_alloc_run_sources( p_rule_id
1519: , p_run_id
1530: END IF; /* End if of source excludes from table */
1531: END LOOP; /* endloop read srcs */
1532: DBMS_SQL.CLOSE_CURSOR(v_source_csr_id ); --added for bug 3799389
1533: IF p_source_clnt_extn_flag = 'Y' THEN
1534: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR INCLUDES';
1535: IF P_DEBUG_MODE = 'Y' THEN
1536: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1537: END IF;
1538: pa_client_extn_alloc.source_extn( p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1532: DBMS_SQL.CLOSE_CURSOR(v_source_csr_id ); --added for bug 3799389
1533: IF p_source_clnt_extn_flag = 'Y' THEN
1534: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR INCLUDES';
1535: IF P_DEBUG_MODE = 'Y' THEN
1536: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1537: END IF;
1538: pa_client_extn_alloc.source_extn( p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1539: IF P_DEBUG_MODE = 'Y' THEN
1540: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client return message: '|| v_err_message);
1536: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1537: END IF;
1538: pa_client_extn_alloc.source_extn( p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1539: IF P_DEBUG_MODE = 'Y' THEN
1540: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client return message: '|| v_err_message);
1541: END IF;
1542: IF nvl(v_status,0) <> 0 then
1543: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
1544: alloc_errors(p_rule_id, p_run_id, 'S', 'E', v_err_message, TRUE) ;
1553: G_fatal_err_found:= TRUE;
1554: alloc_errors( p_rule_id, p_run_id, 'S', 'E',
1555: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id);
1556: IF P_DEBUG_MODE = 'Y' THEN
1557: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client Extension returned an invalid source project: '
1558: || to_char(v_cx_project_id));
1559: END IF;
1560: v_cx_err_flag := 'Y' ;
1561: END IF;
1564: G_fatal_err_found:= TRUE;
1565: alloc_errors( p_rule_id, p_run_id, 'S', 'E',
1566: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id, v_cx_task_id);
1567: IF P_DEBUG_MODE = 'Y' THEN
1568: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client Extension returned an invalid source task: '
1569: ||to_char( v_cx_task_id) );
1570: END IF;
1571: v_cx_err_flag := 'Y' ;
1572: END IF;
1649: END IF; /* end exclude_flag = 'N' */
1650: END LOOP;
1651: END IF; /* if count>0 */
1652: END IF; /* end src_extn_flag = 'Y' */
1653: pa_debug.G_err_stage:= 'READING SRC LINES FOR INCLUDES';
1654: v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; -- added for bug 3799389
1655: FOR source_lines_rec IN c_alloc_source_lines LOOP
1656: IF (source_lines_rec.exclude_flag = 'N') THEN
1657: pa_debug.G_err_stage:= 'EXPLODING SOURCE LINES FOR INCLUDES';
1653: pa_debug.G_err_stage:= 'READING SRC LINES FOR INCLUDES';
1654: v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; -- added for bug 3799389
1655: FOR source_lines_rec IN c_alloc_source_lines LOOP
1656: IF (source_lines_rec.exclude_flag = 'N') THEN
1657: pa_debug.G_err_stage:= 'EXPLODING SOURCE LINES FOR INCLUDES';
1658: IF P_DEBUG_MODE = 'Y' THEN
1659: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1660: END IF;
1661: build_src_sql( source_lines_rec.project_org_id
1655: FOR source_lines_rec IN c_alloc_source_lines LOOP
1656: IF (source_lines_rec.exclude_flag = 'N') THEN
1657: pa_debug.G_err_stage:= 'EXPLODING SOURCE LINES FOR INCLUDES';
1658: IF P_DEBUG_MODE = 'Y' THEN
1659: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1660: END IF;
1661: build_src_sql( source_lines_rec.project_org_id
1662: , source_lines_rec.project_type
1663: , source_lines_rec.task_org_id
1667: , source_lines_rec.project_id
1668: , source_lines_rec.task_id
1669: , v_src_sql_str ) ;
1670: IF P_DEBUG_MODE = 'Y' THEN
1671: pa_debug.write_file('populate_run_sources: ' || 'LOG',v_src_sql_str);
1672: END IF;
1673: /* v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
1674: IF P_DEBUG_MODE = 'Y' THEN
1675: pa_debug.write_file('populate_run_sources: ' || 'LOG','Parsing the code' );
1671: pa_debug.write_file('populate_run_sources: ' || 'LOG',v_src_sql_str);
1672: END IF;
1673: /* v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
1674: IF P_DEBUG_MODE = 'Y' THEN
1675: pa_debug.write_file('populate_run_sources: ' || 'LOG','Parsing the code' );
1676: END IF;
1677: DBMS_SQL.PARSE(v_source_csr_id, v_src_sql_str, DBMS_SQL.V7) ;
1678: IF P_DEBUG_MODE = 'Y' THEN
1679: pa_debug.write_file('populate_run_sources: ' || 'LOG','After Parsing the code' );
1675: pa_debug.write_file('populate_run_sources: ' || 'LOG','Parsing the code' );
1676: END IF;
1677: DBMS_SQL.PARSE(v_source_csr_id, v_src_sql_str, DBMS_SQL.V7) ;
1678: IF P_DEBUG_MODE = 'Y' THEN
1679: pa_debug.write_file('populate_run_sources: ' || 'LOG','After Parsing the code' );
1680: END IF;
1681: IF source_lines_rec.project_org_id is NOT NULL THEN
1682: DBMS_SQL.BIND_VARIABLE(v_source_csr_id,':lp_project_org_id',source_lines_rec.project_org_id) ;
1683: END IF ;
1705: DBMS_SQL.DEFINE_COLUMN(v_source_csr_id,1,v_src_project_id ) ;
1706: DBMS_SQL.DEFINE_COLUMN(v_source_csr_id,2,v_src_task_id ) ;
1707: DBMS_SQL.DEFINE_COLUMN(v_source_csr_id,3,v_src_top_task_id ) ;
1708: IF P_DEBUG_MODE = 'Y' THEN
1709: pa_debug.write_file('populate_run_sources: ' || 'LOG','Executing the code' );
1710: END IF;
1711: v_dummy := DBMS_SQL.execute(v_source_csr_id) ;
1712: IF P_DEBUG_MODE = 'Y' THEN
1713: pa_debug.write_file('populate_run_sources: ' || 'LOG','After Executing the code' );
1709: pa_debug.write_file('populate_run_sources: ' || 'LOG','Executing the code' );
1710: END IF;
1711: v_dummy := DBMS_SQL.execute(v_source_csr_id) ;
1712: IF P_DEBUG_MODE = 'Y' THEN
1713: pa_debug.write_file('populate_run_sources: ' || 'LOG','After Executing the code' );
1714: END IF;
1715: LOOP
1716: IF DBMS_SQL.FETCH_ROWS(v_source_csr_id) = 0 THEN
1717: EXIT ;
1719: dbms_sql.column_value(v_source_csr_id,1,v_src_project_id) ;
1720: dbms_sql.column_value(v_source_csr_id,2,v_src_task_id ) ;
1721: dbms_sql.column_value(v_source_csr_id,3,v_src_top_task_id ) ;
1722: IF P_DEBUG_MODE = 'Y' THEN
1723: pa_debug.write_file('populate_run_sources: ' || 'LOG',to_char(v_src_project_id) || to_char(v_src_task_id) ||
1724: to_char(v_src_top_task_id));
1725: END IF;
1726: -- check if lowest level task
1727: IF( pa_task_utils.check_child_exists(v_src_task_id)= 0) THEN
1773: END LOOP; /* alloc_source_lines */
1774: DBMS_SQL.CLOSE_CURSOR(v_source_csr_id ); --added for bug 3799389
1775: IF (p_resource_list_id IS NOT NULL ) THEN
1776: /* -- validate_srce_proj_for_RL ----------------------- */
1777: pa_debug.G_err_stage:= 'VALIDATING RSRCE ASSIGNMENTS TO SRC PROJECTS';
1778: IF P_DEBUG_MODE = 'Y' THEN
1779: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1780: END IF;
1781: FOR run_src_proj_rec IN c_alloc_run_src_projects LOOP
1775: IF (p_resource_list_id IS NOT NULL ) THEN
1776: /* -- validate_srce_proj_for_RL ----------------------- */
1777: pa_debug.G_err_stage:= 'VALIDATING RSRCE ASSIGNMENTS TO SRC PROJECTS';
1778: IF P_DEBUG_MODE = 'Y' THEN
1779: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1780: END IF;
1781: FOR run_src_proj_rec IN c_alloc_run_src_projects LOOP
1782: OPEN C_proj_in_RL( run_src_proj_rec.project_id
1783: , p_resource_list_id
1795: CLOSE C_proj_in_RL;
1796: END LOOP;
1797: END IF; /* p_resource_list_id */
1798: /* restore the old stack */
1799: pa_debug.reset_err_stack;
1800: EXCEPTION
1801: WHEN OTHERS THEN
1802: pa_debug.G_err_code := SQLCODE;
1803: RAISE;
1798: /* restore the old stack */
1799: pa_debug.reset_err_stack;
1800: EXCEPTION
1801: WHEN OTHERS THEN
1802: pa_debug.G_err_code := SQLCODE;
1803: RAISE;
1804: END populate_run_sources;
1805: -- ------------------------------------------------------------
1806: -- insert_alloc_run_targets
1831: x_return_status VARCHAR2(2000) := Null; /* added bug2619977 */
1832: x_msg_count NUMBER := 0 ; /* added bug2619977 */
1833: x_msg_data VARCHAR2(2000) := Null; /* added bug2619977 */
1834: BEGIN
1835: pa_debug.set_err_stack('insert_alloc_run_targets');
1836: pa_debug.G_err_code:= 0;
1837: IF p_dup_targets_flag = 'N'THEN
1838: /* allow insert only if current proj-task does NOT exist */
1839: OPEN target_exists;
1832: x_msg_count NUMBER := 0 ; /* added bug2619977 */
1833: x_msg_data VARCHAR2(2000) := Null; /* added bug2619977 */
1834: BEGIN
1835: pa_debug.set_err_stack('insert_alloc_run_targets');
1836: pa_debug.G_err_code:= 0;
1837: IF p_dup_targets_flag = 'N'THEN
1838: /* allow insert only if current proj-task does NOT exist */
1839: OPEN target_exists;
1840: FETCH target_exists INTO v_dummy;
1859: ,x_msg_count
1860: ,x_msg_data
1861: );
1862: IF P_DEBUG_MODE = 'Y' THEN
1863: pa_debug.write_file('insert_alloc_run_targets: ' || 'LOG','Fetching budget version id for project ['|| to_char(p_project_id) ||
1864: '] Fin plan type ['||to_char(G_basis_fin_plan_Type_id) ||
1865: '] Budget type code ['||G_basis_budget_type_code ||
1866: '] Budget version ['||to_char(x_budget_version_id) ||
1867: '] Return status ['||x_return_status||']' );
1897: , p_last_updated_by
1898: , p_last_update_login );
1899: END IF;
1900: /* restore the old stack */
1901: pa_debug.reset_err_stack;
1902: EXCEPTION
1903: WHEN OTHERS THEN
1904: pa_debug.G_err_code := SQLCODE;
1905: RAISE;
1900: /* restore the old stack */
1901: pa_debug.reset_err_stack;
1902: EXCEPTION
1903: WHEN OTHERS THEN
1904: pa_debug.G_err_code := SQLCODE;
1905: RAISE;
1906: END insert_alloc_run_targets;
1907: -- ------------------------------------------------------------
1908: -- Build_tgt_sql
1940: l_where_str2 VARCHAR2(80) ;
1941: l_where_clause VARCHAR2(1500) ;
1942: v_csr_id INTEGER ;
1943: BEGIN
1944: pa_debug.set_err_stack('build_tgt_sql');
1945: pa_debug.G_err_code:= 0;
1946: l_proj_org_str := ' pp.carrying_out_organization_id = :lp_project_org_id ' ;
1947: l_proj_type_str := ' pp.project_type = :lp_project_type ' ;
1948: l_task_org_str := ' pt.carrying_out_organization_id = :lp_task_org_id ' ;
1941: l_where_clause VARCHAR2(1500) ;
1942: v_csr_id INTEGER ;
1943: BEGIN
1944: pa_debug.set_err_stack('build_tgt_sql');
1945: pa_debug.G_err_code:= 0;
1946: l_proj_org_str := ' pp.carrying_out_organization_id = :lp_project_org_id ' ;
1947: l_proj_type_str := ' pp.project_type = :lp_project_type ' ;
1948: l_task_org_str := ' pt.carrying_out_organization_id = :lp_task_org_id ' ;
1949: l_serv_type_str := ' pt.service_type_code = :lp_service_type_code ' ;
2016: l_where_clause := l_where_str0 || l_where_str1 || ' AND ' || l_where_str2 || ' AND ' || l_expnd_item_str ;
2017: l_where_clause := l_where_clause || ' AND pp.template_flag = ''N''' ;
2018: x_sql_str := l_select_clause || l_from_clause || l_where_clause ;
2019: /* restore the old stack */
2020: pa_debug.reset_err_stack;
2021: EXCEPTION
2022: WHEN OTHERS THEN
2023: pa_debug.G_err_code := SQLCODE;
2024: RAISE ;
2019: /* restore the old stack */
2020: pa_debug.reset_err_stack;
2021: EXCEPTION
2022: WHEN OTHERS THEN
2023: pa_debug.G_err_code := SQLCODE;
2024: RAISE ;
2025: END build_tgt_sql;
2026: -- ------------------------------------------------------------
2027: -- populate_run_targets
2129: WHEN OTHERS THEN
2130: raise;
2131: END check_line_percent;
2132: BEGIN
2133: pa_debug.set_err_stack('populate_run_targets');
2134: pa_debug.G_err_code:= 0;
2135: x_basis_method := p_basis_method;
2136: IF ( p_basis_method = 'FS' OR p_basis_method ='FP') THEN
2137: IF (p_trgt_client_extn = 'Y') THEN
2130: raise;
2131: END check_line_percent;
2132: BEGIN
2133: pa_debug.set_err_stack('populate_run_targets');
2134: pa_debug.G_err_code:= 0;
2135: x_basis_method := p_basis_method;
2136: IF ( p_basis_method = 'FS' OR p_basis_method ='FP') THEN
2137: IF (p_trgt_client_extn = 'Y') THEN
2138: open c_target_line_exists;
2150: close c_target_line_exists;
2151: END IF;/** client_extn = 'Y' **/
2152: END IF; /* end p_basis_method */
2153: IF (p_trgt_client_extn = 'Y') THEN
2154: pa_debug.G_err_stage:= 'READING TRG CLIENT EXTENSION FOR EXCLUDES';
2155: IF P_DEBUG_MODE = 'Y' THEN
2156: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2157: END IF;
2158: pa_client_extn_alloc.target_extn(p_rule_id, v_trg_extn_tabtype,v_status,v_err_message);
2152: END IF; /* end p_basis_method */
2153: IF (p_trgt_client_extn = 'Y') THEN
2154: pa_debug.G_err_stage:= 'READING TRG CLIENT EXTENSION FOR EXCLUDES';
2155: IF P_DEBUG_MODE = 'Y' THEN
2156: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2157: END IF;
2158: pa_client_extn_alloc.target_extn(p_rule_id, v_trg_extn_tabtype,v_status,v_err_message);
2159: IF nvl(v_status,0) <>0 THEN
2160: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
2172: G_fatal_err_found:= TRUE;
2173: alloc_errors( p_rule_id, p_run_id, 'T', 'E',
2174: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id);
2175: IF P_DEBUG_MODE = 'Y' THEN
2176: pa_debug.write_file('populate_run_targets: ' || 'LOG','Client Extension returned an invalid target project: '
2177: ||to_char(v_cx_project_id));
2178: END IF;
2179: v_cx_err_flag := 'Y' ;
2180: END IF;
2183: G_fatal_err_found:= TRUE;
2184: alloc_errors( p_rule_id, p_run_id, 'T', 'E',
2185: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id, v_cx_task_id);
2186: IF P_DEBUG_MODE = 'Y' THEN
2187: pa_debug.write_file('populate_run_targets: ' || 'LOG','Client Extension returned an invalid target task: '
2188: || to_char(v_cx_task_id));
2189: END IF;
2190: v_cx_err_flag := 'Y' ;
2191: END IF;
2228: END IF; /* exclude_flag */
2229: END LOOP;
2230: END IF; /* count */
2231: END IF; /* if p_trgt_client_extn */
2232: pa_debug.G_err_stage:= 'READING TRG LINES FOR EXCLUDES';
2233: IF P_DEBUG_MODE = 'Y' THEN
2234: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2235: END IF;
2236: v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; --added for bug 3799389
2230: END IF; /* count */
2231: END IF; /* if p_trgt_client_extn */
2232: pa_debug.G_err_stage:= 'READING TRG LINES FOR EXCLUDES';
2233: IF P_DEBUG_MODE = 'Y' THEN
2234: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2235: END IF;
2236: v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; --added for bug 3799389
2237: FOR target_lines_rec IN c_alloc_target_lines LOOP
2238: IF (target_lines_rec.exclude_flag = 'Y') THEN
2248: , p_expnd_item_date
2249: , p_limit_target_projects_code
2250: , v_tgt_sql_str ) ;
2251: IF P_DEBUG_MODE = 'Y' THEN
2252: pa_debug.write_file('populate_run_targets: ' || 'LOG',v_tgt_sql_str);
2253: END IF;
2254: /* v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
2255: DBMS_SQL.PARSE(v_target_csr_id, v_tgt_sql_str, DBMS_SQL.V7) ;
2256: IF target_lines_rec.project_org_id is NOT NULL THEN
2294: dbms_sql.column_value(v_target_csr_id,1,v_tgt_project_id) ;
2295: dbms_sql.column_value(v_target_csr_id,2,v_tgt_task_id ) ;
2296: dbms_sql.column_value(v_target_csr_id,3,v_tgt_top_task_id ) ;
2297: IF P_DEBUG_MODE = 'Y' THEN
2298: pa_debug.write_file('populate_run_targets: ' || 'LOG',to_char(v_tgt_project_id) || to_char(v_tgt_task_id) ||
2299: to_char(v_tgt_top_task_id));
2300: END IF;
2301: insert_alloc_run_targets( p_rule_id
2302: , p_run_id
2316: END IF;
2317: END LOOP; /* for c_alloc_target_lines */
2318: DBMS_SQL.CLOSE_CURSOR(v_target_csr_id ); --added for bug 3799389
2319: IF (p_trgt_client_extn = 'Y') THEN
2320: pa_debug.G_err_stage:= 'READING TRG CLIENT EXTENSION FOR INCLUDES';
2321: IF P_DEBUG_MODE = 'Y' THEN
2322: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2323: END IF;
2324: pa_client_extn_alloc.target_extn( p_rule_id, v_trg_extn_tabtype,v_status, v_err_message);
2318: DBMS_SQL.CLOSE_CURSOR(v_target_csr_id ); --added for bug 3799389
2319: IF (p_trgt_client_extn = 'Y') THEN
2320: pa_debug.G_err_stage:= 'READING TRG CLIENT EXTENSION FOR INCLUDES';
2321: IF P_DEBUG_MODE = 'Y' THEN
2322: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2323: END IF;
2324: pa_client_extn_alloc.target_extn( p_rule_id, v_trg_extn_tabtype,v_status, v_err_message);
2325: IF nvl(v_status,0) <>0 THEN
2326: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
2385: END IF; /* v_cx_exclude_flag = N */
2386: END LOOP;
2387: END IF; /* if count>0 */
2388: END IF; /* trg_lnc_extn_flag */
2389: pa_debug.G_err_stage:= 'READING TRG LINES FOR INCLUDES';
2390: IF P_DEBUG_MODE = 'Y' THEN
2391: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2392: END IF;
2393: v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; --added for bug 3799389
2387: END IF; /* if count>0 */
2388: END IF; /* trg_lnc_extn_flag */
2389: pa_debug.G_err_stage:= 'READING TRG LINES FOR INCLUDES';
2390: IF P_DEBUG_MODE = 'Y' THEN
2391: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2392: END IF;
2393: v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; --added for bug 3799389
2394: FOR target_lines_rec IN c_alloc_target_lines LOOP
2395: IF (target_lines_rec.exclude_flag = 'N') THEN
2405: , p_expnd_item_date
2406: , p_limit_target_projects_code
2407: , v_tgt_sql_str ) ;
2408: IF P_DEBUG_MODE = 'Y' THEN
2409: pa_debug.write_file('populate_run_targets: ' || 'LOG',v_tgt_sql_str);
2410: END IF;
2411: /* v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
2412: DBMS_SQL.PARSE(v_target_csr_id, v_tgt_sql_str, DBMS_SQL.V7) ;
2413: IF target_lines_rec.project_org_id is NOT NULL THEN
2451: dbms_sql.column_value(v_target_csr_id,1,v_tgt_project_id) ;
2452: dbms_sql.column_value(v_target_csr_id,2,v_tgt_task_id ) ;
2453: dbms_sql.column_value(v_target_csr_id,3,v_tgt_top_task_id ) ;
2454: IF P_DEBUG_MODE = 'Y' THEN
2455: pa_debug.write_file('populate_run_targets: ' || 'LOG','Exploded proj/tasks: '|| to_char(v_tgt_project_id) ||
2456: to_char(v_tgt_task_id) ||
2457: to_char(v_tgt_top_task_id));
2458: END IF;
2459: /* since always lowest chargeable task, check whether to exclude */
2480: END LOOP;
2481: DBMS_SQL.CLOSE_CURSOR(v_target_csr_id ); --added for bug 3799389
2482: /* -- validate budget_entry_level_code for trg_proj --------------- */
2483: IF( p_bas_budget_entry_method_code IS NOT NULL) THEN
2484: pa_debug.G_err_stage:= 'VALIDATING BASIS BUDGET ENTRY METHOD TO TRG PROJECTS';
2485: IF P_DEBUG_MODE = 'Y' THEN
2486: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2487: END IF;
2488: OPEN c_rule_entry_level_code;
2482: /* -- validate budget_entry_level_code for trg_proj --------------- */
2483: IF( p_bas_budget_entry_method_code IS NOT NULL) THEN
2484: pa_debug.G_err_stage:= 'VALIDATING BASIS BUDGET ENTRY METHOD TO TRG PROJECTS';
2485: IF P_DEBUG_MODE = 'Y' THEN
2486: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2487: END IF;
2488: OPEN c_rule_entry_level_code;
2489: FETCH c_rule_entry_level_code INTO v_rule_level_code;
2490: CLOSE c_rule_entry_level_code;
2501: END LOOP;
2502: END IF; /* p_bas_budget_entry_method_code */
2503: /* -- validate_trg_proj_for_RL ----------------------- */
2504: IF( p_resource_list_id IS NOT NULL ) THEN
2505: pa_debug.G_err_stage:= 'VALIDATING RSRCE ASSIGNMENTS TO TRG PROJECTS';
2506: IF P_DEBUG_MODE = 'Y' THEN
2507: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2508: END IF;
2509: FOR run_trg_proj_rec IN c_alloc_run_trg_projects LOOP
2503: /* -- validate_trg_proj_for_RL ----------------------- */
2504: IF( p_resource_list_id IS NOT NULL ) THEN
2505: pa_debug.G_err_stage:= 'VALIDATING RSRCE ASSIGNMENTS TO TRG PROJECTS';
2506: IF P_DEBUG_MODE = 'Y' THEN
2507: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2508: END IF;
2509: FOR run_trg_proj_rec IN c_alloc_run_trg_projects LOOP
2510: OPEN C_proj_in_RL( run_trg_proj_rec.project_id
2511: , p_resource_list_id
2525: CLOSE C_proj_in_RL;
2526: END LOOP;
2527: END IF;
2528: IF ( x_basis_method IN ( 'FS', 'FP') AND check_line_percent <> 100 ) THEN
2529: pa_debug.G_err_stage:= 'VALIDATING TRG LINE PERCENT';
2530: IF P_DEBUG_MODE = 'Y' THEN
2531: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2532: END IF;
2533: G_fatal_err_found := TRUE;
2527: END IF;
2528: IF ( x_basis_method IN ( 'FS', 'FP') AND check_line_percent <> 100 ) THEN
2529: pa_debug.G_err_stage:= 'VALIDATING TRG LINE PERCENT';
2530: IF P_DEBUG_MODE = 'Y' THEN
2531: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2532: END IF;
2533: G_fatal_err_found := TRUE;
2534: alloc_errors( p_rule_id, p_run_id,
2535: 'T', 'E',
2535: 'T', 'E',
2536: 'PA_AL_LINE_PRCNT_NOT_100');
2537: END IF;
2538: /* restore the old stack */
2539: pa_debug.reset_err_stack;
2540: EXCEPTION
2541: WHEN OTHERS THEN
2542: pa_debug.G_err_code := SQLCODE;
2543: RAISE;
2538: /* restore the old stack */
2539: pa_debug.reset_err_stack;
2540: EXCEPTION
2541: WHEN OTHERS THEN
2542: pa_debug.G_err_code := SQLCODE;
2543: RAISE;
2544: END populate_run_targets;
2545: -- ------------------------------------------------------------
2546: -- insert_alloc_run_GL_det
2558: , p_source_percent IN NUMBER
2559: , p_amount IN NUMBER
2560: , p_eligible_amount IN NUMBER ) IS
2561: BEGIN
2562: pa_debug.set_err_stack('insert_alloc_run_GL_det');
2563: INSERT INTO pa_alloc_run_gl_det(
2564: RUN_ID
2565: , RULE_ID
2566: , LINE_NUM
2587: , p_last_update_login
2588: , p_source_percent
2589: , p_amount
2590: , p_eligible_amount);
2591: pa_debug.reset_err_stack;
2592: EXCEPTION
2593: WHEN OTHERS THEN
2594: pa_debug.G_err_code := SQLCODE;
2595: RAISE;
2590: , p_eligible_amount);
2591: pa_debug.reset_err_stack;
2592: EXCEPTION
2593: WHEN OTHERS THEN
2594: pa_debug.G_err_code := SQLCODE;
2595: RAISE;
2596: END insert_alloc_run_GL_det;
2597: -- ------------------------------------------------------------
2598: -- calculate_src_GL_amounts
2646: v_period_type VARCHAR2(10);
2647: v_amount NUMBER;
2648: v_pool_percent NUMBER;
2649: BEGIN
2650: pa_debug.set_err_stack('calculate_src_GL_amounts');
2651: pa_debug.G_err_stage:= 'Getting gl_account_details';
2652: IF P_DEBUG_MODE = 'Y' THEN
2653: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', pa_debug.G_err_stage);
2654: END IF;
2647: v_amount NUMBER;
2648: v_pool_percent NUMBER;
2649: BEGIN
2650: pa_debug.set_err_stack('calculate_src_GL_amounts');
2651: pa_debug.G_err_stage:= 'Getting gl_account_details';
2652: IF P_DEBUG_MODE = 'Y' THEN
2653: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', pa_debug.G_err_stage);
2654: END IF;
2655: OPEN gl_account_dets;
2649: BEGIN
2650: pa_debug.set_err_stack('calculate_src_GL_amounts');
2651: pa_debug.G_err_stage:= 'Getting gl_account_details';
2652: IF P_DEBUG_MODE = 'Y' THEN
2653: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', pa_debug.G_err_stage);
2654: END IF;
2655: OPEN gl_account_dets;
2656: FETCH gl_account_dets
2657: INTO v_sob_id, v_period_type, v_currency_code ;
2656: FETCH gl_account_dets
2657: INTO v_sob_id, v_period_type, v_currency_code ;
2658: CLOSE gl_account_dets;
2659: IF P_DEBUG_MODE = 'Y' THEN
2660: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_SOB_ID is: '|| to_char(v_sob_id) );
2661: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_currency_code is: '|| v_currency_code );
2662: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_period_type is: '|| v_period_type );
2663: END IF;
2664: OPEN get_pool_percent;
2657: INTO v_sob_id, v_period_type, v_currency_code ;
2658: CLOSE gl_account_dets;
2659: IF P_DEBUG_MODE = 'Y' THEN
2660: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_SOB_ID is: '|| to_char(v_sob_id) );
2661: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_currency_code is: '|| v_currency_code );
2662: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_period_type is: '|| v_period_type );
2663: END IF;
2664: OPEN get_pool_percent;
2665: FETCH get_pool_percent
2658: CLOSE gl_account_dets;
2659: IF P_DEBUG_MODE = 'Y' THEN
2660: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_SOB_ID is: '|| to_char(v_sob_id) );
2661: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_currency_code is: '|| v_currency_code );
2662: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_period_type is: '|| v_period_type );
2663: END IF;
2664: OPEN get_pool_percent;
2665: FETCH get_pool_percent
2666: INTO v_pool_percent;
2665: FETCH get_pool_percent
2666: INTO v_pool_percent;
2667: CLOSE get_pool_percent;
2668: IF P_DEBUG_MODE = 'Y' THEN
2669: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_pool_percent is: '|| to_char(v_pool_percent));
2670: END IF;
2671: FOR gl_source_rec IN gl_sources LOOP
2672: OPEN get_gl_amount( v_sob_id
2673: , gl_source_rec.source_ccid
2676: , p_amount_type );
2677: FETCH get_gl_amount INTO v_amount;
2678: IF (get_gl_amount%NOTFOUND) THEN
2679: IF P_DEBUG_MODE = 'Y' THEN
2680: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'no amount found for the source');
2681: END IF;
2682: v_amount := 0; /* for bug 2154559 */
2683: alloc_errors( p_rule_id, p_run_id, 'S', 'W',
2684: 'PA_AL_NO_GL_BALANCES');
2687: -- Commented the following line as a part of fixing rounding issues.
2688: -- The rounding is done for eligible amount
2689: -- v_amount:= pa_currency.round_currency_amt( NVl(v_amount,0) );
2690: IF P_DEBUG_MODE = 'Y' THEN
2691: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_amount is: '|| to_char(v_amount) );
2692: END IF;
2693: /* then insert into gl_source_det */
2694: insert_alloc_run_GL_det( p_run_id
2695: , p_rule_id
2707: (gl_source_rec.source_percent/100)*
2708: ( v_pool_percent/100))
2709: ) ;
2710: IF P_DEBUG_MODE = 'Y' THEN
2711: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'After insert into alloc_run_Gl' );
2712: END IF;
2713: END LOOP;
2714: /* calculate total GL pool amount */
2715: OPEN gl_eligible_amount ;
2715: OPEN gl_eligible_amount ;
2716: FETCH gl_eligible_amount INTO v_amount;
2717: CLOSE gl_eligible_amount ;
2718: x_gl_src_amount:= NVL(v_amount, 0);
2719: pa_debug.reset_err_stack;
2720: EXCEPTION
2721: WHEN OTHERS THEN
2722: pa_debug.G_err_code:= SQLCODE;
2723: RAISE;
2718: x_gl_src_amount:= NVL(v_amount, 0);
2719: pa_debug.reset_err_stack;
2720: EXCEPTION
2721: WHEN OTHERS THEN
2722: pa_debug.G_err_code:= SQLCODE;
2723: RAISE;
2724: END calculate_src_GL_amounts;
2725: -- ------------------------------------------------------------
2726: -- get_trg_line_proj_task_count
2733: FROM pa_alloc_run_targets
2734: WHERE run_id = p_run_id
2735: AND line_num = p_line_num;
2736: BEGIN
2737: pa_debug.set_err_stack ('Get Proj_task Count For Each Target Line');
2738: OPEN C_get_count;
2739: FETCH C_get_count INTO x_count;
2740: CLOSE C_get_count;
2741: pa_debug.reset_err_stack ;
2737: pa_debug.set_err_stack ('Get Proj_task Count For Each Target Line');
2738: OPEN C_get_count;
2739: FETCH C_get_count INTO x_count;
2740: CLOSE C_get_count;
2741: pa_debug.reset_err_stack ;
2742: return x_count;
2743: EXCEPTION
2744: WHEN OTHERS THEN
2745: return (SQLCODE);
2753: , p_project_id IN NUMBER
2754: , p_task_id IN NUMBER
2755: , p_amount IN NUMBER ) IS
2756: BEGIN
2757: pa_debug.set_err_stack('Insert missing project costs');
2758: INSERT INTO pa_alloc_missing_costs (
2759: RUN_ID
2760: , TYPE_CODE
2761: , CREATION_DATE
2776: , G_last_update_login
2777: , p_project_id
2778: , p_task_id
2779: , p_amount);
2780: pa_debug.reset_err_stack;
2781: EXCEPTION
2782: WHEN OTHERS THEN
2783: pa_debug.G_err_code := SQLCODE;
2784: RAISE;
2779: , p_amount);
2780: pa_debug.reset_err_stack;
2781: EXCEPTION
2782: WHEN OTHERS THEN
2783: pa_debug.G_err_code := SQLCODE;
2784: RAISE;
2785: END insert_missing_costs;
2786: ---------------------------------------------------------------
2787: -- get_sunk_cost
2911: v_quarter_num NUMBER;
2912: v_fiscal_year NUMBER;
2913: v_period_num NUMBER;
2914: BEGIN
2915: pa_debug.set_err_stack('Get_Sunk_Cost');
2916: IF P_DEBUG_MODE = 'Y' THEN
2917: pa_debug.write_file('get_sunk_cost: ' || 'LOG', 'p_amount_type '||p_amount_type);
2918: END IF;
2919: IF( p_amount_type = 'ITD') THEN
2913: v_period_num NUMBER;
2914: BEGIN
2915: pa_debug.set_err_stack('Get_Sunk_Cost');
2916: IF P_DEBUG_MODE = 'Y' THEN
2917: pa_debug.write_file('get_sunk_cost: ' || 'LOG', 'p_amount_type '||p_amount_type);
2918: END IF;
2919: IF( p_amount_type = 'ITD') THEN
2920: v_fiscal_year:= NULL;
2921: v_quarter_num:= NULL;
2936: v_src_sunk_cost :=0;
2937: v_trg_sunk_cost :=0;
2938: /* calculate total src_sunk_cost */
2939: IF P_DEBUG_MODE = 'Y' THEN
2940: pa_debug.write_file('get_sunk_cost: ' || 'LOG',' calculate total src_sunk_cost' );
2941: END IF;
2942: FOR src_sunk_cost_rec IN C_src_sunk_cost( v_fiscal_year
2943: , v_quarter_num
2944: , v_period_num ) LOOP
2942: FOR src_sunk_cost_rec IN C_src_sunk_cost( v_fiscal_year
2943: , v_quarter_num
2944: , v_period_num ) LOOP
2945: IF P_DEBUG_MODE = 'Y' THEN
2946: pa_debug.write_file('get_sunk_cost: ' || 'LOG','src_sunk_cost :'|| to_char(src_sunk_cost_rec.eligible_amount) );
2947: END IF;
2948: if p_src_proj_id is NULL then
2949: insert_missing_costs( p_run_id
2950: ,'S'
2955: v_src_sunk_cost := v_src_sunk_cost + src_sunk_cost_rec.eligible_amount;
2956: END LOOP;
2957: x_src_sunk_cost := nvl(v_src_sunk_cost, 0) ;
2958: IF P_DEBUG_MODE = 'Y' THEN
2959: pa_debug.write_file('get_sunk_cost: ' || 'LOG','v_src_sunk_cost =' ||to_char(v_src_sunk_cost) );
2960: END IF;
2961: /* calculate total trgt_sunk_cost */
2962: if p_src_proj_id is NULL then
2963: IF P_DEBUG_MODE = 'Y' THEN
2960: END IF;
2961: /* calculate total trgt_sunk_cost */
2962: if p_src_proj_id is NULL then
2963: IF P_DEBUG_MODE = 'Y' THEN
2964: pa_debug.write_file('get_sunk_cost: ' || 'LOG',' calculate total tgt_sunk_cost ' || to_char(v_fiscal_year)
2965: || ' ' || to_char(v_quarter_num) || ' '
2966: || to_char(v_period_num) );
2967: END IF;
2968: FOR trg_sunk_cost_rec IN C_trg_sunk_cost( v_fiscal_year
2968: FOR trg_sunk_cost_rec IN C_trg_sunk_cost( v_fiscal_year
2969: , v_quarter_num
2970: , v_period_num ) LOOP
2971: IF P_DEBUG_MODE = 'Y' THEN
2972: pa_debug.write_file('get_sunk_cost: ' || 'LOG','tgt_sunk_cost = '|| to_char(trg_sunk_cost_rec.Total_allocation) );
2973: END IF;
2974: insert_missing_costs( p_run_id
2975: ,'T'
2976: ,trg_sunk_cost_rec.project_id
2979: v_trg_sunk_cost := v_trg_sunk_cost + trg_sunk_cost_rec.Total_allocation;
2980: END LOOP;
2981: x_tgt_sunk_cost := nvl(v_trg_sunk_cost,0) ;
2982: IF P_DEBUG_MODE = 'Y' THEN
2983: pa_debug.write_file('get_sunk_cost: ' || 'LOG','v_tgt_sunk_cost =' ||to_char(x_tgt_sunk_cost) );
2984: END IF;
2985: End if ;
2986: v_tot_sunk_cost := NVL(v_src_sunk_cost,0) - NVL(v_trg_sunk_cost,0);
2987: pa_debug.reset_err_stack;
2983: pa_debug.write_file('get_sunk_cost: ' || 'LOG','v_tgt_sunk_cost =' ||to_char(x_tgt_sunk_cost) );
2984: END IF;
2985: End if ;
2986: v_tot_sunk_cost := NVL(v_src_sunk_cost,0) - NVL(v_trg_sunk_cost,0);
2987: pa_debug.reset_err_stack;
2988: EXCEPTION
2989: WHEN OTHERS THEN
2990: null;
2991: -- return (SQLCODE);
3025: v_fiscal_year NUMBER;
3026: v_period_num NUMBER;
3027: BEGIN
3028: v_prev_amount :=0;
3029: pa_debug.set_err_stack('Get_Previous_Alloc_Amount');
3030: IF( p_amount_type = 'ITD') THEN
3031: v_fiscal_year:= NULL;
3032: v_quarter_num:= NULL;
3033: v_period_num := NULL;
3049: OPEN C_prev_amount ( v_quarter_num, v_fiscal_year, v_period_num );
3050: FETCH C_prev_amount INTO v_prev_amount;
3051: CLOSE C_prev_amount;
3052: IF P_DEBUG_MODE = 'Y' THEN
3053: pa_debug.write_file('get_previous_alloc_amnt: ' || 'LOG','rule_id:run_id:year:quarter:period:project:task:Amt='||
3054: to_char(p_rule_id)||': '|| to_char(p_run_id)||': '||
3055: to_char(v_fiscal_year)||': '|| to_char(v_quarter_num)||': '||
3056: to_char(v_period_num)||': '|| to_char(p_project_id)||': '||
3057: to_char(p_task_id)||':'||to_char(v_prev_amount));
3055: to_char(v_fiscal_year)||': '|| to_char(v_quarter_num)||': '||
3056: to_char(v_period_num)||': '|| to_char(p_project_id)||': '||
3057: to_char(p_task_id)||':'||to_char(v_prev_amount));
3058: END IF;
3059: pa_debug.reset_err_stack;
3060: return nvl(v_prev_amount,0);
3061: EXCEPTION
3062: WHEN NO_DATA_FOUND THEN
3063: return 0;
3118: v_attribute10 VARCHAR2(150);
3119: v_status NUMBER ;
3120: v_err_message VARCHAR2(250);
3121: BEGIN
3122: pa_debug.set_err_stack('Insert Alloc Txn Details');
3123: /* Added if condition to avoid calling client extns for capint if p_rule_id =-1 */
3124: IF nvl(p_rule_id,0) <> -1 Then
3125: --For Capitalized Interest the DFF will be derived outside the
3126: --table handler by calling client extn
3149: ) ;
3150: IF nvl(v_status,0) <> 0 then
3151: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
3152: IF P_DEBUG_MODE = 'Y' THEN
3153: pa_debug.write_file('insert_alloc_txn_details: ' || 'LOG',v_err_message);
3154: END IF;
3155: alloc_errors(p_rule_id, p_run_id, 'R', 'E',v_err_message, TRUE) ;
3156: END IF ;
3157: Elsif p_transaction_type='O' then
3179: ) ;
3180: IF nvl(v_status,0) <> 0 then
3181: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
3182: IF P_DEBUG_MODE = 'Y' THEN
3183: pa_debug.write_file('insert_alloc_txn_details: ' || 'LOG',v_err_message);
3184: END IF;
3185: alloc_errors(p_rule_id, p_run_id, 'R', 'E',v_err_message, TRUE) ;
3186: END IF ;
3187: End if;
3274: ,p_CINT_CURRENT_BASIS_AMT
3275: ,p_REJECTION_CODE
3276: ,p_STATUS_CODE
3277: );
3278: pa_debug.reset_err_stack;
3279: EXCEPTION
3280: WHEN OTHERS THEN
3281: pa_debug.G_err_code := SQLCODE;
3282: RAISE;
3277: );
3278: pa_debug.reset_err_stack;
3279: EXCEPTION
3280: WHEN OTHERS THEN
3281: pa_debug.G_err_code := SQLCODE;
3282: RAISE;
3283: END insert_alloc_txn_details;
3284: -- ------------------------------------------------------------
3285: -- create_target_txns
3377: return (SQLCODE);
3378: -- return 0 ;
3379: END get_basis_factor ;
3380: BEGIN
3381: pa_debug.set_err_stack('Create_Target_txns');
3382: pa_debug.G_err_stage:= 'CREATING TARGET TRANSACTIONS';
3383: IF P_DEBUG_MODE = 'Y' THEN
3384: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3385: pa_debug.write_file('create_target_txns: ' || 'LOG','p_pool_amount : '|| to_char(p_pool_amount));
3378: -- return 0 ;
3379: END get_basis_factor ;
3380: BEGIN
3381: pa_debug.set_err_stack('Create_Target_txns');
3382: pa_debug.G_err_stage:= 'CREATING TARGET TRANSACTIONS';
3383: IF P_DEBUG_MODE = 'Y' THEN
3384: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3385: pa_debug.write_file('create_target_txns: ' || 'LOG','p_pool_amount : '|| to_char(p_pool_amount));
3386: END IF;
3380: BEGIN
3381: pa_debug.set_err_stack('Create_Target_txns');
3382: pa_debug.G_err_stage:= 'CREATING TARGET TRANSACTIONS';
3383: IF P_DEBUG_MODE = 'Y' THEN
3384: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3385: pa_debug.write_file('create_target_txns: ' || 'LOG','p_pool_amount : '|| to_char(p_pool_amount));
3386: END IF;
3387: v_tot_pool_amount:= p_pool_amount;
3388: IF p_allocation_method = 'I' THEN
3381: pa_debug.set_err_stack('Create_Target_txns');
3382: pa_debug.G_err_stage:= 'CREATING TARGET TRANSACTIONS';
3383: IF P_DEBUG_MODE = 'Y' THEN
3384: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3385: pa_debug.write_file('create_target_txns: ' || 'LOG','p_pool_amount : '|| to_char(p_pool_amount));
3386: END IF;
3387: v_tot_pool_amount:= p_pool_amount;
3388: IF p_allocation_method = 'I' THEN
3389: /* if allocation method is Incremental then consider sunk cost */
3397: , v_tgt_sunk_cost
3398: , NULL );
3399: v_sunk_cost :=NVL( v_src_sunk_cost,0) - NVL( v_tgt_sunk_cost,0) ;
3400: IF P_DEBUG_MODE = 'Y' THEN
3401: pa_debug.write_file('create_target_txns: ' || 'LOG','v_src_sunk_cost is: '||to_char(v_src_sunk_cost) );
3402: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tgt_sunk_cost is: '||to_char(v_tgt_sunk_cost) );
3403: pa_debug.write_file('create_target_txns: ' || 'LOG','v_sunk_cost is: '||to_char(v_sunk_cost) );
3404: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3405: END IF;
3398: , NULL );
3399: v_sunk_cost :=NVL( v_src_sunk_cost,0) - NVL( v_tgt_sunk_cost,0) ;
3400: IF P_DEBUG_MODE = 'Y' THEN
3401: pa_debug.write_file('create_target_txns: ' || 'LOG','v_src_sunk_cost is: '||to_char(v_src_sunk_cost) );
3402: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tgt_sunk_cost is: '||to_char(v_tgt_sunk_cost) );
3403: pa_debug.write_file('create_target_txns: ' || 'LOG','v_sunk_cost is: '||to_char(v_sunk_cost) );
3404: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3405: END IF;
3406: v_tot_pool_amount := NVL(v_tot_pool_amount,0) + NVl(v_sunk_cost, 0);
3399: v_sunk_cost :=NVL( v_src_sunk_cost,0) - NVL( v_tgt_sunk_cost,0) ;
3400: IF P_DEBUG_MODE = 'Y' THEN
3401: pa_debug.write_file('create_target_txns: ' || 'LOG','v_src_sunk_cost is: '||to_char(v_src_sunk_cost) );
3402: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tgt_sunk_cost is: '||to_char(v_tgt_sunk_cost) );
3403: pa_debug.write_file('create_target_txns: ' || 'LOG','v_sunk_cost is: '||to_char(v_sunk_cost) );
3404: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3405: END IF;
3406: v_tot_pool_amount := NVL(v_tot_pool_amount,0) + NVl(v_sunk_cost, 0);
3407: IF P_DEBUG_MODE = 'Y' THEN
3400: IF P_DEBUG_MODE = 'Y' THEN
3401: pa_debug.write_file('create_target_txns: ' || 'LOG','v_src_sunk_cost is: '||to_char(v_src_sunk_cost) );
3402: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tgt_sunk_cost is: '||to_char(v_tgt_sunk_cost) );
3403: pa_debug.write_file('create_target_txns: ' || 'LOG','v_sunk_cost is: '||to_char(v_sunk_cost) );
3404: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3405: END IF;
3406: v_tot_pool_amount := NVL(v_tot_pool_amount,0) + NVl(v_sunk_cost, 0);
3407: IF P_DEBUG_MODE = 'Y' THEN
3408: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_after_ is: '||to_char(v_tot_pool_amount) );
3404: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3405: END IF;
3406: v_tot_pool_amount := NVL(v_tot_pool_amount,0) + NVl(v_sunk_cost, 0);
3407: IF P_DEBUG_MODE = 'Y' THEN
3408: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_after_ is: '||to_char(v_tot_pool_amount) );
3409: END IF;
3410: END IF;
3411: IF( p_basis_method = 'S') THEN
3412: /* get count of total number of project/tasks for targets */
3428: END IF; /* end v_count2 */
3429: END IF; /* if p_basis_method */
3430: IF ( p_basis_method in ( 'P','FP','C' )) THEN
3431: IF P_DEBUG_MODE = 'Y' THEN
3432: pa_debug.write_file('create_target_txns: ' || 'LOG','In basis_method'||p_basis_method);
3433: END IF;
3434: v_factor := get_basis_factor( p_run_id,
3435: p_basis_method,
3436: run_target_rec.line_num,
3436: run_target_rec.line_num,
3437: run_target_rec.project_id,
3438: run_target_rec.task_id ) ;
3439: IF P_DEBUG_MODE = 'Y' THEN
3440: pa_debug.write_file('create_target_txns: ' || 'LOG','V_factor is :'|| to_char(v_factor));
3441: END IF;
3442: END IF ;
3443: /* if basis amount is zero for a particular proj-task then show warning */
3444: IF (v_factor = 0 ) THEN
3447: run_target_rec.project_id, run_target_rec.task_id );
3448: END IF;
3449: v_curr_alloc_amount := v_tot_pool_amount * v_factor ;
3450: IF P_DEBUG_MODE = 'Y' THEN
3451: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_current_alloc_amount :'||to_char(v_curr_alloc_amount) );
3452: END IF;
3453: v_tot_alloc_amount := v_curr_alloc_amount;
3454: v_sum_tot_alloc_amt := v_sum_tot_alloc_amt + v_tot_alloc_amount ;
3455: IF (p_allocation_method = 'F') THEN
3476: v_tot_alloc_amount:= pa_currency.round_currency_amt(v_tot_alloc_amount);
3477: v_prev_alloc_amount:= pa_currency.round_currency_amt(v_prev_alloc_amount);
3478: v_curr_alloc_amount:= pa_currency.round_currency_amt(v_curr_alloc_amount);
3479: IF P_DEBUG_MODE = 'Y' THEN
3480: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Amounts after rounding' );
3481: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3482: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3483: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3484: END IF;
3477: v_prev_alloc_amount:= pa_currency.round_currency_amt(v_prev_alloc_amount);
3478: v_curr_alloc_amount:= pa_currency.round_currency_amt(v_curr_alloc_amount);
3479: IF P_DEBUG_MODE = 'Y' THEN
3480: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Amounts after rounding' );
3481: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3482: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3483: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3484: END IF;
3485: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3478: v_curr_alloc_amount:= pa_currency.round_currency_amt(v_curr_alloc_amount);
3479: IF P_DEBUG_MODE = 'Y' THEN
3480: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Amounts after rounding' );
3481: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3482: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3483: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3484: END IF;
3485: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3486: IF P_DEBUG_MODE = 'Y' THEN
3479: IF P_DEBUG_MODE = 'Y' THEN
3480: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Amounts after rounding' );
3481: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3482: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3483: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3484: END IF;
3485: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3486: IF P_DEBUG_MODE = 'Y' THEN
3487: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3481: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3482: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3483: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3484: END IF;
3485: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3486: IF P_DEBUG_MODE = 'Y' THEN
3487: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3488: END IF;
3489: /* insert into alloc_txn_details */
3483: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3484: END IF;
3485: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3486: IF P_DEBUG_MODE = 'Y' THEN
3487: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3488: END IF;
3489: /* insert into alloc_txn_details */
3490: --
3491: -- Bug: 983057 Do not create txn with zero curren alloc amount
3549: /* get the total allocated */
3550: v_net_alloc_amount := NVl(v_net_alloc_amount,0) + NVL(v_curr_alloc_amount,0) ;
3551: END LOOP;
3552: IF P_DEBUG_MODE = 'Y' THEN
3553: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3554: END IF;
3555: pa_debug.G_err_stage:= 'ALLOCATING REMNANT';
3556: IF P_DEBUG_MODE = 'Y' THEN
3557: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3551: END LOOP;
3552: IF P_DEBUG_MODE = 'Y' THEN
3553: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3554: END IF;
3555: pa_debug.G_err_stage:= 'ALLOCATING REMNANT';
3556: IF P_DEBUG_MODE = 'Y' THEN
3557: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3558: END IF;
3559: If p_allocation_method = 'I' then
3553: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3554: END IF;
3555: pa_debug.G_err_stage:= 'ALLOCATING REMNANT';
3556: IF P_DEBUG_MODE = 'Y' THEN
3557: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3558: END IF;
3559: If p_allocation_method = 'I' then
3560: IF( p_amount_type = 'ITD') THEN
3561: v_fiscal_year:= NULL;
3574: v_quarter_num:= p_quarter_num;
3575: v_period_num := p_period_num;
3576: END IF;
3577: IF P_DEBUG_MODE = 'Y' THEN
3578: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Fetching sum of allocated amounts until the current run' );
3579: END IF;
3580: Open c_sum_of_allocated_amts ;
3581: Fetch c_sum_of_allocated_amts into v_sum_alloc_amts ;
3582: if c_sum_of_allocated_amts%NOTFOUND then
3584: end if ;
3585: Close c_sum_of_allocated_amts ;
3586: End If ;
3587: IF P_DEBUG_MODE = 'Y' THEN
3588: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3589: END IF;
3590:
3591: IF nvl(v_net_alloc_amount,0) <> 0 THEN -- added this if condition as part of the bug 13949738
3592: allocate_remnant ( p_run_id, p_pool_amount + v_src_sunk_cost - nvl(v_sum_alloc_amts,0)
3592: allocate_remnant ( p_run_id, p_pool_amount + v_src_sunk_cost - nvl(v_sum_alloc_amts,0)
3593: , v_remnant_amount);
3594: END IF;
3595:
3596: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3597: IF P_DEBUG_MODE = 'Y' THEN
3598: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3599: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3600: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3594: END IF;
3595:
3596: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3597: IF P_DEBUG_MODE = 'Y' THEN
3598: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3599: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3600: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3601: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3602: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3595:
3596: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3597: IF P_DEBUG_MODE = 'Y' THEN
3598: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3599: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3600: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3601: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3602: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3603: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3596: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3597: IF P_DEBUG_MODE = 'Y' THEN
3598: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3599: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3600: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3601: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3602: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3603: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3604: END IF;
3597: IF P_DEBUG_MODE = 'Y' THEN
3598: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3599: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3600: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3601: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3602: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3603: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3604: END IF;
3605: x_curr_alloc_amount := nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0) ;
3598: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3599: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3600: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3601: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3602: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3603: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3604: END IF;
3605: x_curr_alloc_amount := nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0) ;
3606: UPDATE pa_alloc_runs
3599: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3600: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3601: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3602: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3603: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3604: END IF;
3605: x_curr_alloc_amount := nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0) ;
3606: UPDATE pa_alloc_runs
3607: SET total_pool_amount = nvl(p_pool_amount,0)
3609: -- , Missing_source_proj_amt = v_src_sunk_cost -- commented as part of the bug 13949738
3610: , Missing_target_proj_amt = v_tgt_sunk_cost
3611: , Total_allocated_amount = nvl(v_sum_alloc_amts,0) + nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0)-nvl(v_tgt_sunk_cost,0)
3612: WHERE run_id = p_run_id;
3613: pa_debug.reset_err_stack;
3614: EXCEPTION
3615: WHEN OTHERS THEN
3616: pa_debug.G_err_code := SQLCODE;
3617: RAISE;
3612: WHERE run_id = p_run_id;
3613: pa_debug.reset_err_stack;
3614: EXCEPTION
3615: WHEN OTHERS THEN
3616: pa_debug.G_err_code := SQLCODE;
3617: RAISE;
3618: END create_target_txns;
3619: -- ------------------------------------------------------------
3620: -- create_offset_txns
3718: v_err_message VARCHAR2(250);
3719: v_returned_amount NUMBER := 0;
3720: v_offset_extn_tabtype PA_CLIENT_EXTN_ALLOC.ALLOC_OFFSET_TABTYPE;
3721: BEGIN
3722: pa_debug.set_err_stack('Create_Offset_txns');
3723: pa_debug.G_err_stage:= 'CREATING OFFSET TRANSACTIONS';
3724: IF P_DEBUG_MODE = 'Y' THEN
3725: pa_debug.write_file('create_offset_txns: ' || 'LOG', pa_debug.G_err_stage);
3726: END IF;
3719: v_returned_amount NUMBER := 0;
3720: v_offset_extn_tabtype PA_CLIENT_EXTN_ALLOC.ALLOC_OFFSET_TABTYPE;
3721: BEGIN
3722: pa_debug.set_err_stack('Create_Offset_txns');
3723: pa_debug.G_err_stage:= 'CREATING OFFSET TRANSACTIONS';
3724: IF P_DEBUG_MODE = 'Y' THEN
3725: pa_debug.write_file('create_offset_txns: ' || 'LOG', pa_debug.G_err_stage);
3726: END IF;
3727: IF (p_offset_method = 'A') THEN -- Offset Method: Same as source
3721: BEGIN
3722: pa_debug.set_err_stack('Create_Offset_txns');
3723: pa_debug.G_err_stage:= 'CREATING OFFSET TRANSACTIONS';
3724: IF P_DEBUG_MODE = 'Y' THEN
3725: pa_debug.write_file('create_offset_txns: ' || 'LOG', pa_debug.G_err_stage);
3726: END IF;
3727: IF (p_offset_method = 'A') THEN -- Offset Method: Same as source
3728: FOR offset_det_rec IN c_offset_det LOOP
3729: v_tot_offset_amount := offset_det_rec.eligible_amount * (-1);
3807: END IF ;
3808: END LOOP;
3809: ELSIF (p_offset_method = 'B') THEN -- Offset Method: same as source project, CE tasks
3810: IF P_DEBUG_MODE = 'Y' THEN
3811: pa_debug.write_file('create_offset_txns: ' || 'LOG', 'Test offset method B ');
3812: END IF;
3813: FOR offset_proj_sum_rec IN c_offset_proj_sum LOOP
3814: pa_client_extn_alloc.offset_task_extn(p_rule_id, offset_proj_sum_rec.project_id, v_task_id,v_status,v_err_message);
3815: IF nvl(v_status,0) <>0 THEN
3822: G_fatal_err_found:= TRUE;
3823: alloc_errors( p_rule_id, p_run_id, 'O', 'E',
3824: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',NULL, v_task_id);
3825: IF P_DEBUG_MODE = 'Y' THEN
3826: pa_debug.write_file('create_offset_txns: ' || 'LOG','Client Extension returned an invalid offset task: '
3827: || to_char(v_cx_task_id));
3828: END IF;
3829: END IF;
3830: IF P_DEBUG_MODE = 'Y' THEN
3827: || to_char(v_cx_task_id));
3828: END IF;
3829: END IF;
3830: IF P_DEBUG_MODE = 'Y' THEN
3831: pa_debug.write_file('create_offset_txns: ' || 'LOG', 'v_task_id: '||to_char(v_task_id) );
3832: END IF;
3833: v_tot_offset_amount:= offset_proj_sum_rec.eligible_amount * (-1);
3834: IF (p_allocation_method = 'I') THEN /* incremental */
3835: v_prev_offset_amount := get_previous_alloc_amnt( p_rule_id
3853: open C_prev_offset_task_CE( offset_proj_sum_rec.project_id,v_task_id ) ;
3854: Fetch C_prev_offset_task_CE into v_proj_prev_offset_amt;
3855: Close C_prev_offset_task_CE ;
3856: IF P_DEBUG_MODE = 'Y' THEN
3857: pa_debug.write_file('create_offset_txns: ' || 'LOG', 'offset project:Task:total_amount:src_sunk_cost:prev_offset '
3858: ||to_char(offset_proj_sum_rec.project_id)||':'
3859: ||to_char(v_task_id)|| ':'
3860: ||to_char(v_tot_offset_amount)|| ':'
3861: || to_char(v_src_sunk_cost)||':'
4021: END IF;
4022: /* check whether tot_offset_amount retuned is same as what was passed */
4023: FOR I in 1..v_offset_extn_tabtype.count LOOP
4024: IF P_DEBUG_MODE = 'Y' THEN
4025: pa_debug.write_file('create_offset_txns: ' || 'LOG', 'offset project-Task: '||
4026: to_char(v_offset_extn_tabtype(I).project_id)||'-'||
4027: to_char(v_offset_extn_tabtype(I).task_id) || 'Index :'||to_char(I) );
4028: END IF;
4029: v_cx_project_id:=v_offset_extn_tabtype(I).project_id;
4033: G_fatal_err_found:= TRUE;
4034: alloc_errors( p_rule_id, p_run_id, 'O', 'E',
4035: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id);
4036: IF P_DEBUG_MODE = 'Y' THEN
4037: pa_debug.write_file('create_offset_txns: ' || 'LOG','Client Extension returned an invalid offset project: '
4038: ||to_char(v_cx_project_id) );
4039: END IF;
4040: END IF;
4041: IF is_offset_task_valid(v_cx_project_id,v_cx_task_id ) = 'N' THEN
4042: G_fatal_err_found:= TRUE;
4043: alloc_errors( p_rule_id, p_run_id, 'O', 'E',
4044: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id,v_cx_task_id);
4045: IF P_DEBUG_MODE = 'Y' THEN
4046: pa_debug.write_file('create_offset_txns: ' || 'LOG','Client Extension returned an invalid offset task: '
4047: || to_char(v_cx_task_id ));
4048: END IF;
4049: END IF;
4050: END LOOP;
4151: v_quarter_num:= p_quarter_num;
4152: v_period_num := p_period_num;
4153: END IF;
4154: IF P_DEBUG_MODE = 'Y' THEN
4155: pa_debug.write_file('create_offset_txns: ' || 'LOG',' calculate total offset missing cost'
4156: ||to_char(p_fiscal_year) || ' ' || to_char(v_quarter_num)
4157: || ' ' || to_char(v_period_num) );
4158: END IF;
4159: FOR off_sunk_cost_rec IN C_off_sunk_cost( v_fiscal_year
4159: FOR off_sunk_cost_rec IN C_off_sunk_cost( v_fiscal_year
4160: , v_quarter_num
4161: , v_period_num ) LOOP
4162: IF P_DEBUG_MODE = 'Y' THEN
4163: pa_debug.write_file('create_offset_txns: ' || 'LOG','off_sunk_cost = '|| to_char(off_sunk_cost_rec.Total_allocation) );
4164: END IF;
4165: insert_missing_costs( p_run_id
4166: ,'O'
4167: ,off_sunk_cost_rec.project_id
4169: ,off_sunk_cost_rec.Total_allocation);
4170: v_off_sunk_cost := v_off_sunk_cost + nvl(off_sunk_cost_rec.Total_allocation,0) ;
4171: END LOOP;
4172: IF P_DEBUG_MODE = 'Y' THEN
4173: pa_debug.write_file('create_offset_txns: ' || 'LOG','v_off_sunk_cost =' ||to_char(v_off_sunk_cost) );
4174: END IF;
4175: END IF ;
4176: update pa_alloc_runs
4177: set Missing_offset_proj_amt = nvl(v_off_sunk_cost,0) ,
4176: update pa_alloc_runs
4177: set Missing_offset_proj_amt = nvl(v_off_sunk_cost,0) ,
4178: TOTAL_OFFSETTED_AMOUNT = v_sum_tot_offsets
4179: where run_id = p_run_id ;
4180: pa_debug.reset_err_stack;
4181: EXCEPTION
4182: WHEN OTHERS THEN
4183: pa_debug.G_err_code := SQLCODE;
4184: RAISE;
4179: where run_id = p_run_id ;
4180: pa_debug.reset_err_stack;
4181: EXCEPTION
4182: WHEN OTHERS THEN
4183: pa_debug.G_err_code := SQLCODE;
4184: RAISE;
4185: END create_offset_txns;
4186: -- ------------------------------------------------------------
4187: -- allocate_remnant
4208: v_task_id NUMBER;
4209: v_amount NUMBER;
4210: v_remnant NUMBER;
4211: BEGIN
4212: pa_debug.set_err_stack ('allocate_remnant');
4213: OPEN c_amnt_before_remnant;
4214: FETCH c_amnt_before_remnant INTO v_sum_curr_alloc, v_max_curr_alloc ;
4215: CLOSE c_amnt_before_remnant;
4216: IF P_DEBUG_MODE = 'Y' THEN
4213: OPEN c_amnt_before_remnant;
4214: FETCH c_amnt_before_remnant INTO v_sum_curr_alloc, v_max_curr_alloc ;
4215: CLOSE c_amnt_before_remnant;
4216: IF P_DEBUG_MODE = 'Y' THEN
4217: pa_debug.write_file('allocate_remnant: ' || 'LOG', 'Actual allocated amount is:'|| to_char(p_act_alloc_amount));
4218: END IF;
4219: v_remnant:= p_act_alloc_amount - v_sum_curr_alloc;
4220: IF (v_remnant <>0 ) THEN
4221: OPEN c_remnant_proj_task( v_max_curr_alloc );
4229: AND transaction_type='T'
4230: AND project_id = v_project_id
4231: AND task_id = v_task_id;
4232: END IF;
4233: pa_debug.reset_err_stack ;
4234: EXCEPTION
4235: WHEN OTHERS THEN
4236: pa_debug.G_err_code := SQLCODE;
4237: RAISE;
4232: END IF;
4233: pa_debug.reset_err_stack ;
4234: EXCEPTION
4235: WHEN OTHERS THEN
4236: pa_debug.G_err_code := SQLCODE;
4237: RAISE;
4238: END allocate_remnant;
4239: -- ------------------------------------------------------------
4240: -- insert_alloc_runs
4302: , p_ALLOC_RBS_VERSION In Number default NULL
4303: , p_BASIS_RBS_VERSION In Number default NULL
4304: ) IS
4305: BEGIN
4306: pa_debug.set_err_stack('insert_alloc_runs');
4307: If x_run_id is NULL then
4308: Select pa_alloc_runs_s.nextval
4309: Into x_run_id
4310: From dual;
4439: , p_BASIS_RESOURCE_STRUCT_TYPE
4440: , p_ALLOC_RBS_VERSION
4441: , p_BASIS_RBS_VERSION
4442: ) ;
4443: pa_debug.reset_err_stack;
4444: EXCEPTION
4445: WHEN OTHERS THEN
4446: pa_debug.G_err_code:= SQLCODE;
4447: RAISE;
4442: ) ;
4443: pa_debug.reset_err_stack;
4444: EXCEPTION
4445: WHEN OTHERS THEN
4446: pa_debug.G_err_code:= SQLCODE;
4447: RAISE;
4448: END insert_alloc_runs;
4449: -- ------------------------------------------------------------
4450: -- get_fiscalyear_quarter
4878: Begin
4879: ------------------------------------------------------------------
4880: ---- Filling All Excludes And All its childs to The PLSQL table ----
4881: IF P_DEBUG_MODE = 'Y' THEN
4882: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' Inside fetch_all_excludes');
4883: END IF;
4884: v_cnt := 0;
4885: v_child_resource_excl_id.delete;
4886: If Nvl(p_resource_struct_type,'RL') = 'RL' Then
4884: v_cnt := 0;
4885: v_child_resource_excl_id.delete;
4886: If Nvl(p_resource_struct_type,'RL') = 'RL' Then
4887: IF P_DEBUG_MODE = 'Y' THEN
4888: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' In case of RL');
4889: END IF;
4890: Declare
4891: Cursor C_Exc_Chd_Rlm (V_parent_rlm in Number)
4892: Is
4901: Begin
4902: Open C_Exc_RLM;
4903: Loop
4904: IF P_DEBUG_MODE = 'Y' THEN
4905: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' In case of RL 1 ');
4906: END IF;
4907: Resource_List_Member_tab.Delete;
4908: Fetch C_Exc_RLM Bulk Collect Into Resource_List_Member_tab Limit 500;
4909: If Not Resource_List_Member_Tab.Exists(1) Then
4912: For k in 1..Resource_List_Member_tab.count
4913: Loop
4914: Open C_Exc_Chd_Rlm(Resource_List_Member_tab(k));
4915: IF P_DEBUG_MODE = 'Y' THEN
4916: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' RL : Child Member ' || Resource_List_Member_tab(k) );
4917: END IF;
4918: Loop
4919: v_child_resource_excl_id_temp.Delete;
4920: Fetch C_Exc_Chd_Rlm Bulk Collect
4938: Close C_Exc_RLM;
4939: End;
4940: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
4941: IF P_DEBUG_MODE = 'Y' THEN
4942: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' In case of RBS');
4943: END IF;
4944: Declare
4945: Cursor C_Exc_Chd_Rlm_RBS (V_parent_rlm in Number)
4946: Is
4954: Begin
4955: Open C_Exc_RLM;
4956: Loop
4957: IF P_DEBUG_MODE = 'Y' THEN
4958: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' In case of RBS 1 ');
4959: END IF;
4960: Resource_List_Member_tab.Delete;
4961: Fetch C_Exc_RLM Bulk Collect Into Resource_List_Member_tab Limit 500;
4962: If Not Resource_List_Member_Tab.Exists(1) Then
4965: For k in 1..Resource_List_Member_tab.count
4966: Loop
4967: Open C_Exc_Chd_Rlm_RBS(Resource_List_Member_tab(k));
4968: IF P_DEBUG_MODE = 'Y' THEN
4969: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' RBS : Child Member ' || Resource_List_Member_tab(k) );
4970: END IF;
4971: Loop
4972: v_child_resource_excl_id_temp.Delete;
4973: Fetch C_Exc_Chd_Rlm_RBS Bulk Collect
5019: End Is_Excluded_Rlm;
5020: BEGIN
5021: v_counter := 1 ;
5022: IF P_DEBUG_MODE = 'Y' THEN
5023: pa_debug.write_file('populate_RLM_table: ' || 'LOG','Inside populate_RLM');
5024: END IF;/*2564418*/
5025: IF check_rlm_exists(p_rule_id, p_type) = 'N' then
5026: IF P_DEBUG_MODE = 'Y' THEN
5027: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If check_rlm_exists is N');
5023: pa_debug.write_file('populate_RLM_table: ' || 'LOG','Inside populate_RLM');
5024: END IF;/*2564418*/
5025: IF check_rlm_exists(p_rule_id, p_type) = 'N' then
5026: IF P_DEBUG_MODE = 'Y' THEN
5027: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If check_rlm_exists is N');
5028: END IF;/*2564418*/
5029: -- populate inc_rlm_tbl using pa_budget_resources_v for
5030: -- given alloc_rule.alloc_resource_list ;
5031: /*********************** Bug 3149853 starts *****************************
5095: /*********************** Bug 3149853 ends *****************************/
5096: Else
5097: Fetch_all_excludes; /* Added For 3567201 */
5098: IF P_DEBUG_MODE = 'Y' THEN
5099: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If some RLM exists....');
5100: END IF;/*2564418*/
5101: For RLM_REC in C_RLM LOOP
5102: v_rlm_id := RLM_REC.resource_list_member_id ;
5103: If RLM_REC.exclude_flag <> 'Y' then
5101: For RLM_REC in C_RLM LOOP
5102: v_rlm_id := RLM_REC.resource_list_member_id ;
5103: If RLM_REC.exclude_flag <> 'Y' then
5104: IF P_DEBUG_MODE = 'Y' THEN
5105: pa_debug.write_file('populate_RLM_table: ' || 'LOG','Some include exists....');
5106: END IF; /*2564418*/
5107: v_incld_exists := 'Y'; /*2564418 Hence some include is specified*/
5108: If (
5109: (
5117: AND check_child_rlm_exists(v_rlm_id) = 'Y'
5118: )
5119: ) then
5120: IF P_DEBUG_MODE = 'Y' THEN
5121: pa_debug.write_file('populate_RLM_table: ' || 'LOG','A RG is included specifically');
5122: END IF;/*2564418*/
5123: If NVL(p_resource_struct_type,'RL') = 'RL' Then
5124: Open C_RG_RLM;
5125: Elsif NVL(p_resource_struct_type,'RL') = 'RBS' Then
5159: End If;
5160: End If;
5161: End If;
5162: IF P_DEBUG_MODE = 'Y' THEN
5163: pa_debug.write_file('populate_RLM_table: ' || 'LOG','v_counter: '|| to_char(v_counter)||' RLM ID :' || to_char(v_rlm_id ));
5164: END IF;
5165: --- If check_rlm_excluded(p_rule_id, p_type, v_chd_rlm_id ) = 'N' then
5166: /* Check is thru now from PLSQL tables For 3567201 */
5167: /* commented for bug 2211234
5212: );
5213: End if ;
5214: Else
5215: IF P_DEBUG_MODE = 'Y' THEN
5216: pa_debug.write_file('populate_RLM_table: ' || 'LOG','v_counter : ' || to_char(v_counter) );
5217: END IF;
5218: --- If check_rlm_excluded (p_rule_id, p_type, RLM_REC.resource_list_member_id) = 'N' then
5219: /* Check is thru now from PLSQL tables For 3567201 */
5220: /* commented for bug 2211234
5233: --- End if ;
5234: End if ;
5235: Else /* If the RLM is an exclude. 2564418 changes start here */
5236: IF P_DEBUG_MODE = 'Y' THEN
5237: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If an exclude is specified...');
5238: END IF;
5239: If (v_incld_exists = 'N') then /* no include has been specified */
5240: IF P_DEBUG_MODE = 'Y' THEN
5241: pa_debug.write_file('populate_RLM_table: ' || 'LOG','No include has been specified...');
5237: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If an exclude is specified...');
5238: END IF;
5239: If (v_incld_exists = 'N') then /* no include has been specified */
5240: IF P_DEBUG_MODE = 'Y' THEN
5241: pa_debug.write_file('populate_RLM_table: ' || 'LOG','No include has been specified...');
5242: END IF;
5243: /*include all RLM but this one */
5244: /*********************** Bug 3149853 starts *****************************
5245: For RL_RLM_REC in C_RL_RLM LOOP
5244: /*********************** Bug 3149853 starts *****************************
5245: For RL_RLM_REC in C_RL_RLM LOOP
5246: if(v_excld_exists <> 'Y') then
5247: IF P_DEBUG_MODE = 'Y' THEN
5248: pa_debug.write_file('populate_RLM_table: ' || 'LOG','Inserting the other members-- '|| RLM_REC.resource_list_member_id);
5249: END IF;
5250: insert_alloc_run_resources(p_run_id => p_run_id
5251: ,p_rule_id => p_rule_id
5252: ,p_member_type => p_type
5289: End If;
5290: FOR i IN resource_list_member_tab.first..resource_list_member_tab.last
5291: LOOP
5292: IF P_DEBUG_MODE = 'Y' THEN
5293: pa_debug.write_file('populate_RLM_table:'||'LOG','Inserting member-'|| resource_list_member_tab(i));
5294: END IF;
5295: If Not Is_Excluded_Rlm (resource_list_member_tab(i)) Then /* Added For 3567201 */
5296: insert_alloc_run_resources(p_run_id => p_run_id
5297: ,p_rule_id => p_rule_id
5445: v_commit_count NUMBER;
5446: BEGIN
5447: -- project and task amount are inserted based on the amount type (FTYD/QTD/PTD/ITD)
5448: -- and run period and run period type
5449: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_BASIS_RESOURCE procedure';
5450: IF P_DEBUG_MODE = 'Y' THEN
5451: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5452: END IF;
5453: If p_amt_type in ( 'FYTD', 'QTD') then
5447: -- project and task amount are inserted based on the amount type (FTYD/QTD/PTD/ITD)
5448: -- and run period and run period type
5449: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_BASIS_RESOURCE procedure';
5450: IF P_DEBUG_MODE = 'Y' THEN
5451: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5452: END IF;
5453: If p_amt_type in ( 'FYTD', 'QTD') then
5454: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
5455: IF P_DEBUG_MODE = 'Y' THEN
5450: IF P_DEBUG_MODE = 'Y' THEN
5451: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5452: END IF;
5453: If p_amt_type in ( 'FYTD', 'QTD') then
5454: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
5455: IF P_DEBUG_MODE = 'Y' THEN
5456: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5457: END IF;
5458: v_commit_count := 0;
5452: END IF;
5453: If p_amt_type in ( 'FYTD', 'QTD') then
5454: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
5455: IF P_DEBUG_MODE = 'Y' THEN
5456: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5457: END IF;
5458: v_commit_count := 0;
5459: FOR c_projects_rec in c_projects LOOP
5460: IF P_DEBUG_MODE = 'Y' THEN
5457: END IF;
5458: v_commit_count := 0;
5459: FOR c_projects_rec in c_projects LOOP
5460: IF P_DEBUG_MODE = 'Y' THEN
5461: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
5462: END IF;
5463: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------- {
5464: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5465: RUN_ID
5627: End If; ----------------- }
5628: v_commit_count := v_commit_count + sql%rowcount;
5629: IF v_commit_count > 5000 then
5630: IF P_DEBUG_MODE = 'Y' THEN
5631: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
5632: END IF;
5633: COMMIT;
5634: v_commit_count := 0;
5635: END IF;
5635: END IF;
5636: END LOOP;
5637: COMMIT;
5638: Elsif p_amt_type = 'PTD' then
5639: pa_debug.G_err_stage:= 'inserting for PTD';
5640: IF P_DEBUG_MODE = 'Y' THEN
5641: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5642: END IF;
5643: v_commit_count := 0;
5637: COMMIT;
5638: Elsif p_amt_type = 'PTD' then
5639: pa_debug.G_err_stage:= 'inserting for PTD';
5640: IF P_DEBUG_MODE = 'Y' THEN
5641: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5642: END IF;
5643: v_commit_count := 0;
5644: FOR c_projects_rec in c_projects LOOP
5645: IF p_run_period_type = 'PA' THEN
5643: v_commit_count := 0;
5644: FOR c_projects_rec in c_projects LOOP
5645: IF p_run_period_type = 'PA' THEN
5646: IF P_DEBUG_MODE = 'Y' THEN
5647: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5648: END IF;
5649: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------{
5650: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5651: RUN_ID
5922: END IF;
5923: v_commit_count := v_commit_count + sql%rowcount;
5924: IF v_commit_count > 5000 then
5925: IF P_DEBUG_MODE = 'Y' THEN
5926: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
5927: END IF;
5928: COMMIT;
5929: v_commit_count := 0;
5930: END IF;
5930: END IF;
5931: END LOOP;
5932: COMMIT;
5933: Elsif p_amt_type = 'ITD' then
5934: pa_debug.G_err_stage:= 'inserting for ITD';
5935: IF P_DEBUG_MODE = 'Y' THEN
5936: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5937: END IF;
5938: v_commit_count := 0;
5932: COMMIT;
5933: Elsif p_amt_type = 'ITD' then
5934: pa_debug.G_err_stage:= 'inserting for ITD';
5935: IF P_DEBUG_MODE = 'Y' THEN
5936: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5937: END IF;
5938: v_commit_count := 0;
5939: FOR c_projects_rec in c_projects LOOP
5940: /**** OPEN C_proj_start_date(c_projects_rec.project_id) ;
5944: End if ;
5945: CLOSE C_proj_start_date ;
5946: IF v_project_start_date is NOT NULL then **** Commented for bug 2757875 ****/
5947: IF P_DEBUG_MODE = 'Y' THEN
5948: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5949: END IF;
5950: If Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------------{
5951: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5952: RUN_ID
6100: v_commit_count := v_commit_count + sql%rowcount;
6101: -- END IF; **** Commetned for bug 2757875
6102: IF v_commit_count > 5000 then
6103: IF P_DEBUG_MODE = 'Y' THEN
6104: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
6105: END IF;
6106: COMMIT;
6107: v_commit_count := 0;
6108: END IF;
6108: END IF;
6109: END LOOP;
6110: COMMIT;
6111: End If ;
6112: pa_debug.G_err_stage:= 'exiting insert_alloc_basis_resource';
6113: IF P_DEBUG_MODE = 'Y' THEN
6114: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
6115: END IF;
6116: EXCEPTION
6110: COMMIT;
6111: End If ;
6112: pa_debug.G_err_stage:= 'exiting insert_alloc_basis_resource';
6113: IF P_DEBUG_MODE = 'Y' THEN
6114: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
6115: END IF;
6116: EXCEPTION
6117: WHEN OTHERS THEN
6118: RAISE ;
6176: v_rule_pool_percent NUMBER; /* Added for bug 3227783 */
6177: BEGIN
6178: -- project and task amount are inserted based on the amount type (FTYD/QTD/PTD/ITD)
6179: -- and run period and run period type
6180: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_source_RESOURCE procedure';
6181: IF P_DEBUG_MODE = 'Y' THEN
6182: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6183: END IF;
6184: pa_debug.G_err_stage:= 'getting the pool_percent set for the rule.';
6178: -- project and task amount are inserted based on the amount type (FTYD/QTD/PTD/ITD)
6179: -- and run period and run period type
6180: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_source_RESOURCE procedure';
6181: IF P_DEBUG_MODE = 'Y' THEN
6182: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6183: END IF;
6184: pa_debug.G_err_stage:= 'getting the pool_percent set for the rule.';
6185: IF P_DEBUG_MODE = 'Y' THEN
6186: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6180: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_source_RESOURCE procedure';
6181: IF P_DEBUG_MODE = 'Y' THEN
6182: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6183: END IF;
6184: pa_debug.G_err_stage:= 'getting the pool_percent set for the rule.';
6185: IF P_DEBUG_MODE = 'Y' THEN
6186: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6187: END IF;
6188: /* Added for bug 3227783 */
6182: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6183: END IF;
6184: pa_debug.G_err_stage:= 'getting the pool_percent set for the rule.';
6185: IF P_DEBUG_MODE = 'Y' THEN
6186: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6187: END IF;
6188: /* Added for bug 3227783 */
6189: Open c_get_rule_pool_percent ;
6190: FETCH c_get_rule_pool_percent into v_rule_pool_percent;
6189: Open c_get_rule_pool_percent ;
6190: FETCH c_get_rule_pool_percent into v_rule_pool_percent;
6191: CLOSE c_get_rule_pool_percent;
6192: If p_amt_type in ( 'FYTD', 'QTD') then
6193: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
6194: IF P_DEBUG_MODE = 'Y' THEN
6195: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6196: END IF;
6197: v_commit_count := 0;
6191: CLOSE c_get_rule_pool_percent;
6192: If p_amt_type in ( 'FYTD', 'QTD') then
6193: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
6194: IF P_DEBUG_MODE = 'Y' THEN
6195: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6196: END IF;
6197: v_commit_count := 0;
6198: FOR c_projects_rec in c_projects LOOP
6199: IF P_DEBUG_MODE = 'Y' THEN
6196: END IF;
6197: v_commit_count := 0;
6198: FOR c_projects_rec in c_projects LOOP
6199: IF P_DEBUG_MODE = 'Y' THEN
6200: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
6201: END IF;
6202: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------------{
6203: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
6204: RUN_ID
6398: End If; ------------}
6399: v_commit_count := v_commit_count + sql%rowcount;
6400: IF v_commit_count > 5000 then
6401: IF P_DEBUG_MODE = 'Y' THEN
6402: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
6403: END IF;
6404: COMMIT;
6405: v_commit_count := 0;
6406: END IF;
6406: END IF;
6407: END LOOP;
6408: COMMIT;
6409: Elsif p_amt_type = 'PTD' then
6410: pa_debug.G_err_stage:= 'inserting for PTD';
6411: IF P_DEBUG_MODE = 'Y' THEN
6412: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6413: END IF;
6414: v_commit_count := 0;
6408: COMMIT;
6409: Elsif p_amt_type = 'PTD' then
6410: pa_debug.G_err_stage:= 'inserting for PTD';
6411: IF P_DEBUG_MODE = 'Y' THEN
6412: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6413: END IF;
6414: v_commit_count := 0;
6415: FOR c_projects_rec in c_projects LOOP
6416: IF p_run_period_type = 'PA' THEN
6414: v_commit_count := 0;
6415: FOR c_projects_rec in c_projects LOOP
6416: IF p_run_period_type = 'PA' THEN
6417: IF P_DEBUG_MODE = 'Y' THEN
6418: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6419: END IF;
6420: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ------------{
6421: INSERT INTO PA_ALLOC_RUN_source_DET (
6422: RUN_ID
6749: END IF;
6750: v_commit_count := v_commit_count + sql%rowcount;
6751: IF v_commit_count > 5000 then
6752: IF P_DEBUG_MODE = 'Y' THEN
6753: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
6754: END IF;
6755: COMMIT;
6756: v_commit_count := 0;
6757: END IF;
6757: END IF;
6758: END LOOP;
6759: COMMIT;
6760: Elsif p_amt_type = 'ITD' then
6761: pa_debug.G_err_stage:= 'inserting for ITD';
6762: IF P_DEBUG_MODE = 'Y' THEN
6763: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6764: END IF;
6765: v_commit_count := 0;
6759: COMMIT;
6760: Elsif p_amt_type = 'ITD' then
6761: pa_debug.G_err_stage:= 'inserting for ITD';
6762: IF P_DEBUG_MODE = 'Y' THEN
6763: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6764: END IF;
6765: v_commit_count := 0;
6766: FOR c_projects_rec in c_projects LOOP
6767: /**** OPEN C_proj_start_date(c_projects_rec.project_id) ;
6772: CLOSE C_proj_start_date ;
6773: IF v_project_start_date is NOT NULL then
6774: **** Commented for bug 2757875****/
6775: IF P_DEBUG_MODE = 'Y' THEN
6776: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6777: END IF;
6778: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------{
6779: INSERT INTO PA_ALLOC_RUN_source_DET (
6780: RUN_ID
6957: v_commit_count := v_commit_count + sql%rowcount;
6958: -- END IF; **** Commented for bug 2757875
6959: IF v_commit_count > 5000 then
6960: IF P_DEBUG_MODE = 'Y' THEN
6961: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
6962: END IF;
6963: COMMIT;
6964: v_commit_count := 0;
6965: END IF;
6965: END IF;
6966: END LOOP;
6967: COMMIT;
6968: End If ;
6969: pa_debug.G_err_stage:= 'exiting insert_alloc_source_resource';
6970: IF P_DEBUG_MODE = 'Y' THEN
6971: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6972: END IF;
6973: EXCEPTION
6967: COMMIT;
6968: End If ;
6969: pa_debug.G_err_stage:= 'exiting insert_alloc_source_resource';
6970: IF P_DEBUG_MODE = 'Y' THEN
6971: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6972: END IF;
6973: EXCEPTION
6974: WHEN OTHERS THEN
6975: RAISE ;
7303: , p_last_updated_by IN NUMBER
7304: , p_last_update_login IN NUMBER)
7305: IS
7306: BEGIN
7307: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7308: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7309: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7310: RUN_ID
7311: , RULE_ID
7304: , p_last_update_login IN NUMBER)
7305: IS
7306: BEGIN
7307: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7308: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7309: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7310: RUN_ID
7311: , RULE_ID
7312: , LINE_NUM
7336: , p_last_update_date
7337: , p_last_updated_by
7338: , p_last_update_login ) ;
7339: /* restore the old stack */
7340: pa_debug.reset_err_stack;
7341: EXCEPTION
7342: WHEN OTHERS THEN
7343: RAISE;
7344: END insert_alloc_run_src_det;
7453: Where Rule_Id = P_Rule_Id
7454: And Run_Id = P_Run_Id
7455: And Nvl(Exclude_Flag,'N') = 'N';
7456: BEGIN
7457: pa_debug.set_err_stack('Cal_amounts_from_projects') ;
7458: pa_debug.G_err_stage := 'Get Fiscal Year and quarter for run period and amounttype' ;
7459: IF P_DEBUG_MODE = 'Y' THEN
7460: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7461: END IF;
7454: And Run_Id = P_Run_Id
7455: And Nvl(Exclude_Flag,'N') = 'N';
7456: BEGIN
7457: pa_debug.set_err_stack('Cal_amounts_from_projects') ;
7458: pa_debug.G_err_stage := 'Get Fiscal Year and quarter for run period and amounttype' ;
7459: IF P_DEBUG_MODE = 'Y' THEN
7460: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7461: END IF;
7462: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
7456: BEGIN
7457: pa_debug.set_err_stack('Cal_amounts_from_projects') ;
7458: pa_debug.G_err_stage := 'Get Fiscal Year and quarter for run period and amounttype' ;
7459: IF P_DEBUG_MODE = 'Y' THEN
7460: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7461: END IF;
7462: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
7463: v_period_set_name, v_period_year, v_quarter, v_period_num,
7464: v_run_period_end_date) ;
7461: END IF;
7462: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
7463: v_period_set_name, v_period_year, v_quarter, v_period_num,
7464: v_run_period_end_date) ;
7465: pa_debug.G_err_stage := 'Getting start date for given amount type(FYTD/QTD)' ;
7466: IF P_DEBUG_MODE = 'Y' THEN
7467: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7468: END IF;
7469: get_amttype_start_date( p_run_amount_type, v_period_type, v_period_set_name,
7463: v_period_set_name, v_period_year, v_quarter, v_period_num,
7464: v_run_period_end_date) ;
7465: pa_debug.G_err_stage := 'Getting start date for given amount type(FYTD/QTD)' ;
7466: IF P_DEBUG_MODE = 'Y' THEN
7467: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7468: END IF;
7469: get_amttype_start_date( p_run_amount_type, v_period_type, v_period_set_name,
7470: v_run_period_end_date, v_quarter, v_period_year,
7471: p_run_period, v_amttype_start_date) ;
7484: G_last_update_date,
7485: G_last_updated_by, G_last_update_login);
7486: End if ;
7487: If v_resource_list_id is NOT NULL then
7488: pa_debug.G_err_stage := 'Populating the Resource List Member array ' ;
7489: IF P_DEBUG_MODE = 'Y' THEN
7490: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7491: END IF;
7492: /* bug 2211234 - added p_run_id.
7486: End if ;
7487: If v_resource_list_id is NOT NULL then
7488: pa_debug.G_err_stage := 'Populating the Resource List Member array ' ;
7489: IF P_DEBUG_MODE = 'Y' THEN
7490: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7491: END IF;
7492: /* bug 2211234 - added p_run_id.
7493: - removed v_src_rlm_tab
7494: This procedure will now populate records into pa_alloc_run_resources_det */
7510: alloc_errors(p_rule_id, p_run_id, 'S','E', 'PA_AL_NO_INCL_SRC_RESRC',TRUE) ;
7511: end if ;
7512: EXCEPTION
7513: WHEN OTHERS THEN
7514: pa_debug.G_err_stage := 'error during selecting count from pa_alloc_run_resources_det' ;
7515: IF P_DEBUG_MODE = 'Y' THEN
7516: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7517: END IF;
7518: RAISE;
7512: EXCEPTION
7513: WHEN OTHERS THEN
7514: pa_debug.G_err_stage := 'error during selecting count from pa_alloc_run_resources_det' ;
7515: IF P_DEBUG_MODE = 'Y' THEN
7516: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7517: END IF;
7518: RAISE;
7519: END;
7520: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts ' ;
7516: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7517: END IF;
7518: RAISE;
7519: END;
7520: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts ' ;
7521: IF P_DEBUG_MODE = 'Y' THEN
7522: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7523: END IF;
7524: /* added for 2211234 */
7518: RAISE;
7519: END;
7520: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts ' ;
7521: IF P_DEBUG_MODE = 'Y' THEN
7522: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7523: END IF;
7524: /* added for 2211234 */
7525: insert_alloc_source_resource(p_run_id => p_run_id
7526: ,p_rule_id => p_rule_id
7546: v_period_set_name, v_period_year, v_quarter,
7547: v_run_period_end_date,
7548: v_amttype_start_date, v_amount ) ;
7549: IF P_DEBUG_MODE = 'Y' THEN
7550: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_rlm_percent '||to_char(v_rlm_percent));
7551: END IF;
7552: v_pool_amount := v_amount * (nvl(v_rlm_percent,100)/100) *
7553: (nvl(p_pool_percent,100)/100) ;
7554: IF P_DEBUG_MODE = 'Y' THEN
7551: END IF;
7552: v_pool_amount := v_amount * (nvl(v_rlm_percent,100)/100) *
7553: (nvl(p_pool_percent,100)/100) ;
7554: IF P_DEBUG_MODE = 'Y' THEN
7555: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_amount '||to_char(v_amount));
7556: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_pool_amount '||to_char(v_pool_amount));
7557: END IF;
7558: v_pool_amount := pa_currency.round_currency_amt( v_pool_amount );
7559: v_amount := pa_currency.round_currency_amt( v_amount );
7552: v_pool_amount := v_amount * (nvl(v_rlm_percent,100)/100) *
7553: (nvl(p_pool_percent,100)/100) ;
7554: IF P_DEBUG_MODE = 'Y' THEN
7555: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_amount '||to_char(v_amount));
7556: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_pool_amount '||to_char(v_pool_amount));
7557: END IF;
7558: v_pool_amount := pa_currency.round_currency_amt( v_pool_amount );
7559: v_amount := pa_currency.round_currency_amt( v_amount );
7560: insert_alloc_run_src_det(p_rule_id, p_run_id, src_det_rec.line_num,
7567: END LOOP ;
7568: END LOOP ;
7569: */
7570: Else
7571: pa_debug.G_err_stage := 'Processing project/Tasks to get amounts ' ;
7572: IF P_DEBUG_MODE = 'Y' THEN
7573: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7574: END IF;
7575: If p_run_amount_type in ( 'FYTD','QTD') then
7569: */
7570: Else
7571: pa_debug.G_err_stage := 'Processing project/Tasks to get amounts ' ;
7572: IF P_DEBUG_MODE = 'Y' THEN
7573: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7574: END IF;
7575: If p_run_amount_type in ( 'FYTD','QTD') then
7576: For l_get_proj in c_get_proj
7577: Loop
7820: End Loop;
7821: End if ;
7822: End If ;
7823: IF P_DEBUG_MODE = 'Y' THEN
7824: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'Amount type is '|| p_run_amount_type ||'--' || to_char(SQL%ROWCOUNT));
7825: END IF;
7826: pa_debug.G_err_stage := 'Getting pool amount from projects for run'||to_char(p_run_id) ;
7827: IF P_DEBUG_MODE = 'Y' THEN
7828: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7822: End If ;
7823: IF P_DEBUG_MODE = 'Y' THEN
7824: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'Amount type is '|| p_run_amount_type ||'--' || to_char(SQL%ROWCOUNT));
7825: END IF;
7826: pa_debug.G_err_stage := 'Getting pool amount from projects for run'||to_char(p_run_id) ;
7827: IF P_DEBUG_MODE = 'Y' THEN
7828: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7829: END IF;
7830: commit ;
7824: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'Amount type is '|| p_run_amount_type ||'--' || to_char(SQL%ROWCOUNT));
7825: END IF;
7826: pa_debug.G_err_stage := 'Getting pool amount from projects for run'||to_char(p_run_id) ;
7827: IF P_DEBUG_MODE = 'Y' THEN
7828: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7829: END IF;
7830: commit ;
7831:
7832: -- Start: added as part of the bug 13949738
7852: v_missing_amount := src_missing_cost_rec.eligible_amount - v_previous_amount;
7853: IF v_missing_amount <> 0 THEN
7854:
7855: IF P_DEBUG_MODE = 'Y' THEN
7856: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG','src_missing_cost for project: '||src_missing_cost_rec.project_id ||
7857: ' and task: '||src_missing_cost_rec.task_id ||' is :'|| to_char(v_missing_amount) );
7858: END IF;
7859:
7860: insert_missing_costs( p_run_id
7874: OPEN C_get_pool_amount ;
7875: Fetch C_get_pool_amount into x_proj_pool_amount ;
7876: close C_get_pool_amount ;
7877:
7878: pa_debug.reset_err_stack;
7879: EXCEPTION
7880: WHEN OTHERS THEN
7881: RAISE;
7882: END cal_amounts_from_projects ;
7900: and glp.end_date <= p_run_period_end_date
7901: and glp.adjustment_period_flag <> 'Y' /* Added for Bug#2409474 */
7902: order by start_date desc ;
7903: BEGIN
7904: pa_debug.set_err_stack('get_relative_period_name') ;
7905: pa_debug.G_err_stage := 'Fetching the Relative period name' ;
7906: IF P_DEBUG_MODE = 'Y' THEN
7907: pa_debug.write_file('get_relative_period_name: ' || 'LOG', pa_debug.G_err_stage);
7908: END IF;
7901: and glp.adjustment_period_flag <> 'Y' /* Added for Bug#2409474 */
7902: order by start_date desc ;
7903: BEGIN
7904: pa_debug.set_err_stack('get_relative_period_name') ;
7905: pa_debug.G_err_stage := 'Fetching the Relative period name' ;
7906: IF P_DEBUG_MODE = 'Y' THEN
7907: pa_debug.write_file('get_relative_period_name: ' || 'LOG', pa_debug.G_err_stage);
7908: END IF;
7909: v_rel_period := 1 + nvl(p_relative_period,0)* -1 ;
7903: BEGIN
7904: pa_debug.set_err_stack('get_relative_period_name') ;
7905: pa_debug.G_err_stage := 'Fetching the Relative period name' ;
7906: IF P_DEBUG_MODE = 'Y' THEN
7907: pa_debug.write_file('get_relative_period_name: ' || 'LOG', pa_debug.G_err_stage);
7908: END IF;
7909: v_rel_period := 1 + nvl(p_relative_period,0)* -1 ;
7910: v_counter := 1 ;
7911: If v_rel_period > 0 then
7922: else
7923: x_rel_period_name := p_run_period;
7924: End if ;
7925: IF P_DEBUG_MODE = 'Y' THEN
7926: pa_debug.write_file('get_relative_period_name: ' || 'LOG', 'Relative Period is '||x_rel_period_name);
7927: END IF;
7928: pa_debug.reset_err_stack;
7929: EXCEPTION
7930: WHEN OTHERS THEN
7924: End if ;
7925: IF P_DEBUG_MODE = 'Y' THEN
7926: pa_debug.write_file('get_relative_period_name: ' || 'LOG', 'Relative Period is '||x_rel_period_name);
7927: END IF;
7928: pa_debug.reset_err_stack;
7929: EXCEPTION
7930: WHEN OTHERS THEN
7931: RAISE ;
7932: END get_relative_period_name ;
7948: , p_last_updated_by IN NUMBER
7949: , p_last_update_login IN NUMBER)
7950: IS
7951: BEGIN
7952: pa_debug.set_err_stack('insert_alloc_run_basis_det') ;
7953: IF P_DEBUG_MODE = 'Y' THEN
7954: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7955: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7956: END IF;
7950: IS
7951: BEGIN
7952: pa_debug.set_err_stack('insert_alloc_run_basis_det') ;
7953: IF P_DEBUG_MODE = 'Y' THEN
7954: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7955: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7956: END IF;
7957: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7958: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7951: BEGIN
7952: pa_debug.set_err_stack('insert_alloc_run_basis_det') ;
7953: IF P_DEBUG_MODE = 'Y' THEN
7954: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7955: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7956: END IF;
7957: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7958: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7959: RUN_ID
7953: IF P_DEBUG_MODE = 'Y' THEN
7954: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7955: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7956: END IF;
7957: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7958: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7959: RUN_ID
7960: , RULE_ID
7961: , LINE_NUM
7985: , p_last_update_date
7986: , p_last_updated_by
7987: , p_last_update_login ) ;
7988: /* restore the old stack */
7989: pa_debug.reset_err_stack;
7990: EXCEPTION
7991: WHEN OTHERS THEN
7992: pa_debug.reset_err_stack;
7993: RAISE;
7988: /* restore the old stack */
7989: pa_debug.reset_err_stack;
7990: EXCEPTION
7991: WHEN OTHERS THEN
7992: pa_debug.reset_err_stack;
7993: RAISE;
7994: END insert_alloc_run_basis_det ;
7995: -- ============================================================
7996: -- cal_proj_basis_amounts
8110: BEGIN
8111: v_basis_method := p_basis_method ;
8112: v_resource_list_id := p_basis_RL_id ;
8113: v_sum_tgt_pct := 0 ;
8114: pa_debug.set_err_stack('Cal_proj_basis_amounts') ;
8115: If p_basis_method = 'C' then
8116: pa_debug.G_err_stage := 'Call basis client extension';
8117: IF P_DEBUG_MODE = 'Y' THEN
8118: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8112: v_resource_list_id := p_basis_RL_id ;
8113: v_sum_tgt_pct := 0 ;
8114: pa_debug.set_err_stack('Cal_proj_basis_amounts') ;
8115: If p_basis_method = 'C' then
8116: pa_debug.G_err_stage := 'Call basis client extension';
8117: IF P_DEBUG_MODE = 'Y' THEN
8118: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8119: END IF;
8120: For run_targets_rec in C_run_targets LOOP
8114: pa_debug.set_err_stack('Cal_proj_basis_amounts') ;
8115: If p_basis_method = 'C' then
8116: pa_debug.G_err_stage := 'Call basis client extension';
8117: IF P_DEBUG_MODE = 'Y' THEN
8118: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8119: END IF;
8120: For run_targets_rec in C_run_targets LOOP
8121: pa_client_extn_alloc.basis_extn(p_rule_id, run_targets_rec.project_id,
8122: run_targets_rec.task_id, v_amount,v_status,v_err_message) ;
8134: Open C_tgt_line_pct ;
8135: Fetch C_tgt_line_pct into v_sum_tgt_pct ;
8136: Close C_tgt_line_pct;
8137: elsif p_basis_method in ( 'P', 'FP') then
8138: pa_debug.G_err_stage := 'Call get_fiscalyear_quarter';
8139: IF P_DEBUG_MODE = 'Y' THEN
8140: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8141: END IF;
8142: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
8136: Close C_tgt_line_pct;
8137: elsif p_basis_method in ( 'P', 'FP') then
8138: pa_debug.G_err_stage := 'Call get_fiscalyear_quarter';
8139: IF P_DEBUG_MODE = 'Y' THEN
8140: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8141: END IF;
8142: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
8143: v_period_set_name,
8144: v_period_year, v_quarter, v_period_num,
8142: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
8143: v_period_set_name,
8144: v_period_year, v_quarter, v_period_num,
8145: v_run_period_end_date) ;
8146: pa_debug.G_err_stage := 'Calling get_relative_period_name' ;
8147: IF P_DEBUG_MODE = 'Y' THEN
8148: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8149: END IF;
8150: get_relative_period_name(v_period_set_name, v_period_type, v_run_period_end_date,
8144: v_period_year, v_quarter, v_period_num,
8145: v_run_period_end_date) ;
8146: pa_debug.G_err_stage := 'Calling get_relative_period_name' ;
8147: IF P_DEBUG_MODE = 'Y' THEN
8148: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8149: END IF;
8150: get_relative_period_name(v_period_set_name, v_period_type, v_run_period_end_date,
8151: p_run_period, p_basis_rel_period, v_rel_period_name ) ;
8152: pa_debug.G_err_stage := 'calling get_fiscalyear_quarter for relative period' ;
8148: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8149: END IF;
8150: get_relative_period_name(v_period_set_name, v_period_type, v_run_period_end_date,
8151: p_run_period, p_basis_rel_period, v_rel_period_name ) ;
8152: pa_debug.G_err_stage := 'calling get_fiscalyear_quarter for relative period' ;
8153: IF P_DEBUG_MODE = 'Y' THEN
8154: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8155: END IF;
8156: get_fiscalyear_quarter(p_run_period_type, v_rel_period_name, v_period_type,
8150: get_relative_period_name(v_period_set_name, v_period_type, v_run_period_end_date,
8151: p_run_period, p_basis_rel_period, v_rel_period_name ) ;
8152: pa_debug.G_err_stage := 'calling get_fiscalyear_quarter for relative period' ;
8153: IF P_DEBUG_MODE = 'Y' THEN
8154: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8155: END IF;
8156: get_fiscalyear_quarter(p_run_period_type, v_rel_period_name, v_period_type,
8157: v_period_set_name,
8158: v_period_year, v_quarter, v_period_num,
8156: get_fiscalyear_quarter(p_run_period_type, v_rel_period_name, v_period_type,
8157: v_period_set_name,
8158: v_period_year, v_quarter, v_period_num,
8159: v_rel_period_end_date) ;
8160: pa_debug.G_err_stage := 'calling get_amttype_start_date';
8161: IF P_DEBUG_MODE = 'Y' THEN
8162: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8163: END IF;
8164: get_amttype_start_date( p_basis_amt_type, v_period_type,
8158: v_period_year, v_quarter, v_period_num,
8159: v_rel_period_end_date) ;
8160: pa_debug.G_err_stage := 'calling get_amttype_start_date';
8161: IF P_DEBUG_MODE = 'Y' THEN
8162: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8163: END IF;
8164: get_amttype_start_date( p_basis_amt_type, v_period_type,
8165: v_period_set_name, v_rel_period_end_date,
8166: v_quarter, v_period_year, p_run_period, v_amttype_start_date) ;
8164: get_amttype_start_date( p_basis_amt_type, v_period_type,
8165: v_period_set_name, v_rel_period_end_date,
8166: v_quarter, v_period_year, p_run_period, v_amttype_start_date) ;
8167: IF P_DEBUG_MODE = 'Y' THEN
8168: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','amttype start date = '|| to_char(v_amttype_start_date));
8169: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','rel_period_end_date = '|| to_char(v_rel_period_end_date));
8170: END IF;
8171: If v_resource_list_id is NOT NULL then
8172: pa_debug.G_err_stage := 'Populating the Resource List Member array for basis ' ;
8165: v_period_set_name, v_rel_period_end_date,
8166: v_quarter, v_period_year, p_run_period, v_amttype_start_date) ;
8167: IF P_DEBUG_MODE = 'Y' THEN
8168: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','amttype start date = '|| to_char(v_amttype_start_date));
8169: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','rel_period_end_date = '|| to_char(v_rel_period_end_date));
8170: END IF;
8171: If v_resource_list_id is NOT NULL then
8172: pa_debug.G_err_stage := 'Populating the Resource List Member array for basis ' ;
8173: IF P_DEBUG_MODE = 'Y' THEN
8168: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','amttype start date = '|| to_char(v_amttype_start_date));
8169: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','rel_period_end_date = '|| to_char(v_rel_period_end_date));
8170: END IF;
8171: If v_resource_list_id is NOT NULL then
8172: pa_debug.G_err_stage := 'Populating the Resource List Member array for basis ' ;
8173: IF P_DEBUG_MODE = 'Y' THEN
8174: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8175: END IF;
8176: /* Bug 2211234 - added p_run_id.
8170: END IF;
8171: If v_resource_list_id is NOT NULL then
8172: pa_debug.G_err_stage := 'Populating the Resource List Member array for basis ' ;
8173: IF P_DEBUG_MODE = 'Y' THEN
8174: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8175: END IF;
8176: /* Bug 2211234 - added p_run_id.
8177: - removed v_basis_rlm_tab
8178: this procedure will now insert into pa_alloc_run_resources_det */
8183: p_basis_resource_struct_type ,
8184: p_basis_rbs_version_id ,
8185: p_basis_category
8186: ) ;
8187: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts';
8188: IF P_DEBUG_MODE = 'Y' THEN
8189: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8190: END IF;
8191: /* major changes done for actuals for performance improvements. No more a call to get_alloc_amount
8185: p_basis_category
8186: ) ;
8187: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts';
8188: IF P_DEBUG_MODE = 'Y' THEN
8189: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8190: END IF;
8191: /* major changes done for actuals for performance improvements. No more a call to get_alloc_amount
8192: is made and bulk inserts are done in new procedure added insert_alloc_basis_resource
8193: These changes are not done for budgets as in budgets the volume of data should not be that high.
8235: ,p_basis_resource_struct_Type => p_basis_resource_struct_type);
8236: /***2211234 - insert_budget_basis_resource will take care of this.
8237: *** FOR I in 1.. v_basis_rlm_tab.count LOOP
8238: *** v_rlm_id := v_basis_rlm_tab (I).resource_list_member_id ;
8239: *** pa_debug.write_file('LOG','Resource list member : '|| to_char(v_rlm_id) );
8240: ***
8241: *** FOR run_targets_rec in C_run_targets LOOP
8242: *** get_budget_amounts( p_run_period_type, p_basis_bal_type,
8243: *** run_targets_rec.project_id,
8244: *** run_targets_rec.task_id,
8245: *** v_resource_list_id, v_rlm_id,
8246: *** p_budget_type_code, v_amttype_start_date,
8247: *** v_rel_period_end_date, v_amount) ;
8248: *** pa_debug.write_file('LOG','get_budget_amounts '||'project: '
8249: *** ||to_char( run_targets_rec.project_id)||'
8250: *** task: '|| to_char( run_targets_rec.task_id)|| '
8251: *** Amt:'|| to_char(v_amount) );
8252: ***
8264: END IF ;
8265: COMMIT;
8266: Else
8267: If p_basis_category = 'A' then
8268: pa_debug.G_err_stage := 'Processing project/Tasks to get basis amounts' ;
8269: IF P_DEBUG_MODE = 'Y' THEN
8270: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8271: END IF;
8272: If p_basis_amt_type in ( 'FYTD','QTD') then
8266: Else
8267: If p_basis_category = 'A' then
8268: pa_debug.G_err_stage := 'Processing project/Tasks to get basis amounts' ;
8269: IF P_DEBUG_MODE = 'Y' THEN
8270: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8271: END IF;
8272: If p_basis_amt_type in ( 'FYTD','QTD') then
8273: IF P_DEBUG_MODE = 'Y' THEN
8274: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','p_basis_amt_type' ||p_basis_amt_type);
8270: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8271: END IF;
8272: If p_basis_amt_type in ( 'FYTD','QTD') then
8273: IF P_DEBUG_MODE = 'Y' THEN
8274: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','p_basis_amt_type' ||p_basis_amt_type);
8275: END IF;
8276: For l_get_proj in c_get_proj
8277: Loop
8278: Insert into pa_alloc_run_basis_det (
8512: End Loop;
8513: End if ;
8514: Else -- Processing the Budget amounts ...
8515: IF P_DEBUG_MODE = 'Y' THEN
8516: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Inserting in basis_det from budgets');
8517: END IF;
8518: if p_basis_amt_type = 'ITD' then
8519: Insert into pa_alloc_run_basis_det(
8520: rule_id, run_id, line_num, project_id, task_id,
8710: and parb.line_num = part.line_num
8711: and parb.amount <> 0);
8712: END IF;
8713: IF P_DEBUG_MODE = 'Y' THEN
8714: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', 'No of Records Inserted in basis details'||
8715: to_char(SQL%ROWCOUNT));
8716: END IF;
8717: pa_debug.G_err_stage := 'Calculating basis percent' ;
8718: IF P_DEBUG_MODE = 'Y' THEN
8713: IF P_DEBUG_MODE = 'Y' THEN
8714: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', 'No of Records Inserted in basis details'||
8715: to_char(SQL%ROWCOUNT));
8716: END IF;
8717: pa_debug.G_err_stage := 'Calculating basis percent' ;
8718: IF P_DEBUG_MODE = 'Y' THEN
8719: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8720: END IF;
8721: pa_debug.G_err_stage := 'Getting Basis total for Method:'||p_basis_method ;
8715: to_char(SQL%ROWCOUNT));
8716: END IF;
8717: pa_debug.G_err_stage := 'Calculating basis percent' ;
8718: IF P_DEBUG_MODE = 'Y' THEN
8719: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8720: END IF;
8721: pa_debug.G_err_stage := 'Getting Basis total for Method:'||p_basis_method ;
8722: IF P_DEBUG_MODE = 'Y' THEN
8723: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8717: pa_debug.G_err_stage := 'Calculating basis percent' ;
8718: IF P_DEBUG_MODE = 'Y' THEN
8719: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8720: END IF;
8721: pa_debug.G_err_stage := 'Getting Basis total for Method:'||p_basis_method ;
8722: IF P_DEBUG_MODE = 'Y' THEN
8723: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8724: END IF;
8725: If ((p_basis_method = 'P') or (p_basis_method ='C' and v_sum_tgt_pct = 0) ) then
8719: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8720: END IF;
8721: pa_debug.G_err_stage := 'Getting Basis total for Method:'||p_basis_method ;
8722: IF P_DEBUG_MODE = 'Y' THEN
8723: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8724: END IF;
8725: If ((p_basis_method = 'P') or (p_basis_method ='C' and v_sum_tgt_pct = 0) ) then
8726: OPEN C_tot_basis_amt ;
8727: FETCH C_tot_basis_Amt INTO v_tot_basis_amt , v_max_basis_amt, v_tot_basis_rec; /* 1900331 */
8734: If nvl(v_tot_basis_amt,0) = 0 and nvl(v_max_basis_amt,0) <> 0 then /* 1900331 */
8735: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_ZERO_BASIS',TRUE) ;
8736: Else
8737: IF P_DEBUG_MODE = 'Y' THEN
8738: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Basis_amount :'||to_char(v_tot_basis_amt) );
8739: END IF;
8740: End if;
8741: Close C_tot_basis_amt ;
8742: if nvl(v_tot_basis_amt,0) <> 0 then /* added for 1900331 */
8753: WHERE run_id = p_run_id ;
8754: */
8755: end if;
8756: IF P_DEBUG_MODE = 'Y' THEN
8757: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','No of Records Updated : '|| to_char(SQL%ROWCOUNT));
8758: END IF;
8759: Elsif ((p_basis_method = 'FP') or (p_basis_method ='C' and v_sum_tgt_pct = 100)) then
8760: OPEN C_line_basis_amt;
8761: LOOP
8762: Fetch C_line_basis_amt into v_line_num, v_line_percent, v_line_basis_amt, v_line_max_amt, v_line_count; /* 1900331 */
8763: EXIT when c_line_basis_amt%NOTFOUND ;
8764: If ( nvl(v_line_basis_amt,0) = 0 and nvl(v_line_percent,0) > 0 and nvl(v_line_max_amt,0) <> 0 ) then /* 1900331 */
8765: IF P_DEBUG_MODE = 'Y' THEN
8766: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','basis amount is zero for target line '||
8767: to_char(v_line_num) );
8768: END IF;
8769: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_LINE_BASIS_AMT_IS_ZERO',TRUE) ;
8770: End if;
8785: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_NO_BASIS_FOUND',TRUE) ;
8786: End If ;
8787: Close C_line_basis_amt ;
8788: End If ;
8789: pa_debug.reset_err_stack;
8790: EXCEPTION
8791: WHEN OTHERS THEN
8792: -- pa_debug.reset_err_stack;
8793: RAISE;
8788: End If ;
8789: pa_debug.reset_err_stack;
8790: EXCEPTION
8791: WHEN OTHERS THEN
8792: -- pa_debug.reset_err_stack;
8793: RAISE;
8794: END cal_proj_basis_amounts ;
8795: /* following procedure is obsoleted (not in use after fix for 2211234 */
8796: /***-- ============================================================
8846: *** If p_run_period_type = 'PA' then
8847: *** OPEN C_budget_amt_by_pa_period ;
8848: *** FETCH C_budget_amt_by_pa_period into x_amount ;
8849: *** If C_budget_amt_by_pa_period%NOTFOUND then
8850: *** pa_debug.write_file('LOG','No data found');
8851: *** x_amount := 0 ;
8852: *** End if ;
8853: *** CLOSE C_budget_amt_by_pa_period ;
8854: ***
8893: where rule_id = p_rule_id;
8894: v_commit_count NUMBER ;
8895: v_do_commit VARCHAR2(1);
8896: BEGIN
8897: pa_debug.G_err_stage:= 'INSIDE CLEAN_UP_TARGETS_FOR_ACTUALS procedure';
8898: IF P_DEBUG_MODE = 'Y' THEN
8899: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8900: END IF;
8901: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8895: v_do_commit VARCHAR2(1);
8896: BEGIN
8897: pa_debug.G_err_stage:= 'INSIDE CLEAN_UP_TARGETS_FOR_ACTUALS procedure';
8898: IF P_DEBUG_MODE = 'Y' THEN
8899: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8900: END IF;
8901: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8902: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8903: IF P_DEBUG_MODE = 'Y' THEN
8898: IF P_DEBUG_MODE = 'Y' THEN
8899: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8900: END IF;
8901: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8902: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8903: IF P_DEBUG_MODE = 'Y' THEN
8904: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8905: END IF;
8906: v_commit_count := 0;
8900: END IF;
8901: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8902: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8903: IF P_DEBUG_MODE = 'Y' THEN
8904: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8905: END IF;
8906: v_commit_count := 0;
8907: IF p_basis_method = 'P' THEN
8908: SAVEPOINT delete_unwanted_targets;
8918: and pp.period_name = pta.pa_period
8919: and pp.end_date between p_amttype_start_date
8920: and p_run_period_end_date);
8921: IF P_DEBUG_MODE = 'Y' THEN
8922: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
8923: END IF;
8924: BEGIN
8925: select 'Y'
8926: into v_do_commit
8936: to do spread evenly.
8937: */
8938: COMMIT;
8939: IF P_DEBUG_MODE = 'Y' THEN
8940: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
8941: END IF;
8942: EXCEPTION
8943: WHEN NO_DATA_FOUND THEN
8944: /* switch the basis method to 'S' in case all records are deleted.
8945: We need to do rollback also
8946: */
8947: ROLLBACK TO delete_unwanted_targets;
8948: IF P_DEBUG_MODE = 'Y' THEN
8949: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
8950: END IF;
8951: p_basis_method := 'S';
8952: END;
8953: elsif p_basis_method = 'FP' then
8967: and pp.end_date between p_amttype_start_date
8968: and p_run_period_end_date);
8969:
8970: IF P_DEBUG_MODE = 'Y' THEN
8971: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
8972: END IF;
8973: BEGIN
8974: select 'Y'
8975: into v_do_commit
8987: No switch of basis method can be done in case of FP.
8988: */
8989: COMMIT;
8990: IF P_DEBUG_MODE = 'Y' THEN
8991: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
8992: END IF;
8993: EXCEPTION
8994: WHEN NO_DATA_FOUND THEN
8995: ROLLBACK TO delete_unwanted_targets;
8993: EXCEPTION
8994: WHEN NO_DATA_FOUND THEN
8995: ROLLBACK TO delete_unwanted_targets;
8996: IF P_DEBUG_MODE = 'Y' THEN
8997: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
8998: END IF;
8999: END;
9000: end loop;
9001: END IF;
8999: END;
9000: end loop;
9001: END IF;
9002: ELSIF p_amt_type = 'PTD' THEN
9003: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for PTD';
9004: IF P_DEBUG_MODE = 'Y' THEN
9005: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9006: END IF;
9007: v_commit_count := 0;
9001: END IF;
9002: ELSIF p_amt_type = 'PTD' THEN
9003: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for PTD';
9004: IF P_DEBUG_MODE = 'Y' THEN
9005: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9006: END IF;
9007: v_commit_count := 0;
9008: IF p_basis_method = 'P' THEN
9009: IF p_run_period_type = 'PA' THEN
9007: v_commit_count := 0;
9008: IF p_basis_method = 'P' THEN
9009: IF p_run_period_type = 'PA' THEN
9010: IF P_DEBUG_MODE = 'Y' THEN
9011: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
9012: END IF;
9013: SAVEPOINT delete_unwanted_targets;
9014: DELETE FROM pa_alloc_run_targets part
9015: WHERE part.run_id = p_run_id
9022: and pta.pa_period = p_period
9023: and rownum = 1
9024: );
9025: IF P_DEBUG_MODE = 'Y' THEN
9026: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
9027: END IF;
9028: BEGIN
9029: select 'Y'
9030: into v_do_commit
9040: to do spread evenly.
9041: */
9042: COMMIT;
9043: IF P_DEBUG_MODE = 'Y' THEN
9044: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
9045: END IF;
9046: EXCEPTION
9047: WHEN NO_DATA_FOUND THEN
9048: /* switch the basis method to 'S' in case all records are deleted.
9049: We need to do rollback also
9050: */
9051: ROLLBACK TO delete_unwanted_targets;
9052: IF P_DEBUG_MODE = 'Y' THEN
9053: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
9054: END IF;
9055: p_basis_method := 'S';
9056: END;
9057: ELSE /* if p_run_period_type = GL */
9055: p_basis_method := 'S';
9056: END;
9057: ELSE /* if p_run_period_type = GL */
9058: IF P_DEBUG_MODE = 'Y' THEN
9059: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = GL');
9060: END IF;
9061: SAVEPOINT delete_unwanted_targets;
9062:
9063: /*For Bug 5403833*/
9073: and pp.period_name = pta.pa_period
9074: and pp.gl_period_name = p_period);
9075:
9076: IF P_DEBUG_MODE = 'Y' THEN
9077: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
9078: END IF;
9079: BEGIN
9080: select 'Y'
9081: into v_do_commit
9090: If all the records are deleted then we will need these records
9091: to do spread evenly.
9092: */
9093: IF P_DEBUG_MODE = 'Y' THEN
9094: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
9095: END IF;
9096: COMMIT;
9097: EXCEPTION
9098: WHEN NO_DATA_FOUND THEN
9100: we need to do rollback also
9101: */
9102: ROLLBACK TO delete_unwanted_targets;
9103: IF P_DEBUG_MODE = 'Y' THEN
9104: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
9105: END IF;
9106: p_basis_method := 'S';
9107: END;
9108: END IF; /* p_run_period_type = 'PA' */
9108: END IF; /* p_run_period_type = 'PA' */
9109: elsif p_basis_method = 'FP' then
9110: IF p_run_period_type = 'PA' THEN
9111: IF P_DEBUG_MODE = 'Y' THEN
9112: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
9113: END IF;
9114: FOR c_target_lines_rec in c_target_lines LOOP
9115: SAVEPOINT delete_unwanted_targets;
9116: DELETE FROM pa_alloc_run_targets part
9125: and pta.pa_period = p_period
9126: and rownum = 1
9127: );
9128: IF P_DEBUG_MODE = 'Y' THEN
9129: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
9130: END IF;
9131: BEGIN
9132: select 'Y'
9133: into v_do_commit
9145: No switch of basis method can be done in case of FP.
9146: */
9147: COMMIT;
9148: IF P_DEBUG_MODE = 'Y' THEN
9149: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
9150: END IF;
9151: EXCEPTION
9152: WHEN NO_DATA_FOUND THEN
9153: ROLLBACK TO delete_unwanted_targets;
9151: EXCEPTION
9152: WHEN NO_DATA_FOUND THEN
9153: ROLLBACK TO delete_unwanted_targets;
9154: IF P_DEBUG_MODE = 'Y' THEN
9155: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
9156: END IF;
9157: END;
9158: end loop;
9159: ELSE /* p_run_period_type = 'GL' */
9157: END;
9158: end loop;
9159: ELSE /* p_run_period_type = 'GL' */
9160: IF P_DEBUG_MODE = 'Y' THEN
9161: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = GL');
9162: END IF;
9163: FOR c_target_lines_rec in c_target_lines LOOP
9164: SAVEPOINT delete_unwanted_targets;
9165: /*For Bug 5403833 */
9175: and pp.period_name = pta.pa_period
9176: and pp.gl_period_name = p_period
9177: );
9178: IF P_DEBUG_MODE = 'Y' THEN
9179: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
9180: END IF;
9181: BEGIN
9182: select 'Y'
9183: into v_do_commit
9195: No switch of basis method can be done in case of FP.
9196: */
9197: COMMIT;
9198: IF P_DEBUG_MODE = 'Y' THEN
9199: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
9200: END IF;
9201: EXCEPTION
9202: WHEN NO_DATA_FOUND THEN
9203: ROLLBACK TO delete_unwanted_targets;
9201: EXCEPTION
9202: WHEN NO_DATA_FOUND THEN
9203: ROLLBACK TO delete_unwanted_targets;
9204: IF P_DEBUG_MODE = 'Y' THEN
9205: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
9206: END IF;
9207: END;
9208: end loop;
9209: END IF; /* p_run_period_type = 'GL' */
9211: ELSIF p_amt_type = 'ITD' THEN
9212: /* For ITD let's consider all periods in pa_periods table rather than
9213: doing this delete for each project id in the targets table. Hence do
9214: not use pa_periods table in this case. */
9215: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9216: IF P_DEBUG_MODE = 'Y' THEN
9217: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9218: END IF;
9219: v_commit_count := 0;
9213: doing this delete for each project id in the targets table. Hence do
9214: not use pa_periods table in this case. */
9215: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9216: IF P_DEBUG_MODE = 'Y' THEN
9217: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9218: END IF;
9219: v_commit_count := 0;
9220: IF p_basis_method = 'P' THEN
9221: SAVEPOINT delete_unwanted_targets;
9229: and pta.task_id = part.task_id
9230: and rownum = 1
9231: );
9232: IF P_DEBUG_MODE = 'Y' THEN
9233: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
9234: END IF;
9235: BEGIN
9236: select 'Y'
9237: into v_do_commit
9246: If all the records are deleted then we will need these records
9247: to do spread evenly.
9248: */
9249: IF P_DEBUG_MODE = 'Y' THEN
9250: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
9251: END IF;
9252: COMMIT;
9253: EXCEPTION
9254: WHEN NO_DATA_FOUND THEN
9256: we need to do rollback also
9257: */
9258: ROLLBACK TO delete_unwanted_targets;
9259: IF P_DEBUG_MODE = 'Y' THEN
9260: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
9261: END IF;
9262: p_basis_method := 'S';
9263: END;
9264: elsif p_basis_method = 'FP' then
9298: END;
9299: end loop;
9300: END IF;
9301: END IF ;
9302: pa_debug.G_err_stage:= 'exiting clean_up_targets_for_actuals';
9303: IF P_DEBUG_MODE = 'Y' THEN
9304: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9305: END IF;
9306: EXCEPTION
9300: END IF;
9301: END IF ;
9302: pa_debug.G_err_stage:= 'exiting clean_up_targets_for_actuals';
9303: IF P_DEBUG_MODE = 'Y' THEN
9304: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9305: END IF;
9306: EXCEPTION
9307: WHEN OTHERS THEN
9308: RAISE ;
9313: PROCEDURE lock_rule( p_rule_id IN NUMBER
9314: ,p_run_id IN NUMBER )
9315: IS
9316: BEGIN
9317: pa_debug.G_err_stage := 'Acquiring lock on the rule ' ;
9318: IF P_DEBUG_MODE = 'Y' THEN
9319: pa_debug.write_file('lock_rule: ' || 'LOG',pa_debug.G_err_stage);
9320: END IF;
9321: --Added this If and the else part for capital project changes
9315: IS
9316: BEGIN
9317: pa_debug.G_err_stage := 'Acquiring lock on the rule ' ;
9318: IF P_DEBUG_MODE = 'Y' THEN
9319: pa_debug.write_file('lock_rule: ' || 'LOG',pa_debug.G_err_stage);
9320: END IF;
9321: --Added this If and the else part for capital project changes
9322: IF p_rule_id <> -1 THEN
9323: If pa_debug.Acquire_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9319: pa_debug.write_file('lock_rule: ' || 'LOG',pa_debug.G_err_stage);
9320: END IF;
9321: --Added this If and the else part for capital project changes
9322: IF p_rule_id <> -1 THEN
9323: If pa_debug.Acquire_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9324: G_fatal_err_found:= TRUE;
9325: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9326: G_created_by, G_last_update_date,
9327: G_last_updated_by, G_last_update_login,
9327: G_last_updated_by, G_last_update_login,
9328: 'R', 'E', NULL, NULL, 'PA_AL_CANT_ACQUIRE_LOCK');
9329: end if;
9330: ELSE
9331: If pa_debug.Acquire_user_lock( 'PA_CINT_'||to_char(p_run_id)) <> 0 then
9332: G_fatal_err_found:= TRUE;
9333: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9334: G_created_by, G_last_update_date,
9335: G_last_updated_by, G_last_update_login,
9346: PROCEDURE unlock_rule( p_rule_id IN NUMBER
9347: ,p_run_id IN NUMBER )
9348: IS
9349: BEGIN
9350: pa_debug.G_err_stage := 'unlock the rule ' ;
9351: IF P_DEBUG_MODE = 'Y' THEN
9352: pa_debug.write_file('unlock_rule: ' || 'LOG',pa_debug.G_err_stage);
9353: END IF;
9354: IF p_rule_id <> -1 THEN
9348: IS
9349: BEGIN
9350: pa_debug.G_err_stage := 'unlock the rule ' ;
9351: IF P_DEBUG_MODE = 'Y' THEN
9352: pa_debug.write_file('unlock_rule: ' || 'LOG',pa_debug.G_err_stage);
9353: END IF;
9354: IF p_rule_id <> -1 THEN
9355: If pa_debug.Release_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9356: G_fatal_err_found:= TRUE;
9351: IF P_DEBUG_MODE = 'Y' THEN
9352: pa_debug.write_file('unlock_rule: ' || 'LOG',pa_debug.G_err_stage);
9353: END IF;
9354: IF p_rule_id <> -1 THEN
9355: If pa_debug.Release_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9356: G_fatal_err_found:= TRUE;
9357: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9358: G_created_by, G_last_update_date,
9359: G_last_updated_by, G_last_update_login,
9359: G_last_updated_by, G_last_update_login,
9360: 'R', 'E', NULL, NULL, 'PA_AL_LOCK_RELEASE_FAILED');
9361: end if;
9362: ELSE
9363: If pa_debug.Release_user_lock( 'PA_CINT_'||to_char(p_run_id)) <> 0 then
9364: G_fatal_err_found:= TRUE;
9365: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9366: G_created_by, G_last_update_date,
9367: G_last_updated_by, G_last_update_login,
9438:
9439: --Declared this variable for Capital project Changes
9440: l_transaction_source pa_lookups.meaning%TYPE;
9441: BEGIN
9442: pa_debug.set_err_stack('Release_alloc_txns') ;
9443: pa_debug.G_err_stage := 'Release_alloc_txns' ;
9444: IF P_DEBUG_MODE = 'Y' THEN
9445: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9446: END IF;
9439: --Declared this variable for Capital project Changes
9440: l_transaction_source pa_lookups.meaning%TYPE;
9441: BEGIN
9442: pa_debug.set_err_stack('Release_alloc_txns') ;
9443: pa_debug.G_err_stage := 'Release_alloc_txns' ;
9444: IF P_DEBUG_MODE = 'Y' THEN
9445: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9446: END IF;
9447: -- Init_who_cols ;
9441: BEGIN
9442: pa_debug.set_err_stack('Release_alloc_txns') ;
9443: pa_debug.G_err_stage := 'Release_alloc_txns' ;
9444: IF P_DEBUG_MODE = 'Y' THEN
9445: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9446: END IF;
9447: -- Init_who_cols ;
9448: x_errbuf := NULL ;
9449: --Included this block for Capital project changes
9454: END IF;
9455: -- No need lock the rule here. Allocation_run procedure is called in the report (PAXALRUN)
9456: -- and this procedure locks the rule.
9457: -- lock_rule(p_rule_id, p_run_id) ;
9458: pa_debug.G_err_stage := 'Inserting records in Interface table ' ;
9459: IF P_DEBUG_MODE = 'Y' THEN
9460: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9461: END IF;
9462: --- Get info about the run -----------------------
9456: -- and this procedure locks the rule.
9457: -- lock_rule(p_rule_id, p_run_id) ;
9458: pa_debug.G_err_stage := 'Inserting records in Interface table ' ;
9459: IF P_DEBUG_MODE = 'Y' THEN
9460: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9461: END IF;
9462: --- Get info about the run -----------------------
9463: OPEN C_run;
9464: FETCH C_run INTO run_rec ;
9506: alloc_errors(p_rule_id, p_run_id, 'R', 'E','PA_AL_RELEASE_FAILED',TRUE,'N') ;
9507: END IF;
9508: end if ;
9509: IF P_DEBUG_MODE = 'Y' THEN
9510: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Inserting records in pa_transaction_interface_all');
9511: END IF;
9512: v_batch_name := substr( to_char(p_run_id), 1, 10) ;
9513: Open C_org_id ;
9514: Fetch C_org_id into v_org_id ;
9525: -- v_expnd_comment :='CAPITALIZED INTEREST ';
9526: /* end of bug 3041022 transalation issue */
9527: END IF;
9528: IF P_DEBUG_MODE = 'Y' THEN
9529: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Exp item Description : '||v_expnd_comment );
9530: END IF;
9531: ---- Validate the Items inserted in the Interface table --------
9532: --- We don't do this since transaction import will take care of this
9533: --- If we call validate item then we have to maintain that.
9645: where patd.run_id = p_run_id
9646: and patd.project_id = pp.project_id
9647: and patd.task_id = pt.task_id ;
9648: IF P_DEBUG_MODE = 'Y' THEN
9649: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', to_char(SQL%ROWCOUNT)||' Records inserted');
9650: END IF;
9651: Commit ;
9652: --- Call import procedure to import the records ------
9653: Select pa_interface_id_s.nextval
9653: Select pa_interface_id_s.nextval
9654: into v_interface_id
9655: from dual ;
9656: IF P_DEBUG_MODE = 'Y' THEN
9657: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Calling Transaction Import ..' );
9658: END IF;
9659: /* Adding exception handling for bug 2749043 */
9660: BEGIN
9661: pa_trx_import.import1( l_transaction_source -- Changed this for capital project changes
9665: , NULL ) ;
9666: EXCEPTION
9667: WHEN OTHERS THEN
9668: IF P_DEBUG_MODE = 'Y' THEN
9669: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Error occurred in Transaction Import' );
9670: END IF;
9671: END;
9672: --- Update txn_details table with import info ---------
9673: IF P_DEBUG_MODE = 'Y' THEN
9670: END IF;
9671: END;
9672: --- Update txn_details table with import info ---------
9673: IF P_DEBUG_MODE = 'Y' THEN
9674: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Updating the transactions with import results');
9675: END IF;
9676: update pa_alloc_txn_details patd
9677: set ( status_code
9678: , rejection_code
9687: and pti.transaction_source = l_transaction_source -- Changed this for capital project changes
9688: and pti.batch_name = v_batch_name )
9689: where run_id = p_run_id ;
9690: IF P_DEBUG_MODE = 'Y' THEN
9691: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', to_char(SQL%ROWCOUNT)||' Records updated');
9692: END IF;
9693:
9694: /**** added for bug 6243121 ***/
9695:
9723: When NO_DATA_FOUND then
9724: v_import_failed := 'N' ;
9725: END;
9726: IF P_DEBUG_MODE = 'Y' THEN
9727: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'v_import_failed :' ||v_import_failed );
9728: END IF;
9729: v_tgt_exp_group := v_batch_name||run_rec.target_exp_type_class||to_char(v_interface_id);
9730: v_off_exp_group := v_batch_name||run_rec.offset_exp_type_class||to_char(v_interface_id);
9731: If v_import_failed ='Y' then
9757: ---- Release Lock on the rule_name ---------------------
9758: -- No need unlock the rule here. Allocation_run procedure is called in the report (PAXALRUN)
9759: -- and this procedure locks and unlocks the rule.
9760: -- unlock_rule(p_rule_id, p_run_id) ;
9761: pa_debug.reset_err_stack ;
9762: EXCEPTION
9763: When OTHERS then
9764: RAISE ;
9765: END Release_alloc_txns ;
9783: v_num_reversed NUMBER ;
9784: v_num_rejected NUMBER ;
9785: v_return_code VARCHAR2(30) ;
9786: BEGIN
9787: -- pa_debug.G_process := 'SQL' ;
9788: pa_debug.Init_err_stack('Start') ;
9789: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9790: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9791: IF P_DEBUG_MODE = 'Y' THEN
9784: v_num_rejected NUMBER ;
9785: v_return_code VARCHAR2(30) ;
9786: BEGIN
9787: -- pa_debug.G_process := 'SQL' ;
9788: pa_debug.Init_err_stack('Start') ;
9789: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9790: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9791: IF P_DEBUG_MODE = 'Y' THEN
9792: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9785: v_return_code VARCHAR2(30) ;
9786: BEGIN
9787: -- pa_debug.G_process := 'SQL' ;
9788: pa_debug.Init_err_stack('Start') ;
9789: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9790: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9791: IF P_DEBUG_MODE = 'Y' THEN
9792: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9793: END IF;
9786: BEGIN
9787: -- pa_debug.G_process := 'SQL' ;
9788: pa_debug.Init_err_stack('Start') ;
9789: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9790: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9791: IF P_DEBUG_MODE = 'Y' THEN
9792: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9793: END IF;
9794: Init_who_cols ;
9788: pa_debug.Init_err_stack('Start') ;
9789: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9790: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9791: IF P_DEBUG_MODE = 'Y' THEN
9792: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9793: END IF;
9794: Init_who_cols ;
9795: lock_rule(p_rule_id, p_run_id) ;
9796: /*added for bug#2357646 */
9803: return;
9804: end if;
9805: /*end of fix for bug#2357646 */
9806: ---- Check for the group name and call reverse group ------
9807: pa_debug.G_err_stage := 'Checking for expenditure groups' ;
9808: IF P_DEBUG_MODE = 'Y' THEN
9809: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', pa_debug.G_err_stage);
9810: END IF;
9811: OPEN C_run ;
9805: /*end of fix for bug#2357646 */
9806: ---- Check for the group name and call reverse group ------
9807: pa_debug.G_err_stage := 'Checking for expenditure groups' ;
9808: IF P_DEBUG_MODE = 'Y' THEN
9809: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', pa_debug.G_err_stage);
9810: END IF;
9811: OPEN C_run ;
9812: Fetch C_run into run_rec ;
9813: Close C_run ;
9820: , v_return_code
9821: , 'RELEASED' ) ;
9822: If nvl(v_return_code,'0') <> '0' then
9823: IF P_DEBUG_MODE = 'Y' THEN
9824: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9825: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9826: to_char(v_num_rejected) );
9827: END IF;
9828: -- pa_debug.raise_error('-20010',v_return_code) ;
9821: , 'RELEASED' ) ;
9822: If nvl(v_return_code,'0') <> '0' then
9823: IF P_DEBUG_MODE = 'Y' THEN
9824: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9825: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9826: to_char(v_num_rejected) );
9827: END IF;
9828: -- pa_debug.raise_error('-20010',v_return_code) ;
9829: else
9824: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9825: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9826: to_char(v_num_rejected) );
9827: END IF;
9828: -- pa_debug.raise_error('-20010',v_return_code) ;
9829: else
9830: IF P_DEBUG_MODE = 'Y' THEN
9831: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed :'||
9832: to_char(v_num_reversed) );
9827: END IF;
9828: -- pa_debug.raise_error('-20010',v_return_code) ;
9829: else
9830: IF P_DEBUG_MODE = 'Y' THEN
9831: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed :'||
9832: to_char(v_num_reversed) );
9833: END IF;
9834: end if ;
9835: If run_rec.offset_exp_group is NOT NULL then
9843: , v_return_code
9844: , 'RELEASED' ) ;
9845: if nvl(v_return_code,'0') <> '0' then
9846: IF P_DEBUG_MODE = 'Y' THEN
9847: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9848: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9849: to_char(v_num_rejected) );
9850: END IF;
9851: pa_debug.raise_error('-20010',v_return_code) ;
9844: , 'RELEASED' ) ;
9845: if nvl(v_return_code,'0') <> '0' then
9846: IF P_DEBUG_MODE = 'Y' THEN
9847: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9848: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9849: to_char(v_num_rejected) );
9850: END IF;
9851: pa_debug.raise_error('-20010',v_return_code) ;
9852: else
9847: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9848: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9849: to_char(v_num_rejected) );
9850: END IF;
9851: pa_debug.raise_error('-20010',v_return_code) ;
9852: else
9853: IF P_DEBUG_MODE = 'Y' THEN
9854: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed :'||
9855: to_char(v_num_reversed) );
9850: END IF;
9851: pa_debug.raise_error('-20010',v_return_code) ;
9852: else
9853: IF P_DEBUG_MODE = 'Y' THEN
9854: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed :'||
9855: to_char(v_num_reversed) );
9856: END IF;
9857: end if ;
9858: End if;
9870: x_errbuf := v_return_code ;
9871: end if ;
9872: ---- Release Lock on the rule_name ---------------------
9873: unlock_rule(p_rule_id, p_run_id) ;
9874: pa_debug.reset_err_stack ;
9875: EXCEPTION
9876: WHEN OTHERS THEN
9877: --Added this code for capital project changes
9878: BEGIN
9891: PROCEDURE Delete_alloc_txns( p_rule_id IN NUMBER
9892: ,p_run_id IN NUMBER)
9893: IS
9894: BEGIN
9895: -- pa_debug.G_process := 'SQL' ;
9896: pa_debug.Init_err_stack('Start') ;
9897: pa_debug.set_err_stack('Delete_alloc_txns') ;
9898: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9899: IF P_DEBUG_MODE = 'Y' THEN
9892: ,p_run_id IN NUMBER)
9893: IS
9894: BEGIN
9895: -- pa_debug.G_process := 'SQL' ;
9896: pa_debug.Init_err_stack('Start') ;
9897: pa_debug.set_err_stack('Delete_alloc_txns') ;
9898: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9899: IF P_DEBUG_MODE = 'Y' THEN
9900: pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9893: IS
9894: BEGIN
9895: -- pa_debug.G_process := 'SQL' ;
9896: pa_debug.Init_err_stack('Start') ;
9897: pa_debug.set_err_stack('Delete_alloc_txns') ;
9898: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9899: IF P_DEBUG_MODE = 'Y' THEN
9900: pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9901: END IF;
9894: BEGIN
9895: -- pa_debug.G_process := 'SQL' ;
9896: pa_debug.Init_err_stack('Start') ;
9897: pa_debug.set_err_stack('Delete_alloc_txns') ;
9898: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9899: IF P_DEBUG_MODE = 'Y' THEN
9900: pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9901: END IF;
9902: Init_who_cols ;
9896: pa_debug.Init_err_stack('Start') ;
9897: pa_debug.set_err_stack('Delete_alloc_txns') ;
9898: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9899: IF P_DEBUG_MODE = 'Y' THEN
9900: pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9901: END IF;
9902: Init_who_cols ;
9903: lock_rule(p_rule_id, p_run_id) ;
9904: -- Delete Transactions from pa_alloc_txn_details
9983: Delete from pa_alloc_runs
9984: where run_id = p_run_id ;
9985: ---- Release Lock on the rule_name ---------------------
9986: unlock_rule(p_rule_id , p_run_id) ;
9987: pa_debug.reset_err_stack ;
9988: EXCEPTION
9989: WHEN OTHERS THEN
9990: RAISE ;
9991: END Delete_alloc_txns ;
10010: l_return_status VARCHAR2(1);
10011: l_msg_data VARCHAR2(1000);
10012: l_msg_count NUMBER :=0;
10013: BEGIN
10014: -- pa_debug.G_process := 'SQL' ;
10015: -- v_debug_mode := NVL(p_debug_mode, 'Y');
10016: --v_process_mode := NVL(p_process_mode, 'SQL');
10017: pa_debug.Init_err_stack('Start') ;
10018: v_debug_mode := 'Y';
10013: BEGIN
10014: -- pa_debug.G_process := 'SQL' ;
10015: -- v_debug_mode := NVL(p_debug_mode, 'Y');
10016: --v_process_mode := NVL(p_process_mode, 'SQL');
10017: pa_debug.Init_err_stack('Start') ;
10018: v_debug_mode := 'Y';
10019: v_process_mode := 'PLSQL';
10020: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
10021: pa_debug.set_err_stack('Delete_alloc_run') ;
10016: --v_process_mode := NVL(p_process_mode, 'SQL');
10017: pa_debug.Init_err_stack('Start') ;
10018: v_debug_mode := 'Y';
10019: v_process_mode := 'PLSQL';
10020: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
10021: pa_debug.set_err_stack('Delete_alloc_run') ;
10022: pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
10023: IF P_DEBUG_MODE = 'Y' THEN
10024: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10017: pa_debug.Init_err_stack('Start') ;
10018: v_debug_mode := 'Y';
10019: v_process_mode := 'PLSQL';
10020: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
10021: pa_debug.set_err_stack('Delete_alloc_run') ;
10022: pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
10023: IF P_DEBUG_MODE = 'Y' THEN
10024: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10025: END IF;
10018: v_debug_mode := 'Y';
10019: v_process_mode := 'PLSQL';
10020: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
10021: pa_debug.set_err_stack('Delete_alloc_run') ;
10022: pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
10023: IF P_DEBUG_MODE = 'Y' THEN
10024: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10025: END IF;
10026: Init_who_cols ;
10020: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
10021: pa_debug.set_err_stack('Delete_alloc_run') ;
10022: pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
10023: IF P_DEBUG_MODE = 'Y' THEN
10024: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10025: END IF;
10026: Init_who_cols ;
10027: FOR run_rec IN get_run_id LOOP
10028: l_run_id := run_rec.run_id;
10025: END IF;
10026: Init_who_cols ;
10027: FOR run_rec IN get_run_id LOOP
10028: l_run_id := run_rec.run_id;
10029: pa_debug.G_err_stage := 'Delete Rule Id' || To_char(p_rule_id) ;
10030: IF P_DEBUG_MODE = 'Y' THEN
10031: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10032: END IF;
10033: pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
10027: FOR run_rec IN get_run_id LOOP
10028: l_run_id := run_rec.run_id;
10029: pa_debug.G_err_stage := 'Delete Rule Id' || To_char(p_rule_id) ;
10030: IF P_DEBUG_MODE = 'Y' THEN
10031: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10032: END IF;
10033: pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
10034: IF P_DEBUG_MODE = 'Y' THEN
10035: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10029: pa_debug.G_err_stage := 'Delete Rule Id' || To_char(p_rule_id) ;
10030: IF P_DEBUG_MODE = 'Y' THEN
10031: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10032: END IF;
10033: pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
10034: IF P_DEBUG_MODE = 'Y' THEN
10035: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10036: END IF;
10037: --Delete all the txn source details when this api is called in
10031: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10032: END IF;
10033: pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
10034: IF P_DEBUG_MODE = 'Y' THEN
10035: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10036: END IF;
10037: --Delete all the txn source details when this api is called in
10038: --the context of Capitalized Interest
10039: IF p_rule_id = -1 THEN
10043: ,x_msg_data => l_msg_data
10044: ,x_msg_count => l_msg_count);
10045: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
10046: IF P_DEBUG_MODE = 'Y' THEN
10047: pa_debug.G_err_stage := 'delte cint sources errored out' ;
10048: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10049: END IF;
10050: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
10051: END IF;
10044: ,x_msg_count => l_msg_count);
10045: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
10046: IF P_DEBUG_MODE = 'Y' THEN
10047: pa_debug.G_err_stage := 'delte cint sources errored out' ;
10048: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
10049: END IF;
10050: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
10051: END IF;
10052: END IF;
10134: where run_id = l_run_id ;
10135: ---- Release Lock on the rule_name ---------------------
10136: --unlock_rule(p_rule_id , l_run_id) ;
10137: END LOOP;
10138: pa_debug.reset_err_stack ;
10139: EXCEPTION
10140: WHEN OTHERS THEN
10141: RAISE ;
10142: END Delete_alloc_run ;
10352: l_module_name VARCHAR2(100);
10353: BEGIN
10354: x_msg_count := 0;
10355: x_return_status := FND_API.G_RET_STS_SUCCESS;
10356: l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10357: l_module_name := 'release_capint_txns';
10358: pa_debug.set_curr_function( p_function => 'release_capint_txns',
10359: p_debug_mode => l_debug_mode );
10360: IF l_debug_mode = 'Y' THEN
10354: x_msg_count := 0;
10355: x_return_status := FND_API.G_RET_STS_SUCCESS;
10356: l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10357: l_module_name := 'release_capint_txns';
10358: pa_debug.set_curr_function( p_function => 'release_capint_txns',
10359: p_debug_mode => l_debug_mode );
10360: IF l_debug_mode = 'Y' THEN
10361: pa_debug.G_err_stage := 'About to call release_alloc_txns';
10362: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10357: l_module_name := 'release_capint_txns';
10358: pa_debug.set_curr_function( p_function => 'release_capint_txns',
10359: p_debug_mode => l_debug_mode );
10360: IF l_debug_mode = 'Y' THEN
10361: pa_debug.G_err_stage := 'About to call release_alloc_txns';
10362: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10363: END IF;
10364: --Initialise the who columns
10365: pa_alloc_run.init_who_cols;
10358: pa_debug.set_curr_function( p_function => 'release_capint_txns',
10359: p_debug_mode => l_debug_mode );
10360: IF l_debug_mode = 'Y' THEN
10361: pa_debug.G_err_stage := 'About to call release_alloc_txns';
10362: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10363: END IF;
10364: --Initialise the who columns
10365: pa_alloc_run.init_who_cols;
10366: --Call the api that releases the allocation run
10369: ,p_run_id => p_run_id
10370: ,x_retcode => x_return_status
10371: ,x_errbuf => x_msg_data);
10372: IF l_debug_mode = 'Y' THEN
10373: pa_debug.G_err_stage := 'Returned after releasing with msg '||x_msg_data;
10374: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10375: END IF;
10376: --If failed insert errors into alloc_exceptions table and raise error
10377: IF x_msg_data is NOT NULL then
10370: ,x_retcode => x_return_status
10371: ,x_errbuf => x_msg_data);
10372: IF l_debug_mode = 'Y' THEN
10373: pa_debug.G_err_stage := 'Returned after releasing with msg '||x_msg_data;
10374: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10375: END IF;
10376: --If failed insert errors into alloc_exceptions table and raise error
10377: IF x_msg_data is NOT NULL then
10378: x_return_status := FND_API.G_RET_STS_ERROR;
10381: x_return_status := 'S';
10382: End if ;
10383: --Update the capital interest run status
10384: IF l_debug_mode = 'Y' THEN
10385: pa_debug.G_err_stage := 'About to update the Release status to ['||x_return_status||']';
10386: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10387: END IF;
10388: /* bug fix: 3123539 based on the return status update the run staus and avoid
10389: * setting the concurrent process to raise error
10382: End if ;
10383: --Update the capital interest run status
10384: IF l_debug_mode = 'Y' THEN
10385: pa_debug.G_err_stage := 'About to update the Release status to ['||x_return_status||']';
10386: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10387: END IF;
10388: /* bug fix: 3123539 based on the return status update the run staus and avoid
10389: * setting the concurrent process to raise error
10390: */
10408: x_msg_count := 0;
10409: x_return_status := FND_API.G_RET_STS_SUCCESS;
10410: x_msg_data := Null;
10411: IF l_debug_mode = 'Y' THEN
10412: pa_debug.g_err_stage:= 'Leaving release_capint_txns return status ['||x_return_status||']';
10413: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10414: END IF;
10415: pa_debug.reset_curr_function;
10416: EXCEPTION
10409: x_return_status := FND_API.G_RET_STS_SUCCESS;
10410: x_msg_data := Null;
10411: IF l_debug_mode = 'Y' THEN
10412: pa_debug.g_err_stage:= 'Leaving release_capint_txns return status ['||x_return_status||']';
10413: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10414: END IF;
10415: pa_debug.reset_curr_function;
10416: EXCEPTION
10417: WHEN OTHERS THEN
10411: IF l_debug_mode = 'Y' THEN
10412: pa_debug.g_err_stage:= 'Leaving release_capint_txns return status ['||x_return_status||']';
10413: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10414: END IF;
10415: pa_debug.reset_curr_function;
10416: EXCEPTION
10417: WHEN OTHERS THEN
10418: x_return_status := FND_API.G_RET_STS_ERROR;
10419: x_msg_count := FND_MSG_PUB.count_msg;
10418: x_return_status := FND_API.G_RET_STS_ERROR;
10419: x_msg_count := FND_MSG_PUB.count_msg;
10420: IF l_debug_mode = 'Y' THEN
10421: x_msg_data:=sqlerrm;
10422: pa_debug.write_file('cap_int run: ' || 'LOG', x_msg_data);
10423: pa_debug.write_file('cap_int run: ' || 'LOG', pa_debug.G_err_stack);
10424: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10425: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10426: END IF;
10419: x_msg_count := FND_MSG_PUB.count_msg;
10420: IF l_debug_mode = 'Y' THEN
10421: x_msg_data:=sqlerrm;
10422: pa_debug.write_file('cap_int run: ' || 'LOG', x_msg_data);
10423: pa_debug.write_file('cap_int run: ' || 'LOG', pa_debug.G_err_stack);
10424: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10425: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10426: END IF;
10427: -- Update the status to release failure
10420: IF l_debug_mode = 'Y' THEN
10421: x_msg_data:=sqlerrm;
10422: pa_debug.write_file('cap_int run: ' || 'LOG', x_msg_data);
10423: pa_debug.write_file('cap_int run: ' || 'LOG', pa_debug.G_err_stack);
10424: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10425: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10426: END IF;
10427: -- Update the status to release failure
10428: UPDATE pa_alloc_runs
10421: x_msg_data:=sqlerrm;
10422: pa_debug.write_file('cap_int run: ' || 'LOG', x_msg_data);
10423: pa_debug.write_file('cap_int run: ' || 'LOG', pa_debug.G_err_stack);
10424: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10425: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10426: END IF;
10427: -- Update the status to release failure
10428: UPDATE pa_alloc_runs
10429: SET run_status = 'RF'
10430: ,release_request_id = l_rel_request_id
10431: ,release_request_date =sysdate
10432: WHERE run_id = p_run_id;
10433: IF l_debug_mode = 'Y' THEN
10434: pa_debug.write_file('LOG','p_run_id in when others of Main: '||to_char( p_run_id) );
10435: END IF;
10436: COMMIT;
10437: pa_debug.reset_curr_function;
10438: completion_status := fnd_concurrent.set_completion_status('ERROR', SQLERRM);
10433: IF l_debug_mode = 'Y' THEN
10434: pa_debug.write_file('LOG','p_run_id in when others of Main: '||to_char( p_run_id) );
10435: END IF;
10436: COMMIT;
10437: pa_debug.reset_curr_function;
10438: completion_status := fnd_concurrent.set_completion_status('ERROR', SQLERRM);
10439: END release_capint_txns;
10440: --This procedure deletes the source details for each capital interest transaction. This procedure will
10441: --be called from delete_alloc_run api when the DELETE button is pressed to delete a capital interest
10464: WHERE run_id=p_run_id;
10465: BEGIN
10466: x_msg_count := 0;
10467: x_return_status := FND_API.G_RET_STS_SUCCESS;
10468: l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10469: l_module_name := 'delete_cint_source_dets';
10470: pa_debug.set_curr_function( p_function => l_module_name,
10471: p_debug_mode => l_debug_mode );
10472: IF l_debug_mode = 'Y' THEN
10466: x_msg_count := 0;
10467: x_return_status := FND_API.G_RET_STS_SUCCESS;
10468: l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10469: l_module_name := 'delete_cint_source_dets';
10470: pa_debug.set_curr_function( p_function => l_module_name,
10471: p_debug_mode => l_debug_mode );
10472: IF l_debug_mode = 'Y' THEN
10473: pa_debug.g_err_stage:= 'About to delete the source txn details';
10474: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10469: l_module_name := 'delete_cint_source_dets';
10470: pa_debug.set_curr_function( p_function => l_module_name,
10471: p_debug_mode => l_debug_mode );
10472: IF l_debug_mode = 'Y' THEN
10473: pa_debug.g_err_stage:= 'About to delete the source txn details';
10474: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10475: END IF;
10476: FOR c_cint_txn_rec IN c_get_cint_txns LOOP
10477: LOOP
10470: pa_debug.set_curr_function( p_function => l_module_name,
10471: p_debug_mode => l_debug_mode );
10472: IF l_debug_mode = 'Y' THEN
10473: pa_debug.g_err_stage:= 'About to delete the source txn details';
10474: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10475: END IF;
10476: FOR c_cint_txn_rec IN c_get_cint_txns LOOP
10477: LOOP
10478: DELETE
10484: END IF;
10485: END LOOP;
10486: END LOOP;
10487: IF l_debug_mode = 'Y' THEN
10488: pa_debug.g_err_stage:= 'Leaving delete_cint_source_dets';
10489: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10490: END IF;
10491: pa_debug.reset_curr_function;
10492: EXCEPTION
10485: END LOOP;
10486: END LOOP;
10487: IF l_debug_mode = 'Y' THEN
10488: pa_debug.g_err_stage:= 'Leaving delete_cint_source_dets';
10489: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10490: END IF;
10491: pa_debug.reset_curr_function;
10492: EXCEPTION
10493: WHEN others THEN
10487: IF l_debug_mode = 'Y' THEN
10488: pa_debug.g_err_stage:= 'Leaving delete_cint_source_dets';
10489: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10490: END IF;
10491: pa_debug.reset_curr_function;
10492: EXCEPTION
10493: WHEN others THEN
10494: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10495: x_msg_count := 1;
10498: ( p_pkg_name => 'pa_alloc_run'
10499: ,p_procedure_name => 'delete_cint_source_dets'
10500: ,p_error_text => x_msg_data);
10501: IF l_debug_mode = 'Y' THEN
10502: pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
10503: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
10504: END IF;
10505: pa_debug.reset_curr_function;
10506: RAISE;
10499: ,p_procedure_name => 'delete_cint_source_dets'
10500: ,p_error_text => x_msg_data);
10501: IF l_debug_mode = 'Y' THEN
10502: pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
10503: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
10504: END IF;
10505: pa_debug.reset_curr_function;
10506: RAISE;
10507: END delete_cint_source_dets;
10501: IF l_debug_mode = 'Y' THEN
10502: pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
10503: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
10504: END IF;
10505: pa_debug.reset_curr_function;
10506: RAISE;
10507: END delete_cint_source_dets;
10508: END PA_ALLOC_run;