[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPM_JOB_RSRC_F_C
Source
1 Package Body OPI_EDW_OPM_JOB_RSRC_F_C AS
2 /* $Header: OPIMOJRB.pls 115.13 2004/01/02 19:06:05 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 /* Find if Source and target are on same instance */
13 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
14 IS
15 l_source Varchar2(100) :=Null;
16 l_Target Varchar2(100) :=Null;
17 BEGIN
18 SELECT instance_code INTO l_source
19 FROM edw_local_instance;
20
21 SELECT instance_code INTO l_target
22 FROM edw_local_instance@edw_apps_to_wh;
23
24 IF (l_source = l_target) THEN
25 RETURN TRUE;
26 END IF;
27 RETURN FALSE;
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 g_errbuf:=sqlerrm;
31 g_retcode:=sqlcode;
32 RETURN FALSE;
33 END LOCAL_SAME_AS_REMOTE;
34
35
36 /* Procedure to Print Missing Rows */
37 PROCEDURE PRINT_MISSING_ROWS
38 IS
39 /* Define Missing Rate Cursor for Job Detail */
40 Cursor Missing_Rate is
41 SELECT JOB_RSRC_PK,TRX_DATE_FK,SOB_CURRENCY_FK
42 FROM
43 OPI_EDW_JOB_RSRC_FSTG
44 WHERE
45 COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
46 AND JOB_RSRC_PK like '%OPM'
47 AND SUBSTRB(JOB_RSRC_PK,instrB(JOB_RSRC_PK,'-',-1,3)+1,
48 instrB(JOB_RSRC_PK,'-',-1,2)-1-instrB(JOB_RSRC_PK,'-',-1,3))
49 in (select PRIMARY_KEY from
50 OPI_EDW_OPM_JOB_RSRC_INC
51 WHERE SEQ_ID is NULL);
52
53 BEGIN
54 /* Print Header */
55 edw_log.put_line(' ');
56 edw_log.put_line ('Identified Missing Rows Date:'||SYSDATE);
57
58 edw_log.put_line (' ');
59 edw_log.put_line (' ');
60
61 edw_log.put_line ('Primary Key is PM_BTCH_HDR.Batch_id - PM_MATL_DTL.line_id -
62 PM_OPRN_DTL.BATCHSTEP_NO - PM_OPRN_DTL.RESOURCES - PM_OPRN_DTL.ACTIVITY
63 - PM_OPRN_DTL.BATCHSTEPLINE_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_RSRC_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_RSRC_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_RSRC_FSTG where
92 COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
93 AND JOB_RSRC_PK like '%OPM';
94 IF l_count > 0 THEN
95
96 /* insert into Incremental table all line_id where Currency is missing */
97 INSERT /*+ parallel(OPI_EDW_OPM_JOB_RSRC_INC) */
98 into OPI_EDW_OPM_JOB_RSRC_INC(PRIMARY_KEY,view_id,seq_id)
99 SELECT
100 BATCHSTEPLINE_ID,
101 1,
102 NULL
103 FROM
104 PM_OPRN_DTL
105 WHERE
106 BATCHSTEPLINE_ID
107 in ( SELECT SUBSTRB(JOB_RSRC_PK,instrB(JOB_RSRC_PK,'-',-1,3)+1,
108 instrB(JOB_RSRC_PK,'-',-1,2)-1-instrB(JOB_RSRC_PK,'-',-1,3))
109 from OPI_EDW_JOB_RSRC_FSTG
110 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
111 AND JOB_RSRC_PK like '%OPM');
112 /* The above Sub selcet has a reverse string traverse as the batchlineid is 3 from right in PK KEY */
113 edw_log.Put_line(to_char(sql%rowcount) ||' rows missing Currency Rate Conversion');
114 edw_log.put_line(' ');
115 Commit;
116 edw_log.put_line ('Printing Missing Rate Rows Output');
117 edw_log.put_line(' ');
118 PRINT_MISSING_ROWS;
119 edw_log.put_line ('Output Printed. You can view the output using ''View output'' option from Request page');
120 edw_log.put_line(' ');
121
122 /*Delete all missing rows from FSTG table if source and target are on same instance*/
123 IF (LOCAL_SAME_AS_REMOTE) THEN
124 DELETE OPI_EDW_JOB_RSRC_FSTG
125 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
126 AND JOB_RSRC_PK like '%OPM';
127 edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');
128 END IF;
129 /* Deletion completed */
130 ELSE
131 edw_log.Put_line('0 rows missing Currency Rate Conversion');
132 END IF;
133 EXCEPTION
134 WHEN OTHERS THEN
135 g_errbuf:=sqlerrm;
136 g_retcode:=sqlcode;
137 edw_log.put_line('Raised Exception '||sqlerrm);
138 END;
139
140 -----------------------------------------------------------
141 --PROCEDURE PUSH_TO_LOCAL
142 -----------------------------------------------------------
143 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER, p_seq_id NUMBER) RETURN NUMBER IS
144 l_no_rows number;
145 BEGIN
146 -- ------------------------------------------------
147 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
148 -- In case of source=target, we need to separate
149 -- out the records in progress vs the records which
150 -- is ready to be picked up by collection enginee.
151 -- In our case, we consider the records to be in
152 -- progress until the push_to_local procedure for
153 -- all view types has completed successfully.
154 -- ------------------------------------------------
155 INSERT INTO OPI_EDW_JOB_RSRC_FSTG(
156 ACTIVITY_FK,
157 ACT_CMPL_DATE ,
158 ACT_RSRC_COUNT,
159 ACT_RSRC_QTY ,
160 ACT_RSRC_USAGE ,
161 ACT_RSRC_USAGE_VAL_B ,
162 ACT_RSRC_USAGE_VAL_G ,
163 ACT_RSRC_VAL_B ,
164 ACT_RSRC_VAL_G ,
165 ACT_STRT_DATE ,
166 AVL_RSRC ,
167 COLLECTION_STATUS,
168 CREATION_DATE ,
169 DEPARTMENT,
170 EXTD_RSRC_COST ,
171 INSTANCE_FK,
172 ITEM_FK ,
173 JOB_NO,
174 JOB_RSRC_PK ,
175 LAST_UPDATE_DATE,
176 LOCATOR_FK ,
177 OPERATION_CODE ,
178 OPERATION_SEQ_NO ,
179 OPRN_FK ,
180 PLN_CMPL_DATE,
181 PLN_RSRC_COUNT ,
182 PLN_RSRC_QTY ,
183 PLN_RSRC_USAGE ,
184 PLN_RSRC_USAGE_VAL_B,
185 PLN_RSRC_USAGE_VAL_G,
186 PLN_RSRC_VAL_B ,
187 PLN_RSRC_VAL_G ,
188 PLN_STRT_DATE ,
189 QTY_UOM_FK ,
190 RSRC_FK ,
191 SOB_CURRENCY_FK ,
192 STND_RSRC_USAGE,
193 TRX_DATE_FK ,
194 USAGE_UOM_FK ,
195 USER_ATTRIBUTE1 ,
196 USER_ATTRIBUTE10 ,
197 USER_ATTRIBUTE11 ,
198 USER_ATTRIBUTE12 ,
199 USER_ATTRIBUTE13,
200 USER_ATTRIBUTE14 ,
201 USER_ATTRIBUTE15 ,
202 USER_ATTRIBUTE2 ,
203 USER_ATTRIBUTE3 ,
204 USER_ATTRIBUTE4 ,
205 USER_ATTRIBUTE5 ,
206 USER_ATTRIBUTE6 ,
207 USER_ATTRIBUTE7 ,
208 USER_ATTRIBUTE8 ,
209 USER_ATTRIBUTE9 ,
210 USER_FK1 ,
211 USER_FK2 ,
212 USER_FK3 ,
213 USER_FK4 ,
214 USER_FK5 ,
215 USER_MEASURE1 ,
216 USER_MEASURE2 ,
217 USER_MEASURE3 ,
218 USER_MEASURE4 ,
219 USER_MEASURE5
220 )
221 SELECT /*+ ALL_ROWS */
222 ACTIVITY_FK,
223 ACT_CMPL_DATE ,
224 ACT_RSRC_COUNT,
225 ACT_RSRC_QTY ,
226 ACT_RSRC_USAGE ,
227 ACT_RSRC_USAGE_VAL_B ,
228 ACT_RSRC_USAGE_VAL_G ,
229 ACT_RSRC_VAL_B ,
230 ACT_RSRC_VAL_G ,
231 ACT_STRT_DATE ,
232 AVL_RSRC ,
233 DECODE(PLN_RSRC_USAGE_VAL_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY'),
234 NULL ,
235 DEPARTMENT,
236 EXTD_RSRC_COST ,
237 INSTANCE_FK,
238 ITEM_FK ,
239 JOB_NO,
240 JOB_RSRC_PK ,
241 LAST_UPDATE_DATE,
242 LOCATOR_FK ,
243 NULL OPERATION_CODE ,
244 OPERATION_SEQ_NO ,
245 OPRN_FK ,
246 PLN_CMPL_DATE,
247 PLN_RSRC_COUNT ,
248 PLN_RSRC_QTY ,
249 PLN_RSRC_USAGE ,
250 PLN_RSRC_USAGE_VAL_B,
251 PLN_RSRC_USAGE_VAL_G ,
252 PLN_RSRC_VAL_B ,
253 PLN_RSRC_VAL_G ,
254 PLN_STRT_DATE ,
255 QTY_UOM_FK ,
256 RSRC_FK ,
257 SOB_CURRENCY_FK ,
258 STND_RSRC_USAGE,
259 TRX_DATE_FK ,
260 USAGE_UOM_FK ,
261 USER_ATTRIBUTE1 ,
262 USER_ATTRIBUTE10 ,
263 USER_ATTRIBUTE11 ,
264 USER_ATTRIBUTE12 ,
265 USER_ATTRIBUTE13,
266 USER_ATTRIBUTE14 ,
267 USER_ATTRIBUTE15 ,
268 USER_ATTRIBUTE2 ,
269 USER_ATTRIBUTE3 ,
270 USER_ATTRIBUTE4 ,
271 USER_ATTRIBUTE5 ,
272 USER_ATTRIBUTE6 ,
273 USER_ATTRIBUTE7 ,
274 USER_ATTRIBUTE8 ,
275 USER_ATTRIBUTE9 ,
276 USER_FK1 ,
277 USER_FK2 ,
278 USER_FK3 ,
279 USER_FK4 ,
280 USER_FK5 ,
281 USER_MEASURE1 ,
282 USER_MEASURE2 ,
283 USER_MEASURE3 ,
284 USER_MEASURE4 ,
285 USER_MEASURE5
286 FROM OPI_EDW_OPM_JOB_RSRC_FCV
287 WHERE view_id = p_view_id
288 AND seq_id = p_seq_id;
289 COMMIT;
290 l_no_rows := sql%rowcount;
291 /* Push Currency Conversion Missing Rows */
292 PUSH_MISSING_ROWS;
293 RETURN l_no_rows;
294 EXCEPTION
295 WHEN OTHERS THEN
296 g_errbuf:=sqlerrm;
297 g_retcode:=sqlcode;
298 RETURN(-1);
299 END;
300 -- ---------------------------------
301 -- PUBLIC PROCEDURES
302 -- ---------------------------------
303
304 FUNCTION IDENTIFY_OPM_CHANGE(p_view_id IN NUMBER,
305 p_count OUT NOCOPY NUMBER)
306 RETURN NUMBER
307 IS
308 l_seq_id NUMBER := -1;
309 l_opi_schema VARCHAR2(30);
310 l_status VARCHAR2(30);
311 l_industry VARCHAR2(30);
312 BEGIN
313 p_count := 0;
314 select OPI_EDW_JOB_RSRC_INC_S.nextval into l_seq_id from dual;
315 INSERT /*+ parallel(OPI_EDW_OPM_JOB_RSRC_INC) */
316 into OPI_EDW_OPM_JOB_RSRC_INC(PRIMARY_KEY,view_id,seq_id)
317 SELECT
318 BATCHSTEPLINE_ID,
319 1,
320 l_seq_id
321 FROM
322 PM_OPRN_DTL POD,
323 PM_BTCH_HDR PBH
324 WHERE POD.BATCH_ID=PBH.BATCH_ID
325 AND GREATEST(POD.LAST_UPDATE_DATE, PBH.LAST_UPDATE_DATE)
326 BETWEEN g_push_from_date and g_push_to_date
327 AND BATCHSTEPLINE_ID not in (
328 SELECT PRIMARY_KEY
329 from OPI_EDW_OPM_JOB_RSRC_INC
330 WHERE SEQ_ID is NULL);
331 p_count := sql%rowcount;
332
333 /* Update the Missing Currency convenrsion rows with new Sequence */
334 Update OPI_EDW_OPM_JOB_RSRC_INC set view_id=1,seq_id=l_seq_id
335 WHERE seq_id is NULL;
336
337 p_count:=p_count+sql%rowcount;
338
339 Commit;
340
341 IF (FND_INSTALLATION.GET_APP_INFO('OPI', l_status, l_industry, l_OPI_schema)) THEN
342 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_OPI_schema,
343 TABNAME => 'OPI_EDW_OPM_JOB_RSRC_INC');
344 END IF;
345
346 RETURN(l_seq_id);
347 EXCEPTION
348 WHEN OTHERS THEN
349 g_errbuf:=sqlerrm;
350 g_retcode:=sqlcode;
351 RETURN(-1);
352 END;
353
354
355
356 -----------------------------------------------------------
357 -- PROCEDURE PUSH
358 -----------------------------------------------------------
359 PROCEDURE PUSH(Errbuf in out NOCOPY Varchar2,
360 Retcode in out NOCOPY Varchar2,
361 p_from_date IN Varchar2,
362 p_to_date IN Varchar2) IS
363 l_fact_name Varchar2(30) :='OPI_EDW_JOB_RSRC_F';
364 l_staging_table Varchar2(30) :='OPI_EDW_JOB_RSRC_FSTG';
365 l_exception_msg Varchar2(2000):=Null;
366 l_from_date Date:=Null;
367 l_to_date Date:=Null;
368 l_seq_id NUMBER := -1;
369 l_seq_id2 NUMBER := -1;
370 l_row_count NUMBER := 0;
371 l_row_count1 NUMBER := 0;
372 l_row_count2 NUMBER := 0;
373 l_pmi_schema VARCHAR2(30);
374 l_status VARCHAR2(30);
375 l_industry VARCHAR2(30);
376 l_push_local_failure EXCEPTION;
377 l_iden_change_failure EXCEPTION;
378 -- -------------------------------------------
379 -- Put any additional developer variables here
380 -- -------------------------------------------
381 BEGIN
382 Errbuf :=NULL;
383 Retcode:=0;
384 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
385 l_to_date :=to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS');
386 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,l_staging_table,l_staging_table,l_exception_msg)) THEN
387 errbuf := fnd_message.get;
388 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
389 Return;
390 END IF;
391 -- --------------------------------------------
392 -- Taking care of cases where the input from/to
393 -- date is NULL.
394 -- --------------------------------------------
395 g_push_from_date := nvl(l_from_date,
396 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
397 EDW_COLLECTION_UTIL.g_offset);
398 g_push_to_date := nvl(l_to_date,
399 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
400 edw_log.put_line( 'The collection range is from '||
401 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
402 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
403 edw_log.put_line(' ');
404 l_seq_id := IDENTIFY_OPM_CHANGE(1,l_row_count);
405 if (l_seq_id = -1) THEN
406 RAISE l_iden_change_failure;
407 end if;
408
409 -- --------------------------------------------
410 -- Push to local staging table
411 -- --------------------------------------------
412 edw_log.put_line(' ');
413 edw_log.put_line('Inserting into local staging table ');
414 l_row_count1 := PUSH_TO_LOCAL(1,l_seq_id);
415 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
416 edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
417 ' rows into the local staging table ');
418 edw_log.put_line(' ');
419 g_row_count:= l_row_count1;
420 edw_log.put_line(' ');
421 edw_log.put_line('For all views types, inserted '||nvl(g_row_count,0)||
422 ' rows into local staging table ');
423 -- --------------------------------------------
424 -- No exception raised so far. Call wrapup to transport
425 -- data to target database, and insert messages into logs
426 -- -----------------------------------------------
427 edw_log.put_line(' ');
428 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
429 ' rows into the staging table');
430 edw_log.put_line(' ');
431 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg,
432 g_push_from_date, g_push_to_date);
433 -- ---------------------------------------------------------------------------
434 -- END OF Collection , Developer Customizable Section
435 -- ---------------------------------------------------------------------------
436 EXCEPTION
437 WHEN L_PUSH_LOCAL_FAILURE THEN
438 Errbuf:=g_errbuf;
439 Retcode:=g_retcode;
440 l_exception_msg := Retcode || ':' || Errbuf;
441 rollback; -- Rollback insert into local staging
442 edw_log.put_line('Inserting into local staging have failed');
443 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
444 raise;
445 WHEN L_IDEN_CHANGE_FAILURE THEN
446 Errbuf:=g_errbuf;
447 Retcode:=g_retcode;
448 l_exception_msg := Retcode || ':' || Errbuf;
452 WHEN OTHERS THEN
449 edw_log.put_line('Identifying changed records have Failed');
450 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
451 raise;
453 Errbuf:=g_errbuf;
454 Retcode:=g_retcode;
455 l_exception_msg := Retcode || ':' || Errbuf;
456 rollback;
457 edw_log.put_line('Other errors');
458 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
459 g_push_from_date, g_push_to_date);
460 raise;
461 END;
462 END OPI_EDW_OPM_JOB_RSRC_F_C ;