[Home] [Help]
PACKAGE BODY: APPS.DPP_REQUEST_STATUS_PVT
Source
1 PACKAGE BODY DPP_REQUEST_STATUS_PVT AS
2 /* $Header: dppvrstb.pls 120.11.12010000.3 2010/04/21 13:33:54 kansari ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_REQUEST_STATUS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'dppvrstb.pls';
6
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9
10 DPP_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
11 DPP_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
12 DPP_UNEXP_ERROR_ON BOOLEAN :=FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error);
13 DPP_ERROR_ON BOOLEAN := FND_MSG_PUB.check_msg_level(fnd_msg_pub.g_msg_lvl_error);
14 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
15
16 ---------------------------------------------------------------------
17 -- PROCEDURE
18 -- Event_Subscription
19 --
20 -- PURPOSE
21 -- Subscription for the event raised during status change
22 --
23 -- PARAMETERS
24 --
25 -- NOTES
26 ---------------------------------------------------------------------
27 FUNCTION Event_Subscription(
28 p_subscription_guid IN raw,
29 p_event IN OUT NOCOPY wf_event_t)
30 RETURN varchar2
31 is
32
33 l_api_name CONSTANT VARCHAR2(30) := 'Event_Subscription';
34 l_api_version CONSTANT number := 1.0;
35 l_rule varchar2(20);
36 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
37 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
38 l_parameter_name l_parameter_t.name%type;
39 i pls_integer;
40
41 l_msg_callback_api varchar2(60);
42 l_user_callback_api varchar2(60);
43 l_benefit_id number;
44 l_status varchar2(30);
45 l_event_key varchar2(240);
46 l_object_type varchar2(30) := 'PRICE PROTECTION';
47 l_object_id number;
48 l_user_list varchar2(2000);
49 l_msg_count number;
50 l_msg_data varchar2(2000);
51 l_return_status varchar2(10);
52 l_approval_rec DPP_APPROVAL_PVT.approval_rec_type;
53 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_REQUEST_STATUS_PVT.EVENT_SUBSCRIPTION';
54 BEGIN
55 -- Standard begin of API savepoint
56 SAVEPOINT Event_Subscription_PVT;
57 -- Debug Message
58
59 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, l_api_name||': Start');
60
61
62 l_parameter_list := p_event.getParameterList();
63 l_event_key := p_event.getEventKey();
64
65
66 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Event Key ' || l_event_key);
67
68
69 IF l_parameter_list IS NOT NULL THEN
70 i := l_parameter_list.FIRST;
71 WHILE ( i <= l_parameter_list.last) LOOP
72
73 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Parameter Name ' || l_parameter_list(i).getName());
74 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Parameter Value ' || l_parameter_list(i).getValue());
75 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Parameter ' || i || ' of ' || l_parameter_list.last);
76
77 l_parameter_name := null;
78 l_parameter_name := l_parameter_list(i).getName();
79
80 IF l_parameter_name = 'STATUS_CODE' THEN
81 l_status := l_parameter_list(i).getValue();
82 ELSIF l_parameter_name = 'OBJECT_TYPE' THEN
83 l_object_type := l_parameter_list(i).getValue();
84 ELSIF l_parameter_name = 'OBJECT_ID' THEN
85 l_object_id := l_parameter_list(i).getValue();
86 END IF;
87
88 i := l_parameter_list.next(i);
89 END LOOP;
90
91 l_msg_callback_api := 'DPP_REQUEST_STATUS_PVT.Set_Request_Message';
92
93 l_approval_rec.object_type := l_object_type;
94 l_approval_rec.object_id := l_object_id;
95 l_approval_rec.status_code := l_status;
96
97
98 -- Call api to send notification
99 DPP_APPROVAL_PVT.Send_Notification(
100 p_api_version => l_api_version,
101 p_init_msg_list => FND_API.G_FALSE,
102 --p_validation_level => p_validation_level,
103 x_return_status => l_return_status,
104 x_msg_data => l_msg_data,
105 x_msg_count => l_msg_count,
106 p_transaction_header_id => l_object_id,
107 p_msg_callback_api => l_msg_callback_api,
108 p_approval_rec => l_approval_rec
109 );
110
111 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
112 RETURN 'ERROR';
113 END IF;
114
115 END IF;
116
117 RETURN 'SUCCESS';
118
119 -- Debug Message
120
121 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, l_api_name||': End');
122
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 WF_CORE.CONTEXT(G_PKG_NAME, L_API_NAME, P_EVENT.GETEVENTNAME(), P_SUBSCRIPTION_GUID);
127 WF_EVENT.SETERRORINFO(P_EVENT,'ERROR');
128 RETURN 'ERROR';
129 --
130 END Event_Subscription;
131 ---------------------------------------------------------------------
132 -- PROCEDURE
133 -- Set_Request_Message
134 --
135 -- PURPOSE
136 -- Handles the approvals and rejections of objects
137 --
138 -- PARAMETERS
139 --
140 -- NOTES
141 ---------------------------------------------------------------------
142 PROCEDURE Set_Request_Message (
143 p_itemtype IN VARCHAR2,
144 p_itemkey IN VARCHAR2,
145 P_transaction_header_id IN NUMBER,
146 P_STATUS IN VARCHAR2)
147 IS
148
149 l_api_name CONSTANT VARCHAR2(30) := 'Set_Request_Message';
150
151 l_request_header_id number;
152 l_request_number varchar2(50);
153 l_request_name varchar2(100);
154 l_comp_amount varchar2(20);
155 l_entity_status varchar2(100);
156 l_entity_creation_date varchar2(30);
157 l_notes_clob CLOB;
158 l_notes_varchar varchar2(4000);
159 l_note_size binary_integer := 4000;
160
161 l_last_approver_name varchar2(200);
162 l_request_type varchar2(20);
163 l_agreement_number varchar2(30);
164 l_authorization_code varchar2(30);
165 l_login_url varchar2(1000);
166 l_request_type_code varchar2(30);
167 l_user_dtail_url varchar2(200);
168 l_note_type varchar2(20);
169 l_offer_id NUMBER;
170
171 l_transaction_header_id NUMBER;
172 l_transaction_number VARCHAR2(40);
173 l_ref_document_number VARCHAR2(40);
174 l_vendor_name VARCHAR2(240);
175 l_vendor_site_code VARCHAR2(240);
176 l_contact_name varchar2(360);
177 l_creator_name varchar2(360);
178 l_transaction_status varchar2(40);
179 l_creation_date date;
180 l_currency varchar2(15);
181 l_decline_code varchar2(30);
182 l_return_code varchar2(30);
183 l_decline_meaning varchar2(200);
184 l_return_meaning varchar2(200);
185 l_contact_phone varchar2(15);
186 l_contact_email VARCHAR2(2000);
187 l_start_date date;
188 l_days_covered number;
189 l_status varchar2(30);
190
191 l_function_id NUMBER;
192 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_REQUEST_STATUS_PVT.SET_REQUEST_MESSAGE';
193
194 cursor lc_get_request_details (pc_header_id number) is
195 select dtha.transaction_header_id
196 , dtha.transaction_number
197 , dtha.ref_document_number
198 , asp.vendor_name
199 , assa.vendor_site_code
200 , dtha.vendor_contact_name SUP_CONTACT_name
201 , creator.source_first_name || ' ' || creator.source_last_name creator_name
202 , lkup.meaning
203 , dtha.creation_date
204 , dtha.trx_currency
205 , dtha.contact_phone
206 , dtha.contact_email_address
207 , dtha.effective_start_date
208 , dtha.days_covered,
209 dtha.transaction_status
210 from dpp_transaction_headers_all dtha
211 , ap_suppliers asp
212 , jtf_rs_resource_extns creator
213 , dpp_lookups lkup
214 , ap_supplier_sites_all assa
215 where dtha.transaction_header_id = pc_header_id
216 and dtha.vendor_id = asp.vendor_id
217 and asp.vendor_id = assa.vendor_id
218 and assa.vendor_site_id = dtha.vendor_site_id
219 and assa.org_id = dtha.org_id
220 and dtha.last_updated_by = creator.resource_id (+)
221 and dtha.transaction_status = lkup.lookup_code
222 and lkup.lookup_type = 'DPP_TRANSACTION_STATUSES';
223
224 cursor lc_get_notes(pc_entity_type varchar2, pc_transaction_header_id number) is
225 select notes_detail
226 from jtf_notes_vl
227 where source_object_code = pc_entity_type
228 AND SOURCE_OBJECT_ID = pc_transaction_header_id
229 AND NOTE_STATUS in ('E' , 'I') -- only publish notes and also Public
230 ORDER BY CREATION_DATE DESC;
231
232 cursor lc_last_approver_name (pc_entity_type varchar2, pc_transaction_header_id number) is
233 SELECT res.source_first_name || ' '|| res.source_last_name
234 FROM jtf_rs_resource_extns res, dpp_approval_access dac
235 where res.user_id = dac.approver_id
236 and dac.object_type = pc_entity_type
237 and dac.object_id = pc_transaction_header_id
238 and dac.approval_access_id = ( select max(approval_access_id)
239 from dpp_approval_access
240 where dac.object_type = pc_entity_type
241 and dac.object_id = pc_transaction_header_id );
242
243 cursor lc_media_name ( pc_media_id number) is
244 select media_type_name from
245 ams_media_vl
246 where media_id = pc_media_id;
247
248
249 cursor lc_get_function_id (pc_func_name varchar2) is
250 select function_id from fnd_form_functions where function_name = pc_func_name ;
251
252
253 BEGIN
254
255 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
256 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
257 'dpp.plsql.DPP_REQUEST_STATUS_PVT.Set_Request_Message.start',
258 'Item type:' || p_itemtype || 'Item key:' || p_itemkey || '. Entity id: ' ||
259 p_transaction_header_id || '. Status:' || p_status );
260 end if;
261
262 open lc_get_request_details (pc_header_id => p_transaction_header_id);
263
264 fetch lc_get_request_details into l_transaction_header_id
265 , l_transaction_number
266 , l_ref_document_number
267 , l_vendor_name
268 , l_vendor_site_code
269 , l_contact_name
270 , l_creator_name
271 , l_transaction_status
272 , l_creation_date
273 , l_currency
274 -- , l_decline_code
275 -- , l_return_code
276 , l_contact_phone
277 , l_contact_email
278 , l_start_date
279 , l_days_covered
280 ,l_status;
281
282 close lc_get_request_details;
283
284 if p_itemtype = 'DPPTXAPP' then
285 l_note_type := 'PRICE PROTECTION';
286 end if;
287
288 open lc_get_notes(pc_entity_type => l_note_type, pc_transaction_header_id => p_transaction_header_id);
289 fetch lc_get_notes into l_notes_clob;
290 close lc_get_notes;
291
292
293 l_notes_varchar := dbms_lob.substr(lob_loc => l_notes_clob, amount => l_note_size, offset => 1);
294
295 if p_itemtype = 'DPPTXAPP' then
296 l_request_type := 'PRICE PROTECTION';
297 end if;
298
299 open lc_last_approver_name(pc_entity_type => l_request_type, pc_transaction_header_id => p_transaction_header_id);
300 fetch lc_last_approver_name into l_last_approver_name;
301 close lc_last_approver_name;
302
303 if p_itemtype = 'DPPTXAPP' then
304
305 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'inside Notification ' || p_itemKey );
306 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Phone number is ' || l_contact_phone );
307
308
309 wf_engine.SetItemAttrText( itemtype => p_itemtype,
310 itemkey => p_itemKey,
311 aname => 'SUPPLIER_NAME',
312 avalue => l_vendor_name);
313
314 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_vendor_name ' || l_vendor_name );
315
316
317 wf_engine.SetItemAttrText( itemtype => p_itemtype,
318 itemkey => p_itemKey,
319 aname => 'SUPPLIER_SITE_CODE',
320 avalue => l_vendor_site_code);
321
322 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_vendor_site_code ' || l_vendor_site_code );
323
324 wf_engine.SetItemAttrText( itemtype => p_itemtype,
325 itemkey => p_itemKey,
326 aname => 'SUPPLIER_CONTACT',
327 avalue => l_contact_name
328 );
329
330 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_contact_name ' || l_contact_name );
331
332 wf_engine.SetItemAttrText( itemtype => p_itemtype,
333 itemkey => p_itemKey,
334 aname => 'SUPPLIER_CONTACT_PHONE',
335 avalue => l_contact_phone);
336
337 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_contact_phone ' || l_contact_phone );
338
339 /* wf_engine.SetItemAttrDate( itemtype => p_itemtype,
340 itemkey => p_itemKey,
341 aname => 'SUPPLIER_CONTACT_MAIL',
342 avalue => l_contact_email); */
343
344 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_contact_email ' || l_contact_email );
345 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_transaction_header_id is ' || l_transaction_header_id );
346
347 wf_engine.SetItemAttrText( itemtype => p_itemtype,
348 itemkey => p_itemKey,
349 aname => 'TRANSACTION_HEADER_ID',
350 avalue => l_transaction_header_id);
351
352 wf_engine.SetItemAttrText( itemtype => p_itemtype,
353 itemkey => p_itemKey,
354 aname => 'TRANSACTION_NUMBER',
355 avalue => l_transaction_number);
356
357 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_ref_document_number is ' || l_ref_document_number );
358
359 wf_engine.SetItemAttrText( itemtype => p_itemtype,
360 itemkey => p_itemKey,
361 aname => 'REF_DOCUMENT_NUMBER',
362 avalue => l_ref_document_number);
363
364 wf_engine.SetItemAttrText( itemtype => p_itemtype,
365 itemkey => p_itemKey,
366 aname => 'CURRENCY',
367 avalue => l_currency);
368
369 wf_engine.SetItemAttrText( itemtype => p_itemtype,
370 itemkey => p_itemKey,
371 aname => 'TRANSACTION_STATUS',
372 avalue => l_transaction_status);
373
374 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_start_date is ' || l_start_date );
375
376 wf_engine.SetItemAttrText( itemtype => p_itemtype,
377 itemkey => p_itemKey,
378 aname => 'EFFECTIVE_START_DATE',
379 avalue => l_start_date);
380
381 wf_engine.SetItemAttrText( itemtype => p_itemtype,
382 itemkey => p_itemKey,
383 aname => 'DAYS_COVERED',
384 avalue => l_days_covered);
385
386 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_days_covered is ' || l_days_covered );
387
388 open lc_get_function_id(pc_func_name => 'DPP_TXNUPDPG');
389 fetch lc_get_function_id into l_function_id;
390 close lc_get_function_id;
391
392 l_login_url := fnd_run_function.get_run_function_url
393 (l_function_id,
394 -1,
395 -1,
396 0,
397 'DPPReqFrmTxnHdrId=' || p_transaction_header_id );
398
399 wf_engine.SetItemAttrText( itemtype => p_itemtype,
400 itemkey => p_itemKey,
401 aname => 'USER_LOGIN_URL',
402 avalue => l_login_url );
403
404
405
406 l_user_dtail_url :=
407 '/OA_HTML/OA.jsp?akRegionApplicationId=9000'|| '&'||'OAFunc=DPP_TXNUPDPG'||'&'||
408 'DPPReqFrmTxnHdrId=' || p_transaction_header_id ||'&'||
409 'DPPReqFrmTgtTb=INVTB'||'&'||
410 'DPPReqFrmFuncName=DPP_TXNUPDPG'||'&'||
411 'addBreadCrumb=Y'
412 --||'&'||'retainAM=Y'
413 ;
414
415
416 -- Setting the attribute value for updated projects URL
417 wf_engine.SetItemAttrText
418 ( itemtype => p_itemtype,
419 itemkey => p_itemKey,
420 aname => 'USER_DTAIL_URL',
421 avalue => l_user_dtail_url
422 );
423
424 if l_decline_code IS NOT NULL THEN
425 l_decline_meaning := OZF_Utility_PVT.get_lookup_meaning('DPP_TRANSACTION_DECLINE_CODE',l_decline_code);
426 wf_engine.SetItemAttrText( itemtype => p_itemtype,
427 itemkey => p_itemKey,
428 aname => 'DECLINE_REASON',
429 avalue => l_decline_meaning);
430 END IF;
431
432 end if;
433 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
434 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
435 'dpp.plsql.DPP_REQUEST_STATUS_PVT.Set_Request_Message.end', 'Exiting');
436 end if;
437
438 END;
439 END DPP_REQUEST_STATUS_PVT;