1 PACKAGE BODY PON_WF_UTL_PKG AS
2 /* $Header: PONWFUTB.pls 120.23.12020000.3 2013/03/21 05:19:35 pamaniko 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 */
72 l_base_url VARCHAR2(240);
69
70 FUNCTION get_base_buyer_url RETURN VARCHAR2 IS
71
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'
199
196 ,avalue => p_auction_tp_contact_name);
197
198 END set_hdr_attributes;
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 --Bug 15991171 - removing language_code parameter from url
269 --l_url_parameters_tab(6).name := 'language_code';
270 --l_url_parameters_tab(6).value := fnd_global.current_language; --userenv('LANG');
271
272 --Bug 14486698 (FP of 14406948)
273 --Added URL parameters for Bread Crumbs
274 l_url_parameters_tab(6).name := 'addBreadCrumb';
275 l_url_parameters_tab(6).value := 'Y';
276
277
278 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
279 FND_LOG.string(log_level => FND_LOG.level_statement,
280 module => g_module_prefix || 'get_dest_page_url',
281 message => 'fnd_global.current_language : ' || fnd_global.current_language);
282 END IF; --}
283
284 RETURN get_page_url(p_url_parameters_tab => l_url_parameters_tab
285 ,p_notif_performer => p_notif_performer);
286
287 END get_dest_page_url;
288
289
290 /*
291 * Procedure: get_dest_page_params
292 * Purpose: retrieve WF item attribute values for a given item type and item key. This
293 * procedure is being called from NotificationLinkRedirectAMImpl.java to build
294 * the final destination page url.
295 *
296 * Parameters: p_ntf_id - Notification Id
297 * p_dest_page - final destination page
298 * x_auction_id - auction id
299 * x_site_id - site id
300 * x_bid_number - bid number
301 * x_doc_type_id - document type id
302 * x_reviewpg_redirect_func - redirect func parameter for Review and Submit page
303 * x_neg_deleted - Is negotiation deleted (hard delete)
304 *
305 * The following is the list of destination pages with appropriate parameters needed to launch.
306 * -------------------------------------------------------------------------
307 * PAGE# PAGE_NAME PAGE_PARAMETERS
308 * -------------------------------------------------------------------------
309 * PAGE-1: Negotiation Summary AuctionId; SiteId
310 * PAGE-2: View Net Changes current_auction_id
314 * PAGE-6: Award Summary retainAM;addBreadCrumb; AuctionId
311 * PAGE-3: New Round Summary AuctionId
312 * PAGE-4: Review and Submit from;redirectFunc; auctionHeaderId
313 * PAGE-5: Allocation by Item auction_header_id;docTypeId
315 * PAGE-7: View Quote auction_id; bid_number
316 * PAGE-8: Allocation Summary auction_header_id;docTypeId; From
317 * PAGE-9: Acknowledge participation auction_id; SiteId
318 * ------------------------------------------------------------------------- */
319
320
321 PROCEDURE get_dest_page_params (
322 p_ntf_id IN NUMBER,
323 p_dest_page IN VARCHAR2,
324 x_auction_id OUT NOCOPY NUMBER,
325 x_site_id OUT NOCOPY NUMBER,
326 x_bid_number OUT NOCOPY NUMBER,
327 x_doc_type_id OUT NOCOPY NUMBER,
328 x_reviewpg_redirect_func OUT NOCOPY VARCHAR2,
329 x_request_id OUT NOCOPY NUMBER,
330 x_DocumentNumber OUT NOCOPY VARCHAR2,
331 x_entry_id OUT NOCOPY NUMBER,
332 x_message_type OUT NOCOPY VARCHAR2,
333 x_discussion_id OUT NOCOPY NUMBER,
334 x_neg_deleted OUT NOCOPY VARCHAR2) is
335 CURSOR wf_item_cur IS
336 SELECT item_type,
337 item_key
338 FROM wf_item_activity_statuses
339 WHERE notification_id = p_ntf_id;
340
341
342 -- The following cursor is to parse the CONTEXT string
343 -- in the wf_notifications table to fetch the item type and item key.
344 -- The format for the context string is ITEMTYPE:ITEM_KEY:OTHER
345 -- For eg: The context PONAUCT:7136-1466:6566 will return
346 -- PONAUCT as ItemType, and 7136-1466 as ItemKey.
347 CURSOR wf_notif_context_cur IS
348 SELECT SUBSTR(context,1,INSTR(context,':',1)-1),
349 SUBSTR(context,INSTR(context,':')+1,
350 (INSTR(context,':',1,2) - INSTR(context,':')-1)),
351 message_name
352 FROM wf_notifications
353 WHERE notification_id = p_ntf_id;
354
355 p_itemtype WF_ITEM_ACTIVITY_STATUSES.item_type%TYPE; -- VARCHAR2(8)
356 p_itemkey WF_ITEM_ACTIVITY_STATUSES.item_key%TYPE; -- VARCHAR2(240)
357
358 p_message_name wf_notifications.message_name%TYPE;
359
360 BEGIN
361 x_bid_number := -1;
362 x_site_id := -1;
363 x_request_id := -1;
364 x_DocumentNumber :='NOTSPECIFIED';
365
366 -- Fetch the item_type and item_key values from
367 -- wf_item_activity_statuses for a given notification_id.
368 OPEN wf_item_cur;
369 FETCH wf_item_cur INTO p_itemtype, p_itemkey;
370 CLOSE wf_item_cur;
371
372 -- If the wf_item_activity_statuses does not contain an entry,
373 -- then parse the wf_notifications.context field to
374 -- get the item_type and item_key values for a given notification_id.
375 IF ((p_itemtype IS NULL) AND (p_itemkey IS NULL))
376 THEN
377 OPEN wf_notif_context_cur;
378 FETCH wf_notif_context_cur INTO p_itemtype, p_itemkey, p_message_name;
379 CLOSE wf_notif_context_cur;
380
381 END IF;
382
383
384 -- Existent code had auction id item attribute being defined with different names
385 -- accross the existent item types. Reason why we read different item attributes
386 -- to get auction id value.
387 -- From now on, any new item attribute that gets created to hold auction id
388 -- in new item types should have its item attribute name named 'AUCTION_ID' for
389 -- consistency
390
391 /* For QA Bug : 9182472 Removed if condition p_itemtype = 'PONAWAPR' */
392 IF p_itemtype = 'PONAPPRV' THEN
393
394 /* Added for Federal CLM solicitation amendments project.
395 * If CONF_DOC_AUC_HEAD_ID is set, then auction_header_id to launch any other page should be
396 * that of conformed document not that of amendment.
397 */
398 x_auction_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
399 itemkey => p_itemkey,
400 aname => 'CONF_DOC_AUC_HEAD_ID');
401 if nvl(x_auction_id,-1) = -1 then
402 x_auction_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
403 itemkey => p_itemkey,
404 aname => 'AUCTION_HEADER_ID');
405 else
406 x_documentnumber := wf_engine.GetItemAttrText (itemtype => p_itemtype,
407 itemkey => p_itemkey,
408 aname => 'CONF_DOC_NUMBER');
409 end if;
410 /* For QA Bug : 9182472 Added If p_itemtype = 'PONAWAPR' */
411 ELSIF p_itemtype = 'PONAWAPR' THEN
412 x_auction_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
413 itemkey => p_itemkey,
414 aname => 'AUCTION_HEADER_ID');
415 ELSE
416 x_auction_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
417 itemkey => p_itemkey,
418 aname => 'AUCTION_ID');
419 END IF;
420
421 IF (p_itemtype = 'PONCNCT' ) THEN
422 x_request_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
423 itemkey => p_itemkey,
424 aname => 'REQUEST_ID');
428 END IF;
425 x_documentnumber := wf_engine.GetItemAttrText (itemtype => p_itemtype,
426 itemkey => p_itemkey,
427 aname => 'DOC_NUMBER');
429
430
431 -- getting parameters to access Acknowledgment Participation page
432 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
433 IF (p_dest_page = 'PON_NEG_SUMMARY' or p_dest_page = 'PONRESAPN_ACKPARTICIPATN') THEN
434
435 IF (p_message_name = 'NEGOTIATION_EXTENDED' or p_message_name = 'NEGOTIATION_SHORTENED') THEN
436
437 select number_value
438 into x_site_id
439 from WF_NOTIFICATION_ATTRIBUTES
440 where notification_id = p_ntf_id and
441 name = 'VENDOR_SITE_ID';
442
443 ELSE
444
445 x_site_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
446 itemkey => p_itemkey,
447 aname => 'VENDOR_SITE_ID');
448 END IF;
449
450 END IF;
451
452 -- getting parameters needed to access View Quote page
453 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
454 IF p_dest_page = 'PONRESENQ_VIEWBID' or p_dest_page = 'PONENQMGDR_MANAGEDRAFT' or p_dest_page = 'PONENQMGDR_MANAGEDRAFT_SURROG' THEN
455 x_bid_number := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
456 itemkey => p_itemkey,
457 aname => 'BID_ID');
458 END IF;
459
460 -- getting parameters needed to access Concurrent Errors page
461 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
462 IF p_dest_page = 'PON_CONCURRENT_ERRORS' THEN
463 x_bid_number := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
464 itemkey => p_itemkey,
465 aname => 'BID_ID');
466 END IF;
467
468
469
470 -- getting parameters to access Allocation by Item or Allocation Summary pages
471 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
472 IF (p_dest_page = 'PONCPOABI_ALLOCATEBYITEM' or p_dest_page = 'PONCPOSUM_ALLOCSUMMARY') THEN
473 x_doc_type_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
474 itemkey => p_itemkey,
475 aname => 'DOCTYPE_ID');
476 END IF;
477
478 -- getting parameters to access Review and Submit page
479 -- constant parameters are being set in the NotificationLinkRedirectAMImpl.java
480 IF (p_dest_page = 'PON_NEG_CRT_HEADER') THEN
481 x_reviewpg_redirect_func := wf_engine.GetItemAttrText (itemtype => p_itemtype,
482 itemkey => p_itemkey,
483 aname => 'REVIEWPG_REDIRECTFUNC');
484 END IF;
485
486
487 IF (p_dest_page = 'PON_VIEW_MESSAGE_DETAILS' ) THEN
488 x_entry_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
489 itemkey => p_itemkey,
490 aname => 'MESSAGE_ENTRY_ID');
491 x_message_type := wf_engine.GetItemAttrText (itemtype => p_itemtype,
492 itemkey => p_itemkey,
493 aname => 'MESSAGE_TYPE');
494 x_discussion_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
495 itemkey => p_itemkey,
496 aname => 'DISCUSSION_ID');
497 END IF;
498
499 BEGIN
500 SELECT 'N'
501 INTO x_neg_deleted
502 FROM PON_AUCTION_HEADERS_ALL
503 WHERE auction_header_id = x_auction_id;
504 EXCEPTION
505 WHEN NO_DATA_FOUND THEN
506 x_neg_deleted := 'Y';
507 END;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 IF ( wf_item_cur%ISOPEN ) THEN
512 CLOSE wf_item_cur;
513 RAISE;
514 END IF;
515
516 IF ( wf_notif_context_cur%ISOPEN ) THEN
517 CLOSE wf_notif_context_cur;
518 RAISE;
519 END IF;
520
521 END get_dest_page_params;
522
523 /*
524 Function: get_isp_supplier_register_url
525 Parameters: None
526 Returns: the url to the iSP Supplier Register page
527 */
528
529 FUNCTION get_isp_supplier_register_url (p_registration_key IN VARCHAR2
530 ,p_language_code IN VARCHAR2)
531 RETURN VARCHAR2 IS
532
533 l_ext_fwk_agent VARCHAR2(2000);
534 l_def_ext_user_resp VARCHAR2(240);
535 l_application_id NUMBER;
536 l_responsibility_id NUMBER;
537
538 BEGIN
539
540 --
541 -- Access the Sourcing external APPS_FRAMEWORK_AGENT
542 --
546 --
543 l_ext_fwk_agent := FND_PROFILE.value('PON_EXT_APPS_FRAMEWORK_AGENT');
544 --
545 -- If the profile is not set, then try the default responsibility approach
547 IF (l_ext_fwk_agent IS NULL) THEN
548 --
549 l_ext_fwk_agent := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
550 END IF;
551
552 IF ( substr(l_ext_fwk_agent, -1, 1) <> '/' ) THEN
553 l_ext_fwk_agent := l_ext_fwk_agent||'/';
554 END IF;
555 l_ext_fwk_agent := l_ext_fwk_agent || 'OA_HTML/jsp/pos/registration/RegistrationReply.jsp?registrationKey=' || p_registration_key || '&' || 'regLang=' || p_language_code;
556
557 /* Bug 3290344 removed call to UTL_URL.escape function specific to Oracle 9i database */
558 RETURN l_ext_fwk_agent;
559
560 END get_isp_supplier_register_url;
561
562 PROCEDURE GetConcProgramType(itemtype in varchar2,
563 itemkey in varchar2,
564 actid in number,
565 uncmode in varchar2,
566 resultout out NOCOPY varchar2) IS
567
568 BEGIN
569
570 resultout := wf_engine.GetItemAttrText (itemtype => itemtype,
571 itemkey => itemkey,
572 aname => 'CONCPROGRAM_TYPE');
573
574 END GetConcProgramType;
575
576 Procedure ReportConcProgramStatus(
577 p_request_id in Number,
578 p_messagetype in Varchar2,
579 p_RecepientUsername in Varchar2,
580 p_recepientType in Varchar2,
581 p_auction_header_id in number,
582 p_ProgramTypeCode in Varchar2,
583 p_DestinationPageCode in Varchar2,
584 p_bid_number in Number,
585 p_max_good_line_num in number default -1,
586 p_last_goodline_worksheet in Varchar2 default ''
587 ) is
588 l_item_type Varchar2(8) := 'PONCNCT';
589 l_item_key Varchar2(240);
590 l_language_code VARCHAR2(5);
591 l_msg_suffix VARCHAR2(10);
592 l_auction_title pon_auction_headers_all.auction_title%type;
593 l_document_number pon_auction_headers_all.document_number%type;
594 l_trading_partner_contact_name pon_auction_headers_all.trading_partner_contact_name%type;
595 l_trading_partner_name pon_auction_headers_all.trading_partner_name%type;
596 l_doctype_group_name pon_auc_doctypes.doctype_group_name%type;
597 l_doctypemsgval Varchar2(80);
598
599 /* Variables added for CLM Sol Amendments project. */
600 l_auc_head_id_prev_amend pon_auction_headers_all.auction_header_id%type;
601 l_revision pon_auction_headers_all.revision%type;
602 l_amendment_number pon_auction_headers_all.document_number%type;
603 l_rev_message VARCHAR2(30);
604 l_success_msg VARCHAR2(100);
605
606 BEGIN
607
608 select to_char(p_auction_header_id) || '-' ||
609 to_char(p_request_id)
610 into l_item_key from dual;
611 -- set the db session language
612 PON_PROFILE_UTIL_PKG.get_wf_language(p_RecepientUsername, l_language_code);
613 PON_AUCTION_PKG.set_session_language(null, l_language_code);
614 SELECT
615 auc.trading_partner_contact_name,
616 auc.trading_partner_name,
617 auc.auction_title,
618 auc.document_number,
619 dt.doctype_group_name
620 INTO
621 l_trading_partner_contact_name,
622 l_trading_partner_name,
623 l_auction_title,
624 l_document_number,
625 l_doctype_group_name
626 FROM
627 pon_auction_headers_all auc,
628 pon_auc_doctypes dt
629 WHERE
630 dt.doctype_id= auc.doctype_id
631 AND auc.auction_header_id = p_auction_header_id;
632
633 l_msg_suffix := PON_AUCTION_PKG.get_message_suffix(l_doctype_group_name);
634 l_doctypemsgval := PON_AUCTION_PKG.getmessage('PON_AUCTION',l_msg_suffix);
635 if p_messagetype ='S' then
636
637 wf_engine.CreateProcess(itemtype => l_item_type,
638 itemkey => l_item_key,
639 process => 'REPORT_SUCCESS');
640
641 else
642 wf_engine.CreateProcess(itemtype => l_item_type,
643 itemkey => l_item_key,
644 process => 'REPORT_FAILURE');
645 end if;
646 -- set standard notification header attributes
647 PON_WF_UTL_PKG.set_hdr_attributes(l_item_type,
648 l_item_key,
649 l_trading_partner_name,
650 l_auction_title,
651 l_document_number,
652 l_trading_partner_contact_name);
653
654 -- set other core attributes
655 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
656 itemkey => l_item_key,
657 aname => 'AUCTION_ID',
658 avalue => p_auction_header_id);
659
660 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
661 itemkey => l_item_key,
662 aname => 'BID_ID',
663 avalue => p_bid_number);
664
665 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
666 itemkey => l_item_key,
667 aname => 'REQUEST_ID',
668 avalue => p_request_id);
669
670 wf_engine.SetItemAttrText(itemtype => l_item_type,
671 itemkey => l_item_key,
675 wf_engine.SetItemAttrText(itemtype => l_item_type,
672 aname => 'CONCPROGRAM_TYPE',
673 avalue => p_ProgramTypeCode);
674
676 itemkey => l_item_key,
677 aname => 'DESTINATION_PAGE_CODE',
678 avalue => p_DestinationPageCode);
679
680 wf_engine.SetItemAttrText(itemtype => l_item_type,
681 itemkey => l_item_key,
682 aname => 'RECEPIENT_USERNAME',
683 avalue => p_RecepientUsername);
684
685 wf_engine.SetItemAttrText(itemtype => l_item_type,
686 itemkey => l_item_key,
687 aname => 'DESTINATION_URL',
688 avalue => get_dest_page_url(p_dest_func=>p_DestinationPageCode,
689 p_notif_performer=>p_recepientType));
690
691
692 wf_engine.SetItemAttrText(itemtype => l_item_type,
693 itemkey => l_item_key,
694 aname => 'NOTIFICATION_SUBJECT',
695 avalue => PON_AUCTION_PKG.getMessage('PONCPG' || p_messagetype || '_' || p_ProgramTypeCode,
696 l_msg_suffix,
697 'REQUEST_ID', --REQUEST_ID
698 to_char(p_request_id),
699 'DOC_TYPE',
700 l_doctypemsgval,
701 'DOC_NUMBER',
702 l_document_number,
703 'DOC_TITLE',
704 l_auction_title
705 ));
706
707 wf_engine.SetItemAttrText(itemtype => l_item_type,
708 itemkey => l_item_key,
709 aname => 'MAX_SUCCESS_LAST_BATCH_LINE_NO',
710 avalue => to_char(p_max_good_line_num));
711
712
713 wf_engine.SetItemAttrText(itemtype => l_item_type,
714 itemkey => l_item_key,
715 aname => 'LAST_SUCCESS_BATCH_WORKSHEET',
716 avalue => p_last_goodline_worksheet);
717
718 /* For federal CLM Sol Amendments project.
719 * We need to set some wf attributes for generating the message.
720 */
721 if p_ProgramTypeCode = 'AMENDMENT_COPY' and p_messagetype ='S' then
722
723 select auction_header_id_prev_amend,revision
724 into l_auc_head_id_prev_amend,l_revision
725 from pon_auction_headers_all
726 where auction_header_id = p_auction_header_id;
727
728 select document_number
729 into l_amendment_number
730 from pon_auction_headers_all
731 where auction_header_id_prev_amend = l_auc_head_id_prev_amend
732 and nvl(amendment_flag,'N') = 'Y';
733
734 fnd_message.set_name('PON', 'PON_REVISION_CLM');
735 fnd_message.set_token('REVISION',to_char(l_revision));
736 l_rev_message := fnd_message.get;
737 fnd_message.set_name('PON','PON_AUCTION_AUCTION'||l_msg_suffix);
738 l_success_msg := fnd_message.get||' '||l_document_number||l_rev_message;
739
740 wf_engine.SetItemAttrText(itemtype => l_item_type,
741 itemkey => l_item_key,
742 aname => 'CNF_DOCUMENT_NUMBER',
743 avalue => l_success_msg);
744
745 wf_engine.SetItemAttrText(itemtype => l_item_type,
746 itemkey => l_item_key,
747 aname => 'AMENDMENT_NUMBER',
748 avalue => l_amendment_number);
749 end if;
750
751 wf_engine.StartProcess(itemtype => l_item_type,
752 itemkey => l_item_key );
753 END ReportConcProgramStatus;
754
755
756
757 PROCEDURE GetLastLineNumberInBatch(itemtype in varchar2,
758 itemkey in varchar2,
759 actid in number,
760 uncmode in varchar2,
761 resultout out NOCOPY varchar2) IS
762
763 BEGIN
764
765 BEGIN
766 resultout := wf_engine.GetItemAttrText (itemtype => itemtype,
767 itemkey => itemkey,
768 aname => 'MAX_SUCCESS_LAST_BATCH_LINE_NO');
769 EXCEPTION
770 when others then resultout := -1;
771 END;
772
773
774 END GetLastLineNumberInBatch;
775
776 /*********/
777
778 PROCEDURE GetLastWorksheetInBatch(itemtype in varchar2,
779 itemkey in varchar2,
780 actid in number,
781 uncmode in varchar2,
782 resultout out NOCOPY varchar2) IS
783 BEGIN
784 BEGIN
785 resultout := wf_engine.GetItemAttrText (
786 itemtype => itemtype,
787 itemkey => itemkey,
788 aname => 'LAST_SUCCESS_BATCH_WORKSHEET');
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 resultout := '';
793 END;
794
795 END GetLastWorksheetInBatch;
796
797 /*==============================================================================================
801 p_auction_id IN auction_header_id of current negotiation
798 PROCEDURE : GET_NOTIF_PREFERENCE PUBLIC
799 PARAMETERS:
800 p_wf_message_name IN workflow message name of current notification
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_auction_id IN PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE) RETURN VARCHAR2
816 IS
817
818 l_document_type PON_AUC_DOCTYPES.DOCTYPE_GROUP_NAME%TYPE;
819 l_api_name CONSTANT VARCHAR2(30) := 'get_notif_preference_1';
820
821 BEGIN
822
823 IF (g_fnd_debug = 'Y') THEN
824 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
825 FND_LOG.string(log_level => FND_LOG.level_statement,
826 module => g_module_prefix || l_api_name,
827 message => 'BEGIN: Check WF preference for'
828 || p_wf_message_name || ' for auction '
829 || p_auction_id);
830 END IF;
831 END IF;
832
833
834 SELECT docTypes.DOCTYPE_GROUP_NAME
835 INTO l_document_type
836 FROM PON_AUCTION_HEADERS_ALL auctionHdr,
837 PON_AUC_DOCTYPES docTypes
838 WHERE auctionHdr.AUCTION_HEADER_ID = p_auction_id
839 AND auctionHdr.DOCTYPE_ID = docTypes.DOCTYPE_ID;
840
841 RETURN GET_NOTIF_PREFERENCE(p_wf_message_name, l_document_type);
842
843 EXCEPTION
844 --
845 -- Exception can come if the auction_header_id or the doctype
846 -- data is missing or the GET_NOTIF_PREFERENCE call returns
847 -- error. In all these cases it should raise the error as
848 -- this is an unnatural event
849 --
850 WHEN OTHERS THEN
851 IF (g_fnd_debug = 'Y') THEN
852 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
853 FND_LOG.string(log_level => FND_LOG.level_exception,
854 module => g_module_prefix || l_api_name,
855 message => 'EXCEPTION: Check WF preference for'
856 || p_wf_message_name || ' for auction '
857 || p_auction_id);
858 END IF;
859 END IF;
860 RAISE;
861
862 END GET_NOTIF_PREFERENCE;
863
864
865 /*==============================================================================================
866 PROCEDURE : GET_NOTIF_PREFERENCE PUBLIC
867 PARAMETERS:
868 p_wf_message_name IN workflow message name of current notification
869 p_doctype IN document type group name of current negotiation
870
871 COMMENT : this function was introduced as a part of the notification
872 subscriptions project in release-12. This function should be
873 invoked in order to determine whether we need to send a particular
874 notification to the buyer or supplier user. The UI to set the notification
875 preferences can be viewed or modified by accessing the page via Sourcing
876 Admin home page
877
878 ==============================================================================================*/
879
880
881 FUNCTION GET_NOTIF_PREFERENCE (
882 p_wf_message_name IN WF_MESSAGES.NAME%TYPE,
883 p_doctype IN PON_AUC_DOCTYPES.DOCTYPE_GROUP_NAME%TYPE)
884 RETURN VARCHAR2
885 IS
886
887 l_notif_pref VARCHAR2(3);
888 l_api_name CONSTANT VARCHAR2(30) := 'get_notif_preference_2';
889 l_yes CONSTANT VARCHAR2(2) := 'Y';
890 l_no CONSTANT VARCHAR2(2) := 'N';
891
892 BEGIN
893
894 IF (g_fnd_debug = 'Y') THEN
895 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
896
897 FND_LOG.string(log_level => FND_LOG.level_statement,
898 module => g_module_prefix || l_api_name,
899 message => 'BEGIN: Check WF preference for'
900 || p_wf_message_name || ' for doctype '
901 || p_doctype);
902 END IF;
903 END IF;
904
905
906 SELECT DECODE(P_DOCTYPE,SRC_AUCTION, notifGroups.AUCTION_SUBSCRIPTION_FLAG,
907 SRC_RFQ, notifGroups.RFQ_SUBSCRIPTION_FLAG,
908 SRC_RFI, notifGroups.RFI_SUBSCRIPTION_FLAG,
909 SRC_SOL, notifGroups.SOL_SUBSCRIPTION_FLAG, --<Sol Project>
910 l_yes)
911 INTO l_notif_pref
912 FROM PON_NOTIF_SUBSCRIPTION_GROUPS notifGroups,
913 PON_NOTIF_GROUP_MEMBERS notifMessages
914 WHERE
915 notifGroups.NOTIF_GROUP_CODE = notifMessages.NOTIF_GROUP_CODE
916 AND notifMessages.NOTIF_MESSAGE_NAME = P_WF_MESSAGE_NAME;
917
918
919 IF (g_fnd_debug = 'Y') THEN
920 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
921 FND_LOG.string(log_level => FND_LOG.level_statement,
922 module => g_module_prefix || l_api_name,
923 message => 'BEGIN: Check WF preference for'
924 || p_wf_message_name || ' for doctype '
925 || p_doctype || ' with return value '
926 || l_notif_pref);
927 END IF;
928 END IF;
929
930 IF l_notif_pref = 'Y' THEN
934 END IF;
931 l_notif_pref := l_yes;
932 ELSE
933 l_notif_pref := l_no;
935
936 RETURN l_notif_pref;
937
938 EXCEPTION
939 WHEN NO_DATA_FOUND THEN
940 --
941 -- If there is no subscription for a message name then
942 -- the member data can be missing or it can be for some
943 -- not to be subscribed message. Hence, return true i.e.
944 -- the older behavior
945 --
946 IF (g_fnd_debug = 'Y') THEN
947 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
948 FND_LOG.string(log_level => FND_LOG.level_exception,
949 module => g_module_prefix || l_api_name,
950 message => 'EXCEPTION: NO DATA FOUND for message name:'
951 || p_wf_message_name || ' for doctype '
952 || p_doctype);
953 END IF;
954 END IF;
955 RETURN l_yes;
956
957 WHEN OTHERS THEN
958 --
959 -- Chances are rare but if any such error happens then it will simply
960 -- raise the error to the upper level
961 --
962 IF (g_fnd_debug = 'Y') THEN
963 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
964 FND_LOG.string(log_level => FND_LOG.level_exception,
965 module => g_module_prefix || l_api_name,
966 message => 'EXCEPTION:Check WF preference for '
967 || P_WF_MESSAGE_NAME
968 || ' for doctype '
969 || p_doctype);
970 END IF;
971 END IF;
972 RAISE;
973
974 END GET_NOTIF_PREFERENCE;
975
976
977 /*
978 Function: get_base_external_supplier_url
979 Parameters: None
980 Returns: the base external supplier url
981 Sample output: http://server01:4761/
982 */
983
984 FUNCTION get_base_external_supplier_url RETURN VARCHAR2 IS
985
986 l_def_ext_user_resp VARCHAR2(240);
987 l_application_id NUMBER;
988 l_responsibility_id NUMBER;
989 l_ext_fwk_agent VARCHAR2(240);
990
991 -- First try to get the Sourcing External Framework Agent.
992 -- If not set, then get the responsibility associated with the
993 -- 'Sourcing Default Responsibility for External User' profile option
994 BEGIN
995 --
996 -- Access the Sourcing external APPS_FRAMEWORK_AGENT
997 --
998 l_ext_fwk_agent := FND_PROFILE.value('PON_EXT_APPS_FRAMEWORK_AGENT');
999 --
1000 -- If the profile is not set, then try the default responsibility approach
1001 --
1002 IF (l_ext_fwk_agent IS NULL) THEN
1003 --
1004 l_def_ext_user_resp := FND_PROFILE.value('PON_DEFAULT_EXT_USER_RESP');
1005 --
1006 IF (l_def_ext_user_resp IS NOT NULL) THEN
1007 --
1008 -- get the value of 'APPS_FRAMEWORK_AGENT' profile at this responsibility level
1009 --
1010 BEGIN
1011 SELECT application_id, responsibility_id
1012 INTO l_application_id, l_responsibility_id
1013 FROM fnd_responsibility
1014 WHERE responsibility_key = l_def_ext_user_resp
1015 AND (end_date IS NULL OR end_date > sysdate);
1016 --
1017 l_ext_fwk_agent := FND_PROFILE.value_specific(
1018 name => 'APPS_FRAMEWORK_AGENT',
1019 responsibility_id => l_responsibility_id,
1020 application_id => l_application_id );
1021 --
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 l_ext_fwk_agent := null;
1025 END;
1026 END IF;
1027 END IF;
1028 --
1029 -- If still NULL, fall back to APPS_FRAMEWORK_AGENT
1030 --
1031 IF (l_ext_fwk_agent IS NULL) THEN
1032 l_ext_fwk_agent := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
1033 END IF;
1034
1035 RETURN l_ext_fwk_agent;
1036
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 RETURN '';
1040 END get_base_external_supplier_url;
1041
1042
1043 FUNCTION get_site_level_profile_value(p_profile_name varchar2) RETURN VARCHAR2 IS
1044
1045 l_level_id NUMBER;
1046 l_profile_value varchar2(240);
1047
1048 -- this cursor fetches profile option values
1049 cursor profile_value(p_name varchar2, a_id number, l_id number, l_val number) is
1050 select fpov.profile_option_value
1051 from fnd_profile_options fpo,
1052 fnd_profile_option_values fpov
1053 where fpo.profile_option_name = p_name
1054 and fpo.start_date_active <= sysdate
1055 and nvl(fpo.end_date_active, sysdate) >= sysdate
1056 and fpo.profile_option_id=fpov.profile_option_id
1057 and fpov.application_id=a_id
1058 and fpov.level_id=l_id
1059 and fpov.level_value=l_val
1060 and fpov.profile_option_value is not null;
1061
1062 BEGIN
1063
1064 l_level_id := 10001;
1065 open profile_value(p_profile_name,0,l_level_id,0);
1066 fetch profile_value into l_profile_value;
1067
1068 if (profile_value%NOTFOUND) then
1069 l_profile_value := NULL;
1070 end if; -- value_uas%NOTFOUND
1071
1072 close profile_value;
1073
1074 RETURN l_profile_value;
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 RETURN NULL;
1079 END get_site_level_profile_value;
1080
1081 /*
1082 Function: get_base_internal_buyer_url
1083 Parameters: None
1084 Returns: the base internal buyer url
1085 Sample output: http://server01:4761/
1086 */
1087
1088 FUNCTION get_base_internal_buyer_url RETURN VARCHAR2 IS
1089 l_base_url VARCHAR2(240) := '';
1090 l_api_name CONSTANT VARCHAR2(30) := 'get_base_internal_buyer_url';
1091 BEGIN
1092 -- 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.
1093 l_base_url := get_site_level_profile_value('APPS_FRAMEWORK_AGENT');
1094
1095 IF (g_fnd_debug = 'Y') THEN
1096 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1097 FND_LOG.string(log_level => FND_LOG.level_statement,
1098 module => g_module_prefix || l_api_name,
1099 message => 'After calling get_site_level_profile_value. l_base_url='
1100 || l_base_url);
1101 END IF;
1102 END IF;
1103
1104 IF (l_base_url is null) then
1105 l_base_url := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
1106 IF (g_fnd_debug = 'Y') THEN
1107 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1108 FND_LOG.string(log_level => FND_LOG.level_statement,
1109 module => g_module_prefix || l_api_name,
1110 message => 'get_site_level_profile return NULL. After calling FND_PROFILE.value(); l_base_url='
1111 || l_base_url);
1112 END IF;
1113 END IF;
1114
1115 END IF;
1116
1117 RETURN l_base_url;
1118
1119 END get_base_internal_buyer_url;
1120
1121 END PON_WF_UTL_PKG;
[Home] [Help]