1 PACKAGE BODY mtl_movement_stat_pkg AS
2 /* $Header: INVMVSTB.pls 120.1 2005/06/11 11:39:03 appldev $ */
3
4 -- ***************************************************************
5 /* Use forward declaration because procedure
6 upd_ins_rcv_movements calls get_uom_code, which is not
7 yet declared when the call is made. */
8
9 FUNCTION get_uom_code (p_transaction_uom_code VARCHAR2) RETURN VARCHAR2;
10 -- ***************************************************************
11 --
12 -- PUBLIC PROCEDURES
13 --
14 -- PUBLIC VARIABLES
15 -- pseudo_movement_id
16 -- user_id
17 --
18
19 -- ***************************************************************
20 -- Procedure
21 -- uom_or_quantity_changes
22
23 -- Purpose
24 -- ** PRIVATE PROCEDURE **
25 -- Process any changes to UOM or quantity. This is always called by the
26 -- PO and INV processors.
27 -- If UOM is changed then the unit price and outside unit price are also
28 -- changed. If the UOM or quantity is changed then the total columns are
29 -- re-calculated.
30 -- For weight, if the weight_method is 'MU' then the unit weight was
31 -- entered and thus a change of qty will require the total weight
32 -- to be changed. If w_m = 'MT' then the total weight was entered
33 -- and thus a change to qty will require a change to the unit weight.
34 -- If the UOM is changed then if w_m = 'MU' then the unit_weight is
35 -- recalculated according to the conversion factor (and thus the
36 -- total_weight also). For w_m = 'MT', however, then a change to the UOM
37 -- has no effect because the unit weight is just a calculated value
38 -- from the total weight.
39
40 -- History
41 -- MAY-10-95 Paul Barry Created
42
43 procedure uom_or_quantity_changes (
44 p_old_transaction_uom_code in varchar2,
45 p_new_transaction_uom_code in varchar2,
46 p_old_transaction_quantity in number,
47 p_new_transaction_quantity in number,
48 p_inventory_item_id in number,
49 p_organization_id in number,
50 p_currency_code in varchar2,
51 p_document_unit_price IN OUT NOCOPY /* file.sql.39 change */ number,
52 p_document_line_ext_value IN OUT NOCOPY /* file.sql.39 change */ number,
53 p_weight_method in varchar2,
54 p_unit_weight IN OUT NOCOPY /* file.sql.39 change */ number,
55 p_total_weight IN OUT NOCOPY /* file.sql.39 change */ number,
56 p_stat_adj_percent in number,
57 p_stat_adj_amount in number,
58 p_stat_ext_value IN OUT NOCOPY /* file.sql.39 change */ number,
59 p_outside_unit_price IN OUT NOCOPY /* file.sql.39 change */ number,
60 p_outside_ext_value IN OUT NOCOPY /* file.sql.39 change */ number) as
61 --
62 l_conversion_factor number;
63 l_precision number(1);
64 l_mau number;
65 --
66 --
67 -- Private function to format calculated monetary totals
68 -- according to the precision and MAU of the currency.
69 --
70 function format (p_number number)
71 return number
72 is
73 begin
74 if l_mau is null then
75 return(round(p_number, l_precision));
76 else
77 return(round(p_number / l_mau) * l_mau);
78 end if;
79 end format;
80 --
81 begin
82 --
83 -- First see if the UOM has changed. If it has then convert the
84 -- unit price and outside unit price.
85 --
86 -- e.g. if old UOM is kg and new UOM is grammes (g) then the
87 -- conversion factor will be 1000 and so the price will be reduced
88 -- by 1000 times now we are dealing in grammes.
89 --
90 -- If the UOM or quantity changed in the forms then
91 -- re-calculate the total values.
92 --
93 if p_old_transaction_uom_code <> p_new_transaction_uom_code then
94 --
95 -- Find conversion factor.
96 --
97 select c1.conversion_rate / c2.conversion_rate
98 into l_conversion_factor
99 from mtl_uom_conversions_view c1,
100 mtl_uom_conversions_view c2
101 where c1.organization_id = P_ORGANIZATION_ID
102 and c2.organization_id = P_ORGANIZATION_ID
103 and c1.inventory_item_id = P_INVENTORY_ITEM_ID
104 and c2.inventory_item_id = P_INVENTORY_ITEM_ID
105 and c1.uom_code = P_OLD_TRANSACTION_UOM_CODE
106 and c2.uom_code = P_NEW_TRANSACTION_UOM_CODE;
107 --
108 -- Change the unit price and outside unit price if they
109 -- already contain values (unit price always will).
110 --
114 p_outside_unit_price := p_outside_unit_price /
111 p_document_unit_price := p_document_unit_price /
112 l_conversion_factor;
113 if p_outside_unit_price is not null then
115 l_conversion_factor;
116 end if;
117 --
118 -- If w_m = 'MU' then the unit_weight is recalculated and
119 -- the total_weight also (just below).
120 --
121 if p_unit_weight is not null and p_weight_method = 'MU' then
122 p_unit_weight := p_unit_weight / l_conversion_factor;
123 end if;
124 end if;
125 --
126 -- Re-calculate all totals if the UOM or quantity are changed. Use the
127 -- currency minimum accountable unit (MAU) and precision to 'format'
128 -- the monetary totals. Also update weight values.
129 --
130 if p_old_transaction_uom_code <> p_new_transaction_uom_code or
131 p_old_transaction_quantity <> abs(p_new_transaction_quantity) then
132 --
133 select c.minimum_accountable_unit,
134 c.precision
135 into l_mau,
136 l_precision
137 from fnd_currencies c
138 where c.currency_code = P_CURRENCY_CODE;
139 --
140 p_document_line_ext_value := format(p_document_unit_price *
141 abs(p_new_transaction_quantity));
142 --
143 -- If w_m = 'MT' then the total weight should remain fixed
144 -- and hence we need to update the unit weight. If = 'MU'
145 -- then the total weight needs to be re-calculated from the
146 -- unit weight.
147 --
148 if p_unit_weight is not null then
149 if p_weight_method = 'MU' then
150 p_total_weight := p_unit_weight *
151 abs(p_new_transaction_quantity);
152 elsif p_weight_method = 'MT' then
153 p_unit_weight := p_total_weight /
154 abs(p_new_transaction_quantity);
155 end if;
156 end if;
157 if p_stat_adj_amount is not null then
158 p_stat_ext_value := format(p_document_line_ext_value +
159 p_stat_adj_amount);
160 elsif p_stat_adj_percent is not null then
161 p_stat_ext_value := format(p_document_line_ext_value *
162 (1 + (p_stat_adj_percent / 100)));
163 else p_stat_ext_value := format(p_document_line_ext_value);
164 end if;
165 if p_outside_unit_price is not null then
166 p_outside_ext_value := format(p_outside_unit_price *
167 abs(p_new_transaction_quantity));
168 end if;
169 end if;
170 end uom_or_quantity_changes;
171
172 -- ***************************************************************
173 -- Procedure
174 -- upd_inv_movements
175
176 -- Purpose
177 -- This procedure is called from the inventory or cost
178 -- processor to update either the item unit cost or the
179 -- status, item unit cost, primary quantity, transaction
180 -- quantity and transaction uom code for movement
181 -- records in MTL_MOVEMENT_STATISTICS_TABLE
182
183 -- History
184 -- MAR-10-95 Rudolf F. Reichenberger Created
185
186 -- Arguments
187 -- p_movement_id number
188 -- p_transaction_quantity number
189 -- p_primary_quantity number
190 -- p_transaction_uom varchar2
191 -- p_actual_cost number
192 -- p_transaction_date date
193 -- p_call_type varchar2
194
195 -- Example
196 -- mtl_movement_stat_pkg.upd_inv_movements ()
197
198 -- Notes
199
200 PROCEDURE upd_inv_movements (p_movement_id IN NUMBER,
201 p_transaction_quantity IN NUMBER,
202 p_primary_quantity IN NUMBER,
203 p_transaction_uom IN VARCHAR2,
204 p_actual_cost IN NUMBER,
205 p_transaction_date IN DATE,
206 p_call_type IN VARCHAR2)
207 IS
208
209 v_uom_code varchar2(3);
210 l_old_transaction_uom_code varchar2(3);
211 l_old_transaction_quantity number;
212 l_inventory_item_id number;
213 l_organization_id number;
214 l_currency_code varchar2(15);
215 l_document_unit_price number;
216 l_document_line_ext_value number;
217 l_weight_method varchar2(2);
218 l_unit_weight number;
219 l_total_weight number;
220 l_stat_adj_percent number;
221 l_stat_adj_amount number;
222 l_stat_ext_value number;
223 l_outside_unit_price number;
224 l_outside_ext_value number;
225
226 BEGIN
227
228
229 -- ** Record Check (exists movement_id ?) and lock Table **
230 -- ** 'mtl_movement_statistics' **
231
232 SELECT
233 movement_id,
234 transaction_uom_code,
235 transaction_quantity,
236 inventory_item_id,
237 organization_id,
238 currency_code,
239 document_unit_price,
240 document_line_ext_value,
241 weight_method,
242 unit_weight,
243 total_weight,
244 stat_adj_percent,
245 stat_adj_amount,
246 stat_ext_value,
247 outside_unit_price,
248 outside_ext_value
249 INTO
250 pseudo_movement_id,
251 l_old_transaction_uom_code,
252 l_old_transaction_quantity,
253 l_inventory_item_id,
254 l_organization_id,
255 l_currency_code,
256 l_document_unit_price,
257 l_document_line_ext_value,
258 l_weight_method,
259 l_unit_weight,
260 l_total_weight,
261 l_stat_adj_percent,
262 l_stat_adj_amount,
263 l_stat_ext_value,
264 l_outside_unit_price,
265 l_outside_ext_value
266 FROM mtl_movement_statistics
267 WHERE movement_id = p_movement_id
268 AND movement_status <> 'F' FOR UPDATE NOWAIT;
269
273 -- ** procedure. **
270 -- ** Call routine that takes care of any UOM or unit price changes **
271 -- ** This routine does no database updates but returns the amended **
272 -- ** values to be used to update the database later on in this **
274
275 uom_or_quantity_changes (
276 l_old_transaction_uom_code,
277 v_uom_code,
278 l_old_transaction_quantity,
279 p_transaction_quantity,
280 l_inventory_item_id,
281 l_organization_id,
282 l_currency_code,
283 l_document_unit_price,
284 l_document_line_ext_value,
285 l_weight_method,
286 l_unit_weight,
287 l_total_weight,
288 l_stat_adj_percent,
289 l_stat_adj_amount,
290 l_stat_ext_value,
291 l_outside_unit_price,
292 l_outside_ext_value);
293
294 -- ** UPDATE **
295
296 IF p_call_type = 'T' THEN
297
298 UPDATE mtl_movement_statistics
299 SET movement_status = 'O',
300 primary_quantity = p_primary_quantity,
301 transaction_quantity = ABS(p_transaction_quantity),
302 transaction_uom_code = p_transaction_uom,
303 transaction_date = p_transaction_date,
304 item_cost = p_actual_cost,
305 last_update_date = sysdate,
306 last_updated_by = user_id,
307 document_unit_price = l_document_unit_price,
308 document_line_ext_value = l_document_line_ext_value,
309 unit_weight = l_unit_weight,
310 total_weight = l_total_weight,
311 stat_ext_value = l_stat_ext_value,
312 outside_unit_price = l_outside_unit_price,
313 outside_ext_value = l_outside_ext_value
314 WHERE movement_id = p_movement_id;
315
316 ELSIF p_call_type = 'C' THEN
317
318 UPDATE mtl_movement_statistics
319 SET item_cost = p_actual_cost,
320 last_update_date = sysdate,
321 last_updated_by = user_id,
322 document_unit_price = l_document_unit_price,
323 document_line_ext_value = l_document_line_ext_value,
324 unit_weight = l_unit_weight,
325 total_weight = l_total_weight,
326 stat_ext_value = l_stat_ext_value,
327 outside_unit_price = l_outside_unit_price,
328 outside_ext_value = l_outside_ext_value
329 WHERE movement_id = p_movement_id;
330
331 END IF;
332
333 -- EXCEPTION
334 -- No exception handling required because all data entered are
335 -- validated by the Pro C program. Especially the call_type
336 -- is either 'T' or 'C' and nothing else.
337
338 END upd_inv_movements;
339
340 -- *******************************************************************
341 -- Procedure
342 -- upd_ins_rcv_movements
343
344 -- Purpose
345 -- This procedure is called from the receiving processor after the
346 -- processor generates the appropriate ids and before the final
347 -- commit; the procedure will update the shipment_header_id,
348 -- shipment_line_id, quantity, etc.. for adjustment entries, the
349 -- procedure will insert a corresponding adjusting movement record.
350
351 -- History
352 -- Apr-07-95 Rudolf F. Reichenberger Created
353
354 -- Arguments
355 -- p_movement_id number
356 -- p_parent_movement_id number
357 -- p_shipment_header_id number
358 -- p_shipment_line_id number
359 -- p_transaction_quantity number
360 -- p_transaction_uom_code varchar2
361 -- p_type varchar2
362
363 -- Example
364 -- mtl_movement_stat_pkg.upd_ins_rcv_movements ()
365
366 -- Notes
367
368 PROCEDURE upd_ins_rcv_movements (
369 p_movement_id IN NUMBER,
370 p_parent_movement_id IN NUMBER,
371 p_shipment_header_id IN NUMBER,
372 p_shipment_line_id IN NUMBER,
373 p_transaction_quantity IN NUMBER,
374 p_transaction_uom_code IN VARCHAR2,
375 p_type IN VARCHAR2,
376 p_transaction_date IN DATE)
377 IS
378 l_movement_id number;
379 v_uom_code varchar2(3);
380 l_old_transaction_uom_code varchar2(3);
381 l_old_transaction_quantity number;
382 l_inventory_item_id number;
383 l_organization_id number;
384 l_currency_code varchar2(15);
385 l_document_unit_price number;
386 l_document_line_ext_value number;
387 l_weight_method varchar2(2);
388 l_unit_weight number;
389 l_total_weight number;
390 l_stat_adj_percent number;
391 l_stat_adj_amount number;
392 l_stat_ext_value number;
393 l_outside_unit_price number;
394 l_outside_ext_value number;
395 l_new_movement_id number;
396 l_movement_type varchar2(15);
397
398 BEGIN
399
400 -- ** get the 3 character conversion code from **
401 -- ** the unit_of_measure passed by receiving **
402
403 v_uom_code := get_uom_code(p_transaction_uom_code);
404
405
406 IF NOT p_type = 'CORRECT' THEN
407 l_movement_id := p_movement_id;
408 ELSE
409 l_movement_id := p_parent_movement_id;
410 END IF;
411
412 -- ** Lock Table 'mtl_movement_statistics' **
413
414 SELECT
415 movement_id,
416 transaction_uom_code,
417 transaction_quantity,
418 inventory_item_id,
419 organization_id,
420 currency_code,
421 document_unit_price,
422 document_line_ext_value,
423 weight_method,
424 unit_weight,
428 stat_ext_value,
425 total_weight,
426 stat_adj_percent,
427 stat_adj_amount,
429 outside_unit_price,
430 outside_ext_value
431 INTO
432 pseudo_movement_id,
433 l_old_transaction_uom_code,
434 l_old_transaction_quantity,
435 l_inventory_item_id,
436 l_organization_id,
437 l_currency_code,
438 l_document_unit_price,
439 l_document_line_ext_value,
440 l_weight_method,
441 l_unit_weight,
442 l_total_weight,
443 l_stat_adj_percent,
444 l_stat_adj_amount,
445 l_stat_ext_value,
446 l_outside_unit_price,
447 l_outside_ext_value
448 FROM mtl_movement_statistics
449 WHERE movement_id = l_movement_id
450 FOR UPDATE NOWAIT;
451
452 -- ** Call routine that takes care of any UOM or unit price changes **
453 -- ** This routine does no database updates but returns the amended **
454 -- ** values to be used to update the database later on in this **
455 -- ** procedure. **
456
457 uom_or_quantity_changes (
458 l_old_transaction_uom_code,
459 v_uom_code,
460 l_old_transaction_quantity,
461 p_transaction_quantity,
462 l_inventory_item_id,
463 l_organization_id,
464 l_currency_code,
465 l_document_unit_price,
466 l_document_line_ext_value,
467 l_weight_method,
468 l_unit_weight,
469 l_total_weight,
470 l_stat_adj_percent,
471 l_stat_adj_amount,
472 l_stat_ext_value,
473 l_outside_unit_price,
474 l_outside_ext_value);
475
476 -- ** Update for not adjustments if type is not **
477 -- ** an Arrivial Adjustment ('AA') **
478
479 IF NOT p_type = 'CORRECT' THEN
480
481 -- ** UPDATE **
482 UPDATE mtl_movement_statistics
483 SET shipment_header_id = p_shipment_header_id,
484 shipment_line_id = p_shipment_line_id,
485 transaction_quantity = p_transaction_quantity,
486 transaction_uom_code = v_uom_code,
487 last_update_date = sysdate,
488 last_updated_by = user_id,
489 movement_status = 'O',
490 document_unit_price = l_document_unit_price,
491 document_line_ext_value = l_document_line_ext_value,
492 unit_weight = l_unit_weight,
493 total_weight = l_total_weight,
494 stat_ext_value = l_stat_ext_value,
495 outside_unit_price = l_outside_unit_price,
496 outside_ext_value = l_outside_ext_value
497 WHERE movement_id = p_movement_id;
498
499 -- ** INSERT **
500 ELSE
501
502 select mtl_movement_statistics_s.NEXTVAL
503 into l_new_movement_id
504 from sys.dual;
505
506 INSERT INTO mtl_movement_statistics (
507 movement_id, organization_id,
508 entity_org_id, movement_type,
509 movement_status, transaction_date,
510 last_update_date, last_updated_by,
511 creation_date, created_by,
512 last_update_login, document_source_type,
513 creation_method, document_reference,
514 document_line_reference, document_unit_price,
515 document_line_ext_value,
516 vendor_name, vendor_number,
517 vendor_site, po_header_id,
518 po_line_id, po_line_location_id,
519 shipment_header_id, shipment_line_id,
520 vendor_id, vendor_site_id,
521 parent_movement_id,
522 inventory_item_id, item_description,
523 item_cost, transaction_quantity,
524 transaction_uom_code, outside_code,
525 outside_ext_value, outside_unit_price,
526 currency_code,
527 category_id,
528 weight_method, unit_weight,
529 total_weight, transaction_nature,
530 delivery_terms, transport_mode,
531 dispatch_territory_code,
532 destination_territory_code,
533 origin_territory_code, area,
534 port, stat_type, comments,
535 stat_adj_amount, stat_adj_percent,
536 stat_ext_value
537 )
538 SELECT
539 l_new_movement_id,
540 organization_id, entity_org_id,
541 decode(movement_status, 'O', 'A', 'AA'),
542 'O', p_transaction_date,
543 sysdate, user_id,
544 sysdate, created_by,
545 last_update_login, document_source_type,
546 creation_method, document_reference,
547 document_line_reference, L_DOCUMENT_UNIT_PRICE,
548 L_DOCUMENT_LINE_EXT_VALUE,
549 vendor_name, vendor_number,
550 vendor_site, po_header_id,
551 po_line_id, po_line_location_id,
552 P_SHIPMENT_HEADER_ID, P_SHIPMENT_LINE_ID,
553 vendor_id, vendor_site_id,
554 P_PARENT_MOVEMENT_ID,
555 inventory_item_id, item_description,
556 item_cost, P_TRANSACTION_QUANTITY,
557 transaction_uom_code, outside_code,
558 L_OUTSIDE_EXT_VALUE,
559 L_OUTSIDE_UNIT_PRICE,
560 currency_code,
561 category_id,
562 weight_method, L_UNIT_WEIGHT,
563 L_TOTAL_WEIGHT,
564 transaction_nature,
565 delivery_terms, transport_mode,
566 dispatch_territory_code,
567 destination_territory_code,
568 origin_territory_code, area,
569 port, stat_type, comments,
570 stat_adj_amount, stat_adj_percent,
571 decode(stat_adj_percent, NULL, decode(stat_adj_amount,
572 NULL, NULL, stat_ext_value), stat_ext_value)
573 FROM mtl_movement_statistics
574 WHERE movement_id = p_parent_movement_id;
575
576 --fix bug 109662
577 select movement_type
578 into l_movement_type
579 from mtl_movement_statistics
580 where movement_id = p_parent_movement_id;
581
582
583
584 IF (p_type = 'CORRECT') then
585 if (l_movement_type = 'A') then
586 /* correction to receipt */
587 update mtl_movement_statistics
588 set movement_type = 'AA'
589 where movement_id = l_new_movement_id;
590 elsif (l_movement_type = 'D') then
591
592 update mtl_movement_statistics
593 set movement_type = 'DA',
594 transaction_quantity = (-1) * p_transaction_quantity
595 where movement_id = l_new_movement_id;
596 end if;
597 end if;
598
599 END IF;
600 END upd_ins_rcv_movements;
601
602 -- PRIVATE FUNCTION
603 -- PRIVATE VARIABLES
604 -- v_uom_code
605 -- *******************************************************************
606 -- FUNCTION
607 -- get_uom_code
608
609 -- Purpose
610 -- This function is called from the
611 -- upd_ins_rcv_movements procedure to get the 3 character
612 -- conversion code from the unit_of_measure passed by receiving
613
614 -- History
615 -- MAY-10-95 Rudolf F. Reichenberger Created
616
617 -- Arguments
618 -- p_transaction_uom_code varchar2
619
620 -- Example
621 -- get_uom_code ()
622
623 FUNCTION get_uom_code (p_transaction_uom_code VARCHAR2)
624 RETURN VARCHAR2 IS
625 v_uom_code VARCHAR2(3);
626 BEGIN
627 SELECT uom_code INTO v_uom_code
628 FROM mtl_units_of_measure
629 WHERE unit_of_measure = p_transaction_uom_code;
630 RETURN v_uom_code;
631 END get_uom_code;
632
633 -- *******************************************************************
634
635 -- ** initialization part of the package ** --
636
637 -- ** FUNCTION FND_GLOBAL.USER_ID Returns the **
638 -- ** user_id for the last_updated_by column **
639
640 BEGIN
641 user_id := FND_GLOBAL.USER_ID;
642 -- *******************************************************************
643 END mtl_movement_stat_pkg;