[Home] [Help]
PACKAGE BODY: APPS.WSH_ROUTING_RESPONSE_PKG
Source
1 PACKAGE BODY WSH_ROUTING_RESPONSE_PKG AS
2 /* $Header: WSHRESPB.pls 120.0 2005/05/27 05:10:10 appldev noship $ */
3
4 --
5 -- Pre-reqs : None
6 --
7 -- Parameters
8 -- p_deliveryIdTab - table of delivery_ids to generate routing responses for
9 -- x_routingRespIdTab - table of routing response Ids
10 -- x_RetStatus - Return status from this API
11 --
12 -- Purpose : This is a wrapper API that is called by the Delivery
13 -- Group API in order to generate routing responses for a
14 -- given set of delivery Ids. This API implements the
15 -- following logic:
16 -- (1) Validate each delivery.
17 -- (2) Create a record in WSH_INBOUND_TXN_HISTORY
18 -- (3) Raise a business event for each delivery Id
19 -- (4) Log a message to indicate to the user whether the
20 -- operation was successful or not.
21 --
22 -- Version : 1.0
23 --
24 PROCEDURE GenerateRoutingResponse(
25 p_deliveryIdTab IN wsh_util_core.id_tab_type,
26 x_routingRespIdTab OUT NOCOPY wsh_util_core.id_tab_type,
27 x_RetStatus OUT NOCOPY VARCHAR2) IS
28 --
29 l_Status VARCHAR2(10);
30 l_TxnId NUMBER;
31 l_RevNum NUMBER;
32 l_RespNum NUMBER;
33 l_numErrors NUMBER := 0;
34 l_numWarnings NUMBER := 0;
35 l_DelName VARCHAR2(30);
36 i NUMBER;
37 l_SuccDels NUMBER := 0;
38 l_debugOn BOOLEAN;
39 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.GenerateRoutingResponse';
40 --
41 BEGIN
42 --
43 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
44 --
45 IF l_debugOn IS NULL THEN
46 l_debugOn := wsh_debug_sv.is_debug_enabled;
47 END IF;
48 --
49 IF l_debugOn THEN
50 wsh_debug_sv.push(l_moduleName);
51 wsh_debug_sv.log(l_moduleName, 'p_deliveryIdTab.COUNT', p_deliveryIdTab.COUNT);
52 END IF;
53 --
54 x_RetStatus := WSH_UTIL_CORE.g_RET_STS_SUCCESS;
55 --
56 -- Now for each delivery, let's generate a notification
57 --
58 i := p_deliveryIdTab.FIRST;
59 --
60 WHILE i IS NOT NULL LOOP
61 --{
62 BEGIN
63 --
64 IF l_debugOn THEN
65 wsh_debug_sv.log(l_moduleName, 'Delivery ID', p_DeliveryIdTab(i));
66 END IF;
67 --
68 -- First validate the delivery
69 --
70 ValidateDelivery(p_deliveryIdTab(i), l_Status);
71 --
72 wsh_util_core.api_post_call(p_return_status => l_Status,
73 x_num_warnings => l_numWarnings,
74 x_num_errors => l_numErrors);
75 --
76 l_SuccDels := l_SuccDels + 1;
77 --
78 -- Delivery is valid, so proceed to create a record
79 -- in WSH_INBOUND_TXN_HISTORY
80 --
81 CreateTxnHistory(p_delId => p_deliveryIdTab(i),
82 x_TxnId => l_TxnId,
83 x_RespNum => l_RespNum,
84 x_DelName => l_DelName,
85 x_Status => l_Status);
86 --
87 wsh_util_core.api_post_call(p_return_status => l_Status,
88 x_num_warnings => l_numWarnings,
89 x_num_errors => l_numErrors);
90 --
91 x_routingRespIdTab(x_routingRespIdTab.COUNT + 1) := l_txnId;
92 --
93 -- Raise a business event for the delivery
94 --
95 SendNotification(p_delivId => p_deliveryIdTab(i),
96 p_TxnId => l_TxnId,
97 p_DelName => l_DelName,
98 p_RespNum => l_RespNum,
99 x_RetSts => l_Status);
100 --
101 wsh_util_core.api_post_call(p_return_status => l_Status,
102 x_num_warnings => l_numWarnings,
103 x_num_errors => l_numErrors);
104 --
105 IF l_Status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
106 FND_MESSAGE.SET_NAME('WSH', 'WSH_ROUTING_RESP_SUCCESS');
107 FND_MESSAGE.SET_TOKEN('RESP_NUM', l_RespNum);
108 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
109 WSH_NEW_DELIVERIES_PVT.Get_Name(p_deliveryIdTab(i)));
110 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_SUCCESS,
111 l_moduleName);
112 ELSE
113 FND_MESSAGE.SET_NAME('WSH', 'WSH_ROUTING_RESP_WARNING');
114 FND_MESSAGE.SET_TOKEN('RESP_NUM', l_RespNum);
115 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
116 WSH_NEW_DELIVERIES_PVT.Get_Name(p_deliveryIdTab(i)));
117 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING,
118 l_moduleName);
119 END IF;
120 --
121 EXCEPTION
122 --
123 WHEN FND_API.G_EXC_ERROR THEN
124 --
125 IF l_debugOn THEN
126 wsh_debug_sv.logmsg(l_moduleName, 'Expected error ' || SUBSTRB(SQLERRM,1,200));
127 END IF;
128 --
129 FND_MESSAGE.SET_NAME('WSH', 'WSH_ROUTING_RESP_FAILURE');
130 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
131 WSH_NEW_DELIVERIES_PVT.Get_Name(p_deliveryIdTab(i)));
132 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR,
133 l_moduleName);
134 --
135
136 END;
137 --
138 i := p_deliveryIdTab.NEXT(i);
139 --}
140 END LOOP;
141 --
142 IF l_SuccDels = 0 THEN
143 x_RetStatus := WSH_UTIL_CORE.G_RET_STS_ERROR;
144 ELSIF l_SuccDels < p_deliveryIdTab.COUNT THEN
145 x_RetStatus := WSH_UTIL_CORE.G_RET_STS_WARNING;
146 ELSIF l_numWarnings > 0 THEN
147 x_RetStatus := WSH_UTIL_CORE.G_RET_STS_WARNING;
148 ELSE
149 x_RetStatus := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
150 END IF;
151 --
152 --WSH_UTIL_CORE.ADD_MESSAGE(x_RetStatus, l_moduleName);
153 --
154 IF l_debugOn THEN
155 wsh_debug_sv.log(l_moduleName, 'x_routingRespIdTab.COUNT', x_routingRespIdTab.COUNT);
156 wsh_debug_sv.log(l_moduleName, 'l_numWarnings', l_numWarnings);
157 wsh_debug_sv.log(l_moduleName, 'l_SuccDels', l_SuccDels);
158 wsh_debug_sv.log(l_moduleName, 'p_deliveryIdTab.COUNT', p_deliveryIdTab.COUNT);
159 wsh_debug_sv.log(l_moduleName, '# of messages', FND_MSG_PUB.COUNT_MSG);
160 wsh_debug_sv.pop(l_moduleName, x_RetStatus);
161 END IF;
162 --
163 EXCEPTION
164 --
165 WHEN FND_API.G_EXC_ERROR THEN
166 --
167 x_RetStatus := WSH_UTIL_CORE.g_RET_STS_ERROR;
168 FND_MESSAGE.SET_NAME('WSH', 'WSH_ROUTING_RESP_FAILURE');
169 WSH_UTIL_CORE.ADD_MESSAGE(x_RetStatus, l_moduleName);
170 --
171 IF l_debugOn THEN
172 wsh_debug_sv.logmsg(l_moduleName, 'Expected error ' || SUBSTRB(SQLERRM,1,200));
173 wsh_debug_sv.pop(l_moduleName);
174 END IF;
175 --
176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
177 --
178 x_RetStatus := WSH_UTIL_CORE.g_RET_STS_UNEXP_ERROR;
179 FND_MESSAGE.SET_NAME('WSH', 'WSH_ROUTING_RESP_FAILURE');
180 WSH_UTIL_CORE.ADD_MESSAGE(x_RetStatus, l_moduleName);
181 --
182 IF l_debugOn THEN
183 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,200));
184 wsh_debug_sv.pop(l_moduleName);
185 END IF;
186 --
187 WHEN OTHERS THEN
188 --
189 wsh_util_core.default_handler('WSH_ROUTING_RESPONSE_PKG.GenerateRoutingResponse', l_moduleName);
190 --
191 x_RetStatus := WSH_UTIL_CORE.g_RET_STS_ERROR;
192 FND_MESSAGE.SET_NAME('WSH', 'WSH_ROUTING_RESP_FAILURE');
193 WSH_UTIL_CORE.ADD_MESSAGE(x_RetStatus, l_moduleName);
194 --
195 IF l_debugOn THEN
196 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,100));
197 wsh_debug_sv.pop(l_moduleName);
198 END IF;
199 --
200 END GenerateRoutingResponse;
201
202
203 --
204 -- Pre-reqs : None
205 --
206 -- Parameters
207 -- p_delivId - delivery ID for which the business event should be raised
208 -- p_TxnId - transaction_id from wsh_inbound_txn_history that serves
209 -- as the item key for the business event.
210 -- p_DelName - Delivery Name
211 -- p_RespNum - Routing response Number. This maps to receipt_number from
212 -- wsh_inbound_txn_history
213 -- x_RetSts - Return status from this API.
214 --
215 -- Purpose : This is a API that raises a business event to trigger
216 -- the routing response WF process. The following logic
217 -- is implemented in this API
218 -- (1) Determine the from and to roles for the WF item
219 -- (2) Set the various item attributes of the WF item
220 -- (3) Raise the business event (oracle.apps.fte.inbound.routresp.send)
221 --
222 -- Version : 1.0
223 --
224 PROCEDURE SendNotification(p_delivId IN NUMBER,
225 p_TxnId IN NUMBER,
226 p_DelName IN VARCHAR2,
227 p_RespNum IN NUMBER,
228 x_RetSts OUT NOCOPY VARCHAR2) IS
229 --
230 l_eventName VARCHAR2(50) := g_eventName;
231 l_itemType VARCHAR2(30) := 'FTERRESP';
232 l_itemKey VARCHAR2(30);
233 l_parameter_list wf_parameter_list_t;
234 l_orgId NUMBER;
235 l_FromRole VARCHAR2(100);
236 l_ToRole VARCHAR2(100);
237 --
238 l_debugOn BOOLEAN;
239 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.SendNotification';
240 --
241 BEGIN
242 --
243 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
244 --
245 IF l_debugOn IS NULL THEN
246 l_debugOn := wsh_debug_sv.is_debug_enabled;
247 END IF;
248 --
249 IF l_debugOn THEN
250 wsh_debug_sv.push(l_moduleName);
251 wsh_debug_sv.log(l_moduleName, 'p_TxnId', p_TxnId);
252 wsh_debug_sv.log(l_moduleName, 'p_RespNum', p_RespNum);
253 wsh_debug_sv.log(l_moduleName, 'Delivery Id', p_delivId);
254 wsh_debug_sv.log(l_moduleName, 'Delivery Name', p_delName);
255 wsh_debug_sv.log(l_moduleName, 'Event Name', l_eventName);
256 wsh_debug_sv.log(l_moduleName, 'Item Type', l_itemType);
257 END IF;
258 --
259 x_RetSts := WSH_UTIL_CORE.g_Ret_STS_SUCCESS;
260 l_itemKey := p_TxnId;
261 fnd_profile.get('ORG_ID', l_orgId);
262 --
263 -- Determine what the from and to roles are
264 --
265 l_FromRole := GetFromRole(FND_GLOBAL.USER_ID);
266 l_ToRole := GetToRole(p_delivId);
267 --
268 IF l_debugOn THEN
269 wsh_debug_sv.log(l_moduleName, 'Org ID', l_orgId);
270 wsh_debug_sv.log(l_moduleName, 'From Role', l_FromRole);
271 wsh_debug_sv.log(l_moduleName, 'To Role', l_ToRole);
272 END IF;
273 --
274 -- Set the item attributes for the FTERRESP workflow item
275 --
276 WF_EVENT.AddParameterToList (p_name => 'ORG_ID',
277 p_value => l_orgId,
278 p_parameterlist => l_parameter_list);
279 --
280 WF_EVENT.AddParameterToList (p_name => 'USER_ID',
281 p_value => FND_GLOBAL.USER_ID,
282 p_parameterlist => l_parameter_list);
283 --
284 WF_EVENT.AddParameterToList (p_name => 'APPLICATION_ID',
285 p_value => FND_GLOBAL.RESP_APPL_ID,
286 p_parameterlist => l_parameter_list);
287 --
288 WF_EVENT.AddParameterToList (p_name => 'RESPONSIBILITY_ID',
289 p_value => FND_GLOBAL.RESP_ID,
290 p_parameterlist => l_parameter_list);
291 --
292 WF_EVENT.AddParameterToList (p_name => 'FROM_USER',
293 p_value => l_FromRole,
294 p_parameterlist => l_parameter_list);
295 --
296 WF_EVENT.AddParameterToList(p_name => 'WF_ADMINISTRATOR',
297 p_value => l_FromRole,
298 p_parameterlist => l_parameter_list);
299 --
300 WF_EVENT.AddParameterToList(p_name => 'TO_USER',
301 p_value => l_ToRole,
302 p_parameterlist => l_parameter_list);
303 --
304 WF_EVENT.AddParameterToList (p_name => 'DELIVERY_ID',
305 p_value => p_delivId,
306 p_parameterlist => l_parameter_list);
307 --
308 WF_EVENT.AddParameterToList(p_name => 'TRANSACTION_ID',
309 p_value => p_TxnId,
310 p_parameterlist => l_parameter_list);
311 --
312 WF_EVENT.AddParameterToList (p_name => 'ROUT_RESP_NUM',
313 p_value => p_RespNum,
314 p_parameterlist => l_parameter_list);
315 --
316 IF l_debugOn THEN
317 wsh_debug_sv.log(l_moduleName, 'Event Name', l_eventName);
318 wsh_debug_sv.log(l_moduleName, 'Item Key', l_itemKey);
319 wsh_debug_sv.logmsg(l_moduleName, 'Now Raising business event');
320 END IF;
321 --
322 -- Raise the business event
323 --
324 wf_event.raise(p_event_name => l_eventName,
325 p_event_key => l_itemkey,
326 p_parameters => l_parameter_list
327 );
328 --
329 IF l_debugOn THEN
330 wsh_debug_sv.logmsg(l_moduleName, 'After raising business event');
331 wsh_debug_sv.pop(l_moduleName);
332 END IF;
333 --
334 EXCEPTION
335 --
336 WHEN OTHERS THEN
337 --
338 x_RetSts := WSH_UTIL_CORE.g_Ret_STS_ERROR;
339 --
340 wsh_util_core.default_handler('WSH_ROUTING_RESPONSE_PKG.SendNotification');
341 --
342 IF l_debugOn THEN
343 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,200));
344 wsh_debug_sv.pop(l_moduleName);
345 END IF;
346 --
347 RAISE;
348 --
349 END SendNotification;
350
351
352 --
353 -- Pre-reqs : None
354 --
355 -- Parameters
356 -- itemtype - represents the WF item type. In this case, it is FTERRESP
357 -- itemKey - identifies the unique item Key. In this case, it is the transaction_id
358 -- from wsh_inbound_txn_history
359 -- actid - Standard WF attribute to represent the activity ID inside the process
360 -- funcmode - Standard WF attribute representing the function execution mode
361 -- resultout - Standard WF attribute that represents the return status of this API
362 --
363 -- Purpose : This is a API that is called as part of the actual routing response WF.
364 -- This API performs some additional checks prior to actually
365 -- firing the notification. The following logic is implemented in this API
366 -- (1) Lock the delivery so no other attribute can be altered.
367 -- (2) Validate the delivery again to ensure that it is still a valid one
368 -- to generate the routing response.
369 -- (3) Determine the various Ids (routing request num, revision number,
370 -- pickup and dropoff stop Ids, trip Id) that are reqd for the notification.
371 -- (4) If the delivery and trip are not planned, call the respective APIs
372 -- to plan them.
373 -- (5) If there is an error in any of the above processes, set the notfn.
374 -- subject and body of the email that will be sent to the user.
375 --
376 -- Version : 1.0
377 --
378 PROCEDURE PreNotification(itemtype IN VARCHAR2,
379 itemkey IN VARCHAR2,
380 actid IN NUMBER,
381 funcmode IN VARCHAR2,
382 resultout OUT NOCOPY VARCHAR2) IS
383 --
384 l_deliveryId NUMBER;
385 l_status VARCHAR2(10);
386 l_orgId NUMBER;
387 l_routreqIdTab WSH_UTIL_CORE.ID_TAB_TYPE;
388 l_routreqNum VARCHAR2(30);
389 l_revNum NUMBER;
390 l_delName VARCHAR2(30);
391 l_pickupStopId NUMBER;
392 l_dropoffStopId NUMBER;
393 l_TripId NUMBER;
394 l_numWarnings NUMBER := 0;
395 l_numErrors NUMBER := 0;
396 l_msgSubject VARCHAR2(1000);
397 l_msgFailSubject VARCHAR2(1000);
398 l_failReason VARCHAR2(32767);
399 l_routrespNum NUMBER;
400 l_performer VARCHAR2(30);
401 l_delIdTab WSH_UTIL_CORE.ID_TAB_TYPE;
402 l_tripIdTab WSH_UTIL_CORE.ID_TAB_TYPE;
403 l_delPlanFlag VARCHAR2(1);
404 l_tripPlanFlag VARCHAR2(1);
405 l_retStatus VARCHAR2(10);
406 l_planDelSts BOOLEAN := TRUE;
407 --
408 l_debugOn BOOLEAN;
409 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.PreNotification';
410 --
411 -- Get the routing request Ids associated with the delivery
412 --
413 CURSOR c_RoutReqId(p_delId IN NUMBER) IS
414 SELECT DISTINCT routing_req_id
415 FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
416 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
417 AND wda.delivery_id = p_delId;
418 --
419 -- Given a routing request Id, get the routing request number
420 --
421 CURSOR c_RoutReqNum(p_routreqId IN NUMBER) IS
422 SELECT receipt_number
423 FROM wsh_inbound_txn_history wth
424 WHERE wth.transaction_id = p_routreqId
425 AND wth.transaction_type = 'ROUTING_REQUEST';
426 --
427 -- Get the pickup stop Id, trip Id
428 --
429 CURSOR c_GetPickupStopId(p_delId IN NUMBER) IS
430 SELECT wdl.pick_up_stop_id, wt.trip_id, wnd.name, wt.planned_flag, wnd.planned_flag
431 FROM wsh_trip_stops wts, wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_Trips wt
432 WHERE wnd.delivery_id = p_delId
433 and wnd.delivery_id = wdl.delivery_id
434 and wdl.pick_up_stop_id = wts.stop_id
435 and wnd.initial_pickup_location_id = wts.stop_location_id
436 and wts.trip_id = wt.trip_id;
437 --
438 -- Get the drop off Stop Id
439 --
440 CURSOR c_GetDropoffStopId(p_delId IN NUMBER) IS
441 SELECT nvl(wts.physical_stop_id,drop_off_stop_id) drop_off_stop_id
442 FROM wsh_trip_stops wts, wsh_new_deliveries wnd, wsh_delivery_legs wdl
443 WHERE wnd.delivery_id = p_delId
444 and wnd.delivery_id = wdl.delivery_id
445 and wdl.drop_off_stop_id = wts.stop_id
446 and wnd.ultimate_dropoff_location_id = wts.stop_location_id;
447 --
448 BEGIN
449 --
450 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
451 --
452 IF l_debugOn IS NULL THEN
453 l_debugOn := wsh_debug_sv.is_debug_enabled;
454 END IF;
455 --
456 IF l_debugOn THEN
457 wsh_debug_sv.push(l_moduleName);
458 wsh_debug_sv.log(l_moduleName, 'itemtype', itemtype);
459 wsh_debug_sv.log(l_moduleName, 'itemKey', itemKey);
460 wsh_debug_sv.log(l_moduleName, 'actid', actid);
461 wsh_debug_sv.log(l_moduleName, 'funcmode', funcmode);
462 END IF;
463 --
464 IF (funcmode = 'RUN') THEN
465 --{
466 l_deliveryId := WF_ENGINE.GetItemAttrNumber(itemtype, itemkey, 'DELIVERY_ID');
467 l_orgId := WF_ENGINE.GetItemAttrNumber(itemtype, itemKey, 'ORG_ID');
468 l_routrespNum := WF_ENGINE.GetItemAttrText(itemtype, itemKey, 'ROUT_RESP_NUM');
469 l_performer := WF_ENGINE.GetItemAttrText(itemtype, itemKey, 'TO_USER');
470 --
471 -- Check whether the to role is NULL. If yes, then the WF cannot progress
472 --
473 IF l_performer IS NULL THEN
474 --
475 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_NO_PERFORMER');
476 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
477 --
478 END IF;
479 --
480 -- Lock the delivery
481 --
482 IF LockDelivery(l_deliveryId) THEN
483 --{
484 IF l_debugOn THEN
485 wsh_debug_sv.logmsg(l_moduleName, 'Delivery ' || l_deliveryID || ' locked Successfully');
486 wsh_debug_sv.log(l_moduleName, 'Org ID', l_orgId);
487 wsh_debug_sv.log(l_moduleName, 'Routing response Number', l_routrespNum);
488 END IF;
489 --
490 -- Re-validate the delivery
491 --
492 ValidateDelivery(l_deliveryId, l_status);
493 --
494 wsh_util_core.api_post_call(p_return_status => l_Status,
495 x_num_warnings => l_numWarnings,
496 x_num_errors => l_numErrors,
497 p_raise_error_flag => FALSE);
498 --
499 IF l_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) OR
500 l_performer IS NULL THEN
501 --
502 IF l_debugOn THEN
503 wsh_debug_sv.logmsg(l_moduleName, 'Routing response cannot be generated');
504 wsh_debug_sv.log(l_moduleName, '# of error messages', FND_MSG_PUB.COUNT_MSG);
505 END IF;
506 --
507 -- Delivery failed validation (or) the to Role is null.
508 -- Set the failure notification subject and message body
509 --
510 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_GEN_FAILED_SUBJ');
511 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_deliveryId));
512 l_msgFailSubject := FND_MESSAGE.GET;
513 --
514 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_PRE_FAILURE_REASON');
515 l_failReason := FND_MESSAGE.GET;
516 --
517 FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP
518 --
519 l_failReason := l_failReason || FND_GLOBAL.local_chr(10) ||
520 FND_MSG_PUB.GET(p_encoded => FND_API.G_FALSE);
521 --
522 END LOOP;
523 --
524 IF l_debugOn THEN
525 wsh_debug_sv.log(l_moduleName, 'Failure Message subject', l_msgFailSubject);
526 wsh_debug_sv.log(l_moduleName, 'Body', l_failReason);
527 END IF;
528 --
529 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'SUBJECT_FAIL', l_msgFailSubject);
530 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'NOTFN_FAILED_BODY', l_failReason);
531 resultout := 'F';
532 --
533 ELSE
534 --
535 -- Delivery has been locked and has been determined to be valid
536 --
537 --{
538 IF l_debugOn THEN
539 wsh_debug_sv.logmsg(l_moduleName, 'Delivery is locked and is valid');
540 END IF;
541 --
542 -- Determine how many routing requests are tied to this delivery
543 --
544 OPEN c_RoutReqId(l_deliveryId);
545 FETCH c_RoutReqId BULK COLLECT INTO l_routReqIdTab;
546 --
547 IF c_RoutReqId%ROWCOUNT > 1 THEN
548 --
549 IF l_debugOn THEN
550 wsh_debug_sv.log(l_moduleName, '# of routing requests', c_RoutReqId%ROWCOUNT);
551 wsh_debug_sv.log(l_moduleName, 'l_routReqIdTab.COUNT', l_routReqIdTab.COUNT);
552 END IF;
553 --
554 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_MULTIPLE_RREQ');
555 FND_MESSAGE.SET_TOKEN('RESP_NUM', l_routrespNum);
556 l_msgSubject := FND_MESSAGE.GET;
557 --
558 END IF;
559 --
560 CLOSE c_RoutReqId;
561 --
562 -- Get the routing request number tied to the delivery
563 --
564 IF l_routReqIdTab.COUNT > 0 THEN
565 OPEN c_RoutReqNum(l_routReqIdTab(l_routReqIdTab.FIRST));
566 FETCH c_RoutReqNum INTO l_routReqNum;
567 CLOSE c_RoutReqNum;
568 END IF;
569 --
570 IF l_debugOn THEN
571 wsh_debug_sv.log(l_moduleName, 'Routing Request ID',
572 l_routReqIdTab(l_routReqIdTab.FIRST));
573 wsh_debug_sv.log(l_ModuleName, 'Routing Request Number', l_routReqNum);
574 END IF;
575 --
576 -- Get the highest routing response revision
577 -- and bump it up by 1
578 --
579 SELECT MAX(revision_number)
580 INTO l_revNum
581 FROM wsh_inbound_txn_history
582 WHERE shipment_header_id = l_deliveryId
583 AND transaction_type = 'ROUTING_RESPONSE'
584 AND status = 'GENERATED';
585 --
586 l_revNum := NVL(l_revNum, 0) + 1;
587 --
588 IF l_debugOn THEN
589 wsh_debug_sv.log(l_moduleName, 'Revision Number', l_revNum);
590 END IF;
591 --
592 -- Get the pickup and dropoff stop Ids
593 --
594 OPEN c_GetPickupStopId(l_deliveryId);
595 FETCH c_GetPickupStopId INTO l_pickupStopId, l_TripId,
596 l_DelName, l_delPlanFlag, l_tripPlanFlag;
597 CLOSE c_GetPickupStopId;
598 --
599 OPEN c_GetDropoffStopId(l_deliveryId);
600 FETCH c_GetDropoffStopId INTO l_dropOffStopId;
601 CLOSE c_GetDropoffStopId;
602 --
603 IF l_debugOn THEN
604 wsh_debug_sv.log(l_moduleName, 'Pickup Stop ID', l_pickupStopId);
605 wsh_debug_sv.log(l_moduleName, 'Dropoff Stop Id', l_dropoffStopId);
606 wsh_debug_sv.log(l_moduleName, 'Trip Id', l_TripId);
607 wsh_debug_sv.log(l_moduleName, 'Delivery Name', l_delName);
608 wsh_debug_sv.log(l_moduleName, 'Delivery Plan Flag', l_delPlanFlag);
609 wsh_debug_sv.log(l_moduleName, 'Trip Plan Flag', l_tripPlanFlag);
610 END IF;
611 --
612 -- Bug 317799 : If delivery/trip is not planned,
613 -- go ahead and plan them
614 --
615 IF NVL(l_delPlanFlag, 'N') NOT IN ('F', 'P') THEN
616 --{
617 l_delIdTab(l_delIdTab.COUNT + 1) := l_deliveryId;
618 WSH_NEW_DELIVERY_ACTIONS.Plan(p_del_rows => l_delIdTab,
619 x_return_status => l_retStatus);
620 --
621 wsh_util_core.api_post_call(p_return_status => l_retStatus,
622 x_num_warnings => l_numWarnings,
623 x_num_errors => l_numErrors,
624 p_raise_error_flag => FALSE);
625 --
626 IF l_retStatus <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
627 --{
628 l_planDelSts := FALSE;
629 --
630 IF l_debugOn THEN
631 wsh_debug_sv.logmsg(l_moduleName, 'Error in Plan Delivery API');
632 END IF;
633 --
634 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_GEN_FAILED_SUBJ');
635 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_deliveryId));
636 l_msgFailSubject := FND_MESSAGE.GET;
637 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_PRE_FAILURE_REASON');
638 l_failReason := FND_MESSAGE.GET;
639 --
640 FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP
641 l_failReason := l_failReason || FND_GLOBAL.local_chr(10) ||
642 FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
643 END LOOP;
644 --}
645 END IF;
646 --}
647 END IF;
648 --
649 IF l_planDelSts AND
650 NVL(l_tripPlanFlag, 'N') NOT IN ('F', 'P') THEN
651 --{
652 l_tripIdTab(l_tripIdTab.COUNT+1) := l_tripId;
653 WSH_TRIPS_ACTIONS.Plan(p_trip_rows => l_tripIdTab,
654 p_action => 'PLAN',
655 x_return_status => l_retStatus);
656 --
657 wsh_util_core.api_post_call(p_return_status => l_retStatus,
658 x_num_warnings => l_numWarnings,
659 x_num_errors => l_numErrors,
660 p_raise_error_flag => FALSE);
661 --
662 IF l_retStatus NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
663 --{
664 IF l_debugOn THEN
665 wsh_debug_sv.logmsg(l_moduleName, 'Error in TripPlan API');
666 END IF;
667 --
668 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_PLAN_ERROR');
669 FND_MESSAGE.SET_TOKEN('TRIP_NAME', WSH_TRIPS_PVT.Get_Name(l_tripId));
670 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
671 --}
672 END IF;
673 --}
674 END IF;
675 --
676 IF l_debugOn THEN
677 wsh_debug_sv.log(l_moduleName, 'Message Subject', l_msgSubject);
678 wsh_debug_sv.log(l_moduleName, 'Message Fail Subject', l_msgFailSubject);
679 wsh_debug_sv.log(l_ModuleName, 'Failure Reason', l_failReason);
680 wsh_debug_sv.log(l_moduleName, 'WF_HEADER_ATTR', wf_core.translate('WF_HEADER_ATTR'));
681 wsh_debug_sv.log(l_moduleName, 'l_planDelSts', l_planDelSts);
682 END IF;
683 --
684 IF NOT l_planDelSts THEN
685 --{
686 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'SUBJECT_FAIL', l_msgFailSubject);
687 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'NOTFN_FAILED_BODY', l_failReason);
688 resultout := 'F';
689 --}
690 ELSE
691 --{
692 -- Delivery is tied to only one routing request
693 --
694 IF l_msgSubject IS NULL THEN
695 --
696 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_SINGLE_RREQ');
697 FND_MESSAGE.SET_TOKEN('RESP_NUM', l_routrespNum);
698 FND_MESSAGE.SET_TOKEN('REQ_NUM', l_routreqNum);
699 l_msgSubject := FND_MESSAGE.GET;
700 --
701 END IF;
702 --
703 IF l_debugOn THEN
704 wsh_debug_sv.logmsg(l_moduleName, 'No failures or errors');
705 wsh_debug_sv.log(l_moduleName, 'E-mail Subject', l_msgSubject);
706 END IF;
707 --
708 -- Set the WF item attributes for the routing response OA
709 -- region to be rendered
710 --
711 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'ROUT_REQ_NUM',l_routreqNum);
712 WF_ENGINE.SetItemAttrNumber(itemType, itemKey, 'P_STOPID', l_pickupStopId);
713 WF_ENGINE.SetItemAttrNumber(itemType, itemKey, 'D_STOPID', l_dropoffStopId);
714 WF_ENGINE.SetItemAttrNumber(itemType, itemKey, 'TRIP_ID', l_TripId);
715 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'SUBJECT', l_msgSubject);
716 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'REVISION_NUM', l_revNum);
717 --
718 IF nvl(wf_core.translate('WF_HEADER_ATTR'),'N') = 'Y' THEN
719 --
720 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'DELIVERY_NAME',l_DelName);
721 --
722 END IF;
723 --
724 resultout := 'T';
725 --}
726 END IF;
727 --}
728 END IF;
729 --}
730 ELSE
731 --
732 -- Delivery could not be locked, so set error message
733 --
734 --{
735 IF l_debugOn THEN
736 wsh_debug_sv.logmsg(l_moduleName, 'Delivery ' || l_deliveryId || ' not locked');
737 END IF;
738 --
739 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_GEN_FAILED_SUBJ');
740 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_deliveryId));
741 l_msgFailSubject := FND_MESSAGE.GET;
742 --
743 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_PRE_DEL_LOCKED');
744 l_failReason := FND_MESSAGE.GET;
745 --
746 IF l_debugOn THEN
747 wsh_debug_sv.log(l_moduleName, 'Failure Message subject', l_msgFailSubject);
748 wsh_debug_sv.log(l_moduleName, 'Body', l_failReason);
749 END IF;
750 --
751 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'SUBJECT_FAIL', l_msgFailSubject);
752 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'NOTFN_FAILED_BODY', l_failReason);
753 --
754 resultout := 'F';
755 --}
756 END IF;
757 --}
758 END IF;
759 --
760 IF (funcmode = 'CANCEL') THEN
761 resultout := 'T';
762 END IF;
763 --
764 IF (funcmode = 'TIMEOUT') THEN
765 resultout := 'F';
766 END IF;
767 --
768 IF l_debugOn THEN
769 wsh_debug_sv.log(l_moduleName, 'resultout', resultout);
770 wsh_debug_sv.pop(l_moduleName);
771 END IF;
772 --
773 EXCEPTION
774 --
775 WHEN OTHERS THEN
776 --
777 resultout := 'F';
778 wf_core.context('WSH_ROUTING_RESPONSE_PKG','PreNotification',
779 itemtype, itemkey,TO_CHAR(actid),funcmode);
780 --
781 IF l_debugOn THEN
782 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,200));
783 wsh_debug_sv.log(l_moduleName, 'resultout', resultout);
784 wsh_debug_sv.pop(l_moduleName);
785 END IF;
786 --
787 END PreNotification;
788
789
790 --
791 -- Pre-reqs : None
792 --
793 -- Parameters
794 -- itemtype - represents the WF item type. In this case, it is FTERRESP
795 -- itemKey - identifies the unique item Key. In this case, it is the transaction_id
796 -- from wsh_inbound_txn_history
797 -- actid - Standard WF attribute to represent the activity ID inside the process
798 -- funcmode - Standard WF attribute representing the fuction execution mode.
799 -- resultout - Standard WF attribute that represents the return status of this API
800 --
801 -- Purpose : This is the API that is called as part of the actual routing response WF.
802 -- Control would be transferred to this API after the actual notification
803 -- has been fired. This API in turn calls procedure UpdateTxnHistory to
804 -- update WSH_NEW_DELIVERIES and WSH_INBOUND_TXN_HISTORY
805 --
806 -- Version : 1.0
807 --
808 PROCEDURE PostNotification(itemtype IN VARCHAR2,
809 itemkey IN VARCHAR2,
810 actid IN NUMBER,
811 funcmode IN VARCHAR2,
812 resultout OUT NOCOPY VARCHAR2) IS
813 --
814 l_deliveryId NUMBER;
815 l_TxnId NUMBER;
816 l_RevNum NUMBER;
817 l_RespNum NUMBER;
818 l_Status VARCHAR2(10);
819 v_ErrCount NUMBER;
820 l_failReason VARCHAR2(2000);
821 l_msgFailSubject VARCHAR2(1000);
822 --
823 l_debugOn BOOLEAN;
824 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.PostNotification';
825 --
826 BEGIN
827 --
828 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
829 --
830 IF l_debugOn IS NULL THEN
831 l_debugOn := wsh_debug_sv.is_debug_enabled;
832 END IF;
833 --
834 IF l_debugOn THEN
835 wsh_debug_sv.push(l_moduleName);
836 wsh_debug_sv.log(l_moduleName, 'itemtype', itemtype);
837 wsh_debug_sv.log(l_moduleName, 'itemKey', itemKey);
838 wsh_debug_sv.log(l_moduleName, 'actid', actid);
839 wsh_debug_sv.log(l_moduleName, 'funcmode', funcmode);
840 END IF;
841 --
842 IF (funcmode = 'RUN') THEN
843 --
844 l_deliveryId := WF_ENGINE.GetItemAttrNumber(itemtype, itemkey, 'DELIVERY_ID');
845 l_TxnId := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'TRANSACTION_ID');
846 l_revNum := WF_ENGINE.GetItemAttrText(itemtype, itemKey, 'REVISION_NUM');
847 l_respNum := WF_ENGINE.GetItemAttrText(itemtype, itemKey, 'ROUT_RESP_NUM');
848 --
849 IF l_debugOn THEN
850 wsh_debug_sv.log(l_moduleName, 'l_deliveryId', l_deliveryId);
851 wsh_debug_sv.log(l_moduleName, 'l_TxnId', l_TxnId);
852 wsh_debug_sv.log(l_moduleName, 'l_revNum', l_revNum);
853 wsh_debug_sv.log(l_moduleName, 'l_respNum', l_respNum);
854 END IF;
855 --
856 -- Call API to perform updates
857 --
858 UpdateTxnHistory(p_deliveryId => l_deliveryId,
859 p_TxnId => l_TxnId,
860 p_RevNum => l_RevNum,
861 x_Status => l_Status);
862 --
863 IF l_Status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
864 resultout := 'T';
865 ELSE
866 --
867 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_POST_FAILED_SUBJ');
868 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_deliveryId));
869 l_msgFailSubject := FND_MESSAGE.GET;
870 --
871 FND_MESSAGE.SET_NAME('FTE', 'FTE_RRESP_POST_FAILURE_REASON');
872 l_failReason := FND_MESSAGE.GET;
873 --
874 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'SUBJECT_FAIL', l_msgFailSubject);
875 WF_ENGINE.SetItemAttrText(itemType, itemKey, 'NOTFN_FAILED_BODY', l_failReason);
876 resultout := 'F';
877 --
878 END IF;
879 --
880 END IF;
881 --
882 IF (funcmode = 'CANCEL') THEN
883 resultout := 'T';
884 END IF;
885 --
886 IF (funcmode = 'TIMEOUT') THEN
887 resultout := 'F';
888 END IF;
889 --
890 IF l_debugOn THEN
891 wsh_debug_sv.log(l_moduleName, 'resultout', resultout);
892 wsh_debug_sv.pop(l_moduleName);
893 END IF;
894 --
895 EXCEPTION
896 --
897 WHEN OTHERS THEN
898 --
899 resultout := 'F';
900 wf_core.context('WSH_ROUTING_RESPONSE_PKG','PostNotification',
901 itemtype, itemkey,TO_CHAR(actid),funcmode);
902 --
903 IF l_debugOn THEN
904 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,100));
905 wsh_debug_sv.log(l_moduleName, 'resultout', resultout);
906 wsh_debug_sv.pop(l_moduleName);
907 END IF;
908 --
909 END PostNotification;
910
911
912 --
913 -- Pre-reqs : None
914 --
915 -- Parameters
916 -- p_userId - represents the Apps User Id
917 --
918 -- Purpose : This function is used to determine the sender of the routing
919 -- response notification. Given the Apps User Id, we obtain
920 -- the Apps User name and this is used as the From Role of the
921 -- WF item.
922 --
923 -- Version : 1.0
924 --
925 FUNCTION GetFromRole(p_UserId IN NUMBER) RETURN VARCHAR2 IS
926 --
927 CURSOR c_GetUser is
928 SELECT user_name
929 FROM fnd_user
930 WHERE user_id = p_UserId;
931 --
932 v_Name fnd_user.user_name%TYPE;
933 --
934 l_debugOn BOOLEAN;
935 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.GetFromRole';
936 --
937 BEGIN
938 --
939 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
940 --
941 IF l_debugOn IS NULL THEN
942 l_debugOn := wsh_debug_sv.is_debug_enabled;
943 END IF;
944 --
945 IF l_debugOn THEN
946 wsh_debug_sv.push(l_moduleName);
947 wsh_debug_sv.log(l_moduleName, 'User Id', p_userId);
948 END IF;
949 --
950 OPEN c_GetUser;
951 FETCH c_GetUser INTO v_Name;
952 --
953 IF c_GetUser%NOTFOUND THEN
954 v_Name := NULL;
955 END IF;
956 --
957 CLOSE c_GetUser;
958 --
959 IF l_debugOn THEN
960 wsh_Debug_sv.log(l_moduleName, 'v_Name', v_Name);
961 wsh_debug_sv.pop(l_moduleName);
962 END IF;
963 --
964 RETURN (v_Name);
965 --
966 EXCEPTION
967 --
968 WHEN OTHERS THEN
969 --
970 v_Name := NULL;
971 --
972 wsh_util_core.default_handler('WSH_ROUTING_RESPONSE_PKG.GetFromRole');
973 IF l_debugOn THEN
974 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM, 1, 200));
975 wsh_debug_sv.pop(l_moduleName);
976 END IF;
977 --
978 RAISE;
979 --
980 END GetFromRole;
981
982
983 --
984 -- Pre-reqs : None
985 --
986 -- Parameters
987 -- p_delivId - represents the delivery Id for which the routing response
988 -- is being generated.
989 --
990 -- Purpose : This function is used to determine the recipient of the routing
991 -- response notification. This API assumes that the concurrent
992 -- program "Synchronize Local WF tables" has been run to transfer
993 -- data from TCA tables to WF tables.
994 --
995 -- The following logic is implemented in this API.
996 -- (1) Obtain the vendor Id and pickup location Id for the delivery.
997 -- (2) Obtain the party relationship Id from the TCA tables to
998 -- the pickup location Id and delivery party Id.
999 -- (3) The To Role attribute of the WF item is then set to
1000 -- 'HZ_PARTY:' + party relationship Id.
1001 --
1002 -- Version : 1.0
1003 --
1004 FUNCTION GetToRole(p_delivId IN NUMBER) RETURN VARCHAR2 IS
1005 --
1006 CURSOR c_DelInfo IS
1007 SELECT initial_pickup_location_id, party_id, vendor_id, name -- IB-Phase-2
1008 FROM wsh_new_deliveries
1009 WHERE delivery_id = p_delivId;
1010 --
1011 CURSOR c_GetShipperPartyRelId(p_locId NUMBER, p_delpartyId NUMBER) IS
1012 SELECT hrel.party_id, contact_person.party_id, email_record.email_address, hrel.end_date
1013 FROM hz_party_sites hps,
1014 hz_party_site_uses hpsu,
1015 hz_parties contact_person,
1016 hz_org_contacts supplier_contact,
1017 hz_contact_points phone_record,
1018 hz_contact_points email_record,
1019 hz_relationships hrel
1020 WHERE hps.party_site_id = hpsu.party_site_id
1021 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM'
1022 AND hrel.subject_id = contact_person.party_id
1023 AND hrel.subject_table_name = 'HZ_PARTIES'
1024 AND hrel.subject_type = 'PERSON'
1025 AND hrel.object_id = hps.party_id
1026 AND hrel.object_table_name = 'HZ_PARTIES'
1027 AND hrel.object_type = 'ORGANIZATION'
1028 AND hrel.relationship_code = 'CONTACT_OF'
1029 AND hrel.directional_flag = 'F'
1030 AND supplier_contact.party_relationship_id =hrel.relationship_id
1031 AND supplier_contact.party_site_id = hps.party_site_id
1032 AND phone_record.owner_table_name(+) = 'HZ_PARTIES'
1033 AND phone_record.owner_table_id(+) = hrel.party_id
1034 AND phone_record.contact_point_type(+) = 'PHONE'
1035 AND email_record.owner_table_name = 'HZ_PARTIES'
1036 AND email_record.owner_table_id = hrel.party_id
1037 AND email_record.contact_point_type = 'EMAIL'
1038 AND hps.location_id = p_locId
1039 AND hps.party_id = p_delpartyId;
1040 --
1041 CURSOR c_GetWFRole(p_relId NUMBER) IS
1042 SELECT display_name
1043 FROM wf_roles
1044 WHERE name = 'HZ_PARTY:' || p_relId;
1045 --
1046 CURSOR c_GetPOUser(p_vendorID NUMBER) IS
1047 SELECT fu.user_name, fu.email_address
1048 FROM fnd_user fu, hz_relationships hz
1049 WHERE hz.subject_id = p_vendorId
1050 AND hz.object_id = fu.person_party_id --IB-phase-2 Vendor Merge
1051 AND hz.subject_type = 'ORGANIZATION'
1052 AND hz.object_type = 'PERSON'
1053 AND hz.relationship_type = 'POS_EMPLOYMENT'
1054 AND hz.relationship_code = 'EMPLOYER_OF'
1055 AND hz.subject_table_name = 'HZ_PARTIES'
1056 AND hz.object_table_name = 'HZ_PARTIES'
1057 AND hz.status = 'A'
1058 AND hz.start_date <= sysdate
1059 AND hz.end_date >= sysdate;
1060 --
1061 CURSOR c_GetTxnId(p_delId NUMBER) IS
1062 SELECT transaction_id
1063 FROM wsh_inbound_txn_history
1064 WHERE shipment_header_id = p_delId
1065 AND transaction_type = 'ROUTING_RESPONSE'
1066 ORDER BY revision_number DESC;
1067 --
1068 v_LocId NUMBER;
1069 v_PartyId NUMBER;
1070 v_VendorId NUMBER;
1071 v_Name VARCHAR2(30);
1072 v_FndUserName VARCHAR2(30);
1073 v_relId NUMBER;
1074 v_hzPartyId NUMBER;
1075 v_DocId NUMBER;
1076 v_DocType VARCHAR2(30);
1077 v_SupEmail VARCHAR2(2000);
1078 v_POEmail VARCHAR2(2000);
1079 --
1080 i NUMBER;
1081 l_roleName VARCHAR2(30) := 'WSH_RRESP_ROLE';
1082 l_displayName VARCHAR2(30) := 'WSH_RRESP_ROLE';
1083 l_UserList VARCHAR2(32767);
1084 l_TxnId NUMBER;
1085 l_relationship_end_date DATE; --IB Phase 2
1086 l_del_name VARCHAR2(30);
1087 l_end_date DATE;
1088 --
1089 l_debugOn BOOLEAN;
1090 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.GetToRole';
1091 --
1092 BEGIN
1093 --
1094 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
1095 --
1096 IF l_debugOn IS NULL THEN
1097 l_debugOn := wsh_debug_sv.is_debug_enabled;
1098 END IF;
1099 --
1100 IF l_debugOn THEN
1101 wsh_debug_sv.push(l_moduleName);
1102 wsh_debug_sv.log(l_moduleName, 'Delivery Id', p_delivId);
1103 END IF;
1104 --
1105 -- Get the transaction Id associated with the delivery
1106 --
1107 OPEN c_GetTxnId(p_delivId);
1108 FETCH c_GetTxnId INTO l_txnId;
1109 CLOSE c_GetTxnId;
1110 --
1111 l_roleName := l_roleName || '-' || l_txnId;
1112 --
1113 -- Get the pickup location, party Id and vendor Id for delivery
1114 --
1115 OPEN c_DelInfo;
1116 FETCH c_DelInfo INTO v_LocId, v_PartyId, v_VendorId,l_del_name; --IB Phase 2
1117 --
1118 IF c_DelInfo%NOTFOUND THEN
1119 v_LocId := NULL;
1120 v_PartyId := NULL;
1121 v_VendorId := NULL;
1122 END IF;
1123 --
1124 CLOSE c_DelInfo;
1125 --
1126 IF l_debugOn THEN
1127 wsh_debug_sv.log(l_moduleName, 'Pickup Location Id', v_LocId);
1128 wsh_debug_sv.log(l_moduleName, 'Delivery Party ID', v_PartyId);
1129 wsh_debug_sv.log(l_moduleName, 'Vendor ID', v_VendorId);
1130 wsh_debug_sv.log(l_moduleName, 'Role Name', l_roleName);
1131 END IF;
1132 --
1133 -- Get the party relationship Id
1134 --
1135 IF v_LocId IS NOT NULL AND
1136 v_PartyId IS NOT NULL THEN
1137 --
1138 OPEN c_GetShipperPartyRelId(v_LocId, v_PartyId);
1139 FETCH c_GetShipperPartyRelId INTO v_relId, v_hzPartyId, v_SupEmail,l_relationship_end_date; -- IB-Phase-2
1140 --
1141 IF c_GetShipperPartyRelId%NOTFOUND THEN
1142 v_relId := NULL;
1143 v_hzPartyId := NULL;
1144 v_SupEmail := NULL;
1145 END IF;
1146 --
1147 CLOSE c_GetShipperPartyRelId;
1148 --
1149 END IF;
1150 --
1151 IF l_debugOn THEN
1152 wsh_debug_sv.log(l_moduleName, 'Relationship ID', v_relId);
1153 wsh_debug_sv.log(l_moduleName, 'Hz Party ID', v_hzPartyId);
1154 wsh_debug_sv.log(l_moduleName, 'Shipper Email', v_SupEmail);
1155 wsh_debug_sv.log(l_moduleName, 'Relationship End Date', l_end_date);
1156 END IF;
1157
1158 -- { IB-Phase-2
1159 IF l_relationship_end_date < SYSDATE
1160 THEN
1161 FND_MESSAGE.SET_NAME('WSH', 'WSH_SUPP_CONTACT_INACTIVE');
1162 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',l_del_name);
1163 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
1164 raise FND_API.G_EXC_ERROR;
1165 END IF;
1166 -- } IB-Phase-2
1167
1168 --
1169 -- Build the to_role
1170 --
1171 IF v_relId IS NOT NULL THEN
1172 v_Name := 'HZ_PARTY:' || v_relId;
1173 ELSE
1174 v_Name := NULL;
1175 END IF;
1176 --
1177 -- Get the email address of the vendor
1178 --
1179 IF v_VendorId IS NOT NULL THEN
1180 --{
1181 OPEN c_GetPOUser(v_VendorId);
1182 FETCH c_GetPOUser INTO v_FndUserName, v_POEmail;
1183 --
1184 IF c_GetPOUser%NOTFOUND THEN
1185 v_FndUserName := NULL;
1186 v_POEmail := NULL;
1187 END IF;
1188 --
1189 CLOSE c_GetPOUser;
1190 --
1191 IF l_debugOn THEN
1192 wsh_debug_sv.log(l_moduleName, 'User Name for Vendor', v_FndUserName);
1193 wsh_debug_sv.log(l_moduleName, 'Email address', v_POEmail);
1194 END IF;
1195 --}
1196 END IF;
1197 --
1198 -- If vendor email address matches the email
1199 -- address that was uploaded from teh routing request
1200 -- create a adhoc role and set the to Role attribute
1201 -- as this adhoc role.
1202 --
1203 IF v_POEmail IS NOT NULL AND
1204 v_SupEmail IS NOT NULL AND
1205 v_POEmail = v_SupEmail THEN
1206 --{
1207 l_UserList := v_Name || ',' || v_FndUserName;
1208 --
1209 IF l_debugOn THEN
1210 wsh_debug_sv.log(l_moduleName, 'User List', l_UserList);
1211 wsh_debug_sv.log(l_moduleName, 'Email', v_SupEmail);
1212 wsh_debug_sv.logmsg(l_moduleName, 'Calling WF API');
1213 END IF;
1214 --
1215 -- Create adhoc role
1216 --
1217 WF_DIRECTORY.CreateAdHocRole(role_name => l_roleName,
1218 role_display_name => l_displayName,
1219 role_users => l_UserList,
1220 email_address => v_SupEmail,
1221 expiration_date => g_RoleExpDate);
1222 --}
1223 ELSE
1224 --{
1225 IF l_debugOn THEN
1226 wsh_debug_sv.logmsg(l_moduleName, 'Only one user to be notified');
1227 END IF;
1228 --
1229 l_roleName := v_Name;
1230 --}
1231 END IF;
1232 --
1233 IF l_debugOn THEN
1234 wsh_Debug_sv.log(l_moduleName, 'v_Name', v_Name);
1235 wsh_debug_sv.log(l_moduleName, 'l_userList', l_UserList);
1236 wsh_debug_sv.log(l_moduleName, 'l_roleName', l_roleName);
1237 wsh_debug_sv.pop(l_moduleName);
1238 END IF;
1239 --
1240 RETURN (l_roleName);
1241 --
1242 EXCEPTION
1243 --
1244 WHEN FND_API.G_EXC_ERROR THEN
1245 --
1246 --
1247 IF l_debugOn THEN
1248 wsh_debug_sv.logmsg(l_moduleName,'FND_API.G_EXC_ERROR exception has occured.',
1249 wsh_debug_sv.C_EXCEP_LEVEL);
1250 wsh_debug_sv.pop(l_moduleName,'EXCEPTION:FND_API.G_EXC_ERROR');
1251 END IF;
1252 --
1253 --
1254
1255 WHEN OTHERS THEN
1256 --
1257 l_roleName := NULL;
1258 --
1259 wsh_util_core.default_handler('WSH_ROUTING_RESPONSE_PKG.GetToRole', l_moduleName);
1260 IF l_debugOn THEN
1261 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM, 1, 200));
1262 wsh_debug_sv.pop(l_moduleName);
1263 END IF;
1264 --
1265 RAISE;
1266 --
1267 END GetToRole;
1268
1269
1270 --
1271 -- Pre-reqs : None
1272 --
1273 -- Parameters
1274 -- p_delId - represents the delivery Id for which the routing response
1275 -- is being generated.
1276 -- x_TxnId - transaction_id from WSH_INBOUND_TXN_HISTORY
1277 -- x_RespNum - routing response number
1278 -- x_DelName - Delivery Name
1279 -- x_Status - Return status of this API.
1280 --
1281 -- Purpose : This API is used to create a record in WSH_INBOUND_TXN_HISTORY
1282 -- to indicate that the routing response WF was triggered successfully.
1283 --
1284 -- Version : 1.0
1285 --
1286 PROCEDURE CreateTxnHistory(p_delId IN NUMBER,
1287 x_TxnId OUT NOCOPY NUMBER,
1288 x_RespNum OUT NOCOPY NUMBER,
1289 x_DelName OUT NOCOPY VARCHAR2,
1290 x_Status OUT NOCOPY VARCHAR2) IS
1291 --
1292 l_receiptNum NUMBER;
1293 l_txnId NUMBER;
1294 l_txnHistoryRec WSH_INBOUND_TXN_HISTORY_PKG.ib_txn_history_rec_type;
1295 l_Status VARCHAR2(10);
1296 l_revNum NUMBER;
1297 l_numWarnings NUMBER;
1298 l_numErrors NUMBER;
1299 l_orgId NUMBER;
1300 l_vendorId NUMBER;
1301 l_delivName VARCHAR2(30);
1302 --
1303 l_debugOn BOOLEAN;
1304 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CreateTxnHistory';
1305 --
1306 CURSOR c_DelivData IS
1307 SELECT name, organization_id, vendor_id
1308 FROM wsh_new_deliveries
1309 WHERE delivery_id = p_delId;
1310 --
1311 CURSOR c_ResponseNum IS
1312 SELECT receipt_number
1313 FROM wsh_inbound_txn_history
1314 WHERE shipment_header_id = p_delId
1315 ORDER BY receipt_date DESC;
1316 --
1317 BEGIN
1318 --
1319 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
1320 --
1321 IF l_debugOn IS NULL THEN
1322 l_debugOn := wsh_debug_sv.is_debug_enabled;
1323 END IF;
1324 --
1325 IF l_debugOn THEN
1326 wsh_debug_sv.push(l_moduleName);
1327 wsh_debug_sv.log(l_moduleName, 'Delivery Id', p_delId);
1328 END IF;
1329 --
1330 -- Get the routing response number
1331 --
1332 OPEN c_ResponseNum;
1333 FETCH c_ResponseNum INTO l_receiptNum;
1334 --
1335 IF l_debugOn THEN
1336 wsh_debug_sv.log(l_moduleName, 'c_ResponseNum%ROWCOUNT', c_ResponseNum%ROWCOUNT);
1337 wsh_debug_sv.log(l_moduleName, 'l_receiptNum', l_receiptNum);
1338 END IF;
1339 --
1340 IF c_ResponseNum%NOTFOUND THEN
1341 SELECT WSH_ROUTING_RESPONSE_S.nextval INTO l_receiptNum FROM dual;
1342 END IF;
1343 --
1344 CLOSE c_ResponseNum;
1345 --
1346 IF l_debugOn THEN
1347 wsh_debug_sv.log(l_moduleName, 'Receipt Number', l_receiptNum);
1348 END IF;
1349 --
1350 x_Status := WSH_UTIL_CORE.g_RET_STS_SUCCESS;
1351 --
1352 -- Obtain the delivery name, org Id and vendor Id
1353 --
1354 OPEN c_DelivData;
1355 FETCH c_DelivData INTO l_delivName, l_orgId, l_vendorId;
1356 CLOSE c_DelivData;
1357 --
1358 IF l_debugOn THEN
1359 wsh_debug_sv.log(l_moduleName, 'Delivery name', l_delivName);
1360 wsh_debug_sv.log(l_moduleName, 'Org ID', l_orgId);
1361 wsh_debug_sv.log(l_moduleName, 'Vendor ID', l_vendorId);
1362 END IF;
1363 --
1364 -- Set the values of the WSH_INBOUND_TXN_HISTORY record structure
1365 --
1366 l_txnHistoryRec.receipt_number := l_receiptNum;
1367 l_txnHistoryRec.revision_number := NULL;
1368 l_txnHistoryRec.shipment_number := l_delivName;
1369 l_txnHistoryRec.transaction_type := 'ROUTING_RESPONSE';
1370 l_txnHistoryRec.shipment_header_id := p_delId;
1371 l_txnHistoryRec.organization_id := l_orgId;
1372 l_txnHistoryRec.supplier_id := l_vendorId;
1373 l_txnHistoryRec.receipt_date := SYSDATE;
1374 l_txnHistoryRec.status := 'TRIGGERED';
1375 --
1376 IF l_debugOn THEN
1377 wsh_debug_sv.logmsg(l_moduleName,'** Input record to Create_txn_History **');
1378 wsh_debug_sv.log(l_moduleName,'Receipt Num',l_txnHistoryRec.receipt_number);
1379 wsh_debug_sv.log(l_moduleName, 'Rev Num',l_txnHistoryRec.revision_number);
1380 wsh_debug_sv.log(l_moduleName, 'Shpmt Num',l_txnHistoryRec.shipment_number);
1381 wsh_debug_sv.log(l_moduleName, 'Txn Type',l_txnHistoryRec.transaction_type);
1382 wsh_debug_sv.log(l_moduleName, 'Shp Hdr Id',
1383 l_txnHistoryRec.shipment_header_id);
1384 wsh_debug_sv.log(l_moduleName, 'Org Id', l_txnHistoryRec.organization_id);
1385 wsh_debug_sv.log(l_moduleName, 'Supplier Id', l_txnHistoryRec.supplier_id);
1386 wsh_debug_sv.log(l_moduleName, 'Receipt Date',
1387 to_char(l_txnHistoryRec.receipt_date,'DD-MON-YYYY HH24:MI:SS')); wsh_debug_sv.log(l_moduleName, 'Status', l_txnHistoryRec.status);
1388 END IF;
1389 --
1390 -- Call the table handler to create a record
1391 --
1392 WSH_INBOUND_TXN_HISTORY_PKG.create_txn_history(p_txn_history_rec => l_txnHistoryRec,
1393 x_txn_id => l_txnId,
1394 x_return_status => l_Status);
1395 --
1396 WSH_UTIL_CORE.api_post_call(p_return_status => l_Status,
1397 x_num_warnings => l_numWarnings,
1398 x_num_Errors => l_numErrors);
1399 --
1400 x_TxnId := l_txnId;
1401 x_RespNum := l_receiptNum;
1402 x_DelName := l_delivName;
1403 x_Status := l_Status;
1404 --
1405 IF l_debugOn THEN
1406 wsh_debug_sv.log(l_moduleName, 'Transaction ID', x_TxnId);
1407 wsh_debug_sv.log(l_moduleName, 'Delivery Name', x_DelName);
1408 wsh_debug_sv.log(l_moduleName, 'Routing Resp Number', x_RespNum);
1409 wsh_debug_sv.pop(l_moduleName);
1410 END IF;
1411 --
1412 EXCEPTION
1413 --
1414 WHEN FND_API.G_EXC_ERROR THEN
1415 --
1416 x_Status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1417 --
1418 IF l_debugOn THEN
1419 wsh_debug_sv.logmsg(l_moduleName, 'Expected error ' || SUBSTRB(SQLERRM,1,200));
1420 wsh_debug_sv.pop(l_moduleName);
1421 END IF;
1422 --
1423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1424 --
1425 x_Status := WSH_UTIL_CORE.g_RET_STS_UNEXP_ERROR;
1426 --
1427 IF l_debugOn THEN
1428 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,200));
1429 wsh_debug_sv.pop(l_moduleName);
1430 END IF;
1431 --
1432 WHEN OTHERS THEN
1433 --
1434 x_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1435 --
1436 wsh_util_core.default_handler('WSH_ROUTING_RESPONSE_PKG.CreateTxnHistory', l_moduleName);
1437 --
1438 IF l_debugOn THEN
1439 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,200));
1440 wsh_debug_sv.log(l_moduleName, 'Transaction ID', x_TxnId);
1441 wsh_debug_sv.log(l_moduleName, 'Revision Number', l_revNum);
1442 wsh_debug_sv.pop(l_moduleName);
1443 END IF;
1444 --
1445 RAISE;
1446 --
1447 END CreateTxnHistory;
1448
1449
1450 --
1451 -- Pre-reqs : None
1452 --
1453 -- Parameters
1454 -- p_delId - represents the delivery Id for which the routing response
1455 -- is being generated.
1456 -- p_TxnId - transaction_id from WSH_INBOUND_TXN_HISTORY
1457 -- p_RevNum - routing response revision number
1458 -- x_Status - Return status of this API.
1459 --
1460 -- Purpose : This API is used to update the record in WSH_INBOUND_TXN_HISTORY
1461 -- for this delivery to indicate that the routing response WF
1462 -- completed successfully. Also, an update to WSH_NEW_DELIVERIES
1463 -- is performed to set the routing_response_id.
1464 --
1465 -- Version : 1.0
1466 --
1467 PROCEDURE UpdateTxnHistory(p_deliveryId IN NUMBER,
1468 p_TxnId IN NUMBER,
1469 p_RevNum IN NUMBER,
1470 x_Status OUT NOCOPY VARCHAR2) IS
1471 --
1472 l_Status VARCHAR2(10);
1473 l_txnHistoryRec WSH_INBOUND_TXN_HISTORY_PKG.ib_txn_history_rec_type;
1474 l_numWarnings NUMBER;
1475 l_numErrors NUMBER;
1476 l_Date DATE := SYSDATE;
1477 --
1478 l_debugOn BOOLEAN;
1479 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UpdateTxnHistory';
1480 --
1481 BEGIN
1482 --
1483 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
1484 --
1485 IF l_debugOn IS NULL THEN
1486 l_debugOn := wsh_debug_sv.is_debug_enabled;
1487 END IF;
1488 --
1489 IF l_debugOn THEN
1490 wsh_debug_sv.push(l_moduleName);
1491 wsh_debug_sv.log(l_moduleName, 'Delivery Id', p_deliveryId);
1492 wsh_debug_sv.log(l_moduleName, 'Transaction ID', p_TxnId);
1493 wsh_debug_sv.log(l_moduleName, 'Revision Number', p_RevNum);
1494 wsh_debug_sv.log(l_moduleName, 'l_Date', to_char(l_Date, 'DD/MM/YYYY HH24:MI:SS'));
1495 END IF;
1496 --
1497 -- Update WSH_NEW_DELIVERIES
1498 --
1499 UPDATE wsh_new_deliveries
1500 SET routing_response_id = p_TxnId,
1501 last_update_date = l_Date
1502 WHERE delivery_id = p_deliveryId;
1503 --
1504 IF l_debugOn THEN
1505 wsh_debug_sv.logmsg(l_moduleName, 'No. of rows updated', SQL%ROWCOUNT);
1506 END IF;
1507 --
1508 -- Set the inbound txn history record components
1509 --
1510 l_txnHistoryRec.status := 'GENERATED';
1511 l_txnHistoryRec.receipt_date := l_Date;
1512 l_txnHistoryRec.revision_number := p_revNum;
1513 l_txnHistoryRec.transaction_id := p_txnId;
1514 l_txnHistoryRec.shipment_header_id := p_deliveryId;
1515 --
1516 IF l_debugOn THEN
1517 wsh_debug_sv.logmsg(l_moduleName,'** Input record to update_txn_History **');
1518 wsh_debug_sv.log(l_moduleName, 'Status', l_txnHistoryRec.status);
1519 wsh_debug_sv.log(l_moduleName, 'Receipt Date',
1520 to_char(l_txnHistoryRec.receipt_date,'DD-MON-YYYY HH24:MI:SS'));
1521 wsh_debug_sv.log(l_moduleName, 'Rev Num',l_txnHistoryRec.revision_number);
1522 wsh_debug_sv.log(l_moduleName, 'Txn ID',l_txnHistoryRec.transaction_id);
1523 wsh_debug_sv.log(l_moduleName, 'Shp Hdr Id',
1524 l_txnHistoryRec.shipment_header_id);
1525 END IF;
1526 --
1527 -- Call table handler to update existing record
1528 --
1529 WSH_INBOUND_TXN_HISTORY_PKG.update_txn_history(p_txn_history_rec => l_txnHistoryRec,
1530 x_return_status => l_Status);
1531 --
1532 wsh_util_core.api_post_call(p_return_status => l_Status,
1533 x_num_warnings => l_numWarnings,
1534 x_num_errors => l_numErrors);
1535 --
1536 x_Status := l_Status;
1537 --
1538 IF l_debugOn THEN
1539 wsh_debug_sv.log(l_moduleName, 'Return status after update_Txn_history', l_Status);
1540 wsh_debug_sv.pop(l_moduleName);
1541 END IF;
1542 --
1543 EXCEPTION
1544 --
1545 WHEN FND_API.G_EXC_ERROR THEN
1546 --
1547 x_Status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1548 --
1549 IF l_debugOn THEN
1550 wsh_debug_sv.logmsg(l_moduleName, 'Expected error ' || SUBSTRB(SQLERRM,1,200));
1551 wsh_debug_sv.pop(l_moduleName);
1552 END IF;
1553 --
1554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1555 --
1556 x_Status := WSH_UTIL_CORE.g_RET_STS_UNEXP_ERROR;
1557 --
1558 IF l_debugOn THEN
1559 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,200));
1560 wsh_debug_sv.pop(l_moduleName);
1561 END IF;
1562 --
1563 WHEN OTHERS THEN
1564 --
1565 x_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1566 --
1567 wsh_util_core.default_handler('WSH_ROUTING_RESPONSE_PKG.UpdateTxnHistory');
1568 IF l_debugOn THEN
1569 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected error ' || SUBSTRB(SQLERRM,1,200));
1570 wsh_debug_sv.pop(l_moduleName);
1571 END IF;
1572 --
1573 RAISE;
1574 --
1575 END UpdateTxnHistory;
1576
1577
1578 --
1579 -- Pre-reqs : None
1580 --
1581 -- Parameters
1582 -- p_delivery_id - represents the delivery Id for which PO need to validate.
1583 -- x_return_status - Return status of this API.
1584 --
1585 -- Purpose : This API is used to Validate an input delivery for
1586 -- sending Routing Response. Api validates
1587 -- 1.PO is in correct Hold and Approval status. PO should not be in Hold and Unapproved.
1588 -- 2.Delivery has first leg.
1589 -- 3.None Carrier, Service level or Mode of transport for the trip associated with delivery is not null.
1590 -- 4.
1591 -- Success: If trip load tender status in 'ACCEPTED','AUTO_ACCEPTED'.
1592 -- Warning: If trip load tender status in 'OPEN','SHIPPER_CANCELLED',NULL and carrier is Auto tender enable.
1593 -- Error: Else.
1594 -- Version : 1.0
1595 --
1596 PROCEDURE ValidateDelivery(p_delivery_id IN NUMBER,
1597 x_return_status OUT NOCOPY VARCHAR2) IS
1598 --Cursor to find ,if first leg is created for a delivery.
1599 CURSOR validate_first_leg_csr(cp_delivery_id NUMBER) IS
1600 SELECT wts.trip_id,wnd.organization_id,wnd.shipping_control
1601 FROM wsh_new_deliveries wnd,
1602 wsh_delivery_legs wdl,
1603 wsh_trip_stops wts
1604 WHERE wnd.delivery_id = p_delivery_id
1605 AND wnd.delivery_id = wdl.delivery_id
1606 AND wdl.pick_up_stop_id = wts.stop_id
1607 AND wnd.initial_pickup_location_id = wts.stop_location_id;
1608
1609
1610 --Cursor to find,if carrier is auto load tender enable.
1611 CURSOR validate_auto_tender_csr(p_carrier_id NUMBER,p_organization_id NUMBER) IS
1612 SELECT 1
1613 FROM hz_relationships rel,
1614 wsh_carrier_sites wcs,
1615 hz_party_sites hps,
1616 hz_org_contacts hoc,
1617 hz_contact_points hcp,
1618 hz_contact_points hcp2,
1619 hz_parties party_rel,
1620 wsh_org_carrier_sites wocs
1621 WHERE wcs.carrier_site_id = hps.party_site_id
1622 AND hps.party_id = rel.object_id
1623 AND hps.party_site_id = hoc.party_site_id
1624 AND hoc.party_relationship_id = rel.relationship_id
1625 AND party_rel.party_id = rel.subject_id
1626 AND hcp.owner_table_id = rel.party_id
1627 AND hcp.contact_point_type = 'EMAIL'
1628 AND hcp.owner_table_name = 'HZ_PARTIES'
1629 AND wcs.carrier_id= p_carrier_id
1630 AND hoc.decision_maker_flag = 'Y'
1631 AND hcp2.owner_table_id(+) = rel.party_id
1632 AND hcp2.contact_point_type(+) = 'PHONE'
1633 AND hcp2.owner_table_name(+) = 'HZ_PARTIES'
1634 AND wcs.carrier_site_id = wocs.carrier_site_id
1635 AND wocs.organization_id = p_organization_id;
1636
1637 --Cursor to get carrier,Mode of transport,Service level and load tender status for a trip.
1638 CURSOR validate_trip_csr(p_trip_id NUMBER) IS
1639 SELECT carrier_id,mode_of_transport,service_level, load_tender_status
1640 FROM wsh_trips
1641 WHERE trip_id = p_trip_id;
1642 --
1643
1644 --Cursor to get carrier name.
1645 CURSOR get_carrier_name(p_carrier_id NUMBER) IS
1646 SELECT party_name
1647 FROM wsh_carriers, hz_parties
1648 WHERE carrier_id =party_id
1649 AND carrier_id= p_carrier_id;
1650 --
1651
1652 l_trip_id number;
1653 l_organization_id number;
1654 l_carrier_id number;
1655 l_mode_of_transport varchar2(30);
1656 l_service_level varchar2(30);
1657 l_load_tender_status varchar2(30);
1658 l_email_address varchar2(2000);
1659 l_carrier_name varchar2(2000);
1660 l_return_status varchar2(1);
1661 l_tmp number;
1662 l_shipping_control varchar2(2000);
1663 --
1664 l_debugOn BOOLEAN;
1665 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ValidateDelivery';
1666 --
1667 BEGIN
1668 --
1669 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
1670 --
1671 IF l_debugOn IS NULL THEN
1672 l_debugOn := wsh_debug_sv.is_debug_enabled;
1673 END IF;
1674 --
1675 IF l_debugOn THEN
1676 wsh_debug_sv.push(l_moduleName);
1677 wsh_debug_sv.log(l_moduleName,'p_delivery_id',p_delivery_id);
1678 END IF;
1679 --
1680 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1681 --
1682
1683 --Validate PO is in correct Hold and Approval status. PO should not be in Hold and Unapproved.
1684 Validate_PO(
1685 p_delivery_id => p_delivery_id,
1686 x_return_status => l_return_status);
1687 --
1688 IF l_debugOn THEN
1689 wsh_debug_sv.log(l_moduleName,'l_return_status',l_return_status);
1690 END IF;
1691 --
1692 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1693 raise fnd_api.g_exc_error;
1694 END IF;
1695 --
1696
1697 --Determine,if delivery has fist leg.
1698 OPEN validate_first_leg_csr(p_delivery_id);
1699 FETCH validate_first_leg_csr INTO l_trip_id,l_organization_id,l_shipping_control;
1700 --
1701 IF l_debugOn THEN
1702 wsh_debug_sv.log(l_moduleName,'l_trip_id',l_trip_id);
1703 wsh_debug_sv.log(l_moduleName,'l_organization_id',l_organization_id);
1704 wsh_debug_sv.log(l_moduleName,'l_shipping_control',l_shipping_control);
1705 END IF;
1706 --
1707
1708
1709 --If delivery did not have first leg then error.
1710 IF (validate_first_leg_csr %NOTFOUND) THEN
1711 --
1712 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_NO_FIRST_LEG');
1713 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
1714 CLOSE validate_first_leg_csr;
1715 raise fnd_api.g_exc_error;
1716 --
1717 END IF;
1718 --
1719 CLOSE validate_first_leg_csr;
1720 --
1721
1722 --If transportation is not arranged by Buyer than error.
1723 IF (nvl(l_shipping_control,'$$$') <> 'BUYER') THEN
1724 FND_MESSAGE.SET_NAME('WSH','WSH_RR_NO_BUYER_DEL');
1725 FND_MESSAGE.SET_TOKEN('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(p_delivery_id));
1726 fnd_msg_pub.add;
1727
1728 raise FND_API.G_EXC_ERROR;
1729 END IF;
1730
1731
1732 --Get the Carrier,Mode of transport,Service level and load tendered status of a trip.
1733 OPEN validate_trip_csr(l_trip_id);
1734 FETCH validate_trip_csr INTO l_carrier_id,l_mode_of_transport,
1735 l_service_level,l_load_tender_status;
1736 CLOSE validate_trip_csr;
1737 --
1738 IF l_debugOn THEN
1739 wsh_debug_sv.log(l_moduleName,'l_carrier_id',l_carrier_id);
1740 wsh_debug_sv.log(l_moduleName,'l_mode_of_transport',l_mode_of_transport);
1741 wsh_debug_sv.log(l_moduleName,'l_service_level',l_service_level);
1742 wsh_debug_sv.log(l_moduleName,'l_load_tender_status',l_load_tender_status);
1743 END IF;
1744 --
1745
1746 --Error, if any one of carrier,mode of transport or service level is NULL.
1747 IF (l_carrier_id IS NULL OR l_mode_of_transport IS NULL OR l_service_level IS NULL) THEN
1748 --
1749 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_NO_CARRIER_SETUP');
1750 FND_MESSAGE.SET_TOKEN('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(p_delivery_id));
1751 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
1752 RAISE fnd_api.g_exc_error;
1753 --
1754 END IF;
1755 --
1756 IF (l_load_tender_status IS NULL OR l_load_tender_status IN ('OPEN','SHIPPER_CANCELLED') ) THEN
1757 --
1758 --Find out if carrier acecpt auto tender.
1759 OPEN validate_auto_tender_csr(l_carrier_id,l_organization_id);
1760 FETCH validate_auto_tender_csr INTO l_tmp;
1761 --
1762 IF (validate_auto_tender_csr%FOUND) THEN
1763 --
1764 IF l_debugOn THEN
1765 wsh_debug_sv.logmsg(l_moduleName,'validate_auto_tender_csr FOUND');
1766 END IF;
1767 --
1768
1769 --Warning if delivery is not tendered for carrier and accept auto tender.
1770 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1771 --
1772 OPEN get_carrier_name(l_carrier_id);
1773 FETCH get_carrier_name INTO l_carrier_name;
1774 CLOSE get_carrier_name;
1775
1776 --Delivery DEL_NAME is not tendered for carrier CARRIER.
1777 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_NO_TENDER');
1778 FND_MESSAGE.SET_TOKEN('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(p_delivery_id));
1779 FND_MESSAGE.SET_TOKEN('CARRIER', l_carrier_name);
1780 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING, l_moduleName);
1781 END IF;
1782 --
1783 CLOSE validate_auto_tender_csr;
1784 --
1785 ELSIF (l_load_tender_status IN ('ACCEPTED','AUTO_ACCEPTED')) THEN
1786 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1787 ELSE
1788
1789 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1790
1791 --Routing Response for delivery DEL_NAME
1792 --is waiting response from carrier CARRIER.
1793 OPEN get_carrier_name(l_carrier_id);
1794 FETCH get_carrier_name INTO l_carrier_name;
1795 CLOSE get_carrier_name;
1796
1797 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_NO_TENDER_ACCEPT');
1798 FND_MESSAGE.SET_TOKEN('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(p_delivery_id));
1799 FND_MESSAGE.SET_TOKEN('CARRIER', l_carrier_name);
1800 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING, l_moduleName);
1801 END IF;
1802 --
1803 IF l_debugOn THEN
1804 wsh_debug_sv.log(l_moduleName, 'Valid Delivery?', x_return_status);
1805 wsh_debug_sv.pop(l_moduleName);
1806 END IF;
1807 --
1808 EXCEPTION
1809 --
1810 WHEN FND_API.G_EXC_ERROR THEN
1811 --
1812 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1813 --
1814 IF l_debugOn THEN
1815 wsh_debug_sv.logmsg(l_moduleName,'FND_API.G_EXC_ERROR exception has occured.',
1816 wsh_debug_sv.C_EXCEP_LEVEL);
1817 wsh_debug_sv.pop(l_moduleName,'EXCEPTION:FND_API.G_EXC_ERROR');
1818 END IF;
1819 --
1820 WHEN OTHERS THEN
1821 --
1822 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1823 --
1824 wsh_util_core.default_handler('WSH_ROUTING_RESPONSE_PKG.ValidateDelivery');
1825 IF l_debugOn THEN
1826 wsh_debug_sv.logmsg(l_moduleName,'Unexpected error '|| SQLERRM);
1827 wsh_debug_sv.pop(l_moduleName,'EXCEPTION:OTHERS');
1828 END IF;
1829 --
1830 RAISE;
1831 --
1832 END ValidateDelivery;
1833
1834
1835 --
1836 -- Pre-reqs : None
1837 --
1838 -- Parameters
1839 -- p_delivery_id - represents the delivery Id for which PO need to validate.
1840 -- x_return_status - Return status of this API.
1841 --
1842 -- Purpose : This API is used to validate PO status of corresponding input delivery.
1843 -- Returns error if PO is in HOLD or Not Approved.
1844 --
1845 -- Version : 1.0
1846 --
1847 PROCEDURE Validate_PO(p_delivery_id IN NUMBER,
1848 x_return_status OUT NOCOPY VARCHAR2) IS
1849
1850 --Cursor to get PO attributes
1851 CURSOR get_del_detail IS
1852 SELECT source_header_id, source_line_id,
1853 po_shipment_line_id, source_blanket_reference_id
1854 FROM wsh_delivery_details wdd,
1855 wsh_delivery_assignments_v wda
1856 WHERE wda.delivery_id=p_delivery_id
1857 AND wdd.delivery_detail_id = wda.delivery_detail_id
1858 AND nvl(line_direction,'O') not in ('O','IO')
1859 AND source_code='PO';
1860 --
1861 l_return_status varchar2(1);
1862 l_delivery_status varchar2(30);
1863 l_convert_qty number;
1864 l_po_header_id PO_TBL_NUMBER;
1865 l_sourceLineIds PO_TBL_NUMBER;
1866 l_shpLineIds PO_TBL_NUMBER;
1867 l_blkRefIds PO_TBL_NUMBER;
1868 l_dummy_tbl_number po_tbl_number := po_tbl_number();
1869 l_dummy_tbl_varchar30 po_tbl_varchar30 := po_tbl_varchar30();
1870 l_po_status_rec PO_STATUS_REC_TYPE;
1871 --l_po_status_rec STATUS_REC_TYPE;
1872 l_index number;
1873 --
1874 l_debugOn BOOLEAN;
1875 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_PO';
1876 --
1877 BEGIN
1878 --
1879 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
1880 --
1881 IF l_debugOn IS NULL THEN
1882 l_debugOn := wsh_debug_sv.is_debug_enabled;
1883 END IF;
1884 --
1885 IF l_debugOn THEN
1886 wsh_debug_sv.push(l_moduleName);
1887 wsh_debug_sv.log(l_moduleName,'p_delivery_id',p_delivery_id);
1888 wsh_debug_sv.log(l_moduleName,'Current_Release',PO_CODE_RELEASE_GRP.Current_Release);
1889 wsh_debug_sv.log(l_moduleName,'PRC_11i_Family_Pack_J',PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J);
1890 END IF;
1891 --
1892 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1893
1894 --Get the PO attributes of detail lines assign to delivery.
1895 OPEN get_del_detail;
1896 FETCH get_del_detail BULK COLLECT INTO
1897 l_po_header_id, l_sourceLineIds, l_shpLineIds, l_blkRefIds;
1898 --
1899 IF l_debugOn THEN
1900 wsh_debug_sv.log(l_moduleName,'Rec Count',get_del_detail%ROWCOUNT);
1901 END IF;
1902 --
1903
1904 --No detail lines assign to delivery,error out.
1905 IF (l_po_header_id.count < 1) THEN
1906 --
1907 FND_MESSAGE.SET_NAME('WSH', 'WSH_DEL_NO_DETAILS');
1908 FND_MESSAGE.SET_TOKEN('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(p_delivery_id));
1909 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
1910 raise fnd_api.g_exc_error;
1911 --
1912 END IF;
1913 --
1914 CLOSE get_del_detail;
1915 --
1916 l_dummy_tbl_number.extend(l_po_header_id.count);
1917 l_dummy_tbl_varchar30.extend(l_po_header_id.count);
1918 --
1919
1920 --PO release level should be PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J or higher.
1921 IF (PO_CODE_RELEASE_GRP.Current_Release >= PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J) THEN
1922 --{
1923 IF l_debugOn THEN
1924 wsh_debug_sv.logmsg(l_moduleName,'Before Calling PO_FTE_INTEGRATION_GRP.po_status_check');
1925 END IF;
1926 --
1927 PO_FTE_INTEGRATION_GRP.po_status_check (
1928 p_api_version => 1,
1929 p_header_id => l_po_header_id,
1930 p_line_id => l_sourceLineIds,
1931 p_line_location_id => l_shpLineIds,
1932 p_release_id => l_blkRefIds,
1933 p_mode => 'GET_STATUS',
1934 p_document_type => l_dummy_tbl_varchar30,
1935 p_document_subtype => l_dummy_tbl_varchar30,
1936 p_document_num => l_dummy_tbl_varchar30,
1937 p_vendor_order_num => l_dummy_tbl_varchar30,
1938 p_distribution_id => l_dummy_tbl_number,
1939 x_po_status_rec => l_po_status_rec,
1940 x_return_status => l_return_status);
1941 --
1942 IF l_debugOn THEN
1943 wsh_debug_sv.log(l_moduleName,'PO_FTE_INTEGRATION_GRP.po_status_check l_return_status',
1944 l_return_status);
1945 wsh_debug_sv.log(l_moduleName,'l_po_status_rec.approval_flag.count',
1946 l_po_status_rec.approval_flag.count);
1947 END IF;
1948 --
1949 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1950 --
1951 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_PO_INVALID');
1952 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
1953 raise fnd_api.g_exc_error;
1954 --
1955 END IF;
1956 --
1957 l_index:=l_po_status_rec.approval_flag.first;
1958 --
1959 WHILE (l_index IS NOT NULL) LOOP
1960 --
1961 IF l_debugOn THEN
1962 --
1963 wsh_debug_sv.log(l_moduleName,'approval_flag',l_po_status_rec.approval_flag(l_index));
1964 wsh_debug_sv.log(l_moduleName,'hold_flag',l_po_status_rec.hold_flag(l_index));
1965 --
1966 END IF;
1967 --
1968
1969 --Error if PO is in Hold
1970 IF (l_po_status_rec.hold_flag(l_index) = 'Y') THEN
1971 --
1972 FND_MESSAGE.SET_NAME('WSH','WSH_RR_PO_ERROR_HOLD');
1973 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
1974 raise fnd_api.g_exc_error;
1975 --
1976 END IF;
1977 --
1978 --Error if PO is not Approved.
1979 IF (l_po_status_rec.approval_flag(l_index)<> 'Y' ) THEN
1980 --
1981 FND_MESSAGE.SET_NAME('WSH','WSH_RR_PO_ERROR_UNAPPROVED');
1982 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
1983 raise fnd_api.g_exc_error;
1984 --
1985 END IF;
1986 --
1987 l_index:=l_po_status_rec.approval_flag.next(l_index);
1988 --
1989 END LOOP;
1990 --}
1991 ELSE
1992 --
1993 IF l_debugOn THEN
1994 wsh_debug_sv.logmsg(l_moduleName,'Current Release is Not greater than J');
1995 END IF;
1996 --
1997 raise fnd_api.g_exc_error;
1998 --
1999 END IF;
2000 --
2001 IF l_debugOn THEN
2002 wsh_debug_sv.pop(l_moduleName);
2003 END IF;
2004 --
2005 EXCEPTION
2006 --
2007 WHEN FND_API.G_EXC_ERROR THEN
2008 --
2009 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2010 --
2011 IF l_debugOn THEN
2012 wsh_debug_sv.logmsg(l_moduleName,'FND_API.G_EXC_ERROR exception has occured.',
2013 wsh_debug_sv.C_EXCEP_LEVEL);
2014 wsh_debug_sv.pop(l_moduleName,'EXCEPTION:FND_API.G_EXC_ERROR');
2015 END IF;
2016 --
2017 WHEN OTHERS THEN
2018 --
2019 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2020 --
2021 IF l_debugOn THEN
2022 wsh_debug_sv.logmsg(l_moduleName,'Unexpected error has occured. Oracle error message is '||
2023 SQLERRM,wsh_debug_sv.C_UNEXPEC_ERR_LEVEL);
2024 wsh_debug_sv.pop(l_moduleName,'EXCEPTION:OTHERS');
2025 END IF;
2026 --
2027 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
2028 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
2029 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR, l_moduleName);
2030 --
2031 END Validate_PO;
2032
2033
2034 --
2035 -- Pre-reqs : None
2036 --
2037 -- Parameters
2038 -- p_delId - represents the delivery Id for which the routing response
2039 -- is being generated.
2040 --
2041 -- Purpose : This function is used to lock the delivery so that no
2042 -- other operation can be performed on it for the duration
2043 -- of the routing response process.
2044 --
2045 -- Version : 1.0
2046 --
2047 FUNCTION LockDelivery(p_deliveryId IN NUMBER) RETURN BOOLEAN IS
2048 --
2049 CURSOR c_Deliv IS
2050 SELECT *
2051 FROM wsh_new_deliveries
2052 WHERE delivery_id = p_deliveryId
2053 FOR UPDATE NOWAIT;
2054 --
2055 l_debugOn BOOLEAN;
2056 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LockDelivery';
2057 --
2058 BEGIN
2059 --
2060 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
2061 --
2062 IF l_debugOn IS NULL THEN
2063 l_debugOn := wsh_debug_sv.is_debug_enabled;
2064 END IF;
2065 --
2066 IF l_debugOn THEN
2067 wsh_debug_sv.push(l_moduleName);
2068 wsh_debug_sv.log(l_moduleName,'p_deliveryId',p_deliveryId);
2069 END IF;
2070 --
2071 OPEN c_Deliv;
2072 CLOSE c_Deliv;
2073 --
2074 IF l_debugOn THEN
2075 wsh_debug_sv.logmsg(l_moduleName, 'Returning True');
2076 wsh_debug_sv.pop(l_moduleName);
2077 END IF;
2078 --
2079 RETURN(TRUE);
2080 --
2081 EXCEPTION
2082 --
2083 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
2084 --
2085 IF l_debugOn THEN
2086 wsh_debug_sv.logmsg(l_moduleName, 'Could not lock delivery');
2087 wsh_debug_sv.pop(l_moduleName);
2088 END IF;
2089 --
2090 RETURN(FALSE);
2091 --
2092 END LockDelivery;
2093
2094
2095 --
2096 -- Pre-reqs : None
2097 --
2098 -- Parameters
2099 -- itemtype - represents the WF item type. Here it is FTERRESP
2100 -- itemKey - identifies the unique item Key. Here it is the transaction_id
2101 -- from wsh_inbound_txn_history
2102 -- actid - Standard WF attribute to represent the activity ID inside the process
2103 -- funcmode - Standard WF attribute representing the fuction execution mode.
2104 -- resultout - Standard WF attribute that represents the return status of this API
2105 --
2106 -- Purpose : This is a selector/callback function to initialize data for each
2107 -- routing response process instance. In particular, this procedure
2108 -- set the apps context for the WF item type. It can be used in the foll.
2109 -- modes - TEST_CTX, SET_CTX and RUN.
2110 --
2111 -- Version : 1.0
2112 --
2113 PROCEDURE FTERRESP_SELECTOR(itemType IN VARCHAR2,
2114 itemKey IN VARCHAR2,
2115 actid IN NUMBER,
2116 funcmode IN VARCHAR2,
2117 resultout IN OUT NOCOPY VARCHAR2) IS
2118 --
2119 l_userId NUMBER;
2120 l_respId NUMBER;
2121 l_respAppId NUMBER;
2122 l_orgId NUMBER;
2123 l_currentOrgId NUMBER;
2124 l_clientOrgId NUMBER;
2125 v_Fname VARCHAR2(32767);
2126 v_MsgCnt NUMBER;
2127 v_Msg VARCHAR2(32767);
2128 v_Sts VARCHAR2(32767);
2129 --
2130 l_debugOn BOOLEAN;
2131 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FTERRESP_SELECTOR';
2132 --
2133 BEGIN
2134 --
2135 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
2136 --
2137 IF l_debugOn IS NULL THEN
2138 l_debugOn := wsh_debug_sv.is_debug_enabled;
2139 END IF;
2140 --
2141 --wsh_debug_sv.start_debugger(v_Fname, v_Sts, v_MsgCnt, v_Msg);
2142 --
2143 IF l_debugOn THEN
2144 wsh_debug_sv.push(l_moduleName, 'FTERRESP_SELECTOR');
2145 wsh_debug_sv.log(l_moduleName, 'Item Type', itemtype);
2146 wsh_debug_sv.log(l_moduleName, 'ItemKey', itemKey);
2147 wsh_debug_sv.log(l_moduleName, 'Funcmode', funcmode);
2148 END IF;
2149 --
2150 IF(funcmode = 'RUN') THEN
2151 --
2152 resultout := 'COMPLETE';
2153 --
2154 ELSIF(funcmode = 'SET_CTX') THEN
2155 --{
2156 l_userId := wf_engine.GetItemAttrNumber('FTERRESP',itemKey,'USER_ID');
2157 l_respAppId := wf_engine.GetItemAttrNumber('FTERRESP',itemKey, 'APPLICATION_ID');
2158 l_respId := wf_engine.GetItemAttrNumber('FTERRESP',itemKey,'RESPONSIBILITY_ID');
2159 --
2160 IF(l_respAppId IS NULL OR l_respId IS NULL) THEN
2161 RAISE no_data_found;
2162 ELSE
2163 FND_GLOBAL.Apps_Initialize(l_userId, l_respId, l_respAppId);
2164 END IF;
2165 --
2166 resultout := 'COMPLETE';
2167 --}
2168 ELSIF(funcmode = 'TEST_CTX') THEN
2169 --
2170 l_orgId := wf_engine.GetItemAttrNumber( 'FTERRESP', itemKey, 'ORG_ID');
2171 --
2172 IF l_debugOn THEN
2173 wsh_debug_sv.log(l_moduleName, 'l_orgId', l_orgId);
2174 END IF;
2175 --
2176 IF l_orgId IS NULL THEN
2177 resultout := 'TRUE';
2178 ELSE
2179 --
2180 l_currentOrgId := TO_NUMBER(FND_PROFILE.VALUE('ORG_ID'));
2181 --
2182 IF l_debugOn THEN
2183 wsh_debug_sv.log (l_moduleName, 'l_currentOrgId', l_currentOrgId);
2184 END IF;
2185 --
2186 IF l_currentOrgId IS NOT NULL THEN
2187 --{
2188 IF l_orgId <> l_currentOrgId THEN
2189 resultout := 'FALSE';
2190 ELSE
2191 --{
2192 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
2193 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
2194 INTO l_clientOrgId FROM DUAL;
2195 --
2196 IF l_debugOn THEN
2197 wsh_debug_sv.log (l_moduleName, 'l_clientOrgId',l_clientOrgId);
2198 END IF;
2199 --
2200 IF l_clientOrgId = l_orgId THEN
2201 resultout := 'TRUE';
2202 ELSE
2203 --{
2204 IF l_debugOn THEN
2205 wsh_debug_sv.logmsg(l_moduleName, 'SELECTOR: PROFILE ORG = WF ORG');
2206 wsh_debug_sv.logmsg(l_moduleName, 'BUT CLIENT ORG <> WF ORG');
2207 END IF;
2208 --
2209 resultout := 'FALSE';
2210 --}
2211 END IF;
2212 --}
2213 END IF;
2214 --}
2215 ELSE
2216 resultout := 'FALSE';
2217 END IF;
2218 --
2219 END IF;
2220 --
2221 END IF;
2222 --
2223 IF l_debugOn THEN
2224 wsh_debug_sv.log (l_moduleName, 'resultout', resultout);
2225 wsh_debug_sv.pop(l_moduleName);
2226 END IF;
2227 --
2228 wsh_debug_sv.stop_debug;
2229 --
2230 EXCEPTION
2231 --
2232 WHEN OTHERS THEN
2233 --
2234 resultout := 'COMPLETE:FAILURE';
2235 --
2236 IF l_debugOn THEN
2237 wsh_debug_sv.logmsg(l_moduleName,'Unexpected error '|| SQLERRM);
2238 wsh_debug_sv.pop(l_moduleName,'EXCEPTION:OTHERS');
2239 END IF;
2240 --
2241 wsh_debug_sv.stop_debug;
2242 --
2243 RAISE;
2244 --
2245 END FTERRESP_SELECTOR;
2246
2247
2248 --
2249 -- Pre-reqs : None
2250 --
2251 -- Parameters
2252 -- p_routRespNum - Routing response number
2253 -- x_Changed - Indicates whether the delivery attribs have changed
2254 -- since the routing response was generated.
2255 --
2256 -- Purpose : This procedure is called from the iSP routing response details
2257 -- page to determine whether any delivery characteristics have
2258 -- changed since the time the routing response was generated.
2259 -- This API calls the PO integration group API that checks the
2260 -- last_update_date on all the tables related to the routing
2261 -- response and if any of the dates is greater than the
2262 -- last_update_date from WSH_INBOUND_TXN_HISTORY, this API
2263 -- returns Y, else it returns N.
2264 --
2265 -- Version : 1.0
2266 --
2267 PROCEDURE CheckDeliveryInfo(p_routRespNum IN VARCHAR2,
2268 x_changed OUT NOCOPY VARCHAR2) IS
2269 --
2270 l_debugOn BOOLEAN;
2271 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.GenerateRoutingResponse';
2272 --
2273 v_InRec WSH_PO_INTG_TYPES_GRP.delInfo_in_recType;
2274 v_OutRec WSH_PO_INTG_TYPES_GRP.delInfo_out_recType;
2275 v_Status VARCHAR2(1);
2276 v_msgData VARCHAR2(2000);
2277 v_msgCount NUMBER;
2278 l_numErrors NUMBER := 0;
2279 l_numWarnings NUMBER := 0;
2280 --
2281 BEGIN
2282 --
2283 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
2284 --
2285 IF l_debugOn IS NULL THEN
2286 l_debugOn := wsh_debug_sv.is_debug_enabled;
2287 END IF;
2288 --
2289 IF l_debugOn THEN
2290 wsh_debug_sv.push(l_moduleName);
2291 wsh_debug_sv.log(l_moduleName, 'Routing Response Number', p_routRespNum);
2292 END IF;
2293 --
2294 v_InRec.routingRespNum := p_routRespNum;
2295 --
2296 -- Call PO Integration group API
2297 --
2298 WSH_PO_INTEGRATION_GRP.HasDeliveryInfoChanged(
2299 p_api_version_number => 1.0,
2300 p_init_msg_list => FND_API.G_TRUE,
2301 p_commit => FND_API.G_FALSE,
2302 x_return_status => v_Status,
2303 x_msg_Count => v_msgCount,
2304 x_msg_data => v_msgData,
2305 p_in_rec => v_InRec,
2306 x_out_rec => v_OutRec);
2307 --
2308 wsh_util_core.api_post_call(p_return_status => v_Status,
2309 x_num_warnings => l_numWarnings,
2310 x_num_errors => l_numErrors);
2311 --
2312 IF v_OutRec.hasChanged THEN
2313 x_changed := 'Y'; /* has changed */
2314 ELSE
2315 x_changed := 'N'; /* no change */
2316 END IF;
2317 --
2318 IF l_debugOn THEN
2319 wsh_debug_sv.log(l_moduleName, 'Changed', x_changed);
2320 wsh_debug_sv.pop(l_moduleName);
2321 END IF;
2322 --
2323 END CheckDeliveryInfo;
2324
2325
2326 END WSH_ROUTING_RESPONSE_PKG;