[Home] [Help]
PACKAGE BODY: APPS.WSH_ITM_PKG
Source
1 PACKAGE BODY WSH_ITM_PKG AS
2 /* $Header: WSHITPCB.pls 120.2.12010000.2 2008/08/04 12:31:29 suppal 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 CURSOR C_EXP_COMPL_QUERY(p_req_ctrl_id NUMBER, p_status VARCHAR2) is
53 SELECT RL.EXPORT_COMPLIANCE_TYPE COMPL_TYPE,
54 DECODE(RL.EXPORT_COMPLIANCE_SUCCESS, 'Y', 'COMPLIANT', 'N', 'NOT_COMPLIANT') COMPL_STATUS,
55 RL.EXPORT_COMPLIANCE_DESCRIPTION COMPL_DESC,
56 RL.ERROR_CODE ERROR_CODE,
57 RL.ERROR_TYPE ERROR_TYPE,
58 RL.ERROR_TEXT ERROR_TEXT
59 FROM WSH_ITM_RESPONSE_HEADERS RH,
60 WSH_ITM_RESPONSE_LINES RL,
61 WSH_ITM_REQUEST_CONTROL REQ
62 WHERE
63 REQ.REQUEST_CONTROL_ID = p_req_ctrl_id AND
64 REQ.RESPONSE_HEADER_ID = RH.RESPONSE_HEADER_ID AND
65 RH.RESPONSE_HEADER_ID = RL.RESPONSE_HEADER_ID AND
66 UPPER(RH.EXPORT_COMPLIANCE_STATUS) = p_status;
67
68 -- Declaration Section For Log/close Exception Section
69
70 i number;
71
72 l_api_version NUMBER := 1.0;
73 l_return_status VARCHAR2(1);
74 l_msg_count NUMBER;
75 l_msg_data VARCHAR2(2000); --Bug 7125729:Increasing message buffer size
76 l_validation_level NUMBER default FND_API.G_VALID_LEVEL_FULL;
77
78 l_old_status VARCHAR2(30);
79 l_new_status VARCHAR2(30);
80 l_default_status VARCHAR2(1);
81
82 l_exception_message varchar2(2000);
83 l_exception_name varchar2(30);
84
85 l_location_id NUMBER;
86 l_delivery_id NUMBER;
87 l_delivery_name VARCHAR2(30);
88 l_exception_id NUMBER;
89 xx_exception_id NUMBER;
90
91 l_exception_found boolean := true;
92 l_event_name VARCHAR2(20);
93
94 --
95 l_process_flag NUMBER;
96
97 --for Workflow process
98 l_wf_event_name VARCHAR2(1000);
99 l_wf_return_status VARCHAR2(1);
100 l_organization_id NUMBER;
101
102 BEGIN
103
104
105 SAVEPOINT WSH_ITM_POST_COMPL;
106
107 -------------------------------------------------------------------------------
108 -- Post Processing Section
109 -------------------------------------------------------------------------------
110
111 -- This sub section checks if the process is completed
112 SELECT PROCESS_FLAG INTO L_PROCESS_FLAG
113 FROM WSH_ITM_REQUEST_CONTROL
114 WHERE REQUEST_CONTROL_ID = P_REQUEST_CONTROL_ID;
115
116
117
118 -- This Sub Section Fetches The Location Code,Delivery Name for Submitted Exception
119
120 BEGIN
121 SELECT WE.EXCEPTION_LOCATION_ID,
122 WE.DELIVERY_ID,
123 WE.DELIVERY_NAME,
124 WE.EXCEPTION_ID,
125 IRC.TRIGGERING_POINT,
126 WE.STATUS,
127 IRC.ORGANIZATION_ID
128 INTO l_location_id,
129 l_delivery_id,
130 l_delivery_name,
131 l_exception_id,
132 l_event_name,
133 l_old_status,
134 l_organization_id
135 FROM WSH_EXCEPTIONS WE ,
136 WSH_ITM_REQUEST_CONTROL IRC
137 WHERE WE.DELIVERY_ID = IRC.ORIGINAL_SYSTEM_REFERENCE
138 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)
139 AND IRC.REQUEST_CONTROL_ID = P_REQUEST_CONTROL_ID
140 AND WE.STATUS <> 'CLOSED';
141
142 --Raise Functional Event that could be customized by the Workflow
143 IF l_event_name= 'PICK_RELEASE' THEN
144 l_wf_event_name := G_WF_PICK_RELEASE_EVENT_NAME;
145 ELSIF l_event_name = 'SHIP_CONFIRM' THEN
146 l_wf_event_name := G_WF_SHIP_CONFIRM_EVENT_NAME;
147 END IF;
148
149 WSH_ITM_EXPORT_SCREENING.RAISE_ITM_EVENT
150 (
151 p_event_name => l_wf_event_name ,
152 p_delivery_id => l_delivery_id,
153 p_organization_id => l_organization_id,
154 x_return_status => l_wf_return_status
155 );
156
157 --End of Functional Event that could be customized by the Workflow
158 EXCEPTION
159 WHEN OTHERS THEN
160 RETURN;
161 END;
162
163 -- This Section Takes The Required Action Based on Process Flag and Compliance's Success
164 IF l_process_flag = 1 THEN
165
166 l_exception_message := NULL;
167
168 --Retreiving Type, Success and Description columns from the
169 -- Response lines table.
170 FOR l_nonComplRec IN C_EXP_COMPL_QUERY(p_request_control_id, 'NOT_COMPLIANT') LOOP
171 l_exception_message := l_exception_message||' - '||l_nonComplRec.COMPL_TYPE||
172 ' '|| l_nonComplRec.COMPL_STATUS ||
173 ' '||l_nonComplRec.COMPL_DESC;
174 END LOOP;
175 -- When Compliance Failed
176 IF l_exception_message is NOT NULL THEN
177 -- Sub Section Logs a new Exception For Compliance Failure
178 l_return_status := NULL;
179 l_msg_count := NULL;
180 l_msg_data := NULL;
181 xx_exception_id := NULL;
182 l_exception_name := G_FAIL_EXP_COMPL_EXCEPTION;
183
184 WSH_XC_UTIL.log_exception(
185 p_api_version => l_api_version,
186 p_init_msg_list => FND_API.G_FALSE,
187 p_commit => FND_API.G_FALSE,
188 p_validation_level => l_validation_level,
189 x_return_status => l_return_status,
190 x_msg_count => l_msg_count,
191 x_msg_data => l_msg_data,
192 x_exception_id => xx_exception_id,
193 p_exception_location_id => l_location_id,
194 p_logged_at_location_id => l_location_id,
195 p_logging_entity => 'SHIPPER',
196 p_logging_entity_id => l_delivery_id,
197 p_exception_name => l_exception_name,
198 p_message => l_exception_message,
199 p_delivery_id => l_delivery_id,
200 p_delivery_name => l_delivery_name
201 );
202
203 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
204
205
206 if l_msg_count IS NOT NULL then
207 WSH_UTIL_CORE.Add_Message(l_return_status);
208 for i in 1 ..l_msg_count loop
209 l_msg_data := FND_MSG_PUB.get(p_msg_index => i,
210 p_encoded => 'F');
211
212 end loop;
213 END IF;
214
215 -- Cleaning Operation
216 ROLLBACK TO WSH_ITM_POST_COMPL;
217
218
219 RETURN;
220 END IF;
221 END IF; -- End of Compliance Failure IF Block
222
223 --Bug 6371639: NO AUDIT TRAIL PROVIDED FOR SHIPMENTS SCREENED BY ITM FOR SUCCESSFULLY SCREENED REQUEST.
224 -- Commenting the below Delete Statements to retain the Records in WSH_ITM tables
225
226 /* delete from wsh_itm_response_lines where response_header_id IN
227 (
228 select response_header_id
229 from wsh_itm_response_headers
230 where request_control_id = p_request_control_id
231 );
232
233 delete from wsh_itm_response_headers where request_control_id = p_request_control_id;
234 delete from wsh_itm_request_control where request_control_id = p_request_control_id; */
235
236
237
238 ELSIF l_process_flag = 2 THEN
239 -- System And Data Error Handling
240 l_exception_message := NULL;
241 FOR l_errorRec IN C_EXP_COMPL_QUERY(p_request_control_id, 'ERROR') LOOP
242 l_exception_message := l_exception_message||' - '||l_errorRec.ERROR_CODE ||
243 ' '|| l_errorRec.ERROR_TYPE ||
244 ' '||l_errorRec.ERROR_TEXT;
245 END LOOP;
246
247 --Sub Section To Log a New Exception For System Or Data Error
248 l_return_status := NULL;
249 l_msg_count := NULL;
250 l_msg_data := NULL;
251 xx_exception_id := NULL;
252 l_exception_name := G_FAIL_EXP_COMPL_EXCEPTION;
253
254 WSH_XC_UTIL.log_exception(
255 p_api_version => l_api_version,
256 p_init_msg_list => FND_API.G_FALSE,
257 p_commit => FND_API.G_FALSE,
258 p_validation_level => l_validation_level,
259 x_return_status => l_return_status,
260 x_msg_count => l_msg_count,
261 x_msg_data => l_msg_data,
262 x_exception_id => xx_exception_id,
263 p_exception_location_id => l_location_id,
264 p_logged_at_location_id => l_location_id,
265 p_logging_entity => 'SHIPPER',
266 p_logging_entity_id => l_delivery_id,
267 p_exception_name => l_exception_name,
268 p_message => l_exception_message,
269 p_delivery_id => l_delivery_id,
270 p_delivery_name => l_delivery_name
271 );
272
273 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
274
275
276
277 if l_msg_count IS NOT NULL then
278 WSH_UTIL_CORE.Add_Message(l_return_status);
279 for i in 1 ..l_msg_count loop
280 l_msg_data := FND_MSG_PUB.get(p_msg_index => i,
281 p_encoded => 'F');
282
283 end loop;
284 end if;
285 -- Cleaning Operation
286 ROLLBACK TO WSH_ITM_POST_COMPL;
287
288 RETURN;
289 END IF;
290 END IF; -- End of Process Flag (If Block)
291 ----------------------------------------------------------------------------------------
292 -- Purges the data in ITM Request and Response Tables
293 ----------------------------------------------------------------------------------------
294
295 /*delete from wsh_itm_response_lines where response_header_id IN
296 (
297 select response_header_id
298 from wsh_itm_response_headers
299 where request_control_id = p_request_control_id
300 );
301 */
302 -- delete from wsh_itm_response_headers where request_control_id = p_request_control_id;
303 -- delete from wsh_itm_request_control where request_control_id = p_request_control_id;
304
305 ----------------------------------------------------------------------------------------
306 -- Handle Submitted For Export Screening Exception
307 ----------------------------------------------------------------------------------------
308 -- Section(Applies For Both Compliance Failure and Success) to Handle 'Submitted For Export Screening'
309 l_return_status := NULL;
310 l_msg_count := NULL;
311 l_msg_data := NULL;
312
313 l_new_status := 'CLOSED';
314 l_default_status := 'F';
315
316 IF l_old_status = 'CLOSED' THEN
317 RETURN;
318 END IF;
319
320 WSH_XC_UTIL.change_status (
321 p_api_version => l_api_version,
322 p_init_msg_list => FND_API.G_FALSE,
323 p_commit => FND_API.G_FALSE,
324 p_validation_level => l_validation_level,
325 x_return_status => l_return_status,
326 x_msg_count => l_msg_count,
327 x_msg_data => l_msg_data,
328 p_exception_id => l_exception_id,
329 p_old_status => l_old_status,
330 p_set_default_status => l_default_status,
331 x_new_status => l_new_status
332 );
333
334 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
335
336
337
338 if l_msg_count IS NOT NULL then
339 WSH_UTIL_CORE.Add_Message(l_return_status);
340 for i in 1 ..l_msg_count loop
341 l_msg_data := FND_MSG_PUB.get(p_msg_index => i,
342 p_encoded => 'F');
343
344 end loop;
345 end if;
346 -- Cleaning Operation
347 ROLLBACK TO WSH_ITM_POST_COMPL;
348 RETURN;
349
350 END IF;
351
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 ROLLBACK TO WSH_ITM_POST_COMPL;
356 END WSH_ITM_WSH;
357
358 END WSH_ITM_PKG;