[Home] [Help]
PACKAGE BODY: APPS.OPIMPXIN
Source
1 Package Body OPIMPXIN AS
2 /*$Header: OPIMXINB.pls 120.1 2005/06/08 18:27:52 appldev $ */
3
4
5 /*{----------------------------------------------
6 PROCEDURE CALC_WIP_COMPLETION
7 ----------------------------------------------*/
8
9
10 Procedure calc_wip_completion(Errbuf out nocopy Varchar2,
11 Retcode out nocopy Varchar2,
12 p_from_date IN Date,
13 p_to_date IN Date,
14 Org_id IN Number) IS
15
16 CURSOR wip_completion_no_lot_qty IS
17 SELECT trunc(mmt.TRANSACTION_DATE),
18 mmt.ORGANIZATION_ID,
19 mmt.INVENTORY_ITEM_ID,
20 mmt.COST_GROUP_ID,
21 mmt.REVISION,
22 mmt.SUBINVENTORY_CODE,
23 mmt.LOCATOR_ID,
24 sum(mmt.PRIMARY_QUANTITY)
25 FROM MTL_MATERIAL_TRANSACTIONS mmt,
26 MTL_SYSTEM_ITEMS msi
27 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
28 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
29 AND mmt.ORGANIZATION_ID=Org_id
30 AND msi.LOT_CONTROL_CODE = 1
31 AND mmt.transaction_action_id=31
32 AND mmt.transaction_source_type_id=5
33 AND mmt.transaction_date >= p_from_date
34 AND mmt.transaction_date <= p_to_date
35 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
36 mmt.COST_GROUP_ID,mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
37
38 CURSOR wip_completion_with_lot_qty IS
39 SELECT trunc(mmt.TRANSACTION_DATE),
40 mmt.ORGANIZATION_ID,
41 mmt.INVENTORY_ITEM_ID,
42 mmt.COST_GROUP_ID,
43 mmt.REVISION,
44 mtln.LOT_NUMBER,
45 mmt.SUBINVENTORY_CODE,
46 mmt.LOCATOR_ID,
47 sum(mtln.PRIMARY_QUANTITY)
48 FROM MTL_MATERIAL_TRANSACTIONS mmt,
49 MTL_SYSTEM_ITEMS msi,
50 MTL_TRANSACTION_LOT_NUMBERS mtln
51 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
52 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
53 AND mmt.ORGANIZATION_ID=Org_id
54 AND mmt.transaction_id = mtln.transaction_id
55 AND msi.LOT_CONTROL_CODE = 2
56 AND mmt.transaction_action_id=31
57 AND mmt.transaction_source_type_id=5
58 AND mmt.transaction_date >= p_from_date
59 AND mmt.transaction_date <= p_to_date
60 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
61 mmt.COST_GROUP_ID, mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
62
63 CURSOR wip_completion_no_lot_val IS
64 SELECT trunc(mmt.TRANSACTION_DATE),
65 mmt.ORGANIZATION_ID,
66 mmt.INVENTORY_ITEM_ID,
67 mmt.COST_GROUP_ID,
68 mmt.REVISION,
69 mmt.SUBINVENTORY_CODE,
70 mmt.LOCATOR_ID,
71 sum(mta.BASE_TRANSACTION_VALUE)
72 FROM MTL_MATERIAL_TRANSACTIONS mmt,
73 MTL_SYSTEM_ITEMS msi,
74 MTL_TRANSACTION_ACCOUNTS mta
75 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
76 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
77 AND mmt.ORGANIZATION_ID=Org_id
78 AND msi.LOT_CONTROL_CODE = 1
79 AND mmt.transaction_id = mta.transaction_id
80 AND mta.accounting_line_type = 1
81 AND mmt.transaction_action_id=31
82 AND mmt.transaction_source_type_id=5
83 AND mmt.transaction_date >= p_from_date
84 AND mmt.transaction_date <= p_to_date
85 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
86 mmt.COST_GROUP_ID,mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
87
88
89 CURSOR wip_completion_with_lot_val IS
90 SELECT trunc(mmt.TRANSACTION_DATE),
91 mmt.ORGANIZATION_ID,
92 mmt.INVENTORY_ITEM_ID,
93 mmt.COST_GROUP_ID,
94 mmt.REVISION,
95 mtln.LOT_NUMBER,
96 mmt.SUBINVENTORY_CODE,
97 mmt.LOCATOR_ID,
98 sum(mta.BASE_TRANSACTION_VALUE)
99 FROM MTL_MATERIAL_TRANSACTIONS mmt,
100 MTL_SYSTEM_ITEMS msi,
101 MTL_TRANSACTION_LOT_NUMBERS mtln,
102 MTL_TRANSACTION_ACCOUNTS mta
103 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
104 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
105 AND mmt.ORGANIZATION_ID=Org_id
106 AND mmt.transaction_id = mtln.transaction_id
107 AND msi.LOT_CONTROL_CODE = 2
108 AND mmt.transaction_id = mta.transaction_id
109 AND mta.accounting_line_type = 1
110 AND mmt.transaction_action_id=31
111 AND mmt.transaction_source_type_id=5
112 AND mmt.transaction_date >= p_from_date
113 AND mmt.transaction_date <= p_to_date
114 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
115 mmt.COST_GROUP_ID,mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
116
117 l_trx_date DATE;
118 l_organization_id NUMBER;
119 l_item_id NUMBER;
120 l_cost_group_id NUMBER;
121 l_revision VARCHAR2(3);
122 l_lot_number VARCHAR2(30);
123 l_subinventory VARCHAR2(10);
124 l_locator NUMBER;
125 total_value NUMBER;
126 total_qty NUMBER;
127 trx_type NUMBER;
128 status NUMBER;
129
130 BEGIN
131
132 OPEN wip_completion_no_lot_qty;
133
134
135 edw_log.put_line('CALCWIPCOMP p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
136 edw_log.put_line('CALCWIPCOMP p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
137
138
139 LOOP
140
141 initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
142 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
143
144 FETCH wip_completion_no_lot_qty
145 INTO l_trx_date,
146 l_organization_id,
147 l_item_id,
148 l_cost_group_id,
149 l_revision,
150 l_subinventory,
151 l_locator,
152 total_qty;
153
154
155 if(wip_completion_no_lot_qty%NOTFOUND) then
156
157 edw_log.put_line('NOT FOUND');
158 CLOSE wip_completion_no_lot_qty;
159 exit;
160 end if;
161
162 Insert_update_push_log(
163 p_trx_date => l_trx_date ,
164 p_organization_id => l_organization_id,
165 p_item_id => l_item_id,
166 p_cost_group_id => l_cost_group_id,
167 p_revision => l_revision,
168 p_subinventory => l_subinventory,
169 p_locator => l_locator,
170 p_col_name1 => 'wip_comp_qty',
171 p_total1 => total_qty,
172 selector => 1,
173 success => status);
174
175 /* edw_log.put_line('Insert_update_push_log'); */
176 if (status > 0) then
177 edw_log.put_line('ERROR');
178
179 Retcode := '2';
180 return;
181 end if;
182
183 END LOOP;
184
185
186 OPEN wip_completion_with_lot_qty;
187
188 LOOP
189
190 initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
191 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
192 /* edw_log.put_line('2'); */
193 FETCH wip_completion_with_lot_qty
194 INTO l_trx_date,
195 l_organization_id,
196 l_item_id,
197 l_cost_group_id,
198 l_revision,
199 l_lot_number,
200 l_subinventory,
201 l_locator,
202 total_qty;
203
204
205
206 if(wip_completion_with_lot_qty%NOTFOUND) then
207 edw_log.put_line('NOT FOUND');
208 CLOSE wip_completion_with_lot_qty;
209 exit;
210 end if;
211
212 Insert_update_push_log(
213 p_trx_date => l_trx_date ,
214 p_organization_id => l_organization_id,
215 p_item_id => l_item_id,
216 p_cost_group_id => l_cost_group_id,
217 p_revision => l_revision,
218 p_lot_number => l_lot_number,
219 p_subinventory => l_subinventory,
220 p_locator => l_locator,
221 p_col_name1 => 'wip_comp_qty',
222 p_total1 => total_qty,
223 selector => 1,
224 success => status);
225
226 if (status > 0) then
227 edw_log.put_line('error');
228 Retcode := '2';
229 return;
230 end if;
231
232 END LOOP;
233
234
235 OPEN wip_completion_no_lot_val;
236
237 LOOP
238
239 initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
240 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
241 /* edw_log.put_line('3'); */
242 FETCH wip_completion_no_lot_val
243 INTO l_trx_date,
244 l_organization_id,
245 l_item_id,
246 l_cost_group_id,
247 l_revision,
248 l_subinventory,
249 l_locator,
250 total_value;
251
252 /* edw_log.put_line('3 after fetch'); */
253 if(wip_completion_no_lot_val%NOTFOUND) then
254 edw_log.put_line('NOT FOUND');
255 CLOSE wip_completion_no_lot_val;
256 exit;
257 end if;
258
259 Insert_update_push_log(
260 p_trx_date => l_trx_date ,
261 p_organization_id => l_organization_id,
262 p_item_id => l_item_id,
263 p_cost_group_id => l_cost_group_id,
264 p_revision => l_revision,
265 p_subinventory => l_subinventory,
266 p_locator => l_locator,
267 p_col_name1 => 'wip_comp_val_b',
268 p_total1 => total_value,
269 selector => 1,
270 success => status);
271
272 if (status > 0) then
273 edw_log.put_line('error');
274 Retcode := '2';
275 return;
276 end if;
277
278 END LOOP;
279
280
281 OPEN wip_completion_with_lot_val;
282
283 LOOP
284
285 initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
286 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
287 /* edw_log.put_line('4'); */
288 FETCH wip_completion_with_lot_val
289 INTO l_trx_date,
290 l_organization_id,
291 l_item_id,
292 l_cost_group_id,
293 l_revision,
294 l_lot_number,
295 l_subinventory,
296 l_locator,
297 total_value;
298
299 if(wip_completion_with_lot_val%NOTFOUND) then
300 CLOSE wip_completion_with_lot_val;
301 exit;
302 end if;
303
304 Insert_update_push_log(
305 p_trx_date => l_trx_date ,
306 p_organization_id => l_organization_id,
307 p_item_id => l_item_id,
308 p_cost_group_id => l_cost_group_id,
309 p_revision => l_revision,
310 p_lot_number => l_lot_number,
311 p_subinventory => l_subinventory,
312 p_locator => l_locator,
313 p_col_name1 => 'wip_comp_val_b',
314 p_total1 => total_value,
315 selector => 1,
316 success => status);
317
318 if (status > 0) then
319 edw_log.put_line('error');
320 Retcode := '2';
321 return;
322 end if;
323
324 END LOOP;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 edw_log.put_line('EXCEPTIOn OTHERS');
329 edw_log.put_line('Error in calc_wip_completion');
330 Retcode := '2';
331
332 end calc_wip_completion;
333
334
335 /*}{---------------------------------------------
336 PROCEDURE CALC_WIP_ISSUE
337 ----------------------------------------------*/
338
339
340 Procedure calc_wip_issue(Errbuf out nocopy Varchar2,
341 Retcode out nocopy Varchar2,
342 p_from_date IN Date,
343 p_to_date IN Date,
344 Org_id IN Number) IS
345
346 l_trx_date DATE;
347 l_organization_id NUMBER;
348 l_item_id NUMBER;
349 l_cost_group_id NUMBER;
350 l_revision VARCHAR2(3);
351 l_lot_number VARCHAR2(30);
352 l_subinventory VARCHAR2(10);
353 l_locator NUMBER;
354 total_value NUMBER;
355 total_qty NUMBER;
356 trx_type NUMBER;
357 status NUMBER;
358
359
360 CURSOR wip_issue_no_lot_qty IS
361 SELECT trunc(mmt.TRANSACTION_DATE),
362 mmt.ORGANIZATION_ID,
363 mmt.INVENTORY_ITEM_ID,
364 mmt.COST_GROUP_ID,
365 mmt.REVISION,
366 mmt.SUBINVENTORY_CODE,
367 mmt.LOCATOR_ID,
368 sum(mmt.PRIMARY_QUANTITY)
369 FROM MTL_MATERIAL_TRANSACTIONS mmt,
370 MTL_SYSTEM_ITEMS msi
371 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
372 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
373 AND mmt.ORGANIZATION_ID=Org_id
374 AND msi.LOT_CONTROL_CODE = 1
375 AND mmt.transaction_action_id in (1,27,33,34)
376 AND mmt.transaction_source_type_id=5
377 AND mmt.transaction_date >= p_from_date
378 AND mmt.transaction_date <= p_to_date
379 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
380 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
381
382 CURSOR wip_issue_with_lot_qty IS
383 SELECT trunc(mmt.TRANSACTION_DATE),
384 mmt.ORGANIZATION_ID,
385 mmt.INVENTORY_ITEM_ID,
386 mmt.COST_GROUP_ID,
387 mmt.REVISION,
388 mtln.LOT_NUMBER,
389 mmt.SUBINVENTORY_CODE,
390 mmt.LOCATOR_ID,
391 sum(mtln.PRIMARY_QUANTITY)
392 FROM MTL_MATERIAL_TRANSACTIONS mmt,
393 MTL_SYSTEM_ITEMS msi,
394 MTL_TRANSACTION_LOT_NUMBERS mtln
395 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
396 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
397 AND mmt.transaction_id = mtln.transaction_id
398 AND mmt.ORGANIZATION_ID=Org_id
399 AND msi.LOT_CONTROL_CODE = 2
400 AND mmt.transaction_action_id in (1,27,33,34)
401 AND mmt.transaction_source_type_id=5
402 AND mmt.transaction_date >= p_from_date
403 AND mmt.transaction_date <= p_to_date
404 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
405 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
406
407 CURSOR wip_issue_no_lot_val IS
408 SELECT trunc(mmt.TRANSACTION_DATE),
409 mmt.ORGANIZATION_ID,
410 mmt.INVENTORY_ITEM_ID,
411 mmt.COST_GROUP_ID,
412 mmt.REVISION,
413 mmt.SUBINVENTORY_CODE,
414 mmt.LOCATOR_ID,
415 sum(mta.BASE_TRANSACTION_VALUE)
416 FROM MTL_MATERIAL_TRANSACTIONS mmt,
417 MTL_SYSTEM_ITEMS msi,
418 MTL_TRANSACTION_ACCOUNTS mta
419 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
420 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
421 AND mmt.ORGANIZATION_ID=Org_id
422 AND msi.LOT_CONTROL_CODE = 1
423 AND mmt.transaction_id = mta.transaction_id
424 AND mta.accounting_line_type = 1
425 AND mmt.transaction_action_id in (1,27,33,34)
426 AND mmt.transaction_source_type_id=5
427 AND mmt.transaction_date >= p_from_date
428 AND mmt.transaction_date <= p_to_date
429 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
430 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
431
432 CURSOR wip_issue_with_lot_val IS
433 SELECT trunc(mmt.TRANSACTION_DATE),
434 mmt.ORGANIZATION_ID,
435 mmt.INVENTORY_ITEM_ID,
436 mmt.COST_GROUP_ID,
437 mmt.REVISION,
438 mtln.LOT_NUMBER,
439 mmt.SUBINVENTORY_CODE,
440 mmt.LOCATOR_ID,
441 sum(mta.BASE_TRANSACTION_VALUE)
442 FROM MTL_MATERIAL_TRANSACTIONS mmt,
443 MTL_SYSTEM_ITEMS msi,
444 MTL_TRANSACTION_LOT_NUMBERS mtln,
445 MTL_TRANSACTION_ACCOUNTS mta
446 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
447 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
448 AND mmt.ORGANIZATION_ID=Org_id
449 AND mmt.transaction_id = mtln.transaction_id
450 AND msi.LOT_CONTROL_CODE = 2
451 AND mmt.transaction_id = mta.transaction_id
452 AND mta.accounting_line_type = 1
453 AND mmt.transaction_action_id in (1,27,33,34)
454 AND mmt.transaction_source_type_id=5
455 AND mmt.transaction_date >= p_from_date
456 AND mmt.transaction_date <= p_to_date
457 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
458 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
459 BEGIN
460
461
462 edw_log.put_line('CALCWIPISS p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
463 edw_log.put_line('CALCWIPISS p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
464
465
466 OPEN wip_issue_no_lot_qty;
467
468 LOOP
469
470 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
471 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
472
473 FETCH wip_issue_no_lot_qty
474 INTO l_trx_date,
475 l_organization_id,
476 l_item_id,
477 l_cost_group_id,
478 l_revision,
479 l_subinventory,
480 l_locator,
481 total_qty;
482
483
484 if(wip_issue_no_lot_qty%NOTFOUND) then
485 CLOSE wip_issue_no_lot_qty;
486 exit;
487 end if;
488
489 Insert_update_push_log(
490 p_trx_date => l_trx_date ,
491 p_organization_id => l_organization_id,
492 p_item_id => l_item_id,
493 p_cost_group_id => l_cost_group_id,
494 p_revision => l_revision,
495 p_subinventory => l_subinventory,
496 p_locator => l_locator,
497 p_col_name1 => 'wip_issue_qty',
498 p_total1 => total_qty,
499 selector => 1,
500 success => status);
501
502 if (status > 0) then
503 Retcode := '2';
504 return;
505 end if;
506
507 END LOOP;
508
509
510 OPEN wip_issue_with_lot_qty;
511
512 LOOP
513
514 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
515 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
516
517 FETCH wip_issue_with_lot_qty
518 INTO l_trx_date,
519 l_organization_id,
520 l_item_id,
521 l_cost_group_id,
522 l_revision,
523 l_lot_number,
524 l_subinventory,
525 l_locator,
526 total_qty;
527
528
529
530 if(wip_issue_with_lot_qty%NOTFOUND) then
531 CLOSE wip_issue_with_lot_qty;
532 exit;
533 end if;
534
535 Insert_update_push_log(
536 p_trx_date => l_trx_date ,
537 p_organization_id => l_organization_id,
538 p_item_id => l_item_id,
539 p_cost_group_id => l_cost_group_id,
540 p_revision => l_revision,
541 p_lot_number => l_lot_number,
542 p_subinventory => l_subinventory,
543 p_locator => l_locator,
544 p_col_name1 => 'wip_issue_qty',
545 p_total1 => total_qty,
546 selector => 1,
547 success => status);
548
549 if (status > 0) then
550 Retcode := '2';
551 return;
552 end if;
553
554 END LOOP;
555
556
557 OPEN wip_issue_no_lot_val;
558
559 LOOP
560
561 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
562 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
563
564 FETCH wip_issue_no_lot_val
565 INTO l_trx_date,
566 l_organization_id,
567 l_item_id,
568 l_cost_group_id,
569 l_revision,
570 l_subinventory,
571 l_locator,
572 total_value;
573
574 if(wip_issue_no_lot_val%NOTFOUND) then
575 CLOSE wip_issue_no_lot_val;
576 exit;
577 end if;
578
579 Insert_update_push_log(
580 p_trx_date => l_trx_date ,
581 p_organization_id => l_organization_id,
582 p_item_id => l_item_id,
583 p_cost_group_id => l_cost_group_id,
584 p_revision => l_revision,
585 p_subinventory => l_subinventory,
586 p_locator => l_locator,
587 p_col_name1 => 'wip_issue_val_b',
588 p_total1 => total_value,
589 selector => 1,
590 success => status);
591
592 if (status > 0) then
593 Retcode := '2';
594 return;
595 end if;
596
597 END LOOP;
598
599
600 OPEN wip_issue_with_lot_val;
601
602 LOOP
603
604 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
605 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
606
607 FETCH wip_issue_with_lot_val
608 INTO l_trx_date,
609 l_organization_id,
610 l_item_id,
611 l_cost_group_id,
612 l_revision,
613 l_lot_number,
614 l_subinventory,
615 l_locator,
616 total_value;
617
618 if(wip_issue_with_lot_val%NOTFOUND) then
619 CLOSE wip_issue_with_lot_val;
620 exit;
621 end if;
622
623 Insert_update_push_log(
624 p_trx_date => l_trx_date ,
625 p_organization_id => l_organization_id,
626 p_item_id => l_item_id,
627 p_cost_group_id => l_cost_group_id,
628 p_revision => l_revision,
629 p_lot_number => l_lot_number,
630 p_subinventory => l_subinventory,
631 p_locator => l_locator,
632 p_col_name1 => 'wip_issue_val_b',
633 p_total1 => total_value,
634 selector => 1,
635 success => status);
636
637 if (status > 0) then
638 Retcode := '2';
639 return;
640 end if;
641
642 END LOOP;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 edw_log.put_line('Error in calc_wip_issue');
647 Retcode := '2';
648
649 end calc_wip_issue;
650
651
652
653 /*}{----------------------------------------------
654 PROCEDURE CALC_ASSEMBLY_RETURN
655 ----------------------------------------------*/
656
657 Procedure calc_assembly_return(Errbuf out nocopy Varchar2,
658 Retcode out nocopy Varchar2,
659 p_from_date IN Date,
660 p_to_date IN Date,
661 Org_id IN Number) IS
662
663 l_trx_date DATE;
664 l_organization_id NUMBER;
665 l_item_id NUMBER;
666 l_cost_group_id NUMBER;
667 l_revision VARCHAR2(3);
668 l_lot_number VARCHAR2(30);
669 l_subinventory VARCHAR2(10);
670 l_locator NUMBER;
671 total_value NUMBER;
672 total_qty NUMBER;
673 trx_type NUMBER;
674 status NUMBER;
675
676 CURSOR assembly_return_no_lot_qty IS
677 SELECT trunc(mmt.TRANSACTION_DATE),
678 mmt.ORGANIZATION_ID,
679 mmt.INVENTORY_ITEM_ID,
680 mmt.COST_GROUP_ID,
681 mmt.REVISION,
682 mmt.SUBINVENTORY_CODE,
683 mmt.LOCATOR_ID,
684 sum(mmt.PRIMARY_QUANTITY)
685 FROM MTL_MATERIAL_TRANSACTIONS mmt,
686 MTL_SYSTEM_ITEMS msi
687 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
688 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
689 AND mmt.ORGANIZATION_ID=Org_id
690 AND msi.LOT_CONTROL_CODE = 1
691 AND mmt.transaction_action_id=32
692 AND mmt.transaction_source_type_id=5
693 AND mmt.transaction_date >= p_from_date
694 AND mmt.transaction_date <= p_to_date
695 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
696 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
697
698 CURSOR assembly_return_with_lot_qty IS
699 SELECT trunc(mmt.TRANSACTION_DATE),
700 mmt.ORGANIZATION_ID,
701 mmt.INVENTORY_ITEM_ID,
702 mmt.COST_GROUP_ID,
703 mmt.REVISION,
704 mtln.LOT_NUMBER,
705 mmt.SUBINVENTORY_CODE,
706 mmt.LOCATOR_ID,
707 sum(mtln.PRIMARY_QUANTITY)
708 FROM MTL_MATERIAL_TRANSACTIONS mmt,
709 MTL_SYSTEM_ITEMS msi,
710 MTL_TRANSACTION_LOT_NUMBERS mtln
711 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
712 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
713 AND mmt.ORGANIZATION_ID=Org_id
714 AND mmt.transaction_id = mtln.transaction_id
715 AND msi.LOT_CONTROL_CODE = 2
716 AND mmt.transaction_action_id=32
717 AND mmt.transaction_source_type_id=5
718 AND mmt.transaction_date >= p_from_date
719 AND mmt.transaction_date <= p_to_date
720 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
721 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
722
723 CURSOR assembly_return_no_lot_val IS
724 SELECT trunc(mmt.TRANSACTION_DATE),
725 mmt.ORGANIZATION_ID,
726 mmt.INVENTORY_ITEM_ID,
727 mmt.COST_GROUP_ID,
728 mmt.REVISION,
729 mmt.SUBINVENTORY_CODE,
730 mmt.LOCATOR_ID,
731 sum(mta.BASE_TRANSACTION_VALUE)
732 FROM MTL_MATERIAL_TRANSACTIONS mmt,
733 MTL_SYSTEM_ITEMS msi,
734 MTL_TRANSACTION_ACCOUNTS mta
735 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
736 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
737 AND mmt.ORGANIZATION_ID=Org_id
738 AND msi.LOT_CONTROL_CODE = 1
739 AND mmt.transaction_id = mta.transaction_id
740 AND mta.accounting_line_type = 1
741 AND mmt.transaction_action_id=32
742 AND mmt.transaction_source_type_id=5
743 AND mmt.transaction_date >= p_from_date
744 AND mmt.transaction_date <= p_to_date
745 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
746 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
747
748 CURSOR assembly_return_with_lot_val IS
749 SELECT trunc(mmt.TRANSACTION_DATE),
750 mmt.ORGANIZATION_ID,
751 mmt.INVENTORY_ITEM_ID,
752 mmt.COST_GROUP_ID,
753 mmt.REVISION,
754 mtln.LOT_NUMBER,
755 mmt.SUBINVENTORY_CODE,
756 mmt.LOCATOR_ID,
757 sum(mta.BASE_TRANSACTION_VALUE)
758 FROM MTL_MATERIAL_TRANSACTIONS mmt,
759 MTL_SYSTEM_ITEMS msi,
760 MTL_TRANSACTION_LOT_NUMBERS mtln,
761 MTL_TRANSACTION_ACCOUNTS mta
762 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
763 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
764 AND mmt.transaction_id = mtln.transaction_id
765 AND mmt.ORGANIZATION_ID=Org_id
766 AND msi.LOT_CONTROL_CODE = 2
767 AND mmt.transaction_id = mta.transaction_id
768 AND mta.accounting_line_type = 1
769 AND mmt.transaction_action_id=32
770 AND mmt.transaction_source_type_id=5
771 AND mmt.transaction_date >= p_from_date
772 AND mmt.transaction_date <= p_to_date
773 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
774 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
775 BEGIN
776
777 edw_log.put_line('CALCWIPRET p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
778 edw_log.put_line('CALCWIPRET p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
779 OPEN assembly_return_no_lot_qty;
780
781 LOOP
782
783 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
784 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
785
786
787 FETCH assembly_return_no_lot_qty
788 INTO l_trx_date,
789 l_organization_id,
790 l_item_id,
791 l_cost_group_id,
792 l_revision,
793 l_subinventory,
794 l_locator,
795 total_qty;
796
797
798 if(assembly_return_no_lot_qty%NOTFOUND) then
799 CLOSE assembly_return_no_lot_qty;
800 exit;
801 end if;
802
803 Insert_update_push_log(
804 p_trx_date => l_trx_date ,
805 p_organization_id => l_organization_id,
806 p_item_id => l_item_id,
807 p_cost_group_id => l_cost_group_id,
808 p_revision => l_revision,
809 p_subinventory => l_subinventory,
810 p_locator => l_locator,
811 p_col_name1 => 'wip_assy_qty',
812 p_total1 => total_qty,
813 selector => 1,
814 success => status);
815
816 if (status > 0) then
817 Retcode := '2';
818 return;
819 end if;
820
821 END LOOP;
822
823
824 OPEN assembly_return_with_lot_qty;
825
826 LOOP
827
828 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
829 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
830
831
832 FETCH assembly_return_with_lot_qty
833 INTO l_trx_date,
834 l_organization_id,
835 l_item_id,
836 l_cost_group_id,
837 l_revision,
838 l_lot_number,
839 l_subinventory,
840 l_locator,
841 total_qty;
842
843
844 if(assembly_return_with_lot_qty%NOTFOUND) then
845 CLOSE assembly_return_with_lot_qty;
846 exit;
847 end if;
848
849 Insert_update_push_log(
850 p_trx_date => l_trx_date ,
851 p_organization_id => l_organization_id,
852 p_item_id => l_item_id,
853 p_cost_group_id => l_cost_group_id,
854 p_revision => l_revision,
855 p_lot_number => l_lot_number,
856 p_subinventory => l_subinventory,
857 p_locator => l_locator,
858 p_col_name1 => 'wip_assy_qty',
859 p_total1 => total_qty,
860 selector => 1,
861 success => status);
862
863 if (status > 0) then
864 Retcode := '2';
865 return;
866 end if;
867
868 END LOOP;
869
870
871 OPEN assembly_return_no_lot_val;
872
873 LOOP
874
875 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
876 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
877
878
879 FETCH assembly_return_no_lot_val
880 INTO l_trx_date,
881 l_organization_id,
882 l_item_id,
883 l_cost_group_id,
884 l_revision,
885 l_subinventory,
886 l_locator,
887 total_value;
888
889 if(assembly_return_no_lot_val%NOTFOUND) then
890 CLOSE assembly_return_no_lot_val;
891 exit;
892 end if;
893
894 Insert_update_push_log(
895 p_trx_date => l_trx_date ,
896 p_organization_id => l_organization_id,
897 p_item_id => l_item_id,
898 p_cost_group_id => l_cost_group_id,
899 p_revision => l_revision,
900 p_subinventory => l_subinventory,
901 p_locator => l_locator,
902 p_col_name1 => 'wip_assy_val_b',
903 p_total1 => total_value,
904 selector => 1,
905 success => status);
906
907 if (status > 0) then
908 Retcode := '2';
909 return;
910 end if;
911
912 END LOOP;
913
914
915 OPEN assembly_return_with_lot_val;
916
917 LOOP
918
919 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
920 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
921
922
923 FETCH assembly_return_with_lot_val
924 INTO l_trx_date,
925 l_organization_id,
926 l_item_id,
927 l_cost_group_id,
928 l_revision,
929 l_lot_number,
930 l_subinventory,
931 l_locator,
932 total_value;
933
934 if(assembly_return_with_lot_val%NOTFOUND) then
935 CLOSE assembly_return_with_lot_val;
936 exit;
937 end if;
938
939 Insert_update_push_log(
940 p_trx_date => l_trx_date ,
941 p_organization_id => l_organization_id,
942 p_item_id => l_item_id,
943 p_cost_group_id => l_cost_group_id,
944 p_revision => l_revision,
945 p_lot_number => l_lot_number,
946 p_subinventory => l_subinventory,
947 p_locator => l_locator,
948 p_col_name1 => 'wip_assy_val_b',
949 p_total1 => total_value,
950 selector => 1,
951 success => status);
952
953
954 if (status > 0) then
955 Retcode := '2';
956 return;
957 end if;
958
959 END LOOP;
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 edw_log.put_line('Error in calc_assembly_return');
964 Retcode := '2';
965
966 end calc_assembly_return;
967
968
969
970
971 /*}{----------------------------------------------
972 PROCEDURE CALC_PO_DELIVERIES
973 ----------------------------------------------*/
974
975 Procedure calc_po_deliveries(Errbuf out nocopy Varchar2,
976 Retcode out nocopy Varchar2,
977 p_from_date IN Date,
978 p_to_date IN Date,
979 Org_id IN Number) IS
980
981 l_trx_date DATE;
982 l_organization_id NUMBER;
983 l_item_id NUMBER;
984 l_cost_group_id NUMBER;
985 l_revision VARCHAR2(3);
986 l_lot_number VARCHAR2(30);
987 l_subinventory VARCHAR2(10);
988 l_locator NUMBER;
989 total_value NUMBER;
990 total_qty NUMBER;
991 trx_type NUMBER;
992 status NUMBER;
993
994
995 -- ltong 01/20/2003. Filtered out consigned inventory.
996 CURSOR po_deliveries_no_lot_qty IS
997 SELECT trunc(mmt.TRANSACTION_DATE),
998 mmt.ORGANIZATION_ID,
999 mmt.INVENTORY_ITEM_ID,
1000 mmt.COST_GROUP_ID,
1001 mmt.REVISION,
1002 mmt.SUBINVENTORY_CODE,
1003 mmt.LOCATOR_ID,
1004 sum(mmt.PRIMARY_QUANTITY)
1005 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1006 MTL_SYSTEM_ITEMS msi
1007 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1008 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1009 AND mmt.ORGANIZATION_ID=Org_id
1010 AND msi.LOT_CONTROL_CODE = 1
1011 AND mmt.transaction_action_id in (1,27,29)
1012 AND mmt.transaction_source_type_id=1
1013 AND mmt.transaction_date >= p_from_date
1014 AND mmt.transaction_date <= p_to_date
1015 AND MMT.organization_id = NVL(MMT.owning_organization_id, MMT.organization_id)
1016 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
1017 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1018 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1019
1020
1021 -- ltong 01/20/2003. Filtered out consigned inventory.
1022 CURSOR po_deliveries_with_lot_qty IS
1023 SELECT trunc(mmt.TRANSACTION_DATE),
1024 mmt.ORGANIZATION_ID,
1025 mmt.INVENTORY_ITEM_ID,
1026 mmt.COST_GROUP_ID,
1027 mmt.REVISION,
1028 mtln.LOT_NUMBER,
1029 mmt.SUBINVENTORY_CODE,
1030 mmt.LOCATOR_ID,
1031 sum(mtln.PRIMARY_QUANTITY)
1032 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1033 MTL_SYSTEM_ITEMS msi,
1034 MTL_TRANSACTION_LOT_NUMBERS mtln
1035 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1036 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1037 AND mmt.ORGANIZATION_ID=Org_id
1038 AND mmt.transaction_id = mtln.transaction_id
1039 AND msi.LOT_CONTROL_CODE = 2
1040 AND mmt.transaction_action_id in (1,27,29)
1041 AND mmt.transaction_source_type_id=1
1042 AND mmt.transaction_date >= p_from_date
1043 AND mmt.transaction_date <= p_to_date
1044 AND MMT.organization_id = NVL(MMT.owning_organization_id, MMT.organization_id)
1045 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
1046 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1047 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1048
1049
1050 -- ltong 01/20/2003. Filtered out consigned inventory.
1051 CURSOR po_deliveries_no_lot_val IS
1052 SELECT trunc(mmt.TRANSACTION_DATE),
1053 mmt.ORGANIZATION_ID,
1054 mmt.INVENTORY_ITEM_ID,
1055 mmt.COST_GROUP_ID,
1056 mmt.REVISION,
1057 mmt.SUBINVENTORY_CODE,
1058 mmt.LOCATOR_ID,
1059 sum(mta.BASE_TRANSACTION_VALUE)
1060 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1061 MTL_SYSTEM_ITEMS msi,
1062 MTL_TRANSACTION_ACCOUNTS mta
1063 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1064 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1065 AND mmt.ORGANIZATION_ID=Org_id
1066 AND msi.LOT_CONTROL_CODE = 1
1067 AND mmt.transaction_id = mta.transaction_id
1068 AND mta.accounting_line_type = 1
1069 AND mmt.transaction_action_id in (1,27,29)
1070 AND mmt.transaction_source_type_id=1
1071 AND mmt.transaction_date >= p_from_date
1072 AND mmt.transaction_date <= p_to_date
1073 AND MMT.organization_id = NVL(MMT.owning_organization_id, MMT.organization_id)
1074 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
1075 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1076 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1077
1078
1079 -- ltong 01/20/2003. Filtered out consigned inventory.
1080 CURSOR po_deliveries_with_lot_val IS
1081 SELECT trunc(mmt.TRANSACTION_DATE),
1082 mmt.ORGANIZATION_ID,
1083 mmt.INVENTORY_ITEM_ID,
1084 mmt.COST_GROUP_ID,
1085 mmt.REVISION,
1086 mtln.LOT_NUMBER,
1087 mmt.SUBINVENTORY_CODE,
1088 mmt.LOCATOR_ID,
1089 sum(mta.BASE_TRANSACTION_VALUE)
1090 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1091 MTL_SYSTEM_ITEMS msi,
1092 MTL_TRANSACTION_LOT_NUMBERS mtln,
1093 MTL_TRANSACTION_ACCOUNTS mta
1094 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1095 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1096 AND mmt.transaction_id = mtln.transaction_id
1097 AND mmt.ORGANIZATION_ID=Org_id
1098 AND msi.LOT_CONTROL_CODE = 2
1099 AND mmt.transaction_id = mta.transaction_id
1100 AND mta.accounting_line_type = 1
1101 AND mmt.transaction_action_id in (1,27,29)
1102 AND mmt.transaction_source_type_id=1
1103 AND mmt.transaction_date >= p_from_date
1104 AND mmt.transaction_date <= p_to_date
1105 AND MMT.organization_id = NVL(MMT.owning_organization_id, MMT.organization_id)
1106 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
1107 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1108 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1109 BEGIN
1110
1111 edw_log.put_line('CALCPODEL p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
1112 edw_log.put_line('CALCPODEL p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
1113
1114 OPEN po_deliveries_no_lot_qty;
1115
1116 LOOP
1117
1118 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1119 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1120
1121
1122 FETCH po_deliveries_no_lot_qty
1123 INTO l_trx_date,
1124 l_organization_id,
1125 l_item_id,
1126 l_cost_group_id,
1127 l_revision,
1128 l_subinventory,
1129 l_locator,
1130 total_qty;
1131
1132
1133 if(po_deliveries_no_lot_qty%NOTFOUND) then
1134 CLOSE po_deliveries_no_lot_qty;
1135 exit;
1136 end if;
1137
1138 Insert_update_push_log(
1139 p_trx_date => l_trx_date ,
1140 p_organization_id => l_organization_id,
1141 p_item_id => l_item_id,
1142 p_cost_group_id => l_cost_group_id,
1143 p_revision => l_revision,
1144 p_subinventory => l_subinventory,
1145 p_locator => l_locator,
1146 p_col_name1 => 'po_del_qty',
1147 p_total1 => total_qty,
1148 selector => 1,
1149 success => status);
1150
1151 if (status > 0) then
1152 Retcode := '2';
1153 return;
1154 end if;
1155
1156 END LOOP;
1157
1158
1159 OPEN po_deliveries_with_lot_qty;
1160
1161 LOOP
1162
1163 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1164 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1165
1166
1167
1168 FETCH po_deliveries_with_lot_qty
1169 INTO l_trx_date,
1170 l_organization_id,
1171 l_item_id,
1172 l_cost_group_id,
1173 l_revision,
1174 l_lot_number,
1175 l_subinventory,
1176 l_locator,
1177 total_qty;
1178
1179
1180 if(po_deliveries_with_lot_qty%NOTFOUND) then
1181 CLOSE po_deliveries_with_lot_qty;
1182 exit;
1183 end if;
1184
1185 Insert_update_push_log(
1186 p_trx_date => l_trx_date ,
1187 p_organization_id => l_organization_id,
1188 p_item_id => l_item_id,
1189 p_cost_group_id => l_cost_group_id,
1190 p_revision => l_revision,
1191 p_lot_number => l_lot_number,
1192 p_subinventory => l_subinventory,
1193 p_locator => l_locator,
1194 p_col_name1 => 'po_del_qty',
1195 p_total1 => total_qty,
1196 selector => 1,
1197 success => status);
1198
1199 if (status > 0) then
1200 Retcode := '2';
1201 return;
1202 end if;
1203
1204 END LOOP;
1205
1206
1207 OPEN po_deliveries_no_lot_val;
1208
1209 LOOP
1210
1211 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1212 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1213
1214 FETCH po_deliveries_no_lot_val
1215 INTO l_trx_date,
1216 l_organization_id,
1217 l_item_id,
1218 l_cost_group_id,
1219 l_revision,
1220 l_subinventory,
1221 l_locator,
1222 total_value;
1223
1224 if(po_deliveries_no_lot_val%NOTFOUND) then
1225 CLOSE po_deliveries_no_lot_val;
1226 exit;
1227 end if;
1228
1229 Insert_update_push_log(
1230 p_trx_date => l_trx_date ,
1231 p_organization_id => l_organization_id,
1232 p_item_id => l_item_id,
1233 p_cost_group_id => l_cost_group_id,
1234 p_revision => l_revision,
1235 p_subinventory => l_subinventory,
1236 p_locator => l_locator,
1237 p_col_name1 => 'po_del_val_b',
1238 p_total1 => total_value,
1239 selector => 1,
1240 success => status);
1241
1242 if (status > 0) then
1243 Retcode := '2';
1244 return;
1245 end if;
1246
1247 END LOOP;
1248
1249
1250 OPEN po_deliveries_with_lot_val;
1251
1252 LOOP
1253
1254 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1255 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1256
1257
1258 FETCH po_deliveries_with_lot_val
1259 INTO l_trx_date,
1260 l_organization_id,
1261 l_item_id,
1262 l_cost_group_id,
1263 l_revision,
1264 l_lot_number,
1265 l_subinventory,
1266 l_locator,
1267 total_value;
1268
1269 if(po_deliveries_with_lot_val%NOTFOUND) then
1270 CLOSE po_deliveries_with_lot_val;
1271 exit;
1272 end if;
1273
1274 Insert_update_push_log(
1275 p_trx_date => l_trx_date ,
1276 p_organization_id => l_organization_id,
1277 p_item_id => l_item_id,
1278 p_cost_group_id => l_cost_group_id,
1279 p_revision => l_revision,
1280 p_lot_number => l_lot_number,
1281 p_subinventory => l_subinventory,
1282 p_locator => l_locator,
1283 p_col_name1 => 'po_del_val_b',
1284 p_total1 => total_value,
1285 selector => 1,
1286 success => status);
1287
1288 if (status > 0) then
1289 Retcode := '2';
1290 return;
1291 end if;
1292
1293 END LOOP;
1294
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 edw_log.put_line('Error in calc_po_deliveries');
1298 Retcode := '2';
1299
1300 end calc_po_deliveries;
1301
1302
1303 /*}{----------------------------------------------
1304 PROCEDURE CALC_VALUE_FROM_ORGS
1305 ----------------------------------------------*/
1306
1307 Procedure calc_value_from_orgs(Errbuf out nocopy Varchar2,
1308 Retcode out nocopy Varchar2,
1309 p_from_date IN Date,
1310 p_to_date IN Date,
1311 Org_id IN Number) IS
1312 l_trx_date DATE;
1313 l_organization_id NUMBER;
1314 l_item_id NUMBER;
1315 l_cost_group_id NUMBER;
1316 l_revision VARCHAR2(3);
1317 l_lot_number VARCHAR2(30);
1318 l_subinventory VARCHAR2(10);
1319 l_locator NUMBER;
1320 total_value NUMBER;
1321 total_qty NUMBER;
1322 trx_type NUMBER;
1323 status NUMBER;
1324
1325 CURSOR value_from_orgs_no_lot_qty IS
1326 SELECT trunc(mmt.TRANSACTION_DATE),
1327 mmt.ORGANIZATION_ID,
1328 mmt.INVENTORY_ITEM_ID,
1329 mmt.COST_GROUP_ID,
1330 mmt.REVISION,
1331 mmt.SUBINVENTORY_CODE,
1332 mmt.LOCATOR_ID,
1333 sum(mmt.PRIMARY_QUANTITY)
1334 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1335 MTL_SYSTEM_ITEMS msi
1336 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1337 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1338 AND mmt.ORGANIZATION_ID=Org_id
1339 AND msi.LOT_CONTROL_CODE = 1
1340 AND mmt.transaction_action_id in (3,12)
1341 AND mmt.primary_quantity > 0
1342 AND mmt.transaction_date >= p_from_date
1343 AND mmt.transaction_date <= p_to_date
1344 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1345 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1346
1347 CURSOR value_from_orgs_with_lot_qty IS
1348 SELECT trunc(mmt.TRANSACTION_DATE),
1349 mmt.ORGANIZATION_ID,
1350 mmt.INVENTORY_ITEM_ID,
1351 mmt.COST_GROUP_ID,
1352 mmt.REVISION,
1353 mtln.LOT_NUMBER,
1354 mmt.SUBINVENTORY_CODE,
1355 mmt.LOCATOR_ID,
1356 sum(mtln.PRIMARY_QUANTITY)
1357 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1358 MTL_SYSTEM_ITEMS msi,
1359 MTL_TRANSACTION_LOT_NUMBERS mtln
1360 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1361 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1362 AND mmt.ORGANIZATION_ID=Org_id
1363 AND mmt.transaction_id = mtln.transaction_id
1364 AND msi.LOT_CONTROL_CODE = 2
1365 AND mmt.transaction_action_id in (3,12)
1366 AND mmt.primary_quantity > 0
1367 AND mmt.transaction_date >= p_from_date
1368 AND mmt.transaction_date <= p_to_date
1369 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1370 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1371
1372 CURSOR value_from_orgs_no_lot_val IS
1373 SELECT trunc(mmt.TRANSACTION_DATE),
1374 mmt.ORGANIZATION_ID,
1375 mmt.INVENTORY_ITEM_ID,
1376 mmt.COST_GROUP_ID,
1377 mmt.REVISION,
1378 mmt.SUBINVENTORY_CODE,
1379 mmt.LOCATOR_ID,
1380 sum(mta.BASE_TRANSACTION_VALUE)
1381 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1382 MTL_SYSTEM_ITEMS msi,
1383 MTL_TRANSACTION_ACCOUNTS mta
1384 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1385 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1386 AND mmt.ORGANIZATION_ID=Org_id
1387 AND msi.LOT_CONTROL_CODE = 1
1388 AND mmt.transaction_id = mta.transaction_id
1389 AND mta.accounting_line_type = 1
1390 AND mmt.transaction_action_id in (3,12)
1391 AND mmt.primary_quantity > 0
1392 AND mmt.transaction_date >= p_from_date
1393 AND mmt.transaction_date <= p_to_date
1394 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1395 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1396
1397 CURSOR value_from_orgs_with_lot_val IS
1398 SELECT trunc(mmt.TRANSACTION_DATE),
1399 mmt.ORGANIZATION_ID,
1400 mmt.INVENTORY_ITEM_ID,
1401 mmt.COST_GROUP_ID,
1402 mmt.REVISION,
1403 mtln.LOT_NUMBER,
1404 mmt.SUBINVENTORY_CODE,
1405 mmt.LOCATOR_ID,
1406 sum(mta.BASE_TRANSACTION_VALUE)
1407 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1408 MTL_SYSTEM_ITEMS msi,
1409 MTL_TRANSACTION_LOT_NUMBERS mtln,
1410 MTL_TRANSACTION_ACCOUNTS mta
1411 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1412 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1413 AND mmt.ORGANIZATION_ID=Org_id
1414 AND mmt.transaction_id = mtln.transaction_id
1415 AND msi.LOT_CONTROL_CODE = 2
1416 AND mmt.transaction_id = mta.transaction_id
1417 AND mta.accounting_line_type = 1
1418 AND mmt.transaction_action_id in (3,12)
1419 AND mmt.primary_quantity > 0
1420 AND mmt.transaction_date >= p_from_date
1421 AND mmt.transaction_date <= p_to_date
1422 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1423 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1424 BEGIN
1425
1426 edw_log.put_line('CALCFROMORG p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
1427 edw_log.put_line('CALCFROMORG p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
1428
1429 OPEN value_from_orgs_no_lot_qty;
1430
1431 LOOP
1432
1433 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1434 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1435
1436
1437 FETCH value_from_orgs_no_lot_qty
1438 INTO l_trx_date,
1439 l_organization_id,
1440 l_item_id,
1441 l_cost_group_id,
1442 l_revision,
1443 l_subinventory,
1444 l_locator,
1445 total_qty;
1446
1447
1448 if(value_from_orgs_no_lot_qty%NOTFOUND) then
1449 CLOSE value_from_orgs_no_lot_qty;
1450 exit;
1451 end if;
1452
1453 Insert_update_push_log(
1454 p_trx_date => l_trx_date ,
1455 p_organization_id => l_organization_id,
1456 p_item_id => l_item_id,
1457 p_cost_group_id => l_cost_group_id,
1458 p_revision => l_revision,
1459 p_subinventory => l_subinventory,
1460 p_locator => l_locator,
1461 p_col_name1 => 'from_org_qty',
1462 p_total1 => total_qty,
1463 selector => 1,
1464 success => status);
1465
1466 if (status > 0) then
1467 Retcode := '2';
1468 return;
1469 end if;
1470
1471 END LOOP;
1472
1473
1474 OPEN value_from_orgs_with_lot_qty;
1475
1476 LOOP
1477
1478 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1479 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1480
1481
1482 FETCH value_from_orgs_with_lot_qty
1483 INTO l_trx_date,
1484 l_organization_id,
1485 l_item_id,
1486 l_cost_group_id,
1487 l_revision,
1488 l_lot_number,
1489 l_subinventory,
1490 l_locator,
1491 total_qty;
1492
1493
1494 if(value_from_orgs_with_lot_qty%NOTFOUND) then
1495 CLOSE value_from_orgs_with_lot_qty;
1496 exit;
1497 end if;
1498
1499 Insert_update_push_log(
1500 p_trx_date => l_trx_date ,
1501 p_organization_id => l_organization_id,
1502 p_item_id => l_item_id,
1503 p_cost_group_id => l_cost_group_id,
1504 p_revision => l_revision,
1505 p_lot_number => l_lot_number,
1506 p_subinventory => l_subinventory,
1507 p_locator => l_locator,
1508 p_col_name1 => 'from_org_qty',
1509 p_total1 => total_qty,
1510 selector => 1,
1511 success => status);
1512
1513 if (status > 0) then
1514 Retcode := '2';
1515 return;
1516 end if;
1517
1518 END LOOP;
1519
1520
1521 OPEN value_from_orgs_no_lot_val;
1522
1523 LOOP
1524
1525 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1526 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1527
1528
1529 FETCH value_from_orgs_no_lot_val
1530 INTO l_trx_date,
1531 l_organization_id,
1532 l_item_id,
1533 l_cost_group_id,
1534 l_revision,
1535 l_subinventory,
1536 l_locator,
1537 total_value;
1538
1539 if(value_from_orgs_no_lot_val%NOTFOUND) then
1540 CLOSE value_from_orgs_no_lot_val;
1541 exit;
1542 end if;
1543
1544 Insert_update_push_log(
1545 p_trx_date => l_trx_date ,
1546 p_organization_id => l_organization_id,
1547 p_item_id => l_item_id,
1548 p_cost_group_id => l_cost_group_id,
1549 p_revision => l_revision,
1550 p_subinventory => l_subinventory,
1551 p_locator => l_locator,
1552 p_col_name1 => 'from_org_val_b',
1553 p_total1 => total_value,
1554 selector => 1,
1555 success => status);
1556
1557 if (status > 0) then
1558 Retcode := '2';
1559 return;
1560 end if;
1561
1562 END LOOP;
1563
1564
1565 OPEN value_from_orgs_with_lot_val;
1566
1567 LOOP
1568
1569 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1570 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1571
1572
1573 FETCH value_from_orgs_with_lot_val
1574 INTO l_trx_date,
1575 l_organization_id,
1576 l_item_id,
1577 l_cost_group_id,
1578 l_revision,
1579 l_lot_number,
1580 l_subinventory,
1581 l_locator,
1582 total_value;
1583
1584 if(value_from_orgs_with_lot_val%NOTFOUND) then
1585 CLOSE value_from_orgs_with_lot_val;
1586 exit;
1587 end if;
1588
1589 Insert_update_push_log(
1590 p_trx_date => l_trx_date ,
1591 p_organization_id => l_organization_id,
1592 p_item_id => l_item_id,
1593 p_cost_group_id => l_cost_group_id,
1594 p_revision => l_revision,
1595 p_lot_number => l_lot_number,
1596 p_subinventory => l_subinventory,
1597 p_locator => l_locator,
1598 p_col_name1 => 'from_org_val_b',
1599 p_total1 => total_value,
1600 selector => 1,
1601 success => status);
1602
1603 if (status > 0) then
1604 Retcode := '2';
1605 return;
1606 end if;
1607
1608 END LOOP;
1609
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 edw_log.put_line('Error in calc_value_from_orgs');
1613 Retcode := '2';
1614
1615 end calc_value_from_orgs;
1616
1617
1618
1619 /*}{----------------------------------------------
1620 PROCEDURE CALC_VALUE_TO_ORGS
1621 ----------------------------------------------*/
1622
1623 Procedure calc_value_to_orgs(Errbuf out nocopy Varchar2,
1624 Retcode out nocopy Varchar2,
1625 p_from_date IN Date,
1626 p_to_date IN Date,
1627 Org_id IN Number) IS
1628 l_trx_date DATE;
1629 l_organization_id NUMBER;
1630 l_item_id NUMBER;
1631 l_cost_group_id NUMBER;
1632 l_revision VARCHAR2(3);
1633 l_lot_number VARCHAR2(30);
1634 l_subinventory VARCHAR2(10);
1635 l_locator NUMBER;
1636 total_value NUMBER;
1637 total_qty NUMBER;
1638 trx_type NUMBER;
1639 status NUMBER;
1640
1641
1642 CURSOR value_to_orgs_no_lot_qty IS
1643 SELECT trunc(mmt.TRANSACTION_DATE),
1644 mmt.ORGANIZATION_ID,
1645 mmt.INVENTORY_ITEM_ID,
1646 mmt.COST_GROUP_ID,
1647 mmt.REVISION,
1648 mmt.SUBINVENTORY_CODE,
1649 mmt.LOCATOR_ID,
1650 sum(mmt.PRIMARY_QUANTITY)
1651 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1652 MTL_SYSTEM_ITEMS msi
1653 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1654 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1655 AND mmt.ORGANIZATION_ID=Org_id
1656 AND msi.LOT_CONTROL_CODE = 1
1657 AND mmt.transaction_action_id in (3,21)
1658 AND mmt.primary_quantity < 0
1659 AND mmt.transaction_date >= p_from_date
1660 AND mmt.transaction_date <= p_to_date
1661 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1662 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1663
1664 CURSOR value_to_orgs_with_lot_qty IS
1665 SELECT trunc(mmt.TRANSACTION_DATE),
1666 mmt.ORGANIZATION_ID,
1667 mmt.INVENTORY_ITEM_ID,
1668 mmt.COST_GROUP_ID,
1669 mmt.REVISION,
1670 mtln.LOT_NUMBER,
1671 mmt.SUBINVENTORY_CODE,
1672 mmt.LOCATOR_ID,
1673 sum(mtln.PRIMARY_QUANTITY)
1674 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1675 MTL_SYSTEM_ITEMS msi,
1676 MTL_TRANSACTION_LOT_NUMBERS mtln
1677 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1678 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1679 AND mmt.ORGANIZATION_ID=Org_id
1680 AND mmt.transaction_id = mtln.transaction_id
1681 AND msi.LOT_CONTROL_CODE = 2
1682 AND mmt.transaction_action_id in (3,21)
1683 AND mmt.primary_quantity < 0
1684 AND mmt.transaction_date >= p_from_date
1685 AND mmt.transaction_date <= p_to_date
1686 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1687 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1688
1689 CURSOR value_to_orgs_no_lot_val IS
1690 SELECT trunc(mmt.TRANSACTION_DATE),
1691 mmt.ORGANIZATION_ID,
1692 mmt.INVENTORY_ITEM_ID,
1693 mmt.COST_GROUP_ID,
1694 mmt.REVISION,
1695 mmt.SUBINVENTORY_CODE,
1696 mmt.LOCATOR_ID,
1697 sum(mta.BASE_TRANSACTION_VALUE)
1698 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1699 MTL_SYSTEM_ITEMS msi,
1700 MTL_TRANSACTION_ACCOUNTS mta
1701 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1702 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1703 AND mmt.ORGANIZATION_ID=Org_id
1704 AND msi.LOT_CONTROL_CODE = 1
1705 AND mmt.transaction_id = mta.transaction_id
1706 AND mmt.organization_id=mta.organization_id
1707 AND mta.accounting_line_type = 1
1708 AND mmt.transaction_action_id in (3,21)
1709 AND mmt.primary_quantity < 0
1710 AND mmt.transaction_date >= p_from_date
1711 AND mmt.transaction_date <= p_to_date
1712 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1713 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1714
1715 CURSOR value_to_orgs_with_lot_val IS
1716 SELECT trunc(mmt.TRANSACTION_DATE),
1717 mmt.ORGANIZATION_ID,
1718 mmt.INVENTORY_ITEM_ID,
1719 mmt.COST_GROUP_ID,
1720 mmt.REVISION,
1721 mtln.LOT_NUMBER,
1722 mmt.SUBINVENTORY_CODE,
1723 mmt.LOCATOR_ID,
1724 sum(mta.BASE_TRANSACTION_VALUE)
1725 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1726 MTL_SYSTEM_ITEMS msi,
1727 MTL_TRANSACTION_LOT_NUMBERS mtln,
1728 MTL_TRANSACTION_ACCOUNTS mta
1729 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1730 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1731 AND mmt.ORGANIZATION_ID=Org_id
1732 AND mmt.transaction_id = mtln.transaction_id
1733 AND msi.LOT_CONTROL_CODE = 2
1734 AND mmt.transaction_id = mta.transaction_id
1735 AND mmt.organization_id=mta.organization_id
1736 AND mta.accounting_line_type = 1
1737 AND mmt.transaction_action_id in (3,21)
1738 AND mmt.primary_quantity < 0
1739 AND mmt.transaction_date >= p_from_date
1740 AND mmt.transaction_date <= p_to_date
1741 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1742 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1743 BEGIN
1744
1745 edw_log.put_line('CALCTOORG p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
1746 edw_log.put_line('CALCTOORG p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
1747
1748 OPEN value_to_orgs_no_lot_qty;
1749
1750 LOOP
1751
1752 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1753 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1754
1755
1756 FETCH value_to_orgs_no_lot_qty
1757 INTO l_trx_date,
1758 l_organization_id,
1759 l_item_id,
1760 l_cost_group_id,
1761 l_revision,
1762 l_subinventory,
1763 l_locator,
1764 total_qty;
1765
1766
1767 if(value_to_orgs_no_lot_qty%NOTFOUND) then
1768 CLOSE value_to_orgs_no_lot_qty;
1769 exit;
1770 end if;
1771
1772 Insert_update_push_log(
1773 p_trx_date => l_trx_date ,
1774 p_organization_id => l_organization_id,
1775 p_item_id => l_item_id,
1776 p_cost_group_id => l_cost_group_id,
1777 p_revision => l_revision,
1778 p_subinventory => l_subinventory,
1779 p_locator => l_locator,
1780 p_col_name1 => 'to_org_qty',
1781 p_total1 => total_qty,
1782 selector => 1,
1783 success => status);
1784
1785 if (status > 0) then
1786 Retcode := '2';
1787 return;
1788 end if;
1789
1790 END LOOP;
1791
1792
1793 OPEN value_to_orgs_with_lot_qty;
1794
1795 LOOP
1796
1797 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1798 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1799
1800
1801 FETCH value_to_orgs_with_lot_qty
1802 INTO l_trx_date,
1803 l_organization_id,
1804 l_item_id,
1805 l_cost_group_id,
1806 l_revision,
1807 l_lot_number,
1808 l_subinventory,
1809 l_locator,
1810 total_qty;
1811
1812
1813 if(value_to_orgs_with_lot_qty%NOTFOUND) then
1814 CLOSE value_to_orgs_with_lot_qty;
1815 exit;
1816 end if;
1817
1818 Insert_update_push_log(
1819 p_trx_date => l_trx_date ,
1820 p_organization_id => l_organization_id,
1821 p_item_id => l_item_id,
1822 p_cost_group_id => l_cost_group_id,
1823 p_revision => l_revision,
1824 p_lot_number => l_lot_number,
1825 p_subinventory => l_subinventory,
1826 p_locator => l_locator,
1827 p_col_name1 => 'to_org_qty',
1828 p_total1 => total_qty,
1829 selector => 1,
1830 success => status);
1831
1832 if (status > 0) then
1833 Retcode := '2';
1834 return;
1835 end if;
1836
1837 END LOOP;
1838
1839
1840 OPEN value_to_orgs_no_lot_val;
1841
1842 LOOP
1843
1844 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1845 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1846
1847
1848 FETCH value_to_orgs_no_lot_val
1849 INTO l_trx_date,
1850 l_organization_id,
1851 l_item_id,
1852 l_cost_group_id,
1853 l_revision,
1854 l_subinventory,
1855 l_locator,
1856 total_value;
1857
1858 if(value_to_orgs_no_lot_val%NOTFOUND) then
1859 CLOSE value_to_orgs_no_lot_val;
1860 exit;
1861 end if;
1862
1863 Insert_update_push_log(
1864 p_trx_date => l_trx_date ,
1865 p_organization_id => l_organization_id,
1866 p_item_id => l_item_id,
1867 p_cost_group_id => l_cost_group_id,
1868 p_revision => l_revision,
1869 p_subinventory => l_subinventory,
1870 p_locator => l_locator,
1871 p_col_name1 => 'to_org_val_b',
1872 p_total1 => total_value,
1873 selector => 1,
1874 success => status);
1875
1876 if (status > 0) then
1877 Retcode := '2';
1878 return;
1879 end if;
1880
1881 END LOOP;
1882
1883
1884 OPEN value_to_orgs_with_lot_val;
1885
1886 LOOP
1887
1888 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
1889 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
1890
1891
1892 FETCH value_to_orgs_with_lot_val
1893 INTO l_trx_date,
1894 l_organization_id,
1895 l_item_id,
1896 l_cost_group_id,
1897 l_revision,
1898 l_lot_number,
1899 l_subinventory,
1900 l_locator,
1901 total_value;
1902
1903 if(value_to_orgs_with_lot_val%NOTFOUND) then
1904 CLOSE value_to_orgs_with_lot_val;
1905 exit;
1906 end if;
1907
1908 Insert_update_push_log(
1909 p_trx_date => l_trx_date ,
1910 p_organization_id => l_organization_id,
1911 p_item_id => l_item_id,
1912 p_cost_group_id => l_cost_group_id,
1913 p_revision => l_revision,
1914 p_lot_number => l_lot_number,
1915 p_subinventory => l_subinventory,
1916 p_locator => l_locator,
1917 p_col_name1 => 'to_org_val_b',
1918 p_total1 => total_value,
1919 selector => 1,
1920 success => status);
1921
1922 if (status > 0) then
1923 Retcode := '2';
1924 return;
1925 end if;
1926
1927 END LOOP;
1928
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 edw_log.put_line('Error in calc_value_to_orgs');
1932 Retcode := '2';
1933
1934 end calc_value_to_orgs;
1935
1936
1937
1938
1939 /*}{----------------------------------------------
1940 PROCEDURE CALC_CUSTOMER_SHIPMENT
1941 ----------------------------------------------*/
1942
1943
1944
1945
1946 Procedure calc_customer_shipment(Errbuf out nocopy Varchar2,
1947 Retcode out nocopy Varchar2,
1948 p_from_date IN Date,
1949 p_to_date IN Date,
1950 Org_id IN Number) IS
1951 l_trx_date DATE;
1952 l_organization_id NUMBER;
1953 l_item_id NUMBER;
1954 l_cost_group_id NUMBER;
1955 l_revision VARCHAR2(3);
1956 l_lot_number VARCHAR2(30);
1957 l_subinventory VARCHAR2(10);
1958 l_locator NUMBER;
1959 total_value NUMBER;
1960 total_qty NUMBER;
1961 trx_type NUMBER;
1962 status NUMBER;
1963
1964 CURSOR customer_shipment_no_lot_qty IS
1965 SELECT trunc(mmt.TRANSACTION_DATE),
1966 mmt.ORGANIZATION_ID,
1967 mmt.INVENTORY_ITEM_ID,
1968 mmt.COST_GROUP_ID,
1969 mmt.REVISION,
1970 mmt.SUBINVENTORY_CODE,
1971 mmt.LOCATOR_ID,
1972 sum(mmt.PRIMARY_QUANTITY)
1973 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1974 MTL_SYSTEM_ITEMS msi
1975 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1976 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1977 AND mmt.ORGANIZATION_ID=Org_id
1978 AND msi.LOT_CONTROL_CODE = 1
1979 AND mmt.transaction_action_id in (1,27)
1980 AND mmt.transaction_source_type_id in (2,8,12)
1981 AND mmt.transaction_date >= p_from_date
1982 AND mmt.transaction_date <= p_to_date
1983 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
1984 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
1985
1986 CURSOR customer_shipment_with_lot_qty IS
1987 SELECT trunc(mmt.TRANSACTION_DATE),
1988 mmt.ORGANIZATION_ID,
1989 mmt.INVENTORY_ITEM_ID,
1990 mmt.COST_GROUP_ID,
1991 mmt.REVISION,
1992 mtln.LOT_NUMBER,
1993 mmt.SUBINVENTORY_CODE,
1994 mmt.LOCATOR_ID,
1995 sum(mtln.PRIMARY_QUANTITY)
1996 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1997 MTL_SYSTEM_ITEMS msi,
1998 MTL_TRANSACTION_LOT_NUMBERS mtln
1999 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2000 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2001 AND mmt.transaction_id = mtln.transaction_id
2002 AND mmt.ORGANIZATION_ID=Org_id
2003 AND msi.LOT_CONTROL_CODE = 2
2004 AND mmt.transaction_action_id in (1,27)
2005 AND mmt.transaction_source_type_id in (2,8,12)
2006 AND mmt.transaction_date >= p_from_date
2007 AND mmt.transaction_date <= p_to_date
2008 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2009 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2010 -------------------------------------------------------
2011 -- begin 11.5.10 changes
2012 -- Replace old Cursors for item value with new cursors
2013 -------------------------------------------------------
2014 /*
2015 CURSOR customer_shipment_no_lot_val IS
2016 SELECT trunc(mmt.TRANSACTION_DATE),
2017 mmt.ORGANIZATION_ID,
2018 mmt.INVENTORY_ITEM_ID,
2019 mmt.COST_GROUP_ID,
2020 mmt.REVISION,
2021 mmt.SUBINVENTORY_CODE,
2022 mmt.LOCATOR_ID,
2023 sum(mta.BASE_TRANSACTION_VALUE)
2024 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2025 MTL_SYSTEM_ITEMS msi,
2026 MTL_TRANSACTION_ACCOUNTS mta
2027 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2028 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2029 AND mmt.ORGANIZATION_ID=Org_id
2030 AND msi.LOT_CONTROL_CODE = 1
2031 AND mmt.transaction_id = mta.transaction_id
2032 AND mta.accounting_line_type = 1
2033 AND mmt.transaction_action_id in (1,27)
2034 AND mmt.transaction_source_type_id in (2,8,12)
2035 AND mmt.transaction_date >= p_from_date
2036 AND mmt.transaction_date <= p_to_date
2037 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2038 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2039
2040 CURSOR customer_shipment_with_lot_val IS
2041 SELECT trunc(mmt.TRANSACTION_DATE),
2042 mmt.ORGANIZATION_ID,
2043 mmt.INVENTORY_ITEM_ID,
2044 mmt.COST_GROUP_ID,
2045 mmt.REVISION,
2046 mtln.LOT_NUMBER,
2047 mmt.SUBINVENTORY_CODE,
2048 mmt.LOCATOR_ID,
2049 sum(mta.BASE_TRANSACTION_VALUE)
2050 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2051 MTL_SYSTEM_ITEMS msi,
2052 MTL_TRANSACTION_LOT_NUMBERS mtln,
2053 MTL_TRANSACTION_ACCOUNTS mta
2054 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2055 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2056 AND mmt.ORGANIZATION_ID=Org_id
2057 AND mmt.transaction_id = mtln.transaction_id
2058 AND msi.LOT_CONTROL_CODE = 2
2059 AND mmt.transaction_id = mta.transaction_id
2060 AND mmt.transaction_action_id in (1,27)
2061 AND mmt.transaction_source_type_id in (2,8,12)
2062 AND mta.accounting_line_type = 1
2063 AND mmt.transaction_date >= p_from_date
2064 AND mmt.transaction_date <= p_to_date
2065 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2066 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2067 */
2068 CURSOR customer_shipment_no_lot_val IS
2069 SELECT TRANSACTION_DATE,
2070 ORGANIZATION_ID,
2071 INVENTORY_ITEM_ID,
2072 COST_GROUP_ID,
2073 REVISION,
2074 SUBINVENTORY_CODE,
2075 LOCATOR_ID,
2076 sum(BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2077 FROM
2078 (
2079 /* Regular Sales Transactions (no logical flow)*/
2080 SELECT trunc(mmt.TRANSACTION_DATE)TRANSACTION_DATE,
2081 mmt.ORGANIZATION_ID,
2082 mmt.INVENTORY_ITEM_ID,
2083 mmt.COST_GROUP_ID,
2084 mmt.REVISION,
2085 mmt.SUBINVENTORY_CODE,
2086 mmt.LOCATOR_ID,
2087 sum(mta.BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2088 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2089 MTL_SYSTEM_ITEMS msi,
2090 MTL_TRANSACTION_ACCOUNTS mta
2091 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2092 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2093 AND mmt.ORGANIZATION_ID=Org_id
2094 AND msi.LOT_CONTROL_CODE = 1
2095 AND mmt.TRANSACTION_ID = mta.TRANSACTION_ID
2096 AND mta.ACCOUNTING_LINE_TYPE = 1
2097 AND mmt.TRANSACTION_ACTION_ID IN (1,27)
2098 AND mmt.TRANSACTION_SOURCE_TYPE_ID IN (2,8,12)
2099 AND mmt.TRANSACTION_DATE >= p_from_date
2100 AND mmt.TRANSACTION_DATE <= p_to_date
2101 GROUP BY trunc(mmt.TRANSACTION_DATE), mmt.ORGANIZATION_ID, mmt.INVENTORY_ITEM_ID,
2102 mmt.COST_GROUP_ID, mmt.REVISION, mmt.SUBINVENTORY_CODE, mmt.LOCATOR_ID
2103 UNION
2104 /* Sales Orders and RMA Receipts in Internal Drop Ship to Customer*/
2105 SELECT trunc(mmt1.TRANSACTION_DATE)TRANSACTION_DATE,
2106 mmt1.ORGANIZATION_ID,
2107 mmt1.INVENTORY_ITEM_ID,
2108 mmt1.COST_GROUP_ID,
2109 mmt1.REVISION,
2110 mmt1.SUBINVENTORY_CODE,
2111 mmt1.LOCATOR_ID,
2112 sum(mta.BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2113 FROM MTL_MATERIAL_TRANSACTIONS mmt1, --Parent Physical Txns
2114 MTL_MATERIAL_TRANSACTIONS mmt2, --Logical (Child) Txns
2115 MTL_SYSTEM_ITEMS msi,
2116 MTL_TRANSACTION_ACCOUNTS mta
2117 WHERE mmt1.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2118 AND mmt1.ORGANIZATION_ID=msi.ORGANIZATION_ID
2119 AND mmt1.ORGANIZATION_ID=Org_id
2120 AND msi.LOT_CONTROL_CODE = 1
2121 AND mmt1.TRANSACTION_ACTION_ID IN (1, 27)
2122 AND mmt1.TRANSACTION_SOURCE_TYPE_ID IN (2, 12)
2123 AND mmt1.TRANSACTION_DATE >= p_from_date
2124 AND mmt1.TRANSACTION_DATE <= p_to_date
2125 AND mmt2.TRANSACTION_ID = mta.TRANSACTION_ID
2126 AND mta.ACCOUNTING_LINE_TYPE = 1
2127 /* logical txn triggered by this parent txn*/
2128 AND mmt2.PARENT_TRANSACTION_ID = mmt1.TRANSACTION_ID
2129 AND mmt2.TRANSACTION_TYPE_ID IN (11, 14)
2130 AND mmt2.ORGANIZATION_ID = Org_id
2131 GROUP BY trunc(mmt1.TRANSACTION_DATE), mmt1.ORGANIZATION_ID, mmt1.INVENTORY_ITEM_ID,
2132 mmt1.COST_GROUP_ID, mmt1.REVISION, mmt1.SUBINVENTORY_CODE, mmt1.LOCATOR_ID
2133 )
2134 GROUP BY TRANSACTION_DATE, ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_GROUP_ID, REVISION, SUBINVENTORY_CODE, LOCATOR_ID;
2135
2136 CURSOR customer_shipment_with_lot_val IS
2137 SELECT TRANSACTION_DATE,
2138 ORGANIZATION_ID,
2139 INVENTORY_ITEM_ID,
2140 COST_GROUP_ID,
2141 REVISION,
2142 LOT_NUMBER,
2143 SUBINVENTORY_CODE,
2144 LOCATOR_ID,
2145 sum(BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2146 FROM
2147 (
2148 /* Regular Sales Transactions (no logical flow)*/
2149 SELECT trunc(mmt.TRANSACTION_DATE)TRANSACTION_DATE,
2150 mmt.ORGANIZATION_ID,
2151 mmt.INVENTORY_ITEM_ID,
2152 mmt.COST_GROUP_ID,
2153 mmt.REVISION,
2154 mtln.LOT_NUMBER,
2155 mmt.SUBINVENTORY_CODE,
2156 mmt.LOCATOR_ID,
2157 sum(mta.BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2158 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2159 MTL_SYSTEM_ITEMS msi,
2160 MTL_TRANSACTION_LOT_NUMBERS mtln,
2161 MTL_TRANSACTION_ACCOUNTS mta
2162 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2163 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2164 AND mmt.ORGANIZATION_ID=Org_id
2165 AND mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
2166 AND msi.LOT_CONTROL_CODE = 2
2167 AND mmt.TRANSACTION_ID = mta.TRANSACTION_ID
2168 AND mmt.TRANSACTION_ACTION_ID IN (1,27)
2169 AND mmt.TRANSACTION_SOURCE_TYPE_ID in (2,8,12)
2170 AND mta.ACCOUNTING_LINE_TYPE = 1
2171 AND mmt.TRANSACTION_DATE >= p_from_date
2172 AND mmt.TRANSACTION_DATE <= p_to_date
2173 GROUP BY trunc(mmt.TRANSACTION_DATE), mmt.ORGANIZATION_ID, mmt.INVENTORY_ITEM_ID, mmt.COST_GROUP_ID,
2174 mmt.REVISION, mtln.LOT_NUMBER, mmt.SUBINVENTORY_CODE, mmt.LOCATOR_ID
2175 UNION
2176 /* Sales Orders and RMA Receipts in Internal Drop Ship to Customer*/
2177 SELECT trunc(mmt1.TRANSACTION_DATE)TRANSACTION_DATE,
2178 mmt1.ORGANIZATION_ID,
2179 mmt1.INVENTORY_ITEM_ID,
2180 mmt1.COST_GROUP_ID,
2181 mmt1.REVISION,
2182 mtln.LOT_NUMBER,
2183 mmt1.SUBINVENTORY_CODE,
2184 mmt1.LOCATOR_ID,
2185 sum(mta.BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2186 FROM MTL_MATERIAL_TRANSACTIONS mmt1, --Parent Physical Txns
2187 MTL_MATERIAL_TRANSACTIONS mmt2, --Logical (Child) Txns
2188 MTL_SYSTEM_ITEMS msi,
2189 MTL_TRANSACTION_LOT_NUMBERS mtln,
2190 MTL_TRANSACTION_ACCOUNTS mta
2191 WHERE mmt1.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2192 AND mmt1.ORGANIZATION_ID=msi.ORGANIZATION_ID
2193 AND mmt1.ORGANIZATION_ID=Org_id
2194 AND mmt1.TRANSACTION_ID = mtln.TRANSACTION_ID
2195 AND msi.LOT_CONTROL_CODE = 2
2196 AND mmt1.TRANSACTION_ACTION_ID IN (1,27)
2197 AND mmt1.TRANSACTION_SOURCE_TYPE_ID IN (2,12)
2198 AND mmt1.TRANSACTION_DATE >= p_from_date
2199 AND mmt1.TRANSACTION_DATE <= p_to_date
2200 AND mmt2.TRANSACTION_ID = mta.TRANSACTION_ID
2201 AND mta.ACCOUNTING_LINE_TYPE = 1
2202 /* logical txn triggered by this parent txn*/
2203 AND mmt2.PARENT_TRANSACTION_ID = mmt1.TRANSACTION_ID
2204 AND mmt2.ORGANIZATION_ID=Org_id
2205 AND mmt2.TRANSACTION_TYPE_ID IN (11,14)
2206 GROUP BY trunc(mmt1.TRANSACTION_DATE), mmt1.ORGANIZATION_ID, mmt1.INVENTORY_ITEM_ID, mmt1.COST_GROUP_ID,
2207 mmt1.REVISION, mtln.LOT_NUMBER, mmt1.SUBINVENTORY_CODE, mmt1.LOCATOR_ID
2208 )
2209 GROUP BY TRANSACTION_DATE, ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_GROUP_ID,
2210 REVISION, LOT_NUMBER, SUBINVENTORY_CODE, LOCATOR_ID;
2211 -------------------------------------------------------------------------------
2212 -- End 11.5.10 changes*/
2213 ------------------------------------------------------------------------------
2214 BEGIN
2215
2216 edw_log.put_line('CALCcstship p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
2217 edw_log.put_line('CALCcstship p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
2218
2219 OPEN customer_shipment_no_lot_qty;
2220
2221
2222 LOOP
2223
2224 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
2225 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
2226
2227
2228 FETCH customer_shipment_no_lot_qty
2229 INTO l_trx_date,
2230 l_organization_id,
2231 l_item_id,
2232 l_cost_group_id,
2233 l_revision,
2234 l_subinventory,
2235 l_locator,
2236 total_qty;
2237
2238
2239 if(customer_shipment_no_lot_qty%NOTFOUND) then
2240 CLOSE customer_shipment_no_lot_qty;
2241 exit;
2242 end if;
2243
2244 Insert_update_push_log(
2245 p_trx_date => l_trx_date ,
2246 p_organization_id => l_organization_id,
2247 p_item_id => l_item_id,
2248 p_cost_group_id => l_cost_group_id,
2249 p_revision => l_revision,
2250 p_subinventory => l_subinventory,
2251 p_locator => l_locator,
2252 p_col_name1 => 'tot_cust_ship_qty',
2253 p_total1 => total_qty,
2254 selector => 1,
2255 success => status);
2256
2257 if (status > 0) then
2258 Retcode := '2';
2259 return;
2260 end if;
2261
2262 END LOOP;
2263
2264
2265 OPEN customer_shipment_with_lot_qty;
2266
2267 LOOP
2268
2269 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
2270 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
2271
2272
2273 FETCH customer_shipment_with_lot_qty
2274 INTO l_trx_date,
2275 l_organization_id,
2276 l_item_id,
2277 l_cost_group_id,
2278 l_revision,
2279 l_lot_number,
2280 l_subinventory,
2281 l_locator,
2282 total_qty;
2283
2284
2285 if(customer_shipment_with_lot_qty%NOTFOUND) then
2286 CLOSE customer_shipment_with_lot_qty;
2287 exit;
2288 end if;
2289
2290 Insert_update_push_log(
2291 p_trx_date => l_trx_date ,
2292 p_organization_id => l_organization_id,
2293 p_item_id => l_item_id,
2294 p_cost_group_id => l_cost_group_id,
2295 p_revision => l_revision,
2296 p_lot_number => l_lot_number,
2297 p_subinventory => l_subinventory,
2298 p_locator => l_locator,
2299 p_col_name1 => 'tot_cust_ship_qty',
2300 p_total1 => total_qty,
2301 selector => 1,
2302 success => status);
2303
2304 if (status > 0) then
2305 Retcode := '2';
2306 return;
2307 end if;
2308
2309 END LOOP;
2310
2311
2312 OPEN customer_shipment_no_lot_val;
2313
2314 LOOP
2315
2316 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
2317 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
2318
2319
2320 FETCH customer_shipment_no_lot_val
2321 INTO l_trx_date,
2322 l_organization_id,
2323 l_item_id,
2324 l_cost_group_id,
2325 l_revision,
2326 l_subinventory,
2327 l_locator,
2328 total_value;
2329
2330 if(customer_shipment_no_lot_val%NOTFOUND) then
2331 CLOSE customer_shipment_no_lot_val;
2332 exit;
2333 end if;
2334
2335 Insert_update_push_log(
2336 p_trx_date => l_trx_date ,
2337 p_organization_id => l_organization_id,
2338 p_item_id => l_item_id,
2339 p_cost_group_id => l_cost_group_id,
2340 p_revision => l_revision,
2341 p_subinventory => l_subinventory,
2342 p_locator => l_locator,
2343 p_col_name1 => 'tot_cust_ship_val_b',
2344 p_total1 => total_value,
2345 selector => 1,
2346 success => status);
2347
2348 if (status > 0) then
2349 Retcode := '2';
2350 return;
2351 end if;
2352
2353 END LOOP;
2354
2355
2356 OPEN customer_shipment_with_lot_val;
2357
2358 LOOP
2359 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
2360 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
2361
2362
2363 FETCH customer_shipment_with_lot_val
2364 INTO l_trx_date,
2365 l_organization_id,
2366 l_item_id,
2367 l_cost_group_id,
2368 l_revision,
2369 l_lot_number,
2370 l_subinventory,
2371 l_locator,
2372 total_value;
2373
2374 if(customer_shipment_with_lot_val%NOTFOUND) then
2375 CLOSE customer_shipment_with_lot_val;
2376 exit;
2377 end if;
2378
2379 Insert_update_push_log(
2380 p_trx_date => l_trx_date ,
2381 p_organization_id => l_organization_id,
2382 p_item_id => l_item_id,
2383 p_cost_group_id => l_cost_group_id,
2384 p_revision => l_revision,
2385 p_lot_number => l_lot_number,
2386 p_subinventory => l_subinventory,
2387 p_locator => l_locator,
2388 p_col_name1 => 'tot_cust_ship_val_b',
2389 p_total1 => total_value,
2390 selector => 1,
2391 success => status);
2392
2393 if (status > 0) then
2394 Retcode := '2';
2395 return;
2396 end if;
2397
2398 END LOOP;
2399
2400 EXCEPTION
2401 WHEN OTHERS THEN
2402 edw_log.put_line('Error in calc_customer_shipment');
2403 Retcode := '2';
2404
2405 end calc_customer_shipment;
2406
2407
2408 /*}{----------------------------------------------
2409 PROCEDURE CALC_INV_ADJUSTMENT
2410 ----------------------------------------------*/
2411
2412 Procedure calc_inv_adjustment(Errbuf out nocopy Varchar2,
2413 Retcode out nocopy Varchar2,
2414 p_from_date IN Date,
2415 p_to_date IN Date,
2416 Org_id IN Number) IS
2417 l_trx_date DATE;
2418 l_organization_id NUMBER;
2419 l_item_id NUMBER;
2420 l_cost_group_id NUMBER;
2421 l_revision VARCHAR2(3);
2422 l_lot_number VARCHAR2(30);
2423 l_subinventory VARCHAR2(10);
2424 l_locator NUMBER;
2425 total_value NUMBER;
2426 total_qty NUMBER;
2427 trx_type NUMBER;
2428 status NUMBER;
2429
2430
2431 CURSOR inv_adj_no_lot_qty IS
2432 SELECT trunc(mmt.TRANSACTION_DATE),
2433 mmt.ORGANIZATION_ID,
2434 mmt.INVENTORY_ITEM_ID,
2435 mmt.COST_GROUP_ID,
2436 mmt.REVISION,
2437 mmt.SUBINVENTORY_CODE,
2438 mmt.LOCATOR_ID,
2439 sum(mmt.PRIMARY_QUANTITY)
2440 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2441 MTL_SYSTEM_ITEMS msi
2442 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2443 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2444 AND mmt.ORGANIZATION_ID=Org_id
2445 AND msi.LOT_CONTROL_CODE = 1
2446 AND mmt.transaction_action_id in (4,8)
2447 AND mmt.transaction_date >= p_from_date
2448 AND mmt.transaction_date <= p_to_date
2449 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2450 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2451
2452 CURSOR inv_adj_with_lot_qty IS
2453 SELECT trunc(mmt.TRANSACTION_DATE),
2454 mmt.ORGANIZATION_ID,
2455 mmt.INVENTORY_ITEM_ID,
2456 mmt.COST_GROUP_ID,
2457 mmt.REVISION,
2458 mtln.LOT_NUMBER,
2459 mmt.SUBINVENTORY_CODE,
2460 mmt.LOCATOR_ID,
2461 sum(mtln.PRIMARY_QUANTITY)
2462 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2463 MTL_SYSTEM_ITEMS msi,
2464 MTL_TRANSACTION_LOT_NUMBERS mtln
2465 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2466 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2467 AND mmt.transaction_id = mtln.transaction_id
2468 AND mmt.ORGANIZATION_ID=Org_id
2469 AND msi.LOT_CONTROL_CODE = 2
2470 AND mmt.transaction_action_id in (4,8)
2471 AND mmt.transaction_date >= p_from_date
2472 AND mmt.transaction_date <= p_to_date
2473 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2474 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2475
2476 CURSOR inv_adj_no_lot_val IS
2477 SELECT trunc(mmt.TRANSACTION_DATE),
2478 mmt.ORGANIZATION_ID,
2479 mmt.INVENTORY_ITEM_ID,
2480 mmt.COST_GROUP_ID,
2481 mmt.REVISION,
2482 mmt.SUBINVENTORY_CODE,
2483 mmt.LOCATOR_ID,
2484 sum(mta.BASE_TRANSACTION_VALUE)
2485 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2486 MTL_SYSTEM_ITEMS msi,
2487 MTL_TRANSACTION_ACCOUNTS mta
2488 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2489 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2490 AND mmt.ORGANIZATION_ID=Org_id
2491 AND msi.LOT_CONTROL_CODE = 1
2492 AND mmt.transaction_id = mta.transaction_id
2493 AND mta.accounting_line_type = 1
2494 AND mmt.transaction_action_id in (4,8)
2495 AND mmt.transaction_date >= p_from_date
2496 AND mmt.transaction_date <= p_to_date
2497 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2498 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2499
2500 CURSOR inv_adj_with_lot_val IS
2501 SELECT trunc(mmt.TRANSACTION_DATE),
2502 mmt.ORGANIZATION_ID,
2503 mmt.INVENTORY_ITEM_ID,
2504 mmt.COST_GROUP_ID,
2505 mmt.REVISION,
2506 mtln.LOT_NUMBER,
2507 mmt.SUBINVENTORY_CODE,
2508 mmt.LOCATOR_ID,
2509 sum(mta.BASE_TRANSACTION_VALUE)
2510 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2511 MTL_SYSTEM_ITEMS msi,
2512 MTL_TRANSACTION_LOT_NUMBERS mtln,
2513 MTL_TRANSACTION_ACCOUNTS mta
2514 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2515 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2516 AND mmt.ORGANIZATION_ID=Org_id
2517 AND mmt.transaction_id = mtln.transaction_id
2518 AND msi.LOT_CONTROL_CODE = 2
2519 AND mmt.transaction_id = mta.transaction_id
2520 AND mmt.transaction_action_id in (4,8)
2521 AND mta.accounting_line_type = 1
2522 AND mmt.transaction_date >= p_from_date
2523 AND mmt.transaction_date <= p_to_date
2524 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2525 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2526 BEGIN
2527
2528 edw_log.put_line('CALCadj p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
2529 edw_log.put_line('CALCadj p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
2530
2531 OPEN inv_adj_no_lot_qty;
2532
2533 LOOP
2534 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
2535 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
2536
2537
2538 FETCH inv_adj_no_lot_qty
2539 INTO l_trx_date,
2540 l_organization_id,
2541 l_item_id,
2542 l_cost_group_id,
2543 l_revision,
2544 l_subinventory,
2545 l_locator,
2546 total_qty;
2547
2548
2549 if(inv_adj_no_lot_qty%NOTFOUND) then
2550 CLOSE inv_adj_no_lot_qty;
2551 exit;
2552 end if;
2553
2554 Insert_update_push_log(
2555 p_trx_date => l_trx_date ,
2556 p_organization_id => l_organization_id,
2557 p_item_id => l_item_id,
2558 p_cost_group_id => l_cost_group_id,
2559 p_revision => l_revision,
2560 p_subinventory => l_subinventory,
2561 p_locator => l_locator,
2562 p_col_name1 => 'inv_adj_qty',
2563 p_total1 => total_qty,
2564 selector => 1,
2565 success => status);
2566
2567 if (status > 0) then
2568 Retcode := '2';
2569 return;
2570 end if;
2571
2572 END LOOP;
2573
2574
2575 OPEN inv_adj_with_lot_qty;
2576
2577 LOOP
2578 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
2579 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
2580
2581
2582 FETCH inv_adj_with_lot_qty
2583 INTO l_trx_date,
2584 l_organization_id,
2585 l_item_id,
2586 l_cost_group_id,
2587 l_revision,
2588 l_lot_number,
2589 l_subinventory,
2590 l_locator,
2591 total_qty;
2592
2593
2594 if(inv_adj_with_lot_qty%NOTFOUND) then
2595 CLOSE inv_adj_with_lot_qty;
2596 exit;
2597 end if;
2598
2599 Insert_update_push_log(
2600 p_trx_date => l_trx_date ,
2601 p_organization_id => l_organization_id,
2602 p_item_id => l_item_id,
2603 p_cost_group_id => l_cost_group_id,
2604 p_revision => l_revision,
2605 p_lot_number => l_lot_number,
2606 p_subinventory => l_subinventory,
2607 p_locator => l_locator,
2608 p_col_name1 => 'inv_adj_qty',
2609 p_total1 => total_qty,
2610 selector => 1,
2611 success => status);
2612
2613 if (status > 0) then
2614 Retcode := '2';
2615 return;
2616 end if;
2617
2618 END LOOP;
2619
2620
2621 OPEN inv_adj_no_lot_val;
2622
2623 LOOP
2624 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
2625 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
2626
2627
2628 FETCH inv_adj_no_lot_val
2629 INTO l_trx_date,
2630 l_organization_id,
2631 l_item_id,
2632 l_cost_group_id,
2633 l_revision,
2634 l_subinventory,
2635 l_locator,
2636 total_value;
2637
2638 if(inv_adj_no_lot_val%NOTFOUND) then
2639 CLOSE inv_adj_no_lot_val;
2640 exit;
2641 end if;
2642
2643 Insert_update_push_log(
2644 p_trx_date => l_trx_date ,
2645 p_organization_id => l_organization_id,
2646 p_item_id => l_item_id,
2647 p_cost_group_id => l_cost_group_id,
2648 p_revision => l_revision,
2649 p_subinventory => l_subinventory,
2650 p_locator => l_locator,
2651 p_col_name1 => 'inv_adj_val_b',
2652 p_total1 => total_value,
2653 selector => 1,
2654 success => status);
2655
2656 if (status > 0) then
2657 Retcode := '2';
2658 return;
2659 end if;
2660
2661 END LOOP;
2662
2663
2664 OPEN inv_adj_with_lot_val;
2665
2666 LOOP
2667
2668 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
2669 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
2670
2671
2672 FETCH inv_adj_with_lot_val
2673 INTO l_trx_date,
2674 l_organization_id,
2675 l_item_id,
2676 l_cost_group_id,
2677 l_revision,
2678 l_lot_number,
2679 l_subinventory,
2680 l_locator,
2681 total_value;
2682
2683 if(inv_adj_with_lot_val%NOTFOUND) then
2684 CLOSE inv_adj_with_lot_val;
2685 exit;
2686 end if;
2687
2688 Insert_update_push_log(
2689 p_trx_date => l_trx_date ,
2690 p_organization_id => l_organization_id,
2691 p_item_id => l_item_id,
2692 p_cost_group_id => l_cost_group_id,
2693 p_revision => l_revision,
2694 p_lot_number => l_lot_number,
2695 p_subinventory => l_subinventory,
2696 p_locator => l_locator,
2697 p_col_name1 => 'inv_adj_val_b',
2698 p_total1 => total_value,
2699 selector => 1,
2700 success => status);
2701
2702 if (status > 0) then
2703 Retcode := '2';
2704 return;
2705 end if;
2706
2707 END LOOP;
2708
2709 EXCEPTION
2710 WHEN OTHERS THEN
2711 edw_log.put_line('Error in calc_inv_adjustment');
2712 Retcode := '2';
2713
2714 end calc_inv_adjustment;
2715
2716
2717
2718 /*}{----------------------------------------------
2719 PROCEDURE CALC_TOTAL_ISSUE
2720 ----------------------------------------------*/
2721
2722 Procedure calc_total_issue(Errbuf out nocopy Varchar2,
2723 Retcode out nocopy Varchar2,
2724 p_from_date IN Date,
2725 p_to_date IN Date,
2726 Org_id IN Number) IS
2727 l_trx_date DATE;
2728 l_organization_id NUMBER;
2729 l_item_id NUMBER;
2730 l_cost_group_id NUMBER;
2731 l_revision VARCHAR2(3);
2732 l_lot_number VARCHAR2(30);
2733 l_subinventory VARCHAR2(10);
2734 l_locator NUMBER;
2735 total_value NUMBER;
2736 total_qty NUMBER;
2737 trx_type NUMBER;
2738 status NUMBER;
2739
2740
2741 -- ltong 01/20/2003. Filtered out consigned inventory.
2742 CURSOR total_issue_no_lot_qty IS
2743 SELECT trunc(mmt.TRANSACTION_DATE),
2744 mmt.ORGANIZATION_ID,
2745 mmt.INVENTORY_ITEM_ID,
2746 mmt.COST_GROUP_ID,
2747 mmt.REVISION,
2748 mmt.SUBINVENTORY_CODE,
2749 mmt.LOCATOR_ID,
2750 sum(mmt.PRIMARY_QUANTITY)
2751 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2752 MTL_SYSTEM_ITEMS msi
2753 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2754 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2755 AND mmt.ORGANIZATION_ID=Org_id
2756 AND msi.LOT_CONTROL_CODE = 1
2757 AND ( (mmt.transaction_action_id in (1,2,3,21)
2758 AND mmt.primary_quantity < 0
2759 AND mmt.transaction_source_type_id <> 1)
2760 OR (mmt.transaction_action_id = 27
2761 AND mmt.transaction_source_type_id in (5,12)) )
2762 AND mmt.transaction_date >= p_from_date
2763 AND mmt.transaction_date <= p_to_date
2764 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
2765 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
2766 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2767 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2768
2769
2770 -- ltong 01/20/2003. Filtered out consigned inventory.
2771 CURSOR total_issue_with_lot_qty IS
2772 SELECT trunc(mmt.TRANSACTION_DATE),
2773 mmt.ORGANIZATION_ID,
2774 mmt.INVENTORY_ITEM_ID,
2775 mmt.COST_GROUP_ID,
2776 mmt.REVISION,
2777 mtln.LOT_NUMBER,
2778 mmt.SUBINVENTORY_CODE,
2779 mmt.LOCATOR_ID,
2780 sum(mtln.PRIMARY_QUANTITY)
2781 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2782 MTL_SYSTEM_ITEMS msi,
2783 MTL_TRANSACTION_LOT_NUMBERS mtln
2784 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2785 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2786 AND mmt.transaction_id = mtln.transaction_id
2787 AND mmt.ORGANIZATION_ID=Org_id
2788 AND msi.LOT_CONTROL_CODE = 2
2789 AND ( (mmt.transaction_action_id in (1,2,3,21)
2790 AND mmt.primary_quantity < 0
2791 AND mmt.transaction_source_type_id <> 1)
2792 OR (mmt.transaction_action_id = 27
2793 AND mmt.transaction_source_type_id in (5,12)) )
2794 AND mmt.transaction_date >= p_from_date
2795 AND mmt.transaction_date <= p_to_date
2796 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
2797 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
2798 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2799 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2800
2801 -------------------------------------------------------------------------------
2802 -- Begin 11.5.10 changes
2803 -- Replace old Cursors for Item value with new cusors
2804 -------------------------------------------------------------------------------
2805 /*
2806 -- ltong 01/20/2003. Filtered out consigned inventory.
2807 CURSOR total_issue_no_lot_val IS
2808 SELECT trunc(mmt.TRANSACTION_DATE),
2809 mmt.ORGANIZATION_ID,
2810 mmt.INVENTORY_ITEM_ID,
2811 mmt.COST_GROUP_ID,
2812 mmt.REVISION,
2813 mmt.SUBINVENTORY_CODE,
2814 mmt.LOCATOR_ID,
2815 sum(mta.BASE_TRANSACTION_VALUE)
2816 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2817 MTL_SYSTEM_ITEMS msi,
2818 MTL_TRANSACTION_ACCOUNTS mta
2819 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2820 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2821 AND mmt.ORGANIZATION_ID=Org_id
2822 AND msi.LOT_CONTROL_CODE = 1
2823 AND mmt.transaction_id = mta.transaction_id
2824 AND mta.accounting_line_type = 1
2825 AND ( (mmt.transaction_action_id in (1,2,3,21)
2826 AND mmt.primary_quantity < 0
2827 AND mmt.transaction_source_type_id <> 1)
2828 OR (mmt.transaction_action_id = 27
2829 AND mmt.transaction_source_type_id in (5,12)) )
2830 AND mmt.transaction_date >= p_from_date
2831 AND mmt.transaction_date <= p_to_date
2832 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
2833 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
2834 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2835 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2836
2837
2838 -- ltong 01/20/2003. Filtered out consigned inventory.
2839 CURSOR total_issue_with_lot_val IS
2840 SELECT trunc(mmt.TRANSACTION_DATE),
2841 mmt.ORGANIZATION_ID,
2842 mmt.INVENTORY_ITEM_ID,
2843 mmt.COST_GROUP_ID,
2844 mmt.REVISION,
2845 mtln.LOT_NUMBER,
2846 mmt.SUBINVENTORY_CODE,
2847 mmt.LOCATOR_ID,
2848 sum(mta.BASE_TRANSACTION_VALUE)
2849 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2850 MTL_SYSTEM_ITEMS msi,
2851 MTL_TRANSACTION_LOT_NUMBERS mtln,
2852 MTL_TRANSACTION_ACCOUNTS mta
2853 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2854 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2855 AND mmt.ORGANIZATION_ID=Org_id
2856 AND mmt.transaction_id = mtln.transaction_id
2857 AND msi.LOT_CONTROL_CODE = 2
2858 AND mmt.transaction_id = mta.transaction_id
2859 AND ( (mmt.transaction_action_id in (1,2,3,21)
2860 AND mmt.primary_quantity < 0
2861 AND mmt.transaction_source_type_id <> 1)
2862 OR (mmt.transaction_action_id = 27
2863 AND mmt.transaction_source_type_id in (5,12)) )
2864 AND mta.accounting_line_type = 1
2865 AND mmt.transaction_date >= p_from_date
2866 AND mmt.transaction_date <= p_to_date
2867 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
2868 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
2869 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
2870 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
2871 */
2872
2873 CURSOR total_issue_no_lot_val IS
2874 SELECT TRANSACTION_DATE,
2875 ORGANIZATION_ID,
2876 INVENTORY_ITEM_ID,
2877 COST_GROUP_ID,
2878 REVISION,
2879 SUBINVENTORY_CODE,
2880 LOCATOR_ID,
2881 sum(BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2882 FROM
2883 (
2884 /* Regular Sales Transactions (no logical flow)*/
2885 SELECT trunc(mmt.TRANSACTION_DATE) TRANSACTION_DATE,
2886 mmt.ORGANIZATION_ID,
2887 mmt.INVENTORY_ITEM_ID,
2888 mmt.COST_GROUP_ID,
2889 mmt.REVISION,
2890 mmt.SUBINVENTORY_CODE,
2891 mmt.LOCATOR_ID,
2892 sum(mta.BASE_TRANSACTION_VALUE) BASE_TRANSACTION_VALUE
2893 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2894 MTL_SYSTEM_ITEMS msi,
2895 MTL_TRANSACTION_ACCOUNTS mta
2896 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2897 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2898 AND mmt.ORGANIZATION_ID=Org_id
2899 AND msi.LOT_CONTROL_CODE = 1
2900 AND mmt.TRANSACTION_ID = mta.TRANSACTION_ID
2901 AND mta.ACCOUNTING_LINE_TYPE = 1
2902 AND ((mmt.TRANSACTION_ACTION_ID IN (1,2,3,21)
2903 AND mmt.PRIMARY_QUANTITY < 0
2904 AND mmt.TRANSACTION_SOURCE_TYPE_ID <> 1)
2905 OR (mmt.TRANSACTION_ACTION_ID = 27
2906 AND mmt.TRANSACTION_SOURCE_TYPE_ID IN (5,12)))
2907 AND mmt.TRANSACTION_DATE >= p_from_date
2908 AND mmt.TRANSACTION_DATE <= p_to_date
2909 AND mmt.ORGANIZATION_ID = NVL(mmt.OWNING_ORGANIZATION_ID, mmt.ORGANIZATION_ID)
2910 AND NVL(mmt.OWNING_TP_TYPE,2) = 2
2911 GROUP BY trunc(mmt.TRANSACTION_DATE), mmt.ORGANIZATION_ID, mmt.INVENTORY_ITEM_ID, mmt.COST_GROUP_ID,
2912 mmt.REVISION, mmt.SUBINVENTORY_CODE, mmt.LOCATOR_ID
2913 UNION
2914 /* Sales Orders and RMA Receipts in Internal Drop Ship to Customer*/
2915 SELECT trunc(mmt1.TRANSACTION_DATE)TRANSACTION_DATE,
2916 mmt1.ORGANIZATION_ID,
2917 mmt1.INVENTORY_ITEM_ID,
2918 mmt1.COST_GROUP_ID,
2919 mmt1.REVISION,
2920 mmt1.SUBINVENTORY_CODE,
2921 mmt1.LOCATOR_ID,
2922 sum(mta.BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2923 FROM MTL_MATERIAL_TRANSACTIONS mmt1, -- Parent Physical Txns
2924 MTL_MATERIAL_TRANSACTIONS mmt2, -- Logical (Child) Txns
2925 MTL_SYSTEM_ITEMS msi,
2926 MTL_TRANSACTION_ACCOUNTS mta
2927 WHERE mmt1.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2928 AND mmt1.ORGANIZATION_ID=msi.ORGANIZATION_ID
2929 AND mmt1.ORGANIZATION_ID=Org_id
2930 AND msi.LOT_CONTROL_CODE = 1
2931 AND mmt1.TRANSACTION_ACTION_ID IN (1, 27)
2932 AND mmt1.TRANSACTION_SOURCE_TYPE_ID IN (2, 12)
2933 AND mmt1.TRANSACTION_DATE >= p_from_date
2934 AND mmt1.TRANSACTION_DATE <= p_to_date
2935 AND mmt2.TRANSACTION_ID = mta.TRANSACTION_ID
2936 AND mta.ACCOUNTING_LINE_TYPE = 1
2937 /* logical txn triggered by this parent txn*/
2938 AND mmt2.PARENT_TRANSACTION_ID = mmt1.TRANSACTION_ID
2939 AND mmt2.TRANSACTION_TYPE_ID IN (11, 14)
2940 AND mmt2.ORGANIZATION_ID = Org_id
2941 GROUP BY trunc(mmt1.TRANSACTION_DATE), mmt1.ORGANIZATION_ID, mmt1.INVENTORY_ITEM_ID, mmt1.COST_GROUP_ID,
2942 mmt1.REVISION, mmt1.SUBINVENTORY_CODE, mmt1.LOCATOR_ID
2943 )
2944 GROUP BY TRANSACTION_DATE, ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_GROUP_ID, REVISION, SUBINVENTORY_CODE, LOCATOR_ID;
2945
2946 CURSOR total_issue_with_lot_val IS
2947 SELECT TRANSACTION_DATE,
2948 ORGANIZATION_ID,
2949 INVENTORY_ITEM_ID,
2950 COST_GROUP_ID,
2951 REVISION,
2952 LOT_NUMBER,
2953 SUBINVENTORY_CODE,
2954 LOCATOR_ID,
2955 sum(BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2956 FROM
2957 (
2958 /* Regular Sales Transactions (no logical flow)*/
2959 SELECT trunc(mmt.TRANSACTION_DATE)TRANSACTION_DATE,
2960 mmt.ORGANIZATION_ID,
2961 mmt.INVENTORY_ITEM_ID,
2962 mmt.COST_GROUP_ID,
2963 mmt.REVISION,
2964 mtln.LOT_NUMBER,
2965 mmt.SUBINVENTORY_CODE,
2966 mmt.LOCATOR_ID,
2967 sum(mta.BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
2968 FROM MTL_MATERIAL_TRANSACTIONS mmt,
2969 MTL_SYSTEM_ITEMS msi,
2970 MTL_TRANSACTION_LOT_NUMBERS mtln,
2971 MTL_TRANSACTION_ACCOUNTS mta
2972 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
2973 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
2974 AND mmt.ORGANIZATION_ID=Org_id
2975 AND mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
2976 AND msi.LOT_CONTROL_CODE = 2
2977 AND mmt.TRANSACTION_ID = mta.TRANSACTION_ID
2978 AND ((mmt.TRANSACTION_ACTION_ID IN (1,2,3,21)
2979 AND mmt.PRIMARY_QUANTITY < 0
2980 AND mmt.TRANSACTION_SOURCE_TYPE_ID <> 1)
2981 OR (mmt.TRANSACTION_ACTION_ID = 27
2982 AND mmt.TRANSACTION_SOURCE_TYPE_ID IN (5,12)))
2983 AND mta.ACCOUNTING_LINE_TYPE = 1
2984 AND mmt.TRANSACTION_DATE >= p_from_date
2985 AND mmt.TRANSACTION_DATE <= p_to_date
2986 AND mmt.ORGANIZATION_ID = NVL(mmt.OWNING_ORGANIZATION_ID,mmt.ORGANIZATION_ID)
2987 AND NVL(mmt.OWNING_TP_TYPE,2) = 2
2988 GROUP BY trunc(mmt.TRANSACTION_DATE), mmt.ORGANIZATION_ID, mmt.INVENTORY_ITEM_ID, mmt.COST_GROUP_ID,
2989 mmt.REVISION, mtln.lot_number, mmt.SUBINVENTORY_CODE, mmt.LOCATOR_ID
2990 UNION
2991 /* Sales Orders and RMA Receipts in Internal Drop Ship to Customer*/
2992 SELECT trunc(mmt1.TRANSACTION_DATE)TRANSACTION_DATE,
2993 mmt1.ORGANIZATION_ID,
2994 Mmt1.INVENTORY_ITEM_ID,
2995 Mmt1.COST_GROUP_ID,
2996 Mmt1.REVISION,
2997 mtln.LOT_NUMBER,
2998 Mmt1.SUBINVENTORY_CODE,
2999 Mmt1.LOCATOR_ID,
3000 sum(mta.BASE_TRANSACTION_VALUE)BASE_TRANSACTION_VALUE
3001 FROM MTL_MATERIAL_TRANSACTIONS mmt1, --Parent Physical Txns
3002 MTL_MATERIAL_TRANSACTIONS mmt2, --Logical (Child) Txns
3003 MTL_SYSTEM_ITEMS msi,
3004 MTL_TRANSACTION_LOT_NUMBERS mtln,
3005 MTL_TRANSACTION_ACCOUNTS mta
3006 WHERE mmt1.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
3007 AND mmt1.ORGANIZATION_ID=msi.ORGANIZATION_ID
3008 AND mmt1.ORGANIZATION_ID=Org_id
3009 AND mmt1.TRANSACTION_ID = mtln.TRANSACTION_ID
3010 AND msi.LOT_CONTROL_CODE = 2
3011 AND mmt1.TRANSACTION_ACTION_ID IN (1,27)
3012 AND mmt1.TRANSACTION_SOURCE_TYPE_ID IN (2,12)
3013 AND mmt1.TRANSACTION_DATE >= p_from_date
3014 AND mmt1.TRANSACTION_DATE <= p_to_date
3015 AND mmt2.TRANSACTION_ID = mta.TRANSACTION_ID
3016 AND mta.ACCOUNTING_LINE_TYPE = 1
3017 /* logical txn triggered by this parent txn*/
3018 AND mmt2.PARENT_TRANSACTION_ID = mmt1.TRANSACTION_ID
3019 AND mmt2.TRANSACTION_TYPE_ID IN (11,14)
3020 AND mmt2.ORGANIZATION_ID=org_id
3021 GROUP BY trunc(mmt1.TRANSACTION_DATE), mmt1.ORGANIZATION_ID, mmt1.INVENTORY_ITEM_ID, mmt1.COST_GROUP_ID,
3022 mmt1.REVISION, mtln.LOT_NUMBER, mmt1.SUBINVENTORY_CODE, mmt1.LOCATOR_ID
3023 )
3024 GROUP BY TRANSACTION_DATE, ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_GROUP_ID,
3025 LOT_NUMBER, REVISION, SUBINVENTORY_CODE, LOCATOR_ID;
3026
3027 BEGIN
3028
3029 edw_log.put_line('CALCTOTISS p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
3030 edw_log.put_line('CALCTOTISS p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
3031
3032 OPEN total_issue_no_lot_qty;
3033
3034 LOOP
3035 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
3036 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
3037
3038
3039 FETCH total_issue_no_lot_qty
3040 INTO l_trx_date,
3041 l_organization_id,
3042 l_item_id,
3043 l_cost_group_id,
3044 l_revision,
3045 l_subinventory,
3046 l_locator,
3047 total_qty;
3048
3049
3050 if(total_issue_no_lot_qty%NOTFOUND) then
3051 CLOSE total_issue_no_lot_qty;
3052 exit;
3053 end if;
3054
3055 Insert_update_push_log(
3056 p_trx_date => l_trx_date ,
3057 p_organization_id => l_organization_id,
3058 p_item_id => l_item_id,
3059 p_cost_group_id => l_cost_group_id,
3060 p_revision => l_revision,
3061 p_subinventory => l_subinventory,
3062 p_locator => l_locator,
3063 p_col_name1 => 'tot_issues_qty',
3064 p_total1 => total_qty,
3065 selector => 1,
3066 success => status);
3067
3068 if (status > 0) then
3069 Retcode := '2';
3070 return;
3071 end if;
3072
3073 END LOOP;
3074
3075
3076 OPEN total_issue_with_lot_qty;
3077
3078 LOOP
3079 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
3080 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
3081
3082
3083 FETCH total_issue_with_lot_qty
3084 INTO l_trx_date,
3085 l_organization_id,
3086 l_item_id,
3087 l_cost_group_id,
3088 l_revision,
3089 l_lot_number,
3090 l_subinventory,
3091 l_locator,
3092 total_qty;
3093
3094
3095 if(total_issue_with_lot_qty%NOTFOUND) then
3096 CLOSE total_issue_with_lot_qty;
3097 exit;
3098 end if;
3099
3100 Insert_update_push_log(
3101 p_trx_date => l_trx_date ,
3102 p_organization_id => l_organization_id,
3103 p_item_id => l_item_id,
3104 p_cost_group_id => l_cost_group_id,
3105 p_revision => l_revision,
3106 p_lot_number => l_lot_number,
3107 p_subinventory => l_subinventory,
3108 p_locator => l_locator,
3109 p_col_name1 => 'tot_issues_qty',
3110 p_total1 => total_qty,
3111 selector => 1,
3112 success => status);
3113
3114
3115 if (status > 0) then
3116 Retcode := '2';
3117 return;
3118 end if;
3119
3120 END LOOP;
3121
3122
3123 OPEN total_issue_no_lot_val;
3124
3125 LOOP
3126 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
3127 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
3128
3129
3130 FETCH total_issue_no_lot_val
3131 INTO l_trx_date,
3132 l_organization_id,
3133 l_item_id,
3134 l_cost_group_id,
3135 l_revision,
3136 l_subinventory,
3137 l_locator,
3138 total_value;
3139
3140 if(total_issue_no_lot_val%NOTFOUND) then
3141 CLOSE total_issue_no_lot_val;
3142 exit;
3143 end if;
3144
3145 Insert_update_push_log(
3146 p_trx_date => l_trx_date ,
3147 p_organization_id => l_organization_id,
3148 p_item_id => l_item_id,
3149 p_cost_group_id => l_cost_group_id,
3150 p_revision => l_revision,
3151 p_subinventory => l_subinventory,
3152 p_locator => l_locator,
3153 p_col_name1 => 'tot_issues_val_b',
3154 p_total1 => total_value,
3155 selector => 1,
3156 success => status);
3157
3158 if (status > 0) then
3159 Retcode := '2';
3160 return;
3161 end if;
3162
3163 END LOOP;
3164
3165
3166 OPEN total_issue_with_lot_val;
3167
3168 LOOP
3169 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
3170 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
3171
3172
3173 FETCH total_issue_with_lot_val
3174 INTO l_trx_date,
3175 l_organization_id,
3176 l_item_id,
3177 l_cost_group_id,
3178 l_revision,
3179 l_lot_number,
3180 l_subinventory,
3181 l_locator,
3182 total_value;
3183
3184 if(total_issue_with_lot_val%NOTFOUND) then
3185 CLOSE total_issue_with_lot_val;
3186 exit;
3187 end if;
3188
3189 Insert_update_push_log(
3190 p_trx_date => l_trx_date ,
3191 p_organization_id => l_organization_id,
3192 p_item_id => l_item_id,
3193 p_cost_group_id => l_cost_group_id,
3194 p_revision => l_revision,
3195 p_lot_number => l_lot_number,
3196 p_subinventory => l_subinventory,
3197 p_locator => l_locator,
3198 p_col_name1 => 'tot_issues_val_b',
3199 p_total1 => total_value,
3200 selector => 1,
3201 success => status);
3202
3203 if (status > 0) then
3204 Retcode := '2';
3205 return;
3206 end if;
3207
3208 END LOOP;
3209
3210 EXCEPTION
3211 WHEN OTHERS THEN
3212 edw_log.put_line('Error in calc_total_issue');
3213 Retcode := '2';
3214
3215 end calc_total_issue;
3216
3217
3218 /*}{----------------------------------------------
3219 PROCEDURE CALC_TOTAL_RECEIPT
3220 ----------------------------------------------*/
3221
3222
3223 Procedure calc_total_receipt(Errbuf out nocopy Varchar2,
3224 Retcode out nocopy Varchar2,
3225 p_from_date IN Date,
3226 p_to_date IN Date,
3227 Org_id IN Number) IS
3228 l_trx_date DATE;
3229 l_organization_id NUMBER;
3230 l_item_id NUMBER;
3231 l_cost_group_id NUMBER;
3232 l_revision VARCHAR2(3);
3233 l_lot_number VARCHAR2(30);
3234 l_subinventory VARCHAR2(10);
3235 l_locator NUMBER;
3236 total_value NUMBER;
3237 total_qty NUMBER;
3238 trx_type NUMBER;
3239 status NUMBER;
3240
3241
3242 -- ltong 01/20/2003. Filtered out consigned inventory.
3243 CURSOR total_receipt_no_lot_qty IS
3244 SELECT trunc(mmt.TRANSACTION_DATE),
3245 mmt.ORGANIZATION_ID,
3246 mmt.INVENTORY_ITEM_ID,
3247 mmt.COST_GROUP_ID,
3248 mmt.REVISION,
3249 mmt.SUBINVENTORY_CODE,
3250 mmt.LOCATOR_ID,
3251 sum(mmt.PRIMARY_QUANTITY)
3252 FROM MTL_MATERIAL_TRANSACTIONS mmt,
3253 MTL_SYSTEM_ITEMS msi
3254 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
3255 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
3256 AND mmt.ORGANIZATION_ID=Org_id
3257 AND msi.LOT_CONTROL_CODE = 1
3258 AND ( (mmt.transaction_action_id in (2,3,12)
3259 AND mmt.primary_quantity >0 )
3260 OR ( mmt.transaction_action_id in (4,8))
3261 OR (mmt.transaction_action_id in (27,29)
3262 AND mmt.transaction_source_type_id in (3,6,13,1))
3263 OR (mmt.transaction_action_id in (31,32)
3264 AND mmt.transaction_source_type_id=5)
3265 OR (mmt.transaction_action_id =1 and mmt.transaction_source_type_id =1))
3266 AND mmt.transaction_date >= p_from_date
3267 AND mmt.transaction_date <= p_to_date
3268 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
3269 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
3270 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
3271 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
3272
3273 -- ltong 01/20/2003. Filtered out consigned inventory.
3274 CURSOR total_receipt_with_lot_qty IS
3275 SELECT trunc(mmt.TRANSACTION_DATE),
3276 mmt.ORGANIZATION_ID,
3277 mmt.INVENTORY_ITEM_ID,
3278 mmt.COST_GROUP_ID,
3279 mmt.REVISION,
3280 mtln.LOT_NUMBER,
3281 mmt.SUBINVENTORY_CODE,
3282 mmt.LOCATOR_ID,
3283 sum(mtln.PRIMARY_QUANTITY)
3284 FROM MTL_MATERIAL_TRANSACTIONS mmt,
3285 MTL_SYSTEM_ITEMS msi,
3286 MTL_TRANSACTION_LOT_NUMBERS mtln
3287 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
3288 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
3289 AND mmt.transaction_id = mtln.transaction_id
3290 AND mmt.ORGANIZATION_ID=Org_id
3291 AND msi.LOT_CONTROL_CODE = 2
3292 AND ( (mmt.transaction_action_id in (2,3,12)
3293 AND mmt.primary_quantity >0 )
3294 OR ( mmt.transaction_action_id in (4,8))
3295 OR (mmt.transaction_action_id in (27,29)
3296 AND mmt.transaction_source_type_id in (3,6,13,1))
3297 OR (mmt.transaction_action_id in (31,32)
3298 AND mmt.transaction_source_type_id=5)
3299 OR (mmt.transaction_action_id =1 and mmt.transaction_source_type_id =1))
3300 AND mmt.transaction_date >= p_from_date
3301 AND mmt.transaction_date <= p_to_date
3302 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
3303 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
3304 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
3305 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
3306
3307
3308 -- ltong 01/20/2003. Filtered out consigned inventory.
3309 CURSOR total_receipt_no_lot_val IS
3310 SELECT trunc(mmt.TRANSACTION_DATE),
3311 mmt.ORGANIZATION_ID,
3312 mmt.INVENTORY_ITEM_ID,
3313 mmt.COST_GROUP_ID,
3314 mmt.REVISION,
3315 mmt.SUBINVENTORY_CODE,
3316 mmt.LOCATOR_ID,
3317 sum(mta.BASE_TRANSACTION_VALUE)
3318 FROM MTL_MATERIAL_TRANSACTIONS mmt,
3319 MTL_SYSTEM_ITEMS msi,
3320 MTL_TRANSACTION_ACCOUNTS mta
3321 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
3322 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
3323 AND mmt.ORGANIZATION_ID=Org_id
3324 AND msi.LOT_CONTROL_CODE = 1
3325 AND mmt.transaction_id = mta.transaction_id
3326 AND mta.accounting_line_type = 1
3327 AND ( (mmt.transaction_action_id in (2,3,12)
3328 AND mmt.primary_quantity >0 )
3329 OR ( mmt.transaction_action_id in (4,8))
3330 OR (mmt.transaction_action_id in (27,29)
3331 AND mmt.transaction_source_type_id in (3,6,13,1))
3332 OR (mmt.transaction_action_id in (31,32)
3333 AND mmt.transaction_source_type_id=5)
3334 OR (mmt.transaction_action_id =1 and mmt.transaction_source_type_id =1))
3335 AND mmt.transaction_date >= p_from_date
3336 AND mmt.transaction_date <= p_to_date
3337 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
3338 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
3339 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
3340 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
3341
3342
3343 -- ltong 01/20/2003. Filtered out consigned inventory.
3344 CURSOR total_receipt_with_lot_val IS
3345 SELECT trunc(mmt.TRANSACTION_DATE),
3346 mmt.ORGANIZATION_ID,
3347 mmt.INVENTORY_ITEM_ID,
3348 mmt.COST_GROUP_ID,
3349 mmt.REVISION,
3350 mtln.LOT_NUMBER,
3351 mmt.SUBINVENTORY_CODE,
3352 mmt.LOCATOR_ID,
3353 sum(mta.BASE_TRANSACTION_VALUE)
3354 FROM MTL_MATERIAL_TRANSACTIONS mmt,
3355 MTL_SYSTEM_ITEMS msi,
3356 MTL_TRANSACTION_LOT_NUMBERS mtln,
3357 MTL_TRANSACTION_ACCOUNTS mta
3358 WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
3359 AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
3360 AND mmt.ORGANIZATION_ID=Org_id
3361 AND mmt.transaction_id = mtln.transaction_id
3362 AND msi.LOT_CONTROL_CODE = 2
3363 AND mmt.transaction_id = mta.transaction_id
3364 AND ( (mmt.transaction_action_id in (2,3,12)
3365 AND mmt.primary_quantity >0 )
3366 OR ( mmt.transaction_action_id in (4,8))
3367 OR (mmt.transaction_action_id in (27,29)
3368 AND mmt.transaction_source_type_id in (3,6,13,1))
3369 OR (mmt.transaction_action_id in (31,32)
3370 AND mmt.transaction_source_type_id=5)
3371 OR (mmt.transaction_action_id =1 and mmt.transaction_source_type_id =1))
3372 AND mta.accounting_line_type = 1
3373 AND mmt.transaction_date >= p_from_date
3374 AND mmt.transaction_date <= p_to_date
3375 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
3376 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
3377 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
3378 mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
3379 BEGIN
3380
3381 edw_log.put_line('CALCTOTRCT p_from_Date '||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
3382 edw_log.put_line('CALCTOTRCT p_to_Date '||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
3383 OPEN total_receipt_no_lot_qty;
3384
3385 LOOP
3386 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
3387 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
3388
3389
3390 FETCH total_receipt_no_lot_qty
3391 INTO l_trx_date,
3392 l_organization_id,
3393 l_item_id,
3394 l_cost_group_id,
3395 l_revision,
3396 l_subinventory,
3397 l_locator,
3398 total_qty;
3399
3400
3401 if(total_receipt_no_lot_qty%NOTFOUND) then
3402 CLOSE total_receipt_no_lot_qty;
3403 exit;
3404 end if;
3405
3406 Insert_update_push_log(
3407 p_trx_date => l_trx_date ,
3408 p_organization_id => l_organization_id,
3409 p_item_id => l_item_id,
3410 p_cost_group_id => l_cost_group_id,
3411 p_revision => l_revision,
3412 p_subinventory => l_subinventory,
3413 p_locator => l_locator,
3414 p_col_name1 => 'total_rec_qty',
3415 p_total1 => total_qty,
3416 selector => 1,
3417 success => status);
3418
3419 if (status > 0) then
3420 Retcode := '2';
3421 return;
3422 end if;
3423
3424 END LOOP;
3425
3426
3427 OPEN total_receipt_with_lot_qty;
3428
3429 LOOP
3430 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
3431 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
3432
3433
3434 FETCH total_receipt_with_lot_qty
3435 INTO l_trx_date,
3436 l_organization_id,
3437 l_item_id,
3438 l_cost_group_id,
3439 l_revision,
3440 l_lot_number,
3441 l_subinventory,
3442 l_locator,
3443 total_qty;
3444
3445
3446 if(total_receipt_with_lot_qty%NOTFOUND) then
3447 CLOSE total_receipt_with_lot_qty;
3448 exit;
3449 end if;
3450
3451 Insert_update_push_log(
3452 p_trx_date => l_trx_date ,
3453 p_organization_id => l_organization_id,
3454 p_item_id => l_item_id,
3455 p_cost_group_id => l_cost_group_id,
3456 p_revision => l_revision,
3457 p_lot_number => l_lot_number,
3458 p_subinventory => l_subinventory,
3459 p_locator => l_locator,
3460 p_col_name1 => 'total_rec_qty',
3461 p_total1 => total_qty,
3462 selector => 1,
3463 success => status);
3464
3465 if (status > 0) then
3466 Retcode := '2';
3467 return;
3468 end if;
3469
3470 END LOOP;
3471
3472
3473 OPEN total_receipt_no_lot_val;
3474
3475 LOOP
3476 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
3477 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
3478
3479
3480 FETCH total_receipt_no_lot_val
3481 INTO l_trx_date,
3482 l_organization_id,
3483 l_item_id,
3484 l_cost_group_id,
3485 l_revision,
3486 l_subinventory,
3487 l_locator,
3488 total_value;
3489
3490 if(total_receipt_no_lot_val%NOTFOUND) then
3491 CLOSE total_receipt_no_lot_val;
3492 exit;
3493 end if;
3494
3495 Insert_update_push_log(
3496 p_trx_date => l_trx_date ,
3497 p_organization_id => l_organization_id,
3498 p_item_id => l_item_id,
3499 p_cost_group_id => l_cost_group_id,
3500 p_revision => l_revision,
3501 p_subinventory => l_subinventory,
3502 p_locator => l_locator,
3503 p_col_name1 => 'total_rec_val_b',
3504 p_total1 => total_value,
3505 selector => 1,
3506 success => status);
3507
3508 if (status > 0) then
3509 Retcode := '2';
3510 return;
3511 end if;
3512
3513 END LOOP;
3514
3515
3516 OPEN total_receipt_with_lot_val;
3517
3518 LOOP
3519 Initialize(l_trx_date,l_organization_id,l_item_id,l_cost_group_id,l_revision,
3520 l_lot_number,l_subinventory,l_locator,total_qty,total_value);
3521
3522
3523 FETCH total_receipt_with_lot_val
3524 INTO l_trx_date,
3525 l_organization_id,
3526 l_item_id,
3527 l_cost_group_id,
3528 l_revision,
3529 l_lot_number,
3530 l_subinventory,
3531 l_locator,
3532 total_value;
3533
3534 if(total_receipt_with_lot_val%NOTFOUND) then
3535 CLOSE total_receipt_with_lot_val;
3536 exit;
3537 end if;
3538
3539 Insert_update_push_log(
3540 p_trx_date => l_trx_date ,
3541 p_organization_id => l_organization_id,
3542 p_item_id => l_item_id,
3543 p_cost_group_id => l_cost_group_id,
3544 p_revision => l_revision,
3545 p_lot_number => l_lot_number,
3546 p_subinventory => l_subinventory,
3547 p_locator => l_locator,
3548 p_col_name1 => 'total_rec_val_b',
3549 p_total1 => total_value,
3550 selector => 1,
3551 success => status);
3552
3553 if (status > 0) then
3554 Retcode := '2';
3555 return;
3556 end if;
3557
3558 END LOOP;
3559
3560 EXCEPTION
3561 WHEN OTHERS THEN
3562 edw_log.put_line('Error in calc_total_receipt');
3563 Retcode := '2';
3564
3565 end calc_total_receipt;
3566
3567
3568 /*}----------------------------------------------
3569 PROCEDURE Insert_update_push_log
3570 ----------------------------------------------*/
3571
3572 PROCEDURE Insert_update_push_log(
3573 p_trx_date IN Date,
3574 p_organization_id IN Number,
3575 p_item_id IN Number default NULL,
3576 p_cost_group_id IN Number default NULL,
3577 p_revision IN Varchar2 default NULL,
3578 p_lot_number IN Varchar2 default NULL,
3579 p_subinventory IN Varchar2 default NULL,
3580 p_locator IN Number default NULL,
3581 p_item_status IN Varchar2 default NULL,
3582 p_item_type IN Varchar2 default NULL,
3583 p_base_uom IN Varchar2 default NULL,
3584 p_col_name1 IN Varchar2 default NULL,
3585 p_total1 IN Number default NULL,
3586 p_col_name2 IN Varchar2 default NULL,
3587 p_total2 IN Number default NULL,
3588 p_col_name3 IN Varchar2 default NULL,
3589 p_total3 IN Number default NULL,
3590 p_col_name4 IN Varchar2 default NULL,
3591 p_total4 IN Number default NULL,
3592 p_col_name5 IN Varchar2 default NULL,
3593 p_total5 IN Number default NULL,
3594 p_col_name6 IN Varchar2 default NULL,
3595 p_total6 IN Number default NULL,
3596 selector IN Number default NULL,
3597 success OUT nocopy Number)
3598 IS
3599
3600 l_pk varchar2(100):=null;
3601 l_query varchar2(2000):=null;
3602 l_row_exists number:=0;
3603 l_physical_location number:= 0;
3604 l_locator number;
3605 pjm_org number:= 2;
3606
3607
3608
3609 CURSOR row_exists IS
3610 SELECT 1
3611 FROM opi_ids_push_log
3612 WHERE IDS_KEY=l_pk;
3613
3614 CURSOR change_location IS
3615 SELECT physical_location_id
3616 FROM mtl_item_locations
3617 WHERE organization_id = p_organization_id
3618 AND inventory_location_id <> physical_location_id
3619 AND inventory_location_id = p_locator;
3620
3621 BEGIN
3622
3623 l_locator := p_locator;
3624
3625 /* ---------------------------------------------------------------------------------------------------
3626 Fixes bug 1675273. For PJM controlled orgs, MMT/MOQ stores the inv_location_id for the project
3627 Mtl_item_locations
3628 inventory_location_id physical_location_id Projectid
3629 1 2 P1
3630 2 2
3631 3 2 P2
3632 4 2 P2 Task 2
3633
3634 But Inv Locator dimension only stores the Real physical locators and so it only gets inventory_location_id=
3635 physical_location_id = 2 and doesn't have 1,3 and 4. So while collecting IDS, mmt/moq are grouped by
3636 inventory_location_id (1,2,3,4) but 1,3,4 should be converted into 2. THis is due to the fact that IDS
3637 doesn't support (nor is it required) Project info. To track keeping the original locator_id as in
3638 mmt/moq in the ids_key but changing the locator_fk column to point to physical locator
3639
3640 --rjin
3641 we store the p_locator (the physical locator info for non-pjm controlled org or the project
3642 locator info for pjm-controlled org ) in project_locator_id column in push_log.
3643 Because later in calc_prd_start_end, we need to recover the project locator id info to
3644 construct the ids_key.
3645 -----------------------------------------------------------------------------------------------------*/
3646
3647 SELECT nvl(PROJECT_REFERENCE_ENABLED,2) into pjm_org
3648 FROM mtl_parameters
3649 WHERE organization_id = p_organization_id;
3650
3651
3652 if ( pjm_org = 1 AND p_locator > 0 ) then
3653 OPEN change_location;
3654 FETCH change_location INTO l_physical_location;
3655
3656 if (change_location%FOUND) then
3657 l_locator := l_physical_location;
3658 end if;
3659 CLOSE change_location;
3660 end if;
3661
3662 l_pk := p_trx_date||'-'||p_item_id||'-'||p_organization_id||'-'||p_cost_group_id||'-'
3663 ||p_revision||'-'||p_lot_number||'-'||p_subinventory||'-'||p_locator;
3664
3665 --dbms_output.put_line('l_pk is ' || l_pk);
3666
3667 /* edw_log.put_line('IU_push_log: IDSKEY= '||l_pk); */
3668
3669
3670 OPEN row_exists ;
3671
3672 FETCH row_exists
3673 INTO l_row_exists;
3674
3675 IF row_exists%rowcount > 0 THEN
3676 --dbms_output.put_line(' > 0');
3677 IF(selector = 1) then
3678 l_query := 'UPDATE opi_ids_push_log SET push_flag = 1,' || p_col_name1
3679 || ' = ' || 'nvl(:p_total1,0) WHERE IDS_KEY = :l_pk ';
3680
3681 execute immediate l_query using p_total1, l_pk;
3682 ELSE
3683 l_query := 'UPDATE opi_ids_push_log SET push_flag = 1, '
3684 || p_col_name1 || ' = nvl(:p_total1,0),' || p_col_name2 ||
3685 ' = nvl(:p_total2,0), ' || p_col_name3 || ' = nvl( :p_total3,0), '
3686 || p_col_name4 || ' = nvl(:p_total4,0), '|| p_col_name5 ||
3687 ' = nvl(:p_total5,0), '|| p_col_name6 || ' = nvl(:p_total6,0) '
3688 || ' WHERE IDS_KEY = :l_pk ';
3689
3690 execute immediate l_query using p_total1, p_total2, p_total3, p_total4, p_total5, p_total6, l_pk;
3691 END IF;
3692 ELSE
3693 --dbms_output.put_line('<0, not exist');
3694
3695 l_query := 'INSERT INTO opi_ids_push_log(IDS_KEY,trx_date,organization_id,Push_flag';
3696
3697 l_query := l_query || ', cost_group_id , inventory_item_id, revision, lot_number';
3698 l_query := l_query||', subinventory_code, locator_id, project_locator_id, item_status';
3699 l_query := l_query||', item_type, base_uom';
3700
3701 IF(selector = 1) then
3702 l_query := l_query || ' ,' || p_col_name1;
3703 ELSE
3704 l_query := l_query || ' ,' || p_col_name1 || ' ,' || p_col_name2 || ' ,'||
3705 p_col_name3 || ' ,' || p_col_name4 || ' ,' || p_col_name5 || ' ,' || p_col_name6;
3706 END IF;
3707
3708 l_query := l_query || ') VALUES ( :l_pk ,:p_trx_date, :p_organization_id, 1';
3709 l_query := l_query||', :p_cost_group_id, :p_item_id, :p_revision,:p_lot_number';
3710 l_query := l_query||', :p_subinventory, :l_locator, :p_locator, :p_item_status';
3711 l_query := l_query||', :p_item_type, :p_base_uom ';
3712
3713
3714 IF(selector = 1) then
3715 l_query := l_query || ', Nvl(:p_total1,0) )';
3716
3717 execute immediate l_query using l_pk ,p_trx_date, p_organization_id, p_cost_group_id, p_item_id, p_revision,p_lot_number , p_subinventory, l_locator, p_locator, p_item_status, p_item_type, p_base_uom, p_total1;
3718
3719 ELSE
3720 l_query := l_query || ', Nvl(:p_total1,0), Nvl(:p_total2,0), Nvl(:p_total3,0),';
3721 l_query := l_query || 'Nvl(:p_total4,0), Nvl(:p_total5,0), Nvl(:p_total6,0) ) ';
3722
3723 execute immediate l_query using l_pk ,p_trx_date, p_organization_id, p_cost_group_id,
3724 p_item_id, p_revision,p_lot_number , p_subinventory, l_locator, p_locator, p_item_status,
3725 p_item_type, p_base_uom, p_total1, p_total2, p_total3, p_total4, p_total5, p_total6;
3726
3727 END IF;
3728 END IF;
3729
3730 success:=0;
3731 CLOSE row_exists ;
3732
3733 EXCEPTION
3734 WHEN OTHERS THEN
3735 edw_log.put_line('Error in Insert_update_push_log prodedure ');
3736 edw_log.put_line('query errored '||l_query);
3737 success:=1;
3738 END Insert_update_push_log;
3739
3740 /*}----------------------------------------------
3741 PROCEDURE Initialize
3742 ----------------------------------------------*/
3743
3744 PROCEDURE Initialize(
3745 p_trx_date OUT nocopy Date,
3746 p_organization_id OUT nocopy Number,
3747 p_item_id OUT nocopy Number,
3748 p_cost_group_id OUT nocopy Number,
3749 p_revision OUT nocopy Varchar2,
3750 p_lot_number OUT nocopy Varchar2,
3751 p_subinventory OUT nocopy Varchar2,
3752 p_locator OUT nocopy Number,
3753 total_qty OUT nocopy Number,
3754 total_value OUT nocopy Number) IS
3755 BEGIN
3756 p_trx_date := NULL;
3757 p_organization_id := 0;
3758 p_item_id := 0;
3759 p_cost_group_id := NULL;
3760 p_revision := NULL;
3761 p_lot_number := NULL;
3762 p_subinventory := NULL;
3763 p_locator := 0;
3764 total_qty := 0;
3765 total_value := 0;
3766
3767 EXCEPTION
3768 WHEN OTHERS THEN
3769 edw_log.put_line('Error in Initialize');
3770
3771 end Initialize;
3772
3773 End OPIMPXIN;