1 PACKAGE BODY XDP_CANCEL_ORDER AS
2 /* $Header: XDPPCNLB.pls 115.1 2003/12/15 09:07:45 mboyat noship $ */
3
4 --
5 -- Private API which will check if all FAs are ready for cancel
6 -- for the given order
7 --
8 FUNCTION IS_PROCESSING_STARTED
9 ( p_order_id IN NUMBER
10 ) RETURN BOOLEAN ;
11
12 PROCEDURE CANCEL_SFM_ORDER
13 (
14 p_application_id in NUMBER,
15 p_entity_short_name in VARCHAR2,
16 p_validation_entity_short_name in VARCHAR2,
17 p_validation_tmplt_short_name in VARCHAR2,
18 p_record_set_short_name in VARCHAR2,
19 p_scope in VARCHAR2,
20 x_result out NOCOPY NUMBER
21 )
22 IS
23 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_SFM_ORDER';
24 l_api_version CONSTANT NUMBER := 11.5;
25 l_order_number NUMBER;
26 l_order_version NUMBER;
27 lv_sfm_order_id NUMBER;
28 lv_state VARCHAR2(40);
29 lv_order_type VARCHAR2(40);
30 lv_msg_id RAW(16);
31 l_return_status VARCHAR2(1);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(800);
34 l_item_key VARCHAR2(40);
35 l_user_key VARCHAR2(40);
36 l_err_name VARCHAR2(40);
37 l_err_message VARCHAR2(100);
38 l_err_stack VARCHAR2(1000);
39 error_message VARCHAR2(1000);
40
41 Begin
42
43 ----------------------------------------------------------------------
44 -- Get the value of the Order Id and Order Version from the global
45 -- record variable in the entity's constraint API package.
46 ----------------------------------------------------------------------
47
48
49 -- Set the x_result to 0 i.e. as success and handle it later
50 x_result := 0;
51
52 IF p_validation_entity_short_name = 'HEADER' THEN
53 l_order_number := oe_header_security.g_record.order_number;
54 l_order_version := oe_header_security.g_record.version_number;
55 END IF;
56
57 ---------------------------------------------------------------------
58 -- Check whether the Order Id is null.
59 -- If the Order Id is null then return 1
60 ----------------------------------------------------------------------
61 IF l_order_number IS NULL OR
62 l_order_number = FND_API.G_MISS_NUM
63 THEN
64 x_result := 1;
65 return;
66 END IF;
67
68 ----------------------------------------------------------------------
69 -- Get the SFM Order Id from the XDP_ORDER_HEADERS table
70 ----------------------------------------------------------------------
71 BEGIN
72 IF l_order_version IS NOT NULL THEN
73 SELECT order_id
74 INTO lv_sfm_order_id
75 FROM xdp_order_headers
76 WHERE external_order_number = to_char(l_order_number) and
77 external_order_version = to_char(l_order_version);
78 ELSE
79 SELECT order_id
80 INTO lv_sfm_order_id
81 FROM xdp_order_headers
82 WHERE external_order_number = (l_order_number) and
83 external_order_version IS NULL;
84 END IF;
85 EXCEPTION
86 ---------------------------------------------------------------------
87 -- If no data found then return 0 as there is no SFM order that needs
88 -- to be cancelled for this OM Ordre Id => Success
89 ----------------------------------------------------------------------
90 WHEN no_data_found THEN
91 x_result := 0;
92 return;
93 END;
94
95 ----------------------------------------------------------------------
96 -- Get the status code for the order for furthur processing
97 ----------------------------------------------------------------------
98 SELECT status_code,
99 msgid,
100 order_type
101 INTO lv_state,
102 lv_msg_id,
103 lv_order_type
104 FROM xdp_order_headers
105 WHERE order_id = lv_sfm_order_id;
106
107 ----------------------------------------------------------------------
108 -- If the state is as listed below then return a success
109 -- 1. Cancelled
110 -- 2. Aborted
111 ----------------------------------------------------------------------
112 IF lv_state IN ('CANCELED','ABORTED') THEN
113 x_result := 0;
114 return;
115
116 ----------------------------------------------------------------------
117 -- If the state is as listed below then call the SFM cancel API
118 -- 1. Ready
119 -- 2. Standby
120 ----------------------------------------------------------------------
121
122 ELSIF lv_state IN ('READY','STANDBY') THEN
123 XDP_INTERFACES_PUB.Cancel_Order(
124 p_api_version => l_api_version,
125 p_init_msg_list => FND_API.G_FALSE,
126 p_commit => FND_API.G_FALSE,
127 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
128 x_return_status => l_return_status,
129 x_msg_count => l_msg_count,
130 x_msg_data => l_msg_data,
131 P_SDP_ORDER_ID => lv_sfm_order_id,
132 p_caller_name => FND_GLOBAL.user_name);
133
134 if l_return_status = FND_API.G_RET_STS_SUCCESS THEN
135
136 ---------------------------------------------------------------
137 -- spawn the SFM_Rollback Workflow before returning a
138 -- status of 1
139 ---------------------------------------------------------------
140
141 BEGIN
142 -- Generate an Item Key with the help of OM Order Numebr
143 l_item_key := 'XDPCNCL-' || 'SFM_ROLLBACK-' || l_order_number;
144
145 -- Call The Create Process
146 wf_engine.CreateProcess( itemtype => 'XDPCNCL',
147 itemkey => l_item_key,
148 process => 'SFM_ROLLBACK');
149
150 -- Generate a User Key with the help of OM Order Numebr
151 l_user_key := 'USER_KEY-' || 'XDPCNCL-' || 'SFM_ROLLBACK-' || l_order_number;
152
153 -- Set the User Key for the process which uniquely identifies it
154 wf_engine.setItemUserKey(itemtype => 'XDPCNCL',
155 itemkey => l_item_key,
156 userkey => l_user_key);
157
158 -- Set the Value of OM Order Id for the process
159 wf_engine.SetItemAttrNumber(itemtype => 'XDPCNCL',
160 itemkey => l_item_key,
161 aname => 'ORDER_ID',
162 avalue => l_order_number);
163
164 -- Set the process owner user name
165 wf_engine.SetItemOwner(itemtype => 'XDPCNCL',
166 itemkey => l_item_key,
167 owner => FND_GLOBAL.user_name);
168
169 -- Start the process
170 wf_engine.StartProcess(itemtype => 'XDPCNCL',
171 itemkey => l_item_key);
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 -- Get the error and push it to the OE_MSG_PUB
176 wf_core.GET_ERROR(err_name => l_err_name,
177 err_message => l_err_message,
178 err_stack => l_err_stack,
179 maxErrStackLength => 900);
180
181 FND_MESSAGE.SET_NAME('XDP', 'XDP_INTFACE_PROCESS_FAIL');
182 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_err_name || ' : ' || l_err_message);
183 OE_MSG_PUB.Add;
184
185 -- Return with Failure
186 x_result := 1;
187 return;
188 END;
189
190 -- Return the Success
191 x_result := 0;
192 return;
193 ELSE
194 ---------------------------------------------------------------
195 -- Push a message in the OE_MSG_PUB Stack
196 ---------------------------------------------------------------
197 error_message := FND_MSG_PUB.get(p_encoded => FND_API.g_false);
198 while (error_message is not null) loop
199 FND_MESSAGE.SET_NAME('XDP', 'XDP_INTFACE_PROCESS_FAIL');
200 FND_MESSAGE.SET_TOKEN('ERROR_MSG', error_message);
201 OE_MSG_PUB.Add;
202 error_message := FND_MSG_PUB.get(p_encoded => FND_API.g_false);
203 end loop;
204
205 ---------------------------------------------------------------
206 -- Return a value of one for a failure
207 ---------------------------------------------------------------
208 x_result := 1;
209 return;
210 END IF;
211
212 ----------------------------------------------------------------------
213 -- If the state is as listed below then check if any FA associated with
214 -- it has started. If Not then cancel the order
215 -- 1. Error
216 -- 2. Progress
217 ----------------------------------------------------------------------
218 ELSIF lv_state IN ('ERROR','IN PROGRESS') THEN
219 BEGIN
220 IF IS_PROCESSING_STARTED(lv_sfm_order_id) THEN
221 XDP_INTERFACES_PUB.Cancel_Order(
222 p_api_version => l_api_version,
223 p_init_msg_list => FND_API.G_FALSE,
224 p_commit => FND_API.G_FALSE,
225 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
226 x_return_status => l_return_status,
227 x_msg_count => l_msg_count,
228 x_msg_data => l_msg_data,
229 P_SDP_ORDER_ID => lv_sfm_order_id,
230 p_caller_name => FND_GLOBAL.user_name);
231
232 if l_return_status = FND_API.G_RET_STS_SUCCESS THEN
233
234 ---------------------------------------------------------------
235 -- spawn the SFM_Rollback Workflow before returning a
236 -- status of 1
237 ---------------------------------------------------------------
238
239 BEGIN
240 -- Generate an Item Key with the help of OM Order Numebr
241 l_item_key := 'XDPCNCL' || 'SFM_ROLLBACK' || l_order_number;
242
243 -- Call The Create Process
244 wf_engine.CreateProcess( itemtype => 'XDPCNCL',
245 itemkey => l_item_key,
246 process => 'SFM_ROLLBACK');
247
248 -- Generate a User Key with the help of OM Order Numebr
249 l_user_key := 'USER_KEY' || 'XDPCNCL' || 'SFM_ROLLBACK' || l_order_number;
250
251 -- Set the User Key for the process which uniquely identifies it
252 wf_engine.setItemUserKey(itemtype => 'XDPCNCL',
253 itemkey => l_item_key,
254 userkey => l_user_key);
255
256 -- Set the Value of OM Order Id for the process
257 wf_engine.SetItemAttrNumber(itemtype => 'XDPCNCL',
258 itemkey => l_item_key,
259 aname => 'ORDER_ID',
260 avalue => l_order_number);
261
262 -- Set the process owner user name
263 wf_engine.SetItemOwner(itemtype => 'XDPCNCL',
264 itemkey => l_item_key,
265 owner => FND_GLOBAL.user_name);
266
267 -- Start the process
268 wf_engine.StartProcess(itemtype => 'XDPCNCL',
269 itemkey => l_item_key);
270
271 EXCEPTION
272 WHEN no_data_found THEN
273 -- Get the error and push it to the OE_MSG_PUB
274 wf_core.GET_ERROR(err_name => l_err_name,
275 err_message => l_err_message,
276 err_stack => l_err_stack,
277 maxErrStackLength => 900);
278
279 FND_MESSAGE.SET_NAME('XDP', 'XDP_INTFACE_PROCESS_FAIL');
280 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_err_name || ' : ' || l_err_message);
281 OE_MSG_PUB.Add;
282 -- Return with Failure
283 x_result := 1;
284 return;
285 END;
286
287 -- Return the Success
288 x_result := 0;
289 return;
290 ELSE
291 ---------------------------------------------------------------
292 -- Push a message in the OE_MSG_PUB Stack
293 ---------------------------------------------------------------
294 error_message := FND_MSG_PUB.get(p_encoded => FND_API.g_false);
295 while (error_message is not null) loop
299 error_message := FND_MSG_PUB.get(p_encoded => FND_API.g_false);
296 FND_MESSAGE.SET_NAME('XDP', 'XDP_INTFACE_PROCESS_FAIL');
297 FND_MESSAGE.SET_TOKEN('ERROR_MSG', error_message);
298 OE_MSG_PUB.Add;
300 end loop;
301
302 ---------------------------------------------------------------
303 -- Return a value of one for a failure
304 ---------------------------------------------------------------
305 x_result := 1;
306 return;
307 END IF;
308 ELSE
309 -- Return the failure as there are FA's running/success/error
310 x_result := 1;
311 return;
312 END IF;
313 EXCEPTION
314 when OTHERS THEN
315 -- Return with Failure
316 x_result := 1;
317 return;
318
319 END;
320
321 ----------------------------------------------------------------------
322 -- Or else if the state is as listed below then return a failure
323 -- 1. Unknown
324 -- 2. Success
325 -- 3. Success With Override
326 ----------------------------------------------------------------------
327 ELSE
328 -- Return with Failure
329 x_result := 1;
330 return;
331
332 END IF;
333 ----------------------------------------------------------------------
334 ---- We always need to return a failure with '1' as the return_status
335 ----------------------------------------------------------------------
336 EXCEPTION
337 WHEN FND_API.G_EXC_ERROR THEN
338 x_result := 1;
339
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 x_result := 1 ;
342
343 WHEN OTHERS THEN
344 x_result := 1;
345
346 --End of the Procedure
347 END CANCEL_SFM_ORDER;
348
349 -------------------------------------------------------------------------
350 -- Private API check if all FAs are ready for cancel for the given order
351 -------------------------------------------------------------------------
352
353 FUNCTION IS_PROCESSING_STARTED( p_order_id IN NUMBER)
354 RETURN BOOLEAN IS
355 l_error_progress_state BOOLEAN := TRUE ;
356 CURSOR CheckIfCancellable IS
357 SELECT 'Y'
358 FROM XDP_FULFILL_WORKLIST FulfillWorklist,
359 XDP_FA_RUNTIME_LIST FARuntimeList
360 WHERE FulfillWorklist.order_id = p_order_id
361 AND FulfillWorklist.workitem_instance_id = FARuntimeList.workitem_instance_id
362 AND FARuntimeList.status_code IN ('IN PROGRESS', 'ERROR', 'SUCCESS',
363 'SUCCESS_WITH_OVERRIDE', 'CANCELED', 'ABORTED');
364
365 BEGIN
366 FOR CheckIfCancellableRecord IN CheckIfCancellable
367 LOOP
368 l_error_progress_state := FALSE ;
369 END LOOP ;
370
371 RETURN l_error_progress_state ;
372
373 -- End of the function
374 END IS_PROCESSING_STARTED;
375
376
377
378 --End of the package
379 END XDP_CANCEL_ORDER;