1 Package body ota_wf as
2 /* $Header: ottomiwf.pkb 120.2.12020000.5 2012/11/20 09:21:02 jaysridh ship $ */
3 g_package varchar2(33) := ' ota_wf.'; -- Global package name
4
5 -- ----------------------------------------------------------------------------
6 -- |---------------------------------< CANCEL_ORDER >-------------------------|
7 -- ----------------------------------------------------------------------------
8 -- {Start Of Comments}
9 --
10 -- Description:
11 -- This procedure will be a concurrent process which run in the background.
12 --
13 -- This procedure will only be used for OTA and OM integration. Basically this
14 -- procedure will select all delegate booking data that has daemon_flag='Y' and
15 -- Daemon_type is not nul. If the enrollment got canceled and there is a
16 -- waitlisted student then the automatic waitlist processing will be called.
17 --
18 -- Pre Conditions:
19 -- None.
20 --
21 -- IN
22 -- itemtype - type of the current item
23 -- itemkey - key of the current item
24 -- actid - process activity instance id
25 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
26 -- OUT
27 -- result
28 -- - COMPLETE[:<result>]
29 -- activity has completed with the indicated result
30 -- - WAITING
31 -- activity is waiting for additional transitions
32 -- - DEFERED
33 -- execution should be defered to background
34 -- - NOTIFIED[:<notification_id>:<assigned_user>]
35 -- activity has notified an external entity that this
36 -- step must be performed. A call to wf_engine.CompleteActivty
37 -- will signal when this step is complete. Optional
38 -- return of notification ID and assigned user.
39 -- - ERROR[:<error_code>]
40 -- function encountered an err--
41 --
42 -- Post Success:
43 -- Processing continues.
44 --
45 --
46 -- Post Failure:
47 -- None.
48 --
49 -- Access Status:
50 -- Public.
51 --
52 -- {End Of Comments}
53 ----------------------------------------------------------------------------
54
55 PROCEDURE CANCEL_ORDER (
56 itemtype IN VARCHAR2
57 ,itemkey IN VARCHAR2
58 ,actid IN NUMBER
59 ,funcmode IN VARCHAR2
60 ,resultout OUT NOCOPY VARCHAR2
61
62 )
63 IS
64 l_proc varchar2(72) := g_package||'cancel_order';
65
66 l_Line_id oe_order_lines.Line_Id%type;
67 l_header_id oe_order_lines.header_id%type;
68 l_control_rec OE_GLOBALS.Control_Rec_Type:=
69 OE_GLOBALS.G_MISS_CONTROL_REC;
70
71 --Declare all local variable.
72 l_api_version_number CONSTANT NUMBER := 1.0;
73 l_return_values varchar2(50);
74 l_return_status VARCHAR2(1) ;
75 l_msg_count NUMBER;
76 l_msg_data VARCHAR2(2000);
77 l_header_rec OE_Order_PUB.Header_Rec_Type;
78 x_header_rec OE_Order_PUB.Header_Rec_Type; --added for bug 6347596
79 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
80 l_header_adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
81 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
82 l_header_price_att_tbl OE_Order_PUB.header_Price_Att_Tbl_Type;
83 l_header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
84 l_header_adj_assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
85 l_header_scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
86 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
87 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
88 x_line_tbl OE_Order_PUB.Line_Tbl_Type; --added for bug 6347596
89 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
90 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
91 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
92 l_line_price_att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
93 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
94 l_line_adj_assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
95 l_line_scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
96 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
97 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
98 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
99 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type ;
100
101 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
102 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
103 OE_Order_PUB.G_MISS_REQUEST_TBL;
104
105 l_old_header_rec OE_Order_PUB.Header_Rec_Type ;
106 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type ;
107 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type ;
108 l_old_Header_Adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type ;
109 l_old_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
110 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
111 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
112 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type ;
113 l_old_Header_Scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type ;
114 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type ;
115 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type ;
116 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type ;
117 l_old_Line_Adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type ;
118 l_old_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
119 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
120 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
121 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type ;
122 l_old_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type ;
123 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type ;
124 l_old_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type ;
125
126 l_message_data varchar2(2000);
127
128 l_org_id oe_order_lines.org_id%type;
129 BEGIN
130
131 hr_utility.set_location('Entering:'||l_proc, 5);
132
133 IF (funcmode = 'RUN') THEN
134
135 l_header_id := WF_ENGINE.getitemattrnumber(
136 itemtype => itemtype,
137 itemkey => itemkey,
138 aname => 'HEADER_ID');
139 l_org_id := WF_ENGINE.getitemattrnumber(
140 itemtype => itemtype,
141 itemkey => itemkey,
142 aname => 'ORG_ID');
143 MO_GLOBAL.SET_POLICY_CONTEXT ('S', l_org_id); -- For MOAC support
144 --fnd_client_info.set_org_context(context => to_char(l_org_id)); -- No needed
145
146 l_header_rec.header_id := l_header_id;
147 l_line_rec. operation := 'UPDATE';
148 l_line_rec.ordered_quantity := 0;
149 l_line_rec.line_id := to_number(itemkey);
150 l_line_tbl(1) := l_line_rec;
151
152 OE_Order_GRP.Process_Order
153 ( p_api_version_number => 1.0
154 , p_init_msg_list => FND_API.G_FALSE
155 , p_return_values => l_return_values
156 , p_commit => FND_API.G_FALSE
157 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
158 , p_control_rec => l_control_rec
159 , p_api_service_level => OE_GLOBALS.G_ALL_SERVICE
160 , x_return_status => l_return_status
161 , x_msg_count => l_msg_count
162 , x_msg_data => l_msg_data
163 , p_header_rec => l_header_rec
164 , p_header_val_rec => l_header_val_rec
165 , p_Header_Adj_tbl => l_header_adj_tbl
166 , p_Header_Adj_val_tbl => l_header_adj_val_tbl
167 , p_Header_price_Att_tbl => l_header_price_att_tbl
168 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
169 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
170 , p_Header_Scredit_tbl => l_header_scredit_tbl
171 , p_Header_Scredit_val_tbl => l_header_scredit_val_tbl
172 , p_line_tbl => l_line_tbl
173 , p_line_val_tbl => l_line_val_tbl
174 , p_Line_Adj_tbl => l_line_adj_tbl
175 , p_Line_Adj_val_tbl => l_line_adj_val_tbl
176 , p_Line_price_Att_tbl => l_line_price_att_tbl
177 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
178 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
179 , p_Line_Scredit_tbl => l_line_scredit_tbl
180 , p_Line_Scredit_val_tbl => l_line_scredit_val_tbl
181 , p_Lot_Serial_tbl => l_lot_serial_tbl
182 , p_Lot_Serial_val_tbl => l_lot_serial_val_tbl
183 , p_Action_Request_tbl => l_request_tbl
184 , x_header_rec => x_header_rec --modified for bug 6347596
185 , x_header_val_rec => l_header_val_rec
186 , x_Header_Adj_tbl => l_header_adj_tbl
187 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
188 , x_Header_price_Att_tbl => l_header_price_att_tbl
189 , x_Header_Adj_Att_tbl => l_header_adj_att_tbl
190 , x_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
191 , x_Header_Scredit_tbl => l_header_scredit_tbl
192 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
193 , x_line_tbl => x_line_tbl --modified for bug 6347596
194 , x_line_val_tbl => l_line_val_tbl
195 , x_Line_Adj_tbl => l_line_adj_tbl
196 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
197 , x_Line_price_Att_tbl => l_line_price_att_tbl
198 , x_Line_Adj_Att_tbl => l_line_adj_att_tbl
199 , x_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
200 , x_Line_Scredit_tbl => l_line_scredit_tbl
201 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
202 , x_Lot_Serial_tbl => l_lot_serial_tbl
203 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
204 , x_action_request_tbl => l_action_request_tbl
205 );
206
207 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
208 ota_om_upd_api.retrieve_oe_messages(l_message_data);
209 RAISE FND_API.G_EXC_ERROR;
210
211 END IF;
212 resultout := 'COMPLETE';
213 return;
214
215 END IF;
216 -- CANCEL mode - activity 'compensation'
217 --
218 -- This is an event point is called with the effect of the activity must
219 -- be undone, for example when a process is reset to an earlier point
220 -- due to a loop back.
221 --
222 IF (funcmode = 'CANCEL') THEN
223
224 -- your cancel code goes here
225 null;
226
227 -- no result needed
228 resultout := 'COMPLETE';
229 return;
230 END IF;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 -- The line below records this function call in the error system
235 -- in the case of an exception.
236 wf_core.context('OTA_WF', 'cancel_order',
237 itemtype, itemkey, to_char(actid), funcmode);
238 RAISE;
239
240 hr_utility.set_location(' Leaving:'||l_proc, 10);
241
242 END;
243
244 -- ----------------------------------------------------------------------------
245 -- |---------------------------------< CREATE_RMA >----------------------------|
246 -- ----------------------------------------------------------------------------
247 -- {Start Of Comments}
248 --
249 -- Description:
250 -- This procedure will be a concurrent process which run in the background.
251 --
252 -- This procedure will only be used for OTA and OM integration. Basically this
253 -- procedure will select all delegate booking data that has daemon_flag='Y' and
254 -- Daemon_type is not nul. If the enrollment got canceled and there is a
255 -- waitlisted student then the automatic waitlist processing will be called.
256 --
257 -- Pre Conditions:
258 -- None.
259 --
260 -- IN
261 -- itemtype - type of the current item
262 -- itemkey - key of the current item
263 -- actid - process activity instance id
264 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
265 -- OUT
266 -- result
267 -- - COMPLETE[:<result>]
268 -- activity has completed with the indicated result
269 -- - WAITING
270 -- activity is waiting for additional transitions
271 -- - DEFERED
272 -- execution should be defered to background
273 -- - NOTIFIED[:<notification_id>:<assigned_user>]
274 -- activity has notified an external entity that this
275 -- step must be performed. A call to wf_engine.CompleteActivty
276 -- will signal when this step is complete. Optional
277 -- return of notification ID and assigned user.
278 -- - ERROR[:<error_code>]
279 -- function encountered an err--
280 --
281 -- Post Success:
282 -- Processing continues.
283 --
284 --
285 -- Post Failure:
286 -- None.
287 --
288 -- Access Status:
289 -- Public.
290 --
291 -- {End Of Comments}
292 ----------------------------------------------------------------------------
293
294 PROCEDURE CREATE_RMA (
295 itemtype IN VARCHAR2
296 ,itemkey IN VARCHAR2
297 ,actid IN NUMBER
298 ,funcmode IN VARCHAR2
299 ,resultout OUT NOCOPY VARCHAR2
300
301 )IS
302 l_proc varchar2(72) := g_package||'create_rma';
303
304 l_Line_id oe_order_lines.Line_Id%type;
305 l_header_id oe_order_lines.header_id%type;
306 l_control_rec OE_GLOBALS.Control_Rec_Type ;
307
308 --Declare all local variable.
309 l_api_version_number CONSTANT NUMBER := 1.0;
310 l_return_values varchar2(50);
311 l_return_status VARCHAR2(1) ;
312 l_msg_count NUMBER;
313 l_msg_data VARCHAR2(2000);
314 l_header_rec OE_Order_PUB.Header_Rec_Type;
315 x_header_rec OE_Order_PUB.Header_Rec_Type; --added for bug 6347596
316 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
317 l_header_adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
318 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
319 l_header_price_att_tbl OE_Order_PUB.header_Price_Att_Tbl_Type;
320 l_header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
321 l_header_adj_assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
322 l_header_scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
323 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
324 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
325 x_line_tbl OE_Order_PUB.Line_Tbl_Type; --added for bug 6347596
326 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
327 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
328 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
329 l_line_price_att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
330 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
331 l_line_adj_assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
332 l_line_scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
333 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
334 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
335 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
336 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type ;
337
338 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
339 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
340 OE_Order_PUB.G_MISS_REQUEST_TBL;
341
342 l_old_header_rec OE_Order_PUB.Header_Rec_Type ;
343 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type ;
344 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type ;
345 l_old_Header_Adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type ;
346 l_old_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
347 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
348 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
349 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type ;
350 l_old_Header_Scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type ;
351 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type ;
352 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type ;
353 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type ;
354 l_old_Line_Adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type ;
355 l_old_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
356 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
357 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
358 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type ;
359 l_old_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type ;
360 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type ;
361 l_old_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type ;
362
363 l_message_data varchar2(2000);
364 l_org_id oe_order_lines.org_id%type;
365
366
367 BEGIN
368 hr_utility.set_location('Entering:'||l_proc, 5);
369
370 IF (funcmode = 'RUN') THEN
371
372 l_header_id := WF_ENGINE.getitemattrnumber(
373 itemtype => itemtype,
374 itemkey => itemkey,
375 aname => 'HEADER_ID');
376
377
378 l_org_id := WF_ENGINE.getitemattrnumber(
379 itemtype => itemtype,
380 itemkey => itemkey,
381 aname => 'ORG_ID');
382
383 MO_GLOBAL.SET_POLICY_CONTEXT ('S', l_org_id); -- For MOAC support
384
385 l_header_rec.header_id := l_header_id;
386 l_line_rec. operation := 'INSERT';
387 l_line_rec.reference_Line_id := to_number(itemkey);
388 l_line_rec.reference_header_id := l_header_id;
389 l_line_rec.line_category_code := 'RETURN';
390 l_line_rec.ordered_quantity := -1;
391 l_line_tbl(1) := l_line_rec;
392
393 --fnd_client_info.set_org_context(context => to_char(l_org_id));
394
395 OE_Order_GRP.Process_Order
396 ( p_api_version_number => l_api_version_number
397 , p_init_msg_list => FND_API.G_FALSE
398 , p_return_values => l_return_values
399 , p_commit => FND_API.G_FALSE
400 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
401 , p_control_rec => l_control_rec
402 , p_api_service_level => OE_GLOBALS.G_ALL_SERVICE
403 , x_return_status => l_return_status
404 , x_msg_count => l_msg_count
405 , x_msg_data => l_msg_data
406 , p_header_rec => l_header_rec
407 , p_old_header_rec => l_old_header_rec
408 , p_header_val_rec => l_header_val_rec
409 , p_old_header_val_rec => l_old_header_val_rec
410 , p_Header_Adj_tbl => l_header_adj_tbl
411 , p_old_Header_Adj_tbl => l_old_Header_Adj_tbl
412 , p_Header_Adj_val_tbl => l_header_adj_val_tbl
413 , p_old_Header_Adj_val_tbl => l_old_Header_Adj_val_tbl
414 , p_Header_price_Att_tbl => l_header_price_att_tbl
415 , p_old_Header_Price_Att_tbl => l_old_Header_Price_Att_tbl
416 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
417 , p_old_Header_Adj_Att_tbl => l_old_Header_Adj_Att_tbl
418 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
419 , p_old_Header_Adj_Assoc_tbl => l_old_Header_Adj_Assoc_tbl
420 , p_Header_Scredit_tbl => l_header_scredit_tbl
421 , p_old_Header_Scredit_tbl => l_old_Header_Scredit_tbl
422 , p_Header_Scredit_val_tbl => l_header_scredit_val_tbl
423 , p_old_Header_Scredit_val_tbl => l_old_Header_Scredit_val_tbl
424 , p_line_tbl => l_line_tbl
425 , p_old_line_tbl => l_old_line_tbl
426 , p_line_val_tbl => l_line_val_tbl
427 , p_old_line_val_tbl => l_old_line_val_tbl
428 , p_Line_Adj_tbl => l_line_adj_tbl
429 , p_old_Line_Adj_tbl => l_old_Line_Adj_tbl
430 , p_Line_Adj_val_tbl => l_line_adj_val_tbl
431 , p_old_Line_Adj_val_tbl => l_old_Line_Adj_val_tbl
432 , p_Line_price_Att_tbl => l_line_price_att_tbl
433 , p_old_Line_Price_Att_tbl => l_old_Line_Price_Att_tbl
434 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
435 , p_old_Line_Adj_Att_tbl => l_old_Line_Adj_Att_tbl
436 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
437 , p_old_Line_Adj_Assoc_tbl => l_old_Line_Adj_Assoc_tbl
438 , p_Line_Scredit_tbl => l_line_scredit_tbl
439 , p_old_Line_Scredit_tbl => l_old_Line_Scredit_tbl
440 , p_Line_Scredit_val_tbl => l_line_scredit_val_tbl
441 , p_old_Line_Scredit_val_tbl => l_old_Line_Scredit_val_tbl
442 , p_Lot_Serial_tbl => l_lot_serial_tbl
443 , p_old_Lot_Serial_tbl => l_old_Lot_Serial_tbl
444 , p_Lot_Serial_val_tbl => l_lot_serial_val_tbl
445 , p_old_Lot_Serial_val_tbl => l_old_Lot_Serial_val_tbl
446 , p_Action_Request_tbl => l_request_tbl
447 , x_header_rec => x_header_rec --modified for bug 6347596
448 , x_header_val_rec => l_header_val_rec
449 , x_Header_Adj_tbl => l_header_adj_tbl
450 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
451 , x_Header_price_Att_tbl => l_header_price_att_tbl
452 , x_Header_Adj_Att_tbl => l_header_adj_att_tbl
453 , x_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
454 , x_Header_Scredit_tbl => l_header_scredit_tbl
455 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
456 , x_line_tbl => x_line_tbl --modified for bug 6347596
457 , x_line_val_tbl => l_line_val_tbl
458 , x_Line_Adj_tbl => l_line_adj_tbl
459 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
460 , x_Line_price_Att_tbl => l_line_price_att_tbl
461 , x_Line_Adj_Att_tbl => l_line_adj_att_tbl
462 , x_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
463 , x_Line_Scredit_tbl => l_line_scredit_tbl
464 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
465 , x_Lot_Serial_tbl => l_lot_serial_tbl
466 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
467 , x_action_request_tbl => l_action_request_tbl
468 );
469
470 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
471 ota_om_upd_api.retrieve_oe_messages(l_message_data);
472 RAISE FND_API.G_EXC_ERROR;
473
474 END IF;
475 resultout := 'COMPLETE';
476
477 return;
478
479 END IF;
480 -- CANCEL mode - activity 'compensation'
481 --
482 -- This is an event point is called with the effect of the activity must
483 -- be undone, for example when a process is reset to an earlier point
484 -- due to a loop back.
485 --
486 IF (funcmode = 'CANCEL') THEN
487
488 -- your cancel code goes here
489 null;
490
491 -- no result needed
492 resultout := 'COMPLETE';
493 return;
494 END IF;
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 -- The line below records this function call in the error system
499 -- in the case of an exception.
500 wf_core.context('OTA_WF', 'create_rma',
501 itemtype, itemkey, to_char(actid), funcmode);
502 RAISE;
503
504 hr_utility.set_location(' Leaving:'||l_proc, 10);
505
506 END;
507
508
509 -- ----------------------------------------------------------------------------
510 -- |--------------------------< UPDATE_FULFILL_DATE >-------------------------|
511 -- ----------------------------------------------------------------------------
512 -- {Start Of Comments}
513 --
514 -- Description:
515 -- This procedure will be a concurrent process which run in the background.
516 --
517 -- This procedure will only be used for OTA and OM integration. Basically this
518 -- procedure will select all delegate booking data that has daemon_flag='Y' and
519 -- Daemon_type is not nul. If the enrollment got canceled and there is a
520 -- waitlisted student then the automatic waitlist processing will be called.
521 --
522 -- Pre Conditions:
523 -- None.
524 --
525 -- IN
526 -- itemtype - type of the current item
527 -- itemkey - key of the current item
528 -- actid - process activity instance id
529 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
530 -- OUT
531 -- result
532 -- - COMPLETE[:<result>]
533 -- activity has completed with the indicated result
534 -- - WAITING
535 -- activity is waiting for additional transitions
536 -- - DEFERED
537 -- execution should be defered to background
538 -- - NOTIFIED[:<notification_id>:<assigned_user>]
539 -- activity has notified an external entity that this
540 -- step must be performed. A call to wf_engine.CompleteActivty
541 -- will signal when this step is complete. Optional
542 -- return of notification ID and assigned user.
543 -- - ERROR[:<error_code>]
544 -- function encountered an err--
545 -- Post Success:
546 -- Processing continues.
547 --
548 --
549 -- Post Failure:
550 -- None.
551 --
552 -- Access Status:
553 -- Public.
554 --
555 -- {End Of Comments}
556 ----------------------------------------------------------------------------
557 PROCEDURE UPDATE_FULFILL_DATE (
558 itemtype IN VARCHAR2
559 ,itemkey IN VARCHAR2
560 ,actid IN NUMBER
561 ,funcmode IN VARCHAR2
562 ,resultout OUT NOCOPY VARCHAR2
563
564 ) Is
565 l_proc varchar2(72) := g_package||'update_fulfill_date';
566 l_header_id oe_order_lines.header_id%type;
567
568 l_Line_id OE_ORDER_LINES.line_id%type := to_number(itemkey) ;
569 l_control_rec OE_GLOBALS.Control_Rec_Type:=
570 OE_GLOBALS.G_MISS_CONTROL_REC;
571
572
573 l_api_version_number CONSTANT NUMBER := 1.0;
574 l_return_values varchar2(50);
575 l_return_status VARCHAR2(1) ;
576 l_msg_count NUMBER;
577 l_msg_data VARCHAR2(2000);
578 l_header_rec OE_Order_PUB.Header_Rec_Type;
579 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
580 l_header_adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
581 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
582 l_header_price_att_tbl OE_Order_PUB.header_Price_Att_Tbl_Type;
583 l_header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
584 l_header_adj_assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
585 l_header_scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
586 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
587 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
588 x_line_tbl OE_Order_PUB.Line_Tbl_Type; --added for bug 6347596
589 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
590 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
591 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
592 l_line_price_att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
593 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
594 l_line_adj_assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
595 l_line_scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
596 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
597 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
598 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
599 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type ;
600
601 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
602 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
603 OE_Order_PUB.G_MISS_REQUEST_TBL;
604
605 l_old_header_rec OE_Order_PUB.Header_Rec_Type ;
606 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type ;
607 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type ;
608 l_old_Header_Adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type ;
609 l_old_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
610 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
611 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
612 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type ;
613 l_old_Header_Scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type ;
614 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type ;
615 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type ;
616 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type ;
617 l_old_Line_Adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type ;
618 l_old_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
619 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
620 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
621 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type ;
622 l_old_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type ;
623 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type ;
624 l_old_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type ;
625
626 l_message_data varchar2(2000);
627 l_org_id oe_order_lines.org_id%type;
628
629
630 CURSOR C_EVENT
631 IS
632 SELECT Course_End_date
633 FROM OTA_EVENTS
634 WHERE LINE_ID = l_line_id;
635 Line_id OE_ORDER_LINES.line_id%type;
636 l_course_end_date OTA_EVENTS.course_end_date%type;
637
638 CURSOR C_ORDER IS
639 SELECT HEADER_ID,ORG_ID
640 FROM OE_ORDER_LINES_ALL
641 WHERE LINE_ID = l_line_id;
642
643 BEGIN
644 hr_utility.set_location('Entering:'||l_proc, 5);
645
646 IF (funcmode = 'RUN') THEN
647 OPEN C_ORDER;
648 FETCH C_ORDER INTO l_header_id,l_org_id;
649 CLOSE C_ORDER;
650
651 OPEN C_EVENT;
652 FETCH C_EVENT INTO l_course_end_date;
653 IF C_EVENT%found THEN
654
655 /*l_header_id := WF_ENGINE.getitemattrnumber(
656 itemtype => itemtype,
657 itemkey => itemkey,
658 aname => 'HEADER_ID'); */
659
660
661 /*l_org_id := WF_ENGINE.getitemattrnumber(
662 itemtype => itemtype,
663 itemkey => itemkey,
664 aname => 'ORG_ID'); */
665 MO_GLOBAL.SET_POLICY_CONTEXT ('S', l_org_id); -- For MOAC support
666 --l_header_rec.header_id := l_header_id;
667 l_line_rec. operation := 'UPDATE';
668 l_line_rec.fulfillment_date := l_course_end_date ;
669 l_line_rec.line_id := l_line_id;
670 l_line_tbl(1) := l_line_rec;
671
672 --fnd_client_info.set_org_context(context => to_char(l_org_id));
673
674 OE_Order_GRP.Process_Order
675 ( p_api_version_number => 1.0
676 , p_init_msg_list => FND_API.G_FALSE
677 , p_return_values => l_return_values
678 , p_commit => FND_API.G_FALSE
679 , x_return_status => l_return_status
680 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
681 , p_control_rec => l_control_rec
682 , p_api_service_level => OE_GLOBALS.G_ALL_SERVICE
683 , x_msg_count => l_msg_count
684 , x_msg_data => l_msg_data
685 , p_header_rec => l_header_rec
686 , p_header_val_rec => l_header_val_rec
687 , p_Header_Adj_tbl => l_header_adj_tbl
688 , p_Header_Adj_val_tbl => l_header_adj_val_tbl
689 , p_Header_price_Att_tbl => l_header_price_att_tbl
690 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
691 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
692 , p_Header_Scredit_tbl => l_header_scredit_tbl
693 , p_Header_Scredit_val_tbl => l_header_scredit_val_tbl
694 , p_line_tbl => l_line_tbl
695 , p_line_val_tbl => l_line_val_tbl
696 , p_Line_Adj_tbl => l_line_adj_tbl
697 , p_Line_Adj_val_tbl => l_line_adj_val_tbl
698 , p_Line_price_Att_tbl => l_line_price_att_tbl
699 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
700 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
701 , p_Line_Scredit_tbl => l_line_scredit_tbl
702 , p_Line_Scredit_val_tbl => l_line_scredit_val_tbl
703 , p_Lot_Serial_tbl => l_lot_serial_tbl
704 , p_Lot_Serial_val_tbl => l_lot_serial_val_tbl
705 , p_Action_Request_tbl => l_request_tbl
706 , x_header_rec => l_header_rec
707 , x_header_val_rec => l_header_val_rec
708 , x_Header_Adj_tbl => l_header_adj_tbl
709 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
710 , x_Header_price_Att_tbl => l_header_price_att_tbl
711 , x_Header_Adj_Att_tbl => l_header_adj_att_tbl
712 , x_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
713 , x_Header_Scredit_tbl => l_header_scredit_tbl
714 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
715 , x_line_tbl => x_line_tbl --modified for bug 6347596
716 , x_line_val_tbl => l_line_val_tbl
717 , x_Line_Adj_tbl => l_line_adj_tbl
718 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
719 , x_Line_price_Att_tbl => l_line_price_att_tbl
720 , x_Line_Adj_Att_tbl => l_line_adj_att_tbl
721 , x_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
722 , x_Line_Scredit_tbl => l_line_scredit_tbl
723 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
724 , x_Lot_Serial_tbl => l_lot_serial_tbl
725 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
726 , x_action_request_tbl => l_action_request_tbl
727 );
728
729 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
730 ota_om_upd_api.retrieve_oe_messages(l_message_data);
731 RAISE FND_API.G_EXC_ERROR;
732
733 END IF;
734 resultout := 'COMPLETE';
735 return;
736 END IF;
737 CLOSE C_EVENT;
738 END IF;
739 -- CANCEL mode - activity 'compensation'
740 --
741 -- This is an event point is called with the effect of the activity must
742 -- be undone, for example when a process is reset to an earlier point
743 -- due to a loop back.
744 --
745 IF (funcmode = 'CANCEL') THEN
746
747 -- your cancel code goes here
748 null;
749
750 -- no result needed
751 resultout := 'COMPLETE';
752 return;
753 END IF;
754
755 EXCEPTION
756 WHEN OTHERS THEN
757 -- The line below records this function call in the error system
758 -- in the case of an exception.
759 wf_core.context('OTA_WF', 'update_fulfill_date',
760 itemtype, itemkey, to_char(actid), funcmode);
761 RAISE;
762
763 hr_utility.set_location(' Leaving:'||l_proc, 10);
764
765 END;
766
767 -- ----------------------------------------------------------------------------
768 -- |---------------------------< CHK_INVOICE_EXISTS >-------------------------|
769 -- ----------------------------------------------------------------------------
770 -- {Start Of Comments}
771 --
772 -- Description:
773 -- This procedure will be a used to check the invoice of Order Line.
774 --
775 -- IN
776 -- itemtype - type of the current item
777 -- itemkey - key of the current item
778 -- actid - process activity instance id
779 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
780 -- OUT
781 -- result
782 -- - COMPLETE[:<result>]
783 -- activity has completed with the indicated result
784 -- - WAITING
785 -- activity is waiting for additional transitions
786 -- - DEFERED
787 -- execution should be defered to background
788 -- - NOTIFIED[:<notification_id>:<assigned_user>]
789 -- activity has notified an external entity that this
790 -- step must be performed. A call to wf_engine.CompleteActivty
791 -- will signal when this step is complete. Optional
792 -- return of notification ID and assigned user.
793 -- - ERROR[:<error_code>]
794 -- function encountered an err--
795 --
796 -- Post Failure:
797 -- None.
798 --
799 -- Access Status:
800 -- Public.
801 --
802 -- {End Of Comments}
803 ----------------------------------------------------------------------------
804
805 PROCEDURE CHK_INVOICE_EXISTS (
806 itemtype IN VARCHAR2,
807 itemkey IN VARCHAR2,
808 actid IN NUMBER,
809 funcmode IN VARCHAR2,
810 resultout OUT NOCOPY VARCHAR2
811 )
812 IS
813
814 l_line_id oe_order_lines.line_id%type := to_number(itemkey);
815 l_proc varchar2(72) := g_package||'chk_invoice_exists';
816 l_invoice_quantity oe_order_lines.invoiced_quantity%type;
817
818
819 CURSOR c_invoice IS
820 SELECT
821 decode(invoiced_quantity,null,0,invoiced_quantity)
822 FROM
823 oe_order_lines
824 WHERE
825 line_id = l_line_id;
826
827
828 BEGIN
829 hr_utility.set_location('Entering:'||l_proc, 5);
830
831 IF (funcmode = 'RUN') THEN
832 OPEN c_invoice;
833 FETCH c_invoice into l_invoice_quantity;
834 IF c_invoice%found THEN
835 IF l_invoice_quantity = 1 then
836 resultout := wf_engine.eng_completed || ':' || 'Y';
837 ELSE
838 resultout := wf_engine.eng_completed || ':' || 'N';
839 END IF;
840 END IF;
841 CLOSE c_invoice;
842 END IF;
843
844 --
845 -- CANCEL mode - activity 'compensation'
846 --
847 -- This is an event point is called with the effect of the activity must
848 -- be undone, for example when a process is reset to an earlier point
849 -- due to a loop back.
850 --
851 IF (funcmode = 'CANCEL') THEN
852
853 -- your cancel code goes here
854 null;
855
856 -- no result needed
857 resultout := 'COMPLETE';
858 return;
859 END IF;
860
861
862 --
863 -- Other execution modes may be created in the future. Your
864 -- activity will indicate that it does not implement a mode
865 -- by returning null
866 --
867
868 EXCEPTION
869 WHEN OTHERS THEN
870 -- The line below records this function call in the error system
871 -- in the case of an exception.
872 wf_core.context('OTA_WF', 'Chk_invoice_exists',
873 itemtype, itemkey, to_char(actid), funcmode);
874 RAISE;
875 hr_utility.set_location(' Leaving:'||l_proc, 10);
876 END;
877
878 -- ----------------------------------------------------------------------------
879 -- |------------------------------------< CHECK_UOM>-------------------------|
880 -- ----------------------------------------------------------------------------
881 -- {Start Of Comments}
882 --
883 -- Description:
884 -- This procedure will be used to check the uom of the order line.
885 -- It will be called by the workflow activity.
886 --
887 --
888 -- Pre Conditions:
889 -- None.
890 --
891 -- IN
892 -- itemtype - type of the current item
893 -- itemkey - key of the current item
894 -- actid - process activity instance id
895 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
896 -- OUT
897 -- result
898 -- - COMPLETE[:<result>]
899 -- activity has completed with the indicated result
900 -- - WAITING
901 -- activity is waiting for additional transitions
902 -- - DEFERED
903 -- execution should be defered to background
904 -- - NOTIFIED[:<notification_id>:<assigned_user>]
905 -- activity has notified an external entity that this
906 -- step must be performed. A call to wf_engine.CompleteActivty
907 -- will signal when this step is complete. Optional
908 -- return of notification ID and assigned user.
909 -- - ERROR[:<error_code>]
910 -- function encountered an err--
911 --
912 -- Post Success:
913 -- Processing continues.
914 --
915 --
916 -- Post Failure:
917 -- None.
918 --
919 -- Access Status:
920 -- Public.
921 --
922 -- {End Of Comments}
923 ----------------------------------------------------------------------------
924 PROCEDURE CHECK_UOM(
925 Itemtype IN VARCHAR2,
926 Itemkey IN VARCHAR2,
927 actid IN NUMBER,
928 funcmode IN VARCHAR2,
929 resultout OUT NOCOPY VARCHAR2
930 )
931 IS
932
933 l_uom oe_order_lines.order_quantity_uom%type;
934 l_proc varchar2(72) := g_package||'check_uom';
935 l_line_id oe_order_lines.line_id%type := to_number(itemkey);
936 l_order_number oe_order_headers.order_number%type;
937 l_line_number oe_order_lines.line_number%type;
938 l_email_address per_people_f.email_address%type;
939 l_description varchar2(200);
940
941
942 CURSOR c_uom IS
943 SELECT
944 ol.order_quantity_uom ,
945 oh.order_number,
946 ol.line_number
947 FROM
948 oe_order_lines_all ol,
949 oe_order_headers_all oh
950 WHERE
951 oh.header_id = ol.header_id and
952 ol.line_id = l_line_id;
953
954 /*CURSOR c_event IS
955 SELECT email_address
956 FROM per_people_f
957 WHERE person_id IN(
958 select owner_id from
959 ota_events where
960 line_id = l_line_id);*/
961
962
963 BEGIN
964 hr_utility.set_location('Entering:'||l_proc, 5);
965 IF (funcmode = 'RUN') THEN
966
967 OPEN c_uom;
968 FETCH c_uom into l_uom,
969 l_order_number,
970 l_line_number;
971 IF c_uom%found THEN
972
973 IF l_uom = 'ENR' then
974 resultout := 'COMPLETE:ENR';
975 ELSIF l_uom= 'EVT' THEN
976
977 -- OPEN c_event;
978 -- FETCH c_event into l_email_address;
979 -- WF_ENGINE.SetItemattrnumber(itemtype,itemkey,'NOTIFICATION_APPROVER',l_email_address);
980 -- WF_ENGINE.SetItemattrnumber(p_itemtype,l_itemkey,'LIN_SHORT_DESCRIPTOR',l_order_number);
981
982 resultout := 'COMPLETE:EVT';
983 END IF;
984 END IF;
985 CLOSE c_uom;
986 END IF;
987 --
988 -- CANCEL mode - activity 'compensation'
989 --
990 -- This is an event point is called with the effect of the activity must
991 -- be undone, for example when a process is reset to an earlier point
992 -- due to a loop back.
993 --
994 IF (funcmode = 'CANCEL') THEN
995
996 -- your cancel code goes here
997 null;
998
999 -- no result needed
1000 resultout := 'COMPLETE';
1001 return;
1002 END IF;
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 -- The line below records this function call in the error system
1006 -- in the case of an exception.
1007 wf_core.context('OTA_WF', 'Check_UOM',
1008 itemtype, itemkey, to_char(actid), funcmode);
1009 RAISE;
1010
1011 hr_utility.set_location(' Leaving:'||l_proc, 10);
1012 END;
1013
1014
1015
1016 -- ----------------------------------------------------------------------------
1017 -- |------------------------------------< CHECK_CREATION>----------------------|
1018 -- ----------------------------------------------------------------------------
1019 -- {Start Of Comments}
1020 --
1021 -- Description:
1022 -- This procedure will be a concurrent process which run in the background.
1023 --
1024 -- This procedure will only be used for OTA and OM integration. Basically this
1025 -- procedure will select all delegate booking data that has daemon_flag='Y' and
1026 -- Daemon_type is not nul. If the enrollment got canceled and there is a
1027 -- waitlisted student then the automatic waitlist processing will be called.
1028 --
1029 -- Pre Conditions:
1030 -- None.
1031 --
1032 -- IN
1033 -- itemtype - type of the current item
1034 -- itemkey - key of the current item
1035 -- actid - process activity instance id
1036 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
1037 -- OUT
1038 -- result
1039 -- - COMPLETE[:<result>]
1040 -- activity has completed with the indicated result
1041 -- - WAITING
1042 -- activity is waiting for additional transitions
1043 -- - DEFERED
1044 -- execution should be defered to background
1045 -- - NOTIFIED[:<notification_id>:<assigned_user>]
1046 -- activity has notified an external entity that this
1047 -- step must be performed. A call to wf_engine.CompleteActivty
1048 -- will signal when this step is complete. Optional
1049 -- return of notification ID and assigned user.
1050 -- - ERROR[:<error_code>]
1051 -- function encountered an err--
1052 --
1053 -- Post Success:
1054 -- Processing continues.
1055 --
1056 --
1057 -- Post Failure:
1058 -- None.
1059 --
1060 -- Access Status:
1061 -- Public.
1062 --
1063 -- {End Of Comments}
1064 ----------------------------------------------------------------------------
1065 PROCEDURE CHECK_CREATION(
1066 Itemtype IN VARCHAR2
1067 ,Itemkey IN VARCHAR2
1068 ,actid IN NUMBER
1069 ,funcmode IN VARCHAR2
1070 ,resultout OUT NOCOPY VARCHAR2
1071 )
1072
1073 IS
1074
1075 l_uom oe_order_lines.order_quantity_uom%type;
1076 l_proc varchar2(72) := g_package||'ota_creation_ck';
1077 l_line_id oe_order_lines.line_id%type := to_number(itemkey);
1078 l_exist varchar2(1);
1079
1080
1081 CURSOR c_uom IS
1082 SELECT
1083 order_quantity_uom
1084 FROM
1085 oe_order_lines
1086 WHERE
1087 line_id = l_line_id;
1088
1089 CURSOR c_evt
1090 IS
1091 SELECT
1092 null
1093 FROM
1094 OTA_EVENTS
1095 WHERE
1096 line_id = l_line_id;
1097
1098 CURSOR c_enr
1099 IS
1100 SELECT
1101 null
1102 FROM
1103 ota_delegate_bookings
1104 WHERE
1105 line_id = l_line_id;
1106
1107
1108 BEGIN
1109 hr_utility.set_location('Entering:'||l_proc, 5);
1110 IF (funcmode = 'RUN') THEN
1111
1112 OPEN c_uom;
1113 FETCH c_uom into l_uom;
1114 IF c_uom%found THEN
1115
1116 IF l_uom = 'ENR' then
1117 OPEN C_ENR;
1118 FETCH c_enr INTO l_exist;
1119 IF c_enr%found then
1120 resultout := wf_engine.eng_completed || ':' || 'Y';
1121 ELSE
1122 resultout := wf_engine.eng_completed || ':' || 'N';
1123 END IF;
1124 CLOSE c_enr;
1125 ELSIF l_uom= 'EVT' THEN
1126 OPEN C_EVT;
1127 FETCH c_evt INTO l_exist;
1128 IF c_evt%found then
1129 resultout := wf_engine.eng_completed || ':' || 'Y';
1130 ELSE
1131 resultout := wf_engine.eng_completed || ':' || 'N';
1132 END IF;
1133 CLOSE c_evt;
1134 END IF;
1135 END IF;
1136 CLOSE c_uom;
1137 END IF;
1138 -- CANCEL mode - activity 'compensation'
1139 --
1140 -- This is an event point is called with the effect of the activity must
1141 -- be undone, for example when a process is reset to an earlier point
1142 -- due to a loop back.
1143 --
1144 IF (funcmode = 'CANCEL') THEN
1145
1146 -- your cancel code goes here
1147 null;
1148
1149 -- no result needed
1150 resultout := 'COMPLETE';
1151 return;
1152 END IF;
1153
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 -- The line below records this function call in the error system
1157 -- in the case of an exception.
1158 wf_core.context('OTA_WF', 'Check_Creation',
1159 itemtype, itemkey, to_char(actid), funcmode);
1160 RAISE;
1161
1162 hr_utility.set_location(' Leaving:'||l_proc, 10);
1163 END;
1164
1165 -- ----------------------------------------------------------------------------
1166 -- |------------------------< CHK_ENROLL_STATUS_ADV >-------------------------|
1167 -- ----------------------------------------------------------------------------
1168 -- {Start Of Comments}
1169 --
1170 -- Description:
1171 -- This procedure will be used to check the uom of the order line.
1172 -- It will be called by the workflow activity.
1173 --
1174 --
1175 -- Pre Conditions:
1176 -- None.
1177 --
1178 -- IN
1179 -- itemtype - type of the current item
1180 -- itemkey - key of the current item
1181 -- actid - process activity instance id
1182 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
1183 -- OUT
1184 -- result
1185 -- - COMPLETE[:<result>]
1186 -- activity has completed with the indicated result
1187 -- - WAITING
1188 -- activity is waiting for additional transitions
1189 -- - DEFERED
1190 -- execution should be defered to background
1191 -- - NOTIFIED[:<notification_id>:<assigned_user>]
1192 -- activity has notified an external entity that this
1193 -- step must be performed. A call to wf_engine.CompleteActivty
1194 -- will signal when this step is complete. Optional
1195 -- return of notification ID and assigned user.
1196 -- - ERROR[:<error_code>]
1197 -- function encountered an err--
1198 --
1199 --
1200 -- Post Success:
1201 -- Processing continues.
1202 --
1203 --
1204 -- Post Failure:
1205 -- None.
1206 --
1207 -- Access Status:
1208 -- Public.
1209 --
1210 -- {End Of Comments}
1211 ----------------------------------------------------------------------------
1212
1213 PROCEDURE CHK_ENROLL_STATUS_ADV (
1214 Itemtype IN VARCHAR2
1215 ,Itemkey IN VARCHAR2
1216 ,actid IN NUMBER
1217 ,funcmode IN VARCHAR2
1218 ,resultout OUT NOCOPY VARCHAR2)
1219
1220 IS
1221
1222 CURSOR c_Enroll_type
1223 IS
1224 SELECT
1225 bst.type
1226 FROM
1227 ota_delegate_bookings tdb, ota_booking_status_types bst
1228 WHERE
1229 line_id = to_number(itemkey)AND
1230 bst.booking_status_type_id = tdb.booking_status_type_id;
1231
1232 l_proc varchar2(72) := g_package||'chk_enroll_status_adv';
1233 l_type ota_booking_status_types.type%type;
1234
1235 BEGIN
1236 hr_utility.set_location('Entering:'||l_proc, 5);
1237 IF (funcmode = 'RUN') THEN
1238 OPEN c_enroll_type;
1239 FETCH c_enroll_type INTO l_type;
1240 IF c_enroll_type%found THEN
1241 IF l_type = 'P' THEN
1242 resultout := 'COMPLETE:PLACED';
1243 ELSIF l_type = 'W' THEN
1244 resultout := 'COMPLETE:WAITLISTED';
1245
1246 END IF;
1247 END IF;
1248 CLOSE c_enroll_type;
1249 END IF;
1250 -- CANCEL mode - activity 'compensation'
1251 --
1252 -- This is an event point is called with the effect of the activity must
1253 -- be undone, for example when a process is reset to an earlier point
1254 -- due to a loop back.
1255 --
1256 IF (funcmode = 'CANCEL') THEN
1257
1258 -- your cancel code goes here
1259 null;
1260
1261 -- no result needed
1262 resultout := 'COMPLETE';
1263 return;
1264 END IF;
1265
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268 -- The line below records this function call in the error system
1269 -- in the case of an exception.
1270 wf_core.context('OTA_WF', 'chk_enroll_status_adv',
1271 itemtype, itemkey, to_char(actid), funcmode);
1272 RAISE;
1273
1274 hr_utility.set_location(' Leaving:'||l_proc, 10);
1275
1276 END;
1277 --
1278 -- ----------------------------------------------------------------------------
1279 -- |------------------------< CHK_ENROLL_STATUS_ARR >-------------------------|
1280 -- ----------------------------------------------------------------------------
1281 -- {Start Of Comments}
1282 --
1283 -- Description:
1284 -- This procedure will be used to check the uom of the order line.
1285 -- It will be called by the workflow activity.
1286 --
1287 --
1288 -- Pre Conditions:
1289 -- None.
1290 --
1291 -- IN
1292 -- itemtype - type of the current item
1293 -- itemkey - key of the current item
1294 -- actid - process activity instance id
1295 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
1296 -- OUT
1297 -- result
1298 -- - COMPLETE[:<result>]
1299 -- activity has completed with the indicated result
1300 -- - WAITING
1301 -- activity is waiting for additional transitions
1302 -- - DEFERED
1303 -- execution should be defered to background
1304 -- - NOTIFIED[:<notification_id>:<assigned_user>]
1305 -- activity has notified an external entity that this
1306 -- step must be performed. A call to wf_engine.CompleteActivty
1307 -- will signal when this step is complete. Optional
1308 -- return of notification ID and assigned user.
1309 -- - ERROR[:<error_code>]
1310 -- function encountered an err--
1311 --
1312 --
1313 -- Post Success:
1314 -- Processing continues.
1315 --
1316 --
1317 -- Post Failure:
1318 -- None.
1319 --
1320 -- Access Status:
1321 -- Public.
1322 --
1323 -- {End Of Comments}
1324 ----------------------------------------------------------------------------
1325 PROCEDURE CHK_ENROLL_STATUS_ARR (
1326 Itemtype IN VARCHAR2
1327 ,Itemkey IN VARCHAR2
1328 ,actid IN NUMBER
1329 ,funcmode IN VARCHAR2
1330 ,resultout OUT NOCOPY VARCHAR2)
1331
1332 IS
1333
1334 CURSOR c_Enroll_type
1335 IS
1336 SELECT
1337 bst.type
1338 FROM
1339 ota_delegate_bookings tdb,
1340 ota_booking_status_types bst
1341 WHERE
1342 line_id = to_number(itemkey)AND
1343 bst.booking_status_type_id = tdb.booking_status_type_id;
1344
1345 l_proc varchar2(72) := g_package||'chk_enroll_status_arr';
1346 l_type ota_booking_status_types.type%type;
1347
1348 BEGIN
1349 hr_utility.set_location('Entering:'||l_proc, 5);
1350 IF (funcmode = 'RUN') THEN
1351 OPEN c_enroll_type;
1352 FETCH c_enroll_type INTO l_type;
1353 IF c_enroll_type%found THEN
1354 IF l_type = 'P' THEN
1355 resultout := 'COMPLETE:PLACED';
1356 ELSIF l_type = 'W' THEN
1357 resultout := 'COMPLETE:WAITLISTED';
1358 ELSIF l_type = 'A' THEN
1359 resultout := 'COMPLETE:ATTENDED';
1360 ELSIF l_type = 'R' THEN
1361 resultout := 'COMPLETE:REQUESTED';
1362
1363 END IF;
1364 END IF;
1365 CLOSE c_enroll_type;
1366 END IF;
1367 -- CANCEL mode - activity 'compensation'
1368 --
1369 -- This is an event point is called with the effect of the activity must
1370 -- be undone, for example when a process is reset to an earlier point
1371 -- due to a loop back.
1372 --
1373 IF (funcmode = 'CANCEL') THEN
1374
1375 -- your cancel code goes here
1376 null;
1377
1378 -- no result needed
1379 resultout := 'COMPLETE';
1380 return;
1381 END IF;
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 -- The line below records this function call in the error system
1386 -- in the case of an exception.
1387 wf_core.context('OTA_WF', 'chk_enroll_status_arr',
1388 itemtype, itemkey, to_char(actid), funcmode);
1389 RAISE;
1390
1391 hr_utility.set_location(' Leaving:'||l_proc, 10);
1392
1393 END;
1394
1395 --
1396 -- ----------------------------------------------------------------------------
1397 -- |----------------------------< CHECK_INVOICE_RULE >--------------------------|
1398 -- ----------------------------------------------------------------------------
1399 -- {Start Of Comments}
1400 --
1401 -- Description:
1402 -- This procedure will be used to check the invoicing rule of the order line.
1403 -- It will be called by the workflow activity.
1404 --
1405 --
1406 -- Pre Conditions:
1407 -- None.
1408 --
1409 -- IN
1410 -- itemtype - type of the current item
1411 -- itemkey - key of the current item
1412 -- actid - process activity instance id
1413 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
1414 -- OUT
1415 -- result
1416 -- - COMPLETE[:<result>]
1417 -- activity has completed with the indicated result
1418 -- - WAITING
1419 -- activity is waiting for additional transitions
1420 -- - DEFERED
1421 -- execution should be defered to background
1422 -- - NOTIFIED[:<notification_id>:<assigned_user>]
1423 -- activity has notified an external entity that this
1424 -- step must be performed. A call to wf_engine.CompleteActivty
1425 -- will signal when this step is complete. Optional
1426 -- return of notification ID and assigned user.
1427 -- - ERROR[:<error_code>]
1428 -- function encountered an err--
1429 --
1430 --
1431 -- Post Success:
1432 -- Processing continues.
1433 --
1434 --
1435 -- Post Failure:
1436 -- None.
1437 --
1438 -- Access Status:
1439 -- Public.
1440 --
1441 -- {End Of Comments}
1442 ----------------------------------------------------------------------------
1443
1444 PROCEDURE CHECK_INVOICE_RULE (
1445 Itemtype IN VARCHAR2
1446 ,Itemkey IN VARCHAR2
1447 ,actid IN NUMBER
1448 ,funcmode IN VARCHAR2
1449 ,resultout OUT NOCOPY VARCHAR2)
1450
1451 IS
1452 CURSOR c_invoice_rule
1453 IS
1454 SELECT
1455 invoicing_rule_id
1456 FROM
1457 oe_order_lines_all
1458 WHERE
1459 line_id = to_number(itemkey);
1460
1461 l_proc varchar2(72) := g_package||'check_invoice_rule';
1462 l_rule_id oe_order_lines_all.invoicing_rule_id%type;
1463
1464 BEGIN
1465 hr_utility.set_location('Entering:'||l_proc, 5);
1466 IF (funcmode = 'RUN') THEN
1467 OPEN c_invoice_rule;
1468 FETCH c_invoice_rule INTO l_rule_id;
1469 IF c_invoice_rule%found THEN
1470 IF l_rule_id = -2 THEN
1471 resultout := 'COMPLETE:ADVANCED';
1472 ELSIF l_rule_id = -3 THEN
1473 resultout := 'COMPLETE:ARREAR';
1474 END IF;
1475 END IF;
1476 CLOSE c_invoice_rule;
1477 END IF;
1478 -- CANCEL mode - activity 'compensation'
1479 --
1480 -- This is an event point is called with the effect of the activity must
1481 -- be undone, for example when a process is reset to an earlier point
1482 -- due to a loop back.
1483 --
1484 IF (funcmode = 'CANCEL') THEN
1485
1486 -- your cancel code goes here
1487 null;
1488
1489 -- no result needed
1490 resultout := 'COMPLETE';
1491 return;
1492 END IF;
1493
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 -- The line below records this function call in the error system
1497 -- in the case of an exception.
1498 wf_core.context('OTA_WF', 'check_invoice_rule',
1499 itemtype, itemkey, to_char(actid), funcmode);
1500 RAISE;
1501
1502
1503 hr_utility.set_location(' Leaving:'||l_proc, 10);
1504
1505 END;
1506 --
1507
1508 -- ----------------------------------------------------------------------------
1509 -- |----------------------------< CANCEL_ENROLLMENT>--------------------------|
1510 -- ----------------------------------------------------------------------------
1511 -- {Start Of Comments}
1512 --
1513 -- Description:
1514 -- This procedure will be used to cancel an enrollment.
1515 --
1516 --
1517 -- Pre Conditions:
1518 -- None.
1519 --
1520 -- In Arguments:
1521 -- itemtype,
1522 -- itemkey
1523 -- actid
1524 -- funcmode
1525 --
1526 -- Out Arguments:
1527 -- resultout
1528 --
1529 -- Post Success:
1530 -- Processing continues.
1531 --
1532 --
1533 -- Post Failure:
1534 -- None.
1535 --
1536 -- Access Status:
1537 -- Public.
1538 --
1539 -- {End Of Comments}
1540 ----------------------------------------------------------------------------
1541
1542 PROCEDURE CANCEL_ENROLLMENT(
1543 Itemtype IN VARCHAR2
1544 ,Itemkey IN VARCHAR2
1545 ,actid IN NUMBER
1546 ,funcmode IN VARCHAR2
1547 ,resultout OUT NOCOPY VARCHAR2)IS
1548
1549 l_org_id oe_order_lines.org_id%type;
1550 l_proc varchar2(72) := g_package||'cancel_enrollment';
1551 l_return_status varchar2(1);
1552
1553 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
1554 l_booking_status_type varchar2(3);
1555
1556 CURSOR C_ENROLLMENT IS
1557 SELECT
1558 Booking_status_type_id
1559 FROM
1560 OTA_DELEGATE_BOOKINGS
1561 WHERE
1562 Line_id = to_number(itemkey);
1563
1564 --
1565
1566 CURSOR C_BOOKING_STATUS IS
1567 SELECT
1568 Type
1569 FROM
1570 OTA_BOOKING_STATUS_TYPES
1571 WHERE
1572 booking_status_type_id = l_booking_status_type_id;
1573
1574
1575 BEGIN
1576 IF (funcmode = 'RUN') THEN
1577 hr_utility.set_location(' entering:'||l_proc, 5);
1578 OPEN C_ENROLLMENT;
1579 FETCH c_enrollment into
1580 l_Booking_status_type_id;
1581 IF c_enrollment%found THEN
1582 OPEN C_BOOKING_STATUS;
1583 FETCH c_booking_status into
1584 l_booking_status_type;
1585
1586 IF c_booking_status%found then
1587 IF l_booking_status_type <>'C' then
1588 l_org_id := WF_ENGINE.getitemattrnumber(
1589 itemtype => itemtype,
1590 itemkey => itemkey,
1591 aname => 'ORG_ID');
1592
1593 ota_cancel_api.delete_cancel_line
1594 (
1595 p_line_id =>to_number(itemkey),
1596 p_org_id => l_org_id,
1597 p_UOM => 'ENR',
1598 P_daemon_type => 'C',
1599 x_return_status => l_return_status);
1600 resultout := 'COMPLETE';
1601 return;
1602 ELSE
1603 resultout := 'COMPLETE';
1604 return;
1605 END IF;
1606 END IF;
1607 CLOSE C_BOOKING_STATUS;
1608 END IF;
1609 CLOSE C_ENROLLMENT;
1610
1611 END IF;
1612 -- CANCEL mode - activity 'compensation'
1613 --
1614 -- This is an event point is called with the effect of the activity must
1615 -- be undone, for example when a process is reset to an earlier point
1616 -- due to a loop back.
1617 --
1618 IF (funcmode = 'CANCEL') THEN
1619
1620 -- your cancel code goes here
1621 null;
1622
1623 -- no result needed
1624 resultout := 'COMPLETE';
1625 return;
1626 END IF;
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 -- The line below records this function call in the error system
1631 -- in the case of an exception.
1632 wf_core.context('OTA_WF', 'cancel_enrollment',
1633 itemtype, itemkey, to_char(actid), funcmode);
1634 RAISE;
1635
1636 hr_utility.set_location(' Leaving:'||l_proc, 10);
1637
1638
1639 END;
1640
1641 --
1642 -- ----------------------------------------------------------------------------
1643 -- |-------------------------< UPDATE_OWNER_EMAIL >--------------------------|
1644 -- ----------------------------------------------------------------------------
1645 -- {Start Of Comments}
1646 --
1647 -- Description:
1648 -- This procedure will be used to check the invoicing rule of the order line.
1649 -- It will be called by the workflow activity.
1650 --
1651 --
1652 -- Pre Conditions:
1653 -- None.
1654 --
1655 -- IN
1656 -- itemtype - type of the current item
1657 -- itemkey - key of the current item
1658 -- actid - process activity instance id
1659 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
1660 -- OUT
1661 -- result
1662 -- - COMPLETE[:<result>]
1663 -- activity has completed with the indicated result
1664 -- - WAITING
1665 -- activity is waiting for additional transitions
1666 -- - DEFERED
1667 -- execution should be defered to background
1668 -- - NOTIFIED[:<notification_id>:<assigned_user>]
1669 -- activity has notified an external entity that this
1670 -- step must be performed. A call to wf_engine.CompleteActivty
1671 -- will signal when this step is complete. Optional
1672 -- return of notification ID and assigned user.
1673 -- - ERROR[:<error_code>]
1674 -- function encountered an err--
1675 --
1676 --
1677 -- Post Success:
1678 -- Processing continues.
1679 --
1680 --
1681 -- Post Failure:
1682 -- None.
1683 --
1684 -- Access Status:
1685 -- Public.
1686 --
1687 -- {End Of Comments}
1688 ----------------------------------------------------------------------------
1689
1690 PROCEDURE UPDATE_OWNER_EMAIL (
1691 Itemtype IN VARCHAR2
1692 ,Itemkey IN VARCHAR2
1693 ,actid IN NUMBER
1694 ,funcmode IN VARCHAR2
1695 ,resultout OUT NOCOPY VARCHAR2)
1696
1697 IS
1698
1699 l_proc varchar2(72) := g_package||'update_owner_email';
1700 l_line_id oe_order_lines.line_id%type := to_number(itemkey);
1701 l_user_name fnd_user.user_name%type;
1702
1703
1704 CURSOR c_uom IS
1705 SELECT
1706 ol.order_quantity_uom ,
1707 oh.order_number,
1708 ol.line_number
1709 FROM
1710 oe_order_lines_all ol,
1711 oe_order_headers_all oh
1712 WHERE
1713 oh.header_id = ol.header_id and
1714 ol.line_id = l_line_id;
1715
1716 CURSOR c_event IS
1717 SELECT user_name
1718 FROM FND_USER
1719 WHERE employee_id IN(
1720 select owner_id from
1721 ota_events where
1722 line_id = l_line_id);
1723
1724
1725 BEGIN
1726 hr_utility.set_location('Entering:'||l_proc, 5);
1727 IF (funcmode = 'RUN') THEN
1728
1729 OPEN c_event;
1730 FETCH c_event into l_user_name;
1731 WF_ENGINE.SetItemattrtext(itemtype,itemkey,'NOTIFICATION_APPROVER',l_user_name);
1732 resultout := 'COMPLETE';
1733 CLOSE c_event;
1734 END IF;
1735 --
1736 -- CANCEL mode - activity 'compensation'
1737 --
1738 -- This is an event point is called with the effect of the activity must
1739 -- be undone, for example when a process is reset to an earlier point
1740 -- due to a loop back.
1741 --
1742 IF (funcmode = 'CANCEL') THEN
1743
1744 -- your cancel code goes here
1745 null;
1746
1747 -- no result needed
1748 resultout := 'COMPLETE';
1749 return;
1750 END IF;
1751 EXCEPTION
1752 WHEN OTHERS THEN
1753 -- The line below records this function call in the error system
1754 -- in the case of an exception.
1755 wf_core.context('OTA_WF', 'UPDATE_OWNER_EMAIL',
1756 itemtype, itemkey, to_char(actid), funcmode);
1757 RAISE;
1758
1759 hr_utility.set_location(' Leaving:'||l_proc, 10);
1760 END;
1761
1762 -- ----------------------------------------------------------------------------
1763 -- |------------------------< CHK_EVENT_ENROLL_STATUS >------------------------|
1764 -- ----------------------------------------------------------------------------
1765 -- {Start Of Comments}
1766 --
1767 -- Description:
1768 -- This procedure will be used to check the completion of the order line for
1769 -- Private Event whis is come from OM.
1770 -- It will be called by the workflow activity.
1771 --
1772 --
1773 -- Pre Conditions:
1774 -- None.
1775 --
1776 -- IN
1777 -- itemtype - type of the current item
1778 -- itemkey - key of the current item
1779 -- actid - process activity instance id
1780 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
1781 -- OUT
1782 -- result
1783 -- - COMPLETE[:<result>]
1784 -- activity has completed with the indicated result
1785 -- - WAITING
1786 -- activity is waiting for additional transitions
1787 -- - DEFERED
1788 -- execution should be defered to background
1789 -- - NOTIFIED[:<notification_id>:<assigned_user>]
1790 -- activity has notified an external entity that this
1791 -- step must be performed. A call to wf_engine.CompleteActivty
1792 -- will signal when this step is complete. Optional
1793 -- return of notification ID and assigned user.
1794 -- - ERROR[:<error_code>]
1795 -- function encountered an err--
1796 --
1797 --
1798 -- Post Success:
1799 -- Processing continues.
1800 --
1801 --
1802 -- Post Failure:
1803 -- None.
1804 --
1805 -- Access Status:
1806 -- Public.
1807 --
1808 -- {End Of Comments}
1809 ----------------------------------------------------------------------------
1810 PROCEDURE CHK_EVENT_ENROLL_STATUS (
1811 Itemtype IN VARCHAR2
1812 ,Itemkey IN VARCHAR2
1813 ,actid IN NUMBER
1814 ,funcmode IN VARCHAR2
1815 ,resultout OUT NOCOPY VARCHAR2)
1816
1817 IS
1818
1819 l_event_id ota_events.event_id%type;
1820
1821
1822 CURSOR C_EVENT
1823 IS
1824 SELECT
1825 EVENT_ID
1826 FROM
1827 OTA_EVENTS
1828 WHERE
1829 line_id = to_number(itemkey);
1830
1831 CURSOR c_Enroll_type
1832 IS
1833 SELECT
1834 bst.type
1835 FROM
1836 ota_delegate_bookings tdb,
1837 ota_booking_status_types bst
1838 WHERE
1839 event_id = l_event_id AND
1840 bst.booking_status_type_id = tdb.booking_status_type_id;
1841
1842 l_proc varchar2(72) := g_package||'chk_event_enroll_status';
1843 l_type ota_booking_status_types.type%type;
1844
1845 BEGIN
1846 hr_utility.set_location('Entering:'||l_proc, 5);
1847 IF (funcmode = 'RUN') THEN
1848 OPEN C_EVENT;
1849 FETCH C_EVENT INTO l_event_id;
1850 IF c_event%found then
1851 OPEN c_enroll_type;
1852 FETCH c_enroll_type INTO l_type;
1853 IF c_enroll_type%found THEN
1854 IF l_type = 'P' THEN
1855 resultout := 'COMPLETE:PLACED';
1856 ELSIF l_type = 'W' THEN
1857 resultout := 'COMPLETE:WAITLISTED';
1858 ELSIF l_type = 'A' THEN
1859 resultout := 'COMPLETE:ATTENDED';
1860 ELSIF l_type = 'R' THEN
1861 resultout := 'COMPLETE:REQUESTED';
1862
1863 END IF;
1864 END IF;
1865 CLOSE c_enroll_type;
1866 END IF;
1867 CLOSE C_EVENT;
1868 END IF;
1869 -- CANCEL mode - activity 'compensation'
1870 --
1871 -- This is an event point is called with the effect of the activity must
1872 -- be undone, for example when a process is reset to an earlier point
1873 -- due to a loop back.
1874 --
1875 IF (funcmode = 'CANCEL') THEN
1876
1877 -- your cancel code goes here
1878 null;
1879
1880 -- no result needed
1881 resultout := 'COMPLETE';
1882 return;
1883 END IF;
1884
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 -- The line below records this function call in the error system
1888 -- in the case of an exception.
1889 wf_core.context('OTA_WF', 'chk_enroll_status_arr',
1890 itemtype, itemkey, to_char(actid), funcmode);
1891 RAISE;
1892
1893 hr_utility.set_location(' Leaving:'||l_proc, 10);
1894
1895 END;
1896
1897 -- --------------------------------------------------------------------- *
1898 -- Name : set_v2_attributes
1899 -- Purpose: The wf text attributes values can only be 1950chars in length.
1900 -- This procedure converts the possible 15600 plsql varchar2 value
1901 -- to multiple 1950 char sql value chunks that can be held
1902 -- as attributes in the database.
1903 -- (internal)
1904 -- --------------------------------------------------------------------- *
1905 PROCEDURE set_v2_attributes
1906 (p_wf_attribute_value VARCHAR2
1907 ,p_wf_attribute_name VARCHAR2
1908 ,p_nid NUMBER
1909 )
1910 IS
1911 l_attribute_length NUMBER DEFAULT length(p_wf_attribute_value);
1912 l_counter NUMBER DEFAULT 0;
1913 l_max_no_of_attribs NUMBER DEFAULT 8;
1914 l_next_attrib NUMBER;
1915 BEGIN
1916 IF l_attribute_length > 1950 THEN
1917 -- Loop through p_wf_attribute_value and grab chunks of 1950 bytes of it
1918 -- (up to the max number catered for by the workflow) setting the
1919 -- appropiate wf attribute.
1920 FOR x IN 1 .. least
1921 (trunc
1922 (l_attribute_length/1950), l_max_no_of_attribs)
1923 LOOP
1924 l_counter := l_counter + 1;
1925 wf_notification.setAttrText ( p_nid
1926 , p_wf_attribute_name|| '_' ||x
1927 , substr( p_wf_attribute_value
1928 , ((x * 1950) - 1949)
1929 , 1950
1930 )
1931 );
1932 END LOOP;
1933 -- The previous loop took as many 1950 byte chunks as possible. If
1934 -- there is still a workflow attribute available (there are
1935 -- l_max_no_of_attributes available), use the next one in line to
1936 -- hold the remainder to the value.
1937 IF ((l_counter < l_max_no_of_attribs)
1938 AND mod(l_attribute_length,1950)<> 0) THEN
1939 l_next_attrib := l_counter + 1;
1940 wf_notification.setAttrText ( p_nid
1941 , p_wf_attribute_name|| '_' ||l_next_attrib
1942 , substr( p_wf_attribute_value
1943 , l_attribute_length
1944 - (mod (l_attribute_length,1950)- 1)
1945 , l_attribute_length
1946 )
1947 );
1948 END IF;
1949 ELSE
1950 -- There are less than 1950 chars in the value, so it can be stored
1951 -- whole in the first workflow attribute.
1952 wf_notification.setAttrText ( p_nid
1953 , p_wf_attribute_name|| '_' ||'1'
1954 , p_wf_attribute_value
1955 );
1956 END IF;
1957 END;
1958 -- --------------------------------------------------------------------- *
1959 -- Name : send_text_notification
1960 -- Purpose: Send Notification in Text Format
1961 -- --------------------------------------------------------------------- *
1962 FUNCTION send_text_notification
1963 ( p_user_name IN varchar2
1964 , p_subject IN varchar2
1965 , p_text_body IN varchar2 DEFAULT null
1966 , p_from_role IN varchar2 DEFAULT null
1967 )
1968 RETURN number
1969 IS
1970 PRAGMA autonomous_transaction;
1971 l_message_type wf_messages.type%type;
1972 l_message_name wf_messages.name%type := 'OTA_TEXT_MSG';
1973 l_nid number;
1974 BEGIN
1975 --
1976 l_message_type := 'OTWF';
1977 l_nid:=wf_notification.send( upper(p_user_name)
1978 , l_message_type
1979 , l_message_name
1980 );
1981 --
1982 -- p_from_role contains the name of the person who is sending this
1983 -- Notification
1984 --
1985 if(p_from_role is not null)
1986 then
1987 wf_notification.setAttrText ( l_nid , '#FROM_ROLE' , p_from_role);
1988 end if;
1989 --
1990 wf_notification.setAttrText ( l_nid , 'SUBJECT' , p_subject);
1991 set_v2_attributes
1992 (p_wf_attribute_value => p_text_body
1993 ,p_wf_attribute_name => 'TEXT_BODY'
1994 ,p_nid => l_nid);
1995 wf_notification.denormalize_notification(l_nid);
1996 commit;
1997 RETURN l_nid;
1998 END send_text_notification;
1999 --
2000 -- --------------------------------------------------------------------- *
2001 -- Name : send_html_text_notification
2002 -- Purpose: Send Notification in Text and Html Format
2003 -- --------------------------------------------------------------------- *
2004 FUNCTION send_html_text_notification
2005 ( p_user_name IN varchar2
2006 , p_subject IN varchar2
2007 , p_html_body IN varchar2 DEFAULT null
2008 , p_text_body IN varchar2 DEFAULT null
2009 , p_from_role IN varchar2 DEFAULT null
2010 )
2011 RETURN number
2012 IS
2013 PRAGMA autonomous_transaction;
2014 l_message_type wf_messages.type%type;
2015 l_message_name wf_messages.name%type := 'OTA_TEXT_HTML_MSG';
2016 l_nid number;
2017 BEGIN
2018 --
2019 l_message_type := 'OTWF';
2020 l_nid:=wf_notification.send( upper(p_user_name)
2021 , l_message_type
2022 , l_message_name
2023 );
2024 --
2025 -- p_from_role contains the name of the person who is sending this
2026 -- Notification
2027 --
2028 if(p_from_role is not null)
2029 then
2030 wf_notification.setAttrText ( l_nid , '#FROM_ROLE' , p_from_role);
2031 end if;
2032 --
2033 wf_notification.setAttrText ( l_nid , 'SUBJECT' , p_subject);
2034 set_v2_attributes
2035 (p_wf_attribute_value => p_html_body
2036 ,p_wf_attribute_name => 'HTML_BODY'
2037 ,p_nid => l_nid);
2038 if p_text_body is not null then
2039 set_v2_attributes
2040 (p_wf_attribute_value => p_text_body
2041 ,p_wf_attribute_name => 'TEXT_BODY'
2042 ,p_nid => l_nid);
2043 end if;
2044 wf_notification.denormalize_notification(l_nid);
2045 commit;
2046 RETURN l_nid;
2047 END send_html_text_notification;
2048 -- --------------------------------------------------------------------- *
2049
2050 --
2051 -- --------------------------------------------------------------------- *
2052 -- Name : Create_AdHoc_User
2053 -- Purpose: To create Adhoc User
2054 -- --------------------------------------------------------------------- *
2055 FUNCTION Create_AdHoc_User
2056 (p_email_address IN VARCHAR2)
2057 RETURN varchar2
2058 IS
2059 PRAGMA autonomous_transaction;
2060 l_user_name wf_users.name%TYPE ;
2061 l_user_display_name wf_users.display_name%TYPE ;
2062 BEGIN
2063 -- Create an ad-hoc user
2064 wf_directory.CreateAdHocUser
2065 ( name => l_user_name
2066 , display_name => l_user_display_name
2067 , email_address => p_email_address
2068 , notification_preference => 'MAILHTML'
2069 );
2070 --
2071 commit;
2072 RETURN l_user_name;
2073 END Create_AdHoc_User;
2074 -- --------------------------------------------------------------------- *
2075 -- Name : send_notification
2076 -- Purpose: See header
2077 -- --------------------------------------------------------------------- *
2078 PROCEDURE send_notification
2079 ( p_email_addresses IN varchar2
2080 , p_person_ids IN varchar2
2081 , p_subject IN varchar2
2082 , p_html_body IN varchar2 DEFAULT null
2083 , p_text_body IN varchar2 DEFAULT null
2084 , p_from_role IN varchar2 DEFAULT null
2085 )
2086 IS
2087 l_proc varchar2(72) := g_package||'send_notification';
2088 l_user_name wf_users.name%TYPE ;
2089 l_nid number;
2090 l_email_ids varchar2(32767) := p_email_addresses;
2091 l_person_ids varchar2(32767) := p_person_ids;
2092 l_cur_email_id fnd_user.email_address%TYPE := null;
2093 l_cur_person_id varchar2(100) := null;
2094
2095 cursor get_user_name_from_email(p_email_address varchar2) is
2096 select user_name
2097 from fnd_user
2098 where upper(email_address)=upper(p_email_address)
2099 and (end_date is null or end_date >=sysdate);
2100
2101 cursor get_user_name_from_person_id(p_person_id varchar2) is
2102 select user_name
2103 from fnd_user
2104 where EMPLOYEE_ID = p_person_id
2105 and (end_date is null or end_date >=sysdate);
2106
2107 BEGIN
2108 hr_utility.set_location('Entering:'||l_proc, 5);
2109 l_email_ids := l_email_ids || ',';
2110 l_person_ids := l_person_ids || ',';
2111 hr_utility.set_location('l_email_ids:'||l_email_ids||' =>'||l_proc, 5);
2112 hr_utility.set_location('l_person_ids:'||l_person_ids||' =>'||l_proc, 5);
2113 LOOP
2114 l_cur_email_id := null;
2115 l_cur_person_id := null;
2116 l_user_name := null;
2117 l_cur_email_id := SUBSTR(l_email_ids,1,INSTR(l_email_ids,',')-1);
2118 l_cur_person_id := SUBSTR(l_person_ids,1,INSTR(l_person_ids,',')-1);
2119
2120 if l_cur_email_id is not null then --{ if the email is not null fetch the role from fnd_users table
2121 open get_user_name_from_email(l_cur_email_id);
2122 fetch get_user_name_from_email into l_user_name;
2123 if get_user_name_from_email%notfound then --{ if the role not found for that email id
2124 close get_user_name_from_email;
2125 if l_cur_person_id is not null then --{ if the person id not null then fetch the role from the fnd_users table
2126 open get_user_name_from_person_id(l_cur_person_id);
2127 fetch get_user_name_from_person_id into l_user_name;
2128 if get_user_name_from_person_id%notfound then --{ if the role is not found for the person id
2129 close get_user_name_from_person_id;
2130 -- Create an ad-hoc user
2131 l_user_name := Create_AdHoc_User(p_email_address => l_cur_email_id );
2132 else
2133 close get_user_name_from_person_id;
2134 end if; --}
2135 else -- if the role not found for the email id and the person id is null
2136 -- Create an ad-hoc user
2137 l_user_name := Create_AdHoc_User(p_email_address => l_cur_email_id );
2138 end if; --}
2139 else
2140 close get_user_name_from_email;
2141 end if; --}
2142 elsif l_cur_person_id is not null then -- if the email id is null and person id not null then fetch the role from the fnd_users table
2143 open get_user_name_from_person_id(l_cur_person_id);
2144 fetch get_user_name_from_person_id into l_user_name;
2145 close get_user_name_from_person_id;
2146 end if; --}
2147 --
2148 hr_utility.set_location('l_user_name:'||l_user_name||' =>'||l_proc, 5);
2149 if l_user_name is not null then --{ if the role is not null then send the notification
2150 l_nid := send_notification
2151 ( p_user_name => l_user_name
2152 , p_subject => p_subject
2153 , p_html_body => p_html_body
2154 , p_text_body => p_text_body
2155 , p_from_role => p_from_role);
2156 end if; --}
2157 l_email_ids := SUBSTR(l_email_ids,INSTR(l_email_ids,',')+1);
2158 l_person_ids := SUBSTR(l_person_ids,INSTR(l_person_ids,',')+1);
2159 exit when l_email_ids is null and l_person_ids is null;
2160 END LOOP;
2161 hr_utility.set_location('Leaving:'||l_proc, 5);
2162 --
2163 END;
2164 -- --------------------------------------------------------------------- *
2165 -- Name : send_notification
2166 -- Purpose: See header
2167 -- --------------------------------------------------------------------- *
2168 FUNCTION send_notification
2169 ( p_user_name IN varchar2
2170 , p_subject IN varchar2
2171 , p_html_body IN varchar2 DEFAULT null
2172 , p_text_body IN varchar2 DEFAULT null
2173 , p_from_role IN varchar2 DEFAULT null
2174 )
2175 RETURN number
2176 IS
2177 l_nid number;
2178 BEGIN
2179 --
2180 if p_html_body is not null
2181 then
2182 l_nid := send_html_text_notification
2183 ( p_user_name => p_user_name
2184 , p_subject => p_subject
2185 , p_html_body => p_html_body
2186 , p_text_body => p_text_body
2187 , p_from_role => p_from_role
2188 );
2189 else
2190 l_nid := send_text_notification
2191 ( p_user_name => p_user_name
2192 , p_subject => p_subject
2193 , p_text_body => p_text_body
2194 , p_from_role => p_from_role
2195 );
2196 end if;
2197 RETURN l_nid;
2198 END send_notification;
2199
2200 -- --------------------------------------------------------------------- *
2201 -- Name : get_doc
2202 -- Purpose: See header
2203 -- --------------------------------------------------------------------- *
2204 procedure get_doc (document_id in varchar2
2205 ,display_type in varchar2
2206 ,document in out nocopy varchar2
2207 ,document_type in out nocopy varchar2) is
2208 begin
2209 document:=document_id;
2210 end get_doc;
2211
2212 end ota_wf;
[Home] [Help]