[Home] [Help]
PACKAGE BODY: APPS.OKL_CREDIT_LINE_WF
Source
1 PACKAGE BODY OKL_CREDIT_LINE_WF AS
2 /* $Header: OKLRDWFB.pls 120.2 2006/09/22 09:15:55 varangan noship $ */
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 Credit Line Approval';
20 G_INVALID_APP VARCHAR2(200) := 'OKL_LLA_INVALID_APPLICATION';
21
22 G_MSG_TOKEN_CONTRACT_NUMBER CONSTANT VARCHAR2(30) := 'CONTRACT_NUMBER';
23 G_EVENT_APPROVE_WF CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.approve_credit_line';
24 G_EVENT_APPROVE_AME CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.approve_credit_line';
25 G_LEASE_CONTRACT_APPROVAL_WF CONSTANT VARCHAR2(2) := 'WF';
26 G_LEASE_CONTRACT_APPROVAL_AME CONSTANT VARCHAR2(3) := 'AME';
27 -- cklee : start: 5/18/2005
28 G_TRX_TYPE_CONTRACT_APPROVAL CONSTANT VARCHAR2(20) := 'CREDIT_LINE_APPROVAL';
29 G_TRX_TCN_TYPE CONSTANT VARCHAR2(3) := 'CLA';--10/03/2005 cklee. 'CPR';
30 -- cklee : end: 5/18/2005
31 G_TRX_TSU_CODE_SUBMITTED CONSTANT VARCHAR2(10) := 'SUBMITTED';
32 G_TRX_TSU_CODE_PROCESSED CONSTANT VARCHAR2(10) := 'PROCESSED';
33 G_SOURCE_TRX_TYPE_WF CONSTANT VARCHAR2(10) := 'WF';
34
35 G_KHR_STS_PENDING_APPROVAL CONSTANT VARCHAR2(20) := 'PENDING_APPROVAL';
36 G_KHR_STS_COMPLETE CONSTANT VARCHAR2(10) := 'COMPLETE';
37 G_KHR_STS_INCOMPLETE CONSTANT VARCHAR2(15) := 'INCOMPLETE';
38 G_KHR_STS_APPROVED CONSTANT VARCHAR2(15) := 'APPROVED';
39 G_KHR_STS_DECLINED CONSTANT VARCHAR2(15) := 'DECLINED';
40
41 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(20) := 'CONTRACT_ID';
42 G_WF_ITM_CONTRACT_NUMBER CONSTANT VARCHAR2(20) := 'CONTRACT_NUMBER';
43 G_WF_ITM_APPLICATION_ID CONSTANT VARCHAR2(20) := 'APPLICATION_ID';
44 G_WF_ITM_TRANSACTION_TYPE_ID CONSTANT VARCHAR2(20) := 'TRX_TYPE_ID';
45 G_WF_ITM_TRANSACTION_ID CONSTANT VARCHAR2(20) := 'TRANSACTION_ID';
46 G_WF_ITM_REQUESTER CONSTANT VARCHAR2(20) := 'REQUESTER';
47 G_WF_ITM_REQUESTER_ID CONSTANT VARCHAR2(20) := 'REQUESTER_ID';
48 G_WF_ITM_APPROVER CONSTANT VARCHAR2(20) := 'APPROVER';
49 G_WF_ITM_APPROVAL_REQ_MSG CONSTANT VARCHAR2(30) := 'APPROVAL_REQUEST_MESSAGE';
50 G_WF_ITM_RESULT CONSTANT VARCHAR2(10) := 'RESULT';
51 G_WF_ITM_PARENT_ITEM_KEY CONSTANT VARCHAR2(20) := 'PARENT_ITEM_KEY';
52 G_WF_ITM_PARENT_ITEM_TYPE CONSTANT VARCHAR2(20) := 'PARENT_ITEM_TYPE';
53 G_WF_ITM_APPROVED_YN CONSTANT VARCHAR2(15) := 'APPROVED_YN';
54 G_WF_ITM_MASTER CONSTANT VARCHAR2(10) := 'MASTER';
55 G_WF_ITM_MESSAGE_DESCRIPTION CONSTANT VARCHAR2(30) := 'MESSAGE_DESCRIPTION';
56 G_WF_ITM_MESSAGE_DOC CONSTANT VARCHAR2(15) := 'MESSAGE_DOC';
57 G_WF_ITM_MESSAGE_SUBJECT CONSTANT VARCHAR2(20) := 'MESSAGE_SUBJECT';
58 G_WF_ITM_APP_REQUEST_SUB CONSTANT VARCHAR2(30) := 'APP_REQUEST_SUB';
59 G_WF_ITM_APP_REMINDER_SUB CONSTANT VARCHAR2(30) := 'APP_REMINDER_SUB';
60 G_WF_ITM_APP_APPROVED_SUB CONSTANT VARCHAR2(30) := 'APP_APPROVED_SUB';
61 G_WF_ITM_APP_REJECTED_SUB CONSTANT VARCHAR2(30) := 'APP_REJECTED_SUB';
62 G_WF_ITM_APP_REMINDER_HEAD CONSTANT VARCHAR2(30) := 'APP_REMINDER_HEAD';
63 G_WF_ITM_APP_APPROVED_HEAD CONSTANT VARCHAR2(30) := 'APP_APPROVED_HEAD';
64 G_WF_ITM_APP_REJECTED_HEAD CONSTANT VARCHAR2(30) := 'APP_REJECTED_HEAD';
65
66 G_WF_ITM_RESULT_APPROVED CONSTANT VARCHAR2(15) := 'APPROVED';
67 G_WF_ITM_APPROVED_YN_YES CONSTANT VARCHAR2(1) := 'Y';
68 G_WF_ITM_APPROVED_YN_NO CONSTANT VARCHAR2(1) := 'N';
69
70 G_ITEM_TYPE_WF CONSTANT VARCHAR2(10) := 'OKLCLAPP';
71 G_APPROVAL_PROCESS_WF CONSTANT VARCHAR2(30) := 'CRTLINE_APPROVAL_WF';
72
73 G_DEFAULT_USER CONSTANT VARCHAR2(10) := 'SYSADMIN';
74 G_DEFAULT_USER_DESC CONSTANT VARCHAR2(30) := 'System Administrator';
75 G_WF_USER_ORIG_SYSTEM_HR CONSTANT VARCHAR2(5) := 'PER';
76
77
78 ---------------------------------------------------------------------------
79 -- PROCEDURE l_get_agent
80 ---------------------------------------------------------------------------
81 -- Start of comments
82 --
83 -- Procedure Name : l_get_agent
84 -- Description :
85 -- Business Rules :
86 -- Parameters : p_user_id, x_return_status, x_name, x_description
87 -- Version : 1.0
88 -- End of comments
89 ---------------------------------------------------------------------------
90 PROCEDURE l_get_agent(p_user_id IN NUMBER,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_name OUT NOCOPY VARCHAR2,
93 x_description OUT NOCOPY VARCHAR2) IS
94
95 CURSOR wf_users_csr(c_user_id NUMBER)
96 IS
97 SELECT NAME, DISPLAY_NAME
98 FROM WF_USERS
99 WHERE orig_system_id = c_user_id
100 AND ORIG_SYSTEM = G_WF_USER_ORIG_SYSTEM_HR;
101
102 CURSOR fnd_users_csr(c_user_id NUMBER)
103 IS
104 SELECT USER_NAME, DESCRIPTION
105 FROM FND_USER
106 WHERE user_id = c_user_id;
107 BEGIN
108 x_return_status := OKL_API.G_RET_STS_SUCCESS;
109 OPEN wf_users_csr(p_user_id);
110 FETCH wf_users_csr INTO x_name, x_description;
111 CLOSE wf_users_csr;
112 IF x_name IS NULL THEN
113 OPEN fnd_users_csr(p_user_id);
114 FETCH fnd_users_csr INTO x_name, x_description;
115 CLOSE fnd_users_csr;
116 IF x_name IS NULL THEN
117 x_name := G_DEFAULT_USER_DESC;
118 x_description := G_DEFAULT_USER_DESC;
119 END IF;
120 END IF;
121 EXCEPTION
122 WHEN OTHERS THEN
123 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
124 END l_get_agent;
125
126 ---------------------------------------------------------------------------
127 -- PROCEDURE l_change_k_status
128 ---------------------------------------------------------------------------
129 -- Start of comments
130 --
131 -- Procedure Name : l_change_k_status
132 -- Description :
133 -- Business Rules : This updates the credit line status.
134 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
135 -- x_msg_data, p_khr_status, p_chr_id.
136 -- Version : 1.0
137 -- End of comments
138 ---------------------------------------------------------------------------
139 PROCEDURE l_change_k_status(p_api_version IN NUMBER,
140 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2,
144 p_khr_status IN OKC_K_HEADERS_V.STS_CODE%TYPE,
145 p_chr_id IN NUMBER) IS
146
147 l_api_name CONSTANT VARCHAR2(30) := 'l_change_k_status';
148 lx_khr_status varchar2(30);
149
150 BEGIN
151 x_return_status := OKL_API.G_RET_STS_SUCCESS;
152 -- Call start_activity to create savepoint, check compatibility
153 -- and initialize message list
154 x_return_status := OKL_API.START_ACTIVITY (
155 l_api_name,
156 p_init_msg_list,
157 '_PVT',
158 x_return_status);
159
160 -- Check if activity started successfully
161 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
162 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
163 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
164 RAISE OKL_API.G_EXCEPTION_ERROR;
165 END IF;
166
167 --------------------------------------------------------------------------------
168 -- The status of the credit line will initiate to 'SUBMITTED' and the credit line
169 -- process API will do the validations before invoke:
170 -- OKL_CREDIT_LINE_WF.raise_approval_event().
171 -- cklee May-06-2005
172 --------------------------------------------------------------------------------
173 OKL_CREDIT_PUB.update_credit_line_status(
174 p_api_version => p_api_version,
175 p_init_msg_list => p_init_msg_list,
176 x_return_status => x_return_status,
177 x_msg_count => x_msg_count,
178 x_msg_data => x_msg_data,
179 x_status_code => lx_khr_status,
180 p_status_code => p_khr_status,
181 p_credit_line_id => p_chr_id);
182
183 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
184 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
185 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
186 RAISE OKL_API.G_EXCEPTION_ERROR;
187 END IF;
188
189 OKL_API.END_ACTIVITY (x_msg_count,
190 x_msg_data );
191 EXCEPTION
192 WHEN OKL_API.G_EXCEPTION_ERROR THEN
193 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
194 l_api_name,
195 G_PKG_NAME,
196 'OKL_API.G_RET_STS_ERROR',
197 x_msg_count,
198 x_msg_data,
199 '_PVT');
200 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
201 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
202 l_api_name,
203 G_PKG_NAME,
204 'OKL_API.G_RET_STS_UNEXP_ERROR',
205 x_msg_count,
206 x_msg_data,
207 '_PVT');
208 WHEN OTHERS THEN
209 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
210 l_api_name,
211 G_PKG_NAME,
212 'OTHERS',
213 x_msg_count,
214 x_msg_data,
215 '_PVT');
216 END l_change_k_status;
217
218 ---------------------------------------------------------------------------
219 -- FUNCTION get_message
220 ---------------------------------------------------------------------------
221 -- Start of comments
222 --
223 -- Function Name : get_message
224 -- Description :
225 -- Business Rules : Sets tha appropriate message for approval and for
226 -- approved or rejected credit line.
227 -- Parameters : p_msg_name, p_contract_number
228 -- Version : 1.0
229 -- End of comments
230 ---------------------------------------------------------------------------
231 FUNCTION get_message(p_msg_name IN VARCHAR2,
232 p_contract_number IN VARCHAR2)
233 RETURN VARCHAR2
234 IS
235 l_message VARCHAR2(100);
236 BEGIN
237 IF p_msg_name IS NOT NULL THEN
238 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME
239 ,NAME => p_msg_name);
240 Fnd_Message.SET_TOKEN(TOKEN => G_MSG_TOKEN_CONTRACT_NUMBER,
241 VALUE => p_contract_number);
242 l_message := fnd_message.get();
243 END IF;
244
245 RETURN l_message;
246 EXCEPTION
247 WHEN OTHERS THEN
248 RETURN NULL;
249 END get_message;
250
251 --------------------------------------------------------------------------------------------------
252 ----------------------------------Rasing Business Event ------------------------------------------
253 --------------------------------------------------------------------------------------------------
254 ---------------------------------------------------------------------------
255 -- PROCEDURE raise_approval_event
256 ---------------------------------------------------------------------------
257 -- Start of comments
258 --
259 -- Procedure Name : raise_approval_event
260 -- Description :
261 -- Business Rules : Raises the credit line approval event
262 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
263 -- x_msg_data, p_contract_id.
264 -- Version : 1.0
265 -- End of comments
266 ---------------------------------------------------------------------------
267 PROCEDURE raise_approval_event (p_api_version IN NUMBER,
268 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2,
272 p_contract_id IN OKC_K_HEADERS_B.ID%TYPE)
273 IS
274
275 -- Get Contract Details
276 CURSOR c_fetch_k_number(p_contract_id OKC_K_HEADERS_V.ID%TYPE)
277 IS
278 SELECT chrv.contract_number,
279 chrv.sts_code
280 FROM okc_k_headers_v chrv
281 WHERE chrv.id = p_contract_id;
282
283 -- Get the valid application id from FND
284 CURSOR c_get_app_id_csr
285 IS
286 SELECT APPLICATION_ID
287 FROM FND_APPLICATION
288 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
289
290 -- Get the Transaction Type Id from OAM
291 CURSOR c_get_trx_type_csr(c_trx_type VARCHAR2)
292 IS
293 SELECT DESCRIPTION transaction_type_id, -- for performance issue #5484903
294 FND_APPLICATION_ID fnd_application_id
295 FROM AME_TRANSACTION_TYPES_V
296 WHERE TRANSACTION_TYPE_ID=c_trx_type;
297
298 /* --commented for performance issue#5484903
299 SELECT transaction_type_id,
300 fnd_application_id
301 FROM AME_CALLING_APPS
302 WHERE application_name = c_trx_type; */
303
304 CURSOR l_wf_item_key_csr IS
305 SELECT okl_wf_item_s.NEXTVAL item_key
306 FROM dual;
307
308 CURSOR l_trx_try_csr IS
309 SELECT id
310 FROM okl_trx_types_b
311 WHERE trx_type_class = G_TRX_TYPE_CONTRACT_APPROVAL;
312
313
314 l_return_status VARCHAR2(3);
315 l_api_version NUMBER;
316 l_api_name CONSTANT VARCHAR2(30) := 'raise_approval_event';
317 l_msg_count NUMBER;
318 l_init_msg_list VARCHAR2(10);
319 l_msg_data VARCHAR2(2000);
320 l_parameter_list wf_parameter_list_t;
321 l_key VARCHAR2(240);
322 l_event_name VARCHAR2(240);
323 l_application_id FND_APPLICATION.APPLICATION_ID%TYPE;
324 l_trans_appl_id AME_CALLING_APPS.APPLICATION_ID%TYPE;
325 l_trans_type_id AME_CALLING_APPS.TRANSACTION_TYPE_ID%TYPE;
326 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
327 l_chrv_id OKC_K_HEADERS_V.ID%TYPE ;
328 l_sts_code OKC_K_HEADERS_V.STS_CODE%TYPE;
329 l_requester VARCHAR2(200);
330 l_name VARCHAR2(200);
331 l_requester_id VARCHAR2(200);
332 l_trxH_in_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
333 l_trxH_out_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
337
334 l_approval_option VARCHAR2(5);
335
336 BEGIN
338 l_return_status := OKL_API.G_RET_STS_SUCCESS;
339 l_api_version := 1.0;
340 l_init_msg_list := OKL_API.G_FALSE;
341 l_chrv_id := p_contract_id;
342 x_return_status := OKL_API.G_RET_STS_SUCCESS;
343
344 -- Call start_activity to create savepoint, check compatibility
345 -- and initialize message list
346 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
347 p_init_msg_list,
348 '_PVT',
349 x_return_status);
350 -- Check if activity started successfully
351 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
352 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
353 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
354 RAISE OKL_API.G_EXCEPTION_ERROR;
355 END IF;
356
357 -- Create Internal Transaction
358
359 -- Get the user id, Item key
360 l_requester_id := FND_GLOBAL.USER_ID;
361
362 l_get_agent(p_user_id => l_requester_id,
363 x_return_status => x_return_status,
364 x_name => l_requester,
365 x_description => l_name);
366
367 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
368 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
369 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
370 RAISE OKL_API.G_EXCEPTION_ERROR;
371 END IF;
372
373 FOR l_wf_item_key_rec IN l_wf_item_key_csr
374 LOOP
375 l_key := l_wf_item_key_rec.item_key;
376 END LOOP;
377
378 FOR l_trx_try_rec IN l_trx_try_csr
379 LOOP
380 l_trxH_in_rec.try_id := l_trx_try_rec.id;
381 END LOOP;
382
383 l_trxH_in_rec.tcn_type := G_TRX_TCN_TYPE;
384 l_trxH_in_rec.tsu_code := G_TRX_TSU_CODE_SUBMITTED;
385 l_trxH_in_rec.description := l_requester_id; -- requestor user_id
386 l_trxH_in_rec.date_transaction_occurred := SYSDATE; -- sysdate
387 l_trxH_in_rec.source_trx_id := l_key;
388 l_trxH_in_rec.source_trx_type := G_SOURCE_TRX_TYPE_WF;
389
390 -- Create Transaction Header
391 Okl_Trx_Contracts_Pub.create_trx_contracts(p_api_version => l_api_version
392 ,p_init_msg_list => l_init_msg_list
393 ,x_return_status => l_return_status
394 ,x_msg_count => l_msg_count
395 ,x_msg_data => l_msg_data
396 ,p_tcnv_rec => l_trxH_in_rec
397 ,x_tcnv_rec => l_trxH_out_rec);
398
399 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
400 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
401 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
402 RAISE Okl_Api.G_EXCEPTION_ERROR;
403 END IF;
404 -- end, mvasudev
405
406 l_approval_option := fnd_profile.value('OKL_CREDIT_LINE_APPROVAL_PROCESS');
407 IF l_approval_option = G_LEASE_CONTRACT_APPROVAL_AME THEN
408
409 -- Get the Contract Number
410 OPEN c_fetch_k_number(l_chrv_id);
411 FETCH c_fetch_k_number INTO l_contract_num,l_sts_code;
412 IF c_fetch_k_number%NOTFOUND THEN
413 OKL_API.set_message(p_app_name => G_APP_NAME,
414 p_msg_name => G_NO_MATCHING_RECORD,
415 p_token1 => G_COL_NAME_TOKEN,
416 p_token1_value => 'OKC_K_HEADERS_V.ID');
417 RAISE OKL_API.G_EXCEPTION_ERROR;
418 END IF;
419 CLOSE c_fetch_k_number;
420
421 -- Get the Application ID
422 OPEN c_get_app_id_csr;
423 FETCH c_get_app_id_csr INTO l_application_id;
424 IF c_get_app_id_csr%NOTFOUND THEN
425 OKL_API.set_message(p_app_name => G_APP_NAME,
426 p_msg_name => G_NO_MATCHING_RECORD,
427 p_token1 => G_COL_NAME_TOKEN,
428 p_token1_value => 'Application id');
429 RAISE OKL_API.G_EXCEPTION_ERROR;
430 END IF;
431 CLOSE c_get_app_id_csr;
432
433 -- Get the Transaction Type ID
434 OPEN c_get_trx_type_csr(G_TRANS_APP_NAME);
435 FETCH c_get_trx_type_csr INTO l_trans_type_id,
436 l_trans_appl_id;
437 IF c_get_trx_type_csr%NOTFOUND THEN
438 OKL_API.set_message(p_app_name => G_APP_NAME,
439 p_msg_name => G_NO_MATCHING_RECORD,
440 p_token1 => G_COL_NAME_TOKEN,
441 p_token1_value => 'AME Transcation TYPE id, Application id');
442 RAISE OKL_API.G_EXCEPTION_ERROR;
443 END IF;
444 CLOSE c_get_trx_type_csr;
445
446
447 IF l_application_id = l_trans_appl_id THEN
448 l_event_name := G_EVENT_APPROVE_AME;
449
450 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_contract_id,l_parameter_list);
451 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_NUMBER,l_contract_num,l_parameter_list);
452 wf_event.AddParameterToList(G_WF_ITM_APPLICATION_ID,l_application_id,l_parameter_list);
456 wf_event.AddParameterToList(G_WF_ITM_REQUESTER_ID,l_requester_id,l_parameter_list);
453 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_TYPE_ID,l_trans_type_id,l_parameter_list);
454 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_ID,l_trxH_out_rec.trx_number,l_parameter_list);
455 wf_event.AddParameterToList(G_WF_ITM_REQUESTER,l_requester,l_parameter_list);
457 --added by akrangan
458 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
459
460 ELSE
461 OKL_API.set_message(p_app_name => G_APP_NAME,
462 p_msg_name => G_INVALID_APP);
463 RAISE OKL_API.G_EXCEPTION_ERROR;
464 END IF; -- l_application_id
465
466 ELSIF l_approval_option = G_LEASE_CONTRACT_APPROVAL_WF THEN
467 l_event_name := G_EVENT_APPROVE_WF;
468
469 FOR c_fetch_k_number_rec IN c_fetch_k_number(l_chrv_id)
470 LOOP
471 l_contract_num := c_fetch_k_number_rec.contract_number;
472 END LOOP;
473
474 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_contract_id,l_parameter_list);
475 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_NUMBER,l_contract_num,l_parameter_list);
476 wf_event.AddParameterToList(G_WF_ITM_REQUESTER,l_requester,l_parameter_list);
477 wf_event.AddParameterToList(G_WF_ITM_REQUESTER_ID,l_requester_id,l_parameter_list);
478 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_ID,l_trxH_out_rec.trx_number,l_parameter_list);
479 --added by akrangan
480 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
481 ELSE
482 RAISE OKL_API.G_EXCEPTION_ERROR;
483 END IF; -- l_approval_option
484
485 -- We need to status to Approved Pending since We are sending for approval
486 l_change_k_status(p_api_version => p_api_version,
487 p_init_msg_list => p_init_msg_list,
488 x_return_status => x_return_status,
489 x_msg_count => x_msg_count,
490 x_msg_data => x_msg_data,
491 p_khr_status => G_KHR_STS_PENDING_APPROVAL,
492 p_chr_id => l_chrv_id);
493
494 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
495 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
496 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
497 RAISE OKL_API.G_EXCEPTION_ERROR;
498 END IF;
499
500 -- Raise Event
501 wf_event.RAISE(p_event_name => l_event_name,
502 p_event_key => l_key,
503 p_parameters => l_parameter_list);
504 l_parameter_list.DELETE;
505
506 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
507 x_msg_data => x_msg_data);
508 EXCEPTION
509 WHEN OKL_API.G_EXCEPTION_ERROR THEN
510 x_return_status := OKL_API.G_RET_STS_ERROR;
511 IF c_fetch_k_number%ISOPEN THEN
512 CLOSE c_fetch_k_number;
513 END IF;
514 IF c_get_app_id_csr%ISOPEN THEN
515 CLOSE c_get_app_id_csr;
516 END IF;
517 IF c_get_trx_type_csr%ISOPEN THEN
518 CLOSE c_get_trx_type_csr;
519 END IF;
520 IF l_wf_item_key_csr%ISOPEN THEN
521 CLOSE l_wf_item_key_csr;
522 END IF;
523 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
524 p_api_name => l_api_name,
525 p_pkg_name => G_PKG_NAME,
526 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
527 x_msg_count => x_msg_count,
528 x_msg_data => x_msg_data,
529 p_api_type => G_API_TYPE);
530 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
531 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
532 IF c_fetch_k_number%ISOPEN THEN
533 CLOSE c_fetch_k_number;
534 END IF;
535 IF c_get_app_id_csr%ISOPEN THEN
536 CLOSE c_get_app_id_csr;
537 END IF;
538 IF c_get_trx_type_csr%ISOPEN THEN
539 CLOSE c_get_trx_type_csr;
540 END IF;
541 IF l_wf_item_key_csr%ISOPEN THEN
542 CLOSE l_wf_item_key_csr;
543 END IF;
544 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
545 p_api_name => l_api_name,
546 p_pkg_name => G_PKG_NAME,
547 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
548 x_msg_count => x_msg_count,
549 x_msg_data => x_msg_data,
550 p_api_type => G_API_TYPE);
551 WHEN OTHERS THEN
552 IF c_fetch_k_number%ISOPEN THEN
553 CLOSE c_fetch_k_number;
554 END IF;
555 IF c_get_app_id_csr%ISOPEN THEN
556 CLOSE c_get_app_id_csr;
557 END IF;
558 IF c_get_trx_type_csr%ISOPEN THEN
559 CLOSE c_get_trx_type_csr;
560 END IF;
561 IF l_wf_item_key_csr%ISOPEN THEN
562 CLOSE l_wf_item_key_csr;
563 END IF;
564 -- store SQL error message on message stack
565 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
566 p_api_name => l_api_name,
567 p_pkg_name => G_PKG_NAME,
568 p_exc_name => 'OTHERS',
569 x_msg_count => x_msg_count,
570 x_msg_data => x_msg_data,
571 p_api_type => G_API_TYPE);
575 -- FUNCTION compile_message
572 END raise_approval_event;
573
574 ---------------------------------------------------------------------------
576 ---------------------------------------------------------------------------
577 -- Start of comments
578 --
579 -- Function Name : compile_message
580 -- Description :
581 -- Business Rules : Creates the message body of the notifications
582 -- Parameters : p_contract_id
583 -- Version : 1.0
584 -- End of comments
585 ---------------------------------------------------------------------------
586 FUNCTION compile_message(p_contract_id IN NUMBER)
587 RETURN VARCHAR2
588 IS
589
590 CURSOR l_okl_crtline_csr(p_contract_id OKC_K_HEADERS_V.ID%TYPE)
591 IS
592 SELECT ID,
593 CONTRACT_NUMBER,
594 DESCRIPTION,
595 STS_MEANING,
596 CURRENCY_CODE,
597 CUSTOMER_NAME,
598 CUST_ACCT_NUMBER,
599 TOTAL_LIMIT,
600 START_DATE,
601 END_DATE
602 FROM okl_creditlines_uv
603 WHERE id = p_contract_id;
604
605 CURSOR l_okl_crtline_contents_csr(p_contract_id OKC_K_HEADERS_V.ID%TYPE)
606 IS
607 SELECT KHR_ID,
608 TODO_ITEM_CODE,
609 TODO_ITEM_MEANING,
610 MANDATORY_FLAG_MEANING,
611 MANDATORY_FLAG,
612 CHECK_OFF_RESULTS,
613 FUNC_VAL_RSTS_MEANING,
614 FUNCTION_ID,
615 FUNCTION_NAME,
616 CHECKIST_RESULTS
617 FROM okl_credit_checklists_uv
618 WHERE khr_id = p_contract_id;
619
620 l_msg_count NUMBER;
621 l_msg_data VARCHAR2(2000);
622 l_api_version NUMBER ;
623 l_init_msg_list VARCHAR2(3) ;
624 l_return_status VARCHAR2(3) ;
625 l_true_tax VARCHAR2(200);
626 l_cap_amt NUMBER;
627 l_res_value NUMBER;
628 l_message VARCHAR2(12000);
629 l_flag VARCHAR2(10);
630
631 BEGIN
632
633 -- l_flag := 'Passed'; -- cklee 06/01/2005
634 l_api_version := 1;
635 l_init_msg_list := OKC_API.G_TRUE;
636 l_return_status := OKL_API.G_RET_STS_SUCCESS;
637
638
639 FOR l_okl_crtline_rec IN l_okl_crtline_csr(p_contract_id)
640 LOOP
641 -- Start
642 l_message := l_message || '<TABLE width="100%" border="0" cellspacing="0" cellpadding="0">';
643
644 -- Empty Row
645 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
646
647 l_message := l_message || '<tr><td colspan=6>'
648 || '<table width="100%" border="0" cellspacing="0" cellpadding="0">';
649
650 -- Credit Line, Currency
651 l_message := l_message || '<tr><td width="18%" align="right">'
652 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
653 p_attribute_code => 'OKL_CREDIT')
654 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
655 || '<td width="36%"><b>'
656 || l_okl_crtline_rec.contract_number
657 || '</b></td>'
658 || '<td width="13%" align="right">'
659 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
660 p_attribute_code => 'OKL_CURRENCY')
661 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
662 || '<td width="33%"><b>'
663 || l_okl_crtline_rec.currency_code
664 || '</b></td>'
665 || '</tr>';
666
667 -- Description, Effective From
668 l_message := l_message || '<tr><td width="18%" align="right">'
669 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
670 p_attribute_code => 'OKL_DESCRIPTION')
671 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
672 || '<td width="36%"><b>'
673 || l_okl_crtline_rec.description
674 || '</b></td>'
675 || '<td width="13%" align="right">'
676 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
677 p_attribute_code => 'OKL_EFFECTIVE_FROM')
678 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
679 || '<td width="33%"><b>'
680 || to_date(l_okl_crtline_rec.start_date,'dd-mm-yyyy')
681 || '</b></td>'
682 || '</tr>';
683
684 -- Customer, Effective To
685 l_message := l_message || '<tr><td width="18%" align="right">'
686 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
687 p_attribute_code => 'OKL_CUSTOMER')
688 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
692 || '<td width="13%" align="right">'
689 || '<td width="36%"><b>'
690 || l_okl_crtline_rec.customer_name
691 || '</b></td>'
693 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
694 p_attribute_code => 'OKL_EFFECTIVE_TO')
695 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
696 || '<td width="33%"><b>'
697 || to_date(l_okl_crtline_rec.end_date,'dd-mm-yyyy')
698 || '</b></td>'
699 || '</tr>';
700
701 -- Customer Account, Total Credit Limit
702 l_message := l_message || '<tr><td width="18%" align="right">'
703 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
704 p_attribute_code => 'OKL_KDTLS_CUSTOMER_ACCOUNT_N')
705 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
706 || '<td width="36%"><b>'
707 || l_okl_crtline_rec.cust_acct_number
708 || '</b></td>'
709 || '<td width="13%" align="right">'
710 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
711 p_attribute_code => 'OKL_TOTAL_CREDIT_LIMIT')
712 || '</td><td width="1%">' || G_CHAR_AMPERSAND || 'nbsp;</td>'
713 || '<td width="33%"><b>'
714 || TO_CHAR(l_okl_crtline_rec.total_limit,'999,999,999,999,999,999.00')
715 || '</b></td>'
716 || '</tr>';
717
718 -- Empty Row
719 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
720
721 FOR each_row IN l_okl_crtline_contents_csr(p_contract_id) LOOP
722 l_flag := 'Passed'; -- cklee 06/01/2005
723 IF(each_row.mandatory_flag = 'Y' and each_row.CHECKIST_RESULTS <> 'Passed') THEN --cklee 06/01/2005
724 l_flag := 'Failed';
725 EXIT;
726 END IF;
727 END LOOP;
728
729 IF l_flag = 'Passed' THEN
730 l_flag := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
731 p_attribute_code => 'OKL_PASSED');
732 ELSIF l_flag = 'Failed' THEN
733 l_flag := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
734 p_attribute_code => 'OKL_FAILED');
735 END IF;
736
737 -- Checklist Validation Result
738 l_message := l_message || '<tr><td width="18%" align="right">'
739 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CREDIT',
740 p_attribute_code => 'OKL_CHKLIST_VAL_RESULT')
741 || '</td><td width="1%">=</td>'
742 || '<td width="36%" colspan="4"><b>'
743 || l_flag
744 || '</b></td>';
745 -- Empty Row
746 l_message := l_message || '<tr><td colspan=6>' || G_CHAR_AMPERSAND || 'nbsp;</td></tr>';
747
748 -- End
749 l_message := l_message || '
750 </TABLE>';
751
752 END LOOP; -- l_okl_crtline_rec
753
754 l_message := l_message || '<table class="x1h" cellpadding="1" cellspacing="0" border="1" width="100%">';
755
756 -- Headers for the creditline details table.
757 -- Checklist Item
758 l_message := l_message || '<tr> <th scope="col" class="x1r"> <span title="'
759 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
760 p_attribute_code => 'OKL_ITEM')
761 || '" class="x24">'
762 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
763 p_attribute_code => 'OKL_ITEM')
764 || '</span></th>';
765
766 -- Description
767 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
768 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
769 p_attribute_code => 'OKL_DESCRIPTION')
770 || '" class="x24">'
771 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
772 p_attribute_code => 'OKL_DESCRIPTION')
773 || '</span></th>';
774
775 -- Function
776 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
777 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
778 p_attribute_code => 'OKL_FUNCTION')
779 || '" class="x24">'
780 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
781 p_attribute_code => 'OKL_FUNCTION')
782 || '</span></th>';
783
787 p_attribute_code => 'OKL_MANDATORY')
784 -- Mandatory
785 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
786 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
788 || '" class="x24">'
789 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
790 p_attribute_code => 'OKL_MANDATORY')
791 || '</span></th>';
792
793 -- Results
794 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
795 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
796 p_attribute_code => 'OKL_RESULTS')
797 || '" class="x24">'
798 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_CHECKLIST',
799 p_attribute_code => 'OKL_RESULTS')
800 || '</span></th></tr>';
801
802
803 FOR l_okl_crtline_contents_rec IN l_okl_crtline_contents_csr(p_contract_id)
804 LOOP
805 IF (l_okl_crtline_contents_rec.todo_item_code is not null)THEN
806 l_message := l_message || '<tr><td class="x1l x4x">'
807 || l_okl_crtline_contents_rec.todo_item_code
808 || '</td>';
809 ELSE
810 l_message := l_message || '<tr><td class="x1l x4x"><br></td>';
811 END IF;
812 IF(l_okl_crtline_contents_rec.todo_item_meaning is not null)THEN
813 l_message := l_message || '<td class="x1l x4x">'
814 || l_okl_crtline_contents_rec.todo_item_meaning
815 || '</td>';
816 ELSE
817 l_message := l_message || '<td class="x1l x4x"><br></td>';
818 END IF;
819 IF(l_okl_crtline_contents_rec.function_name is not null) THEN
820 l_message := l_message || '<td class="x1l x4x">'
821 || l_okl_crtline_contents_rec.function_name
822 || '</td>';
823 ELSE
824 l_message := l_message || '<td class="x1l x4x"><br></td>';
825 END IF;
826 IF( l_okl_crtline_contents_rec.mandatory_flag_meaning is not null) THEN
827 l_message := l_message || '<td class="x1l x4x">'
828 || l_okl_crtline_contents_rec.mandatory_flag_meaning
829 || '</td>';
830 ELSE
831 l_message := l_message || '<td class="x1l x4x"><br></td>';
832 END IF;
833 IF(l_okl_crtline_contents_rec.function_id is not null) THEN
834 l_message := l_message || '<td class="x1l x4x">'
835 || l_okl_crtline_contents_rec.func_val_rsts_meaning
836 || '</td></tr>';
837 ELSE
838 l_message := l_message || '<td class="x1l x4x">'
839 || l_okl_crtline_contents_rec.check_off_results
840 || '</td></tr>';
841 END IF;
842 END LOOP;
843 l_message := l_message || '</table>';
844
845 RETURN l_message;
846
847 EXCEPTION
848 WHEN OTHERS THEN
849 RETURN NULL;
850 END compile_message;
851
852 /*
853 -- This API is for Lease Contract Approval via WF
854 */
855 ---------------------------------------------------------------------------
856 -- PROCEDURE get_credit_line_approver
857 ---------------------------------------------------------------------------
858 -- Start of comments
859 --
860 -- Procedure Name : get_credit_line_approver
861 -- Description :
862 -- Business Rules : returns whether the approver is found or not.
863 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
864 -- Version : 1.0
865 -- End of comments
866 ---------------------------------------------------------------------------
867 PROCEDURE get_credit_line_approver(itemtype IN VARCHAR2,
868 itemkey IN VARCHAR2,
869 actid IN NUMBER,
870 funcmode IN VARCHAR2,
871 resultout OUT NOCOPY VARCHAR2)
872 IS
873 CURSOR l_fnd_users_csr(p_user_id NUMBER)
874 IS
875 SELECT USER_NAME
876 FROM FND_USER
877 WHERE user_id = p_user_id;
878
879 l_api_name CONSTANT VARCHAR2(200) := 'get_credit_line_approver';
880 l_user_id VARCHAR2(200);
881 l_contract_number OKC_K_HEADERS_V.contract_number%TYPE;
882 l_return_status VARCHAR2(1);
883
884 BEGIN
885 l_return_status := OKL_API.G_RET_STS_SUCCESS;
886
887 -- "RUN"
888 IF (funcmode = 'RUN') THEN
889 --l_user_id := fnd_profile.value('OKL_LEASE_CONTRACT_APPROVER');
890 l_user_id := wf_engine.GetItemAttrText (itemtype => itemtype,
891 itemkey => itemkey,
892 aname => G_WF_ITM_REQUESTER_ID);
893
894 l_contract_number := wf_engine.GetItemAttrText (itemtype => itemtype,
895 itemkey => itemkey,
899 IF l_user_id IS NOT NULL THEN
896 aname => G_WF_ITM_CONTRACT_NUMBER);
897
898 resultout := 'COMPLETE:NOT_FOUND'; -- default
900 FOR l_fnd_users_rec IN l_fnd_users_csr(l_user_id)
901 LOOP
902 wf_engine.SetItemAttrText (itemtype => itemtype,
903 itemkey => itemkey,
904 aname => G_WF_ITM_APPROVER,
905 avalue => l_fnd_users_rec.user_name);
906
907
908 wf_engine.SetItemAttrText (itemtype => itemtype,
909 itemkey => itemkey,
910 aname => G_WF_ITM_MESSAGE_SUBJECT,
911 avalue => get_message('OKL_CRTLINE_APPROVAL_SUMMARY',l_contract_number));
912
913 resultout := 'COMPLETE:FOUND';
914 END LOOP;
915 END IF; -- l_user_id
916
917 -- CANCEL mode
918 ELSIF (funcmode = 'CANCEL') THEN
919 resultout := 'COMPLETE:';
920 RETURN;
921 -- TIMEOUT mode
922 ELSIF (funcmode = 'TIMEOUT') THEN
923 resultout := 'COMPLETE:';
924 RETURN;
925 END IF; -- funcmode
926 EXCEPTION
927 WHEN OTHERS THEN
928 wf_core.context(G_PKG_NAME,
929 l_api_name,
930 itemtype,
931 itemkey,
932 TO_CHAR(actid),
933 funcmode);
934 RAISE;
935
936 END get_credit_line_approver;
937
938 --------------------------------------------------------------------------------------------------
939 --------------------------------- Set Approval Status --------------------------------------------
940 --------------------------------------------------------------------------------------------------
941 ---------------------------------------------------------------------------
942 -- PROCEDURE Set_Parent_Attributes
943 ---------------------------------------------------------------------------
944 -- Start of comments
945 --
946 -- Procedure Name : Set_Parent_Attributes
947 -- Description :
948 -- Business Rules : sets the parent attributes.
949 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
950 -- Version : 1.0
951 -- End of comments
952 ---------------------------------------------------------------------------
953 PROCEDURE Set_Parent_Attributes(itemtype IN VARCHAR2,
954 itemkey IN VARCHAR2,
955 actid IN NUMBER,
956 funcmode IN VARCHAR2,
957 resultout OUT NOCOPY VARCHAR2) IS
958
959 l_approved_yn VARCHAR2(30);
960 l_parent_key VARCHAR2(240);
961 l_parent_type VARCHAR2(240);
962 l_result VARCHAR2(30);
963 l_api_name CONSTANT VARCHAR2(30) := 'Set_Parent_Attributes';
964 l_contract_number okc_k_headers_v.contract_number%TYPE;
965
966 BEGIN
967 SAVEPOINT set_atts;
968 IF (funcmode = 'RUN') THEN
969 -- Get current approval status
970 l_result := wf_engine.GetItemAttrText (itemtype => itemtype,
971 itemkey => itemkey,
972 aname => G_WF_ITM_RESULT);
973
974 l_parent_key := wf_engine.GetItemAttrText (itemtype => itemtype,
975 itemkey => itemkey,
976 aname => G_WF_ITM_PARENT_ITEM_KEY);
977
978 l_parent_type := wf_engine.GetItemAttrText (itemtype => itemtype,
979 itemkey => itemkey,
980 aname => G_WF_ITM_PARENT_ITEM_TYPE);
981
982 l_contract_number := wf_engine.GetItemAttrText (itemtype => itemtype,
983 itemkey => itemkey,
984 aname => G_WF_ITM_CONTRACT_NUMBER);
985
986 IF l_result = G_WF_ITM_RESULT_APPROVED THEN
987 l_approved_yn := G_WF_ITM_APPROVED_YN_YES;
988 wf_engine.SetItemAttrText (itemtype => itemtype,
989 itemkey => itemkey,
990 aname => G_WF_ITM_MESSAGE_SUBJECT,
991 avalue => get_message('OKL_LLA_REQUEST_APPROVED_SUB',l_contract_number));
992 ELSE
993 l_approved_yn := G_WF_ITM_APPROVED_YN_NO;
994 wf_engine.SetItemAttrText (itemtype => itemtype,
995 itemkey => itemkey,
996 aname => G_WF_ITM_MESSAGE_SUBJECT,
997 avalue => get_message('OKL_LLA_REQUEST_REJECTED_SUB',l_contract_number));
998 END IF;
999
1000 wf_engine.SetItemAttrText(itemtype => l_parent_type,
1001 itemkey => l_parent_key,
1002 aname => G_WF_ITM_APPROVED_YN,
1003 avalue => l_approved_yn);
1004 resultout := 'COMPLETE:';
1005 RETURN;
1006 END IF;
1007 -- CANCEL mode
1008 IF (funcmode = 'CANCEL') THEN
1009 resultout := 'COMPLETE:';
1010 RETURN;
1011 END IF;
1015 RETURN;
1012 -- TIMEOUT mode
1013 IF (funcmode = 'TIMEOUT') THEN
1014 resultout := 'COMPLETE:';
1016 END IF;
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 wf_core.context(G_PKG_NAME,
1020 l_api_name,
1021 itemtype,
1022 itemkey,
1023 TO_CHAR(actid),
1024 funcmode);
1025 RAISE;
1026 END Set_Parent_Attributes;
1027 --------------------------------------------------------------------------------------------------
1028 ----------------------------------Main Approval Process ------------------------------------------
1029 --------------------------------------------------------------------------------------------------
1030 ---------------------------------------------------------------------------
1031 -- PROCEDURE update_approval_status
1032 ---------------------------------------------------------------------------
1033 -- Start of comments
1034 --
1035 -- Procedure Name : update_approval_status
1036 -- Description :
1037 -- Business Rules : Updates the credit line status from pending approval
1038 -- to approved or declined.
1039 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
1040 -- Version : 1.0
1041 -- End of comments
1042 ---------------------------------------------------------------------------
1043 PROCEDURE update_approval_status(itemtype IN VARCHAR2,
1044 itemkey IN VARCHAR2,
1045 actid IN NUMBER,
1046 funcmode IN VARCHAR2,
1047 resultout OUT NOCOPY VARCHAR2)
1048 IS
1049 CURSOR l_okl_trx_contracts_csr(p_trx_number IN VARCHAR2)
1050 IS
1051 SELECT id
1052 FROM okl_trx_contracts
1053 WHERE trx_number = p_trx_number;
1054
1055 l_return_status VARCHAR2(3) ;
1056 l_api_version NUMBER ;
1057 l_msg_count NUMBER;
1058 l_init_msg_list VARCHAR2(10);
1059 l_msg_data VARCHAR2(2000);
1060 l_api_name CONSTANT VARCHAR2(30) := 'update_approval_status';
1061 l_chrv_id OKC_K_HEADERS_V.ID%TYPE;
1062 l_approved_yn VARCHAR2(30);
1063 l_trx_number VARCHAR2(100);
1064 lv_approval_status_ame VARCHAR2(10);
1065 l_trxH_in_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
1066 l_trxH_out_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
1067
1068 BEGIN
1069
1070 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1071 l_api_version := 1.0;
1072 l_init_msg_list := OKL_API.G_FALSE;
1073
1074 -- We getting the contract_Id from WF
1075 l_chrv_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1076 itemkey => itemkey,
1077 aname => G_WF_ITM_CONTRACT_ID);
1078 --Run Mode
1079 IF funcmode = 'RUN' THEN
1080 l_approved_yn := wf_engine.GetItemAttrText (itemtype => itemtype,
1081 itemkey => itemkey,
1082 aname => G_WF_ITM_APPROVED_YN);
1083 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
1084 itemkey => itemkey,
1085 aname => 'APPROVED_YN');
1086
1087 IF (l_approved_yn = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = G_WF_ITM_APPROVED_YN_YES)THEN
1088 l_change_k_status(p_api_version => l_api_version,
1089 p_init_msg_list => l_init_msg_list,
1090 x_return_status => l_return_status,
1091 x_msg_count => l_msg_count,
1092 x_msg_data => l_msg_data,
1093 p_khr_status => G_KHR_STS_APPROVED,
1094 p_chr_id => l_chrv_id);
1095 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1096 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1097 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1098 RAISE OKL_API.G_EXCEPTION_ERROR;
1099 END IF;
1100
1101 ELSE
1102 l_change_k_status(p_api_version => l_api_version,
1103 p_init_msg_list => l_init_msg_list,
1104 x_return_status => l_return_status,
1105 x_msg_count => l_msg_count,
1106 x_msg_data => l_msg_data,
1107 --------------------------------------------------------------------------------
1108 -- cklee's Note: no rejected status available. we use DECLINED instead
1109 --------------------------------------------------------------------------------
1110 p_khr_status => G_KHR_STS_DECLINED,
1111 p_chr_id => l_chrv_id);
1112 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1113 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1114 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1115 RAISE OKL_API.G_EXCEPTION_ERROR;
1116 END IF;
1117 END IF;
1118
1119 -- trx's trx_number IS wf's trx_id
1120 l_trx_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1121 itemkey => itemkey,
1125 LOOP
1122 aname => G_WF_ITM_TRANSACTION_ID);
1123
1124 FOR l_okl_trx_contracts_rec IN l_okl_trx_contracts_csr(l_trx_number)
1126 l_trxH_in_rec.id := l_okl_trx_contracts_rec.id;
1127 END LOOP;
1128
1129 l_trxH_in_rec.tsu_code := G_TRX_TSU_CODE_PROCESSED;
1130
1131 Okl_Trx_Contracts_Pub.update_trx_contracts(
1132 p_api_version => l_api_version
1133 ,p_init_msg_list => l_init_msg_list
1134 ,x_return_status => l_return_status
1135 ,x_msg_count => l_msg_count
1136 ,x_msg_data => l_msg_data
1137 ,p_tcnv_rec => l_trxH_in_rec
1138 ,x_tcnv_rec => l_trxH_out_rec);
1139
1140 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1141 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1142 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1143 RAISE Okl_Api.G_EXCEPTION_ERROR;
1144 END IF;
1145
1146 resultout := 'COMPLETE:';
1147 RETURN;
1148 END IF;
1149 --Transfer Mode
1150 IF funcmode = 'TRANSFER' THEN
1151 resultout := wf_engine.eng_null;
1152 RETURN;
1153 END IF;
1154 -- CANCEL mode
1155 IF (funcmode = 'CANCEL') THEN
1156 resultout := 'COMPLETE:';
1157 RETURN;
1158 END IF;
1159 -- TIMEOUT mode
1160 IF (funcmode = 'TIMEOUT') THEN
1161 resultout := 'COMPLETE:';
1162 RETURN;
1163 END IF;
1164 EXCEPTION
1165 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1166 wf_core.context(G_PKG_NAME,
1167 l_api_name,
1168 itemtype,
1169 itemkey,
1170 TO_CHAR(actid),
1171 funcmode);
1172 RAISE;
1173 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1174 wf_core.context(G_PKG_NAME,
1175 l_api_name,
1176 itemtype,
1177 itemkey,
1178 TO_CHAR(actid),
1179 funcmode);
1180 RAISE;
1181 WHEN OTHERS THEN
1182 wf_core.context(G_PKG_NAME,
1183 l_api_name,
1184 itemtype,
1185 itemkey,
1186 TO_CHAR(actid),
1187 funcmode);
1188 RAISE;
1189 END update_approval_status;
1190
1191 ---------------------------------------------------------------------------
1192 -- PROCEDURE pop_approval_doc
1193 ---------------------------------------------------------------------------
1194 -- Start of comments
1195 --
1196 -- Procedure Name : pop_approval_doc
1197 -- Description :
1198 -- Business Rules : This procedure is invoked dynamically by Workflow API's
1199 -- in order to populate the message body item attribute
1200 -- during notification submission.
1201 -- Parameters : document_id, display_type, document, document_type.
1202 -- Version : 1.0
1203 -- End of comments
1204 ---------------------------------------------------------------------------
1205 PROCEDURE pop_approval_doc (document_id IN VARCHAR2,
1206 display_type IN VARCHAR2,
1207 document IN OUT nocopy VARCHAR2,
1208 document_type IN OUT nocopy VARCHAR2) IS
1209
1210 l_message VARCHAR2(32000);
1211 l_contract_id NUMBER;
1212 BEGIN
1213
1214 l_contract_id := wf_engine.GetItemAttrText (itemtype => G_ITEM_TYPE_WF,
1215 itemkey => document_id,
1216 aname => G_WF_ITM_CONTRACT_ID);
1217
1218 document := compile_message(l_contract_id);
1219 document_type := display_type;
1220
1221 RETURN;
1222
1223 EXCEPTION
1224 WHEN OTHERS THEN NULL;
1225
1226 END pop_approval_doc;
1227
1228 ---------------------------------------------------------------------------
1229 -- PROCEDURE check_approval_process
1230 ---------------------------------------------------------------------------
1231 -- Start of comments
1232 --
1233 -- Procedure Name : check_approval_process
1234 -- Description :
1235 -- Business Rules : Checks whether the profile option is set to WF or AME
1236 -- and sets the parameter accordingly.
1237 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
1238 -- Version : 1.0
1239 -- End of comments
1240 ---------------------------------------------------------------------------
1241 PROCEDURE check_approval_process( itemtype IN VARCHAR2,
1242 itemkey IN VARCHAR2,
1243 actid IN NUMBER,
1244 funcmode IN VARCHAR2,
1245 resultout OUT NOCOPY VARCHAR2 )
1246 IS
1247 l_approval_option VARCHAR2(5);
1248 l_contract_id VARCHAR2(240);
1249 l_contract_number okc_k_headers_b.contract_number%TYPE;
1250 l_api_name CONSTANT VARCHAR2(30) := 'check_approval_process';
1251
1252 BEGIN
1253
1254 IF (funcmode = 'RUN') THEN
1258 l_contract_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1255 l_approval_option := fnd_profile.value('OKL_CREDIT_LINE_APPROVAL_PROCESS');
1256 IF l_approval_option = G_LEASE_CONTRACT_APPROVAL_AME THEN
1257
1259 itemkey => itemkey,
1260 aname => G_WF_ITM_CONTRACT_ID);
1261
1262 l_contract_number := wf_engine.GetItemAttrText(itemtype => itemtype,
1263 itemkey => itemkey,
1264 aname => G_WF_ITM_CONTRACT_NUMBER);
1265
1266 wf_engine.SetItemAttrText (itemtype => itemtype,
1267 itemkey => itemkey,
1268 aname => G_WF_ITM_MESSAGE_DESCRIPTION,
1269 avalue => compile_message(l_contract_id));
1270
1271 wf_engine.SetItemAttrText (itemtype => itemtype,
1272 itemkey => itemkey,
1273 aname => G_WF_ITM_APP_REQUEST_SUB,
1274 avalue => get_message('OKL_LLA_REQUEST_APPROVAL_SUB',l_contract_number));
1275
1276 wf_engine.SetItemAttrText (itemtype => itemtype,
1277 itemkey => itemkey,
1278 aname => G_WF_ITM_APP_REMINDER_SUB,
1279 avalue => get_message('OKL_LLA_REQ_APPR_SUB_REMINDER',l_contract_number));
1280
1281 wf_engine.SetItemAttrText (itemtype => itemtype,
1282 itemkey => itemkey,
1283 aname => G_WF_ITM_APP_APPROVED_SUB,
1284 avalue => get_message('OKL_LLA_REQUEST_APPROVED_SUB',l_contract_number));
1285
1286 wf_engine.SetItemAttrText (itemtype => itemtype,
1287 itemkey => itemkey,
1288 aname => G_WF_ITM_APP_REJECTED_SUB,
1289 avalue => get_message('OKL_LLA_REQUEST_REJECTED_SUB',l_contract_number));
1290
1291 wf_engine.SetItemAttrText (itemtype => itemtype,
1292 itemkey => itemkey,
1293 aname => G_WF_ITM_APP_REMINDER_HEAD,
1294 avalue => get_message('OKL_LLA_REQ_APPROVAL_REMINDER',l_contract_number));
1295
1296 wf_engine.SetItemAttrText (itemtype => itemtype,
1297 itemkey => itemkey,
1298 aname => G_WF_ITM_APP_APPROVED_HEAD,
1299 avalue => get_message('OKL_LLA_REQUEST_APPROVED_SUB',l_contract_number));
1300
1301 wf_engine.SetItemAttrText (itemtype => itemtype,
1302 itemkey => itemkey,
1303 aname => G_WF_ITM_APP_REJECTED_HEAD,
1304 avalue => get_message('OKL_LLA_REQUEST_REJECTED_SUB',l_contract_number));
1305
1306 resultout := 'COMPLETE:AME';
1307 ELSIF l_approval_option = G_LEASE_CONTRACT_APPROVAL_WF THEN
1308 resultout := 'COMPLETE:WF';
1309 END IF;
1310
1311 --resultout := 'COMPLETE:';
1312 RETURN;
1313
1314 END IF;
1315 --
1316 -- CANCEL mode
1317 --
1318 IF (funcmode = 'CANCEL') THEN
1319 --
1320 resultout := 'COMPLETE:';
1321 RETURN;
1322 --
1323 END IF;
1324 --
1325 -- TIMEOUT mode
1326 --
1327 IF (funcmode = 'TIMEOUT') THEN
1328 --
1329 resultout := 'COMPLETE:';
1330 RETURN;
1331 --
1332 END IF;
1333
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
1337 RAISE;
1338
1339 END check_approval_process;
1340
1341 ---------------------------------------------------------------------------
1342 -- PROCEDURE wf_approval_process
1343 ---------------------------------------------------------------------------
1344 -- Start of comments
1345 --
1346 -- Procedure Name : wf_approval_process
1347 -- Description :
1348 -- Business Rules : This is raised when the profile option is WF.
1349 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
1350 -- Version : 1.0
1351 -- End of comments
1352 ---------------------------------------------------------------------------
1353 PROCEDURE wf_approval_process( itemtype IN VARCHAR2,
1354 itemkey IN VARCHAR2,
1355 actid IN NUMBER,
1356 funcmode IN VARCHAR2,
1357 resultout OUT NOCOPY VARCHAR2 )IS
1358
1359
1360 CURSOR l_wf_item_key_csr IS
1361 SELECT okl_wf_item_s.NEXTVAL item_key
1362 FROM dual;
1363
1364 l_key VARCHAR2(240);
1365 l_process VARCHAR2(30);
1366 l_item_type VARCHAR2(10) ;
1367 l_contract_id VARCHAR2(240);
1368 l_contract_number okc_k_headers_v.contract_number%TYPE;
1369 l_requester VARCHAR2(240);
1373 BEGIN
1370 l_requester_id VARCHAR2(240);
1371 l_api_name CONSTANT VARCHAR2(30) := 'wf_Approval_Process';
1372
1374
1375 l_process := G_APPROVAL_PROCESS_WF;
1376 l_item_type := G_ITEM_TYPE_WF;
1377
1378 OPEN l_wf_item_key_csr;
1379 FETCH l_wf_item_key_csr INTO l_key;
1380 CLOSE l_wf_item_key_csr;
1381
1382 IF (funcmode = 'RUN') THEN
1383
1384 wf_engine.CreateProcess(itemtype => l_item_type,
1385 itemkey => l_key,
1386 process => l_process);
1387
1388 wf_engine.SetItemParent(itemtype => l_item_type,
1389 itemkey => l_key,
1390 parent_itemtype => itemtype,
1391 parent_itemkey => itemkey,
1392 parent_context => G_WF_ITM_MASTER);
1393
1394 wf_engine.SetItemAttrText (itemtype => l_item_type,
1395 itemkey => l_key,
1396 aname => G_WF_ITM_PARENT_ITEM_KEY,
1397 avalue => itemkey);
1398
1399 wf_engine.SetItemAttrText (itemtype => l_item_type,
1400 itemkey => l_key,
1401 aname => G_WF_ITM_PARENT_ITEM_TYPE,
1402 avalue => itemtype);
1403
1404 -- Re populate Item Attributes for the Detail Process
1405
1406 l_contract_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1407 itemkey => itemkey,
1408 aname => G_WF_ITM_CONTRACT_ID);
1409
1410 l_contract_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1411 itemkey => itemkey,
1412 aname => G_WF_ITM_CONTRACT_NUMBER);
1413
1414 l_requester := wf_engine.GetItemAttrText (itemtype => itemtype,
1415 itemkey => itemkey,
1416 aname => G_WF_ITM_REQUESTER);
1417
1418 l_requester_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1419 itemkey => itemkey,
1420 aname => G_WF_ITM_REQUESTER_ID);
1421
1422 wf_engine.SetItemAttrText (itemtype => l_item_type,
1423 itemkey => l_key,
1424 aname => G_WF_ITM_CONTRACT_ID,
1425 avalue => l_contract_id);
1426
1427 wf_engine.SetItemAttrText (itemtype => l_item_type,
1428 itemkey => l_key,
1429 aname => G_WF_ITM_CONTRACT_NUMBER,
1430 avalue => l_contract_number);
1431
1432 wf_engine.SetItemAttrText (itemtype => l_item_type,
1433 itemkey => l_key,
1434 aname => G_WF_ITM_REQUESTER,
1435 avalue => l_requester);
1436
1437 wf_engine.SetItemAttrText (itemtype => l_item_type,
1438 itemkey => l_key,
1439 aname => G_WF_ITM_REQUESTER_ID,
1440 avalue => l_requester_id);
1441
1442 -- Set the Message Document
1443 wf_engine.SetItemAttrDocument (itemtype => l_item_type,
1444 itemkey => l_key,
1445 aname => G_WF_ITM_MESSAGE_DOC,
1446 documentid => 'plsql:OKL_CREDIT_LINE_WF.pop_approval_doc/'||l_key);
1447
1448 -- Now, Start the Detail Process
1449 wf_engine.StartProcess(itemtype => l_item_type,
1450 itemkey => l_key);
1451
1452 resultout := 'COMPLETE:';
1453 RETURN;
1454
1455 END IF;
1456 --
1457 -- CANCEL mode
1458 --
1459 IF (funcmode = 'CANCEL') THEN
1460 --
1461 resultout := 'COMPLETE:';
1462 RETURN;
1463 --
1464 END IF;
1465 --
1466 -- TIMEOUT mode
1467 --
1468 IF (funcmode = 'TIMEOUT') THEN
1469 --
1470 resultout := 'COMPLETE:';
1471 RETURN;
1472 --
1473 END IF;
1474
1475 EXCEPTION
1476 WHEN OTHERS THEN
1477
1478 IF l_wf_item_key_csr%ISOPEN THEN
1479 CLOSE l_wf_item_key_csr;
1480 END IF;
1481
1482 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
1483 RAISE;
1484
1485 END wf_approval_process;
1486
1487
1488 ---------------------------------------------------------------------------
1489 -- PROCEDURE ame_approval_process
1490 ---------------------------------------------------------------------------
1491 -- Start of comments
1492 --
1493 -- Procedure Name : ame_approval_process
1494 -- Description :
1495 -- Business Rules : This is raised when the profile option is AME.
1496 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
1497 -- Version : 1.0
1498 -- End of comments
1499 ---------------------------------------------------------------------------
1500 PROCEDURE ame_approval_process( itemtype IN VARCHAR2,
1501 itemkey IN VARCHAR2,
1502 actid IN NUMBER,
1503 funcmode IN VARCHAR2,
1504 resultout OUT NOCOPY VARCHAR2 )IS
1505
1506 BEGIN
1507
1508 IF (funcmode = 'RUN') THEN
1509 wf_engine.SetItemAttrDocument (itemtype => itemtype,
1510 itemkey => itemkey,
1511 aname => G_WF_ITM_MESSAGE_DOC,
1512 documentid => 'plsql:OKL_CREDIT_LINE_WF.pop_approval_doc/'||itemkey);
1513
1514 resultout := 'COMPLETE:';
1515 RETURN;
1516 END IF;
1517 --
1518 -- CANCEL mode
1519 --
1520 IF (funcmode = 'CANCEL') THEN
1521 --
1522 resultout := 'COMPLETE:';
1523 RETURN;
1524 --
1525 END IF;
1526 --
1527 -- TIMEOUT mode
1528 --
1529 IF (funcmode = 'TIMEOUT') THEN
1530 --
1531 resultout := 'COMPLETE:';
1532 RETURN;
1533 --
1534 END IF;
1535
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 wf_core.context('OKL_CREDIT_LINE_WF' , 'AME_APPROVAL_PROCESS', itemtype, itemkey, actid, funcmode);
1539 RAISE;
1540
1541 END AME_APPROVAL_PROCESS;
1542
1543
1544 END OKL_CREDIT_LINE_WF;