[Home] [Help]
PACKAGE BODY: APPS.PA_RESOURCE_MAPPING
Source
1 PACKAGE BODY PA_RESOURCE_MAPPING AS
2 /* $Header: PARSMAPB.pls 120.6 2005/09/08 05:25:46 appldev noship $ */
3
4 -- Global variables -----------------------------------
5 g_pa_schema VARCHAR2(30);
6
7 --g_tmp_id is used to identify if source table is TMP1/2
8 g_tmp_id NUMBER := 1;
9
10 --g_run_number is used to identify records in TMP4 in the
11 --current run (avoids illegal ROWID deletes)
12 g_run_number NUMBER := 1;
13
14 --The following 2 variables represent the current resource
15 --list and format being processed
16 g_resource_list_id NUMBER;
17 g_res_format_id NUMBER;
18
19 g_project_id NUMBER; --bug#3576766
20
21 --This flag indicates if resource list is centrally controlled
22 --or not
23 g_control_flag VARCHAR2(1);
24
25
26 ---------------------------------
27 --Gets the Projects Schema name
28 ---------------------------------
29 FUNCTION get_pa_schema_name RETURN VARCHAR2 IS
30
31 l_status VARCHAR2(30);
32 l_industry VARCHAR2(30);
33 excp_get_app_info EXCEPTION;
34
35 BEGIN
36
37 IF (g_pa_schema IS NULL) THEN
38
39 IF (NOT FND_INSTALLATION.GET_APP_INFO('PA', l_status, l_industry, g_pa_schema)) THEN
40 RAISE excp_get_app_info;
41 END IF;
42
43 END IF;
44
45 RETURN g_pa_schema;
46
47 END GET_PA_SCHEMA_NAME; --end function get_pa_schema name
48
49 ----------------------------------------
50 --Gets the SQL tags for the resource
51 --format element tokens
52 --Operation types allowed - TMP3, TMP4
53 ---------------------------------------
54 FUNCTION get_SQL_tags (
55 p_resource_token VARCHAR2,
56 p_operation_type VARCHAR2, --INSERT, FROM, SELECT, WHERE1, WHERE2
57 p_mode VARCHAR2 DEFAULT 'TMP'
58 ) RETURN VARCHAR2 IS
59
60 l_str VARCHAR2(50);
61
62 BEGIN
63
64 --Get the resource element based on pre-defined tokens
65 IF p_operation_type = 'INSERT' OR
66 p_operation_type = 'SELECT' OR
67 p_operation_type = 'FROM' THEN
68
69 IF p_operation_type = 'SELECT' THEN
70 l_str := 'tab1.';
71 ELSE
72 l_str := ' ';
73 END IF;
74
75 IF p_resource_token = 'PER' THEN
76 RETURN l_str || 'PERSON_ID';
77 ELSIF p_resource_token = 'FN1' THEN
78 RETURN l_str || 'EXPENDITURE_TYPE ,' || l_str || 'FC_RES_TYPE_CODE'; --bug#3779049
79 ELSIF p_resource_token = 'FN2' THEN
80 RETURN l_str || 'EVENT_TYPE ,' || l_str || 'FC_RES_TYPE_CODE'; --bug#3779049
81 ELSIF p_resource_token = 'FN3' THEN
82 RETURN l_str || 'EXPENDITURE_CATEGORY ,' || l_str || 'FC_RES_TYPE_CODE'; --bug#3779049
83 ELSIF p_resource_token = 'FN4' THEN
84 RETURN l_str || 'REVENUE_CATEGORY ,' || l_str || 'FC_RES_TYPE_CODE'; --bug#3779049
85 ELSIF p_resource_token = 'ROL' THEN
86 RETURN l_str || 'PROJECT_ROLE_ID ,' || l_str || 'NAMED_ROLE';
87 ELSIF p_resource_token = 'ORG' THEN
88 RETURN l_str || 'ORGANIZATION_ID';
89 ELSIF p_resource_token = 'BML' THEN
90 RETURN l_str || 'BOM_RESOURCE_ID';
91 ELSIF p_resource_token = 'BME' THEN
92 RETURN l_str || 'BOM_RESOURCE_ID';
93 ELSIF p_resource_token = 'JOB' THEN
94 RETURN l_str || 'JOB_ID';
95 ELSIF p_resource_token = 'PTP' THEN
96 RETURN l_str || 'PERSON_TYPE_CODE';
97 ELSIF p_resource_token = 'VND' THEN
98 RETURN l_str || 'VENDOR_ID';
99 ELSIF p_resource_token = 'NLB' THEN
100 RETURN l_str || 'NON_LABOR_RESOURCE';
101 ELSIF p_resource_token = 'ITM' THEN
102 RETURN l_str || 'INVENTORY_ITEM_ID';
103 ELSIF p_resource_token = 'ITC' THEN
104 RETURN l_str || 'ITEM_CATEGORY_ID';
105 ELSIF p_resource_token = 'IR1' THEN
106 RETURN l_str || 'PERSON_ID';
107 ELSIF p_resource_token = 'IR2' THEN
108 RETURN l_str || 'JOB_ID';
109 ELSIF p_resource_token = 'IR3' THEN
110 RETURN l_str || 'PROJECT_ROLE_ID ,' || l_str || 'NAMED_ROLE';
111 ELSIF p_resource_token = 'IR4' THEN
112 RETURN l_str || 'PERSON_TYPE_CODE';
113 -- bug#3608042 ELSIF p_resource_token = 'IR5' THEN
114 -- bug#3608042 RETURN l_str || 'RESOURCE_CLASS_ID';
115 ELSIF p_resource_token = 'PEP'
116 OR p_resource_token = 'EQP'
117 OR p_resource_token = 'MTL'
118 OR p_resource_token = 'FNL' THEN
119
120 RETURN l_str || 'RESOURCE_CLASS_ID';
121
122 END IF;
123
124 ELSIF p_operation_type = 'WHERE1' THEN
125
126 --Where clause for equi-joins
127 IF p_resource_token = 'PER' THEN
128 RETURN ' tab1.PERSON_ID = tab2.PERSON_ID';
129 ELSIF p_resource_token = 'FN1' THEN
130 l_str := '''EXPENDITURE_TYPE'' ';
131 IF Pa_Resource_Mapping.g_called_process ='ACTUALS' THEN
132 RETURN ' tab1.EXPENDITURE_TYPE = tab2.EXPENDITURE_TYPE '; --bug#3779049
133 ELSE --bug#4318046
134 RETURN ' tab1.EXPENDITURE_TYPE = tab2.EXPENDITURE_TYPE AND tab1.FC_RES_TYPE_CODE = ' || l_str || ' and tab1.FC_RES_TYPE_CODE = tab2.FC_RES_TYPE_CODE '; --bug#3779049
135 END IF;
136 ELSIF p_resource_token = 'FN2' THEN
137 l_str := '''EVENT_TYPE'' ';
138 IF Pa_Resource_Mapping.g_called_process ='ACTUALS' THEN
139 RETURN ' tab1.EVENT_TYPE = tab2.EVENT_TYPE '; --bug#3779049
140 ELSE --bug#4318046
141 RETURN ' tab1.EVENT_TYPE = tab2.EVENT_TYPE AND tab1.FC_RES_TYPE_CODE = ' || l_str || ' and tab1.FC_RES_TYPE_CODE = tab2.FC_RES_TYPE_CODE '; --bug#3779049
142 END IF;
143 ELSIF p_resource_token = 'FN3' THEN
144 l_str := '''EXPENDITURE_CATEGORY'' ';
145 IF Pa_Resource_Mapping.g_called_process ='ACTUALS' THEN
146 RETURN ' tab1.EXPENDITURE_CATEGORY = tab2.EXPENDITURE_CATEGORY '; --bug#3779049
147 ELSE --bug#4318046
148 RETURN ' tab1.EXPENDITURE_CATEGORY = tab2.EXPENDITURE_CATEGORY AND tab1.FC_RES_TYPE_CODE = ' || l_str || ' and tab1.FC_RES_TYPE_CODE = tab2.FC_RES_TYPE_CODE ';
149 END IF;
150 ELSIF p_resource_token = 'FN4' THEN
151 l_str := '''REVENUE_CATEGORY'' ';
152 IF Pa_Resource_Mapping.g_called_process ='ACTUALS' THEN
153 RETURN ' tab1.REVENUE_CATEGORY = tab2.REVENUE_CATEGORY '; --bug#3779049
154 ELSE --bug#4318046
155
156 RETURN ' tab1.REVENUE_CATEGORY = tab2.REVENUE_CATEGORY AND tab1.FC_RES_TYPE_CODE = ' || l_str || ' and tab1.FC_RES_TYPE_CODE = tab2.FC_RES_TYPE_CODE '; --bug#3779049
157 END IF;
158 ELSIF p_resource_token = 'ROL' THEN
159 IF p_mode = 'TMP' THEN
160 RETURN ' tab1.PROJECT_ROLE_ID = tab2.PROJECT_ROLE_ID AND tab1.NAMED_ROLE = tab2.NAMED_ROLE';
161 ELSIF p_mode = 'TMP3' THEN
162 RETURN ' tab1.PROJECT_ROLE_ID = tab2.PROJECT_ROLE_ID AND tab1.NAMED_ROLE = tab2.TEAM_ROLE';
163 END IF;
164 ELSIF p_resource_token = 'ORG' THEN
165 RETURN ' tab1.ORGANIZATION_ID = tab2.ORGANIZATION_ID';
166 ELSIF p_resource_token = 'BML' THEN
167 IF p_mode = 'TMP' THEN
168 RETURN ' tab1.BOM_RESOURCE_ID = tab2.BOM_RESOURCE_ID'; --bug#3608042
169 ELSIF p_mode = 'TMP3' THEN
170 RETURN ' tab1.BOM_RESOURCE_ID = tab2.BOM_RESOURCE_ID';
171 END IF;
172 ELSIF p_resource_token = 'BME' THEN
173 IF p_mode = 'TMP' THEN
174 RETURN ' tab1.BOM_RESOURCE_ID = tab2.BOM_RESOURCE_ID'; --bug#3608042
175 ELSIF p_mode = 'TMP3' THEN
176 RETURN ' tab1.BOM_RESOURCE_ID = tab2.BOM_RESOURCE_ID';
177 END IF;
178 ELSIF p_resource_token = 'JOB' THEN
179 RETURN ' tab1.JOB_ID = tab2.JOB_ID';
180 ELSIF p_resource_token = 'PTP' THEN
181 RETURN ' tab1.PERSON_TYPE_CODE = tab2.PERSON_TYPE_CODE';
182 ELSIF p_resource_token = 'VND' THEN
183 RETURN ' tab1.VENDOR_ID = tab2.VENDOR_ID';
184 ELSIF p_resource_token = 'NLB' THEN
185 RETURN ' tab1.NON_LABOR_RESOURCE = tab2.NON_LABOR_RESOURCE';
186 ELSIF p_resource_token = 'ITM' THEN
187 RETURN ' tab1.INVENTORY_ITEM_ID = tab2.INVENTORY_ITEM_ID';
188 ELSIF p_resource_token = 'ITC' THEN
189 RETURN ' tab1.ITEM_CATEGORY_ID = tab2.ITEM_CATEGORY_ID';
190 ELSIF p_resource_token = 'IR1' THEN
191 RETURN ' tab1.PERSON_ID = tab2.PERSON_ID';
192 ELSIF p_resource_token = 'IR2' THEN
193 RETURN ' tab1.JOB_ID = tab2.JOB_ID';
194 ELSIF p_resource_token = 'IR3' THEN
195 IF p_mode = 'TMP' THEN
196 RETURN ' tab1.PROJECT_ROLE_ID = tab2.PROJECT_ROLE_ID AND tab1.NAMED_ROLE = tab2.NAMED_ROLE';
197 ELSIF p_mode = 'TMP3' THEN
198 RETURN ' tab1.PROJECT_ROLE_ID = tab2.PROJECT_ROLE_ID AND tab1.NAMED_ROLE = tab2.TEAM_ROLE';
199 END IF;
200 ELSIF p_resource_token = 'IR4' THEN
201 RETURN ' tab1.PERSON_TYPE_CODE = tab2.PERSON_TYPE_CODE';
202 -- bug#3608042 ELSIF p_resource_token = 'IR5' THEN
203 -- bug#3608042 RETURN ' tab1.RESOURCE_CLASS_ID = tab2.RESOURCE_CLASS_ID';
204 ELSIF p_resource_token = 'PEP' THEN
205 RETURN ' tab1.RESOURCE_CLASS_ID = tab2.RESOURCE_CLASS_ID';
206 ELSIF p_resource_token = 'EQP' THEN
207 RETURN ' tab1.RESOURCE_CLASS_ID = tab2.RESOURCE_CLASS_ID';
208 ELSIF p_resource_token = 'MTL' THEN
209 RETURN ' tab1.RESOURCE_CLASS_ID = tab2.RESOURCE_CLASS_ID';
210 ELSIF p_resource_token = 'FNL' THEN
211 RETURN ' tab1.RESOURCE_CLASS_ID = tab2.RESOURCE_CLASS_ID';
212 END IF;
213
214 ELSIF p_operation_type = 'WHERE2' THEN
215
216 --Where clause for NOT NULLS
217 IF p_resource_token = 'PER' THEN
218 RETURN ' tab1.PERSON_ID IS NOT NULL';
219 ELSIF p_resource_token = 'FN1' THEN
220 RETURN ' tab1.EXPENDITURE_TYPE IS NOT NULL AND tab1.FC_RES_TYPE_CODE IS NOT NULL '; --bug#3779049
221 ELSIF p_resource_token = 'FN2' THEN
222 RETURN ' tab1.EVENT_TYPE IS NOT NULL AND tab1.FC_RES_TYPE_CODE IS NOT NULL '; --bug#3779049
223 ELSIF p_resource_token = 'FN3' THEN
224 RETURN ' tab1.EXPENDITURE_CATEGORY IS NOT NULL AND tab1.FC_RES_TYPE_CODE IS NOT NULL '; --bug#3779049
225 ELSIF p_resource_token = 'FN4' THEN
226 RETURN ' tab1.REVENUE_CATEGORY IS NOT NULL AND tab1.FC_RES_TYPE_CODE IS NOT NULL '; --bug#3779049
227 ELSIF p_resource_token = 'ROL' THEN
228 RETURN ' tab1.PROJECT_ROLE_ID IS NOT NULL AND tab1.NAMED_ROLE IS NOT NULL';
229 ELSIF p_resource_token = 'ORG' THEN
230 RETURN ' tab1.ORGANIZATION_ID IS NOT NULL';
231 ELSIF p_resource_token = 'BML' THEN
232 RETURN ' tab1.BOM_RESOURCE_ID IS NOT NULL';
233 ELSIF p_resource_token = 'BME' THEN
234 RETURN ' tab1.BOM_RESOURCE_ID IS NOT NULL';
235 ELSIF p_resource_token = 'JOB' THEN
236 RETURN ' tab1.JOB_ID IS NOT NULL';
237 ELSIF p_resource_token = 'PTP' THEN
238 RETURN ' tab1.PERSON_TYPE_CODE IS NOT NULL';
239 ELSIF p_resource_token = 'VND' THEN
240 RETURN ' tab1.VENDOR_ID IS NOT NULL';
241 ELSIF p_resource_token = 'NLB' THEN
242 RETURN ' tab1.NON_LABOR_RESOURCE IS NOT NULL';
243 ELSIF p_resource_token = 'ITM' THEN
244 RETURN ' tab1.INVENTORY_ITEM_ID IS NOT NULL';
245 ELSIF p_resource_token = 'ITC' THEN
246 RETURN ' tab1.ITEM_CATEGORY_ID IS NOT NULL';
247 ELSIF p_resource_token = 'IR1' THEN
248 RETURN ' tab1.PERSON_ID IS NOT NULL';
249 ELSIF p_resource_token = 'IR2' THEN
250 RETURN ' tab1.JOB_ID IS NOT NULL';
251 ELSIF p_resource_token = 'IR3' THEN
252 RETURN ' tab1.PROJECT_ROLE_ID IS NOT NULL AND tab1.NAMED_ROLE IS NOT NULL';
253 ELSIF p_resource_token = 'IR4' THEN
254 RETURN ' tab1.PERSON_TYPE_CODE IS NOT NULL';
255 -- bug#3608042 ELSIF p_resource_token = 'IR5' THEN
256 -- bug#3608042 RETURN ' tab1.RESOURCE_CLASS_ID IS NOT NULL';
257 ELSIF p_resource_token = 'PEP' THEN
258 RETURN ' tab1.RESOURCE_CLASS_ID = 1';
259 ELSIF p_resource_token = 'EQP' THEN
260 RETURN ' tab1.RESOURCE_CLASS_ID = 2';
261 ELSIF p_resource_token = 'MTL' THEN
262 RETURN ' tab1.RESOURCE_CLASS_ID = 3';
263 ELSIF p_resource_token = 'FNL' THEN
264 RETURN ' tab1.RESOURCE_CLASS_ID = 4';
265 END IF; --end p_resource_token
266
267 END IF;--end p_operation_type
268 END; --end function get_SQL_tags
269
270 -------------------------------------
271 --Get token for a given format
272 --Token refers to the resource type
273 --identifiers which are part of the
274 --format
275 -------------------------------------
276 FUNCTION get_format_res_tokens (
277 p_resource_class_id IN NUMBER,
278 p_eff_res_format_id IN NUMBER
279 ) RETURN SYSTEM.pa_varchar2_30_tbl_type IS
280
281 l_plan_res_formats PA_RESOURCE_PREC_PUB.plan_res_formats;
282 l_format pa_plan_res_format;
283 l_token_string VARCHAR2(30);
284 l_tokens SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
285
286 BEGIN
287
288 --Get all resource tokens for a given effective format
289 IF p_resource_class_id = 1 THEN
290 l_plan_res_formats := PA_RESOURCE_PREC_PUB.g_people_formats;
291 ELSIF p_resource_class_id = 2 THEN
292 l_plan_res_formats := PA_RESOURCE_PREC_PUB.g_equipment_formats;
293 ELSIF p_resource_class_id = 3 THEN
294 l_plan_res_formats := PA_RESOURCE_PREC_PUB.g_material_formats;
295 ELSIF p_resource_class_id = 4 THEN
296 l_plan_res_formats := PA_RESOURCE_PREC_PUB.g_fin_element_formats;
297 END IF;
298
299 l_format := l_plan_res_formats (p_eff_res_format_id);
300 l_token_string := l_format.res_tokens;
301 g_res_format_id := l_format.res_format_id;
302
303 FOR i IN 0..MOD(LENGTH(l_token_string),3) LOOP
304
305 l_tokens.EXTEND;
306 l_tokens (i + 1) := SUBSTR(l_token_string, i*4 + 1, 3);
307
308 END LOOP;
309
310 RETURN l_tokens;
311
312 END;--end procedure get_format_res_tokens
313
314 ----------------------------------------------
315 --Generate the INSERT clause for the current
316 --format used in mapping
317 --Operation types allowed - TMP3, TMP4
318 ----------------------------------------------
319 FUNCTION get_insert_clause (
320 p_resource_class_id NUMBER,
321 p_res_format_id NUMBER,
322 p_operation_type VARCHAR2
323 ) RETURN VARCHAR2 IS
324
325 l_insert_clause VARCHAR2(4000);
326 l_res_tokens SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type ();
327
328 BEGIN
329
330 --Generate the INSERT clause for the
331 --given format and operation type
332 IF p_operation_type = 'TMP3' THEN
333
334 l_insert_clause := 'INSERT INTO pa_res_list_map_tmp3 ( RES_FORMAT_ID, RESOURCE_LIST_MEMBER_ID, RES_LIST_MEM_SEQ, ';
335 l_res_tokens := get_format_res_tokens (p_resource_class_id, p_res_format_id);
336
337 FOR i IN 1..l_res_tokens.COUNT LOOP
338
339 IF i <> l_res_tokens.COUNT THEN
340
341 l_insert_clause := l_insert_clause || get_SQL_tags (l_res_tokens(i), 'INSERT') || ', ';
342
343 ELSE
344
345 l_insert_clause := l_insert_clause || get_SQL_tags (l_res_tokens(i), 'INSERT') || ' ';
346
347 END IF;
348
349
350 END LOOP; --end loop l_ers_tokens
351
352 l_insert_clause := l_insert_clause || ') ';
353
354 ELSIF p_operation_type = 'TMP4' THEN
355
356 --Generate insert clause
357 l_insert_clause := ' INSERT INTO pa_res_list_map_tmp4 (PERSON_ID,JOB_ID,ORGANIZATION_ID,VENDOR_ID,'
358 || 'EXPENDITURE_TYPE,EVENT_TYPE,NON_LABOR_RESOURCE,EXPENDITURE_CATEGORY,'
359 || 'REVENUE_CATEGORY,NON_LABOR_RESOURCE_ORG_ID,EVENT_TYPE_CLASSIFICATION,'
360 || 'SYSTEM_LINKAGE_FUNCTION,PROJECT_ROLE_ID,RESOURCE_TYPE_ID,'
361 || 'RESOURCE_TYPE_CODE,RESOURCE_CLASS_ID,RESOURCE_CLASS_CODE,RES_FORMAT_ID,'
362 || 'MFC_COST_TYPE_ID,RESOURCE_CLASS_FLAG,FC_RES_TYPE_CODE,'
363 || 'BOM_LABOR_RESOURCE_ID,BOM_EQUIP_RESOURCE_ID,INVENTORY_ITEM_ID,'
364 || 'ITEM_CATEGORY_ID,PERSON_TYPE_CODE,BOM_RESOURCE_ID,NAMED_ROLE,'
365 || 'INCURRED_BY_RES_FLAG,TXN_COPY_FROM_RL_FLAG,TXN_SPREAD_CURVE_ID,'
366 || 'TXN_ETC_METHOD_CODE,TXN_OBJECT_TYPE,TXN_OBJECT_ID,TXN_PROJECT_ID,'
367 || 'TXN_BUDGET_VERSION_ID,TXN_RESOURCE_LIST_MEMBER_ID,TXN_RESOURCE_ID,'
368 || 'TXN_ALIAS,TXN_TRACK_AS_LABOR_FLAG,TXN_FUNDS_CONTROL_LEVEL_CODE,'
369 || 'TXN_SOURCE_ID,TXN_SOURCE_TYPE_CODE,TXN_PROCESS_CODE,TXN_ERROR_MSG_CODE,'
370 || 'TXN_TASK_ID,TXN_WBS_ELEMENT_VERSION_ID,TXN_RBS_ELEMENT_ID,'
371 || 'TXN_RBS_ELEMENT_VERSION_ID,TXN_PLANNING_START_DATE,TXN_PLANNING_END_DATE,'
372 || 'TXN_RECORD_VERSION_NUMBER,TXN_SP_FIXED_DATE,TXN_RATE_BASED_FLAG,'
373 || 'TXN_RES_CLASS_BILL_RATE_SCH_ID,TXN_RES_CLASS_COST_SCH_ID,'
374 || 'TXN_USE_PLANNING_RATES_FLAG,TXN_BILL_JOB_GROUP_ID,'
375 || 'TXN_PROJECT_CURRENCY_CODE,TXN_PROJFUNC_CURRENCY_CODE,'
376 || 'TXN_EMP_BILL_RATE_SCHEDULE_ID,TXN_JOB_BILL_RATE_SCHEDULE_ID,'
377 || 'TXN_LABOR_BILL_RATE_ORG_ID,TXN_LABOR_SCH_TYPE,TXN_LABOR_SCHEDULE_DISCOUNT,'
378 || 'TXN_LABOR_SCHEDULE_FIXED_DATE,TXN_LABOR_STD_BILL_RATE_SCHDL,'
379 || 'TXN_CURRENCY_CODE,TXN_PLAN_QUANTITY,RESOURCE_LIST_MEMBER_ID, TMP_ROWID) ';
380
381 END IF;--end operation type
382
383 RETURN l_insert_clause;
384
385 END;--end function get_insert_clause
386
387 ----------------------------------------------
388 --Generate the SELECT clause for the current
389 --format used in mapping
390 --Operation types allowed - TMP3, TMP4
391 --p_run_number - used to identify txns that
392 --have already been moved from TMP1/2 -> TMP4
393 --p_run_number along with ROWID ensures
394 --uniqueness
395 ----------------------------------------------
396 FUNCTION get_select_clause (
397 p_resource_class_id NUMBER,
398 p_res_format_id NUMBER,
399 p_operation_type VARCHAR2
400 ) RETURN VARCHAR2 IS
401
402 l_select_clause VARCHAR2(5000);
403 l_res_tokens SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type ();
404
405 BEGIN
406
407 --Generate the SELECT clause for the
408 --given format and operation type
409 IF p_operation_type = 'TMP3' THEN
410
411 l_select_clause := ' SELECT ' || g_res_format_id || ', tab2.RESOURCE_LIST_MEMBER_ID,'
412 || 'DECODE(tab2.RESOURCE_LIST_MEMBER_ID,NULL,pa_resource_list_members_s.NEXTVAL,NULL), ' ;
413 l_res_tokens := get_format_res_tokens (p_resource_class_id, p_res_format_id);
414
415 FOR i IN 1..l_res_tokens.COUNT LOOP
416
417 l_select_clause := l_select_clause || get_SQL_tags (l_res_tokens(i), 'SELECT');
418
419 IF i <> l_res_tokens.COUNT THEN
420
421 l_select_clause := l_select_clause || ', ';
422
423 ELSE
424
425 l_select_clause := l_select_clause || ' ';
426
427 END IF;
428
429 END LOOP; --end loop l_res_tokens
430
431 RETURN l_select_clause;
432
433 ELSIF p_operation_type = 'TMP4' THEN
434
435 --Generate select clause
436 l_select_clause := ' SELECT tab1.PERSON_ID,tab1.JOB_ID,tab1.ORGANIZATION_ID,tab1.VENDOR_ID,';
437 l_select_clause := l_select_clause || 'tab1.EXPENDITURE_TYPE,tab1.EVENT_TYPE,tab1.NON_LABOR_RESOURCE,';
438 l_select_clause := l_select_clause || 'tab1.EXPENDITURE_CATEGORY,tab1.REVENUE_CATEGORY,';
439 l_select_clause := l_select_clause || 'tab1.NON_LABOR_RESOURCE_ORG_ID,tab1.EVENT_TYPE_CLASSIFICATION,';
440 l_select_clause := l_select_clause || 'tab1.SYSTEM_LINKAGE_FUNCTION,tab1.PROJECT_ROLE_ID,tab1.RESOURCE_TYPE_ID,';
441 l_select_clause := l_select_clause || 'tab1.RESOURCE_TYPE_CODE,tab1.RESOURCE_CLASS_ID,tab1.RESOURCE_CLASS_CODE,';
442 l_select_clause := l_select_clause || 'tab2.RES_FORMAT_ID,tab1.MFC_COST_TYPE_ID,tab1.RESOURCE_CLASS_FLAG,';
443 l_select_clause := l_select_clause || 'tab1.FC_RES_TYPE_CODE,tab1.BOM_LABOR_RESOURCE_ID,';
444 l_select_clause := l_select_clause || 'tab1.BOM_EQUIP_RESOURCE_ID,tab1.INVENTORY_ITEM_ID,tab1.ITEM_CATEGORY_ID,';
445 l_select_clause := l_select_clause || 'tab1.PERSON_TYPE_CODE,tab1.BOM_RESOURCE_ID,tab1.NAMED_ROLE,';
446 l_select_clause := l_select_clause || 'tab1.INCURRED_BY_RES_FLAG,tab1.TXN_COPY_FROM_RL_FLAG,';
447 l_select_clause := l_select_clause || 'tab1.TXN_SPREAD_CURVE_ID,tab1.TXN_ETC_METHOD_CODE,tab1.TXN_OBJECT_TYPE,';
448 l_select_clause := l_select_clause || 'tab1.TXN_OBJECT_ID,tab1.TXN_PROJECT_ID,tab1.TXN_BUDGET_VERSION_ID,';
449 l_select_clause := l_select_clause || 'tab1.TXN_RESOURCE_LIST_MEMBER_ID,tab1.TXN_RESOURCE_ID,tab1.TXN_ALIAS,';
450 l_select_clause := l_select_clause || 'tab1.TXN_TRACK_AS_LABOR_FLAG,tab1.TXN_FUNDS_CONTROL_LEVEL_CODE,';
451 l_select_clause := l_select_clause || 'tab1.TXN_SOURCE_ID,tab1.TXN_SOURCE_TYPE_CODE,tab1.TXN_PROCESS_CODE,';
452 l_select_clause := l_select_clause || 'tab1.TXN_ERROR_MSG_CODE,tab1.TXN_TASK_ID,tab1.TXN_WBS_ELEMENT_VERSION_ID,';
453 l_select_clause := l_select_clause || 'tab1.TXN_RBS_ELEMENT_ID,tab1.TXN_RBS_ELEMENT_VERSION_ID,';
454 l_select_clause := l_select_clause || 'tab1.TXN_PLANNING_START_DATE,tab1.TXN_PLANNING_END_DATE,';
455 l_select_clause := l_select_clause || 'tab1.TXN_RECORD_VERSION_NUMBER,tab1.TXN_SP_FIXED_DATE,';
456 l_select_clause := l_select_clause || 'tab1.TXN_RATE_BASED_FLAG,tab1.TXN_RES_CLASS_BILL_RATE_SCH_ID,';
457 l_select_clause := l_select_clause || 'tab1.TXN_RES_CLASS_COST_SCH_ID,tab1.TXN_USE_PLANNING_RATES_FLAG,';
458 l_select_clause := l_select_clause || 'tab1.TXN_BILL_JOB_GROUP_ID,tab1.TXN_PROJECT_CURRENCY_CODE,';
459 l_select_clause := l_select_clause || 'tab1.TXN_PROJFUNC_CURRENCY_CODE,tab1.TXN_EMP_BILL_RATE_SCHEDULE_ID,';
460 l_select_clause := l_select_clause || 'tab1.TXN_JOB_BILL_RATE_SCHEDULE_ID,tab1.TXN_LABOR_BILL_RATE_ORG_ID,';
461 l_select_clause := l_select_clause || 'tab1.TXN_LABOR_SCH_TYPE,tab1.TXN_LABOR_SCHEDULE_DISCOUNT,';
462 l_select_clause := l_select_clause || 'tab1.TXN_LABOR_SCHEDULE_FIXED_DATE,tab1.TXN_LABOR_STD_BILL_RATE_SCHDL,';
463 l_select_clause := l_select_clause || 'tab1.TXN_CURRENCY_CODE,tab1.TXN_PLAN_QUANTITY,';
464 l_select_clause := l_select_clause || 'NVL(tab2.RESOURCE_LIST_MEMBER_ID,tab2.RES_LIST_MEM_SEQ),';
465 l_select_clause := l_select_clause || 'tab1.ROWID';
466
467 RETURN l_select_clause;
468
469 END IF;--end operation type
470
471 END;--end function get_select_clause
472
473 ----------------------------------------------
474 --Generate the FROM clause for the current
475 --format used in mapping
476 --Operation types allowed - TMP3, TMP4
477 ----------------------------------------------
478 FUNCTION get_from_clause (
479 p_resource_class_id NUMBER,
480 p_res_format_id NUMBER,
481 p_operation_type VARCHAR2,
482 p_src_table VARCHAR2
483 ) RETURN VARCHAR2 IS
484
485 l_from_clause VARCHAR2(1000);
486 l_res_tokens SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type ();
487
488 BEGIN
489
490 --Generate the FROM clause for the
491 --given format and operation type
492 IF p_operation_type = 'TMP3' THEN
493
494 l_from_clause := ' FROM (SELECT DISTINCT tab1.RES_FORMAT_ID, ' ;
495 l_res_tokens := get_format_res_tokens (p_resource_class_id, p_res_format_id);
496
497 FOR i IN 1..l_res_tokens.COUNT LOOP
498
499 l_from_clause := l_from_clause || get_SQL_tags (l_res_tokens(i), 'FROM');
500
501 IF i <> l_res_tokens.COUNT THEN
502
503 l_from_clause := l_from_clause || ', ';
504
505 END IF;
506
507 END LOOP; --end loop l_res_tokens
508
509 l_from_clause := l_from_clause || ' FROM ' || p_src_table || ' tab1 where resource_class_id = ' || p_resource_class_id;
510 l_from_clause := l_from_clause || ' ) tab1, pa_resource_list_members tab2 ';
511
512 RETURN l_from_clause;
513
514 ELSIF p_operation_type = 'TMP4' THEN
515
516 --Generate from clause
517 l_from_clause := ' FROM ' || p_src_table || ' tab1, pa_res_list_map_tmp3 tab2';
518
519 RETURN l_from_clause;
520
521 END IF;--end operation type
522
523 END;--end function get_from_clause
524
525 ----------------------------------------------
526 --Generate the WHERE clause for the current
527 --format used in mapping
528 --Operation types allowed - TMP3, TMP4
529 ----------------------------------------------
530 FUNCTION get_where_clause (
531 p_resource_class_id NUMBER,
532 p_res_format_id NUMBER,
533 p_operation_type VARCHAR2
534 ) RETURN VARCHAR2 IS
535
536 l_where_clause VARCHAR2(2000);
537 l_res_tokens SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type ();
538
539 l_pos NUMBER; --bug#3799207
540
541 BEGIN
542
543 --Generate the WHERE clause for the
544 --given format and operation type
545 IF p_operation_type = 'TMP3' THEN
546
547 l_where_clause := ' WHERE ';
548 l_res_tokens := get_format_res_tokens (p_resource_class_id, p_res_format_id);
549
550 FOR i IN 1..l_res_tokens.COUNT LOOP
551
552 -- added (+) for bug#3799207, please note below condition handles only one AND in the string if we get
553 -- more than one AND we need to handle by writing a function
554
555 IF instr(get_SQL_tags (l_res_tokens(i), 'WHERE1', 'TMP3'), ' AND ') > 0 AND g_control_flag = 'N' THEN
556 l_pos := instr(get_SQL_tags (l_res_tokens(i), 'WHERE1', 'TMP3'), ' AND ');
557 l_where_clause := l_where_clause || substr(get_SQL_tags (l_res_tokens(i), 'WHERE1', 'TMP3'),1,l_pos) || ' (+) ' || substr(get_SQL_tags (l_res_tokens(i), 'WHERE1', 'TMP3'),l_pos); --EQUI JOIN
558 --l_where_clause := l_where_clause || CHR(13)|| CHR(10);
559 ELSE
560 l_where_clause := l_where_clause || get_SQL_tags (l_res_tokens(i), 'WHERE1', 'TMP3'); --EQUI JOIN
561 --l_where_clause := l_where_clause || CHR(13)|| CHR(10);
562 END IF;
563
564 --Outer joins are required only when new planning resources need to be
565 --identified. If the resource list is centrally controlled and does not
566 --allow creation of new planning resources then outer join must not be
567 --used. If a planning resource does not exist for the current format
568 --proceed to next format. All resource lists have the resource class
569 --format and this will ensure that there are no unmapped transactions
570 IF g_control_flag = 'N' THEN
571 l_where_clause := l_where_clause || ' (+) AND ';
572 ELSE
573 l_where_clause := l_where_clause || ' AND ';
574 END IF;
575
576 l_where_clause := l_where_clause || get_SQL_tags (l_res_tokens(i), 'WHERE2', 'TMP3');--IS NOT NULL
577
578 IF i <> l_res_tokens.COUNT THEN
579
580 l_where_clause := l_where_clause || ' AND ';
581
582 ELSE
583
584 l_where_clause := l_where_clause || ' ';
585
586 END IF;
587
588 END LOOP; --end loop l_res_tokens
589
590 --Below if conditions for control flag is for same reasons
591 --as explained above
592 l_where_clause := l_where_clause || ' AND tab2.resource_list_id ';
593 IF g_control_flag = 'N' THEN
594 l_where_clause := l_where_clause || ' (+) ';
595 END IF;
596 l_where_clause := l_where_clause || '= ' || g_resource_list_id;
597
598 l_where_clause := l_where_clause || ' AND tab2.res_format_id ';
599 IF g_control_flag = 'N' THEN
600 l_where_clause := l_where_clause || ' (+) ';
601 END IF;
602 l_where_clause := l_where_clause || '= ' || g_res_format_id;
603
604 --start bug#3576766, null value for g_control_flag is considered as Y
605
606 IF g_control_flag = 'N' THEN
607 l_where_clause := l_where_clause || ' AND tab2.object_id (+) = ' || g_project_id;
608 ELSE
609 l_where_clause := l_where_clause || ' AND tab2.object_id = ' || g_resource_list_id;
610 END IF;
611
612 IF g_control_flag = 'N' THEN
613 l_where_clause := l_where_clause || ' AND tab2.object_type (+) = ' || '''' || 'PROJECT' || '''';
614 ELSE
615 l_where_clause := l_where_clause || ' AND tab2.object_type = ' || '''' || 'RESOURCE_LIST' || '''';
616 END IF;
617
618 --end bug#3576766
619
620 --start bug#3627812
621 IF g_control_flag = 'N' THEN
622 l_where_clause := l_where_clause || ' AND tab2.migration_code(+) is not null ' ;
623 ELSE
624 l_where_clause := l_where_clause || ' AND tab2.migration_code is not null ' ;
625 END IF;
626 --end bug#3627812
627
628 --start bug#3665722
629 IF g_control_flag = 'N' THEN
630 l_where_clause := l_where_clause || ' AND tab2.enabled_flag (+) = ' || '''' || 'Y' || '''';
631 ELSE
632 l_where_clause := l_where_clause || ' AND tab2.enabled_flag = ' || '''' || 'Y' || '''';
633 END IF;
634 --end bug#3665722
635
636 ELSIF p_operation_type = 'TMP4' THEN
637
638 --Generate where clause
639 l_where_clause := ' WHERE ';
640 l_res_tokens := get_format_res_tokens (p_resource_class_id, p_res_format_id);
641
642 FOR i IN 1..l_res_tokens.COUNT LOOP
643
644 l_where_clause := l_where_clause || get_SQL_tags (l_res_tokens(i), 'WHERE1'); --EQUI JOIN
645
646 IF i <> l_res_tokens.COUNT THEN
647
648 l_where_clause := l_where_clause || ' AND ';
649
650 END IF;
651
652 END LOOP; --end loop l_res_tokens
653
654 l_where_clause := l_where_clause || ' AND tab1.resource_class_id = ' || p_resource_class_id;
655 END IF;--end operation type
656
657 RETURN l_where_clause;
658
659 END;--end function get_where_clause
660
661 ----------------------------------------
662 --Step that identifies txns in TMP1/TMP2
663 --that already map to existing planning
664 --resources in pa_resource_list_members
665 --Data is populated in TMP3 from TMP1/2
666 --TMP3 contains the mapped planning
667 --resource identifier or the new planning
668 --resource identifier that needs to be
669 --created in pa_resource_list_members
670 -----------------------------------------
671 FUNCTION identify_new_plan_res (
672 p_resource_class_id NUMBER,
673 p_format_id NUMBER
674 ) RETURN NUMBER IS
675
676 l_SQL_statement VARCHAR2 (4000);
677 l_INSERT_clause VARCHAR2 (1000);
678 l_SELECT_clause VARCHAR2 (1000);
679 l_FROM_clause VARCHAR2 (1000);
680 l_WHERE_clause VARCHAR2 (2000);
681
682 l_src_table VARCHAR2(20) := 'PA_RES_LIST_MAP_TMP1';
683
684 BEGIN
685
686 PA_DEBUG.init_err_stack('identify_new_plan_res');
687
688 --Generate SQL statement to populate TMP3
689 --This step differentiates the mapped and
690 --unmapped headers
691
692 l_INSERT_clause := get_insert_clause (p_resource_class_id, p_format_id, 'TMP3');
693 l_SELECT_clause := get_select_clause (p_resource_class_id, p_format_id, 'TMP3');
694 l_FROM_clause := get_from_clause (p_resource_class_id, p_format_id, 'TMP3', l_src_table);
695 l_WHERE_clause := get_where_clause (p_resource_class_id, p_format_id, 'TMP3');
696 l_SQL_statement := l_INSERT_clause || ' ' ||
697 l_SELECT_clause || ' ' ||
698 l_FROM_clause || ' ' ||
699 l_WHERE_clause || ';' ;
700
701 EXECUTE IMMEDIATE 'BEGIN ' ||l_SQL_statement || ' END;';
702 l_SQL_statement := NULL; --reset SQL statement
703
704 pa_debug.reset_err_stack;
705
706 RETURN 0;
707
708 END;--end function identify_new_plan_res
709
710 ------------------------------------
711 --Verifies if unmapped txns exist
712 --in TMP1 or TMP2
713 ------------------------------------
714 FUNCTION txns_to_map_exists
715 RETURN BOOLEAN IS
716 l_count NUMBER := 0;
717 BEGIN
718
719 -- Removed tmp2 as part of bug fix : 4199314
720 /* SELECT count(*) into l_count
721 FROM pa_res_list_map_tmp1
722 IF (l_count = 0) THEN
723 RETURN FALSE;
724 ELSE
725 RETURN TRUE;
726 END IF; */
727 -- Commented this as part of bug fix :4350603
728 SELECT 1
729 INTO l_count FROM dual WHERE EXISTS (SELECT 1
730 FROM pa_res_list_map_tmp1);
731 RETURN TRUE;
732 EXCEPTION WHEN NO_DATA_FOUND THEN
733 RETURN FALSE;
734 END;--end function txns_to_map_exists
735
736 ------------------------------------
737 --Creates planning resources in
738 --pa_resource_list_members
739 ------------------------------------
740 PROCEDURE create_planning_resources (
741 p_format_id NUMBER,
742 p_resource_list_id NUMBER,
743 p_resource_class_id NUMBER,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_msg_count OUT NOCOPY NUMBER,
746 x_msg_data OUT NOCOPY VARCHAR2)
747 IS
748
749 CURSOR new_planning_resources (l_resource_class_id NUMBER) IS
750 SELECT
751 tmp3.RES_LIST_MEM_SEQ,
752 tmp3.RES_FORMAT_ID,
753 tmp3.BOM_LABOR_RESOURCE_ID,
754 tmp3.BOM_EQUIP_RESOURCE_ID,
755 tmp3.BOM_RESOURCE_ID,
756 tmp3.PERSON_ID,
757 tmp3.EVENT_TYPE,
758 tmp3.EXPENDITURE_CATEGORY,
759 tmp3.EXPENDITURE_TYPE,
760 tmp3.ITEM_CATEGORY_ID,
761 tmp3.INVENTORY_ITEM_ID,
762 tmp3.JOB_ID,
763 tmp3.ORGANIZATION_ID,
764 tmp3.PERSON_TYPE_CODE,
765 tmp3.NON_LABOR_RESOURCE,
766 tmp3.REVENUE_CATEGORY,
767 tmp3.VENDOR_ID,
768 tmp3.PROJECT_ROLE_ID,
769 tmp3.FC_RES_TYPE_CODE,
770 tmp3.INCURRED_BY_RES_FLAG,
771 tmp3.NAMED_ROLE,
772 cls.RESOURCE_CLASS_ID,
773 cls.RESOURCE_CLASS_CODE
774 FROM
775 pa_res_list_map_tmp3 tmp3,
776 pa_resource_classes_b cls
777 WHERE
778 RESOURCE_LIST_MEMBER_ID IS NULL AND
779 nvl(tmp3.resource_class_id, l_resource_class_id) = cls.resource_class_id;
780
781 --bug#3691060 l_return_status VARCHAR2(30);
782 l_msg_data VARCHAR2(30);
783 l_msg_count NUMBER;
784 l_record_version_number NUMBER;
785 l_fin_category_name VARCHAR2(30);
786 l_rlm_id NUMBER;
787 l_incur_by_res_code VARCHAR2(30);
788 l_incur_by_res_type VARCHAR2(30);
789
790 BEGIN
791
792 -- Initialize the return status to success
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794
795 PA_DEBUG.init_err_stack('create_planning_resource');
796
797 --Create missing planning resources in
798 --PA_RESOURCE_LIST_MEMBERS
799 FOR planning_res_rec IN new_planning_resources(p_resource_class_id) LOOP
800 -- process data record
801 --Call API to create planning resources
802 --API provided by resource foundation team
803
804 IF planning_res_rec.fc_res_type_code = 'EXPENDITURE_TYPE' THEN
805 l_fin_category_name := planning_res_rec.expenditure_type;
806 ELSIF planning_res_rec.fc_res_type_code = 'EVENT_TYPE' THEN
807 l_fin_category_name := planning_res_rec.event_type;
808 ELSIF planning_res_rec.fc_res_type_code = 'EXPENDITURE_CATEGORY' THEN
809 l_fin_category_name := planning_res_rec.expenditure_category;
810 ELSIF planning_res_rec.fc_res_type_code = 'REVENUE_CATEGORY' THEN
811 l_fin_category_name := planning_res_rec.revenue_category;
812 END IF;
813
814 /* IF planning_res_rec.incurred_by_res_flag = 'Y' THEN bug#3833910 */
815 IF planning_res_rec.person_id IS NOT NULL THEN
816 l_incur_by_res_code := planning_res_rec.person_id;
817 l_incur_by_res_type := 'NAMED_PERSON';
818 ELSIF planning_res_rec.job_id IS NOT NULL THEN
819 l_incur_by_res_code := planning_res_rec.job_id;
820 l_incur_by_res_type := 'JOB';
821 ELSIF planning_res_rec.person_type_code IS NOT NULL THEN
822 l_incur_by_res_code := planning_res_rec.person_type_code;
823 l_incur_by_res_type := 'PERSON_TYPE';
824 ELSIF planning_res_rec.project_role_id IS NOT NULL THEN
825 -- Overloading columns because mapping temp tables are missing
826 -- incur_by_role_id and incur_by_res_class_code columns.
827 l_incur_by_res_code := planning_res_rec.project_role_id;
828 l_incur_by_res_type := 'ROLE';
829 ELSIF planning_res_rec.resource_class_code IS NOT NULL THEN
830 -- Overloading columns because mapping temp tables are missing
831 -- incur_by_role_id and incur_by_res_class_code columns.
832 l_incur_by_res_code := planning_res_rec.resource_class_code;
833 l_incur_by_res_type := 'RESOURCE_CLASS';
834 ELSE
835 l_incur_by_res_code := NULL;
836 l_incur_by_res_type := NULL;
837 END IF;
838 /* bug#3833910 ELSE
839 l_incur_by_res_code := NULL;
840 l_incur_by_res_type := NULL;
841 END IF; */
842
843 pa_planning_resource_pvt.create_planning_resource(
844 p_resource_list_id => g_resource_list_id,
845 p_person_id => planning_res_rec.person_id,
846 p_job_id => planning_res_rec.job_id,
847 p_organization_id => planning_res_rec.organization_id,
848 p_vendor_id => planning_res_rec.vendor_id,
849 p_fin_category_name => l_fin_category_name,
850 p_non_labor_resource => planning_res_rec.non_labor_resource,
851 p_project_role_id => planning_res_rec.project_role_id,
852 p_resource_class_id => planning_res_rec.resource_class_id,
853 p_resource_class_code => planning_res_rec.resource_class_code,
854 p_res_format_id => planning_res_rec.res_format_id,
855 p_fc_res_type_code => planning_res_rec.fc_res_type_code,
856 p_inventory_item_id => planning_res_rec.inventory_item_id,
857 p_item_category_id => planning_res_rec.item_category_id,
858 p_person_type_code => planning_res_rec.person_type_code,
859 p_bom_resource_id => planning_res_rec.bom_resource_id,
860 --p_named_role => planning_res_rec.named_role,
861 p_team_role => planning_res_rec.named_role,
862 p_project_id => g_project_id, /* bug#3679994 */
863 --p_incurred_by_res_flag => planning_res_rec.incurred_by_res_flag,
864 p_incur_by_res_code => l_incur_by_res_code,
865 p_incur_by_res_type => l_incur_by_res_type,
866 p_resource_list_member_id => planning_res_rec.res_list_mem_seq,
867 x_resource_list_member_id => l_rlm_id,
868 x_record_version_number => l_record_version_number,
869 x_return_status => x_return_status, /* bug#3691060 changed l_return_status to x_return_status */
870 x_msg_count => l_msg_count,
871 x_error_msg_data => l_msg_data );
872
873 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
874 EXIT;
875 END IF;
876
877 END LOOP;
878
879 pa_debug.reset_err_stack;
880
881 END; --end procedure create_planning_resources
882
883 -----------------------------------------------
884 --Step moves mapped data from TMP3 -> TMP4
885 --mapped data includes existing/new planning
886 --resources for all transactions
887 -----------------------------------------------
888 FUNCTION process_mapped_txns (
889 p_resource_class_id NUMBER,
890 p_format_id NUMBER)
891 RETURN NUMBER IS
892
893 l_SQL_statement VARCHAR2 (10000);
894 l_INSERT_clause VARCHAR2 (4000);
895 l_SELECT_clause VARCHAR2 (5000);
896 l_FROM_clause VARCHAR2 (500);
897 l_WHERE_clause VARCHAR2 (500);
898 l_src_table VARCHAR2(20) := 'pa_res_list_map_tmp1';
899
900 BEGIN
901
902 PA_DEBUG.init_err_stack('process_mapped_txns');
903
904 --Move mapped txns to TMP4
905 --Truncate TMP3 (It will be used to
906 --map remaining txns)
907 l_INSERT_clause := get_insert_clause (p_resource_class_id, p_format_id, 'TMP4');
908 l_SELECT_clause := get_select_clause (p_resource_class_id, p_format_id, 'TMP4');
909 l_FROM_clause := get_from_clause (p_resource_class_id, p_format_id, 'TMP4', l_src_table);
910 l_WHERE_clause := get_where_clause (p_resource_class_id, p_format_id, 'TMP4');
911
912 l_SQL_statement := l_INSERT_clause || ' ' ||
913 l_SELECT_clause || ' ' ||
914 l_FROM_clause || ' ' ||
915 l_WHERE_clause || ';' ;
916
917 EXECUTE IMMEDIATE 'BEGIN ' || l_SQL_statement || ' END;';
918 l_SQL_statement := NULL; --reset SQL statement
919
920 --
921 -- Replaced truncate statement with delete for resolving the auto commit issue.
922 --EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || get_pa_schema_name || '.PA_RES_LIST_MAP_TMP3');
923 EXECUTE IMMEDIATE ('DELETE FROM ' || get_pa_schema_name || '.PA_RES_LIST_MAP_TMP3');
924 pa_debug.reset_err_stack;
925
926 RETURN 0;
927
928 END;--end function process_mapped_txns
929
930 -----------------------------------------
931 --Processes unmapped txns for next format
932 -----------------------------------------
933 FUNCTION process_txns_for_next_format RETURN NUMBER IS
934 BEGIN
935
936 PA_DEBUG.init_err_stack('process_txns_for_next_format');
937
938 --Move txns that did not satisfy format
939 --to TMP2/TMP1 based on the run sequence
940 --Truncate processed txns in TMP1/TMP2
941 -- commenting OUT for bug : 4199314
942 -- As part of bug fix we are deleting all the records from TEMP1 that exists in
943 -- TEMP4
944 EXECUTE IMMEDIATE ('DELETE FROM ' || get_pa_schema_name || '.PA_RES_LIST_MAP_TMP1 TMP1' || ' WHERE ROWID IN ( SELECT tmp_rowid FROM pa_res_list_map_tmp4 tmp4 WHERE tmp4.tmp_rowid = tmp1.rowid ) ');
945
946 pa_debug.reset_err_stack;
947
948 RETURN 0;
949
950 END;--end function process_txns_for_next_format
951
952 ----------------------------------------------------
953 --Main private procedure that maps transactions to a
954 --format. Steps include the following
955 -- Step 1: Identify new planning resources that
956 -- need to be created
957 -- Step 2: Create new planning resources
958 -- Step 3: Process mapped txns (move them to
959 -- destination table)
960 -- Step 4: Cleanup staging tables and prepare
961 -- remaining transactions for next format
962 ---------------------------------------------------
963 PROCEDURE map_for_format(
964 p_resource_class_id IN NUMBER,
965 p_format_id IN NUMBER,
966 p_resource_list_id IN NUMBER,
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_msg_count OUT NOCOPY NUMBER,
969 x_msg_data OUT NOCOPY VARCHAR2
970 ) IS
971 l_status NUMBER := -1;
972 BEGIN
973
974 -- Initialize the return status to success
975 x_return_status := FND_API.G_RET_STS_SUCCESS;
976
977 PA_DEBUG.init_err_stack('map_for_format');
978
979 --Identify new planning resources that need to be created
980 --Generate SQL for TMP3
981 l_status := identify_new_plan_res (p_resource_class_id, p_format_id);
982
983 IF l_status <> 0 THEN
984 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
985 RETURN;
986 END IF;
987
988
989 --Create missing planning resources identified above
990 --This step involves creating new resource list members using
991 --API provided by resource foundation team
992 --New planning resources will be created only if the
993 --resource list is not centrally controlled
994 IF g_control_flag = 'N' THEN
995 create_planning_resources (
996 p_format_id => p_format_id,
997 p_resource_list_id => p_resource_list_id,
998 p_resource_class_id => p_resource_class_id,
999 x_return_status => x_return_status,
1000 x_msg_count => x_msg_count,
1001 x_msg_data => x_msg_data);
1002 END IF;
1003
1004 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1005 RETURN;
1006 END IF;
1007
1008 --Move mapped data into TMP4
1009 l_status := -1;
1010 l_status := process_mapped_txns (p_resource_class_id, p_format_id ) ;
1011
1012 IF l_status <> 0 THEN
1013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1014 RETURN;
1015 END IF;
1016
1017 --Move txns that did not map to format
1018 --between TMP1 and TMP2
1019 l_status := -1;
1020 l_status := process_txns_for_next_format ;
1021
1022 IF l_status <> 0 THEN
1023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1024 RETURN;
1025 END IF;
1026
1027 pa_debug.reset_err_stack;
1028
1029 END; --end procedure map_for_format
1030
1031
1032 /*--------------------------------------------------------------
1033 This API assumes that the temporary table pa_res_list_map_tmp
1034 has been populated with planning transactions that need to be
1035 mapped to a planning resource
1036 --------------------------------------------------------------*/
1037 PROCEDURE map_resource_list (
1038 p_resource_list_id IN NUMBER,
1039 p_project_id IN NUMBER,
1040 x_return_status OUT NOCOPY VARCHAR2,
1041 x_msg_count OUT NOCOPY NUMBER,
1042 x_msg_data OUT NOCOPY VARCHAR2
1043 ) IS
1044
1045 CURSOR resource_classes
1046 IS
1047 SELECT
1048 resource_class_id
1049 FROM
1050 pa_resource_classes_b;
1051
1052 l_res_list_formats SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1053 l_res_class_formats PA_RESOURCE_PREC_PUB.plan_res_formats;
1054 l_format NUMBER;
1055 l_eff_formats SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1056
1057 l_SQL_statement VARCHAR2 (2000);
1058
1059 l_run_number NUMBER := 0;
1060
1061 l_job_group_id NUMBER ; --added for bug#4027727
1062
1063 BEGIN
1064
1065 -- Initialize the return status to success
1066 x_return_status := FND_API.G_RET_STS_SUCCESS;
1067
1068 --Call function to initialize format precedence
1069 PA_RESOURCE_PREC_PUB.format_precedence_init();
1070
1071 --Update numeric identifiers for non-numeric resource types
1072 --update_resource_element_map;(Not required for resource list
1073 --mapping but is a must for RBS mapping)
1074
1075 --Get all formats for the resource list passed as parameter
1076 BEGIN
1077 SELECT RES_FORMAT_ID
1078 BULK COLLECT
1079 INTO l_res_list_formats
1080 FROM pa_plan_rl_formats
1081 WHERE RESOURCE_LIST_ID = p_resource_list_id;
1082 EXCEPTION
1083 WHEN NO_DATA_FOUND THEN
1084 NULL;
1085 END;
1086
1087 g_resource_list_id := p_resource_list_id;
1088
1089 g_project_id := p_project_id; /* bug#3576766 */
1090
1091 --Identify if this resource list allows new planning resource
1092 --creation or is it centrally controlled
1093 BEGIN
1094 SELECT CONTROL_FLAG
1095 INTO g_control_flag
1096 FROM pa_resource_lists_all_bg
1097 WHERE RESOURCE_LIST_ID = p_resource_list_id;
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND THEN
1100 NULL;
1101 END;
1102
1103
1104 --For every resource class the transactions have to be mapped
1105 --to the resource formats for the given resource list
1106 <<OUTER>>
1107 FOR res_class_rec IN resource_classes LOOP
1108
1109 --Get all resource tokens for a given effective format
1110 IF res_class_rec.resource_class_id = 1 THEN
1111 l_res_class_formats := PA_RESOURCE_PREC_PUB.g_people_formats;
1112 ELSIF res_class_rec.resource_class_id = 2 THEN
1113 l_res_class_formats := PA_RESOURCE_PREC_PUB.g_equipment_formats;
1114 ELSIF res_class_rec.resource_class_id = 3 THEN
1115 l_res_class_formats := PA_RESOURCE_PREC_PUB.g_material_formats;
1116 ELSIF res_class_rec.resource_class_id = 4 THEN
1117 l_res_class_formats := PA_RESOURCE_PREC_PUB.g_fin_element_formats;
1118 END IF;
1119
1120 --get all the effective formats for this resource class
1121 --filtered by the formats for the processed resource list
1122 FOR i IN 1..l_res_class_formats.COUNT LOOP
1123 FOR j IN 1..l_res_list_formats.COUNT LOOP
1124
1125 IF l_res_list_formats(j) = l_res_class_formats(i).res_format_id THEN
1126 l_eff_formats.EXTEND;
1127 l_eff_formats( l_eff_formats.COUNT ) := l_res_class_formats(i).eff_res_format_id;
1128 END IF;
1129
1130 END LOOP;--end j loop
1131 END LOOP; --end i loop
1132
1133 -- bug#3612772 and bug#3612591
1134 -- update resource attributes which can be derived but are not present
1135 /* split the below update into two to remove 9i dependencies
1136 UPDATE pa_res_list_map_tmp1 tmp1
1137 SET tmp1.EXPENDITURE_CATEGORY = nvl(tmp1.EXPENDITURE_CATEGORY, (SELECT typ.EXPENDITURE_CATEGORY
1138 FROM PA_EXPENDITURE_TYPES typ
1139 WHERE typ.EXPENDITURE_TYPE = tmp1.EXPENDITURE_TYPE ) )
1140 ,tmp1.ITEM_CATEGORY_ID = nvl(tmp1.ITEM_CATEGORY_ID, (SELECT cat.CATEGORY_ID
1141 FROM
1142 PA_RESOURCE_CLASSES_B classes,
1143 PA_PLAN_RES_DEFAULTS cls,
1144 MTL_ITEM_CATEGORIES cat
1145 WHERE
1146 classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS' and
1147 cls.RESOURCE_CLASS_ID = classes.RESOURCE_CLASS_ID and
1148 cls.ITEM_CATEGORY_SET_ID = cat.CATEGORY_SET_ID and
1149 cat.INVENTORY_ITEM_ID = tmp1.INVENTORY_ITEM_ID and
1150 cat.organization_id = tmp1.organization_id )
1151 ) ;
1152 */
1153 UPDATE pa_res_list_map_tmp1 tmp1
1154 SET tmp1.EXPENDITURE_CATEGORY = (SELECT typ.EXPENDITURE_CATEGORY
1155 FROM PA_EXPENDITURE_TYPES typ
1156 WHERE typ.EXPENDITURE_TYPE = tmp1.EXPENDITURE_TYPE )
1157 WHERE tmp1.EXPENDITURE_CATEGORY IS NULL;
1158 UPDATE pa_res_list_map_tmp1 tmp1
1159 SET tmp1.ITEM_CATEGORY_ID = (SELECT cat.CATEGORY_ID
1160 FROM
1161 PA_RESOURCE_CLASSES_B classes,
1162 PA_PLAN_RES_DEFAULTS cls,
1163 MTL_ITEM_CATEGORIES cat
1164 WHERE
1165 classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS' and
1166 cls.RESOURCE_CLASS_ID = classes.RESOURCE_CLASS_ID and
1167 cls.ITEM_CATEGORY_SET_ID = cat.CATEGORY_SET_ID and
1168 cat.INVENTORY_ITEM_ID = tmp1.INVENTORY_ITEM_ID and
1169 cat.organization_id = tmp1.organization_id )
1170 WHERE tmp1.ITEM_CATEGORY_ID IS NULL;
1171
1172 /* Added for bug 3653120 */
1173
1174 UPDATE pa_res_list_map_tmp1 tmp1
1175 SET tmp1.revenue_category = (SELECT evt.revenue_category_code
1176 FROM pa_event_types evt
1177 WHERE evt.event_type=tmp1.event_type)
1178 WHERE tmp1.revenue_category IS NULL
1179 AND tmp1.event_type IS NOT NULL;
1180
1181 UPDATE pa_res_list_map_tmp1 tmp1
1182 SET tmp1.revenue_category = (SELECT et.revenue_category_code
1183 FROM pa_expenditure_types et
1184 WHERE et.expenditure_type=tmp1.expenditure_type)
1185 WHERE tmp1.revenue_category IS NULL
1186 AND tmp1.expenditure_type IS NOT NULL;
1187
1188
1189 /* added for bug#4027727 */
1190 BEGIN
1191 SELECT job_group_id INTO l_job_group_id FROM pa_resource_lists_all_bg WHERE resource_list_id = p_resource_list_id ;
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND THEN
1194 NULL;
1195 END;
1196 IF l_job_group_id IS NOT NULL THEN
1197 UPDATE pa_res_list_map_tmp1 tmp1
1198 SET tmp1.job_id =
1199 (SELECT PA_Cross_Business_Grp.IsMappedToJob(tmp1.job_id, l_job_group_id) FROM DUAL)
1200 WHERE tmp1.job_id IS NOT NULL;
1201 END IF;
1202
1203 --Process mapping logic for every format (based on eff_res_format_id)
1204 --For a format there can be more than 1 effective formats
1205 --eg: Financial category breaks down into 4 formats
1206
1207 FOR i IN 1..l_eff_formats.COUNT LOOP
1208
1209 map_for_format(p_resource_class_id => res_class_rec.resource_class_id,
1210 p_format_id => l_eff_formats(i),
1211 p_resource_list_id => p_resource_list_id,
1212 x_return_status => x_return_status,
1213 x_msg_count => x_msg_count,
1214 x_msg_data => x_msg_data);
1215
1216 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1217 EXIT OUTER;
1218 END IF;
1219
1220
1221 IF NOT (txns_to_map_exists) THEN
1222 EXIT OUTER;
1223 END IF;
1224
1225 END LOOP; --end loop for sorted formats
1226
1227 l_eff_formats.DELETE; --cleanup collection for next class
1228
1229 END LOOP;--end cursor loop resource_classes
1230
1231
1232 x_msg_count := FND_MSG_PUB.Count_Msg;
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 x_msg_data :=SQLERRM();
1237
1238 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_RESOURCE_MAPPING'
1239 , p_procedure_name => 'MAP_RESOURCE_LIST');
1240
1241 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1242
1243 END; --end procedure map_resource_list
1244
1245
1246 /* Returns the format precedence for every resource class */
1247 PROCEDURE get_format_precedence (
1248 p_resource_class_id IN NUMBER,
1249 p_res_format_id IN NUMBER,
1250 x_format_precedence OUT NOCOPY /* file.sql.39 change */ NUMBER,
1251 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1252 x_msg_code OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
1253 IS
1254 l_res_class_formats PA_RESOURCE_PREC_PUB.plan_res_formats;
1255 BEGIN
1256 -- Initialize the return status to success
1257 x_return_status := FND_API.G_RET_STS_SUCCESS;
1258
1259 --Call function to initialize format precedence
1260 PA_RESOURCE_PREC_PUB.format_precedence_init();
1261
1262 IF p_resource_class_id = 1 THEN
1263 l_res_class_formats := PA_RESOURCE_PREC_PUB.g_people_formats;
1264 ELSIF p_resource_class_id = 2 THEN
1265 l_res_class_formats := PA_RESOURCE_PREC_PUB.g_equipment_formats;
1266 ELSIF p_resource_class_id = 3 THEN
1267 l_res_class_formats := PA_RESOURCE_PREC_PUB.g_material_formats;
1268 ELSIF p_resource_class_id = 4 THEN
1269 l_res_class_formats := PA_RESOURCE_PREC_PUB.g_fin_element_formats;
1270 END IF;
1271
1272 FOR i IN 1..l_res_class_formats.COUNT LOOP
1273
1274 IF p_res_format_id = l_res_class_formats(i).res_format_id THEN
1275 x_format_precedence := l_res_class_formats(i).eff_res_format_id;
1276 EXIT;
1277 END IF;
1278
1279 END LOOP;
1280
1281 END;
1282
1283 END; --end package pa_resource_mapping