[Home] [Help]
PACKAGE BODY: APPS.ISC_EDW_BACKLOG_SUM1_F_C
Source
1 Package Body ISC_EDW_BACKLOG_SUM1_F_C AS
2 /* $Header: ISCSCF3B.pls 120.0 2005/05/25 17:42:59 appldev noship $ */
3
4
5 TYPE instance_rec IS RECORD (
6 instance_code VARCHAR2(30),
7 db_link VARCHAR2(128),
8 same_inst BOOLEAN,
9 valid BOOLEAN,
10 source_instance VARCHAR2(200));
11
12 TYPE Instance_Tab IS TABLE OF Instance_Rec INDEX BY BINARY_INTEGER;
13
14 g_rec INSTANCE_TAB;
15 g_isc_schema VARCHAR2(30);
16 g_tablespace VARCHAR2(30);
17 g_errbuf VARCHAR2(2000) := NULL;
18 g_retcode VARCHAR2(200) := NULL;
19 g_exception_msg VARCHAR2(200);
20
21 g_drop_table_failure EXCEPTION;
22 g_trunc_table_failure EXCEPTION;
23 g_collect_back_failure EXCEPTION;
24 g_pop_back_sum_failure EXCEPTION;
25 g_table_not_exist EXCEPTION;
26 g_synonym_not_exist EXCEPTION;
27
28 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
29 PRAGMA EXCEPTION_INIT(G_SYNONYM_NOT_EXIST, -1434);
30
31
32 -- ---------------------------------
33 -- PRIVATE PROCEDURES AND FUNCTIONS
34 -- ---------------------------------
35
36 -------------------
37 -- PROCEDURE Init
38 -------------------
39 PROCEDURE Init IS
40 l_stmt VARCHAR2(200);
41 l_status VARCHAR2(30);
42 l_industry VARCHAR2(30);
43 l_db_name1 VARCHAR2(30);
44 l_db_name2 VARCHAR2(30);
45 l_dummy NUMBER := NULL;
46 i NUMBER := 0;
47 j NUMBER := 0;
48
49 CURSOR source_instance IS
50 SELECT edw.instance_code,
51 edw.warehouse_to_instance_link
52 FROM edw_source_instances edw
53 WHERE edw.enabled_flag = 'Y';
54
55 BEGIN
56
57 -- --------------------------------------------------------
58 -- Find the schema owner and tablespace
59 -- ISC_EDW_BACK_SUM1_F is using
60 -- --------------------------------------------------------
61 IF (FND_INSTALLATION.Get_App_Info('ISC', l_status, l_industry, g_isc_schema))
62 THEN NULL;
63 END IF;
64
65 SELECT tablespace_name
66 INTO g_tablespace
67 FROM all_tables
68 WHERE table_name = 'ISC_EDW_BACKLOG_SUM1_F'
69 AND owner = g_isc_schema;
70
71
72
73 -- ----------------------------
74 -- Findwarehouse database name
75 -- ----------------------------
76 select name
77 into l_db_name1
78 from v$database;
79
80
81 -- --------------------------------------------------------
82 -- Loop the complete set of instances
83 -- --------------------------------------------------------
84 FOR c in source_instance LOOP
85
86 i := i + 1;
87 g_rec(i).instance_code := c.instance_code;
88 g_rec(i).db_link := c.warehouse_to_instance_link;
89
90 BEGIN
91 l_stmt := 'SELECT SET_OF_BOOKS_ID FROM ISCBV_EDW_BACKLOG_SUM1_FCV@'
92 ||g_rec(i).db_link
93 ||' WHERE ROWNUM < 2';
94 EDW_LOG.Debug_Line('');
95 EDW_LOG.Debug_Line(l_stmt);
96 EXECUTE IMMEDIATE l_stmt INTO l_dummy;
97 IF l_dummy IS NOT NULL
98 THEN g_rec(i).valid := TRUE;
99 ELSE g_rec(i).valid := FALSE;
100 END IF;
101
102
103 IF g_rec(i).valid = TRUE
104 THEN
105 BEGIN
106 l_stmt := 'SELECT instance_code from edw_local_instance@'||g_rec(i).db_link;
107 EDW_LOG.Debug_Line('');
108 EDW_LOG.Debug_Line(l_stmt);
109 EXECUTE IMMEDIATE l_stmt INTO g_rec(i).source_instance;
110
111 IF i > 1
112 THEN
113 FOR j IN 1..(i-1) LOOP
114 IF g_rec(j).source_instance = g_rec(i).source_instance
115 THEN g_rec(i).valid := FALSE;
116 END IF;
117 END LOOP;
118 END IF;
119 END;
120 END IF;
121
122 EXCEPTION
123 WHEN OTHERS
124 THEN g_rec(i).valid := FALSE;
125
126 END;
127
128 -- ----------------------------
129 -- Check if same instance
130 -- ----------------------------
131 IF g_rec(i).valid
132 THEN l_stmt := 'SELECT name '||'FROM v$database@'||g_rec(i).db_link;
133
134 EDW_LOG.Debug_Line('');
135 EDW_LOG.Debug_Line(l_stmt);
136 EXECUTE IMMEDIATE l_stmt INTO l_db_name2;
137
138 IF (l_db_name1 = l_db_name2)
139 THEN g_rec(i).same_inst := TRUE;
140 ELSE g_rec(i).same_inst := FALSE;
141 END IF;
142
143 END IF;
144
145 END LOOP;
146
147 END Init;
148
149
150
151 ---------------------------------------------------
152 -- PROCEDURE DROP_TABLE
153 ---------------------------------------------------
154 PROCEDURE Drop_Table (p_table_name IN varchar2) IS
155 l_stmt varchar2(400);
156
157 BEGIN
158
159 l_stmt:='DROP TABLE '||g_isc_schema||'.'||p_table_name;
160
161 EDW_LOG.Debug_Line('');
162 EDW_LOG.Debug_Line(l_stmt);
163 EXECUTE IMMEDIATE l_stmt;
164
165
166 EXCEPTION
167 WHEN G_TABLE_NOT_EXIST
168 THEN NULL; -- ORA 942: table not exist, no actions
169 WHEN OTHERS
170 THEN RAISE g_drop_table_failure;
171
172 END Drop_Table;
173
174
175 ---------------------------------------------------
176 -- PROCEDURE TRUNCATE_TABLE
177 ---------------------------------------------------
178 PROCEDURE Truncate_Table (p_table_name in varchar2) IS
179 l_stmt varchar2(400);
180
181 BEGIN
182
183 l_stmt:='TRUNCATE TABLE '||g_isc_schema||'.'||p_table_name;
184
185 EDW_LOG.Debug_Line('');
186 EDW_LOG.Debug_Line(l_stmt);
187 EXECUTE IMMEDIATE l_stmt;
188
189 EXCEPTION
190 WHEN OTHERS
191 THEN
192 g_errbuf := sqlerrm;
193 g_retcode := sqlcode;
194 RAISE g_trunc_table_failure;
195
196 END Truncate_Table;
197
198
199 ---------------------------------------------------
200 -- PROCEDURE Collect_Backlog
201 ---------------------------------------------------
202 Procedure Collect_Backlog IS
203 l_stmt varchar2(1000);
204
205 BEGIN
206
207 -- ------------------------
208 -- Create the table needed
209 -- ------------------------
210 l_stmt := 'CREATE TABLE '||g_isc_schema||'.ISC_EDW_BACK_SUM1_SUMM(
211 BACKLOG_SUM1_PK VARCHAR2(240),
212 CUSTOMER_ID VARCHAR2(80),
213 FUNCTIONAL_CURRENCY VARCHAR2(80),
214 INSTANCE_CODE VARCHAR2(80),
215 SET_OF_BOOKS_ID VARCHAR2(80),
216 OPERATING_UNIT_ID VARCHAR2(80),
217 DAYS_OPEN NUMBER,
218 DLQT_BKLG_AMT_B NUMBER,
219 DLQT_BKLG_AMT_G NUMBER,
220 DLQT_BKLG_LINE_COUNT NUMBER,
221 MAX_DAYS_LATE NUMBER,
222 SHIP_BKLG_AMT_B NUMBER,
223 SHIP_BKLG_AMT_G NUMBER,
224 SHIP_BKLG_LINE_COUNT NUMBER,
225 DATE_BOOKED DATE,
226 DATE_OF_SNAPSHOT DATE,
227 HEADER_ID VARCHAR2(80),
228 ORDER_NUMBER VARCHAR2(80))
229 TABLESPACE '||g_tablespace||'
230 NOLOGGING PCTFREE 5
231 STORAGE (INITIAL 4K NEXT 32K)';
232
233 EDW_LOG.Debug_Line('');
234 EDW_LOG.Debug_Line(l_stmt);
235 EXECUTE IMMEDIATE l_stmt;
236
237
238 -- ------------------------
239 -- Populate the table from
240 -- all the valid sources
241 -- ------------------------
242
243 FOR i IN 1..g_rec.count LOOP
244
245 IF (g_rec(i).valid)
246 THEN
247 l_stmt := 'INSERT INTO '||g_isc_schema||'.ISC_EDW_BACK_SUM1_SUMM T
248 (BACKLOG_SUM1_PK,
249 CUSTOMER_ID,
250 FUNCTIONAL_CURRENCY,
251 INSTANCE_CODE,
252 SET_OF_BOOKS_ID,
253 OPERATING_UNIT_ID,
254 DAYS_OPEN,
255 DLQT_BKLG_AMT_B,
256 DLQT_BKLG_AMT_G,
257 DLQT_BKLG_LINE_COUNT,
258 MAX_DAYS_LATE,
259 SHIP_BKLG_AMT_B,
260 SHIP_BKLG_AMT_G,
261 SHIP_BKLG_LINE_COUNT,
262 DATE_BOOKED,
263 DATE_OF_SNAPSHOT,
264 HEADER_ID,
265 ORDER_NUMBER)
266 SELECT /*+ DRIVING_SITE(BACK) */
267 BACKLOG_SUM1_PK,
268 CUSTOMER_ID,
269 FUNCTIONAL_CURRENCY,
270 INSTANCE_CODE,
271 SET_OF_BOOKS_ID,
272 OPERATING_UNIT_ID,
273 DAYS_OPEN,
274 DLQT_BKLG_AMT_B,
275 DLQT_BKLG_AMT_G,
276 DLQT_BKLG_LINE_COUNT,
277 MAX_DAYS_LATE,
278 SHIP_BKLG_AMT_B,
279 SHIP_BKLG_AMT_G,
280 SHIP_BKLG_LINE_COUNT,
281 DATE_BOOKED,
282 DATE_OF_SNAPSHOT,
283 HEADER_ID,
284 ORDER_NUMBER
285 FROM ISCBV_EDW_BACKLOG_SUM1_FCV';
286
287 IF (g_rec(i).same_inst)
288 THEN l_stmt := l_stmt||' BACK ';
289 ELSE l_stmt := l_stmt||'@'||g_rec(i).db_link||' BACK ';
290 END IF;
291
292 EDW_LOG.Debug_Line('');
293 EDW_LOG.Debug_Line(l_stmt);
294 EXECUTE IMMEDIATE l_stmt;
295
296 COMMIT;
297
298 END IF; -- g_rec(i).valid
299
300 END LOOP;
301
302 EXCEPTION
303 WHEN OTHERS
304 THEN
305 g_errbuf := sqlerrm;
306 g_retcode := sqlcode;
307 RAISE g_collect_back_failure;
308
309 END Collect_Backlog;
310
311
312
313 --------------------------------------------------
314 -- PROCEDURE Populate_Backlog_Summary
315 ---------------------------------------------------
316 PROCEDURE Populate_Backlog_Summary IS
317
318 l_stmt VARCHAR2(4000);
319
320 BEGIN
321
322 l_stmt := 'TRUNCATE TABLE '||g_isc_schema||'.ISC_EDW_BACKLOG_SUM1_F';
323
324 EDW_LOG.Debug_Line('');
325 EDW_LOG.Debug_Line(l_stmt);
326 EXECUTE IMMEDIATE l_stmt;
327
328
329 l_stmt := 'INSERT INTO ISC_EDW_BACKLOG_SUM1_F (
330 BACKLOG_SUM1_PK,
331 CREATION_DATE,
332 LAST_UPDATE_DATE,
333 CUSTOMER_FK_KEY,
334 FUNCTIONAL_CURRENCY_FK_KEY,
335 INSTANCE_FK_KEY,
336 SET_OF_BOOKS_FK_KEY,
337 OPERATING_UNIT_FK_KEY,
338 DAYS_OPEN,
339 DLQT_BKLG_AMT_B,
340 DLQT_BKLG_AMT_G,
341 DLQT_BKLG_LINE_COUNT,
342 MAX_DAYS_LATE,
343 SHIP_BKLG_AMT_B,
344 SHIP_BKLG_AMT_G,
345 SHIP_BKLG_LINE_COUNT,
346 CUSTOMER_NAME,
347 DATE_BOOKED,
348 DATE_OF_SNAPSHOT,
349 HEADER_ID,
350 INSTANCE_CODE,
351 OPERATING_UNIT_NAME,
352 ORDER_NUMBER)
353 SELECT summary.BACKLOG_SUM1_PK,
354 SYSDATE,
355 SYSDATE,
356 cust.TPRT_TRADE_PARTNER_PK_KEY,
357 curr.CRNC_CURRENCY_PK_KEY,
358 inst.INST_INSTANCE_PK_KEY,
359 sob.FABK_FA_BOOK_PK_KEY,
360 org.OPER_OPERATING_UNIT_PK_KEY,
361 summary.DAYS_OPEN,
362 summary.DLQT_BKLG_AMT_B,
363 summary.DLQT_BKLG_AMT_G,
364 summary.DLQT_BKLG_LINE_COUNT,
365 summary.MAX_DAYS_LATE,
366 summary.SHIP_BKLG_AMT_B,
367 summary.SHIP_BKLG_AMT_G,
368 summary.SHIP_BKLG_LINE_COUNT,
369 cust.TPRT_NAME,
370 summary.DATE_BOOKED,
371 summary.DATE_OF_SNAPSHOT,
372 summary.HEADER_ID,
373 summary.INSTANCE_CODE,
374 org.OPER_NAME,
375 summary.ORDER_NUMBER
376 FROM '||g_isc_schema||'.ISC_EDW_BACK_SUM1_SUMM summary,
377 edw_instance_m inst,
378 edw_currency_m curr,
379 edw_gl_book_m sob,
380 edw_organization_m org,
381 edw_trd_partner_m cust
382 WHERE sob.fabk_fa_book_pk
383 = summary.set_of_books_id||''-''||summary.instance_code
384 AND inst.inst_instance_pk = summary.instance_code
385 AND cust.tplo_tpartner_loc_pk
386 = summary.customer_id||''-''||summary.instance_code||''-CUST_ACCT-TPRT''
387 AND org.orga_organization_pk
388 = summary.operating_unit_id||''-''||summary.instance_code
389 AND curr.crnc_currency_pk = summary.functional_currency';
390
391 EDW_LOG.Debug_Line('');
392 EDW_LOG.Debug_Line(l_stmt);
393 EXECUTE IMMEDIATE l_stmt;
394
395 EXCEPTION
396 WHEN OTHERS
397 THEN
398 g_errbuf := sqlerrm;
399 g_retcode := sqlcode;
400 RAISE g_pop_back_sum_failure;
401
402 END Populate_Backlog_Summary;
403
404
405 -- ---------------------------------
406 -- Public PROCEDURES AND FUNCTIONS
407 -- ---------------------------------
408
409 --------------------------------------------------
410 -- PROCEDURE Populate
411 ---------------------------------------------------
412 PROCEDURE Populate( errbuf IN OUT NOCOPY VARCHAR2,
413 retcode IN OUT NOCOPY VARCHAR2) IS
414
415 l_errbuf VARCHAR2(1000) := NULL;
416 l_retcode VARCHAR2(200) := NULL;
417 l_dir VARCHAR2(400);
418 l_stmt VARCHAR2(100);
419
420 BEGIN
421
422
423 l_stmt := 'ALTER SESSION SET GLOBAL_NAMES = FALSE';
424 EXECUTE IMMEDIATE l_stmt;
425
426 IF (fnd_profile.value('EDW_DEBUG') = 'Y')
427 THEN EDW_LOG.G_Debug := TRUE;
428 END IF;
429
430 l_dir := FND_PROFILE.Value('EDW_LOGFILE_DIR');
431 IF l_dir IS NULL
432 THEN l_dir := '/sqlcom/log';
433 END IF;
434 EDW_LOG.Put_Names('ISC_EDW_BACKLOG_SUM1_F.log','ISC_EDW_BACKLOG_SUM1_F.out',l_dir);
435
436
437 FII_UTIL.Put_Timestamp;
438 EDW_LOG.Put_Line('');
439 EDW_LOG.Put_Line('Initialization');
440
441 FII_UTIL.Start_Timer;
442
443 Init;
444
445 FII_UTIL.Stop_Timer;
446 FII_UTIL.Print_Timer('Duration');
447
448 EDW_LOG.Put_Line('');
449 EDW_LOG.Put_Line('Dropping the Intermediary Summary Table');
450 FII_UTIL.Start_Timer;
451
452 Drop_Table('ISC_EDW_BACK_SUM1_SUMM');
453
454 FII_UTIL.Stop_Timer;
455 FII_UTIL.Print_Timer('Duration');
456
457 EDW_LOG.Put_Line('');
458 EDW_LOG.Put_Line('Truncating the Summary Table');
459 FII_UTIL.Start_Timer;
460
461 Truncate_Table('ISC_EDW_BACKLOG_SUM1_F');
462
463 FII_UTIL.Stop_Timer;
464 FII_UTIL.Print_Timer('Duration');
465
466
467 EDW_LOG.Put_Line('');
468 EDW_LOG.Put_Line('Extracting Backlog information from all the source instances ');
469 FII_UTIL.Start_Timer;
470
471 Collect_Backlog;
472
473 FII_UTIL.Stop_Timer;
474 FII_UTIL.Print_Timer('Duration');
475
476 EDW_LOG.Put_Line('');
477 EDW_LOG.Put_Line('Populate the Backlog Summary Table ');
478 FII_UTIL.Start_Timer;
479
480 Populate_Backlog_Summary;
481
482 FII_UTIL.Stop_Timer;
483 FII_UTIL.Print_Timer('Duration');
484
485
486 EXCEPTION
487
488 WHEN G_DROP_TABLE_FAILURE
489 THEN
490 errbuf := g_errbuf;
491 retcode := g_retcode;
492 g_exception_msg := retcode || ':' || errbuf;
493 ROLLBACK;
494 EDW_LOG.Put_Line('Drop Table has failed : '|| g_exception_msg);
495 RAISE;
496
497
498 WHEN G_TRUNC_TABLE_FAILURE
499 THEN
500 errbuf := g_errbuf;
501 retcode := g_retcode;
502 g_exception_msg := retcode || ':' || errbuf;
503 ROLLBACK;
504 EDW_LOG.Put_Line('Truncate Table has failed : '|| g_exception_msg);
505 RAISE;
506
507
508 WHEN G_COLLECT_BACK_FAILURE
509 THEN
510 errbuf := g_errbuf;
511 retcode := g_retcode;
512 g_exception_msg := retcode || ':' || errbuf;
513 ROLLBACK;
514 EDW_LOG.Put_Line('Collect Backlog has failed : '|| g_exception_msg);
515 RAISE;
516
517 WHEN G_POP_BACK_SUM_FAILURE
518 THEN
519 errbuf := g_errbuf;
520 retcode := g_retcode;
521 g_exception_msg := retcode || ':' || errbuf;
522 ROLLBACK;
523 EDW_LOG.Put_Line('Populate Backlog Summary has failed : '|| g_exception_msg);
524 RAISE;
525
526 WHEN OTHERS
527 THEN
528 errbuf := g_errbuf;
529 retcode := g_retcode;
530 g_exception_msg := retcode || ':' || errbuf;
531 ROLLBACK;
532 EDW_LOG.Put_Line('Other errors : '|| g_exception_msg);
533 RAISE;
534
535
536 END Populate;
537
538 END ISC_EDW_BACKLOG_SUM1_F_C;