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