[Home] [Help]
145: -- Bug 8463760
146: l_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag%TYPE;
147:
148: BEGIN
149: --hr_utility.trace_on(null, 'dlai');
150: --hr_utility.trace('STARTING');
151: x_return_status := FND_API.G_RET_STS_SUCCESS;
152: -- x_primary_pp_bv_id := p_orgfcst_version_id;
153: pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID := p_orgfcst_version_id;
146: l_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag%TYPE;
147:
148: BEGIN
149: --hr_utility.trace_on(null, 'dlai');
150: --hr_utility.trace('STARTING');
151: x_return_status := FND_API.G_RET_STS_SUCCESS;
152: -- x_primary_pp_bv_id := p_orgfcst_version_id;
153: pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID := p_orgfcst_version_id;
154: pa_fp_view_plans_pub.G_AMT_OR_PD := p_amt_or_pd; -- this will be used by other procedures
163: ,x_msg_count => l_msg_count
164: ,x_msg_data => l_msg_data);
165: pa_fp_view_plans_pub.G_UNCAT_RLM_ID := l_uncat_rlm_id;
166: x_uncat_rlmid := l_uncat_rlm_id;
167: --hr_utility.trace('Uncategorized Resource List Member Id is ' || to_char(l_uncat_rlm_id));
168: --hr_utility.trace('Amt/Period toggle is ' || pa_fp_view_plans_pub.G_AMT_OR_PD);
169: select bv.project_id,
170: DECODE(p_view_currency_type,
171: 'PROJ', pa.project_currency_code,
164: ,x_msg_data => l_msg_data);
165: pa_fp_view_plans_pub.G_UNCAT_RLM_ID := l_uncat_rlm_id;
166: x_uncat_rlmid := l_uncat_rlm_id;
167: --hr_utility.trace('Uncategorized Resource List Member Id is ' || to_char(l_uncat_rlm_id));
168: --hr_utility.trace('Amt/Period toggle is ' || pa_fp_view_plans_pub.G_AMT_OR_PD);
169: select bv.project_id,
170: DECODE(p_view_currency_type,
171: 'PROJ', pa.project_currency_code,
172: 'PROJFUNC', pa.projfunc_currency_code,
214: fin_plan_type_id = l_fin_plan_type_id and
215: fin_plan_option_level_code='PLAN_TYPE';
216: EXCEPTION
217: WHEN NO_DATA_FOUND THEN
218: --hr_utility.trace('no_data_found 1');
219: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
220: x_msg_count := 1;
221: x_msg_data := SQLERRM;
222: FND_MSG_PUB.add_exc_msg
222: FND_MSG_PUB.add_exc_msg
223: ( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
224: p_procedure_name => 'pa_fp_viewplan_hgrid_init:plan start/end dates: 100');
225: END;
226: --hr_utility.trace('stage 100 passed');
227: x_plan_type_id := l_fin_plan_type_id; -- OUTPUT: x_plan_type_id
228: pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM := l_margin_derived_from_code;
229: x_calc_margin_from := l_margin_derived_from_code; -- OUTPUT: x_calc_margin_from
230: pa_fp_view_plans_pub.G_FP_CALC_QUANTITY_FROM := l_labor_hours_from_code;
371: px_display_margin := l_display_margin;
372: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
373: px_display_marginpct := l_display_marginpct;
374: end if;
375: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
376: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
377: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
378: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
379: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
372: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
373: px_display_marginpct := l_display_marginpct;
374: end if;
375: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
376: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
377: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
378: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
379: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
380: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
373: px_display_marginpct := l_display_marginpct;
374: end if;
375: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
376: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
377: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
378: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
379: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
380: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
381:
374: end if;
375: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
376: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
377: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
378: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
379: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
380: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
381:
382: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
375: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
376: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
377: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
378: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
379: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
380: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
381:
382: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
383: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
376: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
377: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
378: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
379: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
380: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
381:
382: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
383: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
384: l_default_amount_type_code := 'QUANTITY';
436: pa_fp_view_plans_pub.G_DEFAULT_AMT_SUBTYPE_CODE := l_default_amount_subtype_code;
437: x_def_amt_subt_code := l_default_amount_subtype_code; -- OUTPUT: x_def_amt_subt_code
438: pa_fp_view_plans_pub.G_FP_CURRENCY_CODE := l_projfunc_currency_code;
439: pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE := p_view_currency_type;
440: --hr_utility.trace('reached end of user customizations section: 200');
441:
442:
443: -- SETTING PERIOD INFORMATION --
444: -- ONLY IF IN PERIODIC MODE --
478: FND_MSG_PUB.add_exc_msg
479: ( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
480: p_procedure_name => 'pa_fp_viewplan_hgrid_init:plan period type: 300');
481: END;
482: --hr_utility.trace('after selecting from pa_proj_period_profiles: 300 succeeded');
483:
484: -- get period info based on period_profile_id
485: /* Starts Commented for bug 7456425
486: Pa_Prj_Period_Profile_Utils.Get_Prj_Period_Profile_Dtls(
496: x_number_of_periods => l_number_of_periods,
497: x_return_status => l_return_status,
498: x_msg_data => l_msg_data);
499: -- get current period info
500: --hr_utility.trace('executed get_prj_Period_profile_dtls');
501: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
502: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
503: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
504: p_project_id => l_project_id
497: x_return_status => l_return_status,
498: x_msg_data => l_msg_data);
499: -- get current period info
500: --hr_utility.trace('executed get_prj_Period_profile_dtls');
501: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
502: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
503: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
504: p_project_id => l_project_id
505: ,p_period_type => l_plan_period_type
498: x_msg_data => l_msg_data);
499: -- get current period info
500: --hr_utility.trace('executed get_prj_Period_profile_dtls');
501: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
502: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
503: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
504: p_project_id => l_project_id
505: ,p_period_type => l_plan_period_type
506: ,p_period_profile_type => 'FINANCIAL_PLANNING'
509: ,x_end_period => l_cur_end_period
510: ,x_return_status => l_return_status
511: ,x_msg_count => l_msg_count
512: ,x_msg_data => l_msg_data);
513: --hr_utility.trace('executed get_curr_period_profile_info');
514: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
515: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
516: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
517: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
510: ,x_return_status => l_return_status
511: ,x_msg_count => l_msg_count
512: ,x_msg_data => l_msg_data);
513: --hr_utility.trace('executed get_curr_period_profile_info');
514: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
515: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
516: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
517: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
518:
511: ,x_msg_count => l_msg_count
512: ,x_msg_data => l_msg_data);
513: --hr_utility.trace('executed get_curr_period_profile_info');
514: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
515: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
516: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
517: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
518:
519: if l_period_profile_id = l_cur_period_profile_id then
512: ,x_msg_data => l_msg_data);
513: --hr_utility.trace('executed get_curr_period_profile_info');
514: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
515: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
516: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
517: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
518:
519: if l_period_profile_id = l_cur_period_profile_id then
520: x_old_pd_profile_flag := 'N'; -- OUTPUT: x_old_pd_profile_flag
513: --hr_utility.trace('executed get_curr_period_profile_info');
514: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
515: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
516: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
517: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
518:
519: if l_period_profile_id = l_cur_period_profile_id then
520: x_old_pd_profile_flag := 'N'; -- OUTPUT: x_old_pd_profile_flag
521: else
658: x_return_status => l_return_status,
659: x_msg_count => l_msg_count,
660: x_msg_data => l_msg_data);
661:
662: --hr_utility.trace('END OF SETTING PERIOD INFORMATION: 400');
663:
664: end if; -- check p_amt_or_pd flag
665: -- END OF SETTING PERIOD INFORMATION --
666:
677: -- G_FP_REV_VERSION_NUMBER
678: -- G_FP_ALL_VERSION_ID (may be set already, if user-entered)
679: -- G_FP_ALL_VERSION_NAME
680: -- G_FP_ALL_VERSION_NUMBER
681: --hr_utility.trace('calling view_plan_temp_tables');
682: -- delete residual data in the temp tables
683: DELETE from PA_FIN_VP_AMTS_VIEW_TMP;
684: DELETE from PA_FIN_VP_PDS_VIEW_TMP;
685: pa_fp_view_plans_pub.view_plan_temp_tables
733: and tmp2.resource_list_member_id>0);
734: END IF;
735: --End Bug 8463760
736:
737: --hr_utility.trace('after calling view_plan_temp_tables: 500');
738: -- END OF POPULATING GLOBAL TEMPORARY TABLE --
739:
740: x_display_from := pa_fp_view_plans_pub.G_DISPLAY_FROM; -- OUTPUT: x_display_from
741: x_diff_pd_profile_flag := l_diff_pd_profile_flag; -- OUTPUT: x_diff_pd_profile_flag
947: -- figure out LOCKED BY after version sources set; also get RECORD VERSION NUMBERS
948: -- COST VERSION
949: if (pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null) and
950: (pa_fp_view_plans_pub.G_DISPLAY_FROM in ('COST', 'BOTH', 'ANY')) then
951: --hr_utility.trace('figuring out cost LOCKED BY');
952: select record_version_number, -- OUTPUT: x_cost_rv_number
953: budget_status_code -- OUTPUT: x_cost_budget_status_code
954: into x_cost_rv_number,
955: x_cost_budget_status_code
978:
979: -- REVENUE VERSION
980: if (pa_fp_view_plans_pub.G_FP_REV_VERSION_ID is not null) and
981: (pa_fp_view_plans_pub.G_DISPLAY_FROM in ('REVENUE', 'BOTH', 'ANY')) then
982: --hr_utility.trace('figuring out revenue LOCKED BY');
983: select record_version_number, -- OUTPUT: x_rev_rv_number
984: budget_status_code -- OUTPUT: x_rev_budget_status_code
985: into x_rev_rv_number,
986: x_rev_budget_status_code
1007: end if; -- is_rev_locked_by_user
1008: end if; -- END:REVENUE VERSION
1009:
1010:
1011: --hr_utility.trace('after calling locked version stuff');
1012: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
1013: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
1014: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1008: end if; -- END:REVENUE VERSION
1009:
1010:
1011: --hr_utility.trace('after calling locked version stuff');
1012: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
1013: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
1014: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1009:
1010:
1011: --hr_utility.trace('after calling locked version stuff');
1012: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
1013: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
1014: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1017: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
1010:
1011: --hr_utility.trace('after calling locked version stuff');
1012: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
1013: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
1014: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1017: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
1018: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
1011: --hr_utility.trace('after calling locked version stuff');
1012: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
1013: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
1014: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1017: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
1018: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
1019: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
1012: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
1013: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
1014: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1017: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
1018: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
1019: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
1020: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
1013: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
1014: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1017: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
1018: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
1019: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
1020: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
1021:
1014: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1017: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
1018: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
1019: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
1020: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
1021:
1022: EXCEPTION
1015: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1017: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
1018: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
1019: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
1020: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
1021:
1022: EXCEPTION
1023: WHEN OTHERS THEN
1016: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
1017: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
1018: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
1019: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
1020: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
1021:
1022: EXCEPTION
1023: WHEN OTHERS THEN
1024: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1668: fin_plan_type_id = l_fin_plan_type_id and
1669: fin_plan_option_level_code='PLAN_TYPE';
1670: EXCEPTION
1671: WHEN NO_DATA_FOUND THEN
1672: --hr_utility.trace('no_data_found 1');
1673: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1674: x_msg_count := 1;
1675: x_msg_data := SQLERRM;
1676: FND_MSG_PUB.add_exc_msg
1676: FND_MSG_PUB.add_exc_msg
1677: ( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
1678: p_procedure_name => 'pa_fp_viewplan_hgrid_init:plan start/end dates: 100');
1679: END;
1680: --hr_utility.trace('stage 100 passed');
1681: x_plan_type_id := l_fin_plan_type_id; -- OUTPUT: x_plan_type_id
1682: pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM := l_margin_derived_from_code;
1683: x_calc_margin_from := l_margin_derived_from_code; -- OUTPUT: x_calc_margin_from
1684: pa_fp_view_plans_pub.G_FP_CALC_QUANTITY_FROM := l_labor_hours_from_code;
1746: pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE := l_display_revenue;
1747: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN := l_display_margin;
1748: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
1749: end if;
1750: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1751: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1752: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1753: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1754: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1747: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN := l_display_margin;
1748: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
1749: end if;
1750: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1751: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1752: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1753: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1754: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1755: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1748: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
1749: end if;
1750: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1751: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1752: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1753: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1754: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1755: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1756:
1749: end if;
1750: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1751: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1752: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1753: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1754: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1755: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1756:
1757: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
1750: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1751: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1752: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1753: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1754: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1755: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1756:
1757: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
1758: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
1751: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1752: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1753: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1754: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1755: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1756:
1757: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
1758: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
1759: l_default_amount_type_code := 'QUANTITY';
1812: pa_fp_view_plans_pub.G_DEFAULT_AMOUNT_TYPE_CODE := l_default_amount_type_code;
1813: pa_fp_view_plans_pub.G_DEFAULT_AMT_SUBTYPE_CODE := l_default_amount_subtype_code;
1814: pa_fp_view_plans_pub.G_FP_CURRENCY_CODE := l_projfunc_currency_code;
1815: pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE := p_view_currency_type;
1816: --hr_utility.trace('reached end of user customizations section: 200');
1817:
1818: BEGIN
1819: /* Commented for bug 7514054
1820: select pp.plan_period_type,
1849: FND_MSG_PUB.add_exc_msg
1850: ( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
1851: p_procedure_name => 'pa_fp_viewplan_hgrid_init:plan period type: 300');
1852: END;
1853: --hr_utility.trace('after selecting from pa_proj_period_profiles: 300 succeeded');
1854:
1855:
1856: -- SETTING PERIOD INFORMATION --
1857: -- ONLY IF IN PERIODIC MODE --
1871: x_number_of_periods => l_number_of_periods,
1872: x_return_status => l_return_status,
1873: x_msg_data => l_msg_data);
1874: -- get current period info
1875: --hr_utility.trace('executed get_prj_Period_profile_dtls');
1876: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
1877: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1878: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
1879: p_project_id => l_project_id
1872: x_return_status => l_return_status,
1873: x_msg_data => l_msg_data);
1874: -- get current period info
1875: --hr_utility.trace('executed get_prj_Period_profile_dtls');
1876: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
1877: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1878: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
1879: p_project_id => l_project_id
1880: ,p_period_type => l_plan_period_type
1873: x_msg_data => l_msg_data);
1874: -- get current period info
1875: --hr_utility.trace('executed get_prj_Period_profile_dtls');
1876: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
1877: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1878: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
1879: p_project_id => l_project_id
1880: ,p_period_type => l_plan_period_type
1881: ,p_period_profile_type => 'FINANCIAL_PLANNING'
1884: ,x_end_period => l_cur_end_period
1885: ,x_return_status => l_return_status
1886: ,x_msg_count => l_msg_count
1887: ,x_msg_data => l_msg_data);
1888: --hr_utility.trace('executed get_curr_period_profile_info');
1889: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1890: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1891: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1892: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1885: ,x_return_status => l_return_status
1886: ,x_msg_count => l_msg_count
1887: ,x_msg_data => l_msg_data);
1888: --hr_utility.trace('executed get_curr_period_profile_info');
1889: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1890: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1891: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1892: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1893:
1886: ,x_msg_count => l_msg_count
1887: ,x_msg_data => l_msg_data);
1888: --hr_utility.trace('executed get_curr_period_profile_info');
1889: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1890: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1891: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1892: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1893:
1894: if l_period_profile_id = l_cur_period_profile_id then
1887: ,x_msg_data => l_msg_data);
1888: --hr_utility.trace('executed get_curr_period_profile_info');
1889: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1890: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1891: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1892: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1893:
1894: if l_period_profile_id = l_cur_period_profile_id then
1895: x_old_pd_profile_flag := 'N'; -- OUTPUT: x_old_pd_profile_flag
1888: --hr_utility.trace('executed get_curr_period_profile_info');
1889: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1890: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1891: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1892: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1893:
1894: if l_period_profile_id = l_cur_period_profile_id then
1895: x_old_pd_profile_flag := 'N'; -- OUTPUT: x_old_pd_profile_flag
1896: else
1938: p_period_type => ll_plan_period_type,
1939: x_return_status => l_return_status,
1940: x_msg_count => l_msg_count,
1941: x_msg_data => l_msg_data);
1942: --hr_utility.trace('END OF SETTING PERIOD INFORMATION: 400');
1943:
1944: end if; -- check p_amt_or_pd flag
1945: -- END OF SETTING PERIOD INFORMATION --
1946:
1957: -- G_FP_REV_VERSION_NUMBER
1958: -- G_FP_ALL_VERSION_ID (may be set already, if user-entered)
1959: -- G_FP_ALL_VERSION_NAME
1960: -- G_FP_ALL_VERSION_NUMBER
1961: --hr_utility.trace('calling view_plan_temp_tables');
1962: -- delete residual data in the temp tables
1963: DELETE from PA_FIN_VP_AMTS_VIEW_TMP;
1964: DELETE from PA_FIN_VP_PDS_VIEW_TMP;
1965:
2057:
2058: -- figure out LOCKED BY after version sources set; also get RECORD VERSION NUMBERS
2059: -- COST VERSION
2060: if pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null then
2061: --hr_utility.trace('figuring out cost LOCKED BY');
2062: select record_version_number, -- OUTPUT: x_cost_rv_number
2063: budget_status_code -- OUTPUT: x_budget_status_code
2064: into x_cost_rv_number,
2065: x_budget_status_code
2085: end if; -- END:COST VERSION
2086:
2087: -- REVENUE VERSION
2088: if pa_fp_view_plans_pub.G_FP_REV_VERSION_ID is not null then
2089: --hr_utility.trace('figuring out revenue LOCKED BY');
2090: select record_version_number, -- OUTPUT: x_rev_rv_number
2091: budget_status_code -- OUTPUT: x_budget_status_code
2092: into x_rev_rv_number,
2093: x_budget_status_code
2112: end if; -- is_rev_locked_by_user
2113: end if; -- END:REVENUE VERSION
2114:
2115:
2116: --hr_utility.trace('after calling locked version stuff');
2117: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2118: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2119: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2113: end if; -- END:REVENUE VERSION
2114:
2115:
2116: --hr_utility.trace('after calling locked version stuff');
2117: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2118: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2119: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2114:
2115:
2116: --hr_utility.trace('after calling locked version stuff');
2117: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2118: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2119: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2115:
2116: --hr_utility.trace('after calling locked version stuff');
2117: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2118: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2119: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2123: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2116: --hr_utility.trace('after calling locked version stuff');
2117: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2118: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2119: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2123: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2124: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2117: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2118: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2119: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2123: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2124: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2125: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2118: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2119: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2123: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2124: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2125: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2126: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2119: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2123: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2124: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2125: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2126: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2127: */
2120: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2123: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2124: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2125: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2126: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2127: */
2128:
2121: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2123: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2124: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2125: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2126: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2127: */
2128:
2129: EXCEPTION
2122: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2123: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2124: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2125: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2126: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2127: */
2128:
2129: EXCEPTION
2130: WHEN OTHERS THEN
2229: FETCH C1 INTO v_start_date_tab(i),v_end_date_tab(i);
2230:
2231: -- dbms_output.put_line('i: '||i);
2232: i := i+1;
2233: hr_utility.trace('ok so far');
2234: EXIT WHEN C1%NOTFOUND;
2235:
2236: END LOOP;
2237:
2373: pa_fp_view_plans_pub.G_FP_PLAN_END_DATE
2374: order by start_date desc;
2375:
2376: BEGIN
2377: --hr_utility.trace_on(null, 'dlai');
2378: l_rownum := p_num_of_periods;
2379: ll_start_date := null;
2380: l_start_date := pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1;
2381:
2426: OPEN C_backward(l_start_date);
2427:
2428: LOOP
2429: FETCH C_backward INTO ll_start_date;
2430: --hr_utility.trace('current ll_startdate= ' || to_char(ll_start_date));
2431: EXIT WHEN C_backward%NOTFOUND;
2432: EXIT WHEN C_backward%ROWCOUNT = l_rownum;
2433:
2434: END LOOP;
2440: x_start_date := l_start_date;
2441: else
2442: x_start_date := to_char(ll_start_date);
2443: end if;
2444: --hr_utility.trace('x_start_date= ' || x_start_date);
2445: END pa_fp_set_periods_nav;
2446:
2447:
2448: FUNCTION Get_Version_ID return NUMBER is
2848: l_msg_data VARCHAR2(80);
2849:
2850: begin
2851: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '100: entered procedure', 2);
2852: --hr_utility.trace_on(null, 'dlai');
2853: --hr_utility.trace('entered view_plan_temp_tables');
2854: --hr_utility.trace('p_budget_version_id= ' || TO_CHAR(p_budget_version_id));
2855: x_msg_count := 0;
2856: x_return_status := FND_API.G_RET_STS_SUCCESS;
2849:
2850: begin
2851: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '100: entered procedure', 2);
2852: --hr_utility.trace_on(null, 'dlai');
2853: --hr_utility.trace('entered view_plan_temp_tables');
2854: --hr_utility.trace('p_budget_version_id= ' || TO_CHAR(p_budget_version_id));
2855: x_msg_count := 0;
2856: x_return_status := FND_API.G_RET_STS_SUCCESS;
2857: SAVEPOINT VIEW_PLAN_TEMP_TABLES;
2850: begin
2851: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '100: entered procedure', 2);
2852: --hr_utility.trace_on(null, 'dlai');
2853: --hr_utility.trace('entered view_plan_temp_tables');
2854: --hr_utility.trace('p_budget_version_id= ' || TO_CHAR(p_budget_version_id));
2855: x_msg_count := 0;
2856: x_return_status := FND_API.G_RET_STS_SUCCESS;
2857: SAVEPOINT VIEW_PLAN_TEMP_TABLES;
2858: l_diff_pd_profile_flag := 'N';
2859:
2860: -- FOR ADVANCED DISPLAY OPTIONS CUSTOMIZATION: USER-SELECTED BUDGET VERSIONS
2861: -- order of checking: ALL, COST, REVENUE
2862: if p_user_bv_flag = 'Y' then
2863: --hr_utility.trace('p_user_bv_flag = Y');
2864: -- assume that the BV_ID variables have already been set
2865: if pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID is not null then
2866: l_primary_bvid := pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID;
2867: elsif pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null then
2868: l_primary_bvid := pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
2869: else
2870: l_primary_bvid := pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2871: end if;
2872: --hr_utility.trace('allversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID));
2873: --hr_utility.trace('COSTversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_COST_VERSION_ID));
2874: --hr_utility.trace('REVversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_REV_VERSION_ID));
2875:
2876: --hr_utility.trace('selecting from pa_budget_versions');
2869: else
2870: l_primary_bvid := pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2871: end if;
2872: --hr_utility.trace('allversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID));
2873: --hr_utility.trace('COSTversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_COST_VERSION_ID));
2874: --hr_utility.trace('REVversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_REV_VERSION_ID));
2875:
2876: --hr_utility.trace('selecting from pa_budget_versions');
2877: select DECODE(budget_status_code,
2870: l_primary_bvid := pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2871: end if;
2872: --hr_utility.trace('allversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID));
2873: --hr_utility.trace('COSTversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_COST_VERSION_ID));
2874: --hr_utility.trace('REVversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_REV_VERSION_ID));
2875:
2876: --hr_utility.trace('selecting from pa_budget_versions');
2877: select DECODE(budget_status_code,
2878: 'B', 'B',
2872: --hr_utility.trace('allversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID));
2873: --hr_utility.trace('COSTversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_COST_VERSION_ID));
2874: --hr_utility.trace('REVversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_REV_VERSION_ID));
2875:
2876: --hr_utility.trace('selecting from pa_budget_versions');
2877: select DECODE(budget_status_code,
2878: 'B', 'B',
2879: 'W'),
2880: fin_plan_type_id
2891: project_id = p_project_id;
2892:
2893: -- figure out display_from:
2894: if l_fp_preference_code = 'COST_AND_REV_SAME' then
2895: --hr_utility.trace('user_specified: COST_AND_REV_SAME');
2896: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'ANY';
2897: if pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID is null then
2898: if pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null then
2899: pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID := pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
2918: x_msg_count => l_msg_count,
2919: x_msg_data => l_msg_data);
2920:
2921: elsif l_fp_preference_code = 'COST_ONLY' then
2922: --hr_utility.trace('user_specified: COST_ONLY');
2923: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
2924: select version_number,
2925: version_name
2926: into l_cost_version_number,
2939: x_msg_count => l_msg_count,
2940: x_msg_data => l_msg_data);
2941:
2942: elsif l_fp_preference_code = 'REVENUE_ONLY' then
2943: --hr_utility.trace('user_specified: REVENUE_ONLY');
2944: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
2945: select version_number,
2946: version_name
2947: into l_rev_version_number,
2960: x_msg_count => l_msg_count,
2961: x_msg_data => l_msg_data);
2962:
2963: else
2964: --hr_utility.trace('user_specified: COST_AND_REV_SEP');
2965: if (pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null) and
2966: (pa_fp_view_plans_pub.G_FP_REV_VERSION_ID is not null) then
2967:
2968: -- BEFORE MERGING, MAKE SURE PERIOD PROFILES MATCH
2973: select period_profile_id
2974: into l_period_profile_id2
2975: from pa_budget_versions
2976: where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2977: --hr_utility.trace('retrieved the period profile ids');
2978: if (pa_fp_view_plans_util.check_compatible_pd_profiles
2979: (p_period_profile_id1 => l_period_profile_id1,
2980: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
2981: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
2978: if (pa_fp_view_plans_util.check_compatible_pd_profiles
2979: (p_period_profile_id1 => l_period_profile_id1,
2980: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
2981: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
2982: --hr_utility.trace('user_specified: period_profile matches');
2983: -- PERIOD PROFILES MATCH: GO AHEAD WITH MERGING
2984: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
2985: select version_number,
2986: version_name
2997: from pa_budget_versions
2998: where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2999: pa_fp_view_plans_pub.G_FP_REV_VERSION_NUMBER := l_rev_version_number;
3000: pa_fp_view_plans_pub.G_FP_REV_VERSION_NAME := l_rev_version_name;
3001: --hr_utility.trace('entering pa_fp_vp_pop_tables_separate');
3002: pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate
3003: (p_project_id => p_project_id,
3004: p_cost_budget_version_id => pa_fp_view_plans_pub.G_FP_COST_VERSION_ID,
3005: p_rev_budget_version_id => pa_fp_view_plans_pub.G_FP_REV_VERSION_ID,
3005: p_rev_budget_version_id => pa_fp_view_plans_pub.G_FP_REV_VERSION_ID,
3006: x_return_status => l_return_status,
3007: x_msg_count => l_msg_count,
3008: x_msg_data => l_msg_data);
3009: --hr_utility.trace('exiting pa_fp_vp_pop_tables_separate');
3010: else
3011: -- PERIOD PROFILES DO NOT MATCH: ERROR MESSAGE
3012: --hr_utility.trace('user-defined bvs: merge failed because mismatch pd profiles');
3013: l_diff_pd_profile_flag := 'Y';
3008: x_msg_data => l_msg_data);
3009: --hr_utility.trace('exiting pa_fp_vp_pop_tables_separate');
3010: else
3011: -- PERIOD PROFILES DO NOT MATCH: ERROR MESSAGE
3012: --hr_utility.trace('user-defined bvs: merge failed because mismatch pd profiles');
3013: l_diff_pd_profile_flag := 'Y';
3014: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3015: select version_number,
3016: version_name
3081: end if; -- l_fp_preference_code
3082:
3083: -- USER DID NOT SPECIFY ANY BUDGET VERSIONS; PROCEED AS NORMAL
3084: else
3085: --hr_utility.trace('USER DID NOT SPECIFY ANY BUDGET VERSIONS: 1000');
3086:
3087: select DECODE(budget_status_code,
3088: 'B', 'B',
3089: 'W'),
3103: -- if the preference code is 'COST_AND_REV_SAME', then we only need this one version
3104: -- we blindly insert all rows for the budget version into the temp table
3105: if l_fp_preference_code = 'COST_AND_REV_SAME' then
3106: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '200: pref = COST_AND_REV_SAME', 1);
3107: --hr_utility.trace('COST_AND_REV_SAME');
3108: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'ANY';
3109: select version_name,
3110: version_number
3111: into l_cost_version_name,
3127: -- if the preference code is 'COST_ONLY', then we have the bvID of the COST version
3128: -- and we don't have to do anything else
3129: elsif l_fp_preference_code = 'COST_ONLY' then
3130: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '300: pref=COST_ONLY', 1);
3131: --hr_utility.trace('COST_ONLY');
3132: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3133: select version_number,
3134: version_name
3135: into l_cost_version_number,
3150: x_msg_data => l_msg_data);
3151:
3152: elsif l_fp_preference_code = 'REVENUE_ONLY' then
3153: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '400: pref=REVENUE_ONLY', 1);
3154: --hr_utility.trace('REVENUE_ONLY');
3155: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3156: select version_number,
3157: version_name
3158: into l_rev_version_number,
3178: -- have to inspect bv.version_type (which can be 'COST', 'REVENUE', etc)
3179: else
3180: -- find the appropriate current working version
3181: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '400: pref=COST_AND_REV_SEP', 1);
3182: --hr_utility.trace('COST_AND_REV_SEP');
3183: if l_working_or_baselined = 'W' then
3184: -- determine whether we have the COST or REVENUE version, and look for the other one
3185: if p_cost_or_revenue = 'C' then
3186: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '500: pref=COST_AND_REV_SEP, WORKING, COST', 2);
3183: if l_working_or_baselined = 'W' then
3184: -- determine whether we have the COST or REVENUE version, and look for the other one
3185: if p_cost_or_revenue = 'C' then
3186: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '500: pref=COST_AND_REV_SEP, WORKING, COST', 2);
3187: --hr_utility.trace('we have a working cost version');
3188: -- we have the COST version, so look for REVENUE version with 'COST_AND_REV_SEP'
3189: open l_compl_crsep_c_w_csr;
3190: fetch l_compl_crsep_c_w_csr into l_compl_crsep_c_w_rec;
3191: if l_compl_crsep_c_w_csr%NOTFOUND then
3198: close l_compl_crsep_c_w_csr;
3199: if l_compl_budget_version_id = -99 then
3200: -- did not find a compl for the COST version; just process COST version
3201: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '600: pref=COST_AND_REV_SEP, WORKING, COST - did not find complement', 1);
3202: --hr_utility.trace('could NOT find complement for working cost version');
3203: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3204: select version_number,
3205: version_name
3206: into l_cost_version_number,
3234: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
3235: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
3236: --merge the COST and REVENUE versions
3237: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '700: pref=COST_AND_REV_SEP, WORKING, COST - found complement,and passed period profile check', 1);
3238: --hr_utility.trace('found complement for working cost version AND period profile match');
3239: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3240: select version_number,
3241: version_name
3242: into l_cost_version_number,
3264: x_msg_data => l_msg_data);
3265: else
3266: -- the period profiles didn't match: only process one of the versions
3267: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '800: pref=COST_AND_REV_SEP, WORKING, COST - found complement,BUT did not pass period profile check', 1);
3268: --hr_utility.trace('found complement for working cost version BUT no per profile match');
3269: l_diff_pd_profile_flag := 'Y';
3270:
3271: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3272: select version_number,
3313: end if; -- l_compl_budget_version_id is null
3314: else
3315: -- we have the REVENUE version, so look for COST version with 'COST_AND_REV_SEP'
3316: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '900: pref=COST_AND_REV_SEP, WORKING, REVENUE', 1);
3317: --hr_utility.trace('we have a working revenue version');
3318: open l_compl_crsep_r_w_csr;
3319: fetch l_compl_crsep_r_w_csr into l_compl_crsep_r_w_rec;
3320: if l_compl_crsep_r_w_csr%NOTFOUND then
3321: l_compl_budget_version_id := -99;
3327: close l_compl_crsep_r_w_csr;
3328: if l_compl_budget_version_id = -99 then
3329: -- did not find a compl for the REVENUE version; just process REVENUE version
3330: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1000: pref=COST_AND_REV_SEP, WORKING, REVENUE - did not find complement', 1);
3331: --hr_utility.trace('could not find complement for working revenue version');
3332: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3333: select version_number,
3334: version_name
3335: into l_rev_version_number,
3363: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
3364: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
3365: --merge the COST and REVENUE versions
3366: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1100: pref=COST_AND_REV_SEP, WORKING, REVENUE - found complement, and passed period profiles test', 1);
3367: --hr_utility.trace('found compl for working revenue version AND period profile match');
3368: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3369: select version_number,
3370: version_name
3371: into l_rev_version_number,
3393: x_msg_data => l_msg_data);
3394: else
3395: -- the period profiles didn't match: only process one of the versions
3396: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1200: pref=COST_AND_REV_SEP, WORKING, REVENUE - found complement, BUT did not pass period profiles test', 1);
3397: --hr_utility.trace('found compl for working revenue version BUT no per profile match');
3398: l_diff_pd_profile_flag := 'Y';
3399: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3400: select version_number,
3401: version_name
3445: -- determine whether we have the COST or REVENUE version, and look for the other one
3446: if p_cost_or_revenue = 'C' then
3447: -- we have the COST version, so look for REVENUE version with 'COST_AND_REV_SEP'
3448: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1300: pref=COST_AND_REV_SEP, BASELINED, COST', 1);
3449: --hr_utility.trace('we have a baselined cost version');
3450: open l_compl_crsep_c_b_csr;
3451: fetch l_compl_crsep_c_b_csr into l_compl_crsep_c_b_rec;
3452: if l_compl_crsep_c_b_csr%NOTFOUND then
3453: l_compl_budget_version_id := -99;
3459: close l_compl_crsep_c_b_csr;
3460: if l_compl_budget_version_id = -99 then
3461: -- did not find a compl for the COST version; just process COST version
3462: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1400: pref=COST_AND_REV_SEP, BASELINED, COST - did not find complement', 1);
3463: --hr_utility.trace('could not find complement for baselined cost version');
3464: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3465: select version_number,
3466: version_name
3467: into l_cost_version_number,
3495: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
3496: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
3497: --merge the COST and REVENUE versions
3498: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1500: pref=COST_AND_REV_SEP, BASELINED, COST - found complement, and passed period profiles test', 1);
3499: --hr_utility.trace('found compl for baselined cost AND period profile match');
3500: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3501: select version_number,
3502: version_name
3503: into l_cost_version_number,
3525: x_msg_data => l_msg_data);
3526: else
3527: -- period_profiles don't match; process only the COST version
3528: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1600: pref=COST_AND_REV_SEP, BASELINED, COST - found complement, BUT did not pass period profiles test', 1);
3529: --hr_utility.trace('found compl for baselined cost BUT no period profile match');
3530: l_diff_pd_profile_flag := 'Y';
3531: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3532: select version_number,
3533: version_name
3573:
3574: else
3575: -- we have the REVENUE version, so look for COST version with 'COST_AND_REV_SEP'
3576: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1700: pref=COST_AND_REV_SEP, BASELINED, REVENUE', 1);
3577: --hr_utility.trace('we have a baselined revenue version');
3578: open l_compl_crsep_r_b_csr;
3579: fetch l_compl_crsep_r_b_csr into l_compl_crsep_r_b_rec;
3580: if l_compl_crsep_r_b_csr%NOTFOUND then
3581: l_compl_budget_version_id := -99;
3587: close l_compl_crsep_r_b_csr;
3588: if l_compl_budget_version_id = -99 then
3589: -- did not find a compl for the REVENUE version; just process REVENUE version
3590: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1800: pref=COST_AND_REV_SEP, BASELINED, REVENUE - did not find complement', 1);
3591: --hr_utility.trace('we have a baselined revenue version');
3592: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3593: select version_number,
3594: version_name
3595: into l_rev_version_number,
3623: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
3624: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
3625: --merge the COST and REVENUE versions
3626: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1900: pref=COST_AND_REV_SEP, BASELINED, REVENUE - found complement, and passed period profiles test', 1);
3627: --hr_utility.trace('found compl for baselined revenue version AND period profile match');
3628: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3629: select version_number,
3630: version_name
3631: into l_rev_version_number,
3653: x_msg_data => l_msg_data);
3654: else
3655: -- period profiles not compatible; just process REVENUE version
3656: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '2000: pref=COST_AND_REV_SEP, BASELINED, REVENUE - found complement, BUT did not pass period profiles test', 1);
3657: --hr_utility.trace('found compl for baselined revenue version BUT no per profile match');
3658: l_diff_pd_profile_flag := 'Y';
3659: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3660: select version_number,
3661: version_name
3707: x_rev_version_name := l_rev_version_name;
3708:
3709: end if; -- p_user_bv_flag
3710: x_diff_pd_profile_flag := l_diff_pd_profile_flag;
3711: --hr_utility.trace('diff_pd_profile_flag= ' || x_diff_pd_profile_flag);
3712:
3713: EXCEPTION
3714: when others then
3715: --hr_utility.trace('UNHANDLED EXCEPTION');
3711: --hr_utility.trace('diff_pd_profile_flag= ' || x_diff_pd_profile_flag);
3712:
3713: EXCEPTION
3714: when others then
3715: --hr_utility.trace('UNHANDLED EXCEPTION');
3716: rollback to VIEW_PLAN_TEMP_TABLES;
3717: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3718: x_msg_count := 1;
3719: x_msg_data := SQLERRM;
3976:
3977: begin
3978: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '100: entering procedure', 2);
3979: l_err_stage := 100;
3980: --hr_utility.trace('entered pa_fp_vp_pop_tables_separate');
3981: x_return_status := FND_API.G_RET_STS_SUCCESS;
3982: x_msg_count := 0;
3983: SAVEPOINT VIEW_PLANS_POP_TABLES_SEP;
3984:
4408:
4409: -- Bug Fix for Bug#13907149 Ends
4410:
4411: -- now, iterate through all rows in the COST table,
4412: --hr_utility.trace('l_c_project_id.first = ' || TO_CHAR(l_c_project_id.first));
4413: for i in nvl(l_c_project_id.first,0)..nvl(l_c_project_id.last,-1) loop
4414: l_found_complement := false;
4415: -- look for a row in REVENUE table with same project-task-resource id combo
4416: for j in nvl(l_r_project_id.first,0)..nvl(l_r_project_id.last,-1) loop
4420: ((l_r_resource_list_member_id(j) = l_c_resource_list_member_id(i)) or
4421: (l_r_resource_list_member_id(j) = 0 and l_c_resource_list_member_id(i) = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id) or
4422: (l_r_resource_list_member_id(j) = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id and l_c_resource_list_member_id(i) = 0)) then
4423: -- a match has been found: add data from REVENUE PL/SQL to the COST PL/SQL table
4424: --hr_utility.trace('FOUND MATCH');
4425: --hr_utility.trace('cost res assid= ' || to_char(l_c_cost_res_assignment_id(i)));
4426: --hr_utility.trace('rev res assid= ' || to_char(l_r_revenue_res_assignment_id(j)));
4427: l_found_complement := true;
4428: l_c_revenue_budget_version_id(i) := l_r_revenue_budget_version_id(j);
4421: (l_r_resource_list_member_id(j) = 0 and l_c_resource_list_member_id(i) = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id) or
4422: (l_r_resource_list_member_id(j) = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id and l_c_resource_list_member_id(i) = 0)) then
4423: -- a match has been found: add data from REVENUE PL/SQL to the COST PL/SQL table
4424: --hr_utility.trace('FOUND MATCH');
4425: --hr_utility.trace('cost res assid= ' || to_char(l_c_cost_res_assignment_id(i)));
4426: --hr_utility.trace('rev res assid= ' || to_char(l_r_revenue_res_assignment_id(j)));
4427: l_found_complement := true;
4428: l_c_revenue_budget_version_id(i) := l_r_revenue_budget_version_id(j);
4429: l_c_revenue_res_assignment_id(i) := l_r_revenue_res_assignment_id(j);
4422: (l_r_resource_list_member_id(j) = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id and l_c_resource_list_member_id(i) = 0)) then
4423: -- a match has been found: add data from REVENUE PL/SQL to the COST PL/SQL table
4424: --hr_utility.trace('FOUND MATCH');
4425: --hr_utility.trace('cost res assid= ' || to_char(l_c_cost_res_assignment_id(i)));
4426: --hr_utility.trace('rev res assid= ' || to_char(l_r_revenue_res_assignment_id(j)));
4427: l_found_complement := true;
4428: l_c_revenue_budget_version_id(i) := l_r_revenue_budget_version_id(j);
4429: l_c_revenue_res_assignment_id(i) := l_r_revenue_res_assignment_id(j);
4430: l_c_revenue(i) := l_r_revenue(j);
4465:
4466: -- process the QUANTITY row based on the value of l_report_labor_hrs_from_code
4467: if l_report_labor_hrs_from_code = 'COST' then
4468: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
4469: --hr_utility.trace('adding QUANTITY row');
4470: l_row_number := l_row_number + 1; -- increment row counter
4471: l_pd_project_id(l_row_number) := l_c_project_id(i);
4472: l_pd_task_id(l_row_number) := l_c_task_id(i);
4473: l_pd_resource_list_member_id(l_row_number) := l_c_resource_list_member_id(i);
4500: p_resource_assignment_id => l_pd_cost_res_assignment_id(l_row_number),
4501: p_project_currency_type => l_currency_type,
4502: p_amount_type_id => 215,
4503: p_period_number => 1);
4504: --hr_utility.trace('VALUE is = ' || to_char(l_pd_period_1(l_row_number)));
4505: --hr_utility.trace('get_period_n_value for period 2');
4506: l_pd_period_2(l_row_number) := pa_fp_view_plans_util.get_period_n_value
4507: (p_period_profile_id => l_period_profile_id,
4508: p_budget_version_id => l_pd_cost_budget_version_id(l_row_number),
4501: p_project_currency_type => l_currency_type,
4502: p_amount_type_id => 215,
4503: p_period_number => 1);
4504: --hr_utility.trace('VALUE is = ' || to_char(l_pd_period_1(l_row_number)));
4505: --hr_utility.trace('get_period_n_value for period 2');
4506: l_pd_period_2(l_row_number) := pa_fp_view_plans_util.get_period_n_value
4507: (p_period_profile_id => l_period_profile_id,
4508: p_budget_version_id => l_pd_cost_budget_version_id(l_row_number),
4509: p_resource_assignment_id => l_pd_cost_res_assignment_id(l_row_number),
4605: end if; -- display_flag: QUANTITY
4606: end if; -- QUANTITY
4607:
4608: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST = 'Y' then
4609: --hr_utility.trace('adding BURDENED COST row');
4610: -- process the BURDENED_COST row from the COST version
4611: l_row_number := l_row_number + 1; -- increment row counter
4612: -- 02/14/03 dlai: use appropriate cost row based on G_FP_CALC_MARGIN_FROM
4613: if pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM = 'B' then
4651: p_project_currency_type => l_currency_type,
4652: --p_amount_type_id => 150,
4653: p_amount_type_id => 165,
4654: p_period_number => 1);
4655: --hr_utility.trace('VALUE is = ' || to_char(l_pd_period_1(l_row_number)));
4656: l_pd_period_2(l_row_number) := pa_fp_view_plans_util.get_period_n_value
4657: (p_period_profile_id => l_period_profile_id,
4658: p_budget_version_id => l_pd_cost_budget_version_id(l_row_number),
4659: p_resource_assignment_id => l_pd_cost_res_assignment_id(l_row_number),
4768: l_pd_project_total(l_row_number) := l_c_burdened_cost(i); -- bug 2699651
4769: end if; -- display_flag: BURDENED_COST
4770:
4771: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST = 'Y' then
4772: --hr_utility.trace('adding RAW COST row');
4773: -- process the RAW_COST row from the COST version
4774: l_row_number := l_row_number + 1; -- increment row counter
4775: -- 02/14/03 dlai: use appropriate cost row based on G_FP_CALC_MARGIN_FROM
4776: if pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM <> 'B' then
5079: end if; --display_flag: QUANTITY
5080: end if; -- QUANTITY
5081:
5082: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE = 'Y' then
5083: --hr_utility.trace('adding REVENUE row');
5084: -- process the REVENUE row for this REVENUE version
5085: l_row_number := l_row_number + 1; -- increment row counter
5086: l_revenue_row_number := l_row_number; -- used when calculating margin, margin_percent
5087: l_pd_project_id(l_row_number) := l_r_project_id(j);
5218: l_pd_project_total(l_row_number) := l_r_revenue(j); -- bug 2699651
5219: end if; -- display_flag: REVENUE
5220:
5221: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN = 'Y' then
5222: --hr_utility.trace('adding MARGIN row');
5223: -- finally, insert the MARGIN and MARGIN_PERCENT rows
5224: -- MARGIN row
5225: l_row_number := l_row_number + 1; -- increment row counter
5226: l_pd_project_id(l_row_number) := l_c_project_id(i);
5268: l_pd_project_total(l_row_number) := l_pd_project_total(l_revenue_row_number)-l_pd_project_total(l_cost_row_number); -- bug 2699651
5269: end if; -- display_flag: MARGIN
5270:
5271: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT = 'Y' then
5272: --hr_utility.trace('adding MARGIN PERCENT row');
5273: -- MARGIN_PERCENT
5274: l_row_number := l_row_number + 1; -- increment row counter
5275: l_pd_project_id(l_row_number) := l_c_project_id(i);
5276: l_pd_task_id(l_row_number) := l_c_task_id(i);
6632: 'Y'); -- PERFORMANCE LIABILITY: FOR UNMARRIED REVENUE ROWS, WE SAY ALWAYS HAVE CHILD
6633: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD= 'A'
6634: l_err_stage := 900;
6635: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '700: transfer from rev pl/sql table to amts_tmp table', 1);
6636: --hr_utility.trace('total rows is ' || to_char(nvl(l_c_project_id.last,0) + nvl(l_r_project_id.last,0)));
6637: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
6638: -- PERIODS PL/SQL table
6639: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6640: --hr_utility.trace('inserting into pa_fin_vp_pds_view_tmp for POP_TEM_TABLES_SEP');
6636: --hr_utility.trace('total rows is ' || to_char(nvl(l_c_project_id.last,0) + nvl(l_r_project_id.last,0)));
6637: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
6638: -- PERIODS PL/SQL table
6639: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6640: --hr_utility.trace('inserting into pa_fin_vp_pds_view_tmp for POP_TEM_TABLES_SEP');
6641: --hr_utility.trace('l_pd_project_id.last= ' || to_char(l_pd_project_id.last));
6642: forall z in nvl(l_pd_project_id.first,0)..nvl(l_pd_project_id.last,-1)
6643: insert into pa_fin_vp_pds_view_tmp
6644: (project_id,
6637: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
6638: -- PERIODS PL/SQL table
6639: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6640: --hr_utility.trace('inserting into pa_fin_vp_pds_view_tmp for POP_TEM_TABLES_SEP');
6641: --hr_utility.trace('l_pd_project_id.last= ' || to_char(l_pd_project_id.last));
6642: forall z in nvl(l_pd_project_id.first,0)..nvl(l_pd_project_id.last,-1)
6643: insert into pa_fin_vp_pds_view_tmp
6644: (project_id,
6645: task_id,
6818: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '800: leaving procedure', 2);
6819: EXCEPTION
6820: when others then
6821: rollback to VIEW_PLANS_POP_TABLES_SEP;
6822: --hr_utility.trace('error stage= ' || to_char(l_err_stage));
6823: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6824: x_msg_count := 1;
6825: x_msg_data := SQLERRM;
6826: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
7051: t_period_amount12 NUMBER;
7052: t_period_amount13 NUMBER;
7053:
7054: begin
7055: --hr_utility.trace_on(null, 'dlai');
7056: l_err_stage := 100;
7057: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '100: entering pa_fp_vp_pop_tables_together', 2);
7058: --hr_utility.trace('entered pa_fp_vp_pop_tables_together: 100');
7059: x_return_status := FND_API.G_RET_STS_SUCCESS;
7054: begin
7055: --hr_utility.trace_on(null, 'dlai');
7056: l_err_stage := 100;
7057: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '100: entering pa_fp_vp_pop_tables_together', 2);
7058: --hr_utility.trace('entered pa_fp_vp_pop_tables_together: 100');
7059: x_return_status := FND_API.G_RET_STS_SUCCESS;
7060: x_msg_count := 0;
7061: SAVEPOINT VIEW_PLANS_POP_TABLES_SAME;
7062:
7087: po.project_id = p_project_id and
7088: po.fin_plan_option_level_code = 'PLAN_TYPE';
7089: l_err_stage := 200;
7090: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '200: retrieved periodprofileid= ' || l_period_profile_id, 1);
7091: --hr_utility.trace('retrieved period profile id: 200');
7092: -- populate the AMOUNTS PL/SQL table
7093:
7094: /* Modified below code for bug 7514054 */
7095:
7302: end if;
7303: /* Ends added for bug 7514054 */
7304: l_err_stage := 300;
7305: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '300: bulk collected into amts pl/sql table', 1);
7306: --hr_utility.trace('bulk collected into amts pl/sql table: 300');
7307: --hr_utility.trace('number of rows = ' || TO_CHAR(l_project_id.last));
7308:
7309: -- populate the PERIODS PL/SQL table
7310: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
7303: /* Ends added for bug 7514054 */
7304: l_err_stage := 300;
7305: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '300: bulk collected into amts pl/sql table', 1);
7306: --hr_utility.trace('bulk collected into amts pl/sql table: 300');
7307: --hr_utility.trace('number of rows = ' || TO_CHAR(l_project_id.last));
7308:
7309: -- populate the PERIODS PL/SQL table
7310: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
7311: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7308:
7309: -- populate the PERIODS PL/SQL table
7310: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
7311: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7312: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
7313: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
7314: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
7315: --hr_utility.trace('i= ' || to_char(i));
7316: --hr_utility.trace('loop: ' || to_char(i));
7309: -- populate the PERIODS PL/SQL table
7310: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
7311: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7312: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
7313: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
7314: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
7315: --hr_utility.trace('i= ' || to_char(i));
7316: --hr_utility.trace('loop: ' || to_char(i));
7317: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
7311: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7312: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
7313: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
7314: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
7315: --hr_utility.trace('i= ' || to_char(i));
7316: --hr_utility.trace('loop: ' || to_char(i));
7317: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
7318: l_row_number := l_row_number + 1;
7319: -- process the QUANTITY numbers
7312: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
7313: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
7314: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
7315: --hr_utility.trace('i= ' || to_char(i));
7316: --hr_utility.trace('loop: ' || to_char(i));
7317: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
7318: l_row_number := l_row_number + 1;
7319: -- process the QUANTITY numbers
7320: l_pd_project_id(l_row_number) := l_project_id(i);
7340: l_pd_has_child_element(l_row_number) := 'N';
7341: else
7342: l_pd_has_child_element(l_row_number) := 'Y';
7343: end if;
7344: --hr_utility.trace('quantity: period1');
7345: --hr_utility.trace('p_period_profile_id= ' || to_char(l_period_profile_id));
7346: --hr_utility.trace('p_resource_assignment_id= ' || to_char(l_pd_res_assignment_id(l_row_number)));
7347: --hr_utility.trace('p_project_currency_type= ' || l_currency_type);
7348: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7341: else
7342: l_pd_has_child_element(l_row_number) := 'Y';
7343: end if;
7344: --hr_utility.trace('quantity: period1');
7345: --hr_utility.trace('p_period_profile_id= ' || to_char(l_period_profile_id));
7346: --hr_utility.trace('p_resource_assignment_id= ' || to_char(l_pd_res_assignment_id(l_row_number)));
7347: --hr_utility.trace('p_project_currency_type= ' || l_currency_type);
7348: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7349: (p_period_profile_id => l_period_profile_id,
7342: l_pd_has_child_element(l_row_number) := 'Y';
7343: end if;
7344: --hr_utility.trace('quantity: period1');
7345: --hr_utility.trace('p_period_profile_id= ' || to_char(l_period_profile_id));
7346: --hr_utility.trace('p_resource_assignment_id= ' || to_char(l_pd_res_assignment_id(l_row_number)));
7347: --hr_utility.trace('p_project_currency_type= ' || l_currency_type);
7348: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7349: (p_period_profile_id => l_period_profile_id,
7350: p_budget_version_id => l_pd_budget_version_id(l_row_number),
7343: end if;
7344: --hr_utility.trace('quantity: period1');
7345: --hr_utility.trace('p_period_profile_id= ' || to_char(l_period_profile_id));
7346: --hr_utility.trace('p_resource_assignment_id= ' || to_char(l_pd_res_assignment_id(l_row_number)));
7347: --hr_utility.trace('p_project_currency_type= ' || l_currency_type);
7348: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7349: (p_period_profile_id => l_period_profile_id,
7350: p_budget_version_id => l_pd_budget_version_id(l_row_number),
7351: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
7488: if l_burdened_cost(i) is not null then
7489: l_cost_row_number := l_row_number;
7490: end if;
7491: */
7492: --hr_utility.trace('burdened cost: period1');
7493: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7494: (p_period_profile_id => l_period_profile_id,
7495: p_budget_version_id => l_pd_budget_version_id(l_row_number),
7496: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
7648: if l_burdened_cost(i) is null then
7649: l_cost_row_number := l_row_number;
7650: end if;
7651: */
7652: --hr_utility.trace('raw cost: period1');
7653: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7654: (p_period_profile_id => l_period_profile_id,
7655: p_budget_version_id => l_pd_budget_version_id(l_row_number),
7656: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
7800: else
7801: l_pd_has_child_element(l_row_number) := 'Y';
7802: end if;
7803: l_revenue_row_number := l_row_number;
7804: --hr_utility.trace('revenue: period1');
7805: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7806: (p_period_profile_id => l_period_profile_id,
7807: p_budget_version_id => l_pd_budget_version_id(l_row_number),
7808: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
8002: end loop;
8003: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8004: l_err_stage := 400;
8005: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '400: populated the pds pl/sql tables', 1);
8006: --hr_utility.trace('populated the pds pl/sql tables: 400');
8007:
8008: /* TRANSFER DATA FROM PL/SQL TABLES TO GLOBAL TEMPORARY TABLES */
8009: -- POPULATE global temporary table PA_FIN_VP_AMTS_VIEW_TMP
8010: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'A'
8050: l_unit_of_measure(i),
8051: l_has_child_element(i));
8052: l_err_stage := 500;
8053: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '500: populated the amts_tmp tables', 1);
8054: --hr_utility.trace('populated the amts_tmp table: 500');
8055:
8056: -- Bug 8868407:10394217 : Rollup margin and margin_percent to tasks and project levels.
8057: update pa_fin_vp_amts_view_tmp
8058: set margin_percent = DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
8067: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
8068: -- PERIODS PL/SQL table
8069: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8070: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
8071: --hr_utility.trace('l_pd_project_id.first= ' || TO_CHAR(l_pd_project_id.first));
8072: --hr_utility.trace('l_pd_project_id.last= ' || TO_CHAR(l_pd_project_id.last));
8073: forall z in nvl(l_pd_project_id.first,0)..nvl(l_pd_project_id.last,-1)
8074: insert into pa_fin_vp_pds_view_tmp
8075: (project_id,
8068: -- PERIODS PL/SQL table
8069: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8070: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
8071: --hr_utility.trace('l_pd_project_id.first= ' || TO_CHAR(l_pd_project_id.first));
8072: --hr_utility.trace('l_pd_project_id.last= ' || TO_CHAR(l_pd_project_id.last));
8073: forall z in nvl(l_pd_project_id.first,0)..nvl(l_pd_project_id.last,-1)
8074: insert into pa_fin_vp_pds_view_tmp
8075: (project_id,
8076: task_id,
8245: /* Ends logic to rollup the period aounts to the parent levels */
8246:
8247: l_err_stage := 600;
8248: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '600: populated the pds_tmp tables', 1);
8249: --hr_utility.trace('populated the pds_temp table: 600');
8250: commit;
8251: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8252: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '700: leaving procedure', 2);
8253:
8253:
8254: EXCEPTION
8255: when others then
8256: rollback to VIEW_PLANS_POP_TABLES_SAME;
8257: --hr_utility.trace('l_err_stage= ' || to_char(l_err_stage));
8258: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8259: x_msg_count := 1;
8260: x_msg_data := SQLERRM;
8261: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
8525: t_period_amount12 NUMBER;
8526: t_period_amount13 NUMBER;
8527:
8528: begin
8529: --hr_utility.trace_on(null, 'dlai');
8530: l_err_stage := 100;
8531: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '100: entering procedure', 2);
8532: --hr_utility.trace('entered pa_fp_vp_pop_tables_single:100');
8533: x_return_status := FND_API.G_RET_STS_SUCCESS;
8528: begin
8529: --hr_utility.trace_on(null, 'dlai');
8530: l_err_stage := 100;
8531: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '100: entering procedure', 2);
8532: --hr_utility.trace('entered pa_fp_vp_pop_tables_single:100');
8533: x_return_status := FND_API.G_RET_STS_SUCCESS;
8534: x_msg_count := 0;
8535: SAVEPOINT VIEW_PLANS_POP_TABLES_SINGLE;
8536: -- used to query pa_proj_periods_denorm table
8560: po.project_id = p_project_id and
8561: po.fin_plan_option_level_code = 'PLAN_TYPE';
8562: l_err_stage := 200;
8563: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '200: retrieved periodprofileid= ' || l_period_profile_id, 1);
8564: --hr_utility.trace('retrieved periodprofileid: 200');
8565: -- populate AMOUNTS PL/SQL TABLES
8566: if p_cost_or_rev = 'C' then
8567: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '300: this is a COST version - populating amts pl/sql tables', 1);
8568: /* Modified below code for bug 7514054 */
8784: /* ---- populate the PERIODS PL/SQL table with the following rows ---- */
8785: /* ---- COST, REVENUE, MARGIN, MARGIN_PERCENT, QUANTITY ---- */
8786: /* ONLY IF pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' */
8787: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
8788: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
8789: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
8790: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
8791: --hr_utility.trace('i= ' || to_char(i));
8792: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
8785: /* ---- COST, REVENUE, MARGIN, MARGIN_PERCENT, QUANTITY ---- */
8786: /* ONLY IF pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' */
8787: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
8788: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
8789: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
8790: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
8791: --hr_utility.trace('i= ' || to_char(i));
8792: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
8793: -- process QUANTITY numbers based on report_labor_hrs_from_code
8787: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
8788: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
8789: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
8790: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
8791: --hr_utility.trace('i= ' || to_char(i));
8792: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
8793: -- process QUANTITY numbers based on report_labor_hrs_from_code
8794: l_row_number := l_row_number + 1; -- increment row counter
8795: l_pd_project_id(l_row_number) := l_project_id(i);
8816: else
8817: l_pd_has_child_element(l_row_number) := 'Y';
8818: end if;
8819: if (l_report_labor_hrs_from_code = 'COST') then
8820: --hr_utility.trace('try to getperiodnvalue');
8821: --hr_utility.trace('raid= ' || to_char(l_pd_res_assignment_id(l_row_number)));
8822: --hr_utility.trace('ppid= ' || to_char(l_period_profile_id));
8823: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
8824: (p_period_profile_id => l_period_profile_id,
8817: l_pd_has_child_element(l_row_number) := 'Y';
8818: end if;
8819: if (l_report_labor_hrs_from_code = 'COST') then
8820: --hr_utility.trace('try to getperiodnvalue');
8821: --hr_utility.trace('raid= ' || to_char(l_pd_res_assignment_id(l_row_number)));
8822: --hr_utility.trace('ppid= ' || to_char(l_period_profile_id));
8823: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
8824: (p_period_profile_id => l_period_profile_id,
8825: p_budget_version_id => l_pd_budget_version_id(l_row_number),
8818: end if;
8819: if (l_report_labor_hrs_from_code = 'COST') then
8820: --hr_utility.trace('try to getperiodnvalue');
8821: --hr_utility.trace('raid= ' || to_char(l_pd_res_assignment_id(l_row_number)));
8822: --hr_utility.trace('ppid= ' || to_char(l_period_profile_id));
8823: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
8824: (p_period_profile_id => l_period_profile_id,
8825: p_budget_version_id => l_pd_budget_version_id(l_row_number),
8826: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
8826: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
8827: p_project_currency_type => l_currency_type,
8828: p_amount_type_id => 215,
8829: p_period_number => 1);
8830: --hr_utility.trace('reached here');
8831: l_pd_period_2(l_row_number) := pa_fp_view_plans_util.get_period_n_value
8832: (p_period_profile_id => l_period_profile_id,
8833: p_budget_version_id => l_pd_budget_version_id(l_row_number),
8834: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
9621:
9622: elsif p_cost_or_rev = 'R' then
9623: l_err_stage := 700;
9624: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '500: this is a REVENUE version - populating amts pl/sql tables', 1);
9625: --hr_utility.trace('this is a REVENUE version: 500');
9626: /* Modified below code for 7514054 */
9627:
9628: open av_rev_csr;
9629: fetch av_rev_csr bulk collect into
9839: l_err_stage := 800;
9840: /* ---- populate the PERIODS PL/SQL table with the following rows ---- */
9841: /* ---- COST, REVENUE, MARGIN, MARGIN_PERCENT, QUANTITY ---- */
9842: /* ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' */
9843: --hr_utility.trace('number of rows in csr= ' || TO_CHAR(l_project_id.last));
9844: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
9845: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
9846:
9847: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
9844: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
9845: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
9846:
9847: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
9848: --hr_utility.trace('inserting row for Quantity: ' || TO_CHAR(i));
9849: -- process the QUANTITY numbers based on report_labor_hrs_from_code
9850: l_row_number := l_row_number + 1; -- increment row counter
9851: l_pd_project_id(l_row_number) := l_project_id(i);
9852: l_pd_task_id(l_row_number) := l_task_id(i);
10001: end if; -- QUANTITY
10002: end if; -- display_flag: QUANTITY
10003:
10004: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE = 'Y' then
10005: --hr_utility.trace('inserting row for Revenue: ' || TO_CHAR(i));
10006: -- process REVENUE numbers from REVENUE BUDGET VERSION
10007: l_row_number := l_row_number + 1; -- increment row counter
10008: l_pd_project_id(l_row_number) := l_project_id(i);
10009: l_pd_task_id(l_row_number) := l_task_id(i);
10137: l_pd_project_total(l_row_number) := l_revenue(i); -- bug 2699651
10138: end if; -- display_from: REVENUE
10139:
10140: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST = 'Y' then
10141: --hr_utility.trace('creating row for burdened cost: ' || TO_CHAR(i));
10142: -- POPULATE null'S for BURDENED_COST row in PERIODS PL/SQL TABLE
10143: l_row_number := l_row_number + 1; -- increment row counter
10144: l_pd_project_id(l_row_number) := l_project_id(i);
10145: l_pd_task_id(l_row_number) := l_task_id(i);
10184: l_pd_project_total(l_row_number) := null; -- bug 2699651
10185: end if; -- display_from: BURDENED COST
10186:
10187: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST = 'Y' then
10188: --hr_utility.trace('inserting row for RawCost: ' || TO_CHAR(i));
10189: -- POPULATE null'S for RAW_COST row in PERIODS PL/SQL TABLE
10190: l_row_number := l_row_number + 1; -- increment row counter
10191: l_pd_project_id(l_row_number) := l_project_id(i);
10192: l_pd_task_id(l_row_number) := l_task_id(i);
10231: l_pd_project_total(l_row_number) := null; -- bug 2699651
10232: end if; -- display_from: RAW COST
10233:
10234: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN = 'Y' then
10235: --hr_utility.trace('inserting row for Margin: ' || TO_CHAR(i));
10236: -- POPULATE null'S for MARGIN row in PERIODS PL/SQL TABLE
10237: l_row_number := l_row_number + 1; -- increment row counter
10238: l_pd_project_id(l_row_number) := l_project_id(i);
10239: l_pd_task_id(l_row_number) := l_task_id(i);
10278: l_pd_project_total(l_row_number) := null; -- bug 2699651
10279: end if; -- display_from: MARGIN
10280:
10281: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT = 'Y' then
10282: --hr_utility.trace('inserting row for MarginPct: ' || TO_CHAR(i));
10283: -- POPULATE null'S for MARGIN_PERCENT row in PERIODS PL/SQL TABLE
10284: l_row_number := l_row_number + 1; -- increment row counter
10285: l_pd_project_id(l_row_number) := l_project_id(i);
10286: l_pd_task_id(l_row_number) := l_task_id(i);
10327: end loop;
10328: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
10329: l_err_stage:= 900;
10330: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '600: REVENUE version - FINISHED populating amts pl/sql tables', 1);
10331: --hr_utility.trace('finished populating amts pl/sql tables: 600');
10332:
10333: -- POPULATE global temporary table PA_FIN_VP_AMTS_VIEW_TMP
10334: -- only if pa_fp_view_plans_pub.G_AMT_OR_PD = 'A'
10335: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'A' then
10374: l_unit_of_measure(i),
10375: l_has_child_element(i));
10376: l_err_stage:= 1000;
10377: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '800: amts temp table populated', 1);
10378: --hr_utility.trace('amts temp table populated: 800');
10379: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD= 'A'
10380:
10381: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP
10382: -- only if in periodic mode
10565:
10566:
10567: else
10568: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '700: INVALID VALUE FOR p_cost_or_rev', 4);
10569: --hr_utility.trace('invalid value for p_cost_or_rev');
10570: end if;
10571: commit;
10572: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '1000: exiting procedure', 2);
10573:
10573:
10574: EXCEPTION
10575: when others then
10576: rollback to VIEW_PLANS_POP_TABLES_SINGLE;
10577: --hr_utility.trace('l_err_stage= ' || to_char(l_err_stage));
10578: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10579: x_msg_count := 1;
10580: x_msg_data := SQLERRM;
10581: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',