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