1 PACKAGE BODY PON_WF_UTL_PKG AS
2 /* $Header: PONWFUTB.pls 120.20 2007/08/30 21:06:44 rbairraj ship $ */
3 -- Local private functions
4
5 FUNCTION get_base_supplier_url RETURN VARCHAR2;
6 FUNCTION get_base_buyer_url RETURN VARCHAR2;
7 FUNCTION GET_NOTIF_PREFERENCE ( p_wf_message_name IN WF_MESSAGES.NAME%TYPE,
8 p_doctype IN PON_AUC_DOCTYPES.DOCTYPE_GROUP_NAME%TYPE)
9 RETURN VARCHAR2;
10
11
12 /*
13 Function: get_base_supplier_url
14 Parameters: None
15 Returns: the base supplier url
16 Sample output: http://server01:4761/OA_HTML/OA.jsp
17 */
18
19 FUNCTION get_base_supplier_url RETURN VARCHAR2 IS
20
21 l_def_ext_user_resp VARCHAR2(240);
22 l_application_id NUMBER;
23 l_responsibility_id NUMBER;
24 l_ext_fwk_agent VARCHAR2(240);
25
26 -- First try to get the Sourcing External Framework Agent.
27 -- If not set, then get the responsibility associated with the
28 -- 'Sourcing Default Responsibility for External User' profile option
29 BEGIN
30 --
31 -- Access the Sourcing external APPS_FRAMEWORK_AGENT
32 --
33 l_ext_fwk_agent := FND_PROFILE.value('PON_EXT_APPS_FRAMEWORK_AGENT');
34 --
35 -- If the profile is not set, then try the default responsibility approach
36 --
37 IF (l_ext_fwk_agent IS NULL) THEN
38 --
39 l_ext_fwk_agent := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
40 END IF;
41 --
42 -- add OA_HTML/OA.jsp to the profile value
43 --
44 IF ( l_ext_fwk_agent IS NOT NULL ) THEN
45 --
46 IF ( substr(l_ext_fwk_agent, -1, 1) = '/' ) THEN
47 RETURN l_ext_fwk_agent || 'OA_HTML/OA.jsp';
48 ELSE
49 RETURN l_ext_fwk_agent || '/' || 'OA_HTML/OA.jsp';
50 END IF;
51 --
52 -- No profiles are setup so return nothing...
53 --
54 ELSE
55 RETURN '';
56 END IF;
57 EXCEPTION
58 WHEN OTHERS THEN
59 RETURN '';
60 END get_base_supplier_url;
61
62
63 /*
64 Function: get_base_buyer_url
65 Parameters: None
66 Returns: the base buyer url
67 Sample output: http://qapache.us.oracle.com:4761/OA_HTML/OA.jsp
68 */
69
70 FUNCTION get_base_buyer_url RETURN VARCHAR2 IS
71
72 l_base_url VARCHAR2(240);
73
74 l_api_name CONSTANT VARCHAR2(30) := 'get_base_buyer_url';
75 BEGIN
76 -- Bug:6268452. Due to DMZ issue, we need to make sure that Buyer's URL come from internal website. Since Site level Profile always maintain the internal URL, so we get the site level profile first.
77 l_base_url := get_site_level_profile_value('APPS_FRAMEWORK_AGENT');
78
79 IF (g_fnd_debug = 'Y') THEN
80 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
81 FND_LOG.string(log_level => FND_LOG.level_statement,
82 module => g_module_prefix || l_api_name,
83 message => 'After calling get_site_level_profile_value. l_base_url='
84 || l_base_url);
85 END IF;
86 END IF;
87
88 if (l_base_url is null) then
89 l_base_url := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
90 IF (g_fnd_debug = 'Y') THEN
91 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
92 FND_LOG.string(log_level => FND_LOG.level_statement,
93 module => g_module_prefix || l_api_name,
94 message => 'get_site_level_profile return NULL. After calling FND_PROFILE.value(); l_base_url='
95 || l_base_url);
96 END IF;
97 END IF;
98
99 end if;
100
101 IF ( substr(l_base_url, -1, 1) = '/' ) THEN
102 RETURN l_base_url || 'OA_HTML/OA.jsp';
103 ELSE
104 RETURN l_base_url || '/' || 'OA_HTML/OA.jsp';
105 END IF;
106
107 RETURN l_base_url || 'OA_HTML/OA.jsp';
108
109 END get_base_buyer_url;
110
111
112 /*
113 Function: get_page_url
114 Parameters: None
115 Returns: the complete url to access an OA page
116 */
117
118 FUNCTION get_page_url (p_url_parameters_tab url_parameters_tab
119 ,p_notif_performer VARCHAR2)
120 RETURN VARCHAR2 IS
121
122 i PLS_INTEGER;
123 l_page_url VARCHAR2(2000);
124
125 BEGIN
126
127 -- to get the base URL
128
129 IF p_notif_performer = 'BUYER' THEN
130 l_page_url := get_base_buyer_url;
131 ELSE
132 l_page_url := get_base_supplier_url;
133 END IF;
134
135
136 -- appending each parameter as passed in
137 FOR i IN p_url_parameters_tab.FIRST..p_url_parameters_tab.LAST
138 LOOP
139 IF (i = 1) THEN
140 l_page_url := l_page_url || '?';
141 ELSE
142 l_page_url := l_page_url || '&';
143 END IF;
144
145 l_page_url := l_page_url || p_url_parameters_tab(i).name || '=' || p_url_parameters_tab (i).value;
146 END LOOP;
147 /* Bug 3290344 removed call to UTL_URL.escape function specific to Oracle 9i database */
148 RETURN l_page_url;
149
150 END get_page_url;
151
152
153 /*
154 Procedure: set the workflow notification header attributes
155 Parameters:
156 Description: set the workflow notification header attributes
157 Comments: #HDR_NEG_TP_NAME, -> PREPARER_TP_NAME
158 #HDR_NEG_TITLE, -> AUCTION_TITLE
159 #HDR_NEG_NUMBER, -> DOC_NUMBER
160 #FROM_ROLE, -> PREPARER_TP_CONTACT_NAME
161 The #FROM_ROLE attribute is the Preparer Trading Partner Contact Name in
162 98% of the cases. Notifications whose #FROM_ROLE differs from this have to
163 set this attribute in the calling procedure
164 */
165
166 PROCEDURE set_hdr_attributes (p_itemtype IN VARCHAR2
167 ,p_itemkey IN VARCHAR2
168 ,p_auction_tp_name IN VARCHAR2
169 ,p_auction_title IN VARCHAR2
170 ,p_document_number IN VARCHAR2
171 ,p_auction_tp_contact_name IN VARCHAR2) IS
172 BEGIN
173
174 /* Setting the Company header attribute */
175 wf_engine.SetItemAttrText(itemtype => p_itemtype
176 ,itemkey => p_itemkey
177 ,aname => 'PREPARER_TP_NAME'
178 ,avalue => p_auction_tp_name);
179
180 /* Setting the negotiation title header attribute */
181 wf_engine.SetItemAttrText(itemtype => p_itemtype
182 ,itemkey => p_itemkey
183 ,aname => 'AUCTION_TITLE'
184 ,avalue => pon_auction_pkg.replaceHtmlChars(p_auction_title));
185
186 /* Setting the negotiation document number attribute */
187 wf_engine.SetItemAttrText(itemtype => p_itemtype
188 ,itemkey => p_itemkey
189 ,aname => 'DOC_NUMBER'
190 ,avalue => p_document_number);
191
192 /* Setting the #From role attribute */
193 wf_engine.SetItemAttrText (itemtype => p_itemtype
194 ,itemkey => p_itemkey
195 ,aname => 'PREPARER_TP_CONTACT_NAME'
196 ,avalue => p_auction_tp_contact_name);
197
198 END set_hdr_attributes;
199
200 FUNCTION get_menu_function_context (p_notif_performer IN VARCHAR2)
201 RETURN menu_function_parameter_rec IS
202
203 l_menu_function_parameter_rec menu_function_parameter_rec;
204
205 BEGIN
206 IF p_notif_performer = 'BUYER' THEN
207 l_menu_function_parameter_rec.OAHP := 'PON_SRC_SUPER_USER_HOME';
208 l_menu_function_parameter_rec.OASF := 'PON_SOURCING_BUYER';
209 ELSE
210 l_menu_function_parameter_rec.OAHP := 'PON_SRC_SUPPLIER_USER_HOME';
211 l_menu_function_parameter_rec.OASF := 'PON_SOURCING_SUPPLIER';
212 END IF;
213 RETURN l_menu_function_parameter_rec;
214
215 END get_menu_function_context;
216
217
218 /*
219 * Function: get_dest_page_url
220 * Parameters: p_dest_func - the final destination page
221 * p_notif_performer - the recipient of the notification
222 * p_redirect_func - Review and Submit page parameter
223 * Returns: the url for the redirect page
224 * This has 3 parameters
225 */
226 FUNCTION get_dest_page_url (p_dest_func IN VARCHAR2
227 ,p_notif_performer IN VARCHAR2)
228
229 RETURN VARCHAR2 IS
230
231 l_url_parameters_tab url_parameters_tab;
232 l_menu_function_parameter_rec menu_function_parameter_rec;
233
234 BEGIN
235
236 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
237 FND_LOG.string(log_level => FND_LOG.level_statement,
238 module => g_module_prefix || 'get_dest_page_url',
239 message => 'Entered the procedure with p_dest_func : ' || p_dest_func || '; p_notif_performer : ' || p_notif_performer);
240 END IF; --}
241
242 l_menu_function_parameter_rec := get_menu_function_context(p_notif_performer => p_notif_performer);
243
244 -- This is the redirect page which will get these parameters and redirect
245 -- to the final page
246 l_url_parameters_tab(1).name := 'OAFunc';
247 l_url_parameters_tab(1).value := 'PON_NOTIF_LINK_REDIRECT';
248 l_url_parameters_tab(2).name := 'OAHP';
249 l_url_parameters_tab(2).value := l_menu_function_parameter_rec.OAHP;
250 l_url_parameters_tab(3).name := 'OASF';
251 l_url_parameters_tab(3).value := l_menu_function_parameter_rec.OASF;
252 l_url_parameters_tab(4).name := 'destFunc';
253 l_url_parameters_tab(4).value := p_dest_func;
254
255 -- This will be replaced by the actual notification id during runtime
256 l_url_parameters_tab(5).name := 'notificationId';
257 l_url_parameters_tab(5).value := '&#NID';
258
259 --Bug 6369383 : Add language Code
260 -- This fix assumes that the caller of this procedure sets the language appropriately
261 -- before making the call. The userenv language will be taken and set as the language code
262
263 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
264 FND_LOG.string(log_level => FND_LOG.level_statement,
265 module => g_module_prefix || 'get_dest_page_url',
266 message => 'Adding language_code ; fnd_global.current_language : ' || fnd_global.current_language);
267 END IF; --}
268
269 l_url_parameters_tab(6).name := 'language_code';
270 l_url_parameters_tab(6).value := fnd_global.current_language; --userenv('LANG');
271
272
273 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
274 FND_LOG.string(log_level => FND_LOG.level_statement,
275 module => g_module_prefix || 'get_dest_page_url',
276 message => 'fnd_global.current_language : ' || fnd_global.current_language);
277 END IF; --}
278
279 RETURN get_page_url(p_url_parameters_tab => l_url_parameters_tab
280 ,p_notif_performer => p_notif_performer);
281
282 END get_dest_page_url;
283
284
285 /*
286 * Procedure: get_dest_page_params
287 * Purpose: retrieve WF item attribute values for a given item type and item key. This
288 * procedure is being called from NotificationLinkRedirectAMImpl.java to build
289 * the final destination page url.
290 *
291 * Parameters: p_ntf_id - Notification Id
292 * p_dest_page - final destination page
293 * x_auction_id - auction id
294 * x_site_id - site id
295 * x_bid_number - bid number
296 * x_doc_type_id - document type id
297 * x_reviewpg_redirect_func - redirect func parameter for Review and Submit page
298 * x_neg_deleted - Is negotiation deleted (hard delete)
299 *
300 * The following is the list of destination pages with appropriate parameters needed to launch.
301 * -------------------------------------------------------------------------
302 * PAGE# PAGE_NAME PAGE_PARAMETERS
303 * -------------------------------------------------------------------------
304 * PAGE-1: Negotiation Summary AuctionId; SiteId
305 * PAGE-2: View Net Changes current_auction_id
306 * PAGE-3: New Round Summary AuctionId
307 * PAGE-4: Review and Submit from;redirectFunc; auctionHeaderId
308 * PAGE-5: Allocation by Item auction_header_id;docTypeId
309 * PAGE-6: Award Summary retainAM;addBreadCrumb; AuctionId
310 * PAGE-7: View Quote auction_id; bid_number
311 * PAGE-8: Allocation Summary auction_header_id;docTypeId; From
312 * PAGE-9: Acknowledge participation auction_id; SiteId
313 * ------------------------------------------------------------------------- */
314
315
316 PROCEDURE get_dest_page_params (
317 p_ntf_id IN NUMBER,
318 p_dest_page IN VARCHAR2,
319 x_auction_id OUT NOCOPY NUMBER,
320 x_site_id OUT NOCOPY NUMBER,
321 x_bid_number OUT NOCOPY NUMBER,
322 x_doc_type_id OUT NOCOPY NUMBER,
323 x_reviewpg_redirect_func OUT NOCOPY VARCHAR2,
324 x_request_id OUT NOCOPY NUMBER,
325 x_DocumentNumber OUT NOCOPY VARCHAR2,
326 x_entry_id OUT NOCOPY NUMBER,
327 x_message_type OUT NOCOPY VARCHAR2,
328 x_discussion_id OUT NOCOPY NUMBER,
329 x_neg_deleted OUT NOCOPY VARCHAR2) is
330 CURSOR wf_item_cur IS
331 SELECT item_type,
332 item_key
333 FROM wf_item_activity_statuses
334 WHERE notification_id = p_ntf_id;
335
336
337 -- The following cursor is to parse the CONTEXT string
338 -- in the wf_notifications table to fetch the item type and item key.
339 -- The format for the context string is ITEMTYPE:ITEM_KEY:OTHER
340 -- For eg: The context PONAUCT:7136-1466:6566 will return
341 -- PONAUCT as ItemType, and 7136-1466 as ItemKey.
342 CURSOR wf_notif_context_cur IS
343 SELECT SUBSTR(context,1,INSTR(context,':',1)-1),
344 SUBSTR(context,INSTR(context,':')+1,
345 (INSTR(context,':',1,2) - INSTR(context,':')-1)),
346 message_name
347 FROM wf_notifications
348 WHERE notification_id = p_ntf_id;
349
350 p_itemtype WF_ITEM_ACTIVITY_STATUSES.item_type%TYPE; -- VARCHAR2(8)
351 p_itemkey WF_ITEM_ACTIVITY_STATUSES.item_key%TYPE; -- VARCHAR2(240)
352
353 p_message_name wf_notifications.message_name%TYPE;
354
355 BEGIN
356 x_bid_number := -1;
357 x_site_id := -1;
358 x_request_id := -1;
359 x_DocumentNumber :='NOTSPECIFIED';
360
361 -- Fetch the item_type and item_key values from
362 -- wf_item_activity_statuses for a given notification_id.
363 OPEN wf_item_cur;
364 FETCH wf_item_cur INTO p_itemtype, p_itemkey;
365 CLOSE wf_item_cur;
366
367 -- If the wf_item_activity_statuses does not contain an entry,
368 -- then parse the wf_notifications.context field to
369 -- get the item_type and item_key values for a given notification_id.
370 IF ((p_itemtype IS NULL) AND (p_itemkey IS NULL))
371 THEN
372 OPEN wf_notif_context_cur;
373 FETCH wf_notif_context_cur INTO p_itemtype, p_itemkey, p_message_name;
374 CLOSE wf_notif_context_cur;
375
376 END IF;
377
378
379 -- Existent code had auction id item attribute being defined with different names
380 -- accross the existent item types. Reason why we read different item attributes
381 -- to get auction id value.
382 -- From now on, any new item attribute that gets created to hold auction id
383 -- in new item types should have its item attribute name named 'AUCTION_ID' for
384 -- consistency
385 IF (p_itemtype = 'PONAPPRV' or p_itemtype = 'PONAWAPR') THEN
386 x_auction_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
387 itemkey => p_itemkey,
388 aname => 'AUCTION_HEADER_ID');
389 ELSE
390 x_auction_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
391 itemkey => p_itemkey,
392 aname => 'AUCTION_ID');
393 END IF;
394 IF (p_itemtype = 'PONCNCT' ) THEN
395 x_request_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
396 itemkey => p_itemkey,
397 aname => 'REQUEST_ID');
398 x_documentnumber := wf_engine.GetItemAttrText (itemtype => p_itemtype,
399 itemkey => p_itemkey,
400 aname => 'DOC_NUMBER');
401 END IF;
402
403
404 -- getting parameters to access Acknowledgment Participation page
405 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
406 IF (p_dest_page = 'PON_NEG_SUMMARY' or p_dest_page = 'PONRESAPN_ACKPARTICIPATN') THEN
407
408 IF (p_message_name = 'NEGOTIATION_EXTENDED' or p_message_name = 'NEGOTIATION_SHORTENED') THEN
409
410 select number_value
411 into x_site_id
412 from WF_NOTIFICATION_ATTRIBUTES
413 where notification_id = p_ntf_id and
414 name = 'VENDOR_SITE_ID';
415
416 ELSE
417
418 x_site_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
419 itemkey => p_itemkey,
420 aname => 'VENDOR_SITE_ID');
421 END IF;
422
423 END IF;
424
425 -- getting parameters needed to access View Quote page
426 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
427 IF p_dest_page = 'PONRESENQ_VIEWBID' or p_dest_page = 'PONENQMGDR_MANAGEDRAFT' or p_dest_page = 'PONENQMGDR_MANAGEDRAFT_SURROG' THEN
428 x_bid_number := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
429 itemkey => p_itemkey,
430 aname => 'BID_ID');
431 END IF;
432
433 -- getting parameters needed to access Concurrent Errors page
434 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
435 IF p_dest_page = 'PON_CONCURRENT_ERRORS' THEN
436 x_bid_number := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
437 itemkey => p_itemkey,
438 aname => 'BID_ID');
439 END IF;
440
441
442
443 -- getting parameters to access Allocation by Item or Allocation Summary pages
444 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
445 IF (p_dest_page = 'PONCPOABI_ALLOCATEBYITEM' or p_dest_page = 'PONCPOSUM_ALLOCSUMMARY') THEN
446 x_doc_type_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
447 itemkey => p_itemkey,
448 aname => 'DOCTYPE_ID');
449 END IF;
450
451 -- getting parameters to access Review and Submit page
452 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
453 IF (p_dest_page = 'PON_NEG_CRT_HEADER') THEN
454 x_reviewpg_redirect_func := wf_engine.GetItemAttrText (itemtype => p_itemtype,
455 itemkey => p_itemkey,
456 aname => 'REVIEWPG_REDIRECTFUNC');
457 END IF;
458
459
460 IF (p_dest_page = 'PON_VIEW_MESSAGE_DETAILS' ) THEN
461 x_entry_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
462 itemkey => p_itemkey,
463 aname => 'MESSAGE_ENTRY_ID');
464 x_message_type := wf_engine.GetItemAttrText (itemtype => p_itemtype,
465 itemkey => p_itemkey,
466 aname => 'MESSAGE_TYPE');
467 x_discussion_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
468 itemkey => p_itemkey,
469 aname => 'DISCUSSION_ID');
470 END IF;
471
472 BEGIN
473 SELECT 'N'
474 INTO x_neg_deleted
475 FROM PON_AUCTION_HEADERS_ALL
476 WHERE auction_header_id = x_auction_id;
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 x_neg_deleted := 'Y';
480 END;
481
482 EXCEPTION
483 WHEN OTHERS THEN
484 IF ( wf_item_cur%ISOPEN ) THEN
485 CLOSE wf_item_cur;
486 RAISE;
487 END IF;
488
489 IF ( wf_notif_context_cur%ISOPEN ) THEN
490 CLOSE wf_notif_context_cur;
491 RAISE;
492 END IF;
493
494 END get_dest_page_params;
495
496 /*
497 Function: get_isp_supplier_register_url
498 Parameters: None
499 Returns: the url to the iSP Supplier Register page
500 */
501
502 FUNCTION get_isp_supplier_register_url (p_registration_key IN VARCHAR2
503 ,p_language_code IN VARCHAR2)
504 RETURN VARCHAR2 IS
505
506 l_ext_fwk_agent VARCHAR2(2000);
507 l_def_ext_user_resp VARCHAR2(240);
508 l_application_id NUMBER;
509 l_responsibility_id NUMBER;
510
511 BEGIN
512
513 --
514 -- Access the Sourcing external APPS_FRAMEWORK_AGENT
515 --
516 l_ext_fwk_agent := FND_PROFILE.value('PON_EXT_APPS_FRAMEWORK_AGENT');
517 --
518 -- If the profile is not set, then try the default responsibility approach
519 --
520 IF (l_ext_fwk_agent IS NULL) THEN
521 --
522 l_ext_fwk_agent := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
523 END IF;
524
525 IF ( substr(l_ext_fwk_agent, -1, 1) <> '/' ) THEN
526 l_ext_fwk_agent := l_ext_fwk_agent||'/';
527 END IF;
528 l_ext_fwk_agent := l_ext_fwk_agent || 'OA_HTML/jsp/pos/registration/RegistrationReply.jsp?registrationKey=' || p_registration_key || '&' || 'regLang=' || p_language_code;
529
530 /* Bug 3290344 removed call to UTL_URL.escape function specific to Oracle 9i database */
531 RETURN l_ext_fwk_agent;
532
533 END get_isp_supplier_register_url;
534
535 PROCEDURE GetConcProgramType(itemtype in varchar2,
536 itemkey in varchar2,
537 actid in number,
538 uncmode in varchar2,
539 resultout out NOCOPY varchar2) IS
540
541 BEGIN
542
543 resultout := wf_engine.GetItemAttrText (itemtype => itemtype,
544 itemkey => itemkey,
545 aname => 'CONCPROGRAM_TYPE');
546
547 END GetConcProgramType;
548
549
550 Procedure ReportConcProgramStatus(
551 p_request_id in Number,
552 p_messagetype in Varchar2,
553 p_RecepientUsername in Varchar2,
554 p_recepientType in Varchar2,
555 p_auction_header_id in number,
556 p_ProgramTypeCode in Varchar2,
557 p_DestinationPageCode in Varchar2,
558 p_bid_number in Number,
559 p_max_good_line_num in number default -1,
560 p_last_goodline_worksheet in Varchar2 default ''
561 ) is
562 l_item_type Varchar2(8) := 'PONCNCT';
563 l_item_key Varchar2(240);
564 l_language_code VARCHAR2(5);
565 l_msg_suffix VARCHAR2(10);
566 l_auction_title pon_auction_headers_all.auction_title%type;
567 l_document_number pon_auction_headers_all.document_number%type;
568 l_trading_partner_contact_name pon_auction_headers_all.trading_partner_contact_name%type;
569 l_trading_partner_name pon_auction_headers_all.trading_partner_name%type;
570 l_doctype_group_name pon_auc_doctypes.doctype_group_name%type;
571 l_doctypemsgval Varchar2(80);
572 BEGIN
573
574 select to_char(p_auction_header_id) || '-' ||
575 to_char(p_request_id)
576 into l_item_key from dual;
577 -- set the db session language
578 PON_PROFILE_UTIL_PKG.get_wf_language(p_RecepientUsername, l_language_code);
579 PON_AUCTION_PKG.set_session_language(null, l_language_code);
580 SELECT
581 auc.trading_partner_contact_name,
582 auc.trading_partner_name,
583 auc.auction_title,
584 auc.document_number,
585 dt.doctype_group_name
586 INTO
587 l_trading_partner_contact_name,
588 l_trading_partner_name,
589 l_auction_title,
590 l_document_number,
591 l_doctype_group_name
592 FROM
593 pon_auction_headers_all auc,
594 pon_auc_doctypes dt
595 WHERE
596 dt.doctype_id= auc.doctype_id
597 AND auc.auction_header_id = p_auction_header_id;
598
599 l_msg_suffix := PON_AUCTION_PKG.get_message_suffix(l_doctype_group_name);
600 l_doctypemsgval := PON_AUCTION_PKG.getmessage('PON_AUCTION',l_msg_suffix);
601 if p_messagetype ='S' then
602 wf_engine.CreateProcess(itemtype => l_item_type,
603 itemkey => l_item_key,
604 process => 'REPORT_SUCCESS');
605
606 else
607 wf_engine.CreateProcess(itemtype => l_item_type,
608 itemkey => l_item_key,
609 process => 'REPORT_FAILURE');
610 end if;
611 -- set standard notification header attributes
612 PON_WF_UTL_PKG.set_hdr_attributes(l_item_type,
613 l_item_key,
614 l_trading_partner_name,
615 l_auction_title,
616 l_document_number,
617 l_trading_partner_contact_name);
618
619 -- set other core attributes
620 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
621 itemkey => l_item_key,
622 aname => 'AUCTION_ID',
623 avalue => p_auction_header_id);
624
625 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
626 itemkey => l_item_key,
627 aname => 'BID_ID',
628 avalue => p_bid_number);
629
630 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
631 itemkey => l_item_key,
632 aname => 'REQUEST_ID',
633 avalue => p_request_id);
634
635 wf_engine.SetItemAttrText(itemtype => l_item_type,
636 itemkey => l_item_key,
637 aname => 'CONCPROGRAM_TYPE',
638 avalue => p_ProgramTypeCode);
639
640 wf_engine.SetItemAttrText(itemtype => l_item_type,
641 itemkey => l_item_key,
642 aname => 'DESTINATION_PAGE_CODE',
643 avalue => p_DestinationPageCode);
644
645 wf_engine.SetItemAttrText(itemtype => l_item_type,
646 itemkey => l_item_key,
647 aname => 'RECEPIENT_USERNAME',
648 avalue => p_RecepientUsername);
649
650 wf_engine.SetItemAttrText(itemtype => l_item_type,
651 itemkey => l_item_key,
652 aname => 'DESTINATION_URL',
653 avalue => get_dest_page_url(p_dest_func=>p_DestinationPageCode,
654 p_notif_performer=>p_recepientType));
655
656
657 wf_engine.SetItemAttrText(itemtype => l_item_type,
658 itemkey => l_item_key,
659 aname => 'NOTIFICATION_SUBJECT',
660 avalue => PON_AUCTION_PKG.getMessage('PONCPG' || p_messagetype || '_' || p_ProgramTypeCode,
661 l_msg_suffix,
662 'REQUEST_ID', --REQUEST_ID
663 to_char(p_request_id),
664 'DOC_TYPE',
665 l_doctypemsgval,
666 'DOC_NUMBER',
667 l_document_number,
668 'DOC_TITLE',
669 l_auction_title
670 ));
671
672 wf_engine.SetItemAttrText(itemtype => l_item_type,
673 itemkey => l_item_key,
674 aname => 'MAX_SUCCESS_LAST_BATCH_LINE_NO',
675 avalue => to_char(p_max_good_line_num));
676
677
678 wf_engine.SetItemAttrText(itemtype => l_item_type,
679 itemkey => l_item_key,
680 aname => 'LAST_SUCCESS_BATCH_WORKSHEET',
681 avalue => p_last_goodline_worksheet);
682
683 wf_engine.StartProcess(itemtype => l_item_type,
684 itemkey => l_item_key );
685 END ReportConcProgramStatus;
686
687
688
689 PROCEDURE GetLastLineNumberInBatch(itemtype in varchar2,
690 itemkey in varchar2,
691 actid in number,
692 uncmode in varchar2,
693 resultout out NOCOPY varchar2) IS
694
695 BEGIN
696
697 BEGIN
698 resultout := wf_engine.GetItemAttrText (itemtype => itemtype,
699 itemkey => itemkey,
700 aname => 'MAX_SUCCESS_LAST_BATCH_LINE_NO');
701 EXCEPTION
702 when others then resultout := -1;
703 END;
704
705
706 END GetLastLineNumberInBatch;
707
708 /*********/
709
710 PROCEDURE GetLastWorksheetInBatch(itemtype in varchar2,
711 itemkey in varchar2,
712 actid in number,
713 uncmode in varchar2,
714 resultout out NOCOPY varchar2) IS
715 BEGIN
716 BEGIN
717 resultout := wf_engine.GetItemAttrText (
718 itemtype => itemtype,
719 itemkey => itemkey,
720 aname => 'LAST_SUCCESS_BATCH_WORKSHEET');
721
722 EXCEPTION
723 WHEN OTHERS THEN
724 resultout := '';
725 END;
726
727 END GetLastWorksheetInBatch;
728
729 /*==============================================================================================
730 PROCEDURE : GET_NOTIF_PREFERENCE PUBLIC
731 PARAMETERS:
732 p_wf_message_name IN workflow message name of current notification
733 p_auction_id IN auction_header_id of current negotiation
734
735 COMMENT : this function was introduced as a part of the notification
736 subscriptions project in release-12. This function should be
737 invoked in order to determine whether we need to send a particular
738 notification to the buyer or supplier user. The UI to set the notification
739 preferences can be viewed or modified by accessing the page via Sourcing
740 Admin home page
741
742 ==============================================================================================*/
743
744
745 FUNCTION GET_NOTIF_PREFERENCE (
746 p_wf_message_name IN WF_MESSAGES.NAME%TYPE,
747 p_auction_id IN PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE) RETURN VARCHAR2
748 IS
749
750 l_document_type PON_AUC_DOCTYPES.DOCTYPE_GROUP_NAME%TYPE;
751 l_api_name CONSTANT VARCHAR2(30) := 'get_notif_preference_1';
752
753 BEGIN
754
755 IF (g_fnd_debug = 'Y') THEN
756 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
757 FND_LOG.string(log_level => FND_LOG.level_statement,
758 module => g_module_prefix || l_api_name,
759 message => 'BEGIN: Check WF preference for'
760 || p_wf_message_name || ' for auction '
761 || p_auction_id);
762 END IF;
763 END IF;
764
765
766 SELECT docTypes.DOCTYPE_GROUP_NAME
767 INTO l_document_type
768 FROM PON_AUCTION_HEADERS_ALL auctionHdr,
769 PON_AUC_DOCTYPES docTypes
770 WHERE auctionHdr.AUCTION_HEADER_ID = p_auction_id
771 AND auctionHdr.DOCTYPE_ID = docTypes.DOCTYPE_ID;
772
773 RETURN GET_NOTIF_PREFERENCE(p_wf_message_name, l_document_type);
774
775 EXCEPTION
776 --
777 -- Exception can come if the auction_header_id or the doctype
778 -- data is missing or the GET_NOTIF_PREFERENCE call returns
779 -- error. In all these cases it should raise the error as
780 -- this is an unnatural event
781 --
782 WHEN OTHERS THEN
783 IF (g_fnd_debug = 'Y') THEN
784 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
785 FND_LOG.string(log_level => FND_LOG.level_exception,
786 module => g_module_prefix || l_api_name,
787 message => 'EXCEPTION: Check WF preference for'
788 || p_wf_message_name || ' for auction '
789 || p_auction_id);
790 END IF;
791 END IF;
792 RAISE;
793
794 END GET_NOTIF_PREFERENCE;
795
796
797 /*==============================================================================================
798 PROCEDURE : GET_NOTIF_PREFERENCE PUBLIC
799 PARAMETERS:
800 p_wf_message_name IN workflow message name of current notification
801 p_doctype IN document type group name of current negotiation
802
803 COMMENT : this function was introduced as a part of the notification
804 subscriptions project in release-12. This function should be
805 invoked in order to determine whether we need to send a particular
806 notification to the buyer or supplier user. The UI to set the notification
807 preferences can be viewed or modified by accessing the page via Sourcing
808 Admin home page
809
810 ==============================================================================================*/
811
812
813 FUNCTION GET_NOTIF_PREFERENCE (
814 p_wf_message_name IN WF_MESSAGES.NAME%TYPE,
815 p_doctype IN PON_AUC_DOCTYPES.DOCTYPE_GROUP_NAME%TYPE)
816 RETURN VARCHAR2
817 IS
818
819 l_notif_pref VARCHAR2(3);
820 l_api_name CONSTANT VARCHAR2(30) := 'get_notif_preference_2';
821 l_yes CONSTANT VARCHAR2(2) := 'Y';
822 l_no CONSTANT VARCHAR2(2) := 'N';
823
824 BEGIN
825
826 IF (g_fnd_debug = 'Y') THEN
827 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
828
829 FND_LOG.string(log_level => FND_LOG.level_statement,
830 module => g_module_prefix || l_api_name,
831 message => 'BEGIN: Check WF preference for'
832 || p_wf_message_name || ' for doctype '
833 || p_doctype);
834 END IF;
835 END IF;
836
837
838 SELECT DECODE(P_DOCTYPE,SRC_AUCTION, notifGroups.AUCTION_SUBSCRIPTION_FLAG,
839 SRC_RFQ, notifGroups.RFQ_SUBSCRIPTION_FLAG,
840 SRC_RFI, notifGroups.RFI_SUBSCRIPTION_FLAG,
841 l_yes)
842 INTO l_notif_pref
843 FROM PON_NOTIF_SUBSCRIPTION_GROUPS notifGroups,
844 PON_NOTIF_GROUP_MEMBERS notifMessages
845 WHERE
846 notifGroups.NOTIF_GROUP_CODE = notifMessages.NOTIF_GROUP_CODE
847 AND notifMessages.NOTIF_MESSAGE_NAME = P_WF_MESSAGE_NAME;
848
849
850 IF (g_fnd_debug = 'Y') THEN
851 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
852 FND_LOG.string(log_level => FND_LOG.level_statement,
853 module => g_module_prefix || l_api_name,
854 message => 'BEGIN: Check WF preference for'
855 || p_wf_message_name || ' for doctype '
856 || p_doctype || ' with return value '
857 || l_notif_pref);
858 END IF;
859 END IF;
860
861 IF l_notif_pref = 'Y' THEN
862 l_notif_pref := l_yes;
863 ELSE
864 l_notif_pref := l_no;
865 END IF;
866
867 RETURN l_notif_pref;
868
869 EXCEPTION
870 WHEN NO_DATA_FOUND THEN
871 --
872 -- If there is no subscription for a message name then
873 -- the member data can be missing or it can be for some
874 -- not to be subscribed message. Hence, return true i.e.
875 -- the older behavior
876 --
877 IF (g_fnd_debug = 'Y') THEN
878 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
879 FND_LOG.string(log_level => FND_LOG.level_exception,
880 module => g_module_prefix || l_api_name,
881 message => 'EXCEPTION: NO DATA FOUND for message name:'
882 || p_wf_message_name || ' for doctype '
883 || p_doctype);
884 END IF;
885 END IF;
886 RETURN l_yes;
887
888 WHEN OTHERS THEN
889 --
890 -- Chances are rare but if any such error happens then it will simply
891 -- raise the error to the upper level
892 --
893 IF (g_fnd_debug = 'Y') THEN
894 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
895 FND_LOG.string(log_level => FND_LOG.level_exception,
896 module => g_module_prefix || l_api_name,
897 message => 'EXCEPTION:Check WF preference for '
898 || P_WF_MESSAGE_NAME
899 || ' for doctype '
900 || p_doctype);
901 END IF;
902 END IF;
903 RAISE;
904
905 END GET_NOTIF_PREFERENCE;
906
907
908 /*
909 Function: get_base_external_supplier_url
910 Parameters: None
911 Returns: the base external supplier url
912 Sample output: http://server01:4761/
913 */
914
915 FUNCTION get_base_external_supplier_url RETURN VARCHAR2 IS
916
917 l_def_ext_user_resp VARCHAR2(240);
918 l_application_id NUMBER;
919 l_responsibility_id NUMBER;
920 l_ext_fwk_agent VARCHAR2(240);
921
922 -- First try to get the Sourcing External Framework Agent.
923 -- If not set, then get the responsibility associated with the
924 -- 'Sourcing Default Responsibility for External User' profile option
925 BEGIN
926 --
927 -- Access the Sourcing external APPS_FRAMEWORK_AGENT
928 --
929 l_ext_fwk_agent := FND_PROFILE.value('PON_EXT_APPS_FRAMEWORK_AGENT');
930 --
931 -- If the profile is not set, then try the default responsibility approach
932 --
933 IF (l_ext_fwk_agent IS NULL) THEN
934 --
935 l_def_ext_user_resp := FND_PROFILE.value('PON_DEFAULT_EXT_USER_RESP');
936 --
937 IF (l_def_ext_user_resp IS NOT NULL) THEN
938 --
939 -- get the value of 'APPS_FRAMEWORK_AGENT' profile at this responsibility level
940 --
941 BEGIN
942 SELECT application_id, responsibility_id
943 INTO l_application_id, l_responsibility_id
944 FROM fnd_responsibility
945 WHERE responsibility_key = l_def_ext_user_resp
946 AND (end_date IS NULL OR end_date > sysdate);
947 --
948 l_ext_fwk_agent := FND_PROFILE.value_specific(
949 name => 'APPS_FRAMEWORK_AGENT',
950 responsibility_id => l_responsibility_id,
951 application_id => l_application_id );
952 --
953 EXCEPTION
954 WHEN OTHERS THEN
955 l_ext_fwk_agent := null;
956 END;
957 END IF;
958 END IF;
959 --
960 -- If still NULL, fall back to APPS_FRAMEWORK_AGENT
961 --
962 IF (l_ext_fwk_agent IS NULL) THEN
963 l_ext_fwk_agent := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
964 END IF;
965
966 RETURN l_ext_fwk_agent;
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 RETURN '';
971 END get_base_external_supplier_url;
972
973
974 FUNCTION get_site_level_profile_value(p_profile_name varchar2) RETURN VARCHAR2 IS
975
976 l_level_id NUMBER;
977 l_profile_value varchar2(240);
978
979 -- this cursor fetches profile option values
980 cursor profile_value(p_name varchar2, a_id number, l_id number, l_val number) is
981 select fpov.profile_option_value
982 from fnd_profile_options fpo,
983 fnd_profile_option_values fpov
984 where fpo.profile_option_name = p_name
985 and fpo.start_date_active <= sysdate
986 and nvl(fpo.end_date_active, sysdate) >= sysdate
987 and fpo.profile_option_id=fpov.profile_option_id
988 and fpov.application_id=a_id
989 and fpov.level_id=l_id
990 and fpov.level_value=l_val
991 and fpov.profile_option_value is not null;
992
993 BEGIN
994
995 l_level_id := 10001;
996 open profile_value(p_profile_name,0,l_level_id,0);
997 fetch profile_value into l_profile_value;
998
999 if (profile_value%NOTFOUND) then
1000 l_profile_value := NULL;
1001 end if; -- value_uas%NOTFOUND
1002
1003 close profile_value;
1004
1005 RETURN l_profile_value;
1006
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 RETURN NULL;
1010 END get_site_level_profile_value;
1011
1012 /*
1013 Function: get_base_internal_buyer_url
1014 Parameters: None
1015 Returns: the base internal buyer url
1016 Sample output: http://server01:4761/
1017 */
1018
1019 FUNCTION get_base_internal_buyer_url RETURN VARCHAR2 IS
1020 l_base_url VARCHAR2(240) := '';
1021 l_api_name CONSTANT VARCHAR2(30) := 'get_base_internal_buyer_url';
1022 BEGIN
1023 -- Bug:6261134. Due to DMZ issue, we need to make sure that Buyer's URL come from internal website. Since Site level Profile always maintain the internal URL, so we get the site level profile first.
1024 l_base_url := get_site_level_profile_value('APPS_FRAMEWORK_AGENT');
1025
1026 IF (g_fnd_debug = 'Y') THEN
1027 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1028 FND_LOG.string(log_level => FND_LOG.level_statement,
1029 module => g_module_prefix || l_api_name,
1030 message => 'After calling get_site_level_profile_value. l_base_url='
1031 || l_base_url);
1032 END IF;
1033 END IF;
1034
1035 IF (l_base_url is null) then
1036 l_base_url := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
1037 IF (g_fnd_debug = 'Y') THEN
1038 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1039 FND_LOG.string(log_level => FND_LOG.level_statement,
1040 module => g_module_prefix || l_api_name,
1041 message => 'get_site_level_profile return NULL. After calling FND_PROFILE.value(); l_base_url='
1042 || l_base_url);
1043 END IF;
1044 END IF;
1045
1046 END IF;
1047
1048 RETURN l_base_url;
1049
1050 END get_base_internal_buyer_url;
1051
1052 END PON_WF_UTL_PKG;
[Home] [Help]