[Home] [Help]
PACKAGE BODY: APPS.OKL_SO_CREDIT_APP_WF
Source
1 PACKAGE BODY OKL_SO_CREDIT_APP_WF AS
2 /* $Header: OKLRCRQB.pls 120.13 2009/05/22 09:56:41 nikshah ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_SO_CREDIT_APP_WF';
5 G_INIT_VERSION CONSTANT NUMBER := 1.0;
6 L_MODULE FND_LOG_MESSAGES.MODULE%TYPE;
7 L_DEBUG_ENABLED VARCHAR2(10);
8 IS_DEBUG_PROCEDURE_ON BOOLEAN;
9 IS_DEBUG_STATEMENT_ON BOOLEAN;
10
11 SUBTYPE lsqv_rec_type IS OKL_LSQ_PVT.LSQV_REC_TYPE; --Bug 7140398
12 SUBTYPE lapv_rec_type IS OKL_LAP_PVT.LAPV_REC_TYPE; -- added for bug 7375141
13
14 FUNCTION exist_subscription(p_event_name IN VARCHAR2) RETURN VARCHAR2
15 -----------------------------------------------------------------------
16 -- Return 'Y' if there are some active subscription for the given event
17 -- Otherwise it returns 'N'
18 -----------------------------------------------------------------------
19 IS
20 CURSOR cu0 IS
21 SELECT 'Y'
22 FROM wf_event_subscriptions a,
23 wf_events b
24 WHERE a.event_filter_guid = b.guid
25 AND a.status = 'ENABLED'
26 AND b.name = p_event_name
27 AND rownum = 1;
28 l_yn VARCHAR2(1);
29 BEGIN
30 OPEN cu0;
31 FETCH cu0 INTO l_yn;
32 IF cu0%NOTFOUND THEN
33 l_yn := 'N';
34 END IF;
35 CLOSE cu0;
36 RETURN l_yn;
37 END;
38 -----------------------------------------------------------------------
39
40 PROCEDURE create_credit_app_event
41 ( p_quote_id IN NUMBER,
42 p_requestor_id IN NUMBER,
43 x_return_status OUT NOCOPY VARCHAR2)
44 IS
45 l_parameter_list wf_parameter_list_t;
46 l_key varchar2(240);
47 l_yn varchar2(1);
48 l_event_name varchar2(240) := 'oracle.apps.okl.so.creditapplication';
49 l_seq NUMBER ;
50 BEGIN
51
52 SAVEPOINT create_credit_app_event;
53
54 x_return_status := OKC_API.G_RET_STS_SUCCESS ;
55 -- Test if there are any active subscritions
56 -- if it is the case then execute the subscriptions
57 l_yn := exist_subscription(l_event_name);
58 IF l_yn = 'Y' THEN
59
60 --Get the item key
61 select okl_wf_item_s.nextval INTO l_seq FROM DUAL ;
62 l_key := l_event_name ||l_seq ;
63
64 --Set Parameters
65 wf_event.AddParameterToList('QUOTE_ID',TO_CHAR(p_quote_id),l_parameter_list);
66 wf_event.AddParameterToList('REQUESTED_ID',TO_CHAR(p_requestor_id),l_parameter_list);
67 --added by akrangan
68 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
69 -- Call it again if you have more than one parameter
70 -- Keep data type (text) only
71
72 -- Raise Event
73 -- It is overloaded function so use according to requirement
74 wf_event.raise( p_event_name => l_event_name
75 ,p_event_key => l_key
76 ,p_parameters => l_parameter_list);
77 l_parameter_list.DELETE;
78
79 ELSE
80 FND_MESSAGE.SET_NAME('OKL', 'OKL_NO_EVENT');
81 FND_MSG_PUB.ADD;
82 x_return_status := OKC_API.G_RET_STS_ERROR ;
83 END IF;
84 EXCEPTION
85
86 WHEN OTHERS THEN
87 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
88 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
89 FND_MSG_PUB.ADD;
90 ROLLBACK TO create_credit_app_event;
91 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
92
93 END create_credit_app_event;
94
95 -- Start of comments
96 --
97 -- Procedure Name : load_mess
98 -- Description : Private procedure to load messages into attributes
99 -- Business Rules :
100 -- Parameters :
101 -- Version : 1.0
102 -- End of comments
103
104 procedure load_mess( itemtype in varchar2,
105 itemkey in varchar2) is
106 i integer;
107 j integer;
108 begin
109 j := NVL(FND_MSG_PUB.Count_Msg,0);
110 if (j=0) then return; end if;
111 if (j>9) then j:=9; end if;
112 FOR I IN 1..J LOOP
113 wf_engine.SetItemAttrText (itemtype => itemtype,
114 itemkey => itemkey,
115 aname => 'MESSAGE'||i,
116 avalue => FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
117 END LOOP;
118 end;
119
120 -- sets requestor of credit request to the user who submitted the request.
121 procedure set_requestor( itemtype in varchar2
122 ,itemkey in varchar2
123 ,actid in number
124 ,funcmode in varchar2
125 ,resultout out NOCOPY varchar2) is
126
127 V_DUMMY varchar2(240);
128 l_user_id number;
129 l_request_id number;
130 L_REQUESTOR varchar2(240);
131 L_REQUESTOR_DNAME varchar2(240);
132
133 cursor C_REQUESTOR_DISPLAY_NAME(P_USER_ID in number) is
134 select user_name name,user_name display_name
135 from fnd_user
136 where user_id=P_USER_ID
137 and employee_id is null
138 union all
139 select
140 USR.USER_NAME name, PER.FULL_NAME display_name
141 from
142 PER_PEOPLE_F PER,
143 FND_USER USR
144 where trunc(SYSDATE)
145 between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE
146 and PER.PERSON_ID = USR.EMPLOYEE_ID
147 and USR.USER_ID = P_USER_ID;
148
149 -- get the user who submitted the credit request
150 cursor c_submitted_by (b_credit_request_id IN NUMBER) IS
151 select source_user_id
152 from ar_cmgt_credit_requests
153 where credit_request_id = b_credit_request_id;
154
155 begin
156 --
157 -- RUN mode - normal process execution
158 --
159 if (funcmode = 'RUN') then
160 --
161 -- Initiator/Initial
162 --
163 l_request_id := TO_NUMBER( wf_engine.GetItemAttrText (
164 itemtype => itemtype,
165 itemkey => itemkey,
166 aname => 'CREDIT_REQUEST_ID'));
167
168 open c_submitted_by(l_request_id);
169 fetch c_submitted_by INTO l_user_id;
170 close c_submitted_by;
171 wf_engine.SetItemAttrText (
172 itemtype => itemtype,
173 itemkey => itemkey,
174 aname => 'USER_ID',
175 avalue => L_USER_ID);
176
177 open C_REQUESTOR_DISPLAY_NAME(l_user_id);
178 fetch C_REQUESTOR_DISPLAY_NAME into L_REQUESTOR, L_REQUESTOR_DNAME;
179 close C_REQUESTOR_DISPLAY_NAME;
180 wf_engine.SetItemAttrText (
181 itemtype=> itemtype,
182 itemkey => itemkey,
183 aname => 'REQUESTOR',
184 avalue => L_REQUESTOR);
185 wf_engine.SetItemAttrText (
186 itemtype=> itemtype,
187 itemkey => itemkey,
188 aname => 'REQUESTOR_DNAME',
189 avalue => L_REQUESTOR_DNAME);
190
191 resultout := 'COMPLETE:';
192 return;
193 end if;
194 --
195 -- CANCEL mode
196 --
197 if (funcmode = 'CANCEL') then
198 --
199 resultout := 'COMPLETE:';
200 return;
201 --
202 end if;
203 --
204 -- TIMEOUT mode
205 --
206 if (funcmode = 'TIMEOUT') then
207 --
208 resultout := 'COMPLETE:';
209 return;
210 --
211 end if;
212 exception
213 when others then
214 wf_core.context('OKL_SO_CREDIT_APP_WF',
215 'SET_REQUESTOR',
216 itemtype,
217 itemkey,
218 to_char(actid),
219 funcmode);
220 raise;
221 end set_requestor;
222
223 -- sets approver of credit request to the user who submitted the request.
224 procedure set_approver ( itemtype in varchar2
225 ,itemkey in varchar2
226 ,actid in number
227 ,funcmode in varchar2
228 ,resultout out NOCOPY varchar2) IS
229
230 V_DUMMY varchar2(240);
231 l_user_id number;
232 l_request_id number;
233 L_APPROVER varchar2(240);
234 L_APPROVER_DNAME varchar2(240);
235
236 CURSOR C_APPROVER_DISPLAY_NAME IS
237 select role, name
238 from
239 (select 1 num, FND_PROFILE.VALUE('OKL_CREDIT_ANALYST') role,
240 NVL(PER.FULL_NAME, USR.USER_NAME) name
241 from FND_USER USR, PER_PEOPLE_F PER
242 where USR.USER_NAME = FND_PROFILE.VALUE('OKL_CREDIT_ANALYST')
243 and USR.EMPLOYEE_ID = PER.PERSON_ID(+)
244 and trunc(sysdate) between nvl(per.effective_start_date, trunc(sysdate)) and
245 nvl(per.effective_end_date, trunc(sysdate))
246 );
247
248 BEGIN
249 --
250 -- RUN mode - normal process execution
251 --
252 if (funcmode = 'RUN') then
253 open C_APPROVER_DISPLAY_NAME;
254 fetch C_APPROVER_DISPLAY_NAME into L_APPROVER,L_APPROVER_DNAME;
255 close C_APPROVER_DISPLAY_NAME;
256 wf_engine.SetItemAttrText (
257 itemtype=> itemtype,
258 itemkey => itemkey,
259 aname => 'APPROVER',
260 avalue => L_APPROVER);
261 wf_engine.SetItemAttrText (
262 itemtype=> itemtype,
263 itemkey => itemkey,
264 aname => 'APPROVER_DNAME',
265 avalue => L_APPROVER_DNAME);
266 --
267 resultout := 'COMPLETE:';
268 return;
269 --
270 end if;
271 --
272 -- CANCEL mode
273 --
274 if (funcmode = 'CANCEL') then
275 --
276 resultout := 'COMPLETE:';
277 return;
278 --
279 end if;
280 --
281 -- TIMEOUT mode
282 --
283 if (funcmode = 'TIMEOUT') then
284 --
285 resultout := 'COMPLETE:';
286 return;
287 --
288 end if;
289
290 EXCEPTION
291 when others then
292 wf_core.context('OKL_SO_CREDIT_APP_WF',
293 'SET_APPROVER',
294 itemtype,
295 itemkey,
296 to_char(actid),
297 funcmode);
298 raise;
299 END set_approver;
300
301 -- sets credit request attributes in workflow with details from the credit
302 -- request.
303 procedure credit_app_details ( itemtype in varchar2
304 ,itemkey in varchar2
305 ,actid in number
306 ,funcmode in varchar2
307 ,resultout out NOCOPY varchar2) is
308
309 v_dummy VARCHAR2(240);
310 l_request_id NUMBER;
311 l_quote_number VARCHAR2(120);
312 l_credit_req_number VARCHAR2(30);
313 l_credit_amount NUMBER;
314 l_requested_date DATE;
315 l_account_number VARCHAR2(30);
316 l_party_name VARCHAR2(360);
317 l_recomendation VARCHAR2(30);
318 l_recomend_value1 VARCHAR2(60);
319 l_recomend_value2 VARCHAR2(60);
320 l_okl_request_id NUMBER;
321 l_quote_id NUMBER;
322 l_request_status VARCHAR2(30);
323 l_currency VARCHAR2(10);
324 -- stub out the cursor sql, since this procedure is not used
325 -- Performance fix - bug#5484903
326 CURSOR c_credit_app_details (b_credit_request_id IN NUMBER) IS
327 SELECT
328 null quote_number
329 ,null credit_req_number
330 ,to_number(null) credit_amount
331 ,to_date(null) requested_date
332 ,null account_number
333 ,null party_name
334 ,to_number(null) id -- okl credit request id
335 ,to_number(null) quote_id
336 ,null status -- lookup code (not meaning)
337 ,null currency_code
338 from dual;
339
340
341 CURSOR c_recomendation (b_credit_request_id IN NUMBER) IS
342 SELECT
343 credit_recommendation
344 ,recommendation_value1
345 ,recommendation_value2
346 FROM ar_cmgt_cf_recommends
347 WHERE credit_request_id = b_credit_request_id;
348
349 begin
350 --
351 -- RUN mode - normal process execution
352 --
353 if (funcmode = 'RUN') then
354 --
355 -- Initiator/Initial
356 --
357 l_request_id := TO_NUMBER( wf_engine.GetItemAttrText (
358 itemtype => itemtype,
359 itemkey => itemkey,
360 aname => 'CREDIT_REQUEST_ID'));
361
362 open c_credit_app_details(l_request_id);
363 fetch c_credit_app_details INTO l_quote_number,
364 l_credit_req_number,
365 l_credit_amount,
366 l_requested_date,
367 l_account_number,
368 l_party_name,
369 l_okl_request_id,
370 l_quote_id,
371 l_request_status,
372 l_currency;
373 close c_credit_app_details;
374
375 -- open c_recomendation(l_request_id);
376 -- fetch c_recomendation INTO l_recomendation,
377 -- l_recomend_value1,
378 -- l_recomend_value2;
379 -- close c_recomendation;
380
381 FOR r_recomendation IN c_recomendation(l_request_id) LOOP
382
383 IF r_recomendation.credit_recommendation = 'APPROVE' THEN
384
385 l_recomendation := r_recomendation.credit_recommendation;
386 l_recomend_value1 := r_recomendation.recommendation_value1;
387 l_recomend_value2 := r_recomendation.recommendation_value2;
388
389 ELSIF r_recomendation.credit_recommendation = 'REJECT' THEN
390
391 l_recomendation := r_recomendation.credit_recommendation;
392 l_recomend_value1 := r_recomendation.recommendation_value1;
393 l_recomend_value2 := r_recomendation.recommendation_value2;
394
395 ELSE
396 null; -- do nothing, other scenarios not yet used.
397 -- l_recomendation := r_recomendation.credit_recommendation;
398 -- l_recomend_value1 := r_recomendation.recommendation_value1;
399 -- l_recomend_value2 := r_recomendation.recommendation_value2;
400
401 END IF;
402
403 END LOOP; -- r_recomendation
404
405 wf_engine.SetItemAttrText (
406 itemtype => itemtype,
407 itemkey => itemkey,
408 aname => 'QUOTE_NUM',
409 avalue => l_quote_number);
410 wf_engine.SetItemAttrText (
411 itemtype => itemtype,
412 itemkey => itemkey,
413 aname => 'CREDIT_REQ_NUM',
414 avalue => l_credit_req_number);
415 wf_engine.SetItemAttrText (
416 itemtype=> itemtype,
417 itemkey => itemkey,
418 aname => 'CREDIT_AMOUNT',
419 avalue => l_credit_amount);
420 wf_engine.SetItemAttrText (
421 itemtype => itemtype,
422 itemkey => itemkey,
423 aname => 'CREDIT_REQ_DATE',
424 avalue => l_requested_date);
425 wf_engine.SetItemAttrText (
426 itemtype=> itemtype,
427 itemkey => itemkey,
428 aname => 'CUST_ACCT_NUM',
429 avalue => l_account_number);
430 wf_engine.SetItemAttrText (
431 itemtype=> itemtype,
432 itemkey => itemkey,
433 aname => 'PARTY_NAME',
434 avalue => l_party_name);
435 wf_engine.SetItemAttrText (
436 itemtype => itemtype,
437 itemkey => itemkey,
438 aname => 'CREDIT_RECOMENDATION',
439 avalue => l_recomendation);
440 wf_engine.SetItemAttrText (
441 itemtype=> itemtype,
442 itemkey => itemkey,
443 aname => 'RECOMEND_VALUE1',
444 avalue => l_recomend_value1);
445 wf_engine.SetItemAttrText (
446 itemtype=> itemtype,
447 itemkey => itemkey,
448 aname => 'RECOMEND_VALUE2',
449 avalue => l_recomend_value2);
450 wf_engine.SetItemAttrText (
451 itemtype => itemtype,
452 itemkey => itemkey,
453 aname => 'ID',
454 avalue => l_okl_request_id);
455 wf_engine.SetItemAttrText (
456 itemtype => itemtype,
457 itemkey => itemkey,
458 aname => 'QUOTE_ID',
459 avalue => l_quote_id);
460 wf_engine.SetItemAttrText (
461 itemtype => itemtype,
462 itemkey => itemkey,
463 aname => 'REQUEST_STATUS',
464 avalue => l_request_status);
465 wf_engine.SetItemAttrText (
466 itemtype => itemtype,
467 itemkey => itemkey,
468 aname => 'CURRENCY',
469 avalue => l_currency);
470 --
471 IF l_recomendation = 'APPROVE' THEN
472 resultout := 'COMPLETE:T';
473 return;
474 ELSE
475 resultout := 'COMPLETE:F';
476 return;
477 END IF;
478
479 --
480 end if;
481 --
482 -- CANCEL mode
483 --
484 if (funcmode = 'CANCEL') then
485 --
486 resultout := 'COMPLETE:';
487 return;
488 --
489 end if;
490 --
491 -- TIMEOUT mode
492 --
493 if (funcmode = 'TIMEOUT') then
494 --
495 resultout := 'COMPLETE:';
496 return;
497 --
498 end if;
499
500 exception
501 when others then
502 wf_core.context('OKL_SO_CREDIT_APP_WF',
503 'CREDIT_APP_DETAILS',
504 itemtype,
505 itemkey,
506 to_char(actid),
507 funcmode);
508 raise;
509 end credit_app_details;
510
511 --------------------------------------------------------------------------------
512 -- CREATE_CREDIT_LINE --
513 --------------------------------------------------------------------------------
514 procedure CREATE_CREDIT_LINE ( itemtype in varchar2,
515 itemkey in varchar2,
516 actid in number,
517 funcmode in varchar2,
518 resultout out nocopy varchar2 )IS
519
520 -- Variables --
521 l_party_id NUMBER;
522 l_customer_name VARCHAR2(360);
523 l_cust_acct_id NUMBER;
524 l_cust_acct_num VARCHAR2(30);
525 l_site_use_id NUMBER;
526 l_limit_currency VARCHAR2(15);
527 l_limit_amount NUMBER;
528 l_trx_currency VARCHAR2(15);
529 l_trx_amount NUMBER;
530 --
531 l_quote_id NUMBER;
532 l_credit_req_number VARCHAR2(90);
533 l_credit_req_id NUMBER;
534 l_credit_amount NUMBER;
535 l_approved_by NUMBER;
536 l_approved_date DATE;
537 l_effective_from DATE; -- application date
538 l_effective_to DATE; --
539 l_status VARCHAR2(30);
540 l_credit_khr_id NUMBER;
541 l_credit_khr_desc VARCHAR2(200);
542 l_org_id NUMBER;
543 l_currency_code VARCHAR2(15);
544 --
545 l_key VARCHAR2(240); -- := p_event.GetEventKey();
546 l_request_id NUMBER; -- := p_event.GetValueForParameter('CREDIT_REQUEST_ID');
547 --
548 l_credit_recommendation VARCHAR2(30);
549 l_recommendation_value1 VARCHAR2(60);
550 l_recommendation_value2 VARCHAR2(60);
551 --
552 lx_chr_rec l_chr_rec;
553 l_crqv_rec OKL_CRQ_PVT.crqv_rec_type;
554 lx_crqv_rec OKL_CRQ_PVT.crqv_rec_type;
555 l_crqv_tbl OKL_CRQ_PVT.crqv_tbl_type;
556 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
557 l_activity_rec JTF_IH_PUB.activity_rec_type;
558 lx_interaction_id NUMBER;
559 l_action_id NUMBER;
560 lx_activity_id NUMBER;
561 l_login_id NUMBER;
562 l_resource_id NUMBER;
563 l_requestor_id NUMBER;
564 l_capital_amount NUMBER;
565 l_okl_credit_req NUMBER;
566
567 l_user_id NUMBER;
568 l_resp_id NUMBER;
569 l_resp_appl_id NUMBER;
570 L_SUBMITTED_BY NUMBER;
571
572 ls_request_num VARCHAR2(80);
573 l_seq NUMBER;
574
575 l_credit_k_number VARCHAR2(100);
576 l_amount NUMBER ;
577 l_object1_id OKC_K_PARTY_ROLES_V.object1_id1%TYPE;
578 l_object2_id2 OKC_K_PARTY_ROLES_V.object1_id2%TYPE;
579 l_jtot_object_code OKC_K_PARTY_ROLES_V.JTOT_OBJECT1_CODE%TYPE;
580 x_chr_id NUMBER;
581 l_crqv_rec_type OKL_CREDIT_REQUEST_PUB.crqv_rec_type ;
582 x_crqv_rec_type OKL_CREDIT_REQUEST_PUB.crqv_rec_type ;
583 l_rlg_id NUMBER;
584 l_rulv_rec OKL_RULE_PUB.rulv_rec_type ;
585 x_rulv_rec OKL_RULE_PUB.rulv_rec_type ;
586 l_rgpv_rec OKL_RULE_PUB.rgpv_rec_type ;
587 x_rgpv_rec OKL_RULE_PUB.rgpv_rec_type ;
588 --p_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
589 --p_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
590 --x_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
591 --x_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
592 p_clev_tbl OKL_CREDIT_PUB.clev_tbl_type;
593 p_klev_tbl OKL_KLE_PVT.klev_tbl_type;
594 x_clev_tbl OKL_CREDIT_PUB.clev_tbl_type;
595 x_klev_tbl OKL_KLE_PVT.klev_tbl_type;
596
597 x_msg_data VARCHAR2(2000);
598 x_msg_count NUMBER := 0 ;
599 l_api_version NUMBER := 1 ;
600 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
601
602 l_inv_org_id NUMBER;
603
604 CURSOR l_okl_lse_id IS
605 SELECT id
606 FROM OKC_LINE_STYLES_b
607 WHERE LTY_CODE = 'FREE_FORM';
608
609 -- get party name
610 CURSOR c_party_name(b_party_id IN NUMBER) IS
611 SELECT name
612 FROM okx_parties_v
613 WHERE id1 = b_party_id;
614
615 -- get credit request info
616 CURSOR c_credit_request (b_credit_request_id IN NUMBER) IS
617 SELECT
618 limit_currency
619 ,limit_amount
620 ,trx_currency
621 ,trx_amount
622 ,party_id
623 ,cust_account_id
624 ,site_use_id
625 ,application_date
626 FROM ar_cmgt_credit_requests
627 WHERE credit_request_id = b_credit_request_id;
628
629 -- credit/contract relationship
630 CURSOR c_okl_credit_request (b_credit_request_id IN NUMBER) IS
631 SELECT
632 quote_id -- this is actually the contract id.
633 ,credit_req_number
634 ,credit_req_id
635 ,credit_amount
636 ,approved_by
637 ,approved_date
638 ,status
639 ,credit_khr_id
640 ,currency_code
641 ,org_id
642 ,created_by
643 FROM okl_credit_requests
644 WHERE credit_req_id = b_credit_request_id;
645
646 -- cursor to get inventory org id of the sales quote
647 CURSOR c_get_inv_org_id (p_qte_id IN NUMBER) IS
648 SELECT INV_ORGANIZATION_ID FROM OKC_K_HEADERS_B
649 WHERE ID = p_qte_id;
650
651 CURSOR c_recommendation (b_request_id IN NUMBER) IS
652 SELECT credit_recommendation,
653 recommendation_value1,
654 recommendation_value2
655 FROM ar_cmgt_cf_recommends
656 WHERE credit_request_id = b_request_id ;
657
658 CURSOR get_resource_id(b_user_id NUMBER) IS
659 SELECT resource_id
660 FROM jtf_rs_resource_extns
661 WHERE user_id = b_user_id;
662
663 CURSOR c_approver_user_id (b_user_name IN VARCHAR2) IS
664 SELECT user_id
665 FROM fnd_user
666 WHERE user_name = b_user_name;
667
668 BEGIN
669 --
670 -- RUN mode - normal process execution
671 --
672 if (funcmode = 'RUN') then
673
674 l_request_id := TO_NUMBER( wf_engine.GetItemAttrText (
675 itemtype => itemtype,
676 itemkey => itemkey,
677 aname => 'CREDIT_REQUEST_ID'));
678
679 IF (wf_engine.GetItemAttrText(itemtype,itemkey,'TLOC_END_DATE') is NULL) then
680 resultout := 'ERROR: Must enter an End Date for Term Line of Credit ';
681 --resultout := 'COMPLETE:F';
682 ELSE
683 l_effective_to := wf_engine.GetItemAttrText(itemtype,itemkey,'TLOC_END_DATE');
684 -- assign a val just in case. should not go here
685 IF l_effective_to IS NULL THEN
686 l_effective_to := sysdate + 30;
687 END IF;
688 END IF;
689
690 OPEN c_credit_request(l_request_id);
691 FETCH c_credit_request INTO l_limit_currency, l_limit_amount,
692 l_trx_currency, l_trx_amount,
693 l_party_id, l_cust_acct_id, l_site_use_id,
694 l_effective_from;
695 IF c_credit_request%NOTFOUND THEN
696 -- add message -- credit request not found
697 CLOSE c_credit_request;
698 resultout := 'ERROR: unable to find credit request';
699 return;
700 END IF;
701 CLOSE c_credit_request;
702
703 OPEN c_okl_credit_request (l_request_id);
704 FETCH c_okl_credit_request INTO l_quote_id, l_credit_req_number, l_credit_req_id,
705 l_credit_amount, l_approved_by, l_approved_date, l_status, l_credit_khr_id,
706 l_currency_code, l_org_id, l_user_id;
707 IF c_okl_credit_request%NOTFOUND THEN
708 -- add message -- credit request not found
709 CLOSE c_okl_credit_request;
710 resultout := 'ERROR: unable to find credit request';
711 return;
712 END IF;
713 CLOSE c_okl_credit_request;
714
715 OPEN c_get_inv_org_id (l_quote_id);
716 FETCH c_get_inv_org_id INTO l_inv_org_id;
717 IF c_get_inv_org_id%NOTFOUND THEN
718 -- add message -- credit request not found
719 CLOSE c_get_inv_org_id;
720 resultout := 'ERROR: unable to find inventory orgainzation id';
721 return;
722 END IF;
723 CLOSE c_get_inv_org_id;
724
725 OPEN c_approver_user_id(FND_PROFILE.VALUE('OKL_CREDIT_ANALYST'));
726 FETCH c_approver_user_id INTO l_approved_by;
727 close c_approver_user_id;
728
729 -- get credit checklist.
730 -- will pass checklist as a parameter from the submit credit request UI
731 -- in the future.
732
733 l_object2_id2 := '#';
734 l_jtot_object_code := 'OKX_PARTY';
735 l_credit_khr_desc := 'Created as a result of credit request '||l_request_id;
736
737 OPEN c_party_name(l_party_id);
738 FETCH c_party_name INTO l_customer_name;
739 CLOSE c_party_name;
740
741 -- 3. CREATE CREDIT LINE CONTRACT
742 OKL_CREDIT_PUB.create_credit(
743 p_api_version => l_api_version,
744 p_init_msg_list => OKC_API.G_TRUE,
745 x_return_status => x_return_status,
746 x_msg_count => x_msg_count,
747 x_msg_data => x_msg_data,
748 p_contract_number => null,
749 p_description => l_credit_khr_desc,
750 p_customer_id1 => l_party_id,
751 p_customer_id2 => l_object2_id2, -- '#'
752 p_customer_code => l_jtot_object_code, -- 'OKX_PARTY'
753 p_customer_name => l_customer_name,
754 p_effective_from => l_effective_from,
755 p_effective_to => l_effective_to,
756 p_currency_code => l_currency_code,
757 p_currency_conv_type => null,
758 p_currency_conv_rate => null,
759 p_currency_conv_date => null,
760 -- the following two parameters are post 12/31 --
761 p_credit_ckl_id => null, --G_CREDIT_CHKLST_TPL,
762 p_funding_ckl_id => null,
763 -- added customer account details due to rules migration
764 p_cust_acct_id => l_cust_acct_id, -- 11.5.10 rule migration project
765 p_cust_acct_number => l_cust_acct_num, -- 11.5.10 rule migration project
766 p_revolving_credit_yn => 'N', -- Need to find
767 p_sts_code => 'NEW',-- Need to verify
768 p_org_id => l_org_id ,
769 p_organization_id => l_inv_org_id,
770 p_source_chr_id => null,
771 x_chr_id => x_chr_id);
772
773 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
774 resultout := 'ERROR:' || x_msg_data;
775 return;
776 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
777 resultout := 'ERROR:FALSE';
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
779 END IF;
780
781 /**** Customer Account rule now created on header ******************
782 *
783 *-- create customer account rule group and rule
784 * IF l_cust_acct_id IS NOT NULL THEN
785 *
786 --5 CREATE RULE GROUP
787 l_rgpv_rec.dnz_chr_id := x_chr_id ;
788 l_rgpv_rec.rgp_type := 'KRG' ;
789 l_rgpv_rec.rgd_code := 'LACAN' ;
790 l_rgpv_rec.chr_id := x_chr_id;
791
792 okl_rule_pub.create_rule_group(
793 p_api_version =>l_api_version,
794 p_init_msg_list => OKC_API.G_TRUE,
795 x_return_status =>x_return_status,
796 x_msg_count =>x_msg_count,
797 x_msg_data =>x_msg_data,
798 p_rgpv_rec => l_rgpv_rec,
799 x_rgpv_rec => x_rgpv_rec);
800
801
802 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
803 resultout := 'ERROR:' || x_msg_data;
804 return;
805 -- RAISE FND_API.G_EXC_ERROR;
806 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
807 resultout := 'ERROR:FALSE';
808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809 END IF;
810
811 -- 6. CREATE RULE
812
813 l_rulv_rec.rgp_id := x_rgpv_rec.id ;
814 l_rulv_rec.DNZ_CHR_ID := x_chr_id ;
815 l_rulv_rec.rule_information_category := 'CAN';
816 l_rulv_rec.object1_id1 := l_cust_acct_id;
817 l_rulv_rec.object1_id2 := '#';
818 l_rulv_rec.jtot_object1_code := 'OKX_CUSTACCT';
819 l_rulv_rec.WARN_YN := 'N';
820 l_rulv_rec.STD_TEMPLATE_YN := 'N';
821
822 okl_rule_pub.create_rule(
823 p_api_version =>l_api_version,
824 p_init_msg_list => OKC_API.G_TRUE,
825 x_return_status =>x_return_status,
826 x_msg_count =>x_msg_count,
827 x_msg_data =>x_msg_data,
828 p_rulv_rec =>l_rulv_rec,
829 x_rulv_rec =>x_rulv_rec);
830
831 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
832 resultout := 'ERROR:' || x_msg_data;
833 return;
834 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
835 * resultout := 'ERROR:FALSE';
836 * RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 * END IF;
838 * END IF; -- create customer account
839 *
840 *************************************************************************/
841
842 -- CREATE contract line
843 p_clev_tbl(0).dnz_chr_id := x_chr_id;
844
845 OPEN l_okl_lse_id;
846 FETCH l_okl_lse_id INTO p_clev_tbl(0).lse_id ;
847 IF( l_okl_lse_id%NOTFOUND) THEN
848 resultout := 'ERROR: No Line Style';
849 return;
850 END IF;
851 CLOSE l_okl_lse_id ;
852
853 p_clev_tbl(0).chr_id := x_chr_id;
854 p_clev_tbl(0).LINE_NUMBER := 1;
855 p_clev_tbl(0).STS_CODE := 'NEW';
856 p_clev_tbl(0).DISPLAY_SEQUENCE := 1;
857 p_clev_tbl(0).EXCEPTION_YN := 'N';
858 p_clev_tbl(0).START_DATE := l_effective_from;
859 p_klev_tbl(0).amount := l_credit_amount;
860 p_klev_tbl(0).credit_nature := 'NEW';
861 p_klev_tbl(0).OBJECT_VERSION_NUMBER := 1 ;
862
863 OKL_CREDIT_PUB.create_credit_limit(
864 p_api_version => l_api_version,
865 p_init_msg_list => OKC_API.G_TRUE,
866 x_return_status => x_return_status,
867 x_msg_count => x_msg_count,
868 x_msg_data => x_msg_data,
869 p_clev_tbl => p_clev_tbl,
870 p_klev_tbl => p_klev_tbl,
871 x_clev_tbl => x_clev_tbl,
872 x_klev_tbl => x_klev_tbl);
873
874 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
875 resultout := 'ERROR:' || x_msg_data;
876 return;
877 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
878 resultout := 'ERROR:FALSE';
879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880 END IF;
881
882 -- 7. Change Status of the credit request
883 l_okl_credit_req := wf_engine.GetItemAttrNumber (
884 itemtype => itemtype,
885 itemkey => itemkey,
886 aname => 'ID');
887
888 SELECT OBJECT_VERSION_NUMBER
889 INTO l_crqv_rec_type.OBJECT_VERSION_NUMBER
890 FROM OKL_CREDIT_REQUESTS
891 WHERE ID = l_okl_credit_req ;
892
893 l_crqv_rec_type.ID := l_okl_credit_req ;
894 l_crqv_rec_type.STATUS := 'APPROVED';
895 l_crqv_rec_type.CREDIT_KHR_ID := x_chr_id ;
896 l_crqv_rec_type.approved_by := l_approved_by;
897 l_crqv_rec_type.approved_date := SYSDATE;
898
899 wf_engine.SetItemAttrText (
900 itemtype => itemtype,
901 itemkey => itemkey,
902 aname => 'REQUEST_STATUS',
903 avalue => l_crqv_rec_type.STATUS);
904
905 okl_credit_request_pub.update_credit_request(
906 p_api_version => l_api_version
907 ,p_init_msg_list => 'T'
908 ,x_return_status =>x_return_status
909 ,x_msg_count => x_msg_count
910 ,x_msg_data =>x_msg_data
911 ,p_crqv_rec =>l_crqv_rec_type
912 ,x_crqv_rec => x_crqv_rec_type);
913
914 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
915 resultout := 'ERROR:' || x_msg_data;
916 return;
917 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
918 resultout := 'ERROR:NO';
919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
920 END IF;
921
922 -- create Interaction History.
923 l_login_id := fnd_global.login_id;
924 l_resp_id := fnd_global.RESP_ID;
925
926 OPEN get_resource_id (l_user_id);
927 FETCH get_resource_id INTO l_resource_id;
928 CLOSE get_resource_id;
929
930 --
931 -- will not need all these --
932 -- l_interaction_rec.interaction_id := ;
933 --l_interaction_rec.reference_form := null;
934 --l_interaction_rec.follow_up_action := null;
935 --l_interaction_rec.duration := 0;
936 --l_interaction_rec.inter_interaction_duration := 0;
937 --l_interaction_rec.non_productive_time_amount := null;
938 --l_interaction_rec.preview_time_amount := null;
939 --l_interaction_rec.productive_time_amount := 0;
940 l_interaction_rec.start_date_time := sysdate;
941 --l_interaction_rec.end_date_time := null;
942 --l_interaction_rec.wrapup_time_amount := null;
943 l_interaction_rec.handler_id := 540; -- OKL - Lease Management
944 --l_interaction_rec.source_code_id := null;
945 --l_interaction_rec.source_code := null;
946 --l_interaction_rec.script_id := null;
947 l_interaction_rec.resource_id := l_resource_id;
948 l_interaction_rec.outcome_id := 10 ; -- Request Processed
949 l_interaction_rec.party_id := l_party_id;
950 --l_interaction_rec.result_id := null; --6;
951 --l_interaction_rec.reason_id := null;
952 --l_interaction_rec.parent_id := null;
953 --l_interaction_rec.object_id := null;
954 --l_interaction_rec.object_type := null;
955
956 jtf_ih_pub.open_interaction(
957 p_api_version => l_api_version,
958 p_init_msg_list => okl_api.g_true,
959 p_commit => okl_api.g_false,
960 p_user_id => l_user_id,
961 p_login_id => l_login_id,
962 x_return_status => x_return_status,
963 x_msg_count => x_msg_count,
964 x_msg_data => x_msg_data,
965 p_interaction_rec => l_interaction_rec,
966 x_interaction_id => lx_interaction_id);
967
968 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
969 resultout := 'ERROR:NO';
970 return;
971 -- RAISE FND_API.G_EXC_ERROR;
972 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
973 resultout := 'ERROR:NO';
974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
975 END IF;
976
977 -- l_activity_rec.activity_id :=
978 --l_activity_rec.duration := 0;
979 l_activity_rec.cust_account_id := lx_chr_rec.cust_acct_id;
980 l_activity_rec.cust_org_id := lx_chr_rec.org_id;
981 --l_activity_rec.role := null;
982 l_activity_rec.start_date_time := sysdate;
983 --l_activity_rec.task_id := null;
984 --l_activity_rec.doc_id := null;
985 --l_activity_rec.doc_ref := null;
986 --l_activity_rec.doc_source_object_name := null;
987 --l_activity_rec.media_id := null;
988 l_activity_rec.action_item_id := 87; -- Credit Request
989 l_activity_rec.interaction_id := lx_interaction_id;
990 l_activity_rec.outcome_id := 10;
991 --l_activity_rec.result_id := null;
992 --l_activity_rec.reason_id := null;
993 --l_activity_rec.description := null;
994 l_activity_rec.action_id := 81; -- Approved
995 --l_activity_rec.interaction_action_type := null;
996 --l_activity_rec.object_id := null;
997 --l_activity_rec.object_type := null;
998 --l_activity_rec.source_code_id := null;
999 --l_activity_rec.source_code := null;
1000
1001 jtf_ih_pub.add_activity(
1002 p_api_version => l_api_version,
1003 p_init_msg_list => okl_api.g_true,
1004 p_commit => okl_api.g_false,
1005 p_user_id => l_user_id,
1006 p_login_id => l_login_id,
1007 x_return_status => x_return_status,
1008 x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data,
1010 p_activity_rec => l_activity_rec,
1011 x_activity_id => lx_activity_id);
1012
1013 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
1014 resultout := 'ERROR:NO';
1015 return;
1016 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1017 resultout := 'ERROR:NO';
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019 END IF;
1020
1021 jtf_ih_pub.CLOSE_INTERACTION (
1022 p_api_version => l_api_version,
1023 p_init_msg_list => okl_api.g_true,
1024 p_commit => okl_api.g_false,
1025 P_RESP_APPL_ID => l_resp_appl_id,
1026 P_RESP_ID => l_resp_id,
1027 P_USER_ID => l_user_id,
1028 P_LOGIN_ID => l_login_id,
1029 x_return_status => x_return_status,
1030 x_msg_count => x_msg_count,
1031 x_msg_data => x_msg_data,
1032 P_INTERACTION_ID => lx_interaction_id);
1033
1034 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
1035 resultout := 'ERROR:NO';
1036 return;
1037 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1038 resultout := 'ERROR:NO';
1039 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1040 END IF;
1041
1042 resultout := 'COMPLETE:YES';
1043 return;
1044 --
1045 END IF;
1046 --
1047 -- CANCEL mode
1048 --
1049 if (funcmode = 'CANCEL') then
1050 --
1051 resultout := 'COMPLETE:YES';
1052 return;
1053 --
1054 end if;
1055 --
1056 -- TIMEOUT mode
1057 --
1058 if (funcmode = 'TIMEOUT') then
1059 resultout := 'COMPLETE:NO';
1060 return;
1061 --
1062 end if;
1063
1064 exception
1065 when others then
1066 wf_core.context('OKL_SO_CREDIT_WF',
1067 'CREATE_CREDIT_LINE',itemtype,
1068 itemkey,
1069 to_char(actid),
1070 funcmode);
1071 raise;
1072 END CREATE_CREDIT_LINE;
1073
1074 ------------------------------------------------------------------------------
1075 -- PROCEDURE update_status
1076 ------------------------------------------------------------------------------
1077 -- Start of comments
1078 --
1079 -- Procedure Name : update_status
1080 -- Description : This procedure updates the lease application status.
1081 -- Business Rules : This procedure updates the lease application status.
1082 -- Parameters :
1083 -- Version : 1.0
1084 -- History : 26-Oct-2005 PAGARG updated
1085 -- 19-Nov-2008 gboomina Bug 6971371 Modified the logic to update
1086 -- Lease App based on status of Credit Recommendation
1087
1088 --
1089 -- End of comments
1090 PROCEDURE UPDATE_STATUS(
1091 itemtype IN VARCHAR2,
1092 itemkey IN VARCHAR2,
1093 actid IN NUMBER,
1094 funcmode IN VARCHAR2,
1095 resultout OUT NOCOPY VARCHAR2)
1096 IS
1097 -- Variables Declarations
1098 l_api_version CONSTANT NUMBER DEFAULT G_INIT_VERSION;
1099 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'UPDATE_STATUS';
1100 l_return_status VARCHAR2(1);
1101 x_msg_data VARCHAR2(2000);
1102 x_msg_count NUMBER := 0;
1103 l_lap_status VARCHAR2(30);
1104 l_prospect_id NUMBER;
1105 l_cust_acct_id NUMBER;
1106 l_org_id NUMBER;
1107
1108 --Bug 6651146 PAGARG use base table instead of secured synonym to obtain
1109 --lease app id
1110 CURSOR credit_req_csr(cp_credit_req_id IN NUMBER)
1111 IS
1112 SELECT CCR.SOURCE_COLUMN1
1113 , LAB.CREATED_BY
1114 , LAB.PROSPECT_ID
1115 , LAB.CUST_ACCT_ID
1116 , LAB.ORG_ID
1117 FROM AR_CMGT_CREDIT_REQUESTS CCR
1118 , OKL_LEASE_APPS_ALL_B LAB
1119 WHERE CCR.SOURCE_COLUMN3 = 'LEASEAPP'
1120 AND CCR.SOURCE_COLUMN1 = LAB.ID
1121 AND CCR.CREDIT_REQUEST_ID = cp_credit_req_id;
1122
1123 l_credit_req_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
1124 l_lap_id OKL_LEASE_APPLICATIONS_B.ID%TYPE;
1125 l_created_by OKL_LEASE_APPLICATIONS_B.CREATED_BY%TYPE;
1126 -- Bug#4741121 - viselvar - Modified - Start
1127 l_parameter_list wf_parameter_list_t;
1128 p_event_name VARCHAR2(240) := 'oracle.apps.okl.sales.leaseapplication.record_credit_decision';
1129 -- Bug#4741121 - viselvar - Modified - End
1130
1131
1132 CURSOR credit_req_sts_csr(p_credit_request_id IN NUMBER)
1133 IS
1134 SELECT credit_recommendation, status
1135 FROM ar_cmgt_cf_recommends
1136 WHERE credit_request_id = p_credit_request_id
1137 AND credit_recommendation IN ('REJECT','APPROVE');
1138
1139 l_request_status ar_cmgt_cf_recommends.status%TYPE;
1140 l_cr_recom ar_cmgt_cf_recommends.credit_recommendation%TYPE;
1141
1142 --Bug 7140398 START
1143 CURSOR get_main_offer_csr(cp_lap_id NUMBER) IS
1144 SELECT ID
1145 FROM OKL_LEASE_QUOTES_B
1146 WHERE PARENT_OBJECT_ID = cp_lap_id
1147 AND PARENT_OBJECT_CODE = 'LEASEAPP'
1148 AND PRIMARY_QUOTE = 'Y';
1149
1150 l_lsq_id okl_lease_quotes_b.id%type;
1151
1152 CURSOR is_counter_offer_exist(cp_lap_id NUMBER) IS
1153 SELECT DISTINCT STATUS
1154 FROM okl_lease_quotes_b
1155 WHERE status = 'CR-RECOMMENDATION'
1156 AND parent_object_code = 'LEASEAPP'
1157 AND PARENT_OBJECT_ID = cp_lap_id;
1158
1159 l_counter_offer_exist VARCHAR2(1) := 'N';
1160 l_status okl_lease_quotes_b.status%type;
1161 l_lsqv_rec lsqv_rec_type;
1162 x_lsqv_rec lsqv_rec_type;
1163 --Bug 7140398 END
1164
1165 -- Added for bug 7375141
1166 l_lapv_rec lapv_rec_type;
1167 x_lapv_rec lapv_rec_type;
1168 l_exp_date DATE;
1169
1170 BEGIN
1171 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1172 L_MODULE := 'OKL.PLSQL.OKL_SO_CREDIT_APP_WF.UPDATE_STATUS';
1173
1174 -- check for logging on PROCEDURE level
1175 L_DEBUG_ENABLED := NVL(OKL_DEBUG_PUB.CHECK_LOG_ENABLED, 'N');
1176 IS_DEBUG_PROCEDURE_ON := NVL(OKL_DEBUG_PUB.CHECK_LOG_ON(L_MODULE, FND_LOG.LEVEL_PROCEDURE), FALSE);
1177 -- check for logging on STATEMENT level
1178 IS_DEBUG_STATEMENT_ON := NVL(OKL_DEBUG_PUB.CHECK_LOG_ON(L_MODULE, FND_LOG.LEVEL_STATEMENT), FALSE);
1179
1180 --
1181 -- RUN mode - normal process execution
1182 --
1183 IF (funcmode = 'RUN')
1184 THEN
1185 l_credit_req_id := wf_engine.GetItemAttrNumber (
1186 itemtype => itemtype
1187 ,itemkey => itemkey
1188 ,aname => 'CREDIT_REQUEST_ID');
1189
1190 OPEN credit_req_csr(l_credit_req_id);
1191 FETCH credit_req_csr INTO l_lap_id
1192 , l_created_by
1193 , l_prospect_id
1194 , l_cust_acct_id
1195 , l_org_id;
1196 CLOSE credit_req_csr;
1197
1198 --Bug 6651146 PAGARG set the org of lease app in session for update api
1199 --to work properly.
1200 MO_GLOBAL.set_policy_context('S', l_org_id);
1201
1202 OPEN credit_req_sts_csr(l_credit_req_id);
1203 FETCH credit_req_sts_csr INTO l_cr_recom, l_request_status;
1204 CLOSE credit_req_sts_csr;
1205
1206 IF(l_cr_recom = 'APPROVE' AND l_request_status = 'I')
1207 THEN
1208 --If approve recommendation is added in status I then
1209 --update lease app status to Credit Approved
1210 l_lap_status := 'CR-APPROVED';
1211 ELSIF(l_cr_recom = 'REJECT' AND l_request_status = 'I')
1212 THEN
1213 --If reject recommendation is added in status I then
1214 --update lease app status to Credit Rejected
1215 l_lap_status := 'CR-REJECTED';
1216 ELSIF ((l_cr_recom = 'REJECT' OR l_cr_recom = 'APPROVE') AND
1217 l_request_status = 'R')
1218 THEN
1219 --If reject or approve recommendation is added and is in status R then
1220 --update lease app status to Recommendations Not Approved
1221 l_lap_status := 'RECOM_NOT_APPROVED';
1222 END IF;
1223
1224 --Bug 7140398 START
1225 l_counter_offer_exist := 'N';
1226 OPEN is_counter_offer_exist (l_lap_id);
1227 FETCH is_counter_offer_exist INTO l_status;
1228 IF is_counter_offer_exist%NOTFOUND THEN
1229 l_counter_offer_exist := 'N';
1230 else
1231 l_counter_offer_exist := 'Y';
1232 END IF;
1233 CLOSE is_counter_offer_exist;
1234
1235 IF (l_cr_recom = 'REJECT' OR l_counter_offer_exist = 'Y' OR l_lap_status = 'RECOM_NOT_APPROVED') THEN
1236 OPEN get_main_offer_csr (l_lap_id);
1237 FETCH get_main_offer_csr INTO l_lsq_id;
1238 CLOSE get_main_offer_csr;
1239 l_lsqv_rec.id := l_lsq_id;
1240
1241 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1242 THEN
1243 OKL_DEBUG_PUB.LOG_DEBUG(
1244 FND_LOG.LEVEL_PROCEDURE
1245 ,L_MODULE
1246 ,'begin debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
1247 END IF;
1248
1249 -- call the procedure to create lease quote line
1250 OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE(
1251 p_api_version => l_api_version
1252 ,p_init_msg_list => OKL_API.G_FALSE
1253 ,p_transaction_control => OKL_API.G_TRUE
1254 ,p_lease_qte_rec => l_lsqv_rec
1255 ,x_lease_qte_rec => x_lsqv_rec
1256 ,x_return_status => l_return_status
1257 ,x_msg_count => x_msg_count
1258 ,x_msg_data => x_msg_data);
1259
1260 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1261 THEN
1262 OKL_DEBUG_PUB.LOG_DEBUG(
1263 FND_LOG.LEVEL_PROCEDURE
1264 ,L_MODULE
1265 ,'end debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
1266 END IF;
1267
1268
1269 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1270 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1271 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
1272 RAISE OKL_API.G_EXCEPTION_ERROR;
1273 END IF;
1274
1275 END IF;
1276
1277 --Bug 7140398 END
1278
1279 --l_lap_status will be null if Reject or Approve recommendation is not
1280 --available in status I or R in that case don't call api to update lease app status
1281
1282 IF(l_lap_status IS NOT NULL)
1283 THEN
1284 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1285 THEN
1286 OKL_DEBUG_PUB.LOG_DEBUG(
1287 FND_LOG.LEVEL_PROCEDURE
1288 ,L_MODULE
1289 ,'begin debug call OKL_LEASE_APP_PVT.SET_LEASE_APP_STATUS');
1290 END IF;
1291
1292 OKL_LEASE_APP_PVT.SET_LEASE_APP_STATUS(
1293 p_api_version => l_api_version
1294 ,p_init_msg_list => OKL_API.G_FALSE
1295 ,p_lap_id => l_lap_id
1296 ,p_lap_status => l_lap_status
1297 ,x_return_status => l_return_status
1298 ,x_msg_count => x_msg_count
1299 ,x_msg_data => x_msg_data);
1300
1301 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1302 THEN
1303 OKL_DEBUG_PUB.LOG_DEBUG(
1304 FND_LOG.LEVEL_PROCEDURE
1305 ,L_MODULE
1306 ,'end debug call OKL_LEASE_APP_PVT.SET_LEASE_APP_STATUS');
1307 END IF;
1308
1309 -- write to log
1310 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_STATEMENT_ON) THEN
1311 OKL_DEBUG_PUB.LOG_DEBUG(
1312 FND_LOG.LEVEL_STATEMENT
1313 ,L_MODULE || ' Result of OKL_LEASE_APP_PVT.SET_LEASE_APP_STATUS'
1314 ,'l_return_status ' || l_return_status);
1315 END IF; -- end of statement level debug
1316
1317 IF ( l_return_status = FND_API.G_RET_STS_ERROR )
1318 THEN
1319 resultout := 'ERROR:' || x_msg_data;
1320 RETURN;
1321 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1322 THEN
1323 resultout := 'ERROR:' || x_msg_data;
1324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1325 END IF;
1326
1327 -- Added for bug 7375141 - start
1328 l_exp_date := OKL_LEASE_APP_PVT.get_approval_exp_date(l_lap_id);
1329 if (l_exp_date is not null and l_exp_date <> OKL_API.G_MISS_DATE) then
1330 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1331 THEN
1332 OKL_DEBUG_PUB.LOG_DEBUG(
1333 FND_LOG.LEVEL_PROCEDURE
1334 ,L_MODULE
1335 ,'begin debug call POPULATE_LEASE_APP');
1336 END IF;
1337
1338 OKL_LEASE_APP_PVT.POPULATE_LEASE_APP(
1339 p_api_version => l_api_version
1340 ,p_init_msg_list => OKL_API.G_FALSE
1341 ,x_return_status => l_return_status
1342 ,x_msg_count => x_msg_count
1343 ,x_msg_data => x_msg_data
1344 ,p_lap_id => l_lap_id
1345 ,x_lapv_rec => x_lapv_rec
1346 ,x_lsqv_rec => x_lsqv_rec);
1347
1348 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1349 THEN
1350 OKL_DEBUG_PUB.LOG_DEBUG(
1351 FND_LOG.LEVEL_PROCEDURE
1352 ,L_MODULE
1353 ,'end debug call POPULATE_LEASE_APP');
1354 END IF;
1355
1356 -- write to log
1357 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_STATEMENT_ON) THEN
1358 OKL_DEBUG_PUB.LOG_DEBUG(
1359 FND_LOG.LEVEL_STATEMENT
1360 ,L_MODULE || ' Result of POPULATE_LEASE_APP'
1361 ,'l_return_status ' || l_return_status);
1362 END IF; -- end of statement level debug
1363 IF ( l_return_status = FND_API.G_RET_STS_ERROR )
1364 THEN
1365 resultout := 'ERROR:' || x_msg_data;
1366 RETURN;
1367 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1368 THEN
1369 resultout := 'ERROR:' || x_msg_data;
1370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1371 END IF;
1372 l_lapv_rec := x_lapv_rec;
1373 l_lapv_rec.valid_to:= l_exp_date;
1374
1375 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1376 THEN
1377 OKL_DEBUG_PUB.LOG_DEBUG(
1378 FND_LOG.LEVEL_PROCEDURE
1379 ,L_MODULE
1380 ,'begin debug call OKL_LAP_PVT.UPDATE_ROW');
1381 END IF;
1382
1383 OKL_LAP_PVT.UPDATE_ROW(
1384 p_api_version => l_api_version
1385 ,p_init_msg_list => OKL_API.G_FALSE
1386 ,x_return_status => l_return_status
1387 ,x_msg_count => x_msg_count
1388 ,x_msg_data => x_msg_data
1389 ,p_lapv_rec => l_lapv_rec
1390 ,x_lapv_rec => x_lapv_rec);
1391
1392 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1393 THEN
1394 OKL_DEBUG_PUB.LOG_DEBUG(
1395 FND_LOG.LEVEL_PROCEDURE
1396 ,L_MODULE
1397 ,'end debug call OKL_LAP_PVT.UPDATE_ROW');
1398 END IF;
1399
1400 -- write to log
1401 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_STATEMENT_ON) THEN
1402 OKL_DEBUG_PUB.LOG_DEBUG(
1403 FND_LOG.LEVEL_STATEMENT
1404 ,L_MODULE || ' Result of OKL_LAP_PVT.UPDATE_ROW'
1405 ,'l_return_status ' || l_return_status);
1406 END IF; -- end of statement level debug
1407 IF ( l_return_status = FND_API.G_RET_STS_ERROR )
1408 THEN
1409 resultout := 'ERROR:' || x_msg_data;
1410 RETURN;
1411 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1412 THEN
1413 resultout := 'ERROR:' || x_msg_data;
1414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1415 END IF;
1416 End if;
1417 -- Added for bug 7375141 - End
1418
1419 --Bug 4872271 PAGARG Call the API which will check if there is any Parent
1420 --to this Lease App and in status Appeal/Resubmit in Progress. If yes
1421 --then restore the status of parent to original status
1422 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1423 THEN
1424 OKL_DEBUG_PUB.LOG_DEBUG(
1425 FND_LOG.LEVEL_PROCEDURE
1426 ,L_MODULE
1427 ,'begin debug call OKL_LEASE_APP_PVT.REVERT_TO_ORIG_STATUS');
1428 END IF;
1429
1430 OKL_LEASE_APP_PVT.REVERT_TO_ORIG_STATUS(
1431 p_api_version => l_api_version
1432 ,p_init_msg_list => OKL_API.G_FALSE
1433 ,p_lap_id => l_lap_id
1434 ,x_return_status => l_return_status
1435 ,x_msg_count => x_msg_count
1436 ,x_msg_data => x_msg_data);
1437
1438 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
1439 THEN
1440 OKL_DEBUG_PUB.LOG_DEBUG(
1441 FND_LOG.LEVEL_PROCEDURE
1442 ,L_MODULE
1443 ,'end debug call OKL_LEASE_APP_PVT.REVERT_TO_ORIG_STATUS');
1444 END IF;
1445
1446 -- write to log
1447 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_STATEMENT_ON) THEN
1448 OKL_DEBUG_PUB.LOG_DEBUG(
1449 FND_LOG.LEVEL_STATEMENT
1450 ,L_MODULE || ' Result of OKL_LEASE_APP_PVT.REVERT_TO_ORIG_STATUS'
1451 ,'l_return_status ' || l_return_status);
1452 END IF; -- end of statement level debug
1453
1454 IF ( l_return_status = FND_API.G_RET_STS_ERROR )
1455 THEN
1456 resultout := 'ERROR:' || x_msg_data;
1457 RETURN;
1458 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1459 THEN
1460 resultout := 'ERROR:' || x_msg_data;
1461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1462 END IF;
1463 END IF;-- For l_lap_status is not null condition
1464 -- Bug#4741121 - viselvar - Modified - Start
1465 -- raise the business event passing the lease application id added to the parameter list
1466
1467 wf_event.addparametertolist('LAPP_ID'
1468 ,l_lap_id
1469 ,l_parameter_list);
1470
1471 okl_wf_pvt.raise_event(p_api_version => l_api_version
1472 ,p_init_msg_list => OKL_API.G_FALSE
1473 ,x_return_status => l_return_status
1474 ,x_msg_count => x_msg_count
1475 ,x_msg_data => x_msg_data
1476 ,p_event_name => p_event_name
1477 ,p_parameters => l_parameter_list);
1478 -- Bug#4741121 - viselvar - Modified - End
1479
1480 resultout := 'COMPLETE:YES';
1481 RETURN;
1482 END IF;
1483 --
1484 -- CANCEL mode
1485 --
1486 IF (funcmode = 'CANCEL')
1487 THEN
1488 resultout := 'COMPLETE:YES';
1489 RETURN;
1490 END IF;
1491 --
1492 -- TIMEOUT mode
1493 --
1494 IF (funcmode = 'TIMEOUT')
1495 THEN
1496 resultout := 'COMPLETE:NO';
1497 RETURN;
1498 END IF;
1499 EXCEPTION
1500 WHEN OTHERS THEN
1501 --Credit Request cursor
1502 IF credit_req_csr%ISOPEN
1503 THEN
1504 CLOSE credit_req_csr;
1505 END IF;
1506 --Credit Request status cursor
1507 IF credit_req_sts_csr%ISOPEN
1508 THEN
1509 CLOSE credit_req_sts_csr;
1510 END IF;
1511 wf_core.context(
1512 G_PKG_NAME
1513 ,l_api_name
1514 ,itemtype
1515 ,itemkey
1516 ,to_char(actid)
1517 ,funcmode);
1518 RAISE;
1519 END UPDATE_STATUS ;
1520
1521 ---------------------------------------------------------------------------
1522 -- create_credit_app
1523 ---------------------------------------------------------------------------
1524 procedure create_credit_app ( itemtype in varchar2,
1525 itemkey in varchar2,
1526 actid in number,
1527 funcmode in varchar2,
1528 resultout out nocopy varchar2 )IS
1529
1530 l_quote_id NUMBER;
1531 l_requestor_id NUMBER;
1532 l_capital_amount NUMBER;
1533
1534 ls_request_num VARCHAR2(80) ;
1535 l_seq NUMBER ;
1536
1537 l_crqv_rec_type okl_credit_request_pub.crqv_rec_type ;
1538 x_crqv_rec_type okl_credit_request_pub.crqv_rec_type ;
1539 x_msg_data VARCHAR2(2000);
1540 x_msg_count NUMBER := 0 ;
1541 l_api_version NUMBER := 1.0 ;
1542 x_return_status VARCHAR2(1) ;
1543 l_quote_number OKC_K_HEADERS_B.CONTRACT_number%TYPE;
1544
1545 CURSOR contract_number_csr(p_contract_id NUMBER ) IS
1546 SELECT SUM(KLE.CAPITAL_AMOUNT) ,chr.contract_number
1547 FROM OKC_K_LINES_B CLEB,OKL_K_LINES KLE, OKC_K_HEADERS_V CHR
1548 WHERE chr.scs_code = 'QUOTE'
1549 AND chr.ID = l_quote_id
1550 AND CLEB.ID = KLE.ID
1551 AND CLEB.DNZ_CHR_ID = chr.ID
1552 AND CLEB.CLE_ID IS NULL
1553 GROUP BY chr.ID , chr.contract_number;
1554
1555 begin
1556 --
1557 -- RUN mode - normal process execution
1558 --
1559 if (funcmode = 'RUN') then
1560
1561
1562 -- 1. Get values (quote_id , requestor_id) from workflow
1563 l_quote_id := TO_NUMBER( wf_engine.GetItemAttrText (
1564 itemtype => itemtype,
1565 itemkey => itemkey,
1566 aname => 'QUOTE_ID'));
1567
1568 l_requestor_id := TO_NUMBER( wf_engine.GetItemAttrText (
1569 itemtype => itemtype,
1570 itemkey => itemkey,
1571 aname => 'REQUESTED_ID'));
1572
1573 BEGIN
1574 -- 2. Get Capital amount
1575 OPEN contract_number_csr(l_quote_id);
1576 FETCH contract_number_csr INTO l_capital_amount, l_quote_number ;
1577 IF(contract_number_csr%NOTFOUND) THEN
1578 resultout := 'ERROR: No Capital Amount';
1579 CLOSE contract_number_csr ;
1580 RETURN ;
1581 END IF ;
1582 CLOSE contract_number_csr ;
1583 EXCEPTION
1584 WHEN OTHERS THEN
1585 resultout := 'ERROR: Not able to get' ;
1586 RETURN;
1587 END ;
1588
1589 -- 3. Get Request Number
1590 select okl_wf_item_s.nextval INTO l_seq FROM DUAL ;
1591 ls_request_num := l_quote_number ||l_seq ;
1592
1593 -- 4. CREATE credit app request
1594 l_crqv_rec_type.QUOTE_ID := l_quote_id ;
1595 l_crqv_rec_type.CREDIT_REQ_NUMBER := ls_request_num ;
1596 l_crqv_rec_type.CREDIT_REQ_ID := NULL ;
1597 l_crqv_rec_type.CREDIT_AMOUNT := l_capital_amount ;
1598 l_crqv_rec_type.REQUESTED_BY :=l_requestor_id ;
1599 l_crqv_rec_type.REQUESTED_DATE := SYSDATE ;
1600 -- APPROVED_BY
1601 -- APPROVED_DATE
1602 l_crqv_rec_type.STATUS := 'ENTERED';
1603 l_crqv_rec_type.CURRENCY_CODE := 'USD';
1604
1605 okl_credit_request_pub.insert_credit_request(
1606 p_api_version => l_api_version
1607 ,p_init_msg_list => 'T'
1608 ,x_return_status =>x_return_status
1609 ,x_msg_count => x_msg_count
1610 ,x_msg_data =>x_msg_data
1611 ,p_crqv_rec =>l_crqv_rec_type
1612 ,x_crqv_rec => x_crqv_rec_type);
1613
1614 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
1615 resultout := 'COMPLETE:N';
1616 return;
1617 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1618 resultout := 'COMPLETE:NO';
1619 -- return;
1620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1621 END IF;
1622
1623 -- 5. set approver role, REQUEST STATUS , CREDIT_AMOUNT , rEQUEST NUMBER
1624 wf_engine.SetItemAttrText (
1625 itemtype => itemtype,
1626 itemkey => itemkey,
1627 aname => 'APPROVER_ID',
1628 avalue => 'ADMIN');
1629
1630 wf_engine.SetItemAttrText (
1631 itemtype => itemtype,
1632 itemkey => itemkey,
1633 aname => 'CREDIT_REQ_NUM',
1634 avalue => ls_request_num);
1635
1636 wf_engine.SetItemAttrText (
1637 itemtype => itemtype,
1638 itemkey => itemkey,
1639 aname => 'REQUEST_STATUS',
1640 avalue => 'ENTERED');
1641
1642 wf_engine.SetItemAttrNumber (
1643 itemtype => itemtype,
1644 itemkey => itemkey,
1645 aname => 'CREDIT_AMOUNT',
1646 avalue => l_capital_amount);
1647
1648 wf_engine.SetItemAttrNumber (
1649 itemtype => itemtype,
1650 itemkey => itemkey,
1651 aname => 'ID',
1652 avalue => x_crqv_rec_type.ID);
1653
1654 -- 6. set return code YES
1655 resultout := 'COMPLETE:Y';
1656 return;
1657 --
1658 end if;
1659 --
1660 -- CANCEL mode
1661 --
1662 if (funcmode = 'CANCEL') then
1663 --
1664 resultout := 'COMPLETE:N';
1665 return;
1666 --
1667 end if;
1668 --
1669 -- TIMEOUT mode
1670 --
1671 if (funcmode = 'TIMEOUT') then
1672 --
1673 resultout := 'COMPLETE:N';
1674 return;
1675 --
1676 end if;
1677 exception
1678 when others then
1679 wf_core.context('OKL_SO_CREDIT_APP_WF',
1680 'CREATE_CREDIT_APP',
1681 itemtype,
1682 itemkey,
1683 to_char(actid),
1684 funcmode);
1685 raise;
1686
1687 end create_credit_app;
1688
1689 -- Ensures an end date has been specifed for credit contract creation
1690 -- by the user. If no date is entered the notification is resent.
1691 --
1692 procedure credit_k_end_dated(
1693 itemtype in varchar2,
1694 itemkey in varchar2,
1695 actid in number,
1696 funcmode in varchar2,
1697 resultout out nocopy varchar2 ) is
1698 begin
1699 --
1700 -- RUN mode - normal process execution
1701 --
1702 if (funcmode = 'RUN') then
1703 if (wf_engine.GetItemAttrText(itemtype,itemkey,'TLOC_END_DATE') is NULL) then
1704 resultout := 'COMPLETE:F';
1705 else
1706 -- check that the date is greater than the request date
1707 if (wf_engine.GetItemAttrText(itemtype,itemkey,'TLOC_END_DATE') < sysdate) then
1708 resultout := 'COMPLETE:F';
1709 else
1710 resultout := 'COMPLETE:T';
1711 end if;
1712 end if;
1713 end if;
1714 --
1715 -- CANCEL mode
1716 --
1717 if (funcmode = 'CANCEL') then
1718 --
1719 resultout := 'COMPLETE:';
1720 return;
1721 --
1722 end if;
1723 --
1724 -- TIMEOUT mode
1725 --
1726 if (funcmode = 'TIMEOUT') then
1727 --
1728 resultout := 'COMPLETE:';
1729 return;
1730 --
1731 end if;
1732 exception
1733 when others then
1734 wf_core.context('OKL_SO_CREDIT_APP_WF',
1735 'CREDIT_K_END_DATED',
1736 itemtype,
1737 itemkey,
1738 to_char(actid),
1739 funcmode);
1740 raise;
1741 end credit_k_end_dated;
1742
1743 procedure send_message ( itemtype in varchar2,
1744 itemkey in varchar2,
1745 actid in number,
1746 funcmode in varchar2,
1747 resultout out nocopy varchar2 )IS
1748 begin
1749 NULL;
1750 end send_message;
1751
1752 END OKL_SO_CREDIT_APP_WF;