[Home] [Help]
11: l_ret_val BOOLEAN;
12: l_status VARCHAR2(1);
13: l_industry VARCHAR2(1);
14: --
15: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
16: --
17: BEGIN
18:
19: if (p_application_id = 697
21: or ( p_application_id = 515
22: AND G_OKC_STATUS = FND_API.G_MISS_CHAR)
23: then
24: IF l_debug_level > 0 THEN
25: oe_debug_pub.add( 'GET PROD. STATUS' ) ;
26: END IF;
27:
28: -- Make a call to fnd_installation.get function to check for the
29: -- installation status of the CRM products and return the status.
39: end if;
40:
41: if p_application_id = 697 then
42: IF l_debug_level > 0 THEN
43: oe_debug_pub.add( 'RET PROD. STATUS :'||G_ASO_STATUS ) ;
44: END IF;
45: return (G_ASO_STATUS);
46: elsif p_application_id = 515 then
47: return (G_OKC_STATUS);
134: l_bypass_notify_oc VARCHAR2(30) := nvl(FND_PROFILE.VALUE('ONT_BYPASS_NOTIFY_OC'),'N');
135: l_buffer VARCHAR2(2000);
136:
137: --
138: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
139: --
140: BEGIN
141:
142: IF l_debug_level > 0 THEN
139: --
140: BEGIN
141:
142: IF l_debug_level > 0 THEN
143: oe_debug_pub.add( 'ENTERING NOTIFY_OC API' ) ;
144: END IF;
145:
146: -- Call Describe_Proc to check for the existance of the CRM's
147: -- Update_Notice API. If exists Then Call it else No Problem.
164: IF OE_GLOBALS.G_ASO_INSTALLED = 'Y' THEN
165:
166:
167: IF l_debug_level > 0 THEN
168: oe_debug_pub.add( 'BYPASS VALUE:' || L_BYPASS_NOTIFY_OC ) ;
169: END IF;
170: IF l_bypass_notify_oc = 'Y' then
171: IF l_debug_level > 0 THEN
172: oe_debug_pub.add( 'BYPASSING THE CALL TO NOTIFY OC' , 1 ) ;
168: oe_debug_pub.add( 'BYPASS VALUE:' || L_BYPASS_NOTIFY_OC ) ;
169: END IF;
170: IF l_bypass_notify_oc = 'Y' then
171: IF l_debug_level > 0 THEN
172: oe_debug_pub.add( 'BYPASSING THE CALL TO NOTIFY OC' , 1 ) ;
173: END IF;
174: ELSE
175:
176: -- Call Update_Notice API Using Native Dynamic SQL
266: OE_SERVICE_UTIL.g_action_request_tbl := p_action_request_tbl;
267:
268:
269: IF l_debug_level > 0 THEN
270: oe_debug_pub.add( 'CSS: BEFORE CALLS UPDATE_NOTICE ' ) ;
271: END IF;
272:
273: ASO_ORDER_FEEDBACK_PUB.UPDATE_NOTICE(
274: p_api_version => 1.0
305: , p_old_Lot_Serial_tbl => OE_SERVICE_UTIL.g_old_lot_serial_tbl
306: , p_action_request_tbl => OE_SERVICE_UTIL.g_action_request_tbl);
307:
308: IF l_debug_level > 0 THEN
309: oe_debug_pub.add( 'JPN: OC RETURN STATUS IS: ' || L_RETURN_STATUS ) ;
310: END IF;
311: IF l_debug_level > 0 THEN
312: oe_debug_pub.add( 'CSS: OC RETURN STATUS IS: ' || L_RETURN_STATUS ) ;
313: END IF;
308: IF l_debug_level > 0 THEN
309: oe_debug_pub.add( 'JPN: OC RETURN STATUS IS: ' || L_RETURN_STATUS ) ;
310: END IF;
311: IF l_debug_level > 0 THEN
312: oe_debug_pub.add( 'CSS: OC RETURN STATUS IS: ' || L_RETURN_STATUS ) ;
313: END IF;
314:
315: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
316: IF l_debug_level > 0 THEN
313: END IF;
314:
315: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
316: IF l_debug_level > 0 THEN
317: oe_debug_pub.add( 'NOTIFY_OC API - UNEXPECTED ERROR' ) ;
318: END IF;
319: IF l_debug_level > 0 THEN
320: oe_debug_pub.add( 'EXITING NOTIFY_OC API' ) ;
321: END IF;
316: IF l_debug_level > 0 THEN
317: oe_debug_pub.add( 'NOTIFY_OC API - UNEXPECTED ERROR' ) ;
318: END IF;
319: IF l_debug_level > 0 THEN
320: oe_debug_pub.add( 'EXITING NOTIFY_OC API' ) ;
321: END IF;
322: Retrieve_OC_Messages;
323: /* OE_DEBUG_PUB.ADD('Notify OC error msg is: ' || substr(x_msg_data, 1,200)); */
324: -- For bug 3574480. Modified unepected error to expected error to
319: IF l_debug_level > 0 THEN
320: oe_debug_pub.add( 'EXITING NOTIFY_OC API' ) ;
321: END IF;
322: Retrieve_OC_Messages;
323: /* OE_DEBUG_PUB.ADD('Notify OC error msg is: ' || substr(x_msg_data, 1,200)); */
324: -- For bug 3574480. Modified unepected error to expected error to
325: -- support orders of this kind to be rebooked.
326: RAISE FND_API.G_EXC_ERROR;
327: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
325: -- support orders of this kind to be rebooked.
326: RAISE FND_API.G_EXC_ERROR;
327: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
328: IF l_debug_level > 0 THEN
329: oe_debug_pub.add( 'NOTIFY_OC API - ERROR' ) ;
330: END IF;
331: IF l_debug_level > 0 THEN
332: oe_debug_pub.add( 'EXITING NOTIFY_OC API' ) ;
333: END IF;
328: IF l_debug_level > 0 THEN
329: oe_debug_pub.add( 'NOTIFY_OC API - ERROR' ) ;
330: END IF;
331: IF l_debug_level > 0 THEN
332: oe_debug_pub.add( 'EXITING NOTIFY_OC API' ) ;
333: END IF;
334: IF l_msg_data is not null THEN
335: /* fnd_message.set_encoded(l_msg_data);
336: l_buffer := fnd_message.get;
335: /* fnd_message.set_encoded(l_msg_data);
336: l_buffer := fnd_message.get;
337: oe_msg_pub.add_text(p_message_text => l_buffer);
338: IF l_debug_level > 0 THEN
339: oe_debug_pub.add( L_BUFFER , 1 ) ;
340: END IF;*/
341: Retrieve_OC_Messages;
342: --RAISE FND_API.G_EXC_ERROR;
343: END IF;
396: l_radix dbms_describe.number_table;
397: l_spare dbms_describe.number_table;
398:
399: --
400: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
401: --
402: BEGIN
403:
404: IF l_debug_level > 0 THEN
401: --
402: BEGIN
403:
404: IF l_debug_level > 0 THEN
405: oe_debug_pub.add( 'ENTERING CHECK_PROC API' ) ;
406: END IF;
407: BEGIN
408: dbms_describe.describe_procedure
409: (
428: WHEN OTHERS THEN
429: x_return_status := FND_API.G_RET_STS_ERROR;
430: END;
431: IF l_debug_level > 0 THEN
432: oe_debug_pub.add( 'EXITING CHECK_PROC API' ) ;
433: END IF;
434: END Check_Proc;
435:
436: PROCEDURE Get_Service_Start -- added for bug 2897505
447: l_msg_data VARCHAR2(2000);
448: l_end_date DATE;
449: l_start_date DATE;
450: --
451: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
452: --
453: BEGIN
454:
455: IF l_debug_level > 0 THEN
452: --
453: BEGIN
454:
455: IF l_debug_level > 0 THEN
456: oe_debug_pub.add( 'ENTERING GET_SERVICE_START' ) ;
457: END IF;
458:
459: IF l_debug_level > 0 THEN
460: oe_debug_pub.add( 'LINE ID PASSED IS ' || to_char(p_line_id) ) ;
456: oe_debug_pub.add( 'ENTERING GET_SERVICE_START' ) ;
457: END IF;
458:
459: IF l_debug_level > 0 THEN
460: oe_debug_pub.add( 'LINE ID PASSED IS ' || to_char(p_line_id) ) ;
461: END IF;
462:
463: IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
464: OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
490: , OUT l_start_date
491: , OUT l_end_date;
492: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
493: IF l_debug_level > 0 THEN
494: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_SVC_SDATE SUCCESSFUL' ) ;
495: END IF;
496: IF l_debug_level > 0 THEN
497: oe_debug_pub.add( 'WITH VALUE OF ==>' ) ;
498: END IF;
493: IF l_debug_level > 0 THEN
494: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_SVC_SDATE SUCCESSFUL' ) ;
495: END IF;
496: IF l_debug_level > 0 THEN
497: oe_debug_pub.add( 'WITH VALUE OF ==>' ) ;
498: END IF;
499: IF l_debug_level > 0 THEN
500: oe_debug_pub.add( 'START_DATE ==> ' || TO_CHAR ( L_START_DATE ) ) ;
501: END IF;
496: IF l_debug_level > 0 THEN
497: oe_debug_pub.add( 'WITH VALUE OF ==>' ) ;
498: END IF;
499: IF l_debug_level > 0 THEN
500: oe_debug_pub.add( 'START_DATE ==> ' || TO_CHAR ( L_START_DATE ) ) ;
501: END IF;
502: x_start_date := l_start_date;
503: x_return_status := l_return_status;
504: IF l_debug_level > 0 THEN
501: END IF;
502: x_start_date := l_start_date;
503: x_return_status := l_return_status;
504: IF l_debug_level > 0 THEN
505: oe_debug_pub.add( 'EXITING GET_SERVICE_START' ) ;
506: END IF;
507: RETURN;
508: END IF;
509:
508: END IF;
509:
510: x_return_status := l_return_status;
511: IF l_debug_level > 0 THEN
512: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_SVC_SDATE RETURNED STATUS:'||
513: x_return_status ) ;
514: END IF;
515:
516: EXCEPTION
515:
516: EXCEPTION
517: WHEN OTHERS THEN
518: IF l_debug_level > 0 THEN
519: oe_debug_pub.add('Unexpected error calling OKS_OMINT_PUB.GET_SVC_SDATE:');
520: oe_debug_pub.add(sqlerrm);
521: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522: RETURN;
523: END IF;
516: EXCEPTION
517: WHEN OTHERS THEN
518: IF l_debug_level > 0 THEN
519: oe_debug_pub.add('Unexpected error calling OKS_OMINT_PUB.GET_SVC_SDATE:');
520: oe_debug_pub.add(sqlerrm);
521: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522: RETURN;
523: END IF;
524: END; -- to recover from any unexpected errors
524: END; -- to recover from any unexpected errors
525:
526: ELSE
527: IF l_debug_level > 0 THEN
528: oe_debug_pub.add( 'SERVICE CONTRACTS NOT INSTALLED' ) ;
529: END IF;
530: END IF;
531:
532: IF l_debug_level > 0 THEN
529: END IF;
530: END IF;
531:
532: IF l_debug_level > 0 THEN
533: oe_debug_pub.add( 'EXITING GET_SERVICE_START' ) ;
534: END IF;
535: END Get_Service_Start;
536:
537: -- Procedure : Get_Service_Duration
553: l_system_id NUMBER;
554: l_service_duration NUMBER;
555: l_service_period VARCHAR2(3);
556: --
557: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
558: --
559: BEGIN
560: -- oe_debug_pub.Debug_On;
561:
556: --
557: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
558: --
559: BEGIN
560: -- oe_debug_pub.Debug_On;
561:
562: IF l_debug_level > 0 THEN
563: oe_debug_pub.add( 'ENTERING GET_SERVICE_DURATION' ) ;
564: END IF;
559: BEGIN
560: -- oe_debug_pub.Debug_On;
561:
562: IF l_debug_level > 0 THEN
563: oe_debug_pub.add( 'ENTERING GET_SERVICE_DURATION' ) ;
564: END IF;
565:
566: /* The following IF added for 2897505 */
567: IF p_x_line_rec.service_reference_type_code = 'GET_SVC_START' THEN
574: /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.GET_DURATION', l_return_status); */
575:
576:
577: IF l_debug_level > 0 THEN
578: oe_debug_pub.add( 'JPN: SERVICE DURATION PASSED IS ' || P_X_LINE_REC.SERVICE_DURATION ) ;
579: END IF;
580: IF l_debug_level > 0 THEN
581: oe_debug_pub.add( 'JPN: SERVICE DATE PASSED IS ' || P_X_LINE_REC.SERVICE_START_DATE ) ;
582: END IF;
577: IF l_debug_level > 0 THEN
578: oe_debug_pub.add( 'JPN: SERVICE DURATION PASSED IS ' || P_X_LINE_REC.SERVICE_DURATION ) ;
579: END IF;
580: IF l_debug_level > 0 THEN
581: oe_debug_pub.add( 'JPN: SERVICE DATE PASSED IS ' || P_X_LINE_REC.SERVICE_START_DATE ) ;
582: END IF;
583: IF l_debug_level > 0 THEN
584: oe_debug_pub.add( 'JPN: SERVICE END DATE PASSED IS ' || P_X_LINE_REC.SERVICE_END_DATE ) ;
585: END IF;
580: IF l_debug_level > 0 THEN
581: oe_debug_pub.add( 'JPN: SERVICE DATE PASSED IS ' || P_X_LINE_REC.SERVICE_START_DATE ) ;
582: END IF;
583: IF l_debug_level > 0 THEN
584: oe_debug_pub.add( 'JPN: SERVICE END DATE PASSED IS ' || P_X_LINE_REC.SERVICE_END_DATE ) ;
585: END IF;
586: IF l_debug_level > 0 THEN
587: oe_debug_pub.add( 'JPN: SERVICE PERIOD PASSED IS ' || P_X_LINE_REC.SERVICE_PERIOD ) ;
588: END IF;
583: IF l_debug_level > 0 THEN
584: oe_debug_pub.add( 'JPN: SERVICE END DATE PASSED IS ' || P_X_LINE_REC.SERVICE_END_DATE ) ;
585: END IF;
586: IF l_debug_level > 0 THEN
587: oe_debug_pub.add( 'JPN: SERVICE PERIOD PASSED IS ' || P_X_LINE_REC.SERVICE_PERIOD ) ;
588: END IF;
589: /* IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN */
590:
591: -- IF Get_Product_Status(515) IN ('I','S') THEN
640: , OUT l_new_end_date;
641:
642:
643: IF l_debug_level > 0 THEN
644: oe_debug_pub.add( 'GET_DURATION RETURN WITH FOLLOWING VALUES OF ==>' ) ;
645: END IF;
646: IF l_debug_level > 0 THEN
647: oe_debug_pub.add( 'END_DATE ==> ' || TO_CHAR ( L_NEW_END_DATE ) ) ;
648: END IF;
643: IF l_debug_level > 0 THEN
644: oe_debug_pub.add( 'GET_DURATION RETURN WITH FOLLOWING VALUES OF ==>' ) ;
645: END IF;
646: IF l_debug_level > 0 THEN
647: oe_debug_pub.add( 'END_DATE ==> ' || TO_CHAR ( L_NEW_END_DATE ) ) ;
648: END IF;
649: IF l_debug_level > 0 THEN
650: oe_debug_pub.add( 'SERVICE_DURATION ==> ' || TO_CHAR ( L_SERVICE_DURATION ) ) ;
651: END IF;
646: IF l_debug_level > 0 THEN
647: oe_debug_pub.add( 'END_DATE ==> ' || TO_CHAR ( L_NEW_END_DATE ) ) ;
648: END IF;
649: IF l_debug_level > 0 THEN
650: oe_debug_pub.add( 'SERVICE_DURATION ==> ' || TO_CHAR ( L_SERVICE_DURATION ) ) ;
651: END IF;
652: IF l_debug_level > 0 THEN
653: oe_debug_pub.add( 'SERVICE_PERIOD ==> ' || L_SERVICE_PERIOD ) ;
654: END IF;
649: IF l_debug_level > 0 THEN
650: oe_debug_pub.add( 'SERVICE_DURATION ==> ' || TO_CHAR ( L_SERVICE_DURATION ) ) ;
651: END IF;
652: IF l_debug_level > 0 THEN
653: oe_debug_pub.add( 'SERVICE_PERIOD ==> ' || L_SERVICE_PERIOD ) ;
654: END IF;
655: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
656: IF l_debug_level > 0 THEN
657: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION RETURN SUCCESS' ) ;
653: oe_debug_pub.add( 'SERVICE_PERIOD ==> ' || L_SERVICE_PERIOD ) ;
654: END IF;
655: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
656: IF l_debug_level > 0 THEN
657: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION RETURN SUCCESS' ) ;
658: END IF;
659: IF l_debug_level > 0 THEN
660: oe_debug_pub.add( 'WITH VALUE OF ==>' ) ;
661: END IF;
656: IF l_debug_level > 0 THEN
657: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION RETURN SUCCESS' ) ;
658: END IF;
659: IF l_debug_level > 0 THEN
660: oe_debug_pub.add( 'WITH VALUE OF ==>' ) ;
661: END IF;
662: IF l_debug_level > 0 THEN
663: oe_debug_pub.add( 'END_DATE ==> ' || TO_CHAR ( L_NEW_END_DATE ) ) ;
664: END IF;
659: IF l_debug_level > 0 THEN
660: oe_debug_pub.add( 'WITH VALUE OF ==>' ) ;
661: END IF;
662: IF l_debug_level > 0 THEN
663: oe_debug_pub.add( 'END_DATE ==> ' || TO_CHAR ( L_NEW_END_DATE ) ) ;
664: END IF;
665: IF l_debug_level > 0 THEN
666: oe_debug_pub.add( 'SERVICE_DURATION ==> ' || TO_CHAR ( L_SERVICE_DURATION ) ) ;
667: END IF;
662: IF l_debug_level > 0 THEN
663: oe_debug_pub.add( 'END_DATE ==> ' || TO_CHAR ( L_NEW_END_DATE ) ) ;
664: END IF;
665: IF l_debug_level > 0 THEN
666: oe_debug_pub.add( 'SERVICE_DURATION ==> ' || TO_CHAR ( L_SERVICE_DURATION ) ) ;
667: END IF;
668: IF l_debug_level > 0 THEN
669: oe_debug_pub.add( 'SERVICE_PERIOD ==> ' || L_SERVICE_PERIOD ) ;
670: END IF;
665: IF l_debug_level > 0 THEN
666: oe_debug_pub.add( 'SERVICE_DURATION ==> ' || TO_CHAR ( L_SERVICE_DURATION ) ) ;
667: END IF;
668: IF l_debug_level > 0 THEN
669: oe_debug_pub.add( 'SERVICE_PERIOD ==> ' || L_SERVICE_PERIOD ) ;
670: END IF;
671: p_x_line_rec.service_end_date := l_new_end_date;
672: p_x_line_rec.service_duration := l_service_duration;
673: p_x_line_rec.service_period := l_service_period;
674: END IF;
675: -- x_return_status := l_return_status;
676: IF l_return_status is NULL THEN
677: IF l_debug_level > 0 THEN
678: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT RETURNED VALUE' ) ;
679: END IF;
680: --x_return_status := FND_API.G_RET_STS_ERROR;
681: END IF;
682: IF l_debug_level > 0 THEN
679: END IF;
680: --x_return_status := FND_API.G_RET_STS_ERROR;
681: END IF;
682: IF l_debug_level > 0 THEN
683: oe_debug_pub.add( 'EXITING GET_SERVICE_DURATION' ) ;
684: END IF;
685: ELSE
686: IF l_debug_level > 0 THEN
687: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT EXISTS' ) ;
683: oe_debug_pub.add( 'EXITING GET_SERVICE_DURATION' ) ;
684: END IF;
685: ELSE
686: IF l_debug_level > 0 THEN
687: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT EXISTS' ) ;
688: END IF;
689: --x_return_status := FND_API.G_RET_STS_ERROR;
690: END IF;
691: END Get_Service_Duration;
731:
732: -- End of the Blanket related changes.
733:
734: --
735: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
736: --
737: BEGIN
738:
739: -- oe_debug_pub.Debug_On;
735: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
736: --
737: BEGIN
738:
739: -- oe_debug_pub.Debug_On;
740:
741: IF l_debug_level > 0 THEN
742: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE' ) ;
743: oe_debug_pub.add( 'ENTERING For Blanket '||P_X_LINE_REC.BLANKET_NUMBER) ;
738:
739: -- oe_debug_pub.Debug_On;
740:
741: IF l_debug_level > 0 THEN
742: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE' ) ;
743: oe_debug_pub.add( 'ENTERING For Blanket '||P_X_LINE_REC.BLANKET_NUMBER) ;
744: END IF;
745: IF l_debug_level > 0 THEN
746: oe_debug_pub.add( 'SERVICE REF TYPE CODE IS:' || P_X_LINE_REC.SERVICE_REFERENCE_TYPE_CODE ) ;
739: -- oe_debug_pub.Debug_On;
740:
741: IF l_debug_level > 0 THEN
742: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE' ) ;
743: oe_debug_pub.add( 'ENTERING For Blanket '||P_X_LINE_REC.BLANKET_NUMBER) ;
744: END IF;
745: IF l_debug_level > 0 THEN
746: oe_debug_pub.add( 'SERVICE REF TYPE CODE IS:' || P_X_LINE_REC.SERVICE_REFERENCE_TYPE_CODE ) ;
747: END IF;
742: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE' ) ;
743: oe_debug_pub.add( 'ENTERING For Blanket '||P_X_LINE_REC.BLANKET_NUMBER) ;
744: END IF;
745: IF l_debug_level > 0 THEN
746: oe_debug_pub.add( 'SERVICE REF TYPE CODE IS:' || P_X_LINE_REC.SERVICE_REFERENCE_TYPE_CODE ) ;
747: END IF;
748: IF l_debug_level > 0 THEN
749: oe_debug_pub.add( 'SERVICE REF LINE ID:' || P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ;
750: END IF;
745: IF l_debug_level > 0 THEN
746: oe_debug_pub.add( 'SERVICE REF TYPE CODE IS:' || P_X_LINE_REC.SERVICE_REFERENCE_TYPE_CODE ) ;
747: END IF;
748: IF l_debug_level > 0 THEN
749: oe_debug_pub.add( 'SERVICE REF LINE ID:' || P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ;
750: END IF;
751:
752:
753: IF l_debug_level > 0 THEN
750: END IF;
751:
752:
753: IF l_debug_level > 0 THEN
754: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE BEFORE IF' ) ;
755: END IF;
756: IF (p_x_line_rec.service_reference_type_code is NULL)
757: AND (p_x_line_rec.service_reference_line_id is NOT NULL) THEN
758: IF l_debug_level > 0 THEN
755: END IF;
756: IF (p_x_line_rec.service_reference_type_code is NULL)
757: AND (p_x_line_rec.service_reference_line_id is NOT NULL) THEN
758: IF l_debug_level > 0 THEN
759: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE REF TYPE NULL' ) ;
760: END IF;
761: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
762: OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'SERVICE_REFERENCE_TYPE_CODE');
763: fnd_message.set_name('ONT','OE_INVALID_SERVICE_REFERENCE_TYPE_CODE');
773: -- first use IS_SERVICE_AVAILABLE API to confirm that the enter
774: -- service item is valid for the reference item
775:
776: IF l_debug_level > 0 THEN
777: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE WITH REF AS ORDER' ) ;
778: END IF;
779: IF l_debug_level > 0 THEN
780: oe_debug_pub.add( 'REF LINEID# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
781: END IF;
776: IF l_debug_level > 0 THEN
777: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE WITH REF AS ORDER' ) ;
778: END IF;
779: IF l_debug_level > 0 THEN
780: oe_debug_pub.add( 'REF LINEID# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
781: END IF;
782: IF l_debug_level > 0 THEN
783: oe_debug_pub.add( 'REF LINE# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REF_LINE_NUMBER ) ) ;
784: END IF;
779: IF l_debug_level > 0 THEN
780: oe_debug_pub.add( 'REF LINEID# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
781: END IF;
782: IF l_debug_level > 0 THEN
783: oe_debug_pub.add( 'REF LINE# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REF_LINE_NUMBER ) ) ;
784: END IF;
785: IF l_debug_level > 0 THEN
786: oe_debug_pub.add( 'ORDER# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REF_ORDER_NUMBER ) ) ;
787: END IF;
782: IF l_debug_level > 0 THEN
783: oe_debug_pub.add( 'REF LINE# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REF_LINE_NUMBER ) ) ;
784: END IF;
785: IF l_debug_level > 0 THEN
786: oe_debug_pub.add( 'ORDER# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REF_ORDER_NUMBER ) ) ;
787: END IF;
788: BEGIN
789: IF p_x_line_rec.service_reference_line_id is null then
790:
858: ELSE
859:
860: IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
861: IF l_debug_level > 0 THEN
862: oe_debug_pub.add( 'IN SELECT USING REF LINEID# WITH BLANKET'
863: || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
864: END IF;
865: SELECT l.line_number
866: , l.shipment_number
914: ELSE
915:
916:
917: IF l_debug_level > 0 THEN
918: oe_debug_pub.add( 'IN SELECT USING REF LINEID# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
919: END IF;
920: SELECT l.line_number
921: , l.shipment_number
922: , l.option_number
951: END IF;
952: END IF;
953:
954: IF l_debug_level > 0 THEN
955: oe_debug_pub.add( 'AFTER SELECT OF ORDER REF' ) ;
956: END IF;
957:
958: -- IF OKS_OMINT_PUB.IS_SERVICE_AVAILABLE exists in DB
959: -- Then Call it Else Not
970: IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' AND -- AND added for 2331301
971: (NOT(l_top_model_id = l_line_id) OR l_serviceable = 'Y') THEN
972:
973: IF l_debug_level > 0 THEN
974: oe_debug_pub.add( 'IS SERVICE AVAILABLE IS AVAILABLE - RUN DYNAMIC' ) ;
975: END IF;
976: -- Check
977: -- Sets customer_id and request date too to fix 1720185
978:
998: , :x_available_yn);
999: END;';
1000:
1001: IF l_debug_level > 0 THEN
1002: oe_debug_pub.add( L_SQL_STAT ) ;
1003: END IF;
1004:
1005: EXECUTE IMMEDIATE l_sql_stat -- added request date to fix 1720185
1006: USING IN l_inventory_item_id
1020:
1021: -- debug messages added as part of 2170348
1022:
1023: IF l_debug_level > 0 THEN
1024: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1025: END IF;
1026: IF l_debug_level > 0 THEN
1027: oe_debug_pub.add( ' INVENTORY ITEM ID : ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 5 ) ;
1028: END IF;
1023: IF l_debug_level > 0 THEN
1024: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1025: END IF;
1026: IF l_debug_level > 0 THEN
1027: oe_debug_pub.add( ' INVENTORY ITEM ID : ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 5 ) ;
1028: END IF;
1029: IF l_debug_level > 0 THEN
1030: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_X_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1031: END IF;
1026: IF l_debug_level > 0 THEN
1027: oe_debug_pub.add( ' INVENTORY ITEM ID : ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 5 ) ;
1028: END IF;
1029: IF l_debug_level > 0 THEN
1030: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_X_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1031: END IF;
1032: IF l_debug_level > 0 THEN
1033: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_X_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1034: END IF;
1029: IF l_debug_level > 0 THEN
1030: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_X_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1031: END IF;
1032: IF l_debug_level > 0 THEN
1033: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_X_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1034: END IF;
1035: IF l_debug_level > 0 THEN
1036: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_X_LINE_REC.REQUEST_DATE ) , 5 ) ;
1037: END IF;
1032: IF l_debug_level > 0 THEN
1033: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_X_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1034: END IF;
1035: IF l_debug_level > 0 THEN
1036: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_X_LINE_REC.REQUEST_DATE ) , 5 ) ;
1037: END IF;
1038: IF l_debug_level > 0 THEN
1039: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1040: END IF;
1035: IF l_debug_level > 0 THEN
1036: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_X_LINE_REC.REQUEST_DATE ) , 5 ) ;
1037: END IF;
1038: IF l_debug_level > 0 THEN
1039: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1040: END IF;
1041:
1042: IF l_debug_level > 0 THEN
1043: oe_debug_pub.add( 'AFTER CALL TO SERVICE AVAILABLE API' ) ;
1039: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1040: END IF;
1041:
1042: IF l_debug_level > 0 THEN
1043: oe_debug_pub.add( 'AFTER CALL TO SERVICE AVAILABLE API' ) ;
1044: END IF;
1045:
1046: /* OR added for 2282076 */
1047: IF l_available_yn = 'N' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1059:
1060: END IF;
1061:
1062: IF l_debug_level > 0 THEN
1063: oe_debug_pub.add( 'JPN: SERVICE QTY IS' || L_SERVICE_QTY ) ;
1064: END IF;
1065: IF l_debug_level > 0 THEN
1066: oe_debug_pub.add( 'JPN: PRODUCT QTY IS' || P_X_LINE_REC.ORDERED_QUANTITY ) ;
1067: END IF;
1062: IF l_debug_level > 0 THEN
1063: oe_debug_pub.add( 'JPN: SERVICE QTY IS' || L_SERVICE_QTY ) ;
1064: END IF;
1065: IF l_debug_level > 0 THEN
1066: oe_debug_pub.add( 'JPN: PRODUCT QTY IS' || P_X_LINE_REC.ORDERED_QUANTITY ) ;
1067: END IF;
1068:
1069: IF p_x_line_rec.ordered_quantity <> Nvl (l_fulfilled_quantity,l_service_qty)THEN --5699215
1070: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1085:
1086: IF l_header_id = p_x_line_rec.header_id THEN
1087: p_x_line_rec.service_reference_line_id := l_line_id;
1088: IF l_debug_level > 0 THEN
1089: oe_debug_pub.add( 'AKS: LINE NUMBER IN GET SA ' || TO_CHAR ( L_LINE_NUMBER ) ) ;
1090: END IF;
1091: p_x_line_rec.line_number := l_line_number;
1092: p_x_line_rec.shipment_number := l_shipment_number;
1093: p_x_line_rec.option_number := l_option_number;
1187: P_X_LINE_REC.blanket_version_number := l_blanket_version_number;
1188: end if;
1189:
1190: IF l_debug_level > 0 THEN
1191: oe_debug_pub.add( 'SERVICE NUMBER IS :' || TO_CHAR ( P_X_LINE_REC.SERVICE_NUMBER ) ) ;
1192: END IF;
1193: IF l_debug_level > 0 THEN
1194: oe_debug_pub.add( 'LINE NUMBER IS :'|| TO_CHAR ( P_X_LINE_REC.LINE_NUMBER ) ) ;
1195: END IF;
1190: IF l_debug_level > 0 THEN
1191: oe_debug_pub.add( 'SERVICE NUMBER IS :' || TO_CHAR ( P_X_LINE_REC.SERVICE_NUMBER ) ) ;
1192: END IF;
1193: IF l_debug_level > 0 THEN
1194: oe_debug_pub.add( 'LINE NUMBER IS :'|| TO_CHAR ( P_X_LINE_REC.LINE_NUMBER ) ) ;
1195: END IF;
1196: IF l_debug_level > 0 THEN
1197: oe_debug_pub.add( 'REF LINE ID IS :'|| TO_CHAR ( L_LINE_ID ) ) ;
1198: END IF;
1193: IF l_debug_level > 0 THEN
1194: oe_debug_pub.add( 'LINE NUMBER IS :'|| TO_CHAR ( P_X_LINE_REC.LINE_NUMBER ) ) ;
1195: END IF;
1196: IF l_debug_level > 0 THEN
1197: oe_debug_pub.add( 'REF LINE ID IS :'|| TO_CHAR ( L_LINE_ID ) ) ;
1198: END IF;
1199:
1200:
1201: END IF;
1199:
1200:
1201: END IF;
1202: IF l_debug_level > 0 THEN
1203: oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1204: END IF;
1205:
1206: EXCEPTION
1207:
1230: /* Enhancement changes for 1799820. The code references to CS objects */
1231: /* are being replaced by the new CSI product apis. */
1232:
1233: IF l_debug_level > 0 THEN
1234: oe_debug_pub.add( 'GET SERVICE ATTRIBUTE - TYPE IS CUSTOMER_PRODUCT' ) ;
1235: END IF;
1236: --for ER 5926405,6346045 changes start here
1237: /* IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
1238: IF NOT (IB_ACTIVE()) THEN
1269: l_sql_stat := l_sql_stat || ' AND rownum < 2 ';
1270:
1271: -- END IF;
1272: IF l_debug_level > 0 THEN
1273: oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE FOR CUST ' ) ;
1274: END IF;
1275: IF l_debug_level > 0 THEN
1276: oe_debug_pub.add( 'SQL_STAT ' ||L_SQL_STAT ) ;
1277: END IF;
1272: IF l_debug_level > 0 THEN
1273: oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE FOR CUST ' ) ;
1274: END IF;
1275: IF l_debug_level > 0 THEN
1276: oe_debug_pub.add( 'SQL_STAT ' ||L_SQL_STAT ) ;
1277: END IF;
1278: --3549675
1279: /*
1280: EXECUTE IMMEDIATE l_sql_stat
1302: end if;
1303: --for ER 5926405,6346045 changes end here
1304:
1305: IF l_debug_level > 0 THEN
1306: oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE FOR CUST ' ) ;
1307: END IF;
1308:
1309:
1310:
1346: , :x_available_yn);
1347: END;';
1348:
1349: IF l_debug_level > 0 THEN
1350: oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE FOR CUST-IS_SERVICE_AVAIL' ) ;
1351: END IF;
1352: IF l_debug_level > 0 THEN
1353: oe_debug_pub.add( 'SQL_STAT ' ||L_SQL_STAT ) ;
1354: END IF;
1349: IF l_debug_level > 0 THEN
1350: oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE FOR CUST-IS_SERVICE_AVAIL' ) ;
1351: END IF;
1352: IF l_debug_level > 0 THEN
1353: oe_debug_pub.add( 'SQL_STAT ' ||L_SQL_STAT ) ;
1354: END IF;
1355: EXECUTE IMMEDIATE l_sql_stat -- added request_date to fix 1720185
1356: USING IN p_x_line_rec.inventory_item_id
1357: , IN p_x_line_rec.sold_to_org_id
1369:
1370:
1371: -- debug mesasges added added as part of 2170348
1372: IF l_debug_level > 0 THEN
1373: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1374: END IF;
1375: IF l_debug_level > 0 THEN
1376: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_X_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1377: END IF;
1372: IF l_debug_level > 0 THEN
1373: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1374: END IF;
1375: IF l_debug_level > 0 THEN
1376: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_X_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1377: END IF;
1378: IF l_debug_level > 0 THEN
1379: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_X_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1380: END IF;
1375: IF l_debug_level > 0 THEN
1376: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_X_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1377: END IF;
1378: IF l_debug_level > 0 THEN
1379: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_X_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1380: END IF;
1381: IF l_debug_level > 0 THEN
1382: oe_debug_pub.add( ' CUST PROD LINE ID : ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) , 5 ) ;
1383: END IF;
1378: IF l_debug_level > 0 THEN
1379: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_X_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1380: END IF;
1381: IF l_debug_level > 0 THEN
1382: oe_debug_pub.add( ' CUST PROD LINE ID : ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) , 5 ) ;
1383: END IF;
1384: IF l_debug_level > 0 THEN
1385: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_X_LINE_REC.REQUEST_DATE ) , 5 ) ;
1386: END IF;
1381: IF l_debug_level > 0 THEN
1382: oe_debug_pub.add( ' CUST PROD LINE ID : ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) , 5 ) ;
1383: END IF;
1384: IF l_debug_level > 0 THEN
1385: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_X_LINE_REC.REQUEST_DATE ) , 5 ) ;
1386: END IF;
1387: IF l_debug_level > 0 THEN
1388: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1389: END IF;
1384: IF l_debug_level > 0 THEN
1385: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_X_LINE_REC.REQUEST_DATE ) , 5 ) ;
1386: END IF;
1387: IF l_debug_level > 0 THEN
1388: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1389: END IF;
1390:
1391: IF l_debug_level > 0 THEN
1392: oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE FOR CUST-IS_SERVICE_AVAIL' ) ;
1388: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1389: END IF;
1390:
1391: IF l_debug_level > 0 THEN
1392: oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE FOR CUST-IS_SERVICE_AVAIL' ) ;
1393: END IF;
1394:
1395: /* OR added for 2282076 */
1396: IF l_available_yn = 'N' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1408:
1409: END IF;
1410:
1411: IF l_debug_level > 0 THEN
1412: oe_debug_pub.add( 'JPN: CUST PROD SERVICE QTY IS ' || L_SERVICE_QTY ) ;
1413: END IF;
1414: IF l_debug_level > 0 THEN
1415: oe_debug_pub.add( 'JPN: CUST PROD ORDERED QTY IS ' || P_X_LINE_REC.ORDERED_QUANTITY ) ;
1416: END IF;
1411: IF l_debug_level > 0 THEN
1412: oe_debug_pub.add( 'JPN: CUST PROD SERVICE QTY IS ' || L_SERVICE_QTY ) ;
1413: END IF;
1414: IF l_debug_level > 0 THEN
1415: oe_debug_pub.add( 'JPN: CUST PROD ORDERED QTY IS ' || P_X_LINE_REC.ORDERED_QUANTITY ) ;
1416: END IF;
1417: --IF l_service_qty <> p_x_line_rec.ordered_quantity THEN
1418: IF p_x_line_rec.ordered_quantity <> Nvl (l_fulfilled_quantity,l_service_qty)THEN --5699215
1419: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1433: p_x_line_rec.service_number := 1;
1434: p_x_line_rec.ordered_quantity := Nvl (l_fulfilled_quantity,l_service_qty); --5699215
1435: -- x_return_status := FND_API.G_RET_STS_SUCCESS;
1436: IF l_debug_level > 0 THEN
1437: oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1438: END IF;
1439: /* ELSIF (p_x_line_rec.service_reference_type_code is NULL) OR
1440: (p_x_line_rec.service_reference_type_code = FND_API.G_MISS_CHAR) THEN
1441: OE_DEBUG_PUB.ADD('IN Get_Service_Attribute - where code is NULL');
1437: oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1438: END IF;
1439: /* ELSIF (p_x_line_rec.service_reference_type_code is NULL) OR
1440: (p_x_line_rec.service_reference_type_code = FND_API.G_MISS_CHAR) THEN
1441: OE_DEBUG_PUB.ADD('IN Get_Service_Attribute - where code is NULL');
1442: NULL; */
1443: ELSE
1444: OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
1445: 'Get_Service_Attribute - Invalid Context');
1444: OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
1445: 'Get_Service_Attribute - Invalid Context');
1446: -- x_return_status := FND_API.G_RET_STS_ERROR;
1447: IF l_debug_level > 0 THEN
1448: oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1449: END IF;
1450: END IF;
1451:
1452: END Get_Service_Attribute;
1474: l_service_duration NUMBER;
1475: l_service_period VARCHAR2(3);
1476:
1477: --
1478: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1479: --
1480: BEGIN
1481: -- oe_debug_pub.Debug_On;
1482:
1477: --
1478: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1479: --
1480: BEGIN
1481: -- oe_debug_pub.Debug_On;
1482:
1483: IF l_debug_level > 0 THEN
1484: oe_debug_pub.add( 'ENTERING GET_SERVICE_DURATION' ) ;
1485: END IF;
1480: BEGIN
1481: -- oe_debug_pub.Debug_On;
1482:
1483: IF l_debug_level > 0 THEN
1484: oe_debug_pub.add( 'ENTERING GET_SERVICE_DURATION' ) ;
1485: END IF;
1486:
1487: x_line_rec := p_line_rec;
1488: /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.GET_DURATION', l_return_status);
1547: END IF;
1548: x_return_status := l_return_status;
1549: IF l_return_status is NULL THEN
1550: IF l_debug_level > 0 THEN
1551: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT RETURNED VALUE' ) ;
1552: END IF;
1553: --x_return_status := FND_API.G_RET_STS_ERROR;
1554: END IF;
1555: IF l_debug_level > 0 THEN
1552: END IF;
1553: --x_return_status := FND_API.G_RET_STS_ERROR;
1554: END IF;
1555: IF l_debug_level > 0 THEN
1556: oe_debug_pub.add( 'EXITING GET_SERVICE_DURATION' ) ;
1557: END IF;
1558: ELSE
1559: IF l_debug_level > 0 THEN
1560: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT EXISTS' ) ;
1556: oe_debug_pub.add( 'EXITING GET_SERVICE_DURATION' ) ;
1557: END IF;
1558: ELSE
1559: IF l_debug_level > 0 THEN
1560: oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT EXISTS' ) ;
1561: END IF;
1562: --x_return_status := FND_API.G_RET_STS_ERROR;
1563: END IF;
1564: END Get_Service_Duration;
1595: l_top_model_id NUMBER; -- 2331301
1596: l_serviceable VARCHAR2(1); -- 2331301
1597: l_fulfilled_quantity number := null; --5699215
1598: --
1599: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1600: --
1601: BEGIN
1602:
1603: -- oe_debug_pub.Debug_On;
1599: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1600: --
1601: BEGIN
1602:
1603: -- oe_debug_pub.Debug_On;
1604:
1605: IF l_debug_level > 0 THEN
1606: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE' ) ;
1607: END IF;
1602:
1603: -- oe_debug_pub.Debug_On;
1604:
1605: IF l_debug_level > 0 THEN
1606: oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE' ) ;
1607: END IF;
1608:
1609: x_line_rec := p_line_rec;
1610: IF (p_line_rec.service_reference_type_code is NULL) THEN
1711:
1712: -- debug messages added as part of 2170348
1713:
1714: IF l_debug_level > 0 THEN
1715: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1716: END IF;
1717: IF l_debug_level > 0 THEN
1718: oe_debug_pub.add( ' INVENTORY ITEM ID : ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 5 ) ;
1719: END IF;
1714: IF l_debug_level > 0 THEN
1715: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1716: END IF;
1717: IF l_debug_level > 0 THEN
1718: oe_debug_pub.add( ' INVENTORY ITEM ID : ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 5 ) ;
1719: END IF;
1720: IF l_debug_level > 0 THEN
1721: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1722: END IF;
1717: IF l_debug_level > 0 THEN
1718: oe_debug_pub.add( ' INVENTORY ITEM ID : ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 5 ) ;
1719: END IF;
1720: IF l_debug_level > 0 THEN
1721: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1722: END IF;
1723: IF l_debug_level > 0 THEN
1724: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1725: END IF;
1720: IF l_debug_level > 0 THEN
1721: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1722: END IF;
1723: IF l_debug_level > 0 THEN
1724: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1725: END IF;
1726: IF l_debug_level > 0 THEN
1727: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_LINE_REC.REQUEST_DATE ) , 5 ) ;
1728: END IF;
1723: IF l_debug_level > 0 THEN
1724: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1725: END IF;
1726: IF l_debug_level > 0 THEN
1727: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_LINE_REC.REQUEST_DATE ) , 5 ) ;
1728: END IF;
1729:
1730: IF l_debug_level > 0 THEN
1731: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1727: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_LINE_REC.REQUEST_DATE ) , 5 ) ;
1728: END IF;
1729:
1730: IF l_debug_level > 0 THEN
1731: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1732: END IF;
1733:
1734: /* OR added for 2282076 */
1735: IF l_available_yn = 'N' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1795: x_line_rec.service_number := 1;
1796: x_line_rec.ordered_quantity := Nvl(l_fulfilled_quantity,l_service_qty); --5699215
1797:
1798: IF l_debug_level > 0 THEN
1799: oe_debug_pub.add( 'SERVICE NUMBER IS :' || TO_CHAR ( X_LINE_REC.SERVICE_NUMBER ) ) ;
1800: END IF;
1801: IF l_debug_level > 0 THEN
1802: oe_debug_pub.add( 'LINE NUMBER IS :'|| TO_CHAR ( X_LINE_REC.LINE_NUMBER ) ) ;
1803: END IF;
1798: IF l_debug_level > 0 THEN
1799: oe_debug_pub.add( 'SERVICE NUMBER IS :' || TO_CHAR ( X_LINE_REC.SERVICE_NUMBER ) ) ;
1800: END IF;
1801: IF l_debug_level > 0 THEN
1802: oe_debug_pub.add( 'LINE NUMBER IS :'|| TO_CHAR ( X_LINE_REC.LINE_NUMBER ) ) ;
1803: END IF;
1804: IF l_debug_level > 0 THEN
1805: oe_debug_pub.add( 'LINE ID IS :'|| TO_CHAR ( L_LINE_ID ) ) ;
1806: END IF;
1801: IF l_debug_level > 0 THEN
1802: oe_debug_pub.add( 'LINE NUMBER IS :'|| TO_CHAR ( X_LINE_REC.LINE_NUMBER ) ) ;
1803: END IF;
1804: IF l_debug_level > 0 THEN
1805: oe_debug_pub.add( 'LINE ID IS :'|| TO_CHAR ( L_LINE_ID ) ) ;
1806: END IF;
1807:
1808:
1809: END IF;
1807:
1808:
1809: END IF;
1810: IF l_debug_level > 0 THEN
1811: oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1812: END IF;
1813:
1814: EXCEPTION
1815:
1905:
1906:
1907: -- debug mesasges added added as part of 2170348
1908: IF l_debug_level > 0 THEN
1909: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1910: END IF;
1911: IF l_debug_level > 0 THEN
1912: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1913: END IF;
1908: IF l_debug_level > 0 THEN
1909: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1910: END IF;
1911: IF l_debug_level > 0 THEN
1912: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1913: END IF;
1914: IF l_debug_level > 0 THEN
1915: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1916: END IF;
1911: IF l_debug_level > 0 THEN
1912: oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1913: END IF;
1914: IF l_debug_level > 0 THEN
1915: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1916: END IF;
1917: IF l_debug_level > 0 THEN
1918: oe_debug_pub.add( ' CUST PROD LINE ID : ' || TO_CHAR ( P_LINE_REC.SERVICE_REFERENCE_LINE_ID ) , 5 ) ;
1919: END IF;
1914: IF l_debug_level > 0 THEN
1915: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1916: END IF;
1917: IF l_debug_level > 0 THEN
1918: oe_debug_pub.add( ' CUST PROD LINE ID : ' || TO_CHAR ( P_LINE_REC.SERVICE_REFERENCE_LINE_ID ) , 5 ) ;
1919: END IF;
1920: IF l_debug_level > 0 THEN
1921: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_LINE_REC.REQUEST_DATE ) , 5 ) ;
1922: END IF;
1917: IF l_debug_level > 0 THEN
1918: oe_debug_pub.add( ' CUST PROD LINE ID : ' || TO_CHAR ( P_LINE_REC.SERVICE_REFERENCE_LINE_ID ) , 5 ) ;
1919: END IF;
1920: IF l_debug_level > 0 THEN
1921: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_LINE_REC.REQUEST_DATE ) , 5 ) ;
1922: END IF;
1923: IF l_debug_level > 0 THEN
1924: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1925: END IF;
1920: IF l_debug_level > 0 THEN
1921: oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_LINE_REC.REQUEST_DATE ) , 5 ) ;
1922: END IF;
1923: IF l_debug_level > 0 THEN
1924: oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1925: END IF;
1926:
1927: /* OR added for 2282076 */
1928: IF l_available_yn = 'N' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1958: -- x_line_rec.ordered_quantity := l_service_qty;
1959: x_line_rec.ordered_quantity := Nvl(l_fulfilled_quantity,l_service_qty);--5699215
1960: x_return_status := FND_API.G_RET_STS_SUCCESS;
1961: IF l_debug_level > 0 THEN
1962: oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1963: END IF;
1964: ELSIF (p_line_rec.service_reference_type_code is NULL) OR
1965: (p_line_rec.service_reference_type_code = FND_API.G_MISS_CHAR) THEN
1966: IF l_debug_level > 0 THEN
1963: END IF;
1964: ELSIF (p_line_rec.service_reference_type_code is NULL) OR
1965: (p_line_rec.service_reference_type_code = FND_API.G_MISS_CHAR) THEN
1966: IF l_debug_level > 0 THEN
1967: oe_debug_pub.add( 'IN GET_SERVICE_ATTRIBUTE - WHERE CODE IS NULL' ) ;
1968: END IF;
1969: NULL;
1970: ELSE
1971: OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
1971: OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
1972: 'Get_Service_Attribute - Invalid Context');
1973: x_return_status := FND_API.G_RET_STS_ERROR;
1974: IF l_debug_level > 0 THEN
1975: oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1976: END IF;
1977: END IF;
1978:
1979: END Get_Service_Attribute;
1990:
1991: )
1992: IS
1993: --
1994: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1995: --
1996: BEGIN
1997: IF l_debug_level > 0 THEN
1998: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_LINE_ID' ) ;
1994: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1995: --
1996: BEGIN
1997: IF l_debug_level > 0 THEN
1998: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_LINE_ID' ) ;
1999: END IF;
2000:
2001: If p_order_number is not null and
2002: p_line_number is not null and
2013: AND l.item_type_code <> 'SERVICE';
2014: End If;
2015: x_return_status := FND_API.G_RET_STS_SUCCESS;
2016: IF l_debug_level > 0 THEN
2017: oe_debug_pub.add( 'EXITING GET_SERVICE_REF_LINE_ID' ) ;
2018: END IF;
2019: EXCEPTION
2020: WHEN OTHERS THEN
2021: OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
2021: OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
2022: 'Get_Service_Ref_Line_Id -Invalid Order/Line No.');
2023: /* uncomment later
2024: x_return_status := FND_API.G_RET_STS_ERROR;
2025: OE_DEBUG_PUB.ADD('Exiting Get_Service_Ref_Line_Id');
2026: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2027: */
2028: END Get_Service_Ref_Line_Id;
2029:
2041: l_exists VARCHAR2(1);
2042: l_reference_system_id NUMBER;
2043:
2044: --
2045: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2046: --
2047: BEGIN
2048: IF l_debug_level > 0 THEN
2049: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_SYSTEM_ID' ) ;
2045: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2046: --
2047: BEGIN
2048: IF l_debug_level > 0 THEN
2049: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_SYSTEM_ID' ) ;
2050: END IF;
2051:
2052: -- IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2053: IF NOT (IB_ACTIVE()) THEN
2092: END IF;
2093:
2094: x_return_status := FND_API.G_RET_STS_SUCCESS;
2095: IF l_debug_level > 0 THEN
2096: oe_debug_pub.add( 'EXITING GET_SERVICE_REF_SYSTEM_ID' ) ;
2097: END IF;
2098: EXCEPTION
2099: WHEN OTHERS THEN
2100: OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
2100: OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
2101: 'Get_Service_Ref_System_Id - Invalid System No.');
2102: x_return_status := FND_API.G_RET_STS_ERROR;
2103: IF l_debug_level > 0 THEN
2104: oe_debug_pub.add( 'EXITING GET_SERVICE_REF_SYSTEM_ID' ) ;
2105: END IF;
2106: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2107: END Get_Service_Ref_System_Id;
2108:
2118: l_sql_stat VARCHAR2(250);
2119: l_system_name VARCHAR2(50);
2120: l_exists VARCHAR2(1);
2121: --
2122: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2123: --
2124: BEGIN
2125: IF l_debug_level > 0 THEN
2126: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_SYSTEM_NAME' ) ;
2122: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2123: --
2124: BEGIN
2125: IF l_debug_level > 0 THEN
2126: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_SYSTEM_NAME' ) ;
2127: END IF;
2128:
2129: -- IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2130: IF NOT (IB_ACTIVE()) THEN
2166: END IF;
2167:
2168: x_return_status := FND_API.G_RET_STS_SUCCESS;
2169: IF l_debug_level > 0 THEN
2170: oe_debug_pub.add( 'EXITING SUCCESS GET_SERVICE_REF_SYSTEM_NAME' ) ;
2171: END IF;
2172:
2173: Exception
2174: When others then
2173: Exception
2174: When others then
2175: x_return_status := FND_API.G_RET_STS_SUCCESS;
2176: IF l_debug_level > 0 THEN
2177: oe_debug_pub.add( 'EXITING ERROR GET_SERVICE_REF_SYSTEM_NAME' ) ;
2178: END IF;
2179: END Get_Service_Ref_System_Name;
2180:
2181: PROCEDURE Get_Service_Ref_Cust_Product
2191: l_exists VARCHAR2(1);
2192: l_cust_product VARCHAR2(50);
2193:
2194: --
2195: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2196: --
2197: BEGIN
2198: IF l_debug_level > 0 THEN
2199: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_CUST_PRODUCT' ) ;
2195: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2196: --
2197: BEGIN
2198: IF l_debug_level > 0 THEN
2199: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_CUST_PRODUCT' ) ;
2200: END IF;
2201: IF l_debug_level > 0 THEN
2202: oe_debug_pub.add( 'CUSTOMER ID IS ' || P_CUSTOMER_ID ) ;
2203: END IF;
2198: IF l_debug_level > 0 THEN
2199: oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_CUST_PRODUCT' ) ;
2200: END IF;
2201: IF l_debug_level > 0 THEN
2202: oe_debug_pub.add( 'CUSTOMER ID IS ' || P_CUSTOMER_ID ) ;
2203: END IF;
2204: IF l_debug_level > 0 THEN
2205: oe_debug_pub.add( 'LINE ID IS ' || P_REFERENCE_LINE_ID ) ;
2206: END IF;
2201: IF l_debug_level > 0 THEN
2202: oe_debug_pub.add( 'CUSTOMER ID IS ' || P_CUSTOMER_ID ) ;
2203: END IF;
2204: IF l_debug_level > 0 THEN
2205: oe_debug_pub.add( 'LINE ID IS ' || P_REFERENCE_LINE_ID ) ;
2206: END IF;
2207:
2208: -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2209: IF NOT(IB_ACTIVE()) THEN
2224: END;
2225: END IF;
2226:
2227: IF l_debug_level > 0 THEN
2228: oe_debug_pub.add( 'AFTER EXISTS' ) ;
2229: END IF;
2230: IF l_exists = 'Y' THEN
2231: -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2232: IF NOT(IB_ACTIVE()) THEN
2242: WHERE customer_product_id = :l_customer_product_id';
2243: -- AND account_id = :l_customer_id'; --3572516
2244: END IF;
2245: IF l_debug_level > 0 THEN
2246: oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE' ) ;
2247: END IF;
2248: IF l_debug_level > 0 THEN
2249: oe_debug_pub.add( 'SQL STATEMENT BEING EXECUTED'|| L_SQL_STAT ) ;
2250: END IF;
2245: IF l_debug_level > 0 THEN
2246: oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE' ) ;
2247: END IF;
2248: IF l_debug_level > 0 THEN
2249: oe_debug_pub.add( 'SQL STATEMENT BEING EXECUTED'|| L_SQL_STAT ) ;
2250: END IF;
2251: --3572516 using clause is altered accordingly
2252: Execute Immediate l_sql_stat Into l_cust_product
2253: Using p_reference_line_id;
2253: Using p_reference_line_id;
2254: -- Using p_reference_line_id, p_customer_id;
2255: x_cust_product := l_cust_product;
2256: IF l_debug_level > 0 THEN
2257: oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE' ) ;
2258: END IF;
2259: END IF;
2260:
2261: x_return_status := FND_API.G_RET_STS_SUCCESS;
2259: END IF;
2260:
2261: x_return_status := FND_API.G_RET_STS_SUCCESS;
2262: IF l_debug_level > 0 THEN
2263: oe_debug_pub.add( 'EXITING SUCCESS GET_SERVICE_REF_CUST_PRODUCT' ) ;
2264: END IF;
2265:
2266: Exception
2267: When others then
2266: Exception
2267: When others then
2268: x_return_status := FND_API.G_RET_STS_SUCCESS;
2269: IF l_debug_level > 0 THEN
2270: oe_debug_pub.add( 'EXITING ERROR GET_SERVICE_REF_CUST_PRODUCT' ) ;
2271: END IF;
2272: END Get_Service_Ref_Cust_Product;
2273:
2274:
2288: l_order_line_id NUMBER;
2289: l_query_type VARCHAR2(1);--for ER 5926405,6346045
2290:
2291: --
2292: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2293: --
2294: BEGIN
2295: IF l_debug_level > 0 THEN
2296: oe_debug_pub.add( 'ENTERING GET_CUST_PRODUCT_LINE_ID' ) ;
2292: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2293: --
2294: BEGIN
2295: IF l_debug_level > 0 THEN
2296: oe_debug_pub.add( 'ENTERING GET_CUST_PRODUCT_LINE_ID' ) ;
2297: END IF;
2298: IF l_debug_level > 0 THEN
2299: oe_debug_pub.add( 'CUSTOMER ID IS ' || P_CUSTOMER_ID ) ;
2300: END IF;
2295: IF l_debug_level > 0 THEN
2296: oe_debug_pub.add( 'ENTERING GET_CUST_PRODUCT_LINE_ID' ) ;
2297: END IF;
2298: IF l_debug_level > 0 THEN
2299: oe_debug_pub.add( 'CUSTOMER ID IS ' || P_CUSTOMER_ID ) ;
2300: END IF;
2301: IF l_debug_level > 0 THEN
2302: oe_debug_pub.add( 'LINE ID IS ' || P_REFERENCE_LINE_ID ) ;
2303: END IF;
2298: IF l_debug_level > 0 THEN
2299: oe_debug_pub.add( 'CUSTOMER ID IS ' || P_CUSTOMER_ID ) ;
2300: END IF;
2301: IF l_debug_level > 0 THEN
2302: oe_debug_pub.add( 'LINE ID IS ' || P_REFERENCE_LINE_ID ) ;
2303: END IF;
2304:
2305: -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2306: --for ER 5926405,6346045
2323: END IF;
2324: */
2325:
2326: IF l_debug_level > 0 THEN
2327: oe_debug_pub.add( 'AFTER EXISTS' ) ;
2328: END IF;
2329: /* IF l_exists = 'Y' THEN
2330: -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2331: IF NOT(IB_ACTIVE()) THEN
2343: AND account_id = :l_customer_id';
2344: -- END IF;
2345:
2346: IF l_debug_level > 0 THEN
2347: oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE' ) ;
2348: END IF;
2349: IF l_debug_level > 0 THEN
2350: oe_debug_pub.add( 'SQL STATEMENT BEING EXECUTED'|| L_SQL_STAT ) ;
2351: END IF;
2346: IF l_debug_level > 0 THEN
2347: oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE' ) ;
2348: END IF;
2349: IF l_debug_level > 0 THEN
2350: oe_debug_pub.add( 'SQL STATEMENT BEING EXECUTED'|| L_SQL_STAT ) ;
2351: END IF;
2352:
2353: /*Execute Immediate l_sql_stat Into l_order_line_id
2354: Using p_reference_line_id, p_customer_id;
2370:
2371: x_cust_product_line_id := l_order_line_id;
2372:
2373: IF l_debug_level > 0 THEN
2374: oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE' ) ;
2375: END IF;
2376: -- END IF;
2377:
2378: x_return_status := FND_API.G_RET_STS_SUCCESS;
2376: -- END IF;
2377:
2378: x_return_status := FND_API.G_RET_STS_SUCCESS;
2379: IF l_debug_level > 0 THEN
2380: oe_debug_pub.add( 'EXITING SUCCESS GET_CUST_PRODUCT_LINE_ID' ) ;
2381: END IF;
2382:
2383: Exception
2384: WHEN NO_DATA_FOUND THEN
2388: OE_MSG_PUB.Add;
2389: -- Added for Bug 6889117 End
2390:
2391: IF l_debug_level > 0 THEN
2392: oe_debug_pub.add( 'GET_CUST_PRODUCT_LINE_ID: NO DATA FOUND' ) ;
2393: END IF;
2394: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2395:
2396: WHEN FND_API.G_EXC_ERROR THEN
2398:
2399: WHEN OTHERS THEN
2400: x_return_status := FND_API.G_RET_STS_ERROR;
2401: IF l_debug_level > 0 THEN
2402: oe_debug_pub.add( 'EXITING ERROR GET_CUST_PRODUCT_LINE_ID' ) ;
2403: END IF;
2404: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2405: END Get_Cust_Product_Line_Id;
2406:
2421: l_sql_stat VARCHAR2(250);
2422: l_exists VARCHAR2(1);
2423: I NUMBER;
2424: --
2425: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2426: --
2427: BEGIN
2428: IF l_debug_level > 0 THEN
2429: oe_debug_pub.add( 'ENTERING GET_CUST_PROD_RG' ) ;
2425: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2426: --
2427: BEGIN
2428: IF l_debug_level > 0 THEN
2429: oe_debug_pub.add( 'ENTERING GET_CUST_PROD_RG' ) ;
2430: END IF;
2431:
2432: -- IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2433: IF NOT (IB_ACTIVE()) THEN
2485: END IF;
2486: x_srv_cust_prod_tbl := OE_SERVICE_UTIL.l_srv_cust_prod_tbl;
2487: x_return_status := FND_API.G_RET_STS_SUCCESS;
2488: IF l_debug_level > 0 THEN
2489: oe_debug_pub.add( 'EXITING SUCCESS GET_CUST_PROD_RG' ) ;
2490: END IF;
2491:
2492: Exception
2493: When others then
2492: Exception
2493: When others then
2494: x_return_status := FND_API.G_RET_STS_SUCCESS;
2495: IF l_debug_level > 0 THEN
2496: oe_debug_pub.add( 'EXITING ERROR GET_CUST_PROD_RG' ) ;
2497: END IF;
2498:
2499: END Get_Cust_Prod_RG;
2500:
2510: l_return_status VARCHAR2(1);
2511: l_msg_count NUMBER;
2512: l_msg_data VARCHAR2(500);
2513: --
2514: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2515: --
2516: BEGIN
2517: IF l_debug_level > 0 THEN
2518: oe_debug_pub.add( 'ENTERING GET_AVAIL_SERVICE_RG' ) ;
2514: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2515: --
2516: BEGIN
2517: IF l_debug_level > 0 THEN
2518: oe_debug_pub.add( 'ENTERING GET_AVAIL_SERVICE_RG' ) ;
2519: END IF;
2520:
2521: -- Call Describe_Proc to check for the existance of the CRM's
2522: -- AVAILABLE_SERVICES API. If exists Then Call it else No Problem.
2553:
2554: OE_SERVICE_UTIL.l_srv_tbl := l_service_tbl; ';
2555:
2556: IF l_debug_level > 0 THEN
2557: oe_debug_pub.add( L_SQL_STAT ) ;
2558: END IF;
2559:
2560: Execute Immediate l_sql_stat
2561: Using p_service_rec.product_item_id, p_service_rec.customer_id,
2563:
2564: -- debug messages added for bug 2170348
2565:
2566: IF l_debug_level > 0 THEN
2567: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.OKS_AVAILABLE_SERVICES : ' ) ;
2568: END IF;
2569: IF l_debug_level > 0 THEN
2570: oe_debug_pub.add( ' PRODUCT ITEM ID : ' || TO_CHAR ( P_SERVICE_REC.PRODUCT_ITEM_ID ) ) ;
2571: END IF;
2566: IF l_debug_level > 0 THEN
2567: oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.OKS_AVAILABLE_SERVICES : ' ) ;
2568: END IF;
2569: IF l_debug_level > 0 THEN
2570: oe_debug_pub.add( ' PRODUCT ITEM ID : ' || TO_CHAR ( P_SERVICE_REC.PRODUCT_ITEM_ID ) ) ;
2571: END IF;
2572: IF l_debug_level > 0 THEN
2573: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_SERVICE_REC.CUSTOMER_ID ) ) ;
2574: END IF;
2569: IF l_debug_level > 0 THEN
2570: oe_debug_pub.add( ' PRODUCT ITEM ID : ' || TO_CHAR ( P_SERVICE_REC.PRODUCT_ITEM_ID ) ) ;
2571: END IF;
2572: IF l_debug_level > 0 THEN
2573: oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_SERVICE_REC.CUSTOMER_ID ) ) ;
2574: END IF;
2575:
2576: x_return_status := FND_API.G_RET_STS_SUCCESS;
2577: IF l_debug_level > 0 THEN
2574: END IF;
2575:
2576: x_return_status := FND_API.G_RET_STS_SUCCESS;
2577: IF l_debug_level > 0 THEN
2578: oe_debug_pub.add( 'EXITING ERROR GET_AVAIL_SERVICE_RG' ) ;
2579: END IF;
2580: END IF;
2581:
2582: Exception
2582: Exception
2583: When others then
2584: x_return_status := FND_API.G_RET_STS_SUCCESS;
2585: IF l_debug_level > 0 THEN
2586: oe_debug_pub.add( 'EXITING ERROR GET_AVAIL_SERVICE_RG' ) ;
2587: END IF;
2588:
2589: END Get_Avail_Service_RG;
2590:
2591: PROCEDURE Retrieve_OC_Messages IS
2592: l_msg_count NUMBER;
2593: l_msg_data VARCHAR2(2000);
2594: x_msg_data VARCHAR2(2000);
2595: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2596: BEGIN
2597:
2598: IF l_debug_level > 0 THEN
2599: oe_debug_pub.add( 'ENTERING RETRIEVE OC MESSAGES' ) ;
2595: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2596: BEGIN
2597:
2598: IF l_debug_level > 0 THEN
2599: oe_debug_pub.add( 'ENTERING RETRIEVE OC MESSAGES' ) ;
2600: END IF;
2601: fnd_msg_pub.Count_And_Get
2602: (p_count => l_msg_count,
2603: p_data => l_msg_data
2603: p_data => l_msg_data
2604: );
2605:
2606: IF l_debug_level > 0 THEN
2607: oe_debug_pub.add( 'NO. OF OC MESSAGES :' || L_MSG_COUNT ) ;
2608: END IF;
2609: for k in 1 ..l_msg_count loop
2610: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
2611: p_encoded => 'F'
2612: );
2613: -- For bug 3574480. To show the error reason to the user.
2614: oe_msg_pub.add_text(p_message_text =>X_MSG_DATA);
2615: IF l_debug_level > 0 THEN
2616: oe_debug_pub.add( SUBSTR ( X_MSG_DATA , 1 , 255 ) ) ;
2617: END IF;
2618: end loop;
2619: END;
2620:
2629:
2630:
2631: l_exists VARCHAR2(30);
2632: l_line_id NUMBER := oe_line_security.g_record.line_id;
2633: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2634: BEGIN
2635: Select 'EXISTS' into
2636: l_exists
2637: from oe_order_lines_all
2670: SELECT line_id
2671: FROM oe_order_lines
2672: WHERE p_service_reference_line_id = service_reference_line_id
2673: ORDER BY line_id;
2674: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2675: BEGIN
2676:
2677: IF l_debug_level > 0 THEN
2678: oe_debug_pub.add( 'ENTERING OE_SERVICE_UTIL.NEW_SERVICE_LINES' , 1 ) ;
2674: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2675: BEGIN
2676:
2677: IF l_debug_level > 0 THEN
2678: oe_debug_pub.add( 'ENTERING OE_SERVICE_UTIL.NEW_SERVICE_LINES' , 1 ) ;
2679: END IF;
2680:
2681: --lchen fix bug 2027650
2682: I := p_x_line_tbl.FIRST;
2684:
2685: -- l_line_rec := p_x_line_tbl(i); /*3676393*/
2686:
2687: IF l_debug_level > 0 THEN
2688: oe_debug_pub.add( 'I = ' || I , 1 ) ;
2689: END IF;
2690: IF l_debug_level > 0 THEN
2691: oe_debug_pub.add( 'OPERATION:' || p_x_line_tbl(i).OPERATION , 1 ) ;
2692: END IF;
2687: IF l_debug_level > 0 THEN
2688: oe_debug_pub.add( 'I = ' || I , 1 ) ;
2689: END IF;
2690: IF l_debug_level > 0 THEN
2691: oe_debug_pub.add( 'OPERATION:' || p_x_line_tbl(i).OPERATION , 1 ) ;
2692: END IF;
2693: IF l_debug_level > 0 THEN
2694: oe_debug_pub.add( 'SERVICE_REFERENCE_LINE_ID:' || p_x_line_tbl(i).SERVICE_REFERENCE_LINE_ID , 1 ) ;
2695: END IF;
2690: IF l_debug_level > 0 THEN
2691: oe_debug_pub.add( 'OPERATION:' || p_x_line_tbl(i).OPERATION , 1 ) ;
2692: END IF;
2693: IF l_debug_level > 0 THEN
2694: oe_debug_pub.add( 'SERVICE_REFERENCE_LINE_ID:' || p_x_line_tbl(i).SERVICE_REFERENCE_LINE_ID , 1 ) ;
2695: END IF;
2696: /*3676393-Altered the l_line_rec to p_x_line_tbl in the below conditions*/
2697: IF p_x_line_tbl(i).item_type_code = 'SERVICE' AND
2698: p_x_line_tbl(i).service_reference_type_code = 'ORDER' AND
2702: ( p_x_line_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE or
2703: p_x_line_tbl(i).operation= OE_GLOBALS.G_OPR_UPDATE) THEN
2704:
2705: IF l_debug_level > 0 THEN
2706: oe_debug_pub.add( 'IN OE_SERVICE_UTIL.UPDATE_SERVICE_LINES.ENTERING OE_SERVICE_UTIL.NEW_SERVICE_LINES' , 1 ) ;
2707: END IF;
2708:
2709: -- oe_debug_pub.ADD('headerId:' || l_line_rec.header_id , 1);
2710: -- oe_debug_pub.ADD('line_id:' || l_line_rec.line_id , 1);
2705: IF l_debug_level > 0 THEN
2706: oe_debug_pub.add( 'IN OE_SERVICE_UTIL.UPDATE_SERVICE_LINES.ENTERING OE_SERVICE_UTIL.NEW_SERVICE_LINES' , 1 ) ;
2707: END IF;
2708:
2709: -- oe_debug_pub.ADD('headerId:' || l_line_rec.header_id , 1);
2710: -- oe_debug_pub.ADD('line_id:' || l_line_rec.line_id , 1);
2711: -- oe_debug_pub.ADD('service_reference_line_id:' || l_line_rec.service_reference_line_id , 1);
2712:
2713: l_line_rec := p_x_line_tbl(i); /*3676393*/
2706: oe_debug_pub.add( 'IN OE_SERVICE_UTIL.UPDATE_SERVICE_LINES.ENTERING OE_SERVICE_UTIL.NEW_SERVICE_LINES' , 1 ) ;
2707: END IF;
2708:
2709: -- oe_debug_pub.ADD('headerId:' || l_line_rec.header_id , 1);
2710: -- oe_debug_pub.ADD('line_id:' || l_line_rec.line_id , 1);
2711: -- oe_debug_pub.ADD('service_reference_line_id:' || l_line_rec.service_reference_line_id , 1);
2712:
2713: l_line_rec := p_x_line_tbl(i); /*3676393*/
2714:
2707: END IF;
2708:
2709: -- oe_debug_pub.ADD('headerId:' || l_line_rec.header_id , 1);
2710: -- oe_debug_pub.ADD('line_id:' || l_line_rec.line_id , 1);
2711: -- oe_debug_pub.ADD('service_reference_line_id:' || l_line_rec.service_reference_line_id , 1);
2712:
2713: l_line_rec := p_x_line_tbl(i); /*3676393*/
2714:
2715: OE_Line_Util.query_row(
2769: OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
2770: END IF;
2771:
2772: IF l_debug_level > 0 THEN
2773: oe_debug_pub.add( 'EXITING OE_SERVICE_UTIL.UPDATE_SERVICE_LINES' , 1 ) ;
2774: END IF;
2775:
2776:
2777: EXCEPTION
2843: AND exists (select null from mtl_system_items mtl where
2844: mtl.inventory_item_id = l.inventory_item_id and
2845: mtl.serviceable_product_flag = 'Y');
2846:
2847: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2848: BEGIN
2849:
2850: IF l_debug_level > 0 THEN
2851: oe_debug_pub.add( 'ENTER OE_SERVICE_UTIL.CASCADE_CHANGES' , 1 ) ;
2847: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2848: BEGIN
2849:
2850: IF l_debug_level > 0 THEN
2851: oe_debug_pub.add( 'ENTER OE_SERVICE_UTIL.CASCADE_CHANGES' , 1 ) ;
2852: END IF;
2853:
2854: IF fnd_profile.value('ONT_CASCADE_SERVICE') = 'N' THEN
2855: /* 3128684 */
2853:
2854: IF fnd_profile.value('ONT_CASCADE_SERVICE') = 'N' THEN
2855: /* 3128684 */
2856: IF l_debug_level > 0 THEN
2857: oe_debug_pub.add( 'DO NOT CASCADE SERVICES' , 2 ) ;
2858: END IF;
2859: x_return_status := FND_API.G_RET_STS_SUCCESS;
2860: RETURN;
2861: END IF;
2859: x_return_status := FND_API.G_RET_STS_SUCCESS;
2860: RETURN;
2861: END IF;
2862: IF l_debug_level > 0 THEN
2863: oe_debug_pub.add( 'DO CASCADE SERVICES' , 2 ) ;
2864: END IF;
2865:
2866: OE_Line_Util.Lock_Row( p_line_id => p_parent_line_id
2867: ,p_x_line_rec => l_parent_line_rec
2880: l_service_duration := l_parent_line_rec.service_duration;
2881: l_service_period := l_parent_line_rec.service_period;
2882:
2883: IF l_debug_level > 0 THEN
2884: oe_debug_pub.add( 'CSH L_INV_ITEM_ID = ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 1 ) ;
2885: END IF;
2886: IF l_debug_level > 0 THEN
2887: oe_debug_pub.add( 'CSH L_SERVICE_START_DATE = ' || TO_CHAR ( L_SERVICE_START_DATE , 'DD-MON-YYYY HH24:MI:SS' ) , 1 ) ;
2888: END IF;
2883: IF l_debug_level > 0 THEN
2884: oe_debug_pub.add( 'CSH L_INV_ITEM_ID = ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 1 ) ;
2885: END IF;
2886: IF l_debug_level > 0 THEN
2887: oe_debug_pub.add( 'CSH L_SERVICE_START_DATE = ' || TO_CHAR ( L_SERVICE_START_DATE , 'DD-MON-YYYY HH24:MI:SS' ) , 1 ) ;
2888: END IF;
2889: IF l_debug_level > 0 THEN
2890: oe_debug_pub.add( 'CSH L_SERVICE_END_DATE = ' || TO_CHAR ( L_SERVICE_END_DATE , 'DD-MON-YYYY HH24:MI:SS' ) , 1 ) ;
2891: END IF;
2886: IF l_debug_level > 0 THEN
2887: oe_debug_pub.add( 'CSH L_SERVICE_START_DATE = ' || TO_CHAR ( L_SERVICE_START_DATE , 'DD-MON-YYYY HH24:MI:SS' ) , 1 ) ;
2888: END IF;
2889: IF l_debug_level > 0 THEN
2890: oe_debug_pub.add( 'CSH L_SERVICE_END_DATE = ' || TO_CHAR ( L_SERVICE_END_DATE , 'DD-MON-YYYY HH24:MI:SS' ) , 1 ) ;
2891: END IF;
2892: IF l_debug_level > 0 THEN
2893: oe_debug_pub.add( 'CSH L_SERVICE_DURATION = ' || TO_CHAR ( L_SERVICE_DURATION ) , 1 ) ;
2894: END IF;
2889: IF l_debug_level > 0 THEN
2890: oe_debug_pub.add( 'CSH L_SERVICE_END_DATE = ' || TO_CHAR ( L_SERVICE_END_DATE , 'DD-MON-YYYY HH24:MI:SS' ) , 1 ) ;
2891: END IF;
2892: IF l_debug_level > 0 THEN
2893: oe_debug_pub.add( 'CSH L_SERVICE_DURATION = ' || TO_CHAR ( L_SERVICE_DURATION ) , 1 ) ;
2894: END IF;
2895: IF l_debug_level > 0 THEN
2896: oe_debug_pub.add( 'CSH L_SERVICE_PERIOD = ' || L_SERVICE_PERIOD , 1 ) ;
2897: END IF;
2892: IF l_debug_level > 0 THEN
2893: oe_debug_pub.add( 'CSH L_SERVICE_DURATION = ' || TO_CHAR ( L_SERVICE_DURATION ) , 1 ) ;
2894: END IF;
2895: IF l_debug_level > 0 THEN
2896: oe_debug_pub.add( 'CSH L_SERVICE_PERIOD = ' || L_SERVICE_PERIOD , 1 ) ;
2897: END IF;
2898:
2899:
2900: l_line_count := 0;
2901: OPEN model_children ;
2902: LOOP
2903: FETCH model_children into child_line_id;
2904: IF l_debug_level > 0 THEN
2905: oe_debug_pub.add( 'CSH CHILD_LINE_ID = ' || TO_CHAR ( CHILD_LINE_ID ) , 1 ) ;
2906: END IF;
2907: EXIT when model_children%NOTFOUND;
2908: BEGIN
2909: SELECT LINE_ID
2922: END;
2923: */
2924:
2925: IF l_debug_level > 0 THEN
2926: oe_debug_pub.add( 'CSH L_LINE_ID = ' || TO_CHAR ( L_LINE_ID ) , 1 ) ;
2927: END IF;
2928: l_line_count := l_line_count + 1;
2929: l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
2930: l_line_rec.line_id := l_line_id;
2938: END LOOP;
2939:
2940: IF l_line_count = 0 THEN
2941: IF l_debug_level > 0 THEN
2942: oe_debug_pub.add( 'EXITING OE_SERVICE_UTIL.CASCADE_CHANGES' , 1 ) ;
2943: END IF;
2944: IF l_debug_level > 0 THEN
2945: oe_debug_pub.add( 'NO ROWS TO CASCADE' , 2 ) ;
2946: END IF;
2941: IF l_debug_level > 0 THEN
2942: oe_debug_pub.add( 'EXITING OE_SERVICE_UTIL.CASCADE_CHANGES' , 1 ) ;
2943: END IF;
2944: IF l_debug_level > 0 THEN
2945: oe_debug_pub.add( 'NO ROWS TO CASCADE' , 2 ) ;
2946: END IF;
2947: RETURN;
2948: END IF;
2949:
2947: RETURN;
2948: END IF;
2949:
2950: IF l_debug_level > 0 THEN
2951: oe_debug_pub.add( 'NO OF LINES TO CASCADE: ' || TO_CHAR ( L_LINE_TBL.COUNT ) , 1 ) ;
2952: END IF;
2953:
2954: FOR I IN 1..l_line_tbl.count LOOP
2955:
2989: l_control_rec.clear_api_requests := FALSE;
2990:
2991:
2992: IF l_debug_level > 0 THEN
2993: oe_debug_pub.add( 'CALLING PROCESS ORDER' , 1 ) ;
2994: END IF;
2995:
2996: -- Call OE_Order_PVT.Process_order
2997:
3003: ,x_return_status => l_return_status);
3004:
3005: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3006: IF l_debug_level > 0 THEN
3007: oe_debug_pub.add( 'INSIDE UNEXPECTED ERROR ' , 2 ) ;
3008: END IF;
3009: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3010: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3011: IF l_debug_level > 0 THEN
3008: END IF;
3009: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3010: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3011: IF l_debug_level > 0 THEN
3012: oe_debug_pub.add( 'INSIDE ERROR ' , 2 ) ;
3013: END IF;
3014: RAISE FND_API.G_EXC_ERROR;
3015: END IF;
3016:
3033: -- Clear Table
3034: l_line_tbl.DELETE;
3035:
3036: IF l_debug_level > 0 THEN
3037: oe_debug_pub.add( 'EXITING UPDATE_SERVICE_FOR_OPTIONS' , 1 ) ;
3038: END IF;
3039:
3040: EXCEPTION
3041:
3040: EXCEPTION
3041:
3042: WHEN FND_API.G_EXC_ERROR THEN
3043: IF l_debug_level > 0 THEN
3044: oe_debug_pub.add( 'INSIDE EXCEPTION EXE ERROR ' , 1 ) ;
3045: END IF;
3046: x_return_status := FND_API.G_RET_STS_ERROR;
3047:
3048: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3046: x_return_status := FND_API.G_RET_STS_ERROR;
3047:
3048: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3049: IF l_debug_level > 0 THEN
3050: oe_debug_pub.add( 'INSIDE EXCEPTION UNEXP ERROR ' , 1 ) ;
3051: END IF;
3052: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3053:
3054: WHEN OTHERS THEN
3054: WHEN OTHERS THEN
3055:
3056: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3057: IF l_debug_level > 0 THEN
3058: oe_debug_pub.add( 'INSIDE EXCEPTION OTHER ERROR ' , 1 ) ;
3059: END IF;
3060:
3061: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3062: THEN
3073: IS
3074: l_exists VARCHAR2(1);
3075: l_sql_stat VARCHAR2(250);
3076: l_freeze_flag VARCHAR2(1) := 'N';
3077: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3078: BEGIN
3079:
3080: -- oe_debug_pub.Debug_On;
3081:
3076: l_freeze_flag VARCHAR2(1) := 'N';
3077: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3078: BEGIN
3079:
3080: -- oe_debug_pub.Debug_On;
3081:
3082: IF l_debug_level > 0 THEN
3083: oe_debug_pub.add( 'ENTERING IB_ACTIVE' ) ;
3084: END IF;
3079:
3080: -- oe_debug_pub.Debug_On;
3081:
3082: IF l_debug_level > 0 THEN
3083: oe_debug_pub.add( 'ENTERING IB_ACTIVE' ) ;
3084: END IF;
3085:
3086: /* Following sql is commented for Performance problem
3087: reported in Bug-2159103 */
3090: INTO l_exists
3091: FROM all_tables
3092: where table_name='CSI_INSTALL_PARAMETERS';
3093:
3094: OE_DEBUG_PUB.ADD('l_exists= ' || l_exists ,1);
3095:
3096:
3097: IF l_exists = 'Y' THEN */
3098:
3104: EXECUTE IMMEDIATE l_sql_stat INTO l_freeze_flag;
3105:
3106:
3107: IF l_debug_level > 0 THEN
3108: oe_debug_pub.add( 'L_FREEZE_FLAG= ' || L_FREEZE_FLAG , 1 ) ;
3109: END IF;
3110:
3111: IF l_freeze_flag = 'Y' THEN
3112: return TRUE;
3117: /* Commented for Bug-2159103 */
3118: /*
3119: ELSE
3120:
3121: OE_DEBUG_PUB.ADD('CSI_UTILITY_GRP.IB_ACTIVE does not exists');
3122:
3123: END IF;
3124: */
3125:
3125:
3126: EXCEPTION
3127: WHEN NO_DATA_FOUND THEN
3128: IF l_debug_level > 0 THEN
3129: oe_debug_pub.add( 'NO DATA FOUND IN CSI_INSTALL_PARAMETERS' ) ;
3130: END IF;
3131: return FALSE;
3132:
3133: WHEN OTHERS THEN
3135: /* Added for Bug-2159103 */
3136:
3137: if sqlcode = -942 then
3138: IF l_debug_level > 0 THEN
3139: oe_debug_pub.add( SQLERRM ) ;
3140: END IF;
3141: IF l_debug_level > 0 THEN
3142: oe_debug_pub.add( 'CSI_INSTALL_PARAMETERS DOES NOT EXIST' ) ;
3143: END IF;
3138: IF l_debug_level > 0 THEN
3139: oe_debug_pub.add( SQLERRM ) ;
3140: END IF;
3141: IF l_debug_level > 0 THEN
3142: oe_debug_pub.add( 'CSI_INSTALL_PARAMETERS DOES NOT EXIST' ) ;
3143: END IF;
3144: end if;
3145:
3146: return FALSE;
3158: CURSOR option_lines IS
3159: SELECT line_id, option_number
3160: FROM oe_order_lines
3161: WHERE top_model_line_id = p_top_model_line_id;
3162: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3163:
3164: TYPE num_tbl IS TABLE OF NUMBER;
3165: l_line_ids num_tbl;
3166: l_option_numbers num_tbl;
3168: l_ref_type_code CONSTANT VARCHAR2(5) := 'ORDER';
3169: BEGIN
3170:
3171: IF l_debug_level > 0 THEN
3172: oe_debug_pub.add( 'ENTERING OE_SERVICE_UTIL.UPDATE_SERVICE_OPTION_NUMBERS' , 2 ) ;
3173: END IF;
3174:
3175: OPEN option_lines;
3176: FETCH option_lines BULK COLLECT INTO l_line_ids, l_option_numbers;
3182: WHERE service_reference_line_id = l_line_ids(i)
3183: AND service_reference_type_code = l_ref_type_code; -- For Bug 3087370
3184:
3185: IF l_debug_level > 0 THEN
3186: oe_debug_pub.add( 'LEAVING UPDATE_SERVICE_OPTION_NUMBERS' , 2 ) ;
3187: END IF;
3188:
3189: END;
3190: