[Home] [Help]
PACKAGE BODY: APPS.OE_NEGOTIATE_WF
Source
1 PACKAGE BODY OE_Negotiate_WF as
2 /* $Header: OEXWNEGB.pls 120.7.12020000.3 2013/01/30 08:04:45 spothula ship $ */
3
4
5 PROCEDURE Update_Status_Lost(
6 itemtype in varchar2,
7 itemkey in varchar2,
8 actid in number,
9 funcmode in varchar2,
10 resultout in out nocopy varchar2)
11 IS
12 --
13 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
14 l_return_status VARCHAR2(30);
15 --
16
17 BEGIN
18 IF l_debug_level > 0 THEN
19 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Update_Status_Lost:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
20 END IF;
21 OE_STANDARD_WF.Set_Msg_Context(actid);
22 IF (funcmode = 'RUN') then
23
24 OE_MSG_PUB.set_msg_context(
25 p_entity_code => 'HEADER'
26 ,p_entity_id => to_number(itemkey)
27 ,p_header_id => to_number(itemkey));
28
29 OE_ORDER_WF_UTIL.Update_Quote_Blanket( p_item_type => OE_GLOBALS.G_WFI_NGO,
30 p_item_key => itemkey,
31 p_flow_status_code => 'LOST',
32 p_open_flag => 'N',
33 x_return_status => l_return_status);
34
35 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
36 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
37 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
38 RAISE FND_API.G_EXC_ERROR;
39 END IF;
40
41 resultout := 'COMPLETE';
42
43 END IF;
44 EXCEPTION
45 when others then
46 wf_core.context('OE_Negotiate_WF', 'Update_Status_Lost', itemtype, itemkey, to_char(actid), funcmode);
47 -- start data fix project
48 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
49 p_itemtype => itemtype,
50 p_itemkey => itemkey);
51 -- end data fix project
52 oe_standard_wf.save_messages;
53 oe_standard_wf.clear_msg_context;
54 raise;
55
56 END Update_Status_Lost;
57
58
59 PROCEDURE Negotiation_Complete(
60 itemtype in varchar2,
61 itemkey in varchar2,
62 actid in number,
63 funcmode in varchar2,
64 resultout in out nocopy varchar2)
65 IS
66 --
67 l_sales_document_type_code VARCHAR2(1);
68 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
69 l_return_status VARCHAR2(30);
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(2000);
72 --
73
74 BEGIN
75 IF l_debug_level > 0 THEN
76 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Negotiation_Complete:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
77 END IF;
78 OE_STANDARD_WF.Set_Msg_Context(actid);
79 IF (funcmode = 'RUN') then
80
81 OE_MSG_PUB.set_msg_context(
82 p_entity_code => 'HEADER'
83 ,p_entity_id => to_number(itemkey)
84 ,p_header_id => to_number(itemkey));
85
86 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'SALES_DOCUMENT_TYPE_CODE');
87 IF l_sales_document_type_code = 'O' THEN
88 -- Quote Complete_Negotiation
89 OE_QUOTE_UTIL.Complete_Negotiation(p_header_id => to_number(itemkey), x_return_status => l_return_status,
90 x_msg_count => l_msg_Count, x_msg_data => l_msg_data);
91
92 ELSIF l_sales_document_type_code = 'B' THEN
93 -- Blanket Complete_Negotiation
94 OE_BLANKET_WF_UTIL.Complete_Negotiation(p_header_id => to_number(itemkey),
95 x_return_status => l_return_status);
96 ELSE
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
100 resultout := 'COMPLETE:COMPLETE';
101 ELSE
102 resultout := 'COMPLETE:INCOMPLETE';
103 oe_standard_wf.save_messages;
104 oe_standard_wf.clear_msg_context;
105 END IF;
106 END IF;
107 EXCEPTION
108 when others then
109 wf_core.context('OE_Negotiate_WF', 'Negotiation_Complete', itemtype, itemkey, to_char(actid), funcmode);
110 -- start data fix project
111 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
112 p_itemtype => itemtype,
113 p_itemkey => itemkey);
114 -- end data fix project
115 oe_standard_wf.save_messages;
116 oe_standard_wf.clear_msg_context;
117 raise;
118
119 END Negotiation_Complete;
120
121
122 PROCEDURE Submit_Draft_Internal(
123 itemtype in varchar2,
124 itemkey in varchar2,
125 actid in number,
126 funcmode in varchar2,
127 resultout in out nocopy varchar2)
128 IS
129 --
130 l_sales_document_type_code VARCHAR2(1);
131 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
132 l_return_status VARCHAR2(30);
133 l_validate_cfg BOOLEAN;
134 Cursor Query_Lines IS
135 SELECT item_type_code, ordered_quantity
136 FROM oe_order_lines_all
137 WHERE header_id = to_number(itemkey);
138
139 l_item_type_code VARCHAR2(30);
140 l_qa_return_status VARCHAR2(30);
141 l_msg_count NUMBER;
142 l_msg_data VARCHAR2(2000);
143 l_ordered_quantity NUMBER;
144 l_sales_document_type VARCHAR2(30);
145 --
146
147 BEGIN
148 IF l_debug_level > 0 THEN
149 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Submit_Draft_Internal:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
150 END IF;
151 OE_STANDARD_WF.Set_Msg_Context(actid);
152
153 IF (funcmode = 'RUN') then
154
155 OE_MSG_PUB.set_msg_context(
156 p_entity_code => 'HEADER'
157 ,p_entity_id => to_number(itemkey)
158 ,p_header_id => to_number(itemkey));
159
160 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'SALES_DOCUMENT_TYPE_CODE');
161 --OE_BLANKET_WF_UTIL.Blanket_QA_Articles(p_header_id => to_number(itemkey),
162 -- x_return_status => l_return_status);
163
164 OE_CONTRACTS_UTIL.qa_articles ( p_api_version => 1.0,
165 p_doc_type => l_sales_document_type_code,
166 p_doc_id => to_number(itemkey),
167 x_qa_return_status => l_qa_return_status,
168 x_return_status => l_return_status,
169 x_msg_count => l_msg_count,
170 x_msg_data => l_msg_data);
171
172 IF l_debug_level > 0 THEN
173 oe_debug_pub.add( 'Contract returns: x_return_status:' || l_return_status || ' x_qa_return_status:' || l_qa_return_status, 1);
174 END IF;
175 -- If API call is successful, but the check failed, return incomplete
176
177 IF l_return_status = FND_API.G_RET_STS_SUCCESS
178 AND l_qa_return_status <> FND_API.G_RET_STS_SUCCESS
179 AND l_qa_return_status <> 'W' THEN
180 resultout := 'COMPLETE:INCOMPLETE';
181 OE_STANDARD_WF.Save_Messages;
182 OE_STANDARD_WF.Clear_Msg_Context;
183 return;
184 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
185 -- API call failed completely, fail the activity
186 app_exception.raise_exception;
187 END IF;
188
189
190 -- Quotes need to check configs
191 IF l_sales_document_type_code = 'O' THEN
192 l_validate_cfg := FALSE;
193 Open Query_Lines;
194 Loop
195 FETCH Query_Lines INTO l_item_type_code, l_ordered_quantity;
196 EXIT WHEN Query_Lines%NOTFOUND;
197 IF l_item_type_code = 'MODEL' THEN
198 l_validate_cfg := TRUE;
199 END IF;
200
201 IF nvl(l_ordered_quantity, 0) = 0 THEN
202 IF l_sales_document_type_code = 'O' THEN
203 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
204 ELSE -- assume blanket
205 fnd_message.set_name('ONT', 'OE_NTF_BSA');
206 END IF;
207 l_sales_document_type := fnd_message.get;
208
209 FND_MESSAGE.SET_NAME('ONT', 'OE_ZERO_QUANTITY');
210 FND_MESSAGE.SET_TOKEN('SALES_DOCUMENT_TYPE', l_sales_document_type);
211 oe_msg_pub.add;
212 resultout := 'COMPLETE:INCOMPLETE';
213 OE_STANDARD_WF.Save_Messages;
214 OE_STANDARD_WF.Clear_Msg_Context;
215 return;
216 END IF;
217 End Loop;
218 Close Query_Lines;
219
220 IF l_debug_level > 0 THEN
221 oe_debug_pub.add('Submit_Draft_Internal: Finish looking for Configs', 1);
222 END IF;
223
224 IF l_validate_cfg THEN
225 l_return_status := OE_Config_Util.Validate_Cfgs_In_Order(p_header_id => to_number(itemkey));
226 IF l_debug_level > 0 THEN
227 oe_debug_pub.add( 'RETURN STATUS AFTER VALIDATE CFGS:'||L_RETURN_STATUS );
228 END IF;
229
230 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
231 resultout := 'COMPLETE:INCOMPLETE';
232 OE_STANDARD_WF.Save_Messages;
233 OE_STANDARD_WF.Clear_Msg_Context;
234 return;
235 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
236 app_exception.raise_exception;
237 END IF;
238
239 END IF;
240
241 END IF; -- blanket or quote
242
243 OE_ORDER_WF_UTIL.Update_Quote_Blanket(p_item_type => OE_GLOBALS.G_WFI_NGO,
244 p_item_key => itemkey,
245 p_flow_status_code => 'DRAFT_SUBMITTED',
246 p_draft_submitted_flag => 'Y',
247 x_return_status => l_return_status);
248 IF l_debug_level > 0 THEN
249 oe_debug_pub.add('Submit_Draft_Internal: Finish calling Update Draft Submitted status: ' || l_return_status, 1);
250 END IF;
251
252 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
253 resultout := 'COMPLETE:INCOMPLETE';
254 OE_STANDARD_WF.Save_Messages;
255 OE_STANDARD_WF.Clear_Msg_Context;
256 return;
257 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
258 app_exception.raise_exception;
259 END IF;
260
261 resultout := 'COMPLETE:COMPLETE';
262
263 IF l_debug_level > 0 THEN
264 oe_debug_pub.add('Exiting OE_NEGOTIATE_WF.Submit_Draft_Internal Normally', 1);
265 END IF;
266
267
268 END IF;
269 EXCEPTION
270 when others then
271 wf_core.context('OE_Negotiate_WF', 'Submit_Draft_Internal', itemtype, itemkey, to_char(actid), funcmode);
272 -- start data fix project
273 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
274 p_itemtype => itemtype,
275 p_itemkey => itemkey);
276 -- end data fix project
277 oe_standard_wf.save_messages;
278 oe_standard_wf.clear_msg_context;
279 raise;
280
281 END Submit_Draft_Internal;
282
283
284 PROCEDURE Customer_Acceptance(
285 itemtype in varchar2,
286 itemkey in varchar2,
287 actid in number,
288 funcmode in varchar2,
289 resultout in out nocopy varchar2)
290 IS
291 --
292 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
293 l_return_status VARCHAR2(30);
294 --
295
296 BEGIN
297 IF l_debug_level > 0 THEN
298 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Customer_Acceptance:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
299 END IF;
300 OE_STANDARD_WF.Set_Msg_Context(actid);
301 IF (funcmode = 'RUN') then
302 OE_MSG_PUB.set_msg_context(
303 p_entity_code => 'HEADER'
304 ,p_entity_id => to_number(itemkey)
305 ,p_header_id => to_number(itemkey));
306
307 OE_ORDER_WF_UTIL.Update_Quote_Blanket(p_item_type => OE_GLOBALS.G_WFI_NGO,
308 p_item_key => itemkey,
309 p_flow_status_code => 'PENDING_CUSTOMER_ACCEPTANCE',
310 x_return_status => l_return_status);
311
312 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
314 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
315 RAISE FND_API.G_EXC_ERROR;
316 END IF;
317
318 resultout := 'NOTIFIED:#NULL';
319
320 END IF;
321 EXCEPTION
322 when others then
323 wf_core.context('OE_Negotiate_WF', 'Customer_Acceptance', itemtype, itemkey, to_char(actid), funcmode);
324 -- start data fix project
325 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
326 p_itemtype => itemtype,
327 p_itemkey => itemkey);
328 -- end data fix project
329 oe_standard_wf.save_messages;
330 oe_standard_wf.clear_msg_context;
331 raise;
332
333 END Customer_Acceptance;
334
335
336 PROCEDURE Update_Customer_Accepted(
337 itemtype in varchar2,
338 itemkey in varchar2,
339 actid in number,
340 funcmode in varchar2,
341 resultout in out nocopy varchar2)
342 IS
343 --
344 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
345 l_return_status VARCHAR2(30);
346 --
347
348 BEGIN
349 IF l_debug_level > 0 THEN
350 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Update_Customer_Accepted:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
351 END IF;
352 OE_STANDARD_WF.Set_Msg_Context(actid);
353 IF (funcmode = 'RUN') then
354 OE_MSG_PUB.set_msg_context(
355 p_entity_code => 'HEADER'
356 ,p_entity_id => to_number(itemkey)
357 ,p_header_id => to_number(itemkey));
358
359 OE_ORDER_WF_UTIL.Update_Quote_Blanket(p_item_type => OE_GLOBALS.G_WFI_NGO,
360 p_item_key => itemkey,
361 p_flow_status_code => 'CUSTOMER_ACCEPTED',
362 x_return_status => l_return_status);
363
364 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369
370
371 resultout := 'COMPLETE';
372
373 END IF;
374 EXCEPTION
375 when others then
376 wf_core.context('OE_Negotiate_WF', 'Update_Customer_Accepted', itemtype, itemkey, to_char(actid), funcmode);
377 -- start data fix project
378 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
379 p_itemtype => itemtype,
380 p_itemkey => itemkey);
381 -- end data fix project
382 oe_standard_wf.save_messages;
383 oe_standard_wf.clear_msg_context;
384 raise;
385
386 END Update_Customer_Accepted;
387
388
389 PROCEDURE Update_Customer_Rejected(
390 itemtype in varchar2,
391 itemkey in varchar2,
392 actid in number,
393 funcmode in varchar2,
394 resultout in out nocopy varchar2)
395 IS
396 --
397 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
398 l_return_status VARCHAR2(30);
399 --
400
401 BEGIN
402
403 IF l_debug_level > 0 THEN
404 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Update_Customer_Rejected:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
405 END IF;
406 OE_STANDARD_WF.Set_Msg_Context(actid);
407 IF (funcmode = 'RUN') then
408 OE_MSG_PUB.set_msg_context(
409 p_entity_code => 'HEADER'
410 ,p_entity_id => to_number(itemkey)
411 ,p_header_id => to_number(itemkey));
412
413 OE_ORDER_WF_UTIL.Update_Quote_Blanket(p_item_type => OE_GLOBALS.G_WFI_NGO,
414 p_item_key => itemkey,
415 p_flow_status_code => 'DRAFT_CUSTOMER_REJECTED',
416 p_draft_submitted_flag => 'N',
417 x_return_status => l_return_status);
418
419 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
421 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424
425 resultout := 'COMPLETE';
426
427 END IF;
428 EXCEPTION
429 when others then
430 wf_core.context('OE_Negotiate_WF', 'Update_Customer_Rejected', itemtype, itemkey, to_char(actid), funcmode);
431 -- start data fix project
432 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
433 p_itemtype => itemtype,
434 p_itemkey => itemkey);
435 -- end data fix project
436 oe_standard_wf.save_messages;
437 oe_standard_wf.clear_msg_context;
438 raise;
439
440 END Update_Customer_Rejected;
441
442 PROCEDURE Check_Expiration_Date(
443 itemtype in varchar2,
444 itemkey in varchar2,
445 actid in number,
446 funcmode in varchar2,
447 resultout in out nocopy varchar2)
448 IS
449 --
450 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
451 l_expiration_date DATE;
452 l_pre_notification_percent NUMBER;
453 l_aname wf_engine.nametabtyp;
454 l_avalue wf_engine.numtabtyp;
455 l_final_timer NUMBER;
456 l_sales_document_type_code VARCHAR2(1);
457 --
458
459 BEGIN
460
461 IF l_debug_level > 0 THEN
462 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Check_Expiration_Date:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
463 END IF;
464 OE_STANDARD_WF.Set_Msg_Context(actid);
465 IF (funcmode = 'RUN') then
466 OE_MSG_PUB.set_msg_context(
467 p_entity_code => 'HEADER'
468 ,p_entity_id => to_number(itemkey)
469 ,p_header_id => to_number(itemkey));
470
471 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'SALES_DOCUMENT_TYPE_CODE');
472 IF l_sales_document_type_code = 'O' THEN
473 select expiration_date
474 into l_expiration_date
475 from oe_order_headers_all
476 where header_id = to_number(itemkey);
477 ELSE
478 -- even though there is no offer expiration date for blanket for now
479 -- we will still fetch it for the future
480 select expiration_date
481 into l_expiration_date
482 from oe_blanket_headers_all
483 where header_id = to_number(itemkey);
484 END IF;
485
486
487 IF l_expiration_date is null THEN
488 -- no expiration date, set both timer to null
489 l_aname(1) := 'OFFER_PRE_EXPIRE_TIMER';
490 l_avalue(1) := null;
491 l_aname(2) := 'OFFER_FINAL_EXPIRE_TIMER';
492 l_avalue(2) := null;
493
494 wf_engine.SetItemAttrNumberArray(itemtype=>itemtype
495 , itemkey=>itemkey
496 , aname=>l_aname
497 , avalue=>l_avalue
498 );
499 resultout := 'COMPLETE:COMPLETE';
500 IF l_debug_level > 0 THEN
501 oe_debug_pub.add('Leaving OE_Negotiate_WF.Check_Expiration_Date: NO TIMER TO SET', 1);
502 END IF;
503 return;
504 END IF;
505
506 -- expiration date does exist but expired
507 IF l_expiration_date < sysdate THEN
508 resultout := 'COMPLETE:EXPIRED';
509 IF l_debug_level > 0 THEN
510 oe_debug_pub.add('Leaving OE_Negotiate_WF.Check_Expiration_Date: EXPIRED', 1);
511 END IF;
512 return;
513 END IF;
514
515 --if you are here, that means expiration date exists and is in the future
516
517 l_pre_notification_percent := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'PRE_EXPIRE_TIME_PERCENT');
518
519 IF l_pre_notification_percent = 0 THEN
520 -- set the FINAL timer only is enough
521 -- this assumes expiration_date is already set to 23:59:59
522 l_final_timer := (l_expiration_date - sysdate) * 1440;
523 wf_engine.setitemattrnumber(itemtype=>itemtype,
524 itemkey=>itemkey,
525 aname=>'OFFER_FINAL_EXPIRE_TIMER',
526 avalue=>l_final_timer);
527 resultout := 'COMPLETE:NO_REMINDER';
528 IF l_debug_level > 0 THEN
529 oe_debug_pub.add('Leaving OE_Negotiate_WF.Check_Expiration_Date: NO REMINDER', 1);
530 END IF;
531 ELSIF to_char(sysdate, 'DD-MON-RRRR') = to_char(l_expiration_date, 'DD-MON-RRRR') THEN
532 -- pre notification percentage is non-zero
533 -- expiration_date is today midnight, we should send the reminder
534
535 wf_engine.SetItemAttrNumber(itemtype=>itemtype
536 , itemkey=>itemkey
537 , aname=>'OFFER_FINAL_EXPIRE_TIMER'
538 , avalue=>(l_expiration_date - sysdate) * 1440
539 );
540 resultout := 'COMPLETE:EXPIRE_TODAY';
541 IF l_debug_level > 0 THEN
542 oe_debug_pub.add('OE_Negotiate_WF.Check_Expiration_Date: EXPIRE TODAY', 1);
543 END IF;
544 ELSE --expiration is not today, i.e. it is in the future
545 --again expiration_date should already be in 23:59:59
546 l_aname(1) := 'OFFER_FINAL_EXPIRE_TIMER';
547 l_avalue(1) := Ceil((l_expiration_date - sysdate) * l_pre_notification_percent/100) * 1440;
548 l_aname(2) := 'OFFER_PRE_EXPIRE_TIMER';
549 l_avalue(2) := ((l_expiration_date - sysdate) * 1440) - l_avalue(1);
550
551 wf_engine.SetItemAttrNumberArray(itemtype=>itemtype
552 , itemkey=>itemkey
553 , aname=>l_aname
554 , avalue=>l_avalue
555 );
556 resultout := 'COMPLETE:COMPLETE';
557 END IF; -- end if of expiration date is today or future
558
559 IF l_debug_level > 0 THEN
560 oe_debug_pub.add('Leaving OE_Negotiate_WF.Check_Expiration_Date: TIMER(S) SET', 1);
561 END IF;
562 END IF; --funcmode = run
563
564 EXCEPTION
565 when others then
566 wf_core.context('OE_Negotiate_WF', 'Check_Expiration_Date', itemtype, itemkey, to_char(actid), funcmode);
567 -- start data fix project
568 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
569 p_itemtype => itemtype,
570 p_itemkey => itemkey);
571 -- end data fix project
572 oe_standard_wf.save_messages;
573 oe_standard_wf.clear_msg_context;
574 raise;
575
576 END Check_Expiration_Date;
577
578 PROCEDURE Offer_Expired(
579 itemtype in varchar2,
580 itemkey in varchar2,
581 actid in number,
582 funcmode in varchar2,
583 resultout in out nocopy varchar2)
584 IS
585 --
586 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
587 l_return_status VARCHAR2(30);
588 l_sales_document_type_code VARCHAR2(1);
589 l_sold_to_org_id NUMBER;
590 l_salesrep_id NUMBER;
591 l_salesrep VARCHAR2(240);
592 l_sold_to VARCHAR2(240);
593 l_customer_number VARCHAR2(30);
594 l_expiration_date DATE;
595 l_aname wf_engine.nametabtyp;
596 l_avaluetext wf_engine.texttabtyp;
597
598 --
599
600 BEGIN
601 IF l_debug_level > 0 THEN
602 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Offer_Expired:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
603 END IF;
604 OE_STANDARD_WF.Set_Msg_Context(actid);
605 IF (funcmode = 'RUN') then
606 OE_MSG_PUB.set_msg_context(
607 p_entity_code => 'HEADER'
608 ,p_entity_id => to_number(itemkey)
609 ,p_header_id => to_number(itemkey));
610
611 OE_ORDER_WF_UTIL.Update_Quote_Blanket(p_item_type => OE_GLOBALS.G_WFI_NGO,
612 p_item_key => itemkey,
613 p_flow_status_code => 'OFFER_EXPIRED',
614 p_open_flag => 'N',
615 x_return_status => l_return_status);
616
617 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
619 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
620 RAISE FND_API.G_EXC_ERROR;
621 END IF;
622
623 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'SALES_DOCUMENT_TYPE_CODE');
624 IF l_sales_document_type_code = 'O' THEN
625 -- ***DATE_CALCULATION***
626 select sold_to_org_id, expiration_date, salesrep_id
627 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
628 from oe_order_headers_all
629 where header_id = to_number(itemkey);
630 ELSE
631 select sold_to_org_id, expiration_date, salesrep_id
632 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
633 from oe_blanket_headers_all
634 where header_id = to_number(itemkey);
635 END IF;
636
637 l_salesrep := OE_Id_To_Value.Salesrep(p_salesrep_id=>l_salesrep_id);
638 OE_Id_To_Value.Sold_To_Org(p_sold_to_org_id=>l_sold_to_org_id, x_org=> l_sold_to, x_customer_number=>l_customer_number);
639
640
641 l_aname(1) := 'SALESPERSON';
642 l_avaluetext(1) := l_salesrep;
643 l_aname(2) := 'SOLD_TO';
644 l_avaluetext(2) := l_sold_to;
645 l_aname(3) := 'EXPIRATION_DATE';
646 --Bug 12884612: Use date mask while converting to text
647 l_avaluetext(3) := to_char(l_expiration_date,'DD-MON-RRRR');
648
649 wf_engine.SetItemAttrTextArray(itemtype=>itemtype,
650 itemkey=>itemkey,
651 aname=>l_aname,
652 avalue=>l_avaluetext);
653
654 resultout := 'COMPLETE';
655
656 END IF;
657 EXCEPTION
658 when others then
659 wf_core.context('OE_Negotiate_WF', 'Offer_Expired', itemtype, itemkey, to_char(actid), funcmode);
660 -- start data fix project
661 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
662 p_itemtype => itemtype,
663 p_itemkey => itemkey);
664 -- end data fix project
665 oe_standard_wf.save_messages;
666 oe_standard_wf.clear_msg_context;
667 raise;
668
669 END Offer_Expired;
670
671
672
673 PROCEDURE set_negotiate_hdr_descriptor (
674 document_id IN VARCHAR2,
675 display_type IN VARCHAR2,
676 document IN OUT NOCOPY VARCHAR2,
677 document_type IN OUT NOCOPY VARCHAR2
678 )
679 IS
680 --
681 l_sales_document_type_code VARCHAR2 (1);
682 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
683 l_header_id NUMBER;
684 l_order_type_id NUMBER;
685 l_transaction_type_name VARCHAR2 (300);
686 l_transaction_type_id NUMBER;
687 l_blanket_number NUMBER;
688 l_header_txt VARCHAR2 (2000);
689 l_transaction_type_txt VARCHAR2 (300);
690 l_quote_number NUMBER;
691 l_item_type VARCHAR2 (240); --ame er
692 l_item_key VARCHAR2 (100); --ame er
693 --
694 BEGIN
695 document_type := display_type;
696
697 BEGIN
698 -- if viewing method is through URL
699 -- fix bug 1332384
700 SELECT item_key, item_type
701 INTO l_item_key, l_item_type --l_header_id AME ER
702 FROM wf_item_activity_statuses
703 WHERE notification_id = TO_NUMBER (document_id);
704 IF l_debug_level > 0 THEN
705 oe_debug_pub.ADD ('item key selected is:' || l_item_key);
706 END IF ;
707 EXCEPTION
708 WHEN NO_DATA_FOUND
709 THEN
710 /* 9047023: Check details in wf history tables */
711 BEGIN
712 SELECT item_key, item_type
713 INTO l_item_key, l_item_type -- l_header_id AME ER
714 FROM wf_item_activity_statuses_h
715 WHERE notification_id = TO_NUMBER (document_id);
716 EXCEPTION
717 WHEN NO_DATA_FOUND
718 THEN
719 -- if viewing method is email
720 l_item_key := TO_NUMBER (wf_engine.setctx_itemkey);
721 -- AME ER
722 END;
723 /* 9047023: End */
724 END;
725
726 -- AME ER BEGIN
727
728
729 BEGIN
730 IF l_debug_level > 0 THEN
731 oe_debug_pub.ADD ( 'before getting attribute:');
732 END IF ;
733 IF l_item_type='OEAME'
734 THEN --AME ER
735 l_header_id :=
736 wf_engine.getitemattrnumber ('OEAME', l_item_key, 'HEADER_ID');
737
738 ELSE
739 l_header_id := TO_NUMBER (l_item_key);
740 --AME ER
741 END IF; --AME ER
742 IF l_debug_level > 0 THEN
743 oe_debug_pub.ADD ('After getting attribute:' || l_header_id);
744 END IF ;
745 IF l_header_id IS NULL
746 THEN
747 l_header_id := TO_NUMBER (l_item_key);
748 END IF;
749 EXCEPTION
750 WHEN NO_DATA_FOUND
751 THEN
752 l_header_id := TO_NUMBER (l_item_key);
753 END;
754
755 -- AME ER END
756 l_sales_document_type_code :=
757 wf_engine.getitemattrtext (oe_globals.g_wfi_ngo,
758 l_header_id,
759 'SALES_DOCUMENT_TYPE_CODE'
760 );
761
762 IF l_sales_document_type_code = 'B'
763 THEN
764 SELECT order_number
765 INTO l_blanket_number
766 FROM oe_blanket_headers_all
767 WHERE header_id = l_header_id;
768
769 fnd_message.set_name ('ONT', 'OE_WF_BLANKET_ORDER');
770 fnd_message.set_token ('BLANKET_NUMBER', TO_CHAR (l_blanket_number));
771 l_header_txt := fnd_message.get;
772 document := SUBSTRB (l_header_txt, 1, 240);
773 ELSIF l_sales_document_type_code = 'O'
774 THEN
775 SELECT oh.order_number, oh.order_type_id,
776 t.NAME
777 INTO l_quote_number, l_transaction_type_id,
778 l_transaction_type_name
779 FROM oe_order_headers_all oh, oe_transaction_types_tl t
780 WHERE header_id = l_header_id
781 AND t.LANGUAGE = USERENV ('LANG')
782 AND t.transaction_type_id = oh.order_type_id;
783
784 fnd_message.set_name ('ONT', 'OE_WF_TRANSACTION_TYPE');
785 fnd_message.set_token ('TRANSACTION_TYPE', l_transaction_type_name);
786 l_transaction_type_txt := fnd_message.get;
787 fnd_message.set_name ('ONT', 'OE_WF_QUOTE_ORDER');
788 fnd_message.set_token ('QUOTE_NUMBER', TO_CHAR (l_quote_number));
789 l_header_txt := fnd_message.get;
790 document :=
791 SUBSTRB (l_transaction_type_txt || ', ' || l_header_txt, 1, 240);
792 ELSE
793 RAISE fnd_api.g_exc_unexpected_error; -- unrecognized code
794 END IF;
795 EXCEPTION
796 WHEN OTHERS
797 THEN
798 IF oe_msg_pub.check_msg_level (oe_msg_pub.g_msg_lvl_unexp_error)
799 THEN
800 oe_msg_pub.add_exc_msg (g_pkg_name,
801 'Set_Negotiate_Hdr_Descriptor'
802 );
803 RAISE fnd_api.g_exc_unexpected_error;
804 END IF;
805 END set_negotiate_hdr_descriptor;
806
807
808
809 PROCEDURE Lost(p_header_id IN NUMBER,
810 p_entity_code IN VARCHAR2,
811 p_version_number IN NUMBER,
812 p_reason_type IN VARCHAR2,
813 p_reason_code IN VARCHAR2,
814 p_reason_comments IN VARCHAR2,
815 x_return_status OUT NOCOPY VARCHAR2)
816 IS
817 --
818 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
819 l_activity_name VARCHAR2(30);
820 l_sales_document_type VARCHAR2(30);
821 l_reason_id NUMBER;
822 l_return_status VARCHAR2(240);
823
824 --
825 BEGIN
826 OE_MSG_PUB.initialize;
827 x_return_status := FND_API.G_RET_STS_SUCCESS;
828 IF l_debug_level > 0 THEN
829 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Lost: '|| TO_CHAR (p_header_id) ,1) ;
830 END IF;
831 OE_MSG_PUB.set_msg_context(
832 p_entity_code => 'HEADER'
833 ,p_entity_id => p_header_id
834 ,p_header_id => p_header_id);
835
836 BEGIN
837 select wpa.activity_name
838 into l_activity_name
839 from wf_item_activity_statuses wias, wf_process_activities wpa
840 where item_type = OE_GLOBALS.G_WFI_NGO
841 and item_key = to_char(p_header_id)
842 and activity_status = wf_engine.eng_notified
843 and wpa.activity_name in ('SUBMIT_DRAFT_ELIGIBLE', 'NEGOTIATION_COMPLETE_ELIGIBLE')
844 and wias.process_activity = wpa.instance_id;
845
846 EXCEPTION
847 WHEN OTHERS THEN
848
849 IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN
850 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
851 ELSE -- assume blanket
852 fnd_message.set_name('ONT', 'OE_NTF_BSA');
853 END IF;
854 l_sales_document_type := fnd_message.get;
855
856 fnd_message.set_name('ONT', 'OE_WF_NO_LOST'); --flow not at notified state
857 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
858 oe_msg_pub.add;
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 IF l_debug_level > 0 THEN
861 oe_debug_pub.add('EXITING OE_Negotiate_WF.Lost WITH STATUS: '||X_RETURN_STATUS ,1);
862 END IF;
863 return;
864 END;
865 -- ok to go Lost
866 -- call reason API to capture the reason
867
868 OE_REASONS_UTIL.Apply_Reason(p_entity_code => p_entity_code,
869 p_entity_id => p_header_id,
870 p_version_number => p_version_number,
871 p_reason_type => p_reason_type,
872 p_reason_code => p_reason_code,
873 p_reason_comments => p_reason_comments,
874 x_reason_id => l_reason_id,
875 x_return_status => l_return_status);
876
877 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_NGO, to_char(p_header_id), l_activity_name, 'LOST');
878
879 IF l_debug_level > 0 THEN
880 oe_debug_pub.add('EXITING OE_Negotiate_WF.Lost normally', 1);
881 END IF;
882 EXCEPTION
883 WHEN OTHERS THEN
884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
885 IF l_debug_level > 0 THEN
886 oe_debug_pub.add('EXITING OE_Negotiate_WF.Lost WITH STATUS: '||X_RETURN_STATUS ,1);
887 END IF;
888 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
889 THEN
890 OE_MSG_PUB.Add_Exc_Msg
891 ( G_PKG_NAME,
892 'Lost'
893 );
894 END IF;
895
896
897 END Lost;
898
899
900 PROCEDURE Customer_Accepted(p_header_id IN NUMBER,
901 x_return_status OUT NOCOPY VARCHAR2)
902 IS
903 --
904 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
905 l_activity_name VARCHAR2(30);
906 l_sales_document_type_code VARCHAR2(1);
907 l_sales_document_type VARCHAR2(30);
908 l_response VARCHAR2(30);
909 l_entity_code VARCHAR2(30);
910 l_wf_item_count NUMBER;
911 l_so_count NUMBER;
912 l_bsa_count NUMBER;
913 --
914 l_customer_acceptance VARCHAR2(30) := 'CUSTOMER_ACCEPTANCE';
915 BEGIN
916 OE_MSG_PUB.initialize;
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918 IF l_debug_level > 0 THEN
919 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Customer_Accepted: '|| TO_CHAR (p_header_id) ,1) ;
920 END IF;
921
922 OE_MSG_PUB.set_msg_context(
923 p_entity_code => 'HEADER'
924 ,p_entity_id => p_header_id
925 ,p_header_id => p_header_id);
926
927
928 BEGIN
929 -- Bug3435165
930 select count(1)
931 into l_wf_item_count
932 from wf_items
933 where item_type = 'OENH'
934 and item_key = To_Char(p_header_id); -- Added to char function for bug 11793697
935
936 IF l_wf_item_count = 0 THEN --we are in fulfillment phase and it has no nego phase
937 select count(1)
938 into l_so_count
939 from oe_order_headers_all
940 where header_id = p_header_id;
941
942 IF l_so_count > 0 THEN
943 l_sales_document_type_code := 'O';
944 raise FND_API.G_EXC_ERROR;
945 ELSE
946 select count(1)
947 into l_bsa_count
948 from oe_blanket_headers_all
949 where header_id = p_header_id;
950
951 IF l_bsa_count > 0 THEN
952 l_sales_document_type_code := 'B';
953 raise FND_API.G_EXC_ERROR;
954 END IF;
955 END IF;
956 END IF;
957 -- END Bug3435165
958
959 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, p_header_id, 'SALES_DOCUMENT_TYPE_CODE');
960 select wpa.activity_name
961 into l_activity_name
962 from wf_item_activity_statuses wias, wf_process_activities wpa
963 where item_type = OE_GLOBALS.G_WFI_NGO
964 and item_key = to_char(p_header_id)
965 and activity_status = wf_engine.eng_notified
966 and wpa.activity_name = l_customer_acceptance
967 and wias.process_activity = wpa.instance_id;
968
969 EXCEPTION
970 WHEN OTHERS THEN
971 IF l_sales_document_type_code = 'O' THEN
972 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
973 ELSE -- assume blanket
974 fnd_message.set_name('ONT', 'OE_NTF_BSA');
975 END IF;
976 l_sales_document_type := fnd_message.get;
977
978 fnd_message.set_name('ONT', 'OE_WF_NO_CUST_ACCEPTED'); --flow not at right state
979 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
980 oe_msg_pub.add;
981 x_return_status := FND_API.G_RET_STS_ERROR;
982 IF l_debug_level > 0 THEN
983 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Accepted WITH STATUS: '||X_RETURN_STATUS ,1);
984 END IF;
985 return;
986 END;
987 -- ok to go Accept
988 IF l_sales_document_type_code = 'O' THEN
989 l_entity_code := 'HEADER';
990 ELSE
991 l_entity_code := 'BLANKET_HEADER';
992 END IF;
993
994 OE_MSG_PUB.set_msg_context(
995 p_entity_code => l_entity_code
996 ,p_entity_id => p_header_id
997 ,p_header_id => p_header_id
998 ,p_line_id => null
999 ,p_orig_sys_document_ref => null
1000 ,p_orig_sys_document_line_ref => null
1001 ,p_change_sequence => null
1002 ,p_source_document_id => null
1003 ,p_source_document_line_id => null
1004 ,p_order_source_id => null
1005 ,p_source_document_type_id => null);
1006
1007
1008 WF_ENGINE.CompleteActivityInternalName(itemtype => OE_GLOBALS.G_WFI_NGO,
1009 itemkey => to_char(p_header_id),
1010 activity => l_activity_name,
1011 result => 'ACCEPT');
1012 IF l_debug_level > 0 THEN
1013 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Accepted normally', 1);
1014 END IF;
1015 EXCEPTION
1016
1017 WHEN OTHERS THEN
1018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1019 IF l_debug_level > 0 THEN
1020 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Accepted WITH STATUS: '||X_RETURN_STATUS ,1);
1021 END IF;
1022 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1023 THEN
1024 OE_MSG_PUB.Add_Exc_Msg
1025 ( G_PKG_NAME,
1026 'Customer_Accepted'
1027 );
1028 END IF;
1029
1030
1031
1032 END Customer_Accepted;
1033
1034 PROCEDURE Customer_Rejected(p_header_id IN NUMBER,
1035 p_entity_code IN VARCHAR2,
1036 p_version_number IN NUMBER,
1037 p_reason_type IN VARCHAR2,
1038 p_reason_code IN VARCHAR2,
1039 p_reason_comments IN VARCHAR2,
1040 x_return_status OUT NOCOPY VARCHAR2)
1041 IS
1042 --
1043 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1044 l_activity_name VARCHAR2(30);
1045 l_sales_document_type VARCHAR2(30);
1046 l_response VARCHAR2(30);
1047 l_reason_id NUMBER;
1048 l_return_status VARCHAR2(240);
1049
1050 --
1051 l_customer_acceptance VARCHAR2(30) := 'CUSTOMER_ACCEPTANCE';
1052 --
1053 BEGIN
1054 OE_MSG_PUB.initialize;
1055 x_return_status := FND_API.G_RET_STS_SUCCESS;
1056 IF l_debug_level > 0 THEN
1057 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Customer_Rejected: '|| TO_CHAR (p_header_id) ,1) ;
1058 END IF;
1059 OE_MSG_PUB.set_msg_context(
1060 p_entity_code => 'HEADER'
1061 ,p_entity_id => p_header_id
1062 ,p_header_id => p_header_id);
1063
1064 BEGIN
1065 select wpa.activity_name
1066 into l_activity_name
1067 from wf_item_activity_statuses wias, wf_process_activities wpa
1068 where item_type = OE_GLOBALS.G_WFI_NGO
1069 and item_key = to_char(p_header_id)
1070 and activity_status = wf_engine.eng_notified
1071 and wpa.activity_name = l_customer_acceptance
1072 and wias.process_activity = wpa.instance_id;
1073
1074 EXCEPTION
1075 WHEN OTHERS THEN
1076
1077 IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN
1078 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
1079 ELSE -- assume blanket
1080 fnd_message.set_name('ONT', 'OE_NTF_BSA');
1081 END IF;
1082 l_sales_document_type := fnd_message.get;
1083
1084 fnd_message.set_name('ONT', 'OE_WF_NO_CUST_REJECTED'); --flow not at right state
1085 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
1086 oe_msg_pub.add;
1087 x_return_status := FND_API.G_RET_STS_ERROR;
1088 IF l_debug_level > 0 THEN
1089 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Rejected WITH STATUS: '||X_RETURN_STATUS ,1);
1090 END IF;
1091 return;
1092 END;
1093 -- ok to go Reject
1094
1095 OE_REASONS_UTIL.Apply_Reason(p_entity_code => p_entity_code,
1096 p_entity_id => p_header_id,
1097 p_version_number => p_version_number,
1098 p_reason_type => p_reason_type,
1099 p_reason_code => p_reason_code,
1100 p_reason_comments => p_reason_comments,
1101 x_reason_id => l_reason_id,
1102 x_return_status => l_return_status);
1103
1104
1105 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_NGO, to_char(p_header_id), l_activity_name, 'REJECT');
1106 IF l_debug_level > 0 THEN
1107 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Rejected normally', 1);
1108 END IF;
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1112 IF l_debug_level > 0 THEN
1113 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Rejected WITH STATUS: '||X_RETURN_STATUS ,1);
1114 END IF;
1115 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1116 THEN
1117 OE_MSG_PUB.Add_Exc_Msg
1118 ( G_PKG_NAME,
1119 'Customer_Rejected'
1120 );
1121 END IF;
1122
1123 END Customer_Rejected;
1124
1125
1126
1127 PROCEDURE Offer_Date_Changed(p_header_id NUMBER,
1128 x_return_status OUT NOCOPY VARCHAR2)
1129 IS
1130 --
1131 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1132 l_activity_name VARCHAR2(30);
1133 l_sales_document_type_code VARCHAR2(1);
1134 l_sales_document_type VARCHAR2(30);
1135 --
1136 BEGIN
1137 OE_MSG_PUB.initialize;
1138 x_return_status := FND_API.G_RET_STS_SUCCESS;
1139 IF l_debug_level > 0 THEN
1140 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Offer_Date_Changed: '|| TO_CHAR (p_header_id) ,1) ;
1141 END IF;
1142 OE_MSG_PUB.set_msg_context(
1143 p_entity_code => 'HEADER'
1144 ,p_entity_id => p_header_id
1145 ,p_header_id => p_header_id);
1146
1147 BEGIN
1148 select wpa.activity_name
1149 into l_activity_name
1150 from wf_item_activity_statuses wias, wf_process_activities wpa
1151 where item_type = OE_GLOBALS.G_WFI_NGO
1152 and item_key = to_char(p_header_id)
1153 and activity_status = wf_engine.eng_notified
1154 and wpa.activity_name in ('WAIT_FOR_EXPIRATION', 'WAIT_FOR_FINAL_EXPIRATION')
1155 and wias.process_activity = wpa.instance_id;
1156
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, p_header_id, 'SALES_DOCUMENT_TYPE_CODE');
1160 IF l_sales_document_type_code = 'O' THEN
1161 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
1162 ELSE -- assume blanket
1163 fnd_message.set_name('ONT', 'OE_NTF_BSA');
1164 END IF;
1165 l_sales_document_type := fnd_message.get;
1166 fnd_message.set_name('ONT', 'OE_WF_NO_OFFER_DATE_CHANGE'); --flow not at right state
1167 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
1168 oe_msg_pub.add;
1169 x_return_status := FND_API.G_RET_STS_ERROR;
1170 IF l_debug_level > 0 THEN
1171 oe_debug_pub.add('EXITING OE_Negotiate_WF.Offer_Date_Changed WITH STATUS: '||X_RETURN_STATUS ,1);
1172 END IF;
1173 return;
1174 END;
1175 -- ok to go date changed
1176 IF l_debug_level > 0 THEN
1177 oe_debug_pub.add('Calling WF_ENGINE to completeactivity' ,3);
1178 END IF;
1179 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_NGO, to_char(p_header_id), l_activity_name, 'DATE_CHANGED');
1180 IF l_debug_level > 0 THEN
1181 oe_debug_pub.add('EXITING OE_Negotiate_WF.Offer_Date_Changed normally', 1);
1182 END IF;
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1186 IF l_debug_level > 0 THEN
1187 oe_debug_pub.add('EXITING OE_Negotiate_WF.Offer_Date_Changed WITH STATUS: '||X_RETURN_STATUS ,1);
1188 END IF;
1189 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1190 THEN
1191 OE_MSG_PUB.Add_Exc_Msg
1192 ( G_PKG_NAME,
1193 'Offer_Date_Changed'
1194 );
1195 END IF;
1196
1197 END Offer_Date_Changed;
1198
1199
1200 PROCEDURE Submit_Draft(p_header_id NUMBER,
1201 x_return_status OUT NOCOPY VARCHAR2)
1202 IS
1203 --
1204 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1205 l_activity_name VARCHAR2(30);
1206 l_sales_document_type_code VARCHAR2(1);
1207 l_sales_document_type VARCHAR2(30);
1208 l_entity_code VARCHAR2(30);
1209 l_wf_item_count NUMBER;
1210 l_so_count NUMBER;
1211 l_bsa_count NUMBER;
1212 --
1213 l_submit_draft_eligible VARCHAR2(30) := 'SUBMIT_DRAFT_ELIGIBLE';
1214
1215 --
1216 BEGIN
1217 -- OE_MSG_PUB.initialize; commented out for 4671489
1218 x_return_status := FND_API.G_RET_STS_SUCCESS;
1219 IF l_debug_level > 0 THEN
1220 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Submit_Draft: '|| TO_CHAR (p_header_id) ,1) ;
1221 END IF;
1222
1223 BEGIN
1224 --Bug3435165
1225 select count(1)
1226 into l_wf_item_count
1227 from wf_items
1228 where item_type = 'OENH'
1229 and item_key = to_char(p_header_id); -- Bug 9209740, p_header_id conversion to char.
1230
1231 IF l_wf_item_count = 0 THEN --we are in fulfillment phase and it has no nego phase
1232 -- should be a sales order, as BSA UI won't call this API in fulfillment phase, but
1233 -- double check to confirm
1234 select count(1)
1235 into l_so_count
1236 from oe_order_headers_all
1237 where header_id = p_header_id;
1238
1239 IF l_so_count > 0 THEN
1240 l_sales_document_type_code := 'O';
1241 raise FND_API.G_EXC_ERROR;
1242 ELSE --should never come here, given how BSA is coded now
1243 select count(1)
1244 into l_bsa_count
1245 from oe_blanket_headers_all
1246 where header_id = p_header_id;
1247
1248 IF l_bsa_count > 0 THEN
1249 l_sales_document_type_code := 'B';
1250 raise FND_API.G_EXC_ERROR;
1251 END IF;
1252 END IF;
1253 END IF;
1254 -- END Bug3435165
1255
1256 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, p_header_id, 'SALES_DOCUMENT_TYPE_CODE');
1257 IF l_sales_document_type_code = 'O' THEN
1258 l_entity_code := 'HEADER';
1259 ELSE
1260 l_entity_code := 'BLANKET_HEADER';
1261 END IF;
1262
1263 OE_MSG_PUB.set_msg_context(
1264 p_entity_code => l_entity_code
1265 ,p_entity_id => p_header_id
1266 ,p_header_id => p_header_id
1267 ,p_line_id => null
1268 ,p_orig_sys_document_ref => null
1269 ,p_orig_sys_document_line_ref => null
1270 ,p_change_sequence => null
1271 ,p_source_document_id => null
1272 ,p_source_document_line_id => null
1273 ,p_order_source_id => null
1274 ,p_source_document_type_id => null);
1275
1276 select wpa.activity_name
1277 into l_activity_name
1278 from wf_item_activity_statuses wias, wf_process_activities wpa
1279 where item_type = OE_GLOBALS.G_WFI_NGO
1280 and item_key = to_char(p_header_id)
1281 and activity_status = wf_engine.eng_notified
1282 and wpa.activity_name = l_submit_draft_eligible
1283 and wias.process_activity = wpa.instance_id;
1284
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 IF l_sales_document_type_code = 'O' THEN
1288 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
1289 ELSE -- assume blanket
1290 fnd_message.set_name('ONT', 'OE_NTF_BSA');
1291 END IF;
1292 l_sales_document_type := fnd_message.get;
1293
1294 fnd_message.set_name('ONT', 'OE_WF_NO_SUBMIT_DRAFT'); --flow not at right state
1295 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
1296 oe_msg_pub.add;
1297 x_return_status := FND_API.G_RET_STS_ERROR;
1298 IF l_debug_level > 0 THEN
1299 oe_debug_pub.add('EXITING OE_Negotiate_WF.Submit_Draft WITH STATUS: '||X_RETURN_STATUS ,1);
1300 END IF;
1301 return;
1302 END;
1303 -- ok to go Submit Draft
1304 WF_ENGINE.CompleteActivityInternalName(itemtype => OE_GLOBALS.G_WFI_NGO,
1305 itemkey => to_char(p_header_id),
1306 activity => l_activity_name,
1307 result => 'COMPLETE');
1308 IF l_debug_level > 0 THEN
1309 oe_debug_pub.add('EXITING OE_Negotiate_WF.Submit_Draft normally', 1);
1310 END IF;
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1314 IF l_debug_level > 0 THEN
1315 oe_debug_pub.add('EXITING OE_Negotiate_WF.Submit_Draft WITH STATUS: '||X_RETURN_STATUS ,1);
1316 END IF;
1317 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1318 THEN
1319 OE_MSG_PUB.Add_Exc_Msg
1320 ( G_PKG_NAME,
1321 'Submit_Draft'
1322 );
1323 END IF;
1324
1325
1326
1327 END Submit_Draft;
1328
1329 PROCEDURE set_header_attributes_internal(p_header_id IN NUMBER)
1330 IS
1331 --
1332 l_sales_document_type_code VARCHAR2(1);
1333 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1334 l_sold_to_org_id NUMBER;
1335 l_salesrep_id NUMBER;
1336 l_salesrep VARCHAR2(240);
1337 l_sold_to VARCHAR2(240);
1338 l_customer_number VARCHAR2(30);
1339 l_expiration_date DATE;
1340 l_aname wf_engine.nametabtyp;
1341 l_avaluetext wf_engine.texttabtyp;
1342 --
1343 BEGIN
1344 IF l_debug_level > 0 THEN
1345 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Set_Header_Attributes_Internal:'||To_char(p_header_id) ,1 ) ;
1346 END IF;
1347
1348 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, To_char(p_header_id), 'SALES_DOCUMENT_TYPE_CODE');
1349 IF l_sales_document_type_code = 'O' THEN
1350
1351 select sold_to_org_id, expiration_date, salesrep_id
1352 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
1353 from oe_order_headers_all
1354 where header_id = p_header_id;
1355 ELSE
1356 select sold_to_org_id, expiration_date, salesrep_id
1357 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
1358 from oe_blanket_headers_all
1359 where header_id = p_header_id;
1360 END IF;
1361
1362 l_salesrep := OE_Id_To_Value.Salesrep(p_salesrep_id=>l_salesrep_id);
1363 OE_Id_To_Value.Sold_To_Org(p_sold_to_org_id=>l_sold_to_org_id, x_org=> l_sold_to, x_customer_number=>l_customer_number);
1364 l_aname(1) := 'SALESPERSON';
1365 l_avaluetext(1) := l_salesrep;
1366 l_aname(2) := 'SOLD_TO';
1367 l_avaluetext(2) := l_sold_to;
1368 l_aname(3) := 'EXPIRATION_DATE';
1369 --Bug 12884612: Use date mask while converting to text
1370 l_avaluetext(3) := to_char(l_expiration_date,'DD-MON-RRRR');
1371
1372 wf_engine.SetItemAttrTextArray(itemtype=>OE_GLOBALS.G_WFI_NGO,
1373 itemkey=>To_char(p_header_id),
1374 aname=>l_aname,
1375 avalue=>l_avaluetext);
1376
1377 -- end setting item attribute for WF header attributes
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1381 THEN
1382 OE_MSG_PUB.Add_Exc_Msg
1383 (G_PKG_NAME
1384 , 'Set_Header_Attributes_Internal'
1385 );
1386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1387 END IF;
1388
1389 END Set_Header_Attributes_Internal;
1390
1391 PROCEDURE Set_Header_Attributes(
1392 itemtype in varchar2,
1393 itemkey in varchar2,
1394 actid in number,
1395 funcmode in varchar2,
1396 resultout in out nocopy varchar2)
1397 IS
1398 --
1399 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1400 BEGIN
1401 IF l_debug_level > 0 THEN
1402 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Set_Header_Attributes:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
1403 END IF;
1404 OE_STANDARD_WF.Set_Msg_Context(actid);
1405 IF (funcmode = 'RUN') THEN
1406 set_header_attributes_internal(To_number(itemkey));
1407 resultout := 'COMPLETE';
1408 END IF;
1409 EXCEPTION
1410 when others then
1411 wf_core.context('OE_Negotiate_WF', 'Set_Header_Attributes', itemtype, itemkey, to_char(actid), funcmode);
1412 -- start data fix project
1413 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1414 p_itemtype => itemtype,
1415 p_itemkey => itemkey);
1416 -- end data fix project
1417 oe_standard_wf.save_messages;
1418 oe_standard_wf.clear_msg_context;
1419 raise;
1420
1421 END Set_Header_Attributes;
1422
1423 PROCEDURE set_final_expiration_date(
1424 itemtype in varchar2,
1425 itemkey in varchar2,
1426 actid in number,
1427 funcmode in varchar2,
1428 resultout in out nocopy varchar2)
1429 IS
1430 --
1431 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1432 l_final_timer NUMBER;
1433 l_sales_document_type_code VARCHAR2(1);
1434 l_expiration_date DATE;
1435 l_from_role VARCHAR2(200);
1436 --
1437
1438 BEGIN
1439 IF l_debug_level > 0 THEN
1440 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Set_Final_Expiration_Date:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
1441 END IF;
1442 OE_STANDARD_WF.Set_Msg_Context(actid);
1443 IF (funcmode = 'RUN') then
1444 OE_MSG_PUB.set_msg_context(
1445 p_entity_code => 'HEADER'
1446 ,p_entity_id => to_number(itemkey)
1447 ,p_header_id => to_number(itemkey));
1448
1449 set_header_attributes_internal(To_number(itemkey));
1450 IF l_debug_level > 0 THEN
1451 oe_debug_pub.add( 'Done setting header attributes',1);
1452 END IF;
1453
1454 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'SALES_DOCUMENT_TYPE_CODE');
1455 IF l_sales_document_type_code = 'O' THEN
1456 select expiration_date
1457 into l_expiration_date
1458 from oe_order_headers_all
1459 where header_id = to_number(itemkey);
1460 ELSE
1461 -- even though there is no offer expiration date for blanket for now
1462 -- we will still fetch it for the future
1463 select expiration_date
1464 into l_expiration_date
1465 from oe_blanket_headers_all
1466 where header_id = to_number(itemkey);
1467 END IF;
1468
1469 IF l_expiration_date IS NULL THEN
1470 -- should not be coming here
1471 RETURN;
1472 END IF;
1473
1474 l_final_timer := (l_expiration_date - Sysdate) * 1440;
1475 IF l_final_timer > 0 THEN
1476 wf_engine.setitemattrnumber(itemtype=>itemtype,
1477 itemkey=>itemkey,
1478 aname=>'OFFER_FINAL_EXPIRE_TIMER',
1479 avalue=>l_final_timer);
1480 /*starting the fix for bug 9069528 */
1481 l_from_role:=wf_engine.GetItemAttrText(itemtype,itemkey,'NOTIFICATION_FROM_ROLE');
1482 IF NOT WF_DIRECTORY.UserActive(l_from_role) THEN
1483 l_from_role := fnd_profile.Value('OE_NOTIFICATION_APPROVER');
1484 IF l_from_role IS null or NOT WF_DIRECTORY.UserActive(l_from_role) then
1485 l_from_role := 'SYSADMIN';
1486 END IF;
1487 END IF;
1488 wf_engine.setItemAttrText(itemtype,itemkey,'NOTIFICATION_FROM_ROLE',l_from_role);
1489 /*ending the fix for bug 9069528 */
1490 resultout := 'COMPLETE';
1491 ELSE
1492 resultout := 'COMPLETE:EXPIRED';
1493 END IF;
1494
1495 IF l_debug_level > 0 THEN
1496 oe_debug_pub.add('Leaving OE_Negotiate_WF.Set_Final_Expiration_Date', 1);
1497 END IF;
1498 END IF;
1499 EXCEPTION
1500 when others then
1501 wf_core.context('OE_Negotiate_WF', 'Set_Final_Expiration_Date', itemtype, itemkey, to_char(actid), funcmode);
1502 -- start data fix project
1503 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1504 p_itemtype => itemtype,
1505 p_itemkey => itemkey);
1506 -- end data fix project
1507 oe_standard_wf.save_messages;
1508 oe_standard_wf.clear_msg_context;
1509 raise;
1510
1511 END set_final_expiration_date;
1512
1513
1514 FUNCTION At_Customer_Acceptance(p_header_id NUMBER)
1515 RETURN Boolean
1516 IS
1517
1518 --
1519 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1520 l_activity_name VARCHAR2(30);
1521 --
1522 l_customer_acceptance VARCHAR2(30) := 'CUSTOMER_ACCEPTANCE';
1523 --
1524 BEGIN
1525
1526 IF l_debug_level > 0 THEN
1527 oe_debug_pub.add( 'ENTER OE_Negotiate_WF.At_Customer_Acceptance');
1528 END IF;
1529
1530 select wpa.activity_name
1531 into l_activity_name
1532 from wf_item_activity_statuses wias, wf_process_activities wpa
1533 where item_type = OE_GLOBALS.G_WFI_NGO
1534 and item_key = to_char(p_header_id)
1535 and activity_status = wf_engine.eng_notified
1536 and wpa.activity_name = l_customer_acceptance
1537 and wias.process_activity = wpa.instance_id;
1538
1539 RETURN TRUE;
1540
1541 EXCEPTION
1542 WHEN OTHERS THEN
1543 -- not at customer_accpetance
1544 RETURN FALSE;
1545
1546 END At_Customer_Acceptance;
1547
1548
1549 END OE_Negotiate_WF;