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