1 PACKAGE BODY jtf_rs_default_groups_conc_pub AS
2 /* $Header: jtfrsbcb.pls 120.0 2005/05/11 08:19:15 appldev noship $ */
3
4 /****************************************************************************
5 This is a concurrent program to populate the data in JTF_RS_DEFAULT_GROUPS_INT,
6 JTF_RS_DEFAULT_GROUPS_STAGE and JTF_RS_DEFAULT_GROUPS. This program will create
7 primary groups for resources based on usage and rules (specified by product
8 teams) for date date range from 01/01/1900 to 12/31/4712. For a specific date
9 there will be only one primary group for a resource in JTF_RS_DEFAULT_GROUPS.
10
11 Currently, it is being used for only Field Service Application.
12
13 CREATED BY nsinghai 20-JUL-2004
14 MODIFIED BY nsinghai 20-SEP-2004 Made JTF_RS_DEFUALT_GROUPS incremental.
15 Introduced new staging table JTF_RS_DEFAULT_GROUPS_STAGE
16 Only Delta Records will be populated in
17 JTF_RS_DEFAULT_GROUPS table.
18 nsinghai 27-SEP-2004 Bug 3917477 : Defaulting to -1 group
19 should be the last consideration. If any other group
20 is present, even if it is at lower level, it should be
21 defaulted to. Modified First insert stmt to
22 exclude group with group_id = -1
23 ***************************************************************************/
24
25 /*****************************************************************************
26 This procedure will populate default groups for Field Service District (usage:
27 'FLD_SRV_DISTRICT') through concurrent program "Update Primary Districts for
28 Field Service Engineers".
29
30 Created By nsinghai 07/21/2004
31 *****************************************************************************/
32
33 PROCEDURE populate_fs_district
34 (ERRBUF OUT NOCOPY VARCHAR2,
35 RETCODE OUT NOCOPY VARCHAR2)
36 IS
37
38 TYPE default_grp_type IS RECORD
39 (p_resource_id NUMBER,
40 p_user_id NUMBER,
41 p_resource_number VARCHAR2(60),
42 p_group_id NUMBER,
43 p_start_date DATE,
44 p_end_date DATE
45 );
46
47 TYPE default_grp_tbl IS TABLE OF default_grp_type INDEX BY BINARY_INTEGER;
48
49 g_default_grp_tab default_grp_tbl;
50 temp_default_grp_tab default_grp_tbl;
51
52 l_inner_loop VARCHAR2(10) ;
53
54 i INTEGER := 0;
55 j INTEGER := 0;
56 k INTEGER := 0;
57 l INTEGER := 0;
58 m INTEGER := 0;
59 n INTEGER := 0;
60 o INTEGER := 0;
61 p INTEGER := 0;
62 q INTEGER := 0;
63
64 l_user_id NUMBER ;
65 l_sysdate DATE ;
66 l_status VARCHAR2(30);
67 l_index_owner VARCHAR2(240);
68 l_table_owner VARCHAR2(240);
69 l_index_tblspace VARCHAR2(240);
70 l_index_exists VARCHAR2(10);
71 l_prev_start_date DATE;
72 l_prev_end_date DATE;
73 l_skip_row VARCHAR2(10);
74 l_overlap VARCHAR2(10);
75 l_deletion_occured VARCHAR2(10);
76 l_usage VARCHAR2(100);
77 l_stage VARCHAR2(300);
78
79 l_jtfu varchar2(240);
80 l_jtfx varchar2(240);
81 l_jtft varchar2(240);
82
83 CURSOR c_outer IS
84 SELECT DISTINCT resource_id
85 FROM JTF_RS_DEFAULT_GROUPS_INT
86 ;
87
88 CURSOR c1 (ll_resource_id NUMBER)IS
89 SELECT resource_id, user_id, resource_number, group_id, start_date, end_date
90 FROM JTF_RS_DEFAULT_GROUPS_INT
91 WHERE resource_id = ll_resource_id
92 AND start_date <= end_date
93 ORDER BY denorm_count ASC, role_type_priority ASC, role_priority ASC, START_DATE desc;
94
95 CURSOR c_product_info IS
96 SELECT i.tablespace, i.index_tablespace, u.oracle_username
97 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
98 WHERE a.application_short_name = 'JTF'
99 AND a.application_id = i.application_id
100 AND u.oracle_id = i.oracle_id;
101
102 FUNCTION f_get_degree_of_parallelism RETURN NUMBER IS
103 l_parallel NUMBER;
104 BEGIN
105 l_parallel := null;
106 -- EDW : Degree of Parallelism-Source
107 l_parallel := null;
108 l_parallel := floor(fnd_profile.value('EDW_PARALLEL_SRC')); -- gets value of profile option
109
110 /* Set by the customer, return this value */
111
112 IF (l_parallel IS NOT NULL and l_parallel > 0) THEN
113 return l_parallel;
114 END IF;
115
116 /* Not set by customer, so query v$pq_sysstat */
117
118 BEGIN
119 SELECT value INTO l_parallel
120 FROM v$pq_sysstat WHERE trim(statistic) = 'Servers Idle';
121 EXCEPTION WHEN no_data_found THEN
122 l_parallel := 1;
123 END;
124
125 IF (l_parallel IS NULL) THEN
126 l_parallel:=1;
127 END IF;
128
129 l_parallel := floor(l_parallel/2);
130 IF (l_parallel = 0) THEN
131 l_parallel := 1;
132 END IF;
133
134 RETURN l_parallel;
135 END f_get_degree_of_parallelism;
136
137 BEGIN
138
139 -- putting initialization variable here because of GSCC warning File.Sql.35
140
141 l_stage := 'Stage=Initialize';
142
143 l_inner_loop := 'N';
144 l_user_id := fnd_global.user_id;
145 l_sysdate := SYSDATE;
146 l_skip_row := 'N' ;
147 l_overlap := 'N' ;
148 l_deletion_occured := 'N' ;
149 l_usage := 'FLD_SRV_DISTRICT'; -- currently not being used. For future, when more usages use this table.
150
151 retcode := '0' ;
152
153 --EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
154 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
155
156 l_stage := 'Stage=FETCH_SCHEMA_DETAILS';
157
158 --fetch user name for JTF product
159 OPEN c_product_info;
160 FETCH c_product_info INTO l_jtft,l_jtfx,l_jtfu;
161 CLOSE c_product_info;
162
163 -------- Check owner name for the tables and indexes.
164 -- keep index information so that it is easy to create them back
165 -- Check owner name for the tables and indexes.
166 BEGIN
167 SELECT owner, table_owner, tablespace_name
168 INTO l_index_owner, l_table_owner, l_index_tblspace
169 FROM ALL_INDEXES
170 WHERE TABLE_NAME = 'JTF_RS_DEFAULT_GROUPS_INT'
171 AND index_name = 'JTF_RS_DEFAULT_GROUPS_INT_N1'
172 AND table_owner= l_jtfu;
173
174 l_index_exists := 'Y' ;
175
176 EXCEPTION WHEN OTHERS THEN
177
178 l_index_exists := 'N' ;
179
180 -- Check some ther index which will definitly exist
181 SELECT owner, table_owner, tablespace_name
182 INTO l_index_owner, l_table_owner, l_index_tblspace
183 FROM ALL_INDEXES
184 WHERE TABLE_NAME = 'JTF_RS_GROUPS_DENORM'
185 AND index_name = 'JTF_RS_GROUPS_DENORM_U1'
186 AND table_owner= l_jtfu;
187 END;
188
189
190 --------- Drop Index before inserting into intermediate table ----------
191 l_stage := 'Stage=DROP_INDEX '||l_index_owner||'.JTF_RS_DEFAULT_GROUPS_INT_N1';
192
193 IF (l_index_exists = 'Y') THEN
194 EXECUTE IMMEDIATE 'DROP INDEX '||l_index_owner||'.JTF_RS_DEFAULT_GROUPS_INT_N1';
195 END IF;
196
197 -------------Insert into intermediate table ---------
198 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_index_owner||'.JTF_RS_DEFAULT_GROUPS_INT';
199
200 COMMIT;
201
202 l_stage := 'Stage=INSERT_IN_JTF_RS_DEFAULT_GROUPS_INT_TABLE';
203
204 INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ JTF_RS_DEFAULT_GROUPS_int sg1
205 (default_groups_id, resource_id, user_id, resource_number,
206 group_id, role_type_code, role_id, role_type_priority,
207 role_priority, start_date, end_date, denorm_count, usage,
208 created_by, creation_date, last_updated_by, last_update_date )
209 SELECT JTF_RS_DEFAULT_GROUPS_STAGE_s.NEXTVAL default_groups_id,
210 x.resource_id, x.user_id, x.resource_number,
211 x.group_id,
212 x.role_type_code, x.role_id, x.role_type_priority, x.role_priority,
213 x.start_date, x.end_date, x.denorm_count, 'FLD_SRV_DISTRICT' usage,
214 l_user_id, sysdate, l_user_id, sysdate
215 FROM (
216 SELECT /*+ use_hash(rrl mem usg rol den res) PARALLEL(rrl)
217 PARALLEL(mem) PARALLEL(usg) PARALLEL(rol) PARALLEL(den)
218 PARALLEL(res) */
219 mem.resource_id
220 ,mem.group_id
221 ,res.user_id
222 ,res.resource_number
223 ,DECODE(rol.role_type_code,'CSF_REPRESENTATIVE',1,'CSF_DEBRIEF',2,
224 'CSF_DEBRIEF_REVIEWAGENT',3,'CSF_DISPATCHER',4,'CSF_PLANNER',5,
225 'CSF_DBI_DISTRICT',6,7
226 ) role_type_priority
227 ,DECODE('Y',rol.member_flag,1,rol.manager_flag,2) role_priority
228 ,rrl.start_date_active start_date
229 ,nvl(rrl.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR')) end_date
230 ,rrl.role_id
231 ,rol.role_type_code
232 ,COUNT(den.group_id) denorm_count
233 FROM jtf_rs_role_relations rrl
234 ,jtf_rs_group_members mem
235 ,jtf_rs_group_usages usg
236 ,jtf_rs_roles_b rol
237 ,jtf_rs_groups_denorm den
238 ,jtf_rs_resource_extns res
239 WHERE rrl.role_resource_type = 'RS_GROUP_MEMBER'
240 AND rrl.delete_flag = 'N'
241 AND rrl.role_resource_id = mem.group_member_id
242 AND mem.delete_flag = 'N'
243 AND mem.group_id <> -1
244 AND mem.group_id = usg.group_id
245 AND usg.usage = 'FLD_SRV_DISTRICT'
246 AND rrl.role_id = rol.role_id
247 AND rol.role_type_code IN ('CSF_REPRESENTATIVE','CSF_DEBRIEF',
248 'CSF_DEBRIEF_REVIEWAGENT','CSF_DISPATCHER','CSF_PLANNER','CSF_DBI_DISTRICT')
249 AND 'Y' IN (rol.member_flag, rol.manager_flag)
250 AND mem.resource_id = res.resource_id
251 AND mem.group_id = den.group_id
252 AND ( rrl.start_date_active BETWEEN den.start_date_active AND
253 nvl(den.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
254 OR
255 den.start_date_active BETWEEN rrl.start_date_active AND
256 nvl(rrl.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
257 )
258 GROUP BY mem.resource_id, res.user_id, res.resource_number,
259 mem.group_id, rrl.start_date_active, rrl.end_date_active,
260 DECODE(rol.role_type_code,'CSF_REPRESENTATIVE',1,'CSF_DEBRIEF',2,
261 'CSF_DEBRIEF_REVIEWAGENT',3,'CSF_DISPATCHER',4,'CSF_PLANNER',5,
262 'CSF_DBI_DISTRICT',6,7),
263 DECODE('Y',rol.member_flag,1,rol.manager_flag,2)
264 ,rrl.role_id, rol.role_type_code
265 ) x ;
266
267 COMMIT;
268
269 /* Insert all the rest of the Field Service Resources who do not have any group
270 assign to them along with those who have groups assigned for limited time
271 */
272
273 l_stage := 'Stage=INSERT_IN_JTF_RS_DEFAULT_GROUPS_INT_TABLE_FOR_UNASSIGNED_GROUPS';
274
275 INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ JTF_RS_DEFAULT_GROUPS_INT sg1
276 (default_groups_id, resource_id, user_id, resource_number,
277 group_id, role_type_code, role_id, role_type_priority,
278 role_priority, start_date, end_date, denorm_count, usage,
279 created_by, creation_date, last_updated_by, last_update_date )
280 SELECT JTF_RS_DEFAULT_GROUPS_STAGE_s.NEXTVAL default_groups_id,
281 x.resource_id, x.user_id, x.resource_number,
282 x.group_id,
283 x.role_type_code, x.role_id, x.role_type_priority, x.role_priority,
284 x.start_date, x.end_date, x.denorm_count, 'FLD_SRV_DISTRICT' usage,
285 l_user_id, sysdate, l_user_id, sysdate
286 FROM (
287 SELECT /*+ use_hash(rrl rol res) PARALLEL(rrl) PARALLEL(rol) PARALLEL(res)*/
288 DISTINCT
289 res.resource_id,
290 res.user_id,
291 res.resource_number,
292 -1 group_id,
293 'NONE' role_type_code,
294 -1 role_id,
295 99 role_type_priority,
296 99 role_priority,
297 TO_DATE('01/01/1900','MM/DD/RRRR') START_DATE,
298 TO_DATE('12/31/4712','MM/DD/RRRR') END_DATE,
299 99999999 denorm_count
300 FROM jtf_rs_role_relations rrl
301 ,jtf_rs_roles_b rol
302 ,jtf_rs_resource_extns res
303 WHERE rrl.role_resource_type = 'RS_INDIVIDUAL'
304 AND rrl.delete_flag = 'N'
305 AND rrl.role_id = rol.role_id
306 AND rol.role_type_code IN ('CSF_REPRESENTATIVE','CSF_DEBRIEF',
307 'CSF_DEBRIEF_REVIEWAGENT','CSF_DISPATCHER',
308 'CSF_PLANNER','CSF_DBI_DISTRICT')
309 AND rrl.role_resource_id = res.resource_id
310 ) x;
311
312 COMMIT;
313
314
315 -------------Create index and Analyze table on Intermediate table -------
316
317 l_stage := 'Stage=CREATE_INDEX_ON_JTF_RS_DEFAULT_GROUPS_INT_TABLE';
318
319 EXECUTE IMMEDIATE 'CREATE INDEX '||l_index_owner
320 ||'.JTF_RS_DEFAULT_GROUPS_INT_N1 ON '
321 ||l_table_owner||'.JTF_RS_DEFAULT_GROUPS_INT '
322 ||' (resource_id, denorm_count, role_type_priority, role_priority, start_date desc) '
323 ||' TABLESPACE '||l_index_tblspace
324 ||' NOLOGGING PARALLEL (DEGREE '||f_get_degree_of_parallelism||' ) ';
325
326
327 fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DEFAULT_GROUPS_INT',
328 percent=>5, degree=>f_get_degree_of_parallelism, granularity=>'ALL',cascade=>TRUE);
329
330 -------------Truncate the Staging table ---------
331
332 l_stage := 'Stage=TRUNCATE_JTF_RS_DEFAULT_GROUPS_STAGE_TABLE';
333
334 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_index_owner||'.JTF_RS_DEFAULT_GROUPS_STAGE';
335
336 COMMIT;
337
338 ------------ Start the main logic here --------
339 l_stage := 'Stage=START_PLSQL_POPULATION_LOGIC';
340
341 FOR c_get_resource_id IN c_outer
342 LOOP
343
344 l_stage := 'Stage=INSIDE_PLSQL_OUTER_LOOP_FOR RESOURCE_ID : '||c_get_resource_id.resource_id;
345
346 -- re-initialize i and dates
347 i := 0;
348 l_prev_start_date := NULL;
349 l_prev_end_date := NULL;
350 ---------------------main for loop -----------------
351 FOR c_rec IN c1 (c_get_resource_id.resource_id)
352 LOOP
353
354 l_stage := 'Stage=INSIDE_PLSQL_INNER_LOOP_FOR RESOURCE_ID : '||c_get_resource_id.resource_id;
355
356 -- we should procees further only if the date range is different
357 -- this will help in eliminating few rows and improve performance
358 IF (l_prev_start_date IS NULL) THEN
359 l_skip_row := 'N';
360 l_prev_start_date := c_rec.START_DATE;
361 l_prev_end_date := c_rec.END_DATE;
362 ELSIF
363 ((c_rec.START_DATE BETWEEN l_prev_start_date AND l_prev_end_date) AND
364 (c_rec.END_DATE BETWEEN l_prev_start_date AND l_prev_end_date)) THEN
365 l_skip_row := 'Y';
366 ELSE
367 l_skip_row := 'N';
368 l_prev_start_date := c_rec.START_DATE;
369 l_prev_end_date := c_rec.END_DATE;
370 END IF;
371
372 -------------------If i=0------------------------
373 -- proceed only if l_skip_row = N
374 IF (l_skip_row = 'N') THEN
375 IF (i = 0) THEN
376 g_default_grp_tab(i).p_resource_id := c_rec.resource_id;
377 g_default_grp_tab(i).p_user_id := c_rec.user_id;
378 g_default_grp_tab(i).p_resource_number := c_rec.resource_number;
379 g_default_grp_tab(i).p_group_id := c_rec.group_id;
380 g_default_grp_tab(i).p_start_date := c_rec.start_date;
381 g_default_grp_tab(i).p_end_date := c_rec.end_date;
382 -------------------else of If i=0------------------------
383 ELSE
384 -- loop through the plsql table to check the dates of each record
385 ------------------For outer table looop-----------------------
386 l_inner_loop := 'N';
387 l_overlap := 'N';
388 l_deletion_occured := 'N' ;
389 j := g_default_grp_tab.FIRST;
390 WHILE j IS NOT NULL
391 LOOP
392 -- if data existed in inner table
393 IF (temp_default_grp_tab.COUNT > 0) THEN
394 l_inner_loop := 'Y' ;
395 l := temp_default_grp_tab.FIRST ;
396 WHILE l IS NOT NULL
397 LOOP
398 IF ((temp_default_grp_tab(l).p_start_date < g_default_grp_tab(j).p_start_date) AND
399 (temp_default_grp_tab(l).p_end_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date))
400 THEN
401 temp_default_grp_tab(l).p_end_date := g_default_grp_tab(j).p_start_date -1;
402 ELSIF
403 ((temp_default_grp_tab(l).p_start_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date)
404 AND
405 (temp_default_grp_tab(l).p_end_date > g_default_grp_tab(j).p_end_date)) THEN
406 temp_default_grp_tab(l).p_start_date := g_default_grp_tab(j).p_end_date +1;
407 ELSIF
408 ((temp_default_grp_tab(l).p_start_date < g_default_grp_tab(j).p_start_date) AND
409 (temp_default_grp_tab(l).p_end_date > g_default_grp_tab(j).p_end_date)) THEN
410 -- first get temp_default_grp_tab(l).p_end_date into new record
411 -- and then modify the end date in existing record
412 m := temp_default_grp_tab.LAST + 1;
413 --insert the 2nd record for this breakup
414 temp_default_grp_tab(m).p_resource_id := temp_default_grp_tab(l).p_resource_id;
415 temp_default_grp_tab(m).p_user_id := temp_default_grp_tab(l).p_user_id;
416 temp_default_grp_tab(m).p_resource_number := temp_default_grp_tab(l).p_resource_number;
417 temp_default_grp_tab(m).p_group_id := temp_default_grp_tab(l).p_group_id;
418 temp_default_grp_tab(m).p_start_date := g_default_grp_tab(j).p_end_date +1;
419 temp_default_grp_tab(m).p_end_date := temp_default_grp_tab(l).p_end_date;
420
421 -- modify existing record
422 temp_default_grp_tab(l).p_end_date := g_default_grp_tab(j).p_start_date -1;
423 ELSIF
424 ((temp_default_grp_tab(l).p_start_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date)
425 AND
426 (temp_default_grp_tab(l).p_end_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date))
427 THEN
428 -- delete this row from the inner table
429 temp_default_grp_tab.DELETE(l);
430 l_deletion_occured := 'Y' ;
431 END IF; -- end of inner table date checks
432
433 l := temp_default_grp_tab.NEXT(l);
434 END LOOP;
435 ELSE
436 l_inner_loop := 'N' ;
437 END IF;
438
439 -- do not go further if the inner loop record is deleted and now
440 -- we do not have any row inside that inner that inner table
441 -- i.e. further comparison is useless and we should not insert that record.
442 IF ((temp_default_grp_tab.COUNT = 0) AND (l_deletion_occured = 'Y')) THEN
443 -- falsely set the variable so that it does not go inside at all
444 l_inner_loop := 'Y';
445 EXIT ;
446 END IF;
447
448 -- if no row was there in inner table, continue comparing dates with
449 -- cursor dates
450 IF (l_inner_loop = 'N') THEN
451 -- check if date overlaps
452 IF ((g_default_grp_tab(j).p_start_date BETWEEN c_rec.start_date AND c_rec.end_date)
453 OR (c_rec.start_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date))
454 THEN
455 -- go deep
456 --intialize the inner table counter
457 l_overlap := 'Y';
458 k := 0;
459 IF ((c_rec.start_date < g_default_grp_tab(j).p_start_date) AND
460 (c_rec.end_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date)) THEN
461 temp_default_grp_tab(k).p_resource_id := c_rec.resource_id;
462 temp_default_grp_tab(k).p_user_id := c_rec.user_id;
463 temp_default_grp_tab(k).p_resource_number := c_rec.resource_number;
464 temp_default_grp_tab(k).p_group_id := c_rec.group_id;
465 temp_default_grp_tab(k).p_start_date := c_rec.start_date;
466 temp_default_grp_tab(k).p_end_date := g_default_grp_tab(j).p_start_date -1;
467 ELSIF
468 ((c_rec.start_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date) AND
469 (c_rec.end_date > g_default_grp_tab(j).p_end_date)) THEN
470 temp_default_grp_tab(k).p_resource_id := c_rec.resource_id;
471 temp_default_grp_tab(k).p_user_id := c_rec.user_id;
472 temp_default_grp_tab(k).p_resource_number := c_rec.resource_number;
473 temp_default_grp_tab(k).p_group_id := c_rec.group_id;
474 temp_default_grp_tab(k).p_start_date := g_default_grp_tab(j).p_end_date +1;
475 temp_default_grp_tab(k).p_end_date := c_rec.end_date;
476 ELSIF
477 ((c_rec.start_date < g_default_grp_tab(j).p_start_date) AND
478 (c_rec.end_date > g_default_grp_tab(j).p_end_date)) THEN
479 temp_default_grp_tab(k).p_resource_id := c_rec.resource_id;
480 temp_default_grp_tab(k).p_user_id := c_rec.user_id;
481 temp_default_grp_tab(k).p_resource_number := c_rec.resource_number;
482 temp_default_grp_tab(k).p_group_id := c_rec.group_id;
483 temp_default_grp_tab(k).p_start_date := c_rec.start_date;
484 temp_default_grp_tab(k).p_end_date := g_default_grp_tab(j).p_start_date -1;
485
486 k := k + 1;
487 temp_default_grp_tab(k).p_resource_id := c_rec.resource_id;
488 temp_default_grp_tab(k).p_user_id := c_rec.user_id;
489 temp_default_grp_tab(k).p_resource_number := c_rec.resource_number;
490 temp_default_grp_tab(k).p_group_id := c_rec.group_id;
491 temp_default_grp_tab(k).p_start_date := g_default_grp_tab(j).p_end_date +1;
492 temp_default_grp_tab(k).p_end_date := c_rec.end_date;
493 ELSIF
494 ((c_rec.start_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date) AND
495 (c_rec.end_date BETWEEN g_default_grp_tab(j).p_start_date AND g_default_grp_tab(j).p_end_date)) THEN
496 -- break from loop because we do not want to check further
497 -- for this condition
498 EXIT;
499 END IF; -- end of outer table date checks
500
501 ELSE -- doesn't overlap with any one
502 l_overlap := 'N';
503 END IF;
504
505 END IF; -- l_inner_loop = 'N' check
506 -------------------End of outer table loop------------------------
507 j := g_default_grp_tab.NEXT(j);
508 END LOOP;
509
510 -- If there was no overlap with any previous group, insert it
511 IF (l_overlap = 'N') THEN
512 g_default_grp_tab(i).p_resource_id := c_rec.resource_id;
513 g_default_grp_tab(i).p_user_id := c_rec.user_id;
514 g_default_grp_tab(i).p_resource_number := c_rec.resource_number;
515 g_default_grp_tab(i).p_group_id := c_rec.group_id;
516 g_default_grp_tab(i).p_start_date := c_rec.start_date;
517 g_default_grp_tab(i).p_end_date := c_rec.end_date;
518 END IF;
519
520 -- move data from inner table to outer table and then delete inner table.
521 IF (temp_default_grp_tab.COUNT > 0) THEN
522 n := g_default_grp_tab.LAST;
523 p := temp_default_grp_tab.FIRST;
524
525 WHILE p IS NOT NULL
526 LOOP
527 -- do only if start_date is before or eqal to end date
528 IF (temp_default_grp_tab(p).p_start_date <= temp_default_grp_tab(p).p_end_date) THEN
529 n := n + 1;
530 g_default_grp_tab(n).p_resource_id := temp_default_grp_tab(p).p_resource_id;
531 g_default_grp_tab(n).p_user_id := temp_default_grp_tab(p).p_user_id;
532 g_default_grp_tab(n).p_resource_number := temp_default_grp_tab(p).p_resource_number;
533 g_default_grp_tab(n).p_group_id := temp_default_grp_tab(p).p_group_id;
534 g_default_grp_tab(n).p_start_date := temp_default_grp_tab(p).p_start_date;
535 g_default_grp_tab(n).p_end_date := temp_default_grp_tab(p).p_end_date;
536 END IF;
537 p := temp_default_grp_tab.NEXT(p);
538 END LOOP;
539
540 temp_default_grp_tab.DELETE;
541 END IF;
542 -------------------If i=0------------------------
543 END IF;
544 --------------End If l_skip_row = N check---------
545 END IF;
546
547 i := i + 1;
548 ---------------------main for loop -----------------
549 END LOOP;
550
551 IF (g_default_grp_tab.COUNT > 0) THEN
552
553 o := g_default_grp_tab.FIRST ;
554
555 WHILE o IS NOT NULL
556 LOOP
557 INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ JTF_RS_DEFAULT_GROUPS_STAGE sg1
558 (default_groups_id, resource_id, user_id, resource_number,
559 group_id, start_date, end_date, usage,
560 created_by, creation_date, last_updated_by, last_update_date )
561 VALUES (JTF_RS_DEFAULT_GROUPS_STAGE_S.NEXTVAL, g_default_grp_tab(o).p_resource_id,
562 g_default_grp_tab(o).p_user_id, g_default_grp_tab(o).p_resource_number,
563 g_default_grp_tab(o).p_group_id, g_default_grp_tab(o).p_start_date,
564 g_default_grp_tab(o).p_end_date, l_usage,
565 l_user_id, sysdate, l_user_id, sysdate);
566
567 o := g_default_grp_tab.NEXT(o);
568 END LOOP;
569
570 -- delete the table for next resource id
571 g_default_grp_tab.DELETE;
572 END IF;
573 COMMIT;
574
575 END LOOP; -- end of c_outer cursor
576
577 -- analyze the Main table
578 l_stage := 'Stage=ANALYZING_JTF_RS_DEFAULT_GROUPS_STAGE_TABLE';
579
580 fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DEFAULT_GROUPS_STAGE',
581 percent=>5, degree=>f_get_degree_of_parallelism, granularity=>'ALL',cascade=>TRUE);
582
583 -- Now compare data in recently refreshed table JTF_RS_DEFAULT_GROUPS_STAGE (new) with
584 -- previously populated table JTF_RS_DEFAULT_GROUPS (old) (final incremental table)
585
586 -- If there is some data in JTF_RS_DEFAULT_GROUPS (old data) which does not exist in
587 -- recently refreshed table JTF_RS_DEFAULT_GROUPS_STAGE, it means it is STALE data and
588 -- has to be DELETED from JTF_RS_DEFAULT_GROUPS (final table)
589
590 l_stage := 'Stage=DELETE_DATA_FROM_JTF_RS_DEFAULT_GROUPS_TABLE';
591
592 DELETE FROM jtf_rs_default_groups
593 WHERE (resource_id, NVL(user_id,-84), group_id, START_DATE, end_date, usage) IN
594 (
595 SELECT resource_id, NVL(user_id,-84), group_id, START_DATE, end_date, usage
596 FROM jtf_rs_default_groups
597 MINUS
598 SELECT resource_id, NVL(user_id,-84), group_id, START_DATE, end_date, usage
599 FROM jtf_rs_default_groups_stage
600 ) ;
601
602 COMMIT;
603
604 -- If there is some data in recently refreshed table JTF_RS_DEFAULT_GROUPS_STAGE
605 -- (new data) which does not exist in final table JTF_RS_DEFAULT_GROUPS_STAGE, it
606 -- means it is NEW data and has to be INSERTED in JTF_RS_DEFAULT_GROUPS (final table)
607
608 l_stage := 'Stage=INSERT_DATA_IN_JTF_RS_DEFAULT_GROUPS_TABLE';
609
610 INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ jtf_rs_default_groups sg1
611 (default_groups_id, resource_id, user_id, resource_number,
612 group_id, start_date, end_date, usage,
613 created_by, creation_date, last_updated_by, last_update_date )
614 SELECT
615 jtf_rs_default_groups_s.NEXTVAL, resource_id, user_id, resource_number,
616 group_id, start_date, end_date, usage,
617 l_user_id created_by, SYSDATE creation_date, l_user_id last_updated_by, SYSDATE
618 FROM (
619 SELECT resource_id, user_id, resource_number, group_id, START_DATE, end_date, usage
620 FROM jtf_rs_default_groups_stage
621 MINUS
622 SELECT resource_id, user_id, resource_number, group_id, START_DATE, end_date, usage
623 FROM jtf_rs_default_groups
624 );
625
626 COMMIT;
627
628 -- analyze Staging table
629 l_stage := 'Stage=ANALYZING_JTF_RS_DEFAULT_GROUPS_TABLE';
630
631 fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DEFAULT_GROUPS',
632 percent=>5, degree=>f_get_degree_of_parallelism, granularity=>'ALL',cascade=>TRUE);
633
634 EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';
635 --EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=FALSE';
636
637 l_stage := 'Stage=Completed';
638 errbuf := l_stage;
639
640 EXCEPTION WHEN OTHERS THEN
641 fnd_file.put_line(fnd_file.log, l_stage);
642 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
643 retcode := '2'; -- Error
644 errbuf := l_stage||' : ERROR : '||sqlerrm;
645 --dbms_output.put_line(l_stage||': Error : '||SQLERRM);
646 END populate_fs_district; -- end procedure
647
648 /****************************************************************************
649 This Function is used to fetch default group for specific usage given a
650 resource_id, date and usage as input parameter. This function will fetch data
651 only if data is populated in jtf_rs_default_groups table for that usage.
652
653 Created By nsinghai 07/21/2004
654 Modified By
655
656 *****************************************************************************/
657
658 FUNCTION get_default_group
659 (p_resource_id IN NUMBER,
660 p_usage IN VARCHAR2,
661 p_date IN DATE
662 ) RETURN NUMBER
663 IS
664 l_date DATE ;
665 l_group_id NUMBER;
666
667 BEGIN
668 l_date := TRUNC(NVL(p_date, SYSDATE));
669
670 SELECT group_id
671 INTO l_group_id
672 FROM jtf_rs_default_groups
673 WHERE resource_id = p_resource_id
674 AND usage = p_usage
675 AND l_date BETWEEN start_date AND end_date;
676
677 RETURN l_group_id;
678
679 -- if too_many_rows or no_data_found then return -1
680 EXCEPTION WHEN OTHERS THEN
681 RETURN -1;
682
683 END get_default_group;
684
685 END jtf_rs_default_groups_conc_pub; -- end package body
[Home] [Help]