[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPM_JOB_DETAIL_F_C
Source
1 Package Body OPI_EDW_OPM_JOB_DETAIL_F_C AS
2 /* $Header: OPIMOJDB.pls 115.16 2004/01/02 19:05:59 bthammin noship $ */
3 g_errbuf VARCHAR2(2000) := NULL;
4 g_retcode VARCHAR2(200) := NULL;
5 g_row_count NUMBER:=0;
6 g_push_from_date DATE := NULL;
7 g_push_to_date DATE := NULL;
8 g_seq_id NUMBER:=0;
9 -- ---------------------------------
10 -- PRIVATE PROCEDURES AND FUNCTIONS
11 -- ---------------------------------
12
13 /* Find if Source and target are on same instance */
14 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
15 IS
16 l_source Varchar2(100) :=Null;
17 l_Target Varchar2(100) :=Null;
18 BEGIN
19 SELECT instance_code INTO l_source
20 FROM edw_local_instance;
21
22 SELECT instance_code INTO l_target
23 FROM edw_local_instance@edw_apps_to_wh;
24
25 IF (l_source = l_target) THEN
26 RETURN TRUE;
27 END IF;
28 RETURN FALSE;
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 g_errbuf:=sqlerrm;
32 g_retcode:=sqlcode;
33 RETURN FALSE;
34 END LOCAL_SAME_AS_REMOTE;
35
36
37 /* Procedure to Print Missing Rows */
38 PROCEDURE PRINT_MISSING_ROWS
39 IS
40 /* Define Missing Rate Cursor for Job Detail */
41 Cursor Missing_Rate is
42 SELECT JOB_DETAIL_PK,TRX_DATE_FK,SOB_CURRENCY_FK,INSTANCE_FK
43 FROM
44 OPI_EDW_JOB_DETAIL_FSTG
45 WHERE
46 COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
47 AND JOB_DETAIL_PK like '%OPM'
48 AND SUBSTRB(JOB_DETAIL_PK,instrB(JOB_DETAIL_PK,'-',1,1)+1,
49 instrB(JOB_DETAIL_PK,'-',1,2)-1-instrB(JOB_DETAIL_PK,'-',1,1))
50 in (select BATCH_ID from PM_MATL_DTL WHERE
51 LINE_ID in (select PRIMARY_KEY from
52 OPI_EDW_OPM_JOB_DETAIL_INC
53 WHERE SEQ_ID is NULL));
54
55 BEGIN
56 /* Print Header */
57 edw_log.put_line(' ');
58 edw_log.put_line ('Identified Missing Rows Date:'||SYSDATE);
59
60 edw_log.put_line (' ');
61 edw_log.put_line (' ');
62
63 edw_log.put_line ('Primary Key is PM_BTCH_HDR.Plant_code - PM_BTCH_HDR.Batch_id - PM_MATL_DTL.Item_id - instance_code - OPM');
64 edw_log.put_line ('-----------------------------------------');
65 edw_log.put_line ('Primary Key / Currency / Transaction Date');
66 edw_log.put_line ('-----------------------------------------');
67 /* Print Rows */
68
69 For l_rows in missing_rate loop
70 edw_log.put_line (l_rows.JOB_DETAIL_PK||' / '||l_rows.SOB_CURRENCY_FK||' / '||l_rows.TRX_DATE_FK);
71 end loop;
72 edw_log.put_line(' ');
73 edw_log.put_line(' ');
74 EXCEPTION
75 WHEN OTHERS THEN
76 g_errbuf:=sqlerrm;
77 g_retcode:=sqlcode;
78 edw_log.put_line('Raised Exception from PRINT_MISSING_RATE '||sqlerrm);
79 END;
80
81 /* Procedure to Push missing rows */
82 PROCEDURE PUSH_MISSING_ROWS
83 IS
84 l_count number;
85 BEGIN
86 /* Delete the incremental table before inserting new data */
87 DELETE OPI_EDW_OPM_JOB_DETAIL_INC;
88 edw_log.put_line(' ');
89 edw_log.Put_line('Identifying Missing Rate Rows ');
90 edw_log.put_line(' ');
91 SELECT count(*) into l_count from OPI_EDW_JOB_DETAIL_FSTG where
92 COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
93 AND JOB_DETAIL_PK like '%OPM';
94 IF l_count > 0 THEN
95
96
97 /* insert into Incremental table all line_id where Currency is missing */
98 INSERT /*+ parallel(OPI_EDW_OPM_JOB_DETAIL_INC) */
99 into OPI_EDW_OPM_JOB_DETAIL_INC(PRIMARY_KEY,view_id,seq_id)
100 SELECT
101 LINE_ID,
102 1,
103 NULL
104 FROM
105 PM_MATL_DTL
106 WHERE
107 LINE_TYPE=1 AND
108 BATCH_ID
109 in ( SELECT SUBSTRB(JOB_DETAIL_PK,instrB(JOB_DETAIL_PK,'-',1,1)+1,
110 instrB(JOB_DETAIL_PK,'-',1,2)-1-instrB(JOB_DETAIL_PK,'-',1,1))
111 FROM OPI_EDW_JOB_DETAIL_FSTG
112 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
113 AND JOB_DETAIL_PK like '%OPM');
114 edw_log.Put_line(to_char(sql%rowcount) ||' rows missing Currency Rate Conversion');
115 edw_log.put_line(' ');
116 Commit;
117 edw_log.put_line ('Printing Missing Rate Rows Output');
118 edw_log.put_line(' ');
119 PRINT_MISSING_ROWS;
120 edw_log.put_line ('Output Printed. You can view the output using ''View output'' option from Request page');
121 edw_log.put_line(' ');
122
123 /*Delete all missing rows from FSTG table if source and target are on same instance*/
124 IF (LOCAL_SAME_AS_REMOTE) THEN
125 DELETE OPI_EDW_JOB_DETAIL_FSTG
126 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
127 AND JOB_DETAIL_PK like '%OPM';
128 edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');
129 END IF;
130 /* Deletion completed */
131 ELSE
132 edw_log.Put_line('0 rows missing Currency Rate Conversion');
133 END IF;
134 EXCEPTION
135 WHEN OTHERS THEN
136 g_errbuf:=sqlerrm;
137 g_retcode:=sqlcode;
138 edw_log.put_line('Raised Exception from PUSH_MISSING_ROWS '||sqlerrm);
139 END;
140
141 -----------------------------------------------------------
142 --PROCEDURE PUSH_TO_LOCAL
143 -----------------------------------------------------------
144 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER, p_seq_id NUMBER) RETURN NUMBER IS
145 l_no_rows number;
146 BEGIN
147 -- ------------------------------------------------
148 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
149 -- In case of source=target, we need to separate
150 -- out the records in progress vs the records which
151 -- is ready to be picked up by collection enginee.
152 -- In our case, we consider the records to be in
153 -- progress until the push_to_local procedure for
154 -- all view types has completed successfully.
155 -- ------------------------------------------------
156 INSERT INTO OPI_EDW_JOB_DETAIL_FSTG(
157 ACT_BPR_VAL_B ,
158 ACT_BPR_VAL_G ,
159 ACT_CMPL_DATE ,
160 ACT_CNCL_DATE ,
161 ACT_INP_VAL_B ,
162 ACT_INP_VAL_G ,
163 ACT_JOB_TIME ,
164 ACT_MTL_INP_VAL_B ,
165 ACT_MTL_INP_VAL_G ,
166 ACT_OUT_QTY ,
167 ACT_OUT_VAL_B ,
168 ACT_OUT_VAL_G ,
169 ACT_SCR_VAL_B ,
170 ACT_SCR_VAL_G ,
171 ACT_STRT_DATE ,
172 BASE_UOM_FK,
173 COLLECTION_STATUS ,
174 CREATION_DATE ,
175 FST_PASS_YLD ,
176 INSTANCE_FK ,
177 ITEM_FK ,
178 JOB_DETAIL_PK ,
179 JOB_NO ,
180 JOB_STATUS ,
181 LAST_UPDATE_DATE ,
182 LOCATOR_FK ,
183 MFG_MODE ,
184 MOVE_TIME ,
185 NO_ADJ ,
186 NO_TIME_RESH ,
187 OPERATION_CODE ,
188 PLN_BPR_VAL_B ,
189 PLN_BPR_VAL_G ,
190 PLN_CMPL_DATE ,
191 PLN_INP_VAL_B ,
192 PLN_INP_VAL_G ,
193 PLN_JOB_TIME ,
194 PLN_MTL_INP_VAL_B ,
195 PLN_MTL_INP_VAL_G ,
196 PLN_OUT_QTY ,
197 PLN_OUT_VAL_B ,
198 PLN_OUT_VAL_G ,
199 PLN_SCR_VAL_B ,
200 PLN_SCR_VAL_G ,
201 PLN_STRT_DATE ,
202 PRD_LINE_FK ,
203 QC_FAIL_QTY ,
204 QC_TEST ,
205 QUEUE_TIME ,
206 RESH_REASON_CODE ,
207 RES_LOOKUP_FK ,
208 REWORK_QTY ,
209 ROUTING,
210 ROUTING_REVISION,
211 RUN_TIME ,
212 SETUP_TIME ,
213 SMPL_CNT ,
214 SOB_CURRENCY_FK ,
215 STD_QTY ,
216 STD_TIME ,
217 STD_VAL_B ,
218 STD_VAL_G ,
219 STND_HRS_EARNED,
220 STS_LOOKUP_FK ,
221 TRX_DATE_FK ,
222 USER_ATTRIBUTE1 ,
223 USER_ATTRIBUTE10 ,
224 USER_ATTRIBUTE11 ,
225 USER_ATTRIBUTE12 ,
226 USER_ATTRIBUTE13 ,
227 USER_ATTRIBUTE14 ,
228 USER_ATTRIBUTE15 ,
229 USER_ATTRIBUTE2 ,
230 USER_ATTRIBUTE3 ,
231 USER_ATTRIBUTE4 ,
232 USER_ATTRIBUTE5 ,
233 USER_ATTRIBUTE6 ,
234 USER_ATTRIBUTE7 ,
235 USER_ATTRIBUTE8 ,
236 USER_ATTRIBUTE9 ,
237 USER_FK1,
238 USER_FK2 ,
239 USER_FK3 ,
240 USER_FK4 ,
241 USER_FK5 ,
242 USER_MEASURE1 ,
243 USER_MEASURE2 ,
244 USER_MEASURE3 ,
245 USER_MEASURE4 ,
246 USER_MEASURE5
247 )
248 SELECT /*+ ALL_ROWS */
249 ACT_BPR_VAL_B ,
250 DECODE(ACT_BPR_VAL_G,-1,NULL,-2,NULL,ACT_BPR_VAL_G) ,
251 ACT_CMPL_DATE ,
252 ACT_CNCL_DATE ,
253 ACT_INP_VAL_B ,
254 DECODE(ACT_INP_VAL_G,-1,NULL,-2,NULL,ACT_INP_VAL_G) ,
255 ACT_JOB_TIME ,
256 ACT_MTL_INP_VAL_B ,
257 DECODE(ACT_MTL_INP_VAL_G,-1,NULL,-2,NULL,ACT_MTL_INP_VAL_G) ,
258 ACT_OUT_QTY ,
259 ACT_OUT_VAL_B ,
260 DECODE(ACT_OUT_VAL_G,-1,NULL,-2,NULL,ACT_OUT_VAL_G) ,
261 ACT_SCR_VAL_B ,
262 ACT_SCR_VAL_G ,
263 ACT_STRT_DATE ,
264 BASE_UOM_FK ,
265 DECODE(PLN_OUT_VAL_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY') ,
266 CREATION_DATE ,
267 FST_PASS_YLD ,
268 INSTANCE_FK ,
269 ITEM_FK ,
270 JOB_DETAIL_PK ,
271 JOB_NO ,
272 JOB_STATUS ,
273 LAST_UPDATE_DATE ,
274 LOCATOR_FK ,
275 MFG_MODE ,
276 MOVE_TIME ,
277 NO_ADJ ,
278 NO_TIME_RESH ,
279 NULL OPERATION_CODE ,
280 PLN_BPR_VAL_B ,
281 DECODE(PLN_BPR_VAL_G,-1,NULL,-2,NULL,PLN_BPR_VAL_G) ,
282 PLN_CMPL_DATE ,
283 PLN_INP_VAL_B ,
284 DECODE(PLN_INP_VAL_G,-1,NULL,-2,NULL,PLN_INP_VAL_G) ,
285 PLN_JOB_TIME ,
286 PLN_MTL_INP_VAL_B ,
287 DECODE(PLN_MTL_INP_VAL_G,-1,NULL,-2,NULL,PLN_MTL_INP_VAL_G) ,
288 PLN_OUT_QTY ,
289 PLN_OUT_VAL_B ,
290 DECODE(PLN_OUT_VAL_G,-1,NULL,-2,NULL,PLN_OUT_VAL_G) ,
291 PLN_SCR_VAL_B ,
292 PLN_SCR_VAL_G ,
293 PLN_STRT_DATE ,
294 PRD_LINE_FK ,
295 QC_FAIL_QTY ,
296 QC_TEST ,
297 QUEUE_TIME ,
298 RESH_REASON_CODE ,
299 RES_LOOKUP_FK ,
300 REWORK_QTY ,
301 ROUTING,
302 ROUTING_REVISION,
303 RUN_TIME ,
304 SETUP_TIME ,
305 SMPL_CNT ,
306 SOB_CURRENCY_FK ,
307 STD_QTY ,
308 STD_TIME ,
309 STD_VAL_B ,
310 DECODE(STD_VAL_G,-1,NULL,-2,NULL,STD_VAL_G) ,
311 STND_HRS_EARNED,
312 STS_LOOKUP_FK ,
313 TRX_DATE_FK ,
314 USER_ATTRIBUTE1 ,
315 USER_ATTRIBUTE10 ,
316 USER_ATTRIBUTE11 ,
317 USER_ATTRIBUTE12 ,
318 USER_ATTRIBUTE13 ,
319 USER_ATTRIBUTE14 ,
320 USER_ATTRIBUTE15 ,
321 USER_ATTRIBUTE2 ,
322 USER_ATTRIBUTE3 ,
323 USER_ATTRIBUTE4 ,
324 USER_ATTRIBUTE5 ,
325 USER_ATTRIBUTE6 ,
326 USER_ATTRIBUTE7 ,
327 USER_ATTRIBUTE8 ,
328 USER_ATTRIBUTE9 ,
329 USER_FK1,
330 USER_FK2 ,
331 USER_FK3 ,
332 USER_FK4 ,
333 USER_FK5 ,
334 USER_MEASURE1 ,
335 USER_MEASURE2 ,
336 USER_MEASURE3 ,
337 USER_MEASURE4 ,
338 USER_MEASURE5
339 FROM OPI_EDW_OPM_JOB_DETAIL_FCV
340 WHERE view_id = p_view_id
341 AND seq_id = p_seq_id;
342 commit;
343 l_no_rows := sql%rowcount;
344 edw_log.put_line('Sequence is '||to_char(p_seq_id));
345 edw_log.put_line('View ID is '||to_char(p_view_id));
346 /* Push Currency Conversion Missing Rows */
347 PUSH_MISSING_ROWS;
348 RETURN l_no_rows;
349 EXCEPTION
350 WHEN OTHERS THEN
351 g_errbuf:=sqlerrm;
352 g_retcode:=sqlcode;
353 RETURN(-1);
354 END;
355 -- ---------------------------------
356 -- PUBLIC PROCEDURES
357 -- ---------------------------------
358 FUNCTION IDENTIFY_OPM_CHANGE(p_view_id IN NUMBER,
359 p_count OUT NOCOPY NUMBER)
360 RETURN NUMBER
361 IS
362 l_seq_id NUMBER := -1;
363 l_opi_schema VARCHAR2(30);
364 l_status VARCHAR2(30);
365 l_industry VARCHAR2(30);
366 BEGIN
367 p_count := 0;
368 select OPI_EDW_JOB_DETAIL_INC_S.nextval into l_seq_id from dual;
369 /* insert into Incremental table all line_id but not part of missing currency convenrsion rows */
370 INSERT /*+ parallel(OPI_EDW_OPM_JOB_DETAIL_INC) */
371 into OPI_EDW_OPM_JOB_DETAIL_INC(PRIMARY_KEY,view_id,seq_id)
372 SELECT
373 LINE_ID,
374 1,
375 l_seq_id
376 FROM PM_BTCH_HDR BH,
377 PM_MATL_DTL BD
378 WHERE BH.BATCH_ID = BD.BATCH_ID
379 AND BH.BATCH_STATUS in (-1,0,1,2,3,4)
380 AND BD.LINE_TYPE=1
381 AND GREATEST(BH.LAST_UPDATE_DATE, BD.LAST_UPDATE_DATE)
382 BETWEEN g_push_from_date and g_push_to_date
383 AND LINE_ID not in
384 (SELECT PRIMARY_KEY
385 from OPI_EDW_OPM_JOB_DETAIL_INC
386 WHERE SEQ_ID is NULL);
387
388 p_count := sql%rowcount;
389
390 /* Update the Missing Currency convenrsion rows with new Sequence */
391 Update OPI_EDW_OPM_JOB_DETAIL_INC set view_id=1,seq_id=l_seq_id
392 WHERE seq_id is NULL;
393
394 p_count:=p_count+sql%rowcount;
395
396 Commit;
397
398 IF (FND_INSTALLATION.GET_APP_INFO('OPI', l_status, l_industry, l_OPI_schema)) THEN
399 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_OPI_schema,
400 TABNAME => 'OPI_EDW_OPM_JOB_DETAIL_INC');
401 END IF;
402
403 edw_log.put_line('Sequence is '||to_char(l_seq_id));
404 RETURN(l_seq_id);
405 EXCEPTION
406 WHEN OTHERS THEN
407 g_errbuf:=sqlerrm;
408 g_retcode:=sqlcode;
409 edw_log.put_line('Rasied Exception '||sqlerrm);
410
411 RETURN(-1);
412 END;
413
414
415 -----------------------------------------------------------
416 -- PROCEDURE PUSH
417 -----------------------------------------------------------
418 PROCEDURE PUSH(Errbuf in out NOCOPY Varchar2,
419 Retcode in out NOCOPY Varchar2,
420 p_from_date IN Varchar2,
421 p_to_date IN Varchar2) IS
422 l_fact_name Varchar2(30) :='OPI_EDW_JOB_DETAIL_F';
423 l_staging_table Varchar2(30) :='OPI_EDW_JOB_DETAIL_FSTG';
424 l_exception_msg Varchar2(2000):=Null;
425 l_from_date Date:=Null;
426 l_to_date Date:=Null;
427 l_seq_id1 NUMBER := -1;
428 l_row_count NUMBER := 0;
429 l_row_count1 NUMBER := 0;
430 l_row_count2 NUMBER := 0;
431 l_pmi_schema VARCHAR2(30);
432 l_status VARCHAR2(30);
433 l_industry VARCHAR2(30);
434 l_push_local_failure EXCEPTION;
435 l_iden_change_failure EXCEPTION;
436 -- -------------------------------------------
437 -- Put any additional developer variables here
438 -- -------------------------------------------
439 BEGIN
440 Errbuf :=NULL;
441 Retcode:=0;
442 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
443 l_to_date :=to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS');
444
445 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,l_staging_table,l_staging_table,l_exception_msg)) THEN
446 errbuf := fnd_message.get;
447 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
448 Return;
449 END IF;
450 -- --------------------------------------------
451 -- Taking care of cases where the input from/to
452 -- date is NULL.
453 -- --------------------------------------------
454 g_push_from_date := nvl(l_from_date,
455 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
456 EDW_COLLECTION_UTIL.g_offset);
457 g_push_to_date := nvl(l_to_date,
458 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
459 edw_log.put_line( 'The collection range is from '||
460 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
461 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
462 edw_log.put_line(' ');
463 -- --------------------------------------------
464 -- Identify OPM Net Changes
465 -- --------------------------------------------
466 edw_log.put_line(' ');
467 edw_log.put_line('Identifying changes in view type 1');
468 l_seq_id1 := IDENTIFY_OPM_CHANGE(1,l_row_count);
469 edw_log.put_line('Sequence is '||to_char(l_seq_id1));
470
471 if (l_seq_id1 = -1) THEN
472 RAISE l_iden_change_failure;
473 end if;
474 edw_log.put_line ('Identified '||l_row_count||' changed records in view type 1');
475 -- --------------------------------------------
476 -- Push to local staging table
477 -- --------------------------------------------
478 edw_log.put_line(' ');
479 edw_log.put_line('Inserting into local staging table ');
480 l_row_count1 := PUSH_TO_LOCAL(1,l_seq_id1);
481 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
482 edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
483 ' rows into the local staging table ');
484 edw_log.put_line(' ');
485 g_row_count:= l_row_count1;
486 edw_log.put_line(' ');
487 edw_log.put_line('For all views types, inserted '||nvl(g_row_count,0)||
488 ' rows into local staging table ');
489 -- --------------------------------------------
490 -- No exception raised so far. Call wrapup to transport
491 -- data to target database, and insert messages into logs
492 -- -----------------------------------------------
493 edw_log.put_line(' ');
494 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
495 ' rows into the staging table');
496 edw_log.put_line(' ');
497 commit;
498
499 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg,
500 g_push_from_date, g_push_to_date);
501 -- ---------------------------------------------------------------------------
502 -- END OF Collection , Developer Customizable Section
503 -- ---------------------------------------------------------------------------
504 EXCEPTION
505 WHEN L_PUSH_LOCAL_FAILURE THEN
506 Errbuf:=g_errbuf;
507 Retcode:=g_retcode;
508 l_exception_msg := Retcode || ':' || Errbuf;
509 rollback; -- Rollback insert into local staging
510 edw_log.put_line('Inserting into local staging have failed');
511 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
512 raise;
513 WHEN L_IDEN_CHANGE_FAILURE THEN
514 Errbuf:=g_errbuf;
515 Retcode:=g_retcode;
516 l_exception_msg := Retcode || ':' || Errbuf;
517 edw_log.put_line('Identifying changed records have Failed');
518 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
519 raise;
520 WHEN OTHERS THEN
521 Errbuf:=g_errbuf;
522 Retcode:=g_retcode;
523 l_exception_msg := Retcode || ':' || Errbuf;
524 rollback;
525 edw_log.put_line('Other errors');
526 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
527 g_push_from_date, g_push_to_date);
528 raise;
529 END;
530 END OPI_EDW_OPM_JOB_DETAIL_F_C ;