[Home] [Help]
1: PACKAGE BODY PA_ALLOC_RUN AS
2: /* $Header: PAXALRNB.pls 120.7 2008/03/18 14:59:01 sgottimu 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 ;
144: v_basis_method VARCHAR2(2);
145: v_dummy varchar2(1) := NULL ;
146: completion_status boolean; -- Added for 2841843
147: BEGIN
148: pa_debug.Init_err_stack ( 'Allocation Run');
149: v_debug_mode := NVL(p_debug_mode, 'Y');
150: v_process_mode := NVL(p_process_mode, 'SQL');
151: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
152: pa_debug.G_err_code := '0';
147: BEGIN
148: pa_debug.Init_err_stack ( 'Allocation Run');
149: v_debug_mode := NVL(p_debug_mode, 'Y');
150: v_process_mode := NVL(p_process_mode, 'SQL');
151: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
152: pa_debug.G_err_code := '0';
153: x_retcode := pa_debug.G_err_code;
154: x_errbuf := NULL;
155: G_rule_id := p_rule_id ;
148: pa_debug.Init_err_stack ( 'Allocation Run');
149: v_debug_mode := NVL(p_debug_mode, 'Y');
150: v_process_mode := NVL(p_process_mode, 'SQL');
151: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
152: pa_debug.G_err_code := '0';
153: x_retcode := pa_debug.G_err_code;
154: x_errbuf := NULL;
155: G_rule_id := p_rule_id ;
156: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
149: v_debug_mode := NVL(p_debug_mode, 'Y');
150: v_process_mode := NVL(p_process_mode, 'SQL');
151: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
152: pa_debug.G_err_code := '0';
153: x_retcode := pa_debug.G_err_code;
154: x_errbuf := NULL;
155: G_rule_id := p_rule_id ;
156: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
157: IF P_DEBUG_MODE = 'Y' THEN
152: pa_debug.G_err_code := '0';
153: x_retcode := pa_debug.G_err_code;
154: x_errbuf := NULL;
155: G_rule_id := p_rule_id ;
156: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
157: IF P_DEBUG_MODE = 'Y' THEN
158: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
159: END IF;
160: SELECT pa_alloc_runs_s.nextval
154: x_errbuf := NULL;
155: G_rule_id := p_rule_id ;
156: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
157: IF P_DEBUG_MODE = 'Y' THEN
158: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
159: END IF;
160: SELECT pa_alloc_runs_s.nextval
161: INTO v_run_id
162: FROM dual;
161: INTO v_run_id
162: FROM dual;
163: G_alloc_run_id := v_run_id;
164: x_run_id := v_run_id;
165: pa_debug.G_err_stage:= 'INITIALIZING WHO COLUMNS';
166: IF P_DEBUG_MODE = 'Y' THEN
167: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
168: END IF;
169: init_who_cols();
163: G_alloc_run_id := v_run_id;
164: x_run_id := v_run_id;
165: pa_debug.G_err_stage:= 'INITIALIZING WHO COLUMNS';
166: IF P_DEBUG_MODE = 'Y' THEN
167: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
168: END IF;
169: init_who_cols();
170: -- Initialize the global variables for currency information
171: pa_currency.SET_CURRENCY_INFO ;
168: END IF;
169: init_who_cols();
170: -- Initialize the global variables for currency information
171: pa_currency.SET_CURRENCY_INFO ;
172: pa_debug.G_err_stage:= 'GETTING CURENCY CODE';
173: IF P_DEBUG_MODE = 'Y' THEN
174: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
175: END IF;
176: G_denom_currency_code := pa_currency.get_currency_code();
170: -- Initialize the global variables for currency information
171: pa_currency.SET_CURRENCY_INFO ;
172: pa_debug.G_err_stage:= 'GETTING CURENCY CODE';
173: IF P_DEBUG_MODE = 'Y' THEN
174: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
175: END IF;
176: G_denom_currency_code := pa_currency.get_currency_code();
177: -- G_lock_name := 'PA_AL-'||to_char(p_rule_id);
178: -- pa_debug.G_err_stage := 'ACQUIRING LOCK';
174: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
175: END IF;
176: G_denom_currency_code := pa_currency.get_currency_code();
177: -- G_lock_name := 'PA_AL-'||to_char(p_rule_id);
178: -- pa_debug.G_err_stage := 'ACQUIRING LOCK';
179: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
180: -- IF (pa_debug.Acquire_User_Lock(G_lock_name) <> 0 ) THEN
181: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
182: -- 'PA_AL_CANT_ACQUIRE_LOCK', TRUE );
175: END IF;
176: G_denom_currency_code := pa_currency.get_currency_code();
177: -- G_lock_name := 'PA_AL-'||to_char(p_rule_id);
178: -- pa_debug.G_err_stage := 'ACQUIRING LOCK';
179: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
180: -- IF (pa_debug.Acquire_User_Lock(G_lock_name) <> 0 ) THEN
181: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
182: -- 'PA_AL_CANT_ACQUIRE_LOCK', TRUE );
183: -- END IF;
176: G_denom_currency_code := pa_currency.get_currency_code();
177: -- G_lock_name := 'PA_AL-'||to_char(p_rule_id);
178: -- pa_debug.G_err_stage := 'ACQUIRING LOCK';
179: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
180: -- IF (pa_debug.Acquire_User_Lock(G_lock_name) <> 0 ) THEN
181: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
182: -- 'PA_AL_CANT_ACQUIRE_LOCK', TRUE );
183: -- END IF;
184: lock_rule ( p_rule_id , x_run_id ) ;
181: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
182: -- 'PA_AL_CANT_ACQUIRE_LOCK', TRUE );
183: -- END IF;
184: lock_rule ( p_rule_id , x_run_id ) ;
185: pa_debug.G_err_stage := 'GETTING RULE DEFINITION';
186: IF P_DEBUG_MODE = 'Y' THEN
187: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
188: END IF;
189: OPEN C_get_rule;
183: -- END IF;
184: lock_rule ( p_rule_id , x_run_id ) ;
185: pa_debug.G_err_stage := 'GETTING RULE DEFINITION';
186: IF P_DEBUG_MODE = 'Y' THEN
187: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
188: END IF;
189: OPEN C_get_rule;
190: FETCH C_get_rule INTO v_alloc_rule_rec;
191: IF C_get_rule%NOTFOUND THEN
199: pa_client_extn_alloc.check_dependency(p_rule_id, v_status, v_err_message) ;
200: IF nvl(v_status,0) <> 0 then
201: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
202: IF P_DEBUG_MODE = 'Y' THEN
203: pa_debug.write_file('allocation_run: ' || 'LOG',v_err_message);
204: END IF;
205: alloc_errors(p_rule_id, x_run_id, 'R', 'E',v_err_message, TRUE) ;
206: END IF ;
207: pa_debug.G_err_stage := 'CHECKING LAST RUN STATUS';
203: pa_debug.write_file('allocation_run: ' || 'LOG',v_err_message);
204: END IF;
205: alloc_errors(p_rule_id, x_run_id, 'R', 'E',v_err_message, TRUE) ;
206: END IF ;
207: pa_debug.G_err_stage := 'CHECKING LAST RUN STATUS';
208: IF P_DEBUG_MODE = 'Y' THEN
209: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
210: END IF;
211: check_last_run_status (p_rule_id, v_run_id, v_mode );
205: alloc_errors(p_rule_id, x_run_id, 'R', 'E',v_err_message, TRUE) ;
206: END IF ;
207: pa_debug.G_err_stage := 'CHECKING LAST RUN STATUS';
208: IF P_DEBUG_MODE = 'Y' THEN
209: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
210: END IF;
211: check_last_run_status (p_rule_id, v_run_id, v_mode );
212: G_alloc_run_id := v_run_id;
213: x_run_id := v_run_id;
221: ELSE
222: G_alloc_run_id := v_run_id;
223: x_run_id := v_run_id;
224: END IF;
225: pa_debug.G_err_stage := 'GET CONCURRENT REQUEST_ID FOR DRAFT';
226: IF P_DEBUG_MODE = 'Y' THEN
227: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
228: END IF;
229: v_request_id:= fnd_global.conc_request_id ;
223: x_run_id := v_run_id;
224: END IF;
225: pa_debug.G_err_stage := 'GET CONCURRENT REQUEST_ID FOR DRAFT';
226: IF P_DEBUG_MODE = 'Y' THEN
227: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
228: END IF;
229: v_request_id:= fnd_global.conc_request_id ;
230: -- 923184 : Getting rule name and request id into a global variable.
231: G_rule_name := v_alloc_rule_rec.rule_name ;
230: -- 923184 : Getting rule name and request id into a global variable.
231: G_rule_name := v_alloc_rule_rec.rule_name ;
232: G_request_id := v_request_id ;
233: IF (v_mode = 'DRAFT' OR v_mode = 'DELETE') THEN /* for 2176096 */
234: pa_debug.G_err_stage := 'GET FISCAL YEAR QUARTER for '||p_run_period;
235: IF P_DEBUG_MODE = 'Y' THEN
236: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
237: END IF;
238: get_fiscalyear_quarter( v_alloc_rule_rec.period_type
232: G_request_id := v_request_id ;
233: IF (v_mode = 'DRAFT' OR v_mode = 'DELETE') THEN /* for 2176096 */
234: pa_debug.G_err_stage := 'GET FISCAL YEAR QUARTER for '||p_run_period;
235: IF P_DEBUG_MODE = 'Y' THEN
236: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
237: END IF;
238: get_fiscalyear_quarter( v_alloc_rule_rec.period_type
239: , p_run_period
240: , v_period_type
242: , v_period_year
243: , v_quarter
244: , v_period_num
245: , v_run_period_end_date );
246: pa_debug.G_err_stage := 'INSERTING ALLOC RUNS';
247: IF P_DEBUG_MODE = 'Y' THEN
248: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
249: END IF;
250: insert_alloc_runs( x_run_id
244: , v_period_num
245: , v_run_period_end_date );
246: pa_debug.G_err_stage := 'INSERTING ALLOC RUNS';
247: IF P_DEBUG_MODE = 'Y' THEN
248: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
249: END IF;
250: insert_alloc_runs( x_run_id
251: , p_rule_id
252: , p_run_period
315: G_tgt_expnd_type:=v_alloc_rule_rec.target_exp_type;
316: G_offset_expnd_org:=pa_utils.GetOrgName( v_alloc_rule_rec.offset_exp_org_id);
317: G_offset_expnd_type_class:= v_alloc_rule_rec.offset_exp_type_class;
318: G_offset_expnd_type:=v_alloc_rule_rec.offset_exp_type;
319: pa_debug.G_err_stage := 'VALIDATING RULE';
320: IF P_DEBUG_MODE = 'Y' THEN
321: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
322: END IF;
323: validate_rule( p_rule_id
317: G_offset_expnd_type_class:= v_alloc_rule_rec.offset_exp_type_class;
318: G_offset_expnd_type:=v_alloc_rule_rec.offset_exp_type;
319: pa_debug.G_err_stage := 'VALIDATING RULE';
320: IF P_DEBUG_MODE = 'Y' THEN
321: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
322: END IF;
323: validate_rule( p_rule_id
324: , x_run_id
325: , v_alloc_rule_rec.start_date_active
343: , v_alloc_rule_rec.basis_balance_type
344: , v_alloc_rule_rec.ORG_ID
345: , v_alloc_rule_rec.fixed_amount
346: , p_expnd_item_date );
347: pa_debug.G_err_stage := 'POPULATING RUN SOURCES';
348: IF P_DEBUG_MODE = 'Y' THEN
349: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
350: END IF;
351: populate_run_sources( p_rule_id
345: , v_alloc_rule_rec.fixed_amount
346: , p_expnd_item_date );
347: pa_debug.G_err_stage := 'POPULATING RUN SOURCES';
348: IF P_DEBUG_MODE = 'Y' THEN
349: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
350: END IF;
351: populate_run_sources( p_rule_id
352: , x_run_id
353: , v_alloc_rule_rec.alloc_resource_list_id
355: /* FP.M : Allocation Impact */
356: , v_alloc_rule_rec.alloc_resource_struct_type
357: , v_alloc_rule_rec.alloc_rbs_version
358: );
359: pa_debug.G_err_stage := 'POPULATING RUN TARGETS';
360: IF P_DEBUG_MODE = 'Y' THEN
361: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
362: END IF;
363: populate_run_targets( p_rule_id
357: , v_alloc_rule_rec.alloc_rbs_version
358: );
359: pa_debug.G_err_stage := 'POPULATING RUN TARGETS';
360: IF P_DEBUG_MODE = 'Y' THEN
361: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
362: END IF;
363: populate_run_targets( p_rule_id
364: , x_run_id
365: , v_alloc_rule_rec.basis_method
386: alloc_errors( p_rule_id, x_run_id, 'T', 'E',
387: 'PA_AL_NO_TARGET_DETAILS', TRUE,'Y' );
388: end if ;
389: /* raise error NOW if validation failed at any point */
390: pa_debug.G_err_stage := 'CHECKING FOR FATAL ERRORS';
391: IF P_DEBUG_MODE = 'Y' THEN
392: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
393: END IF;
394: IF (G_fatal_err_found) THEN
388: end if ;
389: /* raise error NOW if validation failed at any point */
390: pa_debug.G_err_stage := 'CHECKING FOR FATAL ERRORS';
391: IF P_DEBUG_MODE = 'Y' THEN
392: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
393: END IF;
394: IF (G_fatal_err_found) THEN
395: G_fatal_err_found:= FALSE;
396: COMMIT;
397: alloc_errors( p_rule_id, x_run_id, 'R', 'E',
398: 'PA_AL_FATAL_ERROR_FOUND', TRUE,'N' );
399: END IF;
400: COMMIT ; /* Introduced as part of commit cycle changes */
401: pa_debug.G_err_stage := 'CALCULATE SRC PROJECT AMOUNTS';
402: IF P_DEBUG_MODE = 'Y' THEN
403: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
404: END IF;
405: cal_amounts_from_projects( p_rule_id
399: END IF;
400: COMMIT ; /* Introduced as part of commit cycle changes */
401: pa_debug.G_err_stage := 'CALCULATE SRC PROJECT AMOUNTS';
402: IF P_DEBUG_MODE = 'Y' THEN
403: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
404: END IF;
405: cal_amounts_from_projects( p_rule_id
406: , x_run_id
407: , v_alloc_rule_rec.period_type
415: /* FP.M : Allocation Impact : Bug # 3512552 */
416: , v_alloc_rule_rec.ALLOC_RESOURCE_STRUCT_TYPE
417: , v_alloc_rule_rec.ALLOC_RBS_VERSION
418: );
419: pa_debug.G_err_stage := 'CALCULATE SRC GL AMOUNTS';
420: IF P_DEBUG_MODE = 'Y' THEN
421: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
422: END IF;
423: calculate_src_GL_amounts( p_rule_id
417: , v_alloc_rule_rec.ALLOC_RBS_VERSION
418: );
419: pa_debug.G_err_stage := 'CALCULATE SRC GL AMOUNTS';
420: IF P_DEBUG_MODE = 'Y' THEN
421: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
422: END IF;
423: calculate_src_GL_amounts( p_rule_id
424: , x_run_id
425: , p_run_period
425: , p_run_period
426: , v_alloc_rule_rec.source_amount_type
427: , v_src_amount_from_GL );
428: IF P_DEBUG_MODE = 'Y' THEN
429: pa_debug.write_file('allocation_run: ' || 'LOG', 'v_src_amount_from_GL: '||to_char(v_src_amount_from_GL) );
430: END IF;
431: If v_alloc_rule_rec.allocation_method = 'F' then
432: If (nvl(v_src_amount_from_proj,0) + nvl(v_src_amount_from_GL,0)) = 0 then
433: alloc_errors( p_rule_id, x_run_id, 'R', 'E',
433: alloc_errors( p_rule_id, x_run_id, 'R', 'E',
434: 'PA_AL_ZERO_SOURCE_POOL_AMOUNT', TRUE );
435: End if ;
436: End If ;
437: pa_debug.G_err_stage := 'CALCULATE BASIS PROJECT AMOUNTS';
438: IF P_DEBUG_MODE = 'Y' THEN
439: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
440: END IF;
441: /* 2182563 - v_basis_method is a out variable now. If no records are found
435: End if ;
436: End If ;
437: pa_debug.G_err_stage := 'CALCULATE BASIS PROJECT AMOUNTS';
438: IF P_DEBUG_MODE = 'Y' THEN
439: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
440: END IF;
441: /* 2182563 - v_basis_method is a out variable now. If no records are found
442: for basis method 'P' or 'C' then it is changed to 'S'. */
443: cal_proj_basis_amounts( p_rule_id
456: , v_alloc_rule_rec.BASIS_RESOURCE_STRUCT_TYPE
457: , v_alloc_rule_rec.BASIS_RBS_VERSION
458: );
459: COMMIT ; /* Introduced as part of commit cycle changes */
460: pa_debug.G_err_stage := 'CREATING TARGET TRANSACTIONS';
461: IF P_DEBUG_MODE = 'Y' THEN
462: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
463: END IF;
464: create_target_txns( p_rule_id
458: );
459: COMMIT ; /* Introduced as part of commit cycle changes */
460: pa_debug.G_err_stage := 'CREATING TARGET TRANSACTIONS';
461: IF P_DEBUG_MODE = 'Y' THEN
462: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
463: END IF;
464: create_target_txns( p_rule_id
465: , x_run_id
466: , v_alloc_rule_rec.period_type
474: , v_alloc_rule_rec.source_amount_type
475: , nvl(v_src_amount_from_proj,0) + nvl(v_src_amount_from_GL,0)
476: , v_curr_alloc_amount );
477: IF (v_alloc_rule_rec.offset_method <> 'N') THEN
478: pa_debug.G_err_stage := 'CREATING OFFSET TRANSACTIONS';
479: IF P_DEBUG_MODE = 'Y' THEN
480: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
481: END IF;
482: create_offset_txns( p_rule_id
476: , v_curr_alloc_amount );
477: IF (v_alloc_rule_rec.offset_method <> 'N') THEN
478: pa_debug.G_err_stage := 'CREATING OFFSET TRANSACTIONS';
479: IF P_DEBUG_MODE = 'Y' THEN
480: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
481: END IF;
482: create_offset_txns( p_rule_id
483: , x_run_id
484: , v_alloc_rule_rec.period_type
498: END IF; /* v_mode = draft */
499: COMMIT ; /* Introduced as part of commit cycle changes */
500: IF (v_mode = 'RELEASE' OR v_alloc_rule_rec.auto_release_flag ='Y') THEN
501: v_mode := 'RELEASE' ;
502: pa_debug.G_err_stage := 'RELEASING THE RUN';
503: IF P_DEBUG_MODE = 'Y' THEN
504: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
505: END IF;
506: pa_alloc_run.release_alloc_txns( p_rule_id, x_run_id ,x_retcode,
500: IF (v_mode = 'RELEASE' OR v_alloc_rule_rec.auto_release_flag ='Y') THEN
501: v_mode := 'RELEASE' ;
502: pa_debug.G_err_stage := 'RELEASING THE RUN';
503: IF P_DEBUG_MODE = 'Y' THEN
504: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
505: END IF;
506: pa_alloc_run.release_alloc_txns( p_rule_id, x_run_id ,x_retcode,
507: x_errbuf );
508: END IF; /* v_mode = release */
517: --
518: IF (G_num_txns = 0 ) THEN
519: v_mode := 'RELEASE' ;
520: IF P_DEBUG_MODE = 'Y' THEN
521: pa_debug.write_file('allocation_run: ' || 'LOG', 'All tasks received zero amounts. No transactions created');
522: END IF;
523: alloc_errors( p_rule_id, x_run_id,'R','W','PA_AL_ALL_TASKS_RECD_ZERO_AMT', FALSE) ;
524: END IF ;
525:
529: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
530: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
531: WHERE run_id = x_run_id;
532:
533: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS SUCCESS';
534:
535: IF P_DEBUG_MODE = 'Y' THEN
536: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
537: END IF;
532:
533: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS SUCCESS';
534:
535: IF P_DEBUG_MODE = 'Y' THEN
536: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
537: END IF;
538:
539: /* added for 6243121 */
540: else -- if any error/exception is encountered while releasing
543: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
544: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
545: WHERE run_id = x_run_id;
546:
547: pa_debug.G_err_stage := 'UPDATED RUN STATUS AS FAILURE';
548: IF P_DEBUG_MODE = 'Y' THEN
549: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
550: END IF;
551: end if;
545: WHERE run_id = x_run_id;
546:
547: pa_debug.G_err_stage := 'UPDATED RUN STATUS AS FAILURE';
548: IF P_DEBUG_MODE = 'Y' THEN
549: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
550: END IF;
551: end if;
552: /* added for 6243121 */
553:
550: END IF;
551: end if;
552: /* added for 6243121 */
553:
554: pa_debug.G_err_stage := 'RELEASING LOCK';
555: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
556: -- IF (pa_debug.Release_User_Lock(G_lock_name) <> 0 ) THEN
557: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
558: -- 'PA_AL_LOCK_RELEASE_FAILED', TRUE );
551: end if;
552: /* added for 6243121 */
553:
554: pa_debug.G_err_stage := 'RELEASING LOCK';
555: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
556: -- IF (pa_debug.Release_User_Lock(G_lock_name) <> 0 ) THEN
557: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
558: -- 'PA_AL_LOCK_RELEASE_FAILED', TRUE );
559: -- END IF;
552: /* added for 6243121 */
553:
554: pa_debug.G_err_stage := 'RELEASING LOCK';
555: -- pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
556: -- IF (pa_debug.Release_User_Lock(G_lock_name) <> 0 ) THEN
557: -- alloc_errors( p_rule_id, x_run_id, 'R', 'E',
558: -- 'PA_AL_LOCK_RELEASE_FAILED', TRUE );
559: -- END IF;
560: unlock_rule(p_rule_id, x_run_id) ;
559: -- END IF;
560: unlock_rule(p_rule_id, x_run_id) ;
561: COMMIT;
562: /* restore the old stack */
563: pa_debug.reset_err_stack;
564: EXCEPTION
565: WHEN OTHERS THEN
566: x_errbuf:= pa_debug.G_err_stage|| ': '||sqlerrm;
567: x_run_id:= G_alloc_run_id;
562: /* restore the old stack */
563: pa_debug.reset_err_stack;
564: EXCEPTION
565: WHEN OTHERS THEN
566: x_errbuf:= pa_debug.G_err_stage|| ': '||sqlerrm;
567: x_run_id:= G_alloc_run_id;
568: IF P_DEBUG_MODE = 'Y' THEN
569: pa_debug.write_file('allocation_run: ' || 'LOG', x_errbuf);
570: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
565: WHEN OTHERS THEN
566: x_errbuf:= pa_debug.G_err_stage|| ': '||sqlerrm;
567: x_run_id:= G_alloc_run_id;
568: IF P_DEBUG_MODE = 'Y' THEN
569: pa_debug.write_file('allocation_run: ' || 'LOG', x_errbuf);
570: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
571: END IF;
572: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
573: IF P_DEBUG_MODE = 'Y' THEN
566: x_errbuf:= pa_debug.G_err_stage|| ': '||sqlerrm;
567: x_run_id:= G_alloc_run_id;
568: IF P_DEBUG_MODE = 'Y' THEN
569: pa_debug.write_file('allocation_run: ' || 'LOG', x_errbuf);
570: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
571: END IF;
572: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
573: IF P_DEBUG_MODE = 'Y' THEN
574: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
568: IF P_DEBUG_MODE = 'Y' THEN
569: pa_debug.write_file('allocation_run: ' || 'LOG', x_errbuf);
570: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
571: END IF;
572: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
573: IF P_DEBUG_MODE = 'Y' THEN
574: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
575: END IF;
576: UPDATE pa_alloc_runs
570: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stack);
571: END IF;
572: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
573: IF P_DEBUG_MODE = 'Y' THEN
574: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
575: END IF;
576: UPDATE pa_alloc_runs
577: SET run_status = DECODE( v_mode, 'RELEASE', 'RF',
578: 'DRAFT', 'DF',
580: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
581: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
582: WHERE run_id = x_run_id;
583: IF P_DEBUG_MODE = 'Y' THEN
584: pa_debug.write_file('allocation_run: ' || 'LOG', 'x_run_id in when others of Main: '||to_char( x_run_id) );
585: END IF;
586: COMMIT;
587: /* 2841843 Marking Conc request as completed failure
588: Explicitely not raising the exception as all debug messages
606: WHERE run_id = p_prev_run_id ;
607: v_run_status VARCHAR2(5) := NULL;
608: v_prev_run_id NUMBER;
609: BEGIN
610: pa_debug.set_err_stack('check_last_run_status');
611: pa_debug.G_err_code := 0;
612: OPEN C_run_id;
613: FETCH C_run_id INTO v_prev_run_id;
614: CLOSE C_run_id;
607: v_run_status VARCHAR2(5) := NULL;
608: v_prev_run_id NUMBER;
609: BEGIN
610: pa_debug.set_err_stack('check_last_run_status');
611: pa_debug.G_err_code := 0;
612: OPEN C_run_id;
613: FETCH C_run_id INTO v_prev_run_id;
614: CLOSE C_run_id;
615: IF v_prev_run_id IS NULL THEN
639: x_run_id := v_prev_run_id;
640: END IF ;
641: */
642: /* restore the old stack */
643: pa_debug.reset_err_stack;
644: EXCEPTION
645: WHEN OTHERS THEN
646: pa_debug.G_err_code := SQLCODE;
647: RAISE;
642: /* restore the old stack */
643: pa_debug.reset_err_stack;
644: EXCEPTION
645: WHEN OTHERS THEN
646: pa_debug.G_err_code := SQLCODE;
647: RAISE;
648: END check_last_run_status;
649: -- ------------------------------------------------------------
650: -- ins_alloc_exceptions
661: , p_project_id IN NUMBER
662: , p_task_id IN NUMBER
663: , p_exception_code IN VARCHAR2 ) IS
664: BEGIN
665: pa_debug.set_err_stack('ins_alloc_exceptions');
666: INSERT INTO pa_alloc_exceptions (
667: RUN_ID
668: , RULE_ID
669: , LEVEL_CODE
689: , p_project_id
690: , p_task_id
691: , p_exception_code );
692: /* restore the old stack */
693: pa_debug.reset_err_stack;
694: EXCEPTION
695: WHEN OTHERS THEN
696: pa_debug.G_err_code := SQLCODE;
697: RAISE;
692: /* restore the old stack */
693: pa_debug.reset_err_stack;
694: EXCEPTION
695: WHEN OTHERS THEN
696: pa_debug.G_err_code := SQLCODE;
697: RAISE;
698: END ins_alloc_exceptions;
699: -- ------------------------------------------------------------
700: -- alloc_errors:
710: , p_task_id IN NUMBER DEFAULT NULL ) IS
711: v_mesg_code VARCHAR2(30);
712: BEGIN
713: v_mesg_code := SUBSTR( p_mesg_code,1,30);
714: pa_debug.set_err_stack('alloc_errors');
715: IF ( p_insert_flag = 'Y') THEN
716: ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
717: G_created_by, G_last_update_date,
718: G_last_updated_by, G_last_update_login,
718: G_last_updated_by, G_last_update_login,
719: p_level, p_type, p_project_id, p_task_id, v_mesg_code );
720: END IF;
721: IF (p_fatal_err) THEN
722: pa_debug.raise_error( -20010, p_mesg_code );
723: END IF;
724: pa_debug.reset_err_stack;
725: EXCEPTION
726: WHEN OTHERS THEN
720: END IF;
721: IF (p_fatal_err) THEN
722: pa_debug.raise_error( -20010, p_mesg_code );
723: END IF;
724: pa_debug.reset_err_stack;
725: EXCEPTION
726: WHEN OTHERS THEN
727: pa_debug.G_err_code := SQLCODE;
728: RAISE;
723: END IF;
724: pa_debug.reset_err_stack;
725: EXCEPTION
726: WHEN OTHERS THEN
727: pa_debug.G_err_code := SQLCODE;
728: RAISE;
729: END alloc_errors;
730: -- ----------------------------------------------------------------------
731: -- Validate_rule
802: v_dummy NUMBER;
803: -- v_task_id NUMBER;
804: -- v_billable_only_flag VARCHAR2(1);
805: BEGIN
806: pa_debug.set_err_stack('validate_rule');
807: pa_debug.G_err_code:= 0;
808: pa_debug.G_err_stage:= 'CHECKING DATE EFFECTIVITY OF RULE';
809: IF P_DEBUG_MODE = 'Y' THEN
810: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
803: -- v_task_id NUMBER;
804: -- v_billable_only_flag VARCHAR2(1);
805: BEGIN
806: pa_debug.set_err_stack('validate_rule');
807: pa_debug.G_err_code:= 0;
808: pa_debug.G_err_stage:= 'CHECKING DATE EFFECTIVITY OF RULE';
809: IF P_DEBUG_MODE = 'Y' THEN
810: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
811: END IF;
804: -- v_billable_only_flag VARCHAR2(1);
805: BEGIN
806: pa_debug.set_err_stack('validate_rule');
807: pa_debug.G_err_code:= 0;
808: pa_debug.G_err_stage:= 'CHECKING DATE EFFECTIVITY OF RULE';
809: IF P_DEBUG_MODE = 'Y' THEN
810: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
811: END IF;
812: IF ( TRUNC(p_start_date_active) > TRUNC(G_sysdate) OR
806: pa_debug.set_err_stack('validate_rule');
807: pa_debug.G_err_code:= 0;
808: pa_debug.G_err_stage:= 'CHECKING DATE EFFECTIVITY OF RULE';
809: IF P_DEBUG_MODE = 'Y' THEN
810: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
811: END IF;
812: IF ( TRUNC(p_start_date_active) > TRUNC(G_sysdate) OR
813: TRUNC(p_end_date_active)
814: G_fatal_err_found := TRUE;
815: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
816: 'PA_AL_RULE_INACTIVE');
817: END IF;
818: pa_debug.G_err_stage:= 'CHECKING SRC_LINES DEFINITON';
819: IF P_DEBUG_MODE = 'Y' THEN
820: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
821: END IF;
822: IF(p_fixed_amount IS NULL) THEN
816: 'PA_AL_RULE_INACTIVE');
817: END IF;
818: pa_debug.G_err_stage:= 'CHECKING SRC_LINES DEFINITON';
819: IF P_DEBUG_MODE = 'Y' THEN
820: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
821: END IF;
822: IF(p_fixed_amount IS NULL) THEN
823: IF (p_source_extn_flag = 'N') THEN
824: OPEN C_pa_src_exists;
841: 'PA_AL_NO_SOURCE_LINES');
842: END IF; /* if v_gl_err AND v_src_err */
843: END IF; /* if p_source_extn_flag */
844: END IF; /* if p_fixed_amount */
845: pa_debug.G_err_stage:= 'CHECKING TARGET LINES';
846: IF P_DEBUG_MODE = 'Y' THEN
847: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
848: END IF;
849: IF( p_target_extn_flag = 'N') THEN
843: END IF; /* if p_source_extn_flag */
844: END IF; /* if p_fixed_amount */
845: pa_debug.G_err_stage:= 'CHECKING TARGET LINES';
846: IF P_DEBUG_MODE = 'Y' THEN
847: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
848: END IF;
849: IF( p_target_extn_flag = 'N') THEN
850: v_dummy:= 0;
851: OPEN C_pa_trg_exists ;
901: CLOSE C_billable_task ;
902: END IF; /* billable_flag */
903: END IF; /* task_id IS NOT NULL */
904: END LOOP; /* C_target_lines */
905: pa_debug.G_err_stage:= 'VALIDATING OFFSET METHOD';
906: IF P_DEBUG_MODE = 'Y' THEN
907: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
908: END IF;
909: IF(p_offset_method IN ( 'A', 'B')) THEN
903: END IF; /* task_id IS NOT NULL */
904: END LOOP; /* C_target_lines */
905: pa_debug.G_err_stage:= 'VALIDATING OFFSET METHOD';
906: IF P_DEBUG_MODE = 'Y' THEN
907: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
908: END IF;
909: IF(p_offset_method IN ( 'A', 'B')) THEN
910: /* if GL_source_lines exists or p_fixed_amount exists
911: then error out */
915: 'PA_AL_INVALID_OFFSET_METHOD');
916: END IF;
917: END IF; /* if p_offset_method */
918: IF (p_offset_method <> 'N') THEN
919: pa_debug.G_err_stage:= 'CHECKING OFFSET LINES';
920: IF P_DEBUG_MODE = 'Y' THEN
921: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
922: END IF;
923: IF(p_offset_method = 'C') THEN /* specific offset proj/task */
917: END IF; /* if p_offset_method */
918: IF (p_offset_method <> 'N') THEN
919: pa_debug.G_err_stage:= 'CHECKING OFFSET LINES';
920: IF P_DEBUG_MODE = 'Y' THEN
921: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
922: END IF;
923: IF(p_offset_method = 'C') THEN /* specific offset proj/task */
924: OPEN C_check_task(p_offset_project_id, p_offset_task_id, p_expnd_item_date);
925: FETCH C_check_task INTO v_dummy;
931: CLOSE C_check_task;
932: END IF; /* endif offset_method */
933: END IF; /* p_offset_method <> */
934: IF(p_basis_method NOT IN ( 'S', 'C')) THEN
935: pa_debug.G_err_stage:= 'CHECKING BASIS LINES';
936: IF P_DEBUG_MODE = 'Y' THEN
937: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
938: END IF;
939: IF (p_basis_method IN( 'P', 'FP')) THEN
933: END IF; /* p_offset_method <> */
934: IF(p_basis_method NOT IN ( 'S', 'C')) THEN
935: pa_debug.G_err_stage:= 'CHECKING BASIS LINES';
936: IF P_DEBUG_MODE = 'Y' THEN
937: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
938: END IF;
939: IF (p_basis_method IN( 'P', 'FP')) THEN
940: IF (p_basis_balance_category IS NULL) THEN
941: G_fatal_err_found := TRUE;
967: END IF;
968: END IF; /* if p_basis_balance_category */
969: END IF; /* if p_basis_method = 'P' or 'FP' */
970: END IF; /* if basis_method NOT IN */
971: pa_debug.G_err_stage:= 'VALIDATING TARGET EXP ORGANIZATION';
972: IF P_DEBUG_MODE = 'Y' THEN
973: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
974: END IF;
975: IF( p_target_exp_org_id IS NULL OR
969: END IF; /* if p_basis_method = 'P' or 'FP' */
970: END IF; /* if basis_method NOT IN */
971: pa_debug.G_err_stage:= 'VALIDATING TARGET EXP ORGANIZATION';
972: IF P_DEBUG_MODE = 'Y' THEN
973: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
974: END IF;
975: IF( p_target_exp_org_id IS NULL OR
976: pa_utils2.CheckExpOrg( p_target_exp_org_id) = 'N') THEN
977: G_fatal_err_found := TRUE;
977: G_fatal_err_found := TRUE;
978: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
979: 'PA_AL_INVALID_TARGET_EXP_ORG') ;
980: END IF;
981: pa_debug.G_err_stage:= 'VALIDATING TARGET EXPENDITURE TYPE';
982: IF P_DEBUG_MODE = 'Y' THEN
983: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
984: END IF;
985: IF(p_target_exp_type IS NOT NULL) THEN
979: 'PA_AL_INVALID_TARGET_EXP_ORG') ;
980: END IF;
981: pa_debug.G_err_stage:= 'VALIDATING TARGET EXPENDITURE TYPE';
982: IF P_DEBUG_MODE = 'Y' THEN
983: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
984: END IF;
985: IF(p_target_exp_type IS NOT NULL) THEN
986: OPEN C_exptype_exists(p_target_exp_type);
987: FETCH C_exptype_exists INTO v_dummy;
996: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
997: 'PA_AL_INVALID_TARGET_EXP_TYPE') ;
998: END IF; /* if p_target_exp_type */
999: IF (p_offset_method <> 'N') THEN
1000: pa_debug.G_err_stage:= 'VALIDATING OFFSET EXP ORGANIZATION';
1001: IF P_DEBUG_MODE = 'Y' THEN
1002: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
1003: END IF;
1004: IF( p_offset_exp_org_id IS NULL ) THEN
998: END IF; /* if p_target_exp_type */
999: IF (p_offset_method <> 'N') THEN
1000: pa_debug.G_err_stage:= 'VALIDATING OFFSET EXP ORGANIZATION';
1001: IF P_DEBUG_MODE = 'Y' THEN
1002: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
1003: END IF;
1004: IF( p_offset_exp_org_id IS NULL ) THEN
1005: G_fatal_err_found := TRUE;
1006: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
1009: G_fatal_err_found := TRUE;
1010: alloc_errors( p_rule_id, p_run_id, 'R', 'E',
1011: 'PA_AL_INVALID_OFFSET_EXP_ORG') ;
1012: END IF;
1013: pa_debug.G_err_stage:= 'VALIDATING OFFSET EXPENDITURE TYPE';
1014: IF P_DEBUG_MODE = 'Y' THEN
1015: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
1016: END IF;
1017: IF ( p_offset_exp_type IS NOT NULL) THEN
1011: 'PA_AL_INVALID_OFFSET_EXP_ORG') ;
1012: END IF;
1013: pa_debug.G_err_stage:= 'VALIDATING OFFSET EXPENDITURE TYPE';
1014: IF P_DEBUG_MODE = 'Y' THEN
1015: pa_debug.write_file('validate_rule: ' || 'LOG', pa_debug.G_err_stage);
1016: END IF;
1017: IF ( p_offset_exp_type IS NOT NULL) THEN
1018: OPEN C_exptype_exists(p_offset_exp_type);
1019: FETCH C_exptype_exists INTO v_dummy;
1029: 'PA_AL_UNDEFINED_OFFSET_EXP_TYPE');
1030: END IF; /* if p_offset_exp_type */
1031: END IF; /* p_offset_method */
1032: /* restore the old G_err_stack */
1033: pa_debug.reset_err_stack;
1034: EXCEPTION
1035: WHEN OTHERS THEN
1036: pa_debug.G_err_code := SQLCODE;
1037: RAISE;
1032: /* restore the old G_err_stack */
1033: pa_debug.reset_err_stack;
1034: EXCEPTION
1035: WHEN OTHERS THEN
1036: pa_debug.G_err_code := SQLCODE;
1037: RAISE;
1038: END validate_rule;
1039: -- ------------------------------------------------------------
1040: -- insert_alloc_run_sources
1058: AND task_id = p_task_id;
1059: v_dummy NUMBER;
1060: allow_insert_flag VARCHAR2(1);
1061: BEGIN
1062: pa_debug.set_err_stack('ins_alloc_run_sources');
1063: pa_debug.G_err_code:= 0;
1064: OPEN source_exists;
1065: FETCH source_exists INTO v_dummy;
1066: IF source_exists%NOTFOUND THEN
1059: v_dummy NUMBER;
1060: allow_insert_flag VARCHAR2(1);
1061: BEGIN
1062: pa_debug.set_err_stack('ins_alloc_run_sources');
1063: pa_debug.G_err_code:= 0;
1064: OPEN source_exists;
1065: FETCH source_exists INTO v_dummy;
1066: IF source_exists%NOTFOUND THEN
1067: /* allow insert if current proj-task not exists */
1099: , p_last_update_login
1100: , p_task_id );
1101: END IF;
1102: /* restore the old stack */
1103: pa_debug.reset_err_stack;
1104: EXCEPTION
1105: WHEN OTHERS THEN
1106: pa_debug.G_err_code := SQLCODE;
1107: RAISE;
1102: /* restore the old stack */
1103: pa_debug.reset_err_stack;
1104: EXCEPTION
1105: WHEN OTHERS THEN
1106: pa_debug.G_err_code := SQLCODE;
1107: RAISE;
1108: END insert_alloc_run_sources;
1109: -- ------------------------------------------------------------
1110: -- exclude_curr_proj_task
1130: v_dummy1 NUMBER;
1131: v_dummy2 NUMBER;
1132: v_status NUMBER:= 0;
1133: BEGIN
1134: pa_debug.set_err_stack('exclude_curr_proj_task');
1135: pa_debug.G_err_code:= 0;
1136: pa_debug.G_err_stage := 'DETERMINE EXCL_CURR_PROJ_TASK';
1137: IF P_DEBUG_MODE = 'Y' THEN
1138: pa_debug.write_file('exclude_curr_proj_task: ' || 'LOG', pa_debug.G_err_stage);
1131: v_dummy2 NUMBER;
1132: v_status NUMBER:= 0;
1133: BEGIN
1134: pa_debug.set_err_stack('exclude_curr_proj_task');
1135: pa_debug.G_err_code:= 0;
1136: pa_debug.G_err_stage := 'DETERMINE EXCL_CURR_PROJ_TASK';
1137: IF P_DEBUG_MODE = 'Y' THEN
1138: pa_debug.write_file('exclude_curr_proj_task: ' || 'LOG', pa_debug.G_err_stage);
1139: END IF;
1132: v_status NUMBER:= 0;
1133: BEGIN
1134: pa_debug.set_err_stack('exclude_curr_proj_task');
1135: pa_debug.G_err_code:= 0;
1136: pa_debug.G_err_stage := 'DETERMINE EXCL_CURR_PROJ_TASK';
1137: IF P_DEBUG_MODE = 'Y' THEN
1138: pa_debug.write_file('exclude_curr_proj_task: ' || 'LOG', pa_debug.G_err_stage);
1139: END IF;
1140: IF (p_type = 'SRC') THEN
1134: pa_debug.set_err_stack('exclude_curr_proj_task');
1135: pa_debug.G_err_code:= 0;
1136: pa_debug.G_err_stage := 'DETERMINE EXCL_CURR_PROJ_TASK';
1137: IF P_DEBUG_MODE = 'Y' THEN
1138: pa_debug.write_file('exclude_curr_proj_task: ' || 'LOG', pa_debug.G_err_stage);
1139: END IF;
1140: IF (p_type = 'SRC') THEN
1141: OPEN C_excl_src_proj_task;
1142: FETCH C_excl_src_proj_task INTO v_dummy1, v_dummy2;
1156: END IF;
1157: CLOSE C_excl_trg_proj_task;
1158: END IF;
1159: /* restore the old stack */
1160: pa_debug.reset_err_stack;
1161: return v_status;
1162: EXCEPTION
1163: WHEN OTHERS THEN
1164: return (SQLCODE);
1317: v_src_task_id NUMBER ;
1318: v_src_top_task_id NUMBER ;
1319: v_cx_err_flag VARCHAR2(1) ;
1320: BEGIN
1321: pa_debug.set_err_stack('populate_run_sources');
1322: pa_debug.G_err_code:= 0;
1323: IF p_source_clnt_extn_flag = 'Y' THEN
1324: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR EXCLUDES';
1325: IF P_DEBUG_MODE = 'Y' THEN
1318: v_src_top_task_id NUMBER ;
1319: v_cx_err_flag VARCHAR2(1) ;
1320: BEGIN
1321: pa_debug.set_err_stack('populate_run_sources');
1322: pa_debug.G_err_code:= 0;
1323: IF p_source_clnt_extn_flag = 'Y' THEN
1324: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR EXCLUDES';
1325: IF P_DEBUG_MODE = 'Y' THEN
1326: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1320: BEGIN
1321: pa_debug.set_err_stack('populate_run_sources');
1322: pa_debug.G_err_code:= 0;
1323: IF p_source_clnt_extn_flag = 'Y' THEN
1324: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR EXCLUDES';
1325: IF P_DEBUG_MODE = 'Y' THEN
1326: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1327: END IF;
1328: /* reset the error_flag */
1322: pa_debug.G_err_code:= 0;
1323: IF p_source_clnt_extn_flag = 'Y' THEN
1324: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR EXCLUDES';
1325: IF P_DEBUG_MODE = 'Y' THEN
1326: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1327: END IF;
1328: /* reset the error_flag */
1329: G_fatal_err_found := FALSE;
1330: pa_client_extn_alloc.source_extn(p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1328: /* reset the error_flag */
1329: G_fatal_err_found := FALSE;
1330: pa_client_extn_alloc.source_extn(p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1331: IF P_DEBUG_MODE = 'Y' THEN
1332: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client return message: '|| v_err_message);
1333: END IF;
1334: IF nvl(v_status,0) <> 0 then
1335: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
1336: alloc_errors(p_rule_id, p_run_id, 'S', 'E', v_err_message, TRUE) ;
1346: G_fatal_err_found:= TRUE;
1347: alloc_errors( p_rule_id, p_run_id, 'S', 'E',
1348: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id);
1349: IF P_DEBUG_MODE = 'Y' THEN
1350: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client Extension returned an invalid source project: '
1351: || to_char(v_cx_project_id));
1352: END IF;
1353: v_cx_err_flag := 'Y' ;
1354: END IF;
1357: G_fatal_err_found:= TRUE;
1358: alloc_errors( p_rule_id, p_run_id, 'S', 'E',
1359: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id, v_cx_task_id);
1360: IF P_DEBUG_MODE = 'Y' THEN
1361: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client Extension returned an invalid source task: '
1362: ||to_char( v_cx_task_id) );
1363: END IF;
1364: v_cx_err_flag := 'Y' ;
1365: END IF;
1443: END IF; /* end exclude_flag = 'Y' */
1444: END LOOP;
1445: END IF; /* end cound>0 */
1446: END IF; /* end src_extn_flag = 'Y' */
1447: pa_debug.G_err_stage:= 'READING SRC LINES FOR EXCLUDES';
1448: IF P_DEBUG_MODE = 'Y' THEN
1449: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1450: END IF;
1451: v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; -- added for bug 3799389
1445: END IF; /* end cound>0 */
1446: END IF; /* end src_extn_flag = 'Y' */
1447: pa_debug.G_err_stage:= 'READING SRC LINES FOR EXCLUDES';
1448: IF P_DEBUG_MODE = 'Y' THEN
1449: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1450: END IF;
1451: v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; -- added for bug 3799389
1452:
1453: FOR source_lines_rec IN c_alloc_source_lines LOOP
1461: , source_lines_rec.project_id
1462: , source_lines_rec.task_id
1463: , v_src_sql_str ) ;
1464: IF P_DEBUG_MODE = 'Y' THEN
1465: pa_debug.write_file('populate_run_sources: ' || 'LOG',v_src_sql_str);
1466: END IF;
1467: /* v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
1468: DBMS_SQL.PARSE(v_source_csr_id, v_src_sql_str, DBMS_SQL.V7) ;
1469: IF source_lines_rec.project_org_id is NOT NULL THEN
1501: dbms_sql.column_value(v_source_csr_id,1,v_src_project_id) ;
1502: dbms_sql.column_value(v_source_csr_id,2,v_src_task_id ) ;
1503: dbms_sql.column_value(v_source_csr_id,3,v_src_top_task_id ) ;
1504: IF P_DEBUG_MODE = 'Y' THEN
1505: pa_debug.write_file('populate_run_sources: ' || 'LOG',to_char(v_src_project_id) || to_char(v_src_task_id) ||
1506: to_char(v_src_top_task_id));
1507: END IF;
1508: insert_alloc_run_sources( p_rule_id
1509: , p_run_id
1520: END IF; /* End if of source excludes from table */
1521: END LOOP; /* endloop read srcs */
1522: DBMS_SQL.CLOSE_CURSOR(v_source_csr_id ); --added for bug 3799389
1523: IF p_source_clnt_extn_flag = 'Y' THEN
1524: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR INCLUDES';
1525: IF P_DEBUG_MODE = 'Y' THEN
1526: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1527: END IF;
1528: pa_client_extn_alloc.source_extn( p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1522: DBMS_SQL.CLOSE_CURSOR(v_source_csr_id ); --added for bug 3799389
1523: IF p_source_clnt_extn_flag = 'Y' THEN
1524: pa_debug.G_err_stage:= 'READING SRC CLIENT EXTENSION FOR INCLUDES';
1525: IF P_DEBUG_MODE = 'Y' THEN
1526: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1527: END IF;
1528: pa_client_extn_alloc.source_extn( p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1529: IF P_DEBUG_MODE = 'Y' THEN
1530: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client return message: '|| v_err_message);
1526: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1527: END IF;
1528: pa_client_extn_alloc.source_extn( p_rule_id, v_src_extn_tabtype,v_status,v_err_message);
1529: IF P_DEBUG_MODE = 'Y' THEN
1530: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client return message: '|| v_err_message);
1531: END IF;
1532: IF nvl(v_status,0) <> 0 then
1533: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
1534: alloc_errors(p_rule_id, p_run_id, 'S', 'E', v_err_message, TRUE) ;
1543: G_fatal_err_found:= TRUE;
1544: alloc_errors( p_rule_id, p_run_id, 'S', 'E',
1545: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id);
1546: IF P_DEBUG_MODE = 'Y' THEN
1547: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client Extension returned an invalid source project: '
1548: || to_char(v_cx_project_id));
1549: END IF;
1550: v_cx_err_flag := 'Y' ;
1551: END IF;
1554: G_fatal_err_found:= TRUE;
1555: alloc_errors( p_rule_id, p_run_id, 'S', 'E',
1556: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id, v_cx_task_id);
1557: IF P_DEBUG_MODE = 'Y' THEN
1558: pa_debug.write_file('populate_run_sources: ' || 'LOG','Client Extension returned an invalid source task: '
1559: ||to_char( v_cx_task_id) );
1560: END IF;
1561: v_cx_err_flag := 'Y' ;
1562: END IF;
1639: END IF; /* end exclude_flag = 'N' */
1640: END LOOP;
1641: END IF; /* if count>0 */
1642: END IF; /* end src_extn_flag = 'Y' */
1643: pa_debug.G_err_stage:= 'READING SRC LINES FOR INCLUDES';
1644: v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; -- added for bug 3799389
1645: FOR source_lines_rec IN c_alloc_source_lines LOOP
1646: IF (source_lines_rec.exclude_flag = 'N') THEN
1647: pa_debug.G_err_stage:= 'EXPLODING SOURCE LINES FOR INCLUDES';
1643: pa_debug.G_err_stage:= 'READING SRC LINES FOR INCLUDES';
1644: v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; -- added for bug 3799389
1645: FOR source_lines_rec IN c_alloc_source_lines LOOP
1646: IF (source_lines_rec.exclude_flag = 'N') THEN
1647: pa_debug.G_err_stage:= 'EXPLODING SOURCE LINES FOR INCLUDES';
1648: IF P_DEBUG_MODE = 'Y' THEN
1649: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1650: END IF;
1651: build_src_sql( source_lines_rec.project_org_id
1645: FOR source_lines_rec IN c_alloc_source_lines LOOP
1646: IF (source_lines_rec.exclude_flag = 'N') THEN
1647: pa_debug.G_err_stage:= 'EXPLODING SOURCE LINES FOR INCLUDES';
1648: IF P_DEBUG_MODE = 'Y' THEN
1649: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1650: END IF;
1651: build_src_sql( source_lines_rec.project_org_id
1652: , source_lines_rec.project_type
1653: , source_lines_rec.task_org_id
1657: , source_lines_rec.project_id
1658: , source_lines_rec.task_id
1659: , v_src_sql_str ) ;
1660: IF P_DEBUG_MODE = 'Y' THEN
1661: pa_debug.write_file('populate_run_sources: ' || 'LOG',v_src_sql_str);
1662: END IF;
1663: /* v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
1664: IF P_DEBUG_MODE = 'Y' THEN
1665: pa_debug.write_file('populate_run_sources: ' || 'LOG','Parsing the code' );
1661: pa_debug.write_file('populate_run_sources: ' || 'LOG',v_src_sql_str);
1662: END IF;
1663: /* v_source_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
1664: IF P_DEBUG_MODE = 'Y' THEN
1665: pa_debug.write_file('populate_run_sources: ' || 'LOG','Parsing the code' );
1666: END IF;
1667: DBMS_SQL.PARSE(v_source_csr_id, v_src_sql_str, DBMS_SQL.V7) ;
1668: IF P_DEBUG_MODE = 'Y' THEN
1669: pa_debug.write_file('populate_run_sources: ' || 'LOG','After Parsing the code' );
1665: pa_debug.write_file('populate_run_sources: ' || 'LOG','Parsing the code' );
1666: END IF;
1667: DBMS_SQL.PARSE(v_source_csr_id, v_src_sql_str, DBMS_SQL.V7) ;
1668: IF P_DEBUG_MODE = 'Y' THEN
1669: pa_debug.write_file('populate_run_sources: ' || 'LOG','After Parsing the code' );
1670: END IF;
1671: IF source_lines_rec.project_org_id is NOT NULL THEN
1672: DBMS_SQL.BIND_VARIABLE(v_source_csr_id,':lp_project_org_id',source_lines_rec.project_org_id) ;
1673: END IF ;
1695: DBMS_SQL.DEFINE_COLUMN(v_source_csr_id,1,v_src_project_id ) ;
1696: DBMS_SQL.DEFINE_COLUMN(v_source_csr_id,2,v_src_task_id ) ;
1697: DBMS_SQL.DEFINE_COLUMN(v_source_csr_id,3,v_src_top_task_id ) ;
1698: IF P_DEBUG_MODE = 'Y' THEN
1699: pa_debug.write_file('populate_run_sources: ' || 'LOG','Executing the code' );
1700: END IF;
1701: v_dummy := DBMS_SQL.execute(v_source_csr_id) ;
1702: IF P_DEBUG_MODE = 'Y' THEN
1703: pa_debug.write_file('populate_run_sources: ' || 'LOG','After Executing the code' );
1699: pa_debug.write_file('populate_run_sources: ' || 'LOG','Executing the code' );
1700: END IF;
1701: v_dummy := DBMS_SQL.execute(v_source_csr_id) ;
1702: IF P_DEBUG_MODE = 'Y' THEN
1703: pa_debug.write_file('populate_run_sources: ' || 'LOG','After Executing the code' );
1704: END IF;
1705: LOOP
1706: IF DBMS_SQL.FETCH_ROWS(v_source_csr_id) = 0 THEN
1707: EXIT ;
1709: dbms_sql.column_value(v_source_csr_id,1,v_src_project_id) ;
1710: dbms_sql.column_value(v_source_csr_id,2,v_src_task_id ) ;
1711: dbms_sql.column_value(v_source_csr_id,3,v_src_top_task_id ) ;
1712: IF P_DEBUG_MODE = 'Y' THEN
1713: pa_debug.write_file('populate_run_sources: ' || 'LOG',to_char(v_src_project_id) || to_char(v_src_task_id) ||
1714: to_char(v_src_top_task_id));
1715: END IF;
1716: -- check if lowest level task
1717: IF( pa_task_utils.check_child_exists(v_src_task_id)= 0) THEN
1763: END LOOP; /* alloc_source_lines */
1764: DBMS_SQL.CLOSE_CURSOR(v_source_csr_id ); --added for bug 3799389
1765: IF (p_resource_list_id IS NOT NULL ) THEN
1766: /* -- validate_srce_proj_for_RL ----------------------- */
1767: pa_debug.G_err_stage:= 'VALIDATING RSRCE ASSIGNMENTS TO SRC PROJECTS';
1768: IF P_DEBUG_MODE = 'Y' THEN
1769: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1770: END IF;
1771: FOR run_src_proj_rec IN c_alloc_run_src_projects LOOP
1765: IF (p_resource_list_id IS NOT NULL ) THEN
1766: /* -- validate_srce_proj_for_RL ----------------------- */
1767: pa_debug.G_err_stage:= 'VALIDATING RSRCE ASSIGNMENTS TO SRC PROJECTS';
1768: IF P_DEBUG_MODE = 'Y' THEN
1769: pa_debug.write_file('populate_run_sources: ' || 'LOG', pa_debug.G_err_stage);
1770: END IF;
1771: FOR run_src_proj_rec IN c_alloc_run_src_projects LOOP
1772: OPEN C_proj_in_RL( run_src_proj_rec.project_id
1773: , p_resource_list_id
1785: CLOSE C_proj_in_RL;
1786: END LOOP;
1787: END IF; /* p_resource_list_id */
1788: /* restore the old stack */
1789: pa_debug.reset_err_stack;
1790: EXCEPTION
1791: WHEN OTHERS THEN
1792: pa_debug.G_err_code := SQLCODE;
1793: RAISE;
1788: /* restore the old stack */
1789: pa_debug.reset_err_stack;
1790: EXCEPTION
1791: WHEN OTHERS THEN
1792: pa_debug.G_err_code := SQLCODE;
1793: RAISE;
1794: END populate_run_sources;
1795: -- ------------------------------------------------------------
1796: -- insert_alloc_run_targets
1821: x_return_status VARCHAR2(2000) := Null; /* added bug2619977 */
1822: x_msg_count NUMBER := 0 ; /* added bug2619977 */
1823: x_msg_data VARCHAR2(2000) := Null; /* added bug2619977 */
1824: BEGIN
1825: pa_debug.set_err_stack('insert_alloc_run_targets');
1826: pa_debug.G_err_code:= 0;
1827: IF p_dup_targets_flag = 'N'THEN
1828: /* allow insert only if current proj-task does NOT exist */
1829: OPEN target_exists;
1822: x_msg_count NUMBER := 0 ; /* added bug2619977 */
1823: x_msg_data VARCHAR2(2000) := Null; /* added bug2619977 */
1824: BEGIN
1825: pa_debug.set_err_stack('insert_alloc_run_targets');
1826: pa_debug.G_err_code:= 0;
1827: IF p_dup_targets_flag = 'N'THEN
1828: /* allow insert only if current proj-task does NOT exist */
1829: OPEN target_exists;
1830: FETCH target_exists INTO v_dummy;
1849: ,x_msg_count
1850: ,x_msg_data
1851: );
1852: IF P_DEBUG_MODE = 'Y' THEN
1853: pa_debug.write_file('insert_alloc_run_targets: ' || 'LOG','Fetching budget version id for project ['|| to_char(p_project_id) ||
1854: '] Fin plan type ['||to_char(G_basis_fin_plan_Type_id) ||
1855: '] Budget type code ['||G_basis_budget_type_code ||
1856: '] Budget version ['||to_char(x_budget_version_id) ||
1857: '] Return status ['||x_return_status||']' );
1887: , p_last_updated_by
1888: , p_last_update_login );
1889: END IF;
1890: /* restore the old stack */
1891: pa_debug.reset_err_stack;
1892: EXCEPTION
1893: WHEN OTHERS THEN
1894: pa_debug.G_err_code := SQLCODE;
1895: RAISE;
1890: /* restore the old stack */
1891: pa_debug.reset_err_stack;
1892: EXCEPTION
1893: WHEN OTHERS THEN
1894: pa_debug.G_err_code := SQLCODE;
1895: RAISE;
1896: END insert_alloc_run_targets;
1897: -- ------------------------------------------------------------
1898: -- Build_tgt_sql
1930: l_where_str2 VARCHAR2(80) ;
1931: l_where_clause VARCHAR2(1500) ;
1932: v_csr_id INTEGER ;
1933: BEGIN
1934: pa_debug.set_err_stack('build_tgt_sql');
1935: pa_debug.G_err_code:= 0;
1936: l_proj_org_str := ' pp.carrying_out_organization_id = :lp_project_org_id ' ;
1937: l_proj_type_str := ' pp.project_type = :lp_project_type ' ;
1938: l_task_org_str := ' pt.carrying_out_organization_id = :lp_task_org_id ' ;
1931: l_where_clause VARCHAR2(1500) ;
1932: v_csr_id INTEGER ;
1933: BEGIN
1934: pa_debug.set_err_stack('build_tgt_sql');
1935: pa_debug.G_err_code:= 0;
1936: l_proj_org_str := ' pp.carrying_out_organization_id = :lp_project_org_id ' ;
1937: l_proj_type_str := ' pp.project_type = :lp_project_type ' ;
1938: l_task_org_str := ' pt.carrying_out_organization_id = :lp_task_org_id ' ;
1939: l_serv_type_str := ' pt.service_type_code = :lp_service_type_code ' ;
2006: l_where_clause := l_where_str0 || l_where_str1 || ' AND ' || l_where_str2 || ' AND ' || l_expnd_item_str ;
2007: l_where_clause := l_where_clause || ' AND pp.template_flag = ''N''' ;
2008: x_sql_str := l_select_clause || l_from_clause || l_where_clause ;
2009: /* restore the old stack */
2010: pa_debug.reset_err_stack;
2011: EXCEPTION
2012: WHEN OTHERS THEN
2013: pa_debug.G_err_code := SQLCODE;
2014: RAISE ;
2009: /* restore the old stack */
2010: pa_debug.reset_err_stack;
2011: EXCEPTION
2012: WHEN OTHERS THEN
2013: pa_debug.G_err_code := SQLCODE;
2014: RAISE ;
2015: END build_tgt_sql;
2016: -- ------------------------------------------------------------
2017: -- populate_run_targets
2119: WHEN OTHERS THEN
2120: raise;
2121: END check_line_percent;
2122: BEGIN
2123: pa_debug.set_err_stack('populate_run_targets');
2124: pa_debug.G_err_code:= 0;
2125: x_basis_method := p_basis_method;
2126: IF ( p_basis_method = 'FS' OR p_basis_method ='FP') THEN
2127: IF (p_trgt_client_extn = 'Y') THEN
2120: raise;
2121: END check_line_percent;
2122: BEGIN
2123: pa_debug.set_err_stack('populate_run_targets');
2124: pa_debug.G_err_code:= 0;
2125: x_basis_method := p_basis_method;
2126: IF ( p_basis_method = 'FS' OR p_basis_method ='FP') THEN
2127: IF (p_trgt_client_extn = 'Y') THEN
2128: open c_target_line_exists;
2140: close c_target_line_exists;
2141: END IF;/** client_extn = 'Y' **/
2142: END IF; /* end p_basis_method */
2143: IF (p_trgt_client_extn = 'Y') THEN
2144: pa_debug.G_err_stage:= 'READING TRG CLIENT EXTENSION FOR EXCLUDES';
2145: IF P_DEBUG_MODE = 'Y' THEN
2146: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2147: END IF;
2148: pa_client_extn_alloc.target_extn(p_rule_id, v_trg_extn_tabtype,v_status,v_err_message);
2142: END IF; /* end p_basis_method */
2143: IF (p_trgt_client_extn = 'Y') THEN
2144: pa_debug.G_err_stage:= 'READING TRG CLIENT EXTENSION FOR EXCLUDES';
2145: IF P_DEBUG_MODE = 'Y' THEN
2146: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2147: END IF;
2148: pa_client_extn_alloc.target_extn(p_rule_id, v_trg_extn_tabtype,v_status,v_err_message);
2149: IF nvl(v_status,0) <>0 THEN
2150: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
2162: G_fatal_err_found:= TRUE;
2163: alloc_errors( p_rule_id, p_run_id, 'T', 'E',
2164: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id);
2165: IF P_DEBUG_MODE = 'Y' THEN
2166: pa_debug.write_file('populate_run_targets: ' || 'LOG','Client Extension returned an invalid target project: '
2167: ||to_char(v_cx_project_id));
2168: END IF;
2169: v_cx_err_flag := 'Y' ;
2170: END IF;
2173: G_fatal_err_found:= TRUE;
2174: alloc_errors( p_rule_id, p_run_id, 'T', 'E',
2175: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id, v_cx_task_id);
2176: IF P_DEBUG_MODE = 'Y' THEN
2177: pa_debug.write_file('populate_run_targets: ' || 'LOG','Client Extension returned an invalid target task: '
2178: || to_char(v_cx_task_id));
2179: END IF;
2180: v_cx_err_flag := 'Y' ;
2181: END IF;
2218: END IF; /* exclude_flag */
2219: END LOOP;
2220: END IF; /* count */
2221: END IF; /* if p_trgt_client_extn */
2222: pa_debug.G_err_stage:= 'READING TRG LINES FOR EXCLUDES';
2223: IF P_DEBUG_MODE = 'Y' THEN
2224: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2225: END IF;
2226: v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; --added for bug 3799389
2220: END IF; /* count */
2221: END IF; /* if p_trgt_client_extn */
2222: pa_debug.G_err_stage:= 'READING TRG LINES FOR EXCLUDES';
2223: IF P_DEBUG_MODE = 'Y' THEN
2224: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2225: END IF;
2226: v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; --added for bug 3799389
2227: FOR target_lines_rec IN c_alloc_target_lines LOOP
2228: IF (target_lines_rec.exclude_flag = 'Y') THEN
2238: , p_expnd_item_date
2239: , p_limit_target_projects_code
2240: , v_tgt_sql_str ) ;
2241: IF P_DEBUG_MODE = 'Y' THEN
2242: pa_debug.write_file('populate_run_targets: ' || 'LOG',v_tgt_sql_str);
2243: END IF;
2244: /* v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
2245: DBMS_SQL.PARSE(v_target_csr_id, v_tgt_sql_str, DBMS_SQL.V7) ;
2246: IF target_lines_rec.project_org_id is NOT NULL THEN
2284: dbms_sql.column_value(v_target_csr_id,1,v_tgt_project_id) ;
2285: dbms_sql.column_value(v_target_csr_id,2,v_tgt_task_id ) ;
2286: dbms_sql.column_value(v_target_csr_id,3,v_tgt_top_task_id ) ;
2287: IF P_DEBUG_MODE = 'Y' THEN
2288: pa_debug.write_file('populate_run_targets: ' || 'LOG',to_char(v_tgt_project_id) || to_char(v_tgt_task_id) ||
2289: to_char(v_tgt_top_task_id));
2290: END IF;
2291: insert_alloc_run_targets( p_rule_id
2292: , p_run_id
2306: END IF;
2307: END LOOP; /* for c_alloc_target_lines */
2308: DBMS_SQL.CLOSE_CURSOR(v_target_csr_id ); --added for bug 3799389
2309: IF (p_trgt_client_extn = 'Y') THEN
2310: pa_debug.G_err_stage:= 'READING TRG CLIENT EXTENSION FOR INCLUDES';
2311: IF P_DEBUG_MODE = 'Y' THEN
2312: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2313: END IF;
2314: pa_client_extn_alloc.target_extn( p_rule_id, v_trg_extn_tabtype,v_status, v_err_message);
2308: DBMS_SQL.CLOSE_CURSOR(v_target_csr_id ); --added for bug 3799389
2309: IF (p_trgt_client_extn = 'Y') THEN
2310: pa_debug.G_err_stage:= 'READING TRG CLIENT EXTENSION FOR INCLUDES';
2311: IF P_DEBUG_MODE = 'Y' THEN
2312: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2313: END IF;
2314: pa_client_extn_alloc.target_extn( p_rule_id, v_trg_extn_tabtype,v_status, v_err_message);
2315: IF nvl(v_status,0) <>0 THEN
2316: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
2375: END IF; /* v_cx_exclude_flag = N */
2376: END LOOP;
2377: END IF; /* if count>0 */
2378: END IF; /* trg_lnc_extn_flag */
2379: pa_debug.G_err_stage:= 'READING TRG LINES FOR INCLUDES';
2380: IF P_DEBUG_MODE = 'Y' THEN
2381: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2382: END IF;
2383: v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; --added for bug 3799389
2377: END IF; /* if count>0 */
2378: END IF; /* trg_lnc_extn_flag */
2379: pa_debug.G_err_stage:= 'READING TRG LINES FOR INCLUDES';
2380: IF P_DEBUG_MODE = 'Y' THEN
2381: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2382: END IF;
2383: v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; --added for bug 3799389
2384: FOR target_lines_rec IN c_alloc_target_lines LOOP
2385: IF (target_lines_rec.exclude_flag = 'N') THEN
2395: , p_expnd_item_date
2396: , p_limit_target_projects_code
2397: , v_tgt_sql_str ) ;
2398: IF P_DEBUG_MODE = 'Y' THEN
2399: pa_debug.write_file('populate_run_targets: ' || 'LOG',v_tgt_sql_str);
2400: END IF;
2401: /* v_target_csr_id := DBMS_SQL.OPEN_CURSOR ; commented for bug 3799389 */
2402: DBMS_SQL.PARSE(v_target_csr_id, v_tgt_sql_str, DBMS_SQL.V7) ;
2403: IF target_lines_rec.project_org_id is NOT NULL THEN
2441: dbms_sql.column_value(v_target_csr_id,1,v_tgt_project_id) ;
2442: dbms_sql.column_value(v_target_csr_id,2,v_tgt_task_id ) ;
2443: dbms_sql.column_value(v_target_csr_id,3,v_tgt_top_task_id ) ;
2444: IF P_DEBUG_MODE = 'Y' THEN
2445: pa_debug.write_file('populate_run_targets: ' || 'LOG','Exploded proj/tasks: '|| to_char(v_tgt_project_id) ||
2446: to_char(v_tgt_task_id) ||
2447: to_char(v_tgt_top_task_id));
2448: END IF;
2449: /* since always lowest chargeable task, check whether to exclude */
2470: END LOOP;
2471: DBMS_SQL.CLOSE_CURSOR(v_target_csr_id ); --added for bug 3799389
2472: /* -- validate budget_entry_level_code for trg_proj --------------- */
2473: IF( p_bas_budget_entry_method_code IS NOT NULL) THEN
2474: pa_debug.G_err_stage:= 'VALIDATING BASIS BUDGET ENTRY METHOD TO TRG PROJECTS';
2475: IF P_DEBUG_MODE = 'Y' THEN
2476: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2477: END IF;
2478: OPEN c_rule_entry_level_code;
2472: /* -- validate budget_entry_level_code for trg_proj --------------- */
2473: IF( p_bas_budget_entry_method_code IS NOT NULL) THEN
2474: pa_debug.G_err_stage:= 'VALIDATING BASIS BUDGET ENTRY METHOD TO TRG PROJECTS';
2475: IF P_DEBUG_MODE = 'Y' THEN
2476: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2477: END IF;
2478: OPEN c_rule_entry_level_code;
2479: FETCH c_rule_entry_level_code INTO v_rule_level_code;
2480: CLOSE c_rule_entry_level_code;
2491: END LOOP;
2492: END IF; /* p_bas_budget_entry_method_code */
2493: /* -- validate_trg_proj_for_RL ----------------------- */
2494: IF( p_resource_list_id IS NOT NULL ) THEN
2495: pa_debug.G_err_stage:= 'VALIDATING RSRCE ASSIGNMENTS TO TRG PROJECTS';
2496: IF P_DEBUG_MODE = 'Y' THEN
2497: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2498: END IF;
2499: FOR run_trg_proj_rec IN c_alloc_run_trg_projects LOOP
2493: /* -- validate_trg_proj_for_RL ----------------------- */
2494: IF( p_resource_list_id IS NOT NULL ) THEN
2495: pa_debug.G_err_stage:= 'VALIDATING RSRCE ASSIGNMENTS TO TRG PROJECTS';
2496: IF P_DEBUG_MODE = 'Y' THEN
2497: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2498: END IF;
2499: FOR run_trg_proj_rec IN c_alloc_run_trg_projects LOOP
2500: OPEN C_proj_in_RL( run_trg_proj_rec.project_id
2501: , p_resource_list_id
2515: CLOSE C_proj_in_RL;
2516: END LOOP;
2517: END IF;
2518: IF ( x_basis_method IN ( 'FS', 'FP') AND check_line_percent <> 100 ) THEN
2519: pa_debug.G_err_stage:= 'VALIDATING TRG LINE PERCENT';
2520: IF P_DEBUG_MODE = 'Y' THEN
2521: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2522: END IF;
2523: G_fatal_err_found := TRUE;
2517: END IF;
2518: IF ( x_basis_method IN ( 'FS', 'FP') AND check_line_percent <> 100 ) THEN
2519: pa_debug.G_err_stage:= 'VALIDATING TRG LINE PERCENT';
2520: IF P_DEBUG_MODE = 'Y' THEN
2521: pa_debug.write_file('populate_run_targets: ' || 'LOG', pa_debug.G_err_stage);
2522: END IF;
2523: G_fatal_err_found := TRUE;
2524: alloc_errors( p_rule_id, p_run_id,
2525: 'T', 'E',
2525: 'T', 'E',
2526: 'PA_AL_LINE_PRCNT_NOT_100');
2527: END IF;
2528: /* restore the old stack */
2529: pa_debug.reset_err_stack;
2530: EXCEPTION
2531: WHEN OTHERS THEN
2532: pa_debug.G_err_code := SQLCODE;
2533: RAISE;
2528: /* restore the old stack */
2529: pa_debug.reset_err_stack;
2530: EXCEPTION
2531: WHEN OTHERS THEN
2532: pa_debug.G_err_code := SQLCODE;
2533: RAISE;
2534: END populate_run_targets;
2535: -- ------------------------------------------------------------
2536: -- insert_alloc_run_GL_det
2548: , p_source_percent IN NUMBER
2549: , p_amount IN NUMBER
2550: , p_eligible_amount IN NUMBER ) IS
2551: BEGIN
2552: pa_debug.set_err_stack('insert_alloc_run_GL_det');
2553: INSERT INTO pa_alloc_run_gl_det(
2554: RUN_ID
2555: , RULE_ID
2556: , LINE_NUM
2577: , p_last_update_login
2578: , p_source_percent
2579: , p_amount
2580: , p_eligible_amount);
2581: pa_debug.reset_err_stack;
2582: EXCEPTION
2583: WHEN OTHERS THEN
2584: pa_debug.G_err_code := SQLCODE;
2585: RAISE;
2580: , p_eligible_amount);
2581: pa_debug.reset_err_stack;
2582: EXCEPTION
2583: WHEN OTHERS THEN
2584: pa_debug.G_err_code := SQLCODE;
2585: RAISE;
2586: END insert_alloc_run_GL_det;
2587: -- ------------------------------------------------------------
2588: -- calculate_src_GL_amounts
2636: v_period_type VARCHAR2(10);
2637: v_amount NUMBER;
2638: v_pool_percent NUMBER;
2639: BEGIN
2640: pa_debug.set_err_stack('calculate_src_GL_amounts');
2641: pa_debug.G_err_stage:= 'Getting gl_account_details';
2642: IF P_DEBUG_MODE = 'Y' THEN
2643: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', pa_debug.G_err_stage);
2644: END IF;
2637: v_amount NUMBER;
2638: v_pool_percent NUMBER;
2639: BEGIN
2640: pa_debug.set_err_stack('calculate_src_GL_amounts');
2641: pa_debug.G_err_stage:= 'Getting gl_account_details';
2642: IF P_DEBUG_MODE = 'Y' THEN
2643: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', pa_debug.G_err_stage);
2644: END IF;
2645: OPEN gl_account_dets;
2639: BEGIN
2640: pa_debug.set_err_stack('calculate_src_GL_amounts');
2641: pa_debug.G_err_stage:= 'Getting gl_account_details';
2642: IF P_DEBUG_MODE = 'Y' THEN
2643: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', pa_debug.G_err_stage);
2644: END IF;
2645: OPEN gl_account_dets;
2646: FETCH gl_account_dets
2647: INTO v_sob_id, v_period_type, v_currency_code ;
2646: FETCH gl_account_dets
2647: INTO v_sob_id, v_period_type, v_currency_code ;
2648: CLOSE gl_account_dets;
2649: IF P_DEBUG_MODE = 'Y' THEN
2650: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_SOB_ID is: '|| to_char(v_sob_id) );
2651: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_currency_code is: '|| v_currency_code );
2652: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_period_type is: '|| v_period_type );
2653: END IF;
2654: OPEN get_pool_percent;
2647: INTO v_sob_id, v_period_type, v_currency_code ;
2648: CLOSE gl_account_dets;
2649: IF P_DEBUG_MODE = 'Y' THEN
2650: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_SOB_ID is: '|| to_char(v_sob_id) );
2651: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_currency_code is: '|| v_currency_code );
2652: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_period_type is: '|| v_period_type );
2653: END IF;
2654: OPEN get_pool_percent;
2655: FETCH get_pool_percent
2648: CLOSE gl_account_dets;
2649: IF P_DEBUG_MODE = 'Y' THEN
2650: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_SOB_ID is: '|| to_char(v_sob_id) );
2651: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_currency_code is: '|| v_currency_code );
2652: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_period_type is: '|| v_period_type );
2653: END IF;
2654: OPEN get_pool_percent;
2655: FETCH get_pool_percent
2656: INTO v_pool_percent;
2655: FETCH get_pool_percent
2656: INTO v_pool_percent;
2657: CLOSE get_pool_percent;
2658: IF P_DEBUG_MODE = 'Y' THEN
2659: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_pool_percent is: '|| to_char(v_pool_percent));
2660: END IF;
2661: FOR gl_source_rec IN gl_sources LOOP
2662: OPEN get_gl_amount( v_sob_id
2663: , gl_source_rec.source_ccid
2666: , p_amount_type );
2667: FETCH get_gl_amount INTO v_amount;
2668: IF (get_gl_amount%NOTFOUND) THEN
2669: IF P_DEBUG_MODE = 'Y' THEN
2670: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'no amount found for the source');
2671: END IF;
2672: v_amount := 0; /* for bug 2154559 */
2673: alloc_errors( p_rule_id, p_run_id, 'S', 'W',
2674: 'PA_AL_NO_GL_BALANCES');
2677: -- Commented the following line as a part of fixing rounding issues.
2678: -- The rounding is done for eligible amount
2679: -- v_amount:= pa_currency.round_currency_amt( NVl(v_amount,0) );
2680: IF P_DEBUG_MODE = 'Y' THEN
2681: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'v_amount is: '|| to_char(v_amount) );
2682: END IF;
2683: /* then insert into gl_source_det */
2684: insert_alloc_run_GL_det( p_run_id
2685: , p_rule_id
2697: (gl_source_rec.source_percent/100)*
2698: ( v_pool_percent/100))
2699: ) ;
2700: IF P_DEBUG_MODE = 'Y' THEN
2701: pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'After insert into alloc_run_Gl' );
2702: END IF;
2703: END LOOP;
2704: /* calculate total GL pool amount */
2705: OPEN gl_eligible_amount ;
2705: OPEN gl_eligible_amount ;
2706: FETCH gl_eligible_amount INTO v_amount;
2707: CLOSE gl_eligible_amount ;
2708: x_gl_src_amount:= NVL(v_amount, 0);
2709: pa_debug.reset_err_stack;
2710: EXCEPTION
2711: WHEN OTHERS THEN
2712: pa_debug.G_err_code:= SQLCODE;
2713: RAISE;
2708: x_gl_src_amount:= NVL(v_amount, 0);
2709: pa_debug.reset_err_stack;
2710: EXCEPTION
2711: WHEN OTHERS THEN
2712: pa_debug.G_err_code:= SQLCODE;
2713: RAISE;
2714: END calculate_src_GL_amounts;
2715: -- ------------------------------------------------------------
2716: -- get_trg_line_proj_task_count
2723: FROM pa_alloc_run_targets
2724: WHERE run_id = p_run_id
2725: AND line_num = p_line_num;
2726: BEGIN
2727: pa_debug.set_err_stack ('Get Proj_task Count For Each Target Line');
2728: OPEN C_get_count;
2729: FETCH C_get_count INTO x_count;
2730: CLOSE C_get_count;
2731: pa_debug.reset_err_stack ;
2727: pa_debug.set_err_stack ('Get Proj_task Count For Each Target Line');
2728: OPEN C_get_count;
2729: FETCH C_get_count INTO x_count;
2730: CLOSE C_get_count;
2731: pa_debug.reset_err_stack ;
2732: return x_count;
2733: EXCEPTION
2734: WHEN OTHERS THEN
2735: return (SQLCODE);
2743: , p_project_id IN NUMBER
2744: , p_task_id IN NUMBER
2745: , p_amount IN NUMBER ) IS
2746: BEGIN
2747: pa_debug.set_err_stack('Insert missing project costs');
2748: INSERT INTO pa_alloc_missing_costs (
2749: RUN_ID
2750: , TYPE_CODE
2751: , CREATION_DATE
2766: , G_last_update_login
2767: , p_project_id
2768: , p_task_id
2769: , p_amount);
2770: pa_debug.reset_err_stack;
2771: EXCEPTION
2772: WHEN OTHERS THEN
2773: pa_debug.G_err_code := SQLCODE;
2774: RAISE;
2769: , p_amount);
2770: pa_debug.reset_err_stack;
2771: EXCEPTION
2772: WHEN OTHERS THEN
2773: pa_debug.G_err_code := SQLCODE;
2774: RAISE;
2775: END insert_missing_costs;
2776: -- ------------------------------------------------------------
2777: -- get_sunk_cost
2899: v_quarter_num NUMBER;
2900: v_fiscal_year NUMBER;
2901: v_period_num NUMBER;
2902: BEGIN
2903: pa_debug.set_err_stack('Get_Sunk_Cost');
2904: IF P_DEBUG_MODE = 'Y' THEN
2905: pa_debug.write_file('get_sunk_cost: ' || 'LOG', 'p_amount_type '||p_amount_type);
2906: END IF;
2907: IF( p_amount_type = 'ITD') THEN
2901: v_period_num NUMBER;
2902: BEGIN
2903: pa_debug.set_err_stack('Get_Sunk_Cost');
2904: IF P_DEBUG_MODE = 'Y' THEN
2905: pa_debug.write_file('get_sunk_cost: ' || 'LOG', 'p_amount_type '||p_amount_type);
2906: END IF;
2907: IF( p_amount_type = 'ITD') THEN
2908: v_fiscal_year:= NULL;
2909: v_quarter_num:= NULL;
2924: v_src_sunk_cost :=0;
2925: v_trg_sunk_cost :=0;
2926: /* calculate total src_sunk_cost */
2927: IF P_DEBUG_MODE = 'Y' THEN
2928: pa_debug.write_file('get_sunk_cost: ' || 'LOG',' calculate total src_sunk_cost' );
2929: END IF;
2930: FOR src_sunk_cost_rec IN C_src_sunk_cost( v_fiscal_year
2931: , v_quarter_num
2932: , v_period_num ) LOOP
2930: FOR src_sunk_cost_rec IN C_src_sunk_cost( v_fiscal_year
2931: , v_quarter_num
2932: , v_period_num ) LOOP
2933: IF P_DEBUG_MODE = 'Y' THEN
2934: pa_debug.write_file('get_sunk_cost: ' || 'LOG','src_sunk_cost :'|| to_char(src_sunk_cost_rec.eligible_amount) );
2935: END IF;
2936: if p_src_proj_id is NULL then
2937: insert_missing_costs( p_run_id
2938: ,'S'
2943: v_src_sunk_cost := v_src_sunk_cost + src_sunk_cost_rec.eligible_amount;
2944: END LOOP;
2945: x_src_sunk_cost := nvl(v_src_sunk_cost, 0) ;
2946: IF P_DEBUG_MODE = 'Y' THEN
2947: pa_debug.write_file('get_sunk_cost: ' || 'LOG','v_src_sunk_cost =' ||to_char(v_src_sunk_cost) );
2948: END IF;
2949: /* calculate total trgt_sunk_cost */
2950: if p_src_proj_id is NULL then
2951: IF P_DEBUG_MODE = 'Y' THEN
2948: END IF;
2949: /* calculate total trgt_sunk_cost */
2950: if p_src_proj_id is NULL then
2951: IF P_DEBUG_MODE = 'Y' THEN
2952: pa_debug.write_file('get_sunk_cost: ' || 'LOG',' calculate total tgt_sunk_cost ' || to_char(v_fiscal_year)
2953: || ' ' || to_char(v_quarter_num) || ' '
2954: || to_char(v_period_num) );
2955: END IF;
2956: FOR trg_sunk_cost_rec IN C_trg_sunk_cost( v_fiscal_year
2956: FOR trg_sunk_cost_rec IN C_trg_sunk_cost( v_fiscal_year
2957: , v_quarter_num
2958: , v_period_num ) LOOP
2959: IF P_DEBUG_MODE = 'Y' THEN
2960: pa_debug.write_file('get_sunk_cost: ' || 'LOG','tgt_sunk_cost = '|| to_char(trg_sunk_cost_rec.Total_allocation) );
2961: END IF;
2962: insert_missing_costs( p_run_id
2963: ,'T'
2964: ,trg_sunk_cost_rec.project_id
2967: v_trg_sunk_cost := v_trg_sunk_cost + trg_sunk_cost_rec.Total_allocation;
2968: END LOOP;
2969: x_tgt_sunk_cost := nvl(v_trg_sunk_cost,0) ;
2970: IF P_DEBUG_MODE = 'Y' THEN
2971: pa_debug.write_file('get_sunk_cost: ' || 'LOG','v_tgt_sunk_cost =' ||to_char(x_tgt_sunk_cost) );
2972: END IF;
2973: End if ;
2974: v_tot_sunk_cost := NVL(v_src_sunk_cost,0) - NVL(v_trg_sunk_cost,0);
2975: pa_debug.reset_err_stack;
2971: pa_debug.write_file('get_sunk_cost: ' || 'LOG','v_tgt_sunk_cost =' ||to_char(x_tgt_sunk_cost) );
2972: END IF;
2973: End if ;
2974: v_tot_sunk_cost := NVL(v_src_sunk_cost,0) - NVL(v_trg_sunk_cost,0);
2975: pa_debug.reset_err_stack;
2976: EXCEPTION
2977: WHEN OTHERS THEN
2978: null;
2979: -- return (SQLCODE);
3013: v_fiscal_year NUMBER;
3014: v_period_num NUMBER;
3015: BEGIN
3016: v_prev_amount :=0;
3017: pa_debug.set_err_stack('Get_Previous_Alloc_Amount');
3018: IF( p_amount_type = 'ITD') THEN
3019: v_fiscal_year:= NULL;
3020: v_quarter_num:= NULL;
3021: v_period_num := NULL;
3037: OPEN C_prev_amount ( v_quarter_num, v_fiscal_year, v_period_num );
3038: FETCH C_prev_amount INTO v_prev_amount;
3039: CLOSE C_prev_amount;
3040: IF P_DEBUG_MODE = 'Y' THEN
3041: pa_debug.write_file('get_previous_alloc_amnt: ' || 'LOG','rule_id:run_id:year:quarter:period:project:task:Amt='||
3042: to_char(p_rule_id)||': '|| to_char(p_run_id)||': '||
3043: to_char(v_fiscal_year)||': '|| to_char(v_quarter_num)||': '||
3044: to_char(v_period_num)||': '|| to_char(p_project_id)||': '||
3045: to_char(p_task_id)||':'||to_char(v_prev_amount));
3043: to_char(v_fiscal_year)||': '|| to_char(v_quarter_num)||': '||
3044: to_char(v_period_num)||': '|| to_char(p_project_id)||': '||
3045: to_char(p_task_id)||':'||to_char(v_prev_amount));
3046: END IF;
3047: pa_debug.reset_err_stack;
3048: return nvl(v_prev_amount,0);
3049: EXCEPTION
3050: WHEN NO_DATA_FOUND THEN
3051: return 0;
3106: v_attribute10 VARCHAR2(150);
3107: v_status NUMBER ;
3108: v_err_message VARCHAR2(250);
3109: BEGIN
3110: pa_debug.set_err_stack('Insert Alloc Txn Details');
3111: /* Added if condition to avoid calling client extns for capint if p_rule_id =-1 */
3112: IF nvl(p_rule_id,0) <> -1 Then
3113: --For Capitalized Interest the DFF will be derived outside the
3114: --table handler by calling client extn
3137: ) ;
3138: IF nvl(v_status,0) <> 0 then
3139: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
3140: IF P_DEBUG_MODE = 'Y' THEN
3141: pa_debug.write_file('insert_alloc_txn_details: ' || 'LOG',v_err_message);
3142: END IF;
3143: alloc_errors(p_rule_id, p_run_id, 'R', 'E',v_err_message, TRUE) ;
3144: END IF ;
3145: Elsif p_transaction_type='O' then
3167: ) ;
3168: IF nvl(v_status,0) <> 0 then
3169: v_err_message:=nvl(v_err_message,'PA_AL_CE_FAILED');
3170: IF P_DEBUG_MODE = 'Y' THEN
3171: pa_debug.write_file('insert_alloc_txn_details: ' || 'LOG',v_err_message);
3172: END IF;
3173: alloc_errors(p_rule_id, p_run_id, 'R', 'E',v_err_message, TRUE) ;
3174: END IF ;
3175: End if;
3262: ,p_CINT_CURRENT_BASIS_AMT
3263: ,p_REJECTION_CODE
3264: ,p_STATUS_CODE
3265: );
3266: pa_debug.reset_err_stack;
3267: EXCEPTION
3268: WHEN OTHERS THEN
3269: pa_debug.G_err_code := SQLCODE;
3270: RAISE;
3265: );
3266: pa_debug.reset_err_stack;
3267: EXCEPTION
3268: WHEN OTHERS THEN
3269: pa_debug.G_err_code := SQLCODE;
3270: RAISE;
3271: END insert_alloc_txn_details;
3272: -- ------------------------------------------------------------
3273: -- create_target_txns
3365: return (SQLCODE);
3366: -- return 0 ;
3367: END get_basis_factor ;
3368: BEGIN
3369: pa_debug.set_err_stack('Create_Target_txns');
3370: pa_debug.G_err_stage:= 'CREATING TARGET TRANSACTIONS';
3371: IF P_DEBUG_MODE = 'Y' THEN
3372: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3373: pa_debug.write_file('create_target_txns: ' || 'LOG','p_pool_amount : '|| to_char(p_pool_amount));
3366: -- return 0 ;
3367: END get_basis_factor ;
3368: BEGIN
3369: pa_debug.set_err_stack('Create_Target_txns');
3370: pa_debug.G_err_stage:= 'CREATING TARGET TRANSACTIONS';
3371: IF P_DEBUG_MODE = 'Y' THEN
3372: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3373: pa_debug.write_file('create_target_txns: ' || 'LOG','p_pool_amount : '|| to_char(p_pool_amount));
3374: END IF;
3368: BEGIN
3369: pa_debug.set_err_stack('Create_Target_txns');
3370: pa_debug.G_err_stage:= 'CREATING TARGET TRANSACTIONS';
3371: IF P_DEBUG_MODE = 'Y' THEN
3372: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3373: pa_debug.write_file('create_target_txns: ' || 'LOG','p_pool_amount : '|| to_char(p_pool_amount));
3374: END IF;
3375: v_tot_pool_amount:= p_pool_amount;
3376: IF p_allocation_method = 'I' THEN
3369: pa_debug.set_err_stack('Create_Target_txns');
3370: pa_debug.G_err_stage:= 'CREATING TARGET TRANSACTIONS';
3371: IF P_DEBUG_MODE = 'Y' THEN
3372: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3373: pa_debug.write_file('create_target_txns: ' || 'LOG','p_pool_amount : '|| to_char(p_pool_amount));
3374: END IF;
3375: v_tot_pool_amount:= p_pool_amount;
3376: IF p_allocation_method = 'I' THEN
3377: /* if allocation method is Incremental then consider sunk cost */
3385: , v_tgt_sunk_cost
3386: , NULL );
3387: v_sunk_cost :=NVL( v_src_sunk_cost,0) - NVL( v_tgt_sunk_cost,0) ;
3388: IF P_DEBUG_MODE = 'Y' THEN
3389: pa_debug.write_file('create_target_txns: ' || 'LOG','v_src_sunk_cost is: '||to_char(v_src_sunk_cost) );
3390: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tgt_sunk_cost is: '||to_char(v_tgt_sunk_cost) );
3391: pa_debug.write_file('create_target_txns: ' || 'LOG','v_sunk_cost is: '||to_char(v_sunk_cost) );
3392: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3393: END IF;
3386: , NULL );
3387: v_sunk_cost :=NVL( v_src_sunk_cost,0) - NVL( v_tgt_sunk_cost,0) ;
3388: IF P_DEBUG_MODE = 'Y' THEN
3389: pa_debug.write_file('create_target_txns: ' || 'LOG','v_src_sunk_cost is: '||to_char(v_src_sunk_cost) );
3390: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tgt_sunk_cost is: '||to_char(v_tgt_sunk_cost) );
3391: pa_debug.write_file('create_target_txns: ' || 'LOG','v_sunk_cost is: '||to_char(v_sunk_cost) );
3392: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3393: END IF;
3394: v_tot_pool_amount := NVL(v_tot_pool_amount,0) + NVl(v_sunk_cost, 0);
3387: v_sunk_cost :=NVL( v_src_sunk_cost,0) - NVL( v_tgt_sunk_cost,0) ;
3388: IF P_DEBUG_MODE = 'Y' THEN
3389: pa_debug.write_file('create_target_txns: ' || 'LOG','v_src_sunk_cost is: '||to_char(v_src_sunk_cost) );
3390: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tgt_sunk_cost is: '||to_char(v_tgt_sunk_cost) );
3391: pa_debug.write_file('create_target_txns: ' || 'LOG','v_sunk_cost is: '||to_char(v_sunk_cost) );
3392: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3393: END IF;
3394: v_tot_pool_amount := NVL(v_tot_pool_amount,0) + NVl(v_sunk_cost, 0);
3395: IF P_DEBUG_MODE = 'Y' THEN
3388: IF P_DEBUG_MODE = 'Y' THEN
3389: pa_debug.write_file('create_target_txns: ' || 'LOG','v_src_sunk_cost is: '||to_char(v_src_sunk_cost) );
3390: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tgt_sunk_cost is: '||to_char(v_tgt_sunk_cost) );
3391: pa_debug.write_file('create_target_txns: ' || 'LOG','v_sunk_cost is: '||to_char(v_sunk_cost) );
3392: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3393: END IF;
3394: v_tot_pool_amount := NVL(v_tot_pool_amount,0) + NVl(v_sunk_cost, 0);
3395: IF P_DEBUG_MODE = 'Y' THEN
3396: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_after_ is: '||to_char(v_tot_pool_amount) );
3392: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_before_ is: '||to_char(v_tot_pool_amount) );
3393: END IF;
3394: v_tot_pool_amount := NVL(v_tot_pool_amount,0) + NVl(v_sunk_cost, 0);
3395: IF P_DEBUG_MODE = 'Y' THEN
3396: pa_debug.write_file('create_target_txns: ' || 'LOG','v_tot_pool_amount_after_ is: '||to_char(v_tot_pool_amount) );
3397: END IF;
3398: END IF;
3399: IF( p_basis_method = 'S') THEN
3400: /* get count of total number of project/tasks for targets */
3416: END IF; /* end v_count2 */
3417: END IF; /* if p_basis_method */
3418: IF ( p_basis_method in ( 'P','FP','C' )) THEN
3419: IF P_DEBUG_MODE = 'Y' THEN
3420: pa_debug.write_file('create_target_txns: ' || 'LOG','In basis_method'||p_basis_method);
3421: END IF;
3422: v_factor := get_basis_factor( p_run_id,
3423: p_basis_method,
3424: run_target_rec.line_num,
3424: run_target_rec.line_num,
3425: run_target_rec.project_id,
3426: run_target_rec.task_id ) ;
3427: IF P_DEBUG_MODE = 'Y' THEN
3428: pa_debug.write_file('create_target_txns: ' || 'LOG','V_factor is :'|| to_char(v_factor));
3429: END IF;
3430: END IF ;
3431: /* if basis amount is zero for a particular proj-task then show warning */
3432: IF (v_factor = 0 ) THEN
3435: run_target_rec.project_id, run_target_rec.task_id );
3436: END IF;
3437: v_curr_alloc_amount := v_tot_pool_amount * v_factor ;
3438: IF P_DEBUG_MODE = 'Y' THEN
3439: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_current_alloc_amount :'||to_char(v_curr_alloc_amount) );
3440: END IF;
3441: v_tot_alloc_amount := v_curr_alloc_amount;
3442: v_sum_tot_alloc_amt := v_sum_tot_alloc_amt + v_tot_alloc_amount ;
3443: IF (p_allocation_method = 'F') THEN
3457: v_tot_alloc_amount:= pa_currency.round_currency_amt(v_tot_alloc_amount);
3458: v_prev_alloc_amount:= pa_currency.round_currency_amt(v_prev_alloc_amount);
3459: v_curr_alloc_amount:= pa_currency.round_currency_amt(v_curr_alloc_amount);
3460: IF P_DEBUG_MODE = 'Y' THEN
3461: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Amounts after rounding' );
3462: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3463: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3464: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3465: END IF;
3458: v_prev_alloc_amount:= pa_currency.round_currency_amt(v_prev_alloc_amount);
3459: v_curr_alloc_amount:= pa_currency.round_currency_amt(v_curr_alloc_amount);
3460: IF P_DEBUG_MODE = 'Y' THEN
3461: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Amounts after rounding' );
3462: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3463: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3464: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3465: END IF;
3466: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3459: v_curr_alloc_amount:= pa_currency.round_currency_amt(v_curr_alloc_amount);
3460: IF P_DEBUG_MODE = 'Y' THEN
3461: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Amounts after rounding' );
3462: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3463: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3464: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3465: END IF;
3466: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3467: IF P_DEBUG_MODE = 'Y' THEN
3460: IF P_DEBUG_MODE = 'Y' THEN
3461: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Amounts after rounding' );
3462: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3463: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3464: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3465: END IF;
3466: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3467: IF P_DEBUG_MODE = 'Y' THEN
3468: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3462: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tot_alloc_amount:'||to_char(v_tot_alloc_amount) );
3463: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_prev_alloc_amount:'||to_char(v_prev_alloc_amount) );
3464: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3465: END IF;
3466: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3467: IF P_DEBUG_MODE = 'Y' THEN
3468: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3469: END IF;
3470: /* insert into alloc_txn_details */
3464: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_curr_alloc_amount:'||to_char(v_curr_alloc_amount) );
3465: END IF;
3466: pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
3467: IF P_DEBUG_MODE = 'Y' THEN
3468: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3469: END IF;
3470: /* insert into alloc_txn_details */
3471: --
3472: -- Bug: 983057 Do not create txn with zero curren alloc amount
3530: /* get the total allocated */
3531: v_net_alloc_amount := NVl(v_net_alloc_amount,0) + NVL(v_curr_alloc_amount,0) ;
3532: END LOOP;
3533: IF P_DEBUG_MODE = 'Y' THEN
3534: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3535: END IF;
3536: pa_debug.G_err_stage:= 'ALLOCATING REMNANT';
3537: IF P_DEBUG_MODE = 'Y' THEN
3538: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3532: END LOOP;
3533: IF P_DEBUG_MODE = 'Y' THEN
3534: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3535: END IF;
3536: pa_debug.G_err_stage:= 'ALLOCATING REMNANT';
3537: IF P_DEBUG_MODE = 'Y' THEN
3538: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3539: END IF;
3540: If p_allocation_method = 'I' then
3534: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3535: END IF;
3536: pa_debug.G_err_stage:= 'ALLOCATING REMNANT';
3537: IF P_DEBUG_MODE = 'Y' THEN
3538: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3539: END IF;
3540: If p_allocation_method = 'I' then
3541: IF( p_amount_type = 'ITD') THEN
3542: v_fiscal_year:= NULL;
3555: v_quarter_num:= p_quarter_num;
3556: v_period_num := p_period_num;
3557: END IF;
3558: IF P_DEBUG_MODE = 'Y' THEN
3559: pa_debug.write_file('create_target_txns: ' || 'LOG', 'Fetching sum of allocated amounts until the current run' );
3560: END IF;
3561: Open c_sum_of_allocated_amts ;
3562: Fetch c_sum_of_allocated_amts into v_sum_alloc_amts ;
3563: if c_sum_of_allocated_amts%NOTFOUND then
3565: end if ;
3566: Close c_sum_of_allocated_amts ;
3567: End If ;
3568: IF P_DEBUG_MODE = 'Y' THEN
3569: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3570: END IF;
3571: allocate_remnant ( p_run_id, p_pool_amount + v_src_sunk_cost - nvl(v_sum_alloc_amts,0)
3572: , v_remnant_amount);
3573: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3569: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3570: END IF;
3571: allocate_remnant ( p_run_id, p_pool_amount + v_src_sunk_cost - nvl(v_sum_alloc_amts,0)
3572: , v_remnant_amount);
3573: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3574: IF P_DEBUG_MODE = 'Y' THEN
3575: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3576: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3577: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3571: allocate_remnant ( p_run_id, p_pool_amount + v_src_sunk_cost - nvl(v_sum_alloc_amts,0)
3572: , v_remnant_amount);
3573: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3574: IF P_DEBUG_MODE = 'Y' THEN
3575: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3576: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3577: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3578: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3579: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3572: , v_remnant_amount);
3573: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3574: IF P_DEBUG_MODE = 'Y' THEN
3575: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3576: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3577: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3578: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3579: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3580: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3573: pa_debug.G_err_stage:= 'UPDATING ALLOC RUNS WITH AMOUNTS';
3574: IF P_DEBUG_MODE = 'Y' THEN
3575: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3576: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3577: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3578: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3579: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3580: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3581: END IF;
3574: IF P_DEBUG_MODE = 'Y' THEN
3575: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3576: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3577: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3578: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3579: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3580: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3581: END IF;
3582: x_curr_alloc_amount := nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0) ;
3575: pa_debug.write_file('create_target_txns: ' || 'LOG', pa_debug.G_err_stage);
3576: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3577: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3578: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3579: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3580: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3581: END IF;
3582: x_curr_alloc_amount := nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0) ;
3583: UPDATE pa_alloc_runs
3576: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_sum_alloc_amts:'||to_char(v_sum_alloc_amts) );
3577: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_net_alloc_amount:'||to_char(v_net_alloc_amount) );
3578: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_remnant_amount:'||to_char(v_remnant_amount) );
3579: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3580: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3581: END IF;
3582: x_curr_alloc_amount := nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0) ;
3583: UPDATE pa_alloc_runs
3584: SET total_pool_amount = nvl(p_pool_amount,0)
3586: , Missing_source_proj_amt = v_src_sunk_cost
3587: , Missing_target_proj_amt = v_tgt_sunk_cost
3588: , 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)
3589: WHERE run_id = p_run_id;
3590: pa_debug.reset_err_stack;
3591: EXCEPTION
3592: WHEN OTHERS THEN
3593: pa_debug.G_err_code := SQLCODE;
3594: RAISE;
3589: WHERE run_id = p_run_id;
3590: pa_debug.reset_err_stack;
3591: EXCEPTION
3592: WHEN OTHERS THEN
3593: pa_debug.G_err_code := SQLCODE;
3594: RAISE;
3595: END create_target_txns;
3596: -- ------------------------------------------------------------
3597: -- create_offset_txns
3695: v_err_message VARCHAR2(250);
3696: v_returned_amount NUMBER := 0;
3697: v_offset_extn_tabtype PA_CLIENT_EXTN_ALLOC.ALLOC_OFFSET_TABTYPE;
3698: BEGIN
3699: pa_debug.set_err_stack('Create_Offset_txns');
3700: pa_debug.G_err_stage:= 'CREATING OFFSET TRANSACTIONS';
3701: IF P_DEBUG_MODE = 'Y' THEN
3702: pa_debug.write_file('create_offset_txns: ' || 'LOG', pa_debug.G_err_stage);
3703: END IF;
3696: v_returned_amount NUMBER := 0;
3697: v_offset_extn_tabtype PA_CLIENT_EXTN_ALLOC.ALLOC_OFFSET_TABTYPE;
3698: BEGIN
3699: pa_debug.set_err_stack('Create_Offset_txns');
3700: pa_debug.G_err_stage:= 'CREATING OFFSET TRANSACTIONS';
3701: IF P_DEBUG_MODE = 'Y' THEN
3702: pa_debug.write_file('create_offset_txns: ' || 'LOG', pa_debug.G_err_stage);
3703: END IF;
3704: IF (p_offset_method = 'A') THEN -- Offset Method: Same as source
3698: BEGIN
3699: pa_debug.set_err_stack('Create_Offset_txns');
3700: pa_debug.G_err_stage:= 'CREATING OFFSET TRANSACTIONS';
3701: IF P_DEBUG_MODE = 'Y' THEN
3702: pa_debug.write_file('create_offset_txns: ' || 'LOG', pa_debug.G_err_stage);
3703: END IF;
3704: IF (p_offset_method = 'A') THEN -- Offset Method: Same as source
3705: FOR offset_det_rec IN c_offset_det LOOP
3706: v_tot_offset_amount := offset_det_rec.eligible_amount * (-1);
3784: END IF ;
3785: END LOOP;
3786: ELSIF (p_offset_method = 'B') THEN -- Offset Method: same as source project, CE tasks
3787: IF P_DEBUG_MODE = 'Y' THEN
3788: pa_debug.write_file('create_offset_txns: ' || 'LOG', 'Test offset method B ');
3789: END IF;
3790: FOR offset_proj_sum_rec IN c_offset_proj_sum LOOP
3791: pa_client_extn_alloc.offset_task_extn(p_rule_id, offset_proj_sum_rec.project_id, v_task_id,v_status,v_err_message);
3792: IF nvl(v_status,0) <>0 THEN
3799: G_fatal_err_found:= TRUE;
3800: alloc_errors( p_rule_id, p_run_id, 'O', 'E',
3801: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',NULL, v_task_id);
3802: IF P_DEBUG_MODE = 'Y' THEN
3803: pa_debug.write_file('create_offset_txns: ' || 'LOG','Client Extension returned an invalid offset task: '
3804: || to_char(v_cx_task_id));
3805: END IF;
3806: END IF;
3807: IF P_DEBUG_MODE = 'Y' THEN
3804: || to_char(v_cx_task_id));
3805: END IF;
3806: END IF;
3807: IF P_DEBUG_MODE = 'Y' THEN
3808: pa_debug.write_file('create_offset_txns: ' || 'LOG', 'v_task_id: '||to_char(v_task_id) );
3809: END IF;
3810: v_tot_offset_amount:= offset_proj_sum_rec.eligible_amount * (-1);
3811: IF (p_allocation_method = 'I') THEN /* incremental */
3812: v_prev_offset_amount := get_previous_alloc_amnt( p_rule_id
3830: open C_prev_offset_task_CE( offset_proj_sum_rec.project_id,v_task_id ) ;
3831: Fetch C_prev_offset_task_CE into v_proj_prev_offset_amt;
3832: Close C_prev_offset_task_CE ;
3833: IF P_DEBUG_MODE = 'Y' THEN
3834: pa_debug.write_file('create_offset_txns: ' || 'LOG', 'offset project:Task:total_amount:src_sunk_cost:prev_offset '
3835: ||to_char(offset_proj_sum_rec.project_id)||':'
3836: ||to_char(v_task_id)|| ':'
3837: ||to_char(v_tot_offset_amount)|| ':'
3838: || to_char(v_src_sunk_cost)||':'
3998: END IF;
3999: /* check whether tot_offset_amount retuned is same as what was passed */
4000: FOR I in 1..v_offset_extn_tabtype.count LOOP
4001: IF P_DEBUG_MODE = 'Y' THEN
4002: pa_debug.write_file('create_offset_txns: ' || 'LOG', 'offset project-Task: '||
4003: to_char(v_offset_extn_tabtype(I).project_id)||'-'||
4004: to_char(v_offset_extn_tabtype(I).task_id) || 'Index :'||to_char(I) );
4005: END IF;
4006: v_cx_project_id:=v_offset_extn_tabtype(I).project_id;
4010: G_fatal_err_found:= TRUE;
4011: alloc_errors( p_rule_id, p_run_id, 'O', 'E',
4012: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id);
4013: IF P_DEBUG_MODE = 'Y' THEN
4014: pa_debug.write_file('create_offset_txns: ' || 'LOG','Client Extension returned an invalid offset project: '
4015: ||to_char(v_cx_project_id) );
4016: END IF;
4017: END IF;
4018: IF is_offset_task_valid(v_cx_project_id,v_cx_task_id ) = 'N' THEN
4019: G_fatal_err_found:= TRUE;
4020: alloc_errors( p_rule_id, p_run_id, 'O', 'E',
4021: 'PA_AL_INV_PROJECT_TASK_IN_CE',FALSE,'Y',v_cx_project_id,v_cx_task_id);
4022: IF P_DEBUG_MODE = 'Y' THEN
4023: pa_debug.write_file('create_offset_txns: ' || 'LOG','Client Extension returned an invalid offset task: '
4024: || to_char(v_cx_task_id ));
4025: END IF;
4026: END IF;
4027: END LOOP;
4128: v_quarter_num:= p_quarter_num;
4129: v_period_num := p_period_num;
4130: END IF;
4131: IF P_DEBUG_MODE = 'Y' THEN
4132: pa_debug.write_file('create_offset_txns: ' || 'LOG',' calculate total offset missing cost'
4133: ||to_char(p_fiscal_year) || ' ' || to_char(v_quarter_num)
4134: || ' ' || to_char(v_period_num) );
4135: END IF;
4136: FOR off_sunk_cost_rec IN C_off_sunk_cost( v_fiscal_year
4136: FOR off_sunk_cost_rec IN C_off_sunk_cost( v_fiscal_year
4137: , v_quarter_num
4138: , v_period_num ) LOOP
4139: IF P_DEBUG_MODE = 'Y' THEN
4140: pa_debug.write_file('create_offset_txns: ' || 'LOG','off_sunk_cost = '|| to_char(off_sunk_cost_rec.Total_allocation) );
4141: END IF;
4142: insert_missing_costs( p_run_id
4143: ,'O'
4144: ,off_sunk_cost_rec.project_id
4146: ,off_sunk_cost_rec.Total_allocation);
4147: v_off_sunk_cost := v_off_sunk_cost + nvl(off_sunk_cost_rec.Total_allocation,0) ;
4148: END LOOP;
4149: IF P_DEBUG_MODE = 'Y' THEN
4150: pa_debug.write_file('create_offset_txns: ' || 'LOG','v_off_sunk_cost =' ||to_char(v_off_sunk_cost) );
4151: END IF;
4152: END IF ;
4153: update pa_alloc_runs
4154: set Missing_offset_proj_amt = nvl(v_off_sunk_cost,0) ,
4153: update pa_alloc_runs
4154: set Missing_offset_proj_amt = nvl(v_off_sunk_cost,0) ,
4155: TOTAL_OFFSETTED_AMOUNT = v_sum_tot_offsets
4156: where run_id = p_run_id ;
4157: pa_debug.reset_err_stack;
4158: EXCEPTION
4159: WHEN OTHERS THEN
4160: pa_debug.G_err_code := SQLCODE;
4161: RAISE;
4156: where run_id = p_run_id ;
4157: pa_debug.reset_err_stack;
4158: EXCEPTION
4159: WHEN OTHERS THEN
4160: pa_debug.G_err_code := SQLCODE;
4161: RAISE;
4162: END create_offset_txns;
4163: -- ------------------------------------------------------------
4164: -- allocate_remnant
4185: v_task_id NUMBER;
4186: v_amount NUMBER;
4187: v_remnant NUMBER;
4188: BEGIN
4189: pa_debug.set_err_stack ('allocate_remnant');
4190: OPEN c_amnt_before_remnant;
4191: FETCH c_amnt_before_remnant INTO v_sum_curr_alloc, v_max_curr_alloc ;
4192: CLOSE c_amnt_before_remnant;
4193: IF P_DEBUG_MODE = 'Y' THEN
4190: OPEN c_amnt_before_remnant;
4191: FETCH c_amnt_before_remnant INTO v_sum_curr_alloc, v_max_curr_alloc ;
4192: CLOSE c_amnt_before_remnant;
4193: IF P_DEBUG_MODE = 'Y' THEN
4194: pa_debug.write_file('allocate_remnant: ' || 'LOG', 'Actual allocated amount is:'|| to_char(p_act_alloc_amount));
4195: END IF;
4196: v_remnant:= p_act_alloc_amount - v_sum_curr_alloc;
4197: IF (v_remnant <>0 ) THEN
4198: OPEN c_remnant_proj_task( v_max_curr_alloc );
4206: AND transaction_type='T'
4207: AND project_id = v_project_id
4208: AND task_id = v_task_id;
4209: END IF;
4210: pa_debug.reset_err_stack ;
4211: EXCEPTION
4212: WHEN OTHERS THEN
4213: pa_debug.G_err_code := SQLCODE;
4214: RAISE;
4209: END IF;
4210: pa_debug.reset_err_stack ;
4211: EXCEPTION
4212: WHEN OTHERS THEN
4213: pa_debug.G_err_code := SQLCODE;
4214: RAISE;
4215: END allocate_remnant;
4216: -- ------------------------------------------------------------
4217: -- insert_alloc_runs
4279: , p_ALLOC_RBS_VERSION In Number default NULL
4280: , p_BASIS_RBS_VERSION In Number default NULL
4281: ) IS
4282: BEGIN
4283: pa_debug.set_err_stack('insert_alloc_runs');
4284: If x_run_id is NULL then
4285: Select pa_alloc_runs_s.nextval
4286: Into x_run_id
4287: From dual;
4416: , p_BASIS_RESOURCE_STRUCT_TYPE
4417: , p_ALLOC_RBS_VERSION
4418: , p_BASIS_RBS_VERSION
4419: ) ;
4420: pa_debug.reset_err_stack;
4421: EXCEPTION
4422: WHEN OTHERS THEN
4423: pa_debug.G_err_code:= SQLCODE;
4424: RAISE;
4419: ) ;
4420: pa_debug.reset_err_stack;
4421: EXCEPTION
4422: WHEN OTHERS THEN
4423: pa_debug.G_err_code:= SQLCODE;
4424: RAISE;
4425: END insert_alloc_runs;
4426: -- ------------------------------------------------------------
4427: -- get_fiscalyear_quarter
4855: Begin
4856: ------------------------------------------------------------------
4857: ---- Filling All Excludes And All its childs to The PLSQL table ----
4858: IF P_DEBUG_MODE = 'Y' THEN
4859: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' Inside fetch_all_excludes');
4860: END IF;
4861: v_cnt := 0;
4862: v_child_resource_excl_id.delete;
4863: If Nvl(p_resource_struct_type,'RL') = 'RL' Then
4861: v_cnt := 0;
4862: v_child_resource_excl_id.delete;
4863: If Nvl(p_resource_struct_type,'RL') = 'RL' Then
4864: IF P_DEBUG_MODE = 'Y' THEN
4865: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' In case of RL');
4866: END IF;
4867: Declare
4868: Cursor C_Exc_Chd_Rlm (V_parent_rlm in Number)
4869: Is
4878: Begin
4879: Open C_Exc_RLM;
4880: Loop
4881: IF P_DEBUG_MODE = 'Y' THEN
4882: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' In case of RL 1 ');
4883: END IF;
4884: Resource_List_Member_tab.Delete;
4885: Fetch C_Exc_RLM Bulk Collect Into Resource_List_Member_tab Limit 500;
4886: If Not Resource_List_Member_Tab.Exists(1) Then
4889: For k in 1..Resource_List_Member_tab.count
4890: Loop
4891: Open C_Exc_Chd_Rlm(Resource_List_Member_tab(k));
4892: IF P_DEBUG_MODE = 'Y' THEN
4893: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' RL : Child Member ' || Resource_List_Member_tab(k) );
4894: END IF;
4895: Loop
4896: v_child_resource_excl_id_temp.Delete;
4897: Fetch C_Exc_Chd_Rlm Bulk Collect
4915: Close C_Exc_RLM;
4916: End;
4917: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
4918: IF P_DEBUG_MODE = 'Y' THEN
4919: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' In case of RBS');
4920: END IF;
4921: Declare
4922: Cursor C_Exc_Chd_Rlm_RBS (V_parent_rlm in Number)
4923: Is
4931: Begin
4932: Open C_Exc_RLM;
4933: Loop
4934: IF P_DEBUG_MODE = 'Y' THEN
4935: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' In case of RBS 1 ');
4936: END IF;
4937: Resource_List_Member_tab.Delete;
4938: Fetch C_Exc_RLM Bulk Collect Into Resource_List_Member_tab Limit 500;
4939: If Not Resource_List_Member_Tab.Exists(1) Then
4942: For k in 1..Resource_List_Member_tab.count
4943: Loop
4944: Open C_Exc_Chd_Rlm_RBS(Resource_List_Member_tab(k));
4945: IF P_DEBUG_MODE = 'Y' THEN
4946: pa_debug.write_file('fetch_all_excludes: ' || 'LOG',' RBS : Child Member ' || Resource_List_Member_tab(k) );
4947: END IF;
4948: Loop
4949: v_child_resource_excl_id_temp.Delete;
4950: Fetch C_Exc_Chd_Rlm_RBS Bulk Collect
4996: End Is_Excluded_Rlm;
4997: BEGIN
4998: v_counter := 1 ;
4999: IF P_DEBUG_MODE = 'Y' THEN
5000: pa_debug.write_file('populate_RLM_table: ' || 'LOG','Inside populate_RLM');
5001: END IF;/*2564418*/
5002: IF check_rlm_exists(p_rule_id, p_type) = 'N' then
5003: IF P_DEBUG_MODE = 'Y' THEN
5004: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If check_rlm_exists is N');
5000: pa_debug.write_file('populate_RLM_table: ' || 'LOG','Inside populate_RLM');
5001: END IF;/*2564418*/
5002: IF check_rlm_exists(p_rule_id, p_type) = 'N' then
5003: IF P_DEBUG_MODE = 'Y' THEN
5004: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If check_rlm_exists is N');
5005: END IF;/*2564418*/
5006: -- populate inc_rlm_tbl using pa_budget_resources_v for
5007: -- given alloc_rule.alloc_resource_list ;
5008: /*********************** Bug 3149853 starts *****************************
5072: /*********************** Bug 3149853 ends *****************************/
5073: Else
5074: Fetch_all_excludes; /* Added For 3567201 */
5075: IF P_DEBUG_MODE = 'Y' THEN
5076: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If some RLM exists....');
5077: END IF;/*2564418*/
5078: For RLM_REC in C_RLM LOOP
5079: v_rlm_id := RLM_REC.resource_list_member_id ;
5080: If RLM_REC.exclude_flag <> 'Y' then
5078: For RLM_REC in C_RLM LOOP
5079: v_rlm_id := RLM_REC.resource_list_member_id ;
5080: If RLM_REC.exclude_flag <> 'Y' then
5081: IF P_DEBUG_MODE = 'Y' THEN
5082: pa_debug.write_file('populate_RLM_table: ' || 'LOG','Some include exists....');
5083: END IF; /*2564418*/
5084: v_incld_exists := 'Y'; /*2564418 Hence some include is specified*/
5085: If (
5086: (
5094: AND check_child_rlm_exists(v_rlm_id) = 'Y'
5095: )
5096: ) then
5097: IF P_DEBUG_MODE = 'Y' THEN
5098: pa_debug.write_file('populate_RLM_table: ' || 'LOG','A RG is included specifically');
5099: END IF;/*2564418*/
5100: If NVL(p_resource_struct_type,'RL') = 'RL' Then
5101: Open C_RG_RLM;
5102: Elsif NVL(p_resource_struct_type,'RL') = 'RBS' Then
5136: End If;
5137: End If;
5138: End If;
5139: IF P_DEBUG_MODE = 'Y' THEN
5140: pa_debug.write_file('populate_RLM_table: ' || 'LOG','v_counter: '|| to_char(v_counter)||' RLM ID :' || to_char(v_rlm_id ));
5141: END IF;
5142: --- If check_rlm_excluded(p_rule_id, p_type, v_chd_rlm_id ) = 'N' then
5143: /* Check is thru now from PLSQL tables For 3567201 */
5144: /* commented for bug 2211234
5189: );
5190: End if ;
5191: Else
5192: IF P_DEBUG_MODE = 'Y' THEN
5193: pa_debug.write_file('populate_RLM_table: ' || 'LOG','v_counter : ' || to_char(v_counter) );
5194: END IF;
5195: --- If check_rlm_excluded (p_rule_id, p_type, RLM_REC.resource_list_member_id) = 'N' then
5196: /* Check is thru now from PLSQL tables For 3567201 */
5197: /* commented for bug 2211234
5210: --- End if ;
5211: End if ;
5212: Else /* If the RLM is an exclude. 2564418 changes start here */
5213: IF P_DEBUG_MODE = 'Y' THEN
5214: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If an exclude is specified...');
5215: END IF;
5216: If (v_incld_exists = 'N') then /* no include has been specified */
5217: IF P_DEBUG_MODE = 'Y' THEN
5218: pa_debug.write_file('populate_RLM_table: ' || 'LOG','No include has been specified...');
5214: pa_debug.write_file('populate_RLM_table: ' || 'LOG','If an exclude is specified...');
5215: END IF;
5216: If (v_incld_exists = 'N') then /* no include has been specified */
5217: IF P_DEBUG_MODE = 'Y' THEN
5218: pa_debug.write_file('populate_RLM_table: ' || 'LOG','No include has been specified...');
5219: END IF;
5220: /*include all RLM but this one */
5221: /*********************** Bug 3149853 starts *****************************
5222: For RL_RLM_REC in C_RL_RLM LOOP
5221: /*********************** Bug 3149853 starts *****************************
5222: For RL_RLM_REC in C_RL_RLM LOOP
5223: if(v_excld_exists <> 'Y') then
5224: IF P_DEBUG_MODE = 'Y' THEN
5225: pa_debug.write_file('populate_RLM_table: ' || 'LOG','Inserting the other members-- '|| RLM_REC.resource_list_member_id);
5226: END IF;
5227: insert_alloc_run_resources(p_run_id => p_run_id
5228: ,p_rule_id => p_rule_id
5229: ,p_member_type => p_type
5266: End If;
5267: FOR i IN resource_list_member_tab.first..resource_list_member_tab.last
5268: LOOP
5269: IF P_DEBUG_MODE = 'Y' THEN
5270: pa_debug.write_file('populate_RLM_table:'||'LOG','Inserting member-'|| resource_list_member_tab(i));
5271: END IF;
5272: If Not Is_Excluded_Rlm (resource_list_member_tab(i)) Then /* Added For 3567201 */
5273: insert_alloc_run_resources(p_run_id => p_run_id
5274: ,p_rule_id => p_rule_id
5422: v_commit_count NUMBER;
5423: BEGIN
5424: -- project and task amount are inserted based on the amount type (FTYD/QTD/PTD/ITD)
5425: -- and run period and run period type
5426: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_BASIS_RESOURCE procedure';
5427: IF P_DEBUG_MODE = 'Y' THEN
5428: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5429: END IF;
5430: If p_amt_type in ( 'FYTD', 'QTD') then
5424: -- project and task amount are inserted based on the amount type (FTYD/QTD/PTD/ITD)
5425: -- and run period and run period type
5426: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_BASIS_RESOURCE procedure';
5427: IF P_DEBUG_MODE = 'Y' THEN
5428: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5429: END IF;
5430: If p_amt_type in ( 'FYTD', 'QTD') then
5431: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
5432: IF P_DEBUG_MODE = 'Y' THEN
5427: IF P_DEBUG_MODE = 'Y' THEN
5428: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5429: END IF;
5430: If p_amt_type in ( 'FYTD', 'QTD') then
5431: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
5432: IF P_DEBUG_MODE = 'Y' THEN
5433: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5434: END IF;
5435: v_commit_count := 0;
5429: END IF;
5430: If p_amt_type in ( 'FYTD', 'QTD') then
5431: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
5432: IF P_DEBUG_MODE = 'Y' THEN
5433: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5434: END IF;
5435: v_commit_count := 0;
5436: FOR c_projects_rec in c_projects LOOP
5437: IF P_DEBUG_MODE = 'Y' THEN
5434: END IF;
5435: v_commit_count := 0;
5436: FOR c_projects_rec in c_projects LOOP
5437: IF P_DEBUG_MODE = 'Y' THEN
5438: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
5439: END IF;
5440: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------- {
5441: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5442: RUN_ID
5604: End If; ----------------- }
5605: v_commit_count := v_commit_count + sql%rowcount;
5606: IF v_commit_count > 5000 then
5607: IF P_DEBUG_MODE = 'Y' THEN
5608: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
5609: END IF;
5610: COMMIT;
5611: v_commit_count := 0;
5612: END IF;
5612: END IF;
5613: END LOOP;
5614: COMMIT;
5615: Elsif p_amt_type = 'PTD' then
5616: pa_debug.G_err_stage:= 'inserting for PTD';
5617: IF P_DEBUG_MODE = 'Y' THEN
5618: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5619: END IF;
5620: v_commit_count := 0;
5614: COMMIT;
5615: Elsif p_amt_type = 'PTD' then
5616: pa_debug.G_err_stage:= 'inserting for PTD';
5617: IF P_DEBUG_MODE = 'Y' THEN
5618: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5619: END IF;
5620: v_commit_count := 0;
5621: FOR c_projects_rec in c_projects LOOP
5622: IF p_run_period_type = 'PA' THEN
5620: v_commit_count := 0;
5621: FOR c_projects_rec in c_projects LOOP
5622: IF p_run_period_type = 'PA' THEN
5623: IF P_DEBUG_MODE = 'Y' THEN
5624: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5625: END IF;
5626: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------{
5627: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5628: RUN_ID
5899: END IF;
5900: v_commit_count := v_commit_count + sql%rowcount;
5901: IF v_commit_count > 5000 then
5902: IF P_DEBUG_MODE = 'Y' THEN
5903: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
5904: END IF;
5905: COMMIT;
5906: v_commit_count := 0;
5907: END IF;
5907: END IF;
5908: END LOOP;
5909: COMMIT;
5910: Elsif p_amt_type = 'ITD' then
5911: pa_debug.G_err_stage:= 'inserting for ITD';
5912: IF P_DEBUG_MODE = 'Y' THEN
5913: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5914: END IF;
5915: v_commit_count := 0;
5909: COMMIT;
5910: Elsif p_amt_type = 'ITD' then
5911: pa_debug.G_err_stage:= 'inserting for ITD';
5912: IF P_DEBUG_MODE = 'Y' THEN
5913: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
5914: END IF;
5915: v_commit_count := 0;
5916: FOR c_projects_rec in c_projects LOOP
5917: /**** OPEN C_proj_start_date(c_projects_rec.project_id) ;
5921: End if ;
5922: CLOSE C_proj_start_date ;
5923: IF v_project_start_date is NOT NULL then **** Commented for bug 2757875 ****/
5924: IF P_DEBUG_MODE = 'Y' THEN
5925: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5926: END IF;
5927: If Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------------{
5928: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5929: RUN_ID
6077: v_commit_count := v_commit_count + sql%rowcount;
6078: -- END IF; **** Commetned for bug 2757875
6079: IF v_commit_count > 5000 then
6080: IF P_DEBUG_MODE = 'Y' THEN
6081: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
6082: END IF;
6083: COMMIT;
6084: v_commit_count := 0;
6085: END IF;
6085: END IF;
6086: END LOOP;
6087: COMMIT;
6088: End If ;
6089: pa_debug.G_err_stage:= 'exiting insert_alloc_basis_resource';
6090: IF P_DEBUG_MODE = 'Y' THEN
6091: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
6092: END IF;
6093: EXCEPTION
6087: COMMIT;
6088: End If ;
6089: pa_debug.G_err_stage:= 'exiting insert_alloc_basis_resource';
6090: IF P_DEBUG_MODE = 'Y' THEN
6091: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
6092: END IF;
6093: EXCEPTION
6094: WHEN OTHERS THEN
6095: RAISE ;
6153: v_rule_pool_percent NUMBER; /* Added for bug 3227783 */
6154: BEGIN
6155: -- project and task amount are inserted based on the amount type (FTYD/QTD/PTD/ITD)
6156: -- and run period and run period type
6157: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_source_RESOURCE procedure';
6158: IF P_DEBUG_MODE = 'Y' THEN
6159: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6160: END IF;
6161: pa_debug.G_err_stage:= 'getting the pool_percent set for the rule.';
6155: -- project and task amount are inserted based on the amount type (FTYD/QTD/PTD/ITD)
6156: -- and run period and run period type
6157: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_source_RESOURCE procedure';
6158: IF P_DEBUG_MODE = 'Y' THEN
6159: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6160: END IF;
6161: pa_debug.G_err_stage:= 'getting the pool_percent set for the rule.';
6162: IF P_DEBUG_MODE = 'Y' THEN
6163: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6157: pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_source_RESOURCE procedure';
6158: IF P_DEBUG_MODE = 'Y' THEN
6159: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6160: END IF;
6161: pa_debug.G_err_stage:= 'getting the pool_percent set for the rule.';
6162: IF P_DEBUG_MODE = 'Y' THEN
6163: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6164: END IF;
6165: /* Added for bug 3227783 */
6159: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6160: END IF;
6161: pa_debug.G_err_stage:= 'getting the pool_percent set for the rule.';
6162: IF P_DEBUG_MODE = 'Y' THEN
6163: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6164: END IF;
6165: /* Added for bug 3227783 */
6166: Open c_get_rule_pool_percent ;
6167: FETCH c_get_rule_pool_percent into v_rule_pool_percent;
6166: Open c_get_rule_pool_percent ;
6167: FETCH c_get_rule_pool_percent into v_rule_pool_percent;
6168: CLOSE c_get_rule_pool_percent;
6169: If p_amt_type in ( 'FYTD', 'QTD') then
6170: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
6171: IF P_DEBUG_MODE = 'Y' THEN
6172: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6173: END IF;
6174: v_commit_count := 0;
6168: CLOSE c_get_rule_pool_percent;
6169: If p_amt_type in ( 'FYTD', 'QTD') then
6170: pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
6171: IF P_DEBUG_MODE = 'Y' THEN
6172: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6173: END IF;
6174: v_commit_count := 0;
6175: FOR c_projects_rec in c_projects LOOP
6176: IF P_DEBUG_MODE = 'Y' THEN
6173: END IF;
6174: v_commit_count := 0;
6175: FOR c_projects_rec in c_projects LOOP
6176: IF P_DEBUG_MODE = 'Y' THEN
6177: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
6178: END IF;
6179: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------------{
6180: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
6181: RUN_ID
6375: End If; ------------}
6376: v_commit_count := v_commit_count + sql%rowcount;
6377: IF v_commit_count > 5000 then
6378: IF P_DEBUG_MODE = 'Y' THEN
6379: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
6380: END IF;
6381: COMMIT;
6382: v_commit_count := 0;
6383: END IF;
6383: END IF;
6384: END LOOP;
6385: COMMIT;
6386: Elsif p_amt_type = 'PTD' then
6387: pa_debug.G_err_stage:= 'inserting for PTD';
6388: IF P_DEBUG_MODE = 'Y' THEN
6389: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6390: END IF;
6391: v_commit_count := 0;
6385: COMMIT;
6386: Elsif p_amt_type = 'PTD' then
6387: pa_debug.G_err_stage:= 'inserting for PTD';
6388: IF P_DEBUG_MODE = 'Y' THEN
6389: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6390: END IF;
6391: v_commit_count := 0;
6392: FOR c_projects_rec in c_projects LOOP
6393: IF p_run_period_type = 'PA' THEN
6391: v_commit_count := 0;
6392: FOR c_projects_rec in c_projects LOOP
6393: IF p_run_period_type = 'PA' THEN
6394: IF P_DEBUG_MODE = 'Y' THEN
6395: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6396: END IF;
6397: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ------------{
6398: INSERT INTO PA_ALLOC_RUN_source_DET (
6399: RUN_ID
6726: END IF;
6727: v_commit_count := v_commit_count + sql%rowcount;
6728: IF v_commit_count > 5000 then
6729: IF P_DEBUG_MODE = 'Y' THEN
6730: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
6731: END IF;
6732: COMMIT;
6733: v_commit_count := 0;
6734: END IF;
6734: END IF;
6735: END LOOP;
6736: COMMIT;
6737: Elsif p_amt_type = 'ITD' then
6738: pa_debug.G_err_stage:= 'inserting for ITD';
6739: IF P_DEBUG_MODE = 'Y' THEN
6740: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6741: END IF;
6742: v_commit_count := 0;
6736: COMMIT;
6737: Elsif p_amt_type = 'ITD' then
6738: pa_debug.G_err_stage:= 'inserting for ITD';
6739: IF P_DEBUG_MODE = 'Y' THEN
6740: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6741: END IF;
6742: v_commit_count := 0;
6743: FOR c_projects_rec in c_projects LOOP
6744: /**** OPEN C_proj_start_date(c_projects_rec.project_id) ;
6749: CLOSE C_proj_start_date ;
6750: IF v_project_start_date is NOT NULL then
6751: **** Commented for bug 2757875****/
6752: IF P_DEBUG_MODE = 'Y' THEN
6753: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6754: END IF;
6755: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------{
6756: INSERT INTO PA_ALLOC_RUN_source_DET (
6757: RUN_ID
6934: v_commit_count := v_commit_count + sql%rowcount;
6935: -- END IF; **** Commented for bug 2757875
6936: IF v_commit_count > 5000 then
6937: IF P_DEBUG_MODE = 'Y' THEN
6938: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
6939: END IF;
6940: COMMIT;
6941: v_commit_count := 0;
6942: END IF;
6942: END IF;
6943: END LOOP;
6944: COMMIT;
6945: End If ;
6946: pa_debug.G_err_stage:= 'exiting insert_alloc_source_resource';
6947: IF P_DEBUG_MODE = 'Y' THEN
6948: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6949: END IF;
6950: EXCEPTION
6944: COMMIT;
6945: End If ;
6946: pa_debug.G_err_stage:= 'exiting insert_alloc_source_resource';
6947: IF P_DEBUG_MODE = 'Y' THEN
6948: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
6949: END IF;
6950: EXCEPTION
6951: WHEN OTHERS THEN
6952: RAISE ;
7280: , p_last_updated_by IN NUMBER
7281: , p_last_update_login IN NUMBER)
7282: IS
7283: BEGIN
7284: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7285: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7286: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7287: RUN_ID
7288: , RULE_ID
7281: , p_last_update_login IN NUMBER)
7282: IS
7283: BEGIN
7284: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7285: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7286: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7287: RUN_ID
7288: , RULE_ID
7289: , LINE_NUM
7313: , p_last_update_date
7314: , p_last_updated_by
7315: , p_last_update_login ) ;
7316: /* restore the old stack */
7317: pa_debug.reset_err_stack;
7318: EXCEPTION
7319: WHEN OTHERS THEN
7320: RAISE;
7321: END insert_alloc_run_src_det;
7392: Where Rule_Id = P_Rule_Id
7393: And Run_Id = P_Run_Id
7394: And Nvl(Exclude_Flag,'N') = 'N';
7395: BEGIN
7396: pa_debug.set_err_stack('Cal_amounts_from_projects') ;
7397: pa_debug.G_err_stage := 'Get Fiscal Year and quarter for run period and amounttype' ;
7398: IF P_DEBUG_MODE = 'Y' THEN
7399: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7400: END IF;
7393: And Run_Id = P_Run_Id
7394: And Nvl(Exclude_Flag,'N') = 'N';
7395: BEGIN
7396: pa_debug.set_err_stack('Cal_amounts_from_projects') ;
7397: pa_debug.G_err_stage := 'Get Fiscal Year and quarter for run period and amounttype' ;
7398: IF P_DEBUG_MODE = 'Y' THEN
7399: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7400: END IF;
7401: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
7395: BEGIN
7396: pa_debug.set_err_stack('Cal_amounts_from_projects') ;
7397: pa_debug.G_err_stage := 'Get Fiscal Year and quarter for run period and amounttype' ;
7398: IF P_DEBUG_MODE = 'Y' THEN
7399: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7400: END IF;
7401: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
7402: v_period_set_name, v_period_year, v_quarter, v_period_num,
7403: v_run_period_end_date) ;
7400: END IF;
7401: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
7402: v_period_set_name, v_period_year, v_quarter, v_period_num,
7403: v_run_period_end_date) ;
7404: pa_debug.G_err_stage := 'Getting start date for given amount type(FYTD/QTD)' ;
7405: IF P_DEBUG_MODE = 'Y' THEN
7406: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7407: END IF;
7408: get_amttype_start_date( p_run_amount_type, v_period_type, v_period_set_name,
7402: v_period_set_name, v_period_year, v_quarter, v_period_num,
7403: v_run_period_end_date) ;
7404: pa_debug.G_err_stage := 'Getting start date for given amount type(FYTD/QTD)' ;
7405: IF P_DEBUG_MODE = 'Y' THEN
7406: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7407: END IF;
7408: get_amttype_start_date( p_run_amount_type, v_period_type, v_period_set_name,
7409: v_run_period_end_date, v_quarter, v_period_year,
7410: p_run_period, v_amttype_start_date) ;
7423: G_last_update_date,
7424: G_last_updated_by, G_last_update_login);
7425: End if ;
7426: If v_resource_list_id is NOT NULL then
7427: pa_debug.G_err_stage := 'Populating the Resource List Member array ' ;
7428: IF P_DEBUG_MODE = 'Y' THEN
7429: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7430: END IF;
7431: /* bug 2211234 - added p_run_id.
7425: End if ;
7426: If v_resource_list_id is NOT NULL then
7427: pa_debug.G_err_stage := 'Populating the Resource List Member array ' ;
7428: IF P_DEBUG_MODE = 'Y' THEN
7429: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7430: END IF;
7431: /* bug 2211234 - added p_run_id.
7432: - removed v_src_rlm_tab
7433: This procedure will now populate records into pa_alloc_run_resources_det */
7449: alloc_errors(p_rule_id, p_run_id, 'S','E', 'PA_AL_NO_INCL_SRC_RESRC',TRUE) ;
7450: end if ;
7451: EXCEPTION
7452: WHEN OTHERS THEN
7453: pa_debug.G_err_stage := 'error during selecting count from pa_alloc_run_resources_det' ;
7454: IF P_DEBUG_MODE = 'Y' THEN
7455: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7456: END IF;
7457: RAISE;
7451: EXCEPTION
7452: WHEN OTHERS THEN
7453: pa_debug.G_err_stage := 'error during selecting count from pa_alloc_run_resources_det' ;
7454: IF P_DEBUG_MODE = 'Y' THEN
7455: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7456: END IF;
7457: RAISE;
7458: END;
7459: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts ' ;
7455: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7456: END IF;
7457: RAISE;
7458: END;
7459: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts ' ;
7460: IF P_DEBUG_MODE = 'Y' THEN
7461: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7462: END IF;
7463: /* added for 2211234 */
7457: RAISE;
7458: END;
7459: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts ' ;
7460: IF P_DEBUG_MODE = 'Y' THEN
7461: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7462: END IF;
7463: /* added for 2211234 */
7464: insert_alloc_source_resource(p_run_id => p_run_id
7465: ,p_rule_id => p_rule_id
7485: v_period_set_name, v_period_year, v_quarter,
7486: v_run_period_end_date,
7487: v_amttype_start_date, v_amount ) ;
7488: IF P_DEBUG_MODE = 'Y' THEN
7489: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_rlm_percent '||to_char(v_rlm_percent));
7490: END IF;
7491: v_pool_amount := v_amount * (nvl(v_rlm_percent,100)/100) *
7492: (nvl(p_pool_percent,100)/100) ;
7493: IF P_DEBUG_MODE = 'Y' THEN
7490: END IF;
7491: v_pool_amount := v_amount * (nvl(v_rlm_percent,100)/100) *
7492: (nvl(p_pool_percent,100)/100) ;
7493: IF P_DEBUG_MODE = 'Y' THEN
7494: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_amount '||to_char(v_amount));
7495: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_pool_amount '||to_char(v_pool_amount));
7496: END IF;
7497: v_pool_amount := pa_currency.round_currency_amt( v_pool_amount );
7498: v_amount := pa_currency.round_currency_amt( v_amount );
7491: v_pool_amount := v_amount * (nvl(v_rlm_percent,100)/100) *
7492: (nvl(p_pool_percent,100)/100) ;
7493: IF P_DEBUG_MODE = 'Y' THEN
7494: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_amount '||to_char(v_amount));
7495: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'v_pool_amount '||to_char(v_pool_amount));
7496: END IF;
7497: v_pool_amount := pa_currency.round_currency_amt( v_pool_amount );
7498: v_amount := pa_currency.round_currency_amt( v_amount );
7499: insert_alloc_run_src_det(p_rule_id, p_run_id, src_det_rec.line_num,
7506: END LOOP ;
7507: END LOOP ;
7508: */
7509: Else
7510: pa_debug.G_err_stage := 'Processing project/Tasks to get amounts ' ;
7511: IF P_DEBUG_MODE = 'Y' THEN
7512: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7513: END IF;
7514: If p_run_amount_type in ( 'FYTD','QTD') then
7508: */
7509: Else
7510: pa_debug.G_err_stage := 'Processing project/Tasks to get amounts ' ;
7511: IF P_DEBUG_MODE = 'Y' THEN
7512: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7513: END IF;
7514: If p_run_amount_type in ( 'FYTD','QTD') then
7515: For l_get_proj in c_get_proj
7516: Loop
7759: End Loop;
7760: End if ;
7761: End If ;
7762: IF P_DEBUG_MODE = 'Y' THEN
7763: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'Amount type is '|| p_run_amount_type ||'--' || to_char(SQL%ROWCOUNT));
7764: END IF;
7765: pa_debug.G_err_stage := 'Getting pool amount from projects for run'||to_char(p_run_id) ;
7766: IF P_DEBUG_MODE = 'Y' THEN
7767: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7761: End If ;
7762: IF P_DEBUG_MODE = 'Y' THEN
7763: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'Amount type is '|| p_run_amount_type ||'--' || to_char(SQL%ROWCOUNT));
7764: END IF;
7765: pa_debug.G_err_stage := 'Getting pool amount from projects for run'||to_char(p_run_id) ;
7766: IF P_DEBUG_MODE = 'Y' THEN
7767: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7768: END IF;
7769: commit ;
7763: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', 'Amount type is '|| p_run_amount_type ||'--' || to_char(SQL%ROWCOUNT));
7764: END IF;
7765: pa_debug.G_err_stage := 'Getting pool amount from projects for run'||to_char(p_run_id) ;
7766: IF P_DEBUG_MODE = 'Y' THEN
7767: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7768: END IF;
7769: commit ;
7770: OPEN C_get_pool_amount ;
7771: Fetch C_get_pool_amount into x_proj_pool_amount ;
7769: commit ;
7770: OPEN C_get_pool_amount ;
7771: Fetch C_get_pool_amount into x_proj_pool_amount ;
7772: close C_get_pool_amount ;
7773: pa_debug.reset_err_stack;
7774: EXCEPTION
7775: WHEN OTHERS THEN
7776: RAISE;
7777: END cal_amounts_from_projects ;
7795: and glp.end_date <= p_run_period_end_date
7796: and glp.adjustment_period_flag <> 'Y' /* Added for Bug#2409474 */
7797: order by start_date desc ;
7798: BEGIN
7799: pa_debug.set_err_stack('get_relative_period_name') ;
7800: pa_debug.G_err_stage := 'Fetching the Relative period name' ;
7801: IF P_DEBUG_MODE = 'Y' THEN
7802: pa_debug.write_file('get_relative_period_name: ' || 'LOG', pa_debug.G_err_stage);
7803: END IF;
7796: and glp.adjustment_period_flag <> 'Y' /* Added for Bug#2409474 */
7797: order by start_date desc ;
7798: BEGIN
7799: pa_debug.set_err_stack('get_relative_period_name') ;
7800: pa_debug.G_err_stage := 'Fetching the Relative period name' ;
7801: IF P_DEBUG_MODE = 'Y' THEN
7802: pa_debug.write_file('get_relative_period_name: ' || 'LOG', pa_debug.G_err_stage);
7803: END IF;
7804: v_rel_period := 1 + nvl(p_relative_period,0)* -1 ;
7798: BEGIN
7799: pa_debug.set_err_stack('get_relative_period_name') ;
7800: pa_debug.G_err_stage := 'Fetching the Relative period name' ;
7801: IF P_DEBUG_MODE = 'Y' THEN
7802: pa_debug.write_file('get_relative_period_name: ' || 'LOG', pa_debug.G_err_stage);
7803: END IF;
7804: v_rel_period := 1 + nvl(p_relative_period,0)* -1 ;
7805: v_counter := 1 ;
7806: If v_rel_period > 0 then
7817: else
7818: x_rel_period_name := p_run_period;
7819: End if ;
7820: IF P_DEBUG_MODE = 'Y' THEN
7821: pa_debug.write_file('get_relative_period_name: ' || 'LOG', 'Relative Period is '||x_rel_period_name);
7822: END IF;
7823: pa_debug.reset_err_stack;
7824: EXCEPTION
7825: WHEN OTHERS THEN
7819: End if ;
7820: IF P_DEBUG_MODE = 'Y' THEN
7821: pa_debug.write_file('get_relative_period_name: ' || 'LOG', 'Relative Period is '||x_rel_period_name);
7822: END IF;
7823: pa_debug.reset_err_stack;
7824: EXCEPTION
7825: WHEN OTHERS THEN
7826: RAISE ;
7827: END get_relative_period_name ;
7843: , p_last_updated_by IN NUMBER
7844: , p_last_update_login IN NUMBER)
7845: IS
7846: BEGIN
7847: pa_debug.set_err_stack('insert_alloc_run_basis_det') ;
7848: IF P_DEBUG_MODE = 'Y' THEN
7849: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7850: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7851: END IF;
7845: IS
7846: BEGIN
7847: pa_debug.set_err_stack('insert_alloc_run_basis_det') ;
7848: IF P_DEBUG_MODE = 'Y' THEN
7849: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7850: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7851: END IF;
7852: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7853: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7846: BEGIN
7847: pa_debug.set_err_stack('insert_alloc_run_basis_det') ;
7848: IF P_DEBUG_MODE = 'Y' THEN
7849: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7850: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7851: END IF;
7852: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7853: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7854: RUN_ID
7848: IF P_DEBUG_MODE = 'Y' THEN
7849: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7850: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7851: END IF;
7852: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7853: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7854: RUN_ID
7855: , RULE_ID
7856: , LINE_NUM
7880: , p_last_update_date
7881: , p_last_updated_by
7882: , p_last_update_login ) ;
7883: /* restore the old stack */
7884: pa_debug.reset_err_stack;
7885: EXCEPTION
7886: WHEN OTHERS THEN
7887: pa_debug.reset_err_stack;
7888: RAISE;
7883: /* restore the old stack */
7884: pa_debug.reset_err_stack;
7885: EXCEPTION
7886: WHEN OTHERS THEN
7887: pa_debug.reset_err_stack;
7888: RAISE;
7889: END insert_alloc_run_basis_det ;
7890: -- ============================================================
7891: -- cal_proj_basis_amounts
8005: BEGIN
8006: v_basis_method := p_basis_method ;
8007: v_resource_list_id := p_basis_RL_id ;
8008: v_sum_tgt_pct := 0 ;
8009: pa_debug.set_err_stack('Cal_proj_basis_amounts') ;
8010: If p_basis_method = 'C' then
8011: pa_debug.G_err_stage := 'Call basis client extension';
8012: IF P_DEBUG_MODE = 'Y' THEN
8013: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8007: v_resource_list_id := p_basis_RL_id ;
8008: v_sum_tgt_pct := 0 ;
8009: pa_debug.set_err_stack('Cal_proj_basis_amounts') ;
8010: If p_basis_method = 'C' then
8011: pa_debug.G_err_stage := 'Call basis client extension';
8012: IF P_DEBUG_MODE = 'Y' THEN
8013: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8014: END IF;
8015: For run_targets_rec in C_run_targets LOOP
8009: pa_debug.set_err_stack('Cal_proj_basis_amounts') ;
8010: If p_basis_method = 'C' then
8011: pa_debug.G_err_stage := 'Call basis client extension';
8012: IF P_DEBUG_MODE = 'Y' THEN
8013: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8014: END IF;
8015: For run_targets_rec in C_run_targets LOOP
8016: pa_client_extn_alloc.basis_extn(p_rule_id, run_targets_rec.project_id,
8017: run_targets_rec.task_id, v_amount,v_status,v_err_message) ;
8029: Open C_tgt_line_pct ;
8030: Fetch C_tgt_line_pct into v_sum_tgt_pct ;
8031: Close C_tgt_line_pct;
8032: elsif p_basis_method in ( 'P', 'FP') then
8033: pa_debug.G_err_stage := 'Call get_fiscalyear_quarter';
8034: IF P_DEBUG_MODE = 'Y' THEN
8035: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8036: END IF;
8037: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
8031: Close C_tgt_line_pct;
8032: elsif p_basis_method in ( 'P', 'FP') then
8033: pa_debug.G_err_stage := 'Call get_fiscalyear_quarter';
8034: IF P_DEBUG_MODE = 'Y' THEN
8035: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8036: END IF;
8037: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
8038: v_period_set_name,
8039: v_period_year, v_quarter, v_period_num,
8037: get_fiscalyear_quarter(p_run_period_type, p_run_period, v_period_type,
8038: v_period_set_name,
8039: v_period_year, v_quarter, v_period_num,
8040: v_run_period_end_date) ;
8041: pa_debug.G_err_stage := 'Calling get_relative_period_name' ;
8042: IF P_DEBUG_MODE = 'Y' THEN
8043: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8044: END IF;
8045: get_relative_period_name(v_period_set_name, v_period_type, v_run_period_end_date,
8039: v_period_year, v_quarter, v_period_num,
8040: v_run_period_end_date) ;
8041: pa_debug.G_err_stage := 'Calling get_relative_period_name' ;
8042: IF P_DEBUG_MODE = 'Y' THEN
8043: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8044: END IF;
8045: get_relative_period_name(v_period_set_name, v_period_type, v_run_period_end_date,
8046: p_run_period, p_basis_rel_period, v_rel_period_name ) ;
8047: pa_debug.G_err_stage := 'calling get_fiscalyear_quarter for relative period' ;
8043: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8044: END IF;
8045: get_relative_period_name(v_period_set_name, v_period_type, v_run_period_end_date,
8046: p_run_period, p_basis_rel_period, v_rel_period_name ) ;
8047: pa_debug.G_err_stage := 'calling get_fiscalyear_quarter for relative period' ;
8048: IF P_DEBUG_MODE = 'Y' THEN
8049: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8050: END IF;
8051: get_fiscalyear_quarter(p_run_period_type, v_rel_period_name, v_period_type,
8045: get_relative_period_name(v_period_set_name, v_period_type, v_run_period_end_date,
8046: p_run_period, p_basis_rel_period, v_rel_period_name ) ;
8047: pa_debug.G_err_stage := 'calling get_fiscalyear_quarter for relative period' ;
8048: IF P_DEBUG_MODE = 'Y' THEN
8049: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8050: END IF;
8051: get_fiscalyear_quarter(p_run_period_type, v_rel_period_name, v_period_type,
8052: v_period_set_name,
8053: v_period_year, v_quarter, v_period_num,
8051: get_fiscalyear_quarter(p_run_period_type, v_rel_period_name, v_period_type,
8052: v_period_set_name,
8053: v_period_year, v_quarter, v_period_num,
8054: v_rel_period_end_date) ;
8055: pa_debug.G_err_stage := 'calling get_amttype_start_date';
8056: IF P_DEBUG_MODE = 'Y' THEN
8057: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8058: END IF;
8059: get_amttype_start_date( p_basis_amt_type, v_period_type,
8053: v_period_year, v_quarter, v_period_num,
8054: v_rel_period_end_date) ;
8055: pa_debug.G_err_stage := 'calling get_amttype_start_date';
8056: IF P_DEBUG_MODE = 'Y' THEN
8057: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8058: END IF;
8059: get_amttype_start_date( p_basis_amt_type, v_period_type,
8060: v_period_set_name, v_rel_period_end_date,
8061: v_quarter, v_period_year, p_run_period, v_amttype_start_date) ;
8059: get_amttype_start_date( p_basis_amt_type, v_period_type,
8060: v_period_set_name, v_rel_period_end_date,
8061: v_quarter, v_period_year, p_run_period, v_amttype_start_date) ;
8062: IF P_DEBUG_MODE = 'Y' THEN
8063: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','amttype start date = '|| to_char(v_amttype_start_date));
8064: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','rel_period_end_date = '|| to_char(v_rel_period_end_date));
8065: END IF;
8066: If v_resource_list_id is NOT NULL then
8067: pa_debug.G_err_stage := 'Populating the Resource List Member array for basis ' ;
8060: v_period_set_name, v_rel_period_end_date,
8061: v_quarter, v_period_year, p_run_period, v_amttype_start_date) ;
8062: IF P_DEBUG_MODE = 'Y' THEN
8063: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','amttype start date = '|| to_char(v_amttype_start_date));
8064: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','rel_period_end_date = '|| to_char(v_rel_period_end_date));
8065: END IF;
8066: If v_resource_list_id is NOT NULL then
8067: pa_debug.G_err_stage := 'Populating the Resource List Member array for basis ' ;
8068: IF P_DEBUG_MODE = 'Y' THEN
8063: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','amttype start date = '|| to_char(v_amttype_start_date));
8064: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','rel_period_end_date = '|| to_char(v_rel_period_end_date));
8065: END IF;
8066: If v_resource_list_id is NOT NULL then
8067: pa_debug.G_err_stage := 'Populating the Resource List Member array for basis ' ;
8068: IF P_DEBUG_MODE = 'Y' THEN
8069: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8070: END IF;
8071: /* Bug 2211234 - added p_run_id.
8065: END IF;
8066: If v_resource_list_id is NOT NULL then
8067: pa_debug.G_err_stage := 'Populating the Resource List Member array for basis ' ;
8068: IF P_DEBUG_MODE = 'Y' THEN
8069: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8070: END IF;
8071: /* Bug 2211234 - added p_run_id.
8072: - removed v_basis_rlm_tab
8073: this procedure will now insert into pa_alloc_run_resources_det */
8078: p_basis_resource_struct_type ,
8079: p_basis_rbs_version_id ,
8080: p_basis_category
8081: ) ;
8082: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts';
8083: IF P_DEBUG_MODE = 'Y' THEN
8084: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8085: END IF;
8086: /* major changes done for actuals for performance improvements. No more a call to get_alloc_amount
8080: p_basis_category
8081: ) ;
8082: pa_debug.G_err_stage := 'Processing project/Tasks to get resource level amounts';
8083: IF P_DEBUG_MODE = 'Y' THEN
8084: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8085: END IF;
8086: /* major changes done for actuals for performance improvements. No more a call to get_alloc_amount
8087: is made and bulk inserts are done in new procedure added insert_alloc_basis_resource
8088: These changes are not done for budgets as in budgets the volume of data should not be that high.
8130: ,p_basis_resource_struct_Type => p_basis_resource_struct_type);
8131: /***2211234 - insert_budget_basis_resource will take care of this.
8132: *** FOR I in 1.. v_basis_rlm_tab.count LOOP
8133: *** v_rlm_id := v_basis_rlm_tab (I).resource_list_member_id ;
8134: *** pa_debug.write_file('LOG','Resource list member : '|| to_char(v_rlm_id) );
8135: ***
8136: *** FOR run_targets_rec in C_run_targets LOOP
8137: *** get_budget_amounts( p_run_period_type, p_basis_bal_type,
8138: *** run_targets_rec.project_id,
8139: *** run_targets_rec.task_id,
8140: *** v_resource_list_id, v_rlm_id,
8141: *** p_budget_type_code, v_amttype_start_date,
8142: *** v_rel_period_end_date, v_amount) ;
8143: *** pa_debug.write_file('LOG','get_budget_amounts '||'project: '
8144: *** ||to_char( run_targets_rec.project_id)||'
8145: *** task: '|| to_char( run_targets_rec.task_id)|| '
8146: *** Amt:'|| to_char(v_amount) );
8147: ***
8159: END IF ;
8160: COMMIT;
8161: Else
8162: If p_basis_category = 'A' then
8163: pa_debug.G_err_stage := 'Processing project/Tasks to get basis amounts' ;
8164: IF P_DEBUG_MODE = 'Y' THEN
8165: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8166: END IF;
8167: If p_basis_amt_type in ( 'FYTD','QTD') then
8161: Else
8162: If p_basis_category = 'A' then
8163: pa_debug.G_err_stage := 'Processing project/Tasks to get basis amounts' ;
8164: IF P_DEBUG_MODE = 'Y' THEN
8165: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8166: END IF;
8167: If p_basis_amt_type in ( 'FYTD','QTD') then
8168: IF P_DEBUG_MODE = 'Y' THEN
8169: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','p_basis_amt_type' ||p_basis_amt_type);
8165: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8166: END IF;
8167: If p_basis_amt_type in ( 'FYTD','QTD') then
8168: IF P_DEBUG_MODE = 'Y' THEN
8169: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','p_basis_amt_type' ||p_basis_amt_type);
8170: END IF;
8171: For l_get_proj in c_get_proj
8172: Loop
8173: Insert into pa_alloc_run_basis_det (
8407: End Loop;
8408: End if ;
8409: Else -- Processing the Budget amounts ...
8410: IF P_DEBUG_MODE = 'Y' THEN
8411: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Inserting in basis_det from budgets');
8412: END IF;
8413: if p_basis_amt_type = 'ITD' then
8414: Insert into pa_alloc_run_basis_det(
8415: rule_id, run_id, line_num, project_id, task_id,
8605: and parb.line_num = part.line_num
8606: and parb.amount <> 0);
8607: END IF;
8608: IF P_DEBUG_MODE = 'Y' THEN
8609: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', 'No of Records Inserted in basis details'||
8610: to_char(SQL%ROWCOUNT));
8611: END IF;
8612: pa_debug.G_err_stage := 'Calculating basis percent' ;
8613: IF P_DEBUG_MODE = 'Y' THEN
8608: IF P_DEBUG_MODE = 'Y' THEN
8609: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', 'No of Records Inserted in basis details'||
8610: to_char(SQL%ROWCOUNT));
8611: END IF;
8612: pa_debug.G_err_stage := 'Calculating basis percent' ;
8613: IF P_DEBUG_MODE = 'Y' THEN
8614: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8615: END IF;
8616: pa_debug.G_err_stage := 'Getting Basis total for Method:'||p_basis_method ;
8610: to_char(SQL%ROWCOUNT));
8611: END IF;
8612: pa_debug.G_err_stage := 'Calculating basis percent' ;
8613: IF P_DEBUG_MODE = 'Y' THEN
8614: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8615: END IF;
8616: pa_debug.G_err_stage := 'Getting Basis total for Method:'||p_basis_method ;
8617: IF P_DEBUG_MODE = 'Y' THEN
8618: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8612: pa_debug.G_err_stage := 'Calculating basis percent' ;
8613: IF P_DEBUG_MODE = 'Y' THEN
8614: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8615: END IF;
8616: pa_debug.G_err_stage := 'Getting Basis total for Method:'||p_basis_method ;
8617: IF P_DEBUG_MODE = 'Y' THEN
8618: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8619: END IF;
8620: If ((p_basis_method = 'P') or (p_basis_method ='C' and v_sum_tgt_pct = 0) ) then
8614: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8615: END IF;
8616: pa_debug.G_err_stage := 'Getting Basis total for Method:'||p_basis_method ;
8617: IF P_DEBUG_MODE = 'Y' THEN
8618: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8619: END IF;
8620: If ((p_basis_method = 'P') or (p_basis_method ='C' and v_sum_tgt_pct = 0) ) then
8621: OPEN C_tot_basis_amt ;
8622: FETCH C_tot_basis_Amt INTO v_tot_basis_amt , v_max_basis_amt, v_tot_basis_rec; /* 1900331 */
8629: If nvl(v_tot_basis_amt,0) = 0 and nvl(v_max_basis_amt,0) <> 0 then /* 1900331 */
8630: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_ZERO_BASIS',TRUE) ;
8631: Else
8632: IF P_DEBUG_MODE = 'Y' THEN
8633: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Basis_amount :'||to_char(v_tot_basis_amt) );
8634: END IF;
8635: End if;
8636: Close C_tot_basis_amt ;
8637: if nvl(v_tot_basis_amt,0) <> 0 then /* added for 1900331 */
8648: WHERE run_id = p_run_id ;
8649: */
8650: end if;
8651: IF P_DEBUG_MODE = 'Y' THEN
8652: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','No of Records Updated : '|| to_char(SQL%ROWCOUNT));
8653: END IF;
8654: Elsif ((p_basis_method = 'FP') or (p_basis_method ='C' and v_sum_tgt_pct = 100)) then
8655: OPEN C_line_basis_amt;
8656: LOOP
8657: Fetch C_line_basis_amt into v_line_num, v_line_percent, v_line_basis_amt, v_line_max_amt, v_line_count; /* 1900331 */
8658: EXIT when c_line_basis_amt%NOTFOUND ;
8659: 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 */
8660: IF P_DEBUG_MODE = 'Y' THEN
8661: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','basis amount is zero for target line '||
8662: to_char(v_line_num) );
8663: END IF;
8664: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_LINE_BASIS_AMT_IS_ZERO',TRUE) ;
8665: End if;
8680: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_NO_BASIS_FOUND',TRUE) ;
8681: End If ;
8682: Close C_line_basis_amt ;
8683: End If ;
8684: pa_debug.reset_err_stack;
8685: EXCEPTION
8686: WHEN OTHERS THEN
8687: -- pa_debug.reset_err_stack;
8688: RAISE;
8683: End If ;
8684: pa_debug.reset_err_stack;
8685: EXCEPTION
8686: WHEN OTHERS THEN
8687: -- pa_debug.reset_err_stack;
8688: RAISE;
8689: END cal_proj_basis_amounts ;
8690: /* following procedure is obsoleted (not in use after fix for 2211234 */
8691: /***-- ============================================================
8741: *** If p_run_period_type = 'PA' then
8742: *** OPEN C_budget_amt_by_pa_period ;
8743: *** FETCH C_budget_amt_by_pa_period into x_amount ;
8744: *** If C_budget_amt_by_pa_period%NOTFOUND then
8745: *** pa_debug.write_file('LOG','No data found');
8746: *** x_amount := 0 ;
8747: *** End if ;
8748: *** CLOSE C_budget_amt_by_pa_period ;
8749: ***
8788: where rule_id = p_rule_id;
8789: v_commit_count NUMBER ;
8790: v_do_commit VARCHAR2(1);
8791: BEGIN
8792: pa_debug.G_err_stage:= 'INSIDE CLEAN_UP_TARGETS_FOR_ACTUALS procedure';
8793: IF P_DEBUG_MODE = 'Y' THEN
8794: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8795: END IF;
8796: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8790: v_do_commit VARCHAR2(1);
8791: BEGIN
8792: pa_debug.G_err_stage:= 'INSIDE CLEAN_UP_TARGETS_FOR_ACTUALS procedure';
8793: IF P_DEBUG_MODE = 'Y' THEN
8794: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8795: END IF;
8796: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8797: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8798: IF P_DEBUG_MODE = 'Y' THEN
8793: IF P_DEBUG_MODE = 'Y' THEN
8794: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8795: END IF;
8796: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8797: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8798: IF P_DEBUG_MODE = 'Y' THEN
8799: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8800: END IF;
8801: v_commit_count := 0;
8795: END IF;
8796: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8797: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8798: IF P_DEBUG_MODE = 'Y' THEN
8799: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8800: END IF;
8801: v_commit_count := 0;
8802: IF p_basis_method = 'P' THEN
8803: SAVEPOINT delete_unwanted_targets;
8813: and pp.period_name = pta.pa_period
8814: and pp.end_date between p_amttype_start_date
8815: and p_run_period_end_date);
8816: IF P_DEBUG_MODE = 'Y' THEN
8817: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
8818: END IF;
8819: BEGIN
8820: select 'Y'
8821: into v_do_commit
8831: to do spread evenly.
8832: */
8833: COMMIT;
8834: IF P_DEBUG_MODE = 'Y' THEN
8835: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
8836: END IF;
8837: EXCEPTION
8838: WHEN NO_DATA_FOUND THEN
8839: /* switch the basis method to 'S' in case all records are deleted.
8840: We need to do rollback also
8841: */
8842: ROLLBACK TO delete_unwanted_targets;
8843: IF P_DEBUG_MODE = 'Y' THEN
8844: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
8845: END IF;
8846: p_basis_method := 'S';
8847: END;
8848: elsif p_basis_method = 'FP' then
8862: and pp.end_date between p_amttype_start_date
8863: and p_run_period_end_date);
8864:
8865: IF P_DEBUG_MODE = 'Y' THEN
8866: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
8867: END IF;
8868: BEGIN
8869: select 'Y'
8870: into v_do_commit
8882: No switch of basis method can be done in case of FP.
8883: */
8884: COMMIT;
8885: IF P_DEBUG_MODE = 'Y' THEN
8886: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
8887: END IF;
8888: EXCEPTION
8889: WHEN NO_DATA_FOUND THEN
8890: ROLLBACK TO delete_unwanted_targets;
8888: EXCEPTION
8889: WHEN NO_DATA_FOUND THEN
8890: ROLLBACK TO delete_unwanted_targets;
8891: IF P_DEBUG_MODE = 'Y' THEN
8892: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
8893: END IF;
8894: END;
8895: end loop;
8896: END IF;
8894: END;
8895: end loop;
8896: END IF;
8897: ELSIF p_amt_type = 'PTD' THEN
8898: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for PTD';
8899: IF P_DEBUG_MODE = 'Y' THEN
8900: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8901: END IF;
8902: v_commit_count := 0;
8896: END IF;
8897: ELSIF p_amt_type = 'PTD' THEN
8898: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for PTD';
8899: IF P_DEBUG_MODE = 'Y' THEN
8900: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8901: END IF;
8902: v_commit_count := 0;
8903: IF p_basis_method = 'P' THEN
8904: IF p_run_period_type = 'PA' THEN
8902: v_commit_count := 0;
8903: IF p_basis_method = 'P' THEN
8904: IF p_run_period_type = 'PA' THEN
8905: IF P_DEBUG_MODE = 'Y' THEN
8906: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
8907: END IF;
8908: SAVEPOINT delete_unwanted_targets;
8909: DELETE FROM pa_alloc_run_targets part
8910: WHERE part.run_id = p_run_id
8917: and pta.pa_period = p_period
8918: and rownum = 1
8919: );
8920: IF P_DEBUG_MODE = 'Y' THEN
8921: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
8922: END IF;
8923: BEGIN
8924: select 'Y'
8925: into v_do_commit
8935: to do spread evenly.
8936: */
8937: COMMIT;
8938: IF P_DEBUG_MODE = 'Y' THEN
8939: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
8940: END IF;
8941: EXCEPTION
8942: WHEN NO_DATA_FOUND THEN
8943: /* switch the basis method to 'S' in case all records are deleted.
8944: We need to do rollback also
8945: */
8946: ROLLBACK TO delete_unwanted_targets;
8947: IF P_DEBUG_MODE = 'Y' THEN
8948: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
8949: END IF;
8950: p_basis_method := 'S';
8951: END;
8952: ELSE /* if p_run_period_type = GL */
8950: p_basis_method := 'S';
8951: END;
8952: ELSE /* if p_run_period_type = GL */
8953: IF P_DEBUG_MODE = 'Y' THEN
8954: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = GL');
8955: END IF;
8956: SAVEPOINT delete_unwanted_targets;
8957:
8958: /*For Bug 5403833*/
8968: and pp.period_name = pta.pa_period
8969: and pp.gl_period_name = p_period);
8970:
8971: IF P_DEBUG_MODE = 'Y' THEN
8972: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
8973: END IF;
8974: BEGIN
8975: select 'Y'
8976: into v_do_commit
8985: If all the records are deleted then we will need these records
8986: to do spread evenly.
8987: */
8988: IF P_DEBUG_MODE = 'Y' THEN
8989: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
8990: END IF;
8991: COMMIT;
8992: EXCEPTION
8993: WHEN NO_DATA_FOUND THEN
8995: we need to do rollback also
8996: */
8997: ROLLBACK TO delete_unwanted_targets;
8998: IF P_DEBUG_MODE = 'Y' THEN
8999: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
9000: END IF;
9001: p_basis_method := 'S';
9002: END;
9003: END IF; /* p_run_period_type = 'PA' */
9003: END IF; /* p_run_period_type = 'PA' */
9004: elsif p_basis_method = 'FP' then
9005: IF p_run_period_type = 'PA' THEN
9006: IF P_DEBUG_MODE = 'Y' THEN
9007: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
9008: END IF;
9009: FOR c_target_lines_rec in c_target_lines LOOP
9010: SAVEPOINT delete_unwanted_targets;
9011: DELETE FROM pa_alloc_run_targets part
9020: and pta.pa_period = p_period
9021: and rownum = 1
9022: );
9023: IF P_DEBUG_MODE = 'Y' THEN
9024: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
9025: END IF;
9026: BEGIN
9027: select 'Y'
9028: into v_do_commit
9040: No switch of basis method can be done in case of FP.
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: ROLLBACK TO delete_unwanted_targets;
9046: EXCEPTION
9047: WHEN NO_DATA_FOUND THEN
9048: ROLLBACK TO delete_unwanted_targets;
9049: IF P_DEBUG_MODE = 'Y' THEN
9050: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
9051: END IF;
9052: END;
9053: end loop;
9054: ELSE /* p_run_period_type = 'GL' */
9052: END;
9053: end loop;
9054: ELSE /* p_run_period_type = 'GL' */
9055: IF P_DEBUG_MODE = 'Y' THEN
9056: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = GL');
9057: END IF;
9058: FOR c_target_lines_rec in c_target_lines LOOP
9059: SAVEPOINT delete_unwanted_targets;
9060: /*For Bug 5403833 */
9070: and pp.period_name = pta.pa_period
9071: and pp.gl_period_name = p_period
9072: );
9073: IF P_DEBUG_MODE = 'Y' THEN
9074: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
9075: END IF;
9076: BEGIN
9077: select 'Y'
9078: into v_do_commit
9090: No switch of basis method can be done in case of FP.
9091: */
9092: COMMIT;
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: EXCEPTION
9097: WHEN NO_DATA_FOUND THEN
9098: ROLLBACK TO delete_unwanted_targets;
9096: EXCEPTION
9097: WHEN NO_DATA_FOUND THEN
9098: ROLLBACK TO delete_unwanted_targets;
9099: IF P_DEBUG_MODE = 'Y' THEN
9100: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Rolled back the deletion');
9101: END IF;
9102: END;
9103: end loop;
9104: END IF; /* p_run_period_type = 'GL' */
9106: ELSIF p_amt_type = 'ITD' THEN
9107: /* For ITD let's consider all periods in pa_periods table rather than
9108: doing this delete for each project id in the targets table. Hence do
9109: not use pa_periods table in this case. */
9110: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9111: IF P_DEBUG_MODE = 'Y' THEN
9112: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9113: END IF;
9114: v_commit_count := 0;
9108: doing this delete for each project id in the targets table. Hence do
9109: not use pa_periods table in this case. */
9110: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9111: IF P_DEBUG_MODE = 'Y' THEN
9112: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9113: END IF;
9114: v_commit_count := 0;
9115: IF p_basis_method = 'P' THEN
9116: SAVEPOINT delete_unwanted_targets;
9124: and pta.task_id = part.task_id
9125: and rownum = 1
9126: );
9127: IF P_DEBUG_MODE = 'Y' THEN
9128: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
9129: END IF;
9130: BEGIN
9131: select 'Y'
9132: into v_do_commit
9141: If all the records are deleted then we will need these records
9142: to do spread evenly.
9143: */
9144: IF P_DEBUG_MODE = 'Y' THEN
9145: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
9146: END IF;
9147: COMMIT;
9148: EXCEPTION
9149: WHEN NO_DATA_FOUND THEN
9151: we need to do rollback also
9152: */
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: p_basis_method := 'S';
9158: END;
9159: elsif p_basis_method = 'FP' then
9193: END;
9194: end loop;
9195: END IF;
9196: END IF ;
9197: pa_debug.G_err_stage:= 'exiting clean_up_targets_for_actuals';
9198: IF P_DEBUG_MODE = 'Y' THEN
9199: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9200: END IF;
9201: EXCEPTION
9195: END IF;
9196: END IF ;
9197: pa_debug.G_err_stage:= 'exiting clean_up_targets_for_actuals';
9198: IF P_DEBUG_MODE = 'Y' THEN
9199: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9200: END IF;
9201: EXCEPTION
9202: WHEN OTHERS THEN
9203: RAISE ;
9208: PROCEDURE lock_rule( p_rule_id IN NUMBER
9209: ,p_run_id IN NUMBER )
9210: IS
9211: BEGIN
9212: pa_debug.G_err_stage := 'Acquiring lock on the rule ' ;
9213: IF P_DEBUG_MODE = 'Y' THEN
9214: pa_debug.write_file('lock_rule: ' || 'LOG',pa_debug.G_err_stage);
9215: END IF;
9216: --Added this If and the else part for capital project changes
9210: IS
9211: BEGIN
9212: pa_debug.G_err_stage := 'Acquiring lock on the rule ' ;
9213: IF P_DEBUG_MODE = 'Y' THEN
9214: pa_debug.write_file('lock_rule: ' || 'LOG',pa_debug.G_err_stage);
9215: END IF;
9216: --Added this If and the else part for capital project changes
9217: IF p_rule_id <> -1 THEN
9218: If pa_debug.Acquire_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9214: pa_debug.write_file('lock_rule: ' || 'LOG',pa_debug.G_err_stage);
9215: END IF;
9216: --Added this If and the else part for capital project changes
9217: IF p_rule_id <> -1 THEN
9218: If pa_debug.Acquire_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9219: G_fatal_err_found:= TRUE;
9220: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9221: G_created_by, G_last_update_date,
9222: G_last_updated_by, G_last_update_login,
9222: G_last_updated_by, G_last_update_login,
9223: 'R', 'E', NULL, NULL, 'PA_AL_CANT_ACQUIRE_LOCK');
9224: end if;
9225: ELSE
9226: If pa_debug.Acquire_user_lock( 'PA_CINT_'||to_char(p_run_id)) <> 0 then
9227: G_fatal_err_found:= TRUE;
9228: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9229: G_created_by, G_last_update_date,
9230: G_last_updated_by, G_last_update_login,
9241: PROCEDURE unlock_rule( p_rule_id IN NUMBER
9242: ,p_run_id IN NUMBER )
9243: IS
9244: BEGIN
9245: pa_debug.G_err_stage := 'unlock the rule ' ;
9246: IF P_DEBUG_MODE = 'Y' THEN
9247: pa_debug.write_file('unlock_rule: ' || 'LOG',pa_debug.G_err_stage);
9248: END IF;
9249: IF p_rule_id <> -1 THEN
9243: IS
9244: BEGIN
9245: pa_debug.G_err_stage := 'unlock the rule ' ;
9246: IF P_DEBUG_MODE = 'Y' THEN
9247: pa_debug.write_file('unlock_rule: ' || 'LOG',pa_debug.G_err_stage);
9248: END IF;
9249: IF p_rule_id <> -1 THEN
9250: If pa_debug.Release_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9251: G_fatal_err_found:= TRUE;
9246: IF P_DEBUG_MODE = 'Y' THEN
9247: pa_debug.write_file('unlock_rule: ' || 'LOG',pa_debug.G_err_stage);
9248: END IF;
9249: IF p_rule_id <> -1 THEN
9250: If pa_debug.Release_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9251: G_fatal_err_found:= TRUE;
9252: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9253: G_created_by, G_last_update_date,
9254: G_last_updated_by, G_last_update_login,
9254: G_last_updated_by, G_last_update_login,
9255: 'R', 'E', NULL, NULL, 'PA_AL_LOCK_RELEASE_FAILED');
9256: end if;
9257: ELSE
9258: If pa_debug.Release_user_lock( 'PA_CINT_'||to_char(p_run_id)) <> 0 then
9259: G_fatal_err_found:= TRUE;
9260: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9261: G_created_by, G_last_update_date,
9262: G_last_updated_by, G_last_update_login,
9333:
9334: --Declared this variable for Capital project Changes
9335: l_transaction_source pa_lookups.meaning%TYPE;
9336: BEGIN
9337: pa_debug.set_err_stack('Release_alloc_txns') ;
9338: pa_debug.G_err_stage := 'Release_alloc_txns' ;
9339: IF P_DEBUG_MODE = 'Y' THEN
9340: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9341: END IF;
9334: --Declared this variable for Capital project Changes
9335: l_transaction_source pa_lookups.meaning%TYPE;
9336: BEGIN
9337: pa_debug.set_err_stack('Release_alloc_txns') ;
9338: pa_debug.G_err_stage := 'Release_alloc_txns' ;
9339: IF P_DEBUG_MODE = 'Y' THEN
9340: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9341: END IF;
9342: -- Init_who_cols ;
9336: BEGIN
9337: pa_debug.set_err_stack('Release_alloc_txns') ;
9338: pa_debug.G_err_stage := 'Release_alloc_txns' ;
9339: IF P_DEBUG_MODE = 'Y' THEN
9340: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9341: END IF;
9342: -- Init_who_cols ;
9343: x_errbuf := NULL ;
9344: --Included this block for Capital project changes
9349: END IF;
9350: -- No need lock the rule here. Allocation_run procedure is called in the report (PAXALRUN)
9351: -- and this procedure locks the rule.
9352: -- lock_rule(p_rule_id, p_run_id) ;
9353: pa_debug.G_err_stage := 'Inserting records in Interface table ' ;
9354: IF P_DEBUG_MODE = 'Y' THEN
9355: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9356: END IF;
9357: --- Get info about the run -----------------------
9351: -- and this procedure locks the rule.
9352: -- lock_rule(p_rule_id, p_run_id) ;
9353: pa_debug.G_err_stage := 'Inserting records in Interface table ' ;
9354: IF P_DEBUG_MODE = 'Y' THEN
9355: pa_debug.write_file('Release_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9356: END IF;
9357: --- Get info about the run -----------------------
9358: OPEN C_run;
9359: FETCH C_run INTO run_rec ;
9401: alloc_errors(p_rule_id, p_run_id, 'R', 'E','PA_AL_RELEASE_FAILED',TRUE,'N') ;
9402: END IF;
9403: end if ;
9404: IF P_DEBUG_MODE = 'Y' THEN
9405: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Inserting records in pa_transaction_interface_all');
9406: END IF;
9407: v_batch_name := substr( to_char(p_run_id), 1, 10) ;
9408: Open C_org_id ;
9409: Fetch C_org_id into v_org_id ;
9420: -- v_expnd_comment :='CAPITALIZED INTEREST ';
9421: /* end of bug 3041022 transalation issue */
9422: END IF;
9423: IF P_DEBUG_MODE = 'Y' THEN
9424: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Exp item Description : '||v_expnd_comment );
9425: END IF;
9426: ---- Validate the Items inserted in the Interface table --------
9427: --- We don't do this since transaction import will take care of this
9428: --- If we call validate item then we have to maintain that.
9540: where patd.run_id = p_run_id
9541: and patd.project_id = pp.project_id
9542: and patd.task_id = pt.task_id ;
9543: IF P_DEBUG_MODE = 'Y' THEN
9544: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', to_char(SQL%ROWCOUNT)||' Records inserted');
9545: END IF;
9546: Commit ;
9547: --- Call import procedure to import the records ------
9548: Select pa_interface_id_s.nextval
9548: Select pa_interface_id_s.nextval
9549: into v_interface_id
9550: from dual ;
9551: IF P_DEBUG_MODE = 'Y' THEN
9552: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Calling Transaction Import ..' );
9553: END IF;
9554: /* Adding exception handling for bug 2749043 */
9555: BEGIN
9556: pa_trx_import.import1( l_transaction_source -- Changed this for capital project changes
9560: , NULL ) ;
9561: EXCEPTION
9562: WHEN OTHERS THEN
9563: IF P_DEBUG_MODE = 'Y' THEN
9564: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Error occurred in Transaction Import' );
9565: END IF;
9566: END;
9567: --- Update txn_details table with import info ---------
9568: IF P_DEBUG_MODE = 'Y' THEN
9565: END IF;
9566: END;
9567: --- Update txn_details table with import info ---------
9568: IF P_DEBUG_MODE = 'Y' THEN
9569: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Updating the transactions with import results');
9570: END IF;
9571: update pa_alloc_txn_details patd
9572: set ( status_code
9573: , rejection_code
9582: and pti.transaction_source = l_transaction_source -- Changed this for capital project changes
9583: and pti.batch_name = v_batch_name )
9584: where run_id = p_run_id ;
9585: IF P_DEBUG_MODE = 'Y' THEN
9586: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', to_char(SQL%ROWCOUNT)||' Records updated');
9587: END IF;
9588:
9589: /**** added for bug 6243121 ***/
9590:
9618: When NO_DATA_FOUND then
9619: v_import_failed := 'N' ;
9620: END;
9621: IF P_DEBUG_MODE = 'Y' THEN
9622: pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'v_import_failed :' ||v_import_failed );
9623: END IF;
9624: v_tgt_exp_group := v_batch_name||run_rec.target_exp_type_class||to_char(v_interface_id);
9625: v_off_exp_group := v_batch_name||run_rec.offset_exp_type_class||to_char(v_interface_id);
9626: If v_import_failed ='Y' then
9652: ---- Release Lock on the rule_name ---------------------
9653: -- No need unlock the rule here. Allocation_run procedure is called in the report (PAXALRUN)
9654: -- and this procedure locks and unlocks the rule.
9655: -- unlock_rule(p_rule_id, p_run_id) ;
9656: pa_debug.reset_err_stack ;
9657: EXCEPTION
9658: When OTHERS then
9659: RAISE ;
9660: END Release_alloc_txns ;
9678: v_num_reversed NUMBER ;
9679: v_num_rejected NUMBER ;
9680: v_return_code VARCHAR2(30) ;
9681: BEGIN
9682: -- pa_debug.G_process := 'SQL' ;
9683: pa_debug.Init_err_stack('Start') ;
9684: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9685: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9686: IF P_DEBUG_MODE = 'Y' THEN
9679: v_num_rejected NUMBER ;
9680: v_return_code VARCHAR2(30) ;
9681: BEGIN
9682: -- pa_debug.G_process := 'SQL' ;
9683: pa_debug.Init_err_stack('Start') ;
9684: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9685: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9686: IF P_DEBUG_MODE = 'Y' THEN
9687: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9680: v_return_code VARCHAR2(30) ;
9681: BEGIN
9682: -- pa_debug.G_process := 'SQL' ;
9683: pa_debug.Init_err_stack('Start') ;
9684: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9685: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9686: IF P_DEBUG_MODE = 'Y' THEN
9687: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9688: END IF;
9681: BEGIN
9682: -- pa_debug.G_process := 'SQL' ;
9683: pa_debug.Init_err_stack('Start') ;
9684: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9685: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9686: IF P_DEBUG_MODE = 'Y' THEN
9687: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9688: END IF;
9689: Init_who_cols ;
9683: pa_debug.Init_err_stack('Start') ;
9684: pa_debug.set_err_stack('Reverse_alloc_txns') ;
9685: pa_debug.G_err_stage := 'Reverse_alloc_txns' ;
9686: IF P_DEBUG_MODE = 'Y' THEN
9687: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9688: END IF;
9689: Init_who_cols ;
9690: lock_rule(p_rule_id, p_run_id) ;
9691: /*added for bug#2357646 */
9698: return;
9699: end if;
9700: /*end of fix for bug#2357646 */
9701: ---- Check for the group name and call reverse group ------
9702: pa_debug.G_err_stage := 'Checking for expenditure groups' ;
9703: IF P_DEBUG_MODE = 'Y' THEN
9704: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', pa_debug.G_err_stage);
9705: END IF;
9706: OPEN C_run ;
9700: /*end of fix for bug#2357646 */
9701: ---- Check for the group name and call reverse group ------
9702: pa_debug.G_err_stage := 'Checking for expenditure groups' ;
9703: IF P_DEBUG_MODE = 'Y' THEN
9704: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', pa_debug.G_err_stage);
9705: END IF;
9706: OPEN C_run ;
9707: Fetch C_run into run_rec ;
9708: Close C_run ;
9715: , v_return_code
9716: , 'RELEASED' ) ;
9717: If nvl(v_return_code,'0') <> '0' then
9718: IF P_DEBUG_MODE = 'Y' THEN
9719: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9720: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9721: to_char(v_num_rejected) );
9722: END IF;
9723: -- pa_debug.raise_error('-20010',v_return_code) ;
9716: , 'RELEASED' ) ;
9717: If nvl(v_return_code,'0') <> '0' then
9718: IF P_DEBUG_MODE = 'Y' THEN
9719: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9720: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9721: to_char(v_num_rejected) );
9722: END IF;
9723: -- pa_debug.raise_error('-20010',v_return_code) ;
9724: else
9719: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9720: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9721: to_char(v_num_rejected) );
9722: END IF;
9723: -- pa_debug.raise_error('-20010',v_return_code) ;
9724: else
9725: IF P_DEBUG_MODE = 'Y' THEN
9726: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed :'||
9727: to_char(v_num_reversed) );
9722: END IF;
9723: -- pa_debug.raise_error('-20010',v_return_code) ;
9724: else
9725: IF P_DEBUG_MODE = 'Y' THEN
9726: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed :'||
9727: to_char(v_num_reversed) );
9728: END IF;
9729: end if ;
9730: If run_rec.offset_exp_group is NOT NULL then
9738: , v_return_code
9739: , 'RELEASED' ) ;
9740: if nvl(v_return_code,'0') <> '0' then
9741: IF P_DEBUG_MODE = 'Y' THEN
9742: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9743: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9744: to_char(v_num_rejected) );
9745: END IF;
9746: pa_debug.raise_error('-20010',v_return_code) ;
9739: , 'RELEASED' ) ;
9740: if nvl(v_return_code,'0') <> '0' then
9741: IF P_DEBUG_MODE = 'Y' THEN
9742: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9743: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9744: to_char(v_num_rejected) );
9745: END IF;
9746: pa_debug.raise_error('-20010',v_return_code) ;
9747: else
9742: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG', 'Reversing Error :'||v_return_code);
9743: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed in Rejection :'||
9744: to_char(v_num_rejected) );
9745: END IF;
9746: pa_debug.raise_error('-20010',v_return_code) ;
9747: else
9748: IF P_DEBUG_MODE = 'Y' THEN
9749: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed :'||
9750: to_char(v_num_reversed) );
9745: END IF;
9746: pa_debug.raise_error('-20010',v_return_code) ;
9747: else
9748: IF P_DEBUG_MODE = 'Y' THEN
9749: pa_debug.write_file('Reverse_alloc_txns: ' || 'LOG','Records Reversed :'||
9750: to_char(v_num_reversed) );
9751: END IF;
9752: end if ;
9753: End if;
9765: x_errbuf := v_return_code ;
9766: end if ;
9767: ---- Release Lock on the rule_name ---------------------
9768: unlock_rule(p_rule_id, p_run_id) ;
9769: pa_debug.reset_err_stack ;
9770: EXCEPTION
9771: WHEN OTHERS THEN
9772: --Added this code for capital project changes
9773: BEGIN
9786: PROCEDURE Delete_alloc_txns( p_rule_id IN NUMBER
9787: ,p_run_id IN NUMBER)
9788: IS
9789: BEGIN
9790: -- pa_debug.G_process := 'SQL' ;
9791: pa_debug.Init_err_stack('Start') ;
9792: pa_debug.set_err_stack('Delete_alloc_txns') ;
9793: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9794: IF P_DEBUG_MODE = 'Y' THEN
9787: ,p_run_id IN NUMBER)
9788: IS
9789: BEGIN
9790: -- pa_debug.G_process := 'SQL' ;
9791: pa_debug.Init_err_stack('Start') ;
9792: pa_debug.set_err_stack('Delete_alloc_txns') ;
9793: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9794: IF P_DEBUG_MODE = 'Y' THEN
9795: pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9788: IS
9789: BEGIN
9790: -- pa_debug.G_process := 'SQL' ;
9791: pa_debug.Init_err_stack('Start') ;
9792: pa_debug.set_err_stack('Delete_alloc_txns') ;
9793: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9794: IF P_DEBUG_MODE = 'Y' THEN
9795: pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9796: END IF;
9789: BEGIN
9790: -- pa_debug.G_process := 'SQL' ;
9791: pa_debug.Init_err_stack('Start') ;
9792: pa_debug.set_err_stack('Delete_alloc_txns') ;
9793: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9794: IF P_DEBUG_MODE = 'Y' THEN
9795: pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9796: END IF;
9797: Init_who_cols ;
9791: pa_debug.Init_err_stack('Start') ;
9792: pa_debug.set_err_stack('Delete_alloc_txns') ;
9793: pa_debug.G_err_stage := 'Delete_alloc_txns' ;
9794: IF P_DEBUG_MODE = 'Y' THEN
9795: pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
9796: END IF;
9797: Init_who_cols ;
9798: lock_rule(p_rule_id, p_run_id) ;
9799: -- Delete Transactions from pa_alloc_txn_details
9878: Delete from pa_alloc_runs
9879: where run_id = p_run_id ;
9880: ---- Release Lock on the rule_name ---------------------
9881: unlock_rule(p_rule_id , p_run_id) ;
9882: pa_debug.reset_err_stack ;
9883: EXCEPTION
9884: WHEN OTHERS THEN
9885: RAISE ;
9886: END Delete_alloc_txns ;
9905: l_return_status VARCHAR2(1);
9906: l_msg_data VARCHAR2(1000);
9907: l_msg_count NUMBER :=0;
9908: BEGIN
9909: -- pa_debug.G_process := 'SQL' ;
9910: -- v_debug_mode := NVL(p_debug_mode, 'Y');
9911: --v_process_mode := NVL(p_process_mode, 'SQL');
9912: pa_debug.Init_err_stack('Start') ;
9913: v_debug_mode := 'Y';
9908: BEGIN
9909: -- pa_debug.G_process := 'SQL' ;
9910: -- v_debug_mode := NVL(p_debug_mode, 'Y');
9911: --v_process_mode := NVL(p_process_mode, 'SQL');
9912: pa_debug.Init_err_stack('Start') ;
9913: v_debug_mode := 'Y';
9914: v_process_mode := 'PLSQL';
9915: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
9916: pa_debug.set_err_stack('Delete_alloc_run') ;
9911: --v_process_mode := NVL(p_process_mode, 'SQL');
9912: pa_debug.Init_err_stack('Start') ;
9913: v_debug_mode := 'Y';
9914: v_process_mode := 'PLSQL';
9915: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
9916: pa_debug.set_err_stack('Delete_alloc_run') ;
9917: pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
9918: IF P_DEBUG_MODE = 'Y' THEN
9919: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9912: pa_debug.Init_err_stack('Start') ;
9913: v_debug_mode := 'Y';
9914: v_process_mode := 'PLSQL';
9915: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
9916: pa_debug.set_err_stack('Delete_alloc_run') ;
9917: pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
9918: IF P_DEBUG_MODE = 'Y' THEN
9919: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9920: END IF;
9913: v_debug_mode := 'Y';
9914: v_process_mode := 'PLSQL';
9915: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
9916: pa_debug.set_err_stack('Delete_alloc_run') ;
9917: pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
9918: IF P_DEBUG_MODE = 'Y' THEN
9919: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9920: END IF;
9921: Init_who_cols ;
9915: pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
9916: pa_debug.set_err_stack('Delete_alloc_run') ;
9917: pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
9918: IF P_DEBUG_MODE = 'Y' THEN
9919: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9920: END IF;
9921: Init_who_cols ;
9922: FOR run_rec IN get_run_id LOOP
9923: l_run_id := run_rec.run_id;
9920: END IF;
9921: Init_who_cols ;
9922: FOR run_rec IN get_run_id LOOP
9923: l_run_id := run_rec.run_id;
9924: pa_debug.G_err_stage := 'Delete Rule Id' || To_char(p_rule_id) ;
9925: IF P_DEBUG_MODE = 'Y' THEN
9926: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9927: END IF;
9928: pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
9922: FOR run_rec IN get_run_id LOOP
9923: l_run_id := run_rec.run_id;
9924: pa_debug.G_err_stage := 'Delete Rule Id' || To_char(p_rule_id) ;
9925: IF P_DEBUG_MODE = 'Y' THEN
9926: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9927: END IF;
9928: pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
9929: IF P_DEBUG_MODE = 'Y' THEN
9930: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9924: pa_debug.G_err_stage := 'Delete Rule Id' || To_char(p_rule_id) ;
9925: IF P_DEBUG_MODE = 'Y' THEN
9926: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9927: END IF;
9928: pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
9929: IF P_DEBUG_MODE = 'Y' THEN
9930: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9931: END IF;
9932: --Delete all the txn source details when this api is called in
9926: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9927: END IF;
9928: pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
9929: IF P_DEBUG_MODE = 'Y' THEN
9930: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9931: END IF;
9932: --Delete all the txn source details when this api is called in
9933: --the context of Capitalized Interest
9934: IF p_rule_id = -1 THEN
9938: ,x_msg_data => l_msg_data
9939: ,x_msg_count => l_msg_count);
9940: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9941: IF P_DEBUG_MODE = 'Y' THEN
9942: pa_debug.G_err_stage := 'delte cint sources errored out' ;
9943: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9944: END IF;
9945: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9946: END IF;
9939: ,x_msg_count => l_msg_count);
9940: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9941: IF P_DEBUG_MODE = 'Y' THEN
9942: pa_debug.G_err_stage := 'delte cint sources errored out' ;
9943: pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
9944: END IF;
9945: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9946: END IF;
9947: END IF;
10029: where run_id = l_run_id ;
10030: ---- Release Lock on the rule_name ---------------------
10031: --unlock_rule(p_rule_id , l_run_id) ;
10032: END LOOP;
10033: pa_debug.reset_err_stack ;
10034: EXCEPTION
10035: WHEN OTHERS THEN
10036: RAISE ;
10037: END Delete_alloc_run ;
10247: l_module_name VARCHAR2(100);
10248: BEGIN
10249: x_msg_count := 0;
10250: x_return_status := FND_API.G_RET_STS_SUCCESS;
10251: l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10252: l_module_name := 'release_capint_txns';
10253: pa_debug.set_curr_function( p_function => 'release_capint_txns',
10254: p_debug_mode => l_debug_mode );
10255: IF l_debug_mode = 'Y' THEN
10249: x_msg_count := 0;
10250: x_return_status := FND_API.G_RET_STS_SUCCESS;
10251: l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10252: l_module_name := 'release_capint_txns';
10253: pa_debug.set_curr_function( p_function => 'release_capint_txns',
10254: p_debug_mode => l_debug_mode );
10255: IF l_debug_mode = 'Y' THEN
10256: pa_debug.G_err_stage := 'About to call release_alloc_txns';
10257: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10252: l_module_name := 'release_capint_txns';
10253: pa_debug.set_curr_function( p_function => 'release_capint_txns',
10254: p_debug_mode => l_debug_mode );
10255: IF l_debug_mode = 'Y' THEN
10256: pa_debug.G_err_stage := 'About to call release_alloc_txns';
10257: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10258: END IF;
10259: --Initialise the who columns
10260: pa_alloc_run.init_who_cols;
10253: pa_debug.set_curr_function( p_function => 'release_capint_txns',
10254: p_debug_mode => l_debug_mode );
10255: IF l_debug_mode = 'Y' THEN
10256: pa_debug.G_err_stage := 'About to call release_alloc_txns';
10257: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10258: END IF;
10259: --Initialise the who columns
10260: pa_alloc_run.init_who_cols;
10261: --Call the api that releases the allocation run
10264: ,p_run_id => p_run_id
10265: ,x_retcode => x_return_status
10266: ,x_errbuf => x_msg_data);
10267: IF l_debug_mode = 'Y' THEN
10268: pa_debug.G_err_stage := 'Returned after releasing with msg '||x_msg_data;
10269: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10270: END IF;
10271: --If failed insert errors into alloc_exceptions table and raise error
10272: IF x_msg_data is NOT NULL then
10265: ,x_retcode => x_return_status
10266: ,x_errbuf => x_msg_data);
10267: IF l_debug_mode = 'Y' THEN
10268: pa_debug.G_err_stage := 'Returned after releasing with msg '||x_msg_data;
10269: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10270: END IF;
10271: --If failed insert errors into alloc_exceptions table and raise error
10272: IF x_msg_data is NOT NULL then
10273: x_return_status := FND_API.G_RET_STS_ERROR;
10276: x_return_status := 'S';
10277: End if ;
10278: --Update the capital interest run status
10279: IF l_debug_mode = 'Y' THEN
10280: pa_debug.G_err_stage := 'About to update the Release status to ['||x_return_status||']';
10281: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10282: END IF;
10283: /* bug fix: 3123539 based on the return status update the run staus and avoid
10284: * setting the concurrent process to raise error
10277: End if ;
10278: --Update the capital interest run status
10279: IF l_debug_mode = 'Y' THEN
10280: pa_debug.G_err_stage := 'About to update the Release status to ['||x_return_status||']';
10281: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10282: END IF;
10283: /* bug fix: 3123539 based on the return status update the run staus and avoid
10284: * setting the concurrent process to raise error
10285: */
10303: x_msg_count := 0;
10304: x_return_status := FND_API.G_RET_STS_SUCCESS;
10305: x_msg_data := Null;
10306: IF l_debug_mode = 'Y' THEN
10307: pa_debug.g_err_stage:= 'Leaving release_capint_txns return status ['||x_return_status||']';
10308: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10309: END IF;
10310: pa_debug.reset_curr_function;
10311: EXCEPTION
10304: x_return_status := FND_API.G_RET_STS_SUCCESS;
10305: x_msg_data := Null;
10306: IF l_debug_mode = 'Y' THEN
10307: pa_debug.g_err_stage:= 'Leaving release_capint_txns return status ['||x_return_status||']';
10308: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10309: END IF;
10310: pa_debug.reset_curr_function;
10311: EXCEPTION
10312: WHEN OTHERS THEN
10306: IF l_debug_mode = 'Y' THEN
10307: pa_debug.g_err_stage:= 'Leaving release_capint_txns return status ['||x_return_status||']';
10308: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10309: END IF;
10310: pa_debug.reset_curr_function;
10311: EXCEPTION
10312: WHEN OTHERS THEN
10313: x_return_status := FND_API.G_RET_STS_ERROR;
10314: x_msg_count := FND_MSG_PUB.count_msg;
10313: x_return_status := FND_API.G_RET_STS_ERROR;
10314: x_msg_count := FND_MSG_PUB.count_msg;
10315: IF l_debug_mode = 'Y' THEN
10316: x_msg_data:=sqlerrm;
10317: pa_debug.write_file('cap_int run: ' || 'LOG', x_msg_data);
10318: pa_debug.write_file('cap_int run: ' || 'LOG', pa_debug.G_err_stack);
10319: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10320: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10321: END IF;
10314: x_msg_count := FND_MSG_PUB.count_msg;
10315: IF l_debug_mode = 'Y' THEN
10316: x_msg_data:=sqlerrm;
10317: pa_debug.write_file('cap_int run: ' || 'LOG', x_msg_data);
10318: pa_debug.write_file('cap_int run: ' || 'LOG', pa_debug.G_err_stack);
10319: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10320: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10321: END IF;
10322: -- Update the status to release failure
10315: IF l_debug_mode = 'Y' THEN
10316: x_msg_data:=sqlerrm;
10317: pa_debug.write_file('cap_int run: ' || 'LOG', x_msg_data);
10318: pa_debug.write_file('cap_int run: ' || 'LOG', pa_debug.G_err_stack);
10319: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10320: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10321: END IF;
10322: -- Update the status to release failure
10323: UPDATE pa_alloc_runs
10316: x_msg_data:=sqlerrm;
10317: pa_debug.write_file('cap_int run: ' || 'LOG', x_msg_data);
10318: pa_debug.write_file('cap_int run: ' || 'LOG', pa_debug.G_err_stack);
10319: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10320: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10321: END IF;
10322: -- Update the status to release failure
10323: UPDATE pa_alloc_runs
10324: SET run_status = 'RF'
10325: ,release_request_id = l_rel_request_id
10326: ,release_request_date =sysdate
10327: WHERE run_id = p_run_id;
10328: IF l_debug_mode = 'Y' THEN
10329: pa_debug.write_file('LOG','p_run_id in when others of Main: '||to_char( p_run_id) );
10330: END IF;
10331: COMMIT;
10332: pa_debug.reset_curr_function;
10333: completion_status := fnd_concurrent.set_completion_status('ERROR', SQLERRM);
10328: IF l_debug_mode = 'Y' THEN
10329: pa_debug.write_file('LOG','p_run_id in when others of Main: '||to_char( p_run_id) );
10330: END IF;
10331: COMMIT;
10332: pa_debug.reset_curr_function;
10333: completion_status := fnd_concurrent.set_completion_status('ERROR', SQLERRM);
10334: END release_capint_txns;
10335: --This procedure deletes the source details for each capital interest transaction. This procedure will
10336: --be called from delete_alloc_run api when the DELETE button is pressed to delete a capital interest
10359: WHERE run_id=p_run_id;
10360: BEGIN
10361: x_msg_count := 0;
10362: x_return_status := FND_API.G_RET_STS_SUCCESS;
10363: l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10364: l_module_name := 'delete_cint_source_dets';
10365: pa_debug.set_curr_function( p_function => l_module_name,
10366: p_debug_mode => l_debug_mode );
10367: IF l_debug_mode = 'Y' THEN
10361: x_msg_count := 0;
10362: x_return_status := FND_API.G_RET_STS_SUCCESS;
10363: l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10364: l_module_name := 'delete_cint_source_dets';
10365: pa_debug.set_curr_function( p_function => l_module_name,
10366: p_debug_mode => l_debug_mode );
10367: IF l_debug_mode = 'Y' THEN
10368: pa_debug.g_err_stage:= 'About to delete the source txn details';
10369: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10364: l_module_name := 'delete_cint_source_dets';
10365: pa_debug.set_curr_function( p_function => l_module_name,
10366: p_debug_mode => l_debug_mode );
10367: IF l_debug_mode = 'Y' THEN
10368: pa_debug.g_err_stage:= 'About to delete the source txn details';
10369: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10370: END IF;
10371: FOR c_cint_txn_rec IN c_get_cint_txns LOOP
10372: LOOP
10365: pa_debug.set_curr_function( p_function => l_module_name,
10366: p_debug_mode => l_debug_mode );
10367: IF l_debug_mode = 'Y' THEN
10368: pa_debug.g_err_stage:= 'About to delete the source txn details';
10369: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10370: END IF;
10371: FOR c_cint_txn_rec IN c_get_cint_txns LOOP
10372: LOOP
10373: DELETE
10379: END IF;
10380: END LOOP;
10381: END LOOP;
10382: IF l_debug_mode = 'Y' THEN
10383: pa_debug.g_err_stage:= 'Leaving delete_cint_source_dets';
10384: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10385: END IF;
10386: pa_debug.reset_curr_function;
10387: EXCEPTION
10380: END LOOP;
10381: END LOOP;
10382: IF l_debug_mode = 'Y' THEN
10383: pa_debug.g_err_stage:= 'Leaving delete_cint_source_dets';
10384: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10385: END IF;
10386: pa_debug.reset_curr_function;
10387: EXCEPTION
10388: WHEN others THEN
10382: IF l_debug_mode = 'Y' THEN
10383: pa_debug.g_err_stage:= 'Leaving delete_cint_source_dets';
10384: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10385: END IF;
10386: pa_debug.reset_curr_function;
10387: EXCEPTION
10388: WHEN others THEN
10389: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10390: x_msg_count := 1;
10393: ( p_pkg_name => 'pa_alloc_run'
10394: ,p_procedure_name => 'delete_cint_source_dets'
10395: ,p_error_text => x_msg_data);
10396: IF l_debug_mode = 'Y' THEN
10397: pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
10398: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
10399: END IF;
10400: pa_debug.reset_curr_function;
10401: RAISE;
10394: ,p_procedure_name => 'delete_cint_source_dets'
10395: ,p_error_text => x_msg_data);
10396: IF l_debug_mode = 'Y' THEN
10397: pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
10398: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
10399: END IF;
10400: pa_debug.reset_curr_function;
10401: RAISE;
10402: END delete_cint_source_dets;
10396: IF l_debug_mode = 'Y' THEN
10397: pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
10398: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
10399: END IF;
10400: pa_debug.reset_curr_function;
10401: RAISE;
10402: END delete_cint_source_dets;
10403: END PA_ALLOC_run;