[Home] [Help]
PACKAGE BODY: APPS.ISC_EDW_BACKLOGS_F_C
Source
1 PACKAGE BODY ISC_EDW_BACKLOGS_F_C AS
2 /* $Header: ISCSCF1B.pls 115.17 2004/03/30 14:37:20 adwajan ship $ */
3
4 g_row_count NUMBER := 0;
5 g_rows_collected NUMBER := 0;
6 g_miss_conv NUMBER := 0;
7 g_seq_id_line NUMBER := -1;
8
9 g_push_from_date DATE := NULL;
10 g_push_to_date DATE := NULL;
11
12 g_errbuf VARCHAR2(2000) := NULL;
13 g_all_or_nothing_flag VARCHAR2(5) := 'Y';
14
15
16 -- -------------
17 -- PUSH_TO_LOCAL
18 -- -------------
19
20 FUNCTION Push_To_Local(p_seq_id IN NUMBER) RETURN NUMBER IS
21
22 BEGIN
23
24 INSERT INTO ISC_EDW_BACKLOGS_FSTG(
25 BACKLOGS_PK,
26 BASE_UOM_FK,
27 BILL_BKLG_COST_G,
28 BILL_BKLG_COST_T,
29 BILL_BKLG_MRG_G,
30 BILL_BKLG_MRG_T,
31 BILL_BKLG_REV_G,
32 BILL_BKLG_REV_T,
33 BILL_TO_CUST_FK,
34 BILL_TO_LOCATION_FK,
35 CUSTOMER_FK,
36 DATE_BALANCE_FK,
37 DEMAND_CLASS_FK,
38 DLQT_BKLG_COST_G,
39 DLQT_BKLG_COST_T,
40 DLQT_BKLG_MRG_G,
41 DLQT_BKLG_MRG_T,
42 DLQT_BKLG_REV_G,
43 DLQT_BKLG_REV_T,
44 GL_BOOK_FK,
45 INSTANCE,
46 INSTANCE_FK,
47 INV_ORG_FK,
48 ITEM_ORG_FK,
49 LAST_UPDATE_DATE,
50 OPERATING_UNIT_FK,
51 ORDER_CATEGORY_FK,
52 ORDER_SOURCE_FK,
53 ORDER_TYPE_FK,
54 QTY_BILL_BKLG_B,
55 QTY_DLQT_BKLG_B,
56 QTY_SHIP_BKLG_B,
57 QTY_UNBILL_SHIP_B,
58 SALES_CHANNEL_FK,
59 SALES_PERSON_FK,
60 SHIP_BKLG_COST_G,
61 SHIP_BKLG_COST_T,
62 SHIP_BKLG_MRG_G,
63 SHIP_BKLG_MRG_T,
64 SHIP_BKLG_REV_G,
65 SHIP_BKLG_REV_T,
66 SHIP_TO_CUST_FK,
67 SHIP_TO_LOCATION_FK,
68 TASK_FK,
69 TOP_MODEL_ITEM_FK,
70 TRX_CURRENCY_FK,
71 UNBILL_SHIP_COST_G,
72 UNBILL_SHIP_COST_T,
73 UNBILL_SHIP_MRG_G,
74 UNBILL_SHIP_MRG_T,
75 UNBILL_SHIP_REV_G,
76 UNBILL_SHIP_REV_T,
77 USER_ATTRIBUTE1,
78 USER_ATTRIBUTE2,
79 USER_ATTRIBUTE3,
80 USER_ATTRIBUTE4,
81 USER_ATTRIBUTE5,
82 USER_ATTRIBUTE6,
83 USER_ATTRIBUTE7,
84 USER_ATTRIBUTE8,
85 USER_ATTRIBUTE9,
86 USER_ATTRIBUTE10,
87 USER_ATTRIBUTE11,
88 USER_ATTRIBUTE12,
89 USER_ATTRIBUTE13,
90 USER_ATTRIBUTE14,
91 USER_ATTRIBUTE15,
92 USER_ATTRIBUTE16,
93 USER_ATTRIBUTE17,
94 USER_ATTRIBUTE18,
95 USER_ATTRIBUTE19,
96 USER_ATTRIBUTE20,
97 USER_ATTRIBUTE21,
98 USER_ATTRIBUTE22,
99 USER_ATTRIBUTE23,
100 USER_ATTRIBUTE24,
101 USER_ATTRIBUTE25,
102 USER_FK1,
103 USER_FK2,
104 USER_FK3,
105 USER_FK4,
106 USER_FK5,
107 USER_MEASURE1,
108 USER_MEASURE2,
109 USER_MEASURE3,
110 USER_MEASURE4,
111 USER_MEASURE5,
112 OPERATION_CODE,
113 COLLECTION_STATUS)
114 SELECT BACKLOGS_PK,
115 BASE_UOM_FK,
116 BILL_BKLG_COST_G,
117 BILL_BKLG_COST_T,
118 BILL_BKLG_MRG_G,
119 BILL_BKLG_MRG_T,
120 BILL_BKLG_REV_G,
121 BILL_BKLG_REV_T,
122 BILL_TO_CUST_FK,
123 BILL_TO_LOCATION_FK,
124 CUSTOMER_FK,
125 DATE_BALANCE_FK,
126 DEMAND_CLASS_FK,
127 DLQT_BKLG_COST_G,
128 DLQT_BKLG_COST_T,
129 DLQT_BKLG_MRG_G,
130 DLQT_BKLG_MRG_T,
131 DLQT_BKLG_REV_G,
132 DLQT_BKLG_REV_T,
133 GL_BOOK_FK,
134 INSTANCE,
135 INSTANCE_FK,
136 INV_ORG_FK,
137 ITEM_ORG_FK,
138 LAST_UPDATE_DATE,
139 OPERATING_UNIT_FK,
140 ORDER_CATEGORY_FK,
141 ORDER_SOURCE_FK,
142 ORDER_TYPE_FK,
143 QTY_BILL_BKLG_B,
144 QTY_DLQT_BKLG_B,
145 QTY_SHIP_BKLG_B,
146 QTY_UNBILL_SHIP_B,
147 SALES_CHANNEL_FK,
148 SALES_PERSON_FK,
149 SHIP_BKLG_COST_G,
150 SHIP_BKLG_COST_T,
151 SHIP_BKLG_MRG_G,
152 SHIP_BKLG_MRG_T,
153 SHIP_BKLG_REV_G,
154 SHIP_BKLG_REV_T,
155 SHIP_TO_CUST_FK,
156 SHIP_TO_LOCATION_FK,
157 TASK_FK,
158 TOP_MODEL_ITEM_FK,
159 TRX_CURRENCY_FK,
160 UNBILL_SHIP_COST_G,
161 UNBILL_SHIP_COST_T,
162 UNBILL_SHIP_MRG_G,
163 UNBILL_SHIP_MRG_T,
164 UNBILL_SHIP_REV_G,
165 UNBILL_SHIP_REV_T,
166 USER_ATTRIBUTE1,
167 USER_ATTRIBUTE2,
168 USER_ATTRIBUTE3,
169 USER_ATTRIBUTE4,
170 USER_ATTRIBUTE5,
171 USER_ATTRIBUTE6,
172 USER_ATTRIBUTE7,
173 USER_ATTRIBUTE8,
174 USER_ATTRIBUTE9,
175 USER_ATTRIBUTE10,
176 USER_ATTRIBUTE11,
177 USER_ATTRIBUTE12,
178 USER_ATTRIBUTE13,
179 USER_ATTRIBUTE14,
180 USER_ATTRIBUTE15,
181 USER_ATTRIBUTE16,
182 USER_ATTRIBUTE17,
183 USER_ATTRIBUTE18,
184 USER_ATTRIBUTE19,
185 USER_ATTRIBUTE20,
186 USER_ATTRIBUTE21,
187 USER_ATTRIBUTE22,
188 USER_ATTRIBUTE23,
189 USER_ATTRIBUTE24,
190 USER_ATTRIBUTE25,
191 nvl(USER_FK1,'NA_EDW'),
192 nvl(USER_FK2,'NA_EDW'),
193 nvl(USER_FK3,'NA_EDW'),
194 nvl(USER_FK4,'NA_EDW'),
195 nvl(USER_FK5,'NA_EDW'),
196 USER_MEASURE1,
197 USER_MEASURE2,
198 USER_MEASURE3,
199 USER_MEASURE4,
200 USER_MEASURE5,
201 NULL, -- OPERATION_CODE
202 'LOCAL READY'
203 FROM ISC_EDW_BACKLOGS_F_FCV
204 WHERE seq_id = p_seq_id;
205
206 RETURN(sql%rowcount);
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 g_errbuf := 'Error in Function Push_To_Local : '||sqlerrm;
211 RETURN(-1);
212 END;
213
214
215 -- ---------------
216 -- IDENTIFY_CHANGE
217 -- ---------------
218
219 FUNCTION Identify_Change( p_seq_id IN NUMBER,
220 p_count OUT NOCOPY NUMBER) RETURN NUMBER IS
221
222 l_seq_id NUMBER := -1;
223
224 BEGIN
225
226 p_count := 0;
227 SELECT isc_tmp_back_s.nextval
228 INTO l_seq_id
229 FROM dual;
230
231 -- --------------------------------------------
232 -- Populate rowid into isc_tmp_back table based
233 -- on last update date
234 -- --------------------------------------------
235
236 INSERT INTO isc_tmp_back(
237 SEQ_ID,
238 PK1)
239 SELECT /*+ PARALLEL(h) */
240 l_seq_id,
241 to_char(l.line_id)
242 FROM oe_order_headers_all h,
243 oe_order_lines_all l
244 WHERE h.last_update_date BETWEEN g_push_from_date AND g_push_to_date
245 AND l.header_id = h.header_id
246 AND nvl(l.ordered_quantity,0) > 0
247 AND nvl(l.source_document_type_id,0) <> 10
248 AND l.line_category_code = ('ORDER')
249 UNION
250 SELECT /*+ PARALLEL(l) */
251 l_seq_id,
252 to_char(l.line_id)
253 FROM oe_order_lines_all l
254 WHERE l.last_update_date BETWEEN g_push_from_date AND g_push_to_date
255 AND nvl(l.ordered_quantity,0) > 0
256 AND nvl(l.source_document_type_id,0) <> 10
257 AND l.line_category_code = ('ORDER');
258
259 p_count := sql%rowcount;
260
261 UPDATE ISC_TMP_BACK
262 SET seq_id = l_seq_id
263 WHERE seq_id = -10
264 AND pk1 NOT IN ( SELECT pk1
265 FROM ISC_TMP_BACK
266 WHERE seq_id = l_seq_id) ;
267
268 p_count := p_count + sql%rowcount;
269
270 DELETE ISC_TMP_BACK
271 WHERE seq_id = -10;
272
273 RETURN(l_seq_id);
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 g_errbuf := 'Error in Function Identify_Change : '||sqlerrm;
278 RETURN(-1);
279
280 END;
281
282
283 -- ---------------------
284 -- IDENTIFY_MISSING_RATE
285 -- ---------------------
286
287 FUNCTION Identify_Missing_Rate( p_count OUT NOCOPY NUMBER) RETURN NUMBER IS
288
289 BEGIN
290
291 p_count := 0;
292
293 INSERT INTO ISC_EDW_BACK_MISSING_RATE(
294 ID,
295 PK1,
296 PK2,
297 CURR_CONV_DATE,
298 FROM_CURRENCY,
299 TO_CURRENCY,
300 RATE_TYPE,
301 FROM_UOM_CODE,
302 TO_UOM_CODE,
303 INVENTORY_ITEM_ID,
304 ITEM_NAME,
305 STATUS)
306 SELECT /* Reports Transaction to Base Conversion Currency Issue*/
307 g_seq_id_line ID,
308 ftp.pk1 PK1,
309 '' PK2,
310 decode( upper(h.conversion_type_code), 'USER',
311 h.conversion_rate_date,
312 h.booked_date) CURR_CONV_DATE,
313 h.transactional_curr_code FROM_CURRENCY,
314 gl.currency_code TO_CURRENCY,
315 nvl(h.conversion_type_code,
316 edw_param.rate_type) RATE_TYPE,
317 '' FROM_UOM_CODE,
318 '' TO_UOM_CODE,
319 '' INVENTORY_ITEM_ID,
320 '' ITEM_NAME,
321 decode( decode( upper(h.conversion_type_code), 'USER',
322 h.conversion_rate,
323 decode( h.transactional_curr_code, gl.currency_code,
324 1,
325 GL_CURRENCY_API.get_rate_sql(
326 h.transactional_curr_code,
327 gl.currency_code,
328 h.booked_date,
329 nvl(h.conversion_type_code, edw_param.rate_type)))),
330 -1,'RATE NOT AVAILABLE',
331 -2,'INVALID CURRENCY') STATUS
332 FROM EDW_LOCAL_INSTANCE inst,
333 EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
334 ISC_TMP_BACK ftp,
335 OE_ORDER_LINES_ALL l,
336 OE_ORDER_HEADERS_ALL h,
337 FINANCIALS_SYSTEM_PARAMS_ALL fspa,
338 GL_SETS_OF_BOOKS gl
339 WHERE ftp.pk1 = l.line_id
340 AND ftp.seq_id = g_seq_id_line
341 AND l.org_id = fspa.org_id
342 AND l.header_id = h.header_id
343 AND fspa.set_of_books_id = gl.set_of_books_id
344 AND h.booked_flag = 'Y'
345 AND h.booked_date IS NOT NULL
346 AND decode( upper(h.conversion_type_code), 'USER',
347 h.conversion_rate,
348 decode( h.transactional_curr_code, gl.currency_code,
349 1,
350 GL_CURRENCY_API.get_rate_sql(
351 h.transactional_curr_code,
352 gl.currency_code,
353 h.booked_date,
354 nvl(h.conversion_type_code, edw_param.rate_type)))) < 0
355 UNION
356 SELECT /* Reports Base to Global Conversion Currency Issue */
357 g_seq_id_line ID,
358 ftp.pk1 PK1,
359 '' PK2,
360 decode( upper(h.conversion_type_code), 'USER',
361 h.conversion_rate_date,
362 h.booked_date) CURR_CONV_DATE,
363 gl.currency_code FROM_CURRENCY,
364 edw_param.warehouse_currency_code TO_CURRENCY,
365 nvl(h.conversion_type_code,
366 edw_param.rate_type) RATE_TYPE,
367 '' FROM_UOM_CODE,
368 '' TO_UOM_CODE,
369 '' INVENTORY_ITEM_ID,
370 '' ITEM_NAME,
371 decode( EDW_CURRENCY.Get_Rate (gl.currency_code,h.booked_date),
372 -1,'RATE NOT AVAILABLE',
373 -2,'INVALID CURRENCY') STATUS
374 FROM EDW_LOCAL_INSTANCE inst,
375 EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
376 ISC_TMP_BACK ftp,
377 OE_ORDER_LINES_ALL l,
378 OE_ORDER_HEADERS_ALL h,
379 FINANCIALS_SYSTEM_PARAMS_ALL fspa,
380 GL_SETS_OF_BOOKS gl
381 WHERE ftp.pk1 = l.line_id
382 AND ftp.seq_id = g_seq_id_line
383 AND l.org_id = fspa.org_id
384 AND l.header_id = h.header_id
385 AND fspa.set_of_books_id = gl.set_of_books_id
386 AND h.booked_flag = 'Y'
387 AND h.booked_date IS NOT NULL
388 AND EDW_CURRENCY.Get_Rate (gl.currency_code,h.booked_date) < 0
389 UNION
390 SELECT /* Reports Base to Transaction Conversion Currency Issue */
391 g_seq_id_line ID,
392 ftp.pk1 PK1,
393 '' PK2,
394 decode( upper(h.conversion_type_code), 'USER',
395 h.conversion_rate_date,
396 h.booked_date) CURR_CONV_DATE,
397 gl.currency_code FROM_CURRENCY,
398 h.transactional_curr_code TO_CURRENCY,
399 nvl(h.conversion_type_code,
400 edw_param.rate_type) RATE_TYPE,
401 '' FROM_UOM_CODE,
402 '' TO_UOM_CODE,
403 '' INVENTORY_ITEM_ID,
404 '' ITEM_NAME,
405 decode( decode( upper(h.conversion_type_code),'USER',
406 1/ h.conversion_rate,
407 decode( h.transactional_curr_code, gl.currency_code,
408 1,
409 GL_CURRENCY_API.get_rate_sql (
410 gl.currency_code,
411 h.transactional_curr_code,
412 h.booked_date,
413 nvl(h.conversion_type_code, edw_param.rate_type)))),
414 -1,'RATE NOT AVAILABLE',
415 -2,'INVALID CURRENCY') STATUS
416 FROM EDW_LOCAL_INSTANCE inst,
417 EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
418 ISC_TMP_BACK ftp,
419 OE_ORDER_LINES_ALL l,
420 OE_ORDER_HEADERS_ALL h,
421 FINANCIALS_SYSTEM_PARAMS_ALL fspa,
422 GL_SETS_OF_BOOKS gl
423 WHERE ftp.pk1 = l.line_id
424 AND ftp.seq_id = g_seq_id_line
425 AND l.org_id = fspa.org_id
426 AND l.header_id = h.header_id
427 AND fspa.set_of_books_id = gl.set_of_books_id
428 AND h.booked_flag = 'Y'
429 AND h.booked_date IS NOT NULL
430 AND decode( upper(h.conversion_type_code),'USER',
431 1/ h.conversion_rate,
432 decode( h.transactional_curr_code, gl.currency_code,
433 1,
434 GL_CURRENCY_API.get_rate_sql (
435 gl.currency_code,
436 h.transactional_curr_code,
437 h.booked_date,
438 nvl(h.conversion_type_code, edw_param.rate_type)))) < 0
439 UNION
440 SELECT -- Reports "Ship from Org Base" to "Header Org Base" Conversion Currency Issue
441 g_seq_id_line ID,
442 ftp.pk1 PK1,
443 '' PK2,
444 h.booked_date CURR_CONV_DATE,
445 gl_cost.currency_code FROM_CURRENCY,
446 gl.currency_code TO_CURRENCY,
447 edw_param.rate_type RATE_TYPE,
448 '' FROM_UOM_CODE,
449 '' TO_UOM_CODE,
450 '' INVENTORY_ITEM_ID,
451 '' ITEM_NAME,
452 decode(GL_CURRENCY_API.get_rate_sql (
453 gl_cost.currency_code,
454 gl.currency_code,
455 h.booked_date,
456 edw_param.rate_type),
457 -1,'RATE NOT AVAILABLE',
458 -2,'INVALID CURRENCY') STATUS
459 FROM EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
460 ISC_TMP_PK ftp,
461 OE_ORDER_LINES_ALL l,
462 OE_ORDER_HEADERS_ALL h,
463 FINANCIALS_SYSTEM_PARAMS_ALL fspa,
464 GL_SETS_OF_BOOKS gl,
465 GL_SETS_OF_BOOKS gl_cost,
466 HR_ORGANIZATION_INFORMATION hoi
467 WHERE ftp.pk1 = l.line_id
468 AND ftp.seq_id = g_seq_id_line
469 AND l.org_id = fspa.org_id
470 AND l.header_id = h.header_id
471 AND fspa.set_of_books_id = gl.set_of_books_id
472 AND h.booked_flag = 'Y'
473 AND h.booked_date IS NOT NULL
474 AND l.ship_from_org_id = hoi.organization_id -- if ship_from_org_id is null, don't include row in the missing rates
475 AND hoi.org_information_context = 'Accounting Information'
476 AND hoi.org_information1 = to_char(gl_cost.set_of_books_id)
477 AND GL_CURRENCY_API.get_rate_sql (
478 gl_cost.currency_code,
479 gl.currency_code,
480 h.booked_date,
481 edw_param.rate_type) < 0;
482
483 p_count := sql%rowcount;
484
485 INSERT INTO ISC_EDW_BACK_MISSING_RATE(
486 ID,
487 PK1,
488 PK2,
489 CURR_CONV_DATE,
490 FROM_CURRENCY,
491 TO_CURRENCY,
492 RATE_TYPE,
493 FROM_UOM_CODE,
494 TO_UOM_CODE,
495 INVENTORY_ITEM_ID,
496 ITEM_NAME,
497 STATUS)
498 SELECT /* Reports UOM Conversion Issue */
499 g_seq_id_line ID,
500 ftp.pk1 PK1,
501 '' PK2,
502 to_date(NULL) CURR_CONV_DATE,
503 '' FROM_CURRENCY,
504 '' TO_CURRENCY,
505 '' RATE_TYPE,
506 l.order_quantity_uom FROM_UOM_CODE,
507 EDW_UTIL.Get_Edw_Base_Uom(
508 l.order_quantity_uom,
509 l.inventory_item_id) TO_UOM_CODE,
510 l.inventory_item_id INVENTORY_ITEM_ID,
511 nvl(mtl.segment1,'Item number unavailable')
512 ||' : '||nvl(description,'Description unavailable')
513 ITEM_NAME,
514 'UOM ISSUE' STATUS
515 FROM EDW_LOCAL_INSTANCE inst,
516 ISC_TMP_BACK ftp,
517 OE_ORDER_LINES_ALL l,
518 OE_ORDER_HEADERS_ALL h,
519 MTL_SYSTEM_ITEMS_B mtl
520 WHERE ftp.pk1 = l.line_id
521 AND ftp.seq_id = g_seq_id_line
522 AND l.header_id = h.header_id
523 AND h.booked_flag = 'Y'
524 AND h.booked_date IS NOT NULL
525 AND l.ship_from_org_id = mtl.organization_id
526 AND l.inventory_item_id = mtl.inventory_item_id
527 AND EDW_UTIL.Get_Uom_Conv_Rate(l.order_quantity_uom,l.inventory_item_id) IS NULL;
528
529 p_count := p_count + sql%rowcount;
530
531 RETURN(p_count);
532
533 EXCEPTION
534 WHEN OTHERS THEN
535 g_errbuf := 'Error in Function Identify_Missing_Rate : '||sqlerrm;
536 RETURN(-1);
537 END;
538
539
540 -- -------------------
541 -- INSERT_ISC_TMP_BACK
542 -- -------------------
543
544 FUNCTION Insert_Isc_Tmp_Back RETURN NUMBER IS
545
546 BEGIN
547
548 INSERT INTO ISC_TMP_BACK (
549 seq_id,
550 pk1)
551 SELECT -10 , ftp.pk1
552 FROM ISC_EDW_BACK_MISSING_RATE conv,
553 ISC_TMP_BACK ftp
554 WHERE conv.pk1 = ftp.pk1
555 AND ftp.seq_id = g_seq_id_line
556 AND conv.id = g_seq_id_line ;
557
558 RETURN(sql%rowcount);
559
560 EXCEPTION
561 WHEN OTHERS THEN
562 g_errbuf := 'Error in Function Insert_Tmp_Back : '||sqlerrm;
563 RETURN(-1);
564 END;
565
566
567 -- -------------------
568 -- DELETE_ISC_TMP_BACK
569 -- -------------------
570
571 FUNCTION Delete_Isc_Tmp_Back RETURN NUMBER IS
572
573 BEGIN
574
575 DELETE FROM ISC_TMP_BACK
576 WHERE pk1 IN ( SELECT pk1
577 FROM ISC_EDW_BACK_MISSING_RATE
578 WHERE id = g_seq_id_line )
579 AND seq_id >0 ;
580
581 RETURN(sql%rowcount);
582
583 EXCEPTION
584 WHEN OTHERS THEN
585 g_errbuf := 'Error in Function Delete_Isc_Tmp_Back : '||sqlerrm;
586 RETURN(-1);
587 END;
588
589
590 -- -----------------
591 -- PUBLIC PROCEDURES
592 -- -----------------
593
594 -- --------------
595 -- PROCEDURE PUSH
596 -- --------------
597
598 Procedure Push( Errbuf IN out NOCOPY VARCHAR2,
599 Retcode IN out NOCOPY VARCHAR2,
600 p_from_date IN VARCHAR2,
601 p_to_date IN VARCHAR2,
602 p_coll_flag IN VARCHAR2) IS -- 'Yes' = All or Nothing , 'No' = Collect >0 rows only
603
604 l_fact_name VARCHAR2(30) := 'ISC_EDW_BACKLOGS_F' ;
605
606 l_from_date DATE := NULL;
607 l_to_date DATE := NULL;
608
609 l_row_count NUMBER := 0;
610
611 l_failure EXCEPTION;
612
613 CURSOR Missing_Currency_Conversion IS
614 SELECT DISTINCT trunc(curr_conv_date) curr_conv_date,
615 from_currency,
616 to_currency,
617 rate_type,
618 status
619 FROM ISC_EDW_BACK_MISSING_RATE
620 WHERE status NOT IN ('UOM ISSUE')
621 ORDER BY status,from_currency,trunc(curr_conv_date);
622
623 CURSOR Missing_UOM_Conversion IS
624 SELECT DISTINCT from_uom_code,
625 to_uom_code,
626 inventory_item_id item_id,
627 substr(item_name,0,50) item_name
628 FROM ISC_EDW_BACK_MISSING_RATE
629 WHERE status = 'UOM ISSUE'
630 ORDER BY item_name,from_uom_code;
631
632
633 -- -------------------------------------------
634 -- Put any additional developer variables here
635 -- -------------------------------------------
636
637 BEGIN
638
639 errbuf := NULL;
640 retcode := '0';
641
642 l_from_date := to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
643 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
644
645 g_all_or_nothing_flag := upper(p_coll_flag);
646
647 IF (Not EDW_COLLECTION_UTIL.Setup(l_fact_name))
648 THEN
649 g_errbuf := 'Error in function Setup : '||fnd_message.get;
650 RAISE l_failure;
651 END IF;
652
653 ISC_EDW_BACKLOGS_F_C.G_Push_From_Date := nvl(l_from_date,
654 EDW_COLLECTION_UTIL.G_Local_Last_Push_Start_Date - EDW_COLLECTION_UTIL.G_Offset);
655 ISC_EDW_BACKLOGS_F_C.G_Push_To_Date := nvl(l_to_date,EDW_COLLECTION_UTIL.G_Local_Curr_Push_Start_Date);
656
657 EDW_LOG.Put_Line( 'The collection range is from '||
658 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
659 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
660 EDW_LOG.Put_Line(' ');
661
662 -- ---------------
663 -- Identify Change
664 -- ---------------
665
666 EDW_LOG.Put_Line('Identifying changed Backlog lines');
667
668 FII_UTIL.Start_Timer;
669
670 g_seq_id_line := IDENTIFY_CHANGE(-1,l_row_count);
671
672 FII_UTIL.Stop_Timer;
673
674 IF (g_seq_id_line = -1)
675 THEN RAISE l_failure;
676 END IF;
677
678 FII_UTIL.Print_Timer('Identified '||l_row_count||' changed records in');
679
680
681 -- ----------------------------------------------------------
682 -- Identify Missing Rate into ISC_EDW_BACK_MISSING_RATE table
683 -- ----------------------------------------------------------
684
685 EDW_LOG.Put_Line(' ');
686 EDW_LOG.Put_Line('Identifying the missing conversion rates (currency and UoM)');
687
688 FII_UTIL.Start_Timer;
689
690 g_miss_conv := Identify_Missing_Rate(l_row_count);
691
692 FII_UTIL.Stop_Timer;
693
694 IF (g_miss_conv = -1)
695 THEN RAISE l_failure;
696 END IF;
697
698 FII_UTIL.Print_Timer('Inserted '||g_miss_conv||' rows into the ISC_EDW_BACK_MISSING_RATE table in ');
699 EDW_LOG.Put_Line(' ');
700
701
702 -- ----------------------------------------------------------------------
703 -- Inserting into ISC_TMP_BACK rows having missing rate (with seq_id < 0)
704 -- ----------------------------------------------------------------------
705
706 EDW_LOG.Put_Line(' ');
707 EDW_LOG.Put_Line('Inserting into ISC_TMP_BACK with < 0 seq_id the rows having missing conversion rates (currency and UoM)');
708
709 FII_UTIL.Start_Timer;
710
711 g_row_count := Insert_Isc_Tmp_Back;
712
713 FII_UTIL.Stop_Timer;
714
715 IF (g_row_count = -1)
716 THEN RAISE l_failure;
717 END IF;
718
719 FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows into the ISC_TMP_BACK table in ');
720 EDW_LOG.Put_Line(' ');
721
722
723 -- ----------------------------------------------
724 -- Deleting ISC_TMP_BACK rows having missing rate
725 -- ----------------------------------------------
726
727 EDW_LOG.Put_Line(' ');
728 EDW_LOG.Put_Line('Deleting the ISC_TMP_BACK rows having a missing conversion rates before collecting (currency and UoM)');
729
730 FII_UTIL.Start_Timer;
731
732 g_row_count := Delete_Isc_Tmp_Back ;
733
734 FII_UTIL.Stop_Timer;
735
736 IF (g_row_count = -1)
737 THEN RAISE l_failure;
738 END IF;
739
740 FII_UTIL.Print_Timer('Deleted '||g_row_count||' rows from the ISC_TMP_BACK table in ');
741 EDW_LOG.Put_Line(' ');
742
743
744 IF NOT ((g_all_or_nothing_flag = 'Y') and (g_miss_conv > 0)) -- collect except when this condition applies
745 THEN
746 BEGIN -- IF NOT ((g_all_or_nothing_flag = 'Y') and (g_miss_conv > 0))
747
748 -- ---------------------------
749 -- Push to Local staging table
750 -- ---------------------------
751
752 EDW_LOG.Put_Line(' ');
753 EDW_LOG.Put_Line('Pushing data to local staging');
754
755 EDW_LOG.Put_Line( 'The collection range is from '||
756 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
757 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
758
759 FII_UTIL.Start_Timer;
760
761 g_rows_collected := Push_To_Local(g_seq_id_line);
762
763 FII_UTIL.Stop_Timer;
764
765 IF (g_rows_collected = -1)
766 THEN RAISE l_failure;
767 END IF;
768
769 FII_UTIL.Print_Timer('Inserted '||g_rows_collected||' rows into the local staging table in ');
770 EDW_LOG.Put_Line(' ');
771
772 END;
773 END IF; -- IF NOT ((g_all_or_nothing_flag = 'Y') and (g_miss_conv > 0))
774
775
776 -- -------------------
777 -- Delete ISC_TMP_BACK
778 -- -------------------
779
780 DELETE FROM isc_tmp_back
781 WHERE seq_id = g_seq_id_line;
782
783
784 -- -------------------------------------------
785 -- Reporting of the missing currencies and UoM
786 -- -------------------------------------------
787
788 IF g_miss_conv > 0
789 THEN
790 BEGIN -- begin IF g_miss_conv > 0
791
792 IF g_all_or_nothing_flag = 'N' -- We collected and report missing conversions
793 THEN
794 retcode := 1;
795 EDW_LOG.Put_Line(' ');
796 EDW_LOG.Put_Line('Collection finished with a CONVERSION RATE WARNING.');
797 EDW_LOG.Put_Line(' ');
798 ELSIF g_all_or_nothing_flag = 'Y' -- We did not collect and report missing conversions
799 THEN
800 retcode := -1;
801 EDW_LOG.Put_Line(' ');
802 EDW_LOG.Put_Line('Collection finished with a CONVERSION RATE ERROR.');
803 EDW_LOG.Put_Line('Collection aborted because there are missing conversion rates.');
804 EDW_LOG.Put_Line(' ');
805 END IF; --g_all_or_nothing_flag = 'N'
806
807 EDW_LOG.Put_Line('Below is the list of the missing conversions.');
808 EDW_LOG.Put_Line('Enter the missing currency rates in Oracle General Ledger.');
809 EDW_LOG.Put_Line('To fix the missing UOM please refer to the EDW implementation guide - UOM Setup');
810 EDW_LOG.Put_Line(' ');
811
812 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
813 EDW_LOG.Put_Line(' REPORT FOR THE MISSING CURRENCY CONVERSION RATES');
814 EDW_LOG.Put_Line('');
815 EDW_LOG.Put_Line('CONV. DATE - FROM CURR. - TO CURR. - CONV. TYPE CODE - STATUS');
816 EDW_LOG.Put_Line('---------- - ---------- - -------- - --------------- - ----------------------');
817
818 l_row_count := 0;
819
820 FOR line IN Missing_Currency_Conversion
821 LOOP
822 l_row_count := l_row_count + 1;
823 EDW_LOG.Put_Line( RPAD(line.curr_conv_date,10,' ')
824 ||' - '||RPAD(line.from_currency,10,' ')
825 ||' - '||RPAD(line.to_currency,8,' ')
826 ||' - '||RPAD(line.rate_type,15)
827 ||' - '||RPAD(line.status,20));
828 END LOOP;
829
830 IF l_row_count = 0
831 THEN
832 EDW_LOG.Put_Line('');
833 EDW_LOG.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
834 EDW_LOG.Put_Line('');
835 END IF;
836
837 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
838 EDW_LOG.Put_Line('');
839 EDW_LOG.Put_Line('');
840 EDW_LOG.Put_Line('');
841 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
842 EDW_LOG.Put_Line(' REPORT FOR THE MISSING UNIT OF MEASURE CONVERSION RATES');
843 EDW_LOG.Put_Line('');
844 EDW_LOG.Put_Line('FROM UOM - TO UOM - ITEM_ID - ITEM NUMBER : ITEM DESCRIPTION');
845 EDW_LOG.Put_Line('-------- - -------- - -------- - --------------------------------------------');
846
847 l_row_count := 0;
848
849 FOR line IN Missing_UOM_Conversion
850 LOOP
851 l_row_count := l_row_count + 1;
852 EDW_LOG.Put_Line( RPAD(line.from_uom_code,8,' ')
853 ||' - '||RPAD(line.to_uom_code,8,' ')
854 ||' - '||RPAD(line.item_id,8,' ')
855 ||' - '||RPAD(line.item_name,42));
856 END LOOP;
857
858 IF l_row_count = 0
859 THEN
860 EDW_LOG.Put_Line('');
861 EDW_LOG.Put_Line(' THERE IS NO MISSING UOM CONVERSION RATE ');
862 EDW_LOG.Put_Line('');
863 END IF;
864
865 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
866
867 END; -- begin IF g_miss_conv > 0
868 END IF; -- IF g_miss_conv > 0
869
870
871
872 -- ------------------------------------------------------
873 -- We are cleaning the table containing the missing rates
874 -- ------------------------------------------------------
875
876
877 DELETE FROM ISC_EDW_BACK_MISSING_RATE
878 WHERE id = g_seq_id_line;
879
880 -- ----------------------------------------------
881 -- No exception raised so far. Successful. Call
882 -- wrapup to commit and insert messages into logs
883 -- ----------------------------------------------
884
885 EDW_LOG.Put_Line(' ');
886 EDW_LOG.Put_Line('Inserted '||g_rows_collected||' rows into the local staging table');
887 EDW_LOG.Put_Line(' ');
888 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
889 EDW_LOG.Put_Line(' ');
890 EDW_LOG.Put_Line('Start of the Wrapup section ');
891 EDW_LOG.Put_Line(' ');
892
893 IF retcode = -1
894 THEN
895 EDW_COLLECTION_UTIL.Wrapup(
896 FALSE,
897 g_rows_collected,
898 NULL,
899 ISC_EDW_BACKLOGS_F_C.G_Push_From_Date,
900 ISC_EDW_BACKLOGS_F_C.G_Push_To_Date);
901 ELSE
902 EDW_COLLECTION_UTIL.Wrapup(
903 TRUE,
904 g_rows_collected,
905 NULL,
906 ISC_EDW_BACKLOGS_F_C.G_Push_From_Date,
907 ISC_EDW_BACKLOGS_F_C.G_Push_To_Date);
908 END IF;
909
910 COMMIT;
911
912 -- --------------------------------------------------
913 -- END OF Collection , Developer Customizable Section
914 -- --------------------------------------------------
915
916 EXCEPTION
917
918 WHEN L_FAILURE THEN
919 ROLLBACK;
920 EDW_LOG.Put_Line(g_errbuf);
921 retcode := -1;
922 EDW_COLLECTION_UTIL.Wrapup(
923 FALSE,
924 g_rows_collected,
925 NULL,
926 ISC_EDW_BACKLOGS_F_C.G_Push_From_Date,
927 ISC_EDW_BACKLOGS_F_C.G_Push_To_Date);
928
929 WHEN OTHERS THEN
930 ROLLBACK;
931 g_errbuf := sqlerrm ||' - '|| sqlcode;
932 EDW_LOG.Put_Line('Other errors : '|| g_errbuf);
933 retcode := -1;
934 EDW_COLLECTION_UTIL.Wrapup(
935 FALSE,
936 g_rows_collected,
937 NULL,
938 ISC_EDW_BACKLOGS_F_C.G_Push_From_Date,
939 ISC_EDW_BACKLOGS_F_C.G_Push_To_Date);
940
941 END;
942 END ISC_EDW_BACKLOGS_F_C;