1 PACKAGE body PA_FP_GEN_COMMITMENT_AMOUNTS as
2 /* $Header: PAFPGACB.pls 120.7 2007/02/06 09:55:19 dthakker ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 PROCEDURE GEN_COMMITMENT_AMOUNTS
7 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
8 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
9 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
10 PX_GEN_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
11 PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
12 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
13 X_MSG_COUNT OUT NOCOPY NUMBER,
14 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
15
16 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_COMMITMENT_AMOUNTS.GEN_COMMITMENT_AMOUNTS';
17
18 CURSOR SUM_COMM_CRSR( c_tphase PA_PROJ_FP_OPTIONS.COST_TIME_PHASED_CODE%TYPE
19 ,c_multi_curr_flag PA_PROJ_FP_OPTIONS.PLAN_IN_MULTI_CURR_FLAG%TYPE
20 ,c_appl_id GL_PERIOD_STATUSES.APPLICATION_ID%TYPE
21 ,c_set_of_books_id PA_IMPLEMENTATIONS_ALL.SET_OF_BOOKS_ID%TYPE
22 ,c_org_id PA_PROJECTS_ALL.ORG_ID%TYPE
23 )
24 IS
25 SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
26 P.RESOURCE_ASSIGNMENT_ID
27 ,DECODE(c_multi_curr_flag, 'Y', CT.DENOM_CURRENCY_CODE,CT.PROJECT_CURRENCY_CODE) currency_code
28 ,NVL(CT.CMT_NEED_BY_DATE,CT.EXPENDITURE_ITEM_DATE)
29 ,NVL(CT.CMT_NEED_BY_DATE,CT.EXPENDITURE_ITEM_DATE)
30 ,DECODE(c_multi_curr_flag, 'Y', NVL(CT.DENOM_RAW_COST,0), NVL(CT.PROJ_RAW_COST,0)) tot_raw_cost
31 ,DECODE(c_multi_curr_flag, 'Y', NVL(CT.DENOM_BURDENED_COST,0), NVL(CT.PROJ_BURDENED_COST,0)) tot_burdened_cost
32 ,NVL(CT.PROJ_RAW_COST,0) tot_proj_raw_cost
33 ,NVL(CT.PROJ_BURDENED_COST,0) tot_proj_burdened_cost
34 ,NVL(CT.ACCT_RAW_COST,0) tot_projfunc_raw_cost
35 ,NVL(CT.ACCT_BURDENED_COST,0) tot_projfunc_burdened_cost
36 ,NVL(CT.TOT_CMT_QUANTITY,0) tot_quantity
37 FROM PA_COMMITMENT_TXNS CT,
38 PA_RES_LIST_MAP_TMP4 TMP,
39 PA_RESOURCE_ASSIGNMENTS P
40 WHERE TMP.TXN_SOURCE_ID = CT.CMT_LINE_ID
41 AND CT.PROJECT_ID = P_PROJECT_ID
42 AND NVL(CT.generation_error_flag,'N') = 'N'
43 AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
44 AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
45
46 l_res_asg_id PA_PLSQL_DATATYPES.IdTabTyp;
47 l_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
48 l_tphase PA_PLSQL_DATATYPES.Char30TabTyp;
49 l_exp_itm_date PA_PLSQL_DATATYPES.DateTabTyp;
50 l_commstart_date PA_PLSQL_DATATYPES.DateTabTyp;
51 l_commend_date PA_PLSQL_DATATYPES.DateTabTyp;
52 l_raw_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
53 l_burdened_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
54 l_proj_raw_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
55 l_proj_burdened_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
56 l_projfunc_raw_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
57 l_projfunc_burdened_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
58 l_quantity_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
59 l_DELETED_RES_ASG_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
60 l_bl_raw_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
61 l_bl_burden_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
62 l_bl_quantity_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
63
64 l_qty_tmp NUMBER:= 0;
65 l_txn_raw_cost_tmp NUMBER:= 0;
66 l_upd_count NUMBER:= 0;
67 l_bl_cmt_raw_diff NUMBER:= 0;
68 l_bl_cmt_burden_diff NUMBER:= 0;
69 l_bl_cmt_quantity_diff NUMBER:= 0;
70
71 l_txn_cost_rate_override PA_BUDGET_LINES.TXN_COST_RATE_OVERRIDE%TYPE;
72 l_burden_cost_rate_override PA_BUDGET_LINES.BURDEN_COST_RATE_OVERRIDE%TYPE;
73 l_proj_cost_exchange_rate PA_BUDGET_LINES.PROJECT_COST_EXCHANGE_RATE%TYPE;
74 l_projfunc_cost_exchange_rate PA_BUDGET_LINES.PROJFUNC_COST_EXCHANGE_RATE%TYPE;
75
76 l_appl_id NUMBER;
77 l_cnt NUMBER;
78
79 l_stru_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
80 l_budget_lines_exist VARCHAR2(1) ;
81 l_call_calculate VARCHAR2(1);
82
83 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
84 l_last_update_login NUMBER := FND_GLOBAL.login_id;
85 l_sysdate DATE := SYSDATE;
86 l_ret_status VARCHAR2(100);
87 l_msg_count NUMBER;
88 l_msg_data VARCHAR2(2000);
89 l_data VARCHAR2(2000);
90 l_msg_index_out NUMBER:=0;
91 l_rate_based_flag pa_resource_assignments.rate_based_flag%TYPE;
92 l_res_assgn_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
93 l_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
94
95 l_gen_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
96 l_chk_duplicate_flag VARCHAR2(1) := 'N';
97
98 l_resource_class_id PA_RESOURCE_CLASSES_B.RESOURCE_CLASS_ID%TYPE;
99
100 l_bl_start_date DATE;
101 l_bl_end_date DATE;
102 l_bl_period_name pa_periods_all.period_name%TYPE;
103 l_etc_start_date DATE;
104 l_reference_start_date DATE;
105 l_reference_end_date DATE;
106
107 l_count1 NUMBER;
108
109 --Local pl/sql table to call Map_Rlmi_Rbs api
110 l_TXN_SOURCE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
111 l_TXN_SOURCE_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
112 l_PERSON_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
113 l_JOB_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
114 l_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
115 l_VENDOR_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
116 l_EXPENDITURE_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
117 l_EVENT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
118 l_NON_LABOR_RESOURCE_tab PA_PLSQL_DATATYPES.Char20TabTyp;
119 l_EXPENDITURE_CATEGORY_tab PA_PLSQL_DATATYPES.Char30TabTyp;
120 l_REVENUE_CATEGORY_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
121 l_NLR_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
122 l_EVENT_CLASSIFICATION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
123 l_SYS_LINK_FUNCTION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
124 l_PROJECT_ROLE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
125 l_RESOURCE_CLASS_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
126 l_MFC_COST_TYPE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
127 l_RESOURCE_CLASS_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
128 l_FC_RES_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
129 l_INVENTORY_ITEM_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
130 l_ITEM_CATEGORY_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
131 l_PERSON_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
132 l_BOM_RESOURCE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
133 l_NAMED_ROLE_tab PA_PLSQL_DATATYPES.Char80TabTyp;
134 l_INCURRED_BY_RES_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
135 l_RATE_BASED_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
136 l_TXN_TASK_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
137 l_TXN_WBS_ELEMENT_VER_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
138 l_TXN_RBS_ELEMENT_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
139 l_TXN_PLAN_START_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
140 l_TXN_PLAN_END_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
141 --out param from PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
142 l_map_txn_source_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
143 l_map_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
144 l_map_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
145 l_map_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
146
147 -- Bug 4251148: When the Target is a Forecast version with Cost and Revenue
148 -- planned together and accrual method is Work, we will call the Calculate
149 -- API to compute revenue amounts.
150
151 /* Flag parameters for calling Calculate API */
152 l_refresh_rates_flag VARCHAR2(1);
153 l_refresh_conv_rates_flag VARCHAR2(1);
154 l_spread_required_flag VARCHAR2(1);
155 l_conv_rates_required_flag VARCHAR2(1);
156 l_rollup_required_flag VARCHAR2(1);
157 l_raTxn_rollup_api_call_flag VARCHAR2(1); -- Added for IPM new entity ER
158
159 /* Local PL/SQL table used for calling Calculate API */
160
161 l_source_context pa_fp_res_assignments_tmp.source_context%TYPE;
162 l_txn_currency_code_tab SYSTEM.pa_varchar2_15_tbl_type:=SYSTEM.pa_varchar2_15_tbl_type();
163 l_src_raw_cost_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
164 l_src_brdn_cost_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
165 l_src_revenue_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
166 l_b_cost_rate_override_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
167 l_line_start_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
168 l_line_end_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
169 l_tgt_res_asg_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
170 l_tgt_rate_based_flag_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
171 l_cost_rate_override_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
172 l_bill_rate_override_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
173 l_src_quantity_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
174
175 -- Added for Bug 4320171
176 l_raw_cost_rate_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
177 l_b_cost_rate_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
178 l_bill_rate_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
179
180 l_rev_gen_method VARCHAR2(3);
181 l_calc_api_required_flag VARCHAR2(1);
182 l_index NUMBER;
183 bl_index NUMBER;
184
185 kk NUMBER; -- an index variable during aggregation logic
186 l_index_tab PA_PLSQL_DATATYPES.IdTabTyp;
187
188 -- temporary pl/sql tables to hold pre-aggregated amounts
189 l_cal_tgt_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
190 l_cal_tgt_rate_based_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
191 l_cal_txn_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
192 l_cal_line_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
193 l_cal_line_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
194
195 l_cal_cmt_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
196 l_cal_cmt_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
197 l_cal_cmt_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
198
199 l_cal_base_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
200 l_cal_base_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
201 l_cal_base_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
202
203 -- budget line amounts for update records
204 l_bl_quantity PA_BUDGET_LINES.QUANTITY%TYPE;
205 l_bl_txn_raw_cost PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
206 l_bl_txn_burdened_cost PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE;
207 l_bl_project_raw_cost PA_BUDGET_LINES.PROJECT_RAW_COST%TYPE;
208 l_bl_project_burdened_cost PA_BUDGET_LINES.PROJECT_BURDENED_COST%TYPE;
209 l_bl_pfc_raw_cost PA_BUDGET_LINES.RAW_COST%TYPE;
210 l_bl_pfc_burdened_cost PA_BUDGET_LINES.BURDENED_COST%TYPE;
211
212 l_bl_init_quantity PA_BUDGET_LINES.QUANTITY%TYPE;
213 l_bl_txn_init_raw_cost PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
214 l_bl_txn_init_burdened_cost PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE;
215 l_bl_project_init_raw_cost PA_BUDGET_LINES.PROJECT_RAW_COST%TYPE;
216 l_bl_pfc_init_raw_cost PA_BUDGET_LINES.RAW_COST%TYPE;
217 l_bl_pfc_init_burdened_cost PA_BUDGET_LINES.BURDENED_COST%TYPE;
218
219 -- update amounts (existing budget line amounts + commitments)
220 l_upd_quantity PA_BUDGET_LINES.QUANTITY%TYPE;
221 l_upd_txn_raw_cost PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
222 l_upd_txn_burdened_cost PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE;
223 l_upd_project_raw_cost PA_BUDGET_LINES.PROJECT_RAW_COST%TYPE;
224 l_upd_project_burdened_cost PA_BUDGET_LINES.PROJECT_BURDENED_COST%TYPE;
225 l_upd_pfc_raw_cost PA_BUDGET_LINES.RAW_COST%TYPE;
226 l_upd_pfc_burdened_cost PA_BUDGET_LINES.BURDENED_COST%TYPE;
227
228 l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE;
229 -- stores ids of existing budget lines that need to be deleted
230 -- before Calculate API is called so that a difference is detected.
231 l_budget_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
232
233 /* String constants for valid Calling Context values */
234 lc_BudgetGeneration CONSTANT VARCHAR2(30) := 'BUDGET_GENERATION';
235 lc_ForecastGeneration CONSTANT VARCHAR2(30) := 'FORECAST_GENERATION';
236 l_calling_context VARCHAR2(30);
237
238 /* Variables added for Bug 4549862 */
239 l_gen_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE;
240 l_cost_based_all_from_sp_flag VARCHAR2(1);
241
242 -- Bug 4549862: GEN_COST_BASED_REVENUE expects the BILLABLE_FLAG and
243 -- BUDGET_LINE_ID columns of the PA_FP_ROLLUP_TMP to be populated.
244
245 -- Added l_bl_id_counter to track unique budget_line_id values for
246 -- the PA_FP_ROLLUP_TMP table. Initialize it to the MAX budget_line_id
247 -- value in the temp table to pick up where the Staffing Plan API left
248 -- off, and increment by 1 prior to each Insert to the temp table.
249 -- Note: these are not valid budget_line_id values in pa_budget_lines.
250 -- Rather, we are using the column to index records for processing of
251 -- cost-based revenue amounts, since an Index exists for the column.
252 l_bl_id_counter NUMBER;
253
254 -- The billable flag value will be based on target task billability.
255 l_billable_flag VARCHAR2(1);
256
257 -- Bug 4549862: Whenever Commitments map to a resource having rejection
258 -- code data (either in PA_FP_ROLLUP_TMP when the source is Staffing Plan,
259 -- or in PA_BUDGET_LINES for other cases), a generic error message will be
260 -- added once to the error stack. For each such commitment, the relevant
261 -- rejection code values should be added to the error stack. After all
262 -- Commitments have been processed, an error should be raised. This flag
263 -- tracks if at least 1 commitment maps to a resource having rejection
264 -- code data. By default, the value should be 'N'. Set the value to 'Y'
265 -- in the error situation.
266 l_rejection_code_error_flag VARCHAR2(1);
267
268 /* This user-defined exception is used to skip processing of
269 * a commitment as we process all of the commitments in a loop. */
270 continue_loop EXCEPTION;
271 l_dummy NUMBER;
272
273 -- Bug 4549862: Variables to process rejection codes
274 l_rej_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
275 l_cost_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
276 l_burden_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
277 l_pc_cur_conv_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
278 l_pfc_cur_conv_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
279
280 l_project_name PA_PROJECTS_ALL.NAME%TYPE;
281 l_task_number PA_TASKS.TASK_NUMBER%TYPE;
282 l_resource_name PA_RESOURCE_LIST_MEMBERS.ALIAS%TYPE;
283
284 -- Variables added for Bug Fix 4582616
285 l_fp_src_plan_ver_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
286 l_fp_cols_rec_src_finplan PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
287 l_fp_planning_options_flag VARCHAR2(1);
288
289 -- pl/sql tables to store parameters for POPULATE_GEN_RATE API
290 l_sr_src_ra_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
291 l_sr_tgt_ra_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
292 l_sr_txn_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
293
294 BEGIN
295 -- hr_utility.trace_on(null,'mftest');
296 /* hr_utility.trace('---BEGIN---'); */
297 X_MSG_COUNT := 0;
298 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
299
300 IF p_pa_debug_mode = 'Y' THEN
301 pa_debug.set_curr_function( p_function => 'GEN_COMMITMENT_AMOUNTS'
302 ,p_debug_mode => p_pa_debug_mode);
303 END IF;
304
305 l_stru_sharing_code := PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(P_PROJECT_ID=> P_PROJECT_ID);
306
307 -- Bug 4549862: Moved initialization of l_calling_context
308 -- here from just before call to the Calculate API.
309
310 -- Initialize calling context Calculate API parameter
311 IF p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
312 l_calling_context := lc_BudgetGeneration;
313 ELSIF p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
314 l_calling_context := lc_ForecastGeneration;
315 END IF;
316
317 -- Bug 4549862: Initialize new l_gen_src_code variable.
318 IF l_calling_context = lc_BudgetGeneration THEN
319 l_gen_src_code := p_fp_cols_rec.x_gen_src_code;
320 ELSIF l_calling_context = lc_ForecastGeneration THEN
321 l_gen_src_code := p_fp_cols_rec.x_gen_etc_src_code;
322 END IF;
323
324 -- Bug 4549862: Moved initialization of l_rev_gen_method here
325 -- from just before initialization of l_calc_api_required_flag.
326 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
327 l_rev_gen_method := nvl(p_fp_cols_rec.x_revenue_derivation_method,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id)); -- Bug 5462471
328
329 -- Bug 4549862: Initialize l_cost_based_all_from_sp_flag.
330 IF l_rev_gen_method = 'C' AND
331 p_fp_cols_rec.x_version_type = 'ALL' AND
332 l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
333 l_cost_based_all_from_sp_flag := 'Y';
334 ELSE
335 l_cost_based_all_from_sp_flag := 'N';
336 END IF;
337
338 -- Bug 4549862: The budget_line_id counter will keep track of the next
339 -- unique budget_line_id in the PA_FP_ROLLUP_TMP table. Initialize it
340 -- with the max rolup_id value in the table. Prior to each Insert
341 -- to the temp table, increment the counter by 1.
342 IF l_cost_based_all_from_sp_flag = 'Y' THEN
343 BEGIN
344 SELECT MAX(budget_line_id) INTO l_bl_id_counter
345 FROM pa_fp_rollup_tmp;
346
347 IF l_bl_id_counter IS NULL THEN
348 l_bl_id_counter := 0;
349 END IF;
350 EXCEPTION
351 WHEN OTHERS THEN
352 l_bl_id_counter := 0;
353 END;
354 END IF;
355
356 -- Bug 4549862: Initialize error flag to 'N'.
357 l_rejection_code_error_flag := 'N';
358
359
360 --dbms_output.put_line('Value for struct sharing code [' || l_stru_sharing_code || ']');
361 /* deleting the PJI resource mapping tmp tables. */
362 DELETE FROM PA_RES_LIST_MAP_TMP1;
363 DELETE FROM PA_RES_LIST_MAP_TMP2;
364 DELETE FROM PA_RES_LIST_MAP_TMP3;
365 DELETE FROM PA_RES_LIST_MAP_TMP4;
366
367 SELECT RESOURCE_CLASS_ID
368 INTO l_resource_class_id
369 FROM PA_RESOURCE_CLASSES_B
370 WHERE RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS';
371
372 --dbms_output.put_line('Value for res class id [' || to_char(l_resource_class_id) || ']');
373
374 --dbms_output.put_line('inserting into PA_RES_LIST_MAP_TMP1 P_PROJECT_ID is [' || to_char(P_PROJECT_ID) || ']');
375 SELECT ct.CMT_LINE_ID,
376 'OPEN_COMMITMENTS',
377 ct.ORGANIZATION_ID,
378 ct.VENDOR_ID,
379 ct.EXPENDITURE_TYPE,
380 ct.REVENUE_CATEGORY,
381 ct.TASK_ID
382 ,NVL(ct.CMT_NEED_BY_DATE, ct.EXPENDITURE_ITEM_DATE)
383 ,NVL(ct.CMT_NEED_BY_DATE, ct.EXPENDITURE_ITEM_DATE)
384 ,SYSTEM_LINKAGE_FUNCTION
385 ,INVENTORY_ITEM_ID
386 ,DECODE(EXPENDITURE_TYPE,null,
387 DECODE(EXPENDITURE_CATEGORY,null,NULL,
388 'EXPENDITURE_CATEGORY'),'EXPENDITURE_TYPE'),
389 NVL(ct.RESOURCE_CLASS,'FINANCIAL_ELEMENTS')
390 BULK COLLECT
391 INTO l_TXN_SOURCE_ID_tab,
392 l_TXN_SOURCE_TYPE_CODE_tab,
393 l_ORGANIZATION_ID_tab,
394 l_VENDOR_ID_tab,
395 l_EXPENDITURE_TYPE_tab,
396 l_REVENUE_CATEGORY_CODE_tab,
397 l_TXN_TASK_ID_tab,
398 l_TXN_PLAN_START_DATE_tab,
399 l_TXN_PLAN_END_DATE_tab,
400 l_SYS_LINK_FUNCTION_tab,
401 l_INVENTORY_ITEM_ID_tab,
402 l_FC_RES_TYPE_CODE_tab,
403 l_RESOURCE_CLASS_CODE_tab
404 FROM PA_COMMITMENT_TXNS ct, PA_RESOURCE_CLASSES_B rc
405 WHERE ct.PROJECT_ID = P_PROJECT_ID
406 AND NVL(CT.generation_error_flag,'N') = 'N'
407 AND ct.RESOURCE_CLASS = rc.RESOURCE_CLASS_CODE(+);
408 --dbms_output.put_line('l_TXN_SOURCE_ID_tab.count: '||l_TXN_SOURCE_ID_tab.count);
409 IF l_TXN_SOURCE_ID_tab.count = 0 THEN
410 IF P_PA_DEBUG_MODE = 'Y' THEN
411 PA_DEBUG.Reset_Curr_Function;
412 END IF;
413 RETURN;
414 END IF;
415
416
417 FOR bb in 1..l_TXN_SOURCE_ID_tab.count LOOP
418 l_PERSON_ID_tab(bb) := null;
419 l_JOB_ID_tab(bb) := null;
420 l_EVENT_TYPE_tab(bb) := null;
421 l_NON_LABOR_RESOURCE_tab(bb) := null;
422 l_EXPENDITURE_CATEGORY_tab(bb) := null;
423 l_NLR_ORGANIZATION_ID_tab(bb) := null;
424 l_EVENT_CLASSIFICATION_tab(bb) := null;
425 l_PROJECT_ROLE_ID_tab(bb) := null;
426 l_MFC_COST_TYPE_ID_tab(bb) := null;
427 l_RESOURCE_CLASS_FLAG_tab(bb) := null;
428 l_ITEM_CATEGORY_ID_tab(bb) := null;
429 l_PERSON_TYPE_CODE_tab(bb) := null;
430 l_BOM_RESOURCE_ID_tab(bb) := null;
431 l_NAMED_ROLE_tab(bb) := null;
432 l_INCURRED_BY_RES_FLAG_tab(bb) := null;
433 l_RATE_BASED_FLAG_tab(bb) := null;
434 l_TXN_WBS_ELEMENT_VER_ID_tab(bb):= null;
435 l_TXN_RBS_ELEMENT_ID_tab(bb) := null;
436 END LOOP;
437 --dbms_output.put_line('b4 calling MAP_RLMI_RBS api');
438 IF P_PA_DEBUG_MODE = 'Y' THEN
439 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
440 P_MSG => 'Before calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS',
441 P_MODULE_NAME => l_module_name);
442 END IF;
443 PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS (
444 P_PROJECT_ID => p_project_id,
445 P_BUDGET_VERSION_ID => NULL,
446 P_RESOURCE_LIST_ID => P_FP_COLS_REC.X_RESOURCE_LIST_ID,
447 P_RBS_VERSION_ID => NULL,
448 P_CALLING_PROCESS => 'BUDGET_GENERATION',
449 P_CALLING_CONTEXT => 'PLSQL',
450 P_PROCESS_CODE => 'RES_MAP',
451 P_CALLING_MODE => 'PLSQL_TABLE',
452 P_INIT_MSG_LIST_FLAG => 'N',
453 P_COMMIT_FLAG => 'N',
454 P_TXN_SOURCE_ID_TAB => l_TXN_SOURCE_ID_tab,
455 P_TXN_SOURCE_TYPE_CODE_TAB => l_TXN_SOURCE_TYPE_CODE_tab,
456 P_PERSON_ID_TAB => l_PERSON_ID_tab,
457 P_JOB_ID_TAB => l_JOB_ID_tab,
458 P_ORGANIZATION_ID_TAB => l_ORGANIZATION_ID_tab,
459 P_VENDOR_ID_TAB => l_VENDOR_ID_tab,
460 P_EXPENDITURE_TYPE_TAB => l_EXPENDITURE_TYPE_tab,
461 P_EVENT_TYPE_TAB => l_EVENT_TYPE_tab,
462 P_NON_LABOR_RESOURCE_TAB => l_NON_LABOR_RESOURCE_tab,
463 P_EXPENDITURE_CATEGORY_TAB => l_EXPENDITURE_CATEGORY_tab,
464 P_REVENUE_CATEGORY_CODE_TAB =>l_REVENUE_CATEGORY_CODE_tab,
465 P_NLR_ORGANIZATION_ID_TAB =>l_NLR_ORGANIZATION_ID_tab,
466 P_EVENT_CLASSIFICATION_TAB => l_EVENT_CLASSIFICATION_tab,
467 P_SYS_LINK_FUNCTION_TAB => l_SYS_LINK_FUNCTION_tab,
468 P_PROJECT_ROLE_ID_TAB => l_PROJECT_ROLE_ID_tab,
469 P_RESOURCE_CLASS_CODE_TAB => l_RESOURCE_CLASS_CODE_tab,
470 P_MFC_COST_TYPE_ID_TAB => l_MFC_COST_TYPE_ID_tab,
471 P_RESOURCE_CLASS_FLAG_TAB => l_RESOURCE_CLASS_FLAG_tab,
472 P_FC_RES_TYPE_CODE_TAB => l_FC_RES_TYPE_CODE_tab,
473 P_INVENTORY_ITEM_ID_TAB => l_INVENTORY_ITEM_ID_tab,
474 P_ITEM_CATEGORY_ID_TAB => l_ITEM_CATEGORY_ID_tab,
475 P_PERSON_TYPE_CODE_TAB => l_PERSON_TYPE_CODE_tab,
476 P_BOM_RESOURCE_ID_TAB =>l_BOM_RESOURCE_ID_tab,
477 P_NAMED_ROLE_TAB =>l_NAMED_ROLE_tab,
478 P_INCURRED_BY_RES_FLAG_TAB =>l_INCURRED_BY_RES_FLAG_tab,
479 P_RATE_BASED_FLAG_TAB =>l_RATE_BASED_FLAG_tab,
480 P_TXN_TASK_ID_TAB =>l_TXN_TASK_ID_tab,
481 P_TXN_WBS_ELEMENT_VER_ID_TAB => l_TXN_WBS_ELEMENT_VER_ID_tab,
482 P_TXN_RBS_ELEMENT_ID_TAB => l_TXN_RBS_ELEMENT_ID_tab,
483 P_TXN_PLAN_START_DATE_TAB => l_TXN_PLAN_START_DATE_tab,
484 P_TXN_PLAN_END_DATE_TAB => l_TXN_PLAN_END_DATE_tab,
485 X_TXN_SOURCE_ID_TAB =>l_map_txn_source_id_tab,
486 X_RES_LIST_MEMBER_ID_TAB =>l_map_rlm_id_tab,
487 X_RBS_ELEMENT_ID_TAB =>l_map_rbs_element_id_tab,
488 X_TXN_ACCUM_HEADER_ID_TAB =>l_map_txn_accum_header_id_tab,
489 X_RETURN_STATUS => x_return_status,
490 X_MSG_COUNT => x_msg_count,
491 X_MSG_DATA => x_msg_data );
492 IF P_PA_DEBUG_MODE = 'Y' THEN
493 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
494 P_MSG => 'After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||
495 x_return_status,
496 P_MODULE_NAME => l_module_name);
497 END IF;
498 --dbms_output.put_line('After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||x_return_status);
499 --dbms_output.put_line('l_map_rlm_id_tab.count: '||l_map_rlm_id_tab.count);
500 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
501 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
502 END IF;
503
504 SELECT /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
505 count(*) INTO l_count1
506 FROM PA_RES_LIST_MAP_TMP4
507 WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
508 IF l_count1 > 0 THEN
509 PA_UTILS.ADD_MESSAGE
510 (p_app_short_name => 'PA',
511 p_msg_name => 'PA_INVALID_MAPPING_ERR');
512 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
513 END IF;
514
515 IF p_pa_debug_mode = 'Y' THEN
516 pa_fp_gen_amount_utils.fp_debug
517 (p_msg => 'Before calling pa_fp_gen_budget_amt_pub.create_res_asg',
518 p_module_name => l_module_name,
519 p_log_level => 5);
520 END IF;
521 --dbms_output.put_line('calling PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG');
522 PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG
523 (P_PROJECT_ID => P_PROJECT_ID,
524 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
525 P_STRU_SHARING_CODE => l_stru_sharing_code,
526 P_GEN_SRC_CODE => 'OPEN_COMMITMENTS',
527 P_FP_COLS_REC => P_FP_COLS_REC,
528 X_RETURN_STATUS => X_RETURN_STATUS,
529 X_MSG_COUNT => X_MSG_COUNT,
530 X_MSG_DATA => X_MSG_DATA);
531 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
532 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
533 END IF;
534
535 --dbms_output.put_line('after calling PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG');
536 IF p_pa_debug_mode = 'Y' THEN
537 pa_fp_gen_amount_utils.fp_debug
538 (p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.create_res_asg'
539 ||x_return_status,
540 p_module_name => l_module_name,
541 p_log_level => 5);
542 END IF;
543
544 IF p_pa_debug_mode = 'Y' THEN
545 pa_fp_gen_amount_utils.fp_debug
546 (p_msg => 'Before calling pa_fp_gen_budget_amt_pub.update_res_asg',
547 p_module_name => l_module_name,
548 p_log_level => 5);
549 END IF;
550
551 --dbms_output.put_line('calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG');
552 PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG
553 (P_PROJECT_ID => P_PROJECT_ID,
554 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
555 P_STRU_SHARING_CODE => l_stru_sharing_code,
556 P_GEN_SRC_CODE => 'OPEN_COMMITMENTS',
557 P_FP_COLS_REC => P_FP_COLS_REC,
558 X_RETURN_STATUS => X_RETURN_STATUS,
559 X_MSG_COUNT => X_MSG_COUNT,
560 X_MSG_DATA => X_MSG_DATA);
561 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
562 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
563 END IF;
564 --dbms_output.put_line('after calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG');
565 IF p_pa_debug_mode = 'Y' THEN
566 pa_fp_gen_amount_utils.fp_debug
567 (p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.update_res_asg'
568 ||x_return_status,
569 p_module_name => l_module_name,
570 p_log_level => 5);
571 END IF;
572
573 l_appl_id := PA_PERIOD_PROCESS_PKG.Application_id;
574
575 /*===================================================================+
576 | If plan_type_code is 'FORECAST', use the ETC_START_DATE to derive |
577 | the dates for the budget lines. |
578 +===================================================================*/
579 IF ( P_FP_COLS_REC.X_PLAN_CLASS_CODE = 'FORECAST' )
580 THEN
581 -- hr_utility.trace('---inside forecast plan class---');
582 l_etc_start_date :=
583 PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(P_FP_COLS_REC.X_BUDGET_VERSION_ID);
584 -- hr_utility.trace('---etc start dt --'||to_char(l_etc_start_date,'dd-mon-rrrr'));
585 END IF;
586
587 --dbms_output.put_line('opening cursor SUM_COMM_CRSR');
588 OPEN SUM_COMM_CRSR(P_FP_COLS_REC.X_TIME_PHASED_CODE,
589 P_FP_COLS_REC.X_PLAN_IN_MULTI_CURR_FLAG,
590 l_appl_id,
591 P_FP_COLS_REC.X_SET_OF_BOOKS_ID,
592 P_FP_COLS_REC.X_ORG_ID);
593
594 --dbms_output.put_line('fetching cursor SUM_COMM_CRSR');
595 FETCH SUM_COMM_CRSR
596 BULK COLLECT
597 INTO l_res_asg_id
598 ,l_currency_code
599 ,l_commstart_date
600 ,l_commend_date
601 ,l_raw_cost_sum
602 ,l_burdened_cost_sum
603 ,l_proj_raw_cost_sum
604 ,l_proj_burdened_cost_sum
605 ,l_projfunc_raw_cost_sum
606 ,l_projfunc_burdened_cost_sum
607 ,l_quantity_sum_tab;
608 --dbms_output.put_line('closing cursor SUM_COMM_CRSR');
609 CLOSE SUM_COMM_CRSR;
610
611 IF l_res_asg_id.count = 0 THEN
612 IF P_PA_DEBUG_MODE = 'Y' THEN
613 PA_DEBUG.Reset_Curr_Function;
614 END IF;
615 RETURN; -- added by dkuo 2005.04.13
616 END IF;
617
618 -- Bug 4320171: Before calling the Calculate API with Source Context
619 -- as BUDGET_LINE, the Resource Assignment Planning Start/End dates
620 -- need to be synched up with the Commitment Dates. We can sync up the
621 -- dates any time after the tmp4 table has been created and the
622 -- UPDATE_RES_ASG API has been called. Thus, we have moved the call
623 -- to SYNC_UP_PLANNING_DATES from the end of the API to here.
624
625 IF p_pa_debug_mode = 'Y' THEN
626 pa_fp_gen_amount_utils.fp_debug
627 (p_msg => 'Before calling
628 pa_fp_maintain_actual_pub.sync_up_planning_dates',
629 p_module_name => l_module_name,
630 p_log_level => 5);
631 END IF;
632 PA_FP_MAINTAIN_ACTUAL_PUB.SYNC_UP_PLANNING_DATES
633 (P_BUDGET_VERSION_ID => p_budget_version_id,
634 P_CALLING_CONTEXT => 'GEN_COMMITMENTS',
635 X_RETURN_STATUS => x_return_Status,
636 X_MSG_COUNT => x_msg_count,
637 X_MSG_DATA => x_msg_data );
638 IF p_pa_debug_mode = 'Y' THEN
639 pa_fp_gen_amount_utils.fp_debug
640 (p_msg => 'Status after calling
641 pa_fp_maintain_actual_pub.sync_up_planning_dates'
642 ||x_return_status,
643 p_module_name => l_module_name,
644 p_log_level => 5);
645 END IF;
646 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
647 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
648 END IF;
649
650
651 -- Bug 4251148: When the Target is a Forecast version with Cost and Revenue
652 -- planned together and the revenue accrual method is Work, we will call the
653 -- Calculate API to compute revenue amounts.
654
655 -- Bug 4549862: Moved initialization of l_rev_gen_method to beginning of API.
656
657 IF p_fp_cols_rec.x_version_type = 'ALL' AND l_rev_gen_method = 'T' THEN
658 l_calc_api_required_flag := 'Y';
659 ELSE
660 l_calc_api_required_flag := 'N';
661 END IF;
662
663 -- Initialize l_index for Calculate API pl/sql tables. Increment before use.
664 l_index := 0;
665 bl_index := 0;
666
667 -- Bug 4549862: Placed contents of the main loop in a Begin/End block
668 -- so that the user-defined continue_loop exception can be used to skip
669 -- further processing within a loop iteration if a rejection code error
670 -- is discovered. In this case, rejection codes must be checked for all
671 -- commitments before an error is Raised.
672
673 FOR i IN 1..l_res_asg_id.COUNT LOOP
674 BEGIN
675 /*=================================================================+
676 | If Forecast use etc_start_date to derive the BL start and end |
677 | dates. Else, use commitment start and end dates. |
678 +=================================================================*/
679 /* assigning the commitment start date to the local variable for
680 bug 3846278 */
681 -- hr_utility.trace('---ref start date before---'||to_char(l_reference_start_date,'dd-mon-rrrr'));
682
683 SELECT NVL(rate_based_flag,'N') INTO l_rate_based_flag
684 FROM pa_resource_assignments
685 WHERE
686 resource_assignment_id = l_res_asg_id(i);
687
688 IF l_rate_based_flag = 'N' THEN
689 IF P_FP_COLS_REC.X_PLAN_IN_MULTI_CURR_FLAG = 'Y' THEN
690 l_quantity_sum_tab(i) := l_raw_cost_sum(i);
691 ELSE
692 l_quantity_sum_tab(i) := l_proj_raw_cost_sum(i);
693 END IF;
694 END IF;
695
696 -- Added l_reference_end_date to be used for deriving
697 -- l_bl_end_date when the Target is None timephased.
698
699 l_reference_start_date := TRUNC(l_commstart_date(i));
700 l_reference_end_date := TRUNC(l_commend_date(i));
701 IF ( P_FP_COLS_REC.X_PLAN_CLASS_CODE = 'FORECAST' AND
702 l_etc_start_date IS NOT NULL)
703 THEN
704 IF l_reference_start_date < l_etc_start_date THEN
705 l_reference_start_date := l_etc_start_date;
706 END IF;
707 IF l_reference_end_date < l_etc_start_date THEN
708 l_reference_end_date := l_etc_start_date;
709 END IF;
710 END IF;
711 -- hr_utility.trace('---ref start date aft if chk---'||to_char(l_reference_start_date,'dd-mon-rrrr'));
712
713 --dbms_output.put_line (' time phase [' || P_FP_COLS_REC.X_TIME_PHASED_CODE || ']');
714 IF ( P_FP_COLS_REC.X_TIME_PHASED_CODE = 'P' )
715 THEN
716 BEGIN
717 -- SQL Repository Bug 4884718; SQL ID 14901776
718 -- Fixed Merge Join Cartesian violation by commenting out
719 -- PA_IMPLEMENTATIONS from the FROM clause of the query below.
720
721 SELECT pap.start_date
722 ,pap.end_date
723 ,pap.period_name
724 INTO l_bl_start_date
725 ,l_bl_end_date
726 ,l_bl_period_name
727 FROM pa_periods_all pap
728 --,pa_implementations imp /* Bug 4884718; SQL ID 14901776 */
729 WHERE l_reference_start_date BETWEEN pap.start_date AND pap.end_date
730 AND pap.org_id = p_fp_cols_rec.x_org_id;
731 -- R12 MOAC 4447573: NVL(pap.org_id,-99)
732
733 -- fp cols rec nvl org id is done in the util pkg.
734 EXCEPTION
735 WHEN OTHERS THEN RAISE;
736 END;
737 ELSIF ( P_FP_COLS_REC.X_TIME_PHASED_CODE = 'G' )
738 THEN
739 BEGIN
740 SELECT PERIOD.start_date,
741 PERIOD.end_date,
742 PERIOD.period_name
743 INTO l_bl_start_date
744 ,l_bl_end_date
745 ,l_bl_period_name
746 FROM GL_PERIOD_STATUSES PERIOD
747 WHERE PERIOD.application_id = pa_period_process_pkg.application_id
748 AND PERIOD.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
749 AND PERIOD.adjustment_period_flag = 'N'
750 AND l_reference_start_date BETWEEN
751 PERIOD.start_date AND PERIOD.end_date;
752 EXCEPTION
753 WHEN OTHERS THEN RAISE;
754 END;
755 END IF; -- P_FP_COLS_REC.X_TIME_PHASED_CODE = 'P'
756
757 /* hr_utility.trace('---bef bl chk res asg id---'||l_res_asg_id(i));
758 hr_utility.trace('---bef bl chk cny code ---'||l_currency_code(i)); */
759
760
761 -- Bug 4549862: Now that we have the commitment period start date
762 -- for timephased versions, get budget line rejection codes.
763 --
764 -- When l_cost_based_all_from_sp_flag = 'N', check PA_BUDGET_LINES
765 -- for rejection codes. When l_cost_based_all_from_sp_flag = 'Y',
766 -- check PA_FP_ROLLUP_TMP for rejection codes instead.
767 -- We are only interested in checking the raw cost, burden cost,
768 -- pc/pfc currency conversion rejection codes.
769
770 IF l_cost_based_all_from_sp_flag = 'N' THEN
771
772 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
773 SELECT START_DATE,
774 COST_REJECTION_CODE,
775 BURDEN_REJECTION_CODE,
776 PC_CUR_CONV_REJECTION_CODE,
777 PFC_CUR_CONV_REJECTION_CODE
778 BULK COLLECT
779 INTO l_rej_start_date_tab,
780 l_cost_rej_code_tab,
781 l_burden_rej_code_tab,
782 l_pc_cur_conv_rej_code_tab,
783 l_pfc_cur_conv_rej_code_tab
784 FROM pa_budget_lines
785 WHERE resource_assignment_id = l_res_asg_id(i)
786 AND txn_currency_code = l_currency_code(i)
787 AND start_date = l_bl_start_date
788 AND ( cost_rejection_code is not null OR
789 burden_rejection_code is not null OR
790 pc_cur_conv_rejection_code is not null OR
791 pfc_cur_conv_rejection_code is not null );
792 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
793 SELECT START_DATE,
794 COST_REJECTION_CODE,
795 BURDEN_REJECTION_CODE,
796 PC_CUR_CONV_REJECTION_CODE,
797 PFC_CUR_CONV_REJECTION_CODE
798 BULK COLLECT
799 INTO l_rej_start_date_tab,
800 l_cost_rej_code_tab,
801 l_burden_rej_code_tab,
802 l_pc_cur_conv_rej_code_tab,
803 l_pfc_cur_conv_rej_code_tab
804 FROM pa_budget_lines
805 WHERE resource_assignment_id = l_res_asg_id(i)
806 AND txn_currency_code = l_currency_code(i)
807 AND ( cost_rejection_code is not null OR
808 burden_rejection_code is not null OR
809 pc_cur_conv_rejection_code is not null OR
810 pfc_cur_conv_rejection_code is not null );
811 END IF; -- time phase check
812
813 ELSIF l_cost_based_all_from_sp_flag = 'Y' THEN
814
815 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
816 SELECT DISTINCT
817 start_date,
818 cost_rejection_code,
819 burden_rejection_code,
820 pc_cur_conv_rejection_code,
821 pfc_cur_conv_rejection_code
822 BULK COLLECT
823 INTO l_rej_start_date_tab,
824 l_cost_rej_code_tab,
825 l_burden_rej_code_tab,
826 l_pc_cur_conv_rej_code_tab,
827 l_pfc_cur_conv_rej_code_tab
828 FROM pa_fp_rollup_tmp
829 WHERE resource_assignment_id = l_res_asg_id(i)
830 AND txn_currency_code = l_currency_code(i)
831 AND start_date = l_bl_start_date
832 AND ( cost_rejection_code is not null OR
833 burden_rejection_code is not null OR
834 pc_cur_conv_rejection_code is not null OR
835 pfc_cur_conv_rejection_code is not null );
836 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
837
838 -- When the target version is None timephased, it is
839 -- possible for multiple requirements/assignments with
840 -- different start dates to map to the same target
841 -- (resource, txn currency) combination. As a result,
842 -- this case is handled differently from the others;
843 -- take the minimum start date as the rejection code
844 -- start date. Since the version is None timephased,
845 -- rejection codes can occur any time within the start
846 -- and end date time span, so it is okay to use the
847 -- min start date here.
848
849 SELECT MIN(start_date),
850 cost_rejection_code,
851 burden_rejection_code,
852 pc_cur_conv_rejection_code,
853 pfc_cur_conv_rejection_code
854 BULK COLLECT
855 INTO l_rej_start_date_tab,
856 l_cost_rej_code_tab,
857 l_burden_rej_code_tab,
858 l_pc_cur_conv_rej_code_tab,
859 l_pfc_cur_conv_rej_code_tab
860 FROM pa_fp_rollup_tmp
861 WHERE resource_assignment_id = l_res_asg_id(i)
862 AND txn_currency_code = l_currency_code(i)
863 AND ( cost_rejection_code is not null OR
864 burden_rejection_code is not null OR
865 pc_cur_conv_rejection_code is not null OR
866 pfc_cur_conv_rejection_code is not null )
867 GROUP BY cost_rejection_code,
868 burden_rejection_code,
869 pc_cur_conv_rejection_code,
870 pfc_cur_conv_rejection_code;
871 END IF; -- time phase check
872
873 END IF; -- l_cost_based_all_from_sp_flag check
874
875 -- Bug 4549862: Process budget line rejection codes
876
877 IF l_rej_start_date_tab.count > 0 THEN
878
879 -- Bug 4549862: Add a generic error message to the error stack
880 -- before pushing any rejection codes on to the stack. Only do
881 -- this the first time we discover a rejection code and change
882 -- the l_rejection_code_error_flag from 'N' to 'Y'.
883
884 IF l_rejection_code_error_flag = 'N' THEN
885 PA_UTILS.ADD_MESSAGE
886 ( p_app_short_name => 'PA',
887 p_msg_name => 'PA_CMT_REJ_CODE_ERR' );
888 l_rejection_code_error_flag := 'Y';
889 END IF;
890
891 -- Get other message token details
892 SELECT p.name, ta.task_number, rlm.alias
893 INTO l_project_name, l_task_number, l_resource_name
894 FROM pa_resource_assignments ra,
895 pa_projects_all p,
896 pa_tasks ta,
897 pa_resource_list_members rlm
898 WHERE ra.resource_assignment_id = l_res_asg_id(i)
899 AND p.project_id = ra.project_id
900 AND ta.task_id (+) = ra.task_id
901 AND rlm.resource_list_member_id = ra.resource_list_member_id;
902
903 -- Add rejection code error messages to the error stack.
904 -- Note that the code can be null (e.g. when we select from a
905 -- single budget line, we check that at least 1 rejection code
906 -- is not null, but the others may be null).
907
908 FOR j in 1..l_rej_start_date_tab.count LOOP
909 IF l_cost_rej_code_tab(j) IS NOT NULL THEN
910 PA_UTILS.ADD_MESSAGE
911 ( p_app_short_name => 'PA',
912 p_msg_name => l_cost_rej_code_tab(j),
913 p_token1 => 'PROJECT',
914 p_value1 => l_project_name,
915 p_token2 => 'TASK',
916 p_value2 => l_task_number,
917 p_token3 => 'RESOURCE_NAME',
918 p_value3 => l_resource_name,
919 p_token4 => 'START_DATE',
920 p_value4 => l_rej_start_date_tab(j) );
921 END IF;
922 IF l_burden_rej_code_tab(j) IS NOT NULL THEN
923 PA_UTILS.ADD_MESSAGE
924 ( p_app_short_name => 'PA',
925 p_msg_name => l_burden_rej_code_tab(j),
926 p_token1 => 'PROJECT',
927 p_value1 => l_project_name,
928 p_token2 => 'TASK',
929 p_value2 => l_task_number,
930 p_token3 => 'RESOURCE_NAME',
931 p_value3 => l_resource_name,
932 p_token4 => 'START_DATE',
933 p_value4 => l_rej_start_date_tab(j) );
934 END IF;
935 IF l_pc_cur_conv_rej_code_tab(j) IS NOT NULL THEN
936 PA_UTILS.ADD_MESSAGE
937 ( p_app_short_name => 'PA',
938 p_msg_name => l_pc_cur_conv_rej_code_tab(j),
939 p_token1 => 'PROJECT',
940 p_value1 => l_project_name,
941 p_token2 => 'TASK',
942 p_value2 => l_task_number,
943 p_token3 => 'RESOURCE_NAME',
944 p_value3 => l_resource_name,
945 p_token4 => 'START_DATE',
946 p_value4 => l_rej_start_date_tab(j) );
947 END IF;
948 IF l_pfc_cur_conv_rej_code_tab(j) IS NOT NULL THEN
949 PA_UTILS.ADD_MESSAGE
950 ( p_app_short_name => 'PA',
951 p_msg_name => l_pfc_cur_conv_rej_code_tab(j),
952 p_token1 => 'PROJECT',
953 p_value1 => l_project_name,
954 p_token2 => 'TASK',
955 p_value2 => l_task_number,
956 p_token3 => 'RESOURCE_NAME',
957 p_value3 => l_resource_name,
958 p_token4 => 'START_DATE',
959 p_value4 => l_rej_start_date_tab(j) );
960 END IF;
961 END LOOP; -- FOR j in 1..l_rej_start_date_tab.count LOOP
962
963 END IF; -- l_rej_start_date_tab.count > 0
964
965 -- Bug 4549862: If the rejection code error flag is 'Y', then an error
966 -- will eventually be raised, so there is no point in processing this
967 -- commitment any further.
968
969 IF l_rejection_code_error_flag = 'Y' THEN
970 RAISE continue_loop;
971 END IF;
972
973
974 -- Bug 4549862: For the Cost-based All version from Staffing Plan
975 -- flow, we do not need to check if a budget line exists for the
976 -- current commitment being processed, since we will be inserting
977 -- the commitment data into the PA_FP_ROLLUP_TMP table instead of
978 -- pa_budget_lines for further processing. At the same time, we
979 -- still want the l_budget_lines_exist flag initialized as 'N' so
980 -- that logic for updating dates for None time phased versions is
981 -- still executed properly.
982
983 l_budget_lines_exist := 'N';
984
985 IF l_cost_based_all_from_sp_flag = 'N' THEN
986
987 BEGIN
988 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
989 SELECT BUDGET_LINE_ID,
990 QUANTITY,
991 TXN_RAW_COST,
992 TXN_BURDENED_COST,
993 PROJECT_RAW_COST,
994 PROJECT_BURDENED_COST,
995 RAW_COST,
996 BURDENED_COST
997 INTO l_budget_line_id,
998 l_bl_quantity,
999 l_bl_txn_raw_cost,
1000 l_bl_txn_burdened_cost,
1001 l_bl_project_raw_cost,
1002 l_bl_project_burdened_cost,
1003 l_bl_pfc_raw_cost,
1004 l_bl_pfc_burdened_cost
1005 FROM PA_BUDGET_LINES BL
1006 WHERE BL.RESOURCE_ASSIGNMENT_ID = l_res_asg_id(i)
1007 AND BL.TXN_CURRENCY_CODE = l_currency_code(i)
1008 AND BL.START_DATE = l_bl_start_date;
1009 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1010 IF p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
1011 SELECT BUDGET_LINE_ID,
1012 START_DATE,
1013 END_DATE,
1014 QUANTITY,
1015 TXN_RAW_COST,
1016 TXN_BURDENED_COST,
1017 PROJECT_RAW_COST,
1018 PROJECT_BURDENED_COST,
1019 RAW_COST,
1020 BURDENED_COST,
1021 NVL(INIT_QUANTITY,0),
1022 NVL(TXN_INIT_RAW_COST,0),
1023 NVL(TXN_INIT_BURDENED_COST,0),
1024 NVL(PROJECT_INIT_RAW_COST,0),
1025 NVL(INIT_RAW_COST,0),
1026 NVL(INIT_BURDENED_COST,0)
1027 INTO l_budget_line_id,
1028 l_bl_start_date,
1029 l_bl_end_date,
1030 l_bl_quantity,
1031 l_bl_txn_raw_cost,
1032 l_bl_txn_burdened_cost,
1033 l_bl_project_raw_cost,
1034 l_bl_project_burdened_cost,
1035 l_bl_pfc_raw_cost,
1036 l_bl_pfc_burdened_cost,
1037 l_bl_init_quantity,
1038 l_bl_txn_init_raw_cost,
1039 l_bl_txn_init_burdened_cost,
1040 l_bl_project_init_raw_cost,
1041 l_bl_pfc_init_raw_cost,
1042 l_bl_pfc_init_burdened_cost
1043 FROM PA_BUDGET_LINES BL
1044 WHERE BL.RESOURCE_ASSIGNMENT_ID = l_res_asg_id(i)
1045 AND BL.TXN_CURRENCY_CODE = l_currency_code(i);
1046 ELSE
1047 SELECT BUDGET_LINE_ID,
1048 START_DATE,
1049 END_DATE,
1050 QUANTITY,
1051 TXN_RAW_COST,
1052 TXN_BURDENED_COST,
1053 PROJECT_RAW_COST,
1054 PROJECT_BURDENED_COST,
1055 RAW_COST,
1056 BURDENED_COST
1057 INTO l_budget_line_id,
1058 l_bl_start_date,
1059 l_bl_end_date,
1060 l_bl_quantity,
1061 l_bl_txn_raw_cost,
1062 l_bl_txn_burdened_cost,
1063 l_bl_project_raw_cost,
1064 l_bl_project_burdened_cost,
1065 l_bl_pfc_raw_cost,
1066 l_bl_pfc_burdened_cost
1067 FROM PA_BUDGET_LINES BL
1068 WHERE BL.RESOURCE_ASSIGNMENT_ID = l_res_asg_id(i)
1069 AND BL.TXN_CURRENCY_CODE = l_currency_code(i);
1070 END IF; -- Forecast plan check
1071 END IF; -- time phase check
1072 l_budget_lines_exist := 'Y';
1073 EXCEPTION
1074 WHEN NO_DATA_FOUND THEN
1075 l_budget_lines_exist := 'N';
1076 END;
1077 /*dbms_output.put_line('l_budget_lines_exist [' || l_budget_lines_exist || ']');
1078 dbms_output.put_line('set_of_books_id: '|| p_fp_cols_rec.x_set_of_books_id);
1079 dbms_output.put_line('l_reference_start_date: '||l_reference_start_date);*/
1080
1081 END IF; -- l_cost_based_all_from_sp_flag check
1082
1083
1084 -- We have delayed processing on l_bl_start_date and l_bl_end_date for
1085 -- the None time phase case to avoid an extra query to pa_budget_lines.
1086 IF ( P_FP_COLS_REC.X_TIME_PHASED_CODE = 'N' )
1087 THEN
1088 -- Since start_date and end_date are both non-null columns in
1089 -- pa_budget_lines, checking l_bl_start_date is sufficient.
1090 IF l_bl_start_date IS NOT NULL THEN
1091 IF l_reference_start_date < l_bl_start_date THEN
1092 l_bl_start_date := l_reference_start_date;
1093 END IF;
1094 IF l_reference_end_date > l_bl_end_date THEN
1095 l_bl_end_date := l_reference_end_date;
1096 END IF;
1097 ELSE
1098 l_bl_start_date := l_reference_start_date;
1099 l_bl_end_date := l_reference_end_date;
1100 END IF;
1101 END IF; -- None timephase check
1102
1103
1104 -- Bug 4549862: For the Cost-based All version from Staffing Plan
1105 -- flow, Insert commitment data into the PA_FP_ROLLUP_TMP table
1106 -- instead of pa_budget_lines for further processing by the Cost-
1107 -- based Revenue Generation API, which will propagate the data to
1108 -- the budget lines. For all other flows, proceed with existing
1109 -- logic for Insert/Update to the budget lines.
1110
1111 IF l_cost_based_all_from_sp_flag = 'Y' THEN
1112
1113 -- Increment counter to a new unique id
1114 l_bl_id_counter := l_bl_id_counter + 1;
1115
1116 -- Get the target task billability flag
1117 SELECT NVL(billable_flag,'Y') INTO l_billable_flag
1118 FROM pa_tasks ta,
1119 pa_resource_assignments ra
1120 WHERE ra.resource_assignment_id = l_res_asg_id(i)
1121 AND ra.task_id = ta.task_id (+);
1122
1123 INSERT INTO pa_fp_rollup_tmp(
1124 RESOURCE_ASSIGNMENT_ID,
1125 START_DATE,
1126 END_DATE,
1127 PERIOD_NAME,
1128 QUANTITY,
1129 TXN_CURRENCY_CODE,
1130 TXN_RAW_COST,
1131 TXN_BURDENED_COST,
1132 PROJECT_RAW_COST,
1133 PROJECT_BURDENED_COST,
1134 PROJFUNC_RAW_COST,
1135 PROJFUNC_BURDENED_COST,
1136 BUDGET_LINE_ID,
1137 BILLABLE_FLAG )
1138 VALUES(
1139 l_res_asg_id(i),
1140 l_bl_start_date,
1141 l_bl_end_date,
1142 l_bl_period_name,
1143 l_quantity_sum_tab(i),
1144 l_currency_code(i),
1145 l_raw_cost_sum(i),
1146 l_burdened_cost_sum(i),
1147 l_proj_raw_cost_sum(i),
1148 l_proj_burdened_cost_sum(i),
1149 l_projfunc_raw_cost_sum(i),
1150 l_projfunc_burdened_cost_sum(i),
1151 l_bl_id_counter,
1152 l_billable_flag );
1153
1154 ELSE -- l_cost_based_all_from_sp_flag = 'N'
1155
1156 /*====================================================================================+
1157 | If no budget lines exist for the Resource Assignment Id and the Currency Code, |
1158 | Insert fresh Budget Lines. |
1159 | |
1160 | Bug 4251148: Modified this logic. If the Target is GL/PA timephased, we now also |
1161 | check budget line start date when checking for budget line existence. |
1162 | If Calculate API call is required, then check the Target time phase: |
1163 | PA/GL: Populate the l_cal_ tables for further processing. In this case, we |
1164 | will call Calculate at the Budget Line level, which requires that |
1165 | we do not have budget lines. Therefore, bypass budget line Insert. |
1166 | None: Populate the l_cal_ tables with just Resource Assignment Id and the |
1167 | Currency Code (amounts unneccessary). In this case, we will call the |
1168 | Calculate API at the Resource Assignment level with Partial Refresh |
1169 | of Revenue amounts. This requires that we have budget lines populated.|
1170 | Therefore, still do the Insert. |
1171 +====================================================================================*/
1172
1173 -- Initialize rate overrides and exchange rates
1174 l_txn_cost_rate_override := NULL;
1175 l_burden_cost_rate_override := NULL;
1176 l_proj_cost_exchange_rate := NULL;
1177 l_projfunc_cost_exchange_rate := NULL;
1178
1179 IF ( l_budget_lines_exist = 'N' ) THEN
1180 IF l_calc_api_required_flag = 'Y' AND
1181 p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1182 l_index := l_index + 1;
1183 l_cal_tgt_res_asg_id_tab(l_index) := l_res_asg_id(i);
1184 l_cal_tgt_rate_based_flag_tab(l_index) := l_rate_based_flag;
1185 l_cal_txn_currency_code_tab(l_index) := l_currency_code(i);
1186 l_cal_line_start_date_tab(l_index) := l_bl_start_date;
1187 l_cal_line_end_date_tab(l_index) := l_bl_end_date;
1188
1189 l_cal_cmt_quantity_tab(l_index) := l_quantity_sum_tab(i);
1190 l_cal_cmt_raw_cost_tab(l_index) := l_raw_cost_sum(i);
1191 l_cal_cmt_brdn_cost_tab(l_index) := l_burdened_cost_sum(i);
1192
1193 l_cal_base_quantity_tab(l_index) := 0;
1194 l_cal_base_raw_cost_tab(l_index) := 0;
1195 l_cal_base_brdn_cost_tab(l_index) := 0;
1196 ELSE
1197 IF l_calc_api_required_flag = 'Y' AND
1198 p_fp_cols_rec.x_time_phased_code = 'N' THEN
1199 l_index := l_index + 1;
1200 l_cal_tgt_res_asg_id_tab(l_index) := l_res_asg_id(i);
1201 l_cal_txn_currency_code_tab(l_index) := l_currency_code(i);
1202 END IF; -- Calc Required and None Time Phase
1203
1204 IF l_quantity_sum_tab(i) <> 0 THEN
1205 l_txn_cost_rate_override := l_raw_cost_sum(i) / l_quantity_sum_tab(i);
1206 l_burden_cost_rate_override := l_burdened_cost_sum(i) / l_quantity_sum_tab(i);
1207 END IF;
1208 IF l_raw_cost_sum(i) <> 0 THEN
1209 l_proj_cost_exchange_rate := l_proj_raw_cost_sum(i) / l_raw_cost_sum(i);
1210 l_projfunc_cost_exchange_rate := l_projfunc_raw_cost_sum(i) / l_raw_cost_sum(i);
1211 END IF;
1212
1213 INSERT INTO PA_BUDGET_LINES(RESOURCE_ASSIGNMENT_ID,
1214 START_DATE,
1215 END_DATE,
1216 PERIOD_NAME,
1217 TXN_CURRENCY_CODE,
1218 TXN_RAW_COST,
1219 TXN_BURDENED_COST,
1220 PROJECT_RAW_COST,
1221 PROJECT_BURDENED_COST,
1222 RAW_COST,
1223 BURDENED_COST,
1224 QUANTITY,
1225 BUDGET_LINE_ID,
1226 BUDGET_VERSION_ID,
1227 PROJECT_CURRENCY_CODE,
1228 PROJFUNC_CURRENCY_CODE,
1229 LAST_UPDATE_DATE,
1230 LAST_UPDATED_BY,
1231 CREATION_DATE,
1232 CREATED_BY,
1233 LAST_UPDATE_LOGIN,
1234 TXN_COST_RATE_OVERRIDE,
1235 BURDEN_COST_RATE_OVERRIDE,
1236 PROJECT_COST_EXCHANGE_RATE,
1237 PROJFUNC_COST_EXCHANGE_RATE,
1238 PROJECT_COST_RATE_TYPE,
1239 PROJFUNC_COST_RATE_TYPE
1240 )
1241 VALUES(l_res_asg_id(i),
1242 l_bl_start_date,
1243 l_bl_end_date,
1244 l_bl_period_name,
1245 l_currency_code(i),
1246 l_raw_cost_sum(i),
1247 l_burdened_cost_sum(i),
1248 l_proj_raw_cost_sum(i),
1249 l_proj_burdened_cost_sum(i),
1250 l_projfunc_raw_cost_sum(i),
1251 l_projfunc_burdened_cost_sum(i),
1252 l_quantity_sum_tab(i),
1253 pa_budget_lines_s.nextval,
1254 p_budget_version_id,
1255 p_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
1256 p_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE,
1257 l_sysdate,
1258 l_last_updated_by,
1259 l_sysdate,
1260 l_last_updated_by,
1261 l_last_update_login,
1262 l_txn_cost_rate_override,
1263 l_burden_cost_rate_override,
1264 l_proj_cost_exchange_rate,
1265 l_projfunc_cost_exchange_rate,
1266 'User',
1267 'User'
1268 );
1269 END IF; -- l_calc_api_required_flag check
1270
1271 ELSE --l_budget_lines_exist = 'Y'
1272
1273 /*====================================================================================+
1274 | If budget lines exist for the Resource Assignment Id and Currency Code, do Update |
1275 | |
1276 | Bug 4251148: Modified this logic. If the Target is GL/PA timephased, we now also |
1277 | check budget line start date when checking for budget line existence. |
1278 | If Calculate API call is required, then check the Target time phase: |
1279 | PA/GL: Populate the l_cal_ tables for further processing. In this case, we |
1280 | will call Calculate at the Budget Line level, which requires that |
1281 | we do not have budget lines. Therefore, Updating the budget line is |
1282 | not needed. In fact, we track all existing budget lines and DELETE |
1283 | them later (before calling Calculate). |
1284 | None: Populate the l_cal_ tables with just Resource Assignment Id and the |
1285 | Currency Code (amounts unneccessary). In this case, we will call the |
1286 | Calculate API at the Resource Assignment level with Partial Refresh |
1287 | of Revenue amounts. This requires that we have budget lines populated.|
1288 | Therefore, still do the Update. |
1289 +====================================================================================*/
1290
1291 ---if the record does exist then update the record in the pa_budget_lines table
1292 IF l_calc_api_required_flag = 'Y' AND
1293 p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1294 -- These budget lines will be deleted before calling Calculate
1295 bl_index := bl_index + 1;
1296 l_budget_line_id_tab(bl_index) := l_budget_line_id;
1297
1298 l_index := l_index + 1;
1299 l_cal_tgt_res_asg_id_tab(l_index) := l_res_asg_id(i);
1300 l_cal_tgt_rate_based_flag_tab(l_index) := l_rate_based_flag;
1301 l_cal_txn_currency_code_tab(l_index) := l_currency_code(i);
1302 l_cal_line_start_date_tab(l_index) := l_bl_start_date;
1303 l_cal_line_end_date_tab(l_index) := l_bl_end_date;
1304
1305 l_cal_cmt_quantity_tab(l_index) := l_quantity_sum_tab(i);
1306 l_cal_cmt_raw_cost_tab(l_index) := l_raw_cost_sum(i);
1307 l_cal_cmt_brdn_cost_tab(l_index) := l_burdened_cost_sum(i);
1308
1309 l_cal_base_quantity_tab(l_index) := l_bl_quantity;
1310 l_cal_base_raw_cost_tab(l_index) := l_bl_txn_raw_cost;
1311 l_cal_base_brdn_cost_tab(l_index) := l_bl_txn_burdened_cost;
1312 ELSE
1313 IF l_calc_api_required_flag = 'Y' AND
1314 p_fp_cols_rec.x_time_phased_code = 'N' THEN
1315 l_index := l_index + 1;
1316 l_cal_tgt_res_asg_id_tab(l_index) := l_res_asg_id(i);
1317 l_cal_txn_currency_code_tab(l_index) := l_currency_code(i);
1318 END IF; -- Calc Required and None Timephase
1319
1320 l_upd_quantity := nvl(l_bl_quantity,0) + l_quantity_sum_tab(i);
1321 l_upd_txn_raw_cost := nvl(l_bl_txn_raw_cost,0) + l_raw_cost_sum(i);
1322 l_upd_txn_burdened_cost := nvl(l_bl_txn_burdened_cost,0) + l_burdened_cost_sum(i);
1323 l_upd_project_raw_cost := nvl(l_bl_project_raw_cost,0) + l_proj_raw_cost_sum(i);
1324 l_upd_project_burdened_cost := nvl(l_bl_project_burdened_cost,0) + l_proj_burdened_cost_sum(i);
1325 l_upd_pfc_raw_cost := nvl(l_bl_pfc_raw_cost,0) + l_projfunc_raw_cost_sum(i);
1326 l_upd_pfc_burdened_cost := nvl(l_bl_pfc_burdened_cost,0) + l_projfunc_burdened_cost_sum(i);
1327
1328 -- Rate overrides and exchange rates are initially null
1329 IF p_fp_cols_rec.x_time_phased_code = 'N' AND
1330 p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
1331 -- In this case, the bl amounts include the actual amounts.
1332 -- To compute the rates, we should use (amounts less actuals).
1333 IF (l_upd_quantity - l_bl_init_quantity) <> 0 THEN
1334 l_txn_cost_rate_override :=
1335 (l_upd_txn_raw_cost - l_bl_txn_init_raw_cost) /
1336 (l_upd_quantity - l_bl_init_quantity);
1337 l_burden_cost_rate_override :=
1338 (l_upd_txn_burdened_cost - l_bl_txn_init_burdened_cost) /
1339 (l_upd_quantity - l_bl_init_quantity);
1340 END IF;
1341 IF (l_upd_txn_raw_cost - l_bl_txn_init_raw_cost) <> 0 THEN
1342 l_proj_cost_exchange_rate :=
1343 (l_upd_project_raw_cost - l_bl_project_init_raw_cost) /
1344 (l_upd_txn_raw_cost - l_bl_txn_init_raw_cost);
1345 l_projfunc_cost_exchange_rate :=
1346 (l_upd_pfc_raw_cost - l_bl_pfc_init_raw_cost) /
1347 (l_upd_txn_raw_cost - l_bl_txn_init_raw_cost);
1348 END IF;
1349 ELSE
1350 IF l_upd_quantity <> 0 THEN
1351 l_txn_cost_rate_override := l_upd_txn_raw_cost / l_upd_quantity;
1352 l_burden_cost_rate_override := l_upd_txn_burdened_cost / l_upd_quantity;
1353 END IF;
1354 IF l_upd_txn_raw_cost <> 0 THEN
1355 l_proj_cost_exchange_rate := l_upd_project_raw_cost / l_upd_txn_raw_cost;
1356 l_projfunc_cost_exchange_rate := l_upd_pfc_raw_cost / l_upd_txn_raw_cost;
1357 END IF;
1358 END IF; -- computation of rates
1359
1360 /*dbms_output.put_line('Updating bl table');
1361 dbms_output.put_line('Time phase: '||p_fp_cols_rec.x_time_phased_code);
1362 dbms_output.put_line('Start_date: '||l_bl_start_date);*/
1363
1364
1365 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1366 UPDATE PA_BUDGET_LINES
1367 SET LAST_UPDATE_DATE = l_sysdate
1368 , LAST_UPDATED_BY = l_last_updated_by
1369 , LAST_UPDATE_LOGIN = l_last_update_login
1370 -- , START_DATE = l_bl_start_date
1371 -- , END_DATE = l_bl_end_date
1372 , QUANTITY = l_upd_quantity
1373 , TXN_RAW_COST = l_upd_txn_raw_cost
1374 , TXN_BURDENED_COST = l_upd_txn_burdened_cost
1375 , PROJECT_RAW_COST = l_upd_project_raw_cost
1376 , PROJECT_BURDENED_COST = l_upd_project_burdened_cost
1377 , RAW_COST = l_upd_pfc_raw_cost
1378 , BURDENED_COST = l_upd_pfc_burdened_cost
1379 , PROJECT_COST_RATE_TYPE = 'User'
1380 , PROJFUNC_COST_RATE_TYPE = 'User'
1381 , txn_cost_rate_override = l_txn_cost_rate_override
1382 , burden_cost_rate_override = l_burden_cost_rate_override
1383 , project_cost_exchange_rate = l_proj_cost_exchange_rate
1384 , projfunc_cost_exchange_rate = l_projfunc_cost_exchange_rate
1385 WHERE BUDGET_LINE_ID = l_budget_line_id;
1386 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1387 UPDATE PA_BUDGET_LINES
1388 SET LAST_UPDATE_DATE = l_sysdate
1389 , LAST_UPDATED_BY = l_last_updated_by
1390 , LAST_UPDATE_LOGIN = l_last_update_login
1391 , START_DATE = l_bl_start_date
1392 , END_DATE = l_bl_end_date
1393 , QUANTITY = l_upd_quantity
1394 , TXN_RAW_COST = l_upd_txn_raw_cost
1395 , TXN_BURDENED_COST = l_upd_txn_burdened_cost
1396 , PROJECT_RAW_COST = l_upd_project_raw_cost
1397 , PROJECT_BURDENED_COST = l_upd_project_burdened_cost
1398 , RAW_COST = l_upd_pfc_raw_cost
1399 , BURDENED_COST = l_upd_pfc_burdened_cost
1400 , PROJECT_COST_RATE_TYPE = 'User'
1401 , PROJFUNC_COST_RATE_TYPE = 'User'
1402 , txn_cost_rate_override = l_txn_cost_rate_override
1403 , burden_cost_rate_override = l_burden_cost_rate_override
1404 , project_cost_exchange_rate = l_proj_cost_exchange_rate
1405 , projfunc_cost_exchange_rate = l_projfunc_cost_exchange_rate
1406 WHERE BUDGET_LINE_ID = l_budget_line_id;
1407 END IF; -- update
1408
1409 --dbms_output.put_line('inserted [' || to_char(sql%rowcount) || '] records');
1410
1411 END IF; -- l_calc_api_required_flag check
1412 END IF; -- budget line existence check
1413
1414 END IF; -- l_cost_based_all_from_sp_flag = 'Y' check
1415
1416 EXCEPTION
1417 WHEN CONTINUE_LOOP THEN
1418 l_dummy := 1;
1419 WHEN OTHERS THEN
1420 RAISE;
1421 END; -- continue loop exception block
1422 END LOOP; -- main loop
1423
1424 -- Bug 4549862: Now that we have finished the main commitment processing
1425 -- loop, check if we encountered the rejection code error. If so, all the
1426 -- error messages have been added to the stack, so we can Raise an error.
1427
1428 IF l_rejection_code_error_flag = 'Y' THEN
1429 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1430 END IF;
1431
1432
1433 -- Bug 4549862: Note that no change is required to the
1434 -- l_calc_api_required_flag = 'Y' code below since the
1435 -- accrual method is always WORK (and not COST) when
1436 -- l_calc_api_required_flag is 'Y'.
1437
1438 IF l_calc_api_required_flag = 'Y' AND
1439 l_cal_tgt_res_asg_id_tab.count > 0 THEN
1440
1441 /* Populate Calculate API pl/sql table pameters */
1442 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1443
1444 /*==========================================================================+
1445 | Up to this point, we have collected Resource Assignment and Budget Line |
1446 | info in the l_cal_ pl/sql tables for budget lines for which we need to |
1447 | include Commitment amounts. For a given unique (Resource Assignment Id, |
1448 | Txn Curency Code, Budget Line Start Date) combination, we may have more |
1449 | than 1 commitment. In this case, we will have multiple records in the |
1450 | l_cal_ tables for the combination. To ensure that we only pass 1 record |
1451 | per such combination to the Calculate API, we have the following logic: |
1452 | 1. Populate PA_FP_ROLLUP_TMP with Commitment Amounts and the l_cal_ |
1453 | pl/sql table Index where the amounts came from. We use ROLLUP_ID to |
1454 | store the Index value. |
1455 | 2. Fetch Aggregated Commitment Amounts using Group By. |
1456 | Earlier, when we populated the l_cal_ tables, we made sure that the |
1457 | Base amounts were the same for each (ra_id, currency, start date) |
1458 | combination. So, we use Max(ROLLUP_ID) to get the Index of a l_cal_ |
1459 | record that has the Base amounts for the Group. |
1460 | 3. Add Base Amounts to Commitment Sums to get total Amount |
1461 | 4. Compute rate override values.
1462 +==========================================================================*/
1463
1464 -- Since bulk inserting the loop index variable i is syntactically
1465 -- not allowed, we populate an index table. This will give us the
1466 -- indices for entries in the l_cal_ tables.
1467 FOR i IN 1..l_cal_tgt_res_asg_id_tab.count LOOP
1468 l_index_tab(i) := i;
1469 END LOOP;
1470
1471 -- We need to sort the pl/sql records by (RA id, currency code, start date)
1472 -- The pa_fp_calc_amt_tmp1 table should be unused now; use it to sort.
1473 DELETE PA_FP_ROLLUP_TMP;
1474 FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1475 INSERT INTO PA_FP_ROLLUP_TMP (
1476 ROLLUP_ID, -- l_cal_ table index value
1477 RESOURCE_ASSIGNMENT_ID,
1478 TXN_CURRENCY_CODE,
1479 START_DATE,
1480 END_DATE,
1481 QUANTITY,
1482 TXN_RAW_COST,
1483 TXN_BURDENED_COST )
1484 VALUES (
1485 l_index_tab(i),
1486 l_cal_tgt_res_asg_id_tab(i),
1487 l_cal_txn_currency_code_tab(i),
1488 l_cal_line_start_date_tab(i),
1489 l_cal_line_end_date_tab(i),
1490 l_cal_cmt_quantity_tab(i),
1491 l_cal_cmt_raw_cost_tab(i),
1492 l_cal_cmt_brdn_cost_tab(i) );
1493
1494 l_index_tab.delete;
1495 -- Aggregate Commitment amounts using Group By
1496 /* Populate Calculate API pl/sql table parameters with aggregated
1497 * amounts from l_cal_ commitment pl/sql table records. We do this
1498 * aggregation so that existing budget line amounts are not double
1499 * counted in the amounts passed to the Calculate API. */
1500 SELECT RESOURCE_ASSIGNMENT_ID,
1501 TXN_CURRENCY_CODE,
1502 START_DATE,
1503 max(ROLLUP_ID), -- l_cal_ table index value
1504 max(END_DATE),
1505 sum(nvl(QUANTITY,0)),
1506 sum(nvl(TXN_RAW_COST,0)),
1507 sum(nvl(TXN_BURDENED_COST,0)),
1508 NULL, -- revenue
1509 NULL, -- cost rate override
1510 NULL, -- burden cost rate override
1511 NULL -- bill rate override
1512 BULK COLLECT
1513 INTO l_tgt_res_asg_id_tab,
1514 l_txn_currency_code_tab,
1515 l_line_start_date_tab,
1516 l_index_tab, -- l_cal_ table index value
1517 l_line_end_date_tab,
1518 l_src_quantity_tab,
1519 l_src_raw_cost_tab,
1520 l_src_brdn_cost_tab,
1521 l_src_revenue_tab,
1522 l_cost_rate_override_tab,
1523 l_b_cost_rate_override_tab,
1524 l_bill_rate_override_tab
1525 FROM PA_FP_ROLLUP_TMP
1526 GROUP BY RESOURCE_ASSIGNMENT_ID,
1527 TXN_CURRENCY_CODE,
1528 START_DATE;
1529
1530 FOR i IN 1..l_index_tab.count LOOP
1531 kk := l_index_tab(i);
1532 l_tgt_rate_based_flag_tab.extend;
1533 l_tgt_rate_based_flag_tab(i) := l_cal_tgt_rate_based_flag_tab(kk);
1534
1535 /* Add Base amounts to Commitment sums */
1536 l_src_quantity_tab(i) := l_src_quantity_tab(i) +
1537 l_cal_base_quantity_tab(kk);
1538 l_src_raw_cost_tab(i) := l_src_raw_cost_tab(i) +
1539 l_cal_base_raw_cost_tab(kk);
1540 l_src_brdn_cost_tab(i) := l_src_brdn_cost_tab(i) +
1541 l_cal_base_brdn_cost_tab(kk);
1542 END LOOP;
1543
1544 /* Compute rate override values now that we have aggregated amounts */
1545 FOR i IN 1..l_tgt_res_asg_id_tab.count LOOP
1546 IF l_src_quantity_tab(i) <> 0 THEN
1547 l_cost_rate_override_tab(i) := l_src_raw_cost_tab(i) / l_src_quantity_tab(i);
1548 l_b_cost_rate_override_tab(i) := l_src_brdn_cost_tab(i) / l_src_quantity_tab(i);
1549 END IF;
1550 END LOOP;
1551
1552 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1553
1554 /*==========================================================================+
1555 | Up to this point, we have collected Resource Assignment Ids and Currency |
1556 | Codes for in the l_cal_ pl/sql tables for budget lines for which we need |
1557 | to include Commitment amounts. For a given unique (Resource Assignment |
1558 | Id, Txn Curency Code) pair, we may have more than 1 commitment. In this |
1559 | case, we will have duplicate records l_cal_ tables. We should only pass |
1560 | 1 record per such pair to the Calculate API. The following logic gets |
1561 | distinct records for the pl/sql tables parameters for the Calculate API. |
1562 | |
1563 | Since we are calling Calculate at the Resource Assignment level with |
1564 | Partial Refresh on Revenue, we do not need to pass pl/sql tables with |
1565 | amounts and rate overrides as in the case of PA/GL. We just need to pass |
1566 | one table for Resource Assignment Ids, and one table for Currency Codes. |
1567 +==========================================================================*/
1568
1569 -- We need to get distinct (RA id, currency code) pairs.
1570 -- The pa_fp_rollup_tmp table should be unused now; use it.
1571 DELETE PA_FP_ROLLUP_TMP;
1572 FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1573 INSERT INTO PA_FP_ROLLUP_TMP (
1574 RESOURCE_ASSIGNMENT_ID,
1575 TXN_CURRENCY_CODE ) -- txn_currency_code
1576 VALUES (
1577 l_cal_tgt_res_asg_id_tab(i),
1578 l_cal_txn_currency_code_tab(i) );
1579
1580 SELECT DISTINCT
1581 RESOURCE_ASSIGNMENT_ID,
1582 TXN_CURRENCY_CODE
1583 BULK COLLECT
1584 INTO l_tgt_res_asg_id_tab,
1585 l_txn_currency_code_tab
1586 FROM PA_FP_ROLLUP_TMP;
1587
1588 END IF; -- population of Calculate API pl/sql tables
1589
1590
1591 /*=======================================================================+
1592 | Bug 4582616 Fix Overview |
1593 | ------------------------ |
1594 | The reported issue is that Source Rates are not being used to compute |
1595 | ETC Revenue for resources that do not have Uncommitted ETC when: |
1596 | C1. the Source for the resource is Financial Plan |
1597 | C2. the Source and Target are both Cost and Revenue Together versions |
1598 | C3. Source/Target Planning Options match. |
1599 | This is happening because, in the GEN_FCST_TASK_LEVEL_AMT API, we do |
1600 | not process resources that do not have Uncommitted ETC. As a result, |
1601 | source rates are not populated in PA_FP_GEN_RATE_TMP for the resource |
1602 | and Calculate derives the revenue using the Target's Rate Schedule. |
1603 | The same issue should arise during Budget Generation when C1-C3 hold. |
1604 | |
1605 | The fix is to populate PA_FP_GEN_RATE_TMP with periodic source rates |
1606 | for resources when they do not already exist AND conditions 1-3 hold. |
1607 | To populate the rates, do the following: |
1608 | 1. Verify version-level conditions C2 and C3 |
1609 | 2. Fetch distinct (Source Resource Assignment Id, Target Resource |
1610 | Assignment Id, Txn Currency) triples that: |
1611 | A. are billable |
1612 | B. do not have records in pa_fp_gen_rate_tmp for the |
1613 | (Target Resource Assignment Id, Txn Currency) combo. |
1614 | C. have planning resources in the source financial plan (i.e. C1). |
1615 | 3. For each triple from Step 2, call the POPULATE_GEN_RATE API. |
1616 | 4. If l_source_context is 'BUDGET_LINE', null-out the Raw Cost Rate |
1617 | and Burdened Cost Rate columns of PA_FP_GEN_RATE_TMP since we only |
1618 | want the Calculate API to compute revenue and honor Revenue Rates. |
1619 | |
1620 | Note that l_tgt_res_asg_id_tab and l_txn_currency_code_tab contain |
1621 | are populated at this point and contain Distinct values. |
1622 +=======================================================================*/
1623
1624 l_fp_src_plan_ver_id := p_fp_cols_rec.x_gen_src_plan_version_id;
1625
1626 -- Note that l_calc_api_required_flag = 'Y' in the current context.
1627 -- This guarantees that the Target is a Cost and Revenue Together
1628 -- version with Revenue Accrual Method as Work.
1629
1630 IF l_fp_src_plan_ver_id IS NOT NULL AND
1631 l_gen_src_code IN ('FINANCIAL_PLAN','TASK_LEVEL_SEL') THEN
1632
1633 -- Get Source Financial Plan Details
1634 IF P_PA_DEBUG_MODE = 'Y' THEN
1635 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1636 P_MSG => 'Before calling PA_FP_GEN_AMOUNT_UTILS.'||
1637 'GET_PLAN_VERSION_DTL',
1638 P_MODULE_NAME => l_module_name,
1639 P_LOG_LEVEL => 5);
1640 END IF;
1641 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS(
1642 P_PROJECT_ID => P_PROJECT_ID,
1643 P_BUDGET_VERSION_ID => l_fp_src_plan_ver_id,
1644 X_FP_COLS_REC => l_fp_cols_rec_src_finplan,
1645 X_RETURN_STATUS => x_return_status,
1646 X_MSG_COUNT => x_msg_count,
1647 X_MSG_DATA => x_msg_data);
1648 IF P_PA_DEBUG_MODE = 'Y' THEN
1649 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1650 P_MSG => 'After calling PA_FP_GEN_AMOUNT_UTILS.'||
1651 'GET_PLAN_VERSION_DTL:'||x_return_status,
1652 P_MODULE_NAME => l_module_name,
1653 P_LOG_LEVEL => 5);
1654 END IF;
1655 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1656 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1657 END IF;
1658
1659 -- Bug 4582616 Step 1: Verify C2 - that the Source and Target are
1660 -- both Cost and Revenue Together versions. We already know that
1661 -- the Target version is 'ALL' since l_calc_api_required_flag = 'Y'.
1662 -- So, just check the Source version.
1663
1664 IF l_fp_cols_rec_src_finplan.x_version_type = 'ALL' THEN
1665
1666 -- Get Planning Options Flag for Source Financial Plan
1667 IF P_PA_DEBUG_MODE = 'Y' THEN
1668 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1669 P_MSG => 'Before calling PA_FP_FCST_GEN_AMT_UTILS.'||
1670 'COMPARE_ETC_SRC_TARGET_FP_OPT',
1671 P_MODULE_NAME => l_module_name,
1672 P_LOG_LEVEL => 5);
1673 END IF;
1674 PA_FP_FCST_GEN_AMT_UTILS.COMPARE_ETC_SRC_TARGET_FP_OPT
1675 (P_PROJECT_ID => P_PROJECT_ID,
1676 P_WP_SRC_PLAN_VER_ID => null,
1677 P_FP_SRC_PLAN_VER_ID => l_fp_src_plan_ver_id,
1678 P_FP_TARGET_PLAN_VER_ID => P_BUDGET_VERSION_ID,
1679 X_SAME_PLANNING_OPTION_FLAG => l_fp_planning_options_flag,
1680 X_RETURN_STATUS => X_RETURN_STATUS,
1681 X_MSG_COUNT => X_MSG_COUNT,
1682 X_MSG_DATA => X_MSG_DATA);
1683 IF P_PA_DEBUG_MODE = 'Y' THEN
1684 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1685 P_MSG => 'After calling PA_FP_FCST_GEN_AMT_UTILS.'||
1686 'COMPARE_ETC_SRC_TARGET_FP_OPT:'||
1687 l_fp_planning_options_flag,
1688 P_MODULE_NAME => l_module_name,
1689 P_LOG_LEVEL => 5);
1690 END IF;
1691 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1692 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1693 END IF;
1694
1695 -- Bug 4582616 Step 1 (continued): Verify C3 - that the Source /
1696 -- Target Planning Options match.
1697
1698 IF l_fp_planning_options_flag = 'Y' THEN
1699
1700 -- Bug 4582616 Step 2: Fetch distinct (Source Resource Assignment
1701 -- Id, Target Resource Assignment Id, Txn Currency) triples that:
1702 -- A. are billable
1703 -- B. do not have records in pa_fp_gen_rate_tmp for the
1704 -- (Target Resource Assignment Id, Txn Currency) combo.
1705 -- C. have planning resources in the source financial plan.
1706 -- The pa_fp_rollup_tmp table should be unused now, so use it
1707 -- to do the above processing logic in bulk.
1708
1709 DELETE PA_FP_ROLLUP_TMP;
1710 FORALL i IN 1..l_tgt_res_asg_id_tab.count
1711 INSERT INTO PA_FP_ROLLUP_TMP (
1712 RESOURCE_ASSIGNMENT_ID,
1713 TXN_CURRENCY_CODE )
1714 VALUES (
1715 l_tgt_res_asg_id_tab(i),
1716 l_txn_currency_code_tab(i) );
1717
1718 -- The correctness of the following SELECT statements relies
1719 -- heavily on the current context - in particular, the Source
1720 -- version is a Financial Plan and the Source/Target Planning
1721 -- Options match (i.e. there is a 1-to-1 mapping from Source
1722 -- to Target resources).
1723
1724 IF l_calling_context = lc_BudgetGeneration THEN
1725
1726 SELECT src_ra.RESOURCE_ASSIGNMENT_ID,
1727 tgt_ra.RESOURCE_ASSIGNMENT_ID,
1728 cmt.TXN_CURRENCY_CODE
1729 BULK COLLECT
1730 INTO l_sr_src_ra_id_tab,
1731 l_sr_tgt_ra_id_tab,
1732 l_sr_txn_currency_code_tab
1733 FROM pa_resource_assignments src_ra,
1734 pa_resource_assignments tgt_ra,
1735 pa_tasks ta,
1736 pa_fp_rollup_tmp cmt
1737 WHERE tgt_ra.resource_assignment_id = cmt.resource_assignment_id
1738 AND ta.task_id (+) = NVL(tgt_ra.task_id,0) -- A. check billability
1739 AND NVL(ta.billable_flag,'Y') = 'Y' -- A. check billability
1740 AND src_ra.task_id = tgt_ra.task_id
1741 AND src_ra.resource_list_member_id = tgt_ra.resource_list_member_id
1742 AND tgt_ra.budget_version_id = p_budget_version_id
1743 AND src_ra.budget_version_id = l_fp_src_plan_ver_id
1744 AND tgt_ra.project_id = p_project_id
1745 AND src_ra.project_id = p_project_id
1746 AND NOT EXISTS ( SELECT null -- B. check for existing gen_tmp records
1747 FROM pa_fp_gen_rate_tmp gen_tmp
1748 WHERE gen_tmp.TARGET_RES_ASG_ID = tgt_ra.resource_assignment_id
1749 AND gen_tmp.txn_currency_code = cmt.txn_currency_code );
1750
1751 ELSIF l_calling_context = lc_ForecastGeneration THEN
1752
1753 SELECT tmp1.RESOURCE_ASSIGNMENT_ID,
1754 tmp1.TARGET_RES_ASG_ID,
1755 cmt.TXN_CURRENCY_CODE
1756 BULK COLLECT
1757 INTO l_sr_src_ra_id_tab,
1758 l_sr_tgt_ra_id_tab,
1759 l_sr_txn_currency_code_tab
1760 FROM pa_fp_calc_amt_tmp1 tmp1,
1761 pa_tasks ta,
1762 pa_fp_rollup_tmp cmt
1763 WHERE tmp1.transaction_source_code = 'FINANCIAL_PLAN' -- C. check finplan
1764 AND tmp1.target_res_asg_id = cmt.resource_assignment_id
1765 AND ta.task_id (+) = NVL(tmp1.task_id,0) -- A. check billability
1766 AND NVL(ta.billable_flag,'Y') = 'Y' -- A. check billability
1767 AND NOT EXISTS ( SELECT null -- B. check for existing gen_tmp records
1768 FROM pa_fp_gen_rate_tmp gen_tmp
1769 WHERE gen_tmp.target_res_asg_id = tmp1.target_res_asg_id
1770 AND gen_tmp.txn_currency_code = cmt.txn_currency_code );
1771
1772 END IF; -- l_calling_context check
1773
1774 -- Bug 4582616 Step 3: Populate PA_FP_GEN_RATE_TMP with missing
1775 -- Periodic Source Rates.
1776
1777 FOR i IN 1..l_sr_src_ra_id_tab.count LOOP
1778
1779 IF P_PA_DEBUG_MODE = 'Y' THEN
1780 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1781 P_MSG =>
1782 'Before calling PA_FP_GEN_FCST_AMT_PUB1.POPULATE_GEN_RATE',
1783 P_MODULE_NAME => l_module_name,
1784 P_LOG_LEVEL => 5);
1785 END IF;
1786 PA_FP_GEN_FCST_AMT_PUB1.POPULATE_GEN_RATE
1787 (P_SOURCE_RES_ASG_ID => l_sr_src_ra_id_tab(i),
1788 P_TARGET_RES_ASG_ID => l_sr_tgt_ra_id_tab(i),
1789 P_TXN_CURRENCY_CODE => l_sr_txn_currency_code_tab(i),
1790 X_RETURN_STATUS => x_return_status,
1791 X_MSG_COUNT => x_msg_count,
1792 X_MSG_DATA => x_msg_data);
1793 IF P_PA_DEBUG_MODE = 'Y' THEN
1794 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1795 P_MSG =>
1796 'After calling PA_FP_GEN_FCST_AMT_PUB1.POPULATE_GEN_RATE: '||x_return_status,
1797 P_MODULE_NAME => l_module_name,
1798 P_LOG_LEVEL => 5);
1799 END IF;
1800 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1801 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1802 END IF;
1803
1804 END LOOP; -- FOR i IN 1..l_sr_src_ra_id_tab.count LOOP
1805
1806 END IF; -- l_fp_planning_options_flag = 'Y'
1807
1808 END IF; -- l_fp_cols_rec_src_finplan.x_version_type = 'ALL'
1809
1810 END IF; -- l_fp_src_plan_ver_id IS NOT NULL AND
1811 -- l_gen_src_code IN ('FINANCIAL_PLAN','TASK_LEVEL_SEL')
1812
1813 /* End of Bug Fix 4582616 Steps 1-3 */
1814
1815
1816 -- Bug 4320171: As part of the changes for this bug:
1817 -- Initialize unused Calculate API parameter tables with NULLs
1818 FOR i in 1 .. l_tgt_res_asg_id_tab.count LOOP
1819 l_raw_cost_rate_tab.extend;
1820 l_b_cost_rate_tab.extend;
1821 l_bill_rate_tab.extend;
1822
1823 l_raw_cost_rate_tab(i) := Null;
1824 l_b_cost_rate_tab(i) := Null;
1825 l_bill_rate_tab(i) := Null;
1826 END LOOP;
1827 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1828 -- Bug 5073259: Due to changes in the Calculate API, quantity
1829 -- and rates are ignored for non-rate-based resources. Instead
1830 -- of nulling out amount tables, do nothing here.
1831 l_dummy := 1;
1832 /*********** COMMENTED OUT ******************
1833 FOR i in 1 .. l_tgt_res_asg_id_tab.count LOOP
1834 l_src_raw_cost_tab(i) := null;
1835 l_src_brdn_cost_tab(i) := null;
1836 END LOOP;
1837 ************ END COMMENTING *****************/
1838 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1839 FOR i in 1 .. l_tgt_res_asg_id_tab.count LOOP
1840 l_src_quantity_tab.extend;
1841 l_src_raw_cost_tab.extend;
1842 l_src_brdn_cost_tab.extend;
1843 l_src_revenue_tab.extend;
1844 l_cost_rate_override_tab.extend;
1845 l_b_cost_rate_override_tab.extend;
1846 l_bill_rate_override_tab.extend;
1847 l_line_start_date_tab.extend;
1848 l_line_end_date_tab.extend;
1849
1850 l_src_quantity_tab(i) := null;
1851 l_src_raw_cost_tab(i) := null;
1852 l_src_brdn_cost_tab(i) := null;
1853 l_src_revenue_tab(i) := null;
1854 l_cost_rate_override_tab(i) := null;
1855 l_b_cost_rate_override_tab(i) := null;
1856 l_bill_rate_override_tab(i) := null;
1857 l_line_start_date_tab(i) := null;
1858 l_line_end_date_tab(i) := null;
1859 END LOOP;
1860 END IF;
1861
1862 -- Bug 4549862: Moved initialization of l_calling_context
1863 -- from here to the beginning of the API.
1864
1865 -- Set default values for Calculate API flag parameters
1866 l_refresh_rates_flag := 'N';
1867 l_refresh_conv_rates_flag := 'N';
1868 l_spread_required_flag := 'N';
1869 l_rollup_required_flag := 'N';
1870 l_raTxn_rollup_api_call_flag := 'N'; -- Added for IPM new entity ER
1871
1872
1873 IF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1874 l_source_context := 'RESOURCE_ASSIGNMENT';
1875 l_refresh_rates_flag := 'R';
1876 ELSE
1877 l_source_context := 'BUDGET_LINE';
1878
1879 -- Since the source context is Budget Line, before calling the Calculate
1880 -- API, we need to delete existing budget lines for which we need to
1881 -- populate Commitment amounts.
1882 FORALL i IN 1.. l_budget_line_id_tab.count
1883 DELETE FROM PA_BUDGET_LINES
1884 WHERE budget_line_id = l_budget_line_id_tab(i);
1885
1886 -- Bug 4582616 Step 4: Delete Raw Cost and Burdened Cost Rates from
1887 -- the PA_FP_GEN_RATE_TMP table since we only want the Calculate API
1888 -- to compute revenue and honor Revenue Rates. This Update is not
1889 -- necessary when the Target is None timephased because Calculate
1890 -- should ignore the cost rates when l_refresh_rates_flag is 'R'.
1891
1892 UPDATE pa_fp_gen_rate_tmp
1893 SET raw_cost_rate = null,
1894 burdened_cost_rate = null;
1895
1896 END IF; -- None time phase check
1897
1898 --hr_utility.trace('bef calling calculate api:'||x_return_status);
1899 --Calling the calculate API
1900 IF P_PA_DEBUG_MODE = 'Y' THEN
1901 pa_fp_gen_amount_utils.fp_debug
1902 (p_msg => 'Before calling
1903 PA_FP_CALC_PLAN_PKG.calculate',
1904 p_module_name => l_module_name,
1905 p_log_level => 5);
1906 END IF;
1907 PA_FP_CALC_PLAN_PKG.calculate(
1908 p_project_id => P_PROJECT_ID,
1909 p_budget_version_id => P_BUDGET_VERSION_ID,
1910 p_refresh_rates_flag => l_refresh_rates_flag,
1911 p_refresh_conv_rates_flag => l_refresh_conv_rates_flag,
1912 p_spread_required_flag => l_spread_required_flag,
1913 p_rollup_required_flag => l_rollup_required_flag,
1914 p_source_context => l_source_context,
1915 p_resource_assignment_tab => l_tgt_res_asg_id_tab,
1916 p_txn_currency_code_tab => l_txn_currency_code_tab,
1917 p_total_qty_tab => l_src_quantity_tab,
1918 p_total_raw_cost_tab => l_src_raw_cost_tab,
1919 p_total_burdened_cost_tab => l_src_brdn_cost_tab,
1920 p_total_revenue_tab => l_src_revenue_tab,
1921 p_raw_cost_rate_tab => l_raw_cost_rate_tab,
1922 p_rw_cost_rate_override_tab => l_cost_rate_override_tab,
1923 p_b_cost_rate_tab => l_b_cost_rate_tab,
1924 p_b_cost_rate_override_tab => l_b_cost_rate_override_tab,
1925 p_bill_rate_tab => l_bill_rate_tab,
1926 p_bill_rate_override_tab => l_bill_rate_override_tab,
1927 p_line_start_date_tab => l_line_start_date_tab,
1928 p_line_end_date_tab => l_line_end_date_tab,
1929 p_calling_module => l_calling_context,
1930 p_raTxn_rollup_api_call_flag => l_raTxn_rollup_api_call_flag,
1931 x_return_status => x_return_status,
1932 x_msg_count => x_msg_count,
1933 x_msg_data => x_msg_data);
1934 --hr_utility.trace('aft calling calculate api: '||x_return_status);
1935 IF P_PA_DEBUG_MODE = 'Y' THEN
1936 pa_fp_gen_amount_utils.fp_debug
1937 (p_msg => 'Status after calling
1938 PA_FP_CALC_PLAN_PKG.calculate: '
1939 ||x_return_status,
1940 p_module_name => l_module_name,
1941 p_log_level => 5);
1942 END IF;
1943 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1944 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1945 END IF;
1946
1947 END IF; -- l_calc_api_required_flag check
1948
1949 IF P_PA_DEBUG_MODE = 'Y' THEN
1950 PA_DEBUG.Reset_Curr_Function;
1951 END IF;
1952
1953 EXCEPTION
1954 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1955 -- Bug Fix: 4569365. Removed MRC code.
1956 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
1957 l_msg_count := FND_MSG_PUB.count_msg;
1958 IF l_msg_count = 1 THEN
1959 PA_INTERFACE_UTILS_PUB.get_messages
1960 (p_encoded => FND_API.G_TRUE
1961 ,p_msg_index => 1
1962 ,p_msg_count => l_msg_count
1963 ,p_msg_data => l_msg_data
1964 ,p_data => l_data
1965 ,p_msg_index_out => l_msg_index_out);
1966 x_msg_data := l_data;
1967 x_msg_count := l_msg_count;
1968 ELSE
1969 x_msg_count := l_msg_count;
1970 END IF;
1971 ROLLBACK;
1972
1973 x_return_status := FND_API.G_RET_STS_ERROR;
1974
1975 IF P_PA_DEBUG_MODE = 'Y' THEN
1976 PA_DEBUG.Reset_Curr_Function;
1977 END IF;
1978
1979 RAISE;
1980
1981 WHEN OTHERS THEN
1982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1983 x_msg_data := SUBSTR(SQLERRM,1,240);
1984 FND_MSG_PUB.add_exc_msg
1985 ( p_pkg_name => 'PA_FP_GEN_COMMITMENT_AMOUNTS'
1986 ,p_procedure_name => 'GEN_COMMITMENT_AMOUNTS');
1987
1988 IF P_PA_DEBUG_MODE = 'Y' THEN
1989 PA_DEBUG.Reset_Curr_Function;
1990 END IF;
1991
1992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1993
1994 END GEN_COMMITMENT_AMOUNTS;
1995
1996 END PA_FP_GEN_COMMITMENT_AMOUNTS;
[Home] [Help]