[Home] [Help]
142: l_track_as_labor_flag pa_resources.track_as_labor_flag%TYPE;
143: l_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE;
144:
145: BEGIN
146: --hr_utility.trace_on(null, 'dlai');
147: --hr_utility.trace('STARTING');
148: x_return_status := FND_API.G_RET_STS_SUCCESS;
149: -- x_primary_pp_bv_id := p_orgfcst_version_id;
150: pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID := p_orgfcst_version_id;
143: l_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE;
144:
145: BEGIN
146: --hr_utility.trace_on(null, 'dlai');
147: --hr_utility.trace('STARTING');
148: x_return_status := FND_API.G_RET_STS_SUCCESS;
149: -- x_primary_pp_bv_id := p_orgfcst_version_id;
150: pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID := p_orgfcst_version_id;
151: pa_fp_view_plans_pub.G_AMT_OR_PD := p_amt_or_pd; -- this will be used by other procedures
160: ,x_msg_count => l_msg_count
161: ,x_msg_data => l_msg_data);
162: pa_fp_view_plans_pub.G_UNCAT_RLM_ID := l_uncat_rlm_id;
163: x_uncat_rlmid := l_uncat_rlm_id;
164: --hr_utility.trace('Uncategorized Resource List Member Id is ' || to_char(l_uncat_rlm_id));
165: --hr_utility.trace('Amt/Period toggle is ' || pa_fp_view_plans_pub.G_AMT_OR_PD);
166: select bv.project_id,
167: DECODE(p_view_currency_type,
168: 'PROJ', pa.project_currency_code,
161: ,x_msg_data => l_msg_data);
162: pa_fp_view_plans_pub.G_UNCAT_RLM_ID := l_uncat_rlm_id;
163: x_uncat_rlmid := l_uncat_rlm_id;
164: --hr_utility.trace('Uncategorized Resource List Member Id is ' || to_char(l_uncat_rlm_id));
165: --hr_utility.trace('Amt/Period toggle is ' || pa_fp_view_plans_pub.G_AMT_OR_PD);
166: select bv.project_id,
167: DECODE(p_view_currency_type,
168: 'PROJ', pa.project_currency_code,
169: 'PROJFUNC', pa.projfunc_currency_code,
211: fin_plan_type_id = l_fin_plan_type_id and
212: fin_plan_option_level_code='PLAN_TYPE';
213: EXCEPTION
214: WHEN NO_DATA_FOUND THEN
215: --hr_utility.trace('no_data_found 1');
216: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217: x_msg_count := 1;
218: x_msg_data := SQLERRM;
219: FND_MSG_PUB.add_exc_msg
219: FND_MSG_PUB.add_exc_msg
220: ( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
221: p_procedure_name => 'pa_fp_viewplan_hgrid_init:plan start/end dates: 100');
222: END;
223: --hr_utility.trace('stage 100 passed');
224: x_plan_type_id := l_fin_plan_type_id; -- OUTPUT: x_plan_type_id
225: pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM := l_margin_derived_from_code;
226: x_calc_margin_from := l_margin_derived_from_code; -- OUTPUT: x_calc_margin_from
227: pa_fp_view_plans_pub.G_FP_CALC_QUANTITY_FROM := l_labor_hours_from_code;
368: px_display_margin := l_display_margin;
369: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
370: px_display_marginpct := l_display_marginpct;
371: end if;
372: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
373: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
374: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
375: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
376: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
369: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
370: px_display_marginpct := l_display_marginpct;
371: end if;
372: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
373: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
374: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
375: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
376: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
377: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
370: px_display_marginpct := l_display_marginpct;
371: end if;
372: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
373: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
374: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
375: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
376: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
377: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
378:
371: end if;
372: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
373: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
374: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
375: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
376: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
377: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
378:
379: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
372: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
373: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
374: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
375: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
376: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
377: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
378:
379: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
380: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
373: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
374: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
375: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
376: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
377: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
378:
379: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
380: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
381: l_default_amount_type_code := 'QUANTITY';
433: pa_fp_view_plans_pub.G_DEFAULT_AMT_SUBTYPE_CODE := l_default_amount_subtype_code;
434: x_def_amt_subt_code := l_default_amount_subtype_code; -- OUTPUT: x_def_amt_subt_code
435: pa_fp_view_plans_pub.G_FP_CURRENCY_CODE := l_projfunc_currency_code;
436: pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE := p_view_currency_type;
437: --hr_utility.trace('reached end of user customizations section: 200');
438:
439:
440: -- SETTING PERIOD INFORMATION --
441: -- ONLY IF IN PERIODIC MODE --
460: FND_MSG_PUB.add_exc_msg
461: ( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
462: p_procedure_name => 'pa_fp_viewplan_hgrid_init:plan period type: 300');
463: END;
464: --hr_utility.trace('after selecting from pa_proj_period_profiles: 300 succeeded');
465:
466: -- get period info based on period_profile_id
467: Pa_Prj_Period_Profile_Utils.Get_Prj_Period_Profile_Dtls(
468: p_period_profile_id => l_period_profile_id,
477: x_number_of_periods => l_number_of_periods,
478: x_return_status => l_return_status,
479: x_msg_data => l_msg_data);
480: -- get current period info
481: --hr_utility.trace('executed get_prj_Period_profile_dtls');
482: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
483: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
484: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
485: p_project_id => l_project_id
478: x_return_status => l_return_status,
479: x_msg_data => l_msg_data);
480: -- get current period info
481: --hr_utility.trace('executed get_prj_Period_profile_dtls');
482: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
483: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
484: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
485: p_project_id => l_project_id
486: ,p_period_type => l_plan_period_type
479: x_msg_data => l_msg_data);
480: -- get current period info
481: --hr_utility.trace('executed get_prj_Period_profile_dtls');
482: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
483: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
484: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
485: p_project_id => l_project_id
486: ,p_period_type => l_plan_period_type
487: ,p_period_profile_type => 'FINANCIAL_PLANNING'
490: ,x_end_period => l_cur_end_period
491: ,x_return_status => l_return_status
492: ,x_msg_count => l_msg_count
493: ,x_msg_data => l_msg_data);
494: --hr_utility.trace('executed get_curr_period_profile_info');
495: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
496: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
497: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
498: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
491: ,x_return_status => l_return_status
492: ,x_msg_count => l_msg_count
493: ,x_msg_data => l_msg_data);
494: --hr_utility.trace('executed get_curr_period_profile_info');
495: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
496: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
497: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
498: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
499:
492: ,x_msg_count => l_msg_count
493: ,x_msg_data => l_msg_data);
494: --hr_utility.trace('executed get_curr_period_profile_info');
495: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
496: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
497: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
498: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
499:
500: if l_period_profile_id = l_cur_period_profile_id then
493: ,x_msg_data => l_msg_data);
494: --hr_utility.trace('executed get_curr_period_profile_info');
495: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
496: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
497: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
498: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
499:
500: if l_period_profile_id = l_cur_period_profile_id then
501: x_old_pd_profile_flag := 'N'; -- OUTPUT: x_old_pd_profile_flag
494: --hr_utility.trace('executed get_curr_period_profile_info');
495: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
496: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
497: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
498: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
499:
500: if l_period_profile_id = l_cur_period_profile_id then
501: x_old_pd_profile_flag := 'N'; -- OUTPUT: x_old_pd_profile_flag
502: else
587: x_return_status => l_return_status,
588: x_msg_count => l_msg_count,
589: x_msg_data => l_msg_data);
590:
591: --hr_utility.trace('END OF SETTING PERIOD INFORMATION: 400');
592:
593: end if; -- check p_amt_or_pd flag
594: -- END OF SETTING PERIOD INFORMATION --
595:
606: -- G_FP_REV_VERSION_NUMBER
607: -- G_FP_ALL_VERSION_ID (may be set already, if user-entered)
608: -- G_FP_ALL_VERSION_NAME
609: -- G_FP_ALL_VERSION_NUMBER
610: --hr_utility.trace('calling view_plan_temp_tables');
611: -- delete residual data in the temp tables
612: DELETE from PA_FIN_VP_AMTS_VIEW_TMP;
613: DELETE from PA_FIN_VP_PDS_VIEW_TMP;
614: pa_fp_view_plans_pub.view_plan_temp_tables
637: end if;
638: return;
639: end if;
640:
641: --hr_utility.trace('after calling view_plan_temp_tables: 500');
642: -- END OF POPULATING GLOBAL TEMPORARY TABLE --
643:
644: x_display_from := pa_fp_view_plans_pub.G_DISPLAY_FROM; -- OUTPUT: x_display_from
645: x_diff_pd_profile_flag := l_diff_pd_profile_flag; -- OUTPUT: x_diff_pd_profile_flag
851: -- figure out LOCKED BY after version sources set; also get RECORD VERSION NUMBERS
852: -- COST VERSION
853: if (pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null) and
854: (pa_fp_view_plans_pub.G_DISPLAY_FROM in ('COST', 'BOTH', 'ANY')) then
855: --hr_utility.trace('figuring out cost LOCKED BY');
856: select record_version_number, -- OUTPUT: x_cost_rv_number
857: budget_status_code -- OUTPUT: x_cost_budget_status_code
858: into x_cost_rv_number,
859: x_cost_budget_status_code
882:
883: -- REVENUE VERSION
884: if (pa_fp_view_plans_pub.G_FP_REV_VERSION_ID is not null) and
885: (pa_fp_view_plans_pub.G_DISPLAY_FROM in ('REVENUE', 'BOTH', 'ANY')) then
886: --hr_utility.trace('figuring out revenue LOCKED BY');
887: select record_version_number, -- OUTPUT: x_rev_rv_number
888: budget_status_code -- OUTPUT: x_rev_budget_status_code
889: into x_rev_rv_number,
890: x_rev_budget_status_code
911: end if; -- is_rev_locked_by_user
912: end if; -- END:REVENUE VERSION
913:
914:
915: --hr_utility.trace('after calling locked version stuff');
916: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
917: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
918: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
912: end if; -- END:REVENUE VERSION
913:
914:
915: --hr_utility.trace('after calling locked version stuff');
916: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
917: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
918: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
913:
914:
915: --hr_utility.trace('after calling locked version stuff');
916: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
917: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
918: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
921: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
914:
915: --hr_utility.trace('after calling locked version stuff');
916: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
917: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
918: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
921: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
922: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
915: --hr_utility.trace('after calling locked version stuff');
916: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
917: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
918: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
921: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
922: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
923: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
916: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
917: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
918: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
921: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
922: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
923: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
924: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
917: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
918: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
921: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
922: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
923: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
924: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
925:
918: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
921: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
922: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
923: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
924: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
925:
926: EXCEPTION
919: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
921: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
922: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
923: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
924: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
925:
926: EXCEPTION
927: WHEN OTHERS THEN
920: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
921: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
922: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
923: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
924: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
925:
926: EXCEPTION
927: WHEN OTHERS THEN
928: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1572: fin_plan_type_id = l_fin_plan_type_id and
1573: fin_plan_option_level_code='PLAN_TYPE';
1574: EXCEPTION
1575: WHEN NO_DATA_FOUND THEN
1576: --hr_utility.trace('no_data_found 1');
1577: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1578: x_msg_count := 1;
1579: x_msg_data := SQLERRM;
1580: FND_MSG_PUB.add_exc_msg
1580: FND_MSG_PUB.add_exc_msg
1581: ( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
1582: p_procedure_name => 'pa_fp_viewplan_hgrid_init:plan start/end dates: 100');
1583: END;
1584: --hr_utility.trace('stage 100 passed');
1585: x_plan_type_id := l_fin_plan_type_id; -- OUTPUT: x_plan_type_id
1586: pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM := l_margin_derived_from_code;
1587: x_calc_margin_from := l_margin_derived_from_code; -- OUTPUT: x_calc_margin_from
1588: pa_fp_view_plans_pub.G_FP_CALC_QUANTITY_FROM := l_labor_hours_from_code;
1650: pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE := l_display_revenue;
1651: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN := l_display_margin;
1652: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
1653: end if;
1654: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1655: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1656: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1657: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1658: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1651: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN := l_display_margin;
1652: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
1653: end if;
1654: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1655: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1656: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1657: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1658: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1659: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1652: pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT := l_display_marginpct;
1653: end if;
1654: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1655: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1656: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1657: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1658: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1659: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1660:
1653: end if;
1654: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1655: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1656: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1657: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1658: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1659: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1660:
1661: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
1654: --hr_utility.trace('quantityflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY);
1655: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1656: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1657: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1658: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1659: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1660:
1661: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
1662: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
1655: --hr_utility.trace('rawcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST);
1656: --hr_utility.trace('burdenedcostflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST);
1657: --hr_utility.trace('revenueflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE);
1658: --hr_utility.trace('marginflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN);
1659: --hr_utility.trace('marginpctflag= ' || pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT);
1660:
1661: -- set the DEFAULT AMOUNT TYPE/SUBTYPE CODES, based on order of hierarchy
1662: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
1663: l_default_amount_type_code := 'QUANTITY';
1716: pa_fp_view_plans_pub.G_DEFAULT_AMOUNT_TYPE_CODE := l_default_amount_type_code;
1717: pa_fp_view_plans_pub.G_DEFAULT_AMT_SUBTYPE_CODE := l_default_amount_subtype_code;
1718: pa_fp_view_plans_pub.G_FP_CURRENCY_CODE := l_projfunc_currency_code;
1719: pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE := p_view_currency_type;
1720: --hr_utility.trace('reached end of user customizations section: 200');
1721:
1722: BEGIN
1723: select pp.plan_period_type,
1724: pp.period1_start_date,
1738: FND_MSG_PUB.add_exc_msg
1739: ( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
1740: p_procedure_name => 'pa_fp_viewplan_hgrid_init:plan period type: 300');
1741: END;
1742: --hr_utility.trace('after selecting from pa_proj_period_profiles: 300 succeeded');
1743:
1744:
1745: -- SETTING PERIOD INFORMATION --
1746: -- ONLY IF IN PERIODIC MODE --
1760: x_number_of_periods => l_number_of_periods,
1761: x_return_status => l_return_status,
1762: x_msg_data => l_msg_data);
1763: -- get current period info
1764: --hr_utility.trace('executed get_prj_Period_profile_dtls');
1765: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
1766: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1767: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
1768: p_project_id => l_project_id
1761: x_return_status => l_return_status,
1762: x_msg_data => l_msg_data);
1763: -- get current period info
1764: --hr_utility.trace('executed get_prj_Period_profile_dtls');
1765: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
1766: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1767: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
1768: p_project_id => l_project_id
1769: ,p_period_type => l_plan_period_type
1762: x_msg_data => l_msg_data);
1763: -- get current period info
1764: --hr_utility.trace('executed get_prj_Period_profile_dtls');
1765: --hr_utility.trace('l_period_profile_type= ' || l_period_profile_type);
1766: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1767: Pa_Prj_Period_Profile_Utils.Get_Curr_Period_Profile_Info(
1768: p_project_id => l_project_id
1769: ,p_period_type => l_plan_period_type
1770: ,p_period_profile_type => 'FINANCIAL_PLANNING'
1773: ,x_end_period => l_cur_end_period
1774: ,x_return_status => l_return_status
1775: ,x_msg_count => l_msg_count
1776: ,x_msg_data => l_msg_data);
1777: --hr_utility.trace('executed get_curr_period_profile_info');
1778: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1779: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1780: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1781: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1774: ,x_return_status => l_return_status
1775: ,x_msg_count => l_msg_count
1776: ,x_msg_data => l_msg_data);
1777: --hr_utility.trace('executed get_curr_period_profile_info');
1778: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1779: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1780: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1781: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1782:
1775: ,x_msg_count => l_msg_count
1776: ,x_msg_data => l_msg_data);
1777: --hr_utility.trace('executed get_curr_period_profile_info');
1778: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1779: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1780: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1781: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1782:
1783: if l_period_profile_id = l_cur_period_profile_id then
1776: ,x_msg_data => l_msg_data);
1777: --hr_utility.trace('executed get_curr_period_profile_info');
1778: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1779: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1780: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1781: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1782:
1783: if l_period_profile_id = l_cur_period_profile_id then
1784: x_old_pd_profile_flag := 'N'; -- OUTPUT: x_old_pd_profile_flag
1777: --hr_utility.trace('executed get_curr_period_profile_info');
1778: --hr_utility.trace('l_plan_period_type= ' || l_plan_period_type);
1779: --hr_utility.trace('l_cur_period_profile_id= ' || l_cur_period_profile_id);
1780: --hr_utility.trace('l_cur_start_period= ' || l_cur_start_period);
1781: --hr_utility.trace('l_cur_end_period= ' || l_cur_end_period);
1782:
1783: if l_period_profile_id = l_cur_period_profile_id then
1784: x_old_pd_profile_flag := 'N'; -- OUTPUT: x_old_pd_profile_flag
1785: else
1827: p_period_type => ll_plan_period_type,
1828: x_return_status => l_return_status,
1829: x_msg_count => l_msg_count,
1830: x_msg_data => l_msg_data);
1831: --hr_utility.trace('END OF SETTING PERIOD INFORMATION: 400');
1832:
1833: end if; -- check p_amt_or_pd flag
1834: -- END OF SETTING PERIOD INFORMATION --
1835:
1846: -- G_FP_REV_VERSION_NUMBER
1847: -- G_FP_ALL_VERSION_ID (may be set already, if user-entered)
1848: -- G_FP_ALL_VERSION_NAME
1849: -- G_FP_ALL_VERSION_NUMBER
1850: --hr_utility.trace('calling view_plan_temp_tables');
1851: -- delete residual data in the temp tables
1852: DELETE from PA_FIN_VP_AMTS_VIEW_TMP;
1853: DELETE from PA_FIN_VP_PDS_VIEW_TMP;
1854:
1946:
1947: -- figure out LOCKED BY after version sources set; also get RECORD VERSION NUMBERS
1948: -- COST VERSION
1949: if pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null then
1950: --hr_utility.trace('figuring out cost LOCKED BY');
1951: select record_version_number, -- OUTPUT: x_cost_rv_number
1952: budget_status_code -- OUTPUT: x_budget_status_code
1953: into x_cost_rv_number,
1954: x_budget_status_code
1974: end if; -- END:COST VERSION
1975:
1976: -- REVENUE VERSION
1977: if pa_fp_view_plans_pub.G_FP_REV_VERSION_ID is not null then
1978: --hr_utility.trace('figuring out revenue LOCKED BY');
1979: select record_version_number, -- OUTPUT: x_rev_rv_number
1980: budget_status_code -- OUTPUT: x_budget_status_code
1981: into x_rev_rv_number,
1982: x_budget_status_code
2001: end if; -- is_rev_locked_by_user
2002: end if; -- END:REVENUE VERSION
2003:
2004:
2005: --hr_utility.trace('after calling locked version stuff');
2006: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2007: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2008: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2002: end if; -- END:REVENUE VERSION
2003:
2004:
2005: --hr_utility.trace('after calling locked version stuff');
2006: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2007: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2008: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2003:
2004:
2005: --hr_utility.trace('after calling locked version stuff');
2006: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2007: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2008: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2004:
2005: --hr_utility.trace('after calling locked version stuff');
2006: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2007: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2008: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2012: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2005: --hr_utility.trace('after calling locked version stuff');
2006: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2007: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2008: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2012: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2013: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2006: --hr_utility.trace('G_FP_PERIOD_TYPE = ' || pa_fp_view_plans_pub.G_FP_PERIOD_TYPE);
2007: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2008: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2012: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2013: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2014: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2007: --hr_utility.trace('G_FP_VIEW_START_DATE1 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1));
2008: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2012: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2013: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2014: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2015: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2008: --hr_utility.trace('G_FP_VIEW_START_DATE2 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE2));
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2012: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2013: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2014: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2015: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2016: */
2009: --hr_utility.trace('G_FP_VIEW_START_DATE3 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE3));
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2012: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2013: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2014: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2015: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2016: */
2017:
2010: --hr_utility.trace('G_FP_VIEW_START_DATE4 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE4));
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2012: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2013: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2014: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2015: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2016: */
2017:
2018: EXCEPTION
2011: --hr_utility.trace('G_FP_VIEW_START_DATE5 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE5));
2012: --hr_utility.trace('G_FP_VIEW_START_DATE6 = ' || to_char(pa_fp_view_plans_pub.G_FP_VIEW_START_DATE6));
2013: --hr_utility.trace('x_cost_rv_number= ' || TO_CHAR(x_cost_rv_number));
2014: --hr_utility.trace('x_rev_rv_number= ' || TO_CHAR(x_rev_rv_number));
2015: --hr_utility.trace('x_budget_status_code= ' || x_budget_status_code);
2016: */
2017:
2018: EXCEPTION
2019: WHEN OTHERS THEN
2118: FETCH C1 INTO v_start_date_tab(i),v_end_date_tab(i);
2119:
2120: -- dbms_output.put_line('i: '||i);
2121: i := i+1;
2122: hr_utility.trace('ok so far');
2123: EXIT WHEN C1%NOTFOUND;
2124:
2125: END LOOP;
2126:
2262: pa_fp_view_plans_pub.G_FP_PLAN_END_DATE
2263: order by start_date desc;
2264:
2265: BEGIN
2266: --hr_utility.trace_on(null, 'dlai');
2267: l_rownum := p_num_of_periods;
2268: ll_start_date := null;
2269: l_start_date := pa_fp_view_plans_pub.G_FP_VIEW_START_DATE1;
2270:
2315: OPEN C_backward(l_start_date);
2316:
2317: LOOP
2318: FETCH C_backward INTO ll_start_date;
2319: --hr_utility.trace('current ll_startdate= ' || to_char(ll_start_date));
2320: EXIT WHEN C_backward%NOTFOUND;
2321: EXIT WHEN C_backward%ROWCOUNT = l_rownum;
2322:
2323: END LOOP;
2329: x_start_date := l_start_date;
2330: else
2331: x_start_date := to_char(ll_start_date);
2332: end if;
2333: --hr_utility.trace('x_start_date= ' || x_start_date);
2334: END pa_fp_set_periods_nav;
2335:
2336:
2337: FUNCTION Get_Version_ID return NUMBER is
2730: l_msg_data VARCHAR2(80);
2731:
2732: begin
2733: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '100: entered procedure', 2);
2734: --hr_utility.trace_on(null, 'dlai');
2735: --hr_utility.trace('entered view_plan_temp_tables');
2736: --hr_utility.trace('p_budget_version_id= ' || TO_CHAR(p_budget_version_id));
2737: x_msg_count := 0;
2738: x_return_status := FND_API.G_RET_STS_SUCCESS;
2731:
2732: begin
2733: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '100: entered procedure', 2);
2734: --hr_utility.trace_on(null, 'dlai');
2735: --hr_utility.trace('entered view_plan_temp_tables');
2736: --hr_utility.trace('p_budget_version_id= ' || TO_CHAR(p_budget_version_id));
2737: x_msg_count := 0;
2738: x_return_status := FND_API.G_RET_STS_SUCCESS;
2739: SAVEPOINT VIEW_PLAN_TEMP_TABLES;
2732: begin
2733: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '100: entered procedure', 2);
2734: --hr_utility.trace_on(null, 'dlai');
2735: --hr_utility.trace('entered view_plan_temp_tables');
2736: --hr_utility.trace('p_budget_version_id= ' || TO_CHAR(p_budget_version_id));
2737: x_msg_count := 0;
2738: x_return_status := FND_API.G_RET_STS_SUCCESS;
2739: SAVEPOINT VIEW_PLAN_TEMP_TABLES;
2740: l_diff_pd_profile_flag := 'N';
2741:
2742: -- FOR ADVANCED DISPLAY OPTIONS CUSTOMIZATION: USER-SELECTED BUDGET VERSIONS
2743: -- order of checking: ALL, COST, REVENUE
2744: if p_user_bv_flag = 'Y' then
2745: --hr_utility.trace('p_user_bv_flag = Y');
2746: -- assume that the BV_ID variables have already been set
2747: if pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID is not null then
2748: l_primary_bvid := pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID;
2749: elsif pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null then
2750: l_primary_bvid := pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
2751: else
2752: l_primary_bvid := pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2753: end if;
2754: --hr_utility.trace('allversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID));
2755: --hr_utility.trace('COSTversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_COST_VERSION_ID));
2756: --hr_utility.trace('REVversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_REV_VERSION_ID));
2757:
2758: --hr_utility.trace('selecting from pa_budget_versions');
2751: else
2752: l_primary_bvid := pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2753: end if;
2754: --hr_utility.trace('allversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID));
2755: --hr_utility.trace('COSTversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_COST_VERSION_ID));
2756: --hr_utility.trace('REVversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_REV_VERSION_ID));
2757:
2758: --hr_utility.trace('selecting from pa_budget_versions');
2759: select DECODE(budget_status_code,
2752: l_primary_bvid := pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2753: end if;
2754: --hr_utility.trace('allversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID));
2755: --hr_utility.trace('COSTversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_COST_VERSION_ID));
2756: --hr_utility.trace('REVversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_REV_VERSION_ID));
2757:
2758: --hr_utility.trace('selecting from pa_budget_versions');
2759: select DECODE(budget_status_code,
2760: 'B', 'B',
2754: --hr_utility.trace('allversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID));
2755: --hr_utility.trace('COSTversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_COST_VERSION_ID));
2756: --hr_utility.trace('REVversionid= ' || to_char(pa_fp_view_plans_pub.G_FP_REV_VERSION_ID));
2757:
2758: --hr_utility.trace('selecting from pa_budget_versions');
2759: select DECODE(budget_status_code,
2760: 'B', 'B',
2761: 'W'),
2762: fin_plan_type_id
2773: project_id = p_project_id;
2774:
2775: -- figure out display_from:
2776: if l_fp_preference_code = 'COST_AND_REV_SAME' then
2777: --hr_utility.trace('user_specified: COST_AND_REV_SAME');
2778: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'ANY';
2779: if pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID is null then
2780: if pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null then
2781: pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID := pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
2800: x_msg_count => l_msg_count,
2801: x_msg_data => l_msg_data);
2802:
2803: elsif l_fp_preference_code = 'COST_ONLY' then
2804: --hr_utility.trace('user_specified: COST_ONLY');
2805: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
2806: select version_number,
2807: version_name
2808: into l_cost_version_number,
2821: x_msg_count => l_msg_count,
2822: x_msg_data => l_msg_data);
2823:
2824: elsif l_fp_preference_code = 'REVENUE_ONLY' then
2825: --hr_utility.trace('user_specified: REVENUE_ONLY');
2826: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
2827: select version_number,
2828: version_name
2829: into l_rev_version_number,
2842: x_msg_count => l_msg_count,
2843: x_msg_data => l_msg_data);
2844:
2845: else
2846: --hr_utility.trace('user_specified: COST_AND_REV_SEP');
2847: if (pa_fp_view_plans_pub.G_FP_COST_VERSION_ID is not null) and
2848: (pa_fp_view_plans_pub.G_FP_REV_VERSION_ID is not null) then
2849:
2850: -- BEFORE MERGING, MAKE SURE PERIOD PROFILES MATCH
2855: select period_profile_id
2856: into l_period_profile_id2
2857: from pa_budget_versions
2858: where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2859: --hr_utility.trace('retrieved the period profile ids');
2860: if (pa_fp_view_plans_util.check_compatible_pd_profiles
2861: (p_period_profile_id1 => l_period_profile_id1,
2862: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
2863: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
2860: if (pa_fp_view_plans_util.check_compatible_pd_profiles
2861: (p_period_profile_id1 => l_period_profile_id1,
2862: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
2863: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
2864: --hr_utility.trace('user_specified: period_profile matches');
2865: -- PERIOD PROFILES MATCH: GO AHEAD WITH MERGING
2866: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
2867: select version_number,
2868: version_name
2879: from pa_budget_versions
2880: where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
2881: pa_fp_view_plans_pub.G_FP_REV_VERSION_NUMBER := l_rev_version_number;
2882: pa_fp_view_plans_pub.G_FP_REV_VERSION_NAME := l_rev_version_name;
2883: --hr_utility.trace('entering pa_fp_vp_pop_tables_separate');
2884: pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate
2885: (p_project_id => p_project_id,
2886: p_cost_budget_version_id => pa_fp_view_plans_pub.G_FP_COST_VERSION_ID,
2887: p_rev_budget_version_id => pa_fp_view_plans_pub.G_FP_REV_VERSION_ID,
2887: p_rev_budget_version_id => pa_fp_view_plans_pub.G_FP_REV_VERSION_ID,
2888: x_return_status => l_return_status,
2889: x_msg_count => l_msg_count,
2890: x_msg_data => l_msg_data);
2891: --hr_utility.trace('exiting pa_fp_vp_pop_tables_separate');
2892: else
2893: -- PERIOD PROFILES DO NOT MATCH: ERROR MESSAGE
2894: --hr_utility.trace('user-defined bvs: merge failed because mismatch pd profiles');
2895: l_diff_pd_profile_flag := 'Y';
2890: x_msg_data => l_msg_data);
2891: --hr_utility.trace('exiting pa_fp_vp_pop_tables_separate');
2892: else
2893: -- PERIOD PROFILES DO NOT MATCH: ERROR MESSAGE
2894: --hr_utility.trace('user-defined bvs: merge failed because mismatch pd profiles');
2895: l_diff_pd_profile_flag := 'Y';
2896: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
2897: select version_number,
2898: version_name
2963: end if; -- l_fp_preference_code
2964:
2965: -- USER DID NOT SPECIFY ANY BUDGET VERSIONS; PROCEED AS NORMAL
2966: else
2967: --hr_utility.trace('USER DID NOT SPECIFY ANY BUDGET VERSIONS: 1000');
2968:
2969: select DECODE(budget_status_code,
2970: 'B', 'B',
2971: 'W'),
2985: -- if the preference code is 'COST_AND_REV_SAME', then we only need this one version
2986: -- we blindly insert all rows for the budget version into the temp table
2987: if l_fp_preference_code = 'COST_AND_REV_SAME' then
2988: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '200: pref = COST_AND_REV_SAME', 1);
2989: --hr_utility.trace('COST_AND_REV_SAME');
2990: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'ANY';
2991: select version_name,
2992: version_number
2993: into l_cost_version_name,
3009: -- if the preference code is 'COST_ONLY', then we have the bvID of the COST version
3010: -- and we don't have to do anything else
3011: elsif l_fp_preference_code = 'COST_ONLY' then
3012: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '300: pref=COST_ONLY', 1);
3013: --hr_utility.trace('COST_ONLY');
3014: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3015: select version_number,
3016: version_name
3017: into l_cost_version_number,
3032: x_msg_data => l_msg_data);
3033:
3034: elsif l_fp_preference_code = 'REVENUE_ONLY' then
3035: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '400: pref=REVENUE_ONLY', 1);
3036: --hr_utility.trace('REVENUE_ONLY');
3037: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3038: select version_number,
3039: version_name
3040: into l_rev_version_number,
3060: -- have to inspect bv.version_type (which can be 'COST', 'REVENUE', etc)
3061: else
3062: -- find the appropriate current working version
3063: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '400: pref=COST_AND_REV_SEP', 1);
3064: --hr_utility.trace('COST_AND_REV_SEP');
3065: if l_working_or_baselined = 'W' then
3066: -- determine whether we have the COST or REVENUE version, and look for the other one
3067: if p_cost_or_revenue = 'C' then
3068: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '500: pref=COST_AND_REV_SEP, WORKING, COST', 2);
3065: if l_working_or_baselined = 'W' then
3066: -- determine whether we have the COST or REVENUE version, and look for the other one
3067: if p_cost_or_revenue = 'C' then
3068: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '500: pref=COST_AND_REV_SEP, WORKING, COST', 2);
3069: --hr_utility.trace('we have a working cost version');
3070: -- we have the COST version, so look for REVENUE version with 'COST_AND_REV_SEP'
3071: open l_compl_crsep_c_w_csr;
3072: fetch l_compl_crsep_c_w_csr into l_compl_crsep_c_w_rec;
3073: if l_compl_crsep_c_w_csr%NOTFOUND then
3080: close l_compl_crsep_c_w_csr;
3081: if l_compl_budget_version_id = -99 then
3082: -- did not find a compl for the COST version; just process COST version
3083: 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);
3084: --hr_utility.trace('could NOT find complement for working cost version');
3085: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3086: select version_number,
3087: version_name
3088: into l_cost_version_number,
3116: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
3117: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
3118: --merge the COST and REVENUE versions
3119: 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);
3120: --hr_utility.trace('found complement for working cost version AND period profile match');
3121: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3122: select version_number,
3123: version_name
3124: into l_cost_version_number,
3146: x_msg_data => l_msg_data);
3147: else
3148: -- the period profiles didn't match: only process one of the versions
3149: 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);
3150: --hr_utility.trace('found complement for working cost version BUT no per profile match');
3151: l_diff_pd_profile_flag := 'Y';
3152:
3153: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3154: select version_number,
3195: end if; -- l_compl_budget_version_id is null
3196: else
3197: -- we have the REVENUE version, so look for COST version with 'COST_AND_REV_SEP'
3198: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '900: pref=COST_AND_REV_SEP, WORKING, REVENUE', 1);
3199: --hr_utility.trace('we have a working revenue version');
3200: open l_compl_crsep_r_w_csr;
3201: fetch l_compl_crsep_r_w_csr into l_compl_crsep_r_w_rec;
3202: if l_compl_crsep_r_w_csr%NOTFOUND then
3203: l_compl_budget_version_id := -99;
3209: close l_compl_crsep_r_w_csr;
3210: if l_compl_budget_version_id = -99 then
3211: -- did not find a compl for the REVENUE version; just process REVENUE version
3212: 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);
3213: --hr_utility.trace('could not find complement for working revenue version');
3214: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3215: select version_number,
3216: version_name
3217: into l_rev_version_number,
3245: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
3246: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
3247: --merge the COST and REVENUE versions
3248: 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);
3249: --hr_utility.trace('found compl for working revenue version AND period profile match');
3250: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3251: select version_number,
3252: version_name
3253: into l_rev_version_number,
3275: x_msg_data => l_msg_data);
3276: else
3277: -- the period profiles didn't match: only process one of the versions
3278: 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);
3279: --hr_utility.trace('found compl for working revenue version BUT no per profile match');
3280: l_diff_pd_profile_flag := 'Y';
3281: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3282: select version_number,
3283: version_name
3327: -- determine whether we have the COST or REVENUE version, and look for the other one
3328: if p_cost_or_revenue = 'C' then
3329: -- we have the COST version, so look for REVENUE version with 'COST_AND_REV_SEP'
3330: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1300: pref=COST_AND_REV_SEP, BASELINED, COST', 1);
3331: --hr_utility.trace('we have a baselined cost version');
3332: open l_compl_crsep_c_b_csr;
3333: fetch l_compl_crsep_c_b_csr into l_compl_crsep_c_b_rec;
3334: if l_compl_crsep_c_b_csr%NOTFOUND then
3335: l_compl_budget_version_id := -99;
3341: close l_compl_crsep_c_b_csr;
3342: if l_compl_budget_version_id = -99 then
3343: -- did not find a compl for the COST version; just process COST version
3344: 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);
3345: --hr_utility.trace('could not find complement for baselined cost version');
3346: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3347: select version_number,
3348: version_name
3349: into l_cost_version_number,
3377: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
3378: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
3379: --merge the COST and REVENUE versions
3380: 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);
3381: --hr_utility.trace('found compl for baselined cost AND period profile match');
3382: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3383: select version_number,
3384: version_name
3385: into l_cost_version_number,
3407: x_msg_data => l_msg_data);
3408: else
3409: -- period_profiles don't match; process only the COST version
3410: 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);
3411: --hr_utility.trace('found compl for baselined cost BUT no period profile match');
3412: l_diff_pd_profile_flag := 'Y';
3413: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'COST';
3414: select version_number,
3415: version_name
3455:
3456: else
3457: -- we have the REVENUE version, so look for COST version with 'COST_AND_REV_SEP'
3458: pa_debug.write('pa_fp_view_plans_pub.view_plan_temp_tables', '1700: pref=COST_AND_REV_SEP, BASELINED, REVENUE', 1);
3459: --hr_utility.trace('we have a baselined revenue version');
3460: open l_compl_crsep_r_b_csr;
3461: fetch l_compl_crsep_r_b_csr into l_compl_crsep_r_b_rec;
3462: if l_compl_crsep_r_b_csr%NOTFOUND then
3463: l_compl_budget_version_id := -99;
3469: close l_compl_crsep_r_b_csr;
3470: if l_compl_budget_version_id = -99 then
3471: -- did not find a compl for the REVENUE version; just process REVENUE version
3472: 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);
3473: --hr_utility.trace('we have a baselined revenue version');
3474: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3475: select version_number,
3476: version_name
3477: into l_rev_version_number,
3505: p_period_profile_id2 => l_period_profile_id2) = 'Y') or
3506: not (pa_fp_view_plans_pub.G_AMT_OR_PD = 'P') then
3507: --merge the COST and REVENUE versions
3508: 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);
3509: --hr_utility.trace('found compl for baselined revenue version AND period profile match');
3510: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'BOTH';
3511: select version_number,
3512: version_name
3513: into l_rev_version_number,
3535: x_msg_data => l_msg_data);
3536: else
3537: -- period profiles not compatible; just process REVENUE version
3538: 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);
3539: --hr_utility.trace('found compl for baselined revenue version BUT no per profile match');
3540: l_diff_pd_profile_flag := 'Y';
3541: pa_fp_view_plans_pub.G_DISPLAY_FROM := 'REVENUE';
3542: select version_number,
3543: version_name
3589: x_rev_version_name := l_rev_version_name;
3590:
3591: end if; -- p_user_bv_flag
3592: x_diff_pd_profile_flag := l_diff_pd_profile_flag;
3593: --hr_utility.trace('diff_pd_profile_flag= ' || x_diff_pd_profile_flag);
3594:
3595: EXCEPTION
3596: when others then
3597: --hr_utility.trace('UNHANDLED EXCEPTION');
3593: --hr_utility.trace('diff_pd_profile_flag= ' || x_diff_pd_profile_flag);
3594:
3595: EXCEPTION
3596: when others then
3597: --hr_utility.trace('UNHANDLED EXCEPTION');
3598: rollback to VIEW_PLAN_TEMP_TABLES;
3599: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3600: x_msg_count := 1;
3601: x_msg_data := SQLERRM;
3826:
3827: begin
3828: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '100: entering procedure', 2);
3829: l_err_stage := 100;
3830: --hr_utility.trace('entered pa_fp_vp_pop_tables_separate');
3831: x_return_status := FND_API.G_RET_STS_SUCCESS;
3832: x_msg_count := 0;
3833: SAVEPOINT VIEW_PLANS_POP_TABLES_SEP;
3834:
3913: close av_revenue_csr;
3914: l_err_stage := 500;
3915: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '300: bulk collected into pl/sql tables', 1);
3916: -- now, iterate through all rows in the COST table,
3917: --hr_utility.trace('l_c_project_id.first = ' || TO_CHAR(l_c_project_id.first));
3918: for i in nvl(l_c_project_id.first,0)..nvl(l_c_project_id.last,-1) loop
3919: l_found_complement := false;
3920: -- look for a row in REVENUE table with same project-task-resource id combo
3921: for j in nvl(l_r_project_id.first,0)..nvl(l_r_project_id.last,-1) loop
3925: ((l_r_resource_list_member_id(j) = l_c_resource_list_member_id(i)) or
3926: (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
3927: (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
3928: -- a match has been found: add data from REVENUE PL/SQL to the COST PL/SQL table
3929: --hr_utility.trace('FOUND MATCH');
3930: --hr_utility.trace('cost res assid= ' || to_char(l_c_cost_res_assignment_id(i)));
3931: --hr_utility.trace('rev res assid= ' || to_char(l_r_revenue_res_assignment_id(j)));
3932: l_found_complement := true;
3933: l_c_revenue_budget_version_id(i) := l_r_revenue_budget_version_id(j);
3926: (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
3927: (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
3928: -- a match has been found: add data from REVENUE PL/SQL to the COST PL/SQL table
3929: --hr_utility.trace('FOUND MATCH');
3930: --hr_utility.trace('cost res assid= ' || to_char(l_c_cost_res_assignment_id(i)));
3931: --hr_utility.trace('rev res assid= ' || to_char(l_r_revenue_res_assignment_id(j)));
3932: l_found_complement := true;
3933: l_c_revenue_budget_version_id(i) := l_r_revenue_budget_version_id(j);
3934: l_c_revenue_res_assignment_id(i) := l_r_revenue_res_assignment_id(j);
3927: (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
3928: -- a match has been found: add data from REVENUE PL/SQL to the COST PL/SQL table
3929: --hr_utility.trace('FOUND MATCH');
3930: --hr_utility.trace('cost res assid= ' || to_char(l_c_cost_res_assignment_id(i)));
3931: --hr_utility.trace('rev res assid= ' || to_char(l_r_revenue_res_assignment_id(j)));
3932: l_found_complement := true;
3933: l_c_revenue_budget_version_id(i) := l_r_revenue_budget_version_id(j);
3934: l_c_revenue_res_assignment_id(i) := l_r_revenue_res_assignment_id(j);
3935: l_c_revenue(i) := l_r_revenue(j);
3970:
3971: -- process the QUANTITY row based on the value of l_report_labor_hrs_from_code
3972: if l_report_labor_hrs_from_code = 'COST' then
3973: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
3974: --hr_utility.trace('adding QUANTITY row');
3975: l_row_number := l_row_number + 1; -- increment row counter
3976: l_pd_project_id(l_row_number) := l_c_project_id(i);
3977: l_pd_task_id(l_row_number) := l_c_task_id(i);
3978: l_pd_resource_list_member_id(l_row_number) := l_c_resource_list_member_id(i);
4005: p_resource_assignment_id => l_pd_cost_res_assignment_id(l_row_number),
4006: p_project_currency_type => l_currency_type,
4007: p_amount_type_id => 215,
4008: p_period_number => 1);
4009: --hr_utility.trace('VALUE is = ' || to_char(l_pd_period_1(l_row_number)));
4010: --hr_utility.trace('get_period_n_value for period 2');
4011: l_pd_period_2(l_row_number) := pa_fp_view_plans_util.get_period_n_value
4012: (p_period_profile_id => l_period_profile_id,
4013: p_budget_version_id => l_pd_cost_budget_version_id(l_row_number),
4006: p_project_currency_type => l_currency_type,
4007: p_amount_type_id => 215,
4008: p_period_number => 1);
4009: --hr_utility.trace('VALUE is = ' || to_char(l_pd_period_1(l_row_number)));
4010: --hr_utility.trace('get_period_n_value for period 2');
4011: l_pd_period_2(l_row_number) := pa_fp_view_plans_util.get_period_n_value
4012: (p_period_profile_id => l_period_profile_id,
4013: p_budget_version_id => l_pd_cost_budget_version_id(l_row_number),
4014: p_resource_assignment_id => l_pd_cost_res_assignment_id(l_row_number),
4110: end if; -- display_flag: QUANTITY
4111: end if; -- QUANTITY
4112:
4113: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST = 'Y' then
4114: --hr_utility.trace('adding BURDENED COST row');
4115: -- process the BURDENED_COST row from the COST version
4116: l_row_number := l_row_number + 1; -- increment row counter
4117: -- 02/14/03 dlai: use appropriate cost row based on G_FP_CALC_MARGIN_FROM
4118: if pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM = 'B' then
4156: p_project_currency_type => l_currency_type,
4157: --p_amount_type_id => 150,
4158: p_amount_type_id => 165,
4159: p_period_number => 1);
4160: --hr_utility.trace('VALUE is = ' || to_char(l_pd_period_1(l_row_number)));
4161: l_pd_period_2(l_row_number) := pa_fp_view_plans_util.get_period_n_value
4162: (p_period_profile_id => l_period_profile_id,
4163: p_budget_version_id => l_pd_cost_budget_version_id(l_row_number),
4164: p_resource_assignment_id => l_pd_cost_res_assignment_id(l_row_number),
4273: l_pd_project_total(l_row_number) := l_c_burdened_cost(i); -- bug 2699651
4274: end if; -- display_flag: BURDENED_COST
4275:
4276: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST = 'Y' then
4277: --hr_utility.trace('adding RAW COST row');
4278: -- process the RAW_COST row from the COST version
4279: l_row_number := l_row_number + 1; -- increment row counter
4280: -- 02/14/03 dlai: use appropriate cost row based on G_FP_CALC_MARGIN_FROM
4281: if pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM <> 'B' then
4584: end if; --display_flag: QUANTITY
4585: end if; -- QUANTITY
4586:
4587: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE = 'Y' then
4588: --hr_utility.trace('adding REVENUE row');
4589: -- process the REVENUE row for this REVENUE version
4590: l_row_number := l_row_number + 1; -- increment row counter
4591: l_revenue_row_number := l_row_number; -- used when calculating margin, margin_percent
4592: l_pd_project_id(l_row_number) := l_r_project_id(j);
4723: l_pd_project_total(l_row_number) := l_r_revenue(j); -- bug 2699651
4724: end if; -- display_flag: REVENUE
4725:
4726: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN = 'Y' then
4727: --hr_utility.trace('adding MARGIN row');
4728: -- finally, insert the MARGIN and MARGIN_PERCENT rows
4729: -- MARGIN row
4730: l_row_number := l_row_number + 1; -- increment row counter
4731: l_pd_project_id(l_row_number) := l_c_project_id(i);
4773: 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
4774: end if; -- display_flag: MARGIN
4775:
4776: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT = 'Y' then
4777: --hr_utility.trace('adding MARGIN PERCENT row');
4778: -- MARGIN_PERCENT
4779: l_row_number := l_row_number + 1; -- increment row counter
4780: l_pd_project_id(l_row_number) := l_c_project_id(i);
4781: l_pd_task_id(l_row_number) := l_c_task_id(i);
6137: 'Y'); -- PERFORMANCE LIABILITY: FOR UNMARRIED REVENUE ROWS, WE SAY ALWAYS HAVE CHILD
6138: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD= 'A'
6139: l_err_stage := 900;
6140: 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);
6141: --hr_utility.trace('total rows is ' || to_char(nvl(l_c_project_id.last,0) + nvl(l_r_project_id.last,0)));
6142: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
6143: -- PERIODS PL/SQL table
6144: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6145: --hr_utility.trace('inserting into pa_fin_vp_pds_view_tmp for POP_TEM_TABLES_SEP');
6141: --hr_utility.trace('total rows is ' || to_char(nvl(l_c_project_id.last,0) + nvl(l_r_project_id.last,0)));
6142: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
6143: -- PERIODS PL/SQL table
6144: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6145: --hr_utility.trace('inserting into pa_fin_vp_pds_view_tmp for POP_TEM_TABLES_SEP');
6146: --hr_utility.trace('l_pd_project_id.last= ' || to_char(l_pd_project_id.last));
6147: forall z in nvl(l_pd_project_id.first,0)..nvl(l_pd_project_id.last,-1)
6148: insert into pa_fin_vp_pds_view_tmp
6149: (project_id,
6142: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
6143: -- PERIODS PL/SQL table
6144: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6145: --hr_utility.trace('inserting into pa_fin_vp_pds_view_tmp for POP_TEM_TABLES_SEP');
6146: --hr_utility.trace('l_pd_project_id.last= ' || to_char(l_pd_project_id.last));
6147: forall z in nvl(l_pd_project_id.first,0)..nvl(l_pd_project_id.last,-1)
6148: insert into pa_fin_vp_pds_view_tmp
6149: (project_id,
6150: task_id,
6219: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '800: leaving procedure', 2);
6220: EXCEPTION
6221: when others then
6222: rollback to VIEW_PLANS_POP_TABLES_SEP;
6223: --hr_utility.trace('error stage= ' || to_char(l_err_stage));
6224: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6225: x_msg_count := 1;
6226: x_msg_data := SQLERRM;
6227: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
6402: -- local debugging variables
6403: l_err_stage NUMBER(15);
6404:
6405: begin
6406: --hr_utility.trace_on(null, 'dlai');
6407: l_err_stage := 100;
6408: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '100: entering pa_fp_vp_pop_tables_together', 2);
6409: --hr_utility.trace('entered pa_fp_vp_pop_tables_together: 100');
6410: x_return_status := FND_API.G_RET_STS_SUCCESS;
6405: begin
6406: --hr_utility.trace_on(null, 'dlai');
6407: l_err_stage := 100;
6408: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '100: entering pa_fp_vp_pop_tables_together', 2);
6409: --hr_utility.trace('entered pa_fp_vp_pop_tables_together: 100');
6410: x_return_status := FND_API.G_RET_STS_SUCCESS;
6411: x_msg_count := 0;
6412: SAVEPOINT VIEW_PLANS_POP_TABLES_SAME;
6413:
6438: po.project_id = p_project_id and
6439: po.fin_plan_option_level_code = 'PLAN_TYPE';
6440: l_err_stage := 200;
6441: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '200: retrieved periodprofileid= ' || l_period_profile_id, 1);
6442: --hr_utility.trace('retrieved period profile id: 200');
6443: -- populate the AMOUNTS PL/SQL table
6444: open av_csr;
6445: fetch av_csr bulk collect into
6446: l_project_id,
6463: l_has_child_element;
6464: close av_csr;
6465: l_err_stage := 300;
6466: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '300: bulk collected into amts pl/sql table', 1);
6467: --hr_utility.trace('bulk collected into amts pl/sql table: 300');
6468: --hr_utility.trace('number of rows = ' || TO_CHAR(l_project_id.last));
6469:
6470: -- populate the PERIODS PL/SQL table
6471: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
6464: close av_csr;
6465: l_err_stage := 300;
6466: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '300: bulk collected into amts pl/sql table', 1);
6467: --hr_utility.trace('bulk collected into amts pl/sql table: 300');
6468: --hr_utility.trace('number of rows = ' || TO_CHAR(l_project_id.last));
6469:
6470: -- populate the PERIODS PL/SQL table
6471: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
6472: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6469:
6470: -- populate the PERIODS PL/SQL table
6471: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
6472: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6473: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
6474: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
6475: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
6476: --hr_utility.trace('i= ' || to_char(i));
6477: --hr_utility.trace('loop: ' || to_char(i));
6470: -- populate the PERIODS PL/SQL table
6471: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
6472: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6473: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
6474: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
6475: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
6476: --hr_utility.trace('i= ' || to_char(i));
6477: --hr_utility.trace('loop: ' || to_char(i));
6478: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
6472: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
6473: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
6474: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
6475: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
6476: --hr_utility.trace('i= ' || to_char(i));
6477: --hr_utility.trace('loop: ' || to_char(i));
6478: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
6479: l_row_number := l_row_number + 1;
6480: -- process the QUANTITY numbers
6473: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
6474: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
6475: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
6476: --hr_utility.trace('i= ' || to_char(i));
6477: --hr_utility.trace('loop: ' || to_char(i));
6478: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
6479: l_row_number := l_row_number + 1;
6480: -- process the QUANTITY numbers
6481: l_pd_project_id(l_row_number) := l_project_id(i);
6501: l_pd_has_child_element(l_row_number) := 'N';
6502: else
6503: l_pd_has_child_element(l_row_number) := 'Y';
6504: end if;
6505: --hr_utility.trace('quantity: period1');
6506: --hr_utility.trace('p_period_profile_id= ' || to_char(l_period_profile_id));
6507: --hr_utility.trace('p_resource_assignment_id= ' || to_char(l_pd_res_assignment_id(l_row_number)));
6508: --hr_utility.trace('p_project_currency_type= ' || l_currency_type);
6509: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
6502: else
6503: l_pd_has_child_element(l_row_number) := 'Y';
6504: end if;
6505: --hr_utility.trace('quantity: period1');
6506: --hr_utility.trace('p_period_profile_id= ' || to_char(l_period_profile_id));
6507: --hr_utility.trace('p_resource_assignment_id= ' || to_char(l_pd_res_assignment_id(l_row_number)));
6508: --hr_utility.trace('p_project_currency_type= ' || l_currency_type);
6509: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
6510: (p_period_profile_id => l_period_profile_id,
6503: l_pd_has_child_element(l_row_number) := 'Y';
6504: end if;
6505: --hr_utility.trace('quantity: period1');
6506: --hr_utility.trace('p_period_profile_id= ' || to_char(l_period_profile_id));
6507: --hr_utility.trace('p_resource_assignment_id= ' || to_char(l_pd_res_assignment_id(l_row_number)));
6508: --hr_utility.trace('p_project_currency_type= ' || l_currency_type);
6509: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
6510: (p_period_profile_id => l_period_profile_id,
6511: p_budget_version_id => l_pd_budget_version_id(l_row_number),
6504: end if;
6505: --hr_utility.trace('quantity: period1');
6506: --hr_utility.trace('p_period_profile_id= ' || to_char(l_period_profile_id));
6507: --hr_utility.trace('p_resource_assignment_id= ' || to_char(l_pd_res_assignment_id(l_row_number)));
6508: --hr_utility.trace('p_project_currency_type= ' || l_currency_type);
6509: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
6510: (p_period_profile_id => l_period_profile_id,
6511: p_budget_version_id => l_pd_budget_version_id(l_row_number),
6512: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
6649: if l_burdened_cost(i) is not null then
6650: l_cost_row_number := l_row_number;
6651: end if;
6652: */
6653: --hr_utility.trace('burdened cost: period1');
6654: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
6655: (p_period_profile_id => l_period_profile_id,
6656: p_budget_version_id => l_pd_budget_version_id(l_row_number),
6657: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
6809: if l_burdened_cost(i) is null then
6810: l_cost_row_number := l_row_number;
6811: end if;
6812: */
6813: --hr_utility.trace('raw cost: period1');
6814: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
6815: (p_period_profile_id => l_period_profile_id,
6816: p_budget_version_id => l_pd_budget_version_id(l_row_number),
6817: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
6961: else
6962: l_pd_has_child_element(l_row_number) := 'Y';
6963: end if;
6964: l_revenue_row_number := l_row_number;
6965: --hr_utility.trace('revenue: period1');
6966: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
6967: (p_period_profile_id => l_period_profile_id,
6968: p_budget_version_id => l_pd_budget_version_id(l_row_number),
6969: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
7163: end loop;
7164: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
7165: l_err_stage := 400;
7166: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '400: populated the pds pl/sql tables', 1);
7167: --hr_utility.trace('populated the pds pl/sql tables: 400');
7168:
7169: /* TRANSFER DATA FROM PL/SQL TABLES TO GLOBAL TEMPORARY TABLES */
7170: -- POPULATE global temporary table PA_FIN_VP_AMTS_VIEW_TMP
7171: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'A'
7211: l_unit_of_measure(i),
7212: l_has_child_element(i));
7213: l_err_stage := 500;
7214: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '500: populated the amts_tmp tables', 1);
7215: --hr_utility.trace('populated the amts_tmp table: 500');
7216: commit;
7217: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'A'
7218:
7219: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
7219: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP from the
7220: -- PERIODS PL/SQL table
7221: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
7222: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7223: --hr_utility.trace('l_pd_project_id.first= ' || TO_CHAR(l_pd_project_id.first));
7224: --hr_utility.trace('l_pd_project_id.last= ' || TO_CHAR(l_pd_project_id.last));
7225: forall z in nvl(l_pd_project_id.first,0)..nvl(l_pd_project_id.last,-1)
7226: insert into pa_fin_vp_pds_view_tmp
7227: (project_id,
7220: -- PERIODS PL/SQL table
7221: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
7222: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7223: --hr_utility.trace('l_pd_project_id.first= ' || TO_CHAR(l_pd_project_id.first));
7224: --hr_utility.trace('l_pd_project_id.last= ' || TO_CHAR(l_pd_project_id.last));
7225: forall z in nvl(l_pd_project_id.first,0)..nvl(l_pd_project_id.last,-1)
7226: insert into pa_fin_vp_pds_view_tmp
7227: (project_id,
7228: task_id,
7291: l_pd_has_child_element(z),
7292: l_pd_project_total(z));
7293: l_err_stage := 600;
7294: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '600: populated the pds_tmp tables', 1);
7295: --hr_utility.trace('populated the pds_temp table: 600');
7296: commit;
7297: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
7298: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_together', '700: leaving procedure', 2);
7299:
7299:
7300: EXCEPTION
7301: when others then
7302: rollback to VIEW_PLANS_POP_TABLES_SAME;
7303: --hr_utility.trace('l_err_stage= ' || to_char(l_err_stage));
7304: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7305: x_msg_count := 1;
7306: x_msg_data := SQLERRM;
7307: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
7517: -- local debugging variables
7518: l_err_stage NUMBER(15);
7519:
7520: begin
7521: --hr_utility.trace_on(null, 'dlai');
7522: l_err_stage := 100;
7523: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '100: entering procedure', 2);
7524: --hr_utility.trace('entered pa_fp_vp_pop_tables_single:100');
7525: x_return_status := FND_API.G_RET_STS_SUCCESS;
7520: begin
7521: --hr_utility.trace_on(null, 'dlai');
7522: l_err_stage := 100;
7523: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '100: entering procedure', 2);
7524: --hr_utility.trace('entered pa_fp_vp_pop_tables_single:100');
7525: x_return_status := FND_API.G_RET_STS_SUCCESS;
7526: x_msg_count := 0;
7527: SAVEPOINT VIEW_PLANS_POP_TABLES_SINGLE;
7528: -- used to query pa_proj_periods_denorm table
7552: po.project_id = p_project_id and
7553: po.fin_plan_option_level_code = 'PLAN_TYPE';
7554: l_err_stage := 200;
7555: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '200: retrieved periodprofileid= ' || l_period_profile_id, 1);
7556: --hr_utility.trace('retrieved periodprofileid: 200');
7557: -- populate AMOUNTS PL/SQL TABLES
7558: if p_cost_or_rev = 'C' then
7559: 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);
7560: open av_cost_csr;
7582: /* ---- populate the PERIODS PL/SQL table with the following rows ---- */
7583: /* ---- COST, REVENUE, MARGIN, MARGIN_PERCENT, QUANTITY ---- */
7584: /* ONLY IF pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' */
7585: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7586: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
7587: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
7588: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
7589: --hr_utility.trace('i= ' || to_char(i));
7590: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
7583: /* ---- COST, REVENUE, MARGIN, MARGIN_PERCENT, QUANTITY ---- */
7584: /* ONLY IF pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' */
7585: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7586: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
7587: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
7588: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
7589: --hr_utility.trace('i= ' || to_char(i));
7590: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
7591: -- process QUANTITY numbers based on report_labor_hrs_from_code
7585: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
7586: --hr_utility.trace('l_project_id.first= ' || to_char(l_project_id.first));
7587: --hr_utility.trace('l_project_id.last= ' || to_char(l_project_id.last));
7588: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
7589: --hr_utility.trace('i= ' || to_char(i));
7590: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
7591: -- process QUANTITY numbers based on report_labor_hrs_from_code
7592: l_row_number := l_row_number + 1; -- increment row counter
7593: l_pd_project_id(l_row_number) := l_project_id(i);
7614: else
7615: l_pd_has_child_element(l_row_number) := 'Y';
7616: end if;
7617: if (l_report_labor_hrs_from_code = 'COST') then
7618: --hr_utility.trace('try to getperiodnvalue');
7619: --hr_utility.trace('raid= ' || to_char(l_pd_res_assignment_id(l_row_number)));
7620: --hr_utility.trace('ppid= ' || to_char(l_period_profile_id));
7621: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7622: (p_period_profile_id => l_period_profile_id,
7615: l_pd_has_child_element(l_row_number) := 'Y';
7616: end if;
7617: if (l_report_labor_hrs_from_code = 'COST') then
7618: --hr_utility.trace('try to getperiodnvalue');
7619: --hr_utility.trace('raid= ' || to_char(l_pd_res_assignment_id(l_row_number)));
7620: --hr_utility.trace('ppid= ' || to_char(l_period_profile_id));
7621: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7622: (p_period_profile_id => l_period_profile_id,
7623: p_budget_version_id => l_pd_budget_version_id(l_row_number),
7616: end if;
7617: if (l_report_labor_hrs_from_code = 'COST') then
7618: --hr_utility.trace('try to getperiodnvalue');
7619: --hr_utility.trace('raid= ' || to_char(l_pd_res_assignment_id(l_row_number)));
7620: --hr_utility.trace('ppid= ' || to_char(l_period_profile_id));
7621: l_pd_period_1(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7622: (p_period_profile_id => l_period_profile_id,
7623: p_budget_version_id => l_pd_budget_version_id(l_row_number),
7624: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
7624: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
7625: p_project_currency_type => l_currency_type,
7626: p_amount_type_id => 215,
7627: p_period_number => 1);
7628: --hr_utility.trace('reached here');
7629: l_pd_period_2(l_row_number) := pa_fp_view_plans_util.get_period_n_value
7630: (p_period_profile_id => l_period_profile_id,
7631: p_budget_version_id => l_pd_budget_version_id(l_row_number),
7632: p_resource_assignment_id => l_pd_res_assignment_id(l_row_number),
8314:
8315: elsif p_cost_or_rev = 'R' then
8316: l_err_stage := 700;
8317: 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);
8318: --hr_utility.trace('this is a REVENUE version: 500');
8319: open av_rev_csr;
8320: fetch av_rev_csr bulk collect into
8321: l_project_id,
8322: l_task_id,
8340: l_err_stage := 800;
8341: /* ---- populate the PERIODS PL/SQL table with the following rows ---- */
8342: /* ---- COST, REVENUE, MARGIN, MARGIN_PERCENT, QUANTITY ---- */
8343: /* ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' */
8344: --hr_utility.trace('number of rows in csr= ' || TO_CHAR(l_project_id.last));
8345: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
8346: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
8347:
8348: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
8345: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
8346: for i in nvl(l_project_id.first,0)..nvl(l_project_id.last,-1) loop
8347:
8348: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_QUANTITY = 'Y' then
8349: --hr_utility.trace('inserting row for Quantity: ' || TO_CHAR(i));
8350: -- process the QUANTITY numbers based on report_labor_hrs_from_code
8351: l_row_number := l_row_number + 1; -- increment row counter
8352: l_pd_project_id(l_row_number) := l_project_id(i);
8353: l_pd_task_id(l_row_number) := l_task_id(i);
8502: end if; -- QUANTITY
8503: end if; -- display_flag: QUANTITY
8504:
8505: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_REVENUE = 'Y' then
8506: --hr_utility.trace('inserting row for Revenue: ' || TO_CHAR(i));
8507: -- process REVENUE numbers from REVENUE BUDGET VERSION
8508: l_row_number := l_row_number + 1; -- increment row counter
8509: l_pd_project_id(l_row_number) := l_project_id(i);
8510: l_pd_task_id(l_row_number) := l_task_id(i);
8638: l_pd_project_total(l_row_number) := l_revenue(i); -- bug 2699651
8639: end if; -- display_from: REVENUE
8640:
8641: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_BURDCOST = 'Y' then
8642: --hr_utility.trace('creating row for burdened cost: ' || TO_CHAR(i));
8643: -- POPULATE null'S for BURDENED_COST row in PERIODS PL/SQL TABLE
8644: l_row_number := l_row_number + 1; -- increment row counter
8645: l_pd_project_id(l_row_number) := l_project_id(i);
8646: l_pd_task_id(l_row_number) := l_task_id(i);
8685: l_pd_project_total(l_row_number) := null; -- bug 2699651
8686: end if; -- display_from: BURDENED COST
8687:
8688: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_RAWCOST = 'Y' then
8689: --hr_utility.trace('inserting row for RawCost: ' || TO_CHAR(i));
8690: -- POPULATE null'S for RAW_COST row in PERIODS PL/SQL TABLE
8691: l_row_number := l_row_number + 1; -- increment row counter
8692: l_pd_project_id(l_row_number) := l_project_id(i);
8693: l_pd_task_id(l_row_number) := l_task_id(i);
8732: l_pd_project_total(l_row_number) := null; -- bug 2699651
8733: end if; -- display_from: RAW COST
8734:
8735: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGIN = 'Y' then
8736: --hr_utility.trace('inserting row for Margin: ' || TO_CHAR(i));
8737: -- POPULATE null'S for MARGIN row in PERIODS PL/SQL TABLE
8738: l_row_number := l_row_number + 1; -- increment row counter
8739: l_pd_project_id(l_row_number) := l_project_id(i);
8740: l_pd_task_id(l_row_number) := l_task_id(i);
8779: l_pd_project_total(l_row_number) := null; -- bug 2699651
8780: end if; -- display_from: MARGIN
8781:
8782: if pa_fp_view_plans_pub.G_DISPLAY_FLAG_MARGINPCT = 'Y' then
8783: --hr_utility.trace('inserting row for MarginPct: ' || TO_CHAR(i));
8784: -- POPULATE null'S for MARGIN_PERCENT row in PERIODS PL/SQL TABLE
8785: l_row_number := l_row_number + 1; -- increment row counter
8786: l_pd_project_id(l_row_number) := l_project_id(i);
8787: l_pd_task_id(l_row_number) := l_task_id(i);
8828: end loop;
8829: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8830: l_err_stage:= 900;
8831: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '600: REVENUE version - FINISHED populating amts pl/sql tables', 1);
8832: --hr_utility.trace('finished populating amts pl/sql tables: 600');
8833:
8834: -- POPULATE global temporary table PA_FIN_VP_AMTS_VIEW_TMP
8835: -- only if pa_fp_view_plans_pub.G_AMT_OR_PD = 'A'
8836: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'A' then
8875: l_unit_of_measure(i),
8876: l_has_child_element(i));
8877: l_err_stage:= 1000;
8878: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '800: amts temp table populated', 1);
8879: --hr_utility.trace('amts temp table populated: 800');
8880: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD= 'A'
8881:
8882: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP
8883: -- only if in periodic mode
8956:
8957:
8958: else
8959: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '700: INVALID VALUE FOR p_cost_or_rev', 4);
8960: --hr_utility.trace('invalid value for p_cost_or_rev');
8961: end if;
8962: commit;
8963: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '1000: exiting procedure', 2);
8964:
8964:
8965: EXCEPTION
8966: when others then
8967: rollback to VIEW_PLANS_POP_TABLES_SINGLE;
8968: --hr_utility.trace('l_err_stage= ' || to_char(l_err_stage));
8969: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8970: x_msg_count := 1;
8971: x_msg_data := SQLERRM;
8972: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',