[Home] [Help]
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
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:
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
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
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;
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' ;
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:
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';
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';
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';
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,
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 )
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,
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,
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 )
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,
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 '
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||' ) ';
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:
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';
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;
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 --------
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,
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);
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:
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
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
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)
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';
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:
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
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: (
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
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: ) ;
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:
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';
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:
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
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,
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
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
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: );
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:
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:
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:
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