[Home] [Help]
PACKAGE BODY: APPS.INV_RSV_SYNCH
Source
1 PACKAGE BODY INV_RSV_SYNCH AS
2 /* $Header: INVRSV7B.pls 120.1 2005/06/17 17:28:15 appldev $ */
3
4 -- Global constant holding package name
5 g_pkg_name constant varchar2(50) := 'INV_RSV_SYNCH';
6
7 procedure for_insert (
8 p_reservation_id IN NUMBER
9 , x_return_status OUT NOCOPY VARCHAR2
10 , x_msg_count OUT NOCOPY NUMBER
11 , x_msg_data OUT NOCOPY VARCHAR2 ) is
12
13 -- constants
14 c_api_name constant varchar(30) := 'for_insert';
15
16 l_demand_id_dmd number;
17 l_demand_id_rsv number;
18 l_demand_source_type_id number;
19
20 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
21 begin
22
23 x_return_status := fnd_api.g_ret_sts_success ;
24
25 if (inv_rsv_trigger_global.g_from_trigger = FALSE) then
26 inv_rsv_trigger_global.g_from_trigger := TRUE;
27
28 /*
29 ** Get sequence value for demand_id and parent_demand_id
30 */
31 select mtl_demand_s.nextval
32 into l_demand_id_dmd
33 from dual;
34
35 select mtl_demand_s.nextval
36 into l_demand_id_rsv
37 from dual;
38
39 select demand_source_type_id
40 into l_demand_source_type_id
41 from mtl_reservations
42 where reservation_id = p_reservation_id;
43
44 /* Insert demand into MTL_DEMAND for Non-Orders */
45
46 if (l_demand_source_type_id not in (2,8,12)) then
47 insert into mtl_demand(
48 DEMAND_ID
49 ,ORGANIZATION_ID
50 ,INVENTORY_ITEM_ID
51 ,DEMAND_SOURCE_TYPE
52 ,DEMAND_SOURCE_HEADER_ID
53 ,DEMAND_SOURCE_LINE
54 ,DEMAND_SOURCE_DELIVERY
55 ,DEMAND_SOURCE_NAME
56 ,UOM_CODE
57 ,LINE_ITEM_QUANTITY
58 ,PRIMARY_UOM_QUANTITY
59 ,LINE_ITEM_RESERVATION_QTY
60 ,RESERVATION_QUANTITY
61 ,COMPLETED_QUANTITY
62 ,REQUIREMENT_DATE
63 ,RESERVATION_TYPE
64 ,LAST_UPDATE_DATE
65 ,LAST_UPDATED_BY
66 ,CREATION_DATE
67 ,CREATED_BY
68 ,LAST_UPDATE_LOGIN
69 ,REQUEST_ID
70 ,PROGRAM_APPLICATION_ID
71 ,PROGRAM_ID
72 ,PROGRAM_UPDATE_DATE
73 ,PARENT_DEMAND_ID
74 ,EXTERNAL_SOURCE_CODE
75 ,EXTERNAL_SOURCE_LINE_ID
76 ,USER_LINE_NUM
77 ,USER_DELIVERY
78 ,SCHEDULE_ID
79 ,AUTODETAIL_GROUP_ID
80 ,SUPPLY_SOURCE_TYPE
81 ,SUPPLY_SOURCE_HEADER_ID
82 ,SUPPLY_GROUP_ID
83 ,UPDATED_FLAG
84 ,REVISION
85 ,LOT_NUMBER
86 ,SERIAL_NUMBER
87 ,SUBINVENTORY
88 ,LOCATOR_ID
89 ,COMPONENT_SEQUENCE_ID
90 ,PARENT_COMPONENT_SEQ_ID
91 ,RTO_MODEL_SOURCE_LINE
92 ,RTO_PREVIOUS_QTY
93 ,CONFIG_STATUS
94 ,AVAILABLE_TO_MRP
95 ,AVAILABLE_TO_ATP
96 ,ESTIMATED_RELEASE_DATE
97 ,DEMAND_CLASS
98 ,ROW_STATUS_FLAG
99 ,ORDER_CHANGE_REPORT_FLAG
100 ,ATP_LEAD_TIME
101 ,EXPLOSION_EFFECTIVITY_DATE
102 ,BOM_LEVEL
103 ,MRP_DATE
104 ,MRP_QUANTITY
105 ,CUSTOMER_ID
106 ,TERRITORY_ID
107 ,BILL_TO_SITE_USE_ID
108 ,SHIP_TO_SITE_USE_ID
109 ,MASTER_RESERVATION_QTY
110 ,DESCRIPTION
111 ,ATTRIBUTE_CATEGORY
112 ,ATTRIBUTE1
113 ,ATTRIBUTE2
114 ,ATTRIBUTE3
115 ,ATTRIBUTE4
116 ,ATTRIBUTE5
117 ,ATTRIBUTE6
118 ,ATTRIBUTE7
119 ,ATTRIBUTE8
120 ,ATTRIBUTE9
121 ,ATTRIBUTE10
122 ,ATTRIBUTE11
123 ,ATTRIBUTE12
124 ,ATTRIBUTE13
125 ,ATTRIBUTE14
126 ,ATTRIBUTE15
127 ,DEMAND_TYPE
128 ,DUPLICATED_CONFIG_ITEM_ID
129 ,DUPLICATED_CONFIG_DEMAND_ID
130 ,EXPLOSION_GROUP_ID
131 ,ORDERED_ITEM_ID
132 ,CONFIG_GROUP_ID
133 ,OPERATION_SEQ_NUM
134 ,N_COLUMN1)
135 select
136 l_demand_id_dmd
137 ,a.ORGANIZATION_ID
138 ,a.INVENTORY_ITEM_ID
139 ,a.DEMAND_SOURCE_TYPE_ID
140 ,NVL(a.DEMAND_SOURCE_HEADER_ID,0)
141 ,a.DEMAND_SOURCE_LINE_ID
142 ,a.DEMAND_SOURCE_DELIVERY
143 ,a.DEMAND_SOURCE_NAME
144 ,a.RESERVATION_UOM_CODE
145 ,a.RESERVATION_QUANTITY
146 ,a.PRIMARY_RESERVATION_QUANTITY
147 ,a.RESERVATION_QUANTITY
148 ,a.PRIMARY_RESERVATION_QUANTITY
149 ,0 /* COMPLETED_QUANTITY */
150 ,a.REQUIREMENT_DATE
151 ,1 /* RESERVATION_TYPE */
152 ,a.LAST_UPDATE_DATE
153 ,a.LAST_UPDATED_BY
154 ,a.CREATION_DATE
155 ,a.CREATED_BY
156 ,a.LAST_UPDATE_LOGIN
157 ,a.REQUEST_ID
158 ,a.PROGRAM_APPLICATION_ID
159 ,a.PROGRAM_ID
160 ,a.PROGRAM_UPDATE_DATE
161 ,NULL /* PARENT_DEMAND_ID */
162 ,a.EXTERNAL_SOURCE_CODE
163 ,a.EXTERNAL_SOURCE_LINE_ID
164 ,NULL /* USER_LINE_NUM */
165 ,NULL /* USER_DELIVERY */
166 ,NULL /* SCHEDULE_ID */
167 ,a.AUTODETAIL_GROUP_ID
168 ,decode(a.SUPPLY_SOURCE_TYPE_ID,13,NULL,a.SUPPLY_SOURCE_TYPE_ID)
169 ,a.SUPPLY_SOURCE_HEADER_ID
170 ,NULL /* SUPPLY_GROUP_ID */
171 ,NULL /* UPDATED_FLAG */
172 ,a.REVISION
173 ,a.LOT_NUMBER
174 ,a.SERIAL_NUMBER
175 ,a.SUBINVENTORY_CODE
176 ,a.LOCATOR_ID
177 ,NULL /* COMPONENT_SEQUENCE_ID */
178 ,NULL /* PARENT_COMPONENT_SEQ_ID */
179 ,NULL /* RTO_MODEL_SOURCE_LINE */
180 ,NULL /* RTO_PREVIOUS_QTY */
181 ,NULL /* CONFIG_STATUS */
182 ,1 /* AVAILABLE_TO_MRP */
183 ,1 /* AVAILABLE_TO_ATP */
184 ,NULL /* ESTIMATED_RELEASE_DATE */
185 ,NULL /* DEMAND_CLASS */
186 ,1 /* ROW_STATUS_FLAG */
187 ,NULL /* ORDER_CHANGE_REPORT_FLAG */
188 ,NULL /* ATP_LEAD_TIME */
189 ,NULL /* EXPLOSION_EFFECTIVITY_DATE*/
190 ,NULL /* BOM_LEVEL */
191 ,NULL /* MRP_DATE */
192 ,NULL /* MRP_QUANTITY */
193 ,NULL /* CUSTOMER_ID */
194 ,NULL /* TERRITORY_ID */
195 ,NULL /* BILL_TO_SITE_USE_ID */
196 ,NULL /* SHIP_TO_SITE_USE_ID */
197 ,NULL /* MASTER_RESERVATION_QTY */
198 ,NULL /* DESCRIPTION */
199 ,a.ATTRIBUTE_CATEGORY
200 ,a.ATTRIBUTE1
201 ,a.ATTRIBUTE2
202 ,a.ATTRIBUTE3
203 ,a.ATTRIBUTE4
204 ,a.ATTRIBUTE5
205 ,a.ATTRIBUTE6
206 ,a.ATTRIBUTE7
207 ,a.ATTRIBUTE8
208 ,a.ATTRIBUTE9
209 ,a.ATTRIBUTE10
210 ,a.ATTRIBUTE11
211 ,a.ATTRIBUTE12
212 ,a.ATTRIBUTE13
213 ,a.ATTRIBUTE14
214 ,a.ATTRIBUTE15
215 ,NULL /* DEMAND_TYPE */
216 ,NULL /* DUPLICATED_CONFIG_ITEM_ID */
217 ,NULL /* DUPLICATED_CONFIG_DEMAND_ID */
218 ,NULL /* EXPLOSION_GROUP_ID */
219 ,NULL /* ORDERED_ITEM_ID */
220 ,NULL /* CONFIG_GROUP_ID */
221 ,NULL /* OPERATION_SEQ_NUM */
222 ,p_reservation_id
223 from mtl_reservations a
224 where a.reservation_id = p_reservation_id;
225 end if;
226
227 /* Insert reservation into MTL_DEMAND */
228 insert into mtl_demand(
229 DEMAND_ID
230 ,ORGANIZATION_ID
231 ,INVENTORY_ITEM_ID
232 ,DEMAND_SOURCE_TYPE
233 ,DEMAND_SOURCE_HEADER_ID
234 ,DEMAND_SOURCE_LINE
235 ,DEMAND_SOURCE_DELIVERY
236 ,DEMAND_SOURCE_NAME
237 ,UOM_CODE
238 ,LINE_ITEM_QUANTITY
239 ,PRIMARY_UOM_QUANTITY
240 ,LINE_ITEM_RESERVATION_QTY
241 ,RESERVATION_QUANTITY
242 ,COMPLETED_QUANTITY
243 ,REQUIREMENT_DATE
244 ,RESERVATION_TYPE
245 ,LAST_UPDATE_DATE
246 ,LAST_UPDATED_BY
247 ,CREATION_DATE
248 ,CREATED_BY
249 ,LAST_UPDATE_LOGIN
250 ,REQUEST_ID
251 ,PROGRAM_APPLICATION_ID
252 ,PROGRAM_ID
253 ,PROGRAM_UPDATE_DATE
254 ,PARENT_DEMAND_ID
255 ,EXTERNAL_SOURCE_CODE
256 ,EXTERNAL_SOURCE_LINE_ID
257 ,USER_LINE_NUM
258 ,USER_DELIVERY
259 ,SCHEDULE_ID
260 ,AUTODETAIL_GROUP_ID
261 ,SUPPLY_SOURCE_TYPE
262 ,SUPPLY_SOURCE_HEADER_ID
263 ,SUPPLY_GROUP_ID
264 ,UPDATED_FLAG
265 ,REVISION
266 ,LOT_NUMBER
267 ,SERIAL_NUMBER
268 ,SUBINVENTORY
269 ,LOCATOR_ID
270 ,COMPONENT_SEQUENCE_ID
271 ,PARENT_COMPONENT_SEQ_ID
272 ,RTO_MODEL_SOURCE_LINE
273 ,RTO_PREVIOUS_QTY
274 ,CONFIG_STATUS
275 ,AVAILABLE_TO_MRP
276 ,AVAILABLE_TO_ATP
277 ,ESTIMATED_RELEASE_DATE
278 ,DEMAND_CLASS
279 ,ROW_STATUS_FLAG
280 ,ORDER_CHANGE_REPORT_FLAG
281 ,ATP_LEAD_TIME
282 ,EXPLOSION_EFFECTIVITY_DATE
283 ,BOM_LEVEL
284 ,MRP_DATE
285 ,MRP_QUANTITY
286 ,CUSTOMER_ID
287 ,TERRITORY_ID
288 ,BILL_TO_SITE_USE_ID
289 ,SHIP_TO_SITE_USE_ID
290 ,MASTER_RESERVATION_QTY
291 ,DESCRIPTION
292 ,ATTRIBUTE_CATEGORY
293 ,ATTRIBUTE1
294 ,ATTRIBUTE2
295 ,ATTRIBUTE3
296 ,ATTRIBUTE4
297 ,ATTRIBUTE5
298 ,ATTRIBUTE6
299 ,ATTRIBUTE7
300 ,ATTRIBUTE8
301 ,ATTRIBUTE9
302 ,ATTRIBUTE10
303 ,ATTRIBUTE11
304 ,ATTRIBUTE12
305 ,ATTRIBUTE13
306 ,ATTRIBUTE14
307 ,ATTRIBUTE15
308 ,DEMAND_TYPE
309 ,DUPLICATED_CONFIG_ITEM_ID
310 ,DUPLICATED_CONFIG_DEMAND_ID
311 ,EXPLOSION_GROUP_ID
312 ,ORDERED_ITEM_ID
313 ,CONFIG_GROUP_ID
314 ,OPERATION_SEQ_NUM
315 ,N_COLUMN1)
316 select
317 l_demand_id_rsv
318 ,a.ORGANIZATION_ID
319 ,a.INVENTORY_ITEM_ID
320 ,a.DEMAND_SOURCE_TYPE_ID
321 ,NVL(a.DEMAND_SOURCE_HEADER_ID,0)
322 ,a.DEMAND_SOURCE_LINE_ID
323 ,a.DEMAND_SOURCE_DELIVERY
324 ,a.DEMAND_SOURCE_NAME
325 ,a.RESERVATION_UOM_CODE
326 ,a.RESERVATION_QUANTITY
327 ,a.PRIMARY_RESERVATION_QUANTITY
328 ,NULL /* RESERVATION_QUANTITY */
329 ,NULL /* PRIM_RESERVATION_QUANTITY */
330 ,0 /* COMPLETED_QUANTITY */
331 ,a.REQUIREMENT_DATE
332 ,decode(a.SUPPLY_SOURCE_TYPE_ID,13,2,3) /* RESERVATION_TYPE */
333 ,a.LAST_UPDATE_DATE
334 ,a.LAST_UPDATED_BY
335 ,a.CREATION_DATE
336 ,a.CREATED_BY
337 ,a.LAST_UPDATE_LOGIN
338 ,a.REQUEST_ID
339 ,a.PROGRAM_APPLICATION_ID
340 ,a.PROGRAM_ID
341 ,a.PROGRAM_UPDATE_DATE
342 ,l_demand_id_dmd /* PARENT_DEMAND_ID */
343 ,a.EXTERNAL_SOURCE_CODE
344 ,a.EXTERNAL_SOURCE_LINE_ID
345 ,NULL /* USER_LINE_NUM */
346 ,NULL /* USER_DELIVERY */
347 ,NULL /* SCHEDULE_ID */
348 ,a.AUTODETAIL_GROUP_ID
349 ,decode(a.SUPPLY_SOURCE_TYPE_ID,13,NULL,a.SUPPLY_SOURCE_TYPE_ID)
350 ,a.SUPPLY_SOURCE_HEADER_ID
351 ,NULL /* SUPPLY_GROUP_ID */
352 ,NULL /* UPDATED_FLAG */
353 ,a.REVISION
354 ,a.LOT_NUMBER
355 ,a.SERIAL_NUMBER
356 ,a.SUBINVENTORY_CODE
357 ,a.LOCATOR_ID
358 ,NULL /* COMPONENT_SEQUENCE_ID */
359 ,NULL /* PARENT_COMPONENT_SEQ_ID */
360 ,NULL /* RTO_MODEL_SOURCE_LINE */
361 ,NULL /* RTO_PREVIOUS_QTY */
362 ,NULL /* CONFIG_STATUS */
363 ,1 /* AVAILABLE_TO_MRP */
364 ,1 /* AVAILABLE_TO_ATP */
365 ,NULL /* ESTIMATED_RELEASE_DATE */
366 ,NULL /* DEMAND_CLASS */
367 ,1 /* ROW_STATUS_FLAG */
368 ,NULL /* ORDER_CHANGE_REPORT_FLAG */
369 ,NULL /* ATP_LEAD_TIME */
370 ,NULL /* EXPLOSION_EFFECTIVITY_DATE*/
371 ,NULL /* BOM_LEVEL */
372 ,NULL /* MRP_DATE */
373 ,NULL /* MRP_QUANTITY */
374 ,NULL /* CUSTOMER_ID */
375 ,NULL /* TERRITORY_ID */
376 ,NULL /* BILL_TO_SITE_USE_ID */
377 ,NULL /* SHIP_TO_SITE_USE_ID */
378 ,NULL /* MASTER_RESERVATION_QTY */
379 ,NULL /* DESCRIPTION */
380 ,a.ATTRIBUTE_CATEGORY
381 ,a.ATTRIBUTE1
382 ,a.ATTRIBUTE2
383 ,a.ATTRIBUTE3
384 ,a.ATTRIBUTE4
385 ,a.ATTRIBUTE5
386 ,a.ATTRIBUTE6
387 ,a.ATTRIBUTE7
388 ,a.ATTRIBUTE8
389 ,a.ATTRIBUTE9
390 ,a.ATTRIBUTE10
391 ,a.ATTRIBUTE11
392 ,a.ATTRIBUTE12
393 ,a.ATTRIBUTE13
397 ,NULL /* DUPLICATED_CONFIG_ITEM_ID */
394 ,a.ATTRIBUTE14
395 ,a.ATTRIBUTE15
396 ,NULL /* DEMAND_TYPE */
398 ,NULL /* DUPLICATED_CONFIG_DEMAND_ID */
399 ,NULL /* EXPLOSION_GROUP_ID */
400 ,NULL /* ORDERED_ITEM_ID */
401 ,NULL /* CONFIG_GROUP_ID */
402 ,NULL /* OPERATION_SEQ_NUM */
403 ,p_reservation_id
404 from mtl_reservations a
405 where a.reservation_id = p_reservation_id;
406
407 update mtl_reservations
408 set n_column1 = l_demand_id_rsv
409 where reservation_id = p_reservation_id;
410
411 inv_rsv_trigger_global.g_from_trigger := FALSE;
412 end if;
413
414 exception
415 when fnd_api.g_exc_error then
416 x_return_status := fnd_api.g_ret_sts_error ;
417 inv_rsv_trigger_global.g_from_trigger := FALSE;
418
419 when fnd_api.g_exc_unexpected_error then
420 x_return_status := fnd_api.g_ret_sts_unexp_error ;
421 inv_rsv_trigger_global.g_from_trigger := FALSE;
422
423 when others then
424 x_return_status := fnd_api.g_ret_sts_unexp_error ;
425 inv_rsv_trigger_global.g_from_trigger := FALSE;
426
427 if (fnd_msg_pub.check_msg_level
428 (fnd_msg_pub.g_msg_lvl_unexp_error)) then
429 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
430 end if;
431
432 end for_insert;
433
434 procedure for_update (
435 p_reservation_id IN NUMBER
436 , x_return_status OUT NOCOPY VARCHAR2
437 , x_msg_count OUT NOCOPY NUMBER
438 , x_msg_data OUT NOCOPY VARCHAR2 ) is
439
440 -- constants
441 c_api_name constant varchar(30) := 'for_update';
442
443 -- variables
444 l_demand_source_type_id number;
445
446 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
447 begin
448 x_return_status := fnd_api.g_ret_sts_success ;
449
450 if (inv_rsv_trigger_global.g_from_trigger = FALSE) then
451 inv_rsv_trigger_global.g_from_trigger := TRUE;
452
453 select demand_source_type_id
454 into l_demand_source_type_id
455 from mtl_reservations
456 where reservation_id = p_reservation_id;
457
458 /* Update demand in MTL_DEMAND for Non-Orders */
459 if (l_demand_source_type_id not in (2,8,12)) then
460 update mtl_demand a
461 set (
462 a.ORGANIZATION_ID
463 ,a.INVENTORY_ITEM_ID
464 ,a.DEMAND_SOURCE_TYPE
465 ,a.DEMAND_SOURCE_HEADER_ID
466 ,a.DEMAND_SOURCE_LINE
467 ,a.DEMAND_SOURCE_DELIVERY
468 ,a.DEMAND_SOURCE_NAME
469 ,a.UOM_CODE
470 ,a.LINE_ITEM_QUANTITY
471 ,a.PRIMARY_UOM_QUANTITY
472 ,a.LINE_ITEM_RESERVATION_QTY
473 ,a.RESERVATION_QUANTITY
474 ,a.COMPLETED_QUANTITY
475 ,a.REQUIREMENT_DATE
476 ,a.LAST_UPDATE_DATE
477 ,a.LAST_UPDATED_BY
478 ,a.CREATION_DATE
479 ,a.CREATED_BY
480 ,a.LAST_UPDATE_LOGIN
481 ,a.REQUEST_ID
482 ,a.PROGRAM_APPLICATION_ID
483 ,a.PROGRAM_ID
484 ,a.PROGRAM_UPDATE_DATE
485 ,a.EXTERNAL_SOURCE_CODE
486 ,a.EXTERNAL_SOURCE_LINE_ID
487 ,a.USER_LINE_NUM
488 ,a.USER_DELIVERY
489 ,a.SCHEDULE_ID
490 ,a.AUTODETAIL_GROUP_ID
491 ,a.SUPPLY_SOURCE_TYPE
492 ,a.SUPPLY_SOURCE_HEADER_ID
493 ,a.SUPPLY_GROUP_ID
494 ,a.UPDATED_FLAG
495 ,a.REVISION
496 ,a.LOT_NUMBER
497 ,a.SERIAL_NUMBER
498 ,a.SUBINVENTORY
499 ,a.LOCATOR_ID
500 ,a.COMPONENT_SEQUENCE_ID
501 ,a.PARENT_COMPONENT_SEQ_ID
502 ,a.RTO_MODEL_SOURCE_LINE
503 ,a.RTO_PREVIOUS_QTY
504 ,a.CONFIG_STATUS
505 ,a.AVAILABLE_TO_MRP
506 ,a.AVAILABLE_TO_ATP
507 ,a.ESTIMATED_RELEASE_DATE
508 ,a.DEMAND_CLASS
509 ,a.ROW_STATUS_FLAG
510 ,a.ORDER_CHANGE_REPORT_FLAG
511 ,a.ATP_LEAD_TIME
512 ,a.EXPLOSION_EFFECTIVITY_DATE
513 ,a.BOM_LEVEL
514 ,a.MRP_DATE
515 ,a.MRP_QUANTITY
516 ,a.CUSTOMER_ID
517 ,a.TERRITORY_ID
518 ,a.BILL_TO_SITE_USE_ID
519 ,a.SHIP_TO_SITE_USE_ID
520 ,a.MASTER_RESERVATION_QTY
521 ,a.DESCRIPTION
522 ,a.ATTRIBUTE_CATEGORY
523 ,a.ATTRIBUTE1
524 ,a.ATTRIBUTE2
525 ,a.ATTRIBUTE3
526 ,a.ATTRIBUTE4
527 ,a.ATTRIBUTE5
528 ,a.ATTRIBUTE6
529 ,a.ATTRIBUTE7
530 ,a.ATTRIBUTE8
531 ,a.ATTRIBUTE9
532 ,a.ATTRIBUTE10
533 ,a.ATTRIBUTE11
534 ,a.ATTRIBUTE12
535 ,a.ATTRIBUTE13
536 ,a.ATTRIBUTE14
537 ,a.ATTRIBUTE15
538 ,a.DEMAND_TYPE
539 ,a.DUPLICATED_CONFIG_ITEM_ID
540 ,a.DUPLICATED_CONFIG_DEMAND_ID
541 ,a.EXPLOSION_GROUP_ID
542 ,a.ORDERED_ITEM_ID
543 ,a.CONFIG_GROUP_ID
544 ,a.OPERATION_SEQ_NUM) = (
545 select
546 b.ORGANIZATION_ID
547 ,b.INVENTORY_ITEM_ID
548 ,b.DEMAND_SOURCE_TYPE_ID
549 ,NVL(b.DEMAND_SOURCE_HEADER_ID,0)
550 ,b.DEMAND_SOURCE_LINE_ID
551 ,b.DEMAND_SOURCE_DELIVERY
552 ,b.DEMAND_SOURCE_NAME
553 ,b.RESERVATION_UOM_CODE
554 ,b.RESERVATION_QUANTITY
555 ,b.PRIMARY_RESERVATION_QUANTITY
556 ,b.RESERVATION_QUANTITY
557 ,b.PRIMARY_RESERVATION_QUANTITY
558 ,0 /* COMPLETED_QUANTITY */
559 ,b.REQUIREMENT_DATE
560 ,b.LAST_UPDATE_DATE
561 ,b.LAST_UPDATED_BY
562 ,b.CREATION_DATE
563 ,b.CREATED_BY
564 ,b.LAST_UPDATE_LOGIN
568 ,b.PROGRAM_UPDATE_DATE
565 ,b.REQUEST_ID
566 ,b.PROGRAM_APPLICATION_ID
567 ,b.PROGRAM_ID
569 ,b.EXTERNAL_SOURCE_CODE
570 ,b.EXTERNAL_SOURCE_LINE_ID
571 ,NULL /* USER_LINE_NUM */
572 ,NULL /* USER_DELIVERY */
573 ,NULL /* SCHEDULE_ID */
574 ,b.AUTODETAIL_GROUP_ID
575 ,decode(b.SUPPLY_SOURCE_TYPE_ID,13,NULL,b.SUPPLY_SOURCE_TYPE_ID)
576 ,b.SUPPLY_SOURCE_HEADER_ID
577 ,NULL /* SUPPLY_GROUP_ID */
578 ,NULL /* UPDATED_FLAG */
579 ,b.REVISION
580 ,b.LOT_NUMBER
581 ,b.SERIAL_NUMBER
582 ,b.SUBINVENTORY_CODE
583 ,b.LOCATOR_ID
584 ,NULL /* COMPONENT_SEQUENCE_ID */
585 ,NULL /* PARENT_COMPONENT_SEQ_ID */
586 ,NULL /* RTO_MODEL_SOURCE_LINE */
587 ,NULL /* RTO_PREVIOUS_QTY */
588 ,NULL /* CONFIG_STATUS */
589 ,1 /* AVAILABLE_TO_MRP */
590 ,1 /* AVAILABLE_TO_ATP */
591 ,NULL /* ESTIMATED_RELEASE_DATE */
592 ,NULL /* DEMAND_CLASS */
593 ,1 /* ROW_STATUS_FLAG */
594 ,NULL /* ORDER_CHANGE_REPORT_FLAG */
595 ,NULL /* ATP_LEAD_TIME */
596 ,NULL /* EXPLOSION_EFFECTIVITY_DATE*/
597 ,NULL /* BOM_LEVEL */
598 ,NULL /* MRP_DATE */
599 ,NULL /* MRP_QUANTITY */
600 ,NULL /* CUSTOMER_ID */
601 ,NULL /* TERRITORY_ID */
602 ,NULL /* BILL_TO_SITE_USE_ID */
603 ,NULL /* SHIP_TO_SITE_USE_ID */
604 ,NULL /* MASTER_RESERVATION_QTY */
605 ,NULL /* DESCRIPTION */
606 ,b.ATTRIBUTE_CATEGORY
607 ,b.ATTRIBUTE1
608 ,b.ATTRIBUTE2
609 ,b.ATTRIBUTE3
610 ,b.ATTRIBUTE4
611 ,b.ATTRIBUTE5
612 ,b.ATTRIBUTE6
613 ,b.ATTRIBUTE7
614 ,b.ATTRIBUTE8
615 ,b.ATTRIBUTE9
616 ,b.ATTRIBUTE10
617 ,b.ATTRIBUTE11
618 ,b.ATTRIBUTE12
619 ,b.ATTRIBUTE13
620 ,b.ATTRIBUTE14
621 ,b.ATTRIBUTE15
622 ,NULL /* DEMAND_TYPE */
623 ,NULL /* DUPLICATED_CONFIG_ITEM_ID */
624 ,NULL /* DUPLICATED_CONFIG_DEMAND_ID */
625 ,NULL /* EXPLOSION_GROUP_ID */
626 ,NULL /* ORDERED_ITEM_ID */
627 ,NULL /* CONFIG_GROUP_ID */
628 ,NULL /* OPERATION_SEQ_NUM */
629 from mtl_reservations b
630 where b.reservation_id = p_reservation_id)
631 where a.n_column1 = p_reservation_id
632 and a.reservation_type = 1
633 and a.parent_demand_id is null;
634 end if;
635
636 /* Update reservation in MTL_DEMAND */
637 update mtl_demand a
638 set (
639 a.ORGANIZATION_ID
640 ,a.INVENTORY_ITEM_ID
641 ,a.DEMAND_SOURCE_TYPE
642 ,a.DEMAND_SOURCE_HEADER_ID
643 ,a.DEMAND_SOURCE_LINE
644 ,a.DEMAND_SOURCE_DELIVERY
645 ,a.DEMAND_SOURCE_NAME
646 ,a.UOM_CODE
647 ,a.LINE_ITEM_QUANTITY
648 ,a.PRIMARY_UOM_QUANTITY
649 ,a.LINE_ITEM_RESERVATION_QTY
650 ,a.RESERVATION_QUANTITY
651 ,a.COMPLETED_QUANTITY
652 ,a.REQUIREMENT_DATE
653 ,a.LAST_UPDATE_DATE
654 ,a.LAST_UPDATED_BY
655 ,a.CREATION_DATE
656 ,a.CREATED_BY
657 ,a.LAST_UPDATE_LOGIN
658 ,a.REQUEST_ID
659 ,a.PROGRAM_APPLICATION_ID
660 ,a.PROGRAM_ID
661 ,a.PROGRAM_UPDATE_DATE
662 ,a.EXTERNAL_SOURCE_CODE
663 ,a.EXTERNAL_SOURCE_LINE_ID
664 ,a.USER_LINE_NUM
665 ,a.USER_DELIVERY
666 ,a.SCHEDULE_ID
667 ,a.AUTODETAIL_GROUP_ID
668 ,a.SUPPLY_SOURCE_TYPE
669 ,a.SUPPLY_SOURCE_HEADER_ID
670 ,a.SUPPLY_GROUP_ID
671 ,a.UPDATED_FLAG
672 ,a.REVISION
673 ,a.LOT_NUMBER
674 ,a.SERIAL_NUMBER
675 ,a.SUBINVENTORY
676 ,a.LOCATOR_ID
677 ,a.COMPONENT_SEQUENCE_ID
678 ,a.PARENT_COMPONENT_SEQ_ID
679 ,a.RTO_MODEL_SOURCE_LINE
680 ,a.RTO_PREVIOUS_QTY
681 ,a.CONFIG_STATUS
682 ,a.AVAILABLE_TO_MRP
683 ,a.AVAILABLE_TO_ATP
684 ,a.ESTIMATED_RELEASE_DATE
685 ,a.DEMAND_CLASS
686 ,a.ROW_STATUS_FLAG
687 ,a.ORDER_CHANGE_REPORT_FLAG
688 ,a.ATP_LEAD_TIME
689 ,a.EXPLOSION_EFFECTIVITY_DATE
690 ,a.BOM_LEVEL
691 ,a.MRP_DATE
692 ,a.MRP_QUANTITY
693 ,a.CUSTOMER_ID
694 ,a.TERRITORY_ID
695 ,a.BILL_TO_SITE_USE_ID
696 ,a.SHIP_TO_SITE_USE_ID
697 ,a.MASTER_RESERVATION_QTY
698 ,a.DESCRIPTION
699 ,a.ATTRIBUTE_CATEGORY
700 ,a.ATTRIBUTE1
701 ,a.ATTRIBUTE2
702 ,a.ATTRIBUTE3
703 ,a.ATTRIBUTE4
704 ,a.ATTRIBUTE5
705 ,a.ATTRIBUTE6
706 ,a.ATTRIBUTE7
707 ,a.ATTRIBUTE8
708 ,a.ATTRIBUTE9
709 ,a.ATTRIBUTE10
710 ,a.ATTRIBUTE11
711 ,a.ATTRIBUTE12
712 ,a.ATTRIBUTE13
713 ,a.ATTRIBUTE14
714 ,a.ATTRIBUTE15
715 ,a.DEMAND_TYPE
716 ,a.DUPLICATED_CONFIG_ITEM_ID
717 ,a.DUPLICATED_CONFIG_DEMAND_ID
718 ,a.EXPLOSION_GROUP_ID
719 ,a.ORDERED_ITEM_ID
720 ,a.CONFIG_GROUP_ID
721 ,a.OPERATION_SEQ_NUM
722 ,a.RESERVATION_TYPE) = (
723 select
724 b.ORGANIZATION_ID
725 ,b.INVENTORY_ITEM_ID
726 ,b.DEMAND_SOURCE_TYPE_ID
727 ,NVL(b.DEMAND_SOURCE_HEADER_ID,0)
728 ,b.DEMAND_SOURCE_LINE_ID
729 ,b.DEMAND_SOURCE_DELIVERY
730 ,b.DEMAND_SOURCE_NAME
731 ,b.RESERVATION_UOM_CODE
732 ,b.RESERVATION_QUANTITY
733 ,b.PRIMARY_RESERVATION_QUANTITY
734 ,NULL /* RESERVATION_QUANTITY */
735 ,NULL /* PRIM_RESERVATION_QUANTITY */
739 ,b.LAST_UPDATED_BY
736 ,0 /* COMPLETED_QUANTITY */
737 ,b.REQUIREMENT_DATE
738 ,b.LAST_UPDATE_DATE
740 ,b.CREATION_DATE
741 ,b.CREATED_BY
742 ,b.LAST_UPDATE_LOGIN
743 ,b.REQUEST_ID
744 ,b.PROGRAM_APPLICATION_ID
745 ,b.PROGRAM_ID
746 ,b.PROGRAM_UPDATE_DATE
747 ,b.EXTERNAL_SOURCE_CODE
748 ,b.EXTERNAL_SOURCE_LINE_ID
749 ,NULL /* USER_LINE_NUM */
750 ,NULL /* USER_DELIVERY */
751 ,NULL /* SCHEDULE_ID */
752 ,b.AUTODETAIL_GROUP_ID
753 ,decode(b.SUPPLY_SOURCE_TYPE_ID,13,NULL,b.SUPPLY_SOURCE_TYPE_ID)
754 ,b.SUPPLY_SOURCE_HEADER_ID
755 ,NULL /* SUPPLY_GROUP_ID */
756 ,NULL /* UPDATED_FLAG */
757 ,b.REVISION
758 ,b.LOT_NUMBER
759 ,b.SERIAL_NUMBER
760 ,b.SUBINVENTORY_CODE
761 ,b.LOCATOR_ID
762 ,NULL /* COMPONENT_SEQUENCE_ID */
763 ,NULL /* PARENT_COMPONENT_SEQ_ID */
764 ,NULL /* RTO_MODEL_SOURCE_LINE */
765 ,NULL /* RTO_PREVIOUS_QTY */
766 ,NULL /* CONFIG_STATUS */
767 ,1 /* AVAILABLE_TO_MRP */
768 ,1 /* AVAILABLE_TO_ATP */
769 ,NULL /* ESTIMATED_RELEASE_DATE */
770 ,NULL /* DEMAND_CLASS */
771 ,1 /* ROW_STATUS_FLAG */
772 ,NULL /* ORDER_CHANGE_REPORT_FLAG */
773 ,NULL /* ATP_LEAD_TIME */
774 ,NULL /* EXPLOSION_EFFECTIVITY_DATE*/
775 ,NULL /* BOM_LEVEL */
776 ,NULL /* MRP_DATE */
777 ,NULL /* MRP_QUANTITY */
778 ,NULL /* CUSTOMER_ID */
779 ,NULL /* TERRITORY_ID */
780 ,NULL /* BILL_TO_SITE_USE_ID */
781 ,NULL /* SHIP_TO_SITE_USE_ID */
782 ,NULL /* MASTER_RESERVATION_QTY */
783 ,NULL /* DESCRIPTION */
784 ,b.ATTRIBUTE_CATEGORY
785 ,b.ATTRIBUTE1
786 ,b.ATTRIBUTE2
787 ,b.ATTRIBUTE3
788 ,b.ATTRIBUTE4
789 ,b.ATTRIBUTE5
790 ,b.ATTRIBUTE6
791 ,b.ATTRIBUTE7
792 ,b.ATTRIBUTE8
793 ,b.ATTRIBUTE9
794 ,b.ATTRIBUTE10
795 ,b.ATTRIBUTE11
796 ,b.ATTRIBUTE12
797 ,b.ATTRIBUTE13
798 ,b.ATTRIBUTE14
799 ,b.ATTRIBUTE15
800 ,NULL /* DEMAND_TYPE */
801 ,NULL /* DUPLICATED_CONFIG_ITEM_ID */
802 ,NULL /* DUPLICATED_CONFIG_DEMAND_ID */
803 ,NULL /* EXPLOSION_GROUP_ID */
804 ,NULL /* ORDERED_ITEM_ID */
805 ,NULL /* CONFIG_GROUP_ID */
806 ,NULL /* OPERATION_SEQ_NUM */
807 ,decode(b.SUPPLY_SOURCE_TYPE_ID,13,2,3) /* RESERVATION_TYPE */
808 from mtl_reservations b
809 where b.reservation_id = p_reservation_id)
810 where a.n_column1 = p_reservation_id
811 and a.reservation_type in (2,3)
812 and a.parent_demand_id is not null;
813
814 inv_rsv_trigger_global.g_from_trigger := FALSE;
815 end if;
816
817 exception
818 when fnd_api.g_exc_error then
819 x_return_status := fnd_api.g_ret_sts_error ;
820 inv_rsv_trigger_global.g_from_trigger := FALSE;
821
822 when fnd_api.g_exc_unexpected_error then
823 x_return_status := fnd_api.g_ret_sts_unexp_error ;
824 inv_rsv_trigger_global.g_from_trigger := FALSE;
825
826 when others then
827 x_return_status := fnd_api.g_ret_sts_unexp_error ;
828 inv_rsv_trigger_global.g_from_trigger := FALSE;
829
830 if (fnd_msg_pub.check_msg_level
831 (fnd_msg_pub.g_msg_lvl_unexp_error)) then
832 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
833 end if;
834
835 end for_update;
836
837 procedure for_delete (
838 p_reservation_id IN NUMBER
839 , x_return_status OUT NOCOPY VARCHAR2
840 , x_msg_count OUT NOCOPY NUMBER
841 , x_msg_data OUT NOCOPY VARCHAR2 ) is
842
843 -- constants
844 c_api_name constant varchar(30) := 'for_delete';
845
846 -- variables
847 l_demand_source_type_id number;
848
849 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
850 begin
851 x_return_status := fnd_api.g_ret_sts_success ;
852
853 if (inv_rsv_trigger_global.g_from_trigger = FALSE) then
854 inv_rsv_trigger_global.g_from_trigger := TRUE;
855
856 select demand_source_type_id
857 into l_demand_source_type_id
858 from mtl_reservations
859 where reservation_id = p_reservation_id;
860
861 /* Delete demand in MTL_DEMAND for Non-Orders */
862 if (l_demand_source_type_id not in (2,8,12)) then
863 delete mtl_demand
864 where n_column1 = p_reservation_id
865 and reservation_type = 1
866 and parent_demand_id is null;
867 end if;
868
869 /* Delete reservation in MTL_DEMAND */
870 delete mtl_demand
871 where n_column1 = p_reservation_id
872 and reservation_type in (2,3)
873 and parent_demand_id is not null;
874
875 inv_rsv_trigger_global.g_from_trigger := FALSE;
876 end if;
877
878 exception
879 when fnd_api.g_exc_error then
880 x_return_status := fnd_api.g_ret_sts_error ;
881 inv_rsv_trigger_global.g_from_trigger := FALSE;
882
883 when fnd_api.g_exc_unexpected_error then
884 x_return_status := fnd_api.g_ret_sts_unexp_error ;
885 inv_rsv_trigger_global.g_from_trigger := FALSE;
886
887 when others then
888 x_return_status := fnd_api.g_ret_sts_unexp_error ;
889 inv_rsv_trigger_global.g_from_trigger := FALSE;
890
891 if (fnd_msg_pub.check_msg_level
892 (fnd_msg_pub.g_msg_lvl_unexp_error)) then
893 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
894 end if;
895
899 p_reservation_id IN NUMBER
896 end for_delete;
897
898 procedure for_relieve (
900 , p_primary_relieved_quantity IN NUMBER
901 , x_return_status OUT NOCOPY VARCHAR2
902 , x_msg_count OUT NOCOPY NUMBER
903 , x_msg_data OUT NOCOPY VARCHAR2 ) is
904
905 -- constants
906 c_api_name constant varchar(30) := 'for_relieve';
907
908 -- variables
909 l_demand_source_type_id number;
910
911 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
912 begin
913 --inv_debug.message('tst115', 'in for_relieve');
914 --inv_debug.message('tst115', 'p_reservation_id is ' || p_reservation_id);
915
916 x_return_status := fnd_api.g_ret_sts_success ;
917
918 if (inv_rsv_trigger_global.g_from_trigger = FALSE) then
919 inv_rsv_trigger_global.g_from_trigger := TRUE;
920
921 select demand_source_type_id
922 into l_demand_source_type_id
923 from mtl_reservations
924 where reservation_id = p_reservation_id;
925
926 --inv_debug.message('tst115', 'l_demand_source_type_id is ' || l_demand_source_type_id);
927
928 /* Update completed_quantity for demand in MTL_DEMAND for Non-Orders */
929 if (l_demand_source_type_id not in (2,8,12)) then
930 begin
931 --inv_debug.message('tst115', 'update mtl_demand for non-orders');
932 update mtl_demand
933 set completed_quantity = completed_quantity + p_primary_relieved_quantity
934 where n_column1 = p_reservation_id
935 and reservation_type = 1
936 and parent_demand_id is null;
937 exception
938 when no_data_found then
939 null;
940 end;
941 end if;
942
943 /* Update completed_quantity for reservation in MTL_DEMAND */
944 begin
945 --inv_debug.message('tst115', 'update mtl_demand for orders');
946 update mtl_demand
947 set completed_quantity = completed_quantity + p_primary_relieved_quantity
948 where n_column1 = p_reservation_id
949 and reservation_type in (2,3)
950 and parent_demand_id is not null;
951 exception
952 when no_data_found then
953 null;
954 end;
955
956 inv_rsv_trigger_global.g_from_trigger := FALSE;
957 end if;
958 --inv_debug.message('tst115', 'return from inv_rsv_synch.for_relieve');
959 exception
960 when fnd_api.g_exc_error then
961 x_return_status := fnd_api.g_ret_sts_error ;
962 inv_rsv_trigger_global.g_from_trigger := FALSE;
963
964 when fnd_api.g_exc_unexpected_error then
965 x_return_status := fnd_api.g_ret_sts_unexp_error ;
966 inv_rsv_trigger_global.g_from_trigger := FALSE;
967
968 when others then
969 x_return_status := fnd_api.g_ret_sts_unexp_error ;
970 inv_rsv_trigger_global.g_from_trigger := FALSE;
971
972 if (fnd_msg_pub.check_msg_level
973 (fnd_msg_pub.g_msg_lvl_unexp_error)) then
974 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
975 end if;
976 end for_relieve;
977
978 end INV_RSV_SYNCH;