[Home] [Help]
PACKAGE BODY: APPS.OE_BLANKET_WF_UTIL
Source
1 PACKAGE BODY OE_Blanket_wf_util AS
2 /* $Header: OEXUBWFB.pls 120.1.12010000.2 2009/08/21 08:28:23 nitagarw ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Blanket_wf_Util';
4
5 PROCEDURE create_and_start_flow ( p_header_id IN NUMBER,
6 p_transaction_phase_code IN VARCHAR2,
7 p_blanket_number IN NUMBER,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2)
11 IS
12
13 l_header_rec OE_Blanket_Pub.header_rec_type;
14 l_control_rec OE_Blanket_Pub.control_rec_type;
15 x_header_rec OE_Blanket_Pub.header_rec_type;
16 l_header_id NUMBER;
17 l_count NUMBER;
18 l_item_type varchar2(20);
19 l_sales_doc_type_code varchar2(10) := 'B';
20 l_flow_status_code varchar2(30);
21 --
22 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
23 --
24 BEGIN
25
26 if l_debug_level > 0 then
27 oe_debug_pub.ADD('Entering create_and_start_flow ', 1);
28 end if;
29
30 /* OE_MSG_PUB.set_msg_context(
31 p_entity_code => 'BLANKET_HEADER_WF'
32 ,p_entity_id => p_header_id
33 ,p_header_id => p_header_id
34 ,p_line_id => null
35 ,p_orig_sys_document_ref => null
36 ,p_orig_sys_document_line_ref => null
37 ,p_change_sequence => null
38 ,p_source_document_id => null
39 ,p_source_document_line_id => null
40 ,p_order_source_id => null
41 ,p_source_document_type_id => null); */
42
43 -- if x_return_status = FND_API.G_RET_STS_SUCCESS THEN
44
45 if l_debug_level > 0 then
46 oe_debug_pub.ADD('Entering create_and_start_flow '||x_return_status,2 );
47 end if;
48 IF p_transaction_phase_code = 'F' then
49 l_item_type := 'OEBH';
50 ELSE
51 l_item_type := 'OENH';
52 END IF;
53
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 oe_order_wf_util.createstart_hdrinternal( P_ITEM_TYPE => l_item_type,
57 P_HEADER_ID => p_header_id,
58 P_TRANSACTION_NUMBER => p_blanket_number,
59 P_SALES_DOCUMENT_TYPE_CODE => l_sales_doc_type_code);
60 oe_order_wf_util.Start_All_Flows;
61
62 IF p_transaction_phase_code = 'F' then
63
64 if l_debug_level > 0 then
65 oe_debug_pub.ADD('Entering create_and_start_flow in full '||x_return_status,3 );
66 end if;
67
68 select count(1)
69 into l_count
70 from wf_items
71 where item_type = OE_GLOBALS.G_WFI_NGO
72 and item_key = to_char(p_header_id);
73
74 if l_count = 0
75 then
76 l_flow_status_code := 'ENTERED';
77 oe_order_wf_util.update_flow_status_code (
78 P_HEADER_ID => p_header_id,
79 P_LINE_ID => null,
80 P_FLOW_STATUS_CODE => l_flow_status_code,
81 P_ITEM_TYPE => l_item_type,
82 P_SALES_DOCUMENT_TYPE_CODE => l_sales_doc_type_code,
83 X_RETURN_STATUS => x_return_status );
84 if l_debug_level > 0 then
85 oe_debug_pub.ADD('Exiting create_and_start_flow in full '||x_return_status,3 );
86 end if;
87 END IF;
88
89 -- Call workflow api to update the flow
90
91 ELSE
92 if l_debug_level > 0 then
93 oe_debug_pub.ADD('Entering create_and_start_flow in neg '||x_return_status,3 );
94 end if;
95 l_flow_status_code := 'DRAFT';
96 oe_order_wf_util.update_flow_status_code (
97 P_HEADER_ID => p_header_id,
98 P_LINE_ID => null,
99 P_FLOW_STATUS_CODE => l_flow_status_code,
100 P_ITEM_TYPE => l_item_type,
101 P_SALES_DOCUMENT_TYPE_CODE => l_sales_doc_type_code,
102 X_RETURN_STATUS => x_return_status );
103 if l_debug_level > 0 then
104 oe_debug_pub.ADD('Exiting create_and_start_flow in neg '||x_return_status,3 );
105 end if;
106
107 -- Call workflow api to update the flow
108
109 END IF;
110
111 -- Get message count and data
112 -- end if;
113 oe_debug_pub.ADD(' Exsiting create_and_start_flow '||x_return_status,2 );
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 if l_debug_level > 0 then
118 oe_debug_pub.ADD('Blanket Workflow Exception ', 1);
119 end if;
120
121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122
123 /* IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
124 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
125 'create_and_start_flow');
126 END IF; */
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128
129 end create_and_start_flow;
130
131 PROCEDURE Submit_Draft ( p_header_id IN NUMBER,
132 p_transaction_phase_code IN VARCHAR2,
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_count OUT NOCOPY NUMBER,
135 x_msg_data OUT NOCOPY VARCHAR2)
136 IS
137
138 --
139 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
140 submit_draft_exception exception;
141 l_return_status varchar2(1);
142 l_qa_return_status varchar2(1) := 'Y';
143 l_msg_count number;
144 l_msg_data varchar2(2000);
145 l_blanket_lock_control number;
146 l_Last_Updated_By number; --bug6627904
147 --
148 BEGIN
149 if l_debug_level > 0 then
150 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Submit_draft ', 1);
151 end if;
152 OE_MSG_PUB.initialize;
153 OE_MSG_PUB.set_msg_context(
154 p_entity_code => 'BLANKET_HEADER'
155 ,p_entity_id => p_header_id
156 ,p_header_id => p_header_id
157 ,p_line_id => null
158 ,p_orig_sys_document_ref => null
159 ,p_orig_sys_document_line_ref => null
160 ,p_change_sequence => null
161 ,p_source_document_id => null
162 ,p_source_document_line_id => null
163 ,p_order_source_id => null
164 ,p_source_document_type_id => null);
165
166 x_return_status := FND_API.G_RET_STS_SUCCESS;
167
168 -- Calling Q and A Contracts
169 -- qa_articles is called with NORMAL mode
170 if l_debug_level > 0 then
171 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Submit_draft before QA'||to_char(p_header_id));
172 end if;
173
174 l_qa_return_status := 'S'; --- Temporally setting this value .
175
176
177
178 if l_debug_level > 0 then
179 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Submit_draft after QA'||to_char(p_header_id));
180 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Submit_draft after QA l_qa_R '||l_qa_return_status);
181 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Submit_draft after QA l_r'||l_return_status);
182 end if;
183 --- If the Qand A is passed
184
185 IF p_transaction_phase_code = 'N'
186 THEN
187 OE_NEGOTIATE_WF.submit_draft(p_header_id => p_header_id,
188 x_return_status => l_return_status);
189 if l_debug_level > 0 then
190 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Submit_draft after Neg ret'||l_return_status);
191 end if;
192 ELSE
193 oe_blanket_wf.submit_draft(p_header_id => p_header_id,
194 p_transaction_phase_code => p_transaction_phase_code,
195 x_return_status => l_return_status);
196 if l_debug_level > 0 then
197 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Submit_draft after Ful ret'||l_return_status);
198 end if;
199 END IF;
200 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
201 THEN
202 x_return_status := l_return_status;
203 RAISE submit_draft_exception;
204 else
205 x_return_status := l_return_status;
206 l_Last_Updated_By := Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1);--bug6627904
207 update oe_blanket_headers
208 set lock_control = lock_control + 1,
209 Last_Updated_By = l_Last_Updated_By,
210 LAST_UPDATE_DATE = sysdate
211 where header_id = p_header_id;
212 END IF;
213 -- Get message count and data
214
215 OE_MSG_PUB.Count_And_Get(p_count => x_msg_count,
216 p_data => x_msg_data);
217
218 if l_debug_level > 0 then
219 oe_debug_pub.ADD('End OE_BLANKET_WF_UTIL.Submit_draft return status'||l_return_status);
220 oe_debug_pub.ADD('End OE_BLANKET_WF_UTIL.Submit_draft qa return status'||l_qa_return_status);
221 end if;
222
223 EXCEPTION
224
225 WHEN SUBMIT_DRAFT_EXCEPTION THEN
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228
229 WHEN OTHERS THEN
230 if l_debug_level > 0 then
231 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
232 oe_debug_pub.ADD('In Blanket Workflow Exception QA return Status'||l_qa_return_Status);
233 oe_debug_pub.ADD('In Blanket Workflow Exception l return Status'||l_return_Status);
234 end if;
235
236 x_return_status := l_return_status;
237
238 /* IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
239 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
240 'Submit_Draft');
241 END IF; */
242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
243
244 END Submit_Draft;
245
246 PROCEDURE Blanket_Date_Changed ( p_header_id IN NUMBER,
247 x_return_status OUT NOCOPY VARCHAR2)
248 IS
249
250 --
251 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
252 --
253 l_status VARCHAR2(30);
254 BEGIN
255 if l_debug_level > 0 then
256 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Blanket_Date_Changed ', 1);
257 end if;
258
259 -- Bug 3217417
260 -- Call Extend API if status is Expired and Date is Changed
261 -- For all other statuses, call Date_Changed API
262 select flow_status_code
263 into l_status
264 from oe_blanket_headers
265 where header_id = p_header_id;
266
267 IF l_status = 'EXPIRED' THEN
268 oe_blanket_wf.Extend(p_header_id => p_header_id,
269 x_return_status => x_return_status);
270 ELSE
271 oe_blanket_wf.Blanket_Date_Changed(p_header_id => p_header_id,
272 x_return_status => x_return_status);
273 END IF;
274 /* OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
275 , p_data => x_msg_data); */
276
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 if l_debug_level > 0 then
281 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
282 end if;
283
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
285
286 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
287 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
288 'Blanket_Date_Changed');
289 END IF;
290 /* OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
291 , p_data => x_msg_data); */
292
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294
295 END Blanket_Date_Changed;
296
297 PROCEDURE customer_acceptance (p_header_id IN NUMBER,
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2)
301
302 IS
303
304 --
305 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
306 --
307 BEGIN
308 if l_debug_level > 0 then
309 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Customer_Accepted ', 1);
310 end if;
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312
313 OE_NEGOTIATE_WF.Customer_Accepted(p_header_id => p_header_id,
314 x_return_status => x_return_status);
315 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
316 , p_data => x_msg_data);
317
318
319 EXCEPTION
320 WHEN OTHERS THEN
321 if l_debug_level > 0 then
322 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
323 end if;
324
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
326
327 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
328 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
329 'Customer_Accepted');
330 END IF;
331 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
332 , p_data => x_msg_data);
333
334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
335
336 END Customer_acceptance;
337
338 PROCEDURE Customer_Rejected (p_header_id IN NUMBER,
339 p_entity_code IN VARCHAR2,
340 p_version_number IN NUMBER,
341 p_reason_type IN VARCHAR2,
342 p_reason_code IN VARCHAR2,
343 p_reason_comments IN VARCHAR2,
344 x_return_status OUT NOCOPY VARCHAR2,
345 x_msg_count OUT NOCOPY NUMBER,
346 x_msg_data OUT NOCOPY VARCHAR2)
347 IS
348 l_header_id number := p_header_id;
349 l_entity_id varchar2(240) := p_entity_code;
350 l_version_number number := p_version_number;
351 l_reason_type varchar2(240) := p_reason_type;
352 l_reason_code varchar2(240) := p_reason_code;
353 l_reason_comments varchar2(2000) := p_reason_comments;
354 l_return_status varchar2(1);
355 --
356 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
357 --
358 BEGIN
359 if l_debug_level > 0 then
360 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Customer_Rejected ', 1);
361 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Customer_Rejected HID'||l_header_id);
362 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Customer_Rejected VNUM'||l_version_number);
363 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Customer_Rejected RTYPE'||l_reason_type);
364 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Customer_Rejected RCODE'||l_reason_code);
365 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Customer_Rejected RCOMM'||l_reason_comments);
366 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Customer_RejecteD RETSTAT'||x_return_status);
367 end if;
368 OE_NEGOTIATE_WF.Customer_Rejected(p_header_id => l_header_id,
369 p_entity_code => l_entity_id,
370 p_version_number => l_version_number,
371 p_reason_type => l_reason_type,
372 p_reason_code => l_reason_code,
373 p_reason_comments => l_reason_comments,
374 x_return_status => x_return_status);
375 if l_debug_level > 0 then
376 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.Customer_Rejected ', 1);
377 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.Customer_Rejected HID '||l_header_id);
378 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.Customer_Rejected VNUM '||l_version_number);
379 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.Customer_Rejected RTYPE '||l_reason_type);
380 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.Customer_Rejected RCODE '||l_reason_code);
381 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.Customer_Rejected RCOMM '||l_reason_comments);
382 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.Customer_RejecteD RETSTAT '||x_return_status);
383 end if;
384
385 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
386 , p_data => x_msg_data);
387
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 if l_debug_level > 0 then
392 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
393 end if;
394
395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
396
397 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
398 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
399 'Customer_Rejected');
400 END IF;
401 OE_MSG_PUB.Count_And_Get
402 ( p_count => x_msg_count
403 , p_data => x_msg_data
404 );
405
406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
407
408 end Customer_Rejected;
409
410 PROCEDURE check_release (p_blanket_number IN NUMBER,
411 x_return_status OUT NOCOPY VARCHAR2)
412 IS
413
414 --
415 l_dumy varchar2(10) := 'FALSE';
416 l_return_status varchar2(10);
417 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
418 --
419 BEGIN
420
421 if l_debug_level > 0 then
422 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Check_release ', 1);
423 end if;
424 l_return_status := FND_API.G_RET_STS_SUCCESS;
425 if p_blanket_number is not null then
426
427 SELECT 'TRUE'
428 INTO l_dumy
429 FROM oe_order_headers
430 WHERE blanket_number = p_blanket_number
431 AND open_flag = 'Y'
432 AND ROWNUM = 1;
433 IF l_dumy = 'TRUE' THEN
434 l_return_status := FND_API.G_RET_STS_ERROR;
435 fnd_message.set_name('ONT', 'OE_BKT_NO_DATE_CHANGE');
436 oe_msg_pub.add;
437 END IF;
438
439 IF l_dumy = 'FALSE' THEN
440 SELECT 'TRUE'
441 INTO l_dumy
442 FROM oe_order_lines
443 WHERE blanket_number = p_blanket_number
444 AND open_flag = 'Y'
445 AND ROWNUM = 1;
446 IF l_dumy = 'TRUE' THEN
447 l_return_status := FND_API.G_RET_STS_ERROR;
448 fnd_message.set_name('ONT', 'OE_BKT_NO_DATE_CHANGE');
449 oe_msg_pub.add;
450 END IF;
451 END IF;
452 x_return_status := l_return_status;
453 end if;
454
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 x_return_status := l_return_status;
458
459 WHEN OTHERS THEN
460 x_return_status :='P';
461 if l_debug_level > 0 then
462 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
463 end if;
464
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466
467 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
468 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
469 'Check Release');
470 END IF;
471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472
473 END CHECK_RELEASE;
474
475 PROCEDURE Extend (p_header_id IN NUMBER,
476 x_return_status OUT NOCOPY VARCHAR2)
477 IS
478
479 --
480 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
481 --
482 BEGIN
483 if l_debug_level > 0 then
484 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Extend ', 1);
485 end if;
486 OE_BLANKET_WF.Extend(p_header_id => p_header_id,
487 x_return_status => x_return_status);
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 if l_debug_level > 0 then
492 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
493 end if;
494
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496
497 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
498 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
499 'Extend');
500 END IF;
501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
502
503 END Extend;
504
505 PROCEDURE Close (p_header_id IN NUMBER,
506 x_return_status OUT NOCOPY VARCHAR2,
507 x_msg_count OUT NOCOPY NUMBER,
508 x_msg_data OUT NOCOPY VARCHAR2)
509 IS
510
511 --
512 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
513 --
514 BEGIN
515 if l_debug_level > 0 then
516 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Close ', 1);
517 end if;
518 OE_BLANKET_WF.Close(p_header_id => p_header_id,
519 x_return_status => x_return_status);
520 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
521 , p_data => x_msg_data);
522
523 EXCEPTION
524 WHEN OTHERS THEN
525 if l_debug_level > 0 then
526 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
527 end if;
528
529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
530
531 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
532 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
533 'Close');
534 END IF;
535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536
537 END Close;
538
539 PROCEDURE Terminate (p_header_id IN NUMBER,
540 p_terminated_by IN NUMBER,
541 p_version_number IN NUMBER,
542 p_reason_type IN VARCHAR2,
543 p_reason_code IN VARCHAR2,
544 p_reason_comments IN VARCHAR2,
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2)
548 IS
549 l_header_id number := p_header_id;
550 l_terminated_by NUMBER := p_terminated_by;
551 l_version_number number := p_version_number;
552 l_reason_type varchar2(240) := p_reason_type;
553 l_reason_code varchar2(240) := p_reason_code;
554 l_reason_comments varchar2(2000) := p_reason_comments;
555 --
556 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
557 --
558 BEGIN
559 if l_debug_level > 0 then
560 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Terminate ', 1);
561 end if;
562 OE_BLANKET_WF.Terminate(p_header_id => l_header_id,
563 p_terminated_by => l_terminated_by,
564 p_version_number => l_version_number,
565 p_reason_type => l_reason_type,
566 p_reason_code => l_reason_code,
567 p_reason_comments => l_reason_comments,
568 x_return_status => x_return_status);
569 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
570 , p_data => x_msg_data);
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 if l_debug_level > 0 then
575 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
576 end if;
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578
579 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
580 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
581 'Terminate');
582 END IF;
583 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
584 , p_data => x_msg_data);
585
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587
588 end Terminate;
589
590 PROCEDURE Lost (p_header_id IN NUMBER,
591 p_entity_code IN VARCHAR2,
592 p_version_number IN NUMBER,
593 p_reason_type IN VARCHAR2,
594 p_reason_code IN VARCHAR2,
595 p_reason_comments IN VARCHAR2,
596 x_return_status OUT NOCOPY VARCHAR2,
597 x_msg_count OUT NOCOPY NUMBER,
598 x_msg_data OUT NOCOPY VARCHAR2)
599 IS
600 l_header_id number := p_header_id;
601 l_entity_code varchar2(100) := p_entity_code;
602 l_version_number number := p_version_number;
603 l_reason_type varchar2(240) := p_reason_type;
604 l_reason_code varchar2(240) := p_reason_code;
605 l_reason_comments varchar2(2000) := p_reason_comments;
606 --
607 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
608 --
609 BEGIN
610 if l_debug_level > 0 then
611 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.LOST ', 1);
612 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.LOST HID'||l_header_id);
613 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.LOST ECODE'||l_entity_code);
614 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.LOST VNUM'||l_version_number);
615 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.LOST RTYPE'||l_reason_type);
616 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.LOST RCODE'||l_reason_code);
617 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.LOST RCOMM'||l_reason_comments);
618 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.LOST RETSTAT'||x_return_status);
619 end if;
620 OE_NEGOTIATE_WF.Lost(p_header_id => l_header_id,
621 p_entity_code => l_entity_code,
622 p_version_number => l_version_number,
623 p_reason_type => l_reason_type,
624 p_reason_code => l_reason_code,
625 p_reason_comments => l_reason_comments,
626 x_return_status => x_return_status);
627 if l_debug_level > 0 then
628 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.LOST ', 1);
629 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.LOST HID'||l_header_id);
630 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.LOST ECODE'||l_entity_code);
631 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.LOST VNUM'||l_version_number);
632 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.LOST RTYPE'||l_reason_type);
633 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.LOST RCODE'||l_reason_code);
634 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.LOST RCOMM'||l_reason_comments);
635 oe_debug_pub.ADD('Entering OE_NEGOTIATE_WF.LOST RETSTAT'||x_return_status);
636 end if;
637 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
638 , p_data => x_msg_data);
639
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 if l_debug_level > 0 then
644 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
645 end if;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647
648 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
649 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
650 'Lost');
651 END IF;
652 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
653 , p_data => x_msg_data);
654
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
656
657 end Lost;
658
659 PROCEDURE Complete_Negotiation (p_header_id IN NUMBER,
660 x_return_status OUT NOCOPY VARCHAR2)
661 -- x_msg_count OUT NOCOPY NUMBER,
662 -- x_msg_data OUT NOCOPY VARCHAR2)
663 IS
664 l_return_status varchar2(1);
665 l_result varchar2(1);
666 l_rowtype_rec oe_ak_blanket_headers_v%rowtype;
667 l_action number;
668 l_msg_count number;
669 l_msg_data varchar2(2000);
670 l_header_id number;
671 l_line_id number;
672 l_blanket_lock_control number;
673 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level; -- Bug 8816026
674 l_item_type varchar2(10) := 'OEBH';
675 l_sales_doc_type_code varchar2(1) := 'B';
676 l_blanket_number number;
677 Blanket_Complete_Negotiation exception;
678 l_Last_Updated_By number;
679
680 cursor c_get_lines(p_header_id in number) is
681 select line_id from oe_blanket_lines
682 where header_id = p_header_id;
683
684
685 Begin
686 l_header_id := p_header_id;
687 if l_debug_level > 0 then
688 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Complete_Negotiation in WF UTIL'||l_return_status);
689 end if;
690 IF l_header_id is not null then
691
692 SAVEPOINT Save_Blanket_Changes;
693
694 -- begin fix for bug 3559904: check constraint for phase change
695 IF NOT (OE_Blanket_Util.g_old_version_captured) THEN
696 OE_Blanket_Util.Query_Blanket(p_header_id => p_header_id, p_x_header_rec => OE_Blanket_Util.g_old_header_hist_rec, p_x_line_tbl => OE_Blanket_Util.g_old_line_hist_tbl, x_return_status => l_return_status);
697 OE_Blanket_Util.g_old_version_captured := TRUE;
698 END IF;
699
700 OE_BLANKET_UTIL.API_Rec_To_Rowtype_Rec(OE_Blanket_Util.g_old_header_hist_rec,l_rowtype_rec);
701
702 -- Initialize security global record
703 OE_Blanket_Header_Security.g_record := l_rowtype_rec;
704 OE_Blanket_Header_Security.g_check_all_cols_constraint := 'Y';
705 OE_Quote_Util.G_COMPLETE_NEG := 'Y';
706
707 BEGIN
708 l_result := OE_Blanket_Header_Security.Is_OP_Constrained
709 (p_operation => OE_PC_GLOBALS.UPDATE_OP
710 ,p_column_name => 'TRANSACTION_PHASE_CODE'
711 ,p_record => l_rowtype_rec
712 ,x_on_operation_action => l_action
713 );
714
715 EXCEPTION
716 WHEN FND_API.G_EXC_ERROR THEN
717 l_return_status := FND_API.G_RET_STS_ERROR;
718 raise fnd_api.g_exc_error;
719 END;
720
721 if l_result = OE_PC_GLOBALS.YES then
722 l_return_status := FND_API.G_RET_STS_ERROR;
723 raise fnd_api.g_exc_error;
724 end if;
725
726 OE_GLOBALS.G_REASON_CODE := 'SYSTEM';
727 OE_GLOBALS.G_CAPTURED_REASON := 'Y';
728
729 OE_Versioning_Util.Perform_Versioning(p_header_id => p_header_id,
730 p_document_type => 'BLANKETS',
731 x_msg_count => l_msg_count,
732 x_msg_data => l_msg_data,
733 x_return_status => l_return_status);
734
735 OE_Blanket_Util.g_old_version_captured := FALSE;
736 OE_Quote_Util.G_COMPLETE_NEG := 'N';
737
738 l_blanket_number := OE_Blanket_Util.g_old_header_hist_rec.order_number;
739
740 -- end fix for bug 3559904
741
742 if l_debug_level > 0 then
743 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Complete_Negotiation in WF UTIL HDR ID'
744 ||l_header_id);
745 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Complete_Negotiation in WF UTIL BLKT NUM'
746 ||l_blanket_number);
747 end if;
748 OE_Blanket_Util.Lock_Row(p_blanket_id=>p_header_id
749 , p_blanket_line_id => null
750 , p_x_lock_control=>l_blanket_lock_control
751 , x_return_status => l_return_status
752 , x_msg_count => l_msg_count
753 , x_msg_data => l_msg_data);
754
755 if l_debug_level > 0 then
756 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Complete_Negotiation after LOCKING HEADER'
757 ||l_return_status);
758 end if;
759 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
760 x_return_status := l_return_status;
761 ROLLBACK TO SAVEPOINT Save_Blanket_Changes;
762 END IF;
763
764 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
765 x_return_status := l_return_status;
766 RAISE FND_API.G_EXC_ERROR;
767 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
768 x_return_status := l_return_status;
769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
770 END IF;
771 /*
772 OE_MSG_PUB.set_msg_context (p_entity_code => 'BLANKET',
773 p_entity_id => p_header_id,
774 p_header_id => p_header_id);
775 */
776 l_Last_Updated_By := Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1);
777 update oe_blanket_headers
778 set TRANSACTION_PHASE_CODE = 'F',
779 lock_control = lock_control + 1,
780 Last_Updated_By = l_Last_Updated_By,
781 LAST_UPDATE_DATE = sysdate
782 where header_id = p_header_id;
783
784
785 /* Call the lines and loop around for the lock and update the transaction Phase */
786
787
788 OPEN c_get_lines(l_header_id);
789 LOOP
790 FETCH c_get_lines INTO l_line_id;
791 EXIT WHEN c_get_lines%NOTFOUND;
792
793 OE_Blanket_Util.Lock_Row(p_blanket_id=>null
794 , p_blanket_line_id => l_line_id
795 , p_x_lock_control=>l_blanket_lock_control
796 , x_return_status => l_return_status
797 , x_msg_count => l_msg_count
798 , x_msg_data => l_msg_data);
799 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
800 x_return_status := l_return_status;
801 ROLLBACK TO SAVEPOINT Save_Blanket_Changes;
802 END IF;
803
804 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
805 x_return_status := l_return_status;
806 RAISE FND_API.G_EXC_ERROR;
807 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
808 x_return_status := l_return_status;
809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
810 END IF;
811
812 update oe_blanket_lines
813 set TRANSACTION_PHASE_CODE = 'F',
814 lock_control = lock_control + 1,
815 Last_Updated_By = l_Last_Updated_By,
816 LAST_UPDATE_DATE = sysdate
817 where header_id = l_header_id and line_id = l_line_id;
818 if l_debug_level > 0 then
819 oe_debug_pub.ADD('Entering OE_BLANKET_WF_UTIL.Complete_Negotiation after UPDATE'
820 ||l_return_status);
821 end if;
822 END LOOP;
823 CLOSE c_get_lines;
824 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
825 oe_order_wf_util.createstart_hdrinternal( P_ITEM_TYPE => l_item_type,
826 P_HEADER_ID => l_header_id,
827 P_TRANSACTION_NUMBER => l_blanket_number,
828 P_SALES_DOCUMENT_TYPE_CODE => l_sales_doc_type_code);
829 oe_order_wf_util.Start_All_Flows;
830 x_return_status := l_return_status; -- Bug 8816026
831
832 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
833 x_return_status := l_return_status;
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
836 x_return_status := l_return_status;
837 RAISE FND_API.G_EXC_ERROR;
838 END IF;
839
840 END IF;
841 if l_debug_level > 0 then
842 oe_debug_pub.ADD('End OE_BLANKET_WF_UTIL.Complete_Negotiation return status'||l_return_status);
843 end if;
844 /* OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
845 , p_data => x_msg_data);
846 */
847
848 EXCEPTION
849
850 WHEN Blanket_Complete_Negotiation THEN
851 OE_Quote_Util.G_COMPLETE_NEG := 'N';
852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
854
855 WHEN FND_API.G_EXC_ERROR THEN
856 OE_Quote_Util.G_COMPLETE_NEG := 'N';
857 if l_debug_level > 0 then
858 oe_debug_pub.ADD('In Blanket Workflow Execution error ', 1);
859 end if;
860
861 x_return_status := l_return_status;
862
863 WHEN OTHERS THEN
864 OE_Quote_Util.G_COMPLETE_NEG := 'N';
865
866 if l_debug_level > 0 then
867 oe_debug_pub.ADD('In Blanket Workflow Exception ', 1);
868 oe_debug_pub.ADD('In Blanket Workflow Exception Complete Negotiation return Status'
869 ||l_return_Status);
870 end if;
871
872 x_return_status := l_return_status;
873
874 /* OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count
875 , p_data => x_msg_data); */
876
877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
878
879 end Complete_Negotiation;
880
881 end oe_blanket_wf_util;