[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_CR_WF
Source
1 PACKAGE BODY okl_vp_cr_wf AS
2 /* $Header: OKLRCRWB.pls 120.3 2006/09/22 13:45:35 varangan 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_CHANGE_REQUEST_APP_EVENT CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.la.vp.approve_change_request';
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 'OKLCRAPP';
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 Change Request Approval';
16
17 G_WF_ITM_CHANGE_REQUEST_ID CONSTANT wf_item_attributes.name%TYPE DEFAULT 'CHANGE_REQUEST_ID';
18 G_WF_ITM_CHANGE_REQUEST_NUM CONSTANT wf_item_attributes.name%TYPE DEFAULT 'CHANGE_REQUEST_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_PENDING_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'PENDING_APPROVAL';
50 G_ACTIVE_STS_CODE CONSTANT okc_statuses_b.code%TYPE DEFAULT 'ACTIVE';
51 G_COMPLETED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'COMPLETED';
52 G_REJECTED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'REJECTED';
53 G_INCOMPLETE_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'INCOMPLETE';
54 G_AGR_APP_REJECTED_STS_CODE fnd_lookups.lookup_code%TYPE DEFAULT 'APPROVAL_REJECTED';
55 G_APPROVED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'APPROVED';
56 G_ARGREEMENT_TYPE_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'AGREEMENT';
57
58 -- local procedure. START
59 PROCEDURE l_get_agent(p_user_id IN NUMBER,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_name OUT NOCOPY VARCHAR2,
62 x_description OUT NOCOPY VARCHAR2) IS
63
64 CURSOR wf_users_csr(cp_user_id NUMBER)IS
65 SELECT name, display_name
66 FROM wf_users
67 WHERE orig_system_id = p_user_id
68 AND orig_system = G_WF_USER_ORIG_SYSTEM_HR;
69
70 CURSOR fnd_users_csr(cp_user_id NUMBER)IS
71 SELECT user_name, description
72 FROM fnd_user
73 WHERE user_id = cp_user_id;
74 BEGIN
75 x_return_status := OKL_API.G_RET_STS_SUCCESS;
76 OPEN wf_users_csr(p_user_id);
77 FETCH wf_users_csr INTO x_name, x_description;
78 CLOSE wf_users_csr;
79 IF x_name IS NULL THEN
80 OPEN fnd_users_csr(p_user_id);
81 FETCH fnd_users_csr INTO x_name, x_description;
82 CLOSE fnd_users_csr;
83 IF x_name IS NULL THEN
84 x_name := G_DEFAULT_USER;
85 x_description := G_DEFAULT_USER_DESC;
86 END IF;
87 END IF;
88 EXCEPTION
89 WHEN OTHERS THEN
90 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
91 END l_get_agent;
92
93 -- generates unique value for event key
94 FUNCTION get_item_key_wf RETURN NUMBER IS
95 CURSOR c_wf_item_key_csr IS
96 SELECT okl_wf_item_s.NEXTVAL item_key
97 FROM dual;
98
99 lv_item_key NUMBER;
100 BEGIN
101 OPEN c_wf_item_key_csr; FETCH c_wf_item_key_csr INTO lv_item_key;
102 CLOSE c_wf_item_key_csr;
103 RETURN lv_item_key;
104 END get_item_key_wf;
105
106 PROCEDURE set_attrib_message(itemtype IN VARCHAR2
107 ,itemkey IN VARCHAR2
108 ,message fnd_new_messages.message_name%TYPE
109 ,attrib wf_item_attributes.name%TYPE
110 ) IS
111 lv_change_request_num okc_k_headers_b.contract_number%TYPE;
112 BEGIN
113 lv_change_request_num := wf_engine.GetItemAttrText(itemtype => itemtype,
114 itemkey => itemkey,
115 aname => G_WF_ITM_CHANGE_REQUEST_NUM);
116
117 fnd_message.set_name(G_APP_NAME,message);
118 fnd_message.set_token('CHANGE_REQ_NUM', lv_change_request_num);
119
120 wf_engine.SetItemAttrText(itemtype => itemtype,
121 itemkey => itemkey,
122 aname => attrib,
123 avalue => fnd_message.get
124 );
125
126 END set_attrib_message;
127
128 FUNCTION compile_message_body(p_change_request_id IN okl_vp_change_requests.id%TYPE) RETURN VARCHAR2 IS
129 -- cursor that picks up the agreement details
130 -- for an AGREEMENT type of change request, the copy agreement is displayed
131 -- for ASSOCIATION type of change requst, the original agreement is displayed
132 CURSOR c_get_creq_dtls_csr(cp_change_request_id okl_vp_change_requests.id%TYPE)IS
133 -- this portion of select will work for AGREEMENT type of change requests
134 SELECT agreement.contract_number agreement_number
135 ,subclass.meaning agreement_category
136 ,vendors.vendor_name vendor_name
137 ,agreement.description
138 ,agreement.start_date
139 ,agreement.end_date
140 ,change.change_request_number
141 ,lookups.meaning change_request_type
142 FROM okc_k_headers_v agreement
143 ,okc_subclasses_v subclass
144 ,po_vendors vendors
145 ,okc_k_party_roles_b party_roles
146 ,okl_vp_change_requests change
147 ,fnd_lookups lookups
148 ,okl_k_headers khr
149 WHERE agreement.scs_code = subclass.code
150 AND party_roles.dnz_chr_id (+) = agreement.id
151 AND party_roles.rle_code (+) = 'OKL_VENDOR'
152 AND party_roles.object1_id1 = vendors.vendor_id(+)
153 AND khr.id = agreement.id
154 AND khr.crs_id = change.id
155 AND change.change_type_code = 'AGREEMENT'
156 AND change.change_type_code = lookups.lookup_code
157 and lookups.lookup_type = 'OKL_VP_CHANGE_REQUEST_TYPES'
158 AND change.id = p_change_request_id
159 UNION
160 -- this portion of select will work for ASSOCIATION type of change requests
161 SELECT agreement.contract_number agreement_number
162 ,subclass.meaning agreement_category
163 ,vendors.vendor_name vendor_name
164 ,agreement.description
165 ,agreement.start_date
166 ,agreement.end_date
167 ,change.change_request_number
168 ,lookups.meaning change_request_type
169 FROM okc_k_headers_v agreement
170 ,okc_subclasses_v subclass
171 ,po_vendors vendors
172 ,okc_k_party_roles_b party_roles
173 ,okl_vp_change_requests change
174 ,fnd_lookups lookups
175 WHERE agreement.scs_code = subclass.code
176 AND party_roles.dnz_chr_id (+) = agreement.id
177 AND party_roles.rle_code (+) = 'OKL_VENDOR'
178 AND party_roles.object1_id1 = vendors.vendor_id(+)
179 AND agreement.id = change.chr_id
180 AND change.change_type_code = 'ASSOCIATION'
181 AND change.change_type_code = lookups.lookup_code
182 and lookups.lookup_type = 'OKL_VP_CHANGE_REQUEST_TYPES'
183 AND change.id = p_change_request_id;
184 cv_get_creq_dtls c_get_creq_dtls_csr%ROWTYPE;
185
186 lv_message VARCHAR2(4000);
187 BEGIN
188 -- construct message body
189 -- message body looks like
190 --
191 -- Change Request Number <Value> Change Request Type <Value>
192 -- Agreement Number <Value> Vendor Name <Value>
193 -- Start Date <Value> Category <Value>
194 -- End Date <Value> Description <Value>
195 --
196 OPEN c_get_creq_dtls_csr(p_change_request_id); FETCH c_get_creq_dtls_csr INTO cv_get_creq_dtls;
197 CLOSE c_get_creq_dtls_csr;
198
199 lv_message:= '<TABLE width="100%" border="0" cellspacing="0" cellpadding="0">'||
200 '<tr><td colspan=0>'||G_AMP_SIGN||'nbsp;</td></tr>'||
201 -- first row containing Change Request Number and Change Request Type
202 '<tr><td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_CHANGE_REQUESTS'
203 ,p_attribute_code => 'CHANGEREQNUM')
204 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>' || cv_get_creq_dtls.change_request_number
205 ||'</b></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||'<td></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||
206 '<td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_CHANGE_REQUESTS'
207 ,p_attribute_code => 'CHANGEREQTYPE')
208 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>' || cv_get_creq_dtls.change_request_type
209 ||'</b></td>'||
210 '</tr>'||
211 -- second row containing Agreement Number and Vendor Name
212 '<tr><td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_AGREEMENT_SEARCH'
213 ,p_attribute_code => 'OKL_VP_AGREEMENT_NUMBER')
214 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>'|| cv_get_creq_dtls.agreement_number
215 ||'</b></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||'<td></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||
216 '<td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_AGREEMENT_SEARCH'
217 ,p_attribute_code => 'OKL_VENDOR_NAME')
218 ||'</td><td>'|| G_AMP_SIGN||'nbsp;<b>'|| cv_get_creq_dtls.vendor_name
219 ||'</b></td>'||
220 '</tr>'||
221 -- third row containing Start Date and Category
222 '<tr><td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_QUOTE_DTLS'
223 ,p_attribute_code => 'OKL_START_DATE')
224 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>'|| cv_get_creq_dtls.start_date
225 ||'</b></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||'<td></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||
226 '<td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_AGREEMENT_SEARCH'
227 ,p_attribute_code => 'OKL_VP_CATEGORY')
228 ||'</td><td>'||G_AMP_SIGN ||'nbsp;<b>'|| cv_get_creq_dtls.agreement_category
229 ||'</b></td>'||
230 '</tr>'||
231 -- fourth row containing End Date and Decription
232 '<tr><td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_BP_INV_FORMAT'
233 ,p_attribute_code => 'OKL_END_DATE')
234 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>' || cv_get_creq_dtls.end_date
235 ||'</b></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||'<td></td><td>'||G_AMP_SIGN ||'nbsp;</td>'||
236 '<td align="right">'|| Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_VP_PROGRAM_UPDATE'
237 ,p_attribute_code => 'OKL_DESCRIPTION')
238 ||'</td><td>'|| G_AMP_SIGN ||'nbsp;<b>' || cv_get_creq_dtls.description
239 ||'</b></td>'||
240 '</tr>'||
241 '</TABLE>';
242 RETURN lv_message;
243 END compile_message_body;
244
245 PROCEDURE get_msg_doc(document_id IN VARCHAR2,
246 display_type IN VARCHAR2,
247 document IN OUT nocopy VARCHAR2,
248 document_type IN OUT nocopy VARCHAR2) IS
249 lv_change_req_id okl_vp_change_requests.id%TYPE;
250 BEGIN
251 lv_change_req_id := wf_engine.GetItemAttrText(itemtype => G_ITEM_TYPE_WF
252 ,itemkey => document_id
253 ,aname => G_WF_ITM_CHANGE_REQUEST_ID);
254 document := compile_message_body(lv_change_req_id);
255 document_type := display_type;
256 END get_msg_doc;
257
258 -- local procedure. END
259
260 PROCEDURE raise_cr_event_approval(p_api_version IN NUMBER
261 ,p_init_msg_list IN VARCHAR2
262 ,x_return_status OUT NOCOPY VARCHAR2
263 ,x_msg_count OUT NOCOPY NUMBER
264 ,x_msg_data OUT NOCOPY VARCHAR2
265 ,p_vp_crq_id IN okl_vp_change_requests.id%TYPE
266 ) IS
267 CURSOR c_get_cr_num_csr(cp_crq_id okc_k_headers_b.id%TYPE)IS
268 SELECT id
269 ,change_request_number
270 FROM okl_vp_change_requests
271 WHERE id = cp_crq_id;
272 cv_get_cr_num c_get_cr_num_csr%ROWTYPE;
273 -- Get the valid application id from FND
274 CURSOR c_get_app_id_csr IS
275 SELECT APPLICATION_ID
276 FROM FND_APPLICATION
277 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
278
279 -- Get the Transaction Type Id from OAM
280 CURSOR c_get_trx_type_csr(cp_trx_type VARCHAR2) IS
281 -- Updated the query for performance issue bug#5484903
282 select description transaction_type_id,
283 fnd_application_id
284 from AME_TRANSACTION_TYPES_V
285 where transaction_type_id=cp_trx_type;
286
287 /* --commented out for performance issue bug#5484903
288 SELECT transaction_type_id,
289 fnd_application_id
290 FROM AME_CALLING_APPS
291 WHERE application_name = cp_trx_type; */
292 c_get_trx_type_csr_rec c_get_trx_type_csr%ROWTYPE;
293
294 l_parameter_list wf_parameter_list_t;
295 lv_wf_item_key NUMBER;
296
297 l_requester VARCHAR2(200);
298 l_name VARCHAR2(200);
299
300 l_application_id fnd_application.application_id%TYPE;
301 l_trans_appl_id ame_calling_apps.application_id%TYPE;
302 l_trans_type_id ame_calling_apps.transaction_type_id%TYPE;
303
304 l_api_version CONSTANT NUMBER DEFAULT 1.0;
305 l_approval_process fnd_lookups.lookup_code%TYPE;
306 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SUBMIT_pa_FOR_APPROVAL';
307 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CR_WF.RAISE_CR_EVENT_APPROVAL';
308 l_debug_enabled VARCHAR2(10);
309
310 BEGIN
311 x_return_status := OKL_API.G_RET_STS_SUCCESS;
312
313 l_debug_enabled := okl_debug_pub.check_log_enabled;
314
315 IF(l_debug_enabled='Y') THEN
316 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRXXXB.pls call raise_cr_event_approval');
317 END IF;
318
319 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
320 x_return_status := OKL_API.START_ACTIVITY(
321 p_api_name => l_api_name
322 ,p_pkg_name => G_PKG_NAME
323 ,p_init_msg_list => p_init_msg_list
324 ,l_api_version => l_api_version
325 ,p_api_version => p_api_version
326 ,p_api_type => g_api_type
327 ,x_return_status => x_return_status);
328 -- check if activity started successfully
329 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
330 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
331 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
332 RAISE OKL_API.G_EXCEPTION_ERROR;
333 END IF;
334
335 l_get_agent(p_user_id => fnd_global.user_id
336 ,x_return_status => x_return_status
337 ,x_name => l_requester
338 ,x_description => l_name);
339 -- write to log
340 IF(NVL(l_debug_enabled,'N')='Y') THEN
341 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
342 l_module,
343 'l_get_agent return staus '||x_return_status||' l_requester '||l_requester||' l_name '||l_name
344 );
345 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
346
347 -- read the profile OKL: Change Request Agreement Approval Process
348 l_approval_process := fnd_profile.value('OKL_VP_CR_APPROVAL_PROCESS');
349 IF(NVL(l_approval_process,'NONE') = G_AGRMNT_APPROVAL_AME)THEN
350 -- for AME approvals, put the application_id and transaction_type_id as event parameters
351 -- Get the Application ID
352 OPEN c_get_app_id_csr;
353 FETCH c_get_app_id_csr INTO l_application_id;
354 IF c_get_app_id_csr%NOTFOUND THEN
355 OKL_API.set_message(p_app_name => G_APP_NAME,
356 p_msg_name => G_NO_MATCHING_RECORD,
357 p_token1 => G_COL_NAME_TOKEN,
358 p_token1_value => 'Application id');
359 RAISE OKL_API.G_EXCEPTION_ERROR;
360 END IF;
361 CLOSE c_get_app_id_csr;
362
363 -- Get the Transaction Type ID
364 OPEN c_get_trx_type_csr(G_TRANS_APP_NAME_OA);
365 FETCH c_get_trx_type_csr INTO l_trans_type_id, l_trans_appl_id;
366 IF c_get_trx_type_csr%NOTFOUND THEN
367 OKL_API.set_message(p_app_name => G_APP_NAME,
368 p_msg_name => G_NO_MATCHING_RECORD,
369 p_token1 => G_COL_NAME_TOKEN,
370 p_token1_value => 'AME Transcation TYPE id, Application id');
371 RAISE OKL_API.G_EXCEPTION_ERROR;
372 END IF;
373 CLOSE c_get_trx_type_csr;
374 IF(l_application_id = l_trans_appl_id)THEN
375 wf_event.AddParameterToList(G_WF_ITM_APPLICATION_ID,l_application_id,l_parameter_list);
376 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_TYPE_ID,l_trans_type_id,l_parameter_list);
377 END IF;
378 ELSIF(NVL(l_approval_process,'NONE') = G_AGRMNT_APPROVAL_WF)THEN
379 -- log here. no action required. for common event parameters, see below
380 -- write to log
381 IF(NVL(l_debug_enabled,'N')='Y') THEN
382 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
383 l_module,
384 'l_approval_process '||l_approval_process||' raising change request approval event'
385 );
386 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
387 END IF; -- end of NVL(l_approval_process,'NONE')='AME'
388
389 -- commmon event parameters here,
390 IF(l_approval_process IN (G_AGRMNT_APPROVAL_WF, G_AGRMNT_APPROVAL_AME))THEN
391 OPEN c_get_cr_num_csr(p_vp_crq_id); FETCH c_get_cr_num_csr INTO cv_get_cr_num;
392 CLOSE c_get_cr_num_csr;
393
394 -- get the agreement information to put as event parameters and raise the event
395 wf_event.AddParameterToList(G_WF_ITM_CHANGE_REQUEST_ID, cv_get_cr_num.id, l_parameter_list);
396 wf_event.AddParameterToList(G_WF_ITM_CHANGE_REQUEST_NUM, cv_get_cr_num.change_request_number, l_parameter_list);
397
398 wf_event.AddParameterToList(G_WF_ITM_REQUESTER, l_requester, l_parameter_list);
399 wf_event.AddParameterToList(G_WF_ITM_REQUESTER_ID,fnd_global.user_id,l_parameter_list);
400
401 -- transaction id is the contract id (okc_k_headers_b.id) this parameter is required to write custom queries in AME
402 -- and use them in conditions and rules
403 wf_event.AddParameterToList(G_WF_ITM_TRANSACTION_ID,cv_get_cr_num.id,l_parameter_list);
404 --added by akrangan
405 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
406 lv_wf_item_key := get_item_key_wf;
407 -- Raise Event
408 wf_event.RAISE(p_event_name => G_CHANGE_REQUEST_APP_EVENT,
409 p_event_key => lv_wf_item_key,
410 p_parameters => l_parameter_list);
411
412 l_parameter_list.DELETE;
413 END IF;
414
415 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
416
417 IF(l_debug_enabled='Y') THEN
418 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRXXXB.pls call raise_cr_event_approval');
419 END IF;
420
421 EXCEPTION
422 WHEN OKL_API.G_EXCEPTION_ERROR THEN
423 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
424 p_api_name => l_api_name,
425 p_pkg_name => G_PKG_NAME,
426 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
427 x_msg_count => x_msg_count,
428 x_msg_data => x_msg_data,
429 p_api_type => g_api_type);
430 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
431 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
432 p_api_name => l_api_name,
433 p_pkg_name => G_PKG_NAME,
434 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
435 x_msg_count => x_msg_count,
436 x_msg_data => x_msg_data,
437 p_api_type => g_api_type);
438 WHEN OTHERS THEN
439 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
440 p_api_name => l_api_name,
441 p_pkg_name => G_PKG_NAME,
442 p_exc_name => 'OTHERS',
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data,
445 p_api_type => g_api_type);
446 END raise_cr_event_approval;
447
448
449 PROCEDURE check_approval_process(itemtype IN VARCHAR2
450 ,itemkey IN VARCHAR2
451 ,actid IN NUMBER
452 ,funcmode IN VARCHAR2
453 ,resultout OUT NOCOPY VARCHAR2) IS
454 l_approval_option VARCHAR2(10);
455 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'check_approval_process';
456 BEGIN
457 IF(funcmode = 'RUN')THEN
458 l_approval_option := fnd_profile.value('OKL_VP_CR_APPROVAL_PROCESS');
459 IF l_approval_option = 'AME' THEN
460 resultout := 'COMPLETE:AME';
461 ELSIF l_approval_option = 'WF' THEN
462 resultout := 'COMPLETE:WF';
463 END IF;
464 RETURN;
465 END IF;
466
467 -- CANCEL mode
468 IF (funcmode = 'CANCEL') THEN
469 resultout := 'COMPLETE:';
470 RETURN;
471 END IF;
472
473 -- TIMEOUT mode
474 IF (funcmode = 'TIMEOUT') THEN
475 resultout := 'COMPLETE:';
476 RETURN;
477 END IF;
478
479 EXCEPTION
480 WHEN OTHERS THEN
481 wf_core.context(G_PKG_NAME , l_api_name, itemtype, itemkey, actid, funcmode);
482 RAISE;
483 END check_approval_process;
484
485 PROCEDURE get_cr_approver(itemtype IN VARCHAR2
486 ,itemkey IN VARCHAR2
487 ,actid IN NUMBER
488 ,funcmode IN VARCHAR2
489 ,resultout OUT NOCOPY VARCHAR2) IS
490 CURSOR l_fnd_users_csr(p_user_id NUMBER)
491 IS
492 SELECT USER_NAME
493 FROM FND_USER
494 WHERE user_id = p_user_id;
495
496 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'get_agrmnt_approver';
497
498 l_user_id VARCHAR2(200);
499 lv_requestor VARCHAR2(100);
500
501 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CR_WF.GET_AGRMNT_APPROVER';
502 l_debug_enabled VARCHAR2(10);
503
504 BEGIN
505 l_debug_enabled := okl_debug_pub.check_log_enabled;
506 IF(l_debug_enabled='Y') THEN
507 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug call get_agrmnt_approver');
508 END IF;
509
510 IF(funcmode = 'RUN')THEN
511 l_user_id := wf_engine.GetItemAttrText(itemtype => itemtype,
512 itemkey => itemkey,
513 aname => G_WF_ITM_REQUESTER_ID);
514
515 resultout := 'COMPLETE:N'; -- default
516 IF l_user_id IS NOT NULL THEN
517 FOR l_fnd_users_rec IN l_fnd_users_csr(l_user_id) LOOP
518 wf_engine.SetItemAttrText (itemtype => itemtype,
519 itemkey => itemkey,
520 aname => G_WF_ITM_APPROVER,
521 avalue => l_fnd_users_rec.user_name);
522
523 -- Message: "Change Request <AGR_NUMBER> requires approval"
524 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_APPROVAL_SUB', G_WF_ITM_MESSAGE_SUBJECT);
525
526 wf_engine.SetItemAttrText (itemtype => itemtype,
527 itemkey => itemkey,
528 aname => G_WF_ITM_MESSAGE_BODY,
529 avalue => 'plsql:okl_vp_cr_wf.get_msg_doc/'||itemkey
530 );
531 resultout := 'COMPLETE:Y';
532 END LOOP;
533 END IF; -- l_user_id
534 -- CANCEL mode
535 ELSIF (funcmode = 'CANCEL') THEN
536 resultout := 'COMPLETE:';
537 RETURN;
538 -- TIMEOUT mode
539 ELSIF (funcmode = 'TIMEOUT') THEN
540 resultout := 'COMPLETE:';
541 RETURN;
542 END IF; -- funcmode
543
544 IF(l_debug_enabled='Y') THEN
545 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug call get_agrmnt_approver');
546 END IF;
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
551 RAISE;
552 END get_cr_approver;
553
554 PROCEDURE update_cr_status(itemtype IN VARCHAR2
555 ,itemkey IN VARCHAR2
556 ,actid IN NUMBER
557 ,funcmode IN VARCHAR2
558 ,resultout OUT NOCOPY VARCHAR2) IS
559 -- cursor to get the agreement number that was created for the change request
560 CURSOR c_get_creq_chr_csr(cp_change_request_id okl_vp_change_requests.id%TYPE)IS
561 SELECT CASE WHEN (cr.change_type_code = 'AGREEMENT') THEN
562 (SELECT id FROM okl_k_headers WHERE crs_id = cr.id)
563 ELSE cr.chr_id END creq_chr_id
564 ,cr.change_type_code
565 FROM okl_vp_change_requests cr
566 WHERE cr.id = cp_change_request_id;
567 cv_get_creq_chr c_get_creq_chr_csr%ROWTYPE;
568
569 x_return_status VARCHAR2(10);
570 x_msg_data VARCHAR2(1000);
571 x_msg_count NUMBER;
572 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'update_cr_status';
573 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CR_WF.UPDATE_CR_STATUS';
574 l_debug_enabled VARCHAR2(10);
575 lv_approval_status VARCHAR2(10);
576 lv_approval_status_ame VARCHAR2(10);
577 lv_change_req_id okl_vp_change_requests.id%TYPE;
578 lv_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
579 x_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
580 BEGIN
581 l_debug_enabled := okl_debug_pub.check_log_enabled;
582 IF(l_debug_enabled='Y') THEN
583 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug update_cr_status');
584 END IF;
585
586 IF(funcmode = 'RUN')THEN
587 lv_approval_status := wf_engine.GetItemAttrText(itemtype => itemtype,
588 itemkey => itemkey,
589 aname => G_WF_ITM_RESULT);
590 lv_approval_status_ame := wf_engine.GetItemAttrText(itemtype => itemtype,
591 itemkey => itemkey,
592 aname => 'APPROVED_YN');
593 lv_change_req_id := wf_engine.GetItemAttrText(itemtype => itemtype,
594 itemkey => itemkey,
595 aname => G_WF_ITM_CHANGE_REQUEST_ID);
596
597 -- cursor that fetches the change request type and the agreement associated with the change request
598 OPEN c_get_creq_chr_csr(lv_change_req_id); FETCH c_get_creq_chr_csr INTO cv_get_creq_chr;
599 CLOSE c_get_creq_chr_csr;
600
601 IF(G_WF_ITM_APPROVED_YN_YES = lv_approval_status OR lv_approval_status_ame = 'Y')THEN
602 -- first set the change request and its associated agreement to status APPROVED.
603 -- though the life of the records in this status is only ephemeral, this status is still requried
604 -- for future use. refer to #71 on the vendor programs discussion log
605 okl_vp_change_request_pvt.set_change_request_status(p_api_version => 1.0
606 ,p_init_msg_list => OKL_API.G_TRUE
607 ,x_return_status => x_return_status
608 ,x_msg_count => x_msg_count
609 ,x_msg_data => x_msg_data
610 ,p_vp_crq_id => lv_change_req_id
611 ,p_status_code => G_APPROVED_STS_CODE
612 );
613 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
614 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
615 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
616 RAISE OKL_API.G_EXCEPTION_ERROR;
617 END IF;
618
619 -- now start synchronization, the synchronization api will set the status of the change request
620 -- and the associated agreement accordingly
621 okl_vp_sync_cr_pvt.sync_change_request(p_api_version => 1.0
622 ,p_init_msg_list => OKL_API.G_TRUE
623 ,x_return_status => x_return_status
624 ,x_msg_count => x_msg_count
625 ,x_msg_data => x_msg_data
626 ,p_change_request_id => lv_change_req_id
627 );
628 -- write to log
629 IF(NVL(l_debug_enabled,'N')='Y') THEN
630 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
631 l_module,
632 'okl_vp_sync_cr_pvt.sync_change_request on change request id '||lv_change_req_id||' returned with status '||x_return_status
633 );
634 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
635 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
636 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
637 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
638 RAISE OKL_API.G_EXCEPTION_ERROR;
639 END IF;
640
641 ELSIF(G_WF_ITM_APPROVED_YN_NO = lv_approval_status OR lv_approval_status_ame = 'N')THEN
642 -- set the status of the change request to REJECTED
643 -- the actual agreement should be set back to passed status. from there on the user can update the
644 -- change request and re-submit for approval
645 -- but if in case of an ASSOCIATION type of change request, since there is no copy of the original
646 -- agreement, when the change request is REJECTED
647 lv_vcrv_rec.id := lv_change_req_id;
648 lv_vcrv_rec.chr_id := cv_get_creq_chr.creq_chr_id;
649 lv_vcrv_rec.status_code := G_REJECTED_STS_CODE;
650 lv_vcrv_rec.rejected_date := TRUNC(SYSDATE);
651 okl_vp_change_request_pvt.update_change_request_header(p_api_version => 1.0
652 ,p_init_msg_list => OKL_API.G_TRUE
653 ,x_return_status => x_return_status
654 ,x_msg_count => x_msg_count
655 ,x_msg_data => x_msg_data
656 ,p_vcrv_rec => lv_vcrv_rec
657 ,x_vcrv_rec => x_vcrv_rec
658 );
659 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
660 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
661 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
662 RAISE OKL_API.G_EXCEPTION_ERROR;
663 END IF;
664 -- update the agreement to APPROVAL_REJECTED only in case of AGREEMENT type of change request
665 IF(cv_get_creq_chr.change_type_code = G_ARGREEMENT_TYPE_CODE)THEN
666 okl_contract_status_pub.update_contract_status(p_api_version => 1.0
667 ,p_init_msg_list => OKL_API.G_TRUE
668 ,x_return_status => x_return_status
669 ,x_msg_count => x_msg_count
670 ,x_msg_data => x_msg_data
671 ,p_khr_status => G_AGR_APP_REJECTED_STS_CODE
672 ,p_chr_id => cv_get_creq_chr.creq_chr_id
673 );
674 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
675 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
676 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
677 RAISE OKL_API.G_EXCEPTION_ERROR;
678 END IF;
679 END IF;
680 END IF;
681 resultout := 'COMPLETE:';
682 RETURN;
683 -- CANCEL mode
684 ELSIF (funcmode = 'CANCEL') THEN
685 resultout := 'COMPLETE:';
686 RETURN;
687 -- TIMEOUT mode
688 ELSIF (funcmode = 'TIMEOUT') THEN
689 resultout := 'COMPLETE:';
690 RETURN;
691 END IF; -- funcmode
692 IF(l_debug_enabled='Y') THEN
693 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug update_cr_status');
694 END IF;
695 EXCEPTION
696 WHEN OTHERS THEN
697 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
698 RAISE;
699 END update_cr_status;
700
701 PROCEDURE process_cr_for_ame(itemtype IN VARCHAR2
702 ,itemkey IN VARCHAR2
703 ,actid IN NUMBER
704 ,funcmode IN VARCHAR2
705 ,resultout OUT NOCOPY VARCHAR2) IS
706 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'process_cr_for_ame';
707 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CR_WF.PROCESS_CR_FOR_AME';
708 l_debug_enabled VARCHAR2(10);
709 lv_change_req_id okl_vp_change_requests.id%TYPE;
710 BEGIN
711 IF(funcmode = 'RUN')THEN
712 lv_change_req_id := wf_engine.GetItemAttrText(itemtype => itemtype
713 ,itemkey => itemkey
714 ,aname => G_WF_ITM_CHANGE_REQUEST_ID);
715
716 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_APPROVAL_SUB', G_WF_ITM_APP_REQUEST_SUB);
717 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_APPROVAL_REM', G_WF_ITM_APP_REMINDER_SUB);
718 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_APPROVAL_REM', G_WF_ITM_APP_REMINDER_HEAD);
719 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_APPROVED_SUB', G_WF_ITM_APP_APPROVED_SUB);
720 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_APPROVED_SUB', G_WF_ITM_APP_APPROVED_HEAD);
721 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_REJECT_SUB', G_WF_ITM_APP_REJECTED_SUB);
722 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_REJECT_SUB', G_WF_ITM_APP_REJECTED_HEAD);
723
724 wf_engine.SetItemAttrText (itemtype => itemtype
725 ,itemkey => itemkey
726 ,aname => G_WF_ITM_MESSAGE_DESCR
727 ,avalue => compile_message_body(lv_change_req_id)
728 );
729
730 resultout := 'COMPLETE:';
731 RETURN;
732
733 -- CANCEL mode
734 ELSIF (funcmode = 'CANCEL') THEN
735 resultout := 'COMPLETE:';
736 RETURN;
737 -- TIMEOUT mode
738 ELSIF (funcmode = 'TIMEOUT') THEN
739 resultout := 'COMPLETE:';
740 RETURN;
741 END IF; -- funcmode
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
746 RAISE;
747 END process_cr_for_ame;
748
749 PROCEDURE set_msg_attributes (itemtype IN VARCHAR2
750 ,itemkey IN VARCHAR2
751 ,actid IN NUMBER
752 ,funcmode IN VARCHAR2
753 ,resultout OUT NOCOPY VARCHAR2) IS
754 lv_approval_status VARCHAR2(10);
755 l_api_name CONSTANT VARCHAR2(200) DEFAULT 'set_msg_attributes';
756 BEGIN
757 -- RUN mode
758 IF(funcmode = 'RUN')THEN
759 lv_approval_status := wf_engine.GetItemAttrText(itemtype => itemtype
760 ,itemkey => itemkey
761 ,aname => G_WF_ITM_RESULT);
762 IF(G_WF_ITM_APPROVED_YN_YES = lv_approval_status)THEN
763 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_APPROVED_SUB', G_WF_ITM_MESSAGE_SUBJECT);
764 ELSIF(G_WF_ITM_APPROVED_YN_NO = lv_approval_status)THEN
765 set_attrib_message(itemtype, itemkey, 'OKL_VN_CR_REQ_REJECT_SUB', G_WF_ITM_MESSAGE_SUBJECT);
766 END IF;
767 wf_engine.SetItemAttrText (itemtype => itemtype,
768 itemkey => itemkey,
769 aname => G_WF_ITM_MESSAGE_BODY,
770 avalue => 'plsql:okl_vp_cr_wf.get_msg_doc/'||itemkey
771 );
772 resultout := 'COMPLETE:';
773 END IF; -- end of run mode
774
775 -- CANCEL mode
776 IF(funcmode = 'CANCEL')THEN
777 resultout := 'COMPLETE:';
778 RETURN;
779 END IF;
780
781 -- TIMEOUT mode
782 IF(funcmode = 'TIMEOUT')THEN
783 resultout := 'COMPLETE:';
784 RETURN;
785 END IF;
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,TO_CHAR(actid),funcmode);
790 RAISE;
791 END set_msg_attributes;
792
793 END okl_vp_cr_wf;