[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_COGS_PKG
Source
1 PACKAGE BODY opi_dbi_cogs_pkg AS
2 /* $Header: OPIDECOGSB.pls 120.15 2007/03/15 07:36:24 kvelucha ship $ */
3
4
5
6 /*=========================================
7 Package Level Constants
8 ==========================================*/
9
10 -- ETLs stop reason codes
11 STOP_UNCOSTED CONSTANT VARCHAR2(30) := 'STOP_UNCOSTED';
12 STOP_ALL_COSTED CONSTANT VARCHAR2(30) := 'STOP_ALL_COSTED';
13
14
15 -- Marker for secondary conv. rate if the primary and secondary curr codes
16 -- and rate types are identical. Can't be -1, -2, -3 since the FII APIs
17 -- return those values.
18 C_PRI_SEC_CURR_SAME_MARKER CONSTANT NUMBER := -9999;
19
20
21 --GL API returns -3 if EURO rate missing on 01-JAN-1999
22 C_EURO_MISSING_AT_START CONSTANT NUMBER := -3;
23
24
25 -- return codes
26 g_ERROR CONSTANT NUMBER := -1;
27 g_WARNING CONSTANT NUMBER := 1;
28 g_ok CONSTANT NUMBER := 0;
29
30
31 -- Source constants
32 OPI_SOURCE CONSTANT NUMBER := 1;
33 OPM_SOURCE CONSTANT NUMBER := 2;
34 PRE_R12_OPM_SOURCE CONSTANT NUMBER := 3;
35
36
37 g_euro_start_date CONSTANT DATE := to_date('01/01/1999','DD/MM/YYYY');
38
39
40 /*=========================================
41 Package Level Variables
42 ==========================================*/
43
44 -- Stage failure.
45 stage_failure EXCEPTION;
46 PRAGMA EXCEPTION_INIT (stage_failure, -20004);
47
48 -- Standard WHO columns
49 g_user_id NUMBER;
50 g_login_id NUMBER;
51 g_program_id NUMBER;
52 g_program_login_id NUMBER;
53 g_program_application_id NUMBER;
54 g_request_id NUMBER;
55
56
57 -- Conversion rate related variables
58 g_global_currency_code VARCHAR2(10);
59 g_secondary_currency_code VARCHAR2(10);
60 g_global_rate_type VARCHAR2(15);
61 g_secondary_rate_type VARCHAR2(15);
62
63
64 -- DBI Global start date
65 g_global_start_date DATE;
66
67
68 /*===============================================================
69 This procedure gather statistics of a table.
70
71 Parameters:
72 - p_table_name: table name
73 ================================================================*/
74
75 PROCEDURE gather_stats(p_table_name VARCHAR2) IS
76
77 l_table_owner user_synonyms.table_owner%type;
78
79 cursor get_table_owner is
80 SELECT table_owner
81 FROM user_synonyms
82 WHERE synonym_name = p_table_name;
83
84 data_no_found exception;
85 BEGIN
86
87 bis_collection_utilities.put_line('Enter gather_stats() '||
88 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
89
90 -- Find owner of the table passed to procedure
91 open get_table_owner;
92 fetch get_table_owner into l_table_owner;
93
94 IF get_table_owner%notfound THEN
95 --{
96 raise data_no_found;
97 --}
98 END IF;
99 close get_table_owner;
100
101 -- Gather table statistics to be used by CBO
102 -- for query optimization.
103
104 fnd_stats.gather_table_stats(l_table_owner, p_table_name,
105 percent=>10, degree=>4, cascade=>TRUE);
106
107 bis_collection_utilities.put_line('Exit gather_stats '||
108 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
109
110
111 END gather_stats;
112
113
114 /*===============================================================
115 This procedure sets up global parameters, such as the global
116 start date, globla/secondary currencies, WHO column variables.
117
118 Parameters:
119 - errbuf: error buffer
120 - retcode: return code
121 =================================================================*/
122 PROCEDURE check_setup_globals( errbuf IN OUT NOCOPY VARCHAR2 ,
123 retcode IN OUT NOCOPY VARCHAR2) IS
124
125 l_list dbms_sql.varchar2_table;
126 l_from_date date;
127 l_to_date date;
128 l_missing_day_flag boolean;
129 l_min_miss_date date;
130 l_max_miss_date date;
131
132 BEGIN
133
134 bis_collection_utilities.put_line('Enter check_setup_globals() '||
135 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
136
137 -- Initialization block
138 l_missing_day_flag := FALSE;
139 retcode := g_ok;
140
141
142 -- package level variables
143 g_user_id := nvl(fnd_global.user_id, -1);
144 g_login_id := nvl(fnd_global.login_id, -1);
145 g_program_id := nvl (fnd_global.conc_program_id, -1);
146 g_program_login_id := nvl (fnd_global.conc_login_id, -1);
147 g_program_application_id := nvl (fnd_global.prog_appl_id, -1);
148 g_request_id := nvl (fnd_global.conc_request_id, -1);
149
150
151 -- check for mandatory global setups
152 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
153 l_list(2) := 'BIS_GLOBAL_START_DATE';
154 l_list(3) := 'BIS_PRIMARY_RATE_TYPE';
155
156 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
157 --{
158
159 -- Since these are file scope variables that are cached at a session
160 -- level, make sure to reinitialize them explicitly each time.
161
162 -- GSD - already checked if GSD is set up
163 g_global_start_date := bis_common_parameters.get_global_start_date;
164
165 -- Global currency codes - already checked if primary is set up
166 g_global_currency_code := bis_common_parameters.get_currency_code;
167 g_secondary_currency_code :=
168 bis_common_parameters.get_secondary_currency_code;
169
170 -- Global rate types -- already checked if primary is set up
171 g_global_rate_type := bis_common_parameters.get_rate_type;
172 g_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
173
174 -- check that either both the secondary rate type and secondary
175 -- rate are null, or that neither are null.
176
177 IF ((g_secondary_currency_code IS NULL and
178 g_secondary_rate_type IS NOT NULL) OR
179 (g_secondary_currency_code IS NOT NULL and
180 g_secondary_rate_type IS NULL) ) THEN
181 --{
182 retcode := g_error;
183 errbuf := 'Please check log file for details';
184 bis_collection_utilities.put_line ('The global secondary currency code setup is incorrect. ' ||
185 'The secondary currency code cannot be null when the secondary ' ||
186 'rate type is defined and vice versa.');
187 --}
188
189 END IF;
190
191 -- Sysdate
192 SELECT sysdate INTO l_to_date FROM dual;
193
194 -- check_missing_date
195 fii_time_api.check_missing_date (g_global_start_date,
196 l_to_date,
197 l_missing_day_flag,
198 l_min_miss_date,
199 l_max_miss_date);
200
201 IF l_missing_day_flag THEN
202 --{
203 retcode := g_error;
204 errbuf := 'Please check log file for details. ';
205 bis_collection_utilities.put_line('There are missing date in time dimension.');
206 bis_collection_utilities.put_line( 'The range is from '
207 || l_min_miss_date
208 ||' to ' || l_max_miss_date );
209 --}
210 END IF;
211 --}
212 ELSE
213 --{
214 retcode := g_error;
215 errbuf := 'Please check log file for details. ';
216 bis_collection_utilities.put_line('Global Parameters are not setup.');
217
218 bis_collection_utilities.put_line('Please check that the profile options: BIS_PRIMARY_CURRENCY_CODE, BIS_GLOBAL_START_DATE, BIS_PRIMARY_RATE_TYPE are setup.');
219 --}
220 END IF;
221
222 bis_collection_utilities.put_line('Exit check_setup_globals() '||
223 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 --{
228 retcode := SQLCODE;
229 errbuf := 'Error in opi_dbi_cogs_pkg.check_setup_globals ' || substr(SQLERRM, 1,200);
230
231 bis_collection_utilities.put_line('Error Number: ' || retcode);
232 bis_collection_utilities.put_line('Error Message: ' || errbuf);
233 --}
234 END check_setup_globals;
235
236
237 /*===============================================================
238 This procedure extracts discrete data into the staging table
239 for initial load.
240
241 Parameters:
242 - errbuf: error buffer
243 - retcode : return code
244 ================================================================*/
245
246 PROCEDURE init_opi_cogs ( errbuf IN OUT NOCOPY VARCHAR2,
247 retcode IN OUT NOCOPY VARCHAR2 ) IS
248
249 BEGIN
250
251 bis_collection_utilities.put_line('Enter init_opi_cogs() '||
252 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
253
254
255 retcode := 0;
256
257 -- big insert for OPI COGS
258
259 INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_fstg m (
260 m.inventory_item_id,
261 m.organization_id,
262 m.order_line_id,
263 m.top_model_line_id,
264 m.top_model_item_id,
265 m.top_model_item_uom,
266 m.top_model_org_id,
267 m.customer_id,
268 m.cogs_val_b_draft,
269 m.cogs_val_b,
270 m.cogs_date,
271 m.source,
272 m.turns_cogs_flag,
273 m.internal_flag )
274 -- 2 Regular sales order -- 8 Internal Sales Order
275 SELECT /*+ use_hash(mmt)
276 parallel(log) parallel(mmt) parallel(mta)
277 parallel(l) parallel(pl) parallel(h)
278 parallel(cust_acct) parallel(item)*/
279 mmt.inventory_item_id,
280 mmt.organization_id,
281 lines.line_id,
282 p_lines.line_id top_model_line_id,
283 p_lines.inventory_item_id top_model_item_id,
284 item.primary_uom_code top_model_item_uom,
285 p_lines.ship_from_org_id top_model_org_id,
286 nvl(cust_acct.party_id, -1),
287 0 cogs_val_b_draft,
288 mta.base_transaction_value,
289 mmt.transaction_date,
290 OPI_SOURCE,
291 decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
292 decode(p_lines.order_source_id,10,1,0)
293 FROM opi_dbi_conc_prog_run_log log,
294 mtl_material_transactions mmt,
295 mtl_transaction_accounts mta,
296 oe_order_lines_all lines, -- child line
297 oe_order_lines_all p_lines, -- parent line
298 oe_order_headers_all header,
299 hz_cust_accounts cust_acct,
300 mtl_system_items_b item
301 WHERE mmt.transaction_id >= log.from_bound_id
302 AND mmt.transaction_id < log.to_bound_id
303 AND mmt.organization_id = log.bound_level_entity_id
304 AND log.load_type = 'INIT'
305 AND log.etl_type = 'COGS'
306 AND mmt.transaction_source_type_id IN (2,8)
307 AND mmt.transaction_type_id in (33, 34, 62)
308 AND mmt.transaction_action_id IN (1, 21)
309 AND mmt.transaction_id = mta.transaction_id
310 AND mta.accounting_line_type <> 1
311 AND lines.line_id = mmt.trx_source_line_id
312 AND lines.order_source_id <> 27 -- retroactive billing
313 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
314 AND lines.header_id = header.header_id
315 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
316 AND item.inventory_item_id = p_lines.inventory_item_id
317 AND item.organization_id = p_lines.ship_from_org_id
318 UNION ALL
319 -- 12 RMA
320 SELECT /*+ index(mmt,MTL_MATERIAL_TRANSACTIONS_N8)
321 use_nl(mmt,item,mta,h,cust_acct)
322 parallel(log) parallel(mmt) parallel(mta) parallel(l) parallel(pl)
323 parallel(cl) parallel(h) parallel(cust_acct) parallel(item)*/
324 mmt.inventory_item_id,
325 mmt.organization_id,
326 lines.line_id,
327 p_lines.line_id top_model_line_id,
328 p_lines.inventory_item_id top_model_item_id,
329 item.primary_uom_code top_model_item_uom,
330 p_lines.ship_from_org_id top_model_org_id,
331 nvl(cust_acct.party_id, -1),
332 0 cogs_val_b_draft,
333 mta.base_transaction_value,
334 mmt.transaction_date,
335 OPI_SOURCE,
336 1,
337 0
338 FROM opi_dbi_conc_prog_run_log log,
339 mtl_material_transactions mmt,
340 mtl_transaction_accounts mta,
341 oe_order_lines_all lines, -- child line
342 oe_order_lines_all l_lines, -- linking line
343 oe_order_lines_all p_lines, -- parent line
344 oe_order_headers_all header,
345 hz_cust_accounts cust_acct,
346 mtl_system_items_b item
347 WHERE mmt.transaction_id >= log.from_bound_id
348 AND mmt.transaction_id < log.to_bound_id
349 AND mmt.organization_id = log.bound_level_entity_id
350 AND log.load_type = 'INIT'
351 AND log.etl_type = 'COGS'
352 AND mmt.transaction_source_type_id = 12
353 AND mmt.transaction_id = mta.transaction_id
354 AND mta.accounting_line_type <> 1
355 AND lines.line_id = mmt.trx_source_line_id
356 AND lines.line_category_code = 'RETURN'
357 AND lines.order_source_id <> 27 -- retroactive billing
358 AND l_lines.line_id = nvl(lines.link_to_line_id, lines.line_id)
359 AND p_lines.line_id = nvl(l_lines.top_model_line_id, l_lines.line_id)
360 AND lines.header_id = header.header_id
361 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
362 AND item.inventory_item_id = p_lines.inventory_item_id
363 AND item.organization_id = p_lines.ship_from_org_id
364 UNION ALL
365 -- Drop ship
366 SELECT /*+ leading(log) use_nl(mmt) index(mmt,MTL_MATERIAL_TRANSACTIONS_N9)
367 parallel(log) parallel(mmt) parallel(mta) parallel(l) parallel(pl)
368 parallel(h) parallel(cust_acct) parallel(item)*/
369 mmt.inventory_item_id,
370 mmt.organization_id,
371 lines.line_id,
372 p_lines.line_id top_model_line_id,
373 p_lines.inventory_item_id top_model_item_id,
374 item.primary_uom_code top_model_item_uom,
375 p_lines.ship_from_org_id top_model_org_id,
376 nvl(cust_acct.party_id, -1),
377 0 cogs_val_b_draft,
378 mta.base_transaction_value,
379 mmt.transaction_date,
380 OPI_SOURCE,
381 decode(p_mmt.transaction_type_id, 33, 1, 2),
382 0
383 FROM opi_dbi_conc_prog_run_log log,
384 mtl_material_transactions mmt,
385 mtl_material_transactions p_mmt,
386 mtl_transaction_accounts mta,
387 oe_order_lines_all lines,
388 oe_order_lines_all p_lines, -- parent line
389 oe_order_headers_all header,
390 hz_cust_accounts cust_acct,
391 mtl_system_items_b item
392 WHERE mmt.transaction_id >= log.from_bound_id
393 AND mmt.transaction_id < log.to_bound_id
394 AND mmt.organization_id = log.bound_level_entity_id
395 AND log.load_type = 'INIT'
396 AND log.etl_type = 'COGS'
397 AND mmt.transaction_type_id in (11,30)
398 AND mmt.transaction_action_id in (7,9)
399 AND mmt.organization_id = lines.ship_from_org_id
400 AND p_mmt.transaction_id = mmt.parent_transaction_id
401 AND ( -- internal drop
402 (p_mmt.transaction_type_id = 33
403 and p_mmt.transaction_action_id = 1 )
404 OR -- external drop
405 (p_mmt.transaction_type_id = 19
406 and p_mmt.transaction_action_id = 26 ))
407 AND mmt.transaction_id = mta.transaction_id
408 AND mta.accounting_line_type <> 1
409 AND lines.line_id = mmt.trx_source_line_id
410 AND lines.order_source_id <> 27 -- retroactive billing
411 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
412 AND lines.header_id = header.header_id
413 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
414 AND item.inventory_item_id = p_lines.inventory_item_id
415 AND item.organization_id = p_lines.ship_from_org_id;
416
417 COMMIT;
418
419 bis_collection_utilities.put_line('Exit init_opi_cogs() '||
420 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
421
422 EXCEPTION WHEN OTHERS THEN
423 --{
424 errbuf:= Sqlerrm;
425 retcode:= SQLCODE;
426
427 ROLLBACK;
428
429 bis_collection_utilities.put_line('Exception in init_opi_cogs ' || errbuf );
430 --}
431 END init_opi_cogs;
432
433
434
435 /*===============================================================
436 This procedure extracts discrete data into the staging table
437 for incremental load.
438
439 Parameters:
440 - errbuf: error buffer
441 - retcode : return code
442 ================================================================*/
443
444 PROCEDURE incremental_opi_cogs ( errbuf IN OUT NOCOPY VARCHAR2,
445 retcode IN OUT NOCOPY VARCHAR2 ) IS
446
447 BEGIN
448
449 bis_collection_utilities.put_line('Enter incremental_opi_cogs() '||
450 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
451
452 retcode := 0;
453
454 INSERT /*+ append */ INTO opi_dbi_cogs_fstg m (
455 m.inventory_item_id,
456 m.organization_id,
457 m.order_line_id,
458 m.top_model_line_id,
459 m.top_model_item_id,
460 m.top_model_item_uom,
461 m.top_model_org_id,
462 m.customer_id,
463 m.cogs_val_b_draft,
464 m.cogs_val_b,
465 m.cogs_date,
466 m.source,
467 m.turns_cogs_flag,
468 m.internal_flag
469 )
470 -- 2 Regular sales order -- 8 Internal Sales Order
471 SELECT /*+ leading(log) index(mmt,MTL_MATERIAL_TRANSACTIONS_U2) use_nl(mmt,item,mta,h,cust_acct) */
472 mmt.inventory_item_id,
473 mmt.organization_id,
474 lines.line_id,
475 p_lines.line_id top_model_line_id,
476 p_lines.inventory_item_id top_model_item_id,
477 item.primary_uom_code top_model_item_uom,
478 p_lines.ship_from_org_id top_model_org_id,
479 nvl(cust_acct.party_id, -1),
480 0 cogs_val_b_draft,
481 mta.base_transaction_value,
482 mmt.transaction_date,
483 OPI_SOURCE,
484 decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
485 decode(p_lines.order_source_id,10,1,0)
486 FROM opi_dbi_conc_prog_run_log log,
487 mtl_material_transactions mmt,
488 mtl_transaction_accounts mta,
489 oe_order_lines_all lines, -- child line
490 oe_order_lines_all p_lines, -- parent line
491 oe_order_headers_all header,
492 hz_cust_accounts cust_acct,
493 mtl_system_items_b item
494 WHERE mmt.transaction_id >= log.from_bound_id
495 AND mmt.transaction_id < log.to_bound_id
496 AND mmt.organization_id = log.bound_level_entity_id
497 AND log.load_type = 'INCR'
498 AND log.etl_type = 'COGS'
499 AND mmt.transaction_source_type_id IN (2,8)
500 and mmt.transaction_type_id in (33, 34, 62)
501 and mmt.transaction_action_id IN (1, 21)
502 and mmt.transaction_id = mta.transaction_id
503 and mta.accounting_line_type <> 1
504 and lines.line_id = mmt.trx_source_line_id
505 and lines.order_source_id <> 27 -- retroactive billing
506 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
507 and lines.header_id = header.header_id
508 and header.sold_to_org_id = cust_acct.cust_account_id(+)
509 and item.inventory_item_id = p_lines.inventory_item_id
510 and item.organization_id = p_lines.ship_from_org_id
511 UNION ALL -- 12 RMA
512 SELECT /*+ leading(log) use_nl(mmt) */
513 mmt.inventory_item_id,
514 mmt.organization_id,
515 lines.line_id,
516 p_lines.line_id top_model_line_id,
517 p_lines.inventory_item_id top_model_item_id,
518 item.primary_uom_code top_model_item_uom,
519 p_lines.ship_from_org_id top_model_org_id,
520 nvl(cust_acct.party_id, -1),
521 0 cogs_val_b_draft,
522 mta.base_transaction_value,
523 mmt.transaction_date,
524 OPI_SOURCE,
525 1,
526 0
527 FROM opi_dbi_conc_prog_run_log log,
528 mtl_material_transactions mmt,
529 mtl_transaction_accounts mta,
530 oe_order_lines_all lines, -- child line
531 oe_order_lines_all l_lines, -- linking line
532 oe_order_lines_all p_lines, -- parent line
533 oe_order_headers_all header,
534 hz_cust_accounts cust_acct,
535 mtl_system_items_b item
536 WHERE mmt.transaction_id >= log.from_bound_id
537 AND mmt.transaction_id < log.to_bound_id
538 AND mmt.organization_id = log.bound_level_entity_id
539 AND log.load_type = 'INCR'
540 AND log.etl_type = 'COGS'
541 AND mmt.transaction_source_type_id = 12
542 AND mmt.transaction_id = mta.transaction_id
543 AND mta.accounting_line_type <> 1
544 AND lines.line_id = mmt.trx_source_line_id
545 AND lines.order_source_id <> 27 -- retroactive billing
546 AND lines.line_category_code = 'RETURN'
547 AND l_lines.line_id = nvl(lines.link_to_line_id, lines.line_id)
548 AND p_lines.line_id = nvl(l_lines.top_model_line_id, l_lines.line_id)
549 AND lines.header_id = header.header_id
550 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
551 AND item.inventory_item_id = p_lines.inventory_item_id
552 AND item.organization_id = p_lines.ship_from_org_id
553 UNION ALL -- drop ship
554 SELECT /*+ leading(log) use_nl(mmt) index(mmt,MTL_MATERIAL_TRANSACTIONS_N9) */
555 mmt.inventory_item_id,
556 mmt.organization_id,
557 lines.line_id,
558 p_lines.line_id top_model_line_id,
559 p_lines.inventory_item_id top_model_item_id,
560 item.primary_uom_code top_model_item_uom,
561 p_lines.ship_from_org_id top_model_org_id,
562 nvl(cust_acct.party_id, -1),
563 0 cogs_val_b_draft,
564 mta.base_transaction_value,
565 mmt.transaction_date,
566 OPI_SOURCE,
567 decode(p_mmt.transaction_type_id, 33, 1, 2),
568 0
569 FROM opi_dbi_conc_prog_run_log log,
570 mtl_material_transactions mmt,
571 mtl_material_transactions p_mmt,
572 mtl_transaction_accounts mta,
573 oe_order_lines_all lines,
574 oe_order_lines_all p_lines, -- parent line
575 oe_order_headers_all header,
576 hz_cust_accounts cust_acct,
577 mtl_system_items_b item
578 WHERE mmt.transaction_id >= log.from_bound_id
579 AND mmt.transaction_id < log.to_bound_id
580 AND mmt.organization_id = log.bound_level_entity_id
581 AND log.load_type = 'INCR'
582 AND log.etl_type = 'COGS'
583 AND mmt.transaction_type_id in (11,30)
584 AND mmt.transaction_action_id in (7,9)
585 AND mmt.organization_id = lines.ship_from_org_id
586 AND p_mmt.transaction_id = mmt.parent_transaction_id
587 AND ( -- internal drop
588 (p_mmt.transaction_type_id = 33
589 and p_mmt.transaction_action_id = 1 )
590 OR -- external drop
591 (p_mmt.transaction_type_id = 19
592 and p_mmt.transaction_action_id = 26 ))
593 AND mmt.transaction_id = mta.transaction_id
594 AND mta.accounting_line_type <> 1
595 AND lines.line_id = mmt.trx_source_line_id
596 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
597 AND lines.header_id = header.header_id
598 AND lines.order_source_id <> 27 -- retroactive billing
599 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
600 AND item.inventory_item_id = p_lines.inventory_item_id
601 AND item.organization_id = p_lines.ship_from_org_id
602 ;
603
604 COMMIT;
605
606 bis_collection_utilities.put_line('Exit incremental_opi_cogs() '||
607 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
608
609 EXCEPTION WHEN OTHERS THEN
610 --{
611 errbuf:= Sqlerrm;
612 retcode:= SQLCODE;
613
614 ROLLBACK;
615
616 bis_collection_utilities.put_line('Exception in incremental_opi_cogs() ' || errbuf );
617 --}
618 END incremental_opi_cogs;
619
620
621 /*===============================================================
622 This procedure extracts process data into the staging table.
623
624 Parameters:
625 - p_from_bound_date: lower run bound
626 - p_to_bound_date: upper run bound
627 - errbuf: error buffer
628 - retcode : return code
629 ================================================================*/
630 PROCEDURE initial_opm_cogs( p_from_bound_date IN DATE,
631 p_to_bound_date IN DATE,
632 errbuf IN OUT NOCOPY VARCHAR2,
633 retcode IN OUT NOCOPY VARCHAR2) IS
634
635 BEGIN
636
637 bis_collection_utilities.put_line('Enter initial_opm_cogs() ' ||
638 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
639
640 retcode := 0;
641
642 INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_fstg m (
643 m.inventory_item_id,
644 m.organization_id,
645 m.order_line_id,
646 m.top_model_line_id,
647 m.top_model_item_id,
648 m.top_model_item_uom,
649 m.top_model_org_id,
650 m.customer_id,
651 m.cogs_val_b_draft,
652 m.cogs_val_b,
653 m.cogs_date,
654 m.source,
655 m.turns_cogs_flag,
656 m.internal_flag
657 )
658 -- 33 Sales order issue; 34 Internal order issue; 62 Int Order Intr Ship
659 SELECT gtv.inventory_item_id,
660 gtv.organization_id,
661 lines.line_id,
662 p_lines.line_id top_model_line_id,
663 p_lines.inventory_item_id top_model_item_id,
664 item.primary_uom_code top_model_item_uom,
665 p_lines.ship_from_org_id top_model_org_id,
666 nvl(cust_acct.party_id, -1),
667 gtv.draft_value,
668 gtv.final_value,
669 gtv.transaction_date,
670 OPM_SOURCE,
671 decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
672 decode(p_lines.order_source_id,10,1,0)
673 FROM oe_order_lines_all lines, -- child line
674 oe_order_lines_all p_lines, -- parent line
675 oe_order_headers_all header,
676 hz_cust_accounts cust_acct,
677 mtl_system_items_b item,
678 (
679 SELECT gtv.transaction_id,
680 gtv.inventory_item_id,
681 gtv.organization_id,
682 gtv.line_id,
683 gtv.transaction_date,
684 sum(decode(gtv.accounted_flag, 'D', -gtv.txn_base_value, 0)) draft_value,
685 sum(decode(gtv.accounted_flag, 'D', 0, -gtv.txn_base_value)) final_value
686 FROM gmf_transaction_valuation gtv,
687 opi_dbi_org_le_temp tmp
688 WHERE gtv.transaction_type_id in (33, 34, 62)
689 AND nvl(gtv.accounted_flag, 'F') <> 'N'
690 AND nvl(gtv.final_posting_date, p_from_bound_date) >= p_from_bound_date
691 AND nvl(gtv.final_posting_date, p_from_bound_date) < p_to_bound_date
692 AND gtv.transaction_date >= g_global_start_date
693 AND gtv.journal_line_type = 'INV'
694 AND gtv.ledger_id = tmp.ledger_id
695 AND gtv.legal_entity_id = tmp.legal_entity_id
696 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
697 AND gtv.organization_id = tmp.organization_id
698 GROUP BY
699 gtv.transaction_id,
700 gtv.inventory_item_id,
701 gtv.organization_id,
702 gtv.line_id,
703 gtv.transaction_date) gtv
704 WHERE lines.line_id = gtv.line_id
705 AND lines.order_source_id <> 27 -- retroactive billing
706 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
707 AND lines.header_id = header.header_id
708 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
709 AND item.inventory_item_id = p_lines.inventory_item_id
710 AND item.organization_id = p_lines.ship_from_org_id
711 UNION ALL
712 -- 37 RMA Return; 16 Logical RMA Receipt; 15 RMA Receipt
713 SELECT gtv.inventory_item_id,
714 gtv.organization_id,
715 lines.line_id,
716 p_lines.line_id top_model_line_id,
717 p_lines.inventory_item_id top_model_item_id,
718 item.primary_uom_code top_model_item_uom,
719 p_lines.ship_from_org_id top_model_org_id,
720 nvl(cust_acct.party_id, -1),
721 gtv.draft_value,
722 gtv.final_value,
723 gtv.transaction_date,
724 OPM_SOURCE,
725 1,
726 0
727 FROM oe_order_lines_all lines, -- child line
728 oe_order_lines_all l_lines, -- linking line
729 oe_order_lines_all p_lines, -- parent line
730 oe_order_headers_all header,
731 hz_cust_accounts cust_acct,
732 mtl_system_items_b item,
733 (
734 SELECT gtv.transaction_id,
735 gtv.inventory_item_id,
736 gtv.organization_id,
737 gtv.line_id,
738 gtv.transaction_date,
739 sum(decode(gtv.accounted_flag, 'D', -gtv.txn_base_value, 0)) draft_value,
740 sum(decode(gtv.accounted_flag, 'D', 0, -gtv.txn_base_value)) final_value
741 FROM gmf_transaction_valuation gtv,
742 opi_dbi_org_le_temp tmp
743 WHERE gtv.transaction_type_id in (37, 16, 15)
744 AND nvl(gtv.accounted_flag, 'F') <> 'N'
745 AND nvl(gtv.final_posting_date, p_from_bound_date) >= p_from_bound_date
746 AND nvl(gtv.final_posting_date, p_from_bound_date) < p_to_bound_date
747 AND gtv.transaction_date >= g_global_start_date
748 AND gtv.journal_line_type = 'INV'
749 AND gtv.ledger_id = tmp.ledger_id
750 AND gtv.legal_entity_id = tmp.legal_entity_id
751 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
752 AND gtv.organization_id = tmp.organization_id
753 GROUP BY
754 gtv.transaction_id,
755 gtv.inventory_item_id,
756 gtv.organization_id,
757 gtv.line_id,
758 gtv.transaction_date) gtv
759 WHERE lines.line_id = gtv.line_id
760 AND lines.line_category_code = 'RETURN'
761 AND lines.order_source_id <> 27 -- retroactive billing
762 AND l_lines.line_id = nvl(lines.link_to_line_id, lines.line_id)
763 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
764 AND lines.header_id = header.header_id
765 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
766 AND item.inventory_item_id = p_lines.inventory_item_id
767 AND item.organization_id = p_lines.ship_from_org_id
768 UNION ALL
769 -- Drop Ship
770 SELECT gtv.inventory_item_id,
771 gtv.organization_id,
772 lines.line_id,
773 p_lines.line_id top_model_line_id,
774 p_lines.inventory_item_id top_model_item_id,
775 item.primary_uom_code top_model_item_uom,
776 p_lines.ship_from_org_id top_model_org_id,
777 nvl(cust_acct.party_id, -1),
778 gtv.draft_value,
779 gtv.final_value,
780 gtv.transaction_date,
781 OPM_SOURCE,
782 1,
783 0
784 FROM oe_order_lines_all lines, -- child line
785 oe_order_lines_all p_lines, -- parent line
786 oe_order_headers_all header,
787 hz_cust_accounts cust_acct,
788 mtl_system_items_b item,
789 mtl_material_transactions mmt,
790 mtl_material_transactions p_mmt,
791 (
792 SELECT gtv.transaction_id,
793 gtv.inventory_item_id,
794 gtv.organization_id,
795 gtv.line_id,
796 gtv.transaction_date,
797 sum(decode(gtv.accounted_flag, 'D', -gtv.txn_base_value, 0)) draft_value,
798 sum(decode(gtv.accounted_flag, 'D', 0, -gtv.txn_base_value)) final_value
799 FROM gmf_transaction_valuation gtv,
800 opi_dbi_org_le_temp tmp
801 WHERE gtv.transaction_type_id in (11, 30) -- 11 Logical intercompany sales issue
802 -- 30 Logical sales order issue
803 AND nvl(gtv.accounted_flag, 'F') <> 'N'
804 AND nvl(gtv.final_posting_date, p_from_bound_date) >= p_from_bound_date
805 AND nvl(gtv.final_posting_date, p_from_bound_date) < p_to_bound_date
806 AND gtv.transaction_date >= g_global_start_date
807 AND gtv.journal_line_type = 'INV'
808 AND gtv.ledger_id = tmp.ledger_id
809 AND gtv.legal_entity_id = tmp.legal_entity_id
810 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
811 AND gtv.organization_id = tmp.organization_id
812 GROUP BY
813 gtv.transaction_id,
814 gtv.inventory_item_id,
815 gtv.organization_id,
816 gtv.line_id,
817 gtv.transaction_date) gtv
818 WHERE gtv.transaction_id = mmt.transaction_id
819 AND p_mmt.transaction_id = mmt.parent_transaction_id
820 AND (p_mmt.transaction_type_id = 33 -- sales order issue, internal drop
821 OR p_mmt.transaction_type_id = 19) -- logical PO receipt, external drop
822 AND gtv.line_id = lines.line_id
823 AND gtv.organization_id = lines.ship_from_org_id
824 AND lines.order_source_id <> 27 -- retroactive billing
825 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
826 AND lines.header_id = header.header_id
827 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
828 AND item.inventory_item_id = p_lines.inventory_item_id
829 AND item.organization_id = p_lines.ship_from_org_id;
830
831
832 COMMIT;
833
834 bis_collection_utilities.put_line('Exit initial_opm_cogs() ' ||
835 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
836
837 EXCEPTION WHEN OTHERS THEN
838 --{
839 errbuf := Sqlerrm;
840 retcode := 1;
841
842 ROLLBACK;
843
844 bis_collection_utilities.put_line('Error in initial_opm_cogs()' || errbuf);
845 --}
846 END initial_opm_cogs;
847
848
849 /*======================================================================
850 This is the wrapper to extract COGS OPM data in initial load.
851 It gets the process run bounds, R12 migration, and calls
852 initial_opm_cogs and pre_r12_opm_cogs.
853
854 Parameters:
855 - errbuf: error buffer
856 - retcode: return code
857 =======================================================================*/
858
859 PROCEDURE initial_load_opm_cogs(errbuf IN OUT NOCOPY VARCHAR2,
860 retcode IN OUT NOCOPY VARCHAR2) IS
861
862 -- Declaration block
863
864 l_r12_mgr_date opi_dbi_conc_prog_run_log.last_run_date%type;
865 l_from_bound_date opi_dbi_conc_prog_run_log.from_bound_date%type;
866 l_to_bound_date opi_dbi_conc_prog_run_log.to_bound_date%type;
867
868 no_bounds_found exception;
869 BEGIN
870
871 bis_collection_utilities.put_line('Enter initial_load_opm_cogs() ' ||
872 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
873
874 -- Initialization block
875 retcode := 0;
876
877 -- Get R12 migration date. If GSD < R12 migration date,
878 -- get OPM data from Pre R12 data model
879
880 BEGIN
881 SELECT last_run_date
882 INTO l_r12_mgr_date
883 FROM opi_dbi_conc_prog_run_log
884 WHERE etl_type = 'R12_MIGRATION';
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 --{
888 l_r12_mgr_date := g_global_start_date;
889 --}
890 END;
891
892 IF (g_global_start_date < l_r12_mgr_date) THEN
893 --{
894 opi_dbi_pre_r12_cogs_pkg.pre_r12_opm_cogs(p_global_start_date => g_global_start_date,
895 errbuf => errbuf,
896 retcode => retcode);
897 --}
898 END IF;
899
900 -- Get process data from R12 converged data model
901
902 BEGIN
903 SELECT from_bound_date, to_bound_date
904 INTO l_from_bound_date, l_to_bound_date
905 FROM opi_dbi_conc_prog_run_log
906 WHERE etl_type = 'COGS'
907 AND driving_table_code = 'GTV'
908 AND load_type = 'INIT';
909 EXCEPTION
910 WHEN NO_DATA_FOUND THEN
911 --{
912 RAISE NO_BOUNDS_FOUND;
913 --}
914 END;
915
916 -- Call API to load ledger data into Global temp table
917 -- This temp table will be joined to extract process adjustments
918 bis_collection_utilities.put_line ('Loading Ledger data into temp table');
919 opi_dbi_bounds_pkg.load_opm_org_ledger_data;
920
921 -- Committing the data. Since the temp table is made with On Commit preserve rows
922 -- there will be no problem.
923 commit;
924
925 initial_opm_cogs(p_from_bound_date => l_from_bound_date,
926 p_to_bound_date => l_to_bound_date,
927 errbuf => errbuf,
928 retcode => retcode);
929
930 COMMIT;
931
932 bis_collection_utilities.put_line('Exit initial_load_opm_cogs() ' ||
933 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
934
935 EXCEPTION WHEN OTHERS THEN
936 --{
937 ROLLBACK;
938
939 bis_collection_utilities.put_line('Error in initial_load_opm_cogs() ' || Sqlerrm );
940 errbuf := Sqlerrm;
941 retcode := -1;
942 --}
943 END initial_load_opm_cogs;
944
945
946 /*=================================================================
947 This procedure incrementally extracts process data into the
948 staging table.
949
950 Parameters:
951 - p_from_bound_date: lower run bound
952 - p_to_bound_date: upper run bound
953 - errbuf: error buffer
954 - retcode: return code
955 ===================================================================*/
956
957 PROCEDURE incremental_opm_cogs( p_from_bound_date IN DATE,
958 p_to_bound_date IN DATE,
959 errbuf IN OUT NOCOPY NUMBER,
960 retcode IN OUT NOCOPY VARCHAR2 ) IS
961
962 BEGIN
963
964 bis_collection_utilities.put_line('Enter incremental_opm_cogs() ' ||
965 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
966 retcode := 0;
967
968 INSERT /*+ append */ INTO opi_dbi_cogs_fstg m (
969 m.inventory_item_id,
970 m.organization_id,
971 m.order_line_id,
972 m.top_model_line_id,
973 m.top_model_item_id,
974 m.top_model_item_uom,
975 m.top_model_org_id,
976 m.customer_id,
977 m.cogs_val_b_draft,
978 m.cogs_val_b,
979 m.cogs_date,
980 m.source,
981 m.turns_cogs_flag,
982 m.internal_flag )
983 -- 33 Sales order issue; 34 Internal order issue; 62 Int Order Intr Ship
984 SELECT /*+ ordered use_nl(lines, p_lines, header, cust_acct, item) */
985 gtv.inventory_item_id,
986 gtv.organization_id,
987 lines.line_id,
988 p_lines.line_id top_model_line_id,
989 p_lines.inventory_item_id top_model_item_id,
990 item.primary_uom_code top_model_item_uom,
991 p_lines.ship_from_org_id top_model_org_id,
992 nvl(cust_acct.party_id, -1),
993 gtv.draft_value,
994 gtv.final_value,
995 gtv.transaction_date,
996 OPM_SOURCE,
997 decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
998 decode(p_lines.order_source_id,10,1,0)
999 FROM (
1000 SELECT gtv.transaction_id,
1001 gtv.inventory_item_id,
1002 gtv.organization_id,
1003 gtv.line_id,
1004 gtv.transaction_date,
1005 sum(-gtv.txn_base_value) draft_value,
1006 0 final_value
1007 FROM gmf_transaction_valuation gtv,
1008 opi_dbi_org_le_temp tmp
1009 WHERE gtv.transaction_type_id in (33, 34, 62)
1010 AND gtv.accounted_flag = 'D'
1011 AND gtv.transaction_date >= g_global_start_date
1012 AND gtv.journal_line_type = 'INV'
1013 AND gtv.ledger_id = tmp.ledger_id
1014 AND gtv.legal_entity_id = tmp.legal_entity_id
1015 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1016 AND gtv.organization_id = tmp.organization_id
1017 GROUP BY
1018 gtv.transaction_id,
1019 gtv.inventory_item_id,
1020 gtv.organization_id,
1021 gtv.line_id,
1022 gtv.transaction_date
1023 UNION ALL
1024 SELECT gtv.transaction_id,
1025 gtv.inventory_item_id,
1026 gtv.organization_id,
1027 gtv.line_id,
1028 gtv.transaction_date,
1029 0 draft_value,
1030 sum(-gtv.txn_base_value) final_value
1031 FROM gmf_transaction_valuation gtv,
1032 opi_dbi_org_le_temp tmp
1033 WHERE gtv.transaction_type_id in (33, 34, 62)
1034 AND gtv.accounted_flag is NULL
1035 AND gtv.final_posting_date >= p_from_bound_date
1036 AND gtv.final_posting_date < p_to_bound_date
1037 AND gtv.transaction_date >= g_global_start_date
1038 AND gtv.journal_line_type = 'INV'
1039 AND gtv.ledger_id = tmp.ledger_id
1040 AND gtv.legal_entity_id = tmp.legal_entity_id
1041 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1042 AND gtv.organization_id = tmp.organization_id
1043 GROUP BY
1044 gtv.transaction_id,
1045 gtv.inventory_item_id,
1046 gtv.organization_id,
1047 gtv.line_id,
1048 gtv.transaction_date) gtv,
1049 oe_order_lines_all lines, -- child line
1050 oe_order_lines_all p_lines, -- parent line
1051 oe_order_headers_all header,
1052 hz_cust_accounts cust_acct,
1053 mtl_system_items_b item
1054 WHERE lines.line_id = gtv.line_id
1055 AND lines.order_source_id <> 27 -- retroactive billing
1056 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
1057 AND lines.header_id = header.header_id
1058 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
1059 AND item.inventory_item_id = p_lines.inventory_item_id
1060 AND item.organization_id = p_lines.ship_from_org_id
1061 UNION ALL
1062 -- 37 RMA Return; 16 Logical RMA Receipt; 15 RMA Receipt
1063 SELECT /*+ ordered use_nl(lines, p_lines, l_lines,header, cust_acct, item) */
1064 gtv.inventory_item_id,
1065 gtv.organization_id,
1066 lines.line_id,
1067 p_lines.line_id top_model_line_id,
1068 p_lines.inventory_item_id top_model_item_id,
1069 item.primary_uom_code top_model_item_uom,
1070 p_lines.ship_from_org_id top_model_org_id,
1071 nvl(cust_acct.party_id, -1),
1072 gtv.draft_value,
1073 gtv.final_value,
1074 gtv.transaction_date,
1075 OPM_SOURCE,
1076 1,
1077 0
1078 FROM (
1079 SELECT gtv.transaction_id,
1080 gtv.inventory_item_id,
1081 gtv.organization_id,
1082 gtv.line_id,
1083 gtv.transaction_date,
1084 sum(-gtv.txn_base_value) draft_value,
1085 0 final_value
1086 FROM gmf_transaction_valuation gtv,
1087 opi_dbi_org_le_temp tmp
1088 WHERE gtv.transaction_type_id in (37, 16, 15)
1089 AND gtv.accounted_flag = 'D'
1090 AND gtv.transaction_date >= g_global_start_date
1091 AND gtv.journal_line_type = 'INV'
1092 AND gtv.ledger_id = tmp.ledger_id
1093 AND gtv.legal_entity_id = tmp.legal_entity_id
1094 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1095 AND gtv.organization_id = tmp.organization_id
1096 GROUP BY
1097 gtv.transaction_id,
1098 gtv.inventory_item_id,
1099 gtv.organization_id,
1100 gtv.line_id,
1101 gtv.transaction_date
1102 UNION ALL
1103 SELECT gtv.transaction_id,
1104 gtv.inventory_item_id,
1105 gtv.organization_id,
1106 gtv.line_id,
1107 gtv.transaction_date,
1108 0 draft_value,
1109 sum(-gtv.txn_base_value) final_value
1110 FROM gmf_transaction_valuation gtv,
1111 opi_dbi_org_le_temp tmp
1112 WHERE gtv.transaction_type_id in (37, 16, 15)
1113 AND gtv.accounted_flag is NULL
1114 AND gtv.final_posting_date >= p_from_bound_date
1115 AND gtv.final_posting_date < p_to_bound_date
1116 AND gtv.transaction_date >= g_global_start_date
1117 AND gtv.journal_line_type = 'INV'
1118 AND gtv.ledger_id = tmp.ledger_id
1119 AND gtv.legal_entity_id = tmp.legal_entity_id
1120 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1121 AND gtv.organization_id = tmp.organization_id
1122 GROUP BY
1123 gtv.transaction_id,
1124 gtv.inventory_item_id,
1125 gtv.organization_id,
1126 gtv.line_id,
1127 gtv.transaction_date) gtv,
1128 oe_order_lines_all lines, -- child line
1129 oe_order_lines_all l_lines, -- linking line
1130 oe_order_lines_all p_lines, -- parent line
1131 oe_order_headers_all header,
1132 hz_cust_accounts cust_acct,
1133 mtl_system_items_b item
1134 WHERE lines.line_id = gtv.line_id
1135 AND lines.line_category_code = 'RETURN'
1136 AND lines.order_source_id <> 27 -- retroactive billing
1137 AND l_lines.line_id = nvl(lines.link_to_line_id, lines.line_id)
1138 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
1139 AND lines.header_id = header.header_id
1140 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
1141 AND item.inventory_item_id = p_lines.inventory_item_id
1142 AND item.organization_id = p_lines.ship_from_org_id
1143 UNION ALL
1144 -- Drop Ship
1145 SELECT /*+ ordered use_nl(mmt, p_mmt, lines, p_lines, header, cust_acct, item) index(mmt,mtl_material_transactions_u1)*/
1146 gtv.inventory_item_id,
1147 gtv.organization_id,
1148 lines.line_id,
1149 p_lines.line_id top_model_line_id,
1150 p_lines.inventory_item_id top_model_item_id,
1151 item.primary_uom_code top_model_item_uom,
1152 p_lines.ship_from_org_id top_model_org_id,
1153 nvl(cust_acct.party_id, -1),
1154 gtv.draft_value,
1155 gtv.final_value,
1156 gtv.transaction_date,
1157 OPM_SOURCE,
1158 1,
1159 0
1160 FROM (
1161 SELECT gtv.transaction_id,
1162 gtv.inventory_item_id,
1163 gtv.organization_id,
1164 gtv.line_id,
1165 gtv.transaction_date,
1166 sum(-gtv.txn_base_value) draft_value,
1167 0 final_value
1168 FROM gmf_transaction_valuation gtv,
1169 opi_dbi_org_le_temp tmp
1170 WHERE gtv.transaction_type_id in (11, 30) -- 11 Logical intercompany sales issue
1171 -- 30 Logical sales order issue
1172 AND gtv.accounted_flag = 'D'
1173 AND gtv.transaction_date >= g_global_start_date
1174 AND gtv.journal_line_type = 'INV'
1175 AND gtv.ledger_id = tmp.ledger_id
1176 AND gtv.legal_entity_id = tmp.legal_entity_id
1177 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1178 AND gtv.organization_id = tmp.organization_id
1179 GROUP BY
1180 gtv.transaction_id,
1181 gtv.inventory_item_id,
1182 gtv.organization_id,
1183 gtv.line_id,
1184 gtv.transaction_date
1185 UNION ALL
1186 SELECT gtv.transaction_id,
1187 gtv.inventory_item_id,
1188 gtv.organization_id,
1189 gtv.line_id,
1190 gtv.transaction_date,
1191 0 draft_value,
1192 sum(-gtv.txn_base_value) final_value
1193 FROM gmf_transaction_valuation gtv,
1194 opi_dbi_org_le_temp tmp
1195 WHERE gtv.transaction_type_id in (11, 30) -- 11 Logical intercompany sales issue
1196 -- 30 Logical sales order issue
1197 AND gtv.accounted_flag is NULL
1198 AND gtv.final_posting_date >= p_from_bound_date
1199 AND gtv.final_posting_date < p_to_bound_date
1200 AND gtv.transaction_date >= g_global_start_date
1201 AND gtv.journal_line_type = 'INV'
1202 AND gtv.ledger_id = tmp.ledger_id
1203 AND gtv.legal_entity_id = tmp.legal_entity_id
1204 AND gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1205 AND gtv.organization_id = tmp.organization_id
1206 GROUP BY
1207 gtv.transaction_id,
1208 gtv.inventory_item_id,
1209 gtv.organization_id,
1210 gtv.line_id,
1211 gtv.transaction_date) gtv,
1212 mtl_material_transactions mmt,
1213 mtl_material_transactions p_mmt,
1214 oe_order_lines_all lines, -- child line
1215 oe_order_lines_all p_lines, -- parent line
1216 oe_order_headers_all header,
1217 hz_cust_accounts cust_acct,
1218 mtl_system_items_b item
1219 WHERE gtv.transaction_id = mmt.transaction_id
1220 AND p_mmt.transaction_id = mmt.parent_transaction_id
1221 AND (p_mmt.transaction_type_id = 33 -- sales order issue, internal drop
1222 OR p_mmt.transaction_type_id = 19) -- logical PO receipt, external drop
1223 AND gtv.line_id = lines.line_id
1224 AND gtv.organization_id = lines.ship_from_org_id
1225 AND lines.order_source_id <> 27 -- retroactive billing
1226 AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
1227 AND lines.header_id = header.header_id
1228 AND header.sold_to_org_id = cust_acct.cust_account_id(+)
1229 AND item.inventory_item_id = p_lines.inventory_item_id
1230 AND item.organization_id = p_lines.ship_from_org_id;
1231
1232 COMMIT;
1233
1234 bis_collection_utilities.put_line('Exit incremental_opm_cogs() ' ||
1235 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1236
1237 EXCEPTION WHEN OTHERS THEN
1238 --{
1239 ROLLBACK;
1240
1241 retcode := -1;
1242 bis_collection_utilities.put_line(' Error in incremental_opm_cogs()');
1243 bis_collection_utilities.put_line( Sqlerrm );
1244 --}
1245 END incremental_opm_cogs;
1246
1247
1248 /*=======================================================================
1249 This is the wrapper for OPM COGS incremental load.
1250 It gets the process run bounds and calls incremental_opm_cogs.
1251
1252 Parameters:
1253 - errbuf: error buffer
1254 - retcode: return code
1255 ========================================================================*/
1256
1257 PROCEDURE incremental_load_opm_cogs(errbuf IN OUT NOCOPY VARCHAR2,
1258 retcode IN OUT NOCOPY VARCHAR2) IS
1259
1260 l_from_bound_date opi_dbi_conc_prog_run_log.from_bound_date%type;
1261 l_to_bound_date opi_dbi_conc_prog_run_log.to_bound_date%type;
1262
1263 no_bounds_found exception;
1264
1265 BEGIN
1266
1267 bis_collection_utilities.put_line('Enter incremental_load_opm_cogs() ' ||
1268 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1269
1270 -- Initialization
1271 retcode := 0;
1272
1273 -- Get process data from R12 converged data model
1274
1275 BEGIN
1276 SELECT from_bound_date, to_bound_date
1277 INTO l_from_bound_date, l_to_bound_date
1278 FROM opi_dbi_conc_prog_run_log
1279 WHERE etl_type = 'COGS'
1280 AND driving_table_code = 'GTV'
1281 AND load_type = 'INCR';
1282
1283 EXCEPTION
1284 WHEN NO_DATA_FOUND THEN
1285 --{
1286 RAISE NO_BOUNDS_FOUND;
1287 --}
1288 END;
1289
1290 -- Call API to load ledger data into Global temp table
1291 -- This temp table will be joined to extract process adjustments
1292 bis_collection_utilities.put_line ('Loading Ledger data into temp table');
1293 opi_dbi_bounds_pkg.load_opm_org_ledger_data;
1294
1295 -- Committing the data. Since the temp table is made with On Commit preserve rows
1296 -- there will be no problem.
1297 commit;
1298
1299 incremental_opm_cogs(p_from_bound_date => l_from_bound_date,
1300 p_to_bound_date => l_to_bound_date,
1301 errbuf => errbuf,
1302 retcode => retcode);
1303
1304 COMMIT;
1305
1306 bis_collection_utilities.put_line('Exit incremental_load_opm_cogs() ' ||
1307 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1308 EXCEPTION WHEN OTHERS THEN
1309 --{
1310 ROLLBACK;
1311
1312 bis_collection_utilities.put_line ('Error in incremental_load_opm_cogs() '|| Sqlerrm );
1313 errbuf := Sqlerrm;
1314 retcode := -1;
1315 --}
1316 END incremental_load_opm_cogs;
1317
1318
1319 /*===============================================================
1320 This procedure gets conversion rates for COGS in incremental
1321 load.
1322
1323 Parameters:
1324 - errbuf: error buffer
1325 - retcode : return code
1326 ================================================================*/
1327
1328 PROCEDURE get_cogs_conversion_rate ( errbuf IN OUT NOCOPY VARCHAR2,
1329 retcode IN OUT NOCOPY VARCHAR2) IS
1330
1331 -- Cursor to see if any rates are missing. See below for details
1332 CURSOR invalid_rates_exist_csr IS
1333 SELECT 1
1334 FROM opi_dbi_cogsf_conv_rates
1335 WHERE (nvl (conversion_rate, -999) < 0 OR
1336 nvl (sec_conversion_rate, 999) < 0)
1337 AND rownum < 2;
1338
1339 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
1340
1341
1342 -- Set up a cursor to get all the invalid rates.
1343 -- By the logic of the fii_currency.get_global_rate_primary
1344 -- and fii_currency.get_global_rate_secondary APIs, the returned value
1345 -- is -ve if no rate exists:
1346 -- -1 for dates with no rate.
1347 -- -2 for unrecognized conversion rates.
1348 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
1349 -- transaction_date is prior to 01-JAN-1999 (when the EUR
1350 -- officially went into circulation).
1351 --
1352 -- However, with the secondary currency, the null rate means it
1353 -- has not been setup and should therefore not be reported as an
1354 -- error.
1355 --
1356 -- Also, cross check with the org-date pairs in the staging table,
1357 -- in case some orgs never had a functional currency code defined.
1358 CURSOR get_missing_rates_c (p_pri_sec_curr_same NUMBER) IS
1359 SELECT /*+ parallel (compare) */
1360 DISTINCT
1361 report_order,
1362 curr_code,
1363 rate_type,
1364 cogs_date,
1365 func_currency_code
1366 FROM (
1367 SELECT /*+ parallel (conv) parallel (mp) parallel (to_conv) */
1368 DISTINCT
1369 g_global_currency_code curr_code,
1370 g_global_rate_type rate_type,
1371 1 report_order, -- ordering global currency first
1372 mp.organization_code,
1373 decode (conv.conversion_rate,
1374 C_EURO_MISSING_AT_START, g_euro_start_date,
1375 conv.transaction_date) cogs_date,
1376 conv.f_currency_code func_currency_code
1377 FROM opi_dbi_cogsf_conv_rates conv,
1378 mtl_parameters mp,
1379 (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1380 DISTINCT organization_id, trunc (cogs_date) cogs_date
1381 FROM opi_dbi_cogs_fstg) to_conv
1382 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1383 AND mp.organization_id = to_conv.organization_id
1384 AND conv.transaction_date (+) = to_conv.cogs_date
1385 AND conv.organization_id (+) = to_conv.organization_id
1386 UNION ALL
1387 SELECT /*+ parallel (conv) parallel (mp) parallel (to_conv) */
1388 DISTINCT
1389 g_secondary_currency_code curr_code,
1390 g_secondary_rate_type rate_type,
1391 decode (p_pri_sec_curr_same,
1392 1, 1,
1393 2) report_order, --ordering secondary currency next
1394 mp.organization_code,
1395 decode (conv.sec_conversion_rate,
1396 C_EURO_MISSING_AT_START, g_euro_start_date,
1397 conv.transaction_date) cogs_date,
1398 conv.f_currency_code func_currency_code
1399 FROM opi_dbi_cogsf_conv_rates conv,
1400 mtl_parameters mp,
1401 (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1402 DISTINCT organization_id, trunc (cogs_date) cogs_date
1403 FROM opi_dbi_cogs_fstg) to_conv
1404 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1405 AND mp.organization_id = to_conv.organization_id
1406 AND conv.transaction_date (+) = to_conv.cogs_date
1407 AND conv.organization_id (+) = to_conv.organization_id)
1408 compare
1409 ORDER BY
1410 report_order ASC,
1411 cogs_date,
1412 func_currency_code;
1413
1414 l_stmt_num NUMBER;
1415 no_currency_rate_flag NUMBER;
1416
1417 -- Flag to check if the primary and secondary currencies are the same
1418 l_pri_sec_curr_same NUMBER;
1419
1420
1421 BEGIN
1422
1423 bis_collection_utilities.put_line('Enter get_cogs_conversion_rate() '||
1424 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1425
1426 l_stmt_num := 0;
1427 -- initialization block
1428 retcode := g_ok;
1429 no_currency_rate_flag := 0;
1430 l_pri_sec_curr_same := 0;
1431
1432 l_stmt_num := 10;
1433 -- check if the primary and secondary currencies and rate types are same
1434
1435 IF (g_global_currency_code = nvl (g_secondary_currency_code, '---') AND
1436 g_global_rate_type = nvl (g_secondary_rate_type, '---') ) THEN
1437 --{
1438 l_pri_sec_curr_same := 1;
1439 --}
1440 END IF;
1441
1442
1443 l_stmt_num := 20;
1444 -- Use the fii_currency.get_global_rate_primary function to get the
1445 -- conversion rate given a currency code and a date.
1446 -- The function returns:
1447 -- 1 for currency code of 'USD' which is the global currency
1448 -- -1 for dates for which there is no currency conversion rate
1449 -- -2 for unrecognized currency conversion rates
1450 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
1451 -- transaction_date is prior to 01-JAN-1999 (when the EUR
1452 -- officially went into circulation).
1453
1454 -- Use the fii_currency.get_global_rate_secondary to get the secondary
1455 -- global rate. If the secondary currency has not been set up,
1456 -- make the rate null. If the secondary currency/rate types are the
1457 -- same as the primary, don't call the API but rather use an update
1458 -- statement followed by the insert.
1459
1460 -- By selecting distinct org and currency code from the gl_set_of_books
1461 -- and hr_organization_information, take care of duplicate codes.
1462
1463 INSERT /*+ append parallel(rates) */
1464 INTO opi_dbi_cogsf_conv_rates rates (
1465 organization_id,
1466 f_currency_code,
1467 transaction_date,
1468 conversion_rate,
1469 sec_conversion_rate)
1470 SELECT /*+ parallel (to_conv) parallel (curr_codes) */
1471 to_conv.organization_id,
1472 curr_codes.currency_code,
1473 to_conv.cogs_date,
1474 decode (curr_codes.currency_code,
1475 g_global_currency_code, 1,
1476 fii_currency.get_global_rate_primary (
1477 curr_codes.currency_code,
1478 to_conv.cogs_date) ),
1479 decode (g_secondary_currency_code,
1480 NULL, NULL,
1481 curr_codes.currency_code, 1,
1482 decode (l_pri_sec_curr_same,
1483 1, C_PRI_SEC_CURR_SAME_MARKER,
1484 fii_currency.get_global_rate_secondary (
1485 curr_codes.currency_code,
1486 to_conv.cogs_date)))
1487 FROM
1488 (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1489 DISTINCT organization_id, trunc (cogs_date) cogs_date
1490 FROM opi_dbi_cogs_fstg) to_conv,
1491 (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
1492 parallel (hoi) parallel (gsob)*/
1493 DISTINCT hoi.organization_id, gsob.currency_code
1494 FROM hr_organization_information hoi,
1495 gl_sets_of_books gsob
1496 WHERE hoi.org_information_context = 'Accounting Information'
1497 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
1498 curr_codes
1499 WHERE curr_codes.organization_id = to_conv.organization_id;
1500
1501
1502 --Introduced commit because of append parallel in the insert stmt above.
1503 commit;
1504
1505 l_stmt_num := 40;
1506 -- if the primary and secondary currency codes are the same, then
1507 -- update the secondary with the primary
1508 IF (l_pri_sec_curr_same = 1) THEN
1509 --{
1510 UPDATE /*+ parallel (opi_dbi_cogsf_conv_rates) */
1511 opi_dbi_cogsf_conv_rates
1512 SET sec_conversion_rate = conversion_rate;
1513
1514 -- safe to commit, as before
1515 commit;
1516 --}
1517 END IF;
1518
1519 -- report missing rate
1520 l_stmt_num := 50;
1521
1522 OPEN invalid_rates_exist_csr;
1523 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
1524 IF (invalid_rates_exist_csr%FOUND) THEN
1525 --{
1526 bis_collection_utilities.put_line('missing conversion rates');
1527
1528 -- there are missing rates - prepare to report them.
1529 no_currency_rate_flag := 1;
1530 bis_collection_utilities.writeMissingRateHeader;
1531
1532 l_stmt_num := 60;
1533 FOR get_missing_rates_rec IN get_missing_rates_c (l_pri_sec_curr_same)
1534 LOOP
1535
1536 bis_collection_utilities.writemissingrate (
1537 get_missing_rates_rec.rate_type,
1538 get_missing_rates_rec.func_currency_code,
1539 get_missing_rates_rec.curr_code,
1540 get_missing_rates_rec.cogs_date);
1541
1542 END LOOP;
1543 --}
1544 END IF;
1545 CLOSE invalid_rates_exist_csr;
1546
1547
1548 l_stmt_num := 70; /* check no_currency_rate_flag */
1549 IF (no_currency_rate_flag = 1) THEN /* missing rate found */
1550 bis_collection_utilities.put_line('ERROR: Please setup conversion rate for all missing rates reported');
1551
1552 retcode := g_error;
1553 END IF;
1554
1555 bis_collection_utilities.put_line('Exit get_cogs_conversion_rate() '||
1556 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1557
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 --{
1561 rollback;
1562 retcode := SQLCODE;
1563 errbuf := 'REPORT_MISSING_RATE (' || to_char(l_stmt_num)
1564 || '): '|| substr(SQLERRM, 1,200);
1565
1566 bis_collection_utilities.put_line('Error at statement ('
1567 || to_char(l_stmt_num)
1568 || ')');
1569
1570 bis_collection_utilities.put_line('Error Number: ' || retcode );
1571 bis_collection_utilities.put_line('Error Message: ' || errbuf );
1572 --}
1573 END get_cogs_conversion_rate;
1574
1575
1576
1577 /*===============================================================
1578 This procedure gets conversion rates for COGS in incremental
1579 load.
1580
1581 Parameters:
1582 - errbuf: error buffer
1583 - retcode : return code
1584 ================================================================*/
1585
1586 PROCEDURE get_cogs_conversion_rate_incr ( errbuf IN OUT NOCOPY VARCHAR2,
1587 retcode IN OUT NOCOPY VARCHAR2)
1588 IS
1589
1590 -- Cursor to see if any rates are missing. See below for details
1591 CURSOR invalid_rates_exist_csr IS
1592 SELECT 1
1593 FROM opi_dbi_cogsf_conv_rates
1594 WHERE ( nvl (conversion_rate, -999) < 0
1595 OR nvl (sec_conversion_rate, 999) < 0)
1596 AND rownum < 2;
1597
1598 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
1599
1600
1601 -- Set up a cursor to get all the invalid rates.
1602 -- By the logic of the fii_currency.get_global_rate_primary
1603 -- and fii_currency.get_global_rate_secondary APIs, the returned value
1604 -- is -ve if no rate exists:
1605 -- -1 for dates with no rate.
1606 -- -2 for unrecognized conversion rates.
1607 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
1608 -- transaction_date is prior to 01-JAN-1999 (when the EUR
1609 -- officially went into circulation).
1610 --
1611 -- However, with the secondary currency, the null rate means it
1612 -- has not been setup and should therefore not be reported as an
1613 -- error.
1614 --
1615 -- Also, cross check with the org-date pairs in the staging table,
1616 -- in case some orgs never had a functional currency code defined.
1617 CURSOR get_missing_rates_c (p_pri_sec_curr_same NUMBER) IS
1618 SELECT DISTINCT
1619 report_order,
1620 curr_code,
1621 rate_type,
1622 cogs_date,
1623 func_currency_code
1624 FROM (
1625 SELECT DISTINCT
1626 g_global_currency_code curr_code,
1627 g_global_rate_type rate_type,
1628 1 report_order, -- ordering global currency first
1629 mp.organization_code,
1630 decode (conv.conversion_rate,
1631 C_EURO_MISSING_AT_START, g_euro_start_date,
1632 conv.transaction_date) cogs_date,
1633 conv.f_currency_code func_currency_code
1634 FROM opi_dbi_cogsf_conv_rates conv,
1635 mtl_parameters mp,
1636 (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1637 DISTINCT organization_id, trunc (cogs_date) cogs_date
1638 FROM opi_dbi_cogs_fstg) to_conv
1639 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1640 AND mp.organization_id = to_conv.organization_id
1641 AND conv.transaction_date (+) = to_conv.cogs_date
1642 AND conv.organization_id (+) = to_conv.organization_id
1643 UNION ALL
1644 SELECT DISTINCT
1645 g_secondary_currency_code curr_code,
1646 g_secondary_rate_type rate_type,
1647 decode (p_pri_sec_curr_same,
1648 1, 1,
1649 2) report_order, --ordering secondary currency next
1650 mp.organization_code,
1651 decode (conv.sec_conversion_rate,
1652 C_EURO_MISSING_AT_START, g_euro_start_date,
1653 conv.transaction_date) cogs_date,
1654 conv.f_currency_code func_currency_code
1655 FROM opi_dbi_cogsf_conv_rates conv,
1656 mtl_parameters mp,
1657 (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1658 DISTINCT organization_id, trunc (cogs_date) cogs_date
1659 FROM opi_dbi_cogs_fstg) to_conv
1660 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1661 AND mp.organization_id = to_conv.organization_id
1662 AND conv.transaction_date (+) = to_conv.cogs_date
1663 AND conv.organization_id (+) = to_conv.organization_id)
1664 ORDER BY
1665 report_order ASC,
1666 cogs_date,
1667 func_currency_code;
1668
1669 l_stmt_num NUMBER;
1670 no_currency_rate_flag NUMBER := 0;
1671
1672 -- Flag to check if the primary and secondary currencies are the
1673 -- same
1674 l_pri_sec_curr_same NUMBER;
1675
1676
1677 BEGIN
1678
1679 bis_collection_utilities.put_line('Enter get_cogs_conversion_rate_incr() '||
1680 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1681
1682 -- Initialization block
1683 l_stmt_num := 0;
1684 retcode := g_ok;
1685 no_currency_rate_flag := 0;
1686 l_pri_sec_curr_same := 0;
1687
1688 l_stmt_num := 10;
1689 -- check if the primary and secondary currencies and rate types are same
1690
1691 IF (g_global_currency_code = nvl (g_secondary_currency_code, '---') AND
1692 g_global_rate_type = nvl (g_secondary_rate_type, '---') ) THEN
1693 --{
1694 l_pri_sec_curr_same := 1;
1695 --}
1696 END IF;
1697
1698
1699 l_stmt_num := 20;
1700 -- Use the fii_currency.get_global_rate_primary function to get the
1701 -- conversion rate given a currency code and a date.
1702 -- The function returns:
1703 -- 1 for currency code of 'USD' which is the global currency
1704 -- -1 for dates for which there is no currency conversion rate
1705 -- -2 for unrecognized currency conversion rates
1706 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
1707 -- transaction_date is prior to 01-JAN-1999 (when the EUR
1708 -- officially went into circulation).
1709
1710 -- Use the fii_currency.get_global_rate_secondary to get the secondary
1711 -- global rate. If the secondary currency has not been set up,
1712 -- make the rate null. If the secondary currency/rate types are the
1713 -- same as the primary, don't call the API but rather use an update
1714 -- statement followed by the insert.
1715
1716 -- By selecting distinct org and currency code from the gl_set_of_books
1717 -- and hr_organization_information, take care of duplicate codes.
1718
1719 INSERT /*+ append */
1720 INTO opi_dbi_cogsf_conv_rates rates (
1721 organization_id,
1722 f_currency_code,
1723 transaction_date,
1724 conversion_rate,
1725 sec_conversion_rate)
1726 SELECT
1727 to_conv.organization_id,
1728 curr_codes.currency_code,
1729 to_conv.cogs_date,
1730 decode (curr_codes.currency_code,
1731 g_global_currency_code, 1,
1732 fii_currency.get_global_rate_primary (
1733 curr_codes.currency_code,
1734 to_conv.cogs_date) ),
1735 decode (g_secondary_currency_code,
1736 NULL, NULL,
1737 curr_codes.currency_code, 1,
1738 decode (l_pri_sec_curr_same,
1739 1, C_PRI_SEC_CURR_SAME_MARKER,
1740 fii_currency.get_global_rate_secondary (
1741 curr_codes.currency_code,
1742 to_conv.cogs_date)))
1743 FROM
1744 (SELECT
1745 DISTINCT organization_id, trunc (cogs_date) cogs_date
1746 FROM opi_dbi_cogs_fstg) to_conv,
1747 (SELECT
1748 DISTINCT hoi.organization_id, gsob.currency_code
1749 FROM hr_organization_information hoi,
1750 gl_sets_of_books gsob
1751 WHERE hoi.org_information_context = 'Accounting Information'
1752 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
1753 curr_codes
1754 WHERE curr_codes.organization_id = to_conv.organization_id;
1755
1756
1757 --Introduced commit because of append parallel in the insert stmt above.
1758 commit;
1759
1760 l_stmt_num := 40;
1761 -- if the primary and secondary currency codes are the same, then
1762 -- update the secondary with the primary
1763 IF (l_pri_sec_curr_same = 1) THEN
1764 --{
1765 UPDATE /*+ parallel (opi_dbi_cogsf_conv_rates) */
1766 opi_dbi_cogsf_conv_rates
1767 SET sec_conversion_rate = conversion_rate;
1768
1769 -- safe to commit, as before
1770 commit;
1771 --}
1772 END IF;
1773
1774
1775 -- report missing rate
1776 l_stmt_num := 50;
1777
1778 OPEN invalid_rates_exist_csr;
1779 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
1780 IF (invalid_rates_exist_csr%FOUND) THEN
1781 --{
1782 -- there are missing rates - prepare to report them.
1783 no_currency_rate_flag := 1;
1784 bis_collection_utilities.writeMissingRateHeader;
1785
1786 l_stmt_num := 60;
1787 FOR get_missing_rates_rec IN get_missing_rates_c (l_pri_sec_curr_same)
1788 LOOP
1789
1790 bis_collection_utilities.writemissingrate (
1791 get_missing_rates_rec.rate_type,
1792 get_missing_rates_rec.func_currency_code,
1793 get_missing_rates_rec.curr_code,
1794 get_missing_rates_rec.cogs_date);
1795
1796 END LOOP;
1797 --}
1798 END IF;
1799 CLOSE invalid_rates_exist_csr;
1800
1801
1802 l_stmt_num := 70; /* check no_currency_rate_flag */
1803 IF (no_currency_rate_flag = 1) THEN /* missing rate found */
1804 --{
1805 bis_collection_utilities.put_line('ERROR: Please setup conversion rate for all missing rates reported');
1806
1807 retcode := g_error;
1808 --}
1809 END IF;
1810
1811 bis_collection_utilities.put_line('Exit get_cogs_conversion_rate_incr() '||
1812 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1813
1814 EXCEPTION
1815 WHEN OTHERS THEN
1816 --{
1817 rollback;
1818 retcode := SQLCODE;
1819 errbuf := 'REPORT_MISSING_RATE (' || to_char(l_stmt_num)
1820 || '): '|| substr(SQLERRM, 1,200);
1821
1822 bis_collection_utilities.put_line('Error at statement ('
1823 || to_char(l_stmt_num)
1824 || ')');
1825
1826 bis_collection_utilities.put_line('Error Number: ' || retcode );
1827 bis_collection_utilities.put_line('Error Message: ' || errbuf );
1828 --}
1829 END get_cogs_conversion_rate_incr;
1830
1831 /*======================================================================
1832 This is the wrapper procedure for COGS initial load which extracts
1833 data for discrete and process organizations.
1834
1835 Parameters:
1836 - errbuf: error buffer
1837 - retcode: return code
1838 =======================================================================*/
1839
1840 PROCEDURE initial_load_cogs ( errbuf IN OUT NOCOPY VARCHAR2,
1841 retcode IN OUT NOCOPY VARCHAR2 ) IS
1842
1843 -- Declaration
1844
1845 l_stmt_num NUMBER;
1846 l_row_count NUMBER;
1847 l_error_flag BOOLEAN;
1848 l_bounds_warning BOOLEAN;
1849
1850 l_opi_schema VARCHAR2(30);
1851 l_status VARCHAR2(30);
1852 l_industry VARCHAR2(30);
1853
1854 schema_info_not_found exception;
1855 BEGIN
1856
1857 bis_collection_utilities.put_line('Enter initial_load_cogs() '||
1858 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1859 -- Initialization
1860 l_error_flag := FALSE;
1861 retcode := 0;
1862
1863 bis_collection_utilities.put_line ('Initial Load COGS starts at '
1864 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1865
1866 IF bis_collection_utilities.setup('OPI_DBI_COGS_F' ) = false THEN
1867 --{
1868 RAISE_APPLICATION_ERROR(-20000, errbuf);
1869 --}
1870 END IF;
1871
1872 -- Performance tuning change
1873 execute immediate 'alter session set hash_area_size=100000000';
1874 execute immediate 'alter session set sort_area_size=100000000';
1875
1876 -- Setup globals
1877 l_stmt_num := 10;
1878
1879 check_setup_globals(errbuf => errbuf, retcode => retcode);
1880
1881 IF retcode <> 0 THEN
1882 --{
1883 RETURN ;
1884 --}
1885 END IF;
1886
1887
1888 -- Common Clean up
1889 l_stmt_num := 20;
1890
1891 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
1892 --{
1893 execute immediate 'truncate table ' || l_opi_schema
1894 || '.opi_dbi_cogsf_conv_rates ';
1895
1896 execute immediate 'truncate table ' || l_opi_schema
1897 || '.opi_dbi_cogs_run_log ';
1898
1899 execute immediate 'truncate table ' || l_opi_schema
1900 || '.opi_dbi_cogs_fstg ';
1901
1902 -- bug 3863905- mv log is now dropped before initial load
1903 -- we shouldnt be truncating mv log anymore
1904
1905 -- execute immediate 'truncate table ' || l_opi_schema
1906 --|| '.mlog$_opi_dbi_cogs_f';
1907
1908 execute immediate 'truncate table ' || l_opi_schema
1909 || '.opi_dbi_cogs_f PURGE MATERIALIZED VIEW LOG';
1910 --}
1911 ELSE
1912 --{
1913 RAISE schema_info_not_found;
1914 --}
1915 END IF;
1916
1917
1918 -- Get start/end bounds for Discrete and Process
1919 l_stmt_num := 30;
1920
1921 opi_dbi_bounds_pkg.maintain_opi_dbi_logs(p_etl_type => 'COGS', p_load_type => 'INIT');
1922
1923 IF retcode <> 0 THEN
1924 --{
1925 l_error_flag := TRUE;
1926 --}
1927 END IF;
1928
1929
1930 -- check if some bounds are uncosted before calling any other
1931 -- procedure that can wipe out the stop reason code
1932 l_stmt_num:= 40;
1933 l_bounds_warning := opi_dbi_bounds_pkg.bounds_uncosted(p_etl_type => 'COGS',
1934 p_load_type => 'INIT');
1935
1936 -- Print the discrete org collection bounds
1937 l_stmt_num := 50;
1938 opi_dbi_bounds_pkg.print_opi_org_bounds(p_etl_type => 'COGS', p_load_type => 'INIT');
1939
1940
1941 -- Load discrete cogs into staging table
1942 l_stmt_num := 60;
1943
1944 bis_collection_utilities.put_line('Load OPI cogs into stg '
1945 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1946
1947 init_opi_cogs( errbuf => errbuf, retcode => retcode);
1948
1949 IF retcode <> 0 THEN
1950 --{
1951 l_error_flag := TRUE;
1952 --}
1953 END IF;
1954
1955
1956
1957 -- Load process cogs into staging table
1958 l_stmt_num :=70;
1959
1960 bis_collection_utilities.put_line('Load OPM cogs into stg '
1961 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1962
1963 initial_load_opm_cogs(errbuf => errbuf, retcode => retcode);
1964
1965 IF retcode <> 0 THEN
1966 --{
1967 l_error_flag := TRUE;
1968 --}
1969 END IF;
1970
1971
1972
1973 -- Get conversion rates
1974 -- For improve perf, need to commit in stg/conversion rate tables
1975 -- and gather statistics
1976
1977 l_stmt_num := 80;
1978
1979
1980 gather_stats(p_table_name => 'OPI_DBI_COGS_FSTG');
1981
1982 get_cogs_conversion_rate( errbuf => errbuf, retcode => retcode );
1983 commit;
1984
1985 gather_stats(p_table_name => 'OPI_DBI_COGSF_CONV_RATES');
1986
1987
1988 IF retcode <> 0 THEN
1989 --{
1990 l_error_flag := TRUE;
1991 --}
1992 END IF;
1993
1994
1995 IF l_error_flag <> TRUE THEN
1996 --{
1997 -- Load from staging table into fact table
1998 l_stmt_num := 90;
1999
2000 INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_f m (
2001 m.inventory_item_id,
2002 m.organization_id,
2003 m.order_line_id,
2004 m.top_model_line_id,
2005 m.top_model_item_id,
2006 m.top_model_item_uom,
2007 m.top_model_org_id,
2008 m.customer_id,
2009 m.cogs_val_b_draft,
2010 m.cogs_val_b,
2011 m.cogs_val_g,
2012 m.cogs_val_sg,
2013 m.cogs_date,
2014 m.source,
2015 m.turns_cogs_flag,
2016 m.internal_flag,
2017 m.creation_date,
2018 m.last_update_date,
2019 m.created_by,
2020 m.last_updated_by,
2021 m.last_updated_login,
2022 m.program_id,
2023 m.program_login_id,
2024 m.program_application_id,
2025 m.request_id)
2026 SELECT /*+ parallel(stg) parallel(rate) */
2027 stg.inventory_item_id,
2028 stg.organization_id,
2029 stg.order_line_id,
2030 stg.top_model_line_id,
2031 stg.top_model_item_id,
2032 stg.top_model_item_uom,
2033 stg.top_model_org_id,
2034 stg.customer_id,
2035 sum(stg.cogs_val_b_draft),
2036 sum(stg.cogs_val_b_draft + stg.cogs_val_b),
2037 sum((stg.cogs_val_b_draft + stg.cogs_val_b) * rate.conversion_rate),
2038 sum((stg.cogs_val_b_draft + stg.cogs_val_b) * rate.sec_conversion_rate),
2039 trunc (stg.cogs_date),
2040 stg.source,
2041 stg.turns_cogs_flag,
2042 stg.internal_flag,
2043 sysdate,
2044 sysdate,
2045 g_user_id,
2046 g_user_id,
2047 g_login_id,
2048 g_program_id,
2049 g_program_login_id,
2050 g_program_application_id,
2051 g_request_id
2052 FROM opi_dbi_cogs_fstg stg,
2053 opi_dbi_cogsf_conv_rates rate
2054 WHERE stg.organization_id = rate.organization_id
2055 AND trunc (stg.cogs_date) = rate.transaction_date
2056 GROUP BY
2057 stg.inventory_item_id,
2058 stg.organization_id,
2059 stg.order_line_id,
2060 stg.top_model_line_id,
2061 stg.top_model_item_id,
2062 stg.top_model_item_uom,
2063 stg.top_model_org_id,
2064 stg.customer_id,
2065 stg.cogs_date,
2066 stg.source,
2067 stg.turns_cogs_flag,
2068 stg.internal_flag;
2069 l_row_count := sql%rowcount;
2070
2071 bis_collection_utilities.put_line('Loaded ' || l_row_count || ' rows into opi_dbi_cogs_f.');
2072
2073 -- Report etl success
2074 l_stmt_num := 100;
2075
2076 opi_dbi_bounds_pkg.set_load_successful(p_etl_type => 'COGS', p_load_type => 'INIT');
2077
2078 COMMIT;
2079
2080 -- if uncosted transactions were found, return a warning.
2081 l_stmt_num :=110;
2082 IF (l_bounds_warning) THEN
2083 --{
2084 bis_collection_utilities.put_line('COGS Initial Load found uncosted transactions.');
2085 retcode := g_WARNING;
2086 errbuf := 'COGS Initial Load Found Uncosted Transactions. ';
2087 --}
2088 END IF;
2089
2090
2091 -- Common clean up
2092 l_stmt_num := 120;
2093
2094 execute immediate 'truncate table ' || l_opi_schema
2095 || '.opi_dbi_cogsf_conv_rates ';
2096
2097 execute immediate 'truncate table ' || l_opi_schema
2098 || '.opi_dbi_cogs_fstg ';
2099
2100 bis_collection_utilities.wrapup(p_status => TRUE,
2101 p_count => l_row_count,
2102 p_message => 'successful in initial_load_cogs.');
2103 --}
2104 ELSE
2105 --{
2106 rollback;
2107 retcode := g_error ;
2108 errbuf := 'Please check log file for details.';
2109 bis_collection_utilities.wrapup(p_status => FALSE,
2110 p_count => 0,
2111 p_message => 'failed in initial_load_cogs.');
2112 --}
2113 END IF;
2114
2115 bis_collection_utilities.put_line('Exit initial_load_cogs() '||
2116 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
2117
2118 EXCEPTION WHEN OTHERS THEN
2119
2120 errbuf:= Sqlerrm;
2121 retcode:= SQLCODE;
2122
2123 ROLLBACK;
2124 bis_collection_utilities.put_line('Error in initial_load_cogs() at ' || l_stmt_num);
2125 bis_collection_utilities.wrapup(p_status => FALSE,
2126 p_count => 0,
2127 p_message => 'failed in initial_load_cogs.');
2128
2129 RAISE_APPLICATION_ERROR(-20000,errbuf);
2130
2131 END initial_load_cogs;
2132
2133
2134
2135 /*======================================================================
2136 This is the wrapper procedure for COGS incremental load which extracts
2137 data for discrete and process organizations.
2138
2139 Parameters:
2140 - errbuf: error buffer
2141 - retcode: return code
2142 =======================================================================*/
2143
2144 PROCEDURE incremental_load_cogs ( errbuf IN OUT NOCOPY VARCHAR2,
2145 retcode IN OUT NOCOPY VARCHAR2 ) IS
2146
2147 -- Declaration
2148 l_stmt_num NUMBER;
2149 l_row_count NUMBER;
2150 l_error_flag BOOLEAN;
2151 l_bounds_warning BOOLEAN;
2152
2153 l_opi_schema VARCHAR2(30);
2154 l_status VARCHAR2(30);
2155 l_industry VARCHAR2(30);
2156
2157 schema_info_not_found EXCEPTION;
2158 BEGIN
2159
2160 bis_collection_utilities.put_line('Enter incremental_load_cogs() '||
2161 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
2162
2163 -- Initialization
2164 l_error_flag := false;
2165 retcode :=0;
2166
2167 bis_collection_utilities.put_line('Incrmental Load COGS starts at '
2168 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
2169
2170 IF bis_collection_utilities.setup( 'OPI_DBI_COGS_F' ) = false THEN
2171 --{
2172 RAISE_APPLICATION_ERROR(-20000, errbuf);
2173 --}
2174 END IF;
2175
2176 -- Performance tuning change
2177 execute immediate 'alter session set hash_area_size=100000000 ';
2178 execute immediate 'alter session set sort_area_size=100000000 ';
2179
2180 -- Setup globals
2181 l_stmt_num := 10;
2182
2183 check_setup_globals(errbuf => errbuf, retcode => retcode);
2184
2185 IF retcode <> 0 THEN
2186 --{
2187 RETURN ;
2188 --}
2189 END IF;
2190
2191 -- Common clean up
2192 l_stmt_num := 20;
2193 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema) THEN
2194 --{
2195 execute immediate 'truncate table ' || l_opi_schema
2196 || '.opi_dbi_cogsf_conv_rates ';
2197
2198 execute immediate 'truncate table ' || l_opi_schema
2199 || '.opi_dbi_cogs_fstg ';
2200 --}
2201 ELSE
2202 --{
2203 RAISE schema_info_not_found;
2204 --}
2205 END IF;
2206
2207
2208 -- Get start/end bounds for discrete and process
2209 l_stmt_num := 30;
2210
2211 opi_dbi_bounds_pkg.maintain_opi_dbi_logs(p_etl_type => 'COGS', p_load_type => 'INCR');
2212
2213 IF retcode <> 0 THEN
2214 --{
2215 l_error_flag := TRUE;
2216 --}
2217 END IF;
2218
2219
2220
2221 -- check if some bounds are uncosted before calling any other
2222 -- procedure that can wipe out the stop reason code
2223 l_stmt_num:= 40;
2224 l_bounds_warning := opi_dbi_bounds_pkg.bounds_uncosted(p_etl_type => 'COGS',
2225 p_load_type => 'INCR');
2226
2227 -- Print the discrete org collection bounds
2228 l_stmt_num := 50;
2229 opi_dbi_bounds_pkg.print_opi_org_bounds(p_etl_type => 'COGS', p_load_type => 'INCR');
2230
2231
2232 -- Load discrete cogs into staging table
2233 l_stmt_num :=60;
2234
2235 bis_collection_utilities.put_line('Load discrete cogs into stg '
2236 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
2237
2238 incremental_opi_cogs( errbuf => errbuf, retcode => retcode);
2239
2240 IF retcode <> 0 THEN
2241 --{
2242 l_error_flag := TRUE;
2243 --}
2244 END IF;
2245
2246
2247 -- Load process cogs into staging table
2248 l_stmt_num := 70;
2249
2250 bis_collection_utilities.put_line('Load process cogs into stg '
2251 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
2252
2253 incremental_load_opm_cogs( errbuf => errbuf, retcode => retcode);
2254
2255 IF retcode <> 0 THEN
2256 --{
2257 l_error_flag := TRUE;
2258 --}
2259 END IF;
2260
2261
2262 -- Get conversion rates
2263 -- For improve perf, need to commit in stg/conversion rate tables
2264 -- and gather statistics
2265
2266 l_stmt_num := 80;
2267 gather_stats(p_table_name => 'OPI_DBI_COGS_FSTG');
2268
2269 get_cogs_conversion_rate_incr( errbuf => errbuf, retcode => retcode );
2270
2271 commit;
2272
2273 gather_stats(p_table_name => 'OPI_DBI_COGSF_CONV_RATES');
2274
2275 IF retcode <> 0 THEN
2276 --{
2277 l_error_flag := TRUE;
2278 --}
2279 END IF;
2280
2281
2282 IF l_error_flag <> TRUE THEN
2283 --{
2284 l_stmt_num := 90;
2285
2286 -- Load data from staging table into fact table
2287
2288 MERGE /*+ index(m, OPI_DBI_COGS_F_N2) */ INTO opi_dbi_cogs_f m
2289 USING (
2290 SELECT
2291 stg.inventory_item_id,
2292 stg.organization_id,
2293 stg.order_line_id,
2294 stg.top_model_line_id,
2295 stg.top_model_item_id,
2296 stg.top_model_item_uom,
2297 stg.top_model_org_id,
2298 stg.customer_id,
2299 sum(stg.cogs_val_b_draft) cogs_val_b_draft,
2300 sum(stg.cogs_val_b) cogs_val_b,
2301 trunc (stg.cogs_date) cogs_date,
2302 stg.source,
2303 stg.turns_cogs_flag,
2304 stg.internal_flag,
2305 min(rate.conversion_rate) conversion_rate,
2306 min(rate.sec_conversion_rate) sec_conversion_rate
2307 FROM opi_dbi_cogs_fstg stg,
2308 opi_dbi_cogsf_conv_rates rate
2309 WHERE stg.organization_id = rate.organization_id
2310 AND trunc (stg.cogs_date) = rate.transaction_date
2311 GROUP BY
2312 stg.inventory_item_id,
2313 stg.organization_id,
2314 stg.order_line_id,
2315 stg.top_model_line_id,
2316 stg.top_model_item_id,
2317 stg.top_model_item_uom,
2318 stg.top_model_org_id,
2319 stg.customer_id,
2320 trunc (stg.cogs_date),
2321 stg.source,
2322 stg.turns_cogs_flag,
2323 stg.internal_flag
2324 ) rstg
2325 ON (m.order_line_id = rstg.order_line_id )
2326 WHEN matched THEN UPDATE SET
2327 m.cogs_val_b_draft = rstg.cogs_val_b_draft,
2328 m.cogs_val_b = m.cogs_val_b - m.cogs_val_b_draft + rstg.cogs_val_b_draft + rstg.cogs_val_b,
2329 m.cogs_val_g = (m.cogs_val_b - m.cogs_val_b_draft + rstg.cogs_val_b_draft + rstg.cogs_val_b)
2330 * rstg.conversion_rate,
2331 m.cogs_val_sg = (m.cogs_val_b - m.cogs_val_b_draft + rstg.cogs_val_b_draft + rstg.cogs_val_b)
2332 * rstg.sec_conversion_rate,
2333 m.cogs_date = rstg.cogs_date,
2334 m.last_update_date = sysdate,
2335 m.last_updated_by = g_user_id,
2336 m.last_updated_login = g_login_id
2337 WHEN NOT matched THEN
2338 INSERT (m.inventory_item_id,
2339 m.organization_id,
2340 m.order_line_id,
2341 m.top_model_line_id,
2342 m.top_model_item_id,
2343 m.top_model_item_uom,
2344 m.top_model_org_id,
2345 m.customer_id,
2346 m.cogs_val_b_draft,
2347 m.cogs_val_b,
2348 m.cogs_val_g,
2349 m.cogs_val_sg,
2350 m.cogs_date,
2351 m.source,
2352 m.turns_cogs_flag,
2353 m.internal_flag,
2354 m.creation_date,
2355 m.last_update_date,
2356 m.created_by,
2357 m.last_updated_by,
2358 m.last_updated_login,
2359 m.program_id,
2360 m.program_login_id,
2361 program_application_id,
2362 request_id )
2363 VALUES (rstg.inventory_item_id,
2364 rstg.organization_id,
2365 rstg.order_line_id,
2366 rstg.top_model_line_id,
2367 rstg.top_model_item_id,
2368 rstg.top_model_item_uom,
2369 rstg.top_model_org_id,
2370 rstg.customer_id,
2371 rstg.cogs_val_b_draft,
2372 rstg.cogs_val_b_draft + rstg.cogs_val_b,
2373 (rstg.cogs_val_b_draft + rstg.cogs_val_b) * rstg.conversion_rate,
2374 (rstg.cogs_val_b_draft + rstg.cogs_val_b) * rstg.sec_conversion_rate,
2375 rstg.cogs_date,
2376 rstg.source,
2377 rstg.turns_cogs_flag,
2378 rstg.internal_flag,
2379 sysdate,
2380 sysdate,
2381 g_user_id,
2382 g_user_id,
2383 g_login_id,
2384 g_program_id,
2385 g_program_login_id,
2386 g_program_application_id,
2387 g_request_id );
2388
2389
2390 l_row_count := sql%rowcount;
2391
2392 bis_collection_utilities.put_line('Loaded ' || l_row_count || ' rows into opi_dbi_cogs_f.');
2393 -- Report etl success
2394 l_stmt_num := 100;
2395
2396 opi_dbi_bounds_pkg.set_load_successful(p_etl_type => 'COGS', p_load_type => 'INCR');
2397
2398 COMMIT;
2399
2400 -- if uncosted transactions were found, return a warning.
2401 l_stmt_num := 110;
2402 IF (l_bounds_warning) THEN
2403 --{
2404 bis_collection_utilities.put_line('COGS Incremental Load found uncosted transactions.');
2405 retcode := g_WARNING;
2406 errbuf := ' COGS Incremental Load Found Uncosted Transactions. ';
2407 --}
2408 END IF;
2409
2410 -- common clean up
2411 l_stmt_num := 110;
2412
2413 execute immediate 'truncate table ' || l_opi_schema
2414 || '.opi_dbi_cogsf_conv_rates ';
2415
2416 execute immediate 'truncate table ' || l_opi_schema
2417 || '.opi_dbi_cogs_fstg ';
2418
2419 bis_collection_utilities.wrapup( p_status => TRUE,
2420 p_count => l_row_count,
2421 p_message => 'successful in incremental_load_cogs.');
2422 --}
2423 ELSE
2424 --{
2425 rollback;
2426 retcode := g_error ;
2427 errbuf := 'Please check log file for details.';
2428 bis_collection_utilities.wrapup(p_status => FALSE,
2429 p_count => 0,
2430 p_message => 'failed in incremental_load_cogs.'
2431 );
2432 --}
2433 END IF;
2434
2435 bis_collection_utilities.put_line('Exit incremental_load_cogs() '||
2436 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
2437
2438 EXCEPTION WHEN OTHERS THEN
2439 --{
2440 errbuf:= Sqlerrm;
2441 retcode:= SQLCODE;
2442
2443 ROLLBACK;
2444 bis_collection_utilities.put_line('Error in incremental_load_cogs() at ' || l_stmt_num);
2445 bis_collection_utilities.wrapup(p_status => FALSE,
2446 p_count => 0,
2447 p_message => 'failed in incremental_load_cogs.'
2448 );
2449
2450 RAISE_APPLICATION_ERROR(-20000,errbuf);
2451 --}
2452
2453 END incremental_load_cogs;
2454
2455 END opi_dbi_cogs_pkg;