[Home] [Help]
PACKAGE BODY: APPS.WSH_ITM_PKG
Source
1 PACKAGE BODY WSH_ITM_PKG AS
2 /* $Header: WSHITPCB.pls 120.2.12010000.3 2010/02/16 17:21:29 skanduku ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ITM_PKG';
5 G_SUB_PICK_RELEASE_EXCEPTION CONSTANT VARCHAR2(30) := 'WSH_PR_SUB_EXPORT_COMPL';
6 G_SUB_SHIP_CONFIRM_EXCEPTION CONSTANT VARCHAR2(30) := 'WSH_SC_SUB_EXPORT_COMPL';
7 G_FAIL_EXP_COMPL_EXCEPTION CONSTANT VARCHAR2(30) := 'WSH_EXPORT_COMPL_FAILED';
8 G_PICK_RELEASE_EVENT CONSTANT VARCHAR2(20) := 'PICK_RELEASE';
9 G_SHIP_CONFIRM_EVENT CONSTANT VARCHAR2(20) := 'SHIP_CONFIRM';
10 G_SERVICE_TYPE_CODE CONSTANT VARCHAR2(30) := 'WSH_EXPORT_COMPLIANCE';
11
12
13 G_WF_PICK_RELEASE_EVENT_NAME CONSTANT VARCHAR2(100) :=
14 'oracle.apps.wsh.delivery.itm.responsereceivedatdelcreate';
15 G_WF_SHIP_CONFIRM_EVENT_NAME CONSTANT VARCHAR2(100) :=
16 'oracle.apps.wsh.delivery.itm.responsereceivedatship';
17
18
19
20 /*==========================================================================+
21 | PROCEDURE |
22 | WSH_ITM_WSH |
23 | PARAMETERS |
24 | |
25 | p_request_control_id => This is the request_control which uniquely |
26 | identifies the request information from the |
27 | request control table. |
28 | p_request_set_id => This parameter is not being used in the current |
29 | procedure,but is included to comply with |
30 | itm standards. |
31 | DESCRIPTION |
32 | This procedure is called by ITM Application |
33 | When a response is recived from The Partner Application |
34 | Informing the status of export compliance request. |
35 | This API does the post processing operations depending on |
36 | the status of screening. |
37 | |
38 | |
39 | |
40 +===========================================================================*/
41
42
43
44 PROCEDURE WSH_ITM_WSH (
45 p_request_control_id IN NUMBER,
46 p_request_set_id IN NUMBER
47 )
48 IS
49
50
51 --Declaration Cursor For Export Compliance Query
52 --Bug 9277386:Added column Interpreted_Value_Code from WSH_ITM_RESPONSE_RULES
53 CURSOR C_EXP_COMPL_QUERY(p_req_ctrl_id NUMBER, p_status VARCHAR2) is
54 SELECT RL.EXPORT_COMPLIANCE_TYPE COMPL_TYPE,
55 DECODE(RL.EXPORT_COMPLIANCE_SUCCESS, 'Y', 'COMPLIANT', 'N', 'NOT_COMPLIANT') COMPL_STATUS,
56 RL.EXPORT_COMPLIANCE_DESCRIPTION COMPL_DESC,
57 RL.ERROR_CODE ERROR_CODE,
58 RL.ERROR_TYPE ERROR_TYPE,
59 RL.ERROR_TEXT ERROR_TEXT,
60 WRR.INTERPRETED_VALUE_CODE INTERPRETED_VALUE
61 FROM WSH_ITM_RESPONSE_HEADERS RH,
62 WSH_ITM_RESPONSE_LINES RL,
63 WSH_ITM_REQUEST_CONTROL REQ,
64 WSH_ITM_RESPONSE_RULES WRR
65 WHERE
66 REQ.REQUEST_CONTROL_ID = p_req_ctrl_id AND
67 REQ.RESPONSE_HEADER_ID = RH.RESPONSE_HEADER_ID AND
68 RH.RESPONSE_HEADER_ID = RL.RESPONSE_HEADER_ID AND
69 UPPER(RH.EXPORT_COMPLIANCE_STATUS) = p_status AND
70 WRR.ERROR_CODE(+) = RL.error_code AND
71 WRR.ERROR_TYPE(+) =RL.ERROR_TYPE;
72
73
74 -- Declaration Section For Log/close Exception Section
75
76 i number;
77
78 l_api_version NUMBER := 1.0;
79 l_return_status VARCHAR2(1);
80 l_msg_count NUMBER;
81 l_msg_data VARCHAR2(2000); --Bug 7125729:Increasing message buffer size
82 l_validation_level NUMBER default FND_API.G_VALID_LEVEL_FULL;
83
84 l_old_status VARCHAR2(30);
85 l_new_status VARCHAR2(30);
86 l_default_status VARCHAR2(1);
87
88 l_exception_message varchar2(2000);
89 l_exception_name varchar2(30);
90
91 l_location_id NUMBER;
92 l_delivery_id NUMBER;
93 l_delivery_name VARCHAR2(30);
94 l_exception_id NUMBER;
95 xx_exception_id NUMBER;
96
97 l_exception_found boolean := true;
98 l_event_name VARCHAR2(20);
99
100 --
101 l_process_flag NUMBER;
102 l_interpreted_value VARCHAR2(10);--Bug 9277386
103 --for Workflow process
104 l_wf_event_name VARCHAR2(1000);
105 l_wf_return_status VARCHAR2(1);
106 l_organization_id NUMBER;
107
108 BEGIN
109
110
111 SAVEPOINT WSH_ITM_POST_COMPL;
112
113 -------------------------------------------------------------------------------
114 -- Post Processing Section
115 -------------------------------------------------------------------------------
116
117 -- This sub section checks if the process is completed
118 SELECT PROCESS_FLAG INTO L_PROCESS_FLAG
119 FROM WSH_ITM_REQUEST_CONTROL
120 WHERE REQUEST_CONTROL_ID = P_REQUEST_CONTROL_ID;
121
122
123
124 -- This Sub Section Fetches The Location Code,Delivery Name for Submitted Exception
125
126 BEGIN
127 SELECT WE.EXCEPTION_LOCATION_ID,
128 WE.DELIVERY_ID,
129 WE.DELIVERY_NAME,
130 WE.EXCEPTION_ID,
131 IRC.TRIGGERING_POINT,
132 WE.STATUS,
133 IRC.ORGANIZATION_ID
134 INTO l_location_id,
135 l_delivery_id,
136 l_delivery_name,
137 l_exception_id,
138 l_event_name,
139 l_old_status,
140 l_organization_id
141 FROM WSH_EXCEPTIONS WE ,
142 WSH_ITM_REQUEST_CONTROL IRC
143 WHERE WE.DELIVERY_ID = IRC.ORIGINAL_SYSTEM_REFERENCE
144 AND WE.EXCEPTION_NAME = DECODE(IRC.TRIGGERING_POINT,G_PICK_RELEASE_EVENT,G_SUB_PICK_RELEASE_EXCEPTION,G_SHIP_CONFIRM_EVENT,G_SUB_SHIP_CONFIRM_EXCEPTION)
145 AND IRC.REQUEST_CONTROL_ID = P_REQUEST_CONTROL_ID
146 AND WE.STATUS <> 'CLOSED';
147
148 --Raise Functional Event that could be customized by the Workflow
149 IF l_event_name= 'PICK_RELEASE' THEN
150 l_wf_event_name := G_WF_PICK_RELEASE_EVENT_NAME;
151 ELSIF l_event_name = 'SHIP_CONFIRM' THEN
152 l_wf_event_name := G_WF_SHIP_CONFIRM_EVENT_NAME;
153 END IF;
154
155 WSH_ITM_EXPORT_SCREENING.RAISE_ITM_EVENT
156 (
157 p_event_name => l_wf_event_name ,
158 p_delivery_id => l_delivery_id,
159 p_organization_id => l_organization_id,
160 x_return_status => l_wf_return_status
161 );
162
163 --End of Functional Event that could be customized by the Workflow
164 EXCEPTION
165 WHEN OTHERS THEN
166 RETURN;
167 END;
168
169 -- This Section Takes The Required Action Based on Process Flag and Compliance's Success
170 IF l_process_flag = 1 THEN
171
172 l_exception_message := NULL;
173
174 --Retreiving Type, Success and Description columns from the
175 -- Response lines table.
176 FOR l_nonComplRec IN C_EXP_COMPL_QUERY(p_request_control_id, 'NOT_COMPLIANT') LOOP
177 l_exception_message := l_exception_message||' - '||l_nonComplRec.COMPL_TYPE||
178 ' '|| l_nonComplRec.COMPL_STATUS ||
179 ' '||l_nonComplRec.COMPL_DESC;
180 END LOOP;
181 -- When Compliance Failed
182 IF l_exception_message is NOT NULL THEN
183 -- Sub Section Logs a new Exception For Compliance Failure
184 l_return_status := NULL;
185 l_msg_count := NULL;
186 l_msg_data := NULL;
187 xx_exception_id := NULL;
188 l_exception_name := G_FAIL_EXP_COMPL_EXCEPTION;
189
190 WSH_XC_UTIL.log_exception(
191 p_api_version => l_api_version,
192 p_init_msg_list => FND_API.G_FALSE,
193 p_commit => FND_API.G_FALSE,
194 p_validation_level => l_validation_level,
195 x_return_status => l_return_status,
196 x_msg_count => l_msg_count,
197 x_msg_data => l_msg_data,
198 x_exception_id => xx_exception_id,
199 p_exception_location_id => l_location_id,
200 p_logged_at_location_id => l_location_id,
201 p_logging_entity => 'SHIPPER',
202 p_logging_entity_id => l_delivery_id,
203 p_exception_name => l_exception_name,
204 p_message => l_exception_message,
205 p_delivery_id => l_delivery_id,
206 p_delivery_name => l_delivery_name
207 );
208
209 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
210
211
212 if l_msg_count IS NOT NULL then
213 WSH_UTIL_CORE.Add_Message(l_return_status);
214 for i in 1 ..l_msg_count loop
215 l_msg_data := FND_MSG_PUB.get(p_msg_index => i,
216 p_encoded => 'F');
217
218 end loop;
219 END IF;
220
221 -- Cleaning Operation
222 ROLLBACK TO WSH_ITM_POST_COMPL;
223
224
225 RETURN;
226 END IF;
227 END IF; -- End of Compliance Failure IF Block
228
229 --Bug 6371639: NO AUDIT TRAIL PROVIDED FOR SHIPMENTS SCREENED BY ITM FOR SUCCESSFULLY SCREENED REQUEST.
230 -- Commenting the below Delete Statements to retain the Records in WSH_ITM tables
231
232 /* delete from wsh_itm_response_lines where response_header_id IN
233 (
234 select response_header_id
235 from wsh_itm_response_headers
236 where request_control_id = p_request_control_id
237 );
238
239 delete from wsh_itm_response_headers where request_control_id = p_request_control_id;
240 delete from wsh_itm_request_control where request_control_id = p_request_control_id; */
241
242
243
244 ELSIF l_process_flag = 2 THEN
245 -- System And Data Error Handling
246 l_exception_message := NULL;
247 FOR l_errorRec IN C_EXP_COMPL_QUERY(p_request_control_id, 'ERROR') LOOP
248 l_exception_message := l_exception_message||' - '||l_errorRec.ERROR_CODE ||
249 ' '|| l_errorRec.ERROR_TYPE ||
250 ' '||l_errorRec.ERROR_TEXT;
251 --Bug 9277286
252 l_interpreted_value := l_errorRec.INTERPRETED_VALUE;
253
254 END LOOP;
255 --Bug 9277386:Populating existing Exception's Error Message, when respose STATUS is ERROR.
256 UPDATE wsh_exceptions
257 SET error_message = l_exception_message ,
258 last_update_date = SYSDATE ,
259 last_updated_by = fnd_global.user_id
260 WHERE exception_id = l_exception_id;
261 --Bug 9277386:When the error code from Response XML is interpreted as 'SUCCESS',
262 -- updating process_flag to 1 and closing the existing ITM exceptions.
263 IF l_interpreted_value = 'SUCCESS' THEN
264
265 UPDATE wsh_itm_request_control
266 SET process_flag = 1 ,
267 last_update_date= sysdate,
268 last_updated_by = fnd_global.user_id
269 WHERE request_control_id = p_request_control_id;
270
271 GOTO CLOSE_EXCEPTION;
272 ELSE
273 RETURN;
274 END IF;
275 --Bug 9277386:Whens status is 'ERROR', the exception WSH_EXPORT_COMPL_FAILED should not be logged
276 -- Deleted the code that logs new exception when status is 'ERROR'.
277 END IF; -- End of Process Flag (If Block)
278 ----------------------------------------------------------------------------------------
279 -- Purges the data in ITM Request and Response Tables
280 ----------------------------------------------------------------------------------------
281
282 /*delete from wsh_itm_response_lines where response_header_id IN
283 (
284 select response_header_id
285 from wsh_itm_response_headers
286 where request_control_id = p_request_control_id
287 );
288 */
289 -- delete from wsh_itm_response_headers where request_control_id = p_request_control_id;
290 -- delete from wsh_itm_request_control where request_control_id = p_request_control_id;
291
292 ----------------------------------------------------------------------------------------
293 -- Handle Submitted For Export Screening Exception
294 ----------------------------------------------------------------------------------------
295 -- Section(Applies For Both Compliance Failure and Success) to Handle 'Submitted For Export Screening'
296 <<CLOSE_EXCEPTION>>
297 l_return_status := NULL;
298 l_msg_count := NULL;
299 l_msg_data := NULL;
300
301 l_new_status := 'CLOSED';
302 l_default_status := 'F';
303
304 IF l_old_status = 'CLOSED' THEN
305 RETURN;
306 END IF;
307
308 WSH_XC_UTIL.change_status (
309 p_api_version => l_api_version,
310 p_init_msg_list => FND_API.G_FALSE,
311 p_commit => FND_API.G_FALSE,
312 p_validation_level => l_validation_level,
313 x_return_status => l_return_status,
314 x_msg_count => l_msg_count,
315 x_msg_data => l_msg_data,
316 p_exception_id => l_exception_id,
317 p_old_status => l_old_status,
318 p_set_default_status => l_default_status,
319 x_new_status => l_new_status
320 );
321
322 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
323
324
325
326 if l_msg_count IS NOT NULL then
327 WSH_UTIL_CORE.Add_Message(l_return_status);
328 for i in 1 ..l_msg_count loop
329 l_msg_data := FND_MSG_PUB.get(p_msg_index => i,
330 p_encoded => 'F');
331
332 end loop;
333 end if;
334 -- Cleaning Operation
335 ROLLBACK TO WSH_ITM_POST_COMPL;
336 RETURN;
337
338 END IF;
339
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 ROLLBACK TO WSH_ITM_POST_COMPL;
344 END WSH_ITM_WSH;
345
346 END WSH_ITM_PKG;