4 ----------------------------------------
1 package body MTL_MOVEMENT_RPT_PKG as
2 /* $Header: INVMVTPB.pls 115.3 99/07/16 10:59:14 porting ship $ */
3 --
5 -- Exchange Rate Calculation Function --
6 ----------------------------------------
7 function EXCHANGE_RATE_CALC
8 (C_CONVERSION_OPTION varchar2,
9 C_CONVERSION_TYPE varchar2,
10 C_SET_OF_BOOKS_ID number,
11 C_END_DATE date,
12 C_CURRENCY_CODE varchar2,
13 l_currency_code varchar2,
14 l_transaction_date date,
15 l_invoice_id number,
16 l_document_source_type varchar2,
17 l_movement_type varchar2)
18 return number
19 is
20 --
21 l_exchange_rate number;
22 --
23 begin
24 -- If movement currency code is the same
25 -- as the functionnal currency code
26 if l_currency_code = C_CURRENCY_CODE
27 then
28 l_exchange_rate := 1;
29 else
30 -- If there is no invoice_id
31 if l_invoice_id is null
32 then
33 -- If CONVERSION_OPTION is Daily
34 if C_CONVERSION_OPTION = 'D'
35 then
36 begin
40 l_transaction_date,
37 l_exchange_rate := gl_currency_api.get_rate(
38 c_set_of_books_id,
39 l_currency_code,
41 c_conversion_type);
42
43 exception when gl_currency_api.no_rate then
44 l_exchange_rate := null;
45 end;
46 -- If CONVERSION_OPTION is Last day of the period
47 elsif C_CONVERSION_OPTION = 'L'
48 then
49 begin
50 l_exchange_rate := gl_currency_api.get_rate(
51 c_set_of_books_id,
52 l_currency_code,
53 c_end_date,
54 c_conversion_type);
55
56 exception when gl_currency_api.no_rate then
57 l_exchange_rate := null;
58 end;
59 else l_exchange_rate := null;
60 end if;
61 -- If DOCUMENT_SOURCE_TYPE is Purchase Order
62 -- or (DOCUMENT_SOURCE_TYPE is Inventory
63 -- and MOVEMENT_TYPE is Arrival or Arrival Adjustment)
64 elsif l_document_source_type = 'PO'
65 or (l_document_source_type = 'INV'
66 and l_movement_type in ('A','AA'))
67 then
68 begin
69 select exchange_rate into l_exchange_rate
70 from ap_invoices
71 where invoice_id = l_invoice_id;
72 exception when no_data_found
73 then l_exchange_rate := null;
74 end;
75 -- If DOCUMENT_SOURCE_TYPE is Sales Order
76 -- or DOCUMENT_SOURCE_TYPE is Return Merchandise Adjustment
77 -- or (DOCUMENT_SOURCE_TYPE is Inventory
78 -- and MOVEMENT_TYPE is Dispatch or Dispatch Adjustment)
79 elsif l_document_source_type = 'SO'
80 or l_document_source_type = 'RMA'
81 or (l_document_source_type = 'INV'
82 and l_movement_type in ('D','DA'))
83 then
84 begin
85 select exchange_rate into l_exchange_rate
86 from ra_customer_trx
87 where customer_trx_id = l_invoice_id;
88 exception when no_data_found
89 then l_exchange_rate := null;
90 end;
91 else l_exchange_rate := null;
92 end if;
93 end if;
94 return(l_exchange_rate);
95 --
96 end EXCHANGE_RATE_CALC;
97 --
98 -----------------------------------------------------------------------------
99 ------------------------------------------
100 -- Conversion Date Calculation Function --
101 ------------------------------------------
102 function CONVERSION_DATE_CALC
103 (C_END_DATE date,
104 C_CONVERSION_OPTION varchar2,
105 C_CURRENCY_CODE varchar2,
106 l_currency_code varchar2,
107 l_transaction_date date,
108 l_invoice_id number,
109 l_document_source_type varchar2,
110 l_movement_type varchar2)
111 return date
112 is
113 --
114 l_exchange_date date;
115 --
116 begin
117 -- If movement currency code is the same
118 -- as the functionnal currency code
119 if l_currency_code = C_CURRENCY_CODE
120 then
121 l_exchange_date := null;
122 else
123 -- If there is no invoice_id
124 if l_invoice_id is null
125 then
126 -- If CONVERSION_OPTION is Daily
127 if C_CONVERSION_OPTION = 'D'
128 then l_exchange_date := l_transaction_date;
129 -- If CONVERSION_OPTION is Last day of the period
130 elsif C_CONVERSION_OPTION = 'L'
131 then l_exchange_date := C_END_DATE;
132 else l_exchange_date := null;
133 end if;
134 -- If DOCUMENT_SOURCE_TYPE is Purchase Order
135 -- or (DOCUMENT_SOURCE_TYPE is Inventory
136 -- and MOVEMENT_TYPE is Arrival or Arrival Adjustment)
137 elsif l_document_source_type = 'PO'
138 or (l_document_source_type = 'INV'
139 and l_movement_type in ('A','AA'))
140 then
141 begin
142 select exchange_date into l_exchange_date
143 from ap_invoices
144 where invoice_id = l_invoice_id;
145 exception when no_data_found
146 then l_exchange_date := null;
147 end;
148 -- If DOCUMENT_SOURCE_TYPE is Sales Order
149 -- or DOCUMENT_SOURCE_TYPE is Return Merchandise Adjustment
150 -- or (DOCUMENT_SOURCE_TYPE is Inventory
151 -- and MOVEMENT_TYPE is Dispatch or Dispatch Adjustment)
152 elsif l_document_source_type = 'SO'
153 or l_document_source_type = 'RMA'
154 or (l_document_source_type = 'INV'
155 and l_movement_type in ('D','DA'))
156 then
157 begin
158 select exchange_date into l_exchange_date
159 from ra_customer_trx
160 where customer_trx_id = l_invoice_id;
161 exception when no_data_found
162 then l_exchange_date := null;
163 end;
164 else l_exchange_date := null;
165 end if;
166 end if;
167 return(l_exchange_date);
168 --
169 end CONVERSION_DATE_CALC;
170 --
171 -----------------------------------------------------------------------------
172 --------------------------------------
173 -- Unit Weight Calculation Function --
174 --------------------------------------
175 function UNIT_WEIGHT_CALC
176 (l_inventory_item_id number,
177 l_organization_id number,
178 P_LEGAL_ENTITY_ID number)
179 return number
180 is
181 --
182 l_conversion_rate number;
183 --
184 begin
185 if l_inventory_item_id is not null
186 then
187 begin
188 select
189 conversion_rate into l_conversion_rate
190 from
191 mtl_uom_conversions_view muc,
192 mtl_movement_parameters mmp
193 where
194 muc.inventory_item_id = l_inventory_item_id
195 and muc.organization_id = l_organization_id
196 and muc.uom_code = mmp.weight_uom_code
200 end;
197 and mmp.entity_org_id = P_LEGAL_ENTITY_ID;
198 exception when no_data_found
199 then l_conversion_rate := null;
201 else l_conversion_rate := null;
202 end if;
203 return (l_conversion_rate);
204 --
205 end UNIT_WEIGHT_CALC;
209 -- Weight Calculation Function --
206 --
207 -----------------------------------------------------------------------------
208 ---------------------------------
210 ---------------------------------
211 function WEIGHT_CALC
212 (l_total_weight number,
213 l_inventory_item_id number,
214 l_organization_id number,
215 l_transaction_quantity number,
216 l_transaction_uom_code varchar2,
217 P_LEGAL_ENTITY_ID number,
218 P_FORMAT_TYPE varchar2)
219 return number
220 is
221 --
222 l_conversion_rate number;
223 l_weight number;
224 --
225 begin
226 ----------------------------------------------
227 -- Conversion from movement unit of measure --
228 -- to the base unit --
229 ----------------------------------------------
230 begin
231 select
232 conversion_rate into l_conversion_rate
233 from
234 mtl_uom_conversions_view
235 where
236 inventory_item_id = l_inventory_item_id
237 and organization_id = l_organization_id
238 and uom_code = l_transaction_uom_code;
239 exception when no_data_found
240 then l_conversion_rate := 1;
241 end;
242 l_weight := l_transaction_quantity * l_conversion_rate;
243 --
244 ------------------------------------------
245 -- Conversion from base unit of measure --
246 -- to legal_entity unit --
247 ------------------------------------------
248 begin
249 select
250 conversion_rate into l_conversion_rate
251 from
252 mtl_uom_conversions_view muc,
253 mtl_movement_parameters mmp
254 where
255 muc.inventory_item_id = l_inventory_item_id
256 and muc.organization_id = l_organization_id
257 and muc.uom_code = mmp.weight_uom_code
258 and mmp.entity_org_id = P_LEGAL_ENTITY_ID;
259 exception when no_data_found
260 then l_conversion_rate := 1;
261 end;
262 l_weight := l_weight / l_conversion_rate;
263 --
264 -- Weight rounded up for all the EEC countries
265 -- except Portugal who need 3 decimals
266 if P_FORMAT_TYPE = 'PT'
267 then l_weight := round(l_weight,3);
268 else l_weight := ceil(l_weight);
269 end if;
270 --
271 return(l_weight);
272 --
273 end WEIGHT_CALC;
274 --
275 -----------------------------------------------------------------------------
276 --------------------------------------
277 -- Date Report Calculation Function --
278 --------------------------------------
279 function REPORT_DATE_CALC
280 (l_invoice_date_reference date,
281 l_transaction_date date)
282 return date
283 is
284 --
285 l_report_date date;
286 --
287 begin
288 if l_invoice_date_reference is not null
289 then
290 if l_invoice_date_reference
291 between l_transaction_date and add_months(l_transaction_date,1)
292 then l_report_date := l_invoice_date_reference;
293 else l_report_date := l_transaction_date;
294 end if;
295 else l_report_date :=
296 To_date(To_char(Add_months(l_transaction_date, 1), 'YYYY/MM')
297 || '/15', 'YYYY/MM/DD');
298 end if;
299 return(l_report_date);
300 --
301 end REPORT_DATE_CALC;
302 --
303 -----------------------------------------------------------------------------
304 ------------------------------------------
305 -- Update mtl_movement_statistics table --
306 -- executed in Before Report Trigger --
307 ------------------------------------------
308 --
309 procedure BEFORE_REPORT_UPDATES
310 (P_USER_ID in number,
311 P_CONC_LOGIN_ID in number,
312 P_PERIOD_NAME in varchar2,
313 P_CONC_REQUEST_ID in number,
314 P_CONC_APPLICATION_ID in number,
315 P_CONC_PROGRAM_ID in number,
316 P_REPORT_OPTION in varchar2,
317 P_MOVEMENT_TYPE in varchar2,
318 P_LEGAL_ENTITY_ID in number,
319 P_REPORT_REFERENCE in number,
320 P_FORMAT_TYPE in varchar2,
321 C_CONVERSION_TYPE in varchar2,
322 C_CONVERSION_OPTION in varchar2,
323 C_SET_OF_BOOKS_ID in number,
324 C_START_DATE in date,
325 C_END_DATE in date,
326 C_CURRENCY_CODE in varchar2)
327 is
328 --
329 begin
330 --
331 ------------------------------------------------------
332 -- if REPORT OPTION is not Nullify Official/Summary --
333 ------------------------------------------------------
334 if P_REPORT_OPTION <> 'NO/S'
335 then
336 begin
337 -------------------------------------------------------
338 -- Update of INVOICE_DATE_REFERENCE --
339 -- in MTL_MOVEMENT_STATISTICS table --
340 -- if DOCUMENT_SOURCE_TYPE is not Miscellaneous --
341 -- (Required for Where Clause of select and updates) --
342 -------------------------------------------------------
343 update mtl_movement_statistics mms
344 set invoice_date_reference = (select invoice_date
345 from ap_invoices
346 where invoice_id = mms.invoice_id)
347 where mms.movement_type = P_MOVEMENT_TYPE
348 and mms.entity_org_id = P_LEGAL_ENTITY_ID
349 and mms.movement_status = 'O'
350 and mms.document_source_type = 'PO'
351 and mms.invoice_id is not null;
352 --
353 update mtl_movement_statistics mms
354 set invoice_date_reference = (select trx_date
355 from ra_customer_trx
356 where customer_trx_id = mms.invoice_id)
357 where mms.movement_type = P_MOVEMENT_TYPE
358 and mms.entity_org_id = P_LEGAL_ENTITY_ID
359 and mms.movement_status = 'O'
360 and mms.document_source_type = 'SO'
364 -- Update in MTL_MOVEMENT_STATISTICS table --
361 and mms.invoice_id is not null;
362 --
363 ---------------------------------------------
365 -- for the movements of the period --
366 ---------------------------------------------
367 update mtl_movement_statistics mms
368 set
369 last_update_date = sysdate,
370 last_updated_by = P_USER_ID,
371 last_update_login = P_CONC_LOGIN_ID,
372 period_name = P_PERIOD_NAME,
373 report_reference = P_REPORT_REFERENCE,
374 report_date = MTL_MOVEMENT_RPT_PKG.REPORT_DATE_CALC
375 (invoice_date_reference,
376 transaction_date)
377 where
378 mms.movement_type = P_MOVEMENT_TYPE
379 and mms.entity_org_id = P_LEGAL_ENTITY_ID
380 and ((decode(P_MOVEMENT_TYPE,
381 'D', mms.destination_territory_code,
382 'DA', mms.destination_territory_code,
383 'A', mms.dispatch_territory_code,
384 'AA', mms.dispatch_territory_code)
385 in (select territory_code from fnd_territories_vl)
386 and decode(P_MOVEMENT_TYPE,
387 'D',mms.dispatch_territory_code,
388 'DA', mms.dispatch_territory_code,
389 'A', mms.destination_territory_code,
390 'AA', mms.destination_territory_code) = P_FORMAT_TYPE
391 and decode(P_MOVEMENT_TYPE,
392 'D',mms.destination_territory_code,
393 'DA', mms.destination_territory_code,
394 'A', mms.dispatch_territory_code,
395 'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
396 or P_FORMAT_TYPE = 'GEN')
397 and (mms.movement_type in ('AA','DA')
398 or
399 (mms.movement_type not in ('AA','DA')
400 and ((mms.invoice_id is null and mms.invoice_reference is null
401 and add_months(mms.transaction_date,1)
402 between C_START_DATE and C_END_DATE)
403 or
404 ((mms.invoice_id is not null or mms.invoice_reference is not null)
405 and (mms.invoice_date_reference
406 between transaction_date
407 and add_months(mms.transaction_date,1)
408 or
409 mms.transaction_date between C_START_DATE and C_END_DATE)))))
410 and mms.movement_status = 'O';
411 --
412 -- Currency Conversion
413 update mtl_movement_statistics mms
414 set
415 currency_conversion_rate = MTL_MOVEMENT_RPT_PKG.EXCHANGE_RATE_CALC
416 (C_CONVERSION_OPTION,
417 C_CONVERSION_TYPE,
418 C_SET_OF_BOOKS_ID,
419 C_END_DATE,
420 C_CURRENCY_CODE,
421 currency_code,
422 transaction_date,
423 invoice_id,
424 document_source_type,
425 movement_type),
426 currency_conversion_type = C_CONVERSION_TYPE,
427 currency_conversion_date = MTL_MOVEMENT_RPT_PKG.CONVERSION_DATE_CALC
428 (C_END_DATE,
429 C_CONVERSION_OPTION,
430 C_CURRENCY_CODE,
431 currency_code,
432 transaction_date,
433 invoice_id,
434 document_source_type,
435 movement_type)
436 where
437 mms.movement_type = P_MOVEMENT_TYPE
438 and mms.entity_org_id = P_LEGAL_ENTITY_ID
439 and ((decode(P_MOVEMENT_TYPE,
440 'D', mms.destination_territory_code,
441 'DA', mms.destination_territory_code,
442 'A', mms.dispatch_territory_code,
443 'AA', mms.dispatch_territory_code)
444 in (select territory_code from fnd_territories_vl)
445 and decode(P_MOVEMENT_TYPE,
446 'D',mms.dispatch_territory_code,
447 'DA', mms.dispatch_territory_code,
448 'A', mms.destination_territory_code,
449 'AA', mms.destination_territory_code) = P_FORMAT_TYPE
450 and decode(P_MOVEMENT_TYPE,
451 'D',mms.destination_territory_code,
452 'DA', mms.destination_territory_code,
453 'A', mms.dispatch_territory_code,
454 'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
455 or P_FORMAT_TYPE = 'GEN')
456 and (mms.movement_type in ('AA','DA')
457 or
458 (mms.movement_type not in ('AA','DA')
459 and ((mms.invoice_id is null and mms.invoice_reference is null
460 and add_months(mms.transaction_date,1)
461 between C_START_DATE and C_END_DATE)
462 or
463 ((mms.invoice_id is not null or mms.invoice_reference is not null)
464 and (mms.invoice_date_reference
465 between transaction_date
466 and add_months(mms.transaction_date,1)
467 or
468 mms.transaction_date between C_START_DATE and C_END_DATE)))))
469 and mms.movement_status = 'O';
470 --
471 -- If WEIGHT_METHOD is System
472 update mtl_movement_statistics mms
473 set
474 unit_weight = MTL_MOVEMENT_RPT_PKG.UNIT_WEIGHT_CALC
475 (inventory_item_id,
476 organization_id,
477 P_LEGAL_ENTITY_ID),
478 total_weight = MTL_MOVEMENT_RPT_PKG.WEIGHT_CALC
479 (total_weight,
480 inventory_item_id,
481 organization_id,
482 transaction_quantity,
483 transaction_uom_code,
484 P_LEGAL_ENTITY_ID,
485 P_FORMAT_TYPE)
486 where
487 mms.movement_type = P_MOVEMENT_TYPE
488 and mms.entity_org_id = P_LEGAL_ENTITY_ID
489 and ((decode(P_MOVEMENT_TYPE,
490 'D', mms.destination_territory_code,
494 in (select territory_code from fnd_territories_vl)
491 'DA', mms.destination_territory_code,
492 'A', mms.dispatch_territory_code,
493 'AA', mms.dispatch_territory_code)
495 and decode(P_MOVEMENT_TYPE,
496 'D',mms.dispatch_territory_code,
497 'DA', mms.dispatch_territory_code,
498 'A', mms.destination_territory_code,
499 'AA', mms.destination_territory_code) = P_FORMAT_TYPE
500 and decode(P_MOVEMENT_TYPE,
501 'D',mms.destination_territory_code,
502 'DA', mms.destination_territory_code,
503 'A', mms.dispatch_territory_code,
504 'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
505 or P_FORMAT_TYPE = 'GEN')
506 and (mms.movement_type in ('AA','DA')
507 or
508 (mms.movement_type not in ('AA','DA')
509 and ((mms.invoice_id is null and mms.invoice_reference is null
510 and add_months(mms.transaction_date,1)
511 between C_START_DATE and C_END_DATE)
512 or
513 ((mms.invoice_id is not null or mms.invoice_reference is not null)
514 and (mms.invoice_date_reference
515 between transaction_date
516 and add_months(mms.transaction_date,1)
517 or
518 mms.transaction_date between C_START_DATE and C_END_DATE)))))
519 and mms.movement_status = 'O'
520 and weight_method = 'S';
521 end;
522 end if;
523 --
524 end BEFORE_REPORT_UPDATES;
525 --
526 -----------------------------------------------------------------------------
527 ------------------------------------------
528 -- Update mtl_movement_statistics table --
529 -- and mtl_movement_parameters table --
530 -- executed in After Report Trigger --
531 ------------------------------------------
532 --
533 procedure AFTER_REPORT_UPDATES
534 (P_USER_ID in number,
535 P_CONC_LOGIN_ID in number,
536 P_PERIOD_NAME in varchar2,
537 P_REPORT_OPTION in varchar2,
538 P_MOVEMENT_TYPE in varchar,
539 P_LEGAL_ENTITY_ID in number,
540 P_REPORT_REFERENCE in number,
541 P_FORMAT_TYPE in varchar2,
542 C_START_DATE in date,
543 C_END_DATE in date)
544 is
545 begin
546 --
547 ------------------------------------------
548 -- if REPORT OPTION is Official/Summary --
549 --------------------------------------------
550 if P_REPORT_OPTION = 'O/S'
551 then
552 begin
553 ------------------------------------------
554 -- Update mtl_movement_parameters table --
555 ------------------------------------------
556 update mtl_movement_parameters
557 set
558 last_update_date = sysdate,
559 last_updated_by = P_USER_ID,
560 last_update_login = P_CONC_LOGIN_ID
561 where entity_org_id = P_LEGAL_ENTITY_ID;
562 --
563 -- If MOVEMENT_TYPE is Arrival
564 if P_MOVEMENT_TYPE = 'A'
565 then
566 update mtl_movement_parameters
567 set
568 last_arrival_id = P_REPORT_REFERENCE,
569 last_arrival_period = P_PERIOD_NAME
570 where entity_org_id = P_LEGAL_ENTITY_ID;
571 end if;
572 --
573 -- If MOVEMENT_TYPE is Arrival Adjustment
574 if P_MOVEMENT_TYPE = 'AA'
575 then
576 update mtl_movement_parameters
577 set
578 last_arrival_adj_id = P_REPORT_REFERENCE,
579 last_arrival_adj_period = P_PERIOD_NAME
580 where entity_org_id = P_LEGAL_ENTITY_ID;
581 end if;
582 --
583 -- If MOVEMENT_TYPE is Dispatch
584 if P_MOVEMENT_TYPE = 'D'
585 then
586 update mtl_movement_parameters
587 set
588 last_dispatch_id = P_REPORT_REFERENCE,
589 last_dispatch_period = P_PERIOD_NAME
590 where entity_org_id = P_LEGAL_ENTITY_ID;
591 end if;
592 --
593 -- If MOVEMENT_TYPE is Dispatch Adjustment
594 if P_MOVEMENT_TYPE = 'DA'
595 then
596 update mtl_movement_parameters
597 set
598 last_dispatch_adj_id = P_REPORT_REFERENCE,
599 last_dispatch_adj_period = P_PERIOD_NAME
600 where entity_org_id = P_LEGAL_ENTITY_ID;
601 end if;
602 --
603 ------------------------------------------
604 -- Update mtl_movement_statistics table --
605 -- for Freeze --
606 ------------------------------------------
607 update mtl_movement_statistics mms
608 set
609 movement_status = 'F'
610 where
611 mms.movement_type = P_MOVEMENT_TYPE
612 and mms.entity_org_id = P_LEGAL_ENTITY_ID
613 and ((decode(P_MOVEMENT_TYPE,
614 'D', mms.destination_territory_code,
615 'DA', mms.destination_territory_code,
616 'A', mms.dispatch_territory_code,
617 'AA', mms.dispatch_territory_code)
618 in (select territory_code from fnd_territories_vl)
619 and decode(P_MOVEMENT_TYPE,
620 'D',mms.dispatch_territory_code,
621 'DA', mms.dispatch_territory_code,
622 'A', mms.destination_territory_code,
623 'AA', mms.destination_territory_code) = P_FORMAT_TYPE
624 and decode(P_MOVEMENT_TYPE,
625 'D',mms.destination_territory_code,
626 'DA', mms.destination_territory_code,
627 'A', mms.dispatch_territory_code,
628 'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
629 or P_FORMAT_TYPE = 'GEN')
630 and (mms.movement_type in ('AA','DA')
631 or
632 (mms.movement_type not in ('AA','DA')
636 or
633 and ((mms.invoice_id is null and mms.invoice_reference is null
634 and add_months(mms.transaction_date,1)
635 between C_START_DATE and C_END_DATE)
637 ((mms.invoice_id is not null or mms.invoice_reference is not null)
638 and (mms.invoice_date_reference
639 between transaction_date
640 and add_months(mms.transaction_date,1)
641 or
642 mms.transaction_date between C_START_DATE and C_END_DATE)))))
643 and mms.movement_status = 'O';
644 end;
645 end if;
646 --
647 --------------------------------------------------
648 -- if REPORT OPTION is Nullify Official/Summary --
649 --------------------------------------------------
650 if P_REPORT_OPTION = 'NO/S'
651 then
652 begin
653 ------------------------------------------
654 -- Update mtl_movement_statistics table --
655 -- for Open --
656 ------------------------------------------
657 update mtl_movement_statistics mms
658 set
659 movement_status = 'O',
660 last_update_date = sysdate,
661 last_updated_by = P_USER_ID,
662 last_update_login = P_CONC_LOGIN_ID,
663 period_name = null,
664 report_reference = null,
665 report_date = null
666 where
667 mms.movement_type = P_MOVEMENT_TYPE
668 and mms.entity_org_id = P_LEGAL_ENTITY_ID
669 and ((decode(P_MOVEMENT_TYPE,
670 'D', mms.destination_territory_code,
671 'DA', mms.destination_territory_code,
672 'A', mms.dispatch_territory_code,
673 'AA', mms.dispatch_territory_code)
674 in (select territory_code from fnd_territories_vl)
675 and decode(P_MOVEMENT_TYPE,
676 'D',mms.dispatch_territory_code,
677 'DA', mms.dispatch_territory_code,
678 'A', mms.destination_territory_code,
679 'AA', mms.destination_territory_code) = P_FORMAT_TYPE
680 and decode(P_MOVEMENT_TYPE,
681 'D',mms.destination_territory_code,
682 'DA', mms.destination_territory_code,
683 'A', mms.dispatch_territory_code,
684 'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
685 or P_FORMAT_TYPE = 'GEN')
686 and (mms.movement_type in ('AA','DA')
687 or
688 (mms.movement_type not in ('AA','DA')
689 and ((mms.invoice_id is null and mms.invoice_reference is null
690 and add_months(mms.transaction_date,1)
691 between C_START_DATE and C_END_DATE)
692 or
693 ((mms.invoice_id is not null or mms.invoice_reference is not null)
694 and (mms.invoice_date_reference
695 between transaction_date
696 and add_months(mms.transaction_date,1)
697 or
698 mms.transaction_date between C_START_DATE and C_END_DATE)))))
699 and mms.movement_status = 'F';
700 --
701 -- If DOCUMENT_SOURCE_TYPE is Miscelaneous
702 update mtl_movement_statistics mms
703 set
704 currency_conversion_rate = null,
705 currency_conversion_type = null,
706 currency_conversion_date = null
707 where
708 mms.movement_type = P_MOVEMENT_TYPE
709 and mms.entity_org_id = P_LEGAL_ENTITY_ID
710 and ((decode(P_MOVEMENT_TYPE,
711 'D', mms.destination_territory_code,
712 'DA', mms.destination_territory_code,
713 'A', mms.dispatch_territory_code,
714 'AA', mms.dispatch_territory_code)
715 in (select territory_code from fnd_territories_vl)
716 and decode(P_MOVEMENT_TYPE,
717 'D',mms.dispatch_territory_code,
718 'DA', mms.dispatch_territory_code,
719 'A', mms.destination_territory_code,
720 'AA', mms.destination_territory_code) = P_FORMAT_TYPE
721 and decode(P_MOVEMENT_TYPE,
722 'D',mms.destination_territory_code,
723 'DA', mms.destination_territory_code,
724 'A', mms.dispatch_territory_code,
725 'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
726 or P_FORMAT_TYPE = 'GEN')
727 and (mms.movement_type in ('AA','DA')
728 or
729 (mms.movement_type not in ('AA','DA')
730 and ((mms.invoice_id is null and mms.invoice_reference is null
731 and add_months(mms.transaction_date,1)
732 between C_START_DATE and C_END_DATE)
733 or
734 ((mms.invoice_id is not null or mms.invoice_reference is not null)
735 and (mms.invoice_date_reference
736 between transaction_date
737 and add_months(mms.transaction_date,1)
738 or
739 mms.transaction_date between C_START_DATE and C_END_DATE)))))
740 and mms.movement_status = 'F'
741 and mms.document_source_type <> 'MISC';
742 --
743 -- If WEIGHT_METHOD is not Manual
744 update mtl_movement_statistics mms
745 set
746 unit_weight = null,
747 total_weight = null
748 where
749 mms.movement_type = P_MOVEMENT_TYPE
750 and mms.entity_org_id = P_LEGAL_ENTITY_ID
751 and ((decode(P_MOVEMENT_TYPE,
752 'D', mms.destination_territory_code,
753 'DA', mms.destination_territory_code,
754 'A', mms.dispatch_territory_code,
755 'AA', mms.dispatch_territory_code)
756 in (select territory_code from fnd_territories_vl)
757 and decode(P_MOVEMENT_TYPE,
758 'D',mms.dispatch_territory_code,
759 'DA', mms.dispatch_territory_code,
763 'D',mms.destination_territory_code,
760 'A', mms.destination_territory_code,
761 'AA', mms.destination_territory_code) = P_FORMAT_TYPE
762 and decode(P_MOVEMENT_TYPE,
764 'DA', mms.destination_territory_code,
765 'A', mms.dispatch_territory_code,
766 'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
767 or P_FORMAT_TYPE = 'GEN')
768 and (mms.movement_type in ('AA','DA')
769 or
770 (mms.movement_type not in ('AA','DA')
771 and ((mms.invoice_id is null and mms.invoice_reference is null
772 and add_months(mms.transaction_date,1)
773 between C_START_DATE and C_END_DATE)
774 or
775 ((mms.invoice_id is not null or mms.invoice_reference is not null)
776 and (mms.invoice_date_reference
777 between transaction_date
778 and add_months(mms.transaction_date,1)
779 or
780 mms.transaction_date between C_START_DATE and C_END_DATE)))))
781 and mms.movement_status = 'F'
782 and mms.weight_method <> 'M';
783 end;
784 end if;
785 --
786 end AFTER_REPORT_UPDATES;
787 --
788 -----------------------------------------------------------------------------
789 --
790 end MTL_MOVEMENT_RPT_PKG;
[Home] [Help]