[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_JOB_TXN_STG_PKG
Source
1 PACKAGE BODY OPI_DBI_JOB_TXN_STG_PKG AS
2 /*$Header: OPIDJOBTRB.pls 120.34 2006/09/20 23:58:22 asparama noship $*/
3
4 /* Non planned items have an mrp_planning_code of 6 */
5 NON_PLANNED_ITEM CONSTANT NUMBER := 6;
6
7
8 s_user_id NUMBER := nvl(fnd_global.user_id, -1);
9 s_login_id NUMBER := nvl(fnd_global.login_id, -1);
10 s_global_start_date DATE := NULL;
11 s_r12_migration_date DATE := NULL;
12 s_sysdate DATE := NULL;
13 s_program_id NUMBER:= nvl (fnd_global.conc_program_id, -1);
14 s_program_login_id NUMBER := nvl (fnd_global.conc_login_id, -1);
15 s_program_application_id NUMBER := nvl (fnd_global.prog_appl_id, -1);
16 s_request_id NUMBER := nvl (fnd_global.conc_request_id, -1);
17
18 -- currency types
19 g_global_rate_type VARCHAR2(15);
20 g_global_currency_code VARCHAR2(10);
21 g_secondary_rate_type VARCHAR2(15);
22 g_secondary_currency_code VARCHAR2(10);
23
24 -- Start date of Euro currency
25 g_euro_start_date CONSTANT DATE := to_date('01/01/1999','DD/MM/YYYY');
26
27 g_warning CONSTANT NUMBER(1) := 1;
28 g_error CONSTANT NUMBER(1) := -1;
29 g_ok CONSTANT NUMBER(1) := 0;
30 g_refresh_bmv BOOLEAN := TRUE;
31
32 /* Marker for secondary conv. rate if the primary and secondary curr codes
33 and rate types are identical. Can't be -1, -2, -3 since the FII APIs
34 return those values. */
35 C_PRI_SEC_CURR_SAME_MARKER CONSTANT NUMBER := -9999;
36
37 -- GL API returns -3 if EURO rate missing on 01-JAN-1999
38 C_EURO_MISSING_AT_START CONSTANT NUMBER := -3;
39
40
41 /*
42 Populate Conversion rates for transaction dates.
43
44 Parameters:
45 retcode - 0 on successful completion, -1 on error and 1 for warning.
46 errbuf - empty on successful completion, message on error or warning
47 returns retcode.
48 */
49
50 FUNCTION GET_OPI_JOB_TXN_CRATES (errbuf IN OUT NOCOPY VARCHAR2,retcode IN OUT NOCOPY VARCHAR2)
51 RETURN NUMBER
52 IS
53
54 CURSOR invalid_rates_exist_csr IS
55 SELECT 1
56 FROM opi_dbi_muv_conv_rates
57 WHERE (nvl (conversion_rate, -999) < 0
58 OR nvl (sec_conversion_rate, 999) < 0)
59 AND rownum < 2;
60
61 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
62
63 CURSOR get_missing_rates_c (p_pri_sec_curr_same NUMBER) IS
64 SELECT DISTINCT
65 report_order,
66 curr_code,
67 rate_type,
68 transaction_date,
69 func_currency_code
70 FROM (
71 SELECT DISTINCT
72 g_global_currency_code curr_code,
73 g_global_rate_type rate_type,
74 1 report_order, -- ordering global currency first
75 mp.organization_code,
76 decode (conv.conversion_rate,
77 C_EURO_MISSING_AT_START, g_euro_start_date,
78 conv.transaction_date) transaction_date,
79 conv.f_currency_code func_currency_code
80 FROM opi_dbi_muv_conv_rates conv,
81 mtl_parameters mp,
82 (SELECT
83 DISTINCT organization_id,
84 trunc (transaction_date) transaction_date
85 FROM OPI_DBI_JOBS_TXN_STG) to_conv
86 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
87 AND mp.organization_id = to_conv.organization_id
88 AND conv.transaction_date (+) = to_conv.transaction_date
89 AND conv.organization_id (+) = to_conv.organization_id
90 UNION ALL
91 SELECT DISTINCT
92 g_secondary_currency_code curr_code,
93 g_secondary_rate_type rate_type,
94 decode (p_pri_sec_curr_same,
95 1, 1,
96 2) report_order, --ordering secondary currency next
97 mp.organization_code,
98 decode (conv.sec_conversion_rate,
99 C_EURO_MISSING_AT_START, g_euro_start_date,
100 conv.transaction_date) transaction_date,
101 conv.f_currency_code func_currency_code
102 FROM opi_dbi_muv_conv_rates conv,
103 mtl_parameters mp,
104 (SELECT
105 DISTINCT organization_id,
106 trunc (transaction_date) transaction_date
107 FROM OPI_DBI_JOBS_TXN_STG) to_conv
108 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
109 AND mp.organization_id = to_conv.organization_id
110 AND conv.transaction_date (+) = to_conv.transaction_date
111 AND conv.organization_id (+) = to_conv.organization_id)
112 ORDER BY
113 report_order ASC,
114 transaction_date,
115 func_currency_code;
116
117 l_stmt_num NUMBER;
118 l_status VARCHAR2(30);
119 l_industry VARCHAR2(30);
120 l_opi_schema VARCHAR2(30);
121 no_currency_rate_flag NUMBER;
122
123 -- Flag to check if the primary and secondary currencies are the
124 -- same
125 l_pri_sec_curr_same NUMBER;
126
127 -- old error reporting
128 i_err_num NUMBER;
129 i_err_msg VARCHAR2(255);
130
131 BEGIN
132
133 l_stmt_num := 0;
134 -- initialization block
135 retcode := g_ok;
136 no_currency_rate_flag := 0;
137 l_pri_sec_curr_same := 0;
138
139 l_stmt_num := 20;
140 -- check if the primary and secondary currencies and rate types are
141 -- identical.
142 IF (g_global_currency_code = nvl (g_secondary_currency_code, '---') AND
143 g_global_rate_type = nvl (g_secondary_rate_type, '---') ) THEN
144 --{
145 l_pri_sec_curr_same := 1;
146 --}
147 END IF;
148
149
150 l_stmt_num := 30;
151 -- Use the fii_currency.get_global_rate_primary function to get the
152 -- conversion rate given a currency code and a date.
153 -- The function returns:
154 -- 1 for currency code of 'USD' which is the global currency
155 -- -1 for dates for which there is no currency conversion rate
156 -- -2 for unrecognized currency conversion rates
157 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
158 -- transaction_date is prior to 01-JAN-1999 (when the EUR
159 -- officially went into circulation).
160
161 -- Use the fii_currency.get_global_rate_secondary to get the secondary
162 -- global rate. If the secondary currency has not been set up,
163 -- make the rate null. If the secondary currency/rate types are the
164 -- same as the primary, don't call the API but rather use an update
165 -- statement followed by the insert.
166
167 -- By selecting distinct org and currency code from the gl_set_of_books
168 -- and hr_organization_information, take care of duplicate codes.
169 INSERT /*+ append parallel(rates) */
170 INTO opi_dbi_muv_conv_rates rates (
171 organization_id,
172 f_currency_code,
173 transaction_date,
174 conversion_rate,
175 sec_conversion_rate,
176 creation_date,
177 last_update_date,
178 created_by,
179 last_updated_by,
180 last_update_login,
181 PROGRAM_ID,
182 PROGRAM_LOGIN_ID,
183 PROGRAM_APPLICATION_ID,
184 REQUEST_ID
185 )
186 SELECT /*+ parallel (to_conv) parallel (curr_codes) */
187 to_conv.organization_id,
188 curr_codes.currency_code,
189 to_conv.transaction_date,
190 decode (curr_codes.currency_code,
191 g_global_currency_code, 1,
192 fii_currency.get_global_rate_primary (
193 curr_codes.currency_code,
194 to_conv.transaction_date) ),
195 decode (g_secondary_currency_code,
196 NULL, NULL,
197 curr_codes.currency_code, 1,
198 decode (l_pri_sec_curr_same,
199 1, C_PRI_SEC_CURR_SAME_MARKER,
200 fii_currency.get_global_rate_secondary (
201 curr_codes.currency_code,
202 to_conv.transaction_date))),
203 s_sysdate,
204 s_sysdate,
205 s_user_id,
206 s_user_id,
207 s_login_id,
208 s_program_id,
209 s_program_login_id,
210 s_program_application_id,
211 s_request_id
212 FROM
213 (SELECT
214 DISTINCT organization_id, trunc (transaction_date) transaction_date
215 FROM OPI_DBI_JOBS_TXN_STG
216 ) to_conv,
217 (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
218 parallel (hoi) parallel (gsob)*/
219 DISTINCT hoi.organization_id, gsob.currency_code
220 FROM hr_organization_information hoi,
221 gl_sets_of_books gsob
222 WHERE hoi.org_information_context = 'Accounting Information'
223 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
224 curr_codes
225 WHERE curr_codes.organization_id = to_conv.organization_id;
226
227 --Introduced commit because of append parallel in the insert stmt above.
228 commit;
229
230
231 l_stmt_num := 40;
232 -- if the primary and secondary currency codes are the same, then
233 -- update the secondary with the primary
234 IF (l_pri_sec_curr_same = 1) THEN
235 --{
236
237 UPDATE /*+ parallel (opi_dbi_muv_conv_rates) */
238 opi_dbi_muv_conv_rates
239 SET sec_conversion_rate = conversion_rate;
240
241 -- safe to commit, as before
242 commit;
243 --}
244 END IF;
245
246
247 -- report missing rate
248 l_stmt_num := 50;
249
250 OPEN invalid_rates_exist_csr;
251 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
252 IF (invalid_rates_exist_csr%FOUND) THEN
253 --{
254 -- there are missing rates - prepare to report them.
255 no_currency_rate_flag := 1;
256 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
257
258 l_stmt_num := 60;
259 FOR get_missing_rates_rec IN get_missing_rates_c (l_pri_sec_curr_same)
260 LOOP
261
262 BIS_COLLECTION_UTILITIES.writemissingrate (
263 get_missing_rates_rec.rate_type,
264 get_missing_rates_rec.func_currency_code,
265 get_missing_rates_rec.curr_code,
266 get_missing_rates_rec.transaction_date);
267
268 END LOOP;
269
270 --}
271 END IF;
272 CLOSE invalid_rates_exist_csr;
273
274
275 l_stmt_num := 70; /* check no_currency_rate_flag */
276 IF (no_currency_rate_flag = 1) THEN /* missing rate found */
277 --{
278 bis_collection_utilities.put_line('ERROR: Please setup conversion rate for all missing rates reported');
279
280 retcode := g_error;
281 --}
282 END IF;
283
284 return retcode;
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 rollback;
289 i_err_num := SQLCODE;
290 i_err_msg := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_CRATES ('
291 || to_char(l_stmt_num)
292 || '): '
293 || substr(SQLERRM, 1,200);
294
295 BIS_COLLECTION_UTILITIES.put_line('OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_CRATES - Error at statement ('
296 || to_char(l_stmt_num)
297 || ')');
298
299 BIS_COLLECTION_UTILITIES.put_line('Error Number: ' || to_char(i_err_num));
300 BIS_COLLECTION_UTILITIES.put_line('Error Message: ' || i_err_msg);
301
302 retcode := g_error;
303 return g_error;
304
305 END GET_OPI_JOB_TXN_CRATES;
306
307 /* Function to format printing of error messages */
308
309 FUNCTION err_mesg (p_mesg IN VARCHAR2,
310 p_proc_name IN VARCHAR2 DEFAULT NULL,
311 p_stmt_id IN NUMBER DEFAULT -1)
312 RETURN VARCHAR2
313 IS
314
315 l_proc_name VARCHAR2 (60);
316 l_stmt_id NUMBER;
317 l_buffer_size NUMBER;
318
319 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
320 -- as the size of the declaration. I have to put 300 here.
321 l_formatted_message VARCHAR2 (300) := NULL;
322
323 BEGIN
324
325 l_proc_name := 'err_mesg';
326 l_stmt_id := 0;
327 l_buffer_size := 300;
328
329 l_stmt_id := 10;
330 l_formatted_message := substr (('OPI_DBI_JOB_TXN_STG_PKG' || '.' || p_proc_name || ' #' ||
331 to_char (p_stmt_id) || ': ' || p_mesg),
332 1, l_buffer_size);
333
334 commit;
335
336 return l_formatted_message;
337
338 EXCEPTION
339
340 WHEN OTHERS THEN
341 -- the exception happened in the exception reporting function !!
342 -- return with ERROR.
343 l_formatted_message := substr (('C_PKG_OPI_DBI_JOB_TXN_STG_PKG' || '.' || l_proc_name ||
344 ' #' ||
345 to_char (l_stmt_id) || ': ' ||
346 SQLERRM),
347 1, l_buffer_size);
348
349 l_formatted_message := 'Error in error reporting.';
350 return l_formatted_message;
351
352 END err_mesg;
353
354 /*
355 Refresh MUV base MV
356
357 Parameters:
358 retcode - 0 on successful completion, -1 on error and 1 for warning.
359 errbuf - empty on successful completion, message on error or warning
360 p_method
361
362 */
363
364 PROCEDURE REFRESH_BASE_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2, p_method in varchar2 DEFAULT '?')
365 IS
366 l_stmt_num NUMBER;
367 l_err_num NUMBER;
368 l_err_msg VARCHAR2(255);
369 BEGIN
370
371 l_stmt_num := 10;
372 DBMS_MVIEW.REFRESH(
373 list => 'OPI_MTL_VAR_MV_F',
374 method => p_method,
375 parallelism => 0);
376
377
378 BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh of Base Materialized View finished ...');
379
380
381 EXCEPTION
382 WHEN OTHERS THEN
383
384 l_err_num := SQLCODE;
385 l_err_msg := 'OPI_DBI_JOB_TXN_STG_PKG.REFRESH_BASE_MV ('
386 || to_char(l_stmt_num)
387 || '): '
388 || substr(SQLERRM, 1,200);
389
390 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOB_TXN_STG_PKG.REFRESH_BASE_MV - Error at statement ('
391 || to_char(l_stmt_num)
392 || ')');
393
394 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
395 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
396
397 RAISE_APPLICATION_ERROR(-20000, errbuf);
398
399 END REFRESH_BASE_MV;
400
401
402 /* Procedure Populates the MMT Staging table, will be used only in the
403 initial load.
404
405 Parameters:
406 retcode - 0 on successful completion, -1 on error and 1 for warning.
407 errbuf - empty on successful completion, message on error or warning
408
409 */
410
411 PROCEDURE GET_OPI_JOB_TXN_MMT_STG(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
412 IS
413 l_stmt_num NUMBER;
414 l_row_count NUMBER;
415 l_err_num NUMBER;
416 l_err_msg VARCHAR2(255);
417 l_proc_name VARCHAR2(255);
418 l_status VARCHAR2(30);
419 l_industry VARCHAR2(30);
420 l_opi_schema VARCHAR2(30);
421
422 BEGIN
423
424 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_MMT_STG';
425
426 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
427
428 BIS_COLLECTION_UTILITIES.PUT_LINE('Extracting MMT Staging Load Start Time - ' ||
429 TO_CHAR(SYSDATE, 'hh24:mi:ss'));
430
431 /* Insert MMT data into staging */
432 l_stmt_num := 20;
433
434 INSERT /*+ APPEND parallel(stg) */ INTO OPI_DBI_JOBS_TXN_MMT_STG stg
435 (transaction_id
436 , organization_id
437 , inventory_item_id
438 , transaction_date
439 , primary_quantity
440 , transaction_source_id
441 , transaction_source_type_id
442 , transaction_action_id
443 , reason_id
444 , costed_flag
445 , process_enabled_flag
446 , creation_date
447 , last_update_date
448 , created_by
449 , last_updated_by
450 , last_update_login
451 , PROGRAM_ID
452 , PROGRAM_LOGIN_ID
453 , PROGRAM_APPLICATION_ID
454 , REQUEST_ID
455 )
456 /* For Discrete Orgs, collect between transaction id range */
457 SELECT /*+ ordered use_hash(mtp) swap_join_inputs(mtp) parallel(mmt) full(LOG) full(mmt) parallel(mtp) parallel(log)*/
458 MMT.transaction_id
459 , MMT.organization_id
460 , MMT.inventory_item_id
461 , MMT.transaction_date
462 , MMT.primary_quantity
463 , MMT.transaction_source_id
464 , MMT.transaction_source_type_id
465 , MMT.transaction_action_id
466 , MMT.reason_id
467 , MMT.costed_flag
468 , 'N'
469 , s_sysdate
470 , s_sysdate
471 , s_user_id
472 , s_user_id
473 , s_login_id
474 , s_program_id
475 , s_program_login_id
476 , s_program_application_id
477 , s_request_id
478 FROM OPI_DBI_RUN_LOG_CURR LOG
479 , MTL_MATERIAL_TRANSACTIONS MMT
480 , MTL_PARAMETERS mtp
481 WHERE 1 = 1
482 AND MMT.transaction_action_id IN (1, 27, 31, 32, 30) -- Issue, Receipt, Completion, Return,Scrap
483 AND MMT.transaction_source_type_id = 5 -- Jobs abd Schedules
484 AND MMT.ORGANiZATION_ID = mtp.organization_id
485 AND mtp.process_enabled_flag = 'N'
486 AND mmt.organization_id = LOG.organization_id
487 AND LOG.organization_id IS NOT NULL
488 AND LOG.etl_id = 1
489 AND LOG.SOURCE = 1
490 AND mmt.transaction_id >= LOG.start_txn_id
491 AND mmt.transaction_id <= LOG.next_start_txn_id
492 UNION ALL
493 /* For process orgs, collect from global start date */
494 SELECT /*+ ordered use_hash(mtp) swap_join_inputs(mtp) parallel(mmt) full(LOG) full(mmt) parallel(mtp) parallel(log)*/
495 MMT.transaction_id
496 , MMT.organization_id
497 , MMT.inventory_item_id
498 , MMT.transaction_date
499 , MMT.primary_quantity
500 , MMT.transaction_source_id
501 , MMT.transaction_source_type_id
502 , MMT.transaction_action_id
503 , MMT.reason_id
504 , MMT.costed_flag
505 , 'Y'
506 , s_sysdate
507 , s_sysdate
508 , s_user_id
509 , s_user_id
510 , s_login_id
511 , s_program_id
512 , s_program_login_id
513 , s_program_application_id
514 , s_request_id
515 FROM OPI_DBI_RUN_LOG_CURR LOG
516 , MTL_MATERIAL_TRANSACTIONS MMT
517 , MTL_PARAMETERS mtp
518 WHERE 1 = 1
519 AND MMT.transaction_action_id IN (1, 27, 31, 32, 30) -- Issue, Receipt, Completion, Return,Scrap
520 AND MMT.transaction_source_type_id = 5 -- Jobs abd Schedules
521 AND MMT.ORGANiZATION_ID = mtp.organization_id
522 AND mtp.process_enabled_flag = 'Y'
523 --AND mmt.organization_id = LOG.organization_id
524 AND LOG.organization_id IS NULL
525 AND LOG.etl_id = 1
526 AND LOG.SOURCE = 2
527 AND MMT.transaction_date >= LOG.from_bound_date;
528
529 l_row_count := sql%rowcount;
530
531 commit;
532
533 BIS_COLLECTION_UTILITIES.PUT_LINE('Extracting MMT Staging Load End Time - ' ||
534 TO_CHAR(SYSDATE, 'hh24:mi:ss'));
535 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of MMT Staging Table: '|| l_row_count || ' rows inserted');
536 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
537
538 EXCEPTION
539
540 WHEN OTHERS THEN
541
542 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
543
544 retcode := SQLCODE;
545 errbuf := SQLERRM;
546
547 END GET_OPI_JOB_TXN_MMT_STG;
548
549 /*
550 Procedure Populates the Jobs Transaction Staging table for ODM, Initial load
551 procedure
552
553 Parameters:
554 retcode - 0 on successful completion, -1 on error and 1 for warning.
555 errbuf - empty on successful completion, message on error or warning
556 */
557
558 PROCEDURE GET_OPI_JOB_TXN_ODM_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
559 IS
560 l_stmt_num NUMBER;
561 l_row_count NUMBER;
562 l_err_num NUMBER;
563 l_err_msg VARCHAR2(255);
564 l_proc_name VARCHAR2(255);
565 l_status VARCHAR2(30);
566 l_industry VARCHAR2(30);
567 l_opi_schema VARCHAR2(30);
568
569 BEGIN
570
571 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_ODM_INIT';
572
573 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
574
575 /* Insert ODM data into Staging */
576 /* mta and mmta are joined to give the transaction value and mmt gives the transaction value */
577 l_stmt_num := 20;
578 INSERT /*+ APPEND PARALLEL(STG) */
579 INTO OPI_DBI_JOBS_TXN_STG STG
580 (
581 job_id,
582 job_type,
583 organization_id,
584 assembly_item_id,
585 component_item_id,
586 uom_code,
587 line_type,
588 transaction_date,
589 primary_quantity,
590 primary_quantity_draft,
591 transaction_value_b,
592 transaction_value_draft_b,
593 scrap_reason,
594 planned_item,
595 etl_type_id,
596 source,
597 creation_date,
598 last_update_date,
599 created_by,
600 last_updated_by,
601 last_update_login,
602 PROGRAM_ID,
603 PROGRAM_LOGIN_ID,
604 PROGRAM_APPLICATION_ID,
605 REQUEST_ID
606 )
607 select
608 mmt1.JOB_ID,
609 DECODE(WE.ENTITY_TYPE,1,1,2,2,4,3,3,1,8,5,5,5,5),
610 mta1.ORGANIZATION_ID,
611 WE.PRIMARY_ITEM_ID,
612 mta1.INVENTORY_ITEM_ID,
613 mmt1.PRIMARY_UOM_CODE,
614 decode(mmt1.etl_type_id, 1, -1, 1),
615 mmt1.transaction_date,
616 mmt1.TXN_QTY * -1,
617 0,
618 mta1.BASE_TRANSACTION_VALUE,
619 0,
620 nvl(mmt1.reason_id,-1),
621 MMT1.PLANNED_ITEM,
622 MMT1.ETL_TYPE_ID,
623 1,
624 s_sysdate,
625 s_sysdate,
626 s_user_id,
627 s_user_id,
628 s_login_id,
629 s_program_id,
630 s_program_login_id,
631 s_program_application_id,
632 s_request_id
633 from
634 (
635 select
636 mta.ORGANIZATION_ID,
637 mta.INVENTORY_ITEM_ID,
638 mta.transaction_source_id,
639 mmta.REPETITIVE_SCHEDULE_ID,
640 trunc(mta.transaction_date) transaction_date,
641 mta.transaction_id,
642 decode(mmta.REPETITIVE_SCHEDULE_ID, null,
643 SUM(mta.BASE_TRANSACTION_VALUE),
644 SUM(mta.BASE_TRANSACTION_VALUE) * decode(sum(mmta.tot_primary_quantity), 0,
645 null, sum(mmta.primary_quantity) / sum(mmta.tot_primary_quantity))) BASE_TRANSACTION_VALUE
646 from
647 (select
648 mtain.ORGANIZATION_ID,
649 mtain.INVENTORY_ITEM_ID,
650 mtain.transaction_source_id,
651 mtain.transaction_id,
652 mtain.transaction_date,
653 SUM(mtain.BASE_TRANSACTION_VALUE) BASE_TRANSACTION_VALUE
654 from
655 mtl_transaction_accounts mtain,
656 OPI_DBI_RUN_LOG_CURR log
657 where
658 mtain.accounting_line_type = 7 /* WIP valuation */ and
659 mtain.transaction_source_type_id = 5 /* Job or schedule */ and
660 log.source = 1 and
661 log.etl_id = 1 and
662 mtain.organization_id = log.organization_id and
663 mtain.transaction_id >= log.Start_txn_id and
664 mtain.transaction_id < log.Next_start_txn_id
665 group by
666 mtain.ORGANIZATION_ID,
667 mtain.INVENTORY_ITEM_ID,
668 mtain.transaction_source_id,
669 mtain.transaction_id,
670 mtain.transaction_date
671 )mta, /* For repetitive schedules: An mtl txn can span across multiple repetitive schedules */
672 (
673 select
674 mmtain.organization_id,
675 mmtain.repetitive_schedule_id,
676 mmtain.transaction_id,
677 mmtain.transaction_date,
678 sum(primary_quantity) primary_quantity,
679 sum(sum(primary_quantity)) over
680 (partition by mmtain.organization_id, mmtain.transaction_id) tot_primary_quantity
681 from
682 mtl_material_txn_allocations mmtain,
683 OPI_DBI_RUN_LOG_CURR log
684 where
685 log.source = 1 and
686 log.etl_id = 1 and
687 mmtain.organization_id = log.organization_id and
688 mmtain.transaction_id >= log.Start_txn_id and
689 mmtain.transaction_id < log.Next_start_txn_id
690 group by
691 mmtain.organization_id,
692 mmtain.repetitive_schedule_id,
693 mmtain.transaction_id,
694 mmtain.transaction_date
695 )mmta
696 where
697 mta.organization_id = mmta.organization_id (+) and
698 mta.transaction_id = mmta.transaction_id (+)
699 group by
700 mta.INVENTORY_ITEM_ID,
701 mta.ORGANIZATION_ID,
702 mta.transaction_source_id,
703 mmta.REPETITIVE_SCHEDULE_ID,
704 mta.transaction_id,
705 mta.transaction_date
706 )mta1,
707 (
708 select
709 mmt.transaction_id,
710 mmt.ORGANIZATION_ID,
711 mmt.INVENTORY_ITEM_ID,
712 mmt.transaction_source_id,
713 decode(sum(mmta.primary_quantity), null, mmt.transaction_source_id,mmta.repetitive_schedule_id) JOB_ID,
714 decode(sum(mmta.primary_quantity), null, 1, 2) JOB_TYPE, -- Here 1 is for Discrete and Flow.
715 msi.PRIMARY_UOM_CODE,
716 decode(sum(mmta.primary_quantity), null, sum(mmt.primary_quantity),sum(mmta.primary_quantity)) TXN_QTY,
717 trunc(mmt.transaction_date) transaction_date,
718 mmt.reason_id,
719 decode (msi.mrp_planning_code,
720 NON_PLANNED_ITEM, 'N',
721 'Y') PLANNED_ITEM,
722 decode(mmt.transaction_action_id,1,1,
723 27,1,
724 31,2,
725 32,2,
726 30,3) ETL_TYPE_ID
727 from
728 OPI_DBI_JOBS_TXN_MMT_STG mmt,
729 mtl_material_txn_allocations mmta,
730 mtl_system_items_b msi,
731 OPI_DBI_RUN_LOG_CURR log
732 where
733 mmt.organization_id = msi.organization_id and
734 mmt.inventory_item_id = msi.inventory_item_id and
735 mmt.transaction_action_id in (1, 27,31,32,30) and -- Issue, Receipt, Completion, Return,Scrap
736 mmt.transaction_source_type_id = 5 and -- Jobs abd Schedules
737 mmt.transaction_id = mmta.transaction_id (+) and
738 mmt.organization_id = log.organization_id and
739 mmt.transaction_id >= log.Start_txn_id and
740 mmt.transaction_id < log.Next_start_txn_id and
741 log.etl_id = 1 and
742 log.source = 1
743 group by
744 mmt.ORGANIZATION_ID,
745 mmt.INVENTORY_ITEM_ID,
746 mmt.transaction_source_id,
747 mmta.repetitive_schedule_id,
748 msi.PRIMARY_UOM_CODE,
749 mmt.transaction_date,
750 mmt.transaction_id,
751 mmt.reason_id,
752 mmt.transaction_action_id,
753 msi.mrp_planning_code
754 )mmt1,
755 WIP_ENTITIES we,
756 WIP_DISCRETE_JOBS wdj
757 where
758 mta1.transaction_id = mmt1.transaction_id and
759 mta1.organization_id = mmt1.organization_id and
760 mta1.inventory_item_id = mmt1.inventory_item_id and
761 mta1.transaction_source_id = mmt1.transaction_source_id and
762 mta1.transaction_date = mmt1.transaction_date and
763 (we.ENTITY_TYPE in (1,3,4,5,8) OR (we.ENTITY_TYPE = 2 and mta1.REPETITIVE_SCHEDULE_ID = mmt1.JOB_ID)) and
764 (mmt1.TXN_QTY <> 0 or mta1.BASE_TRANSACTION_VALUE <> 0) and
765 mta1.ORGANIZATION_ID = we.ORGANIZATION_ID and
766 mta1.transaction_source_id = WE.WIP_ENTITY_ID and
767 we.PRIMARY_ITEM_ID IS NOT NULL and
768 we.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID (+) and
769 nvl (wdj.JOB_TYPE, 1) =1;
770
771 l_row_count := sql%rowcount;
772
773 commit;
774
775 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM Txn Staging Table: '|| l_row_count || ' rows inserted');
776 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
777
778
779 EXCEPTION
780
781 WHEN OTHERS THEN
782
783 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
784
785 retcode := SQLCODE;
786 errbuf := SQLERRM;
787
788 END GET_OPI_JOB_TXN_ODM_INIT;
789
790 /*
791 Procedure popultaes the Jobs Transaction Staging table for ODM, Incremental
792 load procedure
793
794 Parameters:
795 retcode - 0 on successful completion, -1 on error and 1 for warning.
796 errbuf - empty on successful completion, message on error or warning
797 */
798
799 PROCEDURE GET_OPI_JOB_TXN_ODM_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
800 IS
801 l_stmt_num NUMBER;
802 l_row_count NUMBER;
803 l_err_num NUMBER;
804 l_err_msg VARCHAR2(255);
805 l_proc_name VARCHAR2(255);
806 l_status VARCHAR2(30);
807 l_industry VARCHAR2(30);
808 l_opi_schema VARCHAR2(30);
809
810 BEGIN
811
812 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_ODM_INCR';
813
814 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
815
816 /* Insert ODM data into Staging*/
817 /* mta and mmta are joined to give the transaction value and mmt gives the transaction value */
818 l_stmt_num := 20;
819 INSERT
820 INTO OPI_DBI_JOBS_TXN_STG
821 (
822 job_id,
823 job_type,
824 organization_id,
825 assembly_item_id,
826 component_item_id,
827 uom_code,
828 line_type,
829 transaction_date,
830 primary_quantity,
831 primary_quantity_draft,
832 transaction_value_b,
833 transaction_value_draft_b,
834 scrap_reason,
835 planned_item,
836 etl_type_id,
837 source,
838 creation_date,
839 last_update_date,
840 created_by,
841 last_updated_by,
842 last_update_login,
843 PROGRAM_ID,
844 PROGRAM_LOGIN_ID,
845 PROGRAM_APPLICATION_ID,
846 REQUEST_ID
847 )
848 select
849 mmt1.JOB_ID,
850 DECODE(WE.ENTITY_TYPE,1,1,2,2,4,3,3,1,8,5,5,5,5),
851 mta1.ORGANIZATION_ID,
852 WE.PRIMARY_ITEM_ID,
853 mta1.INVENTORY_ITEM_ID,
854 mmt1.PRIMARY_UOM_CODE,
855 decode(mmt1.etl_type_id, 1, -1, 1),
856 mmt1.transaction_date,
857 mmt1.TXN_QTY * -1,
858 0,
859 mta1.BASE_TRANSACTION_VALUE,
860 0,
861 nvl(mmt1.reason_id,-1),
862 MMT1.PLANNED_ITEM,
863 MMT1.ETL_TYPE_ID,
864 1,
865 s_sysdate,
866 s_sysdate,
867 s_user_id,
868 s_user_id,
869 s_login_id,
870 s_program_id,
871 s_program_login_id,
872 s_program_application_id,
873 s_request_id
874 from
875 (
876 select
877 mta.ORGANIZATION_ID,
878 mta.INVENTORY_ITEM_ID,
879 mta.transaction_source_id,
880 mmta.REPETITIVE_SCHEDULE_ID,
881 trunc(mta.transaction_date) transaction_date,
882 mta.transaction_id transaction_id,
883 decode(mmta.REPETITIVE_SCHEDULE_ID, null,
884 SUM(mta.BASE_TRANSACTION_VALUE),
885 SUM(mta.BASE_TRANSACTION_VALUE) * decode(sum(mmta.tot_primary_quantity), 0,
886 null, sum(mmta.primary_quantity) / sum(mmta.tot_primary_quantity))) BASE_TRANSACTION_VALUE
887 from
888 (select
889 mtain.ORGANIZATION_ID,
890 mtain.INVENTORY_ITEM_ID,
891 mtain.transaction_source_id,
892 mtain.transaction_id,
893 mtain.transaction_date,
894 SUM(mtain.BASE_TRANSACTION_VALUE) BASE_TRANSACTION_VALUE
895 from
896 mtl_transaction_accounts mtain,
897 OPI_DBI_RUN_LOG_CURR log
898 where
899 mtain.accounting_line_type = 7 /* WIP valuation */ and
900 mtain.transaction_source_type_id = 5 /* Job or schedule */ and
901 log.source = 1 and
902 log.etl_id = 1 and
903 mtain.organization_id = log.organization_id and
904 mtain.transaction_id >= log.Start_txn_id and
905 mtain.transaction_id < log.Next_start_txn_id
906 group by
907 mtain.ORGANIZATION_ID,
908 mtain.INVENTORY_ITEM_ID,
909 mtain.transaction_source_id,
910 mtain.transaction_id,
911 mtain.transaction_date
912 )mta,
913 (
914 select
915 mmtain.organization_id,
916 mmtain.REPETITIVE_SCHEDULE_ID,
917 mmtain.transaction_id,
918 mmtain.transaction_date,
919 sum(primary_quantity) primary_quantity,
920 sum(sum(primary_quantity)) over
921 (partition by mmtain.organization_id, mmtain.transaction_id) tot_primary_quantity
922 from
923 mtl_material_txn_allocations mmtain,
924 OPI_DBI_RUN_LOG_CURR log
925 where
926 log.source = 1 and
927 log.etl_id = 1 and
928 mmtain.organization_id = log.organization_id and
929 mmtain.transaction_id >= log.Start_txn_id and
930 mmtain.transaction_id < log.Next_start_txn_id
931 group by
932 mmtain.organization_id,
933 mmtain.repetitive_schedule_id,
934 mmtain.transaction_id,
935 mmtain.transaction_date
936 )mmta
937 where
938 mta.organization_id = mmta.organization_id (+) and
939 mta.transaction_id = mmta.transaction_id (+)
940 group by
941 mta.INVENTORY_ITEM_ID,
942 mta.ORGANIZATION_ID,
943 mta.transaction_source_id,
944 mmta.REPETITIVE_SCHEDULE_ID,
945 mta.transaction_id,
946 mta.transaction_date
947 )mta1,
948 (
949 select
950 mmt.transaction_id,
951 mmt.ORGANIZATION_ID,
952 mmt.INVENTORY_ITEM_ID,
953 mmt.transaction_source_id,
954 decode(sum(mmta.primary_quantity), null, mmt.transaction_source_id,mmta.repetitive_schedule_id) JOB_ID,
955 decode(sum(mmta.primary_quantity), null, 1, 2) JOB_TYPE, -- Here 1 is for Discrete and Flow.
956 msi.PRIMARY_UOM_CODE,
957 decode(sum(mmta.primary_quantity), null, sum(mmt.primary_quantity),sum(mmta.primary_quantity)) TXN_QTY,
958 trunc(mmt.transaction_date) transaction_date,
959 mmt.reason_id,
960 decode (msi.mrp_planning_code,
961 NON_PLANNED_ITEM, 'N',
962 'Y') PLANNED_ITEM,
963 decode(mmt.transaction_action_id,1,1,
964 27,1,
965 31,2,
966 32,2,
967 30,3) ETL_TYPE_ID
968 from
969 MTL_MATERIAL_TRANSACTIONS mmt,
970 mtl_material_txn_allocations mmta,
971 mtl_system_items_b msi,
972 OPI_DBI_RUN_LOG_CURR log
973 where
974 mmt.organization_id = msi.organization_id and
975 mmt.inventory_item_id = msi.inventory_item_id and
976 mmt.transaction_action_id in (1, 27,31,32,30) and -- Issue, Receipt, Completion, Return,Scrap
977 mmt.transaction_source_type_id = 5 and -- Jobs abd Schedules
978 mmt.transaction_id = mmta.transaction_id (+) and
979 mmt.organization_id = log.organization_id and
980 mmt.transaction_id >= log.Start_txn_id and
981 mmt.transaction_id < log.Next_start_txn_id and
982 log.etl_id = 1 and
983 log.source = 1
984 group by
985 mmt.ORGANIZATION_ID,
986 mmt.INVENTORY_ITEM_ID,
987 mmt.transaction_source_id,
988 mmta.repetitive_schedule_id,
989 msi.PRIMARY_UOM_CODE,
990 mmt.transaction_date,
991 mmt.transaction_id,
992 mmt.reason_id,
993 mmt.transaction_action_id,
994 msi.mrp_planning_code
995 )mmt1,
996 WIP_ENTITIES we,
997 WIP_DISCRETE_JOBS wdj
998 where
999 mta1.transaction_id = mmt1.transaction_id and
1000 mta1.organization_id = mmt1.organization_id and
1001 mta1.inventory_item_id = mmt1.inventory_item_id and
1002 mta1.transaction_source_id = mmt1.transaction_source_id and
1003 mta1.transaction_date = mmt1.transaction_date and
1004 (we.ENTITY_TYPE in (1,3,4,5,8) OR (we.ENTITY_TYPE = 2 and mta1.REPETITIVE_SCHEDULE_ID = mmt1.JOB_ID)) and
1005 (mmt1.TXN_QTY <> 0 or mta1.BASE_TRANSACTION_VALUE <> 0) and
1006 mta1.ORGANIZATION_ID = we.ORGANIZATION_ID and
1007 mta1.transaction_source_id = WE.WIP_ENTITY_ID and
1008 we.PRIMARY_ITEM_ID IS NOT NULL and
1009 we.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID (+) and
1010 nvl (wdj.JOB_TYPE, 1) =1;
1011
1012 l_row_count := sql%rowcount;
1013
1014 commit;
1015
1016 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM Txn Staging Table: '|| l_row_count || ' rows inserted');
1017 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1018
1019 EXCEPTION
1020
1021 WHEN OTHERS THEN
1022
1023 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
1024
1025 retcode := SQLCODE;
1026 errbuf := SQLERRM;
1027
1028 END GET_OPI_JOB_TXN_ODM_INCR;
1029
1030 /*
1031 Procedure populates the Jobs Transactio Staging Table for OPM, Initial Load
1032 procedure
1033
1034 Parameters:
1035 retcode - 0 on successful completion, -1 on error and 1 for warning.
1036 errbuf - empty on successful completion, message on error or warning
1037 */
1038
1039 PROCEDURE GET_OPI_JOB_TXN_OPM_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1040 IS
1041 l_stmt_num NUMBER;
1042 l_row_count NUMBER;
1043 l_err_num NUMBER;
1044 l_err_msg VARCHAR2(255);
1045 l_proc_name VARCHAR2(255);
1046 l_from_date OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
1047 l_to_date OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
1048 l_status VARCHAR2(30);
1049 l_industry VARCHAR2(30);
1050 l_opi_schema VARCHAR2(30);
1051
1052 CURSOR OPI_DBI_RUN_LOG_CURR_CSR IS
1053 select
1054 from_bound_date,
1055 to_bound_date
1056 from
1057 OPI_DBI_RUN_LOG_CURR
1058 where
1059 ETL_ID = 1 and
1060 source = 2;
1061
1062 BEGIN
1063
1064 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_OPM_INIT';
1065
1066 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
1067
1068 l_stmt_num := 10;
1069 OPEN OPI_DBI_RUN_LOG_CURR_CSR;
1070 FETCH OPI_DBI_RUN_LOG_CURR_CSR INTO l_from_date,l_to_date;
1071
1072 l_stmt_num :=15;
1073 IF (OPI_DBI_RUN_LOG_CURR_CSR%NOTFOUND) THEN
1074 --{
1075 RAISE NO_DATA_FOUND;
1076 --}
1077 END IF;
1078 CLOSE OPI_DBI_RUN_LOG_CURR_CSR;
1079
1080 /* GTV is summarised and joined with MMT Staging. GTV gives the transaction value while
1081 MMT Staging gives the quantity. Join with GME_MATERIAL_DETAILS is required to get the
1082 cost alloc factor for products. */
1083 l_stmt_num := 20;
1084 INSERT
1085 INTO OPI_DBI_JOBS_TXN_STG
1086 (
1087 job_id,
1088 job_type,
1089 organization_id,
1090 assembly_item_id,
1091 component_item_id,
1092 uom_code,
1093 line_type,
1094 transaction_date,
1095 primary_quantity,
1096 primary_quantity_draft,
1097 transaction_value_b,
1098 transaction_value_draft_b,
1099 scrap_reason,
1100 planned_item,
1101 etl_type_id,
1102 source,
1103 creation_date,
1104 last_update_date,
1105 created_by,
1106 last_updated_by,
1107 last_update_login,
1108 PROGRAM_ID,
1109 PROGRAM_LOGIN_ID,
1110 PROGRAM_APPLICATION_ID,
1111 REQUEST_ID
1112 )
1113 SELECT
1114 MTL_DTL.batch_id,
1115 4,
1116 MTL_DTL.organization_id,
1117 MTL_DTL.inventory_item_id,
1118 GTV.inventory_item_id,
1119 msi.PRIMARY_UOM_CODE,
1120 GTV.line_type,
1121 GTV.transaction_date,
1122 -sum(decode(GTV.accounted_flag,'F',
1123 MMT_STG.primary_quantity*decode(GTV.line_type,1,
1124 decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
1125 2,
1126 MTL_DTL.cost_alloc,
1127 -1,
1128 MTL_DTL.cost_alloc),0)) primary_quantity,
1129 -sum(decode(GTV.accounted_flag,'D',
1130 MMT_STG.primary_quantity*decode(GTV.line_type,1,
1131 decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
1132 2,
1133 MTL_DTL.cost_alloc,
1134 -1,
1135 MTL_DTL.cost_alloc),0)) primary_quantity_draft,
1136 -sum(decode(GTV.accounted_flag,'F',
1137 GTV.txn_base_value*decode(GTV.line_type,1,
1138 decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
1139 2,
1140 MTL_DTL.cost_alloc,
1141 -1,
1142 MTL_DTL.cost_alloc),0)) transaction_value_b,
1143 -sum(decode(GTV.accounted_flag,'D',
1144 GTV.txn_base_value*decode(GTV.line_type,1,
1145 decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
1146 2,
1147 MTL_DTL.cost_alloc,
1148 -1,
1149 MTL_DTL.cost_alloc),0)) transaction_value_draft_b,
1150 -1,
1151 decode (msi.mrp_planning_code,
1152 NON_PLANNED_ITEM, 'N',
1153 'Y') PLANNED_ITEM,
1154 decode(GTV.line_type,-1,1,
1155 2,1,
1156 1,decode(gtv.inventory_item_id,mtl_dtl.inventory_item_id,2,-1))
1157 ETL_TYPE_ID,
1158 2,
1159 s_sysdate,
1160 s_sysdate,
1161 s_user_id,
1162 s_user_id,
1163 s_login_id,
1164 s_program_id,
1165 s_program_login_id,
1166 s_program_application_id,
1167 s_request_id
1168 FROM
1169 (
1170 select
1171 gtv.transaction_id,
1172 gtv.organization_id,
1173 gtv.doc_id,
1174 gtv.inventory_item_id,
1175 gtv.line_type,
1176 gtv.transaction_date,
1177 nvl(gtv.accounted_flag,'F') accounted_flag,
1178 sum(gtv.txn_base_value) txn_base_value
1179 from
1180 gmf_transaction_valuation gtv,
1181 OPI_DBI_ORG_LE_TEMP tmp
1182 where
1183 gtv.journal_line_type in ('INV') and
1184 --gtv.txn_source = 'PRODUCTION' and
1185 gtv.event_class_code = 'BATCH_MATERIAL' and
1186 gtv.transaction_date>= s_global_start_date and
1187 ( gtv.accounted_flag = 'D' OR -- All draft rows
1188 ( nvl(gtv.accounted_flag,'N') = 'N' and
1189 gtv.final_posting_date between l_from_date and l_to_date
1190 )
1191 ) and
1192 gtv.ledger_id = tmp.ledger_id and
1193 gtv.legal_entity_id = tmp.legal_entity_id and
1194 gtv.valuation_cost_type_id = tmp.valuation_cost_type_id and
1195 gtv.organization_id = tmp. organization_id
1196 group by
1197 gtv.transaction_id,
1198 gtv.organization_id,
1199 gtv.doc_id,
1200 gtv.inventory_item_id,
1201 gtv.line_type,
1202 gtv.transaction_date,
1203 gtv.accounted_flag
1204 ) GTV,
1205 GME_MATERIAL_DETAILS MTL_DTL,
1206 OPI_DBI_JOBS_TXN_MMT_STG MMT_STG,
1207 mtl_system_items_b msi
1208 where
1209 GTV.organization_id = MTL_DTL.organization_id and
1210 GTV.doc_id = MTL_DTL.batch_id and
1211 MTL_DTL.line_type = 1 and --Product
1212 GTV.transaction_id = MMT_STG.transaction_id and
1213 MMT_STG.process_enabled_flag = 'Y' and
1214 msi.organization_id = GTV.organization_id and
1215 msi.inventory_item_id = GTV.inventory_item_id
1216 group by
1217 MTL_DTL.batch_id,
1218 MTL_DTL.organization_id,
1219 MTL_DTL.inventory_item_id,
1220 GTV.inventory_item_id,
1221 msi.PRIMARY_UOM_CODE,
1222 GTV.line_type,
1223 GTV.transaction_date,
1224 msi.mrp_planning_code;
1225
1226 l_row_count := sql%rowcount;
1227
1228 commit;
1229
1230 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM Txn Staging Table: '|| l_row_count || ' rows inserted');
1231 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1232
1233 EXCEPTION
1234
1235 WHEN NO_DATA_FOUND THEN
1236
1237 BIS_COLLECTION_UTILITIES.PUT_LINE ('No rows in Log Table, Run Initial Load again');
1238 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
1239 retcode := SQLCODE;
1240 errbuf := SQLERRM;
1241
1242 WHEN OTHERS THEN
1243
1244 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
1245
1246 retcode := SQLCODE;
1247 errbuf := SQLERRM;
1248
1249 END GET_OPI_JOB_TXN_OPM_INIT;
1250
1251 /*
1252 Procedure populates the Jobs Transactio Staging Table for OPM, Incremental Load
1253 procedure
1254
1255 Parameters:
1256 retcode - 0 on successful completion, -1 on error and 1 for warning.
1257 errbuf - empty on successful completion, message on error or warning
1258 */
1259
1260 PROCEDURE GET_OPI_JOB_TXN_OPM_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1261 IS
1262 l_stmt_num NUMBER;
1263 l_row_count NUMBER;
1264 l_err_num NUMBER;
1265 l_err_msg VARCHAR2(255);
1266 l_proc_name VARCHAR2(255);
1267 l_from_date OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
1268 l_to_date OPI_DBI_RUN_LOG_CURR.FROM_BOUND_DATE%TYPE;
1269 l_status VARCHAR2(30);
1270 l_industry VARCHAR2(30);
1271 l_opi_schema VARCHAR2(30);
1272
1273 CURSOR OPI_DBI_RUN_LOG_CURR_CSR IS
1274 select
1275 from_bound_date,
1276 to_bound_date
1277 from
1278 OPI_DBI_RUN_LOG_CURR
1279 where
1280 ETL_ID = 1 and
1281 source = 2;
1282
1283 BEGIN
1284
1285 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_OPM_INCR';
1286
1287 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
1288
1289 l_stmt_num := 10;
1290 OPEN OPI_DBI_RUN_LOG_CURR_CSR;
1291 FETCH OPI_DBI_RUN_LOG_CURR_CSR INTO l_from_date,l_to_date;
1292
1293 l_stmt_num :=15;
1294 IF (OPI_DBI_RUN_LOG_CURR_CSR%NOTFOUND) THEN
1295 --{
1296 RAISE NO_DATA_FOUND;
1297 --}
1298 END IF;
1299 CLOSE OPI_DBI_RUN_LOG_CURR_CSR;
1300
1301 /* GTV is summarised and joined with MMT. GTV gives the transaction value while
1302 MMT gives the quantity. Join with GME_MATERIAL_DETAILS is required to get the
1303 cost alloc factor for products. */
1304 l_stmt_num := 20;
1305 INSERT
1306 INTO OPI_DBI_JOBS_TXN_STG
1307 (
1308 job_id,
1309 job_type,
1310 organization_id,
1311 assembly_item_id,
1312 component_item_id,
1313 uom_code,
1314 line_type,
1315 transaction_date,
1316 primary_quantity,
1317 primary_quantity_draft,
1318 transaction_value_b,
1319 transaction_value_draft_b,
1320 scrap_reason,
1321 planned_item,
1322 etl_type_id,
1323 source,
1324 creation_date,
1325 last_update_date,
1326 created_by,
1327 last_updated_by,
1328 last_update_login,
1329 PROGRAM_ID,
1330 PROGRAM_LOGIN_ID,
1331 PROGRAM_APPLICATION_ID,
1332 REQUEST_ID
1333 )
1334 SELECT
1335 MTL_DTL.batch_id,
1336 4,
1337 MTL_DTL.organization_id,
1338 MTL_DTL.inventory_item_id,
1339 GTV.inventory_item_id,
1340 msi.PRIMARY_UOM_CODE,
1341 GTV.line_type,
1342 GTV.transaction_date,
1343 -sum(decode(GTV.accounted_flag,'F',
1344 MMT_STG.primary_quantity*decode(GTV.line_type,1,
1345 decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
1346 2,
1347 MTL_DTL.cost_alloc,
1348 -1,
1349 MTL_DTL.cost_alloc),0)) primary_quantity,
1350 -sum(decode(GTV.accounted_flag,'D',
1351 MMT_STG.primary_quantity*decode(GTV.line_type,1,
1352 decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
1353 2,
1354 MTL_DTL.cost_alloc,
1355 -1,
1356 MTL_DTL.cost_alloc),0)) primary_quantity_draft,
1357 -sum(decode(GTV.accounted_flag,'F',
1358 GTV.txn_base_value*decode(GTV.line_type,1,
1359 decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
1360 2,
1361 MTL_DTL.cost_alloc,
1362 -1,
1363 MTL_DTL.cost_alloc),0)) transaction_value_b,
1364 -sum(decode(GTV.accounted_flag,'D',
1365 GTV.txn_base_value*decode(GTV.line_type,1,
1366 decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
1367 2,
1368 MTL_DTL.cost_alloc,
1369 -1,
1370 MTL_DTL.cost_alloc),0)) transaction_value_draft_b,
1371 -1,
1372 decode (msi.mrp_planning_code,
1373 NON_PLANNED_ITEM, 'N',
1374 'Y') PLANNED_ITEM,
1375 decode(GTV.line_type,-1,1,
1376 2,1,
1377 1,decode(gtv.inventory_item_id,mtl_dtl.inventory_item_id,2,-1))
1378 ETL_TYPE_ID,
1379 2,
1380 s_sysdate,
1381 s_sysdate,
1382 s_user_id,
1383 s_user_id,
1384 s_login_id,
1385 s_program_id,
1386 s_program_login_id,
1387 s_program_application_id,
1388 s_request_id
1389 FROM
1390 (
1391 select
1392 gtv.transaction_id,
1393 gtv.organization_id,
1394 gtv.doc_id,
1395 gtv.inventory_item_id,
1396 gtv.line_type,
1397 gtv.transaction_date,
1398 nvl(gtv.accounted_flag,'F') accounted_flag,
1399 sum(gtv.txn_base_value) txn_base_value
1400 from
1401 gmf_transaction_valuation gtv,
1402 OPI_DBI_ORG_LE_TEMP tmp
1403 where
1404 gtv.journal_line_type in ('INV') and
1405 --gtv.txn_source = 'PRODUCTION' and
1406 gtv.event_class_code = 'BATCH_MATERIAL' and
1407 gtv.transaction_date>= s_global_start_date and
1408 ( gtv.accounted_flag = 'D' OR -- All draft rows
1409 ( nvl(gtv.accounted_flag,'N') = 'N' and
1410 gtv.final_posting_date between l_from_date and l_to_date
1411 )
1412 ) and
1413 gtv.ledger_id = tmp.ledger_id and
1414 gtv.legal_entity_id = tmp.legal_entity_id and
1415 gtv.valuation_cost_type_id = tmp.valuation_cost_type_id and
1416 gtv.organization_id = tmp. organization_id
1417 group by
1418 gtv.transaction_id,
1419 gtv.organization_id,
1420 gtv.doc_id,
1421 gtv.inventory_item_id,
1422 gtv.line_type,
1423 gtv.transaction_date,
1424 gtv.accounted_flag
1425 ) GTV,
1426 GME_MATERIAL_DETAILS MTL_DTL,
1427 MTL_MATERIAL_TRANSACTIONS MMT_STG,
1428 mtl_system_items_b msi
1429 where
1430 GTV.organization_id = MTL_DTL.organization_id and
1431 GTV.doc_id = MTL_DTL.batch_id and
1432 MTL_DTL.line_type = 1 and -- Products
1433 GTV.transaction_id = MMT_STG.transaction_id and
1434 --MMT_STG.process_enabled_flag = 'Y' and
1435 msi.organization_id = GTV.organization_id and
1436 msi.inventory_item_id = GTV.inventory_item_id
1437 group by
1438 MTL_DTL.batch_id,
1439 MTL_DTL.organization_id,
1440 MTL_DTL.inventory_item_id,
1441 GTV.inventory_item_id,
1442 msi.PRIMARY_UOM_CODE,
1443 GTV.line_type,
1444 GTV.transaction_date,
1445 msi.mrp_planning_code;
1446
1447 l_row_count := sql%rowcount;
1448
1449 commit;
1450
1451 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM Txn Staging Table: '|| l_row_count || ' rows inserted');
1452 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1453
1454 EXCEPTION
1455
1456 WHEN OTHERS THEN
1457
1458 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
1459
1460 retcode := SQLCODE;
1461 errbuf := SQLERRM;
1462
1463 END GET_OPI_JOB_TXN_OPM_INCR;
1464
1465 /*
1466 Procedure populates the Jobs Transactio Staging Table for Pre R12 OPM, Initial Load
1467 procedure called only if the GSD < R12 Upgrade date
1468
1469 Parameters:
1470 retcode - 0 on successful completion, -1 on error and 1 for warning.
1471 errbuf - empty on successful completion, message on error or warning
1472 */
1473
1474 PROCEDURE GET_OPI_JOB_TXN_PR12OPM_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1475 IS
1476 l_stmt_num NUMBER;
1477 l_row_count NUMBER;
1478 l_err_num NUMBER;
1479 l_err_msg VARCHAR2(255);
1480 l_proc_name VARCHAR2(255);
1481 l_status VARCHAR2(30);
1482 l_industry VARCHAR2(30);
1483 l_opi_schema VARCHAR2(30);
1484
1485 BEGIN
1486
1487 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_PR12OPM_INIT';
1488
1489 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
1490
1491 /* Inserting Pre R12 OPM MU Actuals to Jobs Transaction Staging */
1492 l_stmt_num := 10;
1493 INSERT
1494 INTO OPI_DBI_JOBS_TXN_STG
1495 (
1496 job_id,
1497 job_type,
1498 organization_id,
1499 assembly_item_id,
1500 component_item_id,
1501 uom_code,
1502 line_type,
1503 transaction_date,
1504 primary_quantity,
1505 primary_quantity_draft,
1506 transaction_value_b,
1507 transaction_value_draft_b,
1508 scrap_reason,
1509 planned_item,
1510 etl_type_id,
1511 source,
1512 creation_date,
1513 last_update_date,
1514 created_by,
1515 last_updated_by,
1516 last_update_login,
1517 PROGRAM_ID,
1518 PROGRAM_LOGIN_ID,
1519 PROGRAM_APPLICATION_ID,
1520 REQUEST_ID
1521 )
1522 select
1523 scaled.batch_id,
1524 scaled.job_type,
1525 scaled.Organization_Id,
1526 scaled.coproduct_id,
1527 scaled.item_id,
1528 scaled.item_um,
1529 scaled.line_type,
1530 itp.trans_date,
1531 sum(itp.trans_qty * coprod.cost_alloc),
1532 0,
1533 -sum(led.amount_base * coprod.cost_alloc),
1534 0,
1535 null,
1536 null,
1537 1,
1538 3,
1539 s_sysdate,
1540 s_sysdate,
1541 s_user_id,
1542 s_user_id,
1543 s_login_id,
1544 s_program_id,
1545 s_program_login_id,
1546 s_program_application_id,
1547 s_request_id
1548 from
1549 OPI_DBI_OPM_SCALED_MTL scaled,
1550 gme_material_details coprod,
1551 gl_subr_led led,
1552 (
1553 SELECT
1554 jobs.Organization_id,
1555 jobs.Job_Id,
1556 jobs.Job_Type,
1557 jobs.Assembly_Item_id,
1558 itp.trans_qty,
1559 itp.doc_type,
1560 itp.doc_id,
1561 itp.line_id,
1562 itp.trans_date,
1563 itp.line_type
1564 FROM
1565 OPI_DBI_JOBS_F jobs,
1566 IC_TRAN_PND itp
1567 WHERE
1568 jobs.source = 3 AND
1569 itp.completed_ind = 1 AND
1570 itp.doc_type = 'PROD' AND
1571 itp.doc_id = jobs.job_id
1572 GROUP BY
1573 jobs.Organization_id,
1574 jobs.Job_Id,
1575 jobs.Job_Type,
1576 jobs.Assembly_Item_id,
1577 doc_type,
1578 doc_id,
1579 line_id,
1580 trans_date,
1581 trans_qty,
1582 itp.line_type
1583 )itp
1584 where
1585 coprod.line_type in (1) and
1586 scaled.line_type in (-1, 2) and
1587 coprod.organization_id = scaled.organization_id and
1588 coprod.batch_id = scaled.batch_id and
1589 coprod.inventory_item_id = scaled.coproduct_id and
1590 itp.organization_id = scaled.organization_id and
1591 itp.job_id = scaled.batch_id and
1592 itp.assembly_item_id = scaled.coproduct_id and
1593 led.doc_id = itp.job_id and
1594 led.line_id = itp.line_id and
1595 led.doc_type = 'PROD' and
1596 led.acct_ttl_type = 1500 and
1597 led.sub_event_type in (50010,50040)
1598 group by
1599 scaled.Organization_Id,
1600 scaled.batch_id,
1601 scaled.job_type,
1602 scaled.coproduct_id,
1603 scaled.item_id,
1604 scaled.item_um,
1605 scaled.line_type,
1606 itp.trans_date;
1607
1608 /* Inserting Pre R12 OPM WIP Completions to Jobs Transaction Staging */
1609 l_stmt_num := 20;
1610 INSERT
1611 INTO OPI_DBI_JOBS_TXN_STG
1612 (
1613 job_id,
1614 job_type,
1615 organization_id,
1616 assembly_item_id,
1617 component_item_id,
1618 uom_code,
1619 line_type,
1620 transaction_date,
1621 primary_quantity,
1622 primary_quantity_draft,
1623 transaction_value_b,
1624 transaction_value_draft_b,
1625 scrap_reason,
1626 planned_item,
1627 etl_type_id,
1628 source,
1629 creation_date,
1630 last_update_date,
1631 created_by,
1632 last_updated_by,
1633 last_update_login,
1634 PROGRAM_ID,
1635 PROGRAM_LOGIN_ID,
1636 PROGRAM_APPLICATION_ID,
1637 REQUEST_ID
1638 )
1639 SELECT
1640 itp.doc_id job_id,
1641 4,
1642 mtl_dtl.organization_id,
1643 mtl_dtl.inventory_item_id,
1644 mtl_dtl.inventory_item_id,
1645 mtl_dtl.dtl_um,
1646 mtl_dtl.line_type,
1647 led.gl_trans_date,
1648 -sum (itp.trans_qty),
1649 0,
1650 -sum (led.amount_base),
1651 0,
1652 null,
1653 decode (msi.mrp_planning_code,NON_PLANNED_ITEM,
1654 'N',
1655 'Y') PLANNED_ITEM,
1656
1657 2,
1658 3,
1659 s_sysdate,
1660 s_sysdate,
1661 s_user_id,
1662 s_user_id,
1663 s_login_id,
1664 s_program_id,
1665 s_program_login_id,
1666 s_program_application_id,
1667 s_request_id
1668 FROM
1669 (SELECT
1670 doc_type,
1671 doc_id,
1672 line_id,
1673 TRUNC(trans_date) trans_date,
1674 orgn_code,
1675 item_id,
1676 SUM(trans_qty) trans_qty
1677 FROM
1678 ic_tran_pnd
1679 WHERE
1680 doc_type = 'PROD' AND
1681 line_type IN (1,2) AND
1682 completed_ind = 1 AND
1683 gl_posted_ind = 1 AND
1684 trans_date >= s_global_start_date
1685 GROUP BY
1686 doc_type,
1687 doc_id,
1688 line_id,
1689 TRUNC(trans_date),
1690 orgn_code,
1691 item_id
1692 )itp,
1693 (SELECT
1694 sub.doc_type,
1695 sub.doc_id,
1696 sub.line_id,
1697 TRUNC(sub.gl_trans_date) gl_trans_date,
1698 SUM(sub.amount_base * sub.debit_credit_sign) amount_base
1699 FROM
1700 gl_subr_led sub
1701 WHERE
1702 sub.gl_trans_date >= s_global_start_date AND
1703 sub.acct_ttl_type = 1500 AND
1704 sub.doc_type = 'PROD'
1705 GROUP BY
1706 sub.doc_type,
1707 sub.doc_id,
1708 sub.line_id,
1709 TRUNC(sub.gl_trans_date)
1710 ) led,
1711 GME_MATERIAL_DETAILS mtl_dtl,
1712 mtl_system_items_b msi
1713 WHERE
1714 itp.doc_type = led.doc_type AND
1715 itp.doc_id = led.doc_id AND
1716 itp.line_id = led.line_id AND
1717 itp.trans_date = led.gl_trans_date AND
1718 mtl_dtl.batch_id = itp.doc_id AND
1719 mtl_dtl.material_detail_id = itp.line_id AND
1720 msi.inventory_item_id = mtl_dtl.inventory_item_id AND
1721 msi.organization_id = mtl_dtl.organization_id
1722 GROUP BY
1723 mtl_dtl.organization_id,
1724 itp.doc_id,
1725 mtl_dtl.inventory_item_id,
1726 mtl_dtl.dtl_um,
1727 mtl_dtl.line_type,
1728 led.gl_trans_date,
1729 msi.mrp_planning_code;
1730
1731 l_row_count := sql%rowcount;
1732
1733 commit;
1734
1735 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction Pre R12 OPM to Jobs Txn Staging: '|| l_row_count || ' rows inserted');
1736 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1737
1738 EXCEPTION
1739
1740 WHEN OTHERS THEN
1741
1742 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
1743
1744 retcode := SQLCODE;
1745 errbuf := SQLERRM;
1746
1747 END GET_OPI_JOB_TXN_PR12OPM_INIT;
1748
1749 /*
1750 Procedure populates the Material Usage Actuals Fact from Jobs Transaction Staging for OPM and ODM, Initial
1751 Load procedure
1752
1753 Parameters:
1754 retcode - 0 on successful completion, -1 on error and 1 for warning.
1755 errbuf - empty on successful completion, message on error or warning
1756 */
1757
1758 PROCEDURE GET_OPI_MTL_USAGE_ACT_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1759 IS
1760 l_stmt_num NUMBER;
1761 l_row_count NUMBER;
1762 l_err_num NUMBER;
1763 l_err_msg VARCHAR2(255);
1764 l_proc_name VARCHAR2(255);
1765 l_status VARCHAR2(30);
1766 l_industry VARCHAR2(30);
1767 l_opi_schema VARCHAR2(30);
1768
1769 BEGIN
1770
1771 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_MTL_USAGE_ACT_INIT';
1772
1773 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
1774
1775 /* Extraction of MTL Usage Actuals fact */
1776 l_stmt_num := 20;
1777 INSERT
1778 INTO OPI_DBI_JOB_MTL_DETAILS_F
1779 (
1780 organization_id,
1781 job_id,
1782 job_type,
1783 assembly_item_id,
1784 component_item_id,
1785 uom_code,
1786 line_type,
1787 transaction_date,
1788 standard_value_b,
1789 actual_value_b,
1790 actual_value_draft_b,
1791 standard_quantity,
1792 actual_quantity,
1793 actual_quantity_draft,
1794 source,
1795 creation_date,
1796 last_update_date,
1797 created_by,
1798 last_updated_by,
1799 last_update_login,
1800 actual_value_g,
1801 actual_value_draft_g,
1802 actual_value_sg,
1803 actual_value_draft_sg,
1804 PROGRAM_ID,
1805 PROGRAM_LOGIN_ID,
1806 PROGRAM_APPLICATION_ID,
1807 REQUEST_ID
1808 )
1809 select
1810 jobs_txn.organization_id,
1811 job_id,
1812 job_type,
1813 assembly_item_id,
1814 component_item_id,
1815 uom_code,
1816 line_type,
1817 trunc(jobs_txn.transaction_date),
1818 0,
1819 sum(transaction_value_b+transaction_value_draft_b),
1820 sum(transaction_value_draft_b),
1821 0, /* This fact will no more hold Stabdard Value and Standard Qty and hence 0 */
1822 sum(primary_quantity+primary_quantity_draft),
1823 sum(primary_quantity_draft),
1824 source,
1825 s_sysdate,
1826 s_sysdate,
1827 s_user_id,
1828 s_user_id,
1829 s_login_id,
1830 sum((transaction_value_b+transaction_value_draft_b)*crates.conversion_rate),
1831 sum(transaction_value_draft_b*crates.conversion_rate),
1832 sum((transaction_value_b+transaction_value_draft_b)*crates.sec_conversion_rate),
1833 sum(transaction_value_draft_b*crates.sec_conversion_rate),
1834 s_program_id,
1835 s_program_login_id,
1836 s_program_application_id,
1837 s_request_id
1838 from
1839 OPI_DBI_JOBS_TXN_STG jobs_txn,
1840 opi_dbi_muv_conv_rates crates
1841 where
1842 etl_type_id = 1 and
1843 crates.organization_id = jobs_txn.organization_id and
1844 trunc(jobs_txn.transaction_date) = crates.transaction_date
1845 group by
1846 jobs_txn.organization_id,
1847 job_id,
1848 job_type,
1849 assembly_item_id,
1850 component_item_id,
1851 uom_code,
1852 line_type,
1853 trunc(jobs_txn.transaction_date),
1854 source;
1855
1856 l_row_count := sql%rowcount;
1857
1858 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of MTL USAGE ACTUALS Table: '|| l_row_count || ' rows inserted');
1859 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1860
1861 EXCEPTION
1862
1863 WHEN OTHERS THEN
1864
1865 rollback;
1866
1867 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
1868
1869 retcode := SQLCODE;
1870 errbuf := SQLERRM;
1871
1872 END GET_OPI_MTL_USAGE_ACT_INIT;
1873
1874 /*
1875 Procedure Merges the Material Usage Fact from the Jobs Transaction Staging table for OPM and ODM,
1876 Incremental Load procedure
1877
1878 Parameters:
1879 retcode - 0 on successful completion, -1 on error and 1 for warning.
1880 errbuf - empty on successful completion, message on error or warning
1881 */
1882
1883 PROCEDURE GET_OPI_MTL_USAGE_ACT_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1884 IS
1885 l_stmt_num NUMBER;
1886 l_row_count NUMBER;
1887 l_err_num NUMBER;
1888 l_err_msg VARCHAR2(255);
1889 l_proc_name VARCHAR2(255);
1890 l_status VARCHAR2(30);
1891 l_industry VARCHAR2(30);
1892 l_opi_schema VARCHAR2(30);
1893
1894 BEGIN
1895
1896 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_MTL_USAGE_ACT_INCR';
1897
1898 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
1899
1900 /* Extraction of MTL Usage Actuals fact */
1901 l_stmt_num := 20;
1902 MERGE
1903 INTO OPI_DBI_JOB_MTL_DETAILS_F fact USING
1904 (
1905 select
1906 jobs_txn.organization_id organization_id,
1907 job_id job_id,
1908 job_type job_type,
1909 assembly_item_id assembly_item_id,
1910 component_item_id component_item_id,
1911 uom_code uom_code,
1912 line_type line_type,
1913 trunc(jobs_txn.transaction_date) transaction_date,
1914 0 standard_value_b,
1915 sum(transaction_value_b+transaction_value_draft_b) actual_value_b,
1916 sum(transaction_value_draft_b) actual_value_draft_b,
1917 0 standard_quantity,
1918 sum(primary_quantity+primary_quantity_draft) actual_quantity,
1919 sum(primary_quantity_draft) actual_quantity_draft,
1920 source source,
1921 s_sysdate creation_date,
1922 s_sysdate last_update_date,
1923 s_user_id created_by,
1924 s_user_id last_updated_by,
1925 s_login_id last_update_login,
1926 sum((transaction_value_b+transaction_value_draft_b)*crates.conversion_rate) actual_value_g,
1927 sum(transaction_value_draft_b*crates.conversion_rate) actual_value_draft_g,
1928 sum((transaction_value_b+transaction_value_draft_b)*crates.sec_conversion_rate) actual_value_sg,
1929 sum(transaction_value_draft_b*crates.sec_conversion_rate) actual_value_draft_sg,
1930 s_program_id PROGRAM_ID,
1931 s_program_login_id PROGRAM_LOGIN_ID,
1932 s_program_application_id PROGRAM_APPLICATION_ID,
1933 s_request_id REQUEST_ID
1934 from
1935 OPI_DBI_JOBS_TXN_STG jobs_txn,
1936 opi_dbi_muv_conv_rates crates
1937 where
1938 etl_type_id = 1 and
1939 crates.organization_id = jobs_txn.organization_id and
1940 trunc(jobs_txn.transaction_date) = crates.transaction_date
1941 group by
1942 jobs_txn.organization_id,
1943 job_id,
1944 job_type,
1945 assembly_item_id,
1946 component_item_id,
1947 uom_code,
1948 line_type,
1949 trunc(jobs_txn.transaction_date),
1950 source
1951 )stg
1952 ON
1953 (
1954 fact.organization_id = stg.organization_id and
1955 fact.job_id = stg.job_id and
1956 fact.job_type = stg.job_type and
1957 fact.assembly_item_id = stg.assembly_item_id and
1958 fact.component_item_id = stg.component_item_id and
1959 fact.line_type = stg.line_type and
1960 fact.transaction_date = stg.transaction_date and
1961 fact.uom_code = stg.uom_code and
1962 fact.source = stg.source
1963 )
1964 WHEN MATCHED THEN
1965 UPDATE SET
1966 fact.actual_quantity = fact.actual_quantity + stg.actual_quantity - fact.actual_quantity_draft,
1967 fact.actual_quantity_draft = stg.actual_quantity_draft,
1968 fact.actual_value_b = fact.actual_value_b + stg.actual_value_b - fact.actual_value_draft_b,
1969 fact.actual_value_draft_b = stg.actual_value_draft_b,
1970 fact.actual_value_g = fact.actual_value_g + stg.actual_value_g - fact.actual_value_draft_g,
1971 fact.actual_value_draft_g = stg.actual_value_draft_g,
1972 fact.actual_value_sg = fact.actual_value_sg + stg.actual_value_b - fact.actual_value_draft_sg,
1973 fact.actual_value_draft_sg = stg.actual_value_draft_sg,
1974 fact.creation_date = stg.creation_date,
1975 fact.last_update_date = stg.last_update_date,
1976 fact.created_by = stg.created_by,
1977 fact.last_updated_by = stg.last_updated_by,
1978 fact.last_update_login = stg.last_update_login,
1979 fact.PROGRAM_ID = stg.PROGRAM_ID,
1980 fact.PROGRAM_LOGIN_ID = stg.PROGRAM_LOGIN_ID,
1981 fact.PROGRAM_APPLICATION_ID = stg.PROGRAM_APPLICATION_ID,
1982 fact.REQUEST_ID = stg.REQUEST_ID
1983 WHEN NOT MATCHED THEN
1984 INSERT(
1985 organization_id,
1986 job_id,
1987 job_type,
1988 assembly_item_id,
1989 component_item_id,
1990 uom_code,
1991 line_type,
1992 transaction_date,
1993 standard_value_b,
1994 actual_value_b,
1995 actual_value_draft_b,
1996 standard_quantity,
1997 actual_quantity,
1998 actual_quantity_draft,
1999 source,
2000 creation_date,
2001 last_update_date,
2002 created_by,
2003 last_updated_by,
2004 last_update_login,
2005 actual_value_g,
2006 actual_value_draft_g,
2007 actual_value_sg,
2008 actual_value_draft_sg,
2009 PROGRAM_ID,
2010 PROGRAM_LOGIN_ID,
2011 PROGRAM_APPLICATION_ID,
2012 REQUEST_ID
2013 )
2014 VALUES
2015 (
2016 stg.organization_id,
2017 stg.job_id,
2018 stg.job_type,
2019 stg.assembly_item_id,
2020 stg.component_item_id,
2021 stg.uom_code,
2022 stg.line_type,
2023 stg.transaction_date,
2024 stg.standard_value_b,
2025 stg.actual_value_b,
2026 stg.actual_value_draft_b,
2027 stg.standard_quantity,
2028 stg.actual_quantity,
2029 stg.actual_quantity_draft,
2030 stg.source,
2031 stg.creation_date,
2032 stg.last_update_date,
2033 stg.created_by,
2034 stg.last_updated_by,
2035 stg.last_update_login,
2036 stg.actual_value_g,
2037 stg.actual_value_draft_g,
2038 stg.actual_value_sg,
2039 stg.actual_value_draft_sg,
2040 stg.PROGRAM_ID,
2041 stg.PROGRAM_LOGIN_ID,
2042 stg.PROGRAM_APPLICATION_ID,
2043 stg.REQUEST_ID
2044 );
2045
2046 l_row_count := sql%rowcount;
2047
2048 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of MTL USAGE ACTUALS Table: '|| l_row_count || ' rows inserted/updated');
2049 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2050
2051
2052 EXCEPTION
2053
2054 WHEN OTHERS THEN
2055
2056 rollback;
2057
2058 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
2059
2060 retcode := SQLCODE;
2061 errbuf := SQLERRM;
2062
2063 END GET_OPI_MTL_USAGE_ACT_INCR;
2064
2065 /*
2066 Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact
2067 for ODM, Initial Load procedure
2068
2069 Parameters:
2070 retcode - 0 on successful completion, -1 on error and 1 for warning.
2071 errbuf - empty on successful completion, message on error or warning
2072 */
2073
2074 PROCEDURE GET_OPI_ODM_MTL_USAGE_STD_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
2075 IS
2076 l_stmt_num NUMBER;
2077 l_row_count NUMBER;
2078 l_err_num NUMBER;
2079 l_err_msg VARCHAR2(255);
2080 l_proc_name VARCHAR2(255);
2081 l_status VARCHAR2(30);
2082 l_industry VARCHAR2(30);
2083 l_opi_schema VARCHAR2(30);
2084
2085 BEGIN
2086
2087 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_ODM_MTL_USAGE_STD_INIT';
2088
2089 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2090
2091 /* ODM insert into temp table */
2092 l_stmt_num := 20;
2093 INSERT
2094 INTO OPI_DBI_JOB_MTL_STD_QTY_TMP
2095 (ORGANIZATION_ID,
2096 INVENTORY_ITEM_ID,
2097 JOB_ID,
2098 JOB_TYPE,
2099 Standard_Quantity
2100 )
2101 SELECT
2102 ORGANIZATION_ID,
2103 INVENTORY_ITEM_ID,
2104 JOB_ID,
2105 JOB_TYPE,
2106 Standard_Quantity
2107 FROM
2108 (
2109 SELECT /* Standard Quantities for Discrete */
2110 WRO.ORGANIZATION_ID,
2111 WRO.INVENTORY_ITEM_ID,
2112 WRO.WIP_ENTITY_ID JOB_ID,
2113 decode(WE.ENTITY_TYPE,5,5,8,5,1) JOB_TYPE,
2114 SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
2115 FROM
2116 WIP_ENTITIES WE,
2117 WIP_REQUIREMENT_OPERATIONS WRO
2118 WHERE
2119 WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID AND
2120 WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
2121 WE.ENTITY_TYPE in (1,3,5,8) AND
2122 WE.WIP_ENTITY_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND JOB_TYPE in (1,5))
2123 GROUP BY
2124 WRO.ORGANIZATION_ID,
2125 WRO.INVENTORY_ITEM_ID,
2126 WRO.WIP_ENTITY_ID,
2127 WE.ENTITY_TYPE
2128 UNION ALL
2129 SELECT /* Standard Quantities for Repetitive */
2130 WRO.ORGANIZATION_ID,
2131 WRO.INVENTORY_ITEM_ID,
2132 WRO.REPETITIVE_SCHEDULE_ID JOB_ID,
2133 2 JOB_TYPE,
2134 SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
2135 FROM
2136 WIP_ENTITIES WE,
2137 WIP_REQUIREMENT_OPERATIONS WRO
2138 WHERE
2139 WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID AND
2140 WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
2141 WE.ENTITY_TYPE = 2 AND
2142 WRO.REPETITIVE_SCHEDULE_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND JOB_TYPE=2)
2143 GROUP BY
2144 WRO.ORGANIZATION_ID,
2145 WRO.INVENTORY_ITEM_ID,
2146 WRO.REPETITIVE_SCHEDULE_ID
2147 UNION ALL
2148 SELECT /* Standard Quantities for Flow
2149 Standard Qty for each component in BOM is multiplied with the planned
2150 qty from wfs for the assembly to get the standard qty for each component.
2151 */
2152 wfs.organization_id,
2153 bom_join.component_item_id inventory_item_id,
2154 wfs.wip_entity_id JOB_ID,
2155 3 JOB_TYPE,
2156 SUM(bom_join.Standard_Quantity) * wfs.PLANNED_QUANTITY Standard_Quantity
2157 FROM
2158 ( select /*+ index(bb) */
2159 bb.organization_id organization_id,
2160 bb.assembly_item_id assembly_item_id,
2161 bic.component_item_id component_item_id,
2162 bic.effectivity_date effectivity_date,
2163 bb.alternate_bom_designator alternate_bom_designator,
2164 bic.disable_date disable_date,
2165 nvl(lead(bic.effectivity_date) OVER
2166 (partition by bb.organization_id,
2167 bb.assembly_item_id,
2168 bb.alternate_bom_designator,
2169 bic.component_item_id,
2170 bic.operation_seq_num
2171 order by effectivity_date), sysdate) last_rev,
2172 bic.component_quantity Standard_Quantity
2173 from
2174 bom_bill_of_materials bb,
2175 bom_inventory_components bic
2176 where
2177 bb.COMMON_BILL_SEQUENCE_ID = bic.bill_sequence_id and
2178 bic.implementation_date is not null
2179 ) bom_join,
2180 wip_flow_schedules wfs
2181 WHERE
2182 EFFECTIVITY_DATE <= wfs.scheduled_completion_date and
2183 last_rev > decode (sign(wfs.scheduled_completion_date - nvl(wfs.date_closed,wfs.scheduled_completion_date)),1,wfs.date_closed,wfs.scheduled_completion_date) and
2184 decode (sign(wfs.scheduled_completion_date - nvl(wfs.date_closed,wfs.scheduled_completion_date)),1,wfs.date_closed,wfs.scheduled_completion_date) < nvl(bom_join.disable_date, sysdate) and
2185 wfs.organization_id = bom_join.organization_id and
2186 wfs.PRIMARY_ITEM_ID = bom_join.assembly_item_id and
2187 nvl(wfs.alternate_bom_designator,1) = nvl(bom_join.alternate_bom_designator,1) and
2188 WFS.WIP_ENTITY_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND
2189 JOB_TYPE=3)
2190 GROUP BY
2191 wfs.organization_id,
2192 bom_join.component_item_id,
2193 wfs.wip_entity_id,
2194 wfs.PLANNED_QUANTITY);
2195
2196 /* ODM Standards insert into fact table */
2197 l_stmt_num := 30;
2198 INSERT
2199 INTO OPI_DBI_JOB_MTL_DTL_STD_F
2200 (
2201 organization_id,
2202 job_id,
2203 job_type,
2204 assembly_item_id,
2205 component_item_id,
2206 line_type,
2207 standard_quantity,
2208 standard_value_b,
2209 source,
2210 creation_date,
2211 last_update_date,
2212 created_by,
2213 last_updated_by,
2214 last_update_login,
2215 PROGRAM_ID,
2216 PROGRAM_LOGIN_ID,
2217 PROGRAM_APPLICATION_ID,
2218 REQUEST_ID
2219 )
2220 select
2221 actuals.organization_id,
2222 actuals.job_id,
2223 actuals.job_type,
2224 actuals.assembly_item_id,
2225 actuals.component_item_id,
2226 actuals.line_type,
2227 tmp.standard_quantity,
2228 Decode(actuals.actual_quantity, 0,
2229 tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2230 (actuals.organization_id,
2231 actuals.component_item_id),
2232 tmp.standard_quantity*(actual_value_b/actual_quantity)),
2233 actuals.source,
2234 s_sysdate,
2235 s_sysdate,
2236 s_user_id,
2237 s_user_id,
2238 s_login_id,
2239 s_program_id,
2240 s_program_login_id,
2241 s_program_application_id,
2242 s_request_id
2243 from
2244 OPI_DBI_JOB_MTL_STD_QTY_TMP tmp,
2245 (select
2246 job_id,
2247 job_type,
2248 organization_id,
2249 assembly_item_id,
2250 component_item_id,
2251 line_type,
2252 source,
2253 sum(actual_quantity) actual_quantity,
2254 sum(actual_value_b) actual_value_b
2255 from
2256 OPI_DBI_JOB_MTL_DETAILS_F
2257 where
2258 source = 1
2259 group by
2260 organization_id,
2261 job_id,
2262 assembly_item_id,
2263 component_item_id,
2264 line_type,
2265 job_type,
2266 source
2267 )actuals
2268 where
2269 tmp.organization_id = actuals.organization_id and
2270 tmp.job_id = actuals.job_id and
2271 tmp.inventory_item_id = actuals.component_item_id and
2272 tmp.job_type = actuals.job_type;
2273
2274 l_row_count := sql%rowcount;
2275
2276 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
2277 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2278
2279 EXCEPTION
2280
2281 WHEN OTHERS THEN
2282
2283 rollback;
2284
2285 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
2286
2287 retcode := SQLCODE;
2288 errbuf := SQLERRM;
2289
2290 END GET_OPI_ODM_MTL_USAGE_STD_INIT;
2291
2292 /*
2293 Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact
2294 for ODM, Incremental Load procedure
2295
2296 Parameters:
2297 retcode - 0 on successful completion, -1 on error and 1 for warning.
2298 errbuf - empty on successful completion, message on error or warning
2299 */
2300
2301 PROCEDURE GET_OPI_ODM_MTL_USAGE_STD_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
2302 IS
2303 l_stmt_num NUMBER;
2304 l_row_count NUMBER;
2305 l_err_num NUMBER;
2306 l_err_msg VARCHAR2(255);
2307 l_proc_name VARCHAR2(255);
2308 l_status VARCHAR2(30);
2309 l_industry VARCHAR2(30);
2310 l_opi_schema VARCHAR2(30);
2311
2312 BEGIN
2313
2314 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_ODM_MTL_USAGE_STD_INCR';
2315
2316 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2317
2318 /* ODM insert into temp table */
2319 l_stmt_num := 20;
2320 INSERT
2321 INTO OPI_DBI_JOB_MTL_STD_QTY_TMP
2322 (ORGANIZATION_ID,
2323 INVENTORY_ITEM_ID,
2324 JOB_ID,
2325 JOB_TYPE,
2326 Standard_Quantity
2327 )
2328 SELECT
2329 ORGANIZATION_ID,
2330 INVENTORY_ITEM_ID,
2331 JOB_ID,
2332 JOB_TYPE,
2333 Standard_Quantity
2334 FROM
2335 (
2336 SELECT /* Standard Quantities for Discrete */
2337 WRO.ORGANIZATION_ID,
2338 WRO.INVENTORY_ITEM_ID,
2339 WRO.WIP_ENTITY_ID JOB_ID,
2340 decode(WE.ENTITY_TYPE,5,5,8,5,1) JOB_TYPE,
2341 SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
2342 FROM
2343 WIP_ENTITIES WE,
2344 WIP_REQUIREMENT_OPERATIONS WRO
2345 WHERE
2346 WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID AND
2347 WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
2348 WE.ENTITY_TYPE in (1,3,5,8) AND
2349 WE.WIP_ENTITY_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND JOB_TYPE in (1,5))
2350 GROUP BY
2351 WRO.ORGANIZATION_ID,
2352 WRO.INVENTORY_ITEM_ID,
2353 WRO.WIP_ENTITY_ID,
2354 WE.ENTITY_TYPE
2355 UNION ALL
2356 SELECT /* Standard Quantities for Repetitive */
2357 WRO.ORGANIZATION_ID,
2358 WRO.INVENTORY_ITEM_ID,
2359 WRO.REPETITIVE_SCHEDULE_ID JOB_ID,
2360 2 JOB_TYPE,
2361 SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
2362 FROM
2363 WIP_ENTITIES WE,
2364 WIP_REQUIREMENT_OPERATIONS WRO
2365 WHERE
2366 WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID AND
2367 WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
2368 WE.ENTITY_TYPE = 2 AND
2369 WRO.REPETITIVE_SCHEDULE_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND JOB_TYPE=2)
2370 GROUP BY
2371 WRO.ORGANIZATION_ID,
2372 WRO.INVENTORY_ITEM_ID,
2373 WRO.REPETITIVE_SCHEDULE_ID
2374 UNION ALL
2375 SELECT /* Standard Quantities for Flow */
2376 wfs.organization_id,
2377 t.component_item_id inventory_item_id,
2378 wfs.wip_entity_id JOB_ID,
2379 3 JOB_TYPE,
2380 SUM(t.Standard_Quantity) * wfs.PLANNED_QUANTITY Standard_Quantity
2381 FROM
2382 ( select /*+ index(bb) */
2383 bb.organization_id organization_id,
2384 bb.assembly_item_id assembly_item_id,
2385 bic.component_item_id component_item_id,
2386 bic.effectivity_date effectivity_date,
2387 bb.alternate_bom_designator alternate_bom_designator,
2388 bic.disable_date disable_date,
2389 nvl(lead(bic.effectivity_date) OVER
2390 (partition by bb.organization_id,
2391 bb.assembly_item_id,
2392 bb.alternate_bom_designator,
2393 bic.component_item_id,
2394 bic.operation_seq_num
2395 order by effectivity_date), sysdate) last_rev,
2396 bic.component_quantity Standard_Quantity
2397 from
2398 bom_bill_of_materials bb,
2399 bom_inventory_components bic
2400 where
2401 bb.COMMON_BILL_SEQUENCE_ID = bic.bill_sequence_id and
2402 bic.implementation_date is not null
2403 ) t,
2404 wip_flow_schedules wfs
2405 WHERE
2406 EFFECTIVITY_DATE <= wfs.scheduled_completion_date and
2407 last_rev > decode (sign(wfs.scheduled_completion_date - nvl(wfs.date_closed,wfs.scheduled_completion_date)),1,wfs.date_closed,wfs.scheduled_completion_date) and
2408 decode (sign(wfs.scheduled_completion_date - nvl(wfs.date_closed,wfs.scheduled_completion_date)),1,wfs.date_closed,wfs.scheduled_completion_date) < nvl(t.disable_date, sysdate) and
2409 wfs.organization_id = t.organization_id and
2410 wfs.PRIMARY_ITEM_ID = t.assembly_item_id and
2411 nvl(wfs.alternate_bom_designator,1) = nvl(t.alternate_bom_designator,1) and
2412 WFS.WIP_ENTITY_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND
2413 JOB_TYPE=3)
2414 GROUP BY
2415 wfs.organization_id,
2416 t.component_item_id,
2417 wfs.wip_entity_id,
2418 wfs.PLANNED_QUANTITY);
2419
2420 /* ODM Standards merge into fact table */
2421 l_stmt_num := 30;
2422 MERGE
2423 INTO OPI_DBI_JOB_MTL_DTL_STD_F fact using
2424 (
2425 select
2426 actuals.organization_id organization_id,
2427 actuals.job_id job_id,
2428 actuals.job_type job_type,
2429 actuals.assembly_item_id assembly_item_id,
2430 actuals.component_item_id component_item_id,
2431 actuals.line_type line_type,
2432 tmp.standard_quantity standard_quantity,
2433 Decode(actuals.actual_quantity, 0,
2434 tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2435 (actuals.organization_id,
2436 actuals.component_item_id),
2437 tmp.standard_quantity*(actual_value_b/actual_quantity))
2438 standard_value_b,
2439 actuals.source source,
2440 s_sysdate creation_date,
2441 s_sysdate last_update_date,
2442 s_user_id created_by,
2443 s_user_id last_updated_by,
2444 s_login_id last_update_login,
2445 s_program_id PROGRAM_ID,
2446 s_program_login_id PROGRAM_LOGIN_ID,
2447 s_program_application_id PROGRAM_APPLICATION_ID,
2448 s_request_id REQUEST_ID
2449 from
2450 OPI_DBI_JOB_MTL_STD_QTY_TMP tmp,
2451 (select
2452 job_id,
2453 job_type,
2454 organization_id,
2455 assembly_item_id,
2456 component_item_id,
2457 line_type,
2458 source,
2459 sum(actual_quantity) actual_quantity,
2460 sum(actual_value_b) actual_value_b
2461 from
2462 OPI_DBI_JOB_MTL_DETAILS_F
2463 where
2464 source = 1
2465 group by
2466 organization_id,
2467 job_id,
2468 assembly_item_id,
2469 component_item_id,
2470 line_type,
2471 job_type,
2472 source
2473 )actuals
2474 where
2475 tmp.organization_id = actuals.organization_id and
2476 tmp.job_id = actuals.job_id and
2477 tmp.inventory_item_id = actuals.component_item_id and
2478 tmp.job_type = actuals.job_type
2479 )stg
2480 ON
2481 (
2482 fact.organization_id = stg.organization_id and
2483 fact.job_id = stg.job_id and
2484 fact.job_type = stg.job_type and
2485 fact.assembly_item_id = stg.assembly_item_id and
2486 fact.component_item_id = stg.component_item_id and
2487 fact.line_type = stg.line_type and
2488 fact.source = stg.source
2489 )
2490 WHEN MATCHED THEN
2491 UPDATE SET
2492 fact.standard_quantity = stg.standard_quantity,
2493 fact.standard_value_b = stg.standard_value_b,
2494 fact.creation_date = stg.creation_date,
2495 fact.last_update_date = stg.last_update_date,
2496 fact.created_by = stg.created_by,
2497 fact.last_updated_by = stg.last_updated_by,
2498 fact.last_update_login = stg.last_update_login,
2499 fact.PROGRAM_ID = stg.PROGRAM_ID,
2500 fact.PROGRAM_LOGIN_ID = stg.PROGRAM_LOGIN_ID,
2501 fact.PROGRAM_APPLICATION_ID = stg.PROGRAM_APPLICATION_ID,
2502 fact.REQUEST_ID = stg.REQUEST_ID
2503 WHEN NOT MATCHED THEN
2504 INSERT
2505 (
2506 organization_id,
2507 job_id,
2508 job_type,
2509 assembly_item_id,
2510 component_item_id,
2511 line_type,
2512 standard_quantity,
2513 standard_value_b,
2514 source,
2515 creation_date,
2516 last_update_date,
2517 created_by,
2518 last_updated_by,
2519 last_update_login,
2520 PROGRAM_ID,
2521 PROGRAM_LOGIN_ID,
2522 PROGRAM_APPLICATION_ID,
2523 REQUEST_ID
2524 )
2525 VALUES
2526 (
2527 stg.organization_id,
2528 stg.job_id,
2529 stg.job_type,
2530 stg.assembly_item_id,
2531 stg.component_item_id,
2532 stg.line_type,
2533 stg.standard_quantity,
2534 stg.standard_value_b,
2535 stg.source,
2536 stg.creation_date,
2537 stg.last_update_date,
2538 stg.created_by,
2539 stg.last_updated_by,
2540 stg.last_update_login,
2541 stg.PROGRAM_ID,
2542 stg.PROGRAM_LOGIN_ID,
2543 stg.PROGRAM_APPLICATION_ID,
2544 stg.REQUEST_ID
2545 );
2546
2547 l_row_count := sql%rowcount;
2548
2549 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
2550 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2551
2552 EXCEPTION
2553
2554 WHEN OTHERS THEN
2555
2556 rollback;
2557
2558 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
2559
2560 retcode := SQLCODE;
2561 errbuf := SQLERRM;
2562
2563 END GET_OPI_ODM_MTL_USAGE_STD_INCR;
2564
2565 /*
2566 Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact
2567 for OPM, Initial Load procedure
2568
2569 Parameters:
2570 retcode - 0 on successful completion, -1 on error and 1 for warning.
2571 errbuf - empty on successful completion, message on error or warning
2572 */
2573
2574 PROCEDURE GET_OPI_OPM_MTL_USAGE_STD_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
2575 IS
2576 l_stmt_num NUMBER;
2577 l_row_count NUMBER;
2578 l_err_num NUMBER;
2579 l_err_msg VARCHAR2(255);
2580 l_proc_name VARCHAR2(255);
2581 l_status VARCHAR2(30);
2582 l_industry VARCHAR2(30);
2583 l_opi_schema VARCHAR2(30);
2584
2585 BEGIN
2586
2587 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_OPM_MTL_USAGE_STD_INIT';
2588
2589 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2590
2591 /* OPM Standards insert into fact table */
2592 /* Actuals fact which is at transaction date level is summarised and joined with the
2593 scaled mtl table, standard value is calculated as actual-value* std_qty/actual_qty */
2594 l_stmt_num := 40;
2595 INSERT
2596 INTO OPI_DBI_JOB_MTL_DTL_STD_F
2597 (
2598 organization_id,
2599 job_id,
2600 job_type,
2601 assembly_item_id,
2602 component_item_id,
2603 line_type,
2604 standard_quantity,
2605 standard_value_b,
2606 source,
2607 creation_date,
2608 last_update_date,
2609 created_by,
2610 last_updated_by,
2611 last_update_login,
2612 PROGRAM_ID,
2613 PROGRAM_LOGIN_ID,
2614 PROGRAM_APPLICATION_ID,
2615 REQUEST_ID
2616 )
2617 select
2618 tmp.organization_id,
2619 tmp.batch_id,
2620 tmp.job_type,
2621 tmp.coproduct_id,
2622 tmp.item_id,
2623 actuals.line_type,
2624 sum(tmp.scaled_plan_qty*decode(actuals.line_type,2,-1,1)),
2625 sum(decode(actuals.actual_qty,0,
2626 OPI_DBI_JOBS_PKG.GET_OPM_ITEM_COST(tmp.organization_id,
2627 tmp.item_id,
2628 tmp.completion_date),
2629 actuals.actual_value_b*tmp.scaled_plan_qty/actuals.actual_qty)
2630 *decode(actuals.line_type,2,-1,1)),
2631 actuals.source,
2632 s_sysdate,
2633 s_sysdate,
2634 s_user_id,
2635 s_user_id,
2636 s_login_id,
2637 s_program_id,
2638 s_program_login_id,
2639 s_program_application_id,
2640 s_request_id
2641 from
2642 OPI_DBI_OPM_SCALED_MTL tmp,
2643 (
2644 select
2645 job_id,
2646 job_type,
2647 organization_id,
2648 assembly_item_id,
2649 component_item_id,
2650 line_type,
2651 source,
2652 sum(actual_quantity) actual_qty,
2653 sum(actual_value_b) actual_value_b
2654 from
2655 OPI_DBI_JOB_MTL_DETAILS_F
2656 where
2657 source in(2,3)
2658 group by
2659 job_id,
2660 job_type,
2661 organization_id,
2662 assembly_item_id,
2663 component_item_id,
2664 line_type,
2665 source
2666 )actuals
2667 where
2668 tmp.organization_id = actuals.organization_id and
2669 tmp.batch_id = actuals.job_id and
2670 tmp.job_type = actuals.job_type and
2671 tmp.coproduct_id = actuals.assembly_item_id and
2672 tmp.item_id = actuals.component_item_id and
2673 tmp.line_type = actuals.line_type
2674 group by
2675 tmp.organization_id,
2676 tmp.batch_id,
2677 tmp.job_type,
2678 tmp.coproduct_id,
2679 tmp.item_id,
2680 actuals.line_type,
2681 actuals.source;
2682
2683 l_row_count := sql%rowcount;
2684
2685 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
2686 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2687
2688 EXCEPTION
2689
2690 WHEN OTHERS THEN
2691
2692 rollback;
2693
2694 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
2695
2696 retcode := SQLCODE;
2697 errbuf := SQLERRM;
2698
2699 END GET_OPI_OPM_MTL_USAGE_STD_INIT;
2700
2701 /*
2702 Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact
2703 for OPM, Incremental Load procedure
2704
2705 Parameters:
2706 retcode - 0 on successful completion, -1 on error and 1 for warning.
2707 errbuf - empty on successful completion, message on error or warning
2708 */
2709
2710 PROCEDURE GET_OPI_OPM_MTL_USAGE_STD_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
2711 IS
2712 l_stmt_num NUMBER;
2713 l_row_count NUMBER;
2714 l_err_num NUMBER;
2715 l_err_msg VARCHAR2(255);
2716 l_proc_name VARCHAR2(255);
2717 l_status VARCHAR2(30);
2718 l_industry VARCHAR2(30);
2719 l_opi_schema VARCHAR2(30);
2720
2721 BEGIN
2722
2723 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_OPM_MTL_USAGE_STD_INCR';
2724
2725 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2726
2727 /* OPM Standards insert into fact table */
2728 l_stmt_num := 40;
2729 MERGE
2730 INTO OPI_DBI_JOB_MTL_DTL_STD_F fact using
2731 (
2732 select
2733 tmp.organization_id organization_id,
2734 tmp.batch_id job_id,
2735 tmp.job_type job_type,
2736 tmp.coproduct_id assembly_item_id,
2737 tmp.item_id component_item_id,
2738 actuals.line_type line_type,
2739 sum(tmp.scaled_plan_qty*decode(actuals.line_type,2,-1,1)) standard_quantity,
2740 sum(decode(actuals.actual_qty,0,
2741 OPI_DBI_JOBS_PKG.GET_OPM_ITEM_COST(tmp.organization_id,
2742 tmp.item_id,
2743 tmp.completion_date),
2744 actuals.actual_value_b*tmp.scaled_plan_qty/actuals.actual_qty)
2745 *decode(actuals.line_type,2,-1,1)) standard_value_b,
2746 actuals.source source,
2747 s_sysdate creation_date,
2748 s_sysdate last_update_date,
2749 s_user_id created_by,
2750 s_user_id last_updated_by,
2751 s_login_id last_update_login,
2752 s_program_id PROGRAM_ID,
2753 s_program_login_id PROGRAM_LOGIN_ID,
2754 s_program_application_id PROGRAM_APPLICATION_ID,
2755 s_request_id REQUEST_ID
2756 from
2757 OPI_DBI_OPM_SCALED_MTL tmp,
2758 (
2759 select
2760 job_id,
2761 job_type,
2762 organization_id,
2763 assembly_item_id,
2764 component_item_id,
2765 line_type,
2766 source,
2767 sum(actual_quantity) actual_qty,
2768 sum(actual_value_b) actual_value_b
2769 from
2770 OPI_DBI_JOB_MTL_DETAILS_F
2771 where
2772 source in(2,3)
2773 group by
2774 job_id,
2775 job_type,
2776 organization_id,
2777 assembly_item_id,
2778 component_item_id,
2779 line_type,
2780 source
2781 )actuals
2782 where
2783 tmp.organization_id = actuals.organization_id and
2784 tmp.batch_id = actuals.job_id and
2785 tmp.job_type = actuals.job_type and
2786 tmp.coproduct_id = actuals.assembly_item_id and
2787 tmp.item_id = actuals.component_item_id and
2788 tmp.line_type = actuals.line_type
2789 group by
2790 tmp.organization_id,
2791 tmp.batch_id,
2792 tmp.job_type,
2793 tmp.coproduct_id,
2794 tmp.item_id,
2795 actuals.line_type,
2796 actuals.source
2797 )stg
2798 ON
2799 ( fact.organization_id = stg.organization_id and
2800 fact.job_id = stg.job_id and
2801 fact.job_type = stg.job_type and
2802 fact.assembly_item_id = stg.assembly_item_id and
2803 fact.component_item_id = stg.component_item_id and
2804 fact.line_type = stg.line_type and
2805 fact.source = stg.source
2806 )
2807 WHEN MATCHED THEN
2808 UPDATE SET
2809 fact.standard_quantity = stg.standard_quantity,
2810 fact.standard_value_b = stg.standard_value_b,
2811 fact.last_update_date = stg.last_update_date,
2812 fact.last_updated_by = stg.last_updated_by,
2813 fact.last_update_login = stg.last_update_login
2814 WHEN NOT MATCHED THEN
2815 INSERT
2816 (
2817 organization_id,
2818 job_id,
2819 job_type,
2820 assembly_item_id,
2821 component_item_id,
2822 line_type,
2823 standard_quantity,
2824 standard_value_b,
2825 source,
2826 creation_date,
2827 last_update_date,
2828 created_by,
2829 last_updated_by,
2830 last_update_login,
2831 PROGRAM_ID,
2832 PROGRAM_LOGIN_ID,
2833 PROGRAM_APPLICATION_ID,
2834 REQUEST_ID
2835 )
2836 VALUES
2837 (
2838 stg.organization_id,
2839 stg.job_id,
2840 stg.job_type,
2841 stg.assembly_item_id,
2842 stg.component_item_id,
2843 stg.line_type,
2844 stg.standard_quantity,
2845 stg.standard_value_b,
2846 stg.source,
2847 stg.creation_date,
2848 stg.last_update_date,
2849 stg.created_by,
2850 stg.last_updated_by,
2851 stg.last_update_login,
2852 stg.PROGRAM_ID,
2853 stg.PROGRAM_LOGIN_ID,
2854 stg.PROGRAM_APPLICATION_ID,
2855 stg.REQUEST_ID
2856 );
2857
2858 l_row_count := sql%rowcount;
2859
2860 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
2861 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2862
2863 EXCEPTION
2864
2865 WHEN OTHERS THEN
2866
2867 rollback;
2868
2869 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
2870
2871 retcode := SQLCODE;
2872 errbuf := SQLERRM;
2873
2874 END GET_OPI_OPM_MTL_USAGE_STD_INCR;
2875
2876 /*
2877 Procedure populates temp table OPI_DBI_OPM_SCALED_MTL used to get the planned_qty for OPM,
2878 Initial Load procedure
2879
2880 Parameters:
2881 retcode - 0 on successful completion, -1 on error and 1 for warning.
2882 errbuf - empty on successful completion, message on error or warning
2883 */
2884
2885 PROCEDURE GET_OPI_SCALED_MTL_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
2886 IS
2887 l_stmt_num NUMBER;
2888 l_line_count number;
2889 l_row_count NUMBER;
2890 l_err_num NUMBER;
2891 l_err_msg VARCHAR2(255);
2892 l_proc_name VARCHAR2(255);
2893 l_status VARCHAR2(30);
2894 l_industry VARCHAR2(30);
2895 l_opi_schema VARCHAR2(30);
2896 l_batch_id NUMBER;
2897 l_org_id NUMBER;
2898 l_item_id NUMBER;
2899 l_cost_alloc NUMBER;
2900 l_coproduct_id NUMBER;
2901 i NUMBER;
2902 j NUMBER;
2903 k NUMBER;
2904 x_scale_factor number;
2905 x_return_status VARCHAR2 (80);
2906 l_scale_tab gmd_common_scale.scale_tab;
2907 x_scale_tab gmd_common_scale.scale_tab;
2908 empty_scale_tab gmd_common_scale.scale_tab;
2909
2910 cursor cur_get_batch
2911 is
2912 select
2913 jobs.job_id job_id,
2914 jobs.organization_id organization_id,
2915 jobs.assembly_item_id,
2916 mtl_dtl.cost_alloc cost_alloc
2917 from
2918 opi_dbi_jobs_f jobs,
2919 gme_material_details mtl_dtl
2920 where
2921 jobs.source in (2,3) and
2922 jobs.job_type = 4 and
2923 jobs.status = 'Closed' and
2924 jobs.line_type = 1 and
2925 jobs.organization_id = mtl_dtl.organization_id and
2926 jobs.job_id = mtl_dtl.batch_id and
2927 jobs.assembly_item_id = mtl_dtl.inventory_item_id and
2928 mtl_dtl.line_type = 1;
2929
2930
2931 cursor cur_get_mtl(job_id_in IN NUMBER, org_id_in IN NUMBER,
2932 item_id_in IN NUMBER, cost_alloc_in IN NUMBER)
2933 is
2934 select
2935 job.Organization_Id Organization_Id,
2936 job.job_type Job_Type,
2937 dtl.batch_id,
2938 job.completion_date completion_date,
2939 job.assembly_item_id coproduct_id,
2940 dtl.material_detail_id material_detail_id,
2941 job.start_quantity coproduct_plan_qty,
2942 job.actual_qty_completed coproduct_actual_qty,
2943 NULL scaled_plan_qty,
2944 dtl.actual_qty,
2945 dtl.dtl_um,
2946 dtl.scale_type,
2947 dtl.contribute_yield_ind,
2948 dtl.scale_multiple,
2949 dtl.scale_rounding_variance,
2950 dtl.rounding_direction,
2951 dtl.line_no,
2952 dtl.line_type,
2953 dtl.inventory_item_id ,
2954 dtl.plan_qty,
2955 cost_alloc_in
2956 from
2957 OPI_DBI_JOBS_F job,
2958 gme_material_details dtl
2959 where
2960 job.job_id = dtl.batch_id
2961 and job.status = 'Closed'
2962 and dtl.line_type in (-1,2)
2963 and job.job_id = job_id_in
2964 and job.organization_id = org_id_in
2965 and job.assembly_item_id = item_id_in
2966 and job.line_type = 1
2967 order by
2968 dtl.batch_id,
2969 job.assembly_item_id,
2970 dtl.line_type;
2971
2972 type dtl_type is table of cur_get_mtl%ROWTYPE index by binary_integer;
2973 dtl_tab dtl_type;
2974 temp_dtl cur_get_mtl%ROWTYPE;
2975 l_scale_qty number;
2976
2977 gmd_common_scale_error EXCEPTION;
2978
2979 BEGIN
2980
2981 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_SCALED_MTL_INIT';
2982
2983 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2984
2985 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
2986 --{
2987 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_SCALED_MTL';
2988 --}
2989 END IF;
2990
2991 l_stmt_num := 10;
2992 k := 1;
2993 FOR get_rec1 in cur_get_batch LOOP
2994 --{
2995 l_stmt_num := 1000+k;
2996 l_batch_id := get_rec1.job_id;
2997 l_org_id := get_rec1.organization_id;
2998 l_item_id := get_rec1.assembly_item_id;
2999 l_cost_alloc := get_rec1.cost_alloc;
3000
3001 i := 1;
3002 FOR get_rec2 in cur_get_mtl(l_batch_id, l_org_id, l_item_id, l_cost_alloc) LOOP
3003 --{
3004 l_stmt_num := 2000 + i;
3005 l_scale_tab (i).line_no := get_rec2.line_no;
3006 l_scale_tab (i).detail_uom := get_rec2.DTL_UM;
3007 l_scale_tab (i).scale_type := get_rec2.scale_type;
3008 l_scale_tab (i).contribute_yield_ind := get_rec2.contribute_yield_ind;
3009 l_scale_tab (i).scale_multiple := get_rec2.scale_multiple;
3010 l_scale_tab (i).scale_rounding_variance := get_rec2.scale_rounding_variance;
3011 l_scale_tab (i).rounding_direction := get_rec2.rounding_direction;
3012 l_scale_tab (i).line_no := get_rec2.line_no;
3013 l_scale_tab (i).line_type := get_rec2.line_type;
3014 l_scale_tab (i).inventory_item_id := get_rec2.inventory_item_id;
3015 l_scale_tab (i).qty := get_rec2.plan_qty;
3016
3017 if get_rec2.coproduct_plan_qty <> 0 then
3018 --{
3019 x_scale_factor := get_rec2.coproduct_actual_qty/get_rec2.coproduct_plan_qty;
3020 --}
3021 else
3022 --{
3023 x_scale_factor := 1;
3024 --}
3025 end if;
3026
3027 if l_scale_tab(i).scale_type <> 0 then -- call gmd_common_scale.sale only if ing is scalable
3028 --{
3029 gmd_common_scale.scale( p_scale_tab => l_scale_tab
3030 ,p_orgn_id => l_org_id
3031 ,p_scale_factor => x_scale_factor
3032 ,p_primaries => 'OUTPUT'
3033 ,x_scale_tab => x_scale_tab
3034 ,x_return_status => x_return_status);
3035
3036 if x_return_status = 'S' then
3037 l_scale_qty := x_scale_tab(i).qty;
3038 else
3039 raise gmd_common_scale_error;
3040 end if;
3041
3042 --}
3043 else -- ingredient is not scalable, just return planned qty not scaled
3044 --{
3045 l_scale_qty := l_scale_tab(i).qty;
3046 --}
3047 end if;
3048
3049
3050 INSERT INTO OPI_DBI_OPM_SCALED_MTL
3051 (
3052 ORGANIZATION_ID ,
3053 JOB_TYPE,
3054 BATCH_ID,
3055 COPRODUCT_ID,
3056 MATERIAL_DETAIL_ID,
3057 COPRODUCT_PLAN_QTY,
3058 COPRODUCT_ACTUAL_QTY,
3059 SCALED_PLAN_QTY ,
3060 ACTUAL_QTY,
3061 ITEM_UM,
3062 SCALE_TYPE,
3063 CONTRIBUTE_YIELD_IND,
3064 SCALE_MULTIPLE,
3065 SCALE_ROUNDING_VARIANCE,
3066 ROUNDING_DIRECTION,
3067 LINE_NO,
3068 LINE_TYPE,
3069 item_id,
3070 PLAN_QTY,
3071 COMPLETION_DATE)
3072 values
3073 (
3074 get_rec2.ORGANIZATION_ID,
3075 get_rec2.JOB_TYPE,
3076 get_rec2.BATCH_ID,
3077 get_rec2.COPRODUCT_ID,
3078 get_rec2.MATERIAL_DETAIL_ID,
3079 get_rec2.COPRODUCT_PLAN_QTY,
3080 get_rec2.COPRODUCT_ACTUAL_QTY,
3081 l_scale_qty*l_cost_alloc,
3082 get_rec2.ACTUAL_QTY,
3083 get_rec2.DTL_UM,
3084 get_rec2.SCALE_TYPE,
3085 get_rec2.CONTRIBUTE_YIELD_IND,
3086 get_rec2.SCALE_MULTIPLE,
3087 get_rec2.SCALE_ROUNDING_VARIANCE,
3088 get_rec2.ROUNDING_DIRECTION,
3089 get_rec2.LINE_NO,
3090 get_rec2.LINE_TYPE,
3091 get_rec2.inventory_item_id,
3092 get_rec2.PLAN_QTY,
3093 get_rec2.COMPLETION_DATE);
3094
3095
3096 i := i + 1;
3097
3098 --}
3099 END LOOP;
3100
3101 k := k + 1;
3102 l_scale_tab := empty_scale_tab;
3103
3104 --}
3105 END LOOP;
3106
3107 commit;
3108
3109 select count(*) into l_row_count from OPI_DBI_OPM_SCALED_MTL;
3110
3111 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM Scaled Extraction: '|| l_row_count || ' rows inserted');
3112 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
3113
3114
3115 EXCEPTION
3116 WHEN gmd_common_scale_error THEN
3117 rollback;
3118 BIS_COLLECTION_UTILITIES.PUT_LINE('Error: gmd_common_scale.scale completed with error status at statement' || l_stmt_num);
3119
3120 WHEN OTHERS THEN
3121 rollback;
3122 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
3123
3124 retcode := SQLCODE;
3125 errbuf := SQLERRM;
3126
3127 END GET_OPI_SCALED_MTL_INIT;
3128
3129 /*
3130 Procedurr populates temp table OPI_DBI_OPM_SCALED_MTL used to get the planned_qty for OPM,
3131 Incremental Load procedure
3132
3133 Parameters:
3134 retcode - 0 on successful completion, -1 on error and 1 for warning.
3135 errbuf - empty on successful completion, message on error or warning
3136 */
3137
3138 PROCEDURE GET_OPI_SCALED_MTL_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
3139 IS
3140 l_stmt_num NUMBER;
3141 l_line_count number;
3142 l_row_count NUMBER;
3143 l_err_num NUMBER;
3144 l_err_msg VARCHAR2(255);
3145 l_proc_name VARCHAR2(255);
3146 l_status VARCHAR2(30);
3147 l_industry VARCHAR2(30);
3148 l_opi_schema VARCHAR2(30);
3149 l_batch_id NUMBER;
3150 l_org_id NUMBER;
3151 l_item_id NUMBER;
3152 l_cost_alloc NUMBER;
3153 l_coproduct_id NUMBER;
3154 i NUMBER;
3155 j NUMBER;
3156 k NUMBER;
3157 x_scale_factor number;
3158 x_return_status VARCHAR2 (80);
3159 l_scale_tab gmd_common_scale.scale_tab;
3160 x_scale_tab gmd_common_scale.scale_tab;
3161 empty_scale_tab gmd_common_scale.scale_tab;
3162
3163 cursor cur_get_batch
3164 is
3165 select
3166 jobs.job_id job_id,
3167 jobs.organization_id organization_id,
3168 jobs.assembly_item_id,
3169 mtl_dtl.cost_alloc cost_alloc
3170 from
3171 opi_dbi_jobs_stg jobs,
3172 gme_material_details mtl_dtl
3173 where
3174 jobs.source in (2,3) and
3175 jobs.job_type = 4 and
3176 jobs.status = 'Closed' and
3177 jobs.line_type = 1 and
3178 jobs.organization_id = mtl_dtl.organization_id and
3179 jobs.job_id = mtl_dtl.batch_id and
3180 jobs.assembly_item_id = mtl_dtl.inventory_item_id and
3181 mtl_dtl.line_type = 1;
3182
3183
3184 cursor cur_get_mtl(job_id_in IN NUMBER, org_id_in IN NUMBER,
3185 item_id_in IN NUMBER, cost_alloc_in IN NUMBER)
3186 is
3187 select
3188 job.Organization_Id Organization_Id,
3189 job.job_type Job_Type,
3190 dtl.batch_id,
3191 job.completion_date completion_date,
3192 job.assembly_item_id coproduct_id,
3193 dtl.material_detail_id material_detail_id,
3194 job.start_quantity coproduct_plan_qty,
3195 job.actual_qty_completed coproduct_actual_qty,
3196 NULL scaled_plan_qty,
3197 dtl.actual_qty,
3198 dtl.dtl_um,
3199 dtl.scale_type,
3200 dtl.contribute_yield_ind,
3201 dtl.scale_multiple,
3202 dtl.scale_rounding_variance,
3203 dtl.rounding_direction,
3204 dtl.line_no,
3205 dtl.line_type,
3206 dtl.inventory_item_id ,
3207 dtl.plan_qty,
3208 cost_alloc_in
3209 from
3210 OPI_DBI_JOBS_F job,
3211 gme_material_details dtl
3212 where
3213 job.job_id = dtl.batch_id
3214 and job.status = 'Closed'
3215 and dtl.line_type in (-1,2)
3216 and job.job_id = job_id_in
3217 and job.organization_id = org_id_in
3218 and job.assembly_item_id = item_id_in
3219 and job.line_type = 1
3220 order by
3221 dtl.batch_id,
3222 job.assembly_item_id,
3223 dtl.line_type;
3224
3225 type dtl_type is table of cur_get_mtl%ROWTYPE index by binary_integer;
3226 dtl_tab dtl_type;
3227 temp_dtl cur_get_mtl%ROWTYPE;
3228 l_scale_qty number;
3229
3230 gmd_common_scale_error EXCEPTION;
3231
3232
3233 BEGIN
3234
3235 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_SCALED_MTL_INCR';
3236
3237 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
3238
3239 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
3240 --{
3241 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_SCALED_MTL';
3242 --}
3243 END IF;
3244
3245 l_stmt_num := 10;
3246 k := 1;
3247 FOR get_rec1 in cur_get_batch LOOP
3248 --{
3249 l_stmt_num := 1000+k;
3250 l_batch_id := get_rec1.job_id;
3251 l_org_id := get_rec1.organization_id;
3252 l_item_id := get_rec1.assembly_item_id;
3253 l_cost_alloc := get_rec1.cost_alloc;
3254
3255 i := 1;
3256 FOR get_rec2 in cur_get_mtl(l_batch_id, l_org_id, l_item_id, l_cost_alloc) LOOP
3257 --{
3258 l_stmt_num := 2000 + i;
3259 l_scale_tab (i).line_no := get_rec2.line_no;
3260 l_scale_tab (i).detail_uom := get_rec2.DTL_UM;
3261 l_scale_tab (i).scale_type := get_rec2.scale_type;
3262 l_scale_tab (i).contribute_yield_ind := get_rec2.contribute_yield_ind;
3263 l_scale_tab (i).scale_multiple := get_rec2.scale_multiple;
3264 l_scale_tab (i).scale_rounding_variance := get_rec2.scale_rounding_variance;
3265 l_scale_tab (i).rounding_direction := get_rec2.rounding_direction;
3266 l_scale_tab (i).line_no := get_rec2.line_no;
3267 l_scale_tab (i).line_type := get_rec2.line_type;
3268 l_scale_tab (i).inventory_item_id := get_rec2.inventory_item_id;
3269 l_scale_tab (i).qty := get_rec2.plan_qty;
3270
3271 if get_rec2.coproduct_plan_qty <> 0 then
3272 --{
3273 x_scale_factor := get_rec2.coproduct_actual_qty/get_rec2.coproduct_plan_qty;
3274 --}
3275 else
3276 --{
3277 x_scale_factor := 1;
3278 --}
3279 end if;
3280
3281 if l_scale_tab(i).scale_type <> 0 then -- call gmd_common_scale.sale only if ing is scalable
3282 --{
3283 gmd_common_scale.scale( p_scale_tab => l_scale_tab
3284 ,p_orgn_id => l_org_id
3285 ,p_scale_factor => x_scale_factor
3286 ,p_primaries => 'OUTPUT'
3287 ,x_scale_tab => x_scale_tab
3288 ,x_return_status => x_return_status);
3289
3290 if x_return_status = 'S' then
3291 l_scale_qty := x_scale_tab(i).qty;
3292 else
3293 raise gmd_common_scale_error;
3294 end if;
3295
3296 --}
3297 else -- ingredient is not scalable, just return planned qty not scaled
3298 --{
3299 l_scale_qty := l_scale_tab(i).qty;
3300 --}
3301 end if;
3302
3303 INSERT INTO OPI_DBI_OPM_SCALED_MTL
3304 (
3305 ORGANIZATION_ID ,
3306 JOB_TYPE,
3307 BATCH_ID,
3308 COPRODUCT_ID,
3309 MATERIAL_DETAIL_ID,
3310 COPRODUCT_PLAN_QTY,
3311 COPRODUCT_ACTUAL_QTY,
3312 SCALED_PLAN_QTY ,
3313 ACTUAL_QTY,
3314 ITEM_UM,
3315 SCALE_TYPE,
3316 CONTRIBUTE_YIELD_IND,
3317 SCALE_MULTIPLE,
3318 SCALE_ROUNDING_VARIANCE,
3319 ROUNDING_DIRECTION,
3320 LINE_NO,
3321 LINE_TYPE,
3322 item_id,
3323 PLAN_QTY,
3324 COMPLETION_DATE)
3325 values
3326 (
3327 get_rec2.ORGANIZATION_ID,
3328 get_rec2.JOB_TYPE,
3329 get_rec2.BATCH_ID,
3330 get_rec2.COPRODUCT_ID,
3331 get_rec2.MATERIAL_DETAIL_ID,
3332 get_rec2.COPRODUCT_PLAN_QTY,
3333 get_rec2.COPRODUCT_ACTUAL_QTY,
3334 l_scale_qty*l_cost_alloc,
3335 get_rec2.ACTUAL_QTY,
3336 get_rec2.DTL_UM,
3337 get_rec2.SCALE_TYPE,
3338 get_rec2.CONTRIBUTE_YIELD_IND,
3339 get_rec2.SCALE_MULTIPLE,
3340 get_rec2.SCALE_ROUNDING_VARIANCE,
3341 get_rec2.ROUNDING_DIRECTION,
3342 get_rec2.LINE_NO,
3343 get_rec2.LINE_TYPE,
3344 get_rec2.inventory_item_id,
3345 get_rec2.PLAN_QTY,
3346 get_rec2.COMPLETION_DATE);
3347
3348
3349 i := i + 1;
3350
3351 --}
3352 END LOOP;
3353
3354 k := k + 1;
3355 l_scale_tab := empty_scale_tab;
3356
3357 --}
3358 END LOOP;
3359
3360 commit;
3361
3362 select count(*) into l_row_count from OPI_DBI_OPM_SCALED_MTL;
3363
3364 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM Scaled Extraction: '|| l_row_count || ' rows inserted');
3365 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
3366
3367
3368 EXCEPTION
3369
3370 WHEN gmd_common_scale_error THEN
3371 rollback;
3372 BIS_COLLECTION_UTILITIES.PUT_LINE('Error: gmd_common_scale.scale completed with error status at statement' || l_stmt_num);
3373
3374 WHEN OTHERS THEN
3375 rollback;
3376 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
3377
3378 retcode := SQLCODE;
3379 errbuf := SQLERRM;
3380
3381 END GET_OPI_SCALED_MTL_INCR;
3382
3383 /*
3384 Procedure populates WIP Completions fact for OPM and ODM from Jobs Transaction Staging
3385 table, Initial Load procedure
3386
3387 Parameters:
3388 retcode - 0 on successful completion, -1 on error and 1 for warning.
3389 errbuf - empty on successful completion, message on error or warning
3390 */
3391
3392 PROCEDURE GET_OPI_WIP_COMP_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
3393 IS
3394 l_stmt_num NUMBER;
3395 l_row_count NUMBER;
3396 l_err_num NUMBER;
3397 l_err_msg VARCHAR2(255);
3398 l_proc_name VARCHAR2(255);
3399 l_status VARCHAR2(30);
3400 l_industry VARCHAR2(30);
3401 l_opi_schema VARCHAR2(30);
3402
3403 BEGIN
3404
3405 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_WIP_COMP_INIT';
3406
3407 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
3408
3409 /* Extraction of WIP Completions fact */
3410 l_stmt_num := 20;
3411 INSERT
3412 INTO OPI_DBI_WIP_COMP_F
3413 (
3414 organization_id,
3415 inventory_item_id,
3416 transaction_date,
3417 completion_quantity,
3418 completion_value_b,
3419 uom_code,
3420 conversion_rate,
3421 planned_item,
3422 source,
3423 creation_date,
3424 last_update_date,
3425 created_by,
3426 last_updated_by,
3427 last_update_login,
3428 sec_conversion_rate,
3429 job_id,
3430 job_type,
3431 line_type,
3432 completion_quantity_draft,
3433 completion_value_draft_b,
3434 completion_value_g,
3435 completion_value_draft_g,
3436 completion_value_sg,
3437 completion_value_draft_sg,
3438 PROGRAM_ID,
3439 PROGRAM_LOGIN_ID,
3440 PROGRAM_APPLICATION_ID,
3441 REQUEST_ID
3442 )
3443 select
3444 jobs_txn.organization_id,
3445 assembly_item_id,
3446 trunc(jobs_txn.transaction_date),
3447 -sum(primary_quantity+primary_quantity_draft),
3448 -sum(transaction_value_b+transaction_value_draft_b),
3449 uom_code,
3450 crates.conversion_rate,
3451 planned_item,
3452 source,
3453 s_sysdate,
3454 s_sysdate,
3455 s_user_id,
3456 s_user_id,
3457 s_login_id,
3458 crates.sec_conversion_rate,
3459 job_id,
3460 job_type,
3461 line_type,
3462 -sum(primary_quantity_draft),
3463 -sum(transaction_value_draft_b),
3464 -sum((transaction_value_b+transaction_value_draft_b)*crates.conversion_rate),
3465 -sum(transaction_value_draft_b*crates.conversion_rate),
3466 -sum((transaction_value_b+transaction_value_draft_b)*crates.sec_conversion_rate),
3467 -sum(transaction_value_draft_b*crates.sec_conversion_rate),
3468 s_program_id,
3469 s_program_login_id,
3470 s_program_application_id,
3471 s_request_id
3472 from
3473 OPI_DBI_JOBS_TXN_STG jobs_txn,
3474 opi_dbi_muv_conv_rates crates
3475 where
3476 jobs_txn.etl_type_id = 2 and
3477 jobs_txn.organization_id = crates.organization_id and
3478 trunc(jobs_txn.transaction_date) = crates.transaction_date
3479 group by
3480 jobs_txn.organization_id,
3481 jobs_txn.job_id,
3482 jobs_txn.job_type,
3483 jobs_txn.assembly_item_id,
3484 jobs_txn.component_item_id,
3485 jobs_txn.uom_code,
3486 jobs_txn.line_type,
3487 trunc(jobs_txn.transaction_date),
3488 jobs_txn.source,
3489 crates.conversion_rate,
3490 crates.sec_conversion_rate,
3491 planned_item;
3492
3493 l_row_count := sql%rowcount;
3494
3495 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of WIP Completions Fact Table: '|| l_row_count || ' rows inserted');
3496 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
3497
3498 EXCEPTION
3499
3500 WHEN OTHERS THEN
3501
3502 rollback;
3503
3504 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
3505
3506 retcode := SQLCODE;
3507 errbuf := SQLERRM;
3508
3509 END GET_OPI_WIP_COMP_INIT;
3510
3511 /*
3512 Procedure Merges WIP Completions fact for OPM and ODM from Jobs Transaction Staging
3513 table, Incremental Load procedure
3514
3515 Parameters:
3516 retcode - 0 on successful completion, -1 on error and 1 for warning.
3517 errbuf - empty on successful completion, message on error or warning
3518 */
3519
3520 PROCEDURE GET_OPI_WIP_COMP_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
3521 IS
3522 l_stmt_num NUMBER;
3523 l_row_count NUMBER;
3524 l_err_num NUMBER;
3525 l_err_msg VARCHAR2(255);
3526 l_proc_name VARCHAR2(255);
3527 l_status VARCHAR2(30);
3528 l_industry VARCHAR2(30);
3529 l_opi_schema VARCHAR2(30);
3530
3531 BEGIN
3532
3533 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_WIP_COMP_INCR';
3534
3535 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
3536
3537 /* Extraction of WIP Completions fact */
3538 l_stmt_num := 20;
3539 MERGE
3540 INTO OPI_DBI_WIP_COMP_F fact USING
3541 (
3542 select
3543 jobs_txn.organization_id organization_id,
3544 assembly_item_id inventory_item_id,
3545 trunc(jobs_txn.transaction_date) transaction_date,
3546 -sum(primary_quantity+primary_quantity_draft) completion_quantity,
3547 -sum(transaction_value_b+transaction_value_draft_b) completion_value_b,
3548 uom_code uom_code,
3549 crates.conversion_rate conversion_rate,
3550 planned_item planned_item,
3551 source source,
3552 s_sysdate creation_date,
3553 s_sysdate last_update_date,
3554 s_user_id created_by,
3555 s_user_id last_updated_by,
3556 s_login_id last_update_login,
3557 sec_conversion_rate sec_conversion_rate,
3558 job_id job_id,
3559 job_type job_type,
3560 line_type line_type,
3561 -sum(primary_quantity_draft) completion_quantity_draft,
3562 -sum(transaction_value_draft_b) completion_value_draft_b,
3563 -sum((transaction_value_b+transaction_value_draft_b)*crates.conversion_rate) completion_value_g,
3564 -sum(transaction_value_draft_b*crates.conversion_rate) completion_value_draft_g,
3565 -sum((transaction_value_b+transaction_value_draft_b)*crates.sec_conversion_rate) completion_value_sg,
3566 -sum(transaction_value_draft_b*crates.sec_conversion_rate) completion_value_draft_sg,
3567 s_program_id PROGRAM_ID,
3568 s_program_login_id PROGRAM_LOGIN_ID,
3569 s_program_application_id PROGRAM_APPLICATION_ID,
3570 s_request_id REQUEST_ID
3571 from
3572 OPI_DBI_JOBS_TXN_STG jobs_txn,
3573 opi_dbi_muv_conv_rates crates
3574 where
3575 jobs_txn.etl_type_id = 2 and
3576 jobs_txn.organization_id = crates.organization_id and
3577 trunc(jobs_txn.transaction_date) = crates.transaction_date
3578 group by
3579 jobs_txn.organization_id,
3580 jobs_txn.job_id,
3581 jobs_txn.job_type,
3582 jobs_txn.assembly_item_id,
3583 jobs_txn.component_item_id,
3584 jobs_txn.uom_code,
3585 jobs_txn.line_type,
3586 trunc(jobs_txn.transaction_date),
3587 jobs_txn.source,
3588 crates.conversion_rate,
3589 crates.sec_conversion_rate,
3590 planned_item
3591 )stg
3592 ON
3593 (
3594 fact.organization_id = stg.organization_id and
3595 fact.job_id = stg.job_id and
3596 fact.job_type = stg.job_type and
3597 fact.inventory_item_id = stg.inventory_item_id and
3598 fact.transaction_date = stg.transaction_date and
3599 fact.line_type = stg.line_type and
3600 fact.uom_code = stg.uom_code and
3601 fact.source = stg.source
3602 )
3603 WHEN MATCHED THEN
3604 UPDATE SET
3605 fact.completion_quantity = fact.completion_quantity + stg.completion_quantity - fact.completion_quantity_draft,
3606 fact.completion_quantity_draft = stg.completion_quantity_draft,
3607 fact.completion_value_b = fact.completion_value_b + stg.completion_value_b - fact.completion_value_draft_b,
3608 fact.completion_value_draft_b = stg.completion_value_draft_b,
3609 fact.completion_value_g = fact.completion_value_g + stg.completion_value_g - fact.completion_value_draft_g,
3610 fact.completion_value_draft_g = stg.completion_value_draft_g,
3611 fact.completion_value_sg = fact.completion_value_sg + stg.completion_value_sg - fact.completion_value_draft_sg,
3612 fact.completion_value_draft_sg = stg.completion_value_draft_sg,
3613 fact.last_update_date = stg.last_update_date,
3614 fact.last_updated_by = stg.last_updated_by,
3615 fact.last_update_login = stg.last_update_login
3616 WHEN NOT MATCHED THEN
3617 INSERT
3618 (organization_id,
3619 inventory_item_id,
3620 transaction_date,
3621 completion_quantity,
3622 completion_value_b,
3623 uom_code,
3624 conversion_rate,
3625 planned_item,
3626 source,
3627 creation_date,
3628 last_update_date,
3629 created_by,
3630 last_updated_by,
3631 last_update_login,
3632 sec_conversion_rate,
3633 job_id,
3634 job_type,
3635 line_type,
3636 completion_quantity_draft,
3637 completion_value_draft_b,
3638 completion_value_g,
3639 completion_value_draft_g,
3640 completion_value_sg,
3641 completion_value_draft_sg,
3642 PROGRAM_ID,
3643 PROGRAM_LOGIN_ID,
3644 PROGRAM_APPLICATION_ID,
3645 REQUEST_ID
3646 )
3647 VALUES
3648 (
3649 stg.organization_id,
3650 stg.inventory_item_id,
3651 stg.transaction_date,
3652 stg.completion_quantity,
3653 stg.completion_value_b,
3654 stg.uom_code,
3655 stg.conversion_rate,
3656 stg.planned_item,
3657 stg.source,
3658 stg.creation_date,
3659 stg.last_update_date,
3660 stg.created_by,
3661 stg.last_updated_by,
3662 stg.last_update_login,
3663 stg.sec_conversion_rate,
3664 stg.job_id,
3665 stg.job_type,
3666 stg.line_type,
3667 stg.completion_quantity_draft,
3668 stg.completion_value_draft_b,
3669 stg.completion_value_g,
3670 stg.completion_value_draft_g,
3671 stg.completion_value_sg,
3672 stg.completion_value_draft_sg,
3673 stg.PROGRAM_ID,
3674 stg.PROGRAM_LOGIN_ID,
3675 stg.PROGRAM_APPLICATION_ID,
3676 stg.REQUEST_ID
3677 );
3678
3679 l_row_count := sql%rowcount;
3680
3681 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of WIP Completions Fact Table: '|| l_row_count || ' rows inserted');
3682 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
3683
3684 EXCEPTION
3685
3686 WHEN OTHERS THEN
3687
3688 rollback;
3689
3690 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
3691
3692 retcode := SQLCODE;
3693 errbuf := SQLERRM;
3694
3695 END GET_OPI_WIP_COMP_INCR;
3696
3697 /*
3698 Procedure populates Scrap fact for ODM from Jobs Transaction Staging
3699 table, Initial Load procedure
3700
3701 Parameters:
3702 retcode - 0 on successful completion, -1 on error and 1 for warning.
3703 errbuf - empty on successful completion, message on error or warning
3704 */
3705
3706 PROCEDURE GET_OPI_WIP_SCRAP_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
3707 IS
3708 l_stmt_num NUMBER;
3709 l_row_count NUMBER;
3710 l_err_num NUMBER;
3711 l_err_msg VARCHAR2(255);
3712 l_proc_name VARCHAR2(255);
3713 l_status VARCHAR2(30);
3714 l_industry VARCHAR2(30);
3715 l_opi_schema VARCHAR2(30);
3716
3717 BEGIN
3718
3719 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_WIP_SCRAP_INIT';
3720
3721 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
3722
3723 /* Extraction of Scrap fact */
3724 l_stmt_num := 20;
3725 INSERT
3726 INTO OPI_DBI_WIP_SCRAP_F
3727 (
3728 organization_id,
3729 inventory_item_id,
3730 transaction_date,
3731 scrap_quantity,
3732 scrap_value_b,
3733 uom_code,
3734 conversion_rate,
3735 source,
3736 planned_item,
3737 creation_date,
3738 last_update_date,
3739 created_by,
3740 last_updated_by,
3741 last_update_login,
3742 sec_conversion_rate,
3743 job_id,
3744 job_type,
3745 scrap_reason_id,
3746 scrap_value_g,
3747 scrap_value_sg,
3748 PROGRAM_ID,
3749 PROGRAM_LOGIN_ID,
3750 PROGRAM_APPLICATION_ID,
3751 REQUEST_ID
3752 )
3753 select
3754 jobs_txn.organization_id organization_id,
3755 assembly_item_id inventory_item_id,
3756 jobs_txn.transaction_date transaction_date,
3757 -sum(primary_quantity) scrap_quantity,
3758 -sum(transaction_value_b) scrap_value_b,
3759 uom_code uom_code,
3760 crates.conversion_rate conversion_rate,
3761 source source,
3762 planned_item planned_item,
3763 s_sysdate creation_date,
3764 s_sysdate last_update_date,
3765 s_user_id created_by,
3766 s_user_id last_updated_by,
3767 s_login_id last_update_login,
3768 crates.sec_conversion_rate sec_conversion_rate,
3769 job_id job_id,
3770 job_type job_type,
3771 scrap_reason scrap_reason_id,
3772 -sum(transaction_value_b*crates.conversion_rate) scrap_value_g,
3773 -sum(transaction_value_b*crates.sec_conversion_rate) scrap_value_sg,
3774 s_program_id PROGRAM_ID,
3775 s_program_login_id PROGRAM_LOGIN_ID,
3776 s_program_application_id PROGRAM_APPLICATION_ID,
3777 s_request_id REQUEST_ID
3778 FROM
3779 OPI_DBI_JOBS_TXN_STG jobs_txn,
3780 opi_dbi_muv_conv_rates crates
3781 WHERE
3782 etl_type_id = 3 and
3783 jobs_txn.organization_id = crates.organization_id and
3784 trunc(jobs_txn.transaction_date) = crates.transaction_date
3785 GROUP BY
3786 jobs_txn.organization_id,
3787 assembly_item_id,
3788 jobs_txn.transaction_date,
3789 uom_code,
3790 crates.conversion_rate,
3791 source,
3792 planned_item,
3793 crates.sec_conversion_rate,
3794 job_id,
3795 job_type,
3796 scrap_reason;
3797
3798 l_row_count := sql%rowcount;
3799
3800 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of Scrap Fact Table: '|| l_row_count || ' rows inserted');
3801 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
3802
3803 EXCEPTION
3804
3805 WHEN OTHERS THEN
3806
3807 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
3808
3809 retcode := SQLCODE;
3810 errbuf := SQLERRM;
3811
3812 END GET_OPI_WIP_SCRAP_INIT;
3813
3814 /*
3815 Procedure Merges Scrap fact for ODM from Jobs Transaction Staging
3816 table, Incremental Load procedure
3817
3818 Parameters:
3819 retcode - 0 on successful completion, -1 on error and 1 for warning.
3820 errbuf - empty on successful completion, message on error or warning
3821 */
3822
3823 PROCEDURE GET_OPI_WIP_SCRAP_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
3824 IS
3825 l_stmt_num NUMBER;
3826 l_row_count NUMBER;
3827 l_err_num NUMBER;
3828 l_err_msg VARCHAR2(255);
3829 l_proc_name VARCHAR2(255);
3830 l_status VARCHAR2(30);
3831 l_industry VARCHAR2(30);
3832 l_opi_schema VARCHAR2(30);
3833
3834 BEGIN
3835
3836 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_WIP_SCRAP_INCR';
3837
3838 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
3839
3840 /* Extraction of Scrap fact */
3841 l_stmt_num := 20;
3842 MERGE
3843 INTO OPI_DBI_WIP_SCRAP_F fact USING
3844 (
3845 select
3846 jobs_txn.organization_id organization_id,
3847 assembly_item_id inventory_item_id,
3848 jobs_txn.transaction_date transaction_date,
3849 -sum(primary_quantity) scrap_quantity,
3850 -sum(transaction_value_b) scrap_value_b,
3851 uom_code uom_code,
3852 crates.conversion_rate conversion_rate,
3853 source source,
3854 planned_item planned_item,
3855 s_sysdate creation_date,
3856 s_sysdate last_update_date,
3857 s_user_id created_by,
3858 s_user_id last_updated_by,
3859 s_login_id last_update_login,
3860 crates.sec_conversion_rate sec_conversion_rate,
3861 job_id job_id,
3862 job_type job_type,
3863 scrap_reason scrap_reason_id,
3864 -sum(transaction_value_b*crates.conversion_rate) scrap_value_g,
3865 -sum(transaction_value_b*crates.sec_conversion_rate) scrap_value_sg,
3866 s_program_id PROGRAM_ID,
3867 s_program_login_id PROGRAM_LOGIN_ID,
3868 s_program_application_id PROGRAM_APPLICATION_ID,
3869 s_request_id REQUEST_ID
3870 FROM
3871 OPI_DBI_JOBS_TXN_STG jobs_txn,
3872 opi_dbi_muv_conv_rates crates
3873 WHERE
3874 etl_type_id = 3 and
3875 jobs_txn.organization_id = crates.organization_id and
3876 trunc(jobs_txn.transaction_date) = crates.transaction_date
3877 GROUP BY
3878 jobs_txn.organization_id,
3879 assembly_item_id,
3880 jobs_txn.transaction_date,
3881 uom_code,
3882 crates.conversion_rate,
3883 source,
3884 planned_item,
3885 crates.sec_conversion_rate,
3886 job_id,
3887 job_type,
3888 scrap_reason
3889 )stg
3890 ON
3891 ( fact.organization_id = stg.organization_id and
3892 fact.job_id = stg.job_id and
3893 fact.job_type = stg.job_type and
3894 fact.inventory_item_id = stg.inventory_item_id and
3895 fact.transaction_date = stg.transaction_date and
3896 fact.uom_code = stg.uom_code and
3897 fact.scrap_reason_id = stg.scrap_reason_id and
3898 fact.source = stg.source
3899 )
3900 WHEN MATCHED THEN
3901 UPDATE SET
3902 fact.scrap_quantity = stg.scrap_quantity,
3903 fact.scrap_value_b = stg.scrap_value_b,
3904 fact.scrap_value_g = stg.scrap_value_g,
3905 fact.scrap_value_sg = stg.scrap_value_sg,
3906 fact.last_update_date = stg.last_update_date,
3907 fact.last_updated_by = stg.last_updated_by,
3908 fact.last_update_login = stg.last_update_login
3909 WHEN NOT MATCHED THEN
3910 INSERT
3911 (organization_id,
3912 inventory_item_id,
3913 transaction_date,
3914 scrap_quantity,
3915 scrap_value_b,
3916 uom_code,
3917 conversion_rate,
3918 source,
3919 planned_item,
3920 creation_date,
3921 last_update_date,
3922 created_by,
3923 last_updated_by,
3924 last_update_login,
3925 sec_conversion_rate,
3926 job_id,
3927 job_type,
3928 scrap_reason_id,
3929 scrap_value_g,
3930 scrap_value_sg,
3931 PROGRAM_ID,
3932 PROGRAM_LOGIN_ID,
3933 PROGRAM_APPLICATION_ID,
3934 REQUEST_ID
3935 )
3936 VALUES
3937 (
3938 stg.organization_id,
3939 stg.inventory_item_id,
3940 stg.transaction_date,
3941 stg.scrap_quantity,
3942 stg.scrap_value_b,
3943 stg.uom_code,
3944 stg.conversion_rate,
3945 stg.source,
3946 stg.planned_item,
3947 stg.creation_date,
3948 stg.last_update_date,
3949 stg.created_by,
3950 stg.last_updated_by,
3951 stg.last_update_login,
3952 stg.sec_conversion_rate,
3953 stg.job_id,
3954 stg.job_type,
3955 stg.scrap_reason_id,
3956 stg.scrap_value_g,
3957 stg.scrap_value_sg,
3958 stg.PROGRAM_ID,
3959 stg.PROGRAM_LOGIN_ID,
3960 stg.PROGRAM_APPLICATION_ID,
3961 stg.REQUEST_ID
3962 );
3963
3964 l_row_count := sql%rowcount;
3965
3966 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of Scrap Fact Table: '|| l_row_count || ' rows inserted');
3967 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
3968
3969 EXCEPTION
3970
3971 WHEN OTHERS THEN
3972
3973 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
3974
3975 retcode := SQLCODE;
3976 errbuf := SQLERRM;
3977
3978 END GET_OPI_WIP_SCRAP_INCR;
3979
3980 /*
3981 Procedure to truncate all temp and staging tables
3982
3983 Parameters:
3984 retcode - 0 on successful completion, -1 on error and 1 for warning.
3985 errbuf - empty on successful completion, message on error or warning
3986 */
3987
3988 PROCEDURE OPI_TRUNC_TEMP_TBLS(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
3989 IS
3990 l_stmt_num NUMBER;
3991 l_row_count NUMBER;
3992 l_err_num NUMBER;
3993 l_err_msg VARCHAR2(255);
3994 l_proc_name VARCHAR2(255);
3995 l_status VARCHAR2(30);
3996 l_industry VARCHAR2(30);
3997 l_opi_schema VARCHAR2(30);
3998
3999 BEGIN
4000
4001 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.OPI_TRUNC_TEMP_TBLS';
4002
4003 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
4004
4005 /* truncate all tables */
4006 l_stmt_num := 10;
4007 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
4008 --{
4009 --execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_SCALED_MTL';
4010 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOB_MTL_STD_QTY_TMP';
4011 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOBS_TXN_STG';
4012 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOBS_TXN_MMT_STG';
4013 execute immediate 'truncate table ' || l_opi_schema || '.opi_dbi_muv_conv_rates';
4014 --}
4015 END IF;
4016
4017 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
4018
4019 EXCEPTION
4020
4021 WHEN OTHERS THEN
4022
4023 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
4024
4025 retcode := SQLCODE;
4026 errbuf := SQLERRM;
4027
4028 END OPI_TRUNC_TEMP_TBLS;
4029
4030 /*
4031 Procedure to truncate all fact tables
4032
4033 Parameters:
4034 retcode - 0 on successful completion, -1 on error and 1 for warning.
4035 errbuf - empty on successful completion, message on error or warning
4036 */
4037
4038 PROCEDURE OPI_TRUNC_MFG_FACT_TBLS(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
4039 IS
4040 l_stmt_num NUMBER;
4041 l_row_count NUMBER;
4042 l_err_num NUMBER;
4043 l_err_msg VARCHAR2(255);
4044 l_proc_name VARCHAR2(255);
4045 l_status VARCHAR2(30);
4046 l_industry VARCHAR2(30);
4047 l_opi_schema VARCHAR2(30);
4048
4049 BEGIN
4050
4051 l_proc_name := 'OPI_DBI_JOB_TRN_STG_PKG.OPI_TRUNC_MFG_FACT_TBLS';
4052
4053 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
4054
4055 /* truncate all tables */
4056 l_stmt_num := 10;
4057 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
4058 --{
4059 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOB_MTL_DETAILS_F';
4060 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOB_MTL_DTL_STD_F';
4061 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_WIP_COMP_F';
4062 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_WIP_SCRAP_F';
4063
4064 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOB_MTL_DETAILS_F PURGE MATERIALIZED VIEW LOG';
4065 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOB_MTL_DTL_STD_F PURGE MATERIALIZED VIEW LOG';
4066 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_WIP_COMP_F PURGE MATERIALIZED VIEW LOG';
4067 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_WIP_SCRAP_F PURGE MATERIALIZED VIEW LOG';
4068 --}
4069 END IF;
4070
4071 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
4072
4073 EXCEPTION
4074
4075 WHEN OTHERS THEN
4076
4077 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_num));
4078
4079 retcode := SQLCODE;
4080 errbuf := SQLERRM;
4081
4082 END OPI_TRUNC_MFG_FACT_TBLS;
4083
4084 /*
4085 Procedure Wrapup called after successful collection in all fact tables.
4086
4087 Parameters:
4088 retcode - 0 on successful completion, -1 on error and 1 for warning.
4089 errbuf - empty on successful completion, message on error or warning
4090 */
4091
4092 PROCEDURE OPI_JOB_TXN_WRAPUP(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
4093 IS
4094 l_stmt_num NUMBER;
4095 l_row_count NUMBER;
4096 l_err_num NUMBER;
4097 l_err_msg VARCHAR2(255);
4098 l_proc_name VARCHAR2(255);
4099 BEGIN
4100
4101 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.OPI_JOB_TXN_WRAPUP';
4102
4103 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
4104
4105 l_stmt_num := 10;
4106 IF(opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(1,1) = FALSE
4107 OR opi_dbi_common_mod_incr_pkg.ETL_REPORT_SUCCESS(1,2) = FALSE) THEN
4108 --{
4109 BIS_COLLECTION_UTILITIES.put_line('Failed to store current run time bounds and new run time bounds for next run.');
4110 RAISE_APPLICATION_ERROR(-20000, errbuf);
4111 --}
4112 END IF;
4113
4114 /* Access MMT and obtain the list of jobs that have WIP material transactions
4115 after the first uncosted transaction and use this list of jobs and update
4116 Include_Jobs in Jobs Table with 2 and leave the rest with 1 */
4117 l_stmt_num := 20;
4118 UPDATE /*+ parallel(f) */ OPI_DBI_JOBS_F f
4119 SET
4120 Include_Job = 2
4121 WHERE
4122 JOB_ID IN (
4123 SELECT /*+ ordered use_nl(mmt,mmta) index(log) parallel(mmt) parallel(mmta) */
4124 distinct decode(mmta.primary_quantity, null, mmt.transaction_source_id, mmta.repetitive_schedule_id) JOB_ID
4125 FROM
4126 MTL_MATERIAL_TRANSACTIONS MMT,
4127 mtl_material_txn_allocations mmta,
4128 OPI_DBI_RUN_LOG_CURR log
4129 WHERE
4130 MMT.TRANSACTION_ID >= log.Next_start_txn_id
4131 and mmt.transaction_action_id in (1, 27)
4132 and mmt.transaction_source_type_id = 5
4133 and mmt.transaction_id = mmta.transaction_id (+)
4134 and mmt.organization_id = log.organization_id
4135 and log.source = 1
4136 and log.etl_id = 1
4137 )
4138 AND SOURCE <> 2; /* Do not update OPM Jobs here */
4139
4140 l_stmt_num := 30;
4141 commit;
4142
4143 l_stmt_num := 40;
4144 UPDATE /*+ parallel(f) */ OPI_DBI_JOBS_F f
4145 SET Std_Req_Flag = 0
4146 WHERE Std_Req_Flag = 1 AND SOURCE=1;
4147 commit;
4148
4149 /* VB I forgot the reason why we are refreshing the MV in our code rather than in the RS like other MVs - please review and document here */
4150 /* Refresh base MV for MUV*/
4151 l_stmt_num := 50;
4152 --REFRESH_BASE_MV(errbuf,retcode,'C');
4153
4154 EXCEPTION
4155
4156 WHEN OTHERS THEN
4157 rollback;
4158 l_err_num := SQLCODE;
4159 l_err_msg := 'OPI_DBI_JOB_TXN_STG_PKG.OPI_JOB_TXN_WRAPUP ('
4160 || to_char(l_stmt_num)
4161 || '): '
4162 || substr(SQLERRM, 1,200);
4163 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOB_TXN_STG_PKG.OPI_JOB_TXN_WRAPUP - Error at statement ('
4164 || to_char(l_stmt_num)
4165 || ')');
4166 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
4167 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
4168 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
4169
4170 retcode := SQLCODE;
4171 errbuf := SQLERRM;
4172 RAISE_APPLICATION_ERROR(-20000, errbuf);
4173
4174 END OPI_JOB_TXN_WRAPUP;
4175
4176 /*
4177 Procedure checks for required setups.
4178
4179 Parameters:
4180 retcode - 0 on successful completion, -1 on error and 1 for warning.
4181 errbuf - empty on successful completion, message on error or warning
4182 */
4183
4184 PROCEDURE CHECK_OPI_JOB_TXN_SETUP(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2,
4185 init_incr in NUMBER)
4186 IS
4187 l_stmt_num NUMBER;
4188 l_row_count NUMBER;
4189 l_err_num NUMBER;
4190 l_err_msg VARCHAR2(255);
4191 l_proc_name VARCHAR2(255);
4192 BEGIN
4193
4194 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.CHECK_OPI_JOB_TXN_SETUP';
4195
4196 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
4197
4198 /* calling setup for all fact tables */
4199 l_stmt_num := 10;
4200
4201 IF BIS_COLLECTION_UTILITIES.SETUP('OPI_DBI_JOB_MTL_DETAILS_F') = false then
4202 RAISE_APPLICATION_ERROR(-20000, errbuf);
4203 END IF;
4204
4205 IF BIS_COLLECTION_UTILITIES.SETUP('OPI_DBI_JOB_MTL_DTL_STD_F') = false then
4206 RAISE_APPLICATION_ERROR(-20000, errbuf);
4207 END IF;
4208
4209 IF BIS_COLLECTION_UTILITIES.SETUP('OPI_DBI_WIP_COMP_F') = false then
4210 RAISE_APPLICATION_ERROR(-20000, errbuf);
4211 END IF;
4212
4213 IF BIS_COLLECTION_UTILITIES.SETUP('OPI_DBI_WIP_SCRAP_F') = false then
4214 RAISE_APPLICATION_ERROR(-20000, errbuf);
4215 END IF;
4216
4217 /* calling common modules bounds check */
4218 l_stmt_num := 20;
4219 IF (init_incr = 1) THEN
4220 --{
4221 IF (opi_dbi_common_mod_init_pkg.init_end_bounds_setup (1, 1) = FALSE OR
4222 opi_dbi_common_mod_init_pkg.init_end_bounds_setup (1, 2) = FALSE) THEN
4223 --{
4224
4225 BIS_COLLECTION_UTILITIES.put_line('Initial load concurrent program is running out of turn. ');
4226 BIS_COLLECTION_UTILITIES.put_line('Please submit the initial load request set for initial data collection. ');
4227
4228 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
4229 retcode := SQLCODE;
4230 errbuf := SQLERRM;
4231 RAISE_APPLICATION_ERROR(-20000, errbuf);
4232 --}
4233 END IF;
4234 --}
4235 ELSE
4236 --{
4237 l_stmt_num := 22;
4238 IF opi_dbi_common_mod_incr_pkg.incr_end_bounds_setup (1, 1) = FALSE OR
4239 opi_dbi_common_mod_incr_pkg.incr_end_bounds_setup (1, 2) = FALSE THEN
4240 --{
4241 BIS_COLLECTION_UTILITIES.put_line('Incremental load concurrent program is running out of turn. ');
4242 BIS_COLLECTION_UTILITIES.put_line('Please submit the incremental load request set for incremental data collection. ');
4243
4244 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
4245 retcode := SQLCODE;
4246 errbuf := SQLERRM;
4247 RAISE_APPLICATION_ERROR(-20000, errbuf);
4248 --}
4249 END IF;
4250 --}
4251 END IF;
4252
4253 l_stmt_num := 25;
4254 IF (init_incr = 1) THEN
4255 --{
4256 IF (opi_dbi_common_mod_init_pkg.run_initial_load (1, 1) = FALSE OR
4257 opi_dbi_common_mod_init_pkg.run_initial_load (1, 2) = FALSE) THEN
4258 --{
4259 BIS_COLLECTION_UTILITIES.put_line('Initial load concurrent program should not be running. ');
4260 BIS_COLLECTION_UTILITIES.put_line('Try running the incremental load request set if the initial request set has already been run. ');
4261 BIS_COLLECTION_UTILITIES.put_line('If not, you will need to run the initial load request set.');
4262
4263 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
4264 retcode := SQLCODE;
4265 errbuf := SQLERRM;
4266 RAISE_APPLICATION_ERROR(-20000, errbuf);
4267 --}
4268 END IF;
4269 --}
4270 ELSE
4271 --{
4272 l_stmt_num := 27;
4273 IF opi_dbi_common_mod_incr_pkg.run_incr_load (1, 1) = FALSE OR
4274 opi_dbi_common_mod_incr_pkg.run_incr_load (1, 2) = FALSE THEN
4275 --{
4276 BIS_COLLECTION_UTILITIES.put_line('Incremental load concurrent program should not be running. ');
4277 BIS_COLLECTION_UTILITIES.put_line('If the initial load request set has already been run successfully, please submit the incremental load request set. ');
4278 BIS_COLLECTION_UTILITIES.put_line('If not, please run the initial load request set. ');
4279
4280 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
4281 retcode := SQLCODE;
4282 errbuf := SQLERRM;
4283 RAISE_APPLICATION_ERROR(-20000, errbuf);
4284 --}
4285 END IF;
4286 --}
4287 END IF;
4288
4289 /* get global start date */
4290 l_stmt_num := 30;
4291 s_global_start_date := trunc (bis_common_parameters.get_global_start_date);
4292 IF (s_global_start_date IS NULL) THEN
4293 BIS_COLLECTION_UTILITIES.PUT_LINE ('The global Start date Not Set.');
4294
4295 RAISE_APPLICATION_ERROR(-20000, errbuf);
4296 END IF;
4297
4298 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Start Date:'||s_global_start_date);
4299
4300 l_stmt_num := 40;
4301 -- Global currency codes -- already checked if primary is set up
4302 g_global_currency_code := bis_common_parameters.get_currency_code;
4303 g_secondary_currency_code := bis_common_parameters.get_secondary_currency_code;
4304
4305 -- Global rate types -- already checked if primary is set up
4306 g_global_rate_type := bis_common_parameters.get_rate_type;
4307 g_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
4308
4309 -- check that either both the secondary rate type and secondary
4310 -- rate are null, or that neither are null.
4311 IF ((g_secondary_currency_code IS NULL AND
4312 g_secondary_rate_type IS NOT NULL)
4313 OR
4314 (g_secondary_currency_code IS NOT NULL AND
4315 g_secondary_rate_type IS NULL)
4316 ) THEN
4317 --{
4318 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.');
4319
4320 RAISE_APPLICATION_ERROR(-20000, errbuf);
4321 --}
4322 END IF;
4323
4324 l_stmt_num := 50;
4325 -- get R12 upgrade date
4326 /* If Migration Sate is not setup the api will return sysdate */
4327 OPI_DBI_RPT_UTIL_PKG.get_inv_convergence_date(s_r12_migration_date);
4328
4329 BIS_COLLECTION_UTILITIES.PUT_LINE('R12 Migration Date: '|| s_r12_migration_date);
4330
4331 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
4332
4333 EXCEPTION
4334
4335 WHEN OTHERS THEN
4336 rollback;
4337 l_err_num := SQLCODE;
4338 l_err_msg := 'OPI_DBI_JOB_TXN_STG_PKG.CHECK_OPI_JOB_TXN_SETUP ('
4339 || to_char(l_stmt_num)
4340 || '): '
4341 || substr(SQLERRM, 1,200);
4342 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOB_TXN_STG_PKG.CHECK_OPI_JOB_TXN_SETUP - Error at statement ('
4343 || to_char(l_stmt_num)
4344 || ')');
4345 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
4346 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
4347 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
4348
4349 retcode := SQLCODE;
4350 errbuf := SQLERRM;
4351 RAISE_APPLICATION_ERROR(-20000, errbuf);
4352
4353 END CHECK_OPI_JOB_TXN_SETUP;
4354
4355 /*
4356 Public Procedure to refresh MUV, Scrap, and WIP Completions MV
4357
4358 Parameters:
4359 retcode - 0 on successful completion, -1 on error and 1 for warning.
4360 errbuf - empty on successful completion, message on error or warning
4361 p_method
4362 */
4363
4364 PROCEDURE REFRESH_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
4365 IS
4366 l_stmt_num NUMBER;
4367 l_err_num NUMBER;
4368 l_err_msg VARCHAR2(255);
4369 BEGIN
4370
4371 l_stmt_num := 10;
4372 /* Material Details MV Refresh */
4373
4374 /* VB WHy are we refresing MVs here rather than in RSG */
4375 DBMS_MVIEW.REFRESH ('OPI_MTL_VAR_ACT_MV_F', '?');
4376
4377 dbms_mview.refresh('OPI_MTL_VAR_SUM_MV',
4378 '?',
4379 '', -- ROLLBACK_SEG
4380 TRUE, -- PUSH_DEFERRED_RPC
4381 FALSE, -- REFRESH_AFTER_ERRORS
4382 0, -- PURGE_OPTION
4383 1, -- PARALLELISM
4384 0, -- HEAP_SIZE
4385 FALSE -- ATOMIC_REFRESH
4386 );
4387
4388 BIS_COLLECTION_UTILITIES.PUT_LINE('Material Details MV Refresh finished ...');
4389
4390 l_stmt_num := 20;
4391 /* Scrap MV Refresh */
4392
4393 -- First Level MV
4394 DBMS_MVIEW.REFRESH ('opi_comp_scr_mv', '?');
4395
4396 l_stmt_num := 24;
4397 -- Second Level MV
4398 DBMS_MVIEW.REFRESH ('opi_prod_scr_mv', '?');
4399
4400 l_stmt_num := 28;
4401 -- Third Level MV
4402 DBMS_MVIEW.REFRESH ('opi_scrap_sum_mv', '?');
4403
4404 BIS_COLLECTION_UTILITIES.PUT_LINE('Scrap Refresh finished ...');
4405
4406 l_stmt_num := 32;
4407 /* Refresh MV over WIP Completions fact for On Time production */
4408 dbms_mview.refresh('OPI_ONTIME_PROD_OO1_MV',
4409 '?',
4410 '', -- ROLLBACK_SEG
4411 TRUE, -- PUSH_DEFERRED_RPC
4412 FALSE, -- REFRESH_AFTER_ERRORS
4413 0, -- PURGE_OPTION
4414 1, -- PARALLELISM
4415 0, -- HEAP_SIZE
4416 FALSE -- ATOMIC_REFRESH
4417 );
4418
4419 BIS_COLLECTION_UTILITIES.PUT_LINE('WIP Completions Refresh finished ...');
4420
4421 EXCEPTION
4422 WHEN OTHERS THEN
4423
4424 l_err_num := SQLCODE;
4425 l_err_msg := 'OPI_DBI_JOB_TXN_STG_PKG.REFRESH_MV ('
4426 || to_char(l_stmt_num)
4427 || '): '
4428 || substr(SQLERRM, 1,200);
4429
4430 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOB_TXN_STG_PKG.REFRESH_MV - Error at statement ('
4431 || to_char(l_stmt_num)
4432 || ')');
4433
4434 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
4435 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
4436
4437 RAISE_APPLICATION_ERROR(-20000, errbuf);
4438 /*please note that this api will commit!!*/
4439
4440 END REFRESH_MV;
4441
4442 /*
4443 Public Procedure Wrapper routine for Initial Load
4444
4445 Parameters:
4446 retcode - 0 on successful completion, -1 on error and 1 for warning.
4447 errbuf - empty on successful completion, message on error or warning
4448 */
4449
4450 PROCEDURE GET_OPI_JOB_TXN_MUV_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
4451 IS
4452 l_stmt_num NUMBER;
4453 l_row_count NUMBER;
4454 l_err_num NUMBER;
4455 l_err_msg VARCHAR2(255);
4456 l_proc_name VARCHAR2(255);
4457 r12upgrade_date DATE;
4458
4459 BEGIN
4460
4461 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_MUV_INIT';
4462
4463 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
4464
4465 -- WHO column variable initialization
4466 l_stmt_num := 0;
4467 s_sysdate := SYSDATE;
4468 s_user_id := nvl(fnd_global.user_id, -1);
4469 s_login_id := nvl(fnd_global.login_id, -1);
4470
4471 --Check Setup
4472 l_stmt_num := 5;
4473 CHECK_OPI_JOB_TXN_SETUP(errbuf => errbuf,retcode => retcode, init_incr => 1);
4474
4475 l_stmt_num := 7;
4476 --Truncate all temp, staging and fact tables.
4477 OPI_TRUNC_TEMP_TBLS(errbuf => errbuf,retcode => retcode);
4478 OPI_TRUNC_MFG_FACT_TBLS(errbuf => errbuf,retcode => retcode);
4479
4480 --Populate MMT Staging
4481 l_stmt_num := 10;
4482 GET_OPI_JOB_TXN_MMT_STG(errbuf => errbuf,retcode => retcode);
4483
4484 --Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table
4485 l_stmt_num := 15;
4486 BIS_COLLECTION_UTILITIES.PUT_LINE('Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table');
4487 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
4488
4489 --Populate Jobs Txn Staging with ODM data
4490 l_stmt_num := 20;
4491 GET_OPI_JOB_TXN_ODM_INIT(errbuf => errbuf,retcode => retcode);
4492
4493 --Populate Jobs Txn Staging with OPM data
4494 l_stmt_num := 30;
4495 GET_OPI_JOB_TXN_OPM_INIT(errbuf => errbuf,retcode => retcode);
4496
4497 --Populate Scaled MTL Table for OPM
4498 l_stmt_num := 40;
4499 GET_OPI_SCALED_MTL_INIT(errbuf => errbuf,retcode => retcode);
4500
4501 --Populate Jobs Txn Staging Table for Pre R12 Data
4502 l_stmt_num := 50;
4503 IF s_r12_migration_date > s_global_start_date
4504 THEN
4505 --{
4506
4507 GET_OPI_JOB_TXN_PR12OPM_INIT(errbuf => errbuf,retcode => retcode);
4508
4509 --}
4510 END IF;
4511
4512 --Check For Missing Currency Rates
4513 l_stmt_num := 60;
4514 IF(GET_OPI_JOB_TXN_CRATES(errbuf,retcode) = -1 ) THEN
4515 --{
4516 BIS_COLLECTION_UTILITIES.put_line('Missing currency rate.');
4517 BIS_COLLECTION_UTILITIES.put_line('Please run this concurrent program again after fixing the missing currency rates.');
4518 RAISE_APPLICATION_ERROR(-20000, errbuf);
4519 --}
4520 END IF;
4521
4522 --Populate MU Actuals to fact Table
4523 l_stmt_num := 70;
4524 GET_OPI_MTL_USAGE_ACT_INIT(errbuf => errbuf,retcode => retcode);
4525
4526 --Populate ODM MU Standards to fact Table
4527 l_stmt_num := 80;
4528 GET_OPI_ODM_MTL_USAGE_STD_INIT(errbuf => errbuf,retcode => retcode);
4529
4530 --Populate OPM MU Standards to fact Table
4531 l_stmt_num := 90;
4532 GET_OPI_OPM_MTL_USAGE_STD_INIT(errbuf => errbuf,retcode => retcode);
4533
4534 --Populate WIP Completions Fact
4535 l_stmt_num := 100;
4536 GET_OPI_WIP_COMP_INIT(errbuf => errbuf,retcode => retcode);
4537
4538 --Populate Scrap Completions Fact
4539 l_stmt_num := 110;
4540 GET_OPI_WIP_SCRAP_INIT(errbuf => errbuf,retcode => retcode);
4541
4542 --Truncate all temp and staging tables
4543 l_stmt_num := 120;
4544 OPI_TRUNC_TEMP_TBLS(errbuf => errbuf,retcode => retcode);
4545
4546 --Calling Wrapup procedure
4547 l_stmt_num := 130;
4548 OPI_JOB_TXN_WRAPUP(errbuf => errbuf,retcode => retcode);
4549
4550 commit;
4551
4552 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
4553
4554 EXCEPTION
4555
4556 WHEN OTHERS THEN
4557 rollback;
4558 l_err_num := SQLCODE;
4559 l_err_msg := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_MUV_INIT ('
4560 || to_char(l_stmt_num)
4561 || '): '
4562 || substr(SQLERRM, 1,200);
4563 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_MUV_INIT - Error at statement ('
4564 || to_char(l_stmt_num)
4565 || ')');
4566 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
4567 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
4568 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
4569
4570 retcode := SQLCODE;
4571 errbuf := SQLERRM;
4572 RAISE_APPLICATION_ERROR(-20000, errbuf);
4573
4574 END GET_OPI_JOB_TXN_MUV_INIT;
4575
4576 /*
4577 Public Procedure Wrapper routine for Initial Load
4578
4579 Parameters:
4580 retcode - 0 on successful completion, -1 on error and 1 for warning.
4581 errbuf - empty on successful completion, message on error or warning
4582 */
4583
4584 PROCEDURE GET_OPI_JOB_TXN_MUV_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
4585 IS
4586 l_stmt_num NUMBER;
4587 l_row_count NUMBER;
4588 l_err_num NUMBER;
4589 l_err_msg VARCHAR2(255);
4590 l_proc_name VARCHAR2(255);
4591
4592 BEGIN
4593
4594 l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_MUV_INCR';
4595
4596 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
4597
4598 -- WHO column variable initialization
4599 l_stmt_num := 0;
4600 s_sysdate := SYSDATE;
4601 s_user_id := nvl(fnd_global.user_id, -1);
4602 s_login_id := nvl(fnd_global.login_id, -1);
4603
4604 --Check Setup
4605 l_stmt_num := 10;
4606 CHECK_OPI_JOB_TXN_SETUP(errbuf => errbuf,retcode => retcode, init_incr => 2);
4607
4608 l_stmt_num := 15;
4609 --Truncate all temp, staging tables.
4610 OPI_TRUNC_TEMP_TBLS(errbuf => errbuf,retcode => retcode);
4611
4612 --Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table
4613 l_stmt_num := 17;
4614 BIS_COLLECTION_UTILITIES.PUT_LINE('Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table');
4615 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
4616
4617 --Populate Jobs Txn Staging with ODM data
4618 l_stmt_num := 20;
4619 GET_OPI_JOB_TXN_ODM_INCR(errbuf => errbuf,retcode => retcode);
4620
4621 --Populate Jobs Txn Staging with OPM data
4622 l_stmt_num := 30;
4623 GET_OPI_JOB_TXN_OPM_INCR(errbuf => errbuf,retcode => retcode);
4624
4625 --Populate Scaled MTL Table for OPM
4626 l_stmt_num := 40;
4627 GET_OPI_SCALED_MTL_INCR(errbuf => errbuf,retcode => retcode);
4628
4629 --Check For Missing Currency Rates
4630 l_stmt_num := 60;
4631 IF(GET_OPI_JOB_TXN_CRATES(errbuf => errbuf,retcode => retcode) = -1 ) THEN
4632 --{
4633 BIS_COLLECTION_UTILITIES.put_line('Missing currency rate.');
4634 BIS_COLLECTION_UTILITIES.put_line('Please run this concurrent program again after fixing the missing currency rates.');
4635 RAISE_APPLICATION_ERROR(-20000, errbuf);
4636 --}
4637 END IF;
4638
4639 --Populate MU Actuals to fact Table
4640 l_stmt_num := 70;
4641 GET_OPI_MTL_USAGE_ACT_INCR(errbuf => errbuf,retcode => retcode);
4642
4643 --Populate ODM MU Standards to fact Table
4644 l_stmt_num := 80;
4645 GET_OPI_ODM_MTL_USAGE_STD_INCR(errbuf => errbuf,retcode => retcode);
4646
4647 --Populate OPM MU Standards to fact Table
4648 l_stmt_num := 90;
4649 GET_OPI_OPM_MTL_USAGE_STD_INCR(errbuf => errbuf,retcode => retcode);
4650
4651 --Populate WIP Completions Fact
4652 l_stmt_num := 100;
4653 GET_OPI_WIP_COMP_INCR(errbuf => errbuf,retcode => retcode);
4654
4655 --Populate Scrap Completions Fact
4656 l_stmt_num := 110;
4657 GET_OPI_WIP_SCRAP_INCR(errbuf => errbuf,retcode => retcode);
4658
4659 --Truncate all temp and staging tables
4660 l_stmt_num := 120;
4661 OPI_TRUNC_TEMP_TBLS(errbuf => errbuf,retcode => retcode);
4662
4663 --Calling Wrapup procedure
4664 l_stmt_num := 130;
4665 OPI_JOB_TXN_WRAPUP(errbuf => errbuf,retcode => retcode);
4666
4667 commit;
4668
4669 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
4670
4671 EXCEPTION
4672
4673 WHEN OTHERS THEN
4674 rollback;
4675 l_err_num := SQLCODE;
4676 l_err_msg := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_MUV_INCR ('
4677 || to_char(l_stmt_num)
4678 || '): '
4679 || substr(SQLERRM, 1,200);
4680 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_JOB_TXN_MUV_INCR - Error at statement ('
4681 || to_char(l_stmt_num)
4682 || ')');
4683 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
4684 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
4685 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
4686
4687 retcode := SQLCODE;
4688 errbuf := SQLERRM;
4689 RAISE_APPLICATION_ERROR(-20000, errbuf);
4690
4691 END GET_OPI_JOB_TXN_MUV_INCR;
4692
4693 END OPI_DBI_JOB_TXN_STG_PKG;