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