[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_INV_DAILY_STAT_F_C
Source
1 Package Body OPI_EDW_INV_DAILY_STAT_F_C AS
2 /* $Header: OPIMIDSB.pls 120.1 2005/06/07 03:28:17 appldev $ */
3 g_push_from_date Date:=Null;
4 g_push_to_date Date:=Null;
5
6 -- ---------------------------------
7 -- PUBLIC PROCEDURES
8 -- ---------------------------------
9
10 -----------------------------------------------------------
11 -- PROCEDURE PUSH
12 -----------------------------------------------------------
13 PROCEDURE Push(Errbuf in OUT NOCOPY Varchar2,
14 Retcode in OUT NOCOPY Varchar2,
15 p_from_date IN varchar2,
16 p_to_date IN varchar2,
17 p_org_code IN varchar2 DEFAULT Null) IS
18
19 l_fact_name VARCHAR2(30) ;
20 l_staging_table VARCHAR2(30) ;
21 l_exception_msg VARCHAR2(2000);
22 l_last_push_end_date date;
23 l_sysdate date;
24 l_row_count number;
25 l_row_pushed number;
26
27 l_from_date DATE ;
28 l_to_date DATE ;
29
30 l_global_currency_code VARCHAR2(30);
31 l_rate_type VARCHAR2(30);
32 l_prev_org_id NUMBER;
33 l_conv_rate NUMBER;
34 l_base_currency_code VARCHAR2(40);
35 l_trx_date DATE;
36 l_org_conv_rate_flag BOOLEAN ;
37 l_global_conv_rate_flag BOOLEAN ;
38
39 CURSOR l_org_date_csr IS
40 SELECT organization_id, trx_date
41 FROM opi_ids_push_log
42 WHERE push_flag = 1
43 GROUP BY organization_id, trx_date;
44
45 currency_not_exist EXCEPTION;
46 currency_conv_rate_not_exist EXCEPTION;
47
48 CURSOR get_uom_data_cursor IS
49 select BASE_UOM, INVENTORY_ITEM_ID,
50 EDW_UTIL.get_edw_base_uom(BASE_UOM,INVENTORY_ITEM_ID) EDW_BASE_UOM,
51 EDW_UTIL.get_uom_conv_rate(BASE_UOM,INVENTORY_ITEM_ID) EDW_Uom_Conv_Rate
52 from
53 opi_ids_push_log
54 where
55 base_uom is not null and
56 push_flag = 1
57 group by
58 BASE_UOM,INVENTORY_ITEM_ID;
59
60 BEGIN
61 --dbms_output.put_line('start of push ' || to_char(Sysdate, 'hh24:mi:ss') );
62 l_fact_name :='OPI_EDW_INV_DAILY_STAT_F';
63 l_staging_table :='OPI_EDW_INV_DAILY_STAT_FSTG';
64 l_exception_msg :=Null;
65 l_row_count := 0;
66 l_row_pushed := 0;
67 l_from_date := NULL;
68 l_to_date := NULL;
69 l_org_conv_rate_flag := TRUE;
70 l_global_conv_rate_flag := TRUE;
71 Errbuf :=NULL;
72 Retcode:=0;
73
74 edw_log.put_line(' ');
75 edw_log.put_line('call EDW_COLLECTION_UTIL ');
76 --dbms_output.put_line(p_from_date);
77 --dbms_output.put_line(p_to_date);
78 -- -------------------------------------------
79 -- call edw_collection_util.setup
80 -- -------------------------------------------
81 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,
82 l_staging_table,
83 l_staging_table,
84 l_exception_msg)) THEN
85 errbuf := fnd_message.get;
86 Return;
87 END IF;
88
89 -- -----------------------------------------------------
90 -- figure out the process start/end date
91 -- Append 23:59:59 to the to_date incase it's passed
92 -- -----------------------------------------------------
93 l_from_date := To_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
94 l_to_date := To_date(p_to_date,'YYYY/MM/DD HH24:MI:SS');
95
96 /*
97 IF l_to_date IS NOT NULL THEN
98 l_to_date := to_date(p_to_date||' 23:59:59','YYYY/MM/DD HH24:MI:SS');
99 END IF;
100 */
101
102
103 -- Start of code change for bug fix 2140267.
104 -- --------------------------------------------
105 -- Taking care of cases where the input from/to
106 -- date is NULL.
107 -- --------------------------------------------
108
109 l_from_date := nvl(l_from_date,
110 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
111 EDW_COLLECTION_UTIL.g_offset);
112 l_to_date := nvl(l_to_date,
113 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
114
115
116 -- End of code change for bug fix 2140267.
117
118
119
120
121
122
123
124 -- --------------------------------------------------------
125 -- call opi_extract_ids: This program will process the WIP and INV
126 -- Transactions by organization. It will then insert/update the records
127 -- in the opi_ids_push_log.
128 --
129 -- --------------------------------------------------------
130 --dbms_output.put_line(' ');
131 --dbms_output.put_line('call opi_extract_ids ');
132
133
134 edw_log.put_line(' ');
135 edw_log.put_line('call opi_extract_ids ');
136
137 OPIMPXWI.opi_extract_ids(l_from_date,l_to_date,p_org_code);
138
139
140 --dbms_output.put_line('after extract ids ' || to_char(Sysdate, 'hh24:mi:ss') );
141 select sum(1) INTO l_row_pushed
142 from opi_ids_push_log
143 where push_flag=1
144 and rownum < 2;
145
146 BEGIN
147 SELECT warehouse_currency_code, rate_type
148 INTO l_global_currency_code, l_rate_type
149 FROM EDW_LOCAL_SYSTEM_PARAMETERS;
150 EXCEPTION
151 WHEN OTHERS THEN
152 RAISE currency_not_exist;
153 END;
154
155
156 if (l_row_pushed > 0) THEN
157 -- --------------------------------------------------------
158 -- populate opi_ids_push_log.edw_base_uom, edw_conv_rate
159 -- --------------------------------------------------------
160 FOR l_org_date IN l_org_date_csr LOOP
161
162
163
164 IF l_org_date_csr%rowcount = 1 THEN
165 SELECT edw_util.get_base_currency(l_org_date.organization_id)
166 INTO l_base_currency_code
167 FROM dual;
168
169 l_prev_org_id := l_org_date.organization_id;
170
171 --dbms_output.put_line(' in l_org_date, ' || l_org_date.organization_id || l_org_date.trx_date );
172 END IF;
173
174 IF l_prev_org_id <> l_org_date.organization_id THEN
175 SELECT edw_util.get_base_currency(l_org_date.organization_id)
176 INTO l_base_currency_code
177 FROM dual;
178
179 l_prev_org_id := l_org_date.organization_id;
180 l_org_conv_rate_flag := TRUE;
181
182 -- dbms_output.put_line(' in l_org_date, ' || l_org_date.organization_id || l_org_date.trx_date );
183 END IF;
184
185 BEGIN
186 SELECT
187 GL_CURRENCY_API.get_closest_rate(l_base_currency_code,
188 l_global_currency_code,
189 l_org_date.trx_date,
190 l_rate_type,
191 1000 )
192 INTO l_conv_rate
193 FROM dual;
194 EXCEPTION
195 WHEN OTHERS THEN
196 l_trx_date := l_org_date.trx_date;
197
198 edw_log.put_line ('No conversion rate existed for conversion from '
199 || l_base_currency_code || ' to ' ||
200 l_global_currency_code || ' in organization_id ' ||
201 l_prev_org_id || ' on ' || To_char( l_trx_date, 'dd/mm/yyyy') );
202
203 l_org_conv_rate_flag := FALSE;
204 l_global_conv_rate_flag := FALSE;
205 -- dbms_output.put_line('No conversion rate existed for conversion from '
206 -- || l_base_currency_code || ' to ' ||
207 -- l_global_currency_code || ' in organization_id ' ||
208 -- l_prev_org_id || ' on ' || To_char( l_trx_date, 'dd/mm/yyyy') );
209 END;
210
211 IF l_org_conv_rate_flag THEN
212 UPDATE opi_ids_push_log
213 SET base_currency_code = l_base_currency_code,
214 edw_conv_rate = l_conv_rate
215 WHERE organization_id = l_org_date.organization_id
216 AND trx_date = l_org_date.trx_date
217 AND push_flag = 1;
218 --dbms_output.put_line('after update count is ' || SQL%rowcount );
219
220 END IF;
221
222 END LOOP;
223 --dbms_output.put_line('after edw_conv_rate ' || to_char(Sysdate, 'hh24:mi:ss'));
224 -- ---------------------------------------------------------
225 -- Get UOMs and Conversion Rates
226 -- ---------------------------------------------------------
227
228 FOR each_uom_data_record IN get_uom_data_cursor LOOP
229 Update opi_ids_push_log
230 SET
231 EDW_Base_UOM = each_uom_data_record.EDW_Base_UOM,
232 EDW_uom_Conv_Rate = each_uom_data_record.EDW_uom_Conv_Rate
233 where
234 push_flag = 1 and
235 BASE_UOM = each_uom_data_record.BASE_UOM and
236 INVENTORY_ITEM_ID = each_uom_data_record.INVENTORY_ITEM_ID;
237 END LOOP;
238
239 --dbms_output.put_line('after uom conv ' || to_char(Sysdate, 'hh24:mi:ss'));
240
241 COMMIT;
242 -- --------------------------------------------------------
243 -- Insert into the local staging table
244 -- --------------------------------------------------------
245 edw_log.put_line(' ');
246 edw_log.put_line('Insert into the local staging table');
247
248 INSERT INTO opi_edw_inv_daily_stat_fstg(
249 AVG_INT_QTY
250 ,AVG_INT_VAL_B
251 ,AVG_INT_VAL_G
252 ,AVG_ONH_QTY
253 ,AVG_ONH_VAL_B
254 ,AVG_ONH_VAL_G
255 ,AVG_WIP_QTY
256 ,AVG_WIP_VAL_B
257 ,AVG_WIP_VAL_G
258 ,BASE_CURRENCY_FK
259 ,BASE_UOM_FK
260 ,BEG_INT_QTY
261 ,BEG_INT_VAL_B
262 ,BEG_INT_VAL_G
263 ,BEG_ONH_QTY
264 ,BEG_ONH_VAL_B
265 ,BEG_ONH_VAL_G
266 ,BEG_WIP_QTY
267 ,BEG_WIP_VAL_B
268 ,BEG_WIP_VAL_G
269 ,COMMODITY_CODE
270 ,COST_GROUP
271 ,CREATION_DATE
272 ,END_INT_QTY
273 ,END_INT_VAL_B
274 ,END_INT_VAL_G
275 ,END_ONH_QTY
276 ,END_ONH_VAL_B
277 ,END_ONH_VAL_G
278 ,END_WIP_QTY
279 ,END_WIP_VAL_B
280 ,END_WIP_VAL_G
281 ,FROM_ORG_QTY
282 ,FROM_ORG_VAL_B
283 ,FROM_ORG_VAL_G
284 ,INSTANCE_FK
285 ,INV_ADJ_QTY
286 ,INV_ADJ_VAL_B
287 ,INV_ADJ_VAL_G
288 ,INV_DAILY_STATUS_PK
289 ,INV_ORG_FK
290 ,ITEM_ORG_FK
291 ,ITEM_STATUS
292 ,ITEM_TYPE
293 ,LAST_UPDATE_DATE
294 ,LOCATOR_FK
295 ,LOT_FK
296 ,NETTABLE_FLAG
297 ,PO_DEL_QTY
298 ,PO_DEL_VAL_B
299 ,PO_DEL_VAL_G
300 ,PRD_DATE_FK
301 ,TOTAL_REC_QTY
302 ,TOTAL_REC_VAL_B
303 ,TOTAL_REC_VAL_G
304 ,TOT_CUST_SHIP_QTY
305 ,TOT_CUST_SHIP_VAL_B
306 ,TOT_CUST_SHIP_VAL_G
307 ,TOT_ISSUES_QTY
308 ,TOT_ISSUES_VAL_B
309 ,TOT_ISSUES_VAL_G
310 ,TO_ORG_QTY
311 ,TO_ORG_VAL_B
312 ,TO_ORG_VAL_G
313 ,TRX_DATE_FK
314 ,USER_ATTRIBUTE1
315 ,USER_ATTRIBUTE10
316 ,USER_ATTRIBUTE11
317 ,USER_ATTRIBUTE12
318 ,USER_ATTRIBUTE13
319 ,USER_ATTRIBUTE14
320 ,USER_ATTRIBUTE15
321 ,USER_ATTRIBUTE2
322 ,USER_ATTRIBUTE3
323 ,USER_ATTRIBUTE4
324 ,USER_ATTRIBUTE5
325 ,USER_ATTRIBUTE6
326 ,USER_ATTRIBUTE7
327 ,USER_ATTRIBUTE8
328 ,USER_ATTRIBUTE9
329 ,USER_FK1
330 ,USER_FK2
331 ,USER_FK3
332 ,USER_FK4
333 ,USER_FK5
334 ,USER_MEASURE1
335 ,USER_MEASURE2
336 ,USER_MEASURE3
337 ,USER_MEASURE4
338 ,USER_MEASURE5
339 ,WIP_ASSY_QTY
340 ,WIP_ASSY_VAL_B
341 ,WIP_ASSY_VAL_G
342 ,WIP_COMP_QTY
343 ,WIP_COMP_VAL_B
344 ,WIP_COMP_VAL_G
345 ,WIP_ISSUE_QTY
346 ,WIP_ISSUE_VAL_B
347 ,WIP_ISSUE_VAL_G
348 ,TRX_DATE
349 ,PERIOD_FLAG
350 ,OPERATION_CODE
351 ,COLLECTION_STATUS)
352 select
353 AVG_INT_QTY
354 ,AVG_INT_VAL_B
355 ,AVG_INT_VAL_G
356 ,AVG_ONH_QTY
357 ,AVG_ONH_VAL_B
358 ,AVG_ONH_VAL_G
359 ,AVG_WIP_QTY
360 ,AVG_WIP_VAL_B
361 ,AVG_WIP_VAL_G
362 ,BASE_CURRENCY_FK
363 ,BASE_UOM_FK
364 ,BEG_INT_QTY
365 ,BEG_INT_VAL_B
366 ,BEG_INT_VAL_G
367 ,BEG_ONH_QTY
368 ,BEG_ONH_VAL_B
369 ,BEG_ONH_VAL_G
370 ,BEG_WIP_QTY
371 ,BEG_WIP_VAL_B
372 ,BEG_WIP_VAL_G
373 ,COMMODITY_CODE
374 ,NVL(COST_GROUP,'NO COST GROUP')
375 ,CREATION_DATE
376 ,END_INT_QTY
377 ,END_INT_VAL_B
378 ,END_INT_VAL_G
379 ,END_ONH_QTY
380 ,END_ONH_VAL_B
381 ,END_ONH_VAL_G
382 ,END_WIP_QTY
383 ,END_WIP_VAL_B
384 ,END_WIP_VAL_G
385 ,FROM_ORG_QTY
386 ,FROM_ORG_VAL_B
387 ,FROM_ORG_VAL_G
388 ,INSTANCE_FK
389 ,INV_ADJ_QTY
390 ,INV_ADJ_VAL_B
391 ,INV_ADJ_VAL_G
392 ,INV_DAILY_STATUS_PK
393 ,INV_ORG_FK
394 ,ITEM_ORG_FK
395 ,ITEM_STATUS
396 ,ITEM_TYPE
397 ,LAST_UPDATE_DATE
398 ,LOCATOR_FK
399 ,LOT_FK
400 ,NETTABLE_FLAG
401 ,PO_DEL_QTY
402 ,PO_DEL_VAL_B
403 ,PO_DEL_VAL_G
404 ,PRD_DATE_FK
405 ,TOTAL_REC_QTY
406 ,TOTAL_REC_VAL_B
407 ,TOTAL_REC_VAL_G
408 ,TOT_CUST_SHIP_QTY
409 ,TOT_CUST_SHIP_VAL_B
410 ,TOT_CUST_SHIP_VAL_G
411 ,TOT_ISSUES_QTY
412 ,TOT_ISSUES_VAL_B
413 ,TOT_ISSUES_VAL_G
414 ,TO_ORG_QTY
415 ,TO_ORG_VAL_B
416 ,TO_ORG_VAL_G
417 ,TRX_DATE_FK
418 ,USER_ATTRIBUTE1
419 ,USER_ATTRIBUTE10
420 ,USER_ATTRIBUTE11
421 ,USER_ATTRIBUTE12
422 ,USER_ATTRIBUTE13
423 ,USER_ATTRIBUTE14
424 ,USER_ATTRIBUTE15
425 ,USER_ATTRIBUTE2
426 ,USER_ATTRIBUTE3
427 ,USER_ATTRIBUTE4
428 ,USER_ATTRIBUTE5
429 ,USER_ATTRIBUTE6
430 ,USER_ATTRIBUTE7
431 ,USER_ATTRIBUTE8
432 ,USER_ATTRIBUTE9
433 ,USER_FK1
434 ,USER_FK2
435 ,USER_FK3
436 ,USER_FK4
437 ,USER_FK5
438 ,USER_MEASURE1
439 ,USER_MEASURE2
440 ,USER_MEASURE3
441 ,USER_MEASURE4
442 ,USER_MEASURE5
443 ,WIP_ASSY_QTY
444 ,WIP_ASSY_VAL_B
445 ,WIP_ASSY_VAL_G
446 ,WIP_COMP_QTY
447 ,WIP_COMP_VAL_B
448 ,WIP_COMP_VAL_G
449 ,WIP_ISSUE_QTY
450 ,WIP_ISSUE_VAL_B
451 ,WIP_ISSUE_VAL_G
452 ,TRX_DATE
453 ,PERIOD_FLAG
454 ,NULL -- OPERATION_CODE
455 ,'LOCAL READY'
456 from opi_edw_opiinv_daily_stat_fcv
457 where push_flag=1
458 AND edw_conv_rate IS NOT NULL;
459
460 l_row_count := sql%rowcount;
461
462 --dbms_output.put_line('after insert into fstg ' || to_char(Sysdate, 'hh24:mi:ss'));
463
464
465 end if; -- row_pushed record
466 -- --------------------------------------------
467 -- No exception raised so far. Call wrapup to transport
468 -- data to target database, and insert messages into logs
469 -- -----------------------------------------------
470 edw_log.put_line(' ');
471 edw_log.put_line('Inserted '||nvl(l_row_count,0)||
472 ' rows into the staging table');
473 edw_log.put_line(' Calling EDW_COLLECTION_UTIL.wrapup');
474
475 EDW_COLLECTION_UTIL.wrapup(TRUE,
476 l_row_count,
477 l_exception_msg,
478 l_from_date,
479 l_to_date);
480
481 edw_log.put_line(' Calling UPDATE opi_ids_push_log');
482 --dbms_output.put_line('after wrapup ' || to_char(Sysdate, 'hh24:mi:ss'));
483 UPDATE opi_ids_push_log
484 SET push_flag=0,
485 last_update_date=sysdate
486 WHERE push_flag=1
487 AND edw_conv_rate IS NOT NULL;
488
489 COMMIT;
490
491 IF NOT l_global_conv_rate_flag THEN
492 RAISE currency_conv_rate_not_exist;
493 END IF;
494
495 --dbms_output.put_line('after push flag = 0 ' || to_char(Sysdate, 'hh24:mi:ss'));
496 -- ---------------------------------------------------------------------------
497 -- END OF Collection , Developer Customizable Section
498 -- ---------------------------------------------------------------------------
499 IF opimpxwi.g_org_error THEN
500 Errbuf:= 'Please check log file for details.';
501
502 Retcode:= 1; -- completed with warning
503 END IF;
504
505 EXCEPTION
506 WHEN currency_conv_rate_not_exist THEN
507 Errbuf:= 'No conversion rate existed. Please check log file for details.';
508
509 Retcode:= 1; -- completed with warning
510 l_exception_msg := Retcode || ':' || Errbuf;
511 rollback;
512 edw_log.put_line( l_exception_msg);
513 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
514 l_from_date, l_to_date);
515 --dbms_output.put_line('currency_conv_rate_not_exist ' || l_exception_msg);
516 --raise;
517
518 WHEN currency_not_exist THEN
519 Errbuf:= 'No or too many rows existed in EDW_LOCAL_SYSTEM_PARAMETERS table';
520
521 Retcode:=sqlcode;
522 l_exception_msg := Retcode || ':' || Errbuf;
523 rollback;
524 edw_log.put_line( l_exception_msg);
525 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
526 l_from_date, l_to_date);
527 --dbms_output.put_line('no or too many ' || l_exception_msg);
528 raise;
529 WHEN OTHERS THEN
530 Errbuf:= Sqlerrm;
531 Retcode:=sqlcode;
532 l_exception_msg := Retcode || ':' || Errbuf;
533 rollback;
534 edw_log.put_line('Other errors');
535 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
536 l_from_date, l_to_date);
537 raise;
538
539
540 END push;
541
542 End OPI_EDW_INV_DAILY_STAT_F_C ;