[Home] [Help]
8: X_MSG_DATA OUT NOCOPY VARCHAR2) IS
9:
10: --Bug 4185180.Given a budget version id as parameter the cursor should bring the PLAN_VERSION, PLAN_TYPE level records for that
11: -- project and budget version.
12: cursor get_elig_bud_ver_csr(c_project_id pa_budget_versions.project_id%TYPE,
13: c_budget_ver_id pa_budget_versions.budget_version_id%type) is
14: select fp.fin_plan_version_id budget_version_id
15: , fp.project_id
16: , fp.proj_fp_options_id
9:
10: --Bug 4185180.Given a budget version id as parameter the cursor should bring the PLAN_VERSION, PLAN_TYPE level records for that
11: -- project and budget version.
12: cursor get_elig_bud_ver_csr(c_project_id pa_budget_versions.project_id%TYPE,
13: c_budget_ver_id pa_budget_versions.budget_version_id%type) is
14: select fp.fin_plan_version_id budget_version_id
15: , fp.project_id
16: , fp.proj_fp_options_id
17: , nvl(pa.org_id,-99) org_id
22: --Bug 4046524
23: , pa.project_currency_code
24: , pa.projfunc_currency_code
25: from pa_proj_fp_options fp, pa_projects_all pa,
26: pa_budget_versions pbv
27: where fp.project_id = pa.project_id
28: and fp.project_id = c_project_id
29: and pbv.budget_version_id=c_budget_ver_id
30: and (fp.fin_plan_version_id = c_budget_ver_id OR
36: 'COST_AND_REV_SEP',decode(cost_time_phased_code,
37: 'R',cost_time_phased_code,
38: revenue_time_phased_code)) = 'R';
39:
40: cursor chk_plan_ver_csr(c_budget_ver_id pa_budget_versions.budget_version_id%type) is
41: select project_id,'Y' from pa_proj_fp_options fp
42: where fin_plan_version_id = c_budget_ver_id and
43: fin_plan_option_level_code like 'PLAN_VERSION';
44:
50: where nvl(pi.org_id,-99) = c_org_id
51: and sob.set_of_books_id = pi.set_of_books_id;
52:
53: --Bug 4176129. Removed the UNION clause as at any point only one of the 2 SQLs would get executed
54: cursor chk_ra_exists_csr(c_budget_version_id pa_budget_versions.budget_version_id%type,
55: c_period_type gl_date_period_map.period_type%type,
56: c_period_set_name gl_sets_of_books.period_set_name%type) is
57: select 'Y' from dual
58: where exists (select 'Y' from pa_resource_assignments ra
69: )));
70:
71:
72:
73: cursor chk_ra_for_bl_exists_csr(c_budget_version_id pa_budget_versions.budget_version_id%type) is
74: select 'Y' from dual
75: where exists (select 'Y' from pa_budget_lines bl
76: where bl.budget_version_id = c_budget_version_id
77: group by resource_assignment_id
96: -- Make query similar to get_budget_lines_csr, which explodes date-range into PA/GL periodic data.
97:
98:
99: cursor chk_pa_gl_per_exists_csr(c_period_type pa_implementations_all.pa_period_type%type
100: ,c_budget_version_id pa_budget_versions.budget_version_id%type
101: ,c_period_set_name gl_sets_of_books.period_set_name%type) is
102: select 'Y' from dual
103: where exists ( select 'Y'
104: from pa_budget_lines bl
112: or (gl.start_date > bl.start_date and gl.end_date < bl.end_date)));
113:
114: --Bug 3988010. Removed the NVL so as not to upgrade NULL to 0
115:
116: cursor get_non_time_multi_csr(c_budget_version_id pa_budget_versions.budget_version_id%type) is
117: select min(start_date) min_date ,max(end_date) max_date,
118: sum(quantity) sum_quantity,
119: sum(raw_cost) sum_raw_cost,
120: sum(burdened_cost) sum_burdened_cost,
241: TYPE budget_lines_tbl is table of get_budget_lines_csr%ROWTYPE
242: index by binary_integer;
243: l_get_budget_lines_tbl budget_lines_tbl;
244:
245: l_project_id pa_budget_versions.project_id%TYPE;
246: l_one_ra_exists varchar2(1) := 'N';
247: l_one_ra_for_bl_exists varchar2(1) := 'N';
248: l_pa_per_exists varchar2(1) := 'N';
249: l_gl_per_exists varchar2(1) := 'N';
246: l_one_ra_exists varchar2(1) := 'N';
247: l_one_ra_for_bl_exists varchar2(1) := 'N';
248: l_pa_per_exists varchar2(1) := 'N';
249: l_gl_per_exists varchar2(1) := 'N';
250: l_budget_version_id pa_budget_versions.budget_version_id%type;
251: l_time_phased_mode varchar2(1) := null;
252: l_pa_period_type pa_implementations_all.pa_period_type%type;
253: l_per_type pa_implementations_all.pa_period_type%type;
254: l_accounted_per_type pa_implementations_all.pa_period_type%type;
286: l_project_start_date pa_projects_all.start_date%TYPE := NULL;
287: l_org_id pa_projects_all.org_id%TYPE := NULL;
288:
289: l_period_mask_id pa_period_masks_b.period_mask_id%type;
290: l_curr_plan_period pa_budget_versions.current_planning_period%type;
291: l_cost_current_planning_period pa_proj_fp_options.cost_current_planning_period%type;
292: l_cost_period_mask_id pa_proj_fp_options.cost_period_mask_id%type;
293: l_rev_current_planning_period pa_proj_fp_options.rev_current_planning_period%type;
294: l_rev_period_mask_id pa_proj_fp_options.rev_period_mask_id%type;
699: /* Including this IF so that we can avoid an update (though it would do nothing) in case
700: of project/plan type level record */
701: IF l_get_elig_bud_ver_csr.fin_plan_option_level_code = 'PLAN_VERSION' THEN
702:
703: update pa_budget_versions
704: SET current_planning_period = l_curr_plan_period,
705: period_mask_id = l_period_mask_id
706: where budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
707:
1656: --'N' if the budget version has to upgraded to None Time Phase
1657: --This function will be called from the upgrade script paupg102.sql and PADTRNGB.DATE_RANGE_UPGRD.No validations are done
1658: --in this API and the calling APIs should take care of passing correct values.
1659: FUNCTION get_time_phase_mode
1660: (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
1661: ,p_pa_period_type IN pa_implementations_all.pa_period_type%TYPE
1662: ,p_gl_period_type IN gl_sets_of_books.accounted_period_type%TYPE
1663: ,p_org_id IN pa_projects_all.org_id%TYPE) RETURN VARCHAR2
1664: IS