eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PA_RESOURCE_MAPPING

Source


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