1 PACKAGE BODY OE_CANCEL AS
2 /* $Header: OECANCLB.pls 115.7 99/08/24 11:52:30 porting shi $ */
3
4
5
6 ------------------------------------------------------------------------
7 -- procedure CHECK_ORDER_CANCELLABLE
8 --
9 -- If the order being checked is an RMA check to see if there
10 -- are are line that have been Partially Accepted (S29=16) or
11 -- Completely Accepted (S29=17). If this is the fact then the
12 -- order is not cancellable. Since the order lines are not
13 -- updated until the RMA interface concurrent program is run,
14 -- there is a possiblity that order lines have been received by
15 -- Inventory and the Order Line status is Interfaced (S29=14).
16 -- In this case we need to make sure that no RMA line qtys have
17 -- been received by INV. If qtys have been received then the
18 -- Order is not cancellable.
19 -- If the order being checked for cancellation is not an RMA,
20 -- there is a need to check and see if this order contains
21 -- ATO lines whose details are still linked. In this case
22 -- the order is not cancellable.
23 --
24 -- RETURNS:
25 -- 1 -> if order is cancellable
26 -- 0 -> if order is not cancellable
27 ------------------------------------------------------------------------
28
29 procedure CHECK_ORDER_CANCELLABLE(
30 V_HEADER_ID IN NUMBER
31 , V_ORDER_CATEGORY IN VARCHAR2
32 , V_PRINT_ERR_MSG IN NUMBER
33 , V_RESULT OUT NUMBER
34 )
35 IS
36 DUMMY NUMBER := 0;
37 RECEIVED_QUANTITY NUMBER := 0;
38 x BOOLEAN;
39 BEGIN
40
41
42 IF (V_ORDER_CATEGORY = 'RMA') THEN
43
44 SELECT COUNT(*)
45 INTO DUMMY
46 FROM SO_LINES
47 WHERE HEADER_ID = V_HEADER_ID
48 AND S29 IN (16, 17);
49
50
51
52 IF (DUMMY >= 1) THEN
53 V_RESULT := 0;
54 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_RMA_INTERFACED');
55 -- "You cannot cancel this Return at this level."
56 ELSE
57 SELECT NVL(SUM(NVL(MTLSRR.RECEIVED_QUANTITY,0)),0)
58 INTO RECEIVED_QUANTITY
59 FROM MTL_SO_RMA_RECEIPTS MTLSRR,
60 MTL_SO_RMA_INTERFACE MTLSRI
61 WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
62 AND MTLSRI.RMA_LINE_ID IN
63 (SELECT LINE_ID
64 FROM SO_LINES
65 WHERE HEADER_ID = V_HEADER_ID
66 AND S29 = 14);
67
68 IF (RECEIVED_QUANTITY > 0) THEN
69 V_RESULT := 0;
70 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_RMA_INTERFACED');
71 -- "You cannot cancel this Return at this level."
72 ELSE
73 V_RESULT := 1;
74 END IF;
75 END IF;
76 ELSE
77 SELECT COUNT(*)
78 INTO DUMMY
79 FROM SO_LINE_DETAILS SLD,
80 SO_LINES SOL
81 WHERE SOL.HEADER_ID = V_HEADER_ID
82 AND SLD.LINE_ID = SOL.LINE_ID
83 AND SLD.SCHEDULE_STATUS_CODE = 'SUPPLY_RESERVED'
84 AND SLD.WIP_COMPLETED_QUANTITY < SLD.QUANTITY;
85
86 IF (DUMMY >= 1) THEN
87 V_RESULT := 0;
88 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_ATO_DETS_LINKED');
89 -- This order contains ATO lines whose details are
90 -- linked. See explanation
94 END IF;
91 ELSE
92 V_RESULT := 1;
93 END IF;
95
96 END CHECK_ORDER_CANCELLABLE; -- CHECK_ORDER_CANCELLABLE
97
98 ------------------------------------------------------------------------
99 -- procedure UPDATE_HEADER_INFO
100
101 -- Update the SO_HEADERS table to reflect that the Order has been
102 -- cancelled. This is done by setting the S4 (Cancel Order) column
103 -- to 11 (Complete). Then the lines in the SO_LINES table are also
104 -- updated to reflect the order cancellation. The open_flag is set
105 -- to null, the cancelled_quantity is set to ordered_quantity
106 -- and S9 (Cancel Line) is set to 11 (Complete).
107 -- If the order is also an RMA any records in the interface that
108 -- have not been received by INV need to be modified so they
109 -- don't get picked by the next run of the RMA inferface program.
110 -- For those record found in the interface set the quantity to 0
111 -- and set the closed_flag to 'Y'.
112 -- Need to insert a record into so_order_cancellations indicating
113 -- the order level cancellation. We also need to detele the
114 -- corresponding so_line_details for the order just cancelled,
115 -- excluding any line details that have been released. Also
116 -- need to cleanup the so_line_service_details if there were any
117 -- lines that were of a service nature.
118 --
119 -- RETURNS:
120 -- 1 -> order has been cancelled
121 ------------------------------------------------------------------------
122
123 procedure UPDATE_HEADER_INFO(
124 V_HEADER_ID IN NUMBER
125 , V_ORDER_CATEGORY IN VARCHAR2
126 , V_CANCEL_COMMENT IN LONG
127 , V_CANCEL_CODE IN VARCHAR2
128 , V_LAST_UPDATED_BY IN NUMBER
129 , V_LAST_UPDATE_LOGIN IN NUMBER
130 , V_SOURCE_CODE IN VARCHAR2
131 , V_PRINT_ERR_MSG IN NUMBER
132 , V_RESULT OUT NUMBER)
133
134 is
135
136 x BOOLEAN;
137 v_current_user NUMBER;
138
139 BEGIN
140
141 v_current_user := to_number(FND_PROFILE.VALUE('USER_ID'));
142
143 --
144 -- Set the S values for the headers, specifically set S4 (Cancel Order) to
145 -- 11 (Complete)
146 --
147 UPDATE SO_HEADERS
148 SET S1 = DECODE(S1,18,'',S1),
149 S1_DATE = DECODE(S1,18,'',S1_DATE),
150 S2 = DECODE(S2,18,'',S2),
151 S2_DATE = DECODE(S2,18,'',S2_DATE),
152 S3 = DECODE(S3,18,'',S3),
153 S3_DATE = DECODE(S3,18,'',S3_DATE),
154 S4 = 11,
155 S4_DATE = SYSDATE,
156 S5 = DECODE(S5,18,'',S5),
157 S5_DATE = DECODE(S5,18,'',S5_DATE),
158 S6 = DECODE(S6,18,'',S6),
159 S6_DATE = DECODE(S6,18,'',S6_DATE),
160 S7 = DECODE(S7,18,'',S7),
161 S7_DATE = DECODE(S7,18,'',S7_DATE),
162 S8 = DECODE(S8,18,'',S8),
163 S8_DATE = DECODE(S8,18,'',S8_DATE),
164 S9 = DECODE(S9,18,'',S9),
165 S9_DATE = DECODE(S9,18,'',S9_DATE),
166 S10 = DECODE(S10,18,'',S10),
167 S10_DATE = DECODE(S10,18,'',S10_DATE),
168 S11 = DECODE(S11,18,'',S11),
169 S11_DATE = DECODE(S11,18,'',S11_DATE),
170 S12 = DECODE(S12,18,'',S12),
171 S12_DATE = DECODE(S12,18,'',S12_DATE),
172 S13 = DECODE(S13,18,'',S13),
173 S13_DATE = DECODE(S13,18,'',S13_DATE),
174 S14 = DECODE(S14,18,'',S14),
175 S14_DATE = DECODE(S14,18,'',S14_DATE),
176 S15 = DECODE(S15,18,'',S15),
177 S15_DATE = DECODE(S15,18,'',S15_DATE),
178 S16 = DECODE(S16,18,'',S16),
179 S16_DATE = DECODE(S16,18,'',S16_DATE),
180 S17 = DECODE(S17,18,'',S17),
181 S17_DATE = DECODE(S17,18,'',S17_DATE),
182 S18 = DECODE(S18,18,'',S18),
183 S18_DATE = DECODE(S18,18,'',S18_DATE),
184 S19 = DECODE(S19,18,'',S19),
185 S19_DATE = DECODE(S19,18,'',S19_DATE),
186 S20 = DECODE(S20,18,'',S20),
187 S20_DATE = DECODE(S20,18,'',S20_DATE),
188 S21 = DECODE(S21,18,'',S21),
189 S21_DATE = DECODE(S21,18,'',S21_DATE),
190 S22 = DECODE(S22,18,'',S22),
191 S22_DATE = DECODE(S22,18,'',S22_DATE),
192 S23 = DECODE(S23,18,'',S23),
193 S23_DATE = DECODE(S23,18,'',S23_DATE),
194 S24 = DECODE(S24,18,'',S24),
195 S24_DATE = DECODE(S24,18,'',S24_DATE),
196 S25 = DECODE(S25,18,'',S25),
197 S25_DATE = DECODE(S25,18,'',S25_DATE),
198 S26 = DECODE(S26,18,'',S26),
199 S26_DATE = DECODE(S26,18,'',S26_DATE),
200 S27 = DECODE(S27,18,'',S27),
201 S27_DATE = DECODE(S27,18,'',S27_DATE),
202 S28 = DECODE(S28,18,'',S28),
203 S28_DATE = DECODE(S28,18,'',S28_DATE),
204 S29 = DECODE(S29,18,'',S29),
205 S29_DATE = DECODE(S29,18,'',S29_DATE),
206 S30 = DECODE(S30,18,'',S30),
207 S30_DATE = DECODE(S30,18,'',S30_DATE)
208 WHERE HEADER_ID = V_HEADER_ID;
209
210 --
211 -- Set the S values for the lines, specifically set S9 (Cancel Line) to
212 -- 11 (Complete)
213 --
214
215 UPDATE SO_LINES
216 SET LAST_UPDATED_BY = V_LAST_UPDATED_BY,
217 LAST_UPDATE_LOGIN = V_LAST_UPDATE_LOGIN,
218 LAST_UPDATE_DATE = SYSDATE,
219 OPEN_FLAG = NULL,
220 CANCELLED_QUANTITY = ORDERED_QUANTITY,
221 S1 = DECODE(S1,18,8,S1),
222 S1_DATE = DECODE(S1,18,sysdate,S1_DATE),
223 S2 = DECODE(S2,18,8,S2),
224 S2_DATE = DECODE(S2,18,sysdate,S2_DATE),
225 S3 = DECODE(S3,18,8,S3),
226 S3_DATE = DECODE(S3,18,sysdate,S3_DATE),
227 S4 = DECODE(S4,18,8,S4),
228 S4_DATE = DECODE(S4,18,sysdate,S4_DATE),
229 S5 = DECODE(S5,18,8,S5),
230 S5_DATE = DECODE(S5,18,sysdate,S5_DATE),
231 S6 = DECODE(S6,18,8,S6),
232 S6_DATE = DECODE(S6,18,sysdate,S6_DATE),
233 S7 = DECODE(S7,18,8,S7),
234 S7_DATE = DECODE(S7,18,sysdate,S7_DATE),
235 S8 = DECODE(S8,18,8,S8),
236 S8_DATE = DECODE(S8,18,sysdate,S8_DATE),
237 S9 = 11,
238 S9_DATE = SYSDATE,
239 S10 = DECODE(S10,18,8,S10),
240 S10_DATE = DECODE(S10,18,sysdate,S10_DATE),
241 S11 = DECODE(S11,18,8,S11),
242 S11_DATE = DECODE(S11,18,sysdate,S11_DATE),
243 S12 = DECODE(S12,18,8,S12),
244 S12_DATE = DECODE(S12,18,sysdate,S12_DATE),
245 S13 = DECODE(S13,18,8,S13),
246 S13_DATE = DECODE(S13,18,sysdate,S13_DATE),
247 S14 = DECODE(S14,18,8,S14),
248 S14_DATE = DECODE(S14,18,sysdate,S14_DATE),
249 S15 = DECODE(S15,18,8,S15),
250 S15_DATE = DECODE(S15,18,sysdate,S15_DATE),
251 S16 = DECODE(S16,18,8,S16),
252 S16_DATE = DECODE(S16,18,sysdate,S16_DATE),
253 S17 = DECODE(S17,18,8,S17),
254 S17_DATE = DECODE(S17,18,sysdate,S17_DATE),
255 S18 = DECODE(S18,18,8,S18),
256 S18_DATE = DECODE(S18,18,sysdate,S18_DATE),
257 S19 = DECODE(S19,18,8,S19),
258 S19_DATE = DECODE(S19,18,sysdate,S19_DATE),
259 S20 = DECODE(S20,18,8,S20),
260 S20_DATE = DECODE(S20,18,sysdate,S20_DATE),
261 S21 = DECODE(S21,18,8,S21),
262 S21_DATE = DECODE(S21,18,sysdate,S21_DATE),
263 S22 = DECODE(S22,18,8,S22),
264 S22_DATE = DECODE(S22,18,sysdate,S22_DATE),
265 S23 = DECODE(S23,18,8,S23),
266 S23_DATE = DECODE(S23,18,sysdate,S23_DATE),
267 S24 = DECODE(S24,18,8,S24),
268 S24_DATE = DECODE(S24,18,sysdate,S24_DATE),
269 S25 = DECODE(S25,18,8,S25),
270 S25_DATE = DECODE(S25,18,sysdate,S25_DATE),
271 S26 = DECODE(S26,18,8,S26),
272 S26_DATE = DECODE(S26,18,sysdate,S26_DATE),
273 S27 = DECODE(S27,18,8,S27),
274 S27_DATE = DECODE(S27,18,sysdate,S27_DATE),
275 S28 = DECODE(S28,18,8,S28),
276 S28_DATE = DECODE(S28,18,sysdate,S28_DATE),
277 S29 = DECODE(S29,18,8,S29),
278 S29_DATE = DECODE(S29,18,sysdate,S29_DATE),
279 S30 = DECODE(S30,18,8,S30),
280 S30_DATE = DECODE(S30,18,sysdate,S30_DATE)
281 WHERE HEADER_ID = V_HEADER_ID;
282
283 IF V_ORDER_CATEGORY = 'RMA' THEN
284 declare
285 CURSOR rma1 IS
286 SELECT LINE_ID
287 FROM SO_LINES
288 WHERE HEADER_ID = V_HEADER_ID
289 AND S29 IN (14,16,17);
290 begin
291 FOR rma1rec IN rma1 LOOP
292 UPDATE MTL_SO_RMA_INTERFACE MSRI
293 SET MSRI.QUANTITY = 0,
294 MSRI.CLOSED_FLAG = 'Y'
295 WHERE MSRI.RMA_LINE_ID = rma1rec.LINE_ID
296 AND MSRI.SOURCE_CODE = V_SOURCE_CODE
297 AND MSRI.IN_USE_FLAG IS NULL;
298 END LOOP;
299 end;
300 END IF;
301
302 INSERT INTO SO_ORDER_CANCELLATIONS
303 (HEADER_ID, CANCEL_CODE,
304 CANCELLED_BY, CANCEL_DATE,
305 LAST_UPDATED_BY, LAST_UPDATE_DATE,
306 LAST_UPDATE_LOGIN, CREATION_DATE,
307 CREATED_BY, CANCEL_COMMENT )
308 VALUES(V_HEADER_ID, V_CANCEL_CODE,
309 v_current_user, SYSDATE,
310 V_LAST_UPDATED_BY, SYSDATE,
311 V_LAST_UPDATE_LOGIN, SYSDATE,
312 v_current_user, V_CANCEL_COMMENT );
313
314 DELETE FROM SO_LINE_DETAILS
315 WHERE LINE_ID IN (
316 SELECT LINE_ID
317 FROM SO_LINES
318 WHERE HEADER_ID = V_HEADER_ID)
319 AND RELEASED_FLAG = 'N';
320
321
322 DELETE FROM SO_LINE_SERVICE_DETAILS
323 WHERE LINE_ID IN (
324 SELECT LINE_ID
325 FROM SO_LINES
326 WHERE HEADER_ID = V_HEADER_ID);
327
328
329 IF V_PRINT_ERR_MSG = 1 THEN
330 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_ORD_CANCLD_REQUERY');
331 V_RESULT := 1;
332 ELSE
333 V_RESULT := 1;
334 END IF;
335
336 END UPDATE_HEADER_INFO;
337
338
339 ------------------------------------------------------------------------
340 -- procedure CHECK_SERVICE
341 --
342 -- This is called from oeklcn() to determine if a line can be
343 -- cancelled. This procedure contains the required checks that
344 -- are not covered by security rules.
345 -- Specifically it handles service checks
346 --
347 -- CS_CUSTOMER_PRODUCTS (products)
348 -- /|\
349 -- CS_CP_SERVICES (service)
350 -- /|\
351 -- CS_CP_SERVICE_TRANSACTIONS (sort of history)
352 --
353 -- Two possible situations can occur and hence the need to have
354 -- two separate sql checks. The first sql statement takes care
355 -- of the situation in which a service is associated with a product
356 -- and checks to see if the service is still active. This is done
357 -- by a join on CS_CUSTOMER_PRODUCTS and CS_CP_SERVICES.
358 -- The second possiblity is that service was ordered by itself,
359 -- a standalone service if you will. This would not have any
360 -- record in CS_CUSTOMER_PRODUCTS and hence the need to join
361 -- CS_CP_SERVICES to CS_CP_SERVICE_TRANSACTIONS.
362 --
363 -- RETURNS:
364 -- 1 -> if order is not cancellable
365 -- 0 -> if order is cancellable
366 ------------------------------------------------------------------------
367
368 procedure CHECK_SERVICE(
369 V_LINE_ID IN NUMBER
370 , V_REAL_PARENT_LINE_ID IN NUMBER
371 , V_COMPONENT_CODE IN VARCHAR2
372 , V_ITEM_TYPE_CODE IN VARCHAR2
373 , V_SUBTREE_EXISTS IN NUMBER
374 , V_PRINT_ERR_MSG IN NUMBER
375 , V_RESULT OUT NUMBER
376 )
377 IS
378 DUMMY NUMBER := 0;
379 DUMMY2 NUMBER := 0;
380 DUMMY3 NUMBER := 0;
381 x BOOLEAN;
382 BEGIN
383
384 V_RESULT := 0;
385
386 SELECT COUNT(*)
387 INTO DUMMY
388 FROM CS_CP_SERVICES SER,
389 CS_CUSTOMER_PRODUCTS CPS
390 WHERE SER.CUSTOMER_PRODUCT_ID = CPS.CUSTOMER_PRODUCT_ID
391 AND CPS.ORIGINAL_ORDER_LINE_ID IN
392 (SELECT LINE_ID FROM SO_LINES
393 WHERE PARENT_LINE_ID = V_REAL_PARENT_LINE_ID
394 AND NVL(COMPONENT_CODE,'0') LIKE V_COMPONENT_CODE ||'%'
395 AND V_SUBTREE_EXISTS = 1
396 UNION
397 SELECT LINE_ID
398 FROM SO_LINES
399 WHERE LINE_ID = V_LINE_ID)
400 AND SYSDATE BETWEEN SER.START_DATE_ACTIVE AND SER.END_DATE_ACTIVE;
401
402 IF (DUMMY >= 1) THEN
403 IF V_PRINT_ERR_MSG = 1 THEN
404 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_SERVICE_ACTIVE');
405 -- "You cannot cancel an order that has active service lines."
406 END IF;
407 V_RESULT := 1;
408 ELSE
409 IF V_ITEM_TYPE_CODE = 'SERVICE' THEN
410
411 SELECT COUNT(*)
412 INTO DUMMY2
413 FROM CS_CP_SERVICES SER,
414 CS_CP_SERVICE_TRANSACTIONS TRX
415 WHERE TRX.SERVICE_ORDER_LINE_ID = V_LINE_ID
416 AND TRX.CP_SERVICE_ID = SER.CP_SERVICE_ID
417 AND SYSDATE BETWEEN SER.START_DATE_ACTIVE AND SER.END_DATE_ACTIVE;
418
419 IF (DUMMY2 >= 1) THEN
420 IF V_PRINT_ERR_MSG = 1 THEN
421 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_SERVICE_ACTIVE');
422 END IF;
423 V_RESULT := 1;
424 END IF;
425
426 END IF;
427 END IF;
428
429 SELECT COUNT(*)
430 INTO DUMMY3
431 FROM SO_LINES
432 WHERE LINE_ID = V_LINE_ID
433 AND ATO_FLAG = 'Y'
434 AND ATO_LINE_ID IS NOT NULL
435 AND ITEM_TYPE_CODE <> 'SERVICE'
436 AND LINE_TYPE_CODE <> 'RETURN'
437 AND NVL(S2,18) <> 18;
438
439 IF (DUMMY3 >= 1) THEN
440 IF V_PRINT_ERR_MSG = 1 THEN
441 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_NO_ATO_AFTER_PICK');
442 END IF;
443 V_RESULT := 1;
444 END IF;
445
446
447 END CHECK_SERVICE;
448
449
450 ------------------------------------------------------------------------
451 -- procedure CHECK_IF_CONFIG
452 --
453 -- This procedure checks to see if the line being operated on
454 -- has a configuration item associated with it.
455 --
456 -- RETURNS:
457 -- 1 -> if there is a configuration item associated with the line
458 -- 0 -> if there is no configuration item associated with the line
459 ------------------------------------------------------------------------
460
461 procedure CHECK_IF_CONFIG(
462 V_LOOP_LINE_ID IN NUMBER
463 , V_RESULT OUT NUMBER
464 )
465 IS
466 DUMMY NUMBER;
467
468 BEGIN
469 SELECT COUNT(*)
470 INTO DUMMY
471 FROM SO_LINE_DETAILS
472 WHERE LINE_ID = V_LOOP_LINE_ID
473 AND CONFIGURATION_ITEM_FLAG = 'Y';
474
475 IF (DUMMY >= 1) THEN
476 V_RESULT := 1;
477 ELSE
478 V_RESULT := 0;
479 END IF;
480
481 END CHECK_IF_CONFIG;
482
483 ------------------------------------------------------------------------
484 -- procedure CALCULATE_RMA_QTY
485 --
486 -- This procedure retrieves that allowable RMA cancel qty and the
487 -- received qty into INV. If the line has RMA interface (S29) status
488 -- of Eligible (18) or Not Applicable (8) then the RMA cancel qty is
489 -- is simply the ordered_quantity less the cancelled_quantity. If
490 -- on the other hand the RMA interface (S29) status is Interfaced (14),
491 -- Partially Accepted (16) or Completely Accepted (17) then we get
492 -- the received qty into INV and then calculate the RMA cancel qty.
493 -- Hence the allowable RMA cancel qty would then be the ordered_quantity
494 -- less the cancelled_quantity less the received qty. If we allowed
495 -- an over receipt then the allowable RMA cancel qty is 0.
496 --
497 -- RETURNS
498 -- 1 -> successful
499 -- RMA_ALLOWABLE_CANCEL_QTY
500 -- RECEIVED_QTY
501 ------------------------------------------------------------------------
502
503 procedure CALCULATE_RMA_QTY(
504 V_LINE_ID IN NUMBER
505 , V_S29 IN NUMBER
506 , V_ORDER_QTY IN NUMBER
507 , V_CANCELLED_QTY IN NUMBER
508 , V_RECEIVED_QTY OUT NUMBER
509 , V_ALLOWABLE_CANCEL_QTY OUT NUMBER
510 , V_PRINT_ERR_MSG IN NUMBER
511 , V_RESULT OUT NUMBER
512 )
513 IS
514
515 RECEIVED_QTY NUMBER :=0;
516 TEMP_QTY NUMBER :=0;
517 ALLOWABLE_CANCEL_QTY_TEMP NUMBER :=0;
518 x BOOLEAN;
519
520 BEGIN
521
522 IF ((V_S29=8) OR (V_S29=18) OR (nvl(V_S29,0) = 0 )) THEN
523 RECEIVED_QTY := 0;
524 GOTO do_calculation;
525
526 ELSIF ((V_S29=14) OR (V_S29=16) OR (V_S29=17)) THEN
527
528 SELECT CEIL(NVL(MAX(SUM(NVL(MTLSRR.RECEIVED_QUANTITY,0)) *
529 ((V_ORDER_QTY - NVL(V_CANCELLED_QTY,0))
530 / MAX(MTLSRI.QUANTITY))),0))
531 INTO RECEIVED_QTY
532 FROM MTL_SO_RMA_RECEIPTS MTLSRR,
533 MTL_SO_RMA_INTERFACE MTLSRI
534 WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
535 AND MTLSRI.RMA_LINE_ID = V_LINE_ID
536 GROUP BY MTLSRI.INVENTORY_ITEM_ID,
537 MTLSRI.COMPONENT_SEQUENCE_ID;
538
539 GOTO do_calculation;
540
541 ELSE
542 IF V_PRINT_ERR_MSG = 1 THEN
543 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_INVALID_S29');
544 V_RESULT := 0;
545 ELSE
546 V_RESULT := 0;
547 END IF;
548 RETURN;
549 END IF;
550
551 <<do_calculation>>
552 SELECT ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0)
553 INTO TEMP_QTY
554 FROM SO_LINES
555 WHERE LINE_ID = V_LINE_ID;
556
557 V_ALLOWABLE_CANCEL_QTY := TEMP_QTY - RECEIVED_QTY;
558 ALLOWABLE_CANCEL_QTY_TEMP := TEMP_QTY - RECEIVED_QTY;
559
560 IF (ALLOWABLE_CANCEL_QTY_TEMP < 0) THEN
561 V_ALLOWABLE_CANCEL_QTY := 0;
562 END IF;
563
564 V_RECEIVED_QTY := RECEIVED_QTY;
565 V_RESULT := 1;
566
567 END CALCULATE_RMA_QTY;
568
569
570 ------------------------------------------------------------------------
571 -- procedure SET_SERVICE_QTY
572 --
573 -- This procedure will called from oekclq to retrieve the
574 -- allowable cancel qty for a service item. It is simply
575 -- the ordered_quantity less the cancelled_quantity on the line.
576 --
577 -- RETURNS
578 -- 1 -> successful
579 -- ALLOWABLE_CANCEL_QTY
580 ------------------------------------------------------------------------
581
582 procedure SET_SERVICE_QTY(
583 V_LINE_ID IN NUMBER
584 , V_ALLOWABLE_CANCEL_QTY OUT NUMBER
585 , V_RESULT OUT NUMBER
586 )
587 IS
588 TEMP_QTY NUMBER :=0;
589
590 BEGIN
591 SELECT (ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0))
592 INTO TEMP_QTY
593 FROM SO_LINES
594 WHERE LINE_ID = V_LINE_ID;
595
596 V_ALLOWABLE_CANCEL_QTY := TEMP_QTY;
597 V_RESULT := 1;
598
599 END SET_SERVICE_QTY;
600
601 ------------------------------------------------------------------------
602 -- procedure NONCONFIG_QTY
603 --
604 -- Get the total quantity from the details for the selected line
605 -- that are not included items and not released. For the
606 -- picking line details we sum also over backordered picking lines.
607 -- picking_header_id = 0
608 -- This quantity is used in determining the allowable cancel qty
609 -- for the line selected.
610 --
611 -- RETURNS
612 -- 1 -> successful
613 -- NONCONFIG_QTY
614 ------------------------------------------------------------------------
615
616 procedure NONCONFIG_QTY(
617 V_LOOP_LINE_ID IN NUMBER
618 , V_LOOP_RATIO_DEN IN NUMBER
619 , V_LOOP_RATIO_NUM IN NUMBER
620 , V_S2 IN NUMBER
621 , V_NONCONFIG_QTY OUT NUMBER
622 , V_RESULT OUT NUMBER
623 )
624 IS
625 DUMMY NUMBER;
626 LD_QTY NUMBER := 0;
627 PD_QTY NUMBER := 0;
628
629 BEGIN
630
631 -- statement tells us if we are dealing with an included item
632
633
634 SELECT COUNT(*)
635 INTO DUMMY
636 FROM SO_LINE_DETAILS
637 WHERE LINE_ID = V_LOOP_LINE_ID
638 AND SHIPPABLE_FLAG = 'Y'
639 AND INCLUDED_ITEM_FLAG = 'N';
640
641 IF (DUMMY >= 1) THEN
642
643 IF ( V_S2 <> 4) then
644
645 select nvl(sum(quantity),0)
646 into LD_QTY
647 from so_line_details
648 where nvl(released_flag,'N') = 'N'
649 and line_id = V_LOOP_LINE_ID
650 and included_item_flag = 'N'
651 and nvl(schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
652
653 ELSE
654 LD_QTY := 0;
655 END IF;
656
657 IF (V_S2 = 4 or V_S2 = 5) then
658
659 select nvl(sum(pld.requested_quantity),0)
660 into PD_QTY
661 from so_picking_lines pl, so_picking_line_details pld
662 where pl.picking_line_id = pld.picking_line_id
663 and pl.picking_header_id = 0
664 and pl.order_line_id = V_LOOP_LINE_ID
665 and nvl(pld.released_flag,'N') = 'N'
666 and pl.included_item_flag = 'N'
667 and nvl(pld.schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
668
669 ELSE
670 PD_QTY := 0;
671 END IF;
672
673 V_NONCONFIG_QTY := FLOOR((LD_QTY + PD_QTY) *
674 V_LOOP_RATIO_NUM / V_LOOP_RATIO_DEN);
675
676 END IF;
677
678 V_RESULT := 1;
679
680 END NONCONFIG_QTY;
681
682 ------------------------------------------------------------------------
683 -- procedure INCLUDE_QTY
684 --
685 -- A loop is setup to get the total quantity from the details.
686 -- The minimum qty is the quantity returned from the loop will be
687 -- returned. First get the total quantity from the details for the
688 -- selected line where there are included items that are required for
689 -- revenue and not released. For the picking line details we sum also
690 -- over backordered picking lines. We sum the quantity for both the
691 -- line details and picking line details. Finally the quantity is
692 -- adjusted for any ratio differences. This quantity is compared
693 -- to the quantity found in the details for the line and the
694 -- minimum qty is returned.
695 -- picking_header_id = 0
696 -- This quantity is used in determining the allowable cancel qty
697 -- for the line selected.
698 --
699 -- RETURNS
700 -- 1 -> successful
701 -- INCLUDE_QTY
702
703 ------------------------------------------------------------------------
704
705 procedure INCLUDE_QTY(
706 V_LOOP_LINE_ID IN NUMBER
707 , V_TOTAL_QTY_FINAL IN NUMBER
708 , V_LOOP_RATIO_DEN IN NUMBER
709 , V_LOOP_RATIO_NUM IN NUMBER
710 , V_S2 IN NUMBER
714 IS
711 , V_INCLUDE_QTY OUT NUMBER
712 , V_RESULT OUT NUMBER
713 )
715
716 LD_QTY NUMBER := 0;
717 PD_QTY NUMBER := 0;
718 TEMP_ITEM_ID NUMBER;
719 TOTAL_QTY NUMBER := 0;
720 TEMP_QTY_FINAL NUMBER := V_TOTAL_QTY_FINAL;
721 RATIO NUMBER;
722
723
724 CURSOR c1 IS
725 select distinct inventory_item_id, component_ratio
726 from so_line_details
727 where line_id = V_LOOP_LINE_ID
728 and included_item_flag = 'Y'
729 order by inventory_item_id;
730
731 BEGIN
732
733 FOR c1rec IN c1 LOOP
734 RATIO := c1rec.component_ratio;
735 TEMP_ITEM_ID := c1rec.inventory_item_id;
736
737 IF ( V_S2 <> 4) then
738
739 select nvl(sum(sld.quantity),0)
740 into LD_QTY
741 from so_line_details sld
742 where nvl(sld.released_flag,'N') = 'N'
743 and sld.line_id = V_LOOP_LINE_ID
744 and sld.inventory_item_id = TEMP_ITEM_ID
745 and nvl(sld.schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
746
747 ELSE
748 LD_QTY := 0;
749 END IF;
750
751 IF ( V_S2 = 4 or V_S2 = 5) then
752
753 select nvl(sum(pld.requested_quantity),0)
754 into PD_QTY
755 from so_picking_lines pl,
756 so_picking_line_details pld
757 where pl.picking_line_id = pld.picking_line_id
758 and pl.picking_header_id = 0
759 and pl.order_line_id = V_LOOP_LINE_ID
760 and pl.inventory_item_id = TEMP_ITEM_ID
761 and nvl(pld.released_flag,'N') = 'N'
762 and nvl(pld.schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
763
764 /*
765 ** Fix for Bug # 800989
766 ** Following select added to calculate Qty for Non Shippable
767 ** Included Items as above query will give PD_QTY=0 for such
768 ** cases.
769 */
770 select nvl(sum(sld.quantity),0) + PD_QTY
771 into PD_QTY
772 from so_line_details sld
773 where nvl(sld.released_flag,'N') = 'Y'
774 and nvl(sld.shippable_flag,'N') = 'N'
775 and sld.line_id = V_LOOP_LINE_ID
776 and sld.inventory_item_id = TEMP_ITEM_ID
777 and nvl(sld.schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
778
779 ELSE
780 PD_QTY := 0;
781 END IF;
782
783 TOTAL_QTY := FLOOR((LD_QTY + PD_QTY) / RATIO *
784 V_LOOP_RATIO_NUM / V_LOOP_RATIO_DEN);
785 TEMP_QTY_FINAL := LEAST(TEMP_QTY_FINAL, TOTAL_QTY);
786 END LOOP;
787
788 V_INCLUDE_QTY := TEMP_QTY_FINAL;
789 V_RESULT := 1;
790
791 END INCLUDE_QTY;
792
793 ------------------------------------------------------------------------
794 -- procedure CONFIG_QTY
795 --
796 -- Get the sum of the line details and picking line detail where
797 -- there exists a configuration item that is not released, but
798 -- possibly backordered. Then adjust it for any ratio differences
799 -- that might exist between the parent and its child.
800 --
801 -- RETURNS
802 -- 1 -> successful
803 -- CONFIG_QTY
804 ------------------------------------------------------------------------
805
806 procedure CONFIG_QTY(
807 V_ATO_LOOP_LINE_ID IN NUMBER
808 , V_LOOP_LINE_ID IN NUMBER
809 , V_LOOP_RATIO_DEN IN NUMBER
810 , V_LOOP_RATIO_NUM IN NUMBER
811 , V_S2 IN NUMBER
812 , V_CONFIG_QTY OUT NUMBER
813 , V_RESULT OUT NUMBER
814 )
815 IS
816 LD_QTY NUMBER := 0;
817 PD_QTY NUMBER := 0;
818 TOTAL_UNRELEASED NUMBER := 0;
819
820 BEGIN
821
822 IF (V_S2 <> 4) then
823
824 SELECT NVL(SUM(QUANTITY),0)
825 INTO LD_QTY
826 FROM SO_LINE_DETAILS
827 WHERE LINE_ID = V_ATO_LOOP_LINE_ID
828 AND CONFIGURATION_ITEM_FLAG = 'Y'
829 AND RELEASED_FLAG = 'N'
830 AND NVL(SCHEDULE_STATUS_CODE,'NULL') <> 'SUPPLY RESERVED';
831
832 ELSE
833 LD_QTY := 0;
834 END IF;
835
836 IF (V_S2 = 4 or V_S2 = 5) then
837
838 SELECT NVL(SUM(PLD.REQUESTED_QUANTITY),0)
839 INTO PD_QTY
840 FROM SO_PICKING_LINE_DETAILS PLD, SO_PICKING_LINES PL
841 WHERE PL.ORDER_LINE_ID = V_ATO_LOOP_LINE_ID
842 AND PL.PICKING_HEADER_ID = 0
843 AND PL.PICKING_LINE_ID = PLD.PICKING_LINE_ID
844 AND PLD.RELEASED_FLAG = 'N'
845 AND NVL(PLD.SCHEDULE_STATUS_CODE,'NULL') <> 'SUPPLY RESERVED';
846
847 ELSE
848 PD_QTY := 0;
849 END IF;
850
851 SELECT (L.ordered_quantity - nvl(L.cancelled_quantity,0)) /
852 (M.ordered_quantity - nvl(M.cancelled_quantity,0))
853 INTO TOTAL_UNRELEASED
854 FROM SO_LINES L, SO_LINES M
855 WHERE L.LINE_ID = V_LOOP_LINE_ID
856 AND M.LINE_ID = V_ATO_LOOP_LINE_ID;
857
858 V_CONFIG_QTY := FLOOR((LD_QTY + PD_QTY) * TOTAL_UNRELEASED *
859 V_LOOP_RATIO_NUM / V_LOOP_RATIO_DEN);
860 V_RESULT := 1;
861
862 END CONFIG_QTY;
863
864
865 ------------------------------------------------------------------------
866 -- procedure UPDATE_LINE_INFO
867 --
868 -- If a full cancellation is begin performed then update so_lines
869 -- by setting the cancelled_quantity equal to the ordered_quantity
870 -- less the greatest quantity between what was shipped and what was
871 -- invoiced.
872 -- If the line begin update is a child of a shipment schedule,
873 -- update the quantity on the parent shipment line.
874 -- If we have a detail shipment line, and it's an option,
875 -- once we cancel, we need to disassociate it from it's
876 -- source since we've changed the configuration.
877 -- Update the cancelled_quantity in so_lines adjusted by any
878 -- ratio differences if were have a model.
879 -- Insert a record into so_order_cancellations
880 -- Update the open_flag in so_lines, if ordered_quantity less
881 -- the cancelled_quantity is 0 then set the open_flag = ''
882 -- Set the S9 (Cancel Line) column based on the open flag.
883 -- If open_flag is null then set the S9 to 11 (Complete) otherwise
884 -- set S9 to 5 (Partial).
885 -- Also set S3 which is Backorder Release. Check to see if no
886 -- picking lines exist where the original requested quantity is
887 -- greater than the cancelled quantity. If this is TRUE and
888 -- S3 is 18 (Eligible) then set S3 to 8 (Not Applicable).
889 --
890 -- RETURNS
891 -- 1 -> success
892 ------------------------------------------------------------------------
893
894 procedure UPDATE_LINE_INFO(
895 V_LINE_ID IN NUMBER
896 , V_REQUESTED_CANCEL_QTY IN NUMBER
897 , V_CANCEL_COMMENT IN LONG
898 , V_CANCEL_CODE IN VARCHAR2
899 , V_FULL IN NUMBER
900 , V_OPTION_FLAG IN NUMBER
901 , V_PARENT_LINE_ID IN NUMBER
902 , V_LINE_TYPE_CODE IN VARCHAR2
903 , V_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER
904 , V_SUBTREE_EXISTS IN NUMBER
905 , V_COMPONENT_CODE IN VARCHAR2
906 , V_REAL_PARENT_LINE_ID IN NUMBER
907 , V_LAST_UPDATED_BY IN NUMBER
908 , V_LAST_UPDATE_LOGIN IN NUMBER
909 , V_STATUS IN VARCHAR2
910 , V_RESULT OUT NUMBER
911 )
912 IS
913
914 CANCEL_QTY_TMP NUMBER := 0;
915 L_CURRENT_ORDERED_QTY NUMBER := 0;
916 L_CURRENT_CANCELLED_QTY NUMBER := 0;
917 v_current_user NUMBER ;
918 l_org_id NUMBER := NULL;
919
920 BEGIN
921
922 v_current_user := to_number(FND_PROFILE.VALUE('USER_ID'));
923
924 l_org_id := FND_PROFILE.VALUE ('SO_ORGANIZATION_ID');
925
926 IF (V_OPTION_FLAG = 1) THEN
927 -- Need a sql statement here to mark records as changed after the
928 -- commit whose allowed quantity *may* have been affected by this
929 -- commit. The sql must make a *logical* change to the database
930 -- in order to actually mark the record.
931 -- Here we have chosen to cycle among the users profile_value, 0, and 1
932
933 UPDATE SO_LINES
934 SET LAST_UPDATED_BY = DECODE(LAST_UPDATED_BY, 0, 1, 1,
935 DECODE(V_LAST_UPDATED_BY,1,0,V_LAST_UPDATED_BY),
936 V_LAST_UPDATED_BY, 0, V_LAST_UPDATED_BY)
937 WHERE (LINE_ID = V_PARENT_LINE_ID
938 OR PARENT_LINE_ID = V_PARENT_LINE_ID);
939 END IF;
940
941 IF (V_FULL = 1) THEN
942
943 IF (V_LINE_TYPE_CODE = 'DETAIL') THEN
944 IF (V_PARENT_LINE_ID IS NULL OR V_PARENT_LINE_ID = 0) THEN
945
946 SELECT (ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0)
947 - GREATEST (NVL(SHIPPED_QUANTITY,0),
948 NVL(INVOICED_QUANTITY,0)))
949 INTO CANCEL_QTY_TMP
950 FROM SO_LINES
951 WHERE LINE_ID = V_LINE_ID;
952
953
954 UPDATE SO_LINES L
955 SET CANCELLED_QUANTITY =
956 (SELECT NVL(L.CANCELLED_QUANTITY,0) +
957 (CANCEL_QTY_TMP *
958 (L.ORDERED_QUANTITY-NVL(L.CANCELLED_QUANTITY,0))/
959 (L2.ORDERED_QUANTITY-NVL(L2.CANCELLED_QUANTITY,0)))
960 FROM SO_LINES L2
961 WHERE L2.LINE_ID = V_SHIPMENT_SCHEDULE_LINE_ID)
962 WHERE L.LINE_ID IN
963 (select line_id
964 from so_lines
965 where parent_line_id = V_SHIPMENT_SCHEDULE_LINE_ID
966 and nvl(component_code,'0') like V_COMPONENT_CODE || '%'
967 and V_SUBTREE_EXISTS = 1
968 union
969 select line_id
970 from so_lines
971 where line_id = V_SHIPMENT_SCHEDULE_LINE_ID);
972 ELSE
973 UPDATE SO_LINES
974 SET SOURCE_LINE_ID = NULL
975 WHERE LINE_ID = V_LINE_ID;
976 END IF;
977 END IF;
978
979
980 declare
981 CURSOR full1 IS
982 SELECT LINE_ID, HEADER_ID, ORDERED_QUANTITY, CANCELLED_QUANTITY
983 ,SHIPPED_QUANTITY,INVOICED_QUANTITY
984 FROM SO_LINES
985 WHERE LINE_ID IN
986 (select line_id
987 from so_lines
988 where parent_line_id = V_REAL_PARENT_LINE_ID
989 and (nvl(component_code,'0') like V_COMPONENT_CODE || '%'
990 OR COMPONENT_CODE IS NULL)
991 and V_SUBTREE_EXISTS = 1
992 union
993 select line_id
994 from so_lines
995 where line_id = V_LINE_ID
996 or service_parent_line_id = V_LINE_ID);
997
998
999 begin
1000
1001
1002 FOR full1rec IN full1 LOOP
1003 UPDATE SO_LINES SOL
1004 SET CANCELLED_QUANTITY = ORDERED_QUANTITY
1005 - GREATEST (NVL(SHIPPED_QUANTITY,0),
1006 NVL(INVOICED_QUANTITY,0))
1007 WHERE SOL.LINE_ID = full1rec.LINE_ID;
1008
1009 INSERT INTO SO_ORDER_CANCELLATIONS
1010 (LINE_ID, HEADER_ID, CANCEL_CODE, CANCELLED_BY,
1011 CANCEL_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
1012 CANCEL_COMMENT, CANCELLED_QUANTITY, STATUS,
1013 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )
1014 VALUES
1015 (full1rec.LINE_ID, full1rec.HEADER_ID, V_CANCEL_CODE,
1016 v_current_user,
1017 SYSDATE, V_LAST_UPDATED_BY, SYSDATE, V_CANCEL_COMMENT,
1018 (full1rec.ORDERED_QUANTITY -
1019 NVL(full1rec.CANCELLED_QUANTITY,0) - GREATEST
1020 (NVL(full1rec.SHIPPED_QUANTITY,0),
1021 NVL(full1rec.INVOICED_QUANTITY,0))), V_STATUS,
1022 SYSDATE,
1023 v_current_user, V_LAST_UPDATE_LOGIN);
1024
1025 END LOOP;
1026
1027 end;
1028
1029
1030 ELSE
1031 IF (V_LINE_TYPE_CODE = 'DETAIL') THEN
1032 IF (V_PARENT_LINE_ID IS NULL OR V_PARENT_LINE_ID = 0) THEN
1033
1034 UPDATE SO_LINES L
1035 SET CANCELLED_QUANTITY =
1036 (SELECT NVL(L.CANCELLED_QUANTITY,0) +
1037 (V_REQUESTED_CANCEL_QTY *
1038 (L.ORDERED_QUANTITY-NVL(L.CANCELLED_QUANTITY,0))/
1039 (L2.ORDERED_QUANTITY-NVL(L2.CANCELLED_QUANTITY,0)))
1040 FROM SO_LINES L2
1041 WHERE L2.LINE_ID = V_SHIPMENT_SCHEDULE_LINE_ID)
1042 WHERE L.LINE_ID IN
1043 (select line_id
1044 from so_lines
1045 where parent_line_id = V_SHIPMENT_SCHEDULE_LINE_ID
1046 and nvl(component_code,'0') like V_COMPONENT_CODE || '%'
1047 and V_SUBTREE_EXISTS = 1
1048 union
1049 select line_id
1050 from so_lines
1051 where line_id = V_SHIPMENT_SCHEDULE_LINE_ID);
1052 ELSE
1053 UPDATE SO_LINES
1054 SET SOURCE_LINE_ID = NULL
1055 WHERE LINE_ID = V_LINE_ID;
1056 END IF;
1057 END IF;
1058
1059 SELECT ORDERED_QUANTITY, CANCELLED_QUANTITY
1060 INTO L_CURRENT_ORDERED_QTY, L_CURRENT_CANCELLED_QTY
1061 FROM SO_LINES
1062 WHERE LINE_ID = V_LINE_ID;
1063
1064 DECLARE
1065 CURSOR not_full IS
1066 SELECT LINE_ID, HEADER_ID, ORDERED_QUANTITY
1067 ,CANCELLED_QUANTITY
1068 FROM SO_LINES
1069 WHERE LINE_ID IN
1070 (SELECT LINE_ID
1071 FROM SO_LINES
1072 WHERE PARENT_LINE_ID = V_REAL_PARENT_LINE_ID
1073 AND (NVL(COMPONENT_CODE,'0') LIKE V_COMPONENT_CODE || '%'
1074 OR COMPONENT_CODE IS NULL)
1075 AND V_SUBTREE_EXISTS = 1
1076 UNION
1077 SELECT LINE_ID
1078 FROM SO_LINES
1079 WHERE LINE_ID = V_LINE_ID
1080 OR SERVICE_PARENT_LINE_ID = V_LINE_ID);
1081
1082 BEGIN
1083 FOR not_full_rec IN not_full LOOP
1084 UPDATE SO_LINES L
1085 SET CANCELLED_QUANTITY =
1086 NVL(not_full_rec.CANCELLED_QUANTITY,0)+(V_REQUESTED_CANCEL_QTY *
1087 (not_full_rec.ORDERED_QUANTITY - NVL(not_full_rec.CANCELLED_QUANTITY,0))/
1088 (L_CURRENT_ORDERED_QTY - NVL(L_CURRENT_CANCELLED_QTY,0)))
1089 WHERE L.LINE_ID = not_full_rec.LINE_ID;
1090 END LOOP;
1091 END;
1092
1093 INSERT INTO SO_ORDER_CANCELLATIONS
1094 ( LINE_ID, HEADER_ID,
1095 CANCEL_CODE, CANCELLED_BY,
1096 CANCEL_DATE, LAST_UPDATED_BY,
1097 LAST_UPDATE_DATE, CANCEL_COMMENT,
1098 CANCELLED_QUANTITY, STATUS,
1099 CREATION_DATE, CREATED_BY,
1100 LAST_UPDATE_LOGIN )
1101 SELECT L.LINE_ID, L.HEADER_ID,
1102 V_CANCEL_CODE, v_current_user,
1103 SYSDATE, V_LAST_UPDATED_BY,
1104 SYSDATE, V_CANCEL_COMMENT,
1105 NVL(L.CANCELLED_QUANTITY,0) - NVL(SUM (SOC.CANCELLED_QUANTITY),0),
1106 V_STATUS, SYSDATE, v_current_user,
1107 V_LAST_UPDATE_LOGIN
1108 FROM SO_LINES L, SO_ORDER_CANCELLATIONS SOC
1109 WHERE L.LINE_ID IN
1110 (SELECT LINE_ID
1111 FROM SO_LINES
1112 WHERE PARENT_LINE_ID = V_REAL_PARENT_LINE_ID
1113 AND (NVL(COMPONENT_CODE,'0') LIKE V_COMPONENT_CODE || '%'
1114 OR COMPONENT_CODE IS NULL)
1115 AND V_SUBTREE_EXISTS = 1
1116 UNION
1117 SELECT LINE_ID
1118 FROM SO_LINES
1119 WHERE LINE_ID = V_LINE_ID
1120 OR SERVICE_PARENT_LINE_ID = V_LINE_ID)
1121 AND L.LINE_ID = SOC.LINE_ID(+)
1122 HAVING NVL(L.CANCELLED_QUANTITY,0) <> NVL(SUM (SOC.CANCELLED_QUANTITY),0)
1123 GROUP BY L.LINE_ID, L.HEADER_ID, L.CANCELLED_QUANTITY;
1124
1125 END IF;
1126
1127 /*
1128 ** Fix for Bug # 532221
1129 ** Update quantity_to_invoice for non-shippable items on line Cancellation.
1130 */
1131 UPDATE SO_LINES
1132 SET QUANTITY_TO_INVOICE =
1133 DECODE(NVL(QUANTITY_TO_INVOICE,0),
1134 0, QUANTITY_TO_INVOICE,
1135 DECODE(NVL(SHIPPED_QUANTITY,0),
1136 0,(ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0)),
1137 SHIPPED_QUANTITY))
1138 WHERE (LINE_ID = V_LINE_ID
1139 OR PARENT_LINE_ID = V_LINE_ID)
1140 AND (S4 + 0 IN (5, 7, 22, 8)
1141 OR S29 + 0 IN (14, 16, 8))
1142 AND EXISTS
1143 (SELECT 'NON SHIPPABLE ITEM'
1144 FROM MTL_SYSTEM_ITEMS MSI
1145 WHERE MSI.ORGANIZATION_ID = l_org_id
1146 AND MSI.INVENTORY_ITEM_ID = SO_LINES.INVENTORY_ITEM_ID
1147 AND MSI.SHIPPABLE_ITEM_FLAG = 'N');
1148
1149 UPDATE SO_LINES SET OPEN_FLAG =
1150 DECODE(nvl(ORDERED_QUANTITY,0) - NVL(CANCELLED_QUANTITY,0)
1151 ,0,'',OPEN_FLAG)
1152 WHERE LINE_ID IN
1153 (select line_id
1154 from so_lines
1155 where parent_line_id = V_REAL_PARENT_LINE_ID
1156 and (nvl(component_code,'0') like V_COMPONENT_CODE || '%'
1157 or component_code is null)
1158 and V_SUBTREE_EXISTS = 1
1159 union
1160 select line_id
1161 from so_lines
1162 where line_id = V_LINE_ID
1163 or service_parent_line_id = V_LINE_ID);
1164
1165 UPDATE SO_LINES
1166 SET LAST_UPDATED_BY = V_LAST_UPDATED_BY,
1167 LAST_UPDATE_LOGIN = V_LAST_UPDATE_LOGIN,
1168 LAST_UPDATE_DATE = SYSDATE,
1169 S1 = DECODE(OPEN_FLAG,'',DECODE(S1,18,8,S1),S1),
1170 S1_DATE = DECODE(OPEN_FLAG,'',DECODE(S1,18,sysdate,S1_DATE),S1_DATE),
1171 S2 = DECODE(OPEN_FLAG,'',DECODE(S2,18,8,S2),S2),
1172 S2_DATE = DECODE(OPEN_FLAG,'',DECODE(S2,18,sysdate,S2_DATE),S2_DATE),
1173 S3 = DECODE(OPEN_FLAG,'',DECODE(S3,18,8,S3),S3),
1174 S3_DATE = DECODE(OPEN_FLAG,'',DECODE(S3,18,sysdate,S3_DATE),S3_DATE),
1175 S4 = DECODE(OPEN_FLAG,'',DECODE(S4,18,8,S4),S4),
1176 S4_DATE = DECODE(OPEN_FLAG,'',DECODE(S4,18,sysdate,S4_DATE),S4_DATE),
1177 S5 = DECODE(OPEN_FLAG,'',DECODE(S5,18,8,S5),S5),
1178 S5_DATE = DECODE(OPEN_FLAG,'',DECODE(S5,18,sysdate,S5_DATE),S5_DATE),
1179 S6 = DECODE(OPEN_FLAG,'',DECODE(S6,18,8,S6),S6),
1180 S6_DATE = DECODE(OPEN_FLAG,'',DECODE(S6,18,sysdate,S6_DATE),S6_DATE),
1181 S7 = DECODE(OPEN_FLAG,'',DECODE(S7,18,8,S7),S7),
1182 S7_DATE = DECODE(OPEN_FLAG,'',DECODE(S7,18,sysdate,S7_DATE),S7_DATE),
1183 S8 = DECODE(OPEN_FLAG,'',DECODE(S8,18,8,S8),S8),
1184 S8_DATE = DECODE(OPEN_FLAG,'',DECODE(S8,18,sysdate,S8_DATE),S8_DATE),
1185 S9 = DECODE(OPEN_FLAG,'',11,5),
1186 S9_DATE = sysdate,
1187 S10 = DECODE(OPEN_FLAG,'',DECODE(S10,18,8,S10),S10),
1188 S10_DATE= DECODE(OPEN_FLAG,'',DECODE(S10,18,sysdate,S10_DATE),S10_DATE),
1189 S11 = DECODE(OPEN_FLAG,'',DECODE(S11,18,8,S11),S11),
1190 S11_DATE= DECODE(OPEN_FLAG,'',DECODE(S11,18,sysdate,S11_DATE),S11_DATE),
1191 S12 = DECODE(OPEN_FLAG,'',DECODE(S12,18,8,S12),S12),
1192 S12_DATE= DECODE(OPEN_FLAG,'',DECODE(S12,18,sysdate,S12_DATE),S12_DATE),
1193 S13 = DECODE(OPEN_FLAG,'',DECODE(S13,18,8,S13),S13),
1194 S13_DATE= DECODE(OPEN_FLAG,'',DECODE(S13,18,sysdate,S13_DATE),S13_DATE),
1195 S14 = DECODE(OPEN_FLAG,'',DECODE(S14,18,8,S14),S14),
1196 S14_DATE= DECODE(OPEN_FLAG,'',DECODE(S14,18,sysdate,S14_DATE),S14_DATE),
1197 S15 = DECODE(OPEN_FLAG,'',DECODE(S15,18,8,S15),S15),
1198 S15_DATE= DECODE(OPEN_FLAG,'',DECODE(S15,18,sysdate,S15_DATE),S15_DATE),
1199 S16 = DECODE(OPEN_FLAG,'',DECODE(S16,18,8,S16),S16),
1200 S16_DATE= DECODE(OPEN_FLAG,'',DECODE(S16,18,sysdate,S16_DATE),S16_DATE),
1201 S17 = DECODE(OPEN_FLAG,'',DECODE(S17,18,8,S17),S17),
1202 S17_DATE= DECODE(OPEN_FLAG,'',DECODE(S17,18,sysdate,S17_DATE),S17_DATE),
1203 S18 = DECODE(OPEN_FLAG,'',DECODE(S18,18,8,S18),S18),
1204 S18_DATE= DECODE(OPEN_FLAG,'',DECODE(S18,18,sysdate,S18_DATE),S18_DATE),
1205 S19 = DECODE(OPEN_FLAG,'',DECODE(S19,18,8,S19),S19),
1206 S19_DATE= DECODE(OPEN_FLAG,'',DECODE(S19,18,sysdate,S19_DATE),S19_DATE),
1207 S20 = DECODE(OPEN_FLAG,'',DECODE(S20,18,8,S20),S20),
1208 S20_DATE= DECODE(OPEN_FLAG,'',DECODE(S20,18,sysdate,S20_DATE),S20_DATE),
1209 S21 = DECODE(OPEN_FLAG,'',DECODE(S21,18,8,S21),S21),
1210 S21_DATE= DECODE(OPEN_FLAG,'',DECODE(S21,18,sysdate,S21_DATE),S21_DATE),
1211 S22 = DECODE(OPEN_FLAG,'',DECODE(S22,18,8,S22),S22),
1212 S22_DATE= DECODE(OPEN_FLAG,'',DECODE(S22,18,sysdate,S22_DATE),S22_DATE),
1213 S23 = DECODE(OPEN_FLAG,'',DECODE(S23,18,8,S23),S23),
1214 S23_DATE= DECODE(OPEN_FLAG,'',DECODE(S23,18,sysdate,S23_DATE),S23_DATE),
1215 S24 = DECODE(OPEN_FLAG,'',DECODE(S24,18,8,S24),S24),
1216 S24_DATE= DECODE(OPEN_FLAG,'',DECODE(S24,18,sysdate,S24_DATE),S24_DATE),
1217 S25 = DECODE(OPEN_FLAG,'',DECODE(S25,18,8,S25),S25),
1218 S25_DATE= DECODE(OPEN_FLAG,'',DECODE(S25,18,sysdate,S25_DATE),S25_DATE),
1219 S26 = DECODE(OPEN_FLAG,'',DECODE(S26,18,8,S26),S26),
1220 S26_DATE= DECODE(OPEN_FLAG,'',DECODE(S26,18,sysdate,S26_DATE),S26_DATE),
1221 S27 = DECODE(OPEN_FLAG,'',DECODE(S27,18,8,S27),S27),
1222 S27_DATE= DECODE(OPEN_FLAG,'',DECODE(S27,18,sysdate,S27_DATE),S27_DATE),
1223 S28 = DECODE(OPEN_FLAG,'',DECODE(S28,18,8,S28),S28),
1224 S28_DATE= DECODE(OPEN_FLAG,'',DECODE(S28,18,sysdate,S28_DATE),S28_DATE),
1225 S29 = DECODE(OPEN_FLAG,'',DECODE(S29,18,8,S29),S29),
1226 S29_DATE= DECODE(OPEN_FLAG,'',DECODE(S29,18,sysdate,S29_DATE),S29_DATE),
1227 S30 = DECODE(OPEN_FLAG,'',DECODE(S30,18,8,S30),S30),
1228 S30_DATE= DECODE(OPEN_FLAG,'',DECODE(S30,18,sysdate,S30_DATE),S30_DATE)
1229 WHERE LINE_ID IN
1230 (select line_id from so_lines
1231 where parent_line_id = V_REAL_PARENT_LINE_ID
1232 and (nvl(component_code,'0') like V_COMPONENT_CODE || '%'
1233 or component_code is null)
1234 and V_SUBTREE_EXISTS = 1
1235 union
1236 select line_id
1237 from so_lines
1238 where line_id = V_LINE_ID
1239 or service_parent_line_id = V_LINE_ID);
1240
1241 UPDATE SO_LINES
1242 SET S3 = DECODE(S3,18,8,S3)
1243 WHERE LINE_ID IN
1244 (select line_id from so_lines
1245 where parent_line_id = V_REAL_PARENT_LINE_ID
1246 and (nvl(component_code,'0') like V_COMPONENT_CODE || '%'
1247 or component_code is null)
1248 and V_SUBTREE_EXISTS = 1
1249 union
1250 select line_id
1251 from so_lines
1252 where line_id = V_LINE_ID
1253 or service_parent_line_id = V_LINE_ID)
1254 AND NOT EXISTS
1255 (SELECT 'BACKORDERED PICKING LINES'
1256 FROM SO_PICKING_LINES
1257 WHERE ORDER_LINE_ID = SO_LINES.LINE_ID
1258 AND PICKING_HEADER_ID = 0
1259 AND ORIGINAL_REQUESTED_QUANTITY > NVL(CANCELLED_QUANTITY,0));
1260
1261 V_RESULT := 1;
1262
1263 END UPDATE_LINE_INFO;
1264
1265 ------------------------------------------------------------------------
1266 -- procedure UPDATE_MODEL_INFO
1267 --
1268 -- Update so_lines by setting the cancelled_quantity = the
1269 -- requested quantity and previous cancelled_quantity. Also set the
1270 -- open_flag to '' if ordered_quantity equals cancelled_quantity.
1271 -- If no backordered picking lines exist where the original requested
1272 -- quantity is greater than the cancelled quantity then set S3
1273 -- (Backorder Release) to 8 (Not Applicable) if it was 18 (Eligible).
1274 -- Insert a record into so_order_cancellations.
1275 --
1276 -- RETURNS
1277 -- 1 -> success
1278 ------------------------------------------------------------------------
1279
1280 procedure UPDATE_MODEL_INFO(
1281 V_LINE_ID IN NUMBER
1282 , V_REQUESTED_CANCEL_QTY IN NUMBER
1283 , V_CANCEL_COMMENT IN LONG
1284 , V_CANCEL_CODE IN VARCHAR2
1285 , V_STATUS IN VARCHAR2
1286 , V_LAST_UPDATED_BY IN NUMBER
1287 , V_LAST_UPDATE_LOGIN IN NUMBER
1288 , V_HEADER_ID IN NUMBER
1289 , V_FULL IN NUMBER
1290 , V_RESULT OUT NUMBER
1291 )
1292 IS
1293
1294 DUMMY NUMBER;
1295 TEMP BOOLEAN;
1296 v_current_user NUMBER;
1297
1298 BEGIN
1299
1300 v_current_user := to_number(FND_PROFILE.VALUE('USER_ID'));
1301
1302 -- If there are open pick slips against this line (or its included items),
1303 -- we fail the whole 'Clean empty classes' process and issue an error message.
1304
1305 SELECT COUNT(*)
1306 INTO DUMMY
1307 FROM SO_PICKING_LINES L,
1308 SO_PICKING_HEADERS H
1309 WHERE L.ORDER_LINE_ID = V_LINE_ID
1310 AND L.PICKING_HEADER_ID = H.PICKING_HEADER_ID
1311 AND H.STATUS_CODE IN ('OPEN' ,'PENDING', 'IN PROGRESS');
1312
1313 IF (DUMMY >= 1) THEN
1314 TEMP := OE_MSG.SET_MESSAGE_NAME ('OE_CANCEL_CLASS_OPENSLP');
1315 V_RESULT := 0;
1316 RETURN;
1317 END IF;
1318
1319 UPDATE SO_LINES SET
1320 CANCELLED_QUANTITY =
1321 V_REQUESTED_CANCEL_QTY + NVL(CANCELLED_QUANTITY,0)
1322 WHERE LINE_ID = V_LINE_ID;
1323
1324 UPDATE SO_LINES SET
1325 OPEN_FLAG =
1326 DECODE ((nvl(ORDERED_QUANTITY,0) - NVL(CANCELLED_QUANTITY,0)),
1327 0, '', OPEN_FLAG)
1328 WHERE LINE_ID = V_LINE_ID;
1329
1330 UPDATE SO_LINES SET
1331 LAST_UPDATED_BY = V_LAST_UPDATED_BY,
1332 LAST_UPDATE_LOGIN = V_LAST_UPDATE_LOGIN,
1333 LAST_UPDATE_DATE = SYSDATE,
1334 S1 = DECODE(OPEN_FLAG,'',DECODE(S1,18,8,S1),S1),
1335 S2 = DECODE(OPEN_FLAG,'',DECODE(S2,18,8,S2),S2),
1336 S3 = DECODE(OPEN_FLAG,'',DECODE(S3,18,8,S3),S3),
1337 S4 = DECODE(OPEN_FLAG,'',DECODE(S4,18,8,S4),S4),
1338 S5 = DECODE(OPEN_FLAG,'',DECODE(S5,18,8,S5),S5),
1339 S6 = DECODE(OPEN_FLAG,'',DECODE(S6,18,8,S6),S6),
1340 S7 = DECODE(OPEN_FLAG,'',DECODE(S7,18,8,S7),S7),
1341 S8 = DECODE(OPEN_FLAG,'',DECODE(S8,18,8,S8),S8),
1342 S9 = DECODE(OPEN_FLAG,'',11,5),
1343 S10 = DECODE(OPEN_FLAG,'',DECODE(S10,18,8,S10),S10),
1344 S11 = DECODE(OPEN_FLAG,'',DECODE(S11,18,8,S11),S11),
1345 S12 = DECODE(OPEN_FLAG,'',DECODE(S12,18,8,S12),S12),
1346 S13 = DECODE(OPEN_FLAG,'',DECODE(S13,18,8,S13),S13),
1347 S14 = DECODE(OPEN_FLAG,'',DECODE(S14,18,8,S14),S14),
1348 S15 = DECODE(OPEN_FLAG,'',DECODE(S15,18,8,S15),S15),
1349 S16 = DECODE(OPEN_FLAG,'',DECODE(S16,18,8,S16),S16),
1350 S17 = DECODE(OPEN_FLAG,'',DECODE(S17,18,8,S17),S17),
1351 S18 = DECODE(OPEN_FLAG,'',DECODE(S18,18,8,S18),S18),
1352 S19 = DECODE(OPEN_FLAG,'',DECODE(S19,18,8,S19),S19),
1353 S20 = DECODE(OPEN_FLAG,'',DECODE(S20,18,8,S20),S20),
1354 S21 = DECODE(OPEN_FLAG,'',DECODE(S21,18,8,S21),S21),
1355 S22 = DECODE(OPEN_FLAG,'',DECODE(S22,18,8,S22),S22),
1356 S23 = DECODE(OPEN_FLAG,'',DECODE(S23,18,8,S23),S23),
1357 S24 = DECODE(OPEN_FLAG,'',DECODE(S24,18,8,S24),S24),
1358 S25 = DECODE(OPEN_FLAG,'',DECODE(S25,18,8,S25),S25),
1359 S26 = DECODE(OPEN_FLAG,'',DECODE(S26,18,8,S26),S26),
1360 S27 = DECODE(OPEN_FLAG,'',DECODE(S27,18,8,S27),S27),
1361 S28 = DECODE(OPEN_FLAG,'',DECODE(S28,18,8,S28),S28),
1362 S29 = DECODE(OPEN_FLAG,'',DECODE(S29,18,8,S29),S29),
1363 S30 = DECODE(OPEN_FLAG,'',DECODE(S30,18,8,S30),S30)
1364 WHERE LINE_ID = V_LINE_ID;
1365
1366 -- BACKORDERED PICKING LINES EXIST
1367
1368 SELECT COUNT(*)
1369 INTO DUMMY
1370 FROM SO_PICKING_LINES
1371 WHERE PICKING_HEADER_ID = 0
1372 AND ORIGINAL_REQUESTED_QUANTITY > NVL(CANCELLED_QUANTITY,0)
1373 AND ORDER_LINE_ID = V_LINE_ID;
1374
1375 IF (DUMMY >= 1) THEN
1376 NULL;
1377 ELSE
1378 UPDATE SO_LINES
1379 SET S3 = DECODE(S3,18,8,S3)
1380 WHERE LINE_ID = V_LINE_ID;
1381 END IF;
1382
1383 UPDATE SO_LINES
1384 SET S1_DATE = DECODE(OPEN_FLAG,'',
1385 DECODE(S1,8,SYSDATE,S1_DATE),S1_DATE),
1386 S2_DATE = DECODE(OPEN_FLAG,'',
1387 DECODE(S2,8,SYSDATE,S2_DATE),S2_DATE),
1388 S3_DATE = DECODE(S3,8,SYSDATE,S3_DATE),
1389 S4_DATE = DECODE(OPEN_FLAG,'',
1390 DECODE(S4,8,SYSDATE,S4_DATE),S4_DATE),
1391 S5_DATE = DECODE(OPEN_FLAG,'',
1392 DECODE(S5,8,SYSDATE,S5_DATE),S5_DATE),
1393 S6_DATE = DECODE(OPEN_FLAG,'',
1394 DECODE(S6,8,SYSDATE,S6_DATE),S6_DATE),
1395 S7_DATE = DECODE(OPEN_FLAG,'',
1396 DECODE(S7,8,SYSDATE,S7_DATE),S7_DATE),
1397 S8_DATE = DECODE(OPEN_FLAG,'',
1398 DECODE(S8,8,SYSDATE,S8_DATE),S8_DATE),
1399 S9_DATE = SYSDATE,
1400 S10_DATE=DECODE(OPEN_FLAG,'',
1401 DECODE(S10,8,SYSDATE,S10_DATE),S10_DATE),
1402 S11_DATE=DECODE(OPEN_FLAG,'',
1406 S13_DATE=DECODE(OPEN_FLAG,'',
1403 DECODE(S11,8,SYSDATE,S11_DATE),S11_DATE),
1404 S12_DATE=DECODE(OPEN_FLAG,'',
1405 DECODE(S12,8,SYSDATE,S12_DATE),S12_DATE),
1407 DECODE(S13,8,SYSDATE,S13_DATE),S13_DATE),
1408 S14_DATE=DECODE(OPEN_FLAG,'',
1409 DECODE(S14,8,SYSDATE,S14_DATE),S14_DATE),
1410 S15_DATE=DECODE(OPEN_FLAG,'',
1411 DECODE(S15,8,SYSDATE,S15_DATE),S15_DATE),
1412 S16_DATE=DECODE(OPEN_FLAG,'',
1413 DECODE(S16,8,SYSDATE,S16_DATE),S16_DATE),
1414 S17_DATE=DECODE(OPEN_FLAG,'',
1415 DECODE(S17,8,SYSDATE,S17_DATE),S17_DATE),
1416 S18_DATE=DECODE(OPEN_FLAG,'',
1417 DECODE(S18,8,SYSDATE,S18_DATE),S18_DATE),
1418 S19_DATE=DECODE(OPEN_FLAG,'',
1419 DECODE(S19,8,SYSDATE,S19_DATE),S19_DATE),
1420 S20_DATE=DECODE(OPEN_FLAG,'',
1421 DECODE(S20,8,SYSDATE,S20_DATE),S20_DATE),
1422 S21_DATE=DECODE(OPEN_FLAG,'',
1423 DECODE(S21,8,SYSDATE,S21_DATE),S21_DATE),
1424 S22_DATE=DECODE(OPEN_FLAG,'',
1425 DECODE(S22,8,SYSDATE,S22_DATE),S22_DATE),
1426 S23_DATE=DECODE(OPEN_FLAG,'',
1427 DECODE(S23,8,SYSDATE,S23_DATE),S23_DATE),
1428 S24_DATE=DECODE(OPEN_FLAG,'',
1429 DECODE(S24,8,SYSDATE,S24_DATE),S24_DATE),
1430 S25_DATE=DECODE(OPEN_FLAG,'',
1431 DECODE(S25,8,SYSDATE,S25_DATE),S25_DATE),
1432 S26_DATE=DECODE(OPEN_FLAG,'',
1433 DECODE(S26,8,SYSDATE,S26_DATE),S26_DATE),
1434 S27_DATE=DECODE(OPEN_FLAG,'',
1435 DECODE(S27,8,SYSDATE,S27_DATE),S27_DATE),
1436 S28_DATE=DECODE(OPEN_FLAG,'',
1437 DECODE(S28,8,SYSDATE,S28_DATE),S28_DATE),
1438 S29_DATE=DECODE(OPEN_FLAG,'',
1439 DECODE(S29,8,SYSDATE,S29_DATE),S29_DATE),
1440 S30_DATE=DECODE(OPEN_FLAG,'' ,
1441 DECODE(S30,8,SYSDATE,S30_DATE),S30_DATE)
1442 WHERE LINE_ID = V_LINE_ID;
1443
1444 INSERT INTO SO_ORDER_CANCELLATIONS
1445 ( LINE_ID, HEADER_ID,
1446 CANCEL_CODE, CANCELLED_BY,
1447 CANCEL_DATE, LAST_UPDATED_BY,
1448 LAST_UPDATE_DATE, CANCEL_COMMENT,
1449 CANCELLED_QUANTITY, STATUS,
1450 CREATION_DATE, CREATED_BY,
1451 LAST_UPDATE_LOGIN )
1452 VALUES( V_LINE_ID, V_HEADER_ID,
1453 V_CANCEL_CODE, v_current_user,
1454 SYSDATE, V_LAST_UPDATED_BY,
1455 SYSDATE, V_CANCEL_COMMENT,
1456 V_REQUESTED_CANCEL_QTY,
1457 V_STATUS, SYSDATE, v_current_user,
1458 V_LAST_UPDATE_LOGIN );
1459
1460 V_RESULT := 1;
1461
1462 END UPDATE_MODEL_INFO;
1463
1464 ------------------------------------------------------------------------
1465 -- procedure LOAD_BOM
1466 --
1467 -- call the bom exploder
1468 --
1469 -- RETURNS
1470 -- 1 -> successful
1471 -- 0 -> unsuccessful
1472 ------------------------------------------------------------------------
1473
1474 procedure LOAD_BOM(
1475 V_SO_ORGANIZATION_ID IN NUMBER
1476 , V_TOP_INVENTORY_ITEM_ID IN NUMBER
1477 , V_TOP_COMPONENT_CODE IN VARCHAR2
1478 , V_CREATION_DATE_TIME IN VARCHAR2
1479 , V_LAST_UPDATED_BY IN NUMBER
1480 , V_RESULT OUT NUMBER
1481 )
1482 IS
1483 X_BOM_MESSAGE VARCHAR2(500) := '';
1484 X_BOM_RESULT NUMBER;
1485 V_BOM_EXPLOSION_GROUP_ID NUMBER;
1486 x BOOLEAN;
1487
1488 BEGIN
1489
1490 V_RESULT := 0;
1491
1492 BOMPNORD.BMXPORDER_EXPLODE_FOR_ORDER
1493 ( ORG_ID => V_SO_ORGANIZATION_ID
1494 , COPY_FLAG => 2
1495 , EXPL_TYPE => 'OPTIONAL'
1496 , ORDER_BY => 2
1497 , GRP_ID => V_BOM_EXPLOSION_GROUP_ID
1498 , SESSION_ID => 0
1499 , LEVELS_TO_EXPLODE => 60
1500 , ITEM_ID => V_TOP_INVENTORY_ITEM_ID
1501 , COMP_CODE => V_TOP_COMPONENT_CODE
1502 /* The foll. line has been commented and changed to the line below it.
1503 * This has been done to fix bug# 912073.
1504 */
1505 -- , REV_DATE => substr(V_CREATION_DATE_TIME, 1, 15)
1506 , REV_DATE => substr(V_CREATION_DATE_TIME, 1, 16)
1507 , USER_ID => V_LAST_UPDATED_BY
1508 , ERR_MSG => X_BOM_MESSAGE
1509 , ERROR_CODE => X_BOM_RESULT);
1510
1511 IF (X_BOM_RESULT = 0) THEN
1512 V_RESULT := 1;
1513 ELSE
1514 x :=OE_MSG.SET_BUFFER_MESSAGE('OE_BOM_EXPLOSION_FAILED', 'REASON', X_BOM_RESULT);
1515 -- issue message 'OE_BOM_EXPLOSION_FAILED'
1516 -- REASON=:WORLD.BOM_MESSAGE
1517 V_RESULT := 0;
1518 END IF;
1519
1520 END LOAD_BOM;
1521
1522 ------------------------------------------------------------------------
1523 -- procedure CHECK_MODEL_RATIOS
1524 --
1525 -- If this is a FULL cancellation, don't check the ratios.
1526 -- If this is a return line, don't check the ratios.
1527 -- If this is an option line, make sure the user only cancels in
1528 -- multiples of the model if it's not a full cancellation.
1529 -- Check to make sure that the quantity selected is a multiple of
1530 -- parent quantity.
1531 -- If it is a multiple of parent quantity check to see if we are
1532 -- dealing with a ATO model and if so call the bom exploder.
1533 -- Make sure that we are not cancelling the last option a mandatory class.
1534 -- Make sure that we are not cancelling a mandatory class.
1535 -- Also make sure that we have not gone below the minimum required
1536 -- quantity for the option selected.
1537 --
1538 -- RETURNS
1539 -- 1 -> successful
1540 ------------------------------------------------------------------------
1541
1542 procedure CHECK_MODEL_RATIOS(
1543 V_LINE_ID IN NUMBER
1544 , V_REQUESTED_CANCEL_QTY IN NUMBER
1545 , V_LINE_TYPE_CODE IN VARCHAR2
1546 , V_OPTION_FLAG IN NUMBER
1547 , V_LINK_TO_LINE_ID IN NUMBER
1548 , V_ORDER_QTY IN NUMBER
1549 , V_CANCELLED_QTY IN NUMBER
1550 , V_FULL IN NUMBER
1551 , V_ATO_FLAG IN NUMBER
1552 , V_SO_ORGANIZATION_ID IN NUMBER
1553 , V_TOP_BILL_SEQUENCE_ID IN NUMBER
1554 , V_PARENT_COMPONENT_SEQUENCE_ID IN NUMBER
1555 , V_COMPONENT_SEQUENCE_ID IN NUMBER
1556 , V_TOP_INVENTORY_ITEM_ID IN NUMBER
1557 , V_TOP_COMPONENT_CODE IN VARCHAR2
1558 , V_CREATION_DATE_TIME IN VARCHAR2
1559 , V_LAST_UPDATED_BY IN NUMBER
1560 , V_RESULT OUT NUMBER
1561 )
1562 IS
1563 DUMMY NUMBER;
1564 DUMMY2 NUMBER;
1565 DUMMY3 NUMBER;
1566 DUMMY4 NUMBER;
1567 LOAD_BOM_RESULT NUMBER := 0;
1568 TEMP_QTY NUMBER := 0;
1569 x BOOLEAN;
1570
1571 BEGIN
1572
1573 V_RESULT := 0;
1574
1575 IF (V_FULL = 1) THEN
1576 V_RESULT := 1;
1577 RETURN;
1578 END IF;
1579
1580 IF (V_LINE_TYPE_CODE = 'RETURN') THEN
1581 V_RESULT := 1;
1582 RETURN;
1583 END IF;
1584
1585 IF (V_OPTION_FLAG = 0) THEN
1586 V_RESULT := 1;
1587 RETURN;
1588 END IF;
1589
1590 SELECT COUNT(*)
1591 INTO DUMMY
1592 FROM SO_LINES M
1593 WHERE M.LINE_ID = V_LINK_TO_LINE_ID
1594 AND MOD((V_ORDER_QTY
1595 - NVL(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY),
1596 (M.ORDERED_QUANTITY - nvl(M.CANCELLED_QUANTITY,0))) = 0;
1597
1598 IF (DUMMY >= 1) THEN
1599 IF (V_ATO_FLAG = 1) THEN
1600 IF (V_TOP_COMPONENT_CODE is NULL) THEN
1601 V_RESULT := 1;
1602 RETURN;
1603 ELSE
1604 OE_CANCEL.LOAD_BOM(
1605 V_SO_ORGANIZATION_ID,
1606 V_TOP_INVENTORY_ITEM_ID,
1607 V_TOP_COMPONENT_CODE,
1608 V_CREATION_DATE_TIME,
1609 V_LAST_UPDATED_BY,
1610 LOAD_BOM_RESULT);
1611 END IF;
1612 ELSE
1613 V_RESULT := 1;
1614 RETURN;
1615 END IF;
1616 ELSE
1617 -- You must select a quantity that is a multiple of the parent quantity.
1618 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_OPN_NOT_RATIO');
1619 V_RESULT := 0;
1620 RETURN;
1621 END IF;
1622
1623 IF (LOAD_BOM_RESULT = 1) THEN
1624
1625 SELECT COUNT(*)
1626 INTO DUMMY2
1627 FROM BOM_EXPLOSIONS
1628 WHERE TOP_BILL_SEQUENCE_ID = V_TOP_BILL_SEQUENCE_ID
1629 AND EXPLOSION_TYPE = 'OPTIONAL'
1630 AND PLAN_LEVEL >= 0
1631 AND EFFECTIVITY_DATE <= TO_DATE(
1632 NVL(substr(V_CREATION_DATE_TIME, 1, 16),
1633 TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
1634 'YYYY/MM/DD HH24:MI')
1635 AND DISABLE_DATE > TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
1636 TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
1637 'YYYY/MM/DD HH24:MI')
1638 AND COMPONENT_SEQUENCE_ID = V_PARENT_COMPONENT_SEQUENCE_ID
1639 AND OPTIONAL = 2
1640 AND (V_ORDER_QTY - NVL(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY) = 0
1641 AND NOT EXISTS
1642 (SELECT 'X'
1643 FROM SO_LINES
1644 WHERE LINK_TO_LINE_ID = V_LINK_TO_LINE_ID
1645 AND LINE_ID <> V_LINE_ID
1646 AND ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0) > 0);
1647
1648 IF (DUMMY2 >= 1) THEN
1649 -- You may not cancel the last option in this mandatory class.
1650 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_LAST_OPT');
1651 V_RESULT := 0;
1652 RETURN;
1653 END IF;
1654
1655 SELECT COUNT(*)
1656 INTO DUMMY3
1657 FROM BOM_EXPLOSIONS
1658 WHERE TOP_BILL_SEQUENCE_ID = V_TOP_BILL_SEQUENCE_ID
1659 AND EXPLOSION_TYPE = 'OPTIONAL'
1660 AND PLAN_LEVEL >= 0
1661 AND EFFECTIVITY_DATE <= TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
1662 TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
1663 'YYYY/MM/DD HH24:MI')
1664 AND DISABLE_DATE > TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
1665 TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
1666 'YYYY/MM/DD HH24:MI')
1667 AND COMPONENT_SEQUENCE_ID = V_COMPONENT_SEQUENCE_ID
1668 AND OPTIONAL = 2
1669 AND (V_ORDER_QTY - NVL(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY) = 0;
1670
1671
1672 IF ( DUMMY3 >= 1) THEN
1673 -- You may not cancel a mandatory class.
1674 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_MAND_CLASS');
1675 V_RESULT := 0;
1676 RETURN;
1677 END IF;
1678
1679 TEMP_QTY := V_ORDER_QTY - nvl(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY;
1680
1681 IF(TEMP_QTY = 0) THEN
1682 V_RESULT := 1;
1683 RETURN;
1684 END IF;
1685
1686 -- 'ORDERED QUANTITY OUT OF BOM RANGE'
1687
1688 SELECT COUNT(*)
1689 INTO DUMMY4
1690 FROM BOM_EXPLOSIONS
1691 WHERE TOP_BILL_SEQUENCE_ID = V_TOP_BILL_SEQUENCE_ID
1692 AND EXPLOSION_TYPE = 'OPTIONAL'
1693 AND PLAN_LEVEL >= 0
1694 AND EFFECTIVITY_DATE <= TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
1695 TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
1696 'YYYY/MM/DD HH24:MI')
1697 AND DISABLE_DATE > TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
1698 TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
1699 'YYYY/MM/DD HH24:MI')
1700 AND COMPONENT_SEQUENCE_ID = V_COMPONENT_SEQUENCE_ID
1701 AND NVL(LOW_QUANTITY,0) <=
1702 (SELECT (V_ORDER_QTY - NVL(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY) /
1703 (P.ORDERED_QUANTITY - NVL(P.CANCELLED_QUANTITY,0))
1704 FROM SO_LINES P
1705 WHERE P.LINE_ID = V_LINK_TO_LINE_ID);
1706
1707
1708 IF (DUMMY4 = 0) THEN
1709 -- You have gone below the minimum required quantity for this option.
1710 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_ATO_LOW');
1711 V_RESULT := 0;
1712 RETURN;
1713 ELSE
1714 V_RESULT := 1;
1715 RETURN;
1716 END IF;
1717 ELSE
1718 V_RESULT := 0;
1719 RETURN;
1720 END IF;
1721 END CHECK_MODEL_RATIOS;
1722
1723
1724 END OE_CANCEL;
[Home] [Help]