[Home] [Help]
PACKAGE BODY: APPS.OE_CANSRV
Source
1 PACKAGE BODY OE_CANSRV AS
2 /* $Header: OECANSVB.pls 115.4 99/07/16 08:10:37 porting shi $ */
3
4 ------------------------------------------------------------------------
5 -- procedure CHECK_LINE_INTERFACED
6 --
7 -- See if the line in question is interfaced to service
8 -- If S25 (Service Interface) is not equal to 8 (Not Applicable) or
9 -- 18 (Eligible) then the line is interfaced.
10 --
11 -- RETURNS
12 -- 1 -> line is service interfaced
13 -- 0 -> line is not service interfaced.
14 ------------------------------------------------------------------------
15 procedure CHECK_LINE_INTERFACED(
16 V_LINE_ID IN NUMBER
17 , V_RESULT OUT NUMBER
18 )
19 IS
20 DUMMY NUMBER :=0;
21
22 BEGIN
23
24 SELECT COUNT(*)
25 INTO DUMMY
26 FROM SO_LINES
27 WHERE LINE_ID = V_LINE_ID
28 AND S25 NOT IN (8,18);
29
30 IF DUMMY >= 1 THEN
31 V_RESULT := 1;
32 RETURN;
33 END IF;
34
35 V_RESULT := 0;
36 END CHECK_LINE_INTERFACED;
37
38 ------------------------------------------------------------------------
39 -- procedure CHECK_ORDER_INT_RECS_EXIST
40 --
41 -- get the concurrent_process_id from the cs_orders_interface
42 -- if a row is not found return null for the concurrent_process_id
43 --
44 -- RETURNS
45 -- 1 -> success
46 -- CONCURRENT_PROCESS_ID
47 ------------------------------------------------------------------------
48 procedure CHECK_ORDER_INT_RECS_EXIST(
49 V_LINE_ID IN NUMBER
50 , V_CONCURRENT_PROCESS_ID IN OUT NUMBER
51 , V_RESULT OUT NUMBER
52 )
53 IS
54 DUMMY NUMBER := '';
55
56 BEGIN
57
58 SELECT CONCURRENT_PROCESS_ID
59 INTO DUMMY
60 FROM CS_ORDERS_INTERFACE
61 WHERE LINE_ID = V_LINE_ID
62 AND TRANSACTION_CODE IN ('ORDER', 'RENEW');
63
64 IF DUMMY IS NOT NULL THEN
65 V_CONCURRENT_PROCESS_ID := DUMMY;
66 V_RESULT := 1;
67 RETURN;
68 END IF;
69
70 V_RESULT :=0;
71 END CHECK_ORDER_INT_RECS_EXIST;
72
73 ------------------------------------------------------------------------
74 -- procedure CHECK_ORDER_INT_NOT_IN_PROG
75 ------------------------------------------------------------------------
76 procedure CHECK_ORDER_INT_NOT_IN_PROG(
77 V_LINE_ID IN NUMBER
78 , V_PRINT_ERR_MSG IN NUMBER
79 , V_CONCURRENT_PROCESS_ID IN OUT NUMBER
80 , V_RESULT OUT NUMBER
81 )
82 IS
83 CONCURRENT_PROCESS_ID_TEMP NUMBER :='';
84 RESULT1 NUMBER :='';
85 x BOOLEAN;
86 BEGIN
87
88 OE_CANSRV.CHECK_ORDER_INT_RECS_EXIST(
89 V_LINE_ID, CONCURRENT_PROCESS_ID_TEMP, RESULT1);
90
91 IF RESULT1 = 1 THEN
92 V_RESULT := 1;
93 RETURN;
94 END IF;
95
96 IF V_PRINT_ERR_MSG = 1 THEN
97 x :=OE_MSG.SET_MESSAGE_NAME('OE_CAN_LINE_IN_PROCESS');
98 -- This line is being processed. Please make this change later.
99 END IF;
100
101 V_RESULT := 0;
102 END CHECK_ORDER_INT_NOT_IN_PROG;
103
104
105 ------------------------------------------------------------------------
106 -- procedure MAKE_DELETE_INT_RECS
107 ------------------------------------------------------------------------
108 procedure MAKE_DELETE_INT_RECS(
109 V_LINE_ID IN NUMBER
110 , V_CUSTOMER_PRODUCT_ID IN NUMBER
111 , V_CP_SERVICE_ID IN NUMBER
112 , V_LAST_UPDATED_BY IN NUMBER
113 , V_SERVICE_MASS_TXN_TEMP_ID IN NUMBER
114 , V_RESULT OUT NUMBER
115 )
116 IS
117 DUMMY NUMBER :='';
118 DUMMY2 NUMBER :='';
119
120 BEGIN
121
122 V_RESULT := 0;
123
124 SELECT COUNT(*)
125 INTO DUMMY
126 FROM SO_LINES
127 WHERE LINE_ID = V_LINE_ID
128 AND (CP_SERVICE_ID IS NOT NULL
129 AND SERVICE_MASS_TXN_TEMP_ID IS NOT NULL);
130
131 IF DUMMY >= 1 THEN
132
133 INSERT INTO CS_ORDERS_INTERFACE
134 (ORDER_INTERFACE_ID,
135 CREATED_BY,
136 CREATION_DATE,
137 SERVICE_ITEM_FLAG,
138 TRANSACTION_CODE,
139 LINE_ID,
140 CP_SERVICE_ID,
141 MASS_RENEW_TXN_TEMP_ID)
142 VALUES
143 (CS_ORDERS_INTERFACE_S.NEXTVAL,
144 V_LAST_UPDATED_BY,
145 SYSDATE,
146 'Y',
147 'DELETE',
148 V_LINE_ID,
149 V_CP_SERVICE_ID,
150 V_SERVICE_MASS_TXN_TEMP_ID);
151
152 V_RESULT := 1;
153 RETURN;
154 END IF;
155
156 SELECT COUNT(*)
157 INTO DUMMY2
158 FROM SO_LINES
159 WHERE LINE_ID = V_LINE_ID
160 AND (CUSTOMER_PRODUCT_ID IS NOT NULL
161 AND SERVICE_MASS_TXN_TEMP_ID IS NOT NULL);
162
163 IF DUMMY2 >= 1 THEN
164
165 INSERT INTO CS_ORDERS_INTERFACE
166 (ORDER_INTERFACE_ID,
167 CREATED_BY,
168 CREATION_DATE,
169 SERVICE_ITEM_FLAG,
170 TRANSACTION_CODE,
171 LINE_ID,
172 CANCEL_CP_ID,
173 MASS_RENEW_TXN_TEMP_ID)
174 VALUES
175 (CS_ORDERS_INTERFACE_S.NEXTVAL,
176 V_LAST_UPDATED_BY,
177 SYSDATE,
178 'N',
179 'DELETE',
180 V_LINE_ID,
181 V_CUSTOMER_PRODUCT_ID,
182 V_SERVICE_MASS_TXN_TEMP_ID);
183
184 V_RESULT := 1;
185 RETURN;
186 END IF;
187
188 END MAKE_DELETE_INT_RECS;
189
190
191 ------------------------------------------------------------------------
192 -- procedure CANCEL_SERVICE_CHILDREN
193 ------------------------------------------------------------------------
194 procedure CANCEL_SERVICE_CHILDREN(
195 V_LINE_ID IN NUMBER
196 , V_HEADER_ID IN NUMBER
197 , V_CANCEL_CODE IN VARCHAR2
198 , V_CANCEL_COMMENT IN LONG
199 , V_FULL IN NUMBER
200 , V_STATUS IN VARCHAR2
201 , V_REQUESTED_CANCEL_QTY IN NUMBER
202 , V_CUSTOMER_PRODUCT_ID IN NUMBER
203 , V_CP_SERVICE_ID IN NUMBER
204 , V_LAST_UPDATED_BY IN NUMBER
205 , V_LAST_UPDATE_LOGIN IN NUMBER
206 , V_SERVICE_MASS_TXN_TEMP_ID IN NUMBER
207 , V_PRINT_ERR_MSG IN NUMBER
208 , V_CONCURRENT_PROCESS_ID IN OUT NUMBER
209 , V_RESULT OUT NUMBER
210 )
211 IS
212 CHECK_LINE_INTERFACE_RESULT NUMBER := 0;
213 CHECK_ORDER_INT_RECS_RESULT NUMBER := 0;
214 CHECK_ORDER_INT_NOT_RESULT NUMBER := 0;
215 MAKE_DELETE_INT_RECS_RESULT NUMBER := 0;
216
217 LOOP_LINE_ID NUMBER := '';
218 LOOP_REQUESTED_CANCEL_QTY NUMBER := '';
219 LOOP_CUSTOMER_PRODUCT_ID NUMBER := '';
220 LOOP_SERVICE_MASS_TXN_TEMP_ID NUMBER := '';
221 LOOP_CP_SERVICE_ID NUMBER := '';
222
223 CURSOR c1 IS
224 SELECT LINE_ID,
225 ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY, 0) ORDERED_QUANTITY,
226 CUSTOMER_PRODUCT_ID,
227 SERVICE_MASS_TXN_TEMP_ID,
228 CP_SERVICE_ID
229 FROM SO_LINES
230 WHERE SOURCE_LINE_ID = V_LINE_ID;
231
232
233 BEGIN
234 FOR c1rec IN c1 LOOP
235
236 LOOP_LINE_ID := c1rec.line_id;
237 LOOP_REQUESTED_CANCEL_QTY := c1rec.ordered_quantity;
238 LOOP_CUSTOMER_PRODUCT_ID := c1rec.customer_product_id;
239 LOOP_SERVICE_MASS_TXN_TEMP_ID := c1rec.service_mass_txn_temp_id;
240 LOOP_CP_SERVICE_ID := c1rec.cp_service_id;
241
242 OE_CANSRV.CHECK_LINE_INTERFACED(LOOP_LINE_ID, CHECK_LINE_INTERFACE_RESULT);
243
244 IF CHECK_LINE_INTERFACE_RESULT = 1 THEN
245 OE_CANSRV.CHECK_ORDER_INT_RECS_EXIST(LOOP_LINE_ID, V_CONCURRENT_PROCESS_ID,
246 CHECK_ORDER_INT_RECS_RESULT);
247 END IF;
248
249 IF CHECK_ORDER_INT_RECS_RESULT = 1 THEN
250 OE_CANSRV.CHECK_ORDER_INT_NOT_IN_PROG(LOOP_LINE_ID, V_PRINT_ERR_MSG,
251 V_CONCURRENT_PROCESS_ID, CHECK_ORDER_INT_NOT_RESULT);
252 ELSE
253 INSERT INTO CS_ORDERS_INTERFACE
254 ( ORDER_INTERFACE_ID
255 , CREATED_BY
256 , SERVICE_ITEM_FLAG
257 , CREATION_DATE
258 , TRANSACTION_CODE
259 , LINE_ID
260 , CANCEL_QUANTITY
261 , CANCEL_CP_ID
262 )
263 VALUES
264 ( CS_ORDERS_INTERFACE_S.NEXTVAL
265 , V_LAST_UPDATED_BY
266 , 'Y'
267 , SYSDATE
268 , 'CANCEL'
269 , LOOP_LINE_ID
270 , LOOP_REQUESTED_CANCEL_QTY
271 , LOOP_CUSTOMER_PRODUCT_ID
272 );
273 END IF;
274
275 IF CHECK_ORDER_INT_NOT_RESULT = 1 THEN
276 OE_CANSRV.MAKE_DELETE_INT_RECS(LOOP_LINE_ID,LOOP_CUSTOMER_PRODUCT_ID,
277 LOOP_CP_SERVICE_ID, V_LAST_UPDATED_BY, LOOP_SERVICE_MASS_TXN_TEMP_ID,
278 MAKE_DELETE_INT_RECS_RESULT);
279
280 DELETE FROM CS_ORDERS_INTERFACE
281 WHERE LINE_ID = LOOP_LINE_ID
282 AND TRANSACTION_CODE IN ('ORDER', 'RENEW');
283 END IF;
284
285
286 INSERT INTO SO_ORDER_CANCELLATIONS
287 ( LINE_ID, HEADER_ID,
288 CANCEL_CODE, CANCELLED_BY,
289 CANCEL_DATE, LAST_UPDATED_BY,
290 LAST_UPDATE_DATE, CANCEL_COMMENT,
291 CANCELLED_QUANTITY, STATUS,
292 CREATION_DATE, CREATED_BY,
293 LAST_UPDATE_LOGIN )
294 VALUES (
295 LOOP_LINE_ID, V_HEADER_ID,
296 V_CANCEL_CODE, V_LAST_UPDATED_BY,
297 SYSDATE, V_LAST_UPDATED_BY,
298 SYSDATE, V_CANCEL_COMMENT,
299 DECODE(V_FULL, '1','',LOOP_REQUESTED_CANCEL_QTY),
300 V_STATUS, SYSDATE, V_LAST_UPDATED_BY,
301 V_LAST_UPDATE_LOGIN);
302
303 END LOOP;
304
305 UPDATE SO_LINES
306 SET CANCELLED_QUANTITY = ORDERED_QUANTITY,
307 OPEN_FLAG = '',
308 LAST_UPDATED_BY = V_LAST_UPDATED_BY,
309 LAST_UPDATE_LOGIN = V_LAST_UPDATE_LOGIN,
310 LAST_UPDATE_DATE = SYSDATE,
311 S1 = DECODE(OPEN_FLAG,'',DECODE(S1,18,8,S1),S1),
312 S2 = DECODE(OPEN_FLAG,'',DECODE(S2,18,8,S2),S2),
313 S3 = DECODE(OPEN_FLAG,'',DECODE(S3,18,8,S3),S3),
314 S4 = DECODE(OPEN_FLAG,'',DECODE(S4,18,8,S4),S4),
315 S5 = DECODE(OPEN_FLAG,'',DECODE(S5,18,8,S5),S5),
316 S6 = DECODE(OPEN_FLAG,'',DECODE(S6,18,8,S6),S6),
317 S7 = DECODE(OPEN_FLAG,'',DECODE(S7,18,8,S7),S7),
318 S8 = DECODE(OPEN_FLAG,'',DECODE(S8,18,8,S8),S8),
319 S9 = 11,
320 S10 = DECODE(OPEN_FLAG,'',DECODE(S10,18,8,S10),S10),
321 S11 = DECODE(OPEN_FLAG,'',DECODE(S11,18,8,S11),S11),
322 S12 = DECODE(OPEN_FLAG,'',DECODE(S12,18,8,S12),S12),
323 S13 = DECODE(OPEN_FLAG,'',DECODE(S13,18,8,S13),S13),
324 S14 = DECODE(OPEN_FLAG,'',DECODE(S14,18,8,S14),S14),
325 S15 = DECODE(OPEN_FLAG,'',DECODE(S15,18,8,S15),S15),
326 S16 = DECODE(OPEN_FLAG,'',DECODE(S16,18,8,S16),S16),
327 S17 = DECODE(OPEN_FLAG,'',DECODE(S17,18,8,S17),S17),
328 S18 = DECODE(OPEN_FLAG,'',DECODE(S18,18,8,S18),S18),
329 S19 = DECODE(OPEN_FLAG,'',DECODE(S19,18,8,S19),S19),
330 S20 = DECODE(OPEN_FLAG,'',DECODE(S20,18,8,S20),S20),
331 S21 = DECODE(OPEN_FLAG,'',DECODE(S21,18,8,S21),S21),
332 S22 = DECODE(OPEN_FLAG,'',DECODE(S22,18,8,S22),S22),
333 S23 = DECODE(OPEN_FLAG,'',DECODE(S23,18,8,S23),S23),
334 S24 = DECODE(OPEN_FLAG,'',DECODE(S24,18,8,S24),S24),
335 S25 = DECODE(OPEN_FLAG,'',DECODE(S25,18,8,S25),S25),
336 S26 = DECODE(OPEN_FLAG,'',DECODE(S26,18,8,S26),S26),
337 S27 = DECODE(OPEN_FLAG,'',DECODE(S27,18,8,S27),S27),
338 S28 = DECODE(OPEN_FLAG,'',DECODE(S28,18,8,S28),S28),
339 S29 = DECODE(OPEN_FLAG,'',DECODE(S29,18,8,S29),S29),
340 S30 = DECODE(OPEN_FLAG,'',DECODE(S30,18,8,S30),S30)
341 WHERE SOURCE_LINE_ID = V_LINE_ID;
342
343 UPDATE SO_LINES
344 SET S1_DATE = DECODE(OPEN_FLAG,'',
345 DECODE(S1,18,TO_DATE(NULL),S1_DATE),S1_DATE),
349 S4_DATE = DECODE(OPEN_FLAG,'',
346 S2_DATE = DECODE(OPEN_FLAG,'',
347 DECODE(S2,18,TO_DATE(NULL),S2_DATE),S2_DATE),
348 S3_DATE = DECODE(S3,18,TO_DATE(NULL),S3_DATE),
350 DECODE(S4,18,TO_DATE(NULL),S4_DATE),S4_DATE),
351 S5_DATE = DECODE(OPEN_FLAG,'',
352 DECODE(S5,18,TO_DATE(NULL),S5_DATE),S5_DATE),
353 S6_DATE = DECODE(OPEN_FLAG,'',
354 DECODE(S6,18,TO_DATE(NULL),S6_DATE),S6_DATE),
355 S7_DATE = DECODE(OPEN_FLAG,'',
356 DECODE(S7,18,TO_DATE(NULL),S7_DATE),S7_DATE),
357 S8_DATE = DECODE(OPEN_FLAG,'',
358 DECODE(S8,18,TO_DATE(NULL),S8_DATE),S8_DATE),
359 S9_DATE = SYSDATE,
360 S10_DATE=DECODE(OPEN_FLAG,'',
361 DECODE(S10,18,TO_DATE(NULL),S10_DATE),S10_DATE),
362 S11_DATE=DECODE(OPEN_FLAG,'',
363 DECODE(S11,18,TO_DATE(NULL),S11_DATE),S11_DATE),
364 S12_DATE=DECODE(OPEN_FLAG,'',
365 DECODE(S12,18,TO_DATE(NULL),S12_DATE),S12_DATE),
366 S13_DATE=DECODE(OPEN_FLAG,'',
367 DECODE(S13,18,TO_DATE(NULL),S13_DATE),S13_DATE),
368 S14_DATE=DECODE(OPEN_FLAG,'',
369 DECODE(S14,18,TO_DATE(NULL),S14_DATE),S14_DATE),
370 S15_DATE=DECODE(OPEN_FLAG,'',
371 DECODE(S15,18,TO_DATE(NULL),S15_DATE),S15_DATE),
372 S16_DATE=DECODE(OPEN_FLAG,'',
373 DECODE(S16,18,TO_DATE(NULL),S16_DATE),S16_DATE),
374 S17_DATE=DECODE(OPEN_FLAG,'',
375 DECODE(S17,18,TO_DATE(NULL),S17_DATE),S17_DATE),
376 S18_DATE=DECODE(OPEN_FLAG,'',
377 DECODE(S18,18,TO_DATE(NULL),S18_DATE),S18_DATE),
378 S19_DATE=DECODE(OPEN_FLAG,'',
379 DECODE(S19,18,TO_DATE(NULL),S19_DATE),S19_DATE),
380 S20_DATE=DECODE(OPEN_FLAG,'',
381 DECODE(S20,18,TO_DATE(NULL),S20_DATE),S20_DATE),
382 S21_DATE=DECODE(OPEN_FLAG,'',
383 DECODE(S21,18,TO_DATE(NULL),S21_DATE),S21_DATE),
384 S22_DATE=DECODE(OPEN_FLAG,'',
385 DECODE(S22,18,TO_DATE(NULL),S22_DATE),S22_DATE),
386 S23_DATE=DECODE(OPEN_FLAG,'',
387 DECODE(S23,18,TO_DATE(NULL),S23_DATE),S23_DATE),
388 S24_DATE=DECODE(OPEN_FLAG,'',
389 DECODE(S24,18,TO_DATE(NULL),S24_DATE),S24_DATE),
390 S25_DATE=DECODE(OPEN_FLAG,'',
391 DECODE(S25,18,TO_DATE(NULL),S25_DATE),S25_DATE),
392 S26_DATE=DECODE(OPEN_FLAG,'',
393 DECODE(S26,18,TO_DATE(NULL),S26_DATE),S26_DATE),
394 S27_DATE=DECODE(OPEN_FLAG,'',
395 DECODE(S27,18,TO_DATE(NULL),S27_DATE),S27_DATE),
396 S28_DATE=DECODE(OPEN_FLAG,'',
397 DECODE(S28,18,TO_DATE(NULL),S28_DATE),S28_DATE),
398 S29_DATE=DECODE(OPEN_FLAG,'',
399 DECODE(S29,18,TO_DATE(NULL),S29_DATE),S29_DATE),
400 S30_DATE=DECODE(OPEN_FLAG,'' ,
401 DECODE(S30,18,TO_DATE(NULL),S30_DATE),S30_DATE)
402 WHERE SOURCE_LINE_ID = V_LINE_ID;
403
404
405 V_RESULT := 1;
406
407 END CANCEL_SERVICE_CHILDREN;
408
409
410 ------------------------------------------------------------------------
411 -- procedure CANCEL_LINE
412 ------------------------------------------------------------------------
413 procedure CANCEL_LINE(
414 V_LINE_ID IN NUMBER
415 , V_REQUESTED_CANCEL_QTY IN NUMBER
416 , V_ORDERED_QUANTITY IN NUMBER
417 , V_RECEIVED_QUANTITY IN NUMBER
418 , V_S29 IN NUMBER
419 , V_SOURCE_CODE IN VARCHAR2
420 , V_LINE_TYPE_CODE IN VARCHAR2
421 , V_HEADER_ID IN NUMBER
422 , V_CANCEL_CODE IN VARCHAR2
423 , V_CANCEL_COMMENT IN LONG
424 , V_LAST_UPDATED_BY IN NUMBER
425 , V_LAST_UPDATE_LOGIN IN NUMBER
426 , V_FULL IN NUMBER
427 , V_STATUS IN VARCHAR2
428 , V_RESULT OUT NUMBER
429 )
430 IS
431 V_CANCELLED_QUANTITY NUMBER;
432 DUMMY NUMBER := '';
433 DUMMY2 NUMBER := '';
434 l_org_id NUMBER := NULL;
435
436 BEGIN
437
438 l_org_id := FND_PROFILE.VALUE ('SO_ORGANIZATION_ID');
439
440 SELECT NVL(CANCELLED_QUANTITY,0)
441 INTO V_CANCELLED_QUANTITY
442 FROM SO_LINES
443 WHERE LINE_ID = V_LINE_ID;
444
445 V_CANCELLED_QUANTITY := V_CANCELLED_QUANTITY + V_REQUESTED_CANCEL_QTY;
446
447 IF V_LINE_TYPE_CODE = 'RETURN' THEN
448 IF ((V_S29 = 14) OR (V_S29 = 16) OR (V_S29 = 17)) THEN
449
450 UPDATE MTL_SO_RMA_INTERFACE MSRI
451 SET MSRI.QUANTITY = (MSRI.QUANTITY
452 / (V_ORDERED_QUANTITY - (V_CANCELLED_QUANTITY
453 - V_REQUESTED_CANCEL_QTY))
454 * (V_ORDERED_QUANTITY - V_CANCELLED_QUANTITY))
455 WHERE MSRI.RMA_LINE_ID = V_LINE_ID
456 AND MSRI.SOURCE_CODE = V_SOURCE_CODE
457 AND MSRI.IN_USE_FLAG IS NULL;
458
459 SELECT COUNT(*)
460 INTO DUMMY
461 FROM SO_LINES
462 WHERE LINE_ID = V_LINE_ID
463 AND (ORDERED_QUANTITY - V_CANCELLED_QUANTITY - V_RECEIVED_QUANTITY) > 0;
464
465 IF DUMMY < 1 THEN
466
467 UPDATE MTL_SO_RMA_INTERFACE MSRI
468 SET CLOSED_FLAG = 'Y'
469 WHERE MSRI.RMA_LINE_ID = V_LINE_ID
470 AND MSRI.SOURCE_CODE = V_SOURCE_CODE;
471 END IF;
472 END IF;
473 END IF;
474
475 SELECT COUNT(*)
476 INTO DUMMY2
477 FROM SO_LINES
478 WHERE LINE_ID = V_LINE_ID
479 AND (ORDERED_QUANTITY - V_CANCELLED_QUANTITY) > 0;
480
481 IF DUMMY2 >= 1 THEN
482 UPDATE SO_LINES
483 SET CANCELLED_QUANTITY = V_CANCELLED_QUANTITY,
484 S9 = 5,
485 S9_DATE = SYSDATE
486 WHERE LINE_ID = V_LINE_ID;
487
488 ELSE
489 UPDATE SO_LINES
490 SET OPEN_FLAG = '',
491 CANCELLED_QUANTITY = V_CANCELLED_QUANTITY,
492 S1 = DECODE(S1,18,8,S1),
496 S3 = DECODE(S3,18,8,S3),
493 S1_DATE = DECODE(S1,18, '', S1_DATE),
494 S2 = DECODE(S2,18,8,S2),
495 S2_DATE = DECODE(S2,18, '', S2_DATE),
497 S3_DATE = DECODE(S3,18, '', S3_DATE),
498 S4 = DECODE(S4,18,8,S4),
499 S4_DATE = DECODE(S4,18, '', S4_DATE),
500 S5 = DECODE(S5,18,8,S5),
501 S5_DATE = DECODE(S5,18, '', S5_DATE),
502 S6 = DECODE(S6,18,8,S6),
503 S6_DATE = DECODE(S6,18, '', S6_DATE),
504 S7 = DECODE(S7,18,8,S7),
505 S7_DATE = DECODE(S7,18, '', S7_DATE),
506 S8 = DECODE(S8,18,8,S8),
507 S8_DATE = DECODE(S8,18, '', S8_DATE),
508 S9 = 11,
509 S9_DATE = SYSDATE,
510 S10 = DECODE(S10,18,8,S10),
511 S10_DATE = DECODE(S10,18, '', S10_DATE),
512 S11 = DECODE(S11,18,8,S11),
513 S11_DATE = DECODE(S11,18, '', S11_DATE),
514 S12 = DECODE(S12,18,8,S12),
515 S12_DATE = DECODE(S12,18, '', S12_DATE),
516 S13 = DECODE(S13,18,8,S13),
517 S13_DATE = DECODE(S13,18, '', S13_DATE),
518 S14 = DECODE(S14,18,8,S14),
519 S14_DATE = DECODE(S14,18, '', S14_DATE),
520 S15 = DECODE(S15,18,8,S15),
521 S15_DATE = DECODE(S15,18, '', S15_DATE),
522 S16 = DECODE(S16,18,8,S16),
523 S16_DATE = DECODE(S16,18, '', S16_DATE),
524 S17 = DECODE(S17,18,8,S17),
525 S17_DATE = DECODE(S17,18, '', S17_DATE),
526 S18 = DECODE(S18,18,8,S18),
527 S18_DATE = DECODE(S18,18, '', S18_DATE),
528 S19 = DECODE(S19,18,8,S19),
529 S19_DATE = DECODE(S19,18, '', S19_DATE),
530 S20 = DECODE(S20,18,8,S20),
531 S20_DATE = DECODE(S20,18, '', S20_DATE),
532 S21 = DECODE(S21,18,8,S21),
533 S21_DATE = DECODE(S21,18, '', S21_DATE),
534 S22 = DECODE(S22,18,8,S22),
535 S22_DATE = DECODE(S22,18, '', S22_DATE),
536 S23= DECODE(S23,18,8,S23),
537 S23_DATE = DECODE(S23,18, '', S23_DATE),
538 S24 = DECODE(S24,18,8,S24),
539 S24_DATE = DECODE(S24,18, '', S24_DATE),
540 S25 = DECODE(S25,18,8,S25),
541 S25_DATE = DECODE(S25,18, '', S25_DATE),
542 S26 = DECODE(S26,18,8,S26),
543 S26_DATE = DECODE(S26,18, '', S26_DATE),
544 S27 = DECODE(S27,18,8,S27),
545 S27_DATE = DECODE(S27,18, '', S27_DATE),
546 S28 = DECODE(S28,18,8,S28),
547 S28_DATE = DECODE(S28,18, '', S28_DATE),
548 S29 = DECODE(S29,18,8,S29),
549 S29_DATE = DECODE(S29,18, '', S29_DATE),
550 S30 = DECODE(S30,18,8,S30),
551 S30_DATE = DECODE(S30,18, '', S30_DATE)
552 WHERE LINE_ID = V_LINE_ID;
553
554 END IF;
555
556 INSERT INTO SO_ORDER_CANCELLATIONS
557 ( LINE_ID, HEADER_ID,
558 CANCEL_CODE, CANCELLED_BY,
559 CANCEL_DATE, LAST_UPDATED_BY,
560 LAST_UPDATE_DATE, CANCEL_COMMENT,
561 CANCELLED_QUANTITY, STATUS,
562 CREATION_DATE, CREATED_BY,
563 LAST_UPDATE_LOGIN )
564 VALUES (
565 V_LINE_ID, V_HEADER_ID,
566 V_CANCEL_CODE, V_LAST_UPDATED_BY,
567 SYSDATE, V_LAST_UPDATED_BY,
568 SYSDATE, V_CANCEL_COMMENT,
569 DECODE(V_FULL, '1','',V_REQUESTED_CANCEL_QTY),
570 V_STATUS, SYSDATE, V_LAST_UPDATED_BY,
571 V_LAST_UPDATE_LOGIN);
572
573 /*
574 ** Fix for Bug # 654734
575 ** Update quantity_to_invoice for non-shippable items on line Cancellation.
576 */
577 UPDATE SO_LINES
578 SET QUANTITY_TO_INVOICE =
579 DECODE(NVL(QUANTITY_TO_INVOICE,0),
580 0, QUANTITY_TO_INVOICE,
581 DECODE(NVL(SHIPPED_QUANTITY,0),
582 0,(ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0)),
583 SHIPPED_QUANTITY))
584 WHERE (LINE_ID = V_LINE_ID
585 OR PARENT_LINE_ID = V_LINE_ID)
586 AND (S4 + 0 IN (5, 7, 22, 8)
587 OR S29 + 0 IN (14, 16, 8))
588 AND EXISTS
589 (SELECT 'NON SHIPPABLE ITEM'
590 FROM MTL_SYSTEM_ITEMS MSI
591 WHERE MSI.ORGANIZATION_ID = l_org_id
592 AND MSI.INVENTORY_ITEM_ID = SO_LINES.INVENTORY_ITEM_ID
593 AND MSI.SHIPPABLE_ITEM_FLAG = 'N');
594
595 V_RESULT := 1;
596
597 END CANCEL_LINE;
598
599 ------------------------------------------------------------------------
600 -- procedure CANCEL_SERVICE
601 ------------------------------------------------------------------------
602 procedure CANCEL_SERVICE(
603 V_LINE_ID IN NUMBER
604 , V_REQUESTED_CANCEL_QTY IN NUMBER
605 , V_ORDERED_QUANTITY IN NUMBER
606 , V_RECEIVED_QUANTITY IN NUMBER
607 , V_S29 IN NUMBER
608 , V_HEADER_ID IN NUMBER
609 , V_CANCEL_CODE IN VARCHAR2
610 , V_CANCEL_COMMENT IN LONG
611 , V_LAST_UPDATE_LOGIN IN NUMBER
612 , V_FULL IN NUMBER
613 , V_STATUS IN VARCHAR2
614 , V_CUSTOMER_PRODUCT_ID IN NUMBER
615 , V_CP_SERVICE_ID IN NUMBER
616 , V_LAST_UPDATED_BY IN NUMBER
617 , V_SERVICE_MASS_TXN_TEMP_ID IN NUMBER
618 , V_SOURCE_CODE IN VARCHAR2
619 , V_LINE_TYPE_CODE IN VARCHAR2
620 , V_PRINT_ERR_MSG IN NUMBER
621 , V_CONCURRENT_PROCESS_ID IN OUT NUMBER
622 , V_RESULT OUT NUMBER
623 )
624 IS
625
626 CHK_LINE_INT_RESULT NUMBER :='';
627 CANCEL_SRV_CHILD_RESULT NUMBER :='';
628 CHK_ORD_INT_RESULT NUMBER :='';
629 CHK_ORD_INT_NOT_RESULT NUMBER :='';
630 MAKE_DELETE_INT_RESULT NUMBER :='';
631 CANCEL_LINE_RESULT NUMBER :='';
632
633 BEGIN
634
635 OE_CANSRV.CHECK_LINE_INTERFACED( V_LINE_ID,
636 CHK_LINE_INT_RESULT
637 );
638
642
639 IF CHK_LINE_INT_RESULT = 0 THEN
640 GOTO cancel_line;
641 END IF;
643 OE_CANSRV.CHECK_ORDER_INT_RECS_EXIST( V_LINE_ID,
644 V_CONCURRENT_PROCESS_ID,
645 CHK_ORD_INT_RESULT
646 );
647 IF CHK_ORD_INT_RESULT = 0 THEN
648
649 INSERT INTO CS_ORDERS_INTERFACE
650 ( ORDER_INTERFACE_ID
651 , CREATED_BY
652 , SERVICE_ITEM_FLAG
653 , CREATION_DATE
654 , TRANSACTION_CODE
655 , LINE_ID
656 , CANCEL_QUANTITY
657 , CANCEL_CP_ID
658 )
659 VALUES
660 ( CS_ORDERS_INTERFACE_S.NEXTVAL
661 , V_LAST_UPDATED_BY
662 , 'Y'
663 , SYSDATE
664 , 'CANCEL'
665 , V_LINE_ID
666 , V_REQUESTED_CANCEL_QTY
667 , V_CUSTOMER_PRODUCT_ID);
668
669 GOTO cancel_line;
670 END IF;
671
672
673 OE_CANSRV.CHECK_ORDER_INT_NOT_IN_PROG( V_LINE_ID,
674 V_PRINT_ERR_MSG,
675 V_CONCURRENT_PROCESS_ID,
676 CHK_ORD_INT_NOT_RESULT
677 );
678 IF CHK_ORD_INT_NOT_RESULT = 1 THEN
679 OE_CANSRV.MAKE_DELETE_INT_RECS( V_LINE_ID,
680 V_CUSTOMER_PRODUCT_ID,
681 V_CP_SERVICE_ID,
682 V_LAST_UPDATED_BY,
683 V_SERVICE_MASS_TXN_TEMP_ID,
684 MAKE_DELETE_INT_RESULT
685 );
686
687 DELETE FROM CS_ORDERS_INTERFACE
688 WHERE LINE_ID = V_LINE_ID
689 AND TRANSACTION_CODE IN ('ORDER', 'RENEW');
690
691 GOTO cancel_line;
692 END IF;
693
694 <<cancel_line>>
695 OE_CANSRV.CANCEL_SERVICE_CHILDREN( V_LINE_ID,
696 V_HEADER_ID,
697 V_CANCEL_CODE,
698 V_CANCEL_COMMENT,
699 V_FULL,
700 V_STATUS,
701 V_REQUESTED_CANCEL_QTY,
702 V_CUSTOMER_PRODUCT_ID,
703 V_CP_SERVICE_ID,
704 V_LAST_UPDATED_BY,
705 V_LAST_UPDATE_LOGIN,
706 V_SERVICE_MASS_TXN_TEMP_ID,
707 V_PRINT_ERR_MSG,
708 V_CONCURRENT_PROCESS_ID,
709 CANCEL_SRV_CHILD_RESULT
710 );
711
712 OE_CANSRV.CANCEL_LINE( V_LINE_ID,
713 V_REQUESTED_CANCEL_QTY,
714 V_ORDERED_QUANTITY,
715 V_RECEIVED_QUANTITY,
716 V_S29,
717 V_SOURCE_CODE,
718 V_LINE_TYPE_CODE,
719 V_HEADER_ID,
720 V_CANCEL_CODE,
721 V_CANCEL_COMMENT,
722 V_LAST_UPDATED_BY,
723 V_LAST_UPDATE_LOGIN,
724 V_FULL,
725 V_STATUS,
726 CANCEL_LINE_RESULT
727 );
728
729 IF ((CANCEL_SRV_CHILD_RESULT = 1) AND (CANCEL_LINE_RESULT = 1)) THEN
730 V_RESULT := 1;
731 ELSE
732 V_RESULT := 0;
733 END IF;
734
735 END CANCEL_SERVICE;
736
737
738 ------------------------------------------------------------------------
739 -- procedure HISTORY
740 ------------------------------------------------------------------------
741
742 procedure HISTORY(
743 V_LINE_ID IN NUMBER
744 , V_ITEM OUT VARCHAR2
745 , V_BASE_LINE_NUMBER OUT NUMBER
746 , V_SHIPMENT_SCHEDULE_NUMBER OUT NUMBER
747 , V_OPTION_LINE_NUMBER OUT NUMBER
748 )
749
750 IS
751
752 BEGIN
753 SELECT ITEM,
754 BASE_LINE_NUMBER,
755 SHIPMENT_SCHEDULE_NUMBER,
756 OPTION_LINE_NUMBER
757 INTO V_ITEM,
758 V_BASE_LINE_NUMBER,
759 V_SHIPMENT_SCHEDULE_NUMBER,
760 V_OPTION_LINE_NUMBER
761 FROM SO_LINES_CANCEL_V
762 WHERE LINE_ID = V_LINE_ID;
763
764
765 END HISTORY;
766
767 ------------------------------------------------------------------------
768 -- procedure HOLDS
769 --
770 -- Loop over all holds in the order, for each, check for full
771 -- cancellation of that line.
772 -- If it's completely cancelled, release the hold found.
773 --
774 ------------------------------------------------------------------------
775
776 procedure HOLDS(
777 V_HEADER_ID IN NUMBER
778 , V_LOGIN_ID IN NUMBER
779 , V_USER_ID IN NUMBER
780 )
781 IS
782
783 V_HOLD_RELEASE_ID NUMBER := '';
784
785 LOOP_HOLD_LINE_ID NUMBER := '';
786 LOOP_HOLD_SOURCE_ID NUMBER := '';
787 LOOP_HOLD_ENTITY_ID NUMBER := '';
788 LOOP_HOLD_ENTITY_CODE VARCHAR2(50);
789 LOOP_ORDER_HOLD_ID NUMBER := '';
790
791
792 CURSOR c1 IS
793 SELECT OESOH.LINE_ID LINE_ID,
794 OESRC.HOLD_SOURCE_ID HOLD_SOURCE_ID,
795 OESRC.HOLD_ENTITY_ID HOLD_ENTITY_ID,
796 OESRC.HOLD_ENTITY_CODE HOLD_ENTITY_CODE,
797 OESOH.ORDER_HOLD_ID ORDER_HOLD_ID
798 FROM SO_HOLD_SOURCES OESRC,
799 SO_ORDER_HOLDS OESOH
800 WHERE OESRC.HOLD_SOURCE_ID = OESOH.HOLD_SOURCE_ID
801 AND OESRC.RELEASED_FLAG = 'N'
802 AND OESOH.HOLD_RELEASE_ID IS NULL
803 AND OESOH.LINE_ID IS NOT NULL
804 AND OESOH.HEADER_ID = V_HEADER_ID
805 AND EXISTS (
806 SELECT 'NONE_LEFT'
807 FROM SO_LINES
808 WHERE LINE_ID = OESOH.LINE_ID
809 AND ORDERED_QUANTITY = NVL(CANCELLED_QUANTITY,0) +
810 GREATEST (NVL(SHIPPED_QUANTITY,0), NVL(INVOICED_QUANTITY,0)));
811
812
813 BEGIN
814
815 FOR c1rec IN c1 LOOP
816
817 LOOP_HOLD_LINE_ID := c1rec.line_id;
818 LOOP_HOLD_SOURCE_ID := c1rec.hold_source_id;
819 LOOP_HOLD_ENTITY_ID := c1rec.hold_entity_id;
820 LOOP_HOLD_ENTITY_CODE := c1rec.hold_entity_code;
821 LOOP_ORDER_HOLD_ID := c1rec.order_hold_id;
822
826
823 SELECT SO_HOLD_RELEASES_S.NEXTVAL
824 INTO V_HOLD_RELEASE_ID
825 FROM DUAL;
827 INSERT INTO SO_HOLD_RELEASES
828 (HOLD_RELEASE_ID,
829 HOLD_SOURCE_ID,
830 LAST_UPDATE_DATE,
831 LAST_UPDATED_BY,
832 LAST_UPDATE_LOGIN,
833 CREATION_DATE,
834 CREATED_BY,
835 HOLD_ENTITY_ID,
836 HOLD_ENTITY_CODE,
837 RELEASE_REASON_CODE)
838 VALUES (V_HOLD_RELEASE_ID,
839 LOOP_HOLD_SOURCE_ID,
840 SYSDATE,
841 V_USER_ID,
842 V_LOGIN_ID,
843 SYSDATE,
844 V_USER_ID,
845 LOOP_HOLD_ENTITY_ID,
846 LOOP_HOLD_ENTITY_CODE,
847 'CANCELLATION');
848
849 UPDATE SO_ORDER_HOLDS
850 SET HOLD_RELEASE_ID = V_HOLD_RELEASE_ID,
851 LAST_UPDATE_DATE = SYSDATE,
852 LAST_UPDATED_BY = V_USER_ID,
853 LAST_UPDATE_LOGIN = V_LOGIN_ID
854 WHERE HOLD_RELEASE_ID IS NULL
855 AND ORDER_HOLD_ID = LOOP_ORDER_HOLD_ID;
856
857 END LOOP;
858
859 -- Release automatically applied source holds(with hold_id < 1000) when
860 -- all the underline order holds have been released.
861
862 UPDATE SO_HOLD_SOURCES SHS
863 SET RELEASED_FLAG = 'Y',
864 LAST_UPDATE_DATE = SYSDATE,
865 LAST_UPDATED_BY = V_USER_ID,
866 LAST_UPDATE_LOGIN = V_LOGIN_ID
867 WHERE HOLD_ENTITY_CODE = 'O'
868 AND RELEASED_FLAG = 'N'
869 AND HOLD_ENTITY_ID = V_HEADER_ID
870 AND HOLD_ID < 1000
871 AND NOT EXISTS
872 (SELECT 'EXISTS'
873 FROM SO_ORDER_HOLDS SOH
874 WHERE HOLD_RELEASE_ID IS NULL
875 AND SOH.HOLD_SOURCE_ID = SHS.HOLD_SOURCE_ID);
876 END HOLDS;
877
878 ------------------------------------------------------------------------
879 -- procedure ALL_HOLDS
880 --
881 -- Loop over all holds in the order(both Order Level and Line Level)
882 -- Release the holds found.
883 --
884 ------------------------------------------------------------------------
885
886 procedure ALL_HOLDS(
887 V_HEADER_ID IN NUMBER
888 , V_LOGIN_ID IN NUMBER
889 , V_USER_ID IN NUMBER
890 )
891 IS
892
893 V_HOLD_RELEASE_ID NUMBER := '';
894
895 LOOP_HOLD_LINE_ID NUMBER := '';
896 LOOP_HOLD_SOURCE_ID NUMBER := '';
897 LOOP_HOLD_ENTITY_ID NUMBER := '';
898 LOOP_HOLD_ENTITY_CODE VARCHAR2(50);
899 LOOP_ORDER_HOLD_ID NUMBER := '';
900
901
902 CURSOR c1 IS
903 SELECT OESOH.LINE_ID LINE_ID,
904 OESRC.HOLD_SOURCE_ID HOLD_SOURCE_ID,
905 OESRC.HOLD_ENTITY_ID HOLD_ENTITY_ID,
906 OESRC.HOLD_ENTITY_CODE HOLD_ENTITY_CODE,
907 OESOH.ORDER_HOLD_ID ORDER_HOLD_ID
908 FROM SO_HOLD_SOURCES OESRC,
909 SO_ORDER_HOLDS OESOH
910 WHERE OESRC.HOLD_SOURCE_ID = OESOH.HOLD_SOURCE_ID
911 AND OESRC.RELEASED_FLAG = 'N'
912 AND OESOH.HOLD_RELEASE_ID IS NULL
913 AND OESOH.HEADER_ID = V_HEADER_ID;
914
915 BEGIN
916
917 FOR c1rec IN c1 LOOP
918
919 LOOP_HOLD_LINE_ID := c1rec.line_id;
920 LOOP_HOLD_SOURCE_ID := c1rec.hold_source_id;
921 LOOP_HOLD_ENTITY_ID := c1rec.hold_entity_id;
922 LOOP_HOLD_ENTITY_CODE := c1rec.hold_entity_code;
923 LOOP_ORDER_HOLD_ID := c1rec.order_hold_id;
924
925 SELECT SO_HOLD_RELEASES_S.NEXTVAL
926 INTO V_HOLD_RELEASE_ID
927 FROM DUAL;
928
929 INSERT INTO SO_HOLD_RELEASES
930 (HOLD_RELEASE_ID,
931 HOLD_SOURCE_ID,
932 LAST_UPDATE_DATE,
933 LAST_UPDATED_BY,
934 LAST_UPDATE_LOGIN,
935 CREATION_DATE,
936 CREATED_BY,
937 HOLD_ENTITY_ID,
938 HOLD_ENTITY_CODE,
939 RELEASE_REASON_CODE)
940 VALUES (V_HOLD_RELEASE_ID,
941 LOOP_HOLD_SOURCE_ID,
942 SYSDATE,
943 V_USER_ID,
944 V_LOGIN_ID,
945 SYSDATE,
946 V_USER_ID,
947 LOOP_HOLD_ENTITY_ID,
948 LOOP_HOLD_ENTITY_CODE,
949 'CANCELLATION');
950
951 UPDATE SO_ORDER_HOLDS
952 SET HOLD_RELEASE_ID = V_HOLD_RELEASE_ID,
953 LAST_UPDATE_DATE = SYSDATE,
954 LAST_UPDATED_BY = V_USER_ID,
955 LAST_UPDATE_LOGIN= V_LOGIN_ID
956 WHERE HOLD_RELEASE_ID IS NULL
957 AND ORDER_HOLD_ID = LOOP_ORDER_HOLD_ID;
958
959 END LOOP;
960
961 -- Release automatically applied source holds(with hold_id < 1000) when
962 -- all the underline order holds have been released.
963
964 UPDATE SO_HOLD_SOURCES SHS
965 SET RELEASED_FLAG = 'Y',
966 LAST_UPDATE_DATE = SYSDATE,
967 LAST_UPDATED_BY = V_USER_ID,
968 LAST_UPDATE_LOGIN= V_LOGIN_ID
969 WHERE HOLD_ENTITY_CODE = 'O'
970 AND RELEASED_FLAG = 'N'
971 AND HOLD_ENTITY_ID = V_HEADER_ID
972 AND HOLD_ID < 1000
973 AND NOT EXISTS
974 (SELECT 'EXISTS'
975 FROM SO_ORDER_HOLDS SOH
976 WHERE HOLD_RELEASE_ID IS NULL
977 AND SOH.HOLD_SOURCE_ID = SHS.HOLD_SOURCE_ID);
978 END ALL_HOLDS;
979
980 END OE_CANSRV;