[Home] [Help]
PACKAGE BODY: APPS.OE_NEGOTIATE_WF
Source
1 PACKAGE BODY OE_Negotiate_WF as
2 /* $Header: OEXWNEGB.pls 120.2 2006/03/07 15:46:42 mmisra noship $ */
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 l_avaluetext(3) := l_expiration_date;
647
648 wf_engine.SetItemAttrTextArray(itemtype=>itemtype,
649 itemkey=>itemkey,
650 aname=>l_aname,
651 avalue=>l_avaluetext);
652
653 resultout := 'COMPLETE';
654
655 END IF;
656 EXCEPTION
657 when others then
658 wf_core.context('OE_Negotiate_WF', 'Offer_Expired', itemtype, itemkey, to_char(actid), funcmode);
659 -- start data fix project
660 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
661 p_itemtype => itemtype,
662 p_itemkey => itemkey);
663 -- end data fix project
664 oe_standard_wf.save_messages;
665 oe_standard_wf.clear_msg_context;
666 raise;
667
668 END Offer_Expired;
669
670
671
672 PROCEDURE Set_Negotiate_Hdr_Descriptor(
673 document_id IN VARCHAR2,
674 display_type IN VARCHAR2,
675 document IN OUT NOCOPY VARCHAR2,
676 document_type IN OUT NOCOPY VARCHAR2)
677 IS
678 --
679 l_sales_document_type_code VARCHAR2(1);
680 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
681 l_header_id NUMBER;
682 l_order_type_id NUMBER;
683 l_transaction_type_name VARCHAR2(300);
684 l_transaction_type_id NUMBER;
685 l_blanket_number NUMBER;
686 l_header_txt VARCHAR2(2000);
687 l_transaction_type_txt VARCHAR2(300);
688 l_quote_number NUMBER;
689 --
690 BEGIN
691
692 document_type := display_type;
693 BEGIN
694 -- if viewing method is through URL
695 -- fix bug 1332384
696 SELECT item_key
697 INTO l_header_id
698 FROM wf_item_activity_statuses
699 where notification_id = to_number(document_id);
700 EXCEPTION
701 WHEN NO_DATA_FOUND THEN
702 -- if viewing method is email
703 l_header_id := to_number(wf_engine.setctx_itemkey);
704 END;
705
706 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, l_header_id, 'SALES_DOCUMENT_TYPE_CODE');
707 IF l_sales_document_type_code = 'B' THEN
708 SELECT order_number
709 INTO l_blanket_number
710 FROM oe_blanket_headers_all
711 WHERE header_id = l_header_id;
712
713 fnd_message.set_name('ONT', 'OE_WF_BLANKET_ORDER');
714 fnd_message.set_token('BLANKET_NUMBER', to_char(l_blanket_number));
715 l_header_txt := fnd_message.get;
716 document := substrb(l_header_txt, 1, 240);
717 ELSIF l_sales_document_type_code = 'O' THEN
718 SELECT oh.order_number, oh.order_type_id, t.name
719 INTO l_quote_number, l_transaction_type_id, l_transaction_type_name
720 FROM oe_order_headers_all oh, oe_transaction_types_tl t
721 WHERE header_id = l_header_id
722 AND t.language = userenv('LANG')
723 AND t.transaction_type_id = oh.order_type_id;
724
725 fnd_message.set_name('ONT', 'OE_WF_TRANSACTION_TYPE');
726 fnd_message.set_token('TRANSACTION_TYPE', l_transaction_type_name);
727 l_transaction_type_txt := fnd_message.get;
728
729 fnd_message.set_name('ONT', 'OE_WF_QUOTE_ORDER');
730 fnd_message.set_token('QUOTE_NUMBER', to_char(l_quote_number));
731 l_header_txt := fnd_message.get;
732
733 document := substrb(l_transaction_type_txt || ', ' || l_header_txt, 1, 240);
734
735 ELSE
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- unrecognized code
737 END IF;
738
739 EXCEPTION
740 WHEN OTHERS THEN
741 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
742 THEN
743 OE_MSG_PUB.Add_Exc_Msg
744 (G_PKG_NAME
745 , 'Set_Negotiate_Hdr_Descriptor'
746 );
747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
748 END IF;
749
750 END Set_Negotiate_Hdr_Descriptor;
751
752
753
754 PROCEDURE Lost(p_header_id IN NUMBER,
755 p_entity_code IN VARCHAR2,
756 p_version_number IN NUMBER,
757 p_reason_type IN VARCHAR2,
758 p_reason_code IN VARCHAR2,
759 p_reason_comments IN VARCHAR2,
760 x_return_status OUT NOCOPY VARCHAR2)
761 IS
762 --
763 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
764 l_activity_name VARCHAR2(30);
765 l_sales_document_type VARCHAR2(30);
766 l_reason_id NUMBER;
767 l_return_status VARCHAR2(240);
768
769 --
770 BEGIN
771 OE_MSG_PUB.initialize;
772 x_return_status := FND_API.G_RET_STS_SUCCESS;
773 IF l_debug_level > 0 THEN
774 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Lost: '|| TO_CHAR (p_header_id) ,1) ;
775 END IF;
776 OE_MSG_PUB.set_msg_context(
777 p_entity_code => 'HEADER'
778 ,p_entity_id => p_header_id
779 ,p_header_id => p_header_id);
780
781 BEGIN
782 select wpa.activity_name
783 into l_activity_name
784 from wf_item_activity_statuses wias, wf_process_activities wpa
785 where item_type = OE_GLOBALS.G_WFI_NGO
786 and item_key = to_char(p_header_id)
787 and activity_status = wf_engine.eng_notified
788 and wpa.activity_name in ('SUBMIT_DRAFT_ELIGIBLE', 'NEGOTIATION_COMPLETE_ELIGIBLE')
789 and wias.process_activity = wpa.instance_id;
790
791 EXCEPTION
792 WHEN OTHERS THEN
793
794 IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN
795 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
796 ELSE -- assume blanket
797 fnd_message.set_name('ONT', 'OE_NTF_BSA');
798 END IF;
799 l_sales_document_type := fnd_message.get;
800
801 fnd_message.set_name('ONT', 'OE_WF_NO_LOST'); --flow not at notified state
802 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
803 oe_msg_pub.add;
804 x_return_status := FND_API.G_RET_STS_ERROR;
805 IF l_debug_level > 0 THEN
806 oe_debug_pub.add('EXITING OE_Negotiate_WF.Lost WITH STATUS: '||X_RETURN_STATUS ,1);
807 END IF;
808 return;
809 END;
810 -- ok to go Lost
811 -- call reason API to capture the reason
812
813 OE_REASONS_UTIL.Apply_Reason(p_entity_code => p_entity_code,
814 p_entity_id => p_header_id,
815 p_version_number => p_version_number,
816 p_reason_type => p_reason_type,
817 p_reason_code => p_reason_code,
818 p_reason_comments => p_reason_comments,
819 x_reason_id => l_reason_id,
820 x_return_status => l_return_status);
821
822 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_NGO, to_char(p_header_id), l_activity_name, 'LOST');
823
824 IF l_debug_level > 0 THEN
825 oe_debug_pub.add('EXITING OE_Negotiate_WF.Lost normally', 1);
826 END IF;
827 EXCEPTION
828 WHEN OTHERS THEN
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
830 IF l_debug_level > 0 THEN
831 oe_debug_pub.add('EXITING OE_Negotiate_WF.Lost WITH STATUS: '||X_RETURN_STATUS ,1);
832 END IF;
833 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
834 THEN
835 OE_MSG_PUB.Add_Exc_Msg
836 ( G_PKG_NAME,
837 'Lost'
838 );
839 END IF;
840
841
842 END Lost;
843
844
845 PROCEDURE Customer_Accepted(p_header_id IN NUMBER,
846 x_return_status OUT NOCOPY VARCHAR2)
847 IS
848 --
849 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
850 l_activity_name VARCHAR2(30);
851 l_sales_document_type_code VARCHAR2(1);
852 l_sales_document_type VARCHAR2(30);
853 l_response VARCHAR2(30);
854 l_entity_code VARCHAR2(30);
855 l_wf_item_count NUMBER;
856 l_so_count NUMBER;
857 l_bsa_count NUMBER;
858 --
859 l_customer_acceptance VARCHAR2(30) := 'CUSTOMER_ACCEPTANCE';
860 BEGIN
861 OE_MSG_PUB.initialize;
862 x_return_status := FND_API.G_RET_STS_SUCCESS;
863 IF l_debug_level > 0 THEN
864 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Customer_Accepted: '|| TO_CHAR (p_header_id) ,1) ;
865 END IF;
866
867 OE_MSG_PUB.set_msg_context(
868 p_entity_code => 'HEADER'
869 ,p_entity_id => p_header_id
870 ,p_header_id => p_header_id);
871
872
873 BEGIN
874 -- Bug3435165
875 select count(1)
876 into l_wf_item_count
877 from wf_items
878 where item_type = 'OENH'
879 and item_key = p_header_id;
880
881 IF l_wf_item_count = 0 THEN --we are in fulfillment phase and it has no nego phase
882 select count(1)
883 into l_so_count
884 from oe_order_headers_all
885 where header_id = p_header_id;
886
887 IF l_so_count > 0 THEN
888 l_sales_document_type_code := 'O';
889 raise FND_API.G_EXC_ERROR;
890 ELSE
891 select count(1)
892 into l_bsa_count
893 from oe_blanket_headers_all
894 where header_id = p_header_id;
895
896 IF l_bsa_count > 0 THEN
897 l_sales_document_type_code := 'B';
898 raise FND_API.G_EXC_ERROR;
899 END IF;
900 END IF;
901 END IF;
902 -- END Bug3435165
903
904 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, p_header_id, 'SALES_DOCUMENT_TYPE_CODE');
905 select wpa.activity_name
906 into l_activity_name
907 from wf_item_activity_statuses wias, wf_process_activities wpa
908 where item_type = OE_GLOBALS.G_WFI_NGO
909 and item_key = to_char(p_header_id)
910 and activity_status = wf_engine.eng_notified
911 and wpa.activity_name = l_customer_acceptance
912 and wias.process_activity = wpa.instance_id;
913
914 EXCEPTION
915 WHEN OTHERS THEN
916 IF l_sales_document_type_code = 'O' THEN
917 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
918 ELSE -- assume blanket
919 fnd_message.set_name('ONT', 'OE_NTF_BSA');
920 END IF;
921 l_sales_document_type := fnd_message.get;
922
923 fnd_message.set_name('ONT', 'OE_WF_NO_CUST_ACCEPTED'); --flow not at right state
924 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
925 oe_msg_pub.add;
926 x_return_status := FND_API.G_RET_STS_ERROR;
927 IF l_debug_level > 0 THEN
928 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Accepted WITH STATUS: '||X_RETURN_STATUS ,1);
929 END IF;
930 return;
931 END;
932 -- ok to go Accept
933 IF l_sales_document_type_code = 'O' THEN
934 l_entity_code := 'HEADER';
935 ELSE
936 l_entity_code := 'BLANKET_HEADER';
937 END IF;
938
939 OE_MSG_PUB.set_msg_context(
940 p_entity_code => l_entity_code
941 ,p_entity_id => p_header_id
942 ,p_header_id => p_header_id
943 ,p_line_id => null
944 ,p_orig_sys_document_ref => null
945 ,p_orig_sys_document_line_ref => null
946 ,p_change_sequence => null
947 ,p_source_document_id => null
948 ,p_source_document_line_id => null
949 ,p_order_source_id => null
950 ,p_source_document_type_id => null);
951
952
953 WF_ENGINE.CompleteActivityInternalName(itemtype => OE_GLOBALS.G_WFI_NGO,
954 itemkey => to_char(p_header_id),
955 activity => l_activity_name,
956 result => 'ACCEPT');
957 IF l_debug_level > 0 THEN
958 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Accepted normally', 1);
959 END IF;
960 EXCEPTION
961
962 WHEN OTHERS THEN
963 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
964 IF l_debug_level > 0 THEN
965 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Accepted WITH STATUS: '||X_RETURN_STATUS ,1);
966 END IF;
967 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
968 THEN
969 OE_MSG_PUB.Add_Exc_Msg
970 ( G_PKG_NAME,
971 'Customer_Accepted'
972 );
973 END IF;
974
975
976
977 END Customer_Accepted;
978
979 PROCEDURE Customer_Rejected(p_header_id IN NUMBER,
980 p_entity_code IN VARCHAR2,
981 p_version_number IN NUMBER,
982 p_reason_type IN VARCHAR2,
983 p_reason_code IN VARCHAR2,
984 p_reason_comments IN VARCHAR2,
985 x_return_status OUT NOCOPY VARCHAR2)
986 IS
987 --
988 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
989 l_activity_name VARCHAR2(30);
990 l_sales_document_type VARCHAR2(30);
991 l_response VARCHAR2(30);
992 l_reason_id NUMBER;
993 l_return_status VARCHAR2(240);
994
995 --
996 l_customer_acceptance VARCHAR2(30) := 'CUSTOMER_ACCEPTANCE';
997 --
998 BEGIN
999 OE_MSG_PUB.initialize;
1000 x_return_status := FND_API.G_RET_STS_SUCCESS;
1001 IF l_debug_level > 0 THEN
1002 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Customer_Rejected: '|| TO_CHAR (p_header_id) ,1) ;
1003 END IF;
1004 OE_MSG_PUB.set_msg_context(
1005 p_entity_code => 'HEADER'
1006 ,p_entity_id => p_header_id
1007 ,p_header_id => p_header_id);
1008
1009 BEGIN
1010 select wpa.activity_name
1011 into l_activity_name
1012 from wf_item_activity_statuses wias, wf_process_activities wpa
1013 where item_type = OE_GLOBALS.G_WFI_NGO
1014 and item_key = to_char(p_header_id)
1015 and activity_status = wf_engine.eng_notified
1016 and wpa.activity_name = l_customer_acceptance
1017 and wias.process_activity = wpa.instance_id;
1018
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021
1022 IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN
1023 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
1024 ELSE -- assume blanket
1025 fnd_message.set_name('ONT', 'OE_NTF_BSA');
1026 END IF;
1027 l_sales_document_type := fnd_message.get;
1028
1029 fnd_message.set_name('ONT', 'OE_WF_NO_CUST_REJECTED'); --flow not at right state
1030 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
1031 oe_msg_pub.add;
1032 x_return_status := FND_API.G_RET_STS_ERROR;
1033 IF l_debug_level > 0 THEN
1034 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Rejected WITH STATUS: '||X_RETURN_STATUS ,1);
1035 END IF;
1036 return;
1037 END;
1038 -- ok to go Reject
1039
1040 OE_REASONS_UTIL.Apply_Reason(p_entity_code => p_entity_code,
1041 p_entity_id => p_header_id,
1042 p_version_number => p_version_number,
1043 p_reason_type => p_reason_type,
1044 p_reason_code => p_reason_code,
1045 p_reason_comments => p_reason_comments,
1046 x_reason_id => l_reason_id,
1047 x_return_status => l_return_status);
1048
1049
1050 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_NGO, to_char(p_header_id), l_activity_name, 'REJECT');
1051 IF l_debug_level > 0 THEN
1052 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Rejected normally', 1);
1053 END IF;
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1057 IF l_debug_level > 0 THEN
1058 oe_debug_pub.add('EXITING OE_Negotiate_WF.Customer_Rejected WITH STATUS: '||X_RETURN_STATUS ,1);
1059 END IF;
1060 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1061 THEN
1062 OE_MSG_PUB.Add_Exc_Msg
1063 ( G_PKG_NAME,
1064 'Customer_Rejected'
1065 );
1066 END IF;
1067
1068 END Customer_Rejected;
1069
1070
1071
1072 PROCEDURE Offer_Date_Changed(p_header_id NUMBER,
1073 x_return_status OUT NOCOPY VARCHAR2)
1074 IS
1075 --
1076 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1077 l_activity_name VARCHAR2(30);
1078 l_sales_document_type_code VARCHAR2(1);
1079 l_sales_document_type VARCHAR2(30);
1080 --
1081 BEGIN
1082 OE_MSG_PUB.initialize;
1083 x_return_status := FND_API.G_RET_STS_SUCCESS;
1084 IF l_debug_level > 0 THEN
1085 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Offer_Date_Changed: '|| TO_CHAR (p_header_id) ,1) ;
1086 END IF;
1087 OE_MSG_PUB.set_msg_context(
1088 p_entity_code => 'HEADER'
1089 ,p_entity_id => p_header_id
1090 ,p_header_id => p_header_id);
1091
1092 BEGIN
1093 select wpa.activity_name
1094 into l_activity_name
1095 from wf_item_activity_statuses wias, wf_process_activities wpa
1096 where item_type = OE_GLOBALS.G_WFI_NGO
1097 and item_key = to_char(p_header_id)
1098 and activity_status = wf_engine.eng_notified
1099 and wpa.activity_name in ('WAIT_FOR_EXPIRATION', 'WAIT_FOR_FINAL_EXPIRATION')
1100 and wias.process_activity = wpa.instance_id;
1101
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, p_header_id, 'SALES_DOCUMENT_TYPE_CODE');
1105 IF l_sales_document_type_code = 'O' THEN
1106 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
1107 ELSE -- assume blanket
1108 fnd_message.set_name('ONT', 'OE_NTF_BSA');
1109 END IF;
1110 l_sales_document_type := fnd_message.get;
1111 fnd_message.set_name('ONT', 'OE_WF_NO_OFFER_DATE_CHANGE'); --flow not at right state
1112 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
1113 oe_msg_pub.add;
1114 x_return_status := FND_API.G_RET_STS_ERROR;
1115 IF l_debug_level > 0 THEN
1116 oe_debug_pub.add('EXITING OE_Negotiate_WF.Offer_Date_Changed WITH STATUS: '||X_RETURN_STATUS ,1);
1117 END IF;
1118 return;
1119 END;
1120 -- ok to go date changed
1121 IF l_debug_level > 0 THEN
1122 oe_debug_pub.add('Calling WF_ENGINE to completeactivity' ,3);
1123 END IF;
1124 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_NGO, to_char(p_header_id), l_activity_name, 'DATE_CHANGED');
1125 IF l_debug_level > 0 THEN
1126 oe_debug_pub.add('EXITING OE_Negotiate_WF.Offer_Date_Changed normally', 1);
1127 END IF;
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1131 IF l_debug_level > 0 THEN
1132 oe_debug_pub.add('EXITING OE_Negotiate_WF.Offer_Date_Changed WITH STATUS: '||X_RETURN_STATUS ,1);
1133 END IF;
1134 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1135 THEN
1136 OE_MSG_PUB.Add_Exc_Msg
1137 ( G_PKG_NAME,
1138 'Offer_Date_Changed'
1139 );
1140 END IF;
1141
1142 END Offer_Date_Changed;
1143
1144
1145 PROCEDURE Submit_Draft(p_header_id NUMBER,
1146 x_return_status OUT NOCOPY VARCHAR2)
1147 IS
1148 --
1149 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1150 l_activity_name VARCHAR2(30);
1151 l_sales_document_type_code VARCHAR2(1);
1152 l_sales_document_type VARCHAR2(30);
1153 l_entity_code VARCHAR2(30);
1154 l_wf_item_count NUMBER;
1155 l_so_count NUMBER;
1156 l_bsa_count NUMBER;
1157 --
1158 l_submit_draft_eligible VARCHAR2(30) := 'SUBMIT_DRAFT_ELIGIBLE';
1159
1160 --
1161 BEGIN
1162 -- OE_MSG_PUB.initialize; commented out for 4671489
1163 x_return_status := FND_API.G_RET_STS_SUCCESS;
1164 IF l_debug_level > 0 THEN
1165 oe_debug_pub.add('ENTERING OE_Negotiate_WF.Submit_Draft: '|| TO_CHAR (p_header_id) ,1) ;
1166 END IF;
1167
1168 BEGIN
1169 --Bug3435165
1170 select count(1)
1171 into l_wf_item_count
1172 from wf_items
1173 where item_type = 'OENH'
1174 and item_key = p_header_id;
1175
1176 IF l_wf_item_count = 0 THEN --we are in fulfillment phase and it has no nego phase
1177 -- should be a sales order, as BSA UI won't call this API in fulfillment phase, but
1178 -- double check to confirm
1179 select count(1)
1180 into l_so_count
1181 from oe_order_headers_all
1182 where header_id = p_header_id;
1183
1184 IF l_so_count > 0 THEN
1185 l_sales_document_type_code := 'O';
1186 raise FND_API.G_EXC_ERROR;
1187 ELSE --should never come here, given how BSA is coded now
1188 select count(1)
1189 into l_bsa_count
1190 from oe_blanket_headers_all
1191 where header_id = p_header_id;
1192
1193 IF l_bsa_count > 0 THEN
1194 l_sales_document_type_code := 'B';
1195 raise FND_API.G_EXC_ERROR;
1196 END IF;
1197 END IF;
1198 END IF;
1199 -- END Bug3435165
1200
1201 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, p_header_id, 'SALES_DOCUMENT_TYPE_CODE');
1202 IF l_sales_document_type_code = 'O' THEN
1203 l_entity_code := 'HEADER';
1204 ELSE
1205 l_entity_code := 'BLANKET_HEADER';
1206 END IF;
1207
1208 OE_MSG_PUB.set_msg_context(
1209 p_entity_code => l_entity_code
1210 ,p_entity_id => p_header_id
1211 ,p_header_id => p_header_id
1212 ,p_line_id => null
1213 ,p_orig_sys_document_ref => null
1214 ,p_orig_sys_document_line_ref => null
1215 ,p_change_sequence => null
1216 ,p_source_document_id => null
1217 ,p_source_document_line_id => null
1218 ,p_order_source_id => null
1219 ,p_source_document_type_id => null);
1220
1221 select wpa.activity_name
1222 into l_activity_name
1223 from wf_item_activity_statuses wias, wf_process_activities wpa
1224 where item_type = OE_GLOBALS.G_WFI_NGO
1225 and item_key = to_char(p_header_id)
1226 and activity_status = wf_engine.eng_notified
1227 and wpa.activity_name = l_submit_draft_eligible
1228 and wias.process_activity = wpa.instance_id;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 IF l_sales_document_type_code = 'O' THEN
1233 fnd_message.set_name('ONT', 'OE_NTF_QUOTE');
1234 ELSE -- assume blanket
1235 fnd_message.set_name('ONT', 'OE_NTF_BSA');
1236 END IF;
1237 l_sales_document_type := fnd_message.get;
1238
1239 fnd_message.set_name('ONT', 'OE_WF_NO_SUBMIT_DRAFT'); --flow not at right state
1240 fnd_message.set_token('SALES_DOCUMENT_TYPE', l_sales_document_type);
1241 oe_msg_pub.add;
1242 x_return_status := FND_API.G_RET_STS_ERROR;
1243 IF l_debug_level > 0 THEN
1244 oe_debug_pub.add('EXITING OE_Negotiate_WF.Submit_Draft WITH STATUS: '||X_RETURN_STATUS ,1);
1245 END IF;
1246 return;
1247 END;
1248 -- ok to go Submit Draft
1249 WF_ENGINE.CompleteActivityInternalName(itemtype => OE_GLOBALS.G_WFI_NGO,
1250 itemkey => to_char(p_header_id),
1251 activity => l_activity_name,
1252 result => 'COMPLETE');
1253 IF l_debug_level > 0 THEN
1254 oe_debug_pub.add('EXITING OE_Negotiate_WF.Submit_Draft normally', 1);
1255 END IF;
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1259 IF l_debug_level > 0 THEN
1260 oe_debug_pub.add('EXITING OE_Negotiate_WF.Submit_Draft WITH STATUS: '||X_RETURN_STATUS ,1);
1261 END IF;
1262 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1263 THEN
1264 OE_MSG_PUB.Add_Exc_Msg
1265 ( G_PKG_NAME,
1266 'Submit_Draft'
1267 );
1268 END IF;
1269
1270
1271
1272 END Submit_Draft;
1273
1274 PROCEDURE set_header_attributes_internal(p_header_id IN NUMBER)
1275 IS
1276 --
1277 l_sales_document_type_code VARCHAR2(1);
1278 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1279 l_sold_to_org_id NUMBER;
1280 l_salesrep_id NUMBER;
1281 l_salesrep VARCHAR2(240);
1282 l_sold_to VARCHAR2(240);
1283 l_customer_number VARCHAR2(30);
1284 l_expiration_date DATE;
1285 l_aname wf_engine.nametabtyp;
1286 l_avaluetext wf_engine.texttabtyp;
1287 --
1288 BEGIN
1289 IF l_debug_level > 0 THEN
1290 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Set_Header_Attributes_Internal:'||To_char(p_header_id) ,1 ) ;
1291 END IF;
1292
1293 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(OE_GLOBALS.G_WFI_NGO, To_char(p_header_id), 'SALES_DOCUMENT_TYPE_CODE');
1294 IF l_sales_document_type_code = 'O' THEN
1295
1296 select sold_to_org_id, expiration_date, salesrep_id
1297 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
1298 from oe_order_headers_all
1299 where header_id = p_header_id;
1300 ELSE
1301 select sold_to_org_id, expiration_date, salesrep_id
1302 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
1303 from oe_blanket_headers_all
1304 where header_id = p_header_id;
1305 END IF;
1306
1307 l_salesrep := OE_Id_To_Value.Salesrep(p_salesrep_id=>l_salesrep_id);
1308 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);
1309 l_aname(1) := 'SALESPERSON';
1310 l_avaluetext(1) := l_salesrep;
1311 l_aname(2) := 'SOLD_TO';
1312 l_avaluetext(2) := l_sold_to;
1313 l_aname(3) := 'EXPIRATION_DATE';
1314 l_avaluetext(3) := l_expiration_date;
1315
1316 wf_engine.SetItemAttrTextArray(itemtype=>OE_GLOBALS.G_WFI_NGO,
1317 itemkey=>To_char(p_header_id),
1318 aname=>l_aname,
1319 avalue=>l_avaluetext);
1320
1321 -- end setting item attribute for WF header attributes
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1325 THEN
1326 OE_MSG_PUB.Add_Exc_Msg
1327 (G_PKG_NAME
1328 , 'Set_Header_Attributes_Internal'
1329 );
1330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1331 END IF;
1332
1333 END Set_Header_Attributes_Internal;
1334
1335 PROCEDURE Set_Header_Attributes(
1336 itemtype in varchar2,
1337 itemkey in varchar2,
1338 actid in number,
1339 funcmode in varchar2,
1340 resultout in out nocopy varchar2)
1341 IS
1342 --
1343 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1344 BEGIN
1345 IF l_debug_level > 0 THEN
1346 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Set_Header_Attributes:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
1347 END IF;
1348 OE_STANDARD_WF.Set_Msg_Context(actid);
1349 IF (funcmode = 'RUN') THEN
1350 set_header_attributes_internal(To_number(itemkey));
1351 resultout := 'COMPLETE';
1352 END IF;
1353 EXCEPTION
1354 when others then
1355 wf_core.context('OE_Negotiate_WF', 'Set_Header_Attributes', itemtype, itemkey, to_char(actid), funcmode);
1356 -- start data fix project
1357 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1358 p_itemtype => itemtype,
1359 p_itemkey => itemkey);
1360 -- end data fix project
1361 oe_standard_wf.save_messages;
1362 oe_standard_wf.clear_msg_context;
1363 raise;
1364
1365 END Set_Header_Attributes;
1366
1367 PROCEDURE set_final_expiration_date(
1368 itemtype in varchar2,
1369 itemkey in varchar2,
1370 actid in number,
1371 funcmode in varchar2,
1372 resultout in out nocopy varchar2)
1373 IS
1374 --
1375 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1376 l_final_timer NUMBER;
1377 l_sales_document_type_code VARCHAR2(1);
1378 l_expiration_date DATE;
1379 --
1380
1381 BEGIN
1382 IF l_debug_level > 0 THEN
1383 oe_debug_pub.add( 'ENTERING OE_Negotiate_WF.Set_Final_Expiration_Date:'||ITEMTYPE||'/'||ITEMKEY ,1 ) ;
1384 END IF;
1385 OE_STANDARD_WF.Set_Msg_Context(actid);
1386 IF (funcmode = 'RUN') then
1387 OE_MSG_PUB.set_msg_context(
1388 p_entity_code => 'HEADER'
1389 ,p_entity_id => to_number(itemkey)
1390 ,p_header_id => to_number(itemkey));
1391
1392 set_header_attributes_internal(To_number(itemkey));
1393 IF l_debug_level > 0 THEN
1394 oe_debug_pub.add( 'Done setting header attributes',1);
1395 END IF;
1396
1397 l_sales_document_type_code := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'SALES_DOCUMENT_TYPE_CODE');
1398 IF l_sales_document_type_code = 'O' THEN
1399 select expiration_date
1400 into l_expiration_date
1401 from oe_order_headers_all
1402 where header_id = to_number(itemkey);
1403 ELSE
1404 -- even though there is no offer expiration date for blanket for now
1405 -- we will still fetch it for the future
1406 select expiration_date
1407 into l_expiration_date
1408 from oe_blanket_headers_all
1409 where header_id = to_number(itemkey);
1410 END IF;
1411
1412 IF l_expiration_date IS NULL THEN
1413 -- should not be coming here
1414 RETURN;
1415 END IF;
1416
1417 l_final_timer := (l_expiration_date - Sysdate) * 1440;
1418 IF l_final_timer > 0 THEN
1419 wf_engine.setitemattrnumber(itemtype=>itemtype,
1420 itemkey=>itemkey,
1421 aname=>'OFFER_FINAL_EXPIRE_TIMER',
1422 avalue=>l_final_timer);
1423 resultout := 'COMPLETE';
1424 ELSE
1425 resultout := 'COMPLETE:EXPIRED';
1426 END IF;
1427
1428 IF l_debug_level > 0 THEN
1429 oe_debug_pub.add('Leaving OE_Negotiate_WF.Set_Final_Expiration_Date', 1);
1430 END IF;
1431 END IF;
1432 EXCEPTION
1433 when others then
1434 wf_core.context('OE_Negotiate_WF', 'Set_Final_Expiration_Date', itemtype, itemkey, to_char(actid), funcmode);
1435 -- start data fix project
1436 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1437 p_itemtype => itemtype,
1438 p_itemkey => itemkey);
1439 -- end data fix project
1440 oe_standard_wf.save_messages;
1441 oe_standard_wf.clear_msg_context;
1442 raise;
1443
1444 END set_final_expiration_date;
1445
1446
1447 FUNCTION At_Customer_Acceptance(p_header_id NUMBER)
1448 RETURN Boolean
1449 IS
1450
1451 --
1452 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1453 l_activity_name VARCHAR2(30);
1454 --
1455 l_customer_acceptance VARCHAR2(30) := 'CUSTOMER_ACCEPTANCE';
1456 --
1457 BEGIN
1458
1459 IF l_debug_level > 0 THEN
1460 oe_debug_pub.add( 'ENTER OE_Negotiate_WF.At_Customer_Acceptance');
1461 END IF;
1462
1463 select wpa.activity_name
1464 into l_activity_name
1465 from wf_item_activity_statuses wias, wf_process_activities wpa
1466 where item_type = OE_GLOBALS.G_WFI_NGO
1467 and item_key = to_char(p_header_id)
1468 and activity_status = wf_engine.eng_notified
1469 and wpa.activity_name = l_customer_acceptance
1470 and wias.process_activity = wpa.instance_id;
1471
1472 RETURN TRUE;
1473
1474 EXCEPTION
1475 WHEN OTHERS THEN
1476 -- not at customer_accpetance
1477 RETURN FALSE;
1478
1479 END At_Customer_Acceptance;
1480
1481
1482 END OE_Negotiate_WF;