1 package body OE_RMA_WF as
2 /* $Header: OEXWRMAB.pls 120.0 2005/06/01 00:44:46 appldev noship $ */
3
4 -- PROCEDURE Create_Outbound_Shipment
5 --
6 -- <describe the activity here>
7 --
8 -- IN
9 -- itemtype - type of the current item
10 -- itemkey - key of the current item
11 -- actid - process activity instance id
12 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
13 -- OUT
14 -- result
15 -- - COMPLETE[:<result>]
16 -- activity has completed with the indicated result
17 -- - WAITING
18 -- activity is waiting for additional transitions
19 -- - DEFERED
20 -- execution should be defered to background
21 -- - NOTIFIED[:<notification_id>:<assigned_user>]
22 -- activity has notified an external entity that this
23 -- step must be performed. A call to wf_engine.CompleteActivty
24 -- will signal when this step is complete. Optional
25 -- return of notification ID and assigned user.
26 -- - ERROR[:<error_code>]
27 -- function encountered an error.
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_RMA_WF';
29 procedure Create_Outbound_Shipment(
30 itemtype in varchar2,
31 itemkey in varchar2,
32 actid in number,
33 funcmode in varchar2,
34 resultout in out nocopy varchar2 /* file.sql.39 change */
35 )
36 is
37 l_line_id number;
38 l_workflow_status varchar2(30);
39 l_result_out number; --varchar2(30);
40 l_msg_count NUMBER := 0;
41 l_msg_data VARCHAR2(240);
42 x_return_status VARCHAR2(30);
43 x_msg_count NUMBER;
44 x_msg_data VARCHAR2(240);
45 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
46 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
47 l_control_rec OE_GLOBALS.Control_Rec_Type;
48 l_x_header_rec OE_Order_PUB.Header_Rec_Type;
49 l_x_Header_Adj_rec OE_Order_PUB.Header_Adj_Rec_Type;
50 l_x_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
51 l_x_Header_Scredit_rec OE_Order_PUB.Header_Scredit_Rec_Type;
52 l_x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
53 l_x_line_rec OE_Order_PUB.Line_Rec_Type;
54 l_x_Line_Adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
55 l_x_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
56 l_x_Line_Scredit_rec OE_Order_PUB.Line_Scredit_Rec_Type;
57 l_x_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
58 l_x_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
59 l_x_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
60 l_x_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
61 l_x_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
62 l_x_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
63 l_x_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
64 l_x_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
65 l_x_Action_Request_tbl OE_Order_PUB.Request_Tbl_Type;
66 --serla begin
67 l_x_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
68 l_x_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
69 --serla end
70 --
71 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
72 --
73 begin
74
75 --
76 -- RUN mode - normal process execution
77 --
78 if (funcmode = 'RUN') then
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add( 'ENTERING OE_RMA_WF.CREATE_OUTBOUND_SHIPMENT' , 1 ) ;
81 END IF;
82
83 -- your run code goes here
84
85 -- l_result_out := 'COMPLETE:NULL';
86 l_result_out := 0;
87 l_line_id := to_number(itemkey);
88
89 OE_Line_Util.Query_Rows
90 ( p_line_id => l_line_id
91 , x_line_tbl => l_old_line_tbl
92 );
93 l_line_tbl(1).line_id := FND_API.G_MISS_NUM;
94 l_line_tbl(1).line_type_id := FND_API.G_MISS_NUM;
95 l_line_tbl(1).shipment_number := FND_API.G_MISS_NUM;
96 l_old_line_tbl(1).db_flag := FND_API.G_TRUE;
97 l_line_tbl(1).db_flag := FND_API.G_FALSE;
98
99 -- Set Operation.
100 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
101
102 -- Set control flags.
103
104 l_control_rec.controlled_operation := TRUE;
105 l_control_rec.validate_entity := TRUE;
106 l_control_rec.write_to_DB := TRUE;
107
108 l_control_rec.default_attributes := TRUE;
109 l_control_rec.change_attributes := TRUE;
110 l_control_rec.clear_dependents := TRUE;
111
112 -- Instruct API to retain its caches
113
114 l_control_rec.clear_api_cache := FALSE;
115 l_control_rec.clear_api_requests := FALSE;
116
117 -- Call OE_Order_PVT.Process_order
118
119 OE_Order_PVT.Process_order
120 ( p_api_version_number => 1.0
121 , p_init_msg_list => FND_API.G_TRUE
122 , x_return_status => x_return_status
123 , x_msg_count => x_msg_count
124 , x_msg_data => x_msg_data
125 , p_control_rec => l_control_rec
126 , p_x_line_tbl => l_line_tbl
127 , p_old_line_tbl => l_old_line_tbl
128 , p_x_header_rec => l_x_header_rec
129 , p_x_Header_Adj_tbl => l_x_Header_Adj_tbl
130 , p_x_header_price_att_tbl => l_x_header_price_att_tbl
131 , p_x_Header_Adj_att_tbl => l_x_Header_Adj_att_tbl
132 , p_x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
133 , p_x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
134 --serla begin
135 , p_x_Header_Payment_tbl => l_x_Header_Payment_tbl
136 --serla end
137 , p_x_Line_Adj_tbl => l_x_Line_Adj_tbl
138 , p_x_Line_Price_att_tbl => l_x_Line_Price_att_tbl
139 , p_x_Line_Adj_att_tbl => l_x_Line_Adj_att_tbl
140 , p_x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
141 , p_x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
142 --serla begin
143 , p_x_Line_Payment_tbl => l_x_Line_Payment_tbl
144 --serla end
145 , p_x_Lot_Serial_tbl => l_x_Lot_Serial_tbl
146 , p_x_action_request_tbl => l_x_Action_Request_tbl
147 );
148
149 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
151 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
152 RAISE FND_API.G_EXC_ERROR;
153 END IF;
154
155 -- Load OUT parameters.
156
157 l_x_line_rec := l_line_tbl(1);
158
159 IF l_debug_level > 0 THEN
160 oe_debug_pub.add( 'CALLING CALCULATE TAX' , 2 ) ;
161 END IF;
162 -- Calculate Tax
163
164 -- Commented out the call as the line tax is now calculated as a delayed
165 -- request in process_order API.
166 /*
167 If OE_GLOBALS.G_TAX_FLAG = 'Y' THEN
168 OE_Delayed_Requests_UTIL.Tax_Line (x_return_status
169 , l_x_line_rec
170 , l_x_line_rec
171 );
172 oe_debug_pub.add('Calling Update Row', 2);
173 OE_Line_Util.Update_Row (l_x_line_rec);
174 OE_GLOBALS.G_TAX_FLAG := 'N';
175 END IF;
176 */
177
178 -- example completion
179 -- resultout := l_result_out;
180 IF (x_return_status = FND_API.G_RET_STS_SUCCESS)
181 THEN
182 resultout := 'COMPLETE:PASS';
183 ELSE
184 resultout := 'COMPLETE:FAIL';
185 END IF;
186 return;
187 end if;
188
189
190 --
191 -- CANCEL mode - activity 'compensation'
192 --
193 -- This is an event point is called with the effect of the activity must
194 -- be undone, for example when a process is reset to an earlier point
195 -- due to a loop back.
196 --
197 if (funcmode = 'CANCEL') then
198
199 -- your cancel code goes here
200 null;
201
202 -- no result needed
203 resultout := 'COMPLETE';
204 return;
205 end if;
206
207
208 --
209 -- Other execution modes may be created in the future. Your
210 -- activity will indicate that it does not implement a mode
211 -- by returning null
212 --
213 resultout := '';
214 return;
215 EXCEPTION
216 WHEN OTHERS THEN
217 -- in the case of an exception.
218 wf_core.context('OE_RMA_WF', 'CREATE_OUTBOUND_SHIPMENT',
219 itemtype, itemkey, to_char(actid), funcmode);
220 raise;
221 end Create_Outbound_Shipment;
222
223 procedure Is_Return_Line(
224 itemtype in varchar2,
225 itemkey in varchar2,
226 actid in number,
227 funcmode in varchar2,
228 resultout in out nocopy varchar2 /* file.sql.39 change */
229 )
230 is
231 l_category_code VARCHAR2(30);
232
233 --
234 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
235 --
236 BEGIN
237 IF itemtype = OE_GLOBALS.G_WFI_LIN THEN
238 l_category_code := wf_engine.GetItemAttrText(OE_GLOBALS.G_WFI_LIN,
239 itemkey, 'LINE_CATEGORY');
240 IF l_category_code = 'RETURN' THEN
241 resultout := 'COMPLETE:Y';
242 ELSE
243 resultout := 'COMPLETE:N';
244 END IF;
245 ELSE
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 -- item type is not a line
248 END IF;
249 Exception
250 when others then
251 wf_core.context('OE_RMA_WF', 'Is_Return_Line',
252 itemtype, itemkey, to_char(actid), funcmode);
253 raise;
254 END Is_Return_Line;
255
256 procedure Is_Line_Receivable(
257 itemtype in varchar2,
258 itemkey in varchar2,
259 actid in number,
260 funcmode in varchar2,
261 resultout in out nocopy varchar2 /* file.sql.39 change */
262 )
263 is
264 l_shippable_flag VARCHAR2(1);
265 l_transactable_flag VARCHAR2(1);
266 --
267 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
268 --
269 BEGIN
270
271
272 if (funcmode = 'RUN') then
273
274 -- Check if the item is shippable or transactable
275
276 IF itemtype = OE_GLOBALS.G_WFI_LIN THEN
277
278 SELECT DECODE(l.shippable_flag,NULL,m.shippable_item_flag ,
279 l.shippable_flag),
280 mtl_transactions_enabled_flag
281 INTO l_shippable_flag,l_transactable_flag
282 FROM mtl_system_items m, oe_order_lines_all l
283 WHERE m.inventory_item_id = l.inventory_item_id
284 AND l.line_id = to_number(itemkey)
285 AND m.organization_id = l.ship_from_org_id;
286
287 IF nvl(l_shippable_flag,'N') = 'Y' AND
288 nvl(l_transactable_flag,'N') = 'Y' THEN
289 resultout := 'COMPLETE:Y';
290 ELSE
291 resultout := 'COMPLETE:N';
292 END IF;
293 ELSE
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 -- item type is not a line
296 END IF;
297
298 return;
299
300 END IF; -- End for 'RUN' mode
301
302
303 -- CANCEL mode - activity 'compensation'
304 --
305 -- This is an event point is called with the effect of the activity must
306 -- be undone, for example when a process is reset to an earlier point
307 -- due to a loop back.
308 --
309 if (funcmode = 'CANCEL') then
310 null;
311
312 -- no result needed
313 resultout := 'COMPLETE';
314 return;
315 end if;
316
317 --
318 -- Other execution modes may be created in the future. Your
319 -- activity will indicate that it does not implement a mode
320 -- by returning null
321 --
322 -- resultout := '';
323 -- return;
324
325
326 Exception
327 when others then
328 wf_core.context('OE_RMA_WF', 'Is_Line_Receivable',
329 itemtype, itemkey, to_char(actid), funcmode);
330 raise;
331 END Is_Line_Receivable;
332
333
334 procedure Wait_For_Receiving(
335 itemtype in varchar2,
336 itemkey in varchar2,
337 actid in number,
338 funcmode in varchar2,
339 resultout in out nocopy varchar2 /* file.sql.39 change */
340 )
341 is
342 l_category_code VARCHAR2(30);
343 l_return_status VARCHAR2(30);
344 --
345 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
346 --
347 BEGIN
348
349 IF l_debug_level > 0 THEN
350 oe_debug_pub.add( 'ENTERING OE_RMA_WF.WAIT_FOR_RECEIVING '||ITEMTYPE||'/' ||ITEMKEY , 1 ) ;
351 END IF;
352
353
354 IF (funcmode = 'RUN') then
355
356 IF itemtype = OE_GLOBALS.G_WFI_LIN THEN
357
358 Is_Line_Receivable(itemtype,
359 itemkey,
360 actid,
361 funcmode,
362 resultout);
363
364 IF resultout = 'COMPLETE:Y' THEN
365 resultout := 'NOTIFIED';
366
367 OE_STANDARD_WF.Set_Msg_Context(actid);
368
369 IF l_debug_level > 0 THEN
370 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 1 ) ;
371 END IF;
372
373 IF OE_STANDARD_WF.G_UPGRADE_MODE <> TRUE THEN
374
375 OE_Order_WF_Util.Update_Flow_Status_Code
376 (p_line_id => to_number(itemkey),
377 p_flow_status_code => 'AWAITING_RETURN',
378 x_return_status => l_return_status
379 );
380 IF l_debug_level > 0 THEN
381 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '|| L_RETURN_STATUS , 1 ) ;
382 END IF;
383 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
384 -- start data fix project
385 -- OE_STANDARD_WF.Save_Messages;
386 -- OE_STANDARD_WF.Clear_Msg_Context;
387 -- end data fix project
388 app_exception.raise_exception;
389 END IF;
390 ELSE
391 UPDATE OE_ORDER_LINES_ALL
392 SET flow_status_code = 'AWAITING_RETURN'
393 WHERE line_id = to_number(itemkey);
394
395 END IF;
396
397 OE_STANDARD_WF.Clear_Msg_Context;
398
399 ELSE
400 resultout := 'COMPLETE:NOT_ELIGIBLE';
401 END IF;
402 ELSE
403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
404 -- item type is not a line
405 END IF;
406
407 return;
408
409 END IF; -- End for 'RUN' mode
410
411
412 -- CANCEL mode - activity 'compensation'
413 --
414 -- This is an event point is called with the effect of the activity must
415 -- be undone, for example when a process is reset to an earlier point
416 -- due to a loop back.
417 --
418 if (funcmode = 'CANCEL') then
419 null;
420
421 -- no result needed
422 resultout := 'COMPLETE';
423 return;
424 end if;
425
426 --
427 -- Other execution modes may be created in the future. Your
428 -- activity will indicate that it does not implement a mode
429 -- by returning null
430 --
431 -- resultout := '';
432 -- return;
433
434 IF l_debug_level > 0 THEN
435 oe_debug_pub.add( 'EXITING OE_RMA_WF.WAIT_FOR_RECEIVING ' , 1 ) ;
436 END IF;
437
438 Exception
439 when others then
440 wf_core.context('OE_RMA_WF', 'Wait_For_Receiving',
441 itemtype, itemkey, to_char(actid), funcmode);
442 -- start data fix project
443 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
444 p_itemtype => itemtype,
445 p_itemkey => itemkey);
446 OE_STANDARD_WF.Save_Messages;
447 OE_STANDARD_WF.Clear_Msg_Context;
448 -- end data fix project
449 raise;
450 END Wait_For_Receiving;
451
452
453 end OE_RMA_WF;