[Home] [Help]
PACKAGE BODY: APPS.CSD_RETURNS_BI_PVT
Source
1 PACKAGE BODY CSD_RETURNS_BI_PVT AS
2 /* $Header: csdvrbib.pls 120.9 2011/06/01 22:30:10 swai 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 Returns dashboard business data analysis process.'
25 FND_MESSAGE.SET_NAME('CSD','CSD_RETURNS_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,
37 p_data_set IN varchar2 := 'ALL') IS
38 BEGIN
39
40 -- refresh the mviews for the Receiving transactions
41 -- that are required for "Returns" and "Eco Impact"
42 -- tabs on the Environmental Impact Dashboard
43 if (p_data_set in ('ALL', 'RETURNS', 'ECO_IMPACT')) then
44 DBMS_MVIEW.refresh(list => 'CSD_RETURNS_BI_RCV_TXNS_MV',
45 method => p_method
46 -- method => '?'
47 -- rollback_seg := NULL,
48 -- push_deferred_rpc := TRUE,
49 -- refresh_after_errors := FALSE,
50 -- purge_option := 1,
51 -- parallelism := 0,
52 -- heap_size := 0,
53 -- atomic_refresh := TRUE
54 );
55 end if;
56
57 -- refresh the mviews for the Material transactions
58 -- that are required for "Eco-Impact" tab in the
59 -- Environmental Impact Dashboard
60 if (p_data_set in ('ALL','ECO_IMPACT')) then
61 DBMS_MVIEW.refresh(list => 'CSD_RETURNS_BI_MTL_TXNS_MV',
62 method => p_method
63 -- method => '?'
64 -- rollback_seg := NULL,
65 -- push_deferred_rpc := TRUE,
66 -- refresh_after_errors := FALSE,
67 -- purge_option := 1,
68 -- parallelism := 0,
69 -- heap_size := 0,
70 -- atomic_refresh := TRUE
71 );
72 end if;
73
74 -- swai: 12.2
75 -- refresh the mviews for the Material transactions
76 -- that are required for "Disposition Summary" tab in the
77 -- Environmental Impact Dashboard
78 if (p_data_set in ('ALL','DISP_SUM')) then
79 DBMS_MVIEW.refresh(list => 'CSD_RETURNS_BI_DISP_SUM_MV',
80 method => p_method
81 -- method => '?'
82 -- rollback_seg := NULL,
83 -- push_deferred_rpc := TRUE,
84 -- refresh_after_errors := FALSE,
85 -- purge_option := 1,
86 -- parallelism := 0,
87 -- heap_size := 0,
88 -- atomic_refresh := TRUE
89 );
90 end if;
91
92 -- swai: 12.2
93 -- refresh the mviews for the RCV/SHIP transactions
94 -- that are required for "Put On Market" tab in the
95 -- Environmental Impact Dashboard
96 if (p_data_set in ('ALL','POM')) then
97 DBMS_MVIEW.refresh(list => 'CSD_RETURNS_BI_POM_MV',
98 method => p_method
99 -- method => '?'
100 -- rollback_seg := NULL,
101 -- push_deferred_rpc := TRUE,
102 -- refresh_after_errors := FALSE,
103 -- purge_option := 1,
104 -- parallelism := 0,
105 -- heap_size := 0,
106 -- atomic_refresh := TRUE
107 );
108 end if;
109
110 END Refresh_Mviews;
111
112 /*--------------------------------------------------*/
113 /* procedure name: Initial_Load_RcvTxns_ETL */
114 /* description : procedure to load Repair Orders */
115 /* fact initially. */
116 /*--------------------------------------------------*/
117 PROCEDURE Initial_Load_RcvTxns_ETL(errbuf IN OUT NOCOPY VARCHAR2,
118 retcode IN OUT NOCOPY VARCHAR2)
119
120 IS
121
122 -- Variables --
123 l_run_date DATE;
124 l_user_id NUMBER;
125 l_login_id NUMBER;
126 l_program_id NUMBER;
127 l_program_login_id NUMBER;
128 l_program_application_id NUMBER;
129 l_request_id NUMBER;
130
131 -- Constants --
132 lc_proc_name CONSTANT VARCHAR2(30) := 'Initial_Load_RcvTxns_ETL';
133
134 BEGIN
135 l_user_id := NVL(fnd_global.USER_ID, -1);
136 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
137 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
138 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
139 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
140 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
141
142 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the initial refresh process for Receiving Transactions fact ...');
143
144 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Deleting record from CSD_FACT_DETAILS for CSD_RETURNS_BI_RCV_TXNS_F name ...');
145
146 DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_RETURNS_BI_RCV_TXNS_F;
147
148 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
149
150 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncating table CSD_RETURNS_BI_RCV_TXNS_F ...');
151
152 EXECUTE IMMEDIATE ('TRUNCATE TABLE CSD.CSD_RETURNS_BI_RCV_TXNS_F');
153
154 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncate successful.');
155
156 l_run_date := sysdate - 5 / (24 * 60);
157
158 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_RETURNS_BI_RCV_TXNS_F ...');
159
160 INSERT INTO CSD_RETURNS_BI_RCV_TXNS_F
161 (
162 transaction_id,
163 order_header_id,
164 order_line_id,
165 operating_unit_id,
166 inventory_org_id,
167 inventory_item_id,
168 received_quantity,
169 primary_uom_code,
170 transaction_date,
171 return_stream_code,
172 received_weight,
173 weight_uom_code,
174 received_value,
175 currency_code,
176 category_id,
177 category_set_id,
178 ship_geography_type,
179 ship_geography_id,
180 customer_type, -- swai: 12.2
181 pom_offset, -- swai: 12.2
182 created_by,
183 creation_date,
184 last_update_date,
185 last_updated_by,
186 last_update_login,
187 program_id,
188 program_login_id,
189 program_application_id,
190 request_id
191 )
192 SELECT distinct
193 rcv.transaction_id,
194 oh.header_id order_header_id,
195 oe.line_id order_line_id,
196 oe.org_id operating_unit_id,
197 rcv.organization_id inventory_org_id,
198 oe.inventory_item_id,
199 csd_returns_bi_util.convert_inv_uom( rcv.quantity, rcv.uom_code, mtl.primary_uom_code , 0 ) rcv_quantity, -- swai: bug 12614053
200 mtl.primary_uom_code,
201 trunc(rcv.transaction_date) transaction_date,
202 csd.return_stream_code,
203 (mtl.unit_weight*csd_returns_bi_util.convert_inv_uom( rcv.quantity, rcv.uom_code, mtl.primary_uom_code , null )) rcv_weight,
204 mtl.weight_uom_code,
205 (cic.item_cost* csd_returns_bi_util.convert_inv_uom( rcv.quantity, rcv.uom_code, mtl.primary_uom_code , null )) rcv_value,
206 gl.currency_code,
207 cat.category_id, -- swai: 12.2
208 cat.category_set_id, -- swai: 12.2
209 nvl(geo.geography_type,geo2.geography_type), -- swai: 12.2
210 nvl(geo.geography_id, geo2.geography_id), -- swai: 12.2
211 hp.party_type customer_type, -- swai: 12.2
212 csd.pom_offset, -- swai: 12.2
213 l_user_id,
214 sysdate,
215 sysdate,
216 l_user_id,
217 l_login_id,
218 l_program_id,
219 l_program_login_id,
220 l_program_application_id,
221 l_request_id
222 FROM
223 oe_order_lines_all oe,
224 oe_order_headers_all oh ,
225 rcv_transactions rcv,
226 mtl_system_items_b mtl,
227 cst_item_costs cic,
228 gl_sets_of_books gl,
229 hr_operating_units hr,
230 csd_return_stream_reasons csd,
231 -- swai: 12.2 begin
232 mtl_item_categories cat,
233 hz_geo_name_references geo,
234 hz_cust_site_uses_all csu,
235 hz_cust_acct_sites_all cas,
236 hz_party_sites hps,
237 hz_geo_name_references geo2,
238 hz_cust_site_uses_all csu2,
239 hz_cust_acct_sites_all cas2,
240 hz_party_sites hps2,
241 hz_cust_accounts hca,
242 hz_parties hp
243 -- swai: 12.2 end
244 WHERE oe.header_id = oh.header_id
245 and rcv.oe_order_line_id = oe.line_id
246 and rcv.oe_order_header_id = oe.header_id
247 and rcv.transaction_type = 'DELIVER'
248 and rcv.source_document_code = 'RMA'
249 and mtl.inventory_item_id = oe.inventory_item_id
250 and mtl.organization_id = rcv.organization_id
251 and cic.inventory_item_id(+) = mtl.inventory_item_id
252 and cic.organization_id(+) = mtl.organization_id
253 and cic.cost_type_id(+) = 1
254 and hr.set_of_books_id = gl.set_of_books_id
255 and hr.organization_id = oe.org_id
256 and oe.return_reason_code = csd.return_reason_code
257 and rcv.transaction_date <= l_run_date
258 -- swai: 12.2 begin
259 and mtl.inventory_item_id = cat.inventory_item_id(+)
260 and mtl.organization_id = cat.ORGANIZATION_ID(+)
261 and oe.ship_to_org_id = csu.site_use_id(+)
262 and csu.cust_acct_site_id = cas. cust_acct_site_id(+)
263 and cas. party_site_id = hps.party_site_id(+)
264 and hps.location_id = geo.location_id(+)
265 and geo.location_table_name(+) = 'HZ_LOCATIONS'
266 and oh.sold_to_org_id = hca.cust_account_id(+)
267 and hca.party_id = hp.party_id(+)
268 -- next 4 joins for getting the order header ship-to info
269 -- in case order line info is not avail
270 and oh.ship_to_org_id = csu2.site_use_id(+)
271 and csu2.cust_acct_site_id = cas2.cust_acct_site_id(+)
272 and cas2. party_site_id = hps2.party_site_id(+)
273 and hps2.location_id = geo2.location_id(+)
274 and geo2.location_table_name(+) = 'HZ_LOCATIONS'
275 -- swai: 12.2 end
276 ;
277
278 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
279
280 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
281
282 INSERT INTO CSD_FACT_DETAILS
283 (fact_name,
284 last_run_date,
285 created_by,
286 creation_date,
287 last_update_date,
288 last_updated_by,
289 last_update_login,
290 program_id,
291 program_login_id,
292 program_application_id,
293 request_id)
294 VALUES
295 (C_CSD_RETURNS_BI_RCV_TXNS_F,
296 l_run_date,
297 l_user_id,
298 sysdate,
299 sysdate,
300 l_user_id,
301 l_login_id,
302 l_program_id,
303 l_program_login_id,
304 l_program_application_id,
305 l_request_id);
306
307 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
308
309 commit;
310 retcode := C_OK;
311
312 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the initial refresh process for Receiving Transactions fact ...');
313
314 EXCEPTION
315
316 WHEN OTHERS THEN
317 retcode := C_ERROR;
318 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
319 ROLLBACK;
320 RAISE;
321
322 END Initial_Load_RcvTxns_ETL;
323
324
325 /*--------------------------------------------------*/
326 /* swai: 12.2 */
327 /* procedure name: Initial_Load_ShipTxns_ETL */
328 /* description : procedure to load Repair Orders */
329 /* fact initially. */
330 /*--------------------------------------------------*/
331 PROCEDURE Initial_Load_ShpTxns_ETL(errbuf IN OUT NOCOPY VARCHAR2,
332 retcode IN OUT NOCOPY VARCHAR2)
333
334 IS
335
336 -- Variables --
337 l_run_date DATE;
338 l_user_id NUMBER;
339 l_login_id NUMBER;
340 l_program_id NUMBER;
341 l_program_login_id NUMBER;
342 l_program_application_id NUMBER;
343 l_request_id NUMBER;
344
345 -- Constants --
346 lc_proc_name CONSTANT VARCHAR2(30) := 'Initial_Load_ShpTxns_ETL';
347
348 BEGIN
349 l_user_id := NVL(fnd_global.USER_ID, -1);
350 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
351 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
352 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
353 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
354 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
355
356 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the initial refresh process for Shipping Transactions fact ...');
357
358 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Deleting record from CSD_FACT_DETAILS for CSD_RETURNS_BI_SHP_TXNS_F name ...');
359
360 DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_RETURNS_BI_SHP_TXNS_F;
361
362 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
363
364 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncating table CSD_RETURNS_BI_SHP_TXNS_F ...');
365
366 EXECUTE IMMEDIATE ('TRUNCATE TABLE CSD.CSD_RETURNS_BI_SHP_TXNS_F');
367
368 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncate successful.');
369
370 l_run_date := sysdate - 5 / (24 * 60);
371
372 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_RETURNS_BI_SHP_TXNS_F ...');
373
374 INSERT INTO CSD_RETURNS_BI_SHP_TXNS_F
375 (
376 transaction_id,
377 order_header_id,
378 order_line_id,
379 operating_unit_id,
380 inventory_org_id,
381 inventory_item_id,
382 shipped_quantity,
383 primary_uom_code,
384 transaction_date,
385 shipped_weight,
386 weight_uom_code,
387 category_id,
388 category_set_id,
389 ship_geography_type,
390 ship_geography_id,
391 customer_type,
392 created_by,
393 creation_date,
394 last_update_date,
395 last_updated_by,
396 last_update_login,
397 program_id,
398 program_login_id,
399 program_application_id,
400 request_id
401 )
402 SELECT distinct
403 shp.delivery_detail_id,
404 oh.header_id order_header_id,
405 oe.line_id order_line_id,
406 oe.org_id operating_unit_id,
407 shp.organization_id inventory_org_id,
408 oe.inventory_item_id,
409 nvl(csd_returns_bi_util.convert_inv_uom( shp.shipped_quantity, shp.requested_quantity_uom, mtl.primary_uom_code , null ), 0) shp_quantity, -- swai: bug 12614053
410 mtl.primary_uom_code,
411 trunc(oe.actual_shipment_date) transaction_date,
412 (mtl.unit_weight*csd_returns_bi_util.convert_inv_uom( shp.shipped_quantity, shp.requested_quantity_uom, mtl.primary_uom_code , null )) shp_weight,
413 mtl.weight_uom_code,
414 cat.category_id,
415 cat.category_set_id,
416 geo.geography_type,
417 geo.geography_id,
418 hp.party_type customer_type,
419 l_user_id,
420 sysdate,
421 sysdate,
422 l_user_id,
423 l_login_id,
424 l_program_id,
425 l_program_login_id,
426 l_program_application_id,
427 l_request_id
428 FROM
429 oe_order_lines_all oe,
430 oe_order_headers_all oh ,
431 wsh_delivery_details shp,
432 mtl_system_items_kfv mtl,
433 mtl_item_categories cat,
434 hz_geo_name_references geo,
435 hz_cust_site_uses_all csu,
436 hz_cust_acct_sites_all cas,
437 hz_party_sites hps,
438 hz_cust_accounts hca,
439 hz_parties hp
440 WHERE oe.header_id = oh.header_id
441 and shp.source_header_id = oe.header_id
442 and shp.source_line_id = oe.line_id
443 and shp.source_code = 'OE'
444 and shp.released_status in ('C','I')
445 and mtl.inventory_item_id = oe.inventory_item_id
446 and mtl.organization_id = shp.organization_id
447 and oe.actual_shipment_date <= l_run_date
448 and mtl.inventory_item_id = cat.inventory_item_id(+)
449 and mtl.organization_id = cat.organization_id(+)
450 and oe.ship_to_org_id = csu.site_use_id(+)
451 and csu.cust_acct_site_id = cas. cust_acct_site_id(+)
452 and cas. party_site_id = hps.party_site_id(+)
453 and hps.location_id = geo.location_id(+)
454 and geo.location_table_name(+) = 'HZ_LOCATIONS'
455 and oh.sold_to_org_id = hca.cust_account_id(+)
456 and hca.party_id = hp.party_id(+);
457
458
459 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
460
461 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
462
463 INSERT INTO CSD_FACT_DETAILS
464 (fact_name,
465 last_run_date,
466 created_by,
467 creation_date,
468 last_update_date,
469 last_updated_by,
470 last_update_login,
471 program_id,
472 program_login_id,
473 program_application_id,
474 request_id)
475 VALUES
476 (C_CSD_RETURNS_BI_SHP_TXNS_F,
477 l_run_date,
478 l_user_id,
479 sysdate,
480 sysdate,
481 l_user_id,
482 l_login_id,
483 l_program_id,
484 l_program_login_id,
485 l_program_application_id,
486 l_request_id);
487
488 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
489
490 commit;
491 retcode := C_OK;
492
493 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the initial refresh process for Shipping Transactions fact ...');
494
495 EXCEPTION
496
497 WHEN OTHERS THEN
498 retcode := C_ERROR;
499 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
500 ROLLBACK;
501 RAISE;
502
503 END Initial_Load_SHPTxns_ETL;
504
505
506 /*--------------------------------------------------*/
507 /* procedure name: Initial_Load_MtlTxns_ETL */
508 /* description : procedure to load Material Txns */
509 /* fact initially. */
510 /*--------------------------------------------------*/
511 PROCEDURE Initial_Load_MtlTxns_ETL(errbuf IN OUT NOCOPY VARCHAR2,
512 retcode IN OUT NOCOPY VARCHAR2)
513
514 IS
515
516 -- Variables --
517 l_run_date DATE;
518 l_user_id NUMBER;
519 l_login_id NUMBER;
520 l_program_id NUMBER;
521 l_program_login_id NUMBER;
522 l_program_application_id NUMBER;
523 l_request_id NUMBER;
524
525 -- Constants --
526 lc_proc_name CONSTANT VARCHAR2(30) := 'Initial_Load_MtlTxns_ETL';
527
528 BEGIN
529 l_user_id := NVL(fnd_global.USER_ID, -1);
530 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
531 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID, -1);
532 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID, -1);
533 l_program_application_id := NVL(fnd_global.PROG_APPL_ID, -1);
534 l_request_id := NVL(fnd_global.CONC_REQUEST_ID, -1);
535
536 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Entering the initial refresh process for Material Transactions fact ...');
537
538 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Deleting record from CSD_FACT_DETAILS for CSD_RETURNS_BI_MTL_TXNS_F name ...');
539
540 DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_RETURNS_BI_MTL_TXNS_F;
541
542 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');
543
544 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncating table CSD_RETURNS_BI_MTL_TXNS_F ...');
545
546 EXECUTE IMMEDIATE ('TRUNCATE TABLE CSD.CSD_RETURNS_BI_MTL_TXNS_F');
547
548 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Truncate successful.');
549
550 l_run_date := sysdate - 5 / (24 * 60);
551
552 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_RETURNS_BI_MTL_TXNS_F ...');
553
554 INSERT INTO CSD_RETURNS_BI_MTL_TXNS_F
555 (transaction_id,
556 operating_unit_id,
557 inventory_org_id,
558 inventory_item_id,
559 transacted_qty,
560 primary_uom_code,
561 transaction_date,
562 reason_id,
563 mtl_disposition_code,
564 transacted_weight,
565 weight_uom_code,
566 category_id,
567 category_set_id,
568 inv_geography_type,
569 inv_geography_id,
570 recovery_value, -- swai: 12.2
571 processing_cost, -- swai: 12.2
572 currency_code, -- swai: 12.2
573 created_by,
574 creation_date,
575 last_update_date,
576 last_updated_by,
577 last_update_login,
578 program_id,
579 program_login_id,
580 program_application_id,
581 request_id
582 )
583 SELECT
584 txn.transaction_id,
585 orgv.operating_unit operating_unit_id,
586 txn.organization_id inventory_org_id,
587 txn.inventory_item_id,
588 csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , 0 ) transacted_quantity, -- swai: bug 12614053
589 mtl.primary_uom_code,
590 trunc(txn.transaction_date) transaction_date,
591 txn.reason_id,
592 dis.mtl_disposition_code,
593 (mtl.unit_weight*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) transacted_weight,
594 mtl.weight_uom_code,
595 cat.category_id, -- swai: 12.2
596 cat.category_set_id, -- swai: 12.2
597 geo.geography_type, -- swai: 12.2
598 geo.geography_id, -- swai: 12.2
599 (cic.item_cost*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) recovery_value, -- swai: 12.2
600 (mtl.order_cost*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) processing_cost, -- swai: 12.2
601 gl.currency_code, -- swai: 12.2
602 l_user_id,
603 sysdate,
604 sysdate,
605 l_user_id,
606 l_login_id,
607 l_program_id,
608 l_program_login_id,
609 l_program_application_id,
610 l_request_id
611 FROM
612 mtl_material_transactions txn,
613 mtl_system_items_b mtl,
614 csd_mtl_disposition_reasons dis,
615 org_organization_definitions orgv,
616 -- swai: 12.2 begin
617 mtl_item_categories cat,
618 hz_geo_name_references geo,
619 mtl_secondary_inventories subinv,
620 gl_sets_of_books gl,
621 hr_operating_units hr,
622 cst_item_costs cic
623 -- swai: 12.2 end
624 WHERE txn.inventory_item_id = mtl.inventory_item_id
625 and txn.organization_id = mtl.organization_id
626 and txn.transaction_type_id = 43
627 and txn.reason_id = dis.mtl_txn_reason_id
628 and txn.organization_id = orgv.organization_id
629 and txn.transaction_date <= l_run_date
630 -- swai: 12.2 begin
631 and mtl.inventory_item_id = cat.inventory_item_id(+)
632 and mtl.organization_id = cat.organization_id(+)
633 and txn.subinventory_code = subinv.secondary_inventory_name(+)
634 and txn.organization_id = subinv.organization_id(+)
635 and geo.location_id(+) = subinv.location_id
636 and geo.location_table_name(+) = 'HR_LOCATIONS_ALL'
637 and hr.set_of_books_id = gl.set_of_books_id
638 and hr.set_of_books_id = gl.set_of_books_id
639 and hr.organization_id = orgv.operating_unit
640 and cic.inventory_item_id(+) = mtl.inventory_item_id
641 and cic.organization_id(+) = mtl.organization_id
642 and cic.cost_type_id(+) = 1
643 -- swai: 12.2 end
644 UNION ALL
645 SELECT
646 txn.transaction_id,
647 orgv.operating_unit operating_unit_id,
648 txn.organization_id inventory_org_id,
649 txn.inventory_item_id,
650 csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , 0 ) txn_quantity, -- swai: bug 12614053
651 mtl.primary_uom_code,
652 trunc(txn.transaction_date) transaction_date,
653 txn.reason_id,
654 dis.mtl_disposition_code,
655 (mtl.unit_weight*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) transacted_weight,
656 mtl.weight_uom_code,
657 cat.category_id, -- swai: 12.2
658 cat.category_set_id, -- swai: 12.2
659 geo.geography_type, -- swai: 12.2
660 geo.geography_id, -- swai: 12.2
661 (cic.item_cost*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) recovery_value, -- swai: 12.2
662 (mtl.order_cost*csd_returns_bi_util.convert_inv_uom( txn.transaction_quantity, txn.transaction_uom, mtl.primary_uom_code , null )) processing_cost, -- swai: 12.2
663 gl.currency_code, -- swai: 12.2
664 l_user_id,
665 sysdate,
666 sysdate,
667 l_user_id,
668 l_login_id,
669 l_program_id,
670 l_program_login_id,
671 l_program_application_id,
672 l_request_id
673 FROM
674 mtl_material_transactions txn,
675 mtl_system_items_b mtl,
676 csd_mtl_disposition_reasons dis,
677 org_organization_definitions orgv,
678 -- swai: 12.2 begin
679 mtl_item_categories cat,
680 hz_geo_name_references geo,
681 mtl_secondary_inventories subinv,
682 gl_sets_of_books gl,
683 hr_operating_units hr,
684 cst_item_costs cic
685 -- swai: 12.2 end
686 WHERE txn.inventory_item_id = mtl.inventory_item_id
687 and txn.organization_id = mtl.organization_id
688 and txn.transaction_type_id = 38
689 and txn.reason_id = dis.mtl_txn_reason_id
690 and txn.organization_id = orgv.organization_id
691 and txn.transaction_date <= l_run_date
692 -- swai: 12.2 begin
693 and mtl.inventory_item_id = cat.inventory_item_id(+)
694 and mtl.organization_id = cat.organization_id(+)
695 and txn.subinventory_code = subinv.secondary_inventory_name(+)
696 and txn.organization_id = subinv.organization_id(+)
697 and geo.location_id(+) = subinv.location_id
698 and geo.location_table_name(+) = 'HR_LOCATIONS_ALL'
699 and hr.set_of_books_id = gl.set_of_books_id
700 and hr.set_of_books_id = gl.set_of_books_id
701 and hr.organization_id = orgv.operating_unit
702 and cic.inventory_item_id(+) = mtl.inventory_item_id
703 and cic.organization_id(+) = mtl.organization_id
704 and cic.cost_type_id(+) = 1;
705 -- swai: 12.2 end
706
707 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
708
709 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');
710
711 INSERT INTO CSD_FACT_DETAILS
712 (fact_name,
713 last_run_date,
714 created_by,
715 creation_date,
716 last_update_date,
717 last_updated_by,
718 last_update_login,
719 program_id,
720 program_login_id,
721 program_application_id,
722 request_id)
723 VALUES
724 (C_CSD_RETURNS_BI_MTL_TXNS_F,
725 l_run_date,
726 l_user_id,
727 sysdate,
728 sysdate,
729 l_user_id,
730 l_login_id,
731 l_program_id,
732 l_program_login_id,
733 l_program_application_id,
734 l_request_id);
735
736 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');
737
738 commit;
739 retcode := C_OK;
740
741 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Leaving the initial refresh process for Material Transactions fact ...');
742
743 EXCEPTION
744
745 WHEN OTHERS THEN
746 retcode := C_ERROR;
747 FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Unknown exception. SQLERRM = ' || SQLERRM);
748 ROLLBACK;
749 RAISE;
750
751 END Initial_Load_MtlTxns_ETL;
752
753
754 /*--------------------------------------------------*/
755 /* procedure name: Initial_Load */
756 /* description : procedure to load all Returns */
757 /* Dashboard facts */
758 /*--------------------------------------------------*/
759 PROCEDURE Initial_Load(errbuf IN OUT NOCOPY VARCHAR2,
760 retcode IN OUT NOCOPY varchar2,
761 p_data_set IN VARCHAR2 := 'ALL')
762
763 IS
764
765 -- Variables --
766 l_errbuf VARCHAR2(2000);
767 l_retcode VARCHAR2(1);
768
769 BEGIN
770 FND_FILE.PUT_LINE(FND_FILE.LOG,
771 'Entering the initial refresh process ...');
772
773
774 ---------------------------- RECEIVING TRANSACTIONS ----------------------------
775 if (p_data_set in ('ALL', 'RETURNS', 'ECO_IMPACT', 'POM')) then
776 FND_FILE.PUT_LINE(FND_FILE.LOG,
777 'Calling the initial refresh process for Receiving Transactions fact ...');
778
779 -- Initializing to success.
780 retcode := c_OK;
781
782 CSD_RETURNS_BI_PVT.initial_load_RcvTxns_etl(errbuf => l_errbuf,
783 retcode => l_retcode);
784
785 if (l_retcode = c_OK) then
786 FND_FILE.PUT_LINE(FND_FILE.LOG,
787 'Initial refresh process for Receiving Transactions fact completed succesfully...');
788 else
789 FND_FILE.PUT_LINE(FND_FILE.LOG,
790 'Initial refresh process for Receiving Transactions fact failed with following error: ' || l_errbuf);
791 if (retcode < l_retcode) then
792 retcode := l_retcode;
793 errbuf := l_errbuf;
794 end if;
795 end if;
796 end if;
797 ---------------------------- SHIPPING TRANSACTIONS ----------------------------
798 if (p_data_set in ('ALL', 'POM')) then
799 FND_FILE.PUT_LINE(FND_FILE.LOG,
800 'Calling the initial refresh process for Shipping Transactions fact ...');
801
802 -- Initializing to success.
803 retcode := c_OK;
804
805 CSD_RETURNS_BI_PVT.initial_load_ShpTxns_etl(errbuf => l_errbuf,
806 retcode => l_retcode);
807
808 if (l_retcode = c_OK) then
809 FND_FILE.PUT_LINE(FND_FILE.LOG,
810 'Initial refresh process for Shipping Transactions fact completed succesfully...');
811 else
812 FND_FILE.PUT_LINE(FND_FILE.LOG,
813 'Initial refresh process for Shipping Transactions fact failed with following error: ' || l_errbuf);
814 if (retcode < l_retcode) then
815 retcode := l_retcode;
816 errbuf := l_errbuf;
817 end if;
818 end if;
819 end if;
820 ---------------------------- MATERIAL TRANSACTIONS ----------------------------
821 if (p_data_set in ('ALL', 'ECO_IMPACT', 'DISP_SUM')) then
822 FND_FILE.PUT_LINE(FND_FILE.LOG,
823 'Calling the initial refresh process for Material Transactions fact ...');
824
825 CSD_RETURNS_BI_PVT.initial_load_MtlTxns_etl(errbuf => l_errbuf,
826 retcode => l_retcode);
827
828 if (l_retcode = c_OK) then
829 FND_FILE.PUT_LINE(FND_FILE.LOG,
830 'Initial refresh process for Material Transactions fact completed succesfully...');
831 else
832 if (retcode < l_retcode) then
833 retcode := l_retcode;
834 errbuf := l_errbuf;
835 end if;
836 FND_FILE.PUT_LINE(FND_FILE.LOG,
837 'Initial refresh process for Material Transactions fact failed with following error: ' || l_errbuf);
838 end if;
839 end if;
840 -- commit;
841
842 FND_FILE.PUT_LINE(FND_FILE.LOG,
843 'Leaving the initial refresh process ...');
844
845 EXCEPTION
846
847 WHEN OTHERS THEN
848 retcode := C_ERROR;
849 FND_FILE.PUT_LINE(FND_FILE.LOG,
850 'Unknown exception. SQLERRM = ' || SQLERRM);
851 ROLLBACK;
852 RAISE;
853
854 END Initial_Load;
855
856 /*--------------------------------------------------*/
857 /* procedure name: Returns_Bi_Driver_Main */
858 /* description : procedure to load */
859 /* fact tables */
860 /*--------------------------------------------------*/
861 PROCEDURE Returns_Bi_Driver_Main(errbuf IN OUT NOCOPY VARCHAR2,
862 retcode IN OUT NOCOPY VARCHAR2,
863 p_data_set IN VARCHAR2 := 'ALL'
864 ) IS
865
866 l_refresh_method VARCHAR2(3);
867
868 BEGIN
869
870 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering the concurrent program...');
871
872 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling Initial_Load ...');
873 initial_load(errbuf, retcode, p_data_set);
874 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Returning from Initial_Load ...');
875 l_refresh_method := 'C';
876
877 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling Refresh_Mviews...');
878 Refresh_Mviews(p_method => l_refresh_method,
879 p_data_set => p_data_set);
880 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Returning from Refresh_Mviews...');
881
882
883 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Leaving the concurrent program...');
884
885 EXCEPTION
886
887 WHEN OTHERS THEN
888 retcode := C_ERROR;
889 FND_FILE.PUT_LINE(FND_FILE.LOG,
890 'Unknown exception. SQLERRM = ' || SQLERRM);
891 ROLLBACK;
892 RAISE;
893
894 END Returns_Bi_Driver_Main;
895
896 END CSD_RETURNS_BI_PVT;