[Home] [Help]
PACKAGE BODY: APPS.OZF_REQUEST_STATUS_PVT
Source
1 PACKAGE BODY OZF_REQUEST_STATUS_PVT AS
2 /* $Header: ozfvrstb.pls 120.7.12020000.2 2012/07/13 05:46:49 rvkondur ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_REQUEST_STATUS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvrstb.pls';
6
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9
10 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
11 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
12 OZF_UNEXP_ERROR_ON BOOLEAN :=FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error);
13 OZF_ERROR_ON BOOLEAN := FND_MSG_PUB.check_msg_level(fnd_msg_pub.g_msg_lvl_error);
14 G_DEBUG BOOLEAN := true; --FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
15
16
17 ---------------------------------------------------------------------
18 -- PROCEDURE
19 -- Event_Subscription
20 --
21 -- PURPOSE
22 -- Subscription for the event raised during status change
23 --
24 -- PARAMETERS
25 --
26 -- NOTES
27 ---------------------------------------------------------------------
28 FUNCTION Event_Subscription(
29 p_subscription_guid IN raw,
30 p_event IN OUT NOCOPY wf_event_t)
31 RETURN varchar2
32 is
33
34 l_api_name CONSTANT VARCHAR2(30) := 'Event_Subscription';
35 l_api_version CONSTANT number := 1.0;
36 l_rule varchar2(20);
37 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
38 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
39 l_parameter_name l_parameter_t.name%type;
40 i pls_integer;
41
42 l_msg_callback_api varchar2(60);
43 l_user_callback_api varchar2(60);
44 l_benefit_id number;
45 l_status varchar2(30);
46 l_event_key varchar2(240);
47 l_partner_id number;
48 l_object_type varchar2(30) := 'SPECIAL_PRICE';
49 l_object_id number;
50 l_user_list varchar2(2000);
51 l_msg_count number;
52 l_msg_data varchar2(2000);
53 l_return_status varchar2(10);
54 l_approval_rec OZF_APPROVAL_PVT.approval_rec_type;
55
56 CURSOR csr_benefit (p_object_id in number) IS
57 SELECT benefit_id, partner_id
58 FROM ozf_request_headers_all_b
59 WHERE request_header_id = p_object_id;
60
61 BEGIN
62 -- Standard begin of API savepoint
63 SAVEPOINT Event_Subscription_PVT;
64 -- Debug Message
65 IF G_DEBUG THEN
66 ozf_utility_pvt.debug_message( l_api_name||': Start');
67 END IF;
68
69 l_parameter_list := p_event.getParameterList();
70 l_event_key := p_event.getEventKey();
71
72 IF G_DEBUG THEN
73 ozf_utility_pvt.debug_message( 'Event Key ' || l_event_key);
74 END IF;
75
76 IF l_parameter_list IS NOT NULL THEN
77 i := l_parameter_list.FIRST;
78 WHILE ( i <= l_parameter_list.last) LOOP
79
80 IF G_DEBUG THEN
81 ozf_utility_pvt.debug_message( 'Parameter Name ' || l_parameter_list(i).getName());
82 ozf_utility_pvt.debug_message( 'Parameter Value ' || l_parameter_list(i).getValue());
83 ozf_utility_pvt.debug_message( 'Parameter ' || i || ' of ' || l_parameter_list.last);
84 END IF;
85
86 l_parameter_name := null;
87 l_parameter_name := l_parameter_list(i).getName();
88
89 IF l_parameter_name = 'STATUS_CODE' THEN
90 l_status := l_parameter_list(i).getValue();
91 ELSIF l_parameter_name = 'OBJECT_TYPE' THEN
92 l_object_type := l_parameter_list(i).getValue();
93 ELSIF l_parameter_name = 'OBJECT_ID' THEN
94 l_object_id := l_parameter_list(i).getValue();
95 END IF;
96
97 i := l_parameter_list.next(i);
98 END LOOP;
99
100 OPEN csr_benefit (l_object_id);
101 FETCH csr_benefit INTO l_benefit_id, l_partner_id;
102 CLOSE csr_benefit;
103
104 l_msg_callback_api := 'OZF_REQUEST_STATUS_PVT.Set_Request_Message';
105 l_user_callback_api := 'OZF_REQUEST_STATUS_PVT.Return_Request_Userlist';
106
107 l_approval_rec.object_type := l_object_type;
108 l_approval_rec.object_id := l_object_id;
109 l_approval_rec.status_code := l_status;
110
111 IF G_DEBUG THEN
112 ozf_utility_pvt.debug_message( 'Before call create_interaction. ');
113 END IF;
114
115 -- Create_Interaction History
116 Create_Interaction (
117 p_api_version => l_api_version,
118 p_init_msg_list => FND_API.G_FALSE,
119 x_return_status => l_return_status,
120 x_msg_data => l_msg_data,
121 x_msg_count => l_msg_count,
122 p_approval_rec => l_approval_rec );
123
124 IF l_return_status = FND_API.g_ret_sts_error THEN
125 RAISE FND_API.g_exc_error;
126 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
127 RAISE FND_API.g_exc_unexpected_error;
128 END IF;
129
130 -- Call api to send notification
131 OZF_APPROVAL_PVT.Send_Notification(
132 p_api_version => l_api_version,
133 p_init_msg_list => FND_API.G_FALSE,
134 --p_validation_level => p_validation_level,
135 x_return_status => l_return_status,
136 x_msg_data => l_msg_data,
137 x_msg_count => l_msg_count,
138 p_benefit_id => l_benefit_id,
139 p_partner_id => l_partner_id,
140 p_msg_callback_api => l_msg_callback_api,
141 p_user_callback_api => l_user_callback_api,
142 p_approval_rec => l_approval_rec
143 );
144
145 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
146 RETURN 'ERROR';
147 END IF;
148
149 END IF;
150
151 RETURN 'SUCCESS';
152
153 -- Debug Message
154 IF G_DEBUG THEN
155 ozf_utility_pvt.debug_message( l_api_name||': End');
156 END IF;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 WF_CORE.CONTEXT(G_PKG_NAME, L_API_NAME, P_EVENT.GETEVENTNAME(), P_SUBSCRIPTION_GUID);
161 WF_EVENT.SETERRORINFO(P_EVENT,'ERROR');
162 RETURN 'ERROR';
163 --
164 END Event_Subscription;
165
166 ---------------------------------------------------------------------
167 -- PROCEDURE
168 -- Set_Request_Message
169 --
170 -- PURPOSE
171 -- Handles the approvals and rejections of objects
172 --
173 -- PARAMETERS
174 --
175 -- NOTES
176 --
177 -- HISTORY
178 -- ateotia 01-Jun-2009 Bug# 8208686 fixed.
179 -- Error on click of SPR notification link.
180 ---------------------------------------------------------------------
181 PROCEDURE Set_Request_Message (
182 p_itemtype IN VARCHAR2,
183 p_itemkey IN VARCHAR2,
184 P_ENTITY_ID IN NUMBER,
185 P_USER_TYPE IN VARCHAR2,
186
187 P_STATUS IN VARCHAR2)
188 IS
189 l_api_name CONSTANT VARCHAR2(30) := 'Set_Request_Message';
190 l_request_header_id NUMBER;
191 l_request_number VARCHAR2(50);
192 l_request_name VARCHAR2(100);
193 l_comp_amount VARCHAR2(20);
194 l_partner_org_name VARCHAR2(100);
195 l_partner_cont_name VARCHAR2(100);
196 l_creator_name VARCHAR2(100);
197 l_customer_address VARCHAR2(200);
198 l_customer_name VARCHAR2(100);
199 l_customer_cont_name VARCHAR2(100);
200 l_entity_status VARCHAR2(100);
201 l_entity_creation_date VARCHAR2(30);
202 l_notes_clob CLOB;
203 l_notes_varchar VARCHAR2(4000);
204 l_note_size BINARY_INTEGER := 4000;
205 l_decline_code VARCHAR2(30);
206 l_return_code VARCHAR2(30);
207 l_decline_meaning VARCHAR2(200);
208 l_return_meaning VARCHAR2(200);
209 l_partner_cont_phone VARCHAR2(40);
210 l_partner_cont_email VARCHAR2(2000);
211 l_last_approver_name VARCHAR2(200);
212 l_request_type VARCHAR2(20);
213 l_agreement_number VARCHAR2(30);
214 l_authorization_code VARCHAR2(30);
215 l_start_date DATE;
216 l_end_date DATE;
217 l_activity_name VARCHAR2(80);
218 l_activity_media_id NUMBER;
219 l_vendor_name VARCHAR2(360);
220 l_request_type_code VARCHAR2(30);
221 l_note_type VARCHAR2(20);
222 l_offer_id NUMBER;
223 l_partner_profile_url VARCHAR2(500);
224 l_function_id NUMBER;
225 --Bug# 8208686 fixed by ateotia(+)
226 l_org_id NUMBER;
227 --Maximum URL length is 2,083 characters in IE
228 l_vendor_dtail_url VARCHAR2(2000);
229 l_partner_dtail_url VARCHAR2(2000);
230 l_vendor_url VARCHAR2(2000);
231 l_partner_url VARCHAR2(2000);
232 --Bug# 8208686 fixed by ateotia(-)
233
234 cursor lc_get_request_details (pc_request_id number) is
235 select a.request_header_id
236 , a.request_number
237 , a.request_name
238 , c.party_name
239 , a.end_cust_name
240 , ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(Null,a.end_cust_address1,a.end_cust_address2,
241 a.end_cust_address3,a.end_cust_address4,a.end_cust_city,a.end_cust_county,
242 a.end_cust_state,a.end_cust_province,a.end_cust_postal_code,
243 null,null,Null,Null,Null,Null,Null,FND_PROFILE.Value('ASF_DEFAULT_COUNTRY'),
244 NULL,NULL,2000,1,1) ADDRESS
245 -- BUG 4460277 (+)
246 --, pt_cont.source_first_name || ' ' || pt_cont.source_last_name pt_contact_name
247 , cont.person_last_name||
248 DECODE(cont.person_middle_name, NULL, '', ', '||cont.person_middle_name)||
249 DECODE(cont.person_first_name, NULL, '', ', '||cont.person_first_name) pt_contact_name
250 -- BUG 4460277 (-)
251 , creator.source_first_name || ' ' || creator.source_last_name creator_name
252 , a.end_cust_contact_first_name || ' ' || a.end_cust_contact_last_name
253 , lkup.meaning
254 , a.creation_date
255 , a.requested_amount || ' ' || a.currency_code
256 , a.decline_reason_code
257 , a.return_reason_code
258 , a.partner_contact_phone_number
259 , a.partner_contact_email_address
260 , a.agreement_number
261 , a.authorization_code
262 , a.start_date
263 , a.end_date
264 , a.activity_media_id
265 , a.request_type_code
266 , NVL(a.offer_id,-1)
267 --Bug# 8208686 fixed by ateotia(+)
268 , a.org_id
269 --Bug# 8208686 fixed by ateotia(-)
270 from ozf_request_headers_all_vl a
271 , pv_partner_profiles b
272 , hz_parties c
273 , jtf_rs_resource_extns pt_cont
274 , jtf_rs_resource_extns creator
275 , ozf_lookups lkup
276 -- BUG 4460277 (+)
277 , hz_relationships hz_cont_rel
278 , hz_parties cont
279 , pv_partner_profiles pvpp
280 -- BUG 4460277 (-)
281 where a.request_header_id = pc_request_id
282 and a.partner_id = b.partner_id
283 and b.partner_party_id = c.party_id
284 and a.submitted_by = creator.resource_id (+)
285 and a.status_code = lkup.lookup_code
286 and lkup.lookup_type = 'OZF_REQUEST_STATUS'
287 -- BUG 4460277 (+)
288 and pvpp.partner_id = a.partner_id
289 and hz_cont_rel.object_id = pvpp.partner_party_id
290 and hz_cont_rel.object_table_name = 'HZ_PARTIES'
291 and hz_cont_rel.subject_id = cont.party_id
292 and hz_cont_rel.subject_table_name = 'HZ_PARTIES'
293 and hz_cont_rel.relationship_type = 'EMPLOYMENT'
294 and cont.party_type = 'PERSON'
295 and hz_cont_rel.party_id = pt_cont.source_id
296 and pt_cont.category = 'PARTY'
297 and a.partner_contact_id = pt_cont.resource_id;
298 -- BUG 4460277 (-)
299
300 cursor lc_get_notes(pc_entity_type varchar2, pc_entity_id number) is
301 select notes_detail
302 from jtf_notes_vl
303 where source_object_code = pc_entity_type
304 AND SOURCE_OBJECT_ID = pc_entity_id
305 AND NOTE_STATUS in ('E' , 'I') -- only publish notes and also Public
306 ORDER BY CREATION_DATE DESC;
307
308 cursor lc_last_approver_name (pc_entity_type varchar2, pc_entity_id number) is
309 SELECT res.source_first_name || ' '|| res.source_last_name
310 FROM jtf_rs_resource_extns res, ozf_approval_access oac
311 where res.user_id = oac.approver_id
312 and oac.object_type = pc_entity_type
313 and oac.object_id = pc_entity_id
314 and oac.approval_access_id = ( select max(approval_access_id)
315 from ozf_approval_access
316 where oac.object_type = pc_entity_type
317 and oac.object_id = pc_entity_id );
318
319 cursor lc_media_name ( pc_media_id number) is
320 select media_type_name from
321 ams_media_vl
322 where media_id = pc_media_id;
323
324 cursor lc_vendor_name ( pc_entity_id number) is
325 select vendor.party_name vendor_name
326 from ozf_request_headers_all_vl enrl_req,
327 pv_partner_profiles prtnr_profile,
328 hz_relationships rel_ship,
329 hz_parties vendor
330 where enrl_req.request_header_id = pc_entity_id
331 and enrl_req.partner_id= prtnr_profile.partner_id
332 and prtnr_profile.partner_id = rel_ship.party_id
333 and prtnr_profile.partner_party_id = rel_ship.object_id
334 and enrl_req.partner_id = rel_ship.party_id
335 and rel_ship.subject_id = vendor.party_id
336 and rownum < 2;
337
338 cursor lc_get_function_id (pc_func_name varchar2) is
339 select function_id from fnd_form_functions where function_name = pc_func_name ;
340
341 BEGIN
342
343 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
344 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
345 'ozf.plsql.OZF_REQUEST_STATUS_PVT.Set_Request_Message.start',
346 'Item type:' || p_itemtype || 'Item key:' || p_itemkey || '. Entity id: ' ||
347 p_entity_id || '. Status:' || p_status || '. User type: ' || p_user_type);
348 end if;
349
350 open lc_get_request_details (pc_request_id => p_entity_id);
351 fetch lc_get_request_details into l_request_header_id
352 , l_request_number
353 , l_request_name
354 , l_partner_org_name
355 , l_customer_address
356 , l_customer_name
357 , l_partner_cont_name
358 , l_creator_name
359 , l_customer_cont_name
360 , l_entity_status
361 , l_entity_creation_date
362 , l_comp_amount
363 , l_decline_code
364 , l_return_code
365 , l_partner_cont_phone
366 , l_partner_cont_email
367 , l_agreement_number
368 , l_authorization_code
369 , l_start_date
370 , l_end_date
371 , l_activity_media_id
372 , l_request_type_code
373 ,l_offer_id
374 --Bug# 8208686 fixed by ateotia(+)
375 ,l_org_id;
376 --Bug# 8208686 fixed by ateotia(-)
377 close lc_get_request_details;
378
379 if p_itemtype = 'OZFSPBEN' then
380 l_note_type := 'OZF_SPECIAL_PRICE';
381 else
382 l_note_type := 'OZF_SOFT_FUND';
383 end if;
384
385 open lc_get_notes(pc_entity_type => l_note_type, pc_entity_id => p_entity_id);
386 fetch lc_get_notes into l_notes_clob;
387 close lc_get_notes;
388
389
390 l_notes_varchar := dbms_lob.substr(lob_loc => l_notes_clob, amount => l_note_size, offset => 1);
391
392 if p_itemtype = 'OZFSPBEN' then
393 l_request_type := 'SPECIAL_PRICE';
394 else
395 l_request_type := 'SOFT_FUND';
396 end if;
397
398 open lc_last_approver_name(pc_entity_type => l_request_type, pc_entity_id => p_entity_id);
399 fetch lc_last_approver_name into l_last_approver_name;
400 close lc_last_approver_name;
401
402 open lc_vendor_name(pc_entity_id => p_entity_id);
403 fetch lc_vendor_name into l_vendor_name;
404 close lc_vendor_name;
405
406 if p_itemtype = 'OZFSPBEN' then
407
408 ozf_utility_pvt.debug_message( 'inside Notification ' || p_itemKey );
409 ozf_utility_pvt.debug_message( 'Phone number is ' || l_partner_cont_phone );
410
411 wf_engine.SetItemAttrText( itemtype => p_itemtype,
412 itemkey => p_itemKey,
413 aname => 'REQUEST_NUMBER',
414 avalue => l_request_number);
415
416 wf_engine.SetItemAttrText( itemtype => p_itemtype,
417 itemkey => p_itemKey,
418 aname => 'REQUEST_NAME',
419 avalue => l_request_name);
420
421 wf_engine.SetItemAttrText( itemtype => p_itemtype,
422 itemkey => p_itemKey,
423 aname => 'REQUESTER_NAME',
424 avalue => l_partner_cont_name --l_creator_name -- Bug 4460277
425 );
426
427 wf_engine.SetItemAttrText( itemtype => p_itemtype,
428 itemkey => p_itemKey,
429 aname => 'REQUEST_STATUS',
430 avalue => l_entity_status);
431
432 wf_engine.SetItemAttrDate( itemtype => p_itemtype,
433 itemkey => p_itemKey,
434 aname => 'REQUEST_CREATION_DATE',
435 avalue => l_entity_creation_date);
436
437 wf_engine.SetItemAttrText( itemtype => p_itemtype,
438 itemkey => p_itemKey,
439 aname => 'REQUEST_AMOUNT',
440 avalue => l_comp_amount);
441
442 wf_engine.SetItemAttrText( itemtype => p_itemtype,
443 itemkey => p_itemKey,
444 aname => 'PARTNER_ORG_NAME',
445 avalue => l_partner_org_name);
446
447 wf_engine.SetItemAttrText( itemtype => p_itemtype,
448 itemkey => p_itemKey,
449 aname => 'PARTNER_CONTACT',
450 avalue => l_partner_cont_name);
451
452 wf_engine.SetItemAttrText( itemtype => p_itemtype,
453 itemkey => p_itemKey,
454 aname => 'PARTNER_CONTACT_PHONE',
455 avalue => l_partner_cont_phone);
456
457 wf_engine.SetItemAttrText( itemtype => p_itemtype,
458 itemkey => p_itemKey,
459 aname => 'PARTNER_CONTACT_EMAIL',
460 avalue => l_partner_cont_email);
461
462 wf_engine.SetItemAttrText( itemtype => p_itemtype,
463 itemkey => p_itemKey,
464 aname => 'LAST_APPROVER_NAME',
465 avalue => l_last_approver_name);
466
467 wf_engine.SetItemAttrText( itemtype => p_itemtype,
468 itemkey => p_itemKey,
469 aname => 'AGREEMENT NUMBER',
470 avalue => l_agreement_number);
471
472 wf_engine.SetItemAttrText( itemtype => p_itemtype,
473 itemkey => p_itemKey,
474 aname => 'AUTHORIZATION_CODE',
475 avalue => l_authorization_code);
476
477 wf_engine.SetItemAttrText( itemtype => p_itemtype,
478 itemkey => p_itemKey,
479 aname => 'REQUEST_START_DATE',
480 avalue => l_start_date);
481
482 wf_engine.SetItemAttrText( itemtype => p_itemtype,
483 itemkey => p_itemKey,
484 aname => 'REQUEST_END_DATE',
485 avalue => l_end_date);
486
487 wf_engine.SetItemAttrText( itemtype => p_itemtype,
488 itemkey => p_itemKey,
489 aname => 'LAST_NOTE',
490 avalue => l_notes_varchar);
491
492 wf_engine.SetItemAttrText( itemtype => p_itemtype,
493 itemkey => p_itemKey,
494 aname => 'VENDOR_ORG_NAME',
495 avalue => l_vendor_name);
496
497 open lc_get_function_id(pc_func_name => 'OZF_SP_VENDOR_DTAIL');
498 fetch lc_get_function_id into l_function_id;
499 close lc_get_function_id;
500
501 l_vendor_url := fnd_run_function.get_run_function_url(
502 l_function_id,
503 -1,
504 -1,
505 0,
506 'RequestHeaderId=' || p_entity_id || '&' ||
507 'RequestTypeCode=' || l_request_type_code || '&' ||
508 'OzfPartnerUser=N' || '&' ||
509 'FromPage=Dtail' || '&' ||
510 --Bug# 8208686 fixed by ateotia(+)
511 'RequestOrgId=' || l_org_id || '&' ||
512 'clickNotifLink=Y' || '&' ||
513 'addBreadCrumb=Y');
514 --Bug# 8208686 fixed by ateotia(-)
515
516 wf_engine.SetItemAttrText( itemtype => p_itemtype,
517 itemkey => p_itemKey,
518 aname => 'VENDOR_LOGIN_URL',
519 avalue => l_vendor_url );
520
521 open lc_get_function_id(pc_func_name => 'OZF_SP_PARTNER_CRTPRO');
522 fetch lc_get_function_id into l_function_id;
523 close lc_get_function_id;
524
525 l_partner_profile_url := fnd_profile.value('PV_WORKFLOW_ISTORE_URL');
526
527 l_partner_profile_url := substr(l_partner_profile_url,1,instr(l_partner_profile_url,'/',1,3)-1); -- just get the http://<host>:<port>
528
529 l_partner_url := fnd_run_function.get_run_function_url(
530 l_function_id,
531 -1,
532 -1,
533 0,
534 'RequestHeaderId=' || p_entity_id || '&' ||
535 'RequestTypeCode=' || l_request_type_code || '&' ||
536 'OzfPartnerUser=Y' || '&' ||
537 'FromPage=Dtail' || '&' ||
538 --Bug# 8208686 fixed by ateotia(+)
539 'RequestOrgId=' || l_org_id || '&' ||
540 'clickNotifLink=Y' || '&' ||
541 'addBreadCrumb=Y');
542 --Bug# 8208686 fixed by ateotia(-)
543
544 if length(l_partner_profile_url) > 0 then -- if profile is set, use it for partner URL
545 l_partner_url := l_partner_profile_url || substr(l_partner_url, instr(l_partner_url,'/',1,3));
546 end if;
547
548 wf_engine.SetItemAttrText( itemtype => p_itemtype,
549 itemkey => p_itemKey,
550 aname => 'PARTNER_LOGIN_URL',
551 avalue => l_partner_url );
552
553 l_vendor_dtail_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=682' || '&' ||
554 'OAFunc=OZF_SP_VENDOR_DTAIL' || '&' ||
555 'RequestHeaderId=' || p_entity_id || '&' ||
556 'RequestTypeCode=' || l_request_type_code || '&' ||
557 'OzfPartnerUser=N' || '&' ||
558 'FromPage=Dtail' || '&' ||
559 --Bug# 8208686 fixed by ateotia(+)
560 'RequestOrgId=' || l_org_id || '&' ||
561 'clickNotifLink=Y' || '&' ||
562 'addBreadCrumb=Y';
563 --Bug# 8208686 fixed by ateotia(-)
564
565 l_partner_dtail_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=682' || '&' ||
566 'OAFunc=OZF_SP_PARTNER_CRTPRO' || '&' ||
567 'RequestHeaderId=' || p_entity_id || '&' ||
568 'RequestTypeCode=' || l_request_type_code || '&' ||
569 'OzfPartnerUser=Y' || '&' ||
570 'FromPage=Dtail' || '&' ||
571 --Bug# 8208686 fixed by ateotia(+)
572 'RequestOrgId=' || l_org_id || '&' ||
573 'clickNotifLink=Y' || '&' ||
574 'addBreadCrumb=Y';
575 --Bug# 8208686 fixed by ateotia(-)
576
577 -- Setting the attribute value for updated projects URL
578 wf_engine.SetItemAttrText( itemtype => p_itemtype,
579 itemkey => p_itemKey,
580 aname => 'VENDOR_DTAIL_URL',
581 avalue => l_vendor_dtail_url);
582
583 wf_engine.SetItemAttrText( itemtype => p_itemtype,
584 itemkey => p_itemKey,
585 aname => 'PARTNER_DTAIL_URL',
586 avalue => l_partner_dtail_url);
587
588
589 IF l_decline_code IS NOT NULL THEN
590 l_decline_meaning := OZF_Utility_PVT.get_lookup_meaning('OZF_SP_REQUEST_DECLINE_CODE',l_decline_code);
591 wf_engine.SetItemAttrText( itemtype => p_itemtype,
592 itemkey => p_itemKey,
593 aname => 'DECLINE_REASON',
594 avalue => l_decline_meaning);
595 END IF;
596
597
598 elsif p_itemtype = 'OZFSFBEN' then
599
600 open lc_media_name(pc_media_id => l_activity_media_id);
601 fetch lc_media_name into l_activity_name;
602 close lc_media_name;
603
604 wf_engine.SetItemAttrText( itemtype => p_itemtype,
605 itemkey => p_itemKey,
606 aname => 'REQUEST_NUMBER',
607 avalue => l_request_number);
608
609 wf_engine.SetItemAttrText( itemtype => p_itemtype,
610 itemkey => p_itemKey,
611 aname => 'REQUEST_NAME',
612 avalue => l_request_name);
613
614 wf_engine.SetItemAttrText( itemtype => p_itemtype,
615 itemkey => p_itemKey,
616 aname => 'REQUESTER_NAME',
617 avalue => l_partner_cont_name); --l_creator_name -- Bug 4460277
618
619 wf_engine.SetItemAttrText( itemtype => p_itemtype,
620 itemkey => p_itemKey,
621 aname => 'REQUEST_STATUS',
622 avalue => l_entity_status);
623
624 wf_engine.SetItemAttrDate( itemtype => p_itemtype,
625 itemkey => p_itemKey,
626 aname => 'REQUEST_CREATION_DATE',
627 avalue => l_entity_creation_date);
628
629 wf_engine.SetItemAttrText( itemtype => p_itemtype,
630 itemkey => p_itemKey,
631 aname => 'REQUEST_AMOUNT',
632 avalue => l_comp_amount);
633
634 wf_engine.SetItemAttrText( itemtype => p_itemtype,
635 itemkey => p_itemKey,
636 aname => 'PARTNER_ORG_NAME',
637 avalue => l_partner_org_name);
638
639 wf_engine.SetItemAttrText( itemtype => p_itemtype,
640 itemkey => p_itemKey,
641 aname => 'PARTNER_CONTACT',
642 avalue => l_partner_cont_name);
643
644 wf_engine.SetItemAttrText( itemtype => p_itemtype,
645 itemkey => p_itemKey,
646 aname => 'LAST_APPROVER_NAME',
647 avalue => l_last_approver_name);
648
649 wf_engine.SetItemAttrText( itemtype => p_itemtype,
650 itemkey => p_itemKey,
651 aname => 'REQUEST_START_DATE',
652 avalue => l_start_date);
653
654 wf_engine.SetItemAttrText( itemtype => p_itemtype,
655 itemkey => p_itemKey,
656 aname => 'REQUEST_END_DATE',
657 avalue => l_end_date);
658
659 wf_engine.SetItemAttrText( itemtype => p_itemtype,
660 itemkey => p_itemKey,
661 aname => 'LAST_NOTE',
662 avalue => l_notes_varchar);
663
664 wf_engine.SetItemAttrText( itemtype => p_itemtype,
665 itemkey => p_itemKey,
666 aname => 'ACTIVITY_NAME',
667 avalue => l_activity_name);
668
669 wf_engine.SetItemAttrText( itemtype => p_itemtype,
670 itemkey => p_itemKey,
671 aname => 'VENDOR_ORG_NAME',
672 avalue => l_vendor_name);
673
674 open lc_get_function_id(pc_func_name => 'OZF_SF_VENDOR_DETAILS');
675 fetch lc_get_function_id into l_function_id;
676 close lc_get_function_id;
677
678
679 l_vendor_url := fnd_run_function.get_run_function_url(
680 l_function_id,
681 -1,
682 -1,
683 0,
684 'reqId=' || p_entity_id || '&' ||
685 'OfferId=' || l_offer_id || '&' ||
686 'StatusCode=' || p_status || '&' ||
687 'ApprovePriv=1' || '&' || 'pgMode=VDT');
688
689 wf_engine.SetItemAttrText( itemtype => p_itemtype,
690 itemkey => p_itemKey,
691 aname => 'VENDOR_LOGIN_URL',
692 avalue => l_vendor_url );
693
694 open lc_get_function_id(pc_func_name => 'OZF_SF_PARTNER_DETAILS');
695 fetch lc_get_function_id into l_function_id;
696 close lc_get_function_id;
697
698 l_partner_url := fnd_run_function.get_run_function_url(
699 l_function_id,
700 -1,
701 -1,
702 0,
703 'reqId=' || p_entity_id || '&' ||
704 'pgMode=PDT');
705
706 l_partner_profile_url := fnd_profile.value('PV_WORKFLOW_ISTORE_URL');
707 l_partner_profile_url := substr(l_partner_profile_url,1,instr(l_partner_profile_url,'/',1,3)-1); -- just get the http://<host>:<port>
708
709 if length(l_partner_profile_url) > 0 then -- if profile is set, use it for partner URL
710 l_partner_url := l_partner_profile_url || substr(l_partner_url, instr(l_partner_url,'/',1,3));
711 end if;
712
713 wf_engine.SetItemAttrText( itemtype => p_itemtype,
714 itemkey => p_itemKey,
715 aname => 'PARTNER_LOGIN_URL',
716 avalue => l_partner_url );
717
718 l_vendor_dtail_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=682' || '&' ||
719 'OAFunc=OZF_SF_VENDOR_DETAILS' || '&' ||
720 'reqId=' || p_entity_id || '&' ||
721 'OfferId=' || l_offer_id || '&' ||
722 'StatusCode=' || p_status || '&' ||
723 'ApprovePriv=1' || '&' || 'pgMode=VDT';
724
725 l_partner_dtail_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=682' || '&' ||
726 'OAFunc=OZF_SF_PARTNER_DETAILS' || '&' ||
727 'reqId=' || p_entity_id || '&' ||
728 'pgMode=PDT';
729
730
731 -- Setting the attribute value for updated projects URL
732 wf_engine.SetItemAttrText( itemtype => p_itemtype,
733 itemkey => p_itemKey,
734 aname => 'VENDOR_DTAIL_URL',
735 avalue => l_vendor_dtail_url);
736
737 wf_engine.SetItemAttrText( itemtype => p_itemtype,
738 itemkey => p_itemKey,
739 aname => 'PARTNER_DTAIL_URL',
740 avalue => l_partner_dtail_url);
741
742 IF l_decline_code IS NOT NULL THEN
743 l_decline_meaning := OZF_Utility_PVT.get_lookup_meaning('OZF_SF_DECLINE_CODE',l_decline_code);
744 wf_engine.SetItemAttrText( itemtype => p_itemtype,
745 itemkey => p_itemKey,
746 aname => 'DECLINE_REASON',
747 avalue => l_decline_meaning);
748 ozf_utility_pvt.debug_message( l_decline_meaning||': l_decline_meaning');
749 END IF;
750
751 IF l_return_code IS NOT NULL AND l_return_code <> '' THEN
752 l_return_meaning := OZF_Utility_PVT.get_lookup_meaning('OZF_SF_RETURN_CODE',l_return_code);
753 wf_engine.SetItemAttrText( itemtype => p_itemtype,
754 itemkey => p_itemKey,
755 aname => 'RETURN_REASON',
756 avalue => l_return_code);
757 END IF;
758
759 end if; --if p_itemtype = 'OZFSPBEN' then
760
761 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
762 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
763 'ozf.plsql.OZF_REQUEST_STATUS_PVT.Set_Request_Message.end', 'Exiting');
764 end if;
765
766 END;
767
768 ---------------------------------------------------------------------
769 -- PROCEDURE
770 -- Return_Request_Userlist
771 --
772 -- PURPOSE
773 -- Handles the approvals and rejections of objects
774 --
775 -- PARAMETERS
776 --
777 -- NOTES
778 ---------------------------------------------------------------------
779 FUNCTION Return_Request_Userlist (
780 p_benefit_type IN VARCHAR2,
781 p_entity_id IN NUMBER,
782 p_user_role IN VARCHAR2,
783 p_status IN VARCHAR2) RETURN VARCHAR2
784 is
785 l_role_list varchar2(1000);
786 l_partner_id number;
787
788 cursor lc_get_ext_super_users(pc_permission varchar2,
789 pc_partner_id number) is
790 SELECT
791 usr.user_name
792 FROM
793 pv_partner_profiles prof,
794 hz_relationships pr2,
795 jtf_rs_resource_extns pj,
796 fnd_user usr
797 WHERE
798 prof.partner_id = pc_partner_id
799 and prof.partner_party_id = pr2.object_id
800 and pr2.subject_table_name = 'HZ_PARTIES'
801 and pr2.object_table_name = 'HZ_PARTIES'
802 and pr2.directional_flag = 'F'
803 and pr2.relationship_code = 'EMPLOYEE_OF'
804 and pr2.relationship_type = 'EMPLOYMENT'
805 and (pr2.end_date is null or pr2.end_date > sysdate)
806 and pr2.status = 'A'
807 and pr2.party_id = pj.source_id
808 and pj.category = 'PARTY'
809 and usr.user_id = pj.user_id
810 and (usr.end_date > sysdate OR usr.end_date IS NULL)
811 and exists(select 1 from jtf_auth_principal_maps jtfpm,
812 jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
813 jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
814 jtf_auth_permissions_b jtfperm
815 where PJ.user_name = jtfp1.principal_name
816 and jtfp1.is_user_flag=1
817 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
818 and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
819 and jtfp2.is_user_flag=0
820 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
821 and jtfrp.positive_flag = 1
822 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
823 and jtfperm.permission_name = pc_permission
824 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
825 and jtfd.domain_name='CRM_DOMAIN' );
826
827 cursor lc_get_int_super_users(pc_permission varchar2) is
828 select unique usr.user_name
829 from jtf_auth_principal_maps jtfpm,
830 jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
831 jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
832 jtf_auth_permissions_b jtfperm, jtf_rs_resource_extns pj,
833 fnd_user usr
834 where PJ.user_name = jtfp1.principal_name
835 and pj.category = 'EMPLOYEE'
836 and usr.user_id = pj.user_id
837 and (usr.end_date > sysdate OR usr.end_date IS NULL)
838 and jtfp1.is_user_flag=1
839 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
840 and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
841 and jtfp2.is_user_flag=0
842 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
843 and jtfrp.positive_flag = 1
844 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
845 and jtfperm.permission_name = pc_permission
846 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
847 and jtfd.domain_name='CRM_DOMAIN';
848
849 cursor lc_get_partner_id(pc_entity_id number) is
850 select partner_id
851 from ozf_request_headers_all_b
852 where request_header_id = pc_entity_id;
853
854 cursor lc_get_pt_cont(pc_entity_id number) is
855 select fnd.user_name
856 from fnd_user fnd
857 , ozf_request_headers_all_b ref
858 , jtf_rs_resource_extns jtf
859 where ref.partner_contact_id = jtf.resource_id
860 and jtf.user_id = fnd.user_id
861 and ref.request_header_id = pc_entity_id;
862
863 begin
864 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
865 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
866 'ozf.plsql.OZF_REQUEST_STATUS_PVT.Return_Request_Userlist.start',
867 'Benefit type:' || p_benefit_type || '. Entity id: ' || p_entity_id ||
868 '. Status:' || p_status || '. User type: ' || p_user_role);
869 end if;
870
871 open lc_get_partner_id(pc_entity_id => p_entity_id);
872 fetch lc_get_partner_id into l_partner_id;
873 close lc_get_partner_id;
874
875 if p_user_role = 'SPECIAL_PRICE_SUPERUSER_EXT' then
876
877 for l_row in lc_get_ext_super_users(pc_permission => 'OZF_SPECIAL_PRICE_SUPERUSER',
878 pc_partner_id => l_partner_id) loop
879 l_role_list := l_role_list || ',' || l_row.user_name;
880 end loop;
881 l_role_list := substr(l_role_list,2);
882
883 elsif p_user_role = 'SOFT_FUND_SUPERUSER_EXT' then
884
885 for l_row in lc_get_ext_super_users(pc_permission => 'OZF_SOFTFUND_SUPERUSER',
886 pc_partner_id => l_partner_id) loop
887 l_role_list := l_role_list || ',' || l_row.user_name;
888 end loop;
889 l_role_list := substr(l_role_list,2);
890
891 elsif p_user_role = 'SPECIAL_PRICE_SUPERUSER_INT' then
892
893 for l_row in lc_get_int_super_users(pc_permission => 'OZF_SPECIAL_PRICE_SUPERUSER') loop
894 l_role_list := l_role_list || ',' || l_row.user_name;
895 end loop;
896 l_role_list := substr(l_role_list,2);
897
898 elsif p_user_role = 'SOFT_FUND_SUPERUSER_INT' then
899
900 for l_row in lc_get_int_super_users(pc_permission => 'OZF_SOFTFUND_SUPERUSER') loop
901 l_role_list := l_role_list || ',' || l_row.user_name;
902 end loop;
903 l_role_list := substr(l_role_list,2);
904
905 elsif p_user_role = 'PT_CONTACT' then
906
907 for l_row in lc_get_pt_cont(pc_entity_id => p_entity_id) loop
908 l_role_list := l_role_list || ',' || l_row.user_name;
909 end loop;
910 l_role_list := substr(l_role_list,2);
911
912 else
913 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
914 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
915 'ozf.plsql.OZF_REQUEST_STATUS_PVT.Return_Request_Userlist.info',
916 'Unrecognized user role:' || p_user_role);
917 END IF;
918 end if;
919
920 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
921 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
922 'ozf.plsql.OZF_REQUEST_STATUS_PVT.Return_Request_Userlist.end', 'Exiting');
923 end if;
924
925 return l_role_list;
926 end;
927 ---------------------------------------------------------------------
928 -- PROCEDURE
929 -- Create_Interaction
930 --
931 -- PURPOSE
932 -- Created Interaction History
933 --
934 -- PARAMETERS
935 --
936 -- NOTES
937 ---------------------------------------------------------------------
938 PROCEDURE Create_Interaction (
939 p_api_version IN NUMBER
940 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
941 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
942 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
943
944 ,x_return_status OUT NOCOPY VARCHAR2
945 ,x_msg_data OUT NOCOPY VARCHAR2
946 ,x_msg_count OUT NOCOPY NUMBER
947
948 ,p_approval_rec IN OZF_APPROVAL_PVT.approval_rec_type
949 )
950 IS
951 CURSOR csr_partner (v_request_id in number) IS
952 SELECT partner_id, request_number,
953 agreement_number, authorization_code, activity_media_id
954 FROM ozf_request_headers_all_b
955 WHERE request_header_id = v_request_id;
956
957 CURSOR csr_activity_name (v_activity_id in number) IS
958 SELECT channel_name
959 FROM ams_channels_vl
960 WHERE channel_id = v_activity_id;
961
962 l_api_name CONSTANT varchar2(80) := 'Create_Interaction';
963 l_api_version CONSTANT number := 1.0;
964 l_history_category varchar2(30) := 'GENERAL';
965 l_message_code varchar2(30);
966 l_access_level varchar2(1) := 'V';
967 l_interaction_level number := PVX_UTILITY_PVT.G_INTERACTION_LEVEL_50;
968 l_comments varchar2(2000);
969 l_status varchar2(30) := p_approval_rec.status_code;
970 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
971
972 l_partner_id number;
973 l_request_number varchar2(30);
974 l_agreement_number varchar2(30);
975 l_authorization_code varchar2(30);
976 l_activity_id number;
977 l_activity_name varchar2(80);
978 l_return_status varchar2(1);
979
980 BEGIN
981 -- Standard begin of API savepoint
982 SAVEPOINT Create_Interaction_PVT;
983 -- Standard call to check for call compatibility.
984 IF NOT FND_API.Compatible_API_Call (
985 l_api_version,
986 p_api_version,
987 l_api_name,
988 G_PKG_NAME)
989 THEN
990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
991 END IF;
992 -- Debug Message
993 IF G_DEBUG THEN
994 ozf_utility_pvt.debug_message( l_api_name||': Start');
995 END IF;
996 --Initialize message list if p_init_msg_list is TRUE.
997 IF FND_API.To_Boolean (p_init_msg_list) THEN
998 FND_MSG_PUB.initialize;
999 END IF;
1000 -- Initialize API return status to sucess
1001 x_return_status := FND_API.G_RET_STS_SUCCESS;
1002
1003 -- Get Partner details
1004 OPEN csr_partner (p_approval_rec.object_id);
1005 FETCH csr_partner INTO l_partner_id,
1006 l_request_number,
1007 l_agreement_number,
1008 l_authorization_code,
1009 l_activity_id;
1010 CLOSE csr_partner;
1011
1012
1013 -- Construct Message details
1014 IF p_approval_rec.object_type = 'SPECIAL_PRICE' THEN
1015
1016 IF l_status = 'DRAFT' THEN
1017 l_message_code := 'OZF_SP_DRAFT_LOG';
1018 l_log_params_tbl(1).param_name := 'REQ_NUM';
1019 l_log_params_tbl(1).param_value := l_request_number;
1020
1021 ELSIF l_status = 'SUBMITTED_FOR_APPROVAL' THEN
1022 l_message_code := 'OZF_SP_PENDING_LOG';
1023 l_log_params_tbl(1).param_name := 'REQ_NUM';
1024 l_log_params_tbl(1).param_value := 'l_request_number';
1025
1026 ELSIF l_status = 'RETURNED' THEN
1027 l_message_code := 'OZF_SP_RETURNED_LOG';
1028 l_log_params_tbl(1).param_name := 'REQ_NUM';
1029 l_log_params_tbl(1).param_value := l_request_number;
1030
1031 ELSIF l_status = 'DECLINED' THEN
1032 l_message_code := 'OZF_SP_REJECTED_LOG';
1033 l_log_params_tbl(1).param_name := 'REQ_NUM';
1034 l_log_params_tbl(1).param_value := l_request_number;
1035
1036 ELSIF l_status = 'APPROVED' THEN
1037 l_message_code := 'OZF_SP_APPROVED_LOG';
1038 l_log_params_tbl(1).param_name := 'REQ_NUM';
1039 l_log_params_tbl(1).param_value := l_request_number;
1040 l_log_params_tbl(2).param_name := 'AGREEMENT_NUM';
1041 l_log_params_tbl(2).param_value := l_agreement_number;
1042 l_log_params_tbl(3).param_name := 'AUTH_CODE';
1043 l_log_params_tbl(3).param_value := l_authorization_code;
1044
1045 ELSIF l_status = 'BUDGETAPP' THEN
1046 l_message_code := 'OZF_SP_BUDGETAPP_LOG';
1047 l_log_params_tbl(1).param_name := 'REQ_NUM';
1048 l_log_params_tbl(1).param_value := l_request_number;
1049
1050 ELSIF l_status = 'CLOSED' THEN
1051 l_message_code := 'OZF_SP_CLOSED_LOG';
1052 l_log_params_tbl(1).param_name := 'REQ_NUM';
1053 l_log_params_tbl(1).param_value := l_request_number;
1054 l_log_params_tbl(2).param_name := 'AGREEMENT_NUM';
1055 l_log_params_tbl(2).param_value := l_agreement_number;
1056 l_log_params_tbl(3).param_name := 'AUTH_CODE';
1057 l_log_params_tbl(3).param_value := l_authorization_code;
1058
1059 ELSIF l_status = 'VOID' THEN
1060 l_message_code := 'OZF_SP_VOID_LOG';
1061 l_log_params_tbl(1).param_name := 'REQ_NUM';
1062 l_log_params_tbl(1).param_value := l_request_number;
1063 l_log_params_tbl(2).param_name := 'AGREEMENT_NUM';
1064 l_log_params_tbl(2).param_value := l_agreement_number;
1065 l_log_params_tbl(3).param_name := 'AUTH_CODE';
1066 l_log_params_tbl(3).param_value := l_authorization_code;
1067
1068 ELSIF l_status = 'ARCHIVED' THEN
1069 l_message_code := 'OZF_SP_ARCHIVED_LOG';
1070 l_log_params_tbl(1).param_name := 'REQ_NUM';
1071 l_log_params_tbl(1).param_value := l_request_number;
1072 l_log_params_tbl(2).param_name := 'AGREEMENT_NUM';
1073 l_log_params_tbl(2).param_value := l_agreement_number;
1074 l_log_params_tbl(3).param_name := 'AUTH_CODE';
1075 l_log_params_tbl(3).param_value := l_authorization_code;
1076
1077 END IF;
1078
1079 ELSIF p_approval_rec.object_type = 'SOFT_FUND' THEN
1080
1081 -- Get Activity details
1082 OPEN csr_activity_name (l_activity_id);
1083 FETCH csr_activity_name INTO l_activity_name;
1084 CLOSE csr_activity_name;
1085
1086 IF l_status = 'DRAFT' THEN
1087 l_message_code := 'OZF_SF_DRAFT_LOG';
1088 l_log_params_tbl(1).param_name := 'REQ_NUM';
1089 l_log_params_tbl(1).param_value := l_request_number;
1090
1091 ELSIF l_status = 'SUBMITTED_FOR_APPROVAL' THEN
1092 l_message_code := 'OZF_SF_PENDING_LOG';
1093 l_log_params_tbl(1).param_name := 'REQ_NUM';
1094 l_log_params_tbl(1).param_value := l_request_number;
1095
1096 ELSIF l_status = 'RETURNED' THEN
1097 l_message_code := 'OZF_SF_RETURNED_LOG';
1098 l_log_params_tbl(1).param_name := 'REQ_NUM';
1099 l_log_params_tbl(1).param_value := l_request_number;
1100
1101 ELSIF l_status = 'DECLINED' THEN
1102 l_message_code := 'OZF_SF_REJECTED_LOG';
1103 l_log_params_tbl(1).param_name := 'REQ_NUM';
1104 l_log_params_tbl(1).param_value := l_request_number;
1105
1106 ELSIF l_status = 'APPROVED' THEN
1107 l_message_code := 'OZF_SF_APPROVED_LOG';
1108 l_log_params_tbl(1).param_name := 'REQ_NUM';
1109 l_log_params_tbl(1).param_value := l_request_number;
1110 l_log_params_tbl(2).param_name := 'ACTIVITY';
1111 l_log_params_tbl(2).param_value := l_activity_name;
1112
1113 ELSIF l_status = 'BUDGETAPP' THEN
1114 l_message_code := 'OZF_SF_BUDGETAPP_LOG';
1115 l_log_params_tbl(1).param_name := 'REQ_NUM';
1116 l_log_params_tbl(1).param_value := l_request_number;
1117
1118 ELSIF l_status = 'CLOSED' THEN
1119 l_message_code := 'OZF_SF_CLOSED_LOG';
1120 l_log_params_tbl(1).param_name := 'REQ_NUM';
1121 l_log_params_tbl(1).param_value := l_request_number;
1122
1123 ELSIF l_status = 'VOID' THEN
1124 l_message_code := 'OZF_SF_VOID_LOG';
1125 l_log_params_tbl(1).param_name := 'REQ_NUM';
1126 l_log_params_tbl(1).param_value := l_request_number;
1127
1128 ELSIF l_status = 'ARCHIVED' THEN
1129 l_message_code := 'OZF_SF_ARCHIVED_LOG';
1130 l_log_params_tbl(1).param_name := 'REQ_NUM';
1131 l_log_params_tbl(1).param_value := l_request_number;
1132
1133 END IF;
1134
1135 END IF;
1136
1137 IF G_DEBUG THEN
1138 ozf_utility_pvt.debug_message( 'Before creating interaction ' || l_message_code);
1139 END IF;
1140
1141 IF l_message_code IS NOT null THEN
1142 -- Create Interaction History
1143 PVX_UTILITY_PVT.create_history_log(
1144 p_arc_history_for_entity_code => p_approval_rec.object_type,
1145 p_history_for_entity_id => p_approval_rec.object_id,
1146 p_history_category_code => l_history_category,
1147 p_message_code => l_message_code,
1148 p_partner_id => l_partner_id,
1149 p_access_level_flag => l_access_level,
1150 p_interaction_level => l_interaction_level,
1151 p_comments => l_comments,
1152 p_log_params_tbl => l_log_params_tbl,
1153 x_return_status => l_return_status,
1154 x_msg_count => x_msg_count,
1155 x_msg_data => x_msg_data
1156 );
1157 ozf_utility_pvt.debug_message( 'after creating interaction ' || l_return_status);
1158 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1159 RAISE FND_API.G_EXC_ERROR;
1160 END IF;
1161
1162 END IF;
1163
1164
1165 --Standard check of commit
1166 IF FND_API.To_Boolean ( p_commit ) THEN
1167 COMMIT WORK;
1168 END IF;
1169 -- Debug Message
1170 IF G_DEBUG THEN
1171 ozf_utility_pvt.debug_message( l_api_name||': End');
1172 END IF;
1173 --Standard call to get message count and if count=1, get the message
1174 FND_MSG_PUB.Count_And_Get (
1175 p_encoded => FND_API.G_FALSE,
1176 p_count => x_msg_count,
1177 p_data => x_msg_data
1178 );
1179 EXCEPTION
1180 WHEN FND_API.G_EXC_ERROR THEN
1181 ROLLBACK TO Create_Interaction_PVT;
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 -- Standard call to get message count and if count=1, get the message
1184 FND_MSG_PUB.Count_And_Get (
1185 p_encoded => FND_API.G_FALSE,
1186 p_count => x_msg_count,
1187 p_data => x_msg_data
1188 );
1189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1190 ROLLBACK TO Create_Interaction_PVT;
1191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1192 -- Standard call to get message count and if count=1, get the message
1193 FND_MSG_PUB.Count_And_Get (
1194 p_encoded => FND_API.G_FALSE,
1195 p_count => x_msg_count,
1196 p_data => x_msg_data
1197 );
1198 WHEN OTHERS THEN
1199 ROLLBACK TO Create_Interaction_PVT;
1200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1201 IF OZF_UNEXP_ERROR_ON
1202 THEN
1203 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1204 END IF;
1205 -- Standard call to get message count and if count=1, get the message
1206 FND_MSG_PUB.Count_And_Get (
1207 p_encoded => FND_API.G_FALSE,
1208 p_count => x_msg_count,
1209 p_data => x_msg_data
1210 );
1211 --
1212 END Create_Interaction;
1213
1214 -- 'R12.1 Enhancement: Ship & Debit Request' by ateotia(+)
1215
1216 ---------------------------------------------------------------------
1217 -- PROCEDURE
1218 -- Event_SD_Subscription
1219 --
1220 -- PURPOSE
1221 -- Subscription for the event raised for Ship & Debit Request
1222 --
1223 -- PARAMETERS
1224 --
1225 -- NOTES
1226 ---------------------------------------------------------------------
1227 FUNCTION Event_SD_Subscription(
1228 p_subscription_guid IN raw,
1229 p_event IN OUT NOCOPY wf_event_t)
1230 RETURN varchar2
1231 is
1232
1233 l_api_name CONSTANT VARCHAR2(30) := 'Event_SD_Subscription';
1234 l_api_version CONSTANT number := 1.0;
1235 l_rule varchar2(20);
1236 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1237 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
1238 l_parameter_name l_parameter_t.name%type;
1239 i pls_integer;
1240 l_event_key varchar2(240);
1241 l_object_id number;
1242 l_action_code varchar2(30);
1243 l_user_list varchar2(2000);
1244 l_msg_count number;
1245 l_msg_data varchar2(2000);
1246 l_return_status varchar2(10);
1247
1248 BEGIN
1249 -- Debug Message
1250 IF G_DEBUG THEN
1251 ozf_utility_pvt.debug_message( l_api_name||': Start');
1252 END IF;
1253 l_parameter_list := p_event.getParameterList();
1254 l_event_key := p_event.getEventKey();
1255
1256 IF G_DEBUG THEN
1257 ozf_utility_pvt.debug_message( 'Event Key ' || l_event_key);
1258 END IF;
1259
1260 IF l_parameter_list IS NOT NULL THEN
1261 i := l_parameter_list.FIRST;
1262 WHILE ( i <= l_parameter_list.last) LOOP
1263 IF G_DEBUG THEN
1264 ozf_utility_pvt.debug_message( 'Parameter Name ' || l_parameter_list(i).getName());
1265 ozf_utility_pvt.debug_message( 'Parameter Value ' || l_parameter_list(i).getValue());
1266 ozf_utility_pvt.debug_message( 'Parameter ' || i || ' of ' || l_parameter_list.last);
1267 END IF;
1268 l_parameter_name := null;
1269 l_parameter_name := l_parameter_list(i).getName();
1270 IF l_parameter_name = 'OBJECT_ID' THEN
1271 l_object_id := l_parameter_list(i).getValue();
1272 ELSIF l_parameter_name = 'ACTION_CODE' THEN
1273 l_action_code := l_parameter_list(i).getValue();
1274 END IF;
1275 i := l_parameter_list.next(i);
1276 END LOOP;
1277
1278 -- Call api to send notification
1279 IF G_DEBUG THEN
1280 ozf_utility_pvt.debug_message( 'before calling api to send notification');
1281 END IF;
1282
1283 OZF_APPROVAL_PVT.Send_SD_Notification(
1284 p_api_version => l_api_version,
1285 p_init_msg_list => FND_API.G_FALSE,
1286 x_return_status => l_return_status,
1287 x_msg_data => l_msg_data,
1288 x_msg_count => l_msg_count,
1289 p_object_id => l_object_id,
1290 p_action_code => l_action_code);
1291
1292 IF G_DEBUG THEN
1293 ozf_utility_pvt.debug_message( 'Return Status: '||l_return_status);
1294 END IF;
1295 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1296 RETURN 'ERROR';
1297 END IF;
1298
1299 END IF;
1300
1301 RETURN 'SUCCESS';
1302
1303 -- Debug Message
1304 IF G_DEBUG THEN
1305 ozf_utility_pvt.debug_message( l_api_name||': End');
1306 END IF;
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 WF_CORE.CONTEXT(G_PKG_NAME, L_API_NAME, P_EVENT.GETEVENTNAME(), P_SUBSCRIPTION_GUID);
1311 WF_EVENT.SETERRORINFO(P_EVENT,'ERROR');
1312 RETURN 'ERROR';
1313 --
1314 END Event_SD_Subscription;
1315
1316 -- 'R12.1 Enhancement: Ship & Debit Request' by ateotia(-)
1317
1318
1319 END OZF_REQUEST_STATUS_PVT;