[Home] [Help]
PACKAGE BODY: APPS.EDW_UNSPSC_M_C
Source
1 Package Body EDW_UNSPSC_M_C AS
2 /* $Header: poaphunb.pls 115.16 2004/02/26 13:55:46 apalorka ship $ */
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_UNSPSC_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
19 -- -------------------------------------------
20 -- Put any additional developer variables here
21 -- -------------------------------------------
22 l_from_date date;
23 l_to_date date;
24
25 Begin
26 Errbuf :=NULL;
27 Retcode:=0;
28
29 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
30 errbuf := fnd_message.get;
31 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
32 END IF;
33
34 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
35 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
36
37 EDW_UNSPSC_M_C.g_push_date_range1 := nvl(l_from_date,
38 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
39 EDW_COLLECTION_UTIL.g_offset);
40
41 EDW_UNSPSC_M_C.g_push_date_range2 := nvl(l_to_date,
42 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
43
44 edw_log.put_line( 'The collection range is from '||
45 to_char(EDW_UNSPSC_M_C.g_push_date_range1,
46 'MM/DD/YYYY HH24:MI:SS')||' to '||
47 to_char(EDW_UNSPSC_M_C.g_push_date_range2,
48 'MM/DD/YYYY HH24:MI:SS'));
49
50 edw_log.put_line(' ');
51 edw_log.put_line('Pushing data');
52
53 Push_EDW_DNB_POA_ITEMS();
54 Push_EDW_UNSPSC_CLASS_LSTG(EDW_UNSPSC_M_C.g_push_date_range1,
55 EDW_UNSPSC_M_C.g_push_date_range2);
56
57 Push_EDW_UNSPSC_COMMODITY_LSTG(EDW_UNSPSC_M_C.g_push_date_range1,
58 EDW_UNSPSC_M_C.g_push_date_range2);
59
60 Push_EDW_UNSPSC_FAMILY_LSTG(EDW_UNSPSC_M_C.g_push_date_range1,
61 EDW_UNSPSC_M_C.g_push_date_range2);
62
63 Push_EDW_UNSPSC_FUNCTION_LSTG(EDW_UNSPSC_M_C.g_push_date_range1,
64 EDW_UNSPSC_M_C.g_push_date_range2);
65
66 Push_EDW_UNSPSC_SEGMENT_LSTG(EDW_UNSPSC_M_C.g_push_date_range1,
67 EDW_UNSPSC_M_C.g_push_date_range2);
68
69 l_duration := sysdate - l_temp_date;
70
71 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
72 edw_log.put_line(' ');
73
74 -- ---------------------------------------------------------------------------
75 -- END OF Collection , Developer Customizable Section
76 -- ---------------------------------------------------------------------------
77
78 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, EDW_UNSPSC_M_C.g_exception_msg,
79 g_push_date_range1, g_push_date_range2);
80
81 commit;
82
83 Exception When others then
84 Errbuf:=sqlerrm;
85 Retcode:=sqlcode;
86 l_exception_msg := Retcode || ':' || Errbuf;
87 EDW_UNSPSC_M_C.g_exception_msg := l_exception_msg;
88 rollback;
89
90 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_UNSPSC_M_C.g_exception_msg,
91 g_push_date_range1, g_push_date_range2);
92
93 commit;
94 End Push;
95
96 Procedure Push_EDW_DNB_POA_ITEMS IS
97 BEGIN
98 -- Fill up the Blank Columns from UNSPSC Code
99 Update POA_UNSPSC_INTERFACE
100 set Segment = UNSPSC,
101 Segment_Description = UNSPSC_DESCRIPTION
102 where (Segment IS NULL);
103
104 Update POA_UNSPSC_INTERFACE
105 set Family = UNSPSC,
106 Family_Description = UNSPSC_DESCRIPTION
107 where (Family IS NULL);
108
109 Update POA_UNSPSC_INTERFACE
110 set Class = UNSPSC,
111 Class_Description = UNSPSC_DESCRIPTION
112 where (Class IS NULL);
113
114 Update POA_UNSPSC_INTERFACE
115 set Commodity = UNSPSC,
116 Commodity_Description = UNSPSC_DESCRIPTION
117 where (Commodity IS NULL);
118
119 Update POA_DNB_ITEMS poa
120 set (Item_PK, Item_Name, Function, DNB_Update_Date) =
121 (select Item_PK, Item_Name, UNSPSC || '-' || Function,
122 sysdate from POA_UNSPSC_INTERFACE dnb
123 where poa.Item_PK = dnb.Item_PK)
124 where Item_PK IN
125 (select Item_PK from POA_UNSPSC_INTERFACE dnb
126 where ((poa.Item_PK = dnb.Item_PK) and
127 (poa.Function <> dnb.Function)));
128
129 insert into POA_DNB_ITEMS poa
130 (Item_PK, Item_Name, Function, DNB_Update_Date)
131 (select Item_PK, Item_Name, UNSPSC || '-' || Function,
132 sysdate from POA_UNSPSC_INTERFACE dnb
133 where dnb.Item_PK NOT IN (select Item_PK
134 from POA_DNB_ITEMS));
135
136 END Push_EDW_DNB_POA_ITEMS;
137
138 Procedure Push_EDW_UNSPSC_CLASS_LSTG(p_from_date IN date, p_to_date IN DATE) IS
139 l_date1 DATE;
140 l_date2 DATE;
141 l_rows_inserted NUMBER :=0;
142 BEGIN
143 edw_log.put_line('Starting Push_EDW_UNSPSC_CLASS_LSTG');
144 l_date1 := p_from_date;
145 l_date2 := p_to_date;
146 Insert Into
147 EDW_SPSC_CLASS_LSTG(
148 NAME,
149 CLASS_PK,
150 CLASS_DP,
151 CLASS_CODE,
152 FAMILY_FK,
153 INSTANCE,
154 LAST_UPDATE_DATE,
155 COLLECTION_STATUS)
156 select
157 distinct dnb.Class_Description,
158 dnb.Class,
159 dnb.Class_Description,
160 dnb.Class,
161 NVL(dnb.Family, 'NA_EDW'),
162 NULL,
163 sysdate,
164 'READY'
165 from POA_UNSPSC_INTERFACE dnb;
166
167 l_rows_inserted := sql%rowcount;
168 EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;
169 edw_log.put_line('Commiting records for EDW_SPSC_CLASS_LSTG');
170 commit;
171
172 edw_log.put_line('Completed Push_EDW_UNSPSC_CLASS_LSTG');
173 Exception When others then
174 raise;
175 commit;
176 END Push_EDW_UNSPSC_CLASS_LSTG;
177
178
179
180 Procedure Push_EDW_UNSPSC_COMMODITY_LSTG(p_from_date IN date,
181 p_to_date IN DATE) IS
182 l_date1 DATE;
183 l_date2 DATE;
184 l_rows_inserted NUMBER :=0;
185 BEGIN
186 edw_log.put_line('Starting Push_UNSPSC_COMMODITY_LSTG');
187 l_date1 := p_from_date;
188 l_date2 := p_to_date;
189 Insert Into
190 EDW_SPSC_COMMODITY_LSTG(
191 NAME,
192 COMMODITY_PK,
193 COMMODITY_CODE,
194 COMMODITY_DP,
195 UNSPSC,
196 UNSPSC_DESCRIPTION,
197 CLASS_FK,
198 INSTANCE,
199 LAST_UPDATE_DATE,
200 COLLECTION_STATUS)
201 select
202 distinct Commodity_Description,
203 dnb.Commodity,
204 dnb.Commodity,
205 dnb.Commodity_Description,
206 dnb.UNSPSC,
207 dnb.UNSPSC_Description,
208 NVL(dnb.Class, 'NA_EDW'),
209 NULL,
210 sysdate,
211 'READY'
212 from POA_UNSPSC_INTERFACE dnb;
213
214 l_rows_inserted := sql%rowcount;
215 EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;
216 edw_log.put_line('Commiting records for EDW_SPSC_COMMODITY_LSTG');
217 commit;
218
219 edw_log.put_line('Completed Push_EDW_UNSPSC_COMMODITY_LSTG');
220 Exception When others then
221 raise;
222 commit;
223 END Push_EDW_UNSPSC_COMMODITY_LSTG;
224
225
226
227
228 Procedure Push_EDW_UNSPSC_FAMILY_LSTG(p_from_date IN date,
229 p_to_date IN DATE) IS
230
231 l_date1 DATE;
232 l_date2 DATE;
233 l_rows_inserted NUMBER :=0;
234 BEGIN
235 edw_log.put_line('Starting Push_EDW_UNSPSC_FAMILY_LSTG');
236 l_date1 := p_from_date;
237 l_date2 := p_to_date;
238 Insert Into
239 EDW_SPSC_FAMILY_LSTG(
240 NAME,
241 FAMILY_PK,
242 FAMILY_DP,
243 FAMILY_CODE,
244 SEGMENT_FK,
245 INSTANCE,
246 LAST_UPDATE_DATE,
247 COLLECTION_STATUS)
248 select
249 distinct Family_Description,
250 dnb.Family,
251 dnb.Family_Description,
252 dnb.Family,
253 NVL(Segment, 'NA_EDW'),
254 NULL,
255 sysdate,
256 'READY'
257 from POA_UNSPSC_INTERFACE dnb;
258
259 l_rows_inserted := sql%rowcount;
260 EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;
261 edw_log.put_line('Commiting records for EDW_SPSC_FAMILY_LSTG');
262 commit;
263
264 edw_log.put_line('Completed Push_EDW_UNSPSC_FAMILY_LSTG');
265 Exception When others then
266 raise;
267 commit;
268 END Push_EDW_UNSPSC_FAMILY_LSTG;
269
270
271 Procedure Push_EDW_UNSPSC_FUNCTION_LSTG(p_from_date IN date,
272 p_to_date IN DATE) IS
273 l_date1 DATE;
274 l_date2 DATE;
275 l_rows_inserted NUMBER :=0;
276 BEGIN
277 edw_log.put_line('Starting Push_EDW_UNSPSC_FUNCTION_LSTG_LSTG');
278 l_date1 := p_from_date;
279 l_date2 := p_to_date;
280
281 -- Set the Update Fact Flag
282 Update POA_UNSPSC_INTERFACE
283 set Update_Fact_Flag = 'N';
284
285 Update POA_UNSPSC_INTERFACE
286 set Update_Fact_Flag = 'Y'
287 where Item_PK IN (select Item_PK
288 from POA_DNB_ITEMS poa
289 where poa.DNB_Update_Date between
290 l_date1 and l_date2);
291
292
293 Insert Into
294 EDW_SPSC_FUNCTION_LSTG(
295 NAME,
296 FUNCTION_PK,
297 FUNCTION_DP,
298 FUNCTION_CODE,
299 COMMODITY_FK,
300 INSTANCE,
301 LAST_UPDATE_DATE,
302 COLLECTION_STATUS,
303 UPDATE_FACT_FLAG)
304 select
305 distinct NVL(ltrim(dnb.Function_Description), dnb.UNSPSC_Description),
306 dnb.UNSPSC || '-' || dnb.Function,
307 NVL(ltrim(dnb.Function_Description), dnb.UNSPSC_Description),
308 dnb.UNSPSC || '-' || dnb.Function,
309 NVL(Commodity, 'NA_EDW'),
310 NULL,
311 sysdate,
312 'READY',
313 dnb.Update_Fact_Flag
314 from POA_UNSPSC_INTERFACE dnb,
315 POA_DNB_ITEMS poa
316 where (dnb.Item_PK = poa.Item_PK);
317
318 l_rows_inserted := sql%rowcount;
319 EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;
320 edw_log.put_line('Commiting records for EDW_SPSC_FUNCTION_LSTG');
321 commit;
322
323 edw_log.put_line('Completed Push_EDW_UNSPSC_FUNCTION_LSTG');
324 Exception When others then
325 raise;
326 commit;
327 END Push_EDW_UNSPSC_FUNCTION_LSTG;
328
329
330
331
332 Procedure Push_EDW_UNSPSC_SEGMENT_LSTG(p_from_date IN date,
333 p_to_date IN DATE) IS
334 l_date1 DATE;
335 l_date2 DATE;
336 l_rows_inserted NUMBER :=0;
337 BEGIN
338 edw_log.put_line('Starting Push_EDW_UNSPSC_SEGMENT_LSTG');
339 l_date1 := p_from_date;
340 l_date2 := p_to_date;
341
342 Insert Into
343 EDW_SPSC_SEGMENT_LSTG(
344 NAME,
345 SEGMENT_PK,
346 SEGMENT_DP,
347 SEGMENT_CODE,
348 ALL_FK,
349 INSTANCE,
350 LAST_UPDATE_DATE,
351 COLLECTION_STATUS)
352 select
353 distinct dnb.Segment_Description,
354 dnb.Segment,
355 dnb.Segment_Description,
356 dnb.Segment,
357 'ALL',
358 NULL,
359 sysdate,
360 'READY'
361 from POA_UNSPSC_INTERFACE dnb;
362
363 l_rows_inserted := sql%rowcount;
364 EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;
365 edw_log.put_line('Commiting records for EDW_SPSC_SEGMENT_LSTG');
366 commit;
367
368 edw_log.put_line('Completed Push_EDW_UNSPSC_SEGMENT_LSTG');
369 Exception When others then
370 raise;
371 END Push_EDW_UNSPSC_SEGMENT_LSTG;
372
373 End EDW_UNSPSC_M_C;