1 PACKAGE BODY WSH_ITM_UTIL AS
2 /* $Header: WSHUTITB.pls 115.5 2004/01/19 14:39:18 sgumaste ship $ */
3
4
5 -- Name
6 -- GET_SERVICE_DETAILS
7 -- Purpose
8 -- On passing the p_application_id, p_master_organization_id and
9 -- p_organization_id this procedure returns the services defined for
10 -- user and the additional country codes for all the services.
11 -- Arguments
12 -- p_application_id p_application_id of a request
13 -- p_master_organization_id p_master_organization_id of a request
14 -- p_organization_id p_organization_id of a request
15 -- x_service_tbl service types and addl_country_codes
16 -- returned as a PLSQL table Service_Rec_Type
17 -- x_supports_combination_flag A flag indicating whether a combined
18 -- request for all the services returned
19 -- by x_service_types_rec is supported or
20 -- not.
21 -- x_return_status Return Status
22 -- Notes
23 -- Refer the record T_SERVICE_TYPES_REC
24
25
26
27 PROCEDURE GET_SERVICE_DETAILS (
28 p_application_id IN NUMBER,
29 p_master_organization_id IN NUMBER,
30 p_organization_id IN NUMBER,
31 x_service_tbl OUT NOCOPY Service_Tbl_Type,
32 x_supports_combination_flag OUT NOCOPY VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2)
34 IS
35 priority NUMBER := 0;
36 service_tbl Service_Tbl_Type;
37 G_MISS_SERVICE_TBL Service_Tbl_Type;
38 vendor_id WSH_ITM_VENDORS.VENDOR_ID%TYPE := 0;
39 comb_flag WSH_ITM_VENDORS.SUPPORTS_COMBINATION_FLAG%TYPE := 'Y';
40 l_addl_country_code VARCHAR2(5);
41 l_sql_error VARCHAR2(2000);
42 i NUMBER := 1;
43
44 CURSOR Get_Services(
45 p_application_id NUMBER,
46 p_master_organization_id NUMBER) IS
47 --Bug3330869 SELECT * from (
48 SELECT priority ,
49 service_type ,
50 addl_country_check_req ,
51 vendor_id ,
52 supports_combination_flag
53 FROM (
54 SELECT 1 priority,
55 s.service_type,
56 s.addl_country_check_req,
57 v.vendor_id,
58 v.supports_combination_flag
59 FROM WSH_ITM_SERVICE_PREFERENCES us1,
60 WSH_ITM_VENDOR_SERVICES s,
61 WSH_ITM_VENDORS v
62 WHERE us1.application_id = p_application_id and
63 us1.master_organization_id = p_master_organization_id and
64 us1.vendor_service_id = s.vendor_service_id and
65 us1.active_flag = 'Y'and
66 s.vendor_id = v.vendor_id
67 UNION
68 SELECT 2 priority,
69 s.service_type,
70 s.addl_country_check_req,
71 v.vendor_id,
72 v.supports_combination_flag
73 FROM WSH_ITM_SERVICE_PREFERENCES us1,
74 WSH_ITM_VENDOR_SERVICES s,
75 WSH_ITM_VENDORS v
76 WHERE us1.application_id = p_application_id and
77 us1.master_organization_id is null and
78 us1.active_flag = 'Y'and
79 us1.vendor_service_id = s.vendor_service_id and
80 s.vendor_id = v.vendor_id
81 ) order by priority;
82
83 BEGIN
84 --
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86 oe_debug_pub.add('***Inside the procedure WSH_ITM_UTIL.GET_SERVICE_DETAILS***');
87 oe_debug_pub.add('Application Id ' || p_application_id);
88 oe_debug_pub.add('Master Organization Id ' || p_master_organization_id);
89 oe_debug_pub.add('Organization Id ' || p_organization_id);
90
91 service_tbl := G_MISS_SERVICE_TBL;
92
93 BEGIN
94 SELECT ITM_ADDITIONAL_COUNTRY_CODE
95 INTO l_addl_country_code
96 FROM WSH_SHIPPING_PARAMETERS
97 WHERE organization_id = p_organization_id;
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 l_addl_country_code := '';
101 END;
102
103 oe_debug_pub.add('Additional Country Code is ' || l_addl_country_code);
104
105 FOR ser_details IN Get_Services(p_application_id, p_master_organization_id)
106 LOOP
107
108 IF ser_details.priority >= priority THEN
109 priority := ser_details.priority;
110 ELSE
111 EXIT;
112 END IF;
113
114 service_tbl(i).service_type_code := ser_details.service_type;
115 IF ser_details.addl_country_check_req = 'Y' then
116 service_tbl(i).addl_country_code := l_addl_country_code;
117 ELSE
118 service_tbl(i).addl_country_code := '';
119 END IF;
120
121 oe_debug_pub.add('Service Type ' || service_tbl(i).service_type_code);
122 oe_debug_pub.add('Additional Country Code ' || service_tbl(i).addl_country_code );
123
124 i := i + 1;
125
126 IF vendor_id = 0 then
127 vendor_id := ser_details.vendor_id;
128 END IF;
129
130 IF vendor_id <> ser_details.vendor_id OR ser_details.supports_combination_flag = 'N' THEN
131 comb_flag := 'N';
132 END IF;
133
134 vendor_id := ser_details.vendor_id;
135
136 END LOOP;
137
138 oe_debug_pub.add('Supports Combination Flag ' || comb_flag );
139 x_supports_combination_flag := comb_flag;
140 x_service_tbl := service_tbl;
141 oe_debug_pub.add('***End of the procedure WSH_ITM_UTIL.GET_SERVICE_DETAILS***');
142
143
144 EXCEPTION
145
146 WHEN OTHERS THEN
147 l_sql_error := SQLERRM;
148 OE_DEBUG_PUB.Add('Processing Failed with an Error');
149 OE_DEBUG_PUB.Add('The unexpected error is :' || l_sql_error);
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
151
152 END GET_SERVICE_DETAILS;
153
154
155 -- Name
156 -- UPDATE_PROCESS_FLAG
157 -- Purpose
158 -- To update the process_flag of a request.
159 -- Arguments
160 -- p_control_id_list A PLSQL Table containing the list of transaction control id values
161 -- p_process_flag process_flag value
162 -- x_return_status Return Status
163
164
165 PROCEDURE UPDATE_PROCESS_FLAG (
166 p_control_id_list IN CONTROL_ID_LIST,
167 p_process_flag IN NUMBER,
168 x_return_status OUT NOCOPY VARCHAR2)
169 IS
170 l_sql_error VARCHAR2(2000);
171 i NUMBER;
172
173 BEGIN
174
175 --
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177 oe_debug_pub.add('In the procedure WSH_ITM_UTIL.UPDATE_PROCESS_FLAG');
178
179 FORALL i IN p_control_id_list.FIRST..p_control_id_list.LAST
180 UPDATE WSH_ITM_REQUEST_CONTROL
181 SET
182 PROCESS_FLAG = p_process_flag
183 WHERE REQUEST_CONTROL_ID = p_control_id_list(i);
184
185 oe_debug_pub.add('End of the procedure WSH_ITM_UTIL.UPDATE_PROCESS_FLAG');
186
187 EXCEPTION
188
189 WHEN OTHERS THEN
190 l_sql_error := SQLERRM;
191 oe_debug_pub.add('Processing Failed with an Error');
192 oe_debug_pub.add('The unexpected error is :' || l_sql_error);
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194
195 END UPDATE_PROCESS_FLAG;
196
197 -- Name
198 -- get vendor
199 -- Purpose
200 -- To get the Vendor name from the Request Control ID
201 -- Arguments
202 -- p_request_control_id p_request_control_id of a request
203 -- x_service_provider VendorName
204 PROCEDURE GET_SERVICE_PROVIDER(
205 p_request_control_id IN NUMBER,
206 x_service_provider OUT NOCOPY VARCHAR2) IS
207 BEGIN
208 SELECT VENDOR INTO x_service_provider FROM
209 ( SELECT 1 PRIORITY, V.SERVICE_PROVIDER VENDOR
210 FROM WSH_ITM_SERVICE_PREFERENCES US1,
211 WSH_ITM_VENDOR_SERVICES S, WSH_ITM_VENDORS V,
212 WSH_ITM_REQUEST_CONTROL RC
213 WHERE US1.APPLICATION_ID = RC.APPLICATION_ID AND
214 US1.MASTER_ORGANIZATION_ID = RC.MASTER_ORGANIZATION_ID AND
215 US1.VENDOR_SERVICE_ID = S.VENDOR_SERVICE_ID AND
216 S.VENDOR_ID = V.VENDOR_ID AND
217 US1.ACTIVE_FLAG = 'Y' AND
218 S.SERVICE_TYPE = RC.SERVICE_TYPE_CODE AND
219 RC.REQUEST_CONTROL_ID = p_request_control_id
220 UNION
221 SELECT 2 PRIORITY, V.SERVICE_PROVIDER VENDOR
222 FROM WSH_ITM_SERVICE_PREFERENCES US1,
223 WSH_ITM_VENDOR_SERVICES S, WSH_ITM_VENDORS V,
224 WSH_ITM_REQUEST_CONTROL RC
225 WHERE US1.APPLICATION_ID = RC.APPLICATION_ID AND
226 US1.MASTER_ORGANIZATION_ID IS NULL AND
227 US1.VENDOR_SERVICE_ID = S.VENDOR_SERVICE_ID AND
228 S.VENDOR_ID = V.VENDOR_ID AND
229 US1.ACTIVE_FLAG = 'Y' AND
230 S.SERVICE_TYPE = RC.SERVICE_TYPE_CODE AND
231 RC.REQUEST_CONTROL_ID = p_request_control_id
232 ORDER BY PRIORITY
233 ) WHERE ROWNUM < 2;
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 x_service_provider := NULL;
237 WHEN OTHERS THEN
238 x_service_provider := NULL;
239 END GET_SERVICE_PROVIDER;
240
241
242 END WSH_ITM_UTIL;
[Home] [Help]