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