DBA Data[Home] [Help]

PACKAGE BODY: APPS.MTL_MOVEMENT_STAT_PKG

Source


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;