[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPI_RES_UTIL_F_C
Source
1 PACKAGE BODY OPI_EDW_OPI_RES_UTIL_F_C AS
2 /* $Header: OPIMRUTB.pls 120.1 2005/06/08 18:19:11 appldev $ */
3
4 g_push_from_date Date:=Null;
5 g_push_to_date Date:=Null;
6 g_row_count Number:=0;
7 g_exception_msg varchar2(2000):=Null;
8 g_errbuf VARCHAR2(2000):=NULL;
9 g_retcode VARCHAR2(200) :=NULL;
10 -----------------------------------------------------------
11 --FUNCTION PUSH_TO_LOCAL
12 -----------------------------------------------------------
13
14 FUNCTION PUSH_TO_LOCAL(p_from_date DATE,
15 p_to_date DATE ) RETURN NUMBER IS
16 BEGIN
17
18 -- ------------------------------------------------
19 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
20 -- In case of source=target, we need to separate
21 -- out the records in progress vs the records which
22 -- is ready to be picked up by collection enginee.
23 -- In our case, we consider the records to be in
24 -- progress until all the child processes have
25 -- completed successfully.
26 -- ------------------------------------------------
27
28 Insert Into opi_edw_res_util_fstg
29 (res_util_pk,
30 locator_fk,
31 res_fk,
32 trx_date_fk,
33 uom_fk,
34 instance_fk,
35 USER_FK1,USER_FK2,USER_FK3, USER_FK4, USER_FK5,
36 act_res_usage,
37 avail_res,
38 department,
39 trx_date,
40 USER_MEASURE1,USER_MEASURE2, USER_MEASURE3,
41 USER_MEASURE4, USER_MEASURE5,
42 last_update_date,
43 creation_date,
44 USER_ATTRIBUTE1,
45 USER_ATTRIBUTE2,
46 USER_ATTRIBUTE3,
47 USER_ATTRIBUTE4,
48 USER_ATTRIBUTE5,
49 USER_ATTRIBUTE6,
50 USER_ATTRIBUTE7,
51 USER_ATTRIBUTE8,
52 USER_ATTRIBUTE9,
53 USER_ATTRIBUTE10,
54 USER_ATTRIBUTE11,
55 USER_ATTRIBUTE12,
56 USER_ATTRIBUTE13,
57 USER_ATTRIBUTE14,
58 USER_ATTRIBUTE15,
59 collection_status)
60 SELECT /*+ ALL_ROWS */
61 Nvl(res_util_pk,'NA_EDW'),
62 Nvl(locator_fk,'NA_EDW'),
63 Nvl(res_fk,'NA_EDW'),
64 Nvl(trx_date_fk,'NA_EDW'),
65 Nvl(uom_fk,'NA_EDW'),
66 Nvl(instance_fk,'NA_EDW'),
67 NVL(USER_FK1,'NA_EDW'),
68 NVL(USER_FK2,'NA_EDW'),
69 NVL(USER_FK3,'NA_EDW'),
70 NVL(USER_FK4,'NA_EDW'),
71 NVL(USER_FK5,'NA_EDW'),
72 act_res_usage,
73 avail_res,
74 department,
75 trx_date,
76 USER_MEASURE1,
77 USER_MEASURE2,
78 USER_MEASURE3,
79 USER_MEASURE4,
80 USER_MEASURE5,
81 Sysdate,
82 Sysdate,
83 USER_ATTRIBUTE1,
84 USER_ATTRIBUTE2,
85 USER_ATTRIBUTE3,
86 USER_ATTRIBUTE4,
87 USER_ATTRIBUTE5,
88 USER_ATTRIBUTE6,
89 USER_ATTRIBUTE7,
90 USER_ATTRIBUTE8,
91 USER_ATTRIBUTE9,
92 USER_ATTRIBUTE10,
93 USER_ATTRIBUTE11,
94 USER_ATTRIBUTE12,
95 USER_ATTRIBUTE13,
96 USER_ATTRIBUTE14,
97 USER_ATTRIBUTE15,
98 'LOCAL READY'
99 FROM opi_edw_opi_res_util_fcv;
100
101 RETURN(sql%rowcount);
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 g_errbuf:=sqlerrm;
106 g_retcode:=sqlcode;
107 RETURN(-1);
108 END PUSH_TO_LOCAL;
109
110 -- ---------------------------------
111 -- PUBLIC PROCEDURES
112 -- ---------------------------------
113
114 -----------------------------------------------------------
115 -- PROCEDURE PUSH
116 -----------------------------------------------------------
117 PROCEDURE Push(Errbuf in out nocopy Varchar2,
118 Retcode in out nocopy Varchar2,
119 p_from_date IN varchar2,
120 p_to_date IN VARCHAR2 ) IS
121
122 l_fact_name VARCHAR2(30) :='OPI_EDW_RES_UTIL_F' ;
123 l_staging_table VARCHAR2(30) :='OPI_EDW_RES_UTIL_FSTG';
124 l_opi_schema VARCHAR2(30);
125 l_status VARCHAR2(30);
126 l_industry VARCHAR2(30);
127 l_exception_msg VARCHAR2(2000):=Null;
128
129 l_row_count NUMBER := 0;
130
131 l_push_local_failure EXCEPTION;
132
133 BEGIN
134 Errbuf :=NULL;
135 Retcode:=0;
136
137 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,
138 l_staging_table,
139 l_staging_table,
140 l_exception_msg)) THEN
141 errbuf := fnd_message.get;
142 Return;
143 END IF;
144
145 g_push_from_date := To_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
146 g_push_to_date := To_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
147
148
149 -- Start of code change for bug fix 2140267.
150 -- --------------------------------------------
151 -- Taking care of cases where the input from/to
152 -- date is NULL.
153 -- --------------------------------------------
154
155 g_push_from_date := nvl(g_push_from_date,
156 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
157 EDW_COLLECTION_UTIL.g_offset);
158 g_push_to_date := nvl(g_push_to_date,
159 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
160
161
162 -- End of code change for bug fix 2140267.
163
164
165
166
167
168 edw_log.put_line( 'The collection range is from '||
169 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
170 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
171 edw_log.put_line(' ');
172
173 -- --------------------------------------------------------
174 -- Delete all opi_edw_res_util_push_log records
175 -- --------------------------------------------------------
176 -- a). get schema name
177 IF fnd_installation.get_app_info( 'OPI', l_status,
178 l_industry, l_opi_schema) THEN
179 execute immediate 'truncate table '||l_opi_schema
180 ||'.opi_edw_res_util_push_log ';
181 END IF;
182
183 -- --------------------------------------------------------
184 -- . Pushing data to local push table
185 -- --------------------------------------------------------
186 edw_log.put_line(' ');
187 edw_log.put_line('Inserting into local push log table ');
188
189 opimxru.extract_opi_res_util(g_push_from_date, g_push_to_date);
190
191 -- --------------------------------------------------------
192 -- . Pushing data to local staging table
193 -- --------------------------------------------------------
194 edw_log.put_line(' ');
195 edw_log.put_line('Inserting into local staging table for view type 1');
196
197 l_row_count := push_to_local(p_from_date => g_push_from_date,
198 p_to_date => g_push_to_date );
199
200 -- --------------------------------------------
201 -- No exception raised so far. Call wrapup to transport
202 -- data to target database, and insert messages into logs
203 -- -----------------------------------------------
204 edw_log.put_line(' ');
205 edw_log.put_line('Inserted '||nvl(l_row_count,0)||
206 ' rows into the local staging table');
207 edw_log.put_line( 'The system time after insert is ' ||
208 to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') );
209 edw_log.put_line(' ');
210
211 EDW_COLLECTION_UTIL.wrapup(TRUE,
212 g_row_count,
213 l_exception_msg,
214 g_push_from_date,
215 g_push_to_date);
216
217 -- --------------------------------------------------------
218 -- Delete all opi_edw_res_util_push_log records
219 -- --------------------------------------------------------
220 -- a). get schema name
221 IF fnd_installation.get_app_info( 'OPI', l_status,
222 l_industry, l_opi_schema) THEN
223 execute immediate 'truncate table '||l_opi_schema
224 ||'.opi_edw_res_util_push_log ';
225 END IF;
226
227 -- ---------------------------------------------------------------------------
228 -- END OF Collection , Developer Customizable Section
229 -- ---------------------------------------------------------------------------
230
231 EXCEPTION
232 WHEN L_PUSH_LOCAL_FAILURE THEN
233 Errbuf:=g_errbuf;
234 Retcode:=g_retcode;
235 l_exception_msg := Retcode || ':' || Errbuf;
236 rollback; -- Rollback insert into local staging
237 edw_log.put_line('Inserting into local staging have failed');
238 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
239 g_push_from_date, g_push_to_date);
240 raise;
241
242 WHEN OTHERS THEN
243 Errbuf:= Sqlerrm;
244 Retcode:=sqlcode;
245 l_exception_msg := Retcode || ':' || Errbuf;
246 rollback;
247 edw_log.put_line('Other errors');
248 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
249 g_push_from_date, g_push_to_date);
250 raise;
251
252 END push;
253
254 END OPI_EDW_OPI_RES_UTIL_F_C;