DBA Data[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;