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