[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_JOBS_PKG
Source
1 Package Body OPI_DBI_JOBS_PKG AS
2 /*$Header: OPIDJOBSB.pls 120.16 2007/04/04 05:28:57 sdiwakar ship $ */
3
4 -- Standard WHO column information
5 g_sysdate DATE;
6 g_user_id NUMBER;
7 g_login_id NUMBER;
8 g_program_id NUMBER;
9 g_program_login_id NUMBER;
10 g_program_application_id NUMBER;
11 g_request_id NUMBER;
12
13 -- currency types
14 g_global_rate_type VARCHAR2(15);
15 g_global_currency_code VARCHAR2(10);
16 g_secondary_rate_type VARCHAR2(15);
17 g_secondary_currency_code VARCHAR2(10);
18
19 -- Start date of Euro currency
20 g_euro_start_date CONSTANT DATE := to_date('01/01/1999','DD/MM/YYYY');
21
22 -- error codes
23 g_ok CONSTANT NUMBER(1) := 0;
24 g_warning CONSTANT NUMBER(1) := 1;
25 g_error CONSTANT NUMBER(1) := -1;
26
27 /* Marker for secondary conv. rate if the primary and secondary curr codes
28 and rate types are identical. Can't be -1, -2, -3 since the FII APIs
29 return those values. */
30 C_PRI_SEC_CURR_SAME_MARKER CONSTANT NUMBER := -9999;
31
32 -- GL API returns -3 if EURO rate missing on 01-JAN-1999
33 C_EURO_MISSING_AT_START CONSTANT NUMBER := -3;
34
35 -- File scope variables
36 g_global_start_date DATE;
37 g_last_collection_date DATE;
38 g_opm_last_collection_date DATE;
39 g_number_max_value NUMBER;
40 g_degree NUMBER := 1;
41 g_r12_migration_date DATE;
42
43 /* get_conversion_rate
44
45 Compute all the conversion rates for all distinct organization,
46 transaction date pairs in the staging table. The date in the fact
47 table is already without a timestamp i.e. trunc'ed.
48
49 There are two conversion rates to be computed:
50 1. Primary global
51 2. Secondary global (if set up)
52
53 The conversion rate work table was truncated during
54 the initialization phase.
55
56 Get the currency conversion rates based on the data in
57 OPI_DBI_JOBS_STG using the fii_currency.get_global_rate_primary
58 API for the primary global currency and
59 fii_currency.get_global_rate_secondary for the secondary global currency.
60 The primary currency API:
61 1. finds the conversion rate if one exists.
62 2. returns -1 if there is no conversion rate on that date.
63 3. returns -2 if the currency code is not found.
64 4. returns -3 if the transaction_date is prior to 01-JAN-1999,
65 the functional currency code is EUR and there is no EUR to USD
66 conversion rate defined on 01-JAN-1999.
67
68 The secondary currency API:
69 1. Finds the global secondary currency rate if one exists.
70 2. Returns a rate of 1 if the secondary currency has not been set up.
71 3. Returns -1, -2, -3 in the same way as the primary currency code API.
72
73 If the global and secondary currency codes and rate types are identical,
74 do not call the secondary currency API. Instead update the secondary
75 rates from the primary.
76
77 If the secondary currency has not been set up, set the conversion rate
78 to null.
79
80 If any primary conversion rates are missing, throw an exception.
81 If any secondary currency rates are missing (after the secondary
82 currency has been set up) throw an exception.
83
84 Need to commit data here due to insert+append.
85
86 Date Author Action
87 08/25/2004 Dinkar Gupta Modified to provide secondary
88 currency support.
89 */
90 FUNCTION Get_Conversion_Rate (
91 errbuf IN OUT NOCOPY VARCHAR2,
92 retcode IN OUT NOCOPY VARCHAR2
93 )
94 RETURN NUMBER
95 IS
96
97 -- Cursor to see if any rates are missing. See below for details
98 CURSOR invalid_rates_exist_csr IS
99 SELECT 1
100 FROM opi_dbi_muv_conv_rates
101 WHERE ( nvl (conversion_rate, -999) < 0
102 OR nvl (sec_conversion_rate, 999) < 0)
103 AND rownum < 2;
104
105 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
106
107
108 -- Set up a cursor to get all the invalid rates.
109 -- By the logic of the fii_currency.get_global_rate_primary
110 -- and fii_currency.get_global_rate_secondary APIs, the returned value
111 -- is -ve if no rate exists:
112 -- -1 for dates with no rate.
113 -- -2 for unrecognized conversion rates.
114 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
115 -- transaction_date is prior to 01-JAN-1999 (when the EUR
116 -- officially went into circulation).
117 --
118 -- However, with the secondary currency, the null rate means it
119 -- has not been setup and should therefore not be reported as an
120 -- error.
121 --
122 -- Also, cross check with the org-date pairs in the staging table,
123 -- in case some orgs never had a functional currency code defined.
124 CURSOR get_missing_rates_c (p_pri_sec_curr_same NUMBER) IS
125 SELECT DISTINCT
126 report_order,
127 curr_code,
128 rate_type,
129 completion_date,
130 func_currency_code
131 FROM (
132 SELECT DISTINCT
133 g_global_currency_code curr_code,
134 g_global_rate_type rate_type,
135 1 report_order, -- ordering global currency first
136 mp.organization_code,
137 decode (conv.conversion_rate,
138 C_EURO_MISSING_AT_START, g_euro_start_date,
139 conv.transaction_date) completion_date,
140 conv.f_currency_code func_currency_code
141 FROM opi_dbi_muv_conv_rates conv,
142 mtl_parameters mp,
143 (SELECT /*+ parallel (opi_dbi_jobs_stg) */
144 DISTINCT organization_id,
145 trunc (completion_date) completion_date
146 FROM opi_dbi_jobs_stg) to_conv
147 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
148 AND mp.organization_id = to_conv.organization_id
149 AND conv.transaction_date (+) = to_conv.completion_date
150 AND conv.organization_id (+) = to_conv.organization_id
151 UNION ALL
152 SELECT DISTINCT
153 g_secondary_currency_code curr_code,
154 g_secondary_rate_type rate_type,
155 decode (p_pri_sec_curr_same,
156 1, 1,
157 2) report_order, --ordering secondary currency next
158 mp.organization_code,
159 decode (conv.sec_conversion_rate,
160 C_EURO_MISSING_AT_START, g_euro_start_date,
161 conv.transaction_date) completion_date,
162 conv.f_currency_code func_currency_code
163 FROM opi_dbi_muv_conv_rates conv,
164 mtl_parameters mp,
165 (SELECT /*+ parallel (opi_dbi_jobs_stg) */
166 DISTINCT organization_id,
167 trunc (completion_date) completion_date
168 FROM opi_dbi_jobs_stg) to_conv
169 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
170 AND mp.organization_id = to_conv.organization_id
171 AND conv.transaction_date (+) = to_conv.completion_date
172 AND conv.organization_id (+) = to_conv.organization_id)
173 ORDER BY
174 report_order ASC,
175 completion_date,
176 func_currency_code;
177
178 l_stmt_num NUMBER;
179 no_currency_rate_flag NUMBER;
180
181 -- Flag to check if the primary and secondary currencies are the
182 -- same
183 l_pri_sec_curr_same NUMBER;
184
185 -- old error reporting
186 i_err_num NUMBER;
187 i_err_msg VARCHAR2(255);
188
189
190 BEGIN
191
192 l_stmt_num := 0;
193 -- initialization block
194 retcode := g_ok;
195 no_currency_rate_flag := 0;
196 l_pri_sec_curr_same := 0;
197
198
199 l_stmt_num := 10;
200 -- check if the primary and secondary currencies and rate types are
201 -- identical.
202 IF (g_global_currency_code = nvl (g_secondary_currency_code, '---') AND
203 g_global_rate_type = nvl (g_secondary_rate_type, '---') ) THEN
204 l_pri_sec_curr_same := 1;
205 END IF;
206
207
208 l_stmt_num := 20;
209 -- Use the fii_currency.get_global_rate_primary function to get the
210 -- conversion rate given a currency code and a date.
211 -- The function returns:
212 -- 1 for currency code of 'USD' which is the global currency
213 -- -1 for dates for which there is no currency conversion rate
214 -- -2 for unrecognized currency conversion rates
215 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
216 -- transaction_date is prior to 01-JAN-1999 (when the EUR
217 -- officially went into circulation).
218
219 -- Use the fii_currency.get_global_rate_secondary to get the secondary
220 -- global rate. If the secondary currency has not been set up,
221 -- make the rate null. If the secondary currency/rate types are the
222 -- same as the primary, don't call the API but rather use an update
223 -- statement followed by the insert.
224
225 -- By selecting distinct org and currency code from the gl_set_of_books
226 -- and hr_organization_information, take care of duplicate codes.
227 INSERT /*+ append parallel(rates) */
228 INTO opi_dbi_muv_conv_rates rates (
229 organization_id,
230 f_currency_code,
231 transaction_date,
232 conversion_rate,
233 sec_conversion_rate,
234 creation_date,
235 last_update_date,
236 created_by,
237 last_updated_by,
238 last_update_login,
239 PROGRAM_ID,
240 PROGRAM_LOGIN_ID,
241 PROGRAM_APPLICATION_ID,
242 REQUEST_ID )
243 SELECT /*+ parallel (to_conv) parallel (curr_codes) */
244 to_conv.organization_id,
245 curr_codes.currency_code,
246 to_conv.completion_date,
247 decode (curr_codes.currency_code,
248 g_global_currency_code, 1,
249 fii_currency.get_global_rate_primary (
250 curr_codes.currency_code,
251 to_conv.completion_date) ),
252 decode (g_secondary_currency_code,
253 NULL, NULL,
254 curr_codes.currency_code, 1,
255 decode (l_pri_sec_curr_same,
256 1, C_PRI_SEC_CURR_SAME_MARKER,
257 fii_currency.get_global_rate_secondary (
258 curr_codes.currency_code,
259 to_conv.completion_date))),
260 g_sysdate,
261 g_sysdate,
262 g_user_id,
263 g_user_id,
264 g_login_id,
265 g_program_id,
266 g_program_login_id,
267 g_program_application_id,
268 g_request_id
269 FROM
270 (SELECT /*+ parallel (opi_dbi_jobs_stg) */
271 DISTINCT organization_id, trunc (completion_date) completion_date
272 FROM opi_dbi_jobs_stg) to_conv,
273 (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
274 parallel (hoi) parallel (gsob)*/
275 DISTINCT hoi.organization_id, gsob.currency_code
276 FROM hr_organization_information hoi,
277 gl_sets_of_books gsob
278 WHERE hoi.org_information_context = 'Accounting Information'
279 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
280 curr_codes
281 WHERE curr_codes.organization_id = to_conv.organization_id;
282
283
284 --Introduced commit because of append parallel in the insert stmt above.
285 commit;
286
287
288 l_stmt_num := 40;
289 -- if the primary and secondary currency codes are the same, then
290 -- update the secondary with the primary
291 IF (l_pri_sec_curr_same = 1) THEN
292
293 UPDATE /*+ parallel (opi_dbi_muv_conv_rates) */
294 opi_dbi_muv_conv_rates
295 SET sec_conversion_rate = conversion_rate;
296
297 -- safe to commit, as before
298 commit;
299
300 END IF;
301
302
303 -- report missing rate
304 l_stmt_num := 50;
305
306 OPEN invalid_rates_exist_csr;
307 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
308 IF (invalid_rates_exist_csr%FOUND) THEN
309
310 -- there are missing rates - prepare to report them.
311 no_currency_rate_flag := 1;
312 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
313
314 l_stmt_num := 60;
315 FOR get_missing_rates_rec IN get_missing_rates_c (l_pri_sec_curr_same)
316 LOOP
317
318 BIS_COLLECTION_UTILITIES.writemissingrate (
319 get_missing_rates_rec.rate_type,
320 get_missing_rates_rec.func_currency_code,
321 get_missing_rates_rec.curr_code,
322 get_missing_rates_rec.completion_date);
323
324 END LOOP;
325
326 END IF;
327 CLOSE invalid_rates_exist_csr;
328
329
330 l_stmt_num := 70; /* check no_currency_rate_flag */
331 IF (no_currency_rate_flag = 1) THEN /* missing rate found */
332 bis_collection_utilities.put_line('ERROR: Please setup conversion rate for all missing rates reported');
333
334 retcode := g_error;
335 END IF;
336
337 return retcode;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 rollback;
342 i_err_num := SQLCODE;
343 i_err_msg := 'OPI_DBI_JOBS_PKG.GET_CONVERSION_RATE ('
344 || to_char(l_stmt_num)
345 || '): '
346 || substr(SQLERRM, 1,200);
347
348 BIS_COLLECTION_UTILITIES.put_line('OPI_DBI_JOBS_PKG.GET_CONVERSION_RATE - Error at statement ('
349 || to_char(l_stmt_num)
350 || ')');
351
352 BIS_COLLECTION_UTILITIES.put_line('Error Number: ' || to_char(i_err_num));
353 BIS_COLLECTION_UTILITIES.put_line('Error Message: ' || i_err_msg);
354
355 retcode := g_error;
356 return g_error;
357
358 END Get_Conversion_Rate;
359
360
361
362 FUNCTION Insert_into_Jobs_Fact RETURN NUMBER
363 IS
364 l_row_count NUMBER;
365 BEGIN
366
367 INSERT /*+ append parallel(f) */
368 INTO opi_dbi_jobs_f f (
369 organization_id,
370 job_id,
371 job_type,
372 status,
373 completion_date,
374 assembly_item_id,
375 start_quantity,
376 actual_qty_completed,
377 uom_code,
378 conversion_rate,
379 sec_conversion_rate,
380 include_job,
381 std_req_flag,
382 std_res_flag,
383 source,
384 creation_date,
385 last_update_date,
386 created_by,
387 last_updated_by,
388 last_update_login,
389 job_name,
390 line_type,
391 scheduled_completion_date,
392 job_status_code,
393 job_start_value,
394 PROGRAM_ID,
395 PROGRAM_LOGIN_ID,
396 PROGRAM_APPLICATION_ID,
397 REQUEST_ID)
398 SELECT /*+ parallel (fstg) parallel (conv) */
399 fstg.organization_id,
400 fstg.job_id,
401 fstg.job_type,
402 fstg.status,
403 fstg.completion_date,
404 fstg.assembly_item_id,
405 fstg.start_quantity,
406 fstg.actual_qty_completed,
407 fstg.uom_code,
408 conv.conversion_rate,
409 conv.sec_conversion_rate,
410 fstg.include_job,
411 1,
412 fstg.std_res_flag,
413 fstg.source,
414 fstg.creation_date,
415 fstg.last_update_date,
416 fstg.created_by,
417 fstg.last_updated_by,
418 fstg.last_update_login,
419 fstg.job_name,
420 fstg.line_type,
421 fstg.scheduled_completion_date,
422 fstg.job_status_code,
423 fstg.job_start_value,
424 fstg.PROGRAM_ID,
425 fstg.PROGRAM_LOGIN_ID,
426 fstg.PROGRAM_APPLICATION_ID,
427 fstg.REQUEST_ID
428 FROM opi_dbi_jobs_stg fstg,
429 opi_dbi_muv_conv_rates conv
430 WHERE fstg.organization_id = conv.organization_id
431 AND fstg.completion_date = conv.transaction_date;
432
433 l_row_count := sql%rowcount;
434 commit;
435
436 RETURN l_row_count;
437
438 END Insert_into_Jobs_Fact;
439
440
441
442 FUNCTION Merge_into_Jobs_Fact
443 RETURN NUMBER
444 IS
445 l_row_count NUMBER;
446 BEGIN
447 MERGE INTO OPI_DBI_JOBS_F f
448 USING (
449 SELECT
450 fstg.organization_id,
451 fstg.job_id,
452 fstg.job_type,
453 fstg.status,
454 fstg.completion_date,
455 fstg.assembly_item_id,
456 fstg.start_quantity,
457 fstg.actual_qty_completed,
458 fstg.uom_code,
459 conv.conversion_rate,
460 conv.sec_conversion_rate,
461 fstg.include_job,
462 fstg.source,
463 fstg.creation_date,
464 fstg.last_update_date,
465 fstg.created_by,
466 fstg.last_updated_by,
467 fstg.last_update_login,
468 fstg.job_name,
469 fstg.line_type,
470 fstg.scheduled_completion_date,
471 fstg.job_status_code,
472 fstg.job_start_value,
473 fstg.PROGRAM_ID PROGRAM_ID,
474 fstg.PROGRAM_LOGIN_ID PROGRAM_LOGIN_ID,
475 fstg.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID,
476 fstg.REQUEST_ID REQUEST_ID
477 FROM opi_dbi_jobs_stg fstg,
478 opi_dbi_muv_conv_rates conv
479 WHERE fstg.organization_id = conv.organization_id
480 AND fstg.completion_date = conv.transaction_date ) s
481 ON ( f.Organization_id = s.Organization_id
482 and f.Job_id = s.Job_id
483 and f.Job_Type = s.Job_Type
484 and f.Assembly_Item_id = s.Assembly_Item_id
485 and f.line_type = s.line_type)
486 WHEN MATCHED THEN
487 UPDATE SET
488 f.status = s.status
489 ,f.job_name = s.job_name
490 ,f.completion_date = s.completion_date
491 ,f.start_quantity = s.start_quantity
492 ,f.actual_qty_completed = s.actual_qty_completed
493 ,f.uom_code = s.uom_code
494 ,f.conversion_rate = s.conversion_rate
495 ,f.include_job = s.include_job
496 ,std_req_flag = (CASE
497 WHEN f.Status not in ( 'Closed', 'Complete - No Charges', 'Cancelled' )
498 AND s.Status in ( 'Closed', 'Complete - No Charges', 'Cancelled' ) THEN 1
499 WHEN s.Status in ( 'Closed', 'Complete - No Charges', 'Cancelled' )
500 AND s.Actual_Qty_Completed <> f.Actual_Qty_Completed THEN 1
501 WHEN s.Status in ( 'Closed', 'Complete - No Charges', 'Cancelled' )
502 AND s.Start_Quantity <> f.Start_Quantity THEN 1
503 WHEN s.Status in ( 'Closed', 'Complete - No Charges', 'Cancelled' )
504 AND trunc(s.Completion_date) <> trunc(f.Completion_date) THEN 1
505 ELSE 0
506 END)
507 ,std_res_flag = (CASE
508 WHEN f.Status not in ( 'Closed', 'Complete - No Charges', 'Cancelled' )
509 AND s.Status in ( 'Closed', 'Complete - No Charges', 'Cancelled' ) THEN 1
510 WHEN s.Status in ( 'Closed', 'Complete - No Charges', 'Cancelled' )
511 AND s.Actual_Qty_Completed <> f.Actual_Qty_Completed THEN 1
512 WHEN s.Status in ( 'Closed', 'Complete - No Charges', 'Cancelled' )
513 AND s.Start_Quantity <> f.Start_Quantity THEN 1
514 WHEN s.Status in ( 'Closed', 'Complete - No Charges', 'Cancelled' )
515 AND trunc(s.Completion_date) <> trunc(f.Completion_date) THEN 1
516 ELSE 0
517 END)
518 ,last_Update_Date = SYSDATE
519 ,last_Updated_By = g_user_id
520 ,last_Update_Login = g_login_id
521 ,f.scheduled_completion_date = s.scheduled_completion_date
522 ,f.job_status_code = s.job_status_code
523 ,f.job_start_value = s.job_start_value
524 WHEN NOT MATCHED THEN
525 INSERT (
526 organization_id,
527 job_id,
528 job_type,
529 status,
530 completion_date,
531 assembly_item_id,
532 start_quantity,
533 actual_qty_completed,
534 uom_code,
535 conversion_rate,
536 sec_conversion_rate,
537 include_job,
538 std_req_flag,
539 std_res_flag,
540 source,
541 creation_date,
542 last_update_date,
543 created_by,
544 last_updated_by,
545 last_update_login,
546 job_name,
547 line_type,
548 scheduled_completion_date,
549 job_status_code,
550 job_start_value,
551 PROGRAM_ID,
552 PROGRAM_LOGIN_ID,
553 PROGRAM_APPLICATION_ID,
554 REQUEST_ID)
555 VALUES (
556 s.organization_id,
557 s.job_id,
558 s.job_type,
559 s.status,
560 s.completion_date,
561 s.assembly_item_id,
562 s.start_quantity,
563 s.actual_qty_completed,
564 s.uom_code,
565 s.conversion_rate,
566 s.sec_conversion_rate,
567 s.include_job,
568 1,
569 1,
570 s.source,
571 s.creation_date,
572 s.last_update_date,
573 s.created_by,
574 s.last_updated_by,
575 s.last_update_login,
576 s.job_name,
577 s.line_type,
578 s.scheduled_completion_date,
579 s.job_status_code,
580 s.job_start_value,
581 s.PROGRAM_ID,
582 s.PROGRAM_LOGIN_ID,
583 s.PROGRAM_APPLICATION_ID,
584 s.REQUEST_ID);
585
586 l_row_count := sql%rowcount;
587 commit;
588
589
590 RETURN l_row_count;
591
592 END Merge_into_Jobs_Fact;
593
594
595 PROCEDURE GET_JOBS_INITIAL_LOAD (errbuf in out NOCOPY varchar2,
596 retcode in out NOCOPY varchar2)
597 IS
598 l_stmt_num NUMBER;
599 l_row_count NUMBER;
600 l_ret_code NUMBER;
601 l_err_num NUMBER;
602 l_err_msg VARCHAR2(255);
603 l_proc_name VARCHAR2(255);
604 l_opi_schema VARCHAR2(30);
605 l_status VARCHAR2(30);
606 l_industry VARCHAR2(30);
607 l_list dbms_sql.varchar2_table;
608 l_from_date OPI.OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
609 l_to_date OPI.OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
610
611 CURSOR OPI_DBI_RUN_LOG_CURR_CSR IS
612 select
613 from_bound_date,
614 to_bound_date
615 from
616 OPI_DBI_RUN_LOG_CURR
617 where
618 ETL_ID = 4 and
619 source = 2;
620
621 BEGIN
622
623 l_proc_name := 'OPI_DBI_JOBS_PKG.GET_JOBS_INITIAL_LOAD';
624 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
625
626 l_stmt_num := 2;
627 --Calling Common Module Log
628 opi_dbi_common_mod_init_pkg.run_common_module_init(errbuf,l_ret_code);
629 retcode := to_char(l_ret_code);
630
631 -- session parameters
632 g_sysdate := SYSDATE;
633 g_user_id := nvl(fnd_global.user_id, -1);
634 g_login_id := nvl(fnd_global.login_id, -1);
635 g_program_id := nvl (fnd_global.conc_program_id, -1);
636 g_program_login_id := nvl (fnd_global.conc_login_id, -1);
637 g_program_application_id := nvl (fnd_global.prog_appl_id, -1);
638 g_request_id := nvl (fnd_global.conc_request_id, -1);
639
640 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
641 l_list(2) := 'BIS_GLOBAL_START_DATE';
642 l_list(3) := 'BIS_PRIMARY_RATE_TYPE';
643
644 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
645
646 IF BIS_COLLECTION_UTILITIES.SETUP( 'OPI_DBI_JOBS_F' ) = FALSE THEN
647 RAISE_APPLICATION_ERROR(-20000, errbuf);
648 END IF;
649
650 l_stmt_num := 8;
651 --
652 -- bug 3863905 - mv log is now dropped before initial load
653 -- we shouldnt be truncating mv log anymore
654 --
655 IF fnd_installation.get_app_info('OPI', l_status,
656 l_industry, l_opi_schema) THEN
657 execute immediate 'truncate table ' || l_opi_schema ||
658 '.OPI_DBI_JOBS_STG';
659 --execute immediate 'truncate table ' || l_opi_schema ||
660 -- '.MLOG$_OPI_DBI_JOBS_F';
661 execute immediate 'truncate table ' || l_opi_schema ||
662 '.OPI_DBI_JOBS_F PURGE MATERIALIZED VIEW LOG';
663 execute immediate 'truncate table ' || l_opi_schema ||
664 '.OPI_DBI_MUV_CONV_RATES';
665 END IF;
666
667 l_stmt_num := 10;
668 -- GSD -- already checked if it is set up
669 g_global_start_date := bis_common_parameters.get_global_start_date;
670
671 l_stmt_num := 11;
672 -- Global currency codes -- already checked if primary is set up
673 g_global_currency_code := bis_common_parameters.get_currency_code;
674 g_secondary_currency_code :=
675 bis_common_parameters.get_secondary_currency_code;
676
677 l_stmt_num := 12;
678 -- Global rate types -- already checked if primary is set up
679 g_global_rate_type := bis_common_parameters.get_rate_type;
680 g_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
681
682 l_stmt_num := 13;
683 -- check that either both the secondary rate type and secondary
684 -- rate are null, or that neither are null.
685 IF ( (g_secondary_currency_code IS NULL AND
686 g_secondary_rate_type IS NOT NULL)
687 OR (g_secondary_currency_code IS NOT NULL AND
688 g_secondary_rate_type IS NULL) ) THEN
689
690 BIS_COLLECTION_UTILITIES.PUT_LINE ('The global secondary currency code setup is incorrect. The secondary currency code cannot be null when the secondary rate type is defined and vice versa.');
691
692 RAISE_APPLICATION_ERROR(-20000, errbuf);
693
694 END IF;
695
696 --l_stmt_num := 14;
697 -- Store current sysdate as the Last Collection Date.
698 -- This one for OPI, and a later one for OPM
699 --IF (opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(4,1) = FALSE) THEN
700 -- BIS_COLLECTION_UTILITIES.put_line(
701 -- 'Failed to store current sysdate as the Last Collection Date.');
702 -- RAISE_APPLICATION_ERROR(-20000, errbuf);
703 --END IF;
704
705 l_stmt_num :=15;
706 OPEN OPI_DBI_RUN_LOG_CURR_CSR;
707 FETCH OPI_DBI_RUN_LOG_CURR_CSR INTO l_from_date,l_to_date;
708
709 l_stmt_num :=16;
710 IF (OPI_DBI_RUN_LOG_CURR_CSR%NOTFOUND) THEN
711 --{
712 RAISE NO_DATA_FOUND;
713 --}
714 END IF;
715 CLOSE OPI_DBI_RUN_LOG_CURR_CSR;
716
717 l_stmt_num := 17;
718 -- get R12 upgrade date
719 OPI_DBI_RPT_UTIL_PKG.get_inv_convergence_date(g_r12_migration_date);
720
721 l_stmt_num := 18;
722 /* Insert into Jobs Staging Table */
723
724 /* OPI Jobs master extraction into Jobs Staging Table */
725 INSERT /*+ APPEND PARALLEL(f) */
726 INTO opi_dbi_jobs_stg f (
727 organization_id,
728 job_id,
729 job_type,
730 status,
731 completion_date,
732 assembly_item_id,
733 start_quantity,
734 actual_qty_completed,
735 uom_code,
736 include_job,
737 std_req_flag,
738 std_res_flag,
739 source,
740 creation_date,
741 last_update_date,
742 created_by,
743 last_updated_by,
744 last_update_login,
745 job_name,
746 scheduled_completion_date,
747 line_type,
748 job_status_code,
749 job_start_value,
750 PROGRAM_ID,
751 PROGRAM_LOGIN_ID,
752 PROGRAM_APPLICATION_ID,
753 REQUEST_ID)
754 SELECT /*+ parallel(jobs) use_hash(msi) parallel(msi) */
755 jobs.organization_id organization_id,
756 job_id,
757 job_type,
758 job_status,
759 trunc (completion_date) completion_date,
760 assembly_item_id,
761 start_quantity,
762 actual_qty_completed,
763 msi.primary_uom_code uom_code,
764 include_job,
765 1 std_req_flag,
766 decode(job_status_code, 2, 1, 5, 1, 7, 1, 12, 1, 0) std_res_flag,
767 1 source,
768 g_sysdate creation_date,
769 g_sysdate last_update_date,
770 g_user_id created_by,
771 g_user_id last_updated_by,
772 g_login_id last_update_login,
773 job_name,
774 scheduled_completion_date,
775 line_type,
776 decode(job_type,3,decode (job_status_code,2,12,job_status_code),job_status_code),
777 job_start_value,
778 g_program_id,
779 g_program_login_id,
780 g_program_application_id,
781 g_request_id
782 FROM
783 (
784 SELECT /*+ use_hash(en) use_hash(ml1) use_hash(jobsinner)
785 parallel(en) parallel(ml1) parallel(jobsinner) */
786 en.organization_id organization_id,
787 decode (en.entity_type,
788 2, jobsinner.sch_id,
789 en.wip_entity_id) job_id,
790 decode (en.entity_type,
791 2, 2,
792 4, 3,
793 8, 5,
794 5, 5,
795 1) job_type,
796 ml1.meaning job_status,
797 jobsinner.completion_date,
798 en.primary_item_id assembly_item_id,
799 jobsinner.start_quantity start_quantity,
800 jobsinner.actual_qty_completed actual_qty_completed,
801 1 include_job,
802 decode(en.entity_type,1,en.wip_entity_name
803 ,2,jobsinner.sch_id
804 ,3,en.wip_entity_name
805 ,8,en.wip_entity_name
806 ,5,en.wip_entity_name
807 ,en.wip_entity_id) job_name,
808 jobsinner.line_type line_type,
809 jobsinner.start_quantity*itemcost.item_cost job_start_value,
810 jobsinner.scheduled_completion_date,
811 ml1.lookup_code job_status_code
812 FROM
813 (
814 SELECT /*+ use_hash(di) parallel(di) */
815 null sch_id,
816 nvl (nvl (di.date_closed, di.date_completed),
817 l_to_date) completion_date,
818 di.start_quantity start_quantity,
819 di.quantity_completed actual_qty_completed,
820 di.wip_entity_id wip_entity_id,
821 di.status_type lookup_code,
822 di.organization_id organization_id,
823 di.SCHEDULED_COMPLETION_DATE SCHEDULED_COMPLETION_DATE,
824 1 line_type
825 FROM wip_discrete_jobs di
826 WHERE di.job_type = 1 AND -- only standard jobs
827 di.status_type in (3,4,5,6,7,12,14,15) AND
828 --di.scheduled_start_date >= g_global_start_date
829 di.date_released >= g_global_start_date
830 UNION ALL
831 SELECT /*+ use_hash(re) parallel(re) */
832 re.repetitive_schedule_id sch_id,
833 nvl (nvl (re.date_closed, re.last_unit_completion_date),
834 l_to_date) completion_date,
835 re.daily_production_rate *
836 re.processing_work_days start_quantity,
837 re.quantity_completed actual_qty_completed,
838 re.wip_entity_id wip_entity_id,
839 re.status_type lookup_code,
840 re.organization_id organization_id,
841 re.last_unit_completion_date SCHEDULED_COMPLETION_DATE,
842 1 line_type
843 FROM wip_repetitive_schedules re
844 WHERE re.status_type in (3,4,5,6,7,12,14,15) AND
845 --re.first_unit_start_date >= g_global_start_date
846 re.date_released >= g_global_start_date
847 UNION ALL
848 SELECT /*+ use_hash(fl) parallel(fl) */
849 null sch_id,
850 nvl (nvl (fl.date_closed,fl.scheduled_completion_date),
851 l_to_date) completion_date,
852 fl.planned_quantity start_quantity,
853 fl.quantity_completed actual_qty_completed,
854 fl.wip_entity_id wip_entity_id,
855 fl.status lookup_code,
856 fl.organization_id organization_id,
857 fl.SCHEDULED_COMPLETION_DATE SCHEDULED_COMPLETION_DATE,
858 1 line_type
859 FROM wip_flow_schedules fl
860 WHERE /*fl.status = 2
861 AND */fl.scheduled_start_date >= g_global_start_date
862 ) jobsinner,
863 wip_entities en,
864 CST_ITEM_COSTS itemcost,
865 mfg_lookups ml1
866 WHERE ((en.entity_type in (1,2,3,5,8)
867 AND ml1.lookup_type in ('WIP_JOB_STATUS')) OR
868 (en.entity_type in (4)
869 AND ml1.lookup_type in ('WIP_FLOW_SCHEDULE_STATUS')))
870 AND jobsinner.wip_entity_id = en.wip_entity_id
871 AND ml1.lookup_code = jobsinner.lookup_code
872 AND jobsinner.organization_id = en.organization_id and
873 itemcost.cost_type_id in (1,2,5,6) and
874 itemcost.organization_id = en.organization_id and
875 itemcost.inventory_item_id = en.primary_item_id
876 ) jobs,
877 mtl_system_items_b msi
878 WHERE msi.inventory_item_id = jobs.assembly_item_id
879 AND msi.organization_id = jobs.organization_id;
880
881 l_row_count := sql%rowcount;
882 commit;
883
884 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished OPI Jobs Extraction into Staging Table: '|| l_row_count || ' rows inserted');
885
886 -- Store current sysdate as the Last Collection Date.
887 -- This one for OPM, and an earlier one for OPI
888 --IF (opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(4,2) = FALSE) THEN
889 -- BIS_COLLECTION_UTILITIES.put_line('Failed to store current sysdate as the Process Manufacturing Last Collection Date.');
890 -- RAISE_APPLICATION_ERROR(-20000, errbuf);
891 --END IF;
892
893 /********* OPM Insert *********************************/
894 INSERT /*+ APPEND PARALLEL(f) */ INTO OPI_DBI_JOBS_STG f
895 (
896 organization_id,
897 job_id,
898 job_type,
899 status,
900 completion_date,
901 assembly_item_id,
902 start_quantity,
903 actual_qty_completed,
904 uom_code,
905 conversion_rate,
906 include_job,
907 std_req_flag,
908 std_res_flag,
909 source,
910 creation_date,
911 last_update_date,
912 created_by,
913 last_updated_by,
914 last_update_login,
915 job_name,
916 line_type,
917 scheduled_completion_date,
918 job_status_code,
919 job_start_value,
920 PROGRAM_ID,
921 PROGRAM_LOGIN_ID,
922 PROGRAM_APPLICATION_ID,
923 REQUEST_ID
924 )
925 SELECT /*+ parallel(dtl) parallel(hdr) */
926 hdr.organization_id organization_id,
927 hdr.batch_id job_id,
928 4 job_type, /* process job */
929 decode (hdr.batch_status,
930 4, 'Closed',
931 3, 'Complete',
932 2, 'Released',
933 -1, 'Cancelled' ) Status, --Made change for UT2 bug fix 4721820
934 trunc (nvl (hdr.Actual_Cmplt_Date,l_to_date)) Completion_date,
935 dtl.inventory_item_id Assembly_Item_ID,
936 sum (dtl.plan_qty) start_quantity,
937 sum (dtl.actual_qty) actual_qty_completed,
938 dtl.dtl_um UOM_Code,
939 null conversion_rate,
940 decode (hdr.batch_status, 4, 1, 2) include_job, /* include closed jobs only */
941 1 std_req_flag,
942 decode (hdr.batch_status, 4, 1, -1, 1, 0) std_res_flag, -- Made change for bug 4713488
943 case when g_r12_migration_date>hdr.Actual_Cmplt_Date THEN
944 3
945 ELSE 2
946 END,
947 g_sysdate creation_date,
948 g_sysdate last_update_date,
949 g_user_id created_by,
950 g_user_id last_updated_by,
951 g_login_id last_update_login,
952 hdr.batch_no job_name,
953 dtl.line_type line_type,
954 hdr.plan_cmplt_date scheduled_completion_date,
955 decode(hdr.batch_status, 1, 1,
956 2, 3,
957 3, 4,
958 4, 12,
959 -1, 7) job_status_code,
960 sum (dtl.plan_qty) * OPI_DBI_JOBS_PKG.GET_OPM_ITEM_COST(hdr.organization_id,
961 dtl.inventory_item_id,
962 l_to_date) job_start_value,
963 g_program_id,
964 g_program_login_id,
965 g_program_application_id,
966 g_request_id
967 FROM gme_material_details dtl,
968 gme_batch_header hdr
969 WHERE
970 hdr.batch_id = dtl.batch_id
971 and dtl.line_type in (1,2) /* coproducts, by-products */
972 and batch_status in (2,3,4,-1) /* wip, completed, closed, cancelled */
973 and nvl(actual_start_date, g_global_start_date) >= g_global_start_date
974 GROUP BY
975 hdr.organization_id,
976 hdr.batch_id,
977 hdr.batch_status,
978 hdr.actual_cmplt_date,
979 hdr.plan_cmplt_date,
980 dtl.inventory_item_id,
981 dtl.dtl_um,
982 hdr.batch_no,
983 dtl.line_type,
984 hdr.plan_cmplt_date
985 ;
986
987 l_row_count := sql%rowcount;
988 commit;
989
990 BIS_COLLECTION_UTILITIES.PUT_LINE(
991 'Finished OPM Jobs Extraction into Staging Table: '||
992 l_row_count || ' rows inserted');
993
994 l_stmt_num := 20;
995 IF (Get_Conversion_Rate (errbuf, retcode) = -1) THEN
996 BIS_COLLECTION_UTILITIES.put_line('Missing currency rate.');
997 BIS_COLLECTION_UTILITIES.put_line('Please run this concurrent program again after fixing the missing currency rates.');
998 retcode := g_error;
999 return;
1000 END IF;
1001
1002 l_stmt_num := 30;
1003 /* Once Conversion Rates process finishes successfully,
1004 Merge Conversion Rates and Jobs Staging table into Jobs Fact */
1005 l_row_count := Insert_into_Jobs_Fact;
1006 BIS_COLLECTION_UTILITIES.PUT_LINE(
1007 'Finished Jobs Extraction into Fact Table: '||
1008 l_row_count || ' rows inserted');
1009
1010
1011 l_stmt_num := 35;
1012 --Store current sysdate as the Last Collection Date.
1013 --This one for OPI, and a later one for OPM
1014 IF (opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(4,1) = FALSE) THEN
1015 BIS_COLLECTION_UTILITIES.put_line(
1016 'Failed to store current sysdate as the Last Collection Date.');
1017 RAISE_APPLICATION_ERROR(-20000, errbuf);
1018 END IF;
1019
1020 l_stmt_num := 40;
1021 -- Store current sysdate as the Last Collection Date.
1022 -- This one for OPM, and an earlier one for OPI
1023 IF (opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(4,2) = FALSE) THEN
1024 BIS_COLLECTION_UTILITIES.put_line('Failed to store current sysdate as the Process Manufacturing Last Collection Date.');
1025 RAISE_APPLICATION_ERROR(-20000, errbuf);
1026 END IF;
1027
1028 BIS_COLLECTION_UTILITIES.WRAPUP(
1029 p_status => TRUE,
1030 p_count => l_row_count,
1031 p_message => 'Successfully loaded Jobs master table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
1032 );
1033
1034 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1035
1036 ELSE
1037 retcode := g_error;
1038 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Parameters are not setup.');
1039 BIS_COLLECTION_UTILITIES.PUT_LINE('Please check that the profile options: BIS_PRIMARY_CURRENCY_CODE, BIS_PRIMARY_RATE_TYPE and BIS_GLOBAL_START_DATE are setup.');
1040 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1041
1042 END IF;
1043
1044 EXCEPTION
1045
1046 WHEN OTHERS THEN
1047 rollback;
1048
1049 l_err_num := SQLCODE;
1050 l_err_msg := 'OPI_DBI_JOBS_PKG.GET_JOBS_INITIAL_LOAD ('
1051 || to_char(l_stmt_num)
1052 || '): '
1053 || substr(SQLERRM, 1,200);
1054 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOBS_PKG.GET_JOBS_INITIAL_LOAD - Error at statement ('
1055 || to_char(l_stmt_num)
1056 || ')');
1057 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
1058 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
1059 BIS_COLLECTION_UTILITIES.WRAPUP(FALSE,
1060 l_row_count,
1061 'EXCEPTION '|| l_err_num||' : '||l_err_msg
1062 );
1063 retcode := SQLCODE;
1064 errbuf := SQLERRM;
1065 RAISE_APPLICATION_ERROR(-20000, errbuf);
1066 /*please note that this api will commit!!*/
1067
1068 END GET_JOBS_INITIAL_LOAD;
1069
1070
1071
1072 PROCEDURE GET_OPI_JOBS_INCR_LOAD(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1073 IS
1074 l_stmt_num NUMBER;
1075 l_row_count NUMBER;
1076 l_err_num NUMBER;
1077 l_err_msg VARCHAR2(255);
1078
1079 l_from_date OPI.OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
1080 l_to_date OPI.OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
1081
1082 CURSOR OPI_DBI_RUN_LOG_CURR_CSR IS
1083 select
1084 from_bound_date,
1085 to_bound_date
1086 from
1087 OPI_DBI_RUN_LOG_CURR
1088 where
1089 ETL_ID = 4 and
1090 source = 2;
1091
1092 BEGIN
1093
1094 -- session parameters
1095 g_sysdate := SYSDATE;
1096 g_user_id := nvl(fnd_global.user_id, -1);
1097 g_login_id := nvl(fnd_global.login_id, -1);
1098 g_program_id := nvl (fnd_global.conc_program_id, -1);
1099 g_program_login_id := nvl (fnd_global.conc_login_id, -1);
1100 g_program_application_id := nvl (fnd_global.prog_appl_id, -1);
1101 g_request_id := nvl (fnd_global.conc_request_id, -1);
1102
1103 l_stmt_num :=3;
1104 OPEN OPI_DBI_RUN_LOG_CURR_CSR;
1105 FETCH OPI_DBI_RUN_LOG_CURR_CSR INTO l_from_date,l_to_date;
1106
1107 l_stmt_num :=7;
1108 IF (OPI_DBI_RUN_LOG_CURR_CSR%NOTFOUND) THEN
1109 --{
1110 RAISE NO_DATA_FOUND;
1111 --}
1112 END IF;
1113 CLOSE OPI_DBI_RUN_LOG_CURR_CSR;
1114
1115 /* Insert into Jobs Staging Table */
1116
1117 l_stmt_num := 10;
1118 /* OPI Jobs master extraction into Jobs Staging Table */
1119
1120 MERGE INTO OPI_DBI_JOBS_STG f USING
1121 (
1122 Select
1123 JOBS.ORGANIZATION_ID,
1124 JOB_ID,
1125 JOB_TYPE,
1126 STATUS,
1127 TRUNC(COMPLETION_DATE) COMPLETION_DATE,
1128 Assembly_Item_id,
1129 START_QUANTITY,
1130 ACTUAL_QTY_COMPLETED,
1131 MSI.PRIMARY_UOM_CODE UOM_Code,
1132 INCLUDE_JOB,
1133 1 Std_Req_Flag,
1134 1 Std_Res_Flag,
1135 1 SOURCE,
1136 g_sysdate CREATION_DATE,
1137 g_sysdate LAST_UPDATE_DATE,
1138 g_user_id CREATED_BY,
1139 g_user_id LAST_UPDATED_BY,
1140 g_login_id LAST_UPDATE_LOGIN,
1141 job_name JOB_NAME,
1142 jobs.line_type LINE_TYPE,
1143 jobs.scheduled_completion_date SCHEDULED_COMPLETION_DATE,
1144 jobs.job_status_code,
1145 jobs.start_quantity*itemcost.item_cost JOB_START_VALUE,
1146 g_program_id PROGRAM_ID,
1147 g_program_login_id PROGRAM_LOGIN_ID,
1148 g_program_application_id PROGRAM_APPLICATION_ID,
1149 g_request_id REQUEST_ID
1150 FROM
1151 (
1152 SELECT
1153 EN.ORGANIZATION_ID ORGANIZATION_ID,
1154 EN.WIP_ENTITY_ID JOB_ID,
1155 decode(en.entity_type,8,5,5,5,1) JOB_TYPE,
1156 ML1.MEANING STATUS,
1157 DI.STATUS_TYPE JOB_STATUS_CODE,
1158 NVL(NVL(DI.DATE_CLOSED,DI.date_completed),l_to_date) COMPLETION_DATE,
1159 EN.PRIMARY_ITEM_ID Assembly_Item_id,
1160 DI.START_QUANTITY START_QUANTITY,
1161 DI.QUANTITY_COMPLETED ACTUAL_QTY_COMPLETED,
1162 1 INCLUDE_JOB,
1163 DI.LAST_UPDATE_DATE,
1164 EN.WIP_ENTITY_NAME JOB_NAME,
1165 1 line_type,
1166 DI.SCHEDULED_COMPLETION_DATE SCHEDULED_COMPLETION_DATE
1167 FROM WIP_ENTITIES EN, WIP_DISCRETE_JOBS DI, MFG_LOOKUPS ML1
1168 WHERE
1169 DI.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND DI.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
1170 DI.JOB_TYPE = 1 AND -- Only Standard Jobs
1171 EN.ENTITY_TYPE IN (1,3,5,8) AND -- Discrete jobs and Closed discrete jobs
1172 ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS' AND ML1.LOOKUP_CODE = DI.STATUS_TYPE AND
1173 --DI.SCHEDULED_START_DATE >= g_global_start_date
1174 DI.date_released >= g_global_start_date
1175 UNION ALL
1176 SELECT
1177 EN.ORGANIZATION_ID ORGANIZATION_ID,
1178 RE.REPETITIVE_SCHEDULE_ID JOB_ID,
1179 2 JOB_TYPE,
1180 ML1.MEANING JOB_STATUS,
1181 RE.STATUS_TYPE JOB_STATUS_CODE,
1182 NVL(NVL(RE.DATE_CLOSED,RE.last_unit_completion_date), l_to_date) COMPLETION_DATE,
1183 EN.PRIMARY_ITEM_ID Assembly_Item_id,
1184 RE.DAILY_PRODUCTION_RATE * RE.PROCESSING_WORK_DAYS START_QUANTITY,
1185 RE.QUANTITY_COMPLETED ACTUAL_QTY_COMPLETED,
1186 1 INCLUDE_JOB,
1187 RE.LAST_UPDATE_DATE,
1188 to_char(RE.REPETITIVE_SCHEDULE_ID) JOB_NAME,
1189 1 line_type,
1190 RE.last_unit_completion_date SCHEDULED_COMPLETION_DATE
1191 FROM
1192 WIP_ENTITIES EN, WIP_REPETITIVE_SCHEDULES RE, MFG_LOOKUPS ML1
1193 WHERE
1194 RE.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND RE.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
1195 EN.ENTITY_TYPE = 2 AND -- Repetitive Schedules
1196 ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS' AND ML1.LOOKUP_CODE = RE.STATUS_TYPE AND
1197 --RE.FIRST_UNIT_START_DATE >= g_global_start_date
1198 re.date_released >= g_global_start_date
1199 UNION ALL
1200 SELECT
1201 EN.ORGANIZATION_ID ORGANIZATION_ID,
1202 EN.WIP_ENTITY_ID JOB_ID,
1203 3 JOB_TYPE,
1204 ML1.MEANING JOB_STATUS,
1205 decode(FL.STATUS,2,12,FL.STATUS) JOB_STATUS_CODE,
1206 NVL(NVL(FL.DATE_CLOSED,FL.scheduled_completion_date), l_to_date) COMPLETION_DATE,
1207 EN.PRIMARY_ITEM_ID Assembly_Item_id,
1208 FL.PLANNED_QUANTITY START_QUANTITY,
1209 FL.QUANTITY_COMPLETED ACTUAL_QTY_COMPLETED,
1210 1 INCLUDE_JOB,
1211 FL.LAST_UPDATE_DATE,
1212 to_char(EN.WIP_ENTITY_ID) JOB_NAME,
1213 1 line_type,
1214 FL.SCHEDULED_COMPLETION_DATE SCHEDULED_COMPLETION_DATE
1215 FROM
1216 WIP_ENTITIES EN, WIP_FLOW_SCHEDULES FL, MFG_LOOKUPS ML1
1217 WHERE
1218 FL.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND FL.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
1219 EN.ENTITY_TYPE = 4 AND -- Flow Schedules
1220 ML1.LOOKUP_TYPE = 'WIP_FLOW_SCHEDULE_STATUS' AND ML1.LOOKUP_CODE = FL.STATUS AND
1221 FL.SCHEDULED_START_DATE >= g_global_start_date
1222 ) JOBS,
1223 MTL_SYSTEM_ITEMS_B MSI,
1224 CST_ITEM_COSTS itemcost
1225 WHERE
1226 MSI.INVENTORY_ITEM_ID = JOBS.Assembly_Item_id AND
1227 MSI.ORGANIZATION_ID = JOBS.ORGANIZATION_ID AND
1228 itemcost.cost_type_id in (1,2,5,6) and
1229 itemcost.organization_id = jobs.organization_id and
1230 itemcost.inventory_item_id = jobs.Assembly_Item_id and
1231 ((NOT EXISTS (SELECT 'X' FROM OPI_DBI_JOBS_F WHERE JOB_ID = JOBS.JOB_ID AND JOB_TYPE = JOBS.JOB_TYPE)
1232 AND JOBS.JOB_STATUS_CODE IN (1,2,3,4,5,6,7,12,14,15)
1233 ) -- New jobs in any of the 3 statuses considered: Closed, Complete - No Charges, Cancelled
1234 OR (EXISTS (SELECT 'X' FROM OPI_DBI_JOBS_F WHERE JOB_ID = JOBS.JOB_ID AND JOB_TYPE = JOBS.JOB_TYPE) AND JOBS.LAST_UPDATE_DATE > g_last_collection_date) ) -- Jobs in Jobs Master that have been updated
1235 ) s
1236 ON (f.Organization_id = s.Organization_id
1237 and f.Job_id = s.Job_id
1238 and f.Job_Type = s.Job_Type
1239 and f.Assembly_Item_id = s.Assembly_Item_id)
1240 WHEN MATCHED THEN
1241 UPDATE SET
1242 f.Status = s.Status
1243 ,f.Completion_date = s.Completion_date
1244 ,f.Start_Quantity = s.Start_Quantity
1245 ,f.Actual_Qty_Completed = s.Actual_Qty_Completed
1246 ,f.UOM_Code = s.UOM_Code
1247 ,f.Include_Job = s.Include_Job
1248 ,f.Std_Req_Flag = s.Std_Req_Flag
1249 ,f.Std_Res_Flag = s.Std_Res_Flag
1250 ,f.Last_Update_Date = s.Last_Update_Date
1251 ,f.Last_Updated_By = s.Last_Updated_By
1252 ,f.Last_Update_Login = s.Last_Update_Login
1253 ,f.job_name = s.job_name
1254 ,f.line_type = s.line_type
1255 ,f.scheduled_completion_date = s.scheduled_completion_date
1256 ,f.job_status_code = s.job_status_code
1257 ,f.job_start_value = s.job_start_value
1258 WHEN NOT MATCHED THEN
1259 INSERT (ORGANIZATION_ID, JOB_ID, JOB_TYPE, STATUS, COMPLETION_DATE, Assembly_Item_id,
1260 START_QUANTITY, ACTUAL_QTY_COMPLETED, UOM_Code, CONVERSION_RATE, INCLUDE_JOB, Std_Req_Flag, Std_Res_Flag, SOURCE,
1261 CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, JOB_NAME,
1262 LINE_TYPE, SCHEDULED_COMPLETION_DATE, JOB_STATUS_CODE, JOB_START_VALUE,
1263 PROGRAM_ID,PROGRAM_LOGIN_ID,PROGRAM_APPLICATION_ID,REQUEST_ID)
1264 VALUES (s.ORGANIZATION_ID, s.JOB_ID, s.JOB_TYPE, s.STATUS, s.COMPLETION_DATE, s.Assembly_Item_id,
1265 s.START_QUANTITY, s.ACTUAL_QTY_COMPLETED, s.UOM_Code, null, s.INCLUDE_JOB, s.Std_Req_Flag, s.Std_Res_Flag, s.SOURCE,
1266 s.CREATION_DATE, s.LAST_UPDATE_DATE, s.CREATED_BY, s.LAST_UPDATED_BY, s.LAST_UPDATE_LOGIN, S.JOB_NAME,
1267 s.LINE_TYPE, S.SCHEDULED_COMPLETION_DATE, S.JOB_STATUS_CODE, S.JOB_START_VALUE,
1268 s.PROGRAM_ID,s.PROGRAM_LOGIN_ID,s.PROGRAM_APPLICATION_ID,s.REQUEST_ID);
1269
1270 l_row_count := sql%rowcount;
1271 commit;
1272
1273 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished OPI Jobs Extraction into Staging Table: '|| l_row_count || ' rows inserted');
1274
1275
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 rollback;
1279
1280 l_err_num := SQLCODE;
1281 l_err_msg := 'OPI_DBI_JOBS_PKG.GET_OPI_JOBS_INCR_LOAD ('
1282 || to_char(l_stmt_num)
1283 || '): '
1284 || substr(SQLERRM, 1,200);
1285 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOBS_PKG.GET_OPI_JOBS_INCR_LOAD - Error at statement ('
1286 || to_char(l_stmt_num)
1287 || ')');
1288 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
1289 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
1290 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
1291
1292 retcode := SQLCODE;
1293 errbuf := SQLERRM;
1294
1295 END GET_OPI_JOBS_INCR_LOAD;
1296
1297
1298
1299 PROCEDURE GET_OPM_JOBS_INCR_LOAD(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1300 IS
1301 l_stmt_num NUMBER;
1302 l_row_count NUMBER;
1303 l_err_num NUMBER;
1304 l_err_msg VARCHAR2(255);
1305
1306 l_from_date OPI.OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
1307 l_to_date OPI.OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
1308
1309 CURSOR OPI_DBI_RUN_LOG_CURR_CSR IS
1310 select
1311 from_bound_date,
1312 to_bound_date
1313 from
1314 OPI_DBI_RUN_LOG_CURR
1315 where
1316 ETL_ID = 4 and
1317 source = 2;
1318
1319 BEGIN
1320
1321 l_stmt_num :=3;
1322 OPEN OPI_DBI_RUN_LOG_CURR_CSR;
1323 FETCH OPI_DBI_RUN_LOG_CURR_CSR INTO l_from_date,l_to_date;
1324
1325 l_stmt_num :=7;
1326 IF (OPI_DBI_RUN_LOG_CURR_CSR%NOTFOUND) THEN
1327 --{
1328 RAISE NO_DATA_FOUND;
1329 --}
1330 END IF;
1331 CLOSE OPI_DBI_RUN_LOG_CURR_CSR;
1332
1333 l_stmt_num := 10;
1334
1335 MERGE INTO OPI_DBI_JOBS_STG f USING
1336 (
1337 select
1338 hdr.organization_id Organization_id,
1339 hdr.batch_id Job_id,
1340 4 Job_Type, /* Process Job */
1341 Decode(hdr.batch_status, 4, 'Closed',
1342 3, 'Complete',
1343 2, 'Released',
1344 -1, 'Cancelled') Status, --Change made for UT2 bug # 4723975
1345 trunc(nvl(hdr.Actual_Cmplt_Date,l_to_date)) Completion_date,
1346 dtl.inventory_item_id Assembly_Item_ID,
1347 SUM(dtl.plan_qty) Start_Quantity,
1348 SUM(dtl.actual_qty) Actual_Qty_Completed,
1349 dtl.dtl_um UOM_Code,
1350 NULL Conversion_Rate,
1351 Decode(hdr.batch_status, 4, 1, 2) Include_Job, /* include closed jobs only */
1352 1 Std_Req_Flag,
1353 decode (hdr.batch_status, 4, 1, -1, 1, 0) Std_Res_Flag,
1354 2 Source, /* OPM */
1355 g_Sysdate Creation_Date,
1356 g_Sysdate Last_Update_Date,
1357 g_user_id Created_By,
1358 g_user_id Last_Updated_By,
1359 g_login_id Last_Update_Login,
1360 hdr.batch_no JOB_NAME,
1361 dtl.line_type line_type,
1362 hdr.plan_cmplt_date scheduled_completion_date,
1363 decode(hdr.batch_status, 1, 1,
1364 2, 3,
1365 3, 4,
1366 4, 12,
1367 -1, 7) job_status_code,
1368 sum (dtl.plan_qty*GET_OPM_ITEM_COST(hdr.organization_id,
1369 dtl.inventory_item_id,
1370 l_to_date) )job_start_value,
1371 g_program_id PROGRAM_ID,
1372 g_program_login_id PROGRAM_LOGIN_ID,
1373 g_program_application_id PROGRAM_APPLICATION_ID,
1374 g_request_id REQUEST_ID
1375 from gme_material_details dtl,
1376 gme_batch_header hdr
1377 where hdr.batch_id = dtl.batch_id
1378 and dtl.line_type in (1,2) /* coproducts and by-products*/
1379 and nvl(ACTUAL_START_DATE, g_global_start_date) >= g_global_start_date
1380 and
1381 ( (NOT EXISTS (SELECT 'X' FROM OPI_DBI_JOBS_F WHERE JOB_ID = hdr.batch_ID AND JOB_TYPE = 4)
1382 and hdr.batch_status in (2,3,4,-1) /* wip, completed, closed, cancelled */
1383 )
1384 OR (EXISTS (SELECT 'X' FROM OPI_DBI_JOBS_F WHERE JOB_ID = hdr.batch_ID AND JOB_TYPE = 4)
1385 AND hdr.LAST_UPDATE_DATE > g_opm_last_collection_date) ) -- Jobs in Jobs Master that have been updated
1386 group by
1387 hdr.organization_id,
1388 hdr.batch_id,
1389 hdr.batch_status,
1390 hdr.Actual_Cmplt_Date,
1391 dtl.inventory_item_id,
1392 dtl.dtl_um,
1393 hdr.batch_no,
1394 dtl.line_type,
1395 hdr.plan_cmplt_date
1396 ) s
1397 ON (f.Organization_id = s.Organization_id
1398 and f.Job_id = s.Job_id
1399 and f.Job_Type = s.Job_Type
1400 and f.Assembly_Item_id = s.Assembly_Item_id)
1401 WHEN MATCHED THEN
1402 UPDATE SET
1403 f.Status = s.Status
1404 ,f.Completion_date = s.Completion_date
1405 ,f.Start_Quantity = s.Start_Quantity
1406 ,f.Actual_Qty_Completed = s.Actual_Qty_Completed
1407 ,f.UOM_Code = s.UOM_Code
1408 ,f.Include_Job = s.Include_Job
1409 ,f.Std_Req_Flag = s.Std_Req_Flag
1410 ,f.Std_Res_Flag = s.Std_Res_Flag
1411 ,f.Last_Update_Date = s.Last_Update_Date
1412 ,f.Last_Updated_By = s.Last_Updated_By
1413 ,f.Last_Update_Login = s.Last_Update_Login
1414 ,f.job_name = s.job_name
1415 ,f.line_type = s.line_type
1416 ,f.scheduled_completion_date = s.scheduled_completion_date
1417 ,f.job_status_code = s.job_status_code
1418 ,f.job_start_value = s.job_start_value
1419 WHEN NOT MATCHED THEN
1420 INSERT (ORGANIZATION_ID, JOB_ID, JOB_TYPE, STATUS, COMPLETION_DATE, Assembly_Item_id,
1421 START_QUANTITY, ACTUAL_QTY_COMPLETED, UOM_Code, CONVERSION_RATE, INCLUDE_JOB, Std_Req_Flag, Std_Res_Flag, SOURCE,
1422 CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, JOB_NAME, LINE_TYPE, SCHEDULED_COMPLETION_DATE,
1423 JOB_STATUS_CODE, JOB_START_VALUE, PROGRAM_ID, PROGRAM_LOGIN_ID, PROGRAM_APPLICATION_ID, REQUEST_ID)
1424 VALUES (s.ORGANIZATION_ID, s.JOB_ID, s.JOB_TYPE, s.STATUS, s.COMPLETION_DATE, s.Assembly_Item_id,
1425 s.START_QUANTITY, s.ACTUAL_QTY_COMPLETED, s.UOM_Code, null, s.INCLUDE_JOB, s.Std_Req_Flag, s.Std_Res_Flag, s.SOURCE,
1426 s.CREATION_DATE, s.LAST_UPDATE_DATE, s.CREATED_BY, s.LAST_UPDATED_BY, s.LAST_UPDATE_LOGIN, s.JOB_NAME, s.LINE_TYPE, s.SCHEDULED_COMPLETION_DATE,
1427 s.JOB_STATUS_CODE, s.JOB_START_VALUE,s.PROGRAM_ID,s.PROGRAM_LOGIN_ID,s.PROGRAM_APPLICATION_ID,s.REQUEST_ID);
1428
1429 l_row_count := sql%rowcount;
1430 commit;
1431
1432 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished OPM Jobs Extraction into Staging Table: '|| l_row_count || ' rows inserted');
1433
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 rollback;
1437
1438 l_err_num := SQLCODE;
1439 l_err_msg := 'OPI_DBI_JOBS_PKG.GET_OPM_JOBS_INCR_LOAD ('
1440 || to_char(l_stmt_num)
1441 || '): '
1442 || substr(SQLERRM, 1,200);
1443 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOBS_PKG.GET_OPM_JOBS_INCR_LOAD - Error at statement ('
1444 || to_char(l_stmt_num)
1445 || ')');
1446 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
1447 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
1448 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
1449
1450 retcode := SQLCODE;
1451 errbuf := SQLERRM;
1452
1453 END GET_OPM_JOBS_INCR_LOAD;
1454
1455
1456
1457 PROCEDURE GET_JOBS_INCR_LOAD (errbuf in out NOCOPY varchar2,
1458 retcode in out NOCOPY varchar2)
1459 IS
1460 l_stmt_num NUMBER;
1461 l_row_count NUMBER;
1462 l_err_num NUMBER;
1463 l_ret_code NUMBER;
1464 l_err_msg VARCHAR2(255);
1465 l_proc_name VARCHAR2(255);
1466 l_opi_schema VARCHAR2(30);
1467 l_status VARCHAR2(30);
1468 l_industry VARCHAR2(30);
1469 l_list dbms_sql.varchar2_table;
1470 BEGIN
1471
1472 l_proc_name := 'OPI_DBI_JOBS_PKG.GET_JOBS_INCR_LOAD';
1473 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
1474
1475 l_stmt_num := 2;
1476 --Calling Common Module Log
1477 opi_dbi_common_mod_incr_pkg.run_common_module_incr(errbuf,l_ret_code);
1478 retcode := to_char(l_ret_code);
1479
1480 -- session parameters
1481 g_sysdate := SYSDATE;
1482 g_user_id := nvl(fnd_global.user_id, -1);
1483 g_login_id := nvl(fnd_global.login_id, -1);
1484
1485 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
1486 l_list(2) := 'BIS_GLOBAL_START_DATE';
1487 l_list(3) := 'BIS_PRIMARY_RATE_TYPE';
1488
1489 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
1490
1491 IF BIS_COLLECTION_UTILITIES.SETUP( 'OPI_DBI_JOBS_F' ) = false then
1492 RAISE_APPLICATION_ERROR(-20000, errbuf);
1493 END IF;
1494
1495 l_stmt_num := 10;
1496 -- GSD -- already checked if it is set up
1497 g_global_start_date := bis_common_parameters.get_global_start_date;
1498
1499 -- Global currency codes -- already checked if primary is set up
1500 g_global_currency_code := bis_common_parameters.get_currency_code;
1501 g_secondary_currency_code :=
1502 bis_common_parameters.get_secondary_currency_code;
1503
1504 -- Global rate types -- already checked if primary is set up
1505 g_global_rate_type := bis_common_parameters.get_rate_type;
1506 g_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
1507
1508 -- check that either both the secondary rate type and secondary
1509 -- rate are null, or that neither are null.
1510 IF ( (g_secondary_currency_code IS NULL AND
1511 g_secondary_rate_type IS NOT NULL)
1512 OR (g_secondary_currency_code IS NOT NULL AND
1513 g_secondary_rate_type IS NULL) ) THEN
1514
1515 BIS_COLLECTION_UTILITIES.PUT_LINE ('The global secondary currency code setup is incorrect. The secondary currency code cannot be null when the secondary rate type is defined and vice versa.');
1516
1517 RAISE_APPLICATION_ERROR(-20000, errbuf);
1518
1519 END IF;
1520
1521 l_stmt_num := 20;
1522 BEGIN
1523 SELECT LAST_COLLECTION_DATE INTO g_last_collection_date FROM OPI_DBI_RUN_LOG_CURR
1524 WHERE ETL_ID = 4 AND SOURCE = 1;
1525 EXCEPTION
1526 WHEN NO_DATA_FOUND THEN
1527 BIS_COLLECTION_UTILITIES.put_line('Last collection date is not available. Cannot proceed.');
1528 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
1529 retcode := SQLCODE;
1530 errbuf := SQLERRM;
1531 return;
1532 END;
1533
1534 --l_stmt_num := 30;
1535 -- Store current sysdate as the Last Collection Date.
1536 -- This one for OPI, and a later one for OPM
1537 --IF (opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(4,1) = FALSE) THEN
1538 -- BIS_COLLECTION_UTILITIES.put_line('Failed to store current sysdate as the Last Collection Date.');
1539 -- RAISE_APPLICATION_ERROR(-20000, errbuf);
1540 --END IF;
1541
1542 l_stmt_num := 35;
1543 IF fnd_installation.get_app_info ('OPI', l_status,
1544 l_industry, l_opi_schema) THEN
1545 execute immediate 'truncate table ' || l_opi_schema ||
1546 '.OPI_DBI_MUV_CONV_RATES';
1547
1548 execute immediate 'truncate table ' || l_opi_schema ||
1549 '.OPI_DBI_JOBS_STG';
1550 END IF;
1551
1552 l_stmt_num := 40;
1553 GET_OPI_JOBS_INCR_LOAD(errbuf, retcode);
1554
1555 l_stmt_num := 45;
1556
1557 BEGIN
1558 SELECT LAST_COLLECTION_DATE INTO g_opm_last_collection_date FROM OPI_DBI_RUN_LOG_CURR
1559 WHERE ETL_ID = 4 AND SOURCE = 2;
1560 EXCEPTION
1561 WHEN NO_DATA_FOUND THEN
1562 BIS_COLLECTION_UTILITIES.put_line('Process Manufacturing Last collection date is not available. Cannot proceed.');
1563 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
1564 retcode := SQLCODE;
1565 errbuf := SQLERRM;
1566 return;
1567 END;
1568
1569 --l_stmt_num := 47;
1570 -- Store current sysdate as the Last Collection Date.
1571 -- This one for OPM, and an earlier one for OPI
1572 --IF (opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(4,2) = FALSE) THEN
1573 -- BIS_COLLECTION_UTILITIES.put_line('Failed to store current sysdate as the Process Manufacturing Last Collection Date.');
1574 -- RAISE_APPLICATION_ERROR(-20000, errbuf);
1575 --END IF;
1576
1577 GET_OPM_JOBS_INCR_LOAD(errbuf, retcode);
1578
1579
1580 l_stmt_num := 50;
1581 IF (Get_Conversion_Rate (errbuf, retcode) = -1) THEN
1582 BIS_COLLECTION_UTILITIES.put_line('Missing currency rate.');
1583 BIS_COLLECTION_UTILITIES.put_line('Please run this concurrent program again after fixing the missing currency rates.');
1584 retcode := g_error;
1585 return;
1586 END IF;
1587
1588
1589 l_stmt_num := 60;
1590 /* Once Conversion Rates process finishes successfully,
1591 Merge Conversion Rates and Jobs Staging table into Jobs Fact */
1592 l_row_count := Merge_into_Jobs_Fact;
1593 BIS_COLLECTION_UTILITIES.PUT_LINE(
1594 'Finished Jobs Extraction into Fact Table: '||
1595 l_row_count || ' rows inserted/updated');
1596
1597 l_stmt_num := 65;
1598 -- Store current sysdate as the Last Collection Date.
1599 -- This one for OPI, and a later one for OPM
1600 IF (opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(4,1) = FALSE) THEN
1601 BIS_COLLECTION_UTILITIES.put_line('Failed to store current sysdate as the Last Collection Date.');
1602 RAISE_APPLICATION_ERROR(-20000, errbuf);
1603 END IF;
1604
1605 l_stmt_num := 70;
1606 -- Store current sysdate as the Last Collection Date.
1607 -- This one for OPM, and an earlier one for OPI
1608 IF (opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(4,2) = FALSE) THEN
1609 BIS_COLLECTION_UTILITIES.put_line('Failed to store current sysdate as the Process Manufacturing Last Collection Date.');
1610 RAISE_APPLICATION_ERROR(-20000, errbuf);
1611 END IF;
1612
1613 BIS_COLLECTION_UTILITIES.WRAPUP(
1614 p_status => TRUE,
1615 p_count => l_row_count,
1616 p_message => 'Successfully loaded Jobs master table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
1617 );
1618
1619 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1620
1621 ELSE
1622 retcode := g_error;
1623 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Parameters are not setup.');
1624 BIS_COLLECTION_UTILITIES.PUT_LINE('Please check that the profile options: BIS_PRIMARY_CURRENCY_CODE and BIS_GLOBAL_START_DATE are setup.');
1625 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1626
1627 END IF;
1628
1629 EXCEPTION
1630 WHEN OTHERS THEN
1631 rollback;
1632
1633 l_err_num := SQLCODE;
1634 l_err_msg := 'OPI_DBI_JOBS_PKG.GET_JOBS_INCR_LOAD ('
1635 || to_char(l_stmt_num)
1636 || '): '
1637 || substr(SQLERRM, 1,200);
1638 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOBS_PKG.GET_JOBS_INCR_LOAD - Error at statement ('
1639 || to_char(l_stmt_num)
1640 || ')');
1641 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
1642 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
1643 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
1644
1645 retcode := SQLCODE;
1646 errbuf := SQLERRM;
1647 RAISE_APPLICATION_ERROR(-20000, errbuf);
1648
1649 END GET_JOBS_INCR_LOAD;
1650
1651 /*
1652 Function that is a wrapper around the GMF Cost API.
1653
1654 Parameters:
1655 l_organization_id - Organization id
1656 l_inventory_item_id - inventory item id
1657 l_txn_date - date
1658
1659 retruns unit cost
1660
1661 */
1662
1663 FUNCTION GET_OPM_ITEM_COST( l_organization_id NUMBER,
1664 l_inventory_item_id NUMBER,
1665 l_txn_date DATE)
1666 RETURN NUMBER
1667 IS
1668 x_total_cost NUMBER;
1669 x_return_status VARCHAR2(1);
1670 x_msg_count NUMBER;
1671 x_msg_data VARCHAR2(2000);
1672 x_cost_method cm_mthd_mst.cost_mthd_code%TYPE;
1673 x_cost_component_class_id cm_cmpt_mst.cost_cmpntcls_id%TYPE;
1674 x_cost_analysis_code cm_alys_mst.cost_analysis_code%TYPE;
1675 x_no_of_rows NUMBER;
1676 l_ret_value NUMBER;
1677
1678 BEGIN
1679
1680 l_ret_value := GMF_CMCOMMON.Get_Process_Item_Cost
1681 (
1682 1.0
1683 , FND_API.G_TRUE
1684 , x_return_status
1685 , x_msg_count
1686 , x_msg_data
1687 , l_inventory_item_id
1688 , l_organization_id
1689 , l_txn_date
1690 , 1
1691 , x_cost_method
1692 , x_cost_component_class_id
1693 , x_cost_analysis_code
1694 , x_total_cost
1695 , x_no_of_rows
1696 );
1697
1698 IF l_ret_value <> 1
1699 THEN
1700 return -1;
1701 ELSE
1702 return x_total_cost;
1703 END IF;
1704
1705 END GET_OPM_ITEM_COST;
1706
1707 FUNCTION GET_ODM_ITEM_COST(l_organization_id NUMBER,
1708 l_inventory_item_id NUMBER)
1709 RETURN NUMBER
1710 IS
1711 x_cost NUMBER := 0;
1712
1713 BEGIN
1714 select
1715 item_cost into x_cost
1716 from
1717 cst_item_costs
1718 where
1719 organization_id = l_organization_id and
1720 inventory_item_id = l_inventory_item_id and
1721 cost_type_id in (1,2,5,6);
1722
1723 return x_cost;
1724
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 return x_cost;
1728
1729 END GET_ODM_ITEM_COST;
1730
1731
1732 PROCEDURE REFRESH_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1733 IS
1734 l_stmt_num NUMBER;
1735 l_err_num NUMBER;
1736 l_err_msg VARCHAR2(255);
1737 BEGIN
1738
1739 l_stmt_num := 10;
1740 /* Refresh MV over Jobs fact */
1741
1742 dbms_mview.refresh('OPI_CURR_PROD_DEL_001_MV',
1743 'C',
1744 '', -- ROLLBACK_SEG
1745 TRUE, -- PUSH_DEFERRED_RPC
1746 FALSE, -- REFRESH_AFTER_ERRORS
1747 0, -- PURGE_OPTION
1748 1, -- PARALLELISM
1749 0, -- HEAP_SIZE
1750 FALSE -- ATOMIC_REFRESH
1751 );
1752
1753 BIS_COLLECTION_UTILITIES.PUT_LINE('MV over Jobs Fact Refresh finished ...');
1754
1755 EXCEPTION
1756 WHEN OTHERS THEN
1757
1758 l_err_num := SQLCODE;
1759 l_err_msg := 'OPI_DBI_JOBS_PKG.REFRESH_MV ('
1760 || to_char(l_stmt_num)
1761 || '): '
1762 || substr(SQLERRM, 1,200);
1763
1764 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOBS_PKG.REFRESH_MV - Error at statement ('
1765 || to_char(l_stmt_num)
1766 || ')');
1767
1768 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
1769 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
1770
1771 RAISE_APPLICATION_ERROR(-20000, errbuf);
1772 /*please note that this api will commit!!*/
1773
1774 END REFRESH_MV;
1775
1776 End OPI_DBI_JOBS_PKG;