[Home] [Help]
PACKAGE BODY: APPS.EDW_MTL_INVENTORY_LOC_M_C
Source
1 Package Body EDW_MTL_INVENTORY_LOC_M_C AS
2 /* $Header: OPIINVDB.pls 120.1 2005/06/08 01:10:03 appldev $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_lowest_level_count NUMBER := 0;
7 g_exception_msg varchar2(2000):=Null;
8 Procedure Push(Errbuf in out NOCOPY Varchar2,
9 Retcode in out NOCOPY Varchar2,
10 p_from_date IN VARCHAR2,
11 p_to_date IN VARCHAR2) IS
12 l_dimension_name Varchar2(30) :='EDW_MTL_INVENTORY_LOC_M' ;
13 l_temp_date Date:=Null;
14 l_rows_inserted Number:=0;
15 l_duration Number:=0;
16 l_exception_msg Varchar2(2000):=Null;
17 l_temp_date_char Varchar2(35);
18 -- -------------------------------------------
19 -- Put any additional developer variables here
20 -- -------------------------------------------
21 Begin
22 Errbuf :=NULL;
23 Retcode:=0;
24 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
25 errbuf := fnd_message.get;
26 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
27 Return;
28 END IF;
29 IF (p_from_date IS NULL) THEN
30 EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
31 EDW_COLLECTION_UTIL.g_offset;
32 ELSE
33 EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
34 END IF;
35 IF (p_to_date IS NULL) THEN
36 EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
37 ELSE
38 EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
39 END IF;
40 edw_log.put_line( 'The collection range is from '||
41 to_char(EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
42 to_char(EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
43 edw_log.put_line(' ');
44 -- -----------------------------------------------------------------------------
45 -- Start of Collection , Developer Customizable Section
46 -- -----------------------------------------------------------------------------
47 edw_log.put_line(' ');
48 edw_log.put_line('Pushing data');
49 l_temp_date := sysdate;
50 Push_EDW_MTL_ILDM_LOCATOR_LSTG(EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1, EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2);
51 Push_EDW_MTL_ILDM_SUB_INV_LSTG(EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1, EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2);
52 Push_EDW_MTL_ILDM_PLANT_LSTG(EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1, EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2);
53 Push_EDW_MTL_ILDM_OU_LSTG(EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1, EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2);
54 Push_EDW_MTL_ILDM_PORG_LSTG(EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1, EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2);
55 Push_EDW_MTL_ILDM_PCMP_LSTG(EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1, EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2);
56 l_duration := sysdate - l_temp_date;
57 edw_log.put_line('Total rows inserted : '||g_row_count);
58 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
59 edw_log.put_line(' ');
60 -- ---------------------------------------------------------------------------
61 -- END OF Collection , Developer Customizable Section
62 -- ---------------------------------------------------------------------------
63 EDW_COLLECTION_UTIL.wrapup(TRUE, g_lowest_level_count,
64 EDW_MTL_INVENTORY_LOC_M_C.g_exception_msg,
65 EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1,
66 EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2);
67 commit;
68 Exception When others then
69 Errbuf:=sqlerrm;
70 Retcode:=sqlcode;
71 l_exception_msg := Retcode || ':' || Errbuf;
72 EDW_MTL_INVENTORY_LOC_M_C.g_exception_msg := l_exception_msg;
73 rollback;
74 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_MTL_INVENTORY_LOC_M_C.g_exception_msg,
75 EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range1,
76 EDW_MTL_INVENTORY_LOC_M_C.g_push_date_range2);
77 commit;
78 End;
79 Procedure Push_EDW_MTL_ILDM_LOCATOR_LSTG(p_from_date IN date, p_to_date IN DATE) IS
80 l_date1 DATE;
81 l_date2 DATE;
82 l_rows_inserted NUMBER :=0;
83 BEGIN
84 edw_log.put_line('Starting Push_EDW_MTL_ILDM_LOCATOR_LSTG');
85 l_date1 := p_from_date;
86 l_date2 := p_to_date;
87 Insert Into
88 EDW_MTL_ILDM_LOCATOR_LSTG@EDW_APPS_TO_WH(
89 CREATION_DATE,
90 DESCRIPTION,
91 ENABLED_FLAG,
92 INSTANCE_CODE,
93 LAST_UPDATE_DATE,
94 LOCATOR_DP,
95 LOCATOR_NAME,
96 LOCATOR_PK,
97 NAME,
98 STOCK_ROOM_FK,
99 USER_ATTRIBUTE1,
100 USER_ATTRIBUTE2,
101 USER_ATTRIBUTE3,
102 USER_ATTRIBUTE4,
103 USER_ATTRIBUTE5,
104 OPERATION_CODE,
105 COLLECTION_STATUS)
106 select CREATION_DATE,
107 DESCRIPTION,
108 ENABLED_FLAG,
109 INSTANCE_CODE,
110 LAST_UPDATE_DATE,
111 LOCATOR_DP,
112 LOCATOR_NAME,
113 LOCATOR_PK,
114 NAME,
115 NVL(STOCK_ROOM_FK, 'NA_EDW'),
116 USER_ATTRIBUTE1,
117 USER_ATTRIBUTE2,
118 USER_ATTRIBUTE3,
119 USER_ATTRIBUTE4,
120 USER_ATTRIBUTE5,
121 NULL, -- OPERATION_CODE
122 'READY'
123 from EDW_MTL_ILDM_LOCATOR_LCV@APPS_TO_APPS
124 where last_update_date between l_date1 and l_date2;
125 l_rows_inserted := sql%rowcount;
126
127 EDW_MTL_INVENTORY_LOC_M_C.g_lowest_level_count := l_rows_inserted;
128
129 EDW_MTL_INVENTORY_LOC_M_C.g_row_count := EDW_MTL_INVENTORY_LOC_M_C.g_row_count + l_rows_inserted ;
130 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
131 ' rows into the staging table');
132 edw_log.put_line('Commiting records for EDW_MTL_ILDM_LOCATOR_LSTG');
133 commit;
134 edw_log.put_line('Completed Push_EDW_MTL_ILDM_LOCATOR_LSTG');
135 Exception When others then
136 raise;
137 commit;
138 END;
139 Procedure Push_EDW_MTL_ILDM_SUB_INV_LSTG(p_from_date IN date, p_to_date IN DATE) IS
140 l_date1 DATE;
141 l_date2 DATE;
142 l_rows_inserted NUMBER :=0;
143 BEGIN
144 edw_log.put_line('Starting Push_EDW_MTL_ILDM_SUB_INV_LSTG');
145 l_date1 := p_from_date;
146 l_date2 := p_to_date;
147 Insert Into
148 EDW_MTL_ILDM_SUB_INV_LSTG@EDW_APPS_TO_WH(
149 CREATION_DATE,
150 DESCRIPTION,
151 INSTANCE_CODE,
152 LAST_UPDATE_DATE,
153 NAME,
154 PLANT_FK,
155 STOCK_ROOM,
156 STOCK_ROOM_DP,
157 STOCK_ROOM_PK,
158 USER_ATTRIBUTE1,
159 USER_ATTRIBUTE2,
160 USER_ATTRIBUTE3,
161 USER_ATTRIBUTE4,
162 USER_ATTRIBUTE5,
163 OPERATION_CODE,
164 COLLECTION_STATUS)
165 select CREATION_DATE,
166 DESCRIPTION,
167 INSTANCE_CODE,
168 LAST_UPDATE_DATE,
169 NAME,
170 NVL(PLANT_FK, 'NA_EDW'),
171 STOCK_ROOM,
172 STOCK_ROOM_DP,
173 STOCK_ROOM_PK,
174 USER_ATTRIBUTE1,
175 USER_ATTRIBUTE2,
176 USER_ATTRIBUTE3,
177 USER_ATTRIBUTE4,
178 USER_ATTRIBUTE5,
179 NULL, -- OPERATION_CODE
180 'READY'
181 from EDW_MTL_ILDM_SUB_INV_LCV@APPS_TO_APPS
182 where last_update_date between l_date1 and l_date2;
183 l_rows_inserted := sql%rowcount;
184 EDW_MTL_INVENTORY_LOC_M_C.g_row_count := EDW_MTL_INVENTORY_LOC_M_C.g_row_count + l_rows_inserted ;
185 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
186 ' rows into the staging table');
187 edw_log.put_line('Commiting records for EDW_MTL_ILDM_SUB_INV_LSTG');
188 commit;
189 edw_log.put_line('Completed Push_EDW_MTL_ILDM_SUB_INV_LSTG');
190 Exception When others then
191 raise;
192 commit;
193 END;
194 Procedure Push_EDW_MTL_ILDM_PLANT_LSTG(p_from_date IN date, p_to_date IN DATE) IS
195 l_date1 DATE;
196 l_date2 DATE;
197 l_rows_inserted NUMBER :=0;
198 BEGIN
199 edw_log.put_line('Starting Push_EDW_MTL_ILDM_PLANT_LSTG');
200 l_date1 := p_from_date;
201 l_date2 := p_to_date;
202 Insert Into
203 EDW_MTL_ILDM_PLANT_LSTG@EDW_APPS_TO_WH(
204 OPERATING_UNIT_FK,
205 CREATION_DATE,
206 DESCRIPTION,
207 INSTANCE_CODE,
208 LAST_UPDATE_DATE,
209 NAME,
210 ORGANIZATION_CODE,
211 ORGANIZATION_NAME,
212 PLANT_DP,
213 PLANT_PK,
214 USER_ATTRIBUTE1,
215 USER_ATTRIBUTE2,
216 USER_ATTRIBUTE3,
217 USER_ATTRIBUTE4,
218 USER_ATTRIBUTE5,
219 OPM_ORGANIZATION_FK,
220 OPERATION_CODE,
221 COLLECTION_STATUS)
222 select NVL(Operating_Unit_FK, 'NA_EDW'),
223 CREATION_DATE,
224 DESCRIPTION,
225 INSTANCE_CODE,
226 LAST_UPDATE_DATE,
227 NAME,
228 ORGANIZATION_CODE,
229 ORGANIZATION_NAME,
230 PLANT_DP,
231 PLANT_PK,
232 USER_ATTRIBUTE1,
233 USER_ATTRIBUTE2,
234 USER_ATTRIBUTE3,
235 USER_ATTRIBUTE4,
236 USER_ATTRIBUTE5,
237 NVL(OPM_Organization_FK, 'NA_EDW'),
238 NULL, -- OPERATION_CODE
239 'READY'
240 from EDW_MTL_ILDM_PLANT_LCV@APPS_TO_APPS
241 where last_update_date between l_date1 and l_date2;
242 l_rows_inserted := sql%rowcount;
243 EDW_MTL_INVENTORY_LOC_M_C.g_row_count := EDW_MTL_INVENTORY_LOC_M_C.g_row_count + l_rows_inserted ;
244 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
245 ' rows into the staging table');
246 edw_log.put_line('Commiting records for EDW_MTL_ILDM_PLANT_LSTG');
247 commit;
248 edw_log.put_line('Completed Push_EDW_MTL_ILDM_PLANT_LSTG');
249 Exception When others then
250 raise;
251 commit;
252 END;
253 Procedure Push_EDW_MTL_ILDM_OU_LSTG(p_from_date IN date, p_to_date IN DATE) IS
254 l_date1 DATE;
255 l_date2 DATE;
256 l_rows_inserted NUMBER :=0;
257 BEGIN
258 edw_log.put_line('Starting Push_EDW_MTL_ILDM_OU_LSTG');
259 l_date1 := p_from_date;
260 l_date2 := p_to_date;
261 Insert Into
262 EDW_MTL_ILDM_OU_LSTG@EDW_APPS_TO_WH(
263 OPERATING_UNIT_PK,
264 OPERATING_UNIT_DP,
265 NAME,
266 BUSINESS_GROUP,
267 DATE_FROM,
268 DATE_TO,
269 INT_EXT_FLAG,
270 ORG_TYPE,
271 ORG_CODE,
272 PRIMARY_CST_MTHD,
273 INSTANCE,
274 ALL_FK,
275 USER_ATTRIBUTE1,
276 USER_ATTRIBUTE2,
277 USER_ATTRIBUTE3,
278 USER_ATTRIBUTE4,
279 USER_ATTRIBUTE5,
280 CREATION_DATE,
281 LAST_UPDATE_DATE,
282 OPERATION_CODE,
283 COLLECTION_STATUS)
284 select OPERATING_UNIT_PK,
285 OPERATING_UNIT_DP,
286 NAME,
287 BUSINESS_GROUP,
288 DATE_FROM,
289 DATE_TO,
290 INT_EXT_FLAG,
291 ORG_TYPE,
292 ORG_CODE,
293 PRIMARY_CST_MTHD,
294 INSTANCE,
295 NVL(ALL_FK, 'NA_EDW'),
296 USER_ATTRIBUTE1,
297 USER_ATTRIBUTE2,
298 USER_ATTRIBUTE3,
299 USER_ATTRIBUTE4,
300 USER_ATTRIBUTE5,
301 CREATION_DATE,
302 LAST_UPDATE_DATE,
303 NULL, -- OPERATION_CODE
304 'READY'
305 from EDW_MTL_ILDM_OU_LCV@APPS_TO_APPS
306 where last_update_date between l_date1 and l_date2;
307 l_rows_inserted := sql%rowcount;
308 EDW_MTL_INVENTORY_LOC_M_C.g_row_count := EDW_MTL_INVENTORY_LOC_M_C.g_row_count + l_rows_inserted ;
309 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
310 ' rows into the staging table');
311 edw_log.put_line('Commiting records for EDW_MTL_ILDM_OU_LSTG');
312 commit;
313 edw_log.put_line('Completed Push_EDW_MTL_ILDM_OU_LSTG');
314 Exception When others then
315 raise;
316 commit;
317 END;
318 Procedure Push_EDW_MTL_ILDM_PORG_LSTG(p_from_date IN date, p_to_date IN DATE) IS
319 l_date1 DATE;
320 l_date2 DATE;
321 l_rows_inserted NUMBER :=0;
322 BEGIN
323 edw_log.put_line('Starting Push_EDW_MTL_ILDM_PORG_LSTG');
324 l_date1 := p_from_date;
325 l_date2 := p_to_date;
326 Insert Into
327 EDW_MTL_ILDM_PORG_LSTG@EDW_APPS_TO_WH(
328 OPM_COMPANY_FK,
329 CREATION_DATE,
330 DESCRIPTION,
331 INSTANCE_CODE,
332 LAST_UPDATE_DATE,
333 NAME,
334 OPM_ORGANIZATION_CODE,
335 OPM_ORGANIZATION_NAME,
336 OPM_ORGANIZATION_DP,
337 OPM_ORGANIZATION_PK,
338 USER_ATTRIBUTE1,
339 USER_ATTRIBUTE2,
340 USER_ATTRIBUTE3,
341 USER_ATTRIBUTE4,
342 USER_ATTRIBUTE5,
343 OPERATION_CODE,
344 COLLECTION_STATUS)
345 select NVL(OPM_COMPANY_FK, 'NA_EDW'),
346 CREATION_DATE,
347 DESCRIPTION,
348 INSTANCE_CODE,
349 LAST_UPDATE_DATE,
350 NAME,
351 OPM_ORGANIZATION_CODE,
352 OPM_ORGANIZATION_NAME,
353 OPM_ORGANIZATION_DP,
354 OPM_ORGANIZATION_PK,
355 USER_ATTRIBUTE1,
356 USER_ATTRIBUTE2,
357 USER_ATTRIBUTE3,
358 USER_ATTRIBUTE4,
359 USER_ATTRIBUTE5,
360 NULL, -- OPERATION_CODE
361 'READY'
362 from EDW_MTL_ILDM_PORG_LCV@APPS_TO_APPS
363 where last_update_date between l_date1 and l_date2;
364 l_rows_inserted := sql%rowcount;
365 EDW_MTL_INVENTORY_LOC_M_C.g_row_count := EDW_MTL_INVENTORY_LOC_M_C.g_row_count + l_rows_inserted ;
366 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
367 ' rows into the staging table');
368 edw_log.put_line('Commiting records for EDW_MTL_ILDM_PORG_LSTG');
369 commit;
370 edw_log.put_line('Completed Push_EDW_MTL_ILDM_PORG_LSTG');
371 Exception When others then
372 raise;
373 commit;
374 END;
375 Procedure Push_EDW_MTL_ILDM_PCMP_LSTG(p_from_date IN date, p_to_date IN DATE) IS
376 l_date1 DATE;
377 l_date2 DATE;
378 l_rows_inserted NUMBER :=0;
379 BEGIN
380 edw_log.put_line('Starting Push_EDW_MTL_ILDM_PCMP_LSTG');
381 l_date1 := p_from_date;
382 l_date2 := p_to_date;
383 Insert Into
384 EDW_MTL_ILDM_PCMP_LSTG@EDW_APPS_TO_WH(
385 OPERATING_UNIT_FK,
386 OPM_COMPANY_NAME,
387 OPM_COMPANY_DP,
388 OPM_COMPANY_PK,
389 CREATION_DATE,
390 DESCRIPTION,
391 INSTANCE_CODE,
392 LAST_UPDATE_DATE,
393 NAME,
394 USER_ATTRIBUTE1,
395 USER_ATTRIBUTE2,
396 USER_ATTRIBUTE3,
397 USER_ATTRIBUTE4,
398 USER_ATTRIBUTE5,
399 OPM_COMPANY_CODE,
400 OPERATION_CODE,
401 COLLECTION_STATUS)
402 select NVL(OPERATING_UNIT_FK, 'NA_EDW'),
403 OPM_COMPANY_NAME,
404 OPM_COMPANY_DP,
405 OPM_COMPANY_PK,
406 CREATION_DATE,
407 DESCRIPTION,
408 INSTANCE_CODE,
409 LAST_UPDATE_DATE,
410 NAME,
411 USER_ATTRIBUTE1,
412 USER_ATTRIBUTE2,
413 USER_ATTRIBUTE3,
414 USER_ATTRIBUTE4,
415 USER_ATTRIBUTE5,
416 OPM_COMPANY_CODE,
417 NULL, -- OPERATION_CODE
418 'READY'
419 from EDW_MTL_ILDM_PCMP_LCV@APPS_TO_APPS
420 where last_update_date between l_date1 and l_date2;
421 l_rows_inserted := sql%rowcount;
422 EDW_MTL_INVENTORY_LOC_M_C.g_row_count := EDW_MTL_INVENTORY_LOC_M_C.g_row_count + l_rows_inserted ;
423 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
424 ' rows into the staging table');
425 edw_log.put_line('Commiting records for EDW_MTL_ILDM_PCMP_LSTG');
426 commit;
427 edw_log.put_line('Completed Push_EDW_MTL_ILDM_PCMP_LSTG');
428 Exception When others then
429 raise;
430 commit;
431 END;
432 End EDW_MTL_INVENTORY_LOC_M_C;