[Home] [Help]
1: PACKAGE BODY jtf_rs_dbi_conc_pub AS
2: /* $Header: jtfrsdbb.pls 120.0 2005/05/11 08:19:47 appldev 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.
109: BIS_COLLECTION_UTILITIES.Debug('Error in Update Field Service District Hierarchy:'||errbuf);
110: END populate_fld_srv_district;
111:
112: /****************************************************************************
113: This is main procedure to populate the data in JTF_RS_DBI_MGR_GROUPS
114: and JTF_RS_DBI_DENORM_RES_GROUPS table so that it can be accessed via view
115: JTF_RS_DBI_RES_GRP_VL for usage 'SALES' (Sales Group Hierarchy) and
116: 'FLD_SRV_DISTRICT' (Field Service District Hierarchy) in DBI product.
117:
230: ||': Degree of parallelism='||bis_common_parameters.get_degree_of_parallelism
231: ||': Partition= '||l_partition);
232:
233: -- Truncate Table Partitions
234: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS TRUNCATE PARTITION '||l_partition ;
235: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS TRUNCATE PARTITION '||l_partition;
236:
237: -- Make indexes unusable
238: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS MODIFY PARTITION '||l_partition||' UNUSABLE LOCAL INDEXES ';
234: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS TRUNCATE PARTITION '||l_partition ;
235: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS TRUNCATE PARTITION '||l_partition;
236:
237: -- Make indexes unusable
238: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS MODIFY PARTITION '||l_partition||' UNUSABLE LOCAL INDEXES ';
239: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS MODIFY PARTITION '||l_partition||' UNUSABLE LOCAL INDEXES ';
240:
241: -- change session parameter so that data can be inserted in partition containing unusable index
242: EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE ' ;
247: -- INSERT top manager groups in intermediate table
248: -- INSERT star groups (Top manager groups)
249: -- INSERT top manager groups (first time login manager groups) in main table
250: INSERT ALL
251: INTO /*+ APPEND PARALLEL(jtf_rs_dbi_mgr_groups) NOLOGGING */ jtf_rs_dbi_mgr_groups
252: (dbi_mgr_id, resource_id, user_id, group_id,
253: creation_date, created_by
254: ,last_update_date, last_updated_by, usage )
255: VALUES (jtf_rs_dbi_mgr_groups_s.nextval, resource_id, user_id, group_id
251: INTO /*+ APPEND PARALLEL(jtf_rs_dbi_mgr_groups) NOLOGGING */ jtf_rs_dbi_mgr_groups
252: (dbi_mgr_id, resource_id, user_id, group_id,
253: creation_date, created_by
254: ,last_update_date, last_updated_by, usage )
255: VALUES (jtf_rs_dbi_mgr_groups_s.nextval, resource_id, user_id, group_id
256: , l_sysdate, l_user_id
257: , l_sysdate, l_user_id, usage)
258: INTO /*+ APPEND PARALLEL(jtf_rs_dbi_denorm_res_groups) NOLOGGING */ jtf_rs_dbi_denorm_res_groups
259: (VALUE, id , current_id ,
321: l_insert_count := l_insert_count + SQL%ROWCOUNT ;
322:
323: COMMIT;
324:
325: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS '||
326: ' MODIFY PARTITION '||l_partition||' REBUILD UNUSABLE LOCAL INDEXES ';
327:
328: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_MGR_GROUPS',
329: percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism ,granularity=>'ALL',cascade=>TRUE);
324:
325: EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS '||
326: ' MODIFY PARTITION '||l_partition||' REBUILD UNUSABLE LOCAL INDEXES ';
327:
328: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_MGR_GROUPS',
329: percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism ,granularity=>'ALL',cascade=>TRUE);
330:
331: -- first time login child groups
332: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
359: ,'N' mem_flag , 'A' mem_status
360: ,l_sysdate, l_user_id
361: ,DECODE(d1.active_flag,'Y','Y','N')
362: ,l_sysdate, l_user_id, usg.usage
363: FROM jtf_rs_groups_denorm d1 , jtf_rs_dbi_mgr_groups n1
364: , jtf_rs_group_usages usg
365: WHERE n1.group_id = d1.actual_parent_id
366: AND d1.denorm_level = 1
367: AND d1.latest_relationship_flag = 'Y'
406: ,l_sysdate, l_user_id
407: ,DECODE(d1.active_flag,'Y','Y','N')
408: ,l_sysdate, l_user_id, usg.usage
409: FROM jtf_rs_groups_denorm d1 , jtf_rs_groups_denorm d2,
410: jtf_rs_dbi_mgr_groups n1
411: ,jtf_rs_group_usages usg
412: WHERE n1.group_id = d1.parent_group_id
413: AND d1.group_id = d2.actual_parent_id
414: AND n1.group_id = d2.parent_group_id
455: ,'N' mem_flag , 'A' mem_status
456: ,l_sysdate, l_user_id
457: ,DECODE(d1.active_flag,'Y','Y','N')
458: ,l_sysdate, l_user_id, usg.usage
459: FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
460: ,jtf_rs_group_usages usg
461: WHERE n1.group_id = d1.parent_group_id
462: AND d1.latest_relationship_flag = 'Y'
463: AND d1.group_id = usg.group_id
500: ,'N' mem_flag , 'A' mem_status
501: ,l_sysdate, l_user_id
502: ,DECODE(d1.active_flag,'Y','Y','N')
503: ,l_sysdate, l_user_id, usg.usage
504: FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
505: ,jtf_rs_group_usages usg
506: WHERE n1.group_id = d1.parent_group_id
507: AND d1.denorm_level > 0
508: AND d1.latest_relationship_flag = 'Y'