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