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