[Home] [Help]
PACKAGE BODY: APPS.OKL_KBK_APPROVALS_WF
Source
1 PACKAGE BODY Okl_Kbk_Approvals_Wf AS
2 /* $Header: OKLRBWFB.pls 120.17.12020000.3 2013/02/04 12:43:59 jjuneja ship $ */
3
4 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
5 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
6 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
7 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
8 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_PARENT_TABLE_TOKEN;
9 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_CHILD_TABLE_TOKEN;
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13 G_API_TYPE CONSTANT VARCHAR2(200) := '_PVT';
14 G_CHAR_AMPERSAND CONSTANT VARCHAR2(1) := '&';
15 -------------------------------------------------------------------------------------------------
16 ----------------------------- Messages and constant names ---------------------------------------
17 -------------------------------------------------------------------------------------------------
18 G_KHR_STATUS_NOT_COMPLETE VARCHAR2(200) := 'OKL_LLA_NOT_COMPLETE';
19 G_TRANS_APP_NAME CONSTANT VARCHAR2(200) := 'OKL LA Contract Booking Approval';
20 G_INVALID_APP VARCHAR2(200) := 'OKL_LLA_INVALID_APPLICATION';
21
22 --mvasudev
23 G_MSG_TOKEN_CONTRACT_NUMBER CONSTANT VARCHAR2(30) := 'CONTRACT_NUMBER';
24 G_EVENT_APPROVE_WF CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.approve_lease_contract';
25 G_EVENT_APPROVE_AME CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.approve_lease_contract';
26 G_LEASE_CONTRACT_APPROVAL_WF CONSTANT VARCHAR2(2) := 'WF';
27 G_LEASE_CONTRACT_APPROVAL_AME CONSTANT VARCHAR2(3) := 'AME';
28 G_TRX_TYPE_CONTRACT_APPROVAL CONSTANT VARCHAR2(20) := 'CONTRACT_APPROVAL';
29 G_TRX_TCN_TYPE CONSTANT VARCHAR2(3) := 'APR';
30 G_TRX_TSU_CODE_SUBMITTED CONSTANT VARCHAR2(10) := 'SUBMITTED';
31 G_TRX_TSU_CODE_PROCESSED CONSTANT VARCHAR2(10) := 'PROCESSED';
32 G_SOURCE_TRX_TYPE_WF CONSTANT VARCHAR2(10) := 'WF';
33
34 G_KHR_STS_PENDING_APPROVAL CONSTANT VARCHAR2(20) := 'PENDING_APPROVAL';
35 G_KHR_STS_COMPLETE CONSTANT VARCHAR2(10) := 'COMPLETE';
36 G_KHR_STS_INCOMPLETE CONSTANT VARCHAR2(15) := 'INCOMPLETE';
37 G_KHR_STS_APPROVED CONSTANT VARCHAR2(15) := 'APPROVED';
38
39 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(20) := 'CONTRACT_ID';
40 G_WF_ITM_CONTRACT_NUMBER CONSTANT VARCHAR2(20) := 'CONTRACT_NUMBER';
41 G_WF_ITM_APPLICATION_ID CONSTANT VARCHAR2(20) := 'APPLICATION_ID';
42 G_WF_ITM_TRANSACTION_TYPE_ID CONSTANT VARCHAR2(20) := 'TRX_TYPE_ID';
43 G_WF_ITM_TRANSACTION_ID CONSTANT VARCHAR2(20) := 'TRANSACTION_ID';
44 G_WF_ITM_REQUESTER CONSTANT VARCHAR2(20) := 'REQUESTER';
45 G_WF_ITM_REQUESTER_ID CONSTANT VARCHAR2(20) := 'REQUESTER_ID';
46 G_WF_ITM_APPROVER CONSTANT VARCHAR2(20) := 'APPROVER';
47 G_WF_ITM_APPROVAL_REQ_MSG CONSTANT VARCHAR2(30) := 'APPROVAL_REQUEST_MESSAGE';
48 G_WF_ITM_RESULT CONSTANT VARCHAR2(10) := 'RESULT';
49 G_WF_ITM_PARENT_ITEM_KEY CONSTANT VARCHAR2(20) := 'PARENT_ITEM_KEY';
50 G_WF_ITM_PARENT_ITEM_TYPE CONSTANT VARCHAR2(20) := 'PARENT_ITEM_TYPE';
51 G_WF_ITM_APPROVED_YN CONSTANT VARCHAR2(15) := 'APPROVED_YN';
52 G_WF_ITM_MASTER CONSTANT VARCHAR2(10) := 'MASTER';
53 G_WF_ITM_MESSAGE_DESCRIPTION CONSTANT VARCHAR2(30) := 'MESSAGE_DESCRIPTION';
54 G_WF_ITM_MESSAGE_DOC CONSTANT VARCHAR2(15) := 'MESSAGE_DOC';
55 G_WF_ITM_MESSAGE_SUBJECT CONSTANT VARCHAR2(20) := 'MESSAGE_SUBJECT';
56 G_WF_ITM_APP_REQUEST_SUB CONSTANT VARCHAR2(30) := 'APP_REQUEST_SUB';
57 G_WF_ITM_APP_REMINDER_SUB CONSTANT VARCHAR2(30) := 'APP_REMINDER_SUB';
58 G_WF_ITM_APP_APPROVED_SUB CONSTANT VARCHAR2(30) := 'APP_APPROVED_SUB';
59 G_WF_ITM_APP_REJECTED_SUB CONSTANT VARCHAR2(30) := 'APP_REJECTED_SUB';
60 G_WF_ITM_APP_REMINDER_HEAD CONSTANT VARCHAR2(30) := 'APP_REMINDER_HEAD';
61 G_WF_ITM_APP_APPROVED_HEAD CONSTANT VARCHAR2(30) := 'APP_APPROVED_HEAD';
62 G_WF_ITM_APP_REJECTED_HEAD CONSTANT VARCHAR2(30) := 'APP_REJECTED_HEAD';
63 G_WF_ITM_API_ERR_MSG CONSTANT VARCHAR2(30) := 'API_ERR_MSG'; -- Bug# 9873432
64 G_WF_ITM_FROM_ROLE CONSTANT VARCHAR2(30) := '#FROM_ROLE'; --Added by jjuneja.Bug# 15991081
65 G_WF_ITM_RESULT_APPROVED CONSTANT VARCHAR2(15) := 'APPROVED';
66 G_WF_ITM_APPROVED_YN_YES CONSTANT VARCHAR2(1) := 'Y';
67 G_WF_ITM_APPROVED_YN_NO CONSTANT VARCHAR2(1) := 'N';
68
69 G_ITEM_TYPE_WF CONSTANT VARCHAR2(10) := 'OKLLAAPP';
70 G_APPROVAL_PROCESS_WF CONSTANT VARCHAR2(30) := 'KBK_APPROVAL_WF';
71
72 G_ITEM_TYPE_AME CONSTANT VARCHAR2(10) := 'OKLAMAPP';
73 G_APPROVAL_PROCESS_AME CONSTANT VARCHAR2(30) := 'APPROVAL_PROC';
74
75 G_DEFAULT_USER CONSTANT VARCHAR2(10) := 'SYSADMIN';
76 G_DEFAULT_USER_DESC CONSTANT VARCHAR2(30) := 'System Administrator';
77 G_WF_USER_ORIG_SYSTEM_HR CONSTANT VARCHAR2(5) := 'PER';
78
79
80 --------------------------------------------------------------------------------------------------
81 PROCEDURE l_get_agent(p_user_id IN NUMBER,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_name OUT NOCOPY VARCHAR2,
84 x_description OUT NOCOPY VARCHAR2) IS
85
86 CURSOR wf_users_csr(c_user_id NUMBER)
87 IS
88 SELECT NAME, DISPLAY_NAME
89 FROM WF_USERS
90 WHERE orig_system_id = c_user_id
91 AND ORIG_SYSTEM = G_WF_USER_ORIG_SYSTEM_HR;
92
93 CURSOR fnd_users_csr(c_user_id NUMBER)
94 IS
95 SELECT USER_NAME, DESCRIPTION
96 FROM FND_USER
97 WHERE user_id = c_user_id;
98 BEGIN
99 x_return_status := OKL_API.G_RET_STS_SUCCESS;
100 OPEN wf_users_csr(p_user_id);
101 FETCH wf_users_csr INTO x_name, x_description;
102 CLOSE wf_users_csr;
103 IF x_name IS NULL THEN
104 OPEN fnd_users_csr(p_user_id);
105 FETCH fnd_users_csr INTO x_name, x_description;
106 CLOSE fnd_users_csr;
107 IF x_name IS NULL THEN
108 x_name := G_DEFAULT_USER_DESC;
109 x_description := G_DEFAULT_USER_DESC;
110 END IF;
111 END IF;
112 EXCEPTION
113 WHEN OTHERS THEN
114 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
115 END l_get_agent;
116
117 --------------------------------------------------------------------------------------------------
118 PROCEDURE l_change_k_status(p_api_version IN NUMBER,
119 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
120 x_return_status OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER,
122 x_msg_data OUT NOCOPY VARCHAR2,
123 p_khr_status IN OKC_K_HEADERS_V.STS_CODE%TYPE,
124 p_chr_id IN NUMBER) IS
125 l_api_name CONSTANT VARCHAR2(30) := 'l_change_k_status';
126 BEGIN
127 x_return_status := OKL_API.G_RET_STS_SUCCESS;
128 -- Call start_activity to create savepoint, check compatibility
129 -- and initialize message list
130 x_return_status := OKL_API.START_ACTIVITY (
131 l_api_name,
132 p_init_msg_list,
133 '_PVT',
134 x_return_status);
135
136 -- Check if activity started successfully
137 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
138 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
139 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
140 RAISE OKL_API.G_EXCEPTION_ERROR;
141 END IF;
142 Okl_Contract_Status_Pub.update_contract_status(
143 p_api_version => p_api_version,
144 p_init_msg_list => p_init_msg_list,
145 x_return_status => x_return_status,
146 x_msg_count => x_msg_count,
147 x_msg_data => x_msg_data,
148 p_khr_status => p_khr_status,
149 p_chr_id => p_chr_id);
150
151 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
152 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
153 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
154 RAISE OKL_API.G_EXCEPTION_ERROR;
155 END IF;
156
157 --call to cascade status on to lines
158 Okl_Contract_Status_Pub.cascade_lease_status(
159 p_api_version => p_api_version,
160 p_init_msg_list => p_init_msg_list,
161 x_return_status => x_return_status,
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data,
164 p_chr_id => p_chr_id);
165
166 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
167 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
168 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
169 RAISE OKL_API.G_EXCEPTION_ERROR;
170 END IF;
171 OKL_API.END_ACTIVITY (x_msg_count,
172 x_msg_data );
173 EXCEPTION
174 WHEN OKL_API.G_EXCEPTION_ERROR THEN
175 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
176 l_api_name,
177 G_PKG_NAME,
178 'OKL_API.G_RET_STS_ERROR',
179 x_msg_count,
180 x_msg_data,
181 '_PVT');
182 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
183 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
184 l_api_name,
185 G_PKG_NAME,
186 'OKL_API.G_RET_STS_UNEXP_ERROR',
187 x_msg_count,
188 x_msg_data,
189 '_PVT');
190 WHEN OTHERS THEN
191 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
192 l_api_name,
193 G_PKG_NAME,
194 'OTHERS',
195 x_msg_count,
196 x_msg_data,
197 '_PVT');
198 END l_change_k_status;
199
200 FUNCTION get_message(p_msg_name IN VARCHAR2,p_contract_number IN VARCHAR2)
201 RETURN VARCHAR2
202 IS
203 l_message VARCHAR2(100);
204 BEGIN
205 IF p_msg_name IS NOT NULL THEN
206 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME
207 ,NAME => p_msg_name);
208 Fnd_Message.SET_TOKEN(TOKEN => G_MSG_TOKEN_CONTRACT_NUMBER,
209 VALUE => p_contract_number);
210 l_message := fnd_message.get();
211 END IF;
212
213 RETURN l_message;
214 EXCEPTION
215 WHEN OTHERS THEN
216 RETURN NULL;
217 END get_message;
218
219 --------------------------------------------------------------------------------------------------
220 ----------------------------------Rasing Business Event ------------------------------------------
221 --------------------------------------------------------------------------------------------------
222 PROCEDURE raise_approval_event (p_api_version IN NUMBER,
223 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2,
227 p_contract_id IN OKC_K_HEADERS_B.ID%TYPE)
228 IS
229
230 -- Get Contract Details
231 CURSOR c_fetch_k_number(p_contract_id OKC_K_HEADERS_V.ID%TYPE)
232 IS
233 SELECT chrv.contract_number,
234 chrv.sts_code
235 FROM okc_k_headers_v chrv
236 WHERE chrv.id = p_contract_id;
237
238 -- Get the valid application id from FND
239 CURSOR c_get_app_id_csr
240 IS
241 SELECT APPLICATION_ID
242 FROM FND_APPLICATION
243 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
244
245 -- Modified cursor by bkatraga for bug 9118673
246 -- Changed CURSOR c_get_trx_type_csr SQL definition
247 -- Get the Transaction Type Id from OAM
248 CURSOR c_get_trx_type_csr(c_trx_type VARCHAR2)
249 IS
250 SELECT B.TRANSACTION_TYPE_ID,
251 B.FND_APPLICATION_ID
252 FROM AME_CALLING_APPS B,
253 AME_CALLING_APPS_TL T
254 WHERE B.APPLICATION_ID = T.APPLICATION_ID
255 AND T.LANGUAGE = 'US'
256 AND T.APPLICATION_NAME = c_trx_type;
257
258 CURSOR l_wf_item_key_csr IS
259 SELECT okl_wf_item_s.NEXTVAL item_key
260 FROM dual;
261
262 CURSOR l_trx_try_csr IS
263 SELECT id
264 FROM okl_trx_types_b
265 WHERE trx_type_class = G_TRX_TYPE_CONTRACT_APPROVAL;
266
267 -- Bug 13103855: Cursor to fetch the CREATED_BY from the OKC_K_HEADERS_ALL_B table
268 CURSOR c_fetch_created_by(p_khr_id NUMBER)
269 IS
270 SELECT created_by
271 FROM okc_k_headers_all_b
272 WHERE id = p_khr_id;
273
274 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
275 l_api_version NUMBER := 1.0;
276 l_api_name CONSTANT VARCHAR2(30) := 'raise_approval_event';
277 l_msg_count NUMBER;
278 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
279 l_msg_data VARCHAR2(2000);
280
281 l_parameter_list wf_parameter_list_t;
282 l_key VARCHAR2(240);
283 l_event_name VARCHAR2(240);
284
285 l_application_id FND_APPLICATION.APPLICATION_ID%TYPE;
286 l_trans_appl_id AME_CALLING_APPS.APPLICATION_ID%TYPE;
287 l_trans_type_id AME_CALLING_APPS.TRANSACTION_TYPE_ID%TYPE;
288
289 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
290 l_chrv_id OKC_K_HEADERS_V.ID%TYPE := p_contract_id;
291 l_sts_code OKC_K_HEADERS_V.STS_CODE%TYPE;
292
293 l_requester VARCHAR2(200);
294 l_name VARCHAR2(200);
295 l_requester_id VARCHAR2(200);
296
297 l_trxH_in_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
298 l_trxH_out_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
299 l_approval_option VARCHAR2(5);
300 --Added by dpsingh for LE Uptake
301 l_legal_entity_id NUMBER;
302 BEGIN
303 x_return_status := OKL_API.G_RET_STS_SUCCESS;
304 -- Call start_activity to create savepoint, check compatibility
305 -- and initialize message list
306 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
307 p_init_msg_list,
308 '_PVT',
309 x_return_status);
310 -- Check if activity started successfully
311 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
312 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
313 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
314 RAISE OKL_API.G_EXCEPTION_ERROR;
315 END IF;
316
317 -- mvasudev
318 -- Create Internal Transaction
319
320 -- Get the user id, Item key
321 l_requester_id := FND_GLOBAL.USER_ID;
322
323 -- Bug 13103855: For ESG Contracts, observed that during Import
324 -- AME was showing the requestor as Anonymous. This is because
325 -- the FND User ID context is lost.
326 -- Make sure that if l_requester_id is -1, reset it from OKC_K_HEADERS_ALL_B.CREATED_BY
327 IF l_requester_id = - 1
328 THEN
329 --
330 FOR t_rec IN c_fetch_created_by( p_khr_id => p_contract_id )
331 LOOP
332 l_requester_id := t_rec.created_by;
333 END LOOP;
334 END IF;
335
336 l_get_agent(p_user_id => l_requester_id,
337 x_return_status => x_return_status,
338 x_name => l_requester,
339 x_description => l_name);
340
341 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
342 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
343 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
344 RAISE OKL_API.G_EXCEPTION_ERROR;
345 END IF;
346
347 FOR l_wf_item_key_rec IN l_wf_item_key_csr
348 LOOP
349 l_key := l_wf_item_key_rec.item_key;
350 END LOOP;
351
352 FOR l_trx_try_rec IN l_trx_try_csr
353 LOOP
354 l_trxH_in_rec.try_id := l_trx_try_rec.id;
355 END LOOP;
356
357
358
359 l_trxH_in_rec.khr_id := p_contract_id;
360 l_trxH_in_rec.tcn_type := G_TRX_TCN_TYPE;
361 l_trxH_in_rec.tsu_code := G_TRX_TSU_CODE_SUBMITTED;
362 l_trxH_in_rec.description := l_requester_id; -- requestor user_id
363 l_trxH_in_rec.date_transaction_occurred := SYSDATE; -- sysdate
364 l_trxH_in_rec.source_trx_id := l_key;
365 l_trxH_in_rec.source_trx_type := G_SOURCE_TRX_TYPE_WF;
366
367 --Added by dpsingh for LE Uptake
368 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_contract_id) ;
369 IF l_legal_entity_id IS NOT NULL THEN
370 l_trxH_in_rec.legal_entity_id := l_legal_entity_id;
371 ELSE
372 Okl_Api.set_message(p_app_name => g_app_name,
373 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
374 p_token1 => 'CONTRACT_NUMBER',
375 p_token1_value => l_contract_num);
376 RAISE OKL_API.G_EXCEPTION_ERROR;
377 END IF;
378 -- Create Transaction Header
379 Okl_Trx_Contracts_Pub.create_trx_contracts(
380 p_api_version => l_api_version
381 ,p_init_msg_list => l_init_msg_list
382 ,x_return_status => l_return_status
383 ,x_msg_count => l_msg_count
384 ,x_msg_data => l_msg_data
385 ,p_tcnv_rec => l_trxH_in_rec
386 ,x_tcnv_rec => l_trxH_out_rec);
387
388 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
389 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
390 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
391 RAISE Okl_Api.G_EXCEPTION_ERROR;
392 END IF;
393 -- end, mvasudev
394
395 l_approval_option := fnd_profile.value('OKL_LEASE_CONTRACT_APPROVAL_PROCESS');
396 IF l_approval_option = G_LEASE_CONTRACT_APPROVAL_AME THEN
397
398 -- Get the Contract Number
399 OPEN c_fetch_k_number(l_chrv_id);
400 FETCH c_fetch_k_number INTO l_contract_num,l_sts_code;
401 IF c_fetch_k_number%NOTFOUND THEN
402 OKL_API.set_message(p_app_name => G_APP_NAME,
403 p_msg_name => G_NO_MATCHING_RECORD,
404 p_token1 => G_COL_NAME_TOKEN,
405 p_token1_value => 'OKC_K_HEADERS_V.ID');
406 RAISE OKL_API.G_EXCEPTION_ERROR;
407 END IF;
408 CLOSE c_fetch_k_number;
409 IF l_sts_code <> G_KHR_STS_COMPLETE THEN
410 OKL_API.set_message(p_app_name => G_APP_NAME,
411 p_msg_name => G_KHR_STATUS_NOT_COMPLETE);
412 RAISE OKL_API.G_EXCEPTION_ERROR;
413 END IF;
414
415 -- Get the Application ID
416 OPEN c_get_app_id_csr;
417 FETCH c_get_app_id_csr INTO l_application_id;
418 IF c_get_app_id_csr%NOTFOUND THEN
419 OKL_API.set_message(p_app_name => G_APP_NAME,
420 p_msg_name => G_NO_MATCHING_RECORD,
421 p_token1 => G_COL_NAME_TOKEN,
422 p_token1_value => 'Application id');
423 RAISE OKL_API.G_EXCEPTION_ERROR;
424 END IF;
425 CLOSE c_get_app_id_csr;
426
427 -- Get the Transaction Type ID
428 OPEN c_get_trx_type_csr(G_TRANS_APP_NAME);
429 FETCH c_get_trx_type_csr INTO l_trans_type_id,
430 l_trans_appl_id;
431 IF c_get_trx_type_csr%NOTFOUND THEN
432 OKL_API.set_message(p_app_name => G_APP_NAME,
433 p_msg_name => G_NO_MATCHING_RECORD,
434 p_token1 => G_COL_NAME_TOKEN,
435 p_token1_value => 'AME Transcation TYPE id, Application id');
436 RAISE OKL_API.G_EXCEPTION_ERROR;
437 END IF;
438 CLOSE c_get_trx_type_csr;
439
440
441
442 IF l_application_id = l_trans_appl_id THEN
443 l_event_name := G_EVENT_APPROVE_AME;
444
445 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_contract_id,l_parameter_list);
446 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_NUMBER,l_contract_num,l_parameter_list);
447 wf_event.AddParameterToList(G_WF_ITM_APPLICATION_ID,l_application_id,l_parameter_list);
448 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_TYPE_ID,l_trans_type_id,l_parameter_list);
449 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_ID,l_trxH_out_rec.trx_number,l_parameter_list);
450 wf_event.AddParameterToList(G_WF_ITM_REQUESTER,l_requester,l_parameter_list);
451 wf_event.AddParameterToList(G_WF_ITM_REQUESTER_ID,l_requester_id,l_parameter_list);
452 --added by akrangan
453 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
454
455 ELSE
456 OKL_API.set_message(p_app_name => G_APP_NAME,
457 p_msg_name => G_INVALID_APP);
458 RAISE OKL_API.G_EXCEPTION_ERROR;
459 END IF; -- l_application_id
460
461 ELSIF l_approval_option = G_LEASE_CONTRACT_APPROVAL_WF THEN
462 l_event_name := G_EVENT_APPROVE_WF;
463
464 FOR c_fetch_k_number_rec IN c_fetch_k_number(l_chrv_id)
465 LOOP
466 l_contract_num := c_fetch_k_number_rec.contract_number;
467 END LOOP;
468
469 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_contract_id,l_parameter_list);
470 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_NUMBER,l_contract_num,l_parameter_list);
471 wf_event.AddParameterToList(G_WF_ITM_REQUESTER,l_requester,l_parameter_list);
472 wf_event.AddParameterToList(G_WF_ITM_REQUESTER_ID,l_requester_id,l_parameter_list);
473 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_ID,l_trxH_out_rec.trx_number,l_parameter_list);
474 --added by akrangan
475 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
476 --added by jjuneja for Bug# 15991081
477 wf_event.AddParameterToList(G_WF_ITM_FROM_ROLE,l_requester ,l_parameter_list);
478 ELSE
479 RAISE OKL_API.G_EXCEPTION_ERROR;
480 END IF; -- l_approval_option
481
482 -- We need to status to Approved Pending since We are sending for approval
483 l_change_k_status(p_api_version => p_api_version,
484 p_init_msg_list => p_init_msg_list,
485 x_return_status => x_return_status,
486 x_msg_count => x_msg_count,
487 x_msg_data => x_msg_data,
488 p_khr_status => G_KHR_STS_PENDING_APPROVAL,
489 p_chr_id => l_chrv_id);
490
491 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
492 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
493 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
494 RAISE OKL_API.G_EXCEPTION_ERROR;
495 END IF;
496
497 -- Raise Event
498 wf_event.RAISE(p_event_name => l_event_name,
499 p_event_key => l_key,
500 p_parameters => l_parameter_list);
501 l_parameter_list.DELETE;
502
503 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
504 x_msg_data => x_msg_data);
505 EXCEPTION
506 WHEN OKL_API.G_EXCEPTION_ERROR THEN
507 x_return_status := OKL_API.G_RET_STS_ERROR;
508 IF c_fetch_k_number%ISOPEN THEN
509 CLOSE c_fetch_k_number;
510 END IF;
511 IF c_get_app_id_csr%ISOPEN THEN
512 CLOSE c_get_app_id_csr;
513 END IF;
514 IF c_get_trx_type_csr%ISOPEN THEN
515 CLOSE c_get_trx_type_csr;
516 END IF;
517 IF l_wf_item_key_csr%ISOPEN THEN
518 CLOSE l_wf_item_key_csr;
519 END IF;
520 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
521 p_api_name => l_api_name,
522 p_pkg_name => G_PKG_NAME,
523 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
524 x_msg_count => x_msg_count,
525 x_msg_data => x_msg_data,
526 p_api_type => G_API_TYPE);
527 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
528 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
529 IF c_fetch_k_number%ISOPEN THEN
530 CLOSE c_fetch_k_number;
531 END IF;
532 IF c_get_app_id_csr%ISOPEN THEN
533 CLOSE c_get_app_id_csr;
534 END IF;
535 IF c_get_trx_type_csr%ISOPEN THEN
536 CLOSE c_get_trx_type_csr;
537 END IF;
538 IF l_wf_item_key_csr%ISOPEN THEN
539 CLOSE l_wf_item_key_csr;
540 END IF;
541 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
542 p_api_name => l_api_name,
543 p_pkg_name => G_PKG_NAME,
544 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
545 x_msg_count => x_msg_count,
546 x_msg_data => x_msg_data,
547 p_api_type => G_API_TYPE);
548 WHEN OTHERS THEN
549 IF c_fetch_k_number%ISOPEN THEN
550 CLOSE c_fetch_k_number;
551 END IF;
552 IF c_get_app_id_csr%ISOPEN THEN
553 CLOSE c_get_app_id_csr;
554 END IF;
555 IF c_get_trx_type_csr%ISOPEN THEN
556 CLOSE c_get_trx_type_csr;
557 END IF;
558 IF l_wf_item_key_csr%ISOPEN THEN
559 CLOSE l_wf_item_key_csr;
560 END IF;
561 -- store SQL error message on message stack
562 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
563 p_api_name => l_api_name,
564 p_pkg_name => G_PKG_NAME,
565 p_exc_name => 'OTHERS',
566 x_msg_count => x_msg_count,
567 x_msg_data => x_msg_data,
568 p_api_type => G_API_TYPE);
569 END raise_approval_event;
570
571 FUNCTION compile_message(p_contract_id IN NUMBER)
572 RETURN VARCHAR2
573 IS
574
575 l_msg_count NUMBER;
576 l_msg_data VARCHAR2(2000);
577 l_api_version NUMBER := 1;
578 l_init_msg_list VARCHAR2(3) := OKC_API.G_TRUE;
579 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
580
581 l_true_tax VARCHAR2(200);
582 l_cap_amt NUMBER;
583 l_res_value NUMBER;
584 -- l_message VARCHAR2(4000);
585
586 -- start: cklee 31-May-2005 okl.h Lease App IA Authoring
587 l_flag VARCHAR2(10);
588 l_message VARCHAR2(20000);
589 l_lease_app_found boolean;
590 l_dummy number;
591 -- end: cklee 31-May-2005 okl.h Lease App IA Authoring
592
593 -- Changed query as part of Bug#4693302 by zrehman : starts
594 CURSOR l_okl_khr_details_csr(p_contract_id OKC_K_HEADERS_V.ID%TYPE)
595 IS
596 SELECT chrv.contract_number,
597 chrv.sts_code,
598 stsv.meaning,
599 chrv.description,
600 chrv.currency_code,
601 pdtv.name product,
602 chrv.deal_type,
603 chrv.start_date,
604 chrv.end_date,
605 lgle.name legal_name,
606 INITCAP(REPLACE(ru.rule_information1,'_',' ')) eto,
607 ru.rule_information2 etm,
608 Okl_Am_Util_Pvt.get_party_name(chrv.id,'PRIVATE_LABEL') Pvt_Label,
609 chrv.pre_tax_irr,
610 chrv.after_tax_irr,
611 chrv.implicit_interest_rate,
612 chrv.after_tax_yield
613 FROM okc_rules_b ru,
614 okc_rule_groups_b rg,
615 xle_entity_profiles lgle,
616 okl_k_headers_full_v chrv,
617 okl_products_v pdtv,
618 okc_statuses_v stsv
619 WHERE chrv.id = p_contract_id
620 AND lgle.legal_entity_id = chrv.legal_entity_id
621 AND rg.dnz_chr_id = chrv.id
622 AND rg.rgd_code = 'AMTFOC'
623 AND ru.rgp_id = rg.id
624 AND ru.rule_information_category = 'AMBPOC'
625 AND pdtv.id = chrv.pdt_id
626 AND chrv.sts_code = stsv.code;
627 -- Changed query as part of Bug#4693302 by zrehman : ends
628
629 -- start: cklee 31-May-2005 okl.h Lease App IA Authoring
630 CURSOR l_okl_list_contents_csr(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
631 IS
632 SELECT DNZ_CHECKLIST_OBJ_ID,
633 TODO_ITEM_CODE,
634 TODO_ITEM_MEANING,
635 MANDATORY_FLAG_MEANING,
636 MANDATORY_FLAG,
637 USER_COMPLETE_FLAG_RESULTS,
638 FUNCTION_VALIDATE_RSTS_MEANING,
639 FUNCTION_ID,
640 FUNCTION_NAME,
641 CHECKIST_RESULTS
642 FROM okl_instance_checklist_dtl_uv
643 WHERE DNZ_CHECKLIST_OBJ_ID = p_chr_id;
644
645 CURSOR c_contract(p_contract_id OKC_K_HEADERS_B.ID%TYPE)
646 IS
647 select 1
648 from okc_k_headers_b
649 where ORIG_SYSTEM_SOURCE_CODE = 'OKL_LEASE_APP'
650 and id = p_contract_id;
651 -- end: cklee 31-May-2005 okl.h Lease App IA Authoring
652
653 BEGIN
654
655 --l_message := Fnd_Message.GET_STRING(G_APP_NAME,'OKL_LLA_REQUEST_APPROVAL');
656
657 FOR l_okl_khr_details_rec IN l_okl_khr_details_csr(p_contract_id)
658 LOOP
659 -- Start
660 l_message := l_message || '<TABLE width="100%" border="0" cellspacing="0" cellpadding="0">';
661
662 -- Empty Row
663 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
664
665 l_message := l_message || '<tr><td colspan=6>'
666 || '<table width="100%" border="0" cellspacing="0" cellpadding="0">';
667
668 -- Contract Number, Status
669 l_message := l_message || '<tr><td width="18%" align="right">'
670 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
671 p_attribute_code => 'OKL_KDTLS_CONTRACT_NUMBER')
672 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
673 || '<td width="36%"><b>'
674 || l_okl_khr_details_rec.contract_number
675 || '</b></td>'
676 || '<td width="13%" align="right">'
677 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
678 p_attribute_code => 'OKL_KDTLS_STATUS')
679 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
680 || '<td width="33%"><b>'
681 || l_okl_khr_details_rec.meaning
682 || '</b></td>'
683 || '</tr>';
684
685 -- Description, Product
686 l_message := l_message || '<tr><td width="18%" align="right">'
687 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
688 p_attribute_code => 'OKL_KDTLS_CONTRACT_DESCRIPTION')
689 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
690 || '<td width="36%"><b>'
691 || l_okl_khr_details_rec.description
692 || '</b></td>'
693 || '<td width="13%" align="right">'
694 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
695 p_attribute_code => 'OKL_KDTLS_PRODUCT')
696 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
697 || '<td width="33%"><b>'
698 || l_okl_khr_details_rec.product
699 || '</b></td>'
700 || '</tr>';
701
702
703
704 -- Empty Row
705 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
706
707 -- Currency
708 l_message := l_message || '<tr><td width="18%" align="right">'
709 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
710 p_attribute_code => 'OKL_KDTLS_CURRENCY')
711 || '</td><td width="1%">=</td>'
712 || '<td width="36%" colspan="4"><b>'
713 || l_okl_khr_details_rec.currency_code
714 || '</b></td>';
715
716 -- Empty Row
717 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
718
719 -- "Properties" Sub Head
720 l_message := l_message || '<tr><td align="right" width="16%"><h3><b>'
721 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
722 p_attribute_code => 'OKL_PROPERTIES')
723 || '</b></h3></td>'
724 || '<td colspan="5" valign="middle">'
725 || '<hr></td></tr>';
726 l_message := l_message || '</TABLE></td></tr>';
727
728 -- Empty Row
729 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
730
731 -- Book Classification
732 l_message := l_message || '
733 <tr>
734 <td align="right" width="40%">'
735 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
736 p_attribute_code => 'OKL_DEAL_TYPE')
737 || '</td>
738 <td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>
739 <td><b>'
740 || l_okl_khr_details_rec.deal_type
741 || '</b></td>
742 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
743 </tr>';
744
745 -- True Tax
746 IF l_okl_khr_details_rec.deal_type = 'LEASEOP' OR
747 l_okl_khr_details_rec.deal_type = 'LEASEDF' THEN
748 l_true_tax := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_HEADER_PROMPTS',
749 p_attribute_code => 'OKL_YES');
750 ELSE
751 l_true_tax := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_HEADER_PROMPTS',
752 p_attribute_code => 'OKL_NO');
753 END IF;
754
755 l_message := l_message || '
756 <tr>
757 <td align="right">'
758 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
759 p_attribute_code => 'OKL_TRUE_TAX')
760 || '</td>
761 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
762 <td><b>'
763 || l_true_tax
764 || '</b></td>
765 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
766 </tr>';
767
768 -- Start Date
769 l_message := l_message || '
770 <tr>
771 <td align="right">'
772 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
773 p_attribute_code => 'OKL_START_DATE')
774 || '</td>
775 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
776 <td><b>'
777 || TO_CHAR(l_okl_khr_details_rec.start_date,'dd-mm-yyyy')
778 || '</b></td>
779 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
780 </tr>';
781
782 -- End Date
783 l_message := l_message || '
784 <tr>
785 <td align="right">'
786 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
787 p_attribute_code => 'OKL_END_DATE')
788 || '</td>
789 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
790 <td><b>'
791 || TO_CHAR(l_okl_khr_details_rec.end_date,'dd-mm-yyyy')
792 || '</b></td>
793 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
794 </tr>';
795
796 -- Legal Entity
797 l_message := l_message || '
798 <tr>
799 <td align="right">'
800 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
801 p_attribute_code => 'OKL_LEGAL_ENTITY')
802 || '</td>
803 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
804 <td><b>'
805 || l_okl_khr_details_rec.legal_name
806 || '</b></td>
807 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
808 </tr>';
809
810 -- End of Term Option
811 l_message := l_message || '
812 <tr>
813 <td align="right">'
814 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
815 p_attribute_code => 'OKL_EOT_OPTION')
816 || '</td>
817 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
818 <td><b>'
819 || l_okl_khr_details_rec.eto
820 || '</b></td>
821 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
822 </tr>';
823
824 -- End of Term amount
825 l_message := l_message || '
826 <tr>
827 <td align="right">'
828 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
829 p_attribute_code => 'OKL_EOT_AMOUNT')
830 || '</td>
831 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
832 <td><b>'
833 || TO_CHAR(TO_NUMBER(l_okl_khr_details_rec.etm),'999,999,999,999,999,999.00')
834 || '</b></td>
835 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
836 </tr>';
837
838 -- Private Label
839 l_message := l_message || '
840 <tr>
841 <td align="right">'
842 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
843 p_attribute_code => 'OKL_PRIVATE_LABEL')
844 || '</td>
845 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
846 <td><b>'
847 || l_okl_khr_details_rec.pvt_label
848 || '</b></td>
849 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
850 </tr>';
851
852 -- Capital Amount
853 OKL_EXECUTE_FORMULA_PUB.EXECUTE(p_api_version => l_api_version,
854 p_init_msg_list => l_init_msg_list,
855 x_return_status => l_return_status,
856 x_msg_count => l_msg_count,
857 x_msg_data => l_msg_data,
858 --fmiao start bug 5178182
859 --Changed Formula from CONTRACT_CAP_AMNT to CONTRACT_FINANCED_AMOUNT_BKG
860 --This formula includes the rollover fee as part of Total Financed fee
861 --p_formula_name => 'CONTRACT_CAP_AMNT',
862 p_formula_name => 'CONTRACT_FINANCED_AMOUNT_BKG',
863 --fmiao end bug 5178182
864 p_contract_id => p_contract_id,
865 x_value => l_cap_amt);
866 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
867 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
868 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
869 RAISE OKL_API.G_EXCEPTION_ERROR;
870 END IF;
871 l_message := l_message || '
872 <tr>
873 <td align="right">'
874 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
875 p_attribute_code => 'OKL_TOTAL_FIN_AMOUNT')
876 || '</td>
877 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
878 <td><b>'
879 || TO_CHAR(l_cap_amt,'999,999,999,999,999,999.00')
880 || '</b></td>
881 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
882 </tr>';
883
884 -- Residual Value
885 OKL_EXECUTE_FORMULA_PUB.EXECUTE(p_api_version => l_api_version,
886 p_init_msg_list => l_init_msg_list,
887 x_return_status => l_return_status,
888 x_msg_count => l_msg_count,
889 x_msg_data => l_msg_data,
890 p_formula_name => 'CONTRACT_RESIDUAL_VALUE',
891 p_contract_id => p_contract_id,
892 x_value => l_res_value);
893 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
894 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
895 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
896 RAISE OKL_API.G_EXCEPTION_ERROR;
897 END IF;
898 l_message := l_message || '
899 <tr>
900 <td align="right">'
901 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
902 p_attribute_code => 'OKL_TOTAL_RES_AMOUNT')
903 || '</td>
904 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
905 <td><b>'
906 || TO_CHAR(l_res_value,'999,999,999,999,999,999.00')
907 || '</b></td>
908 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
909 </tr>';
910
911 -- "Yields" Sub Head
912 l_message := l_message || '<tr>
913 <td align="right">
914 <h3><b>'
915 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
916 p_attribute_code => 'OKL_YIELDS')
917 || '</b></h3>
918 </td>
919 <td colspan="5" valign="middle">
920 <hr>
921 </td>
922 </tr>';
923 -- Pre tax irr
924 l_message := l_message || '
925 <tr>
926 <td align="right">'
927 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
928 p_attribute_code => 'OKL_PRE_TAX_IRR')
929 || '</td>
930 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
931 <td><b>'
932 || l_okl_khr_details_rec.pre_tax_irr
933 || '</b></td>
934 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
935 </tr>';
936
937 -- After tax irr
938 l_message := l_message || '
939 <tr>
940 <td align="right">'
941 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
942 p_attribute_code => 'OKL_AFTER_TAX_IRR')
943 || '</td>
944 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
945 <td><b>'
946 || l_okl_khr_details_rec.after_tax_irr
947 || '</b></td>
948 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
949 </tr>';
950
951 -- Implicit Interest Rate
952 l_message := l_message || '
953 <tr>
954 <td align="right">'
955 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
956 p_attribute_code => 'OKL_IMPLICIT_IR')
957 || '</td>
958 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
959 <td><b>'
960 || l_okl_khr_details_rec.implicit_interest_rate
961 || '</b></td>
962 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
963 </tr>';
964 -- After tax yield
965 l_message := l_message || '
966 <tr>
967 <td align="right">'
968 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CONTRACT_BOOK',
969 p_attribute_code => 'OKL_AFTER_TAXT_ROE')
970 || '</td>
971 <td>' || G_CHAR_AMPERSAND || 'nbsp;</td>
972 <td><b>'
973 || l_okl_khr_details_rec.after_tax_yield
974 || '</b></td>
975 <td colspan=3>' || G_CHAR_AMPERSAND || 'nbsp;</td>
976 </tr>';
977
978 --
979 -- start: cklee 31-May-2005 okl.h Lease App IA Authoring
980 --
981 -- Empty Row
982 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
983
984 FOR each_row IN l_okl_list_contents_csr(p_contract_id) LOOP
985 l_flag := 'Passed';
986 -- IF(each_row.mandatory_flag = 'Y' and each_row.check_off_results <> 'Passed') THEN --cklee 06/01/2005
987 IF(each_row.mandatory_flag = 'Y' and each_row.CHECKIST_RESULTS <> 'Passed') THEN
988 l_flag := 'Failed';
989 EXIT;
990 END IF;
991 END LOOP;
992
993 IF l_flag = 'Passed' THEN
994 l_flag := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
995 p_attribute_code => 'OKL_PASSED');
996 ELSIF l_flag = 'Failed' THEN
997 l_flag := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
998 p_attribute_code => 'OKL_FAILED');
999 END IF;
1000
1001 -- Checklist Validation Result
1002 l_message := l_message || '<tr><td width="18%" align="right">'
1003 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
1004 p_attribute_code => 'OKL_CHKLIST_VAL_RESULT')
1005 || '</td><td width="1%">=</td>'
1006 || '<td width="36%" colspan="4"><b>'
1007 || l_flag
1008 || '</b></td>';
1009 -- Empty Row
1010 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
1011
1012 --
1013 -- end: cklee 31-May-2005 okl.h Lease App IA Authoring
1014 --
1015
1016 -- End
1017 l_message := l_message || '
1018 </TABLE>';
1019
1020 END LOOP; -- l_okl_khr_details_csr
1021
1022 --
1023 -- start: cklee 31-May-2005 okl.h Lease App IA Authoring
1024 --
1025 l_message := l_message || '<table class="x1h" cellpadding="1" cellspacing="0" border="1" width="100%">';
1026
1027 -- Headers for the creditline details table.
1028 -- Checklist Item
1029 l_message := l_message || '<tr> <th scope="col" class="x1r"> <span title="'
1030 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1031 p_attribute_code => 'OKL_ITEM')
1032 || '" class="x24">'
1033 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1034 p_attribute_code => 'OKL_ITEM')
1035 || '</span></th>';
1036
1037 -- Description
1038 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
1039 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1040 p_attribute_code => 'OKL_DESCRIPTION')
1041 || '" class="x24">'
1042 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1043 p_attribute_code => 'OKL_DESCRIPTION')
1044 || '</span></th>';
1045
1046 -- Function
1047 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
1048 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1049 p_attribute_code => 'OKL_FUNCTION')
1050 || '" class="x24">'
1051 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1052 p_attribute_code => 'OKL_FUNCTION')
1053 || '</span></th>';
1054
1055 -- Mandatory
1056 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
1057 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1058 p_attribute_code => 'OKL_MANDATORY')
1059 || '" class="x24">'
1060 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1061 p_attribute_code => 'OKL_MANDATORY')
1062 || '</span></th>';
1063
1064 -- Results
1065 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
1066 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1067 p_attribute_code => 'OKL_RESULTS')
1068 || '" class="x24">'
1069 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
1070 p_attribute_code => 'OKL_RESULTS')
1071 || '</span></th></tr>';
1072
1073
1074 FOR l_okl_list_contents_rec IN l_okl_list_contents_csr(p_contract_id)
1075 LOOP
1076 IF (l_okl_list_contents_rec.todo_item_code is not null)THEN
1077 l_message := l_message || '<tr><td class="x1l x4x">'
1078 || l_okl_list_contents_rec.todo_item_code
1079 || '</td>';
1080 ELSE
1081 l_message := l_message || '<tr><td class="x1l x4x"><br></td>';
1082 END IF;
1083 IF(l_okl_list_contents_rec.todo_item_meaning is not null)THEN
1084 l_message := l_message || '<td class="x1l x4x">'
1085 || l_okl_list_contents_rec.todo_item_meaning
1086 || '</td>';
1087 ELSE
1088 l_message := l_message || '<td class="x1l x4x"><br></td>';
1089 END IF;
1090 IF(l_okl_list_contents_rec.function_name is not null) THEN
1091 l_message := l_message || '<td class="x1l x4x">'
1092 || l_okl_list_contents_rec.function_name
1093 || '</td>';
1094 ELSE
1095 l_message := l_message || '<td class="x1l x4x"><br></td>';
1096 END IF;
1097 IF( l_okl_list_contents_rec.mandatory_flag_meaning is not null) THEN
1098 l_message := l_message || '<td class="x1l x4x">'
1099 || l_okl_list_contents_rec.mandatory_flag_meaning
1100 || '</td>';
1101 ELSE
1102 l_message := l_message || '<td class="x1l x4x"><br></td>';
1103 END IF;
1104 IF(l_okl_list_contents_rec.function_id is not null) THEN
1105 l_message := l_message || '<td class="x1l x4x">'
1106 || l_okl_list_contents_rec.FUNCTION_VALIDATE_RSTS_MEANING
1107 || '</td></tr>';
1108 ELSE
1109 l_message := l_message || '<td class="x1l x4x">'
1110 || l_okl_list_contents_rec.USER_COMPLETE_FLAG_RESULTS
1111 || '</td></tr>';
1112 END IF;
1113 END LOOP;
1114 l_message := l_message || '</table>';
1115 --
1116 -- end: cklee 31-May-2005 okl.h Lease App IA Authoring
1117 --
1118
1119 RETURN l_message;
1120
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 RETURN NULL;
1124 END compile_message;
1125
1126 /*
1127 -- This API is for Lease Contract Approval via WF
1128 */
1129 PROCEDURE Get_Lease_Contract_Approver(itemtype IN VARCHAR2,
1130 itemkey IN VARCHAR2,
1131 actid IN NUMBER,
1132 funcmode IN VARCHAR2,
1133 resultout OUT NOCOPY VARCHAR2)
1134 IS
1135 CURSOR l_fnd_users_csr(p_user_id NUMBER)
1136 IS
1137 SELECT USER_NAME
1138 FROM FND_USER
1139 WHERE user_id = p_user_id;
1140
1141 l_api_name VARCHAR2(200) := 'Get_Lease_Contract_Approver';
1142
1143 l_user_id VARCHAR2(200);
1144 l_contract_number OKC_K_HEADERS_V.contract_number%TYPE;
1145
1146 l_return_status VARCHAR2(1);
1147
1148 BEGIN
1149 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1150
1151 -- "RUN"
1152 IF (funcmode = 'RUN') THEN
1153 --l_user_id := fnd_profile.value('OKL_LEASE_CONTRACT_APPROVER');
1154 l_user_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1155 itemkey => itemkey,
1156 aname => G_WF_ITM_REQUESTER_ID);
1157
1158 l_contract_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1159 itemkey => itemkey,
1160 aname => G_WF_ITM_CONTRACT_NUMBER);
1161
1162 resultout := 'COMPLETE:NOT_FOUND'; -- default
1163 IF l_user_id IS NOT NULL THEN
1164 FOR l_fnd_users_rec IN l_fnd_users_csr(l_user_id)
1165 LOOP
1166 --Start of changes by jjuneja for Bug # 16189745
1167 --Commented following by jjuneja for Bug # 16189745
1168 /*
1169 wf_engine.SetItemAttrText (itemtype => itemtype,
1170 itemkey => itemkey,
1171 aname => G_WF_ITM_APPROVER,
1172 avalue => l_fnd_users_rec.user_name);
1173 */
1174 IF (( wf_engine.GetItemAttrText (itemtype => itemtype,
1175 itemkey => itemkey,
1176 aname => G_WF_ITM_APPROVER)) IS NULL)
1177 THEN
1178 wf_engine.SetItemAttrText (itemtype => itemtype,
1179 itemkey => itemkey,
1180 aname => G_WF_ITM_APPROVER,
1181 avalue => l_fnd_users_rec.user_name);
1182 END IF;
1183 --End of changes by jjuneja for Bug # 16189745
1184 wf_engine.SetItemAttrText (itemtype => itemtype,
1185 itemkey => itemkey,
1186 aname => G_WF_ITM_MESSAGE_SUBJECT,
1187 avalue => get_message('OKL_LLA_REQUEST_APPROVAL_SUB',l_contract_number));
1188
1189 --Added by jjuneja for Bug # 15991081
1190 wf_engine.SetItemAttrText (itemtype => itemtype,
1191 itemkey => itemkey,
1192 aname => G_WF_ITM_FROM_ROLE,
1193 avalue => l_fnd_users_rec.user_name);
1194 --End of addition by jjuneja
1195
1196 resultout := 'COMPLETE:FOUND';
1197 END LOOP;
1198 END IF; -- l_user_id
1199
1200 -- CANCEL mode
1201 ELSIF (funcmode = 'CANCEL') THEN
1202 resultout := 'COMPLETE:';
1203 RETURN;
1204 -- TIMEOUT mode
1205 ELSIF (funcmode = 'TIMEOUT') THEN
1206 resultout := 'COMPLETE:';
1207 RETURN;
1208 END IF; -- funcmode
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 wf_core.context(G_PKG_NAME,
1212 l_api_name,
1213 itemtype,
1214 itemkey,
1215 TO_CHAR(actid),
1216 funcmode);
1217 RAISE;
1218
1219 END Get_Lease_Contract_Approver;
1220
1221 --------------------------------------------------------------------------------------------------
1222 --------------------------------- Set Approval Status --------------------------------------------
1223 --------------------------------------------------------------------------------------------------
1224 PROCEDURE Set_Parent_Attributes(itemtype IN VARCHAR2,
1225 itemkey IN VARCHAR2,
1226 actid IN NUMBER,
1227 funcmode IN VARCHAR2,
1228 resultout OUT NOCOPY VARCHAR2) IS
1229
1230 l_approved_yn VARCHAR2(30);
1231 l_parent_key VARCHAR2(240);
1232 l_parent_type VARCHAR2(240);
1233 l_result VARCHAR2(30);
1234 l_api_name VARCHAR2(30) := 'Set_Parent_Attributes';
1235 l_contract_number okc_k_headers_v.contract_number%TYPE;
1236
1237 BEGIN
1238 SAVEPOINT set_atts;
1239 IF (funcmode = 'RUN') THEN
1240 -- Get current approval status
1241 l_result := wf_engine.GetItemAttrText (itemtype => itemtype,
1242 itemkey => itemkey,
1243 aname => G_WF_ITM_RESULT);
1244
1245 l_parent_key := wf_engine.GetItemAttrText (itemtype => itemtype,
1246 itemkey => itemkey,
1247 aname => G_WF_ITM_PARENT_ITEM_KEY);
1248
1249 l_parent_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1250 itemkey => itemkey,
1251 aname => G_WF_ITM_PARENT_ITEM_TYPE);
1252
1253 l_contract_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1254 itemkey => itemkey,
1255 aname => G_WF_ITM_CONTRACT_NUMBER);
1256
1257 IF l_result = G_WF_ITM_RESULT_APPROVED THEN
1258 l_approved_yn := G_WF_ITM_APPROVED_YN_YES;
1259 wf_engine.SetItemAttrText (itemtype => itemtype,
1260 itemkey => itemkey,
1261 aname => G_WF_ITM_MESSAGE_SUBJECT,
1262 avalue => get_message('OKL_LLA_REQUEST_APPROVED_SUB',l_contract_number));
1263 ELSE
1264 l_approved_yn := G_WF_ITM_APPROVED_YN_NO;
1265 wf_engine.SetItemAttrText (itemtype => itemtype,
1266 itemkey => itemkey,
1267 aname => G_WF_ITM_MESSAGE_SUBJECT,
1268 avalue => get_message('OKL_LLA_REQUEST_REJECTED_SUB',l_contract_number));
1269 END IF;
1270
1271 wf_engine.SetItemAttrText(itemtype => l_parent_type,
1272 itemkey => l_parent_key,
1273 aname => G_WF_ITM_APPROVED_YN,
1274 avalue => l_approved_yn);
1275 resultout := 'COMPLETE:';
1276 RETURN;
1277 END IF;
1278 -- CANCEL mode
1279 IF (funcmode = 'CANCEL') THEN
1280 resultout := 'COMPLETE:';
1281 RETURN;
1282 END IF;
1283 -- TIMEOUT mode
1284 IF (funcmode = 'TIMEOUT') THEN
1285 resultout := 'COMPLETE:';
1286 RETURN;
1287 END IF;
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 wf_core.context(G_PKG_NAME,
1291 l_api_name,
1292 itemtype,
1293 itemkey,
1294 TO_CHAR(actid),
1295 funcmode);
1296 RAISE;
1297 END Set_Parent_Attributes;
1298 --------------------------------------------------------------------------------------------------
1299 ----------------------------------Main Approval Process ------------------------------------------
1300 --------------------------------------------------------------------------------------------------
1301 PROCEDURE update_approval_status(itemtype IN VARCHAR2,
1302 itemkey IN VARCHAR2,
1303 actid IN NUMBER,
1304 funcmode IN VARCHAR2,
1305 resultout OUT NOCOPY VARCHAR2)
1306 IS
1307 CURSOR l_okl_trx_contracts_csr(p_trx_number IN VARCHAR2)
1308 IS
1309 SELECT id
1310 FROM okl_trx_contracts
1311 WHERE trx_number = p_trx_number
1312 --rkuttiya added for 12.1.1 Multi GAAP Project
1313 AND representation_type = 'PRIMARY';
1314 --
1315
1316 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1317 l_process_status VARCHAR2 (1) := okl_api.g_ret_sts_success; -- Bug#9873432
1318 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE; -- Bug#9873432
1319 l_api_version NUMBER := 1.0;
1320 l_msg_count NUMBER;
1321 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
1322 l_msg_data VARCHAR2(2000);
1323 l_api_name VARCHAR2(30) := 'update_approval_status';
1324
1325 l_chrv_id OKC_K_HEADERS_V.ID%TYPE;
1326 l_approved_yn VARCHAR2(30);
1327 l_trx_number VARCHAR2(100);
1328
1329 l_trxH_in_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
1330 l_trxH_out_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
1331
1332 l_msg_index_out NUMBER;
1333 l_data VARCHAR2(2000);
1334 l_message VARCHAR2(4000);
1335
1336 BEGIN
1337 -- We getting the contract_Id from WF
1338 l_chrv_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1339 itemkey => itemkey,
1340 aname => G_WF_ITM_CONTRACT_ID);
1341 --Run Mode
1342 IF funcmode = 'RUN' THEN
1343 l_approved_yn := wf_engine.GetItemAttrText (itemtype => itemtype,
1344 itemkey => itemkey,
1345 aname => G_WF_ITM_APPROVED_YN);
1346
1347 IF l_approved_yn = G_WF_ITM_APPROVED_YN_YES THEN
1348 l_change_k_status(p_api_version => l_api_version,
1349 p_init_msg_list => l_init_msg_list,
1350 x_return_status => l_return_status,
1351 x_msg_count => l_msg_count,
1352 x_msg_data => l_msg_data,
1353 p_khr_status => G_KHR_STS_APPROVED,
1354 p_chr_id => l_chrv_id);
1355 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1356 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1357 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1358 RAISE OKL_API.G_EXCEPTION_ERROR;
1359 END IF;
1360
1361 Okl_Contract_Book_Pvt.post_approval_process(p_api_version => l_api_version,
1362 p_init_msg_list => l_init_msg_list,
1363 x_return_status => l_return_status,
1364 x_msg_count => l_msg_count,
1365 x_msg_data => l_msg_data,
1366 p_chr_id => l_chrv_id,
1367 x_process_status => l_process_status -- Bug#9873432
1368 );
1369
1370 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1371 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1372 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1373 RAISE OKL_API.G_EXCEPTION_ERROR;
1374 END IF;
1375 ELSE
1376 l_change_k_status(p_api_version => l_api_version,
1377 p_init_msg_list => l_init_msg_list,
1378 x_return_status => l_return_status,
1379 x_msg_count => l_msg_count,
1380 x_msg_data => l_msg_data,
1381 p_khr_status => G_KHR_STS_INCOMPLETE,
1382 p_chr_id => l_chrv_id);
1383 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1384 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1385 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1386 RAISE OKL_API.G_EXCEPTION_ERROR;
1387 END IF;
1388 END IF;
1389
1390 -- trx's trx_number IS wf's trx_id
1391 l_trx_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1392 itemkey => itemkey,
1393 aname => G_WF_ITM_TRANSACTION_ID);
1394
1395 FOR l_okl_trx_contracts_rec IN l_okl_trx_contracts_csr(l_trx_number)
1396 LOOP
1397 l_trxH_in_rec.id := l_okl_trx_contracts_rec.id;
1398 END LOOP;
1399
1400 l_trxH_in_rec.tsu_code := G_TRX_TSU_CODE_PROCESSED;
1401
1402 Okl_Trx_Contracts_Pub.update_trx_contracts(
1403 p_api_version => l_api_version
1404 ,p_init_msg_list => l_init_msg_list
1405 ,x_return_status => l_return_status
1406 ,x_msg_count => l_msg_count
1407 ,x_msg_data => l_msg_data
1408 ,p_tcnv_rec => l_trxH_in_rec
1409 ,x_tcnv_rec => l_trxH_out_rec);
1410
1411 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1412 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1413 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1414 RAISE Okl_Api.G_EXCEPTION_ERROR;
1415 END IF;
1416
1417 resultout := 'COMPLETE:';
1418
1419 -- Bug#9873432 - Start
1420 -- Populate the message doc and subject for the notification to be sent to the requester
1421 IF l_process_status <> OKL_API.G_RET_STS_SUCCESS THEN
1422 l_contract_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1423 itemkey => itemkey,
1424 aname => G_WF_ITM_CONTRACT_NUMBER);
1425 -- Set the message subject
1426 wf_engine.SetItemAttrText (itemtype => itemtype,
1427 itemkey => itemkey,
1428 aname => G_WF_ITM_MESSAGE_SUBJECT,
1429 avalue => get_message('OKL_LLA_ACT_FAILURE_SUB',l_contract_number));
1430
1431 -- Set the Message Document
1432 wf_engine.SetItemAttrText (itemtype => itemtype,
1433 itemkey => itemkey,
1434 aname => G_WF_ITM_MESSAGE_DOC,
1435 avalue => 'plsql:Okl_Kbk_Approvals_Wf.pop_act_failure_doc/'|| itemkey);
1436
1437 -- Set the error message on workflow attribute to be retrieved later when sending notification
1438 l_msg_count := fnd_msg_pub.count_msg;
1439 l_message := null;
1440 IF (l_msg_count > 0) THEN
1441 FOR l_counter IN 1 .. l_msg_count
1442 LOOP
1443 fnd_msg_pub.get ( p_msg_index => l_counter
1444 , p_encoded => 'F'
1445 , p_data => l_data
1446 , p_msg_index_out => l_msg_index_out
1447 );
1448 l_message := l_message || l_data;
1449 END LOOP;
1450 END IF;
1451 wf_engine.SetItemAttrText (itemtype => itemtype,
1452 itemkey => itemkey,
1453 aname => G_WF_ITM_API_ERR_MSG,
1454 avalue => l_message);
1455
1456 resultout := 'COMPLETE:ERROR';
1457 END IF;
1458 -- Bug#9873432 - End
1459 RETURN;
1460 END IF;
1461 --Transfer Mode
1462 IF funcmode = 'TRANSFER' THEN
1463 resultout := wf_engine.eng_null;
1464 RETURN;
1465 END IF;
1466 -- CANCEL mode
1467 IF (funcmode = 'CANCEL') THEN
1468 resultout := 'COMPLETE:';
1469 RETURN;
1470 END IF;
1471 -- TIMEOUT mode
1472 IF (funcmode = 'TIMEOUT') THEN
1473 resultout := 'COMPLETE:';
1474 RETURN;
1475 END IF;
1476 EXCEPTION
1477 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1478 wf_core.context(G_PKG_NAME,
1479 l_api_name,
1480 itemtype,
1481 itemkey,
1482 TO_CHAR(actid),
1483 funcmode);
1484 RAISE;
1485 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1486 wf_core.context(G_PKG_NAME,
1487 l_api_name,
1488 itemtype,
1489 itemkey,
1490 TO_CHAR(actid),
1491 funcmode);
1492 RAISE;
1493 WHEN OTHERS THEN
1494 wf_core.context(G_PKG_NAME,
1495 l_api_name,
1496 itemtype,
1497 itemkey,
1498 TO_CHAR(actid),
1499 funcmode);
1500 RAISE;
1501 END update_approval_status;
1502
1503 -- Start of comments
1504 --
1505 -- Procedure Name : pop_approval_doc
1506 -- Description :
1507 -- This procedure is invoked dynamically by Workflow API's
1508 -- in order to populate the message body item attribute
1509 -- during notification submission.
1510 -- Business Rules :
1511 -- Parameters : document_id, display_type, document, document_type
1512 -- Version : 1.0
1513 --
1514 -- End of comments
1515 PROCEDURE pop_approval_doc (document_id IN VARCHAR2,
1516 display_type IN VARCHAR2,
1517 document IN OUT nocopy VARCHAR2,
1518 document_type IN OUT nocopy VARCHAR2) IS
1519
1520 l_message VARCHAR2(32000);
1521 l_contract_id NUMBER;
1522 BEGIN
1523
1524 l_contract_id := wf_engine.GetItemAttrText (
1525 itemtype => G_ITEM_TYPE_WF,
1526 itemkey => document_id,
1527 aname => G_WF_ITM_CONTRACT_ID);
1528
1529 document := compile_message(l_contract_id);
1530 document_type := display_type;
1531
1532 RETURN;
1533
1534 EXCEPTION
1535 WHEN OTHERS THEN NULL;
1536
1537 END pop_approval_doc;
1538
1539 -- bug#9873432 - Added
1540 -- Start of comments
1541 --
1542 -- Procedure Name : pop_act_failure_doc
1543 -- Description :
1544 -- This procedure is invoked dynamically by Workflow API's
1545 -- in order to populate the message body item attribute
1546 -- during notification at the time of activation erring out.
1547 -- Business Rules :
1548 -- Parameters : document_id, display_type, document, document_type
1549 -- Version : 1.0
1550 --
1551 -- End of comments
1552 PROCEDURE pop_act_failure_doc (document_id IN VARCHAR2,
1553 display_type IN VARCHAR2,
1554 document IN OUT nocopy VARCHAR2,
1555 document_type IN OUT nocopy VARCHAR2) IS
1556
1557 l_message VARCHAR2(32000);
1558 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
1559 BEGIN
1560
1561 l_contract_number := wf_engine.GetItemAttrText (
1562 itemtype => G_ITEM_TYPE_WF,
1563 itemkey => document_id,
1564 aname => G_WF_ITM_CONTRACT_NUMBER);
1565
1566 -- Set the message for notification
1567 l_message := '<p>'; -- begin message
1568 l_message := l_message || get_message('OKL_LLA_ACT_FAILURE_MSG',l_contract_number);
1569 l_message := l_message || '<br/><br/>';
1570 -- Send the API error stack as well here in the notification
1571 l_message := l_message|| wf_engine.GetItemAttrText (
1572 itemtype => G_ITEM_TYPE_WF,
1573 itemkey => document_id,
1574 aname => G_WF_ITM_API_ERR_MSG);
1575
1576 l_message := l_message || '</p>'; -- end of message
1577
1578 document := l_message;
1579 document_type := display_type;
1580
1581 RETURN;
1582
1583 EXCEPTION
1584 WHEN OTHERS THEN NULL;
1585 END pop_act_failure_doc;
1586
1587 PROCEDURE check_approval_process( itemtype IN VARCHAR2,
1588 itemkey IN VARCHAR2,
1589 actid IN NUMBER,
1590 funcmode IN VARCHAR2,
1591 resultout OUT NOCOPY VARCHAR2 )
1592 IS
1593 l_approval_option VARCHAR2(5);
1594 l_contract_id VARCHAR2(240);
1595 l_contract_number okc_k_headers_b.contract_number%TYPE;
1596
1597 l_api_name VARCHAR2(30) := 'check_approval_process';
1598
1599 BEGIN
1600
1601 IF (funcmode = 'RUN') THEN
1602 l_approval_option := fnd_profile.value('OKL_LEASE_CONTRACT_APPROVAL_PROCESS');
1603 IF l_approval_option = G_LEASE_CONTRACT_APPROVAL_AME THEN
1604
1605 l_contract_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1606 itemkey => itemkey,
1607 aname => G_WF_ITM_CONTRACT_ID);
1608
1609 l_contract_number := wf_engine.GetItemAttrText(itemtype => itemtype,
1610 itemkey => itemkey,
1611 aname => G_WF_ITM_CONTRACT_NUMBER);
1612
1613 -- smadhava - Bug#5235038 - Modified - Start
1614 -- Commenting code as DESCRIPITON attribute is not used by WF or AME. Besides
1615 -- compile_message returns a string which is of length > 4000 which cannot be stored
1616 -- in WF tables. Hence commenting code.
1617 /*
1618 wf_engine.SetItemAttrText (itemtype => itemtype,
1619 itemkey => itemkey,
1620 aname => G_WF_ITM_MESSAGE_DESCRIPTION,
1621 avalue => compile_message(l_contract_id));
1622 */
1623 -- Set the Message Document
1624 wf_engine.SetItemAttrText (itemtype => itemtype,
1625 itemkey => itemkey,
1626 aname => G_WF_ITM_MESSAGE_DOC,
1627 avalue => 'plsql:Okl_Kbk_Approvals_Wf.pop_approval_doc/'|| itemkey);
1628
1629 -- smadhava - Bug#5235038 - Modified - End
1630
1631 wf_engine.SetItemAttrText (itemtype => itemtype,
1632 itemkey => itemkey,
1633 aname => G_WF_ITM_APP_REQUEST_SUB,
1634 avalue => get_message('OKL_LLA_REQUEST_APPROVAL_SUB',l_contract_number));
1635
1636 wf_engine.SetItemAttrText (itemtype => itemtype,
1637 itemkey => itemkey,
1638 aname => G_WF_ITM_APP_REMINDER_SUB,
1639 avalue => get_message('OKL_LLA_REQ_APPR_SUB_REMINDER',l_contract_number));
1640
1641 wf_engine.SetItemAttrText (itemtype => itemtype,
1642 itemkey => itemkey,
1643 aname => G_WF_ITM_APP_APPROVED_SUB,
1644 avalue => get_message('OKL_LLA_REQUEST_APPROVED_SUB',l_contract_number));
1645
1646 wf_engine.SetItemAttrText (itemtype => itemtype,
1647 itemkey => itemkey,
1648 aname => G_WF_ITM_APP_REJECTED_SUB,
1649 avalue => get_message('OKL_LLA_REQUEST_REJECTED_SUB',l_contract_number));
1650
1651 wf_engine.SetItemAttrText (itemtype => itemtype,
1652 itemkey => itemkey,
1653 aname => G_WF_ITM_APP_REMINDER_HEAD,
1654 avalue => get_message('OKL_LLA_REQ_APPROVAL_REMINDER',l_contract_number));
1655
1656 wf_engine.SetItemAttrText (itemtype => itemtype,
1657 itemkey => itemkey,
1658 aname => G_WF_ITM_APP_APPROVED_HEAD,
1659 avalue => get_message('OKL_LLA_REQUEST_APPROVED_SUB',l_contract_number));
1660
1661 wf_engine.SetItemAttrText (itemtype => itemtype,
1662 itemkey => itemkey,
1663 aname => G_WF_ITM_APP_REJECTED_HEAD,
1664 avalue => get_message('OKL_LLA_REQUEST_REJECTED_SUB',l_contract_number));
1665
1666
1667 resultout := 'COMPLETE:AME';
1668 ELSIF l_approval_option = G_LEASE_CONTRACT_APPROVAL_WF THEN
1669 resultout := 'COMPLETE:WF';
1670 END IF;
1671
1672 --resultout := 'COMPLETE:';
1673 RETURN;
1674
1675 END IF;
1676 --
1677 -- CANCEL mode
1678 --
1679 IF (funcmode = 'CANCEL') THEN
1680 --
1681 resultout := 'COMPLETE:';
1682 RETURN;
1683 --
1684 END IF;
1685 --
1686 -- TIMEOUT mode
1687 --
1688 IF (funcmode = 'TIMEOUT') THEN
1689 --
1690 resultout := 'COMPLETE:';
1691 RETURN;
1692 --
1693 END IF;
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
1698 RAISE;
1699
1700 END check_approval_process;
1701
1702 PROCEDURE wf_approval_process( itemtype IN VARCHAR2,
1703 itemkey IN VARCHAR2,
1704 actid IN NUMBER,
1705 funcmode IN VARCHAR2,
1706 resultout OUT NOCOPY VARCHAR2 )IS
1707
1708
1709 CURSOR l_wf_item_key_csr IS
1710 SELECT okl_wf_item_s.NEXTVAL item_key
1711 FROM dual;
1712
1713 l_key VARCHAR2(240);
1714 l_process VARCHAR2(30) := G_APPROVAL_PROCESS_WF;
1715 l_item_type VARCHAR2(10) := G_ITEM_TYPE_WF;
1716
1717 l_contract_id VARCHAR2(240);
1718 l_contract_number okc_k_headers_v.contract_number%TYPE;
1719 l_requester VARCHAR2(240);
1720 l_requester_id VARCHAR2(240);
1721
1722 l_api_name VARCHAR2(30) := 'wf_Approval_Process';
1723
1724 BEGIN
1725
1726 OPEN l_wf_item_key_csr;
1727 FETCH l_wf_item_key_csr INTO l_key;
1728 CLOSE l_wf_item_key_csr;
1729
1730 IF (funcmode = 'RUN') THEN
1731
1732 wf_engine.CreateProcess(itemtype => l_item_type,
1733 itemkey => l_key,
1734 process => l_process);
1735
1736 wf_engine.SetItemParent(itemtype => l_item_type,
1737 itemkey => l_key,
1738 parent_itemtype => itemtype,
1739 parent_itemkey => itemkey,
1740 parent_context => G_WF_ITM_MASTER);
1741
1742 wf_engine.SetItemAttrText (
1743 itemtype => l_item_type,
1744 itemkey => l_key,
1745 aname => G_WF_ITM_PARENT_ITEM_KEY,
1746 avalue => itemkey);
1747
1748 wf_engine.SetItemAttrText (
1749 itemtype => l_item_type,
1750 itemkey => l_key,
1751 aname => G_WF_ITM_PARENT_ITEM_TYPE,
1752 avalue => itemtype);
1753
1754 -- Re populate Item Attributes for the Detail Process
1755
1756 l_contract_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1757 itemkey => itemkey,
1758 aname => G_WF_ITM_CONTRACT_ID);
1759
1760 l_contract_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1761 itemkey => itemkey,
1762 aname => G_WF_ITM_CONTRACT_NUMBER);
1763
1764 l_requester := wf_engine.GetItemAttrText (itemtype => itemtype,
1765 itemkey => itemkey,
1766 aname => G_WF_ITM_REQUESTER);
1767
1768 l_requester_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1769 itemkey => itemkey,
1770 aname => G_WF_ITM_REQUESTER_ID);
1771
1772 wf_engine.SetItemAttrText (
1773 itemtype => l_item_type,
1774 itemkey => l_key,
1775 aname => G_WF_ITM_CONTRACT_ID,
1776 avalue => l_contract_id);
1777
1778 wf_engine.SetItemAttrText (
1779 itemtype => l_item_type,
1780 itemkey => l_key,
1781 aname => G_WF_ITM_CONTRACT_NUMBER,
1782 avalue => l_contract_number);
1783
1784 wf_engine.SetItemAttrText (
1785 itemtype => l_item_type,
1786 itemkey => l_key,
1787 aname => G_WF_ITM_REQUESTER,
1788 avalue => l_requester);
1789 wf_engine.SetItemAttrText (
1790 itemtype => l_item_type,
1791 itemkey => l_key,
1792 aname => G_WF_ITM_REQUESTER_ID,
1793 avalue => l_requester_id);
1794
1795 -- Set the Message Document
1796 wf_engine.SetItemAttrText (itemtype => l_item_type,
1797 itemkey => l_key,
1798 aname => G_WF_ITM_MESSAGE_DOC,
1799 avalue => 'plsql:Okl_Kbk_Approvals_Wf.pop_approval_doc/'||l_key);
1800
1801 -- Now, Start the Detail Process
1802 wf_engine.StartProcess(itemtype => l_item_type,
1803 itemkey => l_key);
1804
1805 resultout := 'COMPLETE:';
1806 RETURN;
1807
1808 END IF;
1809 --
1810 -- CANCEL mode
1811 --
1812 IF (funcmode = 'CANCEL') THEN
1813 --
1814 resultout := 'COMPLETE:';
1815 RETURN;
1816 --
1817 END IF;
1818 --
1819 -- TIMEOUT mode
1820 --
1821 IF (funcmode = 'TIMEOUT') THEN
1822 --
1823 resultout := 'COMPLETE:';
1824 RETURN;
1825 --
1826 END IF;
1827
1828 EXCEPTION
1829 WHEN OTHERS THEN
1830
1831 IF l_wf_item_key_csr%ISOPEN THEN
1832 CLOSE l_wf_item_key_csr;
1833 END IF;
1834
1835 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
1836 RAISE;
1837
1838 END wf_approval_process;
1839
1840
1841 END Okl_Kbk_Approvals_Wf;