[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_COGS_OPM_PKG
Source
1 PACKAGE BODY opi_dbi_cogs_opm_pkg AS
2 /* $Header: OPIDECOGSPB.pls 115.4 2004/06/22 06:01:39 sberi noship $ */
3
4 OPI_SOURCE CONSTANT NUMBER := 1;
5 OPM_SOURCE CONSTANT NUMBER := 2;
6
7 INCLUDE_FOR_TURNS CONSTANT NUMBER := 1;
8 DO_NOT_INCLUDE_FOR_TURNS CONSTANT NUMBER := 2;
9
10 g_cogs_error BOOLEAN := FALSE;
11 g_cogs_rate_error EXCEPTION;
12
13 g_login_id NUMBER;
14 g_user_id NUMBER;
15 g_sysdate date;
16 g_global_rate_type varchar2(15);
17 GLOBAL_CURRENCY_CODE varchar2(10);
18 g_opi_schema VARCHAR2(30);
19 g_opi_status VARCHAR2(30);
20 g_opi_industry VARCHAR2(30);
21
22 global_start_date DATE;
23
24 PROCEDURE check_setup_globals(errbuf IN OUT NOCOPY VARCHAR2 , retcode IN OUT NOCOPY VARCHAR2) IS
25
26 l_list dbms_sql.varchar2_table;
27
28 l_from_date DATE;
29 l_to_date DATE;
30 l_missing_day_flag BOOLEAN := FALSE;
31 l_err_num NUMBER;
32 l_err_msg VARCHAR2(255);
33 l_min_miss_date DATE;
34 l_max_miss_date DATE;
35
36 l_inception_date DATE;
37 BEGIN
38
39 retcode := 0;
40 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
41 l_list(2) := 'BIS_GLOBAL_START_DATE';
42
43 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
44 g_login_id := fnd_global.login_id;
45 g_user_id := fnd_global.user_id;
46 global_start_date := Trunc(bis_common_parameters.get_global_start_date);
47 global_currency_code := bis_common_parameters.get_currency_code;
48
49 if (g_global_rate_type is null)
50 then g_global_rate_type := bis_common_parameters.get_rate_type;
51 end if;
52
53 g_sysdate := sysdate;
54
55 if not fnd_installation.get_app_info (application_short_name => 'OPI',
56 status => g_opi_status,
57 industry => g_opi_industry,
58 oracle_schema => g_opi_schema)
59 then
60 RAISE_APPLICATION_ERROR(-20000, errbuf);
61 END IF;
62
63
64 SELECT NVL(MIN(from_date), global_start_date) INTO l_from_date
65 FROM (SELECT tst.gl_trans_date from_date
66 FROM opi_dbi_cogs_run_log l,
67 gl_subr_tst tst
68 WHERE l.source = OPM_SOURCE
69 AND tst.subledger_id = l.start_txn_id
70 UNION
71 SELECT tst.gl_trans_date from_date
72 FROM opi_dbi_cogs_run_log l,
73 gl_subr_led tst
74 WHERE l.source = OPM_SOURCE
75 AND tst.subledger_id = l.start_txn_id
76 );
77
78 l_to_date := sysdate;
79
80
81
82 -- check_missing_date
83
84 fii_time_api.check_missing_date( l_from_date, l_to_date, l_missing_day_flag,
85 l_min_miss_date, l_max_miss_date);
86
87 IF l_missing_day_flag THEN
88 retcode := 1;
89 errbuf := 'Please check log file for details. ';
90 BIS_COLLECTION_UTILITIES.PUT_LINE('There are missing dates in Time Dimension.');
91
92 BIS_COLLECTION_UTILITIES.PUT_LINE('The range is from ' || l_min_miss_date
93 ||' to ' || l_max_miss_date );
94 END IF;
95 ELSE
96 retcode := 1;
97 errbuf := 'Please check log file for details. ';
98 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Parameters are not setup.');
99
100 BIS_COLLECTION_UTILITIES.PUT_LINE('Please check that the profile options: BIS_PRIMARY_CURRENCY_CODE and BIS_GLOBAL_START_DATE are setup.');
101
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 retcode := 1;
107 l_err_num := SQLCODE;
108 l_err_msg := 'ERROR in OPI_DBI_OPM_COGS_PKG.CHECK_SETUP_GLOBALS '
109 || substr(SQLERRM, 1,200);
110
111 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
112 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
113
114 END check_setup_globals;
115
116 -- return 0 -- normal
117 -- return -1 -- missing rate found
118
119
120
121 PROCEDURE refresh_opm_subl_org_cogs(
122 p_last_id IN NUMBER,
123 p_newest_id IN NUMBER,
124 x_status OUT NOCOPY NUMBER,
125 x_msg OUT NOCOPY VARCHAR2 ) IS
126
127 l_stmt NUMBER := 0;
128
129
130 BEGIN
131 bis_collection_utilities.put_line('Load incremental OPM Test Subr cogs into stg '
132 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
133
134 -- Regular sales order
135 insert into opi_dbi_cogs_fstg
136 (
137 INVENTORY_ITEM_ID
138 ,ORGANIZATION_ID
139 ,ORDER_LINE_ID
140 ,TOP_MODEL_LINE_ID
141 ,TOP_MODEL_ITEM_ID
142 ,TOP_MODEL_ITEM_UOM
143 ,TOP_MODEL_ORG_ID
144 ,CUSTOMER_ID
145 ,COGS_VAL_B
146 ,COGS_DATE
147 ,SOURCE
148 ,TURNS_COGS_FLAG
149 )
150 select
151 INVENTORY_ITEM_ID
152 ,ORGANIZATION_ID
153 ,ORDER_LINE_ID
154 ,ORDER_LINE_ID
155 ,INVENTORY_ITEM_ID
156 ,TOP_MODEL_ITEM_UOM
157 ,ORGANIZATION_ID
158 ,SOLD_TO_ORG_ID
159 ,sum(COGS_VAL_B)
160 ,max(COGS_DATE)
161 ,SOURCE
162 ,TURNS_COGS_FLAG
163 from
164 ( select /*+ leading(whse) use_nl(msi,cust_acct) */
165 lines.inventory_item_id INVENTORY_ITEM_ID,
166 whse.mtl_organization_id ORGANIZATION_ID,
167 tran.oe_order_line_id ORDER_LINE_ID,
168 msi.primary_uom_code TOP_MODEL_ITEM_UOM,
169 tran.cogs_val_b COGS_VAL_B,
170 trunc(tran.gl_trans_date) COGS_DATE,
171 nvl(cust_acct.party_id, -1) SOLD_TO_ORG_ID,
172 Decode(lines.source_type_code, 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,INCLUDE_FOR_TURNS) TURNS_COGS_FLAG,
173 OPM_SOURCE SOURCE
174 from oe_order_lines_all lines,
175 hz_cust_accounts cust_acct,
176 ic_whse_mst whse,
177 mtl_system_items_b msi,
178 (select /*+ leading(tst) index(tran,IC_TRAN_PNDI2) use_nl(tran) */ rcv.oe_order_line_id oe_order_line_id,
179 tran.line_id,
180 tran.orgn_code,
181 tran.whse_code,
182 tst.gl_trans_date,
183 avg(tst.cogs_val_b) COGS_VAL_B
184 from ic_tran_pnd tran,
185 rcv_transactions rcv,
186 (select /*+index(tst,gl_subr_tst_n2) */
187 tst.line_id, tst.doc_type, tst.gl_trans_date,
188 sum(tst.debit_credit_sign*tst.amount_base) COGS_VAL_B
189 from gl_subr_tst tst
190 where tst.doc_type = 'PORC'
191 and tst.acct_ttl_type = 5200
192 and tst.gl_trans_date >= global_start_date
193 group by tst.line_id, tst.doc_type, tst.gl_trans_date) tst
194 where tran.completed_ind = 1
195 and tran.gl_posted_ind = 0
196 and tran.line_id = rcv.transaction_id
197 and rcv.oe_order_line_id is NOT NULL
198 and tran.doc_type = tst.doc_type
199 and tran.line_id = tst.line_id
200 group by rcv.oe_order_line_id, tran.line_id, tran.orgn_code, tran.whse_code, tst.gl_trans_date
201 union all
202 select /*+ leading(tst) index(tran,IC_TRAN_PNDI2) use_nl(tran) */ tran.line_id oe_order_line_id,
203 tran.line_id,
204 tran.orgn_code,
205 tran.whse_code,
206 tst.gl_trans_date,
207 avg(tst.cogs_val_b) COGS_VAL_B
208 from ic_tran_pnd tran,
209 (select /*+index(tst,gl_subr_tst_n2) */
210 tst.line_id, tst.doc_type, tst.gl_trans_date,
211 sum(tst.debit_credit_sign*tst.amount_base) COGS_VAL_B
212 from gl_subr_tst tst
213 where tst.doc_type = 'OMSO'
214 and tst.acct_ttl_type = 5200
215 and tst.gl_trans_date >= global_start_date
216 group by tst.line_id, tst.doc_type, tst.gl_trans_date) tst
217 where tran.completed_ind = 1
218 and tran.gl_posted_ind = 0
219 and tran.doc_type = tst.doc_type
220 and tran.line_id = tst.line_id
221 group by tran.line_id, tran.line_id, tran.orgn_code, tran.whse_code, tst.gl_trans_date) tran
222 where lines.line_id = tran.oe_order_line_id
223 and lines.sold_to_org_id = cust_acct.cust_account_id(+)
224 and whse.whse_code = tran.whse_code
225 and msi.inventory_item_id=lines.inventory_item_id
226 and msi.organization_id=lines.ship_from_org_id)
227 group by
228 INVENTORY_ITEM_ID
229 ,ORGANIZATION_ID
230 ,TOP_MODEL_ITEM_UOM
231 ,SOLD_TO_ORG_ID
232 ,ORDER_LINE_ID
233 ,TURNS_COGS_FLAG
234 ,SOURCE ;
235
236 commit;
237 bis_collection_utilities.put_line('Load incremental OPM Final Subr cogs into stg '
238 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
239
240 insert into opi_dbi_cogs_fstg
241 (
242 INVENTORY_ITEM_ID
243 ,ORGANIZATION_ID
244 ,ORDER_LINE_ID
245 ,TOP_MODEL_LINE_ID
246 ,TOP_MODEL_ITEM_ID
247 ,TOP_MODEL_ITEM_UOM
248 ,TOP_MODEL_ORG_ID
249 ,CUSTOMER_ID
250 ,COGS_VAL_B
251 ,COGS_DATE
252 ,SOURCE
253 ,TURNS_COGS_FLAG
254 )
255 select
256 INVENTORY_ITEM_ID
257 ,ORGANIZATION_ID
258 ,ORDER_LINE_ID
259 ,ORDER_LINE_ID
260 ,INVENTORY_ITEM_ID
261 ,TOP_MODEL_ITEM_UOM
262 ,ORGANIZATION_ID
263 ,SOLD_TO_ORG_ID
264 ,sum(COGS_VAL_B)
265 ,max(COGS_DATE)
266 ,SOURCE
267 ,TURNS_COGS_FLAG
268 from
269 ( select /*+ index(cust_acct, HZ_CUST_ACCOUNTS_U1) use_nl(cust_acct) */
270 lines.inventory_item_id INVENTORY_ITEM_ID,
271 whse.mtl_organization_id ORGANIZATION_ID,
272 tran.oe_order_line_id ORDER_LINE_ID,
273 msi.primary_uom_code TOP_MODEL_ITEM_UOM,
274 tran.cogs_val_b COGS_VAL_B,
275 trunc(tran.gl_trans_date) COGS_DATE,
276 nvl(cust_acct.party_id, -1) SOLD_TO_ORG_ID,
277 Decode(lines.source_type_code, 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,INCLUDE_FOR_TURNS) TURNS_COGS_FLAG,
278 OPM_SOURCE SOURCE
279 from oe_order_lines_all lines,
280 hz_cust_accounts cust_acct,
281 ic_whse_mst whse,
282 mtl_system_items_b msi,
283 (select
284 rcv.oe_order_line_id oe_order_line_id,
285 tran.line_id,
286 tran.orgn_code,
287 tran.whse_code,
288 tst.gl_trans_date,
289 avg(tst.cogs_val_b) COGS_VAL_B
290 from ic_tran_pnd tran,
291 rcv_transactions rcv,
292 (select /*+ index(tst,GL_SUBR_LED_PK) */
293 tst.line_id, tst.doc_type, tst.gl_trans_date,
294 sum(tst.debit_credit_sign*tst.amount_base) COGS_VAL_B
295 from gl_subr_led tst
296 where tst.doc_type = 'PORC'
297 and tst.acct_ttl_type = 5200
298 and tst.subledger_id between p_last_id and p_newest_id
299 and tst.GL_TRANS_DATE >= global_start_date
300 group by tst.line_id, tst.doc_type, tst.gl_trans_date
301 ) tst
302 where tran.completed_ind = 1
303 and tran.gl_posted_ind = 1
304 and tran.line_id = rcv.transaction_id
305 and rcv.oe_order_line_id is NOT NULL
306 and tran.doc_type = tst.doc_type
307 and tran.line_id = tst.line_id
308 group by rcv.oe_order_line_id, tran.line_id, tran.orgn_code, tran.whse_code, tst.gl_trans_date
309 union all
310 select
311 tran.line_id oe_order_line_id,
312 tran.line_id,
313 tran.orgn_code,
314 tran.whse_code,
315 tst.gl_trans_date,
316 avg(tst.cogs_val_b) COGS_VAL_B
317 from ic_tran_pnd tran,
318 (select /*+ index(tst,GL_SUBR_LED_PK) */
319 tst.line_id, tst.doc_type, tst.gl_trans_date,
320 sum(tst.debit_credit_sign*tst.amount_base) COGS_VAL_B
321 from gl_subr_led tst
322 where tst.doc_type = 'OMSO'
323 and tst.acct_ttl_type = 5200
324 and tst.subledger_id between p_last_id and p_newest_id
325 and tst.GL_TRANS_DATE >= global_start_date
326 group by tst.line_id, tst.doc_type, tst.gl_trans_date
327 ) tst
328 where tran.completed_ind = 1
329 and tran.gl_posted_ind = 1
330 and tran.doc_type = tst.doc_type
331 and tran.line_id = tst.line_id
332 group by tran.line_id, tran.line_id, tran.orgn_code, tran.whse_code, tst.gl_trans_date
333 ) tran
334 where lines.line_id = tran.oe_order_line_id
335 and lines.sold_to_org_id = cust_acct.cust_account_id(+)
336 and whse.whse_code = tran.whse_code
337 and msi.inventory_item_id=lines.inventory_item_id
338 and msi.organization_id=lines.ship_from_org_id
339 )
340 group by
341 INVENTORY_ITEM_ID
342 ,ORGANIZATION_ID
343 ,TOP_MODEL_ITEM_UOM
344 ,SOLD_TO_ORG_ID
345 ,ORDER_LINE_ID
346 ,TURNS_COGS_FLAG
347 ,SOURCE ;
348
349 l_stmt := 1;
350
351 fnd_stats.gather_table_stats( ownname => g_opi_schema,
352 tabname => 'OPI_DBI_COGS_FSTG',
353 percent => 10);
354
355
356
357 x_status := 1; -- complete successfully
358 x_msg := NULL;
359 EXCEPTION WHEN OTHERS THEN
360 ROLLBACK;
361
362 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Refresh_opm_subl_org_cogs at statement ' || l_stmt);
363 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
364 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Refresh_opm_subl_org_cogs at statement ' || l_stmt);
365 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
366 x_status := 0; -- error
367 x_msg := Sqlerrm;
368
369 END refresh_opm_subl_org_cogs;
370
371
372 PROCEDURE initial_opm_subl_org_cogs(
373 p_last_id IN NUMBER,
374 p_newest_id IN NUMBER,
375 x_status OUT NOCOPY NUMBER,
376 x_msg OUT NOCOPY VARCHAR2,
377 p_degree IN NUMBER ) IS
378
379 l_stmt NUMBER := 0;
380
381 BEGIN
382 bis_collection_utilities.put_line('Load OPM Test Subr cogs into stg '
383 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
384
385
386 -- Regular sales order
387 insert /*+ append parallel(opi_dbi_cogs_fstg) */ into opi_dbi_cogs_fstg
388 (
389 INVENTORY_ITEM_ID
390 ,ORGANIZATION_ID
391 ,ORDER_LINE_ID
392 ,TOP_MODEL_LINE_ID
393 ,TOP_MODEL_ITEM_ID
394 ,TOP_MODEL_ITEM_UOM
395 ,TOP_MODEL_ORG_ID
396 ,CUSTOMER_ID
397 ,COGS_VAL_B
398 ,COGS_DATE
399 ,SOURCE
400 ,TURNS_COGS_FLAG
401 )
402 select
403 INVENTORY_ITEM_ID
404 ,ORGANIZATION_ID
405 ,ORDER_LINE_ID
406 ,ORDER_LINE_ID
407 ,INVENTORY_ITEM_ID
408 ,TOP_MODEL_ITEM_UOM
409 ,ORGANIZATION_ID
410 ,SOLD_TO_ORG_ID
411 ,COGS_VAL_B
412 ,COGS_DATE
413 ,SOURCE
414 ,TURNS_COGS_FLAG
415 from
416 (
417 select
418 INVENTORY_ITEM_ID
419 ,ORGANIZATION_ID
420 ,ORDER_LINE_ID
421 ,TOP_MODEL_ITEM_UOM
422 ,SOLD_TO_ORG_ID
423 ,sum(COGS_VAL_B) COGS_VAL_B
424 ,max(COGS_DATE) COGS_DATE
425 ,TURNS_COGS_FLAG
426 ,SOURCE
427 from
428 ( select /*+ use_hash(whse,lines,cust_acct,msi) parallel(tst) parallel(lines) parallel(cust_acct) parallel(msi) parallel(whse) */
429 lines.inventory_item_id INVENTORY_ITEM_ID ,
430 whse.mtl_organization_id ORGANIZATION_ID ,
431 tran.oe_order_line_id ORDER_LINE_ID ,
432 msi.primary_uom_code TOP_MODEL_ITEM_UOM ,
433 tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
434 trunc(GL_TRANS_DATE) COGS_DATE ,
435 nvl(cust_acct.party_id, -1) SOLD_TO_ORG_ID ,
436 Decode(lines.source_type_code, 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,INCLUDE_FOR_TURNS ) TURNS_COGS_FLAG ,
437 OPM_SOURCE SOURCE
438 from gl_subr_tst tst,
439 oe_order_lines_all lines,
440 hz_cust_accounts cust_acct,
441 ic_whse_mst whse,
442 mtl_system_items_b msi,
443 (
444 select /*+ full(tran) full(rcv) use_hash(tran) parallel(tran) parallel(rcv) */
445 tran.doc_type,
446 rcv.oe_order_line_id oe_order_line_id,
447 tran.line_id,
448 tran.orgn_code,
449 tran.whse_code
450 from ic_tran_pnd tran,
451 rcv_transactions rcv
452 where doc_type = 'PORC'
453 and completed_ind = 1
454 and gl_posted_ind = 0
455 and tran.line_id = rcv.transaction_id
456 and rcv.oe_order_line_id is NOT NULL
457 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
458 union all
459 select /*+ parallel(tran) */
460 tran.doc_type,
461 tran.line_id oe_order_line_id,
462 tran.line_id,
463 tran.orgn_code,
464 tran.whse_code
465 from ic_tran_pnd tran
466 where doc_type = 'OMSO'
467 and completed_ind = 1
468 and gl_posted_ind = 0
469 group by doc_type, line_id, line_id, orgn_code, whse_code
470 ) tran
471 where tst.doc_type in ( 'OMSO', 'PORC' )
472 and tst.acct_ttl_type = 5200
473 and lines.line_id = tran.oe_order_line_id
474 and lines.sold_to_org_id = cust_acct.cust_account_id(+)
475 and tran.doc_type = tst.doc_type
476 and tran.line_id = tst.line_id
477 and whse.whse_code = tran.whse_code
478 and msi.inventory_item_id=lines.inventory_item_id
479 and msi.organization_id=lines.ship_from_org_id
480 and tst.GL_TRANS_DATE >= global_start_date
481 ) A
482 group by
483 INVENTORY_ITEM_ID
484 ,ORGANIZATION_ID
485 ,TOP_MODEL_ITEM_UOM
486 ,SOLD_TO_ORG_ID
487 ,ORDER_LINE_ID
488 ,TURNS_COGS_FLAG
489 ,SOURCE
490 )
491 ;
492 bis_collection_utilities.put_line('Load OPM Final Subr cogs into stg '
493 || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
494
495 commit;
496 insert /*+ append parallel(opi_dbi_cogs_fstg) */ into opi_dbi_cogs_fstg
497 (
498 INVENTORY_ITEM_ID
499 ,ORGANIZATION_ID
500 ,ORDER_LINE_ID
501 ,TOP_MODEL_LINE_ID
502 ,TOP_MODEL_ITEM_ID
503 ,TOP_MODEL_ITEM_UOM
504 ,TOP_MODEL_ORG_ID
505 ,CUSTOMER_ID
506 ,COGS_VAL_B
507 ,COGS_DATE
508 ,SOURCE
509 ,TURNS_COGS_FLAG
510 )
511 (select
512 INVENTORY_ITEM_ID,
513 ORGANIZATION_ID,
514 ORDER_LINE_ID,
515 ORDER_LINE_ID,
516 INVENTORY_ITEM_ID,
517 TOP_MODEL_ITEM_UOM,
518 ORGANIZATION_ID,
519 SOLD_TO_ORG_ID,
520 sum(COGS_VAL_B),
521 max(COGS_DATE),
522 SOURCE,
523 TURNS_COGS_FLAG
524 from
525 ( select
526 /*+ full(tst) use_hash(tst, lines,cust_acct,msi,whse) parallel(tst) parallel(lines) parallel(cust_acct) parallel(msi) parallel(whse) */
527 lines.inventory_item_id INVENTORY_ITEM_ID,
528 whse.mtl_organization_id ORGANIZATION_ID ,
529 tran.oe_order_line_id ORDER_LINE_ID,
530 msi.primary_uom_code TOP_MODEL_ITEM_UOM ,
531 tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
532 trunc(GL_TRANS_DATE) COGS_DATE ,
533 nvl(cust_acct.party_id, -1) SOLD_TO_ORG_ID ,
534 Decode(lines.source_type_code, 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,INCLUDE_FOR_TURNS) TURNS_COGS_FLAG ,
535 OPM_SOURCE SOURCE
536 from gl_subr_led tst,
537 (select /*+ full(tran) full(rcv) use_hash(tran) parallel(tran) parallel(rcv) */
538 tran.doc_type,
539 rcv.oe_order_line_id oe_order_line_id,
540 tran.line_id,
541 tran.orgn_code,
542 tran.whse_code
543 from ic_tran_pnd tran,
544 rcv_transactions rcv
545 where doc_type = 'PORC'
546 and completed_ind = 1
547 and gl_posted_ind = 1
548 and tran.line_id = rcv.transaction_id
549 and rcv.oe_order_line_id is NOT NULL
550 group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
551 union all
552 select /*+ full(tran) parallel(tran) */
553 tran.doc_type,
554 tran.line_id oe_order_line_id,
555 tran.line_id,
556 tran.orgn_code,
557 tran.whse_code
558 from ic_tran_pnd tran
559 where doc_type = 'OMSO'
560 and completed_ind = 1
561 and gl_posted_ind = 1
562 group by doc_type, line_id, line_id, orgn_code, whse_code) tran,
563 oe_order_lines_all lines,
564 hz_cust_accounts cust_acct,
565 mtl_system_items_b msi,
566 ic_whse_mst whse
567 where tst.doc_type in ( 'OMSO', 'PORC' )
568 and tst.acct_ttl_type = 5200
569 and lines.line_id = tran.oe_order_line_id
570 and lines.sold_to_org_id = cust_acct.cust_account_id(+)
571 and tran.doc_type = tst.doc_type
572 and tran.line_id = tst.line_id
573 and whse.whse_code = tran.whse_code
574 and msi.inventory_item_id=lines.inventory_item_id
575 and msi.organization_id=lines.ship_from_org_id
576 and tst.subledger_id >= p_last_id and tst.subledger_id +0 <= p_newest_id
577 and tst.GL_TRANS_DATE >= global_start_date
578 )
579 group by
580 INVENTORY_ITEM_ID
581 ,ORGANIZATION_ID
582 ,TOP_MODEL_ITEM_UOM
583 ,SOLD_TO_ORG_ID
584 ,ORDER_LINE_ID
585 ,TURNS_COGS_FLAG
586 ,SOURCE );
587
588
589
590
591 l_stmt := 1;
592
593 fnd_stats.gather_table_stats( ownname => g_opi_schema,
594 tabname => 'OPI_DBI_COGS_FSTG',
595 percent => 10);
596
597 commit;
598
599
600 x_status := 1; -- complete successfully
601 x_msg := NULL;
602 EXCEPTION WHEN OTHERS THEN
603 ROLLBACK;
604
605 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Initial_opm_subl_org_cogs at statement ' || l_stmt);
606 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
607 BIS_COLLECTION_UTILITIES.PUT_LINE(' Error in Initial_opm_subl_org_cogs at statement ' || l_stmt);
608 BIS_COLLECTION_UTILITIES.PUT_LINE( Sqlerrm );
609 x_status := 0; -- error
610 x_msg := Sqlerrm;
611
612 END initial_opm_subl_org_cogs;
613
614
615 FUNCTION refresh_opm_cogs (errbuf in out NOCOPY varchar2,
616 retcode in out NOCOPY VARCHAR2
617 ) RETURN NUMBER IS
618
619 l_last_trx_id NUMBER :=0;
620 l_new_trx_id NUMBER :=0;
621
622 l_rows_in_batch NUMBER := 100000;
623 l_status NUMBER := 1;
624 l_msg VARCHAR2(4000);
625
626 l_batch_from_id NUMBER;
627 l_batch_to_id NUMBER;
628
629 l_empty_count NUMBER;
630 l_missing_rate_count NUMBER;
631 l_exception_count NUMBER;
632
633 l_opi_schema VARCHAR2(30);
634 l_industry VARCHAR2(30);
635 l_opi_status VARCHAR2(30);
636
637
638 x_row_count NUMBER := 0;
639
640 BEGIN
641
642 retcode := 0;
643
644 BIS_COLLECTION_UTILITIES.PUT_LINE('OPM COGS refresh started at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
645
646
647 -- 1. get subledger cogs
648
649 BEGIN
650 SELECT start_txn_id INTO l_last_trx_id
651 FROM opi_dbi_cogs_run_log
652 WHERE source = OPM_SOURCE;
653
654
655 SELECT NVL(MAX(subledger_id),l_last_trx_id)
656 INTO l_new_trx_id
657 from gl_subr_led led
658 where led.doc_type in ( 'OMSO', 'PORC')
659 and led.acct_ttl_type = 5200
660 AND led.gl_trans_date >= global_start_date
661 AND led.subledger_id >= l_last_trx_id;
662
663
664 EXCEPTION
665 WHEN no_data_found THEN
666
667 SELECT Nvl(MIN(subledger_id),0) - 1,
668 Nvl(MAX(subledger_id),0)
669 INTO l_last_trx_id,
670 l_new_trx_id
671 from gl_subr_led led
672 where led.doc_type in ( 'OMSO', 'PORC')
673 and led.acct_ttl_type = 5200
674 AND led.gl_trans_date >= global_start_date;
675
676 BIS_COLLECTION_UTILITIES.PUT_LINE('S, ' ||l_last_trx_id );
677 BIS_COLLECTION_UTILITIES.PUT_LINE ('Incremental Refresh chosen, but Initial Load may be faster' );
678 END;
679
680
681
682 BIS_COLLECTION_UTILITIES.PUT_LINE
683 ('Collecting OPM Subledger COGS for transaction ID range: ' || to_char(l_last_trx_id + 1) ||' - ' || l_new_trx_id);
684
685
686
687 BIS_COLLECTION_UTILITIES.PUT_LINE( ' Start at ' || To_char(Sysdate, 'hh24:mi:ss'));
688
689 l_batch_from_id := l_last_trx_id;
690 l_batch_to_id := l_new_trx_id;
691
692 BIS_COLLECTION_UTILITIES.PUT_LINE('batch_id ' || l_batch_from_id);
693
694 refresh_opm_subl_org_cogs(
695 l_batch_from_id + 1,
696 l_batch_to_id,
697 l_status,
698 l_msg );
699
700 merge INTO opi_dbi_cogs_run_log l
701 using ( SELECT NULL organization_id,
702 OPM_SOURCE extraction_type
703 FROM dual ) d
704 ON ( l.source = d.extraction_type )
705 WHEN matched THEN UPDATE SET
706 l.organization_id = NULL,
707 l.start_txn_id = l_batch_from_id,
708 l.next_start_txn_id = l_batch_to_id
709 WHEN NOT matched THEN
710 INSERT ( l.ORGANIZATION_ID
711 ,l.SOURCE
712 ,l.LAST_COLLECTION_DATE
713 ,l.INIT_TXN_ID
714 ,l.START_TXN_ID
715 ,l.NEXT_START_TXN_ID
716 ,l.STOP_REASON_CODE
717 ,l.LAST_TRANSACTION_DATE)
718 VALUES (d.organization_id,
719 OPM_SOURCE,
720 null,
721 Decode(l_status, 0, 0 ,l_batch_from_id),
722 Decode(l_status, 0, 0 ,l_batch_from_id),
723 Decode(l_status, 0, 0 ,l_batch_to_id) ,
724 null,
725 null);
726
727 COMMIT; -- commit per org
728
729 BIS_COLLECTION_UTILITIES.PUT_LINE(' Subledger COGS completed at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
730
731 RETURN x_row_count;
732 EXCEPTION WHEN OTHERS THEN
733 ROLLBACK;
734
735 BIS_COLLECTION_UTILITIES.PUT_LINE('Error in refresh_opm_cogs ' || Sqlerrm );
736 errbuf := Sqlerrm;
737 retcode := 1;
738 RETURN x_row_count;
739 END refresh_opm_cogs;
740
741
742 FUNCTION complete_refresh_opm_cogs (errbuf in out NOCOPY varchar2,
743 retcode in out NOCOPY VARCHAR2,
744 p_degree IN NUMBER ) RETURN NUMBER IS
745
746 l_last_trx_id NUMBER :=0;
747 l_new_trx_id NUMBER :=0;
748
749 l_rows_in_batch NUMBER := 100000;
750 l_status NUMBER := 1;
751 l_msg VARCHAR2(4000);
752
753 l_batch_from_id NUMBER;
754 l_batch_to_id NUMBER;
755
756 l_empty_count NUMBER;
757 l_missing_rate_count NUMBER;
758 l_exception_count NUMBER;
759
760 l_opi_schema VARCHAR2(30);
761 l_industry VARCHAR2(30);
762 l_opi_status VARCHAR2(30);
763
764
765 x_row_count NUMBER := 0;
766
767 BEGIN
768
769 retcode := 0;
770
771 BIS_COLLECTION_UTILITIES.PUT_LINE('OPM COGS refresh started at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
772
773
774 -- 1. get subledger cogs
775
776 SELECT Nvl(MIN(subledger_id),0),
777 Nvl(MAX(subledger_id),0)
778 INTO l_last_trx_id,
779 l_new_trx_id
780 from gl_subr_led tst
781 where tst.doc_type in ( 'OMSO', 'PORC' )
782 and tst.acct_ttl_type = 5200
783 AND tst.gl_trans_date >= global_start_date;
784
785 BIS_COLLECTION_UTILITIES.PUT_LINE('Initial Refresh' );
786
787
788 BIS_COLLECTION_UTILITIES.PUT_LINE
789 ('Collecting OPM Subledger COGS for transaction ID range: ' || l_last_trx_id ||' - ' || l_new_trx_id);
790
791
792 BIS_COLLECTION_UTILITIES.PUT_LINE( ' Start at ' || To_char(Sysdate, 'hh24:mi:ss'));
793
794 l_batch_from_id := l_last_trx_id;
795 l_batch_to_id := l_new_trx_id;
796
797 BIS_COLLECTION_UTILITIES.PUT_LINE('batch_id ' || l_batch_from_id);
798
799 initial_opm_subl_org_cogs(
800 l_batch_from_id,
801 l_batch_to_id,
802 l_status,
803 l_msg ,
804 p_degree);
805
806 merge INTO opi_dbi_cogs_run_log l
807 using ( SELECT NULL organization_id,
808 OPM_SOURCE extraction_type
809 FROM dual ) d
810 ON ( l.source = d.extraction_type )
811 WHEN matched THEN UPDATE SET
812 l.organization_id = NULL,
813 l.start_txn_id = l_batch_to_id,
814 l.next_start_txn_id = NULL
815 WHEN NOT matched THEN
816 INSERT ( l.ORGANIZATION_ID
817 ,l.SOURCE
818 ,l.LAST_COLLECTION_DATE
819 ,l.INIT_TXN_ID
820 ,l.START_TXN_ID
821 ,l.NEXT_START_TXN_ID
822 ,l.STOP_REASON_CODE
823 ,l.LAST_TRANSACTION_DATE)
824 VALUES (d.organization_id,
825 OPM_SOURCE,
826 null,
827 Decode(l_status, 0, 0 ,l_batch_from_id),
828 Decode(l_status, 0, 0 ,l_batch_to_id),
829 Decode(l_status, 0, 0 ,NULL) ,
830 null,
831 null);
832 COMMIT;
833
834 BIS_COLLECTION_UTILITIES.PUT_LINE(' Subledger COGS completed at ' || TO_CHAR(SYSDATE, 'hh24:mi:ss'));
835
836 RETURN x_row_count;
837 EXCEPTION WHEN OTHERS THEN
838 ROLLBACK;
839
840 BIS_COLLECTION_UTILITIES.PUT_LINE('Error in refresh_opm_cogs ' || Sqlerrm );
841 errbuf := Sqlerrm;
842 retcode := 1;
843 RETURN 0; --x_row_count;
844 END complete_refresh_opm_cogs;
845
846
847 PROCEDURE complete_refresh_OPM_margin(Errbuf in out NOCOPY VARCHAR2,
848 Retcode in out NOCOPY VARCHAR2,
849 p_degree IN NUMBER ) IS
850
851 l_opi_schema VARCHAR2(30);
852 l_status VARCHAR2(30);
853 l_industry VARCHAR2(30);
854 l_revenue_count NUMBER := 0;
855 l_cogs_count NUMBER := 0;
856 BEGIN
857
858 -- setup globals
859 check_setup_globals(errbuf, retcode);
860
861 IF retcode <> 0 THEN
862 retcode:= -1;
863
864 ELSE
865
866 l_cogs_count := complete_refresh_opm_cogs(errbuf, retcode, p_degree);
867
868 IF retcode <> 0 THEN
869 retcode:= -1;
870
871 END IF;
872 END IF;
873 RETURN;
874
875 EXCEPTION WHEN OTHERS THEN
876
877 Errbuf:= SQLCODE||':'||Sqlerrm;
878 retcode:= -1;
879
880 ROLLBACK;
881
882
883 END complete_refresh_OPM_margin;
884
885
886
887 PROCEDURE refresh_OPM_margin(Errbuf in out NOCOPY VARCHAR2,
888 Retcode in out NOCOPY VARCHAR2 ) IS
889 l_revenue_count NUMBER := 0;
890 l_cogs_count NUMBER := 0;
891 BEGIN
892 check_setup_globals(errbuf, retcode);
893
894 IF retcode <> 0 THEN
895 retcode:= -1;
896
897 ELSE
898 l_cogs_count := refresh_opm_cogs(errbuf, retcode);
899
900 IF retcode <> 0 THEN
901 retcode:= -1;
902
903 END IF;
904 END IF;
905 RETURN;
906 EXCEPTION WHEN OTHERS THEN
907 Errbuf:= SQLCODE||':'||Sqlerrm;
908 retcode:= -1;
909
910 ROLLBACK;
911
912
913 END refresh_OPM_margin;
914
915
916 END opi_dbi_cogs_opm_pkg;