1 PACKAGE PA_FIN_PLAN_UTILS2 AS
2 /* $Header: PAFPUT2S.pls 120.5 2007/02/06 10:13:58 dthakker ship $ */
3
4 /* Added these variables for Billable Task API */
5 TYPE BillableRec IS RECORD (Billable_Flag Pa_tasks.billable_flag%TYPE);
6 TYPE BillableTab IS TABLE OF BillableRec INDEX BY BINARY_INTEGER;
7
8 /* The following start date and end date variabes and APIs will be used in the
9 * view pa_fp_budget_line_rejections_v to derive the rejection flags based on
10 * given start and end dates
11 */
12 g_bdgt_start_date Date;
13 g_bdgt_end_date Date;
14 period_mask_display Varchar2(100);
15 FUNCTION get_bdgt_start_date Return DATE ;
16 FUNCTION get_bdgt_end_date Return DATE ;
17
18 /* This API will derive the rate based flag and UOM for the planning transaction
19 * This should be called while updating the planning resource transaction
20 * Based on the IN params the new rate based flag and UOM will be derived
21 * If old and new rate base flag values are different then x_rate_based_flag_changed_tab will be set to 'Y'
22 * If old and new UOM values are different then x_uom_changed_flag_tab will be set to 'Y'
23 * NOTE: Since this is PLSQL table LOOPING , this api should be called in batch of 100 records only
24 */
25 PROCEDURE Get_UOM_RateBasedFlag (
26 p_resource_class_code_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
27 ,p_inventory_item_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
28 ,p_rate_organization_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
29 ,p_expenditure_type_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
30 ,p_rate_expenditure_type_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
31 ,p_old_UOM_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
32 ,p_old_rate_based_flag_tab IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE
33 ,x_New_UOM_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
34 ,x_uom_changed_flag_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE
35 ,x_new_rate_based_flag_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE
36 ,x_rate_based_flag_changed_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE
37 ,x_return_status OUT NOCOPY VARCHAR2
38 ) ;
39
40 /* This API derives the rejection code flags from budget lines for the given
41 * resource_assignment_id and txn_currency_code
42 * The out variables will be set to 'Y' if there is any rejection else it is 'N'
43 * If p_start_date and p_end_date is passed then out rejection flags will be based on the Periods
44 */
45 PROCEDURE Get_BdgtLineRejFlags
46 (p_resource_assignment_id IN Number
47 ,p_txn_currency_code IN Varchar2
48 ,p_budget_version_id IN Number
49 ,p_start_date IN Date default Null
50 ,p_end_date IN Date default Null
51 ,x_cost_rejection_flag OUT NOCOPY Varchar2
52 ,x_burden_rejection_flag OUT NOCOPY Varchar2
53 ,x_revenue_rejection_flag OUT NOCOPY Varchar2
54 ,x_pc_conv_rejection_flag OUT NOCOPY Varchar2
55 ,x_pfc_conv_rejection_flag OUT NOCOPY Varchar2
56 ,x_other_rejection_flag OUT NOCOPY Varchar2
57 ,x_return_status OUT NOCOPY Varchar2
58 );
59
60
61 /* This API derives the rejection reason from budget lines for the given
62 * resource_assignment_id and txn_currency_code,Start_date, end_date
63 * The out variable will be an array of messages corresponding to the
64 * budget line rejection codes
65 */
66 PROCEDURE Get_BdgtLineRejctions
67 (p_resource_assignment_id IN Number
68 ,p_txn_currency_code IN Varchar2
69 ,p_budget_version_id IN Number
70 ,p_start_date IN Date
71 ,p_end_date IN Date
72 ,x_period_name_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_80_TBL_TYPE
73 ,x_cost_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
74 ,x_burden_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
75 ,x_revenue_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
76 ,x_pc_conv_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
77 ,x_pfc_conv_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
78 ,x_other_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
79 ,x_return_status OUT NOCOPY Varchar2
80 );
81
82
83 /* This API returns the default resource list for the given project and Plan type
84 * based on the finplan option level code = 'PLAN_TYPE'
85 * By Default it gives the Cost resource list attached at the plan type
86 * if not found then it returns the Revenue resource list
87 */
88 PROCEDURE Get_Default_FP_Reslist
89 (p_project_id IN Number
90 ,p_fin_plan_type_id IN Number DEFAULT NULL
91 ,x_res_list_id OUT NOCOPY NUMBER
92 ,x_res_list_name OUT NOCOPY Varchar2);
93
94 /* This API derives the default Resource list used in the WorkPlan structure
95 * for the given project Id
96 */
97 PROCEDURE Get_Default_WP_ResList
98 (p_project_id IN Number
99 ,p_wps_version_id IN Number default NULL
100 ,x_res_list_id OUT NOCOPY NUMBER
101 ,x_res_list_name OUT NOCOPY Varchar2);
102
103 /* This cursor derives the default resource list associated with the project
104 * Logic: get the ResList from project fp options based on the approved cost Plan type
105 for the given project. If no rows found or resList is null then Get the reslist
106 from the current budget versions for the approved cost budget version, if no rows found
107 then get the Resource List from the project_types
108 */
109 PROCEDURE Get_Default_Project_ResList
110 (p_project_id IN Number
111 ,x_res_list_id OUT NOCOPY NUMBER
112 ,x_res_list_name OUT NOCOPY Varchar2);
113
114
115 /* This API derives the rejection reason from budget lines for the given
116 * budget version id.The out variable will be an array of messages corresponding to the
117 * budget line rejection codes.
118 * This procedure is called from AMG apis.
119 */
120 PROCEDURE Get_AMG_BdgtLineRejctions
121 (p_budget_version_id IN Number
122 ,x_budget_line_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
123 ,x_cost_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
124 ,x_burden_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
125 ,x_revenue_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
126 ,x_pc_conv_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
127 ,x_pfc_conv_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
128 ,x_other_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
129 ,x_return_status OUT NOCOPY Varchar2
130 ) ;
131
132 /* THIS API is called from EditBudgetLineDetails.java page
133 * This api validates the currency conversion parameters and updates the pa_budget_lines table
134 * if there is any changes in the currency conversion attributes, it calls calculate api ()
135 */
136 PROCEDURE validateAndUpdateBdgtLine(
137 p_budget_line_id IN Number
138 ,p_BDGT_VERSION_ID IN Number
139 ,p_RES_ASSIGNMENT_ID IN Number
140 ,p_TXN_CURRENCY_CODE IN Varchar2
141 ,p_START_DATE IN Date
142 ,p_END_DATE IN Date
143 ,P_CALLING_CONTEXT IN Varchar2
144 ,P_ORG_ID IN Number
145 ,p_PLAN_VERSION_TYPE IN Varchar2
146 ,p_PROJFUNC_CURRENCY_CODE IN Varchar2
147 ,p_PROJFUNC_COST_RATE_TYPE IN Varchar2
148 ,p_PROJFUNC_COST_EXCHANGE_RATE IN Number
149 ,p_PROJFUNC_COST_RATE_DATE_TYPE IN Varchar2
150 ,p_PROJFUNC_COST_RATE_DATE IN Date
151 ,p_PROJFUNC_REV_RATE_TYPE IN Varchar2
152 ,p_PROJFUNC_REV_EXCHANGE_RATE IN Number
153 ,p_PROJFUNC_REV_RATE_DATE_TYPE IN Varchar2
154 ,p_PROJFUNC_REV_RATE_DATE IN Date
155 ,p_PROJECT_CURRENCY_CODE IN Varchar2
156 ,p_PROJECT_COST_RATE_TYPE IN Varchar2
157 ,p_PROJECT_COST_EXCHANGE_RATE IN Number
158 ,p_PROJECT_COST_RATE_DATE_TYPE IN Varchar2
159 ,p_PROJECT_COST_RATE_DATE IN Date
160 ,p_PROJECT_REV_RATE_TYPE IN Varchar2
161 ,p_PROJECT_REV_EXCHANGE_RATE IN Number
162 ,p_PROJECT_REV_RATE_DATE_TYPE IN Varchar2
163 ,p_PROJECT_REV_RATE_DATE IN Date
164 ,p_CHANGE_REASON_CODE IN Varchar2
165 ,p_DESCRIPTION IN Varchar2
166 ,p_ATTRIBUTE_CATEGORY IN Varchar2
167 ,p_ATTRIBUTE1 IN Varchar2
168 ,p_ATTRIBUTE2 IN Varchar2
169 ,p_ATTRIBUTE3 IN Varchar2
170 ,p_ATTRIBUTE4 IN Varchar2
171 ,p_ATTRIBUTE5 IN Varchar2
172 ,p_ATTRIBUTE6 IN Varchar2
173 ,p_ATTRIBUTE7 IN Varchar2
174 ,p_ATTRIBUTE8 IN Varchar2
175 ,p_ATTRIBUTE9 IN Varchar2
176 ,p_ATTRIBUTE10 IN Varchar2
177 ,p_ATTRIBUTE11 IN Varchar2
178 ,p_ATTRIBUTE12 IN Varchar2
179 ,p_ATTRIBUTE13 IN Varchar2
180 ,p_ATTRIBUTE14 IN Varchar2
181 ,p_ATTRIBUTE15 IN Varchar2
182 ,p_CI_ID IN Number
183 ,x_return_status OUT NOCOPY Varchar2
184 ,x_msg_data OUT NOCOPY Varchar2
185 ,x_msg_count OUT NOCOPY Number
186 ) ;
187
188 FUNCTION getMaskName Return Varchar2;
189 PROCEDURE setMaskName(p_period_Mask IN Varchar2) ;
190
191 /*
192 * This API provides the budget line rejections for the given Project STructure
193 * Version Id and Task Str Version Id
194 * IN Params:
195 * p_project_id IN Number Required
196 * p_calling_mode IN Varchar2 Default 'PROJ_STR_VER'
197 * the possible values are 'PROJ_STR_LEVEL' or 'TASK_STR_LEVEL'
198 * p_proj_str_version_id IN Number Required
199 * p_Task_str_version_id IN Number If calling mode is TASK_STR_LEVEL then it is reqd
200 * p_start_date IN Date
201 * If calling mode is TASK_STR_LEVEL then it is reqd.
202 * value should be periodmask or task start date
203 * p_end_date IN Date
204 * If calling mode is TASK_STR_LEVEL then it is reqd.
205 * value should be periodmask or task end date
206 * OUT Params:
207 * x_return_status will be 'U' - in case of unexpected error
208 * 'E' - in case of expected error - invalid params
209 * 'S' - in case of success
210 * If calling mode 'PROJ_STR_VER' then
211 * x_projstrlvl_rejn_flag will populated
212 * ElsIf calling mode 'TASK_STR_VER'
213 * the following out variables will be populated
214 * x_cost_rejn_flag
215 * x_burden_rejn_flag
216 * x_revenue_rejn_flag
217 * x_pc_conv_rejn_flag
218 * x_pfc_conv_rejn_flag
219 * End If;
220 *
221 */
222 PROCEDURE Get_WbsBdgtLineRejns
223 (p_project_id IN Number
224 ,p_calling_mode IN Varchar2 Default 'PROJ_STR_VER'
225 ,p_proj_str_version_id IN Number
226 ,p_Task_str_version_id IN Number Default Null
227 ,p_start_date IN Date Default Null
228 ,p_end_date IN Date Default Null
229 ,x_cost_rejn_flag OUT NOCOPY Varchar2
230 ,x_burden_rejn_flag OUT NOCOPY Varchar2
231 ,x_revenue_rejn_flag OUT NOCOPY Varchar2
232 ,x_pc_conv_rejn_flag OUT NOCOPY Varchar2
233 ,x_pfc_conv_rejn_flag OUT NOCOPY Varchar2
234 ,x_projstrlvl_rejn_flag OUT NOCOPY Varchar2
235 ,x_return_status OUT NOCOPY Varchar2
236 ,p_budget_version_id IN Number Default Null --Bug 5611909
237 );
238
239 /* This API provides the budget line Actual Start Date and End Date
240 * for the given budget version and resource assignment id
241 * Logic: Derive Actual SDate as derive the MIN(budget_line.Start_date) where
242 * actuals on the budget lines are populated.
243 * similarly for EDate derive the ETC start date from budget versions for the given resource assignment
244 * if etc start date is null then derive the MAX(budget_line.end_date) where
245 * actuals on the budget lines are populated.
246 * The PARAMS :
247 * p_budget_version_id IN Number Required
248 * p_resource_assignment_id IN Number Required
249 * x_bl_actual_start_date OUT DATE
250 * x_bl_actual_end_date OUT DATE
251 * x_return_status OUT Varchar2
252 * Note : if ETC start date and actual values donot exists then the out params
253 * x_bl_actual_start_date and x_bl_actual_end_date will be passed as NULL
254 * Rule:
255 * 1. If x_bl_actual_start_date is NULL and x_bl_actual_end_date is NULL
256 * then planning trx start date and end date can be shifted.
257 * 2. if x_bl_actual_end_date is NOT NULL then planning trx end date can't be shifted earlier than x_bl_actual_end_date
258 * 3. if x_bl_actual_start_date is NOT NULL then planning trx start date can't be shifted later than x_bl_actual_start_date
259 */
260 PROCEDURE get_blactual_Dates
261 (p_budget_version_id IN Number
262 ,p_resource_assignment_id IN Number
263 ,x_bl_actual_start_date OUT NOCOPY date
264 ,x_bl_actual_end_date OUT NOCOPY date
265 ,x_return_status OUT NOCOPY varchar2
266 ,x_error_msg_code OUT NOCOPY varchar2
267 );
268 /* This API provides the agreement related details
269 * Bug fix: 3679142 Change order versions which have revenue impact should also be in agreement
270 * currency. This means all change order versions with version type as ALL or REVENUE
271 * should ultimately have the planning txns and budget lines in AGR CURRENCY.
272 */
273 PROCEDURE Get_Agreement_Details
274 (p_budget_version_id IN Number
275 ,p_calling_mode IN Varchar2 DEFAULT 'CALCULATE_API'
276 ,x_agr_curr_code OUT NOCOPY Varchar2
277 ,x_AGR_CONV_REQD_FLAG OUT NOCOPY Varchar2
278 ,x_return_status OUT NOCOPY Varchar2 );
279
280 /* This API returns the Agreement currency for the given change order budget version
281 * If the currency is null Or budget version is not part of the change order then
282 * the api returns the NULL
283 */
284 FUNCTION get_Agreement_Currency(p_budget_version_id IN Number)
285 RETURN varchar2;
286
287 /* This API rounds off the given quantity to 5 decimal places. This API should be called for rounding the quantity
288 * for rate based planning transaction only.
289 * This API accepts the following parameters
290 */
291 FUNCTION round_quantity
292 (P_quantity IN Number
293 ) RETURN NUMBER ;
294
295 /* This API checks the given financial Task is billable or not
296 * If task is billable, it returns 'Y' else 'N'
297 */
298 FUNCTION IsFpTaskBillable(p_project_id NUMBER
299 ,p_task_id NUMBER) RETURN varchar2;
300
301
302 PROCEDURE Get_Resource_Rates
303 ( p_calling_module IN VARCHAR2 := 'MSP'
304 ,p_source_context IN VARCHAR2 := 'RLMI'
305 ,p_project_id IN NUMBER
306 ,p_budget_version_id IN NUMBER
307 ,p_resource_assignment_id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
308 ,p_resource_list_member_Id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
309 ,p_ra_date_tab IN SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE()
310 ,p_task_id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
311 ,p_quantity_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
312 ,p_txn_currency_code_ovr_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
313 ,p_cost_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
314 ,p_burden_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
315 ,p_bill_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
316 ,x_resource_assignment_id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
317 ,x_resource_list_member_Id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
318 ,x_expenditure_ou_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
319 ,x_raw_cost_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
320 ,x_burden_cost_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
321 ,x_burden_multiplier_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
322 ,x_ind_compiled_set_id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
323 ,x_bill_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
324 ,x_markup_percent_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
325 ,x_txn_currency_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
326 ,x_cost_txn_curr_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
327 ,x_rev_txn_curr_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
328 ,x_cost_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
329 ,x_burden_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
330 ,x_revenue_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
331 ,x_return_status OUT NOCOPY VARCHAR2
332 ,x_msg_data OUT NOCOPY VARCHAR2
333 ,x_msg_count OUT NOCOPY NUMBER
334 ) ;
335
336 /** MRC Elimination: Moved this procedure from pa_mrc_finplan pkg to utils as
337 * package itself is dropped
338 */
339 PROCEDURE POPULATE_BL_MAP_TMP
340 (p_source_fin_plan_version_id IN PA_BUDGET_LINES.budget_version_id%TYPE
341 ,x_return_status OUT NOCOPY VARCHAR2
342 ,x_msg_count OUT NOCOPY NUMBER
343 ,x_msg_data OUT NOCOPY VARCHAR2
344 );
345 END PA_FIN_PLAN_UTILS2 ;
[Home] [Help]