1 PACKAGE BODY WSH_ITM_RESPONSE_PKG AS
2 /* $Header: WSHITRAB.pls 120.0.12010000.2 2010/02/16 17:22:54 skanduku ship $ */
3
4 SUCCESS CONSTANT VARCHAR2(10) := 'SUCCESS';
5 DATA_ERROR CONSTANT VARCHAR2(10) := 'DATA';
6 SYSTEM_ERROR CONSTANT VARCHAR2(10) := 'SYSTEM';
7 -- Name
8 -- INTERPRET_ERROR
9 --
10 -- Purpose
11 -- Internal. On passing the interpreted error, it sets the result code.
12 --
13 -- Arguments
14 -- p_fetch_interpreted_code Interpreted code got from the rule
15 -- x_interpreted_code Interpreted code to be returned
16
17 PROCEDURE INTERPRET_ERROR
18 (
19 p_fetch_interpreted_code IN WSH_ITM_RESPONSE_RULES.INTERPRETED_VALUE_CODE%TYPE,
20 x_interpreted_code IN OUT NOCOPY WSH_ITM_RESPONSE_RULES.INTERPRETED_VALUE_CODE%TYPE
21 ) IS
22
23 BEGIN
24 --Bug9277386:In response classification, an Error can be classified as 'FAILURE'.
25 -- Considering 'FAILURE' as 'SYSTEM' Error.
26
27 IF p_fetch_interpreted_code = SYSTEM_ERROR OR p_fetch_interpreted_code = 'FAILURE' THEN
28 x_interpreted_code := SYSTEM_ERROR;
29 ELSIF p_fetch_interpreted_code = DATA_ERROR AND nvl(x_interpreted_code,'X') <> SYSTEM_ERROR THEN
30 x_interpreted_code := DATA_ERROR;
31 END IF;
32 END;
33
34
35 PROCEDURE ONT_RESPONSE_ANALYSER
36 (
37 p_request_control_id IN NUMBER,
38 x_interpreted_value OUT NOCOPY VARCHAR2,
39 x_SrvTab OUT NOCOPY WSH_ITM_RESPONSE_PKG.SrvTabTyp,
40 x_return_status OUT NOCOPY VARCHAR2
41 )
42 IS
43
44 l_fetch_interpreted_code WSH_ITM_RESPONSE_RULES.INTERPRETED_VALUE_CODE%TYPE;
45 l_fetch_error_code WSH_ITM_RESPONSE_LINES.ERROR_CODE%TYPE;
46 l_fetch_error_type WSH_ITM_RESPONSE_LINES.ERROR_TYPE%TYPE;
47 l_response_hdr_id NUMBER;
48 l_vendor_id NUMBER;
49 l_sql_error VARCHAR2(2000);
50 --AJPRABHA - Modified service_type to VARCHAR(30)
51 l_service_type VARCHAR2(30);
52 l_exp_Compl_Resl VARCHAR2(30);
53 i NUMBER := 0;
54
55
56 CURSOR Get_Response_Header( req_control_id NUMBER) IS
57 SELECT wrh.response_header_id,
58 wrh.vendor_id,
59 wrh.error_type,
60 wrh.error_code,
61 wrr.interpreted_value_code,
62 wrh.EXPORT_COMPLIANCE_STATUS,
63 wrh.SERVICE_TYPE_CODE
64 FROM WSH_ITM_REQUEST_CONTROL WRC,
65 WSH_ITM_RESPONSE_HEADERS WRH,
66 WSH_ITM_RESPONSE_RULES WRR
67 WHERE wrc.request_control_id = req_control_id
68 AND wrc.response_header_id = wrh.response_header_id
69 AND nvl(wrh.error_code,-99) = nvl(wrr.error_code,nvl(wrh.error_code,-99))
70 AND wrh.error_type = wrr.error_type(+)
71 AND wrh.vendor_id = wrr.vendor_id(+);
72
73
74 CURSOR Get_Response_Line( resp_header_id NUMBER) IS
75 SELECT response_line_id,
76 error_code error_code,
77 error_type error_type,
78 denied_party_flag,
79 embargo_flag,
80 service_type_code
81 FROM WSH_ITM_RESPONSE_LINES
82 WHERE response_header_id = resp_header_id
83 ORDER BY service_type_code;
84
85 CURSOR Get_Interpreted_code(p_error_code VARCHAR2, p_error_type VARCHAR2,
86 p_vendor_id NUMBER) IS
87 SELECT interpreted_value_code
88 FROM WSH_ITM_RESPONSE_RULES
89 WHERE error_type = p_error_type
90 AND nvl(error_code,-99) = nvl(p_error_code,nvl(error_code,-99))
91 AND vendor_id = p_vendor_id
92 ORDER BY error_code;
93
94 BEGIN
95 OE_DEBUG_PUB.Add('***Inside the procedure ONT_RESPONSE_ANALYSER***');
96
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 OPEN Get_Response_Header(p_request_control_id);
100 FETCH Get_Response_Header INTO
101 l_response_hdr_id,
102 l_vendor_id,
103 l_fetch_error_type,
104 l_fetch_error_code,
105 l_fetch_interpreted_code,
106 l_exp_Compl_Resl,
107 l_service_type;
108
109 IF Get_Response_Header%NOTFOUND THEN
110 OE_DEBUG_PUB.Add('Request Control is not Found');
111 --Invalid trans control
112 x_interpreted_value:= SYSTEM_ERROR;
113 CLOSE Get_Response_Header;
114 RETURN;
115 END IF;
116 CLOSE Get_Response_Header;
117
118 OE_DEBUG_PUB.Add('Procesing Response Header :' || l_response_hdr_id);
119 IF l_fetch_interpreted_code is NULL THEN
120
121 IF l_fetch_error_type IS NULL AND
122 l_fetch_error_code IS NULL THEN
123 ---------------------------------------------------------
124 -- Error Code and Error Type is NULL in Response Headers
125 -- Interpreted Value will be SUCCESS
126 ---------------------------------------------------------
127 OE_DEBUG_PUB.Add('Error code and Error Type is Response Headers is null');
128 x_interpreted_value := SUCCESS;
129 ELSE
130 --Bug 9277386:If response rule is not defined, default to SYSTEM ERROR
131 ---------------------------------------------------------------
132 -- If not Response Rule is defined, then default in SYSTEM ERROR
133 ---------------------------------------------------------------
134 OE_DEBUG_PUB.Add('Error in the Request Control. There is no Rule Defined');
135 x_interpreted_value := SYSTEM_ERROR;
136 END IF;
137 ELSE
138
139 OE_DEBUG_PUB.Add('Interpreted Error from Get_Response_Headers Cursor - ' || l_fetch_interpreted_code );
140 OE_DEBUG_PUB.Add('Calling PROCEDURE Interpret_Error');
141
142 interpret_error(l_fetch_interpreted_code, x_interpreted_value ) ;
143 IF(x_interpreted_value = SYSTEM_ERROR) THEN
144 OE_DEBUG_PUB.Add('Procedure Interpret_Error returned SYSTEM Error');
145 RETURN;
146 END IF;
147
148 END IF;
149 i := 1;
150 --Added by AJPRABHA for OM_EXPORT_COMPLIANCE
151 IF (l_exp_Compl_Resl IS NOT NULL and l_service_type='OM_EXPORT_COMPLIANCE') THEN
152
153 OE_DEBUG_PUB.Add('For Service OM_EXPORT_COMPLIANCE');
154 x_SrvTab(i).Service_Type := 'OM_EXPORT_COMPLIANCE';
155
156 IF (l_exp_Compl_Resl = 'NOT_COMPLIANT') THEN
157 x_SrvTab(i).Service_Result := 'Y';
158 OE_DEBUG_PUB.Add('Done with EXPORT_COMPLIANCE : ' || l_exp_Compl_Resl);
159 RETURN;
160 END IF;
161
162 END IF;
163 i := 0; --Resetting the index for Other Services.
164 --End of Code added by AJPRABHA
165
166 --Fix for bug #3876344 for setting ServiceType to apply hold for DP.
167 l_service_type := 'X';
168 FOR resp IN Get_Response_Line(l_response_hdr_id) LOOP
169
170 OE_DEBUG_PUB.Add('Processing Line :' || resp.response_line_id);
171
172 IF ((resp.error_code is not NULL) OR (resp.error_type is not NULL)) THEN
173
174 OPEN Get_Interpreted_code(resp.error_code, resp.error_type, l_vendor_id);
175 FETCH Get_Interpreted_code INTO l_fetch_interpreted_code;
176
177 IF l_fetch_interpreted_code is null THEN --Rule not found
178 x_interpreted_value := SYSTEM_ERROR; --Bug 9277386 : Default is SYSTEM ERROR
179 OE_DEBUG_PUB.Add('Response Line Error, Rule Not Found for interrepted code '||l_fetch_interpreted_code);
180 OE_DEBUG_PUB.Add(' -> Error Code : ' || resp.error_code);
181 OE_DEBUG_PUB.Add(' -> Error Type : '|| resp.error_type );
182 END IF;
183 CLOSE Get_Interpreted_Code;
184
185 OE_DEBUG_PUB.Add('Reponse Line Error, Interpreted Code - ' || l_fetch_interpreted_code );
186 OE_DEBUG_PUB.Add('Calling PROCEDURE Interpret_Error');
187
188 interpret_error(l_fetch_interpreted_code, x_interpreted_value);
189 --Bug9277386:When response Status is 'ERROR', and error code is not specified, default to SYSTEM_ERROR
190 ELSE
191 IF l_exp_Compl_Resl = 'ERROR' THEN
192 x_interpreted_value := SYSTEM_ERROR;
193 RETURN;
194 END IF;
195 END IF;
196
197 IF (nvl(l_service_type,'X') <> resp.service_type_code and l_service_type <> 'WSH_EXPORT_COMPLIANCE') THEN
198
199 OE_DEBUG_PUB.Add('Service Type for Response Line ' || resp.response_line_id || ' is '|| resp.service_type_code);
200 i := i + 1;
201 l_service_type := resp.service_type_code;
202 x_SrvTab(i).Service_Type := resp.service_type_code;
203 x_SrvTab(i).Service_Result := 'N';
204 END IF;
205
206 IF l_service_type = 'DP' AND
207 resp.denied_party_flag = 'Y' THEN
208 OE_DEBUG_PUB.Add('Denied Party is found' );
209 x_SrvTab(i).Service_Result := 'Y';
210 ELSIF
211 l_service_type = 'EM' AND
212 resp.embargo_flag = 'Y' THEN
213 OE_DEBUG_PUB.Add('Embargo is found' );
214 x_SrvTab(i).Service_Result := 'Y';
215 END IF;
216
217 IF(x_interpreted_value = SYSTEM_ERROR) THEN
218 RETURN;
219 END IF;
220
221 END LOOP;
222
223 IF x_interpreted_value = SUCCESS THEN
224 UPDATE wsh_itm_request_control
225 SET process_flag = 1
226 WHERE request_control_id = p_request_control_id;
227 END IF;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 l_sql_error := SQLERRM;
232 OE_DEBUG_PUB.Add('Processing Failed with an Error');
233 OE_DEBUG_PUB.Add('The unexpected error is :' || l_sql_error);
234 x_interpreted_value := SYSTEM_ERROR;
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 END ONT_RESPONSE_ANALYSER;
237
238 PROCEDURE ONT_RESPONSE_ANALYSER
239 (
240 p_reference_id IN NUMBER,
241 p_reference_line_id IN NUMBER,
242 x_interpreted_value OUT NOCOPY VARCHAR2,
243 x_SrvTab OUT NOCOPY WSH_ITM_RESPONSE_PKG.SrvTabTyp,
244 x_return_status OUT NOCOPY VARCHAR2
245 ) IS
246 CURSOR Get_Request_Control IS
247 SELECT
248 DISTINCT wrc.request_control_id
249 FROM
250 WSH_ITM_REQUEST_CONTROL wrc,
251 WSH_ITM_RESPONSE_HEADERS wrh,
252 WSH_ITM_PARTIES wp
253 WHERE
254 wrc.response_header_id = wrh.response_header_id
255 AND wrc.request_control_id = wp.request_control_id
256 AND wrc.original_system_reference = nvl(p_reference_id, wrc.original_system_reference)
257 AND wrc.original_system_line_reference = nvl ( p_reference_line_id, wrc.original_system_line_reference)
258 AND wrc.process_flag IN (1,2)
259 UNION
260 SELECT
261 DISTINCT wrc.request_control_id
262 FROM
263 WSH_ITM_REQUEST_CONTROL wrc,
264 WSH_ITM_RESPONSE_HEADERS wrh,
265 WSH_ITM_RESPONSE_LINES wrl,
266 WSH_ITM_PARTIES wp
270 AND wrc.request_control_id = wp.request_control_id
267 WHERE
268 wrc.response_header_id = wrh.response_header_id
269 AND wrh.response_header_id = wrl.response_header_id
271 AND wrc.original_system_reference = nvl(p_reference_id, wrc.original_system_reference)
272 AND wrc.original_system_line_reference = nvl ( p_reference_line_id, wrc.original_system_line_reference)
273 AND wrc.process_flag IN (1,2);
274
275 BEGIN
276 OE_DEBUG_PUB.Add('Inside ONT_RESPONSE_ANALYSER');
277 ------------------------------------------
278 -- Print the values of all the parameters.
279 ------------------------------------------
280 OE_DEBUG_PUB.Add('Reference ID ' || p_reference_id);
281 OE_DEBUG_PUB.Add('Reference Line ID ' || p_reference_line_id);
282
283 FOR cur_rec in Get_Request_Control LOOP
284 OE_DEBUG_PUB.Add(' Calling ONT_RESPONSE_ANALYSER for reqID' || cur_rec.request_control_id);
285
286 ONT_RESPONSE_ANALYSER (
287 p_request_control_id => cur_rec.request_control_id,
288 x_interpreted_value => x_interpreted_value,
289 x_return_status => x_return_status,
290 x_SrvTab => x_SrvTab
291 );
292 END LOOP;
293 OE_DEBUG_PUB.Add(' Done with ONT_RESPONSE_ANALYSER');
294
295 END ONT_RESPONSE_ANALYSER;
296
297 END WSH_ITM_RESPONSE_PKG;
[Home] [Help]