[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_OPM_COGS_PKG
Source
1 PACKAGE BODY opi_dbi_opm_cogs_pkg AS
2 /* $Header: OPIDMPRB.pls 115.22 2003/11/13 21:40:57 cdaly noship $ */
3
4 g_cogs_error BOOLEAN := FALSE;
5 g_cogs_rate_error EXCEPTION;
6
7 g_login_id NUMBER;
8 g_user_id NUMBER;
9 g_sysdate date;
10 g_global_rate_type varchar2(15);
11 GLOBAL_CURRENCY_CODE varchar2(10);
12 g_opi_schema VARCHAR2(30);
13 g_opi_status VARCHAR2(30);
14 g_opi_industry VARCHAR2(30);
15
16 global_start_date DATE;
17
18 PROCEDURE check_setup_globals(errbuf IN OUT NOCOPY VARCHAR2 , retcode IN OUT NOCOPY VARCHAR2) IS
19
20 l_list dbms_sql.varchar2_table;
21
22 l_from_date DATE;
23 l_to_date DATE;
24 l_missing_day_flag BOOLEAN := FALSE;
25 l_err_num NUMBER;
26 l_err_msg VARCHAR2(255);
27 l_min_miss_date DATE;
28 l_max_miss_date DATE;
29
30 l_inception_date DATE;
31 BEGIN
32
33 retcode := 0;
34 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
35 l_list(2) := 'BIS_GLOBAL_START_DATE';
36
37 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
38 g_login_id := fnd_global.login_id;
39 g_user_id := fnd_global.user_id;
40 global_start_date := Trunc(bis_common_parameters.get_global_start_date);
41 global_currency_code := bis_common_parameters.get_currency_code;
42
43 if (g_global_rate_type is null)
44 then g_global_rate_type := bis_common_parameters.get_rate_type;
45 end if;
46
47 g_sysdate := sysdate;
48
49 if not fnd_installation.get_app_info (application_short_name => 'OPI',
50 status => g_opi_status,
51 industry => g_opi_industry,
52 oracle_schema => g_opi_schema)
53 then
54 RAISE_APPLICATION_ERROR(-20000, errbuf);
55 END IF;
56
57
58 SELECT NVL(MIN(from_date), global_start_date) INTO l_from_date
59 FROM (SELECT tst.gl_trans_date from_date
60 FROM opi_dbi_cogs_log l,
61 gl_subr_tst tst
62 WHERE l.extraction_type = 'COGS_SUBLEDGER'
63 AND tst.subledger_id = l.transaction_id
64 UNION
65 SELECT tst.gl_trans_date from_date
66 FROM opi_dbi_cogs_log l,
67 gl_subr_led tst
68 WHERE l.extraction_type = 'COGS_SUBLEDGER'
69 AND tst.subledger_id = l.transaction_id
70 UNION
71 SELECT aid.accounting_date from_date
72 FROM opi_dbi_cogs_log l,
73 ap_invoice_distributions_all aid
74 WHERE l.extraction_type = 'COGS_AP'
75 AND aid.invoice_distribution_id = l.transaction_id
76 );
77
78 l_to_date := sysdate;
79
80
81 -- IF l_from_date = global_start_date THEN
82 -- it might be the initial load. check min(trans_date) in ic_tran_pnd
83
84 -- l_from_date:= Greatest(l_from_date, l_inception_date);
85 -- END IF;
86
87 -- check_missing_date
88
89 fii_time_api.check_missing_date( l_from_date, l_to_date, l_missing_day_flag,
90 l_min_miss_date, l_max_miss_date);
91
92 IF l_missing_day_flag THEN
93 retcode := 1;
94 errbuf := 'Please check log file for details. ';
95 BIS_COLLECTION_UTILITIES.PUT_LINE('There are missing dates in Time Dimension.');
96
97 BIS_COLLECTION_UTILITIES.PUT_LINE('The range is from ' || l_min_miss_date
98 ||' to ' || l_max_miss_date );
99 END IF;
100 ELSE
101 retcode := 1;
102 errbuf := 'Please check log file for details. ';
103 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Parameters are not setup.');
104
105 BIS_COLLECTION_UTILITIES.PUT_LINE('Please check that the profile options: BIS_PRIMARY_CURRENCY_CODE and BIS_GLOBAL_START_DATE are setup.');
106
107 END IF;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 retcode := 1;
112 l_err_num := SQLCODE;
113 l_err_msg := 'ERROR in OPI_DBI_OPM_COGS_PKG.CHECK_SETUP_GLOBALS '
114 || substr(SQLERRM, 1,200);
115
116 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
117 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
118
119 END check_setup_globals;
120
121 -- return 0 -- normal
122 -- return -1 -- missing rate found
123 FUNCTION Report_Missing_Rate return NUMBER IS
124
125 cursor get_missing_rate_c is
126 select distinct cogs_currency_code,
127 decode(cogs_conversion_rate, -3, to_Date('01/01/1999', 'MM/DD/YYYY'), cogs_date) cogs_date
128 from opi_dbi_cogs_stg
129 where NVL(cogs_conversion_rate,-99) < 0 ;
130
131 get_missing_rate_rec get_missing_rate_c%ROWTYPE;
132
133 l_stmt_num NUMBER;
134 no_currency_rate_flag NUMBER := 0;
135 i_err_num NUMBER;
136 i_err_msg VARCHAR2(255);
137
138 BEGIN
139
140 l_stmt_num := 20; /* call api to get get_global_rate_primary */
141 OPEN get_missing_rate_c;
142 LOOP
143 FETCH get_missing_rate_c into get_missing_rate_rec;
144 EXIT WHEN get_missing_rate_c%notfound;
145
146 IF (no_currency_rate_flag = 0) THEN
147 no_currency_rate_flag := 1;
148 END IF;
149 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
150 BIS_COLLECTION_UTILITIES.writemissingrate
151 (g_global_rate_type,
152 get_missing_rate_rec.cogs_currency_code,
153 global_currency_code,
154 get_missing_rate_rec.cogs_date);
155
156 END LOOP;
157
158 CLOSE get_missing_rate_c;
159
160 l_stmt_num := 30; /* check no_currency_rate_flag */
161 IF (no_currency_rate_flag = 1) THEN /* missing rate found */
162 BIS_COLLECTION_UTILITIES.PUT_LINE('Please setup conversion rate for all missing rates reported');
163 return (-1);
164 END IF;
165 return (0);
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 rollback;
170 i_err_num := SQLCODE;
171 i_err_msg := 'REPORT_MISSING_RATE (' || to_char(l_stmt_num)
172 || '): '|| substr(SQLERRM, 1,200);
173
174 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_MARGIN_VALUE_PKG.REPORT_MISSING_RATE - Error at statement ('
175 || to_char(l_stmt_num)
176 || ')');
177
178 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(i_err_num));
179 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || i_err_msg);
180
181 return -1;
182 END REPORT_MISSING_RATE ;
183
184 FUNCTION check_ici(p_ship_ou_id NUMBER, p_sell_ou_id NUMBER ) RETURN NUMBER IS
185 l_ici_flag VARCHAR2(1);
186
187 BEGIN
188 SELECT 'Y' INTO l_ici_flag
189 FROM mtl_intercompany_parameters mip
190 WHERE mip.ship_organization_id = p_ship_ou_id
191 AND mip.sell_organization_id = p_sell_ou_id ;
192
193 RETURN 1;
194
195 EXCEPTION WHEN NO_DATA_FOUND THEN
196 RETURN 0;
197 END check_ici;
198
199
200 PROCEDURE refresh_opm_subl_org_cogs(
201 p_last_id IN NUMBER,
202 p_newest_id IN NUMBER,
203 x_status OUT NOCOPY NUMBER,
204 x_msg OUT NOCOPY VARCHAR2 ) IS
205
206 l_stmt NUMBER := 0;
207
208
209 BEGIN
210
211 -- Regular sales order
212 insert /*+ append */ into opi_dbi_opm_cogstst_current
213 ( INVENTORY_ITEM_ID
214 ,ORGANIZATION_ID
215 ,ORDER_LINE_ID
216 ,MARGIN_OU_ID
217 ,COGS_VAL_B
218 ,COGS_DATE
219 ,COGS_CURRENCY_CODE
220 ,COGS_CONVERSION_RATE
221 ,SHIP_OU_ID
222 ,SELL_OU_ID
223 ,TURNS_COGS_FLAG
224 ,SOURCE
225 )
226 select
227 INVENTORY_ITEM_ID
228 ,ORGANIZATION_ID
229 ,ORDER_LINE_ID
230 ,MARGIN_OU_ID
231 ,sum(COGS_VAL_B)
232 ,max(COGS_DATE)
233 ,COGS_CURRENCY_CODE
234 ,fii_currency.get_global_rate_primary
235 (cogs_currency_code, max(cogs_date)) COGS_CONVERSION_RATE
236 ,SHIP_OU_ID
237 ,SELL_OU_ID
238 ,TURNS_COGS_FLAG
239 ,SOURCE
240 from
241 ( select
242 lines.inventory_item_id INVENTORY_ITEM_ID ,
243 whse.mtl_organization_id ORGANIZATION_ID ,
244 tran.oe_order_line_id ORDER_LINE_ID ,
245 Decode(lines.source_type_code, 'EXTERNAL', lines.org_id, --drop ship
246 Decode(ou.ship_ou_id, lines.org_id, lines.org_id, -- Same OU,
247 Decode(check_ici(ou.ship_ou_id, lines.org_id), 1, ou.ship_ou_id, -- RO, ICI
248 0, lines.org_id) -- RO, No ICI
249 ) ) MARGIN_OU_ID ,
250
251 tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
252 trunc(GL_TRANS_DATE) COGS_DATE ,
253 tst.currency_base COGS_CURRENCY_CODE ,
254 ou.ship_ou_id SHIP_OU_ID ,
255 lines.org_id SELL_OU_ID ,
256 Decode(lines.source_type_code, 'EXTERNAL', 'N', --drop ship
257 'Y' ) TURNS_COGS_FLAG ,
258 Decode( lines.line_category_code, 'RETURN', 'OPM_RMA',
259 Decode(lines.source_type_code, 'EXTERNAL', 'OPM_RO_DROP', --drop ship
260 Decode(ou.ship_ou_id, lines.org_id, 'OPM_RO', -- Same OU,
261 Decode(check_ici(ou.ship_ou_id, lines.org_id), 1, 'OPM_RO_ICI',
262 0, 'OPM_RO_NOICI', 'OPM') ) ) ) SOURCE
263 from gl_subr_tst tst,
264 oe_order_lines_all lines,
265 ic_whse_mst whse,
266 (
267 SELECT hou.organization_id organization_id,
268 gsob.currency_code currency_code,
269 to_number(HOI.org_information3) ship_ou_id
270 FROM hr_all_organization_units hou,
271 hr_organization_information hoi,
272 gl_sets_of_books gsob
273 WHERE hou.organization_id = hoi.organization_id
274 AND ( hoi.org_information_context || '') ='Accounting Information'
275 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
276 ) OU,
277 (
278 select tran.doc_type,
279 rcv.oe_order_line_id oe_order_line_id,
280 tran.line_id,
281 tran.orgn_code,
282 tran.whse_code
283 from ic_tran_pnd tran,
284 rcv_transactions rcv
285 where doc_type = 'PORC'
286 and completed_ind = 1
287 and gl_posted_ind = 0
288 and tran.line_id = rcv.transaction_id
289 and rcv.oe_order_line_id is NOT NULL
290 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
291 union all
292 select tran.doc_type,
293 rcv.oe_order_line_id oe_order_line_id,
294 tran.line_id,
295 tran.orgn_code,
296 tran.whse_code
297 from ic_tran_cmp tran,
298 rcv_transactions rcv
299 where doc_type = 'PORC'
300 and gl_posted_ind = 0
301 and tran.line_id = rcv.transaction_id
302 and rcv.oe_order_line_id is NOT NULL
303 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
304 union all
305 select tran.doc_type,
306 tran.line_id oe_order_line_id,
307 tran.line_id,
308 tran.orgn_code,
309 tran.whse_code
310 from ic_tran_pnd tran
311 where doc_type = 'OMSO'
312 and completed_ind = 1
313 and gl_posted_ind = 0
314 group by doc_type, line_id, line_id, orgn_code, whse_code
315 union all
316 select tran.doc_type,
317 tran.line_id oe_order_line_id,
318 tran.line_id,
319 tran.orgn_code,
320 tran.whse_code
321 from ic_tran_cmp tran
322 where doc_type = 'OMSO'
323 and gl_posted_ind = 0
324 group by doc_type, line_id, line_id, orgn_code, whse_code ) tran
325 where tst.doc_type in ( 'OMSO', 'PORC' )
326 and tst.acct_ttl_type = 5200
327 and lines.line_id = tran.oe_order_line_id
328 and tran.doc_type = tst.doc_type
329 and tran.line_id = tst.line_id
330 and whse.whse_code = tran.whse_code
331 AND whse.mtl_organization_id = ou.organization_id
332 )
333 group by
334 INVENTORY_ITEM_ID
335 ,ORGANIZATION_ID
336 ,ORDER_LINE_ID
337 ,MARGIN_OU_ID
338 ,COGS_CURRENCY_CODE
339 ,SHIP_OU_ID
340 ,SELL_OU_ID
341 ,TURNS_COGS_FLAG
342 ,SOURCE ;
343
344 commit;
345 fnd_stats.gather_table_stats( ownname => g_opi_schema,
346 tabname => 'OPI_DBI_OPM_COGSTST_CURRENT',
347 percent => 10);
348
349
350
351 insert /*+ append */ into opi_dbi_opm_cogsled_current
352 ( INVENTORY_ITEM_ID
353 ,ORGANIZATION_ID
354 ,ORDER_LINE_ID
355 ,MARGIN_OU_ID
356 ,COGS_VAL_B
357 ,COGS_DATE
358 ,COGS_CURRENCY_CODE
359 ,COGS_CONVERSION_RATE
360 ,SHIP_OU_ID
361 ,SELL_OU_ID
362 ,TURNS_COGS_FLAG
363 ,SOURCE
364 )
365 select
366 INVENTORY_ITEM_ID
367 ,ORGANIZATION_ID
368 ,ORDER_LINE_ID
369 ,MARGIN_OU_ID
370 ,sum(COGS_VAL_B)
371 ,max(COGS_DATE)
372 ,COGS_CURRENCY_CODE
373 ,fii_currency.get_global_rate_primary
374 (cogs_currency_code, max(cogs_date)) COGS_CONVERSION_RATE
375 ,SHIP_OU_ID
376 ,SELL_OU_ID
377 ,TURNS_COGS_FLAG
378 ,SOURCE
379 from
380 ( select
381 lines.inventory_item_id INVENTORY_ITEM_ID ,
382 whse.mtl_organization_id ORGANIZATION_ID ,
383 tran.oe_order_line_id ORDER_LINE_ID ,
384 Decode(lines.source_type_code, 'EXTERNAL', lines.org_id, --drop ship
385 Decode(ou.ship_ou_id, lines.org_id, lines.org_id, -- Same OU,
386 Decode(check_ici(ou.ship_ou_id, lines.org_id), 1, ou.ship_ou_id, -- RO, ICI
387 0, lines.org_id) -- RO, No ICI
388 ) ) MARGIN_OU_ID ,
389 tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
390 trunc(GL_TRANS_DATE) COGS_DATE ,
391 tst.currency_base COGS_CURRENCY_CODE ,
392 ou.ship_ou_id SHIP_OU_ID ,
393 lines.org_id SELL_OU_ID ,
394 Decode(lines.source_type_code, 'EXTERNAL', 'N', --drop ship
395 'Y' ) TURNS_COGS_FLAG ,
396 Decode( lines.line_category_code, 'RETURN', 'OPM_RMA',
397 Decode(lines.source_type_code, 'EXTERNAL', 'OPM_RO_DROP', --drop ship
398 Decode(ou.ship_ou_id, lines.org_id, 'OPM_RO', -- Same OU,
399 Decode(check_ici(ou.ship_ou_id, lines.org_id), 1, 'OPM_RO_ICI',
400 0, 'OPM_RO_NOICI', 'OPM') ) ) ) SOURCE
401 from gl_subr_led tst,
402 oe_order_lines_all lines,
403 ic_whse_mst whse,
404 (
405 SELECT hou.organization_id organization_id,
406 gsob.currency_code currency_code,
407 to_number(HOI.org_information3) ship_ou_id
408 FROM hr_all_organization_units hou,
409 hr_organization_information hoi,
410 gl_sets_of_books gsob
411 WHERE hou.organization_id = hoi.organization_id
412 AND ( hoi.org_information_context || '') ='Accounting Information'
413 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
414 ) OU,
415 (
416 select tran.doc_type,
417 rcv.oe_order_line_id oe_order_line_id,
418 tran.line_id,
419 tran.orgn_code,
420 tran.whse_code
421 from ic_tran_pnd tran,
422 rcv_transactions rcv
423 where doc_type = 'PORC'
424 and completed_ind = 1
425 and gl_posted_ind = 1
426 and tran.line_id = rcv.transaction_id
427 and rcv.oe_order_line_id is NOT NULL
428 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
429 union all
430 select tran.doc_type,
431 rcv.oe_order_line_id oe_order_line_id,
432 tran.line_id,
433 tran.orgn_code,
434 tran.whse_code
435 from ic_tran_cmp tran,
436 rcv_transactions rcv
437 where doc_type = 'PORC'
438 and gl_posted_ind = 1
439 and tran.line_id = rcv.transaction_id
440 and rcv.oe_order_line_id is NOT NULL
441 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
442 union all
443 select tran.doc_type,
444 tran.line_id oe_order_line_id,
445 tran.line_id,
446 tran.orgn_code,
447 tran.whse_code
448 from ic_tran_pnd tran
449 where doc_type = 'OMSO'
450 and completed_ind = 1
451 and gl_posted_ind = 1
452 group by doc_type, line_id, line_id, orgn_code, whse_code
453 union all
454 select tran.doc_type,
455 tran.line_id oe_order_line_id,
456 tran.line_id,
457 tran.orgn_code,
458 tran.whse_code
459 from ic_tran_cmp tran
460 where doc_type = 'OMSO'
461 and gl_posted_ind = 1
462 group by doc_type, line_id, line_id, orgn_code, whse_code ) tran
463 where tst.doc_type in ( 'OMSO', 'PORC' )
464 and tst.acct_ttl_type = 5200
465 and lines.line_id = tran.oe_order_line_id
466 and tran.doc_type = tst.doc_type
467 and tran.line_id = tst.line_id
468 and whse.whse_code = tran.whse_code
469 AND whse.mtl_organization_id = ou.organization_id
470 and tst.subledger_id between p_last_id and p_newest_id
471 )
472 group by
473 INVENTORY_ITEM_ID
474 ,ORGANIZATION_ID
475 ,ORDER_LINE_ID
476 ,MARGIN_OU_ID
477 ,COGS_CURRENCY_CODE
478 ,SHIP_OU_ID
479 ,SELL_OU_ID
480 ,TURNS_COGS_FLAG
481 ,SOURCE ;
482
483 commit;
484 fnd_stats.gather_table_stats( ownname => g_opi_schema,
485 tabname => 'OPI_DBI_OPM_COGSLED_CURRENT',
486 percent => 10);
487
488
489
490 insert /*+ append */ INTO opi_dbi_cogs_stg
491 (inventory_item_id,
492 organization_id,
493 order_line_id,
494 margin_ou_id,
495 cogs_val_b, cogs_date,
496 cogs_currency_code,
497 cogs_conversion_rate,
498 ship_ou_id,
499 sell_ou_id,
500 turns_cogs_flag,
501 source,
502 creation_date,
503 last_update_date,
504 created_by,
505 last_updated_by,
506 last_update_login)
507 SELECT
508 INVENTORY_ITEM_ID
509 ,ORGANIZATION_ID
510 ,ORDER_LINE_ID
511 ,MARGIN_OU_ID
512 ,sum(cogs_val_b) COGS_VAL_B
513 ,max(cogs_date) COGS_DATE
514 ,COGS_CURRENCY_CODE
515 ,COGS_CONVERSION_RATE
516 ,SHIP_OU_ID
517 ,SELL_OU_ID
518 ,TURNS_COGS_FLAG
519 ,SOURCE
520 , g_Sysdate, g_Sysdate, g_user_id, g_user_id, g_login_id
521 FROM
522 (select
523 INVENTORY_ITEM_ID
524 ,ORGANIZATION_ID
525 ,ORDER_LINE_ID
526 ,MARGIN_OU_ID
527 ,COGS_VAL_B
528 ,COGS_DATE
529 ,COGS_CURRENCY_CODE
530 ,COGS_CONVERSION_RATE
531 ,SHIP_OU_ID
532 ,SELL_OU_ID
533 ,TURNS_COGS_FLAG
534 ,SOURCE
535 from opi_dbi_opm_cogstst_current
536 union all
537 select
538 INVENTORY_ITEM_ID
539 ,ORGANIZATION_ID
540 ,ORDER_LINE_ID
541 ,MARGIN_OU_ID
542 ,-COGS_VAL_B
543 ,COGS_DATE
544 ,COGS_CURRENCY_CODE
545 ,COGS_CONVERSION_RATE
546 ,SHIP_OU_ID
547 ,SELL_OU_ID
548 ,TURNS_COGS_FLAG
549 ,SOURCE
550 from opi_dbi_opm_cogstst_prior
551 union all
552 select
553 INVENTORY_ITEM_ID
554 ,ORGANIZATION_ID
555 ,ORDER_LINE_ID
556 ,MARGIN_OU_ID
557 ,COGS_VAL_B
558 ,COGS_DATE
559 ,COGS_CURRENCY_CODE
560 ,COGS_CONVERSION_RATE
561 ,SHIP_OU_ID
562 ,SELL_OU_ID
563 ,TURNS_COGS_FLAG
564 ,SOURCE
565 from opi_dbi_opm_cogsled_current
566 )
567 group by
568 INVENTORY_ITEM_ID
569 ,ORGANIZATION_ID
570 ,ORDER_LINE_ID
571 ,MARGIN_OU_ID
572 ,COGS_CURRENCY_CODE
573 ,COGS_CONVERSION_RATE
574 ,SHIP_OU_ID
575 ,SELL_OU_ID
576 ,TURNS_COGS_FLAG
577 ,SOURCE
578 , g_Sysdate, g_Sysdate, g_user_id, g_user_id, g_login_id
579 ;
580
581
582 l_stmt := 1;
583
584 execute immediate 'truncate table ' || g_opi_schema
585 || '.opi_dbi_opm_cogsled_current ';
586
587 execute immediate 'truncate table ' || g_opi_schema
588 || '.opi_dbi_opm_cogstst_prior ';
589
590 commit;
591 fnd_stats.gather_table_stats( ownname => g_opi_schema,
592 tabname => 'OPI_DBI_COGS_STG',
593 percent => 10);
594
595
596 insert /*+ append */ into opi_dbi_opm_cogstst_prior
597 ( INVENTORY_ITEM_ID
598 ,ORGANIZATION_ID
599 ,ORDER_LINE_ID
600 ,MARGIN_OU_ID
601 ,COGS_VAL_B
602 ,COGS_DATE
603 ,COGS_CURRENCY_CODE
604 ,COGS_CONVERSION_RATE
605 ,SHIP_OU_ID
606 ,SELL_OU_ID
607 ,TURNS_COGS_FLAG
608 ,SOURCE
609 )
610 select
611 INVENTORY_ITEM_ID
612 ,ORGANIZATION_ID
613 ,ORDER_LINE_ID
614 ,MARGIN_OU_ID
615 ,COGS_VAL_B
616 ,COGS_DATE
617 ,COGS_CURRENCY_CODE
618 ,COGS_CONVERSION_RATE
619 ,SHIP_OU_ID
620 ,SELL_OU_ID
621 ,TURNS_COGS_FLAG
622 ,SOURCE
623 from opi_dbi_opm_cogstst_current;
624
625 commit;
626 fnd_stats.gather_table_stats( ownname => g_opi_schema,
627 tabname => 'OPI_DBI_OPM_COGSTST_PRIOR',
628 percent => 10);
629
630
631 execute immediate 'truncate table ' || g_opi_schema
632 || '.opi_dbi_opm_cogstst_current ';
633
634 x_status := 1; -- complete successfully
635 x_msg := NULL;
636 EXCEPTION WHEN OTHERS THEN
637 ROLLBACK;
638
639 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Refresh_opm_subl_org_cogs at statement ' || l_stmt);
640 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
641 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Refresh_opm_subl_org_cogs at statement ' || l_stmt);
642 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
643 x_status := 0; -- error
644 x_msg := Sqlerrm;
645
646 END refresh_opm_subl_org_cogs;
647
648 PROCEDURE refresh_opi_icap_cogs( p_last_dist_id IN NUMBER,
649 p_new_dist_id IN NUMBER,
650 x_status OUT NOCOPY NUMBER,
651 x_msg OUT NOCOPY VARCHAR2) IS
652
653 BEGIN
654 merge INTO opi_dbi_cogs_stg m
655 using ( SELECT
656 pl.inventory_item_id top_model_item_id,
657 pl.line_id top_model_order_line_id,
658 pl.org_id sell_ou_id,
659 to_number(HOI.org_information3) ship_ou_id,
660 pl.org_id margin_ou_id,
661 'N' turns_cogs_flag,
662 trunc( max(aid.accounting_date)) cogs_date,
663 gsob.currency_code currency_code,
664 fii_currency.get_global_rate_primary
665 (gsob.currency_code, trunc( max(aid.accounting_date)) ) cogs_conversion_rate,
666 SUM( Nvl(aid.base_amount, aid.amount) ) cogs_val_b
667 FROM ap_invoice_distributions_all aid,
668 ap_invoices_all ai,
669 ra_customer_trx_lines_all rcl,
670 oe_order_lines_all l,
671 oe_order_lines_all pl,
672 hr_organization_information hoi,
673 gl_sets_of_books gsob,
674 hr_organization_information hoi2
675 WHERE aid.invoice_distribution_id >= p_last_dist_id
676 AND aid.invoice_distribution_id < p_new_dist_id
677 AND ai.invoice_id = aid.invoice_id
678 AND ai.source = 'Intercompany'
679 and ai.org_id = aid.org_id
680 and aid.line_type_lookup_code = 'ITEM'
681 and translate( lower(aid.REFERENCE_1), 'abcdefghijklmnopqrstuvwxyz_ -+0123456789',
682 'abcdefghijklmnopqrstuvwxyz_ -+') is null
683 and rcl.CUSTOMER_TRX_LINE_ID = to_number(aid.REFERENCE_1)
684 and l.line_id = rcl.interface_line_attribute6
685 and pl.line_id = nvl(l.top_model_line_id, l.line_id)
686 AND hoi.organization_id = pl.org_id
687 AND ( hoi.org_information_context || '') ='Accounting Information'
688 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
689 AND hoi2.organization_id = rcl.interface_line_attribute3
690 AND ( hoi.org_information_context || '') ='Accounting Information'
691 group by pl.line_id, pl.inventory_item_id, pl.org_id, hoi.org_information3, gsob.currency_code ) c
692 ON ( m.order_line_id = c.top_model_order_line_id
693 AND m.margin_ou_id = c.margin_ou_id )
694 WHEN matched THEN UPDATE SET
695 m.cogs_val_b = Nvl(m.cogs_val_b,0) + Nvl(c.cogs_val_b,0),
696 m.cogs_date= Greatest( Nvl(m.cogs_date,c.cogs_date), c.cogs_date),
697 m.cogs_conversion_rate = Decode(Sign(c.cogs_date - m.cogs_date),
698 1, c.cogs_conversion_rate,m.cogs_conversion_rate),
699 m.cogs_currency_code = Decode( Sign(c.cogs_conversion_rate), -1, c.currency_code, NULL),
700 m.source = 'OPI_AP',
701 m.last_update_date = Sysdate,
702 m.last_updated_by = g_user_id,
703 m.last_update_login = g_login_id
704 WHEN NOT matched THEN
705 INSERT (m.inventory_item_id, m.organization_id, m.order_line_id,
706 m.margin_ou_id, m.cogs_val_b, m.cogs_date, m.cogs_conversion_rate,
707 m.cogs_currency_code,
708 m.ship_ou_id, m.sell_ou_id, m.turns_cogs_flag,
709 m.source, m.creation_date, m.last_update_date,
710 m.created_by, m.last_updated_by, m.last_update_login)
711 VALUES (c.top_model_item_id, null, c.top_model_order_line_id,
712 c.margin_ou_id, c.cogs_val_b, c.cogs_date, c.cogs_conversion_rate,
713 Decode( Sign(c.cogs_conversion_rate), -1, c.currency_code, NULL),
714 c.ship_ou_id, c.sell_ou_id, c.turns_cogs_flag,
715 'OPI_AP', Sysdate, Sysdate,
716 g_user_id, g_user_id, g_login_id)
717 ;
718
719 x_status := 1;
720 x_msg := NULL;
721 EXCEPTION WHEN OTHERS THEN
722 ROLLBACK;
723 x_status := 0; -- error
724 x_msg := Sqlerrm;
725 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Refresh_opi_icap_cogs ');
726 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
727
728 END refresh_opi_icap_cogs;
729
730 PROCEDURE initial_opm_subl_org_cogs(
731 p_last_id IN NUMBER,
732 p_newest_id IN NUMBER,
733 x_status OUT NOCOPY NUMBER,
734 x_msg OUT NOCOPY VARCHAR2,
735 p_degree IN NUMBER ) IS
736
737 l_stmt NUMBER := 0;
738
739 BEGIN
740
741 -- execute immediate "alter session set sort_area_size=100000000" ;
742 -- execute immediate "alter session set hash_area_size=100000000" ;
743
744
745 -- execute immediate 'alter session force parallel query parallel '||p_degree ;
746
747 -- Regular sales order
748 insert /*+ APPEND PARALLEL(F) */
749 into opi_dbi_opm_cogstst_current F
750 ( INVENTORY_ITEM_ID
751 ,ORGANIZATION_ID
752 ,ORDER_LINE_ID
753 ,MARGIN_OU_ID
754 ,COGS_VAL_B
755 ,COGS_DATE
756 ,COGS_CURRENCY_CODE
757 ,COGS_CONVERSION_RATE
758 ,SHIP_OU_ID
759 ,SELL_OU_ID
760 ,TURNS_COGS_FLAG
761 ,SOURCE
762 )
763 select /*+ PARALLEL(COGS) PARALLEL(MIP) */
764 INVENTORY_ITEM_ID
765 ,ORGANIZATION_ID
766 ,ORDER_LINE_ID
767 ,Decode (margin_ou_id,
768 0, Decode(mip.sell_organization_id,
769 NULL, sell_ou_id, -- NULL indicates no mip row set up, therefore no ici
770 ship_ou_id -- else mip row set up, therefore ici
771 ),
772 margin_ou_id
773 ) MARGIN_OU_ID
774 ,COGS_VAL_B
775 ,COGS_DATE
776 ,COGS_CURRENCY_CODE
777 ,COGS_CONVERSION_RATE
778 ,SHIP_OU_ID
779 ,SELL_OU_ID
780 ,TURNS_COGS_FLAG
781 ,decode (SOURCE, 'OPM_CHECK_ICI',
782 Decode(mip.sell_organization_id,
783 NULL, 'OPM_RO_NOICI', -- RO, NO ICI
784 'OPM_RO_ICI'), -- RO, ICI
785 SOURCE ) SOURCE
786 from
787 (
788 select /*+ PARALLEL(A) */
789 INVENTORY_ITEM_ID
790 ,ORGANIZATION_ID
791 ,ORDER_LINE_ID
792 ,MARGIN_OU_ID
793 ,sum(COGS_VAL_B) COGS_VAL_B
794 ,max(COGS_DATE) COGS_DATE
795 ,COGS_CURRENCY_CODE
796 , fii_currency.get_global_rate_primary
797 (cogs_currency_code, max(cogs_date)) COGS_CONVERSION_RATE
798 ,SHIP_OU_ID
799 ,SELL_OU_ID
800 ,TURNS_COGS_FLAG
801 ,SOURCE
802 from
803 ( select /*+ FULL(tst) PARALLEL(TST) PARALLEL(LINES) PARALLEL(WHSE) PARALLEL(OU) PARALLEL(TRAN) */
804 lines.inventory_item_id INVENTORY_ITEM_ID ,
805 whse.mtl_organization_id ORGANIZATION_ID ,
806 tran.oe_order_line_id ORDER_LINE_ID ,
807 Decode(lines.source_type_code, 'EXTERNAL', lines.org_id, --drop ship
808 Decode(ou.ship_ou_id, lines.org_id, lines.org_id, -- Same OU,
809 0 -- if need to check ici, set OU in outer query
810 ) ) MARGIN_OU_ID ,
811 tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
812 trunc(GL_TRANS_DATE) COGS_DATE ,
813 tst.currency_base COGS_CURRENCY_CODE ,
814 ou.ship_ou_id SHIP_OU_ID ,
815 lines.org_id SELL_OU_ID ,
816 Decode(lines.source_type_code, 'EXTERNAL', 'N', --drop ship
817 'Y' ) TURNS_COGS_FLAG ,
818 Decode( lines.line_category_code, 'RETURN', 'OPM_RMA',
819 Decode(lines.source_type_code, 'EXTERNAL', 'OPM_RO_DROP', --drop ship
820 Decode(ou.ship_ou_id, lines.org_id, 'OPM_RO', -- Same OU,
821 'OPM_CHECK_ICI' ) ) ) SOURCE
822 from gl_subr_tst tst,
823 oe_order_lines_all lines,
824 ic_whse_mst whse,
825 (
826 SELECT /*+ PARALLEL(HOU) PARALLEL(HOI) PARALLEL(GSOB) */
827 hou.organization_id organization_id,
828 gsob.currency_code currency_code,
829 to_number(HOI.org_information3) ship_ou_id
830 FROM hr_all_organization_units hou,
831 hr_organization_information hoi,
832 gl_sets_of_books gsob
833 WHERE hou.organization_id = hoi.organization_id
834 AND ( hoi.org_information_context || '') ='Accounting Information'
835 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
836 ) OU,
837 (
838 select /*+ PARALLEL(TRAN) PARALLEL(RCV) */
839 tran.doc_type,
840 rcv.oe_order_line_id oe_order_line_id,
841 tran.line_id,
842 tran.orgn_code,
843 tran.whse_code
844 from ic_tran_pnd tran,
845 rcv_transactions rcv
846 where doc_type = 'PORC'
847 and completed_ind = 1
848 and gl_posted_ind = 0
849 and tran.line_id = rcv.transaction_id
850 and rcv.oe_order_line_id is NOT NULL
851 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
852 union all
853 select /*+ PARALLEL(TRAN) PARALLEL(RCV) */
854 tran.doc_type,
855 rcv.oe_order_line_id oe_order_line_id,
856 tran.line_id,
857 tran.orgn_code,
858 tran.whse_code
859 from ic_tran_cmp tran,
860 rcv_transactions rcv
861 where doc_type = 'PORC'
862 and gl_posted_ind = 0
863 and tran.line_id = rcv.transaction_id
864 and rcv.oe_order_line_id is NOT NULL
865 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
866 union all
867 select /*+ PARALLEL(TRAN) */
868 tran.doc_type,
869 tran.line_id oe_order_line_id,
870 tran.line_id,
871 tran.orgn_code,
872 tran.whse_code
873 from ic_tran_pnd tran
874 where doc_type = 'OMSO'
875 and completed_ind = 1
876 and gl_posted_ind = 0
877 group by doc_type, line_id, line_id, orgn_code, whse_code
878 union all
879 select /*+ PARALLEL(TRAN) */
880 tran.doc_type,
881 tran.line_id oe_order_line_id,
882 tran.line_id,
883 tran.orgn_code,
884 tran.whse_code
885 from ic_tran_cmp tran
886 where doc_type = 'OMSO'
887 and gl_posted_ind = 0
888 group by doc_type, line_id, line_id, orgn_code, whse_code ) tran
889 where tst.doc_type in ( 'OMSO', 'PORC' )
890 and tst.acct_ttl_type = 5200
891 and lines.line_id = tran.oe_order_line_id
892 and tran.doc_type = tst.doc_type
893 and tran.line_id = tst.line_id
894 and whse.whse_code = tran.whse_code
895 AND whse.mtl_organization_id = ou.organization_id
896 ) A
897 group by
898 INVENTORY_ITEM_ID
899 ,ORGANIZATION_ID
900 ,ORDER_LINE_ID
901 ,MARGIN_OU_ID
902 ,COGS_CURRENCY_CODE
903 ,SHIP_OU_ID
904 ,SELL_OU_ID
905 ,TURNS_COGS_FLAG
906 ,SOURCE
907 ) cogs,
908 mtl_intercompany_parameters mip
909 where mip.ship_organization_id(+) = cogs.ship_ou_id
910 AND mip.sell_organization_id(+) = cogs.sell_ou_id
911 ;
912
913 -- execute immediate 'alter session disable parallel query';
914
915 commit;
916
917 fnd_stats.gather_table_stats( ownname => g_opi_schema,
918 tabname => 'OPI_DBI_OPM_COGSTST_CURRENT',
919 percent => 10);
920
921 -- execute immediate 'alter session force parallel query parallel '||p_degree ;
922
923 insert /*+ APPEND PARALLEL(F) */
924 into opi_dbi_opm_cogsled_current F
925 ( INVENTORY_ITEM_ID
926 ,ORGANIZATION_ID
927 ,ORDER_LINE_ID
928 ,MARGIN_OU_ID
929 ,COGS_VAL_B
930 ,COGS_DATE
931 ,COGS_CURRENCY_CODE
932 ,COGS_CONVERSION_RATE
933 ,SHIP_OU_ID
934 ,SELL_OU_ID
935 ,TURNS_COGS_FLAG
936 ,SOURCE
937 )
938 select /*+ PARALLEL(COGS) PARALLEL(MIP) */
939 INVENTORY_ITEM_ID
940 ,ORGANIZATION_ID
941 ,ORDER_LINE_ID
942 ,Decode (margin_ou_id,
943 0, Decode(mip.sell_organization_id,
944 NULL, sell_ou_id, -- NULL indicates no mip row set up, therefore no ici
945 ship_ou_id -- else mip row set up, therefore ici
946 ),
947 margin_ou_id
948 ) MARGIN_OU_ID
949 ,COGS_VAL_B
950 ,COGS_DATE
951 ,COGS_CURRENCY_CODE
952 ,COGS_CONVERSION_RATE
953 ,SHIP_OU_ID
954 ,SELL_OU_ID
955 ,TURNS_COGS_FLAG
956 ,decode (SOURCE, 'OPM_CHECK_ICI',
957 Decode(mip.sell_organization_id,
958 NULL, 'OPM_RO_NOICI', -- RO, NOICI
959 'OPM_RO_ICI'), -- RO, ICI
960 SOURCE ) SOURCE
961 from
962 (
963 select /*+ PARALLEL(A) */
964 INVENTORY_ITEM_ID
965 ,ORGANIZATION_ID
966 ,ORDER_LINE_ID
967 ,MARGIN_OU_ID
968 ,sum(COGS_VAL_B) COGS_VAL_B
969 ,max(COGS_DATE) COGS_DATE
970 ,COGS_CURRENCY_CODE
971 ,fii_currency.get_global_rate_primary
972 (cogs_currency_code, max(cogs_date)) COGS_CONVERSION_RATE
973 ,SHIP_OU_ID
974 ,SELL_OU_ID
975 ,TURNS_COGS_FLAG
976 ,SOURCE
977 from
978 ( select /*+ FULL(tst) PARALLEL(TST) PARALLEL(LINES) PARALLEL(WHSE) PARALLEL(OU) PARALLEL(TRAN) */
979 lines.inventory_item_id INVENTORY_ITEM_ID ,
980 whse.mtl_organization_id ORGANIZATION_ID ,
981 tran.oe_order_line_id ORDER_LINE_ID ,
982 Decode(lines.source_type_code, 'EXTERNAL', lines.org_id, --drop ship
983 Decode(ou.ship_ou_id, lines.org_id, lines.org_id, -- Same OU,
984 0 -- if need to check ici, set OU in outer query
985 ) ) MARGIN_OU_ID ,
986 tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
987 trunc(GL_TRANS_DATE) COGS_DATE ,
988 tst.currency_base COGS_CURRENCY_CODE ,
989 ou.ship_ou_id SHIP_OU_ID ,
990 lines.org_id SELL_OU_ID ,
991 Decode(lines.source_type_code, 'EXTERNAL', 'N', --drop ship
992 'Y' ) TURNS_COGS_FLAG ,
993 Decode( lines.line_category_code, 'RETURN', 'OPM_RMA',
994 Decode(lines.source_type_code, 'EXTERNAL', 'OPM_RO_DROP', --drop ship
995 Decode(ou.ship_ou_id, lines.org_id, 'OPM_RO', -- Same OU,
996 'OPM_CHECK_ICI' ) ) ) SOURCE
997 from gl_subr_led tst,
998 oe_order_lines_all lines,
999 ic_whse_mst whse,
1000 (
1001 SELECT /*+ PARALLEL(HOU) PARALLEL(HOI) PARALLEL(GSOB) */
1002 hou.organization_id organization_id,
1003 gsob.currency_code currency_code,
1004 to_number(HOI.org_information3) ship_ou_id
1005 FROM hr_all_organization_units hou,
1006 hr_organization_information hoi,
1007 gl_sets_of_books gsob
1008 WHERE hou.organization_id = hoi.organization_id
1009 AND ( hoi.org_information_context || '') ='Accounting Information'
1010 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
1011 ) OU,
1012 (
1013 select /*+ PARALLEL(TRAN) PARALLEL(RCV) */
1014 tran.doc_type,
1015 rcv.oe_order_line_id oe_order_line_id,
1016 tran.line_id,
1017 tran.orgn_code,
1018 tran.whse_code
1019 from ic_tran_pnd tran,
1020 rcv_transactions rcv
1021 where doc_type = 'PORC'
1022 and completed_ind = 1
1023 and gl_posted_ind = 1
1024 and tran.line_id = rcv.transaction_id
1025 and rcv.oe_order_line_id is NOT NULL
1026 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
1027 union all
1028 select /*+ PARALLEL(TRAN) PARALLEL(RCV) */
1029 tran.doc_type,
1030 rcv.oe_order_line_id oe_order_line_id,
1031 tran.line_id,
1032 tran.orgn_code,
1033 tran.whse_code
1034 from ic_tran_cmp tran,
1035 rcv_transactions rcv
1036 where doc_type = 'PORC'
1037 and gl_posted_ind = 1
1038 and tran.line_id = rcv.transaction_id
1039 and rcv.oe_order_line_id is NOT NULL
1040 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
1041 union all
1042 select /*+ PARALLEL(TRAN) */
1043 tran.doc_type,
1044 tran.line_id oe_order_line_id,
1045 tran.line_id,
1046 tran.orgn_code,
1047 tran.whse_code
1048 from ic_tran_pnd tran
1049 where doc_type = 'OMSO'
1050 and completed_ind = 1
1051 and gl_posted_ind = 1
1052 group by doc_type, line_id, line_id, orgn_code, whse_code
1053 union all
1054 select /*+ PARALLEL(TRAN) */
1055 tran.doc_type,
1056 tran.line_id oe_order_line_id,
1057 tran.line_id,
1058 tran.orgn_code,
1059 tran.whse_code
1060 from ic_tran_cmp tran
1061 where doc_type = 'OMSO'
1062 and gl_posted_ind = 1
1063 group by doc_type, line_id, line_id, orgn_code, whse_code ) tran
1064 where tst.doc_type in ( 'OMSO', 'PORC' )
1065 and tst.acct_ttl_type = 5200
1066 and lines.line_id = tran.oe_order_line_id
1067 and tran.doc_type = tst.doc_type
1068 and tran.line_id = tst.line_id
1069 and whse.whse_code = tran.whse_code
1070 AND whse.mtl_organization_id = ou.organization_id
1071 and tst.subledger_id between p_last_id and p_newest_id
1072 ) A
1073 group by
1074 INVENTORY_ITEM_ID
1075 ,ORGANIZATION_ID
1076 ,ORDER_LINE_ID
1077 ,MARGIN_OU_ID
1078 ,COGS_CURRENCY_CODE
1079 ,SHIP_OU_ID
1080 ,SELL_OU_ID
1081 ,TURNS_COGS_FLAG
1082 ,SOURCE
1083 ) cogs,
1084 mtl_intercompany_parameters mip
1085 where mip.ship_organization_id(+) = cogs.ship_ou_id
1086 AND mip.sell_organization_id(+) = cogs.sell_ou_id
1087 ;
1088
1089 -- execute immediate 'alter session disable parallel query';
1090
1091 commit;
1092 fnd_stats.gather_table_stats( ownname => g_opi_schema,
1093 tabname => 'OPI_DBI_OPM_COGSLED_CURRENT',
1094 percent => 10);
1095
1096
1097 -- execute immediate 'alter session force parallel query parallel '||p_degree ;
1098
1099 insert /*+ APPEND PARALLEL(F) */
1100 INTO opi_dbi_cogs_stg F
1101 (inventory_item_id,
1102 organization_id,
1103 order_line_id,
1104 margin_ou_id,
1105 cogs_val_b, cogs_date,
1106 cogs_currency_code,
1107 cogs_conversion_rate,
1108 ship_ou_id,
1109 sell_ou_id,
1110 turns_cogs_flag,
1111 source,
1112 creation_date,
1113 last_update_date,
1114 created_by,
1115 last_updated_by,
1116 last_update_login)
1117 SELECT /*+ PARALLEL(A) */
1118 INVENTORY_ITEM_ID
1119 ,ORGANIZATION_ID
1120 ,ORDER_LINE_ID
1121 ,MARGIN_OU_ID
1122 ,sum(cogs_val_b) COGS_VAL_B
1123 ,max(cogs_date) COGS_DATE
1124 ,COGS_CURRENCY_CODE
1125 ,COGS_CONVERSION_RATE
1126 ,SHIP_OU_ID
1127 ,SELL_OU_ID
1128 ,TURNS_COGS_FLAG
1129 ,SOURCE
1130 , g_Sysdate, g_Sysdate, g_user_id, g_user_id, g_login_id
1131 FROM
1132 (select /*+ PARALLEL(TSTCURR) */
1133 INVENTORY_ITEM_ID
1134 ,ORGANIZATION_ID
1135 ,ORDER_LINE_ID
1136 ,MARGIN_OU_ID
1137 ,COGS_VAL_B
1138 ,COGS_DATE
1139 ,COGS_CURRENCY_CODE
1140 ,COGS_CONVERSION_RATE
1141 ,SHIP_OU_ID
1142 ,SELL_OU_ID
1143 ,TURNS_COGS_FLAG
1144 ,SOURCE
1145 from opi_dbi_opm_cogstst_current TSTCURR
1146 union all
1147 select /*+ PARALLEL(TSTPRIOR) */
1148 INVENTORY_ITEM_ID
1149 ,ORGANIZATION_ID
1150 ,ORDER_LINE_ID
1151 ,MARGIN_OU_ID
1152 ,-COGS_VAL_B
1153 ,COGS_DATE
1154 ,COGS_CURRENCY_CODE
1155 ,COGS_CONVERSION_RATE
1156 ,SHIP_OU_ID
1157 ,SELL_OU_ID
1158 ,TURNS_COGS_FLAG
1159 ,SOURCE
1160 from opi_dbi_opm_cogstst_prior TSTPRIOR
1161 union all
1162 select /*+ PARALLEL(LED) */
1163 INVENTORY_ITEM_ID
1164 ,ORGANIZATION_ID
1165 ,ORDER_LINE_ID
1166 ,MARGIN_OU_ID
1167 ,COGS_VAL_B
1168 ,COGS_DATE
1169 ,COGS_CURRENCY_CODE
1170 ,COGS_CONVERSION_RATE
1171 ,SHIP_OU_ID
1172 ,SELL_OU_ID
1173 ,TURNS_COGS_FLAG
1174 ,SOURCE
1175 from opi_dbi_opm_cogsled_current LED
1176 ) A
1177 group by
1178 INVENTORY_ITEM_ID
1179 ,ORGANIZATION_ID
1180 ,ORDER_LINE_ID
1181 ,MARGIN_OU_ID
1182 ,COGS_CURRENCY_CODE
1183 ,COGS_CONVERSION_RATE
1184 ,SHIP_OU_ID
1185 ,SELL_OU_ID
1186 ,TURNS_COGS_FLAG
1187 ,SOURCE
1188 , g_Sysdate, g_Sysdate, g_user_id, g_user_id, g_login_id
1189 ;
1190 -- execute immediate 'alter session disable parallel query';
1191 commit;
1192
1193
1194 l_stmt := 1;
1195
1196 execute immediate 'truncate table ' || g_opi_schema
1197 || '.opi_dbi_opm_cogsled_current ';
1198
1199 execute immediate 'truncate table ' || g_opi_schema
1200 || '.opi_dbi_opm_cogstst_prior ';
1201
1202 fnd_stats.gather_table_stats( ownname => g_opi_schema,
1203 tabname => 'OPI_DBI_COGS_STG',
1204 percent => 10);
1205
1206
1207 -- execute immediate 'alter session force parallel query parallel '||p_degree ;
1208
1209 insert /*+ APPEND PARALLEL(F) */
1210 into opi_dbi_opm_cogstst_prior F
1211 ( INVENTORY_ITEM_ID
1212 ,ORGANIZATION_ID
1213 ,ORDER_LINE_ID
1214 ,MARGIN_OU_ID
1215 ,COGS_VAL_B
1216 ,COGS_DATE
1217 ,COGS_CURRENCY_CODE
1218 ,COGS_CONVERSION_RATE
1219 ,SHIP_OU_ID
1220 ,SELL_OU_ID
1221 ,TURNS_COGS_FLAG
1222 ,SOURCE
1223 )
1224 select /*+ PARALLEL(CURR) FULL(curr) */
1225 INVENTORY_ITEM_ID
1226 ,ORGANIZATION_ID
1227 ,ORDER_LINE_ID
1228 ,MARGIN_OU_ID
1229 ,COGS_VAL_B
1230 ,COGS_DATE
1231 ,COGS_CURRENCY_CODE
1232 ,COGS_CONVERSION_RATE
1233 ,SHIP_OU_ID
1234 ,SELL_OU_ID
1235 ,TURNS_COGS_FLAG
1236 ,SOURCE
1237 from opi_dbi_opm_cogstst_current curr;
1238
1239 -- execute immediate 'alter session disable parallel query';
1240
1241 commit;
1242
1243
1244 fnd_stats.gather_table_stats( ownname => g_opi_schema,
1245 tabname => 'OPI_DBI_OPM_COGSTST_PRIOR',
1246 percent => 10);
1247
1248
1249
1250 execute immediate 'truncate table ' || g_opi_schema
1251 || '.opi_dbi_opm_cogstst_current ';
1252
1253
1254 x_status := 1; -- complete successfully
1255 x_msg := NULL;
1256 EXCEPTION WHEN OTHERS THEN
1257 ROLLBACK;
1258
1259 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Initial_opm_subl_org_cogs at statement ' || l_stmt);
1260 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
1261 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Initial_opm_subl_org_cogs at statement ' || l_stmt);
1262 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
1263 x_status := 0; -- error
1264 x_msg := Sqlerrm;
1265
1266 END initial_opm_subl_org_cogs;
1267
1268
1269
1270 procedure refresh_icap_cogs is
1271 l_last_trx_id NUMBER :=0;
1272 l_new_trx_id NUMBER :=0;
1273
1274 l_rows_in_batch NUMBER := 100000;
1275 l_status NUMBER := 1;
1276 l_msg VARCHAR2(4000);
1277
1278 l_batch_from_id NUMBER;
1279 l_batch_to_id NUMBER;
1280
1281 l_empty_count NUMBER;
1282 l_missing_rate_count NUMBER;
1283 l_exception_count NUMBER;
1284
1285 l_opi_schema VARCHAR2(30);
1286 l_industry VARCHAR2(30);
1287 l_opi_status VARCHAR2(30);
1288
1289 l_ship_ou_id NUMBER;
1290
1291 x_row_count NUMBER := 0;
1292
1293 begin
1294 BEGIN
1295 SELECT transaction_id INTO l_last_trx_id
1296 FROM opi_dbi_cogs_log
1297 WHERE extraction_type = 'COGS_ICAP';
1298
1299 EXCEPTION
1300 WHEN no_data_found THEN
1301 SELECT Nvl( MIN(invoice_distribution_id), 0) INTO l_last_trx_id
1302 FROM ap_invoice_distributions_all
1303 WHERE accounting_date >= global_start_date;
1304
1305 BIS_COLLECTION_UTILITIES.PUT_LINE('S, ICAP '|| l_last_trx_id );
1306 --l_last_trx_id := 0;
1307 END;
1308
1309 SELECT ap_invoice_distributions_s.NEXTVAL INTO l_new_trx_id
1310 FROM dual;
1311
1312 l_batch_from_id := l_last_trx_id;
1313
1314 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI ICAP COGS at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
1315 LOOP
1316
1317 BIS_COLLECTION_UTILITIES.PUT_LINE('batch_id ' || l_last_trx_id );
1318
1319 IF (l_batch_from_id + l_rows_in_batch) >= l_new_trx_id THEN
1320 l_batch_to_id := l_new_trx_id;
1321 ELSE
1322 l_batch_to_id := l_batch_from_id + l_rows_in_batch;
1323 END IF;
1324
1325 refresh_opi_icap_cogs( l_last_trx_id,
1326 l_new_trx_id,
1327 l_status,
1328 l_msg );
1329
1330 merge INTO opi_dbi_cogs_log l
1331 using ( SELECT 'COGS_ICAP' extraction_type
1332 FROM dual ) d
1333 ON ( l.extraction_type = d.extraction_type )
1334 WHEN matched THEN UPDATE SET
1335 l.transaction_id = Decode(l_status, 0, l_batch_from_id,
1336 1, l_batch_to_id ),
1337 l.error_message = l_msg,
1338 l.last_update_date = Sysdate,
1339 l.last_updated_by = g_user_id,
1340 l.last_update_login = g_login_id
1341 WHEN NOT matched THEN
1342 INSERT (l.organization_id, l.transaction_id, l.extraction_type,
1343 l.error_message, l.creation_date, l.last_update_date, l.created_by,
1344 l.last_updated_by, l.last_update_login )
1345 VALUES (null,
1346 Decode(l_status, 0, l_batch_from_id,1, l_batch_to_id ) , d.extraction_type,
1347 l_msg, Sysdate, Sysdate, g_user_id,
1348 g_user_id, g_login_id );
1349
1350 COMMIT; -- commit per batch
1351
1352 l_batch_from_id := l_batch_to_id;
1353 EXIT WHEN l_batch_to_id >= l_new_trx_id OR l_status = 0;
1354
1355 END LOOP;
1356
1357 BIS_COLLECTION_UTILITIES.PUT_LINE(' after ICAP time is ' || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1358 BIS_COLLECTION_UTILITIES.PUT_LINE('after ICAP time is ' || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1359
1360 end refresh_icap_cogs;
1361
1362
1363 procedure check_rates_and_truncate (errbuf in out NOCOPY varchar2,
1364 retcode in out NOCOPY VARCHAR2,
1365 x_row_count in out NOCOPY NUMBER) is
1366
1367 l_last_trx_id NUMBER :=0;
1368 l_new_trx_id NUMBER :=0;
1369
1370 l_rows_in_batch NUMBER := 100000;
1371 l_status NUMBER := 1;
1372 l_msg VARCHAR2(4000);
1373
1374 l_batch_from_id NUMBER;
1375 l_batch_to_id NUMBER;
1376
1377 l_empty_count NUMBER;
1378 l_missing_rate_count NUMBER;
1379 l_exception_count NUMBER;
1380
1381 l_opi_schema VARCHAR2(30);
1382 l_industry VARCHAR2(30);
1383 l_opi_status VARCHAR2(30);
1384
1385
1386 begin
1387 SELECT COUNT(*) INTO l_exception_count
1388 FROM opi_dbi_cogs_log
1389 WHERE error_message IS NOT NULL;
1390
1391 l_missing_rate_count := report_missing_rate;
1392
1393 BIS_COLLECTION_UTILITIES.PUT_LINE('completed report_missing_rate ');
1394
1395 IF l_exception_count = 0 AND l_missing_rate_count = 0 THEN
1396 BIS_COLLECTION_UTILITIES.PUT_LINE('merging into fact table');
1397 merge INTO opi_dbi_margin_f m
1398 using (SELECT *
1399 FROM opi_dbi_cogs_stg ) c
1400 ON ( m.order_line_id = c.order_line_id
1401 AND m.margin_ou_id = c.margin_ou_id )
1402 WHEN matched THEN UPDATE SET
1403 inventory_item_id = c.inventory_item_id,
1404 organization_id = c.organization_id,
1405 margin_date = Greatest( Nvl(margin_date, c.cogs_date), c.cogs_date),
1406 cogs_val_b = Nvl(cogs_val_b,0) + Nvl(c.cogs_val_b,0),
1407 cogs_conversion_rate = Decode(Sign(c.cogs_date - Nvl(cogs_date, c.cogs_date)),
1408 -1, cogs_conversion_rate, c.cogs_conversion_rate),
1409 cogs_date= Greatest( Nvl(cogs_date,c.cogs_date), c.cogs_date),
1410 cogs_source = c.source,
1411 cogs_ship_ou_id = c.ship_ou_id,
1412 cogs_sell_ou_id = c.sell_ou_id,
1413 turns_cogs_flag = c.turns_cogs_flag,
1414 last_update_date = Sysdate,
1415 last_updated_by = g_user_id,
1416 last_update_login = g_login_id
1417 WHEN NOT matched THEN
1418 INSERT (m.inventory_item_id, m.organization_id, m.order_line_id,
1419 m.margin_date, m.margin_ou_id,
1420 m.cogs_val_b, m.cogs_conversion_rate, m.cogs_date,
1421 m.cogs_source, m.cogs_ship_ou_id, m.cogs_sell_ou_id,
1422 m.turns_cogs_flag,m.creation_date, m.last_update_date,
1423 m.created_by, m.last_updated_by, m.last_update_login)
1424 VALUES ( c.inventory_item_id, c.organization_id, c.order_line_id,
1425 c.cogs_date, c.margin_ou_id,
1426 c.cogs_val_b, c.cogs_conversion_rate, c.cogs_date,
1427 c.source, c.ship_ou_id, c.sell_ou_id,
1428 c.turns_cogs_flag, Sysdate, Sysdate,
1429 g_user_id, g_user_id, g_login_id);
1430
1431 BIS_COLLECTION_UTILITIES.PUT_LINE('merge completed');
1432
1433 x_row_count := SQL%rowcount;
1434
1435 -- truncate staging table
1436 execute immediate 'truncate table ' || g_opi_schema || '.opi_dbi_cogs_stg ';
1437
1438
1439 COMMIT;
1440 ELSE -- there is exception or missing rate
1441 retcode := 1;
1442 errbuf := 'Please check log file for details. ';
1443 BIS_COLLECTION_UTILITIES.PUT_LINE('There are either missing conversion rates or exeception happened.');
1444 BIS_COLLECTION_UTILITIES.PUT_LINE('Please check the log file for details ');
1445 END IF;
1446
1447 BIS_COLLECTION_UTILITIES.PUT_LINE('completed OPI COGS Merge time is ' || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1448
1449 end check_rates_and_truncate;
1450
1451
1452 FUNCTION refresh_opm_cogs (errbuf in out NOCOPY varchar2,
1453 retcode in out NOCOPY VARCHAR2
1454 ) RETURN NUMBER IS
1455
1456 l_last_trx_id NUMBER :=0;
1457 l_new_trx_id NUMBER :=0;
1458
1459 l_rows_in_batch NUMBER := 100000;
1460 l_status NUMBER := 1;
1461 l_msg VARCHAR2(4000);
1462
1463 l_batch_from_id NUMBER;
1464 l_batch_to_id NUMBER;
1465
1466 l_empty_count NUMBER;
1467 l_missing_rate_count NUMBER;
1468 l_exception_count NUMBER;
1469
1470 l_opi_schema VARCHAR2(30);
1471 l_industry VARCHAR2(30);
1472 l_opi_status VARCHAR2(30);
1473
1474
1475 x_row_count NUMBER := 0;
1476
1477 BEGIN
1478
1479 retcode := 0;
1480
1481 BIS_COLLECTION_UTILITIES.PUT_LINE('OPM COGS refresh started at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
1482
1483 -- 0. check if staging table is empty or not
1484 SELECT COUNT(*) INTO l_empty_count
1485 FROM opi_dbi_cogs_stg
1486 Where rownum = 1;
1487
1488
1489
1490 IF l_empty_count > 0 THEN -- not empty, do a master update to remove missing rate
1491 UPDATE opi_dbi_cogs_stg
1492 SET cogs_conversion_rate =
1493 fii_currency.get_global_rate_primary(cogs_currency_code,cogs_date),
1494 last_update_date = Sysdate,
1495 last_updated_by = g_user_id,
1496 last_update_login = g_login_id
1497 WHERE NVL(cogs_conversion_rate,-99) < 0 ;
1498 END IF;
1499
1500
1501 -- 1. get subledger cogs
1502
1503 BEGIN
1504 SELECT transaction_id INTO l_last_trx_id
1505 FROM opi_dbi_cogs_log
1506 WHERE extraction_type = 'OPM_COGS_SUBLEDGER';
1507
1508
1509 SELECT Nvl(MAX(subledger_id),l_last_trx_id)
1510 INTO l_new_trx_id
1511 from gl_subr_led tst
1512 where tst.doc_type in ( 'OMSO', 'PORC')
1513 and tst.acct_ttl_type = 5200
1514 AND tst.gl_trans_date >= global_start_date
1515 AND tst.subledger_id >= l_last_trx_id;
1516
1517
1518 EXCEPTION
1519 WHEN no_data_found THEN
1520
1521
1522
1523 SELECT Nvl(MIN(subledger_id),0) - 1,
1524 Nvl(MAX(subledger_id),0)
1525 INTO l_last_trx_id,
1526 l_new_trx_id
1527 from gl_subr_led tst
1528 where tst.doc_type in ( 'OMSO', 'PORC')
1529 and tst.acct_ttl_type = 5200
1530 AND tst.gl_trans_date >= global_start_date;
1531
1532 BIS_COLLECTION_UTILITIES.PUT_LINE('S, ' ||l_last_trx_id );
1533 BIS_COLLECTION_UTILITIES.PUT_LINE ('Incremental Refresh chosen, but Initial Load may be faster' );
1534 END;
1535
1536
1537
1538 BIS_COLLECTION_UTILITIES.PUT_LINE
1539 ('Collecting OPM Subledger COGS for transaction ID range: ' || to_char(l_last_trx_id + 1) ||' - ' || l_new_trx_id);
1540
1541
1542
1543 BIS_COLLECTION_UTILITIES.PUT_LINE( ' Start at ' || To_char(Sysdate, 'hh24:mi:ss'));
1544
1545 l_batch_from_id := l_last_trx_id;
1546 l_batch_to_id := l_new_trx_id;
1547
1548 BIS_COLLECTION_UTILITIES.PUT_LINE('batch_id ' || l_batch_from_id);
1549
1550 refresh_opm_subl_org_cogs(
1551 l_batch_from_id + 1,
1552 l_batch_to_id,
1553 l_status,
1554 l_msg );
1555
1556 merge INTO opi_dbi_cogs_log l
1557 using ( SELECT NULL organization_id,
1558 'OPM_COGS_SUBLEDGER' extraction_type
1559 FROM dual ) d
1560 ON ( l.extraction_type = d.extraction_type )
1561 WHEN matched THEN UPDATE SET
1562 l.organization_id = NULL,
1563 l.transaction_id = Decode(l_status, 0, l_batch_from_id,
1564 1, l_batch_to_id ),
1565 l.error_message = l_msg,
1566 l.last_update_date = Sysdate,
1567 l.last_updated_by = g_user_id,
1568 l.last_update_login = g_login_id
1569 WHEN NOT matched THEN
1570 INSERT (l.organization_id, l.transaction_id, l.extraction_type,
1571 l.error_message, l.creation_date, l.last_update_date, l.created_by,
1572 l.last_updated_by, l.last_update_login )
1573 VALUES (d.organization_id,
1574 Decode(l_status, 0, l_batch_from_id,1, l_batch_to_id ) , d.extraction_type,
1575 l_msg, Sysdate, Sysdate, g_user_id,
1576 g_user_id, g_login_id );
1577
1578 COMMIT; -- commit per org
1579
1580 BIS_COLLECTION_UTILITIES.PUT_LINE(' Subledger COGS completed at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
1581
1582 -- 2. get Intercompany AP as COGS
1583 -- refresh_icap_cogs; --removed and called from wrapper
1584
1585
1586 -- 3. check exception or missing rates
1587 check_rates_and_truncate(errbuf, retcode, x_row_count);
1588 RETURN x_row_count;
1589 EXCEPTION WHEN OTHERS THEN
1590 ROLLBACK;
1591
1592 BIS_COLLECTION_UTILITIES.PUT_LINE('Error in refresh_opm_cogs ' || Sqlerrm );
1593 errbuf := Sqlerrm;
1594 retcode := 1;
1595 RETURN x_row_count;
1596 END refresh_opm_cogs;
1597
1598
1599 FUNCTION complete_refresh_opm_cogs (errbuf in out NOCOPY varchar2,
1600 retcode in out NOCOPY VARCHAR2,
1601 p_degree IN NUMBER ) RETURN NUMBER IS
1602
1603 l_last_trx_id NUMBER :=0;
1604 l_new_trx_id NUMBER :=0;
1605
1606 l_rows_in_batch NUMBER := 100000;
1607 l_status NUMBER := 1;
1608 l_msg VARCHAR2(4000);
1609
1610 l_batch_from_id NUMBER;
1611 l_batch_to_id NUMBER;
1612
1613 l_empty_count NUMBER;
1614 l_missing_rate_count NUMBER;
1615 l_exception_count NUMBER;
1616
1617 l_opi_schema VARCHAR2(30);
1618 l_industry VARCHAR2(30);
1619 l_opi_status VARCHAR2(30);
1620
1621
1622 x_row_count NUMBER := 0;
1623
1624 BEGIN
1625
1626 retcode := 0;
1627
1628 BIS_COLLECTION_UTILITIES.PUT_LINE('OPM COGS refresh started at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
1629
1630
1631 -- 1. get subledger cogs
1632
1633 SELECT Nvl(MIN(subledger_id),0),
1634 Nvl(MAX(subledger_id),0)
1635 INTO l_last_trx_id,
1636 l_new_trx_id
1637 from gl_subr_led tst
1638 where tst.doc_type in ( 'OMSO', 'PORC' )
1639 and tst.acct_ttl_type = 5200
1640 AND tst.gl_trans_date >= global_start_date;
1641
1642 BIS_COLLECTION_UTILITIES.PUT_LINE('Initial Refresh' );
1643
1644
1645 BIS_COLLECTION_UTILITIES.PUT_LINE
1646 ('Collecting OPM Subledger COGS for transaction ID range: ' || l_last_trx_id ||' - ' || l_new_trx_id);
1647
1648
1649 BIS_COLLECTION_UTILITIES.PUT_LINE( ' Start at ' || To_char(Sysdate, 'hh24:mi:ss'));
1650
1651 l_batch_from_id := l_last_trx_id;
1652 l_batch_to_id := l_new_trx_id;
1653
1654 BIS_COLLECTION_UTILITIES.PUT_LINE('batch_id ' || l_batch_from_id);
1655
1656 initial_opm_subl_org_cogs(
1657 l_batch_from_id,
1658 l_batch_to_id,
1659 l_status,
1660 l_msg ,
1661 p_degree);
1662
1663 merge INTO opi_dbi_cogs_log l
1664 using ( SELECT NULL organization_id,
1665 'OPM_COGS_SUBLEDGER' extraction_type
1666 FROM dual ) d
1667 ON ( l.extraction_type = d.extraction_type )
1668 WHEN matched THEN UPDATE SET
1669 l.organization_id = NULL,
1670 l.transaction_id = Decode(l_status, 0, l_batch_from_id,
1671 1, l_batch_to_id ),
1672 l.error_message = l_msg,
1673 l.last_update_date = Sysdate,
1674 l.last_updated_by = g_user_id,
1675 l.last_update_login = g_login_id
1676 WHEN NOT matched THEN
1677 INSERT (l.organization_id, l.transaction_id, l.extraction_type,
1678 l.error_message, l.creation_date, l.last_update_date, l.created_by,
1679 l.last_updated_by, l.last_update_login )
1680 VALUES (d.organization_id,
1681 Decode(l_status, 0, 0 /* if error then write 0 */, 1, l_batch_to_id ) , d.extraction_type,
1682 l_msg, Sysdate, Sysdate, g_user_id,
1683 g_user_id, g_login_id );
1684
1685 COMMIT;
1686
1687 BIS_COLLECTION_UTILITIES.PUT_LINE(' Subledger COGS completed at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
1688
1689 RETURN x_row_count;
1690 EXCEPTION WHEN OTHERS THEN
1691 ROLLBACK;
1692
1693 BIS_COLLECTION_UTILITIES.PUT_LINE('Error in refresh_opm_cogs ' || Sqlerrm );
1694 errbuf := Sqlerrm;
1695 retcode := 1;
1696 RETURN 0; --x_row_count;
1697 END complete_refresh_opm_cogs;
1698
1699
1700 PROCEDURE complete_refresh_OPM_margin(Errbuf in out NOCOPY VARCHAR2,
1701 Retcode in out NOCOPY VARCHAR2,
1702 p_degree IN NUMBER ) IS
1703
1704 l_opi_schema VARCHAR2(30);
1705 l_status VARCHAR2(30);
1706 l_industry VARCHAR2(30);
1707 l_revenue_count NUMBER := 0;
1708 l_cogs_count NUMBER := 0;
1709 BEGIN
1710 /* IF BIS_COLLECTION_UTILITIES.SETUP( 'OPI_DBI_MARGIN_F' ) = false then
1711 RAISE_APPLICATION_ERROR(-20000, errbuf);
1712 END IF;
1713 */
1714 -- setup globals
1715 check_setup_globals(errbuf, retcode);
1716
1717 IF retcode <> 0 THEN
1718 RETURN ;
1719 END IF;
1720
1721
1722 /* Deletes removed here and replaced in wrapper */
1723 /* delete from opi_dbi_cogs_log */
1724 /* where extraction_type like 'OPM%'; */
1725
1726 /* delete from opi_dbi_cogs_stg */
1727 /* where source like 'OPM%'; */
1728
1729 /* delete from opi_dbi_margin_f */
1730 /* where cogs_source like 'OPM%'; */
1731
1732 execute immediate 'truncate table ' || g_opi_schema
1733 || '.opi_dbi_opm_cogstst_current ';
1734 execute immediate 'truncate table ' || g_opi_schema
1735 || '.opi_dbi_opm_cogstst_prior ';
1736 execute immediate 'truncate table ' || g_opi_schema
1737 || '.opi_dbi_opm_cogsled_current ';
1738
1739
1740 commit;
1741
1742 l_cogs_count := complete_refresh_opm_cogs(errbuf, retcode, p_degree);
1743
1744 IF retcode <> 0 THEN
1745 RETURN ;
1746 ELSE
1747 NULL;
1748
1749 /* Removed call to refresh revenue, this will be performed in the wrapper package */
1750 /* l_revenue_count := opi_dbi_cogs_margin_pkg.complete_refresh_revenue; */
1751
1752 END IF;
1753
1754 /* bis_collection_utilities.WRAPUP( p_status => TRUE,
1755 p_count => 0,
1756 p_message => 'successfully refreshed OPM performance Margin.'
1757 );
1758 */
1759 EXCEPTION WHEN OTHERS THEN
1760
1761 Errbuf:= Sqlerrm;
1762 Retcode:= SQLCODE;
1763
1764 ROLLBACK;
1765 /* bis_collection_utilities.wrapup(p_status => FALSE,
1766 p_count => 0,
1767 p_message => 'failed in refreshing Margin.'
1768 );
1769 */
1770 RAISE_APPLICATION_ERROR(-20000,errbuf);
1771
1772 END complete_refresh_OPM_margin;
1773
1774
1775
1776 PROCEDURE refresh_OPM_margin(Errbuf in out NOCOPY VARCHAR2,
1777 Retcode in out NOCOPY VARCHAR2 ) IS
1778 l_revenue_count NUMBER := 0;
1779 l_cogs_count NUMBER := 0;
1780 BEGIN
1781
1782 /* IF BIS_COLLECTION_UTILITIES.SETUP( 'OPI_DBI_MARGIN_F' ) = false then
1783 RAISE_APPLICATION_ERROR(-20000, errbuf);
1784 END IF;
1785 */
1786 check_setup_globals(errbuf, retcode);
1787
1788 IF retcode <> 0 THEN
1789 RETURN ;
1790 ELSE
1791 l_cogs_count := refresh_opm_cogs(errbuf, retcode);
1792
1793 IF retcode <> 0 THEN
1794 RETURN ;
1795 ELSE
1796 NULL;
1797 /* Removed call to refresh revenue, this will be performed in the wrapper package */
1798 /* l_revenue_count := opi_dbi_cogs_margin_pkg.refresh_revenue; */
1799 END IF;
1800 END IF;
1801
1802 /* bis_collection_utilities.WRAPUP( p_status => TRUE,
1803 p_count => (l_revenue_count + l_cogs_count)/2,
1804 p_message => 'successfully refreshed OPM performance Margin.'
1805 );
1806 */
1807
1808 EXCEPTION WHEN OTHERS THEN
1809
1810 Errbuf:= Sqlerrm;
1811 Retcode:= SQLCODE;
1812
1813 ROLLBACK;
1814 /* bis_collection_utilities.wrapup(p_status => FALSE,
1815 p_count => (l_revenue_count + l_cogs_count)/2,
1816 p_message => 'failed in refreshing OPM Margin.'
1817 );
1818 */
1819 RAISE_APPLICATION_ERROR(-20000,errbuf);
1820
1821 END refresh_OPM_margin;
1822
1823
1824 END opi_dbi_opm_cogs_pkg;