[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_INV_USG_ETL_PKG
Source
1 PACKAGE BODY ISC_FS_INV_USG_ETL_PKG AS
2 /*$Header: iscfsinvetlb.pls 120.2 2006/05/04 17:07:10 kreardon noship $ */
3
4 g_pkg_name constant varchar2(30) := 'ISC_FS_INV_USG_ETL_PKG';
5 g_sysdate DATE := SYSDATE;
6 g_user_id NUMBER := nvl(fnd_global.user_id, -1);
7 g_login_id NUMBER := nvl(fnd_global.login_id, -1);
8 g_last_collection_date DATE;
9 g_ok NUMBER(1) := 0;
10 g_warning NUMBER(1) := 1;
11 g_error NUMBER(1) := -1;
12 g_program_id NUMBER := fnd_global.CONC_PROGRAM_ID;
13 g_program_login_id NUMBER := fnd_global.CONC_LOGIN_ID;
14 g_program_application_id NUMBER := fnd_global.PROG_APPL_ID;
15 g_request_id NUMBER := fnd_global.CONC_REQUEST_ID;
16 g_bis_setup_exception exception;
17 g_object_name constant varchar2(30) := 'ISC_FS_INV_USG_FACT';
18
19
20 PROCEDURE LOGGER
21 ( p_proc_name varchar2
22 , p_stmt_id number
23 , p_message varchar2
24 )
25 AS
26 BEGIN
27 bis_collection_utilities.log( g_pkg_name || '.' || p_proc_name ||
28 ' #' || p_stmt_id || ' ' ||
29 p_message
30 , 3 );
31 END LOGGER;
32
33
34 FUNCTION GET_SCHEMA_NAME
35 ( x_schema_name out nocopy varchar2
36 , x_error_message out nocopy varchar2 )
37 RETURN number
38 AS
39 l_isc_schema varchar2(30);
40 l_status varchar2(30);
41 l_industry varchar2(30);
42
43 BEGIN
44
45 if fnd_installation.get_app_info('ISC', l_status, l_industry, l_isc_schema) then
46 x_schema_name := l_isc_schema;
47 else
48 x_error_message := 'FND_INSTALLATION.GET_APP_INFO returned false';
49 return -1;
50 end if;
51
52 RETURN 0;
53
54 EXCEPTION
55 WHEN others THEN
56 x_error_message := 'Error in function get_schema_name : ' || sqlerrm;
57 RETURN -1;
58
59 END GET_SCHEMA_NAME;
60
61
62 FUNCTION TRUNCATE_TABLE
63 ( p_isc_schema in varchar2
64 , p_table_name in varchar2
65 , x_error_message out nocopy varchar2 )
66 RETURN number
67 AS
68 BEGIN
69
70 execute immediate 'truncate table ' || p_isc_schema || '.' || p_table_name;
71
72 RETURN 0;
73
74 EXCEPTION
75 WHEN others THEN
76 x_error_message := 'Error in function truncate_table : ' || sqlerrm;
77 RETURN -1;
78
79 END TRUNCATE_TABLE;
80
81
82 FUNCTION GATHER_STATISTICS
83 ( p_isc_schema in varchar2
84 , p_table_name in varchar2
85 , x_error_message out nocopy varchar2 )
86 RETURN number
87 AS
88 BEGIN
89
90 fnd_stats.gather_table_stats( ownname => p_isc_schema
91 , tabname => p_table_name
92 );
93
94 RETURN 0;
95
96 EXCEPTION
97 WHEN others THEN
98 x_error_message := 'Error in function gather_statistics : ' || sqlerrm;
99 RETURN -1;
100
101 END GATHER_STATISTICS;
102
103
104 FUNCTION COMPUTE_INV_CONV_RATES
105 ( p_isc_schema in varchar2
106 , x_error_message out nocopy varchar2 )
107 RETURN NUMBER AS
108
109 l_proc_name constant varchar2(30):= 'COMPUTE_INV_CONV_RATES';
110 l_stmt_num number;
111
112 l_global_currency_code1 varchar2(15);
113 l_global_rate_type1 varchar2(15);
114 l_global_currency_code2 varchar2(15);
115 l_global_rate_type2 varchar2(15);
116
117 /* EURO currency became official on 01-JAN-1999 */
118 l_euro_start_date constant date := to_date ('01/01/1999', 'mm/dd/yyyy');
119
120 /* GL API returns -3 if EURO rate missing on 01-JAN-1999 */
121 l_euro_missing_at_start constant number := -3;
122
123 l_all_rates_found boolean;
124
125 -- Set up a cursor to get all the invalid rates.
126 -- By the logic of the fii_currency.get_global_rate_primary
127 -- API, the returned value is -ve if no rate exists:
128 -- -1 for dates with no rate.
129 -- -2 for unrecognized conversion rates.
130 -- Also, cross check with the org-date pairs in the staging table,
131 -- in case some orgs never had a functional currency code defined.
132 CURSOR c_invalid_rates IS
133 SELECT DISTINCT
134 mp.organization_code
135 , decode( least( r.conversion_rate1, r.conversion_rate2 )
136 , l_euro_missing_at_start, l_euro_start_date
137 , r.transaction_date) transaction_date
138 , r.base_currency_code
139 , nvl(r.conversion_rate1, -999) primary_rate
140 , nvl(r.conversion_rate2, -999) secondary_rate
141 FROM
142 isc_fs_inv_usg_conv_rates r
143 , mtl_parameters mp
144 , ( SELECT /*+ index_ffs(isc_fs_inv_usg_value_stg) */ DISTINCT
145 organization_id
146 , transaction_date
147 FROM isc_fs_inv_usg_value_stg
148 ) s
149 WHERE ( nvl(r.conversion_rate1, -999) < 0 OR
150 nvl(r.conversion_rate2, -999) < 0 )
151 AND mp.organization_id = s.organization_id
152 AND r.transaction_date (+) = s.transaction_date
153 AND r.organization_id (+) = s.organization_id;
154
155 l_exception EXCEPTION;
156 l_err_msg varchar2(4000);
157 l_row_count number;
158
159 BEGIN
160
161 BIS_COLLECTION_UTILITIES.LOG( 'Begin Currency Conversion', 1 );
162
163 -- get the primary global currency code
164 l_stmt_num := 10;
165 l_global_currency_code1 := BIS_COMMON_PARAMETERS.GET_CURRENCY_CODE;
166 IF l_global_currency_code1 IS NULL THEN
167 l_err_msg := 'Unable to get primary global currency code.';
168 logger( l_proc_name, l_stmt_num, l_err_msg );
169 RAISE l_exception;
170 END IF;
171
172 BIS_COLLECTION_UTILITIES.LOG( 'Primary global currency code: ' || l_global_currency_code1, 2
173 );
174
175 -- get the primary global rate type
176 l_stmt_num := 20;
177 l_global_rate_type1 := bis_common_parameters.get_rate_type;
178 IF l_global_rate_type1 IS NULL THEN
179 l_err_msg := 'Unable to get primary global rate type.';
180 logger( l_proc_name, l_stmt_num, l_err_msg );
181 RAISE l_exception;
182 END IF;
183
184 BIS_COLLECTION_UTILITIES.LOG( 'Primary global rate type: ' || l_global_rate_type1, 2 );
185
186 -- get the secondary global currency code
187 l_stmt_num := 30;
188 l_global_currency_code2 := bis_common_parameters.get_secondary_currency_code;
189
190 IF l_global_currency_code2 IS NOT NULL THEN
191 BIS_COLLECTION_UTILITIES.LOG( 'Secondary global currency code: ' ||
192 l_global_currency_code2, 2 );
193 ELSE
194 BIS_COLLECTION_UTILITIES.LOG( 'Secondary global currency code is not defined', 2 );
195 END IF;
196
197 -- get the secondary global rate type
198 l_stmt_num := 40;
199 l_global_rate_type2 := bis_common_parameters.get_secondary_rate_type;
200 IF l_global_rate_type2 IS NULL AND l_global_currency_code2 IS NOT NULL THEN
201 l_err_msg := 'Unable to get secondary global rate type.';
202 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
203 RAISE l_exception;
204 END IF;
205
206 IF l_global_currency_code2 IS NOT NULL THEN
207 BIS_COLLECTION_UTILITIES.LOG( 'Secondary global rate type: ' || l_global_rate_type2, 2 );
208 END IF;
209
210 -- truncate the conversion rates work table
211 l_stmt_num := 50;
212 IF truncate_table
213 ( p_isc_schema
214 , 'ISC_FS_INV_USG_CONV_RATES'
215 , l_err_msg ) <> 0 THEN
216 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
217 RAISE l_exception;
218 END IF;
219
220 BIS_COLLECTION_UTILITIES.LOG( 'Currency conversion table truncated', 2 );
221
222 -- Get all the distinct organization and date pairs and the
223 -- base currency codes for the orgs into the conversion rates
224 -- work table.
225
226 -- Use the fii_currency.get_global_rate_primary function to get the
227 -- conversion rate given a currency code and a date.
228 -- only attempt to get conversion rate for rows that are complete
229 -- (have complete_flag = 'Y')
230 --
231 -- The function returns:
232 -- 1 for currency code when is the global currency
233 -- -1 for dates for which there is no currency conversion rate
234 -- -2 for unrecognized currency conversion rates
235
236 -- By selecting distinct org and currency code from the gl_set_of_books
237 -- and hr_organization_information, take care of duplicate codes.
238
239 l_stmt_num := 60;
240 INSERT /*+ append */
241 INTO ISC_FS_INV_USG_CONV_RATES
242 ( organization_id
243 , transaction_date
244 , base_currency_code
245 , conversion_rate1
246 , conversion_rate2
247 , creation_date
248 , last_update_date
249 , created_by
250 , last_updated_by
251 , last_update_login
252 , program_id
253 , program_login_id
254 , program_application_id
255 , request_id
256 )
257 SELECT
258 s.organization_id
259 , s.transaction_date
260 , c.currency_code
261 , fii_currency.get_global_rate_primary
262 ( c.currency_code
263 , s.transaction_date ) conversion_rate1
264 , decode( l_global_currency_code2
265 , null, 0 -- only attempt conversion if secondary currency defined
266 , fii_currency.get_global_rate_secondary
267 ( c.currency_code
268 , s.transaction_date )
269 ) conversion_rate2
270 , g_sysdate
271 , g_sysdate
272 , g_user_id
273 , g_user_id
274 , g_login_id
275 , g_program_id
276 , g_program_login_id
277 , g_program_application_id
278 , g_request_id
279 FROM
280 ( SELECT /*+ index_ffs(isc_fs_inv_usg_value_stg)
281 parallel_index(isc_fs_inv_usg_value_stg) */ DISTINCT
282 organization_id
283 , transaction_date
284 FROM
285 ISC_FS_INV_USG_VALUE_STG
286 ) s
287 , ( SELECT DISTINCT
288 hoi.organization_id
289 , gsob.currency_code
290 FROM
291 hr_organization_information hoi
292 , gl_sets_of_books gsob
293 WHERE hoi.org_information_context = 'Accounting Information'
294 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
295 ) c
296 WHERE c.organization_id = s.organization_id;
297
298 l_row_count := sql%rowcount;
299 COMMIT;
300
301 BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into currency conversion
302 table', 2 );
303
304 l_all_rates_found := true;
305
306 -- gather statistics on conversion rates table before returning
307 l_stmt_num := 70;
308 IF GATHER_STATISTICS
309 ( p_isc_schema
310 , 'ISC_FS_INV_USG_CONV_RATES'
311 , l_err_msg ) <> 0 then
312 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
313 RAISE l_exception;
314 END IF;
315
316 BIS_COLLECTION_UTILITIES.LOG( 'Currency conversion table analyzed', 2 );
317
318 -- Check that all rates have been found and are non-negative.
319 -- If there is a problem, notify user.
320 l_stmt_num := 80;
321 FOR invalid_rate_rec IN c_invalid_rates LOOP
322
323 -- print the header out
324 IF c_invalid_rates%rowcount = 1 THEN
325 bis_collection_utilities.writeMissingRateHeader;
326 END IF;
327
328 l_all_rates_found := false;
329
330 IF invalid_rate_rec.primary_rate < 0 THEN
331 bis_collection_utilities.writeMissingRate
332 ( l_global_rate_type1
333 , invalid_rate_rec.base_currency_code
334 , l_global_currency_code1
335 , invalid_rate_rec.transaction_date );
336 END IF;
337
338 IF invalid_rate_rec.secondary_rate < 0 THEN
339 bis_collection_utilities.writeMissingRate
340 ( l_global_rate_type2
341 , invalid_rate_rec.base_currency_code
342 , l_global_currency_code2
343 , invalid_rate_rec.transaction_date );
344 END IF;
345
346 END LOOP;
347
348 -- If all rates not found raise an exception
349 IF NOT l_all_rates_found THEN
350 l_err_msg := 'Missing currency rates exist.';
351 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
352 RAISE l_exception;
353 END IF;
354
355 BIS_COLLECTION_UTILITIES.LOG( 'End Currency Conversion', 1 );
356
357 RETURN g_ok;
358
359 EXCEPTION
360
361 WHEN l_exception THEN
362 x_error_message := l_err_msg;
363 RETURN g_error;
364
365 WHEN others THEN
366 ROLLBACK;
367 l_err_msg := substr( sqlerrm, 1, 4000 );
368 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
369 x_error_message := 'Load conversion rate computation failed';
370 RETURN g_error;
371
372 END COMPUTE_INV_CONV_RATES;
373
374
375 -- -------------------------------------------------------------------
376 -- PUBLIC PROCEDURES
377 -- -------------------------------------------------------------------
378
379 PROCEDURE GET_INV_USG_INITIAL_LOAD(errbuf in out NOCOPY varchar2, retcode in out NOCOPY
380 varchar2)
381 IS
382 l_proc_name constant varchar2(30) := 'GET_INV_USG_INITIAL_LOAD';
383 l_stmt_num NUMBER;
384 l_row_count NUMBER;
385 l_err_num NUMBER;
386 l_err_msg VARCHAR2(255);
387 l_exception exception;
388 l_isc_schema VARCHAR2(30);
389 l_status VARCHAR2(30);
390 l_industry VARCHAR2(30);
391 l_list dbms_sql.varchar2_table;
392 BEGIN
393
394 l_list(1) := 'BIS_GLOBAL_START_DATE';
395
396 BIS_COLLECTION_UTILITIES.LOG( 'Begin Initial Load' );
397
398 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
399
400 l_stmt_num := 0;
401 IF NOT BIS_COLLECTION_UTILITIES.SETUP( g_object_name ) THEN
402 l_err_msg := 'Error in BIS_COLLECTION_UTILITIES.Setup';
403 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
404 RAISE g_bis_setup_exception;
405 END IF;
406
407 -- get the isc schema name
408 l_stmt_num := 5;
409 IF get_schema_name
410 ( l_isc_schema
411 , l_err_msg ) <> 0 THEN
412 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
413 RAISE l_exception;
414 END IF;
415
416 -- truncate the Log table
417 l_stmt_num := 10;
418 IF truncate_table
419 ( l_isc_schema
420 , 'ISC_FS_INV_USG_LOG'
421 , l_err_msg ) <> 0 THEN
422 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
423 RAISE l_exception;
424 END IF;
425
426 BIS_COLLECTION_UTILITIES.LOG( 'Log table truncated', 1 );
427
428 /* Insert into our log table the upper transaction boundaries for every organization to
429 be extracted. We will access OPI_DBI_CONC_PROG_RUN_LOG to obtain these upper boundaries. The
430 incremental load will use these boundaries as starting points. */
431
432 l_stmt_num := 20;
433 INSERT INTO ISC_FS_INV_USG_LOG
434 (
435 ORGANIZATION_ID
436 ,FROM_TRANSACTION_ID
437 ,TO_TRANSACTION_ID
438 ,CREATED_BY
439 ,CREATION_DATE
440 ,LAST_UPDATE_DATE
441 ,LAST_UPDATED_BY
442 ,LAST_UPDATE_LOGIN
443 ,PROGRAM_ID
444 ,PROGRAM_LOGIN_ID
445 ,PROGRAM_APPLICATION_ID
446 ,REQUEST_ID
447 )
448 SELECT
449 from_to_txn.BOUND_LEVEL_ENTITY_ID organization_id
450 ,min(from_to_txn.FROM_BOUND_ID) FROM_TRANSACTION_ID
451 ,case
452 when nvl(max(from_to_txn_incr.TO_BOUND_ID),-99) > max(from_to_txn.TO_BOUND_ID) then max(from_to_txn_incr.TO_BOUND_ID)
453 else max(from_to_txn.TO_BOUND_ID)
454 end TO_TRANSACTION_ID /* The to_transaction_id must come from the INCR row if there is one */
455 /* the nvl is in case the inventory page incremental has not been run yet */
456 ,g_user_id
457 ,g_sysdate
458 ,g_sysdate
459 ,g_user_id
460 ,g_login_id
461 ,g_program_id
462 ,g_program_login_id
463 ,g_program_application_id
464 ,g_request_id
465 FROM
466 OPI_DBI_CONC_PROG_RUN_LOG from_to_txn
467 ,OPI_DBI_CONC_PROG_RUN_LOG from_to_txn_incr
468 /* Change in Boundaries Log table in OPI caused a change in how our code handles MMT boundaries */
469 WHERE
470 from_to_txn.etl_type = 'INVENTORY'
471 AND from_to_txn.BOUND_TYPE = 'ID'
472 AND from_to_txn.DRIVING_TABLE_CODE = 'MMT'
473 AND from_to_txn.LOAD_TYPE = 'INIT'
474 AND from_to_txn.BOUND_LEVEL_ENTITY_CODE = 'ORGANIZATION'
475 AND from_to_txn_incr.etl_type (+) = 'INVENTORY'
476 AND from_to_txn_incr.BOUND_TYPE (+) = 'ID'
477 AND from_to_txn_incr.DRIVING_TABLE_CODE (+) = 'MMT'
478 AND from_to_txn_incr.LOAD_TYPE (+) = 'INCR'
479 AND from_to_txn_incr.BOUND_LEVEL_ENTITY_CODE (+) = 'ORGANIZATION'
480 AND from_to_txn.BOUND_LEVEL_ENTITY_ID = from_to_txn_incr.BOUND_LEVEL_ENTITY_ID (+)
481 GROUP BY
482 from_to_txn.BOUND_LEVEL_ENTITY_ID
483 UNION /* This union is for Organizations that were created after initial load of inventory page */
484 SELECT
485 from_to_txn.BOUND_LEVEL_ENTITY_ID organization_id
486 ,-1 FROM_TRANSACTION_ID
487 /* Organizations that were created after initial load of inventory page,
488 hence after GSD, do not have INIT rows */
489 ,from_to_txn.TO_BOUND_ID TO_TRANSACTION_ID
490 ,g_user_id
491 ,g_sysdate
492 ,g_sysdate
493 ,g_user_id
494 ,g_login_id
495 ,g_program_id
496 ,g_program_login_id
497 ,g_program_application_id
498 ,g_request_id
499 FROM
500 OPI_DBI_CONC_PROG_RUN_LOG from_to_txn
501 /* Change in Boundaries Log table in OPI caused a change in how our code handles MMT boundaries */
502 WHERE
503 from_to_txn.etl_type = 'INVENTORY'
504 AND from_to_txn.BOUND_TYPE = 'ID'
505 AND from_to_txn.DRIVING_TABLE_CODE = 'MMT'
506 AND from_to_txn.LOAD_TYPE = 'INCR'
507 AND from_to_txn.BOUND_LEVEL_ENTITY_CODE = 'ORGANIZATION'
508 AND not exists (SELECT 'X' FROM OPI_DBI_CONC_PROG_RUN_LOG olog
509 WHERE
510 olog.etl_type = 'INVENTORY'
511 AND olog.BOUND_TYPE = 'ID'
512 AND olog.DRIVING_TABLE_CODE = 'MMT'
513 AND olog.LOAD_TYPE = 'INIT'
514 AND from_to_txn.BOUND_LEVEL_ENTITY_ID = olog.BOUND_LEVEL_ENTITY_ID);
515
516 -- truncate the staging table
517 l_stmt_num := 30;
518 IF TRUNCATE_TABLE
519 ( l_isc_schema
520 , 'ISC_FS_INV_USG_VALUE_STG'
521 , l_err_msg ) <> 0 THEN
522 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
523 RAISE l_exception;
524 END IF;
525
526 BIS_COLLECTION_UTILITIES.LOG( 'Staging table truncated', 1 );
527
528 l_stmt_num := 40;
529 /* Insert field service material issue transaction values into staging table */
530 INSERT /*+ append parallel(ISC_FS_INV_USG_VALUE_STG) */ INTO ISC_FS_INV_USG_VALUE_STG
531 (
532 ORGANIZATION_ID
533 ,SUBINVENTORY_CODE
534 ,TRANSACTION_DATE
535 ,INVENTORY_ITEM_ID
536 ,ONHAND_VALUE_B
537 ,CREATED_BY
538 ,CREATION_DATE
539 ,LAST_UPDATE_DATE
540 ,LAST_UPDATED_BY
541 ,LAST_UPDATE_LOGIN
542 ,PROGRAM_ID
543 ,PROGRAM_LOGIN_ID
544 ,PROGRAM_APPLICATION_ID
545 ,REQUEST_ID
546 )
547 SELECT /*+ ordered use_hash(mmt,mta,bound) swap_join_inputs(bound)
548 parallel(mmt) parallel(mta) pq_distribute(mta,hash,hash) pq_distribute(bound,none,broadcast) */
549 mta.ORGANIZATION_ID
550 ,mmt.SUBINVENTORY_CODE
551 ,trunc(mta.TRANSACTION_DATE) TRANSACTION_DATE
552 ,mta.INVENTORY_ITEM_ID
553 ,SUM(mta.BASE_TRANSACTION_VALUE)*(-1) ONHAND_VALUE_B /* Issue transactions are
554 expressed in negative numbers */
555 ,g_user_id
556 ,g_sysdate
557 ,g_sysdate
558 ,g_user_id
559 ,g_login_id
560 ,g_program_id
561 ,g_program_login_id
562 ,g_program_application_id
563 ,g_request_id
564 FROM
565 (SELECT
566 csi1.organization_id
567 ,csi1.SECONDARY_INVENTORY_NAME
568 ,cutt.TRANSACTION_TYPE_ID
569 ,mtt.TRANSACTION_ACTION_ID
570 ,mtt.TRANSACTION_SOURCE_TYPE_ID
571 FROM
572 CSP_PLANNING_PARAMETERS cpp
573 ,CSP_USG_TRANSACTION_TYPES cutt
574 ,CSP_SEC_INVENTORIES csi1
575 ,MTL_TRANSACTION_TYPES mtt
576 WHERE
577 cpp.FORECAST_RULE_ID = cutt.FORECAST_RULE_ID
578 and csi1.organization_id = cpp.organization_id
579 and csi1.SECONDARY_INVENTORY_NAME = cpp.SECONDARY_INVENTORY
580 and csi1.CONDITION_TYPE = 'G' /* Usable Subinventory */
581 and mtt.TRANSACTION_TYPE_ID = cutt.TRANSACTION_TYPE_ID
582 and mtt.TRANSACTION_ACTION_ID = 1 /* Issue */
583 UNION
584 SELECT /* For subinventories without forecast rule defined: Use this default
585 transaction type */
586 csi2.organization_id
587 ,csi2.SECONDARY_INVENTORY_NAME
588 ,93 transaction_type_id /* Field Service Usage transaction type */
589 ,1 TRANSACTION_ACTION_ID
590 ,13 TRANSACTION_SOURCE_TYPE_ID
591 FROM
592 CSP_SEC_INVENTORIES csi2
593 WHERE
594 csi2.CONDITION_TYPE = 'G' /* Usable Subinventory */
595 and not exists
596 (select 'x' from CSP_PLANNING_PARAMETERS cpp
597 where cpp.organization_id = csi2.organization_id
598 and cpp.SECONDARY_INVENTORY = csi2.secondary_inventory_name
599 and cpp.forecast_rule_id is not null) /* Do not include subinventories that
600 have forecast rules defined */
601 ) sec
602 ,mtl_material_transactions mmt
603 ,ISC_FS_INV_USG_LOG bound /* Obtain the transaction boundaries from our log table */
604 ,mtl_transaction_accounts mta
605 WHERE
606 mmt.organization_id = sec.organization_id
607 and mmt.SUBINVENTORY_CODE = sec.SECONDARY_INVENTORY_NAME
608 and mmt.TRANSACTION_ACTION_ID = sec.TRANSACTION_ACTION_ID
609 and mmt.TRANSACTION_TYPE_ID = sec.TRANSACTION_TYPE_ID
610 and mmt.TRANSACTION_SOURCE_TYPE_ID = sec.TRANSACTION_SOURCE_TYPE_ID
611 and mmt.organization_id = bound.organization_id
612 /* Note that the boundary conditions have changed from > to >= and from <= to < due to changes in OPI's log table logic */
613 and mmt.transaction_id >= bound.from_transaction_id
614 and mmt.transaction_id < bound.to_transaction_id
615 and mta.accounting_line_type = 1 /* Inventory Valuation */
616 and mta.transaction_id = mmt.transaction_id
617 GROUP BY
618 mta.ORGANIZATION_ID
619 ,mmt.SUBINVENTORY_CODE
620 ,mta.INVENTORY_ITEM_ID
621 ,trunc(mta.TRANSACTION_DATE);
622
623 l_row_count := sql%rowcount;
624 COMMIT;
625
626 BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into staging table', 1 );
627
628 -- gather statistics on staging table before computing
629 -- conversion rates
630 l_stmt_num := 50;
631 IF GATHER_STATISTICS
632 ( l_isc_schema
633 , 'ISC_FS_INV_USG_VALUE_STG'
634 , l_err_msg ) <> 0 THEN
635 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
636 RAISE l_exception;
637 END IF;
638
639 BIS_COLLECTION_UTILITIES.LOG( 'Staging table analyzed', 1 );
640
641 -- check currency conversion rates
642 l_stmt_num := 60;
643 IF COMPUTE_INV_CONV_RATES
644 ( l_isc_schema
645 , l_err_msg ) <> 0 THEN
646 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
647 RAISE l_exception;
648 END IF;
649
650 -- truncate the fact table
651 l_stmt_num := 70;
652 IF TRUNCATE_TABLE
653 ( l_isc_schema
654 , 'ISC_FS_INV_USG_VALUE_F PURGE MATERIALIZED VIEW LOG'
655 , l_err_msg ) <> 0 THEN
656 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
657 RAISE l_exception;
658 END IF;
659
660 BIS_COLLECTION_UTILITIES.LOG( 'Base summary table truncated', 1 );
661
662 /* Insert field service inventory usage value data into the DBI Field Service Inventory
663 Usage Value Base Summary table based on Staging table and Current Conversion table */
664 l_stmt_num := 80;
665 INSERT /* append parallel(f) */ INTO ISC_FS_INV_USG_VALUE_F f
666 (
667 ORGANIZATION_ID
668 ,SUBINVENTORY_CODE
669 ,TRANSACTION_DATE
670 ,INVENTORY_ITEM_ID
671 ,ONHAND_VALUE_B
672 ,PRIM_CONVERSION_RATE
673 ,SEC_CONVERSION_RATE
674 ,CREATED_BY
675 ,CREATION_DATE
676 ,LAST_UPDATE_DATE
677 ,LAST_UPDATED_BY
678 ,LAST_UPDATE_LOGIN
679 ,PROGRAM_ID
680 ,PROGRAM_LOGIN_ID
681 ,PROGRAM_APPLICATION_ID
682 ,REQUEST_ID
683 )
684 SELECT /* parallel(s) parallel(c) */
685 s.ORGANIZATION_ID
686 ,s.SUBINVENTORY_CODE
687 ,s.TRANSACTION_DATE
688 ,s.INVENTORY_ITEM_ID
689 ,s.ONHAND_VALUE_B
690 ,c.conversion_rate1
691 ,c.conversion_rate2
692 ,g_user_id
693 ,g_sysdate
694 ,g_sysdate
695 ,g_user_id
696 ,g_login_id
697 ,g_program_id
698 ,g_program_login_id
699 ,g_program_application_id
700 ,g_request_id
701 FROM
702 ISC_FS_INV_USG_VALUE_STG s
703 ,ISC_FS_INV_USG_CONV_RATES c
704 WHERE
705 c.organization_id = s.organization_id
706 AND c.transaction_date = s.transaction_date;
707
708 l_row_count := sql%rowcount;
709 COMMIT;
710
711 BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into base summary', 1 );
712
713 -- cleanup staging/currency conversion tables
714 l_stmt_num := 90;
715 IF TRUNCATE_TABLE
716 ( l_isc_schema
717 , 'ISC_FS_INV_USG_VALUE_STG'
718 , l_err_msg ) <> 0 THEN
719 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
720 RAISE l_exception;
721 END IF;
722
723 BIS_COLLECTION_UTILITIES.LOG( 'Staging table truncated', 1 );
724
725 l_stmt_num := 100;
726 IF TRUNCATE_TABLE
727 ( l_isc_schema
728 , 'ISC_FS_INV_USG_CONV_RATES'
729 , l_err_msg ) <> 0 THEN
730 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
731 RAISE l_exception;
732 END IF;
733
734 BIS_COLLECTION_UTILITIES.LOG( 'Currency conversion table truncated', 1 );
735
736 l_stmt_num := 110;
737 BIS_COLLECTION_UTILITIES.WRAPUP(
738 p_status => TRUE,
739 p_count => l_row_count,
740 p_message => 'Successfully loaded Field Service Inventory Usage Base Table at ' ||
741 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
742 );
743
744 BIS_COLLECTION_UTILITIES.LOG('End Initial Load');
745
746 errbuf := null;
747 retcode := g_ok;
748
749 ELSE
750 retcode := g_error;
751 BIS_COLLECTION_UTILITIES.LOG('Global Parameters are not setup.');
752 BIS_COLLECTION_UTILITIES.LOG('Please check that the profile option BIS_GLOBAL_START_DATE
753 is setup.');
754
755 END IF;
756
757 EXCEPTION
758 WHEN OTHERS THEN
759 rollback;
760 IF l_err_msg is null THEN
761 l_err_msg := substr(sqlerrm,1,4000);
762 END IF;
763 BIS_COLLECTION_UTILITIES.WRAPUP( p_status => FALSE
764 , p_message => l_err_msg
765 );
766 errbuf := l_err_msg;
767 retcode := g_error;
768
769 END GET_INV_USG_INITIAL_LOAD;
770
771
772
773 PROCEDURE GET_INV_USG_INCREMENTAL_LOAD(errbuf in out NOCOPY varchar2, retcode in out NOCOPY
774 varchar2)
775 IS
776 l_proc_name constant varchar2(30) := 'GET_INV_USG_INCREMENTAL_LOAD';
777 l_stmt_num NUMBER;
778 l_row_count NUMBER;
779 l_err_num NUMBER;
780 l_err_msg VARCHAR2(255);
781 l_exception exception;
782 l_isc_schema VARCHAR2(30);
783 l_status VARCHAR2(30);
784 l_industry VARCHAR2(30);
785 l_list dbms_sql.varchar2_table;
786 BEGIN
787
788 l_list(1) := 'BIS_GLOBAL_START_DATE';
789
790 BIS_COLLECTION_UTILITIES.LOG( 'Begin Incremental Load' );
791
792 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
793
794 l_stmt_num := 0;
795 IF NOT BIS_COLLECTION_UTILITIES.SETUP( g_object_name ) THEN
796 l_err_msg := 'Error in BIS_COLLECTION_UTILITIES.Setup';
797 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
798 RAISE g_bis_setup_exception;
799 END IF;
800
801 /* Update or insert into our log table the new upper transaction boundaries for every
802 organization to be extracted. We will access OPI_DBI_INV_VALUE_LOG to obtain these upper
803 boundaries. The next incremental load will use these boundaries as starting points.
804 (Note: We will be using the from transaction id column to store the upper transaction
805 boundary momentarily during the collection since the to transaction id column already
806 stores the lower transaction id. At the end of the collection we will swith the from and
807 to boundaries.) */
808
809 l_stmt_num := 5;
810 MERGE INTO ISC_FS_INV_USG_LOG bivlog USING
811 (
812 /* This select statement has changed due to OPI changes in its Boundaries log table */
813 SELECT
814 from_to_txn.BOUND_LEVEL_ENTITY_ID organization_id
815 ,from_to_txn.TO_BOUND_ID FROM_TRANSACTION_ID
816 /* This will become the To transaction at the end of the collection. We are temporarily storing the To boundary
817 in the from transaction column */
818 FROM
819 OPI_DBI_CONC_PROG_RUN_LOG from_to_txn
820 WHERE
821 from_to_txn.etl_type = 'INVENTORY'
822 AND from_to_txn.BOUND_TYPE = 'ID'
823 AND from_to_txn.DRIVING_TABLE_CODE = 'MMT'
824 AND from_to_txn.LOAD_TYPE = 'INCR'
825 AND from_to_txn.BOUND_LEVEL_ENTITY_CODE = 'ORGANIZATION'
826 ) opilog
827 ON (bivlog.organization_id = opilog.organization_id)
828 WHEN matched THEN
829 UPDATE SET
830 bivlog.FROM_TRANSACTION_ID = opilog.FROM_TRANSACTION_ID
831 ,bivlog.LAST_UPDATE_DATE = g_sysdate
832 ,bivlog.LAST_UPDATED_BY = g_user_id
833 ,bivlog.LAST_UPDATE_LOGIN = g_login_id
834 ,bivlog.PROGRAM_ID = g_program_id
835 ,bivlog.PROGRAM_LOGIN_ID = g_program_login_id
836 ,bivlog.PROGRAM_APPLICATION_ID = g_program_application_id
837 ,bivlog.REQUEST_ID = g_request_id
838 WHEN NOT matched THEN
839 INSERT /* If new organization has been created after field service collection */
840 (
841 ORGANIZATION_ID
842 ,FROM_TRANSACTION_ID
843 ,TO_TRANSACTION_ID
844 ,CREATED_BY
845 ,CREATION_DATE
846 ,LAST_UPDATE_DATE
847 ,LAST_UPDATED_BY
848 ,LAST_UPDATE_LOGIN
849 ,PROGRAM_ID
850 ,PROGRAM_LOGIN_ID
851 ,PROGRAM_APPLICATION_ID
852 ,REQUEST_ID
853 )
854 VALUES
855 (
856 opilog.ORGANIZATION_ID
857 ,opilog.FROM_TRANSACTION_ID /* This temporarily contains the To transaction boundary */
858 ,-1 /* It will become the from transaction id at the end of the collection */
859 /* We use -1 because the OPI Incremental could have run more than once since
860 the last time the Field Service Incremental was run */
861 ,g_user_id
862 ,g_sysdate
863 ,g_sysdate
864 ,g_user_id
865 ,g_login_id
866 ,g_program_id
867 ,g_program_login_id
868 ,g_program_application_id
869 ,g_request_id
870 );
871
872 BIS_COLLECTION_UTILITIES.LOG( 'Log Table updated', 1);
873
874 -- get the isc schema name
875 l_stmt_num := 10;
876 IF get_schema_name
877 ( l_isc_schema
878 , l_err_msg ) <> 0 THEN
879 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
880 RAISE l_exception;
881 END IF;
882
883 -- truncate the staging table
884 l_stmt_num := 20;
885 IF truncate_table
886 ( l_isc_schema
887 , 'ISC_FS_INV_USG_VALUE_STG'
888 , l_err_msg ) <> 0 THEN
889 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
890 RAISE l_exception;
891 END IF;
892
893 BIS_COLLECTION_UTILITIES.LOG( 'Staging table truncated', 1);
894
895 /* Insert field service material issue transaction values into staging table */
896 l_stmt_num := 30;
897 INSERT /*+ append parallel(ISC_FS_INV_USG_VALUE_STG) */ INTO ISC_FS_INV_USG_VALUE_STG
898 (
899 ORGANIZATION_ID
900 ,SUBINVENTORY_CODE
901 ,TRANSACTION_DATE
902 ,INVENTORY_ITEM_ID
903 ,ONHAND_VALUE_B
904 ,CREATED_BY
905 ,CREATION_DATE
906 ,LAST_UPDATE_DATE
907 ,LAST_UPDATED_BY
908 ,LAST_UPDATE_LOGIN
909 ,PROGRAM_ID
910 ,PROGRAM_LOGIN_ID
911 ,PROGRAM_APPLICATION_ID
912 ,REQUEST_ID
913 )
914 SELECT
915 mta.ORGANIZATION_ID
916 ,mmt.SUBINVENTORY_CODE
917 ,trunc(mta.TRANSACTION_DATE) TRANSACTION_DATE
918 ,mta.INVENTORY_ITEM_ID
919 ,SUM(mta.BASE_TRANSACTION_VALUE)*(-1) ONHAND_VALUE_B /* Issue transactions are expressed
920 in negative numbers */
921 ,g_user_id
922 ,g_sysdate
923 ,g_sysdate
924 ,g_user_id
925 ,g_login_id
926 ,g_program_id
927 ,g_program_login_id
928 ,g_program_application_id
929 ,g_request_id
930 FROM
931 mtl_transaction_accounts mta,
932 (SELECT
933 csi1.organization_id
934 ,csi1.SECONDARY_INVENTORY_NAME
935 ,cutt.TRANSACTION_TYPE_ID
936 ,mtt.TRANSACTION_ACTION_ID
937 ,mtt.TRANSACTION_SOURCE_TYPE_ID
938 FROM
939 CSP_PLANNING_PARAMETERS cpp,
940 CSP_USG_TRANSACTION_TYPES cutt,
941 CSP_SEC_INVENTORIES csi1,
942 MTL_TRANSACTION_TYPES mtt
943 WHERE
944 cpp.FORECAST_RULE_ID = cutt.FORECAST_RULE_ID
945 AND csi1.organization_id = cpp.organization_id
946 AND csi1.SECONDARY_INVENTORY_NAME = cpp.SECONDARY_INVENTORY
947 AND csi1.CONDITION_TYPE = 'G' /* Usable Subinventory */
948 AND mtt.TRANSACTION_TYPE_ID = cutt.TRANSACTION_TYPE_ID
949 AND mtt.TRANSACTION_ACTION_ID = 1 /* Issue */
950 UNION
951 SELECT /* For subinventories without forecast rule defined: Use this default transaction
952 type */
953 csi2.organization_id
954 ,csi2.SECONDARY_INVENTORY_NAME
955 ,93 transaction_type_id /* Field Service Usage transaction type */
956 ,1 TRANSACTION_ACTION_ID
957 ,13 TRANSACTION_SOURCE_TYPE_ID
958 FROM CSP_SEC_INVENTORIES csi2
959 WHERE
960 csi2.CONDITION_TYPE = 'G' /* Usable Subinventory */
961 and not exists
962 (select 'x' from CSP_PLANNING_PARAMETERS cpp
963 where cpp.organization_id = csi2.organization_id
964 and cpp.SECONDARY_INVENTORY = csi2.secondary_inventory_name
965 and cpp.forecast_rule_id is not null) /* Do not include subinventories that
966 have forecast rules defined */
967 ) sec,
968 mtl_material_transactions mmt
969 ,ISC_FS_INV_USG_LOG bound /* Obtain the transaction boundaries from our log table */
970 WHERE
971 mmt.organization_id = sec.organization_id
972 AND mmt.SUBINVENTORY_CODE = sec.SECONDARY_INVENTORY_NAME
973 AND mmt.TRANSACTION_ACTION_ID = sec.TRANSACTION_ACTION_ID
974 AND mmt.TRANSACTION_TYPE_ID = sec.TRANSACTION_TYPE_ID
975 AND mmt.TRANSACTION_SOURCE_TYPE_ID = sec.TRANSACTION_SOURCE_TYPE_ID
976 AND mta.accounting_line_type = 1
977 AND mta.transaction_id = mmt.transaction_id
978 AND mta.organization_id = bound.organization_id
979 /* Note that the boundary conditions have changed from > to >= and from <= to < due to changes in OPI's log table logic */
980 AND mta.transaction_id >= bound.to_transaction_id /* The from and to boundaries are
981 switched in the log table at this point */
982 AND mta.transaction_id < bound.from_transaction_id
983 GROUP BY
984 mta.ORGANIZATION_ID
985 ,mmt.SUBINVENTORY_CODE
986 ,mta.INVENTORY_ITEM_ID
987 ,trunc(mta.TRANSACTION_DATE);
988
989 l_row_count := sql%rowcount;
990 COMMIT;
991 BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into staging table', 1 );
992
993 -- gather statistics on staging table before computing
994 -- conversion rates
995 l_stmt_num := 40;
996 IF GATHER_STATISTICS
997 ( l_isc_schema
998 , 'ISC_FS_INV_USG_VALUE_STG'
999 , l_err_msg ) <> 0 THEN
1000 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
1001 RAISE l_exception;
1002 END IF;
1003
1004 BIS_COLLECTION_UTILITIES.LOG( 'Staging table analyzed', 1 );
1005
1006 -- check currency conversion rates
1007 l_stmt_num := 50;
1008 IF COMPUTE_INV_CONV_RATES
1009 ( l_isc_schema
1010 , l_err_msg ) <> 0 THEN
1011 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
1012 RAISE l_exception;
1013 END IF;
1014
1015
1016 /* Merge field service inventory usage value data into the DBI Field Service Inventory
1017 Usage Value Base Summary table based on Staging table and Current Conversion table */
1018
1019 l_stmt_num := 60;
1020 MERGE INTO ISC_FS_INV_USG_VALUE_F f USING
1021 (
1022 SELECT /* parallel(s) parallel(c) */
1023 s.ORGANIZATION_ID
1024 ,s.SUBINVENTORY_CODE
1025 ,s.TRANSACTION_DATE
1026 ,s.INVENTORY_ITEM_ID
1027 ,s.ONHAND_VALUE_B
1028 ,c.conversion_rate1
1029 ,c.conversion_rate2
1030 FROM
1031 ISC_FS_INV_USG_VALUE_STG s
1032 ,ISC_FS_INV_USG_CONV_RATES c
1033 WHERE
1034 c.organization_id = s.organization_id
1035 AND c.transaction_date = s.transaction_date
1036 ) t
1037 ON
1038 (t.organization_id = f.organization_id
1039 AND t.subinventory_code = f.subinventory_code
1040 AND t.inventory_item_id = f.inventory_item_id
1041 AND t.transaction_date = f.transaction_date
1042 )
1043 WHEN MATCHED THEN
1044 UPDATE SET
1045 f.ONHAND_VALUE_B = f.ONHAND_VALUE_B + t.ONHAND_VALUE_B
1046 ,f.LAST_UPDATE_DATE = g_sysdate
1047 ,f.LAST_UPDATED_BY = g_user_id
1048 ,f.LAST_UPDATE_LOGIN = g_login_id
1049 ,f.PROGRAM_ID = g_program_id
1050 ,f.PROGRAM_LOGIN_ID = g_program_login_id
1051 ,f.PROGRAM_APPLICATION_ID = g_program_application_id
1052 ,f.REQUEST_ID = g_request_id
1053 WHEN NOT MATCHED THEN
1054 INSERT
1055 (
1056 ORGANIZATION_ID
1057 ,SUBINVENTORY_CODE
1058 ,TRANSACTION_DATE
1059 ,INVENTORY_ITEM_ID
1060 ,ONHAND_VALUE_B
1061 ,PRIM_CONVERSION_RATE
1062 ,SEC_CONVERSION_RATE
1063 ,CREATED_BY
1064 ,CREATION_DATE
1065 ,LAST_UPDATE_DATE
1066 ,LAST_UPDATED_BY
1067 ,LAST_UPDATE_LOGIN
1068 ,PROGRAM_ID
1069 ,PROGRAM_LOGIN_ID
1070 ,PROGRAM_APPLICATION_ID
1071 ,REQUEST_ID
1072 )
1073 VALUES
1074 (
1075 t.ORGANIZATION_ID
1076 ,t.SUBINVENTORY_CODE
1077 ,t.TRANSACTION_DATE
1078 ,t.INVENTORY_ITEM_ID
1079 ,t.ONHAND_VALUE_B
1080 ,t.conversion_rate1
1081 ,t.conversion_rate2
1082 ,g_user_id
1083 ,g_sysdate
1084 ,g_sysdate
1085 ,g_user_id
1086 ,g_login_id
1087 ,g_program_id
1088 ,g_program_login_id
1089 ,g_program_application_id
1090 ,g_request_id
1091 );
1092
1093 l_row_count := sql%rowcount;
1094 COMMIT;
1095
1096 BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into base summary', 1 );
1097
1098 -- cleanup staging
1099 l_stmt_num := 70;
1100 IF TRUNCATE_TABLE
1101 ( l_isc_schema
1102 , 'ISC_FS_INV_USG_VALUE_STG'
1103 , l_err_msg ) <> 0 THEN
1104 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
1105 RAISE l_exception;
1106 END IF;
1107
1108 BIS_COLLECTION_UTILITIES.LOG( 'Staging table truncated', 1 );
1109
1110 l_stmt_num := 80;
1111 IF TRUNCATE_TABLE
1112 ( l_isc_schema
1113 , 'ISC_FS_INV_USG_CONV_RATES'
1114 , l_err_msg ) <> 0 THEN
1115 LOGGER( l_proc_name, l_stmt_num, l_err_msg );
1116 RAISE l_exception;
1117 END IF;
1118
1119 BIS_COLLECTION_UTILITIES.LOG( 'Currency conversion table truncated', 1 );
1120
1121 l_stmt_num := 90;
1122 UPDATE ISC_FS_INV_USG_LOG
1123 SET from_transaction_id = to_transaction_id,to_transaction_id = from_transaction_id;
1124
1125 COMMIT;
1126
1127 BIS_COLLECTION_UTILITIES.LOG( 'Log table updated', 1 );
1128
1129 l_stmt_num := 100;
1130 BIS_COLLECTION_UTILITIES.WRAPUP(
1131 p_status => TRUE,
1132 p_count => l_row_count,
1133 p_message => 'Successfully loaded Field Service Inventory Usage Base Table at ' ||
1134 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
1135 );
1136
1137 BIS_COLLECTION_UTILITIES.LOG('End Incremental Load');
1138
1139 errbuf := null;
1140 retcode := g_ok;
1141
1142 ELSE
1143 retcode := g_error;
1144 BIS_COLLECTION_UTILITIES.LOG('Global Parameters are not setup.');
1145 BIS_COLLECTION_UTILITIES.LOG('Please check that the profile option BIS_GLOBAL_START_DATE
1146 is setup.');
1147
1148 END IF;
1149
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 ROLLBACK;
1153 IF l_err_msg is null THEN
1154 l_err_msg := substr(sqlerrm,1,4000);
1155 END IF;
1156 BIS_COLLECTION_UTILITIES.WRAPUP( p_status => FALSE
1157 , p_message => l_err_msg
1158 );
1159 errbuf := l_err_msg;
1160 retcode := g_error;
1161
1162 END GET_INV_USG_INCREMENTAL_LOAD;
1163
1164
1165 End ISC_FS_INV_USG_ETL_PKG;