[Home] [Help]
PACKAGE BODY: APPS.INVTVTUS
Source
1 PACKAGE BODY INVTVTUS as
2 /* $Header: INVTVTUB.pls 120.1 2006/03/08 03:57:02 rkatoori noship $ */
3
4 procedure item_only_summaries (
5 sessionid number,
6 orgid mtl_parameters.organization_id%TYPE,
7 orgloctype number,
8 invid mtl_system_items.inventory_item_id%TYPE,
9 rev mtl_item_revisions.revision%TYPE,
10 uom mtl_system_items.primary_uom_code%TYPE,
11 puom mtl_system_items.primary_uom_code%TYPE,
12 sdate mtl_material_transactions.transaction_date%TYPE,
13 edate mtl_material_transactions.transaction_date%TYPE,
14 cg_id mtl_secondary_inventories.default_cost_group_id%TYPE)
15 is
16 cratio number := 1;
17 prc number := 1;
18 msgbuf varchar2(200);
19
20 begin
21 delete from mtl_summary_temp
22 where session_id = sessionid;
23
24 /*Bug4950410 : Added the following query to get the currency precision to round the inventory value.
25 Also rounding the net_val and abs_val based on the currency precision of the organization. */
26 select fc.precision into prc
27 from org_organization_definitions ood,
28 gl_sets_of_books sob,
29 fnd_currencies fc
30 where ood.organization_id = orgid
31 and ood.set_of_books_id = sob.set_of_books_id
32 and fc.currency_code = sob.currency_code;
33
34 if (puom <> uom) then
35 cratio := inv_convert.inv_um_convert(invid,5,null,puom,uom,null,null);
36 end if;
37
38 /* Source Type and Txn Type Summary */
39 /* We are also storing transaction action here
40 for future use */
41 /*Bug2712883 : The insert statements having cg_id in their where clause is modified so as to include NVL's on both sides */
42 insert into mtl_summary_temp
43 (session_id,
44 summary_type,
45 transaction_source_type_name,
46 transaction_type_name,
47 transaction_action_name,
48 net_qty,
49 net_val,
50 num_txns,
51 abs_qty,
52 abs_val)
53 select
54 sessionid,
55 'SRCTYPE_TXNTYPE_SUMMARY',
56 mtst.transaction_source_type_name,
57 mtt.transaction_type_name,
58 ml.meaning,
59 cratio*sum(mmt.primary_quantity),
60 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
61 sum(1),
62 cratio*sum(abs(mmt.primary_quantity)),
63 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
64 from mtl_material_transactions mmt,
65 mfg_lookups ml,
66 mtl_txn_source_types mtst,
67 mtl_transaction_types mtt
68 where mmt.inventory_item_id = invid
69 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
70 and mmt.organization_id = orgid
71 and mmt.transaction_date >= NVL(sdate,mmt.transaction_date - 1)
72 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
73 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
74 and mtst.transaction_source_type_id = mmt.transaction_source_type_id
75 and mtt.transaction_type_id = mmt.transaction_type_id
76 and ml.lookup_code = mmt.transaction_action_id + 0
77 and ml.lookup_type = 'MTL_TRANSACTION_ACTION'
78 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
79 group by mtst.transaction_source_type_name,
80 mtt.transaction_type_name, ml.meaning;
81
82 /* Source Type Summary */
83 insert into mtl_summary_temp
84 (session_id,
85 summary_type,
86 transaction_source_type_name,
87 net_qty,
88 net_val,
89 num_txns,
90 abs_qty,
91 abs_val)
92 select
93 sessionid,
94 'SOURCE_TYPES_SUMMARY',
95 mst.transaction_source_type_name,
96 sum(mst.net_qty),
97 sum(mst.net_val),
98 sum(mst.num_txns),
99 sum(mst.abs_qty),
100 sum(mst.abs_val)
101 from mtl_summary_temp mst
102 where mst.summary_type = 'SRCTYPE_TXNTYPE_SUMMARY'
103 and session_id = sessionid
104 group by mst.transaction_source_type_name;
105
106 /* Transaction type summary */
107 insert into mtl_summary_temp
108 (session_id,
109 summary_type,
110 transaction_type_name,
111 net_qty,
112 net_val,
113 num_txns,
114 abs_qty,
115 abs_val)
116 select
117 sessionid,
118 'TXN_TYPES_SUMMARY',
119 mst.transaction_type_name,
120 sum(mst.net_qty),
121 sum(mst.net_val),
122 sum(mst.num_txns),
123 sum(mst.abs_qty),
124 sum(mst.abs_val)
125 from mtl_summary_temp mst
126 where mst.summary_type = 'SRCTYPE_TXNTYPE_SUMMARY'
127 and session_id = sessionid
128 group by mst.transaction_type_name;
129
130 /* Action Summary */
131 insert into mtl_summary_temp
132 (session_id,
133 summary_type,
134 transaction_action_name,
135 net_qty,
136 net_val,
137 num_txns,
138 abs_qty,
139 abs_val)
140 select
141 sessionid,
142 'TXN_ACTION_SUMMARY',
143 mst.transaction_action_name,
144 sum(mst.net_qty),
145 sum(mst.net_val),
146 sum(mst.num_txns),
147 sum(mst.abs_qty),
148 sum(mst.abs_val)
149 from mtl_summary_temp mst
150 where mst.summary_type = 'SRCTYPE_TXNTYPE_SUMMARY'
151 and session_id = sessionid
152 group by mst.transaction_action_name;
153
154 /* Subinventory Summary */
155 if (orgloctype = 1) then
156 insert into mtl_summary_temp
157 (session_id,
158 summary_type,
159 subinventory,
160 net_qty,
161 net_val,
162 num_txns,
163 abs_qty,
164 abs_val)
165 select
166 sessionid,
167 'SUBINVENTORY_SUMMARY',
168 mmt.subinventory_code,
169 cratio*sum(mmt.primary_quantity),
170 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
171 sum(1),
172 cratio*sum(abs(mmt.primary_quantity)),
173 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
174 from mtl_material_transactions mmt
175 where mmt.inventory_item_id = invid
176 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
177 and mmt.organization_id = orgid
178 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
179 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
180 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
181 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
182 group by mmt.subinventory_code;
183 else
184 /* Subinventory - Locator Summary */
185 insert into mtl_summary_temp
186 (session_id,
187 summary_type,
188 subinventory,
189 locator_id,
190 net_qty,
191 net_val,
192 num_txns,
193 abs_qty,
194 abs_val)
195 select
196 sessionid,
197 'SUBINVENTORY_LOCATOR_SUMMARY',
198 mmt.subinventory_code,
199 mmt.locator_id,
200 cratio*sum(mmt.primary_quantity),
201 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
202 sum(1),
203 cratio*sum(abs(mmt.primary_quantity)),
204 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
205 from mtl_material_transactions mmt
206 where mmt.inventory_item_id = invid
207 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
208 and mmt.organization_id = orgid
209 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
210 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
211 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
212 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
213 group by mmt.subinventory_code, mmt.locator_id;
214
215 /* Subinventory Summary */
216 insert into mtl_summary_temp
217 (session_id,
218 summary_type,
219 subinventory,
220 net_qty,
221 net_val,
222 num_txns,
223 abs_qty,
224 abs_val)
225 select
226 sessionid,
227 'SUBINVENTORY_SUMMARY',
228 mst.subinventory,
229 sum(mst.net_qty),
230 sum(mst.net_val),
231 sum(mst.num_txns),
232 sum(mst.abs_qty),
233 sum(mst.abs_val)
234 from mtl_summary_temp mst
235 where mst.summary_type = 'SUBINVENTORY_LOCATOR_SUMMARY'
236 and session_id = sessionid
237 group by mst.subinventory;
238 end if;
239
240 /* subinventory cost group summary */
241 /* changes for zone/rearchitecture project */
242 /* ssia 06/02/00 */
243 insert into mtl_summary_temp
244 (session_id,
245 summary_type,
246 subinventory,
247 cost_group_id,
248 net_qty,
249 net_val,
250 num_txns,
251 abs_qty,
252 abs_val)
253 select
254 sessionid,
255 'SUB_COST_GROUP_SUMMARY',
256 mmt.subinventory_code,
257 mmt.cost_group_id,
258 cratio*sum(mmt.primary_quantity),
259 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
260 sum(1),
261 cratio*sum(abs(mmt.primary_quantity)),
262 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
263 from mtl_material_transactions mmt
264 where mmt.inventory_item_id = invid
265 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
266 and mmt.organization_id = orgid
267 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
268 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
269 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
270 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
271 group by mmt.subinventory_code, mmt.cost_group_id;
272
273 /* Cost_group_summary */
274 insert into mtl_summary_temp
275 (session_id,
276 summary_type,
277 cost_group_id,
278 net_qty,
279 net_val,
280 num_txns,
281 abs_qty,
282 abs_val)
283 select
284 sessionid,
285 'COST_GROUP_SUMMARY',
286 mst.cost_group_id,
287 sum(mst.net_qty),
288 sum(mst.net_val),
289 sum(mst.num_txns),
290 sum(mst.abs_qty),
291 sum(mst.abs_val)
292 from mtl_summary_temp mst
293 where mst.summary_type = 'SUB_COST_GROUP_SUMMARY'
294 and session_id = sessionid
295 group by mst.cost_group_id;
296
297 /* Totals Summary */
298 /* Total In */
299 insert into mtl_summary_temp
300 (session_id,
301 summary_type,
302 net_qty,
303 net_val,
304 num_txns,
305 locator_id) /* This is populated for ordering purposes */
306 select
307 sessionid,
308 'TOTALS',
309 nvl(cratio*sum(mmt.primary_quantity),0),
310 round(nvl(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),0),prc),
311 nvl(sum(1),0),
312 1
313 from mtl_material_transactions mmt
314 where mmt.inventory_item_id = invid
315 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
316 and mmt.organization_id = orgid
317 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
318 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
319 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
320 and mmt.primary_quantity > 0
321 and mmt.transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
322
323 /* Total Out */
324 insert into mtl_summary_temp
325 (session_id,
326 summary_type,
327 net_qty,
328 net_val,
329 num_txns,
330 locator_id) /* This is populated for ordering purposes */
331 select
332 sessionid,
333 'TOTALS',
334 nvl(abs(cratio*sum(mmt.primary_quantity)),0),
335 round(nvl(abs(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity)),0),prc),
336 nvl(sum(1),0),
337 2
338 from mtl_material_transactions mmt
339 where mmt.inventory_item_id = invid
340 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
341 and mmt.organization_id = orgid
342 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
343 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
344 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
345 and mmt.primary_quantity < 0
346 and mmt.transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
347
348 /* Total Net */
349 insert into mtl_summary_temp
350 (session_id,
351 summary_type,
352 net_qty,
353 net_val,
354 num_txns,
355 locator_id) /* This is populated for ordering purposes */
356 select
357 sessionid,
358 'TOTALS',
359 sum(mst.net_qty),
360 sum(mst.net_val),
361 null,
362 3
363 from mtl_summary_temp mst
364 where mst.summary_type='TXN_ACTION_SUMMARY'
365 and session_id = sessionid;
366 /* TXN_ACTION_SUMMARY is most likely to be a smaller
367 set of rows */
368 exception
369 when others then
370 raise_application_error(-20001, sqlerrm||'---'||msgbuf);
371 end item_only_summaries;
372
373 procedure sub_only_summaries (
374 sessionid number,
375 orgid mtl_parameters.organization_id%TYPE,
376 sub mtl_secondary_inventories.secondary_inventory_name%TYPE,
377 locid mtl_item_locations.inventory_location_id%TYPE,
378 catsetid mtl_category_sets.category_set_id%TYPE,
379 catid mtl_categories.category_id%TYPE,
380 sdate mtl_material_transactions.transaction_date%TYPE,
381 edate mtl_material_transactions.transaction_date%TYPE,
382 cg_id mtl_secondary_inventories.default_cost_group_id%TYPE)
383 is
384 msgbuf varchar2(200);
385 prc number := 1;
386 cratio number := 1;
387 begin
388 delete from mtl_summary_temp where session_id = sessionid;
389
390 /*Bug4950410 : Added the following query to get the currency precision to round the inventory value.
391 Also rounding the net_val and abs_val based on the currency precision of the organization. */
392
393 select fc.precision into prc
394 from org_organization_definitions ood,
395 gl_sets_of_books sob,
396 fnd_currencies fc
397 where ood.organization_id = orgid
398 and ood.set_of_books_id = sob.set_of_books_id
399 and fc.currency_code = sob.currency_code;
400
401 -- Bug 3614951 changing the below sql to execute conditionally.
402 -- Begin changes
403 IF catsetid IS NULL AND catid IS NULL THEN
404 /* Item, Source Type, Txn Type summary */
405 /* We are inserting transaction action as well
406 for later summarization */
407 insert into mtl_summary_temp
408 (session_id,
409 summary_type,
410 inventory_item_id,
411 organization_id,
412 transaction_source_type_name,
413 transaction_type_name,
414 transaction_action_name,
415 net_qty,
416 net_val,
417 num_txns,
418 abs_qty,
419 abs_val)
420 select
421 sessionid,
422 'ITEM_SRCTYPE_TXNTYPE_SUMMARY',
426 mtt.transaction_type_name,
423 mmt.inventory_item_id,
424 min(mmt.organization_id),
425 mtst.transaction_source_type_name,
427 ml.meaning,
428 sum(mmt.primary_quantity),
429 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
430 sum(1),
431 sum(abs(mmt.primary_quantity)),
432 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
433 from mtl_material_transactions mmt,
434 mtl_txn_source_types mtst,
435 mfg_lookups ml,
436 mtl_transaction_types mtt
437 where mmt.organization_id = orgid
438 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
439 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
440 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
441 and mmt.subinventory_code = sub
442 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
443 and mtst.transaction_source_type_id = mmt.transaction_source_type_id
444 and mtt.transaction_type_id = mmt.transaction_type_id
445 and ml.lookup_type = 'MTL_TRANSACTION_ACTION'
446 and ml.lookup_code = mmt.transaction_action_id + 0
447 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
448 group by mmt.inventory_item_id,
449 mtst.transaction_source_type_name,
450 mtt.transaction_type_name,
451 ml.meaning;
452 ELSE
453 insert into mtl_summary_temp
454 (session_id,
455 summary_type,
456 inventory_item_id,
457 organization_id,
458 transaction_source_type_name,
459 transaction_type_name,
460 transaction_action_name,
461 net_qty,
462 net_val,
463 num_txns,
464 abs_qty,
465 abs_val)
466 select
467 sessionid,
468 'ITEM_SRCTYPE_TXNTYPE_SUMMARY',
469 mmt.inventory_item_id,
470 min(mmt.organization_id),
471 mtst.transaction_source_type_name,
472 mtt.transaction_type_name,
473 ml.meaning,
474 sum(mmt.primary_quantity),
475 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
476 sum(1),
477 sum(abs(mmt.primary_quantity)),
478 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
479 from mtl_material_transactions mmt,
480 mtl_txn_source_types mtst,
481 mfg_lookups ml,
482 mtl_transaction_types mtt
483 where mmt.organization_id = orgid
484 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
485 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
486 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
487 and mmt.subinventory_code = sub
488 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
489 and exists ( select 'X'
490 from mtl_item_categories mic
491 where mic.category_set_id = nvl(catsetid, mic.category_set_id)
492 and mic.category_id = nvl(catid, mic.category_id)
493 and mic.inventory_item_id = mmt.inventory_item_id
494 and mic.organization_id = orgid)
495 and mtst.transaction_source_type_id = mmt.transaction_source_type_id
496 and mtt.transaction_type_id = mmt.transaction_type_id
497 and ml.lookup_type = 'MTL_TRANSACTION_ACTION'
498 and ml.lookup_code = mmt.transaction_action_id + 0
499 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
500 group by mmt.inventory_item_id,
501 mtst.transaction_source_type_name,
502 mtt.transaction_type_name,
503 ml.meaning;
504 END IF;
505 -- End changes bug 3614951
506 /* Item summmary */
507 insert into mtl_summary_temp
508 (session_id,
509 summary_type,
510 inventory_item_id,
511 organization_id,
512 net_qty,
513 net_val,
514 num_txns,
515 abs_qty,
516 abs_val)
517 select
518 sessionid,
519 'ITEM_SUMMARY',
520 mst.inventory_item_id,
521 min(mst.organization_id),
522 sum(mst.net_qty),
523 sum(mst.net_val),
524 sum(mst.num_txns),
525 sum(mst.abs_qty),
526 sum(mst.abs_val)
527 from mtl_summary_temp mst
528 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
529 and session_id = sessionid
530 group by mst.inventory_item_id;
531
532 /* Item, Source Type summary */
533 insert into mtl_summary_temp
534 (session_id,
535 summary_type,
536 inventory_item_id,
537 organization_id,
538 transaction_source_type_name,
539 net_qty,
540 net_val,
541 num_txns,
542 abs_qty,
543 abs_val)
544 select
545 sessionid,
546 'ITEM_SRCTYPE_SUMMARY',
547 mst.inventory_item_id,
548 min(mst.organization_id),
549 mst.transaction_source_type_name,
550 sum(mst.net_qty),
551 sum(mst.net_val),
552 sum(mst.num_txns),
553 sum(mst.abs_qty),
554 sum(mst.abs_val)
555 from mtl_summary_temp mst
556 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
557 and session_id = sessionid
558 group by mst.inventory_item_id, mst.transaction_source_type_name;
559
560 /* Item, Txn Type summary */
561 insert into mtl_summary_temp
562 (session_id,
566 transaction_type_name,
563 summary_type,
564 inventory_item_id,
565 organization_id,
567 net_qty,
568 net_val,
569 num_txns,
570 abs_qty,
571 abs_val)
572 select
573 sessionid,
574 'ITEM_TXN_TYPES_SUMMARY',
575 mst.inventory_item_id,
576 min(mst.organization_id),
577 mst.transaction_type_name,
578 sum(mst.net_qty),
579 sum(mst.net_val),
580 sum(mst.num_txns),
581 sum(mst.abs_qty),
582 sum(mst.abs_val)
583 from mtl_summary_temp mst
584 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
585 and session_id = sessionid
586 group by mst.inventory_item_id, mst.transaction_type_name;
587
588 /* Item, Txn action summary */
589 insert into mtl_summary_temp
590 (session_id,
591 summary_type,
592 inventory_item_id,
593 organization_id,
594 transaction_action_name,
595 net_qty,
596 net_val,
597 num_txns,
598 abs_qty,
599 abs_val)
600 select
601 sessionid,
602 'ITEM_TXN_ACTION_SUMMARY',
603 mst.inventory_item_id,
604 min(mst.organization_id),
605 mst.transaction_action_name,
606 sum(mst.net_qty),
607 sum(mst.net_val),
608 sum(mst.num_txns),
609 sum(mst.abs_qty),
610 sum(mst.abs_val)
611 from mtl_summary_temp mst
612 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
613 and session_id = sessionid
614 group by mst.inventory_item_id, mst.transaction_action_name;
615
616 insert into mtl_summary_temp
617 (session_id,
618 summary_type,
619 subinventory,
620 cost_group_id,
621 net_qty,
622 net_val,
623 num_txns,
624 abs_qty,
625 abs_val)
626 select
627 sessionid,
628 'SUB_COST_GROUP_SUMMARY',
629 mmt.subinventory_code,
630 mmt.cost_group_id,
631 cratio*sum(mmt.primary_quantity),
632 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
633 sum(1),
634 cratio*sum(abs(mmt.primary_quantity)),
635 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
636 from mtl_material_transactions mmt
637 where mmt.subinventory_code = sub
638 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
639 and mmt.organization_id = orgid
640 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
641 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
642 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
643 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
644 group by mmt.subinventory_code, mmt.cost_group_id;
645
646 /* Cost_group_summary */
647 insert into mtl_summary_temp
648 (session_id,
649 summary_type,
650 cost_group_id,
651 net_qty,
652 net_val,
653 num_txns,
654 abs_qty,
655 abs_val)
656 select
657 sessionid,
658 'COST_GROUP_SUMMARY',
659 mst.cost_group_id,
660 sum(mst.net_qty),
661 sum(mst.net_val),
662 sum(mst.num_txns),
663 sum(mst.abs_qty),
664 sum(mst.abs_val)
665 from mtl_summary_temp mst
666 where mst.summary_type = 'SUB_COST_GROUP_SUMMARY'
667 and session_id = sessionid
668 group by mst.cost_group_id;
669 -- Bug 3614951 changing the below sql to execute conditionally.
670 -- Begin changes
671 IF catsetid IS NULL AND catid IS NULL THEN
672 /* Totals Summary */
673 insert into mtl_summary_temp
674 (session_id,
675 summary_type,
676 net_val,
677 num_txns,
678 locator_id)
679 select
680 sessionid,
681 'TOTALS',
682 round(nvl(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),0),prc),
683 nvl(sum(1),0),
684 1
685 from mtl_material_transactions mmt
686 where mmt.organization_id = orgid
687 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
688 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
689 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
690 and mmt.subinventory_code = sub
691 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
692 and mmt.primary_quantity > 0
693 and mmt.transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
694
695 insert into mtl_summary_temp
696 (session_id,
697 summary_type,
698 net_val,
699 num_txns,
700 locator_id) /* This is populated for ordering purposes */
701 select
702 sessionid,
703 'TOTALS',
704 round(nvl(abs(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity)),0),prc),
705 nvl(sum(1),0),
706 2
707 from mtl_material_transactions mmt
708 where mmt.organization_id = orgid
709 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
710 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
711 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
712 and mmt.subinventory_code = sub
713 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
717 insert into mtl_summary_temp
714 and mmt.primary_quantity < 0
715 and mmt.transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
716 ELSE
718 (session_id,
719 summary_type,
720 net_val,
721 num_txns,
722 locator_id)
723 select
724 sessionid,
725 'TOTALS',
726 round(nvl(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),0),prc),
727 nvl(sum(1),0),
728 1
729 from mtl_material_transactions mmt
730 where mmt.organization_id = orgid
731 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
732 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
733 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
734 and mmt.subinventory_code = sub
735 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
736 and exists ( select 'X'
737 from mtl_item_categories mic
738 where mic.category_set_id = nvl(catsetid, mic.category_set_id)
739 and mic.category_id = nvl(catid, mic.category_id)
740 and mic.inventory_item_id = mmt.inventory_item_id
741 and mic.organization_id = orgid)
742 and mmt.primary_quantity > 0
743 and mmt.transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
744
745 insert into mtl_summary_temp
746 (session_id,
747 summary_type,
748 net_val,
749 num_txns,
750 locator_id) /* This is populated for ordering purposes */
751 select
752 sessionid,
753 'TOTALS',
754 round(nvl(abs(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity)),0),prc),
755 nvl(sum(1),0),
756 2
757 from mtl_material_transactions mmt
758 where mmt.organization_id = orgid
759 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
760 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
761 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
762 and mmt.subinventory_code = sub
763 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
764 and exists ( select 'X'
765 from mtl_item_categories mic
766 where mic.category_set_id = nvl(catsetid, mic.category_set_id)
767 and mic.category_id = nvl(catid, mic.category_id)
768 and mic.inventory_item_id = mmt.inventory_item_id
769 and mic.organization_id = orgid)
770 and mmt.primary_quantity < 0
771 and mmt.transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
772 END IF;
773 -- End changes bug 3614951
774 insert into mtl_summary_temp
775 (session_id,
776 summary_type,
777 net_val,
778 num_txns,
779 locator_id) /* This is populated for ordering purposes */
780 select
781 sessionid,
782 'TOTALS',
783 sum(mst.net_val),
784 null,
785 3
786 from mtl_summary_temp mst
787 where summary_type = 'ITEM_SUMMARY'
788 and session_id = sessionid;
789 exception
790 when others then
791 raise_application_error(-20001, sqlerrm||'---'||msgbuf);
792 end sub_only_summaries;
793
794 procedure both_summaries (
795 sessionid number,
796 orgid mtl_parameters.organization_id%TYPE,
797 invid mtl_system_items.inventory_item_id%TYPE,
798 rev mtl_item_revisions.revision%TYPE,
799 uom mtl_system_items.primary_uom_code%TYPE,
800 puom mtl_system_items.primary_uom_code%TYPE,
801 sub mtl_secondary_inventories.secondary_inventory_name%TYPE,
802 locid mtl_item_locations.inventory_location_id%TYPE,
803 sdate mtl_material_transactions.transaction_date%TYPE,
804 edate mtl_material_transactions.transaction_date%TYPE,
805 cg_id mtl_secondary_inventories.default_cost_group_id%TYPE)
806 is
807 cratio number := 1;
808 prc number := 1;
809 msgbuf varchar2(200);
810 begin
811 delete from mtl_summary_temp where session_id = sessionid;
812
813 /*Bug4950410 : Added the following query to get the currency precision to round the inventory value.
814 Also rounding the net_val and abs_val based on the currency precision of the organization. */
815 select fc.precision into prc
816 from org_organization_definitions ood,
817 gl_sets_of_books sob,
818 fnd_currencies fc
819 where ood.organization_id = orgid
820 and ood.set_of_books_id = sob.set_of_books_id
821 and fc.currency_code = sob.currency_code;
822
823 if (puom <> uom) then
824 cratio := inv_convert.inv_um_convert(invid,5,null, puom,uom,null,null);
825 end if;
826
827 /* Source Type and Txn Type Summary */
828 insert into mtl_summary_temp
829 (session_id,
830 summary_type,
831 transaction_source_type_name,
832 transaction_type_name,
833 transaction_action_name,
834 net_qty,
835 net_val,
836 num_txns,
837 abs_qty,
838 abs_val)
839 select
840 sessionid,
841 'SRCTYPE_TXNTYPE_SUMMARY',
842 mtst.transaction_source_type_name,
843 mtt.transaction_type_name,
844 ml.meaning,
845 cratio*sum(mmt.primary_quantity),
849 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
846 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
847 sum(1),
848 cratio*sum(abs(mmt.primary_quantity)),
850 from mtl_material_transactions mmt,
851 mtl_txn_source_types mtst,
852 mtl_transaction_types mtt,
853 mfg_lookups ml
854 where mmt.inventory_item_id = invid
855 and mmt.organization_id = orgid
856 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
857 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
858 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
859 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
860 and mtst.transaction_source_type_id = mmt.transaction_source_type_id
861 and mtt.transaction_type_id = mmt.transaction_type_id
862 and mmt.subinventory_code||'' = sub
863 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
864 and ml.lookup_code = mmt.transaction_action_id + 0
865 and ml.lookup_type = 'MTL_TRANSACTION_ACTION'
866 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
867 group by mtst.transaction_source_type_name, mtt.transaction_type_name, ml.meaning;
868
869 /* Source Type Summary */
870 insert into mtl_summary_temp
871 (session_id,
872 summary_type,
873 transaction_source_type_name,
874 net_qty,
875 net_val,
876 num_txns,
877 abs_qty,
878 abs_val)
879 select
880 sessionid,
881 'SOURCE_TYPES_SUMMARY',
882 mst.transaction_source_type_name,
883 sum(mst.net_qty),
884 sum(mst.net_val),
885 sum(mst.num_txns),
886 sum(mst.abs_qty),
887 sum(mst.abs_val)
888 from mtl_summary_temp mst
889 where mst.summary_type = 'SRCTYPE_TXNTYPE_SUMMARY'
890 and session_id = sessionid
891 group by mst.transaction_source_type_name;
892
893 /* */
894 /* Transaction type summary */
895
896 insert into mtl_summary_temp
897 (session_id,
898 summary_type,
899 transaction_type_name,
900 net_qty,
901 net_val,
902 num_txns,
903 abs_qty,
904 abs_val)
905 select
906 sessionid,
907 'TXN_TYPES_SUMMARY',
908 mst.transaction_type_name,
909 sum(mst.net_qty),
910 sum(mst.net_val),
911 sum(mst.num_txns),
912 sum(mst.abs_qty),
913 sum(mst.abs_val)
914 from mtl_summary_temp mst
915 where mst.summary_type = 'SRCTYPE_TXNTYPE_SUMMARY'
916 and session_id = sessionid
917 group by mst.transaction_type_name;
918 /* */
919
920 /* Action Summary */
921 insert into mtl_summary_temp
922 (session_id,
923 summary_type,
924 transaction_action_name,
925 net_qty,
926 net_val,
927 num_txns,
928 abs_qty,
929 abs_val)
930 select
931 sessionid,
932 'TXN_ACTION_SUMMARY',
933 mst.transaction_action_name,
934 sum(mst.net_qty),
935 sum(mst.net_val),
936 sum(mst.num_txns),
937 sum(mst.abs_qty),
938 sum(mst.abs_val)
939 from mtl_summary_temp mst
940 where mst.summary_type = 'SRCTYPE_TXNTYPE_SUMMARY'
941 and session_id = sessionid
942 group by mst.transaction_action_name;
943
944 /* cost group subinventory summary */
945 insert into mtl_summary_temp
946 (session_id,
947 summary_type,
948 subinventory,
949 cost_group_id,
950 net_qty,
951 net_val,
952 num_txns,
953 abs_qty,
954 abs_val)
955 select
956 sessionid,
957 'SUB_COST_GROUP_SUMMARY',
958 mmt.subinventory_code,
959 mmt.cost_group_id,
960 cratio*sum(mmt.primary_quantity),
961 round(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),prc),
962 sum(1),
963 cratio*sum(abs(mmt.primary_quantity)),
964 round(sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity)),prc)
965 from mtl_material_transactions mmt
966 where mmt.subinventory_code = sub
970 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
967 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
968 and mmt.organization_id = orgid
969 and mmt.inventory_item_id = invid
971 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
972 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
973 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
974 and mmt.transaction_action_id NOT IN (24,30,50,51,52) /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
975 group by mmt.subinventory_code, mmt.cost_group_id;
976
977 /* Cost_group_summary */
978 insert into mtl_summary_temp
979 (session_id,
980 summary_type,
981 cost_group_id,
982 net_qty,
983 net_val,
984 num_txns,
985 abs_qty,
986 abs_val)
987 select
988 sessionid,
989 'COST_GROUP_SUMMARY',
990 mst.cost_group_id,
991 sum(mst.net_qty),
992 sum(mst.net_val),
993 sum(mst.num_txns),
994 sum(mst.abs_qty),
995 sum(mst.abs_val)
996 from mtl_summary_temp mst
997 where mst.summary_type = 'SUB_COST_GROUP_SUMMARY'
998 and session_id = sessionid
999 group by mst.cost_group_id;
1000
1001
1002 /* Totals */
1003 insert into mtl_summary_temp
1004 (session_id,
1005 summary_type,
1006 net_qty,
1007 net_val,
1008 num_txns,
1009 locator_id) /* This is populated for ordering purposes */
1010 select
1011 sessionid,
1012 'TOTALS',
1013 nvl(cratio*sum(mmt.primary_quantity),0),
1014 round(nvl(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),0),prc),
1015 nvl(sum(1),0),
1016 1
1017 from mtl_material_transactions mmt
1018 where mmt.inventory_item_id = invid
1019 and mmt.organization_id = orgid
1020 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
1021 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1022 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1023 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
1024 and mmt.subinventory_code||'' = sub
1025 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
1026 and mmt.primary_quantity > 0
1027 and mmt.transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
1028
1029
1030 insert into mtl_summary_temp
1031 (session_id,
1032 summary_type,
1033 net_qty,
1034 net_val,
1035 num_txns,
1036 locator_id) /* This is populated for ordering purposes */
1037 select
1038 sessionid,
1039 'TOTALS',
1040 nvl(abs(cratio*sum(mmt.primary_quantity)),0),
1041 round(nvl(abs(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity)),0),prc),
1042 nvl(sum(1),0),
1043 2
1044 from mtl_material_transactions mmt
1045 where mmt.inventory_item_id = invid
1046 and mmt.organization_id = orgid
1047 and NVL(mmt.cost_group_id,-9999) = NVL(NVL(cg_id,mmt.cost_group_id),-9999)
1048 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1049 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1050 and (mmt.revision = nvl(rev, mmt.revision) or mmt.revision is null)
1051 and mmt.subinventory_code||'' = sub
1052 and (mmt.locator_id = nvl(locid, mmt.locator_id) or mmt.locator_id is null)
1053 and mmt.primary_quantity < 0
1054 and mmt.transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
1055
1056 insert into mtl_summary_temp
1057 (session_id,
1058 summary_type,
1059 net_qty,
1060 net_val,
1061 num_txns,
1062 locator_id) /* This is populated for ordering purposes */
1063 select
1064 sessionid,
1065 'TOTALS',
1066 sum(mst.net_qty),
1067 sum(mst.net_val),
1068 null,
1069 3
1070 from mtl_summary_temp mst
1071 where mst.summary_type = 'TXN_ACTION_SUMMARY'
1072 and session_id = sessionid;
1073
1074 exception
1075 when others then
1076 raise_application_error(-20001, sqlerrm||'---'||msgbuf);
1077 end both_summaries;
1078
1079 /* procedure cost_group_summaries (
1080 sessionid number,
1081 orgid mtl_parameters.organization_id%TYPE,
1082 orgloctype NUMBER,
1083 cost_group_id mtl_secondary_inventories.default_cost_group_id%TYPE,
1084 sdate mtl_material_transactions.transaction_date%TYPE,
1085 edate mtl_material_transactions.transaction_date%TYPE)
1086 IS
1087 msgbuf varchar2(200);
1088 cratio number := 1;
1089 begin
1090 delete from mtl_summary_temp where session_id = sessionid;
1091 */
1092 /* Item, Source Type, Txn Type summary */
1093 /* We are inserting transaction action as well
1094 for later summarization */
1095 /* insert into mtl_summary_temp
1096 (session_id,
1097 summary_type,
1098 inventory_item_id,
1099 organization_id,
1100 transaction_source_type_name,
1101 transaction_type_name,
1102 transaction_action_name,
1103 net_qty,
1104 net_val,
1108 select
1105 num_txns,
1106 abs_qty,
1107 abs_val)
1109 sessionid,
1110 'ITEM_SRCTYPE_TXNTYPE_SUMMARY',
1111 mmt.inventory_item_id,
1112 min(mmt.organization_id),
1113 mtst.transaction_source_type_name,
1114 mtt.transaction_type_name,
1115 ml.meaning,
1116 sum(mmt.primary_quantity),
1117 sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),
1118 sum(1),
1119 sum(abs(mmt.primary_quantity)),
1120 sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity))
1121 from mtl_material_transactions mmt,
1122 mtl_txn_source_types mtst,
1123 mfg_lookups ml,
1124 mtl_transaction_types mtt
1125 where mmt.organization_id = orgid
1126 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1127 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1128 and mmt.cost_group_id = cost_group_id
1129 and mtst.transaction_source_type_id = mmt.transaction_source_type_id
1130 and mtt.transaction_type_id = mmt.transaction_type_id
1131 and ml.lookup_type = 'MTL_TRANSACTION_ACTION'
1132 and ml.lookup_code = mmt.transaction_action_id + 0
1133 and mmt.transaction_action_id NOT IN (24,30)
1134 group by mmt.inventory_item_id,
1135 mtst.transaction_source_type_name,
1136 mtt.transaction_type_name,
1137 ml.meaning;*/
1138
1139 /* Item, Source Type summary */
1140 /*insert into mtl_summary_temp
1141 (session_id,
1142 summary_type,
1143 inventory_item_id,
1144 organization_id,
1145 transaction_source_type_name,
1146 net_qty,
1147 net_val,
1148 num_txns,
1149 abs_qty,
1150 abs_val)
1151 select
1152 sessionid,
1153 'ITEM_SRCTYPE_SUMMARY',
1154 mst.inventory_item_id,
1155 min(mst.organization_id),
1156 mst.transaction_source_type_name,
1157 sum(mst.net_qty),
1158 sum(mst.net_val),
1159 sum(mst.num_txns),
1160 sum(mst.abs_qty),
1161 sum(mst.abs_val)
1162 from mtl_summary_temp mst
1163 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
1164 and session_id = sessionid
1165 group by mst.inventory_item_id, mst.transaction_source_type_name;
1166 */
1167 /* Item, Txn Type summary */
1168 /* insert into mtl_summary_temp
1169 (session_id,
1170 summary_type,
1171 inventory_item_id,
1172 organization_id,
1173 transaction_type_name,
1174 net_qty,
1175 net_val,
1176 num_txns,
1177 abs_qty,
1178 abs_val)
1179 select
1180 sessionid,
1181 'ITEM_TXN_TYPES_SUMMARY',
1182 mst.inventory_item_id,
1183 min(mst.organization_id),
1184 mst.transaction_type_name,
1185 sum(mst.net_qty),
1186 sum(mst.net_val),
1187 sum(mst.num_txns),
1188 sum(mst.abs_qty),
1189 sum(mst.abs_val)
1190 from mtl_summary_temp mst
1191 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
1192 and session_id = sessionid
1193 group by mst.inventory_item_id, mst.transaction_type_name;
1194 */
1195 /* Item, Txn action summary */
1196 /* insert into mtl_summary_temp
1197 (session_id,
1198 summary_type,
1199 inventory_item_id,
1200 organization_id,
1201 transaction_action_name,
1202 net_qty,
1203 net_val,
1204 num_txns,
1205 abs_qty,
1206 abs_val)
1207 select
1208 sessionid,
1209 'ITEM_TXN_ACTION_SUMMARY',
1210 mst.inventory_item_id,
1211 min(mst.organization_id),
1212 mst.transaction_action_name,
1213 sum(mst.net_qty),
1214 sum(mst.net_val),
1215 sum(mst.num_txns),
1216 sum(mst.abs_qty),
1217 sum(mst.abs_val)
1218 from mtl_summary_temp mst
1219 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
1220 and session_id = sessionid
1221 group by mst.inventory_item_id, mst.transaction_action_name;
1222 */
1223 /* Subinventory Summary */
1224 /* if (orgloctype = 1) then
1225 insert into mtl_summary_temp
1226 (session_id,
1230 net_val,
1227 summary_type,
1228 subinventory,
1229 net_qty,
1231 num_txns,
1232 abs_qty,
1233 abs_val)
1234 select
1235 sessionid,
1236 'SUBINVENTORY_SUMMARY',
1237 mmt.subinventory_code,
1238 cratio*sum(mmt.primary_quantity),
1239 sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),
1240 sum(1),
1241 cratio*sum(abs(mmt.primary_quantity)),
1242 sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity))
1243 from mtl_material_transactions mmt
1244 where mmt.cost_group_id = cost_group_id
1245 and mmt.organization_id = orgid
1246 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1247 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1248 and mmt.transaction_action_id NOT IN (24,30)
1249 group by mmt.subinventory_code;
1250 else*/
1251 /* Subinventory - Locator Summary */
1252 /* insert into mtl_summary_temp
1253 (session_id,
1254 summary_type,
1255 subinventory,
1256 locator_id,
1257 net_qty,
1258 net_val,
1259 num_txns,
1260 abs_qty,
1261 abs_val)
1262 select
1263 sessionid,
1264 'SUBINVENTORY_LOCATOR_SUMMARY',
1265 mmt.subinventory_code,
1266 mmt.locator_id,
1267 cratio*sum(mmt.primary_quantity),
1268 sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),
1269 sum(1),
1270 cratio*sum(abs(mmt.primary_quantity)),
1271 sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity))
1272 from mtl_material_transactions mmt
1273 where mmt.cost_group_id = cost_group_id
1274 and mmt.organization_id = orgid
1275 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1276 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1277 and mmt.transaction_action_id NOT IN (24,30)
1278 group by mmt.subinventory_code, mmt.locator_id;
1279 */
1280 /* Subinventory Summary */
1281 /* insert into mtl_summary_temp
1282 (session_id,
1283 summary_type,
1284 subinventory,
1285 net_qty,
1286 net_val,
1287 num_txns,
1288 abs_qty,
1289 abs_val)
1290 select
1291 sessionid,
1292 'SUBINVENTORY_SUMMARY',
1293 mst.subinventory,
1294 sum(mst.net_qty),
1295 sum(mst.net_val),
1296 sum(mst.num_txns),
1297 sum(mst.abs_qty),
1298 sum(mst.abs_val)
1299 from mtl_summary_temp mst
1300 where mst.summary_type = 'SUBINVENTORY_LOCATOR_SUMMARY'
1301 and session_id = sessionid
1302 group by mst.subinventory;
1303 end if;
1304 */
1305 /* subinventory cost group summary */
1306 /* changes for zone/rearchitecture project */
1307 /* ssia 06/02/00 */
1308 /* insert into mtl_summary_temp
1309 (session_id,
1310 summary_type,
1311 subinventory,
1312 cost_group_id,
1313 net_qty,
1314 net_val,
1315 num_txns,
1316 abs_qty,
1317 abs_val)
1318 select
1319 sessionid,
1320 'SUB_COST_GROUP_SUMMARY',
1321 mmt.subinventory_code,
1322 mmt.cost_group_id,
1323 cratio*sum(mmt.primary_quantity),
1324 sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),
1325 sum(1),
1326 cratio*sum(abs(mmt.primary_quantity)),
1327 sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity))
1328 from mtl_material_transactions mmt
1329 where mmt.cost_group_id = cost_group_id
1330 and mmt.organization_id = orgid
1331 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1332 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1333 and mmt.transaction_action_id NOT IN (24,30)
1334 group by mmt.subinventory_code, mmt.cost_group_id;
1335 */
1336 /* Cost_group_summary */
1337 /* insert into mtl_summary_temp
1338 (session_id,
1339 summary_type,
1340 cost_group_id,
1341 net_qty,
1342 net_val,
1343 num_txns,
1344 abs_qty,
1345 abs_val)
1346 select
1347 sessionid,
1348 'COST_GROUP_SUMMARY',
1349 mst.cost_group_id,
1350 sum(mst.net_qty),
1351 sum(mst.net_val),
1352 sum(mst.num_txns),
1353 sum(mst.abs_qty),
1354 sum(mst.abs_val)
1355 from mtl_summary_temp mst
1356 where mst.summary_type = 'SUB_COST_GROUP_SUMMARY'
1357 and session_id = sessionid
1358 group by mst.cost_group_id;
1359 */
1360 /* Totals Summary */
1361 /* insert into mtl_summary_temp
1362 (session_id,
1363 summary_type,
1364 net_val,
1365 num_txns,
1366 locator_id)
1367 select
1368 sessionid,
1369 'TOTALS',
1370 nvl(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),0),
1371 nvl(sum(1),0),
1372 1
1373 from mtl_material_transactions mmt
1374 where mmt.organization_id = orgid
1375 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1376 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1377 and mmt.cost_group_id = cost_group_id
1378 and mmt.primary_quantity > 0
1379 and mmt.transaction_action_id NOT IN (24,30);
1380
1381 insert into mtl_summary_temp
1385 num_txns,
1382 (session_id,
1383 summary_type,
1384 net_val,
1386 locator_id)*/ /* This is populated for ordering purposes */
1387 /* select
1388 sessionid,
1389 'TOTALS',
1390 nvl(abs(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity)),0),
1391 nvl(sum(1),0),
1392 2
1393 from mtl_material_transactions mmt
1394 where mmt.organization_id = orgid
1395 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1396 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1397 and mmt.cost_group_id = cost_group_id
1398 and mmt.primary_quantity < 0
1399 and mmt.transaction_action_id NOT IN (24,30);
1400
1401 insert into mtl_summary_temp
1402 (session_id,
1403 summary_type,
1404 net_val,
1405 num_txns,
1406 locator_id)*/ /* This is populated for ordering purposes */
1407 /* select
1408 sessionid,
1409 'TOTALS',
1410 sum(mst.net_val),
1411 null,
1412 3
1413 from mtl_summary_temp mst
1414 where summary_type = 'ITEM_SUMMARY'
1415 and session_id = sessionid;
1416 exception
1417 when others then
1418 raise_application_error(-20001, sqlerrm||'---'||msgbuf);
1419 end cost_group_summaries;
1420
1421 procedure sub_cost_group_summaries(
1422 sessionid NUMBER,
1423 orgid mtl_parameters.organization_id%TYPE,
1424 sub mtl_secondary_inventories.secondary_inventory_name%TYPE,
1425 cost_group_id mtl_secondary_inventories.default_cost_group_id%TYPE,
1426 sdate mtl_material_transactions.transaction_date%TYPE,
1427 edate mtl_material_transactions.transaction_date%TYPE)
1428 IS
1429 msgbuf varchar2(200);
1430 begin
1431
1432 delete from mtl_summary_temp where session_id = sessionid;
1433 */
1434 /* Item, Source Type, Txn Type summary */
1435 /* We are inserting transaction action as well
1436 for later summarization */
1437 /* insert into mtl_summary_temp
1438 (session_id,
1439 summary_type,
1440 inventory_item_id,
1441 organization_id,
1442 transaction_source_type_name,
1443 transaction_type_name,
1444 transaction_action_name,
1445 net_qty,
1446 net_val,
1447 num_txns,
1448 abs_qty,
1449 abs_val)
1450 select
1451 sessionid,
1452 'ITEM_SRCTYPE_TXNTYPE_SUMMARY',
1453 mmt.inventory_item_id,
1454 min(mmt.organization_id),
1455 mtst.transaction_source_type_name,
1456 mtt.transaction_type_name,
1457 ml.meaning,
1458 sum(mmt.primary_quantity),
1459 sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),
1460 sum(1),
1461 sum(abs(mmt.primary_quantity)),
1462 sum(nvl(mmt.actual_cost,0)*abs(mmt.primary_quantity))
1463 from mtl_material_transactions mmt,
1464 mtl_txn_source_types mtst,
1465 mfg_lookups ml,
1466 mtl_transaction_types mtt
1467 where mmt.organization_id = orgid
1468 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1469 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1470 and mmt.cost_group_id = cost_group_id
1471 and mmt.subinventory_code = sub
1472 and mtt.transaction_type_id = mmt.transaction_type_id
1473 and ml.lookup_type = 'MTL_TRANSACTION_ACTION'
1474 and ml.lookup_code = mmt.transaction_action_id + 0
1475 and mmt.transaction_action_id NOT IN (24,30)
1476 group by mmt.inventory_item_id,
1477 mtst.transaction_source_type_name,
1478 mtt.transaction_type_name,
1479 ml.meaning;
1480 */
1481 /* Item, Source Type summary */
1482 /* insert into mtl_summary_temp
1483 (session_id,
1484 summary_type,
1485 inventory_item_id,
1486 organization_id,
1487 transaction_source_type_name,
1488 net_qty,
1489 net_val,
1490 num_txns,
1491 abs_qty,
1492 abs_val)
1493 select
1494 sessionid,
1495 'ITEM_SRCTYPE_SUMMARY',
1496 mst.inventory_item_id,
1497 min(mst.organization_id),
1498 mst.transaction_source_type_name,
1499 sum(mst.net_qty),
1500 sum(mst.net_val),
1501 sum(mst.num_txns),
1505 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
1502 sum(mst.abs_qty),
1503 sum(mst.abs_val)
1504 from mtl_summary_temp mst
1506 and session_id = sessionid
1507 group by mst.inventory_item_id,
1508 mst.transaction_source_type_name;
1509 */
1510 /* Item, Txn Type summary */
1511 /* insert into mtl_summary_temp
1512 (session_id,
1513 summary_type,
1514 inventory_item_id,
1515 organization_id,
1516 transaction_type_name,
1517 net_qty,
1518 net_val,
1519 num_txns,
1520 abs_qty,
1521 abs_val)
1522 select
1523 sessionid,
1524 'ITEM_TXN_TYPES_SUMMARY',
1525 mst.inventory_item_id,
1526 min(mst.organization_id),
1527 mst.transaction_type_name,
1528 sum(mst.net_qty),
1529 sum(mst.net_val),
1530 sum(mst.num_txns),
1531 sum(mst.abs_qty),
1532 sum(mst.abs_val)
1533 from mtl_summary_temp mst
1534 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
1535 and session_id = sessionid
1536 group by mst.inventory_item_id,
1537 mst.transaction_type_name;
1538 */
1539 /* Item, Txn action summary */
1540 /* insert into mtl_summary_temp
1541 (session_id,
1542 summary_type,
1543 inventory_item_id,
1544 organization_id,
1545 transaction_action_name,
1546 net_qty,
1547 net_val,
1548 num_txns,
1549 abs_qty,
1550 abs_val)
1551 select
1552 sessionid,
1553 'ITEM_TXN_ACTION_SUMMARY',
1554 mst.inventory_item_id,
1555 min(mst.organization_id),
1556 mst.transaction_action_name,
1557 sum(mst.net_qty),
1558 sum(mst.net_val),
1559 sum(mst.num_txns),
1560 sum(mst.abs_qty),
1561 sum(mst.abs_val)
1562 from mtl_summary_temp mst
1563 where summary_type='ITEM_SRCTYPE_TXNTYPE_SUMMARY'
1564 and session_id = sessionid
1565 group by mst.inventory_item_id,
1566 mst.transaction_action_name;
1567 */
1568 /* Totals Summary */
1569 /* insert into mtl_summary_temp
1570 (session_id,
1571 summary_type,
1572 net_val,
1573 num_txns,
1574 locator_id)
1575 select
1576 sessionid,
1577 'TOTALS',
1578 nvl(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity),0),
1579 nvl(sum(1),0),
1580 1
1581 from mtl_material_transactions mmt
1582 where mmt.organization_id = orgid
1583 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1584 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1585 and mmt.subinventory_code = sub
1586 and mmt.cost_group_id = nvl(cost_Group_id, mmt.cost_Group_id)
1587 and mmt.primary_quantity > 0
1588 and mmt.transaction_action_id NOT IN (24,30);
1589
1590 insert into mtl_summary_temp
1591 (session_id,
1592 summary_type,
1593 net_val,
1594 num_txns,
1595 locator_id)*/ /* This is populated for ordering purposes */
1596 /*select
1597 sessionid,
1598 'TOTALS',
1599 nvl(abs(sum(nvl(mmt.actual_cost,0)*mmt.primary_quantity)),0),
1600 nvl(sum(1),0),
1601 2
1602 from mtl_material_transactions mmt
1603 where mmt.organization_id = orgid
1604 and mmt.transaction_date >= NVL(sdate, mmt.transaction_date - 1)
1605 and mmt.transaction_date <= NVL(edate, mmt.transaction_date + 1)
1606 and mmt.subinventory_code = sub
1607 and mmt.cost_group_id = nvl(cost_group_id, mmt.locator_id)
1608 and mmt.primary_quantity < 0
1609 and mmt.transaction_action_id NOT IN (24,30);
1610
1611 insert into mtl_summary_temp
1612 (session_id,
1613 summary_type,
1614 net_val,
1615 num_txns,
1616 locator_id)*/ /* This is populated for ordering purposes */
1617 /* select
1618 sessionid,
1619 'TOTALS',
1620 sum(mst.net_val),
1621 null,
1622 3
1623 from mtl_summary_temp mst
1624 where summary_type = 'ITEM_SUMMARY'
1625 and session_id = sessionid;
1626 exception
1627 when others then
1628 raise_application_error(-20001, sqlerrm||'---'||msgbuf);
1629 end sub_cost_group_summaries;*/
1630
1631 END INVTVTUS;