[Home] [Help]
1: PACKAGE BODY jtf_rs_dbi_conc_pub AS
2: /* $Header: jtfrsdbb.pls 120.2 2011/08/15 22:07:41 nsinghai noship $ */
3:
4: /****************************************************************************
5: This is a concurrent program to populate the data in JTF_RS_DBI_MGR_GROUPS
6: and JTF_RS_DBI_DENORM_RES_GROUPS
7: table so that it can be accessed via view JTF_RS_DBI_RES_GRP_VL for Sales
8: Group Hierarchy in DBI product. This program is exclusively built for DBI
9: product and is NOT included in mainline code of ATG Resource Manager.
111: BIS_COLLECTION_UTILITIES.Debug('Error in Update Field Service District Hierarchy:'||errbuf);
112: END populate_fld_srv_district;
113:
114: /****************************************************************************
115: This is main procedure to populate the data in JTF_RS_DBI_MGR_GROUPS
116: and JTF_RS_DBI_DENORM_RES_GROUPS table so that it can be accessed via view
117: JTF_RS_DBI_RES_GRP_VL for usage 'SALES' (Sales Group Hierarchy) and
118: 'FLD_SRV_DISTRICT' (Field Service District Hierarchy) in DBI product.
119:
237: ||': Degree of parallelism='||bis_common_parameters.get_degree_of_parallelism
238: ||': Partition= '||l_partition);
239:
240: -- Truncate Table Partitions
241: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS TRUNCATE PARTITION '||l_partition ;
242: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS TRUNCATE PARTITION '||l_partition;
243:
244: -- Make indexes unusable
245: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS MODIFY PARTITION '||l_partition||' UNUSABLE LOCAL INDEXES ';
241: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS TRUNCATE PARTITION '||l_partition ;
242: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS TRUNCATE PARTITION '||l_partition;
243:
244: -- Make indexes unusable
245: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS MODIFY PARTITION '||l_partition||' UNUSABLE LOCAL INDEXES ';
246: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS MODIFY PARTITION '||l_partition||' UNUSABLE LOCAL INDEXES ';
247:
248: -- change session parameter so that data can be inserted in partition containing unusable index
249: EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE ' ;
254: -- INSERT top manager groups in intermediate table
255: -- INSERT star groups (Top manager groups)
256: -- INSERT top manager groups (first time login manager groups) in main table
257: INSERT ALL
258: INTO /*+ APPEND PARALLEL(jtf_rs_dbi_mgr_groups) NOLOGGING */ jtf_rs_dbi_mgr_groups
259: (dbi_mgr_id, resource_id, user_id, group_id,
260: creation_date, created_by
261: ,last_update_date, last_updated_by, usage )
262: VALUES (jtf_rs_dbi_mgr_groups_s.nextval, resource_id, user_id, group_id
258: INTO /*+ APPEND PARALLEL(jtf_rs_dbi_mgr_groups) NOLOGGING */ jtf_rs_dbi_mgr_groups
259: (dbi_mgr_id, resource_id, user_id, group_id,
260: creation_date, created_by
261: ,last_update_date, last_updated_by, usage )
262: VALUES (jtf_rs_dbi_mgr_groups_s.nextval, resource_id, user_id, group_id
263: , l_sysdate, l_user_id
264: , l_sysdate, l_user_id, usage)
265: INTO /*+ APPEND PARALLEL(jtf_rs_dbi_denorm_res_groups) NOLOGGING */ jtf_rs_dbi_denorm_res_groups
266: (VALUE, id , current_id ,
328: l_insert_count := l_insert_count + SQL%ROWCOUNT ;
329:
330: COMMIT;
331:
332: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS '||
333: ' MODIFY PARTITION '||l_partition||' REBUILD UNUSABLE LOCAL INDEXES ';
334:
335: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_MGR_GROUPS',
336: percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism ,granularity=>'ALL',cascade=>TRUE);
331:
332: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS '||
333: ' MODIFY PARTITION '||l_partition||' REBUILD UNUSABLE LOCAL INDEXES ';
334:
335: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_MGR_GROUPS',
336: percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism ,granularity=>'ALL',cascade=>TRUE);
337:
338: -- first time login child groups
339: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
366: ,'N' mem_flag , 'A' mem_status
367: ,l_sysdate, l_user_id
368: ,DECODE(d1.active_flag,'Y','Y','N')
369: ,l_sysdate, l_user_id, usg.usage
370: FROM jtf_rs_groups_denorm d1 , jtf_rs_dbi_mgr_groups n1
371: , jtf_rs_group_usages usg
372: WHERE n1.group_id = d1.actual_parent_id
373: AND d1.denorm_level = 1
374: AND d1.latest_relationship_flag = 'Y'
413: ,l_sysdate, l_user_id
414: ,DECODE(d1.active_flag,'Y','Y','N')
415: ,l_sysdate, l_user_id, usg.usage
416: FROM jtf_rs_groups_denorm d1 , jtf_rs_groups_denorm d2,
417: jtf_rs_dbi_mgr_groups n1
418: ,jtf_rs_group_usages usg
419: WHERE n1.group_id = d1.parent_group_id
420: AND d1.group_id = d2.actual_parent_id
421: AND n1.group_id = d2.parent_group_id
462: ,'N' mem_flag , 'A' mem_status
463: ,l_sysdate, l_user_id
464: ,DECODE(d1.active_flag,'Y','Y','N')
465: ,l_sysdate, l_user_id, usg.usage
466: FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
467: ,jtf_rs_group_usages usg
468: WHERE n1.group_id = d1.parent_group_id
469: AND d1.latest_relationship_flag = 'Y'
470: AND d1.group_id = usg.group_id
507: ,'N' mem_flag , 'A' mem_status
508: ,l_sysdate, l_user_id
509: ,DECODE(d1.active_flag,'Y','Y','N')
510: ,l_sysdate, l_user_id, usg.usage
511: FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
512: ,jtf_rs_group_usages usg
513: WHERE n1.group_id = d1.parent_group_id
514: AND d1.denorm_level > 0
515: AND d1.latest_relationship_flag = 'Y'