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