[Home] [Help]
PACKAGE BODY: APPS.PA_FP_COMMITMENT_AMOUNTS
Source
1 PACKAGE body PA_FP_COMMITMENT_AMOUNTS as
2 /* $Header: PAFPCMTB.pls 120.4 2010/06/15 06:56:57 paljain ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 PROCEDURE GET_COMMITMENT_AMTS
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, --File.Sql.39 bug 4440895
11 PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp, --File.Sql.39 bug 4440895
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 ,TMP.TXN_TASK_ID
28 ,TMP.RESOURCE_LIST_MEMBER_ID
29 ,DECODE(c_multi_curr_flag, 'Y',
30 CT.DENOM_CURRENCY_CODE,
31 CT.PROJECT_CURRENCY_CODE) currency_code
32 ,MAX(P.planning_start_date) planning_start_date
33 ,MAX(P.planning_end_date) planning_end_date
34 ,MIN(NVL(CT.CMT_NEED_BY_DATE,CT.EXPENDITURE_ITEM_DATE))
35 ,MAX(NVL(CT.CMT_NEED_BY_DATE,CT.EXPENDITURE_ITEM_DATE))
36 ,SUM(DECODE(c_multi_curr_flag, 'Y',
37 CT.DENOM_RAW_COST,
38 CT.PROJ_RAW_COST)) tot_raw_cost
39 ,SUM(DECODE(c_multi_curr_flag, 'Y',
40 CT.DENOM_BURDENED_COST,
41 CT.PROJ_BURDENED_COST)) tot_burdened_cost
42 ,SUM(CT.proj_raw_cost)
43 ,SUM(CT.proj_burdened_cost)
44 ,SUM(CT.acct_raw_cost)
45 ,SUM(CT.acct_burdened_cost)
46 ,SUM(NVL(CT.TOT_CMT_QUANTITY,
47 DECODE(c_multi_curr_flag, 'Y',
48 CT.DENOM_RAW_COST,
49 CT.PROJ_RAW_COST)) ) tot_quantity
50 FROM PA_COMMITMENT_TXNS CT,
51 PA_RES_LIST_MAP_TMP4 TMP,
52 PA_RESOURCE_ASSIGNMENTS P
53 WHERE TMP.TXN_SOURCE_ID = CT.CMT_LINE_ID
54 AND CT.PROJECT_ID = P_PROJECT_ID
55 AND NVL(CT.generation_error_flag,'N') = 'N'
56 AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
57 AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
58 GROUP BY P.RESOURCE_ASSIGNMENT_ID,
59 TMP.TXN_TASK_ID
60 ,TMP.RESOURCE_LIST_MEMBER_ID
61 ,DECODE(c_multi_curr_flag, 'Y',
62 CT.DENOM_CURRENCY_CODE,
63 CT.PROJECT_CURRENCY_CODE);
64
65
66 l_res_asg_id PA_PLSQL_DATATYPES.IdTabTyp;
67 l_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
68 l_tphase PA_PLSQL_DATATYPES.Char30TabTyp;
69 l_exp_itm_date PA_PLSQL_DATATYPES.DateTabTyp;
70 l_commstart_date PA_PLSQL_DATATYPES.DateTabTyp;
71 l_commend_date PA_PLSQL_DATATYPES.DateTabTyp;
72 l_ra_start_date PA_PLSQL_DATATYPES.DateTabTyp;
73 l_ra_end_date PA_PLSQL_DATATYPES.DateTabTyp;
74 l_raw_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
75 l_burdened_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
76 l_pc_raw_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
77 l_pc_burdened_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
78 l_pfc_raw_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
79 l_pfc_burdened_cost_sum PA_PLSQL_DATATYPES.NumTabTyp;
80 l_quantity_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
81 l_DELETED_RES_ASG_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
82 l_bl_raw_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
83 l_bl_burden_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
84 l_bl_quantity_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
85 l_unit_of_measure_tab PA_PLSQL_DATATYPES.Char30TabTyp;
86
87 l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE;
88 l_budget_line_id_tmp PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE;
89 l_qty_tmp NUMBER:= 0;
90 l_upd_count NUMBER:= 0;
91 l_bl_cmt_raw_diff NUMBER:= 0;
92 l_bl_cmt_burden_diff NUMBER:= 0;
93 l_bl_cmt_quantity_diff NUMBER:= 0;
94
95 l_txn_cost_rate_override PA_BUDGET_LINES.TXN_COST_RATE_OVERRIDE%TYPE;
96 l_burden_cost_rate_override PA_BUDGET_LINES.BURDEN_COST_RATE_OVERRIDE%TYPE;
97
98 l_appl_id NUMBER;
99 l_cnt NUMBER;
100
101 l_stru_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
102 l_budget_lines_exist VARCHAR2(1) ;
103 l_call_calculate VARCHAR2(1);
104
105 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
106 l_last_update_login NUMBER := FND_GLOBAL.login_id;
107 l_sysdate DATE := SYSDATE;
108 l_ret_status VARCHAR2(100);
109 l_msg_count NUMBER;
110 l_msg_data VARCHAR2(2000);
111 l_data VARCHAR2(2000);
112 l_msg_index_out NUMBER := 0;
113
114 l_res_assgn_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
115 l_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
116
117 l_gen_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
118 l_chk_duplicate_flag VARCHAR2(1) := 'N';
119
120 l_resource_class_id PA_RESOURCE_CLASSES_B.RESOURCE_CLASS_ID%TYPE;
121
122 l_txn_currency_code_tab SYSTEM.pa_varchar2_15_tbl_type:=SYSTEM.pa_varchar2_15_tbl_type();
123 l_txn_currency_override_tab SYSTEM.pa_varchar2_15_tbl_type:=SYSTEM.pa_varchar2_15_tbl_type();
124 l_total_raw_cost_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
125 l_total_burdened_cost_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
126 l_total_quantity_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
127 l_total_revenue_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
128 l_raw_cost_rate_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
129 l_b_cost_rate_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
130 l_rw_cost_rate_override_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
131 l_b_cost_rate_override_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
132 l_line_start_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
133 l_line_end_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
134 l_resource_assignment_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
135 l_spread_amts_flag_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
136 l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
137
138 l_bl_start_date DATE;
139 l_bl_end_date DATE;
140 l_bl_period_name PA_PERIODS_ALL.PERIOD_NAME%TYPE;
141 l_etc_start_date DATE;
142 l_reference_start_date DATE;
143
144 l_count1 NUMBER;
145 l_date DATE;
146 l_burden_rate NUMBER;
147 l_burden_override_rate NUMBER;
148 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
149 l_res_list_mem_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
150
151 --Local pl/sql table to call Map_Rlmi_Rbs api
152 l_TXN_SOURCE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
153 l_TXN_SOURCE_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
154 l_PERSON_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
155 l_JOB_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
156 l_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
157 l_VENDOR_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
158 l_EXPENDITURE_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
159 l_EVENT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
160 l_NON_LABOR_RESOURCE_tab PA_PLSQL_DATATYPES.Char20TabTyp;
161 l_EXPENDITURE_CATEGORY_tab PA_PLSQL_DATATYPES.Char30TabTyp;
162 l_REVENUE_CATEGORY_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
163 l_NLR_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
164 l_EVENT_CLASSIFICATION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
165 l_SYS_LINK_FUNCTION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
166 l_PROJECT_ROLE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
167 l_RESOURCE_CLASS_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
168 l_MFC_COST_TYPE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
169 l_RESOURCE_CLASS_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
170 l_FC_RES_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
171 l_INVENTORY_ITEM_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
172 l_ITEM_CATEGORY_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
173 l_PERSON_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
174 l_BOM_RESOURCE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
175 l_NAMED_ROLE_tab PA_PLSQL_DATATYPES.Char80TabTyp;
176 l_INCURRED_BY_RES_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
177 l_RATE_BASED_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
178 l_TXN_TASK_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
179 l_TXN_WBS_ELEMENT_VER_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
180 l_TXN_RBS_ELEMENT_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
181 l_TXN_PLAN_START_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
182 l_TXN_PLAN_END_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
183 --out param from PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
184 l_map_txn_source_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
185 l_map_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
186 l_map_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
187 l_map_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
188
189 l_tmp4 Number := 0;
190
191 BEGIN
192 X_MSG_COUNT := 0;
193 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
194
195 IF p_pa_debug_mode = 'Y' THEN
196 pa_debug.set_curr_function( p_function => 'GEN_COMMITMENT_AMOUNTS'
197 ,p_debug_mode => p_pa_debug_mode);
198 END IF;
199
200 l_stru_sharing_code := PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(P_PROJECT_ID=> P_PROJECT_ID);
201
202 DELETE FROM PA_RES_LIST_MAP_TMP1;
203
204 SELECT RESOURCE_CLASS_ID
205 INTO l_resource_class_id
206 FROM PA_RESOURCE_CLASSES_B
207 WHERE RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS';
208
209 SELECT ct.CMT_LINE_ID,
210 'OPEN_COMMITMENTS',
211 ct.ORGANIZATION_ID,
212 ct.VENDOR_ID,
213 ct.EXPENDITURE_TYPE,
214 ct.REVENUE_CATEGORY,
215 ct.TASK_ID
216 ,NVL(ct.CMT_NEED_BY_DATE, ct.EXPENDITURE_ITEM_DATE)
217 ,NVL(ct.CMT_NEED_BY_DATE, ct.EXPENDITURE_ITEM_DATE)
218 ,SYSTEM_LINKAGE_FUNCTION
219 ,INVENTORY_ITEM_ID
220 ,DECODE(EXPENDITURE_TYPE,null,
221 DECODE(EXPENDITURE_CATEGORY,null,NULL,
222 'EXPENDITURE_CATEGORY'),'EXPENDITURE_TYPE'),
223 NVL(ct.RESOURCE_CLASS,'FINANCIAL_ELEMENTS')
224 BULK COLLECT
225 INTO l_TXN_SOURCE_ID_tab,
226 l_TXN_SOURCE_TYPE_CODE_tab,
227 l_ORGANIZATION_ID_tab,
228 l_VENDOR_ID_tab,
229 l_EXPENDITURE_TYPE_tab,
230 l_REVENUE_CATEGORY_CODE_tab,
231 l_TXN_TASK_ID_tab,
232 l_TXN_PLAN_START_DATE_tab,
233 l_TXN_PLAN_END_DATE_tab,
234 l_SYS_LINK_FUNCTION_tab,
235 l_INVENTORY_ITEM_ID_tab,
236 l_FC_RES_TYPE_CODE_tab,
237 l_RESOURCE_CLASS_CODE_tab
238 FROM PA_COMMITMENT_TXNS ct, PA_RESOURCE_CLASSES_B rc
239 WHERE ct.PROJECT_ID = P_PROJECT_ID
240 AND NVL(CT.generation_error_flag,'N') = 'N'
241 AND ct.RESOURCE_CLASS = rc.RESOURCE_CLASS_CODE(+);
242
243 IF l_TXN_SOURCE_ID_tab.count = 0 THEN
244 IF P_PA_DEBUG_MODE = 'Y' THEN
245 PA_DEBUG.Reset_Curr_Function;
246 END IF;
247 RETURN;
248 END IF;
249 --dbms_output.put_line('l_TXN_SOURCE_ID_tab.count: '||l_TXN_SOURCE_ID_tab.count);
250 FOR bb in 1..l_TXN_SOURCE_ID_tab.count LOOP
251 l_PERSON_ID_tab(bb) := null;
252 l_JOB_ID_tab(bb) := null;
253 l_EVENT_TYPE_tab(bb) := null;
254 l_NON_LABOR_RESOURCE_tab(bb) := null;
255 l_EXPENDITURE_CATEGORY_tab(bb) := null;
256 l_NLR_ORGANIZATION_ID_tab(bb) := null;
257 l_EVENT_CLASSIFICATION_tab(bb) := null;
258 l_PROJECT_ROLE_ID_tab(bb) := null;
259 l_MFC_COST_TYPE_ID_tab(bb) := null;
260 l_RESOURCE_CLASS_FLAG_tab(bb) := null;
261 l_ITEM_CATEGORY_ID_tab(bb) := null;
262 l_PERSON_TYPE_CODE_tab(bb) := null;
263 l_BOM_RESOURCE_ID_tab(bb) := null;
264 l_NAMED_ROLE_tab(bb) := null;
265 l_INCURRED_BY_RES_FLAG_tab(bb) := null;
266 l_RATE_BASED_FLAG_tab(bb) := null;
267 l_TXN_WBS_ELEMENT_VER_ID_tab(bb):= null;
268 l_TXN_RBS_ELEMENT_ID_tab(bb) := null;
269 END LOOP;
270
271 IF P_PA_DEBUG_MODE = 'Y' THEN
272 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
273 P_MSG => 'Before calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS',
274 P_MODULE_NAME => l_module_name);
275 END IF;
276 pa_resource_mapping.g_called_process :='COMMITMENTS'; --Bug 9753875
277 PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS (
278 P_PROJECT_ID => p_project_id,
279 P_BUDGET_VERSION_ID => NULL,
280 P_RESOURCE_LIST_ID => P_FP_COLS_REC.X_RESOURCE_LIST_ID,
281 P_RBS_VERSION_ID => NULL,
282 P_CALLING_PROCESS => 'BUDGET_GENERATION',
283 P_CALLING_CONTEXT => 'PLSQL',
284 P_PROCESS_CODE => 'RES_MAP',
285 P_CALLING_MODE => 'PLSQL_TABLE',
286 P_INIT_MSG_LIST_FLAG => 'N',
287 P_COMMIT_FLAG => 'N',
288 P_TXN_SOURCE_ID_TAB => l_TXN_SOURCE_ID_tab,
289 P_TXN_SOURCE_TYPE_CODE_TAB => l_TXN_SOURCE_TYPE_CODE_tab,
290 P_PERSON_ID_TAB => l_PERSON_ID_tab,
291 P_JOB_ID_TAB => l_JOB_ID_tab,
292 P_ORGANIZATION_ID_TAB => l_ORGANIZATION_ID_tab,
293 P_VENDOR_ID_TAB => l_VENDOR_ID_tab,
294 P_EXPENDITURE_TYPE_TAB => l_EXPENDITURE_TYPE_tab,
295 P_EVENT_TYPE_TAB => l_EVENT_TYPE_tab,
296 P_NON_LABOR_RESOURCE_TAB => l_NON_LABOR_RESOURCE_tab,
297 P_EXPENDITURE_CATEGORY_TAB => l_EXPENDITURE_CATEGORY_tab,
298 P_REVENUE_CATEGORY_CODE_TAB =>l_REVENUE_CATEGORY_CODE_tab,
299 P_NLR_ORGANIZATION_ID_TAB =>l_NLR_ORGANIZATION_ID_tab,
300 P_EVENT_CLASSIFICATION_TAB => l_EVENT_CLASSIFICATION_tab,
301 P_SYS_LINK_FUNCTION_TAB => l_SYS_LINK_FUNCTION_tab,
302 P_PROJECT_ROLE_ID_TAB => l_PROJECT_ROLE_ID_tab,
303 P_RESOURCE_CLASS_CODE_TAB => l_RESOURCE_CLASS_CODE_tab,
304 P_MFC_COST_TYPE_ID_TAB => l_MFC_COST_TYPE_ID_tab,
305 P_RESOURCE_CLASS_FLAG_TAB => l_RESOURCE_CLASS_FLAG_tab,
306 P_FC_RES_TYPE_CODE_TAB => l_FC_RES_TYPE_CODE_tab,
307 P_INVENTORY_ITEM_ID_TAB => l_INVENTORY_ITEM_ID_tab,
308 P_ITEM_CATEGORY_ID_TAB => l_ITEM_CATEGORY_ID_tab,
309 P_PERSON_TYPE_CODE_TAB => l_PERSON_TYPE_CODE_tab,
310 P_BOM_RESOURCE_ID_TAB =>l_BOM_RESOURCE_ID_tab,
311 P_NAMED_ROLE_TAB =>l_NAMED_ROLE_tab,
312 P_INCURRED_BY_RES_FLAG_TAB =>l_INCURRED_BY_RES_FLAG_tab,
313 P_RATE_BASED_FLAG_TAB =>l_RATE_BASED_FLAG_tab,
314 P_TXN_TASK_ID_TAB =>l_TXN_TASK_ID_tab,
315 P_TXN_WBS_ELEMENT_VER_ID_TAB => l_TXN_WBS_ELEMENT_VER_ID_tab,
316 P_TXN_RBS_ELEMENT_ID_TAB => l_TXN_RBS_ELEMENT_ID_tab,
317 P_TXN_PLAN_START_DATE_TAB => l_TXN_PLAN_START_DATE_tab,
318 P_TXN_PLAN_END_DATE_TAB => l_TXN_PLAN_END_DATE_tab,
319 X_TXN_SOURCE_ID_TAB =>l_map_txn_source_id_tab,
320 X_RES_LIST_MEMBER_ID_TAB =>l_map_rlm_id_tab,
321 X_RBS_ELEMENT_ID_TAB =>l_map_rbs_element_id_tab,
322 X_TXN_ACCUM_HEADER_ID_TAB =>l_map_txn_accum_header_id_tab,
323 X_RETURN_STATUS => x_return_status,
324 X_MSG_COUNT => x_msg_count,
325 X_MSG_DATA => x_msg_data );
326 IF P_PA_DEBUG_MODE = 'Y' THEN
327 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
328 P_MSG => 'After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||
329 x_return_status,
330 P_MODULE_NAME => l_module_name);
331 END IF;
332
333 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
334 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
335 END IF;
336
337 pa_resource_mapping.g_called_process :='PLAN'; --Bug 9753875
338
339 --dbms_output.put_line('l_map_rlm_id_tab.count: '||l_map_rlm_id_tab.count);
340
341 SELECT /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
342 count(*) INTO l_count1
343 FROM PA_RES_LIST_MAP_TMP4
344 WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
345 IF l_count1 > 0 THEN
346 PA_UTILS.ADD_MESSAGE
347 (p_app_short_name => 'PA',
348 p_msg_name => 'PA_INVALID_MAPPING_ERR');
349 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
350 END IF;
351
352 IF p_pa_debug_mode = 'Y' THEN
353 pa_fp_gen_amount_utils.fp_debug
354 (p_msg => 'Before calling pa_fp_gen_budget_amt_pub.create_res_asg',
355 p_module_name => l_module_name,
356 p_log_level => 5);
357 END IF;
358 --dbms_output.put_line('calling PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG');
359 PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG
360 (P_PROJECT_ID => P_PROJECT_ID,
361 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
362 P_STRU_SHARING_CODE => l_stru_sharing_code,
363 P_GEN_SRC_CODE => 'OPEN_COMMITMENTS',
364 P_FP_COLS_REC => P_FP_COLS_REC,
365 X_RETURN_STATUS => X_RETURN_STATUS,
366 X_MSG_COUNT => X_MSG_COUNT,
367 X_MSG_DATA => X_MSG_DATA);
368 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
369 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
370 END IF;
371
372 --dbms_output.put_line('Return status after calling PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG: '||X_RETURN_STATUS);
373 IF p_pa_debug_mode = 'Y' THEN
374 pa_fp_gen_amount_utils.fp_debug
375 (p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.create_res_asg'
376 ||x_return_status,
377 p_module_name => l_module_name,
378 p_log_level => 5);
379 END IF;
380
381 IF p_pa_debug_mode = 'Y' THEN
382 pa_fp_gen_amount_utils.fp_debug
383 (p_msg => 'Before calling pa_fp_gen_budget_amt_pub.update_res_asg',
384 p_module_name => l_module_name,
385 p_log_level => 5);
386 END IF;
387
388 --dbms_output.put_line('calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG');
389 PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG
390 (P_PROJECT_ID => P_PROJECT_ID,
391 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
392 P_STRU_SHARING_CODE => l_stru_sharing_code,
393 P_GEN_SRC_CODE => 'OPEN_COMMITMENTS',
394 P_FP_COLS_REC => P_FP_COLS_REC,
395 X_RETURN_STATUS => X_RETURN_STATUS,
396 X_MSG_COUNT => X_MSG_COUNT,
397 X_MSG_DATA => X_MSG_DATA);
398 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
399 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
400 END IF;
401 --dbms_output.put_line('Return status after calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG: '||X_RETURN_STATUS);
402 IF p_pa_debug_mode = 'Y' THEN
403 pa_fp_gen_amount_utils.fp_debug
404 (p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.update_res_asg'
405 ||x_return_status,
406 p_module_name => l_module_name,
407 p_log_level => 5);
408 END IF;
409
410 l_appl_id := PA_PERIOD_PROCESS_PKG.Application_id;
411 OPEN SUM_COMM_CRSR(P_FP_COLS_REC.X_TIME_PHASED_CODE,
412 P_FP_COLS_REC.X_PLAN_IN_MULTI_CURR_FLAG,
413 l_appl_id,
414 P_FP_COLS_REC.X_SET_OF_BOOKS_ID,
415 P_FP_COLS_REC.X_ORG_ID);
416 FETCH SUM_COMM_CRSR
417 BULK COLLECT
418 INTO l_res_asg_id
419 ,l_task_id_tab
420 ,l_res_list_mem_id_tab
421 ,l_currency_code
422 ,l_ra_start_date
423 ,l_ra_end_date
424 ,l_commstart_date
425 ,l_commend_date
426 ,l_raw_cost_sum
427 ,l_burdened_cost_sum
428 ,l_pc_raw_cost_sum
429 ,l_pc_burdened_cost_sum
430 ,l_pfc_raw_cost_sum
431 ,l_pfc_burdened_cost_sum
432 ,l_quantity_sum_tab;
433 --,l_unit_of_measure_tab;
434 CLOSE SUM_COMM_CRSR;
435 --dbms_output.put_line('l_res_asg_id.count: '||l_res_asg_id.count);
436
437 --SELECT count(*) INTO l_tmp4
438 --FROM PA_RES_LIST_MAP_TMP4;
439
440 --dbms_output.put_line('res_map_Tmp4 table count: '|| l_tmp4);
441
442 INSERT INTO PA_FP_CALC_AMT_TMP1(
443 RESOURCE_ASSIGNMENT_ID,
444 BUDGET_VERSION_ID,
445 PROJECT_ID,
446 TASK_ID,
447 RESOURCE_LIST_MEMBER_ID,
448 UNIT_OF_MEASURE,
449 PLANNING_START_DATE,
450 PLANNING_END_DATE,
451 FC_RES_TYPE_CODE,
452 RESOURCE_CLASS_CODE,
453 ORGANIZATION_ID,
454 JOB_ID,
455 PERSON_ID,
456 EXPENDITURE_TYPE,
457 EXPENDITURE_CATEGORY,
458 EVENT_TYPE,
459 PROJECT_ROLE_ID,
460 PERSON_TYPE_CODE,
461 NON_LABOR_RESOURCE,
462 BOM_RESOURCE_ID,
463 INVENTORY_ITEM_ID,
464 ITEM_CATEGORY_ID,
465 TRANSACTION_SOURCE_CODE,
466 MFC_COST_TYPE_ID,
467 INCURRED_BY_RES_FLAG,
468 RATE_BASED_FLAG,
469 NAMED_ROLE,
470 ETC_METHOD_CODE,
471 TARGET_RLM_ID,
472 MAPPED_FIN_TASK_ID)
473 SELECT /*+ leading(tmp4) */ -- SQL Repository Bug 4884824; SQL ID 14901250.
474 TMP4.TXN_RESOURCE_ASSIGNMENT_ID,
475 TMP4.TXN_BUDGET_VERSION_ID,
476 TMP4.TXN_PROJECT_ID,
477 TMP4.TXN_TASK_ID,
478 TMP4.RESOURCE_LIST_MEMBER_ID,
479 CT.UNIT_OF_MEASURE,
480 TMP4.TXN_PLANNING_START_DATE,
481 TMP4.TXN_PLANNING_END_DATE,
482 TMP4.FC_RES_TYPE_CODE,
483 TMP4.RESOURCE_CLASS_CODE,
484 TMP4.ORGANIZATION_ID,
485 TMP4.JOB_ID,
486 TMP4.PERSON_ID,
487 TMP4.EXPENDITURE_TYPE,
488 TMP4.EXPENDITURE_CATEGORY,
489 TMP4.EVENT_TYPE,
490 TMP4.PROJECT_ROLE_ID,
491 TMP4.PERSON_TYPE_CODE,
492 TMP4.NON_LABOR_RESOURCE,
493 TMP4.BOM_RESOURCE_ID,
494 TMP4.INVENTORY_ITEM_ID,
495 TMP4.ITEM_CATEGORY_ID,
496 'OPEN_COMMITMENTS',
497 TMP4.MFC_COST_TYPE_ID,
498 TMP4.INCURRED_BY_RES_FLAG,
499 TMP4.TXN_RATE_BASED_FLAG,
500 TMP4.NAMED_ROLE,
501 TMP4.TXN_ETC_METHOD_CODE,
502 TMP4.RESOURCE_LIST_MEMBER_ID,
503 TMP4.TXN_TASK_ID
504 FROM PA_RES_LIST_MAP_TMP4 TMP4, PA_COMMITMENT_TXNS CT
505 WHERE CT.CMT_LINE_ID = TMP4.TXN_SOURCE_ID;
506
507 --dbms_output.put_line('calc_tmp1 table count: '||sql%rowcount);
508
509 FORALL i IN 1..l_res_asg_id.count
510 INSERT INTO PA_FP_CALC_AMT_TMP2(
511 TARGET_RES_ASG_ID
512 , TXN_CURRENCY_CODE
513 , TOTAL_PLAN_QUANTITY
514 , TOTAL_TXN_RAW_COST
515 , TOTAL_TXN_BURDENED_COST
516 , TOTAL_PC_RAW_COST
517 , TOTAL_PC_BURDENED_COST
518 , TOTAL_PFC_RAW_COST
519 , TOTAL_PFC_BURDENED_COST
520 --, TARGET_RES_ASG_ID
521 ,TRANSACTION_SOURCE_CODE
522 )
523 VALUES(l_res_asg_id(i),
524 l_currency_code(i),
525 l_quantity_sum_tab(i),
526 l_raw_cost_sum(i),
527 l_burdened_cost_sum(i),
528 l_pc_raw_cost_sum(i),
529 l_pc_burdened_cost_sum(i),
530 l_pfc_raw_cost_sum(i),
531 l_pfc_burdened_cost_sum(i),
532 'OPEN_COMMITMENTS'
533 );
534 --dbms_output.put_line('calc_tmp2 table count: '||sql%rowcount);
535
536 IF P_PA_DEBUG_MODE = 'Y' THEN
537 PA_DEBUG.Reset_Curr_Function;
538 END IF;
539
540 EXCEPTION
541 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
542 -- Bug Fix: 4569365. Removed MRC code.
543 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
544 l_msg_count := FND_MSG_PUB.count_msg;
545 IF l_msg_count = 1 THEN
546 PA_INTERFACE_UTILS_PUB.get_messages
547 (p_encoded => FND_API.G_TRUE
548 ,p_msg_index => 1
549 ,p_msg_count => l_msg_count
550 ,p_msg_data => l_msg_data
551 ,p_data => l_data
552 ,p_msg_index_out => l_msg_index_out);
553 x_msg_data := l_data;
554 x_msg_count := l_msg_count;
555 ELSE
556 x_msg_count := l_msg_count;
557 END IF;
558 ROLLBACK;
559
560 x_return_status := FND_API.G_RET_STS_ERROR;
561 RAISE;
562
563 WHEN OTHERS THEN
564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
565 x_msg_data := SUBSTR(SQLERRM,1,240);
566 FND_MSG_PUB.add_exc_msg
567 ( p_pkg_name => 'PA_FP_GEN_COMMITMENT_AMOUNTS'
568 ,p_procedure_name => 'GEN_COMMITMENT_AMOUNTS');
569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
570
571 END GET_COMMITMENT_AMTS;
572
573 END PA_FP_COMMITMENT_AMOUNTS;