[Home] [Help]
PACKAGE BODY: APPS.WSH_TRANSACTIONS_FORM_PKG
Source
1 PACKAGE BODY WSH_TRANSACTIONS_FORM_PKG as
2 /* $Header: WSHINFMB.pls 120.6 2011/12/21 10:17:17 skanduku ship $ */
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRANSACTIONS_FORM_PKG';
5 --
6 PROCEDURE process_wf_status( p_item_type IN VARCHAR2,
7 p_event_key IN VARCHAR2,
8 X_Return_Status OUT NOCOPY VARCHAR2) IS
9
10
11 pragma AUTONOMOUS_TRANSACTION;
12
13 l_activity VARCHAR2(200);
14
15 l_debug_on BOOLEAN;
16 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'process_wf_status';
17 --
18 BEGIN
19 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
20 IF l_debug_on IS NULL THEN
21 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
22 END IF;
23
24 IF l_debug_on THEN
25 wsh_debug_sv.push(l_module_name);
26 wsh_debug_sv.log (l_module_name,'p_item_type',p_item_type);
27 wsh_debug_sv.log (l_module_name,'p_event_key',p_event_key);
28 END IF;
29
30 x_return_status := wsh_util_core.g_ret_sts_success;
31
32 IF (p_item_type = 'WSHTPWI' ) THEN
33 l_activity := 'WSH_TPW_PROCESS_WF:POPULATE_BASE_TABLES';
34 ELSIF (p_item_type = 'WSHSUPI' ) THEN
35 l_activity := 'WSH_SUPPLIER_WF:WSH_PROCESS_DELIVERY';
36 END IF;
37
38 IF l_debug_on THEN
39 wsh_debug_sv.log (l_module_name,'l_activity',l_activity);
40 END IF;
41
42 Savepoint l_wf_status;
43
44 wf_engine.HandleError(
45 itemtype => p_item_type,
46 itemkey => p_event_key,
47 activity => l_activity,
48 command => 'SKIP',
49 result => 'SUCCESS'
50 );
51
52 COMMIT;
53
54 IF l_debug_on THEN
55 wsh_debug_sv.pop(l_module_name);
56 END IF;
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 Rollback to savepoint l_wf_status;
61 X_return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
62 IF l_debug_on THEN
63 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
64 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
65 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
66 wsh_debug_sv.stop_debug;
67 END IF;
68 END process_wf_status;
69
70
71 /*=====================================================================================
72 PROCEDURE NAME : Process_form
73
74 This procedure looks at the Delivery ID , Trip id and Transaction Id and then call
75 Process_inbound for further processing.
76 =======================================================================================*/
77
78 PROCEDURE Process_form( P_delivery_id IN NUMBER,
79 P_trip_id IN NUMBER,
80 P_transaction_id IN NUMBER,
81 X_Return_Status OUT NOCOPY VARCHAR2) IS
82
83
84 L_return_status VARCHAR2(1);
85 l_wf_status varchar2(40);
86 l_wf_result varchar2(40);
87 l_result_code varchar2(40);
88 l_activity_name VARCHAR2(200);--Fulfillment Batch XML Project
89
90 WSH_FAILED_PROCESS EXCEPTION;
91 -- LSP PROJECT : Get client_Code value for the given transactionId.
92 CURSOR history_cur IS
93 SELECT wth.document_type,wth.document_number,wth.orig_document_number,wth.document_direction,wth.transaction_status,wth.
94 action_type,wth.entity_number,wth.entity_type,wth.trading_partner_id,wth.ecx_message_id,wth.
95 event_name,wth.event_key,wth.internal_control_number,wth.item_type,wndi.client_code
96 FROM WSH_TRANSACTIONS_HISTORY wth,
97 WSH_NEW_DEL_INTERFACE wndi
98 WHERE wth.transaction_id = P_transaction_id
99 AND wth.entity_number = wndi.delivery_interface_id(+);
100
101 cursor c_get_status (v_trx_id NUMBER)
102 IS select TRANSACTION_STATUS
103 FROM wsh_transactions_history
104 WHERE transaction_id = v_trx_id;
105
106 l_trx_status wsh_transactions_history.transaction_status%TYPE;
107 -- TPW - Distribution Changes
108 l_trns_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
109 --R12.1.1 STANDALONE PROJECT
110 l_wms_deploy_mode VARCHAR2(1);
111
112 --
113 l_debug_on BOOLEAN;
114 --
115 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_FORM';
116 --
117 BEGIN
118 --
119 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
120 --
121 IF l_debug_on IS NULL
122 THEN
123 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
124 END IF;
125 --
126 IF l_debug_on THEN
127 WSH_DEBUG_SV.start_debug (P_transaction_id);
128 wsh_debug_sv.push(l_module_name, 'PROCESS_FORM');
129 wsh_debug_sv.log (l_module_name,'Transaction ID', P_Transaction_id);
130 wsh_debug_sv.log (l_module_name,'DELIVERY ID', P_delivery_id);
131 END IF;
132
133 IF (P_transaction_id IS NOT NULL ) THEN --{
134 -- R12.1.1 STANDALONE PROJECT
135 l_wms_deploy_mode := WMS_DEPLOY.WMS_DEPLOYMENT_MODE;
136 --
137 IF l_debug_on THEN
138 wsh_debug_sv.log (l_module_name,'l_wms_deploy_mode', l_wms_deploy_mode);
139 END IF;
140 --
141 FOR history_rec in history_cur
142 LOOP
143
144 IF l_debug_on THEN
145 wsh_debug_sv.log (l_module_name,'Document Number', history_rec.DOCUMENT_NUMBER);
146 wsh_debug_sv.log (l_module_name,'transaction_status', history_rec.transaction_status);
147 wsh_debug_sv.log (l_module_name,'document_type', history_rec.document_type);
148 wsh_debug_sv.log (l_module_name,'client code',history_rec.client_code);
149 END IF;
150
151 -- TPW - Distribution Changes - Start
152 -- Populate Txn History Record details into l_trns_history_rec variable,
153 -- so that it can be passed to Process_Inbound API
154 IF ( history_rec.document_type = 'SA' AND
155 history_rec.item_type IS NULL AND
156 history_rec.event_key IS NULL )
157 THEN
158 l_trns_history_rec.DOCUMENT_TYPE := history_rec.DOCUMENT_TYPE;
159 l_trns_history_rec.DOCUMENT_NUMBER := history_rec.DOCUMENT_NUMBER;
160 l_trns_history_rec.ORIG_DOCUMENT_NUMBER := history_rec.ORIG_DOCUMENT_NUMBER;
161 l_trns_history_rec.DOCUMENT_DIRECTION := history_rec.DOCUMENT_DIRECTION;
162 l_trns_history_rec.TRANSACTION_STATUS := history_rec.TRANSACTION_STATUS;
163 l_trns_history_rec.ACTION_TYPE := history_rec.ACTION_TYPE;
164 l_trns_history_rec.ENTITY_NUMBER := history_rec.ENTITY_NUMBER;
165 l_trns_history_rec.ENTITY_TYPE := history_rec.ENTITY_TYPE;
166 l_trns_history_rec.TRADING_PARTNER_ID := history_rec.TRADING_PARTNER_ID;
167 l_trns_history_rec.ECX_MESSAGE_ID := history_rec.ECX_MESSAGE_ID;
168 l_trns_history_rec.EVENT_NAME := history_rec.EVENT_NAME;
169 l_trns_history_rec.EVENT_KEY := history_rec.EVENT_KEY;
170 l_trns_history_rec.ITEM_TYPE := history_rec.ITEM_TYPE;
171 l_trns_history_rec.INTERNAL_CONTROL_NUMBER := history_rec.INTERNAL_CONTROL_NUMBER;
172 END IF;
173 -- TPW - Distribution Changes - End
174
175 --
176 IF history_rec.transaction_status = 'ER'
177 THEN --{
178 IF history_rec.document_type = 'SR' THEN --{
179
180 --R12.1.1 STANDALONE PROJECT
181 IF (l_wms_deploy_mode = 'D' OR ( l_wms_deploy_mode = 'L' and history_rec.client_code IS NOT NULL)) THEN --{ LSP PROJECT : Consider LSP mode also
182
183 IF l_debug_on THEN
184 wsh_debug_sv.logmsg(l_module_name, 'Item Type: '||history_rec.item_type||' Event Key: '||history_rec.event_key);
185 END IF;
186 --
187 IF (history_rec.item_type is not null) and (history_rec.event_key is not null) THEN
188
189 IF l_debug_on THEN
190 wsh_debug_sv.logmsg(l_module_name, 'Calling wf_engine.handleError');
191 END IF;
192 wf_engine.handleError(
193 itemType => history_rec.item_type,
194 itemKey => history_rec.event_key,
195 activity => 'WSH_STAND_PROCESS_WF:POPULATE_BASE_TABLES',
196 command => 'RETRY',
197 result => NULL
198 );
199 ELSE
200 --
201 IF l_debug_on THEN
202 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPMENT_REQUEST_PKG.Process_Shipment_Request', WSH_DEBUG_SV.C_PROC_LEVEL);
203 END IF;
204 --
205 WSH_SHIPMENT_REQUEST_PKG.Process_Shipment_Request(
206 p_commit_flag => FND_API.G_TRUE,
207 p_transaction_status => history_rec.transaction_status,
208 p_client_code => null, -- LSP PROJECT
209 p_from_document_number => null,
210 p_to_document_number => null,
211 p_from_creation_date => null,
212 p_to_creation_date => null,
213 p_transaction_id => p_transaction_id,
214 x_return_status => l_return_status);
215 --
216 IF l_debug_on THEN
217 wsh_debug_sv.log (l_module_name,'l_return_status', l_return_status);
218 END IF;
219 --
220 END IF;
221 --
222 ELSE --} {
223 wf_engine.handleError(
224 itemType => history_rec.item_type,
225 itemKey => history_rec.event_key,
226 activity => 'WSH_TPW_PROCESS_WF:POPULATE_BASE_TABLES',
227 command => 'RETRY',
228 result => NULL
229 );
230 END IF; --}
231 ELSE --}{
232 -- TPW - Distribution Changes - Start
233 -- Call WF API to process the SA data if its workflow driven otherwise call Process_Inbound API.
234 IF (history_rec.item_type is not null) and (history_rec.event_key is not null) THEN
235 --Fullfilment Batch XML Project
236 IF history_rec.item_type='WSHBATI' THEN
237 l_activity_name := 'WSH_FULFILLMENT_INBOUND_WF:PROCESS_SHIPMENT_ADVICE';
238 ELSE
239 l_activity_name := 'WSH_SUPPLIER_WF:WSH_PROCESS_DELIVERY';
240 END IF;
241 --
242 IF l_debug_on THEN
243 wsh_debug_sv.logmsg(l_module_name,
244 'Calling wf_engine.handleError');
245 END IF;
246 --
247 wf_engine.handleError(
248 itemType => history_rec.item_type,
249 itemKey => history_rec.event_key,
250 activity => l_activity_name,
251 command => 'RETRY',
252 result => NULL
253 );
254 ELSE
255 --
256 IF l_debug_on THEN
257 wsh_debug_sv.logmsg(l_module_name, 'Calling WSH_PROCESS_INTERFACED_PKG.Process_Inbound');
258 END IF;
259 --
260 -- Call process inbound
261 WSH_PROCESS_INTERFACED_PKG.Process_Inbound(
262 l_trns_history_rec => l_trns_history_rec,
263 x_return_status => l_return_status );
264
265 --
266 IF l_debug_on THEN
267 wsh_debug_sv.log (l_module_name,'Process_Inbound: l_return_status', l_return_status);
268 END IF;
269 --
270 END IF;
271 -- TPW - Distribution Changes - End
272 END IF; --}
273 --COMMIT; as per code review
274 ELSIF history_rec.transaction_status = 'AP'
275 THEN --}{
276 --
277 -- TPW - Distribution Changes
278 -- Call Process_shipment_Request api if document type is SR
279 IF history_rec.document_type = 'SR' AND
280 ( l_wms_deploy_mode = 'D' OR ( l_wms_deploy_mode = 'L' and history_rec.client_code IS NOT NULL)) THEN --{ LSP PROJECT : Consider LSP mode also
281
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPMENT_REQUEST_PKG.Process_Shipment_Request', WSH_DEBUG_SV.C_PROC_LEVEL);
284 END IF;
285
286 WSH_SHIPMENT_REQUEST_PKG.Process_Shipment_Request(
287 p_commit_flag => FND_API.G_TRUE,
288 p_transaction_status => history_rec.transaction_status,
289 p_client_code => null, -- LSP PROJECT
290 p_from_document_number => null,
291 p_to_document_number => null,
292 p_from_creation_date => null,
293 p_to_creation_date => null,
294 p_transaction_id => p_transaction_id,
295 x_return_status => l_return_status);
296
297 IF l_debug_on THEN
298 wsh_debug_sv.log (l_module_name,'l_return_status', l_return_status);
299 END IF;
300
301 -- TPW - Distribution Changes
302 -- Call Process_Inbound api, if document type is SA and its NOT triggered from WF
303 -- If Txn Status is 'AP' then SA process is NOT triggered from WF.
304 -- } {
305 ELSIF ( history_rec.document_type = 'SA' )
306 THEN
307 --
308 IF l_debug_on THEN
309 wsh_debug_sv.logmsg(l_module_name, 'For Txn Status AP, Calling WSH_PROCESS_INTERFACED_PKG.Process_Inbound');
310 END IF;
311 --
312 -- Call process inbound
313 WSH_PROCESS_INTERFACED_PKG.Process_Inbound(
314 l_trns_history_rec => l_trns_history_rec,
315 x_return_status => l_return_status );
316
317 --
318 IF l_debug_on THEN
319 wsh_debug_sv.log (l_module_name,'For Txn Status AP, Process_Inbound: l_return_status', l_return_status);
320 END IF;
321 --
322 -- TPW - Distribution Changes
323 -- Added Comments: Not sure why API wf_engine.completeActivity is invoked, since
324 -- Txn History record populated from XML Gateway will be with Txn Status 'IP'
325 ELSE --} {
326 IF l_debug_on THEN
327 wsh_debug_sv.logmsg(l_module_name,
328 'Calling wf_engine.completeActivity');
329 END IF;
330 --
331 wf_engine.completeActivity (
332 itemtype => history_rec.item_type,
333 itemkey => history_rec.event_key,
334 activity => 'WSH_SUPPLIER_WF:CONTINUE_SHIPMENT_ADVICE',
335 result => l_result_code);
336 --COMMIT; as percode review
337 END IF; --}
338 END IF; --}
339
340 --
341 OPEN c_get_status(P_transaction_id);
342 FETCH c_get_status INTO l_trx_status;
343 CLOSE c_get_status;
344
345 IF l_debug_on THEN
346 wsh_debug_sv.log (l_module_name,'l_trx_status'
347 , l_trx_status);
348 END IF;
349
350 IF l_trx_status <> 'SC' THEN
351 RAISE WSH_FAILED_PROCESS;
352 END IF;
353 END LOOP;
354
355 IF l_debug_on THEN
356 wsh_debug_sv.log (l_module_name,'After loop');
357 END IF;
358 END IF; --}
359 IF l_debug_on THEN
360 wsh_debug_sv.pop(l_module_name);
361 wsh_debug_sv.stop_debug;
362 END IF;
363 EXCEPTION
364 WHEN WSH_FAILED_PROCESS THEN
365 x_return_status := wsh_util_core.g_ret_sts_error;
366 FND_MESSAGE.Set_Name('WSH', 'WSH_FAILED_PROCESS');
367 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
368 IF l_debug_on THEN
369 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FAILED_PROCESS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
370 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FAILED_PROCESS');
371 wsh_debug_sv.stop_debug;
372 END IF;
373
374 WHEN OTHERS THEN
375 X_return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
376 IF l_debug_on THEN
377 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
378 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
379 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
380 wsh_debug_sv.stop_debug;
381 END IF;
382 END process_form;
383 END WSH_TRANSACTIONS_FORM_PKG;