1 PACKAGE Pa_Fp_Control_Items_Utils AUTHID CURRENT_USER AS
2 /* $Header: PAFPCIUS.pls 120.4 2009/08/13 11:34:46 kkorada ship $ */
3
4 --Merge exception
5 RAISE_MERGE_ERROR EXCEPTION;
6 PRAGMA EXCEPTION_INIT(RAISE_MERGE_ERROR, -501);
7
8 PROCEDURE Get_Fin_Plan_Dtls(p_project_id IN Pa_Projects_All.Project_Id%TYPE,
9 p_ci_id IN NUMBER,
10 x_fin_plan_type_id_cost OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
11 x_fin_plan_type_id_rev OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
12 x_fin_plan_type_id_all OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
13 x_fp_type_id_margin_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
14 x_margin_derived_from_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
15 x_report_labor_hours_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 x_fp_pref_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
17 x_project_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
18 x_baseline_funding_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
19 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
20 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
21 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
22 x_ci_type_class_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
23 x_no_of_ci_plan_versions OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
24 x_ci_est_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25 x_ci_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
26 x_baselined_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
27 x_ci_ver_plan_prc_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
28 x_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE); --File.Sql.39 bug 4440895
29
30 FUNCTION Is_Financial_Planning_Allowed(p_project_id NUMBER) RETURN VARCHAR2;
31
32 PROCEDURE get_finplan_ci_type_name
33 (
34 p_ci_id IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
35 x_ci_type_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
36 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
37 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
39
40 ) ;
41
42 PROCEDURE get_fp_ci_agreement_dtls
43 (
44 p_project_id IN NUMBER,
45 p_ci_id IN NUMBER,
46 x_agreement_num OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
47 x_agreement_amount OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
48 x_agreement_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
49 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
50 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
51 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
52 ) ;
53
54 PROCEDURE FP_CI_GET_VERSION_DETAILS
55 (
56 p_project_id IN NUMBER,
57 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
58 x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
59 x_multi_curr_flag OUT NOCOPY pa_proj_fp_options.plan_in_multi_curr_flag%TYPE, --File.Sql.39 bug 4440895
60 x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.all_fin_plan_level_code%TYPE, --File.Sql.39 bug 4440895
61 x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
62 x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
63 x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
64 x_group_res_type_id OUT NOCOPY pa_resource_lists_all_bg.group_resource_type_id%TYPE, --File.Sql.39 bug 4440895
65 x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE, --File.Sql.39 bug 4440895
66 x_ci_id OUT NOCOPY pa_budget_versions.ci_id%TYPE, --File.Sql.39 bug 4440895
67 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
68 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
69 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
70 ) ;
71
72 PROCEDURE FP_CI_CHECK_MERGE_POSSIBLE
73 (
74 p_project_id IN NUMBER,
75 p_source_fp_version_id_tbl IN SYSTEM.pa_num_tbl_type,
76 p_target_fp_version_id IN NUMBER,
77 p_calling_mode IN VARCHAR2,
78 x_merge_possible_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type,
79 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
80 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
81 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
82 );
83
84 PROCEDURE isFundingLevelChangeAllowed
85 (
86 p_project_id IN NUMBER,
87 p_proposed_fund_level IN VARCHAR2 DEFAULT NULL,
88 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
89 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
90 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
91
92 PROCEDURE isAgreementDeletionAllowed
93 (
94 p_agreement_id IN NUMBER,
95 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
96 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
97 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
98
99 PROCEDURE isAgrCurrencyChangeAllowed
100 (
101 p_agreement_id IN NUMBER,
102 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
103 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
104 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
105
106 PROCEDURE Is_Create_CI_Version_Allowed
107 ( p_project_id IN pa_budget_versions.project_id%TYPE
108 ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
109 ,p_version_type IN pa_budget_versions.version_type%TYPE
110 ,p_impacted_task_id IN pa_tasks.task_id%TYPE
111 ,x_version_allowed_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
112 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
113 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
114 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
115
116 PROCEDURE IsValidAgreement(
117 p_project_id IN NUMBER,
118 p_agreement_number IN VARCHAR2,
119 x_agreement_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
120 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
121 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
122 x_return_status OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
123
124 FUNCTION IsFpAutoBaselineEnabled(p_project_id IN NUMBER)
125 RETURN VARCHAR2;
126
127 PROCEDURE GET_BUDGET_VERSION_INFO
128 (
129 p_project_id IN NUMBER,
130 p_budget_version_id IN NUMBER,
131 x_version_number OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
132 x_version_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
133 x_version_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
134 x_project_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
135 x_approved_cost_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
136 x_approved_rev_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
137 x_fin_plan_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
138 x_plan_type_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
139 x_plan_class_code OUT NOCOPY VARCHAR2, -- added for FPM --File.Sql.39 bug 4440895
140 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
141 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
142 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
143 ) ;
144
145 FUNCTION GET_FUNINDG_AMOUNT(
146 p_project_id IN pa_projects_all.project_id%TYPE,
147 p_agreement_id IN pa_agreements_all.agreement_id%TYPE)
148 RETURN NUMBER;
149
150 PROCEDURE CHK_APRV_CUR_WORKING_BV_EXISTS
151 ( p_project_id IN pa_projects_all.project_id%TYPE
152 ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
153 ,p_version_type IN pa_budget_versions.version_type%TYPE
154 ,x_cur_work_bv_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
155 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
156 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
157 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
158
159 PROCEDURE COMPARE_SOURCE_TARGET_VER_ATTR
160 ( p_source_bv_id IN pa_budget_versions.budget_version_id%TYPE
161 ,p_target_bv_id IN pa_budget_versions.budget_version_id%TYPE
162 ,x_attributes_same_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
163 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
164 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
165 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
166
167 PROCEDURE CHECK_PLAN_VERSION_NAME_OR_ID
168 (
169 p_project_id IN NUMBER,
170 p_budget_version_name IN VARCHAR2,
171 p_fin_plan_type_id IN NUMBER,
172 p_version_type IN VARCHAR2,
173 x_no_of_bv_versions OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
174 x_budget_version_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
175 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
176 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
177 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
178 ) ;
179
180 PROCEDURE FP_CI_CHECK_COPY_POSSIBLE
181 (
182 p_source_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE
183 ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
184 ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
185 ,p_source_version_type IN pa_budget_versions.version_type%TYPE
186 ,p_project_id IN pa_budget_versions.project_id%TYPE
187 ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
188 ,p_multiple_plan_types_flag IN VARCHAR2 DEFAULT 'N'
189 ,x_copy_possible_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
190 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
191 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
192 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
193 ) ;
194
195 PROCEDURE CHECK_FP_PLAN_VERSION_EXISTS
196 (
197 p_project_id IN NUMBER,
198 p_ci_id IN VARCHAR2,
199 x_call_fp_api_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
200 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
201 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
202 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
203 ) ;
204
205 /* Added the following procedure for bug #2651851. This procedure is used for Review
206 and Submit of Control Items. */
207
208 PROCEDURE FP_CI_IMPACT_SUBMIT_CHK
209 ( p_project_id IN pa_budget_versions.project_id%TYPE
210 ,p_ci_id IN pa_budget_versions.ci_id%TYPE
211 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
212 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
213 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
214
215 /*
216 Bug # 2681589 - This API returns Y/N in x_update_impact_allowed depending on
217 whether we can update the impact as IMPLEMENTED or not.
218 */
219 --Bug 3550073. Included x_upd_cost_impact_allowed and x_upd_rev_impact_allowed
220 PROCEDURE FP_CI_VALIDATE_UPDATE_IMPACT
221 (
222 p_project_id IN pa_budget_versions.project_id%TYPE,
223 p_ci_id IN pa_control_items.ci_id%TYPE,
224 p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
228 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
225 p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
226 x_upd_cost_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
227 x_upd_rev_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
229 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
233 procedure chk_res_resgrp_mismatch(
230 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
231 );
232
234 p_project_id in number,
235 p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
236 p_s_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
237 p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
238 p_t_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
239 p_calling_mode in varchar2,
240 x_res_resgr_mismatch_flag OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
241 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
242 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
243 x_return_status OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
244
245 procedure chk_tsk_plan_level_mismatch(
246 p_project_id in number,
247 p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
248 p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
249 p_calling_mode in varchar2,
250 x_tsk_plan_level_mismatch OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
251 x_s_task_id_tbl OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
252 x_t_task_id_tbl OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
253 x_s_fin_plan_level_tbl OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
254 x_t_fin_plan_level_tbl OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
255 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
256 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
257 x_return_status OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
258
259 /* dbora - FP M New functions to check for CI
260 smullapp-Changed NON_APP_STATUSES_EXIST to add p_fin_plan_type_id as input parameter(bug 3899756)
261 */
262 FUNCTION NON_APP_STATUSES_EXIST (
263 p_ci_type_id IN pa_pt_co_impl_statuses.ci_type_id%TYPE,
264 p_version_type IN pa_pt_co_impl_statuses.version_type%TYPE,
265 p_fin_plan_type_id IN pa_pt_co_impl_statuses.fin_plan_type_id%TYPE)
266 RETURN VARCHAR2;
267
268 FUNCTION GET_CI_ALLOWED_IMPACTS (
269 p_ci_type_id IN pa_pt_co_impl_statuses.ci_type_id%TYPE)
270 RETURN VARCHAR2;
271
272 -- This procedure will reutrn data for populating Plan Summary Region
273 PROCEDURE get_summary_data
274 ( p_project_id IN NUMBER
275 ,p_cost_version_id IN pa_budget_versions.budget_version_id%TYPE DEFAULT NULL
276 ,p_revenue_version_id IN pa_budget_versions.budget_version_id%TYPE DEFAULT NULL
277 ,p_page_context IN VARCHAR2
278 ,p_calling_mode IN VARCHAR2 DEFAULT 'APPROVED' --Bug 5278200 kchaitan
279 ,x_context OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
280 ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
281 ,x_url_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_240_TBL_TYPE --File.Sql.39 bug 4440895
282 ,x_reference_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE --File.Sql.39 bug 4440895
283 ,x_equipment_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
284 ,x_labor_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
285 ,x_cost_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
286 ,x_revenue_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
287 ,x_margin_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
288 ,x_margin_percent_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
289 ,x_project_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
290 ,x_report_labor_hrs_code OUT NOCOPY VARCHAR2 /* Bug 4038253 */ --File.Sql.39 bug 4440895
291 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
292 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
293 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
294 );
295
296 -- This procedure will return the revenue amount,
297 FUNCTION get_labor_qty_partial(
298 p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
299 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
300 p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
301 p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
302 p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
303 )
304 RETURN NUMBER;
305
306 FUNCTION get_equip_qty_partial(
307 p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
308 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
309 p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
310 p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
311 p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
312 )
313 RETURN NUMBER;
314
315 FUNCTION get_pc_revenue_partial (
319 p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
316 p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
317 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
318 p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
320 p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
321 )
322 RETURN NUMBER;
323
324 FUNCTION get_pc_cost (
325 p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
326 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
327 p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
328 p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL ,
329 p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
330 p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
331 )
332 RETURN NUMBER;
333
334 PROCEDURE get_not_included
335 ( p_project_id IN NUMBER
336 ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
337 ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
338 ,p_version_type IN pa_budget_versions.version_type%TYPE
339 ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
340 ,x_equipment_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
341 ,x_labor_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
342 ,x_cost_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
343 ,x_revenue_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
344 ,x_margin_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
345 ,x_margin_percent_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
346 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
347 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
348 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
349 );
350
351 /* FP.M -dbora */
352 FUNCTION is_impact_exists(p_ci_id IN pa_ci_impacts.ci_id%TYPE)
353 RETURN VARCHAR2;
354
355 FUNCTION is_fin_impact_enabled(p_ci_id IN pa_control_items.ci_id%TYPE,
356 p_project_id IN pa_projects_all.project_id%TYPE)
357 RETURN VARCHAR2;
358
359 /* Returns the plan types into which a change order can be implemented along with other information */
360 -- Added New Params for Quantity in GET_PLAN_TYPES_FOR_IMPL - Bug 3902176
361 PROCEDURE GET_PLAN_TYPES_FOR_IMPL
362 (P_ci_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE, -- Id of the Change Document
363 P_project_id IN Pa_budget_versions.project_id%TYPE, -- Id of the Project
364 X_pt_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for fin plan type ids --File.Sql.39 bug 4440895
365 X_pt_name_tbl OUT NOCOPY SYSTEM.pa_varchar2_150_tbl_type, -- Plsql table for fin plan type names --File.Sql.39 bug 4440895
366 x_cost_impact_impl_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, --File.Sql.39 bug 4440895
367 x_rev_impact_impl_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, --File.Sql.39 bug 4440895
368 X_cost_impl_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- Plsql table for Implement Cost Flag --File.Sql.39 bug 4440895
369 x_rev_impl_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- Plsql table for Implement Rev Flag --File.Sql.39 bug 4440895
370 X_raw_cost_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for raw cost --File.Sql.39 bug 4440895
371 X_burd_cost_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for burdened cost --File.Sql.39 bug 4440895
372 X_revenue_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for revenue --File.Sql.39 bug 4440895
373 X_labor_hrs_c_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for labor hrs -Cost --File.Sql.39 bug 4440895
374 X_equipment_hrs_c_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql tabe for equipment hrs -Cost --File.Sql.39 bug 4440895
375 X_labor_hrs_r_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for labor hrs -Rev --File.Sql.39 bug 4440895
376 X_equipment_hrs_r_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql tabe for equipment hrs -Rev --File.Sql.39 bug 4440895
377 X_margin_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for margin --File.Sql.39 bug 4440895
378 X_margin_percent_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for margin percent --File.Sql.39 bug 4440895
379 X_margin_derived_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type, -- Plsql table for Margin Derived From Code - Bug 3734840 --File.Sql.39 bug 4440895
380 x_approved_fin_pt_id OUT NOCOPY Pa_fin_plan_types_b.fin_plan_type_id%TYPE, -- Contains the ID of the approved plan type --File.Sql.39 bug 4440895
381 X_cost_bv_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for cost bv id --File.Sql.39 bug 4440895
385 X_agreement_num OUT NOCOPY Pa_agreements_all.agreement_num%TYPE, -- Agreement number of the agreement --File.Sql.39 bug 4440895
382 X_rev_bv_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for revenue bv id --File.Sql.39 bug 4440895
383 X_all_bv_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for all bv id --File.Sql.39 bug 4440895
384 X_select_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- The flag which indicates whether the select flag can be checked by default or not --File.Sql.39 bug 4440895
386 X_partially_impl_flag OUT NOCOPY VARCHAR2, -- A flag that indicates whether a partially implemented CO exists for the plan type or not. Possible values are Y/N --File.Sql.39 bug 4440895
387 X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
388 X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
389 X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
390 x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
391 x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
392 x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
393 X_autobaseline_project OUT NOCOPY VARCHAR2, -- This flag will be set to Y if the project is enabled for autobaseline --File.Sql.39 bug 4440895
394 X_disable_baseline_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- Plsql table for Disable Baseline Checkbox Flag --File.Sql.39 bug 4440895
395 x_return_status OUT NOCOPY VARCHAR2, -- Indicates the exit status of the API --File.Sql.39 bug 4440895
396 x_msg_data OUT NOCOPY VARCHAR2, -- Indicates the error occurred --File.Sql.39 bug 4440895
397 X_msg_count OUT NOCOPY NUMBER); -- Indicates the number of error messages --File.Sql.39 bug 4440895
398
399
400 /* Returns the Ids of the versions created for this Change Document. The ID will be -1 if the version can never be there */
401
402 PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
403 ,X_cost_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the cost version associated with the CI --File.Sql.39 bug 4440895
404 ,X_rev_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the revenue version associated with the CI --File.Sql.39 bug 4440895
405 ,X_all_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the all version associated with the CI --File.Sql.39 bug 4440895
406 ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
407 ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
408 ,X_msg_count OUT NOCOPY NUMBER); -- Indicates the number of error messages --File.Sql.39 bug 4440895
409
410 PROCEDURE GET_IMPL_DETAILS(P_fin_plan_type_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE -- Id of the plan type
411 ,P_project_id IN Pa_budget_versions.project_id%TYPE -- Id of the Project
412 ,P_app_rev_plan_type_flag IN pa_budget_versions.approved_rev_plan_type_flag%TYPE DEFAULT NULL -- Indicates whether the plan type passed is approved rev_plan_type or not. If the value is NULL the value will be derived
413 ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
414 ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
415 ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
416 ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
417 ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
418 ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
419 ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
420 ,x_cost_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the cost impact can be implemented --File.Sql.39 bug 4440895
421 ,x_rev_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the rev impact can be implemented --File.Sql.39 bug 4440895
422 ,X_cost_impact_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the impact is completely implemented --File.Sql.39 bug 4440895
423 ,x_rev_impact_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
424 ,X_partially_impl_flag OUT NOCOPY VARCHAR2 -- Can be Y or N. Indicates whether a CI is partially implemented . --File.Sql.39 bug 4440895
428 ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
425 ,x_agreement_num OUT NOCOPY pa_agreements_all.agreement_num%TYPE --File.Sql.39 bug 4440895
426 ,x_approved_fin_pt_id OUT NOCOPY Pa_fin_plan_types_b.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
427 ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
429 ,X_msg_count OUT NOCOPY NUMBER); -- Indicates the number of error messages --File.Sql.39 bug 4440895
430
431 --This function returns either Y or N. If the user status code passed exists in pa_pt_co_impl_statuses, meaning
432 --that there exists a ci type whose change documents can be implemented/included into the working versions of a
433 --plan type, then Y is returned. N is returned otherwise
434 FUNCTION is_user_status_implementable(p_status_code IN pa_control_items.status_code%TYPE)
435 RETURN VARCHAR2 ;
436
437 --This procedure will return the approved cost/rev current working version ids for a project.
438 --If there is only one version which is approved for both cost and revenue and the same version id will be
439 --populated in both x_app_cost_cw_ver_id and x_app_rev_cw_ver_id
440 --If the current working versions do not exist then null will be returned
441 PROCEDURE get_app_cw_ver_ids_for_proj
442 (p_project_id IN pa_projects_all.project_id%TYPE,
443 x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
444 x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
445 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
446 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
447 x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
448
449 --This API will be called from the View Financial Impact page. This API will return the details required for
450 --that page
451 --p_budget_version_id is the target version id with which comparision happens in the view fin impact page. If this
452 --is available its not required to fetch the approved cost/rev current working ids.
453 PROCEDURE get_dtls_for_view_fin_imp_pg
454 (p_project_id IN pa_projects_all.project_id%TYPE,
455 p_ci_id IN pa_control_items.ci_id%TYPE,
456 p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
457 p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
458 p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
459 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
460 x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
461 x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
462 x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
463 x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
464 x_impact_in_mc_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
465 x_targ_version_type OUT NOCOPY pa_budget_Versions.version_type%TYPE, --File.Sql.39 bug 4440895
466 x_show_resources_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
467 x_plan_class_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
468 x_report_cost_using OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
469 x_cost_impl_into_app_cw_ver OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
470 x_rev_impl_into_app_cw_ver OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
471 x_ci_type OUT NOCOPY pa_ci_types_vl.name%TYPE, --File.Sql.39 bug 4440895
472 x_ci_number OUT NOCOPY pa_control_items.ci_number%TYPE, --File.Sql.39 bug 4440895
473 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
474 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
475 x_return_status OUT NOCOPY VARCHAR2) ; --File.Sql.39 bug 4440895
476
477 /* Bug 3731948- New Function to return the CO amount already implemented
478 * for REVENUE implementation in agreement currency
479 */
480 FUNCTION get_impl_agr_revenue (p_project_id IN pa_projects_all.project_id%TYPE,
481 p_ci_id IN pa_fp_merged_ctrl_items.ci_id%TYPE)
482 RETURN NUMBER;
483
484 FUNCTION is_edit_plan_enabled(p_ci_id IN pa_ci_impacts.ci_id%TYPE)
485 RETURN VARCHAR2;
486
487 /* Function returns 'Y' if the change order has been implemented/included into ANY budget version. */
488 FUNCTION has_co_been_merged(p_ci_id IN pa_ci_impacts.ci_id%TYPE)
489 RETURN VARCHAR2;
490
491 /* This API returns the txn_currency_code and the ci version id of the budget lines of a REVENUE or ALL ci version, if lines exist. Else it returns NULL
492 All the lines of a revenue change order version will be in a single currency
493 */
494 PROCEDURE get_txn_curr_code_of_ci_ver(
495 p_project_id IN pa_projects_all.project_id%TYPE
496 ,p_ci_id IN pa_budget_versions.ci_id%TYPE
497 ,x_txn_currency_code OUT NOCOPY pa_budget_lines.txn_currency_code%TYPE --File.Sql.39 bug 4440895
498 ,x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
499 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
500 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
501 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
502 ) ;
503
507 * for financial impact implementation
504 /* Bug 3927208: DBORA- The following function is to be used by Control Item team, before
505 * deleting any CI type from the system, to check if the ci type is being used
506 * in any financial plan type context to define implementation/inclusion statuses
508 */
509 FUNCTION validate_fp_ci_type_delete (p_ci_type_id IN pa_ci_types_b.ci_type_id%TYPE)
510 RETURN VARCHAR2;
511
512 -- Bug 5845142
513 FUNCTION check_valid_combo
514 (p_project_id IN NUMBER,
515 p_targ_app_cost_flag IN VARCHAR2,
516 p_targ_app_rev_flag IN VARCHAR2)
517 RETURN VARCHAR2;
518
519 end Pa_Fp_Control_Items_Utils;
[Home] [Help]