[Home] [Help]
PACKAGE BODY: APPS.CSD_HVR_BI_PVT
Source
1 PACKAGE BODY CSD_HVR_BI_PVT AS
2 /* $Header: csdvhbib.pls 120.1 2005/08/23 17:26:03 vkjain noship $ */
3
4 /*--------------------------------------------------*/
5 /* procedure name: get_last_run_date */
6 /* description : procedure used to get */
7 /* the last run date for the ETL */
8 /*--------------------------------------------------*/
9 FUNCTION get_last_run_date(p_fact_name VARCHAR2) RETURN DATE IS
10 l_last_run_date DATE;
11
12 BEGIN
13
14 SELECT last_run_date
15 INTO l_last_run_date
16 FROM csd_fact_details
17 WHERE fact_name = p_fact_name;
18
19 RETURN l_last_run_date;
20
21 EXCEPTION
22
23 WHEN NO_DATA_FOUND THEN
24 -- 'Please launch the Initial Refresh for the High Volume Repair business data analysis process.'
25 FND_MESSAGE.SET_NAME('CSD','CSD_HVR_BI_RUN_INITIAL_LOAD');
26 FND_MSG_PUB.ADD;
27 RAISE;
28
29 END get_last_run_date;
30
31 /*--------------------------------------------------*/
32 /* procedure name: Refresh_Mviews */
33 /* description : procedure to refresh all related */
34 /* mviews */
35 /*--------------------------------------------------*/
36 PROCEDURE Refresh_Mviews (p_method IN varchar2) IS
37
38 BEGIN
39
40 -- refresh the mviews for the HVR Execution that
41 -- are required for "Most Common Materials/Resources"
42 DBMS_MVIEW.refresh(list => 'CSD_RO_PER_ITEM_MV',
43 method => p_method
44 -- method => '?'
45 -- rollback_seg := NULL,
46 -- push_deferred_rpc := TRUE,
47 -- refresh_after_errors := FALSE,
48 -- purge_option := 1,
49 -- parallelism := 0,
50 -- heap_size := 0,
51 -- atomic_refresh := TRUE
52 );
53
54 DBMS_MVIEW.refresh(list => 'CSD_WIP_MTL_USED_MV',
55 method => p_method
56 -- method => '?'
57 -- rollback_seg := NULL,
58 -- push_deferred_rpc := TRUE,
59 -- refresh_after_errors := FALSE,
60 -- purge_option := 1,
61 -- parallelism := 0,
62 -- heap_size := 0,
63 -- atomic_refresh := TRUE
64 );
65
66 dBMS_MVIEW.refresh(list => 'CSD_WIP_RES_USED_MV',
67 method => p_method
68 -- method => '?'
69 -- rollback_seg := NULL,
70 -- push_deferred_rpc := TRUE,
71 -- refresh_after_errors := FALSE,
72 -- purge_option := 1,
73 -- parallelism := 0,
74 -- heap_size := 0,
75 -- atomic_refresh := TRUE
76 );
77
78 -- Refresh the mviews for the HVR SC,DC
79 -- recommendations;required for "Frequency"
80 -- The following mviews refresh is dependent
81 -- on the refresh above (CSD_RO_PER_ITEM_MV)
82 -- DO NOT reorder the refresh sequence.
83 DBMS_MVIEW.refresh(list => 'CSD_DC_FREQ_SUM_MV',
84 method => p_method
85 -- method => '?'
86 -- rollback_seg := NULL,
87 -- push_deferred_rpc := TRUE,
88 -- refresh_after_errors := FALSE,
89 -- purge_option := 1,
90 -- parallelism := 0,
91 -- heap_size := 0,
92 -- atomic_refresh := TRUE
93 );
94
95 DBMS_MVIEW.refresh(list => 'CSD_SC_FREQ_SUM_MV',
96 method => p_method
97 -- method => '?'
98 -- rollback_seg := NULL,
99 -- push_deferred_rpc := TRUE,
100 -- refresh_after_errors := FALSE,
101 -- purge_option := 1,
102 -- parallelism := 0,
103 -- heap_size := 0,
104 -- atomic_refresh := TRUE
105 );
106
107 END Refresh_Mviews;
108
109 /*--------------------------------------------------*/
110 /* procedure name: Initial_Load_Ro_ETL */
111 /* description : procedure to load Repair Orders */
112 /* fact initially. */
113 /*--------------------------------------------------*/
114 PROCEDURE Initial_Load_Ro_ETL(errbuf IN OUT NOCOPY VARCHAR2,
115 retcode IN OUT NOCOPY VARCHAR2)
116
117 IS
118
119 -- Variables --
120 l_run_date DATE;
121 l_user_id NUMBER;
122 l_login_id NUMBER;
123 l_program_id NUMBER;
124 l_program_login_id NUMBER;
125 l_program_application_id NUMBER;
126 l_request_id NUMBER;
127
128 -- Constants --
129 lc_proc_name CONSTANT VARCHAR2(30) := 'Initial_Load_Ro_ETL';
130
131 BEGIN
132 l_user_id := NVL(fnd_global.USER_ID, -1);
133 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
134 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
135 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
136 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
137 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
138
139 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the initial refresh process for Repair Orders fact ...');
140
141 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Deleting record from CSD_FACT_DETAILS for CSD_REPAIR_ORDERS_F name ...');
142
143 DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_REPAIR_ORDERS_F;
144
145 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
146
147 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncating table CSD_REPAIR_ORDERS_F ...');
148
149 EXECUTE IMMEDIATE ('TRUNCATE TABLE CSD.CSD_REPAIR_ORDERS_F');
150
151 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncate successful.');
152
153 l_run_date := sysdate - 5 / (24 * 60);
154
155 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_REPAIR_ORDERS_F ...');
156
157 INSERT INTO CSD_REPAIR_ORDERS_F
158 (repair_line_id,
159 inventory_item_id,
160 primary_quantity,
161 primary_uom_code,
162 ro_creation_date,
163 date_closed,
164 Status,
165 created_by,
166 creation_date,
167 last_update_date,
168 last_updated_by,
169 last_update_login,
170 program_id,
171 program_login_id,
172 program_application_id,
173 request_id)
174 SELECT RO.repair_line_id,
175 RO.inventory_item_id,
176 (RO.quantity * UOM.conversion_rate) primary_quantity,
177 UOM.primary_uom_code primary_uom_code,
178 RO.creation_date,
179 RO.date_closed,
180 RO.status,
181 l_user_id,
182 sysdate,
183 sysdate,
184 l_user_id,
185 l_login_id,
186 l_program_id,
187 l_program_login_id,
188 l_program_application_id,
189 l_request_id
190 FROM CSD_REPAIRS RO, mtl_uom_conversions_view UOM
191 WHERE RO.status = 'C'
192 AND RO.repair_mode = 'WIP'
193 AND UOM.inventory_item_id = RO.inventory_item_id
194 AND UOM.organization_id = RO.inventory_org_id
195 AND UOM.uom_code = RO.unit_of_measure;
196
197 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
198
199 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
200
201 INSERT INTO CSD_FACT_DETAILS
202 (fact_name,
203 last_run_date,
204 created_by,
205 creation_date,
206 last_update_date,
207 last_updated_by,
208 last_update_login,
209 program_id,
210 program_login_id,
211 program_application_id,
212 request_id)
213 VALUES
214 (C_CSD_REPAIR_ORDERS_F,
215 l_run_date,
216 l_user_id,
217 sysdate,
218 sysdate,
219 l_user_id,
220 l_login_id,
221 l_program_id,
222 l_program_login_id,
223 l_program_application_id,
224 l_request_id);
225
226 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
227
228 commit;
229 retcode := C_OK;
230
231 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the initial refresh process for Repair Orders fact ...');
232
233 EXCEPTION
234
235 WHEN OTHERS THEN
236 retcode := C_ERROR;
237 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
238 ROLLBACK;
239 RAISE;
240
241 END Initial_Load_Ro_ETL;
242
243 /*--------------------------------------------------*/
244 /* procedure name: Incr_Load_Ro_ETL */
245 /* description : procedure to load Repair Orders */
246 /* fact incrementally */
247 /*--------------------------------------------------*/
248 PROCEDURE Incr_Load_Ro_ETL(errbuf in out NOCOPY VARCHAR2,
249 retcode IN OUT NOCOPY VARCHAR2)
250
251 IS
252
253 -- Variables --
254 l_run_date DATE;
255 l_last_run_date DATE;
256 l_user_id NUMBER;
257 l_login_id NUMBER;
258 l_program_id NUMBER;
259 l_program_login_id NUMBER;
260 l_program_application_id NUMBER;
261 l_request_id NUMBER;
262
263 -- Constants --
264 lc_proc_name CONSTANT VARCHAR2(30) := 'Incr_Load_Ro_ETL';
265
266 BEGIN
267
268 l_user_id := NVL(fnd_global.USER_ID, -1);
269 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
270 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
271 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
272 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
273 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
274
275 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the incremental refresh process for Repair Orders fact ...');
276
277 l_last_run_date := get_last_run_date(C_CSD_REPAIR_ORDERS_F);
278
279 l_run_date := sysdate - 5 / (24 * 60);
280
281 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Merging data into CSD_REPAIR_ORDERS_F ...');
282
283 MERGE INTO CSD_REPAIR_ORDERS_F fact
284 USING (SELECT RO.repair_line_id,
285 RO.inventory_item_id,
286 (RO.quantity * UOM.conversion_rate) primary_quantity,
287 UOM.primary_uom_code primary_uom_code,
288 RO.creation_date,
289 RO.date_closed,
290 RO.status
291 FROM CSD_REPAIRS RO,
292 mtl_uom_conversions_view UOM
293 WHERE
294 -- RO.status = 'C' AND
295 RO.repair_mode = 'WIP'
296 AND UOM.inventory_item_id = RO.inventory_item_id
297 AND UOM.organization_id = RO.inventory_org_id
298 AND UOM.uom_code = RO.unit_of_measure
299 AND RO.last_update_date > l_last_run_date) OLTP
300 ON (fact.repair_line_id = OLTP.repair_line_id)
301 WHEN MATCHED THEN
302 UPDATE
303 SET fact.inventory_item_id = OLTP.inventory_item_id,
304 fact.primary_quantity = OLTP.primary_quantity,
305 fact.primary_uom_code = OLTP.primary_uom_code,
306 fact.date_closed = OLTP.date_closed,
307 fact.status = OLTP.status,
308 fact.last_update_date = sysdate,
309 fact.last_updated_by = l_user_id,
310 fact.last_update_login = l_login_id,
311 fact.program_id = l_program_id,
312 fact.program_login_id = l_program_login_id,
313 fact.program_application_id = l_program_application_id,
314 fact.request_id = l_request_id
315 WHEN NOT MATCHED THEN
316 INSERT
317 VALUES
318 (OLTP.repair_line_id,
319 OLTP.inventory_item_id,
320 OLTP.primary_quantity,
321 OLTP.primary_uom_code,
322 OLTP.creation_date,
326 sysdate,
323 OLTP.date_closed,
324 OLTP.status,
325 l_user_id,
327 sysdate,
328 l_user_id,
329 l_login_id,
330 l_program_id,
331 l_program_login_id,
332 l_program_application_id,
333 l_request_id);
334
335 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Merge complete.');
336
337 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Updating CSD_FACT_DETAILS ...');
338
339 UPDATE CSD_FACT_DETAILS
340 SET last_run_date = l_run_date,
341 last_update_date = sysdate,
342 last_updated_by = l_user_id,
343 last_update_login = l_login_id,
344 program_id = l_program_id,
345 program_login_id = l_program_login_id,
346 program_application_id = l_program_application_id,
347 request_id = l_request_id
348 WHERE fact_name = C_CSD_REPAIR_ORDERS_F;
349
350 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Update complete.');
351
352 commit;
353 retcode := C_OK;
354
355 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the incremental refresh process for Repair Orders fact ...');
356
357 EXCEPTION
358
359 WHEN OTHERS THEN
360
361 retcode := C_ERROR;
362 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
363 ROLLBACK;
364 RAISE;
365
366 END Incr_Load_Ro_ETL;
367
368 /*--------------------------------------------------*/
369 /* procedure name: Initial_Load_Mtl_ETL */
370 /* description : procedure to load Materials */
371 /* Consumed fact initially. */
372 /*--------------------------------------------------*/
373
374 PROCEDURE Initial_Load_Mtl_ETL(errbuf IN OUT NOCOPY VARCHAR2,
375 retcode IN OUT NOCOPY VARCHAR2)
376
377 IS
378
379 -- Variables --
380 l_run_date DATE;
381 l_user_id NUMBER;
382 l_login_id NUMBER;
383 l_program_id NUMBER;
384 l_program_login_id NUMBER;
385 l_program_application_id NUMBER;
386 l_request_id NUMBER;
387
388 -- Constants --
389 lc_proc_name CONSTANT VARCHAR2(30) := 'Initial_Load_Mtl_ETL';
390
391 BEGIN
392 l_user_id := NVL(fnd_global.USER_ID, -1);
393 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
394 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
395 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
396 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
397 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
398
399 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the initial refresh process for Materials Consumed fact ...');
400
401 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Deleting record from CSD_FACT_DETAILS for CSD_MTL_CONSUMED_F name ...');
402
403 DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_MTL_CONSUMED_F;
404
405 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
406
407 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncating table CSD_MTL_CONSUMED_F ...');
408
409 EXECUTE IMMEDIATE ('TRUNCATE TABLE CSD.CSD_MTL_CONSUMED_F');
410
411 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncate successful.');
412
413 l_run_date := sysdate - 5 / (24 * 60);
414
415 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_MTL_CONSUMED_F ...');
416
417 INSERT INTO CSD_MTL_CONSUMED_F
418 (repair_line_id,
419 inventory_item_id,
420 primary_quantity,
421 primary_uom_code,
425 last_updated_by,
422 created_by,
423 creation_date,
424 last_update_date,
426 last_update_login,
427 program_id,
428 program_login_id,
429 program_application_id,
430 request_id)
431 SELECT RO.repair_line_id,
432 mmt.inventory_item_id INVENTORY_ITEM_ID,
433 SUM(DECODE(MMT.transaction_type_id,
434 lc_MTL_TXN_TYPE_COMP_ISSUE,
435 ABS(mmt.primary_quantity),
436 lc_MTL_TXN_TYPE_COMP_RETURN,
437 (-1 * ABS(mmt.primary_quantity)))) QUANTITY,
438 MSI.primary_uom_code UOM,
439 l_user_id,
440 sysdate,
441 sysdate,
442 l_user_id,
443 l_login_id,
444 l_program_id,
445 l_program_login_id,
446 l_program_application_id,
447 l_request_id
448 FROM CSD_REPAIR_ORDERS_F RO,
449 CSD_REPAIR_JOB_XREF XREF,
450 WIP_DISCRETE_JOBS DJOB,
451 MTL_MATERIAL_TRANSACTIONS MMT,
452 MTL_SYSTEM_ITEMS_B MSI
453 WHERE RO.status = 'C'
454 AND XREF.repair_line_id = RO.repair_line_id
455 AND XREF.inventory_item_id = RO.inventory_item_id
456 AND DJOB.wip_entity_id = XREF.wip_entity_id
457 AND DJOB.status_type in (4, 5, 12)
458 AND MMT.transaction_source_id = DJOB.wip_entity_id
459 AND MMT.transaction_source_type_id = 5 -- 'WIP'
460 AND (MMT.transaction_type_id = lc_MTL_TXN_TYPE_COMP_ISSUE
461 OR
462 MMT.transaction_type_id = lc_MTL_TXN_TYPE_COMP_RETURN)
463 AND MMT.inventory_item_id <> RO.inventory_item_id
464 AND MSI.inventory_item_id = MMT.inventory_item_id
465 AND MSI.organization_id = XREF.organization_id
466 GROUP BY RO.repair_line_id,
467 MMT.inventory_item_id,
468 MSI.primary_uom_code;
469
470 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
471
472 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
473
474 INSERT INTO CSD_FACT_DETAILS
475 (fact_name,
476 last_run_date,
477 created_by,
478 creation_date,
479 last_update_date,
480 last_updated_by,
481 last_update_login,
482 program_id,
483 program_login_id,
484 program_application_id,
485 request_id)
486 VALUES
487 (C_CSD_MTL_CONSUMED_F,
488 l_run_date,
489 l_user_id,
490 sysdate,
491 sysdate,
492 l_user_id,
493 l_login_id,
494 l_program_id,
495 l_program_login_id,
496 l_program_application_id,
497 l_request_id);
498
499 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
500
501 commit;
502 retcode := C_OK;
503
504 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the initial refresh process for Materials Consumed fact ...');
505
506 EXCEPTION
507
508 WHEN OTHERS THEN
509 retcode := C_ERROR;
510 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
511 ROLLBACK;
512 RAISE;
513
514 END Initial_Load_Mtl_ETL;
515
516 /*--------------------------------------------------*/
517 /* procedure name: Incr_Load_Mtl_ETL */
518 /* description : procedure to load Materials */
519 /* Consumed fact incrementally */
520 /*--------------------------------------------------*/
521 PROCEDURE Incr_Load_Mtl_ETL(errbuf in out NOCOPY VARCHAR2,
522 retcode IN OUT NOCOPY VARCHAR2)
526 -- Variables --
523
524 IS
525
527 l_run_date DATE;
528 l_last_run_date DATE;
529 l_user_id NUMBER;
530 l_login_id NUMBER;
531 l_program_id NUMBER;
532 l_program_login_id NUMBER;
533 l_program_application_id NUMBER;
534 l_request_id NUMBER;
535
536 -- Constants --
537 lc_proc_name CONSTANT VARCHAR2(30) := 'Incr_Load_Mtl_ETL';
538
539 BEGIN
540
541 l_user_id := NVL(fnd_global.USER_ID, -1);
542 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
543 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
544 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
545 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
546 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
547
548 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the incremental refresh process for Materials Consumed fact ...');
549
550 l_last_run_date := get_last_run_date(C_CSD_MTL_CONSUMED_F);
551
552 l_run_date := sysdate - 5 / (24 * 60);
553
554 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Merging data into CSD_MTL_CONSUMED_F ...');
555
556 MERGE INTO CSD_MTL_CONSUMED_F fact
557 USING (SELECT RO.repair_line_id,
558 mmt.inventory_item_id INVENTORY_ITEM_ID,
559 CEIL(SUM(DECODE(MMT.transaction_type_id,
560 lc_MTL_TXN_TYPE_COMP_ISSUE,
561 ABS(mmt.primary_quantity),
562 lc_MTL_TXN_TYPE_COMP_RETURN,
563 (-1 * ABS(mmt.primary_quantity))))) PRIMARY_QUANTITY,
564 MSI.primary_uom_code PRIMARY_UOM_CODE
565 FROM CSD_REPAIR_ORDERS_F RO,
566 CSD_REPAIR_JOB_XREF XREF,
567 WIP_DISCRETE_JOBS DJOB,
568 MTL_MATERIAL_TRANSACTIONS MMT,
569 MTL_SYSTEM_ITEMS_B MSI
570 WHERE RO.status = 'C'
571 AND XREF.repair_line_id = RO.repair_line_id
572 AND XREF.inventory_item_id = RO.inventory_item_id
573 AND DJOB.wip_entity_id = XREF.wip_entity_id
574 AND DJOB.status_type in (4, 5, 12)
575 AND MMT.transaction_source_id = DJOB.wip_entity_id
576 AND MMT.transaction_source_type_id = 5 -- 'WIP'
577 AND (MMT.transaction_type_id = lc_MTL_TXN_TYPE_COMP_ISSUE
578 OR
579 MMT.transaction_type_id = lc_MTL_TXN_TYPE_COMP_RETURN)
580 AND MMT.inventory_item_id <> RO.inventory_item_id
581 AND MSI.inventory_item_id = MMT.inventory_item_id
582 AND MSI.organization_id = XREF.organization_id
583 AND RO.last_update_date > l_last_run_date
584 GROUP BY RO.repair_line_id,
585 MMT.inventory_item_id,
586 MSI.primary_uom_code) OLTP
587 ON (fact.repair_line_id = OLTP.repair_line_id AND fact.inventory_item_id = OLTP.inventory_item_id)
588 WHEN MATCHED THEN
589 UPDATE
590 SET fact.primary_quantity = OLTP.primary_quantity,
591 fact.primary_uom_code = OLTP.primary_uom_code,
592 fact.last_update_date = sysdate,
593 fact.last_updated_by = l_user_id,
594 fact.last_update_login = l_login_id,
595 fact.program_id = l_program_id,
596 fact.program_login_id = l_program_login_id,
597 fact.program_application_id = l_program_application_id,
598 fact.request_id = l_request_id
599 WHEN NOT MATCHED THEN
600 INSERT
601 VALUES
602 (OLTP.repair_line_id,
603 OLTP.inventory_item_id,
604 OLTP.primary_quantity,
605 OLTP.primary_uom_code,
606 l_user_id,
607 sysdate,
608 sysdate,
609 l_user_id,
610 l_login_id,
611 l_program_id,
612 l_program_login_id,
613 l_program_application_id,
614 l_request_id);
615
616 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Merge complete.');
617
618 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Updating CSD_FACT_DETAILS ...');
619
620 UPDATE CSD_FACT_DETAILS
621 SET last_run_date = l_run_date,
622 last_update_date = sysdate,
623 last_updated_by = l_user_id,
624 last_update_login = l_login_id,
625 program_id = l_program_id,
626 program_login_id = l_program_login_id,
627 program_application_id = l_program_application_id,
628 request_id = l_request_id
629 WHERE fact_name = C_CSD_MTL_CONSUMED_F;
630
631 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Update complete.');
632
633 commit;
634 retcode := C_OK;
635
636 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the incremental refresh process for Materials Consumed fact ...');
637
638 EXCEPTION
639
640 WHEN OTHERS THEN
641
642 retcode := C_ERROR;
643 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
644 ROLLBACK;
645 RAISE;
646
647 END Incr_Load_Mtl_ETL;
648
649 /*--------------------------------------------------*/
650 /* procedure name: Initial_Load_Res_ETL */
651 /* description : procedure to load Resources */
655 PROCEDURE Initial_Load_Res_ETL(errbuf IN OUT NOCOPY VARCHAR2,
652 /* Consumed fact initially. */
653 /*--------------------------------------------------*/
654
656 retcode IN OUT NOCOPY VARCHAR2)
657
658 IS
659
660 -- Variables --
661 l_run_date DATE;
662 l_user_id NUMBER;
663 l_login_id NUMBER;
664 l_program_id NUMBER;
665 l_program_login_id NUMBER;
666 l_program_application_id NUMBER;
667 l_request_id NUMBER;
668
669 -- Constants --
670 lc_proc_name CONSTANT VARCHAR2(30) := 'Initial_Load_Res_ETL';
671
672 BEGIN
673 l_user_id := NVL(fnd_global.USER_ID, -1);
674 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
675 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
676 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
677 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
678 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
679
680 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the initial refresh process for Resources Consumed fact ...');
681
682 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Deleting record from CSD_FACT_DETAILS for CSD_RES_CONSUMED_F name ...');
683
684 DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_RES_CONSUMED_F;
685
686 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
687
688 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncating table CSD_RES_CONSUMED_F ...');
689
690 EXECUTE IMMEDIATE ('TRUNCATE TABLE CSD.CSD_RES_CONSUMED_F');
691
692 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncate successful.');
693
694 l_run_date := sysdate - 5 / (24 * 60);
695
696 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_RES_CONSUMED_F ...');
697
698 INSERT INTO CSD_RES_CONSUMED_F
699 (repair_line_id,
700 resource_id,
701 primary_quantity,
702 primary_uom_code,
703 created_by,
704 creation_date,
705 last_update_date,
706 last_updated_by,
707 last_update_login,
708 program_id,
709 program_login_id,
710 program_application_id,
711 request_id)
712 SELECT RO.repair_line_id,
713 WTXN.resource_id resource_id,
714 SUM(NVL(WTXN.primary_quantity, 0)) primary_quantity,
715 WTXN.primary_uom primary_uom_code,
716 l_user_id,
717 sysdate,
718 sysdate,
719 l_user_id,
720 l_login_id,
721 l_program_id,
722 l_program_login_id,
723 l_program_application_id,
724 l_request_id
725 FROM CSD_REPAIR_ORDERS_F RO,
726 CSD_REPAIR_JOB_XREF XREF,
727 WIP_DISCRETE_JOBS DJOB,
728 WIP_TRANSACTIONS WTXN
729 WHERE RO.status = 'C'
730 AND XREF.repair_line_id = RO.repair_line_id
731 AND XREF.inventory_item_id = RO.inventory_item_id
732 AND DJOB.wip_entity_id = XREF.wip_entity_id
733 AND DJOB.status_type in (4, 5, 12)
734 AND WTXN.wip_entity_id = DJOB.wip_entity_id
735 AND WTXN.transaction_type IN (1, 2, 3)
736 AND WTXN.resource_id IS NOT NULL
737 GROUP BY RO.repair_line_id, WTXN.primary_uom, WTXN.resource_id;
738
739 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
740
741 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
742
743 INSERT INTO CSD_FACT_DETAILS
744 (fact_name,
745 last_run_date,
746 created_by,
747 creation_date,
748 last_update_date,
749 last_updated_by,
750 last_update_login,
751 program_id,
752 program_login_id,
753 program_application_id,
754 request_id)
755 VALUES
756 (C_CSD_RES_CONSUMED_F,
757 l_run_date,
758 l_user_id,
759 sysdate,
760 sysdate,
761 l_user_id,
762 l_login_id,
763 l_program_id,
764 l_program_login_id,
765 l_program_application_id,
766 l_request_id);
767
768 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
769
770 commit;
771 retcode := C_OK;
772
773 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the initial refresh process for Resources Consumed fact ...');
774
775 EXCEPTION
776
777 WHEN OTHERS THEN
778 retcode := C_ERROR;
779 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
780 ROLLBACK;
781 RAISE;
782
783 END Initial_Load_Res_ETL;
784
785 /*--------------------------------------------------*/
786 /* procedure name: Incr_Load_Res_ETL */
787 /* description : procedure to load Resources */
788 /* Consumed fact incrementally */
789 /*--------------------------------------------------*/
790 PROCEDURE Incr_Load_Res_ETL(errbuf in out NOCOPY VARCHAR2,
791 retcode IN OUT NOCOPY VARCHAR2)
792
793 IS
794
795 -- Variables --
796 l_run_date DATE;
797 l_last_run_date DATE;
798 l_user_id NUMBER;
799 l_login_id NUMBER;
800 l_program_id NUMBER;
801 l_program_login_id NUMBER;
802 l_program_application_id NUMBER;
803 l_request_id NUMBER;
804
805 -- Constants --
806 lc_proc_name CONSTANT VARCHAR2(30) := 'Incr_Load_Res_ETL';
807
808 BEGIN
809
810 l_user_id := NVL(fnd_global.USER_ID, -1);
811 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
812 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
813 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
814 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
815 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
816
817 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the incremental refresh process for Resources Consumed fact ...');
818
819 l_last_run_date := get_last_run_date(C_CSD_RES_CONSUMED_F);
820
821 l_run_date := sysdate - 5 / (24 * 60);
822
823 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Merging data into CSD_RES_CONSUMED_F ...');
824
825 MERGE INTO CSD_RES_CONSUMED_F fact
826 USING (SELECT RO.repair_line_id,
827 WTXN.resource_id resource_id,
828 SUM(NVL(WTXN.primary_quantity, 0)) primary_quantity,
829 WTXN.primary_uom primary_uom_code
830 FROM CSD_REPAIR_ORDERS_F RO,
831 CSD_REPAIR_JOB_XREF XREF,
832 WIP_DISCRETE_JOBS DJOB,
833 WIP_TRANSACTIONS WTXN
834 WHERE RO.status = 'C'
835 AND XREF.repair_line_id = RO.repair_line_id
836 AND XREF.inventory_item_id = RO.inventory_item_id
837 AND DJOB.wip_entity_id = XREF.wip_entity_id
838 AND DJOB.status_type in (4, 5, 12)
839 AND WTXN.wip_entity_id = DJOB.wip_entity_id
840 AND WTXN.transaction_type IN (1, 2, 3)
841 AND WTXN.resource_id IS NOT NULL
842 AND RO.last_update_date > l_last_run_date
843 GROUP BY RO.repair_line_id, WTXN.primary_uom, WTXN.resource_id) OLTP
844 ON (fact.repair_line_id = OLTP.repair_line_id AND fact.resource_id = OLTP.resource_id)
845 WHEN MATCHED THEN
846 UPDATE
847 SET fact.primary_quantity = OLTP.primary_quantity,
848 fact.primary_uom_code = OLTP.primary_uom_code,
849 fact.last_update_date = sysdate,
850 fact.last_updated_by = l_user_id,
851 fact.last_update_login = l_login_id,
852 fact.program_id = l_program_id,
853 fact.program_login_id = l_program_login_id,
854 fact.program_application_id = l_program_application_id,
855 fact.request_id = l_request_id
856 WHEN NOT MATCHED THEN
857 INSERT
858 VALUES
859 (OLTP.repair_line_id,
863 l_user_id,
860 OLTP.resource_id,
861 OLTP.primary_quantity,
862 OLTP.primary_uom_code,
864 sysdate,
865 sysdate,
866 l_user_id,
867 l_login_id,
868 l_program_id,
869 l_program_login_id,
870 l_program_application_id,
871 l_request_id);
872
873 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Merge complete.');
874
875 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Updating CSD_FACT_DETAILS ...');
876
877 UPDATE CSD_FACT_DETAILS
878 SET last_run_date = l_run_date,
879 last_update_date = sysdate,
880 last_updated_by = l_user_id,
881 last_update_login = l_login_id,
882 program_id = l_program_id,
883 program_login_id = l_program_login_id,
884 program_application_id = l_program_application_id,
885 request_id = l_request_id
886 WHERE fact_name = C_CSD_RES_CONSUMED_F;
887
888 FND_FILE.PUT_LINE(FND_FILE.LOG,lc_proc_name || ': ' || 'Update complete.');
889
890 commit;
891 retcode := C_OK;
892
893 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the incremental refresh process for Resources Consumed fact ...');
894
895 EXCEPTION
896
897 WHEN OTHERS THEN
898
899 retcode := C_ERROR;
900 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
901 ROLLBACK;
902 RAISE;
903
904 END Incr_Load_Res_ETL;
905
906 /*--------------------------------------------------*/
907 /* procedure name: Initial_Load */
908 /* description : procedure to load Repair Orders */
909 /* Resource and Material facts */
910 /* initially. */
911 /*--------------------------------------------------*/
912 PROCEDURE Initial_Load(errbuf IN OUT NOCOPY VARCHAR2,
913 retcode IN OUT NOCOPY varchar2)
914
915 IS
916
917 -- Variables --
918 l_errbuf VARCHAR2(2000);
919 l_retcode VARCHAR2(1);
920
921 BEGIN
922 FND_FILE.PUT_LINE(FND_FILE.LOG,
923 'Entering the initial refresh process ...');
924
925 FND_FILE.PUT_LINE(FND_FILE.LOG,
926 'Calling the initial refresh process for Repair Orders fact ...');
927
928 -- Initializing to success.
929 retcode := c_OK;
930
931 CSD_HVR_BI_PVT.initial_load_Ro_etl(errbuf => l_errbuf,
932 retcode => l_retcode);
933
934 if (l_retcode = c_OK) then
935 FND_FILE.PUT_LINE(FND_FILE.LOG,
936 'Initial refresh process for Repair Orders fact completed succesfully...');
937 else
938 FND_FILE.PUT_LINE(FND_FILE.LOG,
939 'Initial refresh process for Repair Orders fact failed with following error: ' || l_errbuf);
940 if (retcode < l_retcode) then
941 retcode := l_retcode;
942 errbuf := l_errbuf;
943 end if;
944 end if;
945
946 FND_FILE.PUT_LINE(FND_FILE.LOG,
947 'Calling the initial refresh process for Material Consumption fact ...');
948
949 CSD_HVR_BI_PVT.initial_load_MTL_etl(errbuf => l_errbuf,
950 retcode => l_retcode);
951
952 if (l_retcode = c_OK) then
953 FND_FILE.PUT_LINE(FND_FILE.LOG,
954 'Initial refresh process for Material Consumption fact completed succesfully...');
955 else
956 if (retcode < l_retcode) then
957 retcode := l_retcode;
958 errbuf := l_errbuf;
959 end if;
960 FND_FILE.PUT_LINE(FND_FILE.LOG,
961 'Initial refresh process for Material Consumption fact failed with following error: ' || l_errbuf);
962 end if;
963
964 FND_FILE.PUT_LINE(FND_FILE.LOG,
965 'Calling the initial refresh process for Resource fact ...');
966
967 CSD_HVR_BI_PVT.initial_load_RES_etl(errbuf => l_errbuf,
968 retcode => l_retcode);
969
970 if (l_retcode = c_OK) then
971 FND_FILE.PUT_LINE(FND_FILE.LOG,
972 'Initial refresh process for resource fact completed succesfully...');
973 else
974 if (retcode < l_retcode) then
975 retcode := l_retcode;
976 errbuf := l_errbuf;
977 end if;
978 FND_FILE.PUT_LINE(FND_FILE.LOG,
979 'Initial refresh process for Resource fact failed with following error: ' || l_errbuf);
980 end if;
981
982 -- commit;
983
984 FND_FILE.PUT_LINE(FND_FILE.LOG,
985 'Leaving the initial refresh process ...');
986
987 EXCEPTION
988
989 WHEN OTHERS THEN
990 retcode := C_ERROR;
991 FND_FILE.PUT_LINE(FND_FILE.LOG,
992 'Unknown exception. SQLERRM = ' || SQLERRM);
993 ROLLBACK;
994 RAISE;
995
996 END Initial_Load;
997
998 /*--------------------------------------------------*/
999 /* procedure name: Incr_load */
1000 /* description : procedure to load */
1001 /* fact tables incrementally */
1002 /*--------------------------------------------------*/
1003 PROCEDURE Incr_load(errbuf in out NOCOPY VARCHAR2,
1004 retcode in out NOCOPY VARCHAR2)
1005
1006 IS
1007
1008 -- Variables --
1009 l_errbuf VARCHAR2(2000);
1010 l_retcode VARCHAR2(1);
1011
1012 BEGIN
1016 FND_FILE.PUT_LINE(FND_FILE.LOG,
1013 FND_FILE.PUT_LINE(FND_FILE.LOG,
1014 'Entering the incremental refresh process ...');
1015
1017 'Calling the incremental refresh process for Repair Orders fact ...');
1018
1019 CSD_HVR_BI_PVT.Incr_load_Ro_etl(errbuf => l_errbuf,
1020 retcode => l_retcode);
1021
1022 if (retcode = c_OK) then
1023 FND_FILE.PUT_LINE(FND_FILE.LOG,
1024 'Incremental refresh process for Repair Orders fact completed succesfully...');
1025 else
1026 if (retcode < l_retcode) then
1027 retcode := l_retcode;
1028 errbuf := l_errbuf;
1029 end if;
1030 FND_FILE.PUT_LINE(FND_FILE.LOG,
1031 'Incremental refresh process for Repair Orders fact failed with following error: ' || l_errbuf);
1032 end if;
1033
1034 FND_FILE.PUT_LINE(FND_FILE.LOG,
1035 'Calling the incremental refresh process for Material Consumption fact ...');
1036
1037 CSD_HVR_BI_PVT.Incr_load_MTL_etl(errbuf => l_errbuf,
1038 retcode => l_retcode);
1039
1040 if (retcode = c_OK) then
1041 FND_FILE.PUT_LINE(FND_FILE.LOG,
1042 'Incremental refresh process for Material Consumption fact completed succesfully...');
1043 else
1044 if (retcode < l_retcode) then
1045 retcode := l_retcode;
1046 errbuf := l_errbuf;
1047 end if;
1048 FND_FILE.PUT_LINE(FND_FILE.LOG,
1049 'Incremental refresh process for Material Consumption fact failed with following error: ' || l_errbuf);
1050 end if;
1051
1052 FND_FILE.PUT_LINE(FND_FILE.LOG,
1053 'Calling the incremental refresh process for Resource fact ...');
1054
1055 CSD_HVR_BI_PVT.Incr_load_RES_etl(errbuf => l_errbuf,
1056 retcode => l_retcode);
1057
1058 if (retcode = c_OK) then
1059 FND_FILE.PUT_LINE(FND_FILE.LOG,
1060 'Incremental refresh process for resource fact completed succesfully...');
1061 else
1062 if (retcode < l_retcode) then
1063 retcode := l_retcode;
1064 errbuf := l_errbuf;
1065 end if;
1066 FND_FILE.PUT_LINE(FND_FILE.LOG,
1067 'Incremental refresh process for Resource fact failed with following error: ' || l_errbuf);
1068 end if;
1069
1070 -- commit;
1071
1072 FND_FILE.PUT_LINE(FND_FILE.LOG,
1073 'Leaving the incremental refresh process ...');
1074
1075 EXCEPTION
1076
1077 WHEN OTHERS THEN
1078 retcode := C_ERROR;
1079 FND_FILE.PUT_LINE(FND_FILE.LOG,
1080 'Unknown exception. SQLERRM = ' || SQLERRM);
1081 ROLLBACK;
1082 RAISE;
1083
1084 END Incr_load;
1085
1086 /*--------------------------------------------------*/
1087 /* procedure name: Hvr_Bi_Driver_Main */
1088 /* description : procedure to load */
1089 /* fact tables incrementally */
1090 /*--------------------------------------------------*/
1091 PROCEDURE Hvr_Bi_Driver_Main(errbuf IN OUT NOCOPY VARCHAR2,
1092 retcode IN OUT NOCOPY VARCHAR2,
1093 p_refresh_type IN VARCHAR2) IS
1094
1095 l_refresh_method VARCHAR2(3);
1096
1097 BEGIN
1098
1099 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering the concurrent program...');
1100
1101 IF (p_refresh_type = 'INITIAL') then
1102 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling Initial_Load ...');
1103 initial_load(errbuf, retcode);
1104 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Returning from Initial_Load ...');
1105 l_refresh_method := 'C';
1106 else
1107 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling Incr_Load ...');
1108 incr_load(errbuf, retcode);
1109 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Returning from Incr_Load ...');
1110 l_refresh_method := '?';
1111 END IF;
1112
1113 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling Refresh_Mviews...');
1114 Refresh_Mviews(p_method => l_refresh_method);
1115 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Returning from Refresh_Mviews...');
1116
1117 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Leaving the concurrent program...');
1118
1119 EXCEPTION
1120
1121 WHEN OTHERS THEN
1122 retcode := C_ERROR;
1123 FND_FILE.PUT_LINE(FND_FILE.LOG,
1124 'Unknown exception. SQLERRM = ' || SQLERRM);
1125 ROLLBACK;
1126 RAISE;
1127
1128 END Hvr_Bi_Driver_Main;
1129
1130 END CSD_HVR_BI_PVT;