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