[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_COGS_F_C
Source
1 Package Body OPI_EDW_COGS_F_C as
2 /* $Header: OPIMCOGB.pls 120.1 2006/05/31 23:40:41 julzhang noship $ */
3 g_push_from_date Date:=Null;
4 g_push_to_date Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7 g_errbuf VARCHAR2(2000):=NULL;
8 g_retcode VARCHAR2(200) :=NULL;
9
10
11 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
12 IS
13
14 l_instance1 Varchar2(100) :=Null;
15 l_instance2 Varchar2(100) :=Null;
16
17 BEGIN
18
19
20 SELECT instance_code
21 INTO l_instance1
22 FROM edw_local_instance;
23
24 SELECT instance_code
25 INTO l_instance2
26 FROM edw_local_instance@edw_apps_to_wh;
27
28 IF (l_instance1 = l_instance2) THEN
29 RETURN TRUE;
30 END IF;
31
32 RETURN FALSE;
33
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 g_errbuf:=sqlerrm;
37 g_retcode:=sqlcode;
38
39 RETURN FALSE;
40
41 END;
42
43 ---------------------------------------------------
44 -- FUNCTION IDENTIFY_CHANGE by checking last_update_date
45 ---------------------------------------------------
46 /*--------------------------------------------------------------+
47 | Date: 03-Nov-2003
48 | Developer: ADWAJAN
49 | Comments: Additional condition in the where clause to
50 | calculate COGS for the logical txns in the
51 | Drop Ship scenario - 11.5.10 Impact Analysis
52 +-------------------------------------------------------------*/
53
54 FUNCTION IDENTIFY_CHANGE( p_view_id IN NUMBER,
55 p_count OUT NOCOPY NUMBER) RETURN NUMBER
56 IS
57
58 l_seq_id NUMBER := -1;
59 l_opi_schema VARCHAR2(30);
60 l_status VARCHAR2(30);
61 l_industry VARCHAR2(30);
62 BEGIN
63
64 p_count := 0;
65
66 SELECT opi_edw_cogs_inc_s.NEXTVAL INTO l_seq_id FROM dual;
67
68 IF p_view_id = 1 THEN
69 INSERT
70 INTO opi_edw_cogs_inc(primary_key1, seq_id, view_id)
71 SELECT /*+ parallel(mmt) */
72 DISTINCT mmt.transaction_id, l_seq_id, 1
73 FROM
74 oe_order_headers_all h,
75 oe_order_lines_all pl,
76 oe_order_lines_all l,
77 mtl_transaction_accounts mta,
78 mtl_material_transactions mmt
79 where ( (mmt.transaction_source_type_id = 2
80 and mta.transaction_source_type_id = 2)
81 or
82 (mmt.transaction_source_type_id = 13
83 and mmt.transaction_action_id = 9
84 and mta.transaction_source_type_id = 13)
85 )
86 and mmt.transaction_id = mta.transaction_id
87 and mta.accounting_line_type in (2, 35)
88 and pl.org_id = l.org_id
89 and h.org_id = l.org_id
90 and l.line_id = mmt.trx_source_line_id
91 and l.line_category_code = 'ORDER'
92 and pl.line_category_code = 'ORDER'
93 and pl.line_id = nvl(l.top_model_line_id, l.line_id)
94 and h.header_id = l.header_id
95 and h.header_id = pl.header_id
96 AND greatest(
97 nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
98 nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
99 nvl(mta.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
100 nvl(mmt.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
101 nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
102 BETWEEN g_push_from_date and g_push_to_date
103 UNION
104 SELECT primary_key1, l_seq_id, 1
105 FROM opi_edw_cogs_inc
106 WHERE view_id =1;
107
108 ELSIF p_view_id = 2 THEN
109 INSERT
110 INTO opi_edw_cogs_inc(primary_key1, seq_id, view_id)
111 SELECT /*+ parallel(mmt) */
112 DISTINCT mmt.transaction_id, l_seq_id, 2
113 FROM
114 oe_order_headers_all h,
115 oe_order_lines_all pl,
116 oe_order_lines_all cl,
117 oe_order_lines_all l,
118 mtl_transaction_accounts mta,
119 mtl_material_transactions mmt
120 where ( (mmt.transaction_source_type_id = 12
121 and mta.transaction_source_type_id = 12)
122 or
123 (mmt.transaction_source_type_id = 13
124 and mmt.transaction_action_id = 14
125 and mta.transaction_source_type_id = 13)
126 )
127 and mmt.transaction_id = mta.transaction_id
128 and mta.accounting_line_type in (2, 35)
129 and h.org_id = l.org_id
130 and l.line_id = mmt.trx_source_line_id
131 and l.line_category_code = 'RETURN'
132 and cl.line_id (+) = l.link_to_line_id
133 and pl.line_id (+) = nvl(cl.top_model_line_id, cl.line_id)
134 and h.header_id = l.header_id
135 AND greatest(
136 nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
137 nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
138 nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
139 nvl(mta.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
140 nvl(mmt.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
141 nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
142 BETWEEN g_push_from_date and g_push_to_date
143 UNION
144 SELECT primary_key1, l_seq_id, 2
145 FROM opi_edw_cogs_inc
146 WHERE view_id =2;
147 ELSIF p_view_id = 3 THEN
148 INSERT
149 INTO opi_edw_cogs_inc(primary_key1, primary_key2, seq_id, view_id)
150 SELECT /*+ parallel(aid) */
151 DISTINCT aid.invoice_id,
152 aid.distribution_line_number,
153 l_seq_id, 3
154 FROM
155 oe_order_headers_all h,
156 oe_order_lines_all pl, /* parent line */
157 oe_order_lines_all l, /* child line */
158 ra_customer_trx_lines_all rcl,
159 ap_invoice_distributions_all aid,
160 ap_invoices_all ai,
161 mtl_material_transactions mmt,
162 mtl_parameters mp
163 WHERE ai.source = 'Intercompany'
164 AND aid.invoice_id = ai.invoice_id
165 and translate( lower(aid.REFERENCE_1), 'abcdefghijklmnopqrstuvwxyz_ -+0123456789',
166 'abcdefghijklmnopqrstuvwxyz_ -+') is null
167 and aid.org_id = ai.org_id
168 and rcl.CUSTOMER_TRX_LINE_ID = to_number(aid.REFERENCE_1)
169 and aid.line_type_lookup_code = 'ITEM'
170 and rcl.interface_line_attribute6 = l.line_id
171 and pl.line_id = nvl(l.top_model_line_id, l.line_id)
172 and pl.org_id = l.org_id
173 and h.org_id = l.org_id
174 and h.header_id = l.header_id
175 and h.header_id = pl.header_id
176 and l.line_category_code = 'ORDER'
177 and pl.line_category_code = 'ORDER'
178 and rcl.interface_line_attribute7 = mmt.transaction_id
179 and nvl(mmt.logical_transaction,0) <> 1
180 and mmt.organization_id = mp.organization_id
181 and mp.process_enabled_flag <> 'Y'
182 AND greatest(
183 nvl(aid.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
184 nvl(ai.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
185 nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
186 nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
187 nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
188 BETWEEN g_push_from_date and g_push_to_date
189 UNION
190 SELECT primary_key1, primary_key2, l_seq_id, 3
191 FROM opi_edw_cogs_inc
192 WHERE view_id =3;
193 END IF;
194
195 p_count := SQL%rowcount;
196
197 DELETE opi_edw_cogs_inc WHERE view_id = p_view_id AND seq_id <> l_seq_id;
198
199 COMMIT;
200 --dbms_output.put_line('Identified '|| p_count || ' changed records in view type '|| p_view_id);
201 RETURN(l_seq_id);
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 g_errbuf:=sqlerrm;
206 g_retcode:=sqlcode;
207 RETURN(-1);
208 END identify_change;
209
210 -----------------------------------------------------------
211 --FUNCTION PUSH_TO_LOCAL
212 -----------------------------------------------------------
213
214 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER,
215 p_seq_id NUMBER) RETURN NUMBER IS
216 BEGIN
217
218 -- ------------------------------------------------
219 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
220 -- In case of source=target, we need to separate
221 -- out the records in progress vs the records which
222 -- is ready to be picked up by collection enginee.
223 -- In our case, we consider the records to be in
224 -- progress until all the child processes have
225 -- completed successfully.
226 -- ------------------------------------------------
227
228 Insert Into opi_edw_cogs_fstg
229 (
230 ACCOUNT,
231 BASE_CURRENCY_FK,
232 BASE_UOM_FK,
233 BILL_TO_LOC_FK,
234 BILL_TO_SITE_FK,
235 CAMPAIGN_ACTL_FK,
236 CAMPAIGN_INIT_FK,
237 campaign_status_actl_fk,
238 campaign_status_init_fk,
239 COGS_B,
240 cogs_date,
241 cogs_date_fk,
242 COGS_G,
243 COGS_PK,
244 COGS_T,
245 COST_ELEMENT,
246 CUSTOMER_FK,
247 DATE_BOOKED_FK,
248 DATE_PROMISED_FK,
249 DATE_REQUESTED_FK,
250 DATE_SCHEDULED_FK,
251 DATE_SHIPPED_FK,
252 ICAP_QTY_B,
253 INSTANCE_FK,
254 INV_ORG_FK,
255 ITEM_ORG_FK,
256 LOCATOR_FK,
257 LOT,
258 MARKET_SEGMENT_FK,
259 MEDCHN_ACTL_FK,
260 MEDCHN_INIT_FK,
261 MONTH_BOOKED_FK,
262 OFFER_HDR_FK,
263 OFFER_LINE_FK,
264 OPERATING_UNIT_FK,
265 ORDER_CATEGORY_FK,
266 order_date,
267 ORDER_LEAD_TIME,
268 order_line_id,
269 ORDER_NUMBER,
270 ORDER_SOURCE_FK,
271 ORDER_TYPE_FK,
272 PRIM_SALES_REP_FK,
273 prim_salesresource_fk,
274 PROJECT_FK,
275 PROMISE_LEAD_TIME,
276 PROM_EARLY_COUNT,
277 PROM_EARLY_VAL_G,
278 PROM_LATE_COUNT,
279 PROM_LATE_VAL_G,
280 REQUEST_LEAD_TIME,
281 REQ_EARLY_COUNT,
282 REQ_EARLY_VAL_G,
283 REQ_LATE_COUNT,
284 REQ_LATE_VAL_G,
285 REVISION,
286 RMA_QTY_B,
287 RMA_VAL_G,
288 RMA_VAL_T,
289 SALES_CHANNEL_FK,
290 SERIAL_NUMBER,
291 SET_OF_BOOKS_FK,
292 ship_inv_locator_fk,
293 SHIPPED_QTY_B,
294 SHIP_TO_LOC_FK,
295 SHIP_TO_SITE_FK,
296 TARGET_SEGMENT_ACTL_FK,
297 TARGET_SEGMENT_INIT_FK,
298 TASK_FK,
299 TOP_MODEL_ITEM_FK,
300 TRX_CURRENCY_FK,
301 USER_ATTRIBUTE1,
302 USER_ATTRIBUTE10,
303 USER_ATTRIBUTE11,
304 USER_ATTRIBUTE12,
305 USER_ATTRIBUTE13,
306 USER_ATTRIBUTE14,
307 USER_ATTRIBUTE15,
308 USER_ATTRIBUTE2,
309 USER_ATTRIBUTE3,
310 USER_ATTRIBUTE4,
311 USER_ATTRIBUTE5,
312 USER_ATTRIBUTE6,
313 USER_ATTRIBUTE7,
314 USER_ATTRIBUTE8,
315 USER_ATTRIBUTE9,
316 USER_FK1,
317 USER_FK2,
318 USER_FK3,
319 USER_FK4,
320 USER_FK5,
321 USER_MEASURE1,
322 USER_MEASURE2,
323 USER_MEASURE3,
324 USER_MEASURE4,
325 USER_MEASURE5,
326 WAYBILL_NUMBER,
327 OPERATION_CODE,
328 collection_status,
329 creation_date,
330 last_update_date
331 ,delivery_id )
332 SELECT /*+ ALL_ROWS */
333 ACCOUNT,
334 NVL(BASE_CURRENCY_FK,'NA_EDW'),
335 NVL(BASE_UOM_FK,'NA_EDW'),
336 NVL(BILL_TO_LOC_FK,'NA_EDW'),
337 NVL(BILL_TO_SITE_FK,'NA_EDW'),
338 NVL(CAMPAIGN_ACTL_FK,'NA_EDW'),
339 NVL(CAMPAIGN_INIT_FK,'NA_EDW'),
340 Nvl(campaign_status_actl_fk, 'NA_EDW'),
341 Nvl(campaign_status_init_fk, 'NA_EDW'),
342 COGS_B,
343 cogs_date,
344 cogs_date_fk,
345 global_currency_rate* cogs_b cogs_g,
346 COGS_PK,
347 COGS_T,
348 COST_ELEMENT,
349 NVL(CUSTOMER_FK,'NA_EDW'),
350 DATE_BOOKED_FK,
351 DATE_PROMISED_FK,
352 DATE_REQUESTED_FK,
353 DATE_SCHEDULED_FK,
354 DATE_SHIPPED_FK,
355 ICAP_QTY_B,
356 NVL(INSTANCE_FK,'NA_EDW'),
357 NVL(INV_ORG_FK,'NA_EDW'),
358 NVL(ITEM_ORG_FK,'NA_EDW'),
359 NVL(LOCATOR_FK,'NA_EDW'),
360 LOT,
361 NVL(MARKET_SEGMENT_FK,'NA_EDW'),
362 NVL(MEDCHN_ACTL_FK,'NA_EDW'),
363 NVL(MEDCHN_INIT_FK,'NA_EDW'),
364 MONTH_BOOKED_FK,
365 NVL(OFFER_HDR_FK,'NA_EDW'),
366 NVL(OFFER_LINE_FK,'NA_EDW'),
367 NVL(OPERATING_UNIT_FK,'NA_EDW'),
368 NVL(ORDER_CATEGORY_FK,'NA_EDW'),
369 order_date,
370 ORDER_LEAD_TIME,
371 order_line_id,
372 ORDER_NUMBER,
373 NVL(ORDER_SOURCE_FK,'NA_EDW'),
374 NVL(ORDER_TYPE_FK,'NA_EDW'),
375 NVL(PRIM_SALES_REP_FK,'NA_EDW'),
376 Nvl(prim_salesresource_fk, 'NA_EDW'),
377 NVL(PROJECT_FK,'NA_EDW'),
378 PROMISE_LEAD_TIME,
379 PROM_EARLY_COUNT,
380 prom_early_val_g * global_currency_rate,
381 PROM_LATE_COUNT,
382 prom_late_val_g * global_currency_rate,
383 REQUEST_LEAD_TIME,
384 REQ_EARLY_COUNT,
385 req_early_val_g * global_currency_rate,
386 REQ_LATE_COUNT,
387 req_late_val_g * global_currency_rate,
388 REVISION,
389 RMA_QTY_B,
390 rma_val_t * global_currency_rate rma_val_g,
391 RMA_VAL_T,
392 NVL(SALES_CHANNEL_FK,'NA_EDW'),
393 SERIAL_NUMBER,
394 NVL(SET_OF_BOOKS_FK,'NA_EDW'),
395 Nvl(ship_inv_locator_fk,'NA_EDW'),
396 SHIPPED_QTY_B,
397 NVL(SHIP_TO_LOC_FK,'NA_EDW'),
398 NVL(SHIP_TO_SITE_FK,'NA_EDW'),
399 NVL(TARGET_SEGMENT_ACTL_FK,'NA_EDW'),
400 NVL(TARGET_SEGMENT_INIT_FK,'NA_EDW'),
401 NVL(TASK_FK,'NA_EDW'),
402 NVL(TOP_MODEL_ITEM_FK,'NA_EDW'),
403 NVL(TRX_CURRENCY_FK,'NA_EDW'),
404 USER_ATTRIBUTE1,
405 USER_ATTRIBUTE10,
406 USER_ATTRIBUTE11,
407 USER_ATTRIBUTE12,
408 USER_ATTRIBUTE13,
409 USER_ATTRIBUTE14,
410 USER_ATTRIBUTE15,
411 USER_ATTRIBUTE2,
412 USER_ATTRIBUTE3,
413 USER_ATTRIBUTE4,
414 USER_ATTRIBUTE5,
415 USER_ATTRIBUTE6,
416 USER_ATTRIBUTE7,
417 USER_ATTRIBUTE8,
418 USER_ATTRIBUTE9,
419 NVL(USER_FK1,'NA_EDW'),
420 NVL(USER_FK2,'NA_EDW'),
421 NVL(USER_FK3,'NA_EDW'),
422 NVL(USER_FK4,'NA_EDW'),
423 NVL(USER_FK5,'NA_EDW'),
424 USER_MEASURE1,
425 USER_MEASURE2,
426 USER_MEASURE3,
427 USER_MEASURE4,
428 USER_MEASURE5,
429 WAYBILL_NUMBER,
430 NULL, -- OPERATION_CODE
431 Decode( global_currency_rate,
432 -1, 'RATE NOT AVAILABLE',
433 -2, 'INVALID CURRENCY',
434 'LOCAL READY'),
435 Sysdate,
436 Sysdate
437 ,delivery_id
438 FROM opi_edw_cogs_fcv
439 WHERE view_id = p_view_id
440 AND seq_id = p_seq_id;
441
442 --dbms_output.put_line('Inserted ' || Nvl(SQL%rowcount,0) ||' rows into local staging table for view type ' || p_view_id || ' with seq_id ' || p_seq_id);
443 RETURN(sql%rowcount);
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 g_errbuf:=sqlerrm;
448 g_retcode:=sqlcode;
449 RETURN(-1);
450 END PUSH_TO_LOCAL;
451
452
453 -- ---------------------------------
454 -- PUBLIC PROCEDURES
455 -- ---------------------------------
456
457 -----------------------------------------------------------
458 -- PROCEDURE PUSH
459 -----------------------------------------------------------
460 PROCEDURE Push(Errbuf in out NOCOPY Varchar2,
461 Retcode in out NOCOPY Varchar2,
462 p_from_date IN varchar2,
463 p_to_date IN varchar2) IS
464
465 l_fact_name VARCHAR2(30) :='OPI_EDW_COGS_F' ;
466 l_staging_table VARCHAR2(30) :='OPI_EDW_COGS_FSTG';
467 l_opi_schema VARCHAR2(30);
468 l_status VARCHAR2(30);
469 l_industry VARCHAR2(30);
470 l_exception_msg VARCHAR2(2000):=Null;
471
472 l_seq_id_view1 NUMBER := 0;
473 l_seq_id_view2 NUMBER := 0;
474 l_seq_id_view3 NUMBER := 0;
475 l_row_count_view1 NUMBER := 0;
476 l_row_count_view2 NUMBER := 0;
477 l_row_count_view3 NUMBER := 0;
478 l_row_count NUMBER := 0;
479
480 l_push_local_failure EXCEPTION;
481 l_iden_change_failure EXCEPTION;
482
483 l_missing_rate_count NUMBER :=0;
484 currency_conv_rate_not_exist EXCEPTION;
485
486 CURSOR missing_rate_csr IS
487 SELECT DISTINCT
488 base_currency_fk from_currency,
489 Substr(cogs_date_fk, 1,10) c_date,
490 collection_status
491 FROM opi_edw_cogs_fstg
492 WHERE collection_status IN ('RATE NOT AVAILABLE','INVALID CURRENCY')
493 AND Substr(cogs_pk,0,3) <> 'OPM'
494 ORDER BY from_currency, c_date;
495
496 /*
497 l_date1 Date:=Null;
498 l_date2 Date:=Null;
499 l_temp_date Date:=Null;
500 l_rows_inserted Number:=0;
501 l_duration Number:=0;
502 */
503
504
505 -- -------------------------------------------
506 -- Put any additional developer variables here
507 -- -------------------------------------------
508 BEGIN
509 Errbuf :=NULL;
510 Retcode:=0;
511
512
513 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,
514 l_staging_table,
515 l_staging_table,
516 l_exception_msg)) THEN
517 errbuf := fnd_message.get;
518 Return;
519 END IF;
520
521 g_push_from_date := To_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
522 g_push_to_date := To_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
523
524
525
526
527
528 -- Start of code change for bug fix 2140267.
529 -- --------------------------------------------
530 -- Taking care of cases where the input from/to
531 -- date is NULL.
532 -- --------------------------------------------
533
534 g_push_from_date := nvl(g_push_from_date,
535 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
536 EDW_COLLECTION_UTIL.g_offset);
537 g_push_to_date := nvl(g_push_to_date,
538 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
539
540
541 -- End of code change for bug fix 2140267.
542
543
544
545
546
547
548
549
550
551 edw_log.put_line( 'The collection range is from '||
552 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
553 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
554 edw_log.put_line(' ');
555
556 -- --------------------------------------------------------
557 -- Identify Change for View Type 1
558 -- --------------------------------------------------------
559 edw_log.put_line(' ');
560 edw_log.put_line('Identifying change in view type 1');
561
562 l_row_count := 0;
563 l_seq_id_view1 := identify_change( p_view_id => 1,
564 p_count => l_row_count );
565 IF (l_seq_id_view1 = -1 ) THEN
566 RAISE l_iden_change_failure;
567 END IF;
568
569 edw_log.put_line('Identified '|| l_row_count
570 || ' changed records in view type 1. ');
571 -- --------------------------------------------------------
572 -- Identify Change for View Type 2
573 -- --------------------------------------------------------
574 edw_log.put_line(' ');
575 edw_log.put_line('Identifying change in view type 2');
576 l_row_count := 0;
577 l_seq_id_view2 := identify_change( p_view_id => 2,
578 p_count => l_row_count );
579 IF (l_seq_id_view2 = -1 ) THEN
580 RAISE l_iden_change_failure;
581 END IF;
582
583 edw_log.put_line('Identified '|| l_row_count
584 || ' changed records in view type 2. ');
585
586 -- --------------------------------------------------------
587 -- Identify Change for View Type 3
588 -- --------------------------------------------------------
589 edw_log.put_line(' ');
590 edw_log.put_line('Identifying change in view type 3');
591 l_row_count := 0;
592 l_seq_id_view3 := identify_change( p_view_id => 3,
593 p_count => l_row_count );
594 IF (l_seq_id_view3 = -1 ) THEN
595 RAISE l_iden_change_failure;
596 END IF;
597
598 edw_log.put_line('Identified '|| l_row_count
599 || ' changed records in view type 3. ');
600
601 --RAISE l_iden_change_failure;
602 -- --------------------------------------------------------
603 -- Analyze the incremental table
604 -- --------------------------------------------------------
605 IF fnd_installation.get_app_info( 'OPI', l_status,
606 l_industry, l_opi_schema) THEN
607 fnd_stats.gather_table_stats(ownname=> l_opi_schema,
608 tabname=> 'OPI_EDW_COGS_INC' );
609 END IF;
610
611 -- --------------------------------------------------------
612 -- . Pushing data to local staging table
613 -- --------------------------------------------------------
614 edw_log.put_line(' ');
615 edw_log.put_line('Inserting into local staging table for view type 1');
616
617 l_row_count_view1 := push_to_local( p_view_id => 1,
618 p_seq_id => l_seq_id_view1 );
619 IF l_row_count_view1 = -1 THEN
620 RAISE l_push_local_failure;
621 END IF;
622
623 edw_log.put_line('Inserted ' || Nvl(l_row_count_view1,0) ||
624 ' rows into local staging table for view type 1');
625 edw_log.put_line(' ');
626
627 --
628 edw_log.put_line(' ');
629 edw_log.put_line('Inserting into local staging table for view type 2');
630
631 l_row_count_view2 := push_to_local( p_view_id => 2,
632 p_seq_id => l_seq_id_view2 );
633 IF l_row_count_view2 = -1 THEN
634 RAISE l_push_local_failure;
635 END IF;
636 edw_log.put_line('Inserted ' || Nvl(l_row_count_view2,0) ||
637 ' rows into local staging table for view type 2');
638 edw_log.put_line(' ');
639
640
641 --
642 edw_log.put_line(' ');
643 edw_log.put_line('Inserting into local staging table for view type 3');
644
645 l_row_count_view3 := push_to_local( p_view_id => 3,
646 p_seq_id => l_seq_id_view3 );
647 IF l_row_count_view3 = -1 THEN
648 RAISE l_push_local_failure;
649 END IF;
650 edw_log.put_line('Inserted ' || Nvl(l_row_count_view3,0) ||
651 ' rows into local staging table for view type 3');
652 edw_log.put_line(' ');
653
654 --
655 g_row_count := l_row_count_view1 + l_row_count_view2 + l_row_count_view3;
656
657 edw_log.put_line('For all view types, inserted ' || Nvl(g_row_count,0)
658 || ' rows into local staging table.');
659 edw_log.put_line(' ');
660
661
662
663 -- --------------------------------------------------------
664 -- Delete all incremental table's record
665 -- --------------------------------------------------------
666
667 execute immediate 'truncate table '||l_opi_schema||'.opi_edw_cogs_inc ';
668
669 -- --------------------------------------------------------
670 -- insert missing rate/invalid currency into incremental table
671 -- --------------------------------------------------------
672 INSERT INTO opi_edw_cogs_inc(view_id, primary_key1, primary_key2 )
673 SELECT Decode(Substr(cogs_pk,0,3), 'INV', 1, 'RMA', 2, 'ICI', 3 ) view_id,
674 Decode(Substr(cogs_pk,0,3),
675 'INV', Substr(cogs_pk,5,Instr(cogs_pk,'-',1,2) -5),
676 'RMA', Substr(cogs_pk,5,Instr(cogs_pk,'-',1,2) -5),
677 'ICI', Substr(cogs_pk,Instr(cogs_pk, '-',1,2)+1,
678 Instr(cogs_pk,'-', 1, 3)- Instr(cogs_pk, '-',1,2)-1)
679 ) primary_key1,
680 Decode(Substr(cogs_pk,0,3), 'INV', NULL, 'RMA', NULL,
681 'ICI', Substr(cogs_pk,5,Instr(cogs_pk,'-',1,2) -5)) primary_key2
682 FROM opi_edw_cogs_fstg
683 WHERE collection_status IN ('RATE NOT AVAILABLE','INVALID CURRENCY')
684 AND Substr(cogs_pk,0,3) <> 'OPM'
685 ;
686
687 l_missing_rate_count := SQL%rowcount;
688
689 COMMIT;
690
691 -- --------------------------------------------------------
692 -- report missing rate/invalid currency
693 -- --------------------------------------------------------
694
695 IF l_missing_rate_count > 0 THEN
696 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FROM CURRENCY CONVERSION DATE COLLECTION STATUS');
697 FOR ms_rate IN missing_rate_csr LOOP
698 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, Rpad(ms_rate.from_currency,16,' ')||
699 Rpad(ms_rate.c_date,19, ' ')|| ms_rate.collection_status );
700 END LOOP;
701 END IF;
702
703 -- --------------------------------------------------------
704 -- if on single instance, delete records with
705 -- 'RATE NOT AVAILABLE','INVALID CURRENCY' from fstg
706 -- --------------------------------------------------------
707 IF local_same_as_remote THEN
708 DELETE opi_edw_cogs_fstg
709 WHERE collection_status IN ('RATE NOT AVAILABLE','INVALID CURRENCY')
710 AND Substr(cogs_pk,0,3) <> 'OPM';
711 END IF;
712
713
714
715 -- --------------------------------------------
716 -- No exception raised so far. Call wrapup to transport
717 -- data to target database, and insert messages into logs
718 -- -----------------------------------------------
719 edw_log.put_line(' ');
720 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
721 ' rows into the staging table');
722 edw_log.put_line(' ');
723
724 EDW_COLLECTION_UTIL.wrapup(TRUE,
725 g_row_count,
726 l_exception_msg,
727 g_push_from_date,
728 g_push_to_date);
729
730
731 IF l_missing_rate_count > 0 THEN
732 RAISE currency_conv_rate_not_exist;
733 END IF;
734 --dbms_output.put_line( 'l_opi_schema after wrapup true ' || l_opi_schema);
735
736
737 -- ---------------------------------------------------------------------------
738 -- END OF Collection , Developer Customizable Section
739 -- ---------------------------------------------------------------------------
740
741 EXCEPTION
742 WHEN currency_conv_rate_not_exist THEN
743 Errbuf:= 'No conversion rate existed. Please check log file for details.';
744
745 Retcode:= 1; -- completed with warning
746 l_exception_msg := Retcode || ':' || Errbuf;
747 rollback;
748 edw_log.put_line( l_exception_msg);
749 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
750 g_push_from_date, g_push_to_date);
751
752 WHEN L_PUSH_LOCAL_FAILURE THEN
753 Errbuf:=g_errbuf;
754 Retcode:=g_retcode;
755 l_exception_msg := Retcode || ':' || Errbuf;
756 rollback; -- Rollback insert into local staging
757 edw_log.put_line('Inserting into local staging have failed');
758 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
759 g_push_from_date, g_push_to_date);
760 raise;
761
762 WHEN L_IDEN_CHANGE_FAILURE THEN
763 Errbuf:=g_errbuf;
764 Retcode:=g_retcode;
765 l_exception_msg := Retcode || ':' || Errbuf;
766
767 IF fnd_installation.get_app_info( 'OPI', l_status,
768 l_industry, l_opi_schema) THEN
769 execute immediate 'truncate table ' || l_opi_schema
770 || '.opi_edw_cogs_inc ';
771 END IF;
772 edw_log.put_line('Identifying changed records have Failed');
773 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
774 g_push_from_date, g_push_to_date);
775 raise;
776
777 WHEN OTHERS THEN
778 Errbuf:= Sqlerrm;
779 Retcode:=sqlcode;
780 l_exception_msg := Retcode || ':' || Errbuf;
781 rollback;
782 edw_log.put_line('Other errors');
783 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
784 g_push_from_date, g_push_to_date);
785 raise;
786
787 END push;
788
789 End OPI_EDW_COGS_F_C;