[Home] [Help]
PACKAGE BODY: APPS.OKL_VSS_WF
Source
1 PACKAGE BODY OKL_VSS_WF AS
2 /* $Header: OKLRVSWB.pls 120.4 2007/08/14 11:45:20 zrehman ship $ */
3
4
5 PROCEDURE raise_business_event (
6 p_khr_id IN VARCHAR2,
7 p_kle_id IN VARCHAR2,
8 p_qte_id IN VARCHAR2,
9 p_requestor_id IN VARCHAR2) IS
10
11
12 l_parameter_list WF_PARAMETER_LIST_T;
13 l_key WF_ITEMS.item_key%TYPE;
14 l_event_name WF_EVENTS.NAME%TYPE := 'oracle.apps.okl.vss.requestrepquote';
15 l_seq NUMBER;
16
17 -- Cursor to get the value of the sequence
18 CURSOR okl_key_csr IS
19 SELECT okl_wf_item_s.nextval
20 FROM DUAL;
21
22 BEGIN
23
24 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
25 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
26 'OKL_VSS_WF.raise_business_event.',
27 'Begin(+)');
28 END IF;
29
30 SAVEPOINT raise_business_event_sv;
31
32 OPEN okl_key_csr;
33 FETCH okl_key_csr INTO l_seq;
34 CLOSE okl_key_csr;
35
36 l_key := l_event_name ||l_seq ;
37
38 -- *******
39 -- Set the parameter list
40 -- *******
41
42 WF_EVENT.AddParameterToList('SSCKHRID',
43 p_khr_id,
44 l_parameter_list);
45
46 WF_EVENT.AddParameterToList('SSCKLEID',
47 p_kle_id,
48 l_parameter_list);
49 WF_EVENT.AddParameterToList('SSCQTEID',
50 p_qte_id,
51 l_parameter_list);
52 WF_EVENT.AddParameterToList('REQUESTOR_ID',
53 p_requestor_id,
54 l_parameter_list);
55
56
57 -- Raise Business Event
58 WF_EVENT.raise(
59 p_event_name => l_event_name,
60 p_event_key => l_key,
61 p_parameters => l_parameter_list);
62
63 l_parameter_list.DELETE;
64
65 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
66 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
67 'OKL_VSS_WF.raise_business_event.',
68 'End(-)');
69 END IF;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
74 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
75 FND_MSG_PUB.ADD;
76 IF okl_key_csr%ISOPEN THEN
77 CLOSE okl_key_csr;
78 END IF;
79 ROLLBACK TO raise_business_event_sv;
80 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
81 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
82 'OKL_VSS_WF.raise_business_event.',
83 'EXP - OTHERS');
84 END IF;
85
86 END raise_business_event;
87
88
89 -----------Create Repurchase Quote--
90
91 procedure createRepurchaseQuote(
92 p_api_version IN NUMBER,
93 p_init_msg_list IN VARCHAR2,
94 p_khr_id IN NUMBER,
95 p_kle_id IN NUMBER,
96 p_art_id IN NUMBER,
97 p_qtp_code IN VARCHAR2,
98 p_requestor_id IN VARCHAR2,
99 x_return_status OUT NOCOPY VARCHAR2,
100 x_msg_count OUT NOCOPY NUMBER,
101 x_msg_data OUT NOCOPY VARCHAR2) IS
102
103 l_api_version NUMBER := 1.0;
104 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
105
106 l_return_status VARCHAR2(1);
107 l_msg_count NUMBER;
108 l_msg_data VARCHAR2(2000);
109
110 SUBTYPE qtev_rec_type IS OKL_AM_REPURCHASE_ASSET_PVT.qtev_rec_type;
111 SUBTYPE tqlv_tbl_type IS OKL_AM_REPURCHASE_ASSET_PVT.tqlv_tbl_type;
112
113 l_qtev_rec qtev_rec_type;
114 x_qtev_rec qtev_rec_type;
115 l_tqlv_tbl tqlv_tbl_type;
116 x_tqlv_tbl tqlv_tbl_type;
117 i NUMBER :=0;
118
119 BEGIN
120
121 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
122 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
123 'OKL_VSS_WF.createRepurchaseQuote.',
124 'Begin(+)');
125 END IF;
126
127 l_qtev_rec.khr_id := p_khr_id;
128 l_qtev_rec.art_id := p_art_id;
129 l_qtev_rec.qtp_code := p_qtp_code;
130 l_tqlv_tbl(1).kle_id := p_kle_id;
131
132 OKL_AM_REPURCHASE_ASSET_PUB.create_repurchase_quote(
133 p_api_version,
134 p_init_msg_list,
135 l_return_status,
136 l_msg_count,
137 l_msg_data,
138 l_qtev_rec,
139 l_tqlv_tbl,
140 x_qtev_rec,
141 x_tqlv_tbl);
142
143 x_return_status := l_return_status;
144 x_msg_count := l_msg_count;
145 x_msg_data := l_msg_data;
146
147 IF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
148 RAISE OKL_API.G_EXCEPTION_ERROR;
149 ELSIF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
150 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
151 END IF;
152
153 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
155 'OKL_VSS_WF.createRepurchaseQuote.',
156 'End(-)');
157 END IF;
158 -- Raise Event
159
160 raise_business_event( p_khr_id ,
161 p_kle_id ,
162 x_qtev_rec.id ,
163 p_requestor_id);
164
165 EXCEPTION
166
167 WHEN OKL_API.G_EXCEPTION_ERROR THEN
168 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
169 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
170 p_data => x_msg_data);
171
172 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
173 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
174 'OKL_VSS_WF.createRepurchaseQuote.',
175 'EXP - G_EXCEPTION_ERROR');
176 END IF;
177
178 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
179 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
180 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
181 p_data => x_msg_data);
182 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
183 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
184 'OKL_VSS_WF.createRepurchaseQuote.',
185 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
186 END IF;
187
188 WHEN OTHERS THEN
189 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
190 FND_MSG_PUB.ADD_EXC_MSG('OKL_SSC_ASST_LOC_SERNUM_PUB','update_counter');
191 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
192 p_data => x_msg_data);
193 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
194 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
195 'OKL_VSS_WF.createRepurchaseQuote.',
196 'EXP - OTHERS');
197 END IF;
198
199
200 END createRepurchaseQuote;
201
202
203
204
205
206
207 ----------------------------UPDATE COUNTERS------------------------------------------------------
208
209 procedure getCountersMessage (itemtype in varchar2,
210 itemkey in varchar2,
211 actid in number,
212 funcmode in varchar2,
213 resultout out nocopy varchar2 )
214
215 IS
216
217 CURSOR user_info(p_id NUMBER) IS
218 SELECT user_name from fnd_user
219 WHERE user_id = p_id;
220
221 CURSOR approver_cur(respKey varchar2) IS
222 SELECT responsibility_id
223 FROM fnd_responsibility
224 WHERE responsibility_key = respKey
225 AND application_id = 540;
226
227 requestor_info_rec user_info%rowtype;
228
229 p_requestor_id NUMBER;
230 p_resp_key varchar2(30);
231
232 l_requestor_name varchar2(100);
233 l_approver_name varchar2(100);
234 l_respString VARCHAR2(15) := 'FND_RESP540:';
235 l_approver_id varchar2(20);
236
237 l_doc_attr varchar2(20) := 'SSCUPASNDOC';
238
239
240 BEGIN
241
242 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
243 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
244 'OKL_VSS_WF.getCountersMessage.',
245 'Begin(+)');
246 END IF;
247
248 if ( funcmode = 'RUN' ) then
249
250 p_requestor_id:=to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID'));
251
252 open user_info(p_requestor_id);
253 fetch user_info into requestor_info_rec;
254 l_requestor_name := requestor_info_rec.user_name;
255 close user_info;
256
257
258
259 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTOR', l_requestor_name);
260
261 p_resp_key := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCAPPROVERRESPONSIBILITYKEY');
262
263 open approver_cur(p_resp_key);
264 fetch approver_cur into l_approver_id;
265 close approver_cur;
266 l_approver_name := l_respString||l_approver_id;
267
268 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCAPPROVER', l_approver_name);
269
270
271 wf_engine.SetItemAttrText (itemtype => itemtype,
272 itemkey => itemkey,
273 aname => l_doc_attr,
274 avalue => 'PLSQLCLOB:OKL_SSC_WF.getCountersDocument/'||
275 itemtype ||':'||itemkey||':&#NID');
276
277 resultout := 'COMPLETE';
278
279 return;
280 end if;
281
282
283 if ( funcmode = 'CANCEL' ) then
284
285 resultout := 'COMPLETE';
286 return;
287 end if;
288 if ( funcmode = 'RESPOND') then
289 resultout := 'COMPLETE';
290 return;
291 end if;
292 if ( funcmode = 'FORWARD') then
293 resultout := 'COMPLETE';
294 return;
295 end if;
296 if ( funcmode = 'TRANSFER') then
297 resultout := 'COMPLETE';
298 return;
299 end if;
300 if ( funcmode = 'TIMEOUT' ) then
301 resultout := 'COMPLETE';
302 else
303 resultout := wf_engine.eng_timedout;
304 return;
305 end if;
306
307
308 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
309 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
310 'OKL_VSS_WF.getCountersMessage.',
311 'End(-)');
312 END IF;
313
314 exception
315 when others then
316 WF_CORE.CONTEXT ('okl_ssc_wf', 'getCountersMessage', itemtype, itemkey,actid,funcmode);
317 resultout := 'ERROR';
318
319 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
320 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
321 'OKL_VSS_WF.getCountersMessage.',
322 'EXP - OTHERS');
323 END IF;
324 raise;
325 end getCountersMessage;
326
327 Procedure getCountersDocument
328 ( document_id in varchar2,
329 display_type in varchar2,
330 document in out nocopy clob,
331 document_type in out nocopy varchar2
332 )
333
334 IS
335
336
337
338 CURSOR counter_nums(p_counter_id NUMBER) IS
339 SELECT CN.COUNTER_ID COUNTER_NUMBER, CN.NAME COUNTER_NAME,
340 KHR.CONTRACT_NUMBER CONTRACT_NUMBER, ASSET.NAME ASSET_NUMBER
341 FROM
342 OKC_K_LINES_TL ASSET,
343 OKC_K_LINES_B USAGE,
344 OKC_K_ITEMS UITEM,
345 OKC_K_LINES_B OKS,
346 OKC_K_LINES_B OKSU,
347 OKC_K_ITEMS OKSITEM,
348 CSI_COUNTERS_VL CN,
349 OKC_K_ITEMS ASSET_ITEM,
350 OKC_K_REL_OBJS REL,
351 OKC_K_HEADERS_B KHR,
352 OKC_K_LINES_B USUB_LINE
353 WHERE
354 CN.COUNTER_ID = p_counter_id AND
355 USAGE.DNZ_CHR_ID = KHR.ID AND
356 KHR.ID = USAGE.CHR_ID AND
357 USAGE.ID = UITEM.CLE_ID AND
358 to_char(OKS.ID) = UITEM.OBJECT1_ID1 AND
359 to_char(OKS.DNZ_CHR_ID) = REL.OBJECT1_ID1 AND
360 OKS.ID = OKSU.CLE_ID AND
361 OKS.DNZ_CHR_ID = OKSU.DNZ_CHR_ID AND
362 OKSU.ID = OKSITEM.CLE_ID AND
363 OKSITEM.OBJECT1_ID1 = to_char(CN.COUNTER_ID) AND
364 OKSITEM.JTOT_OBJECT1_CODE = 'OKX_COUNTER' AND
365 USUB_LINE.DNZ_CHR_ID = USAGE.CHR_ID AND
366 USAGE.ID = USUB_LINE.CLE_ID AND
367 USUB_LINE.ID = ASSET_ITEM.CLE_ID AND
368 OKSU.dnz_chr_id = REL.OBJECT1_ID1 AND
369 ASSET_ITEM.OBJECT1_ID1 = ASSET.ID AND
370 ASSET.language = userenv ( 'LANG' ) AND
371 REL.RTY_CODE = 'OKLUBB' AND
372 REL.JTOT_OBJECT1_CODE = 'OKL_SERVICE' AND
373 REL.CHR_ID = USAGE.DNZ_CHR_ID;
374
375 first_index number;
376 second_index number;
377 third_index number;
378
379 l_counter_id number;
380 --l_tal_type varchar2(100);
381 l_itemtype varchar2(100);
382 l_itemkey varchar2(100);
383
384 l_document varchar2(32000);
385 NL VARCHAR2(1) := fnd_global.newline;
386
387 BEGIN
388
389 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
390 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
391 'OKL_VSS_WF.getCountersDocument.',
392 'Begin(+)');
393 END IF;
394
395 --the document_id is in the form of
396 --'PLSQLCLOB:OKL_SSC_WF.getAssetReturnDocument/itemtyp:itemkey:&#NID'
397 --we need to get itemtype and itemkey
398
399 first_index := instr(document_id, '/', 1, 1); --index of the slash '/'
400 second_index := instr(document_id, ':', 1,1); --index of first colon ':'
401 third_index := instr(document_id, ':', 1, 2); --index of the second colon ':'
402
403 l_itemtype := substr(document_id, first_index+1, second_index-first_index-1);
404 l_itemkey := substr(document_id, second_index+1, third_index-second_index-1);
405
406 l_counter_id := to_number(WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SSCTASID'));
407 -- l_tal_type := WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SSCTALTYPE');
408
409
410 IF (display_type = 'text/html') THEN
411
412 --first generate the header
413 l_document := '<BR>' || NL;
414 l_document := l_document ||
415 '<table cellpadding="3" cellspacing="3" border="3" summary="">' || NL;
416 l_document := l_document ||
417 '<tr><th>Asset Number</th><th>Serial Number</th></tr>' || NL;
418 --loop through the record, and generate line by line
419
420
421 FOR serial_nums_rec in counter_nums(l_counter_id)
422 LOOP
423 l_document := l_document ||
424 '<tr><td>' ||serial_nums_rec.COUNTER_NUMBER || '</td>';
425
426 l_document := l_document ||
427 '<td>' ||serial_nums_rec.COUNTER_NAME || '</td></tr>' || NL;
428
429 l_document := l_document ||
430 '<td>' ||serial_nums_rec.ASSET_NUMBER || '</td></tr>' || NL;
431
432 l_document := l_document ||
433 '<td>' ||serial_nums_rec.CONTRACT_NUMBER || '</td></tr>' || NL;
434
435 END LOOP;
436
437 l_document := l_document || '</table>';
438 END IF;-- end to 'text/html' display type
439
440 wf_notification.WriteToClob( document, l_document);
441
442 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
443 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
444 'OKL_VSS_WF.getCountersDocument.',
445 'End(-)');
446 END IF;
447
448 EXCEPTION
449
450 when others then
451 WF_CORE.CONTEXT ('OKL_SSC_WF', 'getCountersDocument', l_itemtype, l_itemkey);
452
453 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
454 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
455 'OKL_VSS_WF.getCountersDocument.',
456 'EXP - OTHERS');
457 END IF;
458 raise;
459
460 END getCountersDocument;
461
462 PROCEDURE update_counter(
463 p_api_version IN NUMBER,
464 p_init_msg_list IN VARCHAR2,
465 p_trx_id IN NUMBER,
466 x_return_status OUT NOCOPY VARCHAR2,
467 x_msg_count OUT NOCOPY NUMBER,
468 x_msg_data OUT NOCOPY VARCHAR2)
469 AS
470
471
472 l_counter_id NUMBER;
473 l_trx_id NUMBER;
474 l_object_version_number NUMBER;
475
476 l_api_version NUMBER := 1.0;
477 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
478
479 l_return_status VARCHAR2(1);
480 l_msg_count NUMBER;
481 l_msg_data VARCHAR2(2000);
482
483 SUBTYPE cntr_bill_rec_type IS OKL_CNTR_GRP_BILLING_PVT.cntr_bill_rec_type;
484 SUBTYPE cntr_bill_tbl_type IS OKL_CNTR_GRP_BILLING_PVT.cntr_bill_tbl_type;
485
486 l_cntr_bill_rec cntr_bill_rec_type;
487 x_cntr_bill_rec cntr_bill_rec_type;
488 l_cntr_bill_tbl cntr_bill_tbl_type;
489 x_cntr_bill_tbl cntr_bill_tbl_type;
490 i NUMBER :=0;
491
492
493 /*
494 CURSOR c_counter_rec(c_trx_id NUMBER)IS
495 select counter_id, counter_reading, reading_date, amount, clg_id
496 from dual;
497 */
498 -- where trx_id = c_trx_id;
499
500
501 BEGIN
502
503 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
504 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
505 'OKL_VSS_WF.update_counter.',
506 'Begin(+)');
507 END IF;
508
509 /*
510 FOR r_counter_rec in c_counter_rec(l_trx_id)
511 loop
512
513 l_cntr_bill_rec.clg_id := r_counter_rec.clg_id;
514 l_cntr_bill_rec.counter_group := null;
515 l_cntr_bill_rec.counter_number := r_counter_rec.counter_id;
516 l_cntr_bill_rec.counter_name := null;
517 l_cntr_bill_rec.contract_number := null;
518 l_cntr_bill_rec.asset_number := null;
519 l_cntr_bill_rec.asset_serial_number := null;
520 l_cntr_bill_rec.asset_description := null;
521 l_cntr_bill_rec.effective_date_from := null;
522 l_cntr_bill_rec.effective_date_to := null;
523 l_cntr_bill_rec.Reading_date := r_counter_rec.reading_date;
524 l_cntr_bill_rec.Meter_reading := r_counter_rec.counter_reading;
525 l_cntr_bill_rec.Bill_amount := r_counter_rec.amount;
526
527
528 l_cntr_bill_tbl(i) := l_cntr_bill_rec;
529
530 i := i + 1;
531
532 end loop;
533 */
534 OKL_CNTR_GRP_BILLING_PUB.insert_cntr_grp_bill(
535 p_api_version => l_api_version,
536 p_init_msg_list => l_init_msg_list,
537 x_return_status => l_return_status,
538 x_msg_count => l_msg_count,
539 x_msg_data => l_msg_data,
540 p_cntr_bill_tbl => l_cntr_bill_tbl,
541 x_cntr_bill_tbl => x_cntr_bill_tbl
542 );
543
544 IF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
545 RAISE OKL_API.G_EXCEPTION_ERROR;
546 ELSIF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
547 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
548 END IF;
549
550 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
552 'OKL_VSS_WF.update_counter.',
553 'End(-)');
554 END IF;
555
556 EXCEPTION
557
558 WHEN OKL_API.G_EXCEPTION_ERROR THEN
559 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
560 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
561 p_data => x_msg_data);
562 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
563 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
564 'OKL_VSS_WF.update_counter.',
565 'EXP - G_EXCEPTION_ERROR');
566 END IF;
567
568 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
569 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
570 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
571 p_data => x_msg_data);
572 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
573 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
574 'OKL_VSS_WF.update_counter.',
575 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
576 END IF;
577
578 WHEN OTHERS THEN
579 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
580 FND_MSG_PUB.ADD_EXC_MSG('OKL_SSC_ASST_LOC_SERNUM_PUB','update_counter');
581 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
582 p_data => x_msg_data);
583
584 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
585 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
586 'OKL_VSS_WF.update_counter.',
587 'EXP - OTHERS');
588 END IF;
589
590 END update_counter;
591
592 procedure update_counter_fnc (itemtype in varchar2,
593 itemkey in varchar2,
594 actid in number,
595 funcmode in varchar2,
596 resultout out nocopy varchar2 ) is
597
598 x_return_status varchar2(1);
599 x_msg_count number;
600 l_msg_data varchar2(2000);
601 l_trx_id number;
602
603 l_admin VARCHAR2(120) := 'SYSADMIN';
604
605 error_updating_counters EXCEPTION;
606
607 begin
608
609 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
611 'OKL_VSS_WF.update_counter_fnc.',
612 'Begin(+)');
613 END IF;
614 -- assign variable to attribute
615 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'WF_ADMINISTRATOR',l_admin);
616
617
618 if ( funcmode = 'RUN' ) then
619
620
621 l_trx_id := to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCTASID'));
622
623 update_counter(p_api_version => 1.0,
624 p_init_msg_list => OKC_API.G_FALSE,
625 p_trx_id => l_trx_id,
626 x_return_status => x_return_status,
627 x_msg_count => x_msg_count,
628 x_msg_data => l_msg_data);
629
630 --check the update result
631 IF x_return_status <> 'S' THEN
632 RAISE error_updating_counters;
633 ELSE
634 resultout := 'COMPLETE';
635 return;
636 END IF;
637
638
639 end if;
640
641
642 if ( funcmode = 'CANCEL' ) then
643 resultout := 'COMPLETE';
644 return;
645 end if;
646 if ( funcmode = 'RESPOND') then
647 resultout := 'COMPLETE';
648 return;
649 end if;
650 if ( funcmode = 'FORWARD') then
651 resultout := 'COMPLETE';
652 return;
653 end if;
654 if ( funcmode = 'TRANSFER') then
655 resultout := 'COMPLETE';
656 return;
657 end if;
658 if ( funcmode = 'TIMEOUT' ) then
659 resultout := 'COMPLETE';
660 else
661 resultout := wf_engine.eng_timedout;
662 return;
663 end if;
664 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
665 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
666 'OKL_VSS_WF.update_counter_fnc.',
667 'End(-)');
668 END IF;
669
670 exception
671 when others then
672 WF_CORE.CONTEXT ('okl_ssc_wf', 'update_counter_fnc:'||l_msg_data, itemtype, itemkey,actid,funcmode);
673 resultout := 'ERROR';
674 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
675 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
676 'OKL_VSS_WF.update_counter_fnc.',
677 'EXP - OTHERS');
678 END IF;
679 raise;
680
681 end update_counter_fnc;
682
683
684
685 PROCEDURE populate_req_repqte_attr_wf
686 (itemtype IN VARCHAR2,
687 itemkey IN VARCHAR2,
688 actid IN number,
689 funcmode IN VARCHAR2,
690 resultout OUT NOCOPY VARCHAR2
691 ) IS
692
693 CURSOR contract_info_cur( p_khr_id IN NUMBER) IS
694 SELECT contract_number
695 FROM okc_k_headers_b
696 WHERE id = p_khr_id;
697
698 CURSOR asset_info_cur(p_kle_id IN NUMBER) IS
699 SELECT name
700 FROM okc_k_lines_v
701 WHERE id = p_kle_id;
702
703
704 CURSOR quote_info_cur(p_qte_id IN NUMBER) IS
705 SELECT quote_number
706 FROM okl_trx_quotes_v
707 WHERE id = p_qte_id;
708
709 CURSOR requestor_info_cur(p_requestor_id IN NUMBER) IS
710 SELECT user_name FROM fnd_user
711 WHERE user_id = p_requestor_id;
712
713
714 l_khr_id VARCHAR2(40);
715 l_kle_id VARCHAR2(40);
716 l_qte_id VARCHAR2(40);
717 l_quote_number VARCHAR2(80);
718 l_requestor_id VARCHAR2(40);
719 l_lease_agent_id VARCHAR2(120) := 'LEASE';
720 l_contract_number VARCHAR2(120);
721 l_asset_number VARCHAR2(80);
722 l_user_name VARCHAR2(100);
723 l_resp_id VARCHAR2(15);
724 l_resp_key VARCHAR2(30);
725 l_performer VARCHAR2(27);
726 l_respString VARCHAR2(12):='FND_RESP540:';
727
728 api_exception EXCEPTION;
729
730 BEGIN
731
732 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
733 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
734 'OKL_VSS_WF.populate_req_repqte_attr_wf.',
735 'Begin(+)');
736 END IF;
737
738 IF ( funcmode = 'RUN' ) THEN
739
740 --Read attributes from WorkFlow
741 -- l_khr_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCQUOTEID');
742 l_khr_id:=WF_ENGINE.GetItemAttrText( itemtype => itemtype,
743 itemkey => itemkey,
744 aname => 'SSCKHRID');
745 l_kle_id:=WF_ENGINE.GetItemAttrText( itemtype => itemtype,
746 itemkey => itemkey,
747 aname => 'SSCKLEID');
748 l_qte_id:=WF_ENGINE.GetItemAttrText( itemtype => itemtype,
749 itemkey => itemkey,
750 aname => 'SSCQTEID');
751 l_requestor_id:=WF_ENGINE.GetItemAttrText( itemtype => itemtype,
752 itemkey => itemkey,
753 aname => 'REQUESTOR_ID');
754 -- l_requestor_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID');
755 -- l_resp_key:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCRESPONSIBILITYKEY');
756 --l_resp_key:='OKLCSMGR';
757
758
759 --Read from table
760 OPEN contract_info_cur(l_khr_id);
761 FETCH contract_info_cur INTO l_contract_number;
762 CLOSE contract_info_cur;
763
764 OPEN asset_info_cur(l_kle_id);
765 FETCH asset_info_cur INTO l_asset_number;
766 CLOSE asset_info_cur;
767
768 OPEN quote_info_cur(l_qte_id);
769 FETCH quote_info_cur INTO l_quote_number;
770 CLOSE quote_info_cur;
771
772 OPEN requestor_info_cur(l_requestor_id);
773 FETCH requestor_info_cur INTO l_user_name;
774 CLOSE requestor_info_cur;
775
776 /*
777 SELECT responsibility_id INTO l_resp_id
778 FROM fnd_responsibility
779 WHERE responsibility_key = l_resp_key
780 AND application_id = 540; */
781
782 WF_ENGINE.SetItemAttrText ( itemtype=> itemtype,
783 itemkey => itemkey,
784 aname => 'SSCASSET',
785 avalue => l_asset_number);
786
787 WF_ENGINE.SetItemAttrText ( itemtype=> itemtype,
788 itemkey => itemkey,
789 aname => 'SSCCONTRACT',
790 avalue => l_contract_number);
791
792 WF_ENGINE.SetItemAttrText ( itemtype=> itemtype,
793 itemkey => itemkey,
794 aname => 'LEASE_AGENT_ID',
795 avalue => 'LEASE');
796
797
798 WF_ENGINE.SetItemAttrText ( itemtype=> itemtype,
799 itemkey => itemkey,
800 aname => 'SSCQTENUM',
801 avalue => l_quote_number);
802
803 WF_ENGINE.SetItemAttrText ( itemtype=> itemtype,
804 itemkey => itemkey,
805 aname => 'REQUESTOR_NAME',
806 avalue => l_user_name);
807
808 -- WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCPERFORMER', l_respString||l_resp_id);
809 -- WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCPERFORMER', 'LEASE');
810
811
812 resultout := 'COMPLETE';
813
814 RETURN;
815 END IF;
816
817
818 IF ( funcmode = 'CANCEL' ) THEN
819 resultout := 'COMPLETE';
820 RETURN;
821 END IF;
822
823 IF ( funcmode = 'RESPOND') THEN
824 resultout := 'COMPLETE';
825 RETURN;
826 END IF;
827
828 IF ( funcmode = 'FORWARD') THEN
829 resultout := 'COMPLETE';
830 RETURN;
831 END IF;
832
833 IF ( funcmode = 'TRANSFER') THEN
834 resultout := 'COMPLETE';
835 RETURN;
836 END IF;
837
838 IF ( funcmode = 'TIMEOUT' ) THEN
839 resultout := 'COMPLETE';
840 ELSE
841 resultout := wf_engine.eng_timedout;
842 RETURN;
843 END IF;
844
845 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
846 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
847 'OKL_VSS_WF.populate_req_repqte_attr_wf.',
848 'End(-)');
849 END IF;
850
851 EXCEPTION
852 WHEN OTHERS THEN
853 WF_CORE.CONTEXT (G_PKG_NAME, 'populate_req_repqte_attr_wf', itemtype, itemkey);
854
855 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
856 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
857 'OKL_VSS_WF.populate_req_repqte_attr_wf.',
858 'EXP - OTHERS');
859 END IF;
860 RAISE;
861
862 END populate_req_repqte_attr_wf;
863
864 PROCEDURE approve_quote_status( itemtype IN VARCHAR2,
865 itemkey IN VARCHAR2,
866 actid IN NUMBER,
867 funcmode IN VARCHAR2,
868 resultout OUT NOCOPY VARCHAR2 ) AS
869
870
871 l_id VARCHAR2(100);
872 l_approved VARCHAR2(1);
873 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
874 x_msg_count NUMBER;
875 x_msg_data VARCHAR2(2000);
876 p_qtev_rec OKL_QTE_PVT.qtev_rec_type;
877 x_qtev_rec OKL_QTE_PVT.qtev_rec_type;
878 p_api_version NUMBER := 1;
879 p_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
880
881 API_ERROR EXCEPTION;
882
883 l_notify_response VARCHAR2(30);
884 BEGIN
885
886 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
887 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
888 'OKL_VSS_WF.approve_quote_status.',
889 'Begin(+)');
890 END IF;
891
892 IF (funcmode = 'RUN') THEN
893 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
894 itemkey => itemkey,
895 aname => 'SSCQTEID');
896
897 /*
898 l_approved := wf_engine.GetItemAttrText( itemtype => itemtype,
899 itemkey => itemkey,
900 aname => 'APPROVED_YN'); */
901
902 -- Set the quote status to REJECTED if the approval is declined
903 -- else set to 'APPROVED'
904 /* IF nvl(l_approved, 'Y') = 'N' THEN
905 p_qtev_rec.QST_CODE := 'REJECTED';
906 ELSE */
907 p_qtev_rec.QST_CODE := 'APPROVED';
908 p_qtev_rec.DATE_APPROVED := SYSDATE;
909 -- END IF;
910
911 p_qtev_rec.ID := to_number(l_id);
912
913 p_qtev_rec.APPROVED_YN := 'Y';
914
915 okl_qte_pvt.update_row( p_api_version => p_api_version,
916 p_init_msg_list => p_init_msg_list,
917 x_return_status => x_return_status,
918 x_msg_count => x_msg_count,
919 x_msg_data => x_msg_data,
920 p_qtev_rec => p_qtev_rec,
921 x_qtev_rec => x_qtev_rec);
922
923 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
924 IF nvl(l_approved, 'Y') = 'Y' THEN
925 resultout := 'COMPLETE:SUCCESS';
926 END IF;
927 ELSE
928 RAISE API_ERROR;
929 END IF;
930
931 RETURN ;
932 END IF;
933
934 --
935 -- CANCEL mode
936 --
937 IF (funcmode = 'CANCEL') THEN
938 --
939 resultout := 'COMPLETE:';
940 RETURN;
941 --
942 END IF;
943
944 --
945 -- TIMEOUT mode
946 --
947 IF (funcmode = 'TIMEOUT') THEN
948 --
949 resultout := 'COMPLETE:';
950 RETURN;
951 --
952 END IF;
953
954 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
955 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
956 'OKL_VSS_WF.approve_quote_status.',
957 'End(-)');
958 END IF;
959
960 EXCEPTION
961
962 WHEN API_ERROR THEN
963
964 wf_core.context('OKL_VSS_WF' , 'approve_quote_status', itemtype, itemkey, actid, funcmode);
965
966 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
967 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
968 'OKL_VSS_WF.approve_quote_status.',
969 'EXP - API_ERROR');
970 END IF;
971 RAISE;
972
973 WHEN OTHERS THEN
974
975 wf_core.context('OKL_VSS_WF' , 'approve_quote_status', itemtype, itemkey, actid, funcmode);
976
977 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
978 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
979 'OKL_VSS_WF.approve_quote_status.',
980 'EXP - OTHERS');
981 END IF;
982
983 RAISE;
984
985 END approve_quote_status;
986
987 PROCEDURE reject_quote_status( itemtype IN VARCHAR2,
988 itemkey IN VARCHAR2,
989 actid IN NUMBER,
990 funcmode IN VARCHAR2,
991 resultout OUT NOCOPY VARCHAR2 ) AS
992
993
994 l_id VARCHAR2(100);
995 l_approved VARCHAR2(1);
996 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
997 x_msg_count NUMBER;
998 x_msg_data VARCHAR2(2000);
999 p_qtev_rec OKL_QTE_PVT.qtev_rec_type;
1000 x_qtev_rec OKL_QTE_PVT.qtev_rec_type;
1001 p_api_version NUMBER := 1;
1002 p_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
1003
1004 API_ERROR EXCEPTION;
1005
1006 l_notify_response VARCHAR2(30);
1007 BEGIN
1008
1009 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1010 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1011 'OKL_VSS_WF.reject_quote_status.',
1012 'Begin(+)');
1013 END IF;
1014
1015 IF (funcmode = 'RUN') THEN
1016 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1017 itemkey => itemkey,
1018 aname => 'SSCQTEID');
1019
1020 /*
1021 l_approved := wf_engine.GetItemAttrText( itemtype => itemtype,
1022 itemkey => itemkey,
1023 aname => 'APPROVED_YN'); */
1024
1025 -- Set the quote status to REJECTED if the approval is declined
1026
1027
1028 p_qtev_rec.QST_CODE := 'REJECTED';
1029
1030
1031 p_qtev_rec.ID := to_number(l_id);
1032
1033 p_qtev_rec.APPROVED_YN := 'N';
1034
1035 okl_qte_pvt.update_row( p_api_version => p_api_version,
1036 p_init_msg_list => p_init_msg_list,
1037 x_return_status => x_return_status,
1038 x_msg_count => x_msg_count,
1039 x_msg_data => x_msg_data,
1040 p_qtev_rec => p_qtev_rec,
1041 x_qtev_rec => x_qtev_rec);
1042
1043 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1044 IF nvl(l_approved, 'N') = 'N' THEN
1045 resultout := 'COMPLETE:ERROR';
1046 END IF;
1047 ELSE
1048 RAISE API_ERROR;
1049 END IF;
1050
1051 RETURN ;
1052 END IF;
1053
1054 --
1055 -- CANCEL mode
1056 --
1057 IF (funcmode = 'CANCEL') THEN
1058 --
1059 resultout := 'COMPLETE:';
1060 RETURN;
1061 --
1062 END IF;
1063
1064 --
1065 -- TIMEOUT mode
1066 --
1067 IF (funcmode = 'TIMEOUT') THEN
1068 --
1069 resultout := 'COMPLETE:';
1070 RETURN;
1071 --
1072 END IF;
1073
1074 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1075 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1076 'OKL_VSS_WF.reject_quote_status.',
1077 'End(-)');
1078 END IF;
1079
1080 EXCEPTION
1081
1082 WHEN API_ERROR THEN
1083
1084 wf_core.context('OKL_VSS_WF' , 'reject_quote_status', itemtype, itemkey, actid, funcmode);
1085
1086 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1087 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1088 'OKL_VSS_WF.reject_quote_status.',
1089 'EXP - API_ERROR');
1090 END IF;
1091 RAISE;
1092
1093 WHEN OTHERS THEN
1094
1095 wf_core.context('OKL_VSS_WF' , 'reject_quote_status', itemtype, itemkey, actid, funcmode);
1096
1097 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1098 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1099 'OKL_VSS_WF.reject_quote_status.',
1100 'EXP - OTHERS');
1101 END IF;
1102
1103 RAISE;
1104
1105 END reject_quote_status;
1106
1107 END OKL_VSS_WF;