[Home] [Help]
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)
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,
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
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,
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)
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
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;
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);
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
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: -- --------------------------------------------------------
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),
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);