[Home] [Help]
PACKAGE BODY: APPS.ISC_EDW_BOOKINGS_F_C
Source
1 PACKAGE BODY ISC_EDW_BOOKINGS_F_C AS
2 /* $Header: ISCSCF0B.pls 120.1 2005/09/07 11:55:23 scheung noship $ */
3
4 g_row_count NUMBER := 0;
5 g_rows_collected NUMBER := 0;
6 g_miss_conv NUMBER := 0;
7 g_seq_id_line_1 NUMBER := -1;
8 g_seq_id_line_2 NUMBER := -1;
9 g_seq_id_line_3 NUMBER := -1;
10
11 g_push_from_date DATE := NULL;
12 g_push_to_date DATE := NULL;
13
14 g_all_or_nothing_flag VARCHAR2(5) := 'Y';
15 g_errbuf VARCHAR2(2000) := NULL;
16
17
18
19 -- -------------
20 -- PUSH_TO_LOCAL
21 -- -------------
22
23 FUNCTION Push_To_Local(p_view_type IN NUMBER, p_seq_id IN NUMBER) RETURN NUMBER IS
24
25 BEGIN
26
27 INSERT INTO ISC_EDW_BOOKINGS_FSTG(
28 AGREEMENT_ID,
29 AGREEMENT_TYPE_FK,
30 BILL_TO_CUST_FK,
31 BILL_TO_LOC_FK,
32 BOOKED_DATE,
33 BOOKINGS_PK,
34 CAMPAIGN_ACTL_FK,
35 CAMPAIGN_INIT_FK,
36 CAMPAIGN_STATUS_ACTL_FK,
37 CAMPAIGN_STATUS_INIT_FK,
38 CANCEL_REASON_FK,
39 CONFIGURATION_ITEM_FLAG,
40 CONVERSION_DATE,
41 CONVERSION_RATE,
42 CONVERSION_TYPE,
43 CURRENCY_TRN_FK,
44 CUSTOMER_FK,
45 CUST_PO_NUMBER,
46 DATE_BOOKED_FK,
47 DATE_FULFILLED,
48 DATE_LATEST_PICK,
49 DATE_LATEST_SHIP,
50 DATE_PROMISED_FK,
51 DATE_REQUESTED_FK,
52 DATE_SCHEDULED_FK,
53 DEMAND_CLASS_FK,
54 EVENT_OFFER_ACTL_FK,
55 EVENT_OFFER_INIT_FK,
56 EVENT_OFFER_REG_FK,
57 FULFILLMENT_FLAG,
58 HEADER_ID,
59 INCLUDED_ITEM_FLAG,
60 INSTANCE,
61 INSTANCE_FK,
62 INV_ORG_FK,
63 ITEM_TYPE_CODE,
64 ITEM_ORG_FK,
65 LAST_UPDATE_DATE,
66 LINE_DETAIL_ID,
67 LINE_ID,
68 MARKET_SEGMENT_FK,
69 MEDCHN_ACTL_FK,
70 MEDCHN_INIT_FK,
71 OFFER_HDR_FK,
72 OFFER_LINE_FK,
73 OPERATING_UNIT_FK,
74 ORDER_CATEGORY_FK,
75 ORDER_NUMBER,
76 ORDER_SOURCE_FK,
77 ORDER_TYPE_FK,
78 ORDERED_DATE,
79 PRICE_LIST_ID,
80 PROMISED_DATE,
81 QTY_CANCELLED,
82 QTY_FULFILLED,
83 QTY_INVOICED,
84 QTY_ORDERED,
85 QTY_RESERVED,
86 QTY_RETURNED,
87 QTY_SHIPPED,
88 REQUESTED_DATE,
89 RETURN_REASON_FK,
90 SALES_CHANNEL_FK,
91 SALES_PERSON_FK,
92 SCHEDULED_DATE,
93 SET_OF_BOOKS_FK,
94 SHIPPABLE_FLAG,
95 SHIP_TO_CUST_FK,
96 SHIP_TO_LOC_FK,
97 SOURCE_LIST_FK,
98 TARGET_SEGMENT_ACTL_FK,
99 TARGET_SEGMENT_INIT_FK,
100 TASK_FK,
101 TOP_MODEL_FK,
102 TOTAL_NET_ORDER_VALUE,
103 TRANSACTABLE_FLAG,
104 UNIT_COST_G,
105 UNIT_COST_T,
106 UNIT_LIST_PRC_G,
107 UNIT_LIST_PRC_T,
108 UNIT_SELL_PRC_G,
109 UNIT_SELL_PRC_T,
110 UOM_UOM_FK,
111 USER_ATTRIBUTE1,
112 USER_ATTRIBUTE10,
113 USER_ATTRIBUTE11,
114 USER_ATTRIBUTE12,
115 USER_ATTRIBUTE13,
116 USER_ATTRIBUTE14,
117 USER_ATTRIBUTE15,
118 USER_ATTRIBUTE16,
119 USER_ATTRIBUTE17,
120 USER_ATTRIBUTE18,
121 USER_ATTRIBUTE19,
122 USER_ATTRIBUTE2,
123 USER_ATTRIBUTE20,
124 USER_ATTRIBUTE21,
125 USER_ATTRIBUTE22,
126 USER_ATTRIBUTE23,
127 USER_ATTRIBUTE24,
128 USER_ATTRIBUTE25,
129 USER_ATTRIBUTE3,
130 USER_ATTRIBUTE4,
131 USER_ATTRIBUTE5,
132 USER_ATTRIBUTE6,
133 USER_ATTRIBUTE7,
134 USER_ATTRIBUTE8,
135 USER_ATTRIBUTE9,
136 USER_FK1,
137 USER_FK2,
138 USER_FK3,
139 USER_FK4,
140 USER_FK5,
141 USER_MEASURE1,
142 USER_MEASURE2,
143 USER_MEASURE3,
144 USER_MEASURE4,
145 USER_MEASURE5,
146 OPERATION_CODE,
147 COLLECTION_STATUS)
148 SELECT /*+ leading(ISC_EDW_BOOKINGS_F_FCV.ISCBV_BOOKINGS_FCV.ftp) */
149 AGREEMENT_ID,
150 AGREEMENT_TYPE_FK,
151 BILL_TO_CUST_FK,
152 BILL_TO_LOC_FK,
153 BOOKED_DATE,
154 BOOKINGS_PK,
155 CAMPAIGN_ACTL_FK,
156 CAMPAIGN_INIT_FK,
157 CAMPAIGN_STATUS_ACTL_FK,
158 CAMPAIGN_STATUS_INIT_FK,
159 CANCEL_REASON_FK,
160 CONFIGURATION_ITEM_FLAG,
161 CONVERSION_DATE,
162 CONVERSION_RATE,
163 CONVERSION_TYPE,
164 CURRENCY_TRN_FK,
165 CUSTOMER_FK,
166 CUST_PO_NUMBER,
167 DATE_BOOKED_FK,
168 DATE_FULFILLED,
169 DATE_LATEST_PICK,
170 DATE_LATEST_SHIP,
171 DATE_PROMISED_FK,
172 DATE_REQUESTED_FK,
173 DATE_SCHEDULED_FK,
174 DEMAND_CLASS_FK,
175 EVENT_OFFER_ACTL_FK,
176 EVENT_OFFER_INIT_FK,
177 EVENT_OFFER_REG_FK,
178 FULFILLMENT_FLAG,
179 HEADER_ID,
180 INCLUDED_ITEM_FLAG,
181 INSTANCE,
182 INSTANCE_FK,
183 INV_ORG_FK,
184 ITEM_TYPE_CODE,
185 ITEM_ORG_FK,
186 LAST_UPDATE_DATE,
187 LINE_DETAIL_ID,
188 LINE_ID,
189 MARKET_SEGMENT_FK,
190 MEDCHN_ACTL_FK,
191 MEDCHN_INIT_FK,
192 OFFER_HDR_FK,
193 OFFER_LINE_FK,
194 OPERATING_UNIT_FK,
195 ORDER_CATEGORY_FK,
196 ORDER_NUMBER,
197 ORDER_SOURCE_FK,
198 ORDER_TYPE_FK,
199 ORDERED_DATE,
200 PRICE_LIST_ID,
201 PROMISED_DATE,
202 QTY_CANCELLED,
203 QTY_FULFILLED,
204 QTY_INVOICED,
205 QTY_ORDERED,
206 QTY_RESERVED,
207 QTY_RETURNED,
208 QTY_SHIPPED,
209 REQUESTED_DATE,
210 RETURN_REASON_FK,
211 SALES_CHANNEL_FK,
212 SALES_PERSON_FK,
213 SCHEDULED_DATE,
214 SET_OF_BOOKS_FK,
215 SHIPPABLE_FLAG,
216 SHIP_TO_CUST_FK,
217 SHIP_TO_LOC_FK,
218 SOURCE_LIST_FK,
219 TARGET_SEGMENT_ACTL_FK,
220 TARGET_SEGMENT_INIT_FK,
221 TASK_FK,
222 TOP_MODEL_FK,
223 TOTAL_NET_ORDER_VALUE,
224 TRANSACTABLE_FLAG,
225 UNIT_COST_G,
226 UNIT_COST_T,
227 UNIT_LIST_PRC_G,
228 UNIT_LIST_PRC_T,
229 UNIT_SELL_PRC_G,
230 UNIT_SELL_PRC_T,
231 UOM_UOM_FK,
232 USER_ATTRIBUTE1,
233 USER_ATTRIBUTE10,
234 USER_ATTRIBUTE11,
235 USER_ATTRIBUTE12,
236 USER_ATTRIBUTE13,
237 USER_ATTRIBUTE14,
238 USER_ATTRIBUTE15,
239 USER_ATTRIBUTE16,
240 USER_ATTRIBUTE17,
241 USER_ATTRIBUTE18,
242 USER_ATTRIBUTE19,
243 USER_ATTRIBUTE2,
244 USER_ATTRIBUTE20,
245 USER_ATTRIBUTE21,
246 USER_ATTRIBUTE22,
247 USER_ATTRIBUTE23,
248 USER_ATTRIBUTE24,
249 USER_ATTRIBUTE25,
250 USER_ATTRIBUTE3,
251 USER_ATTRIBUTE4,
252 USER_ATTRIBUTE5,
253 USER_ATTRIBUTE6,
254 USER_ATTRIBUTE7,
255 USER_ATTRIBUTE8,
256 USER_ATTRIBUTE9,
257 nvl(USER_FK1,'NA_EDW'),
258 nvl(USER_FK2,'NA_EDW'),
259 nvl(USER_FK3,'NA_EDW'),
260 nvl(USER_FK4,'NA_EDW'),
261 nvl(USER_FK5,'NA_EDW'),
262 USER_MEASURE1,
263 USER_MEASURE2,
264 USER_MEASURE3,
265 USER_MEASURE4,
266 USER_MEASURE5,
267 NULL, -- OPERATION_CODE
268 'READY'
269 FROM ISC_EDW_BOOKINGS_F_FCV
270 WHERE view_type = p_view_type
271 AND seq_id = p_seq_id;
272
273 RETURN(sql%rowcount);
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 g_errbuf := 'Error in Function Push_To_Local : '||sqlerrm;
278 RETURN(-1);
279 END;
280
281
282
283 -- ---------------
284 -- IDENTIFY_CHANGE
285 -- ---------------
286
287 FUNCTION Identify_Change( p_view_type IN NUMBER,
288 p_parent_seq_id IN NUMBER,
289 p_count OUT NOCOPY NUMBER) RETURN NUMBER IS
290
291 l_seq_id NUMBER := -1;
292
293 BEGIN
294
295 p_count := 0;
296
297 SELECT isc_tmp_pk_s.nextval
298 INTO l_seq_id
299 FROM dual;
300
301 -- ------------------------------------------
302 -- Populate rowid into isc_tmp_pk table based
303 -- on last update date
304 -- ------------------------------------------
305
306 -- --------------------------------------------
307 -- For EVERYTHING BUT INVOICES AND RESERVATIONS
308 -- --------------------------------------------
309 IF (p_view_type = 1)
310 THEN
311 INSERT INTO isc_tmp_pk(
312 SEQ_ID,
313 PK1,
314 PK2 )
315 SELECT /*+ PARALLEL(h) */
316 l_seq_id,
317 to_char(l.line_id),
318 to_char(l.line_id)
319 FROM oe_order_headers_all h,
320 oe_order_lines_all l
321 WHERE h.last_update_date BETWEEN g_push_from_date AND g_push_to_date
322 AND l.header_id = h.header_id
323 UNION
324 SELECT /*+ PARALLEL(l) */
325 l_seq_id,
326 to_char(l.line_id),
327 to_char(l.line_id)
328 FROM oe_order_lines_all l
329 WHERE l.last_update_date BETWEEN g_push_from_date AND g_push_to_date;
330
331 p_count := sql%rowcount;
332
333 UPDATE ISC_TMP_PK
334 SET seq_id = l_seq_id
335 WHERE seq_id = -10
336 AND pk2 NOT IN (SELECT pk2 FROM ISC_TMP_PK WHERE seq_id = l_seq_id) ;
337
338 p_count := p_count + sql%rowcount;
339
340 DELETE ISC_TMP_PK
341 WHERE seq_id = -10;
342
343 -- ----------------
344 -- For RESERVATIONS
345 -- ----------------
346
347 ELSIF (p_view_type = 2)
348 THEN
349 INSERT INTO isc_tmp_pk(
350 SEQ_ID,
351 PK1,
352 PK2)
353 SELECT l_seq_id,
354 mtl.reservation_id,
355 mtl.demand_source_line_id
356 FROM isc_tmp_pk isc,
357 mtl_reservations mtl
358 WHERE isc.PK1 = mtl.demand_source_line_id
359 AND isc.seq_ID = p_parent_seq_id
360 AND mtl.reservation_quantity IS NOT NULL
361 AND mtl.reservation_quantity <> 0
362 UNION
363 SELECT l_seq_id,
364 mtl.reservation_id,
365 mtl.demand_source_line_id
366 FROM mtl_reservations mtl
367 WHERE mtl.last_update_date BETWEEN g_push_from_date AND g_push_to_date
368 AND mtl.reservation_quantity IS NOT NULL
369 AND mtl.reservation_quantity <> 0;
370
371 p_count := sql%rowcount;
372
373 UPDATE ISC_TMP_PK
374 SET seq_id = l_seq_id
375 WHERE seq_id = -20
376 AND pk2 NOT IN (SELECT pk2 FROM ISC_TMP_PK WHERE SEQ_ID = l_seq_id);
377
378 p_count := p_count + sql%rowcount;
379
380 DELETE ISC_TMP_PK
381 WHERE seq_id = -20;
382
383 -- -----------------
384 -- For CANCELLATIONS
385 -- -----------------
386
387 ELSIF (p_view_type = 3)
388 THEN
389 INSERT INTO isc_tmp_pk(
390 SEQ_ID,
391 PK1,
392 PK2)
393 SELECT l_seq_id,
394 hist.line_id||to_char(hist.hist_creation_date,'SSSSS'),
395 hist.line_id
396 FROM isc_tmp_pk isc,
397 oe_order_lines_history hist
398 WHERE isc.PK1 = hist.line_id
399 AND isc.seq_ID = p_parent_seq_id
400 AND hist.hist_type_code = 'CANCELLATION'
401 UNION
402 SELECT l_seq_id,
403 hist.line_id||to_char(hist.hist_creation_date,'SSSSS'),
404 hist.line_id
405 FROM oe_order_lines_history hist
406 WHERE hist.last_update_date BETWEEN g_push_from_date AND g_push_to_date
407 AND hist.hist_type_code = 'CANCELLATION';
408
409 p_count := sql%rowcount;
410
411 UPDATE ISC_TMP_PK
412 SET seq_id = l_seq_id
413 WHERE seq_id = -30
414 AND pk2 NOT IN (SELECT pk2 FROM ISC_TMP_PK WHERE SEQ_ID = l_seq_id);
415
416 p_count := p_count + sql%rowcount;
417
418 DELETE ISC_TMP_PK
419 WHERE seq_id = -30;
420
421 END IF;
422
423 RETURN(l_seq_id);
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 g_errbuf := 'Error in Function Identify_Change : '||sqlerrm;
428 RETURN(-1);
429 END;
430
431
432 -- ---------------------
433 -- IDENTIFY_MISSING_RATE
434 -- ---------------------
435
436 FUNCTION Identify_Missing_Rate (p_count OUT NOCOPY NUMBER) RETURN NUMBER IS
437
438 BEGIN
439
440 p_count := 0;
441
442 INSERT INTO ISC_EDW_BOOK_MISSING_RATE
443 ( ID,
444 PK1,
445 PK2,
446 CURR_CONV_DATE,
447 FROM_CURRENCY,
448 TO_CURRENCY,
449 RATE_TYPE,
450 FROM_UOM_CODE,
451 TO_UOM_CODE,
452 INVENTORY_ITEM_ID,
453 ITEM_NAME,
454 STATUS)
455 SELECT -- Reports Transaction to Base Conversion Currency Issue
456 g_seq_id_line_1 ID,
457 ftp.pk1 PK1,
458 l.line_id PK2,
459 decode( upper(h.conversion_type_code), 'USER',
460 h.conversion_rate_date,
461 h.booked_date) CURR_CONV_DATE,
462 h.transactional_curr_code FROM_CURRENCY,
463 gl.currency_code TO_CURRENCY,
464 nvl(h.conversion_type_code,
465 edw_param.rate_type) RATE_TYPE,
466 '' FROM_UOM_CODE,
467 '' TO_UOM_CODE,
468 '' INVENTORY_ITEM_ID,
469 '' ITEM_NAME,
470 decode( decode( upper(h.conversion_type_code), 'USER',
471 h.conversion_rate,
472 decode( h.transactional_curr_code, gl.currency_code,
473 1,
474 GL_CURRENCY_API.get_rate_sql(
475 h.transactional_curr_code,
476 gl.currency_code,
477 h.booked_date,
478 nvl(h.conversion_type_code, edw_param.rate_type)))),
479 -1,
480 'RATE NOT AVAILABLE',
481 -2,
482 'INVALID CURRENCY') STATUS
483 FROM EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
484 ISC_TMP_PK ftp,
485 OE_ORDER_LINES_ALL l,
486 OE_ORDER_HEADERS_ALL h,
487 FINANCIALS_SYSTEM_PARAMS_ALL fspa,
488 GL_SETS_OF_BOOKS gl
489 WHERE ftp.pk2 = l.line_id
490 AND ftp.seq_id = g_seq_id_line_1
491 AND l.org_id = fspa.org_id
492 AND l.header_id = h.header_id
493 AND fspa.set_of_books_id = gl.set_of_books_id
494 AND h.booked_flag = 'Y'
495 AND h.booked_date IS NOT NULL
496 AND decode( upper(h.conversion_type_code), 'USER',
497 h.conversion_rate,
498 decode( h.transactional_curr_code, gl.currency_code,
499 1,
500 GL_CURRENCY_API.get_rate_sql(
501 h.transactional_curr_code,
502 gl.currency_code,
503 h.booked_date,
504 nvl(h.conversion_type_code, edw_param.rate_type)))) < 0
505 UNION
506 SELECT -- Reports Base to Global Conversion Currency Issue
507 g_seq_id_line_1 ID,
508 ftp.pk1 PK1,
509 l.line_id PK2,
510 decode(upper(h.conversion_type_code), 'USER',
511 h.conversion_rate_date,
512 h.booked_date) CURR_CONV_DATE,
513 gl.currency_code FROM_CURRENCY,
514 edw_param.warehouse_currency_code TO_CURRENCY,
515 nvl(h.conversion_type_code,
516 edw_param.rate_type) RATE_TYPE,
517 '' FROM_UOM_CODE,
518 '' TO_UOM_CODE,
519 '' INVENTORY_ITEM_ID,
520 '' ITEM_NAME,
521 decode( EDW_CURRENCY.Get_Rate (gl.currency_code,h.booked_date),
522 -1,
523 'RATE NOT AVAILABLE',
524 -2,
525 'INVALID CURRENCY') STATUS
526 FROM EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
527 ISC_TMP_PK ftp,
528 OE_ORDER_LINES_ALL l,
529 OE_ORDER_HEADERS_ALL h,
530 FINANCIALS_SYSTEM_PARAMS_ALL fspa,
531 GL_SETS_OF_BOOKS gl
532 WHERE ftp.pk2 = l.line_id
533 AND ftp.seq_id = g_seq_id_line_1
534 AND l.org_id = fspa.org_id
535 AND l.header_id = h.header_id
536 AND fspa.set_of_books_id = gl.set_of_books_id
537 AND h.booked_flag = 'Y'
538 AND h.booked_date IS NOT NULL
539 AND EDW_CURRENCY.Get_Rate (gl.currency_code,h.booked_date) < 0
540 UNION
541 SELECT -- Reports Base to Transaction Conversion Currency Issue
542 g_seq_id_line_1 ID,
543 ftp.pk1 PK1,
544 l.line_id PK2,
545 decode(upper(h.conversion_type_code), 'USER',
546 h.conversion_rate_date,
547 h.booked_date) CURR_CONV_DATE,
548 gl.currency_code FROM_CURRENCY,
549 h.transactional_curr_code TO_CURRENCY,
550 nvl(h.conversion_type_code,
551 edw_param.rate_type) RATE_TYPE,
552 '' FROM_UOM_CODE,
553 '' TO_UOM_CODE,
554 '' INVENTORY_ITEM_ID,
555 '' ITEM_NAME,
556 decode( decode( upper(h.conversion_type_code),'USER',
557 1/ h.conversion_rate,
558 decode( h.transactional_curr_code, gl.currency_code,
559 1,
560 GL_CURRENCY_API.get_rate_sql (
561 gl.currency_code,
562 h.transactional_curr_code,
563 h.booked_date,
564 nvl(h.conversion_type_code, edw_param.rate_type)))),
565 -1,
566 'RATE NOT AVAILABLE',
567 -2,
568 'INVALID CURRENCY') STATUS
569 FROM EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
570 ISC_TMP_PK ftp,
571 OE_ORDER_LINES_ALL l,
572 OE_ORDER_HEADERS_ALL h,
573 FINANCIALS_SYSTEM_PARAMS_ALL fspa,
574 GL_SETS_OF_BOOKS gl
575 WHERE ftp.pk2 = l.line_id
576 AND ftp.seq_id = g_seq_id_line_1
577 AND l.org_id = fspa.org_id
578 AND l.header_id = h.header_id
579 AND fspa.set_of_books_id = gl.set_of_books_id
580 AND h.booked_flag = 'Y'
581 AND h.booked_date IS NOT NULL
582 AND decode( upper(h.conversion_type_code),'USER',
583 1/ h.conversion_rate,
584 decode( h.transactional_curr_code, gl.currency_code,
585 1,
586 GL_CURRENCY_API.get_rate_sql (
587 gl.currency_code,
588 h.transactional_curr_code,
589 h.booked_date,
590 nvl(h.conversion_type_code, edw_param.rate_type)))) < 0
591 UNION
592 SELECT -- Reports "Ship from Org Base" to "Header Org Base" Conversion Currency Issue
593 g_seq_id_line_1 ID,
594 ftp.pk1 PK1,
595 l.line_id PK2,
596 h.booked_date CURR_CONV_DATE,
597 gl_cost.currency_code FROM_CURRENCY,
598 gl.currency_code TO_CURRENCY,
599 edw_param.rate_type RATE_TYPE,
600 '' FROM_UOM_CODE,
601 '' TO_UOM_CODE,
602 '' INVENTORY_ITEM_ID,
603 '' ITEM_NAME,
604 decode(GL_CURRENCY_API.get_rate_sql (
605 gl_cost.currency_code,
606 gl.currency_code,
607 h.booked_date,
608 edw_param.rate_type),
609 -1,
610 'RATE NOT AVAILABLE',
611 -2,
612 'INVALID CURRENCY') STATUS
613 FROM EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
614 ISC_TMP_PK ftp,
615 OE_ORDER_LINES_ALL l,
616 OE_ORDER_HEADERS_ALL h,
617 FINANCIALS_SYSTEM_PARAMS_ALL fspa,
618 GL_SETS_OF_BOOKS gl,
619 GL_SETS_OF_BOOKS gl_cost,
620 HR_ORGANIZATION_INFORMATION hoi
621 WHERE ftp.pk2 = l.line_id
622 AND ftp.seq_id = g_seq_id_line_1
623 AND l.org_id = fspa.org_id
624 AND l.header_id = h.header_id
625 AND fspa.set_of_books_id = gl.set_of_books_id
626 AND l.ship_from_org_id = hoi.organization_id -- if ship_from_org_id is null, don't include row in the missing rates
627 AND hoi.org_information_context = 'Accounting Information'
628 AND hoi.org_information1 = to_char(gl_cost.set_of_books_id)
629 AND h.booked_flag = 'Y'
630 AND h.booked_date IS NOT NULL
631 AND GL_CURRENCY_API.get_rate_sql (
632 gl_cost.currency_code,
633 gl.currency_code,
634 h.booked_date,
635 edw_param.rate_type) < 0;
636
637 p_count := sql%rowcount;
638
639 INSERT INTO ISC_EDW_BOOK_MISSING_RATE(
640 ID,
641 PK1,
642 PK2,
643 CURR_CONV_DATE,
644 FROM_CURRENCY,
645 TO_CURRENCY,
646 RATE_TYPE,
647 FROM_UOM_CODE,
648 TO_UOM_CODE,
649 INVENTORY_ITEM_ID,
650 ITEM_NAME,
651 STATUS)
652 SELECT -- Reports UOM Conversion Issue
653 g_seq_id_line_1 ID,
654 ftp.pk1 PK1,
655 l.line_id PK2,
656 to_date(NULL) CURR_CONV_DATE,
657 '' FROM_CURRENCY,
658 '' TO_CURRENCY,
659 '' RATE_TYPE,
660 l.order_quantity_uom FROM_UOM_CODE,
661 EDW_UTIL.Get_Edw_Base_Uom(
662 l.order_quantity_uom,
663 l.inventory_item_id) TO_UOM_CODE,
664 l.inventory_item_id INVENTORY_ITEM_ID,
665 nvl(mtl.segment1,'Item number unavailable')
666 ||' : '||nvl(description,'Description unavailable')
667 ITEM_NAME,
668 'UOM ISSUE' STATUS
669 FROM ISC_TMP_PK ftp,
670 OE_ORDER_LINES_ALL l,
671 OE_ORDER_HEADERS_ALL h,
672 MTL_SYSTEM_ITEMS_B mtl
673 WHERE ftp.pk2 = l.line_id
674 AND ftp.seq_id = g_seq_id_line_1
675 AND l.header_id = h.header_id
676 AND h.booked_flag = 'Y'
677 AND h.booked_date IS NOT NULL
678 AND l.ship_from_org_id = mtl.organization_id
679 AND l.inventory_item_id = mtl.inventory_item_id
680 AND EDW_UTIL.Get_Uom_Conv_Rate(l.order_quantity_uom,l.inventory_item_id) IS NULL;
681
682 p_count := p_count + sql%rowcount;
683
684 RETURN(p_count);
685
686 EXCEPTION
687 WHEN OTHERS THEN
688 g_errbuf := 'Error in Function Identify_Missing_Rate : '||sqlerrm;
689 RETURN(-1);
690 END;
691
692
693 -- -----------------
694 -- INSERT_ISC_TMP_PK
695 -- -----------------
696
697 FUNCTION Insert_Isc_Tmp_Pk (p_count OUT NOCOPY NUMBER) RETURN NUMBER IS
698
699 BEGIN
700
701 p_count := 0 ;
702
703 INSERT INTO ISC_TMP_PK (
704 seq_id,
705 pk1,
706 pk2)
707 SELECT -10,
708 ftp.pk1,
709 ftp.pk2
710 FROM ISC_EDW_BOOK_MISSING_RATE conv,
711 ISC_TMP_PK ftp
712 WHERE conv.pk2 = ftp.pk2
713 AND ftp.seq_id = g_seq_id_line_1
714 AND conv.id = g_seq_id_line_1 ;
715
716 p_count := p_count + sql%rowcount ;
717
718 INSERT INTO ISC_TMP_PK (
719 seq_id,
720 pk1,
721 pk2)
722 SELECT -20,
723 ftp.pk1,
724 ftp.pk2
725 FROM ISC_EDW_BOOK_MISSING_RATE conv,
726 ISC_TMP_PK ftp
727 WHERE conv.pk2 = ftp.pk2
728 AND ftp.seq_id = g_seq_id_line_2
729 AND conv.id = g_seq_id_line_1;
730
731 p_count := p_count + sql%rowcount ;
732
733 INSERT INTO ISC_TMP_PK (
734 seq_id,
735 pk1,
736 pk2)
737 SELECT -30,
738 ftp.pk1,
739 ftp.pk2
740 FROM ISC_EDW_BOOK_MISSING_RATE conv,
741 ISC_TMP_PK ftp
742 WHERE conv.pk2 = ftp.pk2
743 AND ftp.seq_id = g_seq_id_line_3
744 AND conv.id = g_seq_id_line_1;
745
746 p_count := p_count + sql%rowcount ;
747
748 RETURN(p_count);
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 g_errbuf := 'Error in Function INSERT_ISC_TMP_PK : '||sqlerrm;
753 RETURN(-1);
754 END;
755
756
757 -- -----------------
758 -- DELETION_HANDLING
759 -- -----------------
760
761 FUNCTION Deletion_Handling RETURN NUMBER IS
762
763 l_stmt VARCHAR2(20000);
764 l_apps_to_apps VARCHAR2(100);
765 l_edw_apps_to_wh VARCHAR2(100);
766 l_edw_local_instance VARCHAR2(100);
767
768 BEGIN
769
770 EDW_COLLECTION_UTIL.Get_Dblink_Names(l_apps_to_apps,l_edw_apps_to_wh);
771 l_stmt := 'SELECT instance_code FROM EDW_LOCAL_INSTANCE';
772 EXECUTE IMMEDIATE l_stmt INTO l_edw_local_instance;
773
774 l_stmt := 'INSERT INTO ISC_EDW_BOOKINGS_FSTG (
775 BOOKINGS_PK,
776 COLLECTION_STATUS,
777 OPERATION_CODE,
778 AGREEMENT_TYPE_FK,
779 BILL_TO_CUST_FK,
780 BILL_TO_LOC_FK,
781 CAMPAIGN_ACTL_FK,
782 CAMPAIGN_INIT_FK,
783 CAMPAIGN_STATUS_ACTL_FK,
784 CAMPAIGN_STATUS_INIT_FK,
785 CANCEL_REASON_FK,
786 CURRENCY_TRN_FK,
787 CUSTOMER_FK,
788 DATE_BOOKED_FK,
789 DATE_PROMISED_FK,
790 DATE_REQUESTED_FK,
791 DATE_SCHEDULED_FK,
792 DEMAND_CLASS_FK,
793 EVENT_OFFER_ACTL_FK,
794 EVENT_OFFER_INIT_FK,
795 EVENT_OFFER_REG_FK,
796 INSTANCE_FK,
797 INV_ORG_FK,
798 ITEM_ORG_FK,
799 MARKET_SEGMENT_FK,
800 MEDCHN_ACTL_FK,
801 MEDCHN_INIT_FK,
802 OFFER_HDR_FK,
803 OFFER_LINE_FK,
804 OPERATING_UNIT_FK,
805 ORDER_CATEGORY_FK,
806 ORDER_SOURCE_FK,
807 ORDER_TYPE_FK,
808 RETURN_REASON_FK,
809 SALES_CHANNEL_FK,
810 SALES_PERSON_FK,
811 SET_OF_BOOKS_FK,
812 SHIP_TO_CUST_FK,
813 SHIP_TO_LOC_FK,
814 SOURCE_LIST_FK,
815 TARGET_SEGMENT_ACTL_FK,
816 TARGET_SEGMENT_INIT_FK,
817 TASK_FK,
818 TOP_MODEL_FK,
819 UOM_UOM_FK,
820 USER_FK1,
821 USER_FK2,
822 USER_FK3,
823 USER_FK4,
824 USER_FK5)
825 SELECT /*+ INDEX(del ISC_EDW_BOOK_DEL_N1)*/
826 del.BOOKINGS_PK,
827 ''READY'',
828 ''DELETE'',
829 ''NA_EDW'',
830 ''NA_EDW'',
831 ''NA_EDW'',
832 ''NA_EDW'',
833 ''NA_EDW'',
834 ''NA_EDW'',
835 ''NA_EDW'',
836 ''NA_EDW'',
837 ''NA_EDW'',
838 ''NA_EDW'',
839 ''NA_EDW'',
840 ''NA_EDW'',
841 ''NA_EDW'',
842 ''NA_EDW'',
843 ''NA_EDW'',
844 ''NA_EDW'',
845 ''NA_EDW'',
846 ''NA_EDW'',
847 ''NA_EDW'',
848 ''NA_EDW'',
849 ''NA_EDW'',
850 ''NA_EDW'',
851 ''NA_EDW'',
852 ''NA_EDW'',
853 ''NA_EDW'',
854 ''NA_EDW'',
855 ''NA_EDW'',
856 ''NA_EDW'',
857 ''NA_EDW'',
858 ''NA_EDW'',
859 ''NA_EDW'',
860 ''NA_EDW'',
861 ''NA_EDW'',
862 ''NA_EDW'',
863 ''NA_EDW'',
864 ''NA_EDW'',
865 ''NA_EDW'',
866 ''NA_EDW'',
867 ''NA_EDW'',
868 ''NA_EDW'',
869 ''NA_EDW'',
870 ''NA_EDW'',
871 ''NA_EDW'',
872 ''NA_EDW'',
873 ''NA_EDW'',
874 ''NA_EDW'',
875 ''NA_EDW''
876 FROM ISC_EDW_BOOK_DEL@'||l_edw_apps_to_wh||' del
877 WHERE del.inst_name = '''||l_edw_local_instance|| '''
878 AND NOT EXISTS ( SELECT l.line_id
879 FROM OE_ORDER_LINES_ALL l
880 WHERE l.line_id = del.line_id)
881 UNION ALL
882 SELECT /*+ INDEX(del ISC_EDW_BOOK_DEL_N1)*/
883 del.BOOKINGS_PK,
884 ''READY'',
885 ''DELETE'',
886 ''NA_EDW'',
887 ''NA_EDW'',
888 ''NA_EDW'',
889 ''NA_EDW'',
890 ''NA_EDW'',
891 ''NA_EDW'',
892 ''NA_EDW'',
893 ''NA_EDW'',
894 ''NA_EDW'',
895 ''NA_EDW'',
896 ''NA_EDW'',
897 ''NA_EDW'',
898 ''NA_EDW'',
899 ''NA_EDW'',
900 ''NA_EDW'',
901 ''NA_EDW'',
902 ''NA_EDW'',
903 ''NA_EDW'',
904 ''NA_EDW'',
905 ''NA_EDW'',
906 ''NA_EDW'',
907 ''NA_EDW'',
908 ''NA_EDW'',
909 ''NA_EDW'',
910 ''NA_EDW'',
911 ''NA_EDW'',
912 ''NA_EDW'',
913 ''NA_EDW'',
914 ''NA_EDW'',
915 ''NA_EDW'',
916 ''NA_EDW'',
917 ''NA_EDW'',
918 ''NA_EDW'',
919 ''NA_EDW'',
920 ''NA_EDW'',
921 ''NA_EDW'',
922 ''NA_EDW'',
923 ''NA_EDW'',
924 ''NA_EDW'',
925 ''NA_EDW'',
926 ''NA_EDW'',
927 ''NA_EDW'',
928 ''NA_EDW'',
929 ''NA_EDW'',
930 ''NA_EDW'',
931 ''NA_EDW'',
932 ''NA_EDW''
933 FROM ISC_EDW_BOOK_DEL@'||l_edw_apps_to_wh||' del
934 WHERE del.inst_name = '''||l_edw_local_instance|| '''
935 AND substr(del.bookings_pk,1,3) = ''RES''
936 AND NOT EXISTS (SELECT demand_source_line_id
937 FROM MTL_RESERVATIONS res
938 WHERE res.demand_source_line_id = del.line_id
939 and del.bookings_pk= ''RES-''||res.reservation_id||''-'||l_edw_local_instance||''')';
940
941 EXECUTE IMMEDIATE l_stmt;
942
943 RETURN(sql%rowcount);
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 g_errbuf := 'Error in Function Deletion_Handling : '||sqlerrm;
948 RETURN(-1);
949 END;
950
951
952 -- -----------------
953 -- DELETE_ISC_TMP_PK
954 -- -----------------
955
956 FUNCTION Delete_Isc_Tmp_Pk RETURN NUMBER IS
957
958 BEGIN
959
960 DELETE FROM ISC_TMP_PK
961 WHERE pk2 IN ( SELECT pk2
962 FROM ISC_EDW_BOOK_MISSING_RATE
963 WHERE id = g_seq_id_line_1 )
964 AND seq_id >0 ;
965
966 RETURN(sql%rowcount);
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 g_errbuf := 'Error in Function Delete_Isc_Tmp_Pk : '||sqlerrm;
971 RETURN(-1);
972 END;
973
974
975 -- -----------------
976 -- PUBLIC PROCEDURES
977 -- -----------------
978
979 -- --------------
980 -- PROCEDURE PUSH
981 -- --------------
982
983 Procedure Push( errbuf IN OUT NOCOPY VARCHAR2,
984 retcode IN OUT NOCOPY VARCHAR2,
985 p_from_date IN VARCHAR2,
986 p_to_date IN VARCHAR2,
987 p_coll_flag IN VARCHAR2) IS -- 'Yes' = All or Nothing , 'No' = Collect >0 rows only
988
989 l_fact_name VARCHAR2(30) := 'ISC_EDW_BOOKINGS_F' ;
990
991 l_from_date DATE := NULL;
992 l_to_date DATE := NULL;
993
994 l_row_count NUMBER := 0;
995
996 l_failure EXCEPTION;
997
998 CURSOR Missing_Currency_Conversion IS
999 SELECT DISTINCT trunc(curr_conv_date) curr_conv_date,
1000 from_currency,
1001 to_currency,
1002 rate_type,
1003 status
1004 FROM ISC_EDW_BOOK_MISSING_RATE
1005 WHERE status NOT IN ('UOM ISSUE')
1006 ORDER BY status,from_currency,trunc(curr_conv_date);
1007
1008 CURSOR Missing_UOM_Conversion IS
1009 SELECT DISTINCT from_uom_code,
1010 to_uom_code,
1011 inventory_item_id item_id,
1012 substr(item_name,0,50) item_name
1013 FROM ISC_EDW_BOOK_MISSING_RATE
1014 WHERE status = 'UOM ISSUE'
1015 ORDER BY item_name,from_uom_code;
1016
1017
1018 -- -------------------------------------------
1019 -- Put any additional developer variables here
1020 -- -------------------------------------------
1021 BEGIN
1022
1023 errbuf := NULL;
1024 retcode := '0';
1025
1026 l_from_date := to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
1027 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
1028
1029 g_all_or_nothing_flag := upper(p_coll_flag);
1030
1031 IF (NOT EDW_COLLECTION_UTIL.Setup(l_fact_name))
1032 THEN
1033 g_errbuf := 'Error in function Setup : '||fnd_message.get;
1034 RAISE l_failure;
1035 END IF;
1036
1037 ISC_EDW_BOOKINGS_F_C.G_Push_From_Date := nvl(l_from_date,
1038 EDW_COLLECTION_UTIL.G_Local_Last_Push_Start_Date - EDW_COLLECTION_UTIL.G_Offset);
1039
1040 ISC_EDW_BOOKINGS_F_C.G_Push_To_Date := nvl(l_to_date,
1041 EDW_COLLECTION_UTIL.G_Local_Curr_Push_Start_Date);
1042
1043 EDW_LOG.Put_Line( 'The collection range is from '||
1044 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
1045 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
1046 EDW_LOG.Put_Line(' ');
1047
1048
1049 -- ---------------------------------------
1050 -- Identify Change for Booked Orders Lines
1051 -- ---------------------------------------
1052
1053 EDW_LOG.Put_Line('Identifying changed Booked orders lines');
1054
1055 FII_UTIL.Start_Timer;
1056
1057 g_seq_id_line_1 := Identify_Change(1,-1 , l_row_count);
1058
1059 FII_UTIL.Stop_Timer;
1060 FII_UTIL.Print_Timer('Identified '||l_row_count||' changed records in ');
1061
1062 IF (g_seq_id_line_1 = -1)
1063 THEN RAISE l_failure;
1064 END IF;
1065
1066
1067 -- -----------------------------------
1068 -- Identify Change for Reserved Orders
1069 -- -----------------------------------
1070
1071 EDW_LOG.Put_Line(' ');
1072 EDW_LOG.Put_Line('Identifying changed for reserved orders');
1073
1074 FII_UTIL.Start_Timer;
1075
1076 g_seq_id_line_2 := Identify_Change(2, g_seq_id_line_1, l_row_count);
1077
1078 FII_UTIL.Stop_Timer;
1079 FII_UTIL.Print_Timer('Identified '||l_row_count||' changed records in ');
1080
1081 IF (g_seq_id_line_2 = -1)
1082 THEN RAISE l_failure;
1083 END IF;
1084
1085
1086 -- -------------------------------------------------
1087 -- Identify Change for Multiple-Cancellations Orders
1088 -- -------------------------------------------------
1089
1090 EDW_LOG.Put_Line(' ');
1091 EDW_LOG.Put_Line('Identifying changed for Multi-Cancellations orders');
1092
1093 FII_UTIL.Start_Timer;
1094
1095 g_seq_id_line_3 := Identify_Change(3, g_seq_id_line_1, l_row_count);
1096
1097 FII_UTIL.Stop_Timer;
1098 FII_UTIL.Print_Timer('Identified '||l_row_count||' changed records in ');
1099
1100 IF (g_seq_id_line_3 = -1)
1101 THEN RAISE l_failure;
1102 END IF;
1103
1104
1105 -- ----------------------------------------------------------
1106 -- Identify Missing Rate into ISC_EDW_BOOK_MISSING_RATE table
1107 -- ----------------------------------------------------------
1108
1109 EDW_LOG.Put_Line(' ');
1110 EDW_LOG.Put_Line('Identifying the missing conversion rates (currency and UoM)');
1111
1112 FII_UTIL.Start_Timer;
1113
1114 g_miss_conv := Identify_Missing_Rate (l_row_count);
1115
1116 FII_UTIL.Stop_Timer;
1117
1118 IF (g_miss_conv = -1)
1119 THEN RAISE l_failure;
1120 END IF;
1121
1122 FII_UTIL.Print_Timer('Inserted '||g_miss_conv||' rows into the ISC_EDW_BOOK_MISSING_RATE table in ');
1123 EDW_LOG.Put_Line(' ');
1124
1125
1126 -- --------------------------------------------------------------------
1127 -- Inserting into ISC_TMP_PK rows having missing rate (with seq_id < 0)
1128 -- --------------------------------------------------------------------
1129
1130 EDW_LOG.Put_Line(' ');
1131 EDW_LOG.Put_Line('Inserting into ISC_TMP_PK with < 0 seq_id the rows having missing conversion rates (currency and UoM)');
1132
1133 FII_UTIL.Start_Timer;
1134
1135 g_row_count := Insert_Isc_Tmp_Pk (l_row_count);
1136
1137 FII_UTIL.Stop_Timer;
1138
1139 IF (g_row_count = -1)
1140 THEN RAISE l_failure;
1141 END IF;
1142
1143 FII_UTIL.Print_Timer('Inserted '||l_row_count||' rows into the ISC_TMP_PK table in ');
1144 EDW_LOG.Put_Line(' ');
1145
1146
1147 -- --------------------------------------------
1148 -- Deleting ISC_TMP_PK rows having missing rate
1149 -- --------------------------------------------
1150
1151 EDW_LOG.Put_Line(' ');
1152 EDW_LOG.Put_Line('Deleting the ISC_TMP_PK rows having a missing conversion rates before collecting (currency and UoM)');
1153
1154 FII_UTIL.Start_Timer;
1155
1156 g_row_count := Delete_Isc_Tmp_Pk ;
1157
1158 FII_UTIL.Stop_Timer;
1159
1160 IF (g_row_count = -1)
1161 THEN RAISE l_failure;
1162 END IF;
1163
1164 FII_UTIL.Print_Timer('Deleted '||g_row_count||' rows from the ISC_TMP_PK table in ');
1165 EDW_LOG.Put_Line(' ');
1166
1167
1168 IF NOT ((g_all_or_nothing_flag = 'Y') and (g_miss_conv > 0)) -- collect except when this condition applies
1169 THEN
1170 BEGIN -- IF NOT ((g_all_or_nothing_flag = 'Y') and (g_miss_conv > 0))
1171
1172 -- -------------------------------------------
1173 -- Push to Local staging table for view type 1
1174 -- -------------------------------------------
1175
1176 EDW_LOG.Put_Line(' ');
1177 EDW_LOG.Put_Line('Pushing data to local staging with the view type = 1');
1178
1179 EDW_LOG.Put_Line( 'The collection range is from '||
1180 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
1181 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
1182
1183 FII_UTIL.Start_Timer;
1184
1185 g_row_count := Push_To_Local(1,g_seq_id_line_1);
1186
1187 FII_UTIL.Stop_Timer;
1188
1189 IF (g_row_count = -1)
1190 THEN RAISE l_failure;
1191 END IF;
1192
1193 g_rows_collected := g_row_count;
1194
1195 FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows with view type = 1 into the local staging table in ');
1196 EDW_LOG.Put_Line(' ');
1197
1198
1199 -- -------------------------------------------
1200 -- Push to Local staging table for view type 2
1201 -- -------------------------------------------
1202
1203 EDW_LOG.Put_Line(' ');
1204 EDW_LOG.Put_Line('Pushing data to local staging with the view type = 2');
1205
1206 EDW_LOG.Put_Line(' ');
1207 EDW_LOG.Put_Line( 'The collection range is from '||
1208 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
1209 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
1210 EDW_LOG.Put_Line(' ');
1211
1212 FII_UTIL.Start_Timer;
1213
1214 g_row_count := Push_To_Local(2,g_seq_id_line_2);
1215
1216 FII_UTIL.Stop_Timer;
1217
1218 IF (g_row_count = -1)
1219 THEN RAISE l_failure;
1220 END IF;
1221
1222 g_rows_collected := g_rows_collected + g_row_count;
1223
1224 FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows with view type = 2 into the local staging table in ');
1225 EDW_LOG.Put_Line(' ');
1226
1227
1228 -- -------------------------------------------
1229 -- Push to Local staging table for view type 3
1230 -- -------------------------------------------
1231
1232 EDW_LOG.Put_Line(' ');
1233 EDW_LOG.Put_Line('Pushing data to local staging with the view type = 3');
1234 EDW_LOG.Put_Line(' ');
1235
1236 FII_UTIL.Start_Timer;
1237
1238 g_row_count := Push_To_Local(3,g_seq_id_line_3);
1239
1240 FII_UTIL.Stop_Timer;
1241
1242 IF (g_row_count = -1)
1243 THEN RAISE l_failure;
1244 END IF;
1245
1246 g_rows_collected := g_rows_collected + g_row_count;
1247
1248 FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows with view type = 3 into the local staging table in ');
1249 EDW_LOG.Put_Line(' ');
1250
1251 EDW_LOG.Put_Line(' ');
1252 EDW_LOG.Put_Line('Marking rows to be deleted from the Fact (rows having beeing deleted from the source instance table)');
1253 EDW_LOG.Put_Line(' ');
1254
1255 FII_UTIL.Start_Timer;
1256
1257 g_row_count := Deletion_Handling;
1258
1259 FII_UTIL.Stop_Timer;
1260
1261 IF (g_row_count = -1)
1262 THEN RAISE l_failure;
1263 END IF;
1264
1265 FII_UTIL.Print_Timer('Marked '||g_row_count||' rows to be deleted from the Fact in ');
1266 EDW_LOG.Put_Line(' ');
1267
1268 END;
1269 END IF; -- IF NOT ((g_all_or_nothing_flag = 'Y') and (g_miss_conv > 0))
1270
1271
1272 -- -----------------
1273 -- Delete ISC_TMP_PK
1274 -- -----------------
1275
1276 DELETE FROM isc_tmp_pk
1277 WHERE seq_id IN (g_seq_id_line_1, g_seq_id_line_2, g_seq_id_line_3);
1278
1279
1280 -- -------------------------------------------
1281 -- Reporting of the missing currencies and UoM
1282 -- -------------------------------------------
1283
1284 IF g_miss_conv > 0
1285 THEN
1286 BEGIN -- begin IF g_miss_conv > 0
1287
1288 IF g_all_or_nothing_flag = 'N' -- We collected and report missing conversions
1289 THEN
1290 retcode := 1;
1291 EDW_LOG.Put_Line(' ');
1292 EDW_LOG.Put_Line('Collection finished with a CONVERSION RATE WARNING.');
1293 EDW_LOG.Put_Line(' ');
1294 ELSIF g_all_or_nothing_flag = 'Y' -- We did not collect and report missing conversions
1295 THEN
1296 retcode := -1;
1297 EDW_LOG.Put_Line(' ');
1298 EDW_LOG.Put_Line('Collection finished with a CONVERSION RATE ERROR.');
1299 EDW_LOG.Put_Line('Collection aborted because there are missing conversion rates.');
1300 EDW_LOG.Put_Line(' ');
1301 END IF; --g_all_or_nothing_flag = 'N'
1302
1303 EDW_LOG.Put_Line('Below is the list of the missing conversions.');
1304 EDW_LOG.Put_Line('Enter the missing currency rates in Oracle General Ledger.');
1305 EDW_LOG.Put_Line('To fix the missing UOM please refer to the EDW implementation guide - UOM Setup');
1306 EDW_LOG.Put_Line(' ');
1307
1308 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
1309 EDW_LOG.Put_Line(' REPORT FOR THE MISSING CURRENCY CONVERSION RATES');
1310 EDW_LOG.Put_Line('');
1311 EDW_LOG.Put_Line('CONV. DATE - FROM CURR. - TO CURR. - CONV. TYPE CODE - STATUS');
1312 EDW_LOG.Put_Line('---------- - ---------- - -------- - --------------- - ----------------------');
1313
1314 l_row_count := 0;
1315
1316 FOR line IN Missing_Currency_Conversion
1317 LOOP
1318 l_row_count := l_row_count + 1;
1319 EDW_LOG.Put_Line( RPAD(line.curr_conv_date,10,' ')
1320 ||' - '||RPAD(line.from_currency,10,' ')
1321 ||' - '||RPAD(line.to_currency,8,' ')
1322 ||' - '||RPAD(line.rate_type,15)
1323 ||' - '||RPAD(line.status,20));
1324 END LOOP;
1325
1326 IF l_row_count = 0
1327 THEN
1328 EDW_LOG.Put_Line('');
1329 EDW_LOG.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1330 EDW_LOG.Put_Line('');
1331 END IF;
1332
1333 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
1334 EDW_LOG.Put_Line('');
1335 EDW_LOG.Put_Line('');
1336 EDW_LOG.Put_Line('');
1337 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
1338 EDW_LOG.Put_Line(' REPORT FOR THE MISSING UNIT OF MEASURE CONVERSION RATES');
1339 EDW_LOG.Put_Line('');
1340 EDW_LOG.Put_Line('FROM UOM - TO UOM - ITEM_ID - ITEM NUMBER : ITEM DESCRIPTION');
1341 EDW_LOG.Put_Line('-------- - -------- - -------- - --------------------------------------------');
1342
1343 l_row_count := 0;
1344
1345 FOR line IN Missing_UOM_Conversion
1346 LOOP
1347 l_row_count := l_row_count + 1;
1348 EDW_LOG.Put_Line( RPAD(line.from_uom_code,8,' ')
1349 ||' - '||RPAD(line.to_uom_code,8,' ')
1350 ||' - '||RPAD(line.item_id,8,' ')
1351 ||' - '||RPAD(line.item_name,42));
1352 END LOOP;
1353
1354 IF l_row_count = 0
1355 THEN
1356 EDW_LOG.Put_Line('');
1357 EDW_LOG.Put_Line(' THERE IS NO MISSING UOM CONVERSION RATE ');
1358 EDW_LOG.Put_Line('');
1359 END IF;
1360 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
1361 END; -- begin IF g_miss_conv > 0
1362 END IF; -- IF g_miss_conv > 0
1363
1364
1365
1366 -- ------------------------------------------------------
1367 -- We are cleaning the table containing the missing rates
1368 -- ------------------------------------------------------
1369
1370
1371 DELETE FROM ISC_EDW_BOOK_MISSING_RATE
1372 WHERE id = g_seq_id_line_1;
1373
1374 -- ----------------------------------------------
1375 -- No exception raised so far. Successful. Call
1376 -- Wrapup to commit and insert messages into logs
1377 -- ----------------------------------------------
1378
1379 EDW_LOG.Put_Line(' ');
1380 EDW_LOG.Put_Line('Inserted '||g_rows_collected||' rows into the local staging table');
1381 EDW_LOG.Put_Line(' ');
1382 EDW_LOG.Put_Line('+---------------------------------------------------------------------------+');
1383 EDW_LOG.Put_Line(' ');
1384 EDW_LOG.Put_Line('Start of the Wrapup section ');
1385 EDW_LOG.Put_Line(' ');
1386
1387
1388 IF retcode = -1
1389 THEN
1390 EDW_COLLECTION_UTIL.Wrapup(
1391 FALSE,
1392 g_rows_collected,
1393 NULL,
1394 ISC_EDW_BOOKINGS_F_C.G_Push_From_Date,
1395 ISC_EDW_BOOKINGS_F_C.G_Push_To_Date);
1396 ELSE
1397 EDW_COLLECTION_UTIL.Wrapup(
1398 TRUE,
1399 g_rows_collected,
1400 NULL,
1401 ISC_EDW_BOOKINGS_F_C.G_Push_From_Date,
1402 ISC_EDW_BOOKINGS_F_C.G_Push_To_Date);
1403 END IF;
1404
1405 COMMIT;
1406
1407
1408 -- --------------------------------------------------
1409 -- END OF Collection , Developer Customizable Section
1410 -- --------------------------------------------------
1411
1412 EXCEPTION
1413
1414 WHEN L_FAILURE THEN
1415 ROLLBACK;
1416 EDW_LOG.Put_Line(g_errbuf);
1417 retcode := -1;
1418 EDW_COLLECTION_UTIL.Wrapup(
1419 FALSE,
1420 g_rows_collected,
1421 NULL,
1422 ISC_EDW_BOOKINGS_F_C.G_Push_From_Date,
1423 ISC_EDW_BOOKINGS_F_C.G_Push_To_Date);
1424 WHEN OTHERS THEN
1425 ROLLBACK;
1426 g_errbuf := sqlerrm ||' - '|| sqlcode;
1427 EDW_LOG.Put_Line('Other errors : '|| g_errbuf);
1428 retcode := -1;
1429 EDW_COLLECTION_UTIL.Wrapup(
1430 FALSE,
1431 g_rows_collected,
1432 NULL,
1433 ISC_EDW_BOOKINGS_F_C.G_Push_From_Date,
1434 ISC_EDW_BOOKINGS_F_C.G_Push_To_Date);
1435 END;
1436 END ISC_EDW_BOOKINGS_F_C;