1 Package Body OPIMPXWI AS
2 /*$Header: OPIMXWIB.pls 120.4 2006/07/27 21:47:18 julzhang noship $ */
3
4
5 /* Profile for calculating all the inventory measures like wip issue,
6 po deliveries.
7 We only want to calculate measures when the
8 EDW_ENABLE_INV_ACTIVITY_MEASURES profile is set to Y. Otherwise do
9 not calculate the measures that literally double the run time of the
10 basic balance calculation.
11 */
12 g_measures_profile VARCHAR2 (50) := 'EDW_ENABLE_INV_ACTIVITY_MEASURES';
13
14 -- ---------------------------------
15 -- PRIVATE PROCEDURES
16 -- ---------------------------------
17
18 /*
19 get_cost_from_cic
20
21 Description: return the cost for an item/org from the cst_item_costs (cic)
22 table.
23
24 Parameters IN: p_org_id - organization id
25 p_item_id - inventory_item_id
26
27 Return values: l_item_cost (NUMBER) - item cost in CIC
28
29 Error Handling:
30 */
31
32 FUNCTION get_cost_from_cic (p_org_id IN NUMBER, p_item_id IN NUMBER)
33 RETURN NUMBER
34 IS
35
36 -- procedure name
37 proc_name VARCHAR2(20) := 'get_cost_from_cic';
38
39 -- item cost from cic
40 l_item_cost NUMBER; -- no default needed since CIC must have some cost
41
42 -- Cursor to get the item cost from the CIC (cst_item_costs). If there
43 -- are no cost updates ever on the item, then the CIC stores the most
44 -- historical cost.
45 CURSOR cic_item_cost_cur (p_org_id NUMBER, p_item_id NUMBER)
46 IS
47 SELECT cic.item_cost item_cost
48 FROM cst_item_costs cic
49 WHERE cic.organization_id = p_org_id
50 AND cic.inventory_item_id = p_item_id
51 and cost_type_id=1;
52
53 BEGIN
54
55 -- Fetch the cost from the CIC - should always get some cost
56 OPEN cic_item_cost_cur (p_org_id, p_item_id);
57 FETCH cic_item_cost_cur INTO l_item_cost;
58 CLOSE cic_item_cost_cur;
59
60 return nvl(l_item_cost,0);
61
62 EXCEPTION
63 WHEN OTHERS
64 THEN
65 NULL;
66 -- DBMS_OUTPUT.PUT_LINE (proc_name || ':' || sqlerrm);
67 END get_cost_from_cic;
68
69
70 /*
71 std_costing_org_item_cost
72
73 Description: Gets the cost for an inventory item in a standard costing org
74 for a given date.
75
76 The mcacd (mtl_cst_actual_cost_details) does not store the
77 the new cost in the actual_cost column of a transaction ID
78 corresponding to a standard cost update (SCU). When we look
79 up the item cost for an item with a SCU which:
80 1. Was made prior to the start date of the collection program
81 2. Was the last transaction on the item before the start
82 date of the collection program,
83 we get the wrong cost from the actual_cost column of mcacd
84 as the starting cost for the collection program.
85
86 To correct this, we need to do the following:
87 1. Get the historical item cost from the csc (cst_standard_costs)
88 table. This table has the historical costs after an SCU is
89 made. We just need the latest cost prior to the start date.
90 If there is more than one SCU on the same day, use the latest
91 cost on that day.
92 2. If csc is has no data prior to the start date, but has data
93 after the start date,
94 ----Then use the cost in the mmt (mtl_material_transactions)
95 because the cic (cst_item_costs) no longer has the
96 historical cost.
97 ----Else use the cost in the CIC
98
99 Parameters IN: p_org_id - standard costing organization_id
100 p_item_id - inventory item id
101 p_date - date for which we need cost
102
103 Return values: item_cost (NUMBER) - item cost
104
105 Error Handling:
106
107 Date Author Action
108 25th Sept, 2002 Dinkar Gupta Wrote function
109
110 */
111 FUNCTION std_costing_org_item_cost (p_org_id IN NUMBER, p_item_id IN NUMBER,
112 p_date IN DATE)
113 RETURN NUMBER
114 IS
115
116 -- procedure name
117 proc_name VARCHAR2(30) := 'std_costing_org_item_cost';
118
119 -- Cursor to get the historical cost from the cst_standard_costs (csc)
120 -- table. Need the latest cost in the csc prior to the given date
121 CURSOR latest_csc_cost_to_date_cur (p_org_id NUMBER, p_item_id NUMBER,
122 p_cost_date DATE)
123 IS
124 SELECT csc.standard_cost unit_cost
125 FROM cst_standard_costs csc
126 WHERE csc.organization_id = p_org_id
127 AND csc.inventory_item_id = p_item_id
128 AND csc.standard_cost_revision_date =
129 (SELECT max(csc2.standard_cost_revision_date)
130 FROM cst_standard_costs csc2
131 WHERE csc2.organization_id = p_org_id
132 AND csc2.inventory_item_id = p_item_id
133 AND csc2.standard_cost_revision_date <
134 trunc(p_cost_date) + 1);
135
136 -- Cursor to get all the entries in the cst_standard_cost table for a
137 -- given item/org. If the cost on date cursor returns nothing, then
138 -- we need to know whether there have been cost updates after the
139 -- the date in question.
140 -- Since we have already checked for cost updates prior to the given
141 -- date in the latest_csc_cost_to_date_cur, we only need to check if
142 -- there were ever any cost updates on the item/org.
143
144 CURSOR all_csc_costs_post_date_cur (p_org_id NUMBER, p_item_id NUMBER,
145 p_cost_date DATE)
146 IS
147 SELECT csc.standard_cost unit_cost
148 FROM cst_standard_costs csc
149 WHERE csc.organization_id = p_org_id
150 AND csc.inventory_item_id = p_item_id;
151
152 -- Cursor to get the historical item cost from the
153 -- mtl_material_transactions (mmt). If there are no cost updates prior
154 -- to the date, but one after the given date, then the historical cost
155 -- cannot be obtained from the csc or the cic. We need to go back to the
156 -- mmt.
157 -- digupta 07/10/02 -- filtered out certain transactions that do not
158 -- affect inventory quantity or balance.
159 CURSOR mmt_historical_cost_cur (p_org_id NUMBER, p_item_id NUMBER,
160 p_cost_date DATE)
161 IS
162 SELECT actual_cost
163 FROM mtl_material_transactions
164 WHERE transaction_id =
165 (SELECT max(transaction_id)
166 FROM mtl_material_transactions
167 WHERE inventory_item_id = p_item_id
168 AND organization_id = p_org_id
169 AND actual_cost IS NOT NULL
170 AND transaction_type_id NOT IN
171 (73, 80, 25, 26, 28, 90, 91, 92,
172 55, 56, 57, 58, 87, 88, 89, 24)
173 AND organization_id = NVL(owning_organization_id, organization_id)
174 AND NVL(OWNING_TP_TYPE,2) = 2
175 AND transaction_date =
176 (SELECT max(transaction_date)
177 FROM mtl_material_transactions
178 WHERE inventory_item_id = p_item_id
179 AND organization_id = p_org_id
180 AND (transaction_date) <
181 trunc(p_cost_date) + 1
182 AND actual_cost IS NOT NULL
183 AND transaction_type_id NOT IN
184 (73, 80, 25, 26, 28, 90, 91, 92,
185 55, 56, 57, 58, 87, 88, 89, 24)));
186
187 -- cost to return -- default to 0, though we are doing everything here
188 -- to find the real cost, so a return value of 0 should be treated
189 -- suspiciously.
190 l_item_cost NUMBER := 0;
191
192 l_cost_exists_csc NUMBER;
193
194 BEGIN
195
196 OPEN latest_csc_cost_to_date_cur (p_org_id, p_item_id, p_date);
197
198 --get the latest cost
199 FETCH latest_csc_cost_to_date_cur INTO l_item_cost;
200
201 IF (latest_csc_cost_to_date_cur%NOTFOUND)
202 THEN
203
204 -- if no latest cost was found, then check to see if there were
205 -- any cost updates at all for this item.
206 OPEN all_csc_costs_post_date_cur (p_org_id, p_item_id, p_date);
207 FETCH all_csc_costs_post_date_cur INTO l_cost_exists_csc;
208
209 -- If there are cost updates after the given date, then
210 -- we must use the cost from the mmt
211 IF (all_csc_costs_post_date_cur%FOUND)
212 THEN
213
214 -- get the cost from the mmt
215 OPEN mmt_historical_cost_cur (p_org_id, p_item_id, p_date);
216 FETCH mmt_historical_cost_cur INTO l_item_cost;
217
218 IF (mmt_historical_cost_cur%NOTFOUND)
219 THEN
220
221 l_item_cost := 0;
222
223 END IF; -- IF (mmt_historical_cost_cur%NOTFOUND)
224
225 CLOSE mmt_historical_cost_cur;
226
227 ELSE -- can simply get the cost from CIC since there have never
228 -- been cost updates on this item/org
229
230 l_item_cost := get_cost_from_cic (p_org_id, p_item_id);
231
232 END IF; -- IF (all_csc_costs_post_date_cur%FOUND)
233
234 CLOSE all_csc_costs_post_date_cur;
235
236
237 END IF; -- IF (latest_csc_cost_to_date_cur%NOTFOUND)
238
239 CLOSE latest_csc_cost_to_date_cur;
240
241 return nvl(l_item_cost,0);
242
243
244 EXCEPTION
245
246 WHEN OTHERS
247 THEN
248 NULL;
249 -- DBMS_OUTPUT.PUT_LINE (proc_name || ':' || sqlerrm);
250
251 END std_costing_org_item_cost;
252
253
254 /*
255 avg_costing_org_item_cost
256
257 Description: Return the item cost for an item in an average costing org.
258 Average costing orgs store the cost in the
259 mtl_cst_actual_cost_details tables. If there is no cost there,
260 then return the cost to be 0.
261
262 Arguments: p_organization_id - organization id
263 p_item_id - inventory_item_id
264 p_cost_date - date for which we want cost.
265 p_cost_group_id - cost group of item passed in
266
267 Return values: item_cost - cost of item on the given day.
268
269 Error Handling:
270
271 Date Author Action
272 11/27/02 Dinkar Gupta Wrote function
273 */
274 FUNCTION avg_costing_org_item_cost (p_organization_id IN NUMBER,
275 p_item_id IN NUMBER, p_cost_date IN DATE,
276 p_cost_group_id IN NUMBER)
277 RETURN NUMBER
278 IS
279 proc_name VARCHAR2 (30) := 'avg_costing_org_item_cost';
280 l_item_cost NUMBER := 0;
281 l_trx_id NUMBER := NULL;
282
283 BEGIN
284
285 -- digupta 07/10/02 -- filtered out certain transactions that do not
286 -- affect inventory quantity or balance.
287 -- ltong 01/20/2003. Filtered out consigned inventory.
288 SELECT max (macd.transaction_id)
289 INTO l_trx_id
290 FROM mtl_cst_actual_cost_details macd,
291 mtl_material_transactions mmt
292 WHERE mmt.transaction_id = macd.transaction_id
293 AND mmt.organization_id = p_organization_id
294 AND mmt.inventory_item_id = p_item_id
295 AND nvl (mmt.cost_group_id, -999) = nvl (p_cost_group_id, -999)
296 AND mmt.transaction_type_id NOT IN
297 (73, 80, 25, 26, 28, 90, 91, 92,
298 55, 56, 57, 58, 87, 88, 89, 24)
299 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
300 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
301 AND mmt.transaction_date = (
302 SELECT transaction_date
303 FROM
304 (SELECT /*+ first_rows */ mt.transaction_date
305 FROM mtl_cst_actual_cost_details mcacd,
306 mtl_material_transactions mt
307 WHERE mt.transaction_id = mcacd.transaction_id
308 AND mt.transaction_date < p_cost_date + 1
309 AND mt.organization_id = p_organization_id
310 AND mt.inventory_item_id = p_item_id
311 AND mt.transaction_type_id NOT IN
312 (73, 80, 25, 26, 28, 90, 91, 92, 55, 56,
313 57, 58, 87, 88, 89, 24)
314 AND nvl (mt.cost_group_id,-999) = nvl (p_cost_group_id,
315 -999)
316 ORDER BY mt.transaction_date DESC)
317 WHERE rownum = 1);
318
319 IF(l_trx_id IS NULL) THEN -- {
320 l_item_cost:=0;
321 ELSE
322 SELECT SUM(macd.new_cost)
323 INTO l_item_cost
324 FROM mtl_cst_actual_cost_details macd
325 WHERE macd.transaction_id = l_trx_id
326 AND macd.organization_id = p_organization_id; /* Bug 3661478 - add filter on organization_id*/
327
328 END IF; --} l_trx_id is null
329
330 RETURN l_item_cost;
331
332 EXCEPTION
333 WHEN NO_DATA_FOUND -- could not get a trx_id in mcacd
334 THEN
335 l_item_cost := 0;
336
337 WHEN OTHERS
338 THEN
339 NULL;
340 EDW_LOG.PUT_LINE (proc_name || ':' || sqlerrm);
341 -- DBMS_OUTPUT.PUT_LINE (proc_name || ':' || sqlerrm);
342
343 END avg_costing_org_item_cost;
344
345 -- ---------------------------------
346 -- PUBLIC PROCEDURES
347 -- ---------------------------------
348
349 -----------------------------------------------------------
350 -- PROCEDURE PUSH
351 -----------------------------------------------------------
352 PROCEDURE opi_extract_ids(p_from_date IN DATE,
353 p_to_date IN DATE,
354 p_org_code IN VARCHAR2) IS
355
356 inv_from_date DATE;
357 wip_from_date DATE;
358 inv_trx_id NUMBER;
359 wip_trx_id NUMBER;
360 inv_trx_date DATE;
361 wip_trx_date DATE;
362 to_date DATE;
363 org_id NUMBER;
364 l_statement NUMBER;
365 l_errnum NUMBER;
366 l_retcode VARCHAR2(100);
367 l_errbuf VARCHAR2(200);
368 status NUMBER;
369 l_first_push NUMBER;
370 l_txn_flag NUMBER;
371 select_cursor NUMBER:=0;
372 l_from_date DATE;
373 l_to_date DATE;
374 l_edw_start_date_org DATE := null;
375 no_from_date EXCEPTION;
376 l_exit NUMBER;
377 l_print_date DATE;
378
379 -- Cursor to fetch organizations that have MMT or WT transactions
380 -- between the from and to date.
381
382 cursor c_inv_org is
383 select mp.organization_id
384 from mtl_parameters mp
385 where
386 mp.process_enabled_flag <> 'Y' AND
387 exists
388 (select 'there are transactions'
389 from mtl_material_transactions mmt
390 where mmt.organization_id = mp.organization_id
391 and mmt.transaction_date between l_from_date and l_to_date)
392 or exists
393 (select 'there are transactions'
394 from wip_transactions wt
395 where wt.organization_id = mp.organization_id
396 and wt.transaction_date between l_from_date and l_to_date);
397
398
399
400 -- Cursor to fetch organizations that have MMT or WT transactions
401 -- between the from date and latest trx date in MMT and WT and the
402 -- orgs in MOQ.
403
404 cursor c_inv_org_first_push is
405 select mp.organization_id from
406 mtl_parameters mp,
407 (select distinct organization_id
408 from mtl_material_transactions
409 where transaction_date >= l_from_date
410 UNION
411 select distinct organization_id
412 from wip_transactions
413 where transaction_date >= l_from_date
414 UNION
415 select distinct organization_id
416 from mtl_onhand_quantities) mtl
417 where mp.organization_id = mtl.organization_id and
418 mp.process_enabled_flag <> 'Y';
419
420 --testing purpose rjin
421 /*
422 CURSOR c_inv_org IS
423 SELECT mp.organization_id
424 from mtl_parameters mp
425 WHERE organization_id IN (606);
426
427 CURSOR c_inv_org_first_push IS
428 SELECT mp.organization_id
429 from mtl_parameters mp
430 WHERE organization_id IN (606);
431 */
432 -- Cursor to get the transaction dates from the latest push of each org.
433 -- When a fetch is performed, the oldest of those dates will be selected
434 -- to be used as default from date if none passed by the calling program.
435
436 CURSOR c_txn_date is
437 SELECT max(last_push_inv_txn_date) l_date
438 FROM opi_ids_push_date_log
439 GROUP BY organization_id
440 UNION
441 SELECT max(last_push_wip_txn_date) l_date
442 FROM opi_ids_push_date_log
443 GROUP BY organization_id
444 ORDER BY 1;
445
446 -- cursor to get all the periods spanned by this push
447 CURSOR l_extraction_periods_csr ( p_organization_id NUMBER,
448 p_from_date DATE, p_to_date DATE) IS
449 SELECT Trunc(period_start_date) start_date,
450 Trunc(schedule_close_date) end_date
451 FROM org_acct_periods
452 WHERE organization_id = p_organization_id
453 AND (( period_start_date between p_from_date
454 and p_to_date )
455 OR( schedule_close_date between p_from_date
456 and p_to_date )
457 OR
458 ( (p_from_date between period_start_date and schedule_close_date)
459 AND (p_to_date between period_start_date and schedule_close_date) )
460 )
461 ORDER BY start_date;
462
463 l_extraction_periods_rec l_extraction_periods_csr%ROWTYPE;
464
465 BEGIN
466
467 g_org_error := false;
468
469 /*--------------------------------------------------------
470 -- Looping thru cursor to process extraction for each org.
471 The following steps are done for each org:
472 . get process dates
473 . purge previous push log data for closed periods
474 . calculate inventory balances
475 . calculate wip balances
476 . write to opi_ids_push_date_log
477 Process for each org is a commit cycle. If an error occurs
478 only data for that currently processed org is rolled back.
479 ---------------------------------------------------------*/
480
481 -- Make sure from and to date are not null
482
483 EDW_LOG.PUT_LINE('At start EXTRACT IDS LOG');
484 select sysdate into l_print_date from dual;
485 EDW_LOG.PUT_LINE('Start time: ' ||
486 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
487
488 if p_from_date is null then -- check from date
489 l_statement := 10;
490
491 open c_txn_date;
492 fetch c_txn_date into l_from_date;
493
494 if c_txn_date%NOTFOUND then -- check row existence
495 close c_txn_date;
496 l_errnum := 0;
497 l_retcode := 'No start date for process';
498 l_errbuf := 'This is the first extract process, '
499 || 'you must enter a start date';
500 raise no_from_date;
501 end if; -- end check row existence
502 close c_txn_date;
503 else
504 l_from_date := p_from_date;
505 end if; -- end check from date
506
507 if p_to_date is null then
508 l_to_date := sysdate;
509 else
510 l_to_date := p_to_date;
511 end if;
512
513
514 EDW_LOG.PUT_LINE('l_from_date'||to_char(l_from_date,'DD-MON-YYYY hh24:mi:ss'));
515 EDW_LOG.PUT_LINE('l_to_date'||to_char(l_to_date,'DD-MON-YYYY hh24:mi:ss'));
516
517 select sum(1)
518 into select_cursor
519 from opi_ids_push_date_log
520 where rownum < 2;
521
522 if (p_org_code IS NOT NULL) then
523 BEGIN
524 select mp.organization_id into org_id
525 from mtl_parameters mp
526 where organization_code = p_org_code
527 and mp.process_enabled_flag <> 'Y';
528 l_exit := 0;
529 EXCEPTION
530 when NO_DATA_FOUND then
531 EDW_LOG.PUT_LINE('Invalid organization code, please verify.');
532 l_errnum := 0;
533 l_retcode := 'Invalid organization code.';
534 l_errbuf := 'Please provide a valid inventory organization code.';
535 return;
536 END;
537 EDW_LOG.PUT_LINE('Processing single org');
538 else
539 if (select_cursor > 1) then
540 OPEN c_inv_org;
541 EDW_LOG.PUT_LINE('Processing cursor c_inv_org');
542 else
543 OPEN c_inv_org_first_push;
544 EDW_LOG.PUT_LINE('Processing cursor c_inv_org_first_push');
545 end if;
546 end if;
547
548 /*-------------------------
549 Start cursor loop
550 -------------------------*/
551 l_statement := 20;
552
553
554 LOOP
555
556 if (p_org_code IS NOT NULL) then
557 if (l_exit = 1) then
558 exit;
559 end if;
560 l_exit := 1;
561 else
562 if (select_cursor > 1) then
563 FETCH c_inv_org into org_id;
564 if (c_inv_org%NOTFOUND) then
565 CLOSE c_inv_org;
566 exit;
567 end if;
568 else
569 FETCH c_inv_org_first_push into org_id;
570 if (c_inv_org_first_push%NOTFOUND) then
571 CLOSE c_inv_org_first_push;
572 exit;
573 end if;
574 end if;
575 end if;
576
577
578 EDW_LOG.PUT_LINE('*********************************************');
579
580 EDW_LOG.PUT_LINE('Start extraction process for organization: '
581 || to_char(org_id));
582
583 SAVEPOINT sav_org;
584
585
586 -- Get the EDW inception date for this org. We don't want to delete
587 -- the inception rows unless this is a first push
588 -- Get the EDW start date for this org, to check later whether we have
589 -- backposted transactions on the inception date. If so, we do not want
590 -- lose the beginning onhand quantities.
591 BEGIN
592 SELECT trunc (min (trx_date)) -- must drop time stamp timestamp.
593 INTO l_edw_start_date_org
594 FROM opi_ids_push_log
595 WHERE organization_id = org_id;
596 EXCEPTION
597 WHEN NO_DATA_FOUND THEN
598 NULL; -- l_edw_start_date_org would be NULL if no date found
599 -- l_edw_start_date_org := NULL; -- was the first push, so
600 -- the ids_push_log is empty
601
602 END;
603
604
605 -- -----------------------------------------------------------------
606 -- Get dates and last transaction id's to be processed for INV
607 -- and WIP -- remove . for bug 3556719
608 -- -----------------------------------------------------------------
609
610 l_statement := 30;
611
612 EDW_LOG.PUT_LINE('Calling OPIMPDAT.get_push_dates ...');
613 select sysdate into l_print_date from dual;
614 EDW_LOG.PUT_LINE('Start time: ' ||
615 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
616
617 OPIMPDAT.get_push_dates(
618 org_id,
619 trunc (p_from_date),
620 trunc (p_to_date),
621 trunc (l_from_date),
622 inv_from_date,
623 wip_from_date,
624 inv_trx_id,
625 wip_trx_id,
626 inv_trx_date,
627 wip_trx_date,
628 to_date,
629 l_first_push,
630 l_errnum,
631 l_retcode,
632 l_errbuf,
633 l_txn_flag);
634 select sysdate into l_print_date from dual;
635 EDW_LOG.PUT_LINE('End time: ' ||
636 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
637
638 if l_errnum <> 0 THEN
639 IF l_errnum <> 9999 THEN
640 process_error(l_statement,
641 l_errnum,
642 l_retcode,
643 l_errbuf);
644 ELSE
645 process_warning(l_statement,
646 l_errnum,
647 l_retcode,
648 l_errbuf);
649 END IF;
650
651 goto next_org;
652
653 end if;
654
655 EDW_LOG.PUT_LINE('inv_from_date: ' || to_char(inv_from_date,'DD-MON-YYYY hh24:mi:ss'));
656 EDW_LOG.PUT_LINE('wip_from_date: ' || to_char(wip_from_date,'DD-MON-YYYY hh24:mi:ss'));
657 EDW_LOG.PUT_LINE('to_date: ' || to_char(to_date,'DD-MON-YYYY hh24:mi:ss'));
658
659
660 /*-------------------------------------------------------------------
661 Purge old data in opi_ids_push_log table.
662 For a specified org, all rows with transaction date in a closed period
663 will be purged, EXCEPT rows with txn dates falling into a period
664 start date or a period end date. In addition, data in a closed period
665 which include the lastest pushed transaction date will not be purged.
666 --------------------------------------------------------------------*/
667 l_statement := 40;
668 EDW_LOG.PUT_LINE('Calling purge_opi_ids_push_log ...');
669 select sysdate into l_print_date from dual;
670 EDW_LOG.PUT_LINE('Start time: ' ||
671 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
672
673 -- if we are running across periods, then we must not only recompute
674 -- all the activity rows, but even the period start and end rows, so
675 -- delete the old rows.
676 -- SPECIAL CASE: Never delete the rows on the edw inception date.
677 -- However, if this is the first push, the ids_push_log is empty, so
678 -- don't bother to delete anything.
679 IF ((l_first_push <= 0) AND (l_edw_start_date_org IS NOT NULL))
680 THEN
681 OPEN l_extraction_periods_csr (org_id, inv_from_date, to_date);
682 FETCH l_extraction_periods_csr INTO l_extraction_periods_rec;
683 FETCH l_extraction_periods_csr INTO l_extraction_periods_rec;
684 IF l_extraction_periods_csr%FOUND THEN
685 -- must have more than 1 period
686 DELETE FROM opi_ids_push_log
687 WHERE trx_date BETWEEN inv_from_date AND to_date
688 AND trx_date <> l_edw_start_date_org
689 AND organization_id = org_id;
690 END IF;
691 CLOSE l_extraction_periods_csr;
692 END IF;
693
694 purge_opi_ids_push_log(org_id,
695 l_errnum,
696 l_retcode,
697 l_errbuf);
698
699 select sysdate into l_print_date from dual;
700 EDW_LOG.PUT_LINE('End time: ' ||
701 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
702
703 if l_errnum <> 0 then
704 process_error(l_statement,
705 l_errnum,
706 l_retcode,
707 l_errbuf);
708 goto next_org;
709 end if;
710
711 /*-------------------------------------------------------------------
712 Calling Inventory procedures to populate the Inv Balance and Activities
713 ---------------------------------------------------------------------*/
714
715 if l_first_push > 0 then
716
717 l_statement := 45;
718
719 DECLARE
720 CURSOR prd_start_date_csr IS
721 SELECT period_start_date
722 FROM org_acct_periods
723 WHERE organization_id = org_id
724 AND period_start_date <= p_from_date
725 AND schedule_close_date >= p_from_date;
726
727 CURSOR prd_start_date_min_csr IS
728 SELECT MIN(period_start_date)
729 FROM org_acct_periods
730 WHERE organization_id = org_id
731 AND period_start_date BETWEEN p_from_date AND p_to_date;
732
733 BEGIN
734 OPEN prd_start_date_csr;
735 FETCH prd_start_date_csr INTO inv_from_date;
736
737 IF prd_start_date_csr%notfound THEN
738 CLOSE prd_start_date_csr;
739
740 OPEN prd_start_date_min_csr;
741 FETCH prd_start_date_min_csr INTO inv_from_date;
742
743 IF inv_from_date IS NULL THEN
744 CLOSE prd_start_date_min_csr;
745 EDW_LOG.PUT_LINE('No valid period during the date range specified for Org_id :' || org_id);
746 GOTO next_org;
747 ELSE
748 CLOSE prd_start_date_min_csr;
749 END IF;
750 ELSE
751
752 CLOSE prd_start_date_csr;
753 END IF;
754
755 EDW_LOG.PUT_LINE(' first ever push, inv_from_date is '
756 || To_char(inv_from_date,'DD-MON-YYYY hh24:mi:ss') );
757
758 END ;
759
760 EDW_LOG.PUT_LINE('Calling calc_begin_inv ...');
761 select sysdate into l_print_date from dual;
762 EDW_LOG.PUT_LINE('Start time: ' ||
763 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
764
765 calc_begin_inv(inv_from_date, org_id,status);
766 select sysdate into l_print_date from dual;
767 EDW_LOG.PUT_LINE('End time: ' ||
768 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
769
770 if status > 0 then
771 l_errbuf := 'Error calling calc_begin_inv. Org id: '
772 || to_char(org_id);
773
774 process_error(l_statement,
775 l_errnum,
776 l_retcode,
777 l_errbuf);
778 goto next_org;
779 end if;
780
781 end if;
782
783 l_statement := 50;
784
785 -- -------------------------------------------------------------------
786 -- l_first_push = 2 means that there are no inv transaction for the Org
787 -- in the date range specified. But we need to build the begin balance
788 -- for them. So if l_first_push = 2 then only need to call the
789 -- calc_begin_inv
790 -- ------------------------------------------------------------------
791
792
793 if (l_first_push = 2 or l_txn_flag = 2) then
794 goto wip_calculation;
795 end if;
796
797
798 EDW_LOG.PUT_LINE('Calling calc_inv_balance ...');
799 select sysdate into l_print_date from dual;
800 EDW_LOG.PUT_LINE('Start time: ' ||
801 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
802 -- ------------------------------------------------------------------
803 -- Calling calc_inv_balance to calculate the Inv daily balance
804 -- for each organization
805 --
806 -- -----------------------------------------------------------------
807 IF l_first_push > 0 THEN
808 -- if this is the first push, then starting from inv_from_date+1
809 -- since inv_from_date is handle in calc_beg_inv
810 calc_inv_balance(inv_from_date+1,
811 to_date,org_id,
812 status);
813 ELSE
814 calc_inv_balance(inv_from_date,
815 to_date,org_id,
816 status);
817 END IF;
818 select sysdate into l_print_date from dual;
819 EDW_LOG.PUT_LINE('End time: ' ||
820 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
821
822 if status > 0 then
823 l_errbuf := 'Error calling calc_inv_balance. Org id: '
824 || to_char(org_id);
825
826 process_error(l_statement,
827 l_errnum,
828 l_retcode,
829 l_errbuf);
830 goto next_org;
831 end if;
832
833 -- if the user wants to calculate all the measures of activity, he
834 -- must have set the EDW_ENABLE_INV_ACTIVITY_MEASURES to Y. If so,
835 -- calculate all the measures.
836 -- For backward compatibility, if a customer has not implemented
837 -- the profile, then these measures should get collected too.
838 EDW_LOG.PUT_LINE ('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
839 EDW_LOG.PUT_LINE ('PROFILE ' || g_measures_profile ||' = ' ||
840 NVL (FND_PROFILE.VALUE (g_measures_profile),
841 'NULL'));
842 EDW_LOG.PUT_LINE ('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
843
844 IF (NVL (FND_PROFILE.VALUE (g_measures_profile), 'Y') = 'Y') THEN
845
846 -- --------------------------------------------------------------
847 -- Calling procedures to calculate the Inv daily activities
848 -- for each organization
849 --
850 -- --------------------------------------------------------------
851 EDW_LOG.PUT_LINE ('Calling calc_wip_completion ....');
852 select sysdate into l_print_date from dual;
853 EDW_LOG.PUT_LINE('Start time: ' ||
854 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
855 OPIMPXIN.calc_wip_completion( l_errbuf,l_retcode,inv_from_date,to_date,org_id);
856 select sysdate into l_print_date from dual;
857 EDW_LOG.PUT_LINE('End time: ' ||
858 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
859
860 if l_retcode = '2' then
861 l_errbuf := 'Error calling calc_wip_completion. Org id: '
862 || to_char(org_id);
863
864 process_error(l_statement,
865 l_errnum,
866 l_retcode,
867 l_errbuf);
868 goto next_org;
869 end if;
870
871
872
873 EDW_LOG.PUT_LINE ('Calling calc_wip_issue ....');
874 select sysdate into l_print_date from dual;
875 EDW_LOG.PUT_LINE('Start time: ' ||
876 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
877 OPIMPXIN.calc_wip_issue(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
878 select sysdate into l_print_date from dual;
879 EDW_LOG.PUT_LINE('End time: ' ||
880 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
881
882 if l_retcode = '2' then
883 l_errbuf := 'Error calling calc_wip_issue. Org id: '
884 || to_char(org_id);
885
886 process_error(l_statement,
887 l_errnum,
888 l_retcode,
889 l_errbuf);
890 goto next_org;
891 end if;
892
893 EDW_LOG.PUT_LINE ('Calling calc_assembly_return ....');
894 select sysdate into l_print_date from dual;
895 EDW_LOG.PUT_LINE('Start time: ' ||
896 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
897 OPIMPXIN.calc_assembly_return(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
898 select sysdate into l_print_date from dual;
899 EDW_LOG.PUT_LINE('End time: ' ||
900 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
901
902 if l_retcode = '2' then
903 l_errbuf := 'Error calling calc_assembly_return. Org id: '
904 || to_char(org_id);
905
906 process_error(l_statement,
907 l_errnum,
908 l_retcode,
909 l_errbuf);
910 goto next_org;
911 end if;
912
913 EDW_LOG.PUT_LINE ('Calling calc_po_deliveries ....');
914 select sysdate into l_print_date from dual;
915 EDW_LOG.PUT_LINE('Start time: ' ||
916 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
917 OPIMPXIN.calc_po_deliveries(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
918 select sysdate into l_print_date from dual;
919 EDW_LOG.PUT_LINE('End time: ' ||
920 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
921
922 if l_retcode = '2' then
923 l_errbuf := 'Error calling calc_po_deliveries. Org id: '
924 || to_char(org_id);
925
926 process_error(l_statement,
927 l_errnum,
928 l_retcode,
929 l_errbuf);
930 goto next_org;
931 end if;
932
933 EDW_LOG.PUT_LINE ('Calling calc_value_to_orgs ....');
934 select sysdate into l_print_date from dual;
935 EDW_LOG.PUT_LINE('Start time: ' ||
936 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
937 OPIMPXIN.calc_value_to_orgs(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
938 select sysdate into l_print_date from dual;
939 EDW_LOG.PUT_LINE('End time: ' ||
940 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
941
942 if l_retcode = '2' then
943 l_errbuf := 'Error calling calc_value_to_orgs. Org id: '
944 || to_char(org_id);
945
946 process_error(l_statement,
947 l_errnum,
948 l_retcode,
949 l_errbuf);
950 goto next_org;
951 end if;
952
953 EDW_LOG.PUT_LINE ('Calling calc_value_from_orgs ....');
954 select sysdate into l_print_date from dual;
955 EDW_LOG.PUT_LINE('Start time: ' ||
956 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
957 OPIMPXIN.calc_value_from_orgs(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
958 select sysdate into l_print_date from dual;
959 EDW_LOG.PUT_LINE('End time: ' ||
960 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
961
962 if l_retcode = '2' then
963 l_errbuf := 'Error calling calc_value_from_orgs. Org id: '
964 || to_char(org_id);
965
966 process_error(l_statement,
967 l_errnum,
968 l_retcode,
969 l_errbuf);
970 goto next_org;
971 end if;
972
973 EDW_LOG.PUT_LINE ('Calling calc_customer_shipment ....');
974 select sysdate into l_print_date from dual;
975 EDW_LOG.PUT_LINE('Start time: ' ||
976 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
977 OPIMPXIN.calc_customer_shipment(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
978 select sysdate into l_print_date from dual;
979 EDW_LOG.PUT_LINE('End time: ' ||
980 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
981
982 if l_retcode = '2' then
983 l_errbuf := 'Error calling calc_customer_shipment. Org id: '
984 || to_char(org_id);
985
986 process_error(l_statement,
987 l_errnum,
988 l_retcode,
989 l_errbuf);
990 goto next_org;
991 end if;
992
993 EDW_LOG.PUT_LINE ('Calling calc_inv_adjustment ....');
994 select sysdate into l_print_date from dual;
995 EDW_LOG.PUT_LINE('Start time: ' ||
996 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
997 OPIMPXIN.calc_inv_adjustment(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
998 select sysdate into l_print_date from dual;
999 EDW_LOG.PUT_LINE('End time: ' ||
1000 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1001
1002 if l_retcode = '2' then
1003 l_errbuf := 'Error calling calc_inv_adjustment. Org id: '
1004 || to_char(org_id);
1005
1006 process_error(l_statement,
1007 l_errnum,
1008 l_retcode,
1009 l_errbuf);
1010 goto next_org;
1011 end if;
1012
1013 EDW_LOG.PUT_LINE ('Calling calc_total_issue ....');
1014 select sysdate into l_print_date from dual;
1015 EDW_LOG.PUT_LINE('Start time: ' ||
1016 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1017 OPIMPXIN.calc_total_issue(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
1018 select sysdate into l_print_date from dual;
1019 EDW_LOG.PUT_LINE('End time: ' ||
1020 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1021
1022 if l_retcode = '2' then
1023 l_errbuf := 'Error calling calc_total_issue. Org id: '
1024 || to_char(org_id);
1025
1026 process_error(l_statement,
1027 l_errnum,
1028 l_retcode,
1029 l_errbuf);
1030 goto next_org;
1031 end if;
1032
1033 EDW_LOG.PUT_LINE ('Calling calc_total_receipt ....');
1034 select sysdate into l_print_date from dual;
1035 EDW_LOG.PUT_LINE('Start time: ' ||
1036 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1037 OPIMPXIN.calc_total_receipt(l_errbuf,l_retcode,inv_from_date,to_date,org_id);
1038 select sysdate into l_print_date from dual;
1039 EDW_LOG.PUT_LINE('End time: ' ||
1040 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1041
1042 if l_retcode = '2' then
1043 l_errbuf := 'Error calling calc_total_receipt. Org id: '
1044 || to_char(org_id);
1045
1046 process_error(l_statement,
1047 l_errnum,
1048 l_retcode,
1049 l_errbuf);
1050 goto next_org;
1051 end if;
1052
1053 END IF; -- end of calculating activity measures based on profile option
1054
1055 EDW_LOG.PUT_LINE ('Calling calc_intrst_balance ....');
1056 select sysdate into l_print_date from dual;
1057 EDW_LOG.PUT_LINE('Start time: ' ||
1058 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1059 calc_intrst_balance(inv_from_date,to_date,org_id,status);
1060 select sysdate into l_print_date from dual;
1061 EDW_LOG.PUT_LINE('End time: ' ||
1062 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1063
1064 if status > 0 then
1065 l_errbuf := 'Error calling calc_intrst_balance. Org id: '
1066 || to_char(org_id);
1067
1068 process_error(l_statement,
1069 l_errnum,
1070 l_retcode,
1071 l_errbuf);
1072 goto next_org;
1073 end if;
1074
1075 <<wip_calculation>>
1076 /*-------------------------------------------------------------------
1077 Calling WIP procedures to populate the WIP Balance and Activities
1078 ---------------------------------------------------------------------*/
1079 l_statement := 60;
1080
1081 -- Call wip procedure regardless of l_txn_flag, because both MMT
1082 -- and WT must be accumulated for WIP balances.
1083
1084 EDW_LOG.PUT_LINE('Calling OPIMPXWP.calc_wip_balance ...');
1085 select sysdate into l_print_date from dual;
1086 EDW_LOG.PUT_LINE('Start time: ' ||
1087 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1088 OPIMPXWP.calc_wip_balance(org_id,
1089 inv_from_date,
1090 wip_from_date,
1091 inv_trx_id,
1092 wip_trx_id,
1093 to_date,
1094 l_first_push,
1095 l_errnum,
1096 l_retcode,
1097 l_errbuf);
1098 select sysdate into l_print_date from dual;
1099 EDW_LOG.PUT_LINE('End time: ' ||
1100 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1101
1102 if l_errnum <> 0 then
1103 process_error(l_statement,
1104 l_errnum,
1105 l_retcode,
1106 l_errbuf);
1107 goto next_org;
1108 end if;
1109
1110 -- ------------------------------------------------------------------
1111 -- Procedure calc_prd_start_end to insert/update records for
1112 -- the last and first day of the period.
1113 --
1114 -- ------------------------------------------------------------------
1115
1116 EDW_LOG.PUT_LINE ('Calling calc_prd_start_end ....');
1117 select sysdate into l_print_date from dual;
1118 EDW_LOG.PUT_LINE('Start time: ' ||
1119 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1120 opi_edw_ids_calc.calc_prd_start_end ( inv_from_date,
1121 to_date,
1122 org_id,
1123 status);
1124 select sysdate into l_print_date from dual;
1125 EDW_LOG.PUT_LINE('End time: ' ||
1126 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1127
1128 if status > 0 then
1129 l_errbuf := 'Error calling calc_prd_start_end. Org id: '
1130 || to_char(org_id);
1131
1132 process_error(l_statement,
1133 l_errnum,
1134 l_retcode,
1135 l_errbuf);
1136 goto next_org;
1137 end if;
1138
1139 insert into opi_ids_push_date_log
1140 (organization_id,
1141 last_push_date,
1142 last_push_inv_txn_id,
1143 last_push_inv_txn_date,
1144 last_push_wip_txn_id,
1145 last_push_wip_txn_date,
1146 creation_date,
1147 last_update_date)
1148 values
1149 (org_id,
1150 sysdate,
1151 inv_trx_id,
1152 trunc (inv_trx_date),
1153 wip_trx_id,
1154 trunc (wip_trx_date),
1155 sysdate,
1156 sysdate);
1157
1158 commit;
1159
1160 <<next_org>> -- label
1161
1162 EDW_LOG.PUT_LINE('Extraction completed for org '|| to_char(org_id));
1163
1164 end loop; -- end c_inv_org loop
1165
1166 EDW_LOG.PUT_LINE ('Done with inv balance extraction in opi_extract_ids.'); -- Remove . for bug 3556719
1167 select sysdate into l_print_date from dual;
1168 EDW_LOG.PUT_LINE('End time: ' ||
1169 to_char (sysdate, 'DD-MON-YY HH24:MI:SS'));
1170
1171 EXCEPTION
1172
1173 WHEN no_from_date THEN
1174 EDW_LOG.PUT_LINE('OPIMPXWI.opi_extract_ids - Error at statement ('
1175 || to_char(l_statement)
1176 || ')');
1177
1178 EDW_LOG.PUT_LINE('Error Number: ' || to_char(l_errnum));
1179 EDW_LOG.PUT_LINE('Error Code: ' || l_retcode);
1180 EDW_LOG.PUT_LINE('Error Message: ' || l_errbuf);
1181
1182 WHEN others then
1183 rollback;
1184 l_errnum := SQLCODE;
1185 l_errbuf := 'OPIMXWI.opi_extract_ids ('
1186 || to_char(l_statement)
1187 || '): '
1188 || substr(SQLERRM, 1,200);
1189
1190 EDW_LOG.PUT_LINE('OPIMPXWI.opi_extract_ids - Error at statement ('
1191 || to_char(l_statement)
1192 || ')');
1193
1194 EDW_LOG.PUT_LINE('Error Number: ' || to_char(l_errnum));
1195 EDW_LOG.PUT_LINE('Error Code: ' || l_retcode);
1196 EDW_LOG.PUT_LINE('Error Message: ' || l_errbuf);
1197
1198 END opi_extract_ids;
1199
1200
1201 /*}{--------------------------------------------------------------
1202 PRIVATE PROCEDURE: CALC_INV_BALANCE
1203 -----------------------------------------------------------------*/
1204
1205
1206 PROCEDURE calc_inv_balance(p_from_date IN Date,
1207 p_to_date IN Date,
1208 Org_id IN Number,
1209 status OUT NOCOPY Number) IS ---- bug 3589921 added nocopy
1210
1211 l_trx_date DATE;
1212 l_organization_id NUMBER;
1213 l_item_id NUMBER;
1214 l_cost_group_id NUMBER;
1215 l_revision VARCHAR2(3);
1216 l_lot_number VARCHAR2(30);
1217 l_subinventory VARCHAR2(10);
1218 l_locator NUMBER;
1219 l_item_status VARCHAR2(10);
1220 l_item_type VARCHAR2(30);
1221 l_base_uom VARCHAR2(3);
1222 total_value NUMBER;
1223 total_qty NUMBER;
1224 trx_type NUMBER;
1225 l_status NUMBER;
1226 l_statement NUMBER;
1227 l_edw_start_date_org DATE := null;
1228
1229 -- digupta 07/10/02 -- filtered out certain transactions that do not
1230 -- affect inventory quantity or balance.
1231 -- ltong 01/20/2003. Filtered out consigned inventory.
1232 -- mochawla 10/29/2003. filtered out logical transactions from Daily onhand quantity
1233 CURSOR inv_balance_no_lot IS
1234 SELECT trunc(mmt.TRANSACTION_DATE),
1235 mmt.ORGANIZATION_ID,
1236 mmt.INVENTORY_ITEM_ID,
1237 mmt.COST_GROUP_ID,
1238 mmt.REVISION,
1239 mmt.SUBINVENTORY_CODE,
1240 mmt.LOCATOR_ID,
1241 sum(mmt.PRIMARY_QUANTITY)
1242 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1243 MTL_SYSTEM_ITEMS msi
1244 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1245 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1246 AND mmt.ORGANIZATION_ID=Org_id
1247 AND msi.LOT_CONTROL_CODE = 1
1248 AND mmt.transaction_date >= p_from_date
1249 AND mmt.transaction_date <= p_to_date
1250 AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
1251 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
1252 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
1253 AND NVL(mmt.logical_transaction, 2) <> 1 /*11.5.10 changes*/
1254 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
1255 mmt.COST_GROUP_ID,mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.locator_id
1256 ORDER BY trunc(mmt.TRANSACTION_DATE); -- added by rjin
1257
1258 -- digupta 07/10/02 -- filtered out certain transactions that do not
1259 -- affect inventory quantity or balance.
1260 -- ltong 01/20/2003. Filtered out consigned inventory.
1261 -- mochawla 10/29/2003. filtered out logical transactions from Daily onhand quantity
1262 CURSOR inv_balance_with_lot IS
1263 SELECT trunc(mmt.TRANSACTION_DATE),
1264 mmt.ORGANIZATION_ID,
1265 mmt.INVENTORY_ITEM_ID,
1266 mmt.COST_GROUP_ID,
1267 mmt.REVISION,
1268 mtln.LOT_NUMBER,
1269 mmt.SUBINVENTORY_CODE,
1270 mmt.LOCATOR_ID,
1271 sum(mtln.PRIMARY_QUANTITY)
1272 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1273 MTL_SYSTEM_ITEMS msi,
1274 MTL_TRANSACTION_LOT_NUMBERS mtln
1275 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1276 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1277 AND mmt.ORGANIZATION_ID=Org_id
1278 AND msi.LOT_CONTROL_CODE = 2
1279 AND mmt.transaction_date >= p_from_date
1280 AND mmt.transaction_date <= p_to_date
1281 AND mmt.transaction_id = mtln.transaction_id
1282 AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92,
1283 55, 56, 57, 58, 87, 88, 89, 24)
1284 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
1285 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
1286 AND NVL(mmt.logical_transaction, 2) <> 1 /*11.5.10 changes*/
1287 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
1288 mmt.COST_GROUP_ID,mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID
1289 ORDER BY trunc(mmt.TRANSACTION_DATE);
1290
1291
1292 BEGIN
1293
1294 EDW_LOG.PUT_LINE(' p_from_date= '||to_char(p_from_date,'DD-MON-YYYY hh24:mi:ss')||
1295 ' p_to_Date= '||to_char(p_to_date,'DD-MON-YYYY hh24:mi:ss')||' org= '||Org_id);
1296
1297
1298 /*----------------------------------------------------------------------------------------
1299 Get the total qty transacted for the group (date,item,org,cg,rev,lot,sub,locator) for
1300 non lot control items.
1301 -----------------------------------------------------------------------------------------*/
1302
1303 OPEN inv_balance_no_lot;
1304 l_lot_number := null;
1305
1306
1307 -- Get the EDW start date for this org, to check later whether we have
1308 -- backposted transactions on the inception date. If so, we do not want
1309 -- lose the beginning onhand quantities.
1310 BEGIN
1311 SELECT trunc (min (trx_date)) -- must drop time stamp timestamp.
1312 INTO l_edw_start_date_org
1313 FROM opi_ids_push_log
1314 WHERE organization_id = org_id;
1315 EXCEPTION -- don't expect to be here, because we should
1316 -- are not doing a first push and at least the
1317 -- EDW inception entries should be present. Just
1318 -- being safe.
1319 WHEN NO_DATA_FOUND THEN
1320 NULL; -- l_edw_start_date_org would be NULL if no date found
1321 -- l_edw_start_date_org := NULL; -- was the first push, so
1322 -- the ids_push_log is empty
1323 END;
1324
1325 l_statement:=1;
1326
1327 LOOP
1328
1329
1330 FETCH inv_balance_no_lot
1331 INTO l_trx_date,
1332 l_organization_id,
1333 l_item_id,
1334 l_cost_group_id,
1335 l_revision,
1336 l_subinventory,
1337 l_locator,
1338 total_qty;
1339
1340
1341 if(inv_balance_no_lot%NOTFOUND) then
1342 CLOSE inv_balance_no_lot;
1343 EXIT;
1344 end if;
1345
1346
1347 /*
1348 EDW_LOG.PUT_LINE('///////////////with no lot cursor, count is ' ||inv_balance_no_lot%rowcount );
1349
1350 EDW_LOG.PUT_LINE('l_trx_date='||l_trx_date);
1351 EDW_LOG.PUT_LINE('l_item_id = ' || l_item_id );
1352 EDW_LOG.PUT_LINE('l_cost_group_id='||to_char(l_cost_group_id));
1353 EDW_LOG.PUT_LINE('l_revision='||l_revision);
1354 EDW_LOG.PUT_LINE('l_lot_number='||l_lot_number);
1355 EDW_LOG.PUT_LINE('l_subinventory='||l_subinventory);
1356 EDW_LOG.PUT_LINE('total_qty='||to_char(total_qty));
1357 */
1358
1359
1360 SELECT INVENTORY_ITEM_STATUS_CODE,
1361 ITEM_TYPE,
1362 PRIMARY_UOM_CODE
1363 INTO l_item_status,
1364 l_item_type,
1365 l_base_uom
1366 FROM mtl_system_items
1367 WHERE inventory_item_id=l_item_id
1368 AND organization_id = l_organization_id;
1369
1370 l_statement:=2;
1371
1372 Calculate_Balance( l_trx_date,
1373 l_organization_id,
1374 l_item_id,
1375 l_cost_group_id,
1376 l_edw_start_date_org,
1377 l_revision,
1378 l_lot_number,
1379 l_subinventory,
1380 l_locator,
1381 l_item_status,
1382 l_item_type,
1383 l_base_uom,
1384 total_qty,
1385 l_status);
1386
1387 if(l_status > 0) then
1388 status:=1;
1389 return;
1390 end if;
1391
1392 l_statement:=3;
1393
1394 END LOOP;
1395
1396
1397 /*----------------------------------------------------------------------------------------
1398 Get the total qty transacted for the group (date,item,org,cg,rev,lot,sub,locator) for
1399 non lot control items.
1400 -----------------------------------------------------------------------------------------*/
1401 l_statement:=4;
1402
1403 open inv_balance_with_lot;
1404
1405 LOOP
1406
1407 FETCH inv_balance_with_lot
1408 INTO l_trx_date,
1409 l_organization_id,
1410 l_item_id,
1411 l_cost_group_id,
1412 l_revision,
1413 l_lot_number,
1414 l_subinventory,
1415 l_locator,
1416 total_qty;
1417
1418 l_statement:=5;
1419
1420
1421 if(inv_balance_with_lot%NOTFOUND) then
1422 CLOSE inv_balance_with_lot;
1423 EXIT;
1424 end if;
1425
1426
1427 /*
1428 EDW_LOG.PUT_LINE('/////within with_lot cursor count is ' ||inv_balance_with_lot%rowcount );
1429
1430 EDW_LOG.PUT_LINE('l_trx_date='||l_trx_date);
1431 EDW_LOG.PUT_LINE('l_item_id = ' || l_item_id );
1432
1433 EDW_LOG.PUT_LINE('l_cost_group_id='||to_char(l_cost_group_id));
1434 EDW_LOG.PUT_LINE('l_revision='||l_revision);
1435 EDW_LOG.PUT_LINE('l_lot_number='||l_lot_number);
1436 EDW_LOG.PUT_LINE('l_subinventory='||l_subinventory);
1437
1438 EDW_LOG.PUT_LINE('total_qty='||to_char(total_qty));
1439 */
1440
1441
1442 SELECT INVENTORY_ITEM_STATUS_CODE,
1443 ITEM_TYPE,
1444 PRIMARY_UOM_CODE
1445 INTO l_item_status,
1446 l_item_type,
1447 l_base_uom
1448 FROM mtl_system_items
1449 WHERE inventory_item_id=l_item_id
1450 AND organization_id = l_organization_id;
1451
1452 l_statement:=6;
1453
1454 Calculate_Balance( l_trx_date,
1455 l_organization_id,
1456 l_item_id,
1457 l_cost_group_id,
1458 l_edw_start_date_org,
1459 l_revision,
1460 l_lot_number,
1461 l_subinventory,
1462 l_locator,
1463 l_item_status,
1464 l_item_type,
1465 l_base_uom,
1466 total_qty,
1467 l_status);
1468
1469 if(l_status > 0) then
1470 status:=1;
1471 return;
1472 end if;
1473
1474
1475 l_statement:=7;
1476
1477 END LOOP;
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 edw_log.put_line('Error in CALC_INV_BALANCE at statement= '||l_statement);
1482 edw_log.put_line('Error Code: ' || SQLCODE );
1483 edw_log.put_line('Error Msg: ' || Sqlerrm );
1484 status := 2;
1485
1486 END CALC_INV_BALANCE;
1487
1488
1489 /*------------------------------------------------------------------------------
1490 PROCEDURE CALCULATE_BALANCE:
1491 This procedure will calculate the start and end qty and value for a key.
1492 ---------------------------------------------------------------------------------*/
1493
1494 PROCEDURE Calculate_Balance( p_trx_date IN DATE,
1495 p_organization_id IN NUMBER,
1496 p_item_id IN NUMBER,
1497 p_cost_group_id IN NUMBER,
1498 p_edw_start_date IN DATE, -- first date for org in push log
1499 p_revision IN VARCHAR2,
1500 p_lot_number IN VARCHAR2,
1501 p_subinventory IN VARCHAR2,
1502 p_locator IN NUMBER,
1503 p_item_status IN VARCHAR2,
1504 p_item_type IN VARCHAR2,
1505 p_base_uom IN VARCHAR2,
1506 p_total_qty IN NUMBER,
1507 status OUT NOCOPY NUMBER) IS -- bug 3589921 added nocopy
1508
1509 l_trx_id Number:=0;
1510 l_start_qty Number:=0;
1511 l_end_qty Number:=0;
1512 l_avg_qty Number:=0;
1513 l_last_end_val Number:=0;
1514 l_last_end_qty Number:=0;
1515 l_start_val Number:=0;
1516 l_end_val Number:=0;
1517 l_avg_val Number:=0;
1518 l_total_val Number:=0;
1519 cost_method Number;
1520 item_cost Number:=0;
1521 prev_day_item_cost Number := 0;
1522 l_max_trx_date Date := null;
1523 asset_sub Number:=0;
1524 non_expense_item VARCHAR2(1); -- digupta 11/11/02
1525 l_statement Number:=0;
1526 l_status Number:=1;
1527
1528 CURSOR last_trx is
1529 SELECT trunc(MAX(trx_date))
1530 FROM OPI_IDS_PUSH_LOG
1531 WHERE ORGANIZATION_ID = p_organization_id
1532 AND INVENTORY_ITEM_ID = p_item_id
1533 AND nvl(COST_GROUP_ID,-999) = nvl(p_cost_group_id,-999)
1534 AND nvl(REVISION,-999) = nvl(p_revision,-999)
1535 AND nvl(LOT_NUMBER,-999) = nvl(p_lot_number,-999)
1536 AND nvl(SUBINVENTORY_CODE,-999)= nvl(p_subinventory,-999)
1537 AND nvl(project_locator_id, nvl(LOCATOR_ID,-999)) = nvl(p_locator,-999) -- Suhasini Added project_locator_id,Forward port from 11.5.9.3
1538 AND trx_date < p_trx_date; -- added by rjin
1539
1540 l_ids_key VARCHAR2(240);
1541 BEGIN
1542
1543 /*
1544 EDW_LOG.PUT_LINE('total_qty='||to_char(p_total_qty));
1545 */
1546
1547 SELECT primary_cost_method
1548 INTO cost_method
1549 FROM mtl_parameters
1550 WHERE Organization_id=p_organization_id;
1551
1552
1553 OPEN last_trx;
1554
1555 FETCH last_trx
1556 INTO l_max_trx_date;
1557
1558
1559 l_statement := 1;
1560
1561
1562
1563 /*---------------------------------------------------------------------------------------
1564 The above cursor will find if the record is already existing in opi_ids_push_log for a
1565 particular item,org,costgroup,rev,lot,sub and locator. It not then the start qty and start
1566 value are 0, otherwise get the start value and qty for the last transacted record for that
1567 combination (last record) from opi_ids_push_log
1568 ------------------------------------------------------------------------------------------*/
1569
1570 l_ids_key := l_max_trx_date||'-'||p_item_id||'-'||p_organization_id||'-'||p_cost_group_id||'-'||p_revision||'-'||p_lot_number||'-'||p_subinventory||'-'||p_locator;
1571
1572 --EDW_LOG.PUT_LINE( ' ids_key ' || l_ids_key );
1573
1574 IF(l_max_trx_date is null) THEN
1575
1576 -- Since there are no entries in the push log for this item-org prior
1577 -- to this date, we just need to ensure that the date being collected
1578 -- is not the EDW inception date for this org i.e. the first date
1579 -- on which any data was ever reported for this org. If it is, then
1580 -- we must have hit a backdated transaction for this date, and are
1581 -- recollecting the start date without calculating the inception
1582 -- balances. So we must not throw away the already calculated
1583 -- beginning onhand quantities for this item-org-date.
1584 -- Both, p_trx_date and p_edw_start_date, are trunc'ed.
1585 IF ((p_trx_date <> p_edw_start_date) OR
1586 (p_edw_start_date IS NULL)) -- could be NULL in case of first push
1587 THEN
1588 l_last_end_qty := 0; -- really don't expect to be in here
1589 l_last_end_val := 0; -- because we should not be running
1590 -- Calculate_Balance with a first push
1591 -- BE CAREFUL THOUGH - this means that
1592 -- we cannot delete the edw inception rows
1593 -- and run an incremental from that day
1594 -- all over again. So don't ever delete
1595 -- edw inception rows.
1596 ELSE -- trunc (p_trx_date) = p_edw_start_date
1597
1598 l_ids_key := p_trx_date||'-'||p_item_id||'-'||p_organization_id||'-'||p_cost_group_id||'-'||p_revision||'-'||p_lot_number||'-'||p_subinventory||'-'||p_locator;
1599 Begin -- Suhasini Added exception handling, Forward port 11.5.9.3
1600 SELECT nvl(sum(nvl(beg_onh_qty, 0)),0),
1601 nvl(sum(nvl(beg_onh_val_b, 0)),0)
1602 -- if nothing is found, then there
1603 -- must never have been an
1604 -- inception qty
1605 INTO l_last_end_qty, l_last_end_val
1606 FROM opi_ids_push_log
1607 WHERE IDS_KEY = l_ids_key;
1608 Exception
1609 when others then
1610 edw_log.put_line('ids key - ' || l_ids_key );
1611 Raise;
1612 end;
1613
1614 END IF;
1615 ELSE
1616 Begin -- Suhasini Added exception handling, Forward port 11.5.9.3
1617 SELECT Nvl(end_onh_val_b,0), Nvl(end_onh_qty,0)
1618 INTO l_last_end_val, l_last_end_qty
1619 FROM OPI_IDS_PUSH_LOG
1620 WHERE IDS_KEY = l_ids_key;
1621 Exception
1622 when others then
1623 edw_log.put_line('ids key - ' || l_ids_key );
1624 Raise;
1625 end;
1626 END IF;
1627
1628 CLOSE last_trx;
1629
1630
1631 -- EDW_LOG.PUT_LINE('l_max_trx_date is ' || l_max_trx_date || 'l_last_end_qty is ' || l_last_end_qty || 'l_last_end_val is ' || l_last_end_val );
1632
1633 l_statement := 2;
1634
1635 /*--------------------------------------------------------------------------------------------------
1636 We need to take qty into account for the expense subs but should not calculate the value for
1637 Expense sub
1638 ---------------------------------------------------------------------------------------------------*/
1639
1640 IF p_subinventory IS NOT NULL THEN
1641 SELECT asset_inventory
1642 INTO asset_sub
1643 FROM mtl_secondary_inventories sub
1644 WHERE sub.SECONDARY_INVENTORY_NAME=p_subinventory
1645 AND sub.organization_id = p_organization_id;
1646
1647 if (asset_sub<>1) then
1648 item_cost:=0;
1649 prev_day_item_cost := 0;
1650 end if;
1651 END IF;
1652
1653 -- check if item is expense item
1654 SELECT inventory_asset_flag
1655 INTO non_expense_item
1656 FROM mtl_system_items
1657 WHERE organization_id = p_organization_id
1658 AND inventory_item_id = p_item_id;
1659 /*---------------------------------------------------------------------------------------------------
1660 Find the cost for the item as of last trx in the day for the key combination.
1661 So we will look for the
1662 -new_cost: for avg/fifo/lifo costing org
1663 -actual_cost: for std costing org
1664 for the max(transaction_id) for a day.
1665 -----------------------------------------------------------------------------------------------------*/
1666 /* get the item cost if this is not an expense subinventory or an expense
1667 item */
1668 IF ((asset_sub = 1 OR p_subinventory is NULL)
1669 AND (non_expense_item = 'Y')) THEN -- {
1670 IF (cost_method = 1) THEN -- is a standard costing org
1671
1672 -- get standard costing org cost specially since mcacd does
1673 -- not update the actual cost properly in a standard cost
1674 -- update scenario
1675 item_cost := std_costing_org_item_cost (p_organization_id, p_item_id,
1676 p_trx_date);
1677 -- item cost on previous day for starting balances
1678 prev_day_item_cost := std_costing_org_item_cost (p_organization_id,
1679 p_item_id,
1680 p_trx_date - 1);
1681
1682 ELSE
1683 -- item cost at the end of the day of transaction
1684 item_cost := avg_costing_org_item_cost (p_organization_id, p_item_id,
1685 p_trx_date, p_cost_group_id);
1686 -- item cost at the start of the day i.e. item cost at the end
1687 -- of last day
1688 prev_day_item_cost := avg_costing_org_item_cost (p_organization_id,
1689 p_item_id,
1690 p_trx_date - 1,
1691 p_cost_group_id);
1692
1693 END IF; -- } cost_method = 1
1694
1695 END IF; -- } (asset_sub = 1 OR p_subinventory is NULL) AND
1696 -- (non_expense_item = 'Y')
1697
1698 l_statement := 3;
1699
1700
1701 /*-------------------------------------------------------------------------------------------
1702 Calculate the ending and average value and qty for the key
1703 --------------------------------------------------------------------------------------------*/
1704 l_start_qty := l_last_end_qty;
1705 l_end_qty := l_start_qty + p_total_qty;
1706 l_avg_qty := (l_start_qty+l_end_qty)/2;
1707
1708 IF (cost_method = 1) THEN -- standard costing org - we have a previous day
1709 -- cost
1710 -- calculate values from costs
1711 l_start_val := l_start_qty * prev_day_item_cost;
1712 l_end_val := l_end_qty * item_cost;
1713 ELSE
1714 -- calculate values based on cost
1715 l_start_val := l_last_end_val;
1716 l_end_val := l_end_qty * item_cost;
1717 END IF;
1718
1719 l_avg_val := (l_start_val+l_end_val)/2;
1720
1721 /* EDW_LOG.PUT_LINE('l_start_qty = '|| l_start_qty || ' l_end_qty = ' || l_end_qty || ' l_start_val = '|| l_start_val || 'l_total_val = ' || l_total_val || 'l_end_val = ' || l_end_val ); */
1722
1723
1724 l_statement := 4;
1725
1726 OPIMPXIN.Insert_update_push_log(
1727 p_trx_date => p_trx_date ,
1728 p_organization_id => p_organization_id,
1729 p_item_id => p_item_id,
1730 p_cost_group_id => p_cost_group_id,
1731 p_revision => p_revision,
1732 p_lot_number => p_lot_number,
1733 p_subinventory => p_subinventory,
1734 p_locator => p_locator,
1735 p_item_status => p_item_status,
1736 p_item_type => p_item_type,
1737 p_base_uom => p_base_uom,
1738 p_col_name1 => 'beg_onh_qty',
1739 p_total1 => l_start_qty,
1740 p_col_name2 => 'beg_onh_val_b',
1741 p_total2 => l_start_val,
1742 p_col_name3 => 'end_onh_qty',
1743 p_total3 => l_end_qty,
1744 p_col_name4 => 'end_onh_val_b',
1745 p_total4 => l_end_val,
1746 p_col_name5 => 'avg_onh_val_b',
1747 p_total5 => l_avg_val,
1748 p_col_name6 => 'avg_onh_qty',
1749 p_total6 => l_avg_qty,
1750 selector => 2,
1751 success => l_status);
1752
1753 l_statement := 5;
1754
1755 if( l_status > 0 ) then
1756 edw_log.put_line('Error in Insert_update_push_log');
1757 status := 1;
1758 else
1759 status := 0;
1760 end if;
1761
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 edw_log.put_line('Error in Calculate_Balance at statement= '||l_statement);
1765 edw_log.put_line('Error Code: ' || SQLCODE );
1766 edw_log.put_line('Error Msg: ' || Sqlerrm );
1767 status := 1;
1768
1769 END Calculate_Balance;
1770
1771 PROCEDURE purge_opi_ids_push_log(
1772 i_org_id IN NUMBER,
1773 o_errnum OUT NOCOPY NUMBER, -- bug 3589921 added nocopy
1774 o_retcode OUT NOCOPY VARCHAR2, -- bug 3589921 added nocopy
1775 o_errbuf OUT NOCOPY VARCHAR2 -- bug 3589921 added nocopy
1776 )IS
1777
1778 l_errnum number;
1779 l_retcode varchar2(20);
1780 l_errbuf varchar2(240);
1781 l_statement number;
1782 l_purge_from_date date;
1783 l_purge_to_date date;
1784 l_last_push_inv_date date;
1785 l_last_push_wip_date date;
1786 l_last_push_date date;
1787 l_latest_push_date date;
1788 no_purge exception;
1789
1790 BEGIN
1791
1792 -- Initialize local variables
1793 l_errnum := 0;
1794 l_retcode := '';
1795 l_errbuf := '';
1796 l_purge_from_date := null;
1797 l_purge_to_date := null;
1798 l_last_push_inv_date := null;
1799 l_last_push_wip_date := null;
1800 l_last_push_date := null;
1801 l_latest_push_date := null;
1802
1803
1804 -- Get the min date of opi_ids_push_log rows which are not period start
1805 -- or period end dates. Rows with that date would be the first ones to
1806 -- be purged if they belong to an 'eligible' closed period, i.e. a
1807 -- period that does not include the last push transaction date.
1808
1809 l_statement := 10;
1810 select min(trx_date)
1811 into l_purge_from_date
1812 from opi_ids_push_log ipl
1813 where push_flag = 0 -- already pushed
1814 and period_flag is null -- not start or end period rows
1815 and organization_id = i_org_id;
1816
1817 if (l_purge_from_date is NOT NULL) then -- { We do not have any rows to purge. Return Success
1818
1819 l_statement := 20;
1820
1821 -- Get the last Inv and WIP push for the org
1822
1823 select max(last_push_inv_txn_date),max(last_push_wip_txn_date)
1824 into l_last_push_inv_date,
1825 l_last_push_wip_date
1826 from opi_ids_push_date_log
1827 where organization_id = i_org_id;
1828
1829 --l_last_push_date := min(l_last_push_inv_date,l_last_push_wip_date);
1830
1831 if (l_last_push_inv_date is NOT NULL AND l_last_push_wip_date IS NOT NULL) THEN
1832
1833 if l_last_push_inv_date <= l_last_push_wip_date then
1834 l_last_push_date := l_last_push_inv_date;
1835 else
1836 l_last_push_date := l_last_push_wip_date;
1837 end if;
1838 elsif (l_last_push_inv_date is NOT NULL) Then -- If INV is not null and WIP is null
1839 l_last_push_date := l_last_push_inv_date;
1840 else
1841 l_last_push_date := l_last_push_wip_date; -- If Inv is null OR both Inv and WIP are null
1842 end if;
1843
1844 -- Get the end date of the latest closed period which can be purged, i.e.
1845 -- a closed period which does not include the last push date
1846
1847 if(l_last_push_date is NOT NULL) THEN --{
1848 l_statement := 40;
1849 select max(oap.schedule_close_date)
1850 into l_purge_to_date
1851 from org_acct_periods oap
1852 where oap.organization_id = i_org_id
1853 and oap.period_close_date is not null
1854 and oap.schedule_close_date < l_last_push_date;
1855
1856 -- Do not purge if the transaction date of the first eligible row is
1857 -- beyond the the last date that purge can be done.
1858 -- Otherwise, purge data between those two dates
1859
1860 if(l_purge_to_date is NOT NULL) THEN
1861 if l_purge_from_date > l_purge_to_date then
1862 raise no_purge;
1863 else
1864 l_statement := 50;
1865 delete from opi_ids_push_log ipl
1866 where ipl.organization_id = i_org_id
1867 and ipl.trx_date between l_purge_from_date
1868 and l_purge_to_date
1869 and ipl.push_flag = 0
1870 and ipl.period_flag is null;
1871
1872 EDW_LOG.PUT_LINE('org id: '||i_org_id ||' from date ' ||
1873 To_char(l_purge_from_date, 'DD-MON-YYYY HH24:MI:SS') ||
1874 ' to date ' || To_char(l_purge_to_date, 'DD-MON-YYYY HH24:MI:SS')
1875 || ' delete rowcount ' || SQL%rowcount );
1876 end if;
1877 end if;
1878
1879 end if; -- } l_last_push_date is NOT NULL
1880
1881 end if; --- }
1882 o_errnum := l_errnum;
1883 o_retcode := l_retcode;
1884 o_errbuf := l_errbuf;
1885
1886 EXCEPTION
1887 WHEN no_purge then
1888 o_errnum := l_errnum;
1889 o_errbuf := l_errbuf;
1890 EDW_LOG.PUT_LINE('OPIMXWI.purge_opi_ids_push_log - no purge; ');
1891 EDW_LOG.PUT_LINE('Data can only be purged thru '
1892 || to_char(l_purge_to_date));
1893 WHEN others then
1894 o_errnum := SQLCODE;
1895 o_errbuf := 'OPIMXWI.purge_opi_ids_push_log ('
1896 || to_char(l_statement)
1897 || '): '
1898 || substr(SQLERRM, 1,200);
1899
1900 END purge_opi_ids_push_log;
1901
1902 PROCEDURE process_error(
1903 i_stmt_num IN NUMBER,
1904 i_errnum IN NUMBER,
1905 i_retcode IN VARCHAR2,
1906 i_errbuf IN VARCHAR2
1907 )IS
1908
1909 BEGIN
1910
1911 rollback to sav_org;
1912
1913 EDW_LOG.PUT_LINE('OPIMPXWI.opi_extract_ids - Error at statement ('
1914 || to_char(i_stmt_num)
1915 || ')');
1916
1917 EDW_LOG.PUT_LINE('Error Number: ' || to_char(i_errnum));
1918 EDW_LOG.PUT_LINE('Error Code: ' || i_retcode);
1919 EDW_LOG.PUT_LINE('Error Message: ' || i_errbuf);
1920
1921 g_org_error := true;
1922 END process_error;
1923
1924 PROCEDURE process_warning(
1925 i_stmt_num IN NUMBER,
1926 i_errnum IN NUMBER,
1927 i_retcode IN VARCHAR2,
1928 i_errbuf IN VARCHAR2) IS
1929
1930 BEGIN
1931
1932 rollback to sav_org;
1933
1934 EDW_LOG.PUT_LINE('OPIMPXWI.opi_extract_ids - Warning at statement ('
1935 || to_char(i_stmt_num)
1936 || ')');
1937
1938 EDW_LOG.PUT_LINE('Warning Number: ' || to_char(i_errnum));
1939 EDW_LOG.PUT_LINE('Warning Code: ' || i_retcode);
1940 EDW_LOG.PUT_LINE('Warning Message: ' || i_errbuf);
1941
1942 g_org_error := true;
1943 END process_warning;
1944
1945 -- -------------------------------------------------------------------------------
1946 -- Procedure calc_begin_inv to calculate the begin Balance for the first time
1947 -- the PUSH is run.
1948 -- ------------------------------------------------------------------------------
1949
1950 PROCEDURE calc_begin_inv(p_from_date IN DATE,
1951 Org_id IN Number,
1952 status OUT NOCOPY Number) IS -- bug 3589921 added nocopy
1953
1954 cost_method Number;
1955 l_item_id NUMBER;
1956 l_cost_group_id NUMBER;
1957 l_revision VARCHAR2(3);
1958 l_lot_number VARCHAR2(30);
1959 l_subinventory VARCHAR2(10);
1960 l_locator NUMBER;
1961 current_onhand_qty NUMBER;
1962 net_transacted_quantity NUMBER;
1963 beg_onh_qty NUMBER;
1964 beg_onh_val NUMBER;
1965 item_cost NUMBER;
1966 l_status NUMBER;
1967 l_item_status VARCHAR2(10);
1968 l_item_type VARCHAR2(30);
1969 l_base_uom VARCHAR2(3);
1970 l_trx_id NUMBER;
1971 asset_sub Number:=0; -- digupta 10/08/02
1972 non_expense_item VARCHAR2(1); -- digupta 11/11/02
1973 ctr1 NUMBER:=0;
1974 ctr2 NUMBER:=0;
1975 ctr3 NUMBER:=0;
1976
1977 from_date_transacted_quantity NUMBER := 0;
1978 end_onh_qty NUMBER;
1979 end_onh_val NUMBER;
1980 avg_onh_qty NUMBER;
1981 avg_onh_val NUMBER;
1982
1983 -- digupta 07/10/02 -- filtered out certain transactions that do not
1984 -- affect inventory quantity or balance.
1985 -- ltong 01/20/2003. Filtered out consigned inventory.
1986 -- mochawla 10/29/2003. filtered out logical transactions from quantity and balance
1987 CURSOR beg_inv_balance IS
1988 SELECT mmt.INVENTORY_ITEM_ID,
1989 mmt.COST_GROUP_ID,
1990 mmt.REVISION,
1991 decode(msi.LOT_CONTROL_CODE,2,nvl(mtln.LOT_NUMBER,'-99'),NULL) LOT_NUMBER, --bug 4561628 Forward ported
1992 mmt.SUBINVENTORY_CODE,
1993 mmt.LOCATOR_ID
1994 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1995 MTL_TRANSACTION_LOT_NUMBERS mtln,
1996 mtl_system_items msi
1997 WHERE mmt.ORGANIZATION_ID=Org_id
1998 AND mmt.transaction_date >= Trunc(p_from_date)
1999 AND mmt.transaction_id = mtln.transaction_id (+)
2000 and msi.organization_id = mmt.organization_id
2001 and msi.inventory_item_id = mmt.inventory_item_id
2002 AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92,
2003 55, 56, 57, 58, 87, 88, 89, 24)
2004 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
2005 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
2006 AND NVL(mmt.logical_transaction, 2) <> 1 /*11.5.10 changes*/
2007 GROUP BY mmt.INVENTORY_ITEM_ID, mmt.COST_GROUP_ID,mmt.REVISION,mtln.lot_number,msi.LOT_CONTROL_CODE,
2008 mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID
2009 UNION
2010 SELECT INVENTORY_ITEM_ID,
2011 COST_GROUP_ID,
2012 REVISION,
2013 LOT_NUMBER,
2014 SUBINVENTORY_CODE,
2015 LOCATOR_ID
2016 FROM mtl_onhand_quantities
2017 WHERE ORGANIZATION_ID=Org_id
2018 GROUP BY INVENTORY_ITEM_ID,COST_GROUP_ID,REVISION,LOT_NUMBER,SUBINVENTORY_CODE,locator_id;
2019
2020
2021 BEGIN
2022
2023 SELECT primary_cost_method
2024 INTO cost_method
2025 FROM mtl_parameters
2026 WHERE Organization_id=Org_id;
2027
2028 OPEN beg_inv_balance;
2029
2030 LOOP
2031
2032 -- by default, item cost = 0 at the start for every trx.
2033 item_cost := 0;
2034
2035 FETCH beg_inv_balance
2036 INTO l_item_id,
2037 l_cost_group_id,
2038 l_revision,
2039 l_lot_number,
2040 l_subinventory,
2041 l_locator;
2042
2043
2044 if(beg_inv_balance%NOTFOUND) then
2045 CLOSE beg_inv_balance;
2046 EXIT;
2047 end if;
2048
2049 if (l_lot_number <> '99') then -- Forward port Bug 4561628
2050 ctr1:=ctr1+1;
2051
2052 -- digupta 07/10/02 -- filtered out certain transactions that do not
2053 -- affect inventory quantity or balance.
2054 SELECT sum(TRANSACTION_QUANTITY)
2055 INTO current_onhand_qty
2056 FROM mtl_onhand_quantities
2057 WHERE INVENTORY_ITEM_ID = l_item_id
2058 AND ORGANIZATION_ID = Org_id
2059 AND SUBINVENTORY_CODE = l_subinventory
2060 AND nvl(REVISION,-999) = nvl(l_revision,-999)
2061 AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
2062 AND nvl(LOT_NUMBER,-999) = nvl(l_lot_number,-999)
2063 AND Nvl(cost_group_id,-999) = Nvl(l_cost_group_id,-999);
2064
2065 IF (l_lot_number IS NULL) THEN
2066
2067 -- digupta 07/10/02 -- filtered out certain transactions that do not
2068 -- affect inventory quantity or balance.
2069 -- mochawla 10/29/2003. filtered out logical transactions from net quantity
2070 SELECT sum(primary_quantity)
2071 INTO net_transacted_quantity
2072 FROM mtl_material_transactions
2073 WHERE INVENTORY_ITEM_ID = l_item_id
2074 AND ORGANIZATION_ID = Org_id
2075 AND Nvl(subinventory_code,-999) = Nvl(l_subinventory,-999)
2076 AND nvl(REVISION,-999) = nvl(l_revision,-999)
2077 AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
2078 AND Nvl(cost_group_id,-999) = Nvl(l_cost_group_id,-999)
2079 AND TRANSACTION_DATE >= Trunc(p_from_date+1)
2080 AND transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
2081 AND organization_id = NVL(owning_organization_id, organization_id)
2082 AND NVL(OWNING_TP_TYPE,2) = 2
2083 AND NVL(logical_transaction, 2) <> 1; /*11.5.10 changes*/
2084
2085 -- digupta 07/10/02 -- filtered out certain transactions that do not
2086 -- affect inventory quantity or balance.
2087 -- mochawla 10/29/2003. filtered out logical transactions from transacted quantity
2088 SELECT sum(primary_quantity)
2089 INTO from_date_transacted_quantity
2090 FROM mtl_material_transactions
2091 WHERE INVENTORY_ITEM_ID = l_item_id
2092 AND ORGANIZATION_ID = Org_id
2093 AND Nvl(subinventory_code,-999) = Nvl(l_subinventory,-999)
2094 AND nvl(REVISION,-999) = nvl(l_revision,-999)
2095 AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
2096 AND Nvl(cost_group_id,-999) = Nvl(l_cost_group_id,-999)
2097 AND TRANSACTION_DATE >= Trunc(p_from_date)
2098 AND transaction_date < Trunc(p_from_date+1)
2099 AND transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
2100 AND organization_id = NVL(owning_organization_id, organization_id)
2101 AND NVL(OWNING_TP_TYPE,2) = 2
2102 AND NVL(logical_transaction, 2) <> 1; /*11.5.10 changes*/
2103 ELSE
2104
2105 -- digupta 07/10/02 -- filtered out certain transactions that do not
2106 -- affect inventory quantity or balance.
2107 -- ltong 01/20/2003. Filtered out consigned inventory.
2108 -- mochawla 10/29/2003. filtered out logical transactions from net quantity
2109 SELECT sum(mtln.primary_quantity)
2110 INTO net_transacted_quantity
2111 FROM mtl_material_transactions mmt,
2112 MTL_TRANSACTION_LOT_NUMBERS mtln
2113 WHERE mmt.INVENTORY_ITEM_ID = l_item_id
2114 AND mmt.ORGANIZATION_ID = Org_id
2115 AND Nvl(mmt.subinventory_code,-999) = Nvl(l_subinventory,-999)
2116 AND nvl(REVISION,-999) = nvl(l_revision,-999)
2117 AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
2118 AND nvl(mtln.LOT_NUMBER,-999) = nvl(l_lot_number,-999)
2119 AND Nvl(mmt.cost_group_id,-999) = Nvl(l_cost_group_id,-999)
2120 AND mmt.TRANSACTION_DATE >= Trunc( p_from_date +1)
2121 AND mmt.transaction_id = mtln.transaction_id
2122 AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
2123 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
2124 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
2125 AND NVL(mmt.logical_transaction, 2) <> 1; /*11.5.10 changes*/
2126
2127 -- digupta 07/10/02 -- filtered out certain transactions that do not
2128 -- affect inventory quantity or balance.
2129 -- ltong 01/20/2003. Filtered out consigned inventory.
2130 -- mochawla 10/29/2003. filtered out logical transactions from quantity
2131 SELECT sum(mtln.primary_quantity)
2132 INTO from_date_transacted_quantity
2133 FROM mtl_material_transactions mmt,
2134 MTL_TRANSACTION_LOT_NUMBERS mtln
2135 WHERE mmt.INVENTORY_ITEM_ID = l_item_id
2136 AND mmt.ORGANIZATION_ID = Org_id
2137 AND Nvl(mmt.subinventory_code,-999) = Nvl(l_subinventory,-999)
2138 AND nvl(REVISION,-999) = nvl(l_revision,-999)
2139 AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
2140 AND nvl(mtln.LOT_NUMBER,-999) = nvl(l_lot_number,-999)
2141 AND Nvl(mmt.cost_group_id,-999) = Nvl(l_cost_group_id,-999)
2142 AND mmt.TRANSACTION_DATE >= Trunc(p_from_date)
2143 AND mmt.transaction_date < Trunc( p_from_date +1)
2144 AND mmt.transaction_id = mtln.transaction_id
2145 AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
2146 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
2147 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
2148 AND NVL(mmt.logical_transaction, 2) <> 1; /*11.5.10 changes*/
2149
2150 END IF;
2151
2152 beg_onh_qty := nvl(current_onhand_qty,0) - nvl(net_transacted_quantity,0)
2153 - Nvl(from_date_transacted_quantity,0);
2154
2155 end_onh_qty := beg_onh_qty + Nvl(from_date_transacted_quantity, 0);
2156 avg_onh_qty := (beg_onh_qty + end_onh_qty) /2;
2157
2158 /*
2159 edw_log.put_line('item_id ' || l_item_id || 'c_onhand_qty is ' || current_onhand_qty ||
2160 ' net_qty is ' || net_transacted_quantity ||
2161 ' from_qty is ' || from_date_transacted_quantity || 'beg_onh_qty ' || beg_onh_qty );
2162 */
2163
2164 -- if beg_onh_qty > 0 OR end_onh_qty > 0 THEN
2165 -- there might be negative value allowed for inventory
2166 -- actually we can create the entry. If there is no activity/balance in this period,
2167 -- then the cleanup will be taken care in calc_prd_start_end by rjin
2168
2169 ctr2:=ctr2+1;
2170
2171 /*-----------------------------------------------------------------------
2172 digupta 10/08/02
2173 We need to take qty into account for the expense subs but should not
2174 calculate the value for Expense sub. This is analogous to what we
2175 do in the calculate_balance procedure.
2176 -------------------------------------------------------------------*/
2177
2178 IF l_subinventory IS NOT NULL THEN
2179 SELECT asset_inventory
2180 INTO asset_sub
2181 FROM mtl_secondary_inventories sub
2182 WHERE sub.SECONDARY_INVENTORY_NAME=l_subinventory
2183 AND sub.organization_id = Org_id;
2184
2185 if (asset_sub<>1) then
2186 item_cost:=0;
2187 end if;
2188 END IF;
2189
2190 -- see if this is an expense item
2191 SELECT inventory_asset_flag
2192 INTO non_expense_item
2193 FROM mtl_system_items
2194 WHERE organization_id = org_id
2195 AND inventory_item_id = l_item_id;
2196
2197 -- If this is not an expense sub or and expense item,
2198 -- then get the item cost
2199 IF ((asset_sub = 1 OR l_subinventory is NULL)
2200 AND (non_expense_item = 'Y')) THEN -- {
2201 -- standard costing orgs need be treated differently
2202 IF (cost_method = 1) -- is a standard costing org
2203 THEN -- {
2204 -- get standard costing org cost specially since mcacd does
2205 -- not update the actual cost properly in a standard cost
2206 -- update scenario
2207 item_cost := std_costing_org_item_cost (org_id,
2208 l_item_id,
2209 p_from_date);
2210
2211 ELSE
2212 -- digupta 07/10/02 -- filtered out certain transactions that do not
2213 -- affect inventory quantity or balance.
2214 -- ltong 01/20/2003. Filtered out consigned inventory.
2215 SELECT MAX(macd.transaction_id)
2216 INTO l_trx_id
2217 FROM mtl_cst_actual_cost_details macd,
2218 mtl_material_transactions mmt
2219 WHERE mmt.transaction_id = macd.transaction_id
2220 AND mmt.ORGANIZATION_ID = Org_id
2221 AND mmt.INVENTORY_ITEM_ID = l_item_id
2222 AND Nvl(mmt.cost_group_id,-999) = Nvl(l_cost_group_id, -999)
2223 AND mmt.transaction_type_id NOT IN
2224 (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58,
2225 87, 88, 89, 24)
2226 AND MMT.organization_id = NVL(MMT.owning_organization_id,
2227 MMT.organization_id)
2228 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
2229 AND mmt.transaction_date =
2230 (SELECT transaction_date
2231 FROM
2232 (SELECT /*+ first_rows */ mt.transaction_date
2233 FROM mtl_cst_actual_cost_details mcacd,
2234 mtl_material_transactions mt
2235 WHERE mt.transaction_id = mcacd.transaction_id
2236 AND mt.TRANSACTION_DATE < Trunc( p_from_date+1)
2237 AND mt.ORGANIZATION_ID = Org_id
2238 AND mt.INVENTORY_ITEM_ID = l_item_id
2239 AND mt.transaction_type_id NOT IN
2240 (73, 80, 25, 26, 28, 90, 91, 92, 55, 56,
2241 57, 58, 87, 88, 89, 24)
2242 AND Nvl(mt.cost_group_id,-999) =
2243 Nvl(l_cost_group_id,-999)
2244 ORDER BY mt.transaction_date DESC)
2245 WHERE rownum = 1);
2246
2247 SELECT sum(macd.NEW_cost)
2248 INTO item_cost
2249 FROM mtl_cst_actual_cost_details macd
2250 WHERE macd.transaction_id=l_trx_id
2251 AND macd.organization_id = Org_id; /* Bug 3661478 - add filter on organization_id*/
2252
2253
2254 END IF; -- cost_method = 1
2255 END IF; -- } (asset_sub = 1 OR l_subinventory is NULL) AND
2256 -- (non_expense_item = 'Y')
2257
2258
2259 -- edw_log.put_line(' item_cost is ' || Nvl( item_cost,0) );
2260
2261 beg_onh_val := beg_onh_qty * nvl(item_cost,0);
2262 end_onh_val := end_onh_qty * nvl(item_cost,0);
2263 avg_onh_val := (beg_onh_val + end_onh_val) /2;
2264
2265 SELECT INVENTORY_ITEM_STATUS_CODE,
2266 ITEM_TYPE,
2267 PRIMARY_UOM_CODE
2268 INTO l_item_status,
2269 l_item_type,
2270 l_base_uom
2271 FROM mtl_system_items
2272 WHERE inventory_item_id=l_item_id
2273 AND organization_id = Org_id;
2274
2275 OPIMPXIN.Insert_update_push_log(
2276 p_trx_date => p_from_date ,
2277 p_organization_id => Org_id,
2278 p_item_id => l_item_id,
2279 p_cost_group_id => l_cost_group_id,
2280 p_revision => l_revision,
2281 p_lot_number => l_lot_number,
2282 p_subinventory => l_subinventory,
2283 p_locator => l_locator,
2284 p_item_status => l_item_status,
2285 p_item_type => l_item_type,
2286 p_base_uom => l_base_uom,
2287 p_col_name1 => 'beg_onh_qty',
2288 p_total1 => beg_onh_qty,
2289 p_col_name2 => 'beg_onh_val_b',
2290 p_total2 => beg_onh_val,
2291 p_col_name3 => 'end_onh_qty',
2292 p_total3 => end_onh_qty, -- Setting end_onh_qty same as beg_onh_qty. It will
2293 p_col_name4 => 'end_onh_val_b', -- get changed if there are activities on that day.
2294 p_total4 => end_onh_val,
2295 p_col_name5 => 'avg_onh_val_b',
2296 p_total5 => avg_onh_val,
2297 p_col_name6 => 'avg_onh_qty',
2298 p_total6 => avg_onh_qty,
2299 selector => 2,
2300 success => l_status);
2301
2302 /*
2303 EDW_LOG.PUT_LINE('Inserted '||to_char(p_from_date)||','||to_char(Org_id)||','||to_char(l_item_id)||','||to_char(l_cost_group_id)||','||l_revision||',');
2304
2305 EDW_LOG.PUT_LINE(l_lot_number||','||l_subinventory||','||to_char(l_locator)||','||to_char(beg_onh_qty)||','||to_char(beg_onh_val));
2306 */
2307
2308 if( l_status > 0 ) then
2309 edw_log.put_line('Error in Insert_update_push_log');
2310 status := 1;
2311 else
2312 status := 0;
2313 end if;
2314
2315 /* --rjin
2316 else
2317 ctr3 := ctr3+1;
2318 beg_onh_val := 0;
2319 status := 0;
2320 end if;
2321 */
2322 end if; -- Forward port Bug 4561628.
2323 end loop;
2324
2325 edw_log.put_line('Begin Balance Processed ');
2326 edw_log.put_line('ctr1 '||to_char(ctr1));
2327 edw_log.put_line('ctr2 '||to_char(ctr1));
2328 edw_log.put_line('ctr3 '||to_char(ctr1));
2329 EXCEPTION
2330 WHEN OTHERS THEN
2331 edw_log.put_line('Error in calc_begin_inv : ');
2332 edw_log.put_line('Error code: '|| to_char(SQLCODE));
2333 edw_log.put_line('Error message: '||substr(SQLERRM, 1,200));
2334 status := 1;
2335 return;
2336
2337 End calc_begin_inv;
2338
2339 PROCEDURE calc_intrst_balance(p_from_date IN Date,
2340 p_to_date IN Date,
2341 Org_id IN Number,
2342 status OUT NOCOPY Number) IS --bug 3589921 added no copy
2343
2344 l_trx_date DATE;
2345 l_max_trx_date DATE;
2346 l_organization_id NUMBER;
2347 xfr_org_id NUMBER;
2348 l_item_id NUMBER;
2349 l_cost_group_id NUMBER;
2350 l_trx_action_id NUMBER;
2351 l_fob_pt NUMBER;
2352 l_row_exists NUMBER;
2353 l_item_status VARCHAR2(10);
2354 l_item_type VARCHAR2(30);
2355 l_base_uom VARCHAR2(3);
2356 l_pk VARCHAR2(100);
2357 l_last_end_qty NUMBER;
2358 l_last_end_val NUMBER;
2359 l_beg_int_qty NUMBER;
2360 l_beg_int_val_b NUMBER;
2361 l_end_int_qty NUMBER;
2362 l_end_int_val_b NUMBER;
2363 l_avg_int_qty NUMBER;
2364 l_avg_int_val_b NUMBER;
2365 l_cost NUMBER;
2366 total_value NUMBER;
2367 total_qty NUMBER;
2368 trx_type NUMBER;
2369 l_status NUMBER;
2370 l_statement NUMBER;
2371 l_process_org VARCHAR2(2);
2372 l_org_id NUMBER;
2373
2374 CURSOR intrst_balance IS
2375 SELECT trunc(mmt.TRANSACTION_DATE),
2376 mmt.organization_id,
2377 mmt.INVENTORY_ITEM_ID,
2378 mmt.COST_GROUP_ID,
2379 mmt.PRIMARY_QUANTITY,
2380 mmt.transaction_action_id,
2381 mmt.transfer_organization_id,
2382 mmt.actual_cost,
2383 msi.inventory_item_status_code,
2384 msi.item_type,
2385 msi.primary_uom_code
2386 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2387 mtl_system_items msi
2388 WHERE ( mmt.ORGANIZATION_ID=Org_id or mmt.transfer_organization_id =Org_id)
2389 AND mmt.organization_id=msi.organization_id
2390 AND mmt.inventory_item_id=msi.inventory_item_id
2391 AND mmt.transaction_action_id in (12,21)
2392 AND mmt.transaction_date >= p_from_date
2393 AND mmt.transaction_date <= p_to_date
2394 ORDER BY trunc(mmt.TRANSACTION_DATE),mmt.inventory_item_id;
2395
2396 CURSOR row_exists IS
2397 SELECT 1
2398 FROM opi_ids_push_log
2399 WHERE IDS_KEY=l_pk;
2400
2401 CURSOR last_trx is
2402 SELECT trunc(MAX(trx_date))
2403 FROM OPI_IDS_PUSH_LOG
2404 WHERE ORGANIZATION_ID = l_organization_id
2405 AND INVENTORY_ITEM_ID = l_item_id
2406 AND nvl(COST_GROUP_ID,-999) = nvl(l_cost_group_id,-999)
2407 AND REVISION is null
2408 AND LOT_NUMBER is null
2409 AND SUBINVENTORY_CODE is null
2410 AND LOCATOR_ID is null
2411 AND trx_date < l_trx_date;
2412
2413 BEGIN
2414 --DBMS_OUTPUT.PUT_LINE(' p_from_date= '||to_char(p_from_date)||' p_to_Date= '||to_char(p_to_date)||' org= '||Org_id);
2415
2416
2417 OPEN intrst_balance;
2418
2419 l_statement:=1;
2420
2421 LOOP
2422
2423
2424 FETCH intrst_balance
2425 INTO l_trx_date,
2426 l_org_id,
2427 l_item_id,
2428 l_cost_group_id,
2429 total_qty,
2430 l_trx_action_id,
2431 xfr_org_id,
2432 l_cost,
2433 l_item_status,
2434 l_item_type,
2435 l_base_uom;
2436
2437
2438 if(intrst_balance%NOTFOUND) then
2439 CLOSE intrst_balance;
2440 EXIT;
2441 end if;
2442
2443 l_statement:=2;
2444
2445 if(l_trx_action_id = 21 or l_trx_action_id = 22) then
2446
2447 select fob_point
2448 into l_fob_pt
2449 from mtl_interorg_parameters
2450 where from_organization_id = l_org_id
2451 and to_organization_id = xfr_org_id;
2452
2453
2454 -- if the FOB point is NULL, then shipping network settings
2455 -- must have been changed. Ignore this transaction,
2456 -- and report this in the log. Program will still terminate
2457 -- normally.
2458 IF (l_fob_pt IS NULL) THEN
2459
2460 EDW_LOG.PUT_LINE ('Error: Intransit from org ' || Org_id ||
2461 ' to org ' || xfr_org_id ||
2462 ' has NULL FOB point.' ||
2463 ' Intransit transactions between these orgs on '|| l_trx_date || ' cannot be collected. Ignoring transaction.' );
2464
2465 goto next_intrst_trx; -- skip this trx.
2466 END IF;
2467
2468
2469 if l_fob_pt = 1 then -- 1 = FOB Ship 2 = FOB Rcpt
2470 l_organization_id := xfr_org_id;
2471 total_qty := (-1)*total_qty;
2472
2473
2474 elsif l_fob_pt = 2 then
2475 l_organization_id := Org_id;
2476 total_qty := (-1)*total_qty;
2477 end if;
2478
2479 else
2480
2481 -- -------------------------------------------------------------
2482 -- --- For Intrasit receipt transactions, xfr_org_id stores
2483 -- --- the from org and Org_id stores the To org.
2484 -- -------------------------------------------------------------
2485
2486 select fob_point
2487 into l_fob_pt
2488 from mtl_interorg_parameters
2489 where from_organization_id = xfr_org_id
2490 and to_organization_id = l_org_id;
2491
2492 -- if the FOB point is NULL, then shipping network settings
2493 -- must have been changed. Ignore this transaction,
2494 -- and report this in the log. Program will still terminate
2495 -- sucessfully.
2496 IF (l_fob_pt IS NULL) THEN
2497
2498 EDW_LOG.PUT_LINE ('Error: Intransit from org ' ||
2499 xfr_org_id || ' to org ' || Org_id ||
2500 ' has NULL FOB point.' ||
2501 ' Intransit transactions between these orgs on '|| l_trx_date || ' cannot be collected. Ignoring transaction.' );
2502 goto next_intrst_trx; -- skip this trx.
2503 END IF;
2504
2505 if l_fob_pt = 1 then -- 1 = FOB Ship 2 = FOB Rcpt
2506 l_organization_id := Org_id;
2507 total_qty := (-1)*total_qty;
2508
2509 elsif l_fob_pt = 2 then
2510 l_organization_id := xfr_org_id;
2511 total_qty := (-1)*total_qty;
2512 end if;
2513
2514 end if;
2515
2516 l_pk := l_trx_date||'-'||l_item_id||'-'||l_organization_id||'-'||l_cost_group_id||'-'||'-'||'-'||'-';
2517
2518 /* edw_log.put_line('IU_push_log: IDSKEY= '||l_pk); */
2519
2520 l_statement:=3;
2521 OPEN row_exists ;
2522
2523 FETCH row_exists
2524 INTO l_row_exists;
2525
2526 if(row_exists%rowcount > 0) then
2527 l_statement:=4;
2528 UPDATE opi_ids_push_log
2529 SET end_int_qty = (end_int_qty + total_qty)
2530 WHERE IDS_KEY = l_pk;
2531
2532 else
2533
2534 l_statement:=5;
2535 OPEN last_trx;
2536
2537 FETCH last_trx
2538 INTO l_max_trx_date;
2539
2540
2541 /*---------------------------------------------------------------
2542 The above cursor will find if the record is already existing
2543 in opi_ids_push_log for a particular item,org,costgroup.
2544 If not then the start int qty and start value are 0,
2545 otherwise get the start value and qty for the last
2546 transacted record for that combination (last record)
2547 from opi_ids_push_log
2548 -----------------------------------------------------------------*/
2549
2550 IF(l_max_trx_date is null) THEN
2551 l_last_end_qty := 0;
2552 l_last_end_val := 0;
2553 ELSE
2554 SELECT Nvl(end_int_val_b,0), Nvl(end_int_qty,0)
2555 INTO l_last_end_val, l_last_end_qty
2556 FROM OPI_IDS_PUSH_LOG
2557 WHERE IDS_KEY = l_max_trx_date||'-'||l_item_id||'-'||l_organization_id||'-'||l_cost_group_id||'-'||'-'||'-'||'-';
2558
2559 END IF;
2560
2561 CLOSE last_trx;
2562
2563 total_value := nvl(total_qty,0)*nvl(l_cost,0);
2564 l_beg_int_qty := nvl(l_last_end_qty,0);
2565 l_beg_int_val_b := nvl(l_last_end_val,0);
2566 l_end_int_qty := l_beg_int_qty + nvl(total_qty,0);
2567 l_end_int_val_b := l_beg_int_val_b + nvl(total_value,0);
2568 l_avg_int_qty := (l_beg_int_qty + l_end_int_qty)/2;
2569 l_avg_int_val_b := (l_beg_int_val_b + l_end_int_val_b)/2;
2570
2571 select process_enabled_flag into l_process_org
2572 from mtl_parameters where organization_id = l_organization_id;
2573
2574 if l_process_org <> 'Y' then
2575 INSERT INTO opi_ids_push_log
2576 (ids_key,
2577 cost_group_id,
2578 organization_id,
2579 inventory_item_id,
2580 trx_date,
2581 push_flag,
2582 beg_int_qty, beg_int_val_b,
2583 end_int_qty, end_int_val_b,
2584 avg_int_qty, avg_int_val_b,
2585 base_uom,
2586 item_status,
2587 item_type )
2588 VALUES
2589 (l_pk,
2590 l_cost_group_id,
2591 l_organization_id,
2592 l_item_id,
2593 l_trx_date,
2594 1,
2595 l_beg_int_qty,
2596 l_beg_int_val_b,
2597 l_end_int_qty,
2598 l_end_int_val_b,
2599 l_avg_int_qty,
2600 l_avg_int_val_b,
2601 l_base_uom,
2602 l_item_status,
2603 l_item_type );
2604 end if;
2605
2606 end if; -- row_exists > 1
2607
2608 CLOSE row_exists;
2609
2610 <<next_intrst_trx>> -- label for next intransit transaction
2611 null;
2612
2613 END LOOP;
2614
2615
2616 l_statement:=6;
2617 status := 0;
2618
2619 EXCEPTION
2620 WHEN NO_DATA_FOUND THEN
2621 edw_log.put_line('No intercompany Shipment network defined between Org'||to_char(Org_id)||' and '||to_char(xfr_org_id));
2622 edw_log.put_line('Error code: '|| to_char(SQLCODE));
2623 edw_log.put_line('Error message: '||substr(SQLERRM, 1,200));
2624 status := 1;
2625 return;
2626
2627 WHEN OTHERS THEN
2628 edw_log.put_line('Error in calc_intrst_balance : ');
2629 edw_log.put_line('Error code: '|| to_char(SQLCODE));
2630 edw_log.put_line('Error message: '||substr(SQLERRM, 1,200));
2631 status := 1;
2632 return;
2633
2634 END CALC_INTRST_BALANCE;
2635
2636 END OPIMPXWI;
[Home] [Help]