[Home] [Help]
PACKAGE BODY: APPS.PA_RLMI_RBS_MAP_PUB
Source
1 PACKAGE BODY PA_RLMI_RBS_MAP_PUB AS
2 /* $Header: PAFPUT3B.pls 120.6.12020000.6 2013/05/30 22:49:17 sachandr ship $ */
3
4 g_debug_flag Varchar2(1) := NULL;
5 g_calling_context Varchar2(100) := Null;
6 g_commit_flag Varchar2(1) := NULL;
7 g_project_id Number := Null;
8 g_budget_version_id Number := Null;
9 g_resource_list_id Number := Null;
10 --This variable indicates whether to call the resource list mapping API or not.
11 g_call_res_list_mapping_api VARCHAR2(1);
12 g_rbs_version_id Number := Null;
13 g_res_numRecInserted Number := Null;
14 g_rbs_numRecInserted Number := Null;
15 G_DEBUG_CONTEXT Varchar2(100) ;
16 /* declaration of plsql tables for populating resmap tmp */
17 g_txn_Id_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab ;
18 g_TXN_SOURCE_ID_sqlTab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab ;
19 g_TXN_SOURCE_TYPE_CODE_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab ;
20 g_PERSON_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
21 g_JOB_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
22 g_ORGANIZATION_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
23 g_VENDOR_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
24 g_EXPENDITURE_TYPE_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
25 g_EVENT_TYPE_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
26 g_NON_LABOR_RESOURCE_sqltab PA_PLSQL_DATATYPES.Char20TabTyp := PA_PLSQL_DATATYPES.EmptyChar20Tab;
27 g_EXPENDITURE_CATEGORY_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
28 g_EXP_CATEGORY_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
29 g_REVENUE_CATEGORY_CODE_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
30 g_NLR_ORGANIZATION_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
31 g_EVENT_CLASSIFICATION_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
32 g_SYS_LINK_FUNCTION_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
33 g_PROJECT_ROLE_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
34 g_RESOURCE_CLASS_CODE_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
35 g_MFC_COST_TYPE_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
36 g_RESOURCE_CLASS_FLAG_sqltab PA_PLSQL_DATATYPES.Char1TabTyp := PA_PLSQL_DATATYPES.EmptyChar1Tab;
37 g_FC_RES_TYPE_CODE_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
38 g_INVENTORY_ITEM_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
39 g_ITEM_CATEGORY_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
40 g_PERSON_TYPE_CODE_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
41 g_BOM_RESOURCE_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
42 g_NAMED_ROLE_sqltab PA_PLSQL_DATATYPES.Char80TabTyp:= PA_PLSQL_DATATYPES.EmptyChar80Tab;
43 g_INCURRED_BY_RES_FLAG_sqltab PA_PLSQL_DATATYPES.Char1TabTyp := PA_PLSQL_DATATYPES.EmptyChar1Tab;
44 g_RATE_BASED_FLAG_sqltab PA_PLSQL_DATATYPES.Char1TabTyp := PA_PLSQL_DATATYPES.EmptyChar1Tab;
45 g_TXN_TASK_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
46 g_TXN_WBS_ELE_VER_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
47 g_TXN_RBS_ELEMENT_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
48 g_CBS_ELEMENT_ID_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab; --bug#16827157
49 g_TXN_PLAN_START_DATE_sqltab PA_PLSQL_DATATYPES.DateTabTyp := PA_PLSQL_DATATYPES.EmptyDateTab ;
50 g_TXN_PLAN_END_DATE_sqltab PA_PLSQL_DATATYPES.DateTabTyp := PA_PLSQL_DATATYPES.EmptyDateTab;
51 /* declatioin of System Tables */
52 g_txn_Id_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
53 g_TXN_SOURCE_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
54 g_TXN_SOURCE_TYPE_CODE_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
55 g_PERSON_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
56 g_JOB_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
57 g_ORGANIZATION_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
58 g_VENDOR_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
59 g_EXPENDITURE_TYPE_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
60 g_EVENT_TYPE_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
61 g_NON_LABOR_RESOURCE_systab system.PA_VARCHAR2_20_TBL_TYPE := system.PA_VARCHAR2_20_TBL_TYPE();
62 g_EXPENDITURE_CATEGORY_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
63 g_EXP_CATEGORY_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
64 g_REVENUE_CATEGORY_CODE_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
65 g_NLR_ORGANIZATION_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
66 g_EVENT_CLASSIFICATION_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
67 g_SYS_LINK_FUNCTION_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
68 g_PROJECT_ROLE_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
69 g_RESOURCE_CLASS_CODE_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
70 g_MFC_COST_TYPE_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
71 g_RESOURCE_CLASS_FLAG_systab system.PA_VARCHAR2_1_TBL_TYPE := system.PA_VARCHAR2_1_TBL_TYPE();
72 g_FC_RES_TYPE_CODE_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
73 g_INVENTORY_ITEM_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
74 g_ITEM_CATEGORY_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
75 g_PERSON_TYPE_CODE_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
76 g_BOM_RESOURCE_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
77 g_NAMED_ROLE_systab system.PA_VARCHAR2_80_TBL_TYPE := system.PA_VARCHAR2_80_TBL_TYPE();
78 g_INCURRED_BY_RES_FLAG_systab system.PA_VARCHAR2_1_TBL_TYPE := system.PA_VARCHAR2_1_TBL_TYPE();
79 g_RATE_BASED_FLAG_systab system.PA_VARCHAR2_1_TBL_TYPE := system.PA_VARCHAR2_1_TBL_TYPE();
80 g_TXN_TASK_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
81 g_TXN_WBS_ELE_VER_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
82 g_TXN_RBS_ELEMENT_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
83 g_CBS_ELEMENT_ID_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE(); --bug#16827157
84 g_TXN_PLAN_START_DATE_systab system.PA_DATE_TBL_TYPE := system.PA_DATE_TBL_TYPE();
85 g_TXN_PLAN_END_DATE_systab system.PA_DATE_TBL_TYPE := system.PA_DATE_TBL_TYPE();
86 /* OUT Plsql Variables */
87 gx_txn_source_id_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
88 g_res_map_reject_code_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
89 g_rbs_map_reject_code_sqltab PA_PLSQL_DATATYPES.Char30TabTyp := PA_PLSQL_DATATYPES.EmptyChar30Tab;
90 g_res_list_member_id_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
91 g_rbs_element_id_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
92 g_txn_accum_header_id_sqltab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
93 /* OUT System Variables */
94 gx_txn_source_id_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
95 g_res_map_reject_code_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
96 g_rbs_map_reject_code_systab system.PA_VARCHAR2_30_TBL_TYPE := system.PA_VARCHAR2_30_TBL_TYPE();
97 g_res_list_member_id_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
98 g_rbs_element_id_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
99 g_txn_accum_header_id_systab system.PA_NUM_TBL_TYPE := system.PA_NUM_TBL_TYPE();
100
101 /**
102 procedure calc_log(p_msg varchar2) IS
103
104 pragma autonomous_transaction ;
105 BEGIN
106 --dbms_output.put_line(p_msg);
107 --IF P_PA_DEBUG_MODE = 'Y' Then
108 NULL;
109 INSERT INTO PA_FP_CALCULATE_LOG
110 (SESSIONID
111 ,SEQ_NUMBER
112 ,LOG_MESSAGE)
113 VALUES
114 (userenv('sessionid')
115 ,HR.PAY_US_GARN_FEE_RULES_S.nextval
116 ,substr(P_MSG,1,240)
117 );
118 --END IF;
119 COMMIT;
120
121 end calc_log;
122 **/
123 PROCEDURE print_msg(p_debug_flag varchar2
124 ,p_msg varchar2
125 ,p_proc_name varchar2 default NULL ) IS
126
127 l_module varchar2(100) := 'PA_RLMI_RBS_MAP_PUB';
128 BEGIN
129 --calc_log(p_msg);
130 /* Bug fix:4403327 Enclose the Push_RBS_Version calls inside the debug flag */
131 If p_debug_flag = 'Y' Then
132 PA_DEBUG.WRITE(x_module => 'PA_RLMI_RBS_MAP_PUB.map_rlmi_rbs'
133 ,x_msg => p_msg
134 ,x_log_level => 3 );
135
136 If p_proc_name = 'Push_RBS_Version' Then
137 PA_DEBUG.write_file('LOG',p_msg);
138 PA_DEBUG.log_message(p_msg);
139 End If;
140 End If;
141 END print_msg;
142
143 /* This API initializes the required variables into global variables */
144 PROCEDURE Init_ReqdVariables(
145 p_process_code IN varchar2
146 ,p_project_id IN Number
147 ,p_resource_list_id IN Number
148 ,p_rbs_version_id IN Number
149 ,p_budget_version_id IN NUmber ) IS
150
151 l_stage varchar2(1000);
152 BEGIN
153 l_stage := 'Begin Init_ReqdVariables';
154 print_msg(g_debug_flag,l_stage);
155 IF ((p_process_code = 'RES_MAP' and ( p_resource_list_id is NULL OR p_project_id is NULL) )
156 OR
157 (p_process_code = 'RBS_MAP' and p_rbs_version_id is NULL )
158 OR
159 (p_process_code in ('RES_RBS_MAP')
160 and (p_resource_list_id is NULL OR p_rbs_version_id is NULL OR p_project_id is NULL ))
161 ) Then
162 If p_budget_version_id is NOT NULL Then
163 l_stage := 'Fetch the res and Rbs Details for the given budget version';
164 print_msg(g_debug_flag,l_stage);
165 Select NVL(p_resource_list_id,bv.resource_list_id)
166 ,NVL(p_rbs_version_id,fp.rbs_version_id)
167 ,NVL(p_project_id,bv.project_id)
168 ,bv.budget_version_id
169 Into g_resource_list_id
170 ,g_rbs_version_id
171 ,g_project_id
172 ,g_budget_version_id
173 From pa_budget_versions bv
174 ,pa_proj_fp_options fp
175 Where bv.budget_version_id = p_budget_version_id
176 And fp.fin_plan_version_id (+) = bv.budget_version_id
177 and rownum = 1;
178 End If;
179
180 Else
181 l_stage := 'Fetch the project Details for the given budget version';
182 print_msg(g_debug_flag,l_stage);
183 If p_budget_version_id is NOT NULL and p_project_id is NULL Then
184 Select NVL(p_project_id,project_id)
185 Into g_project_id
186 From pa_budget_versions
187 Where budget_version_id = p_budget_version_id;
188 Else
189 g_project_id := p_project_id;
190 End If;
191
192 g_resource_list_id := p_resource_list_id;
193 g_rbs_version_id := p_rbs_version_id;
194 g_budget_version_id := p_budget_version_id;
195
196
197 End If;
198 l_stage := 'End Of Init_ReqdVariables';
199 print_msg(g_debug_flag,l_stage);
200 EXCEPTION
201 WHEN OTHERS THEN
202 print_msg(g_debug_flag,l_stage||':'||SQLCODE||SQLERRM);
203 RAISE;
204
205 END Init_ReqdVariables;
206
207 /* This API inserts records into RBS mapping tmp tables
208 * the records will be inserted Based on calling mode
209 */
210 PROCEDURE populate_rbsmap_tmp
211 (p_budget_version_id IN Number
212 ,p_calling_mode IN varchar2
213 ,x_return_status OUT NOCOPY varchar2 ) IS
214
215 l_stage varchar2(1000);
216
217 -- bug 14040849 start
218 CURSOR get_res_asmts_cur IS
219 select resource_assignment_id, expenditure_type, non_labor_resource,
220 expenditure_category, event_type, incur_by_res_class_code, resource_class_code
221 from pa_resource_assignments where budget_version_id = p_budget_version_id;
222
223 l_get_res_asmts_rec get_res_asmts_cur%ROWTYPE;
224
225 l_expenditure_type_id NUMBER;
226 l_non_labor_resource_id NUMBER;
227 l_resource_class_id NUMBER;
228 l_expenditure_category_id NUMBER;
229 l_event_type_id NUMBER;
230
231 TYPE expenditure_type_id_t IS TABLE OF pa_rbs_plans_in_tmp.expenditure_type_id%TYPE;
232 l_EXPENDITURE_TYPE_ID_TBL expenditure_type_id_t := expenditure_type_id_t();
233
234 TYPE non_labor_resource_id_t IS TABLE OF pa_rbs_plans_in_tmp.non_labor_resource_id%TYPE;
235 l_NON_LABOR_RESOURCE_ID_TBL non_labor_resource_id_t := non_labor_resource_id_t();
236
237 TYPE resource_class_id_t IS TABLE OF pa_rbs_plans_in_tmp.resource_class_id%TYPE;
238 l_RESOURCE_CLASS_ID_TBL resource_class_id_t := resource_class_id_t();
239
240 TYPE expenditure_category_id_t IS TABLE OF pa_rbs_plans_in_tmp.expenditure_category_id%TYPE;
241 L_expenditure_category_id_TBL expenditure_category_id_t := expenditure_category_id_t();
242
243 TYPE event_type_id_t IS TABLE OF pa_rbs_plans_in_tmp.event_type_id%TYPE;
244 l_EVENT_TYPE_ID_TBL event_type_id_t := event_type_id_t();
245
246 -- bug 14040849 end
247
248 BEGIN
249 l_stage := 'Start of populate_rbsmap_tmp';
250 print_msg(g_debug_flag,l_stage);
251 /* Initialize the IN and OUT tmp tables */
252 DELETE FROM pa_rbs_plans_in_tmp ;
253 DELETE FROM pa_rbs_plans_out_tmp;
254 If p_calling_mode = 'BUDGET_VERSION' Then
255 l_stage := 'Inserting recrods into pa_rbs_plans_in_tmp for the budget version';
256 print_msg(g_debug_flag,l_stage);
257 INSERT INTO pa_rbs_plans_in_tmp
258 (source_id
259 ,person_id
260 ,Job_id
261 ,organization_id
262 ,Supplier_id
263 --,Expenditure_type_id
264 --,Event_type_id
265 --,Expenditure_category_id
266 --,Non_labor_resource_id
267 --,Resource_class_id
268 ,Revenue_category_code
269 ,Inventory_item_id
270 ,Item_category_id
271 ,Bom_labor_id
272 ,Bom_equipment_id
273 ,Role_id
274 ,Person_type_code
275 )
276 SELECT ra.resource_assignment_id
277 ,ra.person_id
278 ,ra.job_id
279 ,ra.organization_id
280 ,ra.supplier_id
281 --,et.expenditure_type_id
282 --,ev.event_type_id
283 --,ec.expenditure_category_id
284 --,nlr.non_labor_resource_id
285 --,rc.resource_class_id
286 ,ra.revenue_category_code
287 ,ra.inventory_item_id
288 ,ra.item_category_id
289 ,decode(ra.bom_resource_id,NULL,NULL,
290 decode(nvl(ra.incur_by_res_class_code,ra.resource_class_code),'PEOPLE',ra.bom_resource_id,NULL))
291 ,decode(ra.bom_resource_id,NULL,NULL,
292 decode(nvl(ra.incur_by_res_class_code,ra.resource_class_code),'EQUIPMENT',ra.bom_resource_id,NULL))
293 ,nvl(ra.incur_by_role_id,ra.project_role_id)
294 ,ra.person_type_code
295 FROM pa_resource_assignments ra
296 --,pa_budget_versions bv
297 --,pa_proj_fp_options fp
298 --,pa_expenditure_types et
299 --,pa_non_labor_resources nlr
300 --,pa_expenditure_categories ec
301 --,pa_event_types ev
302 --,pa_resource_classes_b rc
303 WHERE ra.budget_version_id = p_budget_version_id
304 --AND ra.budget_version_id = bv.budget_version_id (+)
305 --ANd bv.budget_version_id = fp.fin_plan_version_id(+)
306 --and ra.expenditure_type = et.expenditure_type (+)
307 --and ra.non_labor_resource = nlr.non_labor_resource (+)
308 --and ra.expenditure_category = ec.expenditure_category (+)
309 --and ra.event_type = ev.event_type (+)
310 --and ra.resource_class_code = rc.resource_class_code (+)
311 ;
312 g_rbs_numRecInserted := sql%Rowcount ;
313 l_stage := 'Num Of Records Inserted ['||g_rbs_numRecInserted||']';
314 print_msg(g_debug_flag,l_stage);
315 /* bug fix: 3678165 added this update commented out the outer join from insert */
316 If g_rbs_numRecInserted > 0 Then
317 l_stage := 'Update the tmp table with exp,event,cate,Ids';
318 /* replaced the update stmt with the one below for bug 14040849
319 UPDATE pa_rbs_plans_in_tmp tmp
320 SET tmp.expenditure_type_id = (select et.expenditure_type_id
321 from pa_expenditure_types et
322 ,pa_resource_assignments ra
323 where et.expenditure_type = ra.expenditure_type
324 and ra.resource_assignment_id = tmp.source_id
325 and rownum =1 )
326 ,tmp.non_labor_resource_id = (select nlr.non_labor_resource_id
327 from pa_non_labor_resources nlr
328 ,pa_resource_assignments ra
329 where nlr.non_labor_resource = ra.non_labor_resource
330 and ra.resource_assignment_id = tmp.source_id
331 and rownum = 1)
332 ,tmp.expenditure_category_id = (select ec.expenditure_category_id
333 from pa_expenditure_categories ec
334 ,pa_resource_assignments ra
335 where ec.expenditure_category = ra.expenditure_category
336 and ra.resource_assignment_id = tmp.source_id
337 and rownum = 1)
338 ,tmp.event_type_id = (select ev.event_type_id
339 from pa_event_types ev
340 ,pa_resource_assignments ra
341 where ra.event_type = ev.event_type
342 and ra.resource_assignment_id = tmp.source_id
343 and rownum = 1)
344 ,tmp.resource_class_id = (select rc.resource_class_id
345 from pa_resource_classes_b rc
346 ,pa_resource_assignments ra
347 where nvl(ra.incur_by_res_class_code,ra.resource_class_code) = rc.resource_class_code
348 and ra.resource_assignment_id = tmp.source_id
349 and rownum = 1) ;
350 End comment for bug 14040849*/
351 -- bug 14040849 start
352 FOR l_get_res_asmts_rec IN get_res_asmts_cur LOOP
353
354 l_EXPENDITURE_TYPE_ID := NULL;
355 l_NON_LABOR_RESOURCE_ID := NULL;
356 l_RESOURCE_CLASS_ID := NULL;
357 l_EXPENDITURE_CATEGORY_ID := NULL;
358 l_EVENT_TYPE_ID := NULL;
359
360 IF l_get_res_asmts_rec.expenditure_type IS NOT NULL THEN
361 SELECT ET.EXPENDITURE_TYPE_ID into l_EXPENDITURE_TYPE_ID
362 FROM PA_EXPENDITURE_TYPES ET WHERE ET.EXPENDITURE_TYPE = l_get_res_asmts_rec.expenditure_type;
363 END IF;
364
365 IF l_get_res_asmts_rec.non_labor_resource IS NOT NULL THEN
366 SELECT NLR.NON_LABOR_RESOURCE_ID into l_NON_LABOR_RESOURCE_ID
367 FROM PA_NON_LABOR_RESOURCES NLR WHERE NLR.NON_LABOR_RESOURCE = l_get_res_asmts_rec.non_labor_resource;
368 END IF;
369
370 IF (l_get_res_asmts_rec.incur_by_res_class_code IS NOT NULL) OR (l_get_res_asmts_rec.resource_class_code) IS NOT NULL THEN
371 SELECT RC.RESOURCE_CLASS_ID into l_RESOURCE_CLASS_ID
372 FROM PA_RESOURCE_CLASSES_B RC
373 WHERE RC.RESOURCE_CLASS_CODE = NVL(l_get_res_asmts_rec.incur_by_res_class_code,l_get_res_asmts_rec.resource_class_code)
374 and rownum = 1;
375 END IF;
376
377 IF l_get_res_asmts_rec.expenditure_category IS NOT NULL THEN
378 SELECT EC.EXPENDITURE_CATEGORY_ID into l_EXPENDITURE_CATEGORY_ID
379 FROM PA_EXPENDITURE_CATEGORIES EC WHERE EC.EXPENDITURE_CATEGORY = l_get_res_asmts_rec.expenditure_category;
380 END IF;
381
382 IF l_get_res_asmts_rec.event_type IS NOT NULL THEN
383 SELECT EVT.EVENT_TYPE_ID into l_EVENT_TYPE_ID
384 FROM PA_EVENT_TYPES EVT WHERE EVT.EVENT_TYPE = l_get_res_asmts_rec.event_type;
385 END IF;
386
387 UPDATE PA_RBS_PLANS_IN_TMP
388 SET EXPENDITURE_TYPE_ID = l_EXPENDITURE_TYPE_ID,
389 NON_LABOR_RESOURCE_ID = l_NON_LABOR_RESOURCE_ID,
390 RESOURCE_CLASS_ID = l_RESOURCE_CLASS_ID,
391 EXPENDITURE_CATEGORY_ID = l_EXPENDITURE_CATEGORY_ID,
392 EVENT_TYPE_ID = l_EVENT_TYPE_ID
393 WHERE SOURCE_ID = l_get_res_asmts_rec.resource_assignment_id;
394
395 END LOOP;
396 -- bug 14040849 end
397
398 print_msg(g_debug_flag,'Number of rows updated on pa_rbs_plans_in_tmp['||sql%Rowcount||']');
399 End IF;
400 Elsif p_calling_mode = 'PLSQL_TABLE' Then
401 l_stage := 'Inserting recrods into pa_rbs_plans_in_tmp from PLSQL tables';
402 print_msg(g_debug_flag,l_stage);
403
404 /* Added for 14040849 Start*/
405 l_EXPENDITURE_TYPE_ID_TBL.extend(g_expenditure_type_sqltab.count);
406 For i in g_expenditure_type_sqltab.FIRST .. g_expenditure_type_sqltab.LAST LOOP
407 IF g_expenditure_type_sqltab(i) IS NOT NULL THEN
408 select et.expenditure_type_id INTO l_EXPENDITURE_TYPE_ID_TBL(i)
409 from pa_expenditure_types et
410 where et.expenditure_type = g_expenditure_type_sqltab(i)
411 and rownum = 1;
412 ELSE
413 l_EXPENDITURE_TYPE_ID_TBL(i) := NULL;
414 END IF;
415 END LOOP;
416
417 l_NON_LABOR_RESOURCE_ID_TBL.extend(g_non_labor_resource_sqltab.count);
418 For i in g_non_labor_resource_sqltab.FIRST .. g_non_labor_resource_sqltab.LAST LOOP
419 IF g_non_labor_resource_sqltab(i) IS NOT NULL THEN
420 select nlr.non_labor_resource_id INTO l_NON_LABOR_RESOURCE_ID_TBL(i)
421 from pa_non_labor_resources nlr
422 where nlr.non_labor_resource = g_non_labor_resource_sqltab(i)
423 and rownum = 1;
424 ELSE
425 l_NON_LABOR_RESOURCE_ID_TBL(i) := NULL;
426 END IF;
427 END LOOP;
428
429 l_RESOURCE_CLASS_ID_TBL.extend(g_resource_class_code_sqltab.count);
430 For i in g_resource_class_code_sqltab.FIRST .. g_resource_class_code_sqltab.LAST LOOP
431 IF g_resource_class_code_sqltab(i) IS NOT NULL THEN
432 select rc.resource_class_id into l_RESOURCE_CLASS_ID_TBL(i)
433 from pa_resource_classes_b rc
434 where rc.resource_class_code = g_resource_class_code_sqltab(i)
435 and rownum = 1;
436 ELSE
437 l_RESOURCE_CLASS_ID_TBL(i) := NULL;
438 END IF;
439 END LOOP;
440
441 L_expenditure_category_id_TBL.extend(g_expenditure_category_sqltab.count);
442 For i in g_expenditure_category_sqltab.FIRST .. g_expenditure_category_sqltab.LAST LOOP
443 IF g_expenditure_category_sqltab(i) IS NOT NULL THEN
444 select ec.expenditure_category_id into L_expenditure_category_id_TBL(i)
445 from pa_expenditure_categories ec
446 where ec.expenditure_category = g_expenditure_category_sqltab(i)
447 and rownum = 1;
448 ELSE
449 L_expenditure_category_id_TBL(i) := NULL;
450 END IF;
451 END LOOP;
452
453 l_EVENT_TYPE_ID_TBL.extend(g_event_type_sqltab.count);
454 For i in g_event_type_sqltab.FIRST .. g_event_type_sqltab.LAST LOOP
455 IF g_event_type_sqltab(i) IS NOT NULL THEN
456 select evt.event_type_id into l_EVENT_TYPE_ID_TBL(i)
457 from pa_event_types evt
458 where evt.event_type = g_event_type_sqltab(i)
459 and rownum = 1;
460 ELSE
461 l_EVENT_TYPE_ID_TBL(i) := NULL;
462 END IF;
463 END LOOP;
464
465 FORALL i IN g_txn_source_id_sqltab.FIRST .. g_txn_source_id_sqltab.LAST
466 INSERT INTO pa_rbs_plans_in_tmp
467 (source_id
468 ,person_id
469 ,Job_id
470 ,organization_id
471 ,Supplier_id
472 ,Expenditure_type_id
473 ,Event_type_id
474 ,Expenditure_category_id
475 ,Revenue_category_code
476 ,Inventory_item_id
477 ,Item_category_id
478 ,Bom_labor_id
479 ,Bom_equipment_id
480 ,Non_labor_resource_id
481 ,Role_id
482 ,Person_type_code
483 ,Resource_class_id
484 )
485 SELECT g_txn_source_id_sqltab(i)
486 ,g_person_id_sqltab(i)
487 ,g_job_id_sqltab(i)
488 ,g_organization_id_sqltab(i)
489 ,g_vendor_id_sqltab(i)
490 ,l_EXPENDITURE_TYPE_ID_TBL(i) --et.expenditure_type_id //added for 14040849
491 ,l_EVENT_TYPE_ID_TBL(i) --ev.event_type_id //added for 14040849
492 ,L_expenditure_category_id_TBL(i) --ec.expenditure_category_id //added for 14040849
493 ,g_revenue_category_code_sqltab(i)
494 ,g_inventory_item_id_sqltab(i)
495 ,g_item_category_id_sqltab(i)
496 ,decode(g_bom_resource_id_sqltab(i),NULL,NULL,
497 decode(g_resource_class_code_sqltab(i),'PEOPLE',g_bom_resource_id_sqltab(i),NULL))
498 ,decode(g_bom_resource_id_sqltab(i),NULL,NULL,
499 decode(g_resource_class_code_sqltab(i),'EQUIPMENT',g_bom_resource_id_sqltab(i),NULL))
500 ,l_NON_LABOR_RESOURCE_ID_TBL(i) --nlr.non_labor_resource_id //added for 14040849
501 ,g_project_role_id_sqltab(i)
502 ,g_person_type_code_sqltab(i)
503 ,l_RESOURCE_CLASS_ID_TBL(i) --rc.resource_class_id //added for 14040849
504 FROM Dual ;
505
506 g_rbs_numRecInserted := g_txn_source_id_sqltab.Count ;
507 l_stage := 'Num Of Records Inserted ['||g_rbs_numRecInserted||']';
508 print_msg(g_debug_flag,l_stage);
509 -- added for 14040849 commented below update statement and added the same in insert itself
510 /*
511 FORALL i IN g_txn_source_id_sqltab.FIRST .. g_txn_source_id_sqltab.LAST
512 UPDATE pa_rbs_plans_in_tmp tmp
513 SET tmp.expenditure_type_id = l_EXPENDITURE_TYPE_ID_TBL(i)
514 , tmp.non_labor_resource_id = l_NON_LABOR_RESOURCE_ID_TBL(i)
515 ,tmp.resource_class_id = l_RESOURCE_CLASS_ID_TBL(i)
516 ,tmp.expenditure_category_id = L_expenditure_category_id_TBL(i)
517 /* Bug fix: 3999186 populating event type */
518 /* ,tmp.event_type_id = l_EVENT_TYPE_ID_TBL(i)
519 WHERE tmp.source_id = g_txn_source_id_sqltab(i); */
520 /* end of 14040849 */
521 Elsif p_calling_mode = 'SYSTEM_TABLE' Then
522 l_stage := 'Inserting recrods into pa_rbs_plans_in_tmp from SYSTEM tables';
523 print_msg(g_debug_flag,l_stage);
524 /* Start of14040849 */
525 l_EXPENDITURE_TYPE_ID_TBL.extend(g_expenditure_type_systab.count);
526 For i in g_expenditure_type_systab.FIRST .. g_expenditure_type_systab.LAST LOOP
527 IF g_expenditure_type_systab(i) IS NOT NULL THEN
528 select et.expenditure_type_id INTO l_EXPENDITURE_TYPE_ID_TBL(i)
529 from pa_expenditure_types et
530 where et.expenditure_type = g_expenditure_type_systab(i)
531 and rownum = 1;
532 ELSE
533 l_EXPENDITURE_TYPE_ID_TBL(i) := NULL;
534 END IF;
535 END LOOP;
536
537 l_NON_LABOR_RESOURCE_ID_TBL.extend(g_non_labor_resource_systab.count);
538 For i in g_non_labor_resource_systab.FIRST .. g_non_labor_resource_systab.LAST LOOP
539 IF g_non_labor_resource_systab(i) IS NOT NULL THEN
540 select nlr.non_labor_resource_id INTO l_NON_LABOR_RESOURCE_ID_TBL(i)
541 from pa_non_labor_resources nlr
542 where nlr.non_labor_resource = g_non_labor_resource_systab(i)
543 and rownum = 1;
544 ELSE
545 l_NON_LABOR_RESOURCE_ID_TBL(i) := NULL;
546 END IF;
547 END LOOP;
548
549 l_RESOURCE_CLASS_ID_TBL.extend(g_resource_class_code_systab.count);
550 For i in g_resource_class_code_systab.FIRST .. g_resource_class_code_systab.LAST LOOP
551 IF g_resource_class_code_systab(i) IS NOT NULL THEN
552 select rc.resource_class_id into l_RESOURCE_CLASS_ID_TBL(i)
553 from pa_resource_classes_b rc
554 where rc.resource_class_code = g_resource_class_code_systab(i)
555 and rownum = 1;
556 ELSE
557 l_RESOURCE_CLASS_ID_TBL(i) := NULL;
558 END IF;
559 END LOOP;
560
561 L_expenditure_category_id_TBL.extend(g_expenditure_category_systab.count);
562 For i in g_expenditure_category_systab.FIRST .. g_expenditure_category_systab.LAST LOOP
563 IF g_expenditure_category_systab(i) IS NOT NULL THEN
564 select ec.expenditure_category_id into L_expenditure_category_id_TBL(i)
565 from pa_expenditure_categories ec
566 where ec.expenditure_category = g_expenditure_category_systab(i)
567 and rownum = 1;
568 ELSE
569 L_expenditure_category_id_TBL(i) := NULL;
570 END IF;
571 END LOOP;
572
573 l_EVENT_TYPE_ID_TBL.extend(g_event_type_systab.count);
574 For i in g_event_type_systab.FIRST .. g_event_type_systab.LAST LOOP
575 IF g_event_type_systab(i) IS NOT NULL THEN
576 select evt.event_type_id into l_EVENT_TYPE_ID_TBL(i)
577 from pa_event_types evt
578 where evt.event_type = g_event_type_systab(i)
579 and rownum = 1;
580 ELSE
581 l_EVENT_TYPE_ID_TBL(i) := NULL;
582 END IF;
583 END LOOP;
584
585 FORALL i IN g_txn_source_id_systab.FIRST .. g_txn_source_id_systab.LAST
586 INSERT INTO pa_rbs_plans_in_tmp
587 (source_id
588 ,person_id
589 ,Job_id
590 ,organization_id
591 ,Supplier_id
592 ,Expenditure_type_id
593 ,Event_type_id
594 ,Expenditure_category_id
595 ,Revenue_category_code
596 ,Inventory_item_id
597 ,Item_category_id
598 ,Bom_labor_id
599 ,Bom_equipment_id
600 ,Non_labor_resource_id
601 ,Role_id
602 ,Person_type_code
603 ,Resource_class_id
604 )
605 SELECT g_txn_source_id_systab(i)
606 ,g_person_id_systab(i)
607 ,g_job_id_systab(i)
608 ,g_organization_id_systab(i)
609 ,g_vendor_id_systab(i)
610 ,l_EXPENDITURE_TYPE_ID_TBL(i) --et.expenditure_type_id -- added for 14040849
611 ,l_EVENT_TYPE_ID_TBL(i) --ev.event_type_id -- added for 14040849
612 ,L_expenditure_category_id_TBL(i) --ec.expenditure_category_id -- added for 14040849
613 ,g_revenue_category_code_systab(i)
614 ,g_inventory_item_id_systab(i)
615 ,g_item_category_id_systab(i)
616 ,decode(g_bom_resource_id_systab(i),NULL,NULL,
617 decode(g_resource_class_code_systab(i),'PEOPLE',g_bom_resource_id_systab(i),NULL))
618 ,decode(g_bom_resource_id_systab(i),NULL,NULL,
619 decode(g_resource_class_code_systab(i),'EQUIPMENT',g_bom_resource_id_systab(i),NULL))
620 ,l_NON_LABOR_RESOURCE_ID_TBL(i) --nlr.non_labor_resource_id -- added for 14040849
621 ,g_project_role_id_systab(i)
622 ,g_person_type_code_systab(i)
623 ,l_RESOURCE_CLASS_ID_TBL(i) --rc.resource_class_id -- added for 14040849
624 FROM Dual ;
625
626 g_rbs_numRecInserted := g_txn_source_id_systab.count;
627 l_stage := 'Num Of Records Inserted ['||g_rbs_numRecInserted||']';
628 print_msg(g_debug_flag,l_stage);
629
630 -- added for 14040849 commented below update statement and added the same in insert itself
631 /* FORALL i IN g_txn_source_id_systab.FIRST .. g_txn_source_id_systab.LAST
632 UPDATE pa_rbs_plans_in_tmp
633 SET expenditure_type_id = l_EXPENDITURE_TYPE_ID_TBL(i)
634 , non_labor_resource_id = l_NON_LABOR_RESOURCE_ID_TBL(i)
635 ,resource_class_id = l_RESOURCE_CLASS_ID_TBL(i)
636 ,expenditure_category_id = L_expenditure_category_id_TBL(i)
637 /* Bug fix: 3999186 populating event type */
638 /* ,event_type_id = l_EVENT_TYPE_ID_TBL(i)
639 WHERE source_id = g_txn_source_id_systab(i); */
640
641 /* End of 14040849 */
642 End If;
643
644 /* Bug fix: 3698579 */
645 -- update exp category id if null
646 UPDATE pa_rbs_plans_in_tmp tmp
647 SET tmp.expenditure_category_id = (select etc.expenditure_category_id
648 from pa_expenditure_types et
649 ,pa_expenditure_categories etc
650 where et.expenditure_type_id = tmp.expenditure_type_id
651 and et.expenditure_category = etc.expenditure_category
652 )
653 WHERE tmp.expenditure_category_id is NULL
654 AND tmp.expenditure_type_id is NOT NULL ;
655
656
657 /* Bug fix: 3999186 populate revenue category based on event types */
658 -- update revenue category if its null based on event types
659 UPDATE pa_rbs_plans_in_tmp tmp
660 SET tmp.Revenue_category_code = (select et.Revenue_category_code
661 from pa_event_types et
662 where et.event_type_id = tmp.event_type_id
663 )
664 WHERE tmp.Revenue_category_code is NULL
665 AND tmp.event_type_id is NOT NULL ;
666 /* end of Bug fix: 3999186 */
667
668 -- update revenue category if its null based on expendiure types
669 UPDATE pa_rbs_plans_in_tmp tmp
670 SET tmp.Revenue_category_code = (select et.Revenue_category_code
671 from pa_expenditure_types et
672 where et.expenditure_type_id = tmp.expenditure_type_id
673 )
674 WHERE tmp.Revenue_category_code is NULL
675 AND tmp.expenditure_type_id is NOT NULL ;
676
677 -- update the default item category
678 UPDATE pa_rbs_plans_in_tmp tmp
679 SET tmp.item_category_id = ( SELECT cat.CATEGORY_ID
680 FROM PA_RESOURCE_CLASSES_B classes
681 ,PA_PLAN_RES_DEFAULTS cls
682 ,MTL_ITEM_CATEGORIES cat
683 WHERE classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS'
684 AND cls.RESOURCE_CLASS_ID = classes.RESOURCE_CLASS_ID
685 AND cls.ITEM_CATEGORY_SET_ID = cat.CATEGORY_SET_ID
686 AND cat.ORGANIZATION_ID = tmp.organization_id
687 AND cat.INVENTORY_ITEM_ID = tmp.inventory_item_id
688 AND rownum = 1
689 )
690 WHERE tmp.item_category_id is NULL
691 AND tmp.inventory_item_id is NOT NULL;
692 /* End of bug fix:3698579 */
693
694 l_stage := 'End Of populate_rbsmap_tmp API';
695 print_msg(g_debug_flag,l_stage);
696
697 EXCEPTION
698 WHEN OTHERS THEN
699 print_msg(g_debug_flag,l_stage||':'||SQLCODE||SQLERRM);
700 RAISE;
701
702 END populate_rbsmap_tmp;
703
704 /* This API inserts records into Resource mapping tmp tables
705 * the records will be inserted Based on calling mode
706 */
707 PROCEDURE populate_resmap_tmp
708 (p_budget_version_id IN Number
709 ,p_calling_mode IN varchar2
710 ,x_return_status OUT NOCOPY varchar2 ) IS
711
712 l_NumRecInserted Number := 0;
713 l_stage Varchar2(1000);
714 l_struct_ver_id pa_budget_versions.project_structure_version_id%TYPE;
715
716 CURSOR Cur_projStrVer IS
717 SELECT decode(nvl(wp_version_flag,'N'),
718 'N',PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID( project_id ),
719 project_structure_version_id)
720 FROM pa_budget_versions
721 WHERE budget_version_id = p_budget_version_id;
722 l_uncategorized_flag VARCHAR2(1);
723 l_financial_res_class_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
724 BEGIN
725
726 l_NumRecInserted := 0;
727 l_stage := 'Start of populate_resmap_tmp ';
728 print_msg(g_debug_flag,l_stage);
729 /* Initialize the IN and OUT tmp tables */
730 DELETE FROM pa_res_list_map_tmp1;
731 DELETE FROM pa_res_list_map_tmp4;
732
733
734 IF p_budget_version_id is NOT NULL Then
735 print_msg(g_debug_flag, 'Getting project structure version Id for ResMap');
736 OPEN Cur_projStrVer;
737 FETCH Cur_projStrVer INTO l_struct_ver_id;
738 IF Cur_projStrVer%NOTFOUND Then
739 l_struct_ver_id := NULL;
740 End IF;
741 CLOSE Cur_projStrVer;
742 End If;
743
744 SELECT nvl(uncategorized_flag,'N')
745 INTO l_uncategorized_flag
746 FROM pa_resource_lists_all_bg
747 WHERE resource_list_id=g_resource_list_id;
748
749 g_call_res_list_mapping_api := 'Y';
750 IF l_uncategorized_flag = 'Y' THEN
751
752 l_financial_res_class_rlm_id := PA_FP_GEN_AMOUNT_UTILS.GET_RLM_ID
753 (p_project_id => g_project_id,
754 p_resource_list_id => g_resource_list_id,
755 p_resource_class_code => 'FINANCIAL_ELEMENTS' );
756 g_call_res_list_mapping_api := 'N';
757
758 END IF;
759
760 --If the target is an uncategorized resource list then all the source resource list members would be mapped
761 --to financial class resource list member in the target. In this case resource list mapping API will not be
762 --called and tmp4 table will be populated directly
763
764 l_stage := 'l_uncategorized_flag IS '||l_uncategorized_flag;
765 print_msg(g_debug_flag,l_stage);
766
767 IF p_calling_mode = 'BUDGET_VERSION' Then
768 l_stage := 'Inserting recrods into pa_res_list_map_tmp1 for the given budget version';
769 print_msg(g_debug_flag,l_stage);
770 IF l_uncategorized_flag = 'Y' THEN
771
772 INSERT INTO pa_res_list_map_tmp4
773 (TXN_SOURCE_ID
774 ,TXN_SOURCE_TYPE_CODE
775 ,PERSON_ID
776 ,JOB_ID
777 ,ORGANIZATION_ID
778 ,VENDOR_ID
779 ,EXPENDITURE_TYPE
780 --,EXPENDITURE_TYPE_ID
781 ,EVENT_TYPE
782 --,EVENT_TYPE_ID
783 ,NON_LABOR_RESOURCE
784 --,NON_LABOR_RESOURCE_ID
785 ,EXPENDITURE_CATEGORY
786 --,EXPENDITURE_CATEGORY_ID
787 ,REVENUE_CATEGORY
788 ,NON_LABOR_RESOURCE_ORG_ID
789 ,EVENT_TYPE_CLASSIFICATION
790 ,SYSTEM_LINKAGE_FUNCTION
791 ,PROJECT_ROLE_ID
792 ,RESOURCE_CLASS_CODE
793 ,MFC_COST_TYPE_ID
794 ,RESOURCE_CLASS_FLAG
795 ,FC_RES_TYPE_CODE
796 ,INVENTORY_ITEM_ID
797 ,ITEM_CATEGORY_ID
798 ,PERSON_TYPE_CODE
799 ,BOM_RESOURCE_ID
800 ,BOM_LABOR_RESOURCE_ID
801 ,BOM_EQUIP_RESOURCE_ID
802 ,NAMED_ROLE
803 --,NAMED_ROLE_ID
804 ,INCURRED_BY_RES_FLAG
805 ,TXN_RATE_BASED_FLAG
806 ,TXN_TASK_ID
807 ,TXN_WBS_ELEMENT_VERSION_ID
808 ,TXN_RBS_ELEMENT_ID
809 ,TXN_PLANNING_START_DATE
810 ,TXN_PLANNING_END_DATE
811 ,TXN_PROJECT_ID
812 ,TXN_BUDGET_VERSION_ID
813 ,resource_list_member_id
814 ,cbs_element_id --bug#16827157
815 )
816 SELECT ra.resource_assignment_id
817 ,'RES_ASSIGNMENT'
818 ,PERSON_ID
819 ,JOB_ID
820 ,ORGANIZATION_ID
821 ,SUPPLIER_ID VENDOR_ID
822 -- bug fix: 3698197 ,NVL(EXPENDITURE_TYPE,RATE_EXPENDITURE_TYPE)
823 ,EXPENDITURE_TYPE
824 --,Null EXPENDITURE_TYPE_ID
825 ,EVENT_TYPE
826 --,Null EVENT_TYPE_ID
827 ,NON_LABOR_RESOURCE
828 --,Null NON_LABOR_RESOURCE_ID
829 ,EXPENDITURE_CATEGORY
830 --,Null EXPENDITURE_CATEGORY_ID
831 ,REVENUE_CATEGORY_CODE
832 ,Null NLR_ORGANIZATION_ID
833 ,Null EVENT_CLASSIFICATION
834 ,Null SYS_LINK_FUNCTION
835 ,NVL(incur_by_role_id,PROJECT_ROLE_ID)
836 ,NVL(incur_by_res_class_code,RESOURCE_CLASS_CODE)
837 ,MFC_COST_TYPE_ID
838 ,RESOURCE_CLASS_FLAG
839 ,FC_RES_TYPE_CODE
840 ,INVENTORY_ITEM_ID
841 ,ITEM_CATEGORY_ID
842 ,PERSON_TYPE_CODE
843 ,BOM_RESOURCE_ID
844 ,decode(bom_resource_id,NULL,NULL,
845 decode(nvl(incur_by_res_class_code,RESOURCE_CLASS_CODE),'PEOPLE',BOM_RESOURCE_ID,NULL))
846 ,decode(bom_resource_id,NULL,NULL,
847 decode(nvl(incur_by_res_class_code,RESOURCE_CLASS_CODE),'EQUIPMENT',BOM_RESOURCE_ID,NULL))
848 ,NAMED_ROLE
849 --,Null NAMED_ROLE_ID
850 ,INCURRED_BY_RES_FLAG
851 ,RATE_BASED_FLAG
852 ,TASK_ID
853 ,NULL --pelm.element_version_id
854 ,RBS_ELEMENT_ID
855 ,PLANNING_START_DATE
856 ,PLANNING_END_DATE
857 ,ra.project_id
858 ,ra.budget_version_id
859 ,l_financial_res_class_rlm_id
860 ,ra.cbs_element_id --bug#16827157
861 FROM pa_resource_assignments ra
862 WHERE ra.budget_version_id = p_budget_version_id;
863
864 ELSE
865
866 INSERT INTO pa_res_list_map_tmp1
867 (TXN_SOURCE_ID
868 ,TXN_SOURCE_TYPE_CODE
869 ,PERSON_ID
870 ,JOB_ID
871 ,ORGANIZATION_ID
872 ,VENDOR_ID
873 ,EXPENDITURE_TYPE
874 --,EXPENDITURE_TYPE_ID
875 ,EVENT_TYPE
876 --,EVENT_TYPE_ID
877 ,NON_LABOR_RESOURCE
878 --,NON_LABOR_RESOURCE_ID
879 ,EXPENDITURE_CATEGORY
880 --,EXPENDITURE_CATEGORY_ID
881 ,REVENUE_CATEGORY
882 ,NON_LABOR_RESOURCE_ORG_ID
883 ,EVENT_TYPE_CLASSIFICATION
884 ,SYSTEM_LINKAGE_FUNCTION
885 ,PROJECT_ROLE_ID
886 ,RESOURCE_CLASS_CODE
887 ,MFC_COST_TYPE_ID
888 ,RESOURCE_CLASS_FLAG
889 ,FC_RES_TYPE_CODE
890 ,INVENTORY_ITEM_ID
891 ,ITEM_CATEGORY_ID
892 ,PERSON_TYPE_CODE
893 ,BOM_RESOURCE_ID
894 ,BOM_LABOR_RESOURCE_ID
895 ,BOM_EQUIP_RESOURCE_ID
896 ,NAMED_ROLE
897 --,NAMED_ROLE_ID
898 ,INCURRED_BY_RES_FLAG
899 ,TXN_RATE_BASED_FLAG
900 ,TXN_TASK_ID
901 ,TXN_WBS_ELEMENT_VERSION_ID
902 ,TXN_RBS_ELEMENT_ID
903 ,TXN_PLANNING_START_DATE
904 ,TXN_PLANNING_END_DATE
905 ,TXN_PROJECT_ID
906 ,TXN_BUDGET_VERSION_ID
907 ,cbs_element_id --bug#16827157
908 )
909 SELECT ra.resource_assignment_id
910 ,'RES_ASSIGNMENT'
911 ,PERSON_ID
912 ,JOB_ID
913 ,ORGANIZATION_ID
914 ,SUPPLIER_ID VENDOR_ID
915 -- bug fix: 3698197 ,NVL(EXPENDITURE_TYPE,RATE_EXPENDITURE_TYPE)
916 ,EXPENDITURE_TYPE
917 --,Null EXPENDITURE_TYPE_ID
918 ,EVENT_TYPE
919 --,Null EVENT_TYPE_ID
920 ,NON_LABOR_RESOURCE
921 --,Null NON_LABOR_RESOURCE_ID
922 ,EXPENDITURE_CATEGORY
923 --,Null EXPENDITURE_CATEGORY_ID
924 ,REVENUE_CATEGORY_CODE
925 ,Null NLR_ORGANIZATION_ID
926 ,Null EVENT_CLASSIFICATION
927 ,Null SYS_LINK_FUNCTION
928 ,NVL(incur_by_role_id,PROJECT_ROLE_ID)
929 ,NVL(incur_by_res_class_code,RESOURCE_CLASS_CODE)
930 ,MFC_COST_TYPE_ID
931 ,RESOURCE_CLASS_FLAG
932 ,FC_RES_TYPE_CODE
933 ,INVENTORY_ITEM_ID
934 ,ITEM_CATEGORY_ID
935 ,PERSON_TYPE_CODE
936 ,BOM_RESOURCE_ID
937 ,decode(bom_resource_id,NULL,NULL,
938 decode(nvl(incur_by_res_class_code,RESOURCE_CLASS_CODE),'PEOPLE',BOM_RESOURCE_ID,NULL))
939 ,decode(bom_resource_id,NULL,NULL,
940 decode(nvl(incur_by_res_class_code,RESOURCE_CLASS_CODE),'EQUIPMENT',BOM_RESOURCE_ID,NULL))
941 ,NAMED_ROLE
942 --,Null NAMED_ROLE_ID
943 ,INCURRED_BY_RES_FLAG
944 ,RATE_BASED_FLAG
945 ,TASK_ID
946 ,NULL --pelm.element_version_id
947 ,RBS_ELEMENT_ID
948 ,PLANNING_START_DATE
949 ,PLANNING_END_DATE
950 ,ra.project_id
951 ,ra.budget_version_id
952 ,ra.cbs_element_id --bug#16827157
953 FROM pa_resource_assignments ra
954 WHERE ra.budget_version_id = p_budget_version_id;
955
956 END IF;
957
958 l_NumRecInserted := sql%Rowcount;
959 g_res_numRecInserted := l_NumRecInserted ;
960 l_stage := 'Num of Records inserted ['||g_res_numRecInserted||']';
961 print_msg(g_debug_flag,l_stage);
962 Elsif p_calling_mode = 'PLSQL_TABLE' Then
963
964 /* Insert these plsql tables into ResMap Temp Tables*/
965 If g_TXN_SOURCE_ID_sqltab.COUNT > 0 Then
966
967 IF l_uncategorized_flag = 'Y' THEN
968 l_stage := 'Inserting records into pa_res_list_map_tmp4 from PLSQL tables';
969 print_msg(g_debug_flag,l_stage);
970 FORALL i IN g_TXN_SOURCE_ID_sqltab.FIRST .. g_TXN_SOURCE_ID_sqltab.LAST
971 INSERT INTO pa_res_list_map_tmp4
972 (TXN_SOURCE_ID
973 ,TXN_SOURCE_TYPE_CODE
974 ,PERSON_ID
975 ,JOB_ID
976 ,ORGANIZATION_ID
977 ,VENDOR_ID
978 ,EXPENDITURE_TYPE
979 --,EXPENDITURE_TYPE_ID
980 ,EVENT_TYPE
981 --,EVENT_TYPE_ID
982 ,NON_LABOR_RESOURCE
983 --,NON_LABOR_RESOURCE_ID
984 ,EXPENDITURE_CATEGORY
985 --,EXPENDITURE_CATEGORY_ID
986 ,REVENUE_CATEGORY
987 ,NON_LABOR_RESOURCE_ORG_ID
988 ,EVENT_TYPE_CLASSIFICATION
989 ,SYSTEM_LINKAGE_FUNCTION
990 ,PROJECT_ROLE_ID
991 ,RESOURCE_CLASS_CODE
992 ,MFC_COST_TYPE_ID
993 ,RESOURCE_CLASS_FLAG
994 ,FC_RES_TYPE_CODE
995 ,INVENTORY_ITEM_ID
996 ,ITEM_CATEGORY_ID
997 ,PERSON_TYPE_CODE
998 ,BOM_RESOURCE_ID
999 ,BOM_LABOR_RESOURCE_ID
1000 ,BOM_EQUIP_RESOURCE_ID
1001 ,NAMED_ROLE
1002 --,NAMED_ROLE_ID
1003 ,INCURRED_BY_RES_FLAG
1004 ,TXN_RATE_BASED_FLAG
1005 ,TXN_TASK_ID
1006 ,TXN_WBS_ELEMENT_VERSION_ID
1007 ,TXN_RBS_ELEMENT_ID
1008 ,TXN_PLANNING_START_DATE
1009 ,TXN_PLANNING_END_DATE
1010 ,TXN_PROJECT_ID
1011 ,TXN_BUDGET_VERSION_ID
1012 ,resource_list_member_id
1013 ,cbs_element_id --bug#16827157
1014 )
1015 SELECT g_TXN_SOURCE_ID_sqltab(i)
1016 ,g_TXN_SOURCE_TYPE_CODE_sqltab(i)
1017 ,g_PERSON_ID_sqltab(i)
1018 ,g_JOB_ID_sqltab(i)
1019 ,g_ORGANIZATION_ID_sqltab(i)
1020 ,g_VENDOR_ID_sqltab(i)
1021 ,g_EXPENDITURE_TYPE_sqltab(i)
1022 --,g_EXPENDITURE_TYPE_ID_sqltab(i)
1023 ,g_EVENT_TYPE_sqltab(i)
1024 --,g_EVENT_TYPE_ID_sqltab(i)
1025 ,g_NON_LABOR_RESOURCE_sqltab(i)
1026 --,g_NON_LABOR_RESOURCE_ID_sqltab(i)
1027 ,g_EXPENDITURE_CATEGORY_sqltab(i)
1028 --,g_EXP_CATEGORY_ID_sqltab(i)
1029 ,g_REVENUE_CATEGORY_CODE_sqltab(i)
1030 ,g_NLR_ORGANIZATION_ID_sqltab(i)
1031 ,g_EVENT_CLASSIFICATION_sqltab(i)
1032 ,g_SYS_LINK_FUNCTION_sqltab(i)
1033 ,g_PROJECT_ROLE_ID_sqltab(i)
1034 ,g_RESOURCE_CLASS_CODE_sqltab(i)
1035 ,g_MFC_COST_TYPE_ID_sqltab(i)
1036 ,g_RESOURCE_CLASS_FLAG_sqltab(i)
1037 ,g_FC_RES_TYPE_CODE_sqltab(i)
1038 ,g_INVENTORY_ITEM_ID_sqltab(i)
1039 ,g_ITEM_CATEGORY_ID_sqltab(i)
1040 ,g_PERSON_TYPE_CODE_sqltab(i)
1041 ,g_BOM_RESOURCE_ID_sqltab(i)
1042 ,decode(g_BOM_RESOURCE_ID_sqltab(i),NULL,NULL,
1043 decode(g_RESOURCE_CLASS_CODE_sqltab(i),'PEOPLE',g_BOM_RESOURCE_ID_sqltab(i),NULL))
1044 ,decode(g_BOM_RESOURCE_ID_sqltab(i),NULL,NULL,
1045 decode(g_RESOURCE_CLASS_CODE_sqltab(i),'EQUIPMENT',g_BOM_RESOURCE_ID_sqltab(i),NULL))
1046 ,g_NAMED_ROLE_sqltab(i)
1047 --,g_NAMED_ROLE_ID_sqltab(i)
1048 ,g_INCURRED_BY_RES_FLAG_sqltab(i)
1049 ,g_RATE_BASED_FLAG_sqltab(i)
1050 ,g_TXN_TASK_ID_sqltab(i)
1051 ,g_TXN_WBS_ELE_VER_ID_sqltab(i)
1052 ,g_TXN_RBS_ELEMENT_ID_sqltab(i)
1053 ,g_TXN_PLAN_START_DATE_sqltab(i)
1054 ,g_TXN_PLAN_END_DATE_sqltab (i)
1055 ,g_PROJECT_ID
1056 ,g_BUDGET_VERSION_ID
1057 ,l_financial_res_class_rlm_id --bug#16827157
1058 ,g_CBS_ELEMENT_ID_sqltab(i)
1059 FROM DUAL;
1060
1061
1062 ELSE -- IF l_uncategorized_flag = 'Y' THEN
1063 l_stage := 'Inserting records into pa_res_list_map_tmp1 from PLSQL tables';
1064 print_msg(g_debug_flag,l_stage);
1065 FORALL i IN g_TXN_SOURCE_ID_sqltab.FIRST .. g_TXN_SOURCE_ID_sqltab.LAST
1066 INSERT INTO pa_res_list_map_tmp1
1067 (TXN_SOURCE_ID
1068 ,TXN_SOURCE_TYPE_CODE
1069 ,PERSON_ID
1070 ,JOB_ID
1071 ,ORGANIZATION_ID
1072 ,VENDOR_ID
1073 ,EXPENDITURE_TYPE
1074 --,EXPENDITURE_TYPE_ID
1075 ,EVENT_TYPE
1076 --,EVENT_TYPE_ID
1077 ,NON_LABOR_RESOURCE
1078 --,NON_LABOR_RESOURCE_ID
1079 ,EXPENDITURE_CATEGORY
1080 --,EXPENDITURE_CATEGORY_ID
1081 ,REVENUE_CATEGORY
1082 ,NON_LABOR_RESOURCE_ORG_ID
1083 ,EVENT_TYPE_CLASSIFICATION
1084 ,SYSTEM_LINKAGE_FUNCTION
1085 ,PROJECT_ROLE_ID
1086 ,RESOURCE_CLASS_CODE
1087 ,MFC_COST_TYPE_ID
1088 ,RESOURCE_CLASS_FLAG
1089 ,FC_RES_TYPE_CODE
1090 ,INVENTORY_ITEM_ID
1091 ,ITEM_CATEGORY_ID
1092 ,PERSON_TYPE_CODE
1093 ,BOM_RESOURCE_ID
1094 ,BOM_LABOR_RESOURCE_ID
1095 ,BOM_EQUIP_RESOURCE_ID
1096 ,NAMED_ROLE
1097 --,NAMED_ROLE_ID
1098 ,INCURRED_BY_RES_FLAG
1099 ,TXN_RATE_BASED_FLAG
1100 ,TXN_TASK_ID
1101 ,TXN_WBS_ELEMENT_VERSION_ID
1102 ,TXN_RBS_ELEMENT_ID
1103 ,TXN_PLANNING_START_DATE
1104 ,TXN_PLANNING_END_DATE
1105 ,TXN_PROJECT_ID
1106 ,TXN_BUDGET_VERSION_ID
1107 ,cbs_element_id --bug#16827157
1108 )
1109 SELECT g_TXN_SOURCE_ID_sqltab(i)
1110 ,g_TXN_SOURCE_TYPE_CODE_sqltab(i)
1111 ,g_PERSON_ID_sqltab(i)
1112 ,g_JOB_ID_sqltab(i)
1113 ,g_ORGANIZATION_ID_sqltab(i)
1114 ,g_VENDOR_ID_sqltab(i)
1115 ,g_EXPENDITURE_TYPE_sqltab(i)
1116 --,g_EXPENDITURE_TYPE_ID_sqltab(i)
1117 ,g_EVENT_TYPE_sqltab(i)
1118 --,g_EVENT_TYPE_ID_sqltab(i)
1119 ,g_NON_LABOR_RESOURCE_sqltab(i)
1120 --,g_NON_LABOR_RESOURCE_ID_sqltab(i)
1121 ,g_EXPENDITURE_CATEGORY_sqltab(i)
1122 --,g_EXP_CATEGORY_ID_sqltab(i)
1123 ,g_REVENUE_CATEGORY_CODE_sqltab(i)
1124 ,g_NLR_ORGANIZATION_ID_sqltab(i)
1125 ,g_EVENT_CLASSIFICATION_sqltab(i)
1126 ,g_SYS_LINK_FUNCTION_sqltab(i)
1127 ,g_PROJECT_ROLE_ID_sqltab(i)
1128 ,g_RESOURCE_CLASS_CODE_sqltab(i)
1129 ,g_MFC_COST_TYPE_ID_sqltab(i)
1130 ,g_RESOURCE_CLASS_FLAG_sqltab(i)
1131 ,g_FC_RES_TYPE_CODE_sqltab(i)
1132 ,g_INVENTORY_ITEM_ID_sqltab(i)
1133 ,g_ITEM_CATEGORY_ID_sqltab(i)
1134 ,g_PERSON_TYPE_CODE_sqltab(i)
1135 ,g_BOM_RESOURCE_ID_sqltab(i)
1136 ,decode(g_BOM_RESOURCE_ID_sqltab(i),NULL,NULL,
1137 decode(g_RESOURCE_CLASS_CODE_sqltab(i),'PEOPLE',g_BOM_RESOURCE_ID_sqltab(i),NULL))
1138 ,decode(g_BOM_RESOURCE_ID_sqltab(i),NULL,NULL,
1139 decode(g_RESOURCE_CLASS_CODE_sqltab(i),'EQUIPMENT',g_BOM_RESOURCE_ID_sqltab(i),NULL))
1140 ,g_NAMED_ROLE_sqltab(i)
1141 --,g_NAMED_ROLE_ID_sqltab(i)
1142 ,g_INCURRED_BY_RES_FLAG_sqltab(i)
1143 ,g_RATE_BASED_FLAG_sqltab(i)
1144 ,g_TXN_TASK_ID_sqltab(i)
1145 ,g_TXN_WBS_ELE_VER_ID_sqltab(i)
1146 ,g_TXN_RBS_ELEMENT_ID_sqltab(i)
1147 ,g_TXN_PLAN_START_DATE_sqltab(i)
1148 ,g_TXN_PLAN_END_DATE_sqltab (i)
1149 ,g_PROJECT_ID
1150 ,g_BUDGET_VERSION_ID
1151 ,g_CBS_ELEMENT_ID_sqltab(i) --bug#16827157
1152 FROM DUAL;
1153 END IF; -- IF l_uncategorized_flag = 'Y' THEN
1154 l_NumRecInserted := sql%Rowcount;
1155 g_res_numRecInserted := l_NumRecInserted;
1156
1157
1158
1159 l_stage := 'Num of Records inserted ['||g_res_numRecInserted||']';
1160 print_msg(g_debug_flag,l_stage);
1161 End If;--If g_TXN_SOURCE_ID_sqltab.COUNT > 0 Then
1162
1163 Elsif p_calling_mode = 'SYSTEM_TABLE' Then
1164 /* Insert these system.tab into ResMap Temp Tables*/
1165 If g_TXN_SOURCE_ID_systab.COUNT > 0 Then
1166 IF l_uncategorized_flag = 'Y' THEN
1167
1168 l_stage := 'Inserting records into pa_res_list_map_tmp4 from SYSTEM tables';
1169 print_msg(g_debug_flag,l_stage);
1170 FORALL i IN g_TXN_SOURCE_ID_systab.FIRST .. g_TXN_SOURCE_ID_systab.LAST
1171 INSERT INTO pa_res_list_map_tmp4
1172 (TXN_SOURCE_ID
1173 ,TXN_SOURCE_TYPE_CODE
1174 ,PERSON_ID
1175 ,JOB_ID
1176 ,ORGANIZATION_ID
1177 ,VENDOR_ID
1178 ,EXPENDITURE_TYPE
1179 --,EXPENDITURE_TYPE_ID
1180 ,EVENT_TYPE
1181 --,EVENT_TYPE_ID
1182 ,NON_LABOR_RESOURCE
1183 --,NON_LABOR_RESOURCE_ID
1184 ,EXPENDITURE_CATEGORY
1185 --,EXPENDITURE_CATEGORY_ID
1186 ,REVENUE_CATEGORY
1187 ,NON_LABOR_RESOURCE_ORG_ID
1188 ,EVENT_TYPE_CLASSIFICATION
1189 ,SYSTEM_LINKAGE_FUNCTION
1190 ,PROJECT_ROLE_ID
1191 ,RESOURCE_CLASS_CODE
1192 ,MFC_COST_TYPE_ID
1193 ,RESOURCE_CLASS_FLAG
1194 ,FC_RES_TYPE_CODE
1195 ,INVENTORY_ITEM_ID
1196 ,ITEM_CATEGORY_ID
1197 ,PERSON_TYPE_CODE
1198 ,BOM_RESOURCE_ID
1199 ,BOM_LABOR_RESOURCE_ID
1200 ,BOM_EQUIP_RESOURCE_ID
1201 ,NAMED_ROLE
1202 --,NAMED_ROLE_ID
1203 ,INCURRED_BY_RES_FLAG
1204 ,TXN_RATE_BASED_FLAG
1205 ,TXN_TASK_ID
1206 ,TXN_WBS_ELEMENT_VERSION_ID
1207 ,TXN_RBS_ELEMENT_ID
1208 ,TXN_PLANNING_START_DATE
1209 ,TXN_PLANNING_END_DATE
1210 ,TXN_PROJECT_ID
1211 ,TXN_BUDGET_VERSION_ID
1212 ,resource_list_member_id
1213 ,cbs_element_id --bug#16827157
1214 )
1215 SELECT g_TXN_SOURCE_ID_systab(i)
1216 ,g_TXN_SOURCE_TYPE_CODE_systab(i)
1217 ,g_PERSON_ID_systab(i)
1218 ,g_JOB_ID_systab(i)
1219 ,g_ORGANIZATION_ID_systab(i)
1220 ,g_VENDOR_ID_systab(i)
1221 ,g_EXPENDITURE_TYPE_systab(i)
1222 --,g_EXPENDITURE_TYPE_ID_systab(i)
1223 ,g_EVENT_TYPE_systab(i)
1224 --,g_EVENT_TYPE_ID_systab(i)
1225 ,g_NON_LABOR_RESOURCE_systab(i)
1226 --,g_NON_LABOR_RESOURCE_ID_systab(i)
1227 ,g_EXPENDITURE_CATEGORY_systab(i)
1228 --,g_EXP_CATEGORY_ID_systab(i)
1229 ,g_REVENUE_CATEGORY_CODE_systab(i)
1230 ,g_NLR_ORGANIZATION_ID_systab(i)
1231 ,g_EVENT_CLASSIFICATION_systab(i)
1232 ,g_SYS_LINK_FUNCTION_systab(i)
1233 ,g_PROJECT_ROLE_ID_systab(i)
1234 ,g_RESOURCE_CLASS_CODE_systab(i)
1235 ,g_MFC_COST_TYPE_ID_systab(i)
1236 ,g_RESOURCE_CLASS_FLAG_systab(i)
1237 ,g_FC_RES_TYPE_CODE_systab(i)
1238 ,g_INVENTORY_ITEM_ID_systab(i)
1239 ,g_ITEM_CATEGORY_ID_systab(i)
1240 ,g_PERSON_TYPE_CODE_systab(i)
1241 ,g_BOM_RESOURCE_ID_systab(i)
1242 ,decode(g_BOM_RESOURCE_ID_systab(i),NULL,NULL,
1243 decode(g_RESOURCE_CLASS_CODE_systab(i),'PEOPLE',g_BOM_RESOURCE_ID_systab(i),NULL))
1244 ,decode(g_BOM_RESOURCE_ID_systab(i),NULL,NULL,
1245 decode(g_RESOURCE_CLASS_CODE_systab(i),'EQUIPMENT',g_BOM_RESOURCE_ID_systab(i),NULL))
1246 ,g_NAMED_ROLE_systab(i)
1247 --,g_NAMED_ROLE_ID_systab(i)
1248 ,g_INCURRED_BY_RES_FLAG_systab(i)
1249 ,g_RATE_BASED_FLAG_systab(i)
1250 ,g_TXN_TASK_ID_systab(i)
1251 ,g_TXN_WBS_ELE_VER_ID_systab(i)
1252 ,g_TXN_RBS_ELEMENT_ID_systab(i)
1253 ,g_TXN_PLAN_START_DATE_systab(i)
1254 ,g_TXN_PLAN_END_DATE_systab (i)
1255 ,g_PROJECT_ID
1256 ,g_BUDGET_VERSION_ID
1257 ,l_financial_res_class_rlm_id
1258 ,g_CBS_ELEMENT_ID_systab(i) --bug#16827157
1259 FROM DUAL;
1260
1261 ELSE--IF l_uncategorized_flag = 'Y' THEN
1262
1263 l_stage := 'Inserting records into pa_res_list_map_tmp1 from SYSTEM tables';
1264 print_msg(g_debug_flag,l_stage);
1265 FORALL i IN g_TXN_SOURCE_ID_systab.FIRST .. g_TXN_SOURCE_ID_systab.LAST
1266 INSERT INTO pa_res_list_map_tmp1
1267 (TXN_SOURCE_ID
1268 ,TXN_SOURCE_TYPE_CODE
1269 ,PERSON_ID
1270 ,JOB_ID
1271 ,ORGANIZATION_ID
1272 ,VENDOR_ID
1273 ,EXPENDITURE_TYPE
1274 --,EXPENDITURE_TYPE_ID
1275 ,EVENT_TYPE
1276 --,EVENT_TYPE_ID
1277 ,NON_LABOR_RESOURCE
1278 --,NON_LABOR_RESOURCE_ID
1279 ,EXPENDITURE_CATEGORY
1280 --,EXPENDITURE_CATEGORY_ID
1281 ,REVENUE_CATEGORY
1282 ,NON_LABOR_RESOURCE_ORG_ID
1283 ,EVENT_TYPE_CLASSIFICATION
1284 ,SYSTEM_LINKAGE_FUNCTION
1285 ,PROJECT_ROLE_ID
1286 ,RESOURCE_CLASS_CODE
1287 ,MFC_COST_TYPE_ID
1288 ,RESOURCE_CLASS_FLAG
1289 ,FC_RES_TYPE_CODE
1290 ,INVENTORY_ITEM_ID
1291 ,ITEM_CATEGORY_ID
1292 ,PERSON_TYPE_CODE
1293 ,BOM_RESOURCE_ID
1294 ,BOM_LABOR_RESOURCE_ID
1295 ,BOM_EQUIP_RESOURCE_ID
1296 ,NAMED_ROLE
1297 --,NAMED_ROLE_ID
1298 ,INCURRED_BY_RES_FLAG
1299 ,TXN_RATE_BASED_FLAG
1300 ,TXN_TASK_ID
1301 ,TXN_WBS_ELEMENT_VERSION_ID
1302 ,TXN_RBS_ELEMENT_ID
1303 ,TXN_PLANNING_START_DATE
1304 ,TXN_PLANNING_END_DATE
1305 ,TXN_PROJECT_ID
1306 ,TXN_BUDGET_VERSION_ID
1307 ,cbs_element_id --bug#16827157
1308 )
1309 SELECT g_TXN_SOURCE_ID_systab(i)
1310 ,g_TXN_SOURCE_TYPE_CODE_systab(i)
1311 ,g_PERSON_ID_systab(i)
1312 ,g_JOB_ID_systab(i)
1313 ,g_ORGANIZATION_ID_systab(i)
1314 ,g_VENDOR_ID_systab(i)
1315 ,g_EXPENDITURE_TYPE_systab(i)
1316 --,g_EXPENDITURE_TYPE_ID_systab(i)
1317 ,g_EVENT_TYPE_systab(i)
1318 --,g_EVENT_TYPE_ID_systab(i)
1319 ,g_NON_LABOR_RESOURCE_systab(i)
1320 --,g_NON_LABOR_RESOURCE_ID_systab(i)
1321 ,g_EXPENDITURE_CATEGORY_systab(i)
1322 --,g_EXP_CATEGORY_ID_systab(i)
1323 ,g_REVENUE_CATEGORY_CODE_systab(i)
1324 ,g_NLR_ORGANIZATION_ID_systab(i)
1325 ,g_EVENT_CLASSIFICATION_systab(i)
1326 ,g_SYS_LINK_FUNCTION_systab(i)
1327 ,g_PROJECT_ROLE_ID_systab(i)
1328 ,g_RESOURCE_CLASS_CODE_systab(i)
1329 ,g_MFC_COST_TYPE_ID_systab(i)
1330 ,g_RESOURCE_CLASS_FLAG_systab(i)
1331 ,g_FC_RES_TYPE_CODE_systab(i)
1332 ,g_INVENTORY_ITEM_ID_systab(i)
1333 ,g_ITEM_CATEGORY_ID_systab(i)
1334 ,g_PERSON_TYPE_CODE_systab(i)
1335 ,g_BOM_RESOURCE_ID_systab(i)
1336 ,decode(g_BOM_RESOURCE_ID_systab(i),NULL,NULL,
1337 decode(g_RESOURCE_CLASS_CODE_systab(i),'PEOPLE',g_BOM_RESOURCE_ID_systab(i),NULL))
1338 ,decode(g_BOM_RESOURCE_ID_systab(i),NULL,NULL,
1339 decode(g_RESOURCE_CLASS_CODE_systab(i),'EQUIPMENT',g_BOM_RESOURCE_ID_systab(i),NULL))
1340 ,g_NAMED_ROLE_systab(i)
1341 --,g_NAMED_ROLE_ID_systab(i)
1342 ,g_INCURRED_BY_RES_FLAG_systab(i)
1343 ,g_RATE_BASED_FLAG_systab(i)
1344 ,g_TXN_TASK_ID_systab(i)
1345 ,g_TXN_WBS_ELE_VER_ID_systab(i)
1346 ,g_TXN_RBS_ELEMENT_ID_systab(i)
1347 ,g_TXN_PLAN_START_DATE_systab(i)
1348 ,g_TXN_PLAN_END_DATE_systab (i)
1349 ,g_PROJECT_ID
1350 ,g_BUDGET_VERSION_ID
1351 ,g_CBS_ELEMENT_ID_systab(i) --bug#16827157
1352 FROM DUAL;
1353
1354 END IF;--IF l_uncategorized_flag = 'Y' THEN
1355 l_NumRecInserted := sql%Rowcount;
1356 g_res_numRecInserted := l_NumRecInserted;
1357 l_stage := 'Num of Records inserted ['||g_res_numRecInserted||']';
1358 print_msg(g_debug_flag,l_stage);
1359
1360 End If;-- If g_TXN_SOURCE_ID_systab.COUNT > 0 Then
1361
1362 End If;--If p_calling_mode =
1363
1364 --Some of the attributes that are required for resource list mapping are derived below. If the
1365 --target is an uncategorized resource list then the resource list mapping would not be called and all the
1366 --source resource list members would be mapped to financial class resource list member in the target. Hence
1367 --the below code need not be executed for uncategorzied resource lists.
1368 IF l_NumRecInserted > 0 AND
1369 l_uncategorized_flag = 'N' THEN
1370
1371 /* update the resource class id for the inseted rows*/
1372 --FORALL i IN g_txn_id_sqltab.FIRST .. g_txn_id_sqltab.LAST
1373 UPDATE pa_res_list_map_tmp1 tmp
1374 SET tmp.resource_class_id = (select rc.resource_class_id
1375 from pa_resource_classes_b rc
1376 where rc.resource_class_code = tmp.resource_class_code)
1377 WHERE tmp.resource_class_code is NOT NULL
1378 ;
1379
1380 /* Bug fix: 3698579 */
1381 -- update exp category id if null
1382 UPDATE pa_res_list_map_tmp1 tmp
1383 SET tmp.expenditure_category = (select etc.expenditure_category
1384 from pa_expenditure_types et
1385 ,pa_expenditure_categories etc
1386 where et.expenditure_type = tmp.expenditure_type
1387 and et.expenditure_category = etc.expenditure_category
1388 and rownum = 1
1389 )
1390 WHERE tmp.expenditure_category is NULL
1391 AND tmp.expenditure_type is NOT NULL ;
1392
1393 -- update revenue category based on event type if its null
1394 UPDATE pa_res_list_map_tmp1 tmp
1395 SET tmp.revenue_category = (SELECT evt.revenue_category_code
1396 FROM pa_event_types evt
1397 WHERE evt.event_type=tmp.event_type)
1398 WHERE tmp.revenue_category IS NULL
1399 AND tmp.event_type IS NOT NULL;
1400
1401 -- update revenue category based on exp type if its null
1402 UPDATE pa_res_list_map_tmp1 tmp
1403 SET tmp.Revenue_category = (select et.Revenue_category_code
1404 from pa_expenditure_types et
1405 where et.expenditure_type = tmp.expenditure_type
1406 and rownum = 1
1407 )
1408 WHERE tmp.Revenue_category is NULL
1409 AND tmp.expenditure_type is NOT NULL ;
1410
1411 -- update default item category id if the resource is a inventory item
1412 UPDATE pa_res_list_map_tmp1 tmp
1413 SET tmp.item_category_id = ( SELECT cat.CATEGORY_ID
1414 FROM PA_RESOURCE_CLASSES_B classes
1415 ,PA_PLAN_RES_DEFAULTS cls
1416 ,MTL_ITEM_CATEGORIES cat
1417 WHERE classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS'
1418 AND cls.RESOURCE_CLASS_ID = classes.RESOURCE_CLASS_ID
1419 AND cls.ITEM_CATEGORY_SET_ID = cat.CATEGORY_SET_ID
1420 AND cat.ORGANIZATION_ID = tmp.organization_id
1421 AND cat.INVENTORY_ITEM_ID = tmp.inventory_item_id
1422 AND rownum = 1
1423 )
1424 WHERE tmp.item_category_id is NULL
1425 AND tmp.inventory_item_id is NOT NULL;
1426
1427 /* bug fix:3843815 ,3841480 if p_budget_version_id is not passed in plsql_table mode then
1428 * the sql to derive l_struct_ver_id causes no data found . so added if condition and
1429 * moved the select to cursor */
1430 IF l_struct_ver_id is NOT NULL Then
1431 UPDATE pa_res_list_map_tmp1 tmp
1432 SET tmp.TXN_WBS_ELEMENT_VERSION_ID = (Select pelm.element_version_id
1433 From pa_proj_element_versions pelm
1434 WHERE pelm.parent_structure_version_id = l_struct_ver_id
1435 AND pelm.proj_element_id = tmp.txn_task_id
1436 AND rownum = 1
1437 )
1438 WHERE tmp.TXN_WBS_ELEMENT_VERSION_ID is NULL
1439 AND tmp.txn_task_id is NOT NULL;
1440 End If;
1441 /* End of bug fix:3698579 */
1442
1443 /* added this update for bug fix:3854817 */
1444 UPDATE pa_res_list_map_tmp1 tmp
1445 SET tmp.fc_res_type_code = DECODE(tmp.EXPENDITURE_TYPE,null
1446 ,DECODE(tmp.EVENT_TYPE,null
1447 ,DECODE(tmp.EXPENDITURE_CATEGORY,null
1448 ,DECODE(tmp.REVENUE_CATEGORY,null,NULL,'REVENUE_CATEGORY')
1449 ,'EXPENDITURE_CATEGORY')
1450 ,'EVENT_TYPE')
1451 ,'EXPENDITURE_TYPE')
1452 WHERE tmp.fc_res_type_code is NULL;
1453
1454
1455 End If;
1456 l_stage := 'End of populate_resmap_tmp api';
1457 print_msg(g_debug_flag,l_stage);
1458
1459 EXCEPTION
1460 WHEN OTHERS THEN
1461 print_msg(g_debug_flag,l_stage||':'||SQLCODE||SQLERRM);
1462 RAISE;
1463 END populate_resmap_tmp;
1464
1465 /* This API reads the output records from Resource and RBS mapping tmp tables and
1466 * populates the output plsql and system tables
1467 */
1468 PROCEDURE populate_resrbsmap_outTbls
1469 (p_process_code IN Varchar2
1470 ,p_calling_mode IN Varchar2
1471 ,p_resource_list_id IN Number
1472 ,p_budget_version_id IN Number
1473 ,x_return_status OUT NOCOPY varchar2
1474 ) IS
1475
1476 CURSOR cur_resmapRejections IS
1477 SELECT rsmap.txn_source_id
1478 ,rsmap.resource_list_member_id
1479 ,null -- rsmap.res_map_rejection_code
1480 ,rsmap.txn_source_id
1481 ,rsmap.resource_list_member_id
1482 ,null --rsmap.res_map_rejection_code
1483 ,null
1484 ,null
1485 ,null
1486 ,null
1487 ,null
1488 ,null
1489 FROM pa_res_list_map_tmp4 rsmap;
1490
1491 CURSOR cur_rbsmapRejections IS
1492 SELECT rsmap.source_id
1493 ,rsmap.rbs_element_id
1494 ,rsmap.txn_accum_header_id
1495 ,null -- rsmap.rbs_map_rejection_code
1496 ,rsmap.source_id
1497 ,rsmap.rbs_element_id
1498 ,rsmap.txn_accum_header_id
1499 ,null -- rsmap.rbs_map_rejection_code
1500 ,null
1501 ,null
1502 ,null
1503 ,null
1504 FROM pa_rbs_plans_out_tmp rsmap;
1505
1506 CURSOR cur_resrbsmapRejections IS
1507 SELECT resmap.txn_source_id
1508 ,resmap.resource_list_member_id
1509 ,null -- resmap.res_map_rejection_code
1510 ,resmap.txn_source_id
1511 ,resmap.resource_list_member_id
1512 ,null --resmap.res_map_rejection_code
1513 ,rbsmap.rbs_element_id
1514 ,rbsmap.txn_accum_header_id
1515 ,null -- rbsmap.rbs_map_rejection_code
1516 ,rbsmap.rbs_element_id
1517 ,rbsmap.txn_accum_header_id
1518 ,null -- rbsmap.rbs_map_rejection_code
1519 FROM pa_res_list_map_tmp4 resmap
1520 ,pa_rbs_plans_out_tmp rbsmap
1521 WHERE resmap.txn_source_id = rbsmap.source_id ;
1522
1523 l_stage varchar2(1000);
1524
1525 l_count Number := 0;
1526 BEGIN
1527
1528 /* Initiazlize the global plsql tables Out variables */
1529 l_stage := 'Start of populate_resrbsmap_outTbls Initializing the OUT global plsql tables';
1530 print_msg(g_debug_flag,l_stage);
1531 x_return_status := 'S';
1532 g_res_map_reject_code_sqltab.delete;
1533 g_res_map_reject_code_systab := system.pa_varchar2_30_tbl_type ();
1534 g_res_list_member_id_sqltab.delete;
1535 g_res_list_member_id_systab := system.pa_num_tbl_type();
1536 g_txn_source_id_sqltab.delete;
1537 g_txn_source_id_systab := system.pa_num_tbl_type();
1538 g_rbs_map_reject_code_sqltab.delete;
1539 g_rbs_map_reject_code_systab := system.PA_VARCHAR2_30_TBL_TYPE();
1540 g_rbs_element_id_sqltab.delete;
1541 g_rbs_element_id_systab := system.PA_NUM_TBL_TYPE();
1542 g_txn_accum_header_id_sqltab.delete;
1543 g_txn_accum_header_id_systab := system.PA_NUM_TBL_TYPE();
1544
1545 If p_process_code = 'RES_MAP' Then
1546 If NVL(g_debug_flag,'N') = 'Y' Then /* Bug No. 4419245 */
1547 select count(*)
1548 into l_count
1549 from pa_res_list_map_tmp4;
1550 print_msg(g_debug_flag,'For Debug purpose counting Number of records from mapping temp table is ['||l_count||']');
1551 end if; /* Bug No. 4419245 */
1552 l_stage := 'Opening Resource Mapping rejection cursor';
1553 print_msg(g_debug_flag,l_stage);
1554 OPEN cur_resmapRejections;
1555 FETCH cur_resmapRejections BULK COLLECT INTO
1556 g_txn_source_id_sqltab
1557 ,g_res_list_member_id_sqltab
1558 ,g_res_map_reject_code_sqltab
1559 ,g_txn_source_id_systab
1560 ,g_res_list_member_id_systab
1561 ,g_res_map_reject_code_systab
1562 ,g_rbs_element_id_sqltab
1563 ,g_txn_accum_header_id_sqltab
1564 ,g_rbs_map_reject_code_sqltab
1565 ,g_rbs_element_id_systab
1566 ,g_txn_accum_header_id_systab
1567 ,g_rbs_map_reject_code_systab;
1568 CLOSE cur_resmapRejections;
1569 l_stage := 'Num of records fetched into global tables ['||g_txn_source_id_sqltab.count||']';
1570 print_msg(g_debug_flag,l_stage);
1571 Elsif p_process_code = 'RBS_MAP' Then
1572 If NVL(g_debug_flag,'N') = 'Y' Then /* Bug No. 4419245 */
1573 select count(*)
1574 into l_count
1575 from pa_rbs_plans_out_tmp;
1576 print_msg(g_debug_flag,'For Debug purpose counting Number of records from mapping temp table is ['||l_count||']');
1577 /* just for debug purpose priting the values of all the reocrds*/
1578 FOR i IN ( select * from pa_rbs_plans_out_tmp ) LOOP
1579 print_msg(g_debug_flag,'Value from rbs outtmp SourceId['||i.source_id||']RbsEleId['||i.rbs_element_id||']TxnAccum['||i.txn_accum_header_id||']');
1580 END LOOP;
1581 End if; /* Bug No. 4419245 */
1582 l_stage := 'Opening RBS map rejections cursor';
1583 print_msg(g_debug_flag,l_stage);
1584 OPEN cur_rbsmapRejections ;
1585 FETCH cur_rbsmapRejections BULK COLLECT INTO
1586 g_txn_source_id_sqltab
1587 ,g_rbs_element_id_sqltab
1588 ,g_txn_accum_header_id_sqltab
1589 ,g_rbs_map_reject_code_sqltab
1590 ,g_txn_source_id_systab
1591 ,g_rbs_element_id_systab
1592 ,g_txn_accum_header_id_systab
1593 ,g_rbs_map_reject_code_systab
1594 ,g_res_list_member_id_sqltab
1595 ,g_res_map_reject_code_sqltab
1596 ,g_res_list_member_id_systab
1597 ,g_res_map_reject_code_systab;
1598 CLOSE cur_rbsmapRejections;
1599 l_stage := 'Num of records fetched into global tables ['||g_txn_source_id_sqltab.count||']';
1600 print_msg(g_debug_flag,l_stage);
1601 Elsif p_process_code = 'RES_RBS_MAP' Then
1602 If NVL(g_debug_flag,'N') = 'Y' Then /* Bug No. 4419245 */
1603 select count(*)
1604 into l_count
1605 from pa_res_list_map_tmp4;
1606 print_msg(g_debug_flag,'For Debug purpose counting Number of records from RESmapping temp table is ['||l_count||']');
1607 select count(*)
1608 into l_count
1609 from pa_rbs_plans_out_tmp;
1610 print_msg(g_debug_flag,'For Debug purpose counting Number of records from RBSmapping temp table is ['||l_count||']');
1611 End if; /* Bug No. 4419245 */
1612 l_stage := 'Opening ResRBS map rejections cursor';
1613 print_msg(g_debug_flag,l_stage);
1614 OPEN cur_resrbsmapRejections;
1615 FETCH cur_resrbsmapRejections BULK COLLECT INTO
1616 g_txn_source_id_sqltab
1617 ,g_res_list_member_id_sqltab
1618 ,g_res_map_reject_code_sqltab
1619 ,g_txn_source_id_systab
1620 ,g_res_list_member_id_systab
1621 ,g_res_map_reject_code_systab
1622 ,g_rbs_element_id_sqltab
1623 ,g_txn_accum_header_id_sqltab
1624 ,g_rbs_map_reject_code_sqltab
1625 ,g_rbs_element_id_systab
1626 ,g_txn_accum_header_id_systab
1627 ,g_rbs_map_reject_code_systab ;
1628 CLOSE cur_resrbsmapRejections;
1629 l_stage := 'Num of records fetched into global tables ['||g_txn_source_id_sqltab.count||']';
1630 print_msg(g_debug_flag,l_stage);
1631 End If;
1632 l_stage := 'End of populate_resrbsmap_outTbl ';
1633 print_msg(g_debug_flag,l_stage);
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 print_msg(g_debug_flag,l_stage||':'||SQLCODE||SQLERRM);
1637 x_return_status := 'U';
1638 RAISE;
1639
1640 END populate_resrbsmap_outTbls;
1641
1642 /* This API derives the Resource list member id and RBS element Id for the
1643 * given resource list Id / RBS version Id. This procedure calls resource mapping and rbs mapping API
1644 * depending the parameter p_process_code
1645 * If p_process_code = 'RES_MAP' then RLMI will be derived by calling resource mapping api
1646 * If p_process_code = 'RBS_MAP' then RBS element Id will be derived by caling RBS mapping api
1647 * The following are the possible values for these IN params
1648 * p_calling_process IN varchar2
1649 * values 'BUDGET_GENERATION' , 'RBS_REFRESH' , 'COPY_PROJECT'
1650 * p_process_code IN varchar2
1651 * values 'RES_MAP', 'RBS_MAP'
1652 * p_calling_context IN varchar2
1653 * values 'PLSQL' , 'SELF_SERVICE'
1654 * p_calling_mode IN varchar2
1655 * values 'PLSQL_TABLE', 'BUDGET_VERSION'
1656 *
1657 * NOTES
1658 * 1.p_txn_source_id_tab must be populated with UNIQUE value
1659 * 2.If the p_calling_mode is 'BUDGET_VERSION' then values passed in plsql and system table params
1660 * will be ignored
1661 * 3.If the p_calling_context is 'SELF_SERVICE' then debug msg will write to PA_DEBUG.WRITE_LOG();
1662 * 4.If the p_calling_context is 'PLSQL' then debug msg will write to PA_DEBUG.WRITE_FILE();
1663 */
1664 PROCEDURE Map_Rlmi_Rbs
1665 ( p_budget_version_id IN Number
1666 ,p_project_id IN Number Default NULL
1667 ,p_resource_list_id IN Number Default NULL
1668 ,p_rbs_version_id IN Number Default NULL
1669 ,p_calling_process IN Varchar2
1670 ,p_calling_context IN varchar2 Default 'PLSQL'
1671 ,p_process_code IN varchar2 Default 'RES_MAP'
1672 ,p_calling_mode IN Varchar2 Default 'PLSQL_TABLE'
1673 ,p_init_msg_list_flag IN Varchar2 Default 'Y'
1674 ,p_commit_flag IN Varchar2 Default 'N'
1675 ,p_TXN_SOURCE_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1676 ,p_TXN_SOURCE_TYPE_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1677 ,p_PERSON_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1678 ,p_JOB_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1679 ,p_ORGANIZATION_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1680 ,p_VENDOR_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1681 ,p_EXPENDITURE_TYPE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1682 ,p_EVENT_TYPE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1683 ,p_NON_LABOR_RESOURCE_tab IN PA_PLSQL_DATATYPES.Char20TabTyp Default PA_PLSQL_DATATYPES.EmptyChar20Tab
1684 ,p_EXPENDITURE_CATEGORY_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1685 ,p_REVENUE_CATEGORY_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1686 ,p_NLR_ORGANIZATION_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1687 ,p_EVENT_CLASSIFICATION_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1688 ,p_SYS_LINK_FUNCTION_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1689 ,p_PROJECT_ROLE_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1690 ,p_RESOURCE_CLASS_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1691 ,p_MFC_COST_TYPE_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1692 ,p_RESOURCE_CLASS_FLAG_tab IN PA_PLSQL_DATATYPES.Char1TabTyp Default PA_PLSQL_DATATYPES.EmptyChar1Tab
1693 ,p_FC_RES_TYPE_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1694 ,p_INVENTORY_ITEM_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1695 ,p_ITEM_CATEGORY_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1696 ,p_PERSON_TYPE_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
1697 ,p_BOM_RESOURCE_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1698 ,p_NAMED_ROLE_tab IN PA_PLSQL_DATATYPES.Char80TabTyp Default PA_PLSQL_DATATYPES.EmptyChar80Tab
1699 ,p_INCURRED_BY_RES_FLAG_tab IN PA_PLSQL_DATATYPES.Char1TabTyp Default PA_PLSQL_DATATYPES.EmptyChar1Tab
1700 ,p_RATE_BASED_FLAG_tab IN PA_PLSQL_DATATYPES.Char1TabTyp Default PA_PLSQL_DATATYPES.EmptyChar1Tab
1701 ,p_TXN_TASK_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1702 ,p_TXN_WBS_ELEMENT_VER_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1703 ,p_TXN_RBS_ELEMENT_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
1704 ,P_CBS_ELEMENT_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab --bug#16827157
1705 ,p_TXN_PLAN_START_DATE_tab IN PA_PLSQL_DATATYPES.DateTabTyp Default PA_PLSQL_DATATYPES.EmptyDateTab
1706 ,p_TXN_PLAN_END_DATE_tab IN PA_PLSQL_DATATYPES.DateTabTyp Default PA_PLSQL_DATATYPES.EmptyDateTab
1707 ,x_txn_source_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
1708 ,x_res_list_member_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
1709 ,x_rbs_element_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
1710 ,x_txn_accum_header_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
1711 ,x_return_status OUT NOCOPY Varchar2
1712 ,x_msg_count OUT NOCOPY Number
1713 ,x_msg_data OUT NOCOPY Varchar2
1714 ) IS
1715 l_resource_list_id Number;
1716 l_rbs_version_id Number;
1717 l_calling_mode Varchar2(100);
1718 l_return_status varchar2(10) := 'S';
1719 l_msg_count Number := 0;
1720 l_msg_data Varchar2(1000);
1721 l_stage Varchar2(1000);
1722 l_tab_count Number := 0;
1723 l_resmap_return_status varchar2(10) := 'S';
1724 l_rbsmap_return_status varchar2(10) := 'S';
1725
1726 BEGIN
1727 /* INitizalize the out variables*/
1728 x_return_status := 'S';
1729 x_msg_data := Null;
1730 x_msg_count := Null;
1731 --x_res_map_reject_code_tab.delete;
1732 --x_rbs_map_reject_code_tab.delete;
1733 g_debug_context := p_calling_context;
1734
1735 --- Initialize the error statck
1736 PA_DEBUG.init_err_stack ('PA_RLMI_RBS_MAP_PUB.Map_Rlmi_Rbs');
1737
1738 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
1739 g_debug_flag := NVL(g_debug_flag, 'N');
1740
1741 /* Bug fix: 4345057 */
1742 If NVL(g_debug_flag,'N') = 'Y' Then
1743 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
1744 ,x_write_file => 'LOG'
1745 ,x_debug_mode => g_debug_flag
1746 );
1747 End If;
1748
1749 l_stage := 'Begin of PLSQL.Map_Rlmi_Rbs IN Params:BdgtVer['||p_budget_version_id||']ReslistId['||p_resource_list_id||
1750 ']RbsVers['||p_rbs_version_id||']CallingProcess['||p_calling_process||']CallContext['||p_calling_context||
1751 ']ProcessCode['||p_process_code||']CallingMode['||p_calling_mode||']InitMsg['||p_init_msg_list_flag||
1752 ']CommitFlag['||p_commit_flag||']ProjectId['||p_project_Id||']' ;
1753 Print_msg(g_debug_flag,l_stage);
1754
1755 /* Initialize the Variables with IN params*/
1756 l_stage := 'Calling Init_ReqdVariables api';
1757 print_msg(g_debug_flag,l_stage);
1758 Init_ReqdVariables(
1759 p_process_code => p_process_code
1760 ,p_project_id => p_project_id
1761 ,p_resource_list_id => p_resource_list_id
1762 ,p_rbs_version_id => p_rbs_version_id
1763 ,p_budget_version_id => p_budget_version_id
1764 );
1765 l_stage := 'GlobalVariables:BdgtVer['||g_budget_version_id||']ProjId['||g_project_id||']ReslistId['||g_resource_list_id||
1766 ']RbsVersion['||g_rbs_version_id||']';
1767 print_msg(g_debug_flag,l_stage);
1768
1769 /* based on the calling mode Assign the INparam to Global varaibles */
1770 IF p_calling_mode = 'PLSQL_TABLE' Then
1771 l_tab_count := p_TXN_SOURCE_ID_Tab.count;
1772 l_stage := 'Begin Assigning In PLSQL tables to Global Plsql Tables plsqlTabCount['||l_tab_count||']';
1773 print_msg(g_debug_flag,l_stage);
1774
1775 g_TXN_SOURCE_ID_sqlTab := p_TXN_SOURCE_ID_Tab;
1776 g_TXN_SOURCE_TYPE_CODE_sqltab := p_TXN_SOURCE_TYPE_CODE_tab;
1777 g_PERSON_ID_sqltab := p_PERSON_ID_tab;
1778 g_JOB_ID_sqltab := p_JOB_ID_tab;
1779 g_ORGANIZATION_ID_sqltab := p_ORGANIZATION_ID_tab;
1780 g_VENDOR_ID_sqltab := p_vendor_id_tab;
1781 g_EXPENDITURE_TYPE_sqltab := p_expenditure_type_tab;
1782 g_EVENT_TYPE_sqltab := p_event_type_tab;
1783 g_NON_LABOR_RESOURCE_sqltab := p_non_labor_resource_tab;
1784 g_EXPENDITURE_CATEGORY_sqltab := p_expenditure_category_tab;
1785 g_REVENUE_CATEGORY_CODE_sqltab := p_revenue_category_code_tab;
1786 g_NLR_ORGANIZATION_ID_sqltab := p_NLR_organization_id_tab;
1787 g_EVENT_CLASSIFICATION_sqltab := p_event_classification_tab;
1788 g_SYS_LINK_FUNCTION_sqltab := p_sys_link_function_tab;
1789 g_PROJECT_ROLE_ID_sqltab := p_project_role_id_tab;
1790 g_RESOURCE_CLASS_CODE_sqltab := p_resource_class_code_tab;
1791 g_MFC_COST_TYPE_ID_sqltab := p_mfc_cost_type_id_tab;
1792 g_RESOURCE_CLASS_FLAG_sqltab := p_resource_class_flag_tab;
1793 g_FC_RES_TYPE_CODE_sqltab := p_fc_res_type_code_tab;
1794 g_INVENTORY_ITEM_ID_sqltab := p_inventory_item_id_tab;
1795 g_ITEM_CATEGORY_ID_sqltab := p_item_category_id_tab;
1796 g_PERSON_TYPE_CODE_sqltab := p_person_type_code_tab;
1797 g_BOM_RESOURCE_ID_sqltab := p_bom_resource_id_tab;
1798 g_NAMED_ROLE_sqltab := p_named_role_tab;
1799 g_INCURRED_BY_RES_FLAG_sqltab := p_incurred_by_res_flag_tab;
1800 g_RATE_BASED_FLAG_sqltab := p_rate_based_flag_tab;
1801 g_TXN_TASK_ID_sqltab := p_txn_task_id_tab;
1802 g_TXN_WBS_ELE_VER_ID_sqltab := p_txn_wbs_element_ver_id_tab;
1803 g_TXN_RBS_ELEMENT_ID_sqltab := p_txn_rbs_element_id_tab;
1804 g_CBS_ELEMENT_ID_sqltab := P_CBS_ELEMENT_ID_TAB; --bug#16827157
1805 g_TXN_PLAN_START_DATE_sqltab := p_txn_plan_start_date_tab;
1806 g_TXN_PLAN_END_DATE_sqltab := p_txn_plan_end_date_tab;
1807 l_stage := 'End of Assigning plsql tables to Global Plsql Tables';
1808 print_msg(g_debug_flag,l_stage);
1809 /* End of Assigning plsql tables */
1810 IF l_tab_count = 0 THEN
1811 --No records to process. Return
1812 l_stage := 'The Source Id tab is Null. No record to process Return';
1813 print_msg(g_debug_flag,l_stage);
1814 PA_DEBUG.reset_err_stack;
1815 RETURN;
1816 ELSIF l_tab_count > 0 Then
1817 l_stage := ' Loop through plsql tables and check any of the index not exists';
1818 print_msg(g_debug_flag,l_stage);
1819 FOR i IN g_TXN_SOURCE_ID_sqlTab.FIRST .. g_TXN_SOURCE_ID_sqlTab.LAST LOOP
1820 If NOT g_TXN_SOURCE_TYPE_CODE_sqltab.EXISTS(i) Then
1821 g_TXN_SOURCE_TYPE_CODE_sqltab(i) := null;
1822 Else
1823 IF g_TXN_SOURCE_TYPE_CODE_sqltab(i) = fnd_api.g_miss_char Then
1824 g_TXN_SOURCE_TYPE_CODE_sqltab(i) := null;
1825 End If;
1826 End If;
1827 If NOT g_PERSON_ID_sqltab.EXISTS(i) Then
1828 g_PERSON_ID_sqltab(i) := null;
1829 Else
1830 IF g_PERSON_ID_sqltab(i) = fnd_api.g_miss_num Then
1831 g_PERSON_ID_sqltab(i) := null;
1832 End If;
1833 End If;
1834 If NOT g_JOB_ID_sqltab.EXISTS(i) Then
1835 g_JOB_ID_sqltab(i) := null;
1836 Else
1837 IF g_JOB_ID_sqltab(i) = fnd_api.g_miss_num Then
1838 g_JOB_ID_sqltab(i) := null;
1839 End If;
1840 End If;
1841 If NOT g_ORGANIZATION_ID_sqltab.EXISTS(i) Then
1842 g_ORGANIZATION_ID_sqltab(i) := null;
1843 Else
1844 If g_ORGANIZATION_ID_sqltab(i) = fnd_api.g_miss_num Then
1845 g_ORGANIZATION_ID_sqltab(i) := null;
1846 End If;
1847 End If;
1848 If NOT g_VENDOR_ID_sqltab.EXISTS(i) Then
1849 g_VENDOR_ID_sqltab(i) := null;
1850 Else
1851 IF g_VENDOR_ID_sqltab(i) = fnd_api.g_miss_num Then
1852 g_VENDOR_ID_sqltab(i) := null;
1853 End If;
1854 End If;
1855 If NOT g_EXPENDITURE_TYPE_sqltab.EXISTS(i) Then
1856 g_EXPENDITURE_TYPE_sqltab(i) := null;
1857 Else
1858 If g_EXPENDITURE_TYPE_sqltab(i) = fnd_api.g_miss_char Then
1859 g_EXPENDITURE_TYPE_sqltab(i) := null;
1860 End If;
1861 End If;
1862 If NOT g_EVENT_TYPE_sqltab.EXISTS(i) Then
1863 g_EVENT_TYPE_sqltab(i) := null;
1864 Else
1865 If g_EVENT_TYPE_sqltab(i) = fnd_api.g_miss_char Then
1866 g_EVENT_TYPE_sqltab(i) := null;
1867 End If;
1868 End If;
1869 If NOT g_NON_LABOR_RESOURCE_sqltab.EXISTS(i) Then
1870 g_NON_LABOR_RESOURCE_sqltab(i) := null;
1871 Else
1872 IF g_NON_LABOR_RESOURCE_sqltab(i) = fnd_api.g_miss_char Then
1873 g_NON_LABOR_RESOURCE_sqltab(i) := null;
1874 End If;
1875 End If;
1876 If NOT g_EXPENDITURE_CATEGORY_sqltab.EXISTS(i) Then
1877 g_EXPENDITURE_CATEGORY_sqltab(i) := null;
1878 Else
1879 IF g_EXPENDITURE_CATEGORY_sqltab(i) = fnd_api.g_miss_char Then
1880 g_EXPENDITURE_CATEGORY_sqltab(i) := null;
1881 End If;
1882 End If;
1883 If NOT g_REVENUE_CATEGORY_CODE_sqltab.EXISTS(i) Then
1884 g_REVENUE_CATEGORY_CODE_sqltab(i) := null;
1885 Else
1886 IF g_REVENUE_CATEGORY_CODE_sqltab(i) = fnd_api.g_miss_char Then
1887 g_REVENUE_CATEGORY_CODE_sqltab(i) := null;
1888 End If;
1889 End If;
1890 If NOT g_NLR_ORGANIZATION_ID_sqltab.EXISTS(i) Then
1891 g_NLR_ORGANIZATION_ID_sqltab(i) := null;
1892 Else
1893 If g_NLR_ORGANIZATION_ID_sqltab(i) = fnd_api.g_miss_num Then
1894 g_NLR_ORGANIZATION_ID_sqltab(i) := null;
1895 End If;
1896 End If;
1897 If NOT g_EVENT_CLASSIFICATION_sqltab.EXISTS(i) Then
1898 g_EVENT_CLASSIFICATION_sqltab(i) := null;
1899 Else
1900 IF g_EVENT_CLASSIFICATION_sqltab(i) = fnd_api.g_miss_char Then
1901 g_EVENT_CLASSIFICATION_sqltab(i) := null;
1902 End If;
1903 End If;
1904 If NOT g_SYS_LINK_FUNCTION_sqltab.EXISTS(i) Then
1905 g_SYS_LINK_FUNCTION_sqltab(i) := null;
1906 Else
1907 IF g_SYS_LINK_FUNCTION_sqltab(i) = fnd_api.g_miss_char Then
1908 g_SYS_LINK_FUNCTION_sqltab(i) := null;
1909 End If;
1910 End If;
1911 If NOT g_PROJECT_ROLE_ID_sqltab.EXISTS(i) Then
1912 g_PROJECT_ROLE_ID_sqltab(i) := null;
1913 Else
1914 IF g_PROJECT_ROLE_ID_sqltab(i) = fnd_api.g_miss_num Then
1915 g_PROJECT_ROLE_ID_sqltab(i) := null;
1916 End If;
1917 End If;
1918 If NOT g_RESOURCE_CLASS_CODE_sqltab.EXISTS(i) Then
1919 g_RESOURCE_CLASS_CODE_sqltab(i) := null;
1920 Else
1921 IF g_RESOURCE_CLASS_CODE_sqltab(i) = fnd_api.g_miss_char Then
1922 g_RESOURCE_CLASS_CODE_sqltab(i) := null;
1923 End If;
1924 End If;
1925 IF NOT g_MFC_COST_TYPE_ID_sqltab.EXISTS(i) Then
1926 g_MFC_COST_TYPE_ID_sqltab(i) := null;
1927 Else
1928 IF g_MFC_COST_TYPE_ID_sqltab(i) = fnd_api.g_miss_num Then
1929 g_MFC_COST_TYPE_ID_sqltab(i) := null;
1930 End If;
1931 End If;
1932 If NOT g_RESOURCE_CLASS_FLAG_sqltab.EXISTS(i) Then
1933 g_RESOURCE_CLASS_FLAG_sqltab(i) := null;
1934 Else
1935 If g_RESOURCE_CLASS_FLAG_sqltab(i) = fnd_api.g_miss_char Then
1936 g_RESOURCE_CLASS_FLAG_sqltab(i) := null;
1937 End If;
1938 End If;
1939 If NOT g_FC_RES_TYPE_CODE_sqltab.EXISTS(i) Then
1940 g_FC_RES_TYPE_CODE_sqltab(i) := null;
1941 Else
1942 IF g_FC_RES_TYPE_CODE_sqltab(i) = fnd_api.g_miss_char Then
1943 g_FC_RES_TYPE_CODE_sqltab(i) := null;
1944 End If;
1945 End If;
1946 If NOT g_INVENTORY_ITEM_ID_sqltab.EXISTS(i) Then
1947 g_INVENTORY_ITEM_ID_sqltab(i) := null;
1948 Else
1949 IF g_INVENTORY_ITEM_ID_sqltab(i) = fnd_api.g_miss_num Then
1950 g_INVENTORY_ITEM_ID_sqltab(i) := null;
1951 End If;
1952 End If;
1953 IF NOT g_ITEM_CATEGORY_ID_sqltab.EXISTS(i) Then
1954 g_ITEM_CATEGORY_ID_sqltab(i) := null;
1955 Else
1956 IF g_ITEM_CATEGORY_ID_sqltab(i) = fnd_api.g_miss_num Then
1957 g_ITEM_CATEGORY_ID_sqltab(i) := null;
1958 End If;
1959 End If;
1960 IF NOT g_PERSON_TYPE_CODE_sqltab.EXISTS(i) Then
1961 g_PERSON_TYPE_CODE_sqltab(i) := null;
1962 Else
1963 IF g_PERSON_TYPE_CODE_sqltab(i) = fnd_api.g_miss_char Then
1964 g_PERSON_TYPE_CODE_sqltab(i) := null;
1965 End if;
1966 End If;
1967 IF NOT g_BOM_RESOURCE_ID_sqltab.EXISTS(i) Then
1968 g_BOM_RESOURCE_ID_sqltab(i) := null;
1969 Else
1970 IF g_BOM_RESOURCE_ID_sqltab(i) = fnd_api.g_miss_num Then
1971 g_BOM_RESOURCE_ID_sqltab(i) := null;
1972 End IF;
1973 End IF;
1974 IF NOT g_NAMED_ROLE_sqltab.EXISTS(i) Then
1975 g_NAMED_ROLE_sqltab(i) := null;
1976 Else
1977 IF g_NAMED_ROLE_sqltab(i) = fnd_api.g_miss_char Then
1978 g_NAMED_ROLE_sqltab(i) := null;
1979 End IF;
1980 End If;
1981 IF NOT g_INCURRED_BY_RES_FLAG_sqltab.EXISTS(i) Then
1982 g_INCURRED_BY_RES_FLAG_sqltab(i) := null;
1983 Else
1984 IF g_INCURRED_BY_RES_FLAG_sqltab(i) = fnd_api.g_miss_char Then
1985 g_INCURRED_BY_RES_FLAG_sqltab(i) := null;
1986 End IF;
1987 End If;
1988 IF NOT g_RATE_BASED_FLAG_sqltab.EXISTS(i) Then
1989 g_RATE_BASED_FLAG_sqltab(i) := null;
1990 Else
1991 IF g_RATE_BASED_FLAG_sqltab(i) = fnd_api.g_miss_char Then
1992 g_RATE_BASED_FLAG_sqltab(i) := null;
1993 End IF;
1994 End IF;
1995 If NOT g_TXN_TASK_ID_sqltab.EXISTS(i) Then
1996 g_TXN_TASK_ID_sqltab(i) := null;
1997 Else
1998 IF g_TXN_TASK_ID_sqltab(i) = fnd_api.g_miss_num Then
1999 g_TXN_TASK_ID_sqltab(i) := null;
2000 End IF;
2001 end if;
2002 If NOT g_TXN_WBS_ELE_VER_ID_sqltab.EXISTS(i) Then
2003 g_TXN_WBS_ELE_VER_ID_sqltab(i) := null;
2004 Else
2005 IF g_TXN_WBS_ELE_VER_ID_sqltab(i) = fnd_api.g_miss_num Then
2006 g_TXN_WBS_ELE_VER_ID_sqltab(i) := null;
2007 End IF;
2008 End IF;
2009 IF NOT g_TXN_RBS_ELEMENT_ID_sqltab.EXISTS(i) Then
2010 g_TXN_RBS_ELEMENT_ID_sqltab(i) := null;
2011 Else
2012 IF g_TXN_RBS_ELEMENT_ID_sqltab(i) = fnd_api.g_miss_num Then
2013 g_TXN_RBS_ELEMENT_ID_sqltab(i) := null;
2014 End IF;
2015 End IF;
2016
2017 /* bug#16827157 */
2018 IF NOT g_CBS_ELEMENT_ID_sqltab.EXISTS(i) Then
2019 g_CBS_ELEMENT_ID_sqltab(i) := null;
2020 Else
2021
2022 IF g_CBS_ELEMENT_ID_sqltab(i) = fnd_api.g_miss_num Then
2023 g_CBS_ELEMENT_ID_sqltab(i) := null;
2024 End IF;
2025 End IF;
2026
2027
2028
2029 If NOT g_TXN_PLAN_START_DATE_sqltab.EXISTS(i) Then
2030 g_TXN_PLAN_START_DATE_sqltab(i) := null;
2031 Else
2032 IF g_TXN_PLAN_START_DATE_sqltab(i) = fnd_api.g_miss_date Then
2033 g_TXN_PLAN_START_DATE_sqltab(i) := null;
2034 End IF;
2035 End IF;
2036 If NOT g_TXN_PLAN_END_DATE_sqltab.EXISTS(i) Then
2037 g_TXN_PLAN_END_DATE_sqltab(i) := null;
2038 Else
2039 IF g_TXN_PLAN_END_DATE_sqltab(i) = fnd_api.g_miss_date Then
2040 g_TXN_PLAN_END_DATE_sqltab(i) := null;
2041 End If;
2042 End If;
2043 END LOOP;
2044 l_stage := ' End of Loop plsql tables and check any of the index not exists';
2045 print_msg(g_debug_flag,l_stage);
2046 END If;
2047 End If;
2048
2049 IF p_calling_mode = 'BUDGET_VERSION' Then
2050 l_calling_mode := 'BUDGET_VERSION';
2051 Elsif p_calling_mode = 'PLSQL_TABLE' Then
2052 l_calling_mode := 'PLSQL_TABLE';
2053 End IF;
2054
2055 /* populate the resource mapping temp tables */
2056 If p_process_code in ('RES_MAP','RES_RBS_MAP') Then
2057 If g_resource_list_id is NOT NULL Then
2058 l_stage := 'Calling Populate populate_resmap_tmp api for Resource List ';
2059 print_msg(g_debug_flag,l_stage);
2060 populate_resmap_tmp
2061 (p_budget_version_id => g_budget_version_id
2062 ,p_calling_mode => l_calling_mode
2063 ,x_return_status => l_return_status
2064 );
2065 l_stage := 'End of Populate populate_resmap_tmp api NumOfRecsInserted['||g_res_numRecInserted||']';
2066 print_msg(g_debug_flag,l_stage);
2067
2068 /* call resource mapping api */
2069 l_resmap_return_status := 'S';
2070 If g_res_numRecInserted > 0
2071 AND g_call_res_list_mapping_api ='Y' Then
2072 l_stage := 'Calling pa_resource_mapping.map_resource_list api';
2073 print_msg(g_debug_flag,l_stage);
2074
2075 pa_resource_mapping.map_resource_list
2076 (p_resource_list_id => g_resource_list_id
2077 ,p_project_id => g_project_id
2078 ,x_return_status => l_resmap_return_status
2079 ,x_msg_count => l_msg_count
2080 ,x_msg_data => l_msg_data
2081 );
2082
2083 l_stage := 'End of pa_resource_mapping.map_resource_list api retSts['||l_resmap_return_status||']';
2084 print_msg(g_debug_flag,l_stage);
2085 End If;
2086 End If;
2087 End If;
2088
2089 /* populate the rbs mapping temp tables */
2090 If p_process_code in ('RBS_MAP','RES_RBS_MAP') Then
2091 If g_rbs_version_id is NOT NULL Then
2092 l_stage := 'Calling populate_rbsmap_tmp api for RBS ';
2093 print_msg(g_debug_flag,l_stage);
2094 populate_rbsmap_tmp
2095 (p_budget_version_id => g_budget_version_id
2096 ,p_calling_mode => l_calling_mode
2097 ,x_return_status => l_return_status
2098 );
2099 l_stage := 'End of populate_rbsmap_tmp api for RBS NumOfRecsInserted['||g_rbs_numRecInserted||']';
2100 print_msg(g_debug_flag,l_stage);
2101
2102 /* call rbs mapping api */
2103 l_rbsmap_return_status := 'S';
2104 If g_rbs_numRecInserted > 0 Then
2105 /** bug fix 3658113 is reverted
2106 l_stage := 'Calling pa_rbs_mapping.create_mapping_rules api';
2107 print_msg(g_debug_flag,l_stage);
2108 pa_rbs_mapping.create_mapping_rules
2109 (
2110 p_rbs_version_id => g_rbs_version_id
2111 ,x_return_status => l_rbsmap_return_status
2112 ,x_msg_count => l_msg_count
2113 ,x_msg_data => l_msg_data
2114 );
2115 l_stage := 'End Ofcreate_mapping_rules api RetSts['||l_rbsmap_return_status||']msgData['||l_msg_data||']';
2116 print_msg(g_debug_flag,l_stage);
2117 ***/
2118 If NVL(l_rbsmap_return_status,'S') = 'S' Then
2119 l_stage := 'Calling pa_rbs_mapping.map_rbs_plans api';
2120 print_msg(g_debug_flag,l_stage);
2121 pa_rbs_mapping.map_rbs_plans
2122 (p_rbs_version_id => g_rbs_version_id
2123 ,x_return_status => l_rbsmap_return_status
2124 ,x_msg_count => l_msg_count
2125 ,x_msg_data => l_msg_data
2126 );
2127 l_stage := 'End Of pa_rbs_mapping.map_rbs_plans api RetSts['||l_rbsmap_return_status||']';
2128 print_msg(g_debug_flag,l_stage);
2129 End If;
2130 End If;
2131 End If;
2132 End If;
2133
2134
2135 If ( g_res_numRecInserted > 0 OR g_rbs_numRecInserted > 0 ) Then
2136 /* After resource mapping read the values from out tmp tables and
2137 * populate the plsqltables */
2138 l_stage := 'Calling populate_resrbsmap_outTbls API';
2139 print_msg(g_debug_flag,l_stage);
2140 populate_resrbsmap_outTbls
2141 (p_process_code => p_process_code
2142 ,p_calling_mode => l_calling_mode
2143 ,p_resource_list_id => g_resource_list_id
2144 ,p_budget_version_id => g_budget_version_id
2145 ,x_return_status => l_return_status
2146 );
2147 l_stage := 'Calling populate_resrbsmap_outTbls API NumofOutRecs['||g_txn_source_id_sqltab.count||']';
2148 print_msg(g_debug_flag,l_stage);
2149
2150 /* Assign the output to plsqltabls */
2151 l_stage := 'Assigning values to OUT Plsql Tabs';
2152 print_msg(g_debug_flag,l_stage);
2153 If p_TXN_SOURCE_ID_tab.COUNT > 0 Then
2154 FOR i IN 1..p_TXN_SOURCE_ID_tab.COUNT LOOP
2155 IF(p_TXN_SOURCE_ID_tab(i) <> g_txn_source_id_sqltab(i)) THEN
2156 FOR j IN 1..g_txn_source_id_sqltab.COUNT LOOP
2157 IF(g_txn_source_id_sqltab(j) = p_TXN_SOURCE_ID_tab(i) ) THEN
2158 x_txn_source_id_tab(i) := g_txn_source_id_sqltab(j);
2159 x_res_list_member_id_tab(i) := g_res_list_member_id_sqltab(j);
2160 x_rbs_element_id_tab(i) := g_rbs_element_id_sqltab(j);
2161 x_txn_accum_header_id_tab(i) := g_txn_accum_header_id_sqltab(j);
2162 EXIT;
2163 END IF;
2164 END LOOP;
2165
2166 ELSE
2167 x_txn_source_id_tab(i) := g_txn_source_id_sqltab(i);
2168 x_res_list_member_id_tab(i) := g_res_list_member_id_sqltab(i);
2169 x_rbs_element_id_tab(i) := g_rbs_element_id_sqltab(i);
2170 x_txn_accum_header_id_tab(i) := g_txn_accum_header_id_sqltab(i);
2171 END IF;
2172
2173 END LOOP;
2174 Elsif p_calling_mode = 'BUDGET_VERSION' Then
2175 x_txn_source_id_tab := g_txn_source_id_sqltab;
2176 x_res_list_member_id_tab := g_res_list_member_id_sqltab;
2177 x_rbs_element_id_tab := g_rbs_element_id_sqltab;
2178 x_txn_accum_header_id_tab := g_txn_accum_header_id_sqltab;
2179 End If;
2180 End If;
2181
2182 /* Set the return status of based on the res and rbs mapping api */
2183 l_stage := 'Setting Return sts based on Res/Rbs mapping ResMapSts['||l_resmap_return_status||
2184 ']RbsMapSts['||l_rbsmap_return_status||']';
2185 print_msg(g_debug_flag,l_stage);
2186 If l_resmap_return_status <> 'S' Then
2187 l_return_status := l_resmap_return_status;
2188 Else
2189 If l_rbsmap_return_status <> 'S' Then
2190 l_return_status := l_rbsmap_return_status;
2191 End if;
2192 End If;
2193
2194 /* Assign the out put variables */
2195 x_return_status := l_return_status;
2196 x_msg_count := l_msg_count;
2197 x_msg_data := l_msg_data;
2198
2199 l_stage := 'End Of PA_RLMI_RBS_MAP_PUB.Map_Rlmi_Rbs API';
2200 print_msg(g_debug_flag,l_stage);
2201 /* Reset the error Stack */
2202 PA_DEBUG.reset_err_stack;
2203
2204 EXCEPTION
2205 WHEN OTHERS THEN
2206 print_msg(g_debug_flag,l_stage||':'||SQLCODE||SQLERRM);
2207 If g_debug_context = 'PLSQL' Then
2208 PA_DEBUG.WRITE_FILE('LOG',l_stage);
2209 PA_DEBUG.WRITE_FILE('LOG','SQLERROR:'||SQLCODE||SQLERRM);
2210 Elsif g_debug_context = 'SELF_SERVICE' Then
2211 PA_DEBUG.WRITE_LOG(x_module => 'pa.plsql.pa_rlmi_rbs_pub.map_rlmi_rbs'
2212 ,x_msg => l_stage||':'||SQLCODE||SQLERRM
2213 ,x_log_level => 5 );
2214 End If;
2215 FND_MSG_PUB.add_exc_msg
2216 ( p_pkg_name => 'PA_RLMI_RBS_MAP_PUB'
2217 ,p_procedure_name => 'Map_Rlmi_Rbs');
2218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2219 PA_DEBUG.reset_err_stack;
2220 RAISE;
2221
2222 END Map_Rlmi_Rbs;
2223
2224 /* This API will be called from Self-Service and Java pages */
2225 PROCEDURE Map_Rlmi_Rbs
2226 ( p_budget_version_id IN Number
2227 ,p_project_id IN Number Default NULL
2228 ,p_resource_list_id IN Number Default NULL
2229 ,p_rbs_version_id IN Number Default NULL
2230 ,p_calling_process IN Varchar2
2231 ,p_calling_context IN varchar2 Default 'PLSQL'
2232 ,p_process_code IN varchar2 Default 'RES_MAP'
2233 ,p_calling_mode IN Varchar2 Default 'PLSQL_TABLE'
2234 ,p_init_msg_list_flag IN Varchar2 Default 'N'
2235 ,p_commit_flag IN Varchar2 Default 'N'
2236 ,p_TXN_SOURCE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2237 ,p_TXN_SOURCE_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2238 ,p_PERSON_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2239 ,p_JOB_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2240 ,p_ORGANIZATION_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2241 ,p_VENDOR_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2242 ,p_EXPENDITURE_TYPE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2243 ,p_EVENT_TYPE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2244 ,p_NON_LABOR_RESOURCE_tab IN system.PA_VARCHAR2_20_TBL_TYPE Default system.PA_VARCHAR2_20_TBL_TYPE()
2245 ,p_EXPENDITURE_CATEGORY_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2246 ,p_REVENUE_CATEGORY_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2247 ,p_NLR_ORGANIZATION_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2248 ,p_EVENT_CLASSIFICATION_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2249 ,p_SYS_LINK_FUNCTION_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2250 ,p_PROJECT_ROLE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2251 ,p_RESOURCE_CLASS_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2252 ,p_MFC_COST_TYPE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2253 ,p_RESOURCE_CLASS_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
2254 ,p_FC_RES_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2255 ,p_INVENTORY_ITEM_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2256 ,p_ITEM_CATEGORY_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2257 ,p_PERSON_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
2258 ,p_BOM_RESOURCE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2259 ,p_NAMED_ROLE_tab IN system.PA_VARCHAR2_80_TBL_TYPE Default system.PA_VARCHAR2_80_TBL_TYPE()
2260 ,p_INCURRED_BY_RES_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
2261 ,p_RATE_BASED_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
2262 ,p_TXN_TASK_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2263 ,p_TXN_WBS_ELEMENT_VER_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2264 ,p_TXN_RBS_ELEMENT_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
2265 ,P_CBS_ELEMENT_ID_TAB IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE() --bug#16827157
2266 ,p_TXN_PLAN_START_DATE_tab IN system.PA_DATE_TBL_TYPE Default system.PA_DATE_TBL_TYPE()
2267 ,p_TXN_PLAN_END_DATE_tab IN system.PA_DATE_TBL_TYPE Default system.PA_DATE_TBL_TYPE()
2268 ,x_txn_source_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
2269 ,x_res_list_member_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
2270 ,x_rbs_element_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
2271 ,x_txn_accum_header_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
2272 ,x_return_status OUT NOCOPY Varchar2
2273 ,x_msg_count OUT NOCOPY Number
2274 ,x_msg_data OUT NOCOPY Varchar2
2275 ) IS
2276 l_resource_list_id Number;
2277 l_rbs_version_id Number;
2278 l_calling_mode Varchar2(100);
2279 l_return_status varchar2(10) := 'S';
2280 l_resmap_return_status varchar2(10) := 'S';
2281 l_rbsmap_return_status varchar2(10) := 'S';
2282 l_msg_count Number := 0;
2283 l_msg_data Varchar2(1000);
2284 l_stage Varchar2(1000);
2285 l_tab_count Number := 0;
2286
2287
2288 BEGIN
2289 /* INitizalize the out variables*/
2290 x_return_status := 'S';
2291 x_msg_data := Null;
2292 x_msg_count := Null;
2293 --- Initialize the error statck
2294 PA_DEBUG.init_err_stack ('PA_RLMI_RBS_MAP_PUB.Map_Rlmi_Rbs');
2295
2296 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
2297 g_debug_flag := NVL(g_debug_flag, 'N');
2298
2299 /* Bug fix: 4345057 */
2300 If NVL(g_debug_flag,'N') = 'Y' Then
2301 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
2302 ,x_write_file => 'LOG'
2303 ,x_debug_mode => g_debug_flag
2304 );
2305 End If;
2306 g_debug_context := p_calling_context;
2307 x_txn_source_id_tab := system.PA_NUM_TBL_TYPE();
2308 --x_res_map_reject_code_tab := system.PA_VARCHAR2_30_TBL_TYPE();
2309 --x_rbs_map_reject_code_tab := system.PA_VARCHAR2_30_TBL_TYPE();
2310 x_res_list_member_id_tab := system.PA_NUM_TBL_TYPE();
2311 x_rbs_element_id_tab := system.PA_NUM_TBL_TYPE();
2312 x_txn_accum_header_id_tab := system.PA_NUM_TBL_TYPE();
2313
2314 l_stage := 'Begin of SYSTEM.Map_Rlmi_Rbs IN Params:BdgtVer['||p_budget_version_id||']ReslistId['||p_resource_list_id||
2315 ']RbsVers['||p_rbs_version_id||']CallingProcess['||p_calling_process||']CallContext['||p_calling_context||
2316 ']ProcessCode['||p_process_code||']CallingMode['||p_calling_mode||']InitMsg['||p_init_msg_list_flag||
2317 ']CommitFlag['||p_commit_flag||']ProjectId['||p_project_Id||']' ;
2318 Print_msg(g_debug_flag,l_stage);
2319
2320 /* Initialize the Variables with IN params*/
2321 l_stage := 'Calling Init_ReqdVariables api';
2322 print_msg(g_debug_flag,l_stage);
2323 Init_ReqdVariables(
2324 p_process_code => p_process_code
2325 ,p_project_id => p_project_id
2326 ,p_resource_list_id => p_resource_list_id
2327 ,p_rbs_version_id => p_rbs_version_id
2328 ,p_budget_version_id => p_budget_version_id
2329 );
2330 l_stage := 'GlobalVariables:BdgtVer['||g_budget_version_id||']ProjId['||g_project_id||']ReslistId['||g_resource_list_id||
2331 ']RbsVersion['||g_rbs_version_id||']';
2332 print_msg(g_debug_flag,l_stage);
2333
2334
2335
2336 /* based on the calling mode Assign the INparam to Global varaibles */
2337 IF p_calling_mode = 'PLSQL_TABLE' Then
2338 l_tab_count := p_TXN_SOURCE_ID_Tab.Count;
2339 l_stage := 'Begin Assigning In system tables to Global Plsql Tables plsqlTabCount['||l_tab_count||']';
2340 print_msg(g_debug_flag,l_stage);
2341 g_TXN_SOURCE_ID_sysTab := p_TXN_SOURCE_ID_Tab;
2342 g_TXN_SOURCE_TYPE_CODE_systab := p_TXN_SOURCE_TYPE_CODE_tab;
2343 g_PERSON_ID_systab := p_PERSON_ID_tab;
2344 g_JOB_ID_systab := p_JOB_ID_tab;
2345 g_ORGANIZATION_ID_systab := p_ORGANIZATION_ID_tab;
2346 g_VENDOR_ID_systab := p_vendor_id_tab;
2347 g_EXPENDITURE_TYPE_systab := p_expenditure_type_tab;
2348 g_EVENT_TYPE_systab := p_event_type_tab;
2349 g_NON_LABOR_RESOURCE_systab := p_non_labor_resource_tab;
2350 g_EXPENDITURE_CATEGORY_systab := p_expenditure_category_tab;
2351 g_REVENUE_CATEGORY_CODE_systab := p_revenue_category_code_tab;
2352 g_NLR_ORGANIZATION_ID_systab := p_NLR_organization_id_tab;
2353 g_EVENT_CLASSIFICATION_systab := p_event_classification_tab;
2354 g_SYS_LINK_FUNCTION_systab := p_sys_link_function_tab;
2355 g_PROJECT_ROLE_ID_systab := p_project_role_id_tab;
2356 g_RESOURCE_CLASS_CODE_systab := p_resource_class_code_tab;
2357 g_MFC_COST_TYPE_ID_systab := p_mfc_cost_type_id_tab;
2358 g_RESOURCE_CLASS_FLAG_systab := p_resource_class_flag_tab;
2359 g_FC_RES_TYPE_CODE_systab := p_fc_res_type_code_tab;
2360 g_INVENTORY_ITEM_ID_systab := p_inventory_item_id_tab;
2361 g_ITEM_CATEGORY_ID_systab := p_item_category_id_tab;
2362 g_PERSON_TYPE_CODE_systab := p_person_type_code_tab;
2363 g_BOM_RESOURCE_ID_systab := p_bom_resource_id_tab;
2364 g_NAMED_ROLE_systab := p_named_role_tab;
2365 g_INCURRED_BY_RES_FLAG_systab := p_incurred_by_res_flag_tab;
2366 g_RATE_BASED_FLAG_systab := p_rate_based_flag_tab;
2367 g_TXN_TASK_ID_systab := p_txn_task_id_tab;
2368 g_TXN_WBS_ELE_VER_ID_systab := p_txn_wbs_element_ver_id_tab;
2369 g_TXN_RBS_ELEMENT_ID_systab := p_txn_rbs_element_id_tab;
2370 g_CBS_ELEMENT_ID_systab := P_CBS_ELEMENT_ID_TAB; --bug#16827157
2371 g_TXN_PLAN_START_DATE_systab := p_txn_plan_start_date_tab;
2372 g_TXN_PLAN_END_DATE_systab := p_txn_plan_end_date_tab;
2373 l_stage := 'End of Assigning system tables to Global system Tables';
2374
2375
2376 print_msg(g_debug_flag,l_stage);
2377 /* End of Assigning plsql tables */
2378 IF l_tab_count = 0 THEN
2379 --No records to process. Return
2380 l_stage := 'The Source Id tab is Null. No record to process so Return';
2381 print_msg(g_debug_flag,l_stage);
2382 PA_DEBUG.reset_err_stack;
2383 RETURN;
2384 ELSIF l_tab_count > 0 Then
2385 l_stage := 'Checking the Table count and Extending the elements if not found';
2386 print_msg(g_debug_flag,l_stage);
2387 FOR i IN g_TXN_SOURCE_ID_sysTab.FIRST .. g_TXN_SOURCE_ID_sysTab.LAST LOOP
2388 If NOT g_TXN_SOURCE_TYPE_CODE_systab.EXISTS(i) Then
2389 g_TXN_SOURCE_TYPE_CODE_systab.Extend;
2390 g_TXN_SOURCE_TYPE_CODE_systab(i) := null;
2391 Else
2392 If g_TXN_SOURCE_TYPE_CODE_systab(i) = fnd_api.g_miss_char Then
2393 g_TXN_SOURCE_TYPE_CODE_systab(i) := null;
2394 End If;
2395 End If;
2396 If NOT g_PERSON_ID_systab.EXISTS(i) Then
2397 g_PERSON_ID_systab.Extend;
2398 g_PERSON_ID_systab(i) := null;
2399 Else
2400 IF g_PERSON_ID_systab(i) = fnd_api.g_miss_num Then
2401 g_PERSON_ID_systab(i) := null;
2402 End IF;
2403 End If;
2404 If NOT g_JOB_ID_systab.EXISTS(i) Then
2405 g_JOB_ID_systab.Extend;
2406 g_JOB_ID_systab(i) := null;
2407 Else
2408 If g_JOB_ID_systab(i) = fnd_api.g_miss_num Then
2409 g_JOB_ID_systab(i) := null;
2410 End if;
2411 End If;
2412 If NOT g_ORGANIZATION_ID_systab.EXISTS(i) Then
2413 g_ORGANIZATION_ID_systab.Extend;
2414 g_ORGANIZATION_ID_systab(i) := null;
2415 Else
2416 If g_ORGANIZATION_ID_systab(i) = fnd_api.g_miss_num Then
2417 g_ORGANIZATION_ID_systab(i) := null;
2418 End IF;
2419 End If;
2420 If NOT g_VENDOR_ID_systab.EXISTS(i) Then
2421 g_VENDOR_ID_systab.Extend;
2422 g_VENDOR_ID_systab(i) := null;
2423 Else
2424 If g_VENDOR_ID_systab(i) = fnd_api.g_miss_num Then
2425 g_VENDOR_ID_systab(i) := null;
2426 End if;
2427 End If;
2428 If NOT g_EXPENDITURE_TYPE_systab.EXISTS(i) Then
2429 g_EXPENDITURE_TYPE_systab.Extend;
2430 g_EXPENDITURE_TYPE_systab(i) := null;
2431 Else
2432 If g_EXPENDITURE_TYPE_systab(i) = fnd_api.g_miss_char Then
2433 g_EXPENDITURE_TYPE_systab(i) := null;
2434 End IF;
2435 End If;
2436 If NOT g_EVENT_TYPE_systab.EXISTS(i) Then
2437 g_EVENT_TYPE_systab.Extend;
2438 g_EVENT_TYPE_systab(i) := null;
2439 Else
2440 If g_EVENT_TYPE_systab(i) = fnd_api.g_miss_char Then
2441 g_EVENT_TYPE_systab(i) := null;
2442 End IF;
2443 End If;
2444 If NOT g_NON_LABOR_RESOURCE_systab.EXISTS(i) Then
2445 g_NON_LABOR_RESOURCE_systab.Extend;
2446 g_NON_LABOR_RESOURCE_systab(i) := null;
2447 Else
2448 IF g_NON_LABOR_RESOURCE_systab(i) = fnd_api.g_miss_char Then
2449 g_NON_LABOR_RESOURCE_systab(i) := null;
2450 End If;
2451 End If;
2452 If NOT g_EXPENDITURE_CATEGORY_systab.EXISTS(i) Then
2453 g_EXPENDITURE_CATEGORY_systab.Extend;
2454 g_EXPENDITURE_CATEGORY_systab(i) := null;
2455 Else
2456 If g_EXPENDITURE_CATEGORY_systab(i) = fnd_api.g_miss_char Then
2457 g_EXPENDITURE_CATEGORY_systab(i) := null;
2458 End if;
2459
2460 End If;
2461 If NOT g_REVENUE_CATEGORY_CODE_systab.EXISTS(i) Then
2462 g_REVENUE_CATEGORY_CODE_systab.Extend;
2463 g_REVENUE_CATEGORY_CODE_systab(i) := null;
2464
2465 Else
2466 IF g_REVENUE_CATEGORY_CODE_systab(i) = fnd_api.g_miss_char Then
2467 g_REVENUE_CATEGORY_CODE_systab(i) := null;
2468 End IF;
2469 End If;
2470 If NOT g_NLR_ORGANIZATION_ID_systab.EXISTS(i) Then
2471 g_NLR_ORGANIZATION_ID_systab.Extend;
2472 g_NLR_ORGANIZATION_ID_systab(i) := null;
2473 Else
2474 IF g_NLR_ORGANIZATION_ID_systab(i) = fnd_api.g_miss_num Then
2475 g_NLR_ORGANIZATION_ID_systab(i) := null;
2476 End IF;
2477 End If;
2478 If NOT g_EVENT_CLASSIFICATION_systab.EXISTS(i) Then
2479 g_EVENT_CLASSIFICATION_systab.Extend;
2480 g_EVENT_CLASSIFICATION_systab(i) := null;
2481 Else
2482 If g_EVENT_CLASSIFICATION_systab(i) = fnd_api.g_miss_char Then
2483 g_EVENT_CLASSIFICATION_systab(i) := null;
2484 End IF;
2485 End If;
2486 If NOT g_SYS_LINK_FUNCTION_systab.EXISTS(i) Then
2487 g_SYS_LINK_FUNCTION_systab.Extend;
2488 g_SYS_LINK_FUNCTION_systab(i) := null;
2489 Else
2490 If g_SYS_LINK_FUNCTION_systab(i) = fnd_api.g_miss_char Then
2491 g_SYS_LINK_FUNCTION_systab(i) := null;
2492 End IF;
2493 End If;
2494 If NOT g_PROJECT_ROLE_ID_systab.EXISTS(i) Then
2495 g_PROJECT_ROLE_ID_systab.Extend;
2496 g_PROJECT_ROLE_ID_systab(i) := null;
2497 Else
2498 IF g_PROJECT_ROLE_ID_systab(i) = fnd_api.g_miss_num Then
2499 g_PROJECT_ROLE_ID_systab(i) := null;
2500 End If;
2501 End If;
2502 If NOT g_RESOURCE_CLASS_CODE_systab.EXISTS(i) Then
2503 g_RESOURCE_CLASS_CODE_systab.Extend;
2504 g_RESOURCE_CLASS_CODE_systab(i) := null;
2505 Else
2506 IF g_RESOURCE_CLASS_CODE_systab(i) = fnd_api.g_miss_char Then
2507 g_RESOURCE_CLASS_CODE_systab(i) := null;
2508 End IF;
2509 End If;
2510 IF NOT g_MFC_COST_TYPE_ID_systab.EXISTS(i) Then
2511 g_MFC_COST_TYPE_ID_systab.Extend;
2512 g_MFC_COST_TYPE_ID_systab(i) := null;
2513 Else
2514 IF g_MFC_COST_TYPE_ID_systab(i) = fnd_api.g_miss_num Then
2515 g_MFC_COST_TYPE_ID_systab(i) := null;
2516 End IF;
2517 End If;
2518 If NOT g_RESOURCE_CLASS_FLAG_systab.EXISTS(i) Then
2519 g_RESOURCE_CLASS_FLAG_systab.Extend;
2520 g_RESOURCE_CLASS_FLAG_systab(i) := null;
2521 Else
2522 IF g_RESOURCE_CLASS_FLAG_systab(i) = fnd_api.g_miss_char Then
2523 g_RESOURCE_CLASS_FLAG_systab(i) := null;
2524 End IF;
2525 End If;
2526 If NOT g_FC_RES_TYPE_CODE_systab.EXISTS(i) Then
2527 g_FC_RES_TYPE_CODE_systab.Extend;
2528 g_FC_RES_TYPE_CODE_systab(i) := null;
2529 Else
2530 IF g_FC_RES_TYPE_CODE_systab(i) = fnd_api.g_miss_char Then
2531 g_FC_RES_TYPE_CODE_systab(i) := null;
2532 End If;
2533 End If;
2534 If NOT g_INVENTORY_ITEM_ID_systab.EXISTS(i) Then
2535 g_INVENTORY_ITEM_ID_systab.Extend;
2536 g_INVENTORY_ITEM_ID_systab(i) := null;
2537 Else
2538 IF g_INVENTORY_ITEM_ID_systab(i) = fnd_api.g_miss_num Then
2539 g_INVENTORY_ITEM_ID_systab(i) := null;
2540 End IF;
2541 End If;
2542 IF NOT g_ITEM_CATEGORY_ID_systab.EXISTS(i) Then
2543 g_ITEM_CATEGORY_ID_systab.Extend;
2544 g_ITEM_CATEGORY_ID_systab(i) := null;
2545 Else
2546 If g_ITEM_CATEGORY_ID_systab(i) = fnd_api.g_miss_num Then
2547 g_ITEM_CATEGORY_ID_systab(i) := null;
2548 End IF;
2549 End If;
2550 IF NOT g_PERSON_TYPE_CODE_systab.EXISTS(i) Then
2551 g_PERSON_TYPE_CODE_systab.Extend;
2552 g_PERSON_TYPE_CODE_systab(i) := null;
2553 Else
2554 IF g_PERSON_TYPE_CODE_systab(i) = fnd_api.g_miss_char Then
2555 g_PERSON_TYPE_CODE_systab(i) := null;
2556 End IF;
2557 End If;
2558 IF NOT g_BOM_RESOURCE_ID_systab.EXISTS(i) Then
2559 g_BOM_RESOURCE_ID_systab.Extend;
2560 g_BOM_RESOURCE_ID_systab(i) := null;
2561 Else
2562 IF g_BOM_RESOURCE_ID_systab(i) = fnd_api.g_miss_num Then
2563 g_BOM_RESOURCE_ID_systab(i) := null;
2564 End IF;
2565 End IF;
2566 IF NOT g_NAMED_ROLE_systab.EXISTS(i) Then
2567 g_NAMED_ROLE_systab.Extend;
2568 g_NAMED_ROLE_systab(i) := null;
2569 Else
2570 IF g_NAMED_ROLE_systab(i) = fnd_api.g_miss_char Then
2571 g_NAMED_ROLE_systab(i) := null;
2572 End IF;
2573 End If;
2574 IF NOT g_INCURRED_BY_RES_FLAG_systab.EXISTS(i) Then
2575 g_INCURRED_BY_RES_FLAG_systab.Extend;
2576 g_INCURRED_BY_RES_FLAG_systab(i) := null;
2577 Else
2578 IF g_INCURRED_BY_RES_FLAG_systab(i) = fnd_api.g_miss_char Then
2579 g_INCURRED_BY_RES_FLAG_systab(i) := null;
2580 End IF;
2581 End If;
2582 IF NOT g_RATE_BASED_FLAG_systab.EXISTS(i) Then
2583 g_RATE_BASED_FLAG_systab.Extend;
2584 g_RATE_BASED_FLAG_systab(i) := null;
2585 Else
2586 IF g_RATE_BASED_FLAG_systab(i) = fnd_api.g_miss_char Then
2587 g_RATE_BASED_FLAG_systab(i) := null;
2588 End IF;
2589 End IF;
2590 If NOT g_TXN_TASK_ID_systab.EXISTS(i) Then
2591 g_TXN_TASK_ID_systab.Extend;
2592 g_TXN_TASK_ID_systab(i) := null;
2593 Else
2594 IF g_TXN_TASK_ID_systab(i) = fnd_api.g_miss_num Then
2595 g_TXN_TASK_ID_systab(i) := null;
2596 End IF;
2597 end if;
2598 If NOT g_TXN_WBS_ELE_VER_ID_systab.EXISTS(i) Then
2599 g_TXN_WBS_ELE_VER_ID_systab.Extend;
2600 g_TXN_WBS_ELE_VER_ID_systab(i) := null;
2601 Else
2602 IF g_TXN_WBS_ELE_VER_ID_systab(i) = fnd_api.g_miss_num Then
2603 g_TXN_WBS_ELE_VER_ID_systab(i) := null;
2604 End if;
2605 End IF;
2606 IF NOT g_TXN_RBS_ELEMENT_ID_systab.EXISTS(i) Then
2607 g_TXN_RBS_ELEMENT_ID_systab.Extend;
2608 g_TXN_RBS_ELEMENT_ID_systab(i) := null;
2609 Else
2610 IF g_TXN_RBS_ELEMENT_ID_systab(i) = fnd_api.g_miss_num Then
2611 g_TXN_RBS_ELEMENT_ID_systab(i) := null;
2612 End IF;
2613 End IF;
2614
2615 /* bug#16827157 */
2616 IF NOT g_CBS_ELEMENT_ID_systab.EXISTS(i) Then
2617 g_CBS_ELEMENT_ID_systab.Extend;
2618 g_CBS_ELEMENT_ID_systab(i) := null;
2619 Else
2620
2621 IF g_CBS_ELEMENT_ID_systab(i) = fnd_api.g_miss_num Then
2622 g_CBS_ELEMENT_ID_systab(i) := null;
2623 End IF;
2624 End IF;
2625
2626 If NOT g_TXN_PLAN_START_DATE_systab.EXISTS(i) Then
2627 g_TXN_PLAN_START_DATE_systab.Extend;
2628 g_TXN_PLAN_START_DATE_systab(i) := null;
2629 Else
2630 IF g_TXN_PLAN_START_DATE_systab(i) = fnd_api.g_miss_date Then
2631 g_TXN_PLAN_START_DATE_systab(i) := null;
2632 End IF;
2633 End IF;
2634 If NOT g_TXN_PLAN_END_DATE_systab.EXISTS(i) Then
2635 g_TXN_PLAN_END_DATE_systab.Extend;
2636 g_TXN_PLAN_END_DATE_systab(i) := null;
2637 Else
2638 IF g_TXN_PLAN_END_DATE_systab(i) = fnd_api.g_miss_date Then
2639 g_TXN_PLAN_END_DATE_systab(i) := null;
2640 End IF;
2641 End If;
2642 END LOOP;
2643 l_stage := ' End of Loop check any of the index not exists';
2644 print_msg(g_debug_flag,l_stage);
2645 END If;
2646 End If;
2647
2648 IF p_calling_mode = 'BUDGET_VERSION' Then
2649 l_calling_mode := 'BUDGET_VERSION';
2650 Elsif p_calling_mode = 'PLSQL_TABLE' Then
2651 l_calling_mode := 'SYSTEM_TABLE';
2652 End IF;
2653
2654 /* populate the resource mapping temp tables */
2655 If p_process_code in ('RES_MAP','RES_RBS_MAP') Then
2656 If g_resource_list_id is NOT NULL Then
2657 l_stage := 'Calling Populate populate_resmap_tmp api for Resource List ';
2658 print_msg(g_debug_flag,l_stage);
2659
2660
2661 populate_resmap_tmp
2662 (p_budget_version_id => g_budget_version_id
2663 ,p_calling_mode => l_calling_mode
2664 ,x_return_status => l_return_status
2665 );
2666 l_stage := 'End of Populate populate_resmap_tmp api NumOfRecsInserted['||g_res_numRecInserted||']';
2667 print_msg(g_debug_flag,l_stage);
2668
2669 l_resmap_return_status := 'S';
2670 If g_res_numRecInserted > 0 AND
2671 g_call_res_list_mapping_api = 'Y' Then
2672 /* call resource mapping api */
2673 l_stage := 'Calling pa_resource_mapping.map_resource_list api';
2674 print_msg(g_debug_flag,l_stage);
2675
2676 pa_resource_mapping.map_resource_list
2677 (p_resource_list_id => g_resource_list_id
2678 ,p_project_id => g_project_id
2679 ,x_return_status => l_resmap_return_status
2680 ,x_msg_count => l_msg_count
2681 ,x_msg_data => l_msg_data
2682 );
2683
2684 l_stage := 'End of pa_resource_mapping.map_resource_list api Resmap RetSts['
2685 ||l_resmap_return_status||']';
2686 print_msg(g_debug_flag,l_stage);
2687 End If;
2688 End If;
2689 End If;
2690
2691 /* populate the resource mapping temp tables */
2692 If p_process_code in ('RBS_MAP','RES_RBS_MAP') Then
2693 If g_rbs_version_id is NOT NULL Then
2694 l_stage := 'Calling populate_rbsmap_tmp api for RBS ';
2695 print_msg(g_debug_flag,l_stage);
2696 populate_rbsmap_tmp
2697 (p_budget_version_id => g_budget_version_id
2698 ,p_calling_mode => l_calling_mode
2699 ,x_return_status => l_return_status
2700 );
2701 l_stage := 'End of populate_rbsmap_tmp api for RBS NumOfRecsInserted['||g_rbs_numRecInserted||']';
2702 print_msg(g_debug_flag,l_stage);
2703 l_rbsmap_return_status := 'S';
2704 If g_rbs_numRecInserted > 0 Then
2705 /** bug fix3658113 is reverted
2706 l_stage := 'Calling pa_rbs_mapping.create_mapping_rules api';
2707 print_msg(g_debug_flag,l_stage);
2708 pa_rbs_mapping.create_mapping_rules
2709 (
2710 p_rbs_version_id => g_rbs_version_id
2711 ,x_return_status => l_rbsmap_return_status
2712 ,x_msg_count => l_msg_count
2713 ,x_msg_data => l_msg_data
2714 );
2715 l_stage := 'End Ofcreate_mapping_rules api RetSts['||l_rbsmap_return_status||']msgData['||l_msg_data||']';
2716 print_msg(g_debug_flag,l_stage);
2717 **/
2718
2719 If NVL(l_rbsmap_return_status,'S') = 'S' Then
2720 l_stage := 'Calling pa_rbs_mapping.map_rbs_plans api';
2721 print_msg(g_debug_flag,l_stage);
2722 pa_rbs_mapping.map_rbs_plans
2723 (p_rbs_version_id => g_rbs_version_id
2724 ,x_return_status => l_rbsmap_return_status
2725 ,x_msg_count => l_msg_count
2726 ,x_msg_data => l_msg_data
2727 );
2728 l_stage := 'End Of pa_rbs_mapping.map_rbs_plans api RetSts['||l_rbsmap_return_status||']';
2729 print_msg(g_debug_flag,l_stage);
2730 End If;
2731 End If;
2732 End If;
2733 End If;
2734
2735 If ( g_res_numRecInserted > 0 OR g_rbs_numRecInserted > 0 )Then
2736 /* After resource mapping read the values from out tmp tables and
2737 * populate the plsqltables */
2738 l_stage := 'Calling populate_resrbsmap_outTbls API';
2739 print_msg(g_debug_flag,l_stage);
2740 populate_resrbsmap_outTbls
2741 (p_process_code => p_process_code
2742 ,p_calling_mode => l_calling_mode
2743 ,p_resource_list_id => g_resource_list_id
2744 ,p_budget_version_id => g_budget_version_id
2745 ,x_return_status => l_return_status
2746 );
2747 l_stage := 'Calling populate_resrbsmap_outTbls API NumofOutRecs['||g_txn_source_id_sqltab.count||
2748 ']retSts['||l_return_status||']';
2749 print_msg(g_debug_flag,l_stage);
2750 /* Assign the output to plsqltabls */
2751 l_stage := 'Assigning values to OUT Plsql Tabs';
2752 print_msg(g_debug_flag,l_stage);
2753
2754 IF p_TXN_SOURCE_ID_tab.count > 0 Then
2755 FOR i IN 1..p_TXN_SOURCE_ID_tab.COUNT LOOP
2756 x_txn_source_id_tab.extend(1);
2757 x_res_list_member_id_tab.extend(1);
2758 x_rbs_element_id_tab.extend(1);
2759 x_txn_accum_header_id_tab.extend(1);
2760
2761 IF g_txn_source_id_systab.COUNT>0 THEN /* Added for bug 11843445 */
2762 IF(p_TXN_SOURCE_ID_tab(i) <> g_txn_source_id_systab(i)) THEN
2763 FOR j IN 1..g_txn_source_id_systab.COUNT LOOP
2764 IF(g_txn_source_id_systab(j) = p_TXN_SOURCE_ID_tab(i) ) THEN
2765 x_txn_source_id_tab(i) := g_txn_source_id_systab(j);
2766 x_res_list_member_id_tab(i) := g_res_list_member_id_systab(j);
2767 x_rbs_element_id_tab(i) := g_rbs_element_id_systab(j);
2768 x_txn_accum_header_id_tab(i) := g_txn_accum_header_id_systab(j);
2769 EXIT;
2770 END IF;
2771 END LOOP;
2772 ELSE
2773 x_txn_source_id_tab(i) := g_txn_source_id_systab(i);
2774 x_res_list_member_id_tab(i) := g_res_list_member_id_systab(i);
2775 x_rbs_element_id_tab(i) := g_rbs_element_id_systab(i);
2776 x_txn_accum_header_id_tab(i) := g_txn_accum_header_id_systab(i);
2777
2778 END IF;
2779 END IF; /* Added for bug 11843445 */
2780 END LOOP;
2781 Elsif p_calling_mode = 'BUDGET_VERSION' Then
2782 x_txn_source_id_tab := g_txn_source_id_systab;
2783 x_res_list_member_id_tab := g_res_list_member_id_systab;
2784 x_rbs_element_id_tab := g_rbs_element_id_systab;
2785 x_txn_accum_header_id_tab := g_txn_accum_header_id_systab;
2786 End If;
2787
2788 End If;
2789
2790 /* Set the return status of based on the res and rbs mapping api */
2791 l_stage := 'Setting Return sts based on Res/Rbs mapping ResMapSts['||l_resmap_return_status||
2792 ']RbsMapSts['||l_rbsmap_return_status||']';
2793 print_msg(g_debug_flag,l_stage);
2794
2795 l_stage := 'Out params TabCounts:TxnSrcTab['||x_txn_source_id_tab.count||']RlmiTabCt['||x_res_list_member_id_tab.Count||']';
2796 l_stage := l_stage||'RbsTab['||x_rbs_element_id_tab.count||']TxnAccTab['||x_txn_accum_header_id_tab.count||']';
2797 print_msg(g_debug_flag,l_stage);
2798
2799 If l_resmap_return_status <> 'S' Then
2800 l_return_status := l_resmap_return_status;
2801 Else
2802 If l_rbsmap_return_status <> 'S' Then
2803 l_return_status := l_rbsmap_return_status;
2804 End if;
2805 End If;
2806
2807 /* Assign the out put variables */
2808 x_return_status := l_return_status;
2809 x_msg_count := l_msg_count;
2810 x_msg_data := l_msg_data;
2811
2812 l_stage := 'End Of PA_RLMI_RBS_MAP_PUB.Map_Rlmi_Rbs API';
2813 print_msg(g_debug_flag,l_stage);
2814 /* Reset the error Stack */
2815 PA_DEBUG.reset_err_stack;
2816
2817 EXCEPTION
2818 WHEN OTHERS THEN
2819 print_msg(g_debug_flag,l_stage||':'||SQLCODE||SQLERRM);
2820 If g_debug_context = 'PLSQL' Then
2821 PA_DEBUG.WRITE_FILE('LOG',l_stage);
2822 PA_DEBUG.WRITE_FILE('LOG','SQLERROR:'||SQLCODE||SQLERRM);
2823 Elsif g_debug_context = 'SELF_SERVICE' Then
2824 PA_DEBUG.WRITE_LOG(x_module => 'pa.plsql.pa_rlmi_rbs_pub.map_rlmi_rbs'
2825 ,x_msg => l_stage||':'||SQLCODE||SQLERRM
2826 ,x_log_level => 5 );
2827 End If;
2828 FND_MSG_PUB.add_exc_msg
2829 ( p_pkg_name => 'PA_RLMI_RBS_MAP_PUB'
2830 ,p_procedure_name => 'Map_Rlmi_Rbs');
2831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2832 PA_DEBUG.reset_err_stack;
2833 RAISE;
2834 END Map_Rlmi_Rbs;
2835
2836 /* This API updates the new frozen RBS version on all affected projects.
2837 * Befare Calling this API, user has to populate the following global temp Table
2838 * with all the affected project Ids : pji_pjp_proj_batch_map
2839 * The out param x_return_status will be 'S' in case of Success, 'E'- Error , 'U' - Unexpected Errors
2840 */
2841 PROCEDURE Push_RBS_Version
2842 (p_old_rbs_version_id IN NUMBER
2843 ,p_new_rbs_version_id IN NUMBER
2844 ,x_return_status OUT NOCOPY VARCHAR2
2845 ,x_msg_count OUT NOCOPY Number
2846 ,x_msg_data OUT NOCOPY Varchar2 ) IS
2847
2848 CURSOR cur_rbsVersions(p_worker_id Number,p_rbs_header_id NUMBER) IS -- Modified for Bug 6450168
2849 SELECT fp.project_id project_id
2850 ,fp.fin_plan_option_level_code finplan_option_level
2851 ,fp.fin_plan_version_id budget_version_id
2852 ,fp.Rbs_version_id
2853 ,fp.proj_fp_options_id
2854 ,pp.segment1 project_name
2855 ,bv.version_name version_name
2856 ,fptyp.name plan_type_name
2857 FROM pa_budget_versions bv
2858 ,pa_proj_fp_options fp
2859 -- ,pji_pjp_proj_batch_map rbs -- Bug 16841995 - The templates should also be updated with the latest rbs_version_id
2860 ,pa_projects_all pp
2861 ,pa_fin_plan_types_tl fptyp
2862 ,pa_rbs_versions_b rvb -- Added for Bug 6450168
2863 WHERE bv.budget_version_id (+) = fp.fin_plan_version_id
2864 AND nvl(bv.project_id,fp.project_id) = fp.project_id
2865 -- ANd fp.project_id = rbs.project_id
2866 AND fp.project_id = pp.project_id
2867 AND fp.rbs_version_id = rvb.rbs_version_id -- Added for Bug 6450168
2868 AND rvb.rbs_header_id = p_rbs_header_id -- Added for Bug 6450168
2869 -- AND rbs.PROJECT_ACTIVE_FLAG = 'Y' --commented for bug 4579741
2870 -- AND rbs.WORKER_ID = p_worker_id
2871 AND fp.fin_plan_type_id = fptyp.fin_plan_type_id (+)
2872 AND NVL(fptyp.language,userenv('LANG')) = userenv('LANG')
2873 ORDER BY project_id, budget_version_id
2874 FOR UPDATE OF fp.proj_fp_options_id,bv.budget_version_id ;
2875
2876
2877 -- OLAP
2878 CURSOR cur_rbsVersions_ppr(p_rbs_header_id NUMBER) IS -- Modified for Bug 6450168
2879 SELECT fp.project_id project_id
2880 ,fp.fin_plan_option_level_code finplan_option_level
2881 ,fp.fin_plan_version_id budget_version_id
2882 ,fp.Rbs_version_id
2883 ,fp.proj_fp_options_id
2884 ,pp.segment1 project_name
2885 ,bv.version_name version_name
2886 ,fptyp.name plan_type_name
2887 FROM pa_budget_versions bv
2888 ,pa_proj_fp_options fp
2889 --,pji_pjp_proj_batch_map rbs
2890 ,pa_projects_all pp
2891 ,pa_fin_plan_types_tl fptyp
2892 ,pa_rbs_versions_b rvb -- Added for Bug 6450168
2893 WHERE bv.budget_version_id (+) = fp.fin_plan_version_id
2894 AND nvl(bv.project_id,fp.project_id) = fp.project_id
2895 --ANd fp.project_id = PA_ppr_concurrent_program.g_project_id -- Bug 16841995 - The templates should also be updated with the latest rbs_version_id
2896 AND fp.project_id = pp.project_id
2897 AND fp.rbs_version_id = rvb.rbs_version_id -- Added for Bug 6450168
2898 AND rvb.rbs_header_id = PA_ppr_concurrent_program.g_rbs_header_id -- Added for Bug 6450168
2899 -- AND rbs.PROJECT_ACTIVE_FLAG = 'Y' --commented for bug 4579741
2900 -- AND rbs.WORKER_ID = p_worker_id
2901 AND fp.fin_plan_type_id = fptyp.fin_plan_type_id (+)
2902 AND NVL(fptyp.language,userenv('LANG')) = userenv('LANG')
2903 ORDER BY project_id, budget_version_id
2904 FOR UPDATE OF fp.proj_fp_options_id,bv.budget_version_id ;
2905 -- End OLAP
2906
2907 CURSOR check_ResAsgn_Exists(p_budget_version_id Number) IS
2908 SELECT 'Y'
2909 FROM dual
2910 WHERE EXISTS (select null
2911 from pa_resource_assignments ra
2912 where ra.budget_version_id = p_budget_version_id);
2913
2914 CURSOR rbs_name IS
2915 SELECT rbs1.name old_rbs_name
2916 ,rbs2.name new_rbs_name
2917 FROM pa_rbs_versions_v rbs1
2918 ,pa_rbs_versions_v rbs2
2919 WHERE rbs1.rbs_version_id = p_old_rbs_version_id
2920 AND rbs2.rbs_version_id = p_new_rbs_version_id;
2921
2922 rbsnameRec rbs_name%rowtype;
2923
2924 l_txn_source_id_tab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
2925 l_res_list_member_id_tab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
2926 l_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
2927 l_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
2928 l_calling_mode Varchar2(100);
2929 l_calling_context Varchar2(100);
2930 l_prev_project_id Number;
2931 l_return_status Varchar2(100);
2932 l_msg_count Number;
2933 l_msg_data Varchar2(1000);
2934 l_stage Varchar2(1000);
2935 l_worker_id Number;
2936 l_resAsgnExistsFlag Varchar2(10) := 'N';
2937 l_proc_name varchar2(100) := 'Push_RBS_Version';
2938 l_msg_index_out Number;
2939 INVALID_PARAMS EXCEPTION;
2940 l_process varchar2(30); -- Added for Bug 6450168
2941 l_rbs_header_id Number; -- Added for Bug 6450168
2942
2943 BEGIN
2944 /* INitizalize the out variables*/
2945 x_return_status := 'S';
2946 l_return_status := 'S';
2947 x_msg_data := Null;
2948 x_msg_count := Null;
2949
2950 --- Initialize the error statck
2951 PA_DEBUG.init_err_stack ('PA_RLMI_RBS_MAP_PUB.Push_RBS_Version');
2952
2953 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
2954 g_debug_flag := NVL(g_debug_flag, 'N');
2955
2956 /* Initialize the msg stack */
2957 FND_MSG_PUB.initialize;
2958
2959 /* Bug fix: 4345057 */
2960 If NVL(g_debug_flag,'N') = 'Y' Then
2961 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
2962 ,x_write_file => 'LOG'
2963 ,x_debug_mode => g_debug_flag
2964 );
2965 End If;
2966
2967 l_stage := 'Begin of RBS_PUSH API: IN Param OldRbsVer['||p_old_rbs_version_id||']NewRbsVer['||p_new_rbs_version_id||']';
2968 Print_msg(g_debug_flag,l_stage,l_proc_name);
2969
2970 /* this initializes the tmp table */
2971 IF PA_ppr_concurrent_program.g_project_id IS NULL THEN --OLAP
2972 l_worker_id := PJI_PJP_EXTRACTION_UTILS.GET_WORKER_ID;
2973
2974 /* Added for Bug 6450168 - START */
2975 l_process := PJI_PJP_SUM_MAIN.g_process || to_char(l_worker_id);
2976 l_rbs_header_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,'RBS_HEADER_ID');
2977 /* Added for Bug 6450168 - END */
2978
2979 If p_old_rbs_version_id is NULL OR p_new_rbs_version_id is NULL OR l_worker_id is NULL Then
2980 print_msg(g_debug_flag,'Invalid Params',l_proc_name);
2981 Raise Invalid_params;
2982 END If;
2983 ELSE
2984
2985 l_rbs_header_id := PA_ppr_concurrent_program.g_rbs_header_id;
2986 If p_old_rbs_version_id is NULL OR p_new_rbs_version_id is NULL Then
2987 print_msg(g_debug_flag,'Invalid Params',l_proc_name);
2988 Raise Invalid_params;
2989 END If;
2990 END IF; -- End OLAP
2991
2992 OPEN rbs_name;
2993 FETCH rbs_name INTO rbsnameRec;
2994 CLOSE rbs_name;
2995
2996 /* For Each budget version Id call the RBS mapping api and update the
2997 * the rbs_element_id from the tmp table on proj_fp_options, budget versions, and resource assignments
2998 * table
2999 */
3000 l_prev_project_id := NULL;
3001 IF PA_ppr_concurrent_program.g_project_id IS NULL THEN -- OLAP
3002
3003 FOR i IN cur_rbsVersions(l_worker_id,l_rbs_header_id) LOOP --{
3004 l_stage := 'Inside loop For Finplanlevel['||i.finplan_option_level||']Project['||i.project_id||']Bdgt['||i.budget_version_id||']';
3005 Print_msg(g_debug_flag,l_stage,l_proc_name);
3006 l_return_status := 'S';
3007 l_msg_count := Null;
3008 l_msg_data := Null;
3009
3010 /* Bug fix: 3977666 As discussed with Vijay Ranganathan, The proj fp options must be updated first and then
3011 * to call the RBS mapping api. So moving the code of updating pa_proj_fp_options at end to first */
3012 /* once all the mapping is done for the project update the fp options at plan Type and project level and version level
3013 */
3014 l_stage := 'Update pa proj fp options with RBS details: FpOptionId['||i.proj_fp_options_id||']BdgtVer['||i.budget_version_id||']';
3015 Print_msg(g_debug_flag,l_stage,l_proc_name);
3016 UPDATE pa_proj_fp_options fp
3017 SET fp.rbs_version_id = p_new_rbs_version_id
3018 ,fp.record_version_number = nvl(fp.record_version_number,0) +1
3019 WHERE fp.project_id = i.Project_id
3020 --AND fp.rbs_version_id = p_old_rbs_version_id -- Commented for Bug 6450168
3021 AND fp.proj_fp_options_id = i.proj_fp_options_id;
3022 print_msg(g_debug_flag,'NumberOfRowsUpdated['||sql%rowcount||']',l_proc_name);
3023
3024 /* Call rbs mapping at plan version level */
3025 IF i.budget_version_id is NOT NULL Then --{
3026 /* Initialize the plsql tables*/
3027 l_txn_source_id_tab.delete;
3028 l_res_list_member_id_tab.delete;
3029 l_rbs_element_id_tab.delete;
3030 l_txn_accum_header_id_tab.delete;
3031
3032 l_resAsgnExistsFlag := 'N';
3033 OPEN check_ResAsgn_Exists(i.budget_version_id);
3034 FETCH check_ResAsgn_Exists INTO l_resAsgnExistsFlag;
3035 IF check_ResAsgn_Exists%NOTFOUND THEN
3036 l_resAsgnExistsFlag := 'N';
3037 END IF;
3038 CLOSE check_ResAsgn_Exists;
3039
3040 IF nvl(l_resAsgnExistsFlag,'N') = 'Y' Then
3041 print_msg(g_debug_flag,'Calling PA_RLMI_RBS_MAP_PUB.Map_Rlmi_Rbs API l_resAsgnExistsFlag['||l_resAsgnExistsFlag||']',l_proc_name);
3042 l_return_status := 'S';
3043 PA_RLMI_RBS_MAP_PUB.Map_Rlmi_Rbs
3044 ( p_budget_version_id => i.budget_version_id
3045 ,p_resource_list_id => Null
3046 ,p_rbs_version_id => p_new_rbs_version_id
3047 ,p_calling_process => 'RBS_PUSH'
3048 ,p_calling_context => 'PLSQL'
3049 ,p_process_code => 'RBS_MAP'
3050 ,p_calling_mode => 'BUDGET_VERSION'
3051 ,p_init_msg_list_flag => 'N'
3052 ,p_commit_flag => 'N'
3053 ,x_txn_source_id_tab => l_txn_source_id_tab
3054 ,x_res_list_member_id_tab => l_res_list_member_id_tab
3055 ,x_rbs_element_id_tab => l_rbs_element_id_tab
3056 ,x_txn_accum_header_id_tab => l_txn_accum_header_id_tab
3057 ,x_return_status => l_return_status
3058 ,x_msg_count => l_msg_count
3059 ,x_msg_data => l_msg_data
3060 );
3061 l_stage := 'End of Map_Rlmi_Rbs RetSts['||l_return_status||']MsgData['||l_msg_data||']txnSrcTabCount['||l_txn_source_id_tab.count||']';
3062 l_stage := l_stage||'RlmiTabCount['||l_res_list_member_id_tab.count||']RbsEleTabCount['||l_rbs_element_id_tab.count||']';
3063 l_stage := l_stage||'TxnAccHeadCount['||l_txn_accum_header_id_tab.count||']';
3064 Print_msg(g_debug_flag,l_stage,l_proc_name);
3065 pa_debug.write_file('LOG',l_stage);
3066
3067 IF NVL(l_return_status,'E') <> 'S' Then
3068 l_stage := 'PA_FP_RBS_PUSH_ERROR: '||'OLD_RBS_VERSION['||rbsnameRec.old_rbs_name||']NEW_RBS_VERSION['||rbsnameRec.new_rbs_name;
3069 l_stage := substr(l_stage||']PROJECT_NAME['||i.project_name||']PLAN_VERSION_NAME['||i.version_name,1,1000);
3070 l_stage := substr(l_stage||']PLAN_TYPE['||i.plan_type_name||']',1,1000);
3071 l_stage := substr(l_stage,1,1000);
3072 print_msg(g_debug_flag,l_stage,l_proc_name);
3073
3074 pa_utils.add_message
3075 ( p_app_short_name => 'PA'
3076 ,p_msg_name => 'PA_FP_RBS_PUSH_ERROR'
3077 ,p_token1 => 'OLD_RBS_VERSION'
3078 ,p_value1 => rbsnameRec.old_rbs_name
3079 ,p_token2 => 'NEW_RBS_VERSION'
3080 ,p_value2 => rbsnameRec.new_rbs_name
3081 ,p_token3 => 'PROJECT_NAME'
3082 ,p_value3 => i.project_name
3083 ,p_token4 => 'PLAN_VERSION_NAME'
3084 ,p_value4 => i.version_name
3085 ,p_token5 => 'PLAN_TYPE'
3086 ,p_value5 => i.plan_type_name
3087 );
3088 END IF;
3089
3090 /* update the resource assignment table with the new rbs details */
3091 If l_txn_source_id_tab.count > 0 AND l_return_status = 'S' Then
3092 l_stage := 'Update Resource assignments with new RBS details';
3093 Print_msg(g_debug_flag,l_stage,l_proc_name);
3094 FORALL j IN l_txn_source_id_tab.FIRST ..l_txn_source_id_tab.LAST
3095 UPDATE pa_resource_assignments ra
3096 SET ra.rbs_element_id = NVL(l_rbs_element_id_tab(j),ra.rbs_element_id)
3097 ,ra.txn_accum_header_id = NVL(l_txn_accum_header_id_tab(j),ra.txn_accum_header_id)
3098 WHERE ra.budget_version_id = i.budget_version_id
3099 AND ra.project_id = i.project_id
3100 AND ra.resource_assignment_id = l_txn_source_id_tab(j) ;
3101 End If;
3102
3103 End If; --end of l_resAsgnExistsFlag =Y
3104
3105 If l_return_status = 'S' Then
3106 /* update the budget version recod version nubmer, so that any changes in the rbs version related to
3107 * old budget version should be in synch with these changes
3108 */
3109 l_stage := 'Update pa_budget_versions with recordVerNum:BdgtVer['||i.budget_version_id||']';
3110 Print_msg(g_debug_flag,l_stage);
3111 UPDATE pa_budget_versions bv
3112 SET bv.record_version_number = nvl(bv.record_version_number,0) +1
3113 WHERE bv.budget_version_id = i.budget_version_id;
3114
3115 End If;
3116 End if; --}
3117
3118 END LOOP; --}
3119 ELSE
3120
3121 FOR i IN cur_rbsVersions_ppr(l_rbs_header_id) LOOP --{
3122 l_stage := 'Inside loop For Finplanlevel['||i.finplan_option_level||']Project['||i.project_id||']Bdgt['||i.budget_version_id||']';
3123 Print_msg(g_debug_flag,l_stage,l_proc_name);
3124 l_return_status := 'S';
3125 l_msg_count := Null;
3126 l_msg_data := Null;
3127
3128 /* Bug fix: 3977666 As discussed with Vijay Ranganathan, The proj fp options must be updated first and then
3129 * to call the RBS mapping api. So moving the code of updating pa_proj_fp_options at end to first */
3130 /* once all the mapping is done for the project update the fp options at plan Type and project level and version level
3131 */
3132 l_stage := 'Update pa proj fp options with RBS details: FpOptionId['||i.proj_fp_options_id||']BdgtVer['||i.budget_version_id||']';
3133 Print_msg(g_debug_flag,l_stage,l_proc_name);
3134 UPDATE pa_proj_fp_options fp
3135 SET fp.rbs_version_id = p_new_rbs_version_id
3136 ,fp.record_version_number = nvl(fp.record_version_number,0) +1
3137 WHERE fp.project_id = i.Project_id
3138 --AND fp.rbs_version_id = p_old_rbs_version_id -- Commented for Bug 6450168
3139 AND fp.proj_fp_options_id = i.proj_fp_options_id;
3140 print_msg(g_debug_flag,'NumberOfRowsUpdated['||sql%rowcount||']',l_proc_name);
3141
3142 /* Call rbs mapping at plan version level */
3143 IF i.budget_version_id is NOT NULL Then --{
3144 /* Initialize the plsql tables*/
3145 l_txn_source_id_tab.delete;
3146 l_res_list_member_id_tab.delete;
3147 l_rbs_element_id_tab.delete;
3148 l_txn_accum_header_id_tab.delete;
3149
3150 l_resAsgnExistsFlag := 'N';
3151 OPEN check_ResAsgn_Exists(i.budget_version_id);
3152 FETCH check_ResAsgn_Exists INTO l_resAsgnExistsFlag;
3153 IF check_ResAsgn_Exists%NOTFOUND THEN
3154 l_resAsgnExistsFlag := 'N';
3155 END IF;
3156 CLOSE check_ResAsgn_Exists;
3157
3158 IF nvl(l_resAsgnExistsFlag,'N') = 'Y' Then
3159 print_msg(g_debug_flag,'Calling PA_RLMI_RBS_MAP_PUB.Map_Rlmi_Rbs API l_resAsgnExistsFlag['||l_resAsgnExistsFlag||']',l_proc_name);
3160 l_return_status := 'S';
3161 PA_RLMI_RBS_MAP_PUB.Map_Rlmi_Rbs
3162 ( p_budget_version_id => i.budget_version_id
3163 ,p_resource_list_id => Null
3164 ,p_rbs_version_id => p_new_rbs_version_id
3165 ,p_calling_process => 'RBS_PUSH'
3166 ,p_calling_context => 'PLSQL'
3167 ,p_process_code => 'RBS_MAP'
3168 ,p_calling_mode => 'BUDGET_VERSION'
3169 ,p_init_msg_list_flag => 'N'
3170 ,p_commit_flag => 'N'
3171 ,x_txn_source_id_tab => l_txn_source_id_tab
3172 ,x_res_list_member_id_tab => l_res_list_member_id_tab
3173 ,x_rbs_element_id_tab => l_rbs_element_id_tab
3174 ,x_txn_accum_header_id_tab => l_txn_accum_header_id_tab
3175 ,x_return_status => l_return_status
3176 ,x_msg_count => l_msg_count
3177 ,x_msg_data => l_msg_data
3178 );
3179 l_stage := 'End of Map_Rlmi_Rbs RetSts['||l_return_status||']MsgData['||l_msg_data||']txnSrcTabCount['||l_txn_source_id_tab.count||']';
3180 l_stage := l_stage||'RlmiTabCount['||l_res_list_member_id_tab.count||']RbsEleTabCount['||l_rbs_element_id_tab.count||']';
3181 l_stage := l_stage||'TxnAccHeadCount['||l_txn_accum_header_id_tab.count||']';
3182 Print_msg(g_debug_flag,l_stage,l_proc_name);
3183 pa_debug.write_file('LOG',l_stage);
3184
3185 IF NVL(l_return_status,'E') <> 'S' Then
3186 l_stage := 'PA_FP_RBS_PUSH_ERROR: '||'OLD_RBS_VERSION['||rbsnameRec.old_rbs_name||']NEW_RBS_VERSION['||rbsnameRec.new_rbs_name;
3187 l_stage := substr(l_stage||']PROJECT_NAME['||i.project_name||']PLAN_VERSION_NAME['||i.version_name,1,1000);
3188 l_stage := substr(l_stage||']PLAN_TYPE['||i.plan_type_name||']',1,1000);
3189 l_stage := substr(l_stage,1,1000);
3190 print_msg(g_debug_flag,l_stage,l_proc_name);
3191
3192 pa_utils.add_message
3193 ( p_app_short_name => 'PA'
3194 ,p_msg_name => 'PA_FP_RBS_PUSH_ERROR'
3195 ,p_token1 => 'OLD_RBS_VERSION'
3196 ,p_value1 => rbsnameRec.old_rbs_name
3197 ,p_token2 => 'NEW_RBS_VERSION'
3198 ,p_value2 => rbsnameRec.new_rbs_name
3199 ,p_token3 => 'PROJECT_NAME'
3200 ,p_value3 => i.project_name
3201 ,p_token4 => 'PLAN_VERSION_NAME'
3202 ,p_value4 => i.version_name
3203 ,p_token5 => 'PLAN_TYPE'
3204 ,p_value5 => i.plan_type_name
3205 );
3206 END IF;
3207
3208 /* update the resource assignment table with the new rbs details */
3209 If l_txn_source_id_tab.count > 0 AND l_return_status = 'S' Then
3210 l_stage := 'Update Resource assignments with new RBS details';
3211 Print_msg(g_debug_flag,l_stage,l_proc_name);
3212 FORALL j IN l_txn_source_id_tab.FIRST ..l_txn_source_id_tab.LAST
3213 UPDATE pa_resource_assignments ra
3214 SET ra.rbs_element_id = NVL(l_rbs_element_id_tab(j),ra.rbs_element_id)
3215 ,ra.txn_accum_header_id = NVL(l_txn_accum_header_id_tab(j),ra.txn_accum_header_id)
3216 WHERE ra.budget_version_id = i.budget_version_id
3217 AND ra.project_id = i.project_id
3218 AND ra.resource_assignment_id = l_txn_source_id_tab(j) ;
3219 End If;
3220
3221 End If; --end of l_resAsgnExistsFlag =Y
3222
3223 If l_return_status = 'S' Then
3224 /* update the budget version recod version nubmer, so that any changes in the rbs version related to
3225 * old budget version should be in synch with these changes
3226 */
3227 l_stage := 'Update pa_budget_versions with recordVerNum:BdgtVer['||i.budget_version_id||']';
3228 Print_msg(g_debug_flag,l_stage);
3229 UPDATE pa_budget_versions bv
3230 SET bv.record_version_number = nvl(bv.record_version_number,0) +1
3231 WHERE bv.budget_version_id = i.budget_version_id;
3232
3233 End If;
3234 End if; --}
3235
3236 END LOOP; --}
3237 END IF; -- End OLAP
3238
3239 x_return_status := l_return_status;
3240 x_msg_data := l_msg_data;
3241 x_msg_count := l_msg_count;
3242
3243 x_msg_count := fnd_msg_pub.count_msg;
3244 If x_msg_count is NULL then x_msg_count := 0; End if;
3245 IF x_msg_count = 1 THEN
3246 pa_interface_utils_pub.get_messages
3247 ( p_encoded => FND_API.G_TRUE
3248 ,p_msg_index => 1
3249 ,p_data => x_msg_data
3250 ,p_msg_index_out => l_msg_index_out
3251 );
3252 x_return_status := 'E';
3253 ELSIF x_msg_count > 1 THEN
3254 x_msg_count := x_msg_count;
3255 x_msg_data := null;
3256 x_return_status := 'E';
3257 END IF;
3258
3259 l_stage := 'End Push_RBS_Version API: RtrnSts['||x_return_status||']x_msg_count['||x_msg_count||']';
3260 Print_msg(g_debug_flag,l_stage,l_proc_name);
3261
3262 /* Reset the error Stack */
3263 PA_DEBUG.reset_err_stack;
3264
3265 EXCEPTION
3266
3267 WHEN INVALID_PARAMS THEN
3268
3269 print_msg(g_debug_flag,l_stage||':'||'INVALID_PARAMS',l_proc_name);
3270 PA_DEBUG.WRITE_FILE('LOG',l_stage);
3271 PA_DEBUG.WRITE_FILE('LOG','SQLERROR:'||SQLCODE||SQLERRM);
3272 FND_MSG_PUB.add_exc_msg
3273 ( p_pkg_name => 'PA_RLMI_RBS_MAP_PUB'
3274 ,p_procedure_name => 'Push_RBS_Version');
3275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3276 x_msg_data := 'INVALID_PARAMS';
3277 PA_DEBUG.reset_err_stack;
3278 WHEN OTHERS THEN
3279 print_msg(g_debug_flag,l_stage||':'||SQLCODE||SQLERRM,l_proc_name);
3280 PA_DEBUG.WRITE_FILE('LOG',l_stage);
3281 PA_DEBUG.WRITE_FILE('LOG','SQLERROR:'||SQLCODE||SQLERRM);
3282 FND_MSG_PUB.add_exc_msg
3283 ( p_pkg_name => 'PA_RLMI_RBS_MAP_PUB'
3284 ,p_procedure_name => 'Push_RBS_Version');
3285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3286 PA_DEBUG.reset_err_stack;
3287 RAISE;
3288
3289 END Push_RBS_Version;
3290
3291
3292 END PA_RLMI_RBS_MAP_PUB ;