[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_MTL_VARIANCE_PKG
Source
1 Package Body OPI_DBI_MTL_VARIANCE_PKG AS
2 /*$Header: OPIDMUVETLB.pls 120.24 2006/09/21 00:38:11 asparama noship $ */
3
4 -- DBI GSD
5 g_global_start_date DATE;
6 g_r12_migration_date DATE;
7
8 g_refresh_bmv BOOLEAN := TRUE;
9
10 -- For reporting rows touched by certain SQLs
11 g_row_count NUMBER;
12
13 -- WHO column information
14 g_sysdate DATE := SYSDATE;
15 g_user_id NUMBER := nvl(fnd_global.user_id, -1);
16 g_login_id NUMBER := nvl(fnd_global.login_id, -1);
17 g_last_collection_date DATE;
18 g_program_id NUMBER;
19 g_program_login_id NUMBER;
20 g_program_application_id NUMBER;
21 g_request_id NUMBER;
22
23 -- Currency code related file scope variables
24 g_global_currency_code VARCHAR2(10);
25 g_secondary_currency_code VARCHAR2 (10);
26 g_global_rate_type VARCHAR2(15);
27 g_secondary_rate_type VARCHAR2 (15);
28
29 -- Missing rate related constants
30 /* Marker for secondary conv. rate if the primary and secondary curr codes
31 and rate types are identical. Can't be -1, -2, -3 since the FII APIs
32 return those values. */
33 C_PRI_SEC_CURR_SAME_MARKER CONSTANT NUMBER := -9999;
34
35 -- GL API returns -3 if EURO rate missing on 01-JAN-1999
36 C_EURO_MISSING_AT_START CONSTANT NUMBER := -3;
37
38 -- Start date of Euro currency
39 g_euro_start_date CONSTANT DATE := to_date('01/01/1999','DD/MM/YYYY');
40
41 -- Program return codes
42 g_ok CONSTANT NUMBER(1) := 0;
43 g_warning CONSTANT NUMBER(1) := 1;
44 g_error CONSTANT NUMBER(1) := -1;
45
46 -- OPI schema parameters
47 g_opi_schema VARCHAR2(30);
48 g_opi_status VARCHAR2(30);
49 g_opi_industry VARCHAR2(30);
50
51 PROCEDURE CHECK_OPI_MFG_CST_VAR_SETUP(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2);
52
53
54
55 /* get_conversion_rate
56
57 Compute all the conversion rates for all distinct organization,
58 transaction date pairs in the staging table. The date in the fact
59 table is already without a timestamp i.e. trunc'ed.
60
61 There are two conversion rates to be computed:
62 1. Primary global
63 2. Secondary global (if set up)
64
65 The conversion rate work table was truncated during
66 the initialization phase.
67
68 Get the currency conversion rates based on the organizations in the
69 WIP_ENTITIES and IC_WHSE_MST tabls using the
70 fii_currency.get_global_rate_primary API for the primary global
71 currency and fii_currency.get_global_rate_secondary for the
72 secondary global currency.
73 The primary currency API:
74 1. finds the conversion rate if one exists.
75 2. returns -1 if there is no conversion rate on that date.
76 3. returns -2 if the currency code is not found.
77 4. returns -3 if the transaction_date is prior to 01-JAN-1999,
78 the functional currency code is EUR and there is no EUR to USD
79 conversion rate defined on 01-JAN-1999.
80
81 The secondary currency API:
82 1. Finds the global secondary currency rate if one exists.
83 2. Returns a rate of 1 if the secondary currency has not been set up.
84 3. Returns -1, -2, -3 in the same way as the primary currency code API.
85
86 If the global and secondary currency codes and rate types are identical,
87 do not call the secondary currency API. Instead update the secondary
88 rates from the primary.
89
90 If the secondary currency has not been set up, set the conversion rate
91 to null.
92
93 If any primary conversion rates are missing, throw an exception.
94 If any secondary currency rates are missing (after the secondary
95 currency has been set up) throw an exception.
96
97 Need to commit data here due to insert+append.
98
99 Date Author Action
100 08/30/2004 Dinkar Gupta Modified to provide secondary
101 currency support.
102 */
103
104 FUNCTION get_conversion_rate (errbuf in out NOCOPY VARCHAR2,
105 retcode in out NOCOPY VARCHAR2)
106 RETURN NUMBER
107 IS
108
109 -- Cursor to see if any rates are missing. See below for details
110 CURSOR invalid_rates_exist_csr IS
111 SELECT 1
112 FROM opi_dbi_cuv_conv_rates
113 WHERE ( nvl (conversion_rate, -999) < 0
114 OR nvl (sec_conversion_rate, 999) < 0)
115 AND rownum < 2;
116
117 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
118
119
120 -- Set up a cursor to get all the invalid rates.
121 -- By the logic of the fii_currency.get_global_rate_primary
122 -- and fii_currency.get_global_rate_secondary APIs, the returned value
123 -- is -ve if no rate exists:
124 -- -1 for dates with no rate.
125 -- -2 for unrecognized conversion rates.
126 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
127 -- transaction_date is prior to 01-JAN-1999 (when the EUR
128 -- officially went into circulation).
129 --
130 -- However, with the secondary currency, the null rate means it
131 -- has not been setup and should therefore not be reported as an
132 -- error.
133 --
134 -- Also, cross check with the org-date pairs in the staging table,
135 -- in case some orgs never had a functional currency code defined.
136 CURSOR get_missing_rates_c (p_pri_sec_curr_same NUMBER,
137 p_global_currency_code VARCHAR2,
138 p_global_rate_type VARCHAR2,
139 p_secondary_currency_code VARCHAR2,
140 p_secondary_rate_type VARCHAR2,
141 p_sysdate DATE) IS
142 SELECT DISTINCT
143 report_order,
144 curr_code,
145 rate_type,
146 transaction_date,
147 func_currency_code
148 FROM (
149 SELECT DISTINCT
150 p_global_currency_code curr_code,
151 p_global_rate_type rate_type,
152 1 report_order, -- ordering global currency first
153 mp.organization_code,
154 decode (conv.conversion_rate,
155 C_EURO_MISSING_AT_START, g_euro_start_date,
156 conv.transaction_date) transaction_date,
157 conv.f_currency_code func_currency_code
158 FROM opi_dbi_cuv_conv_rates conv,
159 mtl_parameters mp,
160 (SELECT /*+ parallel_index(we) index_ffs(we) */
161 DISTINCT
162 organization_id,
163 p_sysdate transaction_date
164 FROM wip_entities we
165 ) to_conv
166 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
167 AND mp.organization_id = to_conv.organization_id
168 AND conv.transaction_date (+) = to_conv.transaction_date
169 AND conv.organization_id (+) = to_conv.organization_id
170 UNION ALL
171 SELECT DISTINCT
172 p_secondary_currency_code curr_code,
173 p_secondary_rate_type rate_type,
174 decode (p_pri_sec_curr_same,
175 1, 1,
176 2) report_order, --ordering secondary currency next
177 mp.organization_code,
178 decode (conv.sec_conversion_rate,
179 C_EURO_MISSING_AT_START, g_euro_start_date,
180 conv.transaction_date) transaction_date,
181 conv.f_currency_code func_currency_code
182 FROM opi_dbi_cuv_conv_rates conv,
183 mtl_parameters mp,
184 (SELECT /*+ parallel_index(we) index_ffs(we) */
185 DISTINCT
186 organization_id,
187 p_sysdate transaction_date
188 FROM wip_entities we
189 ) to_conv
190 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
191 AND mp.organization_id = to_conv.organization_id
192 AND conv.transaction_date (+) = to_conv.transaction_date
193 AND conv.organization_id (+) = to_conv.organization_id)
194 ORDER BY
195 report_order ASC,
196 transaction_date,
197 func_currency_code;
198
199 -- position marker in function
200 l_stmt_num NUMBER;
201
202 no_currency_rate_flag NUMBER;
203
204 -- Flag to check if the primary and secondary currencies are the
205 -- same
206 l_pri_sec_curr_same NUMBER;
207
208 -- old error reporting
209 i_err_num NUMBER;
210 i_err_msg VARCHAR2(255);
211
212
213 BEGIN
214
215 l_stmt_num := 0;
216 -- initialization block
217 no_currency_rate_flag := 0;
218 l_pri_sec_curr_same := 0;
219 retcode := g_ok;
220
221 l_stmt_num := 10;
222 -- WHO column variable initialization
223 g_sysdate := trunc (SYSDATE);
224 g_user_id := nvl(fnd_global.user_id, -1);
225 g_login_id := nvl(fnd_global.login_id, -1);
226
227
228 l_stmt_num := 12;
229 -- Global currency codes -- already checked if primary is set up
230 g_global_currency_code := bis_common_parameters.get_currency_code;
231 g_secondary_currency_code :=
232 bis_common_parameters.get_secondary_currency_code;
233
234 g_global_start_date := trunc (bis_common_parameters.get_global_start_date);
235
236 l_stmt_num := 14;
237 -- Global rate types -- already checked if primary is set up
238 g_global_rate_type := bis_common_parameters.get_rate_type;
239 g_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
240
241 l_stmt_num := 16;
242 -- check that either both the secondary rate type and secondary
243 -- rate are null, or that neither are null.
244 IF ( (g_secondary_currency_code IS NULL AND
245 g_secondary_rate_type IS NOT NULL)
246 OR (g_secondary_currency_code IS NOT NULL AND
247 g_secondary_rate_type IS NULL) ) THEN
248
249 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.');
250
251 RAISE_APPLICATION_ERROR(-20000, errbuf);
252
253 END IF;
254
255
256 l_stmt_num := 18;
257 -- check if the primary and secondary currencies and rate types are
258 -- identical.
259 IF (g_global_currency_code = nvl (g_secondary_currency_code, '---') AND
260 g_global_rate_type = nvl (g_secondary_rate_type, '---') ) THEN
261 l_pri_sec_curr_same := 1;
262 END IF;
263
264 l_stmt_num := 20;
265 -- Use the fii_currency.get_global_rate_primary function to get the
266 -- conversion rate given a currency code and a date.
267 -- The function returns:
268 -- 1 for currency code of 'USD' which is the global currency
269 -- -1 for dates for which there is no currency conversion rate
270 -- -2 for unrecognized currency conversion rates
271 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
272 -- transaction_date is prior to 01-JAN-1999 (when the EUR
273 -- officially went into circulation).
274
275 -- Use the fii_currency.get_global_rate_secondary to get the secondary
276 -- global rate. If the secondary currency has not been set up,
277 -- make the rate null. If the secondary currency/rate types are the
278 -- same as the primary, don't call the API but rather use an update
279 -- statement followed by the insert.
280
281 -- By selecting distinct org and currency code from the gl_set_of_books
282 -- and hr_organization_information, take care of duplicate codes.
283 INSERT /*+ append parallel(rates) */
284 INTO opi_dbi_cuv_conv_rates rates (
285 organization_id,
286 f_currency_code,
287 transaction_date,
288 conversion_rate,
289 sec_conversion_rate,
290 creation_date,
291 last_update_date,
292 created_by,
293 last_updated_by,
294 last_update_login,
295 PROGRAM_ID,
296 PROGRAM_LOGIN_ID,
297 PROGRAM_APPLICATION_ID,
298 REQUEST_ID)
299 SELECT /*+ parallel (to_conv) parallel (curr_codes) */
300 to_conv.organization_id,
301 curr_codes.currency_code,
302 to_conv.transaction_date,
303 decode (curr_codes.currency_code,
304 g_global_currency_code, 1,
305 fii_currency.get_global_rate_primary (
306 curr_codes.currency_code,
307 to_conv.transaction_date) ),
308 decode (g_secondary_currency_code,
309 NULL, NULL,
310 curr_codes.currency_code, 1,
311 decode (l_pri_sec_curr_same,
312 1, C_PRI_SEC_CURR_SAME_MARKER,
313 fii_currency.get_global_rate_secondary (
314 curr_codes.currency_code,
315 to_conv.transaction_date))),
316 g_sysdate,
317 g_sysdate,
318 g_user_id,
319 g_user_id,
320 g_login_id,
321 g_program_id,
322 g_program_login_id,
323 g_program_application_id,
324 g_request_id
325 FROM
326 (SELECT /*+ parallel_index(we) index_ffs(we) */
327 DISTINCT
328 organization_id,
329 g_sysdate transaction_date
330 FROM wip_entities we
331 ) to_conv,
332 (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
333 parallel (hoi) parallel (gsob)*/
334 DISTINCT hoi.organization_id, gsob.currency_code
335 FROM hr_organization_information hoi,
336 gl_sets_of_books gsob
337 WHERE hoi.org_information_context = 'Accounting Information'
338 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
339 curr_codes
340 WHERE curr_codes.organization_id = to_conv.organization_id;
341
342
343 --Introduced commit because of append parallel in the insert stmt above.
344 commit;
345
346 l_stmt_num := 40;
347 -- if the primary and secondary currency codes are the same, then
348 -- update the secondary with the primary
349 IF (l_pri_sec_curr_same = 1) THEN
350
351 UPDATE /*+ parallel (opi_dbi_cuv_conv_rates) */
352 opi_dbi_cuv_conv_rates
353 SET sec_conversion_rate = conversion_rate;
354
355 -- safe to commit, as before
356 commit;
357
358 END IF;
359
360 -- report missing rate
361 l_stmt_num := 50;
362
363 OPEN invalid_rates_exist_csr;
364 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
365 IF (invalid_rates_exist_csr%FOUND) THEN
366
367 -- there are missing rates - prepare to report them.
368 no_currency_rate_flag := 1;
369 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
370
371 l_stmt_num := 60;
372 FOR get_missing_rates_rec IN get_missing_rates_c
373 (l_pri_sec_curr_same,
374 g_global_currency_code,
375 g_global_rate_type,
376 g_secondary_currency_code,
377 g_secondary_rate_type,
378 g_sysdate)
379 LOOP
380
381 BIS_COLLECTION_UTILITIES.writemissingrate (
382 get_missing_rates_rec.rate_type,
383 get_missing_rates_rec.func_currency_code,
384 get_missing_rates_rec.curr_code,
385 get_missing_rates_rec.transaction_date);
386
387 END LOOP;
388
389 END IF;
390 CLOSE invalid_rates_exist_csr;
391
392 l_stmt_num := 70; /* check no_currency_rate_flag */
393 IF (no_currency_rate_flag = 1) THEN /* missing rate found */
394 BIS_COLLECTION_UTILITIES.put_line('Please setup conversion rate for all missing rates reported');
395 retcode := g_error; -- there are missing rates to report
396 END IF;
397
398 return retcode;
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 rollback;
403 i_err_num := SQLCODE;
404 i_err_msg := 'OPI_DBI_MTL_VARIANCE_PKG.GET_CONVERSION_RATE ('
405 || to_char(l_stmt_num)
406 || '): '
407 || substr(SQLERRM, 1,200);
408
409 BIS_COLLECTION_UTILITIES.put_line('OPI_DBI_MTL_VARIANCE_PKG.GET_CONVERSION_RATE - Error at statement ('
410 || to_char(l_stmt_num)
411 || ')');
412
413 BIS_COLLECTION_UTILITIES.put_line('Error Number: ' || to_char(i_err_num));
414 BIS_COLLECTION_UTILITIES.put_line('Error Message: ' || i_err_msg);
415
416 return g_error;
417
418 END get_conversion_rate;
419
420 -- Replace standard costs with get_cost for rows where zero std cost
421 -- was caused by actual qty = 0 ans std qty <> 0
422 procedure Fix_OPM_Std_costs
423 is
424 l_stmt_num Number;
425 begin
426
427 commit;
428
429 l_stmt_num := 61;
430
431 --Setup bulk costing parameter table
432
433 insert into opi_pmi_cost_param_gtmp
434 (
435 ITEM_ID,
436 WHSE_CODE,
437 ORGN_CODE,
438 TRANS_DATE
439 )
440 select distinct
441 scaled.ITEM_ID ,
442 whse.WHSE_CODE ,
443 whse.ORGN_CODE ,
444 jobs.COMPLETION_DATE TRANS_DATE
445 from
446 OPI_DBI_OPM_SCALED_MTL scaled,
447 ic_whse_mst whse,
448 OPI_DBI_JOBS_F jobs
449 where
450 whse.mtl_organization_id = jobs.organization_id
451 and jobs.job_id = scaled.batch_id
452 and jobs.job_type = 4
453 and scaled.actual_qty = 0
454 and scaled.plan_qty <> 0;
455
456 l_stmt_num := 62;
457
458 opi_pmi_cost.get_cost;
459
460 l_stmt_num := 63;
461
462
463 -- bulk costing results to update fact
464
465
466 update /*+ parallel(f) */ OPI_DBI_JOB_MTL_DETAILS_F f
467 set STANDARD_VALUE_B =
468 (select f.standard_quantity * costs.total_cost
469 from opi_pmi_cost_result_gtmp costs,
470 OPI_DBI_JOBS_F jobs,
471 ic_whse_mst whse,
472 ic_item_mst_b item,
473 mtl_System_items_b msi
474 where whse.mtl_organization_id = f.organization_id
475 and whse.whse_code = costs.whse_code
476 and whse.orgn_code = costs.orgn_code
477 and trunc(jobs.completion_date) = trunc(costs.trans_date)
478 and jobs.job_type = 4
479 and jobs.job_id = f.job_id
480 and jobs.organization_id = f.organization_id
481 and jobs.assembly_item_id = f.assembly_item_id
482 and f.component_item_id = msi.inventory_item_id
483 and f.organization_id = msi.organization_id
484 and msi.segment1 = item.item_no
485 and costs.item_id = item.item_id
486 )
487 where f.standard_value_b = 0
488 and f.actual_value_b = 0
489 and f.actual_quantity = 0
490 and f.standard_quantity <> 0
491 and f.job_type= 4 -- OPM jobs
492 and f.source = 2; -- OPM source
493
494
495 end Fix_OPM_Std_costs;
496
497 /*
498
499 Pre R12 OPM Extraction code will be called only if r12 migration date is
500 less than GSD.
501
502 Parameters:
503 retcode - 0 on successful completion, -1 on error and 1 for warning.
504 errbuf - empty on successful completion, message on error or warning
505
506 */
507
508 PROCEDURE GET_MFG_CST_VAR_PRER12_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
509 IS
510 l_stmt_num NUMBER;
511 l_row_count NUMBER;
512 l_err_num NUMBER;
513 l_err_msg VARCHAR2(255);
514 l_proc_name VARCHAR2(255);
515 BEGIN
516
517 l_proc_name := 'OPI_DBI_MTL_VARIANCE_PKG.GET_MFG_CST_VAR_PRER12_INIT';
518
519 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
520
521 l_stmt_num := 10;
522 --Inserting Pre R12 OPM data
523 INSERT /*+ append parallel(OPI_DBI_MFG_CST_VAR_F) */
524 INTO OPI_DBI_MFG_CST_VAR_F
525 (
526 Organization_Id
527 ,Job_Id
528 ,Job_Type
529 ,Assembly_Item_id
530 ,Closed_date
531 ,standard_value_b
532 ,actual_value_b
533 ,standard_value_g
534 ,actual_value_g
535 ,standard_value_sg
536 ,actual_value_sg
537 ,Actual_Qty_Completed
538 ,UOM_Code
539 ,Conversion_rate
540 ,Sec_conversion_rate
541 ,Source
542 ,Creation_Date
543 ,Last_Update_Date
544 ,Created_By
545 ,Last_Updated_By
546 ,Last_Update_Login
547 ,PROGRAM_ID
548 ,PROGRAM_LOGIN_ID
549 ,PROGRAM_APPLICATION_ID
550 ,REQUEST_ID
551 )
552 SELECT
553 var.organization_id organization_id,
554 var.job_id job_id,
555 var.job_type job_type,
556 var.assembly_item_id assembly_item_id,
557 var.closed_date closed_date,
558 sum (standard_value_b * nvl(dtl.cost_alloc, 0)) standard_value_b,
559 sum (actual_value_b * nvl(dtl.cost_alloc, 0)) actual_value_b,
560 sum (standard_value_b * nvl(dtl.cost_alloc, 0)*conversion_rate) standard_value_g,
561 sum (standard_value_b * nvl(dtl.cost_alloc, 0)*sec_conversion_rate) standard_value_sg,
562 sum (actual_value_b * nvl(dtl.cost_alloc, 0)*conversion_rate) actual_value_g,
563 sum (actual_value_b * nvl(dtl.cost_alloc, 0)*sec_conversion_rate) actual_value_sg,
564 sum (actual_qty_completed) actual_qty_completed,
565 uom_code uom_code,
566 conversion_rate conversion_rate,
567 sec_conversion_rate sec_conversion_rate,
568 3 source,
569 g_sysdate creation_date,
570 g_sysdate last_update_date,
571 g_user_id created_by,
572 g_user_id last_updated_by,
573 g_login_id last_update_login,
574 g_program_id PROGRAM_ID,
575 g_program_login_id PROGRAM_LOGIN_ID,
576 g_program_application_id PROGRAM_APPLICATION_ID,
577 g_request_id REQUEST_ID
578 FROM
579 (
580 SELECT
581 led.Organization_id Organization_id,
582 led.job_id job_id,
583 led.job_type job_type,
584 led.assembly_item_id assembly_item_id,
585 led.completion_date closed_date,
586 sum (led.actual_value_b) actual_value_b,
587 sum (led.standard_value_b) standard_value_b,
588 led.actual_qty_completed actual_qty_completed,
589 led.uom_code uom_code,
590 rates.conversion_rate conversion_rate,
591 rates.sec_conversion_rate sec_conversion_rate
592 FROM
593 OPI_DBI_JOBS_F rates,
594 (
595 SELECT
596 jobs.Organization_id,
597 jobs.Job_Id,
598 jobs.Job_Type,
599 jobs.Assembly_Item_id,
600 jobs.Completion_date,
601 jobs.Actual_Qty_Completed,
602 jobs.UOM_Code,
603 gsl.doc_id,
604 gsl.doc_type,
605 gsl.gl_trans_date,
606 gsl.line_id,
607 -sum (decode (acct_ttl_type,1500,
608 decode (sub_event_type,50040 ,
609 decode(jobs.line_type,2,gsl.amount_base*gsl.debit_credit_sign,0),
610 /* decode else */
611 gsl.amount_base * gsl.debit_credit_sign),
612 /* decode else */
613 gsl.amount_base * gsl.debit_credit_sign)) Actual_Value_B,
614 sum (decode (acct_ttl_type, 1500,
615 decode (sub_event_type, 50040,
616 decode(jobs.line_type,1,gsl.amount_base * gsl.debit_credit_sign,0),
617 /* decode else */
618 0),
619 /* decode else */ 0 ) ) Standard_Value_B
620 FROM
621 GL_SUBR_LED gsl,
622 (
623 select
624 jobs.Organization_id,
625 jobs.Job_Id,
626 jobs.Job_Type,
627 jobs.Assembly_Item_id,
628 jobs.Completion_date,
629 jobs.Actual_Qty_Completed,
630 jobs.UOM_Code,
631 gmd.line_type,
632 gmd.material_detail_id line_id
633 from
634 OPI_DBI_JOBS_F jobs,
635 GME_MATERIAL_DETAILS gmd
636 where
637 jobs.job_id = gmd.batch_id and
638 jobs.status = 'Closed' and
639 jobs.source = 3
640 union all
641 select
642 jobs.Organization_id,
643 jobs.Job_Id,
644 jobs.Job_Type,
645 jobs.Assembly_Item_id,
646 jobs.Completion_date,
647 jobs.Actual_Qty_Completed,
648 jobs.UOM_Code,
649 0,
650 gbsr.batchstep_resource_id line_id
651 from
652 OPI_DBI_JOBS_F jobs,
653 GME_BATCH_STEP_RESOURCES gbsr
654 where
655 jobs.job_id = gbsr.batch_id and
656 jobs.status = 'Closed' and
657 jobs.source = 3) jobs
658 WHERE
659 gsl.doc_type = 'PROD'
660 and gsl.line_id = jobs.line_id
661 AND gsl.doc_id = jobs.job_id
662 AND (( gsl.acct_ttl_type = 5400
663 and gsl.sub_event_type in ( 50010, 50040, 50050 )
664 )
665 or
666 ( gsl.acct_ttl_type = 1500
667 and gsl.sub_event_type in ( 50010, 50040 )
668 ))
669 GROUP BY
670 jobs.Organization_id,
671 jobs.Job_Id,
672 jobs.Job_Type,
673 jobs.Assembly_Item_id,
674 jobs.Completion_date,
675 jobs.Actual_Qty_Completed,
676 jobs.UOM_Code,
677 doc_id,
678 doc_type,
679 gl_trans_date,
680 gsl.line_id ) led
681 WHERE
682 led.ORGANIZATION_ID = rates.organization_id and
683 led.job_id = rates.job_id and
684 led.job_type = rates.job_type and
685 led.assembly_item_id = rates.assembly_item_id and
686 rates.source = 3
687 GROUP BY
688 led.Organization_id,
689 led.Job_Id,
690 led.Job_Type,
691 led.Assembly_Item_id,
692 led.Completion_date,
693 led.Actual_Qty_Completed,
694 led.UOM_Code,
695 rates.Conversion_Rate,
696 rates.Sec_conversion_rate) var,
697 gme_material_details dtl
698 where
699 dtl.batch_id = var.job_id
700 AND dtl.line_type = 1
701 AND dtl.inventory_item_id = var.assembly_item_id
702 AND dtl.organization_id = var.organization_id
703 group by
704 var.organization_id,
705 var.job_id,
706 var.job_type,
707 var.assembly_item_id,
708 var.closed_date,
709 uom_code,
710 conversion_rate,
711 sec_conversion_rate;
712
713 l_row_count := sql%rowcount;
714
715 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished Pre R12 OPM Manufacturing Cost Variance load into Fact Table: '|| l_row_count || ' rows inserted');
716 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
717
718 END GET_MFG_CST_VAR_PRER12_INIT;
719
720 /*
721
722 Initial Load MCV.
723 This procedure extracts R12 OPM and ODM for MCV, also call to Pre R12 OPM is made if
724 R12 migration date is greater than global start date.
725
726 */
727
728 PROCEDURE GET_MFG_CST_VAR_INIT(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
729 IS
730 l_stmt_num NUMBER;
731 l_row_count NUMBER;
732 l_err_num NUMBER;
733 l_err_msg VARCHAR2(255);
734 l_status VARCHAR2(30);
735 l_industry VARCHAR2(30);
736 l_opi_schema VARCHAR2(30);
737 l_proc_name VARCHAR2(255);
738 BEGIN
739
740 l_proc_name := 'OPI_DBI_MTL_VARIANCE_PKG.GET_MFG_CST_VAR_INIT';
741 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
742
743 -- WHO column variable initialization
744 g_sysdate := SYSDATE;
745 g_user_id := nvl(fnd_global.user_id, -1);
746 g_login_id := nvl(fnd_global.login_id, -1);
747 g_program_id := nvl (fnd_global.conc_program_id, -1);
748 g_program_login_id := nvl (fnd_global.conc_login_id, -1);
749 g_program_application_id := nvl (fnd_global.prog_appl_id, -1);
750 g_request_id := nvl (fnd_global.conc_request_id, -1);
751
752
753 /* Check For Setup */
754 l_stmt_num := 10;
755 CHECK_OPI_MFG_CST_VAR_SETUP(errbuf,retcode);
756
757 /* Truncating Fact Table */
758 l_stmt_num := 15;
759 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
760 --{
761 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_MFG_CST_VAR_F';
762 --}
763 END IF;
764
765 execute immediate 'alter session enable parallel dml';
766
767 /* Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table */
768 l_stmt_num := 17;
769 BIS_COLLECTION_UTILITIES.PUT_LINE('Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table');
770 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
771
772 /* Insert OPI Manufacturing Cost Variances */
773 l_stmt_num := 20;
774 INSERT /*+ append parallel(OPI_DBI_MFG_CST_VAR_F) */
775 INTO OPI_DBI_MFG_CST_VAR_F
776 (
777 Organization_Id
778 ,Job_Id
779 ,Job_Type
780 ,Assembly_Item_id
781 ,Closed_date
782 ,standard_value_b
783 ,actual_value_b
784 ,standard_value_g
785 ,actual_value_g
786 ,standard_value_sg
787 ,actual_value_sg
788 ,Actual_Qty_Completed
789 ,UOM_Code
790 ,Conversion_rate
791 ,Sec_conversion_rate
792 ,Source
793 ,Creation_Date
794 ,Last_Update_Date
795 ,Created_By
796 ,Last_Updated_By
797 ,Last_Update_Login
798 ,PROGRAM_ID
799 ,PROGRAM_LOGIN_ID
800 ,PROGRAM_APPLICATION_ID
801 ,REQUEST_ID
802 )
803 SELECT /*+ ordered use_hash(wpb) parallel(wpb) parallel(jobs)*/
804 wpb.organization_id organization_id,
805 jobs.job_id job_id,
806 jobs.job_type job_type,
807 jobs.assembly_item_id assembly_item_id,
808 jobs.completion_date closed_date,
809 sum (nvl(tl_material_out,0) + nvl(tl_material_overhead_out,0) +
810 nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
811 nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
812 nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
813 nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
814 nvl(pl_outside_processing_out,0) ) standard_value_b,
815 sum (nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
816 nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
817 nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
818 nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
819 nvl(tl_scrap_in,0)) actual_value_b,
820 sum ((nvl(tl_material_out,0) + nvl(tl_material_overhead_out,0) +
821 nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
822 nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
823 nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
824 nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
825 nvl(pl_outside_processing_out,0))*jobs.conversion_rate) standard_value_g,
826 sum ((nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
827 nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
828 nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
829 nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
830 nvl(tl_scrap_in,0))*jobs.conversion_rate) actual_value_g,
831 sum ((nvl(tl_material_out,0) + nvl(tl_material_overhead_out,0) +
832 nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
833 nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
834 nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
835 nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
836 nvl(pl_outside_processing_out,0))*jobs.sec_conversion_rate) standard_value_sg,
837 sum ((nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
838 nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
839 nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
840 nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
841 nvl(tl_scrap_in,0))*jobs.sec_conversion_rate) actual_value_sg,
842 jobs.actual_qty_completed actual_qty_completed,
843 jobs.uom_code uom_code,
844 jobs.conversion_rate conversion_rate,
845 jobs.sec_conversion_rate sec_conversion_rate,
846 1 source,
847 g_sysdate creation_date,
848 g_sysdate last_update_date,
849 g_user_id created_by,
850 g_user_id last_updated_by,
851 g_login_id last_update_login,
852 g_program_id PROGRAM_ID,
853 g_program_login_id PROGRAM_LOGIN_ID,
854 g_program_application_id PROGRAM_APPLICATION_ID,
855 g_request_id REQUEST_ID
856 FROM
857 OPI_DBI_JOBS_F jobs,
858 WIP_PERIOD_BALANCES wpb
859 WHERE jobs.Status = 'Closed'
860 AND jobs.organization_id = wpb.organization_id
861 AND jobs.job_id = decode (wpb.class_type,
862 1, wpb.wip_entity_id,
863 5, wpb.wip_entity_id,
864 2, wpb.repetitive_schedule_id)
865 GROUP BY
866 wpb.organization_id,
867 jobs.job_id,
868 jobs.job_type,
869 jobs.assembly_item_id,
870 jobs.completion_date,
871 jobs.actual_qty_completed,
872 jobs.uom_code,
873 jobs.conversion_rate,
874 jobs.sec_conversion_rate
875 /*Post R12 OPM Inert */
876 UNION ALL
877 SELECT /*+ parallel(var) */
878 var.organization_id organization_id,
879 var.job_id job_id,
880 var.job_type job_type,
881 var.assembly_item_id assembly_item_id,
882 var.closed_date closed_date,
883 var.standard_value_b,
884 var.actual_value_b,
885 var.standard_value_b*var.conversion_rate standard_value_g,
886 var.actual_value_b*var.conversion_rate actual_value_g,
887 var.standard_value_b*var.sec_conversion_rate standard_value_sg,
888 var.actual_value_b*var.sec_conversion_rate actual_value_sg,
889 var.actual_qty_completed,
890 var.uom_code uom_code,
891 var.conversion_rate conversion_rate,
892 var.sec_conversion_rate sec_conversion_rate,
893 2 source,
894 g_sysdate creation_date,
895 g_sysdate last_update_date,
896 g_user_id created_by,
897 g_user_id last_updated_by,
898 g_login_id last_update_login,
899 g_program_id PROGRAM_ID,
900 g_program_login_id PROGRAM_LOGIN_ID,
901 g_program_application_id PROGRAM_APPLICATION_ID,
902 g_request_id REQUEST_ID
903 FROM
904 (
905 select /*+ use_hash(jobs) parallel(jobs) parallel(gtv) parallel(tmp) parallel(mtl_dtl) use_hash(gtv mtl_dtl) full(gtv)*/
906 job_id,
907 job_type,
908 jobs.assembly_item_id,
909 jobs.organization_id,
910 jobs.actual_qty_completed,
911 jobs.uom_code,
912 jobs.completion_date closed_date,
913 jobs.conversion_rate,
914 jobs.sec_conversion_rate,
915 -sum(Decode(jobs.line_type,1,decode(jobs.line_id, mtl_dtl.material_detail_id,
916 -txn_base_value,0),0)) standard_Value_b,
917 -sum(Decode(jobs.line_type,-1,txn_base_value*mtl_dtl.cost_alloc,
918 2,txn_base_value*mtl_dtl.cost_alloc, 0)) actual_Value_b
919 from
920 GMF_TRANSACTION_VALUATION GTV,
921 OPI_DBI_ORG_LE_TEMP tmp,
922 GME_MATERIAL_DETAILS mtl_dtl,
923 (
924 select /*+ no_merge ordered use_hash(mtl_dtl) full(jobs) parallel(mtl_dtl) parallel(jobs) */
925 jobs.Job_id,
926 jobs.job_type,
927 mtl_dtl.material_detail_id line_id,
928 jobs.assembly_item_id,
929 to_char(mtl_dtl.inventory_item_id) item_resource_id,
930 jobs.actual_qty_completed,
931 jobs.completion_date,
932 jobs.uom_code,
933 mtl_dtl.Line_type,
934 jobs.organization_id,
935 jobs.conversion_rate,
936 jobs.sec_conversion_rate
937 from
938 OPI_DBI_JOBS_F jobs,
939 GME_MATERIAL_DETAILS mtl_dtl
940 where
941 jobs.job_id = mtl_dtl.batch_id and
942 jobs.organization_id = mtl_dtl.organization_id and
943 mtl_dtl.line_type in (-1,1,2) and
944 jobs.source = 2 and
945 jobs.status in ('Closed') and
946 jobs.line_type = 1
947 union all
948 select /*+ ordered use_hash(GBSR) full(jobs) parallel(jobs) parallel(gbsr)*/
949 jobs.job_id,
950 jobs.job_type,
951 gbsr.batchstep_resource_id line_id,
952 jobs.assembly_item_id,
953 gbsr.resources item_resource_id,
954 jobs.actual_qty_completed,
955 jobs.completion_date,
956 jobs.uom_code,
957 -1 Line_type,
958 jobs.Organization_id,
959 jobs.conversion_rate,
960 jobs.sec_conversion_rate
961 from
962 OPI_DBI_JOBS_F jobs,
963 GME_BATCH_STEP_RESOURCES gbsr
964 where
965 jobs.job_id = gbsr.batch_id and
966 jobs.source = 2 and
967 jobs.status in ('Closed') and
968 jobs.line_type = 1) Jobs
969 where
970 jobs.organization_id = GTV.organization_id and
971 jobs.job_id = GTV.doc_id and
972 (jobs.item_resource_id = to_char(GTV.inventory_item_id)
973 or jobs.item_resource_id = GTV.resources) and
974 GTV.line_id = jobs.line_id and
975 GTV.journal_line_type in ('INV','RCA') and
976 GTV.event_class_code in ('BATCH_MATERIAL','BATCH_RESOURCE') and
977 GTV.transaction_source = 'PRODUCTION' and
978 jobs.job_id = mtl_dtl.batch_id and
979 jobs.organization_id = mtl_dtl.organization_id and
980 jobs.assembly_item_id = mtl_dtl.inventory_item_id and
981 mtl_dtl.line_type = 1 and
982 GTV.ledger_id = tmp.ledger_id and
983 GTV.legal_entity_id = tmp.legal_entity_id and
984 GTV.valuation_cost_type_id = tmp.valuation_cost_type_id and
985 GTV.organization_id = tmp. organization_id
986 Group by
987 jobs.job_id,
988 jobs.job_type,
989 jobs.organization_id,
990 jobs.assembly_item_id,
991 jobs.actual_qty_completed,
992 jobs.uom_code,
993 jobs.completion_date,
994 jobs.conversion_rate,
995 jobs.sec_conversion_rate) var;
996
997
998 l_row_count := sql%rowcount;
999 g_row_count := g_row_count + l_row_count;
1000 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished OPI and OPM Manufacturing Cost Variance load into Fact Table: '|| l_row_count || ' rows inserted');
1001
1002 commit;
1003
1004 IF(g_r12_migration_date > g_global_start_date) THEN
1005
1006 GET_MFG_CST_VAR_PRER12_INIT(errbuf => errbuf, retcode => retcode);
1007
1008 END IF;
1009 commit;
1010 execute immediate 'alter session disable parallel dml';
1011
1012 /* Truncating Jobs Staging Table */
1013 l_stmt_num := 30;
1014 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
1015 --{
1016 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOBS_STG';
1017 --}
1018 END IF;
1019
1020 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1021
1022 END GET_MFG_CST_VAR_INIT;
1023
1024 /*
1025
1026 Incremental Load MCV.
1027 This procedure extracts R12 OPM and ODM for MCV.
1028
1029 */
1030
1031 PROCEDURE GET_MFG_CST_VAR_INCR(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1032 IS
1033 l_stmt_num NUMBER;
1034 l_row_count1 NUMBER;
1035 l_row_count2 NUMBER;
1036 l_err_num NUMBER;
1037 l_err_msg VARCHAR2(255);
1038 l_status VARCHAR2(30);
1039 l_industry VARCHAR2(30);
1040 l_opi_schema VARCHAR2(30);
1041 l_proc_name VARCHAR2(255);
1042 BEGIN
1043
1044 l_proc_name := 'OPI_DBI_MTL_VARIANCE_PKG.GET_MFG_CST_VAR_INCR';
1045 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
1046
1047 -- WHO column variable initialization
1048 g_sysdate := SYSDATE;
1049 g_user_id := nvl(fnd_global.user_id, -1);
1050 g_login_id := nvl(fnd_global.login_id, -1);
1051 g_program_id := nvl (fnd_global.conc_program_id, -1);
1052 g_program_login_id := nvl (fnd_global.conc_login_id, -1);
1053 g_program_application_id := nvl (fnd_global.prog_appl_id, -1);
1054 g_request_id := nvl (fnd_global.conc_request_id, -1);
1055
1056 /* Check For Setup */
1057 l_stmt_num := 10;
1058 CHECK_OPI_MFG_CST_VAR_SETUP(errbuf,retcode);
1059
1060 /* Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table */
1061 l_stmt_num := 15;
1062 BIS_COLLECTION_UTILITIES.PUT_LINE('Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table');
1063 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
1064
1065 /* Insert OPI Manufacturing Cost Variances */
1066 l_stmt_num := 20;
1067 MERGE INTO OPI_DBI_MFG_CST_VAR_F f
1068 USING
1069 (
1070 SELECT
1071 wpb_Organization_id Organization_id,
1072 jobs.Job_Id Job_id,
1073 jobs.Job_Type Job_Type,
1074 jobs.Assembly_Item_id Assembly_Item_id,
1075 jobs.Completion_date Closed_date,
1076 sum (nvl(tl_material_out,0) + nvl(tl_material_overhead_out,0) +
1077 nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
1078 nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
1079 nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
1080 nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
1081 nvl(pl_outside_processing_out,0) ) Standard_Value_B,
1082 sum (nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
1083 nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
1084 nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
1085 nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
1086 nvl(tl_scrap_in,0)) Actual_Value_B,
1087 sum ((nvl(tl_material_out,0) + nvl(tl_material_overhead_out,0) +
1088 nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
1089 nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
1090 nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
1091 nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
1092 nvl(pl_outside_processing_out,0))*rates.Conversion_Rate) Standard_Value_G,
1093 sum ((nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
1094 nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
1095 nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
1096 nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
1097 nvl(tl_scrap_in,0))*rates.Conversion_Rate) Actual_Value_G,
1098 sum ((nvl(tl_material_out,0) + nvl(tl_material_overhead_out,0) +
1099 nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
1100 nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
1101 nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
1102 nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
1103 nvl(pl_outside_processing_out,0))*rates.sec_Conversion_Rate) Standard_Value_SG,
1104 sum ((nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
1105 nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
1106 nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
1107 nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
1108 nvl(tl_scrap_in,0))*rates.sec_Conversion_Rate) Actual_Value_SG,
1109 jobs.Actual_Qty_Completed Actual_Qty_Completed,
1110 jobs.UOM_Code UOM_Code,
1111 rates.Conversion_Rate Conversion_Rate,
1112 rates.sec_Conversion_Rate sec_Conversion_Rate,
1113 1 SOURCE,
1114 g_sysdate CREATION_DATE,
1115 g_sysdate LAST_UPDATE_DATE,
1116 g_user_id CREATED_BY,
1117 g_user_id LAST_UPDATED_BY,
1118 g_login_id LAST_UPDATE_LOGIN,
1119 g_program_id PROGRAM_ID,
1120 g_program_login_id PROGRAM_LOGIN_ID,
1121 g_program_application_id PROGRAM_APPLICATION_ID,
1122 g_request_id REQUEST_ID
1123 FROM (SELECT WPB.ORGANIZATION_ID WPB_ORGANIZATION_ID
1124 , JOBS.ORGANIZATION_ID
1125 , JOBS.JOB_ID
1126 , JOBS.JOB_TYPE
1127 , JOBS.ASSEMBLY_ITEM_ID
1128 , JOBS.COMPLETION_DATE
1129 , JOBS.ACTUAL_QTY_COMPLETED
1130 , JOBS.UOM_CODE
1131 , JOBS.LINE_TYPE
1132 , JOBS.SOURCE
1133 , WPB.PL_MATERIAL_IN
1134 , WPB.PL_MATERIAL_OUT
1135 , WPB.PL_MATERIAL_OVERHEAD_IN
1136 , WPB.PL_MATERIAL_OVERHEAD_OUT
1137 , WPB.PL_OUTSIDE_PROCESSING_IN
1138 , WPB.PL_OUTSIDE_PROCESSING_OUT
1139 , WPB.PL_OVERHEAD_IN
1140 , WPB.PL_OVERHEAD_OUT
1141 , WPB.PL_RESOURCE_IN
1142 , WPB.PL_RESOURCE_OUT
1143 , WPB.TL_MATERIAL_OUT
1144 , WPB.TL_MATERIAL_OVERHEAD_OUT
1145 , WPB.TL_OUTSIDE_PROCESSING_IN
1146 , WPB.TL_OUTSIDE_PROCESSING_OUT
1147 , WPB.TL_OVERHEAD_IN
1148 , WPB.TL_OVERHEAD_OUT
1149 , WPB.TL_RESOURCE_IN
1150 , WPB.TL_RESOURCE_OUT
1151 , WPB.TL_SCRAP_IN
1152 , WPB.TL_SCRAP_OUT
1153 FROM OPI_DBI_JOBS_STG jobs
1154 , WIP_PERIOD_BALANCES wpb
1155 WHERE jobs.Status = 'Closed'
1156 AND jobs.ORGANIZATION_ID = wpb.ORGANIZATION_ID
1157 AND wpb.CLASS_TYPE IN (1, 5)
1158 AND jobs.job_id = wpb.wip_entity_id
1159 UNION ALL
1160 SELECT WPB.ORGANIZATION_ID wpb_organization_id
1161 , JOBS.ORGANIZATION_ID
1162 , JOBS.JOB_ID
1163 , JOBS.JOB_TYPE
1164 , JOBS.ASSEMBLY_ITEM_ID
1165 , JOBS.COMPLETION_DATE
1166 , JOBS.ACTUAL_QTY_COMPLETED
1167 , JOBS.UOM_CODE
1168 , JOBS.LINE_TYPE
1169 , JOBS.SOURCE
1170 , WPB.PL_MATERIAL_IN
1171 , WPB.PL_MATERIAL_OUT
1172 , WPB.PL_MATERIAL_OVERHEAD_IN
1173 , WPB.PL_MATERIAL_OVERHEAD_OUT
1174 , WPB.PL_OUTSIDE_PROCESSING_IN
1175 , WPB.PL_OUTSIDE_PROCESSING_OUT
1176 , WPB.PL_OVERHEAD_IN
1177 , WPB.PL_OVERHEAD_OUT
1178 , WPB.PL_RESOURCE_IN
1179 , WPB.PL_RESOURCE_OUT
1180 , WPB.TL_MATERIAL_OUT
1181 , WPB.TL_MATERIAL_OVERHEAD_OUT
1182 , WPB.TL_OUTSIDE_PROCESSING_IN
1183 , WPB.TL_OUTSIDE_PROCESSING_OUT
1184 , WPB.TL_OVERHEAD_IN
1185 , WPB.TL_OVERHEAD_OUT
1186 , WPB.TL_RESOURCE_IN
1187 , WPB.TL_RESOURCE_OUT
1188 , WPB.TL_SCRAP_IN
1189 , WPB.TL_SCRAP_OUT
1190 FROM OPI_DBI_JOBS_STG jobs
1191 , WIP_PERIOD_BALANCES wpb
1192 WHERE jobs.Status = 'Closed'
1193 AND jobs.ORGANIZATION_ID = wpb.ORGANIZATION_ID
1194 AND wpb.CLASS_TYPE = 2
1195 AND jobs.Job_id = wpb.REPETITIVE_SCHEDULE_ID) jobs
1196 , OPI_DBI_JOBS_F rates
1197 WHERE 1=1
1198 AND jobs.ORGANIZATION_ID = rates.organization_id
1199 AND jobs.job_id = rates.job_id
1200 AND jobs.job_type = rates.job_type
1201 AND jobs.line_type = rates.line_type
1202 AND jobs.assembly_item_id = rates.assembly_item_id
1203 AND jobs.source = rates.source
1204 GROUP BY jobs.wpb_Organization_id
1205 , jobs.Job_Id
1206 , jobs.Job_Type
1207 , jobs.Assembly_Item_id
1208 , jobs.Completion_date
1209 , jobs.Actual_Qty_Completed
1210 , jobs.UOM_Code
1211 , rates.Conversion_Rate
1212 , rates.sec_Conversion_Rate
1213 ) v
1214 ON ( F.Organization_Id = V.Organization_Id AND F.Job_Id = V.Job_Id
1215 AND F.Job_Type = V.Job_Type
1216 AND F.Assembly_Item_id = V.Assembly_Item_id)
1217 WHEN MATCHED THEN
1218 UPDATE SET
1219 F.Closed_date = V.Closed_date
1220 ,F.Standard_Value_B = V.Standard_Value_B
1221 ,F.Actual_Value_B = V.Actual_Value_B
1222 ,F.Actual_Qty_Completed = V.Actual_Qty_Completed
1223 ,F.Conversion_rate = V.Conversion_rate
1224 ,F.Sec_Conversion_rate = V.Sec_Conversion_rate
1225 ,F.Last_Update_Date = V.Last_Update_Date
1226 ,F.Last_Updated_By = V.Last_Updated_By
1227 ,F.Last_Update_Login = V.Last_Update_Login
1228 WHEN NOT MATCHED THEN
1229 INSERT (
1230 Organization_Id,
1231 Job_Id,
1232 Job_Type,
1233 Assembly_Item_id,
1234 Closed_date,
1235 Standard_Value_B,
1236 Actual_Value_B,
1237 Standard_Value_G,
1238 Actual_Value_G,
1239 Standard_Value_SG,
1240 Actual_Value_SG,
1241 Actual_Qty_Completed,
1242 UOM_Code,
1243 Conversion_rate,
1244 Sec_Conversion_rate,
1245 Source,
1246 Creation_Date,
1247 Last_Update_Date,
1248 Created_By,
1249 Last_Updated_By,
1250 Last_Update_Login,
1251 PROGRAM_ID,
1252 PROGRAM_LOGIN_ID,
1253 PROGRAM_APPLICATION_ID,
1254 REQUEST_ID)
1255 VALUES (
1256 V.Organization_Id,
1257 V.Job_Id,
1258 V.Job_Type,
1259 V.Assembly_Item_id,
1260 V.Closed_date,
1261 V.Standard_Value_B,
1262 V.Actual_Value_B,
1263 V.Standard_Value_G,
1264 V.Actual_Value_G,
1265 V.Standard_Value_SG,
1266 V.Actual_Value_SG,
1267 V.Actual_Qty_Completed,
1268 V.UOM_Code,
1269 V.Conversion_rate,
1270 V.Sec_Conversion_rate,
1271 V.Source,
1272 V.Creation_Date,
1273 V.Last_Update_Date,
1274 V.Created_By,
1275 V.Last_Updated_By,
1276 V.Last_Update_Login,
1277 V.PROGRAM_ID,
1278 V.PROGRAM_LOGIN_ID,
1279 V.PROGRAM_APPLICATION_ID,
1280 V.REQUEST_ID);
1281
1282
1283 l_row_count1 := sql%rowcount;
1284
1285 /* OPM Big Merge here */
1286
1287 l_stmt_num := 30;
1288
1289 MERGE INTO OPI_DBI_MFG_CST_VAR_F f
1290 USING (
1291 SELECT
1292 var.organization_id organization_id,
1293 var.job_id job_id,
1294 var.job_type job_type,
1295 var.assembly_item_id assembly_item_id,
1296 var.closed_date closed_date,
1297 var.standard_value_b,
1298 var.actual_value_b,
1299 var.actual_qty_completed,
1300 var.uom_code uom_code,
1301 rates.conversion_rate conversion_rate,
1302 rates.sec_conversion_rate sec_conversion_rate,
1303 var.standard_value_b*rates.conversion_rate standard_value_g,
1304 var.actual_value_b*conversion_rate actual_value_g,
1305 var.standard_value_b*sec_conversion_rate standard_value_sg,
1306 var.actual_value_b*sec_conversion_rate actual_value_sg,
1307 2 source,
1308 g_sysdate creation_date,
1309 g_sysdate last_update_date,
1310 g_user_id created_by,
1311 g_user_id last_updated_by,
1312 g_login_id last_update_login,
1313 g_program_id PROGRAM_ID,
1314 g_program_login_id PROGRAM_LOGIN_ID,
1315 g_program_application_id PROGRAM_APPLICATION_ID,
1316 g_request_id REQUEST_ID
1317 FROM
1318 (
1319 select /*+ ordered use_nl(mtl_dtl) full(gtv)*/
1320 job_id,
1321 job_type,
1322 jobs.assembly_item_id,
1323 jobs.organization_id,
1324 jobs.actual_qty_completed,
1325 jobs.uom_code,
1326 jobs.completion_date closed_date,
1327 -sum(Decode(jobs.line_type,1,decode(jobs.line_id, mtl_dtl.material_detail_id,
1328 -txn_base_value,0),0)) standard_Value_b,
1329 -sum(Decode(jobs.line_type,-1,txn_base_value*mtl_dtl.cost_alloc,
1330 2,txn_base_value*mtl_dtl.cost_alloc, 0)) actual_Value_b
1331 from
1332 (
1333 select /*+ ordered use_nl(mtl_dtl) index(mtl_dtl)*/
1334 jobs.Job_id,
1335 jobs.job_type,
1336 mtl_dtl.material_detail_id line_id,
1337 jobs.assembly_item_id,
1338 to_char(mtl_dtl.inventory_item_id) item_resource_id,
1339 jobs.actual_qty_completed,
1340 jobs.completion_date,
1341 jobs.uom_code,
1342 mtl_dtl.Line_type,
1343 jobs.organization_id
1344 from
1345 OPI_DBI_JOBS_STG jobs,
1346 GME_MATERIAL_DETAILS mtl_dtl
1347 where
1348 jobs.job_id = mtl_dtl.batch_id and
1349 jobs.organization_id = mtl_dtl.organization_id and
1350 mtl_dtl.line_type in (-1,1,2) and
1351 jobs.source = 2 and
1352 jobs.status in ('Closed') and
1353 jobs.line_type = 1
1354 union all
1355 select /*+ leading(jobs)*/
1356 jobs.job_id,
1357 jobs.job_type,
1358 gbsr.batchstep_resource_id line_id,
1359 jobs.assembly_item_id,
1360 gbsr.resources item_resource_id,
1361 jobs.actual_qty_completed,
1362 jobs.completion_date,
1363 jobs.uom_code,
1364 -1 Line_type,
1365 jobs.Organization_id
1366 from
1367 OPI_DBI_JOBS_STG jobs,
1368 GME_BATCH_STEP_RESOURCES gbsr
1369 where
1370 jobs.job_id = gbsr.batch_id and
1371 jobs.source = 2 and
1372 jobs.status in ('Closed') and
1373 jobs.line_type = 1
1374 ) Jobs ,
1375 GMF_TRANSACTION_VALUATION GTV,
1376 OPI_DBI_ORG_LE_TEMP tmp,
1377 GME_MATERIAL_DETAILS mtl_dtl
1378 where
1379 jobs.organization_id = GTV.organization_id and
1380 jobs.job_id = GTV.doc_id and
1381 (jobs.item_resource_id = to_char(GTV.inventory_item_id)
1382 or jobs.item_resource_id = GTV.resources) and
1383 GTV.line_id = jobs.line_id and
1384 GTV.journal_line_type in ('INV', 'RCA') and
1385 GTV.event_class_code in ('BATCH_MATERIAL', 'BATCH_RESOURCE') and
1386 GTV.transaction_source = 'PRODUCTION' and
1387 jobs.job_id = mtl_dtl.batch_id and
1388 jobs.organization_id = mtl_dtl.organization_id and
1389 jobs.assembly_item_id = mtl_dtl.inventory_item_id and
1390 mtl_dtl.line_type = 1 and
1391 GTV.ledger_id = tmp.ledger_id and
1392 GTV.legal_entity_id = tmp.legal_entity_id and
1393 GTV.valuation_cost_type_id = tmp.valuation_cost_type_id and
1394 GTV.organization_id = tmp. organization_id
1395 Group by
1396 jobs.job_id,
1397 jobs.job_type,
1398 jobs.organization_id,
1399 jobs.assembly_item_id,
1400 jobs.actual_qty_completed,
1401 jobs.uom_code,
1402 jobs.completion_date) var,
1403 OPI_DBI_JOBS_F rates
1404 where
1405 var.organization_id = rates.organization_id and
1406 var.job_id = rates.job_id and
1407 var.job_type = rates.job_type and
1408 var.assembly_item_id = rates.assembly_item_id and
1409 rates.line_type = 1
1410 ) v
1411 ON ( F.Organization_Id = V.Organization_Id
1412 AND F.Job_Id = V.Job_Id
1413 AND F.Job_Type = V.Job_Type
1414 AND F.Assembly_Item_id = V.Assembly_Item_id)
1415 WHEN MATCHED THEN
1416 UPDATE SET
1417 F.Closed_date = V.Closed_date
1418 ,F.Standard_Value_B = V.Standard_Value_B
1419 ,F.Actual_Value_B = V.Actual_Value_B
1420 ,F.Actual_Qty_Completed = V.Actual_Qty_Completed
1421 ,F.Conversion_rate = V.Conversion_rate
1422 ,F.Sec_Conversion_rate = V.Sec_Conversion_rate
1423 ,F.Last_Update_Date = V.Last_Update_Date
1424 ,F.Last_Updated_By = V.Last_Updated_By
1425 ,F.Last_Update_Login = V.Last_Update_Login
1426 WHEN NOT MATCHED THEN
1427 INSERT (
1428 Organization_Id,
1429 Job_Id,
1430 Job_Type,
1431 Assembly_Item_id,
1432 Closed_date,
1433 Standard_Value_B,
1434 Actual_Value_B,
1435 Actual_Qty_Completed,
1436 UOM_Code,
1437 Conversion_rate,
1438 Sec_Conversion_rate,
1439 Source,
1440 Creation_Date,
1441 Last_Update_Date,
1442 Created_By,
1443 Last_Updated_By,
1444 Last_Update_Login,
1445 PROGRAM_ID,
1446 PROGRAM_LOGIN_ID,
1447 PROGRAM_APPLICATION_ID,
1448 REQUEST_ID)
1449 VALUES (
1450 V.Organization_Id,
1451 V.Job_Id,
1452 V.Job_Type,
1453 V.Assembly_Item_id,
1454 V.Closed_date,
1455 V.Standard_Value_B,
1456 V.Actual_Value_B,
1457 V.Actual_Qty_Completed,
1458 V.UOM_Code,
1459 V.Conversion_rate,
1460 V.Sec_Conversion_rate,
1461 V.Source,
1462 V.Creation_Date,
1463 V.Last_Update_Date,
1464 V.Created_By,
1465 V.Last_Updated_By,
1466 V.Last_Update_Login,
1467 V.PROGRAM_ID,
1468 V.PROGRAM_LOGIN_ID,
1469 V.PROGRAM_APPLICATION_ID,
1470 V.REQUEST_ID);
1471
1472
1473 l_row_count2 := sql%rowcount;
1474
1475 g_row_count := g_row_count + l_row_count1 + l_row_count2;
1476
1477 commit;
1478
1479 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished OPI Manufacturing Cost Variance load into Fact Table: '|| l_row_count1 || ' rows inserted');
1480 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished OPM Manufacturing Cost Variance load into Fact Table: '|| l_row_count2 || ' rows inserted');
1481
1482 /* Truncating Jobs Staging Table */
1483 l_stmt_num := 30;
1484 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
1485 --{
1486 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_JOBS_STG';
1487 --}
1488 END IF;
1489
1490 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1491
1492 END GET_MFG_CST_VAR_INCR;
1493
1494 /*
1495
1496 Procedure extracts CUV for OPM and ODM. No Pre R12 OPM data is collected.
1497
1498 */
1499
1500
1501 PROCEDURE GET_CURR_UNREC_VAR (errbuf in out NOCOPY varchar2,
1502 retcode in out NOCOPY varchar2)
1503 IS
1504 l_stmt_num NUMBER;
1505 l_row_count NUMBER;
1506 l_err_num NUMBER;
1507 l_err_msg VARCHAR2(255);
1508 l_opi_schema VARCHAR2(30);
1509 l_status VARCHAR2(30);
1510 l_industry VARCHAR2(30);
1511 l_list dbms_sql.varchar2_table;
1512 l_proc_name VARCHAR2(255);
1513
1514 BEGIN
1515
1516 l_proc_name := 'OPI_DBI_MTL_VARIANCE_PKG.GET_CURR_UNREC_VAR';
1517
1518 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
1519
1520 -- WHO column variable initialization
1521 g_sysdate := SYSDATE;
1522 g_user_id := nvl(fnd_global.user_id, -1);
1523 g_login_id := nvl(fnd_global.login_id, -1);
1524 g_program_id := nvl (fnd_global.conc_program_id, -1);
1525 g_program_login_id := nvl (fnd_global.conc_login_id, -1);
1526 g_program_application_id := nvl (fnd_global.prog_appl_id, -1);
1527 g_request_id := nvl (fnd_global.conc_request_id, -1);
1528
1529 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
1530 l_list(2) := 'BIS_GLOBAL_START_DATE';
1531 l_list(3) := 'BIS_PRIMARY_RATE_TYPE';
1532
1533 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
1534
1535 IF BIS_COLLECTION_UTILITIES.SETUP( 'OPI_DBI_CURR_UNREC_VAR_F' ) = false then
1536 RAISE_APPLICATION_ERROR(-20000, errbuf);
1537 End if;
1538
1539 /* Truncate Current Unrecognized Variances Fact Table */
1540 l_stmt_num := 10;
1541 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
1542 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_CURR_UNREC_VAR_F';
1543 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_CUV_CONV_RATES';
1544 END IF;
1545
1546 l_stmt_num := 20;
1547 IF (Get_Conversion_Rate (errbuf, retcode) = -1) THEN
1548 BIS_COLLECTION_UTILITIES.put_line('Missing currency rate.');
1549 BIS_COLLECTION_UTILITIES.put_line('Please run this concurrent program again after fixing the missing currency rates.');
1550 retcode := g_error;
1551 return;
1552 END IF;
1553
1554 /* Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table */
1555 l_stmt_num := 25;
1556 BIS_COLLECTION_UTILITIES.PUT_LINE('Calling to populate temp table for mutli ledger/valuation_cost_type in gtv table');
1557 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
1558
1559
1560 /* Insert OPI Current Unrecognized Variances */
1561 l_stmt_num := 30;
1562 INSERT /*+ append parallel(OPI_DBI_CURR_UNREC_VAR_F) */
1563 INTO OPI_DBI_CURR_UNREC_VAR_F (
1564 organization_id
1565 ,inventory_item_id
1566 ,item_org_id
1567 ,inv_category_id
1568 ,standard_value_b
1569 ,standard_value_g
1570 ,standard_value_sg
1571 ,actual_value_b
1572 ,actual_value_g
1573 ,actual_value_sg
1574 ,actual_prd_qty
1575 ,uom_code
1576 ,source
1577 ,creation_date
1578 ,last_update_date
1579 ,created_by
1580 ,last_updated_by
1581 ,last_update_login
1582 ,job_id
1583 ,job_type
1584 ,PROGRAM_ID
1585 ,PROGRAM_LOGIN_ID
1586 ,PROGRAM_APPLICATION_ID
1587 ,REQUEST_ID
1588 )
1589 SELECT /*+ parallel(cat) parallel(ACT_STD) parallel(MSI) parallel(conv) full(cat) full(msi)*/
1590 act_std.organization_id organization_id,
1591 act_std.inventory_item_id inventory_item_id,
1592 act_std.inventory_item_id||'-'||act_std.organization_id item_org_id,
1593 nvl(cat.inv_category_id,-1) inv_category_id,
1594 act_std.std_val_b standard_value_b,
1595 act_std.std_val_b * conv.conversion_rate standard_value_g,
1596 act_std.std_val_b * conv.sec_conversion_rate standard_value_sg,
1597 act_std.act_val_b actual_value_b,
1598 act_std.act_val_b * conv.conversion_rate actual_value_g,
1599 act_std.act_val_b * conv.sec_conversion_rate actual_value_sg,
1600 act_std.act_prd_qty act_prd_qty,
1601 msi.primary_uom_code uom_code,
1602 1 source,
1603 g_sysdate creation_date,
1604 g_sysdate last_update_date,
1605 g_user_id created_by,
1606 g_user_id last_updated_by,
1607 g_login_id last_update_login,
1608 act_std.job_id,
1609 act_std.job_type,
1610 g_program_id PROGRAM_ID,
1611 g_program_login_id PROGRAM_LOGIN_ID,
1612 g_program_application_id PROGRAM_APPLICATION_ID,
1613 g_request_id REQUEST_ID
1614 FROM
1615 (
1616 SELECT /*+ no_merge parallel(x) */
1617 organization_id organization_id,
1618 inventory_item_id inventory_item_id,
1619 to_number(job_id) job_id,
1620 to_number(job_type) job_type,
1621 sum (act_cost_b) act_val_b,
1622 sum (std_val_b) std_val_b,
1623 sum (actual_qty_completed) act_prd_qty
1624 FROM
1625 (
1626 SELECT /*+ no_merge parallel(AC) parallel(Std_val_and_qty) */
1627 std_val_and_qty.organization_id organization_id,
1628 std_val_and_qty.inventory_item_id inventory_item_id,
1629 std_val_and_qty.job_id job_id,
1630 std_val_and_qty.job_type,
1631 ac.act_cost_b act_cost_b,
1632 std_val_and_qty.std_val_b std_val_b,
1633 actual_qty_completed
1634 FROM
1635 (
1636 SELECT /*+ no_merge parallel(not_cl_jobs) parallel(icosts) full(icosts) use_hash(icosts) use_hash(not_cl_jobs)*/
1637 not_cl_jobs.organization_id,
1638 not_cl_jobs.inventory_item_id,
1639 not_cl_jobs.job_idj,
1640 not_cl_jobs.job_id,
1641 not_cl_jobs.job_type,
1642 not_cl_jobs.status_type,
1643 CASE WHEN status_type IN (5, 4, 7) THEN
1644 actual_qty_completed * nvl(icosts.item_cost, 0) -- Use complete qty when Complete, Complete-No charges and Cancelled
1645 ELSE
1646 greatest(start_qty, actual_qty_completed) *
1647 nvl(icosts.item_cost, 0)
1648 END std_val_b,
1649 actual_qty_completed
1650 FROM
1651 (
1652 SELECT /*+ no_merge parallel(WDJ) parallel(WE) */
1653 wdj.organization_id organization_id,
1654 wdj.primary_item_id inventory_item_id,
1655 to_char (wdj.wip_entity_id) job_idj,
1656 to_char (wdj.wip_entity_id) job_id,
1657 decode(we.entity_type,5,5,1) job_type,
1658 wdj.status_type status_type,
1659 wdj.start_quantity start_qty,
1660 wdj.quantity_completed actual_qty_completed
1661 FROM
1662 wip_discrete_jobs wdj,
1663 wip_entities we
1664 WHERE wdj.Status_Type <> 12 -- Not closed
1665 AND we.entity_type IN (1,3, 5)
1666 AND wdj.job_type = 1 -- Standard Jobs only
1667 AND wdj.wip_entity_id = we.wip_entity_id
1668 AND wdj.organization_id = we.organization_id
1669 AND wdj.date_released >= g_global_start_date
1670 UNION
1671 SELECT /*+ no_merge use_hash(WRS WE) parallel(WRS)
1672 parallel(WE) */
1673 wrs.organization_id organization_id,
1674 we.primary_item_id inventory_item_id,
1675 wrs.wip_entity_id||'-'||wrs.repetitive_schedule_id
1676 job_idj,
1677 to_char(wrs.repetitive_schedule_id) job_id,
1678 2 job_type,
1679 wrs.status_type status_type,
1680 wrs.daily_production_rate *
1681 wrs.processing_work_days start_qty,
1682 wrs.quantity_completed actual_qty_completed
1683 FROM
1684 wip_repetitive_schedules wrs,
1685 wip_entities we
1686 WHERE
1687 wrs.status_type <> 12 -- not closed
1688 AND we.entity_type = 2
1689 AND we.wip_entity_id = wrs.wip_entity_id
1690 AND we.organization_id = wrs.organization_id
1691 AND wrs.date_released >= g_global_start_date
1692 ) not_cl_jobs, -- Note: Flow schedules can only be overcharged when the schedule is Closed, hence no need to extract them here.
1693 cst_item_costs icosts
1694 WHERE
1695 not_cl_jobs.organization_id =
1696 icosts.organization_id
1697 AND not_cl_jobs.inventory_item_id =
1698 icosts.inventory_item_id
1699 AND icosts.cost_type_id in (1,2,5,6)
1700 ) std_val_and_qty, -- Standard Values and Start and Completed Quantities
1701 (
1702 SELECT /*+ no_merge parallel(WPB) */
1703 wpb.organization_id organization_id,
1704 wpb.wip_entity_id || decode(wpb.repetitive_schedule_id, null, null, '-'||wpb.repetitive_schedule_id) job_id,
1705 --decode(wpb.repetitive_schedule_id,null,1,2) job_type,
1706 sum( tl_resource_in + tl_overhead_in +
1707 tl_outside_processing_in + pl_material_in +
1708 pl_material_overhead_in + pl_resource_in +
1709 pl_overhead_in + pl_outside_processing_in) act_cost_b
1710 FROM
1711 wip_period_balances wpb
1712 GROUP BY
1713 wpb.organization_id,
1714 wpb.wip_entity_id,
1715 --decode(wpb.repetitive_schedule_id,null,1,2),
1716 wpb.repetitive_schedule_id
1717 ) AC -- Actual Cost
1718 WHERE
1719 std_val_and_qty.organization_id = ac.organization_id
1720 AND std_val_and_qty.job_idj = ac.job_id
1721 --AND std_val_and_qty.job_type = ac.job_type
1722 AND ac.act_cost_b > std_val_and_qty.std_val_b -- filter cost charged greater than Std Cost
1723 ) x
1724 GROUP BY
1725 organization_id,
1726 inventory_item_id,
1727 to_number(job_id),
1728 to_number(job_type)
1729 ) act_std,
1730 mtl_system_items_b msi,
1731 eni_oltp_item_star cat,
1732 opi_dbi_cuv_conv_rates conv
1733 WHERE
1734 act_std.organization_id = msi.organization_id
1735 AND act_std.inventory_item_id = msi.inventory_item_id
1736 AND act_std.organization_id = cat.organization_id
1737 AND act_std.inventory_item_id = cat.inventory_item_id
1738 AND act_std.organization_id = conv.organization_id
1739 /* OPM Big Insert Select has to union with OPIs here (limitation of Insert append) */
1740 UNION ALL
1741 select /*+ ordered full(cat) */
1742 var.organization_id,
1743 var.assembly_item_id,
1744 var.assembly_item_id||'-'||var.organization_id,
1745 cat.inv_category_id,
1746 standard_value_b,
1747 standard_value_b*conversion_rate standard_value_g,
1748 standard_value_b*sec_conversion_rate standard_value_sg,
1749 actual_value_b,
1750 actual_value_b*conversion_rate standard_value_g,
1751 actual_value_b*sec_conversion_rate standard_value_sg,
1752 var.actual_qty_completed,
1753 var.uom_code,
1754 2 source,
1755 g_sysdate creation_date,
1756 g_sysdate last_update_date,
1757 g_user_id created_by,
1758 g_user_id last_updated_by,
1759 g_login_id last_update_login,
1760 var.job_id,
1761 var.job_type,
1762 g_program_id PROGRAM_ID,
1763 g_program_login_id PROGRAM_LOGIN_ID,
1764 g_program_application_id PROGRAM_APPLICATION_ID,
1765 g_request_id REQUEST_ID
1766 from
1767 OPI_DBI_CUV_CONV_RATES rates,
1768 (select /*+ full(gtv) use_hash(mtl_dtl) full(mtl_dtl) parallel(mtl_dtl) parallel(gtv) use_hash(jobs)*/
1769 jobs.organization_id,
1770 Jobs.assembly_item_id,
1771 jobs.job_id,
1772 jobs.job_type,
1773 jobs.status,
1774 OPI_DBI_JOBS_PKG.GET_OPM_ITEM_COST(
1775 jobs.organization_id,
1776 jobs.assembly_item_id,
1777 sysdate)*
1778 decode(jobs.status,'Complete',jobs.actual_qty_completed,
1779 greatest(Jobs.planned_qty,jobs.actual_qty_completed)) standard_value_b,
1780 Sum(Decode(jobs.line_type,-1,txn_base_value*mtl_dtl.cost_alloc,
1781 2,txn_base_value*mtl_dtl.cost_alloc, 0)) Actual_Value_b,
1782 jobs.planned_qty planned_qty,
1783 jobs.actual_qty_completed,
1784 jobs.uom_code
1785 from
1786 GMF_TRANSACTION_VALUATION GTV,
1787 OPI_DBI_ORG_LE_TEMP tmp,
1788 GME_MATERIAL_DETAILS MTL_DTL,
1789 (
1790 select /*+ no_merge ordered full(jobs) use_hash(mtl_dtl) full(mtl_dtl) */
1791 jobs.Job_id,
1792 jobs.assembly_item_id,
1793 jobs.job_type,
1794 jobs.status,
1795 jobs.uom_code,
1796 to_char(mtl_dtl.Inventory_item_id) item_resource_id,
1797 mtl_dtl.material_detail_id line_id,
1798 jobs.start_quantity planned_qty,
1799 jobs.actual_qty_completed,
1800 mtl_dtl.line_type line_type,
1801 jobs.Organization_id
1802 from
1803 OPI_DBI_JOBS_F jobs,
1804 GME_MATERIAL_DETAILS mtl_dtl
1805 where
1806 jobs.source = 2 and
1807 jobs.line_type = 1 and
1808 jobs.job_id = mtl_dtl.batch_id and
1809 mtl_dtl.line_type in (-1,2) and
1810 jobs.status in ('Released', 'WIP', 'Complete')
1811 union all
1812 select /*+ no_merge ordered full(jobs) use_hash(gbsr) full(gbsr) */
1813 job_id,
1814 jobs.assembly_item_id,
1815 jobs.job_type,
1816 jobs.status,
1817 jobs.uom_code,
1818 gbsr.resources item_resource_id,
1819 gbsr.batchstep_resource_id line_id,
1820 jobs.start_quantity planned_qty,
1821 jobs.actual_qty_completed,
1822 -1 line_type,
1823 jobs.Organization_id
1824 from
1825 OPI_DBI_JOBS_F jobs,
1826 GME_BATCH_STEP_RESOURCES gbsr
1827 where
1828 jobs.source = 2 and
1829 jobs.line_type = 1 and
1830 jobs.job_id = gbsr.batch_id and
1831 jobs.status in ('Released', 'WIP', 'Complete')
1832 ) Jobs
1833 where
1834 jobs.organization_id = GTV.organization_id and
1835 jobs.job_id = GTV.doc_id and
1836 (jobs.item_resource_id = to_char(GTV.inventory_item_id)
1837 or jobs.item_resource_id = GTV.resources) and
1838 GTV.line_id = jobs.line_id and
1839 GTV.journal_line_type in ('WIP') and
1840 GTV.event_class_code in ('BATCH_MATERIAL','BATCH_RESOURCE') and
1841 jobs.job_id = mtl_dtl.batch_id and
1842 jobs.organization_id = mtl_dtl.organization_id and
1843 jobs.assembly_item_id = mtl_dtl.inventory_item_id and
1844 mtl_dtl.line_type = 1 and
1845 GTV.ledger_id = tmp.ledger_id and
1846 GTV.legal_entity_id = tmp.legal_entity_id and
1847 GTV.valuation_cost_type_id = tmp.valuation_cost_type_id and
1848 GTV.organization_id = tmp. organization_id
1849 Group by
1850 jobs.organization_id,
1851 jobs.job_id,
1852 jobs.job_type,
1853 jobs.assembly_item_id,
1854 jobs.status,
1855 jobs.uom_code,
1856 jobs.planned_qty,
1857 jobs.actual_qty_completed
1858 )var
1859 , eni_oltp_item_Star cat
1860 where
1861 var.organization_id = rates.organization_id and
1862 var.assembly_item_id = cat.inventory_item_id and
1863 var.organization_id = cat.organization_id and
1864 var.actual_value_b > var.standard_value_b;
1865
1866 l_row_count := sql%rowcount;
1867
1868 commit;
1869
1870 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished OPI and OPM Current Unrecognized Variance into Fact Table: '|| l_row_count || ' rows inserted');
1871
1872 BIS_COLLECTION_UTILITIES.WRAPUP(
1873 p_status => TRUE,
1874 p_count => l_row_count,
1875 p_message => 'Successfully loaded Current Unrecognized Variance table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
1876 );
1877
1878 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1879
1880 ELSE
1881 retcode := g_error;
1882 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Parameters are not setup.');
1883 BIS_COLLECTION_UTILITIES.PUT_LINE('Please check that the profile option BIS_PRIMARY_CURRENCY_CODE is setup.');
1884 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
1885
1886 END IF;
1887
1888
1889 EXCEPTION
1890 WHEN OTHERS THEN
1891 rollback;
1892
1893 l_err_num := SQLCODE;
1894 l_err_msg := 'OPI_DBI_MTL_VARIANCE_PKG.GET_CURR_UNREC_VAR ('
1895 || to_char(l_stmt_num)
1896 || '): '
1897 || substr(SQLERRM, 1,200);
1898 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_MTL_VARIANCE_PKG.GET_CURR_UNREC_VAR - Error at statement ('
1899 || to_char(l_stmt_num)
1900 || ')');
1901 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
1902 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
1903 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
1904
1905 retcode := SQLCODE;
1906 errbuf := SQLERRM;
1907 RAISE_APPLICATION_ERROR(-20000, errbuf);
1908
1909 END GET_CURR_UNREC_VAR;
1910
1911
1912
1913 PROCEDURE Refresh_Base_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2, p_method in varchar2 DEFAULT '?')
1914 IS
1915 l_stmt_num NUMBER;
1916 l_err_num NUMBER;
1917 l_err_msg VARCHAR2(255);
1918 BEGIN
1919
1920 l_stmt_num := 10;
1921 DBMS_MVIEW.REFRESH(
1922 list => 'OPI_MTL_VAR_MV_F',
1923 method => p_method,
1924 parallelism => 0);
1925
1926
1927 BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh of Base Materialized View finished ...');
1928
1929
1930 EXCEPTION
1931 WHEN OTHERS THEN
1932
1933 l_err_num := SQLCODE;
1934 l_err_msg := 'OPI_DBI_MTL_VARIANCE_PKG.Refresh_Base_MV ('
1935 || to_char(l_stmt_num)
1936 || '): '
1937 || substr(SQLERRM, 1,200);
1938
1939 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_MTL_VARIANCE_PKG.Refresh_Base_MV - Error at statement ('
1940 || to_char(l_stmt_num)
1941 || ')');
1942
1943 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
1944 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
1945
1946 RAISE_APPLICATION_ERROR(-20000, errbuf);
1947
1948 END Refresh_Base_MV;
1949
1950 PROCEDURE REFRESH_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1951 IS
1952 l_stmt_num NUMBER;
1953 l_err_num NUMBER;
1954 l_err_msg VARCHAR2(255);
1955 BEGIN
1956
1957 /*l_stmt_num := 10;*/
1958 /* Material Details MV Refresh */
1959
1960 /*dbms_mview.refresh('OPI_MTL_VAR_SUM_MV',
1961 '?',
1962 '', -- ROLLBACK_SEG
1963 TRUE, -- PUSH_DEFERRED_RPC
1964 FALSE, -- REFRESH_AFTER_ERRORS
1965 0, -- PURGE_OPTION
1966 1, -- PARALLELISM
1967 0, -- HEAP_SIZE
1968 FALSE -- ATOMIC_REFRESH
1969 );
1970
1971 BIS_COLLECTION_UTILITIES.PUT_LINE('Material Details MV Refresh finished ...');*/
1972
1973 l_stmt_num := 20;
1974 /* MFG Cost Variance MV Refresh */
1975
1976 dbms_mview.refresh('OPI_MFG_VAR_SUM_MV',
1977 '?',
1978 '', -- ROLLBACK_SEG
1979 TRUE, -- PUSH_DEFERRED_RPC
1980 FALSE, -- REFRESH_AFTER_ERRORS
1981 0, -- PURGE_OPTION
1982 1, -- PARALLELISM
1983 0, -- HEAP_SIZE
1984 FALSE -- ATOMIC_REFRESH
1985 );
1986
1987 BIS_COLLECTION_UTILITIES.PUT_LINE('Manufacturing Cost Variance Refresh finished ...');
1988
1989 EXCEPTION
1990 WHEN OTHERS THEN
1991
1992 l_err_num := SQLCODE;
1993 l_err_msg := 'OPI_DBI_MTL_VARIANCE_PKG.REFRESH_MV ('
1994 || to_char(l_stmt_num)
1995 || '): '
1996 || substr(SQLERRM, 1,200);
1997
1998 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_MTL_VARIANCE_PKG.REFRESH_MV - Error at statement ('
1999 || to_char(l_stmt_num)
2000 || ')');
2001
2002 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
2003 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
2004
2005 RAISE_APPLICATION_ERROR(-20000, errbuf);
2006 /*please note that this api will commit!!*/
2007
2008 END REFRESH_MV;
2009
2010 /*
2011 Procedure checks for required setups.
2012
2013 Parameters:
2014 retcode - 0 on successful completion, -1 on error and 1 for warning.
2015 errbuf - empty on successful completion, message on error or warning
2016 */
2017
2018 PROCEDURE CHECK_OPI_MFG_CST_VAR_SETUP(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
2019 IS
2020 l_stmt_num NUMBER;
2021 l_row_count NUMBER;
2022 l_err_num NUMBER;
2023 l_err_msg VARCHAR2(255);
2024 l_proc_name VARCHAR2(255);
2025 BEGIN
2026
2027 l_proc_name := 'OPI_DBI_MTL_VARIANCE_PKG.CHECK_OPI_MFG_CST_VAR_SETUP';
2028
2029 BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2030
2031 /* calling setup for all fact tables */
2032 l_stmt_num := 10;
2033
2034 IF BIS_COLLECTION_UTILITIES.SETUP('OPI_DBI_MFG_CST_VAR_F') = false then
2035 RAISE_APPLICATION_ERROR(-20000, errbuf);
2036 END IF;
2037
2038 /* get global start date */
2039 l_stmt_num := 20;
2040 g_global_start_date := trunc (bis_common_parameters.get_global_start_date);
2041 IF (g_global_start_date IS NULL) THEN
2042 BIS_COLLECTION_UTILITIES.PUT_LINE ('The global Start date Not Set.');
2043
2044 RAISE_APPLICATION_ERROR(-20000, errbuf);
2045 END IF;
2046
2047 l_stmt_num := 30;
2048 -- Global currency codes -- already checked if primary is set up
2049 g_global_currency_code := bis_common_parameters.get_currency_code;
2050 g_secondary_currency_code := bis_common_parameters.get_secondary_currency_code;
2051
2052 -- Global rate types -- already checked if primary is set up
2053 g_global_rate_type := bis_common_parameters.get_rate_type;
2054 g_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
2055
2056 -- check that either both the secondary rate type and secondary
2057 -- rate are null, or that neither are null.
2058 IF ((g_secondary_currency_code IS NULL AND
2059 g_secondary_rate_type IS NOT NULL)
2060 OR
2061 (g_secondary_currency_code IS NOT NULL AND
2062 g_secondary_rate_type IS NULL)
2063 ) THEN
2064 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.');
2065
2066 RAISE_APPLICATION_ERROR(-20000, errbuf);
2067
2068 END IF;
2069
2070 l_stmt_num := 40;
2071 -- get R12 upgrade date
2072 OPI_DBI_RPT_UTIL_PKG.get_inv_convergence_date(g_r12_migration_date);
2073
2074 BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2075
2076 EXCEPTION
2077
2078 WHEN OTHERS THEN
2079 rollback;
2080 l_err_num := SQLCODE;
2081 l_err_msg := 'OPI_DBI_MTL_VARIANCE_PKG.CHECK_OPI_MFG_CST_VAR_SETUP ('
2082 || to_char(l_stmt_num)
2083 || '): '
2084 || substr(SQLERRM, 1,200);
2085 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_MTL_VARIANCE_PKG.CHECK_OPI_MFG_CST_VAR_SETUP - Error at statement ('
2086 || to_char(l_stmt_num)
2087 || ')');
2088 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
2089 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
2090 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
2091
2092 retcode := SQLCODE;
2093 errbuf := SQLERRM;
2094 RAISE_APPLICATION_ERROR(-20000, errbuf);
2095
2096 END CHECK_OPI_MFG_CST_VAR_SETUP;
2097
2098 END OPI_DBI_MTL_VARIANCE_PKG;