[Home] [Help]
PACKAGE BODY: APPS.EDW_OPI_ACTV_M_C
Source
1 Package Body EDW_OPI_ACTV_M_C AS
2 /* $Header: OPIPACDB.pls 120.1 2005/06/07 02:34:08 appldev $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7
8
9 Procedure Push(Errbuf in out NOCOPY Varchar2,
10 Retcode in out NOCOPY Varchar2,
11 p_from_date IN VARCHAR2,
12 p_to_date IN VARCHAR2) IS
13 l_dimension_name Varchar2(30) :='EDW_OPI_ACTV_M' ;
14 l_temp_date Date:=Null;
15 l_rows_inserted Number:=0;
16 l_duration Number:=0;
17 l_exception_msg Varchar2(2000):=Null;
18 l_temp_date_char Varchar2(35);
19 -- -------------------------------------------
20 -- Put any additional developer variables here
21 -- -------------------------------------------
22 Begin
23 Errbuf :=NULL;
24 Retcode:=0;
25 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
26 errbuf := fnd_message.get;
27 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
28 Return;
29 END IF;
30
31 IF (p_from_date IS NULL) THEN
32 EDW_OPI_ACTV_M_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
33 EDW_COLLECTION_UTIL.g_offset;
34 ELSE
35 EDW_OPI_ACTV_M_C.g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
36 END IF;
37
38 IF (p_to_date IS NULL) THEN
39 EDW_OPI_ACTV_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
40 ELSE
41 /* Bug 2333020
42 IF to_char(to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD') = to_char(sysdate,'YYYY/MM/DD') THEN
43 EDW_OPI_ACTV_M_C.g_push_date_range2 := to_date(to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS');
44 ELSE
45 l_temp_date_char := to_char(to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD');
46 EDW_OPI_ACTV_M_C.g_push_date_range2 := to_date(l_temp_date_char||' 23:59:59', 'YYYY/MM/DD HH24:MI:SS');
47 END IF;
48 */
49 EDW_OPI_ACTV_M_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
50 END IF;
51
52
53 edw_log.put_line( 'The collection range is from '||
54 to_char(EDW_OPI_ACTV_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
55 to_char(EDW_OPI_ACTV_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
56 edw_log.put_line(' ');
57
58 -- -----------------------------------------------------------------------------
59 -- Start of Collection , Developer Customizable Section
60 -- -----------------------------------------------------------------------------
61
62 edw_log.put_line(' ');
63 edw_log.put_line('Pushing data');
64
65 l_temp_date := sysdate;
66
67
68 Push_EDW_OPI_ACTV_ACTV_LSTG(EDW_OPI_ACTV_M_C.g_push_date_range1, EDW_OPI_ACTV_M_C.g_push_date_range2);
69
70
71 l_duration := sysdate - l_temp_date;
72
73 edw_log.put_line('Total rows inserted : '||g_row_count);
74 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
75 edw_log.put_line(' ');
76 -- ---------------------------------------------------------------------------
77 -- END OF Collection , Developer Customizable Section
78 -- ---------------------------------------------------------------------------
79 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,
80 EDW_OPI_ACTV_M_C.g_exception_msg,
81 edw_opi_actv_m_c.g_push_date_range1,
82 EDW_OPI_ACTV_M_C.g_push_date_range2);
83 commit;
84
85 Exception When others then
86 Errbuf:=sqlerrm;
87 Retcode:=sqlcode;
88 l_exception_msg := Retcode || ':' || Errbuf;
89 EDW_OPI_ACTV_M_C.g_exception_msg := l_exception_msg;
90 rollback;
91 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_OPI_ACTV_M_C.g_exception_msg,
92 edw_opi_actv_m_c.g_push_date_range1,
93 EDW_OPI_ACTV_M_C.g_push_date_range2);
94
95 commit;
96 End;
97
98
99 Procedure Push_EDW_OPI_ACTV_ACTV_LSTG(p_from_date IN date, p_to_date IN DATE) IS
100 l_date1 DATE;
101 l_date2 DATE;
102 l_rows_inserted NUMBER :=0;
103 BEGIN
104 edw_log.put_line('Starting Push_EDW_OPI_ACTV_ACTV_LSTG');
105 l_date1 := p_from_date;
106 l_date2 := p_to_date;
107
108 INSERT INTO EDW_OPI_ACTV_ACTV_LSTG@EDW_APPS_TO_WH
109 (
110 ACTV_CODE,
111 ACTV_PK,
112 ACTV_DP,
113 ALL_FK,
114 NAME,
115 ACTV_NAME,
116 DESCRIPTION,
117 COST_ANALYSIS_CODE,
118 VALUE_ADDED,
119 USER_ATTRIBUTE1,
120 USER_ATTRIBUTE2,
121 USER_ATTRIBUTE3,
122 USER_ATTRIBUTE4,
123 USER_ATTRIBUTE5,
124 LAST_UPDATE_DATE,
125 CREATION_DATE,
126 COLLECTION_STATUS
127 )
128 select /*+ ALL_ROWS */
129 ACTV_CODE,
130 ACTV_PK,
131 ACTV_DP,
132 NVL(ALL_FK, 'NA_EDW'),
133 NAME,
134 ACTV_NAME,
135 DESCRIPTION,
136 COST_ANALYSIS_CODE,
137 VALUE_ADDED,
138 USER_ATTRIBUTE1,
139 USER_ATTRIBUTE2,
140 USER_ATTRIBUTE3,
141 USER_ATTRIBUTE4,
142 USER_ATTRIBUTE5,
143 LAST_UPDATE_DATE,
144 CREATION_DATE,
145 'READY'
146 FROM
147 EDW_OPI_ACTV_ACTV_LCV@APPS_TO_APPS
148 WHERE last_update_date between l_date1 and l_date2;
149
150
151 l_rows_inserted := sql%rowcount;
152 EDW_OPI_ACTV_M_C.g_row_count := EDW_OPI_ACTV_M_C.g_row_count + l_rows_inserted ;
153 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
154 ' rows into the staging table');
155 edw_log.put_line('Commiting records for EDW_OPI_ACTV_ACTV_LSTG');
156 commit;
157
158 edw_log.put_line('Completed Push_EDW_OPI_ACTV_ACTV_LSTG');
159 Exception When others then
160 raise;
161 commit;
162 END;
163 End EDW_OPI_ACTV_M_C;