[Home] [Help]
PACKAGE BODY: APPS.MSC_ATP_REFRESH_MVIEW
Source
1 PACKAGE BODY MSC_ATP_REFRESH_MVIEW AS
2 /* $Header: MSCATMVB.pls 120.6 2011/11/08 12:19:38 vjuluri ship $ */
3
4 -- rajjain 12/20/2002
5 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
6
7 PROCEDURE REFRESH_MVIEW(
8 ERRBUF OUT NoCopy VARCHAR2,
9 RETCODE OUT NoCopy NUMBER)
10 IS
11 TYPE char40_arr IS TABLE OF varchar2(40);
12 TYPE number_arr IS TABLE OF number;
13
14 i NUMBER := 1;
15 j NUMBER := 1;
16 l_applsys_schema varchar2(10);
17 l_msc_schema VARCHAR2(30);
18 l_apps_schema VARCHAR2(30); --Bug#12964070
19 l_retval BOOLEAN;
20 dummy1 varchar2(10);
21 dummy2 varchar2(10);
22 sql_stmt1 varchar2(30000);
23 sql_stmt2 varchar2(30000);
24 l_part_name char40_arr;
25 l_cur_table char40_arr;
26 l_temp_table char40_arr;
27 l_tbspace char40_arr;
28 l_min_instance_id NUMBER := 0;
29 l_max_instance_id NUMBER := 31;
30 --l_sub_part number_arr;
31
32 -- bug 2383867 : krajan
33 -- stores value of MSC_CAP_ALLOCATION profile option
34 l_msc_cap_allocation varchar2(1);
35 -- rajjain 12/20/2002
36 l_spid VARCHAR2(12);
37 --5053818
38 l_item_hier_init_extent number;
39 l_item_hier_next_extent number;
40 l_item_hier_pct_inc number;
41 l_item_hier_indx_init_extent number;
42 l_item_hier_indx_nxt_extent number;
43 l_item_hier_indx_pct_inc number;
44
45 l_res_hier_init_extent number;
46 l_res_hier_next_extent number;
47 l_res_hier_pct_inc number;
48 l_res_hier_indx_init_extent number;
49 l_res_hier_indx_nxt_extent number;
50 l_res_hier_indx_pct_inc number;
51
52 BEGIN
53 -- Bug 3304390 Disable Trace
54 -- Deleted related code
55
56 msc_util.msc_log('Begin REFRESH_MVIEW');
57 l_retval := FND_INSTALLATION.GET_APP_INFO('FND', dummy1, dummy2, l_applsys_schema);
58 /*
59 SELECT a.oracle_username
60 INTO l_msc_schema
61 FROM FND_ORACLE_USERID a,
62 FND_PRODUCT_INSTALLATIONS b
63 WHERE a.oracle_id = b.oracle_id
64 AND b.application_id = 724;
65 */
66 --Bug#12964070
67 select oracle_username
68 into l_apps_schema
69 from fnd_oracle_userid
70 where read_only_flag = 'U';
71
72
73 -- bug 2383867 : krajan
74 l_msc_cap_allocation := NVL(FND_PROFILE.VALUE('MSC_CAP_ALLOCATION'), 'Y');
75 msc_util.msc_log('Profile Option value' || l_msc_cap_allocation);
76
77 msc_util.msc_log('Before getting partitions name');
78 IF (l_msc_cap_allocation = 'Y') THEN
79
80 msc_util.msc_log('AATP Profile Option Set. Getting everything.');
81 SELECT table_name, partition_name, partition_name || '_TEMP',
82 --subpartition_count,
83 tablespace_name
84 BULK COLLECT
85 INTO l_cur_table, l_part_name, l_temp_table,
86 --l_sub_part,
87 l_tbspace
88 --bug 2495962: Change refrence from dba_xxx to all_xxx tables
89 --FROM dba_tab_partitions
90 FROM all_tab_partitions
91 WHERE table_owner = l_apps_schema
92 AND table_name IN ('MSC_ITEM_HIERARCHY_MV', 'MSC_RESOURCE_HIERARCHY_MV')
93 ORDER BY table_name, partition_name;
94
95 -- :In Case of Huge data in MSC_ITEM_HIERARCHY_MV and MSC_RESOURCE_HIERARCHY_MV --5053818
96 -- need to set proper Initial extent and next extent value for customers
97 msc_util.msc_log('AATP Profile Option Set. Getting everything.');
98
99 BEGIN
100 SELECT nvl(ITEM_HIER_INIT_EXTENT,40),
101 nvl(ITEM_HIER_NEXT_EXTENT,5),
102 nvl(ITEM_HIER_PCT_INCREASE,0),
103 nvl(ITEM_HIER_INDX_INIT_EXTENT,40),
104 nvl(ITEM_HIER_INDX_NEXT_EXTENT,2),
105 nvl(ITEM_HIER_INDX_PCT_INCREASE,0),
106 nvl(RES_HIER_INIT_EXTENT,40),
107 nvl(RES_HIER_NEXT_EXTENT,5),
108 nvl(RES_HIER_PCT_INCREASE,0),
109 nvl(RES_HIER_INDX_INIT_EXTENT,40),
110 nvl(RES_HIER_INDX_NEXT_EXTENT,2),
111 nvl(RES_HIER_INDX_PCT_INCREASE,0)
112 INTO l_item_hier_init_extent,l_item_hier_next_extent,l_item_hier_pct_inc,
113 l_item_hier_indx_init_extent,l_item_hier_indx_nxt_extent,l_item_hier_indx_pct_inc,
114 l_res_hier_init_extent,l_res_hier_next_extent,l_res_hier_pct_inc,l_res_hier_indx_init_extent,
115 l_res_hier_indx_nxt_extent,l_res_hier_indx_pct_inc
116 from msc_atp_parameters
117 WHERE rownum = 1;
118 EXCEPTION
119 WHEN no_data_found THEN
120 l_item_hier_init_extent := 40;
121 l_item_hier_next_extent :=5;
122 l_item_hier_pct_inc :=0;
123 l_item_hier_indx_init_extent :=40;
124 l_item_hier_indx_nxt_extent :=2;
125 l_item_hier_indx_pct_inc :=0;
126 l_res_hier_init_extent := 40;
127 l_res_hier_next_extent :=5;
128 l_res_hier_pct_inc :=0;
129 l_res_hier_indx_init_extent := 40;
130 l_res_hier_indx_nxt_extent :=2;
131 l_res_hier_indx_pct_inc :=0;
132 END;
133
134 ELSE
135 msc_util.msc_log('AATP Profile Option set to NO. Not getting Resource Hierarchy.');
136 SELECT table_name, partition_name, partition_name || '_TEMP',
137 --subpartition_count,
138 tablespace_name
139 BULK COLLECT
140 INTO l_cur_table, l_part_name, l_temp_table,
141 --l_sub_part,
142 l_tbspace
143 --bug 2495962: Change refrence from dba_xxx to all_xxx tables
144 --FROM dba_tab_partitions
145 FROM all_tab_partitions
146 WHERE table_owner = l_apps_schema
147 AND table_name = 'MSC_ITEM_HIERARCHY_MV'
148 ORDER BY partition_name;
149
150 -- :In Case of Huge data in MSC_ITEM_HIERARCHY_MV and MSC_RESOURCE_HIERARCHY_MV --5053818
151 -- need to set proper Initial extent and next extent value for customers
152 msc_util.msc_log('AATP Profile Option Set. Getting everything.');
153
154 BEGIN
155 SELECT NVL(ITEM_HIER_INIT_EXTENT,40),
156 nvl(ITEM_HIER_NEXT_EXTENT,5),
157 nvl(ITEM_HIER_PCT_INCREASE,0),
158 nvl(ITEM_HIER_INDX_INIT_EXTENT,40),
159 nvl(ITEM_HIER_INDX_NEXT_EXTENT,2),
160 nvl(ITEM_HIER_INDX_PCT_INCREASE,0)
161 INTO l_item_hier_init_extent,l_item_hier_next_extent,l_item_hier_pct_inc,
162 l_item_hier_indx_init_extent,l_item_hier_indx_nxt_extent,l_item_hier_indx_pct_inc
163 from msc_atp_parameters
164 WHERE rownum = 1;
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 l_item_hier_init_extent := 40;
168 l_item_hier_next_extent :=5;
169 l_item_hier_pct_inc :=0;
170 l_item_hier_indx_init_extent :=40;
171 l_item_hier_indx_nxt_extent :=2;
172 l_item_hier_indx_pct_inc :=0;
173 END;
174
175 END IF;
176 -- End bug 2383867 changes : krajan
177
178 i := l_part_name.FIRST;
179
180 msc_util.msc_log('Before Create Temp Table Loop');
181 WHILE i IS NOT NULL LOOP
182
183 IF i > 1 THEN
184 IF l_cur_table(i) <> l_cur_table(i-1) THEN
185 j := 2;
186 l_min_instance_id := 0;
187 l_max_instance_id := 31;
188 END IF;
189 END IF;
190
191 -- Bug 1852008, modified materialized view definition to remove subpartitions
192 -- Also, modified INITIAL extent sizes for MV and indexes to be 40K instead of 2M
193 -- and NEXT for MV to be 5M and for indexes to be 2M.
194 -- This would reduce the space needed to create these MV.
195 --
196 IF j = 1 THEN
197 --5053818
198 sql_stmt1 := 'create table ' || l_temp_table(i) ||
199 ' PCTFREE 0 STORAGE(INITIAL '||l_item_hier_init_extent||'K NEXT '||l_item_hier_next_extent||'M PCTINCREASE '||l_item_hier_pct_inc||')' ||
200 ' TABLESPACE ' || l_tbspace(i) ||
201 --' NOLOGGING' ||
202 --' PARTITION BY HASH (INVENTORY_ITEM_ID) PARTITIONS ' || to_char(l_sub_part(i))||
203 ' AS ' ||
204 'SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
205 mi.organization_id ORGANIZATION_ID,
206 mi.sr_instance_id SR_INSTANCE_ID,
207 ma.demand_class DEMAND_CLASS,
208 maa.allocation_rule_name ALLOCATION_RULE_NAME,
209 maa.assignment_type ASSIGNMENT_TYPE,
210 ma.allocation_percent ALLOCATION_PERCENT,
211 martp.effective_date EFFECTIVE_DATE,
212 martp.disable_date DISABLE_DATE,
213 ma.priority PRIORITY,
214 ma.service_level SERVICE_LEVEL,
215 martp.time_phase_id TIME_PHASE_ID,
216 ma.class CLASS,
217 ma.partner_id PARTNER_ID,
218 ma.partner_site_id PARTNER_SITE_ID,
219 ma.level_id LEVEL_ID,
220 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
221 ma.level_priority LEVEL_PRIORITY,
222 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
223 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
224 FROM msc_allocations ma,
225 msc_alloc_rule_time_phases martp,
226 msc_system_items mi,
227 msc_allocation_assignments maa
228 WHERE maa.assignment_type = 7
229 AND maa.inventory_item_id = mi.inventory_item_id
230 AND maa.organization_id = mi.organization_id
231 AND maa.sr_instance_id = mi.sr_instance_id
232 AND maa.allocation_rule_name = martp.allocation_rule_name
233 AND martp.time_phase_id = ma.time_phase_id
234 AND mi.plan_id = -1
235 AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
236 ' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
237 ' UNION ALL
238 SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
239 mi.organization_id ORGANIZATION_ID,
240 mi.sr_instance_id SR_INSTANCE_ID,
241 ma.demand_class DEMAND_CLASS,
242 maa.allocation_rule_name ALLOCATION_RULE_NAME,
243 maa.assignment_type ASSIGNMENT_TYPE,
244 ma.allocation_percent ALLOCATION_PERCENT,
245 martp.effective_date EFFECTIVE_DATE,
246 martp.disable_date DISABLE_DATE,
247 ma.priority PRIORITY,
248 ma.service_level SERVICE_LEVEL,
249 martp.time_phase_id TIME_PHASE_ID,
250 ma.class CLASS,
251 ma.partner_id PARTNER_ID,
252 ma.partner_site_id PARTNER_SITE_ID,
253 ma.level_id LEVEL_ID,
254 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
255 ma.level_priority LEVEL_PRIORITY,
256 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
257 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
258 FROM msc_allocations ma,
259 msc_alloc_rule_time_phases martp,
260 msc_system_items mi,
261 msc_allocation_assignments maa
262 WHERE maa.assignment_type = 3
263 AND maa.inventory_item_id = mi.inventory_item_id
264 AND maa.allocation_rule_name = martp.allocation_rule_name
265 AND martp.time_phase_id = ma.time_phase_id
266 AND mi.plan_id = -1
267 AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
268 ' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
269 ' AND NOT EXISTS (
270 SELECT maa7.inventory_item_id
271 FROM msc_allocation_assignments maa7
272 WHERE maa7.inventory_item_id = mi.inventory_item_id
273 AND maa7.organization_id = mi.organization_id
274 AND maa7.sr_instance_id = mi.sr_instance_id
275 AND maa7.assignment_type = 7 )
276 UNION ALL
277 SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
278 mi.organization_id ORGANIZATION_ID,
279 mi.sr_instance_id SR_INSTANCE_ID,
280 ma.demand_class DEMAND_CLASS,
281 maa.allocation_rule_name ALLOCATION_RULE_NAME,
282 maa.assignment_type ASSIGNMENT_TYPE,
283 ma.allocation_percent ALLOCATION_PERCENT,
284 martp.effective_date EFFECTIVE_DATE,
285 martp.disable_date DISABLE_DATE,
286 ma.priority PRIORITY,
287 ma.service_level SERVICE_LEVEL,
288 martp.time_phase_id TIME_PHASE_ID,
289 ma.class CLASS,
290 ma.partner_id PARTNER_ID,
291 ma.partner_site_id PARTNER_SITE_ID,
292 ma.level_id LEVEL_ID,
293 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
294 ma.level_priority LEVEL_PRIORITY,
295 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
296 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
297 FROM msc_allocations ma,
298 msc_alloc_rule_time_phases martp,
299 msc_allocation_assignments maa,
300 msc_system_items mi,
301 msc_item_categories mic
302 WHERE maa.assignment_type = 2
303 AND maa.category_set_id = mic.category_set_id
304 AND maa.category_name = mic.category_name
305 AND maa.allocation_rule_name = martp.allocation_rule_name
306 AND martp.time_phase_id = ma.time_phase_id
307 AND mic.inventory_item_id = mi.inventory_item_id
308 AND mic.organization_id = mi.organization_id
309 AND mic.sr_instance_id = mi.sr_instance_id
310 AND mi.plan_id = -1
311 AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
312 ' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
313 ' AND NOT EXISTS (
314 SELECT maa1.category_set_id
315 FROM msc_allocation_assignments maa1
316 WHERE maa1.inventory_item_id = mi.inventory_item_id
317 AND maa1.assignment_type = 3)
318 AND NOT EXISTS (
319 SELECT maa7.inventory_item_id
320 FROM msc_allocation_assignments maa7
321 WHERE maa7.inventory_item_id = mi.inventory_item_id
322 AND maa7.organization_id = mi.organization_id
323 AND maa7.sr_instance_id = mi.sr_instance_id
324 AND maa7.assignment_type = 7 )
325 UNION ALL
326 SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
327 mi.organization_id ORGANIZATION_ID,
328 mi.sr_instance_id SR_INSTANCE_ID,
329 ma.demand_class DEMAND_CLASS,
330 maa.allocation_rule_name ALLOCATION_RULE_NAME,
331 maa.assignment_type ASSIGNMENT_TYPE,
332 ma.allocation_percent ALLOCATION_PERCENT,
333 martp.effective_date EFFECTIVE_DATE,
334 martp.disable_date DISABLE_DATE,
335 ma.priority PRIORITY,
336 ma.service_level SERVICE_LEVEL,
337 martp.time_phase_id TIME_PHASE_ID,
338 ma.class CLASS,
339 ma.partner_id PARTNER_ID,
340 ma.partner_site_id PARTNER_SITE_ID,
341 ma.level_id LEVEL_ID,
342 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
343 ma.level_priority LEVEL_PRIORITY,
344 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
345 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
346 FROM msc_allocations ma,
347 msc_alloc_rule_time_phases martp,
348 msc_allocation_assignments maa,
349 msc_system_items mi
350 WHERE maa.assignment_type = 6
351 AND maa.allocation_rule_name = martp.allocation_rule_name
352 AND martp.time_phase_id = ma.time_phase_id
353 AND mi.organization_id = maa.organization_id
354 AND mi.sr_instance_id = maa.sr_instance_id
355 AND mi.plan_id = -1
356 AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
357 ' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
358 ' AND NOT EXISTS (
359 SELECT /*+ leading(maa1) */ maa1.inventory_item_id
360 FROM msc_allocation_assignments maa1,
361 msc_item_categories mic
362 WHERE maa1.category_set_id = mic.category_set_id
363 AND maa1.category_name = mic.category_name
364 AND mi.inventory_item_id = mic.inventory_item_id
365 AND mi.organization_id = mic.organization_id
366 AND mi.sr_instance_id = mic.sr_instance_id
367 AND maa1.assignment_type = 2)
368 AND NOT EXISTS (
369 SELECT maa2.inventory_item_id
370 FROM msc_allocation_assignments maa2
371 WHERE maa2.inventory_item_id = mi.inventory_item_id
372 AND maa2.assignment_type = 3)
373 AND NOT EXISTS (
374 SELECT maa7.inventory_item_id
375 FROM msc_allocation_assignments maa7
376 WHERE maa7.inventory_item_id = mi.inventory_item_id
377 AND maa7.organization_id = mi.organization_id
378 AND maa7.sr_instance_id = mi.sr_instance_id
379 AND maa7.assignment_type = 7 )
380 UNION ALL
381 SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
382 mi.organization_id ORGANIZATION_ID,
383 mi.sr_instance_id SR_INSTANCE_ID,
384 ma.demand_class DEMAND_CLASS,
385 maa.allocation_rule_name ALLOCATION_RULE_NAME,
386 maa.assignment_type ASSIGNMENT_TYPE,
387 ma.allocation_percent ALLOCATION_PERCENT,
388 martp.effective_date EFFECTIVE_DATE,
389 martp.disable_date DISABLE_DATE,
390 ma.priority PRIORITY,
391 ma.service_level SERVICE_LEVEL,
392 martp.time_phase_id TIME_PHASE_ID,
393 ma.class CLASS,
394 ma.partner_id PARTNER_ID,
395 ma.partner_site_id PARTNER_SITE_ID,
396 ma.level_id LEVEL_ID,
397 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
398 ma.level_priority LEVEL_PRIORITY,
399 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
400 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
401 FROM msc_allocations ma,
402 msc_system_items mi,
403 msc_alloc_rule_time_phases martp,
404 msc_allocation_assignments maa
405 WHERE maa.assignment_type = 1
406 AND maa.allocation_rule_name = martp.allocation_rule_name
407 AND martp.time_phase_id = ma.time_phase_id
408 AND mi.plan_id = -1
409 AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
410 ' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
411 ' AND NOT EXISTS (
412 SELECT /*+ leading(maa1) */ maa1.inventory_item_id
413 FROM msc_allocation_assignments maa1,
414 msc_item_categories mic
415 WHERE maa1.category_set_id = mic.category_set_id
416 AND maa1.category_name = mic.category_name
417 AND mi.inventory_item_id = mic.inventory_item_id
418 AND mi.organization_id = mic.organization_id
419 AND mi.sr_instance_id = mic.sr_instance_id
420 AND maa1.assignment_type = 2)
421 AND NOT EXISTS (
422 SELECT maa2.inventory_item_id
423 FROM msc_allocation_assignments maa2
424 WHERE maa2.inventory_item_id = mi.inventory_item_id
425 AND maa2.assignment_type = 3)
426 AND NOT EXISTS (
427 SELECT maa3.inventory_item_id
428 FROM msc_allocation_assignments maa3
429 WHERE maa3.organization_id = mi.organization_id
430 AND maa3.sr_instance_id = mi.sr_instance_id
431 AND maa3.assignment_type = 6)
432 AND NOT EXISTS (
433 SELECT maa7.inventory_item_id
434 FROM msc_allocation_assignments maa7
435 WHERE maa7.inventory_item_id = mi.inventory_item_id
436 AND maa7.organization_id = mi.organization_id
437 AND maa7.sr_instance_id = mi.sr_instance_id
438 AND maa7.assignment_type = 7 )';
439
440 sql_stmt2 := 'create index ' || l_temp_table(i) || '_N1 on ' ||
441 l_temp_table(i) || '
442 --NOLOGGING
443 --5053818
444 (inventory_item_id, organization_id, sr_instance_id, demand_class)
445 storage(INITIAL '||l_item_hier_indx_init_extent||'K NEXT '||l_item_hier_indx_nxt_extent||'M PCTINCREASE '||l_item_hier_indx_pct_inc||') tablespace ' || l_tbspace(i);
446 --storage(INITIAL 40K NEXT 2M PCTINCREASE 0) LOCAL tablespace ' || l_tbspace(i);
447
448 ELSIF j = 2 THEN
449
450 sql_stmt1 := 'create table ' || l_temp_table(i) ||
451 ' PCTFREE 0 STORAGE(INITIAL '||l_res_hier_init_extent||'K NEXT '||l_res_hier_next_extent||'M PCTINCREASE '||l_res_hier_pct_inc||')' ||
452 ' TABLESPACE ' || l_tbspace(i) ||
453 --' NOLOGGING' ||
454 --' PARTITION BY HASH (DEPARTMENT_ID) PARTITIONS ' || to_char(l_sub_part(i))||
455 ' AS ' ||
456 'SELECT maa.resource_id RESOURCE_ID,
457 mdr.department_id DEPARTMENT_ID,
458 mdr.organization_id ORGANIZATION_ID,
459 mdr.sr_instance_id SR_INSTANCE_ID,
460 ma.demand_class DEMAND_CLASS,
461 maa.allocation_rule_name ALLOCATION_RULE_NAME,
462 maa.assignment_type ASSIGNMENT_TYPE,
463 ma.allocation_percent ALLOCATION_PERCENT,
464 martp.effective_date EFFECTIVE_DATE,
465 martp.disable_date DISABLE_DATE,
466 ma.priority PRIORITY,
467 ma.service_level SERVICE_LEVEL,
468 martp.time_phase_id TIME_PHASE_ID,
469 ma.class CLASS,
470 ma.partner_id PARTNER_ID,
471 ma.partner_site_id PARTNER_SITE_ID,
472 ma.level_id LEVEL_ID,
473 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
474 ma.level_priority LEVEL_PRIORITY,
475 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
476 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
477 FROM msc_allocations ma,
478 msc_alloc_rule_time_phases martp,
479 msc_allocation_assignments maa,
480 msc_department_resources mdr
481 WHERE maa.assignment_type = 4
482 AND maa.allocation_rule_name = martp.allocation_rule_name
483 AND martp.time_phase_id = ma.time_phase_id
484 AND maa.resource_id = mdr.resource_id
485 AND maa.department_id = mdr.department_id
486 AND maa.organization_id = mdr.organization_id
487 AND maa.sr_instance_id = mdr.sr_instance_id
488 AND maa.resource_group IS NULL
489 AND mdr.plan_id = -1
490 AND mdr.sr_instance_id >= ' || to_char(l_min_instance_id) ||
491 ' AND mdr.sr_instance_id < ' || to_char(l_max_instance_id) ||
492 ' UNION ALL
493 SELECT mdr.resource_id RESOURCE_ID,
494 mdr.department_id DEPARTMENT_ID,
495 mdr.organization_id ORGANIZATION_ID,
496 mdr.sr_instance_id SR_INSTANCE_ID,
497 ma.demand_class DEMAND_CLASS,
498 maa.allocation_rule_name ALLOCATION_RULE_NAME,
499 maa.assignment_type ASSIGNMENT_TYPE,
500 ma.allocation_percent ALLOCATION_PERCENT,
501 martp.effective_date EFFECTIVE_DATE,
502 martp.disable_date DISABLE_DATE,
503 ma.priority PRIORITY,
504 ma.service_level SERVICE_LEVEL,
505 martp.time_phase_id TIME_PHASE_ID,
506 ma.class CLASS,
507 ma.partner_id PARTNER_ID,
508 ma.partner_site_id PARTNER_SITE_ID,
509 ma.level_id LEVEL_ID,
510 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
511 ma.level_priority LEVEL_PRIORITY,
512 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
513 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
514 FROM msc_allocations ma,
515 msc_alloc_rule_time_phases martp,
516 msc_allocation_assignments maa,
517 msc_department_resources mdr
518 WHERE maa.assignment_type = 5
519 AND maa.sr_instance_id = mdr.sr_instance_id
520 AND maa.resource_group = mdr.resource_group_name
521 AND maa.allocation_rule_name = martp.allocation_rule_name
522 AND martp.time_phase_id = ma.time_phase_id
523 AND mdr.plan_id = -1
524 AND maa.resource_group IS NOT NULL
525 AND mdr.sr_instance_id >= ' || to_char(l_min_instance_id) ||
526 ' AND mdr.sr_instance_id < ' || to_char(l_max_instance_id) ||
527 ' AND NOT EXISTS (
528 SELECT maa1.resource_id
529 FROM msc_allocation_assignments maa1
530 WHERE maa1.resource_id = mdr.resource_id
531 AND maa1.organization_id = mdr.organization_id
532 AND maa1.sr_instance_id = mdr.sr_instance_id
533 AND maa1.department_id = mdr.department_id
534 AND maa1.assignment_type = 4)
535 UNION ALL
536 SELECT mdr.resource_id RESOURCE_ID,
537 mdr.department_id DEPARTMENT_ID,
538 mdr.organization_id ORGANIZATION_ID,
539 mdr.sr_instance_id SR_INSTANCE_ID,
540 ma.demand_class DEMAND_CLASS,
541 maa.allocation_rule_name ALLOCATION_RULE_NAME,
542 maa.assignment_type ASSIGNMENT_TYPE,
543 ma.allocation_percent ALLOCATION_PERCENT,
544 martp.effective_date EFFECTIVE_DATE,
545 martp.disable_date DISABLE_DATE,
546 ma.priority PRIORITY,
547 ma.service_level SERVICE_LEVEL,
548 martp.time_phase_id TIME_PHASE_ID,
549 ma.class CLASS,
550 ma.partner_id PARTNER_ID,
551 ma.partner_site_id PARTNER_SITE_ID,
552 ma.level_id LEVEL_ID,
553 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
554 ma.level_priority LEVEL_PRIORITY,
555 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
556 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
557 FROM msc_allocations ma,
558 msc_alloc_rule_time_phases martp,
559 msc_allocation_assignments maa,
560 msc_department_resources mdr
561 WHERE maa.assignment_type = 6
562 AND maa.sr_instance_id = mdr.sr_instance_id
563 AND maa.organization_id = mdr.organization_id
564 AND maa.allocation_rule_name = martp.allocation_rule_name
565 AND martp.time_phase_id = ma.time_phase_id
566 AND mdr.plan_id = -1
567 AND maa.resource_group IS NULL
568 AND maa.resource_id IS NULL
569 AND mdr.sr_instance_id >= ' || to_char(l_min_instance_id) ||
570 ' AND mdr.sr_instance_id < ' || to_char(l_max_instance_id) ||
571 ' AND NOT EXISTS (
572 SELECT maa1.resource_id
573 FROM msc_allocation_assignments maa1
574 WHERE maa1.resource_id = mdr.resource_id
575 AND maa1.organization_id = mdr.organization_id
576 AND maa1.sr_instance_id = mdr.sr_instance_id
577 AND maa1.department_id = mdr.department_id
578 AND maa1.assignment_type = 4)
579 AND NOT EXISTS (
580 SELECT maa2.resource_group
581 FROM msc_allocation_assignments maa2
582 WHERE maa2.resource_group = mdr.resource_group_name
583 AND maa2.sr_instance_id = mdr.sr_instance_id
584 AND maa2.assignment_type = 5)
585 UNION ALL
586 SELECT mdr.resource_id RESOURCE_ID,
587 mdr.department_id DEPARTMENT_ID,
588 mdr.organization_id ORGANIZATION_ID,
589 mdr.sr_instance_id SR_INSTANCE_ID,
590 ma.demand_class DEMAND_CLASS,
591 maa.allocation_rule_name ALLOCATION_RULE_NAME,
592 maa.assignment_type ASSIGNMENT_TYPE,
593 ma.allocation_percent ALLOCATION_PERCENT,
594 martp.effective_date EFFECTIVE_DATE,
595 martp.disable_date DISABLE_DATE,
596 ma.priority PRIORITY,
597 ma.service_level SERVICE_LEVEL,
598 martp.time_phase_id TIME_PHASE_ID,
599 ma.class CLASS,
600 ma.partner_id PARTNER_ID,
601 ma.partner_site_id PARTNER_SITE_ID,
602 ma.level_id LEVEL_ID,
603 ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
604 ma.level_priority LEVEL_PRIORITY,
605 ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
606 ma.min_allocation_percent MIN_ALLOCATION_PERCENT
607 FROM msc_allocations ma,
608 msc_department_resources mdr,
609 msc_alloc_rule_time_phases martp,
610 msc_allocation_assignments maa
611 WHERE maa.assignment_type = 1
612 AND maa.allocation_rule_name = martp.allocation_rule_name
613 AND martp.time_phase_id = ma.time_phase_id
614 AND mdr.plan_id = -1
615 AND maa.resource_id IS NULL
616 AND maa.resource_group IS NULL
617 AND mdr.sr_instance_id >= ' || to_char(l_min_instance_id) ||
618 ' AND mdr.sr_instance_id < ' || to_char(l_max_instance_id) ||
619 ' AND NOT EXISTS (
620 SELECT maa1.resource_id
621 FROM msc_allocation_assignments maa1
622 WHERE maa1.resource_id = mdr.resource_id
623 AND maa1.department_id = mdr.department_id
624 AND maa1.organization_id = mdr.organization_id
625 AND maa1.sr_instance_id = mdr.sr_instance_id
626 AND maa1.assignment_type = 4)
627 AND NOT EXISTS (
628 SELECT maa1.resource_group
629 FROM msc_allocation_assignments maa1
630 WHERE maa1.resource_group = mdr.resource_group_name
631 AND maa1.sr_instance_id = mdr.sr_instance_id
632 AND maa1.assignment_type = 5)
633 AND NOT EXISTS (
634 SELECT maa3.resource_group
635 FROM msc_allocation_assignments maa3
636 WHERE maa3.organization_id = mdr.organization_id
637 AND maa3.sr_instance_id = mdr.sr_instance_id
638 AND maa3.assignment_type = 6)';
639
640 sql_stmt2 := 'create index ' || l_temp_table(i) || '_N1 on ' ||
641 l_temp_table(i) || '
642 --NOLOGGING
643 --5053818
644 (resource_id, department_id, organization_id, sr_instance_id, demand_class)
645 storage(INITIAL '||l_res_hier_indx_init_extent||'K NEXT '||l_res_hier_indx_nxt_extent||'M PCTINCREASE '||l_res_hier_indx_pct_inc||') tablespace ' || l_tbspace(i);
646 --storage(INITIAL 40K NEXT 2M PCTINCREASE 0) LOCAL tablespace ' || l_tbspace(i);
647
648 END IF;
649
650 msc_util.msc_log('Before AD_DDL');
651 BEGIN
652 msc_util.msc_log('Before create table : ' ||l_temp_table(i));
653 ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
654 APPLICATION_SHORT_NAME => l_apps_schema,
655 STATEMENT_TYPE => ad_ddl.create_table,
656 STATEMENT => sql_stmt1,
657 OBJECT_NAME => l_temp_table(i));
658 EXCEPTION
659 WHEN others THEN
660
661 IF SQLCODE IN (-01471) THEN
662 msc_util.msc_log('SYNONYM already created in APPS schema.');
663 msc_util.msc_log('After create table SYNONYM: ' ||l_temp_table(i));
664 ELSIF SQLCODE IN (-00955) THEN
665 msc_util.msc_log('Object already created in APPS Schema. No Need to create SYNONYM again');
666 msc_util.msc_log('After create table OBJECT SYNONYM: ' ||l_temp_table(i));
667 ELSE
668 msc_util.msc_log(sqlerrm);
669 msc_util.msc_log('Inside Exception of create table : ' ||l_temp_table(i));
670
671 ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
672 APPLICATION_SHORT_NAME => l_apps_schema,
673 STATEMENT_TYPE => ad_ddl.drop_table,
674 STATEMENT => 'DROP TABLE ' || l_temp_table(i),
675 OBJECT_NAME => l_temp_table(i));
676
677 msc_util.msc_log('After Drop table : ' ||l_temp_table(i));
678 ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
679 APPLICATION_SHORT_NAME => l_apps_schema,
680 STATEMENT_TYPE => ad_ddl.create_table,
681 STATEMENT => sql_stmt1,
682 OBJECT_NAME => l_temp_table(i));
683 msc_util.msc_log('After create table : ' ||l_temp_table(i));
684
685 END IF;
686 END;
687
688 BEGIN
689 msc_util.msc_log('Before create index : ' ||l_temp_table(i));
690 ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
691 APPLICATION_SHORT_NAME => l_apps_schema,
692 STATEMENT_TYPE => ad_ddl.create_index,
693 STATEMENT => sql_stmt2,
694 OBJECT_NAME => l_temp_table(i));
695 EXCEPTION
696 WHEN others THEN
697
698 msc_util.msc_log(sqlerrm);
699 msc_util.msc_log('Inside Exception of create index : ' ||l_temp_table(i));
700 --- bug 4156016: Raise exception so that we could error out gracefully
701 RAISE FND_API.G_EXC_ERROR;
702
703 END;
704
705 fnd_stats.gather_table_stats(l_apps_schema, l_temp_table(i), granularity => 'ALL');
706
707 i := l_cur_table.NEXT(i);
708 l_min_instance_id := l_max_instance_id;
709
710 IF i = l_cur_table.COUNT THEN
711 l_max_instance_id := 99999;
712 ELSIF i < l_cur_table.COUNT THEN
713 IF l_cur_table(i) <> l_cur_table(i+1) THEN
714 l_max_instance_id := 99999;
715 ELSE
716 l_max_instance_id := l_max_instance_id + 30;
717 END IF;
718 END IF;
719
720 END LOOP; -- WHILE i IS NOT NULL
721
722 i := l_cur_table.FIRST;
723 WHILE i IS NOT NULL LOOP
724 sql_stmt1 := 'ALTER TABLE ' || l_cur_table(i) || ' exchange partition ' ||
725 l_part_name(i) || ' with table ' || l_temp_table(i) ||
726 ' including indexes without validation';
727
728 BEGIN
729 msc_util.msc_log('Before alter table : ' ||l_cur_table(i));
730 ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
731 APPLICATION_SHORT_NAME => l_apps_schema,
732 STATEMENT_TYPE => ad_ddl.alter_table,
733 STATEMENT => sql_stmt1,
734 OBJECT_NAME => l_cur_table(i));
735 EXCEPTION
736 WHEN others THEN
737
738 msc_util.msc_log(sqlerrm);
739 msc_util.msc_log('Inside Exception of alter table : ' ||l_cur_table(i));
740 --- bug 4156016: Raise exception so that we could error out gracefully
741 RAISE FND_API.G_EXC_ERROR;
742
743 END;
744
745 sql_stmt2 := 'DROP TABLE ' || l_temp_table(i);
746
747 BEGIN
748 msc_util.msc_log('Before drop table : ' ||l_temp_table(i));
749 ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
750 APPLICATION_SHORT_NAME => l_apps_schema,
751 STATEMENT_TYPE => ad_ddl.drop_table,
752 STATEMENT => sql_stmt2,
753 OBJECT_NAME => l_temp_table(i));
754 EXCEPTION
755 WHEN others THEN
756
757 msc_util.msc_log(sqlerrm);
758 msc_util.msc_log('Inside Exception of alter table : ' ||l_temp_table(i));
759 --- bug 4156016: Raise exception so that we could error out gracefully
760 RAISE FND_API.G_EXC_ERROR;
761
762 END;
763
764 i := l_cur_table.NEXT(i);
765 END LOOP; -- WHILE i IS NOT NULL LOOP
766
767 RETCODE:= G_SUCCESS;
768 msc_util.msc_log('End REFRESH_MVIEW');
769 EXCEPTION
770 WHEN OTHERS THEN
771 msc_util.msc_log(sqlerrm);
772 msc_util.msc_log('Inside Main Exception');
773 --bug 4156016: Set the error code here so that if an unhandled eception occurs in
774 --- exception block then atleast the program will error out.
775 RETCODE:= G_ERROR;
776 ERRBUF:= SQLERRM;
777
778 i := l_temp_table.FIRST;
779 WHILE i IS NOT NULL LOOP
780
781 sql_stmt2 := 'DROP TABLE ' || l_temp_table(i);
782 BEGIN
783 ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
784 APPLICATION_SHORT_NAME => l_apps_schema,
785 STATEMENT_TYPE => ad_ddl.drop_table,
786 STATEMENT => sql_stmt2,
787 OBJECT_NAME => l_temp_table(i));
788 EXCEPTION
789 WHEN OTHERS THEN
790 msc_util.msc_log('Error in droping table ' || l_temp_table(i));
791 END;
792
793 i := l_temp_table.NEXT(i);
794 END LOOP; -- WHILE i IS NOT NULL LOOP
795
796 ROLLBACK;
797 ---bug 4156016: The variables have already been set
798 --RETCODE:= G_ERROR;
799 --ERRBUF:= SQLERRM;
800 END REFRESH_MVIEW;
801
802
803 END MSC_ATP_REFRESH_MVIEW;