[Home] [Help]
2729: l_msg_count NUMBER;
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;
2984:
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
3008:
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
3031: x_msg_count => l_msg_count,
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
3059: -- the other one will also have preference_code = 'COST_AND_REV_SEP', and we will
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
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);
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;
3079: end if; -- l_compl_crsep_c_w_csr: no data found
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
3115: (p_period_profile_id1 => l_period_profile_id1,
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
3145: x_msg_count => l_msg_count,
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';
3194: end if;
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
3208: end if; --l_compl_crsep_r_w_csr: no data found
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
3244: (p_period_profile_id1 => l_period_profile_id1,
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
3274: x_msg_count => l_msg_count,
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,
3326: else
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
3340: end if; -- l_compl_crsep_c_b_csr: no data found
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
3376: (p_period_profile_id1 => l_period_profile_id1,
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
3406: x_msg_count => l_msg_count,
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,
3454: end if; -- l_compl_budget_version_id is null
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
3468: end if; -- l_compl_crsep_r_b_csr: no data found
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
3504: (p_period_profile_id1 => l_period_profile_id1,
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
3534: x_msg_count => l_msg_count,
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,
3600: x_msg_count := 1;
3601: x_msg_data := SQLERRM;
3602: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
3603: p_procedure_name => 'view_plan_temp_tables');
3604: pa_debug.reset_err_stack;
3605: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3606:
3607: end; -- procedure view_plan_temp_tables
3608: /* --------------------------------------------------------------------- */
3824: -- local debugging variables
3825: l_err_stage NUMBER(15);
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;
3862: bv.fin_plan_type_id = po.fin_plan_type_id and
3863: po.project_id = p_project_id and
3864: po.fin_plan_option_level_code = 'PLAN_TYPE';
3865: l_err_stage := 300;
3866: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '200: periodprofileid= ' || l_period_profile_id, 1);
3867: -- insert all cost rows into the COST PL/SQL table
3868: open av_cost_csr;
3869: fetch av_cost_csr bulk collect into
3870: l_c_project_id,
3911: l_r_parent_element_name,
3912: l_r_unit_of_measure;
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;
5516: end if; -- display_flag: MARGIN PERCENT
5517: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
5518: end if; -- not found_complement
5519: end loop; -- COST PL/SQL loop
5520: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '400: iterated through cost pl/sql table', 1);
5521: l_err_stage := 600;
5522: -- after looping through the COST PL/SQL table, iterate through the REVENUE PL/SQL
5523: -- table, and copy over the untouched rows for PERIOD PL/SQL table
5524: for k in nvl(l_r_project_id.first,0)..nvl(l_r_project_id.last,-1) loop
6042: end if; -- display_flag: MARGIN PERCENT
6043: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
6044: end loop; -- REVENUE PL/SQL loop: the untouched rows
6045: l_err_stage := 700;
6046: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '500: iterated through rev pl/sql table', 1);
6047:
6048: /* TRANSFER DATA FROM PL/SQL TABLES TO GLOBAL TEMPORARY TABLES */
6049:
6050: -- POPULATE global temporary table PA_FIN_VP_AMTS_VIEW_TMP from the
6092: -1,
6093: l_c_unit_of_measure(x),
6094: l_c_has_child_element(x));
6095: l_err_stage := 800;
6096: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '600: transfer from cost pl/sql table to amts_tmp table', 1);
6097: -- rows in the REVENUE PL/SQL table
6098: forall y in nvl(l_r_project_id.first,0)..nvl(l_r_project_id.last,-1)
6099: insert into pa_fin_vp_amts_view_tmp
6100: (project_id,
6136: l_r_unit_of_measure(y),
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
6213: l_pd_has_child_element(z),
6214: l_pd_project_total(z));
6215: end if; --pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
6216: l_err_stage:= 1000;
6217: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '700: transfer from pds pl/sql table to pds_tmp table', 1);
6218: commit;
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
6215: end if; --pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
6216: l_err_stage:= 1000;
6217: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_separate', '700: transfer from pds pl/sql table to pds_tmp table', 1);
6218: commit;
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));
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',
6228: p_procedure_name => 'pa_fp_vp_pop_tables_separate');
6229: pa_debug.reset_err_stack;
6230: return;
6231: end pa_fp_vp_pop_tables_separate;
6232: /* ------------------------------------------------------------------ */
6233:
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;
6411: x_msg_count := 0;
6412: SAVEPOINT VIEW_PLANS_POP_TABLES_SAME;
6437: bv.fin_plan_type_id = po.fin_plan_type_id and
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
6462: l_unit_of_measure,
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
7162: l_pd_project_total(l_row_number) := pa_fp_view_plans_util.calc_margin_percent(l_pd_project_total(l_cost_row_number),l_pd_project_total(l_revenue_row_number)); -- bug 2699651
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
7210: l_res_assignment_id(i),
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:
7290: l_pd_succeeding(z),
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);
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:
7300: EXCEPTION
7301: when others then
7302: rollback to VIEW_PLANS_POP_TABLES_SAME;
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',
7308: p_procedure_name => 'View_Plans_Pop_Tables_Tog');
7309: pa_debug.reset_err_stack;
7310: return;
7311: end pa_fp_vp_pop_tables_together;
7312: /* ------------------------------------------------------------- */
7313:
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;
7526: x_msg_count := 0;
7527: SAVEPOINT VIEW_PLANS_POP_TABLES_SINGLE;
7551: bv.fin_plan_type_id = po.fin_plan_type_id and
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);
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;
7561: fetch av_cost_csr bulk collect into
7562: l_project_id,
7563: l_task_id,
8186: end if; --display_from: MARGIN PERCENT
8187: end loop;
8188: end if; --pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8189: l_err_stage := 400;
8190: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '400: COST version - FINISHED populating amts pl/sql tables', 1);
8191:
8192: -- POPULATE global temporary table PA_FIN_VP_AMTS_VIEW_TMP
8193: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'A'
8194: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'A' then
8233: l_unit_of_measure(i),
8234: l_has_child_element(i));
8235: end if; --pa_fp_view_plans_pub.G_AMT_OR_PD= 'A'
8236: l_err_stage := 500;
8237: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '800: amts temp table populated', 1);
8238:
8239: -- POPULATE global temporary table PA_FIN_VP_PDS_VIEW_TMP
8240: -- ONLY if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8241: if pa_fp_view_plans_pub.G_AMT_OR_PD = 'P' then
8308: l_pd_has_child_element(z),
8309: l_pd_project_total(z));
8310: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8311: l_err_stage := 600;
8312: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '900: pds temp table populated', 1);
8313:
8314:
8315: elsif p_cost_or_rev = 'R' then
8316: l_err_stage := 700;
8313:
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,
8827: end if; --display_from: MARGIN PERCENT
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'
8874: -1,
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
8951: l_pd_has_child_element(z),
8952: l_pd_project_total(z));
8953: end if; -- pa_fp_view_plans_pub.G_AMT_OR_PD = 'P'
8954: l_err_stage := 1100;
8955: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '900: pds temp table populated', 1);
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);
8955: pa_debug.write('pa_fp_view_plans_pub.pa_fp_vp_pop_tables_single', '900: pds temp table populated', 1);
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);
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:
8965: EXCEPTION
8966: when others then
8967: rollback to VIEW_PLANS_POP_TABLES_SINGLE;
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',
8973: p_procedure_name => 'View_Plans_Pop_Tables_Single');
8974: pa_debug.reset_err_stack;
8975: return;
8976: end pa_fp_vp_pop_tables_single;
8977:
8978: