1 PACKAGE BODY OPI_DBI_INV_VALUE_OPM_INCR_PKG as
2 /* $Header: OPIDIPRB.pls 120.1 2005/08/02 01:46:47 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
7 g_sysdate DATE;
8 g_created_by NUMBER;
9 g_last_update_login NUMBER;
10 g_last_updated_by NUMBER;
11 g_global_start_date DATE;
12 g_inception_date DATE := NULL;
13 global_currency_code VARCHAR2(10);
14 -- csheu change 09/02/03 --
15 g_global_rate_type VARCHAR2(15);
16
17 g_global_sec_currency_code VARCHAR2(10);
18 g_global_sec_rate_type VARCHAR2(15);
19
20 C_PRI_SEC_CURR_SAME_MARKER CONSTANT NUMBER := -9999;
21
22 g_opi_schema VARCHAR2(32);
23 g_opi_status VARCHAR2(32);
24 g_opi_industry VARCHAR2(32);
25 g_opi_appinfo BOOLEAN;
26
27
28 FUNCTION dsql_date(p_date DATE)
29 RETURN VARCHAR2
30 IS
31 BEGIN
32 RETURN 'TO_DATE(''' || TO_CHAR(p_date,'DD-MON-YYYY HH24:MI:SS') || ''', ''DD-MON-YYYY HH24:MI:SS'')';
33 END;
34
35 Function initial_load
36 RETURN BOOLEAN
37 IS
38 BEGIN
39 IF g_inception_date IS NOT NULL
40 THEN
41 RETURN TRUE;
42 ELSE
43 RETURN FALSE;
44 END IF;
45 END;
46
47 Function incremental_load
48 RETURN BOOLEAN
49 IS
50 BEGIN
51 RETURN NOT initial_load;
52 END;
53
54 Procedure Refresh_ONH_LED_Current
55 (
56 from_transaction_id NUMBER,
57 to_transaction_id NUMBER
58 )
59 IS
60 lv_sql VARCHAR2(32767);
61 lv_led_hint VARCHAR2(32);
62 BEGIN
63 IF initial_load
64 THEN
65 lv_led_hint := '/*+ full(led) */';
66 ELSE
67 lv_led_hint := '';
68 END IF;
69
70 lv_sql :=
71 'INSERT INTO opi_dbi_opm_inv_led_current
72 (
73 whse_code,
74 item_id,
75 transaction_date,
76 onhand_qty,
77 onhand_value_b
78 )
79 SELECT
80 t.whse_code,
81 t.item_id,
82 t.trans_date,
83 SUM(whse_line_trans_qty),
84 SUM(DECODE(line_trans_qty, 0, 0,
85 led.amount_base*(whse_line_trans_qty/line_trans_qty))) activity_val_b
86 FROM
87 (
88 SELECT ' || lv_led_hint || '
89 led.doc_type, led.doc_id, led.line_id,
90 TRUNC(led.gl_trans_date) gl_trans_date,
91 SUM(led.amount_base * led.debit_credit_sign) amount_base
92 FROM gl_subr_led led
93 WHERE
94 led.acct_ttl_type = 1500
95 AND led.subledger_id BETWEEN :1 AND :2
96 AND led.gl_trans_date >= :3
97 GROUP BY doc_type, doc_id, line_id, TRUNC(gl_trans_date)
98 ) led,
99 (
100 SELECT
101 doc_type, doc_id, line_id,
102 trans_date, item_id, whse_code,
103 SUM(trans_qty) whse_line_trans_qty,
104 SUM(SUM(trans_qty))
105 OVER (PARTITION BY doc_type, doc_id, line_id) line_trans_qty
106 FROM
107 (SELECT doc_type, doc_id, line_id, item_id, whse_code,
108 trunc(trans_date) trans_date, trans_qty
109 FROM ic_tran_pnd
110 WHERE completed_ind = 1 AND gl_posted_ind = decode(doc_type, ''RECV'', gl_posted_ind, 1)
111 AND trans_date >= :4
112 UNION ALL
113 SELECT doc_type, doc_id, line_id, item_id, whse_code,
114 trunc(trans_date) trans_date, trans_qty
115 FROM ic_tran_cmp
116 WHERE gl_posted_ind = 1
117 AND trans_date >= :5
118 )
119 GROUP BY
120 doc_type, doc_id, line_id, trans_date, item_id, whse_code
121 HAVING SUM(trans_qty) <> 0
122 ) t
123 WHERE
124 led.doc_type = t.doc_type
125 AND led.doc_id = DECODE(led.doc_type, ''XFER'', t.doc_id, led.doc_id)
126 AND led.gl_trans_date = t.trans_date
127 AND led.line_id = t.line_id
128 GROUP BY
129 t.whse_code,
130 t.item_id,
131 t.trans_date';
132
133 EXECUTE IMMEDIATE lv_sql USING from_transaction_id, to_transaction_id, g_global_start_date,
134 g_global_start_date, g_global_start_date;
135
136 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' Onhand transactions collected from permanent subledger.');
137 COMMIT;
138
139 EXCEPTION
140 WHEN OTHERS
141 THEN
142 bis_collection_utilities.put_line ('Refresh_ONH_LED_Current error: '|| sqlerrm);
143 RAISE;
144
145 END Refresh_ONH_LED_Current;
146
147 /* Refresh_RVAL_LED_Current
148
149 Description - Extracts Inventory Cost Revaluation data from the
150 permanent subledger and puts it in the current subledger
151 work table, opi_dbi_opm_onhled_current.
152
153 Cost Revaluation entries are
154 made in the subledgers with an acct_ttl_type = 6250.
155
156 The granularity of the data extracted is:
157 organization_id, inventory_item_id, transaction_date.
158
159 Since this is the permanent subledger, we will extract
160 based on a high watermark approach. The
161 subledger_ids to extract between will be passed in as
162 arguments
163
164 Parameters - from_subledger_id - subledger id to start collecting from
165 to_subledger_id - subledger id to collect upto
166
167 Algorithm - Add rows to the opi_dbi_opm_onhled_current table
168 by extracting all the test rows corresponding to
169 Cost Revaluation Variance (acct_ttl_type = 6250):
170
171
172 Error Handling - Commited data in work tables will be truncated when the
173 procedure is rerun aafter an unhandled exception stops the
174 exraction.
175
176 Date Author Action
177 01 Oct, 2003 Christopher Daly Wrote routine
178
179 */
180
181
182
183 Procedure Refresh_RVAL_LED_Current
184 (
185 from_transaction_id NUMBER,
186 to_transaction_id NUMBER
187 )
188 IS
189 lv_sql VARCHAR2(32767);
190 lv_led_hint VARCHAR2(32);
191 BEGIN
192 IF initial_load
193 THEN
194 lv_led_hint := '/*+ full(led) */';
195 ELSE
196 lv_led_hint := '';
197 END IF;
198
199 lv_sql :=
200 'INSERT INTO opi_dbi_opm_inv_led_current
201 (
202 whse_code,
203 item_id,
204 transaction_date,
205 onhand_qty,
206 onhand_value_b
207 )
208 SELECT
209 whse.whse_code whse_code,
210 led.line_id item_id,
211 led.gl_doc_date + 1 transaction_date, -- Add one to date to prevent transaction from
212 -- being counted for morning of last day of period
213 0 onhand_qty, -- Transaction did not affect quantities, only value
214 -sum(led.amount_base) onhand_value_b
215 FROM
216 (
217 SELECT ' || lv_led_hint || '
218 led.doc_id,
219 led.line_id,
220 TRUNC(led.gl_doc_date) gl_doc_date,
221 SUM(led.amount_base * led.debit_credit_sign) amount_base
222 FROM gl_subr_led led
223 WHERE
224 led.acct_ttl_type = 6250 -- IRV ACCT
225 AND led.subledger_id BETWEEN :1 AND :2
226 AND led.gl_doc_date >= :3
227 AND DOC_TYPE = ''RVAL''
228 GROUP BY doc_id, line_id, TRUNC(gl_doc_date)
229 ) led,
230 IC_WHSE_MST WHSE
231 WHERE
232 led.doc_id = whse.mtl_organization_id
233 GROUP BY
234 whse.whse_code,
235 led.line_id, --item_id from subledger line_id column
236 led.gl_doc_date';
237
238 EXECUTE IMMEDIATE lv_sql USING from_transaction_id, to_transaction_id, g_global_start_date;
239
240 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' Onhand Inventory Revaluation transactions collected from permanent subledger.');
241 COMMIT;
242
243 EXCEPTION
244 WHEN OTHERS
245 THEN
246 bis_collection_utilities.put_line ('Refresh_RVAL_LED_Current error: '|| sqlerrm);
247 RAISE;
248
249 END Refresh_RVAL_LED_Current;
250
251
252 /* Refresh_ITR_LED_Current
253
254 Description - Extracts intransit data from the permanent subledger and
255 puts it in the current test subledger table,
256 opi_dbi_opm_intled_current.
257
258 In-transit activity happens due to inventory transfers
259 and internal orders. Inventory transfer entries are
260 made in the ledgers with an acct_ttl_type = 1570.
261 Internal orders are entered into the subledger with
262 an acct_ttl_type = 1590.
263
264 The granularity of the data extracted is:
265 organization_id, inventory_item_id, transaction_date.
266
267 Since this is the permanent subledger, we will extract
268 based on a high watermark approach. The
269 subledger_ids to extract between will be passed in as
270 arguments
271
272 This routine will extract all inventory transfers
273 (shipments and receipts) that affect the intransit
274 activity of the org passed in as the argument that
275 is present in the permanent subledger.
276
277 Parameters - p_whse_code - warehouse for which to collect data
278 p_from_subledger_id - subledger id to start collecting from
279 p_to_subledger_id - subledger id to collect upto
280
281 Algorithm - Truncate the opi_dbi_opm_intled_current table.
282
283 Extract all the test subledger rows corresponding to
284 inventory transfers (acct_ttl_type = 1570):
285 -- extract shipment entries: join to ic_tran_pnd
286 -- extract receipt entries: join to ic_tran_pnd
287 -- extract shipment entries: join to ic_tran_cmp
288 -- extract receipt entries: join to ic_tran_cmp.
289
290 Error Handling - ???
291
292 Date Author Action
293 16 Oct, 2002 Dinkar Gupta Wrote routine
294
295 */
296 PROCEDURE Refresh_ITR_LED_Current (p_from_subledger_id IN NUMBER,
297 p_to_subledger_id IN NUMBER)
298 IS
299
300 proc_name VARCHAR2 (30) ;
301 lv_sql VARCHAR2(32767);
302 lv_led_hint VARCHAR2(32);
303
304 BEGIN
305 proc_name := 'extract_permanent_subledger ';
306 /*
307 For inventory transfers, we can join back between
308 the test subledgers using the doc_id, doc_type and
309 line_id.
310 Since the FOB point is always 'RECEIPT' currently,
311 the subledger stores the shipment lines (line_id = 1)
312 as a credit (+ve) and receipt lines as (-ve). Since
313 only the intransit of shipping warehouse is affected,
314 we need to find the shipping warehouse corresponding
315 to the receipt entry in the subledgers. This can
316 done by joining back on the doc_id, doc_type and
317 line_id.
318
319 The transactions can be in the ic_tran_pnd or the ic_tran_cmp.
320 Shipping quantities for shipments must be qualified with a -1
321 because they are increases to intransit.
322 Intransit account entries have acct_ttl_type = 1570 -- we need this
323 because there is an adjusting entry to every 1570 to the account
324 type 1500 and we need to ignore that.
325
326 The ic_tran tables have a gl_posted_ind column. The column is
327 0 when a transaction has not been posted into the permanent
328 subledger and is 1 if it has been posted to the permanent subledger.
329 Therefore, when extracting data from the perm. subledger, we need
330 to ensure that gl_posted_ind = 1.
331 No need to check for the completed_ind in the pending tables here,
332 since gl_posted_ind = 1 ==> completed_ind = 1
333
334 */
335 IF initial_load
336 THEN
337 lv_led_hint := '/*+ full(led) */';
338 ELSE
339 lv_led_hint := '';
340 END IF;
341
342 lv_sql :=
343 'INSERT INTO opi_dbi_opm_inv_led_current
344 (
345 whse_code,
346 item_id,
347 transaction_date,
348 intransit_qty,
349 intransit_value_b
350 )
351 (SELECT whse_code, item_id, trans_date, sum (qty) qty, sum (val) val
352 FROM (
353 SELECT ' || lv_led_hint || '
354 whse_mst.whse_code,
355 ic_item.item_id,
356 trunc (led.gl_trans_date) trans_date,
357 pnd.trans_qty qty,
358 sum (led.debit_credit_sign * led.amount_base *
359 decode (pnd.line_id, -- check if need exchange rate
360 1, 1, -- shipment does not need exchange rate,
361 -- so default to 1
362 2, decode (nvl (led.mul_div_sign, 0),
363 -- if there is no mul_div_sign,
364 -- there will be no exchange rate,
365 -- so we return 1 i.e. no exchange
366 -- rate.
367 -- Else if mul_div_sign = 0,
368 -- multiply by exchange rate
369 0, nvl(led.exchange_rate, 1),
370 -- if mul_div_sign = 2, divide
371 -- by exchange rate
372 1, 1/(nvl (led.exchange_rate, 1))))
373 ) val
374 FROM
375 -- there can be a many to many mapping between the
376 -- ic_tran and subledger, so we sum up all quantities
377 -- for a given doc and line id before matching it
378 -- up to subledger.
379 -- Note the -ve sign on the quantities
380 (SELECT doc_type, doc_id, line_id, whse_code, item_id,
381 sum (-1 * trans_qty) trans_qty,
382 trans_um, gl_posted_ind,
383 completed_ind
384 FROM ic_tran_pnd
385 WHERE doc_type = ''XFER''
386 AND completed_ind = 1 -- completed transaction
387 AND gl_posted_ind = 1 -- and posted to perm ledger
388 GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
389 trans_um, gl_posted_ind, completed_ind
390 UNION ALL
391 SELECT doc_type, doc_id, line_id, whse_code, item_id,
392 sum (-1 * trans_qty) trans_qty, trans_um,
393 gl_posted_ind,
394 1 -- all transactions are completed in tran_cmp
395 FROM ic_tran_cmp
396 WHERE doc_type = ''XFER''
397 AND gl_posted_ind = 1 -- completed+posted transaction
398 GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
399 trans_um, gl_posted_ind, 1
400 ) pnd,
401 gl_subr_led led,
402 ic_whse_mst whse_mst,
403 mtl_system_items_b msi,
404 ic_item_mst_b ic_item,
405 ic_xfer_mst xfer
406 WHERE pnd.doc_id = led.doc_id
407 AND pnd.doc_type = led.doc_type
408 AND pnd.doc_id = xfer.transfer_id
409 AND led.acct_ttl_type = 1570 -- intransit account
410 -- ignore acct. type 1500 entries
411 AND pnd.line_id = led.line_id
412 AND led.subledger_id BETWEEN :1
413 AND :2
414 AND trunc(led.gl_trans_date) >= :3
415 AND xfer.from_warehouse = whse_mst.whse_code -- for org_id
416 -- fob = receipt
417 AND ic_item.item_id = pnd.item_id -- for inventory_item_id
418 AND msi.segment1 = ic_item.item_no
419 AND msi.organization_id = whse_mst.mtl_organization_id
420 GROUP BY whse_mst.whse_code, ic_item.item_id,
421 trunc (led.gl_trans_date), pnd.trans_qty,
422 pnd.doc_id, pnd.line_id
423 -- grouping by line_id and doc_id
424 -- important if we are summing up
425 -- multiple lines in ic_tran with same
426 -- doc_id
427 ) inv_intransit_led_led
428 GROUP BY whse_code, item_id, trans_date)';
429
430 EXECUTE IMMEDIATE lv_sql USING p_from_subledger_id, p_to_subledger_id, g_global_start_date;
431
432 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' intransit transactions collected from permanent subledger.');
433 COMMIT;
434
435 EXCEPTION
436 WHEN OTHERS
437 THEN
438 bis_collection_utilities.put_line ('Refresh_ITR_LED_Current: '|| sqlerrm);
439 RAISE;
440
441 END Refresh_ITR_LED_Current;
442
443
444 /* Refresh_IOR_LED_Current
445
446 Description - Extracts internal order intransit from the permanent
447 subledger and
448 puts it in the current test subledger table,
449 opi_dbi_opm_intled_current.
450
451 Internal orders are entered into the subledger with
452 an acct_ttl_type = 1590.
453 Internal orders have an associated FOB and this
454 has to be taken into account.
455
456 The granularity of the data extracted is:
457 organization_id, inventory_item_id, transaction_date.
458
459 Since this is the permanent subledger, we can use the
460 high watermark technique on the extraction.
461
462 This routine will extract all internal orders (shipments
463 and receipts) that affect the intransit activity of
464 the org passed in as the argument that is present in the
465 permanent subledger.
466
467 Algorithm - Extract all the previously unextracted permanent
468 subledger rows corresponding to
469 internal orders (acct_ttl_type = 1590):
470 -- extract shipment entries: join to ic_tran_pnd
471 -- extract receipt entries: join to ic_tran_pnd
472 -- extract shipment entries: join to ic_tran_cmp
473 -- extract receipt entries: join to ic_tran_cmp.
474
475 Parameters - p_whse_code - whse_code for which to collect data
476 p_from_subledger_id - subledger id to start collecting from
477 p_to_subledger_id - subledger id to collect upto
478
479 Error Handling - ???
480
481 Date Author Action
482 29 Oct, 2002 Dinkar Gupta Wrote package
483
484
485
486 */
487 PROCEDURE Refresh_IOR_LED_Current (p_from_subledger_id IN NUMBER,
488 p_to_subledger_id IN NUMBER)
489 IS
490 proc_name VARCHAR2 (50) ;
491 lv_sql VARCHAR2(32767);
492 lv_led_hint VARCHAR2(32);
493 BEGIN
494 proc_name := 'extract_led_subr_int_orders ';
495 -- Cursor to extract all internal orders from the permanent subledger.
496 -- Internal order shipment entries have a doc_type = 'OMSO'
497 -- and receipt entries have doc_type = 'PORC'.
498 -- Internal orders are qualified by acct_ttl_type = 1590 in the
499 -- subledgers.
500 -- The doc_type, line_id and completed_ind give a 1-1 mapping between
501 -- the ic_tran_pnd/cmp and the subledgers (for cmp table, completed_ind
502 -- is always 1).
503 --
504 -- For tran tables, we need to look at all entries with gl_posted_ind
505 -- = 1.
506 --
507 -- For internal orders, the FOB determines who owns the intransit
508 -- inventory. We find the FOB and shipment/receipt orgs from the
509 -- oe_order_lines_all table that has one line per line in the
510 -- requisition that created the internal order. The mapping from
511 -- the ic_tran record to the oe_order_lines record is given by the
512 -- line_id.
513 --
514 -- Shipment entry quantities are always recorded as -ve numbers, though
515 -- shipments always increase intransit inventories. Receipts always
516 -- decrease intransit but are recorded as +ve quantities in the
517 -- ic_tran_tables. So we will have to take the negative of the
518 -- quantities in the tran tables.
519 --
520 -- The ic_tran tables have a gl_posted_ind column. The column is
521 -- 0 when a transaction has not been posted into the permanent
522 -- subledger and is 1 if it has been posted to the permanent subledger.
523 -- Therefore, when extracting data from the test subledger, we need
524 -- to ensure that gl_posted_ind = 0 since we do not want to double count.
525 -- Additionally, for the pnd table, we need to pick up only completed
526 -- transactions, i.e. ones with completed_ind = 1
527 --
528 -- Why does the ic_tran_pnd/cmp have to be collapsed before matching to
529 -- gl_subr_led?
530 -- Why do we not need a date join between the ic_tran_pnd/cmp and
531 -- gl_subr_led?
532 -- There can be a N-N mapping between the tran tables and the ledger.
533 -- The N in the ledger can be because of some adjustment accounts etc.
534 -- where one line in the tran tables generates multiple lines in the
535 -- ledger. So far, all but one such multiple lines I have seen have a
536 -- value of 0, but we cannot be sure of this.
537 -- The N in the tran tables happen for both shipments and receipts:
538 --
539 -- Shipments: If a line to be shipped is split into N lines, (where
540 -- each such line can be shipped from any valid lot), and any M out of
541 -- these N lines are ship confirmed at the same time, all these M
542 -- lines get the same line_id, doc_type, doc_id, and trans_date
543 -- (including timestamp).
544 -- The remaining L = N - M lines, if ship confirmed separately, will get
545 -- a different line_id from the M lines. Correspondingly, the ledger will
546 -- have one (or more than 1 if there are adjustments etc.) lines for the
547 -- M lines with the same line_id, and other entry[ies] for the remaining
548 -- lines.
549 -- Incidentally, the date of a shipment is the sysdate i.e.
550 -- the date at which the order shipped out using the forms, not any
551 -- shipment date etc... pre-specified in any of the forms. This means that
552 -- all lines with the same line_id will be picked simultaneously to be
553 -- posted into the ledger.
554 --
555 -- Receipts: No item can be over-received - the forms ensure this.
556 -- A shipment can be partially received i.e. any of the lines can be
557 -- received with any quantity less than or equal to the quantity shipped.
558 -- If any receipt line is received into multiple lots,
559 -- all the transaction table entries get the same line id. Two separate
560 -- lines of receipt get separate line ids. The date can be specified in
561 -- the receipt header as any date beyond (or including) the ship date
562 -- and before (or including) the sysdate. The dates are trunc'ed, which
563 -- means that all entries for a line id get posted to one of the ledgers,
564 -- but not both, at the same time.
565 -- If a shipped line is received in 2 or more separate receipts i.e.
566 -- partial quantity once and partial quantity another time, then
567 -- the two lines get separate line ids.
568 -- If a shipped line was split, the receipt of the two shipped lines,
569 -- even if done simultaneously, gets two separate line_ids. So again,
570 -- no risk of ever getting something for the same line id in the future.
571 -- The only case for using a date filter would be if some transactions
572 -- for a line id have been posted to the ledger, and then some more
573 -- transactions for the same line id are entered but not posted.
574 -- Then we could mismatch the quantities, but for OMSO and PORC, this
575 -- should never happen.
576
577 IF initial_load
578 THEN
579 lv_led_hint := '/*+ full(subr) */';
580 ELSE
581 lv_led_hint := '';
582 END IF;
583
584 lv_sql :=
585 'INSERT INTO opi_dbi_opm_inv_led_current
586 (
587 whse_code,
588 item_id,
589 transaction_date,
590 intransit_qty,
591 intransit_value_b
592 )
593 SELECT whse_code, item_id, trans_date, sum (qty) qty, sum (val) val
594 FROM
595 -- to get the from and to organizations depending on what the FOB
596 -- is, we need to join back to the purchase order requisition lines
597 -- table, po_requisitions_lines_all.
598 -- For shipments (doc_type = OMSO) this is achieved through the
599 -- oe_order_lines_all table.
600 -- For receipts (doc_type = PORC) this is achieved through the
601 -- rcv_transactions table.
602 (SELECT w.whse_code,
603 ic_tran.item_id,
604 led.gl_trans_date trans_date,
605 ic_tran.trans_qty qty,
606 sum (led.amount_base * led.debit_credit_sign *
607 decode (mip.fob_point, -- check if need exchange rate
608 2, 1, -- FOB = receipt ==> shipping org is
609 -- owner. Since this is doc_type
610 -- OMSO, no exchange rate needed.
611 -- If FOB = shipment, then need
612 -- exchange rate to get shipping org
613 -- value
614 1, decode (nvl (led.mul_div_sign, 0),
615 -- if there is no mul_div_sign,
616 -- there will be no exchange rate,
617 -- so we return 1 i.e. no exchange
618 -- rate.
619 -- Else if mul_div_sign = 0,
620 -- multiply by exchange rate
621 0, nvl(led.exchange_rate, 1),
622 -- if mul_div_sign = 2, divide
623 -- by exchange rate
624 1, 1/(nvl (led.exchange_rate, 1))))
625 ) val
626 FROM (SELECT doc_type, doc_id, line_id, whse_code, item_id,
627 sum(-1 * trans_qty) trans_qty, trans_um,
628 gl_posted_ind,
629 completed_ind
630 FROM ic_tran_pnd
631 WHERE doc_type = ''OMSO''
632 AND completed_ind = 1 -- completed and
633 AND gl_posted_ind = 1 -- posted to perm ledger
634 GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
635 trans_um, gl_posted_ind, completed_ind
636 UNION ALL
637 -- receipts into different lots can generate two lines
638 -- in ic_tran with same line_id so collapse based on
639 -- line_id
640 SELECT doc_type, doc_id, line_id, whse_code, item_id,
641 sum (-1 * trans_qty) trans_qty, trans_um,
642 gl_posted_ind,
643 1 -- all transactions are completed in tran_cmp
644 FROM ic_tran_cmp
645 WHERE doc_type = ''OMSO''
646 AND gl_posted_ind = 1 -- posted to perm ledger
647 GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
648 trans_um, gl_posted_ind, 1
649 ) ic_tran,
650 oe_order_lines_all oola,
651 po_requisition_lines_all req,
652 mtl_interorg_parameters mip,
653 -- some transactions generate two entries in gl, so
654 -- make sure to collapse gl by line_id, doc_type ...
655 (SELECT ' || lv_led_hint || '
656 trunc (gl_trans_date) gl_trans_date, doc_id,
657 doc_type, line_id, acct_ttl_type,
658 amount_base, debit_credit_sign, exchange_rate,
659 mul_div_sign
660 FROM gl_subr_led subr
661 WHERE subledger_id BETWEEN :1
662 AND :2
663 AND trunc(subr.gl_trans_date) >= :3
664 AND acct_ttl_type = 1590
665 ) led,
666 ic_whse_mst w
667 WHERE ic_tran.doc_type = led.doc_type
668 AND ic_tran.line_id = led.line_id
669 AND ic_tran.line_id = oola.line_id -- get line id details
670 AND oola.source_document_type_id = 10 --ensure this is internal
671 --order
672 AND req.requisition_line_id = oola.source_document_line_id
673 AND mip.from_organization_id = req.source_organization_id
674 AND mip.to_organization_id = req.destination_organization_id
675 AND w.mtl_organization_id =
676 decode (mip.fob_point, --FOB selects owning org
677 2, req.source_organization_id,
678 1, req.destination_organization_id)
679 GROUP BY w.whse_code,
680 ic_tran.item_id,
681 led.gl_trans_date,
682 ic_tran.trans_qty,
683 ic_tran.line_id
684 UNION ALL
685 SELECT w.whse_code,
686 ic_tran.item_id,
687 led.gl_trans_date trans_date,
688 ic_tran.trans_qty qty,
689 sum(led.amount_base * led.debit_credit_sign *
690 decode (mip.fob_point, -- check if need exchange rate
691 1, 1, -- FOB = shipping ==> receiving org is
692 -- owner. Since this is doc_type
693 -- PORC, no exchange rate needed.
694 -- If FOB = receipt, then need
695 -- exchange rate to get shipping org
696 -- value
697 2, decode (nvl (led.mul_div_sign, 0),
698 -- if there is no mul_div_sign,
699 -- there will be no exchange rate,
700 -- so we return 1 i.e. no exchange
701 -- rate.
702 -- Else if mul_div_sign = 0,
703 -- multiply by exchange rate
704 0, nvl(led.exchange_rate, 1),
705 -- if mul_div_sign = 2, divide
706 -- by exchange rate
707 1, 1/(nvl (led.exchange_rate, 1))))
708 ) val
709 FROM (SELECT doc_type, doc_id, line_id, whse_code, item_id,
710 sum(-1 * trans_qty) trans_qty, trans_um,
711 gl_posted_ind, completed_ind
712 FROM ic_tran_pnd
713 WHERE doc_type = ''PORC''
714 AND completed_ind = 1 -- completed and
715 AND gl_posted_ind = 1 -- posted to perm ledger
716 GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
717 trans_um, gl_posted_ind, completed_ind
718 UNION ALL
719 -- receipts into different lots can generate two lines
720 -- in ic_tran with same line_id so collapse based on
721 -- line_id
722 SELECT doc_type, doc_id, line_id, whse_code, item_id,
723 sum (-1 * trans_qty) trans_qty, trans_um,
724 gl_posted_ind,
725 1 -- all transactions are completed in tran_cmp
726 FROM ic_tran_cmp
727 WHERE doc_type = ''PORC''
728 AND gl_posted_ind = 1 -- posted to perm ledger
729 GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
730 trans_um, gl_posted_ind, 1
731 ) ic_tran,
732 rcv_transactions rcv,
733 po_requisition_lines_all req,
734 mtl_interorg_parameters mip,
735 -- some transactions generate two entries in gl, so
736 -- make sure to collapse gl by line_id, doc_type ...
737 (SELECT ' || lv_led_hint || '
738 trunc (gl_trans_date) gl_trans_date, doc_id,
739 doc_type, line_id, acct_ttl_type,
740 amount_base, debit_credit_sign, exchange_rate,
741 mul_div_sign
742 FROM gl_subr_led subr
743 WHERE subledger_id BETWEEN :4
744 AND :5
745 AND trunc(subr.gl_trans_date) >= :6
746 AND acct_ttl_type = 1590 -- internal order account
747 ) led,
748 ic_whse_mst w
749 WHERE ic_tran.doc_type = led.doc_type
750 AND ic_tran.line_id = led.line_id
751 AND rcv.transaction_id = led.line_id
752 AND req.requisition_line_id = rcv.requisition_line_id
753 AND req.destination_type_code = ''INVENTORY'' -- internal order
754 AND req.source_type_code = ''INVENTORY'' -- has source and
755 -- dest type as
756 -- INVENTORY
757 AND mip.from_organization_id = req.source_organization_id
758 AND mip.to_organization_id = req.destination_organization_id
759 AND w.mtl_organization_id =
760 decode (mip.fob_point, --FOB selects owning org
761 2, req.source_organization_id,
762 1, req.destination_organization_id)
763 GROUP BY w.whse_code,
764 ic_tran.item_id,
765 led.gl_trans_date,
766 ic_tran.trans_qty,
767 ic_tran.line_id
768 ) int_order_in_transit
769 GROUP BY whse_code, item_id, trans_date';
770
771
772 EXECUTE IMMEDIATE lv_sql USING p_from_subledger_id,
773 p_to_subledger_id,
774 g_global_start_date,
775 p_from_subledger_id,
776 p_to_subledger_id,
777 g_global_start_date;
778
779 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' Internal Order transactions collected from permanent subledger.');
780 COMMIT;
781
782 EXCEPTION
783 WHEN OTHERS
784 THEN
785 bis_collection_utilities.put_line ('Refresh_IOR_LED_Current: '|| sqlerrm);
786 RAISE;
787
788 END Refresh_IOR_LED_Current;
789
790
791
792 Procedure Refresh_WIP_LED_Current
793 (
794 from_transaction_id NUMBER,
795 to_transaction_id NUMBER
796 )
797 IS
798 lv_sql VARCHAR2(32767);
799 lv_led_hint VARCHAR2(32);
800 BEGIN
801 IF initial_load
802 THEN
803 lv_led_hint := '/*+ full(l) */';
804 ELSE
805 lv_led_hint := '';
806 END IF;
807
808 lv_sql :=
809 'INSERT INTO opi_dbi_opm_inv_led_current
810 (
811 whse_code,
812 item_id,
813 transaction_date,
814 wip_value_b
815 )
816 SELECT
817 led.whse_code,
818 gmd.item_id,
819 led.transaction_date,
820 SUM(led.amount_base * gmd.cost_alloc) wip_val_b
821 FROM
822 (
823 SELECT ' || lv_led_hint || '
824 doc_id, h.wip_whse_code whse_code,
825 TRUNC(l.gl_trans_date) transaction_date,
826 SUM(l.amount_base * l.debit_credit_sign) amount_base
827 FROM gl_subr_led l, gme_batch_header h
828 WHERE
829 l.doc_type = ''PROD''
830 AND l.acct_ttl_type = 1530
831 AND l.doc_id = h.batch_id
832 AND l.subledger_id BETWEEN :1 AND :2
833 AND trunc(l.gl_trans_date) >= :3
834
835 GROUP BY l.doc_id, h.wip_whse_code, TRUNC(l.gl_trans_date)
836 ) led,
837 gme_material_details gmd
838 WHERE
839 led.doc_id = gmd.batch_id
840 AND gmd.line_type = 1
841 GROUP BY
842 led.whse_code,
843 gmd.item_id,
844 led.transaction_date';
845
846 EXECUTE IMMEDIATE lv_sql USING from_transaction_id, to_transaction_id, g_global_start_date;
847
848 bis_collection_utilities.put_line(TO_CHAR(sql%rowcount) || ' WIP transactions collected from permanent subledger.');
849 COMMIT;
850
851 EXCEPTION
852 WHEN OTHERS
853 THEN
854 bis_collection_utilities.put_line ('Refresh_WIP_LED_Current: '|| sqlerrm);
855 RAISE;
856
857 END Refresh_WIP_LED_Current;
858
859
860
861 Procedure Put_Net_Activity_to_Stg
862 IS
863 BEGIN
864 insert into OPI_DBI_OPM_INV_STG
865 (
866 organization_id,
867 /* csheu added the following line */
868 subinventory_code,
869 inventory_item_id,
870 transaction_date,
871 onhand_qty,
872 intransit_qty,
873 primary_uom,
874 onhand_value_b,
875 intransit_value_b,
876 wip_value_b
877 )
878 SELECT
879 whse.mtl_organization_id,
880 /* csheu added the following line */
881 /* stg.whse_code, */
882 /* cdaly replaced itwith the following line */
883 '-1', /* Key for Subinventory named Unassigned */
884 msi.inventory_item_id,
885 stg.transaction_date,
886 stg.onhand_qty,
887 stg.intransit_qty,
888 msi.primary_uom_code,
889 stg.onhand_value_b,
890 stg.intransit_value_b,
891 stg.wip_value_b
892 FROM
893 (
894 SELECT
895 item_id item_id,
896 whse_code whse_code,
897 transaction_date transaction_date,
898 SUM(onhand_qty) onhand_qty,
899 SUM(intransit_qty) intransit_qty,
900 SUM(onhand_value_b) onhand_value_b,
901 SUM(intransit_value_b) intransit_value_b,
902 SUM(wip_value_b) wip_value_b
903 FROM
904 (
905 SELECT
906 item_id,
907 whse_code,
908 transaction_date,
909 onhand_qty,
910 intransit_qty,
911 onhand_value_b,
912 intransit_value_b,
913 wip_value_b
914 FROM
915 opi_dbi_opm_inv_tst_current c
916 -- UNION ALL
917 -- SELECT
918 -- item_id,
919 -- whse_code,
920 -- transaction_date,
921 -- -onhand_qty,
922 -- -intransit_qty,
923 -- -onhand_value_b,
924 -- -intransit_value_b,
925 -- -wip_value_b
926 -- FROM
927 -- opi_dbi_opm_inv_tst_prior p
928 UNION ALL
929 SELECT
930 item_id,
931 whse_code,
932 transaction_date,
933 onhand_qty,
934 intransit_qty,
935 onhand_value_b,
936 intransit_value_b,
937 wip_value_b
938 FROM
939 opi_dbi_opm_inv_led_current led
940 )
941 GROUP BY
942 item_id,
943 whse_code,
944 transaction_date
945 HAVING
946 SUM(onhand_qty) <> 0
947 OR SUM(intransit_qty) <> 0
948 OR SUM(onhand_value_b) <> 0
949 OR SUM(intransit_value_b) <> 0
950 OR SUM(wip_value_b) <> 0
951 ) stg,
952 ic_item_mst_b iim,
953 ic_whse_mst whse,
954 mtl_system_items_b msi
955 WHERE
956 iim.item_id = stg.item_id
957 AND whse.whse_code = stg.whse_code
958 AND msi.organization_id = whse.mtl_organization_id
959 AND msi.segment1 = iim.item_no
960 ;
961
962 bis_collection_utilities.put_line(TO_CHAR(SQL%ROWCOUNT) || ' Subledger Net Change rows identified.');
963
964 fnd_stats.gather_table_stats(
965 ownname => g_opi_schema,
966 tabname => 'OPI_DBI_OPM_INV_STG',
967 percent => 10);
968
969 COMMIT;
970
971 EXCEPTION
972 WHEN OTHERS
973 THEN
974 bis_collection_utilities.put_line ('Put_Net_Activity_to_Stg: '|| sqlerrm);
975 RAISE;
976
977 END Put_Net_Activity_to_Stg;
978
979
980 FUNCTION Get_OPM_Net_Activity (
981 errbuf IN OUT NOCOPY VARCHAR2,
982 retcode IN OUT NOCOPY VARCHAR2
983 )
984 return NUMBER
985 IS
986 l_from_transaction_id NUMBER;
987 l_to_transaction_id NUMBER;
988
989 l_status VARCHAR2(20);
990 l_return NUMBER;
991
992 BEGIN
993 bis_collection_utilities.put_line('Start of collecting daily activity.');
994 l_return := 0;
995
996 BEGIN
997 /* SELECT
998 log.transaction_id + 1 from_transaction_id,
999 led.to_transaction_id
1000 INTO
1001 l_from_transaction_id,
1002 l_to_transaction_id
1003 FROM
1004 (
1005 SELECT /*+ NO_MERGE MAX(subledger_id) to_transaction_id
1006 FROM gl_subr_led
1007 ) led,
1008 opi_dbi_inv_value_log log
1009 WHERE
1010 log.type = 'GSL'
1011 AND log.organization_id = 0
1012 AND log.source = 2;*/
1013
1014 select MAX(subledger_id),MIN(subledger_id) into l_to_transaction_id , l_from_transaction_id
1015 FROM gl_subr_led
1016 where gl_trans_date > g_global_start_date;
1017
1018
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 bis_collection_utilities.put_line('Missing log record indicates that Initial Load of Inventory did not complete successfully.');
1022 bis_collection_utilities.put_line('Please verify the successful completion of Initial Load before submitting Incremental Load.');
1023 RAISE;
1024 END;
1025
1026 bis_collection_utilities.put_line('Starting Transaction_ID = ' || to_char(l_from_transaction_id));
1027 bis_collection_utilities.put_line('Ending Transaction_ID = ' || to_char(l_to_transaction_id));
1028 IF (l_to_transaction_id is not NULL AND l_from_transaction_id is not null) THEN
1029 Refresh_ONH_LED_Current(l_from_transaction_id, l_to_transaction_id);
1030 Refresh_RVAL_LED_Current(l_from_transaction_id, l_to_transaction_id);
1031 Refresh_ITR_LED_Current(l_from_transaction_id, l_to_transaction_id);
1032 Refresh_IOR_LED_Current(l_from_transaction_id, l_to_transaction_id);
1033 Refresh_WIP_LED_Current(l_from_transaction_id, l_to_transaction_id);
1034
1035 -- Refresh_ONH_TST_Current;
1036 -- Refresh_RVAL_TST_Current;
1037 -- Refresh_ITR_TST_Current;
1038 -- Refresh_IOR_TST_Current;
1039 -- Refresh_WIP_TST_Current;
1040
1041 Put_Net_Activity_to_Stg;
1042 END IF;
1043 --Move_ONH_TST_Current_to_Prior;
1044
1045 --put call for log
1046
1047 bis_collection_utilities.put_line('End of collecting daily activity.');
1048 commit;
1049
1050 return l_return;
1051
1052 EXCEPTION
1053 WHEN OTHERS
1054 THEN
1055 bis_collection_utilities.put_line ('Get_OPM_Net_Activity: '|| sqlerrm);
1056 RAISE;
1057
1058 END Get_OPM_Net_Activity;
1059
1060
1061
1062
1063 PROCEDURE OPM_Refresh
1064 (
1065 errbuf IN OUT NOCOPY VARCHAR2,
1066 retcode IN OUT NOCOPY VARCHAR2
1067 )
1068 IS
1069 l_list dbms_sql.varchar2_table;
1070 l_from_date DATE;
1071 l_has_missing_date BOOLEAN;
1072 l_staging NUMBER;
1073 l_count NUMBER;
1074 l_rows1 NUMBER;
1075 l_rows2 NUMBER;
1076 BEGIN
1077
1078 l_list(1) := 'BIS_GLOBAL_START_DATE';
1079 l_list(2) := 'BIS_PRIMARY_CURRENCY_CODE';
1080 IF (NOT BIS_COMMON_PARAMETERS.CHECK_GLOBAL_PARAMETERS(l_list))
1081 THEN
1082 bis_collection_utilities.put_line(
1083 'Missing global parameters. ' ||
1084 'Please setup global_start_date and primary_currency_code first.');
1085 retcode := 1;
1086 return;
1087 END IF;
1088
1089
1090
1091 -- If initial load hasn't been run yet, exit and warn.
1092
1093 -- set global variables
1094 bis_collection_utilities.put_line('Set global variables.');
1095 l_rows1 := 0;
1096 l_rows2 := 0;
1097 g_sysdate := sysdate;
1098 g_created_by := fnd_global.user_id;
1099 g_last_update_login := fnd_global.login_id;
1100 g_last_updated_by := fnd_global.user_id;
1101
1102 bis_collection_utilities.put_line('Collection started at ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1103
1104 select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into g_global_start_date from DUAL;
1105 IF g_global_start_date IS NULL
1106 THEN
1107 bis_collection_utilities.put_line('Global start date is not available. Can not proceed.');
1108 return;
1109 END IF;
1110
1111
1112
1113
1114 -- collect new activity
1115 IF (Get_OPM_Net_Activity (errbuf, retcode) = -1)
1116 THEN
1117 bis_collection_utilities.put_line('Fail to collect daily activity into staging table.');
1118 ELSE
1119 COMMIT;
1120 END IF;
1121
1122 -- SETUP/WRAPUP: take out wrapup api call, to be safe, put a commit, log message here.
1123 commit;
1124 bis_collection_utilities.put_line('Successfully refreshed inventory value base table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
1125 bis_collection_utilities.put_line(TO_CHAR(l_rows1 + l_rows2) || ' rows have been inserted from OPM');
1126 return;
1127
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 bis_collection_utilities.put_line('Failed in refreshing inventory value base table.');
1131 bis_collection_utilities.put_line(SQLERRM);
1132 retcode := SQLCODE;
1133 errbuf := SQLERRM;
1134 RAISE_APPLICATION_ERROR(-20000,errbuf);
1135 RETURN;
1136
1137 END OPM_Refresh;
1138
1139
1140 PROCEDURE Extract_OPM_Daily_Activity
1141 (
1142 errbuf IN OUT NOCOPY VARCHAR2,
1143 retcode IN OUT NOCOPY VARCHAR2,
1144 l_min_inception_date IN DATE
1145 )
1146 IS
1147 BEGIN
1148
1149 g_inception_date := l_min_inception_date;
1150 OPM_Refresh(errbuf, retcode);
1151
1152 END Extract_OPM_Daily_Activity;
1153
1154 BEGIN
1155 g_opi_appinfo := fnd_installation.get_app_info
1156 (
1157 application_short_name => 'OPI',
1158 status => g_opi_status,
1159 industry => g_opi_industry,
1160 oracle_schema => g_opi_schema
1161 );
1162
1163 END OPI_DBI_INV_VALUE_OPM_INCR_PKG;
[Home] [Help]