[Home] [Help]
PACKAGE BODY: APPS.OPI_COLLECTION_HOOK_P
Source
1 Package body OPI_COLLECTION_HOOK_P AS
2 /*$Header: OPICOLLB.pls 120.0 2005/05/24 17:56:39 appldev noship $ */
3
4 /* Constants for session - including schema name for truncating and
5 collecting stats */
6 g_opi_schema VARCHAR2(30);
7 g_status VARCHAR2(30);
8 g_industry VARCHAR2(30);
9
10 Procedure POST_IPS_COLL(FACT_NAME IN Varchar2)IS
11 BEGIN
12
13 /* get global session parameters */
14 IF NOT (fnd_installation.get_app_info( 'OPI', g_status,
15 g_industry, g_opi_schema)) THEN
16 RAISE_APPLICATION_ERROR (-20000, 'Unable to get session information.');
17 END IF;
18
19 execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IDS_OPICOLLB_LOG';
20 execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IDS_NET_CHG';
21 execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IPS_NET_CHG';
22
23 COMMIT;
24
25 -- Gather stats on the daily status fact to optimize joins to it.
26 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_INV_DAILY_STAT_F');
27
28
29 EDW_OWB_COLLECTION_UTIL.write_to_log_file('get distinct row ids from IDS Snapshot Log log table');
30 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
31 /* get distinct row ids from IDS Snapshot Log log table */
32 IF FACT_NAME = 'OPI_EDW_INV_DAILY_STAT_F' THEN
33 INSERT INTO OPI_EDW_IDS_OPICOLLB_LOG (ROW_ID)
34 SELECT DISTINCT M_ROW$$
35 FROM MLOG$_OPI_EDW_INV_DAILY_ST ;
36 ELSIF FACT_NAME = 'OPI_EDW_INV_PERD_STAT_F' THEN
37 INSERT INTO OPI_EDW_IDS_OPICOLLB_LOG (ROW_ID)
38 SELECT ROWID
39 FROM OPI_EDW_INV_DAILY_STAT_F
40 WHERE period_flag = 1 ; -- add this condition to minimize join below for the
41 -- initial load
42 END IF;
43
44 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows changed in IDS'||SQL%ROWCOUNT);
45 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
46
47 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_IDS_OPICOLLB_LOG');
48
49 /* Move Non Attribute Columns into Tep Table 1 */
50
51 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Populate Non key attributes into OPI_EDW_IDS_NET_CHG Table');
52 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
53
54
55 INSERT INTO OPI_EDW_IDS_NET_CHG(
56 BASE_UOM_FK_KEY,
57 BASE_CURRENCY_FK_KEY,
58 INSTANCE_FK_KEY,
59 INV_ORG_FK_KEY,
60 ITEM_ORG_FK_KEY,
61 LOCATOR_FK_KEY,
62 LOT_FK_KEY,
63 PRD_DATE_FK_KEY,
64 COST_GROUP,
65 NETTABLE_FLAG,
66 ITEM_STATUS)
67 SELECT /*+ROWID(OPI_EDW_INV_DAILY_STAT_F)*/
68 BASE_UOM_FK_KEY,
69 BASE_CURRENCY_FK_KEY,
70 INSTANCE_FK_KEY,
71 INV_ORG_FK_KEY,
72 ITEM_ORG_FK_KEY,
73 LOCATOR_FK_KEY,
74 LOT_FK_KEY,
75 PRD_DATE_FK_KEY,
76 COST_GROUP,
77 MAX(NETTABLE_FLAG),
78 MAX(ITEM_STATUS)
79 FROM OPI_EDW_INV_DAILY_STAT_F IDS, OPI_EDW_IDS_OPICOLLB_LOG CHANGED
80 WHERE IDS.ROWID = CHANGED.ROW_ID
81 AND IDS.PERIOD_FLAG = 1
82 GROUP BY
83 BASE_UOM_FK_KEY,
84 BASE_CURRENCY_FK_KEY,
85 INSTANCE_FK_KEY,
86 INV_ORG_FK_KEY,
87 ITEM_ORG_FK_KEY,
88 LOCATOR_FK_KEY,
89 LOT_FK_KEY,
90 PRD_DATE_FK_KEY,
91 COST_GROUP;
92
93 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Populated Non key attributes into OPI_EDW_IDS_NET_CHG Table'||SQL%ROWCOUNT);
94 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
95
96 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_IDS_NET_CHG');
97
98 /* Move net change data from IPS and Temp OPI_EDW_IPS_NET_CHG */
99
100 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Move net change data from IPS and Temp OPI_EDW_IPS_NET_CHG ');
101 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
102
103 INSERT INTO OPI_EDW_IPS_NET_CHG
104 ( ROW_ID
105 ,AVG_INT_QTY
106 ,AVG_INT_VAL_B
107 ,AVG_INT_VAL_G
108 ,AVG_ONH_QTY
109 ,AVG_ONH_VAL_B
110 ,AVG_ONH_VAL_G
111 ,AVG_WIP_QTY
112 ,AVG_WIP_VAL_B
113 ,AVG_WIP_VAL_G
114 ,BASE_CURRENCY_FK_KEY
115 ,BASE_UOM_FK_KEY
116 ,BEG_INT_QTY
117 ,BEG_INT_VAL_B
118 ,BEG_INT_VAL_G
119 ,BEG_ONH_QTY
120 ,BEG_ONH_VAL_B
121 ,BEG_ONH_VAL_G
122 ,BEG_WIP_QTY
123 ,BEG_WIP_VAL_B
124 ,BEG_WIP_VAL_G
125 ,COST_GROUP
126 ,CREATION_DATE
127 ,END_INT_QTY
128 ,END_INT_VAL_B
129 ,END_INT_VAL_G
130 ,END_ONH_QTY
131 ,END_ONH_VAL_B
132 ,END_ONH_VAL_G
133 ,END_WIP_QTY
134 ,END_WIP_VAL_B
135 ,END_WIP_VAL_G
136 ,FROM_ORG_QTY
137 ,FROM_ORG_VAL_B
138 ,FROM_ORG_VAL_G
139 ,INSTANCE_FK_KEY
140 ,INV_ADJ_QTY
141 ,INV_ADJ_VAL_B
142 ,INV_ADJ_VAL_G
143 ,INV_ORG_FK_KEY
144 ,INV_PERIOD_STATUS_PK
145 ,INV_PERIOD_STATUS_PK_KEY
146 ,ITEM_ORG_FK_KEY
147 ,ITEM_STATUS
148 ,LAST_UPDATE_DATE
149 ,LOCATOR_FK_KEY
150 ,LOT_FK_KEY
151 ,NETTABLE_FLAG
152 ,PO_DEL_QTY
153 ,PO_DEL_VAL_B
154 ,PO_DEL_VAL_G
155 ,PRD_DATE_FK_KEY
156 ,TOTAL_REC_QTY
157 ,TOTAL_REC_VAL_B
158 ,TOTAL_REC_VAL_G
159 ,TOT_CUST_SHIP_QTY
160 ,TOT_CUST_SHIP_VAL_B
161 ,TOT_CUST_SHIP_VAL_G
162 ,TOT_ISSUES_QTY
163 ,TOT_ISSUES_VAL_B
164 ,TOT_ISSUES_VAL_G
165 ,TO_ORG_QTY
166 ,TO_ORG_VAL_B
167 ,TO_ORG_VAL_G
168 ,USER_ATTRIBUTE1
169 ,USER_ATTRIBUTE10
170 ,USER_ATTRIBUTE11
171 ,USER_ATTRIBUTE12
172 ,USER_ATTRIBUTE13
173 ,USER_ATTRIBUTE14
174 ,USER_ATTRIBUTE15
175 ,USER_ATTRIBUTE2
176 ,USER_ATTRIBUTE3
177 ,USER_ATTRIBUTE4
178 ,USER_ATTRIBUTE5
179 ,USER_ATTRIBUTE6
180 ,USER_ATTRIBUTE7
181 ,USER_ATTRIBUTE8
182 ,USER_ATTRIBUTE9
183 ,USER_FK1_KEY
184 ,USER_FK2_KEY
185 ,USER_FK3_KEY
186 ,USER_FK4_KEY
187 ,USER_FK5_KEY
188 ,USER_MEASURE1
189 ,USER_MEASURE2
190 ,USER_MEASURE3
191 ,USER_MEASURE4
192 ,USER_MEASURE5
193 ,WIP_ASSY_QTY
194 ,WIP_ASSY_VAL_B
195 ,WIP_ASSY_VAL_G
196 ,WIP_COMP_QTY
197 ,WIP_COMP_VAL_B
198 ,WIP_COMP_VAL_G
199 ,WIP_ISSUE_QTY
200 ,WIP_ISSUE_VAL_B
201 ,WIP_ISSUE_VAL_G)
202 SELECT
203 IPS.ROWID
204 , IPS.AVG_INT_QTY
205 , IPS.AVG_INT_VAL_B
206 , IPS.AVG_INT_VAL_G
207 , IPS.AVG_ONH_QTY
208 , IPS.AVG_ONH_VAL_B
209 , IPS.AVG_ONH_VAL_G
210 , IPS.AVG_WIP_QTY
211 , IPS.AVG_WIP_VAL_B
212 , IPS.AVG_WIP_VAL_G
213 , IPS.BASE_CURRENCY_FK_KEY
214 , IPS.BASE_UOM_FK_KEY
215 , IPS.BEG_INT_QTY
216 , IPS.BEG_INT_VAL_B
217 , IPS.BEG_INT_VAL_G
218 , IPS.BEG_ONH_QTY
219 , IPS.BEG_ONH_VAL_B
220 , IPS.BEG_ONH_VAL_G
221 , IPS.BEG_WIP_QTY
222 , IPS.BEG_WIP_VAL_B
223 , IPS.BEG_WIP_VAL_G
224 , IPS.COST_GROUP
225 , IPS.CREATION_DATE
226 , IPS.END_INT_QTY
227 , IPS.END_INT_VAL_B
228 , IPS.END_INT_VAL_G
229 , IPS.END_ONH_QTY
230 , IPS.END_ONH_VAL_B
231 , IPS.END_ONH_VAL_G
232 , IPS.END_WIP_QTY
233 , IPS.END_WIP_VAL_B
234 , IPS.END_WIP_VAL_G
235 , IPS.FROM_ORG_QTY
236 , IPS.FROM_ORG_VAL_B
237 , IPS.FROM_ORG_VAL_G
238 , IPS.INSTANCE_FK_KEY
239 , IPS.INV_ADJ_QTY
240 , IPS.INV_ADJ_VAL_B
241 , IPS.INV_ADJ_VAL_G
242 , IPS.INV_ORG_FK_KEY
243 , IPS.INV_PERIOD_STATUS_PK
244 , IPS.INV_PERIOD_STATUS_PK_KEY
245 , IPS.ITEM_ORG_FK_KEY
246 , WK2.ITEM_STATUS
247 , IPS.LAST_UPDATE_DATE
248 , IPS.LOCATOR_FK_KEY
249 , IPS.LOT_FK_KEY
250 , WK2.NETTABLE_FLAG
251 , IPS.PO_DEL_QTY
252 , IPS.PO_DEL_VAL_B
253 , IPS.PO_DEL_VAL_G
254 , IPS.PRD_DATE_FK_KEY
255 , IPS.TOTAL_REC_QTY
256 , IPS.TOTAL_REC_VAL_B
257 , IPS.TOTAL_REC_VAL_G
258 , IPS.TOT_CUST_SHIP_QTY
259 , IPS.TOT_CUST_SHIP_VAL_B
260 , IPS.TOT_CUST_SHIP_VAL_G
261 , IPS.TOT_ISSUES_QTY
262 , IPS.TOT_ISSUES_VAL_B
263 , IPS.TOT_ISSUES_VAL_G
264 , IPS.TO_ORG_QTY
265 , IPS.TO_ORG_VAL_B
266 , IPS.TO_ORG_VAL_G
267 , IPS.USER_ATTRIBUTE1
268 , IPS.USER_ATTRIBUTE10
269 , IPS.USER_ATTRIBUTE11
270 , IPS.USER_ATTRIBUTE12
271 , IPS.USER_ATTRIBUTE13
272 , IPS.USER_ATTRIBUTE14
273 , IPS.USER_ATTRIBUTE15
274 , IPS.USER_ATTRIBUTE2
275 , IPS.USER_ATTRIBUTE3
276 , IPS.USER_ATTRIBUTE4
277 , IPS.USER_ATTRIBUTE5
278 , IPS.USER_ATTRIBUTE6
279 , IPS.USER_ATTRIBUTE7
280 , IPS.USER_ATTRIBUTE8
281 , IPS.USER_ATTRIBUTE9
282 , IPS.USER_FK1_KEY
283 , IPS.USER_FK2_KEY
284 , IPS.USER_FK3_KEY
285 , IPS.USER_FK4_KEY
286 , IPS.USER_FK5_KEY
287 , IPS.USER_MEASURE1
288 , IPS.USER_MEASURE2
289 , IPS.USER_MEASURE3
290 , IPS.USER_MEASURE4
291 , IPS.USER_MEASURE5
292 , IPS.WIP_ASSY_QTY
293 , IPS.WIP_ASSY_VAL_B
294 , IPS.WIP_ASSY_VAL_G
295 , IPS.WIP_COMP_QTY
296 , IPS.WIP_COMP_VAL_B
297 , IPS.WIP_COMP_VAL_G
298 , IPS.WIP_ISSUE_QTY
299 , IPS.WIP_ISSUE_VAL_B
300 , IPS.WIP_ISSUE_VAL_G
301 FROM OPI_EDW_INV_PERD_STAT_F IPS, OPI_EDW_IDS_NET_CHG WK2
302 WHERE
303 IPS.BASE_UOM_FK_KEY = WK2.BASE_UOM_FK_KEY AND
304 IPS.BASE_CURRENCY_FK_KEY=WK2.BASE_CURRENCY_FK_KEY AND
305 IPS.INSTANCE_FK_KEY=WK2.INSTANCE_FK_KEY AND
306 IPS.INV_ORG_FK_KEY =WK2.INV_ORG_FK_KEY AND
307 IPS.ITEM_ORG_FK_KEY=WK2.ITEM_ORG_FK_KEY AND
308 IPS.LOCATOR_FK_KEY=WK2.LOCATOR_FK_KEY AND
309 IPS.LOT_FK_KEY=WK2.LOT_FK_KEY AND
310 IPS.PRD_DATE_FK_KEY=WK2.PRD_DATE_FK_KEY AND
311 NVL(IPS.COST_GROUP,'0') = NVL(WK2.COST_GROUP,'0');
312
313 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Moved net change data from IPS and Temp OPI_EDW_IPS_NET_CHG ' ||SQL%ROWCOUNT);
314 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
315
316 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_IPS_NET_CHG');
317
318 /* Delete IPS table for Changed rows */
319
320 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Deleting Changed rows from IPS ');
321 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
322
323 DELETE OPI_EDW_INV_PERD_STAT_F
324 WHERE ROWID IN (SELECT ROW_ID FROM OPI_EDW_IPS_NET_CHG);
325
326 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Deleted Changed rows from IPS ' ||SQL%ROWCOUNT);
327 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
328
329
330 /* insert data from temp Table to IPS */
331
332 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Moving Data from Work table to IPS');
333 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
334
335
336 INSERT INTO OPI_EDW_INV_PERD_STAT_F (
337 AVG_INT_QTY
338 ,AVG_INT_VAL_B
339 ,AVG_INT_VAL_G
340 ,AVG_ONH_QTY
341 ,AVG_ONH_VAL_B
342 ,AVG_ONH_VAL_G
343 ,AVG_WIP_QTY
344 ,AVG_WIP_VAL_B
345 ,AVG_WIP_VAL_G
346 ,BASE_CURRENCY_FK_KEY
347 ,BASE_UOM_FK_KEY
348 ,BEG_INT_QTY
349 ,BEG_INT_VAL_B
350 ,BEG_INT_VAL_G
351 ,BEG_ONH_QTY
352 ,BEG_ONH_VAL_B
353 ,BEG_ONH_VAL_G
354 ,BEG_WIP_QTY
355 ,BEG_WIP_VAL_B
356 ,BEG_WIP_VAL_G
357 ,COST_GROUP
358 ,CREATION_DATE
359 ,END_INT_QTY
360 ,END_INT_VAL_B
361 ,END_INT_VAL_G
362 ,END_ONH_QTY
363 ,END_ONH_VAL_B
364 ,END_ONH_VAL_G
365 ,END_WIP_QTY
366 ,END_WIP_VAL_B
367 ,END_WIP_VAL_G
368 ,FROM_ORG_QTY
369 ,FROM_ORG_VAL_B
370 ,FROM_ORG_VAL_G
371 ,INSTANCE_FK_KEY
372 ,INV_ADJ_QTY
373 ,INV_ADJ_VAL_B
374 ,INV_ADJ_VAL_G
375 ,INV_ORG_FK_KEY
376 ,INV_PERIOD_STATUS_PK
377 ,INV_PERIOD_STATUS_PK_KEY
378 ,ITEM_ORG_FK_KEY
379 ,ITEM_STATUS
380 ,LAST_UPDATE_DATE
381 ,LOCATOR_FK_KEY
382 ,LOT_FK_KEY
383 ,NETTABLE_FLAG
384 ,PO_DEL_QTY
385 ,PO_DEL_VAL_B
386 ,PO_DEL_VAL_G
387 ,PRD_DATE_FK_KEY
388 ,TOTAL_REC_QTY
389 ,TOTAL_REC_VAL_B
390 ,TOTAL_REC_VAL_G
391 ,TOT_CUST_SHIP_QTY
392 ,TOT_CUST_SHIP_VAL_B
393 ,TOT_CUST_SHIP_VAL_G
394 ,TOT_ISSUES_QTY
395 ,TOT_ISSUES_VAL_B
396 ,TOT_ISSUES_VAL_G
397 ,TO_ORG_QTY
398 ,TO_ORG_VAL_B
399 ,TO_ORG_VAL_G
400 ,USER_ATTRIBUTE1
401 ,USER_ATTRIBUTE10
402 ,USER_ATTRIBUTE11
403 ,USER_ATTRIBUTE12
404 ,USER_ATTRIBUTE13
405 ,USER_ATTRIBUTE14
406 ,USER_ATTRIBUTE15
407 ,USER_ATTRIBUTE2
408 ,USER_ATTRIBUTE3
409 ,USER_ATTRIBUTE4
410 ,USER_ATTRIBUTE5
411 ,USER_ATTRIBUTE6
412 ,USER_ATTRIBUTE7
413 ,USER_ATTRIBUTE8
414 ,USER_ATTRIBUTE9
415 ,USER_FK1_KEY
416 ,USER_FK2_KEY
417 ,USER_FK3_KEY
418 ,USER_FK4_KEY
419 ,USER_FK5_KEY
420 ,USER_MEASURE1
421 ,USER_MEASURE2
422 ,USER_MEASURE3
423 ,USER_MEASURE4
424 ,USER_MEASURE5
425 ,WIP_ASSY_QTY
426 ,WIP_ASSY_VAL_B
427 ,WIP_ASSY_VAL_G
428 ,WIP_COMP_QTY
429 ,WIP_COMP_VAL_B
430 ,WIP_COMP_VAL_G
431 ,WIP_ISSUE_QTY
432 ,WIP_ISSUE_VAL_B
433 ,WIP_ISSUE_VAL_G)
434 SELECT
435 (BEG_INT_QTY+END_INT_QTY)/2 AVG_INT_QTY
436 ,(BEG_INT_VAL_B+END_INT_VAL_B)/2 AVG_INT_VAL_B
437 ,(BEG_INT_VAL_G+END_INT_VAL_G)/2 AVG_INT_VAL_G
438 ,(BEG_ONH_QTY+END_ONH_QTY)/2 AVG_ONH_QTY
439 ,(BEG_ONH_VAL_B+END_ONH_VAL_B)/2 AVG_ONH_VAL_B
440 ,(BEG_ONH_VAL_G+END_ONH_VAL_G)/2 AVG_ONH_VAL_G
441 ,(BEG_WIP_QTY+END_WIP_QTY)/2 AVG_WIP_QTY
442 , (BEG_WIP_VAL_B+END_WIP_VAL_B)/2 AVG_WIP_VAL_B
443 ,(BEG_WIP_VAL_G+END_WIP_VAL_G)/2 AVG_WIP_VAL_G
444 ,BASE_CURRENCY_FK_KEY
445 ,BASE_UOM_FK_KEY
446 ,BEG_INT_QTY
447 ,BEG_INT_VAL_B
448 ,BEG_INT_VAL_G
449 ,BEG_ONH_QTY
450 ,BEG_ONH_VAL_B
451 ,BEG_ONH_VAL_G
452 ,BEG_WIP_QTY
453 ,BEG_WIP_VAL_B
454 ,BEG_WIP_VAL_G
455 ,COST_GROUP
456 ,CREATION_DATE
457 ,END_INT_QTY
458 ,END_INT_VAL_B
459 ,END_INT_VAL_G
460 ,END_ONH_QTY
461 ,END_ONH_VAL_B
462 ,END_ONH_VAL_G
466 ,FROM_ORG_QTY
463 ,END_WIP_QTY
464 ,END_WIP_VAL_B
465 ,END_WIP_VAL_G
467 ,FROM_ORG_VAL_B
468 ,FROM_ORG_VAL_G
469 ,INSTANCE_FK_KEY
470 ,INV_ADJ_QTY
471 ,INV_ADJ_VAL_B
472 ,INV_ADJ_VAL_G
473 ,INV_ORG_FK_KEY
474 ,INV_PERIOD_STATUS_PK
475 ,INV_PERIOD_STATUS_PK_KEY
476 ,ITEM_ORG_FK_KEY
477 ,ITEM_STATUS
478 ,LAST_UPDATE_DATE
479 ,LOCATOR_FK_KEY
480 ,LOT_FK_KEY
481 ,NETTABLE_FLAG
482 ,PO_DEL_QTY
483 ,PO_DEL_VAL_B
484 ,PO_DEL_VAL_G
485 ,PRD_DATE_FK_KEY
486 ,TOTAL_REC_QTY
487 ,TOTAL_REC_VAL_B
488 ,TOTAL_REC_VAL_G
489 ,TOT_CUST_SHIP_QTY
490 ,TOT_CUST_SHIP_VAL_B
491 ,TOT_CUST_SHIP_VAL_G
492 ,TOT_ISSUES_QTY
493 ,TOT_ISSUES_VAL_B
494 ,TOT_ISSUES_VAL_G
495 ,TO_ORG_QTY
496 ,TO_ORG_VAL_B
497 ,TO_ORG_VAL_G
498 ,USER_ATTRIBUTE1
499 ,USER_ATTRIBUTE10
500 ,USER_ATTRIBUTE11
501 ,USER_ATTRIBUTE12
502 ,USER_ATTRIBUTE13
503 ,USER_ATTRIBUTE14
504 ,USER_ATTRIBUTE15
505 ,USER_ATTRIBUTE2
506 ,USER_ATTRIBUTE3
507 ,USER_ATTRIBUTE4
508 ,USER_ATTRIBUTE5
509 ,USER_ATTRIBUTE6
510 ,USER_ATTRIBUTE7
511 ,USER_ATTRIBUTE8
512 ,USER_ATTRIBUTE9
513 ,USER_FK1_KEY
514 ,USER_FK2_KEY
515 ,USER_FK3_KEY
516 ,USER_FK4_KEY
517 ,USER_FK5_KEY
518 ,USER_MEASURE1
519 ,USER_MEASURE2
520 ,USER_MEASURE3
521 ,USER_MEASURE4
522 ,USER_MEASURE5
523 ,WIP_ASSY_QTY
524 ,WIP_ASSY_VAL_B
525 ,WIP_ASSY_VAL_G
526 ,WIP_COMP_QTY
527 ,WIP_COMP_VAL_B
528 ,WIP_COMP_VAL_G
529 ,WIP_ISSUE_QTY
530 ,WIP_ISSUE_VAL_B
531 ,WIP_ISSUE_VAL_G
532 FROM OPI_EDW_IPS_NET_CHG;
533
534 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Moved Data from Work table to IPS' ||SQL%ROWCOUNT);
535 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
536
537 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_INV_PERD_STAT_F');
538
539 execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IDS_OPICOLLB_LOG';
540 execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IDS_NET_CHG';
541 execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IPS_NET_CHG';
542
543 COMMIT;
544
545 END;
546
547 PROCEDURE POST_MARGIN_COLL(p_Base_fact_name VARCHAR2) IS
548 l_table_owner VARCHAR2(40);
549 l_stmt VARCHAR2(200);
550 l_chunk_size NUMBER := NVL(fnd_profile.value('EDW_COLLECTION_SIZE'),20000);
551 l_insert NUMBER := 0;
552 BEGIN
553
554 --
555 --
556 -- Clean up Temporary work tables data. if previous run failed these tables will have data.
557 --
558 --
559 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
560 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
561 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
562 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
566 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
567 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
568 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
569 --
570 --
571 -- Clean up section is over
572 --
573 --
574
575
576 --
577 --
578 -- Identify the rows which are modified or deleted in margin fact.
579 -- First add all the changed rows from margin to period summary
580 -- then subtract old rows backed up by following insert statement.
581 --
582 --
583 IF p_base_fact_name <> 'OPI_EDW_MARGIN_F' THEN
584 EXECUTE IMMEDIATE ' insert into OPI_EDW_MARGIN_PERD_FDLG (SOB_FK_KEY
585 ,USER_MEASURE5
586 ,USER_MEASURE4
587 ,USER_MEASURE3
588 ,USER_MEASURE2
589 ,USER_MEASURE1
590 ,ICAP_QTY
591 ,RMA_QTY
592 ,SHIPPED_QTY
593 ,PROD_LINE_QTY_CREDITED
594 ,PROD_LINE_QTY_INVOICED
595 ,PROD_AMT_B
596 ,PROD_AMT_G
597 ,COGS_B
598 ,COGS_G
599 ,UOM_FK_KEY
600 ,PROJECT_FK_KEY
601 ,GL_PERIOD_FK_KEY
602 ,SHIP_TO_LOC_FK_KEY
603 ,BILL_TO_LOC_FK_KEY
604 ,PRIM_SALES_REP_FK_KEY
605 ,INSTANCE_FK_KEY
606 ,SALES_CHANNEL_FK_KEY
607 ,CUSTOMER_FK_KEY
608 ,OPERATING_UNIT_FK_KEY
609 ,ITEM_ORG_FK_KEY
610 ,BASE_CURRENCY_FK_KEY
611 ,COGS_DATE_FK_KEY
612 ,INVOICE_DATE
613 ,MARGIN_DATE_FK_KEY
614 ,ORDER_DATE
615 ,ORDER_LINE_ID
616 ,ORDER_NO
617 ,REVENUE_DATE_FK_KEY
618 ,SHIP_DATE
619 ,SHIP_LOCATION_FK_KEY
620 ,UNIT_SELLING_PRICE)
621 SELECT
622 SOB_FK_KEY
623 ,USER_MEASURE5
624 ,USER_MEASURE4
625 ,USER_MEASURE3
626 ,USER_MEASURE2
627 ,USER_MEASURE1
628 ,ICAP_QTY
629 ,RMA_QTY
630 ,SHIPPED_QTY
631 ,PROD_LINE_QTY_CREDITED
635 ,COGS_B
632 ,PROD_LINE_QTY_INVOICED
633 ,PROD_AMT_B
634 ,PROD_AMT_G
636 ,COGS_G
637 ,UOM_FK_KEY
638 ,PROJECT_FK_KEY
639 ,GL_PERIOD_FK_KEY
640 ,SHIP_TO_LOC_FK_KEY
641 ,BILL_TO_LOC_FK_KEY
642 ,PRIM_SALES_REP_FK_KEY
643 ,INSTANCE_FK_KEY
644 ,SALES_CHANNEL_FK_KEY
645 ,CUSTOMER_FK_KEY
646 ,OPERATING_UNIT_FK_KEY
647 ,ITEM_ORG_FK_KEY
648 ,BASE_CURRENCY_FK_KEY
649 ,COGS_DATE_FK_KEY
650 ,INVOICE_DATE
651 ,MARGIN_DATE_FK_KEY
652 ,ORDER_DATE
653 ,ORDER_LINE_ID
654 ,ORDER_NO
655 ,REVENUE_DATE_FK_KEY
656 ,SHIP_DATE
657 ,SHIP_LOCATION_FK_KEY
658 ,UNIT_SELLING_PRICE
659 FROM
660 (
661 SELECT
662 SOB_FK_KEY
663 ,USER_MEASURE5
664 ,USER_MEASURE4
665 ,USER_MEASURE3
666 ,USER_MEASURE2
667 ,USER_MEASURE1
668 ,ICAP_QTY
669 ,RMA_QTY
670 ,SHIPPED_QTY
671 ,PROD_LINE_QTY_CREDITED
672 ,PROD_LINE_QTY_INVOICED
673 ,PROD_AMT_B
674 ,PROD_AMT_G
675 ,COGS_B
676 ,COGS_G
677 ,UOM_FK_KEY
678 ,PROJECT_FK_KEY
679 ,GL_PERIOD_FK_KEY
680 ,SHIP_TO_LOC_FK_KEY
681 ,BILL_TO_LOC_FK_KEY
682 ,PRIM_SALES_REP_FK_KEY
683 ,INSTANCE_FK_KEY
684 ,SALES_CHANNEL_FK_KEY
685 ,CUSTOMER_FK_KEY
686 ,OPERATING_UNIT_FK_KEY
687 ,ITEM_ORG_FK_KEY
688 ,BASE_CURRENCY_FK_KEY
689 ,COGS_DATE_FK_KEY
690 ,INVOICE_DATE
691 ,MARGIN_DATE_FK_KEY
692 ,ORDER_DATE
693 ,ORDER_LINE_ID
694 ,ORDER_NO
695 ,REVENUE_DATE_FK_KEY
696 ,SHIP_DATE
697 ,SHIP_LOCATION_FK_KEY
698 ,UNIT_SELLING_PRICE
699 ,LAST_UPDATE_DATE
700 ,MIN(LAST_UPDATE_DATE) OVER (PARTITION BY M_ROW$$) FIRST_UPDATE_DATE
701 FROM MLOG$_OPI_EDW_MARGIN_F a
702 WHERE a.DMLTYPE$$ IN (''D'',''U'')
703 AND a.GL_PERIOD_FK_KEY IS NOT NULL
704 AND a.GL_PERIOD_FK_KEY <> ''0'' )
705 WHERE
706 FIRST_UPDATE_DATE = LAST_UPDATE_DATE';
707
708 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows inserted into OPI_EDW_MARGIN_PERD_FDLG '||SQL%ROWCOUNT);
709 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
710 commit;
711 --
712 --
713 -- Gather table statistics these stats will be used by CBO for query optimisation.
714 --
715 --
716
717 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MARGIN_PERD_FDLG');
718
719 insert into OPI_EDW_MARGIN_PERD_FD (select rowid ,0 from OPI_EDW_MARGIN_PERD_FDLG);
720
721 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows inserted into OPI_EDW_MARGIN_PERD_FD '||SQL%ROWCOUNT);
722 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
723 commit;
724
725 --
726 --
727 -- Gather table statistics these stats will be used by CBO for query optimisation.
728 --
729 --
730
731 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MARGIN_PERD_FD');
732
733 ELSE
734 /***********************************************************************************************************
735 *** Trancate the Period Margin Fact because margin fact is rebuilt so we need to rebuild period summary ****
736 ************************************************************************************************************/
737 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_F');
738 END IF;
739
740 --
741 --
742 -- Gather table statistics these stats will be used by CBO for query optimisation.
743 --
744 --
745
746 OPI_COLLECTION_HOOK_P.GATHER_STATS('MLOG$_OPI_EDW_MARGIN_F');
747
748 --
749 --
750 -- Take the backup of changed rows
751 --
752 --
753
754 insert into OPI_EDW_MARGIN_PERD_ILOG(ROW_ID,STATUS) (select /*+ ALL_ROWS */ distinct m_row$$ ,0 from MLOG$_OPI_EDW_MARGIN_F);
755
756 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows to be processed to build margin period summary'||SQL%ROWCOUNT);
757 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
758
759 --
760 --
761 -- Gather table statistics these stats will be used by CBO for query optimisation.
762 --
763 --
764
765 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MARGIN_PERD_ILOG');
766
767 commit;
768
769
770 /****************************************************************************************+
771 *** Update Margin Base fact with non common keys and attributes from base fact ***
772 *** COGS and REVENUE based on Fact name passed by collection Engine. ***
773 +*****************************************************************************************/
774 IF p_Base_fact_name = 'FII_AR_TRX_DIST_F' /* Revenue Fact */
775 THEN
776 INSERT INTO OPI_EDW_MRG_KEYS (ORDER_LINE_ID,
777 UOM_FK_KEY,
778 PROJECT_FK_KEY,
779 SHIP_TO_LOC_FK_KEY,
780 BILL_TO_LOC_FK_KEY,
781 PRIM_SALES_REP_FK_KEY,
782 INSTANCE_FK_KEY,
786 ITEM_ORG_FK_KEY,
783 SALES_CHANNEL_FK_KEY,
784 CUSTOMER_FK_KEY,
785 OPERATING_UNIT_FK_KEY,
787 BASE_CURRENCY_FK_KEY,
788 SOB_FK_KEY)
789 select DISTINCT ORDER_LINE_ID, /*bug3331025 - Removed RULE Hint*/
790 UOM_FK_KEY,
791 PROJECT_FK_KEY,
792 SHIP_TO_SITE_FK_KEY,
793 BILL_TO_SITE_FK_KEY,
794 PRIM_SALESREP_FK_KEY,
795 INSTANCE_FK_KEY,
796 SALESCHANNEL_FK_KEY,
797 SOLD_TO_CUSTOMER_FK_KEY,
798 ORGANIZATION_FK_KEY,
799 PARENT_ITEM_FK_KEY,
800 FUNCTIONAL_CURRENCY_FK_KEY,
801 SET_OF_BOOKS_FK_KEY
802 FROM FII_AR_TRX_DIST_F,OPI_EDW_REV_LOG
803 WHERE GL_DATE_FK_KEY <> 0
804 AND FII_AR_TRX_DIST_F.ROWID = OPI_EDW_REV_LOG.row_id;
805
806 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_KEYS');
807 INSERT INTO OPI_EDW_MRG_MAX_VALUES
808 (ORDER_LINE_ID,
809 UOM_FK_KEY,
810 PROJECT_FK_KEY,
811 SHIP_TO_LOC_FK_KEY,
812 BILL_TO_LOC_FK_KEY,
813 PRIM_SALES_REP_FK_KEY,
814 INSTANCE_FK_KEY,
815 SALES_CHANNEL_FK_KEY,
816 CUSTOMER_FK_KEY,
817 OPERATING_UNIT_FK_KEY,
818 ITEM_ORG_FK_KEY,
819 BASE_CURRENCY_FK_KEY,
820 SOB_FK_KEY,
821 MAX_GL_POSTED_DATE,
822 MAX_UNIT_SELLING_PRICE,
823 GL_DATE_FK_KEY)
824 SELECT /*+ ORDERED */ DISTINCT MAXVALS.ORDER_LINE_ID,
825 MAXVALS.UOM_FK_KEY,
826 MAXVALS.PROJECT_FK_KEY,
827 MAXVALS.SHIP_TO_SITE_FK_KEY,
828 MAXVALS.BILL_TO_SITE_FK_KEY,
829 MAXVALS.PRIM_SALESREP_FK_KEY,
830 MAXVALS.INSTANCE_FK_KEY,
831 MAXVALS.SALESCHANNEL_FK_KEY,
832 MAXVALS.SOLD_TO_CUSTOMER_FK_KEY,
833 MAXVALS.ORGANIZATION_FK_KEY,
834 MAXVALS.PARENT_ITEM_FK_KEY,
835 MAXVALS.FUNCTIONAL_CURRENCY_FK_KEY,
836 MAXVALS.SET_OF_BOOKS_FK_KEY,
837 MAXVALS.GL_DATE,
838 MAXVALS.UNIT_SELLING_PRICE,
839 REV.GL_DATE_FK_KEY
840 FROM
841 FII_AR_TRX_DIST_F REV,
842 (select /*+ use_hash(REV) */ REV.ORDER_LINE_ID,
843 REV.UOM_FK_KEY,
844 REV.PROJECT_FK_KEY,
845 REV.SHIP_TO_SITE_FK_KEY,
846 REV.BILL_TO_SITE_FK_KEY,
847 REV.PRIM_SALESREP_FK_KEY,
848 REV.INSTANCE_FK_KEY,
849 REV.SALESCHANNEL_FK_KEY,
850 REV.SOLD_TO_CUSTOMER_FK_KEY,
851 REV.ORGANIZATION_FK_KEY,
852 REV.PARENT_ITEM_FK_KEY,
853 REV.FUNCTIONAL_CURRENCY_FK_KEY,
854 REV.SET_OF_BOOKS_FK_KEY,
855 MAX(GL_DATE) GL_DATE,
856 MAX(UNIT_SELLING_PRICE) UNIT_SELLING_PRICE
857 FROM FII_AR_TRX_DIST_F REV,
858 OPI_EDW_MRG_KEYS KEYS
859 WHERE KEYS.ORDER_LINE_ID = REV.ORDER_LINE_ID
860 AND KEYS.UOM_FK_KEY = REV.UOM_FK_KEY
861 AND KEYS.PROJECT_FK_KEY = REV.PROJECT_FK_KEY
862 AND KEYS.SHIP_TO_LOC_FK_KEY = REV.SHIP_TO_SITE_FK_KEY
863 AND KEYS.BILL_TO_LOC_FK_KEY = REV.BILL_TO_SITE_FK_KEY
864 AND KEYS.PRIM_SALES_REP_FK_KEY = REV.PRIM_SALESREP_FK_KEY
865 AND KEYS.INSTANCE_FK_KEY = REV.INSTANCE_FK_KEY
866 AND KEYS.SALES_CHANNEL_FK_KEY = REV.SALESCHANNEL_FK_KEY
867 AND KEYS.CUSTOMER_FK_KEY = REV.SOLD_TO_CUSTOMER_FK_KEY
868 AND KEYS.OPERATING_UNIT_FK_KEY = REV.ORGANIZATION_FK_KEY
869 AND KEYS.ITEM_ORG_FK_KEY = REV.PARENT_ITEM_FK_KEY
870 AND KEYS.BASE_CURRENCY_FK_KEY = REV.FUNCTIONAL_CURRENCY_FK_KEY
871 AND KEYS.SOB_FK_KEY = REV.SET_OF_BOOKS_FK_KEY
872 AND REV.GL_DATE_FK_KEY <> 0
873 GROUP BY
874 REV.ORDER_LINE_ID,
875 REV.UOM_FK_KEY,
876 REV.PROJECT_FK_KEY,
877 REV.SHIP_TO_SITE_FK_KEY,
878 REV.BILL_TO_SITE_FK_KEY,
879 REV.PRIM_SALESREP_FK_KEY,
880 REV.INSTANCE_FK_KEY,
881 REV.SALESCHANNEL_FK_KEY,
882 REV.SOLD_TO_CUSTOMER_FK_KEY,
883 REV.ORGANIZATION_FK_KEY,
884 REV.PARENT_ITEM_FK_KEY,
885 REV.FUNCTIONAL_CURRENCY_FK_KEY,
886 REV.SET_OF_BOOKS_FK_KEY) MAXVALS
887 WHERE MAXVALS.ORDER_LINE_ID = REV.ORDER_LINE_ID
888 AND MAXVALS.GL_DATE = REV.GL_DATE
889 AND MAXVALS.UOM_FK_KEY = REV.UOM_FK_KEY
890 AND MAXVALS.PROJECT_FK_KEY = REV.PROJECT_FK_KEY
891 AND MAXVALS.SHIP_TO_SITE_FK_KEY = REV.SHIP_TO_SITE_FK_KEY
892 AND MAXVALS.BILL_TO_SITE_FK_KEY = REV.BILL_TO_SITE_FK_KEY
893 AND MAXVALS.PRIM_SALESREP_FK_KEY = REV.PRIM_SALESREP_FK_KEY
894 AND MAXVALS.INSTANCE_FK_KEY = REV.INSTANCE_FK_KEY
895 AND MAXVALS.SALESCHANNEL_FK_KEY = REV.SALESCHANNEL_FK_KEY
899 AND MAXVALS.FUNCTIONAL_CURRENCY_FK_KEY = REV.FUNCTIONAL_CURRENCY_FK_KEY
896 AND MAXVALS.SOLD_TO_CUSTOMER_FK_KEY= REV.SOLD_TO_CUSTOMER_FK_KEY
897 AND MAXVALS.ORGANIZATION_FK_KEY = REV.ORGANIZATION_FK_KEY
898 AND MAXVALS.PARENT_ITEM_FK_KEY = REV.PARENT_ITEM_FK_KEY
900 AND MAXVALS.SET_OF_BOOKS_FK_KEY = REV.SET_OF_BOOKS_FK_KEY
901 AND REV.GL_DATE_FK_KEY <> 0;
902
903 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_MAX_VALUES');
904
905 LOOP
906 UPDATE OPI_EDW_MARGIN_PERD_ILOG
907 SET STATUS =1
908 WHERE STATUS = 0 AND
909 ROWNUM < l_chunk_size;
910 IF SQL%ROWCOUNT = 0 THEN
911 EXIT;
912 END IF;
913 INSERT INTO OPI_EDW_MRGIN_REV_POSTCOLL_FUR
914 (MAX_GL_POSTED_DATE,
915 MAX_UNIT_SELLING_PRICE,
916 GL_DATE_FK_KEY,
917 ROW_ID)
918 SELECT /*+ INDEX(MRG, OPI_EDW_MARGIN_F_U)
919 INDEX(LOG, OPI_EDW_MARGIN_PERD_ILOG_U1) */
920 MAXVALS.MAX_GL_POSTED_DATE,
921 MAXVALS.MAX_UNIT_SELLING_PRICE,
922 MAXVALS.GL_DATE_FK_KEY,
923 MRG.ROWID
924 FROM
925 OPI_EDW_MARGIN_PERD_ILOG LOG,
926 OPI_EDW_MARGIN_F MRG,
927 OPI_EDW_MRG_MAX_VALUES MAXVALS
928 WHERE
929 MAXVALS.ORDER_LINE_ID = MRG.ORDER_LINE_ID
930 AND MAXVALS.UOM_FK_KEY = MRG.UOM_FK_KEY
931 AND MAXVALS.PROJECT_FK_KEY = MRG.PROJECT_FK_KEY
932 AND MAXVALS.SHIP_TO_LOC_FK_KEY = MRG.SHIP_TO_LOC_FK_KEY
933 AND MAXVALS.BILL_TO_LOC_FK_KEY = MRG.BILL_TO_LOC_FK_KEY
934 AND MAXVALS.PRIM_SALES_REP_FK_KEY = MRG.PRIM_SALES_REP_FK_KEY
935 AND MAXVALS.INSTANCE_FK_KEY = MRG.INSTANCE_FK_KEY
936 AND MAXVALS.SALES_CHANNEL_FK_KEY = MRG.SALES_CHANNEL_FK_KEY
937 AND MAXVALS.CUSTOMER_FK_KEY = MRG.CUSTOMER_FK_KEY
938 AND MAXVALS.OPERATING_UNIT_FK_KEY = MRG.OPERATING_UNIT_FK_KEY
939 AND MAXVALS.ITEM_ORG_FK_KEY = MRG.ITEM_ORG_FK_KEY
940 AND MAXVALS.BASE_CURRENCY_FK_KEY = MRG.BASE_CURRENCY_FK_KEY
941 AND MAXVALS.SOB_FK_KEY = MRG.SOB_FK_KEY
942 AND LOG.ROW_ID = MRG.ROWID
943 AND LOG.STATUS = 1;
944
945 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
946
947 UPDATE OPI_EDW_MARGIN_F MRG
948 SET (INVOICE_DATE
949 ,REVENUE_DATE_FK_KEY
950 ,UNIT_SELLING_PRICE)=(SELECT distinct MAXVALS.MAX_GL_POSTED_DATE
951 ,MAXVALS.GL_DATE_FK_KEY
952 ,MAXVALS.MAX_UNIT_SELLING_PRICE FROM OPI_EDW_MRGIN_REV_POSTCOLL_FUR MAXVALS WHERE MRG.rowid = MAXVALS.ROW_ID)
953 where EXISTS (SELECT 1
954 FROM OPI_EDW_MRGIN_REV_POSTCOLL_FUR MAXVALS
955 WHERE MRG.rowid = MAXVALS.ROW_ID);
956
957 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);
958 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
959 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
960 UPDATE OPI_EDW_MARGIN_PERD_ILOG
961 SET STATUS =2
962 WHERE STATUS = 1;
963 END LOOP;
964 UPDATE OPI_EDW_MARGIN_PERD_ILOG
965 SET STATUS =0;
966 COMMIT;
967 ELSIF p_Base_fact_name = 'OPI_EDW_COGS_F' /*COGS Fact */
968 THEN
969 INSERT INTO OPI_EDW_MRG_KEYS (ORDER_LINE_ID,
970 UOM_FK_KEY,
971 PROJECT_FK_KEY,
972 SHIP_TO_LOC_FK_KEY,
973 BILL_TO_LOC_FK_KEY,
974 PRIM_SALES_REP_FK_KEY,
975 INSTANCE_FK_KEY,
976 SALES_CHANNEL_FK_KEY,
977 CUSTOMER_FK_KEY,
978 OPERATING_UNIT_FK_KEY,
979 ITEM_ORG_FK_KEY,
980 BASE_CURRENCY_FK_KEY,
981 SOB_FK_KEY)
982 select DISTINCT ORDER_LINE_ID, /*bug3331025 - Removed RULE Hint*/
983 BASE_UOM_FK_KEY,
984 PROJECT_FK_KEY,
985 SHIP_TO_LOC_FK_KEY,
986 BILL_TO_LOC_FK_KEY,
987 PRIM_SALES_REP_FK_KEY,
988 INSTANCE_FK_KEY,
989 SALES_CHANNEL_FK_KEY,
990 CUSTOMER_FK_KEY,
991 OPERATING_UNIT_FK_KEY,
992 top_model_item_fk_key,
993 BASE_CURRENCY_FK_KEY,
994 GL_SET_OF_BOOKS_FK_KEY
995 FROM OPI_EDW_COGS_F,
996 OPI_EDW_COGS_LOG
997 WHERE OPI_EDW_COGS_F.ROWID = OPI_EDW_COGS_LOG.row_id;
998
999 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_KEYS');
1000
1001 INSERT INTO OPI_EDW_MRG_MAX_VALUES
1002 (ORDER_LINE_ID,
1003 UOM_FK_KEY,
1004 PROJECT_FK_KEY,
1005 SHIP_TO_LOC_FK_KEY,
1006 BILL_TO_LOC_FK_KEY,
1007 PRIM_SALES_REP_FK_KEY,
1008 INSTANCE_FK_KEY,
1012 ITEM_ORG_FK_KEY,
1009 SALES_CHANNEL_FK_KEY,
1010 CUSTOMER_FK_KEY,
1011 OPERATING_UNIT_FK_KEY,
1013 BASE_CURRENCY_FK_KEY,
1014 SOB_FK_KEY,
1015 MAX_COGS_DATE,
1016 MAX_ORDER_NUMBER,
1017 MAX_ORDER_DATE,
1018 SHIP_INV_LOCATOR_FK_KEY,
1019 COGS_DATE_FK_KEY)
1020 SELECT /*+ ORDERED */ DISTINCT
1021 MAXVALS.ORDER_LINE_ID,
1022 MAXVALS.BASE_UOM_FK_KEY,
1023 MAXVALS.PROJECT_FK_KEY,
1024 MAXVALS.SHIP_TO_LOC_FK_KEY,
1025 MAXVALS.BILL_TO_LOC_FK_KEY,
1026 MAXVALS.PRIM_SALES_REP_FK_KEY,
1027 MAXVALS.INSTANCE_FK_KEY,
1028 MAXVALS.SALES_CHANNEL_FK_KEY,
1029 MAXVALS.CUSTOMER_FK_KEY,
1030 MAXVALS.OPERATING_UNIT_FK_KEY,
1031 MAXVALS.ITEM_ORG_FK_KEY,
1032 MAXVALS.BASE_CURRENCY_FK_KEY,
1033 MAXVALS.GL_SET_OF_BOOKS_FK_KEY,
1034 MAXVALS.COGS_DATE,
1035 MAXVALS.ORDER_NUMBER,
1036 MAXVALS.ORDER_DATE,
1037 COGS.SHIP_INV_LOCATOR_FK_KEY,
1038 COGS.COGS_DATE_FK_KEY
1039 FROM
1040 OPI_EDW_COGS_F COGS,
1041 (select COGS.ORDER_LINE_ID,
1042 COGS.BASE_UOM_FK_KEY,
1043 COGS.PROJECT_FK_KEY,
1044 COGS.SHIP_TO_LOC_FK_KEY,
1045 COGS.BILL_TO_LOC_FK_KEY,
1046 COGS.PRIM_SALES_REP_FK_KEY,
1047 COGS.INSTANCE_FK_KEY,
1048 COGS.SALES_CHANNEL_FK_KEY,
1049 COGS.CUSTOMER_FK_KEY,
1050 COGS.OPERATING_UNIT_FK_KEY,
1051 COGS.top_model_item_fk_key ITEM_ORG_FK_KEY,
1052 COGS.BASE_CURRENCY_FK_KEY,
1053 COGS.GL_SET_OF_BOOKS_FK_KEY,
1054 MAX(COGS.COGS_DATE) COGS_DATE,
1055 MAX(COGS.ORDER_NUMBER) ORDER_NUMBER,
1056 MAX(COGS.ORDER_DATE) ORDER_DATE
1057 FROM OPI_EDW_COGS_F COGS,
1058 OPI_EDW_MRG_KEYS KEYS
1059 WHERE KEYS.ORDER_LINE_ID = COGS.ORDER_LINE_ID
1060 AND KEYS.UOM_FK_KEY = COGS.BASE_UOM_FK_KEY
1061 AND KEYS.PROJECT_FK_KEY = COGS.PROJECT_FK_KEY
1062 AND KEYS.SHIP_TO_LOC_FK_KEY = COGS.SHIP_TO_LOC_FK_KEY
1063 AND KEYS.BILL_TO_LOC_FK_KEY = COGS.BILL_TO_LOC_FK_KEY
1064 AND KEYS.PRIM_SALES_REP_FK_KEY = COGS.PRIM_SALES_REP_FK_KEY
1065 AND KEYS.INSTANCE_FK_KEY = COGS.INSTANCE_FK_KEY
1066 AND KEYS.SALES_CHANNEL_FK_KEY = COGS.SALES_CHANNEL_FK_KEY
1067 AND KEYS.CUSTOMER_FK_KEY = COGS.CUSTOMER_FK_KEY
1068 AND KEYS.OPERATING_UNIT_FK_KEY = COGS.OPERATING_UNIT_FK_KEY
1069 AND KEYS.ITEM_ORG_FK_KEY = COGS.top_model_item_fk_key
1070 AND KEYS.BASE_CURRENCY_FK_KEY = COGS.BASE_CURRENCY_FK_KEY
1071 AND KEYS.SOB_FK_KEY = COGS.GL_SET_OF_BOOKS_FK_KEY
1072 GROUP BY
1073 COGS.ORDER_LINE_ID,
1074 COGS.BASE_UOM_FK_KEY,
1075 COGS.PROJECT_FK_KEY,
1076 COGS.SHIP_TO_LOC_FK_KEY,
1077 COGS.BILL_TO_LOC_FK_KEY,
1078 COGS.PRIM_SALES_REP_FK_KEY,
1079 COGS.INSTANCE_FK_KEY,
1080 COGS.SALES_CHANNEL_FK_KEY,
1081 COGS.CUSTOMER_FK_KEY,
1082 COGS.OPERATING_UNIT_FK_KEY,
1083 COGS.top_model_item_fk_key,
1084 COGS.BASE_CURRENCY_FK_KEY,
1085 COGS.GL_SET_OF_BOOKS_FK_KEY) MAXVALS
1086 WHERE
1087 MAXVALS.ORDER_LINE_ID = COGS.ORDER_LINE_ID
1088 AND MAXVALS.COGS_DATE = COGS.COGS_DATE
1089 AND MAXVALS.BASE_UOM_FK_KEY = COGS.BASE_UOM_FK_KEY
1090 AND MAXVALS.PROJECT_FK_KEY = COGS.PROJECT_FK_KEY
1091 AND MAXVALS.SHIP_TO_LOC_FK_KEY = COGS.SHIP_TO_LOC_FK_KEY
1092 AND MAXVALS.BILL_TO_LOC_FK_KEY = COGS.BILL_TO_LOC_FK_KEY
1093 AND MAXVALS.PRIM_SALES_REP_FK_KEY = COGS.PRIM_SALES_REP_FK_KEY
1094 AND MAXVALS.INSTANCE_FK_KEY = COGS.INSTANCE_FK_KEY
1095 AND MAXVALS.SALES_CHANNEL_FK_KEY = COGS.SALES_CHANNEL_FK_KEY
1096 AND MAXVALS.CUSTOMER_FK_KEY = COGS.CUSTOMER_FK_KEY
1097 AND MAXVALS.OPERATING_UNIT_FK_KEY = COGS.OPERATING_UNIT_FK_KEY
1098 AND MAXVALS.ITEM_ORG_FK_KEY = COGS.top_model_item_fk_key
1099 AND MAXVALS.BASE_CURRENCY_FK_KEY = COGS.BASE_CURRENCY_FK_KEY
1100 AND MAXVALS.GL_SET_OF_BOOKS_FK_KEY= COGS.GL_SET_OF_BOOKS_FK_KEY;
1101
1102 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_MAX_VALUES');
1103
1104
1105
1106 LOOP
1107 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1108 SET STATUS =1
1109 WHERE STATUS = 0 AND
1110 ROWNUM < l_chunk_size;
1111 IF SQL%ROWCOUNT = 0 THEN
1112 EXIT;
1113 END IF;
1114 INSERT INTO OPI_EDW_MRGIN_COGSPOSTCOLL_FUR
1115 (MAX_ORDER_DATE,
1116 SHIP_INV_LOCATOR_FK_KEY ,
1117 COGS_DATE_FK_KEY,
1118 MAX_ORDER_NUMBER,
1119 MAX_COGS_DATE,
1120 ROW_ID)
1121 SELECT /*+ INDEX(MRG, OPI_EDW_MARGIN_F_U) */ MAXVALS.MAX_ORDER_DATE,
1122 MAXVALS.SHIP_INV_LOCATOR_FK_KEY,
1123 MAXVALS.COGS_DATE_FK_KEY,
1124 MAXVALS.MAX_ORDER_NUMBER,
1125 MAXVALS.MAX_COGS_DATE,
1126 MRG.ROWID
1127 FROM
1128 OPI_EDW_MARGIN_PERD_ILOG LOG,
1129 OPI_EDW_MARGIN_F MRG,
1130 OPI_EDW_MRG_MAX_VALUES MAXVALS
1131 WHERE
1132 MAXVALS.ORDER_LINE_ID = MRG.ORDER_LINE_ID
1136 AND MAXVALS.BILL_TO_LOC_FK_KEY = MRG.BILL_TO_LOC_FK_KEY
1133 AND MAXVALS.UOM_FK_KEY = MRG.UOM_FK_KEY
1134 AND MAXVALS.PROJECT_FK_KEY = MRG.PROJECT_FK_KEY
1135 AND MAXVALS.SHIP_TO_LOC_FK_KEY = MRG.SHIP_TO_LOC_FK_KEY
1137 AND MAXVALS.PRIM_SALES_REP_FK_KEY = MRG.PRIM_SALES_REP_FK_KEY
1138 AND MAXVALS.INSTANCE_FK_KEY = MRG.INSTANCE_FK_KEY
1139 AND MAXVALS.SALES_CHANNEL_FK_KEY = MRG.SALES_CHANNEL_FK_KEY
1140 AND MAXVALS.CUSTOMER_FK_KEY = MRG.CUSTOMER_FK_KEY
1141 AND MAXVALS.OPERATING_UNIT_FK_KEY = MRG.OPERATING_UNIT_FK_KEY
1142 AND MAXVALS.ITEM_ORG_FK_KEY = MRG.ITEM_ORG_FK_KEY
1143 AND MAXVALS.BASE_CURRENCY_FK_KEY = MRG.BASE_CURRENCY_FK_KEY
1144 AND MAXVALS.SOB_FK_KEY = MRG.SOB_FK_KEY
1145 AND LOG.ROW_ID = MRG.ROWID
1146 AND LOG.STATUS = 1;
1147
1148 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
1149
1150 UPDATE OPI_EDW_MARGIN_F MRG
1151 SET (ORDER_DATE
1152 ,ORDER_NO
1153 ,SHIP_DATE
1154 ,SHIP_LOCATION_FK_KEY
1155 ,COGS_DATE_FK_KEY)=(SELECT distinct MAXVALS.MAX_ORDER_DATE
1156 ,MAXVALS.MAX_ORDER_NUMBER
1157 ,MAXVALS.MAX_COGS_DATE
1158 ,MAXVALS.SHIP_INV_LOCATOR_FK_KEY
1159 ,MAXVALS.COGS_DATE_FK_KEY
1160 FROM OPI_EDW_MRGIN_COGSPOSTCOLL_FUR MAXVALS
1161 WHERE MRG.rowid = MAXVALS.ROW_ID)
1162 where EXISTS (SELECT 1
1163 FROM OPI_EDW_MRGIN_COGSPOSTCOLL_FUR MAXVALS
1164 WHERE MRG.rowid = MAXVALS.ROW_ID);
1165
1166 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);
1167 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1168
1169 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
1170
1171 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1172 SET STATUS =2
1173 WHERE STATUS = 1;
1174 END LOOP;
1175 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1176 SET STATUS =0;
1177 COMMIT;
1178 ELSIF p_base_fact_name = 'OPI_EDW_MARGIN_F' /* if it is a full Refesh */
1179 THEN
1180 INSERT INTO OPI_EDW_MRG_KEYS (ORDER_LINE_ID,
1181 UOM_FK_KEY,
1182 PROJECT_FK_KEY,
1183 SHIP_TO_LOC_FK_KEY,
1184 BILL_TO_LOC_FK_KEY,
1185 PRIM_SALES_REP_FK_KEY,
1186 INSTANCE_FK_KEY,
1187 SALES_CHANNEL_FK_KEY,
1188 CUSTOMER_FK_KEY,
1189 OPERATING_UNIT_FK_KEY,
1190 ITEM_ORG_FK_KEY,
1191 BASE_CURRENCY_FK_KEY,
1192 SOB_FK_KEY)
1193 select DISTINCT ORDER_LINE_ID, /*bug3331025 - Removed RULE Hint*/
1194 UOM_FK_KEY,
1195 PROJECT_FK_KEY,
1196 SHIP_TO_SITE_FK_KEY,
1197 BILL_TO_SITE_FK_KEY,
1198 PRIM_SALESREP_FK_KEY,
1199 INSTANCE_FK_KEY,
1200 SALESCHANNEL_FK_KEY,
1201 SOLD_TO_CUSTOMER_FK_KEY,
1202 ORGANIZATION_FK_KEY,
1203 PARENT_ITEM_FK_KEY,
1204 FUNCTIONAL_CURRENCY_FK_KEY,
1205 SET_OF_BOOKS_FK_KEY
1206 FROM FII_AR_TRX_DIST_F
1207 WHERE GL_DATE_FK_KEY <> 0;
1208
1209 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_KEYS');
1210
1211
1212 INSERT INTO OPI_EDW_MRG_MAX_VALUES
1213 (ORDER_LINE_ID,
1214 UOM_FK_KEY,
1215 PROJECT_FK_KEY,
1216 SHIP_TO_LOC_FK_KEY,
1217 BILL_TO_LOC_FK_KEY,
1218 PRIM_SALES_REP_FK_KEY,
1219 INSTANCE_FK_KEY,
1220 SALES_CHANNEL_FK_KEY,
1221 CUSTOMER_FK_KEY,
1222 OPERATING_UNIT_FK_KEY,
1223 ITEM_ORG_FK_KEY,
1224 BASE_CURRENCY_FK_KEY,
1225 SOB_FK_KEY,
1226 MAX_GL_POSTED_DATE,
1227 MAX_UNIT_SELLING_PRICE,
1228 GL_DATE_FK_KEY)
1229 SELECT /*+ ORDERED */ DISTINCT MAXVALS.ORDER_LINE_ID,
1230 MAXVALS.UOM_FK_KEY,
1231 MAXVALS.PROJECT_FK_KEY,
1232 MAXVALS.SHIP_TO_SITE_FK_KEY,
1233 MAXVALS.BILL_TO_SITE_FK_KEY,
1234 MAXVALS.PRIM_SALESREP_FK_KEY,
1235 MAXVALS.INSTANCE_FK_KEY,
1236 MAXVALS.SALESCHANNEL_FK_KEY,
1237 MAXVALS.SOLD_TO_CUSTOMER_FK_KEY,
1238 MAXVALS.ORGANIZATION_FK_KEY,
1239 MAXVALS.PARENT_ITEM_FK_KEY,
1240 MAXVALS.FUNCTIONAL_CURRENCY_FK_KEY,
1241 MAXVALS.SET_OF_BOOKS_FK_KEY,
1242 MAXVALS.GL_DATE,
1243 MAXVALS.UNIT_SELLING_PRICE,
1244 REV.GL_DATE_FK_KEY
1245 FROM
1246 FII_AR_TRX_DIST_F REV,
1247 (select REV.ORDER_LINE_ID,
1248 REV.UOM_FK_KEY,
1249 REV.PROJECT_FK_KEY,
1250 REV.SHIP_TO_SITE_FK_KEY,
1254 REV.SALESCHANNEL_FK_KEY,
1251 REV.BILL_TO_SITE_FK_KEY,
1252 REV.PRIM_SALESREP_FK_KEY,
1253 REV.INSTANCE_FK_KEY,
1255 REV.SOLD_TO_CUSTOMER_FK_KEY,
1256 REV.ORGANIZATION_FK_KEY,
1257 REV.PARENT_ITEM_FK_KEY,
1258 REV.FUNCTIONAL_CURRENCY_FK_KEY,
1259 REV.SET_OF_BOOKS_FK_KEY,
1260 MAX(GL_DATE) GL_DATE,
1261 MAX(UNIT_SELLING_PRICE) UNIT_SELLING_PRICE
1262 FROM FII_AR_TRX_DIST_F REV,
1263 OPI_EDW_MRG_KEYS KEYS
1264 WHERE KEYS.ORDER_LINE_ID = REV.ORDER_LINE_ID
1265 AND KEYS.UOM_FK_KEY = REV.UOM_FK_KEY
1266 AND KEYS.PROJECT_FK_KEY = REV.PROJECT_FK_KEY
1267 AND KEYS.SHIP_TO_LOC_FK_KEY = REV.SHIP_TO_SITE_FK_KEY
1268 AND KEYS.BILL_TO_LOC_FK_KEY = REV.BILL_TO_SITE_FK_KEY
1269 AND KEYS.PRIM_SALES_REP_FK_KEY = REV.PRIM_SALESREP_FK_KEY
1270 AND KEYS.INSTANCE_FK_KEY = REV.INSTANCE_FK_KEY
1271 AND KEYS.SALES_CHANNEL_FK_KEY = REV.SALESCHANNEL_FK_KEY
1272 AND KEYS.CUSTOMER_FK_KEY = REV.SOLD_TO_CUSTOMER_FK_KEY
1273 AND KEYS.OPERATING_UNIT_FK_KEY = REV.ORGANIZATION_FK_KEY
1274 AND KEYS.ITEM_ORG_FK_KEY = REV.PARENT_ITEM_FK_KEY
1275 AND KEYS.BASE_CURRENCY_FK_KEY = REV.FUNCTIONAL_CURRENCY_FK_KEY
1276 AND KEYS.SOB_FK_KEY = REV.SET_OF_BOOKS_FK_KEY
1277 AND REV.GL_DATE_FK_KEY <> 0
1278 GROUP BY
1279 REV.ORDER_LINE_ID,
1280 REV.UOM_FK_KEY,
1281 REV.PROJECT_FK_KEY,
1282 REV.SHIP_TO_SITE_FK_KEY,
1283 REV.BILL_TO_SITE_FK_KEY,
1284 REV.PRIM_SALESREP_FK_KEY,
1285 REV.INSTANCE_FK_KEY,
1286 REV.SALESCHANNEL_FK_KEY,
1287 REV.SOLD_TO_CUSTOMER_FK_KEY,
1288 REV.ORGANIZATION_FK_KEY,
1289 REV.PARENT_ITEM_FK_KEY,
1290 REV.FUNCTIONAL_CURRENCY_FK_KEY,
1291 REV.SET_OF_BOOKS_FK_KEY) MAXVALS
1292 WHERE MAXVALS.ORDER_LINE_ID = REV.ORDER_LINE_ID
1293 AND MAXVALS.GL_DATE = REV.GL_DATE
1294 AND MAXVALS.UOM_FK_KEY = REV.UOM_FK_KEY
1295 AND MAXVALS.PROJECT_FK_KEY = REV.PROJECT_FK_KEY
1296 AND MAXVALS.SHIP_TO_SITE_FK_KEY = REV.SHIP_TO_SITE_FK_KEY
1297 AND MAXVALS.BILL_TO_SITE_FK_KEY = REV.BILL_TO_SITE_FK_KEY
1298 AND MAXVALS.PRIM_SALESREP_FK_KEY = REV.PRIM_SALESREP_FK_KEY
1299 AND MAXVALS.INSTANCE_FK_KEY = REV.INSTANCE_FK_KEY
1300 AND MAXVALS.SALESCHANNEL_FK_KEY = REV.SALESCHANNEL_FK_KEY
1301 AND MAXVALS.SOLD_TO_CUSTOMER_FK_KEY= REV.SOLD_TO_CUSTOMER_FK_KEY
1302 AND MAXVALS.ORGANIZATION_FK_KEY = REV.ORGANIZATION_FK_KEY
1303 AND MAXVALS.PARENT_ITEM_FK_KEY = REV.PARENT_ITEM_FK_KEY
1304 AND MAXVALS.FUNCTIONAL_CURRENCY_FK_KEY = REV.FUNCTIONAL_CURRENCY_FK_KEY
1305 AND MAXVALS.SET_OF_BOOKS_FK_KEY = REV.SET_OF_BOOKS_FK_KEY
1306 AND REV.GL_DATE_FK_KEY <> 0;
1307
1308 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_MAX_VALUES');
1309
1310 LOOP
1311 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1312 SET STATUS =1
1313 WHERE STATUS = 0 AND
1314 ROWNUM < l_chunk_size;
1315 IF SQL%ROWCOUNT = 0 THEN
1316 EXIT;
1317 END IF;
1318 INSERT INTO OPI_EDW_MRGIN_REV_POSTCOLL_FUR
1319 (MAX_GL_POSTED_DATE,
1320 MAX_UNIT_SELLING_PRICE,
1321 GL_DATE_FK_KEY,
1322 ROW_ID)
1323 SELECT MAXVALS.MAX_GL_POSTED_DATE,
1324 MAXVALS.MAX_UNIT_SELLING_PRICE,
1325 MAXVALS.GL_DATE_FK_KEY,
1326 MRG.ROWID
1327 FROM
1328 OPI_EDW_MARGIN_PERD_ILOG LOG,
1329 OPI_EDW_MARGIN_F MRG,
1330 OPI_EDW_MRG_MAX_VALUES MAXVALS
1331 WHERE
1332 MAXVALS.ORDER_LINE_ID = MRG.ORDER_LINE_ID
1333 AND MAXVALS.UOM_FK_KEY = MRG.UOM_FK_KEY
1334 AND MAXVALS.PROJECT_FK_KEY = MRG.PROJECT_FK_KEY
1335 AND MAXVALS.SHIP_TO_LOC_FK_KEY = MRG.SHIP_TO_LOC_FK_KEY
1336 AND MAXVALS.BILL_TO_LOC_FK_KEY = MRG.BILL_TO_LOC_FK_KEY
1337 AND MAXVALS.PRIM_SALES_REP_FK_KEY = MRG.PRIM_SALES_REP_FK_KEY
1338 AND MAXVALS.INSTANCE_FK_KEY = MRG.INSTANCE_FK_KEY
1339 AND MAXVALS.SALES_CHANNEL_FK_KEY = MRG.SALES_CHANNEL_FK_KEY
1340 AND MAXVALS.CUSTOMER_FK_KEY = MRG.CUSTOMER_FK_KEY
1341 AND MAXVALS.OPERATING_UNIT_FK_KEY = MRG.OPERATING_UNIT_FK_KEY
1342 AND MAXVALS.ITEM_ORG_FK_KEY = MRG.ITEM_ORG_FK_KEY
1343 AND MAXVALS.BASE_CURRENCY_FK_KEY = MRG.BASE_CURRENCY_FK_KEY
1344 AND MAXVALS.SOB_FK_KEY = MRG.SOB_FK_KEY
1345 AND LOG.ROW_ID = MRG.ROWID
1346 AND LOG.STATUS = 1;
1347
1348 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
1349
1350 UPDATE OPI_EDW_MARGIN_F MRG
1351 SET (INVOICE_DATE
1352 ,REVENUE_DATE_FK_KEY
1353 ,UNIT_SELLING_PRICE)=(SELECT distinct MAXVALS.MAX_GL_POSTED_DATE
1354 ,MAXVALS.GL_DATE_FK_KEY
1355 ,MAXVALS.MAX_UNIT_SELLING_PRICE
1356 FROM OPI_EDW_MRGIN_REV_POSTCOLL_FUR MAXVALS
1357 WHERE MRG.rowid = MAXVALS.ROW_ID)
1358 where EXISTS (SELECT 1
1359 FROM OPI_EDW_MRGIN_REV_POSTCOLL_FUR MAXVALS
1360 WHERE MRG.rowid = MAXVALS.ROW_ID);
1361
1365 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
1362 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);
1363 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1364
1366
1367 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1368 SET STATUS =2
1369 WHERE STATUS = 1;
1370 END LOOP;
1371 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1372 SET STATUS =0;
1373 COMMIT;
1374
1375 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
1376 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
1377
1378 /*********************************************************************************************
1379 *** Populate COGS data for full refresh ****
1380 **********************************************************************************************/
1381
1382 INSERT INTO OPI_EDW_MRG_KEYS (ORDER_LINE_ID,
1383 UOM_FK_KEY,
1384 PROJECT_FK_KEY,
1385 SHIP_TO_LOC_FK_KEY,
1386 BILL_TO_LOC_FK_KEY,
1387 PRIM_SALES_REP_FK_KEY,
1388 INSTANCE_FK_KEY,
1389 SALES_CHANNEL_FK_KEY,
1390 CUSTOMER_FK_KEY,
1391 OPERATING_UNIT_FK_KEY,
1392 ITEM_ORG_FK_KEY,
1393 BASE_CURRENCY_FK_KEY,
1394 SOB_FK_KEY)
1395 select DISTINCT ORDER_LINE_ID,
1396 BASE_UOM_FK_KEY,
1397 PROJECT_FK_KEY,
1398 SHIP_TO_LOC_FK_KEY,
1399 BILL_TO_LOC_FK_KEY,
1400 PRIM_SALES_REP_FK_KEY,
1401 INSTANCE_FK_KEY,
1402 SALES_CHANNEL_FK_KEY,
1403 CUSTOMER_FK_KEY,
1404 OPERATING_UNIT_FK_KEY,
1405 top_model_item_fk_key,
1406 BASE_CURRENCY_FK_KEY,
1407 GL_SET_OF_BOOKS_FK_KEY
1408 FROM OPI_EDW_COGS_F;
1409 EDW_OWB_COLLECTION_UTIL.write_to_log_file('1 sqlcount ddd ' || SQL%rowcount);
1410 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_KEYS');
1411
1412 INSERT INTO OPI_EDW_MRG_MAX_VALUES
1413 (ORDER_LINE_ID,
1414 UOM_FK_KEY,
1415 PROJECT_FK_KEY,
1416 SHIP_TO_LOC_FK_KEY,
1417 BILL_TO_LOC_FK_KEY,
1418 PRIM_SALES_REP_FK_KEY,
1419 INSTANCE_FK_KEY,
1420 SALES_CHANNEL_FK_KEY,
1421 CUSTOMER_FK_KEY,
1422 OPERATING_UNIT_FK_KEY,
1423 ITEM_ORG_FK_KEY,
1424 BASE_CURRENCY_FK_KEY,
1425 SOB_FK_KEY,
1426 MAX_COGS_DATE,
1427 MAX_ORDER_NUMBER,
1428 MAX_ORDER_DATE,
1429 SHIP_INV_LOCATOR_FK_KEY,
1430 COGS_DATE_FK_KEY)
1431 SELECT DISTINCT
1432 MAXVALS.ORDER_LINE_ID,
1433 MAXVALS.BASE_UOM_FK_KEY,
1434 MAXVALS.PROJECT_FK_KEY,
1435 MAXVALS.SHIP_TO_LOC_FK_KEY,
1436 MAXVALS.BILL_TO_LOC_FK_KEY,
1437 MAXVALS.PRIM_SALES_REP_FK_KEY,
1438 MAXVALS.INSTANCE_FK_KEY,
1439 MAXVALS.SALES_CHANNEL_FK_KEY,
1440 MAXVALS.CUSTOMER_FK_KEY,
1441 MAXVALS.OPERATING_UNIT_FK_KEY,
1442 MAXVALS.ITEM_ORG_FK_KEY,
1443 MAXVALS.BASE_CURRENCY_FK_KEY,
1444 MAXVALS.GL_SET_OF_BOOKS_FK_KEY,
1445 MAXVALS.COGS_DATE,
1446 MAXVALS.ORDER_NUMBER,
1447 MAXVALS.ORDER_DATE,
1448 COGS.SHIP_INV_LOCATOR_FK_KEY,
1449 COGS.COGS_DATE_FK_KEY
1450 FROM
1451 OPI_EDW_COGS_F COGS,
1452 (select COGS.ORDER_LINE_ID,
1453 COGS.BASE_UOM_FK_KEY,
1454 COGS.PROJECT_FK_KEY,
1455 COGS.SHIP_TO_LOC_FK_KEY,
1456 COGS.BILL_TO_LOC_FK_KEY,
1457 COGS.PRIM_SALES_REP_FK_KEY,
1458 COGS.INSTANCE_FK_KEY,
1459 COGS.SALES_CHANNEL_FK_KEY,
1460 COGS.CUSTOMER_FK_KEY,
1461 COGS.OPERATING_UNIT_FK_KEY,
1462 COGS.top_model_item_fk_key ITEM_ORG_FK_KEY,
1463 COGS.BASE_CURRENCY_FK_KEY,
1464 COGS.GL_SET_OF_BOOKS_FK_KEY,
1465 MAX(COGS.COGS_DATE) COGS_DATE,
1466 MAX(COGS.ORDER_NUMBER) ORDER_NUMBER,
1467 MAX(COGS.ORDER_DATE) ORDER_DATE
1468 FROM OPI_EDW_COGS_F COGS,
1469 OPI_EDW_MRG_KEYS KEYS
1470 WHERE KEYS.ORDER_LINE_ID = COGS.ORDER_LINE_ID
1471 AND KEYS.UOM_FK_KEY = COGS.BASE_UOM_FK_KEY
1472 AND KEYS.PROJECT_FK_KEY = COGS.PROJECT_FK_KEY
1473 AND KEYS.SHIP_TO_LOC_FK_KEY = COGS.SHIP_TO_LOC_FK_KEY
1474 AND KEYS.BILL_TO_LOC_FK_KEY = COGS.BILL_TO_LOC_FK_KEY
1475 AND KEYS.PRIM_SALES_REP_FK_KEY = COGS.PRIM_SALES_REP_FK_KEY
1476 AND KEYS.INSTANCE_FK_KEY = COGS.INSTANCE_FK_KEY
1477 AND KEYS.SALES_CHANNEL_FK_KEY = COGS.SALES_CHANNEL_FK_KEY
1478 AND KEYS.CUSTOMER_FK_KEY = COGS.CUSTOMER_FK_KEY
1479 AND KEYS.OPERATING_UNIT_FK_KEY = COGS.OPERATING_UNIT_FK_KEY
1483 GROUP BY
1480 AND KEYS.ITEM_ORG_FK_KEY = COGS.top_model_item_fk_key
1481 AND KEYS.BASE_CURRENCY_FK_KEY = COGS.BASE_CURRENCY_FK_KEY
1482 AND KEYS.SOB_FK_KEY = COGS.GL_SET_OF_BOOKS_FK_KEY
1484 COGS.ORDER_LINE_ID,
1485 COGS.BASE_UOM_FK_KEY,
1486 COGS.PROJECT_FK_KEY,
1487 COGS.SHIP_TO_LOC_FK_KEY,
1488 COGS.BILL_TO_LOC_FK_KEY,
1489 COGS.PRIM_SALES_REP_FK_KEY,
1490 COGS.INSTANCE_FK_KEY,
1491 COGS.SALES_CHANNEL_FK_KEY,
1492 COGS.CUSTOMER_FK_KEY,
1493 COGS.OPERATING_UNIT_FK_KEY,
1494 COGS.top_model_item_fk_key,
1495 COGS.BASE_CURRENCY_FK_KEY,
1496 COGS.GL_SET_OF_BOOKS_FK_KEY) MAXVALS
1497 WHERE
1498 MAXVALS.ORDER_LINE_ID = COGS.ORDER_LINE_ID
1499 AND MAXVALS.COGS_DATE = COGS.COGS_DATE
1500 AND MAXVALS.BASE_UOM_FK_KEY = COGS.BASE_UOM_FK_KEY
1501 AND MAXVALS.PROJECT_FK_KEY = COGS.PROJECT_FK_KEY
1502 AND MAXVALS.SHIP_TO_LOC_FK_KEY = COGS.SHIP_TO_LOC_FK_KEY
1503 AND MAXVALS.BILL_TO_LOC_FK_KEY = COGS.BILL_TO_LOC_FK_KEY
1504 AND MAXVALS.PRIM_SALES_REP_FK_KEY = COGS.PRIM_SALES_REP_FK_KEY
1505 AND MAXVALS.INSTANCE_FK_KEY = COGS.INSTANCE_FK_KEY
1506 AND MAXVALS.SALES_CHANNEL_FK_KEY = COGS.SALES_CHANNEL_FK_KEY
1507 AND MAXVALS.CUSTOMER_FK_KEY = COGS.CUSTOMER_FK_KEY
1508 AND MAXVALS.OPERATING_UNIT_FK_KEY = COGS.OPERATING_UNIT_FK_KEY
1509 AND MAXVALS.ITEM_ORG_FK_KEY = COGS.top_model_item_fk_key
1510 AND MAXVALS.BASE_CURRENCY_FK_KEY = COGS.BASE_CURRENCY_FK_KEY
1511 AND MAXVALS.GL_SET_OF_BOOKS_FK_KEY= COGS.GL_SET_OF_BOOKS_FK_KEY;
1512 EDW_OWB_COLLECTION_UTIL.write_to_log_file('2 sqlcount ddd ' || SQL%rowcount);
1513 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_MAX_VALUES');
1514
1515
1516
1517 LOOP
1518 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1519 SET STATUS =1
1520 WHERE STATUS = 0 AND
1521 ROWNUM < l_chunk_size;
1522 IF SQL%ROWCOUNT = 0 THEN
1523 EXIT;
1524 END IF;
1525 INSERT INTO OPI_EDW_MRGIN_COGSPOSTCOLL_FUR
1526 (MAX_ORDER_DATE,
1527 SHIP_INV_LOCATOR_FK_KEY ,
1528 COGS_DATE_FK_KEY,
1529 MAX_ORDER_NUMBER,
1530 MAX_COGS_DATE,
1531 ROW_ID)
1532 SELECT MAXVALS.MAX_ORDER_DATE, /*bug3331025 - Removed RULE Hint*/
1533 MAXVALS.SHIP_INV_LOCATOR_FK_KEY,
1534 MAXVALS.COGS_DATE_FK_KEY,
1535 MAXVALS.MAX_ORDER_NUMBER,
1536 MAXVALS.MAX_COGS_DATE,
1537 MRG.ROWID
1538 FROM
1539 OPI_EDW_MARGIN_PERD_ILOG LOG,
1540 OPI_EDW_MARGIN_F MRG,
1541 OPI_EDW_MRG_MAX_VALUES MAXVALS
1542 WHERE
1543 MAXVALS.ORDER_LINE_ID = MRG.ORDER_LINE_ID
1544 AND MAXVALS.UOM_FK_KEY = MRG.UOM_FK_KEY
1545 AND MAXVALS.PROJECT_FK_KEY = MRG.PROJECT_FK_KEY
1546 AND MAXVALS.SHIP_TO_LOC_FK_KEY = MRG.SHIP_TO_LOC_FK_KEY
1547 AND MAXVALS.BILL_TO_LOC_FK_KEY = MRG.BILL_TO_LOC_FK_KEY
1548 AND MAXVALS.PRIM_SALES_REP_FK_KEY = MRG.PRIM_SALES_REP_FK_KEY
1549 AND MAXVALS.INSTANCE_FK_KEY = MRG.INSTANCE_FK_KEY
1550 AND MAXVALS.SALES_CHANNEL_FK_KEY = MRG.SALES_CHANNEL_FK_KEY
1551 AND MAXVALS.CUSTOMER_FK_KEY = MRG.CUSTOMER_FK_KEY
1552 AND MAXVALS.OPERATING_UNIT_FK_KEY = MRG.OPERATING_UNIT_FK_KEY
1553 AND MAXVALS.ITEM_ORG_FK_KEY = MRG.ITEM_ORG_FK_KEY
1554 AND MAXVALS.BASE_CURRENCY_FK_KEY = MRG.BASE_CURRENCY_FK_KEY
1555 AND MAXVALS.SOB_FK_KEY = MRG.SOB_FK_KEY
1556 AND LOG.ROW_ID = MRG.ROWID
1557 AND LOG.STATUS = 1;
1558 EDW_OWB_COLLECTION_UTIL.write_to_log_file('3 sqlcount ddd ' || SQL%rowcount);
1559 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
1560
1561 UPDATE OPI_EDW_MARGIN_F MRG
1562 SET (ORDER_DATE
1563 ,ORDER_NO
1564 ,SHIP_DATE
1565 ,SHIP_LOCATION_FK_KEY
1566 ,COGS_DATE_FK_KEY)=(SELECT distinct MAXVALS.MAX_ORDER_DATE
1567 ,MAXVALS.MAX_ORDER_NUMBER
1568 ,MAXVALS.MAX_COGS_DATE
1569 ,MAXVALS.SHIP_INV_LOCATOR_FK_KEY
1570 ,MAXVALS.COGS_DATE_FK_KEY
1571 FROM OPI_EDW_MRGIN_COGSPOSTCOLL_FUR MAXVALS
1572 WHERE MRG.rowid = MAXVALS.ROW_ID)
1573 where EXISTS (SELECT 1
1574 FROM OPI_EDW_MRGIN_COGSPOSTCOLL_FUR MAXVALS
1575 WHERE MRG.rowid = MAXVALS.ROW_ID);
1576
1577 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);
1578 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1579
1580 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
1581
1582 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1583 SET STATUS =2
1584 WHERE STATUS = 1;
1585 END LOOP;
1586 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1587 SET STATUS =0;
1588 COMMIT;
1589
1590 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from cogs fact'||SQL%ROWCOUNT);
1591 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1592
1593 END IF;
1597 +*****************************************************************************************/
1594 /****************************************************************************************+
1595 *** Find Max of SHIP_DATE and INVOICE_DATE based on that populate Margin Date FK Key ***
1596 *** with either COGS Date FK KEy OR Revenue Date Fk Key ***
1598
1599 LOOP
1600 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1601 SET STATUS =1
1602 WHERE STATUS = 0 AND
1603 ROWNUM < l_chunk_size;
1604 IF SQL%ROWCOUNT = 0 THEN
1605 EXIT;
1606 END IF;
1607 /**********************************************************************
1608 *** Update Margin Date FK Key with COGS Date FK Key if Ship date is **
1609 *** greater than or equal to Invoice Date **
1610 ***********************************************************************/
1611
1612 UPDATE OPI_EDW_MARGIN_F MRG
1613 SET MARGIN_DATE_FK_KEY = MRG.COGS_DATE_FK_KEY,
1614 GL_PERIOD_FK_KEY =
1615 (SELECT TIM2.CDAY_CAL_DAY_PK_KEY
1616 FROM EDW_TIME_M TIM1,
1617 EDW_TIME_M TIM2,
1618 EDW_TIME_CAL_PERIOD_LTC PERDLTC
1619 WHERE MRG.SHIP_DATE IS NOT NULL
1620 AND MRG.INVOICE_DATE IS NOT NULL
1621 AND MRG.COGS_DATE_FK_KEY = TIM1.CDAY_CAL_DAY_PK_KEY
1622 AND TIM1.CPER_CAL_PERIOD_PK_KEY= PERDLTC.CAL_PERIOD_PK_KEY
1623 AND TIM2.CDAY_CAL_DAY_PK = PERDLTC.CAL_PERIOD_PK ||'-CPER' )
1624 WHERE MRG.SHIP_DATE IS NOT NULL
1625 AND MRG.INVOICE_DATE IS NOT NULL
1626 AND MRG.ship_date >= MRG.invoice_date
1627 AND EXISTS (SELECT 1
1628 FROM OPI_EDW_MARGIN_PERD_ILOG
1629 WHERE MRG.ROWID = ROW_ID
1630 AND STATUS = 1);
1631 /*************************************************************************
1632 *** Update Margin Date FK Key with Revenue Date FK Key if Ship date is **
1633 *** Less than Invoice Date **
1634 **************************************************************************/
1635 --3836905 -- Added this for RMA transcations
1636 UPDATE OPI_EDW_MARGIN_F MRG
1637 SET MARGIN_DATE_FK_KEY = MRG.COGS_DATE_FK_KEY,
1638 invoice_date= MRG.ship_date ,
1639 GL_PERIOD_FK_KEY =
1640 (SELECT TIM2.CDAY_CAL_DAY_PK_KEY
1641 FROM EDW_TIME_M TIM1,
1642 EDW_TIME_M TIM2,
1643 EDW_TIME_CAL_PERIOD_LTC PERDLTC
1644 WHERE MRG.SHIP_DATE IS NOT NULL
1645 AND MRG.INVOICE_DATE IS NOT NULL
1646 AND MRG.COGS_DATE_FK_KEY = TIM1.CDAY_CAL_DAY_PK_KEY
1647 AND TIM1.CPER_CAL_PERIOD_PK_KEY= PERDLTC.CAL_PERIOD_PK_KEY
1648 AND TIM2.CDAY_CAL_DAY_PK = PERDLTC.CAL_PERIOD_PK ||'-CPER' )
1649 WHERE MRG.SHIP_DATE IS NOT NULL
1650 AND MRG.RMA_QTY IS NOT NULL
1651 AND EXISTS (SELECT 1
1652 FROM OPI_EDW_MARGIN_PERD_ILOG
1653 WHERE MRG.ROWID = ROW_ID
1654 AND STATUS = 1);
1655 -- End of addition --
1656 UPDATE OPI_EDW_MARGIN_F MRG
1657 SET MARGIN_DATE_FK_KEY = MRG.REVENUE_DATE_FK_KEY,
1658 GL_PERIOD_FK_KEY =
1659 (SELECT TIM2.CDAY_CAL_DAY_PK_KEY
1660 FROM EDW_TIME_M TIM1,
1661 EDW_TIME_M TIM2,
1662 EDW_TIME_CAL_PERIOD_LTC PERDLTC
1663 WHERE MRG.SHIP_DATE is NOT NULL
1664 AND MRG.INVOICE_DATE is NOT NULL
1665 AND MRG.REVENUE_DATE_FK_KEY = TIM1.CDAY_CAL_DAY_PK_KEY
1666 AND TIM1.CPER_CAL_PERIOD_PK_KEY= PERDLTC.CAL_PERIOD_PK_KEY
1667 AND TIM2.CDAY_CAL_DAY_PK = PERDLTC.CAL_PERIOD_PK ||'-CPER' )
1668 WHERE MRG.SHIP_DATE is NOT NULL
1669 AND MRG.INVOICE_DATE is NOT NULL
1670 AND MRG.ship_date < MRG.invoice_date
1671 AND EXISTS (SELECT 1
1672 FROM OPI_EDW_MARGIN_PERD_ILOG
1673 WHERE MRG.ROWID = ROW_ID
1674 AND STATUS = 1);
1675
1676 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1677 SET STATUS =2
1678 WHERE STATUS = 1;
1679 END LOOP;
1680 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1681 SET STATUS =0;
1682 COMMIT;
1683
1684 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F generated period FK KEY'||SQL%ROWCOUNT);
1685 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1686 /***************************************************************************************+
1687 ***** Delete Snapshot log of margin. Since this on derived fact collection engine *****
1688 ***** doesn't delete it. *****
1689 +****************************************************************************************/
1690
1691
1692 -- delete MLOG$_OPI_EDW_MARGIN_F;
1693 LOOP
1694 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1695 SET STATUS =1
1696 WHERE STATUS = 0 AND
1697 ROWNUM < l_chunk_size;
1698 IF SQL%ROWCOUNT = 0 THEN
1699 EXIT;
1700 END IF;
1701 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows in processing'||SQL%ROWCOUNT);
1702 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1703 COMMIT;
1704 INSERT INTO OPI_EDW_PERD_MARGIN_FT(
1705 ITEM_ORG_FK_KEY
1706 ,OPERATING_UNIT_FK_KEY
1707 ,BASE_CURRENCY_FK_KEY
1708 ,SOB_FK_KEY
1709 ,CUSTOMER_FK_KEY
1710 ,SALES_CHANNEL_FK_KEY
1711 ,INSTANCE_FK_KEY
1712 ,PRIM_SALES_REP_FK_KEY
1713 ,BILL_TO_LOC_FK_KEY
1714 ,SHIP_TO_LOC_FK_KEY
1715 ,PROJECT_FK_KEY
1716 ,UOM_FK_KEY
1720 ,USER_FK4_KEY
1717 ,SHIP_LOCATION_FK_KEY
1718 ,MARGIN_PERIOD_FK_KEY
1719 ,USER_FK5_KEY
1721 ,USER_FK3_KEY
1722 ,USER_FK2_KEY
1723 ,USER_FK1_KEY
1724 ,COGS_G
1725 ,COGS_B
1726 ,PROD_AMT_G
1727 ,PROD_AMT_B
1728 ,PROD_LINE_QTY_INVOICED
1729 ,PROD_LINE_QTY_CREDITED
1730 ,SHIPPED_QTY
1731 ,RMA_QTY
1732 ,ICAP_QTY
1733 ,USER_MEASURE5
1734 ,USER_MEASURE4
1735 ,USER_MEASURE3
1736 ,USER_MEASURE2
1737 ,USER_MEASURE1)
1738 (SELECT /*+ ALL_ROWS */
1739 ITEM_ORG_FK_KEY
1740 ,OPERATING_UNIT_FK_KEY
1741 ,BASE_CURRENCY_FK_KEY
1742 ,SOB_FK_KEY
1743 ,CUSTOMER_FK_KEY
1744 ,SALES_CHANNEL_FK_KEY
1745 ,INSTANCE_FK_KEY
1746 ,PRIM_SALES_REP_FK_KEY
1747 ,BILL_TO_LOC_FK_KEY
1748 ,SHIP_TO_LOC_FK_KEY
1749 ,PROJECT_FK_KEY
1750 ,UOM_FK_KEY
1751 ,SHIP_LOCATION_FK_KEY
1752 ,GL_PERIOD_FK_KEY
1753 ,USER_FK5_KEY
1754 ,USER_FK4_KEY
1755 ,USER_FK3_KEY
1756 ,USER_FK2_KEY
1757 ,USER_FK1_KEY
1758 ,SUM(COGS_G)
1759 ,SUM(COGS_B)
1760 ,SUM(PROD_AMT_G)
1761 ,SUM(PROD_AMT_B)
1762 ,SUM(PROD_LINE_QTY_INVOICED)
1763 ,SUM(PROD_LINE_QTY_CREDITED)
1764 ,SUM(SHIPPED_QTY)
1765 ,SUM(RMA_QTY)
1766 ,SUM(ICAP_QTY)
1767 ,SUM(USER_MEASURE5)
1768 ,SUM(USER_MEASURE4)
1769 ,SUM(USER_MEASURE3)
1770 ,SUM(USER_MEASURE2)
1771 ,SUM(USER_MEASURE1)
1772 FROM OPI_EDW_MARGIN_F, OPI_EDW_MARGIN_PERD_ILOG
1773 WHERE OPI_EDW_MARGIN_PERD_ILOG.ROW_ID = OPI_EDW_MARGIN_F.ROWID
1774 AND OPI_EDW_MARGIN_PERD_ILOG.STATUS = 1
1775 AND GL_PERIOD_FK_KEY IS NOT NULL
1776 AND GL_PERIOD_FK_KEY <> '0'
1777 GROUP BY
1778 ITEM_ORG_FK_KEY
1779 ,OPERATING_UNIT_FK_KEY
1780 ,BASE_CURRENCY_FK_KEY
1781 ,SOB_FK_KEY
1782 ,CUSTOMER_FK_KEY
1783 ,SALES_CHANNEL_FK_KEY
1784 ,INSTANCE_FK_KEY
1785 ,PRIM_SALES_REP_FK_KEY
1786 ,BILL_TO_LOC_FK_KEY
1787 ,SHIP_TO_LOC_FK_KEY
1788 ,PROJECT_FK_KEY
1789 ,UOM_FK_KEY
1790 ,SHIP_LOCATION_FK_KEY
1791 ,GL_PERIOD_FK_KEY
1792 ,USER_FK5_KEY
1793 ,USER_FK4_KEY
1794 ,USER_FK3_KEY
1795 ,USER_FK2_KEY
1796 ,USER_FK1_KEY);
1797
1798 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows moved to OPI_EDW_PERD_MARGIN_FT '||SQL%ROWCOUNT);
1799 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1800 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_FT');
1801
1802
1803 INSERT INTO OPI_EDW_PERD_MARGIN_FUR(
1804 COGS_G
1805 ,COGS_B
1806 ,PROD_AMT_G
1807 ,PROD_AMT_B
1808 ,PROD_LINE_QTY_INVOICED
1809 ,PROD_LINE_QTY_CREDITED
1810 ,SHIPPED_QTY
1811 ,RMA_QTY
1812 ,ICAP_QTY
1813 ,USER_MEASURE5
1814 ,USER_MEASURE4
1815 ,USER_MEASURE3
1816 ,USER_MEASURE2
1817 ,USER_MEASURE1
1818 ,ROW_ID
1819 ,ROW_ID1)
1820 (SELECT /*+ ALL_ROWS */
1821 NVL(MRGF.COGS_G,0) +NVL(MRGFT.COGS_G,0)
1822 ,NVL(MRGF.COGS_B,0) +NVL(MRGFT.COGS_B,0)
1823 ,NVL(MRGF.PROD_AMT_G,0) +NVL(MRGFT.PROD_AMT_G,0)
1824 ,NVL(MRGF.PROD_AMT_B,0) +NVL(MRGFT.PROD_AMT_B,0)
1825 ,NVL(MRGF.PROD_LINE_QTY_INVOICED,0) +NVL(MRGFT.PROD_LINE_QTY_INVOICED,0)
1826 ,NVL(MRGF.PROD_LINE_QTY_CREDITED,0) +NVL(MRGFT.PROD_LINE_QTY_CREDITED,0)
1827 ,NVL(MRGF.SHIPPED_QTY,0) +NVL(MRGFT.SHIPPED_QTY,0)
1828 ,NVL(MRGF.RMA_QTY,0) +NVL(MRGFT.RMA_QTY,0)
1829 ,NVL(MRGF.ICAP_QTY,0) +NVL(MRGFT.ICAP_QTY,0)
1830 ,NVL(MRGF.USER_MEASURE5,0) +NVL(MRGFT.USER_MEASURE5,0)
1831 ,NVL(MRGF.USER_MEASURE4,0) +NVL(MRGFT.USER_MEASURE4,0)
1832 ,NVL(MRGF.USER_MEASURE3,0) +NVL(MRGFT.USER_MEASURE3,0)
1833 ,NVL(MRGF.USER_MEASURE2,0) +NVL(MRGFT.USER_MEASURE2,0)
1834 ,NVL(MRGF.USER_MEASURE1,0) +NVL(MRGFT.USER_MEASURE1,0)
1835 ,MRGFT.ROWID
1836 ,MRGF.ROWID
1837 FROM OPI_EDW_PERD_MARGIN_F MRGF, OPI_EDW_PERD_MARGIN_FT MRGFT
1838 WHERE MRGF.ITEM_ORG_FK_KEY=MRGFT.ITEM_ORG_FK_KEY
1839 AND MRGF.OPERATING_UNIT_FK_KEY=MRGFT.OPERATING_UNIT_FK_KEY
1840 AND MRGF.BASE_CURR_FK_KEY = MRGFT.BASE_CURRENCY_FK_KEY
1841 AND MRGF.SOB_FK_KEY=MRGFT.SOB_FK_KEY
1842 AND MRGF.CUSTOMER_FK_KEY=MRGFT.CUSTOMER_FK_KEY
1843 AND MRGF.SALES_CHANNEL_FK_KEY=MRGFT.SALES_CHANNEL_FK_KEY
1844 AND MRGF.INSTANCE_FK_KEY=MRGFT.INSTANCE_FK_KEY
1845 AND MRGF.PRIM_SALES_REP_FK_KEY=MRGFT.PRIM_SALES_REP_FK_KEY
1846 AND MRGF.BILL_TO_LOC_FK_KEY=MRGFT.BILL_TO_LOC_FK_KEY
1847 AND MRGF.SHIP_TO_LOC_FK_KEY=MRGFT.SHIP_TO_LOC_FK_KEY
1848 AND MRGF.PROJECT_FK_KEY=MRGFT.PROJECT_FK_KEY
1849 AND MRGF.UOM_FK_KEY=MRGFT.UOM_FK_KEY
1850 AND MRGF.SHIP_LOCATION_FK_KEY=MRGFT.SHIP_LOCATION_FK_KEY
1851 AND MRGF.MARGIN_PERIOD_FK_KEY=MRGFT.MARGIN_PERIOD_FK_KEY
1852 AND NVL(MRGF.USER_FK5_KEY,0)=NVL(MRGFT.USER_FK5_KEY,0)
1853 AND NVL(MRGF.USER_FK4_KEY,0)=NVL(MRGFT.USER_FK4_KEY,0)
1854 AND NVL(MRGF.USER_FK3_KEY,0)=NVL(MRGFT.USER_FK3_KEY,0)
1855 AND NVL(MRGF.USER_FK2_KEY,0)=NVL(MRGFT.USER_FK2_KEY,0)
1856 AND NVL(MRGF.USER_FK1_KEY,0)=NVL(MRGFT.USER_FK1_KEY,0));
1857
1861
1858 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows moved to OPI_EDW_PERD_MARGIN_FUR '||SQL%ROWCOUNT);
1859 EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1860 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_FUR');
1862
1863 COMMIT;
1864
1865 INSERT INTO OPI_EDW_MARGIN_PERD_FIR (
1866 SELECT /*+ ALL_ROWS */ ROWID ROW_ID FROM OPI_EDW_PERD_MARGIN_FT
1867 MINUS
1868 SELECT /*+ ALL_ROWS */ ROW_ID ROW_ID FROM OPI_EDW_PERD_MARGIN_FUR);
1869
1870 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows to be inserted into Margin period summary '||SQL%ROWCOUNT);
1871
1872 l_insert := SQL%ROWCOUNT;
1873
1874 EDW_OWB_COLLECTION_UTIL.write_to_log_file('end Time '||to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1875 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MARGIN_PERD_FIR');
1876
1877
1878 IF l_insert <> 0 THEN
1879 INSERT INTO OPI_EDW_PERD_MARGIN_F
1880 (ITEM_ORG_FK_KEY
1881 ,OPERATING_UNIT_FK_KEY
1882 ,BASE_CURR_FK_KEY
1883 ,SOB_FK_KEY
1884 ,CUSTOMER_FK_KEY
1885 ,SALES_CHANNEL_FK_KEY
1886 ,INSTANCE_FK_KEY
1887 ,PRIM_SALES_REP_FK_KEY
1888 ,BILL_TO_LOC_FK_KEY
1889 ,SHIP_TO_LOC_FK_KEY
1890 ,PROJECT_FK_KEY
1891 ,UOM_FK_KEY
1892 ,SHIP_LOCATION_FK_KEY
1893 ,MARGIN_PERIOD_FK_KEY
1894 ,COGS_G
1895 ,COGS_B
1896 ,PROD_AMT_G
1897 ,PROD_AMT_B
1898 ,PROD_LINE_QTY_INVOICED
1899 ,PROD_LINE_QTY_CREDITED
1900 ,SHIPPED_QTY
1901 ,RMA_QTY
1902 ,ICAP_QTY
1903 ,LAST_UPDATE_DATE
1904 ,CREATION_DATE
1905 ,USER_FK5_KEY
1906 ,USER_FK4_KEY
1907 ,USER_FK3_KEY
1908 ,USER_FK2_KEY
1909 ,USER_FK1_KEY
1910 ,USER_MEASURE5
1911 ,USER_MEASURE4
1912 ,USER_MEASURE3
1913 ,USER_MEASURE2
1914 ,USER_MEASURE1)
1915 SELECT /*+ ALL_ROWS */
1916 ITEM_ORG_FK_KEY
1917 ,OPERATING_UNIT_FK_KEY
1918 ,BASE_CURRENCY_FK_KEY
1919 ,SOB_FK_KEY
1920 ,CUSTOMER_FK_KEY
1921 ,SALES_CHANNEL_FK_KEY
1922 ,INSTANCE_FK_KEY
1923 ,PRIM_SALES_REP_FK_KEY
1924 ,BILL_TO_LOC_FK_KEY
1925 ,SHIP_TO_LOC_FK_KEY
1926 ,PROJECT_FK_KEY
1927 ,UOM_FK_KEY
1928 ,SHIP_LOCATION_FK_KEY
1929 ,MARGIN_PERIOD_FK_KEY
1930 ,COGS_G
1931 ,COGS_B
1932 ,PROD_AMT_G
1933 ,PROD_AMT_B
1934 ,PROD_LINE_QTY_INVOICED
1935 ,PROD_LINE_QTY_CREDITED
1936 ,SHIPPED_QTY
1937 ,RMA_QTY
1938 ,ICAP_QTY
1939 ,SYSDATE
1940 ,SYSDATE
1941 ,USER_FK5_KEY
1942 ,USER_FK4_KEY
1943 ,USER_FK3_KEY
1944 ,USER_FK2_KEY
1945 ,USER_FK1_KEY
1946 ,USER_MEASURE5
1947 ,USER_MEASURE4
1948 ,USER_MEASURE3
1949 ,USER_MEASURE2
1950 ,USER_MEASURE1
1951 FROM OPI_EDW_PERD_MARGIN_FT ,
1952 OPI_EDW_MARGIN_PERD_FIR
1953 WHERE OPI_EDW_PERD_MARGIN_FT.ROWID = OPI_EDW_MARGIN_PERD_FIR.ROW_ID;
1954 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows inserted into Margin period summary '||SQL%ROWCOUNT);
1955 EDW_OWB_COLLECTION_UTIL.write_to_log_file('end Time '||to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1956 END IF;
1957 COMMIT;
1958
1959 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_F');
1960
1961 UPDATE OPI_EDW_PERD_MARGIN_F MRGF
1962 SET (COGS_G
1963 ,COGS_B
1964 ,PROD_AMT_G
1965 ,PROD_AMT_B
1966 ,PROD_LINE_QTY_INVOICED
1967 ,PROD_LINE_QTY_CREDITED
1968 ,SHIPPED_QTY
1969 ,RMA_QTY
1970 ,ICAP_QTY
1971 ,USER_MEASURE5
1972 ,USER_MEASURE4
1973 ,USER_MEASURE3
1974 ,USER_MEASURE2
1975 ,USER_MEASURE1) =
1976 (SELECT COGS_G
1977 ,COGS_B
1978 ,PROD_AMT_G
1979 ,PROD_AMT_B
1980 ,PROD_LINE_QTY_INVOICED
1981 ,PROD_LINE_QTY_CREDITED
1982 ,SHIPPED_QTY
1983 ,RMA_QTY
1984 ,ICAP_QTY
1985 ,USER_MEASURE5
1986 ,USER_MEASURE4
1987 ,USER_MEASURE3
1988 ,USER_MEASURE2
1989 ,USER_MEASURE1
1990 FROM OPI_EDW_PERD_MARGIN_FUR MRGFU
1991 WHERE MRGF.ROWID = MRGFU.ROW_ID1)
1992 WHERE EXISTS (SELECT 1
1993 FROM OPI_EDW_PERD_MARGIN_FUR MRGFU
1994 WHERE MRGF.ROWID = MRGFU.ROW_ID1);
1995 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows updated in Margin period summary '||SQL%ROWCOUNT);
1996 EDW_OWB_COLLECTION_UTIL.write_to_log_file('end Time '||to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1997 COMMIT;
1998 UPDATE OPI_EDW_MARGIN_PERD_ILOG
1999 SET STATUS =2
2000 WHERE STATUS = 1;
2001 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2002 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2003 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2004 END LOOP;
2005 INSERT INTO OPI_EDW_PERD_MARGIN_FT(
2006 ITEM_ORG_FK_KEY
2007 ,OPERATING_UNIT_FK_KEY
2008 ,BASE_CURRENCY_FK_KEY
2009 ,SOB_FK_KEY
2010 ,CUSTOMER_FK_KEY
2011 ,SALES_CHANNEL_FK_KEY
2012 ,INSTANCE_FK_KEY
2013 ,PRIM_SALES_REP_FK_KEY
2017 ,UOM_FK_KEY
2014 ,BILL_TO_LOC_FK_KEY
2015 ,SHIP_TO_LOC_FK_KEY
2016 ,PROJECT_FK_KEY
2018 ,SHIP_LOCATION_FK_KEY
2019 ,MARGIN_PERIOD_FK_KEY
2020 ,USER_FK5_KEY
2021 ,USER_FK4_KEY
2022 ,USER_FK3_KEY
2023 ,USER_FK2_KEY
2024 ,USER_FK1_KEY
2025 ,COGS_G
2026 ,COGS_B
2027 ,PROD_AMT_G
2028 ,PROD_AMT_B
2029 ,PROD_LINE_QTY_INVOICED
2030 ,PROD_LINE_QTY_CREDITED
2031 ,SHIPPED_QTY
2032 ,RMA_QTY
2033 ,ICAP_QTY
2034 ,USER_MEASURE5
2035 ,USER_MEASURE4
2036 ,USER_MEASURE3
2037 ,USER_MEASURE2
2038 ,USER_MEASURE1)
2039 (SELECT /*+ ALL_ROWS */
2040 ITEM_ORG_FK_KEY
2041 ,OPERATING_UNIT_FK_KEY
2042 ,BASE_CURRENCY_FK_KEY
2043 ,SOB_FK_KEY
2044 ,CUSTOMER_FK_KEY
2045 ,SALES_CHANNEL_FK_KEY
2046 ,INSTANCE_FK_KEY
2047 ,PRIM_SALES_REP_FK_KEY
2048 ,BILL_TO_LOC_FK_KEY
2049 ,SHIP_TO_LOC_FK_KEY
2050 ,PROJECT_FK_KEY
2051 ,UOM_FK_KEY
2052 ,SHIP_LOCATION_FK_KEY
2053 ,GL_PERIOD_FK_KEY
2054 ,USER_FK5_KEY
2055 ,USER_FK4_KEY
2056 ,USER_FK3_KEY
2057 ,USER_FK2_KEY
2058 ,USER_FK1_KEY
2059 ,SUM(COGS_G)
2060 ,SUM(COGS_B)
2061 ,SUM(PROD_AMT_G)
2062 ,SUM(PROD_AMT_B)
2063 ,SUM(PROD_LINE_QTY_INVOICED)
2064 ,SUM(PROD_LINE_QTY_CREDITED)
2065 ,SUM(SHIPPED_QTY)
2066 ,SUM(RMA_QTY)
2067 ,SUM(ICAP_QTY)
2068 ,SUM(USER_MEASURE5)
2069 ,SUM(USER_MEASURE4)
2070 ,SUM(USER_MEASURE3)
2071 ,SUM(USER_MEASURE2)
2072 ,SUM(USER_MEASURE1)
2073 FROM OPI_EDW_MARGIN_PERD_FDLG
2074 GROUP BY
2075 ITEM_ORG_FK_KEY
2076 ,OPERATING_UNIT_FK_KEY
2077 ,BASE_CURRENCY_FK_KEY
2078 ,SOB_FK_KEY
2079 ,CUSTOMER_FK_KEY
2080 ,SALES_CHANNEL_FK_KEY
2081 ,INSTANCE_FK_KEY
2082 ,PRIM_SALES_REP_FK_KEY
2083 ,BILL_TO_LOC_FK_KEY
2084 ,SHIP_TO_LOC_FK_KEY
2085 ,PROJECT_FK_KEY
2086 ,UOM_FK_KEY
2087 ,SHIP_LOCATION_FK_KEY
2088 ,GL_PERIOD_FK_KEY
2089 ,USER_FK5_KEY
2090 ,USER_FK4_KEY
2091 ,USER_FK3_KEY
2092 ,USER_FK2_KEY
2093 ,USER_FK1_KEY);
2094 COMMIT;
2095
2096 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_FT');
2097
2098
2099 INSERT INTO OPI_EDW_PERD_MARGIN_FUR(
2100 COGS_G
2101 ,COGS_B
2102 ,PROD_AMT_G
2103 ,PROD_AMT_B
2104 ,PROD_LINE_QTY_INVOICED
2105 ,PROD_LINE_QTY_CREDITED
2106 ,SHIPPED_QTY
2107 ,RMA_QTY
2108 ,ICAP_QTY
2109 ,USER_MEASURE5
2110 ,USER_MEASURE4
2111 ,USER_MEASURE3
2112 ,USER_MEASURE2
2113 ,USER_MEASURE1
2114 ,ROW_ID
2115 ,ROW_ID1)
2116 (SELECT /*+ ALL_ROWS */
2117 NVL(MRGF.COGS_G,0) -NVL(MRGFT.COGS_G,0)
2118 ,NVL(MRGF.COGS_B,0) -NVL(MRGFT.COGS_B,0)
2119 ,NVL(MRGF.PROD_AMT_G,0) -NVL(MRGFT.PROD_AMT_G,0)
2120 ,NVL(MRGF.PROD_AMT_B,0) -NVL(MRGFT.PROD_AMT_B,0)
2121 ,NVL(MRGF.PROD_LINE_QTY_INVOICED,0) -NVL(MRGFT.PROD_LINE_QTY_INVOICED,0)
2122 ,NVL(MRGF.PROD_LINE_QTY_CREDITED,0) -NVL(MRGFT.PROD_LINE_QTY_CREDITED,0)
2123 ,NVL(MRGF.SHIPPED_QTY,0) -NVL(MRGFT.SHIPPED_QTY,0)
2124 ,NVL(MRGF.RMA_QTY,0) -NVL(MRGFT.RMA_QTY,0)
2125 ,NVL(MRGF.ICAP_QTY,0) -NVL(MRGFT.ICAP_QTY,0)
2126 ,NVL(MRGF.USER_MEASURE5,0) -NVL(MRGFT.USER_MEASURE5,0)
2127 ,NVL(MRGF.USER_MEASURE4,0) -NVL(MRGFT.USER_MEASURE4,0)
2128 ,NVL(MRGF.USER_MEASURE3,0) -NVL(MRGFT.USER_MEASURE3,0)
2129 ,NVL(MRGF.USER_MEASURE2,0) -NVL(MRGFT.USER_MEASURE2,0)
2130 ,NVL(MRGF.USER_MEASURE1,0) -NVL(MRGFT.USER_MEASURE1,0)
2131 ,MRGFT.ROWID
2132 ,MRGF.ROWID
2133 FROM OPI_EDW_PERD_MARGIN_F MRGF, OPI_EDW_PERD_MARGIN_FT MRGFT
2134 WHERE MRGF.ITEM_ORG_FK_KEY=MRGFT.ITEM_ORG_FK_KEY
2135 AND MRGF.OPERATING_UNIT_FK_KEY=MRGFT.OPERATING_UNIT_FK_KEY
2136 AND MRGF.BASE_CURR_FK_KEY = MRGFT.BASE_CURRENCY_FK_KEY
2137 AND MRGF.SOB_FK_KEY=MRGFT.SOB_FK_KEY
2138 AND MRGF.CUSTOMER_FK_KEY=MRGFT.CUSTOMER_FK_KEY
2139 AND MRGF.SALES_CHANNEL_FK_KEY=MRGFT.SALES_CHANNEL_FK_KEY
2140 AND MRGF.INSTANCE_FK_KEY=MRGFT.INSTANCE_FK_KEY
2141 AND MRGF.PRIM_SALES_REP_FK_KEY=MRGFT.PRIM_SALES_REP_FK_KEY
2142 AND MRGF.BILL_TO_LOC_FK_KEY=MRGFT.BILL_TO_LOC_FK_KEY
2143 AND MRGF.SHIP_TO_LOC_FK_KEY=MRGFT.SHIP_TO_LOC_FK_KEY
2144 AND MRGF.PROJECT_FK_KEY=MRGFT.PROJECT_FK_KEY
2145 AND MRGF.UOM_FK_KEY=MRGFT.UOM_FK_KEY
2146 AND MRGF.SHIP_LOCATION_FK_KEY=MRGFT.SHIP_LOCATION_FK_KEY
2147 AND MRGF.MARGIN_PERIOD_FK_KEY=MRGFT.MARGIN_PERIOD_FK_KEY
2148 AND NVL(MRGF.USER_FK5_KEY,0)=NVL(MRGFT.USER_FK5_KEY,0)
2149 AND NVL(MRGF.USER_FK4_KEY,0)=NVL(MRGFT.USER_FK4_KEY,0)
2150 AND NVL(MRGF.USER_FK3_KEY,0)=NVL(MRGFT.USER_FK3_KEY,0)
2151 AND NVL(MRGF.USER_FK2_KEY,0)=NVL(MRGFT.USER_FK2_KEY,0)
2152 AND NVL(MRGF.USER_FK1_KEY,0)=NVL(MRGFT.USER_FK1_KEY,0));
2153 COMMIT;
2154
2155 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_FUR');
2156
2157
2158 UPDATE OPI_EDW_PERD_MARGIN_F MRGF
2159 SET (COGS_G
2160 ,COGS_B
2161 ,PROD_AMT_G
2162 ,PROD_AMT_B
2163 ,PROD_LINE_QTY_INVOICED
2164 ,PROD_LINE_QTY_CREDITED
2165 ,SHIPPED_QTY
2166 ,RMA_QTY
2170 ,USER_MEASURE3
2167 ,ICAP_QTY
2168 ,USER_MEASURE5
2169 ,USER_MEASURE4
2171 ,USER_MEASURE2
2172 ,USER_MEASURE1) =
2173 (SELECT COGS_G
2174 ,COGS_B
2175 ,PROD_AMT_G
2176 ,PROD_AMT_B
2177 ,PROD_LINE_QTY_INVOICED
2178 ,PROD_LINE_QTY_CREDITED
2179 ,SHIPPED_QTY
2180 ,RMA_QTY
2181 ,ICAP_QTY
2182 ,USER_MEASURE5
2183 ,USER_MEASURE4
2184 ,USER_MEASURE3
2185 ,USER_MEASURE2
2186 ,USER_MEASURE1
2187 FROM OPI_EDW_PERD_MARGIN_FUR MRGFU
2188 WHERE MRGF.ROWID = MRGFU.ROW_ID1)
2189 WHERE EXISTS (SELECT 1
2190 FROM OPI_EDW_PERD_MARGIN_FUR MRGFU
2191 WHERE MRGF.ROWID = MRGFU.ROW_ID1);
2192 COMMIT;
2193 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2194 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2195 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2196 OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2203 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_COGS_LOG');
2204 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
2205 OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
2206 END POST_MARGIN_COLL;
2207 PROCEDURE PRE_MARGIN_COLL IS
2208 BEGIN
2209 /* get global session parameters */
2210 IF NOT (fnd_installation.get_app_info( 'OPI', g_status,
2211 g_industry, g_opi_schema)) THEN
2212 RAISE_APPLICATION_ERROR (-20000, 'Unable to get session information.');
2213 END IF;
2214
2215 execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';
2216
2217 COMMIT;
2218
2219 END PRE_MARGIN_COLL;
2220 PROCEDURE POST_REVENUE_COLL IS
2221 BEGIN
2222
2223 /* get global session parameters */
2224 IF NOT (fnd_installation.get_app_info( 'OPI', g_status,
2225 g_industry, g_opi_schema)) THEN
2226 RAISE_APPLICATION_ERROR (-20000, 'Unable to get session information.');
2227 END IF;
2228
2229 execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';
2230 execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_REV_LOG';
2231
2232 COMMIT;
2233
2234 INSERT INTO OPI_EDW_REV_LOG (select /*+ ALL_ROWS */ distinct m_row$$,0 from MLOG$_FII_AR_TRX_DIST_F);
2235 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_REV_LOG');
2236 END;
2237 PROCEDURE POST_COGS_COLL IS
2238 BEGIN
2239
2240 /* get global session parameters */
2241 IF NOT (fnd_installation.get_app_info( 'OPI', g_status,
2242 g_industry, g_opi_schema)) THEN
2243 RAISE_APPLICATION_ERROR (-20000, 'Unable to get session information.');
2244 END IF;
2245
2246 execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';
2247 execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_COGS_LOG';
2248
2249 COMMIT;
2250
2251 INSERT INTO OPI_EDW_COGS_LOG (select /*+ ALL_ROWS */ distinct m_row$$,0 from MLOG$_OPI_EDW_COGS_F);
2252 OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_COGS_LOG');
2253 END;
2254 PROCEDURE GATHER_STATS(P_TABLE_NAME VARCHAR2) IS
2255 l_TABLE_OWNER VARCHAR2(32);
2256 BEGIN
2257 -- --
2258 --
2259 -- Find owner of the table passed to procedure
2260 --
2261 -- --
2262 SELECT TABLE_OWNER INTO l_table_owner
2263 FROM USER_SYNONYMS
2264 WHERE SYNONYM_NAME = P_TABLE_NAME;
2265
2266 --
2267 --
2268 -- Gather table statistics these stats will be used by CBO for query optimisation.
2269 --
2270 --
2271
2272 FND_STATS.GATHER_TABLE_STATS(l_table_owner,P_TABLE_NAME);
2273
2274 END GATHER_STATS;
2275
2276 PROCEDURE TURNC_TAB (p_table_name VARCHAR2) IS
2277 l_TABLE_OWNER VARCHAR2(32);
2278 l_stmt VARCHAR2(200);
2279 BEGIN
2280 -- --
2281 --
2282 -- Find owner of the table passed to procedure
2283 --
2284 -- --
2285 SELECT TABLE_OWNER INTO l_table_owner
2286 FROM USER_SYNONYMS
2287 WHERE SYNONYM_NAME = P_TABLE_NAME;
2288
2289 --
2290 --
2291 -- Truncate the table
2292 --
2293 --
2294 IF l_table_owner IS NOT NULL THEN
2295 l_stmt := 'truncate table '||l_table_owner||'.'||P_TABLE_NAME;
2296 execute immediate l_stmt;
2297 END IF;
2298 EXCEPTION WHEN OTHERS THEN
2299 NULL;
2300 END TURNC_TAB;
2301
2302
2303 End OPI_COLLECTION_HOOK_P;