[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_CSTM_MSR_F_C
Source
1 Package Body POA_EDW_CSTM_MSR_F_C AS
2 /* $Header: poafpcmb.pls 120.1 2005/06/13 12:56:21 sriswami noship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6
7 g_errbuf VARCHAR2(2000) := NULL;
8 g_retcode VARCHAR2(200) := NULL;
9
10 -- ---------------------------------
11 -- PRIVATE PROCEDURES AND FUNCTIONS
12 -- ---------------------------------
13
14 -----------------------------------------------------------
15 -- PROCEDURE TRUNCATE_INC
16 -----------------------------------------------------------
17
18 PROCEDURE TRUNCATE_INC IS
19
20 l_poa_schema VARCHAR2(30);
21 l_stmt VARCHAR2(200);
22 l_status VARCHAR2(30);
23 l_industry VARCHAR2(30);
24
25 BEGIN
26
27 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
28 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_EDW_CSTM_MSR_INC';
29 EXECUTE IMMEDIATE l_stmt;
30 END IF;
31
32 END;
33
34 -----------------------------------------------------------
35 --PROCEDURE PUSH_TO_LOCAL
36 -----------------------------------------------------------
37
38 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER, p_seq_id NUMBER) RETURN NUMBER IS
39
40 l_duration NUMBER;
41 l_temp_date DATE:=NULL;
42 l_rows_inserted Number:=0;
43
44 BEGIN
45
46 -- ------------------------------------------------
47 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
48 -- In case of source=target, we need to separate
49 -- out the records in progress vs the records which
50 -- is ready to be picked up by collection enginee.
51 -- In our case, we consider the records to be in
52 -- progress until the push_to_local procedure for
53 -- all view types has completed successfully.
54 -- ------------------------------------------------
55
56 edw_log.put_line(' ');
57 edw_log.put_line('Pushing data to local staging table...');
58
59 l_temp_date := sysdate;
60 Insert Into POA_EDW_CSTM_MSR_FSTG (
61 DUNS_FK,
62 UNSPSC_FK,
63 SIC_CODE_FK,
64 CRITERIA_CODE_FK,
65 CSTM_MSR_PK,
66 CUSTOM_MEASURE_FK,
67 EVAL_COMMENTS,
68 EVAL_DATE_FK,
69 INSTANCE_FK,
70 ITEM_FK,
71 MAX_SCORE,
72 MIN_SCORE,
73 OPERATING_UNIT_FK,
74 SCORE,
75 SCORE_COMMENTS,
76 SUPPLIER_SITE_FK,
77 USER_ATTRIBUTE1,
78 USER_ATTRIBUTE2,
79 USER_ATTRIBUTE3,
80 USER_ATTRIBUTE4,
81 USER_ATTRIBUTE5,
82 USER_ATTRIBUTE6,
83 USER_ATTRIBUTE7,
84 USER_ATTRIBUTE8,
85 USER_ATTRIBUTE9,
86 USER_ATTRIBUTE10,
87 USER_ATTRIBUTE11,
88 USER_ATTRIBUTE12,
89 USER_ATTRIBUTE13,
90 USER_ATTRIBUTE14,
91 USER_ATTRIBUTE15,
92 USER_FK1,
93 USER_FK2,
94 USER_FK3,
95 USER_FK4,
96 USER_FK5,
97 USER_MEASURE1,
98 USER_MEASURE2,
99 USER_MEASURE3,
100 USER_MEASURE4,
101 USER_MEASURE5,
102 USER_NAME,
103 WEIGHT,
104 WEIGHTED_SCORE,
105 OPERATION_CODE,
106 COLLECTION_STATUS,
107 EVALUATION_ID)
108 select
109 NVL(DUNS_FK, 'NA_EDW'),
110 NVL(UNSPSC_FK, 'NA_EDW'),
111 NVL(SIC_CODE_FK, 'NA_EDW'),
112 NVL(CRITERIA_CODE_FK,'NA_EDW'),
113 CSTM_MSR_PK,
114 NVL(CUSTOM_MEASURE_FK,'NA_EDW'),
115 EVAL_COMMENTS,
116 NVL(EVAL_DATE_FK,'NA_EDW'),
117 NVL(INSTANCE_FK,'NA_EDW'),
118 NVL(ITEM_FK,'NA_EDW'),
119 MAX_SCORE,
120 MIN_SCORE,
121 NVL(OPERATING_UNIT_FK,'NA_EDW'),
122 SCORE,
123 SCORE_COMMENTS,
124 NVL(SUPPLIER_SITE_FK,'NA_EDW'),
125 USER_ATTRIBUTE1,
126 USER_ATTRIBUTE2,
127 USER_ATTRIBUTE3,
128 USER_ATTRIBUTE4,
129 USER_ATTRIBUTE5,
130 USER_ATTRIBUTE6,
131 USER_ATTRIBUTE7,
132 USER_ATTRIBUTE8,
133 USER_ATTRIBUTE9,
134 USER_ATTRIBUTE10,
135 USER_ATTRIBUTE11,
136 USER_ATTRIBUTE12,
137 USER_ATTRIBUTE13,
138 USER_ATTRIBUTE14,
139 USER_ATTRIBUTE15,
140 NVL(USER_FK1,'NA_EDW'),
141 NVL(USER_FK2,'NA_EDW'),
142 NVL(USER_FK3,'NA_EDW'),
143 NVL(USER_FK4,'NA_EDW'),
144 NVL(USER_FK5,'NA_EDW'),
145 USER_MEASURE1,
146 USER_MEASURE2,
147 USER_MEASURE3,
148 USER_MEASURE4,
149 USER_MEASURE5,
150 USER_NAME,
151 WEIGHT,
152 WEIGHTED_SCORE,
153 NULL, -- OPERATION_CODE
154 'LOCAL READY',
155 EVALUATION_ID
156 from POA_EDW_CUSTOM_MEASURE_FCV
157 WHERE view_id = p_view_id
158 AND seq_id = p_seq_id;
159
160 l_rows_inserted := sql%rowcount;
161 l_duration := sysdate - l_temp_date;
162
163 edw_log.put_line('...Inserted ' || to_char(nvl(l_rows_inserted,0))||
164 ' rows into the local staging table');
165 edw_log.put_line('Process Time: ' || edw_log.duration(l_duration));
166 edw_log.put_line(' ');
167
168 RETURN (l_rows_inserted);
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 g_errbuf := sqlerrm;
173 g_retcode := sqlcode;
174 RETURN(-1);
175
176 END;
177
178 ---------------------------------------------------
179 -- FUNCTION IDENTIFY_CHANGE1
180 ---------------------------------------------------
181
182 FUNCTION IDENTIFY_CHANGE1 (p_view_id IN NUMBER,
183 p_count OUT NOCOPY NUMBER) RETURN NUMBER IS
184
185 l_seq_id NUMBER := -1;
186 l_poa_schema VARCHAR2(30);
187 l_status VARCHAR2(30);
188 l_industry VARCHAR2(30);
189
190 BEGIN
191
192 p_count := 0;
193 select poa_edw_cstm_msr_inc_s.nextval into l_seq_id from dual;
194
195 INSERT INTO poa_edw_cstm_msr_inc(primary_key, seq_id)
196 SELECT pms.evaluation_score_id, l_seq_id
197 FROM poa_cm_eval_scores pms,
198 poa_cm_evaluation pme
199 WHERE pms.evaluation_id = pme.evaluation_id
200 AND greatest(pms.last_update_date, pme.last_update_date)
201 between g_push_date_range1 and g_push_date_range2;
202
203 p_count := sql%rowcount;
204
205 RETURN (l_seq_id);
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 g_errbuf:=sqlerrm;
210 g_retcode:=sqlcode;
211 RETURN(-1);
212
213 END;
214
215 -- ---------------------------------
216 -- PUBLIC PROCEDURES
217 -- ---------------------------------
218
219 -----------------------------------------------------------
220 -- PROCEDURE PUSH
221 -----------------------------------------------------------
222
223
224 Procedure Push(Errbuf out NOCOPY Varchar2,
225 Retcode out NOCOPY Varchar2,
226 p_from_date IN Varchar2,
227 p_to_date IN Varchar2) IS
228
229 l_fact_name Varchar2(30) := 'POA_EDW_CSTM_MSR_F';
230 l_staging_table Varchar2(30) := 'POA_EDW_CSTM_MSR_FSTG';
231
232 -- -------------------------------------------
233 -- Put any additional developer variables here
234 -- -------------------------------------------
235
236 l_temp_date DATE:=NULL;
237 l_duration NUMBER:=0;
238 l_exception_msg VARCHAR2(2000):=NULL;
239 l_seq_id NUMBER := -1;
240 l_row_count NUMBER := 0;
241 l_row_count1 NUMBER := 0;
242
243 l_push_local_failure EXCEPTION;
244 l_iden_change_failure EXCEPTION;
245
246 l_from_date date;
247 l_to_date date;
248
249 Begin
250
251 Errbuf :=NULL;
252 Retcode:=0;
253
254 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
255 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
256
257 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name, l_staging_table,
258 l_staging_table, l_exception_msg)) THEN
259 errbuf := fnd_message.get;
260 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
261 END IF;
262
263
264 -- --------------------------------------------
265 -- Taking care of cases where the input from/to
266 -- date is NULL.
267 -- --------------------------------------------
268
269 g_push_date_range1 := nvl(l_from_date,
270 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
271 g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
272
273 edw_log.put_line( 'The collection range is from '||
274 to_char(g_push_date_range1, 'MM/DD/YYYY HH24:MI:SS')||' to '||
275 to_char(g_push_date_range2, 'MM/DD/YYYY HH24:MI:SS'));
276 edw_log.put_line(' ');
277
278 l_temp_date := sysdate;
279
280 -- --------------------------------------------
281 -- Identify Change
282 -- --------------------------------------------
283 edw_log.put_line(' ');
284 edw_log.put_line('Identifying changes...');
285 l_seq_id := IDENTIFY_CHANGE1 (1, l_row_count);
286
287 if (l_seq_id = -1) THEN
288 RAISE l_iden_change_failure;
289 end if;
290 edw_log.put_line('Identified ' || l_row_count || ' changed records');
291
292 -- --------------------------------------------
293 -- Push to local staging table for view type 1
294 -- --------------------------------------------
295
296 edw_log.put_line(' ');
297 edw_log.put_line('Inserting into local staging table for view type 1');
298 l_row_count1 := PUSH_TO_LOCAL(1, l_seq_id);
299
300 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
301
302 edw_log.put_line('Inserted '|| nvl(l_row_count1, 0) ||
303 ' rows into the local staging table for view type 1');
304 edw_log.put_line(' ');
305
306 -- --------------------------------------------
307 -- Delete all incremental tables' record
308 -- --------------------------------------------
309
310 TRUNCATE_INC;
311
312 -- --------------------------------------------
313 -- No exception raised so far. Call wrapup to transport
314 -- data to target database, and insert messages into logs
315 -- -----------------------------------------------
316 g_row_count := g_row_count + l_row_count1;
317 edw_log.put_line(' ');
318 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
319 ' rows into the staging table');
320 l_duration := sysdate - l_temp_date;
321 edw_log.put_line(' ');
322 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
323 edw_log.put_line(' ');
324
325 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,
326 P_PERIOD_START => g_push_date_range1,
327 P_PERIOD_END => g_push_date_range2);
328
329 EXCEPTION
330
331 WHEN L_PUSH_LOCAL_FAILURE THEN
332 Errbuf:=g_errbuf;
333 Retcode:=g_retcode;
334 l_exception_msg := Retcode || ':' || Errbuf;
335 rollback; -- Rollback insert into local staging
336 edw_log.put_line('Inserting into local staging have failed');
337 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
338 g_push_date_range1, g_push_date_range2);
339 raise;
340
341 WHEN L_IDEN_CHANGE_FAILURE THEN
342 Errbuf:=g_errbuf;
343 Retcode:=g_retcode;
344 l_exception_msg := Retcode || ':' || Errbuf;
345 TRUNCATE_INC;
346 edw_log.put_line('Identifying changed records have Failed');
347 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
348 g_push_date_range1, g_push_date_range2);
349 raise;
350
351 WHEN OTHERS THEN
352 Errbuf:=g_errbuf;
353 Retcode:=g_retcode;
354 l_exception_msg := Retcode || ':' || Errbuf;
355 rollback;
356 edw_log.put_line('Other errors');
357 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
358 g_push_date_range1, g_push_date_range2);
359 raise;
360
361 End;
362 End POA_EDW_CSTM_MSR_F_C;