[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_WORKFLOW_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_WORKFLOW_PVT AS
2 /* $Header: OKLRQUWB.pls 120.7 2006/07/21 13:14:41 akrangan noship $ */
3 -- Bug 4741121 viselvar modified start
4 G_MSG_TOKEN_OBJECT_NAME CONSTANT VARCHAR2(20) := 'OBJECT_NAME';
5 G_MSG_TOKEN_NAME CONSTANT VARCHAR2(20) := 'NAME';
6 G_WF_ITM_APPLICATION_ID CONSTANT VARCHAR2(20) := 'APPLICATION_ID';
7 G_WF_ITM_TRANSACTION_TYPE_ID CONSTANT VARCHAR2(20) := 'TRX_TYPE_ID';
8 G_FE_APPROVAL_WF CONSTANT VARCHAR2(2) := 'WF';
9 G_FE_APPROVAL_AME CONSTANT VARCHAR2(3) := 'AME';
10 G_WF_ITM_TRANSACTION_ID CONSTANT VARCHAR2(20) := 'TRANSACTION_ID';
11 G_WF_ITM_REQUESTER CONSTANT VARCHAR2(20) := 'REQUESTER';
12 G_WF_ITM_REQUESTER_ID CONSTANT VARCHAR2(20) := 'REQUESTOR_ID';
13 G_WF_ITM_APPROVAL_REQ_MSG CONSTANT VARCHAR2(30) := 'APPROVAL_REQUEST_MESSAGE';
14 G_WF_ITM_PARENT_ITEM_KEY CONSTANT VARCHAR2(20) := 'PARENT_ITEM_KEY';
15 G_WF_ITM_PARENT_ITEM_TYPE CONSTANT VARCHAR2(20) := 'PARENT_ITEM_TYPE';
16 G_WF_ITM_APPROVED_YN CONSTANT VARCHAR2(15) := 'APPROVED_YN';
17 G_WF_ITM_MASTER CONSTANT VARCHAR2(10) := 'MASTER';
18 G_WF_ITM_MESSAGE_SUBJECT CONSTANT VARCHAR2(20) := 'MESSAGE_SUBJECT';
19 G_WF_ITM_APP_REQUEST_SUB CONSTANT VARCHAR2(30) := 'APP_REQUEST_SUB';
20 G_WF_ITM_APP_REMINDER_SUB CONSTANT VARCHAR2(30) := 'APP_REMINDER_SUB';
21 G_WF_ITM_APP_APPROVED_SUB CONSTANT VARCHAR2(30) := 'APP_APPROVED_SUB';
22 G_WF_ITM_APP_REJECTED_SUB CONSTANT VARCHAR2(30) := 'APP_REJECTED_SUB';
23 G_WF_ITM_APP_REMINDER_HEAD CONSTANT VARCHAR2(30) := 'APP_REMINDER_HEAD';
24 G_WF_ITM_APP_APPROVED_HEAD CONSTANT VARCHAR2(30) := 'APP_APPROVED_HEAD';
25 G_WF_ITM_APP_REJECTED_HEAD CONSTANT VARCHAR2(30) := 'APP_REJECTED_HEAD';
26 G_WF_ITM_APPROVER CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVER';
27 G_WF_ITM_MESSAGE_SUBJECT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_SUBJECT';
28 G_WF_ITM_MESSAGE_DESCR CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DESCRIPTION';
29 G_WF_ITM_MESSAGE_BODY CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DOC';
30 G_WF_ITM_RESULT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'RESULT';
31 G_WF_ITM_APPROVED_YN_YES CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVED';
32 G_WF_ITM_APPROVED_YN_NO CONSTANT wf_item_attributes.name%TYPE DEFAULT 'REJECTED';
33 -- viselvar start
34 --subtype the lease application header
35 SUBTYPE lapv_rec_type IS OKL_LAP_PVT.LAPV_REC_TYPE;
36
37 -- curosr to fetch the parent record of
38 CURSOR get_parent_object(quote_id IN NUMBER) IS
39 SELECT parent_object_code, parent_object_id FROM okl_lease_quotes_b
40 WHERE id=quote_id;
41 -- viselvar end
42
43 ------------------------------------------------------------------------------
44 -- FUNCTION get_message
45 ------------------------------------------------------------------------------
46 -- Start of comments
47 --
48 -- Procedure Name : get_message
49 -- Description : function to return the message from fnd message for notifications
50 -- Business Rules : function to return the message from fnd message for notifications
51 -- Parameters :
52 -- Version : 1.0
53 -- History : viselvar created
54 --
55 -- End of comments
56
57 FUNCTION get_message(p_msg_name IN VARCHAR2, object_name IN VARCHAR2,
58 object_value IN VARCHAR2) RETURN VARCHAR2 IS
59 l_message VARCHAR2(100);
60
61 BEGIN
62
63 IF p_msg_name IS NOT NULL THEN
64 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME, NAME => p_msg_name);
65 Fnd_Message.SET_TOKEN(TOKEN => G_MSG_TOKEN_OBJECT_NAME,
66 VALUE => object_name);
67 Fnd_Message.SET_TOKEN(TOKEN => G_MSG_TOKEN_NAME, VALUE => object_value);
68 l_message := fnd_message.get();
69 END IF;
70 RETURN l_message;
71 EXCEPTION
72 WHEN OTHERS THEN
73 RETURN NULL;
74 END get_message;
75
76 ------------------------------------------------------------------------------
77 -- PROCEDURE get_token
78 ------------------------------------------------------------------------------
79 -- Start of comments
80 --
81 -- Function Name : get_token
82 -- Description : get the message for a message name frm fnd messages
83 -- Business Rules : get the message for a message name frm fnd messages
84 -- Parameters :
85 -- Version : 1.0
86 -- History : viselvar created
87 --
88 -- End of comments
89
90 FUNCTION get_token(p_msg_name IN VARCHAR2, token_name IN VARCHAR2,
91 token_value IN VARCHAR2) RETURN VARCHAR2 IS
92 l_message VARCHAR2(100);
93
94 BEGIN
95
96 IF p_msg_name IS NOT NULL THEN
97 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME, NAME => p_msg_name);
98 Fnd_Message.SET_TOKEN(TOKEN => token_name, VALUE => token_value);
99 l_message := fnd_message.get();
100 END IF;
101 RETURN l_message;
102 EXCEPTION
103 WHEN OTHERS THEN
104 RETURN NULL;
105 END get_token;
106
107 ------------------------------------------------------------------------------
108 -- PROCEDURE get_quote_msg_body
109 ------------------------------------------------------------------------------
110 -- Start of comments
111 --
112 -- Function Name : get_quote_msg_body
113 -- Description : this function generates the message body
114 -- Business Rules : this function generates the message body
115 -- Parameters :
116 -- Version : 1.0
117 -- History : viselvar created
118 --
119 -- End of comments
120
121 FUNCTION get_quote_msg_body(itemtype IN VARCHAR2, itemkey IN VARCHAR2) RETURN VARCHAR2 IS
122
123 l_name VARCHAR2(240);
124 l_currency VARCHAR2(30);
125 l_type VARCHAR2(30);
126 l_version_number VARCHAR2(24);
127 l_effective_from DATE;
128 l_effective_to DATE;
129 lv_message_body VARCHAR2(4000);
130 l_parent_object_code VARCHAR2(20);
131 l_parent_id NUMBER;
132 l_quote_id NUMBER;
133
134
135 CURSOR get_lease_app(lease_app_id IN NUMBER) IS
136 SELECT reference_number, valid_from, valid_to FROM
137 OKL_LEASE_APPLICATIONS_B
138 WHERE id= lease_app_id;
139
140 BEGIN
141 -- set the attributes
142 l_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'QUOTE_NUM');
143 l_effective_from := wf_engine.GetItemAttrDate(itemtype,
144 itemkey,
145 'EFFECTIVE_FROM');
146 l_effective_to := wf_engine.GetItemAttrDate(itemtype,
147 itemkey,
148 'EFFECTIVE_TO');
149
150 l_quote_id:=wf_engine.GetItemAttrText(itemtype, itemkey, 'QUOTE_ID');
151
152
153 OPEN get_parent_object(l_quote_id);
154 FETCH get_parent_object INTO l_parent_object_code, l_parent_id;
155 CLOSE get_parent_object;
156
157 IF (l_parent_object_code = 'LEASEAPP') THEN
158 -- get the lease application details and not the quote details
159 OPEN get_lease_app(l_parent_id);
160 FETCH get_lease_app INTO l_name, l_effective_from, l_effective_to;
161 CLOSE get_lease_app;
162
163 END IF;
164 lv_message_body := '<body>' ||
165 get_token('OKL_NAME', 'NAME', l_name) ||
166 '<br>' ||
167 get_token('OKL_EFFECTIVE_FROM',
168 'FROM_DATE',
169 fnd_Date.date_to_displaydate(l_effective_from)) ||
170 '<br>' ||
171 get_token('OKL_EFFECTIVE_TO',
172 'TO_DATE',
173 fnd_Date.date_to_displaydate(l_effective_to)) ||
174 '<br>' ||
175 '</body>';
176
177 -- return the message body
178 RETURN lv_message_body;
179
180 END get_quote_msg_body;
181
182 ------------------------------------------------------------------------------
183 -- PROCEDURE get_quote_msg_doc
184 ------------------------------------------------------------------------------
185 -- Start of comments
186 --
187 -- Function Name : get_quote_msg_doc
188 -- Description : this function generates the message body
189 -- Business Rules : this function generates the message body
190 -- Parameters :
191 -- Version : 1.0
192 -- History : viselvar created
193 --
194 -- End of comments
195
196 PROCEDURE get_quote_msg_doc(document_id IN VARCHAR2,
197 display_type IN VARCHAR2,
198 document IN OUT NOCOPY VARCHAR2,
199 document_type IN OUT NOCOPY VARCHAR2) IS
200
201 BEGIN
202
203 -- get the message body
204
205 document := get_quote_msg_body('OKLSOQUO', document_id);
206 document_type := display_type;
207 END get_quote_msg_doc;
208
209 -- Bug 4741121 viselvar Modified End
210 --------------------------------
211 -- PROCEDURE change_quote_status
212 --------------------------------
213 PROCEDURE change_quote_status(p_quote_id IN NUMBER,
214 p_qte_status IN VARCHAR2,
215 x_return_status OUT NOCOPY VARCHAR2) IS
216
217 lx_return_status VARCHAR2(1);
218 lx_msg_count NUMBER;
219 lx_msg_data VARCHAR2(4000);
220
221 l_lease_qte_rec okl_lsq_pvt.lsqv_rec_type;
222 x_lease_qte_rec okl_lsq_pvt.lsqv_rec_type;
223
224 -- Bug 4713798 - Added cursor
225 CURSOR c_obj
226 IS
227 SELECT object_version_number
228 FROM okl_lease_quotes_b
229 WHERE id = p_quote_id;
230
231 BEGIN
232
233 l_lease_qte_rec.id := p_quote_id;
234 l_lease_qte_rec.status := p_qte_status;
235
236 OPEN c_obj;
237 FETCH c_obj INTO l_lease_qte_rec.object_version_number;
238 CLOSE c_obj;
239
240 okl_lsq_pvt.update_row(p_api_version => G_API_VERSION
241 ,p_init_msg_list => G_FALSE
242 ,x_return_status => lx_return_status
243 ,x_msg_count => lx_msg_count
244 ,x_msg_data => lx_msg_data
245 ,p_lsqv_rec => l_lease_qte_rec
246 ,x_lsqv_rec => x_lease_qte_rec );
247
248 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
249 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
250 ELSIF lx_return_status = G_RET_STS_ERROR THEN
251 RAISE OKL_API.G_EXCEPTION_ERROR;
252 END IF;
253
254 x_return_status := lx_return_status;
255
256 EXCEPTION
257
258 WHEN OKL_API.G_EXCEPTION_ERROR THEN
259
260 x_return_status := G_RET_STS_ERROR;
261
262 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
263
264 x_return_status := G_RET_STS_UNEXP_ERROR;
265
266 WHEN OTHERS THEN
267
268 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
269 p_msg_name => G_UNEXPECTED_ERROR,
270 p_token1 => G_SQLCODE_TOKEN,
271 p_token1_value => sqlcode,
272 p_token2 => G_SQLERRM_TOKEN,
273 p_token2_value => sqlerrm);
274
275 x_return_status := G_RET_STS_UNEXP_ERROR;
276
277 END change_quote_status;
278
279 -------------------------------------
280 -- PROCEDURE raise_quote_accept_event
281 -------------------------------------
282 PROCEDURE raise_quote_accept_event (p_quote_id IN NUMBER,
283 x_return_status OUT NOCOPY VARCHAR2) IS
284
285 l_parameter_list wf_parameter_list_t;
286 l_key VARCHAR2(240);
287 l_event_name VARCHAR2(240) := 'oracle.apps.okl.sales.acceptquote';
288 l_seq NUMBER;
289
290 lx_return_status VARCHAR2(1);
291
292 CURSOR okl_key_csr IS
293 SELECT okl_wf_item_s.nextval
294 FROM dual;
295
296 BEGIN
297
298 OPEN okl_key_csr;
299 FETCH okl_key_csr INTO l_seq;
300 CLOSE okl_key_csr;
301
302 l_key := l_event_name ||l_seq;
303
304 -- raise the business event
305 wf_event.AddParameterToList('QUOTE_ID', p_quote_id, l_parameter_list);
306 --added by akrangan
307 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
308
309 wf_event.raise(p_event_name => l_event_name,
310 p_event_key => l_key,
311 p_parameters => l_parameter_list);
312
313 l_parameter_list.DELETE;
314
315 -- change the quote status
316 change_quote_status(p_quote_id => p_quote_id,
317 p_qte_status => 'CT-ACCEPTED',
318 x_return_status => lx_return_status);
319
320 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
321 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
322 ELSIF lx_return_status = G_RET_STS_ERROR THEN
323 RAISE OKL_API.G_EXCEPTION_ERROR;
324 END IF;
325
326 x_return_status := lx_return_status;
327
328 EXCEPTION
329
330 WHEN OTHERS THEN
331
332 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
333 p_msg_name => G_UNEXPECTED_ERROR,
334 p_token1 => G_SQLCODE_TOKEN,
335 p_token1_value => sqlcode,
336 p_token2 => G_SQLERRM_TOKEN,
337 p_token2_value => sqlerrm);
338
339 x_return_status := G_RET_STS_UNEXP_ERROR;
340
341 END raise_quote_accept_event;
342
343 ------------------------------------
344 -- PROCEDURE populate_accept_attribs
345 ------------------------------------
346 PROCEDURE populate_accept_attribs(itemtype IN VARCHAR2,
347 itemkey IN VARCHAR2,
348 actid IN NUMBER,
349 funcmode IN VARCHAR2,
350 resultout OUT NOCOPY VARCHAR2) IS
351
352 l_api_name CONSTANT VARCHAR2(30) := 'populate_accept_attribs';
353
354 l_quote_number OKL_LEASE_QUOTES_V.REFERENCE_NUMBER%TYPE;
355 l_quote_id OKL_LEASE_QUOTES_V.ID%TYPE;
356
357 CURSOR c_fetch_quote_number(p_quote_id OKL_LEASE_QUOTES_V.ID%TYPE)
358 IS
359 SELECT reference_number
360 FROM okl_lease_quotes_v
361 WHERE id = p_quote_id;
362
363 lx_return_status VARCHAR2(1);
364
365 BEGIN
366
367 IF (funcmode = 'RUN') THEN
368
369 l_quote_id := wf_engine.GetItemAttrText(itemtype => itemtype,
370 itemkey => itemkey,
371 aname => 'QUOTE_ID');
372
373 OPEN c_fetch_quote_number(p_quote_id => l_quote_id);
374 FETCH c_fetch_quote_number INTO l_quote_number;
375 CLOSE c_fetch_quote_number;
376
377 wf_engine.SetItemAttrText (itemtype => itemtype,
378 itemkey => itemkey,
379 aname => 'QUOTE_ID',
380 avalue => l_quote_number);
381
382 change_quote_status(p_quote_id => l_quote_id,
383 p_qte_status => 'CT-ACCEPTED',
384 x_return_status => lx_return_status);
385
386 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
387 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
388 ELSIF lx_return_status = G_RET_STS_ERROR THEN
389 RAISE OKL_API.G_EXCEPTION_ERROR;
390 END IF;
391
392 resultout := 'COMPLETE:';
393 RETURN;
394
395 END IF;
396
397 -- CANCEL mode
398 IF (funcmode = 'CANCEL') then
399 resultout := 'COMPLETE:';
400 RETURN;
401 END IF;
402 -- TIMEOUT mode
403 IF (funcmode = 'TIMEOUT') then
404 resultout := 'COMPLETE:';
405 RETURN;
406 END IF;
407
408 EXCEPTION
409
410 WHEN OKL_API.G_EXCEPTION_ERROR THEN
411
412 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
413 RAISE;
414
415 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
416
417 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
418 RAISE;
419
420 WHEN OTHERS THEN
421
422 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
423 p_msg_name => G_UNEXPECTED_ERROR,
424 p_token1 => G_SQLCODE_TOKEN,
425 p_token1_value => sqlcode,
426 p_token2 => G_SQLERRM_TOKEN,
427 p_token2_value => sqlerrm);
428
429 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
430 RAISE;
431
432 END populate_accept_attribs;
433
434 -------------------------------------
435 -- PROCEDURE raise_quote_submit_event
436 -------------------------------------
437 PROCEDURE raise_quote_submit_event (p_quote_id IN NUMBER,
438 x_return_status OUT NOCOPY VARCHAR2) AS
439
440 CURSOR okl_key_csr IS
441 SELECT okl_wf_item_s.nextval
442 FROM dual;
443
444 l_parameter_list wf_parameter_list_t;
445 l_key VARCHAR2(240);
446 l_event_name CONSTANT VARCHAR2(100) := 'oracle.apps.okl.sales.submitquote';
447 l_seq NUMBER;
448
449 lx_return_status VARCHAR2(1) := G_RET_STS_SUCCESS ;
450 l_return_status VARCHAR2(1):=G_RET_STS_SUCCESS ;
451 l_lapv_rec lapv_rec_type;
452 x_lapv_rec lapv_rec_type;
453 l_parent_object VARCHAR2(30);
454 l_parent_id NUMBER;
455 x_msg_count NUMBER;
456 x_msg_data VARCHAR2(4000);
457
458 BEGIN
459
460 OPEN okl_key_csr;
461 FETCH okl_key_csr INTO l_seq;
462 CLOSE okl_key_csr;
463
464 l_key := l_event_name ||l_seq;
465
466 -- viselvar added
467 -- check the profile option and accordingly change the status
468 IF NVL(FND_PROFILE.VALUE('OKL_SO_APPROVAL_PROCESS'),'NONE') = 'NONE' THEN
469
470 change_quote_status(p_quote_id => p_quote_id,
471 p_qte_status => 'PR-APPROVED',
472 x_return_status => lx_return_status);
473
474 OPEN get_parent_object(p_quote_id);
475 FETCH get_parent_object INTO l_parent_object, l_parent_id;
476 CLOSE get_parent_object;
477
478 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
479 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
480 ELSIF l_return_status = G_RET_STS_ERROR THEN
481 RAISE OKL_API.G_EXCEPTION_ERROR;
482 END IF;
483
484 IF (l_parent_object = 'LEASEAPP') THEN
485 l_lapv_rec.application_status := 'PR-APPROVED';
486 l_lapv_rec.id := l_parent_id;
487
488 -- update the status of the lease application to approved
489 OKL_LAP_PVT.UPDATE_ROW(
490 p_api_version => 1.0
491 ,p_init_msg_list => 'T'
492 ,x_return_status => l_return_status
493 ,x_msg_count => x_msg_count
494 ,x_msg_data => x_msg_data
495 ,p_lapv_rec => l_lapv_rec
496 ,x_lapv_rec => x_lapv_rec);
497
498 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
499 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
500 ELSIF l_return_status = G_RET_STS_ERROR THEN
501 RAISE OKL_API.G_EXCEPTION_ERROR;
502 END IF;
503 END IF;
504 ELSE
505
506 wf_event.AddParameterToList('QUOTE_ID', p_quote_id, l_parameter_list);
507 --added by akrangan
508 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
509
510 -- Raise Event
511 wf_event.raise(p_event_name => l_event_name,
512 p_event_key => l_key,
513 p_parameters => l_parameter_list);
514
515 l_parameter_list.DELETE;
516
517 END IF;
518 x_return_status := lx_return_status;
519
520 EXCEPTION
521
522 WHEN OTHERS THEN
523
524 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
525 p_msg_name => G_UNEXPECTED_ERROR,
526 p_token1 => G_SQLCODE_TOKEN,
527 p_token1_value => sqlcode,
528 p_token2 => G_SQLERRM_TOKEN,
529 p_token2_value => sqlerrm);
530
531 x_return_status := G_RET_STS_UNEXP_ERROR;
532
533 END raise_quote_submit_event;
534
535 ------------------------------------
536 -- PROCEDURE populate_submit_attribs
537 ------------------------------------
538 PROCEDURE populate_submit_attribs(itemtype IN VARCHAR2,
539 itemkey IN VARCHAR2,
540 actid IN NUMBER,
541 funcmode IN VARCHAR2,
542 resultout OUT NOCOPY VARCHAR2) AS
543
544 l_api_name CONSTANT VARCHAR2(30) := 'populate_submit_attribs';
545
546 l_quote_id OKL_LEASE_QUOTES_V.ID%TYPE;
547 l_quote_number OKL_LEASE_QUOTES_V.REFERENCE_NUMBER%TYPE;
548
549 lx_return_status VARCHAR2(1);
550 lx_msg_count NUMBER;
551 lx_msg_data VARCHAR2(4000);
552
553 CURSOR c_fetch_quote_number(p_quote_id OKL_LEASE_QUOTES_V.ID%TYPE)
554 IS
555 SELECT reference_number
556 FROM okl_lease_quotes_v
557 WHERE id = p_quote_id;
558
559 BEGIN
560
561 IF (funcmode = 'RUN') THEN
562
563 l_quote_id := wf_engine.GetItemAttrText(itemtype => itemtype,
564 itemkey => itemkey,
565 aname => 'QUOTE_ID');
566
567 OPEN c_fetch_quote_number(p_quote_id => l_quote_id);
568 FETCH c_fetch_quote_number INTO l_quote_number;
569 CLOSE c_fetch_quote_number;
570
571 wf_engine.SetItemAttrText (itemtype => itemtype,
572 itemkey => itemkey,
573 aname => 'QUOTE_ID',
574 avalue => l_quote_number);
575
576 change_quote_status(p_quote_id => l_quote_id,
577 p_qte_status => 'PR-APPROVED',
578 x_return_status => lx_return_status);
579
580 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
581 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
582 ELSIF lx_return_status = G_RET_STS_ERROR THEN
583 RAISE OKL_API.G_EXCEPTION_ERROR;
584 END IF;
585
586 resultout := 'COMPLETE:';
587 RETURN;
588
589 END IF;
590
591 -- CANCEL mode
592 IF (funcmode = 'CANCEL') then
593 resultout := 'COMPLETE:';
594 RETURN;
595 END IF;
596
597 -- TIMEOUT mode
598 IF (funcmode = 'TIMEOUT') then
599 resultout := 'COMPLETE:';
600 RETURN;
601 END IF;
602
603 EXCEPTION
604
605 WHEN OKL_API.G_EXCEPTION_ERROR THEN
606
607 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
608 RAISE;
609
610 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
611
612 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
613 RAISE;
614
615 WHEN OTHERS THEN
616
617 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
618 p_msg_name => G_UNEXPECTED_ERROR,
619 p_token1 => G_SQLCODE_TOKEN,
620 p_token1_value => sqlcode,
621 p_token2 => G_SQLERRM_TOKEN,
622 p_token2_value => sqlerrm);
623
624 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
625 RAISE;
626
627 END populate_submit_attribs;
628
629 -- Bug 4741121 viselvar Modified Start
630 ------------------------------------------------------------------------------
631 -- PROCEDURE check_approval_process
632 ------------------------------------------------------------------------------
633 -- Start of comments
634 --
635 -- Procedure Name : check_approval_process
636 -- Description : procedure to check the approval process
637 -- Business Rules : procedure to check the approval process
638 -- Parameters :
639 -- Version : 1.0
640 -- History : viselvar created
641 --
642 -- End of comments
643 PROCEDURE check_approval_process(itemtype IN VARCHAR2,
644 itemkey IN VARCHAR2, actid IN NUMBER,
645 funcmode IN VARCHAR2,
646 resultout OUT NOCOPY VARCHAR2) IS
647 l_approval_option VARCHAR2(10);
648 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'check_approval_process';
649
650 BEGIN
651
652 IF (funcmode = 'RUN') THEN
653
654 -- get the profile option
655
656 l_approval_option := fnd_profile.value('OKL_SO_APPROVAL_PROCESS');
657
658 -- depending on the profile option, take the workflow branch or the AME branch
659
660 IF l_approval_option = G_FE_APPROVAL_AME THEN
661 resultout := 'COMPLETE:AME';
662 ELSIF l_approval_option = G_FE_APPROVAL_WF THEN
663 resultout := 'COMPLETE:WF';
664 END IF;
665 RETURN;
666 END IF;
667
668 -- CANCEL mode
669
670 IF (funcmode = 'CANCEL') THEN
671 resultout := 'COMPLETE:';
672 RETURN;
673 END IF;
674
675 -- TIMEOUT mode
676
677 IF (funcmode = 'TIMEOUT') THEN
678 resultout := 'COMPLETE:';
679 RETURN;
680 END IF;
681
682 EXCEPTION
683 WHEN OTHERS THEN
684 wf_core.context(G_PKG_NAME,
685 l_api_name,
686 itemtype,
687 itemkey,
688 actid,
689 funcmode);
690 RAISE;
691 END check_approval_process;
692
693 ------------------------------------------------------------------------------
694 -- PROCEDURE populate_quote_attr
695 ------------------------------------------------------------------------------
696 -- Start of comments
697 --
698 -- Procedure Name : populate_quote_attr
699 -- Description : populate the quote attributes for the workflow
700 -- Business Rules : populate the quote attributes for the workflow
701 -- Parameters :
702 -- Version : 1.0
703 -- History : viselvar created
704 --
705 -- End of comments
706 PROCEDURE populate_quote_attr(itemtype IN VARCHAR2, itemkey IN VARCHAR2,
707 actid IN NUMBER, funcmode IN VARCHAR2,
708 resultout OUT NOCOPY VARCHAR2) IS
709
710 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'quote_ame';
711 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.PROCESS_POOL_AME';
712 l_debug_enabled VARCHAR2(10);
713 is_debug_procedure_on BOOLEAN;
714 l_quote_number VARCHAR2(100);
715 l_effective_from DATE;
716 l_effective_to DATE;
717 l_quote_id NUMBER;
718 l_object_name VARCHAR2(50);
719 l_request_message VARCHAR2(500);
720 l_approved_message VARCHAR2(500);
721 l_rejected_message VARCHAR2(500);
722 l_reminder_message VARCHAR2(500);
723 l_user_name VARCHAR2(240);
724 l_application_id fnd_application.application_id%TYPE;
725 l_parent_code VARCHAR2(20);
726 l_parent_id NUMBER;
727
728 CURSOR c_fetch_quote_number(p_quote_id OKL_LEASE_QUOTES_V.ID%TYPE)
729 IS
730 SELECT reference_number, valid_from, valid_to, parent_object_code, parent_object_id
731 FROM okl_lease_quotes_b
732 WHERE id = p_quote_id;
733
734 CURSOR fnd_user_csr IS
735 SELECT USER_NAME
736 FROM FND_USER
737 WHERE USER_ID = fnd_global.user_id;
738
739 -- Get the valid application id from FND
740
741 CURSOR c_get_app_id_csr IS
742 SELECT APPLICATION_ID
743 FROM FND_APPLICATION
744 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
745
746
747 CURSOR get_lease_app(lease_app_id IN NUMBER) IS
748 SELECT reference_number FROM
749 OKL_LEASE_APPLICATIONS_B
750 WHERE id= lease_app_id;
751
752 CURSOR get_object_name(l_code IN VARCHAR2) IS
753 SELECT meaning from fnd_lookups where
754 lookup_type='OKL_FRONTEND_OBJECTS' and lookup_code=l_code;
755
756
757 BEGIN
758
759 -- RUN mode
760
761 IF (funcmode = 'RUN') THEN
762
763 -- get the messages and set the messages accordingly depending on the object type
764
765 l_quote_id := wf_engine.GetItemAttrText(itemtype => itemtype,
766 itemkey => itemkey,
767 aname => 'QUOTE_ID');
768
769 OPEN c_fetch_quote_number(p_quote_id => l_quote_id);
770 FETCH c_fetch_quote_number INTO l_quote_number, l_effective_from, l_effective_to,
771 l_parent_code, l_parent_id;
772 CLOSE c_fetch_quote_number;
773
774 wf_engine.SetItemAttrText (itemtype => itemtype,
775 itemkey => itemkey,
776 aname => 'QUOTE_NUM',
777 avalue => l_quote_number);
778
779 wf_engine.SetItemAttrText (itemtype => itemtype,
780 itemkey => itemkey,
781 aname => 'EFFECTIVE_FROM',
782 avalue => l_effective_from);
783
784 wf_engine.SetItemAttrText (itemtype => itemtype,
785 itemkey => itemkey,
786 aname => 'EFFECTIVE_TO',
787 avalue => l_effective_to);
788
789 IF (l_parent_code = 'LEASEAPP') THEN
790 -- get the lease application details and not the quote details
791 OPEN get_lease_app(l_parent_id);
792 FETCH get_lease_app INTO l_quote_number;
793 CLOSE get_lease_app;
794
795 OPEN get_object_name('LAP');
796 FETCH get_object_name INTO l_object_name;
797 CLOSE get_object_name;
798
799 ELSE
800
801 OPEN get_object_name('LQ');
802 FETCH get_object_name INTO l_object_name;
803 CLOSE get_object_name;
804
805 END IF;
806
807 l_request_message := get_message('OKL_FE_REQUEST_APPROVAL_SUB',
808 l_object_name,
809 l_quote_number);
810 l_approved_message := get_message('OKL_FE_REQUEST_APPROVED_SUB',
811 l_object_name,
812 l_quote_number);
813 l_rejected_message := get_message('OKL_FE_REQUEST_REJECTED_SUB',
814 l_object_name,
815 l_quote_number);
816 l_reminder_message := get_message('OKL_FE_REMINDER_APPROVAL_SUB',
817 l_object_name,
818 l_quote_number);
819 wf_engine.SetItemAttrText(itemtype,
820 itemkey,
821 G_WF_ITM_APP_REQUEST_SUB,
822 l_request_message);
823 wf_engine.SetItemAttrText(itemtype,
824 itemkey,
825 G_WF_ITM_APP_REMINDER_SUB,
826 l_reminder_message);
827 wf_engine.SetItemAttrText(itemtype,
828 itemkey,
829 G_WF_ITM_APP_REMINDER_HEAD,
830 l_reminder_message);
831 wf_engine.SetItemAttrText(itemtype,
832 itemkey,
833 G_WF_ITM_APP_APPROVED_SUB,
834 l_approved_message);
835 wf_engine.SetItemAttrText(itemtype,
836 itemkey,
837 G_WF_ITM_APP_APPROVED_HEAD,
838 l_approved_message);
839 wf_engine.SetItemAttrText(itemtype,
840 itemkey,
841 G_WF_ITM_APP_REJECTED_SUB,
842 l_rejected_message);
843 wf_engine.SetItemAttrText(itemtype,
844 itemkey,
845 G_WF_ITM_APP_REJECTED_HEAD,
846 l_rejected_message);
847 wf_engine.SetItemAttrText(itemtype => itemtype,
848 itemkey => itemkey,
849 aname => G_WF_ITM_MESSAGE_BODY,
850 avalue => 'plsql:okl_lease_quote_workflow_pvt.get_quote_msg_doc/' ||
851 itemkey);
852 wf_engine.SetItemAttrText(itemtype => itemtype,
853 itemkey => itemkey,
854 aname => G_WF_ITM_MESSAGE_DESCR,
855 avalue => get_quote_msg_body(itemtype, itemkey));
856
857 OPEN fnd_user_csr;
858 FETCH fnd_user_csr INTO l_user_name ;
859 CLOSE fnd_user_csr;
860
861 -- get the application id
862
863 OPEN c_get_app_id_csr;
864 FETCH c_get_app_id_csr INTO l_application_id ;
865 CLOSE c_get_app_id_csr;
866
867 -- set the values of the approver and the requestor
868
869 wf_engine.SetItemAttrText(itemtype,
870 itemkey,
871 G_WF_ITM_APPROVER,
872 l_user_name);
873 wf_engine.SetItemAttrText(itemtype,
874 itemkey,
875 G_WF_ITM_REQUESTER,
876 l_user_name);
877 wf_engine.SetItemAttrText(itemtype,
878 itemkey,
879 G_WF_ITM_REQUESTER_ID,
880 fnd_global.user_id);
881 wf_engine.SetItemAttrText(itemtype,
882 itemkey,
883 G_WF_ITM_TRANSACTION_TYPE_ID,
884 itemtype);
885 wf_engine.SetItemAttrText(itemtype,
886 itemkey,
887 G_WF_ITM_TRANSACTION_ID,
888 l_quote_id);
889 wf_engine.SetItemAttrText(itemtype,
890 itemkey,
891 G_WF_ITM_APPLICATION_ID,
892 l_application_id);
893
894 resultout := 'COMPLETE:';
895 END IF;
896
897 -- CANCEL mode
898
899 IF (funcmode = 'CANCEL') THEN
900 resultout := 'COMPLETE:';
901 RETURN;
902 END IF;
903
904 -- TIMEOUT mode
905
906 IF (funcmode = 'TIMEOUT') THEN
907 resultout := 'COMPLETE:';
908 RETURN;
909 END IF;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 wf_core.context(G_PKG_NAME,
914 l_api_name,
915 itemtype,
916 itemkey,
917 actid,
918 funcmode);
919 RAISE;
920 END populate_quote_attr;
921 ------------------------------------------------------------------------------
922 -- PROCEDURE handle_approval
923 ------------------------------------------------------------------------------
924 -- Start of comments
925 --
926 -- Procedure Name : handle_approval
927 -- Description : this function handles the approval process by changing the status
928 -- Business Rules : this function handles the approval process by changing the status
929 -- Parameters :
930 -- Version : 1.0
931 -- History : viselvar created
932 --
933 -- End of comments
934 PROCEDURE handle_approval(itemtype IN VARCHAR2,
935 itemkey IN VARCHAR2,
936 actid IN NUMBER,
937 funcmode IN VARCHAR2,
938 resultout OUT NOCOPY VARCHAR2) IS
939
940 l_api_name CONSTANT VARCHAR2(30) := 'handle_approval';
941
942 l_quote_id OKL_LEASE_QUOTES_V.ID%TYPE;
943 l_result VARCHAR2(30);
944 lv_approval_status_ame VARCHAR2(30);
945 l_flag NUMBER;
946
947 lx_return_status VARCHAR2(1);
948 l_parent_object VARCHAR2(30);
949 l_parent_id NUMBER;
950 x_msg_count NUMBER;
951 x_msg_data VARCHAR2(4000);
952
953 --BUG 4951955 PAGARG Added cursor to obtain Lease App current status
954 CURSOR lse_app_dtls_csr(cp_lap_id NUMBER)
955 IS
956 SELECT LAP.REFERENCE_NUMBER
957 , LAP.APPLICATION_STATUS
958 FROM OKL_LEASE_APPLICATIONS_B LAP
959 WHERE LAP.ID = cp_lap_id;
960 lse_app_dtls_rec lse_app_dtls_csr%ROWTYPE;
961 BEGIN
962
963 IF (funcmode = 'RUN') THEN
964 l_result := wf_engine.GetItemAttrText(itemtype, itemkey, 'RESULT');
965 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
966 itemkey => itemkey,
967 aname => 'APPROVED_YN');
968
969 l_quote_id := wf_engine.GetItemAttrText(itemtype => itemtype,
970 itemkey => itemkey,
971 aname => 'QUOTE_ID');
972
973 OPEN get_parent_object(l_quote_id);
974 FETCH get_parent_object INTO l_parent_object, l_parent_id;
975 CLOSE get_parent_object;
976
977 --BUG 4951955 PAGARG If parent object is Lease App then obtain the status.
978 --If status is Pricing Submitted then set the flag as 1
979 --If parent object is other than Lease App then set the flag as 1
980 l_flag := 0;
981 IF l_parent_object = 'LEASEAPP'
982 THEN
983 OPEN lse_app_dtls_csr(l_parent_id);
984 FETCH lse_app_dtls_csr INTO lse_app_dtls_rec;
985 CLOSE lse_app_dtls_csr;
986 IF lse_app_dtls_rec.application_status = 'PR-SUBMITTED'
987 THEN
988 l_flag := 1;
989 END IF;
990 ELSE
991 l_flag := 1;
992 END IF;
993
994 -- if approved, then change the status
995 IF (l_result = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = 'Y') THEN
996 IF(l_flag = 1)
997 THEN
998 change_quote_status(p_quote_id => l_quote_id,
999 p_qte_status => 'PR-APPROVED',
1000 x_return_status => lx_return_status);
1001 END IF;
1002
1003 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1004 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1005 ELSIF lx_return_status = G_RET_STS_ERROR THEN
1006 RAISE OKL_API.G_EXCEPTION_ERROR;
1007 END IF;
1008
1009 --BUG 4951955 PAGARG Update Lease App status only if current status is
1010 --Pricing Submitted
1011 IF(l_parent_object = 'LEASEAPP'
1012 AND lse_app_dtls_rec.application_status = 'PR-SUBMITTED')
1013 THEN
1014 -- update the status of the lease application to Pricing approved
1015 OKL_LEASE_APP_PVT.SET_LEASE_APP_STATUS(
1016 p_api_version => 1.0
1017 ,p_init_msg_list => OKL_API.G_FALSE
1018 ,p_lap_id => l_parent_id
1019 ,p_lap_status => 'PR-APPROVED'
1020 ,x_return_status => lx_return_status
1021 ,x_msg_count => x_msg_count
1022 ,x_msg_data => x_msg_data);
1023
1024 IF(lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1025 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1026 ELSIF (lx_return_status = OKL_API.G_RET_STS_ERROR)THEN
1027 RAISE OKL_API.G_EXCEPTION_ERROR;
1028 END IF;
1029 END IF;
1030 ELSE
1031 IF(l_flag = 1)
1032 THEN
1033 change_quote_status(p_quote_id => l_quote_id,
1034 p_qte_status => 'PR-REJECTED',
1035 x_return_status => lx_return_status);
1036 END IF;
1037
1038 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1039 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1040 ELSIF lx_return_status = G_RET_STS_ERROR THEN
1041 RAISE OKL_API.G_EXCEPTION_ERROR;
1042 END IF;
1043
1044 --BUG 4951955 PAGARG Update Lease App status only if current status is
1045 --Pricing Submitted
1046 IF(l_parent_object = 'LEASEAPP'
1047 AND lse_app_dtls_rec.application_status = 'PR-SUBMITTED')
1048 THEN
1049 -- update the status of the lease application to Pricing Rejected
1050 OKL_LEASE_APP_PVT.SET_LEASE_APP_STATUS(
1051 p_api_version => 1.0
1052 ,p_init_msg_list => OKL_API.G_FALSE
1053 ,p_lap_id => l_parent_id
1054 ,p_lap_status => 'PR-REJECTED'
1055 ,x_return_status => lx_return_status
1056 ,x_msg_count => x_msg_count
1057 ,x_msg_data => x_msg_data);
1058
1059 IF(lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1060 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1061 ELSIF (lx_return_status = OKL_API.G_RET_STS_ERROR)THEN
1062 RAISE OKL_API.G_EXCEPTION_ERROR;
1063 END IF;
1064 END IF;
1065 END IF;
1066
1067 resultout := 'COMPLETE:';
1068 RETURN;
1069
1070 END IF;
1071
1072 -- CANCEL mode
1073 IF (funcmode = 'CANCEL') then
1074 resultout := 'COMPLETE:';
1075 RETURN;
1076 END IF;
1077 -- TIMEOUT mode
1078 IF (funcmode = 'TIMEOUT') then
1079 resultout := 'COMPLETE:';
1080 RETURN;
1081 END IF;
1082
1083 EXCEPTION
1084 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1085 --Lease Quote Parent Object Cursor
1086 IF get_parent_object%ISOPEN
1087 THEN
1088 CLOSE get_parent_object;
1089 END IF;
1090 --Lease App Details Cursor
1091 IF lse_app_dtls_csr%ISOPEN
1092 THEN
1093 CLOSE lse_app_dtls_csr;
1094 END IF;
1095 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
1096 RAISE;
1097
1098 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1099 --Lease Quote Parent Object Cursor
1100 IF get_parent_object%ISOPEN
1101 THEN
1102 CLOSE get_parent_object;
1103 END IF;
1104 --Lease App Details Cursor
1105 IF lse_app_dtls_csr%ISOPEN
1106 THEN
1107 CLOSE lse_app_dtls_csr;
1108 END IF;
1109 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
1110 RAISE;
1111
1112 WHEN OTHERS THEN
1113 --Lease Quote Parent Object Cursor
1114 IF get_parent_object%ISOPEN
1115 THEN
1116 CLOSE get_parent_object;
1117 END IF;
1118 --Lease App Details Cursor
1119 IF lse_app_dtls_csr%ISOPEN
1120 THEN
1121 CLOSE lse_app_dtls_csr;
1122 END IF;
1123 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1124 p_msg_name => G_UNEXPECTED_ERROR,
1125 p_token1 => G_SQLCODE_TOKEN,
1126 p_token1_value => sqlcode,
1127 p_token2 => G_SQLERRM_TOKEN,
1128 p_token2_value => sqlerrm);
1129
1130 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, actid, funcmode);
1131 RAISE;
1132
1133 END handle_approval;
1134 -- Bug 4741121 viselvar Modified End
1135
1136 END OKL_LEASE_QUOTE_WORKFLOW_PVT;