[Home] [Help]
PACKAGE BODY: APPS.OKL_SUBSIDY_POOL_WF
Source
1 PACKAGE BODY okl_subsidy_pool_wf AS
2 /* $Header: OKLRSWFB.pls 120.2 2006/09/28 06:04:57 zrehman noship $ */
3
4 G_NO_MATCHING_RECORD CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_LLA_NO_MATCHING_RECORD';
5 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
6 G_SUBSIDY_POOL_APPROVAL_WF CONSTANT VARCHAR2(2) DEFAULT 'WF';
7 G_SUBSIDY_POOL_APPROVAL_AME CONSTANT VARCHAR2(3) DEFAULT 'AME';
8 G_ITEM_TYPE_WF CONSTANT VARCHAR2(10) DEFAULT 'OKLSPAPP';
9 G_POOL_APPROVAL_PROCESS_WF CONSTANT VARCHAR2(30) DEFAULT 'SUBSIDY_POOL_APPROVAL_WF';
10 G_LINE_APPROVAL_PROCESS_WF CONSTANT VARCHAR2(30) DEFAULT 'BUDGET_LINE_APPROVAL_WF';
11 G_ITEM_TYPE_AME CONSTANT VARCHAR2(10) DEFAULT 'OKLAMAPP';
12 G_APPROVAL_PROCESS_AME CONSTANT VARCHAR2(30) DEFAULT 'APPROVAL_PROC';
13 G_TRANS_APP_NAME_POOL CONSTANT ame_calling_apps.application_name%TYPE DEFAULT 'OKL LA Subsidy Pool Approval';
14 G_TRANS_APP_NAME_LINE CONSTANT ame_calling_apps.application_name%TYPE DEFAULT 'OKL LA Subsidy Pool Budget Approval';
15 G_TRX_TYPE_POOL_APPROVAL CONSTANT okl_trx_types_b.trx_type_class%TYPE DEFAULT 'SUBSIDY_POOL_APPROVAL';
16
17 G_WF_ITM_SUBSIDY_POOL_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'SUBSIDY_POOL_ID';
18 G_WF_ITM_SUBSIDY_POOL_NAME CONSTANT wf_item_attributes.name%TYPE DEFAULT 'SUBSIDY_POOL_NAME';
19 G_WF_ITM_FROM_DATE CONSTANT wf_item_attributes.name%TYPE DEFAULT 'FROM_DATE';
20 G_WF_ITM_TO_DATE CONSTANT wf_item_attributes.name%TYPE DEFAULT 'TO_DATE';
21 G_WF_ITM_TOTAL_BUDGETS CONSTANT wf_item_attributes.name%TYPE DEFAULT 'TOTAL_BUDGETS';
22 G_WF_ITM_POOL_CURRENCY CONSTANT wf_item_attributes.name%TYPE DEFAULT 'CURRENCY_CODE';
23 G_WF_ITM_POOL_DESCR CONSTANT wf_item_attributes.name%TYPE DEFAULT 'POOL_DESCRIPTION';
24 G_WF_ITM_BUDGET_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'BUDGET_LINE_ID';
25 G_WF_ITM_BUDGET_AMOUNT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'BUDGET_AMOUNT';
26 G_WF_ITM_BUDGET_FROM_DATE CONSTANT wf_item_attributes.name%TYPE DEFAULT 'BUDGET_EFFECTIVE_DATE';
27 G_WF_ITM_BUDGET_TYPE CONSTANT wf_item_attributes.name%TYPE DEFAULT 'BUDGET_TYPE_CODE';
28 G_WF_ITM_REQUESTOR CONSTANT wf_item_attributes.name%TYPE DEFAULT 'REQUESTER';
29 G_WF_ITM_REQUESTOR_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'REQUESTOR_ID';
30 G_WF_ITM_APP_REQUEST_SUB CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REQUEST_SUB';
31 G_WF_ITM_APP_REMINDER_SUB CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REMINDER_SUB';
32 G_WF_ITM_APP_APPROVED_SUB CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_APPROVED_SUB';
33 G_WF_ITM_APP_REJECTED_SUB CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REJECTED_SUB';
34 G_WF_ITM_APP_REMINDER_HEAD CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REMINDER_HEAD';
35 G_WF_ITM_APP_APPROVED_HEAD CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_APPROVED_HEAD';
36 G_WF_ITM_APP_REJECTED_HEAD CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REJECTED_HEAD';
37 G_WF_ITM_APPLICATION_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPLICATION_ID';
38 G_WF_ITM_TRANSACTION_TYPE_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'TRX_TYPE_ID';
39 G_WF_ITM_TRANSACTION_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'TRANSACTION_ID';
40
41 G_WF_ITM_APPROVER CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVER';
42 G_WF_ITM_MESSAGE_SUBJECT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_SUBJECT';
43 G_WF_ITM_MESSAGE_DESCR CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DESCRIPTION';
44 G_WF_ITM_MESSAGE_BODY CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DOC';
45 G_WF_ITM_RESULT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'RESULT';
46 G_WF_ITM_APPROVED_YN_YES CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVED';
47 G_WF_ITM_APPROVED_YN_NO CONSTANT wf_item_attributes.name%TYPE DEFAULT 'REJECTED';
48
49 G_POOL_APPROVAL_EVENT CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.subsidy_pool.approval_requested';
50 G_POOL_BUDGET_APPROVAL_EVENT CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.subsidy_pool.approval_budget_requested';
51
52 G_DEFAULT_USER CONSTANT VARCHAR2(10) := 'SYSADMIN';
53 G_DEFAULT_USER_DESC CONSTANT VARCHAR2(30) := 'System Administrator';
54 G_WF_USER_ORIG_SYSTEM_HR CONSTANT VARCHAR2(5) := 'PER';
55 -- local procedure. START
56 PROCEDURE l_get_agent(p_user_id IN NUMBER,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_name OUT NOCOPY VARCHAR2,
59 x_description OUT NOCOPY VARCHAR2) IS
60
61 CURSOR wf_users_csr(cp_user_id NUMBER)IS
62 SELECT NAME, DISPLAY_NAME
63 FROM WF_USERS
64 WHERE orig_system_id = p_user_id
65 AND ORIG_SYSTEM = G_WF_USER_ORIG_SYSTEM_HR;
66
67 CURSOR fnd_users_csr(c_user_id NUMBER)
68 IS
69 SELECT USER_NAME, DESCRIPTION
70 FROM FND_USER
71 WHERE user_id = c_user_id;
72 BEGIN
73 x_return_status := OKL_API.G_RET_STS_SUCCESS;
74 OPEN wf_users_csr(p_user_id);
75 FETCH wf_users_csr INTO x_name, x_description;
76 CLOSE wf_users_csr;
77 IF x_name IS NULL THEN
78 OPEN fnd_users_csr(p_user_id);
79 FETCH fnd_users_csr INTO x_name, x_description;
80 CLOSE fnd_users_csr;
81 IF x_name IS NULL THEN
82 x_name := G_DEFAULT_USER;
83 x_description := G_DEFAULT_USER_DESC;
84 END IF;
85 END IF;
86 EXCEPTION
87 WHEN OTHERS THEN
88 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
89 END l_get_agent;
90
91 FUNCTION get_item_key_wf RETURN NUMBER IS
92 CURSOR c_wf_item_key_csr IS
93 SELECT okl_wf_item_s.NEXTVAL item_key
94 FROM dual;
95
96 lv_item_key NUMBER;
97 BEGIN
98 OPEN c_wf_item_key_csr; FETCH c_wf_item_key_csr INTO lv_item_key;
99 CLOSE c_wf_item_key_csr;
100
101 RETURN lv_item_key;
102 END get_item_key_wf;
103
104 FUNCTION get_message(p_msg_name IN fnd_new_messages.message_name%TYPE
105 ,p_msg_token IN VARCHAR2
106 ,p_token_value IN VARCHAR2
107 ) RETURN VARCHAR2 IS
108 BEGIN
109 fnd_message.set_name(G_APP_NAME,p_msg_name);
110 IF(p_msg_token IS NOT NULL)THEN
111 fnd_message.set_token(p_msg_token,p_token_value);
112 END IF;
113 RETURN fnd_message.get;
114 END get_message;
115
116 FUNCTION get_pool_msg_body(itemtype IN VARCHAR2
117 ,itemkey IN VARCHAR2) RETURN VARCHAR2 IS
118 lv_pool_name okl_subsidy_pools_b.subsidy_pool_name%TYPE;
119 lv_total_budget VARCHAR2(200);
120 lv_currency_code okl_subsidy_pools_b.currency_code%TYPE;
121 lv_from_date okl_subsidy_pools_b.effective_from_date%TYPE;
122 lv_to_date okl_subsidy_pools_b.effective_to_date%TYPE;
123 lv_pool_description okl_subsidy_pools_v.short_description%TYPE;
124 lv_requestor VARCHAR2(100);
125 lv_message_body VARCHAR2(4000);
126 BEGIN
127 lv_pool_name := wf_engine.GetItemAttrText(itemtype => itemtype,
128 itemkey => itemkey,
129 aname => G_WF_ITM_SUBSIDY_POOL_NAME);
130
131 lv_requestor := wf_engine.GetItemAttrText(itemtype => itemtype,
132 itemkey => itemkey,
133 aname => G_WF_ITM_REQUESTOR);
134
135 lv_currency_code := wf_engine.GetItemAttrText(itemtype => itemtype,
136 itemkey => itemkey,
137 aname => G_WF_ITM_POOL_CURRENCY);
138
139 lv_from_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
140 itemkey => itemkey,
141 aname => G_WF_ITM_FROM_DATE);
142 lv_from_date := to_date(lv_from_date,'DD/MM/RRRR');
143
144 lv_to_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
145 itemkey => itemkey,
146 aname => G_WF_ITM_TO_DATE);
147
148 lv_pool_description := wf_engine.GetItemAttrText(itemtype => itemtype,
149 itemkey => itemkey,
150 aname => G_WF_ITM_POOL_DESCR);
151 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
152 itemkey => itemkey,
153 aname => G_WF_ITM_TOTAL_BUDGETS);
154 -- format this amount
155 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
156
157 lv_message_body := get_message('OKL_TOTAL_BUDGETS','TOTAL_BUDGET',lv_total_budget)||' '||lv_currency_code||'<br>'||
158 get_message('OKL_EFFECTIVE_FROM','FROM_DATE',fnd_Date.date_to_displaydate(lv_from_date))||'<br>'||
159 get_message('OKL_EFFECTIVE_TO','TO_DATE',lv_to_date)||'<br>'||
160 get_message('OKL_POOL_DESCRIPTION','DESCR',lv_pool_description)||'<br><br>'||
161 get_message('OKL_REQUESTOR','REQUESTOR',lv_requestor);
162 RETURN lv_message_body;
163 END get_pool_msg_body;
164
165 FUNCTION get_pool_line_msg_body(itemtype IN VARCHAR2
166 ,itemkey IN VARCHAR2) RETURN VARCHAR2 IS
167 lv_pool_name okl_subsidy_pools_b.subsidy_pool_name%TYPE;
168 lv_total_budget VARCHAR2(200);
169 lv_currency_code okl_subsidy_pools_b.currency_code%TYPE;
170 lv_from_date okl_subsidy_pools_b.effective_from_date%TYPE;
171 lv_requestor VARCHAR2(100);
172 lv_budget_type fnd_lookups.meaning%TYPE;
173 lv_message_body VARCHAR2(4000);
174 BEGIN
175 lv_pool_name := wf_engine.GetItemAttrText(itemtype => itemtype,
176 itemkey => itemkey,
177 aname => G_WF_ITM_SUBSIDY_POOL_NAME);
178
179 lv_requestor := wf_engine.GetItemAttrText(itemtype => itemtype,
180 itemkey => itemkey,
181 aname => G_WF_ITM_REQUESTOR);
182
183 lv_currency_code := wf_engine.GetItemAttrText(itemtype => itemtype,
184 itemkey => itemkey,
185 aname => G_WF_ITM_POOL_CURRENCY);
186
187 lv_from_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
188 itemkey => itemkey,
189 aname => G_WF_ITM_BUDGET_FROM_DATE);
190
191 lv_from_date := to_date(lv_from_date,'DD/MM/RRRR');
192
193 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
194 itemkey => itemkey,
195 aname => G_WF_ITM_BUDGET_AMOUNT);
196 -- format this amount
197 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
198 lv_budget_type := wf_engine.GetItemAttrText(itemtype => itemtype,
199 itemkey => itemkey,
200 aname => G_WF_ITM_BUDGET_TYPE);
201 lv_budget_type := okl_accounting_util.get_lookup_meaning('OKL_SUB_POOL_LINE_TYPE',lv_budget_type);
202
203 lv_message_body := '<body>'||get_message('OKL_TOTAL_BUDGETS','TOTAL_BUDGET',lv_total_budget)||' '||lv_currency_code||'<br>'||
204 get_message('OKL_EFFECTIVE_FROM','FROM_DATE',fnd_Date.date_to_displaydate(lv_from_date))||'<br>'||
205 get_message('OKL_SUB_POOL_LINE_TYPE','TYPE',lv_budget_type)||'<br>'||
206 get_message('OKL_SUB_POOL_NAME','SUB_POOL',lv_pool_name)||'<br><br>'||
207 get_message('OKL_REQUESTOR','REQUESTOR',lv_requestor)||'</body>';
208 RETURN lv_message_body;
209
210 END get_pool_line_msg_body;
211
212 PROCEDURE set_attrib_message(itemtype IN VARCHAR2
213 ,itemkey IN VARCHAR2
214 ,message fnd_new_messages.message_name%TYPE
215 ,attrib wf_item_attributes.name%TYPE
216 ,pool_or_line IN VARCHAR2
217 ) IS
218 lv_pool_name okl_subsidy_pools_b.subsidy_pool_name%TYPE;
219 lv_total_budget VARCHAR2(200);
220 lv_currency_code okl_subsidy_pools_b.currency_code%TYPE;
221
222 BEGIN
223 lv_currency_code := wf_engine.GetItemAttrText(itemtype => itemtype,
224 itemkey => itemkey,
225 aname => G_WF_ITM_POOL_CURRENCY);
226
227 lv_pool_name := wf_engine.GetItemAttrText(itemtype => itemtype,
228 itemkey => itemkey,
229 aname => G_WF_ITM_SUBSIDY_POOL_NAME);
230 IF('POOL' = pool_or_line)THEN
231 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
232 itemkey => itemkey,
233 aname => G_WF_ITM_TOTAL_BUDGETS);
234 ELSIF('BUDGET' = pool_or_line)THEN
235 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
236 itemkey => itemkey,
237 aname => G_WF_ITM_BUDGET_AMOUNT);
238 END IF;
239 -- format this amount
240 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
241
242 fnd_message.set_name(G_APP_NAME,message);
243 fnd_message.set_token('NAME', lv_pool_name);
244
245 IF('POOL' = pool_or_line)THEN
246 fnd_message.set_token('TOTAL_BUDGET', lv_total_budget);
247 ELSIF('BUDGET' = pool_or_line)THEN
248 fnd_message.set_token('BUDGET', lv_total_budget);
249 END IF;
250
251 fnd_message.set_token('CURR', lv_currency_code);
252 wf_engine.SetItemAttrText(itemtype => itemtype,
253 itemkey => itemkey,
254 aname => attrib,
255 avalue => fnd_message.get
256 );
257
258 END set_attrib_message;
259
260
261 PROCEDURE get_pool_msg_doc(document_id IN VARCHAR2,
262 display_type IN VARCHAR2,
263 document IN OUT nocopy VARCHAR2,
264 document_type IN OUT nocopy VARCHAR2) IS
265 BEGIN
266 document := get_pool_msg_body(G_ITEM_TYPE_WF,document_id);
267 document_type := display_type;
268 END get_pool_msg_doc;
269
270 PROCEDURE get_pool_line_msg_doc(document_id IN VARCHAR2,
271 display_type IN VARCHAR2,
272 document IN OUT nocopy VARCHAR2,
273 document_type IN OUT nocopy VARCHAR2) IS
274 BEGIN
275 document := get_pool_line_msg_body(G_ITEM_TYPE_WF,document_id);
276 document_type := display_type;
277 END get_pool_line_msg_doc;
278
279
280 -- local procedure. END
281
282 PROCEDURE process_pool_ame (itemtype IN VARCHAR2
283 ,itemkey IN VARCHAR2
284 ,actid IN NUMBER
285 ,funcmode IN VARCHAR2
286 ,resultout OUT NOCOPY VARCHAR2) IS
287 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'process_pool_ame';
288 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.PROCESS_POOL_AME';
289 l_debug_enabled VARCHAR2(10);
290 is_debug_procedure_on BOOLEAN;
291
292 BEGIN
293 l_debug_enabled := okl_debug_pub.check_log_enabled;
294 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
295 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
296 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call process_pool_ame');
297 END IF;
298
299 -- RUN mode
300 IF(funcmode = 'RUN')THEN
301 set_attrib_message(itemtype, itemkey, 'OKL_SUB_POOL_REQ_APPROVAL_SUB', G_WF_ITM_APP_REQUEST_SUB, 'POOL');
302 set_attrib_message(itemtype, itemkey, 'OKL_SUB_POOL_REQ_APPROVAL_REM', G_WF_ITM_APP_REMINDER_SUB, 'POOL');
303 set_attrib_message(itemtype, itemkey, 'OKL_SUB_POOL_REQ_APPROVAL_REM', G_WF_ITM_APP_REMINDER_HEAD, 'POOL');
304 set_attrib_message(itemtype, itemkey, 'OKL_SUB_POOL_REQ_APPROVED_SUB', G_WF_ITM_APP_APPROVED_SUB, 'POOL');
305 set_attrib_message(itemtype, itemkey, 'OKL_SUB_POOL_REQ_APPROVED_SUB', G_WF_ITM_APP_APPROVED_HEAD, 'POOL');
306 set_attrib_message(itemtype, itemkey, 'OKL_SUB_POOL_REQ_REJECT_SUB', G_WF_ITM_APP_REJECTED_SUB, 'POOL');
307 set_attrib_message(itemtype, itemkey, 'OKL_SUB_POOL_REQ_REJECT_SUB', G_WF_ITM_APP_REJECTED_HEAD, 'POOL');
308
309 -- OKL_SUB_POOL_REQ_APPROVAL_BOD (TOTAL_BUDGET, CURR, FROM_DATE, TO_DATE, POOL_DESCR, REQUESTOR) G_WF_ITM_MESSAGE_BODY
310 wf_engine.SetItemAttrText(itemtype => itemtype,
311 itemkey => itemkey,
312 aname => G_WF_ITM_MESSAGE_BODY,
313 avalue => 'plsql:okl_subsidy_pool_wf.get_pool_msg_doc/'||itemkey
314 );
315
316 wf_engine.SetItemAttrText(itemtype => itemtype,
317 itemkey => itemkey,
318 aname => G_WF_ITM_MESSAGE_DESCR,
319 avalue => get_pool_msg_body(itemtype, itemkey)
320 );
321
322 resultout := 'COMPLETE:';
323 END IF;
324
325 -- CANCEL mode
326 IF (funcmode = 'CANCEL') THEN
327 resultout := 'COMPLETE:';
328 RETURN;
329 END IF;
330
331 -- TIMEOUT mode
332 IF (funcmode = 'TIMEOUT') THEN
333 resultout := 'COMPLETE:';
334 RETURN;
335 END IF;
336
337 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
338 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRBWFB.pls call process_pool_ame');
339 END IF;
340 EXCEPTION
341 WHEN OTHERS THEN
342 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
343 RAISE;
344 END process_pool_ame;
345
346 PROCEDURE process_pool_line_ame (itemtype IN VARCHAR2
347 ,itemkey IN VARCHAR2
348 ,actid IN NUMBER
349 ,funcmode IN VARCHAR2
350 ,resultout OUT NOCOPY VARCHAR2) IS
351 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'process_pool_line_ame';
352 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.PROCESS_POOL_LINE_AME';
353 l_debug_enabled VARCHAR2(10);
354 is_debug_procedure_on BOOLEAN;
355 BEGIN
356 l_debug_enabled := okl_debug_pub.check_log_enabled;
357 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
358 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
359 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call process_pool_line_ame');
360 END IF;
361
362 -- RUN mode
363 IF(funcmode = 'RUN')THEN
364 set_attrib_message(itemtype, itemkey, 'OKL_SUB_LINE_REQ_APPROVAL_SUB', G_WF_ITM_APP_REQUEST_SUB, 'BUDGET');
365 set_attrib_message(itemtype, itemkey, 'OKL_SUB_LINE_REQ_APPROVAL_REM', G_WF_ITM_APP_REMINDER_SUB, 'BUDGET');
366 set_attrib_message(itemtype, itemkey, 'OKL_SUB_LINE_REQ_APPROVAL_REM', G_WF_ITM_APP_REMINDER_HEAD, 'BUDGET');
367 set_attrib_message(itemtype, itemkey, 'OKL_SUB_LINE_REQ_APPROVED_SUB', G_WF_ITM_APP_APPROVED_SUB, 'BUDGET');
368 set_attrib_message(itemtype, itemkey, 'OKL_SUB_LINE_REQ_APPROVED_SUB', G_WF_ITM_APP_APPROVED_HEAD, 'BUDGET');
369 set_attrib_message(itemtype, itemkey, 'OKL_SUB_LINE_REQ_REJECT_SUB', G_WF_ITM_APP_REJECTED_SUB, 'BUDGET');
370 set_attrib_message(itemtype, itemkey, 'OKL_SUB_LINE_REQ_REJECT_SUB', G_WF_ITM_APP_REJECTED_HEAD, 'BUDGET');
371
372 -- OKL_SUB_LINE_REQ_APPROVAL_BOD (BUDGET, FROM_DATE, NAME, REQUESTOR)
373 wf_engine.SetItemAttrText(itemtype => itemtype,
374 itemkey => itemkey,
375 aname => G_WF_ITM_MESSAGE_BODY,
376 avalue => 'plsql:okl_subsidy_pool_wf.get_pool_line_msg_doc/'||itemkey
377 );
378
379 wf_engine.SetItemAttrText(itemtype => itemtype,
380 itemkey => itemkey,
381 aname => G_WF_ITM_MESSAGE_DESCR,
382 avalue => get_pool_line_msg_body(itemtype, itemkey)
383 );
384 resultout := 'COMPLETE:';
385 END IF;
386
387 -- CANCEL mode
388 IF (funcmode = 'CANCEL') THEN
389 resultout := 'COMPLETE:';
390 RETURN;
391 END IF;
392
393 -- TIMEOUT mode
394 IF (funcmode = 'TIMEOUT') THEN
395 resultout := 'COMPLETE:';
396 RETURN;
397 END IF;
398 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
399 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRBWFB.pls call process_pool_line_ame');
400 END IF;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
405 RAISE;
406
407 END process_pool_line_ame;
408
409 PROCEDURE check_approval_process(itemtype IN VARCHAR2
410 ,itemkey IN VARCHAR2
411 ,actid IN NUMBER
412 ,funcmode IN VARCHAR2
413 ,resultout OUT NOCOPY VARCHAR2) IS
414 l_approval_option VARCHAR2(10);
415 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'check_approval_process';
416 BEGIN
417 IF(funcmode = 'RUN')THEN
418 l_approval_option := fnd_profile.value('OKL_SUBSIDY_POOL_APPROVAL_PROCESS');
419 IF l_approval_option = G_SUBSIDY_POOL_APPROVAL_AME THEN
420 resultout := 'COMPLETE:AME';
421 ELSIF l_approval_option = G_SUBSIDY_POOL_APPROVAL_WF THEN
422 resultout := 'COMPLETE:WF';
423 END IF;
424 RETURN;
425 END IF;
426
427 -- CANCEL mode
428 IF (funcmode = 'CANCEL') THEN
429 resultout := 'COMPLETE:';
430 RETURN;
431 END IF;
432
433 -- TIMEOUT mode
434 IF (funcmode = 'TIMEOUT') THEN
435 resultout := 'COMPLETE:';
436 RETURN;
437 END IF;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
442 RAISE;
443 END check_approval_process;
444
445
446 PROCEDURE update_pool_approval_status(itemtype IN VARCHAR2
447 ,itemkey IN VARCHAR2
448 ,actid IN NUMBER
449 ,funcmode IN VARCHAR2
450 ,resultout OUT NOCOPY VARCHAR2) IS
451 CURSOR c_get_bud_line_csr (cp_subsidy_pool_id okl_subsidy_pools_b.id%TYPE)IS
452 SELECT id
453 FROM okl_subsidy_pool_budgets_b
454 WHERE subsidy_pool_id = cp_subsidy_pool_id;
455
456 lv_approval_status VARCHAR2(10);
457 lv_approval_status_ame VARCHAR2(10);
458 lv_total_budget VARCHAR2(200);
459 lv_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
460 lv_budget_line_id okl_subsidy_pool_budgets_b.id%TYPE;
461 x_return_status VARCHAR2(10);
462 x_msg_data VARCHAR2(1000);
463 x_msg_count NUMBER;
464 lv_decision_status_code fnd_lookups.lookup_code%TYPE;
465 l_api_name CONSTANT VARCHAR2(60) DEFAULT 'update_pool_status';
466 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.UPDATE_POOL_APPROVAL_STATUS';
467 l_debug_enabled VARCHAR2(10);
468 is_debug_procedure_on BOOLEAN;
469 is_debug_statement_on BOOLEAN;
470
471 BEGIN
472 l_debug_enabled := okl_debug_pub.check_log_enabled;
473 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
474 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
475 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call update_pool_approval_status');
476 END IF;
477
478 -- check for logging on STATEMENT level
479 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
480
481 IF(funcmode = 'RUN')THEN
482 lv_approval_status := wf_engine.GetItemAttrText(itemtype => itemtype,
483 itemkey => itemkey,
484 aname => G_WF_ITM_RESULT);
485
486 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
487 itemkey => itemkey,
488 aname => 'APPROVED_YN');
489 -- get the subsidy pool id here, this can be used for case or approval or rejected also
490 lv_subsidy_pool_id := wf_engine.GetItemAttrText(itemtype => itemtype,
491 itemkey => itemkey,
492 aname => G_WF_ITM_SUBSIDY_POOL_ID);
493 -- also get the budget line id, since this is pool approval, there will be only one budget line for
494 -- the pool in context.
495 OPEN c_get_bud_line_csr(cp_subsidy_pool_id => lv_subsidy_pool_id); FETCH c_get_bud_line_csr INTO lv_budget_line_id;
496 CLOSE c_get_bud_line_csr;
497
498 IF(G_WF_ITM_APPROVED_YN_YES = lv_approval_status OR lv_approval_status_ame = 'Y')THEN
499 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
500 itemkey => itemkey,
501 aname => G_WF_ITM_TOTAL_BUDGETS);
502 okl_subsidy_pool_pvt.update_total_budget(p_api_version => '1.0'
503 ,p_init_msg_list => OKL_API.G_TRUE
504 ,x_return_status => x_return_status
505 ,x_msg_count => x_msg_count
506 ,x_msg_data => x_msg_data
507 ,p_subsidy_pool_id => lv_subsidy_pool_id
508 ,p_total_budget_amt => lv_total_budget
509 );
510 -- write to log
511 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
512 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
513 l_module,
514 'okl_subsidy_pool_pvt.update_total_budget with total budget amount '||lv_total_budget||
515 ' returned with status '||x_return_status ||' x_msg_data '||x_msg_data
516 );
517 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
518
519 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
520 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
521 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
522 RAISE OKL_API.G_EXCEPTION_ERROR;
523 END IF;
524 lv_decision_status_code := 'ACTIVE';
525 okl_subsidy_pool_budget_pvt.set_decision_status_code(p_api_version => '1.0'
526 ,p_init_msg_list => OKL_API.G_TRUE
527 ,x_return_status => x_return_status
528 ,x_msg_count => x_msg_count
529 ,x_msg_data => x_msg_data
530 ,p_sub_pool_budget_id => lv_budget_line_id
531 ,p_decision_status_code => lv_decision_status_code
532 );
533 -- write to log
534 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
535 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
536 l_module,
537 'okl_subsidy_pool_budget_pvt.set_decision_status_code to '||lv_decision_status_code||' lv_budget_line_id '||lv_budget_line_id ||
538 ' returned with status '||x_return_status ||' x_msg_data '||x_msg_data
539 );
540 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
541
542 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
543 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
544 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
545 RAISE OKL_API.G_EXCEPTION_ERROR;
546 END IF;
547
548 okl_subsidy_pool_pvt.set_decision_status_code(p_api_version => '1.0'
549 ,p_init_msg_list => OKL_API.G_TRUE
550 ,x_return_status => x_return_status
551 ,x_msg_count => x_msg_count
552 ,x_msg_data => x_msg_data
553 ,p_subsidy_pool_id => lv_subsidy_pool_id
554 ,p_decision_status_code => lv_decision_status_code);
555 -- write to log
556 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
557 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
558 l_module,
559 'okl_subsidy_pool_pvt.set_decision_status_code to '||lv_decision_status_code||' lv_subsidy_pool_id '||lv_subsidy_pool_id ||
560 ' returned with status '||x_return_status ||' x_msg_data '||x_msg_data
561 );
562 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
563
564 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
565 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
566 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
567 RAISE OKL_API.G_EXCEPTION_ERROR;
568 END IF;
569 resultout := 'COMPLETE:';
570 ELSIF(G_WF_ITM_APPROVED_YN_NO = lv_approval_status OR lv_approval_status_ame = 'N')THEN
571 lv_decision_status_code := 'REJECTED';
572 okl_subsidy_pool_budget_pvt.set_decision_status_code(p_api_version => '1.0'
573 ,p_init_msg_list => OKL_API.G_TRUE
574 ,x_return_status => x_return_status
575 ,x_msg_count => x_msg_count
576 ,x_msg_data => x_msg_data
577 ,p_sub_pool_budget_id => lv_budget_line_id
578 ,p_decision_status_code => lv_decision_status_code
579 );
580 -- write to log
581 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
582 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
583 l_module,
584 'okl_subsidy_pool_budget_pvt.set_decision_status_code to '||lv_decision_status_code||' lv_budget_line_id '||lv_budget_line_id ||
585 ' returned with status '||x_return_status ||' x_msg_data '||x_msg_data
586 );
587 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
588
589 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
590 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
591 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
592 RAISE OKL_API.G_EXCEPTION_ERROR;
593 END IF;
594 okl_subsidy_pool_pvt.set_decision_status_code(p_api_version => '1.0'
595 ,p_init_msg_list => OKL_API.G_TRUE
596 ,x_return_status => x_return_status
597 ,x_msg_count => x_msg_count
598 ,x_msg_data => x_msg_data
599 ,p_subsidy_pool_id => lv_subsidy_pool_id
600 ,p_decision_status_code => lv_decision_status_code);
601 -- write to log
602 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
603 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
604 l_module,
605 'okl_subsidy_pool_pvt.set_decision_status_code to '||lv_decision_status_code||' lv_subsidy_pool_id '||lv_subsidy_pool_id ||
606 ' returned with status '||x_return_status ||' x_msg_data '||x_msg_data
607 );
608 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
609
610 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
611 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
612 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
613 RAISE OKL_API.G_EXCEPTION_ERROR;
614 END IF;
615 resultout := 'COMPLETE:';
616 ELSE
617 NULL;
618 END IF;
619 END IF;
620 -- CANCEL mode
621 IF (funcmode = 'CANCEL') THEN
622 resultout := 'COMPLETE:';
623 RETURN;
624 END IF;
625
626 -- TIMEOUT mode
627 IF (funcmode = 'TIMEOUT') THEN
628 resultout := 'COMPLETE:';
629 RETURN;
630 END IF;
631 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
632 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRBWFB.pls call update_pool_approval_status');
633 END IF;
634
635 EXCEPTION
636 WHEN OKL_API.G_EXCEPTION_ERROR THEN
637 wf_core.context(G_PKG_NAME,
638 l_api_name,
639 itemtype,
640 itemkey,
641 TO_CHAR(actid),
642 funcmode);
643 RAISE;
644 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
645 wf_core.context(G_PKG_NAME,
646 l_api_name,
647 itemtype,
648 itemkey,
649 TO_CHAR(actid),
650 funcmode);
651 RAISE;
652 WHEN OTHERS THEN
653 wf_core.context(G_PKG_NAME,
654 l_api_name,
655 itemtype,
656 itemkey,
657 TO_CHAR(actid),
658 funcmode);
659 RAISE;
660
661 END update_pool_approval_status;
662
663 PROCEDURE update_line_approval_status(itemtype IN VARCHAR2
664 ,itemkey IN VARCHAR2
665 ,actid IN NUMBER
666 ,funcmode IN VARCHAR2
667 ,resultout OUT NOCOPY VARCHAR2) IS
668 lv_budget_type_code okl_subsidy_pool_budgets_b.budget_type_code%TYPE;
669 CURSOR c_get_total_budget_csr (cp_subsidy_pool_id okl_subsidy_pools_b.id%TYPE) IS
670 SELECT total_budgets
671 FROM okl_subsidy_pools_b
672 WHERE id = cp_subsidy_pool_id;
673 lv_approval_status VARCHAR2(10);
674 lv_approval_status_ame VARCHAR2(10);
675 lv_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
676 lv_budget_line_id okl_subsidy_pool_budgets_b.id%TYPE;
677 lv_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
678 lv_line_amount NUMBER;
679 lv_decision_status_code fnd_lookups.lookup_code%TYPE;
680 x_return_status VARCHAR2(10);
681 x_msg_data VARCHAR2(1000);
682 x_msg_count NUMBER;
683 l_api_name CONSTANT VARCHAR2(60) DEFAULT 'update_line_status';
684 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.UPDATE_LINE_APPROVAL_STATUS';
685 l_debug_enabled VARCHAR2(10);
686 is_debug_procedure_on BOOLEAN;
687 is_debug_statement_on BOOLEAN;
688 BEGIN
689 l_debug_enabled := okl_debug_pub.check_log_enabled;
690 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
691 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
692 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call update_line_approval_status');
693 END IF;
694
695 -- RUN mode
696 IF(funcmode = 'RUN')THEN
697 -- check for logging on STATEMENT level
698 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
699
700 lv_approval_status := wf_engine.GetItemAttrText(itemtype => itemtype,
701 itemkey => itemkey,
702 aname => G_WF_ITM_RESULT);
703
704 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
705 itemkey => itemkey,
706 aname => 'APPROVED_YN');
707
708
709 lv_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
710 itemkey => itemkey,
711 aname => G_WF_ITM_BUDGET_TYPE);
712
713 lv_line_amount := wf_engine.GetItemAttrText(itemtype => itemtype,
714 itemkey => itemkey,
715 aname => G_WF_ITM_BUDGET_AMOUNT);
716 -- get the subsidy pool id here, this can be used for case or approval or rejected also
717 lv_subsidy_pool_id := wf_engine.GetItemAttrText(itemtype => itemtype,
718 itemkey => itemkey,
719 aname => G_WF_ITM_SUBSIDY_POOL_ID);
720 OPEN c_get_total_budget_csr(cp_subsidy_pool_id => lv_subsidy_pool_id);
721 FETCH c_get_total_budget_csr INTO lv_total_budget;
722 CLOSE c_get_total_budget_csr;
723 lv_budget_line_id := wf_engine.GetItemAttrText(itemtype => itemtype,
724 itemkey => itemkey,
725 aname => G_WF_ITM_BUDGET_ID);
726
727 IF(G_WF_ITM_APPROVED_YN_YES = lv_approval_status OR lv_approval_status_ame = 'Y')THEN
728 -- if the budget line is of type ADDITION, then add this amount to the total budgets on the subsidy pool
729 -- for all other cases in approval, do not do nuthing
730 IF(lv_budget_type_code = 'ADDITION')THEN
731 lv_total_budget := lv_total_budget + lv_line_amount;
732 okl_subsidy_pool_pvt.update_total_budget(p_api_version => '1.0'
733 ,p_init_msg_list => OKL_API.G_TRUE
734 ,x_return_status => x_return_status
735 ,x_msg_count => x_msg_count
736 ,x_msg_data => x_msg_data
737 ,p_subsidy_pool_id => lv_subsidy_pool_id
738 ,p_total_budget_amt => lv_total_budget
739 );
740 -- write to log
741 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
742 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
743 l_module,
744 'okl_subsidy_pool_pvt.update_total_budget of budget amount '||lv_total_budget||' returned with status '||x_return_status||
745 ' x_msg_data '||x_msg_data
746 );
747 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
748
749 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
750 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
751 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
752 RAISE OKL_API.G_EXCEPTION_ERROR;
753 END IF;
754 END IF; -- end of lv_budget_type_code = 'ADDITION'
755 lv_decision_status_code := 'ACTIVE';
756 okl_subsidy_pool_budget_pvt.set_decision_status_code(p_api_version => '1.0'
757 ,p_init_msg_list => OKL_API.G_TRUE
758 ,x_return_status => x_return_status
759 ,x_msg_count => x_msg_count
760 ,x_msg_data => x_msg_data
761 ,p_sub_pool_budget_id => lv_budget_line_id
762 ,p_decision_status_code => lv_decision_status_code
763 );
764 -- write to log
765 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
766 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
767 l_module,
768 'okl_subsidy_pool_budget_pvt.set_decision_status_code code '||lv_decision_status_code||' returned with status '||x_return_status||
769 ' x_msg_data '||x_msg_data
770 );
771 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
772
773 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
774 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
775 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
776 RAISE OKL_API.G_EXCEPTION_ERROR;
777 END IF;
778 resultout := 'COMPLETE:';
779 ELSIF(G_WF_ITM_APPROVED_YN_NO = lv_approval_status OR lv_approval_status_ame = 'N')THEN
780 IF(lv_budget_type_code = 'REDUCTION')THEN
781 lv_total_budget := lv_total_budget + lv_line_amount;
782 okl_subsidy_pool_pvt.update_total_budget(p_api_version => '1.0'
783 ,p_init_msg_list => OKL_API.G_TRUE
784 ,x_return_status => x_return_status
785 ,x_msg_count => x_msg_count
786 ,x_msg_data => x_msg_data
787 ,p_subsidy_pool_id => lv_subsidy_pool_id
788 ,p_total_budget_amt => lv_total_budget
789 );
790 -- write to log
791 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
792 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
793 l_module,
794 'okl_subsidy_pool_pvt.update_total_budget budget '||lv_total_budget||' returned with status '||x_return_status||
795 ' x_msg_data '||x_msg_data
796 );
797 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
798
799 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
800 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
801 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
802 RAISE OKL_API.G_EXCEPTION_ERROR;
803 END IF;
804 END IF; -- end of lv_budget_type_code = 'REDUCTION'
805 lv_decision_status_code := 'REJECTED';
806 okl_subsidy_pool_budget_pvt.set_decision_status_code(p_api_version => '1.0'
807 ,p_init_msg_list => OKL_API.G_TRUE
808 ,x_return_status => x_return_status
809 ,x_msg_count => x_msg_count
810 ,x_msg_data => x_msg_data
811 ,p_sub_pool_budget_id => lv_budget_line_id
812 ,p_decision_status_code => lv_decision_status_code
813 );
814 -- write to log
815 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
816 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
817 l_module,
818 'okl_subsidy_pool_budget_pvt.set_decision_status_code code '||lv_decision_status_code||' returned with status '||x_return_status||
819 ' x_msg_data '||x_msg_data
820 );
821 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
822
823 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
824 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
825 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
826 RAISE OKL_API.G_EXCEPTION_ERROR;
827 END IF;
828 resultout := 'COMPLETE:';
829 ELSE
830 NULL;
831 END IF;
832 END IF;
833
834 -- CANCEL mode
835 IF (funcmode = 'CANCEL') THEN
836 resultout := 'COMPLETE:';
837 RETURN;
838 END IF;
839
840 -- TIMEOUT mode
841 IF (funcmode = 'TIMEOUT') THEN
842 resultout := 'COMPLETE:';
843 RETURN;
844 END IF;
845
846 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
847 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call update_line_approval_status');
848 END IF;
849
850 EXCEPTION
851 WHEN OKL_API.G_EXCEPTION_ERROR THEN
852 wf_core.context(G_PKG_NAME,
853 l_api_name,
854 itemtype,
855 itemkey,
856 TO_CHAR(actid),
857 funcmode);
858 RAISE;
859 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
860 wf_core.context(G_PKG_NAME,
861 l_api_name,
862 itemtype,
863 itemkey,
864 TO_CHAR(actid),
865 funcmode);
866 RAISE;
867 WHEN OTHERS THEN
868 wf_core.context(G_PKG_NAME,
869 l_api_name,
870 itemtype,
871 itemkey,
872 TO_CHAR(actid),
873 funcmode);
874 RAISE;
875 END update_line_approval_status;
876
877 PROCEDURE get_subsidy_pool_approver (itemtype IN VARCHAR2
878 ,itemkey IN VARCHAR2
879 ,actid IN NUMBER
880 ,funcmode IN VARCHAR2
881 ,resultout OUT NOCOPY VARCHAR2) IS
882 CURSOR l_fnd_users_csr(p_user_id NUMBER)
883 IS
884 SELECT USER_NAME
885 FROM FND_USER
886 WHERE user_id = p_user_id;
887
888 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'get_subsidy_pool_approver';
889
890 l_user_id VARCHAR2(200);
891 lv_receiving_event wf_events.name%TYPE;
892 lv_pool_name okl_subsidy_pools_b.subsidy_pool_name%TYPE;
893 lv_total_budget VARCHAR2(200);
894 lv_currency_code okl_subsidy_pools_b.currency_code%TYPE;
895 lv_from_date okl_subsidy_pools_b.effective_from_date%TYPE;
896 lv_to_date okl_subsidy_pools_b.effective_to_date%TYPE;
897 lv_pool_description okl_subsidy_pools_v.short_description%TYPE;
898 lv_requestor VARCHAR2(100);
899 lv_budget_type fnd_lookups.meaning%TYPE;
900
901 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.GET_SUBSIDY_POOL_APPROVER';
902 l_debug_enabled VARCHAR2(10);
903 is_debug_procedure_on BOOLEAN;
904 is_debug_statement_on BOOLEAN; -- not using this as of now
905
906 BEGIN
907 l_debug_enabled := okl_debug_pub.check_log_enabled;
908 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
909 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
910 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call get_subsidy_pool_approver');
911 END IF;
912
913 IF(funcmode = 'RUN')THEN
914 l_user_id := wf_engine.GetItemAttrText(itemtype => itemtype,
915 itemkey => itemkey,
916 aname => G_WF_ITM_REQUESTOR_ID);
917
918 lv_receiving_event := wf_engine.GetItemAttrText(itemtype => itemtype,
919 itemkey => itemkey,
920 aname => 'LAUNCHING_EVENT');
921
922 resultout := 'COMPLETE:N'; -- default
923 IF l_user_id IS NOT NULL THEN
924 FOR l_fnd_users_rec IN l_fnd_users_csr(l_user_id) LOOP
925 wf_engine.SetItemAttrText (itemtype => itemtype,
926 itemkey => itemkey,
927 aname => G_WF_ITM_APPROVER,
928 avalue => l_fnd_users_rec.user_name);
929
930 lv_pool_name := wf_engine.GetItemAttrText(itemtype => itemtype,
931 itemkey => itemkey,
932 aname => G_WF_ITM_SUBSIDY_POOL_NAME);
933
934 lv_requestor := wf_engine.GetItemAttrText(itemtype => itemtype,
935 itemkey => itemkey,
936 aname => G_WF_ITM_REQUESTOR);
937
938 lv_currency_code := wf_engine.GetItemAttrText(itemtype => itemtype,
939 itemkey => itemkey,
940 aname => G_WF_ITM_POOL_CURRENCY);
941
942 IF(lv_receiving_event = G_POOL_APPROVAL_EVENT)THEN
943 -- prepare the message subject and message body for subsidy pool approval
944 -- OKL_SUB_POOL_REQ_APPROVAL_SUB (NAME, TOTAL_BUDGET, CURR)
945 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
946 itemkey => itemkey,
947 aname => G_WF_ITM_TOTAL_BUDGETS);
948 -- format this amount
949 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
950
951 fnd_message.set_name(G_APP_NAME,'OKL_SUB_POOL_REQ_APPROVAL_SUB');
952 fnd_message.set_token('NAME', lv_pool_name);
953 fnd_message.set_token('TOTAL_BUDGET', lv_total_budget);
954 fnd_message.set_token('CURR', lv_currency_code);
955
956 wf_engine.SetItemAttrText (itemtype => itemtype,
957 itemkey => itemkey,
958 aname => G_WF_ITM_MESSAGE_SUBJECT,
959 avalue => fnd_message.get);
960
961 -- OKL_SUB_POOL_REQ_APPROVAL_BOD (TOTAL_BUDGET, CURR, FROM_DATE, TO_DATE, POOL_DESCR, REQUESTOR) G_WF_ITM_MESSAGE_BODY
962 wf_engine.SetItemAttrText (itemtype => itemtype,
963 itemkey => itemkey,
964 aname => G_WF_ITM_MESSAGE_BODY,
965 avalue => 'plsql:okl_subsidy_pool_wf.get_pool_msg_doc/'||itemkey
966 );
967
968 ELSIF(G_POOL_BUDGET_APPROVAL_EVENT = lv_receiving_event)THEN
969 -- prepare the message subject and message body for subsidy pool budget line approval
970 -- OKL_SUB_LINE_REQ_APPROVAL_SUB (BUDGET, CURR)
971 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
972 itemkey => itemkey,
973 aname => G_WF_ITM_BUDGET_AMOUNT);
974 -- format this amount
975 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
976 lv_budget_type := wf_engine.GetItemAttrText(itemtype => itemtype,
977 itemkey => itemkey,
978 aname => G_WF_ITM_BUDGET_TYPE);
979 lv_budget_type := okl_accounting_util.get_lookup_meaning('OKL_SUB_POOL_LINE_TYPE',lv_budget_type);
980
981 fnd_message.set_name(G_APP_NAME,'OKL_SUB_LINE_REQ_APPROVAL_SUB');
982 fnd_message.set_token('NAME', lv_pool_name);
983 fnd_message.set_token('BUDGET', lv_total_budget);
984 fnd_message.set_token('CURR', lv_currency_code);
985
986 wf_engine.SetItemAttrText (itemtype => itemtype,
987 itemkey => itemkey,
988 aname => G_WF_ITM_MESSAGE_SUBJECT,
989 avalue => fnd_message.get);
990
991 -- OKL_SUB_LINE_REQ_APPROVAL_BOD (BUDGET, FROM_DATE, NAME, REQUESTOR)
992 wf_engine.SetItemAttrText (itemtype => itemtype,
993 itemkey => itemkey,
994 aname => G_WF_ITM_MESSAGE_BODY,
995 avalue => 'plsql:okl_subsidy_pool_wf.get_pool_line_msg_doc/'||itemkey
996 );
997
998 END IF;
999 resultout := 'COMPLETE:Y';
1000 END LOOP;
1001 END IF; -- l_user_id
1002 -- CANCEL mode
1003 ELSIF (funcmode = 'CANCEL') THEN
1004 resultout := 'COMPLETE:';
1005 RETURN;
1006 -- TIMEOUT mode
1007 ELSIF (funcmode = 'TIMEOUT') THEN
1008 resultout := 'COMPLETE:';
1009 RETURN;
1010 END IF; -- funcmode
1011
1012 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1013 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRBWFB.pls call get_subsidy_pool_approver');
1014 END IF;
1015
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
1019 RAISE;
1020 END get_subsidy_pool_approver;
1021
1022 PROCEDURE set_msg_attributes (itemtype IN VARCHAR2
1023 ,itemkey IN VARCHAR2
1024 ,actid IN NUMBER
1025 ,funcmode IN VARCHAR2
1026 ,resultout OUT NOCOPY VARCHAR2) IS
1027 lv_approval_status VARCHAR2(10);
1028 lv_receiving_event wf_events.name%TYPE;
1029 lv_pool_name okl_subsidy_pools_b.subsidy_pool_name%TYPE;
1030 lv_total_budget VARCHAR2(200);
1031 lv_currency_code okl_subsidy_pools_b.currency_code%TYPE;
1032 lv_from_date okl_subsidy_pools_b.effective_from_date%TYPE;
1033 lv_to_date okl_subsidy_pools_b.effective_to_date%TYPE;
1034 lv_pool_description okl_subsidy_pools_v.short_description%TYPE;
1035 lv_approver VARCHAR2(100);
1036 lv_budget_type fnd_lookups.meaning%TYPE;
1037 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'set_msg_attributes';
1038
1039 l_module CONSTANT fnd_log_messages.module%TYPE DEFAULT'okl.plsql.OKL_SUBSIDY_POOL_WF.SET_MSG_ATTRIBUTES';
1040 l_debug_enabled VARCHAR2(10);
1041 is_debug_procedure_on BOOLEAN;
1042
1043 BEGIN
1044
1045 l_debug_enabled := okl_debug_pub.check_log_enabled;
1046 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1047 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1048 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call set_msg_attributes');
1049 END IF;
1050
1051 -- RUN mode
1052 IF(funcmode = 'RUN')THEN
1053 lv_approval_status := wf_engine.GetItemAttrText(itemtype => itemtype,
1054 itemkey => itemkey,
1055 aname => G_WF_ITM_RESULT);
1056 lv_receiving_event := wf_engine.GetItemAttrText(itemtype => itemtype,
1057 itemkey => itemkey,
1058 aname => 'LAUNCHING_EVENT');
1059
1060 lv_pool_name := wf_engine.GetItemAttrText(itemtype => itemtype,
1061 itemkey => itemkey,
1062 aname => G_WF_ITM_SUBSIDY_POOL_NAME);
1063
1064 lv_approver := wf_engine.GetItemAttrText(itemtype => itemtype,
1065 itemkey => itemkey,
1066 aname => G_WF_ITM_APPROVER);
1067
1068 lv_currency_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1069 itemkey => itemkey,
1070 aname => G_WF_ITM_POOL_CURRENCY);
1071
1072 IF(G_WF_ITM_APPROVED_YN_YES = lv_approval_status)THEN
1073 IF(lv_receiving_event = G_POOL_APPROVAL_EVENT)THEN
1074 -- prepare the message subject and message body for subsidy pool approval
1075 -- OKL_SUB_POOL_REQ_APPROVED_SUB (NAME, TOTAL_BUDGET, CURR)
1076 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
1077 itemkey => itemkey,
1078 aname => G_WF_ITM_TOTAL_BUDGETS);
1079 -- format this amount
1080 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
1081
1082 fnd_message.set_name(G_APP_NAME,'OKL_SUB_POOL_REQ_APPROVED_SUB');
1083 fnd_message.set_token('NAME', lv_pool_name);
1084 fnd_message.set_token('TOTAL_BUDGET', lv_total_budget);
1085 fnd_message.set_token('CURR', lv_currency_code);
1086
1087 wf_engine.SetItemAttrText (itemtype => itemtype,
1088 itemkey => itemkey,
1089 aname => G_WF_ITM_MESSAGE_SUBJECT,
1090 avalue => fnd_message.get);
1091
1092 -- OKL_SUB_POOL_REQ_APPROVED_BOD (TOTAL_BUDGET, CURR, FROM_DATE, TO_DATE, POOL_DESCR, REQUESTOR) G_WF_ITM_MESSAGE_BODY
1093 lv_from_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
1094 itemkey => itemkey,
1095 aname => G_WF_ITM_FROM_DATE);
1096 lv_to_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
1097 itemkey => itemkey,
1098 aname => G_WF_ITM_TO_DATE);
1099 lv_pool_description := wf_engine.GetItemAttrText(itemtype => itemtype,
1100 itemkey => itemkey,
1101 aname => G_WF_ITM_POOL_DESCR);
1102
1103 /*fnd_message.set_name(G_APP_NAME,'OKL_SUB_POOL_REQ_APPROVED_BOD');
1104 fnd_message.set_token('TOTAL_BUDGET',lv_total_budget);
1105 fnd_message.set_token('CURR',lv_currency_code);
1106 fnd_message.set_token('FROM_DATE',lv_from_date);
1107 fnd_message.set_token('TO_DATE',lv_to_date);
1108 fnd_message.set_token('POOL_DESCR',lv_pool_description);
1109 fnd_message.set_token('APPROVER',lv_approver);*/
1110
1111 wf_engine.SetItemAttrText (itemtype => itemtype,
1112 itemkey => itemkey,
1113 aname => G_WF_ITM_MESSAGE_BODY,
1114 avalue => 'plsql:okl_subsidy_pool_wf.get_pool_msg_doc/'||itemkey);
1115
1116 ELSIF(G_POOL_BUDGET_APPROVAL_EVENT = lv_receiving_event)THEN
1117 -- prepare the message subject and message body for subsidy pool budget line approval
1118 -- OKL_SUB_LINE_REQ_APPROVED_SUB (BUDGET, CURR)
1119 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
1120 itemkey => itemkey,
1121 aname => G_WF_ITM_BUDGET_AMOUNT);
1122 -- format this amount
1123 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
1124 lv_budget_type := wf_engine.GetItemAttrText(itemtype => itemtype,
1125 itemkey => itemkey,
1126 aname => G_WF_ITM_BUDGET_TYPE);
1127 lv_budget_type := okl_accounting_util.get_lookup_meaning('OKL_SUB_POOL_LINE_TYPE',lv_budget_type);
1128
1129 fnd_message.set_name(G_APP_NAME,'OKL_SUB_LINE_REQ_APPROVED_SUB');
1130 fnd_message.set_token('NAME', lv_pool_name);
1131 fnd_message.set_token('BUDGET', lv_total_budget);
1132 fnd_message.set_token('CURR', lv_currency_code);
1133
1134 wf_engine.SetItemAttrText (itemtype => itemtype,
1135 itemkey => itemkey,
1136 aname => G_WF_ITM_MESSAGE_SUBJECT,
1137 avalue => fnd_message.get);
1138
1139 -- OKL_SUB_LINE_REQ_APPROVED_BOD (BUDGET, FROM_DATE, NAME, REQUESTOR)
1140 lv_from_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
1141 itemkey => itemkey,
1142 aname => G_WF_ITM_BUDGET_FROM_DATE);
1143
1144 lv_from_date := to_date(lv_from_date,'DD/MM/RRRR');
1145
1146 /*fnd_message.set_name(G_APP_NAME, 'OKL_SUB_LINE_REQ_APPROVED_BOD');
1147 fnd_message.set_token('BUDGET', lv_total_budget);
1148 fnd_message.set_token('CURR', lv_currency_code);
1149 fnd_message.set_token('FROM_DATE', lv_from_date);
1150 fnd_message.set_token('TYPE', lv_budget_type);
1151 fnd_message.set_token('NAME', lv_pool_name);
1152 fnd_message.set_token('APPROVER', lv_approver);*/
1153
1154 wf_engine.SetItemAttrText (itemtype => itemtype,
1155 itemkey => itemkey,
1156 aname => G_WF_ITM_MESSAGE_BODY,
1157 avalue => 'plsql:okl_subsidy_pool_wf.get_pool_line_msg_doc/'||itemkey);
1158 END IF;
1159 resultout := 'COMPLETE:';
1160 ELSIF(G_WF_ITM_APPROVED_YN_NO = lv_approval_status)THEN
1161 IF(lv_receiving_event = G_POOL_APPROVAL_EVENT)THEN
1162 -- prepare the message subject and message body for subsidy pool approval
1163 -- OKL_SUB_POOL_REQ_REJECT_SUB (NAME, TOTAL_BUDGET, CURR)
1164 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
1165 itemkey => itemkey,
1166 aname => G_WF_ITM_TOTAL_BUDGETS);
1167 -- format this amount
1168 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
1169
1170 fnd_message.set_name(G_APP_NAME,'OKL_SUB_POOL_REQ_REJECT_SUB');
1171 fnd_message.set_token('NAME', lv_pool_name);
1172 fnd_message.set_token('TOTAL_BUDGET', lv_total_budget);
1173 fnd_message.set_token('CURR', lv_currency_code);
1174
1175 wf_engine.SetItemAttrText (itemtype => itemtype,
1176 itemkey => itemkey,
1177 aname => G_WF_ITM_MESSAGE_SUBJECT,
1178 avalue => fnd_message.get);
1179
1180 -- OKL_SUB_POOL_REQ_REJECT_BOD (TOTAL_BUDGET, CURR, FROM_DATE, TO_DATE, POOL_DESCR, REQUESTOR) G_WF_ITM_MESSAGE_BODY
1181 lv_from_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
1182 itemkey => itemkey,
1183 aname => G_WF_ITM_FROM_DATE);
1184 lv_to_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
1185 itemkey => itemkey,
1186 aname => G_WF_ITM_TO_DATE);
1187 lv_pool_description := wf_engine.GetItemAttrText(itemtype => itemtype,
1188 itemkey => itemkey,
1189 aname => G_WF_ITM_POOL_DESCR);
1190
1191 /*fnd_message.set_name(G_APP_NAME,'OKL_SUB_POOL_REQ_REJECT_BOD');
1192 fnd_message.set_token('TOTAL_BUDGET',lv_total_budget);
1193 fnd_message.set_token('CURR',lv_currency_code);
1194 fnd_message.set_token('FROM_DATE',lv_from_date);
1195 fnd_message.set_token('TO_DATE',lv_to_date);
1196 fnd_message.set_token('POOL_DESCR',lv_pool_description);
1197 fnd_message.set_token('APPROVER',lv_approver);*/
1198
1199 wf_engine.SetItemAttrText (itemtype => itemtype,
1200 itemkey => itemkey,
1201 aname => G_WF_ITM_MESSAGE_BODY,
1202 avalue => 'plsql:okl_subsidy_pool_wf.get_pool_msg_doc/'||itemkey);
1203
1204 ELSIF(G_POOL_BUDGET_APPROVAL_EVENT = lv_receiving_event)THEN
1205 -- prepare the message subject and message body for subsidy pool budget line approval
1206 -- OKL_SUB_LINE_REQ_REJECT_SUB (BUDGET, CURR)
1207 lv_total_budget := wf_engine.GetItemAttrText(itemtype => itemtype,
1208 itemkey => itemkey,
1209 aname => G_WF_ITM_BUDGET_AMOUNT);
1210 -- format this amount
1211 lv_total_budget := okl_accounting_util.format_amount(lv_total_budget,lv_currency_code);
1212 lv_budget_type := wf_engine.GetItemAttrText(itemtype => itemtype,
1213 itemkey => itemkey,
1214 aname => G_WF_ITM_BUDGET_TYPE);
1215 lv_budget_type := okl_accounting_util.get_lookup_meaning('OKL_SUB_POOL_LINE_TYPE',lv_budget_type);
1216
1217 fnd_message.set_name(G_APP_NAME,'OKL_SUB_LINE_REQ_REJECT_SUB');
1218 fnd_message.set_token('NAME', lv_pool_name);
1219 fnd_message.set_token('BUDGET', lv_total_budget);
1220 fnd_message.set_token('CURR', lv_currency_code);
1221
1222 wf_engine.SetItemAttrText (itemtype => itemtype,
1223 itemkey => itemkey,
1224 aname => G_WF_ITM_MESSAGE_SUBJECT,
1225 avalue => fnd_message.get);
1226
1227 -- OKL_SUB_LINE_REQ_REJECT_BOD (BUDGET, FROM_DATE, NAME, REQUESTOR)
1228 lv_from_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
1229 itemkey => itemkey,
1230 aname => G_WF_ITM_BUDGET_FROM_DATE);
1231
1232 lv_from_date := to_date(lv_from_date,'DD/MM/RRRR');
1233
1234 /*fnd_message.set_name(G_APP_NAME, 'OKL_SUB_LINE_REQ_REJECT_BOD');
1235 fnd_message.set_token('BUDGET', lv_total_budget);
1236 fnd_message.set_token('CURR', lv_currency_code);
1237 fnd_message.set_token('FROM_DATE', lv_from_date);
1238 fnd_message.set_token('TYPE', lv_budget_type);
1239 fnd_message.set_token('NAME', lv_pool_name);
1240 fnd_message.set_token('APPROVER', lv_approver);*/
1241
1242 wf_engine.SetItemAttrText (itemtype => itemtype,
1243 itemkey => itemkey,
1244 aname => G_WF_ITM_MESSAGE_BODY,
1245 avalue => 'plsql:okl_subsidy_pool_wf.get_pool_line_msg_doc/'||itemkey);
1246 END IF;
1247 resultout := 'COMPLETE:';
1248 ELSE
1249 NULL; -- status other than approval or rejected, no operation
1250 END IF; -- end of approval value comparision
1251 END IF; -- end of run mode
1252
1253 -- CANCEL mode
1254 IF (funcmode = 'CANCEL') THEN
1255 resultout := 'COMPLETE:';
1256 RETURN;
1257 END IF;
1258
1259 -- TIMEOUT mode
1260 IF (funcmode = 'TIMEOUT') THEN
1261 resultout := 'COMPLETE:';
1262 RETURN;
1263 END IF;
1264
1265 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1266 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRBWFB.pls call set_msg_attributes');
1267 END IF;
1268
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
1272 RAISE;
1273 END set_msg_attributes;
1274
1275 PROCEDURE raise_pool_event_approval(p_api_version IN NUMBER
1276 ,p_init_msg_list IN VARCHAR2
1277 ,x_return_status OUT NOCOPY VARCHAR2
1278 ,x_msg_count OUT NOCOPY NUMBER
1279 ,x_msg_data OUT NOCOPY VARCHAR2
1280 ,p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE) IS
1281
1282 l_api_name CONSTANT VARCHAR2(150) DEFAULT 'raise_pool_event';
1283 l_parameter_list wf_parameter_list_t;
1284 l_approval_option VARCHAR2(10);
1285 l_requester VARCHAR2(200);
1286 l_name VARCHAR2(200);
1287 -- cursor to fetch the initial budget amount and the pool details
1288 -- at this point of approval, only one budget line is allowed.
1289 CURSOR c_get_pool_info_csr (cp_subsidy_pool_id okl_subsidy_pools_b.id%TYPE)IS
1290 SELECT pool.subsidy_pool_name
1291 ,pool.effective_from_date
1292 ,pool.effective_to_date
1293 ,pool.total_budgets
1294 ,pool.currency_code
1295 ,pool.short_description
1296 ,bud.budget_amount
1297 FROM okl_subsidy_pools_v pool
1298 ,okl_subsidy_pool_budgets_b bud
1299 WHERE pool.id = cp_subsidy_pool_id
1300 AND pool.id = bud.subsidy_pool_id;
1301 cv_get_pool_info c_get_pool_info_csr%ROWTYPE;
1302
1303 lv_wf_item_key NUMBER;
1304
1305 -- Get the valid application id from FND
1306 CURSOR c_get_app_id_csr
1307 IS
1308 SELECT APPLICATION_ID
1309 FROM FND_APPLICATION
1310 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
1311
1312 -- Get the Transaction Type Id from OAM
1313 CURSOR c_get_trx_type_csr(cp_trx_type VARCHAR2) IS
1314 SELECT transaction_type_id,
1315 fnd_application_id
1316 FROM ame_transaction_types_v
1317 WHERE transaction_type_id = cp_trx_type;
1318 c_get_trx_type_csr_rec c_get_trx_type_csr%ROWTYPE;
1319
1320 CURSOR l_trx_try_csr IS
1321 SELECT id
1322 FROM okl_trx_types_b
1323 WHERE trx_type_class = G_TRX_TYPE_POOL_APPROVAL;
1324
1325 l_application_id fnd_application.application_id%TYPE;
1326 l_trans_appl_id ame_calling_apps.application_id%TYPE;
1327 l_trans_type_id ame_calling_apps.transaction_type_id%TYPE;
1328
1329 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.RAISE_POOL_EVENT_APPROVAL';
1330 l_debug_enabled VARCHAR2(10);
1331 is_debug_procedure_on BOOLEAN;
1332 is_debug_statement_on BOOLEAN;
1333
1334 BEGIN
1335 l_debug_enabled := okl_debug_pub.check_log_enabled;
1336 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1337 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1338 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call raise_pool_event_approval');
1339 END IF;
1340
1341 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1342 -- Call start_activity to create savepoint, check compatibility and initialize message list
1343 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1344 p_init_msg_list,
1345 '_PVT',
1346 x_return_status);
1347 -- Check if activity started successfully
1348 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1349 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1350 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1351 RAISE OKL_API.G_EXCEPTION_ERROR;
1352 END IF;
1353
1354 -- check for logging on STATEMENT level
1355 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1356
1357 l_get_agent(p_user_id => fnd_global.user_id
1358 ,x_return_status => x_return_status
1359 ,x_name => l_requester
1360 ,x_description => l_name);
1361 -- write to log
1362 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1363 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1364 l_module,
1365 'l_get_agent return staus '||x_return_status||' l_requester '||l_requester||' l_name '||l_name
1366 );
1367 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1368
1369 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1370 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1371 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1372 RAISE OKL_API.G_EXCEPTION_ERROR;
1373 END IF;
1374
1375 l_approval_option := fnd_profile.value('OKL_SUBSIDY_POOL_APPROVAL_PROCESS');
1376 IF(l_approval_option = G_SUBSIDY_POOL_APPROVAL_AME)THEN
1377 -- Get the Application ID
1378 OPEN c_get_app_id_csr;
1379 FETCH c_get_app_id_csr INTO l_application_id;
1380 IF c_get_app_id_csr%NOTFOUND THEN
1381 OKL_API.set_message(p_app_name => G_APP_NAME,
1382 p_msg_name => G_NO_MATCHING_RECORD,
1383 p_token1 => G_COL_NAME_TOKEN,
1384 p_token1_value => 'Application id');
1385 RAISE OKL_API.G_EXCEPTION_ERROR;
1386 END IF;
1387 CLOSE c_get_app_id_csr;
1388
1389 -- Get the Transaction Type ID
1390 OPEN c_get_trx_type_csr(G_TRANS_APP_NAME_POOL);
1391 FETCH c_get_trx_type_csr INTO l_trans_type_id,
1392 l_trans_appl_id;
1393 IF c_get_trx_type_csr%NOTFOUND THEN
1394 OKL_API.set_message(p_app_name => G_APP_NAME,
1395 p_msg_name => G_NO_MATCHING_RECORD,
1396 p_token1 => G_COL_NAME_TOKEN,
1397 p_token1_value => 'AME Transcation TYPE id, Application id');
1398 RAISE OKL_API.G_EXCEPTION_ERROR;
1399 END IF;
1400 CLOSE c_get_trx_type_csr;
1401 IF l_application_id = l_trans_appl_id THEN
1402 wf_event.AddParameterToList(G_WF_ITM_APPLICATION_ID,l_application_id,l_parameter_list);
1403 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_TYPE_ID,l_trans_type_id,l_parameter_list);
1404 END IF;
1405 -- write to log
1406 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1407 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1408 l_module,
1409 'l_approval_option '||l_approval_option||' raising budget line approval event '
1410 );
1411 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1412
1413 ELSIF(l_approval_option = G_SUBSIDY_POOL_APPROVAL_WF)THEN
1414 -- write to log
1415 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1416 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1417 l_module,
1418 'l_approval_option '||l_approval_option||' raising budget line approval event '
1419 );
1420 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1421 ELSE
1422 RAISE OKL_API.G_EXCEPTION_ERROR;
1423 END IF;
1424
1425 IF(l_approval_option IN (G_SUBSIDY_POOL_APPROVAL_WF, G_SUBSIDY_POOL_APPROVAL_AME))THEN
1426 OPEN c_get_pool_info_csr (cp_subsidy_pool_id => p_subsidy_pool_id);
1427 FETCH c_get_pool_info_csr INTO cv_get_pool_info;
1428 CLOSE c_get_pool_info_csr;
1429 wf_event.AddParameterToList(G_WF_ITM_SUBSIDY_POOL_ID,p_subsidy_pool_id,l_parameter_list);
1430 wf_event.AddParameterToList(G_WF_ITM_SUBSIDY_POOL_NAME,cv_get_pool_info.subsidy_pool_name,l_parameter_list);
1431 wf_event.AddParameterToList(G_WF_ITM_FROM_DATE,to_date(cv_get_pool_info.effective_from_date,'DD/MM/RRRR'),l_parameter_list);
1432 wf_event.AddParameterToList(G_WF_ITM_TO_DATE,to_date(cv_get_pool_info.effective_to_date,'DD/MM/RRRR'),l_parameter_list);
1433 wf_event.AddParameterToList(G_WF_ITM_TOTAL_BUDGETS,cv_get_pool_info.budget_amount,l_parameter_list);
1434 wf_event.AddParameterToList(G_WF_ITM_POOL_CURRENCY,cv_get_pool_info.currency_code,l_parameter_list);
1435 wf_event.AddParameterToList(G_WF_ITM_POOL_DESCR,cv_get_pool_info.short_description,l_parameter_list);
1436
1437 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_ID,p_subsidy_pool_id,l_parameter_list);
1438
1439 wf_event.AddParameterToList(G_WF_ITM_REQUESTOR,l_requester,l_parameter_list);
1440 wf_event.AddParameterToList(G_WF_ITM_REQUESTOR_ID,fnd_global.user_id,l_parameter_list);
1441
1442 lv_wf_item_key := get_item_key_wf;
1443 -- Raise Event
1444 wf_event.RAISE(p_event_name => G_POOL_APPROVAL_EVENT,
1445 p_event_key => lv_wf_item_key,
1446 p_parameters => l_parameter_list);
1447 l_parameter_list.DELETE;
1448 END IF;
1449
1450 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,x_msg_data => x_msg_data);
1451
1452 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1453 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRBWFB.pls call raise_pool_event_approval');
1454 END IF;
1455
1456 EXCEPTION
1457 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1458 x_return_status := OKL_API.G_RET_STS_ERROR;
1459 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1460 p_api_name => l_api_name,
1461 p_pkg_name => G_PKG_NAME,
1462 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1463 x_msg_count => x_msg_count,
1464 x_msg_data => x_msg_data,
1465 p_api_type => G_API_TYPE);
1466 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1467 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1468 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1469 p_api_name => l_api_name,
1470 p_pkg_name => G_PKG_NAME,
1471 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1472 x_msg_count => x_msg_count,
1473 x_msg_data => x_msg_data,
1474 p_api_type => G_API_TYPE);
1475 WHEN OTHERS THEN
1476 -- store SQL error message on message stack
1477 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1478 p_api_name => l_api_name,
1479 p_pkg_name => G_PKG_NAME,
1480 p_exc_name => 'OTHERS',
1481 x_msg_count => x_msg_count,
1482 x_msg_data => x_msg_data,
1483 p_api_type => G_API_TYPE);
1484 END raise_pool_event_approval;
1485
1486 PROCEDURE raise_budget_event_approval(p_api_version IN NUMBER
1487 ,p_init_msg_list IN VARCHAR2
1488 ,x_return_status OUT NOCOPY VARCHAR2
1489 ,x_msg_count OUT NOCOPY NUMBER
1490 ,x_msg_data OUT NOCOPY VARCHAR2
1491 ,p_subsidy_pool_budget_id IN okl_subsidy_pool_budgets_b.id%TYPE) IS
1492 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'raise_budget_event';
1493 l_parameter_list wf_parameter_list_t;
1494 l_approval_option VARCHAR2(10);
1495 l_requester VARCHAR2(200);
1496 l_name VARCHAR2(200);
1497 lv_wf_item_key NUMBER;
1498
1499 CURSOR c_get_budget_info_csr (cp_pool_budget_id okl_subsidy_pool_budgets_b.id%TYPE) IS
1500 SELECT sub.subsidy_pool_name
1501 ,sub.id subsidy_pool_id
1502 ,sub.effective_from_date pool_start_date
1503 ,sub.effective_to_date
1504 ,sub.total_budgets
1505 ,bud.budget_type_code
1506 ,bud.effective_from_date budget_line_date
1507 ,bud.budget_amount
1508 ,sub.currency_code
1509 FROM okl_subsidy_pools_b sub
1510 ,okl_subsidy_pool_budgets_b bud
1511 WHERE bud.subsidy_pool_id = sub.id
1512 AND bud.id = cp_pool_budget_id;
1513 cv_get_budget_info c_get_budget_info_csr%ROWTYPE;
1514
1515 -- Get the valid application id from FND
1516 CURSOR c_get_app_id_csr
1517 IS
1518 SELECT APPLICATION_ID
1519 FROM FND_APPLICATION
1520 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
1521
1522 -- Get the Transaction Type Id from OAM
1523 CURSOR c_get_trx_type_csr(cp_trx_type VARCHAR2) IS
1524 SELECT transaction_type_id,
1525 fnd_application_id
1526 FROM ame_transaction_types_v
1527 WHERE transaction_type_id = cp_trx_type;
1528 c_get_trx_type_csr_rec c_get_trx_type_csr%ROWTYPE;
1529
1530 CURSOR l_trx_try_csr IS
1531 SELECT id
1532 FROM okl_trx_types_b
1533 WHERE trx_type_class = G_TRX_TYPE_POOL_APPROVAL;
1534
1535 l_application_id fnd_application.application_id%TYPE;
1536 l_trans_appl_id ame_calling_apps.application_id%TYPE;
1537 l_trans_type_id ame_calling_apps.transaction_type_id%TYPE;
1538
1539 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_WF.RAISE_BUDGET_EVENT_APPROVAL';
1540 l_debug_enabled VARCHAR2(10);
1541 is_debug_procedure_on BOOLEAN;
1542 is_debug_statement_on BOOLEAN;
1543 BEGIN
1544 l_debug_enabled := okl_debug_pub.check_log_enabled;
1545 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1546 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1547 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBWFB.pls call raise_budget_event_approval');
1548 END IF;
1549 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1550 -- Call start_activity to create savepoint, check compatibility and initialize message list
1551 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1552 p_init_msg_list,
1553 '_PVT',
1554 x_return_status);
1555 -- Check if activity started successfully
1556 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1557 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1558 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1559 RAISE OKL_API.G_EXCEPTION_ERROR;
1560 END IF;
1561
1562 -- check for logging on STATEMENT level
1563 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1564
1565 l_get_agent(p_user_id => fnd_global.user_id
1566 ,x_return_status => x_return_status
1567 ,x_name => l_requester
1568 ,x_description => l_name);
1569 -- write to log
1570 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1571 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1572 l_module,
1573 'l_get_agent return staus '||x_return_status||' l_requester '||l_requester||' l_name '||l_name
1574 );
1575 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1576
1577 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1578 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1579 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1580 RAISE OKL_API.G_EXCEPTION_ERROR;
1581 END IF;
1582
1583 l_approval_option := fnd_profile.value('OKL_SUBSIDY_POOL_APPROVAL_PROCESS');
1584 IF(l_approval_option = G_SUBSIDY_POOL_APPROVAL_AME)THEN
1585 -- Get the Application ID
1586 OPEN c_get_app_id_csr;
1587 FETCH c_get_app_id_csr INTO l_application_id;
1588 IF c_get_app_id_csr%NOTFOUND THEN
1589 OKL_API.set_message(p_app_name => G_APP_NAME,
1590 p_msg_name => G_NO_MATCHING_RECORD,
1591 p_token1 => G_COL_NAME_TOKEN,
1592 p_token1_value => 'Application id');
1593 RAISE OKL_API.G_EXCEPTION_ERROR;
1594 END IF;
1595 CLOSE c_get_app_id_csr;
1596
1597 -- Get the Transaction Type ID
1598 OPEN c_get_trx_type_csr(G_TRANS_APP_NAME_LINE);
1599 FETCH c_get_trx_type_csr INTO l_trans_type_id,
1600 l_trans_appl_id;
1601 IF c_get_trx_type_csr%NOTFOUND THEN
1602 OKL_API.set_message(p_app_name => G_APP_NAME,
1603 p_msg_name => G_NO_MATCHING_RECORD,
1604 p_token1 => G_COL_NAME_TOKEN,
1605 p_token1_value => 'AME Transcation TYPE id, Application id');
1606 RAISE OKL_API.G_EXCEPTION_ERROR;
1607 END IF;
1608 CLOSE c_get_trx_type_csr;
1609 IF l_application_id = l_trans_appl_id THEN
1610 wf_event.AddParameterToList(G_WF_ITM_APPLICATION_ID,l_application_id,l_parameter_list);
1611 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_TYPE_ID,l_trans_type_id,l_parameter_list);
1612 END IF;
1613 -- write to log
1614 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1615 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1616 l_module,
1617 'l_approval_option '||l_approval_option||' raising budget line approval event '
1618 );
1619 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1620
1621 ELSIF(l_approval_option = G_SUBSIDY_POOL_APPROVAL_WF)THEN
1622 -- write to log
1623 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1624 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1625 l_module,
1626 'l_approval_option '||l_approval_option||' raising budget line approval event '
1627 );
1628 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1629 ELSE
1630 RAISE OKL_API.G_EXCEPTION_ERROR;
1631 END IF;
1632 IF(l_approval_option IN (G_SUBSIDY_POOL_APPROVAL_WF, G_SUBSIDY_POOL_APPROVAL_AME))THEN
1633 OPEN c_get_budget_info_csr(cp_pool_budget_id => p_subsidy_pool_budget_id);
1634 FETCH c_get_budget_info_csr INTO cv_get_budget_info;
1635 CLOSE c_get_budget_info_csr;
1636
1637 wf_event.AddParameterToList(G_WF_ITM_SUBSIDY_POOL_ID,cv_get_budget_info.subsidy_pool_id,l_parameter_list); -- pool id
1638 wf_event.AddParameterToList(G_WF_ITM_SUBSIDY_POOL_NAME,cv_get_budget_info.subsidy_pool_name,l_parameter_list); -- pool name
1639 wf_event.AddParameterToList(G_WF_ITM_FROM_DATE,cv_get_budget_info.pool_start_date,l_parameter_list); -- pool start date
1640 wf_event.AddParameterToList(G_WF_ITM_TO_DATE,cv_get_budget_info.effective_to_date,l_parameter_list); -- pool end date
1641 wf_event.AddParameterToList(G_WF_ITM_TOTAL_BUDGETS,cv_get_budget_info.total_budgets,l_parameter_list); -- pool total budget
1642
1643 wf_event.AddParameterToList(G_WF_ITM_BUDGET_ID,p_subsidy_pool_budget_id,l_parameter_list); -- budget line id
1644 wf_event.AddParameterToList(G_WF_ITM_BUDGET_AMOUNT,cv_get_budget_info.budget_amount,l_parameter_list); -- budget amount
1645 wf_event.AddParameterToList(G_WF_ITM_BUDGET_FROM_DATE,to_date(cv_get_budget_info.budget_line_date,'DD/MM/RRRR'),l_parameter_list); -- budget line date
1646 wf_event.AddParameterToList(G_WF_ITM_BUDGET_TYPE,cv_get_budget_info.budget_type_code,l_parameter_list); -- type of budget (Addition or Reduction)
1647 wf_event.AddParameterToList(G_WF_ITM_POOL_CURRENCY,cv_get_budget_info.currency_code,l_parameter_list); -- pool currency code
1648
1649 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_ID,p_subsidy_pool_budget_id,l_parameter_list);
1650
1651 wf_event.AddParameterToList(G_WF_ITM_REQUESTOR,l_requester,l_parameter_list);
1652 wf_event.AddParameterToList(G_WF_ITM_REQUESTOR_ID,fnd_global.user_id,l_parameter_list);
1653
1654 lv_wf_item_key := get_item_key_wf;
1655 -- Raise Event
1656 wf_event.RAISE(p_event_name => G_POOL_BUDGET_APPROVAL_EVENT,
1657 p_event_key => lv_wf_item_key,
1658 p_parameters => l_parameter_list);
1659 l_parameter_list.DELETE;
1660 END IF;
1661
1662 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,x_msg_data => x_msg_data);
1663 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1664 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRBWFB.pls call raise_budget_event_approval');
1665 END IF;
1666
1667 EXCEPTION
1668 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1669 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1670 p_api_name => l_api_name,
1671 p_pkg_name => G_PKG_NAME,
1672 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1673 x_msg_count => x_msg_count,
1674 x_msg_data => x_msg_data,
1675 p_api_type => G_API_TYPE);
1676 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1677 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1678 p_api_name => l_api_name,
1679 p_pkg_name => G_PKG_NAME,
1680 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1681 x_msg_count => x_msg_count,
1682 x_msg_data => x_msg_data,
1683 p_api_type => G_API_TYPE);
1684 WHEN OTHERS THEN
1685 -- store SQL error message on message stack
1686 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1687 p_api_name => l_api_name,
1688 p_pkg_name => G_PKG_NAME,
1689 p_exc_name => 'OTHERS',
1690 x_msg_count => x_msg_count,
1691 x_msg_data => x_msg_data,
1692 p_api_type => G_API_TYPE);
1693 END raise_budget_event_approval;
1694
1695 END okl_subsidy_pool_wf;