[Home] [Help]
PACKAGE BODY: APPS.PON_AUCTION_APPROVAL_PKG
Source
1 PACKAGE BODY PON_AUCTION_APPROVAL_PKG as
2 /* $Header: PONAPPRB.pls 120.28 2011/08/25 06:56:47 rakearun ship $ */
3 /*=======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME
8 | PONAPPRB.pls
9 |
10 | DESCRIPTION
11 | PL/SQL body for package PON_AUCTION_APPROVAL_PKG
12 |
13 | NOTES
14 | PL/SQL routines for negotiation approvals
15 |
16 | HISTORY
17 | Date UserName Description
18 | -------------------------------------------------------------------------------------------
19 |
20 | 25-Aug-05 sparames Bug 4295915: Missing owner in Sourcing notifications
21 | Added call to wf_engine.SetItemOwner
22 |
23 | 09-Sep-05 120.25 sparames ECO 4456420: Added setting of ORIGIN_USER_NAME to the
24 | current user for most operations
25 |
26 |
27 =========================================================================+*/
28
29
30 FUNCTION Get_Emd_Update_Url (pn_aunction_header_id NUMBER)
31 RETURN VARCHAR2
32 IS
33 l_ext_fwk_agent VARCHAR2(240);
34 l_auction_header_id NUMBER := pn_aunction_header_id;
35 l_emd_update_link VARCHAR2(4000);
36
37 BEGIN
38 -- Access the Sourcing external APPS_FRAMEWORK_AGENT
39 --
40 l_ext_fwk_agent := FND_PROFILE.value('PON_EXT_APPS_FRAMEWORK_AGENT');
41 --
42 -- If the profile is not set, then try the default responsibility approach
43 --
44 IF (l_ext_fwk_agent IS NULL) THEN
45 --
46 l_ext_fwk_agent := FND_PROFILE.value('APPS_FRAMEWORK_AGENT');
47 END IF;
48 --
49 -- add OA_HTML/OA.jsp to the profile value
50 --
51 IF ( l_ext_fwk_agent IS NOT NULL ) THEN
52 --
53 IF ( substr(l_ext_fwk_agent, -1, 1) = '/' ) THEN
54 --RETURN l_ext_fwk_agent || 'OA_HTML/OA.jsp';
55 l_ext_fwk_agent := l_ext_fwk_agent || 'OA_HTML/OA.jsp';
56 ELSE
57 --RETURN l_ext_fwk_agent || '/' || 'OA_HTML/OA.jsp';
58 l_ext_fwk_agent := l_ext_fwk_agent || '/' || 'OA_HTML/OA.jsp';
59 END IF;
60
61 l_emd_update_link := l_ext_fwk_agent || '?'|| 'page=/oracle/apps/pon/emd/creation/webui/ponEmdUpdatePG'
62 || '&' ||'akRegionApplicationId=396' || '&' ||'OAHP=PON_EMD_ADMIN_HOME'||'&'||'OASF=PON_EMD_UPDATE'
63 ||'&'||'OAPB=PON_SOURCING_BRAND'||'&'|| 'notificationId=&' || '#NID' ||'&'||'language_code=' || fnd_global.current_language;
64 -- No profiles are setup so return nothing...
65 --
66 ELSE
67 l_emd_update_link := '';
68 END IF;
69
70 Return l_emd_update_link;
71 --dbms_output.put_line(l_emd_update_link);
72 END Get_Emd_Update_Url;
73
74 -- choli add for emd update page link in notification
75 Procedure Get_Emd_HeaderId(pn_notification_id IN NUMBER,
76 l_auction_header_id OUT NOCOPY NUMBER) IS
77
78 CURSOR wf_item_cur IS
79 SELECT item_type,
80 item_key
81 FROM wf_item_activity_statuses
82 WHERE notification_id = pn_notification_id;
83 CURSOR wf_notif_context_cur IS
84 SELECT SUBSTR(context,1,INSTR(context,':',1)-1),
85 SUBSTR(context,INSTR(context,':')+1,
86 (INSTR(context,':',1,2) - INSTR(context,':')-1)),
87 message_name
88 FROM wf_notifications
89 WHERE notification_id = pn_notification_id;
90
91 p_itemtype WF_ITEM_ACTIVITY_STATUSES.item_type%TYPE; -- VARCHAR2(8)
92 p_itemkey WF_ITEM_ACTIVITY_STATUSES.item_key%TYPE; -- VARCHAR2(240)
93
94 p_message_name wf_notifications.message_name%TYPE;
95
96 BEGIN
97
98
99 -- Fetch the item_type and item_key values from
100 -- wf_item_activity_statuses for a given notification_id.
101 OPEN wf_item_cur;
102 FETCH wf_item_cur INTO p_itemtype, p_itemkey;
103 CLOSE wf_item_cur;
104
105 -- If the wf_item_activity_statuses does not contain an entry,
106 -- then parse the wf_notifications.context field to
107 -- get the item_type and item_key values for a given notification_id.
108 IF ((p_itemtype IS NULL) AND (p_itemkey IS NULL))
109 THEN
110 OPEN wf_notif_context_cur;
111 FETCH wf_notif_context_cur INTO p_itemtype, p_itemkey, p_message_name;
112 CLOSE wf_notif_context_cur;
113
114 END IF;
115
116 IF (p_itemtype = 'PONAPPRV' or p_itemtype = 'PONAWAPR') THEN
117 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
118 itemkey => p_itemkey,
119 aname => 'AUCTION_HEADER_ID');
120 END IF;
121
122
123 END Get_Emd_HeaderId;
124
125
126 Procedure Process_If_Doc_Approved(p_auction_header_id number,
127 p_top_process_item_key Varchar2) is
128 l_approved_count number;
129 l_approval_count number;
130 l_activity_status Varchar2(80);
131 l_item_type Varchar2(30) := 'PONAPPRV';
132 l_auction_approval_status Varchar2(30);
133 begin
134 select count(auction_header_id),
135 nvl(sum(decode(approval_status,'APPROVED',1,0)),0)
139 --FOR ERIC TEST ONLY,BEGIN
136 into l_approval_count,l_approved_count
137 from pon_neg_team_members
138 where auction_header_id = p_auction_header_id
140 ---------------------------------------------------------
141 --and approver_flag='Y';
142 AND ( approver_flag = 'Y' --for emd module
143 );
144 ---------------------------------------------------------
145 --FOR ERIC TEST ONLY,END
146 if l_approval_count =0 then
147 l_auction_approval_status := 'NOT_REQUIRED';
148 elsif l_approval_count = l_approved_count then
149 l_auction_approval_status := 'APPROVED';
150 else
151 l_auction_approval_status := 'REQUIRED';
152 end if;
153
154 -- The doucment is approved
155 if (l_auction_approval_status = 'APPROVED' or
156 l_auction_approval_status = 'NOT_REQUIRED') then
157 begin
158 select activity_label
159 into l_activity_status
160 from wf_item_activity_statuses_v
161 where item_type = 'PONAPPRV'
162 and item_key = p_top_process_item_key
163 and activity_status_code = 'NOTIFIED';
164 exception when others then
165 l_activity_status := 'DO Nothing';
166 end;
167 -- time to move the parent activity to the approved state
168 if l_activity_status = 'WAITFOR APPROVALS' then
169 if (l_auction_approval_status = 'APPROVED') then
170 wf_engine.CompleteActivity(l_item_type,p_top_process_item_key,l_activity_status,'APPROVED');
171 else wf_engine.CompleteActivity(l_item_type,p_top_process_item_key,l_activity_status,'NOT_REQUIRED');
172 end if;
173 end if;
174 end if; -- APPROVED
175 End Process_If_Doc_Approved;
176
177
178
179
180 Function Get_Signed_date(p_auction_header_id IN Number,itemtype IN Varchar2, itemkey IN Varchar2) Return Date is
181 l_is_federal Number;
182 l_is_amendment Varchar2(1);
183 l_signed_date Date;
184 Begin
185 l_signed_date := null;
186 l_is_federal := PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id);
187 if ( l_is_federal = 1 ) THEN
188 l_is_amendment := PON_CLM_UTIL_PKG.IS_AMENDMENT(p_auction_header_id);
189 if ( l_is_amendment = 'Y' ) THEN
190 l_signed_date := wf_engine.GetItemAttrDate (itemtype => itemtype,
191 itemkey => itemkey,
192 aname => 'SIGNED_DATE');
193 if ( l_signed_date is null ) THEN
194 l_signed_date := sysdate;
195 end if;
196 end if;
197 end if;
198 return l_signed_date;
199 End;
200
201 Procedure Process_Doc_Rejected(p_auction_header_id number, p_top_process_item_key Varchar2) is
202 l_activity_status Varchar2(80);
203 l_item_type Varchar2(30) := 'PONAPPRV';
204 begin
205 begin
206 select activity_label
207 into l_activity_status
208 from wf_item_activity_statuses_v
209 where item_type = 'PONAPPRV'
210 and item_key = p_top_process_item_key
211 and activity_status_code = 'NOTIFIED';
212 exception when others then
213 l_activity_status := 'DO Nothing';
214 end;
215 -- time to move the parent activity to the rejected state
216 if l_activity_status = 'WAITFOR APPROVALS' then
217 wf_engine.CompleteActivity(l_item_type,p_top_process_item_key,l_activity_status,'REJECTED');
218 end if;
219 End Process_Doc_Rejected;
220
221 PROCEDURE CANCEL_NOTIFICATION(p_auction_header_id number,
222 p_user_name varchar2,
223 p_resultOut out nocopy number) is
224 l_itemKey varchar2(240);
225 l_parent_process_itemKey varchar2(240);
226 l_itemType varchar2(25):= 'PONAPPRV';
227 l_nid number;
228 l_replied varchar2(1);
229 l_auction_status varchar2(30);
230 l_user_status varchar2(30);
231 l_user_approval varchar2(30);
232 l_user_id number;
233 begin
234 -- need to change after understanding requirment from Datta
235 p_resultOut := 0;
236 -- Check to see if this user has already approved
237 begin
238
239 -- use user_id wherever possible
240 select user_id
241 into l_user_id
242 from fnd_user
243 where user_name = p_user_name;
244
245
246
247
248 select neg.approver_flag, neg.approval_status, auc.approval_status,
249 wf_approval_item_key
250 into l_user_approval,l_user_status,l_auction_status,l_parent_process_itemKey
251 from pon_neg_team_members neg, pon_auction_headers_all auc
252 where auc.auction_header_id = p_auction_header_id
253 and neg.auction_header_id= auc.auction_header_id
254 and neg.user_id = l_user_id;
255
256 -- update user as not an approver
257 update pon_neg_team_members
258 set approver_flag ='N'
259 where auction_header_id = p_auction_header_id
260 and user_id = l_user_id;
261 l_itemKey := l_parent_process_itemKey || '_' || l_user_id;
262 /* Select notification Id from the item key and user name */
263 select notification_id
264 into l_nid
265 from WF_ITEM_ACTIVITY_STATUSES
266 where ASSIGNED_USER = p_user_name
267 and ITEM_TYPE = l_itemType
268 and ITEM_KEY = l_itemkey
269 and activity_status ='NOTIFIED';
270 WF_Notification.cancel(l_nid);
271 exception
272 when others then
273 p_resultOut := 1; -- unexpected
274 end;
275 /* Check for doc approval conditions */
276 Process_If_Doc_Approved(p_auction_header_id,l_parent_process_itemKey);
277
278 end CANCEL_NOTIFICATION;
279
280 --added p_signed_date as an IN PARAM
281 PROCEDURE UPDATE_NOTIF_ONLINE (p_auction_header_id number,
282 p_user_name varchar2,
283 p_result varchar2,
287 l_itemKey varchar2(240);
284 p_note_to_buyer varchar2,
285 p_signed_date DATE,
286 p_resultOut out nocopy number) is
288 l_itemType varchar2(25):= 'PONAPPRV';
289 l_nid number;
290 l_replied varchar2(1);
291 l_auction_status varchar2(30);
292 l_user_status varchar2(30);
293 l_user_approval varchar2(30);
294 l_user_id number;
295
296 --added by amundhra for federal CLM
297 l_item_key_main_process VARCHAR2(240);
298 l_index NUMBER;
299 begin
300 p_resultOut := 0;
301
302 -- use user_id wherever possible
303 select user_id
304 into l_user_id
305 from fnd_user
306 where user_name = p_user_name;
307
308 -- Check to see if this user has already approved
309 begin
310 select neg.approver_flag, neg.approval_status, auc.approval_status,
311 wf_approval_item_key
312 into l_user_approval,l_user_status,l_auction_status,l_itemKey
313 from pon_neg_team_members neg, pon_auction_headers_all auc
314 where auc.auction_header_id = p_auction_header_id
315 and neg.auction_header_id= auc.auction_header_id
316 and neg.user_id = l_user_id;
317
318 -- the item key for the user will be
319 l_itemKey := l_itemKey || '_' || l_user_id;
320 /* Select notification Id from the item key and user name */
321 begin
322 select notification_id
323 into l_nid
324 from WF_ITEM_ACTIVITY_STATUSES
325 where ASSIGNED_USER = p_user_name
326 and ITEM_TYPE = l_itemType
327 and ITEM_KEY = l_itemkey
328 and activity_status ='NOTIFIED';
329 exception when no_data_found then
330 /* This is a situation where the user responded using E-Mail and
331 edited the decision with a typo!!
332 */
333 User_Decision_Without_WF(l_user_id, p_result, p_note_to_buyer,
334 p_auction_header_id);
335 return;
336 end;
337 wf_notification.SetAttrText(l_nid, 'RESULT',p_result);
338 wf_notification.SetAttrText(l_nid, 'APPROVER_NOTES',p_note_to_buyer);
339 --added by amundhra for federal CLM
340 --added signed date setting the item attribute date.
341
342 SELECT wf_approval_item_key
343 INTO l_item_key_main_process
344 FROM pon_auction_headers_all
345 WHERE auction_header_id = p_auction_header_id;
346
347 --added by amundhra for Federal CLM
348 --added signed date setting the item attribute date.
349 wf_engine.SetItemAttrDate(l_itemType,l_itemKey, 'SIGNED_DATE', p_signed_date);
350
351 wf_engine.SetItemAttrDate(l_itemType,l_item_key_main_process, 'SIGNED_DATE', p_signed_date);
352
353 WF_Notification.respond(l_nid,p_result,p_user_name);
354 exception
355 when others then
356 p_resultOut := 1; -- unexpected
357 end;
358
359 end UPDATE_NOTIF_ONLINE;
360
361 Procedure Close_Child_Process(p_parent_item_key Varchar2) is
362 Cursor List_of_Process(p_item_type varchar2, p_parent_item_key Varchar2) is
363 select activity_label, item_key,notification_id
364 from wf_item_activity_statuses_v
365 where item_type = p_item_type
366 and item_key like p_parent_item_key || '_%'
367 and activity_status_code = 'NOTIFIED';
368 l_item_type Varchar2(30) := 'PONAPPRV';
369 begin
370 for r1 in List_of_Process(l_item_type,p_parent_item_key) loop
371 begin
372 WF_Notification.cancel(r1.notification_id);
373
374 --Bug 9386801
375 --Uncommenting the CompleteActivity because of which
376 --workflow was not ending and resulting in unneeded reminder
377 --notifications after one approver rejects
378 wf_engine.CompleteActivity(l_item_type,r1.item_key,
379 r1.activity_label,'CLOSE');
380
381 exception when others then null;
382 end;
383 end loop;
384
385 End Close_Child_Process;
386
387 PROCEDURE UPDATE_DOC_TO_CANCELLED ( itemtype in varchar2,
388 Itemkey in varchar2,
389 actid in number,
390 uncmode in varchar2,
391 resultout out nocopy varchar2) is
392
393 l_auction_header_id NUMBER;
394 begin
395 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
396 itemkey => itemkey,
397 aname => 'AUCTION_HEADER_ID');
398 update pon_auction_headers_all
399 set auction_status = 'CANCELLED'
400 where auction_header_id=l_auction_header_id;
401
402 end UPDATE_DOC_TO_CANCELLED;
403
404 /*procedure print_debug_messages(msg in varchar2)
405 as pragma autonomous_transaction;
406 begin
407 insert into vhk_dummy values(msg);
408 commit;
409 end print_debug_messages;*/
410
411 /* Entry procedure to start document approval process.
412 */
413
414 PROCEDURE SUBMIT_FOR_APPROVAL(p_auction_header_id_encrypted VARCHAR2, -- 1
415 p_auction_header_id number, -- 2
416 p_note_to_approvers varchar2, -- 3
417 p_submit_user_name varchar2, -- 4
418 p_redirect_func varchar2) is -- 5
419 l_seq varchar2(100);
420 l_itemKey varchar2(240);
421 l_itemType varchar(25) := 'PONAPPRV';
422 l_creator_user_name varchar2(100);
423 l_creator_full_name varchar2(240);
424 l_creator_user_id number;
425 l_close_bidding_date date;
426 l_open_bidding_date date;
427 l_auction_title varchar2(80);
428 l_creator_time_zone varchar2(80);
429 l_doctype_group_name varchar2(100);
433 l_language_code varchar2(100);
430 l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
431 l_msg_suffix varchar2(10);
432 l_auction_contact_id number;
434 l_timezone varchar2(100);
435 l_timezone_disp varchar2(100);
436 l_oex_timezone varchar2(100);
437 l_url_preview varchar2(500);
438 l_url_modify varchar2(500);
439 l_timeout_factor number;
440 l_open_date_in_tz date;
441 l_close_date_in_tz date;
442 l_open_auction_now_flag varchar2(1);
443 l_trading_partner_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
444 l_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
445 l_auction_start_date PON_AUCTION_HEADERS_ALL.OPEN_BIDDING_DATE%TYPE;
446 l_auction_end_date PON_AUCTION_HEADERS_ALL.CLOSE_BIDDING_DATE%TYPE;
447 l_round_number NUMBER;
448 l_amendment_number NUMBER;
449 l_auction_header_id_orig_amend NUMBER;
450 l_orig_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
451 l_review_changes_url VARCHAR2(2000);
452 l_preview_date DATE;
453 l_preview_date_in_tz DATE;
454 l_timezone1_disp VARCHAR2(240);
455 l_submit_user_id number;
456
457 -- Added for Federal Documents
458 l_is_federal NUMBER DEFAULT 0;
459 l_doctype_id PON_AUCTION_HEADERS_ALL.DOCTYPE_ID%TYPE;
460 l_manage_draft_url VARCHAR2(2000);
461
462 l_resp_id NUMBER; -- bug 11732401
463 l_appl_id NUMBER; -- bug 11732401
464
465 /* Get FND user name from trading_partner_contact_id */
466 CURSOR c_auction_info IS
467 select fnd.user_name, fnd.user_id, pon.close_bidding_date,
468 pon.auction_title,
469 decode(nvl(pon.open_auction_now_flag,'N'),'Y',to_date(null),pon.open_bidding_date) open_bidding_date,
470 nvl(pon.open_auction_now_flag,'N') open_auction_now_flag,
471 pon.document_number, trading_partner_contact_id, trading_partner_name, trading_partner_contact_name,
472 open_bidding_date, close_bidding_date, nvl(auction_round_number, 1),
473 nvl(amendment_number, 0), auction_header_id_orig_amend, view_by_date
474 from fnd_user fnd,pon_auction_headers_all pon
475 where fnd.person_party_id = pon.trading_partner_contact_id and
476 pon.auction_header_id = p_auction_header_id and
477 rownum=1;
478
479 begin
480
481 OPEN c_auction_info;
482 FETCH c_auction_info
483 INTO l_creator_user_name, l_creator_user_id, l_close_bidding_date,l_auction_title,
484 l_open_bidding_date,
485 l_open_auction_now_flag,
486 l_doc_number,
487 l_auction_contact_id, l_trading_partner_name, l_trading_partner_contact_name,
488 l_auction_start_date, l_auction_end_date, l_round_number,
489 l_amendment_number, l_auction_header_id_orig_amend, l_preview_date;
490 CLOSE c_auction_info;
491
492 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
493 FND_LOG.string(log_level => FND_LOG.level_statement,
494 module => g_module_prefix || 'SUBMIT_FOR_APPROVAL',
495 message => 'l_language_code : ' || l_language_code);
496 END IF; --}
497
498
499
500 /* Get sequence number to construct itemKey */
501 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_creator_user_id,l_language_code);
502
503 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
504 FND_LOG.string(log_level => FND_LOG.level_statement,
505 module => g_module_prefix || 'SUBMIT_FOR_APPROVAL',
506 message => 'l_creator_user_id : '|| l_creator_user_id ||';'||' l_language_code : ' || l_language_code);
507 END IF; --}
508
509 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
510 FND_LOG.string(log_level => FND_LOG.level_statement,
511 module => g_module_prefix || 'SUBMIT_FOR_APPROVAL',
512 message => '1. Calling SET_SESSION_LANGUAGE with l_language_code : ' || l_language_code);
513 END IF; --}
514
515 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null,l_language_code);
516
517 select to_char(PON_AUCTION_WF_APPROVALS_S.NEXTVAL)
518 into l_seq from sys.dual;
519
520 l_itemKey := to_char (p_auction_header_id)|| '-' || l_seq;
521
522 wf_engine.createProcess ( itemType => l_itemType,
523 itemKey => l_itemKey,
524 process => 'SOURCINGAPPROVAL');
525
526
527 -- call to notification utility package to set the message header common attributes and #from_role
528 pon_wf_utl_pkg.set_hdr_attributes (p_itemtype => l_itemType
529 ,p_itemkey => l_itemKey
530 ,p_auction_tp_name => l_trading_partner_name
531 ,p_auction_title => l_auction_title
532 ,p_document_number => l_doc_number
533 ,p_auction_tp_contact_name => l_trading_partner_contact_name);
534
535
536
537 -- call to notification utility package to get the redirect page url that
538 -- is responsible for getting the Review and Submit page url and forward to it.
539 l_review_changes_url := pon_wf_utl_pkg.get_dest_page_url (
540 p_dest_func => 'PON_NEG_CRT_HEADER'
541 ,p_notif_performer => 'BUYER');
542
543 /* For federal CLM Sol. Amendments project.
544 * Set the manage drafts page url to forward in case of
545 * 1. Federal conformed document created with errors
546 * 2. Super large neg.
547 */
548 l_manage_draft_url := pon_wf_utl_pkg.get_dest_page_url (p_dest_func => 'PON_MANAGE_DRAFT_NEG',
549 p_notif_performer => 'BUYER');
550 wf_engine.SetItemAttrText (itemtype => l_itemType,
551 itemkey => l_itemKey,
555 -- new item attribute to hold the redirect Function. Attribute value is going
552 aname => 'MAN_DRAFT_NEG_URL',
553 avalue => l_manage_draft_url);
554
556 -- to be used as a parameter to access Review and Submit page
557 wf_engine.SetItemAttrText (itemtype => l_itemType,
558 itemkey => l_itemKey,
559 aname => 'REVIEWPG_REDIRECTFUNC',
560 avalue => p_redirect_func);
561
562 wf_engine.SetItemAttrText (itemtype => l_itemType,
563 itemkey => l_itemKey,
564 aname => 'REVIEW_CHANGES_URL',
565 avalue => l_review_changes_url);
566
567 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
568 itemkey => l_itemKey,
569 aname => 'TRADING_PARTNER_CONTACT_ID',
570 avalue => l_auction_contact_id);
571
572 wf_engine.SetItemAttrDate (itemtype => l_itemType,
573 itemkey => l_itemKey,
574 aname => 'AUCTION_START_DATE',
575 avalue => l_open_bidding_date);
576
577 wf_engine.SetItemAttrDate (itemtype => l_itemType,
578 itemkey => l_itemKey,
579 aname => 'AUCTION_END_DATE',
580 avalue => l_close_bidding_date);
581
582 wf_engine.SetItemAttrText (itemtype => l_itemType,
583 itemkey => l_itemKey,
584 aname => 'TOP_PROCESS_ITEM_KEY',
585 avalue => l_itemKey);
586
587 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
588 itemkey => l_itemKey,
589 aname => 'AUCTION_HEADER_ID',
590 avalue => p_auction_header_id);
591
592 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
593 itemkey => l_itemKey,
594 aname => 'DOC_ROUND_NUMBER',
595 avalue => l_round_number);
596
597 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
598 itemkey => l_itemKey,
599 aname => 'DOC_AMENDMENT_NUMBER',
600 avalue => l_amendment_number);
601
602
603 wf_engine.SetItemAttrDate (itemtype => l_itemType,
604 itemkey => l_itemKey,
605 aname => 'TIMEOUT_MAINPROCESS',
606 avalue => l_close_bidding_date);
607
608 wf_engine.SetItemAttrText (itemtype => l_itemType,
609 itemkey => l_itemKey,
610 aname => 'CREATOR_USER_NAME',
611 avalue => l_creator_user_name);
612
613 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
614 itemkey => l_itemKey,
615 aname => 'CREATOR_USER_ID',
616 avalue => l_creator_user_id);
617
618 select dt.doctype_group_name,auh.doctype_id
619 into l_doctype_group_name,l_doctype_id
620 from pon_auction_headers_all auh, pon_auc_doctypes dt
621 where auh.auction_header_id = p_auction_header_id
622 and auh.doctype_id = dt.doctype_id;
623
624 /* Added for Federal CLM Solicitation Amendments Project.
625 * Setting "Is Federal" workflow item attribute to 1
626 */
627 if PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(l_doctype_id) = 1 then
628 l_is_federal := 1;
629 End if;
630 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
631 itemkey => l_itemKey,
632 aname => 'IS_FEDERAL',
633 avalue => l_is_federal);
634 -- End of Federal Code changes
635
636 l_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (l_doctype_group_name);
637
638 wf_engine.SetItemAttrText (itemtype => l_itemType,
639 itemkey => l_itemKey,
640 aname => 'MSG_SUFFIX',
641 avalue => l_msg_suffix);
642 -- Get the user's timezone
643 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(l_auction_contact_id);
644
645 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
646
647 if (l_timezone is null or l_timezone = '' ) then
648 l_timezone := l_oex_timezone;
649 end if;
650
651
652 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(l_timezone) = 1) THEN
653 l_open_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_open_bidding_date,l_oex_timezone,l_timezone);
654 l_close_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_close_bidding_date,l_oex_timezone,l_timezone);
655 l_preview_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_preview_date,l_oex_timezone,l_timezone);
656 ELSE
657 l_open_date_in_tz := l_open_bidding_date;
658 l_close_date_in_tz := l_close_bidding_date;
659 l_preview_date_in_tz := l_preview_date;
660 l_timezone := l_oex_timezone;
661 END IF;
662
663 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
664
665 wf_engine.SetItemAttrText (itemtype => l_itemType,
666 itemkey => l_itemKey,
667 aname => 'TIMEZONE',
668 avalue => l_timezone_disp);
669
673 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
670 IF (l_preview_date IS NULL) THEN
671 l_timezone1_disp := null;
672
674 itemkey => l_itemkey,
675 aname => 'PREVIEW_DATE',
676 avalue => null);
677
678 wf_engine.SetItemAttrText (itemtype => l_itemtype,
679 itemkey => l_itemkey,
680 aname => 'TP_TIME_ZONE1',
681 avalue => l_timezone1_disp);
682
683 wf_engine.SetItemAttrText (itemtype => l_itemtype,
684 itemkey => l_itemkey,
685 aname => 'PREVIEW_DATE_NOTSPECIFIED',
686 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_PREVIEW_DATE_NOTSPEC',l_msg_suffix));
687 ELSE
688 l_timezone1_disp := l_timezone_disp;
689
690 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
691 itemkey => l_itemkey,
692 aname => 'PREVIEW_DATE',
693 avalue => l_preview_date_in_tz);
694
695 wf_engine.SetItemAttrText (itemtype => l_itemtype,
696 itemkey => l_itemkey,
697 aname => 'TP_TIME_ZONE1',
698 avalue => l_timezone1_disp);
699
700 wf_engine.SetItemAttrText (itemtype => l_itemtype,
701 itemkey => l_itemkey,
702 aname => 'PREVIEW_DATE_NOTSPECIFIED',
703 avalue => null);
704 END IF;
705
706
707 wf_engine.SetItemAttrDate (itemtype => l_itemType,
708 itemkey => l_itemKey,
709 aname => 'AUCTION_START_DATE',
710 avalue => l_open_date_in_tz);
711 if (l_open_auction_now_flag = 'Y') then
712 wf_engine.SetItemAttrText (itemtype => l_itemType,
713 itemkey => l_itemKey,
714 aname => 'OPEN_IMMEDIATELY',
715 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_OPEN_IMM_AFTER_PUB',l_msg_suffix));
716
717 wf_engine.SetItemAttrText (itemtype => l_itemType,
718 itemkey => l_itemKey,
719 aname => 'O_TIMEZONE',
720 avalue => null);
721 else
722 wf_engine.SetItemAttrText (itemtype => l_itemType,
723 itemkey => l_itemKey,
724 aname => 'OPEN_IMMEDIATELY',
725 avalue =>null);
726 wf_engine.SetItemAttrText (itemtype => l_itemType,
727 itemkey => l_itemKey,
728 aname => 'O_TIMEZONE',
729 avalue => l_timezone_disp);
730 end if;
731
732 wf_engine.SetItemAttrDate (itemtype => l_itemType,
733 itemkey => l_itemKey,
734 aname => 'AUCTION_END_DATE',
735 avalue => l_close_date_in_tz);
736
737
738 wf_engine.SetItemAttrText (itemtype => l_itemType,
739 itemkey => l_itemKey,
740 aname => 'NOTE_TO_APPROVERS',
741 avalue => p_note_to_approvers);
742 select document_number
743 into l_orig_document_number
744 from pon_auction_headers_all
745 where auction_header_id = l_auction_header_id_orig_amend;
746
747 -- set notification subjects
748 set_notification_subject(l_itemType, l_itemKey, l_msg_suffix, l_doc_number, l_orig_document_number, l_amendment_number, l_auction_title);
749
750 /* Get the creator's full name */
751 select emp.full_name into l_creator_full_name from
752 per_all_people_f emp,
753 fnd_user fnd
754 where fnd.employee_id=emp.person_id and
755 fnd.user_id = l_creator_user_id and
756 trunc(sysdate) between emp.effective_start_date and emp.effective_end_date;
757
758 wf_engine.SetItemAttrText (itemtype => l_itemType,
759 itemkey => l_itemKey,
760 aname => 'AUCTIONEER_NAME',
761 avalue => l_creator_full_name);
762
763
764 wf_engine.SetItemAttrText (itemtype => l_itemType,
765 itemkey => l_itemKey,
766 aname => 'ORIGIN_USER_NAME',
767 avalue => fnd_global.user_name);
768
769 -- bug 11732401 start
770 l_resp_id := fnd_global.RESP_ID;
771 l_appl_id := fnd_global.RESP_APPL_ID;
772 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
773 itemkey => l_itemKey,
774 aname => 'PREPARER_RESP_ID',
775 avalue => l_resp_id);
776
777 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
778 itemkey => l_itemKey,
779 aname => 'PREPARER_APPL_ID',
780 avalue => l_appl_id);
781 -- bug 11732401 end
782
783 -- use user_id wherever possible
784 select user_id
785 into l_submit_user_id
786 from fnd_user
787 where user_name = p_submit_user_name;
788
789
790 UPD_AUCTION_STATUSHISTORY(p_auction_header_id,
791 'SUBMIT',
792 p_note_to_approvers,
793 l_submit_user_id,
794 'USER',
795 NULL); -- Adding by amundhra for Federal CLM, signed date will be null here
796
797
798 -- Bug 4295915: Set the workflow owner
802
799 wf_engine.SetItemOwner(itemtype => l_itemtype,
800 itemkey => l_itemkey,
801 owner => fnd_global.user_name);
803 wf_engine.StartProcess (itemType => l_itemType,
804 itemKey => l_itemKey );
805
806 /* Update Headers table */
807 UPDATE pon_auction_headers_all set
808 wf_approval_item_key = l_itemKey,
809 approval_status = 'INPROCESS'
810 WHERE auction_header_id = p_auction_header_id;
811
812 --bug 7602688
813 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
814
815 end SUBMIT_FOR_APPROVAL;
816
817 PROCEDURE StartUserApprovalProcess(itemtype in varchar2,
818 Itemkey in varchar2,
819 actid in number,
820 uncmode in varchar2,
821 resultout out nocopy varchar2) is
822 l_auction_header_id number;
823 l_seq varchar2(100);
824 l_itemKey varchar2(240);
825 l_itemType varchar(25) := 'PONAPPRV';
826 l_creator_user_id number;
827 l_creator_full_name varchar2(240);
828 l_creator_user_name varchar2(100);
829 l_creator_session_lang_code varchar2(3);
830 l_creator_time_zone varchar2(80);
831 l_doctype_group_name varchar2(100);
832 l_msg_suffix varchar2(10);
833 l_auction_contact_id number;
834 l_language_code varchar2(3);
835 l_timezone varchar2(100);
836 l_timezone_disp varchar2(100);
837 l_oex_timezone varchar2(100);
838 l_open_date_in_tz date;
839 l_close_date_in_tz date;
840 l_url_preview varchar2(500);
841 l_url_modify varchar2(500);
842 l_timeout_factor number;
843 l_subtab varchar(80);
844 l_note_to_approvers Varchar2(2000);
845 l_publish_auction_now_flag varchar2(1);
846 l_open_auction_now_flag varchar2(1);
847 l_reminder_date date;
848 l_preparer_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
849 l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
850 l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
851 l_preparer_tp_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
852 l_auction_start_date DATE;
853 l_auction_end_date DATE;
854 l_timezone_dsp varchar2(100);
855 l_review_changes_url VARCHAR2(2000);
856 l_orig_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
857 l_round_number NUMBER;
858 l_amendment_number NUMBER;
859 l_preview_date_in_tz DATE;
860 l_timezone1_disp varchar2(240);
861 l_preview_date_nspec FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
862 l_redirect_func VARCHAR2(50);
863
864 -- bug 11732401
865 l_is_federal NUMBER;
866 l_resp_id NUMBER;
867 l_appl_id NUMBER;
868
869 Cursor C_APPROVALS(p_auction_header_id number, p_timeout_factor number) is
870 select u.user_name user_name,
871 u.user_id,
872 auc.close_bidding_date close_bidding_date,
873 auc.auction_title,
874 decode(nvl(auc.open_auction_now_flag,'N'),'Y',to_date(null),auc.open_bidding_date) open_bidding_date,
875 decode(nvl(auc.publish_auction_now_flag,'N'),'Y',to_date(null),auc.view_by_date) view_by_date,
876 auc.document_number doc_number,
877 trading_partner_contact_id auction_contact_id,
878 nvl(auc.publish_auction_now_flag,'N') publish_auction_now_flag,
879 nvl(auc.open_auction_now_flag,'N') open_auction_now_flag,
880 auc.auction_header_id_orig_amend
881 from pon_neg_team_members neg, pon_auction_headers_all auc, fnd_user u
882 where neg.auction_header_id = auc.auction_header_id and
883 auc.auction_header_id = p_auction_header_id
884 and neg.APPROVER_FLAG ='Y'
885 AND neg.MENU_NAME <> 'EMD_ADMIN' --FOR ERIC TEST ONLY
886 and u.user_id = neg.user_id;
887 begin
888 --INSERT INTO ERIC_LOG VALUES ('INTO StartUserApprovalProcess','','','','','','','');--for eric test only
889
890 l_timeout_factor := .5; -- use to get this value from an item attribute
891 -- but WFLOAD was complaining....
892
893 if l_timeout_factor <= 0 or l_timeout_factor >=1 then
894 l_timeout_factor := .5;
895 end if;
896
897 l_preparer_tp_name := wf_engine.GetItemAttrText (itemtype => itemType,
898 itemkey => itemKey,
899 aname => 'PREPARER_TP_NAME');
900
901 l_auction_title := wf_engine.GetItemAttrText (itemtype => itemType,
902 itemkey => itemKey,
903 aname => 'AUCTION_TITLE');
904
905 l_doc_number := wf_engine.GetItemAttrText (itemtype => itemType,
906 itemkey => itemKey,
907 aname => 'DOC_NUMBER');
908
909 l_preparer_tp_contact_name := wf_engine.GetItemAttrText (itemtype => itemType,
910 itemkey => itemKey,
911 aname => 'PREPARER_TP_CONTACT_NAME');
912
913 l_auction_start_date := wf_engine.GetItemAttrDate (itemtype => itemType,
914 itemkey => itemKey,
915 aname => 'AUCTION_START_DATE');
916
917 l_auction_end_date := wf_engine.GetItemAttrDate (itemtype => itemType,
918 itemkey => itemKey,
919 aname => 'AUCTION_END_DATE');
920
921 l_timezone_dsp := wf_engine.GetItemAttrText (itemtype => itemType,
925
922 itemkey => itemKey,
923 aname => 'TIMEZONE');
924
926
927 l_note_to_approvers := wf_engine.GetItemAttrText (itemtype => itemType,
928 itemkey => itemKey,
929 aname => 'NOTE_TO_APPROVERS');
930
931 l_preview_date_in_tz := wf_engine.GetItemAttrDate (itemtype => itemType,
932 itemkey => itemKey,
933 aname => 'PREVIEW_DATE');
934 l_timezone1_disp := wf_engine.GetItemAttrText (itemtype => itemType,
935 itemkey => itemKey,
936 aname => 'TP_TIME_ZONE1');
937
938 l_preview_date_nspec := wf_engine.GetItemAttrText (itemtype => itemType,
939 itemkey => itemKey,
940 aname => 'PREVIEW_DATE_NOTSPECIFIED');
941 l_msg_suffix := wf_engine.GetItemAttrText (itemtype => itemType,
942 itemkey => itemKey,
943 aname => 'MSG_SUFFIX');
944
945 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemType,
946 itemkey => itemKey,
947 aname => 'AUCTION_HEADER_ID');
948
949 l_round_number := wf_engine.GetItemAttrNumber (itemtype => itemType,
950 itemkey => itemKey,
951 aname => 'DOC_ROUND_NUMBER');
952
953
954
955 l_amendment_number := wf_engine.GetItemAttrNumber (itemtype => itemType,
956 itemkey => itemKey,
957 aname => 'DOC_AMENDMENT_NUMBER');
958
959 l_creator_full_name:= wf_engine.GetItemAttrText (itemtype => itemType,
960 itemkey => itemKey,
961 aname => 'AUCTIONEER_NAME');
962
963 l_redirect_func:= wf_engine.GetItemAttrText (itemtype => itemType,
964 itemkey => itemKey,
965 aname => 'REVIEWPG_REDIRECTFUNC');
966
967 /* Preserve creator's session language */
968 l_creator_user_name := wf_engine.GetItemAttrText (itemtype => itemType,
969 itemkey => itemKey,
970 aname => 'CREATOR_USER_NAME');
971
972 l_creator_user_id := wf_engine.GetItemAttrNumber (itemtype => itemType,
973 itemkey => itemKey,
974 aname => 'CREATOR_USER_ID');
975
976 -- bug 11732401
977 l_is_federal := wf_engine.GetItemAttrNumber (itemtype => itemType,
978 itemkey => itemKey,
979 aname => 'IS_FEDERAL');
980
981 l_resp_id := wf_engine.GetItemAttrNumber (itemtype => itemType,
982 itemkey => itemKey,
983 aname => 'PREPARER_RESP_ID');
984
985 l_appl_id := wf_engine.GetItemAttrNumber (itemtype => itemType,
986 itemkey => itemKey,
987 aname => 'PREPARER_APPL_ID');
988 -- bug 11732401 end
989
990
991 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_creator_user_id,l_creator_session_lang_code);
992
993 for r1 in C_APPROVALS(l_auction_header_id,l_timeout_factor) loop
994
995 --INSERT INTO ERIC_LOG VALUES ('INTO for r1 in C_APPROVALS','','','','','','','');--for eric test only
996
997 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
998 FND_LOG.string(log_level => FND_LOG.level_statement,
999 module => g_module_prefix || 'StartUserApprovalProcess',
1000 message => 'r1.user_id : ' || r1.user_id);
1001 END IF; --}
1002
1003 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(r1.user_id,l_language_code);
1004
1005 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
1006 FND_LOG.string(log_level => FND_LOG.level_statement,
1007 module => g_module_prefix || 'StartUserApprovalProcess',
1008 message => 'r1.user_id : '|| r1.user_id ||';'|| 'l_language_code : ' || l_language_code);
1009 END IF; --}
1010
1011 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
1012 FND_LOG.string(log_level => FND_LOG.level_statement,
1013 module => g_module_prefix || 'StartUserApprovalProcess',
1014 message => '1. Calling SET_SESSION_LANGUAGE with l_language_code : ' || l_language_code);
1015 END IF; --}
1016
1017 -- bug 7602688 session language for workflow is already set in submit_for_approval procedure.
1018 -- this process is called from 'sourcingapproval' workflow only. so no need to set again.
1019 -- PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null,l_language_code);
1020
1021 --Bug 6472383 : If the Negotiation Preview date is mentioned as 'Not Specified', i.e. if the value of
1022 -- l_preview_date_nspec is not null, we need to replace it with the string specific to recipient's language
1023 IF (l_preview_date_nspec is not null) THEN
1024 l_preview_date_nspec := PON_AUCTION_PKG.getMessage('PON_AUC_PREVIEW_DATE_NOTSPEC',l_msg_suffix);
1025 END IF;
1026
1027 l_itemKey := itemkey || '_' || r1.user_id;
1028
1029
1030 wf_engine.createProcess ( itemType => l_itemType,
1031 itemKey => l_itemKey,
1035 if (r1.view_by_date is not null) then
1032 process => 'USERAPPROVALS');
1033
1034
1036 l_reminder_date := r1.view_by_date;
1037 elsif (r1.open_bidding_date is not null) then
1038 l_reminder_date := r1.open_bidding_date;
1039 else
1040 l_reminder_date := r1.close_bidding_date;
1041 end if;
1042
1043 select sysdate+((l_reminder_date - sysdate) * l_timeout_factor)
1044 into l_reminder_date
1045 from dual;
1046
1047 wf_engine.SetItemAttrDate (itemtype => l_itemType,
1048 itemkey => l_itemKey,
1049 aname => 'TIMEOUT_USERPROCESS',
1050 avalue => l_reminder_date);
1051
1052 wf_engine.SetItemAttrText (itemtype => l_itemType,
1053 itemkey => l_itemKey,
1054 aname => 'CREATOR_USER_NAME',
1055 avalue => l_creator_user_name);
1056
1057 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
1058 itemkey => l_itemKey,
1059 aname => 'CREATOR_USER_ID',
1060 avalue => l_creator_user_id);
1061
1062 wf_engine.SetItemAttrText (itemtype => l_itemType,
1063 itemkey => l_itemKey,
1064 aname => 'REVIEWPG_REDIRECTFUNC',
1065 avalue => l_redirect_func);
1066
1067 wf_engine.SetItemAttrText (itemtype => l_itemType,
1068 itemkey => l_itemKey,
1069 aname => 'PREPARER_TP_NAME',
1070 avalue => l_preparer_tp_name);
1071
1072 wf_engine.SetItemAttrText (itemtype => l_itemType,
1073 itemkey => l_itemKey,
1074 aname => 'AUCTION_TITLE',
1075 avalue => l_auction_title);
1076
1077 wf_engine.SetItemAttrText (itemtype => l_itemType,
1078 itemkey => l_itemKey,
1079 aname => 'DOC_NUMBER',
1080 avalue => l_doc_number);
1081
1082 wf_engine.SetItemAttrText (itemtype => l_itemType,
1083 itemkey => l_itemKey,
1084 aname => 'PREPARER_TP_CONTACT_NAME',
1085 avalue => l_preparer_tp_contact_name);
1086
1087 wf_engine.SetItemAttrDate (itemtype => l_itemType,
1088 itemkey => l_itemKey,
1089 aname => 'AUCTION_START_DATE',
1090 avalue => l_auction_start_date);
1091
1092 wf_engine.SetItemAttrDate (itemtype => l_itemType,
1093 itemkey => l_itemKey,
1094 aname => 'AUCTION_END_DATE',
1095 avalue => l_auction_end_date);
1096
1097 wf_engine.SetItemAttrText (itemtype => l_itemType,
1098 itemkey => l_itemKey,
1099 aname => 'TIMEZONE',
1100 avalue => l_timezone_dsp);
1101
1102 wf_engine.SetItemAttrDate (itemtype => l_itemType,
1103 itemkey => l_itemKey,
1104 aname => 'PREVIEW_DATE',
1105 avalue => l_preview_date_in_tz);
1106
1107 wf_engine.SetItemAttrText (itemtype => l_itemType,
1108 itemkey => l_itemKey,
1109 aname => 'TP_TIME_ZONE1',
1110 avalue => l_timezone1_disp);
1111
1112
1113 wf_engine.SetItemAttrText (itemtype => l_itemType,
1114 itemkey => l_itemKey,
1115 aname => 'PREVIEW_DATE_NOTSPECIFIED',
1116 avalue => l_preview_date_nspec);
1117
1118 wf_engine.SetItemAttrText (itemtype => l_itemType,
1119 itemkey => l_itemKey,
1120 aname => 'NOTE_TO_APPROVERS',
1121 avalue => l_note_to_approvers);
1122
1123 wf_engine.SetItemAttrText (itemtype => l_itemType,
1124 itemkey => l_itemKey,
1125 aname => 'TOP_PROCESS_ITEM_KEY',
1126 avalue =>
1127 wf_engine.GetItemAttrText (itemtype => l_itemType,
1128 itemkey => itemKey,
1129 aname => 'TOP_PROCESS_ITEM_KEY')
1130 );
1131
1132 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
1133 itemkey => l_itemKey,
1134 aname => 'AUCTION_HEADER_ID',
1135 avalue => l_auction_header_id);
1136
1137 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
1138 itemkey => l_itemKey,
1139 aname => 'DOC_ROUND_NUMBER',
1140 avalue => l_round_number);
1141
1142 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
1143 itemkey => l_itemKey,
1144 aname => 'DOC_AMENDMENT_NUMBER',
1145 avalue => l_amendment_number);
1146
1147 l_review_changes_url := pon_wf_utl_pkg.get_dest_page_url (
1148 p_dest_func => 'PON_NEG_CRT_HEADER'
1149 ,p_notif_performer => 'BUYER');
1150
1151 --Bug 11898698
1155
1152 --Modifying the language_code in the URL with that of the recipient
1153 --The profile "ICX_LANGUAGE" needs to be set for the recipient for this fix
1154 l_review_changes_url:=regexp_replace(l_review_changes_url , 'language_code='||fnd_global.current_language, 'language_code='||l_language_code);
1156 wf_engine.SetItemAttrText (itemtype => l_itemType,
1157 itemkey => l_itemKey,
1158 aname => 'REVIEW_CHANGES_URL',
1159 avalue => l_review_changes_url);
1160
1161
1162 wf_engine.SetItemAttrText (itemtype => l_itemType,
1163 itemkey => l_itemKey,
1164 aname => 'APPOVER',
1165 avalue => r1.user_name);
1166
1167 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
1168 itemkey => l_itemKey,
1169 aname => 'APPOVER_ID',
1170 avalue => r1.user_id);
1171
1172
1173 wf_engine.SetItemAttrText (itemtype => l_itemType,
1174 itemkey => l_itemKey,
1175 aname => 'ORIGIN_USER_NAME',
1176 avalue => fnd_global.user_name);
1177
1178 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(r1.user_name);
1179
1180 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
1181
1182 if (l_timezone is null) then
1183 l_timezone := l_oex_timezone;
1184 end if;
1185
1186 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
1187
1188 wf_engine.SetItemAttrText (itemtype => l_itemType,
1189 itemkey => l_itemKey,
1190 aname => 'TIMEZONE',
1191 avalue => l_timezone_disp);
1192
1193 l_open_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(r1.open_bidding_date,l_oex_timezone,l_timezone);
1194 l_close_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(r1.close_bidding_date,l_oex_timezone,l_timezone);
1195
1196 wf_engine.SetItemAttrDate (itemtype => l_itemType,
1197 itemkey => l_itemKey,
1198 aname => 'AUCTION_START_DATE',
1199 avalue => l_open_date_in_tz);
1200 if (r1.open_auction_now_flag = 'Y') then
1201 wf_engine.SetItemAttrText (itemtype => l_itemType,
1202 itemkey => l_itemKey,
1203 aname => 'OPEN_IMMEDIATELY',
1204 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_OPEN_IMM_AFTER_PUB',l_msg_suffix));
1205
1206 wf_engine.SetItemAttrText (itemtype => l_itemType,
1207 itemkey => l_itemKey,
1208 aname => 'O_TIMEZONE',
1209 avalue => null);
1210 else
1211 wf_engine.SetItemAttrText (itemtype => l_itemType,
1212 itemkey => l_itemKey,
1213 aname => 'OPEN_IMMEDIATELY',
1214 avalue =>null);
1215
1216 wf_engine.SetItemAttrText (itemtype => l_itemType,
1217 itemkey => l_itemKey,
1218 aname => 'O_TIMEZONE',
1219 avalue => l_timezone_disp);
1220 end if;
1221
1222 wf_engine.SetItemAttrDate (itemtype => l_itemType,
1223 itemkey => l_itemKey,
1224 aname => 'AUCTION_END_DATE',
1225 avalue => l_close_date_in_tz);
1226
1227
1228 wf_engine.SetItemAttrText (itemtype => l_itemType,
1229 itemkey => l_itemKey,
1230 aname => 'NOTE_TO_APPROVERS',
1231 avalue => l_note_to_approvers);
1232
1233 -- bug 11732401
1234 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
1235 itemkey => l_itemKey,
1236 aname => 'IS_FEDERAL',
1237 avalue => l_is_federal);
1238
1239 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
1240 itemkey => l_itemKey,
1241 aname => 'PREPARER_RESP_ID',
1242 avalue => l_resp_id);
1243
1244 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
1245 itemkey => l_itemKey,
1246 aname => 'PREPARER_APPL_ID',
1247 avalue => l_appl_id);
1248 -- bug 11732401 end
1249
1250 select document_number
1251 into l_orig_document_number
1252 from pon_auction_headers_all
1253 where auction_header_id = r1.auction_header_id_orig_amend;
1254
1255 -- set notification subjects
1256 set_notification_subject(l_itemType, l_itemKey, l_msg_suffix, l_doc_number, l_orig_document_number, l_amendment_number, l_auction_title);
1257
1258 -- Bug 4295915: Set the workflow owner
1259 wf_engine.SetItemOwner(itemtype => l_itemtype,
1260 itemkey => l_itemkey,
1261 owner => fnd_global.user_name);
1262
1263 wf_engine.StartProcess (itemType => l_itemType,
1264 itemKey => l_itemKey );
1265 end loop;
1266
1267 /* Reset to creator's language */
1268 -- bug 7602688
1269 --PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null,l_creator_session_lang_code);
1270
1271 end StartUserApprovalProcess;
1272
1273
1274 Function Is_Valid_Response(p_auction_header_id number,p_user_id varchar2,p_source VARCHAR2 DEFAULT 'USER')
1278 Begin
1275 return Varchar2 is
1276 l_result Varchar2(10) := 'N';
1277 ls_error varchar2(4000); -- for eric test only
1279 --ADDED FOR ERIC TEST ONLY,BEGIN
1280 -----------------------------
1281 -- validate the response source from EMD USER or NORMAL APPROVAL USER
1282
1283 --INSERT INTO ERIC_LOG VALUES ('INTO Function Is_Valid_Response','','','','','','','');--for eric test only
1284 IF p_source ='EMD'
1285 THEN
1286 BEGIN
1287 SELECT 'Y'
1288 INTO l_result
1289 FROM
1290 pon_auction_headers_all auc
1291 , pon_neg_team_members neg
1292 WHERE auc.auction_header_id = neg.auction_header_id
1293 AND auc.auction_header_id = p_auction_header_id
1294 AND auc.approval_status = 'INPROCESS'
1295 AND neg.user_id = p_user_id
1296 AND neg.MENU_NAME = 'EMD_ADMIN'
1297 and neg.approver_flag = 'Y'
1298 AND neg.approval_status IS NULL;
1299 EXCEPTION
1300 WHEN OTHERS
1301 THEN
1302 ls_error := sqlcode||sqlerrm;
1303 --INSERT INTO ERIC_LOG VALUES ('EMD Is_Valid_Response error : '||ls_error, 'l_result = '|| l_result,'p_user_id ='||p_user_id,'p_auction_header_id = '||p_auction_header_id,'','','','');--for eric test only
1304 l_result := 'N';
1305 END;
1306
1307 --INSERT INTO ERIC_LOG VALUES ( 'p_source =EMD','l_result= '|| l_result,'','','','','','');--for eric test only
1308 RETURN (l_result) ;
1309 ELSE --NORMAL APPROVAL USER
1310 begin
1311 select 'Y'
1312 into l_result
1313 from pon_auction_headers_all auc,
1314 pon_neg_team_members neg
1315 where auc.auction_header_id = neg.auction_header_id
1316 and auc.auction_header_id = p_auction_header_id
1317 and auc.approval_status = 'INPROCESS'
1318 and neg.user_id = p_user_id
1319 AND neg.MENU_NAME <> 'EMD_ADMIN' --FOR ERIC TEST ONLY
1320 and neg.approver_flag = 'Y'
1321 and neg.approval_status is null;
1322 exception when others then
1323 l_result := 'N';
1324 end;
1325 return(l_result);
1326 END IF;
1327 End Is_Valid_Response;
1328
1329 PROCEDURE User_Approved(itemtype in varchar2,
1330 itemkey in varchar2,
1331 actid in number,
1332 uncmode in varchar2,
1333 resultout out nocopy varchar2) is
1334 l_notes varchar2(2000);
1335 l_user_name varchar2(100);
1336 l_user_id number;
1337 l_auction_header_id number;
1338 l_top_process_item_key Varchar2(240);
1339 l_result Varchar2(30) := 'APPROVED';
1340 --added by amundhra for Federal CLM
1341 l_signed_date DATE := null;
1342
1343 -- bug 11732401
1344 l_is_federal NUMBER;
1345 l_resp_id NUMBER;
1346 l_appl_id NUMBER;
1347 l_prep_resp_id NUMBER;
1348 l_prep_appl_id NUMBER;
1349
1350 begin
1351 /* Get auction header id from the workflow */
1352 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1353 itemkey => itemkey,
1354 aname => 'AUCTION_HEADER_ID');
1355
1356 l_user_name := wf_engine.GetItemAttrText (itemtype => itemtype,
1357 itemkey => itemkey,
1358 aname => 'APPOVER');
1359
1360 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1361 itemkey => itemkey,
1362 aname => 'APPOVER_ID');
1363
1364 l_notes := wf_engine.GetItemAttrText (itemtype => itemtype,
1365 itemkey => itemkey,
1366 aname => 'APPROVER_NOTES');
1367
1368 -- bug 11732401
1369 l_is_federal := wf_engine.getItemAttrNumber (itemtype => itemType,
1370 itemkey => itemKey,
1371 aname => 'IS_FEDERAL');
1372
1373 IF l_is_federal = 1 THEN
1374 l_resp_id := fnd_global.RESP_ID;
1375 l_appl_id := fnd_global.RESP_APPL_ID;
1376
1377 l_prep_resp_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1378 itemkey => itemkey,
1379 aname => 'PREPARER_RESP_ID');
1380
1381 l_prep_appl_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1382 itemkey => itemkey,
1383 aname => 'PREPARER_APPL_ID');
1384 fnd_global.APPS_INITIALIZE (l_user_id, l_prep_resp_id, l_prep_appl_id);
1385 END IF;
1386 -- bug 11732401 end
1387
1388 --added by amundhra for Federal CLM
1389
1390
1391
1392 -- l_is_federal := wf_engine.getItemAttrNumber (itemtype => itemType,
1393 -- itemkey => itemKey,
1394 -- aname => 'IS_FEDERAL');
1395
1396 l_signed_date := Get_Signed_Date(l_auction_header_id, itemtype, itemkey);
1397
1398
1399 l_top_process_item_key := wf_engine.GetItemAttrText (itemtype => itemType,
1400 itemkey => itemKey,
1401 aname => 'TOP_PROCESS_ITEM_KEY');
1402
1403 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1404 itemkey => l_top_process_item_key,
1405 aname => 'APPOVER_ID',
1406 avalue => l_user_id);
1407
1411 avalue => l_user_name);
1408 wf_engine.SetItemAttrText (itemtype => itemtype,
1409 itemkey => l_top_process_item_key,
1410 aname => 'APPOVER',
1412
1413 /* Check is the responder a valid approver */
1414 if (Is_Valid_Response(l_auction_header_id,l_user_id) = 'N') then
1415 /* Responder is not a valid approver. Ignore this response */
1416 return;
1417 end if;
1418
1419
1420 /* Insert a row into history table */
1421 UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
1422 l_result,
1423 l_notes,
1424 l_user_id,
1425 'USER',
1426 l_signed_date);-- Adding by amundhra for Federal CLM, signed date
1427
1428
1429 Process_If_Doc_Approved(l_auction_header_id,l_top_process_item_key);
1430
1431 IF l_is_federal = 1 THEN -- bug 11732401
1432 fnd_global.APPS_INITIALIZE (l_user_id, l_resp_id, l_appl_id);
1433 END IF;
1434
1435 end User_approved;
1436
1437 /* This is a sort of "backup" to approve online if workflow
1438 fails */
1439 PROCEDURE User_Decision_Without_WF(p_user_id in number,
1440 p_decision in varchar2,
1441 p_notes in varchar2,
1442 p_auctionHeaderId in number) is
1443 l_top_process_itemKey varchar2(240);
1444 begin
1445 /* Check is the responder a valid approver */
1446 if (Is_Valid_Response(p_auctionHeaderId,p_user_id) = 'N') then
1447 /* Responder is not a valid approver. Ignore this response */
1448 return;
1449 end if;
1450
1451 /* Insert a row into history table */
1452 UPD_AUCTION_STATUSHISTORY(p_auctionHeaderId, p_decision,
1453 p_notes, p_user_id,'USER',NULL);-- Adding by amundhra for Federal CLM, signed date will be null here
1454 -- need to check whether to put null or sysdate
1455
1456 /* Get the top process item key */
1457 Select wf_approval_item_key
1458 into l_top_process_itemKey
1459 from pon_auction_headers_all
1460 where auction_header_id = p_auctionHeaderId;
1461
1462 if (p_decision = 'APPROVE') then
1463 Process_If_Doc_Approved(p_auctionHeaderId,l_top_process_itemKey);
1464 end if;
1465 if (p_decision = 'REJECT') then
1466 Process_Doc_Rejected(p_auctionHeaderId,l_top_process_itemKey);
1467 end if;
1468
1469 end User_Decision_Without_WF;
1470
1471 PROCEDURE User_Rejected(itemtype in varchar2,
1472 itemkey in varchar2,
1473 actid in number,
1474 uncmode in varchar2,
1475 resultout out nocopy varchar2) is
1476 l_notes varchar2(2000);
1477 l_user_name varchar2(100);
1478 l_user_id number;
1479 l_auction_header_id number;
1480 l_top_process_item_key Varchar2(240);
1481 l_result Varchar2(30) := 'REJECTED';
1482 --added by amundhra for Federal CLM
1483 l_signed_date DATE;
1484
1485 -- bug 11732401
1486 l_is_federal NUMBER;
1487 l_resp_id NUMBER;
1488 l_appl_id NUMBER;
1489 l_prep_resp_id NUMBER;
1490 l_prep_appl_id NUMBER;
1491
1492 begin
1493
1494 -- bug 11732401
1495 l_is_federal := wf_engine.getItemAttrNumber (itemtype => itemType,
1496 itemkey => itemKey,
1497 aname => 'IS_FEDERAL');
1498
1499 IF l_is_federal = 1 THEN
1500 l_resp_id := fnd_global.RESP_ID;
1501 l_appl_id := fnd_global.RESP_APPL_ID;
1502
1503
1504 l_prep_resp_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1505 itemkey => itemkey,
1506 aname => 'PREPARER_RESP_ID');
1507
1508 l_prep_appl_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1509 itemkey => itemkey,
1510 aname => 'PREPARER_APPL_ID');
1511 fnd_global.APPS_INITIALIZE (l_user_id, l_prep_resp_id, l_prep_appl_id);
1512 END IF;
1513 -- bug 11732401 end
1514
1515 /* Get auction header id from the workflow */
1516 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1517 itemkey => itemkey,
1518 aname => 'AUCTION_HEADER_ID');
1519
1520 l_user_name := wf_engine.GetItemAttrText (itemtype => itemtype,
1521 itemkey => itemkey,
1522 aname => 'APPOVER');
1523
1524 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1525 itemkey => itemkey,
1526 aname => 'APPOVER_ID');
1527
1528 l_notes := wf_engine.GetItemAttrText (itemtype => itemtype,
1529 itemkey => itemkey,
1530 aname => 'APPROVER_NOTES');
1531
1532 --added by amundhra for Federal CLM
1533 l_signed_date := Get_Signed_Date(l_auction_header_id, itemtype, itemkey);
1534
1535
1536
1537 l_top_process_item_key := wf_engine.GetItemAttrText (itemtype => itemType,
1538 itemkey => itemKey,
1539 aname => 'TOP_PROCESS_ITEM_KEY');
1540
1541 wf_engine.SetItemAttrText (itemtype => itemtype,
1542 itemkey => l_top_process_item_key,
1546 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1543 aname => 'APPOVER',
1544 avalue => l_user_name);
1545
1547 itemkey => l_top_process_item_key,
1548 aname => 'APPOVER_ID',
1549 avalue => l_user_id);
1550
1551 /* Check is the responder a valid approver */
1552 if (Is_Valid_Response(l_auction_header_id,l_user_id) = 'N') then
1553 /* Responder is not a valid approver. Ignore this response */
1554 return;
1555 end if;
1556 wf_engine.SetItemAttrText (itemtype => itemtype,
1557 itemkey => l_top_process_item_key,
1558 aname => 'NOTE_TO_BUYER_ON_REJECT',
1559 avalue => l_notes);
1560
1561 /*Update PON_NEG_TEAM_MEMEBERS APPROVAL_STATUS field */
1562 /* Insert a row into history table */
1563
1564 --changed by amundhra for Federal CLM
1565 --added l_signed_date in the IN PARAM
1566 UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
1567 l_result,
1568 l_notes,
1569 l_user_id,
1570 'USER',
1571 l_signed_date); ---- Adding by amundhra for Federal CLM
1572 Process_Doc_Rejected(l_auction_header_id,l_top_process_item_key);
1573
1574 IF l_is_federal = 1 THEN -- bug 11732401
1575 fnd_global.APPS_INITIALIZE (l_user_id, l_resp_id, l_appl_id);
1576 END IF;
1577
1578 end User_Rejected;
1579
1580 /*Added by amundhra for Federal CLM*/
1581 PROCEDURE UPD_AUCTION_HEADER(p_auction_header_id NUMBER,
1582 p_signed_date DATE) IS
1583
1584 l_effective_date DATE;
1585 --l_signed_date DATE;
1586
1587 BEGIN
1588 SELECT effective_date
1589 INTO l_effective_date
1590 FROM pon_auction_headers_all
1591 WHERE auction_header_id = p_auction_header_id;
1592
1593
1594
1595
1596
1597 IF(l_effective_date IS NULL OR l_effective_date < SYSDATE) THEN
1598
1599
1600
1601
1602 UPDATE pon_auction_headers_all
1603 SET effective_date = p_signed_date,
1604 signed_date = p_signed_date
1605 WHERE auction_header_id = p_auction_header_id;
1606
1607 ELSE
1608
1609 UPDATE pon_auction_headers_all
1610 SET signed_date = p_signed_date
1611 WHERE auction_header_id = p_auction_header_id;
1612
1613 END IF;
1614
1615 END UPD_AUCTION_HEADER;
1616
1617 PROCEDURE Doc_Approved(itemtype in varchar2,
1618 itemkey in varchar2,
1619 actid in number,
1620 uncmode in varchar2,
1621 resultout out nocopy varchar2) is
1622
1623 l_auction_header_id Number;
1624 l_status Varchar2(30) := 'APPROVED';
1625 l_user_id Number;
1626 l_approve_date PON_ACTION_HISTORY.ACTION_DATE%TYPE;
1627 l_approve_date_in_tz DATE;
1628 l_language_code varchar2(100);
1629 l_timezone varchar2(100);
1630 l_timezone_disp varchar2(100);
1631 l_oex_timezone varchar2(100);
1632 l_auction_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
1633 l_preview_date_in_tz DATE;
1634 l_timezone1_disp varchar2(240);
1635 l_preview_date_nspec FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1636
1637 --added by amundhra for Federal CLM
1638 l_is_federal NUMBER;
1639 l_signed_date DATE;
1640 l_is_amendment varchar2(1);
1641
1642 Begin
1643 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1644 itemkey => itemkey,
1645 aname => 'AUCTION_HEADER_ID');
1646
1647 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1648 itemkey => itemkey,
1649 aname => 'CREATOR_USER_ID');
1650
1651 l_auction_contact_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1652 itemkey => itemkey,
1653 aname => 'TRADING_PARTNER_CONTACT_ID');
1654
1655
1656 l_preview_date_in_tz := wf_engine.GetItemAttrDate (itemtype => itemType,
1657 itemkey => itemKey,
1658 aname => 'PREVIEW_DATE');
1659 l_timezone1_disp := wf_engine.GetItemAttrText (itemtype => itemType,
1660 itemkey => itemKey,
1661 aname => 'TP_TIME_ZONE1');
1662
1663 l_preview_date_nspec := wf_engine.GetItemAttrText (itemtype => itemType,
1664 itemkey => itemKey,
1665 aname => 'PREVIEW_DATE_NOTSPECIFIED');
1666 SELECT max(action_date)
1667 INTO l_approve_date
1668 FROM pon_action_history
1669 WHERE object_id = l_auction_header_id
1670 and object_type_code = 'NEGOTIATION'
1671 and action_type = 'APPROVE';
1672
1673 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_user_id,l_language_code);
1674 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
1675
1676 -- Get the user's time zone
1677 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(l_auction_contact_id);
1678
1679 if (l_timezone is null or l_timezone = '') then
1680 l_timezone := l_oex_timezone;
1681 end if;
1682
1686 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(l_timezone) = 1) THEN
1683
1684 -- Convert the date to the user's timezone.
1685 -- If the timezone is not recognized, just use server timezone
1687 l_approve_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_approve_date,l_oex_timezone,l_timezone);
1688 ELSE
1689 l_approve_date_in_tz := l_approve_date;
1690 l_timezone := l_oex_timezone;
1691 END IF;
1692
1693 -- Set the dates based on the user's time zone
1694 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
1695
1696 wf_engine.SetItemAttrDate (itemtype => itemType,
1697 itemkey => itemKey,
1698 aname => 'APPROVE_DATE',
1699 avalue => l_approve_date_in_tz);
1700
1701 wf_engine.SetItemAttrText (itemtype => itemType,
1702 itemkey => itemKey,
1703 aname => 'TIMEZONE',
1704 avalue => l_timezone_disp);
1705
1706 --added BY amundhra for federal CLM
1707 -- l_is_federal := wf_engine.getItemAttrNumber (itemtype => itemType,
1708 -- itemkey => itemKey,
1709 -- aname => 'IS_FEDERAL');
1710
1711 /* For CLM ST bug 9766460 : Commented below line
1712 * When user approves from notification, then signed_date should be same as approval_date.
1713 */
1714 --l_signed_date := Get_Signed_Date(l_auction_header_id, itemtype, itemkey);
1715 if PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(l_auction_header_id) = 1 then
1716 l_signed_date := l_approve_date;
1717 else
1718 l_signed_date := null;
1719 end if;
1720 -- End of ST Bug fix : 9766460
1721
1722 if ( l_signed_date is not null ) then
1723 UPD_AUCTION_HEADER(l_auction_header_id, l_signed_date);
1724 end if;
1725
1726 UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
1727 l_status,
1728 NULL,
1729 l_user_id,
1730 'AUCTION',
1731 l_signed_date); -- Adding by amundhra for Federal CLM, signed date will be null here
1732 -- need to check if null of sysdate needed (amundhra)
1733
1734 End Doc_Approved;
1735
1736 PROCEDURE Doc_Rejected(itemtype in varchar2,
1737 itemkey in varchar2,
1738 actid in number,
1739 uncmode in varchar2,
1740 resultout out nocopy varchar2) is
1741
1742 l_auction_header_id Number;
1743 l_status Varchar2(30) := 'REJECTED';
1744 l_user_id Number;
1745 l_rejection_note Varchar2(2000);
1746 l_reject_date PON_ACTION_HISTORY.ACTION_DATE%TYPE;
1747 l_reject_date_in_tz DATE;
1748 l_language_code varchar2(100);
1749 l_timezone varchar2(100);
1750 l_timezone_disp varchar2(100);
1751 l_oex_timezone varchar2(100);
1752 l_auction_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
1753 l_preview_date_in_tz DATE;
1754 l_timezone1_disp varchar2(240);
1755 l_preview_date_nspec FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1756
1757 --added by amundhra for Federal CLM
1758 l_signed_date DATE;
1759
1760 Begin
1761 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1762 itemkey => itemkey,
1763 aname => 'AUCTION_HEADER_ID');
1764
1765 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1766 itemkey => itemkey,
1767 aname => 'CREATOR_USER_ID');
1768
1769 l_rejection_note := wf_engine.GetItemAttrText (itemtype => itemtype,
1770 itemkey => itemkey,
1771 aname => 'NOTE_TO_BUYER_ON_REJECT');
1772
1773 l_auction_contact_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1774 itemkey => itemkey,
1775 aname => 'TRADING_PARTNER_CONTACT_ID');
1776
1777
1778 l_preview_date_in_tz := wf_engine.GetItemAttrDate (itemtype => itemType,
1779 itemkey => itemKey,
1780 aname => 'PREVIEW_DATE');
1781
1782
1783 l_timezone1_disp := wf_engine.GetItemAttrText (itemtype => itemType,
1784 itemkey => itemKey,
1785 aname => 'TP_TIME_ZONE1');
1786
1787 l_preview_date_nspec := wf_engine.GetItemAttrText (itemtype => itemType,
1788 itemkey => itemKey,
1789 aname => 'PREVIEW_DATE_NOTSPECIFIED');
1790
1791 SELECT max(action_date)
1792 INTO l_reject_date
1793 FROM pon_action_history
1794 WHERE object_id = l_auction_header_id
1795 and object_type_code = 'NEGOTIATION'
1796 and action_type = 'REJECT';
1797
1798 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_user_id,l_language_code);
1799 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
1800
1801 -- Get the user's time zone
1802 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(l_auction_contact_id);
1803
1807
1804 if (l_timezone is null or l_timezone = '') then
1805 l_timezone := l_oex_timezone;
1806 end if;
1808
1809 -- Convert the date to the user's timezone.
1810 -- If the timezone is not recognized, just use server timezone
1811 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(l_timezone) = 1) THEN
1812 l_reject_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_reject_date,l_oex_timezone,l_timezone);
1813 ELSE
1814 l_reject_date_in_tz := l_reject_date;
1815 l_timezone := l_oex_timezone;
1816 END IF;
1817
1818 -- Set the dates based on the user's time zone
1819 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
1820
1821 wf_engine.SetItemAttrDate (itemtype => itemType,
1822 itemkey => itemKey,
1823 aname => 'REJECT_DATE',
1824 avalue => l_reject_date_in_tz);
1825
1826 wf_engine.SetItemAttrText (itemtype => itemType,
1827 itemkey => itemKey,
1828 aname => 'TIMEZONE',
1829 avalue => l_timezone_disp);
1830
1831 /* For CLM ST bug 9766460 : Commented above line
1832 * When user rejects from notification, then signed_date should be same as rejection_date.
1833 */
1834 --l_signed_date := Get_Signed_Date(l_auction_header_id, itemtype, itemkey);
1835 if PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(l_auction_header_id) = 1 then
1836 l_signed_date := l_reject_date;
1837 else
1838 l_signed_date := null;
1839 end if;
1840 -- End of ST Bug fix : 9766460
1841
1842 if ( l_signed_date is not null ) then
1843 UPD_AUCTION_HEADER(l_auction_header_id, l_signed_date);
1844 end if;
1845
1846
1847 UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
1848 l_status,
1849 l_rejection_note,
1850 l_user_id,
1851 'AUCTION',
1852 l_signed_date); -- Adding by amundhra for Federal CLM, signed date will be null here
1853 -- need to check if sysdate needed (amundhra)
1854
1855 close_child_process(itemkey);
1856
1857 End Doc_Rejected;
1858
1859 PROCEDURE Doc_timedout(itemtype in varchar2,
1860 itemkey in varchar2,
1861 actid in number,
1862 uncmode in varchar2,
1863 resultout out nocopy varchar2) is
1864 l_auction_header_id Number;
1865 l_status Varchar2(30) := 'TIMEOUT';
1866 l_user_id Number;
1867 l_rejection_note Varchar2(2000);
1868 Begin
1869 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1870 itemkey => itemkey,
1871 aname => 'AUCTION_HEADER_ID');
1872
1873 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1874 itemkey => itemkey,
1875 aname => 'CREATOR_USER_ID');
1876
1877 l_rejection_note := wf_engine.GetItemAttrText (itemtype => itemtype,
1878 itemkey => itemkey,
1879 aname => 'NOTE_TO_BUYER_ON_REJECT');
1880
1881 UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
1882 l_status,
1883 NULL,
1884 l_user_id,'AUCTION',
1885 NULL);-- Adding by amundhra for Federal CLM, signed date will be null here
1886
1887 close_child_process(itemkey);
1888
1889 End Doc_timedout;
1890
1891 --changed by amundhra for Federal CLM
1892 --added l_signed_date in the IN PARAM
1893
1894 Procedure UPD_AUCTION_STATUSHISTORY(p_auction_header_id number,
1895 p_status Varchar2,
1896 p_notes Varchar2,
1897 p_user_id number,
1898 p_upd_history_type VARCHAR2,
1899 p_signed_date DATE) is
1900
1901 i number ; --for eric test only
1902
1903 begin
1904
1905 --INSERT INTO ERIC_LOG VALUES ('go into UPD_AUCTION_STATUSHISTORY function','p_upd_history_type =' ||p_upd_history_type,'p_status =' ||p_status,'p_auction_header_id = ' ||p_auction_header_id,'','','','');--for eric test only
1906
1907 if p_upd_history_type = 'USER' then
1908 --INSERT INTO ERIC_LOG VALUES ('ENTER USER Branch','','','','','','',''); --for eric test only
1909 if (p_status <> 'SUBMIT') then
1910 Update pon_neg_team_members
1911 set approval_status = decode(p_status,'APPROVE','APPROVED','REJECT','REJECTED',p_status)
1912 where auction_header_id = p_auction_header_id
1913 and user_id = p_user_id
1914 AND approver_flag = 'Y' -- for eric test only
1915 AND MENU_NAME<>'EMD_ADMIN'; -- for eric test only
1916
1917 i:=SQL%ROWCOUNT ;-- for eric test only
1918 --INSERT INTO ERIC_LOG VALUES (i ||'row(s) has been updated in pon_neg_team_members','','','','','','',''); --for eric test only
1919 end if;
1920
1921 insert into pon_action_history
1922 (object_id,
1923 object_id2,
1924 object_type_code,
1925 sequence_num,
1926 action_type,
1927 action_date,
1928 action_user_id,
1929 action_note,
1930 signed_date) --Added by amundhra for Federal CLM, inserting into the action_history table
1931 values (
1932 p_auction_header_id,
1933 p_auction_header_id,
1934 'NEGOTIATION',
1935 0,
1936 decode(p_status,'APPROVED','APPROVE','REJECTED','REJECT',p_status),
1937 sysdate,
1941 );
1938 p_user_id,
1939 p_notes,
1940 p_signed_date --added by amundhra for Federal CLM, digned date needs to be stored in action history
1942 --INSERT INTO ERIC_LOG VALUES ('go out USER Branch','','','','','','',''); --for eric test only
1943 --FOR ERIC TEST ONLY ,BEGIN
1944 ----------------------------------------------------------------------------------------------
1945 ELSIF p_upd_history_type = 'EMD'
1946 THEN
1947 --INSERT INTO ERIC_LOG VALUES ('enter EMD Branch','','','','','','',''); --for eric test only
1948
1949
1950 IF (p_status <> 'SUBMIT')
1951 THEN
1952 UPDATE pon_neg_team_members
1953 SET approval_status = decode(p_status
1954 ,'APPROVE'
1955 ,'APPROVED'
1956 ,'REJECT'
1957 ,'REJECTED'
1958 ,p_status
1959 )
1960 WHERE auction_header_id = p_auction_header_id
1961 AND user_id = p_user_id
1962 AND MENU_NAME='EMD_ADMIN'
1963 AND approver_flag = 'Y';
1964
1965 i:=SQL%ROWCOUNT ;-- for eric test only
1966 --INSERT INTO ERIC_LOG VALUES (i ||'row(s) has been updated in pon_neg_team_members','','','','','','',''); --for eric test only
1967 END IF;
1968
1969 INSERT INTO pon_action_history
1970 (object_id
1971 ,object_id2
1972 ,object_type_code
1973 ,sequence_num
1974 ,action_type
1975 ,action_date
1976 ,action_user_id
1977 ,action_note,
1978 signed_date)
1979 VALUES
1980 (p_auction_header_id
1981 ,p_auction_header_id
1982 ,'NEGOTIATION-EMD'
1983 ,0
1984 ,decode(p_status
1985 ,'APPROVED'
1986 ,'APPROVE'
1987 ,'REJECTED'
1988 ,'REJECT'
1989 ,p_status
1990 )
1991 ,SYSDATE
1992 ,p_user_id
1993 ,p_notes
1994 ,p_signed_date); -- added signed date , will be null here
1995
1996 --INSERT INTO ERIC_LOG VALUES ('go out EMD Branch','','','','','','',''); --for eric test only
1997 ----------------------------------------------------------------------------
1998 --FOR ERIC TEST ONLY ,END
1999
2000 elsif p_upd_history_type = 'AUCTION' then
2001 --INSERT INTO ERIC_LOG VALUES ('ENTER AUCTION Branch','p_status= '||p_status,'p_auction_header_id = '||p_auction_header_id,'','','','',''); --for eric test only
2002 /*
2003 * In case the document has been APPROVED, REJECTED, TIMEOUT
2004 * then we unlock the negotiation. If we do not unlock the
2005 * neg the lock might remain with the approver in case he
2006 * navigated to the review page. Bug 4777895.
2007 */
2008
2009 update pon_auction_headers_all
2010 set approval_status = p_status,
2011 draft_locked = 'N',
2012 draft_locked_by = null,
2013 draft_locked_by_contact_id = null,
2014 draft_locked_date = null
2015 where auction_header_id = p_auction_header_id;
2016
2017 i:=SQL%ROWCOUNT;
2018 --INSERT INTO ERIC_LOG VALUES (i ||'row has been updated in pon_auction_headers_all','','','','','','',''); --for eric test only
2019 --INSERT INTO ERIC_LOG VALUES ('go out AUCTION Branch','','','','','','',''); --for eric test only
2020 end if;
2021
2022 end UPD_AUCTION_STATUSHISTORY;
2023
2024
2025 PROCEDURE SET_NOTIFICATION_SUBJECT(p_itemtype in varchar2,
2026 p_itemkey in varchar2,
2027 p_msg_suffix in varchar2,
2028 p_doc_number in varchar2,
2029 p_orig_document_number in varchar2,
2030 p_amendment_number in number,
2031 p_auction_title in varchar2) IS
2032 BEGIN
2033
2034
2035 if (p_amendment_number is not null and p_amendment_number > 0) then
2036 wf_engine.SetItemAttrText (itemtype => p_itemType,
2037 itemkey => p_itemKey,
2038 aname => 'REQUEST_FOR_APPROVALS_SUBJECT',
2039 avalue => PON_AUCTION_PKG.getMessage('PON_AMEND_APPR_REQ_SUBJECT',p_msg_suffix,'AMENDMENT_NUMBER', p_amendment_number, 'ORIG_NUMBER', p_orig_document_number, 'AUCTION_TITLE', p_auction_title));
2040
2041 wf_engine.SetItemAttrText (itemtype => p_itemType,
2042 itemkey => p_itemKey,
2043 aname => 'PON_AUC_APPR_REMINDER_SUB',
2044 avalue => PON_AUCTION_PKG.getMessage('PON_AMEND_APPR_REMINDER_SUB',p_msg_suffix,'AMENDMENT_NUMBER', p_amendment_number, 'ORIG_NUMBER', p_orig_document_number));
2045
2046 --added for eric test,begin
2047 wf_engine.SetItemAttrText (itemtype => p_itemType,
2048 itemkey => p_itemKey,
2049 aname => 'EMD_REQ_FOR_APPROVALS_SUBJECT',
2050 avalue => 'EMD '||PON_AUCTION_PKG.getMessage('PON_AMEND_APPR_REQ_SUBJECT',p_msg_suffix,'AMENDMENT_NUMBER', p_amendment_number, 'ORIG_NUMBER', p_orig_document_number, 'AUCTION_TITLE', p_auction_title));
2051
2052 wf_engine.SetItemAttrText (itemtype => p_itemType,
2053 itemkey => p_itemKey,
2054 aname => 'EMD_PON_AUC_APPR_REMINDER_SUB',
2055 avalue => 'EMD '||PON_AUCTION_PKG.getMessage('PON_AMEND_APPR_REMINDER_SUB',p_msg_suffix,'AMENDMENT_NUMBER', p_amendment_number, 'ORIG_NUMBER', p_orig_document_number));
2056
2057 --added for eric test,end
2058
2059
2060 wf_engine.SetItemAttrText (itemtype => p_itemType,
2061 itemkey => p_itemKey,
2065 wf_engine.SetItemAttrText (itemtype => p_itemType,
2062 aname => 'DOC_APPROVED_MAIL_SUBJECT',
2063 avalue => PON_AUCTION_PKG.getMessage('PON_AMEND_APPR_APPRD_SUBJECT',p_msg_suffix, 'AMENDMENT_NUMBER', p_amendment_number, 'ORIG_NUMBER',p_orig_document_number, 'AUCTION_TITLE', p_auction_title));
2064
2066 itemkey => p_itemKey,
2067 aname => 'DOC_REJECTED_MAIL_SUBJECT',
2068 avalue => PON_AUCTION_PKG.getMessage('PON_AMEND_APPR_REJ_SUBJECT', p_msg_suffix, 'AMENDMENT_NUMBER', p_amendment_number, 'ORIG_NUMBER',p_orig_document_number, 'AUCTION_TITLE', p_auction_title));
2069
2070 else
2071 wf_engine.SetItemAttrText (itemtype => p_itemType,
2072 itemkey => p_itemKey,
2073 aname => 'REQUEST_FOR_APPROVALS_SUBJECT',
2074 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_APPR_REQ_SUBJECT',p_msg_suffix,'DOC_NUMBER', p_doc_number, 'AUCTION_TITLE', p_auction_title));
2075
2076
2077 wf_engine.SetItemAttrText (itemtype => p_itemType,
2078 itemkey => p_itemKey,
2079 aname => 'PON_AUC_APPR_REMINDER_SUB',
2080 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_APPR_REMINDER_SUB',p_msg_suffix,'DOC_NUMBER', p_doc_number));
2081
2082
2083 --added for eric test,begin
2084 wf_engine.SetItemAttrText (itemtype => p_itemType,
2085 itemkey => p_itemKey,
2086 aname => 'EMD_REQ_FOR_APPROVALS_SUBJECT',
2087 avalue => 'EMD '||PON_AUCTION_PKG.getMessage('PON_AUC_APPR_REQ_SUBJECT',p_msg_suffix,'DOC_NUMBER', p_doc_number, 'AUCTION_TITLE', p_auction_title));
2088
2089 wf_engine.SetItemAttrText (itemtype => p_itemType,
2090 itemkey => p_itemKey,
2091 aname => 'EMD_PON_AUC_APPR_REMINDER_SUB',
2092 avalue => 'EMD '|| PON_AUCTION_PKG.getMessage('PON_AUC_APPR_REMINDER_SUB',p_msg_suffix,'DOC_NUMBER', p_doc_number));
2093
2094 --added for eric test,end
2095
2096
2097 wf_engine.SetItemAttrText (itemtype => p_itemType,
2098 itemkey => p_itemKey,
2099 aname => 'DOC_APPROVED_MAIL_SUBJECT',
2100 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_APPR_APPRD_SUBJECT',p_msg_suffix, 'DOC_NUMBER',p_doc_number, 'AUCTION_TITLE', p_auction_title));
2101
2102 wf_engine.SetItemAttrText (itemtype => p_itemType,
2103 itemkey => p_itemKey,
2104 aname => 'DOC_REJECTED_MAIL_SUBJECT',
2105 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_APPR_REJ_SUBJECT', p_msg_suffix,'DOC_NUMBER',p_doc_number, 'AUCTION_TITLE', p_auction_title));
2106 end if;
2107
2108 END SET_NOTIFICATION_SUBJECT;
2109
2110 --PROCEDURE StartEmdApprovalProcess is ADDED FOR ERIC TEST ONLY
2111 PROCEDURE StartEmdApprovalProcess
2112 ( itemtype IN VARCHAR2
2113 ,Itemkey IN VARCHAR2
2114 ,actid IN NUMBER
2115 ,uncmode IN VARCHAR2
2116 ,resultout OUT NOCOPY VARCHAR2
2117 )
2118 IS
2119 l_auction_header_id NUMBER;
2120 l_seq VARCHAR2(100);
2121 l_itemKey VARCHAR2(240);
2122 l_itemType VARCHAR(25) := 'PONAPPRV';
2123 l_creator_user_id NUMBER;
2124 l_creator_full_name VARCHAR2(240);
2125 l_creator_user_name VARCHAR2(100);
2126 l_creator_session_lang_code VARCHAR2(3);
2127 l_creator_time_zone VARCHAR2(80);
2128 l_doctype_group_name VARCHAR2(100);
2129 l_msg_suffix VARCHAR2(10);
2130 l_auction_contact_id NUMBER;
2131 l_language_code VARCHAR2(3);
2132 l_timezone VARCHAR2(100);
2133 l_timezone_disp VARCHAR2(100);
2134 l_oex_timezone VARCHAR2(100);
2135 l_open_date_in_tz DATE;
2136 l_close_date_in_tz DATE;
2137 l_timeout_factor NUMBER;
2138 l_subtab VARCHAR(80);
2139 l_note_to_approvers VARCHAR2(2000);
2140 l_publish_auction_now_flag VARCHAR2(1);
2141 l_open_auction_now_flag VARCHAR2(1);
2142 l_reminder_date DATE;
2143 l_preparer_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
2144 l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
2145 l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
2146 l_preparer_tp_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
2147 l_auction_start_date DATE;
2148 l_auction_end_date DATE;
2149 l_timezone_dsp VARCHAR2(100);
2150 l_review_emd_changes_url VARCHAR2(2000);
2151 l_review_changes_url VARCHAR2(2000);
2152 l_orig_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
2153 l_round_number NUMBER;
2154 l_amendment_number NUMBER;
2155 l_preview_date_in_tz DATE;
2156 l_timezone1_disp VARCHAR2(240);
2157 l_preview_date_nspec FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2158 l_redirect_func VARCHAR2(50);
2159
2160 CURSOR C_APPROVALS(p_auction_header_id NUMBER, p_timeout_factor NUMBER) IS
2161 SELECT
2162 u.user_name user_name
2163 , u.user_id
2164 , auc.close_bidding_date close_bidding_date
2165 , auc.auction_title
2166 , decode( nvl(auc.open_auction_now_flag,'N')
2167 ,'Y'
2168 ,to_date(NULL)
2172 , to_date(NULL)
2169 ,auc.open_bidding_date) open_bidding_date
2170 , decode( nvl(auc.publish_auction_now_flag,'N')
2171 , 'Y'
2173 , auc.view_by_date ) view_by_date
2174 , auc.document_number doc_number
2175 , trading_partner_contact_id auction_contact_id
2176 , nvl(auc.publish_auction_now_flag ,'N') publish_auction_now_flag
2177 , nvl(auc.open_auction_now_flag,'N') open_auction_now_flag
2178 , auc.auction_header_id_orig_amend
2179 FROM
2180 pon_neg_team_members neg
2181 , pon_auction_headers_all auc
2182 , fnd_user u
2183 WHERE neg.auction_header_id = auc.auction_header_id
2184 AND auc.auction_header_id = p_auction_header_id
2185 AND MENU_NAME='EMD_ADMIN'
2186 AND approver_flag = 'Y' --for eric test only
2187 AND u.user_id = neg.user_id;
2188 BEGIN
2189 --INSERT INTO ERIC_LOG VALUES ('INTO StartEmdApprovalProcess','','','','','','','');--for eric test only
2190 l_timeout_factor := .5; -- use to get this value from an item attribute
2191
2192 l_preparer_tp_name := wf_engine.GetItemAttrText( itemtype => itemType
2193 , itemkey => itemKey
2194 , aname => 'PREPARER_TP_NAME');
2195
2196 l_auction_title := wf_engine.GetItemAttrText( itemtype => itemType
2197 , itemkey => itemKey
2198 , aname => 'AUCTION_TITLE');
2199
2200 l_doc_number := wf_engine.GetItemAttrText( itemtype => itemType
2201 , itemkey => itemKey
2202 , aname => 'DOC_NUMBER');
2203
2204 l_preparer_tp_contact_name := wf_engine.GetItemAttrText( itemtype => itemType
2205 , itemkey => itemKey
2206 , aname => 'PREPARER_TP_CONTACT_NAME');
2207
2208 l_auction_start_date := wf_engine.GetItemAttrDate( itemtype => itemType
2209 , itemkey => itemKey
2210 , aname => 'AUCTION_START_DATE');
2211
2212 l_auction_end_date := wf_engine.GetItemAttrDate( itemtype => itemType
2213 , itemkey => itemKey
2214 , aname => 'AUCTION_END_DATE');
2215
2216 l_timezone_dsp := wf_engine.GetItemAttrText( itemtype => itemType
2217 , itemkey => itemKey
2218 , aname => 'TIMEZONE');
2219
2220 l_note_to_approvers := wf_engine.GetItemAttrText( itemtype => itemType
2221 , itemkey => itemKey
2222 , aname => 'NOTE_TO_APPROVERS');
2223
2224 l_preview_date_in_tz := wf_engine.GetItemAttrDate( itemtype => itemType
2225 , itemkey => itemKey
2226 , aname => 'PREVIEW_DATE');
2227 l_timezone1_disp := wf_engine.GetItemAttrText( itemtype => itemType
2228 , itemkey => itemKey
2229 , aname => 'TP_TIME_ZONE1');
2230
2231 l_preview_date_nspec := wf_engine.GetItemAttrText( itemtype => itemType
2232 , itemkey => itemKey
2233 , aname => 'PREVIEW_DATE_NOTSPECIFIED');
2234 l_msg_suffix := wf_engine.GetItemAttrText( itemtype => itemType
2235 , itemkey => itemKey
2236 , aname => 'MSG_SUFFIX');
2237
2238 l_auction_header_id := wf_engine.GetItemAttrNumber( itemtype => itemType
2239 , itemkey => itemKey
2240 , aname => 'AUCTION_HEADER_ID');
2241
2242 l_round_number := wf_engine.GetItemAttrNumber( itemtype => itemType
2243 , itemkey => itemKey
2244 , aname => 'DOC_ROUND_NUMBER');
2245
2246 l_amendment_number := wf_engine.GetItemAttrNumber( itemtype => itemType
2247 , itemkey => itemKey
2248 , aname => 'DOC_AMENDMENT_NUMBER');
2249
2250 l_creator_full_name := wf_engine.GetItemAttrText( itemtype => itemType
2251 , itemkey => itemKey
2252 , aname => 'AUCTIONEER_NAME');
2253
2254 l_redirect_func := wf_engine.GetItemAttrText( itemtype => itemType
2255 , itemkey => itemKey
2256 , aname => 'REVIEWPG_REDIRECTFUNC');
2257
2258 /* Preserve creator's session language */
2259 l_creator_user_name := wf_engine.GetItemAttrText( itemtype => itemType
2260 , itemkey => itemKey
2261 , aname => 'CREATOR_USER_NAME');
2262
2263 l_creator_user_id := wf_engine.GetItemAttrNumber( itemtype => itemType
2264 , itemkey => itemKey
2265 , aname => 'CREATOR_USER_ID');
2266
2267 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE( l_creator_user_id
2268 , l_creator_session_lang_code);
2272 --INSERT INTO ERIC_LOG VALUES ('EMD INTO FOR r1 IN C_APPROVALS','','','','','','','');--for eric test only
2269
2270 FOR r1 IN C_APPROVALS(l_auction_header_id,l_timeout_factor)
2271 LOOP
2273
2274 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)
2275 THEN
2276 --{
2277 FND_LOG.STRING( log_level => FND_LOG.level_statement
2278 , module => g_module_prefix ||'StartUserApprovalProcess'
2279 , message => 'r1.user_id : ' || r1.user_id);
2280 END IF; --}
2281
2282 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(r1.user_id,l_language_code);
2283
2284 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)
2285 THEN
2286 --{
2287 FND_LOG.STRING( log_level => FND_LOG.level_statement
2288 , module => g_module_prefix ||'StartEMDApprovalProcess'
2289 , message => 'r1.user_id : ' || r1.user_id || ';' ||'l_language_code : ' || l_language_code
2290 );
2291 END IF; --}
2292
2293 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)
2294 THEN
2295 --{
2296 FND_LOG.STRING( log_level => FND_LOG.level_statement
2297 , module => g_module_prefix ||'StartEMDApprovalProcess'
2298 , message => '1. Calling SET_SESSION_LANGUAGE with l_language_code : ' ||l_language_code
2299 );
2300 END IF; --}
2301
2302 -- bug 7602688 session language for workflow is already set in submit_for_approval procedure.
2303 -- this process is called from 'sourcingapproval' workflow only. so no need to set again.
2304 --PON_AUCTION_PKG.SET_SESSION_LANGUAGE(NULL,l_language_code);
2305
2306 --Bug 6472383 : If the Negotiation Preview date is mentioned as 'Not Specified', i.e. if the value of
2307 -- l_preview_date_nspec is not null, we need to replace it with the string specific to recipient's language
2308 IF (l_preview_date_nspec IS NOT NULL)
2309 THEN
2310 l_preview_date_nspec := PON_AUCTION_PKG.getMessage('PON_AUC_PREVIEW_DATE_NOTSPEC',l_msg_suffix);
2311 END IF;
2312
2313 l_itemKey := itemkey || '_EMD_' || r1.user_id;
2314
2315 wf_engine.createProcess( itemType => l_itemType
2316 , itemKey => l_itemKey
2317 , process => 'EMDAPPROVALS');
2318
2319 IF (r1.view_by_date IS NOT NULL)
2320 THEN
2321 l_reminder_date := r1.view_by_date;
2322 ELSIF (r1.open_bidding_date IS NOT NULL)
2323 THEN
2324 l_reminder_date := r1.open_bidding_date;
2325 ELSE
2326 l_reminder_date := r1.close_bidding_date;
2327 END IF;
2328
2329 SELECT SYSDATE + ((l_reminder_date - SYSDATE) * l_timeout_factor)
2330 INTO l_reminder_date
2331 FROM dual;
2332
2333
2334 wf_engine.SetItemAttrDate( itemtype => l_itemType
2335 , itemkey => l_itemKey
2336 , aname => 'TIMEOUT_USERPROCESS'
2337 , avalue => l_reminder_date);
2338 wf_engine.SetItemAttrText( itemtype => l_itemType
2339 , itemkey => l_itemKey
2340 , aname => 'CREATOR_USER_NAME'
2341 , avalue => l_creator_user_name);
2342 wf_engine.SetItemAttrNumber( itemtype => l_itemType
2343 , itemkey => l_itemKey
2344 , aname => 'CREATOR_USER_ID'
2345 , avalue => l_creator_user_id);
2346 wf_engine.SetItemAttrText( itemtype => l_itemType
2347 , itemkey => l_itemKey
2348 , aname => 'REVIEWPG_REDIRECTFUNC'
2349 , avalue => l_redirect_func);
2350 wf_engine.SetItemAttrText( itemtype => l_itemType
2351 , itemkey => l_itemKey
2352 , aname => 'PREPARER_TP_NAME'
2353 , avalue => l_preparer_tp_name);
2354 wf_engine.SetItemAttrText( itemtype => l_itemType
2355 , itemkey => l_itemKey
2356 , aname => 'AUCTION_TITLE'
2357 , avalue => l_auction_title);
2358 wf_engine.SetItemAttrText( itemtype => l_itemType
2359 , itemkey => l_itemKey
2360 , aname => 'DOC_NUMBER'
2361 , avalue => l_doc_number);
2362 wf_engine.SetItemAttrText( itemtype => l_itemType
2363 , itemkey => l_itemKey
2364 , aname => 'PREPARER_TP_CONTACT_NAME'
2365 , avalue => l_preparer_tp_contact_name);
2366 wf_engine.SetItemAttrDate( itemtype => l_itemType
2367 , itemkey => l_itemKey
2368 , aname => 'AUCTION_START_DATE'
2369 , avalue => l_auction_start_date);
2370 wf_engine.SetItemAttrDate( itemtype => l_itemType
2371 , itemkey => l_itemKey
2372 , aname => 'AUCTION_END_DATE'
2373 , avalue => l_auction_end_date);
2374 wf_engine.SetItemAttrText( itemtype => l_itemType
2375 , itemkey => l_itemKey
2376 , aname => 'TIMEZONE'
2377 , avalue => l_timezone_dsp);
2378 wf_engine.SetItemAttrDate( itemtype => l_itemType
2379 , itemkey => l_itemKey
2380 , aname => 'PREVIEW_DATE'
2384 , aname => 'TP_TIME_ZONE1'
2381 , avalue => l_preview_date_in_tz);
2382 wf_engine.SetItemAttrText( itemtype => l_itemType
2383 , itemkey => l_itemKey
2385 , avalue => l_timezone1_disp);
2386 wf_engine.SetItemAttrText( itemtype => l_itemType
2387 , itemkey => l_itemKey
2388 , aname => 'PREVIEW_DATE_NOTSPECIFIED'
2389 , avalue => l_preview_date_nspec);
2390 wf_engine.SetItemAttrText( itemtype => l_itemType
2391 , itemkey => l_itemKey
2392 , aname => 'NOTE_TO_APPROVERS'
2393 , avalue => l_note_to_approvers);
2394 wf_engine.SetItemAttrText( itemtype => l_itemType
2395 , itemkey => l_itemKey
2396 , aname => 'TOP_PROCESS_ITEM_KEY'
2397 , avalue => wf_engine.GetItemAttrText( itemtype => l_itemType
2398 , itemkey => itemKey
2399 , aname => 'TOP_PROCESS_ITEM_KEY'
2400 )
2401 );
2402 wf_engine.SetItemAttrNumber( itemtype => l_itemType
2403 , itemkey => l_itemKey
2404 , aname => 'AUCTION_HEADER_ID'
2405 , avalue => l_auction_header_id);
2406 /*
2407 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
2408 itemkey => l_itemKey,
2409 aname => 'DOC_ROUND_NUMBER',
2410 avalue => l_round_number);
2411
2412 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
2413 itemkey => l_itemKey,
2414 aname => 'DOC_AMENDMENT_NUMBER',
2415 avalue => l_amendment_number);
2416 */
2417
2418 l_review_emd_changes_url := Get_Emd_Update_Url (l_auction_header_id ); --FOR ERIC TEST ONLY
2419
2420 /*pon_wf_utl_pkg.get_dest_page_url (
2421 p_dest_func => 'PON_NEG_CRT_HEADER'
2422 ,p_notif_performer => 'BUYER');*/
2423
2424
2425 wf_engine.SetItemAttrText( itemtype => l_itemType
2426 , itemkey => l_itemKey
2427 , aname => 'REVIEW_EMD_CHANGES_URL'
2428 , avalue => l_review_emd_changes_url);
2429
2430
2431 l_review_changes_url := pon_wf_utl_pkg.get_dest_page_url (
2432 p_dest_func => 'PON_NEG_CRT_HEADER'
2433 ,p_notif_performer => 'BUYER');
2434
2435 wf_engine.SetItemAttrText (itemtype => l_itemType,
2436 itemkey => l_itemKey,
2437 aname => 'REVIEW_CHANGES_URL',
2438 avalue => l_review_changes_url);
2439
2440 wf_engine.SetItemAttrText( itemtype => l_itemType
2441 , itemkey => l_itemKey
2442 , aname => 'EMD_APPROVER'
2443 , avalue => r1.user_name);
2444
2445 wf_engine.SetItemAttrNumber( itemtype => l_itemType
2446 , itemkey => l_itemKey
2447 , aname => 'EMD_APPROVER_ID'
2448 , avalue => r1.user_id);
2449
2450 wf_engine.SetItemAttrText( itemtype => l_itemType
2451 , itemkey => l_itemKey
2452 , aname => 'ORIGIN_USER_NAME'
2453 , avalue => fnd_global.user_name);
2454
2455 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(r1.user_name);
2456
2457 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
2458
2459 IF (l_timezone IS NULL)
2460 THEN
2461 l_timezone := l_oex_timezone;
2462 END IF;
2463
2464 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone,l_language_code);
2465
2466 wf_engine.SetItemAttrText( itemtype => l_itemType
2467 , itemkey => l_itemKey
2468 , aname => 'TIMEZONE'
2469 , avalue => l_timezone_disp);
2470
2471 l_open_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME( r1.open_bidding_date
2472 , l_oex_timezone
2473 , l_timezone);
2474 l_close_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME( r1.close_bidding_date
2475 , l_oex_timezone
2476 , l_timezone);
2477
2478 wf_engine.SetItemAttrDate(itemtype => l_itemType
2479 ,itemkey => l_itemKey
2480 ,aname => 'AUCTION_START_DATE'
2481 ,avalue => l_open_date_in_tz);
2482 IF (r1.open_auction_now_flag = 'Y')
2483 THEN
2484 wf_engine.SetItemAttrText( itemtype => l_itemType
2485 , itemkey => l_itemKey
2486 , aname => 'OPEN_IMMEDIATELY'
2487 , avalue => PON_AUCTION_PKG.getMessage('PON_AUC_OPEN_IMM_AFTER_PUB'
2491 , itemkey => l_itemKey
2488 ,l_msg_suffix));
2489
2490 wf_engine.SetItemAttrText( itemtype => l_itemType
2492 , aname => 'O_TIMEZONE'
2493 , avalue => NULL);
2494 ELSE
2495 wf_engine.SetItemAttrText( itemtype => l_itemType
2496 , itemkey => l_itemKey
2497 , aname => 'OPEN_IMMEDIATELY'
2498 , avalue => NULL);
2499
2500 wf_engine.SetItemAttrText( itemtype => l_itemType
2501 , itemkey => l_itemKey
2502 , aname => 'O_TIMEZONE'
2503 , avalue => l_timezone_disp);
2504 END IF;
2505
2506 wf_engine.SetItemAttrDate( itemtype => l_itemType
2507 , itemkey => l_itemKey
2508 , aname => 'AUCTION_END_DATE'
2509 , avalue => l_close_date_in_tz);
2510
2511 wf_engine.SetItemAttrText( itemtype => l_itemType
2512 , itemkey => l_itemKey
2513 , aname => 'NOTE_TO_APPROVERS'
2514 , avalue => l_note_to_approvers);
2515
2516 SELECT document_number
2517 INTO l_orig_document_number
2518 FROM pon_auction_headers_all
2519 WHERE auction_header_id = r1.auction_header_id_orig_amend;
2520
2521 -- set notification subjects
2522 set_notification_subject( l_itemType
2523 , l_itemKey
2524 , l_msg_suffix
2525 , l_doc_number
2526 , l_orig_document_number
2527 , l_amendment_number
2528 , l_auction_title);
2529
2530 -- Bug 4295915: Set the workflow owner
2531 wf_engine.SetItemOwner( itemtype => l_itemtype
2532 , itemkey => l_itemkey
2533 , owner => fnd_global.user_name);
2534
2535 wf_engine.StartProcess( itemType => l_itemType
2536 , itemKey => l_itemKey);
2537
2538 --dbms_output.put_line('EMDAPPROVALS: Started: '||l_itemType ||l_itemKey);-- for eric test only
2539 END LOOP;
2540
2541 /* Reset to creator's language */
2542 -- 7602688
2543 --PON_AUCTION_PKG.SET_SESSION_LANGUAGE(NULL,l_creator_session_lang_code);
2544 END StartEmdApprovalProcess;
2545
2546 --PROCEDURE Emd_User_Approved is ADDED FOR ERIC TEST ONLY
2547 PROCEDURE Emd_User_Approved
2548 ( itemtype IN VARCHAR2
2549 ,itemkey IN VARCHAR2
2550 ,actid IN NUMBER
2551 ,uncmode IN VARCHAR2
2552 ,resultout OUT NOCOPY VARCHAR2
2553 )
2554 IS
2555 ls_error VARCHAR2(4000); -- for eric test only
2556 l_notes VARCHAR2(2000);
2557 l_user_name VARCHAR2(100);
2558 l_user_id NUMBER;
2559 l_auction_header_id NUMBER;
2560 l_top_process_item_key VARCHAR2(240);
2561 l_result VARCHAR2(30) := 'APPROVED';
2562 lv_wf_source VARCHAR2(10) := 'EMD'; --FOR ERIC TEST ONLY
2563 BEGIN
2564 --INSERT INTO ERIC_LOG VALUES ('INTO Emd_User_Approved','','','','','','','');--for eric test only
2565 /* Get auction header id from the workflow */
2566 l_auction_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype
2567 , itemkey => itemkey
2568 , aname => 'AUCTION_HEADER_ID');
2569
2570 l_user_name := wf_engine.GetItemAttrText( itemtype => itemtype
2571 , itemkey => itemkey
2572 , aname => 'EMD_APPROVER');
2573
2574 l_user_id := wf_engine.GetItemAttrNumber( itemtype => itemtype
2575 , itemkey => itemkey
2576 , aname => 'EMD_APPROVER_ID');
2577
2578 l_notes := wf_engine.GetItemAttrText( itemtype => itemtype
2579 , itemkey => itemkey
2580 , aname => 'APPROVER_NOTES');
2581
2582 l_top_process_item_key := wf_engine.GetItemAttrText( itemtype => itemType
2583 , itemkey => itemKey
2584 , aname => 'TOP_PROCESS_ITEM_KEY');
2585
2586 wf_engine.SetItemAttrNumber( itemtype => itemtype
2587 , itemkey => l_top_process_item_key
2588 , aname => 'EMD_APPROVER_ID'
2589 , avalue => l_user_id
2590 );
2591
2592 wf_engine.SetItemAttrText( itemtype => itemtype
2593 , itemkey => l_top_process_item_key
2594 , aname => 'EMD_APPROVER'
2595 , avalue => l_user_name
2596 );
2597
2598 --INSERT INTO ERIC_LOG VALUES ('BEFORE Is_Valid_Response',l_auction_header_id,l_user_id,lv_wf_source,'','','','');--for eric test only
2599 /* Check is the responder a valid approver */
2600 --IF (Is_Valid_Response(l_auction_header_id,l_user_id) = 'N')
2601 IF (Is_Valid_Response(l_auction_header_id,l_user_id,lv_wf_source) = 'N')
2602 THEN
2603 /* Responder is not a valid approver. Ignore this response */
2604 --INSERT INTO ERIC_LOG VALUES ('INTO Is_Valid_Response','','','','','','','');--for eric test only
2605 RETURN;
2606 END IF;
2607
2608 --INSERT INTO ERIC_LOG VALUES ('BEFORE UPD_AUCTION_STATUSHISTORY',l_auction_header_id,l_result,l_notes,l_user_id,'','','');--for eric test only
2609 /* Insert a row into history table */
2610 UPD_AUCTION_STATUSHISTORY(l_auction_header_id
2611 ,l_result
2612 ,l_notes
2616
2613 ,l_user_id
2614 ,'EMD'
2615 ,NULL); -- Adding by amundhra for Federal CLM, signed date will be null here
2617 --INSERT INTO ERIC_LOG VALUES ('BEFORE Process_If_Doc_Approved',l_auction_header_id,l_top_process_item_key,'','','','','');--for eric test only
2618 Process_If_Doc_Approved(l_auction_header_id,l_top_process_item_key);
2619
2620 --INSERT INTO ERIC_LOG VALUES ('GO OUT Emd_User_Approved','','','','','','','');--for eric test only
2621 EXCEPTION
2622 WHEN OTHERS
2623 THEN
2624 ls_error := sqlerrm ;
2625 --INSERT INTO ERIC_LOG VALUES ('Function EMD_User_Approved Exception: ',ls_error,'','','','','','');--for eric test only
2626 END EMD_User_Approved;
2627
2628 --PROCEDURE Emd_User_Rejected is ADDED FOR ERIC TEST ONLY
2629 PROCEDURE Emd_User_Rejected
2630 ( itemtype IN VARCHAR2
2631 , itemkey IN VARCHAR2
2632 , actid IN NUMBER
2633 , uncmode IN VARCHAR2
2634 , resultout OUT NOCOPY VARCHAR2
2635 )
2636 IS
2637 l_notes VARCHAR2(2000);
2638 l_user_name VARCHAR2(100);
2639 l_user_id NUMBER;
2640 l_auction_header_id NUMBER;
2641 l_top_process_item_key VARCHAR2(240);
2642 l_result VARCHAR2(30) := 'REJECTED';
2643 lv_wf_source VARCHAR2(10) := 'EMD'; --FOR ERIC TEST ONLY
2644 BEGIN
2645
2646 /* Get auction header id from the workflow */
2647 l_auction_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype
2648 , itemkey => itemkey
2649 , aname => 'AUCTION_HEADER_ID'
2650 );
2651
2652 l_user_name := wf_engine.GetItemAttrText( itemtype => itemtype
2653 , itemkey => itemkey
2654 , aname => 'EMD_APPROVER'
2655 );
2656
2657 l_user_id := wf_engine.GetItemAttrNumber( itemtype => itemtype
2658 , itemkey => itemkey
2659 , aname => 'EMD_APPROVER_ID'
2660 );
2661
2662 l_notes := wf_engine.GetItemAttrText( itemtype => itemtype
2663 , itemkey => itemkey
2664 , aname => 'APPROVER_NOTES');
2665
2666 l_top_process_item_key := wf_engine.GetItemAttrText( itemtype => itemType
2667 , itemkey => itemKey
2668 , aname => 'TOP_PROCESS_ITEM_KEY'
2669 );
2670
2671 wf_engine.SetItemAttrText( itemtype => itemtype
2672 , itemkey => l_top_process_item_key
2673 , aname => 'EMD_APPROVER'
2674 , avalue => l_user_name
2675 );
2676
2677 wf_engine.SetItemAttrNumber( itemtype => itemtype
2678 , itemkey => l_top_process_item_key
2679 , aname => 'EMD_APPROVER_ID'
2680 , avalue => l_user_id
2681 );
2682
2683 /* Check is the responder a valid approver */
2684 --IF (Is_Valid_Response(l_auction_header_id,l_user_id) = 'N')
2685
2686 IF (Is_Valid_Response(l_auction_header_id,l_user_id,lv_wf_source) = 'N')
2687 THEN
2688 /* Responder is not a valid approver. Ignore this response */
2689 RETURN;
2690 END IF;
2691
2692 wf_engine.SetItemAttrText( itemtype => itemtype
2693 , itemkey => l_top_process_item_key
2694 , aname => 'NOTE_TO_BUYER_ON_REJECT'
2695 , avalue => l_notes);
2696
2697 /*Update PON_NEG_TEAM_MEMEBERS APPROVAL_STATUS field */
2698 /* Insert a row into history table */
2699 UPD_AUCTION_STATUSHISTORY( l_auction_header_id
2700 , l_result
2701 , l_notes
2702 , l_user_id
2703 , 'EMD'
2704 ,NULL); -- Adding by amundhra for Federal CLM, signed date will be null here
2705
2706 Process_Doc_Rejected(l_auction_header_id,l_top_process_item_key);
2707
2708 END Emd_User_Rejected;
2709
2710 /* Added for Federal CLM Solicitaion Amendments Project.
2711 * We cannot commit in a procedure called by workflow process.
2712 * This is a wrapper procedure that is autonomous, so that workflow process
2713 * standards are not violated.
2714 */
2715 /*PROCEDURE CALL_CNF_AMENDMENT_CLM(p_source_auction_header_id IN NUMBER,
2716 x_auction_header_id OUT NUMBER,
2717 x_document_number OUT VARCHAR2,
2718 x_request_id OUT NUMBER,
2719 x_return_status OUT VARCHAR2,
2720 x_msg_count OUT NUMBER,
2721 x_msg_data OUT VARCHAR2)
2722 AS PRAGMA AUTONOMOUS_TRANSACTION;
2723 Begin
2724 --print_debug_messages('Start of procedure CALL_CNF_AMENDMENT_CLM p_source_auction_header_id : '||p_source_auction_header_id);
2725 PON_NEGOTIATION_COPY_GRP.CONFORM_AMENDMENT(
2726 p_source_auction_header_id,
2727 x_auction_header_id,
2728 x_document_number,
2729 x_request_id,
2730 x_return_status,
2731 x_msg_count,
2732 x_msg_data);
2733 commit;
2734 --print_debug_messages('End of procedure CALL_CNF_AMENDMENT_CLM : x_return_status : '||x_return_status);
2735 End CALL_CNF_AMENDMENT_CLM;*/
2736
2740 PROCEDURE Create_Cnf_Doc_CLM(itemtype in varchar2,
2737 /* Added for Federal CLM Solicitaion Amendments Project.
2738 * Will be called from Souring Approval Workflow in case of federal document.
2739 */
2741 itemkey in varchar2,
2742 actid in number,
2743 uncmode in varchar2,
2744 resultout out nocopy varchar2) IS
2745
2746 l_source_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
2747 l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
2748 l_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
2749 l_request_id NUMBER;
2750 l_return_status VARCHAR2(30);
2751 l_msg_count NUMBER;
2752 l_msg_data VARCHAR2(500);
2753
2754 l_revision PON_AUCTION_HEADERS_ALL.REVISION%TYPE;
2755 l_msg_suffix VARCHAR2(10);
2756 l_rev_message VARCHAR2(30);
2757 l_success_msg VARCHAR2(1000);
2758
2759 l_unexp_error VARCHAR2(1) := 'N';
2760
2761 Begin
2762 l_source_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemType,
2763 itemkey => itemKey,
2764 aname => 'AUCTION_HEADER_ID');
2765
2766 --print_debug_messages('Start of procedure Create_Cnf_Doc_CLM l_source_auction_header_id : '||l_source_auction_header_id);
2767 pon_negotiation_copy_grp.g_from_workflow := 'Y';
2768 PON_NEGOTIATION_COPY_GRP.CONFORM_AMENDMENT(l_source_auction_header_id,
2769 l_auction_header_id,
2770 l_document_number,
2771 l_request_id,
2772 l_return_status,
2773 l_msg_count,
2774 l_msg_data,
2775 l_revision);
2776
2777 pon_negotiation_copy_grp.g_from_workflow := 'N';
2778 --print_debug_messages('After procedure CALL_CNF_AMENDMENT_CLM l_auction_header_id : '||l_auction_header_id);
2779 --print_debug_messages('l_return_status : '||l_return_status);
2780
2781 --Added for testing, need to be removed
2782 /* l_return_status := 'E';
2783 l_msg_count := 3;
2784 FND_MSG_PUB.INITIALIZE;
2785 FND_MESSAGE.CLEAR;
2786 FND_MESSAGE.SET_NAME('PON','PON_INVALID_TP_ID');
2787 FND_MSG_PUB.ADD;
2788 FND_MESSAGE.SET_NAME('PON','PON_INV_COPY_OPTION');
2789 FND_MSG_PUB.ADD;
2790 FND_MESSAGE.SET_NAME('PON','PON_MULTI_NEWRND_OR_AMND_ERR');
2791 FND_MSG_PUB.ADD;
2792 FND_MESSAGE.SET_NAME('PON','PON_CRT_CANCEL_AMEND_CLM');
2793 FND_MESSAGE.SET_TOKEN('DOC_GROUP','Amendment');
2794 FND_MESSAGE.SET_TOKEN('NUMBER','11111');
2795 FND_MSG_PUB.ADD;*/
2796
2797 wf_engine.SetItemAttrText (itemtype => itemType,
2798 itemkey => itemKey,
2799 aname => 'IS_COPY_SUCCESS_FED',
2800 avalue => l_return_status);
2801 -- If conc. request is submitted to create conformed document.
2802 if l_return_status = 'CONC_REQ_SUBMITTED' then
2803
2804 wf_engine.SetItemAttrText (itemtype => itemType,
2805 itemkey => itemKey,
2806 aname => 'IS_CONC_REQUEST',
2807 avalue => 'Y');
2808 wf_engine.SetItemAttrNumber (itemtype => itemType,
2809 itemkey => itemKey,
2810 aname => 'CONC_REQUEST_ID_FED',
2811 avalue => l_request_id);
2812 wf_engine.SetItemAttrNumber(itemtype => itemType,
2813 itemkey => itemKey,
2814 aname => 'CONF_DOC_AUC_HEAD_ID',
2815 avalue => l_auction_header_id);
2816 wf_engine.SetItemAttrNumber(itemtype => itemType,
2817 itemkey => itemKey,
2818 aname => 'CONF_DOC_REVISION',
2819 avalue => l_revision);
2820 wf_engine.SetItemAttrText(itemtype => itemType,
2821 itemkey => itemKey,
2822 aname => 'CONF_DOC_NUMBER',
2823 avalue => l_document_number);
2824
2825 l_msg_suffix := wf_engine.GetItemAttrText(itemtype => itemType,
2826 itemkey => itemKey,
2827 aname => 'MSG_SUFFIX');
2828 fnd_message.set_name('PON', 'PON_REVISION_CLM');
2829 fnd_message.set_token('REVISION',to_char(l_revision));
2830 l_rev_message := fnd_message.get;
2831 fnd_message.set_name('PON','PON_AUCTION_AUCTION'||l_msg_suffix);
2832 l_success_msg := fnd_message.get||' '||l_document_number||l_rev_message;
2833 wf_engine.SetItemAttrText(itemtype => itemType,
2834 itemkey => itemKey,
2835 aname => 'NEW_CONF_DOC_CLM',
2836 avalue => l_success_msg);
2837 else -- If normal copy
2838
2839 -- If conformed doc copy returned success
2840 if l_return_status = 'S' then
2841
2842 wf_engine.SetItemAttrNumber(itemtype => itemType,
2843 itemkey => itemKey,
2844 aname => 'CONF_DOC_AUC_HEAD_ID',
2845 avalue => l_auction_header_id);
2846 wf_engine.SetItemAttrNumber(itemtype => itemType,
2847 itemkey => itemKey,
2848 aname => 'CONF_DOC_REVISION',
2849 avalue => l_revision);
2850 wf_engine.SetItemAttrText(itemtype => itemType,
2851 itemkey => itemKey,
2852 aname => 'CONF_DOC_NUMBER',
2853 avalue => l_document_number);
2854
2855 l_msg_suffix := wf_engine.GetItemAttrText(itemtype => itemType,
2856 itemkey => itemKey,
2857 aname => 'MSG_SUFFIX');
2858
2859 fnd_message.set_name('PON', 'PON_REVISION_CLM');
2860 fnd_message.set_token('REVISION',to_char(l_revision));
2861 l_rev_message := fnd_message.get;
2862 fnd_message.set_name('PON','PON_AUCTION_AUCTION'||l_msg_suffix);
2863 l_success_msg := fnd_message.get||' '||l_document_number||l_rev_message;
2864 wf_engine.SetItemAttrText(itemtype => itemType,
2868 else
2865 itemkey => itemKey,
2866 aname => 'NEW_CONF_DOC_CLM',
2867 avalue => l_success_msg);
2869 -- If conformed doc copy failed with error
2870 if l_return_status = 'E' then
2871 wf_engine.SetItemAttrText(itemtype => itemType,
2872 itemkey => itemKey,
2873 aname => 'CONF_DOC_ERROR_MESSAGE',
2874 avalue => PON_CLM_UTIL_PKG.GET_COPY_ERR_MSGS(l_msg_count,l_unexp_error));
2875 wf_engine.SetItemAttrText(itemtype => itemType,
2876 itemkey => itemKey,
2877 aname => 'CONF_DOC_IS_UNEXP_ERROR',
2878 avalue => l_unexp_error);
2879 else -- If conformed doc copy done with warnings.
2880 null;
2881 end if; -- end l_return_status = 'E'
2882 end if; -- end l_return_status = 'S'
2883 end if; --end l_return_status = 'CONC_REQ_SUBMITTED'
2884
2885 End Create_Cnf_Doc_CLM;
2886
2887 /* Added for Federal CLM Solicitaion Amendments Project.
2888 * Will be called from approval page when approval is done online.
2889 * Gets the conformed document number, auction header id, revision from workflow.
2890 */
2891 PROCEDURE get_cnf_doc_params_wf(p_auction_header_id IN NUMBER,
2892 x_return_status OUT NOCOPY VARCHAR2,
2893 x_cnf_doc_auc_head_id OUT NOCOPY NUMBER,
2894 x_cnf_doc_number OUT NOCOPY VARCHAR2,
2895 x_cnf_doc_rev OUT NOCOPY NUMBER,
2896 x_request_id OUT NOCOPY NUMBER,
2897 x_message OUT NOCOPY VARCHAR2,
2898 x_amend_doc_number OUT NOCOPY VARCHAR2,
2899 x_error_messages OUT NOCOPY VARCHAR2,
2900 x_is_unexp_error OUT NOCOPY VARCHAR2) IS
2901 l_itemKey varchar2(240);
2902 l_itemType varchar2(25):= 'PONAPPRV';
2903 Begin
2904 --Get work flow item key
2905 select wf_approval_item_key
2906 into l_itemKey
2907 from pon_auction_headers_all
2908 where auction_header_id = p_auction_header_id;
2909 --Get the attributes from workflow
2910 x_return_status := wf_engine.GetItemAttrText (itemtype => l_itemType,
2911 itemkey => l_itemKey,
2912 aname => 'IS_COPY_SUCCESS_FED');
2913
2914 x_cnf_doc_auc_head_id := wf_engine.GetItemAttrNumber(itemtype => l_itemType,
2915 itemkey => l_itemKey,
2916 aname => 'CONF_DOC_AUC_HEAD_ID');
2917 x_cnf_doc_number := wf_engine.GetItemAttrText(itemtype => l_itemType,
2918 itemkey => l_itemKey,
2919 aname => 'CONF_DOC_NUMBER');
2920 x_cnf_doc_rev := wf_engine.GetItemAttrNumber(itemtype => l_itemType,
2921 itemkey => l_itemKey,
2922 aname => 'CONF_DOC_REVISION');
2923
2924 x_message := wf_engine.GetItemAttrText(itemtype => l_itemType,
2925 itemkey => l_itemKey,
2926 aname => 'NEW_CONF_DOC_CLM');
2927 select document_number
2928 into x_amend_doc_number
2929 from pon_auction_headers_all
2930 where
2931 auction_header_id = p_auction_header_id;
2932
2933 if x_return_status = 'CONC_REQ_SUBMITTED' then
2934 x_request_id := wf_engine.GetItemAttrNumber (itemtype => l_itemType,
2935 itemkey => l_itemKey,
2936 aname => 'CONC_REQUEST_ID_FED');
2937 else
2938 if x_return_status = 'E' then
2939 x_error_messages := wf_engine.GetItemAttrText(itemtype => l_itemType,
2940 itemkey => l_itemKey,
2941 aname => 'CONF_DOC_ERROR_MESSAGE');
2942 x_is_unexp_error := wf_engine.GetItemAttrText(itemtype => l_itemType,
2943 itemkey => l_itemKey,
2944 aname => 'CONF_DOC_IS_UNEXP_ERROR');
2945 else
2946 null;
2947 end if;
2948 end if;
2949 End get_cnf_doc_params_wf;
2950
2951 /* Added for Federal CLM Solicitaion Amendments Project.
2952 * Will be called from NegotiationCreation am in case of cancellation amendments.
2953 * This procedure inturn calls actual submit_for_approval procedure.
2954 * This procedure sets the workflow item attribute "CANCELNOTETOSUPPLIERS"
2955 */
2956 PROCEDURE SUB_CANCEL_AMEND_FOR_APPROVAL(
2957 p_auction_header_id_encrypted VARCHAR2,
2958 p_auction_header_id number,
2959 p_note_to_approvers varchar2,
2960 p_submit_user_name varchar2,
2961 p_redirect_func varchar2,
2962 p_cancel_supplier_note varchar2) IS
2963 l_itemKey varchar2(240);
2964 l_itemType varchar2(25):= 'PONAPPRV';
2965 Begin
2966 SUBMIT_FOR_APPROVAL(p_auction_header_id_encrypted,
2967 p_auction_header_id,
2968 p_note_to_approvers,
2969 p_submit_user_name,
2970 p_redirect_func);
2971
2972 --Get work flow item key
2973 select wf_approval_item_key
2974 into l_itemKey
2975 from pon_auction_headers_all
2976 where auction_header_id = p_auction_header_id;
2977
2978 --set workflow item attribute CANCELNOTETOSUPPLIERS.
2979 wf_engine.SetItemAttrText (itemtype => l_itemType,
2980 itemkey => l_itemKey,
2981 aname => 'CANCEL_NOTE_TO_SUPPLIERS',
2982 avalue => p_cancel_supplier_note);
2983
2984 End SUB_CANCEL_AMEND_FOR_APPROVAL;
2985
2986 /* Added for Federal CLM Solicitaion Amendments Project.
2987 * Will be called from Souring Approval Workflow to find if amendment type is cancellation.
2988 */
2989 PROCEDURE Is_Cancel_Amendment(itemtype in varchar2,
2990 itemkey in varchar2,
2994 l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
2991 actid in number,
2992 uncmode in varchar2,
2993 resultout out nocopy varchar2) IS
2995 Begin
2996 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2997 itemkey => itemkey,
2998 aname => 'AUCTION_HEADER_ID');
2999 select nvl(cancel_amendment,'N')
3000 into resultout
3001 from pon_auction_headers_all
3002 where
3003 auction_header_id = l_auction_header_id;
3004
3005 End Is_Cancel_Amendment;
3006
3007 /* Added for Federal CLM Solicitaion Amendments Project.
3008 * Will be called from Souring Approval Workflow to cancel the original document.
3009 */
3010 PROCEDURE CANCEL_FEDERAL_DOCUMENT_WF(itemtype in varchar2,
3011 itemkey in varchar2,
3012 actid in number,
3013 uncmode in varchar2,
3014 resultout out nocopy varchar2) IS
3015
3016 l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
3017 l_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
3018 l_revision PON_AUCTION_HEADERS_ALL.REVISION%TYPE;
3019 l_cancel_note_to_supplier varchar2(2000);
3020 l_error_code varchar2(1000);
3021 l_auction_id_prev_doc PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
3022
3023 l_msg_data varchar2(250);
3024 l_msg_count number;
3025 l_return_status varchar2(1);
3026 l_msg_suffix VARCHAR2(10);
3027 l_rev_message VARCHAR2(30);
3028 l_success_msg VARCHAR2(1000);
3029
3030 Begin
3031 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3032 itemkey => itemkey,
3033 aname => 'AUCTION_HEADER_ID');
3034
3035 l_cancel_note_to_supplier := wf_engine.GetItemAttrText (itemtype => itemtype,
3036 itemkey => itemkey,
3037 aname => 'CANCEL_NOTE_TO_SUPPLIERS');
3038
3039 PON_NEG_UPDATE_PKG.CANCEL_FEDERAL_NEGOTIATION(l_auction_header_id,
3040 'Y',
3041 l_cancel_note_to_supplier,
3042 fnd_global.user_id,
3043 l_document_number,
3044 l_error_code);
3045
3046 if l_error_code = 'SUCCESS' then
3047 select pon1.auction_header_id,pon1.document_number,pon1.revision
3048 into l_auction_id_prev_doc,l_document_number,l_revision
3049 from pon_auction_headers_all pon1
3050 where
3051 pon1.auction_header_id = (select pon2.auction_header_id_prev_amend
3052 from pon_auction_headers_all pon2
3053 where pon2.auction_header_id = l_auction_header_id);
3054
3055 PON_CONTERMS_UTL_PVT.cancelDeliverables(l_auction_id_prev_doc,
3056 -1,
3057 l_msg_data,
3058 l_msg_count,
3059 l_return_status);
3060
3061 -- Set the conformed doc details, so that they can be used later.
3062 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3063 itemkey => itemkey,
3064 aname => 'CONF_DOC_AUC_HEAD_ID',
3065 avalue => l_auction_id_prev_doc);
3066
3067 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3068 itemkey => itemkey,
3069 aname => 'CONF_DOC_REVISION',
3070 avalue => l_revision);
3071
3072 wf_engine.SetItemAttrText(itemtype => itemtype,
3073 itemkey => itemkey,
3074 aname => 'CONF_DOC_NUMBER',
3075 avalue => l_document_number);
3076
3077
3078 l_msg_suffix := wf_engine.GetItemAttrText(itemtype => itemtype,
3079 itemkey => itemkey,
3080 aname => 'MSG_SUFFIX');
3081
3082 fnd_message.set_name('PON', 'PON_REVISION_CLM');
3083 fnd_message.set_token('REVISION',to_char(l_revision));
3084 l_rev_message := fnd_message.get;
3085 fnd_message.set_name('PON','PON_AUCTION_AUCTION'||l_msg_suffix);
3086 l_success_msg := fnd_message.get||' '||l_document_number||l_rev_message;
3087 wf_engine.SetItemAttrText(itemtype => itemType,
3088 itemkey => itemKey,
3089 aname => 'NEW_CONF_DOC_CLM',
3090 avalue => l_success_msg);
3091 end if;
3092
3093 End CANCEL_FEDERAL_DOCUMENT_WF;
3094
3095 /* Added for Federal CLM Solicitaion Amendments Project.
3096 * Will be called from approval page when approval is done online.
3097 * Gets the conformed document number, auction header id, revision from workflow.
3098 */
3099 PROCEDURE get_cancel_doc_params_wf(p_auction_header_id IN NUMBER,
3100 x_cnf_doc_auc_head_id OUT NOCOPY NUMBER,
3101 x_cnf_doc_number OUT NOCOPY VARCHAR2,
3102 x_cnf_doc_rev OUT NOCOPY NUMBER,
3103 x_message OUT NOCOPY VARCHAR2,
3104 x_amend_doc_number OUT NOCOPY VARCHAR2) IS
3105 l_itemKey varchar2(240);
3106 l_itemType varchar2(25):= 'PONAPPRV';
3107 Begin
3108 --Get work flow item key
3109 select wf_approval_item_key
3110 into l_itemKey
3111 from pon_auction_headers_all
3112 where auction_header_id = p_auction_header_id;
3113 --Get the attributes from workflow
3114 x_cnf_doc_auc_head_id := wf_engine.GetItemAttrNumber(itemtype => l_itemType,
3115 itemkey => l_itemKey,
3116 aname => 'CONF_DOC_AUC_HEAD_ID');
3117 x_cnf_doc_number := wf_engine.GetItemAttrText(itemtype => l_itemType,
3118 itemkey => l_itemKey,
3119 aname => 'CONF_DOC_NUMBER');
3120 x_cnf_doc_rev := wf_engine.GetItemAttrNumber(itemtype => l_itemType,
3121 itemkey => l_itemKey,
3122 aname => 'CONF_DOC_REVISION');
3123
3124 x_message := wf_engine.GetItemAttrText(itemtype => l_itemType,
3125 itemkey => l_itemKey,
3126 aname => 'NEW_CONF_DOC_CLM');
3127 select document_number
3128 into x_amend_doc_number
3129 from pon_auction_headers_all
3130 where
3131 auction_header_id = p_auction_header_id;
3132
3133 End get_cancel_doc_params_wf;
3134
3135
3136 end PON_AUCTION_APPROVAL_PKG;