[Home] [Help]
468: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
469: --
470: IF l_debug_on IS NULL
471: THEN
472: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
473: END IF;
474: --
475: IF l_debug_on THEN
476: WSH_DEBUG_SV.push(l_module_name);
472: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
473: END IF;
474: --
475: IF l_debug_on THEN
476: WSH_DEBUG_SV.push(l_module_name);
477: --
478: WSH_DEBUG_SV.log(l_module_name,'p_source_line_id ', p_source_line_id);
479: WSH_DEBUG_SV.log(l_module_name,'p_source_line_set_id ', p_source_line_set_id);
480: WSH_DEBUG_SV.log(l_module_name,'p_source_header_id ', p_source_header_id);
474: --
475: IF l_debug_on THEN
476: WSH_DEBUG_SV.push(l_module_name);
477: --
478: WSH_DEBUG_SV.log(l_module_name,'p_source_line_id ', p_source_line_id);
479: WSH_DEBUG_SV.log(l_module_name,'p_source_line_set_id ', p_source_line_set_id);
480: WSH_DEBUG_SV.log(l_module_name,'p_source_header_id ', p_source_header_id);
481: END IF;
482: --
475: IF l_debug_on THEN
476: WSH_DEBUG_SV.push(l_module_name);
477: --
478: WSH_DEBUG_SV.log(l_module_name,'p_source_line_id ', p_source_line_id);
479: WSH_DEBUG_SV.log(l_module_name,'p_source_line_set_id ', p_source_line_set_id);
480: WSH_DEBUG_SV.log(l_module_name,'p_source_header_id ', p_source_header_id);
481: END IF;
482: --
483: -- { Section to be Modified by Customers
476: WSH_DEBUG_SV.push(l_module_name);
477: --
478: WSH_DEBUG_SV.log(l_module_name,'p_source_line_id ', p_source_line_id);
479: WSH_DEBUG_SV.log(l_module_name,'p_source_line_set_id ', p_source_line_set_id);
480: WSH_DEBUG_SV.log(l_module_name,'p_source_header_id ', p_source_header_id);
481: END IF;
482: --
483: -- { Section to be Modified by Customers
484:
487: -- } End Section to be Modified by Customers
488:
489: ---
490: IF l_debug_on THEN
491: WSH_DEBUG_SV.log(l_module_name,'l_cancel_flag ', l_cancel_flag);
492: WSH_DEBUG_SV.pop(l_module_name);
493: END IF;
494:
495: RETURN (l_cancel_flag);
488:
489: ---
490: IF l_debug_on THEN
491: WSH_DEBUG_SV.log(l_module_name,'l_cancel_flag ', l_cancel_flag);
492: WSH_DEBUG_SV.pop(l_module_name);
493: END IF;
494:
495: RETURN (l_cancel_flag);
496:
525: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
526: --
527: IF l_debug_on IS NULL
528: THEN
529: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
530: END IF;
531: --
532: IF l_debug_on THEN
533: WSH_DEBUG_SV.push(l_module_name);
529: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
530: END IF;
531: --
532: IF l_debug_on THEN
533: WSH_DEBUG_SV.push(l_module_name);
534: WSH_DEBUG_SV.log(l_module_name, 'p_transaction_id', p_transaction_id);
535: END IF;
536: --
537:
530: END IF;
531: --
532: IF l_debug_on THEN
533: WSH_DEBUG_SV.push(l_module_name);
534: WSH_DEBUG_SV.log(l_module_name, 'p_transaction_id', p_transaction_id);
535: END IF;
536: --
537:
538: -- { Section to be Modified by Customers
550: x_currency_code := l_currency_code;
551:
552: ---
553: IF l_debug_on THEN
554: WSH_DEBUG_SV.log(l_module_name,'l_order_type_id ', l_order_type_id);
555: WSH_DEBUG_SV.log(l_module_name,'l_price_list_id ', l_price_list_id);
556: WSH_DEBUG_SV.log(l_module_name,'l_payment_term_id ', l_payment_term_id);
557: WSH_DEBUG_SV.log(l_module_name,'l_currency_code ', l_currency_code);
558: WSH_DEBUG_SV.pop(l_module_name);
551:
552: ---
553: IF l_debug_on THEN
554: WSH_DEBUG_SV.log(l_module_name,'l_order_type_id ', l_order_type_id);
555: WSH_DEBUG_SV.log(l_module_name,'l_price_list_id ', l_price_list_id);
556: WSH_DEBUG_SV.log(l_module_name,'l_payment_term_id ', l_payment_term_id);
557: WSH_DEBUG_SV.log(l_module_name,'l_currency_code ', l_currency_code);
558: WSH_DEBUG_SV.pop(l_module_name);
559: END IF;
552: ---
553: IF l_debug_on THEN
554: WSH_DEBUG_SV.log(l_module_name,'l_order_type_id ', l_order_type_id);
555: WSH_DEBUG_SV.log(l_module_name,'l_price_list_id ', l_price_list_id);
556: WSH_DEBUG_SV.log(l_module_name,'l_payment_term_id ', l_payment_term_id);
557: WSH_DEBUG_SV.log(l_module_name,'l_currency_code ', l_currency_code);
558: WSH_DEBUG_SV.pop(l_module_name);
559: END IF;
560: --
553: IF l_debug_on THEN
554: WSH_DEBUG_SV.log(l_module_name,'l_order_type_id ', l_order_type_id);
555: WSH_DEBUG_SV.log(l_module_name,'l_price_list_id ', l_price_list_id);
556: WSH_DEBUG_SV.log(l_module_name,'l_payment_term_id ', l_payment_term_id);
557: WSH_DEBUG_SV.log(l_module_name,'l_currency_code ', l_currency_code);
558: WSH_DEBUG_SV.pop(l_module_name);
559: END IF;
560: --
561: END Get_Standalone_WMS_Defaults;
554: WSH_DEBUG_SV.log(l_module_name,'l_order_type_id ', l_order_type_id);
555: WSH_DEBUG_SV.log(l_module_name,'l_price_list_id ', l_price_list_id);
556: WSH_DEBUG_SV.log(l_module_name,'l_payment_term_id ', l_payment_term_id);
557: WSH_DEBUG_SV.log(l_module_name,'l_currency_code ', l_currency_code);
558: WSH_DEBUG_SV.pop(l_module_name);
559: END IF;
560: --
561: END Get_Standalone_WMS_Defaults;
562:
578: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
579: --
580: IF l_debug_on IS NULL
581: THEN
582: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
583: END IF;
584: --
585: IF l_debug_on THEN
586: WSH_DEBUG_SV.push(l_module_name);
582: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
583: END IF;
584: --
585: IF l_debug_on THEN
586: WSH_DEBUG_SV.push(l_module_name);
587: WSH_DEBUG_SV.log(l_module_name, 'p_transaction_id', p_transaction_id);
588: END IF;
589: --
590:
583: END IF;
584: --
585: IF l_debug_on THEN
586: WSH_DEBUG_SV.push(l_module_name);
587: WSH_DEBUG_SV.log(l_module_name, 'p_transaction_id', p_transaction_id);
588: END IF;
589: --
590:
591: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
596: -- } End Section to be Modified by Customers
597:
598: ---
599: IF l_debug_on THEN
600: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
601: WSH_DEBUG_SV.pop(l_module_name);
602: END IF;
603: --
604: EXCEPTION
597:
598: ---
599: IF l_debug_on THEN
600: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
601: WSH_DEBUG_SV.pop(l_module_name);
602: END IF;
603: --
604: EXCEPTION
605: WHEN OTHERS THEN
606: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
607: ROLLBACK TO Post_Process_Shipment_Request;
608: --
609: IF l_debug_on THEN
610: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
611: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
612: END IF;
613: --
614: END Post_Process_Shipment_Request;
607: ROLLBACK TO Post_Process_Shipment_Request;
608: --
609: IF l_debug_on THEN
610: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
611: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
612: END IF;
613: --
614: END Post_Process_Shipment_Request;
615:
656: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
657: --
658: IF l_debug_on IS NULL
659: THEN
660: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
661: END IF;
662: --
663: IF l_debug_on THEN
664: WSH_DEBUG_SV.push(l_module_name);
660: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
661: END IF;
662: --
663: IF l_debug_on THEN
664: WSH_DEBUG_SV.push(l_module_name);
665: WSH_DEBUG_SV.log(l_module_name,'p_trip_stop_id', p_trip_stop_id);
666: WSH_DEBUG_SV.log(l_module_name,'p_doc_number', p_doc_number);
667: WSH_DEBUG_SV.log(l_module_name,'p_dsno_file_ext', p_dsno_file_ext);
668: END IF;
661: END IF;
662: --
663: IF l_debug_on THEN
664: WSH_DEBUG_SV.push(l_module_name);
665: WSH_DEBUG_SV.log(l_module_name,'p_trip_stop_id', p_trip_stop_id);
666: WSH_DEBUG_SV.log(l_module_name,'p_doc_number', p_doc_number);
667: WSH_DEBUG_SV.log(l_module_name,'p_dsno_file_ext', p_dsno_file_ext);
668: END IF;
669: --
662: --
663: IF l_debug_on THEN
664: WSH_DEBUG_SV.push(l_module_name);
665: WSH_DEBUG_SV.log(l_module_name,'p_trip_stop_id', p_trip_stop_id);
666: WSH_DEBUG_SV.log(l_module_name,'p_doc_number', p_doc_number);
667: WSH_DEBUG_SV.log(l_module_name,'p_dsno_file_ext', p_dsno_file_ext);
668: END IF;
669: --
670:
663: IF l_debug_on THEN
664: WSH_DEBUG_SV.push(l_module_name);
665: WSH_DEBUG_SV.log(l_module_name,'p_trip_stop_id', p_trip_stop_id);
666: WSH_DEBUG_SV.log(l_module_name,'p_doc_number', p_doc_number);
667: WSH_DEBUG_SV.log(l_module_name,'p_dsno_file_ext', p_dsno_file_ext);
668: END IF;
669: --
670:
671: -- { Section to be Modified by Customers
678: -- length( l_dsno_file_prefix || to_char(p_doc_number) || '.' || p_dsno_file_ext ) <= 30;
679:
680: --
681: IF l_debug_on THEN
682: WSH_DEBUG_SV.log(l_module_name,'l_dsno_file_prefix', l_dsno_file_prefix);
683: WSH_DEBUG_SV.pop(l_module_name);
684: END IF;
685: --
686:
679:
680: --
681: IF l_debug_on THEN
682: WSH_DEBUG_SV.log(l_module_name,'l_dsno_file_prefix', l_dsno_file_prefix);
683: WSH_DEBUG_SV.pop(l_module_name);
684: END IF;
685: --
686:
687: RETURN l_dsno_file_prefix;
688: EXCEPTION
689: WHEN OTHERS THEN
690: --
691: IF l_debug_on THEN
692: WSH_DEBUG_SV.log(l_module_name,'Inside Exception', sqlerrm);
693: WSH_DEBUG_SV.log(l_module_name,'l_dsno_file_prefix', l_dsno_file_prefix);
694: WSH_DEBUG_SV.pop(l_module_name);
695: END IF;
696: --
689: WHEN OTHERS THEN
690: --
691: IF l_debug_on THEN
692: WSH_DEBUG_SV.log(l_module_name,'Inside Exception', sqlerrm);
693: WSH_DEBUG_SV.log(l_module_name,'l_dsno_file_prefix', l_dsno_file_prefix);
694: WSH_DEBUG_SV.pop(l_module_name);
695: END IF;
696: --
697: RETURN l_dsno_file_prefix;
690: --
691: IF l_debug_on THEN
692: WSH_DEBUG_SV.log(l_module_name,'Inside Exception', sqlerrm);
693: WSH_DEBUG_SV.log(l_module_name,'l_dsno_file_prefix', l_dsno_file_prefix);
694: WSH_DEBUG_SV.pop(l_module_name);
695: END IF;
696: --
697: RETURN l_dsno_file_prefix;
698: END Dsno_Output_File_Prefix;
749: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
750: --
751: IF l_debug_on IS NULL
752: THEN
753: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
754: END IF;
755: --
756: IF l_debug_on THEN
757: WSH_DEBUG_SV.push(l_module_name);
753: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
754: END IF;
755: --
756: IF l_debug_on THEN
757: WSH_DEBUG_SV.push(l_module_name);
758: END IF;
759: --
760:
761: -- { Section to be Modified by Customers
783: x_grp_by_within_order := l_grp_by_within_order;
784:
785: --
786: IF l_debug_on THEN
787: WSH_DEBUG_SV.pop(l_module_name);
788: END IF;
789: --
790: EXCEPTION
791: WHEN OTHERS THEN
790: EXCEPTION
791: WHEN OTHERS THEN
792: --
793: IF l_debug_on THEN
794: WSH_DEBUG_SV.log(l_module_name,'Inside Exception', sqlerrm);
795: WSH_DEBUG_SV.pop(l_module_name);
796: END IF;
797: --
798: END Shipment_Batch_Group_Criteria;
791: WHEN OTHERS THEN
792: --
793: IF l_debug_on THEN
794: WSH_DEBUG_SV.log(l_module_name,'Inside Exception', sqlerrm);
795: WSH_DEBUG_SV.pop(l_module_name);
796: END IF;
797: --
798: END Shipment_Batch_Group_Criteria;
799:
865:
866:
867: IF l_debug_on IS NULL
868: THEN
869: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
870: END IF;
871: IF l_debug_on THEN
872: WSH_DEBUG_SV.push(l_module_name);
873: END IF;
868: THEN
869: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
870: END IF;
871: IF l_debug_on THEN
872: WSH_DEBUG_SV.push(l_module_name);
873: END IF;
874: l_carrier_api_url := 'https://onlinetools.ups.com/ups.app/xml/AV'; --UPS Prod URL
875: dbms_lob.createTemporary(clobdoc,TRUE);
876: x_msg_count := 0;
876: x_msg_count := 0;
877: x_msg_data := NULL;
878: -- Added by SHTADEPA
879: IF l_debug_on THEN
880: WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
881: WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
882: END IF;
883:
884:
877: x_msg_data := NULL;
878: -- Added by SHTADEPA
879: IF l_debug_on THEN
880: WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
881: WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
882: END IF;
883:
884:
885: l_csp_request_in := p_request_in;
943:
944:
945:
946: IF l_debug_on THEN
947: WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
948: WSH_DEBUG_SV.log(l_module_name,l_av_soap_request);
949: END IF;
950:
951: IF l_debug_on THEN
944:
945:
946: IF l_debug_on THEN
947: WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
948: WSH_DEBUG_SV.log(l_module_name,l_av_soap_request);
949: END IF;
950:
951: IF l_debug_on THEN
952: WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
948: WSH_DEBUG_SV.log(l_module_name,l_av_soap_request);
949: END IF;
950:
951: IF l_debug_on THEN
952: WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
953: END IF;
954:
955: l_internet_proxy := wsh_u_util.get_proxy
956: ( p_api_version => 1.0
960: , x_msg_data => l_msg_data);
961:
962: IF l_debug_on
963: THEN
964: WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
965: WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
966: END IF;
967:
968: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
961:
962: IF l_debug_on
963: THEN
964: WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
965: WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
966: END IF;
967:
968: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
969: THEN
999: l_http_resp := utl_http.get_response (l_http_req);
1000:
1001: IF l_debug_on
1002: THEN
1003: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
1004: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
1005: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
1006: END IF;
1007:
1000:
1001: IF l_debug_on
1002: THEN
1003: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
1004: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
1005: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
1006: END IF;
1007:
1008: BEGIN
1001: IF l_debug_on
1002: THEN
1003: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
1004: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
1005: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
1006: END IF;
1007:
1008: BEGIN
1009: LOOP
1017: END LOOP;
1018: EXCEPTION
1019: WHEN OTHERS THEN
1020: IF l_debug_on THEN
1021: WSH_DEBUG_SV.log(l_module_name,'Catch while handling response '||sqlerrm);
1022: END IF;
1023: END;
1024:
1025: IF l_debug_on THEN
1022: END IF;
1023: END;
1024:
1025: IF l_debug_on THEN
1026: WSH_DEBUG_SV.log(l_module_name,'Debug l_resp_txt is '||l_resp_txt);
1027: END IF;
1028:
1029: -- Look for client-side error and report it.
1030: IF l_http_resp.status_code >= 400 AND
1040:
1041: IF l_my_proxy
1042: THEN
1043: IF l_debug_on THEN
1044: WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
1045: WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
1046: ||l_my_scheme
1047: ||' authentication username/password for realm '
1048: ||l_my_realm
1041: IF l_my_proxy
1042: THEN
1043: IF l_debug_on THEN
1044: WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
1045: WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
1046: ||l_my_scheme
1047: ||' authentication username/password for realm '
1048: ||l_my_realm
1049: ||' for the proxy server.');
1049: ||' for the proxy server.');
1050: END IF;
1051: ELSE
1052: IF l_debug_on THEN
1053: WSH_DEBUG_SV.log(l_module_name,'Web page '
1054: ||l_carrier_api_url
1055: ||' is protected.');
1056: WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
1057: ||l_my_scheme
1052: IF l_debug_on THEN
1053: WSH_DEBUG_SV.log(l_module_name,'Web page '
1054: ||l_carrier_api_url
1055: ||' is protected.');
1056: WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
1057: ||l_my_scheme
1058: ||' authentication username/password for realm '
1059: ||l_my_realm
1060: ||' for the Web page.');
1061: END IF;
1062: END IF;
1063: ELSE
1064: IF l_debug_on THEN
1065: WSH_DEBUG_SV.log(l_module_name,'Please Check the URL.');
1066: END IF;
1067: END IF;
1068:
1069: utl_http.end_response(l_http_resp);
1072: ELSIF l_http_resp.status_code >= 500 AND
1073: l_http_resp.status_code <= 599
1074: THEN
1075: IF l_debug_on THEN
1076: WSH_DEBUG_SV.log(l_module_name,'Check if the URL is up.');
1077: END IF;
1078:
1079: utl_http.end_response(l_http_resp);
1080: RETURN;
1082:
1083: utl_http.end_response (l_http_resp);
1084:
1085: IF l_debug_on THEN
1086: WSH_DEBUG_SV.log(l_module_name,'Parsing Response l_resp_txt');
1087: END IF;
1088:
1089: l_xml := xmltype(l_resp_txt);
1090:
1097: x_cspvalidate_out(l_index).Rank := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Rank/text()').getStringVal();
1098: EXCEPTION
1099: WHEN OTHERS THEN
1100: IF l_debug_on THEN
1101: WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').Rank'||sqlerrm);
1102: END IF;
1103: END;
1104: BEGIN
1105: x_cspvalidate_out(l_index).Quality := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Quality/text()').getStringVal();
1105: x_cspvalidate_out(l_index).Quality := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Quality/text()').getStringVal();
1106: EXCEPTION
1107: WHEN OTHERS THEN
1108: IF l_debug_on THEN
1109: WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').Quality'||sqlerrm);
1110: END IF;
1111: END;
1112: BEGIN
1113: x_cspvalidate_out(l_index).City := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Address/City/text()').getStringVal();
1113: x_cspvalidate_out(l_index).City := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Address/City/text()').getStringVal();
1114: EXCEPTION
1115: WHEN OTHERS THEN
1116: IF l_debug_on THEN
1117: WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').City'||sqlerrm);
1118: END IF;
1119: END;
1120: BEGIN
1121: x_cspvalidate_out(l_index).StateProv := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Address/StateProvinceCode/text()').getStringVal();
1121: x_cspvalidate_out(l_index).StateProv := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Address/StateProvinceCode/text()').getStringVal();
1122: EXCEPTION
1123: WHEN OTHERS THEN
1124: IF l_debug_on THEN
1125: WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').StateProv'||sqlerrm);
1126: END IF;
1127: END;
1128: BEGIN
1129: x_cspvalidate_out(l_index).PostalCodeLow := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/PostalCodeLowEnd/text()').getStringVal();
1129: x_cspvalidate_out(l_index).PostalCodeLow := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/PostalCodeLowEnd/text()').getStringVal();
1130: EXCEPTION
1131: WHEN OTHERS THEN
1132: IF l_debug_on THEN
1133: WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').PostalCodeLow'||sqlerrm);
1134: END IF;
1135: END;
1136: BEGIN
1137: x_cspvalidate_out(l_index).PostalCodeHigh := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/PostalCodeHighEnd/text()').getStringVal();
1137: x_cspvalidate_out(l_index).PostalCodeHigh := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/PostalCodeHighEnd/text()').getStringVal();
1138: EXCEPTION
1139: WHEN OTHERS THEN
1140: IF l_debug_on THEN
1141: WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').PostalCodeHigh'||sqlerrm);
1142: END IF;
1143: END;
1144:
1145: l_index := l_index + 1;
1149: l_errmsg_txt := l_xml.extract('/AddressValidationResponse/Response/Error/ErrorDescription/text()').getStringVal();
1150: EXCEPTION
1151: WHEN OTHERS THEN
1152: IF l_debug_on THEN
1153: WSH_DEBUG_SV.log(l_module_name,'Exception while fetching the Address Validation error message :'||sqlerrm);
1154: END IF;
1155: l_errmsg_txt := 'Exception while fetching the Address Validation error message';
1156: END;
1157: x_return_status := FND_API.G_RET_STS_ERROR;
1180: ELSE
1181: x_msg_data := l_msg_summary;
1182: END IF;
1183: IF l_debug_on THEN
1184: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1185: WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
1186: END IF;
1187: WHEN WSH_U_CSP THEN
1188: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CSP');
1181: x_msg_data := l_msg_summary;
1182: END IF;
1183: IF l_debug_on THEN
1184: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1185: WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
1186: END IF;
1187: WHEN WSH_U_CSP THEN
1188: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CSP');
1189: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1194: ELSE
1195: x_msg_data := l_msg_summary;
1196: END IF;
1197: IF l_debug_on THEN
1198: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CSP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1199: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CSP');
1200: END IF;
1201: WHEN WSH_U_HOST_FAILED THEN
1202: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
1195: x_msg_data := l_msg_summary;
1196: END IF;
1197: IF l_debug_on THEN
1198: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CSP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1199: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CSP');
1200: END IF;
1201: WHEN WSH_U_HOST_FAILED THEN
1202: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
1203: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1212: x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
1213:
1214: --
1215: IF l_debug_on THEN
1216: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
1217: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1218: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
1219: END IF;
1220:
1213:
1214: --
1215: IF l_debug_on THEN
1216: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
1217: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1218: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
1219: END IF;
1220:
1221: WHEN WSH_U_CITYORZIP THEN
1214: --
1215: IF l_debug_on THEN
1216: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
1217: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1218: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
1219: END IF;
1220:
1221: WHEN WSH_U_CITYORZIP THEN
1222: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CITYORZIP');
1229: x_msg_data := l_msg_summary;
1230: END IF;
1231:
1232: IF l_debug_on THEN
1233: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CITYORZIP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1234: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CITYORZIP');
1235: END IF;
1236: --
1237: WHEN OTHERS THEN
1230: END IF;
1231:
1232: IF l_debug_on THEN
1233: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CITYORZIP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1234: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CITYORZIP');
1235: END IF;
1236: --
1237: WHEN OTHERS THEN
1238: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1247: ELSE
1248: x_msg_data := l_msg_summary;
1249: END IF;
1250: IF l_debug_on THEN
1251: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1252: END IF;
1253: RETURN;
1254: ELSIF sqlcode ='-29106'
1255: THEN
1263: ELSE
1264: x_msg_data := l_msg_summary;
1265: END IF;
1266: IF l_debug_on THEN
1267: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1268: END IF;
1269: RETURN;
1270: ELSIF sqlcode ='-28759'
1271: THEN
1279: ELSE
1280: x_msg_data := l_msg_summary;
1281: END IF;
1282: IF l_debug_on THEN
1283: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1284: END IF;
1285: RETURN;
1286:
1287:
1292: END IF;
1293: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
1294: --
1295: IF l_debug_on THEN
1296: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1297: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1298: END IF;
1299: END csp_shipment_services;
1300: --Set the below global variables
1293: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
1294: --
1295: IF l_debug_on THEN
1296: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1297: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1298: END IF;
1299: END csp_shipment_services;
1300: --Set the below global variables
1301: --G_UPS_WS_USERNAME
1369:
1370:
1371: IF l_debug_on IS NULL
1372: THEN
1373: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1374: END IF;
1375: IF l_debug_on THEN
1376: WSH_DEBUG_SV.push(l_module_name);
1377: END IF;
1372: THEN
1373: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1374: END IF;
1375: IF l_debug_on THEN
1376: WSH_DEBUG_SV.push(l_module_name);
1377: END IF;
1378:
1379: dbms_lob.createTemporary(clobdoc,TRUE);
1380: x_msg_count := 0;
1381: x_msg_data := NULL;
1382:
1383: -- Added by SHTADEPA
1384: IF l_debug_on THEN
1385: WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
1386: WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
1387: END IF;
1388:
1389:
1382:
1383: -- Added by SHTADEPA
1384: IF l_debug_on THEN
1385: WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
1386: WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
1387: END IF;
1388:
1389:
1390:
1450: l_rate_request_in := p_request_in;
1451:
1452: IF l_debug_on THEN
1453:
1454: WSH_DEBUG_SV.log(l_module_name,'ActionCode :'||l_rate_request_in.ActionCode);
1455: WSH_DEBUG_SV.log(l_module_name,'ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1456: WSH_DEBUG_SV.log(l_module_name,'RateChart :'||l_rate_request_in.RateChart);
1457: WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1451:
1452: IF l_debug_on THEN
1453:
1454: WSH_DEBUG_SV.log(l_module_name,'ActionCode :'||l_rate_request_in.ActionCode);
1455: WSH_DEBUG_SV.log(l_module_name,'ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1456: WSH_DEBUG_SV.log(l_module_name,'RateChart :'||l_rate_request_in.RateChart);
1457: WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1452: IF l_debug_on THEN
1453:
1454: WSH_DEBUG_SV.log(l_module_name,'ActionCode :'||l_rate_request_in.ActionCode);
1455: WSH_DEBUG_SV.log(l_module_name,'ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1456: WSH_DEBUG_SV.log(l_module_name,'RateChart :'||l_rate_request_in.RateChart);
1457: WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1453:
1454: WSH_DEBUG_SV.log(l_module_name,'ActionCode :'||l_rate_request_in.ActionCode);
1455: WSH_DEBUG_SV.log(l_module_name,'ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1456: WSH_DEBUG_SV.log(l_module_name,'RateChart :'||l_rate_request_in.RateChart);
1457: WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1454: WSH_DEBUG_SV.log(l_module_name,'ActionCode :'||l_rate_request_in.ActionCode);
1455: WSH_DEBUG_SV.log(l_module_name,'ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1456: WSH_DEBUG_SV.log(l_module_name,'RateChart :'||l_rate_request_in.RateChart);
1457: WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1455: WSH_DEBUG_SV.log(l_module_name,'ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1456: WSH_DEBUG_SV.log(l_module_name,'RateChart :'||l_rate_request_in.RateChart);
1457: WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1456: WSH_DEBUG_SV.log(l_module_name,'RateChart :'||l_rate_request_in.RateChart);
1457: WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1457: WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1458: WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1459: WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1460: WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1461: WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1462: WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1463: WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471: WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1464: WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471: WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1472: WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1465: WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471: WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1472: WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1473: WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1466: WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471: WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1472: WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1473: WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1474: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1467: WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471: WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1472: WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1473: WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1474: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1475:
1468: WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471: WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1472: WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1473: WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1474: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1475:
1476: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd2 :'||l_rate_request_in.SNDestinationInd2);
1469: WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471: WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1472: WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1473: WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1474: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1475:
1476: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd2 :'||l_rate_request_in.SNDestinationInd2);
1477: WSH_DEBUG_SV.log(l_module_name,'ResidentialInd :'||l_rate_request_in.ResidentialInd);
1470: WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471: WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1472: WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1473: WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1474: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1475:
1476: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd2 :'||l_rate_request_in.SNDestinationInd2);
1477: WSH_DEBUG_SV.log(l_module_name,'ResidentialInd :'||l_rate_request_in.ResidentialInd);
1478: WSH_DEBUG_SV.log(l_module_name,'PackagingType :'||l_rate_request_in.PackagingType);
1472: WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1473: WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1474: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1475:
1476: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd2 :'||l_rate_request_in.SNDestinationInd2);
1477: WSH_DEBUG_SV.log(l_module_name,'ResidentialInd :'||l_rate_request_in.ResidentialInd);
1478: WSH_DEBUG_SV.log(l_module_name,'PackagingType :'||l_rate_request_in.PackagingType);
1479: END IF;
1480:
1473: WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1474: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1475:
1476: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd2 :'||l_rate_request_in.SNDestinationInd2);
1477: WSH_DEBUG_SV.log(l_module_name,'ResidentialInd :'||l_rate_request_in.ResidentialInd);
1478: WSH_DEBUG_SV.log(l_module_name,'PackagingType :'||l_rate_request_in.PackagingType);
1479: END IF;
1480:
1481: IF l_rate_request_in.ServiceLevelCode IS NULL THEN
1474: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1475:
1476: WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd2 :'||l_rate_request_in.SNDestinationInd2);
1477: WSH_DEBUG_SV.log(l_module_name,'ResidentialInd :'||l_rate_request_in.ResidentialInd);
1478: WSH_DEBUG_SV.log(l_module_name,'PackagingType :'||l_rate_request_in.PackagingType);
1479: END IF;
1480:
1481: IF l_rate_request_in.ServiceLevelCode IS NULL THEN
1482: RAISE wsh_u_srvlevcode;
1519: END IF;
1520: END IF;
1521:
1522: IF l_debug_on THEN
1523: WSH_DEBUG_SV.log(l_module_name,'Derived ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1524: END IF;
1525: IF l_rate_request_in.PackageActualWeight IS NULL
1526: THEN
1527: RAISE wsh_u_pkgactwt;
1548: ELSE
1549: l_rate_request_in.RateChart := '01';
1550: END IF;
1551: IF l_debug_on THEN
1552: WSH_DEBUG_SV.log(l_module_name,'Derived RateChart :'||l_rate_request_in.RateChart);
1553: END IF;
1554: IF l_rate_request_in.ShipperPostalCode IS NULL
1555: THEN
1556: RAISE wsh_u_spostalcode;
1675: WHERE w1.source_location_id = :b13
1676: AND w2.source_location_id = :b14 ';
1677:
1678:
1679: WSH_DEBUG_SV.log(l_module_name,'dynamic rate query :'||l_rate_query);
1680: WSH_DEBUG_SV.log(l_module_name,'Rate ship_from_location_id'||p_request_in.ship_from_location_id);
1681: WSH_DEBUG_SV.log(l_module_name,'Rate ship_to_location_id'||p_request_in.ship_to_location_id);
1682:
1683: OPEN rate_xml_cur
1676: AND w2.source_location_id = :b14 ';
1677:
1678:
1679: WSH_DEBUG_SV.log(l_module_name,'dynamic rate query :'||l_rate_query);
1680: WSH_DEBUG_SV.log(l_module_name,'Rate ship_from_location_id'||p_request_in.ship_from_location_id);
1681: WSH_DEBUG_SV.log(l_module_name,'Rate ship_to_location_id'||p_request_in.ship_to_location_id);
1682:
1683: OPEN rate_xml_cur
1684: FOR l_rate_query
1677:
1678:
1679: WSH_DEBUG_SV.log(l_module_name,'dynamic rate query :'||l_rate_query);
1680: WSH_DEBUG_SV.log(l_module_name,'Rate ship_from_location_id'||p_request_in.ship_from_location_id);
1681: WSH_DEBUG_SV.log(l_module_name,'Rate ship_to_location_id'||p_request_in.ship_to_location_id);
1682:
1683: OPEN rate_xml_cur
1684: FOR l_rate_query
1685: USING g_ups_ws_username
1695: , l_rate_request_in.SatDeliveryInd
1696: , p_request_in.ship_from_location_id
1697: , p_request_in.ship_to_location_id;
1698:
1699: WSH_DEBUG_SV.log(l_module_name,'cursor opened');
1700: FETCH rate_xml_cur INTO xdata;
1701: WSH_DEBUG_SV.log(l_module_name,'fetched from rate cursor');
1702: IF rate_xml_cur%NOTFOUND
1703: THEN
1697: , p_request_in.ship_to_location_id;
1698:
1699: WSH_DEBUG_SV.log(l_module_name,'cursor opened');
1700: FETCH rate_xml_cur INTO xdata;
1701: WSH_DEBUG_SV.log(l_module_name,'fetched from rate cursor');
1702: IF rate_xml_cur%NOTFOUND
1703: THEN
1704: IF l_debug_on THEN
1705: WSH_DEBUG_SV.log(l_module_name,'No Rows Fetched for the ship_from_location_id/ship_to_location_id');
1701: WSH_DEBUG_SV.log(l_module_name,'fetched from rate cursor');
1702: IF rate_xml_cur%NOTFOUND
1703: THEN
1704: IF l_debug_on THEN
1705: WSH_DEBUG_SV.log(l_module_name,'No Rows Fetched for the ship_from_location_id/ship_to_location_id');
1706: END IF;
1707: RETURN;
1708: END IF;
1709: CLOSE rate_xml_cur;
1715:
1716: dbms_xmldom.writeToClob(doc,clobdoc) ;
1717:
1718: IF l_debug_on THEN
1719: WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
1720: WSH_DEBUG_SV.log(l_module_name,clobdoc);
1721: END IF;
1722: l_carrier_api_url := 'https://onlinetools.ups.com/webservices/Rate'; --UPS Prod URL
1723:
1716: dbms_xmldom.writeToClob(doc,clobdoc) ;
1717:
1718: IF l_debug_on THEN
1719: WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
1720: WSH_DEBUG_SV.log(l_module_name,clobdoc);
1721: END IF;
1722: l_carrier_api_url := 'https://onlinetools.ups.com/webservices/Rate'; --UPS Prod URL
1723:
1724: IF l_debug_on THEN
1721: END IF;
1722: l_carrier_api_url := 'https://onlinetools.ups.com/webservices/Rate'; --UPS Prod URL
1723:
1724: IF l_debug_on THEN
1725: WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
1726: END IF;
1727:
1728: l_internet_proxy := wsh_u_util.get_proxy
1729: ( p_api_version => 1.0
1733: , x_msg_data => l_msg_data);
1734:
1735: IF l_debug_on
1736: THEN
1737: WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
1738: WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
1739: END IF;
1740:
1741: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1734:
1735: IF l_debug_on
1736: THEN
1737: WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
1738: WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
1739: END IF;
1740:
1741: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1742: THEN
1775: l_http_resp := utl_http.get_response (l_http_req);
1776:
1777: IF l_debug_on
1778: THEN
1779: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
1780: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
1781: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
1782: END IF;
1783:
1776:
1777: IF l_debug_on
1778: THEN
1779: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
1780: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
1781: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
1782: END IF;
1783:
1784: BEGIN
1777: IF l_debug_on
1778: THEN
1779: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
1780: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
1781: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
1782: END IF;
1783:
1784: BEGIN
1785: LOOP
1793: END LOOP;
1794: EXCEPTION
1795: WHEN OTHERS THEN
1796: IF l_debug_on THEN
1797: WSH_DEBUG_SV.log(l_module_name,'Catch while handling response '||sqlerrm);
1798: END IF;
1799: END;
1800:
1801: IF l_debug_on THEN
1798: END IF;
1799: END;
1800:
1801: IF l_debug_on THEN
1802: WSH_DEBUG_SV.log(l_module_name,'Debug l_resp_txt is '||l_resp_txt);
1803: END IF;
1804:
1805: -- Look for client-side error and report it.
1806: IF l_http_resp.status_code >= 400 AND
1816:
1817: IF l_my_proxy
1818: THEN
1819: IF l_debug_on THEN
1820: WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
1821: WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
1822: ||l_my_scheme
1823: ||' authentication username/password for realm '
1824: ||l_my_realm
1817: IF l_my_proxy
1818: THEN
1819: IF l_debug_on THEN
1820: WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
1821: WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
1822: ||l_my_scheme
1823: ||' authentication username/password for realm '
1824: ||l_my_realm
1825: ||' for the proxy server.');
1825: ||' for the proxy server.');
1826: END IF;
1827: ELSE
1828: IF l_debug_on THEN
1829: WSH_DEBUG_SV.log(l_module_name,'Web page '
1830: ||l_carrier_api_url
1831: ||' is protected.');
1832: WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
1833: ||l_my_scheme
1828: IF l_debug_on THEN
1829: WSH_DEBUG_SV.log(l_module_name,'Web page '
1830: ||l_carrier_api_url
1831: ||' is protected.');
1832: WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
1833: ||l_my_scheme
1834: ||' authentication username/password for realm '
1835: ||l_my_realm
1836: ||' for the Web page.');
1837: END IF;
1838: END IF;
1839: ELSE
1840: IF l_debug_on THEN
1841: WSH_DEBUG_SV.log(l_module_name,'Please Check the URL.');
1842: END IF;
1843: END IF;
1844:
1845: utl_http.end_response(l_http_resp);
1848: ELSIF l_http_resp.status_code >= 500 AND
1849: l_http_resp.status_code <= 599
1850: THEN
1851: IF l_debug_on THEN
1852: WSH_DEBUG_SV.log(l_module_name,'Check if the URL is up.');
1853: END IF;
1854:
1855: utl_http.end_response(l_http_resp);
1856: RETURN;
1858:
1859: utl_http.end_response (l_http_resp);
1860:
1861: IF l_debug_on THEN
1862: WSH_DEBUG_SV.log(l_module_name,'Parsing Response l_resp_txt');
1863: END IF;
1864:
1865: l_xml := xmltype(l_resp_txt);
1866:
1868: INTO v_decimal
1869: FROM NLS_SESSION_PARAMETERS
1870: WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
1871:
1872: WSH_DEBUG_SV.log(l_module_name,'v_decimal NLS_NUMERIC_CHARACTERS:'||v_decimal);
1873:
1874: EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
1875:
1876:
1884: x_output(l_index).PackageActualWeight := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:RatedPackage/rate:BillingWeight/rate:Weight/text()', l_namespace).getStringVal();
1885: EXCEPTION
1886: WHEN OTHERS THEN
1887: IF l_debug_on THEN
1888: WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').PackageActualWeight :'||sqlerrm);
1889: END IF;
1890: END;
1891: BEGIN
1892: x_output(l_index).ProductCharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:TransportationCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1892: x_output(l_index).ProductCharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:TransportationCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1893: EXCEPTION
1894: WHEN OTHERS THEN
1895: IF l_debug_on THEN
1896: WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').ProductCharge :'||sqlerrm);
1897: END IF;
1898: END;
1899: BEGIN
1900: x_output(l_index).AccessorySurcharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:ServiceOptionsCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1900: x_output(l_index).AccessorySurcharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:ServiceOptionsCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1901: EXCEPTION
1902: WHEN OTHERS THEN
1903: IF l_debug_on THEN
1904: WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').AccessorySurcharge :'||sqlerrm);
1905: END IF;
1906: END;
1907: BEGIN
1908: x_output(l_index).TotalCharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:TotalCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1908: x_output(l_index).TotalCharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:TotalCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1909: EXCEPTION
1910: WHEN OTHERS THEN
1911: IF l_debug_on THEN
1912: WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').TotalCharge :'||sqlerrm);
1913: END IF;
1914: END;
1915: BEGIN
1916: l_commit_time := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:GuaranteedDelivery/rate:BusinessDaysInTransit/text()', l_namespace).getStringVal();
1919: WHEN NO_DATA_FOUND THEN
1920: x_output(l_index).CommitTime := '-1';
1921: WHEN OTHERS THEN
1922: IF l_debug_on THEN
1923: WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').CommitTime :'||sqlerrm);
1924: END IF;
1925: END;
1926: l_index := l_index + 1;
1927: END LOOP;
1931: l_errmsg_txt := l_xml.extract('//err:Errors/err:ErrorDetail/err:PrimaryErrorCode/err:Description/text()', l_namespace).getStringVal();
1932: EXCEPTION
1933: WHEN OTHERS THEN
1934: IF l_debug_on THEN
1935: WSH_DEBUG_SV.log(l_module_name,'Exception while fetching the Rate and Services error message :'||sqlerrm);
1936: END IF;
1937: l_errmsg_txt := 'Exception while fetching the Rate and Services error message';
1938: END;
1939: x_return_status := FND_API.G_RET_STS_ERROR;
1968: ELSE
1969: x_msg_data := l_msg_summary;
1970: END IF;
1971: IF l_debug_on THEN
1972: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1973: WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
1974: END IF;
1975: WHEN WSH_U_SRVLEVCODE THEN
1976: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SRVLEVCODE');
1969: x_msg_data := l_msg_summary;
1970: END IF;
1971: IF l_debug_on THEN
1972: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1973: WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
1974: END IF;
1975: WHEN WSH_U_SRVLEVCODE THEN
1976: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SRVLEVCODE');
1977: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1983: x_msg_data := l_msg_summary;
1984: END IF;
1985: --
1986: IF l_debug_on THEN
1987: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
1988: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SRVLEVCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1989: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SRVLEVCODE');
1990: END IF;
1991: WHEN WSH_U_PKGACTWT THEN
1984: END IF;
1985: --
1986: IF l_debug_on THEN
1987: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
1988: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SRVLEVCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1989: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SRVLEVCODE');
1990: END IF;
1991: WHEN WSH_U_PKGACTWT THEN
1992: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PKGACTWT');
1985: --
1986: IF l_debug_on THEN
1987: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
1988: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SRVLEVCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1989: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SRVLEVCODE');
1990: END IF;
1991: WHEN WSH_U_PKGACTWT THEN
1992: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PKGACTWT');
1993: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1999: x_msg_data := l_msg_summary;
2000: END IF;
2001: --
2002: IF l_debug_on THEN
2003: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2004: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_PKGACTWT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2005: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_PKGACTWT');
2006: END IF;
2007: WHEN WSH_U_SPOSTALCODE THEN
2000: END IF;
2001: --
2002: IF l_debug_on THEN
2003: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2004: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_PKGACTWT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2005: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_PKGACTWT');
2006: END IF;
2007: WHEN WSH_U_SPOSTALCODE THEN
2008: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SPOSTALCODE');
2001: --
2002: IF l_debug_on THEN
2003: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2004: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_PKGACTWT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2005: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_PKGACTWT');
2006: END IF;
2007: WHEN WSH_U_SPOSTALCODE THEN
2008: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SPOSTALCODE');
2009: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2015: x_msg_data := l_msg_summary;
2016: END IF;
2017: --
2018: IF l_debug_on THEN
2019: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2020: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2021: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SPOSTALCODE');
2022: END IF;
2023: --
2016: END IF;
2017: --
2018: IF l_debug_on THEN
2019: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2020: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2021: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SPOSTALCODE');
2022: END IF;
2023: --
2024: WHEN WSH_U_CPOSTALCODE THEN
2017: --
2018: IF l_debug_on THEN
2019: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2020: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2021: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SPOSTALCODE');
2022: END IF;
2023: --
2024: WHEN WSH_U_CPOSTALCODE THEN
2025: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CPOSTALCODE');
2032: x_msg_data := l_msg_summary;
2033: END IF;
2034: --
2035: IF l_debug_on THEN
2036: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2037: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2038: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CPOSTALCODE');
2039: END IF;
2040: --
2033: END IF;
2034: --
2035: IF l_debug_on THEN
2036: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2037: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2038: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CPOSTALCODE');
2039: END IF;
2040: --
2041: WHEN WSH_U_CCOUNTRY THEN
2034: --
2035: IF l_debug_on THEN
2036: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2037: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2038: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CPOSTALCODE');
2039: END IF;
2040: --
2041: WHEN WSH_U_CCOUNTRY THEN
2042: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CCOUNTRY');
2049: x_msg_data := l_msg_summary;
2050: END IF;
2051: --
2052: IF l_debug_on THEN
2053: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2054: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CCOUNTRY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2055: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CCOUNTRY');
2056: END IF;
2057:
2050: END IF;
2051: --
2052: IF l_debug_on THEN
2053: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2054: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CCOUNTRY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2055: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CCOUNTRY');
2056: END IF;
2057:
2058: WHEN WSH_U_RATECHART THEN
2051: --
2052: IF l_debug_on THEN
2053: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2054: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CCOUNTRY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2055: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CCOUNTRY');
2056: END IF;
2057:
2058: WHEN WSH_U_RATECHART THEN
2059: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_RATECHART');
2066: x_msg_data := l_msg_summary;
2067: end if;
2068: --
2069: IF l_debug_on THEN
2070: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2071: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_RATECHART exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2072: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_RATECHART');
2073: END IF;
2074: WHEN WSH_U_HOST_FAILED THEN
2067: end if;
2068: --
2069: IF l_debug_on THEN
2070: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2071: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_RATECHART exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2072: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_RATECHART');
2073: END IF;
2074: WHEN WSH_U_HOST_FAILED THEN
2075: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
2068: --
2069: IF l_debug_on THEN
2070: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2071: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_RATECHART exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2072: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_RATECHART');
2073: END IF;
2074: WHEN WSH_U_HOST_FAILED THEN
2075: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
2076: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2085: x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
2086:
2087: --
2088: IF l_debug_on THEN
2089: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2090: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2091: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
2092: END IF;
2093:
2086:
2087: --
2088: IF l_debug_on THEN
2089: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2090: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2091: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
2092: END IF;
2093:
2094: WHEN OTHERS THEN
2087: --
2088: IF l_debug_on THEN
2089: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2090: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2091: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
2092: END IF;
2093:
2094: WHEN OTHERS THEN
2095: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2104: ELSE
2105: x_msg_data := l_msg_summary;
2106: END IF;
2107: IF l_debug_on THEN
2108: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2109: END IF;
2110: RETURN;
2111: ELSIF sqlcode ='-29106'
2112: THEN
2120: ELSE
2121: x_msg_data := l_msg_summary;
2122: END IF;
2123: IF l_debug_on THEN
2124: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2125: END IF;
2126: RETURN;
2127: ELSIF sqlcode ='-28759'
2128: THEN
2136: ELSE
2137: x_msg_data := l_msg_summary;
2138: END IF;
2139: IF l_debug_on THEN
2140: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2141: END IF;
2142: RETURN;
2143:
2144:
2149: END IF;
2150: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2151: --
2152: IF l_debug_on THEN
2153: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2154: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2155: END IF;
2156: END rate_shipment_services;
2157: --Set the below global variables
2150: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2151: --
2152: IF l_debug_on THEN
2153: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2154: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2155: END IF;
2156: END rate_shipment_services;
2157: --Set the below global variables
2158: --G_UPS_WS_USERNAME
2238: --If Customer want to use UPS WS Integ, they need to uncomment the below code
2239: /*
2240: IF l_debug_on IS NULL
2241: THEN
2242: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2243: END IF;
2244: IF l_debug_on
2245: THEN
2246: WSH_DEBUG_SV.push(l_module_name);
2242: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2243: END IF;
2244: IF l_debug_on
2245: THEN
2246: WSH_DEBUG_SV.push(l_module_name);
2247: END IF;
2248: x_msg_count := 0;
2249: x_msg_data := NULL;
2250:
2248: x_msg_count := 0;
2249: x_msg_data := NULL;
2250:
2251: IF l_debug_on THEN
2252: WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
2253: WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
2254: END IF;
2255:
2256: dbms_lob.createTemporary(clobdoc,TRUE);
2249: x_msg_data := NULL;
2250:
2251: IF l_debug_on THEN
2252: WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
2253: WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
2254: END IF;
2255:
2256: dbms_lob.createTemporary(clobdoc,TRUE);
2257:
2259: FETCH trk_xml_cur INTO xdata;
2260: IF trk_xml_cur%NOTFOUND
2261: THEN
2262: IF l_debug_on THEN
2263: WSH_DEBUG_SV.log(l_module_name,'No Rows Fetched for the given Delivery Detail Id');
2264: END IF;
2265: RAISE wsh_u_no_trk;
2266: --x_return_status := FND_API.G_RET_STS_ERROR;
2267: --RETURN;
2274:
2275: dbms_xmldom.writeToClob(doc,clobdoc) ;
2276:
2277: IF l_debug_on THEN
2278: WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
2279: WSH_DEBUG_SV.log(l_module_name,clobdoc);
2280: END IF;
2281:
2282: l_carrier_api_url := 'https://onlinetools.ups.com/webservices/Track'; --UPS Prod URL
2275: dbms_xmldom.writeToClob(doc,clobdoc) ;
2276:
2277: IF l_debug_on THEN
2278: WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
2279: WSH_DEBUG_SV.log(l_module_name,clobdoc);
2280: END IF;
2281:
2282: l_carrier_api_url := 'https://onlinetools.ups.com/webservices/Track'; --UPS Prod URL
2283:
2281:
2282: l_carrier_api_url := 'https://onlinetools.ups.com/webservices/Track'; --UPS Prod URL
2283:
2284: IF l_debug_on THEN
2285: WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
2286: END IF;
2287:
2288: l_internet_proxy := wsh_u_util.get_proxy
2289: ( p_api_version => 1.0
2293: , x_msg_data => l_msg_data);
2294:
2295: IF l_debug_on
2296: THEN
2297: WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
2298: WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
2299: END IF;
2300:
2301: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2294:
2295: IF l_debug_on
2296: THEN
2297: WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
2298: WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
2299: END IF;
2300:
2301: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2302: THEN
2334: l_http_resp := utl_http.get_response (l_http_req);
2335:
2336: IF l_debug_on
2337: THEN
2338: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
2339: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
2340: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
2341: END IF;
2342:
2335:
2336: IF l_debug_on
2337: THEN
2338: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
2339: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
2340: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
2341: END IF;
2342:
2343: BEGIN
2336: IF l_debug_on
2337: THEN
2338: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
2339: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
2340: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
2341: END IF;
2342:
2343: BEGIN
2344: LOOP
2352: END LOOP;
2353: EXCEPTION
2354: WHEN OTHERS THEN
2355: IF l_debug_on THEN
2356: WSH_DEBUG_SV.log(l_module_name,'Catch while handling response '||sqlerrm);
2357: END IF;
2358: END;
2359:
2360: IF l_debug_on THEN
2357: END IF;
2358: END;
2359:
2360: IF l_debug_on THEN
2361: WSH_DEBUG_SV.log(l_module_name,'Debug l_resp_txt is '||l_resp_txt);
2362: END IF;
2363:
2364: -- Look for client-side error and report it.
2365: IF l_http_resp.status_code >= 400 AND
2375:
2376: IF l_my_proxy
2377: THEN
2378: IF l_debug_on THEN
2379: WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
2380: WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
2381: ||l_my_scheme
2382: ||' authentication username/password for realm '
2383: ||l_my_realm
2376: IF l_my_proxy
2377: THEN
2378: IF l_debug_on THEN
2379: WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
2380: WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
2381: ||l_my_scheme
2382: ||' authentication username/password for realm '
2383: ||l_my_realm
2384: ||' for the proxy server.');
2384: ||' for the proxy server.');
2385: END IF;
2386: ELSE
2387: IF l_debug_on THEN
2388: WSH_DEBUG_SV.log(l_module_name,'Web page '
2389: ||l_carrier_api_url
2390: ||' is protected.');
2391: WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
2392: ||l_my_scheme
2387: IF l_debug_on THEN
2388: WSH_DEBUG_SV.log(l_module_name,'Web page '
2389: ||l_carrier_api_url
2390: ||' is protected.');
2391: WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
2392: ||l_my_scheme
2393: ||' authentication username/password for realm '
2394: ||l_my_realm
2395: ||' for the Web page.');
2396: END IF;
2397: END IF;
2398: ELSE
2399: IF l_debug_on THEN
2400: WSH_DEBUG_SV.log(l_module_name,'Please Check the URL.');
2401: END IF;
2402: END IF;
2403:
2404: utl_http.end_response(l_http_resp);
2407: ELSIF l_http_resp.status_code >= 500 AND
2408: l_http_resp.status_code <= 599
2409: THEN
2410: IF l_debug_on THEN
2411: WSH_DEBUG_SV.log(l_module_name,'Check if the URL is up.');
2412: END IF;
2413:
2414: utl_http.end_response(l_http_resp);
2415: RETURN;
2417:
2418: utl_http.end_response (l_http_resp);
2419:
2420: IF l_debug_on THEN
2421: WSH_DEBUG_SV.log(l_module_name,'Parsing Response l_resp_txt');
2422: END IF;
2423:
2424: l_xml := xmltype(l_resp_txt);
2425:
2426: EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
2427:
2428: l_namespace := 'xmlns:trk="http://www.ups.com/XMLSchema/XOLTWS/Track/v2.0"';
2429: l_index := 1;
2430: WSH_DEBUG_SV.log(l_module_name,'AD 1');
2431: IF l_xml.Existsnode('//trk:TrackResponse/'||'trk:Shipment[' || To_Char(l_index) || ']',l_namespace) > 0
2432: THEN
2433: WHILE l_xml.Existsnode('//trk:TrackResponse/'||'trk:Shipment[' || To_Char(l_index) || ']',l_namespace) > 0
2434: LOOP
2436: x_pkg_detail_segment(l_index).ServiceLevelDescription := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Service/trk:Description/text()',l_namespace).getStringVal();
2437: EXCEPTION
2438: WHEN OTHERS THEN
2439: IF l_debug_on THEN
2440: WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').ServiceLevelDescription :'||sqlerrm);
2441: END IF;
2442: END;
2443: x_pkg_detail_segment(l_index).ConsigneeAddressIndex := l_index;
2444: BEGIN
2445: x_pkg_detail_segment(l_index).SignedForByName := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:SignedForByName/text()',l_namespace).getStringVal();
2446: EXCEPTION
2447: WHEN OTHERS THEN
2448: IF l_debug_on THEN
2449: WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').SignedForByName :'||sqlerrm);
2450: END IF;
2451: END;
2452: BEGIN
2453: x_pkg_detail_segment(l_index).PickupDate := to_date(l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:PickupDate/text()',l_namespace).getStringVal(),'YYYYMMDD');
2453: x_pkg_detail_segment(l_index).PickupDate := to_date(l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:PickupDate/text()',l_namespace).getStringVal(),'YYYYMMDD');
2454: EXCEPTION
2455: WHEN OTHERS THEN
2456: IF l_debug_on THEN
2457: WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').PickupDate :'||sqlerrm);
2458: END IF;
2459: END;
2460: BEGIN
2461: x_pkg_detail_segment(l_index).CusotmerReferenceNumber := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:ReferenceNumber/trk:Value/text()',l_namespace).getStringVal();
2461: x_pkg_detail_segment(l_index).CusotmerReferenceNumber := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:ReferenceNumber/trk:Value/text()',l_namespace).getStringVal();
2462: EXCEPTION
2463: WHEN OTHERS THEN
2464: IF l_debug_on THEN
2465: WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').CusotmerReferenceNumber :'||sqlerrm);
2466: END IF;
2467: END;
2468: BEGIN
2469: x_pkg_detail_segment(l_index).PackageWeight := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:PackageWeight/trk:Weight/text()',l_namespace).getStringVal();
2469: x_pkg_detail_segment(l_index).PackageWeight := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:PackageWeight/trk:Weight/text()',l_namespace).getStringVal();
2470: EXCEPTION
2471: WHEN OTHERS THEN
2472: IF l_debug_on THEN
2473: WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').PackageWeight :'||sqlerrm);
2474: END IF;
2475: END;
2476: BEGIN
2477: x_pkg_detail_segment(l_index).weightuom := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:PackageWeight/trk:UnitOfMeasurement/trk:Code/text()',l_namespace).getStringVal();
2477: x_pkg_detail_segment(l_index).weightuom := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:PackageWeight/trk:UnitOfMeasurement/trk:Code/text()',l_namespace).getStringVal();
2478: EXCEPTION
2479: WHEN OTHERS THEN
2480: IF l_debug_on THEN
2481: WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').weightuom :'||sqlerrm);
2482: END IF;
2483: END;
2484: BEGIN
2485: x_pkg_detail_segment(l_index).location := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity/trk:ActivityLocation/trk:Description/text()',l_namespace).getStringVal();
2485: x_pkg_detail_segment(l_index).location := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity/trk:ActivityLocation/trk:Description/text()',l_namespace).getStringVal();
2486: EXCEPTION
2487: WHEN OTHERS THEN
2488: IF l_debug_on THEN
2489: WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').location :'||sqlerrm);
2490: END IF;
2491: END;
2492: l_index := l_index +1;
2493: END LOOP;
2523: l_errmsg_txt := l_xml.extract('//err:Errors/err:ErrorDetail/err:PrimaryErrorCode/err:Description/text()', l_namespace).getStringVal();
2524: EXCEPTION
2525: WHEN OTHERS THEN
2526: IF l_debug_on THEN
2527: WSH_DEBUG_SV.log(l_module_name,'Exception while fetching the tracking error message :'||sqlerrm);
2528: END IF;
2529: l_errmsg_txt := 'Exception while fetching the tracking error message';
2530: END;
2531:
2535: WSH_UTIL_CORE.Add_Message(FND_API.G_RET_STS_ERROR);
2536:
2537: END IF;
2538: IF l_debug_on THEN
2539: WSH_DEBUG_SV.log(l_module_name,'Count x_activity_detail:'||x_activity_detail.count);
2540: WSH_DEBUG_SV.log(l_module_name,'Count x_pkg_detail_segment:'||x_pkg_detail_segment.count);
2541: WSH_DEBUG_SV.log(l_module_name,'Count x_track_address:'||x_track_address.count);
2542: END IF;
2543:
2536:
2537: END IF;
2538: IF l_debug_on THEN
2539: WSH_DEBUG_SV.log(l_module_name,'Count x_activity_detail:'||x_activity_detail.count);
2540: WSH_DEBUG_SV.log(l_module_name,'Count x_pkg_detail_segment:'||x_pkg_detail_segment.count);
2541: WSH_DEBUG_SV.log(l_module_name,'Count x_track_address:'||x_track_address.count);
2542: END IF;
2543:
2544: IF x_return_status = FND_API.G_RET_STS_ERROR
2537: END IF;
2538: IF l_debug_on THEN
2539: WSH_DEBUG_SV.log(l_module_name,'Count x_activity_detail:'||x_activity_detail.count);
2540: WSH_DEBUG_SV.log(l_module_name,'Count x_pkg_detail_segment:'||x_pkg_detail_segment.count);
2541: WSH_DEBUG_SV.log(l_module_name,'Count x_track_address:'||x_track_address.count);
2542: END IF;
2543:
2544: IF x_return_status = FND_API.G_RET_STS_ERROR
2545: THEN
2558: ELSE
2559: x_msg_data := l_msg_summary;
2560: END IF;
2561: IF l_debug_on THEN
2562: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2563: WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
2564: END IF;
2565: WHEN WSH_U_HOST_FAILED THEN
2566: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
2559: x_msg_data := l_msg_summary;
2560: END IF;
2561: IF l_debug_on THEN
2562: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2563: WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
2564: END IF;
2565: WHEN WSH_U_HOST_FAILED THEN
2566: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
2567: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2576: x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
2577:
2578: --
2579: IF l_debug_on THEN
2580: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2581: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2582: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
2583: END IF;
2584: WHEN WSH_U_NO_TRK THEN
2577:
2578: --
2579: IF l_debug_on THEN
2580: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2581: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2582: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
2583: END IF;
2584: WHEN WSH_U_NO_TRK THEN
2585: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_NO_TRK');
2578: --
2579: IF l_debug_on THEN
2580: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2581: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2582: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
2583: END IF;
2584: WHEN WSH_U_NO_TRK THEN
2585: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_NO_TRK');
2586: FND_MESSAGE.SET_TOKEN('MSG_TEXT','Invalid Tracking Number');
2596: x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
2597:
2598: --
2599: IF l_debug_on THEN
2600: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2601: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_NO_TRK exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2602: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_NO_TRK');
2603: END IF; --
2604: WHEN OTHERS THEN
2597:
2598: --
2599: IF l_debug_on THEN
2600: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2601: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_NO_TRK exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2602: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_NO_TRK');
2603: END IF; --
2604: WHEN OTHERS THEN
2605: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2598: --
2599: IF l_debug_on THEN
2600: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2601: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_NO_TRK exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2602: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_NO_TRK');
2603: END IF; --
2604: WHEN OTHERS THEN
2605: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2606: IF sqlcode ='-12543' THEN
2614: ELSE
2615: x_msg_data := l_msg_summary;
2616: END IF;
2617: IF l_debug_on THEN
2618: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2619: END IF;
2620: RETURN;
2621: ELSIF sqlcode ='-29106'
2622: THEN
2630: ELSE
2631: x_msg_data := l_msg_summary;
2632: END IF;
2633: IF l_debug_on THEN
2634: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2635: END IF;
2636: RETURN;
2637: ELSIF sqlcode ='-28759'
2638: THEN
2646: ELSE
2647: x_msg_data := l_msg_summary;
2648: END IF;
2649: IF l_debug_on THEN
2650: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2651: END IF;
2652: RETURN;
2653:
2654:
2664: END IF;
2665: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2666: --
2667: IF l_debug_on THEN
2668: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2669: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS xmsg count'||x_msg_count||' x msg data:'||x_msg_data);
2670: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2671: END IF;
2672: */
2665: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2666: --
2667: IF l_debug_on THEN
2668: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2669: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS xmsg count'||x_msg_count||' x msg data:'||x_msg_data);
2670: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2671: END IF;
2672: */
2673: -- End of Comment the UPS WS Integeration code
2666: --
2667: IF l_debug_on THEN
2668: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2669: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS xmsg count'||x_msg_count||' x msg data:'||x_msg_data);
2670: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2671: END IF;
2672: */
2673: -- End of Comment the UPS WS Integeration code
2674:
2769: --If Customer want to use UPS WS Integ, they need to uncomment the below code
2770: /*
2771: IF l_debug_on IS NULL
2772: THEN
2773: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2774: END IF;
2775: IF l_debug_on THEN
2776: WSH_DEBUG_SV.push(l_module_name);
2777: END IF;
2772: THEN
2773: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2774: END IF;
2775: IF l_debug_on THEN
2776: WSH_DEBUG_SV.push(l_module_name);
2777: END IF;
2778: x_msg_count := 0;
2779: x_msg_data := NULL;
2780: -- Added by SHTADEPA
2778: x_msg_count := 0;
2779: x_msg_data := NULL;
2780: -- Added by SHTADEPA
2781: IF l_debug_on THEN
2782: WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
2783: WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
2784: END IF;
2785:
2786: dbms_lob.createTemporary(clobdoc,TRUE);
2779: x_msg_data := NULL;
2780: -- Added by SHTADEPA
2781: IF l_debug_on THEN
2782: WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
2783: WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
2784: END IF;
2785:
2786: dbms_lob.createTemporary(clobdoc,TRUE);
2787:
2789: FETCH tnt_xml_cur INTO xdata;
2790: IF tnt_xml_cur%NOTFOUND
2791: THEN
2792: IF l_debug_on THEN
2793: WSH_DEBUG_SV.log(l_module_name,'No Rows Fetched for the given Delivery Detail Id');
2794: END IF;
2795: x_return_status := FND_API.G_RET_STS_ERROR;
2796: RETURN;
2797: END IF;
2804:
2805: dbms_xmldom.writeToClob(doc,clobdoc) ;
2806:
2807: IF l_debug_on THEN
2808: WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
2809: WSH_DEBUG_SV.log(l_module_name,clobdoc);
2810: END IF;
2811:
2812:
2805: dbms_xmldom.writeToClob(doc,clobdoc) ;
2806:
2807: IF l_debug_on THEN
2808: WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
2809: WSH_DEBUG_SV.log(l_module_name,clobdoc);
2810: END IF;
2811:
2812:
2813: IF l_debug_on THEN
2810: END IF;
2811:
2812:
2813: IF l_debug_on THEN
2814: WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
2815: END IF;
2816:
2817: l_internet_proxy := wsh_u_util.get_proxy
2818: ( p_api_version => 1.0
2822: , x_msg_data => l_msg_data);
2823:
2824: IF l_debug_on
2825: THEN
2826: WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
2827: WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
2828: END IF;
2829:
2830: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2823:
2824: IF l_debug_on
2825: THEN
2826: WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
2827: WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
2828: END IF;
2829:
2830: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2831: THEN
2854: l_http_resp := utl_http.get_response (l_http_req);
2855:
2856: IF l_debug_on
2857: THEN
2858: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
2859: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
2860: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
2861: END IF;
2862:
2855:
2856: IF l_debug_on
2857: THEN
2858: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
2859: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
2860: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
2861: END IF;
2862:
2863: BEGIN
2856: IF l_debug_on
2857: THEN
2858: WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
2859: WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
2860: WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
2861: END IF;
2862:
2863: BEGIN
2864: LOOP
2872: END LOOP;
2873: EXCEPTION
2874: WHEN OTHERS THEN
2875: IF l_debug_on THEN
2876: WSH_DEBUG_SV.log(l_module_name,'Catch while handling response '||sqlerrm);
2877: END IF;
2878: END;
2879:
2880: IF l_debug_on THEN
2877: END IF;
2878: END;
2879:
2880: IF l_debug_on THEN
2881: WSH_DEBUG_SV.log(l_module_name,'Debug l_resp_txt is '||l_resp_txt);
2882: END IF;
2883:
2884: -- Look for client-side error and report it.
2885: IF l_http_resp.status_code >= 400 AND
2895:
2896: IF l_my_proxy
2897: THEN
2898: IF l_debug_on THEN
2899: WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
2900: WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
2901: ||l_my_scheme
2902: ||' authentication username/password for realm '
2903: ||l_my_realm
2896: IF l_my_proxy
2897: THEN
2898: IF l_debug_on THEN
2899: WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
2900: WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
2901: ||l_my_scheme
2902: ||' authentication username/password for realm '
2903: ||l_my_realm
2904: ||' for the proxy server.');
2904: ||' for the proxy server.');
2905: END IF;
2906: ELSE
2907: IF l_debug_on THEN
2908: WSH_DEBUG_SV.log(l_module_name,'Web page '
2909: ||l_carrier_api_url
2910: ||' is protected.');
2911: WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
2912: ||l_my_scheme
2907: IF l_debug_on THEN
2908: WSH_DEBUG_SV.log(l_module_name,'Web page '
2909: ||l_carrier_api_url
2910: ||' is protected.');
2911: WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
2912: ||l_my_scheme
2913: ||' authentication username/password for realm '
2914: ||l_my_realm
2915: ||' for the Web page.');
2916: END IF;
2917: END IF;
2918: ELSE
2919: IF l_debug_on THEN
2920: WSH_DEBUG_SV.log(l_module_name,'Please Check the URL.');
2921: END IF;
2922: END IF;
2923:
2924: utl_http.end_response(l_http_resp);
2927: ELSIF l_http_resp.status_code >= 500 AND
2928: l_http_resp.status_code <= 599
2929: THEN
2930: IF l_debug_on THEN
2931: WSH_DEBUG_SV.log(l_module_name,'Check if the URL is up.');
2932: END IF;
2933:
2934: utl_http.end_response(l_http_resp);
2935: RETURN;
2937:
2938: utl_http.end_response (l_http_resp);
2939:
2940: IF l_debug_on THEN
2941: WSH_DEBUG_SV.log(l_module_name,'Parsing Response l_resp_txt');
2942: END IF;
2943:
2944: l_xml := xmltype(l_resp_txt);
2945: EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
2954: x_TimeInTransit_out.TransitTime := l_xml.extract('//tnt:TransitResponse/tnt:ServiceSummary[' || To_Char(l_index) || ']/tnt:EstimatedArrival/tnt:BusinessDaysInTransit/text()',l_namespace).getStringVal()||' ';
2955: EXCEPTION
2956: WHEN OTHERS THEN
2957: IF l_debug_on THEN
2958: WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.TransitTime:'||sqlerrm);
2959: END IF;
2960: END;
2961: BEGIN
2962: x_TimeInTransit_out.OriginCity := l_xml.extract('//tnt:TransitResponse/tnt:ShipFrom/tnt:Address/tnt:City/text()',l_namespace).getStringVal();
2962: x_TimeInTransit_out.OriginCity := l_xml.extract('//tnt:TransitResponse/tnt:ShipFrom/tnt:Address/tnt:City/text()',l_namespace).getStringVal();
2963: EXCEPTION
2964: WHEN OTHERS THEN
2965: IF l_debug_on THEN
2966: WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.OriginCity:'||sqlerrm);
2967: END IF;
2968: END;
2969: BEGIN
2970: x_TimeInTransit_out.OriginStateProv := l_xml.extract('//tnt:TransitResponse/tnt:ShipFrom/tnt:Address/tnt:StateProvinceCode/text()',l_namespace).getStringVal();
2970: x_TimeInTransit_out.OriginStateProv := l_xml.extract('//tnt:TransitResponse/tnt:ShipFrom/tnt:Address/tnt:StateProvinceCode/text()',l_namespace).getStringVal();
2971: EXCEPTION
2972: WHEN OTHERS THEN
2973: IF l_debug_on THEN
2974: WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.OriginStateProv:'||sqlerrm);
2975: END IF;
2976: END;
2977: BEGIN
2978: x_TimeInTransit_out.DestinationStateProv := l_xml.extract('//tnt:TransitResponse/tnt:ShipTo/tnt:Address/tnt:StateProvinceCode/text()',l_namespace).getStringVal();
2978: x_TimeInTransit_out.DestinationStateProv := l_xml.extract('//tnt:TransitResponse/tnt:ShipTo/tnt:Address/tnt:StateProvinceCode/text()',l_namespace).getStringVal();
2979: EXCEPTION
2980: WHEN OTHERS THEN
2981: IF l_debug_on THEN
2982: WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.DestinationStateProv:'||sqlerrm);
2983: END IF;
2984: END;
2985: BEGIN
2986: x_TimeInTransit_out.DestinationCity := l_xml.extract('//tnt:TransitResponse/tnt:ShipTo/tnt:Address/tnt:City/text()',l_namespace).getStringVal();
2986: x_TimeInTransit_out.DestinationCity := l_xml.extract('//tnt:TransitResponse/tnt:ShipTo/tnt:Address/tnt:City/text()',l_namespace).getStringVal();
2987: EXCEPTION
2988: WHEN OTHERS THEN
2989: IF l_debug_on THEN
2990: WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.DestinationCity:'||sqlerrm);
2991: END IF;
2992: END;
2993: l_index := l_index + 1;
2994:
3001: l_errmsg_txt := l_xml.extract('//err:Errors/err:ErrorDetail/err:PrimaryErrorCode/err:Description/text()', l_namespace).getStringVal();
3002: EXCEPTION
3003: WHEN OTHERS THEN
3004: IF l_debug_on THEN
3005: WSH_DEBUG_SV.log(l_module_name,'Exception while fetching the time in Transit error message :'||sqlerrm);
3006: END IF;
3007: l_errmsg_txt := 'Exception while fetching the Time In Transit error message';
3008: END;
3009: x_return_status := FND_API.G_RET_STS_ERROR;
3013:
3014: END IF;
3015:
3016: IF l_debug_on THEN
3017: WSH_DEBUG_SV.log(l_module_name,'TransitTime from UPS is '||x_TimeInTransit_out.TransitTime);
3018: WSH_DEBUG_SV.log(l_module_name,'Origin State Province returned from UPS is '||x_TimeInTransit_out.OriginStateProv);
3019: WSH_DEBUG_SV.log(l_module_name,'Destination State Prov returned from UPS is '||x_TimeInTransit_out.DestinationStateProv);
3020: WSH_DEBUG_SV.log(l_module_name,'OriginCity returned from UPS is '||x_TimeInTransit_out.OriginCity);
3021: WSH_DEBUG_SV.log(l_module_name,'Destination City returned from UPS is '||x_TimeInTransit_out.DestinationCity);
3014: END IF;
3015:
3016: IF l_debug_on THEN
3017: WSH_DEBUG_SV.log(l_module_name,'TransitTime from UPS is '||x_TimeInTransit_out.TransitTime);
3018: WSH_DEBUG_SV.log(l_module_name,'Origin State Province returned from UPS is '||x_TimeInTransit_out.OriginStateProv);
3019: WSH_DEBUG_SV.log(l_module_name,'Destination State Prov returned from UPS is '||x_TimeInTransit_out.DestinationStateProv);
3020: WSH_DEBUG_SV.log(l_module_name,'OriginCity returned from UPS is '||x_TimeInTransit_out.OriginCity);
3021: WSH_DEBUG_SV.log(l_module_name,'Destination City returned from UPS is '||x_TimeInTransit_out.DestinationCity);
3022: END IF;
3015:
3016: IF l_debug_on THEN
3017: WSH_DEBUG_SV.log(l_module_name,'TransitTime from UPS is '||x_TimeInTransit_out.TransitTime);
3018: WSH_DEBUG_SV.log(l_module_name,'Origin State Province returned from UPS is '||x_TimeInTransit_out.OriginStateProv);
3019: WSH_DEBUG_SV.log(l_module_name,'Destination State Prov returned from UPS is '||x_TimeInTransit_out.DestinationStateProv);
3020: WSH_DEBUG_SV.log(l_module_name,'OriginCity returned from UPS is '||x_TimeInTransit_out.OriginCity);
3021: WSH_DEBUG_SV.log(l_module_name,'Destination City returned from UPS is '||x_TimeInTransit_out.DestinationCity);
3022: END IF;
3023:
3016: IF l_debug_on THEN
3017: WSH_DEBUG_SV.log(l_module_name,'TransitTime from UPS is '||x_TimeInTransit_out.TransitTime);
3018: WSH_DEBUG_SV.log(l_module_name,'Origin State Province returned from UPS is '||x_TimeInTransit_out.OriginStateProv);
3019: WSH_DEBUG_SV.log(l_module_name,'Destination State Prov returned from UPS is '||x_TimeInTransit_out.DestinationStateProv);
3020: WSH_DEBUG_SV.log(l_module_name,'OriginCity returned from UPS is '||x_TimeInTransit_out.OriginCity);
3021: WSH_DEBUG_SV.log(l_module_name,'Destination City returned from UPS is '||x_TimeInTransit_out.DestinationCity);
3022: END IF;
3023:
3024: IF x_return_status = FND_API.G_RET_STS_ERROR
3017: WSH_DEBUG_SV.log(l_module_name,'TransitTime from UPS is '||x_TimeInTransit_out.TransitTime);
3018: WSH_DEBUG_SV.log(l_module_name,'Origin State Province returned from UPS is '||x_TimeInTransit_out.OriginStateProv);
3019: WSH_DEBUG_SV.log(l_module_name,'Destination State Prov returned from UPS is '||x_TimeInTransit_out.DestinationStateProv);
3020: WSH_DEBUG_SV.log(l_module_name,'OriginCity returned from UPS is '||x_TimeInTransit_out.OriginCity);
3021: WSH_DEBUG_SV.log(l_module_name,'Destination City returned from UPS is '||x_TimeInTransit_out.DestinationCity);
3022: END IF;
3023:
3024: IF x_return_status = FND_API.G_RET_STS_ERROR
3025: THEN
3040: ELSE
3041: x_msg_data := l_msg_summary;
3042: END IF;
3043: IF l_debug_on THEN
3044: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3045: WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
3046: END IF;
3047: WHEN WSH_U_HOST_FAILED THEN
3048: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
3041: x_msg_data := l_msg_summary;
3042: END IF;
3043: IF l_debug_on THEN
3044: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3045: WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
3046: END IF;
3047: WHEN WSH_U_HOST_FAILED THEN
3048: FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
3049: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
3058: x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
3059:
3060: --
3061: IF l_debug_on THEN
3062: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
3063: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3064: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
3065: END IF;
3066: WHEN OTHERS THEN
3059:
3060: --
3061: IF l_debug_on THEN
3062: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
3063: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3064: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
3065: END IF;
3066: WHEN OTHERS THEN
3067: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3060: --
3061: IF l_debug_on THEN
3062: WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
3063: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3064: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
3065: END IF;
3066: WHEN OTHERS THEN
3067: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3068: IF sqlcode ='-12543' THEN
3076: ELSE
3077: x_msg_data := l_msg_summary;
3078: END IF;
3079: IF l_debug_on THEN
3080: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3081: END IF;
3082: RETURN;
3083: ELSIF sqlcode ='-29106'
3084: THEN
3092: ELSE
3093: x_msg_data := l_msg_summary;
3094: END IF;
3095: IF l_debug_on THEN
3096: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3097: END IF;
3098: RETURN;
3099: ELSIF sqlcode ='-28759'
3100: THEN
3108: ELSE
3109: x_msg_data := l_msg_summary;
3110: END IF;
3111: IF l_debug_on THEN
3112: WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3113: END IF;
3114: RETURN;
3115: END IF;
3116: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3119: END IF;
3120: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
3121: --
3122: IF l_debug_on THEN
3123: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3124: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3125: END IF;
3126: END tnt_shipment_services;
3127:
3120: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
3121: --
3122: IF l_debug_on THEN
3123: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3124: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3125: END IF;
3126: END tnt_shipment_services;
3127:
3128: -- Carrier Services Shipping integration Project 16095594 End