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