[Home] [Help]
PACKAGE BODY: APPS.OKL_SEC_AGREEMENT_WF
Source
1 PACKAGE BODY OKL_SEC_AGREEMENT_WF AS
2 /* $Header: OKLRZWFB.pls 120.4.12010000.3 2008/10/03 19:22:55 rkuttiya ship $ */
3 ----------------------------------------------------------------------------
4 -- Global Message Constants
5 ----------------------------------------------------------------------------
6
7 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
8 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
9 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
10 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
11 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_PARENT_TABLE_TOKEN;
12 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_CHILD_TABLE_TOKEN;
13 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
14 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
15 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
16 G_API_TYPE CONSTANT VARCHAR2(200) := '_PVT';
17 G_CHAR_AMPERSAND CONSTANT VARCHAR2(1) := '&';
18 -------------------------------------------------------------------------------------------------
19 ----------------------------- Messages and constant names ---------------------------------------
20 -------------------------------------------------------------------------------------------------
21 G_KHR_STATUS_NOT_COMPLETE VARCHAR2(200) := 'OKL_LLA_NOT_COMPLETE';
22 G_TRANS_APP_NAME CONSTANT VARCHAR2(200) := 'OKL Investor Adjustment Approval';
23 G_INVALID_APP VARCHAR2(200) := 'OKL_LLA_INVALID_APPLICATION';
24
25 G_MSG_TOKEN_AGREEMENT_NUMBER CONSTANT VARCHAR2(30) := 'IA_NUMBER';
26 G_EVENT_APPROVE_WF CONSTANT VARCHAR2(50) := 'oracle.apps.okl.ia.approve_ia_add_khr';
27 G_EVENT_APPROVE_AME CONSTANT VARCHAR2(50) := 'oracle.apps.okl.ia.approve_ia_add_khr';
28 G_IA_ADD_KHR_APPROVAL_PROCESS CONSTANT VARCHAR2(50) := 'OKL_IA_ADD_KHR_APPR_PROCESS';
29 G_IA_ADD_CONTRACTS_APPRV_WF CONSTANT VARCHAR2(5) := 'WF';
30 G_IA_ADD_KHR_APPROVAL_AME CONSTANT VARCHAR2(5) := 'AME';
31 G_TRX_TSU_CODE_SUBMITTED CONSTANT VARCHAR2(20) := 'SUBMITTED';
32 G_TRX_TSU_CODE_PROCESSED CONSTANT VARCHAR2(20) := 'PROCESSED';
33 G_SOURCE_TRX_TYPE_WF CONSTANT VARCHAR2(10) := 'WF';
34
35 G_POOL_TRX_STS_PEND_APPROVAL CONSTANT VARCHAR2(30) := 'PENDING_APPROVAL';
36 G_POOL_TRX_STS_APPROVED CONSTANT VARCHAR2(15) := 'APPROVED';
37 G_POOL_TRX_STS_APPR_REJECTED CONSTANT VARCHAR2(30) := 'APPROVAL_REJECTED';
38
39 G_WF_ITM_INVESTOR_AGRMNT_ID CONSTANT VARCHAR2(20) := 'INV_AGRMNT_ID';
40 G_WF_ITM_INV_AGRMNT_NUMBER CONSTANT VARCHAR2(20) := 'INV_AGRMNT_NUMBER';
41 G_WF_ITM_POOL_ID CONSTANT VARCHAR2(20) := 'POOL_ID';
42 G_WF_ITM_POOL_NUMBER CONSTANT VARCHAR2(20) := 'POOL_NUMBER';
43 G_WF_ITM_APPLICATION_ID CONSTANT VARCHAR2(20) := 'APPLICATION_ID';
44 G_WF_ITM_POOL_TRANSACTION_ID CONSTANT VARCHAR2(20) := 'TRANSACTION_ID';
45 G_WF_ITM_TRANSACTION_TYPE_ID CONSTANT VARCHAR2(20) := 'TRX_TYPE_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_REMINDER_HEAD CONSTANT VARCHAR2(30) := 'APP_REMINDER_HEAD';
61 G_WF_ITM_APP_APPROVED_SUB CONSTANT VARCHAR2(30) := 'APP_APPROVED_SUB';
62 G_WF_ITM_APP_APPROVED_HEAD CONSTANT VARCHAR2(30) := 'APP_APPROVED_HEAD';
63 G_WF_ITM_APP_REJECTED_SUB CONSTANT VARCHAR2(30) := 'APP_REJECTED_SUB';
64 G_WF_ITM_APP_REJECTED_HEAD CONSTANT VARCHAR2(30) := 'APP_REJECTED_HEAD';
65 G_WF_ITM_ORG_ID CONSTANT VARCHAR2(20) := 'ORG_ID';
66
67 G_WF_ITM_RESULT_APPROVED CONSTANT VARCHAR2(15) := 'APPROVED';
68 G_WF_ITM_APPROVED_YN_YES CONSTANT VARCHAR2(5) := 'Y';
69 G_WF_ITM_APPROVED_YN_NO CONSTANT VARCHAR2(5) := 'N';
70
71 G_ITEM_TYPE_WF CONSTANT VARCHAR2(30) := 'OKLIAADD';
72 G_APPROVAL_PROCESS_WF CONSTANT VARCHAR2(30) := 'IAADD_APPROVAL_WF';
73
74 G_DEFAULT_USER CONSTANT VARCHAR2(30) := 'SYSADMIN';
75 G_DEFAULT_USER_DESC CONSTANT VARCHAR2(30) := 'System Administrator';
76 G_WF_USER_ORIG_SYSTEM_HR CONSTANT VARCHAR2(5) := 'PER';
77
78 ----------------------------------------------------------------------------
79 -- Data Structures
80 ----------------------------------------------------------------------------
81
82 ---------------------------------------------------------------------------
83 -- PROCEDURE l_get_agent
84 ---------------------------------------------------------------------------
85 -- Start of comments
86 --
87 -- Procedure Name : l_get_agent
88 -- Description :
89 -- Business Rules :
90 -- Parameters : p_user_id , x_return_status, x_name, x_description
91 -- Version : 1.0
92 -- End of comments
93 ------------------------------ ---------------------------------------------
94 PROCEDURE l_get_agent(p_user_id IN NUMBER,
95 x_return_status OUT NOCOPY VARCHAR2,
96 x_name OUT NOCOPY VARCHAR2,
97 x_description OUT NOCOPY VARCHAR2) IS
98
99 CURSOR wf_users_csr(c_user_id NUMBER)
100 IS
101 SELECT NAME, DISPLAY_NAME
102 FROM WF_USERS
103 WHERE orig_system_id = c_user_id
104 AND ORIG_SYSTEM = G_WF_USER_ORIG_SYSTEM_HR;
105
106 CURSOR fnd_users_csr(c_user_id NUMBER)
107 IS
108 SELECT USER_NAME, DESCRIPTION
109 FROM FND_USER
110 WHERE user_id = c_user_id;
111 BEGIN
112 x_return_status := OKL_API.G_RET_STS_SUCCESS;
113 OPEN wf_users_csr(p_user_id);
114 FETCH wf_users_csr INTO x_name, x_description;
115 CLOSE wf_users_csr;
116 IF x_name IS NULL THEN
117 OPEN fnd_users_csr(p_user_id);
118 FETCH fnd_users_csr INTO x_name, x_description;
119 CLOSE fnd_users_csr;
120 IF x_name IS NULL THEN
121 x_name := G_DEFAULT_USER_DESC;
122 x_description := G_DEFAULT_USER_DESC;
123 END IF;
124 END IF;
125 EXCEPTION
126 WHEN OTHERS THEN
127 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
128 END l_get_agent;
129
130 ---------------------------------------------------------------------------
131 -- PROCEDURE l_change_add_req_status
132 ---------------------------------------------------------------------------
133 -- Start of comments
134 --
135 -- Procedure Name : l_change_add_req_status
136 -- Description :
137 -- Business Rules : This updates the credit line status.
138 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
139 -- x_msg_data, p_pool_trx_status, p_pool_trans_id, p_pool_id
140 -- Version : 1.0
141 -- End of comments
142 ---------------------------------------------------------------------------
143 PROCEDURE l_change_add_req_status(p_api_version IN NUMBER,
144 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2,
148 p_pool_trx_status IN OKL_POOL_TRANSACTIONS.TRANSACTION_STATUS%TYPE,
149 p_pool_trans_id IN OKL_POOL_TRANSACTIONS.ID%TYPE,
150 p_pool_id IN OKL_POOLS_ALL.ID%TYPE
151 ) IS
152
153 l_api_name CONSTANT VARCHAR2(30) := 'l_change_add_req_status';
154 l_pool_trx_status OKL_POOL_TRANSACTIONS.TRANSACTION_STATUS%TYPE;
155 l_pool_trans_id OKL_POOL_TRANSACTIONS.ID%TYPE;
156 l_pool_id OKL_POOLS_ALL.ID%TYPE;
157 lp_poxv_rec poxv_rec_type;
158 lx_poxv_rec poxv_rec_type;
159
160 BEGIN
161 x_return_status := OKL_API.G_RET_STS_SUCCESS;
162 l_pool_trans_id := p_pool_trans_id;
163 l_pool_trx_status := p_pool_trx_status;
164 l_pool_id := p_pool_id;
165 -- Call start_activity to create savepoint, check compatibility
166 -- and initialize message list
167 x_return_status := OKL_API.START_ACTIVITY (
168 l_api_name,
169 p_init_msg_list,
170 '_PVT',
171 x_return_status);
172
173 -- Check if activity started successfully
174 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
175 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
176 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
177 RAISE OKL_API.G_EXCEPTION_ERROR;
178 END IF;
179
180 --Set the Pool Transaction Id for the update call
181 lp_poxv_rec.ID := l_pool_trans_id;
182 lp_poxv_rec.TRANSACTION_STATUS := l_pool_trx_status;
183 lp_poxv_rec.pol_id := l_pool_id;
184 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
185 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.l_change_add_req_status', 'Before Pool Transaction update call ');
186 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.l_change_add_req_status', 'lp_poxv_rec.TRANSACTION_STATUS ' || lp_poxv_rec.TRANSACTION_STATUS);
187 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.l_change_add_req_status', 'lp_poxv_rec.ID ' || lp_poxv_rec.ID);
188 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.l_change_add_req_status', 'lp_poxv_rec.pol_id ' || lp_poxv_rec.pol_ID);
189 END IF;
190
191 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
192 p_init_msg_list => p_init_msg_list,
193 x_return_status => x_return_status,
194 x_msg_count => x_msg_count,
195 x_msg_data => x_msg_data,
196 p_poxv_rec => lp_poxv_rec,
197 x_poxv_rec => lx_poxv_rec);
198
199 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
200 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.l_change_add_req_status', 'After Pool Transaction update call x_return_status ' || x_return_status);
201 END IF;
202
203 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
204 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
205 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
206 RAISE OKL_API.G_EXCEPTION_ERROR;
207 END IF;
208
209 OKL_API.END_ACTIVITY (x_msg_count,
210 x_msg_data );
211 EXCEPTION
212 WHEN OKL_API.G_EXCEPTION_ERROR THEN
213 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
214 l_api_name,
215 G_PKG_NAME,
216 'OKL_API.G_RET_STS_ERROR',
217 x_msg_count,
218 x_msg_data,
219 '_PVT');
220 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
221 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
222 l_api_name,
223 G_PKG_NAME,
224 'OKL_API.G_RET_STS_UNEXP_ERROR',
225 x_msg_count,
226 x_msg_data,
227 '_PVT');
228 WHEN OTHERS THEN
229 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
230 l_api_name,
231 G_PKG_NAME,
232 'OTHERS',
233 x_msg_count,
234 x_msg_data,
235 '_PVT');
236 END l_change_add_req_status;
237
238 ---------------------------------------------------------------------------
239 -- FUNCTION get_message
240 ---------------------------------------------------------------------------
241 -- Start of comments
242 --
243 -- Function Name : get_message
244 -- Description :
245 -- Business Rules : Sets tha appropriate message for approval and for
246 -- approved or rejected credit line.
247 -- Parameters : p_msg_name, p_inv_agrmnt_number
248 -- Version : 1.0
249 -- End of comments
250 ---------------------------------------------------------------------------
251 FUNCTION get_message(p_msg_name IN VARCHAR2,
252 p_inv_agrmnt_number IN VARCHAR2)
253 RETURN VARCHAR2
254 IS
255 l_message VARCHAR2(100);
256 BEGIN
257 IF p_msg_name IS NOT NULL THEN
258 Fnd_Message.SET_NAME(APPLICATION => G_APP_NAME
259 ,NAME => p_msg_name);
260 Fnd_Message.SET_TOKEN(TOKEN => G_MSG_TOKEN_AGREEMENT_NUMBER,
261 VALUE => p_inv_agrmnt_number);
262 l_message := fnd_message.get();
263 END IF;
264
265 RETURN l_message;
266 EXCEPTION
267 WHEN OTHERS THEN
268 RETURN NULL;
269 END get_message;
270
271 --------------------------------------------------------------------------------------------------
272 ----------------------------------Raising Business Event ------------------------------------------
273 --------------------------------------------------------------------------------------------------
274 ---------------------------------------------------------------------------
275 -- PROCEDURE raise_add_khr_approval_event
276 ---------------------------------------------------------------------------
277 -- Start of comments
278 --
279 -- Procedure Name : raise_add_khr_approval_event
280 -- Description :
281 -- Business Rules : Raises the credit line approval event
282 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
283 -- x_msg_data, p_agreement_id, p_pool_id.
284 -- Version : 1.0
285 -- End of comments
286 ---------------------------------------------------------------------------
287 PROCEDURE raise_add_khr_approval_event (p_api_version IN NUMBER,
288 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
289 x_return_status OUT NOCOPY VARCHAR2,
290 x_msg_count OUT NOCOPY NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2,
292 p_agreement_id IN OKC_K_HEADERS_B.ID%TYPE,
293 p_pool_id IN OKL_POOLS_ALL.ID%TYPE,
294 p_pool_trans_id IN OKL_POOL_TRANSACTIONS.ID%TYPE)
295 IS
296
297 -- Get Investor Agreement Details
298 CURSOR c_fetch_ia_number_csr(p_agreement_id OKC_K_HEADERS_V.ID%TYPE)
299 IS
300 SELECT chrv.contract_number
301 FROM okc_k_headers_v chrv
302 WHERE chrv.id = p_agreement_id;
303
304 -- Get Pool Details
305 CURSOR c_fetch_pool_number_csr(p_pool_id OKL_POOLS.ID%TYPE)
306 IS
307 SELECT pol.pool_number
308 FROM okl_pools_all pol
309 WHERE pol.id = p_pool_id ;
310
311 -- Get the valid application id from FND
312 CURSOR c_get_app_id_csr
313 IS
314 SELECT APPLICATION_ID
315 FROM FND_APPLICATION
316 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
317
318 -- Get the Transaction Type Id from OAM
319 CURSOR c_get_trx_type_csr(c_trx_type VARCHAR2)
320 IS
321 SELECT DESCRIPTION transaction_type_id,
322 FND_APPLICATION_ID fnd_application_id
323 FROM AME_TRANSACTION_TYPES_V
324 WHERE TRANSACTION_TYPE_ID=c_trx_type;
325
326 CURSOR l_wf_item_key_csr IS
327 SELECT okl_wf_item_s.NEXTVAL item_key
328 FROM dual;
329
330 l_return_status VARCHAR2(3);
331 l_api_version NUMBER;
332 l_api_name CONSTANT VARCHAR2(30) := 'raise_add_khr_app_event';
333 l_msg_count NUMBER;
334 l_init_msg_list VARCHAR2(10);
335 l_msg_data VARCHAR2(2000);
336 l_parameter_list wf_parameter_list_t;
337 l_key VARCHAR2(240);
338 l_event_name VARCHAR2(240);
339 l_application_id FND_APPLICATION.APPLICATION_ID%TYPE;
340 l_trans_appl_id AME_CALLING_APPS.APPLICATION_ID%TYPE;
341 l_trans_type_id AME_CALLING_APPS.TRANSACTION_TYPE_ID%TYPE;
342 l_agreement_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
343 l_agreement_id OKC_K_HEADERS_V.ID%TYPE;
344 l_pool_id OKL_POOLS_ALL.ID%TYPE;
345 l_pool_trans_id OKL_POOL_TRANSACTIONS.ID%TYPE;
346 l_requester VARCHAR2(200);
347 l_pool_number OKL_POOLS_ALL.POOL_NUMBER%TYPE;
348 l_name VARCHAR2(200);
349 l_requester_id VARCHAR2(200);
350 l_approval_option VARCHAR2(5);
351
352 BEGIN
353
354 l_return_status := OKL_API.G_RET_STS_SUCCESS;
355 l_api_version := 1.0;
356 l_init_msg_list := OKL_API.G_FALSE;
357 l_agreement_id := p_agreement_id;
358 l_pool_id := p_pool_id;
359 l_pool_trans_id := p_pool_trans_id;
360 x_return_status := OKL_API.G_RET_STS_SUCCESS;
361
362 -- Call start_activity to create savepoint, check compatibility
363 -- and initialize message list
364 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
365 p_init_msg_list,
366 '_PVT',
367 x_return_status);
368 -- Check if activity started successfully
369 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
370 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
371 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
372 RAISE OKL_API.G_EXCEPTION_ERROR;
373 END IF;
374 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
375 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'Begin Add Contract Approval Processing');
376 END IF;
377 -- Create Internal Transaction
378 -- Get the user id, Item key
379 l_requester_id := FND_GLOBAL.USER_ID;
380
381 l_get_agent(p_user_id => l_requester_id,
382 x_return_status => x_return_status,
383 x_name => l_requester,
384 x_description => l_name);
385
386 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
387 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
388 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
389 RAISE OKL_API.G_EXCEPTION_ERROR;
390 END IF;
391 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
392 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'After l_get_agent l_requester : ' || l_requester);
393 END IF;
394 FOR l_wf_item_key_rec IN l_wf_item_key_csr
395 LOOP
396 l_key := l_wf_item_key_rec.item_key;
397 END LOOP;
398
399 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
400 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'l_wf_item_key_csr returned l_key : ' || l_key);
401 END IF;
402
403 -- Get the Investor Agreement Number
404 OPEN c_fetch_ia_number_csr(l_agreement_id);
405 FETCH c_fetch_ia_number_csr INTO l_agreement_num;
406 IF c_fetch_ia_number_csr%NOTFOUND THEN
407 OKL_API.set_message(p_app_name => G_APP_NAME,
408 p_msg_name => G_NO_MATCHING_RECORD,
409 p_token1 => G_COL_NAME_TOKEN,
410 p_token1_value => 'OKC_K_HEADERS_V.ID');
411 RAISE OKL_API.G_EXCEPTION_ERROR;
412 END IF;
413 CLOSE c_fetch_ia_number_csr;
414 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
415 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event','After c_fetch_ia_number_csr l_agreement_num : ' || l_agreement_num);
416 END IF;
417 --Get Pool Number
418 OPEN c_fetch_pool_number_csr(p_pool_id);
419 FETCH c_fetch_pool_number_csr into l_pool_number;
420 IF c_fetch_pool_number_csr%NOTFOUND THEN
421 OKL_API.set_message(p_app_name => G_APP_NAME,
422 p_msg_name => G_NO_MATCHING_RECORD,
423 p_token1 => G_COL_NAME_TOKEN,
424 p_token1_value => 'OKL_POOLS.NUMBER');
425 RAISE OKL_API.G_EXCEPTION_ERROR;
426 END IF;
427 CLOSE c_fetch_pool_number_csr ;
428 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
429 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'c_fetch_pool_number_csr returned l_pool_number : ' || l_pool_number);
430 END IF;
431
432 l_approval_option := fnd_profile.value(G_IA_ADD_KHR_APPROVAL_PROCESS);
433
434 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
435 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'The Profile Option for Sending Workflow is set as ' || l_approval_option);
436 END IF;
437
438 IF l_approval_option = G_IA_ADD_KHR_APPROVAL_AME THEN
439 -- Get the Application ID
440 OPEN c_get_app_id_csr;
441 FETCH c_get_app_id_csr INTO l_application_id;
442 IF c_get_app_id_csr%NOTFOUND THEN
443 OKL_API.set_message(p_app_name => G_APP_NAME,
444 p_msg_name => G_NO_MATCHING_RECORD,
445 p_token1 => G_COL_NAME_TOKEN,
446 p_token1_value => 'Application id');
447 RAISE OKL_API.G_EXCEPTION_ERROR;
448 END IF;
449 CLOSE c_get_app_id_csr;
450 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
451 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'After Get the Application ID c_get_app_id_csr l_application_id : ' || l_application_id);
452 END IF;
453
454 -- Get the Transaction Type ID
455 OPEN c_get_trx_type_csr(G_TRANS_APP_NAME);
456 FETCH c_get_trx_type_csr INTO l_trans_type_id,
457 l_trans_appl_id;
458 IF c_get_trx_type_csr%NOTFOUND THEN
459 OKL_API.set_message(p_app_name => G_APP_NAME,
460 p_msg_name => G_NO_MATCHING_RECORD,
461 p_token1 => G_COL_NAME_TOKEN,
462 p_token1_value => 'AME Transcation TYPE id, Application id');
463 RAISE OKL_API.G_EXCEPTION_ERROR;
464 END IF;
465 CLOSE c_get_trx_type_csr;
466
467 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
468 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'After c_get_trx_type_csr ');
469 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'l_trans_type_id : ' || l_trans_type_id);
470 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'l_trans_appl_id : ' || l_trans_appl_id);
471 END IF;
472
473 IF l_application_id = l_trans_appl_id THEN
474 l_event_name := G_EVENT_APPROVE_AME;
475
476 wf_event.AddParameterToList(G_WF_ITM_INVESTOR_AGRMNT_ID,p_agreement_id,l_parameter_list);
477 wf_event.AddParameterToList(G_WF_ITM_INV_AGRMNT_NUMBER,l_agreement_num,l_parameter_list);
478 wf_event.AddParameterToList(G_WF_ITM_POOL_NUMBER,l_pool_number,l_parameter_list);
479 wf_event.AddParameterToList(G_WF_ITM_POOL_TRANSACTION_ID,l_pool_trans_id,l_parameter_list);
480 wf_event.AddParameterToList(G_WF_ITM_APPLICATION_ID,l_application_id,l_parameter_list);
481 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_TYPE_ID,l_trans_type_id,l_parameter_list);
482 wf_event.AddParameterToList(G_WF_ITM_REQUESTER,l_requester,l_parameter_list);
483 wf_event.AddParameterToList(G_WF_ITM_REQUESTER_ID,l_requester_id,l_parameter_list);
484 wf_event.AddParameterToList(G_WF_ITM_ORG_ID, mo_global.get_current_org_id ,l_parameter_list);
485 ELSE
486 OKL_API.set_message(p_app_name => G_APP_NAME,
487 p_msg_name => G_INVALID_APP);
488 RAISE OKL_API.G_EXCEPTION_ERROR;
489 END IF; -- l_application_id
490 ELSIF l_approval_option = G_IA_ADD_CONTRACTS_APPRV_WF THEN
491 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
492 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'l_approval_option ' || l_approval_option);
493 END IF;
494 l_event_name := G_EVENT_APPROVE_WF;
495
496 wf_event.AddParameterToList(G_WF_ITM_INVESTOR_AGRMNT_ID,p_agreement_id,l_parameter_list);
497 wf_event.AddParameterToList(G_WF_ITM_INV_AGRMNT_NUMBER,l_agreement_num,l_parameter_list);
498 wf_event.AddParameterToList(G_WF_ITM_POOL_ID,l_pool_id,l_parameter_list);
499 wf_event.AddParameterToList(G_WF_ITM_POOL_NUMBER,l_pool_number,l_parameter_list);
500 wf_event.AddParameterToList(G_WF_ITM_POOL_TRANSACTION_ID,l_pool_trans_id,l_parameter_list);
501 wf_event.AddParameterToList(G_WF_ITM_REQUESTER,l_requester,l_parameter_list);
502 wf_event.AddParameterToList(G_WF_ITM_REQUESTER_ID,l_requester_id,l_parameter_list);
503 wf_event.AddParameterToList(G_WF_ITM_ORG_ID, mo_global.get_current_org_id ,l_parameter_list);
504 ELSE
505 RAISE OKL_API.G_EXCEPTION_ERROR;
506 END IF; -- l_approval_option
507
508 -- Raise Event
509 wf_event.RAISE(p_event_name => l_event_name,
510 p_event_key => l_key,
511 p_parameters => l_parameter_list);
512 l_parameter_list.DELETE;
513
514 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
515 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_wf.raise_add_khr_approval_event', 'Worklow Intiated and Approval Sent');
516 END IF;
517
518 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
519 x_msg_data => x_msg_data);
520 EXCEPTION
521 WHEN OKL_API.G_EXCEPTION_ERROR THEN
522 x_return_status := OKL_API.G_RET_STS_ERROR;
523 IF c_fetch_ia_number_csr%ISOPEN THEN
524 CLOSE c_fetch_ia_number_csr;
525 END IF;
526 IF c_get_app_id_csr%ISOPEN THEN
527 CLOSE c_get_app_id_csr;
528 END IF;
529 IF c_get_trx_type_csr%ISOPEN THEN
530 CLOSE c_get_trx_type_csr;
531 END IF;
532 IF l_wf_item_key_csr%ISOPEN THEN
533 CLOSE l_wf_item_key_csr;
534 END IF;
535 IF c_fetch_pool_number_csr%ISOPEN THEN
536 CLOSE c_fetch_pool_number_csr;
537 END IF;
538 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
539 p_api_name => l_api_name,
540 p_pkg_name => G_PKG_NAME,
541 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
542 x_msg_count => x_msg_count,
543 x_msg_data => x_msg_data,
544 p_api_type => G_API_TYPE);
545 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
546 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
547 IF c_fetch_ia_number_csr%ISOPEN THEN
548 CLOSE c_fetch_ia_number_csr;
549 END IF;
550 IF c_get_app_id_csr%ISOPEN THEN
551 CLOSE c_get_app_id_csr;
552 END IF;
553 IF c_get_trx_type_csr%ISOPEN THEN
554 CLOSE c_get_trx_type_csr;
555 END IF;
556 IF l_wf_item_key_csr%ISOPEN THEN
557 CLOSE l_wf_item_key_csr;
558 END IF;
559 IF c_fetch_pool_number_csr%ISOPEN THEN
560 CLOSE c_fetch_pool_number_csr;
561 END IF;
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 => 'OKL_API.G_RET_STS_UNEXP_ERROR',
566 x_msg_count => x_msg_count,
567 x_msg_data => x_msg_data,
568 p_api_type => G_API_TYPE);
569 WHEN OTHERS THEN
570 IF c_fetch_ia_number_csr%ISOPEN THEN
571 CLOSE c_fetch_ia_number_csr;
572 END IF;
573 IF c_get_app_id_csr%ISOPEN THEN
574 CLOSE c_get_app_id_csr;
575 END IF;
576 IF c_get_trx_type_csr%ISOPEN THEN
577 CLOSE c_get_trx_type_csr;
578 END IF;
579 IF l_wf_item_key_csr%ISOPEN THEN
580 CLOSE l_wf_item_key_csr;
581 END IF;
582 IF c_fetch_pool_number_csr%ISOPEN THEN
583 CLOSE c_fetch_pool_number_csr;
584 END IF;
585 -- store SQL error message on message stack
586 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
587 p_api_name => l_api_name,
588 p_pkg_name => G_PKG_NAME,
589 p_exc_name => 'OTHERS',
590 x_msg_count => x_msg_count,
591 x_msg_data => x_msg_data,
592 p_api_type => G_API_TYPE);
593 END raise_add_khr_approval_event;
594
595 ---------------------------------------------------------------------------
596 -- FUNCTION compile_msg_for_add_req
597 ---------------------------------------------------------------------------
598 -- Start of comments
599 --
600 -- Function Name : compile_msg_for_add_req
601 -- Description :
602 -- Business Rules : Creates the message body of the notifications
603 -- Parameters : p_inv_agrmnt_id,p_pool_id,p_pool_trans_id
604 -- Version : 1.0
605 -- End of comments
606 ---------------------------------------------------------------------------
607 FUNCTION compile_msg_for_add_req(p_inv_agrmnt_id IN NUMBER, p_pool_id IN NUMBER,p_pool_trans_id IN NUMBER)
608 RETURN VARCHAR2
609 IS
610 CURSOR l_okl_add_contracts_csr(p_inv_agrmnt_id OKC_K_HEADERS_V.ID%TYPE, p_pool_id OKL_POOLS.ID%TYPE,p_pool_trans_id IN NUMBER)
611 IS
612 SELECT OPA.POOL_NUMBER
613 , OKC.CONTRACT_NUMBER AGREEMENT_NUMBER
614 , FND_REASON.MEANING TRANSACTION_REASON
615 , FND.MEANING TRANSACTION_STATUS
616 , SUM(STRM.AMOUNT) STREAM_VALUE
617 , OPT.TRANSACTION_NUMBER
618 , OPT.TRANSACTION_DATE
619 FROM OKL_POOL_CONTENTS OPC
620 ,OKL_POOL_TRANSACTIONS OPT
621 ,OKL_STRM_ELEMENTS STRM
622 ,FND_LOOKUPS FND
623 ,FND_LOOKUPS FND_REASON
624 ,OKL_POOLS_ALL OPA
625 ,OKC_K_HEADERS_ALL_B OKC
626 WHERE OPT.POL_ID = p_pool_id
627 AND OPT.TRANSACTION_REASON = 'ADJUSTMENTS'
628 AND OPT.TRANSACTION_TYPE = 'ADD'
629 AND OPT.POL_ID = OPA.ID
630 AND OPT.TRANSACTION_STATUS = FND.LOOKUP_CODE
631 AND FND.LOOKUP_TYPE = 'OKL_POOL_TRANSACTION_STATUS'
632 AND OPC.POX_ID = OPT.ID
633 AND OPT.ID = p_pool_trans_id
634 AND OPC.POL_ID = OPT.POL_ID
635 AND OPC.STM_ID = STRM.STM_ID
636 AND STRM.STREAM_ELEMENT_DATE BETWEEN OPC.STREAMS_FROM_DATE AND NVL(OPC.STREAMS_TO_DATE,OPC.STREAMS_FROM_DATE)
637 AND OKC.ID = p_inv_agrmnt_id
638 AND OPT.TRANSACTION_REASON = FND_REASON.LOOKUP_CODE
639 AND FND_REASON.LOOKUP_TYPE = 'OKL_POOL_TRANSACTION_REASON'
640 GROUP BY
641 OPT.TRANSACTION_REASON,
642 OPT.ID,
643 POX_ID,
644 FND.MEANING,
645 FND_REASON.MEANING,
646 OPT.TRANSACTION_NUMBER,
647 OPT.TRANSACTION_DATE,
648 OPA.POOL_NUMBER,
649 OKC.CONTRACT_NUMBER;
650
651 l_msg_count NUMBER;
652 l_msg_data VARCHAR2(2000);
653 l_api_version NUMBER ;
654 l_init_msg_list VARCHAR2(3) ;
655 l_return_status VARCHAR2(3) ;
656 l_true_tax VARCHAR2(200);
657 l_cap_amt NUMBER;
658 l_res_value NUMBER;
659 l_message VARCHAR2(12000);
660 l_flag VARCHAR2(10);
661
662 BEGIN
663
664 l_api_version := 1;
665 l_init_msg_list := OKC_API.G_TRUE;
666 l_return_status := OKL_API.G_RET_STS_SUCCESS;
667 l_message := l_message || '<table class="x1h" cellpadding="1" cellspacing="0" border="1" width="100%">';
668
669 /* 08-Feb-2008 ankushar
670 Modified compile message to display correct notification header format.
671 Start Changes
672 */
673 -- Headers for the creditline details table.
674 -- Investor Agreement Number
675 l_message := l_message || '<tr class="x1r"> <th scope="col" class="x1r"> <span title="'
676 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_INVESTOR_DTLS',
677 p_attribute_code => 'OKL_INV_AGR_NUM')
678 || '" class="x24">'
679 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_INVESTOR_DTLS',
680 p_attribute_code => 'OKL_INV_AGR_NUM')
681 || '</span></th>';
682 -- Pool Number
683 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
684 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_POOL_SERCH',
685 p_attribute_code => 'OKL_POOL_NUMBER')
686 || '" class="x24">'
687 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_POOL_SERCH',
688 p_attribute_code => 'OKL_POOL_NUMBER')
689 || '</span></th>';
690
691 -- Transaction Reason
692 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
693 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_POOL_TRANS_DTLS',
694 p_attribute_code => 'OKL_TRANSACTION_REASON')
695 || '" class="x24">'
696 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_POOL_TRANS_DTLS',
697 p_attribute_code => 'OKL_TRANSACTION_REASON')
698 || '</span></th>';
699
700 -- Transaction Status
701 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
702 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_VIEW_TRX_DTAIL',
703 p_attribute_code => 'OKL_TRANSACTION_STATUS')
704 || '" class="x24">'
705 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_VIEW_TRX_DTAIL',
706 p_attribute_code => 'OKL_TRANSACTION_STATUS')
707 || '</span></th>';
708
709 -- Transaction Date
710 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
711 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_POOL_TRANS_DTLS',
712 p_attribute_code => 'OKL_TRANSACTION_DATE')
713 || '" class="x24">'
714 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_POOL_TRANS_DTLS',
715 p_attribute_code => 'OKL_TRANSACTION_DATE')
716 || '</span></th>';
717 -- Value Of Streams
718 l_message := l_message || '<th scope="col" class="x1r"> <span title="'
719 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_SEC_COMMON_HDR',
720 p_attribute_code => 'OKL_VALUE_OF_STREAMS')
721 || '" class="x24">'
722 || Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LA_SEC_COMMON_HDR',
723 p_attribute_code => 'OKL_VALUE_OF_STREAMS')
724 || '</span></th>';
725 /* 08-Feb-2008 ankushar
726 End Changes
727 */
728
729 FOR l_okl_add_contracts_rec IN l_okl_add_contracts_csr(p_inv_agrmnt_id, p_pool_id,p_pool_trans_id)
730 LOOP
731 IF (l_okl_add_contracts_rec.agreement_number is not null)THEN
732 l_message := l_message || '<tr><td class="x1l x4x">'
733 || l_okl_add_contracts_rec.agreement_number
734 || '</td>';
735 ELSE
736 l_message := l_message || '<tr><td class="x1l x4x"><br></td>';
737 END IF;
738
739 IF(l_okl_add_contracts_rec.pool_number is not null)THEN
740 l_message := l_message || '<td class="x1l x4x">'
741 || l_okl_add_contracts_rec.pool_number
742 || '</td>';
743 ELSE
744 l_message := l_message || '<td class="x1l x4x"><br></td>';
745 END IF;
746
747 IF(l_okl_add_contracts_rec.transaction_reason is not null) THEN
748 l_message := l_message || '<td class="x1l x4x">'
749 || l_okl_add_contracts_rec.transaction_reason
750 || '</td>';
751 ELSE
752 l_message := l_message || '<td class="x1l x4x"><br></td>';
753 END IF;
754
755 IF( l_okl_add_contracts_rec.transaction_status is not null) THEN
756 l_message := l_message || '<td class="x1l x4x">'
757 || l_okl_add_contracts_rec.transaction_status
758 || '</td>';
759 ELSE
760 l_message := l_message || '<td class="x1l x4x"><br></td>';
761 END IF;
762
763 IF(l_okl_add_contracts_rec.transaction_date is not null) THEN
764 l_message := l_message || '<td class="x1l x4x">'
765 || to_date(l_okl_add_contracts_rec.transaction_date, 'dd-mm-yyyy')
766 || '</td>';
767 ELSE
768 l_message := l_message || '<td class="x1l x4x""><br></td>';
769 END IF;
770
771 IF(l_okl_add_contracts_rec.stream_value is not null) THEN
772 l_message := l_message || '<td class="x1l x4x">'
773 || l_okl_add_contracts_rec.stream_value
774 || '</td></tr>';
775 ELSE
776 l_message := l_message || '<td class="x1l x4x"><br></td></tr>';
777
778 END IF;
779
780 END LOOP;
781 l_message := l_message || '</table>';
782
783 RETURN l_message;
784
785 EXCEPTION
786 WHEN OTHERS THEN
787 RETURN NULL;
788 END compile_msg_for_add_req;
789
790 /*
791 -- This API is for IA Add Contracts Request Approval via WF
792 */
793 ---------------------------------------------------------------------------
794 -- PROCEDURE get_add_khr_approver
795 ---------------------------------------------------------------------------
796 -- Start of comments
797 --
798 -- Procedure Name : get_add_khr_approver
799 -- Description :
800 -- Business Rules : returns whether the approver is found or not.
801 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
802 -- Version : 1.0
803 -- End of comments
804 ---------------------------------------------------------------------------
805 PROCEDURE get_add_khr_approver(itemtype IN VARCHAR2,
806 itemkey IN VARCHAR2,
807 actid IN NUMBER,
808 funcmode IN VARCHAR2,
809 resultout OUT NOCOPY VARCHAR2)
810 IS
811 CURSOR l_fnd_users_csr(p_user_id NUMBER)
812 IS
813 SELECT USER_NAME
814 FROM FND_USER
815 WHERE user_id = p_user_id;
816
817 l_api_name CONSTANT VARCHAR2(200) := 'get_add_khr_approver';
818 l_user_id VARCHAR2(200);
819 l_ia_number OKC_K_HEADERS_V.contract_number%TYPE;
820 l_return_status VARCHAR2(1);
821
822 BEGIN
823 l_return_status := OKL_API.G_RET_STS_SUCCESS;
824
825 -- "RUN"
826 IF (funcmode = 'RUN') THEN
827 l_user_id := wf_engine.GetItemAttrText (itemtype => itemtype,
828 itemkey => itemkey,
829 aname => G_WF_ITM_REQUESTER_ID);
830
831 l_ia_number := wf_engine.GetItemAttrText (itemtype => itemtype,
832 itemkey => itemkey,
833 aname => G_WF_ITM_INV_AGRMNT_NUMBER);
834
835 resultout := 'COMPLETE:NOT_FOUND'; -- default
836 IF l_user_id IS NOT NULL THEN
837 FOR l_fnd_users_rec IN l_fnd_users_csr(l_user_id)
838 LOOP
839 wf_engine.SetItemAttrText (itemtype => itemtype,
840 itemkey => itemkey,
841 aname => G_WF_ITM_APPROVER,
842 avalue => l_fnd_users_rec.user_name);
843
844
845 wf_engine.SetItemAttrText (itemtype => itemtype,
846 itemkey => itemkey,
847 aname => G_WF_ITM_MESSAGE_SUBJECT,
848 avalue => get_message('OKL_IA_ADD_KHR_APPROVAL_SUB',l_ia_number));
849
850 wf_engine.SetItemAttrDocument (itemtype => itemtype,
851 itemkey => itemkey,
852 aname => G_WF_ITM_MESSAGE_DOC,
853 documentid => 'plsql:OKL_SEC_AGREEMENT_WF.pop_approval_doc/'||itemkey);
854
855 resultout := 'COMPLETE:FOUND';
856 END LOOP;
857 END IF; -- l_user_id
858
859 -- CANCEL mode
860 ELSIF (funcmode = 'CANCEL') THEN
861 resultout := 'COMPLETE:';
862 RETURN;
863 -- TIMEOUT mode
864 ELSIF (funcmode = 'TIMEOUT') THEN
865 resultout := 'COMPLETE:';
866 RETURN;
867 END IF; -- funcmode
868 EXCEPTION
869 WHEN OTHERS THEN
870 wf_core.context(G_PKG_NAME,
871 l_api_name,
872 itemtype,
873 itemkey,
874 TO_CHAR(actid),
875 funcmode);
876 RAISE;
877
878 END get_add_khr_approver;
879
880 --------------------------------------------------------------------------------------------------
881 --------------------------------- Set Approval Status --------------------------------------------
882 --------------------------------------------------------------------------------------------------
883 ---------------------------------------------------------------------------
884 -- PROCEDURE Set_Add_Khr_Attributes
885 ---------------------------------------------------------------------------
886 -- Start of comments
887 --
888 -- Procedure Name : Set_Add_Khr_Attributes
889 -- Description :
890 -- Business Rules : sets the parent attributes.
891 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
892 -- Version : 1.0
893 -- End of comments
894 ---------------------------------------------------------------------------
895 PROCEDURE Set_Add_Khr_Attributes(itemtype IN VARCHAR2,
896 itemkey IN VARCHAR2,
897 actid IN NUMBER,
898 funcmode IN VARCHAR2,
899 resultout OUT NOCOPY VARCHAR2) IS
900
901 l_approved_yn VARCHAR2(30);
902 l_parent_key VARCHAR2(240);
903 l_parent_type VARCHAR2(240);
904 l_result VARCHAR2(30);
905 l_api_name CONSTANT VARCHAR2(30) := 'Set_Add_Khr_Attributes';
906 l_ia_number okc_k_headers_v.contract_number%TYPE;
907
908 BEGIN
909 SAVEPOINT set_atts;
910 IF (funcmode = 'RUN') THEN
911 -- Get current approval status
912 l_result := wf_engine.GetItemAttrText (itemtype => itemtype,
913 itemkey => itemkey,
914 aname => G_WF_ITM_RESULT);
915
916 l_parent_key := wf_engine.GetItemAttrText (itemtype => itemtype,
917 itemkey => itemkey,
918 aname => G_WF_ITM_PARENT_ITEM_KEY);
919
920 l_parent_type := wf_engine.GetItemAttrText (itemtype => itemtype,
921 itemkey => itemkey,
922 aname => G_WF_ITM_PARENT_ITEM_TYPE);
923
924 l_ia_number := wf_engine.GetItemAttrText (itemtype => itemtype,
925 itemkey => itemkey,
926 aname => G_WF_ITM_INV_AGRMNT_NUMBER);
927
928 IF l_result = G_WF_ITM_RESULT_APPROVED THEN
929 l_approved_yn := G_WF_ITM_APPROVED_YN_YES;
930 wf_engine.SetItemAttrText (itemtype => itemtype,
931 itemkey => itemkey,
932 aname => G_WF_ITM_MESSAGE_SUBJECT,
933 avalue => get_message('OKL_IA_ADD_KHR_APPROVED_SUB',l_ia_number));
934 ELSE
935 l_approved_yn := G_WF_ITM_APPROVED_YN_NO;
936 wf_engine.SetItemAttrText (itemtype => itemtype,
937 itemkey => itemkey,
938 aname => G_WF_ITM_MESSAGE_SUBJECT,
939 avalue => get_message('OKL_IA_ADD_KHR_REJECTED_SUB',l_ia_number));
940 END IF;
941
942 wf_engine.SetItemAttrText(itemtype => l_parent_type,
943 itemkey => l_parent_key,
944 aname => G_WF_ITM_APPROVED_YN,
945 avalue => l_approved_yn);
946 resultout := 'COMPLETE:';
947 RETURN;
948 END IF;
949 -- CANCEL mode
950 IF (funcmode = 'CANCEL') THEN
951 resultout := 'COMPLETE:';
952 RETURN;
953 END IF;
954 -- TIMEOUT mode
955 IF (funcmode = 'TIMEOUT') THEN
956 resultout := 'COMPLETE:';
957 RETURN;
958 END IF;
959 EXCEPTION
960 WHEN OTHERS THEN
961 wf_core.context(G_PKG_NAME,
962 l_api_name,
963 itemtype,
964 itemkey,
965 TO_CHAR(actid),
966 funcmode);
967 RAISE;
968 END Set_Add_Khr_Attributes;
969
970 --------------------------------------------------------------------------------------------------
971 ----------------------------------Main Approval Process ------------------------------------------
972 --------------------------------------------------------------------------------------------------
973 ---------------------------------------------------------------------------
974 -- PROCEDURE update_add_khr_apprv_sts
975 ---------------------------------------------------------------------------
976 -- Start of comments
977 --
978 -- Procedure Name : update_add_khr_apprv_sts
979 -- Description :
980 -- Business Rules : Updates the Add Contracts Request status from pending approval
981 -- to approved or approval rejected.
982 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
983 -- Version : 1.0
984 -- End of comments
985 ---------------------------------------------------------------------------
986 PROCEDURE update_add_khr_apprv_sts(itemtype IN VARCHAR2,
987 itemkey IN VARCHAR2,
988 actid IN NUMBER,
989 funcmode IN VARCHAR2,
990 resultout OUT NOCOPY VARCHAR2)
991 IS
992 CURSOR l_okl_trx_contracts_csr(p_trx_number IN VARCHAR2)
993 IS
994 SELECT id
995 FROM okl_trx_contracts
996 WHERE trx_number = p_trx_number
997 --rkuttiya added for 12.1.1 Multi GAAP
998 AND representation_type = 'PRIMARY';
999 --
1000
1001 l_return_status VARCHAR2(3) ;
1002 l_api_version NUMBER ;
1003 l_msg_count NUMBER;
1004 l_init_msg_list VARCHAR2(10);
1005 l_msg_data VARCHAR2(2000);
1006 l_api_name CONSTANT VARCHAR2(30) := 'update_add_khr_apprv_sts';
1007 l_pool_trans_id OKC_K_HEADERS_V.ID%TYPE;
1008 l_pool_id OKL_POOLS_ALL.ID%TYPE;
1009 l_approved_yn VARCHAR2(30);
1010 l_trx_number VARCHAR2(100);
1011 lv_approval_status_ame VARCHAR2(10);
1012
1013 BEGIN
1014
1015 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1016 l_api_version := 1.0;
1017 l_init_msg_list := OKL_API.G_FALSE;
1018
1019 -- We getting the pool_trans_Id from WF
1020 l_pool_trans_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1021 itemkey => itemkey,
1022 aname => G_WF_ITM_POOL_TRANSACTION_ID);
1023 -- We getting the pool_Id from WF
1024 l_pool_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1025 itemkey => itemkey,
1026 aname => G_WF_ITM_POOL_ID);
1027 --Run Mode
1028 IF funcmode = 'RUN' THEN
1029 l_approved_yn := wf_engine.GetItemAttrText (itemtype => itemtype,
1030 itemkey => itemkey,
1031 aname => G_WF_ITM_APPROVED_YN);
1032 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
1033 itemkey => itemkey,
1034 aname => 'APPROVED_YN');
1035
1036 IF (l_approved_yn = G_WF_ITM_APPROVED_YN_YES OR lv_approval_status_ame = G_WF_ITM_APPROVED_YN_YES)THEN
1037 l_change_add_req_status(p_api_version => l_api_version,
1038 p_init_msg_list => l_init_msg_list,
1039 x_return_status => l_return_status,
1040 x_msg_count => l_msg_count,
1041 x_msg_data => l_msg_data,
1042 p_pool_trx_status => G_POOL_TRX_STS_APPROVED,
1043 p_pool_trans_id => l_pool_trans_id,
1044 p_pool_id => l_pool_id);
1045
1046 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1047 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1048 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1049 RAISE OKL_API.G_EXCEPTION_ERROR;
1050 END IF;
1051
1052 ELSE
1053 l_change_add_req_status(p_api_version => l_api_version,
1054 p_init_msg_list => l_init_msg_list,
1055 x_return_status => l_return_status,
1056 x_msg_count => l_msg_count,
1057 x_msg_data => l_msg_data,
1058 p_pool_trx_status => G_POOL_TRX_STS_APPR_REJECTED,
1059 p_pool_trans_id => l_pool_trans_id,
1060 p_pool_id => l_pool_id);
1061
1062
1063 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1064 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1065 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1066 RAISE OKL_API.G_EXCEPTION_ERROR;
1067 END IF;
1068 END IF;
1069
1070 resultout := 'COMPLETE:';
1071 RETURN;
1072 END IF;
1073 --Transfer Mode
1074 IF funcmode = 'TRANSFER' THEN
1075 resultout := wf_engine.eng_null;
1076 RETURN;
1077 END IF;
1078 -- CANCEL mode
1079 IF (funcmode = 'CANCEL') THEN
1080 resultout := 'COMPLETE:';
1081 RETURN;
1082 END IF;
1083 -- TIMEOUT mode
1084 IF (funcmode = 'TIMEOUT') THEN
1085 resultout := 'COMPLETE:';
1086 RETURN;
1087 END IF;
1088 EXCEPTION
1089 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1090 wf_core.context(G_PKG_NAME,
1091 l_api_name,
1092 itemtype,
1093 itemkey,
1094 TO_CHAR(actid),
1095 funcmode);
1096 RAISE;
1097 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1098 wf_core.context(G_PKG_NAME,
1099 l_api_name,
1100 itemtype,
1101 itemkey,
1102 TO_CHAR(actid),
1103 funcmode);
1104 RAISE;
1105 WHEN OTHERS THEN
1106 wf_core.context(G_PKG_NAME,
1107 l_api_name,
1108 itemtype,
1109 itemkey,
1110 TO_CHAR(actid),
1111 funcmode);
1112 RAISE;
1113 END update_add_khr_apprv_sts;
1114
1115 ---------------------------------------------------------------------------
1116 -- PROCEDURE pop_approval_doc
1117 ---------------------------------------------------------------------------
1118 -- Start of comments
1119 --
1120 -- Procedure Name : pop_approval_doc
1121 -- Description :
1122 -- Business Rules : This procedure is invoked dynamically by Workflow API's
1123 -- in order to populate the message body item attribute
1124 -- during notification submission.
1125 -- Parameters : document_id, display_type, document, document_type.
1126 -- Version : 1.0
1127 -- End of comments
1128 ---------------------------------------------------------------------------
1129 PROCEDURE pop_approval_doc (document_id IN VARCHAR2,
1130 display_type IN VARCHAR2,
1131 document IN OUT nocopy VARCHAR2,
1132 document_type IN OUT nocopy VARCHAR2) IS
1133
1134 l_message VARCHAR2(32000);
1135 l_inv_agrmnt_id NUMBER;
1136 l_pool_id NUMBER;
1137 l_pool_trans_id okl_pool_transactions.ID%TYPE;
1138 BEGIN
1139 l_inv_agrmnt_id := wf_engine.GetItemAttrText (itemtype => G_ITEM_TYPE_WF,
1140 itemkey => document_id,
1141 aname => G_WF_ITM_INVESTOR_AGRMNT_ID);
1142
1143 l_pool_id := wf_engine.GetItemAttrText (itemtype => G_ITEM_TYPE_WF,
1144 itemkey => document_id,
1145 aname => G_WF_ITM_POOL_ID);
1146
1147 l_pool_trans_id := wf_engine.GetItemAttrText (itemtype => G_ITEM_TYPE_WF,
1148 itemkey => document_id,
1149 aname => G_WF_ITM_POOL_TRANSACTION_ID);
1150
1151
1152 document := compile_msg_for_add_req(l_inv_agrmnt_id, l_pool_id,l_pool_trans_id);
1153 document_type := display_type;
1154
1155 RETURN;
1156
1157 EXCEPTION
1158 WHEN OTHERS THEN NULL;
1159
1160 END pop_approval_doc;
1161
1162 ---------------------------------------------------------------------------
1163 -- PROCEDURE check_add_apprv_process
1164 ---------------------------------------------------------------------------
1165 -- Start of comments
1166 --
1167 -- Procedure Name : check_add_apprv_process
1168 -- Description :
1169 -- Business Rules : Checks whether the profile option is set to WF or AME
1170 -- and sets the parameter accordingly.
1171 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
1172 -- Version : 1.0
1173 -- End of comments
1174 ---------------------------------------------------------------------------
1175 PROCEDURE check_add_apprv_process( itemtype IN VARCHAR2,
1176 itemkey IN VARCHAR2,
1177 actid IN NUMBER,
1178 funcmode IN VARCHAR2,
1179 resultout OUT NOCOPY VARCHAR2 )
1180 IS
1181 l_approval_option VARCHAR2(5);
1182 l_pool_id okl_pools.ID%TYPE;
1183 l_inv_agrmnt_id okc_k_headers_v.ID%TYPE;
1184 l_inv_agrmnt_number okc_k_headers_v.contract_number%TYPE;
1185 l_pool_number okl_pools_all.pool_number%TYPE;
1186 l_api_name CONSTANT VARCHAR2(30) := 'check_add_apprv_process';
1187 l_pool_trans_id okl_pool_transactions.ID%TYPE;
1188
1189 BEGIN
1190 IF (funcmode = 'RUN') THEN
1191 l_approval_option := fnd_profile.value(G_IA_ADD_KHR_APPROVAL_PROCESS);
1192 IF l_approval_option = G_IA_ADD_KHR_APPROVAL_AME THEN
1193
1194 l_inv_agrmnt_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1195 itemkey => itemkey,
1196 aname => G_WF_ITM_INVESTOR_AGRMNT_ID);
1197
1198 l_inv_agrmnt_number := wf_engine.GetItemAttrText(itemtype => itemtype,
1199 itemkey => itemkey,
1200 aname => G_WF_ITM_INV_AGRMNT_NUMBER);
1201 l_pool_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1202 itemkey => itemkey,
1203 aname => G_WF_ITM_POOL_ID);
1204 l_pool_number := wf_engine.GetItemAttrText(itemtype => itemtype,
1205 itemkey => itemkey,
1206 aname => G_WF_ITM_POOL_NUMBER);
1207
1208 l_pool_trans_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1209 itemkey => itemkey,
1210 aname => G_WF_ITM_POOL_TRANSACTION_ID);
1211
1212
1213 wf_engine.SetItemAttrText (itemtype => itemtype,
1214 itemkey => itemkey,
1215 aname => G_WF_ITM_MESSAGE_DESCRIPTION,
1216 avalue => compile_msg_for_add_req(l_inv_agrmnt_id, l_pool_id,l_pool_trans_id));
1217
1218 wf_engine.SetItemAttrText (itemtype => itemtype,
1219 itemkey => itemkey,
1220 aname => G_WF_ITM_APP_REQUEST_SUB,
1221 avalue => get_message('OKL_IA_ADD_KHR_APPROVAL_SUB',l_inv_agrmnt_number));
1222
1223 wf_engine.SetItemAttrText (itemtype => itemtype,
1224 itemkey => itemkey,
1225 aname => G_WF_ITM_APP_REMINDER_SUB,
1226 avalue => get_message('OKL_IA_ADD_KHR_APPROVAL_REM',l_inv_agrmnt_number));
1227 wf_engine.SetItemAttrText (itemtype => itemtype,
1228 itemkey => itemkey,
1229 aname => G_WF_ITM_APP_REMINDER_HEAD,
1230 avalue => get_message('OKL_IA_ADD_KHR_APPROVAL_REM',l_inv_agrmnt_number));
1231
1232 wf_engine.SetItemAttrText (itemtype => itemtype,
1233 itemkey => itemkey,
1234 aname => G_WF_ITM_APP_APPROVED_SUB,
1235 avalue => get_message('OKL_IA_ADD_KHR_APPROVED_SUB',l_inv_agrmnt_number));
1236 wf_engine.SetItemAttrText (itemtype => itemtype,
1237 itemkey => itemkey,
1238 aname => G_WF_ITM_APP_APPROVED_HEAD,
1239 avalue => get_message('OKL_IA_ADD_KHR_APPROVED_SUB',l_inv_agrmnt_number));
1240
1241 wf_engine.SetItemAttrText (itemtype => itemtype,
1242 itemkey => itemkey,
1243 aname => G_WF_ITM_APP_REJECTED_SUB,
1244 avalue => get_message('OKL_IA_ADD_KHR_REJECTED_SUB',l_inv_agrmnt_number));
1245 wf_engine.SetItemAttrText (itemtype => itemtype,
1246 itemkey => itemkey,
1247 aname => G_WF_ITM_APP_REJECTED_HEAD,
1248 avalue => get_message('OKL_IA_ADD_KHR_REJECTED_SUB',l_inv_agrmnt_number));
1249
1250 resultout := 'COMPLETE:AME';
1251 ELSIF l_approval_option = G_IA_ADD_CONTRACTS_APPRV_WF THEN
1252 resultout := 'COMPLETE:WF';
1253 END IF;
1254
1255 RETURN;
1256 END IF;
1257 --
1258 -- CANCEL mode
1259 --
1260 IF (funcmode = 'CANCEL') THEN
1261 --
1262 resultout := 'COMPLETE:';
1263 RETURN;
1264 --
1265 END IF;
1266 --
1267 -- TIMEOUT mode
1268 --
1269 IF (funcmode = 'TIMEOUT') THEN
1270 --
1271 resultout := 'COMPLETE:';
1272 RETURN;
1273 --
1274 END IF;
1275
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
1279 RAISE;
1280
1281 END check_add_apprv_process;
1282
1283
1284 ---------------------------------------------------------------------------
1285 -- PROCEDURE wf_add_khr_apprv_process
1286 ---------------------------------------------------------------------------
1287 -- Start of comments
1288 --
1289 -- Procedure Name : wf_add_khr_apprv_process
1290 -- Description :
1291 -- Business Rules : This is raised when the profile option is WF.
1292 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
1293 -- Version : 1.0
1294 -- End of comments
1295 ---------------------------------------------------------------------------
1296 PROCEDURE wf_add_khr_apprv_process( itemtype IN VARCHAR2,
1297 itemkey IN VARCHAR2,
1298 actid IN NUMBER,
1299 funcmode IN VARCHAR2,
1300 resultout OUT NOCOPY VARCHAR2 )IS
1301
1302
1303 CURSOR l_wf_item_key_csr IS
1304 SELECT okl_wf_item_s.NEXTVAL item_key
1305 FROM dual;
1306
1307 l_key VARCHAR2(240);
1308 l_process VARCHAR2(30);
1309 l_item_type VARCHAR2(10) ;
1310 l_pool_id okl_pools.ID%TYPE;
1311 l_pool_number okl_pools.pool_number%TYPE;
1312 l_pool_trans_id okl_pool_transactions.ID%TYPE;
1313 l_agreement_id VARCHAR2(240);
1314 l_agreement_number okc_k_headers_v.contract_number%TYPE;
1315 l_requester VARCHAR2(240);
1316 l_requester_id VARCHAR2(240);
1317 l_api_name CONSTANT VARCHAR2(30) := 'wf_add_khr_apprv_process';
1318
1319 BEGIN
1320
1321 l_process := G_APPROVAL_PROCESS_WF;
1322 l_item_type := G_ITEM_TYPE_WF;
1323
1324 OPEN l_wf_item_key_csr;
1325 FETCH l_wf_item_key_csr INTO l_key;
1326 CLOSE l_wf_item_key_csr;
1327
1328 IF (funcmode = 'RUN') THEN
1329
1330 wf_engine.CreateProcess(itemtype => l_item_type,
1331 itemkey => l_key,
1332 process => l_process);
1333
1334 wf_engine.SetItemParent(itemtype => l_item_type,
1335 itemkey => l_key,
1336 parent_itemtype => itemtype,
1337 parent_itemkey => itemkey,
1338 parent_context => G_WF_ITM_MASTER);
1339
1340 wf_engine.SetItemAttrText (itemtype => l_item_type,
1341 itemkey => l_key,
1342 aname => G_WF_ITM_PARENT_ITEM_KEY,
1343 avalue => itemkey);
1344
1345 wf_engine.SetItemAttrText (itemtype => l_item_type,
1346 itemkey => l_key,
1347 aname => G_WF_ITM_PARENT_ITEM_TYPE,
1348 avalue => itemtype);
1349
1350 -- Re populate Item Attributes for the Detail Process
1351
1352 l_agreement_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1353 itemkey => itemkey,
1354 aname => G_WF_ITM_INVESTOR_AGRMNT_ID);
1355
1356 l_agreement_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1357 itemkey => itemkey,
1358 aname => G_WF_ITM_INV_AGRMNT_NUMBER);
1359
1360 l_requester := wf_engine.GetItemAttrText (itemtype => itemtype,
1361 itemkey => itemkey,
1362 aname => G_WF_ITM_REQUESTER);
1363
1364 l_requester_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1365 itemkey => itemkey,
1366 aname => G_WF_ITM_REQUESTER_ID);
1367
1368 l_pool_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1369 itemkey => itemkey,
1370 aname => G_WF_ITM_POOL_ID);
1371 l_pool_number := wf_engine.GetItemAttrText (itemtype => itemtype,
1372 itemkey => itemkey,
1373 aname => G_WF_ITM_POOL_NUMBER);
1374 l_pool_trans_id := wf_engine.GetItemAttrText (itemtype => itemtype,
1375 itemkey => itemkey,
1376 aname => G_WF_ITM_POOL_TRANSACTION_ID);
1377
1378 wf_engine.SetItemAttrText (itemtype => l_item_type,
1379 itemkey => l_key,
1380 aname => G_WF_ITM_POOL_NUMBER,
1381 avalue => l_pool_number);
1382
1383 wf_engine.SetItemAttrText (itemtype => l_item_type,
1384 itemkey => l_key,
1385 aname => G_WF_ITM_POOL_ID,
1386 avalue => l_pool_id);
1387 wf_engine.SetItemAttrText (itemtype => l_item_type,
1388 itemkey => l_key,
1389 aname => G_WF_ITM_POOL_TRANSACTION_ID,
1390 avalue => l_pool_trans_id);
1391
1392 wf_engine.SetItemAttrText (itemtype => l_item_type,
1393 itemkey => l_key,
1394 aname => G_WF_ITM_INVESTOR_AGRMNT_ID,
1395 avalue => l_agreement_id);
1396
1397 wf_engine.SetItemAttrText (itemtype => l_item_type,
1398 itemkey => l_key,
1399 aname => G_WF_ITM_INV_AGRMNT_NUMBER,
1400 avalue => l_agreement_number);
1401
1402 wf_engine.SetItemAttrText (itemtype => l_item_type,
1403 itemkey => l_key,
1404 aname => G_WF_ITM_REQUESTER,
1405 avalue => l_requester);
1406
1407 wf_engine.SetItemAttrText (itemtype => l_item_type,
1408 itemkey => l_key,
1409 aname => G_WF_ITM_REQUESTER_ID,
1410 avalue => l_requester_id);
1411
1412 -- Set the Message Document
1413 wf_engine.SetItemAttrDocument (itemtype => l_item_type,
1414 itemkey => l_key,
1415 aname => G_WF_ITM_MESSAGE_DOC,
1416 documentid => 'plsql:OKL_SEC_AGREEMENT_WF.pop_approval_doc/'||l_key);
1417
1418 -- Now, Start the Detail Process
1419 wf_engine.StartProcess(itemtype => l_item_type,
1420 itemkey => l_key);
1421
1422 resultout := 'COMPLETE:';
1423 RETURN;
1424
1425 END IF;
1426 --
1427 -- CANCEL mode
1428 --
1429 IF (funcmode = 'CANCEL') THEN
1430 --
1431 resultout := 'COMPLETE:';
1432 RETURN;
1433 --
1434 END IF;
1435 --
1436 -- TIMEOUT mode
1437 --
1438 IF (funcmode = 'TIMEOUT') THEN
1439 --
1440 resultout := 'COMPLETE:';
1441 RETURN;
1442 --
1443 END IF;
1444
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447
1448 IF l_wf_item_key_csr%ISOPEN THEN
1449 CLOSE l_wf_item_key_csr;
1450 END IF;
1451
1452 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
1453 RAISE;
1454
1455 END wf_add_khr_apprv_process;
1456
1457
1458 ---------------------------------------------------------------------------
1459 -- PROCEDURE ame_add_khr_apprv_process
1460 ---------------------------------------------------------------------------
1461 -- Start of comments
1462 --
1463 -- Procedure Name : ame_add_khr_apprv_process
1464 -- Description :
1465 -- Business Rules : This is raised when the profile option is AME.
1466 -- Parameters : itemtype, itemkey, actid, funcmode,resultout.
1467 -- Version : 1.0
1468 -- End of comments
1469 ---------------------------------------------------------------------------
1470 PROCEDURE ame_add_khr_apprv_process( itemtype IN VARCHAR2,
1471 itemkey IN VARCHAR2,
1472 actid IN NUMBER,
1473 funcmode IN VARCHAR2,
1474 resultout OUT NOCOPY VARCHAR2 )IS
1475
1476 BEGIN
1477
1478 IF (funcmode = 'RUN') THEN
1479 wf_engine.SetItemAttrDocument (itemtype => itemtype,
1480 itemkey => itemkey,
1481 aname => G_WF_ITM_MESSAGE_DOC,
1482 documentid => 'plsql:OKL_SEC_AGREEMENT_WF.pop_approval_doc/'||itemkey);
1483
1484 resultout := 'COMPLETE:';
1485 RETURN;
1486 END IF;
1487 --
1488 -- CANCEL mode
1489 --
1490 IF (funcmode = 'CANCEL') THEN
1491 --
1492 resultout := 'COMPLETE:';
1493 RETURN;
1494 --
1495 END IF;
1496 --
1497 -- TIMEOUT mode
1498 --
1499 IF (funcmode = 'TIMEOUT') THEN
1500 --
1501 resultout := 'COMPLETE:';
1502 RETURN;
1503 --
1504 END IF;
1505
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 wf_core.context('OKL_SEC_AGREEMENT_WF' , 'AME_APPROVAL_PROCESS', itemtype, itemkey, actid, funcmode);
1509 RAISE;
1510
1511 END ame_add_khr_apprv_process;
1512
1513
1514 END OKL_SEC_AGREEMENT_WF;
1515