[Home] [Help]
PACKAGE BODY: APPS.OE_BULK_WF_UTIL
Source
1 PACKAGE BODY OE_BULK_WF_UTIL AS
2 /* $Header: OEBUOWFB.pls 120.6.12020000.2 2013/04/19 05:45:14 shaofhua 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 OR
36 p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_INCLUDED) --9775352
37 AND p_line_rec.ato_line_id(p_line_index) = p_line_rec.line_id(p_line_index)
38 THEN
39 oe_debug_pub.add( ' 3:');
40 RETURN 'ATO_ITEM';
41 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_MODEL AND
42 p_line_rec.line_id(p_line_index) =
43 p_line_rec.ato_line_id(p_line_index)) THEN
44 oe_debug_pub.add( ' 4:');
45 RETURN 'ATO_MODEL';
46 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_CONFIG) THEN
47 oe_debug_pub.add( ' 5:');
48 RETURN 'CONFIGURATION';
49 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_INCLUDED)
50 THEN
51 oe_debug_pub.add( ' 6:');
52 RETURN 'II';
53 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_KIT) THEN
54 oe_debug_pub.add( ' 7:');
55 RETURN 'KIT';
56 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_MODEL AND
57 p_line_rec.line_id(p_line_index) =
58 p_line_rec.top_model_line_id(p_line_index) AND
59 p_line_rec.ato_line_id(p_line_index) IS NULL) THEN
60 oe_debug_pub.add( ' 8:');
61 RETURN 'PTO_MODEL';
62 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_CLASS AND
63 p_line_rec.ato_line_id(p_line_index) IS NULL) THEN
64 oe_debug_pub.add( ' 9:');
65 RETURN 'PTO_CLASS';
66 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_OPTION AND
67 p_line_rec.ato_line_id(p_line_index) IS NULL) THEN
68 oe_debug_pub.add( ' 10:');
69 RETURN 'PTO_OPTION';
70 -- for ato under pto, we want to start ato model flow
71 -- even if the item_type_code is class. For ato under ato
72 -- start standard flow.
73 oe_debug_pub.add( ' 11:');
74 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_CLASS AND
75 p_line_rec.ato_line_id(p_line_index) IS NOT NULL) THEN
76
77 IF p_line_rec.ato_line_id(p_line_index) = p_line_rec.line_id(p_line_index)
78 THEN
79 oe_debug_pub.add( ' 11:');
80 RETURN 'ATO_MODEL';
81 ELSE
82 oe_debug_pub.add( ' 12:');
83 RETURN 'ATO_CLASS'; -- changed from STANDARD for 4572204
84 END IF;
85 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_OPTION AND
86 p_line_rec.ato_line_id(p_line_index) IS NOT NULL) THEN
87 oe_debug_pub.add( ' 13:');
88 RETURN 'ATO_OPTION'; -- changed from STANDARD for 4572204
89 ELSIF (p_line_rec.item_type_code(p_line_index) = OE_GLOBALS.G_ITEM_STANDARD)
90 THEN
91 oe_debug_pub.add( ' 14:');
92 RETURN 'STANDARD';
93 /* ELSIF OE_OTA_UTIL.Is_OTA_Line(p_line_rec.order_quantity_uom(p_line_index)) THEN
94 oe_debug_pub.add( ' 15:');
95 RETURN 'EDUCATION_ITEM';
96 syed */
97 ELSE
98
99 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_WF_ITEM_TYPE');
100 OE_BULK_MSG_PUB.ADD;
101 RAISE FND_API.G_EXC_ERROR;
102 END IF;
103
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 IF l_debug_level > 0 THEN
108 oe_debug_pub.add( 'OTHERS ERROR , GET_WF_ITEM_TYPE' ) ;
109 END IF;
110 IF l_debug_level > 0 THEN
111 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
112 END IF;
113 OE_BULK_MSG_PUB.Add_Exc_Msg
114 ( G_PKG_NAME
115 , 'Get_WF_Item_Type'
116 );
117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
118 END Get_WF_Item_Type;
119
120 PROCEDURE Create_HdrWorkItem
121 ( p_index IN NUMBER
122 , p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE
123 )
124 IS
125 l_aname wf_engine.nametabtyp;
126 l_aname2 wf_engine.nametabtyp;
127 l_avalue wf_engine.numtabtyp;
128 l_avaluetext wf_engine.texttabtyp;
129 sales_order VARCHAR2(240);
130 --
131 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
132 --
133 BEGIN
134
135 IF l_debug_level > 0 THEN
136 oe_debug_pub.add( 'IN CREATE_HDRWORKITEM' ) ;
137 END IF;
138
139 -- Set Header User Key
140 if p_header_rec.order_category_code(p_index) = 'RETURN' then
141 fnd_message.set_name('ONT', 'OE_WF_RETURN_ORDER');
142 else
143 fnd_message.set_name('ONT', 'OE_WF_SALES_ORDER');
144 end if;
145
146 fnd_message.set_token('ORDER_NUMBER'
147 , to_char(p_header_rec.order_number(p_index)));
148 sales_order := substrb(fnd_message.get, 1, 240);
149
150 -- Create Header Work item
151 WF_ENGINE.CreateProcess(OE_Globals.G_WFI_HDR
152 ,to_char(p_header_rec.header_id(p_index))
153 ,p_header_rec.wf_process_name(p_index)
154 ,sales_order);
155
156 --Bug 16582023
157 wf_engine.additemattr (oe_globals.g_wfi_hdr,
158 TO_CHAR (p_header_rec.header_id(p_index)),
159 '#WAITFORDETAIL',
160 NULL,
161 0
162 );
163
164 -- Set various Header Attributes
165 l_aname(1) := 'USER_ID';
166 l_avalue(1) := FND_GLOBAL.USER_ID;
167 l_aname(2) := 'APPLICATION_ID';
168 l_avalue(2) := FND_GLOBAL.RESP_APPL_ID;
169 l_aname(3) := 'RESPONSIBILITY_ID';
170 l_avalue(3) := FND_GLOBAL.RESP_ID;
171 l_aname(4) := 'ORG_ID';
172 l_avalue(4) := to_number(OE_GLOBALS.G_ORG_ID);
173 l_aname(5) := 'ORDER_NUMBER'; -- Added for bug 6066313
174 l_avalue(5) := p_header_rec.order_number(p_index); -- Added for bug 6066313
175 wf_engine.SetItemAttrNumberArray( OE_GLOBALS.G_WFI_HDR
176 , p_header_rec.header_id(p_index)
177 , l_aname
178 , l_avalue
179 );
180 l_aname2(1) := 'ORDER_CATEGORY';
181 l_avaluetext(1) := p_header_rec.order_category_code(p_index);
182 l_aname2(2) := 'NOTIFICATION_APPROVER';
183 l_avaluetext(2) := OE_BULK_ORDER_PVT.G_NOTIFICATION_APPROVER;
184
185 wf_engine.SetItemAttrTextArray( OE_GLOBALS.G_WFI_HDR
186 , p_header_rec.header_id(p_index)
187 , l_aname2
188 , l_avaluetext
189 );
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 OE_BULK_MSG_PUB.Add_Exc_Msg
194 ( G_PKG_NAME
195 , 'Create_HdrWorkItem'
196 );
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198 END Create_HdrWorkItem;
199
200 PROCEDURE Create_LineWorkItem
201 ( p_line_index IN NUMBER
202 , p_header_index IN NUMBER
203 , p_line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
204 , p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE
205 )
206 IS
207 l_process_name VARCHAR2(30);
208 l_wf_item_type VARCHAR2(30);
209 l_wf_assigned BOOLEAN;
210 l_order_number NUMBER;
211 l_aname wf_engine.nametabtyp;
212 l_aname2 wf_engine.nametabtyp;
213 l_avalue wf_engine.numtabtyp;
214 l_avaluetext wf_engine.texttabtyp;
215 line VARCHAR2(240);
216 --
217 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
218 --
219 BEGIN
220
221 -- Set Line User Key
222 if p_line_rec.line_category_code(p_line_index) = 'RETURN' THEN
223 fnd_message.set_name('ONT', 'OE_WF_RETURN_LINE');
224 else
225 fnd_message.set_name('ONT', 'OE_WF_LINE');
226 end if;
227
228 fnd_message.set_token('ORDER_NUMBER',
229 to_char(p_header_rec.order_number(p_header_index)));
230 fnd_message.set_token('LINE_NUMBER',
231 to_char(p_line_rec.line_number(p_line_index)));
232 fnd_message.set_token('SHIPMENT_NUMBER',
233 to_char(p_line_rec.shipment_number(p_line_index)));
234 fnd_message.set_token('OPTION_NUMBER',
235 to_char(p_line_rec.option_number(p_line_index)));
236 fnd_message.set_token('SERVICE_NUMBER',
237 to_char(p_line_rec.service_number(p_line_index)));
238
239 line := substrb(fnd_message.get, 1, 240);
240
241 -- Create Line Work item
242 WF_ENGINE.CreateProcess(OE_Globals.G_WFI_LIN
243 ,to_char(p_line_rec.line_id(p_line_index))
244 ,p_line_rec.wf_process_name(p_line_index)
245 ,line);
246
247
248 -- Set various Line Attributes
249 l_aname(1) := 'USER_ID';
250 l_avalue(1) := FND_GLOBAL.USER_ID;
251 l_aname(2) := 'APPLICATION_ID';
252 l_avalue(2) := FND_GLOBAL.RESP_APPL_ID;
253 l_aname(3) := 'RESPONSIBILITY_ID';
254 l_avalue(3) := FND_GLOBAL.RESP_ID;
255 l_aname(4) := 'ORG_ID';
256 l_avalue(4) := to_number(OE_GLOBALS.G_ORG_ID);
257
258 wf_engine.SetItemAttrNumberArray( OE_GLOBALS.G_WFI_LIN
259 , p_line_rec.line_id(p_line_index)
260 , l_aname
261 , l_avalue
262 );
263
264 l_aname2(1) := 'LINE_CATEGORY';
265 l_avaluetext(1) := p_line_rec.line_category_code(p_line_index);
266 l_aname2(2) := 'NOTIFICATION_APPROVER';
267 l_avaluetext(2) := OE_BULK_ORDER_PVT.G_NOTIFICATION_APPROVER;
268
269 wf_engine.SetItemAttrTextArray( OE_GLOBALS.G_WFI_LIN
270 , p_line_rec.line_id(p_line_index)
271 , l_aname2
272 , l_avaluetext
273 );
274
275 WF_ITEM.Set_Item_Parent(OE_Globals.G_WFI_LIN,
276 to_char(p_line_rec.line_id(p_line_index)),
277 OE_GLOBALS.G_WFI_HDR,
278 to_char(p_line_rec.header_id(p_line_index)), '');
279
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 IF l_debug_level > 0 THEN
284 oe_debug_pub.add( 'CREATE_LINEWORKITEM OTHER ERROR' ) ;
285 END IF;
286 IF l_debug_level > 0 THEN
287 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
288 END IF;
289 OE_BULK_MSG_PUB.Add_Exc_Msg
290 ( G_PKG_NAME
291 , 'Create_LineWorkItem'
292 );
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 END Create_LineWorkItem;
295
296
297 -------------------------------------------------------------------
298 -- PUBLIC PROCEDURES/FUNCTIONS
299 -------------------------------------------------------------------
300
301 FUNCTION Validate_OT_WF_Assignment
302 (p_order_type_id IN NUMBER
303 ,x_process_name OUT NOCOPY VARCHAR2)
304
305 RETURN BOOLEAN
306 IS
307 l_process_name VARCHAR2(30);
308 CURSOR c_header_process (p_type_id NUMBER) IS
309 SELECT process_name
310 FROM OE_WORKFLOW_ASSIGNMENTS
311 WHERE order_type_id = p_type_id
312 AND line_type_id IS NULL
313 -- 11i10 - only fulfillment orders supported in HVOP so
314 -- select fulfillment flow
315 AND nvl(wf_item_type,'OEOH') = 'OEOH'
316 AND sysdate >= start_date_active
317 AND sysdate <= nvl(end_date_active, sysdate);
318 --
319 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
320 --
321 BEGIN
322
323 -- Check if Order Type exists in Global and has valid assignment.
324 IF G_ORDER_TYPE_WF_ASSIGN_TBL.EXISTS(p_order_type_id) THEN
325
326 x_process_name := G_ORDER_TYPE_WF_ASSIGN_TBL(p_order_type_id).process_name;
327 IF x_process_name IS NOT NULL THEN
328 RETURN TRUE;
329 ELSE -- No assignment exists
330 RETURN FALSE;
331 END IF;
332
333 ELSE -- no entry in cache
334
335 OPEN c_header_process(p_order_type_id);
336 FETCH c_header_process INTO x_process_name;
337 IF c_header_process%NOTFOUND THEN
338 x_process_name := NULL;
339 END IF;
340 CLOSE c_header_process;
341
342 -- Update cache for null and not null values.
343 G_ORDER_TYPE_WF_ASSIGN_TBL(p_order_type_id).process_name := x_process_name;
344
345 IF x_process_name IS NOT NULL THEN
346 RETURN TRUE;
347 ELSE
348 RETURN FALSE;
349 END IF;
350
351 END IF; -- Order Type is in cache
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 IF l_debug_level > 0 THEN
356 oe_debug_pub.add( 'OTHERS ERROR , VALIDATE_OT_WF_ASSIGNMENT' ) ;
357 END IF;
358 IF l_debug_level > 0 THEN
359 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
360 END IF;
361 OE_BULK_MSG_PUB.Add_Exc_Msg
362 ( G_PKG_NAME
363 , 'Validate_OT_WF_Assignment'
364 );
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END Validate_OT_WF_Assignment;
367
368 -- This Function Validates that a valid WF assignment exists for the Line Type
369 FUNCTION Validate_LT_WF_Assignment
370 ( p_order_type_id IN NUMBER
371 , p_line_index IN NUMBER
372 , p_line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
373 , x_process_name OUT NOCOPY VARCHAR2
374 )
375 RETURN BOOLEAN
376 IS
377 ctr NUMBER := 1;
378 l_cache_exists BOOLEAN := FALSE;
379 l_wf_item_type VARCHAR2(30);
380 CURSOR c_line_process IS
381 SELECT process_name
382 ,item_type_code
383 FROM OE_WORKFLOW_ASSIGNMENTS
384 WHERE order_type_id = p_order_type_id
385 AND line_type_id = p_line_rec.line_type_id(p_line_index)
386 AND nvl(item_type_code,l_wf_item_type) = l_wf_item_type
387 AND sysdate >= start_date_active
388 AND sysdate <= nvl(end_date_active, sysdate);
389 --
390 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
391 --
392 BEGIN
393
394 IF l_debug_level > 0 THEN
395 oe_debug_pub.add( 'Entering Validate_LT_WF_Assignment',1 ) ;
396 END IF;
397
398 -- Bug 2900592, get_wf_item_type returns error if item type is not
399 -- one of the bulk supported item types ('STANDARD','KIT' or 'INCLUDED')
400 -- So handle it as an invalid WF assignment
401 BEGIN
402
403 oe_debug_pub.add( 'ITEM_TYPE_CODE IS ' || P_LINE_REC.ITEM_TYPE_CODE(p_line_index) ) ;
404 oe_debug_pub.add( ' Order Quantity UOM :'|| p_line_rec.order_quantity_uom(p_line_index));
405 oe_debug_pub.add( ' ato line id :'|| p_line_rec.ato_line_id(p_line_index));
406 oe_debug_pub.add( ' line id :'|| p_line_rec.line_id(p_line_index));
407 oe_debug_pub.add( ' Top Model Line Id ;'|| p_line_rec.top_model_line_id(p_line_index));
408
409 -- Check if combination exists in Global and has valid assignment.
410 l_wf_item_type := Get_WF_Item_Type(p_line_index,p_line_rec);
411 if l_debug_level > 0 then
412 oe_debug_pub.add('work flow item type'||l_wf_item_type);
413 end if;
414 EXCEPTION
415 WHEN OTHERS THEN
416 oe_debug_pub.add('Into the exception');
417 RETURN FALSE;
418 END;
419
420 WHILE (ctr <= G_LINE_TYPE_WF_ASSIGN_TBL.COUNT) LOOP
421
422 IF (G_LINE_TYPE_WF_ASSIGN_TBL(ctr).order_type_id = p_order_TYPE_ID)
423 AND (G_LINE_TYPE_WF_ASSIGN_TBL(ctr).line_type_id = p_line_rec.line_type_id(p_line_index))
424 AND (nvl(G_LINE_TYPE_WF_ASSIGN_TBL(ctr).wf_item_type,l_wf_item_type)
425 = l_wf_item_type)
426 THEN
427
428 x_process_name := G_LINE_TYPE_WF_ASSIGN_TBL(ctr).process_name;
429 l_cache_exists := TRUE;
430 EXIT;
431
432 END IF;
433
434 ctr := ctr + 1;
435 END LOOP;
436
437 IF (l_cache_exists) THEN
438
439 IF x_process_name IS NOT NULL THEN
440 oe_debug_pub.add('returning true');
441 RETURN TRUE;
442 ELSE -- no assignment for this combination.
443 oe_debug_pub.add('returning false');
444 RETURN FALSE;
445 END IF;
446
447 ELSE -- no entry in cache
448
449 OPEN c_line_process;
450 FETCH c_line_process INTO x_process_name, l_wf_item_type;
451 IF c_line_process%NOTFOUND THEN
452 x_process_name := NULL;
453 END IF;
454 CLOSE c_line_process;
455
456 -- Update cache
457 oe_debug_pub.add('updating the cache');
458 ctr := G_LINE_TYPE_WF_ASSIGN_TBL.COUNT + 1;
459 G_LINE_TYPE_WF_ASSIGN_TBL(ctr).order_type_id := p_order_type_id;
460 G_LINE_TYPE_WF_ASSIGN_TBL(ctr).line_type_id := p_line_rec.line_type_id(p_line_index);
461 G_LINE_TYPE_WF_ASSIGN_TBL(ctr).process_name := x_process_name;
462 G_LINE_TYPE_WF_ASSIGN_TBL(ctr).wf_item_type := l_wf_item_type;
463
464 IF x_process_name IS NOT NULL THEN
465 RETURN TRUE;
466 ELSE
467 RETURN FALSE;
468 END IF;
469
470 END IF; -- Combination is in cache
471
472 IF l_debug_level > 0 THEN
473 oe_debug_pub.add( 'Exiting Validate_LT_WF_Assignment',1 ) ;
474 END IF;
475
476
477 EXCEPTION
478 WHEN OTHERS THEN
479 IF l_debug_level > 0 THEN
480 oe_debug_pub.add( 'OTHERS ERROR , VALIDATE_LT_WF_ASSIGNMENT' ) ;
481 END IF;
482 IF l_debug_level > 0 THEN
483 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
484 END IF;
485 OE_BULK_MSG_PUB.Add_Exc_Msg
486 ( G_PKG_NAME
487 , 'Validate_LT_WF_Assignment'
488 );
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END Validate_LT_WF_Assignment;
491
492
493 -----------------------------------------------------------------------
494 -- PROCEDURE Start_Flows
495 --
496 -- This API is called from BULK process order to start workflows for
497 -- all orders or lines processed in a batch.
498 -----------------------------------------------------------------------
499
500 PROCEDURE Start_Flows
501 (p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
502 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
503 ,x_return_status OUT NOCOPY VARCHAR2)
504
505 IS
506 l_msg_text VARCHAR2(2000);
507 l_msg_count NUMBER;
508 l_header_id NUMBER;
509 l_wf_item_type VARCHAR2(30);
510 i NUMBER;
511 j NUMBER := 1;
512 l_header_count NUMBER := p_header_rec.HEADER_ID.COUNT;
513 l_ii_index NUMBER;
514 --
515 /* Start of WF Bulk API ER #8601238 */
516 type process_name_bulk is table of varchar2(120) index by varchar2(120);
517 type item_keys_bulk is table of varchar2(240) index by varchar2(80);
518 type user_keys_bulk is table of varchar2(240) index by varchar2(80);
519
520 l_process_name_tbl process_name_bulk;
521 l_item_keys item_keys_bulk;
522 l_user_keys user_keys_bulk;
523
524 l_owner_role VARCHAR2(320);
525 l_line_to_key VARCHAR2(40);
526
527 l_my_index_ind VARCHAR2(120);
528 l_process_name_ind VARCHAR2(120);
529 l_wf_bulk_api_index NUMBER := 0;
530 l_index_Attr_Txt NUMBER := 0;
531 l_index_Attr_Num NUMBER := 0;
532
533 l_aname wf_engine.nametabtyp;
534 l_aname2 wf_engine.nametabtyp;
535 l_avalue wf_engine.numtabtyp;
536 l_avaluetext wf_engine.texttabtyp;
537 l_itemkeys_4NAttr wf_engine_bulk.itemkeytabtype;
538 l_itemkeys_4TAttr wf_engine_bulk.itemkeytabtype;
539
540 WF_Api_l_item_keys wf_engine_bulk.itemkeytabtype;
541 WF_Api_l_user_keys wf_engine_bulk.userkeytabtype;
542 WF_Api_l_owner_roles wf_engine_bulk.ownerroletabtype;
543 /* End of WF Bulk API ER #8601238 */
544 --
545 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
546 --
547 BEGIN
548
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 -- bug 3549993, turning off saving of messages through WF
552 OE_STANDARD_WF.G_SAVE_MESSAGES := FALSE;
553
554 /* Start of WF Bulk API ER #8601238 */
555 SELECT user_name
556 INTO l_owner_role
557 FROM FND_USER
558 WHERE USER_ID = FND_GLOBAL.USER_ID;
559 /* End of WF Bulk API ER #8601238 */
560
561 FOR I IN 1..l_header_count LOOP
562
563 l_header_id := p_header_rec.header_id(i);
564
565 IF nvl(p_header_rec.lock_control(i),0) NOT IN ( -99, -98, -97) THEN
566
567 IF l_debug_level > 0 THEN
568 oe_debug_pub.add( 'START WF , HEADER ID:'||L_HEADER_ID ) ;
569 END IF;
570
571 G_HEADER_INDEX := i;
572
573 -- Bug 3482897
574 -- Set line index to null when header WF item is created.
575 -- WFs for free good lines are started in the pricing call prior
576 -- to this WF start. This is allowed by WF even though parent WF for
577 -- header does not exist.
578 -- If line index is not nulled out, line WFs for these promotional items
579 -- that are progressed in complete book call later think they are
580 -- running in bulk mode and use an incorrect line index set by
581 -- last line in previous order.
582 -- This causes issues like free good lines not being scheduled for
583 -- subsequent orders.
584 G_LINE_INDEX := null;
585
586 OE_BULK_WF_UTIL.Create_HdrWorkItem
587 (p_index => i
588 ,p_header_rec => p_header_rec
589 );
590 /*Before we move to start Header flow, create line flow also -- bug 5261216*/
591 WHILE p_line_rec.HEADER_ID.EXISTS(j)
592 AND p_line_rec.header_id(j) <= l_header_id
593 AND NOT (p_line_rec.item_type_code(j) = 'INCLUDED') /* Uncommented for #9302459 */
594 LOOP
595 IF p_line_rec.header_id(j) = l_header_id THEN
596
597 G_LINE_INDEX := j;
598
599 /* Start of WF Bulk API ER #8601238 */
600 /* OE_BULK_WF_UTIL.Create_LineWorkItem
601 (p_line_index => j
602 ,p_header_index => i
603 ,p_line_rec => p_line_rec
604 ,p_header_rec => p_header_rec
605 ); */ -- Commented for WF Bulk API ER #8601238
606
607 l_line_to_key := to_char(p_line_rec.line_id(j));
608
609 IF NOT (l_process_name_tbl.EXISTS(p_line_rec.wf_process_name(j))) THEN
610 l_process_name_tbl(p_line_rec.wf_process_name(j)) := p_line_rec.wf_process_name(j);
611 END IF;
612 l_item_keys(p_line_rec.wf_process_name(j)||':'||l_line_to_key) := l_line_to_key;
613
614 -- Setting Line User Key
615 if p_line_rec.line_category_code(j) = 'RETURN' THEN
616 fnd_message.set_name('ONT', 'OE_WF_RETURN_LINE');
617 else
618 fnd_message.set_name('ONT', 'OE_WF_LINE');
619 end if;
620 fnd_message.set_token('ORDER_NUMBER', to_char(p_header_rec.order_number(i)));
621 fnd_message.set_token('LINE_NUMBER', to_char(p_line_rec.line_number(j)));
622 fnd_message.set_token('SHIPMENT_NUMBER', to_char(p_line_rec.shipment_number(j)));
623 fnd_message.set_token('OPTION_NUMBER', to_char(p_line_rec.option_number(j)));
624 fnd_message.set_token('SERVICE_NUMBER', to_char(p_line_rec.service_number(j)));
625 l_user_keys(p_line_rec.wf_process_name(j)||':'||l_line_to_key) := substrb(fnd_message.get, 1, 240);
626
627 l_index_Attr_Num := l_index_Attr_Num + 1;
628 l_itemkeys_4NAttr(l_index_Attr_Num) := to_char(p_line_rec.line_id(j));
629 l_aname(l_index_Attr_Num) := 'USER_ID';
630 l_avalue(l_index_Attr_Num) := FND_GLOBAL.USER_ID;
631
632 l_index_Attr_Num := l_index_Attr_Num + 1;
633 l_itemkeys_4NAttr(l_index_Attr_Num) := to_char(p_line_rec.line_id(j));
634 l_aname(l_index_Attr_Num) := 'APPLICATION_ID';
635 l_avalue(l_index_Attr_Num) := FND_GLOBAL.RESP_APPL_ID;
636
637 l_index_Attr_Num := l_index_Attr_Num + 1;
638 l_itemkeys_4NAttr(l_index_Attr_Num) := to_char(p_line_rec.line_id(j));
639 l_aname(l_index_Attr_Num) := 'RESPONSIBILITY_ID';
640 l_avalue(l_index_Attr_Num) := FND_GLOBAL.RESP_ID;
641
642 l_index_Attr_Num := l_index_Attr_Num + 1;
643 l_itemkeys_4NAttr(l_index_Attr_Num) := to_char(p_line_rec.line_id(j));
644 l_aname(l_index_Attr_Num) := 'ORG_ID';
645 l_avalue(l_index_Attr_Num) := to_number(OE_GLOBALS.G_ORG_ID);
646
647 -- Set various Line Attributes of Text datatype
648 l_index_Attr_Txt := l_index_Attr_Txt + 1;
649 l_itemkeys_4TAttr(l_index_Attr_Txt) := to_char(p_line_rec.line_id(j));
650 l_aname2(l_index_Attr_Txt) := 'LINE_CATEGORY';
651 l_avaluetext(l_index_Attr_Txt) := p_line_rec.line_category_code(j);
652
653 l_index_Attr_Txt := l_index_Attr_Txt + 1;
654 l_itemkeys_4TAttr(l_index_Attr_Txt) := to_char(p_line_rec.line_id(j));
655 l_aname2(l_index_Attr_Txt) := 'NOTIFICATION_APPROVER';
656 l_avaluetext(l_index_Attr_Txt) := OE_BULK_ORDER_PVT.G_NOTIFICATION_APPROVER;
657
658 -- Uncommented for #9302459
659 -- Start Workflows for included items if this is a kit line
660 IF p_line_rec.item_type_code(j) IN ('KIT', 'MODEL', 'CLASS')
661 AND p_line_rec.ii_start_index(j) IS NOT NULL THEN
662
663 l_ii_index := p_line_rec.ii_start_index(j);
664
665 FOR k IN 1..p_line_rec.ii_count(j) LOOP
666 G_LINE_INDEX := l_ii_index;
667
668 /* OE_BULK_WF_UTIL.Create_LineWorkItem
669 (p_line_index => l_ii_index
670 ,p_header_index => i
671 ,p_line_rec => p_line_rec
672 ,p_header_rec => p_header_rec
673 );
674 */ -- Commented for #9302459
675
676 l_line_to_key := to_char(p_line_rec.line_id(l_ii_index));
677
678 IF NOT (l_process_name_tbl.EXISTS(p_line_rec.wf_process_name(l_ii_index))) THEN
679 l_process_name_tbl(p_line_rec.wf_process_name(l_ii_index)) := p_line_rec.wf_process_name(l_ii_index);
680 END IF;
681 l_item_keys(p_line_rec.wf_process_name(l_ii_index)||':'||l_line_to_key) := l_line_to_key;
682
683 -- Setting Line User Key
684 if p_line_rec.line_category_code(j) = 'RETURN' THEN
685 fnd_message.set_name('ONT', 'OE_WF_RETURN_LINE');
686 else
687 fnd_message.set_name('ONT', 'OE_WF_LINE');
688 end if;
689 fnd_message.set_token('ORDER_NUMBER', to_char(p_header_rec.order_number(i)));
690 fnd_message.set_token('LINE_NUMBER', to_char(p_line_rec.line_number(l_ii_index)));
691 fnd_message.set_token('SHIPMENT_NUMBER', to_char(p_line_rec.shipment_number(l_ii_index)));
692 fnd_message.set_token('OPTION_NUMBER', to_char(p_line_rec.option_number(l_ii_index)));
693 fnd_message.set_token('SERVICE_NUMBER', to_char(p_line_rec.service_number(l_ii_index)));
694 l_user_keys(p_line_rec.wf_process_name(l_ii_index)||':'||l_line_to_key) := substrb(fnd_message.get, 1, 240);
695
696 l_index_Attr_Num := l_index_Attr_Num + 1;
697 l_itemkeys_4NAttr(l_index_Attr_Num) := to_char(p_line_rec.line_id(l_ii_index));
698 l_aname(l_index_Attr_Num) := 'USER_ID';
699 l_avalue(l_index_Attr_Num) := FND_GLOBAL.USER_ID;
700
701 l_index_Attr_Num := l_index_Attr_Num + 1;
702 l_itemkeys_4NAttr(l_index_Attr_Num) := to_char(p_line_rec.line_id(l_ii_index));
703 l_aname(l_index_Attr_Num) := 'APPLICATION_ID';
704 l_avalue(l_index_Attr_Num) := FND_GLOBAL.RESP_APPL_ID;
705
706 l_index_Attr_Num := l_index_Attr_Num + 1;
707 l_itemkeys_4NAttr(l_index_Attr_Num) := to_char(p_line_rec.line_id(l_ii_index));
708 l_aname(l_index_Attr_Num) := 'RESPONSIBILITY_ID';
709 l_avalue(l_index_Attr_Num) := FND_GLOBAL.RESP_ID;
710
711 l_index_Attr_Num := l_index_Attr_Num + 1;
712 l_itemkeys_4NAttr(l_index_Attr_Num) := to_char(p_line_rec.line_id(l_ii_index));
713 l_aname(l_index_Attr_Num) := 'ORG_ID';
714 l_avalue(l_index_Attr_Num) := to_number(OE_GLOBALS.G_ORG_ID);
715
716 -- Set various Line Attributes of Text datatype
717 l_index_Attr_Txt := l_index_Attr_Txt + 1;
718 l_itemkeys_4TAttr(l_index_Attr_Txt) := to_char(p_line_rec.line_id(l_ii_index));
719 l_aname2(l_index_Attr_Txt) := 'LINE_CATEGORY';
720 l_avaluetext(l_index_Attr_Txt) := p_line_rec.line_category_code(l_ii_index);
721
722 l_index_Attr_Txt := l_index_Attr_Txt + 1;
723 l_itemkeys_4TAttr(l_index_Attr_Txt) := to_char(p_line_rec.line_id(l_ii_index));
724 l_aname2(l_index_Attr_Txt) := 'NOTIFICATION_APPROVER';
725 l_avaluetext(l_index_Attr_Txt) := OE_BULK_ORDER_PVT.G_NOTIFICATION_APPROVER;
726
727 l_ii_index := l_ii_index + 1;
728
729 END LOOP;
730
731 END IF; -- Commented for WF Bulk API ER #8601238
732
733 /* End of WF Bulk API ER #8601238 */
734
735 END IF; -- End if line header id = l_header_id
736
737 j := j + 1;
738
739 END LOOP; /*Bug 5261216*/
740
741 /* Start of WF Bulk API ER #8601238 */
742
743 IF l_process_name_tbl.COUNT > 0 THEN
744 l_process_name_ind := l_process_name_tbl.FIRST;
745 FOR x in 1 .. l_process_name_tbl.COUNT LOOP
746 l_wf_bulk_api_index := 0;
747 l_my_index_ind := l_item_keys.FIRST;
748 FOR xx in 1 .. l_item_keys.COUNT LOOP
749 IF substr(l_my_index_ind,1,(INSTR(l_my_index_ind,':')-1)) = l_process_name_ind THEN
750 l_wf_bulk_api_index := l_wf_bulk_api_index + 1;
751 WF_Api_l_item_keys(l_wf_bulk_api_index) := l_item_keys(l_my_index_ind);
752 WF_Api_l_user_keys(l_wf_bulk_api_index) := l_user_keys(l_my_index_ind);
753 WF_Api_l_owner_roles(l_wf_bulk_api_index) := l_owner_role;
754 END IF;
755 l_my_index_ind := l_item_keys.NEXT(l_my_index_ind);
756 END LOOP;
757
758 -- Calling WF Bulk APIs for Creating the Workflow Process Definition
759 WF_ENGINE_BULK.CreateProcess
760 ( itemtype => OE_GLOBALS.G_WFI_LIN
761 , itemkeys => WF_Api_l_item_keys
762 , process => l_process_name_tbl(l_process_name_ind)
763 , user_keys => WF_Api_l_user_keys
764 , owner_roles => WF_Api_l_owner_roles
765 , parent_itemtype => OE_GLOBALS.G_WFI_HDR
766 , parent_itemkey => l_header_id
767 , masterdetail => TRUE
768 );
769
770 WF_Api_l_item_keys.DELETE;
771 WF_Api_l_user_keys.DELETE;
772 WF_Api_l_owner_roles.DELETE;
773 l_wf_bulk_api_index := 0;
774 l_process_name_ind := l_process_name_tbl.NEXT(l_process_name_ind);
775 END LOOP;
776 END IF;
777
778 -- Calling WF Bulk APIs for setting Item attributes of Number and Text type
779 WF_ENGINE_BULK.SetItemAttrText
780 ( itemtype => OE_GLOBALS.G_WFI_LIN
781 , itemkeys => l_itemkeys_4TAttr
782 , anames => l_aname2
783 , avalues => l_avaluetext
784 );
785
786 WF_ENGINE_BULK.SetItemAttrNumber
787 ( itemtype => OE_GLOBALS.G_WFI_LIN
788 , itemkeys => l_itemkeys_4NAttr
789 , anames => l_aname
790 , avalues => l_avalue
791 );
792
793 l_process_name_tbl.DELETE;
794 l_item_keys.DELETE;
795 l_user_keys.DELETE;
796 l_itemkeys_4TAttr.DELETE;
797 l_aname2.DELETE;
798 l_avaluetext.DELETE;
799 l_itemkeys_4NAttr.DELETE;
800 l_aname.DELETE;
801 l_avalue.DELETE;
802 -- Added above X.DELETE for bug 9302459
803
804 /* End of WF Bulk API ER #8601238 */
805
806 /*Progress Both Header and Line Flows*/
807 WF_ENGINE.StartProcess(OE_GLOBALS.G_WFI_HDR
808 ,to_char(l_header_id));
809
810 IF p_header_rec.booked_flag(i) = 'Y' THEN
811 -- Call WF_ENGINE to complete the BOOK_ELIGIBLE activity and proceed
812 -- to the next activity in the order workflow
813 WF_ENGINE.CompleteActivityInternalName
814 ( itemtype => 'OEOH'
815 , itemkey => to_char(l_header_id)
816 , activity => 'BOOK_ELIGIBLE'
817 , result => NULL
818 );
819 OE_BULK_ORDER_IMPORT_PVT.G_BOOKED_ORDERS :=
820 OE_BULK_ORDER_IMPORT_PVT.G_BOOKED_ORDERS + 1;
821 ELSE
822 OE_BULK_ORDER_IMPORT_PVT.G_ENTERED_ORDERS :=
823 OE_BULK_ORDER_IMPORT_PVT.G_ENTERED_ORDERS + 1;
824 END IF;
825 j:=1; /*Resetting j Bug 5261216*/
826 WHILE p_line_rec.HEADER_ID.EXISTS(j)
827 -- Bug 2802876
828 -- Changed condition from = to <=
829 -- As there could be lines for erroneous orders in
830 -- the line table which should be skipped until you
831 -- find lines for current order.
832 AND p_line_rec.header_id(j) <= l_header_id
833 AND NOT (p_line_rec.item_type_code(j) = 'INCLUDED')
834 LOOP
835
836 IF l_debug_level > 0 THEN
837 oe_debug_pub.add('Line Index : '||j);
838 oe_debug_pub.add('Line Header ID : '||p_line_rec.header_id(j));
839 END IF;
840
841 -- Start flows only if line header_id matches current
842 -- header_id
843 IF p_line_rec.header_id(j) = l_header_id THEN
844
845 G_LINE_INDEX := j;
846
847 /*OE_BULK_WF_UTIL.Create_LineWorkItem
848 (p_line_index => j
849 ,p_header_index => i
850 ,p_line_rec => p_line_rec
851 ,p_header_rec => p_header_rec
852 );*/ --Line work flow already created Bug 5261216
853
854 -- Start Workflow for this line
855 WF_ENGINE.StartProcess(OE_GLOBALS.G_WFI_LIN
856 ,to_char(p_line_rec.line_id(j)));
857
858 -- Pricing Post-Processing
859 -- Not needed for included items.
860 -- This check is done here and not in OEBVPRCB-Price_Orders procedure
861 -- as pricing API does not loop over lines while wf start does!
862
863 OE_Bulk_Process_Line.Post_Process
864 (p_line_index => j
865 ,p_header_index => i
866 ,p_line_rec => p_line_rec
867 ,p_header_rec => p_header_rec
868 );
869
870 -- Start Workflows for included items if this is a kit line
871 IF p_line_rec.item_type_code(j) IN ('KIT', 'MODEL', 'CLASS')
872 AND p_line_rec.ii_start_index(j) IS NOT NULL THEN
873
874 l_ii_index := p_line_rec.ii_start_index(j);
875
876 FOR k IN 1..p_line_rec.ii_count(j) LOOP
877
878 -- Bug 2670420, line index was not set for included items.
879 -- Scheduling WF API was checking parent index and mistakenly
880 -- concluding that all included items were scheduled if the
881 -- kit is schedule. This is not true for non-SMC kits.
882 G_LINE_INDEX := l_ii_index;
883
884 /* OE_BULK_WF_UTIL.Create_LineWorkItem
885 (p_line_index => l_ii_index
886 ,p_header_index => i
887 ,p_line_rec => p_line_rec
888 ,p_header_rec => p_header_rec
889 );*/ --Line work flow already created Bug 5261216
890
891 WF_ENGINE.StartProcess(OE_GLOBALS.G_WFI_LIN
892 ,to_char(p_line_rec.line_id(l_ii_index)));
893 l_ii_index := l_ii_index + 1;
894
895 END LOOP;
896
897 END IF;
898
899 END IF; -- End if line header id = l_header_id
900
901 j := j + 1;
902
903 END LOOP; -- End loop for lines
904
905 ELSE
906
907 OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS :=
908 OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS + 1;
909
910 END IF; -- If order does not have errors
911
912 END LOOP; -- End loop for headers
913
914 G_HEADER_INDEX := NULL;
915 G_LINE_INDEX := NULL;
916
917 EXCEPTION
918 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
919 G_HEADER_INDEX := NULL;
920 G_LINE_INDEX := NULL;
921 IF l_debug_level > 0 THEN
922 oe_debug_pub.add( 'UNEXP ERROR , START_FLOWS' ) ;
923 END IF;
924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
925 WHEN OTHERS THEN
926 G_HEADER_INDEX := NULL;
927 G_LINE_INDEX := NULL;
928 IF l_debug_level > 0 THEN
929 oe_debug_pub.add( 'OTHERS ERROR , START_FLOWS' ) ;
930 END IF;
931 IF l_debug_level > 0 THEN
932 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
933 END IF;
934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
935 OE_BULK_MSG_PUB.Add_Exc_Msg
936 ( G_PKG_NAME
937 , 'Start_Flows'
938 );
939 END Start_Flows;
940
941 END OE_BULK_WF_UTIL;