1 Package Body OPI_DBI_INV_VALUE_OPM_INIT_PKG AS
2 /*$Header: OPIDIPIB.pls 120.1 2005/08/02 01:45:18 achandak noship $ */
3
4 g_user_id NUMBER := nvl(fnd_global.user_id, -1);
5 g_login_id NUMBER := nvl(fnd_global.login_id, -1);
6 g_inception_date DATE;
7 g_global_start_date DATE := SYSDATE;
8
9
10 g_opi_schema VARCHAR2(32);
11 g_opi_status VARCHAR2(32);
12 g_opi_industry VARCHAR2(32);
13 g_opi_appinfo BOOLEAN;
14
15
16 PROCEDURE Clean_OPM_Tables
17 IS
18 l_stmt_num NUMBER;
19 l_opi_schema VARCHAR2(30);
20 l_status VARCHAR2(30);
21 l_industry VARCHAR2(30);
22 l_err_num NUMBER;
23 l_err_msg VARCHAR2(255);
24 BEGIN
25
26 l_stmt_num := 10;
27 IF fnd_installation.get_app_info( 'OPI', l_status, l_industry, l_opi_schema)
28 THEN
29
30 bis_collection_utilities.put_line('Initializing Tables:');
31
32 l_stmt_num := 10;
33 /* Truncating Staging Tables */
34 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_INV_STG ';
35 bis_collection_utilities.put_line('... OPI_DBI_OPM_INV_STG');
36
37 l_stmt_num := 20;
38 /* Truncating Base Table */
39 DELETE FROM OPI_DBI_INV_VALUE_F WHERE source = 2;
40 bis_collection_utilities.put_line('... OPI_DBI_INV_VALUE_F');
41
42 l_stmt_num := 30;
43 /* Truncating Log Table */
44 DELETE FROM OPI_DBI_INV_VALUE_LOG log
45 WHERE type IN ('GSL','OID');
46 bis_collection_utilities.put_line('... OPI_DBI_INV_VALUE_LOG');
47
48 l_stmt_num := 40;
49 /* Truncating Conversion Rates Table */
50 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_CONVERSION_RATES ';
51 bis_collection_utilities.put_line('... OPI_DBI_OPM_CONVERSION_RATES');
52
53 l_stmt_num := 50;
54 /* Truncating OPM Inception Qtys */
55 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_INCEPTION_QTY ';
56 bis_collection_utilities.put_line('... OPI_DBI_OPM_INCEPTION_QTY');
57
58 l_stmt_num := 60;
59 /* Truncating OPM Current Permanent Subledger Rows */
60 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_INV_LED_CURRENT ';
61 bis_collection_utilities.put_line('... OPI_DBI_OPM_INV_LED_CURRENT');
62
63 l_stmt_num := 70;
64 /* Truncating OPM Current Test Subldger Rows */
65 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_INV_TST_CURRENT ';
66 bis_collection_utilities.put_line('... OPI_DBI_OPM_INV_TST_CURRENT');
67
68 l_stmt_num := 80;
69 /* Truncating OPM Prior Test Subledger Rows */
70 execute immediate 'truncate table ' || l_opi_schema || '.OPI_DBI_OPM_INV_TST_PRIOR ';
71 bis_collection_utilities.put_line('... OPI_DBI_OPM_INV_TST_PRIOR');
72
73 END IF;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 l_err_msg := 'OPI_DBI_INV_VALUE_OPM_INIT_PKG.Clean_OPM_Tables (Error at statement '
78 || to_char(l_stmt_num)
79 || '): '
80 || substr(SQLERRM, 1,200);
81
82 bis_collection_utilities.put_line('Error Message: ' || l_err_msg);
83
84 RAISE;
85
86 END Clean_OPM_Tables;
87
88
89 PROCEDURE Get_OPM_Inception_Date(l_min_inception_date OUT NOCOPY DATE)
90 IS
91 l_stmt_num NUMBER;
92
93 CURSOR inception_date_cursor IS
94 SELECT o.orgn_code co_code, log.transaction_date inception_date
95 FROM opi_dbi_inv_value_log log, sy_orgn_mst o
96 WHERE log.type= 'OID'
97 AND o.organization_id = log.organization_id
98 AND g_global_start_date > log.transaction_date;
99
100 BEGIN
101
102 l_stmt_num := 10;
103 SELECT BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE INTO g_global_start_date FROM DUAL;
104
105 l_stmt_num := 20;
106
107 /*
108 The following insert statement creates OPM Inception Balance rows in opi_dbi_inv_value_log, one for
109 each co_code represented in gl_subr_led. Each purge will be for a single company, so this is the
110 right level of granularity. If the global_start_date precedes the available data, it is important
111 that all of a company's rows be preserved. In such a case, if one company has less history than another,
112 then we need to know the shortenned history so that the correct costing date is used.
113 */
114 INSERT INTO opi_dbi_inv_value_log
115 (
116 organization_id,
117 transaction_id,
118 transaction_date,
119 type,
120 source,
121 creation_date,
122 last_update_date,
123 created_by,
124 last_updated_by,
125 last_update_login
126 )
127 SELECT
128 c.organization_id organization_id,
129 0 transaction_id,
130 TRUNC(MIN(led.gl_trans_date)) transaction_date,
131 'OID' type,
132 2 source,
133 SYSDATE creation_date,
134 SYSDATE last_update_date,
135 g_user_id created_by,
136 g_user_id last_updated_by,
137 g_login_id last_update_login
138 FROM
139 sy_orgn_mst c,
140 gl_subr_led led
141 WHERE
142 c.orgn_code = led.co_code
143 GROUP BY c.organization_id;
144
145 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' company inception date rows created.');
146
147 fnd_stats.gather_table_stats(
148 ownname => g_opi_schema,
149 tabname => 'OPI_DBI_INV_VALUE_LOG',
150 percent => 10);
151 /*
152 The following minimum inception date is calculated for two purposes:
153 (1) to assist in the quick determination of whether any data is clipped by the global_start_date
154 (2) to be returned by this procedure for passing to the daily activity collection, letting
155 it know that it is being called in an initial mode.
156 */
157 SELECT MIN(transaction_date) INTO l_min_inception_date
158 FROM opi_dbi_inv_value_log
159 WHERE type = 'OID';
160
161 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' l_min_inception_date values determined.');
162
163 IF g_global_start_date > l_min_inception_date
164 THEN
165 bis_collection_utilities.put_line('Warning: The Global Start Date (' || TO_CHAR(g_global_start_date) ||
166 ') is later than the earliest available transaction');
167 bis_collection_utilities.put_line('This means that you are not going to extract all ' ||
168 'of the historic data that exists in your database.');
169 bis_collection_utilities.put_line('This affects the following OPM companies:');
170 bis_collection_utilities.put_line(
171 RPAD('-',10,'-') || ' ' || RPAD('-',15,'-') || ' ' || RPAD('-',15,'-'));
172 bis_collection_utilities.put_line(
173 RPAD('Company',10) || ' ' || RPAD('Earliest Date', 15) || ' ' || 'Days Truncated');
174 bis_collection_utilities.put_line(
175 RPAD('-',10,'-') || ' ' || RPAD('-',15,'-') || ' ' || RPAD('-',15,'-'));
176
177 FOR id IN inception_date_cursor
178 LOOP
179 bis_collection_utilities.put_line(RPAD(id.co_code,11) ||
180 RPAD(TO_CHAR(id.inception_date),16) ||
181 TO_CHAR(ROUND(g_global_start_date - id.inception_date))
182 );
183 END LOOP;
184
185 UPDATE opi_dbi_inv_value_log
186 SET transaction_date = g_global_start_date
187 WHERE type = 'OID'
188 AND g_global_start_date > transaction_date;
189
190 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' inception dates shortened.');
191
192 END IF;
193
194 COMMIT;
195
196 EXCEPTION
197 WHEN OTHERS
198 THEN
199 bis_collection_utilities.put_line ('Get_OPM_Inception_Date: '|| sqlerrm);
200 RAISE;
201
202 END Get_OPM_Inception_Date;
203
204
205 PROCEDURE Get_OPM_Onhand_Balance(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
206 IS
207 l_stmt_num NUMBER;
208 l_row_count NUMBER;
209 l_err_num NUMBER;
210 l_err_msg VARCHAR2(255);
211 BEGIN
212
213 /* Insert Inception Balances Into its Staging table */
214 l_stmt_num := 10;
215
216 INSERT INTO opi_dbi_opm_inception_qty
217 (item_id, whse_code, type, quantity)
218 SELECT
219 item_id, whse_code, 1, SUM(onhand_qty) onhand_qty
220 FROM
221 (
222 SELECT
223 item_id, whse_code, loct_onhand onhand_qty
224 FROM
225 ic_loct_inv
226 UNION ALL
227 SELECT t.item_id, t.whse_code, -t.trans_qty
228 FROM
229 opi_dbi_inv_value_log sd,
230 sy_orgn_mst o,
231 ic_tran_pnd t
232 WHERE
233 sd.type = 'OID'
234 AND o.organization_id = sd.organization_id
235 AND t.co_code = o.orgn_code
236 AND t.completed_ind = 1
237 AND t.trans_date >= sd.transaction_date
238 UNION ALL
239 SELECT t.item_id, t.whse_code, -t.trans_qty
240 FROM
241 opi_dbi_inv_value_log sd,
242 sy_orgn_mst o,
243 ic_tran_cmp t
244 WHERE
245 sd.type = 'OID'
246 AND o.organization_id = sd.organization_id
247 AND t.co_code = o.orgn_code
248 AND t.trans_date >= sd.transaction_date
249 ) onh
250 GROUP BY item_id, whse_code
251 HAVING SUM(onhand_qty) <> 0;
252
253 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' Onhand Inception Quantity rows extracted.');
254
255 EXCEPTION
256 WHEN OTHERS THEN
257
258 l_err_num := SQLCODE;
259 l_err_msg := 'OPI_DBI_INV_VALUE_OPM_INIT_PKG.Get_OPM_Onhand_Balance ('
260 || to_char(l_stmt_num) || '): ' || substr(SQLERRM, 1,200);
261
262 bis_collection_utilities.put_line('OPI_DBI_INV_VALUE_OPM_INIT_PKG.Get_OPM_Onhand_Balance '
263 || '- Error at statement (' || to_char(l_stmt_num) || ')');
264 bis_collection_utilities.put_line('Error Number: ' || to_char(l_err_num));
265 bis_collection_utilities.put_line('Error Message: ' || l_err_msg);
266
267 RAISE_APPLICATION_ERROR(-20000, errbuf);
268
269 END Get_OPM_Onhand_Balance;
270
271
272 PROCEDURE Cost_Inception_Quantities
273 IS
274 BEGIN
275
276 INSERT INTO opi_pmi_cost_param_gtmp
277 (whse_code, orgn_code, item_id, trans_date)
278 SELECT
279 DISTINCT q.whse_code, w.orgn_code, q.item_id, id.transaction_date
280 FROM
281 opi_dbi_opm_inception_qty q,
282 opi_dbi_inv_value_log id,
283 ic_whse_mst w,
284 sy_orgn_mst o,
285 sy_orgn_mst c
286 WHERE
287 w.whse_code = q.whse_code
288 AND o.orgn_code = w.orgn_code
289 AND c.orgn_code = o.co_code
290 AND id.type = 'OID'
291 AND id.organization_id = c.organization_id;
292
293 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' cost parameter rows inserted.');
294
295 opi_pmi_cost.get_cost;
296
297 EXCEPTION
298 WHEN OTHERS
299 THEN
300 bis_collection_utilities.put_line ('Cost_Inception_Quantities: '|| sqlerrm);
301 RAISE;
302
303 END Cost_Inception_Quantities;
304
305
306 PROCEDURE Create_Inception_Balances
307 IS
308 l_row_count NUMBER;
309 BEGIN
310 INSERT INTO opi_dbi_opm_inv_stg
311 (
312 ORGANIZATION_ID,
313 INVENTORY_ITEM_ID,
314 /* csheu added the following line */
315 SUBINVENTORY_CODE,
316 TRANSACTION_DATE,
317 ONHAND_QTY,
318 INTRANSIT_QTY,
319 PRIMARY_UOM,
320 ONHAND_VALUE_B,
321 INTRANSIT_VALUE_B
322 )
323 SELECT
324 msi.organization_id,
325 msi.inventory_item_id,
326 /* csheu added the following line */
327 /* w.whse_code subinventory_code, */
328 /* cdaly replaced it with the following line */
329 '-1', /* Key for Subinventory named Unassigned */
330 c.trans_date,
331 SUM(DECODE(q.type, 1, q.quantity, 0)) onhand_qty,
332 SUM(DECODE(q.type, 2, q.quantity, 0)) intransit_qty,
333 msi.primary_uom_code,
334 SUM(DECODE(q.type, 1, q.quantity * c.total_cost, 0)) onhand_value_b,
335 SUM(DECODE(q.type, 2, q.quantity * c.total_cost, 0)) intransit_value_b
336 FROM
337 opi_dbi_opm_inception_qty q,
338 opi_pmi_cost_result_gtmp c,
339 ic_item_mst_b iim,
340 ic_whse_mst w,
341 mtl_system_items_b msi
342 WHERE
343 q.type = 1
344 AND c.whse_code = q.whse_code
345 AND c.item_id = q.item_id
346 AND iim.item_id = q.item_id
347 AND iim.noninv_ind = 0
348 AND w.whse_code = q.whse_code
349 AND msi.segment1 = iim.item_no
350 AND msi.organization_id = w.mtl_organization_id
351 GROUP BY
352 msi.organization_id,
353 msi.inventory_item_id,
354 -- csheu added the following line
355 -- w.whse_code,
356 /* cdaly replaced it with the following line */
357 '-1', /* Key for Subinventory named Unassigned */
358 c.trans_date,
359 msi.primary_uom_code
360 ;
361
362 l_row_count := sql%rowcount;
363 bis_collection_utilities.put_line(TO_CHAR(l_row_count) || ' Onhand and Intransit Inception Balances Costed.');
364
365 -- SETUP/WRAPUP: take out wrapup api call, put log message here.
366 bis_collection_utilities.put_line('FINISHED Onhand Inception Balances SUCCESSFULLY');
367
368 COMMIT;
369
370 EXCEPTION
371 WHEN OTHERS
372 THEN
373 bis_collection_utilities.put_line ('Create_Inception_Balances: '|| sqlerrm);
374 RAISE;
375
376 END Create_Inception_Balances;
377
378
379 /*
380 Get_OPM_Intransit_Balance
381
382 Description - Calculate the OPM inception intransit balances for all
383 orgs.
384
385 Algorithm - Inception Intransit balance =
386 Total Intransit Activity - Current Intransit Activity
387 Inception implies as of start of the collection date, i.e.
388 the end of day value of the collection start date is the
389 inception balance + all activity on collection start date.
390
391 The granularity of the intransit balance is organization_id,
392 inventory_item_id.
393
394 Current Intransit Activity:
395 Contributions to the intransit current inventory are
396 made by:
397 1. inventory transfers - only those that have not
398 been completed yet i.e. transfer_status = 2
399 2. internal orders
400
401
402 Total Intransit Activity:
403 Contributions to the total intransit inventory
404 are made by:
405 1. Inventory transfers - Consists of all subinventory
406 activity that was ever undertaken i.e. completed
407 or not completed. Since completed activity is
408 reported with the receiving warehouse's code,
409 make sure to join back and get the shipping warehouse
410 code.
411 2. Internal orders
412
413
414 Parameters -errbuf
415 retcode
416
417 Error Handling -If collection fails, undoes everything done to make sure
418 no change is committed.
419
420 Date Author Action
421 14 Oct, 2002 Dinkar Gupta Prototyped
422 */
423 PROCEDURE Get_OPM_Intransit_Balance ( errbuf in out NOCOPY varchar2,
424 retcode in out NOCOPY varchar2)
425 IS
426 proc_name VARCHAR2(40);
427
428 BEGIN
429 proc_name := 'Get_OPM_Intransit_Balance ' ;
430
431 INSERT INTO opi_dbi_opm_inception_qty
432 (whse_code, item_id, type, quantity)
433 SELECT whse, item, 2, sum (qty) qty
434 FROM ( /* inception = current - total */
435 SELECT whse, item, qty
436 FROM ( /* current inventory transfers */
437 -- Current intransit inventory transfer activity is determined by
438 -- the transfer_status = 2 for a transfer_id in the
439 -- transfer master table, IC_XFER_MST. The transfer_id
440 -- is the same as the doc_id in the pending transaction tables,
441 -- IC_TRAN_PND. Since the FOB = 'Receipt'
442 -- always, only the shipment lines in the transaction
443 -- tables will contribute to the intransit inventory of the
444 -- shipping warehouse.
445 -- Note that incomplete inventory transfers (i.e. ones that have
446 -- not been received and therefore contribute to current intransit
447 -- balances) are found only in IC_TRAN_PND.
448 -- The pnd tables store -ve quantities for shipments which increase
449 -- intransit and positive quantities for receipts which decrease
450 -- intransit inventory. So quantity signs need to be reversed.
451 SELECT
452 pnd.whse_code whse,
453 pnd.item_id item,
454 (-1 * pnd.trans_qty) qty
455 FROM
456 ic_tran_pnd pnd,
457 ic_xfer_mst xfer,
458 sy_orgn_mst o,
459 opi_dbi_inv_value_log sd
460 WHERE
461 pnd.doc_id = xfer.transfer_id
462 AND xfer.transfer_status = 2 -- not received yet
463 AND pnd.doc_type = 'XFER' -- inventory transfer
464 AND pnd.completed_ind = 1 -- not pending
465 AND pnd.line_id = 1 -- shipping entry
466 AND o.orgn_code = pnd.co_code
467 AND sd.type = 'OID'
468 AND sd.organization_id = o.organization_id
469 AND pnd.trans_date >= sd.transaction_date
470 -- collect only from global
471 -- start date
472 UNION ALL /* current internal order activity*/
473 -- Current intransit internal order activity is taken from the
474 -- MTL_SUPPLY table that stores unfinished internal orders.
475 -- Since this is the OPM extraction, need to ensure that
476 -- the org corresponding the internal order is an OPM org i.e.
477 -- PROCESS_ENABLED_FLAG = 'Y' in MTL_PARAMETERS.
478 -- The owning org for internal orders is determined by the FOB
479 -- which can be either receipt or shipment. We cannot use the
480 -- intransit_owning_org_id field of MTL_SUPPLY since the FOB
481 -- can be changed while the internal order is being processed.
482 -- In addition, there is a bug with that field, as far as I can
483 -- tell. The intransit_owning_org_id field is populated incorrectly
484 -- as of 10/31/02.
485 -- Since MTL supply stores the Quantity (quantity released by the
486 -- shipping org in their primary UOM)
487 -- and the to_org_primary_quantity
488 -- (the primary quantity in the receiving org's primary UOM) we do
489 -- not need any UOM conversions.
490 SELECT whse_mst.whse_code whse,
491 ic_item.item_id item,
492 (decode (mip.fob_point,
493 2, nvl (sup.quantity,0),
494 1, nvl (sup.to_org_primary_quantity,0))) qty
495 FROM mtl_supply sup,
496 mtl_parameters mp,
497 mtl_system_items_b msi,
498 ic_whse_mst whse_mst,
499 ic_item_mst_b ic_item,
500 mtl_interorg_parameters mip,
501 sy_orgn_mst o,
502 sy_orgn_mst c,
503 opi_dbi_inv_value_log sd
504 WHERE supply_type_code in ('SHIPMENT' , 'RECEIVING')
505 AND intransit_owning_org_id is not NULL -- necessary for
506 -- intransit
507 AND mip.from_organization_id = sup.from_organization_id
508 AND mip.to_organization_id = sup.to_organization_id
509 AND nvl(mip.fob_point,-1) > 0
510 AND whse_mst.mtl_organization_id =
511 decode(mip.fob_point,
512 1, sup.to_organization_id, -- receipt
513 2, sup.from_organization_id, -- shipment
514 -1)
515 AND mp.organization_id = whse_mst.mtl_organization_id
516 AND mp.process_enabled_flag = 'Y' -- OPM org
517 AND msi.inventory_item_id = sup.item_id -- to get OPM item_id
518 AND msi.organization_id = whse_mst.mtl_organization_id
519 AND msi.segment1 = ic_item.item_no
520 AND nvl(msi.inventory_asset_flag,'N') = 'Y'
521 -- ignore expense items
522 AND o.orgn_code = whse_mst.orgn_code
523 AND c.orgn_code = o.co_code
524 AND sd.organization_id = c.organization_id
525 AND sd.type = 'OID'
526 AND trunc (receipt_date) >= sd.transaction_date
527 -- collect only from global
528 -- start date
529 ) current_intransit
530 UNION ALL /* (-) total intransit */
531 -- The total quantity is the sum of all the internal transfer and
532 -- internal order activity.
533 -- Since inception quantity = total quantity - current quantity,
534 -- we take the negative of the total quantity in the outer most
535 -- select here.
536 SELECT whse, item, (-1 * qty) qty
537 FROM ( /* total inventory transfers */
538 -- Total intransit inventory transfer activity is the sum of all
539 -- inventory transfer transactions i.e. with doc_type = 'XFER'
540 -- The transactions must be after the collection start date.
541 -- Since the FOB = 'Receipt' always and receipt transactions
542 -- i.e. those with line_type = 2, are associated with the receiving
543 -- warehouse, we need to join to the transfer master, ic_xfer_mst,
544 -- to figure out the shipping org whose intransit balance is
545 -- affected. For consistency with the all other modules, we look
546 -- at pending transactions where completed_ind = 1.
547 -- The pnd/cmp tables store -ve quantities for shipments which
548 -- increase intransit and positive quantities for receipts which
549 -- decrease intransit inventory.
550 -- So quantity signs need to be reversed.
551 SELECT xfer.from_warehouse whse,
552 pnd.item_id item,
553 (-1 * pnd.trans_qty) qty
554 FROM
555 ic_tran_vw1 pnd,
556 ic_xfer_mst xfer,
557 sy_orgn_mst o,
558 opi_dbi_inv_value_log sd
559 WHERE pnd.completed_ind = 1 -- completed transfer
560 -- view sets completed_ind to 1
561 -- for everything in the ic_tran_cmp
562 AND pnd.doc_type = 'XFER' -- inventory transfer
563 AND pnd.doc_id = xfer.transfer_id
564 AND o.orgn_code = pnd.co_code
565 AND sd.type = 'OID'
566 AND sd.organization_id = o.organization_id
567 AND trunc (pnd.trans_date) >= sd.transaction_date
568 -- collect only from global
569 -- start date
570 UNION ALL /* total internal shipments */
571 -- Total internal order intransit activity is given by the sum
572 -- of all the completed shipment and receipt transactions i.e.
573 -- transaction of type 'OMSO' or 'PORC'.
574 -- To get the from and to organizations depending on what the FOB
575 -- is, we need to join back to the purchase order requisition lines
576 -- table, po_requisitions_lines_all.
577 -- For shipments (doc_type = OMSO) this is achieved through the
578 -- oe_order_lines_all table.
579 -- For receipts (doc_type = PORC) this is achieved through the
580 -- rcv_transactions table.
581 SELECT whse_mst.whse_code whse,
582 ic_item.item_id item,
583 (-1 * ic_tran.trans_qty) qty
584 FROM (SELECT doc_type, doc_id, line_id, co_code, whse_code, item_id,
585 sum(trans_qty) trans_qty, trans_um, gl_posted_ind,
586 trans_date, completed_ind
587 FROM ic_tran_pnd
588 WHERE doc_type = 'OMSO'
589 AND completed_ind = 1
590 GROUP BY doc_type, doc_id, line_id, co_code, whse_code, item_id,
591 trans_um, gl_posted_ind, trans_date,
592 completed_ind
593 UNION ALL
594 -- receipts into different lots can generate two lines
595 -- in ic_tran with same line_id so collapse based on
596 -- line_id
597 SELECT doc_type, doc_id, line_id, co_code, whse_code, item_id,
598 sum (trans_qty) trans_qty, trans_um, gl_posted_ind,
599 trans_date,
600 1 -- all transactions are completed in tran_cmp
601 FROM ic_tran_cmp
602 WHERE doc_type = 'OMSO'
603 GROUP BY doc_type, doc_id, line_id, co_code, whse_code, item_id,
604 trans_um, gl_posted_ind, trans_date, 1
605 ) ic_tran,
606 oe_order_lines_all oola,
607 po_requisition_lines_all req,
608 ic_whse_mst whse_mst,
609 mtl_system_items_b msi,
610 ic_item_mst_b ic_item,
611 mtl_interorg_parameters mip,
612 sy_orgn_mst o,
613 opi_dbi_inv_value_log sd
614 WHERE ic_tran.completed_ind = 1 -- but complete
615 AND ic_tran.doc_type = 'OMSO' -- internal sales order shipment
616 AND ic_tran.line_id = oola.line_id -- get line id details
617 AND oola.source_document_type_id = 10 --ensure this is internal
618 --order
619 AND req.requisition_line_id = oola.source_document_line_id
620 AND mip.from_organization_id = req.source_organization_id
621 AND mip.to_organization_id = req.destination_organization_id
622 AND whse_mst.mtl_organization_id =
623 decode (mip.fob_point, --FOB selects owning org
624 2, req.source_organization_id,
625 1, req.destination_organization_id)
626 -- for warehouse
627 AND msi.organization_id = whse_mst.mtl_organization_id
628 AND msi.inventory_item_id = req.item_id -- for OPM item id
629 AND ic_item.item_no = msi.segment1
630 AND o.orgn_code = ic_tran.co_code
631 AND sd.type = 'OID'
632 AND sd.organization_id = o.organization_id
633 AND trunc (ic_tran.trans_date) >= sd.transaction_date
634 -- collect only from global
635 -- start date
636 UNION ALL /* total internal receipts */
637 SELECT whse_mst.whse_code whse,
638 ic_item.item_id item,
639 (-1 * ic_tran.trans_qty) qty
640 FROM (SELECT doc_type, doc_id, line_id, co_code, whse_code, item_id,
641 sum(trans_qty) trans_qty, trans_um, gl_posted_ind,
642 trans_date, completed_ind
643 FROM ic_tran_pnd
644 WHERE doc_type = 'PORC'
645 AND completed_ind = 1
646 GROUP BY doc_type, doc_id, line_id, co_code, whse_code, item_id,
647 trans_um, gl_posted_ind, trans_date,
648 completed_ind
649 UNION ALL
650 -- receipts into different lots can generate two lines
651 -- in ic_tran with same line_id so collapse based on
652 -- line_id
653 SELECT doc_type, doc_id, line_id, co_code, whse_code, item_id,
654 sum (trans_qty) trans_qty, trans_um, gl_posted_ind,
655 trans_date,
656 1 -- all transactions are completed in tran_cmp
657 FROM ic_tran_cmp
658 WHERE doc_type = 'PORC'
659 GROUP BY doc_type, doc_id, line_id, co_code, whse_code, item_id,
660 trans_um, gl_posted_ind, trans_date, 1
661 ) ic_tran,
662 rcv_transactions rcv,
663 po_requisition_lines_all req,
664 ic_whse_mst whse_mst,
665 mtl_system_items_b msi,
666 ic_item_mst_b ic_item,
667 mtl_interorg_parameters mip,
668 sy_orgn_mst o,
669 opi_dbi_inv_value_log sd
670 WHERE ic_tran.completed_ind = 1 -- but complete
671 AND ic_tran.doc_type = 'PORC' -- internal sales order shipment
672 AND rcv.transaction_id = ic_tran.line_id
673 AND req.requisition_line_id = rcv.requisition_line_id
674 AND req.destination_type_code = 'INVENTORY' -- internal order
675 AND req.source_type_code = 'INVENTORY' -- has source and
676 -- dest type as
677 -- INVENTORY
678 AND mip.from_organization_id = req.source_organization_id
679 AND mip.to_organization_id = req.destination_organization_id
680 AND whse_mst.mtl_organization_id =
681 decode (mip.fob_point, --FOB selects owning org
682 2, req.source_organization_id,
683 1, req.destination_organization_id)
684 AND msi.organization_id = whse_mst.mtl_organization_id
685 AND msi.inventory_item_id = req.item_id -- for OPM item id
686 AND ic_item.item_no = msi.segment1
687 AND o.orgn_code = ic_tran.co_code
688 AND sd.type = 'OID'
689 AND sd.organization_id = o.organization_id
690 AND trunc (ic_tran.trans_date) >= sd.transaction_date
691 -- collect only from global
692 -- start date
693 ) tot_intransit
694 ) inception_intransit
695 GROUP BY whse, item;
696
697 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' Intransit Inception Quantity rows extracted.');
698
699 EXCEPTION
700
701 WHEN OTHERS
702 THEN
703 bis_collection_utilities.put_line (proc_name || sqlerrm);
704
705 END Get_OPM_Intransit_Balance;
706
707
708
709 PROCEDURE Get_OPM_WIP_Balance(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
710 IS
711 BEGIN
712
713 INSERT INTO opi_dbi_opm_inv_stg
714 (
715 ORGANIZATION_ID,
716 INVENTORY_ITEM_ID,
717 TRANSACTION_DATE,
718 WIP_VALUE_B
719 )
720 SELECT
721 msi.organization_id,
722 msi.inventory_item_id,
723 log.transaction_date,
724 ib.wip_value_b
725 FROM
726 (
727 SELECT
728 led.whse_code,
729 gmd.item_id,
730 nvl(sum(led.amount_base * gmd.cost_alloc),0) wip_value_b
731 FROM
732 (
733 select
734 l.doc_id, h.wip_whse_code whse_code,
735 sum(l.amount_base * l.debit_credit_sign) amount_base
736 from
737 gl_subr_led l,
738 gme_batch_header h
739 where
740 l.doc_type = 'PROD'
741 and l.acct_ttl_type = 1530
742 and l.doc_id = h.batch_id
743 and h.gl_posted_ind = 0
744 group by
745 l.doc_id, h.wip_whse_code
746 UNION ALL
747 select
748 l.doc_id, h.wip_whse_code whse_code,
749 -1 * sum(amount_base * debit_credit_sign) amount_base
750 from
751 gl_subr_led l,
752 gme_batch_header h,
753 ic_whse_mst w,
754 sy_orgn_mst o,
755 opi_dbi_inv_value_log log
756 where
757 l.doc_type = 'PROD'
758 and l.acct_ttl_type = 1530
759 and l.doc_id = h.batch_id
760 and h.wip_whse_code = w.whse_code
761 and o.orgn_code = l.co_code
762 and log.organization_id = o.organization_id
763 and log.type = 'OID'
764 and l.gl_trans_date >= log.transaction_date
765 group by
766 l.doc_id, h.wip_whse_code
767 ) led,
768 gme_material_details gmd
769 WHERE
770 led.doc_id = gmd.batch_id
771 AND gmd.line_type = 1
772 GROUP BY
773 led.whse_code,
774 gmd.item_id
775 HAVING
776 nvl(sum(led.amount_base * gmd.cost_alloc),0) <> 0
777 ) ib,
778 ic_whse_mst w,
779 sy_orgn_mst o,
780 sy_orgn_mst c,
781 ic_item_mst_b i,
782 mtl_system_items_b msi,
783 opi_dbi_inv_value_log log
784 WHERE
785 w.whse_code = ib.whse_code
786 AND i.item_id = ib.item_id
787 AND msi.segment1 = i.item_no
788 AND msi.organization_id = w.mtl_organization_id
789 AND o.orgn_code = w.orgn_code
790 AND c.orgn_code = o.co_code
791 AND log.organization_id = c.organization_id
792 AND log.type= 'OID';
793
794 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' WIP Inception Balances calculated.');
795
796 COMMIT;
797
798 EXCEPTION
799 WHEN OTHERS
800 THEN
801 bis_collection_utilities.put_line ('Get_OPM_WIP_Balance: '|| sqlerrm);
802 RAISE;
803
804 END Get_OPM_WIP_Balance;
805
806
807 PROCEDURE initialize_high_water_mark
808 IS
809 BEGIN
810 INSERT INTO opi_dbi_inv_value_log
811 (
812 organization_id,
813 transaction_id,
814 transaction_date,
815 type,
816 source,
817 creation_date,
818 last_update_date,
819 created_by,
820 last_updated_by,
821 last_update_login
822 )
823 VALUES
824 (
825 0,
826 0,
827 g_global_start_date,
828 'GSL',
829 2,
830 SYSDATE,
831 SYSDATE,
832 g_user_id,
833 g_user_id,
834 g_login_id
835 );
836
837 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' high water mark log rows inserted.');
838
839 fnd_stats.gather_table_stats(
840 ownname => g_opi_schema,
841 tabname => 'OPI_DBI_INV_VALUE_LOG',
842 percent => 10);
843
844 COMMIT;
845
846 EXCEPTION
847 WHEN OTHERS
848 THEN
849 bis_collection_utilities.put_line ('Initialize_High_Water_Mark: '|| sqlerrm);
850 RAISE;
851
852 END Initialize_High_Water_Mark;
853
854
855 PROCEDURE Get_OPM_Inception_Inv_Balance(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
856 IS
857 BEGIN
858 Clean_OPM_Tables;
859
860 Get_OPM_Inception_Date(g_inception_date);
861
862 Get_OPM_Onhand_Balance(errbuf, retcode);
863
864 Get_OPM_Intransit_Balance(errbuf, retcode);
865
866 fnd_stats.gather_table_stats(
867 ownname => g_opi_schema,
868 tabname => 'OPI_DBI_OPM_INCEPTION_QTY',
869 percent => 10);
870
871 Cost_Inception_Quantities;
872
873 Create_Inception_Balances;
874
875 fnd_stats.gather_table_stats(
876 ownname => g_opi_schema,
877 tabname => 'OPI_DBI_OPM_INV_STG',
878 percent => 10);
879
880 Get_OPM_WIP_Balance(errbuf, retcode);
881
882 Initialize_High_Water_Mark;
883
884 EXCEPTION
885 WHEN OTHERS THEN
886
887 bis_collection_utilities.put_line('Error encounted in OPI_DBI_INV_VALUE_OPM_INIT_PKG.Get_OPM_Inception_Inv_Balance');
888 bis_collection_utilities.put_line('Error Message: ' || SQLERRM);
889
890 RAISE_APPLICATION_ERROR(-20000, errbuf);
891 /*please note that this api will commit!!*/
892
893 END Get_OPM_Inception_Inv_Balance;
894
895
896 PROCEDURE Run_OPM_First_ETL(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
897 IS
898 l_stmt_num NUMBER;
899 l_err_num NUMBER;
900 l_err_msg VARCHAR2(255);
901 l_list dbms_sql.varchar2_table;
902 BEGIN
903 l_stmt_num := 0;
904 retcode := 0;
905 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
906 l_list(2) := 'BIS_GLOBAL_START_DATE';
907
908 IF (bis_common_parameters.check_global_parameters(l_list))
909 THEN
910 l_stmt_num := 10;
911 bis_collection_utilities.put_line('< Starting Inception Balance Extraction >');
912 --Get_OPM_Inception_Inv_Balance(errbuf, retcode);
913 bis_collection_utilities.put_line('</ Finished Inception Balance Extraction >');
914
915 l_stmt_num := 20;
916 bis_collection_utilities.put_line('< Starting Daily Activity Extraction >');
917 opi_dbi_inv_value_opm_incr_pkg.Extract_OPM_Daily_Activity(errbuf, retcode, g_inception_date);
918 bis_collection_utilities.put_line('</ Finished Daily Activity Extraction ');
919 ELSE
920 retcode := 1;
921 bis_collection_utilities.put_line('Global Parameters are not setup.');
922 bis_collection_utilities.put_line('Please check that the profile options: ' ||
923 'BIS_PRIMARY_CURRENCY_CODE and BIS_GLOBAL_START_DATE are setup.');
924 END IF;
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 retcode := 1;
929 l_err_num := SQLCODE;
930 l_err_msg := 'OPI_DBI_INV_VALUE_OPM_INIT_PKG.Run_OPM_First_ETL ('
931 || to_char(l_stmt_num)
932 || '): '
933 || substr(SQLERRM, 1,200);
934
935 bis_collection_utilities.put_line('OPI_DBI_INV_VALUE_OPM_INIT_PKG.Run_OPM_First_ETL '
936 || '- Error at statement ('
937 || to_char(l_stmt_num)
938 || ')');
939
940 bis_collection_utilities.put_line('Error Number: ' || to_char(l_err_num));
941 bis_collection_utilities.put_line('Error Message: ' || l_err_msg);
942
943 END Run_OPM_First_ETL;
944
945
946 BEGIN
947 g_opi_appinfo := fnd_installation.get_app_info
948 (
949 application_short_name => 'OPI',
950 status => g_opi_status,
951 industry => g_opi_industry,
952 oracle_schema => g_opi_schema
953 );
954
955 End OPI_DBI_INV_VALUE_OPM_INIT_PKG ;
[Home] [Help]