1 PACKAGE BODY OE_BULK_WF_UTIL AS
2 /* $Header: OEBUOWFB.pls 120.2.12010000.2 2008/11/18 13:29:01 smusanna ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_WF_UTIL';
5
6 -------------------------------------------------------------------
7 -- LOCAL PROCEDURES/FUNCTIONS
8 -------------------------------------------------------------------
9 FUNCTION Get_Wf_Item_type
10 ( p_line_index In NUMBER,
11 p_Line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
12 ) RETURN VARCHAR2
13 IS
14 --
15 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
16 --
17 BEGIN
18
19 IF l_debug_level > 0 THEN
20 oe_debug_pub.add( 'ITEM_TYPE_CODE IS ' || P_LINE_REC.ITEM_TYPE_CODE(p_line_index) ) ;
21 oe_debug_pub.add( ' Order Quantity UOM :'|| p_line_rec.order_quantity_uom(p_line_index));
22 oe_debug_pub.add( ' ato line id :'|| p_line_rec.ato_line_id(p_line_index));
23 oe_debug_pub.add( ' line id :'|| p_line_rec.line_id(p_line_index));
24 oe_debug_pub.add( ' Top Model Line Id ;'|| p_line_rec.top_model_line_id(p_line_index));
25 END IF;
26
27 -- Code for Returns
28 IF p_line_rec.line_category_code(p_line_index) = 'RETURN' THEN
29 RETURN 'STANDARD';
30 END IF;
31
32 oe_debug_pub.add( ' 1:');
33
34 IF ( p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_STANDARD OR
35 p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_OPTION)
36 AND p_line_rec.ato_line_id(p_line_index) = p_line_rec.line_id(p_line_index)
37 THEN
38 oe_debug_pub.add( ' 3:');
39 RETURN 'ATO_ITEM';
40 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_MODEL AND
41 p_line_rec.line_id(p_line_index) =
42 p_line_rec.ato_line_id(p_line_index)) THEN
43 oe_debug_pub.add( ' 4:');
44 RETURN 'ATO_MODEL';
45 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_CONFIG) THEN
46 oe_debug_pub.add( ' 5:');
47 RETURN 'CONFIGURATION';
48 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_INCLUDED)
49 THEN
50 oe_debug_pub.add( ' 6:');
51 RETURN 'II';
52 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_KIT) THEN
53 oe_debug_pub.add( ' 7:');
54 RETURN 'KIT';
55 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_MODEL AND
56 p_line_rec.line_id(p_line_index) =
57 p_line_rec.top_model_line_id(p_line_index) AND
58 p_line_rec.ato_line_id(p_line_index) IS NULL) THEN
59 oe_debug_pub.add( ' 8:');
60 RETURN 'PTO_MODEL';
61 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_CLASS AND
62 p_line_rec.ato_line_id(p_line_index) IS NULL) THEN
63 oe_debug_pub.add( ' 9:');
64 RETURN 'PTO_CLASS';
65 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_OPTION AND
66 p_line_rec.ato_line_id(p_line_index) IS NULL) THEN
67 oe_debug_pub.add( ' 10:');
68 RETURN 'PTO_OPTION';
69 -- for ato under pto, we want to start ato model flow
70 -- even if the item_type_code is class. For ato under ato
71 -- start standard flow.
72 oe_debug_pub.add( ' 11:');
73 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_CLASS AND
74 p_line_rec.ato_line_id(p_line_index) IS NOT NULL) THEN
75
76 IF p_line_rec.ato_line_id(p_line_index) = p_line_rec.line_id(p_line_index)
77 THEN
78 oe_debug_pub.add( ' 11:');
79 RETURN 'ATO_MODEL';
80 ELSE
81 oe_debug_pub.add( ' 12:');
82 RETURN 'ATO_CLASS'; -- changed from STANDARD for 4572204
83 END IF;
84 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_OPTION AND
85 p_line_rec.ato_line_id(p_line_index) IS NOT NULL) THEN
86 oe_debug_pub.add( ' 13:');
87 RETURN 'ATO_OPTION'; -- changed from STANDARD for 4572204
88 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_STANDARD)
89 THEN
90 oe_debug_pub.add( ' 14:');
91 RETURN 'STANDARD';
92 /* ELSIF OE_OTA_UTIL.Is_OTA_Line(p_line_rec.order_quantity_uom(p_line_index)) THEN
93 oe_debug_pub.add( ' 15:');
94 RETURN 'EDUCATION_ITEM';
95 syed */
96 ELSE
97
98 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_WF_ITEM_TYPE');
99 OE_BULK_MSG_PUB.ADD;
100 RAISE FND_API.G_EXC_ERROR;
101 END IF;
102
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 IF l_debug_level > 0 THEN
107 oe_debug_pub.add( 'OTHERS ERROR , GET_WF_ITEM_TYPE' ) ;
108 END IF;
109 IF l_debug_level > 0 THEN
110 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
111 END IF;
112 OE_BULK_MSG_PUB.Add_Exc_Msg
113 ( G_PKG_NAME
114 , 'Get_WF_Item_Type'
115 );
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 END Get_WF_Item_Type;
118
119 PROCEDURE Create_HdrWorkItem
120 ( p_index IN NUMBER
121 , p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE
122 )
123 IS
124 l_aname wf_engine.nametabtyp;
125 l_aname2 wf_engine.nametabtyp;
126 l_avalue wf_engine.numtabtyp;
127 l_avaluetext wf_engine.texttabtyp;
128 sales_order VARCHAR2(240);
129 --
130 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
131 --
132 BEGIN
133
134 IF l_debug_level > 0 THEN
135 oe_debug_pub.add( 'IN CREATE_HDRWORKITEM' ) ;
136 END IF;
137
138 -- Set Header User Key
139 if p_header_rec.order_category_code(p_index) = 'RETURN' then
140 fnd_message.set_name('ONT', 'OE_WF_RETURN_ORDER');
141 else
142 fnd_message.set_name('ONT', 'OE_WF_SALES_ORDER');
143 end if;
144
145 fnd_message.set_token('ORDER_NUMBER'
146 , to_char(p_header_rec.order_number(p_index)));
147 sales_order := substrb(fnd_message.get, 1, 240);
148
149 -- Create Header Work item
150 WF_ENGINE.CreateProcess(OE_Globals.G_WFI_HDR
151 ,to_char(p_header_rec.header_id(p_index))
152 ,p_header_rec.wf_process_name(p_index)
153 ,sales_order);
154
155
156 -- Set various Header Attributes
157 l_aname(1) := 'USER_ID';
158 l_avalue(1) := FND_GLOBAL.USER_ID;
159 l_aname(2) := 'APPLICATION_ID';
160 l_avalue(2) := FND_GLOBAL.RESP_APPL_ID;
161 l_aname(3) := 'RESPONSIBILITY_ID';
162 l_avalue(3) := FND_GLOBAL.RESP_ID;
163 l_aname(4) := 'ORG_ID';
164 l_avalue(4) := to_number(OE_GLOBALS.G_ORG_ID);
165 l_aname(5) := 'ORDER_NUMBER'; -- Added for bug 6066313
166 l_avalue(5) := p_header_rec.order_number(p_index); -- Added for bug 6066313
167 wf_engine.SetItemAttrNumberArray( OE_GLOBALS.G_WFI_HDR
168 , p_header_rec.header_id(p_index)
169 , l_aname
170 , l_avalue
171 );
172 l_aname2(1) := 'ORDER_CATEGORY';
173 l_avaluetext(1) := p_header_rec.order_category_code(p_index);
174 l_aname2(2) := 'NOTIFICATION_APPROVER';
175 l_avaluetext(2) := OE_BULK_ORDER_PVT.G_NOTIFICATION_APPROVER;
176
177 wf_engine.SetItemAttrTextArray( OE_GLOBALS.G_WFI_HDR
178 , p_header_rec.header_id(p_index)
179 , l_aname2
180 , l_avaluetext
181 );
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 OE_BULK_MSG_PUB.Add_Exc_Msg
186 ( G_PKG_NAME
187 , 'Create_HdrWorkItem'
188 );
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END Create_HdrWorkItem;
191
192 PROCEDURE Create_LineWorkItem
193 ( p_line_index IN NUMBER
194 , p_header_index IN NUMBER
195 , p_line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
196 , p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE
197 )
198 IS
199 l_process_name VARCHAR2(30);
200 l_wf_item_type VARCHAR2(30);
201 l_wf_assigned BOOLEAN;
202 l_order_number NUMBER;
203 l_aname wf_engine.nametabtyp;
204 l_aname2 wf_engine.nametabtyp;
205 l_avalue wf_engine.numtabtyp;
206 l_avaluetext wf_engine.texttabtyp;
207 line VARCHAR2(240);
208 --
209 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
210 --
211 BEGIN
212
213 -- Set Line User Key
214 if p_line_rec.line_category_code(p_line_index) = 'RETURN' THEN
215 fnd_message.set_name('ONT', 'OE_WF_RETURN_LINE');
216 else
217 fnd_message.set_name('ONT', 'OE_WF_LINE');
218 end if;
219
220 fnd_message.set_token('ORDER_NUMBER',
221 to_char(p_header_rec.order_number(p_header_index)));
222 fnd_message.set_token('LINE_NUMBER',
223 to_char(p_line_rec.line_number(p_line_index)));
224 fnd_message.set_token('SHIPMENT_NUMBER',
225 to_char(p_line_rec.shipment_number(p_line_index)));
226 fnd_message.set_token('OPTION_NUMBER',
227 to_char(p_line_rec.option_number(p_line_index)));
228 fnd_message.set_token('SERVICE_NUMBER',
229 to_char(p_line_rec.service_number(p_line_index)));
230
231 line := substrb(fnd_message.get, 1, 240);
232
233 -- Create Line Work item
234 WF_ENGINE.CreateProcess(OE_Globals.G_WFI_LIN
235 ,to_char(p_line_rec.line_id(p_line_index))
236 ,p_line_rec.wf_process_name(p_line_index)
237 ,line);
238
239
240 -- Set various Line Attributes
241 l_aname(1) := 'USER_ID';
242 l_avalue(1) := FND_GLOBAL.USER_ID;
243 l_aname(2) := 'APPLICATION_ID';
244 l_avalue(2) := FND_GLOBAL.RESP_APPL_ID;
245 l_aname(3) := 'RESPONSIBILITY_ID';
246 l_avalue(3) := FND_GLOBAL.RESP_ID;
247 l_aname(4) := 'ORG_ID';
248 l_avalue(4) := to_number(OE_GLOBALS.G_ORG_ID);
249
250 wf_engine.SetItemAttrNumberArray( OE_GLOBALS.G_WFI_LIN
251 , p_line_rec.line_id(p_line_index)
252 , l_aname
253 , l_avalue
254 );
255
256 l_aname2(1) := 'LINE_CATEGORY';
257 l_avaluetext(1) := p_line_rec.line_category_code(p_line_index);
258 l_aname2(2) := 'NOTIFICATION_APPROVER';
259 l_avaluetext(2) := OE_BULK_ORDER_PVT.G_NOTIFICATION_APPROVER;
260
261 wf_engine.SetItemAttrTextArray( OE_GLOBALS.G_WFI_LIN
262 , p_line_rec.line_id(p_line_index)
263 , l_aname2
264 , l_avaluetext
265 );
266
267 WF_ITEM.Set_Item_Parent(OE_Globals.G_WFI_LIN,
268 to_char(p_line_rec.line_id(p_line_index)),
269 OE_GLOBALS.G_WFI_HDR,
270 to_char(p_line_rec.header_id(p_line_index)), '');
271
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 IF l_debug_level > 0 THEN
276 oe_debug_pub.add( 'CREATE_LINEWORKITEM OTHER ERROR' ) ;
277 END IF;
278 IF l_debug_level > 0 THEN
279 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
280 END IF;
281 OE_BULK_MSG_PUB.Add_Exc_Msg
282 ( G_PKG_NAME
283 , 'Create_LineWorkItem'
284 );
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END Create_LineWorkItem;
287
288
289 -------------------------------------------------------------------
290 -- PUBLIC PROCEDURES/FUNCTIONS
291 -------------------------------------------------------------------
292
293 FUNCTION Validate_OT_WF_Assignment
294 (p_order_type_id IN NUMBER
295 ,x_process_name OUT NOCOPY VARCHAR2)
296
297 RETURN BOOLEAN
298 IS
299 l_process_name VARCHAR2(30);
300 CURSOR c_header_process (p_type_id NUMBER) IS
301 SELECT process_name
302 FROM OE_WORKFLOW_ASSIGNMENTS
303 WHERE order_type_id = p_type_id
304 AND line_type_id IS NULL
305 -- 11i10 - only fulfillment orders supported in HVOP so
306 -- select fulfillment flow
307 AND nvl(wf_item_type,'OEOH') = 'OEOH'
308 AND sysdate >= start_date_active
309 AND sysdate <= nvl(end_date_active, sysdate);
310 --
311 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
312 --
313 BEGIN
314
315 -- Check if Order Type exists in Global and has valid assignment.
316 IF G_ORDER_TYPE_WF_ASSIGN_TBL.EXISTS(p_order_type_id) THEN
317
318 x_process_name := G_ORDER_TYPE_WF_ASSIGN_TBL(p_order_type_id).process_name;
319 IF x_process_name IS NOT NULL THEN
320 RETURN TRUE;
321 ELSE -- No assignment exists
322 RETURN FALSE;
323 END IF;
324
325 ELSE -- no entry in cache
326
327 OPEN c_header_process(p_order_type_id);
328 FETCH c_header_process INTO x_process_name;
329 IF c_header_process%NOTFOUND THEN
330 x_process_name := NULL;
331 END IF;
332 CLOSE c_header_process;
333
334 -- Update cache for null and not null values.
335 G_ORDER_TYPE_WF_ASSIGN_TBL(p_order_type_id).process_name := x_process_name;
336
337 IF x_process_name IS NOT NULL THEN
338 RETURN TRUE;
339 ELSE
340 RETURN FALSE;
341 END IF;
342
343 END IF; -- Order Type is in cache
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 IF l_debug_level > 0 THEN
348 oe_debug_pub.add( 'OTHERS ERROR , VALIDATE_OT_WF_ASSIGNMENT' ) ;
349 END IF;
350 IF l_debug_level > 0 THEN
351 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
352 END IF;
353 OE_BULK_MSG_PUB.Add_Exc_Msg
354 ( G_PKG_NAME
355 , 'Validate_OT_WF_Assignment'
356 );
357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
358 END Validate_OT_WF_Assignment;
359
360 -- This Function Validates that a valid WF assignment exists for the Line Type
361 FUNCTION Validate_LT_WF_Assignment
362 ( p_order_type_id IN NUMBER
363 , p_line_index IN NUMBER
364 , p_line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
365 , x_process_name OUT NOCOPY VARCHAR2
366 )
367 RETURN BOOLEAN
368 IS
369 ctr NUMBER := 1;
370 l_cache_exists BOOLEAN := FALSE;
371 l_wf_item_type VARCHAR2(30);
372 CURSOR c_line_process IS
373 SELECT process_name
374 ,item_type_code
375 FROM OE_WORKFLOW_ASSIGNMENTS
376 WHERE order_type_id = p_order_type_id
377 AND line_type_id = p_line_rec.line_type_id(p_line_index)
378 AND nvl(item_type_code,l_wf_item_type) = l_wf_item_type
379 AND sysdate >= start_date_active
380 AND sysdate <= nvl(end_date_active, sysdate);
381 --
382 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
383 --
384 BEGIN
385
386 IF l_debug_level > 0 THEN
387 oe_debug_pub.add( 'Entering Validate_LT_WF_Assignment',1 ) ;
388 END IF;
389
390 -- Bug 2900592, get_wf_item_type returns error if item type is not
391 -- one of the bulk supported item types ('STANDARD','KIT' or 'INCLUDED')
392 -- So handle it as an invalid WF assignment
393 BEGIN
394
395 oe_debug_pub.add( 'ITEM_TYPE_CODE IS ' || P_LINE_REC.ITEM_TYPE_CODE(p_line_index) ) ;
396 oe_debug_pub.add( ' Order Quantity UOM :'|| p_line_rec.order_quantity_uom(p_line_index));
397 oe_debug_pub.add( ' ato line id :'|| p_line_rec.ato_line_id(p_line_index));
398 oe_debug_pub.add( ' line id :'|| p_line_rec.line_id(p_line_index));
399 oe_debug_pub.add( ' Top Model Line Id ;'|| p_line_rec.top_model_line_id(p_line_index));
400
401 -- Check if combination exists in Global and has valid assignment.
402 l_wf_item_type := Get_WF_Item_Type(p_line_index,p_line_rec);
403 if l_debug_level > 0 then
404 oe_debug_pub.add('work flow item type'||l_wf_item_type);
405 end if;
406 EXCEPTION
407 WHEN OTHERS THEN
408 oe_debug_pub.add('Into the exception');
409 RETURN FALSE;
410 END;
411
412 WHILE (ctr <= G_LINE_TYPE_WF_ASSIGN_TBL.COUNT) LOOP
413
414 IF (G_LINE_TYPE_WF_ASSIGN_TBL(ctr).order_type_id = p_order_TYPE_ID)
415 AND (G_LINE_TYPE_WF_ASSIGN_TBL(ctr).line_type_id = p_line_rec.line_type_id(p_line_index))
416 AND (nvl(G_LINE_TYPE_WF_ASSIGN_TBL(ctr).wf_item_type,l_wf_item_type)
417 = l_wf_item_type)
418 THEN
419
420 x_process_name := G_LINE_TYPE_WF_ASSIGN_TBL(ctr).process_name;
421 l_cache_exists := TRUE;
422 EXIT;
423
424 END IF;
425
426 ctr := ctr + 1;
427 END LOOP;
428
429 IF (l_cache_exists) THEN
430
431 IF x_process_name IS NOT NULL THEN
432 oe_debug_pub.add('returning true');
433 RETURN TRUE;
434 ELSE -- no assignment for this combination.
435 oe_debug_pub.add('returning false');
436 RETURN FALSE;
437 END IF;
438
439 ELSE -- no entry in cache
440
441 OPEN c_line_process;
442 FETCH c_line_process INTO x_process_name, l_wf_item_type;
443 IF c_line_process%NOTFOUND THEN
444 x_process_name := NULL;
445 END IF;
446 CLOSE c_line_process;
447
448 -- Update cache
449 oe_debug_pub.add('updating the cache');
450 ctr := G_LINE_TYPE_WF_ASSIGN_TBL.COUNT + 1;
451 G_LINE_TYPE_WF_ASSIGN_TBL(ctr).order_type_id := p_order_type_id;
452 G_LINE_TYPE_WF_ASSIGN_TBL(ctr).line_type_id := p_line_rec.line_type_id(p_line_index);
453 G_LINE_TYPE_WF_ASSIGN_TBL(ctr).process_name := x_process_name;
454 G_LINE_TYPE_WF_ASSIGN_TBL(ctr).wf_item_type := l_wf_item_type;
455
456 IF x_process_name IS NOT NULL THEN
457 RETURN TRUE;
458 ELSE
459 RETURN FALSE;
460 END IF;
461
462 END IF; -- Combination is in cache
463
464 IF l_debug_level > 0 THEN
465 oe_debug_pub.add( 'Exiting Validate_LT_WF_Assignment',1 ) ;
466 END IF;
467
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 IF l_debug_level > 0 THEN
472 oe_debug_pub.add( 'OTHERS ERROR , VALIDATE_LT_WF_ASSIGNMENT' ) ;
473 END IF;
474 IF l_debug_level > 0 THEN
475 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
476 END IF;
477 OE_BULK_MSG_PUB.Add_Exc_Msg
478 ( G_PKG_NAME
479 , 'Validate_LT_WF_Assignment'
480 );
481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
482 END Validate_LT_WF_Assignment;
483
484
485 -----------------------------------------------------------------------
486 -- PROCEDURE Start_Flows
487 --
488 -- This API is called from BULK process order to start workflows for
489 -- all orders or lines processed in a batch.
490 -----------------------------------------------------------------------
491
492 PROCEDURE Start_Flows
493 (p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
494 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
495 ,x_return_status OUT NOCOPY VARCHAR2)
496
497 IS
498 l_msg_text VARCHAR2(2000);
499 l_msg_count NUMBER;
500 l_header_id NUMBER;
501 l_wf_item_type VARCHAR2(30);
502 i NUMBER;
503 j NUMBER := 1;
504 l_header_count NUMBER := p_header_rec.HEADER_ID.COUNT;
505 l_ii_index NUMBER;
506 --
507 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
508 --
509 BEGIN
510
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512
513 -- bug 3549993, turning off saving of messages through WF
514 OE_STANDARD_WF.G_SAVE_MESSAGES := FALSE;
515
516 FOR I IN 1..l_header_count LOOP
517
518 l_header_id := p_header_rec.header_id(i);
519
520 IF nvl(p_header_rec.lock_control(i),0) NOT IN ( -99, -98, -97) THEN
521
522 IF l_debug_level > 0 THEN
523 oe_debug_pub.add( 'START WF , HEADER ID:'||L_HEADER_ID ) ;
524 END IF;
525
526 G_HEADER_INDEX := i;
527
528 -- Bug 3482897
529 -- Set line index to null when header WF item is created.
530 -- WFs for free good lines are started in the pricing call prior
531 -- to this WF start. This is allowed by WF even though parent WF for
532 -- header does not exist.
533 -- If line index is not nulled out, line WFs for these promotional items
534 -- that are progressed in complete book call later think they are
535 -- running in bulk mode and use an incorrect line index set by
536 -- last line in previous order.
537 -- This causes issues like free good lines not being scheduled for
538 -- subsequent orders.
539 G_LINE_INDEX := null;
540
541 OE_BULK_WF_UTIL.Create_HdrWorkItem
542 (p_index => i
543 ,p_header_rec => p_header_rec
544 );
545 /*Before we move to start Header flow, create line flow also -- bug 5261216*/
546 WHILE p_line_rec.HEADER_ID.EXISTS(j)
547 AND p_line_rec.header_id(j) <= l_header_id
548 AND NOT (p_line_rec.item_type_code(j) = 'INCLUDED')
549 LOOP
550 IF p_line_rec.header_id(j) = l_header_id THEN
551
552 G_LINE_INDEX := j;
553
554 OE_BULK_WF_UTIL.Create_LineWorkItem
555 (p_line_index => j
556 ,p_header_index => i
557 ,p_line_rec => p_line_rec
558 ,p_header_rec => p_header_rec
559 );
560
561 -- Start Workflows for included items if this is a kit line
562 IF p_line_rec.item_type_code(j) IN ('KIT', 'MODEL', 'CLASS')
563 AND p_line_rec.ii_start_index(j) IS NOT NULL THEN
564
565 l_ii_index := p_line_rec.ii_start_index(j);
566
567 FOR k IN 1..p_line_rec.ii_count(j) LOOP
568 G_LINE_INDEX := l_ii_index;
569
570 OE_BULK_WF_UTIL.Create_LineWorkItem
571 (p_line_index => l_ii_index
572 ,p_header_index => i
573 ,p_line_rec => p_line_rec
574 ,p_header_rec => p_header_rec
575 );
576
577 l_ii_index := l_ii_index + 1;
578
579 END LOOP;
580
581 END IF;
582
583 END IF; -- End if line header id = l_header_id
584
585 j := j + 1;
586
587 END LOOP; /*Bug 5261216*/
588
589 /*Progress Both Header and Line Flows*/
590 WF_ENGINE.StartProcess(OE_GLOBALS.G_WFI_HDR
591 ,to_char(l_header_id));
592
593 IF p_header_rec.booked_flag(i) = 'Y' THEN
594 -- Call WF_ENGINE to complete the BOOK_ELIGIBLE activity and proceed
595 -- to the next activity in the order workflow
596 WF_ENGINE.CompleteActivityInternalName
597 ( itemtype => 'OEOH'
598 , itemkey => to_char(l_header_id)
599 , activity => 'BOOK_ELIGIBLE'
600 , result => NULL
601 );
602 OE_BULK_ORDER_IMPORT_PVT.G_BOOKED_ORDERS :=
603 OE_BULK_ORDER_IMPORT_PVT.G_BOOKED_ORDERS + 1;
604 ELSE
605 OE_BULK_ORDER_IMPORT_PVT.G_ENTERED_ORDERS :=
606 OE_BULK_ORDER_IMPORT_PVT.G_ENTERED_ORDERS + 1;
607 END IF;
608 j:=1; /*Resetting j Bug 5261216*/
609 WHILE p_line_rec.HEADER_ID.EXISTS(j)
610 -- Bug 2802876
611 -- Changed condition from = to <=
612 -- As there could be lines for erroneous orders in
613 -- the line table which should be skipped until you
614 -- find lines for current order.
615 AND p_line_rec.header_id(j) <= l_header_id
616 AND NOT (p_line_rec.item_type_code(j) = 'INCLUDED')
617 LOOP
618
619 IF l_debug_level > 0 THEN
620 oe_debug_pub.add('Line Index : '||j);
621 oe_debug_pub.add('Line Header ID : '||p_line_rec.header_id(j));
622 END IF;
623
624 -- Start flows only if line header_id matches current
625 -- header_id
626 IF p_line_rec.header_id(j) = l_header_id THEN
627
628 G_LINE_INDEX := j;
629
630 /*OE_BULK_WF_UTIL.Create_LineWorkItem
631 (p_line_index => j
632 ,p_header_index => i
633 ,p_line_rec => p_line_rec
634 ,p_header_rec => p_header_rec
635 );*/ --Line work flow already created Bug 5261216
636
637 -- Start Workflow for this line
638 WF_ENGINE.StartProcess(OE_GLOBALS.G_WFI_LIN
639 ,to_char(p_line_rec.line_id(j)));
640
641 -- Pricing Post-Processing
642 -- Not needed for included items.
643 -- This check is done here and not in OEBVPRCB-Price_Orders procedure
644 -- as pricing API does not loop over lines while wf start does!
645 OE_Bulk_Process_Line.Post_Process
646 (p_line_index => j
647 ,p_header_index => i
648 ,p_line_rec => p_line_rec
649 ,p_header_rec => p_header_rec
650 );
651
652 -- Start Workflows for included items if this is a kit line
653 IF p_line_rec.item_type_code(j) IN ('KIT', 'MODEL', 'CLASS')
654 AND p_line_rec.ii_start_index(j) IS NOT NULL THEN
655
656 l_ii_index := p_line_rec.ii_start_index(j);
657
658 FOR k IN 1..p_line_rec.ii_count(j) LOOP
659
660 -- Bug 2670420, line index was not set for included items.
661 -- Scheduling WF API was checking parent index and mistakenly
662 -- concluding that all included items were scheduled if the
663 -- kit is schedule. This is not true for non-SMC kits.
664 G_LINE_INDEX := l_ii_index;
665
666 /*OE_BULK_WF_UTIL.Create_LineWorkItem
667 (p_line_index => l_ii_index
668 ,p_header_index => i
669 ,p_line_rec => p_line_rec
670 ,p_header_rec => p_header_rec
671 );*/ --Line work flow already created Bug 5261216
672
673 WF_ENGINE.StartProcess(OE_GLOBALS.G_WFI_LIN
674 ,to_char(p_line_rec.line_id(l_ii_index)));
675 l_ii_index := l_ii_index + 1;
676
677 END LOOP;
678
679 END IF;
680
681 END IF; -- End if line header id = l_header_id
682
683 j := j + 1;
684
685 END LOOP; -- End loop for lines
686
687 ELSE
688
689 OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS :=
690 OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS + 1;
691
692 END IF; -- If order does not have errors
693
694 END LOOP; -- End loop for headers
695
696 G_HEADER_INDEX := NULL;
697 G_LINE_INDEX := NULL;
698
699 EXCEPTION
700 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
701 G_HEADER_INDEX := NULL;
702 G_LINE_INDEX := NULL;
703 IF l_debug_level > 0 THEN
704 oe_debug_pub.add( 'UNEXP ERROR , START_FLOWS' ) ;
705 END IF;
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 WHEN OTHERS THEN
708 G_HEADER_INDEX := NULL;
709 G_LINE_INDEX := NULL;
710 IF l_debug_level > 0 THEN
711 oe_debug_pub.add( 'OTHERS ERROR , START_FLOWS' ) ;
712 END IF;
713 IF l_debug_level > 0 THEN
714 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
715 END IF;
716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
717 OE_BULK_MSG_PUB.Add_Exc_Msg
718 ( G_PKG_NAME
719 , 'Start_Flows'
720 );
721 END Start_Flows;
722
723 END OE_BULK_WF_UTIL;
[Home] [Help]