[Home] [Help]
PACKAGE BODY: APPS.PA_FP_COMMITMENT_AMOUNTS
Source
1 PACKAGE body PA_FP_COMMITMENT_AMOUNTS as
2 /* $Header: PAFPCMTB.pls 120.3 2006/01/13 18:29:51 dkuo noship $ */
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_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS (
277 P_PROJECT_ID => p_project_id,
278 P_BUDGET_VERSION_ID => NULL,
279 P_RESOURCE_LIST_ID => P_FP_COLS_REC.X_RESOURCE_LIST_ID,
280 P_RBS_VERSION_ID => NULL,
281 P_CALLING_PROCESS => 'BUDGET_GENERATION',
282 P_CALLING_CONTEXT => 'PLSQL',
283 P_PROCESS_CODE => 'RES_MAP',
284 P_CALLING_MODE => 'PLSQL_TABLE',
285 P_INIT_MSG_LIST_FLAG => 'N',
286 P_COMMIT_FLAG => 'N',
287 P_TXN_SOURCE_ID_TAB => l_TXN_SOURCE_ID_tab,
288 P_TXN_SOURCE_TYPE_CODE_TAB => l_TXN_SOURCE_TYPE_CODE_tab,
289 P_PERSON_ID_TAB => l_PERSON_ID_tab,
290 P_JOB_ID_TAB => l_JOB_ID_tab,
291 P_ORGANIZATION_ID_TAB => l_ORGANIZATION_ID_tab,
292 P_VENDOR_ID_TAB => l_VENDOR_ID_tab,
293 P_EXPENDITURE_TYPE_TAB => l_EXPENDITURE_TYPE_tab,
294 P_EVENT_TYPE_TAB => l_EVENT_TYPE_tab,
295 P_NON_LABOR_RESOURCE_TAB => l_NON_LABOR_RESOURCE_tab,
296 P_EXPENDITURE_CATEGORY_TAB => l_EXPENDITURE_CATEGORY_tab,
297 P_REVENUE_CATEGORY_CODE_TAB =>l_REVENUE_CATEGORY_CODE_tab,
298 P_NLR_ORGANIZATION_ID_TAB =>l_NLR_ORGANIZATION_ID_tab,
299 P_EVENT_CLASSIFICATION_TAB => l_EVENT_CLASSIFICATION_tab,
300 P_SYS_LINK_FUNCTION_TAB => l_SYS_LINK_FUNCTION_tab,
301 P_PROJECT_ROLE_ID_TAB => l_PROJECT_ROLE_ID_tab,
302 P_RESOURCE_CLASS_CODE_TAB => l_RESOURCE_CLASS_CODE_tab,
306 P_INVENTORY_ITEM_ID_TAB => l_INVENTORY_ITEM_ID_tab,
303 P_MFC_COST_TYPE_ID_TAB => l_MFC_COST_TYPE_ID_tab,
304 P_RESOURCE_CLASS_FLAG_TAB => l_RESOURCE_CLASS_FLAG_tab,
305 P_FC_RES_TYPE_CODE_TAB => l_FC_RES_TYPE_CODE_tab,
307 P_ITEM_CATEGORY_ID_TAB => l_ITEM_CATEGORY_ID_tab,
308 P_PERSON_TYPE_CODE_TAB => l_PERSON_TYPE_CODE_tab,
309 P_BOM_RESOURCE_ID_TAB =>l_BOM_RESOURCE_ID_tab,
310 P_NAMED_ROLE_TAB =>l_NAMED_ROLE_tab,
311 P_INCURRED_BY_RES_FLAG_TAB =>l_INCURRED_BY_RES_FLAG_tab,
312 P_RATE_BASED_FLAG_TAB =>l_RATE_BASED_FLAG_tab,
313 P_TXN_TASK_ID_TAB =>l_TXN_TASK_ID_tab,
314 P_TXN_WBS_ELEMENT_VER_ID_TAB => l_TXN_WBS_ELEMENT_VER_ID_tab,
315 P_TXN_RBS_ELEMENT_ID_TAB => l_TXN_RBS_ELEMENT_ID_tab,
316 P_TXN_PLAN_START_DATE_TAB => l_TXN_PLAN_START_DATE_tab,
317 P_TXN_PLAN_END_DATE_TAB => l_TXN_PLAN_END_DATE_tab,
318 X_TXN_SOURCE_ID_TAB =>l_map_txn_source_id_tab,
319 X_RES_LIST_MEMBER_ID_TAB =>l_map_rlm_id_tab,
320 X_RBS_ELEMENT_ID_TAB =>l_map_rbs_element_id_tab,
321 X_TXN_ACCUM_HEADER_ID_TAB =>l_map_txn_accum_header_id_tab,
322 X_RETURN_STATUS => x_return_status,
323 X_MSG_COUNT => x_msg_count,
324 X_MSG_DATA => x_msg_data );
325 IF P_PA_DEBUG_MODE = 'Y' THEN
326 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
327 P_MSG => 'After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||
328 x_return_status,
329 P_MODULE_NAME => l_module_name);
330 END IF;
331
332 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
333 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
334 END IF;
335 --dbms_output.put_line('l_map_rlm_id_tab.count: '||l_map_rlm_id_tab.count);
336
337 SELECT /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
338 count(*) INTO l_count1
339 FROM PA_RES_LIST_MAP_TMP4
340 WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
341 IF l_count1 > 0 THEN
342 PA_UTILS.ADD_MESSAGE
343 (p_app_short_name => 'PA',
344 p_msg_name => 'PA_INVALID_MAPPING_ERR');
345 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
346 END IF;
347
348 IF p_pa_debug_mode = 'Y' THEN
349 pa_fp_gen_amount_utils.fp_debug
350 (p_msg => 'Before calling pa_fp_gen_budget_amt_pub.create_res_asg',
351 p_module_name => l_module_name,
352 p_log_level => 5);
353 END IF;
354 --dbms_output.put_line('calling PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG');
355 PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG
356 (P_PROJECT_ID => P_PROJECT_ID,
357 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
358 P_STRU_SHARING_CODE => l_stru_sharing_code,
359 P_GEN_SRC_CODE => 'OPEN_COMMITMENTS',
360 P_FP_COLS_REC => P_FP_COLS_REC,
361 X_RETURN_STATUS => X_RETURN_STATUS,
362 X_MSG_COUNT => X_MSG_COUNT,
363 X_MSG_DATA => X_MSG_DATA);
364 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
365 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
366 END IF;
367
368 --dbms_output.put_line('Return status after calling PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG: '||X_RETURN_STATUS);
369 IF p_pa_debug_mode = 'Y' THEN
370 pa_fp_gen_amount_utils.fp_debug
371 (p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.create_res_asg'
372 ||x_return_status,
373 p_module_name => l_module_name,
374 p_log_level => 5);
375 END IF;
376
377 IF p_pa_debug_mode = 'Y' THEN
378 pa_fp_gen_amount_utils.fp_debug
379 (p_msg => 'Before calling pa_fp_gen_budget_amt_pub.update_res_asg',
380 p_module_name => l_module_name,
381 p_log_level => 5);
382 END IF;
383
384 --dbms_output.put_line('calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG');
385 PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG
386 (P_PROJECT_ID => P_PROJECT_ID,
387 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
388 P_STRU_SHARING_CODE => l_stru_sharing_code,
389 P_GEN_SRC_CODE => 'OPEN_COMMITMENTS',
390 P_FP_COLS_REC => P_FP_COLS_REC,
391 X_RETURN_STATUS => X_RETURN_STATUS,
392 X_MSG_COUNT => X_MSG_COUNT,
393 X_MSG_DATA => X_MSG_DATA);
394 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
395 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
396 END IF;
397 --dbms_output.put_line('Return status after calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG: '||X_RETURN_STATUS);
398 IF p_pa_debug_mode = 'Y' THEN
399 pa_fp_gen_amount_utils.fp_debug
400 (p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.update_res_asg'
401 ||x_return_status,
402 p_module_name => l_module_name,
403 p_log_level => 5);
404 END IF;
405
406 l_appl_id := PA_PERIOD_PROCESS_PKG.Application_id;
407 OPEN SUM_COMM_CRSR(P_FP_COLS_REC.X_TIME_PHASED_CODE,
408 P_FP_COLS_REC.X_PLAN_IN_MULTI_CURR_FLAG,
409 l_appl_id,
410 P_FP_COLS_REC.X_SET_OF_BOOKS_ID,
411 P_FP_COLS_REC.X_ORG_ID);
412 FETCH SUM_COMM_CRSR
413 BULK COLLECT
414 INTO l_res_asg_id
415 ,l_task_id_tab
416 ,l_res_list_mem_id_tab
417 ,l_currency_code
418 ,l_ra_start_date
422 ,l_raw_cost_sum
419 ,l_ra_end_date
420 ,l_commstart_date
421 ,l_commend_date
423 ,l_burdened_cost_sum
424 ,l_pc_raw_cost_sum
425 ,l_pc_burdened_cost_sum
426 ,l_pfc_raw_cost_sum
427 ,l_pfc_burdened_cost_sum
428 ,l_quantity_sum_tab;
429 --,l_unit_of_measure_tab;
430 CLOSE SUM_COMM_CRSR;
431 --dbms_output.put_line('l_res_asg_id.count: '||l_res_asg_id.count);
432
433 --SELECT count(*) INTO l_tmp4
434 --FROM PA_RES_LIST_MAP_TMP4;
435
436 --dbms_output.put_line('res_map_Tmp4 table count: '|| l_tmp4);
437
438 INSERT INTO PA_FP_CALC_AMT_TMP1(
439 RESOURCE_ASSIGNMENT_ID,
440 BUDGET_VERSION_ID,
441 PROJECT_ID,
442 TASK_ID,
443 RESOURCE_LIST_MEMBER_ID,
444 UNIT_OF_MEASURE,
445 PLANNING_START_DATE,
446 PLANNING_END_DATE,
447 FC_RES_TYPE_CODE,
448 RESOURCE_CLASS_CODE,
449 ORGANIZATION_ID,
450 JOB_ID,
451 PERSON_ID,
452 EXPENDITURE_TYPE,
453 EXPENDITURE_CATEGORY,
454 EVENT_TYPE,
455 PROJECT_ROLE_ID,
456 PERSON_TYPE_CODE,
457 NON_LABOR_RESOURCE,
458 BOM_RESOURCE_ID,
459 INVENTORY_ITEM_ID,
460 ITEM_CATEGORY_ID,
461 TRANSACTION_SOURCE_CODE,
462 MFC_COST_TYPE_ID,
463 INCURRED_BY_RES_FLAG,
464 RATE_BASED_FLAG,
465 NAMED_ROLE,
466 ETC_METHOD_CODE,
467 TARGET_RLM_ID,
468 MAPPED_FIN_TASK_ID)
469 SELECT /*+ leading(tmp4) */ -- SQL Repository Bug 4884824; SQL ID 14901250.
470 TMP4.TXN_RESOURCE_ASSIGNMENT_ID,
471 TMP4.TXN_BUDGET_VERSION_ID,
472 TMP4.TXN_PROJECT_ID,
473 TMP4.TXN_TASK_ID,
474 TMP4.RESOURCE_LIST_MEMBER_ID,
475 CT.UNIT_OF_MEASURE,
476 TMP4.TXN_PLANNING_START_DATE,
477 TMP4.TXN_PLANNING_END_DATE,
478 TMP4.FC_RES_TYPE_CODE,
479 TMP4.RESOURCE_CLASS_CODE,
480 TMP4.ORGANIZATION_ID,
481 TMP4.JOB_ID,
482 TMP4.PERSON_ID,
483 TMP4.EXPENDITURE_TYPE,
484 TMP4.EXPENDITURE_CATEGORY,
485 TMP4.EVENT_TYPE,
486 TMP4.PROJECT_ROLE_ID,
487 TMP4.PERSON_TYPE_CODE,
488 TMP4.NON_LABOR_RESOURCE,
489 TMP4.BOM_RESOURCE_ID,
490 TMP4.INVENTORY_ITEM_ID,
491 TMP4.ITEM_CATEGORY_ID,
492 'OPEN_COMMITMENTS',
493 TMP4.MFC_COST_TYPE_ID,
494 TMP4.INCURRED_BY_RES_FLAG,
495 TMP4.TXN_RATE_BASED_FLAG,
496 TMP4.NAMED_ROLE,
497 TMP4.TXN_ETC_METHOD_CODE,
498 TMP4.RESOURCE_LIST_MEMBER_ID,
499 TMP4.TXN_TASK_ID
500 FROM PA_RES_LIST_MAP_TMP4 TMP4, PA_COMMITMENT_TXNS CT
501 WHERE CT.CMT_LINE_ID = TMP4.TXN_SOURCE_ID;
502
503 --dbms_output.put_line('calc_tmp1 table count: '||sql%rowcount);
504
505 FORALL i IN 1..l_res_asg_id.count
506 INSERT INTO PA_FP_CALC_AMT_TMP2(
507 TARGET_RES_ASG_ID
508 , TXN_CURRENCY_CODE
509 , TOTAL_PLAN_QUANTITY
510 , TOTAL_TXN_RAW_COST
511 , TOTAL_TXN_BURDENED_COST
512 , TOTAL_PC_RAW_COST
513 , TOTAL_PC_BURDENED_COST
514 , TOTAL_PFC_RAW_COST
515 , TOTAL_PFC_BURDENED_COST
516 --, TARGET_RES_ASG_ID
517 ,TRANSACTION_SOURCE_CODE
518 )
519 VALUES(l_res_asg_id(i),
520 l_currency_code(i),
521 l_quantity_sum_tab(i),
522 l_raw_cost_sum(i),
523 l_burdened_cost_sum(i),
524 l_pc_raw_cost_sum(i),
525 l_pc_burdened_cost_sum(i),
526 l_pfc_raw_cost_sum(i),
527 l_pfc_burdened_cost_sum(i),
528 'OPEN_COMMITMENTS'
529 );
530 --dbms_output.put_line('calc_tmp2 table count: '||sql%rowcount);
531
532 IF P_PA_DEBUG_MODE = 'Y' THEN
533 PA_DEBUG.Reset_Curr_Function;
534 END IF;
535
536 EXCEPTION
537 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
538 -- Bug Fix: 4569365. Removed MRC code.
539 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
540 l_msg_count := FND_MSG_PUB.count_msg;
541 IF l_msg_count = 1 THEN
542 PA_INTERFACE_UTILS_PUB.get_messages
543 (p_encoded => FND_API.G_TRUE
547 ,p_data => l_data
544 ,p_msg_index => 1
545 ,p_msg_count => l_msg_count
546 ,p_msg_data => l_msg_data
548 ,p_msg_index_out => l_msg_index_out);
549 x_msg_data := l_data;
550 x_msg_count := l_msg_count;
551 ELSE
552 x_msg_count := l_msg_count;
553 END IF;
554 ROLLBACK;
555
556 x_return_status := FND_API.G_RET_STS_ERROR;
557 RAISE;
558
559 WHEN OTHERS THEN
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 x_msg_data := SUBSTR(SQLERRM,1,240);
562 FND_MSG_PUB.add_exc_msg
563 ( p_pkg_name => 'PA_FP_GEN_COMMITMENT_AMOUNTS'
564 ,p_procedure_name => 'GEN_COMMITMENT_AMOUNTS');
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566
567 END GET_COMMITMENT_AMTS;
568
569 END PA_FP_COMMITMENT_AMOUNTS;