[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPI_JOB_DETAIL_F_C
Source
1 Package Body OPI_EDW_OPI_JOB_DETAIL_F_C as
2 /* $Header: OPIMJDTB.pls 120.1 2006/05/11 02:51:39 vganeshk noship $ */
3 g_push_from_date Date:=Null;
4 g_push_to_date Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7 g_errbuf VARCHAR2(2000):=NULL;
8 g_retcode VARCHAR2(200) :=NULL;
9
10
11 ---------------------------------------------------
12 -- FUNCTION IDENTIFY_CHANGE by checking last_update_date
13 ---------------------------------------------------
14 FUNCTION IDENTIFY_CHANGE( p_view_id IN NUMBER,
15 p_count OUT NOCOPY NUMBER) RETURN NUMBER
16 IS
17
18 l_seq_id NUMBER := -1;
19 l_opi_schema VARCHAR2(30);
20 l_status VARCHAR2(30);
21 l_industry VARCHAR2(30);
22 BEGIN
23
24 p_count := 0;
25
26 SELECT OPI_EDW_JOB_DETAIL_INC_S.NEXTVAL INTO l_seq_id FROM dual;
27
28 INSERT
29 INTO OPI_EDW_OPI_JOB_DETAIL_INC(primary_key1, primary_key2, primary_key3, seq_id)
30 SELECT
31 primary_key1,
32 primary_key2,
33 primary_key3,
34 l_seq_id
35 FROM
36 (
37 SELECT
38 JOBS.WIP_ENTITY_ID primary_key1,
39 JOBS.REPETITIVE_SCHEDULE_ID primary_key2,
40 JOBS.JOB_ID primary_key3
41 FROM
42 (
43 SELECT
44 EN.WIP_ENTITY_ID WIP_ENTITY_ID,
45 TO_NUMBER(NULL) REPETITIVE_SCHEDULE_ID,
46 EN.WIP_ENTITY_ID || '-' JOB_ID,
47 MAX(GREATEST(EN.LAST_UPDATE_DATE, DI.LAST_UPDATE_DATE, WPB.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
48 FROM WIP_ENTITIES EN , WIP_DISCRETE_JOBS DI, WIP_PERIOD_BALANCES WPB
49 WHERE
50 DI.STATUS_TYPE IN (4,5,7,12) AND
51 DI.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND DI.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
52 DI.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
53 GROUP BY
54 EN.WIP_ENTITY_ID
55 UNION
56 SELECT
57 EN.WIP_ENTITY_ID WIP_ENTITY_ID,
58 RE.REPETITIVE_SCHEDULE_ID REPETITIVE_SCHEDULE_ID,
59 EN.WIP_ENTITY_ID || '-' || RE.REPETITIVE_SCHEDULE_ID JOB_ID,
60 MAX(GREATEST(EN.LAST_UPDATE_DATE, RE.LAST_UPDATE_DATE, WPB.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
61 FROM
62 WIP_ENTITIES EN, WIP_REPETITIVE_SCHEDULES RE, WIP_PERIOD_BALANCES WPB
63 WHERE
64 RE.STATUS_TYPE IN (4,5,7,12) AND
65 RE.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND RE.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
66 RE.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID AND RE.REPETITIVE_SCHEDULE_ID = WPB.REPETITIVE_SCHEDULE_ID
67 GROUP BY
68 EN.WIP_ENTITY_ID, RE.REPETITIVE_SCHEDULE_ID
69 UNION
70 SELECT
71 EN.WIP_ENTITY_ID WIP_ENTITY_ID,
72 TO_NUMBER(NULL) REPETITIVE_SCHEDULE_ID,
73 EN.WIP_ENTITY_ID || '-' JOB_ID,
74 MAX(GREATEST(EN.LAST_UPDATE_DATE, FL.LAST_UPDATE_DATE, WPB.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
75 FROM
76 WIP_ENTITIES EN , WIP_FLOW_SCHEDULES FL, WIP_PERIOD_BALANCES WPB
77 WHERE
78 FL.STATUS = 2 AND
79 FL.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND FL.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
80 FL.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
81 GROUP BY
82 EN.WIP_ENTITY_ID
83 ) JOBS,
84 (
85 SELECT /*+ parallel(mmt) */
86 MMTMMTA.TRANSACTION_SOURCE_ID WIP_ENTITY_ID,
87 MMTMMTA.REPETITIVE_SCHEDULE_ID REPETITIVE_SCHEDULE_ID,
88 MMTMMTA.JOB_ID,
89 MAX(GREATEST(MMTMMTA.LAST_UPDATE_DATE, WRO.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
90 FROM
91 (select MMT.TRANSACTION_SOURCE_ID,
92 MMTA.REPETITIVE_SCHEDULE_ID ,
93 MMT.TRANSACTION_SOURCE_ID ||'-'|| NVL(MMTA.REPETITIVE_SCHEDULE_ID,'') JOB_ID,
94 GREATEST(NVL(MMT.LAST_UPDATE_DATE, TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
95 NVL(MMTA.LAST_UPDATE_DATE, TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
96 NVL(WSV.LAST_UPDATE_DATE,TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss'))) LAST_UPDATE_DATE
97 from MTL_MATERIAL_TRANSACTIONS MMT, MTL_MATERIAL_TXN_ALLOCATIONS MMTA,
98 WIP_SCRAP_VALUES WSV,
99 MTL_PARAMETERS MP
100 where
101 (MMT.TRANSACTION_ACTION_ID IN (1, 27, 33, 34, 31, 32, 30)) AND
102 MMT.TRANSACTION_SOURCE_TYPE_ID = 5 AND
103 MMT.TRANSACTION_ID = MMTA.TRANSACTION_ID (+) AND
104 MMT.TRANSACTION_ID = WSV.TRANSACTION_ID (+) AND
105 MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID AND
106 MP.PROCESS_ENABLED_FLAG > 'Y'
107 ) MMTMMTA,
108 (select WRO.WIP_ENTITY_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.LAST_UPDATE_DATE,
109 WRO.WIP_ENTITY_ID ||'-'|| NVL(WRO.REPETITIVE_SCHEDULE_ID,'') JOB_ID
110 from WIP_REQUIREMENT_OPERATIONS WRO) WRO
111 WHERE
112 MMTMMTA.JOB_ID = WRO.JOB_ID (+)
113 GROUP BY
114 MMTMMTA.TRANSACTION_SOURCE_ID, MMTMMTA.REPETITIVE_SCHEDULE_ID, MMTMMTA.JOB_ID
115 ) JOBITEMTOTAL_MAT_BPR_SCRAP
116 WHERE
117 JOBS.JOB_ID = JOBITEMTOTAL_MAT_BPR_SCRAP.JOB_ID (+) AND
118 GREATEST(NVL(JOBITEMTOTAL_MAT_BPR_SCRAP.LAST_UPDATE_DATE,TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
119 NVL(JOBS.LAST_UPDATE_DATE,TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
120 BETWEEN g_push_from_date and g_push_to_date
121 UNION
122 SELECT
123 primary_key1,
124 primary_key2,
125 primary_key3
126 FROM
127 OPI_EDW_OPI_JOB_DETAIL_INC
128 );
129
130 p_count := SQL%rowcount;
131
132 DELETE FROM OPI_EDW_OPI_JOB_DETAIL_INC WHERE seq_id IS NULL;
133 COMMIT;
134
135 RETURN(l_seq_id);
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 g_errbuf:=sqlerrm;
140 g_retcode:=sqlcode;
141 RETURN(-1);
142 END identify_change;
143
144 -----------------------------------------------------------
145 --FUNCTION PUSH_TO_LOCAL
146 -----------------------------------------------------------
147
148 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER,
149 p_seq_id NUMBER) RETURN NUMBER IS
150 BEGIN
151
152 -- ------------------------------------------------
153 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
154 -- In case of source=target, we need to separate
155 -- out the records in progress vs the records which
156 -- is ready to be picked up by collection enginee.
157 -- In our case, we consider the records to be in
158 -- progress until all the child processes have
159 -- completed successfully.
160 -- ------------------------------------------------
161 /* FIX for BUG # 1695577 */
162 /* Removed TO_NUMBER(NULL) and replaced with STANDARD_QTY */
163 /* in the Select statement */
164
165 INSERT INTO OPI_EDW_JOB_DETAIL_FSTG
166 (
167 JOB_DETAIL_PK,
168 LOCATOR_FK,
169 ITEM_FK,
170 PRD_LINE_FK,
171 TRX_DATE_FK,
172 SOB_CURRENCY_FK,
173 BASE_UOM_FK,
174 INSTANCE_FK,
175 USER_FK1,
176 USER_FK2,
177 USER_FK3,
178 USER_FK4,
179 USER_FK5,
180 ACT_BPR_VAL_B,
181 ACT_BPR_VAL_G,
182 ACT_CMPL_DATE,
183 ACT_CNCL_DATE,
184 ACT_INP_VAL_B,
185 ACT_INP_VAL_G,
186 ACT_JOB_TIME,
187 ACT_MTL_INP_VAL_B,
188 ACT_MTL_INP_VAL_G,
189 ACT_OUT_QTY,
190 ACT_OUT_VAL_B,
191 ACT_OUT_VAL_G,
192 ACT_SCR_VAL_B,
193 ACT_SCR_VAL_G,
194 ACT_STRT_DATE,
195 CREATION_DATE,
196 FST_PASS_YLD,
197 JOB_NO,
198 JOB_STATUS,
199 LAST_UPDATE_DATE,
200 MFG_MODE,
201 MOVE_TIME,
202 NO_ADJ,
203 NO_TIME_RESH,
204 PLN_BPR_VAL_B,
205 PLN_BPR_VAL_G,
206 PLN_CMPL_DATE,
207 PLN_INP_VAL_B,
208 PLN_INP_VAL_G,
209 PLN_JOB_TIME,
210 PLN_MTL_INP_VAL_B,
211 PLN_MTL_INP_VAL_G,
212 PLN_OUT_QTY,
213 PLN_OUT_VAL_B,
214 PLN_OUT_VAL_G,
215 PLN_SCR_VAL_B,
216 PLN_SCR_VAL_G,
217 PLN_STRT_DATE,
218 QC_FAIL_QTY,
219 QC_TEST,
220 QUEUE_TIME,
221 RESH_REASON_CODE,
222 RES_LOOKUP_FK,
223 REWORK_QTY,
224 RUN_TIME,
225 SETUP_TIME,
226 SMPL_CNT,
227 STD_QTY,
228 STD_TIME,
229 STD_VAL_B,
230 STD_VAL_G,
231 STS_LOOKUP_FK,
232 USER_ATTRIBUTE1,
233 USER_ATTRIBUTE10,
234 USER_ATTRIBUTE11,
235 USER_ATTRIBUTE12,
236 USER_ATTRIBUTE13,
237 USER_ATTRIBUTE14,
238 USER_ATTRIBUTE15,
239 USER_ATTRIBUTE2,
240 USER_ATTRIBUTE3,
241 USER_ATTRIBUTE4,
242 USER_ATTRIBUTE5,
243 USER_ATTRIBUTE6,
244 USER_ATTRIBUTE7,
245 USER_ATTRIBUTE8,
246 USER_ATTRIBUTE9,
247 USER_MEASURE1,
248 USER_MEASURE2,
249 USER_MEASURE3,
250 USER_MEASURE4,
251 USER_MEASURE5,
252 OPERATION_CODE,
253 COLLECTION_STATUS)
254 SELECT /*+ ALL_ROWS */
255 JOB_DETAIL_PK,
256 NVL(LOCATOR_FK,'NA_EDW'),
257 NVL(ITEM_FK,'NA_EDW'),
258 NVL(PRD_LINE_FK,'NA_EDW'),
259 NVL(TRX_DATE_FK,'NA_EDW'),
260 NVL(SOB_CURRENCY_FK,'NA_EDW'),
261 NVL(BASE_UOM_FK,'NA_EDW'),
262 NVL(INSTANCE_FK,'NA_EDW'),
263 NVL(USER_FK1,'NA_EDW'),
264 NVL(USER_FK2,'NA_EDW'),
265 NVL(USER_FK3,'NA_EDW'),
266 NVL(USER_FK4,'NA_EDW'),
267 NVL(USER_FK5,'NA_EDW'),
268 ACT_BPR_VAL_B,
269 ACT_BPR_VAL_B * CONVERSION_RATE,
270 ACT_CMPL_DATE,
271 ACT_CNCL_DATE,
272 ACT_INP_VAL_B,
273 ACT_INP_VAL_B * CONVERSION_RATE,
274 ACT_JOB_TIME,
275 ACT_MTL_INP_VAL_B,
276 ACT_MTL_INP_VAL_B * CONVERSION_RATE,
277 ACT_OUT_QTY,
278 ACT_OUT_VAL_B,
279 ACT_OUT_VAL_B * CONVERSION_RATE,
280 ACT_SCR_VAL_B,
281 ACT_SCR_VAL_B * CONVERSION_RATE,
282 ACT_STRT_DATE,
283 CREATION_DATE,
284 TO_NUMBER(NULL),
285 JOB_NO,
286 JOB_STATUS,
287 LAST_UPDATE_DATE,
288 MFG_MODE,
289 TO_NUMBER(NULL),
290 TO_NUMBER(NULL),
291 TO_NUMBER(NULL),
292 PLN_BPR_VAL_B,
293 PLN_BPR_VAL_B * CONVERSION_RATE,
294 PLN_CMPL_DATE,
295 PLN_INP_VAL_B,
296 PLN_INP_VAL_B * CONVERSION_RATE,
297 PLN_JOB_TIME,
298 PLN_MTL_INP_VAL_B,
299 PLN_MTL_INP_VAL_B * CONVERSION_RATE,
300 PLN_OUT_QTY,
301 PLN_OUT_VAL_B,
302 PLN_OUT_VAL_B * CONVERSION_RATE,
303 PLN_SCR_VAL_B,
304 PLN_SCR_VAL_B * CONVERSION_RATE,
305 PLN_STRT_DATE,
306 TO_NUMBER(NULL),
307 NULL,
308 TO_NUMBER(NULL),
309 NULL,
310 'NA_EDW',
311 TO_NUMBER(NULL),
312 TO_NUMBER(NULL),
313 TO_NUMBER(NULL),
314 TO_NUMBER(NULL),
315 STANDARD_QTY,
316 STD_TIME,
317 STD_VAL_B,
318 STD_VAL_B * CONVERSION_RATE,
319 'NA_EDW',
320 USER_ATTRIBUTE1,
321 USER_ATTRIBUTE10,
322 USER_ATTRIBUTE11,
323 USER_ATTRIBUTE12,
324 USER_ATTRIBUTE13,
325 USER_ATTRIBUTE14,
326 USER_ATTRIBUTE15,
327 USER_ATTRIBUTE2,
328 USER_ATTRIBUTE3,
329 USER_ATTRIBUTE4,
330 USER_ATTRIBUTE5,
331 USER_ATTRIBUTE6,
332 USER_ATTRIBUTE7,
333 USER_ATTRIBUTE8,
334 USER_ATTRIBUTE9,
335 USER_MEASURE1,
336 USER_MEASURE2,
337 USER_MEASURE3,
338 USER_MEASURE4,
339 USER_MEASURE5,
340 NULL, -- OPERATION_CODE
341 DECODE( CONVERSION_RATE, -1, 'RATE NOT AVAILABLE', DECODE( CONVERSION_RATE, -2, 'INVALID CURRENCY', 'LOCAL READY') )
342 FROM OPI_EDW_OPI_JOB_DETAIL_FCV
343 WHERE view_id = p_view_id
344 AND seq_id = p_seq_id;
345
346 RETURN(sql%rowcount);
347
348 EXCEPTION
349 WHEN OTHERS THEN
350 g_errbuf:=sqlerrm;
351 g_retcode:=sqlcode;
352 RETURN(-1);
353 END PUSH_TO_LOCAL;
354
355
356 -- ---------------------------------
357 -- PUBLIC PROCEDURES
358 -- ---------------------------------
359
360 -----------------------------------------------------------
361 -- PROCEDURE PUSH
362 -----------------------------------------------------------
363 PROCEDURE Push(Errbuf in out NOCOPY Varchar2,
364 Retcode in out NOCOPY Varchar2,
365 p_from_date IN varchar2,
366 p_to_date IN varchar2) IS
367
368 l_fact_name VARCHAR2(30) :='OPI_EDW_JOB_DETAIL_F' ;
369 l_staging_table VARCHAR2(30) :='OPI_EDW_JOB_DETAIL_FSTG';
370 l_opi_schema VARCHAR2(30);
371 l_status VARCHAR2(30);
372 l_industry VARCHAR2(30);
373 l_exception_msg VARCHAR2(2000):=Null;
374
375 l_from_date DATE := NULL;
376 l_to_date DATE := NULL;
377
378 l_seq_id_view1 NUMBER := 0;
379 l_row_count_view1 NUMBER := 0;
380 l_row_count NUMBER := 0;
381
382 l_push_local_failure EXCEPTION;
383 l_iden_change_failure EXCEPTION;
384
385 /*
386 l_date1 Date:=Null;
387 l_date2 Date:=Null;
388 l_temp_date Date:=Null;
389 l_rows_inserted Number:=0;
390 l_duration Number:=0;
391 */
392
393 -- -------------------------------------------
394 -- Put any additional developer variables here
395 -- -------------------------------------------
396
397 CURSOR cur_missing_rates IS
398 SELECT DISTINCT
399 SOB_CURRENCY_FK FROM_CURRENCY,
400 NVL(SUBSTR(ACT_CMPL_DATE,1,10),CREATION_DATE) C_DATE,
401 COLLECTION_STATUS
402 FROM
403 OPI_EDW_JOB_DETAIL_FSTG
404 WHERE
405 SUBSTRB(JOB_DETAIL_PK,LENGTH(JOB_DETAIL_PK)-2,3) = 'OPI'
406 AND COLLECTION_STATUS IN ('RATE NOT AVAILABLE', 'INVALID CURRENCY')
407 ORDER BY FROM_CURRENCY, C_DATE;
408
409 l_SOB_CURRENCY_FK VARCHAR2(80);
410 l_C_DATE DATE;
411 l_COLLECTION_STATUS VARCHAR2(30);
412 l_rows_deleted Number:=0;
413
414 BEGIN
415 Errbuf :=NULL;
416 Retcode:=0;
417
418 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,
419 l_staging_table,
420 l_staging_table,
421 l_exception_msg)) THEN
422 errbuf := fnd_message.get;
423 Return;
424 END IF;
425
426 l_from_date := To_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
427 l_to_date := To_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
428
429 g_push_from_date
430 := nvl(l_from_date,
431 EDW_COLLECTION_UTIL.G_local_last_push_start_date
432 - EDW_COLLECTION_UTIL.g_offset);
433
434 g_push_to_date:= nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
435
436 --l_date1 := g_push_date_range1;
437 --l_date2 := g_push_date_range2;
438
439 edw_log.put_line( 'The collection range is from '||
440 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
441 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
442 edw_log.put_line(' ');
443
444 -- --------------------------------------------------------
445 -- Identify Change for View Type 1
446 -- --------------------------------------------------------
447 edw_log.put_line(' ');
448 edw_log.put_line('Identifying change in view type 1');
449
450 l_row_count := 0;
451 l_seq_id_view1 := identify_change( p_view_id => 1,
452 p_count => l_row_count );
453 IF (l_seq_id_view1 = -1 ) THEN
454 RAISE l_iden_change_failure;
455 END IF;
456
457 edw_log.put_line('Identified '|| l_row_count
458 || ' changed records in view type 1. ');
459
460 --RAISE l_iden_change_failure;
461 -- --------------------------------------------------------
462 -- Analyze the incremental table
463 -- --------------------------------------------------------
464 IF fnd_installation.get_app_info( 'OPI', l_status,
465 l_industry, l_opi_schema) THEN
466 fnd_stats.gather_table_stats(ownname=> l_opi_schema,
467 tabname=> 'OPI_EDW_OPI_JOB_DETAIL_INC' );
468 END IF;
469
470 -- --------------------------------------------------------
471 -- Pushing data to local staging table
472 -- --------------------------------------------------------
473 edw_log.put_line(' ');
474 edw_log.put_line('Inserting into local staging table for view type 1');
475
476 l_row_count_view1 := push_to_local( p_view_id => 1,
477 p_seq_id => l_seq_id_view1 );
478 IF l_row_count_view1 = -1 THEN
479 RAISE l_push_local_failure;
480 END IF;
481
482 edw_log.put_line('Inserted ' || Nvl(l_row_count_view1,0) ||
483 ' rows into local staging table for view type 1');
484 edw_log.put_line(' ');
485
486 --
487 g_row_count := l_row_count_view1;
488
489 edw_log.put_line('For all view types, inserted ' || Nvl(g_row_count,0)
490 || ' rows into local staging table.');
491 edw_log.put_line(' ');
492
493
494 -- ---------------------------------------
495 -- Delete all incremental table's records
496 -- ---------------------------------------
497
498 execute immediate 'truncate table '||l_opi_schema||'.OPI_EDW_OPI_JOB_DETAIL_INC ';
499
500 -- --------------------------------------------------------------------------
501 -- Insert missing currency rate/invalid currency rows into incremental table
502 -- --------------------------------------------------------------------------
503
504 INSERT
505 INTO OPI_EDW_OPI_JOB_DETAIL_INC(primary_key1, primary_key2, primary_key3)
506 SELECT distinct
507 SUBSTRB(JOB_DETAIL_PK,INSTR(JOB_DETAIL_PK,'-',1,1)+1,INSTR(JOB_DETAIL_PK,'-',1,2)-INSTR(JOB_DETAIL_PK,'-',1,1)-1) primary_key1,
508 SUBSTRB(JOB_DETAIL_PK,INSTR(JOB_DETAIL_PK,'-',1,2)+1,INSTR(JOB_DETAIL_PK,'-',1,3)-INSTR(JOB_DETAIL_PK,'-',1,2)-1) primary_key2,
509 SUBSTRB(JOB_DETAIL_PK,INSTR(JOB_DETAIL_PK,'-',1,1)+1,INSTR(JOB_DETAIL_PK,'-',1,3)-INSTR(JOB_DETAIL_PK,'-',1,1)-1) primary_key3
510 FROM
511 OPI_EDW_JOB_DETAIL_FSTG
512 WHERE
513 SUBSTRB(JOB_DETAIL_PK,LENGTH(JOB_DETAIL_PK)-2,3) = 'OPI'
514 AND COLLECTION_STATUS IN ('RATE NOT AVAILABLE', 'INVALID CURRENCY');
515
516 -- Create output file
517 OPEN cur_missing_rates;
518 FETCH cur_missing_rates INTO L_SOB_CURRENCY_FK, L_C_DATE, l_COLLECTION_STATUS;
519 IF cur_missing_rates%FOUND THEN
520
521 edw_log.put_line(' ');
522 edw_log.put_line('There are missing currency rates/invalid currencies. Please check the output file.');
523 edw_log.put_line(' ');
524 edw_log.put_line ('MISSING RATES');
525 edw_log.put_line ('=============');
526 edw_log.put_line (' ');
527 edw_log.put_line ('FROM CURRENCY CONVERSION DATE COLLECTION STATUS');
528 edw_log.put_line ('============= =============== =================' );
529 edw_log.put_line ( RPAD(L_SOB_CURRENCY_FK,13,' ') || ' ' || RPAD(L_C_DATE,15,' ') || ' ' || l_COLLECTION_STATUS);
530
531 LOOP
532 FETCH cur_missing_rates INTO L_SOB_CURRENCY_FK, L_C_DATE, l_COLLECTION_STATUS;
533 EXIT WHEN cur_missing_rates%NOTFOUND;
534 edw_log.put_line ( RPAD(L_SOB_CURRENCY_FK,13,' ') || ' ' || RPAD(L_C_DATE,15,' ') || ' ' || l_COLLECTION_STATUS);
535 END LOOP;
536 edw_log.put_line (' ');
537 edw_log.put_line ('--- END OF FILE ----');
538 END IF;
539 CLOSE cur_missing_rates;
540
541 if EDW_COLLECTION_UTIL.source_same_as_target then
542 DELETE FROM OPI_EDW_JOB_DETAIL_FSTG
543 WHERE SUBSTRB(JOB_DETAIL_PK,LENGTH(JOB_DETAIL_PK)-2,3) = 'OPI'
544 AND COLLECTION_STATUS IN ('RATE NOT AVAILABLE', 'INVALID CURRENCY');
545 l_rows_deleted:= sql%rowcount;
546 edw_log.put_line(' ');
547
548 edw_log.put_line('Deleted '||nvl(l_rows_deleted,0)||
549 ' missing rate/invalid currency rows from local staging table');
550 edw_log.put_line(' ');
551
552 edw_log.put_line('There are ' || to_char(Nvl(g_row_count,0) - nvl(l_rows_deleted,0))
553 || ' remaining rows in local staging table.');
554 edw_log.put_line(' ');
555 end if;
556
557 -- --------------------------------------------------------
558 -- No exception raised so far. Call wrapup to transport
559 -- data to target database, and insert messages into logs
560 -- --------------------------------------------------------
561 edw_log.put_line(' ');
562 edw_log.put_line('Inserted '|| to_char(nvl(g_row_count,0) - nvl(l_rows_deleted,0))||
563 ' rows into the staging table');
564 edw_log.put_line(' ');
565
566 EDW_COLLECTION_UTIL.wrapup(TRUE,
567 g_row_count,
568 l_exception_msg,
569 g_push_from_date,
570 g_push_to_date);
571
572 -- ---------------------------------------------------------------------------
573 -- END OF Collection , Developer Customizable Section
574 -- ---------------------------------------------------------------------------
575
576 EXCEPTION
577 WHEN L_PUSH_LOCAL_FAILURE THEN
578 Errbuf:=g_errbuf;
579 Retcode:=g_retcode;
580 l_exception_msg := Retcode || ':' || Errbuf;
581 rollback; -- Rollback insert into local staging
582 edw_log.put_line('Inserting into local staging have failed');
583 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
584 raise;
585
586 WHEN L_IDEN_CHANGE_FAILURE THEN
587 Errbuf:=g_errbuf;
588 Retcode:=g_retcode;
589 l_exception_msg := Retcode || ':' || Errbuf;
590
591 IF fnd_installation.get_app_info( 'OPI', l_status,
592 l_industry, l_opi_schema) THEN
593 execute immediate 'truncate table ' || l_opi_schema
594 || '.opi_edw_opi_job_detail_inc ';
595 END IF;
596 edw_log.put_line('Identifying changed records have Failed');
597 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
598 raise;
599
600 WHEN OTHERS THEN
601 Errbuf:= Sqlerrm;
602 Retcode:=sqlcode;
603 l_exception_msg := Retcode || ':' || Errbuf;
604 rollback;
605 edw_log.put_line('Other errors');
606 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
607 g_push_from_date, g_push_to_date);
608 raise;
609
610 END push;
611
612 End OPI_EDW_OPI_JOB_DETAIL_F_C;