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