[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_MSC_OBJECTS_C
Source
1 PACKAGE BODY ISC_DBI_MSC_OBJECTS_C AS
2 /* $Header: ISCSCF8B.pls 120.5.12000000.2 2007/01/23 20:36:28 achandak ship $ */
3
4 g_errbuf VARCHAR2(2000) := NULL;
5 g_retcode VARCHAR2(200) := '0';
6
7 g_isc_schema VARCHAR2(30);
8 g_status VARCHAR2(30);
9 g_industry VARCHAR2(30);
10 g_db_link VARCHAR2(200) := NULL;
11 g_batch_size NUMBER;
12 g_row_count NUMBER;
13 g_snapshot_date DATE;
14 g_global_currency VARCHAR2(15);
15 g_global_rate_type VARCHAR2(15);
16 g_sec_global_currency VARCHAR2(15);
17 g_sec_global_rate_type VARCHAR2(15);
18 g_rebuild_snapshot_index VARCHAR2(1) := 'N';
19
20 TYPE TableList IS TABLE OF VARCHAR2(80);
21 g_small_bases TableList := TableList('ISC_DBI_PLAN_ORGANIZATIONS', 'ISC_DBI_PLAN_BUCKETS', 'ISC_DBI_PLANS');
22 g_large_bases TableList := TableList('ISC_DBI_SUPPLIES_F','ISC_DBI_INV_DETAIL_F','ISC_DBI_RES_SUMMARY_F',
23 'ISC_DBI_EXCEPTION_DETAILS_F','ISC_DBI_DEMANDS_F',
24 'ISC_DBI_FULL_PEGGING_F');
25
26 g_small_snapshots TableList := TableList('ISC_DBI_PLAN_ORG_SNAPSHOTS', 'ISC_DBI_PLAN_SNAPSHOTS');
27 g_large_snapshots TableList := TableList('ISC_DBI_SUPPLIES_SNAPSHOTS','ISC_DBI_INV_DETAIL_SNAPSHOTS',
28 'ISC_DBI_RES_SUM_SNAPSHOTS', 'ISC_DBI_SHORTFALL_SNAPSHOTS');
29
30 FUNCTION DROP_PLANS(p_plan_id NUMBER) RETURN NUMBER IS
31
32 l_delete_stmt VARCHAR2(2000);
33 l_drop_stmt VARCHAR2(2000);
34 no_partition EXCEPTION;
35 PRAGMA EXCEPTION_INIT(no_partition, -02149);
36
37 BEGIN
38
39 FOR j in 1..g_large_bases.last LOOP
40 BEGIN
41 l_drop_stmt := 'ALTER TABLE '|| g_isc_schema || '.' || g_large_bases(j) || ' DROP PARTITION plan_' || p_plan_id;
42 EXECUTE IMMEDIATE l_drop_stmt;
43 EXCEPTION
44 WHEN no_partition THEN
45 BIS_COLLECTION_UTILITIES.put_line('The partition plan_' || p_plan_id || ' of table '|| g_large_bases(j) ||' does not exist.');
46 END;
47 END LOOP;
48
49 FOR i in 1..g_small_bases.last LOOP
50 l_delete_stmt := 'DELETE FROM ' || g_small_bases(i) || ' WHERE plan_id = :1';
51 EXECUTE IMMEDIATE l_delete_stmt USING p_plan_id;
52 END LOOP;
53
54 RETURN(1);
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 g_errbuf := 'Error in function DROP_PLANS : '||sqlerrm;
59 RETURN(-1);
60 END;
61
62 FUNCTION DROP_SNAPSHOTS(p_snapshot_id NUMBER) RETURN NUMBER IS
63
64 l_delete_stmt VARCHAR2(2000);
65 l_drop_stmt VARCHAR2(2000);
66 no_partition EXCEPTION;
67 PRAGMA EXCEPTION_INIT(no_partition, -02149);
68
69 BEGIN
70
71 FOR j in 1..g_large_snapshots.last LOOP
72 BEGIN
73
74 l_drop_stmt := 'ALTER TABLE '|| g_isc_schema || '.' || g_large_snapshots(j) || ' DROP PARTITION s_' || p_snapshot_id;
75 EXECUTE IMMEDIATE l_drop_stmt;
76
77 EXCEPTION
78 WHEN no_partition THEN
79 BIS_COLLECTION_UTILITIES.put_line('The partition s_'||p_snapshot_id||' of table '||g_large_snapshots(j)||' does not exist.');
80 END;
81 END LOOP;
82
83 g_rebuild_snapshot_index := 'Y';
84
85 FOR i in 1..g_small_snapshots.last LOOP
86 l_delete_stmt := 'DELETE FROM ' || g_small_snapshots(i) || ' WHERE snapshot_id = :1';
87 EXECUTE IMMEDIATE l_delete_stmt USING p_snapshot_id;
88 END LOOP;
89
90 RETURN(1);
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 g_errbuf := 'Error in function DROP_SNAPSHOTS : '||sqlerrm;
95 RETURN(-1);
96 END;
97
98
99 -- -----------
100 -- CHECK_SETUP
101 -- -----------
102
103 FUNCTION CHECK_SETUP RETURN NUMBER IS
104
105 l_trunc_stmt VARCHAR2(500);
106 l_instance_stmt VARCHAR2(2000);
107 l_stmt VARCHAR2(2000);
108 l_num_all_sources NUMBER;
109 l_num_sources NUMBER;
110 l_plan_id NUMBER;
111 l_drop NUMBER;
112 l_sec_curr_def VARCHAR2(1);
113
114 BEGIN
115
116 BIS_COLLECTION_UTILITIES.Put_Line(' ');
117
118 IF (NOT FND_INSTALLATION.GET_APP_INFO('ISC', g_status, g_industry, g_isc_schema)) THEN
119 g_errbuf := 'Error while retrieving product information.';
120 RETURN (-1);
121 END IF;
122
123 l_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
124 IF (l_sec_curr_def = 'E') THEN
125 g_errbuf := 'Collection aborted because the set-up of the DBI Global Parameter "Secondary Global Currency" is incomplete. Please verify the proper set-up of the Global Currency Rate Type and the Global Currency Code.';
126 return(-1);
127 END IF;
128
129 BIS_COLLECTION_UTILITIES.put_line('The schema name is '|| g_isc_schema);
130
131 g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
132 BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);
133
134 g_global_currency := bis_common_parameters.get_currency_code;
135 BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
136
137 g_global_rate_type := bis_common_parameters.get_rate_type;
138 BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
139
140 g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
141 BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
142
143 g_sec_global_rate_type := bis_common_parameters.get_secondary_rate_type;
144 BIS_COLLECTION_UTILITIES.put_line('The secondary rate type is ' || g_sec_global_rate_type);
145
146 -- To get the database link to APS instance
147 -- If the db link is NULL,
148 -- that means APS and ERP are sitting in the same instance
149
150 -- The program will error out if there are more than one row in this table
151
152 g_db_link := FND_PROFILE.VALUE('ISC_DBI_PLANNING_INSTANCE');
153
154 IF (g_db_link is NULL) THEN
155 BEGIN
156 SELECT decode(ltrim(a2m_dblink, ' '), NULL, NULL, '@'||a2m_dblink)
157 INTO g_db_link
158 FROM mrp_ap_apps_instances_all;
159 EXCEPTION
160 WHEN TOO_MANY_ROWS THEN
161 g_errbuf := 'There are multiple APS desitinations for this ERP instance. Only one APS instance is supported. Please set up the profile option (ISC: DBI Planning Instance) with the DBI planning instance.';
162 RETURN(-1);
163 WHEN NO_DATA_FOUND THEN
164 g_errbuf := 'This ERP instance is not configured as a source of APS.';
165 RETURN(-1);
166 END;
167 ELSIF (g_db_link = '@') THEN
168 g_db_link := NULL;
169 END IF;
170
171 BIS_COLLECTION_UTILITIES.put_line('Retrieve the db link '|| g_db_link);
172
173 l_stmt := 'SELECT count(*) FROM msc_apps_instances' || g_db_link ||
174 ' WHERE enable_flag = 1 AND apps_ver NOT IN (1,2)';
175
176 EXECUTE IMMEDIATE l_stmt INTO l_num_all_sources;
177 BIS_COLLECTION_UTILITIES.put_line('The number of ERP instances is '|| l_num_all_sources);
178
179 IF (l_num_all_sources > 1) THEN
180 g_errbuf := 'There are more than one ERP sources. DBI only supports one ERP source.';
181 RETURN (-1);
182 ELSIF (l_num_all_sources = 0) THEN
183 g_errbuf := 'No ERP source has been set up.';
184 RETURN (-1);
185 END IF;
186
187 l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_apps_instances';
188 EXECUTE IMMEDIATE l_trunc_stmt;
189
190 BIS_COLLECTION_UTILITIES.put_line('Begin to collect instance information');
191 FII_UTIL.Start_Timer;
192
193 l_stmt := 'INSERT INTO isc_dbi_apps_instances (' ||
194 ' instance_id, currency, instance_code, ' ||
195 ' m2a_dblink, a2m_dblink, created_by, creation_date, '||
196 ' last_updated_by, last_update_date, last_update_login)' ||
197 'SELECT msc_inst.instance_id, msc_inst.currency, msc_inst.instance_code, ' ||
198 'msc_inst.m2a_dblink, msc_inst.a2m_dblink, msc_inst.created_by, msc_inst.creation_date, '||
199 'msc_inst.last_updated_by, msc_inst.last_update_date, msc_inst.last_update_login ' ||
200 'FROM msc_apps_instances' || g_db_link || ' msc_inst ' ||
201 'WHERE enable_flag = 1 AND apps_ver NOT IN (1,2)';
202 EXECUTE IMMEDIATE l_stmt;
203 l_num_sources := sql%rowcount;
204 COMMIT;
205
206 IF (l_num_sources = 0) THEN
207 g_errbuf := 'No corresponding source instance defined in the APS instance.';
208 RETURN (-1);
209 END IF;
210
211 FII_UTIL.Stop_Timer;
212 FII_UTIL.Print_Timer('Collected instance information in');
213 BIS_COLLECTION_UTILITIES.Put_Line(' ');
214
215 /* From DBI7.0, functional and DBI global currencies will be used. */
216
217 -- BIS_COLLECTION_UTILITIES.put_line('Begin to validate APS currency.');
218 -- FII_UTIL.Start_Timer;
219 --
220 -- SELECT count(*)
221 -- INTO l_currency
222 -- FROM isc_dbi_apps_instances i, fnd_currencies cur
223 -- WHERE i.currency = cur.currency_code;
224 --
225 -- IF (l_currency = 0) THEN
226 -- g_retcode := 1;
227 -- g_errbuf := 'The planning currency code is invalid.';
228 -- BIS_COLLECTION_UTILITIES.Put_Line('The planning currency code is invalid.');
229 -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
230 -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
231 -- BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_INVALID_CURRENCY'));
232 -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
233 -- END IF;
234 --
235 -- FII_UTIL.Stop_Timer;
236 -- FII_UTIL.Print_Timer('Validated the planning currency in');
237 -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
238
239
240 RETURN(1);
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 g_errbuf := 'Error in function CHECK_SETUP : '||sqlerrm;
245 RETURN(-1);
246
247 END check_setup;
248
249 -- ------------------------------
250 -- Identify Plans to be collected
251 -- ------------------------------
252
253 FUNCTION IDENTIFY_PLANS RETURN NUMBER IS
254
255 l_count NUMBER;
256 l_trunc_stmt VARCHAR2(500);
257 l_stmt VARCHAR2(2000);
258 l_plan_id NUMBER;
259
260 CURSOR Obsolete_Plans IS
261 SELECT tmp.plan_id
262 FROM isc_dbi_tmp_plans tmp
263 WHERE nvl(tmp.old_data_start_date, tmp.data_start_date-1) < tmp.data_start_date;
264
265 BEGIN
266
267 l_count := 0;
268
269 -- ------------------------------------------------------------
270 -- Insert the plans need to be collected into ISC_DBI_TMP_PLANS
271 -- ------------------------------------------------------------
272
273 BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
274 FII_UTIL.Start_Timer;
275
276 l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';
277 EXECUTE IMMEDIATE l_trunc_stmt;
278
279 FII_UTIL.Stop_Timer;
280 FII_UTIL.Print_Timer('Truncated the temp table in');
281 BIS_COLLECTION_UTILITIES.Put_Line(' ');
282
283 g_snapshot_date := sysdate;
284 BIS_COLLECTION_UTILITIES.put_line('The snapshot date is '|| to_char(g_snapshot_date, 'MM/DD/YYYY HH24:MI:SS'));
285
286 BIS_COLLECTION_UTILITIES.put_line('Begin to load into the temp table.');
287 FII_UTIL.Start_Timer;
288
289 l_stmt := 'INSERT INTO isc_dbi_tmp_plans (' ||
290 'PLAN_ID, PLAN_NAME, OLD_DATA_START_DATE, DATA_START_DATE, ' ||
291 'INSTANCE_ID, PLAN_USAGE) ' ||
292 'SELECT setup.plan_id, setup.plan_name, p.data_start_date, setup.data_start_date, inst.instance_id, '||
293 'sum(plan_usage) '||
294 'FROM isc_dbi_plans p, isc_dbi_apps_instances inst, '||
295 '(SELECT plan.plan_id, opi.plan_name, plan.data_start_date,1 PLAN_USAGE '||
296 'FROM opi_dbi_baseline_schedules sched, opi_dbi_baseline_plans opi, '||
297 'msc_plans' || g_db_link || ' plan '||
298 'WHERE sched.baseline_id = opi.baseline_id '||
299 'AND sched.next_collection_date <= trunc(sysdate) '||
300 'AND sched.schedule_type = 1 '||
301 'AND opi.plan_name = plan.compile_designator '||
302 'UNION ALL '||
303 'SELECT plan.plan_id, isc.plan_name, plan.data_start_date, '||
304 '(CASE WHEN isc.last_collected_date < plan.data_start_date THEN 2
305 WHEN isc.last_collected_date is null THEN 2 ELSE 4 END) PLAN_USAGE '||
306 'FROM isc_dbi_plan_schedules isc, msc_plans' || g_db_link || ' plan ' ||
307 'WHERE isc.next_collection_date <= trunc(sysdate)'||
308 'AND isc.plan_name = plan.compile_designator) setup '||
309 'WHERE p.compile_designator(+) = setup.plan_name '||
310 -- 'AND p.complete_flag(+) = ''Y'' '||
311 'GROUP BY setup.plan_id, setup.plan_name, p.data_start_date, setup.data_start_date, inst.instance_id';
312 EXECUTE IMMEDIATE l_stmt;
313 l_count := SQL%ROWCOUNT;
314 COMMIT;
315
316 FII_UTIL.Stop_Timer;
317 FII_UTIL.Print_Timer('Retrieved '|| l_count || ' plans from setup tables in');
318 BIS_COLLECTION_UTILITIES.Put_Line(' ');
319
320 BIS_COLLECTION_UTILITIES.Put_Line(' ');
321 BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_DBI_TMP_PLANS');
322 FII_UTIL.Start_Timer;
323
324 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
325 TABNAME => 'ISC_DBI_TMP_PLANS');
326
327 FII_UTIL.Stop_Timer;
328 FII_UTIL.Print_Timer('Analyzed table ISC_DBI_TMP_PLANS in ');
329
330 -- Clean up the obsolete planned data
331
332 l_count := 0;
333 OPEN Obsolete_Plans;
334 FETCH Obsolete_Plans INTO l_plan_id;
335 IF Obsolete_Plans%ROWCOUNT <> 0 THEN
336 WHILE Obsolete_Plans%Found LOOP
337 BIS_COLLECTION_UTILITIES.Put_Line('Dropping plan '||l_plan_id);
338 IF (DROP_PLANS(l_plan_id) = -1) THEN RETURN(-1); END IF;
339 l_count := l_count +1;
340 FETCH Obsolete_Plans INTO l_plan_id;
341 END LOOP;
342 END IF;
343 CLOSE Obsolete_Plans;
344
345 /*
346 SELECT count(*)
347 INTO l_count
348 FROM isc_dbi_tmp_plans tmp
349 WHERE nvl(tmp.old_data_start_date, tmp.data_start_date-1) < tmp.data_start_date;
350 */
351
352 BIS_COLLECTION_UTILITIES.Put_Line('Identified '|| l_count || ' plans need to be collected.');
353 RETURN(l_count);
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 g_errbuf := 'Error in function Identify_Plans : '||sqlerrm;
358 RETURN(-1);
359
360 END identify_plans;
361
362 FUNCTION PULL_DATA RETURN NUMBER IS
363
364 l_insert_stmt VARCHAR2(32767);
365 l_sel_stmt1 VARCHAR2(32767);
366 l_sel_stmt2 VARCHAR2(32767);
367 l_count NUMBER;
368 l_add_stmt VARCHAR2(2000);
369 l_add_plan_id NUMBER;
370 l_trunc_stmt VARCHAR2(500);
371 l_in_length NUMBER;
372 l_sel_length1 NUMBER;
373 l_sel_length2 NUMBER;
374
375 partition_exists EXCEPTION;
376 PRAGMA EXCEPTION_INIT(partition_exists, -14013);
377
378 part_value_exists EXCEPTION;
379 PRAGMA EXCEPTION_INIT(part_value_exists, -14312);
380
381 CURSOR Plan_List IS
382 SELECT plan_id FROM isc_dbi_tmp_plans tmp
383 WHERE nvl(tmp.old_data_start_date, tmp.data_start_date-1) < tmp.data_start_date;
384
385 BEGIN
386
387 -- --------------
388 -- Add Partitions
389 -- --------------
390
391 OPEN Plan_List;
392 FETCH Plan_List INTO l_add_plan_id;
393 IF Plan_List%ROWCOUNT <> 0 THEN
394 WHILE Plan_List%Found LOOP
395 BIS_COLLECTION_UTILITIES.Put_Line('Adding partitions for plan '|| l_add_plan_id);
396 FOR i in 1..g_large_bases.last LOOP
397 BEGIN
398 l_add_stmt := 'ALTER TABLE '||g_isc_schema||'.'||g_large_bases(i)||' ADD PARTITION plan_'|| l_add_plan_id ||' VALUES ('''||l_add_plan_id||''')';
399 EXECUTE IMMEDIATE l_add_stmt;
400 EXCEPTION
401 WHEN partition_exists THEN
402 BIS_COLLECTION_UTILITIES.put_line('The partition plan_'||l_add_plan_id||' of table '||g_large_bases(i)||' already exists.');
403 NULL;
404 WHEN part_value_exists THEN
405 BIS_COLLECTION_UTILITIES.put_line('The value '||l_add_plan_id||' already exists in another partition of table '||g_large_bases(i));
406 NULL;
407 END;
408 END LOOP;
409 FETCH Plan_List INTO l_add_plan_id;
410 END LOOP;
411 END IF;
412 l_count := Plan_List%ROWCOUNT;
413 CLOSE Plan_List;
414
415 l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_periods';
416 EXECUTE IMMEDIATE l_trunc_stmt;
417 BIS_COLLECTION_UTILITIES.put_line('Table isc_dbi_periods has been truncated.');
418
419 BIS_COLLECTION_UTILITIES.put_line('Begin to load the base tables from APS instance.');
420 FII_UTIL.Start_Timer;
421
422 l_insert_stmt := 'INSERT /*+ APPEND PARALLEL */ FIRST '||
423 'WHEN union_flag = 1 THEN '||
424 'INTO isc_dbi_plans ( ' ||
425 'PLAN_ID,ORGANIZATION_ID,COMPILE_DESIGNATOR,CONSTRAINED_FLAG,CURR_CUTOFF_DATE,'||
426 'CURR_PLAN_TYPE,CURR_START_DATE,CUTOFF_DATE,DATA_START_DATE,DESCRIPTION,COMPLETE_FLAG,'||
427 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
428 'VALUES(plan_id,organization_id,compile_designator,constrained_flag, curr_cutoff_date,'||
429 'curr_plan_type,curr_start_date,cutoff_date,data_start_date,description,complete_flag,'||
430 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
431 'WHEN union_flag = 2 THEN '||
432 'INTO isc_dbi_plan_organizations ( '||
433 'PLAN_ID,ORGANIZATION_ID,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
434 'VALUES(plan_id,organization_id,'||
435 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
436 'WHEN union_flag = 3 THEN '||
437 'INTO isc_dbi_plan_buckets ( '||
438 'PLAN_ID,ORGANIZATION_ID,BKT_END_DATE,BKT_START_DATE,'||
439 'BUCKET_INDEX,BUCKET_TYPE,CURR_FLAG,DAYS_IN_BKT,'||
440 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
441 'VALUES(plan_id,organization_id,bkt_end_date,bkt_start_date,'||
442 'bucket_index,bucket_type,curr_flag,days_in_bkt,'||
443 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
444 'WHEN union_flag = 4 THEN '||
445 'INTO isc_dbi_supplies_f ( '||
446 'PLAN_ID,TRANSACTION_ID,ORGANIZATION_ID,TIME_NEW_SCH_DATE_ID,SOURCE_ORGANIZATION_ID,'||
447 'SOURCE_SR_INSTANCE_ID,SOURCE_SUPPLIER_ID,SOURCE_SUPPLIER_SITE_ID,SR_INSTANCE_ID,SR_INVENTORY_ITEM_ID,'||
448 'SR_SUPPLIER_ID,SUPPLIER_ID,SUPPLIER_SITE_ID,BOM_ITEM_TYPE,DISPOSITION_STATUS_TYPE,'||
449 'IN_SOURCE_PLAN,ITEM_PRICE,NEW_ORDER_QUANTITY,NEW_PROCESSING_DAYS,NEW_SCHEDULE_DATE,ORDER_TYPE,'||
450 'PLANNING_MAKE_BUY_CODE,R_CFM_ROUTING_FLAG,STANDARD_COST,UOM_CODE,'||
451 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
452 'VALUES(plan_id,transaction_id,organization_id,time_new_sch_date_id,source_organization_id,'||
453 'source_sr_instance_id,source_supplier_id,source_supplier_site_id,sr_instance_id,'||
454 'sr_inventory_item_id,sr_supplier_id,supplier_id,supplier_site_id,bom_item_type,'||
455 'disposition_status_type,in_source_plan,item_price,new_order_quantity,'||
456 'new_processing_days,new_schedule_date,order_type,planning_make_buy_code,r_cfm_routing_flag,'||
457 'standard_cost,uom_code,'||
458 'created_by,creation_date,last_updated_by,last_update_date,last_update_login ) '||
459 'WHEN union_flag = 5 THEN '||
460 'INTO isc_dbi_inv_detail_f (PLAN_ID,ORGANIZATION_ID,'||
461 'SR_INVENTORY_ITEM_ID,TIME_DETAIL_DATE_ID,CARRYING_COST,DETAIL_DATE,INVENTORY_COST,'||
462 'MDS_COST,MDS_PRICE,MDS_QUANTITY,PRODUCTION_COST,PURCHASING_COST,UOM_CODE,'||
463 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
464 'VALUES(plan_id,organization_id,sr_inventory_item_id,time_detail_date_id,carrying_cost,detail_date,'||
465 'inventory_cost,mds_cost,mds_price,mds_quantity,production_cost,purchasing_cost,'||
466 'uom_code,created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
467 'WHEN union_flag = 6 THEN '||
468 'INTO isc_dbi_res_summary_f ( '||
469 'PLAN_ID,ORGANIZATION_ID,DEPARTMENT_ID,RESOURCE_ID,TIME_RESOURCE_DATE_ID,'||
470 'AVAILABLE_HOURS,ORGANIZATION_TYPE,REQUIRED_HOURS,RESOURCE_DATE,UTILIZATION,'||
471 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
472 'VALUES(plan_id,organization_id,department_id,resource_id,time_resource_date_id,'||
473 'available_hours,organization_type,required_hours,resource_date,utilization,'||
474 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
475 'WHEN union_flag = 7 THEN '||
476 'INTO isc_dbi_exception_details_f ('||
477 'PLAN_ID,ORGANIZATION_ID,SR_INVENTORY_ITEM_ID,ORGANIZATION_TYPE,'||
478 'DEPARTMENT_ID, RESOURCE_ID, SR_SUPPLIER_ID, '||
479 'EXCEPTION_DETAIL_ID,EXCEPTION_TYPE,NUMBER1,NUMBER2,SR_SUPPLIER_SITE_ID,'||
480 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
481 'VALUES(plan_id,organization_id,sr_inventory_item_id,organization_type,'||
482 'department_id, resource_id, sr_supplier_id, '||
483 'exception_detail_id,exception_type,number1,number2,sr_supplier_site_id, '||
484 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
485 'WHEN union_flag = 8 THEN '||
486 'INTO isc_dbi_demands_f (PLAN_ID,ORGANIZATION_ID,SR_INVENTORY_ITEM_ID,'||
487 'DEMAND_ID,TIME_AS_DMD_COMP_DATE,TIME_DMD_DATE_ID,TIME_USING_AS_DMD_DATE,ASSEMBLY_DEMAND_COMP_DATE,'||
488 'ORIGINATION_TYPE,UOM_CODE,USING_ASSEMBLY_DEMAND_DATE,'||
489 'AVERAGE_DISCOUNT,LIST_PRICE,SELLING_PRICE,STANDARD_COST,USING_REQUIREMENT_QUANTITY,'||
490 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
491 'VALUES(plan_id,organization_id,sr_inventory_item_id,demand_id,'||
492 'time_as_dmd_comp_date,time_dmd_date_id,time_using_as_dmd_date,assembly_demand_comp_date,'||
493 'origination_type,uom_code,using_assembly_demand_date,'||
494 'average_discount,list_price,selling_price,standard_cost,using_requirement_quantity,'||
495 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
496 'WHEN union_flag = 9 THEN '||
497 'INTO isc_dbi_periods (ORGANIZATION_ID,PERIOD_SET_NAME,PERIOD_NAME,'||
498 'START_DATE,END_DATE,YEAR_START_DATE,QUARTER_START_DATE,'||
499 'PERIOD_TYPE,PERIOD_YEAR,PERIOD_NUM,QUARTER_NUM,'||
500 'ENTERED_PERIOD_NAME,ADJUSTMENT_PERIOD_FLAG,DESCRIPTION,'||
501 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
502 'VALUES(organization_id,period_set_name,period_name,'||
503 'start_date,end_date,year_start_date,quarter_start_date,'||
504 'period_type,period_year,period_num,quarter_num,'||
505 'entered_period_name,adjustment_period_flag,description,'||
506 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
507 'WHEN union_flag = 10 THEN '||
508 'INTO isc_dbi_full_pegging_f (PLAN_ID,PEGGING_ID,DEMAND_ID,END_PEGGING_ID,TRANSACTION_ID,'||
509 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) '||
510 'VALUES(plan_id,pegging_id,demand_id, end_pegging_id, transaction_id,'||
511 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) ';
512
513 l_sel_stmt1 := 'SELECT /*+ DRIVING_SITE (p) */ p.plan_id,p.organization_id,null SR_INVENTORY_ITEM_ID,null UOM_CODE,'||
514 'p.compile_designator,'||
515 'DECODE(daily_material_constraints,1,1,'||
516 'DECODE(daily_resource_constraints,1,1,'||
517 'DECODE(weekly_material_constraints,1,1,'||
518 'DECODE(weekly_resource_constraints,1,1,'||
519 'DECODE(period_material_constraints, 1, 1,'||
520 'DECODE(period_resource_constraints, 1, 1, 2)))))) CONSTRAINED_FLAG,'||
521 'p.curr_cutoff_date,p.curr_plan_type,'||
522 'p.curr_start_date,p.cutoff_date,p.data_start_date,p.description,''N'' complete_flag,'||
523 'null BKT_END_DATE,null BKT_START_DATE,'||
524 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
525 'null TRANSACTION_ID,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
526 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
527 'null SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
528 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
529 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
530 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,null STANDARD_COST,'||
531 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
532 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
533 'null PRODUCTION_COST,null PURCHASING_COST,'||
534 'null RESOURCE_ID,null DEPARTMENT_ID,'||
535 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
536 'null RESOURCE_DATE,null UTILIZATION,'||
537 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2, null SR_SUPPLIER_SITE_ID,'||
538 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
539 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
540 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
541 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
542 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
543 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
544 'null PEGGING_ID,null END_PEGGING_ID,'||
545 'p.created_by,p.creation_date,p.last_updated_by,p.last_update_date,p.last_update_login,1 union_flag '||
546 'FROM isc_dbi_tmp_plans tmp,msc_plans' || g_db_link || ' p '||
547 'WHERE nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
548 'AND tmp.plan_name = p.compile_designator '||
549 'AND tmp.instance_id = p.sr_instance_id UNION ALL ' ||
550 'SELECT /*+ DRIVING_SITE (po) */ po.plan_id,po.organization_id,null SR_INVENTORY_ITEM_ID,null UOM_CODE,'||
551 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
552 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
553 'null BKT_END_DATE,null BKT_START_DATE,'||
554 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
555 'null TRANSACTION_ID,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
556 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
557 'null SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
558 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
559 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
560 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,null STANDARD_COST,'||
561 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
562 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
563 'null PRODUCTION_COST,null PURCHASING_COST,'||
564 'null RESOURCE_ID,null DEPARTMENT_ID,'||
565 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
566 'null RESOURCE_DATE,null UTILIZATION,'||
567 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2, null SR_SUPPLIER_SITE_ID,'||
568 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
569 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
570 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
571 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
572 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
573 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
574 'null PEGGING_ID,null END_PEGGING_ID,'||
575 'po.created_by,po.creation_date,po.last_updated_by,po.last_update_date,po.last_update_login,2 union_flag '||
576 'FROM isc_dbi_tmp_plans tmp,msc_plan_organizations' || g_db_link || ' po '||
577 'WHERE nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
578 'AND tmp.plan_id = po.plan_id '||
579 'AND tmp.instance_id = po.sr_instance_id UNION ALL '||
580 'SELECT /*+ DRIVING_SITE (pb) */ pb.plan_id,pb.organization_id,null SR_INVENTORY_ITEM_ID,null UOM_CODE,'||
581 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
582 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
583 'pb.bkt_end_date,pb.bkt_start_date,'||
584 'pb.bucket_index,pb.bucket_type,pb.curr_flag,pb.days_in_bkt,'||
585 'null TRANSACTION_ID,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
586 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
587 'null SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
588 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
589 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
590 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,null STANDARD_COST,'||
591 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
592 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
593 'null PRODUCTION_COST,null PURCHASING_COST,'||
594 'null RESOURCE_ID,null DEPARTMENT_ID,'||
595 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
596 'null RESOURCE_DATE,null UTILIZATION,'||
597 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2, null SR_SUPPLIER_SITE_ID,'||
598 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
599 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
600 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
601 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
602 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
603 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
604 'null PEGGING_ID,null END_PEGGING_ID,'||
605 'pb.created_by,pb.creation_date,pb.last_updated_by,pb.last_update_date,pb.last_update_login,3 union_flag '||
606 'FROM isc_dbi_tmp_plans tmp,msc_plan_buckets' || g_db_link || ' pb '||
607 'WHERE nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
608 'AND tmp.plan_id = pb.plan_id '||
609 'AND tmp.instance_id = pb.sr_instance_id UNION ALL '||
610 'SELECT /*+ DRIVING_SITE (s) parallel(it) parallel(s) parallel(r1) parallel(its2) parallel(tp) */ s.plan_id,s.organization_id,it.sr_inventory_item_id,it.uom_code,'||
611 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
612 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
613 'null BKT_END_DATE,null BKT_START_DATE,'||
614 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
615 's.transaction_id,trunc(s.new_schedule_date) TIME_NEW_SCH_DATE_ID,s.source_organization_id,'||
616 's.source_sr_instance_id,s.source_supplier_id,s.source_supplier_site_id,s.sr_instance_id,'||
617 'tp.sr_tp_id SR_SUPPLIER_ID,s.supplier_id,s.supplier_site_id,it.bom_item_type,'||
618 's.disposition_status_type,it.in_source_plan,its2.item_price,'||
619 's.new_order_quantity,s.new_processing_days,s.new_schedule_date,s.order_type,'||
620 'it.planning_make_buy_code,r1.cfm_routing_flag R_CFM_ROUTING_FLAG,it.standard_cost,'||
621 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
622 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
623 'null PRODUCTION_COST,null PURCHASING_COST,'||
624 'null RESOURCE_ID,null DEPARTMENT_ID,'||
625 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
626 'null RESOURCE_DATE,null UTILIZATION,'||
627 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2, null SR_SUPPLIER_SITE_ID,'||
628 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
629 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
630 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
631 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
632 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
633 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
634 'null PEGGING_ID,null END_PEGGING_ID,'||
635 's.created_by,s.creation_date,s.last_updated_by,s.last_update_date,s.last_update_login,4 union_flag '||
636 'FROM isc_dbi_tmp_plans tmp,msc_supplies'|| g_db_link || ' s,msc_system_items'|| g_db_link ||' it,'||
637 'msc_routings' || g_db_link || ' r1,msc_item_suppliers' || g_db_link || ' its2,'||
638 'msc_tp_id_lid' || g_db_link || ' tp '||
639 'WHERE tmp.plan_id = s.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
640 'AND tmp.instance_id = s.sr_instance_id '||
641 'AND s.organization_id = it.organization_id '||
642 'AND s.plan_id = it.plan_id '||
643 'AND s.inventory_item_id = it.inventory_item_id '||
644 'AND s.sr_instance_id = it.sr_instance_id '||
645 'AND s.plan_id = r1.plan_id(+) '||
646 'AND s.routing_sequence_id = r1.routing_sequence_id(+) '||
647 'AND s.sr_instance_id = r1.sr_instance_id(+) '||
648 'AND s.plan_id = its2.plan_id(+) '||
649 'AND s.organization_id = its2.organization_id(+) '||
650 'AND s.inventory_item_id = its2.inventory_item_id(+) '||
651 'AND s.supplier_id = its2.supplier_id(+) '||
652 'AND s.supplier_site_id = its2.supplier_site_id(+) '||
653 'AND s.sr_instance_id = its2.sr_instance_id(+) '||
654 'AND s.order_type not in (5,27) '||
655 'AND tp.partner_type(+) = 1 '||
656 'AND s.supplier_id = tp.tp_id(+) '||
657 'AND s.sr_instance_id = tp.sr_instance_id(+) UNION ALL '||
658 'SELECT /*+ DRIVING_SITE (s) parallel(it) parallel(s) parallel(r2) parallel(its1) parallel(process) parallel(tp) */ s.plan_id,s.organization_id,it.sr_inventory_item_id,it.uom_code,'||
659 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
660 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
661 'null BKT_END_DATE,null BKT_START_DATE,'||
662 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
663 's.transaction_id,trunc(s.new_schedule_date) TIME_NEW_SCH_DATE_ID,s.source_organization_id,'||
664 's.source_sr_instance_id,s.source_supplier_id,s.source_supplier_site_id,s.sr_instance_id,'||
665 'tp.sr_tp_id SR_SUPPLIER_ID,s.supplier_id,s.supplier_site_id,it.bom_item_type,'||
666 's.disposition_status_type,it.in_source_plan,its1.item_price,'||
667 's.new_order_quantity,s.new_processing_days,s.new_schedule_date,s.order_type,'||
668 'it.planning_make_buy_code,r2.cfm_routing_flag R_CFM_ROUTING_FLAG,it.standard_cost,'||
669 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
670 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
671 'null PRODUCTION_COST,null PURCHASING_COST,'||
672 'null RESOURCE_ID,null DEPARTMENT_ID,'||
673 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
674 'null RESOURCE_DATE,null UTILIZATION,'||
675 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2, null SR_SUPPLIER_SITE_ID,'||
676 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
677 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
678 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
679 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
680 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
681 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
682 'null PEGGING_ID,null END_PEGGING_ID,'||
683 's.created_by,s.creation_date,s.last_updated_by,s.last_update_date,s.last_update_login,4 union_flag '||
684 'FROM isc_dbi_tmp_plans tmp,msc_supplies'|| g_db_link || ' s,msc_system_items'|| g_db_link || ' it,'||
685 'msc_process_effectivity'|| g_db_link || ' process,msc_routings'|| g_db_link || ' r2,'||
686 'msc_item_suppliers'|| g_db_link || ' its1,msc_tp_id_lid'|| g_db_link || ' tp '||
687 'WHERE tmp.plan_id = s.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
688 'AND tmp.instance_id = s.sr_instance_id '||
689 'AND s.organization_id = it.organization_id '||
690 'AND s.plan_id = it.plan_id '||
691 'AND s.inventory_item_id = it.inventory_item_id '||
692 'AND s.sr_instance_id = it.sr_instance_id '||
693 'AND s.plan_id = process.plan_id (+) '||
694 'AND s.process_seq_id = process.process_sequence_id(+) '||
695 'AND s.sr_instance_id = process.sr_instance_id(+) '||
696 'AND process.plan_id = r2.plan_id (+) '||
697 'AND process.routing_sequence_id = r2.routing_sequence_id(+) '||
698 'AND process.sr_instance_id = r2.sr_instance_id(+) '||
699 'AND s.plan_id = its1.plan_id(+) '||
700 'AND s.organization_id = its1.organization_id(+) '||
701 'AND s.inventory_item_id = its1.inventory_item_id(+) '||
702 'AND s.source_supplier_id = its1.supplier_id(+) '||
703 'AND s.source_supplier_site_id = its1.supplier_site_id(+) '||
704 'AND s.sr_instance_id = its1.sr_instance_id(+) '||
705 'AND s.order_type in (5,27) '||
706 'AND tp.partner_type(+) = 1 '||
707 'AND s.source_supplier_id = tp.tp_id(+) '||
708 'AND s.sr_instance_id = tp.sr_instance_id(+) UNION ALL '||
709 'SELECT /*+ DRIVING_SITE (inv) parallel(it) parallel(inv) */ inv.plan_id,inv.organization_id,it.sr_inventory_item_id,it.uom_code,'||
710 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
711 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
712 'null BKT_END_DATE,null BKT_START_DATE,'||
713 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
714 'null TRANSACTION_ID,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
715 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
716 'null SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
717 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
718 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
719 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,null STANDARD_COST,'||
720 'trunc(inv.detail_date) TIME_DETAIL_DATE_ID,inv.carrying_cost,'||
721 'inv.detail_date,inv.inventory_cost,inv.mds_cost,inv.mds_price,inv.mds_quantity,'||
722 'inv.production_cost,inv.purchasing_cost,'||
723 'null RESOURCE_ID,null DEPARTMENT_ID,'||
724 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
725 'null RESOURCE_DATE,null UTILIZATION,'||
726 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2, null SR_SUPPLIER_SITE_ID,'||
727 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
728 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
729 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
730 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
731 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
732 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
733 'null PEGGING_ID,null END_PEGGING_ID,'||
734 'inv.created_by,inv.creation_date,inv.last_updated_by,inv.last_update_date,inv.last_update_login,5 union_flag '||
735 'FROM isc_dbi_tmp_plans tmp,msc_bis_inv_detail' ||g_db_link|| ' inv,msc_system_items' ||g_db_link|| ' it '||
736 'WHERE tmp.plan_id = inv.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
737 'AND tmp.instance_id = inv.sr_instance_id AND nvl(inv.period_type,0)=0 '||
738 'AND inv.plan_id = it.plan_id '||
739 'AND inv.inventory_item_id = it.inventory_item_id '||
740 'AND inv.organization_id = it.organization_id '||
741 'AND inv.sr_instance_id = it.sr_instance_id UNION ALL ';
742
743 l_sel_stmt2 := 'SELECT /*+ DRIVING_SITE (res) parallel(res) parallel(org) */ res.plan_id,res.organization_id,null SR_INVENTORY_ITEM_ID,null UOM_CODE,'||
744 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
745 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
746 'null BKT_END_DATE,null BKT_START_DATE,'||
747 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
748 'null TRANSACTION_ID,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
749 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
750 'null SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
751 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
752 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
753 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,null STANDARD_COST,'||
754 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
755 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
756 'null PRODUCTION_COST,null PURCHASING_COST,'||
757 'decode(org.organization_type,1,res.resource_id/2,2,(res.resource_id-1)/2) RESOURCE_ID,'||
758 'decode(org.organization_type,1,res.department_id/2,2,null) DEPARTMENT_ID,'||
759 'trunc(res.resource_date) TIME_RESOURCE_DATE_ID,res.available_hours,org.organization_type,res.required_hours,'||
760 'res.resource_date,res.utilization,'||
761 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2, null SR_SUPPLIER_SITE_ID,'||
762 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
763 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
764 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
765 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
766 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
767 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
768 'null PEGGING_ID,null END_PEGGING_ID,'||
769 'res.created_by,res.creation_date,res.last_updated_by,res.last_update_date,res.last_update_login,6 union_flag '||
770 'FROM isc_dbi_tmp_plans tmp,msc_bis_res_summary' || g_db_link|| ' res,msc_trading_partners' || g_db_link||' org '||
771 'WHERE tmp.plan_id = res.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
772 'AND tmp.instance_id = res.sr_instance_id '||
773 'AND res.organization_id = org.sr_tp_id '||
774 'AND org.partner_type = 3 '||
775 'AND res.sr_instance_id = org.sr_instance_id UNION ALL '||
776 'SELECT /*+ DRIVING_SITE (ex) */ ex.plan_id,ex.organization_id,it.sr_inventory_item_id,null UOM_CODE,'||
777 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
778 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
779 'null BKT_END_DATE,null BKT_START_DATE,'||
780 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
781 'null TRANSACTION_ID,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
782 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
783 'tp.sr_tp_id SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
784 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
785 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
786 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,null STANDARD_COST,'||
787 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
788 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
789 'null PRODUCTION_COST,null PURCHASING_COST,'||
790 'decode(org.organization_type,1,ex.resource_id/2,2,(ex.resource_id-1)/2) RESOURCE_ID,'||
791 'decode(org.organization_type,1,ex.department_id/2,2,null) DEPARTMENT_ID,'||
792 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,org.organization_type ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
793 'null RESOURCE_DATE,null UTILIZATION,'||
794 'ex.exception_detail_id,ex.exception_type,ex.number1,ex.number2,tp_site.sr_tp_site_id SR_SUPPLIER_SITE_ID,'||
795 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
796 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
797 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
798 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
799 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
800 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
801 'null PEGGING_ID,null END_PEGGING_ID,'||
802 'ex.created_by,ex.creation_date,ex.last_updated_by,ex.last_update_date,ex.last_update_login,7 union_flag '||
803 'FROM isc_dbi_tmp_plans tmp,msc_exception_details'|| g_db_link||' ex,msc_system_items'||g_db_link||' it,'||
804 'MSC_TP_ID_LID'|| g_db_link||' tp,MSC_TP_SITE_ID_LID'|| g_db_link||' tp_site,MSC_TRADING_PARTNERS'|| g_db_link||' org '||
805 'WHERE tmp.plan_id = ex.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
806 'AND tmp.instance_id = ex.sr_instance_id '||
807 'AND ex.sr_instance_id = org.sr_instance_id '||
808 'AND ex.organization_id = org.sr_tp_id '||
809 'AND org.partner_type = 3 '||
810 'AND ex.supplier_id = tp.tp_id(+) '||
811 'AND tp.partner_type(+) = 1 '||
812 'AND ex.supplier_site_id = tp_site.tp_site_id(+) '||
813 'AND tp_site.partner_type(+) = 1 '||
814 'AND ex.plan_id = it.plan_id(+) '||
815 'AND ex.inventory_item_id = it.inventory_item_id(+) '||
816 'AND ex.organization_id = it.organization_id(+) '||
817 'AND ex.sr_instance_id = it.sr_instance_id(+) UNION ALL '||
818 'SELECT /*+ DRIVING_SITE (d) */ d.plan_id,d.organization_id,it.sr_inventory_item_id,it.uom_code,'||
819 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
820 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
821 'null BKT_END_DATE,null BKT_START_DATE,'||
822 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
823 'null TRANSACTION_ID,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
824 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
825 'null SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
826 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
827 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
828 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,it.standard_cost STANDARD_COST,'||
829 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
830 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
831 'null PRODUCTION_COST,null PURCHASING_COST,'||
832 'null RESOURCE_ID,null DEPARTMENT_ID,'||
833 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
834 'null RESOURCE_DATE,null UTILIZATION,'||
835 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2,null SR_SUPPLIER_SITE_ID,'||
836 'd.demand_id,trunc(d.assembly_demand_comp_date) TIME_AS_DMD_COMP_DATE,'||
837 'trunc(nvl(assembly_demand_comp_date,using_assembly_demand_date)) TIME_DMD_DATE_ID,'||
838 'trunc(d.using_assembly_demand_date) TIME_USING_AS_DMD_DATE,d.assembly_demand_comp_date,'||
839 'd.origination_type,d.using_assembly_demand_date,'||
840 'it.average_discount,it.list_price,d.selling_price,d.using_requirement_quantity,'||
841 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
842 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
843 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
844 'null PEGGING_ID,null END_PEGGING_ID,'||
845 'd.created_by,d.creation_date,d.last_updated_by,d.last_update_date,d.last_update_login,8 union_flag '||
846 'FROM isc_dbi_tmp_plans tmp,msc_demands' ||g_db_link|| ' d,msc_system_items'||g_db_link|| ' it '||
847 'WHERE tmp.plan_id = d.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
848 'AND tmp.instance_id = d.sr_instance_id '||
849 'AND d.plan_id = it.plan_id '||
850 'AND d.inventory_item_id = it.inventory_item_id '||
851 'AND d.organization_id = it.organization_id '||
852 'AND d.sr_instance_id = it.sr_instance_id UNION ALL '||
853 'SELECT /*+ DRIVING_SITE (pr) */ null PLAN_ID,pr.organization_id,null SR_INVENTORY_ITEM_ID,null UOM_CODE,'||
854 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
855 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,pr.description,''N'' COMPLETE_FLAG,'||
856 'null BKT_END_DATE,null BKT_START_DATE,'||
857 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
858 'null TRANSACTION_ID,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
859 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
860 'null SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
861 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
862 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
863 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,null STANDARD_COST,'||
864 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
865 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
866 'null PRODUCTION_COST,null PURCHASING_COST,'||
867 'null RESOURCE_ID,null DEPARTMENT_ID,'||
868 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
869 'null RESOURCE_DATE,null UTILIZATION,'||
870 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2,null SR_SUPPLIER_SITE_ID,'||
871 'null DEMAND_ID,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
872 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
873 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
874 'pr.period_set_name,pr.period_name,pr.start_date,pr.end_date,'||
875 'pr.year_start_date,pr.quarter_start_date,pr.period_type,pr.period_year,'||
876 'pr.period_num,pr.quarter_num,pr.entered_period_name,pr.adjustment_period_flag,'||
877 'null PEGGING_ID,null END_PEGGING_ID,'||
878 'pr.created_by,pr.creation_date,pr.last_updated_by,pr.last_update_date,pr.last_update_login,9 union_flag '||
879 'FROM msc_bis_periods' || g_db_link || ' pr,isc_dbi_apps_instances inst '||
880 'WHERE pr.sr_instance_id = inst.instance_id UNION ALL '||
881 'SELECT /*+ DRIVING_SITE (pg) */ pg.plan_id,null ORGANIZATION_ID,null SR_INVENTORY_ITEM_ID,null UOM_CODE,'||
882 'null COMPILE_DESIGNATOR,null CONSTRAINED_FLAG,null CURR_CUTOFF_DATE,null CURR_PLAN_TYPE,'||
883 'null CURR_START_DATE,null CUTOFF_DATE,null DATA_START_DATE,null DESCRIPTION,''N'' COMPLETE_FLAG,'||
884 'null BKT_END_DATE,null BKT_START_DATE,'||
885 'null BUCKET_INDEX,null BUCKET_TYPE,null CURR_FLAG,null DAYS_IN_BKT,'||
886 'pg.transaction_id,null TIME_NEW_SCH_DATE_ID,null SOURCE_ORGANIZATION_ID,'||
887 'null SOURCE_SR_INSTANCE_ID,null SOURCE_SUPPLIER_ID,null SOURCE_SUPPLIER_SITE_ID,null SR_INSTANCE_ID,'||
888 'null SR_SUPPLIER_ID,null SUPPLIER_ID,null SUPPLIER_SITE_ID,null BOM_ITEM_TYPE,'||
889 'null DISPOSITION_STATUS_TYPE,null IN_SOURCE_PLAN,null ITEM_PRICE,'||
890 'null NEW_ORDER_QUANTITY,null NEW_PROCESSING_DAYS,null NEW_SCHEDULE_DATE,null ORDER_TYPE,'||
891 'null PLANNING_MAKE_BUY_CODE,null R_CFM_ROUTING_FLAG,null STANDARD_COST,'||
892 'null TIME_DETAIL_DATE_ID,null CARRYING_COST,'||
893 'null DETAIL_DATE,null INVENTORY_COST,null MDS_COST,null MDS_PRICE,null MDS_QUANTITY,'||
894 'null PRODUCTION_COST,null PURCHASING_COST,'||
895 'null RESOURCE_ID,null DEPARTMENT_ID,'||
896 'null TIME_RESOURCE_DATE_ID,null AVAILABLE_HOURS,null ORGANIZATION_TYPE,null REQUIRED_HOURS,'||
897 'null RESOURCE_DATE,null UTILIZATION,'||
898 'null EXCEPTION_DETAIL_ID,null EXCEPTION_TYPE,null NUMBER1,null NUMBER2,null SR_SUPPLIER_SITE_ID,'||
899 'pg.demand_id,null TIME_AS_DMD_COMP_DATE,null TIME_DMD_DATE_ID,null TIME_USING_AS_DMD_DATE,'||
900 'null ASSEMBLY_DEMAND_COMP_DATE,null ORIGINATION_TYPE,null USING_ASSEMBLY_DEMAND_DATE,'||
901 'null AVERAGE_DISCOUNT,null LIST_PRICE,null SELLING_PRICE,null USING_REQUIREMENT_QUANTITY,'||
902 'null PERIOD_SET_NAME,null PERIOD_NAME,null START_DATE,null END_DATE,'||
903 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
904 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
905 'pg.pegging_id,pg.end_pegging_id,'||
906 'pg.created_by,pg.creation_date,pg.last_updated_by,pg.last_update_date,pg.last_update_login,10 union_flag '||
907 'FROM isc_dbi_tmp_plans tmp,msc_full_pegging' ||g_db_link|| ' pg '||
908 'WHERE tmp.plan_id = pg.plan_id '||
909 'AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
910 'AND tmp.instance_id = pg.sr_instance_id';
911
912 l_in_length := length(l_insert_stmt);
913 BIS_COLLECTION_UTILITIES.Put_Line('The length of the insert statement is '|| l_in_length);
914 l_sel_length1 := length(l_sel_stmt1);
915 BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 1 is '|| l_sel_length1);
916 l_sel_length2 := length(l_sel_stmt2);
917 BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 2 is '|| l_sel_length2);
918
919 EXECUTE IMMEDIATE (l_insert_stmt || l_sel_stmt1 || l_sel_stmt2);
920 l_count := l_count + sql%rowcount;
921 COMMIT;
922
923 FII_UTIL.Stop_Timer;
924 FII_UTIL.Print_Timer('Loaded the base tables in');
925 BIS_COLLECTION_UTILITIES.Put_Line(' ');
926
927 UPDATE isc_dbi_tmp_plans tmp SET constrained_flag = (select constrained_flag from isc_dbi_plans p where p.plan_id = tmp.plan_id);
928 COMMIT;
929
930 FII_UTIL.Start_Timer;
931
932 FOR i in 1..g_small_bases.last LOOP
933 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema, TABNAME => g_small_bases(i));
934 END LOOP;
935
936 FOR j in 1..g_large_bases.last LOOP
937 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema, TABNAME => g_large_bases(j));
938 END LOOP;
939
940 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
941 TABNAME => 'ISC_DBI_PERIODS');
942
943 FII_UTIL.Stop_Timer;
944 FII_UTIL.Print_Timer('Analyzed the base tables in');
945
946 l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_plan_curr_rates';
947 EXECUTE IMMEDIATE l_trunc_stmt;
948 BIS_COLLECTION_UTILITIES.put_line('Table isc_dbi_plan_curr_rates has been truncated.');
949
950 BIS_COLLECTION_UTILITIES.put_line('Begin to retrieve the currency conversion rates.');
951 FII_UTIL.Start_Timer;
952
953 INSERT INTO isc_dbi_plan_curr_rates(
954 ORGANIZATION_ID,
955 FROM_CURRENCY,
956 CONVERSION_DATE,
957 RATE,
958 RATE2)
959 SELECT org.organization_id ORGANIZATION_ID,
960 gsb.currency_code FROM_CURRENCY,
961 g_snapshot_date CONVERSION_DATE,
962 fii_currency.get_global_rate_primary(gsb.currency_code, g_snapshot_date) RATE,
963 fii_currency.get_global_rate_secondary(gsb.currency_code, g_snapshot_date) RATE2
964 FROM (SELECT distinct organization_id
965 FROM isc_dbi_plan_organizations ido,
966 isc_dbi_tmp_plans tmp
967 WHERE bitand(tmp.plan_usage, 2) = 2
968 AND ido.plan_id = tmp.plan_id) org,
969 GL_SETS_OF_BOOKS gsb,
970 HR_ORGANIZATION_INFORMATION hoi
971 WHERE hoi.org_information_context ='Accounting Information'
972 AND hoi.organization_id = org.organization_id
973 AND hoi.org_information1 = to_char(gsb.set_of_books_id);
974
975 FII_UTIL.Stop_Timer;
976 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' currency rates in');
977 COMMIT;
978
979 FII_UTIL.Start_Timer;
980
981 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
982 TABNAME => 'ISC_DBI_PLAN_CURR_RATES');
983
984 FII_UTIL.Stop_Timer;
985 FII_UTIL.Print_Timer('Analyzed ISC_DBI_PLAN_CURR_RATES in');
986
987 RETURN(l_count);
988
989 EXCEPTION
990 WHEN OTHERS THEN
991 g_errbuf := 'Error in Function PULL_DATA : '||sqlerrm;
992 g_retcode := sqlcode;
993 RETURN(-1);
994 END pull_data;
995
996 -- ---------------------
997 -- CHECK_TIME_CONTINUITY
998 -- ---------------------
999
1000 FUNCTION CHECK_TIME_CONTINUITY RETURN NUMBER IS
1001
1002 l_min DATE;
1003 l_max DATE;
1004 l_is_missing BOOLEAN := TRUE;
1005
1006 BEGIN
1007
1008 FII_UTIL.Start_Timer;
1009
1010 SELECT min(p.data_start_date),max(p.cutoff_date)
1011 INTO l_min, l_max
1012 FROM isc_dbi_plans p, isc_dbi_tmp_plans tmp
1013 WHERE p.plan_id = tmp.plan_id;
1014
1015 FII_UTIL.Stop_Timer;
1016 FII_UTIL.Print_Timer('Retrieved the min and max date in ');
1017
1018 FII_UTIL.Start_Timer;
1019
1020 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1021 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1022
1023 IF(l_min IS NOT NULL and l_max IS NOT NULL) THEN
1024 FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1025
1026 IF (l_is_missing) THEN
1027 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1028 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1029 RETURN (-999);
1030 ELSE
1031 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1032 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1033 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1034 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1035 END IF;
1036 END IF;
1037
1038 FII_UTIL.Stop_Timer;
1039 FII_UTIL.Print_Timer('Completed time continuity check in');
1040
1041 RETURN(1);
1042
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 g_errbuf := 'Error in Function CHECK_TIME_CONTINUITY : '||sqlerrm;
1046 g_retcode := sqlcode;
1047 RETURN(-1);
1048 END;
1049
1050 -- ----------------------------------------
1051 -- Identify Dangling Key for Item Dimension
1052 -- ----------------------------------------
1053
1054 FUNCTION IDENTIFY_DANGLING_ITEM RETURN NUMBER IS
1055
1056 CURSOR Dangling_Items IS
1057 SELECT distinct s.sr_inventory_item_id, s.organization_id
1058 FROM isc_dbi_supplies_f s,
1059 isc_dbi_tmp_plans tmp,
1060 eni_oltp_item_star item
1061 WHERE s.plan_id = tmp.plan_id
1062 AND s.sr_inventory_item_id = item.inventory_item_id(+)
1063 AND s.organization_id = item.organization_id(+)
1064 AND item.inventory_item_id IS NULL
1065 UNION
1066 SELECT distinct d.sr_inventory_item_id, d.organization_id
1067 FROM isc_dbi_demands_f d,
1068 isc_dbi_tmp_plans tmp,
1069 eni_oltp_item_star item
1070 WHERE d.plan_id = tmp.plan_id
1071 AND d.sr_inventory_item_id = item.inventory_item_id(+)
1072 AND d.organization_id = item.organization_id(+)
1073 AND item.inventory_item_id IS NULL
1074 UNION
1075 SELECT distinct d.sr_inventory_item_id, d.organization_id
1076 FROM isc_dbi_inv_detail_f d,
1077 isc_dbi_tmp_plans tmp,
1078 eni_oltp_item_star item
1079 WHERE d.plan_id = tmp.plan_id
1080 AND d.sr_inventory_item_id = item.inventory_item_id(+)
1081 AND d.organization_id = item.organization_id(+)
1082 AND item.inventory_item_id IS NULL;
1083
1084 l_item NUMBER;
1085 l_org NUMBER;
1086 l_total NUMBER;
1087
1088 BEGIN
1089 l_total := 0;
1090 OPEN Dangling_Items;
1091 FETCH Dangling_Items INTO l_item, l_org;
1092
1093 IF Dangling_Items%ROWCOUNT <> 0 THEN
1094 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1095 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1096
1097 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1098 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1099 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1100 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1101 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1102 BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1103
1104 WHILE Dangling_Items%FOUND LOOP
1105 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,18,' ')||' - '||RPAD(l_org,18,' '));
1106 FETCH Dangling_Items INTO l_item, l_org;
1107 END LOOP;
1108 BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------+');
1109 ELSE
1110 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1111 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1112 BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1113 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1114 END IF;
1115 l_total := Dangling_Items%ROWCOUNT;
1116 CLOSE Dangling_Items;
1117
1118 RETURN(l_total);
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 g_errbuf := 'Error in Function IDENTIFY_DANGLING_ITEM : '||sqlerrm;
1123 g_retcode := sqlcode;
1124 RETURN(-1);
1125 END;
1126
1127 -- -----------------------------------
1128 -- Reporting of the missing currencies
1129 -- -----------------------------------
1130
1131 FUNCTION REPORT_MISSING_RATE RETURN NUMBER IS
1132
1133 l_sec_curr_def VARCHAR2(1) := isc_dbi_currency_pkg.is_sec_curr_defined;
1134
1135 CURSOR Missing_Currency_Conversion IS
1136 SELECT distinct decode(rate, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1137 from_currency FROM_CURRENCY,
1138 g_global_currency TO_CURRENCY,
1139 g_global_rate_type RATE_TYPE,
1140 decode(rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1141 FROM isc_dbi_plan_curr_rates tmp
1142 WHERE rate < 0
1143 UNION
1144 SELECT distinct decode(rate2, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1145 from_currency FROM_CURRENCY,
1146 g_sec_global_currency TO_CURRENCY,
1147 g_sec_global_rate_type RATE_TYPE,
1148 decode(rate2, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1149 FROM isc_dbi_plan_curr_rates tmp
1150 WHERE rate2 < 0
1151 AND l_sec_curr_def = 'Y';
1152
1153 l_record Missing_Currency_Conversion%ROWTYPE;
1154 l_total NUMBER := 0;
1155
1156 BEGIN
1157
1158 OPEN Missing_Currency_Conversion;
1159 FETCH Missing_Currency_Conversion INTO l_record;
1160
1161 IF Missing_Currency_Conversion%ROWCOUNT <> 0
1162 THEN
1163 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1164 BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1165
1166 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1167 WHILE Missing_Currency_Conversion%FOUND LOOP
1168 l_total := l_total + 1;
1169 BIS_COLLECTION_UTILITIES.writeMissingRate(
1170 l_record.rate_type,
1171 l_record.from_currency,
1172 l_record.to_currency,
1173 l_record.curr_conv_date);
1174 FETCH Missing_Currency_Conversion INTO l_record;
1175 END LOOP;
1176 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1177 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1178
1179 ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1180 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1181 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1182 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1183 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1184 END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1185
1186 CLOSE Missing_Currency_Conversion;
1187
1188 RETURN(l_total);
1189
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 g_errbuf := 'Error in Function REPORT_MISSING_RATE : '||sqlerrm;
1193 g_retcode := sqlcode;
1194 RETURN(-1);
1195 END;
1196
1197 -- --------------
1198 -- DANGLING_CHECK
1199 -- --------------
1200
1201 FUNCTION DANGLING_CHECK RETURN NUMBER IS
1202
1203 l_time_danling NUMBER := 0;
1204 l_item_count NUMBER := 0;
1205 l_dangling NUMBER := 0;
1206 l_miss_conv NUMBER := 0;
1207
1208 BEGIN
1209
1210 -- ----------------------------------------------------------
1211 -- Identify Missing Currency Rate from ISC_DBI_PLAN_CURR_RATES
1212 -- When there is missing rate, exit the collection with error
1213 -- ----------------------------------------------------------
1214
1215 BIS_COLLECTION_UTILITIES.put_line(' ');
1216 BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
1217 FII_UTIL.Start_Timer;
1218
1219 l_miss_conv := REPORT_MISSING_RATE;
1220
1221 FII_UTIL.Stop_Timer;
1222 FII_UTIL.Print_Timer('Completed missing currency check in');
1223
1224 IF (l_miss_conv = -1) THEN
1225 return(-1);
1226 ELSIF (l_miss_conv > 0) THEN
1227 g_errbuf := g_errbuf || 'Collection aborted due to missing currency conversion rates. ';
1228 l_dangling := -999;
1229 END IF;
1230
1231 -- ---------------------
1232 -- CHECK_TIME_CONTINUITY
1233 -- ---------------------
1234
1235 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1236 BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1237
1238 l_time_danling := check_time_continuity;
1239
1240 IF (l_time_danling = -1) THEN
1241 return(-1);
1242 ELSIF (l_time_danling = -999) THEN
1243 g_errbuf := g_errbuf || 'Collection aborted due to dangling keys for time dimension. ';
1244 l_dangling := -999;
1245 END IF;
1246
1247 -- -------------------------------------
1248 -- Check Dangling Key for Item Dimension
1249 -- -------------------------------------
1250
1251 BIS_COLLECTION_UTILITIES.put_line(' ');
1252 BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1253
1254 FII_UTIL.Start_Timer;
1255
1256 l_item_count := IDENTIFY_DANGLING_ITEM;
1257
1258 FII_UTIL.Stop_Timer;
1259 FII_UTIL.Print_Timer('Identified '||l_item_count||' dangling items in');
1260
1261 IF (l_item_count = -1) THEN
1262 return(-1);
1263 ELSIF (l_item_count > 0) THEN
1264 g_errbuf := g_errbuf || 'Collection aborted due to dangling items. ';
1265 l_dangling := -999;
1266 END IF;
1267
1268 IF (l_dangling = -999) THEN
1269 return(-1);
1270 END IF;
1271
1272 UPDATE isc_dbi_plans SET complete_flag = 'Y'
1273 WHERE plan_id IN (select plan_id from isc_dbi_tmp_plans tmp);
1274 COMMIT;
1275
1276 RETURN(1);
1277
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 g_errbuf := 'Error in Function DANGLING_CHECK : '||sqlerrm;
1281 g_retcode := sqlcode;
1282 RETURN(-1);
1283
1284 END dangling_check;
1285
1286 ------------------------------
1287 -- Function: LOAD_BASES_INIT
1288 ------------------------------
1289
1290 FUNCTION load_bases_init RETURN NUMBER IS
1291
1292 l_failure EXCEPTION;
1293
1294 l_row_count NUMBER;
1295 l_trunc_stmt VARCHAR2(500);
1296
1297 BEGIN
1298
1299 IF (CHECK_SETUP = -1)
1300 THEN RAISE l_failure;
1301 END IF;
1302
1303 -- ----------------------------------------------------
1304 -- Truncate all the base tables before the initial load
1305 -- ----------------------------------------------------
1306
1307 BIS_COLLECTION_UTILITIES.put_line(' ');
1308 BIS_COLLECTION_UTILITIES.put_line('Begin to truncate all the base tables');
1309
1310 FOR j in 1..g_large_bases.last LOOP
1311 l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.'|| g_large_bases(j);
1312 EXECUTE IMMEDIATE l_trunc_stmt;
1313 END LOOP;
1314
1315 FOR i in 1..g_small_bases.last LOOP
1316 l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.'|| g_small_bases(i);
1317 EXECUTE IMMEDIATE l_trunc_stmt;
1318 END LOOP;
1319
1320 FII_UTIL.Stop_Timer;
1321 FII_UTIL.Print_Timer('Truncate the base tables in');
1322
1323 -- --------------------------------------------
1324 -- Clean Up the Last Collected Date
1325 -- --------------------------------------------
1326
1327 UPDATE isc_dbi_plan_schedules
1328 SET last_collected_date = NULL;
1329
1330 BIS_COLLECTION_UTILITIES.Put_Line('Cleaned up the last collected date.');
1331
1332 -- --------------------------------------------
1333 -- Identify Plans to be collected
1334 -- --------------------------------------------
1335
1336 l_row_count := IDENTIFY_PLANS;
1337
1338 IF (l_row_count = -1)
1339 THEN RAISE l_failure;
1340 ELSIF (l_row_count = 0) THEN
1341 g_row_count := 0;
1342 ELSE
1343
1344 -- --------------------------------------------
1345 -- Insert data into base tables
1346 -- --------------------------------------------
1347
1348 g_row_count := PULL_DATA;
1349
1350 IF (g_row_count = -1) THEN
1351 RAISE l_failure;
1352 END IF;
1353 END IF;
1354
1355 -- SELECT count(*) FROM isc_dbi_plans WHERE complete_flag = 'N';
1356
1357 IF (DANGLING_CHECK = -1) THEN
1358 RAISE l_failure;
1359 END IF;
1360
1361 RETURN(1);
1362
1363 EXCEPTION
1364
1365 WHEN L_FAILURE THEN
1366 ROLLBACK;
1367 BIS_COLLECTION_UTILITIES.put_line(' ');
1368 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
1369 RETURN(-1);
1370
1371 WHEN OTHERS THEN
1372 ROLLBACK;
1373 g_errbuf := sqlerrm ||' - '||sqlcode;
1374 BIS_COLLECTION_UTILITIES.put_line(' ');
1375 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
1376 RETURN(-1);
1377
1378 END load_bases_init;
1379
1380 ------------------------------
1381 -- Public Function: LOAD_BASES
1382 ------------------------------
1383
1384 FUNCTION load_bases RETURN NUMBER IS
1385
1386 l_failure EXCEPTION;
1387
1388 l_row_count NUMBER;
1389
1390 BEGIN
1391
1392 IF (CHECK_SETUP = -1)
1393 THEN RAISE l_failure;
1394 END IF;
1395
1396 -- --------------------------------------------
1397 -- Identify Plans to be collected
1398 -- --------------------------------------------
1399
1400 l_row_count := IDENTIFY_PLANS;
1401
1402 IF (l_row_count = -1)
1403 THEN RAISE l_failure;
1404 ELSIF (l_row_count = 0) THEN
1405 g_row_count := 0;
1406 ELSE
1407
1408 -- --------------------------------------------
1409 -- Insert data into base tables
1410 -- --------------------------------------------
1411
1412 g_row_count := PULL_DATA;
1413
1414 IF (g_row_count = -1) THEN
1415 RAISE l_failure;
1416 END IF;
1417 END IF;
1418
1419 -- SELECT count(*) FROM isc_dbi_plans WHERE complete_flag = 'N';
1420
1421 IF (DANGLING_CHECK = -1) THEN
1422 RAISE l_failure;
1423 END IF;
1424
1425 RETURN(1);
1426
1427 EXCEPTION
1428
1429 WHEN L_FAILURE THEN
1430 ROLLBACK;
1431 BIS_COLLECTION_UTILITIES.put_line(' ');
1432 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
1433 RETURN(-1);
1434
1435 WHEN OTHERS THEN
1436 ROLLBACK;
1437 g_errbuf := sqlerrm ||' - '||sqlcode;
1438 BIS_COLLECTION_UTILITIES.put_line(' ');
1439 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
1440 RETURN(-1);
1441
1442 END load_bases;
1443
1444 -- --------------------
1445 -- Load Snapshots
1446 -- --------------------
1447
1448 FUNCTION LOAD_SNAPSHOTS RETURN NUMBER IS
1449
1450 partition_exists EXCEPTION;
1451 PRAGMA EXCEPTION_INIT(partition_exists, -14013);
1452
1453 part_value_exists EXCEPTION;
1454 PRAGMA EXCEPTION_INIT(part_value_exists, -14312);
1455
1456 -- item level, change to range partition
1457
1458 CURSOR Delete_List IS
1459 SELECT p.snapshot_id
1460 FROM isc_dbi_tmp_plans tmp, isc_dbi_plan_snapshots p
1461 WHERE bitand(tmp.plan_usage, 2) = 2
1462 AND tmp.plan_id = p.plan_id
1463 AND trunc(tmp.data_start_date) = trunc(p.data_start_date);
1464
1465 CURSOR Snapshot_List IS
1466 SELECT snapshot_id
1467 FROM isc_dbi_tmp_plans tmp
1468 WHERE bitand(plan_usage, 2) = 2
1469 ORDER BY snapshot_id;
1470
1471 l_delete_id NUMBER;
1472 l_add_snapshot_id NUMBER;
1473 l_higher_bound NUMBER;
1474 l_add_stmt VARCHAR2(2000);
1475 l_count NUMBER;
1476 l_rebuild_index_stmt VARCHAR2(2000);
1477 l_index_name VARCHAR2(240);
1478
1479 CURSOR Get_Index_Lists(p_table_name IN VARCHAR2, p_schema_name IN VARCHAR2) IS
1480 SELECT index_name
1481 FROM all_indexes
1482 WHERE table_name = p_table_name
1483 AND owner = p_schema_name;
1484
1485
1486 BEGIN
1487
1488 l_count := 0;
1489 FII_UTIL.Start_Timer;
1490
1491 OPEN Delete_List;
1492 FETCH Delete_List INTO l_delete_id;
1493 IF Delete_List%ROWCOUNT <> 0 THEN
1494 WHILE Delete_List%Found LOOP
1495 BIS_COLLECTION_UTILITIES.Put_Line('Dropping snapshot '|| l_delete_id);
1496 IF (drop_snapshots(l_delete_id) = -1) THEN RETURN(-1); END IF;
1497 FETCH Delete_List INTO l_delete_id;
1498 END LOOP;
1499 END IF;
1500
1501 FII_UTIL.Stop_Timer;
1502 FII_UTIL.Print_Timer('Dropped '|| Delete_List%ROWCOUNT ||' duplicate snapshots in');
1503 CLOSE Delete_List;
1504
1505 IF (g_rebuild_snapshot_index = 'Y') THEN
1506
1507 FOR j in 1..g_large_snapshots.last LOOP
1508 OPEN Get_Index_Lists(g_large_snapshots(j), g_isc_schema);
1509 FETCH Get_Index_Lists INTO l_index_name;
1510 IF Get_Index_Lists%ROWCOUNT <> 0 THEN
1511 WHILE Get_Index_Lists%Found LOOP
1512 FII_UTIL.Start_Timer;
1513 l_rebuild_index_stmt := 'ALTER INDEX '|| g_isc_schema || '.' || l_index_name || ' REBUILD';
1514 EXECUTE IMMEDIATE l_rebuild_index_stmt;
1515 FII_UTIL.Stop_Timer;
1516 FII_UTIL.Print_Timer('Rebuilt index '|| l_index_name || ' in');
1517 FETCH Get_Index_Lists INTO l_index_name;
1518 END LOOP;
1519 END IF;
1520 CLOSE Get_Index_Lists;
1521 END LOOP;
1522
1523 END IF;
1524
1525 UPDATE isc_dbi_tmp_plans SET snapshot_id = isc_dbi_msc_objects_s.nextval WHERE bitand(plan_usage, 2) = 2;
1526 l_count := SQL%ROWCOUNT;
1527 COMMIT;
1528
1529 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1530 BIS_COLLECTION_UTILITIES.Put_Line('Identified '|| l_count || ' snapshots to be collected.');
1531
1532 IF (l_count > 0) THEN
1533
1534 -- --------------
1535 -- Add Partitions
1536 -- --------------
1537
1538 FII_UTIL.Start_Timer;
1539
1540 OPEN Snapshot_List;
1541 FETCH Snapshot_List INTO l_add_snapshot_id;
1542 IF Snapshot_List%ROWCOUNT <> 0 THEN
1543 WHILE Snapshot_List%Found LOOP
1544 BIS_COLLECTION_UTILITIES.Put_Line('Adding snapshot '|| l_add_snapshot_id);
1545 FOR i in 1..g_large_snapshots.last LOOP
1546 BEGIN
1547 l_add_stmt := 'ALTER TABLE '||g_isc_schema||'.'||g_large_snapshots(i)||' ADD PARTITION s_'|| l_add_snapshot_id ||' VALUES LESS THAN ('''|| to_char(l_add_snapshot_id+1) ||''')';
1548 EXECUTE IMMEDIATE l_add_stmt;
1549 EXCEPTION
1550 WHEN partition_exists THEN
1551 BIS_COLLECTION_UTILITIES.put_line('The partition s_'||l_add_snapshot_id||' of table '||g_large_snapshots(i)||' already exists.');
1552 NULL;
1553 WHEN part_value_exists THEN
1554 BIS_COLLECTION_UTILITIES.put_line('The value '||l_add_snapshot_id||' already exists in another partition of table '||g_large_snapshots(i));
1555 NULL;
1556 END;
1557 END LOOP;
1558 FETCH Snapshot_List INTO l_add_snapshot_id;
1559 END LOOP;
1560 END IF;
1561 FII_UTIL.Stop_Timer;
1562 FII_UTIL.Print_Timer('Added '|| Snapshot_List%ROWCOUNT ||' partitions in');
1563 CLOSE Snapshot_List;
1564
1565 -- ----------------------------------
1566 -- Collect ISC_DBI_PLAN_SNAPSHOTS
1567 -- ----------------------------------
1568
1569 BIS_COLLECTION_UTILITIES.put_line(' ');
1570 BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_plan_snapshots.');
1571 FII_UTIL.Start_Timer;
1572
1573 INSERT
1574 INTO isc_dbi_plan_snapshots F(
1575 SNAPSHOT_ID,
1576 PLAN_ID,
1577 ORGANIZATION_ID,
1578 COMPILE_DESIGNATOR,
1579 CONSTRAINED_FLAG,
1580 CURR_PLAN_TYPE,
1581 CUTOFF_DATE,
1582 DATA_START_DATE,
1583 DESCRIPTION,
1584 ORG_CNT,
1585 SNAPSHOT_DATE)
1586 SELECT tmp.snapshot_id, ip.plan_id, ip.organization_id, ip.compile_designator, ip.constrained_flag,
1587 ip.curr_plan_type, ip.cutoff_date, ip.data_start_date,
1588 ip.description, count(*), g_snapshot_date
1589 FROM isc_dbi_tmp_plans tmp,
1590 isc_dbi_plans ip,
1591 isc_dbi_plan_organizations ipo
1592 WHERE tmp.plan_id = ip.plan_id
1593 AND bitand(tmp.plan_usage, 2) = 2
1594 AND ip.plan_id = ipo.plan_id
1595 GROUP BY tmp.snapshot_id, ip.plan_id, ip.organization_id, ip.compile_designator, ip.constrained_flag, ip.curr_plan_type,
1596 ip.cutoff_date, ip.data_start_date, ip.description;
1597
1598 FII_UTIL.Stop_Timer;
1599 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_plan_snapshots in');
1600 -- COMMIT;
1601
1602 -- ----------------------------------
1603 -- Collect ISC_DBI_PLAN_ORG_SNAPSHOTS
1604 -- ----------------------------------
1605
1606 BIS_COLLECTION_UTILITIES.put_line(' ');
1607 BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_plan_org_snapshots.');
1608 FII_UTIL.Start_Timer;
1609
1610 INSERT
1611 INTO isc_dbi_plan_org_snapshots(
1612 SNAPSHOT_ID,
1613 ORGANIZATION_ID)
1614 SELECT tmp.snapshot_id, ipo.organization_id
1615 FROM isc_dbi_tmp_plans tmp,
1616 isc_dbi_plan_organizations ipo
1617 WHERE tmp.plan_id = ipo.plan_id
1618 AND bitand(tmp.plan_usage, 2) = 2;
1619
1620 FII_UTIL.Stop_Timer;
1621 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_plan_organizations in');
1622 -- COMMIT;
1623
1624 -- ----------------------------------
1625 -- Collect ISC_DBI_SUPPLIES_SNAPSHOTS
1626 -- ----------------------------------
1627
1628 BIS_COLLECTION_UTILITIES.put_line(' ');
1629 BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_supplies_snapshots.');
1630 FII_UTIL.Start_Timer;
1631
1632 -- item level only, move category to MV
1633
1634 INSERT INTO isc_dbi_supplies_snapshots F(
1635 SNAPSHOT_ID,
1636 ORGANIZATION_ID,
1637 SR_INVENTORY_ITEM_ID,
1638 START_DATE,
1639 PERIOD_TYPE_ID,
1640 SR_SUPPLIER_ID,
1641 PURCHASING_COST,
1642 PURCHASING_COST_G,
1643 PURCHASING_COST_G1,
1644 UOM_CODE)
1645 SELECT /*+ parallel(ids) */ tmp.snapshot_id SNAPSHOT_ID,
1646 ids.organization_id ORGANIZATION_ID,
1647 ids.sr_inventory_item_id SR_INVENTORY_ITEM_ID,
1648 decode(grouping_id(ent_year_start_date, ent_qtr_start_date, ent_period_start_date),
1649 6, ent_period_start_date, 5, ent_qtr_start_date, 3, ent_year_start_date) START_DATE,
1650 decode(grouping_id(ent_year_start_date, ent_qtr_start_date, ent_period_start_date),
1651 6, 32, 5, 64, 3, 128) PERIOD_TYPE_ID,
1652 nvl(ids.sr_supplier_id, -1) SR_SUPPLIER_ID,
1653 sum(nvl(ids.new_order_quantity,0)*nvl(ids.item_price,nvl(ids.standard_cost,0))) PURCHASING_COST,
1654 sum(nvl(ids.new_order_quantity,0)*nvl(ids.item_price,nvl(ids.standard_cost,0))*curr.rate) PURCHASING_COST_G,
1655 sum(nvl(ids.new_order_quantity,0)*nvl(ids.item_price,nvl(ids.standard_cost,0))*curr.rate2) PURCHASING_COST_G1,
1656 ids.uom_code UOM_CODE
1657 FROM isc_dbi_tmp_plans tmp,
1658 isc_dbi_supplies_f ids,
1659 isc_dbi_plan_curr_rates curr,
1660 fii_time_day time
1661 WHERE tmp.plan_id = ids.plan_id
1662 AND bitand(tmp.plan_usage, 2) = 2
1663 AND ids.organization_id = curr.organization_id
1664 AND ids.time_new_sch_date_id = time.report_date
1665 AND ((ids.order_type = 5 AND ids. source_organization_id IS NULL)
1666 OR ids.order_type in (1, 2, 8))
1667 AND ids.disposition_status_type <> 2
1668 GROUP BY tmp.snapshot_id, ids.organization_id, nvl(ids.sr_supplier_id, -1), ids.sr_inventory_item_id, ids.uom_code,
1669 grouping sets(ent_year_start_date, ent_qtr_start_date, ent_period_start_date);
1670
1671 FII_UTIL.Stop_Timer;
1672 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_supplies_snapshots in');
1673 -- COMMIT;
1674
1675
1676 -- ----------------------------------------
1677 -- Collect ISC_DBI_SHORTFALL_SNAPSHOTS
1678 --
1679 -- Reason Type: -1 - Unassigned
1680 -- 0 - On-time Demands
1681 -- 1 - Item Related
1682 -- 2 - Resource Related
1683 -- 3 - Transportation Related
1684 -- ----------------------------------------
1685
1686 BIS_COLLECTION_UTILITIES.put_line(' ');
1687 BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_shortfall_snapshots for unconstrained plans.');
1688 FII_UTIL.Start_Timer;
1689
1690 INSERT
1691 INTO isc_dbi_shortfall_snapshots F(
1692 SNAPSHOT_ID,
1693 ORGANIZATION_ID,
1694 START_DATE,
1695 PERIOD_TYPE_ID,
1696 REASON_TYPE,
1697 DMD_ITEM_ID,
1698 ORGANIZATION_TYPE,
1699 R_ITEM_ID,
1700 R_SUPPLIER_ID,
1701 R_SUPPLIER_SITE_ID,
1702 R_RESOURCE_ID,
1703 R_ORG_ID,
1704 R_DEPARTMENT_ID,
1705 REV_TEMP,
1706 REV_TEMP_G,
1707 REV_TEMP_G1,
1708 COST_TEMP,
1709 COST_TEMP_G,
1710 COST_TEMP_G1,
1711 LATE_LINES_TEMP,
1712 TOTAL_LINES_TEMP,
1713 REV_SHORTFALL,
1714 REV_SHORTFALL_G,
1715 REV_SHORTFALL_G1,
1716 COST_SHORTFALL,
1717 COST_SHORTFALL_G,
1718 COST_SHORTFALL_G1,
1719 UOM_CODE)
1720 SELECT /*+ parallel(f) use_hash(TIME,CURR) */ f.snapshot_id SNAPSHOT_ID, f.organization_id,
1721 decode(grouping_id(ent_year_start_date, ent_qtr_start_date, ent_period_start_date),
1722 6, ent_period_start_date, 5, ent_qtr_start_date, 3, ent_year_start_date) START_DATE,
1723 decode(grouping_id(ent_year_start_date, ent_qtr_start_date, ent_period_start_date),
1724 6, 32, 5, 64, 3, 128) PERIOD_TYPE_ID,
1725 f.reason_type, f.dmd_item_id, f.organization_type,
1726 f.r_item_id, f.r_supplier_id, f.r_supplier_site_id, f.r_resource_id, f.r_org_id, f.r_department_id,
1727 sum(revenue/cnt) REV_TEMP, sum(revenue*curr.rate/cnt) REV_TEMP_G, sum(revenue*curr.rate2/cnt) REV_TEMP_G1,
1728 sum(cost/cnt) COST_TEMP, sum(cost*curr.rate/cnt) COST_TEMP_G, sum(cost*curr.rate2/cnt) COST_TEMP_G1,
1729 sum(late_lines/cnt) LATE_LINES_TEMP, sum(1/cnt) TOTAL_LINES_TEMP,
1730 sum(decode(reason_cnt, 0, 0, revenue/reason_cnt)) REV_SHORTFALL,
1731 sum(decode(reason_cnt, 0, 0, revenue*curr.rate/reason_cnt)) REV_SHORTFALL_G,
1732 sum(decode(reason_cnt, 0, 0, revenue*curr.rate2/reason_cnt)) REV_SHORTFALL_G1,
1733 sum(decode(reason_cnt, 0, 0, cost/reason_cnt)) COST_SHORTFALL,
1734 sum(decode(reason_cnt, 0, 0, cost*curr.rate/reason_cnt)) COST_SHORTFALL_G,
1735 sum(decode(reason_cnt, 0, 0, cost*curr.rate2/reason_cnt)) COST_SHORTFALL_G1,
1736 f.uom_code
1737 FROM (
1738 SELECT /*+ ordered no_merge use_hash(ID,REASON) parallel(ID) paralell(REASON)
1739 pq_distribute(ID,hash,hash) pq_distribute(REASON,hash,hash) */ tmp.snapshot_id,
1740 id.demand_id,
1741 id.time_dmd_date_id,
1742 id.sr_inventory_item_id DMD_ITEM_ID,
1743 id.organization_id,
1744 reason.organization_type,
1745 reason.sr_inventory_item_id R_ITEM_ID,
1746 reason.sr_supplier_id R_SUPPLIER_ID,
1747 reason.sr_supplier_site_id R_SUPPLIER_SITE_ID,
1748 reason.organization_id R_ORG_ID,
1749 reason.resource_id R_RESOURCE_ID,
1750 reason.department_id R_DEPARTMENT_ID,
1751 nvl(reason.reason_type, 0) REASON_TYPE,
1752 avg(decode(id.origination_type,
1753 6, nvl(id.selling_price * id.using_requirement_quantity,0),
1754 30, nvl(id.selling_price * id.using_requirement_quantity,0),
1755 nvl((id.list_price * (100-id.average_discount)/100 * id.using_requirement_quantity),0))) REVENUE,
1756 avg(nvl(id.standard_cost * id.using_requirement_quantity,0)) COST,
1757 sum(1) over (partition by tmp.snapshot_id, id.demand_id) CNT,
1758 sum(decode(reason.reason_type, null, 0, 1)) over (partition by tmp.snapshot_id, id.demand_id) REASON_CNT,
1759 avg(decode(reason.reason_type, null, 0, 1)) LATE_LINES,
1760 id.uom_code
1761 FROM isc_dbi_tmp_plans tmp,
1762 isc_dbi_demands_f id,
1763 (SELECT /*+ ordered */ peg1.plan_id, peg1.demand_id,
1764 r.organization_type, r.sr_inventory_item_id, r.sr_supplier_id, r.sr_supplier_site_id,
1765 r.organization_id, r.resource_id, r.department_id,
1766 decode(r.exception_type, 23, 2, 1) reason_type
1767 FROM isc_dbi_tmp_plans p, isc_dbi_exception_details_f r, isc_dbi_full_pegging_f peg1
1768 WHERE bitand(p.plan_usage, 2) = 2
1769 AND p.plan_id = r.plan_id
1770 AND p.constrained_flag = 2
1771 AND r.exception_type in (15, 16, 23, 42)
1772 AND r.plan_id = peg1.plan_id
1773 AND r.number2 = peg1.pegging_id) reason
1774 WHERE bitand(tmp.plan_usage, 2) = 2
1775 AND tmp.plan_id = id.plan_id
1776 AND tmp.constrained_flag = 2
1777 AND id.origination_type in (6,7,8,9,10,11,12,15,22,24,27,29,30)
1778 AND reason.plan_id(+) = id.plan_id
1779 AND reason.demand_id(+) = id.demand_id
1780 GROUP BY tmp.snapshot_id, id.organization_id, id.uom_code, id.demand_id, id.time_dmd_date_id, id.sr_inventory_item_id,
1781 reason.organization_type, reason.sr_inventory_item_id, reason.sr_supplier_id, reason.sr_supplier_site_id,
1782 reason.organization_id, reason.resource_id, reason.department_id, reason.reason_type) f,
1783 isc_dbi_plan_curr_rates curr,
1784 fii_time_day time
1785 WHERE f.time_dmd_date_id = time.report_date
1786 AND f.organization_id = curr.organization_id
1787 GROUP BY f.snapshot_id, f.organization_id, f.dmd_item_id, f.uom_code, f.organization_type, f.reason_type,
1788 f.r_item_id, f.r_supplier_id, f.r_supplier_site_id, f.r_resource_id, f.r_org_id, f.r_department_id,
1789 grouping sets(ent_year_start_date, ent_qtr_start_date, ent_period_start_date);
1790
1791 FII_UTIL.Stop_Timer;
1792 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_shortfall_snapshots in');
1793
1794 BIS_COLLECTION_UTILITIES.put_line(' ');
1795 BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_shortfall_snapshots for constrained plans.');
1796 FII_UTIL.Start_Timer;
1797
1798 INSERT
1799 INTO isc_dbi_shortfall_snapshots F(
1800 SNAPSHOT_ID,
1801 ORGANIZATION_ID,
1802 START_DATE,
1803 PERIOD_TYPE_ID,
1804 REASON_TYPE,
1805 DMD_ITEM_ID,
1806 ORGANIZATION_TYPE,
1807 R_ITEM_ID,
1808 R_SUPPLIER_ID,
1809 R_SUPPLIER_SITE_ID,
1810 R_RESOURCE_ID,
1811 R_ORG_ID,
1812 R_DEPARTMENT_ID,
1813 REV_TEMP,
1814 REV_TEMP_G,
1815 REV_TEMP_G1,
1816 COST_TEMP,
1817 COST_TEMP_G,
1818 COST_TEMP_G1,
1819 LATE_LINES_TEMP,
1820 TOTAL_LINES_TEMP,
1821 REV_SHORTFALL,
1822 REV_SHORTFALL_G,
1823 REV_SHORTFALL_G1,
1824 COST_SHORTFALL,
1825 COST_SHORTFALL_G,
1826 COST_SHORTFALL_G1,
1827 UOM_CODE)
1828 SELECT /*+ parallel(f) use_hash(TIME,CURR) */ f.snapshot_id SNAPSHOT_ID, f.organization_id,
1829 decode(grouping_id(ent_year_start_date, ent_qtr_start_date, ent_period_start_date),
1830 6, ent_period_start_date, 5, ent_qtr_start_date, 3, ent_year_start_date) START_DATE,
1831 decode(grouping_id(ent_year_start_date, ent_qtr_start_date, ent_period_start_date),
1832 6, 32, 5, 64, 3, 128) PERIOD_TYPE_ID,
1833 f.reason_type, f.dmd_item_id, f.organization_type,
1834 f.r_item_id, f.r_supplier_id, f.r_supplier_site_id, f.r_resource_id, f.r_org_id, f.r_department_id,
1835 sum(revenue/cnt) REV_TEMP, sum(revenue*curr.rate/cnt) REV_TEMP_G, sum(revenue*curr.rate2/cnt) REV_TEMP_G1,
1836 sum(cost/cnt) COST_TEMP, sum(cost*curr.rate/cnt) COST_TEMP_G, sum(cost*curr.rate2/cnt) COST_TEMP_G1,
1837 sum(late_lines/cnt) LATE_LINES_TEMP, sum(1/cnt) TOTAL_LINES_TEMP,
1838 sum(decode(reason_cnt, 0, 0, revenue/reason_cnt)) REV_SHORTFALL,
1839 sum(decode(reason_cnt, 0, 0, revenue*curr.rate/reason_cnt)) REV_SHORTFALL_G,
1840 sum(decode(reason_cnt, 0, 0, revenue*curr.rate2/reason_cnt)) REV_SHORTFALL_G1,
1841 sum(decode(reason_cnt, 0, 0, cost/reason_cnt)) COST_SHORTFALL,
1842 sum(decode(reason_cnt, 0, 0, cost*curr.rate/reason_cnt)) COST_SHORTFALL_G,
1843 sum(decode(reason_cnt, 0, 0, cost*curr.rate2/reason_cnt)) COST_SHORTFALL_G1,
1844 f.uom_code
1845 FROM (
1846 SELECT /*+ ordered no_merge use_hash(ID,REASON) parallel(ID) paralell(REASON)
1847 pq_distribute(ID,hash,hash) pq_distribute(REASON,hash,hash) */ tmp.snapshot_id,
1848 id.demand_id,
1849 id.time_dmd_date_id,
1850 id.sr_inventory_item_id DMD_ITEM_ID,
1851 id.organization_id,
1852 reason.organization_type,
1853 reason.sr_inventory_item_id R_ITEM_ID,
1854 reason.sr_supplier_id R_SUPPLIER_ID,
1855 reason.sr_supplier_site_id R_SUPPLIER_SITE_ID,
1856 reason.organization_id R_ORG_ID,
1857 reason.resource_id R_RESOURCE_ID,
1858 reason.department_id R_DEPARTMENT_ID,
1859 decode(iex.number1, null, 0, nvl(reason.reason_type, -1)) REASON_TYPE,
1860 avg(decode(id.origination_type,
1861 6, nvl(id.selling_price * id.using_requirement_quantity,0),
1862 30, nvl(id.selling_price * id.using_requirement_quantity,0),
1863 nvl((id.list_price * (100-id.average_discount)/100 * id.using_requirement_quantity),0))) REVENUE,
1864 avg(nvl(id.standard_cost * id.using_requirement_quantity,0)) COST,
1865 sum(1) over (partition by tmp.snapshot_id, id.demand_id) CNT,
1866 sum(decode(iex.number1, null, 0, 1)) over (partition by tmp.snapshot_id, id.demand_id) REASON_CNT,
1867 avg(CASE WHEN iex.late_lines >= 1 THEN 1 ELSE 0 END) LATE_LINES,
1868 id.uom_code
1869 FROM isc_dbi_tmp_plans tmp,
1870 isc_dbi_demands_f id,
1871 (SELECT plan_id, number1, sum(decode(exception_type, 13, 1, 14, 1, 24, 1, 26, 1, 0)) LATE_LINES
1872 FROM isc_dbi_exception_details_f ex
1873 WHERE ex.exception_type in (13, 14, 24, 26, 52) AND ex.number1 is not null
1874 GROUP BY plan_id, number1) iex,
1875 (SELECT /*+ ordered */ peg1.plan_id, peg.demand_id,
1876 r.organization_type, r.sr_inventory_item_id, r.sr_supplier_id, r.sr_supplier_site_id,
1877 r.organization_id, r.resource_id, r.department_id,
1878 decode(r.exception_type,36,2,53,2,58,2,60,2,63,2,40,3,55,3,56,3,61,3,1) reason_type
1879 FROM isc_dbi_tmp_plans p, isc_dbi_exception_details_f r, isc_dbi_full_pegging_f peg1, isc_dbi_full_pegging_f peg
1880 WHERE bitand(p.plan_usage, 2) = 2
1881 AND p.constrained_flag = 1
1882 AND p.plan_id = r.plan_id
1883 AND r.exception_type in (9, 36, 37, 40, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 66, 67)
1884 AND r.plan_id = peg1.plan_id
1885 AND r.number1 = peg1.transaction_id
1886 AND peg.plan_id = peg1.plan_id
1887 AND peg.pegging_id = peg1.end_pegging_id) reason
1888 WHERE tmp.plan_id = id.plan_id
1889 AND bitand(tmp.plan_usage, 2) = 2
1890 AND tmp.constrained_flag = 1
1891 AND id.demand_id = iex.number1(+)
1892 AND id.plan_id = iex.plan_id(+)
1893 AND id.origination_type in (6,7,8,9,10,11,12,15,22,24,27,29,30)
1894 AND reason.plan_id(+) = id.plan_id
1895 AND reason.demand_id(+) = id.demand_id
1896 GROUP BY tmp.snapshot_id, id.organization_id, id.uom_code, id.demand_id, id.time_dmd_date_id, id.sr_inventory_item_id,
1897 iex.number1, reason.organization_type, reason.sr_inventory_item_id, reason.sr_supplier_id, reason.sr_supplier_site_id,
1898 reason.organization_id, reason.resource_id, reason.department_id, reason.reason_type) f,
1899 isc_dbi_plan_curr_rates curr,
1900 fii_time_day time
1901 WHERE f.time_dmd_date_id = time.report_date
1902 AND f.organization_id = curr.organization_id
1903 GROUP BY f.snapshot_id, f.organization_id, f.dmd_item_id, f.uom_code, f.organization_type, f.reason_type,
1904 f.r_item_id, f.r_supplier_id, f.r_supplier_site_id, f.r_resource_id, f.r_org_id, f.r_department_id,
1905 grouping sets(ent_year_start_date, ent_qtr_start_date, ent_period_start_date);
1906
1907 FII_UTIL.Stop_Timer;
1908 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_shortfall_snapshots in');
1909 -- COMMIT;
1910
1911 -- ------------------------------------
1912 -- Collect ISC_DBI_INV_DETAIL_SNAPSHOTS
1913 -- ------------------------------------
1914
1915 BIS_COLLECTION_UTILITIES.put_line(' ');
1916 BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_inv_detail_snapshots.');
1917 FII_UTIL.Start_Timer;
1918
1919 -- item level only
1920
1921 INSERT /*+ APPEND PARALLEL(F) */
1922 INTO isc_dbi_inv_detail_snapshots f(
1923 SNAPSHOT_ID,
1924 ORGANIZATION_ID,
1925 SR_INVENTORY_ITEM_ID,
1926 START_DATE,
1927 PERIOD_TYPE_ID,
1928 UOM_CODE,
1929 CARRYING_COST,
1930 CARRYING_COST_G,
1931 CARRYING_COST_G1,
1932 COST_SHORTFALL,
1933 COST_SHORTFALL_G,
1934 COST_SHORTFALL_G1,
1935 INVENTORY_COST,
1936 INVENTORY_COST_G,
1937 INVENTORY_COST_G1,
1938 MDS_COST,
1939 MDS_COST_G,
1940 MDS_COST_G1,
1941 MDS_PRICE,
1942 MDS_PRICE_G,
1943 MDS_PRICE_G1,
1944 MDS_QUANTITY,
1945 PRODUCTION_COST,
1946 PRODUCTION_COST_G,
1947 PRODUCTION_COST_G1,
1948 PURCHASING_COST,
1949 PURCHASING_COST_G,
1950 PURCHASING_COST_G1,
1951 LATE_LINES,
1952 REV_SHORTFALL,
1953 REV_SHORTFALL_G,
1954 REV_SHORTFALL_G1,
1955 TOTAL_LINES,
1956 UNION1_FLAG,
1957 UNION2_FLAG)
1958 SELECT snapshot_id, organization_id, sr_inventory_item_id, start_date, period_type_id, uom_code,
1959 sum(carrying_cost), sum(carrying_cost_g), sum(carrying_cost_g1), sum(cost_shortfall), sum(cost_shortfall_g), sum(cost_shortfall_g1), sum(inventory_cost), sum(inventory_cost_g), sum(inventory_cost_g1),
1960 sum(mds_cost), sum(mds_cost_g), sum(mds_cost_g1), sum(mds_price), sum(mds_price_g), sum(mds_price_g1), sum(mds_quantity),
1961 sum(production_cost), sum(production_cost_g), sum(production_cost_g1), sum(purchasing_cost), sum(purchasing_cost_g), sum(purchasing_cost_g1),
1962 sum(late_lines), sum(rev_shortfall), sum(rev_shortfall_g), sum(rev_shortfall_g1), sum(total_lines), sum(union1_flag), sum(union2_flag)
1963 FROM (SELECT fact.snapshot_id, fact.organization_id, fact.sr_inventory_item_id,
1964 decode(grouping_id(fact.ent_year_start_date, fact.ent_qtr_start_date, fact.ent_period_start_date),
1965 6, fact.ent_period_start_date, 5, fact.ent_qtr_start_date, 3, fact.ent_year_start_date) START_DATE,
1966 decode(grouping_id(fact.ent_year_start_date, fact.ent_qtr_start_date, fact.ent_period_start_date),
1967 6, 32, 5, 64, 3, 128) PERIOD_TYPE_ID, uom_code,
1968 sum(carrying_cost) CARRYING_COST, sum(carrying_cost*curr.rate) CARRYING_COST_G, sum(carrying_cost*curr.rate2) CARRYING_COST_G1,
1969 sum(cost_shortfall) COST_SHORTFALL, sum(cost_shortfall*curr.rate) COST_SHORTFALL_G, sum(cost_shortfall*curr.rate2) COST_SHORTFALL_G1,
1970 sum(decode(report_date, ent_period_start_date,inventory_cost, 0)) INVENTORY_COST,
1971 sum(decode(report_date, ent_period_start_date,inventory_cost*curr.rate, 0)) INVENTORY_COST_G,
1972 sum(decode(report_date, ent_period_start_date,inventory_cost*curr.rate2, 0)) INVENTORY_COST_G1,
1973 sum(mds_cost) MDS_COST, sum(mds_cost*curr.rate) MDS_COST_G, sum(mds_cost*curr.rate2) MDS_COST_G1,
1974 sum(mds_price) MDS_PRICE, sum(mds_price*curr.rate) MDS_PRICE_G, sum(mds_price*curr.rate2) MDS_PRICE_G1, sum(MDS_QUANTITY) MDS_QUANTITY,
1975 sum(production_cost) PRODUCTION_COST, sum(production_cost*curr.rate) PRODUCTION_COST_G, sum(production_cost*curr.rate2) PRODUCTION_COST_G1,
1976 sum(purchasing_cost) PURCHASING_COST, sum(purchasing_cost*curr.rate) PURCHASING_COST_G, sum(purchasing_cost*curr.rate2) PURCHASING_COST_G1,
1977 sum(late_lines) LATE_LINES, sum(rev_shortfall) REV_SHORTFALL, sum(rev_shortfall*curr.rate) REV_SHORTFALL_G, sum(rev_shortfall*curr.rate2) REV_SHORTFALL_G1,
1978 sum(total_lines) TOTAL_LINES, sum(union1_flag) UNION1_FLAG, sum(union2_flag) UNION2_FLAG
1979 FROM (SELECT /*+ parallel(iinv) */ tmp.snapshot_id, iinv.organization_id, iinv.sr_inventory_item_id,
1980 iinv.uom_code UOM_CODE, time1.report_date,
1981 time1.ent_period_start_date, time1.ent_qtr_start_date, time1.ent_year_start_date,
1982 nvl(iinv.carrying_cost,0)/(per.end_date - per.start_date + 1) CARRYING_COST,
1983 0 COST_SHORTFALL,
1984 nvl(iinv.inventory_cost,0) INVENTORY_COST,
1985 0 MDS_COST,
1986 0 MDS_PRICE,
1987 nvl(iinv.mds_quantity,0)/(per.end_date - per.start_date + 1) MDS_QUANTITY,
1988 nvl(iinv.production_cost,0)/(per.end_date - per.start_date + 1) PRODUCTION_COST,
1989 0 PURCHASING_COST,
1990 0 LATE_LINES,
1991 0 REV_SHORTFALL,
1992 0 TOTAL_LINES,
1993 1 UNION1_FLAG,
1994 0 UNION2_FLAG
1995 FROM isc_dbi_tmp_plans tmp,
1996 isc_dbi_inv_detail_f iinv,
1997 isc_dbi_periods per,
1998 fii_time_day time1
1999 WHERE tmp.plan_id = iinv.plan_id
2000 AND bitand(tmp.plan_usage, 2) = 2
2001 AND iinv.organization_id = per.organization_id
2002 AND per.adjustment_period_flag = 'N'
2003 AND iinv.detail_date = per.start_date
2004 AND time1.report_date between per.start_date and per.end_date) fact,
2005 isc_dbi_plan_curr_rates curr
2006 WHERE fact.organization_id = curr.organization_id
2007 GROUP BY fact.snapshot_id, fact.organization_id, fact.sr_inventory_item_id, fact.uom_code,
2008 grouping sets(fact.ent_year_start_date, fact.ent_qtr_start_date, fact.ent_period_start_date)
2009 UNION ALL
2010 SELECT sup.snapshot_id, organization_id, sr_inventory_item_id, start_date, period_type_id, uom_code UOM_CODE,
2011 0 CARRYING_COST, 0 CARRYING_COST_G, 0 CARRYING_COST_G1, 0 COST_SHORTFALL, 0 COST_SHORTFALL_G, 0 COST_SHORTFALL_G1, 0 INVENTORY_COST, 0 INVENTORY_COST_G, 0 INVENTORY_COST_G1,
2012 0 MDS_COST, 0 MDS_COST_G, 0 MDS_COST_G1, 0 MDS_PRICE, 0 MDS_PRICE_G, 0 MDS_PRICE_G1, 0 MDS_QUANTITY, 0 PRODUCTION_COST, 0 PRODUCTION_COST_G, 0 PRODUCTION_COST_G1,
2013 purchasing_cost PURCHASING_COST, purchasing_cost_g PURCHASING_COST_G, purchasing_cost_g1 PURCHASING_COST_G1,
2014 0 LATE_LINES, 0 REV_SHORTFALL, 0 REV_SHORTFALL_G, 0 REV_SHORTFALL_G1, 0 TOTAL_LINES, 1 UNION1_FLAG, 0 UNION2_FLAG
2015 FROM isc_dbi_tmp_plans tmp,
2016 isc_dbi_supplies_snapshots sup
2017 WHERE tmp.snapshot_id = sup.snapshot_id
2018 AND bitand(tmp.plan_usage, 2) = 2
2019 UNION ALL
2020 SELECT sh.snapshot_id, sh.organization_id, sh.dmd_item_id, sh.start_date, sh.period_type_id, sh.uom_code,
2021 0 CARRYING_COST, 0 CARRYING_COST_G, 0 CARRYING_COST_G1, sh.cost_shortfall COST_SHORTFALL, sh.cost_shortfall_g COST_SHORTFALL_G, sh.cost_shortfall_g1 COST_SHORTFALL_G1,
2022 0 INVENTORY_COST, 0 INVENTORY_COST_G, 0 INVENTORY_COST_G1, cost_temp MDS_COST, cost_temp_g MDS_COST_G, cost_temp_g1 MDS_COST_G1,
2023 rev_temp MDS_PRICE, rev_temp_g MDS_PRICE_G, rev_temp_g1 MDS_PRICE_G1, 0 MDS_QUANTITY,
2024 0 PRODUCTION_COST, 0 PRODUCTION_COST_G, 0 PRODUCTION_COST_G1, 0 PURCHASING_COST, 0 PURCHASING_COST_G, 0 PURCHASING_COST_G1, late_lines_temp LATE_LINES,
2025 sh.rev_shortfall REV_SHORTFALL, sh.rev_shortfall_G REV_SHORTFALL_G, sh.rev_shortfall_G1 REV_SHORTFALL_G1, total_lines_temp TOTAL_LINES,
2026 1 UNION1_FLAG, 1 UNION2_FLAG
2027 FROM isc_dbi_tmp_plans tmp,
2028 isc_dbi_shortfall_snapshots sh
2029 WHERE tmp.snapshot_id = sh.snapshot_id
2030 AND bitand(tmp.plan_usage, 2) = 2)
2031 GROUP BY snapshot_id, organization_id, sr_inventory_item_id, start_date, period_type_id, uom_code;
2032
2033 FII_UTIL.Stop_Timer;
2034 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_inv_detail_snapshots in');
2035 -- COMMIT;
2036
2037 -- ----------------------------------
2038 -- Collect ISC_DBI_RES_SUM_SNAPSHOTS
2039 -- ----------------------------------
2040
2041 BIS_COLLECTION_UTILITIES.put_line(' ');
2042 BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_res_sum_snapshots.');
2043 FII_UTIL.Start_Timer;
2044
2045 INSERT /*+ APPEND PARALLEL(F) */
2046 INTO isc_dbi_res_sum_snapshots F(
2047 SNAPSHOT_ID,
2048 ORGANIZATION_ID,
2049 START_DATE,
2050 PERIOD_TYPE_ID,
2051 DEPARTMENT_ID,
2052 ORGANIZATION_TYPE,
2053 RESOURCE_ID,
2054 REQUIRED_HOURS,
2055 AVAILABLE_HOURS)
2056 SELECT /*+ parallel(ires) */ tmp.snapshot_id,
2057 ires.organization_id,
2058 decode(grouping_id(ent_year_start_date, ent_qtr_start_date, ent_period_start_date),
2059 6, ent_period_start_date, 5, ent_qtr_start_date, 3, ent_year_start_date) START_DATE,
2060 decode(grouping_id(ent_year_start_date, ent_qtr_start_date, ent_period_start_date),
2061 6, 32, 5, 64, 3, 128) PERIOD_TYPE_ID,
2062 ires.department_id DEPARTMENT_ID,
2063 ires.organization_type ORGANIZATION_TYPE,
2064 ires.resource_id,
2065 sum(nvl(required_hours,0)/(per.end_date-per.start_date+1)) REQUIRED_HOURS,
2066 sum(nvl(available_hours,0)/(per.end_date-per.start_date+1)) AVAILABLE_HOURS
2067 FROM isc_dbi_tmp_plans tmp,
2068 isc_dbi_res_summary_f ires,
2069 isc_dbi_periods per,
2070 fii_time_day time
2071 WHERE tmp.plan_id = ires.plan_id
2072 AND bitand(tmp.plan_usage, 2) = 2
2073 AND ires.organization_id = per.organization_id
2074 AND per.adjustment_period_flag = 'N'
2075 AND ires.resource_date = per.start_date
2076 AND time.report_date between per.start_date and per.end_date
2077 -- AND ires.resource_id > 0
2078 GROUP BY snapshot_id, ires.organization_id, ires.resource_id, ires.organization_type, ires.department_id,
2079 grouping sets(ent_year_start_date, ent_qtr_start_date, ent_period_start_date);
2080
2081 FII_UTIL.Stop_Timer;
2082 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_res_sum_snapshots in');
2083 -- COMMIT;
2084 END IF;
2085
2086 RETURN(1);
2087
2088 EXCEPTION
2089 WHEN OTHERS THEN
2090 g_errbuf := 'Error in Function LOAD_SNAPSHOTS : '||sqlerrm;
2091 g_retcode := sqlcode;
2092 RETURN(-1);
2093 END load_snapshots;
2094
2095 Function purge_snapshots RETURN NUMBER IS
2096
2097 l_stmt VARCHAR2(2000);
2098 l_snapshot_id NUMBER;
2099 l_total NUMBER;
2100
2101 CURSOR List_of_Snapshots IS
2102 SELECT snapshot_id
2103 FROM isc_dbi_plan_snapshots
2104 WHERE purge_flag = 'Y';
2105
2106 no_partition EXCEPTION;
2107 PRAGMA EXCEPTION_INIT(no_partition, -02149);
2108
2109 BEGIN
2110
2111 OPEN List_of_Snapshots;
2112 FETCH List_of_Snapshots INTO l_snapshot_id;
2113
2114 IF List_of_Snapshots%ROWCOUNT <> 0 THEN
2115 WHILE List_of_Snapshots%FOUND LOOP
2116 BIS_COLLECTION_UTILITIES.Put_Line('Purging snapshot '|| l_snapshot_id);
2117 IF (drop_snapshots(l_snapshot_id) = -1) THEN RETURN(-1); END IF;
2118 FETCH List_of_Snapshots INTO l_snapshot_id;
2119 END LOOP;
2120 END IF;
2121
2122 l_total := List_of_Snapshots%ROWCOUNT;
2123 CLOSE List_of_Snapshots;
2124 RETURN(l_total);
2125
2126 EXCEPTION
2127 WHEN OTHERS THEN
2128 g_errbuf := 'Error in function purge_snapshots : '||sqlerrm;
2129 RETURN(-1);
2130
2131 END purge_snapshots;
2132
2133 Function WrapUp RETURN NUMBER IS
2134
2135 l_plan_name VARCHAR2(30);
2136 l_date DATE;
2137 l_total NUMBER;
2138
2139 /*
2140 CURSOR Plan_List IS
2141 SELECT plan_name
2142 FROM isc_dbi_tmp_plans tmp
2143 WHERE bitand(plan_usage, 2) = 2;
2144 */
2145 CURSOR Plan_List IS
2146 SELECT tmp.plan_name
2147 FROM isc_dbi_tmp_plans tmp, isc_dbi_plan_schedules s
2148 WHERE (bitand(tmp.plan_usage, 2) = 2 or bitand(tmp.plan_usage, 4) = 4)
2149 AND tmp.plan_name = s.plan_name
2150 AND s.frequency <> 'ONCE';
2151
2152 BEGIN
2153
2154 -- --------------------------------------------
2155 -- Update the last and next collection date
2156 -- --------------------------------------------
2157
2158 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2159 BIS_COLLECTION_UTILITIES.put_line('Begin to update the collection information.');
2160 FII_UTIL.Start_Timer;
2161
2162 DELETE from isc_dbi_plan_schedules
2163 WHERE frequency = 'ONCE'
2164 AND next_collection_date <= g_snapshot_date
2165 AND plan_name IN (select plan_name from isc_dbi_tmp_plans tmp
2166 WHERE (bitand(tmp.plan_usage, 2) = 2 or bitand(tmp.plan_usage, 4) = 4));
2167
2168 UPDATE isc_dbi_plan_schedules
2169 SET last_collected_date = g_snapshot_date
2170 WHERE plan_name IN (select plan_name from isc_dbi_tmp_plans tmp
2171 WHERE (bitand(tmp.plan_usage, 2) = 2 or bitand(tmp.plan_usage, 4) = 4));
2172
2173 -- API to populate the next collection date
2174
2175 OPEN Plan_List;
2176 FETCH Plan_List INTO l_plan_name;
2177
2178 IF Plan_List%ROWCOUNT <> 0 THEN
2179 WHILE Plan_List%FOUND LOOP
2180 BEGIN
2181 BIS_COLLECTION_UTILITIES.Put_Line('Populating the next collection date for plan '|| l_plan_name);
2182 l_date := ISC_DBI_PLAN_SETUP_UTIL_PKG.get_next_collection_date(l_plan_name);
2183 UPDATE isc_dbi_plan_schedules
2184 SET next_collection_date = l_date
2185 WHERE plan_name = l_plan_name;
2186 EXCEPTION
2187 WHEN no_data_found THEN
2188 BIS_COLLECTION_UTILITIES.put_line('Plan ' || l_plan_name || ' has already been deleted from the setup form.');
2189 END;
2190 FETCH Plan_List INTO l_plan_name;
2191 END LOOP;
2192 END IF;
2193
2194 l_total := Plan_List%ROWCOUNT;
2195 CLOSE Plan_List;
2196
2197 -- UPDATE isc_dbi_plan_schedules
2198 -- SET next_collection_date = ISC_DBI_PLAN_SETUP_UTIL_PKG.get_next_collection_date(plan_name)
2199 -- WHERE plan_name IN (select plan_name from isc_dbi_tmp_plans tmp WHERE bitand(tmp.plan_usage, 2) = 2);
2200
2201 FII_UTIL.Stop_Timer;
2202 FII_UTIL.Print_Timer('Updated the setup tables in');
2203 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2204
2205 /*
2206 -- ------------------------
2207 -- Delete ISC_DBI_TMP_PLANS
2208 -- ------------------------
2209
2210 BIS_COLLECTION_UTILITIES.put_line(' ');
2211 BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
2212 FII_UTIL.Start_Timer;
2213
2214 l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';
2215 EXECUTE IMMEDIATE l_trunc_stmt;
2216
2217 FII_UTIL.Stop_Timer;
2218 FII_UTIL.Print_Timer('Truncated the temp table in');
2219 */
2220 -- ----------------------------------------------
2221 -- No exception raised so far. Successful. Call
2222 -- Wrapup to commit and insert messages into logs
2223 -- ----------------------------------------------
2224 COMMIT;
2225
2226 BIS_COLLECTION_UTILITIES.WRAPUP(TRUE, 0, NULL, NULL, NULL);
2227 RETURN(1);
2228
2229 EXCEPTION
2230 WHEN OTHERS THEN
2231 g_errbuf := 'Error in function wrapup : '||sqlerrm;
2232 RETURN(-1);
2233
2234 END wrapup;
2235
2236 ---------------------
2237 -- Public Procedures
2238 ---------------------
2239
2240 Procedure load_facts(errbuf IN OUT NOCOPY VARCHAR2,
2241 retcode IN OUT NOCOPY VARCHAR2) IS
2242
2243 l_failure EXCEPTION;
2244 l_no_aps_failure EXCEPTION;
2245 l_purge NUMBER;
2246 l_base NUMBER;
2247 l_trunc_stmt VARCHAR2(500);
2248
2249 l_partition_name VARCHAR2(80);
2250 l_drop_stmt VARCHAR2(2000);
2251
2252 CURSOR Get_Partitions(p_table_name IN VARCHAR2, p_schema_name IN VARCHAR2) IS
2253 SELECT partition_name
2254 FROM all_tab_partitions
2255 WHERE table_name = p_table_name
2256 AND table_owner = p_schema_name;
2257
2258 BEGIN
2259 errbuf := NULL;
2260 retcode := '0';
2261
2262 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_PLAN_F')) THEN
2263 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
2264 return;
2265 END IF;
2266
2267 EXECUTE IMMEDIATE 'alter session set hash_area_size=104857600';
2268 EXECUTE IMMEDIATE 'alter session set sort_area_size=104857600';
2269
2270 -- --------------------------------------------
2271 -- Load Base Tables
2272 -- --------------------------------------------
2273
2274 l_base := LOAD_BASES_INIT;
2275
2276 IF (l_base = -1)
2277 THEN RAISE l_no_aps_failure;
2278 END IF;
2279
2280 -- --------------------------------------------------------
2281 -- Truncate all the snapshot tables before the initial load
2282 -- --------------------------------------------------------
2283
2284 BIS_COLLECTION_UTILITIES.put_line(' ');
2285 BIS_COLLECTION_UTILITIES.put_line('Begin to truncate and drop all the snapshots');
2286
2287 -- Bug 4939001: Drop all partitions during initial load
2288 --
2289 -- FOR j in 1..g_large_snapshots.last LOOP
2290 -- l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.'|| g_large_snapshots(j);
2291 -- EXECUTE IMMEDIATE l_trunc_stmt;
2292 -- END LOOP;
2293
2294 FOR j in 1..g_large_snapshots.last LOOP
2295 OPEN Get_Partitions(g_large_snapshots(j), g_isc_schema);
2296 FETCH Get_Partitions INTO l_partition_name;
2297 IF Get_Partitions%ROWCOUNT <> 0 THEN
2298 WHILE Get_Partitions%Found LOOP
2299 IF upper(l_partition_name) <> 'S_0' THEN
2300 l_drop_stmt := 'ALTER TABLE '|| g_isc_schema || '.' || g_large_snapshots(j) || ' DROP PARTITION ' || l_partition_name;
2301 EXECUTE IMMEDIATE l_drop_stmt;
2302 END IF;
2303 FETCH Get_Partitions INTO l_partition_name;
2304 END LOOP;
2305 END IF;
2306 CLOSE Get_Partitions;
2307 END LOOP;
2308
2309 g_rebuild_snapshot_index := 'Y';
2310
2311 FOR i in 1..g_small_snapshots.last LOOP
2312 l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.'|| g_small_snapshots(i);
2313 EXECUTE IMMEDIATE l_trunc_stmt;
2314 END LOOP;
2315
2316 FII_UTIL.Stop_Timer;
2317 FII_UTIL.Print_Timer('Truncated the snapshot tables in');
2318 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2319
2320 -- --------------------------------------------
2321 -- Insert data into the snapshot tables
2322 -- --------------------------------------------
2323
2324 BIS_COLLECTION_UTILITIES.put_line(' ');
2325 BIS_COLLECTION_UTILITIES.put_line('Begin to load the snapshot tables');
2326
2327 IF (load_snapshots = -1) THEN
2328 RAISE l_failure;
2329 END IF;
2330
2331 IF (wrapup = -1) THEN
2332 RAISE l_failure;
2333 END IF;
2334
2335 retcode := g_retcode;
2336 errbuf := g_errbuf;
2337
2338 EXCEPTION
2339
2340 WHEN L_FAILURE THEN
2341 ROLLBACK;
2342 BIS_COLLECTION_UTILITIES.put_line(' ');
2343 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2344 retcode := -1;
2345 errbuf := g_errbuf;
2346
2347 BIS_COLLECTION_UTILITIES.WRAPUP(
2348 FALSE,
2349 g_row_count,
2350 g_errbuf,
2351 NULL,
2352 NULL
2353 );
2354
2355 WHEN L_NO_APS_FAILURE THEN
2356 ROLLBACK;
2357 retcode := -1;
2358 errbuf := g_errbuf;
2359
2360 BIS_COLLECTION_UTILITIES.WRAPUP(
2361 FALSE,
2362 g_row_count,
2363 g_errbuf,
2364 NULL,
2365 NULL
2366 );
2367
2368 WHEN OTHERS THEN
2369 ROLLBACK;
2370 g_errbuf := sqlerrm ||' - '||sqlcode;
2371 BIS_COLLECTION_UTILITIES.put_line(' ');
2372 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2373 retcode := -1;
2374 errbuf := g_errbuf;
2375
2376 BIS_COLLECTION_UTILITIES.WRAPUP(
2377 FALSE,
2378 g_row_count,
2379 g_errbuf,
2380 NULL,
2381 NULL
2382 );
2383
2384 END load_facts;
2385
2386 Procedure update_facts(errbuf IN OUT NOCOPY VARCHAR2,
2387 retcode IN OUT NOCOPY VARCHAR2) IS
2388
2389 l_failure EXCEPTION;
2390 l_no_aps_failure EXCEPTION;
2391 l_purge NUMBER;
2392 l_base NUMBER;
2393
2394 BEGIN
2395 errbuf := NULL;
2396 retcode := '0';
2397
2398 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_PLAN_F_INC')) THEN
2399 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
2400 return;
2401 END IF;
2402
2403 EXECUTE IMMEDIATE 'alter session set hash_area_size=104857600';
2404 EXECUTE IMMEDIATE 'alter session set sort_area_size=104857600';
2405
2406 -- --------------------------------------------
2407 -- Load Base Tables
2408 -- --------------------------------------------
2409
2410 l_base := LOAD_BASES;
2411
2412 IF (l_base = -1)
2413 THEN RAISE l_no_aps_failure;
2414 END IF;
2415
2416 -- --------------------------------------------
2417 -- Purge Snapshot Tables
2418 -- --------------------------------------------
2419
2420 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2421 BIS_COLLECTION_UTILITIES.put_line('Begin to purge the obsolete snapshots ');
2422 FII_UTIL.Start_Timer;
2423
2424 l_purge := PURGE_SNAPSHOTS;
2425
2426 IF (l_purge = -1) THEN
2427 RAISE l_failure;
2428 END IF;
2429
2430 FII_UTIL.Stop_Timer;
2431 FII_UTIL.Print_Timer('Purged '|| l_purge ||' snapshots in');
2432 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2433
2434 -- --------------------------------------------
2435 -- Insert data into the snapshot tables
2436 -- --------------------------------------------
2437
2438 BIS_COLLECTION_UTILITIES.put_line(' ');
2439 BIS_COLLECTION_UTILITIES.put_line('Begin to load the snapshot tables');
2440
2441 IF (load_snapshots = -1) THEN
2442 RAISE l_failure;
2443 END IF;
2444
2445 IF (wrapup = -1) THEN
2446 RAISE l_failure;
2447 END IF;
2448
2449 retcode := g_retcode;
2450 errbuf := g_errbuf;
2451
2452 EXCEPTION
2453
2454 WHEN L_FAILURE THEN
2455 ROLLBACK;
2456 BIS_COLLECTION_UTILITIES.put_line(' ');
2457 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2458 retcode := -1;
2459 errbuf := g_errbuf;
2460
2461 BIS_COLLECTION_UTILITIES.WRAPUP(
2462 FALSE,
2463 g_row_count,
2464 g_errbuf,
2465 NULL,
2466 NULL
2467 );
2468
2469 WHEN L_NO_APS_FAILURE THEN
2470 ROLLBACK;
2471 retcode := -1;
2472 errbuf := g_errbuf;
2473
2474 BIS_COLLECTION_UTILITIES.WRAPUP(
2475 FALSE,
2476 g_row_count,
2477 g_errbuf,
2478 NULL,
2479 NULL
2480 );
2481
2482 WHEN OTHERS THEN
2483 ROLLBACK;
2484 g_errbuf := sqlerrm ||' - '||sqlcode;
2485 BIS_COLLECTION_UTILITIES.put_line(' ');
2486 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2487 retcode := -1;
2488 errbuf := g_errbuf;
2489
2490 BIS_COLLECTION_UTILITIES.WRAPUP(
2491 FALSE,
2492 g_row_count,
2493 g_errbuf,
2494 NULL,
2495 NULL
2496 );
2497
2498 END update_facts;
2499
2500 END ISC_DBI_MSC_OBJECTS_C;
2501