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