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.

APPS.JTF_RS_DEFAULT_GROUPS_CONC_PUB dependencies on JTF_RS_DEFAULT_GROUPS

Line 1: PACKAGE BODY jtf_rs_default_groups_conc_pub AS

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,

Line 5: This is a concurrent program to populate the data in JTF_RS_DEFAULT_GROUPS_INT,

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.

Line 6: JTF_RS_DEFAULT_GROUPS_STAGE and JTF_RS_DEFAULT_GROUPS. This program will create

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:

Line 9: there will be only one primary group for a resource in JTF_RS_DEFAULT_GROUPS.

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

Line 15: Introduced new staging table JTF_RS_DEFAULT_GROUPS_STAGE

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

Line 17: JTF_RS_DEFAULT_GROUPS table.

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

Line 85: FROM JTF_RS_DEFAULT_GROUPS_INT

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

Line 90: 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:

Line 170: WHERE TABLE_NAME = 'JTF_RS_DEFAULT_GROUPS_INT'

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' ;

Line 171: AND index_name = 'JTF_RS_DEFAULT_GROUPS_INT_N1'

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:

Line 191: l_stage := 'Stage=DROP_INDEX '||l_index_owner||'.JTF_RS_DEFAULT_GROUPS_INT_N1';

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;

Line 194: EXECUTE IMMEDIATE 'DROP INDEX '||l_index_owner||'.JTF_RS_DEFAULT_GROUPS_INT_N1';

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';

Line 198: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_index_owner||'.JTF_RS_DEFAULT_GROUPS_INT';

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';

Line 202: l_stage := 'Stage=INSERT_IN_JTF_RS_DEFAULT_GROUPS_INT_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,

Line 204: INSERT INTO /+ APPEND PARALLEL(sg1) NOLOGGING / JTF_RS_DEFAULT_GROUPS_int sg1

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 )

Line 209: SELECT JTF_RS_DEFAULT_GROUPS_STAGE_s.NEXTVAL default_groups_id,

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,

Line 273: l_stage := 'Stage=INSERT_IN_JTF_RS_DEFAULT_GROUPS_INT_TABLE_FOR_UNASSIGNED_GROUPS';

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,

Line 275: INSERT INTO /+ APPEND PARALLEL(sg1) NOLOGGING / JTF_RS_DEFAULT_GROUPS_INT sg1

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 )

Line 280: SELECT JTF_RS_DEFAULT_GROUPS_STAGE_s.NEXTVAL default_groups_id,

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,

Line 317: l_stage := 'Stage=CREATE_INDEX_ON_JTF_RS_DEFAULT_GROUPS_INT_TABLE';

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 '

Line 320: ||'.JTF_RS_DEFAULT_GROUPS_INT_N1 ON '

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||' ) ';

Line 321: ||l_table_owner||'.JTF_RS_DEFAULT_GROUPS_INT '

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:

Line 327: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DEFAULT_GROUPS_INT',

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:

Line 332: l_stage := 'Stage=TRUNCATE_JTF_RS_DEFAULT_GROUPS_STAGE_TABLE';

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;

Line 334: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_index_owner||'.JTF_RS_DEFAULT_GROUPS_STAGE';

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 --------

Line 557: INSERT INTO /+ APPEND PARALLEL(sg1) NOLOGGING / JTF_RS_DEFAULT_GROUPS_STAGE sg1

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,

Line 561: VALUES (JTF_RS_DEFAULT_GROUPS_STAGE_S.NEXTVAL, g_default_grp_tab(o).p_resource_id,

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);

Line 578: l_stage := 'Stage=ANALYZING_JTF_RS_DEFAULT_GROUPS_STAGE_TABLE';

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:

Line 580: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DEFAULT_GROUPS_STAGE',

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)

Line 583: -- Now compare data in recently refreshed table JTF_RS_DEFAULT_GROUPS_STAGE (new) with

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

Line 584: -- previously populated table JTF_RS_DEFAULT_GROUPS (old) (final incremental table)

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)

Line 586: -- If there is some data in JTF_RS_DEFAULT_GROUPS (old data) which does not exist in

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';

Line 587: -- recently refreshed table JTF_RS_DEFAULT_GROUPS_STAGE, it means it is STALE data and

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:

Line 588: -- has to be DELETED from JTF_RS_DEFAULT_GROUPS (final table)

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

Line 590: l_stage := 'Stage=DELETE_DATA_FROM_JTF_RS_DEFAULT_GROUPS_TABLE';

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: (

Line 592: DELETE FROM jtf_rs_default_groups

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

Line 596: FROM jtf_rs_default_groups

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: ) ;

Line 599: FROM jtf_rs_default_groups_stage

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:

Line 604: -- If there is some data in recently refreshed table 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';

Line 605: -- (new data) which does not exist in final table JTF_RS_DEFAULT_GROUPS_STAGE, it

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:

Line 606: -- means it is NEW data and has to be INSERTED in JTF_RS_DEFAULT_GROUPS (final table)

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

Line 608: l_stage := 'Stage=INSERT_DATA_IN_JTF_RS_DEFAULT_GROUPS_TABLE';

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,

Line 610: INSERT INTO /+ APPEND PARALLEL(sg1) NOLOGGING / jtf_rs_default_groups sg1

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

Line 615: jtf_rs_default_groups_s.NEXTVAL, resource_id, user_id, resource_number,

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

Line 620: FROM jtf_rs_default_groups_stage

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: );

Line 623: FROM jtf_rs_default_groups

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:

Line 629: l_stage := 'Stage=ANALYZING_JTF_RS_DEFAULT_GROUPS_TABLE';

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:

Line 631: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DEFAULT_GROUPS',

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';

Line 651: only if data is populated in jtf_rs_default_groups table for that usage.

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:

Line 672: FROM jtf_rs_default_groups

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:

Line 685: END jtf_rs_default_groups_conc_pub; -- end package body

681: RETURN -1;
682:
683: END get_default_group;
684:
685: END jtf_rs_default_groups_conc_pub; -- end package body