[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_BOOK_SUM2_REF_PKG
Source
1 PACKAGE BODY ISC_DBI_BOOK_SUM2_REF_PKG AS
2 /* $Header: ISCRF70B.pls 120.3 2006/02/27 17:26:18 scheung noship $ */
3
4 g_login_id NUMBER;
5 g_user_id NUMBER;
6 g_commit_size NUMBER := 500;
7 g_errbuf VARCHAR2(2000) := NULL;
8 g_retcode VARCHAR2(200) := NULL;
9
10 FUNCTION REFRESH_MV(p_mview_name VARCHAR2) RETURN NUMBER IS
11
12 l_row_count NUMBER;
13 l_sql_stmt VARCHAR2(2000);
14 l_degree NUMBER := 0;
15
16 BEGIN
17
18 l_degree := bis_common_parameters.get_degree_of_parallelism;
19 BIS_COLLECTION_UTILITIES.put_line('The degree of parallelism is '|| l_degree);
20
21 BIS_COLLECTION_UTILITIES.put_line(' ');
22 BIS_COLLECTION_UTILITIES.put_line('Start to Refresh '|| p_mview_name);
23
24 FII_UTIL.Start_Timer;
25
26 DBMS_MVIEW.REFRESH(
27 list => p_mview_name,
28 method => '?',
29 parallelism => l_degree
30 );
31
32 FII_UTIL.Stop_Timer;
33 FII_UTIL.Print_Timer(p_mview_name || ' has been refreshed in ');
34
35 l_sql_stmt := 'SELECT count(*) FROM ' || p_mview_name ;
36 EXECUTE IMMEDIATE l_sql_stmt INTO l_row_count;
37
38 BIS_COLLECTION_UTILITIES.put_line(p_mview_name ||' has '||l_row_count||' rows.');
39
40 RETURN(l_row_count);
41
42 EXCEPTION
43
44 WHEN OTHERS THEN
45 BIS_COLLECTION_UTILITIES.put_line('Errored while refreshing '|| p_mview_name);
46 g_errbuf := sqlerrm ||' - '||sqlcode;
47 RETURN(-1);
48
49 END refresh_mv;
50
51 PROCEDURE refresh_past_due(errbuf IN OUT NOCOPY VARCHAR2,
52 retcode IN OUT NOCOPY VARCHAR2) IS
53
54 l_start DATE := NULL;
55 l_end DATE := NULL;
56 l_period_from DATE := NULL;
57 l_from_date DATE := NULL;
58 l_to_date DATE := NULL;
59 l_failure EXCEPTION;
60 l_row_count NUMBER := 0;
61
62 BEGIN
63 errbuf := NULL;
64 retcode := '0';
65
66 IF (Not BIS_COLLECTION_UTILITIES.setup('ISC_BOOK_SUM2_PDUE_F')) THEN
67 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
68 return;
69 END IF;
70
71 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('ISC_BOOK_SUM2_PDUE_F', l_start, l_end, l_period_from, l_from_date);
72 l_to_date := sysdate;
73
74 BIS_COLLECTION_UTILITIES.put_line('Updating ISC_BOOK_SUM2_PDUE_F');
75
76 FII_UTIL.Start_Timer;
77
78 UPDATE isc_book_sum2_pdue_f
79 SET time_snapshot_date_id = to_date('1000/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')
80 WHERE time_snapshot_date_id = trunc(sysdate);
81
82 FII_UTIL.Stop_Timer;
83 FII_UTIL.Print_Timer('Obsoleted ' || sql%rowcount || ' rows in ISC_BOOK_SUM2_PDUE_F in');
84
85 BIS_COLLECTION_UTILITIES.put_line(' ');
86 BIS_COLLECTION_UTILITIES.put_line('Inserting data into ISC_BOOK_SUM2_PDUE_F');
87
88 FII_UTIL.Start_Timer;
89
90 INSERT INTO isc_book_sum2_pdue_f(
91 INV_ORG_ID,
92 INVENTORY_ITEM_ID,
93 CUSTOMER_ID,
94 TIME_BOOKED_DATE_ID,
95 ORDER_NUMBER,
96 HEADER_ID,
97 PDUE_LINE_CNT,
98 PDUE_QTY,
99 UOM,
100 LINE_NUMBER,
101 DAYS_LATE,
102 TIME_SNAPSHOT_DATE_ID,
103 SOLD_TO_ORG_ID,
104 SHIP_TO_ORG_ID,
105 LINE_ID)
106 SELECT NULL, NULL, NULL, NULL, -1, -1, 0, 0, NULL, NULL, 0, trunc(sysdate), NULL, NULL, NULL
107 FROM dual
108 UNION ALL
109 SELECT inv_org_id,
110 inventory_item_id,
111 customer_id,
112 time_booked_date_id,
113 order_number,
114 header_id,
115 count_pdue_line PDUE_LINE_CNT,
116 pdue_qty,
117 uom,
118 line_number,
119 (trunc(sysdate) - time_schedule_date_id) DAYS_LATE,
120 trunc(sysdate) TIME_SNAPSHOT_DATE_ID,
121 sold_to_org_id,
122 ship_to_org_id,
123 line_id
124 FROM isc_dbi_fm_0004_mv
125 WHERE time_schedule_date_id < trunc(sysdate);
126
127 l_row_count := l_row_count + sql%rowcount;
128
129 FII_UTIL.Stop_Timer;
130 FII_UTIL.Print_Timer('Inserted ' || l_row_count || ' rows into ISC_BOOK_SUM2_PDUE_F in');
131
132 BIS_COLLECTION_UTILITIES.WRAPUP(
133 TRUE,
134 l_row_count,
135 NULL,
136 l_from_date,
137 l_to_date
138 );
139
140 EXCEPTION
141
142 WHEN L_FAILURE THEN
143 ROLLBACK;
144 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
145 retcode := -1;
146
147 BIS_COLLECTION_UTILITIES.WRAPUP(
148 FALSE,
149 l_row_count,
150 g_errbuf,
151 l_from_date,
152 l_to_date
153 );
154
155 WHEN OTHERS THEN
156 ROLLBACK;
157 errbuf := sqlerrm ||' - '||sqlcode;
158 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| errbuf);
159 retcode := -1;
160
161 BIS_COLLECTION_UTILITIES.WRAPUP(
162 FALSE,
163 l_row_count,
164 errbuf,
165 l_from_date,
166 l_to_date
167 );
168
169 END refresh_past_due;
170
171 PROCEDURE refresh_past_due2(errbuf IN OUT NOCOPY VARCHAR2,
172 retcode IN OUT NOCOPY VARCHAR2) IS
173
174 l_start DATE := NULL;
175 l_end DATE := NULL;
176 l_period_from DATE := NULL;
177 l_from_date DATE := NULL;
178 l_to_date DATE := NULL;
179 l_failure EXCEPTION;
180 l_row_count NUMBER := 0;
181
182 BEGIN
183 errbuf := NULL;
184 retcode := '0';
185
186 IF (Not BIS_COLLECTION_UTILITIES.setup('ISC_BOOK_SUM2_PDUE2_F')) THEN
187 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
188 return;
189 END IF;
190
191 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('ISC_BOOK_SUM2_PDUE2_F', l_start, l_end, l_period_from, l_from_date);
192 l_to_date := sysdate;
193
194 BIS_COLLECTION_UTILITIES.put_line('Updating ISC_BOOK_SUM2_PDUE2_F');
195
196 FII_UTIL.Start_Timer;
197
198 UPDATE isc_book_sum2_pdue2_f
199 SET time_snapshot_date_id = to_date('1000/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')
200 WHERE time_snapshot_date_id = trunc(sysdate);
201
202 FII_UTIL.Stop_Timer;
203 FII_UTIL.Print_Timer('Obsoleted ' || sql%rowcount || ' rows in ISC_BOOK_SUM2_PDUE2_F in');
204
205 BIS_COLLECTION_UTILITIES.put_line(' ');
206 BIS_COLLECTION_UTILITIES.put_line('Inserting data into ISC_BOOK_SUM2_PDUE2_F');
207
208 FII_UTIL.Start_Timer;
209
210 INSERT INTO isc_book_sum2_pdue2_f(
211 INV_ORG_ID,
212 INVENTORY_ITEM_ID,
213 CUSTOMER_ID,
214 TIME_BOOKED_DATE_ID,
215 ORDER_NUMBER,
216 HEADER_ID,
217 PDUE_LINE_CNT,
218 PDUE_QTY,
219 UOM,
220 LINE_NUMBER,
221 DAYS_LATE,
222 DAYS_LATE_PROMISE,
223 PDUE_AMT_F,
224 PDUE_AMT_G,
225 LATE_SCHEDULE_FLAG,
226 LATE_PROMISE_FLAG,
227 TIME_SNAPSHOT_DATE_ID,
228 PDUE_AMT_G1,
229 SOLD_TO_ORG_ID,
230 SHIP_TO_ORG_ID,
231 TOP_MODEL_LINE_ID,
232 LINE_ID,
233 ITEM_TYPE_CODE)
234 SELECT NULL, NULL, NULL, NULL, -1, -1, 0, 0, NULL, NULL, 0, 0, 0, 0, -1, -1, trunc(sysdate),0, NULL, NULL, NULL, NULL, NULL
235 FROM dual
236 UNION ALL
237 SELECT inv_org_id,
238 inventory_item_id,
239 customer_id,
240 time_booked_date_id,
241 order_number,
242 header_id,
243 count_pdue_line PDUE_LINE_CNT,
244 pdue_qty,
245 uom,
246 line_number,
247 (trunc(sysdate) - time_schedule_date_id) DAYS_LATE,
248 (trunc(sysdate) - time_promise_date_id) DAYS_LATE_PROMISE,
249 pdue_amt_f,
250 pdue_amt_g,
251 (CASE WHEN trunc(sysdate) > time_schedule_date_id THEN 1 ELSE 0 END) LATE_SCHEDULE_FLAG,
252 (CASE WHEN trunc(sysdate) > time_promise_date_id THEN 1 ELSE 0 END) LATE_PROMISE_FLAG,
253 trunc(sysdate) TIME_SNAPSHOT_DATE_ID,
254 pdue_amt_g1,
255 sold_to_org_id,
256 ship_to_org_id,
257 top_model_line_id,
258 line_id,
259 item_type_code
260 FROM isc_dbi_cfm_006_mv
261 WHERE (time_schedule_date_id < trunc(sysdate) OR time_promise_date_id < trunc(sysdate));
262
263 l_row_count := l_row_count + sql%rowcount;
264
265 FII_UTIL.Stop_Timer;
266 FII_UTIL.Print_Timer('Inserted ' || l_row_count || ' rows into ISC_BOOK_SUM2_PDUE2_F in');
267
268 BIS_COLLECTION_UTILITIES.WRAPUP(
269 TRUE,
270 l_row_count,
271 NULL,
272 l_from_date,
273 l_to_date
274 );
275
276 EXCEPTION
277
278 WHEN L_FAILURE THEN
279 ROLLBACK;
280 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
281 retcode := -1;
282
283 BIS_COLLECTION_UTILITIES.WRAPUP(
284 FALSE,
285 l_row_count,
286 g_errbuf,
287 l_from_date,
288 l_to_date
289 );
290
291 WHEN OTHERS THEN
292 ROLLBACK;
293 errbuf := sqlerrm ||' - '||sqlcode;
294 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| errbuf);
295 retcode := -1;
296
297 BIS_COLLECTION_UTILITIES.WRAPUP(
298 FALSE,
299 l_row_count,
300 errbuf,
301 l_from_date,
302 l_to_date
303 );
304
305 END refresh_past_due2;
306
307 PROCEDURE refresh_backorder(errbuf IN OUT NOCOPY VARCHAR2,
308 retcode IN OUT NOCOPY VARCHAR2) IS
309
310 l_start DATE := NULL;
311 l_end DATE := NULL;
312 l_period_from DATE := NULL;
313 l_from_date DATE := NULL;
314 l_to_date DATE := NULL;
315 l_failure EXCEPTION;
316 l_row_count NUMBER := 0;
317
318 BEGIN
319 errbuf := NULL;
320 retcode := '0';
321
322 IF (Not BIS_COLLECTION_UTILITIES.setup('ISC_BOOK_SUM2_BKORD_F')) THEN
323 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
324 return;
325 END IF;
326
327 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('ISC_BOOK_SUM2_BKORD_F', l_start, l_end, l_period_from, l_from_date);
328 l_to_date := sysdate;
329
330 BIS_COLLECTION_UTILITIES.put_line('Updating ISC_BOOK_SUM2_BKORD_F');
331
332 FII_UTIL.Start_Timer;
333
334 UPDATE isc_book_sum2_bkord_f
335 SET time_snapshot_date_id = to_date('1000/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')
336 WHERE time_snapshot_date_id = trunc(sysdate);
337
338 FII_UTIL.Stop_Timer;
339 FII_UTIL.Print_Timer('Obsoleted ' || sql%rowcount || ' rows in ISC_BOOK_SUM2_BKORD_F in');
340
341 BIS_COLLECTION_UTILITIES.put_line(' ');
342 BIS_COLLECTION_UTILITIES.put_line('Inserting data into ISC_BOOK_SUM2_BKORD_F');
343
344 FII_UTIL.Start_Timer;
345
346 INSERT INTO isc_book_sum2_bkord_f(
347 INV_ORG_ID,
348 INVENTORY_ITEM_ID,
349 CUSTOMER_ID,
350 ORDER_NUMBER,
351 HEADER_ID,
352 TIME_REQUEST_DATE_ID,
353 TIME_SCHEDULE_DATE_ID,
354 LINE_NUMBER,
355 DAYS_LATE_REQUEST,
356 DAYS_LATE_SCHEDULE,
357 BACKORDER_QTY,
358 UOM,
359 BACKORDER_LINE_CNT,
360 TIME_SNAPSHOT_DATE_ID,
361 SOLD_TO_ORG_ID,
362 SHIP_TO_ORG_ID,
363 LINE_ID)
364 SELECT NULL, NULL, NULL, -1, -1, NULL, NULL, NULL, 0, 0, 0, NULL, 0, trunc(sysdate), NULL, NULL, NULL
365 FROM dual
366 UNION ALL
367 SELECT fact.item_inv_org_id INV_ORG_ID,
368 fact.inventory_item_id,
369 fact.ship_to_party_id,
370 fact.order_number,
371 fact.header_id,
372 fact.time_request_date_id,
373 fact.time_schedule_date_id,
374 fact.line_number,
375 (trunc(sysdate) - fact.time_request_date_id) DAYS_LATE_REQUEST,
376 (trunc(sysdate) - fact.time_schedule_date_id) DAYS_LATE_SCHEDULE,
377 fact.booked_qty_inv BACKORDER_QTY,
378 fact.inv_uom_code UOM,
379 1 BACKORDER_LINE_CNT,
380 trunc(sysdate) TIME_SNAPSHOT_DATE_ID,
381 fact.sold_to_org_id,
382 fact.ship_to_org_id,
383 fact.line_id
384 FROM isc_book_sum2_f fact,
385 wsh_delivery_details wdd
386 WHERE fact.line_id = wdd.source_line_id
387 AND fact.flow_status_code in ('AWAITING_SHIPPING','PRODUCTION_COMPLETE','PRODUCTION_OPEN','PRODUCTION_PARTIAL','PRODUCTION_ELIGIBLE')
388 AND wdd.released_status = 'B'
389 AND fact.line_category_code <> 'RETURN'
390 AND fact.open_flag = 'Y'
391 AND fact.item_type_code <> 'SERVICE'
392 AND fact.order_source_id <> 27
393 AND fact.ordered_quantity <> 0
394 AND fact.charge_periodicity_code is NULL;
395
396 l_row_count := l_row_count + sql%rowcount;
397
398 FII_UTIL.Stop_Timer;
399 FII_UTIL.Print_Timer('Inserted ' || l_row_count || ' rows into ISC_BOOK_SUM2_BKORD_F in');
400
401 BIS_COLLECTION_UTILITIES.WRAPUP(
402 TRUE,
403 l_row_count,
404 NULL,
405 l_from_date,
406 l_to_date
407 );
408
409 EXCEPTION
410
411 WHEN L_FAILURE THEN
412 ROLLBACK;
413 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
414 retcode := -1;
415
416 BIS_COLLECTION_UTILITIES.WRAPUP(
417 FALSE,
418 l_row_count,
419 g_errbuf,
420 l_from_date,
421 l_to_date
422 );
423
424 WHEN OTHERS THEN
425 ROLLBACK;
426 errbuf := sqlerrm ||' - '||sqlcode;
427 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| errbuf);
428 retcode := -1;
429
430 BIS_COLLECTION_UTILITIES.WRAPUP(
431 FALSE,
432 l_row_count,
433 errbuf,
434 l_from_date,
435 l_to_date
436 );
437
438 END refresh_backorder;
439
440 END isc_dbi_book_sum2_ref_pkg;