[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_PA_WF
Source
1 PACKAGE BODY okl_vp_pa_wf AS
2 /* $Header: OKLRPAWB.pls 120.1 2006/07/21 13:13:18 akrangan 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
7 G_PROGRAM_AGRMNT_EVENT CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.la.vp.approve_program_agreement';
8
9 G_AGRMNT_APPROVAL_WF CONSTANT VARCHAR2(2) DEFAULT 'WF';
10 G_AGRMNT_APPROVAL_AME CONSTANT VARCHAR2(3) DEFAULT 'AME';
11
12 G_ITEM_TYPE_WF CONSTANT VARCHAR2(10) DEFAULT 'OKLPAAPP';
13 G_ITEM_TYPE_AME CONSTANT VARCHAR2(10) DEFAULT 'OKLAMAPP';
14
15 G_TRANS_APP_NAME_PA CONSTANT ame_calling_apps.application_name%TYPE DEFAULT 'OKL LA Program Agreement Approval';
16
17 G_WF_ITM_AGREEMENT_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'AGREEMENT_ID';
18 G_WF_ITM_AGREEMENT_NUMBER CONSTANT wf_item_attributes.name%TYPE DEFAULT 'AGREEMENT_NUMBER';
19
20 G_WF_ITM_APP_REQUEST_SUB CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REQUEST_SUB';
21 G_WF_ITM_APP_REMINDER_SUB CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REMINDER_SUB';
22 G_WF_ITM_APP_APPROVED_SUB CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_APPROVED_SUB';
23 G_WF_ITM_APP_REJECTED_SUB CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REJECTED_SUB';
24 G_WF_ITM_APP_REMINDER_HEAD CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REMINDER_HEAD';
25 G_WF_ITM_APP_APPROVED_HEAD CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_APPROVED_HEAD';
26 G_WF_ITM_APP_REJECTED_HEAD CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APP_REJECTED_HEAD';
27
28 G_WF_ITM_REQUESTER CONSTANT wf_item_attributes.name%TYPE DEFAULT 'REQUESTER';
29 G_WF_ITM_REQUESTER_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'REQUESTER_ID';
30 G_WF_ITM_APPROVER CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVER';
31
32 G_WF_ITM_MESSAGE_SUBJECT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_SUBJECT';
33 G_WF_ITM_MESSAGE_DESCR CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DESCRIPTION';
34 G_WF_ITM_MESSAGE_BODY CONSTANT wf_item_attributes.name%TYPE DEFAULT 'MESSAGE_DOC';
35 G_WF_ITM_RESULT CONSTANT wf_item_attributes.name%TYPE DEFAULT 'RESULT';
36 G_WF_ITM_APPROVED_YN_YES CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPROVED';
37 G_WF_ITM_APPROVED_YN_NO CONSTANT wf_item_attributes.name%TYPE DEFAULT 'REJECTED';
38
39 G_WF_ITM_APPLICATION_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'APPLICATION_ID';
40 G_WF_ITM_TRANSACTION_TYPE_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'TRX_TYPE_ID';
41 G_WF_ITM_TRANSACTION_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'TRANSACTION_ID';
42
43 G_DEFAULT_USER CONSTANT VARCHAR2(10) := 'SYSADMIN';
44 G_DEFAULT_USER_DESC CONSTANT VARCHAR2(30) := 'System Administrator';
45 G_WF_USER_ORIG_SYSTEM_HR CONSTANT VARCHAR2(5) := 'PER';
46
47 G_AMP_SIGN CONSTANT VARCHAR2(1) := '&';
48
49 G_DECLINED_STS_CODE CONSTANT okc_statuses_b.code%TYPE DEFAULT 'DECLINED';
50 G_ACTIVE_STS_CODE CONSTANT okc_statuses_b.code%TYPE DEFAULT 'ACTIVE';
51
52 -- local procedure. START
53
54 -- l_get_agent finds the current user who has submitted the requisition in wf_roles,
55 -- if not found, the notification is sent to sysadmin
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(cp_user_id NUMBER)IS
68 SELECT user_name, description
69 FROM fnd_user
70 WHERE user_id = cp_user_id;
71 BEGIN
72 x_return_status := OKL_API.G_RET_STS_SUCCESS;
73 OPEN wf_users_csr(p_user_id);
74 FETCH wf_users_csr INTO x_name, x_description;
75 CLOSE wf_users_csr;
76 IF x_name IS NULL THEN
77 OPEN fnd_users_csr(p_user_id);
78 FETCH fnd_users_csr INTO x_name, x_description;
79 CLOSE fnd_users_csr;
80 IF x_name IS NULL THEN
81 x_name := G_DEFAULT_USER;
82 x_description := G_DEFAULT_USER_DESC;
83 END IF;
84 END IF;
85 EXCEPTION
86 WHEN OTHERS THEN
87 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
88 END l_get_agent;
89
90 -- generates unique value for event key
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 RETURN lv_item_key;
101 END get_item_key_wf;
102
103 PROCEDURE set_attrib_message(itemtype IN VARCHAR2
104 ,itemkey IN VARCHAR2
105 ,message fnd_new_messages.message_name%TYPE
106 ,attrib wf_item_attributes.name%TYPE
107 ) IS
108 lv_agreement_number okc_k_headers_b.contract_number%TYPE;
109 BEGIN
110 -- construct message text for the purpose of subject line of the notification,
111 -- the token is set to AGR_NUMBER for both operating agreement and program agreement
112 lv_agreement_number := wf_engine.GetItemAttrText(itemtype => itemtype,
113 itemkey => itemkey,
114 aname => G_WF_ITM_AGREEMENT_NUMBER);
115
116 fnd_message.set_name(G_APP_NAME,message);
117 fnd_message.set_token('AGR_NUMBER', lv_agreement_number);
118
119 wf_engine.SetItemAttrText(itemtype => itemtype,
120 itemkey => itemkey,
121 aname => attrib,
122 avalue => fnd_message.get
123 );
124
125 END set_attrib_message;
126
127 FUNCTION compile_message_body(p_chr_id IN okc_k_headers_b.id%TYPE) RETURN VARCHAR2 IS
128 CURSOR c_get_agrmnt_details_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
129 SELECT agreement.contract_number agreement_number
130 ,subclass.meaning agreement_category
131 ,vendors.vendor_name vendor_name
132 ,agreement.id agreement_id
133 ,agreement.description
134 ,agreement.start_date
135 ,agreement.end_date
136 FROM okc_k_headers_v agreement
137 ,okc_subclasses_v subclass
138 ,po_vendors vendors
139 ,okc_k_party_roles_b party_roles
140 WHERE agreement.scs_code = subclass.code
141 AND party_roles.dnz_chr_id (+) = agreement.id
142 AND party_roles.rle_code (+) = 'OKL_VENDOR'
143 AND party_roles.object1_id1 = vendors.vendor_id(+)
144 AND agreement.id = cp_chr_id;
145 cv_get_agrmnt_details c_get_agrmnt_details_csr%ROWTYPE;
146
147 lv_message VARCHAR2(4000);
148 BEGIN
149 -- construct message body
150 -- message body looks like
151 --
152 -- Agreement Number <Value> Vendor Name <Value>
153 -- Start Date <Value> Category <Value>
154 -- End Date <Value> Description <Value>
155 --
156 OPEN c_get_agrmnt_details_csr(p_chr_id); FETCH c_get_agrmnt_details_csr INTO cv_get_agrmnt_details;
157 CLOSE c_get_agrmnt_details_csr;
158
159 lv_message:= '<TABLE width="100%" border="0" cellspacing="0" cellpadding="0">'||
160 '<tr><td colspan=0>'||G_AMP_SIGN||'nbsp;</td></tr>'||
161 -- first row containing Agreement Number and Vendor Name
162 '<tr><td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_AGREEMENT_SEARCH'
163 ,p_attribute_code => 'OKL_VP_AGREEMENT_NUMBER')
164 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>' || cv_get_agrmnt_details.agreement_number
165 ||'</b></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||'<td></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||
166 '<td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_AGREEMENT_SEARCH'
167 ,p_attribute_code => 'OKL_VENDOR_NAME')
168 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>' || cv_get_agrmnt_details.vendor_name
169 ||'</b></td>'||
170 '</tr>'||
171 -- second row containing Start Date and Category
172 '<tr><td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_QUOTE_DTLS'
173 ,p_attribute_code => 'OKL_START_DATE')
174 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>'|| cv_get_agrmnt_details.start_date
175 ||'</b></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||'<td></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||
176 '<td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_AGREEMENT_SEARCH'
177 ,p_attribute_code => 'OKL_VP_CATEGORY')
178 ||'</td><td>'|| G_AMP_SIGN||'nbsp;<b>'|| cv_get_agrmnt_details.agreement_category
179 ||'</b></td>'||
180 '</tr>'||
181 -- third row containing End Date and Description
182 '<tr><td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_BP_INV_FORMAT'
183 ,p_attribute_code => 'OKL_END_DATE')
184 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>'|| cv_get_agrmnt_details.end_date
185 ||'</b></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||'<td></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||
186 '<td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_PROGRAM_UPDATE'
187 ,p_attribute_code => 'OKL_DESCRIPTION')
188 ||'</td><td>'||G_AMP_SIGN ||'nbsp;<b>'|| cv_get_agrmnt_details.description
189 ||'</b></td>'||
190 '</tr>'||
191 '</TABLE>';
192
193 RETURN lv_message;
194 END compile_message_body;
195
196 PROCEDURE get_msg_doc(document_id IN VARCHAR2,
197 display_type IN VARCHAR2,
198 document IN OUT nocopy VARCHAR2,
199 document_type IN OUT nocopy VARCHAR2) IS
200 lv_agreement_id okc_k_headers_b.id%TYPE;
201 BEGIN
202 lv_agreement_id := wf_engine.GetItemAttrText(itemtype => G_ITEM_TYPE_WF
203 ,itemkey => document_id
204 ,aname => G_WF_ITM_AGREEMENT_ID);
205 document := compile_message_body(lv_agreement_id);
206 document_type := display_type;
207 END get_msg_doc;
208 -- local procedure. END
209
210 PROCEDURE raise_pa_event_approval(p_api_version IN NUMBER
211 ,p_init_msg_list IN VARCHAR2
212 ,x_return_status OUT NOCOPY VARCHAR2
213 ,x_msg_count OUT NOCOPY NUMBER
214 ,x_msg_data OUT NOCOPY VARCHAR2
215 ,p_chr_id IN okc_k_headers_b.id%TYPE
216 ) IS
217 CURSOR c_get_pa_num_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
218 SELECT id
219 ,contract_number
220 FROM okc_k_headers_b
221 WHERE id = cp_chr_id;
222 cv_get_pa_num c_get_pa_num_csr%ROWTYPE;
223
224 -- Get the valid application id from FND
225 CURSOR c_get_app_id_csr IS
226 SELECT APPLICATION_ID
227 FROM FND_APPLICATION
228 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
229
230 -- Get the Transaction Type Id from OAM
231 CURSOR c_get_trx_type_csr(cp_trx_type VARCHAR2) IS
232 SELECT transaction_type_id,
233 fnd_application_id
234 FROM AME_CALLING_APPS
235 WHERE application_name = cp_trx_type;
236 c_get_trx_type_csr_rec c_get_trx_type_csr%ROWTYPE;
237
238 l_parameter_list wf_parameter_list_t;
239 lv_wf_item_key NUMBER;
240
241 l_requester VARCHAR2(200);
242 l_name VARCHAR2(200);
243
244 l_application_id fnd_application.application_id%TYPE;
245 l_trans_appl_id ame_calling_apps.application_id%TYPE;
246 l_trans_type_id ame_calling_apps.transaction_type_id%TYPE;
247
248 l_api_version CONSTANT NUMBER DEFAULT 1.0;
249 l_approval_process fnd_lookups.lookup_code%TYPE;
250 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SUBMIT_pa_FOR_APPROVAL';
251 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_PA_WF.RAISE_PA_EVENT_APPROVAL';
252 l_debug_enabled VARCHAR2(10);
253
254 BEGIN
255 x_return_status := OKL_API.G_RET_STS_SUCCESS;
256
257 l_debug_enabled := okl_debug_pub.check_log_enabled;
258
259 IF(l_debug_enabled='Y') THEN
260 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRXXXB.pls call raise_pa_event_approval');
261 END IF;
262
263 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
264 x_return_status := OKL_API.START_ACTIVITY(
265 p_api_name => l_api_name
266 ,p_pkg_name => G_PKG_NAME
267 ,p_init_msg_list => p_init_msg_list
268 ,l_api_version => l_api_version
269 ,p_api_version => p_api_version
270 ,p_api_type => g_api_type
271 ,x_return_status => x_return_status);
272 -- check if activity started successfully
273 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
274 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
275 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
276 RAISE OKL_API.G_EXCEPTION_ERROR;
277 END IF;
278
279 l_get_agent(p_user_id => fnd_global.user_id
280 ,x_return_status => x_return_status
281 ,x_name => l_requester
282 ,x_description => l_name);
283 -- write to log
284 IF(NVL(l_debug_enabled,'N')='Y') THEN
285 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
286 l_module,
287 'l_get_agent return staus '||x_return_status||' l_requester '||l_requester||' l_name '||l_name
288 );
289 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
290
291 -- read the profile OKL: Program Agreement Approval Process
292 l_approval_process := fnd_profile.value('OKL_VP_PA_APPROVAL_PROCESS');
293 IF(NVL(l_approval_process,'NONE') = G_AGRMNT_APPROVAL_AME)THEN
294 -- for AME approvals, put the application_id and transaction_type_id as event parameters
295 -- Get the Application ID
296 OPEN c_get_app_id_csr;
297 FETCH c_get_app_id_csr INTO l_application_id;
298 IF c_get_app_id_csr%NOTFOUND THEN
299 OKL_API.set_message(p_app_name => G_APP_NAME,
300 p_msg_name => G_NO_MATCHING_RECORD,
301 p_token1 => G_COL_NAME_TOKEN,
302 p_token1_value => 'Application id');
303 RAISE OKL_API.G_EXCEPTION_ERROR;
304 END IF;
305 CLOSE c_get_app_id_csr;
306
307 -- Get the Transaction Type ID
308 OPEN c_get_trx_type_csr(G_TRANS_APP_NAME_PA);
309 FETCH c_get_trx_type_csr INTO l_trans_type_id, l_trans_appl_id;
310 IF c_get_trx_type_csr%NOTFOUND THEN
311 OKL_API.set_message(p_app_name => G_APP_NAME,
312 p_msg_name => G_NO_MATCHING_RECORD,
313 p_token1 => G_COL_NAME_TOKEN,
314 p_token1_value => 'AME Transcation TYPE id, Application id');
315 RAISE OKL_API.G_EXCEPTION_ERROR;
316 END IF;
317 CLOSE c_get_trx_type_csr;
318 IF(l_application_id = l_trans_appl_id)THEN
319 wf_event.AddParameterToList(G_WF_ITM_APPLICATION_ID,l_application_id,l_parameter_list);
320 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_TYPE_ID,l_trans_type_id,l_parameter_list);
321 END IF;
322 ELSIF(NVL(l_approval_process,'NONE') = G_AGRMNT_APPROVAL_WF)THEN
323 -- log here. no action required. for common event parameters, see below
324 -- write to log
325 IF(NVL(l_debug_enabled,'N')='Y') THEN
326 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
327 l_module,
328 'l_approval_process '||l_approval_process||' raising program agreement approval event'
329 );
330 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
331 END IF; -- end of NVL(l_approval_process,'NONE')='AME'
332
333 -- commmon event parameters here,
334 IF(l_approval_process IN (G_AGRMNT_APPROVAL_WF, G_AGRMNT_APPROVAL_AME))THEN
335 OPEN c_get_pa_num_csr(p_chr_id); FETCH c_get_pa_num_csr INTO cv_get_pa_num;
336 CLOSE c_get_pa_num_csr;
337
338 -- get the agreement information to put as event parameters and raise the event
339 wf_event.AddParameterToList(G_WF_ITM_AGREEMENT_ID, cv_get_pa_num.id, l_parameter_list);
340 wf_event.AddParameterToList(G_WF_ITM_AGREEMENT_NUMBER, cv_get_pa_num.contract_number, l_parameter_list);
341
342 wf_event.AddParameterToList(G_WF_ITM_REQUESTER, l_requester, l_parameter_list);
343 wf_event.AddParameterToList(G_WF_ITM_REQUESTER_ID,fnd_global.user_id,l_parameter_list);
344
345 -- transaction id is the contract id (okc_k_headers_b.id) this parameter is required to write custom queries in AME
346 -- and use them in conditions and rules
347 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_ID,cv_get_pa_num.id,l_parameter_list);
348 --added by akrangan
349 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
350
351 lv_wf_item_key := get_item_key_wf;
352 -- Raise Event
353 wf_event.RAISE(p_event_name => G_PROGRAM_AGRMNT_EVENT,
354 p_event_key => lv_wf_item_key,
355 p_parameters => l_parameter_list);
356
357 l_parameter_list.DELETE;
358 END IF;
359
360 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
361
362 IF(l_debug_enabled='Y') THEN
363 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRXXXB.pls call raise_pa_event_approval');
364 END IF;
365
366 EXCEPTION
367 WHEN OKL_API.G_EXCEPTION_ERROR THEN
368 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
369 p_api_name => l_api_name,
370 p_pkg_name => G_PKG_NAME,
371 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
372 x_msg_count => x_msg_count,
373 x_msg_data => x_msg_data,
374 p_api_type => g_api_type);
375
376 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
377 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
378 p_api_name => l_api_name,
379 p_pkg_name => G_PKG_NAME,
380 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
381 x_msg_count => x_msg_count,
382 x_msg_data => x_msg_data,
383 p_api_type => g_api_type);
384
385 WHEN OTHERS THEN
386 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
387 p_api_name => l_api_name,
388 p_pkg_name => G_PKG_NAME,
389 p_exc_name => 'OTHERS',
390 x_msg_count => x_msg_count,
391 x_msg_data => x_msg_data,
392 p_api_type => g_api_type);
393
394 END raise_pa_event_approval;
395
396 PROCEDURE check_approval_process(itemtype IN VARCHAR2
397 ,itemkey IN VARCHAR2
398 ,actid IN NUMBER
399 ,funcmode IN VARCHAR2
400 ,resultout OUT NOCOPY VARCHAR2) IS
401 l_approval_option VARCHAR2(10);
402 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'check_approval_process';
403 BEGIN
404 IF(funcmode = 'RUN')THEN
405 l_approval_option := fnd_profile.value('OKL_VP_PA_APPROVAL_PROCESS');
406 IF l_approval_option = 'AME' THEN
407 resultout := 'COMPLETE:AME';
408 ELSIF l_approval_option = 'WF' THEN
409 resultout := 'COMPLETE:WF';
410 END IF;
411 RETURN;
412 END IF;
413
414 -- CANCEL mode
415 IF (funcmode = 'CANCEL') THEN
416 resultout := 'COMPLETE:';
417 RETURN;
418 END IF;
419
420 -- TIMEOUT mode
421 IF (funcmode = 'TIMEOUT') THEN
422 resultout := 'COMPLETE:';
423 RETURN;
424 END IF;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
429 RAISE;
430 END check_approval_process;
431
432 PROCEDURE get_agrmnt_approver(itemtype IN VARCHAR2
433 ,itemkey IN VARCHAR2
434 ,actid IN NUMBER
435 ,funcmode IN VARCHAR2
436 ,resultout OUT NOCOPY VARCHAR2) IS
437 CURSOR l_fnd_users_csr(p_user_id NUMBER)
438 IS
439 SELECT USER_NAME
440 FROM FND_USER
441 WHERE user_id = p_user_id;
442
443 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'get_agrmnt_approver';
444
445 l_user_id VARCHAR2(200);
446 lv_requestor VARCHAR2(100);
447
448 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_PA_WF.GET_AGRMNT_APPROVER';
449 l_debug_enabled VARCHAR2(10);
450
451 BEGIN
452 l_debug_enabled := okl_debug_pub.check_log_enabled;
453 IF(l_debug_enabled='Y') THEN
454 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug call get_agrmnt_approver');
455 END IF;
456
457 IF(funcmode = 'RUN')THEN
458 l_user_id := wf_engine.GetItemAttrText(itemtype => itemtype,
459 itemkey => itemkey,
460 aname => G_WF_ITM_REQUESTER_ID);
461
462 resultout := 'COMPLETE:N'; -- default
463 IF l_user_id IS NOT NULL THEN
464 FOR l_fnd_users_rec IN l_fnd_users_csr(l_user_id) LOOP
465 wf_engine.SetItemAttrText (itemtype => itemtype,
466 itemkey => itemkey,
467 aname => G_WF_ITM_APPROVER,
468 avalue => l_fnd_users_rec.user_name);
469
470 -- Message: "Program Agreement <AGR_NUMBER> requires approval"
471 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_APPROVAL_SUB', G_WF_ITM_MESSAGE_SUBJECT);
472
473 wf_engine.SetItemAttrText (itemtype => itemtype,
474 itemkey => itemkey,
475 aname => G_WF_ITM_MESSAGE_BODY,
476 avalue => 'plsql:okl_vp_pa_wf.get_msg_doc/'||itemkey
477 );
478 resultout := 'COMPLETE:Y';
479 END LOOP;
480 END IF; -- l_user_id
481 -- CANCEL mode
482 ELSIF (funcmode = 'CANCEL') THEN
483 resultout := 'COMPLETE:';
484 RETURN;
485 -- TIMEOUT mode
486 ELSIF (funcmode = 'TIMEOUT') THEN
487 resultout := 'COMPLETE:';
488 RETURN;
489 END IF; -- funcmode
490
491 IF(l_debug_enabled='Y') THEN
492 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug call get_agrmnt_approver');
493 END IF;
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
498 RAISE;
499 END get_agrmnt_approver;
500
501 PROCEDURE update_agrmnt_status(itemtype IN VARCHAR2
502 ,itemkey IN VARCHAR2
503 ,actid IN NUMBER
504 ,funcmode IN VARCHAR2
505 ,resultout OUT NOCOPY VARCHAR2) IS
506 x_return_status VARCHAR2(10);
507 x_msg_data VARCHAR2(1000);
508 x_msg_count NUMBER;
509 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'update_agrmnt_status';
510 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_PA_WF.UPDATE_AGRMNT_STATUS';
511 l_debug_enabled VARCHAR2(10);
512 lv_approval_status VARCHAR2(10);
513 lv_approval_status_ame VARCHAR2(10);
514 lv_agreement_id okc_k_headers_b.id%TYPE;
515 BEGIN
516 l_debug_enabled := okl_debug_pub.check_log_enabled;
517 IF(l_debug_enabled='Y') THEN
518 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug update_agrmnt_status');
519 END IF;
520
521 IF(funcmode = 'RUN')THEN
522 lv_approval_status := wf_engine.GetItemAttrText(itemtype => itemtype,
523 itemkey => itemkey,
524 aname => G_WF_ITM_RESULT);
525
526 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
527 itemkey => itemkey,
528 aname => 'APPROVED_YN');
529 lv_agreement_id := wf_engine.GetItemAttrText(itemtype => itemtype,
530 itemkey => itemkey,
531 aname => G_WF_ITM_AGREEMENT_ID);
532
533 IF(G_WF_ITM_APPROVED_YN_YES = lv_approval_status OR lv_approval_status_ame = 'Y')THEN
534 okl_contract_status_pub.update_contract_status(p_api_version => '1.0'
535 ,p_init_msg_list => OKL_API.G_TRUE
536 ,x_return_status => x_return_status
537 ,x_msg_count => x_msg_count
538 ,x_msg_data => x_msg_data
539 ,p_khr_status => G_ACTIVE_STS_CODE
540 ,p_chr_id => lv_agreement_id
541 );
542 -- write to log
543 IF(NVL(l_debug_enabled,'N')='Y') THEN
544 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
545 l_module,
546 'okl_contract_status_pub.update_contract_status G_ACTIVE_STS_CODE returned with status '||x_return_status||' x_msg_data '||x_msg_data
547 );
548 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
549 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
550 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
551 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
552 RAISE OKL_API.G_EXCEPTION_ERROR;
553 END IF;
554 ELSIF(G_WF_ITM_APPROVED_YN_NO = lv_approval_status OR lv_approval_status_ame = 'N')THEN
555 okl_contract_status_pub.update_contract_status(p_api_version => '1.0'
556 ,p_init_msg_list => OKL_API.G_TRUE
557 ,x_return_status => x_return_status
558 ,x_msg_count => x_msg_count
559 ,x_msg_data => x_msg_data
560 ,p_khr_status => G_DECLINED_STS_CODE
561 ,p_chr_id => lv_agreement_id
562 );
563 -- write to log
564 IF(NVL(l_debug_enabled,'N')='Y') THEN
565 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
566 l_module,
567 'okl_contract_status_pub.update_contract_status G_DECLINED_STS_CODE returned with status '||x_return_status||' x_msg_data '||x_msg_data
568 );
569 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
570 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
571 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
572 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
573 RAISE OKL_API.G_EXCEPTION_ERROR;
574 END IF;
575 END IF;
576
577 resultout := 'COMPLETE:';
578 RETURN;
579 -- CANCEL mode
580 ELSIF (funcmode = 'CANCEL') THEN
581 resultout := 'COMPLETE:';
582 RETURN;
583 -- TIMEOUT mode
584 ELSIF (funcmode = 'TIMEOUT') THEN
585 resultout := 'COMPLETE:';
586 RETURN;
587 END IF; -- funcmode
588 IF(l_debug_enabled='Y') THEN
589 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug update_agrmnt_status');
590 END IF;
591
592 EXCEPTION
593 WHEN OTHERS THEN
594 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
595 RAISE;
596 END update_agrmnt_status;
597
598 PROCEDURE process_pa_for_ame(itemtype IN VARCHAR2
599 ,itemkey IN VARCHAR2
600 ,actid IN NUMBER
601 ,funcmode IN VARCHAR2
602 ,resultout OUT NOCOPY VARCHAR2) IS
603 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'process_pa_for_ame';
604 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_PA_WF.PROCESS_PA_FOR_AME';
605 l_debug_enabled VARCHAR2(10);
606 lv_agreement_id okc_k_headers_b.id%TYPE;
607 BEGIN
608 IF(funcmode = 'RUN')THEN
609 lv_agreement_id := wf_engine.GetItemAttrText(itemtype => itemtype
610 ,itemkey => itemkey
611 ,aname => G_WF_ITM_AGREEMENT_ID);
612
613 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_APPROVAL_SUB', G_WF_ITM_APP_REQUEST_SUB);
614 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_APPROVAL_REM', G_WF_ITM_APP_REMINDER_SUB);
615 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_APPROVAL_REM', G_WF_ITM_APP_REMINDER_HEAD);
616 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_APPROVED_SUB', G_WF_ITM_APP_APPROVED_SUB);
617 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_APPROVED_SUB', G_WF_ITM_APP_APPROVED_HEAD);
618 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_REJECT_SUB', G_WF_ITM_APP_REJECTED_SUB);
619 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_REJECT_SUB', G_WF_ITM_APP_REJECTED_HEAD);
620
621 wf_engine.SetItemAttrText (itemtype => itemtype
622 ,itemkey => itemkey
623 ,aname => G_WF_ITM_MESSAGE_DESCR
624 ,avalue => compile_message_body(lv_agreement_id)
625 );
626
627 resultout := 'COMPLETE:';
628 RETURN;
629
630 -- CANCEL mode
631 ELSIF (funcmode = 'CANCEL') THEN
632 resultout := 'COMPLETE:';
633 RETURN;
634 -- TIMEOUT mode
635 ELSIF (funcmode = 'TIMEOUT') THEN
636 resultout := 'COMPLETE:';
637 RETURN;
638 END IF; -- funcmode
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
643 RAISE;
644 END process_pa_for_ame;
645
646 PROCEDURE set_msg_attributes (itemtype IN VARCHAR2
647 ,itemkey IN VARCHAR2
648 ,actid IN NUMBER
649 ,funcmode IN VARCHAR2
650 ,resultout OUT NOCOPY VARCHAR2) IS
651 lv_approval_status VARCHAR2(10);
652 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'set_msg_attributes';
653 BEGIN
654 -- RUN mode
655 IF(funcmode = 'RUN')THEN
656 lv_approval_status := wf_engine.GetItemAttrText(itemtype => itemtype
657 ,itemkey => itemkey
658 ,aname => G_WF_ITM_RESULT);
659 IF(G_WF_ITM_APPROVED_YN_YES = lv_approval_status)THEN
660 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_APPROVED_SUB', G_WF_ITM_MESSAGE_SUBJECT);
661 ELSIF(G_WF_ITM_APPROVED_YN_NO = lv_approval_status)THEN
662 set_attrib_message(itemtype, itemkey, 'OKL_VN_PA_REQ_REJECT_SUB', G_WF_ITM_MESSAGE_SUBJECT);
663 END IF;
664 wf_engine.SetItemAttrText (itemtype => itemtype,
665 itemkey => itemkey,
666 aname => G_WF_ITM_MESSAGE_BODY,
667 avalue => 'plsql:okl_vp_pa_wf.get_msg_doc/'||itemkey
668 );
669 resultout := 'COMPLETE:';
670 END IF; -- end of run mode
671
672 -- CANCEL mode
673 IF(funcmode = 'CANCEL')THEN
674 resultout := 'COMPLETE:';
675 RETURN;
676 END IF;
677
678 -- TIMEOUT mode
679 IF(funcmode = 'TIMEOUT')THEN
680 resultout := 'COMPLETE:';
681 RETURN;
682 END IF;
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
687 RAISE;
688 END set_msg_attributes;
689
690 END okl_vp_pa_wf;