[Home] [Help]
PACKAGE BODY: APPS.WSH_ITM_EXPORT_SCREENING
Source
1 PACKAGE BODY WSH_ITM_EXPORT_SCREENING AS
2 /* $Header: WSHITESB.pls 120.3 2007/12/26 10:41:27 sankarun ship $ */
3
4
5
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ITM_EXPORT_SCREENING';
7 G_REQ_PICK_RELEASE_EXCEPTION CONSTANT VARCHAR2(30) := 'WSH_PR_REQ_EXPORT_COMPL';
8 G_REQ_SHIP_CONFIRM_EXCEPTION CONSTANT VARCHAR2(30) := 'WSH_SC_REQ_EXPORT_COMPL';
9 G_SUB_PICK_RELEASE_EXCEPTION CONSTANT VARCHAR2(30) := 'WSH_PR_SUB_EXPORT_COMPL';
10 G_SUB_SHIP_CONFIRM_EXCEPTION CONSTANT VARCHAR2(30) := 'WSH_SC_SUB_EXPORT_COMPL';
11 G_APPLICATION_ID CONSTANT NUMBER := 665;
12 G_PICK_RELEASE_EVENT CONSTANT VARCHAR2(50) := 'PICK_RELEASE';
13 G_SHIP_CONFIRM_EVENT CONSTANT VARCHAR2(50) := 'SHIP_CONFIRM';
14 G_SERVICE_TYPE_CODE CONSTANT VARCHAR2(50) := 'WSH_EXPORT_COMPLIANCE';
15
16 --Workflow Global values
17 G_WF_ENTITY_TYPE CONSTANT VARCHAR2(30) := 'DELIVERY';
18 G_WF_PICK_RELEASE_EVENT_NAME CONSTANT VARCHAR2(100) :=
19 'oracle.apps.wsh.delivery.itm.submittedscreeningatdelcreate';
20 G_WF_SHIP_CONFIRM_EVENT_NAME CONSTANT VARCHAR2(100) :=
21 'oracle.apps.wsh.delivery.itm.submittedscreeningatship';
22
23
24 TYPE delivery_detail_rec_type IS RECORD
25 ( delivery_detail_id NUMBER ,
26 transaction_temp_id NUMBER ,
27 inventory_item_id NUMBER
28 );
29
30 --TYPE delivery_detail_tab_type IS TABLE OF delivery_detail_rec_type INDEX BY BINARY_INTEGER;
31
32 TYPE sn_range_rec_type IS RECORD
33 ( delivery_detail_id NUMBER ,
34 transaction_temp_id NUMBER ,
35 from_serial_number VARCHAR2(30),
36 to_serial_number VARCHAR2(30),
37 quantity NUMBER
38 );
39
40 --TYPE sn_range_tab_type IS TABLE OF sn_range_rec_type INDEX BY BINARY_INTEGER;
41
42
43 PROCEDURE PROCESS_SERIAL_NUMBERS(p_sn_range_rec_type IN sn_range_rec_type,
44 p_request_control_id IN NUMBER,
45 p_delivery_id IN NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2
47 )
48
49 IS
50
51 l_from_serial_num VARCHAR2(30);
52 l_to_serial_num VARCHAR2(30);
53 l_debug_on BOOLEAN;
54 l_real_serial_prefix VARCHAR2(30);
55 l_prefix_length NUMBER;
56 l_from_numeric NUMBER;
57 l_to_numeric NUMBER;
58 l_range_count NUMBER;
59 l_new_serial_number VARCHAR2(30);
60
61 l_module_name CONSTANT VARCHAR2(100) := G_PKG_NAME || '.' || 'PROCESS_SERIAL_NUMBERS';
62
63
64 BEGIN
65
66 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
67
68 IF l_debug_on IS NULL
69 THEN
70 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
71 END IF;
72 IF l_debug_on THEN
73 WSH_DEBUG_SV.log(l_module_name,'ENTERING '||l_module_name);
74 END IF;
75
76 IF p_sn_range_rec_type.from_serial_number IS NOT NULL THEN
77
78 l_from_serial_num := p_sn_range_rec_type.from_serial_number;
79 l_to_serial_num := p_sn_range_rec_type.to_serial_number;
80
81 IF l_debug_on THEN
82 WSH_DEBUG_SV.log(l_module_name,'l_from_serial_num ',l_from_serial_num);
83 END IF;
84
85 IF l_debug_on THEN
86 WSH_DEBUG_SV.log(l_module_name,'l_to_serial_num ',l_to_serial_num);
87 END IF;
88
89 l_real_serial_prefix := RTRIM(p_sn_range_rec_type.from_serial_number,
90 '0123456789');
91
92 IF l_debug_on THEN
93 WSH_DEBUG_SV.log(l_module_name,'l_real_serial_prefix ',l_real_serial_prefix);
94 END IF;
95
96 l_prefix_length := NVL(LENGTH(l_real_serial_prefix), 0);
97
98 IF l_debug_on THEN
99 WSH_DEBUG_SV.log(l_module_name,'l_prefix_length ',l_prefix_length);
100 END IF;
101
102 l_from_numeric := TO_NUMBER(SUBSTR(p_sn_range_rec_type.from_serial_number,
103 l_prefix_length + 1));
104
105 IF l_debug_on THEN
106 WSH_DEBUG_SV.log(l_module_name,'l_from_numeric ',l_from_numeric);
107 END IF;
108
109 l_to_numeric := TO_NUMBER(SUBSTR(p_sn_range_rec_type.to_serial_number,
110 l_prefix_length + 1));
111
112 IF l_debug_on THEN
113 WSH_DEBUG_SV.log(l_module_name,'l_to_numeric ',l_to_numeric);
114 END IF;
115
116 l_range_count := l_to_numeric - l_from_numeric ;
117
118 IF l_debug_on THEN
119 WSH_DEBUG_SV.log(l_module_name,'l_range_count ',l_range_count);
120 END IF;
121
122 -- If range count is zero, means from and to are same and no new
123 -- serial numbers need to be generated
124 IF l_range_count = 0 THEN
125
126 IF l_debug_on THEN
127 WSH_DEBUG_SV.log(l_module_name,'Inside l_range_count ',l_range_count);
128 END IF;
129 IF l_debug_on THEN
130 WSH_DEBUG_SV.log(l_module_name,'l_from_serial_num ',l_from_serial_num);
131 END IF;
132
133 -- Only one serial number needs to go in either From or to as both are same.
134 INSERT INTO WSH_ITM_SERIAL_NUMBERS
135 (request_control_id,
136 delivery_id,
137 delivery_detail_id,
138 serial_number)
139 VALUES
140 ( p_request_control_id,
141 p_delivery_id,
142 p_sn_range_rec_type.delivery_detail_id,
143 l_from_serial_num
144 );
145 ELSE
146 -- If range_count is > 0 then generate new serial numbers
147 -- starting with 0 to that range count
148 FOR i IN 0..l_range_count LOOP
149
150 l_new_serial_number := l_real_serial_prefix || LPAD(TO_CHAR(l_from_numeric+i),
151 LENGTH(p_sn_range_rec_type.from_serial_number)
152 - l_prefix_length, '0');
153
154 IF l_debug_on THEN
155 WSH_DEBUG_SV.log(l_module_name,'New Serial Number ',l_new_serial_number);
156 END IF;
157
158 INSERT INTO WSH_ITM_SERIAL_NUMBERS
159 (request_control_id,
160 delivery_id,
161 delivery_detail_id,
162 serial_number)
163 VALUES
164 ( p_request_control_id,
165 p_delivery_id,
166 p_sn_range_rec_type.delivery_detail_id,
167 l_new_serial_number
168 );
169 END LOOP;
170 END IF;
171 END IF;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 ROLLBACK TO WSH_ITM_EXPORT_COMPLIANCE;
176 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
177 IF l_debug_on THEN
178 WSH_DEBUG_SV.log(l_module_name,'The unexpected Error Code ' || SQLCODE || ' : ' || SQLERRM);
179 WSH_DEBUG_SV.pop(l_module_name);
180 END IF;
181
182 IF l_debug_on THEN
183 WSH_DEBUG_SV.log(l_module_name,'EXITING '||l_module_name);
184 END IF;
185
186
187 END PROCESS_SERIAL_NUMBERS;
188
189
190 PROCEDURE POPULATE_SERIAL_NUMBERS(
191 p_request_control_id IN NUMBER,
192 p_delivery_id IN NUMBER,
193 x_return_status OUT NOCOPY VARCHAR2
194 )
195 IS
196
197 l_debug_on BOOLEAN;
198
199 l_delv_detail_rec delivery_detail_rec_type;
200 l_sn_range_rec_type sn_range_rec_type;
201
202 CURSOR c_get_delivery_details(c_delivery_id IN NUMBER)
203 IS
204 SELECT wdd.delivery_detail_id, wdd.transaction_temp_id, wdd.inventory_item_id
205 FROM WSH_DELIVERY_DETAILS wdd,wsh_delivery_assignments wda
206 WHERE wda.delivery_id = c_delivery_id
207 AND wda.delivery_detail_id = wdd.delivery_detail_id;
208
209 CURSOR c_get_serial_num_range(c_transaction_temp_id IN NUMBER, c_delivery_detail_id IN NUMBER)
210 IS
211 SELECT wdd.delivery_detail_id,wdd.transaction_temp_id,mt.fm_serial_number,
212 mt.to_serial_number , to_number(mt.serial_prefix) quantity
213 FROM mtl_serial_numbers_temp mt , wsh_delivery_details wdd
214 WHERE mt.transaction_temp_id = c_transaction_temp_id
215 AND wdd.transaction_temp_id = mt.transaction_temp_id
216 AND wdd.delivery_detail_id = c_delivery_detail_id;
217
218 CURSOR c_get_serial_number_from_wdd(c_delivery_detail_id IN NUMBER)
219 IS
220 SELECT wdd.delivery_detail_id,null,wdd.serial_number,wdd.to_serial_number,shipped_quantity
221 FROM wsh_delivery_details wdd
222 WHERE wdd.delivery_detail_id = c_delivery_detail_id;
223
224 l_module_name CONSTANT VARCHAR2(100) := G_PKG_NAME || '.' || 'POPULATE_SERIAL_NUMBERS';
225
226 BEGIN
227
228 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
229
230 IF l_debug_on IS NULL
231 THEN
232 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
233 END IF;
234 IF l_debug_on THEN
235 WSH_DEBUG_SV.log(l_module_name,'ENTERING '||l_module_name);
236 END IF;
237
238
239
240 OPEN c_get_delivery_details(p_delivery_id);
241 LOOP
242 FETCH c_get_delivery_details INTO l_delv_detail_rec;
243 EXIT WHEN c_get_delivery_details%NOTFOUND;
244 IF l_debug_on THEN
245 WSH_DEBUG_SV.push(l_module_name);
246 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id',
247 l_delv_detail_rec.delivery_detail_id);
248 WSH_DEBUG_SV.log(l_module_name,'transaction_temp_id',
249 l_delv_detail_rec.transaction_temp_id);
250 END IF;
251
252 IF l_delv_detail_rec.transaction_temp_id IS NULL THEN
253 IF l_debug_on THEN
254 WSH_DEBUG_SV.log(l_module_name,'TRANSACTION_TEMP_ID IS NULL');
255 END IF;
256
257 OPEN c_get_serial_number_from_wdd (l_delv_detail_rec.delivery_detail_id);
258 LOOP
259 FETCH c_get_serial_number_from_wdd INTO l_sn_range_rec_type;
260
261 EXIT WHEN c_get_serial_number_from_wdd%NOTFOUND;
262 IF l_debug_on THEN
263 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id',
264 l_sn_range_rec_type.delivery_detail_id);
265 WSH_DEBUG_SV.log(l_module_name,'to_serial_number',
266 l_sn_range_rec_type.to_serial_number);
267 WSH_DEBUG_SV.log(l_module_name,'from_serial_number',
268 l_sn_range_rec_type.from_serial_number);
269 END IF;
270 PROCESS_SERIAL_NUMBERS(
271 p_sn_range_rec_type => l_sn_range_rec_type,
272 p_request_control_id => p_request_control_id,
273 p_delivery_id => p_delivery_id,
274 x_return_status => x_return_status);
275 END LOOP;
276 CLOSE c_get_serial_number_from_wdd;
277 ELSE
278 IF l_debug_on THEN
279 WSH_DEBUG_SV.log(l_module_name,'TRANSACTION_TEMP_ID IS NOT NULL');
280 END IF;
281
282 OPEN c_get_serial_num_range (l_delv_detail_rec.transaction_temp_id,l_delv_detail_rec.delivery_detail_id);
283 LOOP
284 FETCH c_get_serial_num_range INTO l_sn_range_rec_type;
285
286 EXIT WHEN c_get_serial_num_range%NOTFOUND;
287 IF l_debug_on THEN
288 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id',
289 l_sn_range_rec_type.delivery_detail_id);
290 WSH_DEBUG_SV.log(l_module_name,'to_serial_number',
291 l_sn_range_rec_type.to_serial_number);
292 WSH_DEBUG_SV.log(l_module_name,'from_serial_number',
293 l_sn_range_rec_type.from_serial_number);
294 END IF;
295 PROCESS_SERIAL_NUMBERS(
296 p_sn_range_rec_type => l_sn_range_rec_type,
297 p_request_control_id => p_request_control_id,
298 p_delivery_id => p_delivery_id,
299 x_return_status => x_return_status);
300 END LOOP;
301 CLOSE c_get_serial_num_range;
302 END IF;
303 END LOOP;
304 CLOSE c_get_delivery_details;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 ROLLBACK TO WSH_ITM_EXPORT_COMPLIANCE;
309 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
310 IF l_debug_on THEN
311 WSH_DEBUG_SV.log(l_module_name,'The unexpected Error Code ' || SQLCODE || ' : ' || SQLERRM);
312 WSH_DEBUG_SV.pop(l_module_name);
313 END IF;
314
315 IF l_debug_on THEN
316 WSH_DEBUG_SV.log(l_module_name,'EXITING '||l_module_name);
317 END IF;
318
319
320 END POPULATE_SERIAL_NUMBERS;
321
322
323
324 /*==========================================================================+
325 | PROCEDURE |
326 | SCREEN_EVENT_DELIVERIES |
327 | PARAMETERS |
328 | |
329 | x_return_status => This is updated with the process status which |
330 | could either be a success of warning or an error.|
331 | p_organization_id => This parameter is used to filter the deliveries |
332 | based on organization. |
333 | |
334 | p_delivery_from_id => This parameter indicates the starting of the |
335 | range of deliveries to be processed. |
336 | p_delivery_to_id => This parameter indicates the ending of the |
337 | range of deliveries to be processed. |
338 | p_event_name => This parameter indicates the event on which the |
339 | the export screening was initiated. |
340 | p_ship_method_code => This paramter indicates the ship method of the |
341 | delivery |
342 | p_pickup_date_from => This parameter indicates the initail pickup date | | |
343 | p_pickup_date_to => This parameter indicates the last pick up date |
344 | |
345 | p_event_name => This parameter indicates the event on which the |
346 | the export screening was initiated. |
347 | DESCRIPTION |
348 | This procedure is called For the deliveries of a |
349 | Specific event. It Logs and Handles Appropriate |
350 | Exceptions which hold the delivery until the export |
351 | screening is done for the delivery ad populates data into |
352 | WSH_ITM_REQUEST_CONTROL Table. |
353 | |
354 +===========================================================================*/
355
356 PROCEDURE SCREEN_EVENT_DELIVERIES (
357 x_return_status OUT NOCOPY VARCHAR2,
358 p_organization_id IN NUMBER,
359 p_delivery_from_id IN NUMBER,
360 p_delivery_to_id IN NUMBER,
361 p_event_name IN VARCHAR2,
362 p_ship_method_code IN VARCHAR2,
363 p_pickup_date_from IN VARCHAR2,
364 p_pickup_date_to IN VARCHAR2
365 )IS
366 -- Declaration Section For Log/close Exception Section
367
368 i NUMBER;
369 l_api_version NUMBER := 1.0;
370 l_return_status VARCHAR2(1);
371 l_msg_count NUMBER;
372 l_msg_data VARCHAR2(200);
373 l_exception_id NUMBER;
374 x_exception_id NUMBER;
375 l_old_status VARCHAR2(30);
376 l_new_status VARCHAR2(30);
377 l_default_status VARCHAR2(1);
378 l_validation_level NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL;
379 l_exception_message VARCHAR2(2000);
380 l_exception_name VARCHAR2(30);
381
382 l_CursorID NUMBER;
383 l_ignore NUMBER;
384 l_tempStr VARCHAR2(10000) := ' ';
385
386 --Declaration Section For Bulk Select PL/SQL Tables
387 l_num_exception_id_tab DBMS_SQL.Number_Table;
388 l_varchar_status_tab DBMS_SQL.Varchar2_Table;
389 l_num_location_id_tab DBMS_SQL.Number_Table;
390 l_num_delivery_id_tab DBMS_SQL.Number_Table;
391 l_varchar_delivery_name_tab DBMS_SQL.Varchar2_Table;
392
393 --AJPRABHA for inserting DATA into Transactions History.
394 l_tranx_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
395 x_txns_id NUMBER;
396 l_rec_found NUMBER DEFAULT 0;
397 l_request_control_id_s NUMBER;
398
399 -- Declaration Section for columns(Non - PL/SQL Table) used in Interface Table Population
400 l_user_id NUMBER;
401 l_login_id NUMBER;
402 l_LanguageCode VARCHAR2(20);
403
404 --Added for Raising tracking Workflows
405 l_wf_event_name VARCHAR2(1000);
406 l_wf_return_status VARCHAR2(1);
407 l_num_organization_id_tab DBMS_SQL.Number_Table;
408 l_parameter_list wf_parameter_list_t;
409 --BUG 6700736 Added variable to store master_organization_id
410 l_num_master_org_id_tab DBMS_SQL.Number_Table;
411
412 l_debug_on BOOLEAN;
413 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||'SCREEN_EVENT_DELIVERIES';
414
415 --Addded condition to get deliveries that contains items Bug Number 3411941
416 --BUG 6700736 Fetching master_organization_id to populate into wsh_itm_request_control
417 l_exp_compliance_dlvy_query VARCHAR2(1800) :=
418 ' SELECT '||
419 ' WE.EXCEPTION_ID AS EXCEPTION_ID, '||
420 ' WE.EXCEPTION_LOCATION_ID AS LOCATION_ID, '||
421 ' WND.DELIVERY_ID AS DELIVERY_ID, '||
422 ' WE.STATUS AS STATUS, '||
423 ' WND.NAME AS DELIVERY_NAME, '||
424 ' WND.ORGANIZATION_ID AS ORGANIZATION_ID, '||
425 ' MTL.MASTER_ORGANIZATION_ID AS MASTER_ORGANIZATION_ID '||
426 ' FROM '||
427 ' WSH_EXCEPTIONS WE , '||
428 ' WSH_NEW_DELIVERIES WND, '||
429 ' MTL_PARAMETERS MTL '||
430 ' WHERE '||
431 ' WE.STATUS <> ''CLOSED'' '||
432 ' AND WND.ORGANIZATION_ID = MTL.ORGANIZATION_ID '||
433 ' AND WND.DELIVERY_ID = WE.DELIVERY_ID '||
434 ' AND WND.DELIVERY_ID = (SELECT '||
435 ' WDA.DELIVERY_ID '||
436 ' FROM '||
437 ' WSH_DELIVERY_ASSIGNMENTS WDA '||
438 ' WHERE '||
439 ' WDA.DELIVERY_ID = WND.DELIVERY_ID '||
440 ' AND ROWNUM = 1) ' ;
441 l_Delivery_Table WSH_ITM_QUERY_CUSTOM.g_CondnValTableType;
442 l_Delivery_Condn1Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
443 l_Delivery_Condn2Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
444 l_Delivery_Condn3Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
445 l_Delivery_Condn4Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
446 l_Delivery_Condn5Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
447 l_Delivery_Condn6Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
448 l_Delivery_Condn7Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
449 l_Delivery_Condn71Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
450 l_Delivery_Condn8Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
451 l_Delivery_Condn9Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
452 BEGIN
453
454 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
455 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
456
457 IF l_debug_on IS NULL
458 THEN
459 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
460 END IF;
461
462 IF l_debug_on THEN
463 WSH_DEBUG_SV.push(l_module_name);
464 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
465 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_FROM_ID',p_delivery_from_id);
466 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_TO_ID',p_delivery_to_id);
467 WSH_DEBUG_SV.log(l_module_name,'P_EVENT_NAME',p_event_name);
468 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',p_ship_method_code);
469 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_DATE_FROM',p_pickup_date_from);
470 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_DATE_TO',p_pickup_date_to);
471 END IF;
472
473 -------------------------------------------------------------------------------
474 -- Pickup Deliveries which require export screening
475 -------------------------------------------------------------------------------
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.log(l_module_name,'This Section Picks up the Deliveries Which Require Screeenig For '||p_event_name||' event ');
478 END IF;
479
480 -- This sub section prepares the query to pick up the Deliveries which require screening
481 IF p_event_name = G_PICK_RELEASE_EVENT THEN
482 l_Delivery_Condn1Tab(1).g_varchar_val := G_REQ_PICK_RELEASE_EXCEPTION;
483 l_Delivery_Condn1Tab(1).g_Bind_Literal := ':b_req_pick_release';
484 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Delivery_Table, ' AND WE.EXCEPTION_NAME = :b_req_pick_release', l_Delivery_Condn1Tab, 'VARCHAR');
485 ELSIF p_event_name = G_SHIP_CONFIRM_EVENT THEN
486 l_Delivery_Condn2Tab(1).g_varchar_val := G_REQ_SHIP_CONFIRM_EXCEPTION;
487 l_Delivery_Condn2Tab(1).g_Bind_Literal := ':b_req_ship_confirm';
488 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Delivery_Table, ' AND WE.EXCEPTION_NAME = :b_req_ship_confirm', l_Delivery_Condn2Tab, 'VARCHAR');
489 END IF;
490
491 IF p_organization_id is not null THEN
492 l_Delivery_Condn3Tab(1).g_number_val := p_organization_id;
493 l_Delivery_Condn3Tab(1).g_Bind_Literal := ':b_org_id';
494 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Delivery_Table, ' AND WND.ORGANIZATION_ID = :b_org_id', l_Delivery_Condn3Tab, 'NUMBER');
495 END IF;
496
497 IF p_delivery_from_id is not null THEN
498 l_Delivery_Condn4Tab(1).g_number_val := p_delivery_from_id;
499 l_Delivery_Condn4Tab(1).g_Bind_Literal := ':b_delivery_from_id';
500 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Delivery_Table, ' AND WE.DELIVERY_ID >= :b_delivery_from_id', l_Delivery_Condn4Tab, 'NUMBER');
501 END IF;
502
503
504 IF p_delivery_to_id is not null THEN
505 l_Delivery_Condn5Tab(1).g_number_val := p_delivery_to_id;
506 l_Delivery_Condn5Tab(1).g_Bind_Literal := ':b_delivery_to_id';
507 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Delivery_Table, ' AND WE.DELIVERY_ID <= :b_delivery_to_id', l_Delivery_Condn5Tab, 'NUMBER');
508 END IF;
509
510
511 IF p_ship_method_code is not null THEN
512 l_Delivery_Condn6Tab(1).g_varchar_val := p_ship_method_code;
513 l_Delivery_Condn6Tab(1).g_Bind_Literal := ':b_ship_method_code';
514 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Delivery_Table, ' AND WND.SHIP_METHOD_CODE = :b_ship_method_code', l_Delivery_Condn6Tab, 'VARCHAR');
515 END IF;
516
517 IF p_pickup_date_from is not null THEN
518 l_Delivery_Condn8Tab(1).g_date_val := FND_DATE.CANONICAL_TO_DATE(p_pickup_date_from);
519 l_Delivery_Condn8Tab(1).g_Bind_Literal := ':b_pickup_date_from';
520 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Delivery_Table, ' AND WND.INITIAL_PICKUP_DATE >= :b_pickup_date_from', l_Delivery_Condn8Tab, 'DATE');
521 END IF;
522
523 IF p_pickup_date_to is not null THEN
524 l_Delivery_Condn9Tab(1).g_date_val := FND_DATE.CANONICAL_TO_DATE(p_pickup_date_to);
525 l_Delivery_Condn9Tab(1).g_Bind_Literal := ':b_pickup_date_to';
526 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Delivery_Table, ' AND WND.INITIAL_PICKUP_DATE <= :b_pickup_date_to', l_Delivery_Condn9Tab, 'DATE');
527 END IF;
528
529 FOR I IN 1..l_Delivery_Table.COUNT
530 LOOP
531 l_tempStr := l_tempStr || ' ' || l_Delivery_table(i).g_Condn_Qry;
532 END LOOP;
533
534
535
536 l_exp_compliance_dlvy_query := l_exp_compliance_dlvy_query||l_tempStr||' ';
537 IF l_debug_on THEN
538 WSH_DEBUG_SV.log(l_module_name,' The Query executed ',l_exp_compliance_dlvy_query);
539 END IF;
540
541
542
543 -- This sub section executes the query and collects the result set into PL/SQL Tables
544
545 l_CursorID := DBMS_SQL.Open_Cursor;
546 DBMS_SQL.PARSE(l_CursorID, l_exp_compliance_dlvy_query, DBMS_SQL.v7);
547 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 1, l_num_exception_id_tab, 100, 0);
548 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 2, l_num_location_id_tab, 100, 0);
549 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 3, l_num_delivery_id_tab, 100, 0);
550 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 4, l_varchar_status_tab, 100, 0);
551 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 6, l_num_organization_id_tab, 100, 0);
552 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 5, l_varchar_delivery_name_tab, 100, 0);
553 --Bug 6700736 Defined array for master_organization_id
554 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 7, l_num_master_org_id_tab, 100, 0);
555
556 WSH_ITM_QUERY_CUSTOM.BIND_VALUES(l_Delivery_Table,l_CursorID);
557 l_ignore := DBMS_SQL.EXECUTE(l_CursorID);
558
559
560 LOOP
561 l_ignore := DBMS_SQL.FETCH_ROWS(l_CursorID);
562 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1,l_num_exception_id_tab);
563 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2,l_num_location_id_tab);
564 DBMS_SQL.COLUMN_VALUE(l_CursorID, 3,l_num_delivery_id_tab );
565 DBMS_SQL.COLUMN_VALUE(l_CursorID, 4,l_varchar_status_tab);
566 DBMS_SQL.COLUMN_VALUE(l_CursorID, 5,l_varchar_delivery_name_tab );
567 DBMS_SQL.COLUMN_VALUE(l_CursorID, 6, l_num_organization_id_tab);
568 --Bug 6700736 Associated array variables master_organization_id
569 DBMS_SQL.COLUMN_VALUE(l_CursorID, 7,l_num_master_org_id_tab );
570 EXIT WHEN l_ignore <> 100;
571 END LOOP;
572
573 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
574
575 IF l_debug_on THEN
576 WSH_DEBUG_SV.log(l_module_name,' No Of Deliveries Selected For Screening ',l_num_delivery_id_tab.count);
577 END IF;
578
579 IF l_num_delivery_id_tab.count < 1 THEN
580 IF l_debug_on THEN
581 WSH_DEBUG_SV.pop(l_module_name);
582 END IF;
583 RETURN;
584 END IF;
585
586
587 FOR j in l_num_delivery_id_tab.first .. l_num_delivery_id_tab.last LOOP
588 ---------------------------------------------------------------------------------
589 -- Handling Require Export Screening Exceptions
590 ---------------------------------------------------------------------------------
591 IF l_debug_on THEN
592 WSH_DEBUG_SV.log(l_module_name,'This Section Handles The REQUIRE Export Screening Exception for'||p_event_name||' event ');
593 END IF;
594
595 l_return_status := NULL;
596 l_msg_count := NULL;
597 l_msg_data := NULL;
598 l_exception_id := l_num_exception_id_tab(j);
599 l_old_status := l_varchar_status_tab(j);
600 l_new_status := 'CLOSED';
601 l_default_status := 'F';
602
603 WSH_XC_UTIL.change_status (
604 p_api_version => l_api_version,
605 p_init_msg_list => FND_API.g_false,
606 p_commit => FND_API.g_false,
607 p_validation_level => l_validation_level,
608 x_return_status => l_return_status,
609 x_msg_count => l_msg_count,
610 x_msg_data => l_msg_data,
611 p_exception_id => l_exception_id,
612 p_old_status => l_old_status,
613 p_set_default_status => l_default_status,
614 x_new_status => l_new_status
615 );
616
617 -- Error Handling Section
618
619 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
620 IF l_debug_on THEN
621 WSH_DEBUG_SV.log(l_module_name,' Error Handling the exception for Delivery Id : '||l_num_delivery_id_tab(j));
622 END IF;
623
624 IF l_msg_count IS NOT NULL THEN
625 WSH_UTIL_CORE.Add_Message(l_return_status);
626 FOR i IN 1 ..l_msg_count LOOP
627 l_msg_data := FND_MSG_PUB.get
628 (
629 p_msg_index => i,
630 p_encoded => 'F'
631 );
632 IF l_debug_on THEN
633 WSH_DEBUG_SV.log(l_module_name,l_msg_data);
634 END IF;
635 END LOOP;
636 END IF;
637
638 -- Cleaning Operation
639 ROLLBACK TO WSH_ITM_EXPORT_COMPLIANCE;
640
641 x_return_status := l_return_status;
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.pop(l_module_name);
644 END IF;
645 RETURN;
646 END IF;
647
648 --------------------------------------------------------------------------------------------------------
649 -- Logging exceptions for Deliveries submitted for export screening
650 ---------------------------------------------------------------------------------------------------------
651
652 IF l_debug_on THEN
653 WSH_DEBUG_SV.log(l_module_name,'This Section Logs a Submitted For Export Screening For Delivery which Require Export Screening and Handles the Existing Require Export Screening Exceptions for '||p_event_name||' event ');
654 END IF;
655
656 l_return_status := NULL;
657 l_msg_count := NULL;
658 l_msg_data := NULL;
659 l_exception_id := NULL;
660 l_exception_name := NULL;
661 l_exception_message := 'Delivery has been submitted for export screening';
662
663 IF p_event_name = G_PICK_RELEASE_EVENT THEN
664 l_exception_name := G_SUB_PICK_RELEASE_EXCEPTION;
665 l_wf_event_name := G_WF_PICK_RELEASE_EVENT_NAME;
666 ELSIF p_event_name = G_SHIP_CONFIRM_EVENT THEN
667 l_exception_name := G_SUB_SHIP_CONFIRM_EXCEPTION;
668 l_wf_event_name := G_WF_SHIP_CONFIRM_EVENT_NAME;
669 END IF;
670
671
672 WSH_XC_UTIL.log_exception(
673 p_api_version => l_api_version,
674 p_init_msg_list => FND_API.g_false,
675 p_commit => FND_API.g_false,
676 p_validation_level => l_validation_level,
677 x_return_status => l_return_status,
678 x_msg_count => l_msg_count,
679 x_msg_data => l_msg_data,
680 x_exception_id => l_exception_id,
681 p_exception_location_id => l_num_location_id_tab(j),
682 p_logged_at_location_id => l_num_location_id_tab(j),
683 p_logging_entity => 'SHIPPER',
684 p_logging_entity_id => FND_GLOBAL.USER_ID,
685 p_exception_name => l_exception_name,
686 p_message => l_exception_message,
687 p_delivery_id => l_num_delivery_id_tab(j),
688 p_delivery_name => l_varchar_delivery_name_tab(j)
689 );
690
691 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
692
693 IF l_debug_on THEN
694 WSH_DEBUG_SV.log(l_module_name,' Error Logging the exception for Delivery Id : ',l_num_delivery_id_tab(j));
695 END IF;
696
697 IF l_msg_count IS NOT NULL THEN
698 WSH_UTIL_CORE.Add_Message(l_return_status);
699 FOR i in 1 ..l_msg_count LOOP
700 l_msg_data := FND_MSG_PUB.get
701 (
702 p_msg_index => i,
703 p_encoded => 'F'
704 );
705 IF l_debug_on THEN
706 WSH_DEBUG_SV.log(l_module_name,l_msg_data);
707 END IF;
708 END LOOP;
709 END IF;
710
711 -- Cleaning Operation
712 ROLLBACK TO WSH_ITM_EXPORT_COMPLIANCE;
713
714 x_return_status := l_return_status;
715 IF l_debug_on THEN
716 WSH_DEBUG_SV.pop(l_module_name);
717 END IF;
718 RETURN;
719 END IF;
720
721 RAISE_ITM_EVENT
722 (
723 p_event_name => l_wf_event_name ,
724 p_delivery_id => l_num_delivery_id_tab(j),
725 p_organization_id => l_num_organization_id_tab(j),
726 x_return_status => l_wf_return_status
727 );
728
729 IF l_debug_on THEN
730 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_return_status);
731 END IF;
732
733 END LOOP;
734
735 -------------------------------------------------------------------------------
736 -- Populates Deliveries Into ITM Request Control Tables
737 -------------------------------------------------------------------------------
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.log(l_module_name,'This Section Populates The Delivery Information Into Request Control Table for '||p_event_name||' event ');
740 END IF;
741
742 -- This sub section Prepares the data to be inserted into ITM Request Control Table
743 -- Fetch user and login information
744 l_user_id := FND_GLOBAL.USER_ID;
745 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
746
747 -- Getting the Base Language into the variable
748
749 SELECT LANGUAGE_CODE INTO l_LanguageCode FROM
750 FND_LANGUAGES WHERE INSTALLED_FLAG = 'B';
751 -- AJPRABHA INSERTING Records INTO TransactionsHistory Table
752 -- Check for existing records
753 -- If NO insert rec by using API
754
755 FOR k IN l_num_delivery_id_tab.FIRST .. l_num_delivery_id_tab.LAST LOOP
756
757 --Checking if record Exists for this delivery in
758 -- Transactions Hsitory Table
759 BEGIN
760 SELECT 1 INTO l_rec_found
761 FROM WSH_TRANSACTIONS_HISTORY
762 WHERE DOCUMENT_TYPE = 'SS' AND
763 ENTITY_NUMBER = l_varchar_delivery_name_tab(k);
764
765 EXCEPTION
766 WHEN NO_DATA_FOUND THEN
767 NULL;
768 END;
769 IF l_debug_on THEN
770 WSH_DEBUG_SV.log(l_module_name,'Records found for delivery ' || l_rec_found);
771 END IF;
772
773 IF (l_rec_found = 0) THEN
774 -- Get the ORGANIZATION_IF of the delivery
775 -- and setting the TRADING_PARTNER_ID.
776 BEGIN
777 SELECT ORGANIZATION_ID INTO l_tranx_history_rec.trading_partner_id
778 FROM WSH_NEW_DELIVERIES
779 WHERE DELIVERY_ID = l_num_delivery_id_tab(k);
780
781 EXCEPTION
782 WHEN NO_DATA_FOUND THEN
783 NULL;
784 END;
785 --
786 l_tranx_history_rec.document_type := 'SS'; --ShipmentScreening
787 l_tranx_history_rec.document_direction := 'O'; --Outbound
788 l_tranx_history_rec.transaction_status := 'ST'; --Sent to
789 l_tranx_history_rec.entity_type := 'DLVY'; --Delivery
790 l_tranx_history_rec.entity_number := l_varchar_delivery_name_tab(k);--DELIVERY NUMBER
791 l_tranx_history_rec.action_type := 'A'; --Sending new Msg
792
793 SELECT WSH_DOCUMENT_NUMBER_S.NEXTVAL
794 INTO l_tranx_history_rec.document_number FROM DUAL;
795
796 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History(
797 l_tranx_history_rec,
798 x_txns_id,
799 x_return_status
800 );
801 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
802 ROLLBACK TO WSH_ITM_EXPORT_COMPLIANCE;
803 IF l_debug_on THEN
804 WSH_DEBUG_SV.log(l_module_name,'Create_Update_Txns_History failed ' || x_return_status);
805 WSH_DEBUG_SV.pop(l_module_name);
806 END IF;
807 RETURN;
808 END IF;
809
810 IF l_debug_on THEN
811 WSH_DEBUG_SV.log(l_module_name,'Created Transaction for Delivery txns_id = ' || x_txns_id );
812 END IF;
813 END IF;
814 END LOOP;
815
816 -------------------------------------------------------------
817
818 -- This Sub Section Does the Bulk Insert to Request Control Table
819 FOR k IN l_num_delivery_id_tab.FIRST .. l_num_delivery_id_tab.LAST
820 LOOP
821 SELECT WSH_ITM_REQUEST_CONTROL_S.NEXTVAL
822 INTO l_request_control_id_s FROM DUAL;
823 --Bug 6700736 Populating master_organization_id
824 INSERT INTO WSH_ITM_REQUEST_CONTROL(
825 REQUEST_CONTROL_ID,
826 APPLICATION_ID,
827 APPLICATION_USER_ID,
828 SERVICE_TYPE_CODE,
829 TRANSACTION_DATE,
830 ORIGINAL_SYSTEM_REFERENCE,
831 PROCESS_FLAG,
832 CREATION_DATE,
833 CREATED_BY,
834 LAST_UPDATED_BY,
835 LAST_UPDATE_DATE,
836 LAST_UPDATE_LOGIN,
837 LANGUAGE_CODE,
838 TRIGGERING_POINT,
839 ORGANIZATION_ID,
840 MASTER_ORGANIZATION_ID
841 )
842 VALUES(
843 l_request_control_id_s,
844 G_APPLICATION_ID,
845 l_user_id,
846 G_SERVICE_TYPE_CODE,
847 SYSDATE,
848 l_num_delivery_id_tab(k),
849 0,
850 SYSDATE,
851 l_user_id,
852 l_user_id,
853 SYSDATE,
854 l_login_id,
855 l_LanguageCode,
856 p_event_name,
857 l_num_organization_id_tab(k),
858 l_num_master_org_id_tab(k)
859 );
860 WSH_ITM_EXPORT_SCREENING.POPULATE_SERIAL_NUMBERS(l_request_control_id_s
861 ,l_num_delivery_id_tab(k)
862 , x_return_status);
863
864 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
865 ROLLBACK TO WSH_ITM_EXPORT_COMPLIANCE;
866 IF l_debug_on THEN
867 WSH_DEBUG_SV.log(l_module_name,'Create_Serial_numbers_failed ' || x_return_status);
868 WSH_DEBUG_SV.pop(l_module_name);
869 END IF;
870 RETURN;
871 END IF;
872
873 IF l_debug_on THEN
874 WSH_DEBUG_SV.pop(l_module_name);
875 END IF;
876 IF l_debug_on THEN
877 WSH_DEBUG_SV.log(l_module_name,'Before Calling WSH_ITM_CUSTOM_PROCESS.PRE_PROCESS_WSH_REQUEST');
878 END IF;
879
880 WSH_ITM_CUSTOM_PROCESS.PRE_PROCESS_WSH_REQUEST
881 (
882 p_request_control_id => l_request_control_id_s
883 );
884
885 IF l_debug_on THEN
886 WSH_DEBUG_SV.log(l_module_name,'After Calling WSH_ITM_CUSTOM_PROCESS.PRE_PROCESS_WSH_REQUEST');
887 END IF;
888 END LOOP;
889
890 EXCEPTION
891 WHEN OTHERS THEN
892
893
894 ROLLBACK TO WSH_ITM_EXPORT_COMPLIANCE;
895 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
896 IF l_debug_on THEN
897 WSH_DEBUG_SV.log(l_module_name,'The unexpected Error Code ' || SQLCODE || ' : ' || SQLERRM);
898 WSH_DEBUG_SV.pop(l_module_name);
899 END IF;
900
901 END SCREEN_EVENT_DELIVERIES;
902
903
904 /*==========================================================================+
905 | PROCEDURE |
906 | RAISE_ITM_EVENT |
907 | PARAMETERS |
908 | |
909 | p_event_name => The WF event name that has to be raised |
910 | by the procedure |
911 | p_organization_id => This parameter is used to indiate the organization|
912 | of the delivery. |
913 | |
914 | p_delivery_id => This delivery for which the workflow |
915 | event has to be raised |
916 | |
917 | DESCRIPTION |
918 | This procedure is called when the concurrent program is |
919 | Launched. It is invoked by the screen_event_deliveries |
920 | Procedure |
921 | |
922 +===========================================================================
923 */
924
925
926 PROCEDURE RAISE_ITM_EVENT(
927 p_event_name IN VARCHAR2,
928 p_delivery_id IN NUMBER,
929 p_organization_id IN NUMBER,
930 x_return_status OUT NOCOPY VARCHAR2
931 ) IS
932 l_parameter_list wf_parameter_list_t;
933 l_debug_on BOOLEAN;
934 --
935 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||'RAISE_ITM_EVENT';
936
937 BEGIN
938 SAVEPOINT RAISE_ITM_EVENT;
939
940 --
941 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
942 --
943 IF l_debug_on IS NULL THEN
944 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
945 END IF;
946
947 IF l_debug_on THEN
948 WSH_DEBUG_SV.push(l_module_name);
949 WSH_DEBUG_SV.log(l_module_name,'P_EVENT_NAME',p_event_name);
950 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',p_delivery_id);
951 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
952 END IF;
953
954 wf_event.AddParameterToList(
955 p_name=>'ORGANIZATION_ID',
956 p_value => p_organization_id,
957 p_parameterlist=> l_parameter_list);
958
959 WSH_WF_STD.raise_event(
960 p_entity_type => G_WF_ENTITY_TYPE,
961 p_entity_id => p_delivery_id,
962 p_event => p_event_name,
963 p_parameters => l_parameter_list,
964 p_organization_id => p_organization_id,
965 x_return_status => x_return_status);
966
967 EXCEPTION
968 WHEN OTHERS THEN
969 ROLLBACK TO RAISE_ITM_EVENT;
970 IF l_debug_on THEN
971 WSH_DEBUG_SV.log(l_module_name,'The unexpected Error Code ' || SQLCODE || ' : ' || SQLERRM);
972 WSH_DEBUG_SV.pop(l_module_name);
973 END IF;
974 END RAISE_ITM_EVENT;
975
976
977 /*==========================================================================+
978 | PROCEDURE |
979 | SCREEN_DELIVERIES |
980 | PARAMETERS |
981 | |
982 | ret_code => This is updated with the process status which |
983 | could either be a success or error. |
984 | p_organization_id => This parameter is used to filter the deliveries |
985 | based on organization. |
986 | |
987 | p_delivery_from_id => This parameter indicates the starting of the |
988 | range of deliveries to be processed. |
989 | p_delivery_to_id => This parameter indicates the ending of the |
990 | range of deliveries to be processed. |
991 | |
992 | DESCRIPTION |
993 | This procedure is called when the concurrent program is |
994 | Launched. It invokes the screen_event_deliveries Procedure |
995 | for handling the export screening requests for both |
996 | Pick Release and Ship Conifirm Events. |
997 | |
998 +===========================================================================*/
999
1000
1001
1002
1003 PROCEDURE SCREEN_DELIVERIES (
1004 errbuf OUT NOCOPY VARCHAR2,
1005 retcode OUT NOCOPY NUMBER,
1006 p_organization_id IN NUMBER,
1007 p_delivery_from_id IN NUMBER,
1008 p_delivery_to_id IN NUMBER,
1009 p_ship_method_code IN VARCHAR2,
1010 p_pickup_date_from IN VARCHAR2,
1011 p_pickup_date_to IN VARCHAR2
1012 )IS
1013
1014 l_return_status VARCHAR2(1);
1015 l_temp BOOLEAN;
1016 l_debug_on BOOLEAN;
1017 --
1018 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||'SCREEN_DELIVERIES';
1019
1020 BEGIN
1021 SAVEPOINT WSH_ITM_EXPORT_COMPLIANCE;
1022
1023 --
1024 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1025 --
1026 IF l_debug_on IS NULL THEN
1027 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1028 END IF;
1029
1030 IF l_debug_on THEN
1031 WSH_DEBUG_SV.push(l_module_name);
1032 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION ID',p_organization_id);
1033 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_FROM_ID',p_delivery_from_id);
1034 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_TO_ID',p_delivery_to_id);
1035 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',p_ship_method_code);
1036 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_DATE_FROM',p_pickup_date_from);
1037 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_DATE_TO',p_pickup_date_to);
1038 END IF;
1039
1040 ------------------------------------------------------------------------------------------
1041 -- Performs Export Screening For Deliveries on Pick Release Event
1042 ------------------------------------------------------------------------------------------
1043 l_return_status := NULL;
1044
1045 WSH_ITM_EXPORT_SCREENING.SCREEN_EVENT_DELIVERIES(l_return_status,p_organization_id,p_delivery_from_id,p_delivery_to_id,G_PICK_RELEASE_EVENT,p_ship_method_code,p_pickup_date_from,p_pickup_date_to);
1046
1047
1048 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1049 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error in procedure WSH_ITM_EXPORT_SCREENING.SCREEN_DELIVERIES for Pick Release event ');
1050 retcode := 2;
1051 IF l_debug_on THEN
1052 WSH_DEBUG_SV.pop(l_module_name);
1053 END IF;
1054 RETURN;
1055 END IF;
1056
1057 ------------------------------------------------------------------------------------------
1058 -- Performs Export Screening For Deliveries on Ship Confirm Event
1059 ------------------------------------------------------------------------------------------
1060 l_return_status := NULL;
1061
1062 WSH_ITM_EXPORT_SCREENING.SCREEN_EVENT_DELIVERIES(l_return_status,p_organization_id,p_delivery_from_id,p_delivery_to_id,G_SHIP_CONFIRM_EVENT,p_ship_method_code,p_pickup_date_from,p_pickup_date_to);
1063
1064 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1065 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error in procedure WSH_ITM_EXPORT_SCREENING.SCREEN_DELIVERIES for Ship Confirm event ');
1066 retcode := 2;
1067 IF l_debug_on THEN
1068 WSH_DEBUG_SV.pop(l_module_name);
1069 END IF;
1070 RETURN;
1071 END IF;
1072
1073 IF l_debug_on THEN
1074 WSH_DEBUG_SV.pop(l_module_name);
1075 END IF;
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079
1080 ROLLBACK TO WSH_ITM_EXPORT_COMPLIANCE;
1081 IF l_debug_on THEN
1082 WSH_DEBUG_SV.log(l_module_name,'The unexpected Error Code ' || SQLCODE || ' : ' || SQLERRM);
1083 END IF;
1084 retcode := 2;
1085
1086 IF l_debug_on THEN
1087 WSH_DEBUG_SV.pop(l_module_name);
1088 END IF;
1089
1090 END SCREEN_DELIVERIES;
1091
1092
1093 END WSH_ITM_EXPORT_SCREENING;