[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIPMENT_ADVICE_PKG
Source
1 PACKAGE BODY WSH_SHIPMENT_ADVICE_PKG AS
2 /* $Header: WSHSAPKB.pls 120.1 2011/12/21 10:12:56 skanduku noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_SHIPMENT_ADVICE_PKG';
5 g_interface_action_code WSH_NEW_DEL_INTERFACE.INTERFACE_ACTION_CODE%TYPE := '94X_INBOUND';
6
7 --
8 --=============================================================================
9 -- PUBLIC PROCEDURE :
10 -- Shipment_Advice_Inbound
11 --
12 -- PARAMETERS:
13 -- errbuf => Message returned to Concurrent Manager
14 -- retcode => Code (0, 1, 2) returned to Concurrent Manager
15 -- p_transaction_status => Either AP, ER, NULL
16 -- p_from_document_number => From Document Number
17 -- p_to_document_number => To Document Number
18 -- p_from_creation_date => From Creation Date
19 -- p_to_creation_date => To Creation Date
20 -- p_transaction_id => Transacation id to be processed
21 -- p_log_level => Either 1(Debug), 0(No Debug)
22 -- COMMENT:
23 -- API will be invoked from Concurrent Manager whenever concurrent program
24 -- 'Process Shipment Advices' is triggered.
25 --=============================================================================
26 --
27 PROCEDURE Shipment_Advice_Inbound (
28 errbuf OUT NOCOPY VARCHAR2,
29 retcode OUT NOCOPY NUMBER,
30 p_transaction_status IN VARCHAR2,
31 p_from_document_number IN VARCHAR2,
32 p_to_document_number IN VARCHAR2,
33 p_from_creation_date IN VARCHAR2,
34 p_to_creation_date IN VARCHAR2,
35 p_transaction_id IN NUMBER,
36 p_log_level IN NUMBER )
37 IS
38 l_completion_status VARCHAR2(30);
39 l_return_status VARCHAR2(1);
40
41 l_debug_on BOOLEAN;
42 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Shipment_Advice_Inbound';
43 BEGIN
44 --
45 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
46 WSH_UTIL_CORE.Set_Log_Level(p_log_level);
47 --
48 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
49 --
50 IF l_debug_on IS NULL
51 THEN
52 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
53 END IF;
54 --
55 IF l_debug_on THEN
56 wsh_debug_sv.push(l_module_name);
57 wsh_debug_sv.log(l_module_name, 'p_transaction_status', p_transaction_status);
58 wsh_debug_sv.log(l_module_name, 'p_from_document_number', p_from_document_number);
59 wsh_debug_sv.log(l_module_name, 'p_to_document_number', p_to_document_number);
60 wsh_debug_sv.log(l_module_name, 'p_from_creation_date', p_from_creation_date);
61 wsh_debug_sv.log(l_module_name, 'p_to_creation_date', p_to_creation_date);
62 wsh_debug_sv.log(l_module_name, 'p_transaction_id', p_transaction_id);
63 wsh_debug_sv.log(l_module_name, 'p_log_level', p_log_level);
64 END IF;
65 --
66
67 --
68 IF l_debug_on THEN
69 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Process_Shipment_Advice', WSH_DEBUG_SV.C_PROC_LEVEL);
70 END IF;
71 --
72
73 Process_Shipment_Advice(
74 p_commit_flag => FND_API.G_TRUE,
75 p_transaction_status => p_transaction_status,
76 p_from_document_number => p_from_document_number,
77 p_to_document_number => p_to_document_number,
78 p_from_creation_date => p_from_creation_date,
79 p_to_creation_date => p_to_creation_date,
80 p_transaction_id => p_transaction_id,
81 x_return_status => l_return_status );
82
83 --
84 IF l_debug_on THEN
85 WSH_DEBUG_SV.log(l_module_name, 'Return Status of Process_Shipment_Advice', l_return_status);
86 END IF;
87 --
88
89
90 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
91 l_completion_status := 'SUCCESS';
92 errbuf := 'Process Shipment Advices Program has completed successfully';
93 retcode := '0';
94 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
95 l_completion_status := 'WARNING';
96 errbuf := 'Process Shipment Advices Program has completed with warning';
97 retcode := '1';
98 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
99 l_completion_status := 'ERROR';
100 errbuf := 'Process Shipment Advices Program has completed with error';
101 retcode := '2';
102 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
103 l_completion_status := 'UNEXPECTED ERROR';
104 errbuf := 'Process Shipment Advices Program has completed with unexpected error';
105 retcode := '2';
106 END IF;
107
108 --
109 IF l_debug_on THEN
110 WSH_DEBUG_SV.log(l_module_name,'l_completion_status', l_completion_status);
111 WSH_DEBUG_SV.log(l_module_name,'errbuf', errbuf);
112 WSH_DEBUG_SV.log(l_module_name,'retcode', retcode);
113 WSH_DEBUG_SV.pop(l_module_name);
114 END IF;
115 --
116 EXCEPTION
117 WHEN OTHERS THEN
118 l_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
119 errbuf := 'Process Shipment Advices Program is completed with unexpected error - ' || SQLCODE;
120 retcode := '2';
121 --
122 IF l_debug_on THEN
123 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
124 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
125 END IF;
126 --
127 END Shipment_Advice_Inbound;
128 --
129 --=============================================================================
130 -- PUBLIC PROCEDURE :
131 -- Process_Shipment_Advice
132 --
133 -- PARAMETERS:
134 -- p_commit_flag => Either FND_API.G_TRUE, FND_API.G_FALSE
135 -- p_transaction_status => Either AP, ER, NULL
136 -- p_from_document_number => From Document Number
137 -- p_to_document_number => To Document Number
138 -- p_from_creation_date => From Creation Date
139 -- p_to_creation_date => To Creation Date
140 -- p_transaction_id => Transacation id to be processed
141 -- x_return_status => Return Status of API (S,W,E,U)
142 -- COMMENT:
143 -- Based on input parameter values, eligble records for processing are
144 -- queried from WTH table.
145 -- Calling API WSH_PROCESS_INTERFACED_PKG.Process_Inbound to process the
146 -- eligible records queried from WTH table.
147 --=============================================================================
148 PROCEDURE Process_Shipment_Advice (
149 p_commit_flag IN VARCHAR2,
150 p_transaction_status IN VARCHAR2,
151 p_from_document_number IN VARCHAR2,
152 p_to_document_number IN VARCHAR2,
153 p_from_creation_date IN VARCHAR2,
154 p_to_creation_date IN VARCHAR2,
155 p_transaction_id IN NUMBER,
156 x_return_status OUT NOCOPY VARCHAR2 )
157 IS
158
159 --Fulfillment Batch XML Project
160 cursor c_get_status (c_trx_id NUMBER)
161 is
162 select transaction_status
163 from wsh_transactions_history
164 where transaction_id = c_trx_id;
165
166 CURSOR C_Get_One_Transactions
167 IS
168 SELECT wth.Transaction_ID,
169 wth.Document_Type,
170 wth.Document_Direction,
171 wth.Document_Number,
172 wth.Orig_Document_Number,
173 wth.Entity_Number,
174 wth.Entity_Type,
175 wth.Trading_Partner_ID,
176 wth.Action_Type,
177 wth.Transaction_Status,
178 wth.ECX_Message_ID,
179 wth.Event_Name,
180 wth.Event_Key,
181 wth.Item_Type,
182 wth.Internal_Control_Number,
183 wth.document_revision,
184 wth.Attribute_Category,
185 wth.Attribute1,
186 wth.Attribute2,
187 wth.Attribute3,
188 wth.Attribute4,
189 wth.Attribute5,
190 wth.Attribute6,
191 wth.Attribute7,
192 wth.Attribute8,
193 wth.Attribute9,
194 wth.Attribute10,
195 wth.Attribute11,
196 wth.Attribute12,
197 wth.Attribute13,
198 wth.Attribute14,
199 wth.Attribute15,
200 null
201 FROM Wsh_Transactions_History wth,
202 Wsh_New_Del_Interface wndi
203 WHERE wndi.interface_action_code = g_interface_action_code
204 AND wndi.delivery_interface_id = to_number(wth.entity_number)
205 AND wth.transaction_id = p_transaction_id
206 AND wth.transaction_status = nvl(p_transaction_status, wth.transaction_status);
207
208 l_transaction_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
209 l_trx_status wsh_transactions_history.transaction_status%TYPE;
210
211 l_from_creation_date DATE;
212 l_to_creation_date DATE;
213
214 l_total NUMBER := 0;
215 l_success NUMBER := 0;
216 l_errors NUMBER := 0;
217 v_cursorid INTEGER;
218 v_ignore INTEGER;
219 l_tmp_status VARCHAR2(1);
220 l_standalone_mode VARCHAR2(1);
221 l_tpw_install VARCHAR2(30);
222 l_transaction_query VARCHAR2(15000);
223
224 l_debug_on BOOLEAN;
225 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_Shipment_Advice';
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 --
235 IF l_debug_on THEN
236 wsh_debug_sv.push(l_module_name);
237 wsh_debug_sv.log(l_module_name, 'p_commit_flag', p_commit_flag);
238 wsh_debug_sv.log(l_module_name, 'p_transaction_status', p_transaction_status);
239 wsh_debug_sv.log(l_module_name, 'p_from_document_number', p_from_document_number);
240 wsh_debug_sv.log(l_module_name, 'p_to_document_number', p_to_document_number);
241 wsh_debug_sv.log(l_module_name, 'p_from_creation_date', p_from_creation_date);
242 wsh_debug_sv.log(l_module_name, 'p_to_creation_date', p_to_creation_date);
243 wsh_debug_sv.log(l_module_name, 'p_transaction_id', p_transaction_id);
244 END IF;
245 --
246
247 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
248 l_tpw_install := FND_PROFILE.Value('WSH_SR_SOURCE');
249 IF nvl(l_tpw_install, FND_API.G_MISS_CHAR) <> 'B' THEN
250 --
251 IF l_debug_on THEN
252 WSH_DEBUG_SV.log(l_module_name,'Error: Profile option "WSH: Distributed Source Entity" value is not set to B(Batch)', l_tpw_install);
253 RAISE FND_API.G_EXC_ERROR;
254 END IF;
255 --
256 END IF;
257
258 l_from_creation_date := to_date(p_from_creation_date,'YYYY/MM/DD HH24:MI:SS');
259 l_to_creation_date := to_date(p_to_creation_date,'YYYY/MM/DD HH24:MI:SS');
260
261 --
262 IF l_debug_on THEN
263 WSH_DEBUG_SV.log(l_module_name,'l_from_creation_date',l_from_creation_date);
264 WSH_DEBUG_SV.log(l_module_name,'l_to_creation_date',l_to_creation_date);
265 END IF;
266 --
267
268 IF p_transaction_id is not null THEN
269 OPEN C_Get_One_Transactions;
270 ELSE
271 --SELECT Clause
272 l_transaction_query := 'SELECT wth.Transaction_ID, ';
273 l_transaction_query := l_transaction_query || 'wth.Document_Type, ';
274 l_transaction_query := l_transaction_query || 'wth.Document_Direction, ';
275 l_transaction_query := l_transaction_query || 'wth.Document_Number, ';
276 l_transaction_query := l_transaction_query || 'wth.Orig_Document_Number, ';
277 l_transaction_query := l_transaction_query || 'wth.Entity_Number, ';
278 l_transaction_query := l_transaction_query || 'wth.Entity_Type, ';
279 l_transaction_query := l_transaction_query || 'wth.Trading_Partner_ID, ';
280 l_transaction_query := l_transaction_query || 'wth.Action_Type, ';
281 l_transaction_query := l_transaction_query || 'wth.Transaction_Status, ';
282 l_transaction_query := l_transaction_query || 'wth.ECX_Message_ID, ';
283 l_transaction_query := l_transaction_query || 'wth.Event_Name, ';
284 l_transaction_query := l_transaction_query || 'wth.Event_Key, ';
285 l_transaction_query := l_transaction_query || 'wth.Item_Type, ';
286 l_transaction_query := l_transaction_query || 'wth.Internal_Control_Number, ';
287 l_transaction_query := l_transaction_query || 'wth.Attribute_Category, ';
288 l_transaction_query := l_transaction_query || 'wth.Attribute1, ';
289 l_transaction_query := l_transaction_query || 'wth.Attribute2, ';
290 l_transaction_query := l_transaction_query || 'wth.Attribute3, ';
291 l_transaction_query := l_transaction_query || 'wth.Attribute4, ';
292 l_transaction_query := l_transaction_query || 'wth.Attribute5, ';
293 l_transaction_query := l_transaction_query || 'wth.Attribute6, ';
294 l_transaction_query := l_transaction_query || 'wth.Attribute7, ';
295 l_transaction_query := l_transaction_query || 'wth.Attribute8, ';
296 l_transaction_query := l_transaction_query || 'wth.Attribute9, ';
297 l_transaction_query := l_transaction_query || 'wth.Attribute10, ';
298 l_transaction_query := l_transaction_query || 'wth.Attribute11, ';
299 l_transaction_query := l_transaction_query || 'wth.Attribute12, ';
300 l_transaction_query := l_transaction_query || 'wth.Attribute13, ';
301 l_transaction_query := l_transaction_query || 'wth.Attribute14, ';
302 l_transaction_query := l_transaction_query || 'wth.Attribute15 ';
303
304 --FROM Clause
305 l_transaction_query := l_transaction_query || ' FROM Wsh_Transactions_History wth, ';
306 l_transaction_query := l_transaction_query || ' Wsh_New_Del_Interface wndi ';
307 l_transaction_query := l_transaction_query || ' WHERE wth.document_type = ''SA'' ';
308 l_transaction_query := l_transaction_query || ' AND wth.document_direction = ''I'' ';
309
310 IF p_from_document_number is not null and p_to_document_number is not null
311 THEN
312 l_transaction_query := l_transaction_query || ' AND wth.document_number between :x_from_document_number ';
313 l_transaction_query := l_transaction_query || ' and :x_to_document_number ';
314 ELSIF p_from_document_number is not null and p_to_document_number is null
315 THEN
316 l_transaction_query := l_transaction_query || ' AND wth.document_number >= :x_from_document_number ';
317 ELSIF p_from_document_number is null and p_to_document_number is not null
318 THEN
319 l_transaction_query := l_transaction_query || ' AND wth.document_number <= :x_from_document_number ';
320 END IF;
321
322 IF p_transaction_status is not null
323 THEN
324 l_transaction_query := l_transaction_query || ' AND wth.transaction_status = :x_transaction_status ';
325 ELSE
326 l_transaction_query := l_transaction_query || ' AND wth.transaction_status in (''AP'', ''ER'') ';
327 END IF;
328
329 IF l_from_creation_date is not null and l_to_creation_date is not null
330 THEN
331 l_transaction_query := l_transaction_query || ' AND wth.creation_date between :x_from_creation_date ';
332 l_transaction_query := l_transaction_query || ' and :x_to_creation_date ';
333 ELSIF l_from_creation_date is not null and l_to_creation_date is null
334 THEN
335 l_transaction_query := l_transaction_query || ' AND wth.creation_date >= :x_from_creation_date ';
336 ELSIF l_from_creation_date is null and l_to_creation_date is not null
337 THEN
338 l_transaction_query := l_transaction_query || ' AND wth.creation_date <= :x_to_creation_date ';
339 END IF;
340
341 l_transaction_query := l_transaction_query || ' AND wndi.interface_action_code = ''' || g_interface_action_code || '''';
342 l_transaction_query := l_transaction_query || ' AND wndi.delivery_interface_id = to_number(wth.entity_number) ';
343 l_transaction_query := l_transaction_query || ' ORDER BY wth.transaction_id ';
344
345 --
346 IF l_debug_on THEN
347 WSH_DEBUG_SV.log(l_module_name, 'Transaction Query', l_transaction_query);
348 END IF;
349 --
350
351 v_cursorid := DBMS_SQL.Open_Cursor;
352 --
353 IF l_debug_on THEN
354 WSH_DEBUG_SV.log(l_module_name, 'Opended cursor successfully', v_cursorid);
355 END IF;
356 --
357 DBMS_SQL.Parse(v_cursorid, l_transaction_query, DBMS_SQL.v7 );
358
359 --
360 IF l_debug_on THEN
361 WSH_DEBUG_SV.logmsg(l_module_name, 'Parsed cursor successfully');
362 END IF;
363 --
364 DBMS_SQL.Define_Column(v_cursorid, 1, l_transaction_rec.Transaction_ID);
365 DBMS_SQL.Define_Column(v_cursorid, 2, l_transaction_rec.Document_Type, 30);
366 DBMS_SQL.Define_Column(v_cursorid, 3, l_transaction_rec.Document_Direction, 1);
367 DBMS_SQL.Define_Column(v_cursorid, 4, l_transaction_rec.Document_Number, 120);
368 DBMS_SQL.Define_Column(v_cursorid, 5, l_transaction_rec.Orig_Document_Number, 120);
369 DBMS_SQL.Define_Column(v_cursorid, 6, l_transaction_rec.Entity_Number, 30);
370 DBMS_SQL.Define_Column(v_cursorid, 7, l_transaction_rec.Entity_Type, 30);
371 DBMS_SQL.Define_Column(v_cursorid, 8, l_transaction_rec.Trading_Partner_ID);
372 DBMS_SQL.Define_Column(v_cursorid, 9, l_transaction_rec.Action_Type, 30);
373 DBMS_SQL.Define_Column(v_cursorid, 10, l_transaction_rec.Transaction_Status, 2);
374 DBMS_SQL.Define_Column_Raw(v_cursorid, 11, l_transaction_rec.ecx_message_id, 16);
375 DBMS_SQL.Define_Column(v_cursorid, 12, l_transaction_rec.Event_Name, 240);
376 DBMS_SQL.Define_Column(v_cursorid, 13, l_transaction_rec.Event_Key, 240);
377 DBMS_SQL.Define_Column(v_cursorid, 14, l_transaction_rec.Item_Type, 8);
378 DBMS_SQL.Define_Column(v_cursorid, 15, l_transaction_rec.Internal_Control_Number);
379 DBMS_SQL.Define_Column(v_cursorid, 16, l_transaction_rec.Attribute_Category, 150);
380 DBMS_SQL.Define_Column(v_cursorid, 17, l_transaction_rec.Attribute1, 150);
381 DBMS_SQL.Define_Column(v_cursorid, 18, l_transaction_rec.Attribute2, 150);
382 DBMS_SQL.Define_Column(v_cursorid, 19, l_transaction_rec.Attribute3, 150);
383 DBMS_SQL.Define_Column(v_cursorid, 20, l_transaction_rec.Attribute4, 150);
384 DBMS_SQL.Define_Column(v_cursorid, 21, l_transaction_rec.Attribute5, 150);
385 DBMS_SQL.Define_Column(v_cursorid, 22, l_transaction_rec.Attribute6, 150);
386 DBMS_SQL.Define_Column(v_cursorid, 23, l_transaction_rec.Attribute7, 150);
387 DBMS_SQL.Define_Column(v_cursorid, 24, l_transaction_rec.Attribute8, 150);
388 DBMS_SQL.Define_Column(v_cursorid, 25, l_transaction_rec.Attribute9, 150);
389 DBMS_SQL.Define_Column(v_cursorid, 26, l_transaction_rec.Attribute10, 150);
390 DBMS_SQL.Define_Column(v_cursorid, 27, l_transaction_rec.Attribute11, 150);
391 DBMS_SQL.Define_Column(v_cursorid, 28, l_transaction_rec.Attribute12, 150);
392 DBMS_SQL.Define_Column(v_cursorid, 29, l_transaction_rec.Attribute13, 150);
393 DBMS_SQL.Define_Column(v_cursorid, 30, l_transaction_rec.Attribute14, 150);
394 DBMS_SQL.Define_Column(v_cursorid, 31, l_transaction_rec.Attribute15, 150);
395 --
396 IF l_debug_on THEN
397 WSH_DEBUG_SV.logmsg(l_module_name, 'Defined Columns successfully');
398 END IF;
399 --
400
401 --Start assigning BIND values
402 IF p_from_document_number is not null and p_to_document_number is not null
403 THEN
404 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_from_document_number', p_from_document_number);
405 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_to_document_number', p_to_document_number);
406 ELSIF p_from_document_number is not null and p_to_document_number is null
407 THEN
408 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_from_document_number', p_from_document_number);
409 ELSIF p_from_document_number is null and p_to_document_number is not null
410 THEN
411 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_to_document_number', p_to_document_number);
412 END IF;
413
414 IF p_transaction_status is not null
415 THEN
416 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_transaction_status', p_transaction_status);
417 END IF;
418
419 IF l_from_creation_date is not null and l_to_creation_date is not null
420 THEN
421 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_from_creation_date', l_from_creation_date);
422 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_to_creation_date', l_to_creation_date);
423 ELSIF l_from_creation_date is not null and l_to_creation_date is null
424 THEN
425 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_from_creation_date', l_from_creation_date);
426 ELSIF l_from_creation_date is null and l_to_creation_date is not null
427 THEN
428 DBMS_SQL.BIND_VARIABLE(v_cursorid,':x_to_creation_date', l_to_creation_date);
429 END IF;
430 --End assigning BIND values
431 --
432 IF l_debug_on THEN
433 WSH_DEBUG_SV.logmsg(l_module_name, 'Bind values successfully');
434 END IF;
435 --
436
437 v_ignore := DBMS_SQL.Execute(v_cursorid);
438 --
439 IF l_debug_on THEN
440 WSH_DEBUG_SV.log(l_module_name, 'Cursor executed successfully', v_ignore);
441 END IF;
442 --
443 END IF;
444
445 LOOP --{
446 IF p_transaction_id is not null THEN
447 FETCH C_Get_One_Transactions INTO l_transaction_rec;
448 EXIT WHEN C_Get_One_Transactions%NOTFOUND;
449 ELSE
450 v_ignore := DBMS_SQL.Fetch_Rows(v_cursorid);
451 --
452 IF l_debug_on THEN
453 WSH_DEBUG_SV.log(l_module_name, 'Fetched successfully', v_ignore);
454 END IF;
455 --
456 IF v_ignore = 0 THEN
457 EXIT;
458 END IF;
459
460 DBMS_SQL.Column_Value(v_cursorid, 1, l_transaction_rec.Transaction_ID);
461 DBMS_SQL.Column_Value(v_cursorid, 2, l_transaction_rec.Document_Type);
462 DBMS_SQL.Column_Value(v_cursorid, 3, l_transaction_rec.Document_Direction);
463 DBMS_SQL.Column_Value(v_cursorid, 4, l_transaction_rec.Document_Number);
464 DBMS_SQL.Column_Value(v_cursorid, 5, l_transaction_rec.Orig_Document_Number);
465 DBMS_SQL.Column_Value(v_cursorid, 6, l_transaction_rec.Entity_Number);
466 DBMS_SQL.Column_Value(v_cursorid, 7, l_transaction_rec.Entity_Type);
467 DBMS_SQL.Column_Value(v_cursorid, 8, l_transaction_rec.Trading_Partner_ID);
468 DBMS_SQL.Column_Value(v_cursorid, 9, l_transaction_rec.Action_Type);
469 DBMS_SQL.Column_Value(v_cursorid, 10, l_transaction_rec.Transaction_Status);
470 DBMS_SQL.Column_Value_Raw(v_cursorid, 11, l_transaction_rec.ecx_message_id);
471 DBMS_SQL.Column_Value(v_cursorid, 12, l_transaction_rec.Event_Name);
472 DBMS_SQL.Column_Value(v_cursorid, 13, l_transaction_rec.Event_Key);
473 DBMS_SQL.Column_Value(v_cursorid, 14, l_transaction_rec.Item_Type);
474 DBMS_SQL.Column_Value(v_cursorid, 15, l_transaction_rec.Internal_Control_Number);
475 DBMS_SQL.Column_Value(v_cursorid, 16, l_transaction_rec.Attribute_Category);
476 DBMS_SQL.Column_Value(v_cursorid, 17, l_transaction_rec.Attribute1);
477 DBMS_SQL.Column_Value(v_cursorid, 18, l_transaction_rec.Attribute2);
478 DBMS_SQL.Column_Value(v_cursorid, 19, l_transaction_rec.Attribute3);
479 DBMS_SQL.Column_Value(v_cursorid, 20, l_transaction_rec.Attribute4);
480 DBMS_SQL.Column_Value(v_cursorid, 21, l_transaction_rec.Attribute5);
481 DBMS_SQL.Column_Value(v_cursorid, 22, l_transaction_rec.Attribute6);
482 DBMS_SQL.Column_Value(v_cursorid, 23, l_transaction_rec.Attribute7);
483 DBMS_SQL.Column_Value(v_cursorid, 24, l_transaction_rec.Attribute8);
484 DBMS_SQL.Column_Value(v_cursorid, 25, l_transaction_rec.Attribute9);
485 DBMS_SQL.Column_Value(v_cursorid, 26, l_transaction_rec.Attribute10);
486 DBMS_SQL.Column_Value(v_cursorid, 27, l_transaction_rec.Attribute11);
487 DBMS_SQL.Column_Value(v_cursorid, 28, l_transaction_rec.Attribute12);
488 DBMS_SQL.Column_Value(v_cursorid, 29, l_transaction_rec.Attribute13);
489 DBMS_SQL.Column_Value(v_cursorid, 30, l_transaction_rec.Attribute14);
490 DBMS_SQL.Column_Value(v_cursorid, 31, l_transaction_rec.Attribute15);
491 --
492 IF l_debug_on THEN
493 WSH_DEBUG_SV.logmsg(l_module_name, 'Fetched from cursor successfully');
494 END IF;
495 --
496 END IF;
497
498 l_tmp_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
499 l_total := l_total + 1;
500 --Fulfillment Batch XML Project
501 --If the tranaction record is in Errored status and a workflow exists,
502 --the 'Process Shipment Advice' activity in workflow will be 'retried'
503 IF l_transaction_rec.item_type is not null and
504 l_transaction_rec.transaction_status='ER' and
505 l_transaction_rec.event_name='oracle.apps.wsh.batch.bsai' and
506 l_transaction_rec.event_key is not null --{
507 THEN
508 --
509 IF l_debug_on THEN
510 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling wf_engine.handleError', WSH_DEBUG_SV.C_PROC_LEVEL);
511 END IF;
512 --
513 WF_ENGINE.handleError(
514 itemType => l_transaction_rec.item_type,
515 itemKey => l_transaction_rec.event_key,
516 activity => 'WSH_FULFILLMENT_INBOUND_WF:PROCESS_SHIPMENT_ADVICE',
517 command => 'RETRY',
518 result => NULL );
519 OPEN c_get_status(l_transaction_rec.transaction_id);
520 FETCH c_get_status INTO l_trx_status;
521 CLOSE c_get_status;
522
523 IF l_debug_on THEN
524 wsh_debug_sv.log(l_module_name,'l_trx_status', l_trx_status);
525 END IF;
526
527 IF l_trx_status <> 'SC' THEN
528 l_tmp_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
529 END IF;
530
531 ELSE
532 --
533 IF l_debug_on THEN
534 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_PROCESS_INTERFACED_PKG.Process_Inbound', WSH_DEBUG_SV.C_PROC_LEVEL);
535 END IF;
536 --
537 WSH_PROCESS_INTERFACED_PKG.Process_Inbound(
538 l_trns_history_rec => l_transaction_rec,
539 x_return_status => l_tmp_status );
540
541 --
542 IF l_debug_on THEN
543 wsh_debug_sv.log(l_module_name,'Return status from WSH_PROCESS_INTERFACED_PKG.Process_Inbound', l_tmp_status);
544 END IF;
545 --
546 END IF; --Fulfillment Batch XML Project
547 -- API Process_Shipment_Advice will return WARNING if its not able to lock tables.
548 IF l_tmp_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
549 l_success := l_success + 1;
550 ELSIF l_tmp_status = WSH_UTIL_CORE.G_RET_STS_ERROR
551 THEN
552 l_errors := l_errors + 1;
553 ELSIF l_tmp_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
554 THEN
555 --
556 IF l_debug_on THEN
557 wsh_debug_sv.log(l_module_name,'Unexpected error occurred in Process_Shipment_Advice', l_tmp_status);
558 END IF;
559 --
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
561 END IF;
562 END LOOP; --}
563
564 IF p_transaction_id is not null THEN
565 IF C_Get_One_Transactions%ISOPEN THEN
566 CLOSE C_Get_One_Transactions;
567 END IF;
568 ELSE
569 IF DBMS_SQL.Is_Open(v_cursorid) THEN
570 --
571 IF l_debug_on THEN
572 WSH_DEBUG_SV.logmsg(l_module_name, 'Closing cursor');
573 END IF;
574 --
575 DBMS_SQL.Close_Cursor(v_cursorid);
576 v_cursorid := null;
577 END IF;
578 END IF;
579
580 IF (l_total = l_success) THEN
581 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
582 ELSIF (l_total = l_errors) THEN
583 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
584 ELSE
585 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
586 END IF;
587
588 --
589 IF l_debug_on THEN
590 WSH_DEBUG_SV.log(l_module_name, 'Return Status from Process_Shipment_Advice', x_return_status);
591 WSH_DEBUG_SV.logmsg(l_module_name,'');
592 WSH_DEBUG_SV.logmsg(l_module_name,'Summary:-');
593 WSH_DEBUG_SV.logmsg(l_module_name,'===================================');
594 WSH_DEBUG_SV.log(l_module_name,'No. of Shipment Advices selected for processing ', l_total);
595 WSH_DEBUG_SV.log(l_module_name,'No. of Shipment Advices processed successfully ', l_success);
596 WSH_DEBUG_SV.log(l_module_name,'No. of Shipment Advices errored during processing', l_errors);
597 WSH_DEBUG_SV.pop(l_module_name);
598 -- To Print in Concurrent Request Output File
599 ELSIF FND_GLOBAL.Conc_Request_Id > 0 THEN
600 FND_FILE.put_line(FND_FILE.output, 'Summary:-');
601 FND_FILE.put_line(FND_FILE.output,'===================================');
602 FND_FILE.put_line(FND_FILE.output, 'No. of Shipment Advices selected for processing => ' || l_total);
603 FND_FILE.put_line(FND_FILE.output, 'No. of Shipment Advices processed successfully => ' || l_success);
604 FND_FILE.put_line(FND_FILE.output, 'No. of Shipment Advices errored during processing => ' || l_errors);
605 END IF;
606 --
607 EXCEPTION
608 when FND_API.G_EXC_ERROR then
609 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
610 IF p_transaction_id is not null THEN
611 IF C_Get_One_Transactions%ISOPEN THEN
612 CLOSE C_Get_One_Transactions;
613 END IF;
614 ELSE
615 IF DBMS_SQL.Is_Open(v_cursorid) THEN
616 DBMS_SQL.Close_Cursor(v_cursorid);
617 v_cursorid := null;
618 END IF;
619 END IF;
620 --
621 IF l_debug_on THEN
622 wsh_debug_sv.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
623 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
624 END IF;
625 --
626
627 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
629 IF p_transaction_id is not null THEN
630 IF C_Get_One_Transactions%ISOPEN THEN
631 CLOSE C_Get_One_Transactions;
632 END IF;
633 ELSE
634 IF DBMS_SQL.Is_Open(v_cursorid) THEN
635 DBMS_SQL.Close_Cursor(v_cursorid);
636 v_cursorid := null;
637 END IF;
638 END IF;
639 --
640 IF l_debug_on THEN
641 wsh_debug_sv.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
642 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
643 END IF;
644 --
645
646 WHEN OTHERS THEN
647 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
648 IF p_transaction_id is not null THEN
649 IF C_Get_One_Transactions%ISOPEN THEN
650 CLOSE C_Get_One_Transactions;
651 END IF;
652 ELSE
653 IF DBMS_SQL.Is_Open(v_cursorid) THEN
654 DBMS_SQL.Close_Cursor(v_cursorid);
655 v_cursorid := null;
656 END IF;
657 END IF;
658 --
659 IF l_debug_on THEN
660 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
661 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
662 END IF;
663 --
664 END Process_Shipment_Advice;
665
666 END WSH_SHIPMENT_ADVICE_PKG;