1 PACKAGE BODY WSH_ITM_UTIL AS
2 /* $Header: WSHUTITB.pls 120.0.12010000.3 2009/09/23 12:39:51 gbhargav 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 --Added in Bug 8916313
242 --===================================================================================================
243 -- Start of comments
244 --
245 -- API Name : GET_COMPLIANCE_STATUS
246 -- Type : Public
247 -- Purpose : Called by OM to get the ITM request control compliance status.
248 -- Pre-reqs : None
249 -- Function : This API can be used to get the compliance status of the ITM request control lines
250 -- In case if multiple request lines are present this will only return the first record
251 -- found with the matching i/p criteria
252 -- Before OM inserts a record in Wsh_Itm_Request_Control table, if there exists a record
253 -- in WIRC table for same order line then OM (OEXVITMB.pls) calls API
254 -- WSH_ITM_UTIL.Update_process_Flag to update the process_flag value to 4.
255 -- So, while querying from WIRC table ignore record with process_flag value 4.
256 --
257 -- PARAMETERS : p_appliciation_id i/p Appliciation id
258 -- p_original_sys_reference i/p Original system reference
259 -- p_original_sys_line_reference i/p Original system line reference
260 -- x_process_flag o/p compliance status.
261 -- x_request_control_id o/p request_control_id ,
262 -- x_request_set_id o/p request_set_id ,
263 -- x_return_status o/p return status
264 -- VERSION : current version 1.0
265 -- initial version 1.0
266 -- End of comments
267 --===================================================================================================
268
269 PROCEDURE GET_COMPLIANCE_STATUS ( p_appliciation_id IN NUMBER,
270 p_original_sys_reference IN NUMBER,
271 p_original_sys_line_reference IN NUMBER,
272 x_process_flag OUT NOCOPY NUMBER,
273 x_request_control_id OUT NOCOPY NUMBER,
274 x_request_set_id OUT NOCOPY NUMBER,
275 x_return_status OUT NOCOPY VARCHAR2)
276 IS
277 l_sql_error VARCHAR2(2000);
278 l_process_flag NUMBER ;
279 l_request_control_id NUMBER;
280 l_request_set_id NUMBER ;
281
282 --Cursor to get the compliance details of the ITM request lines
283 CURSOR c_get_compliance_details(l_appliciation_id NUMBER,l_original_sys_reference NUMBER ,l_original_sys_line_reference NUMBER) IS
284 SELECT process_flag,
285 request_control_id,
286 request_set_id
287 FROM wsh_itm_request_control WRC
288 WHERE wrc.application_id = l_appliciation_id AND
289 wrc.original_system_reference = l_original_sys_reference AND
290 wrc.original_system_line_reference = l_original_sys_line_reference AND
291 wrc.process_flag <> 4;
292
293 MISSING_INPUT EXCEPTION;
294 INVALID_APPLICATION EXCEPTION;
295
296 BEGIN
297
298 --
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300 oe_debug_pub.add('In the procedure WSH_ITM_UTIL.GET_COMPLIANCE_STATUS');
301 oe_debug_pub.add('p_appliciation_id :' || p_appliciation_id );
302 oe_debug_pub.add('p_original_sys_reference :' || p_original_sys_reference );
303 oe_debug_pub.add('p_original_sys_line_reference :' || p_original_sys_line_reference );
304
305 IF p_appliciation_id IS NOT NULL THEN
306 --{
307
308 IF p_appliciation_id = 660 THEN
309 --{
310 IF ( p_original_sys_reference IS NULL OR p_original_sys_line_reference IS NULL ) THEN
311 --{
312 oe_debug_pub.add('P_original_sys_reference and p_original_sys_line_reference are mandatory parameter for appplication_id 660');
313 RAISE MISSING_INPUT ;
314 --}
315 END IF;
316
317 --Get the compliance details of the ITM request lines
318 --API will return compliance data for only first reqest line (in case of multiple lines are present)
319 OPEN c_get_compliance_details(p_appliciation_id,p_original_sys_reference,p_original_sys_line_reference);
320 FETCH c_get_compliance_details INTO l_process_flag , l_request_control_id ,l_request_set_id;
321 CLOSE c_get_compliance_details;
322 --}
323 ELSE
324 --{
325 oe_debug_pub.add('Applciation_id is invalid');
326 RAISE INVALID_APPLICATION;
327 --}
328 END IF;
329 --}
330 ELSE
331 --{
332 oe_debug_pub.add('Application_id is mandatory');
333 RAISE MISSING_INPUT ;
334 --}
335 END IF;
336
337 x_process_flag := l_process_flag;
338 x_request_control_id := l_request_control_id;
339 x_request_set_id := l_request_set_id;
340
341 oe_debug_pub.add('End of the procedure WSH_ITM_UTIL.GET_COMPLIANCE_STATUS');
342 --
343 EXCEPTION
344
345 WHEN INVALID_APPLICATION THEN
346 oe_debug_pub.add('Processing Failed as application_id provided is invalid ');
347 x_return_status := FND_API.G_RET_STS_ERROR;
348
349 WHEN MISSING_INPUT THEN
350 oe_debug_pub.add('Processing Failed as some mandatory parameters are missing ');
351 x_return_status := FND_API.G_RET_STS_ERROR;
352
353 WHEN OTHERS THEN
354
355 IF c_get_compliance_details%ISOPEN THEN
356 CLOSE c_get_compliance_details;
357 END IF;
358 l_sql_error := SQLERRM;
359 oe_debug_pub.add('Processing Failed with an Error');
360 oe_debug_pub.add('The unexpected error is :' || l_sql_error);
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362
363 END GET_COMPLIANCE_STATUS;
364
365 END WSH_ITM_UTIL;
[Home] [Help]