[Home] [Help]
PACKAGE BODY: APPS.PON_AUCTION_APPROVAL_PKG
Source
1 PACKAGE BODY PON_AUCTION_APPROVAL_PKG as
2 /* $Header: PONAPPRB.pls 120.11 2007/10/08 10:26:27 pchintap 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 Procedure Process_If_Doc_Approved(p_auction_header_id number,
31 p_top_process_item_key Varchar2) is
32 l_approved_count number;
33 l_approval_count number;
34 l_activity_status Varchar2(80);
35 l_item_type Varchar2(30) := 'PONAPPRV';
36 l_auction_approval_status Varchar2(30);
37 begin
38 select count(auction_header_id),
39 nvl(sum(decode(approval_status,'APPROVED',1,0)),0)
40 into l_approval_count,l_approved_count
41 from pon_neg_team_members
42 where auction_header_id = p_auction_header_id
43 and approver_flag='Y';
44 if l_approval_count =0 then
45 l_auction_approval_status := 'NOT_REQUIRED';
46 elsif l_approval_count = l_approved_count then
47 l_auction_approval_status := 'APPROVED';
48 else
49 l_auction_approval_status := 'REQUIRED';
50 end if;
51
52 -- The doucment is approved
53 if (l_auction_approval_status = 'APPROVED' or
54 l_auction_approval_status = 'NOT_REQUIRED') then
55 begin
56 select activity_label
57 into l_activity_status
58 from wf_item_activity_statuses_v
59 where item_type = 'PONAPPRV'
60 and item_key = p_top_process_item_key
61 and activity_status_code = 'NOTIFIED';
62 exception when others then
63 l_activity_status := 'DO Nothing';
64 end;
65 -- time to move the parent activity to the approved state
66 if l_activity_status = 'WAITFOR APPROVALS' then
67 if (l_auction_approval_status = 'APPROVED') then
68 wf_engine.CompleteActivity(l_item_type,p_top_process_item_key,l_activity_status,'APPROVED');
69 else wf_engine.CompleteActivity(l_item_type,p_top_process_item_key,l_activity_status,'NOT_REQUIRED');
70 end if;
71 end if;
72 end if; -- APPROVED
73 End Process_If_Doc_Approved;
74
75 Procedure Process_Doc_Rejected(p_auction_header_id number, p_top_process_item_key Varchar2) is
76 l_activity_status Varchar2(80);
77 l_item_type Varchar2(30) := 'PONAPPRV';
78 begin
79 begin
80 select activity_label
81 into l_activity_status
82 from wf_item_activity_statuses_v
83 where item_type = 'PONAPPRV'
84 and item_key = p_top_process_item_key
85 and activity_status_code = 'NOTIFIED';
86 exception when others then
87 l_activity_status := 'DO Nothing';
88 end;
89 -- time to move the parent activity to the rejected state
90 if l_activity_status = 'WAITFOR APPROVALS' then
91 wf_engine.CompleteActivity(l_item_type,p_top_process_item_key,l_activity_status,'REJECTED');
92 end if;
93 End Process_Doc_Rejected;
94
95 PROCEDURE CANCEL_NOTIFICATION(p_auction_header_id number,
96 p_user_name varchar2,
97 p_resultOut out nocopy number) is
98 l_itemKey varchar2(240);
99 l_parent_process_itemKey varchar2(240);
100 l_itemType varchar2(25):= 'PONAPPRV';
101 l_nid number;
102 l_replied varchar2(1);
103 l_auction_status varchar2(30);
104 l_user_status varchar2(30);
105 l_user_approval varchar2(30);
106 l_user_id number;
107 begin
108 -- need to change after understanding requirment from Datta
109 p_resultOut := 0;
110 -- Check to see if this user has already approved
111 begin
112
113 -- use user_id wherever possible
114 select user_id
115 into l_user_id
116 from fnd_user
117 where user_name = p_user_name;
118
119
120
121
122 select neg.approver_flag, neg.approval_status, auc.approval_status,
123 wf_approval_item_key
124 into l_user_approval,l_user_status,l_auction_status,l_parent_process_itemKey
125 from pon_neg_team_members neg, pon_auction_headers_all auc
126 where auc.auction_header_id = p_auction_header_id
127 and neg.auction_header_id= auc.auction_header_id
128 and neg.user_id = l_user_id;
129
130 -- update user as not an approver
131 update pon_neg_team_members
132 set approver_flag ='N'
133 where auction_header_id = p_auction_header_id
134 and user_id = l_user_id;
135 l_itemKey := l_parent_process_itemKey || '_' || l_user_id;
136 /* Select notification Id from the item key and user name */
137 select notification_id
138 into l_nid
139 from WF_ITEM_ACTIVITY_STATUSES
140 where ASSIGNED_USER = p_user_name
141 and ITEM_TYPE = l_itemType
142 and ITEM_KEY = l_itemkey
143 and activity_status ='NOTIFIED';
144 WF_Notification.cancel(l_nid);
145 exception
146 when others then
147 p_resultOut := 1; -- unexpected
148 end;
149 /* Check for doc approval conditions */
150 Process_If_Doc_Approved(p_auction_header_id,l_parent_process_itemKey);
151
152 end CANCEL_NOTIFICATION;
153
154 PROCEDURE UPDATE_NOTIF_ONLINE (p_auction_header_id number,
155 p_user_name varchar2,
156 p_result varchar2,
157 p_note_to_buyer varchar2,
158 p_resultOut out nocopy number) is
159 l_itemKey varchar2(240);
160 l_itemType varchar2(25):= 'PONAPPRV';
161 l_nid number;
162 l_replied varchar2(1);
163 l_auction_status varchar2(30);
164 l_user_status varchar2(30);
165 l_user_approval varchar2(30);
166 l_user_id number;
167 begin
168 p_resultOut := 0;
169
170 -- use user_id wherever possible
171 select user_id
172 into l_user_id
173 from fnd_user
174 where user_name = p_user_name;
175
176 -- Check to see if this user has already approved
177 begin
178 select neg.approver_flag, neg.approval_status, auc.approval_status,
179 wf_approval_item_key
180 into l_user_approval,l_user_status,l_auction_status,l_itemKey
181 from pon_neg_team_members neg, pon_auction_headers_all auc
182 where auc.auction_header_id = p_auction_header_id
183 and neg.auction_header_id= auc.auction_header_id
184 and neg.user_id = l_user_id;
185
186 -- the item key for the user will be
187 l_itemKey := l_itemKey || '_' || l_user_id;
188 /* Select notification Id from the item key and user name */
189 begin
190 select notification_id
191 into l_nid
192 from WF_ITEM_ACTIVITY_STATUSES
193 where ASSIGNED_USER = p_user_name
194 and ITEM_TYPE = l_itemType
195 and ITEM_KEY = l_itemkey
196 and activity_status ='NOTIFIED';
197 exception when no_data_found then
198 /* This is a situation where the user responded using E-Mail and
199 edited the decision with a typo!!
200 */
201 User_Decision_Without_WF(l_user_id, p_result, p_note_to_buyer,
202 p_auction_header_id);
203 return;
204 end;
205 wf_notification.SetAttrText(l_nid, 'RESULT',p_result);
206 wf_notification.SetAttrText(l_nid, 'APPROVER_NOTES',p_note_to_buyer);
207 WF_Notification.respond(l_nid,p_result,p_user_name);
208 exception
209 when others then
210 p_resultOut := 1; -- unexpected
211 end;
212
213 end UPDATE_NOTIF_ONLINE;
214
215 Procedure Close_Child_Process(p_parent_item_key Varchar2) is
216 Cursor List_of_Process(p_item_type varchar2, p_parent_item_key Varchar2) is
217 select activity_label, item_key,notification_id
218 from wf_item_activity_statuses_v
219 where item_type = p_item_type
220 and item_key like p_parent_item_key || '_%'
221 and activity_status_code = 'NOTIFIED';
222 l_item_type Varchar2(30) := 'PONAPPRV';
223 begin
224 for r1 in List_of_Process(l_item_type,p_parent_item_key) loop
225 begin
226 WF_Notification.cancel(r1.notification_id);
227 -- wf_engine.CompleteActivity(l_item_type,r1.item_key,
228 -- r1.activity_label,'CLOSE');
229
230 exception when others then null;
231 end;
232 end loop;
233
234 End Close_Child_Process;
235
236 PROCEDURE UPDATE_DOC_TO_CANCELLED ( itemtype in varchar2,
237 Itemkey in varchar2,
238 actid in number,
239 uncmode in varchar2,
240 resultout out nocopy varchar2) is
241
242 l_auction_header_id NUMBER;
243 begin
244 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
245 itemkey => itemkey,
246 aname => 'AUCTION_HEADER_ID');
247 update pon_auction_headers_all
248 set auction_status = 'CANCELLED'
249 where auction_header_id=l_auction_header_id;
250
251 end UPDATE_DOC_TO_CANCELLED;
252
253 /* Entry procedure to start document approval process.
254 */
255
256 PROCEDURE SUBMIT_FOR_APPROVAL(p_auction_header_id_encrypted VARCHAR2, -- 1
257 p_auction_header_id number, -- 2
258 p_note_to_approvers varchar2, -- 3
259 p_submit_user_name varchar2, -- 4
260 p_redirect_func varchar2) is -- 5
261 l_seq varchar2(100);
262 l_itemKey varchar2(240);
263 l_itemType varchar(25) := 'PONAPPRV';
264 l_creator_user_name varchar2(100);
265 l_creator_full_name varchar2(240);
266 l_creator_user_id number;
267 l_close_bidding_date date;
268 l_open_bidding_date date;
269 l_auction_title varchar2(80);
270 l_creator_time_zone varchar2(80);
271 l_doctype_group_name varchar2(100);
272 l_doc_number varchar2(25);
273 l_msg_suffix varchar2(10);
274 l_auction_contact_id number;
275 l_language_code varchar2(100);
276 l_timezone varchar2(100);
277 l_timezone_disp varchar2(100);
278 l_oex_timezone varchar2(100);
279 l_url_preview varchar2(500);
280 l_url_modify varchar2(500);
281 l_timeout_factor number;
282 l_open_date_in_tz date;
283 l_close_date_in_tz date;
284 l_open_auction_now_flag varchar2(1);
285 l_trading_partner_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
286 l_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
287 l_auction_start_date PON_AUCTION_HEADERS_ALL.OPEN_BIDDING_DATE%TYPE;
288 l_auction_end_date PON_AUCTION_HEADERS_ALL.CLOSE_BIDDING_DATE%TYPE;
289 l_round_number NUMBER;
290 l_amendment_number NUMBER;
291 l_auction_header_id_orig_amend NUMBER;
292 l_orig_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
293 l_review_changes_url VARCHAR2(2000);
294 l_preview_date DATE;
295 l_preview_date_in_tz DATE;
296 l_timezone1_disp VARCHAR2(240);
297 l_submit_user_id number;
298
299 /* Get FND user name from trading_partner_contact_id */
300 CURSOR c_auction_info IS
301 select fnd.user_name, fnd.user_id, pon.close_bidding_date,
302 pon.auction_title,
303 decode(nvl(pon.open_auction_now_flag,'N'),'Y',to_date(null),pon.open_bidding_date) open_bidding_date,
304 nvl(pon.open_auction_now_flag,'N') open_auction_now_flag,
305 pon.document_number, trading_partner_contact_id, trading_partner_name, trading_partner_contact_name,
306 open_bidding_date, close_bidding_date, nvl(auction_round_number, 1),
307 nvl(amendment_number, 0), auction_header_id_orig_amend, view_by_date
308 from fnd_user fnd,pon_auction_headers_all pon
309 where fnd.person_party_id = pon.trading_partner_contact_id and
310 pon.auction_header_id = p_auction_header_id and
311 rownum=1;
312
313 begin
314
315 OPEN c_auction_info;
316 FETCH c_auction_info
317 INTO l_creator_user_name, l_creator_user_id, l_close_bidding_date,l_auction_title,
318 l_open_bidding_date,
319 l_open_auction_now_flag,
320 l_doc_number,
321 l_auction_contact_id, l_trading_partner_name, l_trading_partner_contact_name,
322 l_auction_start_date, l_auction_end_date, l_round_number,
323 l_amendment_number, l_auction_header_id_orig_amend, l_preview_date;
324 CLOSE c_auction_info;
325
326 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
327 FND_LOG.string(log_level => FND_LOG.level_statement,
328 module => g_module_prefix || 'SUBMIT_FOR_APPROVAL',
329 message => 'l_language_code : ' || l_language_code);
330 END IF; --}
331
332
333
334 /* Get sequence number to construct itemKey */
335 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_creator_user_id,l_language_code);
336
337 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
338 FND_LOG.string(log_level => FND_LOG.level_statement,
339 module => g_module_prefix || 'SUBMIT_FOR_APPROVAL',
340 message => 'l_creator_user_id : '|| l_creator_user_id ||';'||' l_language_code : ' || l_language_code);
341 END IF; --}
342
343 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
344 FND_LOG.string(log_level => FND_LOG.level_statement,
345 module => g_module_prefix || 'SUBMIT_FOR_APPROVAL',
346 message => '1. Calling SET_SESSION_LANGUAGE with l_language_code : ' || l_language_code);
347 END IF; --}
348
349 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null,l_language_code);
350
351 select to_char(PON_AUCTION_WF_APPROVALS_S.NEXTVAL)
352 into l_seq from sys.dual;
353
354 l_itemKey := to_char (p_auction_header_id)|| '-' || l_seq;
355
359
356 wf_engine.createProcess ( itemType => l_itemType,
357 itemKey => l_itemKey,
358 process => 'SOURCINGAPPROVAL');
360
361 -- call to notification utility package to set the message header common attributes and #from_role
362 pon_wf_utl_pkg.set_hdr_attributes (p_itemtype => l_itemType
363 ,p_itemkey => l_itemKey
364 ,p_auction_tp_name => l_trading_partner_name
365 ,p_auction_title => l_auction_title
366 ,p_document_number => l_doc_number
367 ,p_auction_tp_contact_name => l_trading_partner_contact_name);
368
369
370
371 -- call to notification utility package to get the redirect page url that
372 -- is responsible for getting the Review and Submit page url and forward to it.
373 l_review_changes_url := pon_wf_utl_pkg.get_dest_page_url (
374 p_dest_func => 'PON_NEG_CRT_HEADER'
375 ,p_notif_performer => 'BUYER');
376
377
378 -- new item attribute to hold the redirect Function. Attribute value is going
379 -- to be used as a parameter to access Review and Submit page
380 wf_engine.SetItemAttrText (itemtype => l_itemType,
381 itemkey => l_itemKey,
382 aname => 'REVIEWPG_REDIRECTFUNC',
383 avalue => p_redirect_func);
384
385 wf_engine.SetItemAttrText (itemtype => l_itemType,
386 itemkey => l_itemKey,
387 aname => 'REVIEW_CHANGES_URL',
388 avalue => l_review_changes_url);
389
390 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
391 itemkey => l_itemKey,
392 aname => 'TRADING_PARTNER_CONTACT_ID',
393 avalue => l_auction_contact_id);
394
395 wf_engine.SetItemAttrDate (itemtype => l_itemType,
396 itemkey => l_itemKey,
397 aname => 'AUCTION_START_DATE',
398 avalue => l_open_bidding_date);
399
400 wf_engine.SetItemAttrDate (itemtype => l_itemType,
401 itemkey => l_itemKey,
402 aname => 'AUCTION_END_DATE',
403 avalue => l_close_bidding_date);
404
405 wf_engine.SetItemAttrText (itemtype => l_itemType,
406 itemkey => l_itemKey,
407 aname => 'TOP_PROCESS_ITEM_KEY',
408 avalue => l_itemKey);
409
410 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
411 itemkey => l_itemKey,
412 aname => 'AUCTION_HEADER_ID',
413 avalue => p_auction_header_id);
414
415 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
416 itemkey => l_itemKey,
417 aname => 'DOC_ROUND_NUMBER',
418 avalue => l_round_number);
419
420 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
421 itemkey => l_itemKey,
422 aname => 'DOC_AMENDMENT_NUMBER',
423 avalue => l_amendment_number);
424
425
426 wf_engine.SetItemAttrDate (itemtype => l_itemType,
427 itemkey => l_itemKey,
428 aname => 'TIMEOUT_MAINPROCESS',
429 avalue => l_close_bidding_date);
430
431 wf_engine.SetItemAttrText (itemtype => l_itemType,
432 itemkey => l_itemKey,
433 aname => 'CREATOR_USER_NAME',
434 avalue => l_creator_user_name);
435
436 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
437 itemkey => l_itemKey,
438 aname => 'CREATOR_USER_ID',
439 avalue => l_creator_user_id);
440
441 select dt.doctype_group_name
442 into l_doctype_group_name
443 from pon_auction_headers_all auh, pon_auc_doctypes dt
444 where auh.auction_header_id = p_auction_header_id
445 and auh.doctype_id = dt.doctype_id;
446
447 l_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (l_doctype_group_name);
448
449 wf_engine.SetItemAttrText (itemtype => l_itemType,
450 itemkey => l_itemKey,
451 aname => 'MSG_SUFFIX',
452 avalue => l_msg_suffix);
453 -- Get the user's timezone
454 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(l_auction_contact_id);
455
456 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
457
458 if (l_timezone is null or l_timezone = '' ) then
459 l_timezone := l_oex_timezone;
460 end if;
461
462
463 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(l_timezone) = 1) THEN
464 l_open_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_open_bidding_date,l_oex_timezone,l_timezone);
468 l_open_date_in_tz := l_open_bidding_date;
465 l_close_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_close_bidding_date,l_oex_timezone,l_timezone);
466 l_preview_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_preview_date,l_oex_timezone,l_timezone);
467 ELSE
469 l_close_date_in_tz := l_close_bidding_date;
470 l_preview_date_in_tz := l_preview_date;
471 l_timezone := l_oex_timezone;
472 END IF;
473
474 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
475
476 wf_engine.SetItemAttrText (itemtype => l_itemType,
477 itemkey => l_itemKey,
478 aname => 'TIMEZONE',
479 avalue => l_timezone_disp);
480
481 IF (l_preview_date IS NULL) THEN
482 l_timezone1_disp := null;
483
484 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
485 itemkey => l_itemkey,
486 aname => 'PREVIEW_DATE',
487 avalue => null);
488
489 wf_engine.SetItemAttrText (itemtype => l_itemtype,
490 itemkey => l_itemkey,
491 aname => 'TP_TIME_ZONE1',
492 avalue => l_timezone1_disp);
493
494 wf_engine.SetItemAttrText (itemtype => l_itemtype,
495 itemkey => l_itemkey,
496 aname => 'PREVIEW_DATE_NOTSPECIFIED',
497 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_PREVIEW_DATE_NOTSPEC',l_msg_suffix));
498 ELSE
499 l_timezone1_disp := l_timezone_disp;
500
501 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
502 itemkey => l_itemkey,
503 aname => 'PREVIEW_DATE',
504 avalue => l_preview_date_in_tz);
505
506 wf_engine.SetItemAttrText (itemtype => l_itemtype,
507 itemkey => l_itemkey,
508 aname => 'TP_TIME_ZONE1',
509 avalue => l_timezone1_disp);
510
511 wf_engine.SetItemAttrText (itemtype => l_itemtype,
512 itemkey => l_itemkey,
513 aname => 'PREVIEW_DATE_NOTSPECIFIED',
514 avalue => null);
515 END IF;
516
517
518 wf_engine.SetItemAttrDate (itemtype => l_itemType,
519 itemkey => l_itemKey,
520 aname => 'AUCTION_START_DATE',
521 avalue => l_open_date_in_tz);
522 if (l_open_auction_now_flag = 'Y') then
523 wf_engine.SetItemAttrText (itemtype => l_itemType,
524 itemkey => l_itemKey,
525 aname => 'OPEN_IMMEDIATELY',
526 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_OPEN_IMM_AFTER_PUB',l_msg_suffix));
527
528 wf_engine.SetItemAttrText (itemtype => l_itemType,
529 itemkey => l_itemKey,
530 aname => 'O_TIMEZONE',
531 avalue => null);
532 else
533 wf_engine.SetItemAttrText (itemtype => l_itemType,
534 itemkey => l_itemKey,
535 aname => 'OPEN_IMMEDIATELY',
536 avalue =>null);
537 wf_engine.SetItemAttrText (itemtype => l_itemType,
538 itemkey => l_itemKey,
539 aname => 'O_TIMEZONE',
540 avalue => l_timezone_disp);
541 end if;
542
543 wf_engine.SetItemAttrDate (itemtype => l_itemType,
544 itemkey => l_itemKey,
545 aname => 'AUCTION_END_DATE',
546 avalue => l_close_date_in_tz);
547
548
549 wf_engine.SetItemAttrText (itemtype => l_itemType,
550 itemkey => l_itemKey,
551 aname => 'NOTE_TO_APPROVERS',
552 avalue => p_note_to_approvers);
553 select document_number
554 into l_orig_document_number
555 from pon_auction_headers_all
556 where auction_header_id = l_auction_header_id_orig_amend;
557
558 -- set notification subjects
559 set_notification_subject(l_itemType, l_itemKey, l_msg_suffix, l_doc_number, l_orig_document_number, l_amendment_number, l_auction_title);
560
561 /* Get the creator's full name */
562 select emp.full_name into l_creator_full_name from
563 per_all_people_f emp,
564 fnd_user fnd
565 where fnd.employee_id=emp.person_id and
566 fnd.user_id = l_creator_user_id and
567 trunc(sysdate) between emp.effective_start_date and emp.effective_end_date;
568
569 wf_engine.SetItemAttrText (itemtype => l_itemType,
570 itemkey => l_itemKey,
571 aname => 'AUCTIONEER_NAME',
572 avalue => l_creator_full_name);
573
574
575 wf_engine.SetItemAttrText (itemtype => l_itemType,
576 itemkey => l_itemKey,
577 aname => 'ORIGIN_USER_NAME',
578 avalue => fnd_global.user_name);
579
580
581 -- use user_id wherever possible
582 select user_id
583 into l_submit_user_id
584 from fnd_user
585 where user_name = p_submit_user_name;
586
587
588 UPD_AUCTION_STATUSHISTORY(p_auction_header_id, 'SUBMIT',p_note_to_approvers,
592 -- Bug 4295915: Set the workflow owner
589 l_submit_user_id,'USER');
590
591
593 wf_engine.SetItemOwner(itemtype => l_itemtype,
594 itemkey => l_itemkey,
595 owner => fnd_global.user_name);
596
597 wf_engine.StartProcess (itemType => l_itemType,
598 itemKey => l_itemKey );
599
600 /* Update Headers table */
601 UPDATE pon_auction_headers_all set
602 wf_approval_item_key = l_itemKey,
603 approval_status = 'INPROCESS'
604 WHERE auction_header_id = p_auction_header_id;
605
606 end SUBMIT_FOR_APPROVAL;
607
608 PROCEDURE StartUserApprovalProcess(itemtype in varchar2,
609 Itemkey in varchar2,
610 actid in number,
611 uncmode in varchar2,
612 resultout out nocopy varchar2) is
613 l_auction_header_id number;
614 l_seq varchar2(100);
615 l_itemKey varchar2(240);
616 l_itemType varchar(25) := 'PONAPPRV';
617 l_creator_user_id number;
618 l_creator_full_name varchar2(240);
619 l_creator_user_name varchar2(100);
620 l_creator_session_lang_code varchar2(3);
621 l_creator_time_zone varchar2(80);
622 l_doctype_group_name varchar2(100);
623 l_msg_suffix varchar2(10);
624 l_auction_contact_id number;
625 l_language_code varchar2(3);
626 l_timezone varchar2(100);
627 l_timezone_disp varchar2(100);
628 l_oex_timezone varchar2(100);
629 l_open_date_in_tz date;
630 l_close_date_in_tz date;
631 l_url_preview varchar2(500);
632 l_url_modify varchar2(500);
633 l_timeout_factor number;
634 l_subtab varchar(80);
635 l_note_to_approvers Varchar2(2000);
636 l_publish_auction_now_flag varchar2(1);
637 l_open_auction_now_flag varchar2(1);
638 l_reminder_date date;
639 l_preparer_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
640 l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
641 l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
642 l_preparer_tp_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
643 l_auction_start_date DATE;
644 l_auction_end_date DATE;
645 l_timezone_dsp varchar2(100);
646 l_review_changes_url VARCHAR2(2000);
647 l_orig_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
648 l_round_number NUMBER;
649 l_amendment_number NUMBER;
650 l_preview_date_in_tz DATE;
651 l_timezone1_disp varchar2(240);
652 l_preview_date_nspec FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
653 l_redirect_func VARCHAR2(50);
654
655 Cursor C_APPROVALS(p_auction_header_id number, p_timeout_factor number) is
656 select u.user_name user_name,
657 u.user_id,
658 auc.close_bidding_date close_bidding_date,
659 auc.auction_title,
660 decode(nvl(auc.open_auction_now_flag,'N'),'Y',to_date(null),auc.open_bidding_date) open_bidding_date,
661 decode(nvl(auc.publish_auction_now_flag,'N'),'Y',to_date(null),auc.view_by_date) view_by_date,
662 auc.document_number doc_number,
663 trading_partner_contact_id auction_contact_id,
664 nvl(auc.publish_auction_now_flag,'N') publish_auction_now_flag,
665 nvl(auc.open_auction_now_flag,'N') open_auction_now_flag,
666 auc.auction_header_id_orig_amend
667 from pon_neg_team_members neg, pon_auction_headers_all auc, fnd_user u
668 where neg.auction_header_id = auc.auction_header_id and
669 auc.auction_header_id = p_auction_header_id
670 and neg.APPROVER_FLAG ='Y'
671 and u.user_id = neg.user_id;
672 begin
673
674
675 l_timeout_factor := .5; -- use to get this value from an item attribute
676 -- but WFLOAD was complaining....
677
678 if l_timeout_factor <= 0 or l_timeout_factor >=1 then
679 l_timeout_factor := .5;
680 end if;
681
682 l_preparer_tp_name := wf_engine.GetItemAttrText (itemtype => itemType,
683 itemkey => itemKey,
684 aname => 'PREPARER_TP_NAME');
685
686 l_auction_title := wf_engine.GetItemAttrText (itemtype => itemType,
687 itemkey => itemKey,
688 aname => 'AUCTION_TITLE');
689
690 l_doc_number := wf_engine.GetItemAttrText (itemtype => itemType,
691 itemkey => itemKey,
692 aname => 'DOC_NUMBER');
693
694 l_preparer_tp_contact_name := wf_engine.GetItemAttrText (itemtype => itemType,
695 itemkey => itemKey,
696 aname => 'PREPARER_TP_CONTACT_NAME');
697
698 l_auction_start_date := wf_engine.GetItemAttrDate (itemtype => itemType,
699 itemkey => itemKey,
700 aname => 'AUCTION_START_DATE');
701
702 l_auction_end_date := wf_engine.GetItemAttrDate (itemtype => itemType,
703 itemkey => itemKey,
704 aname => 'AUCTION_END_DATE');
705
706 l_timezone_dsp := wf_engine.GetItemAttrText (itemtype => itemType,
710
707 itemkey => itemKey,
708 aname => 'TIMEZONE');
709
711
712 l_note_to_approvers := wf_engine.GetItemAttrText (itemtype => itemType,
713 itemkey => itemKey,
714 aname => 'NOTE_TO_APPROVERS');
715
716 l_preview_date_in_tz := wf_engine.GetItemAttrDate (itemtype => itemType,
717 itemkey => itemKey,
718 aname => 'PREVIEW_DATE');
719 l_timezone1_disp := wf_engine.GetItemAttrText (itemtype => itemType,
720 itemkey => itemKey,
721 aname => 'TP_TIME_ZONE1');
722
723 l_preview_date_nspec := wf_engine.GetItemAttrText (itemtype => itemType,
724 itemkey => itemKey,
725 aname => 'PREVIEW_DATE_NOTSPECIFIED');
726 l_msg_suffix := wf_engine.GetItemAttrText (itemtype => itemType,
727 itemkey => itemKey,
728 aname => 'MSG_SUFFIX');
729
730 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemType,
731 itemkey => itemKey,
732 aname => 'AUCTION_HEADER_ID');
733
734 l_round_number := wf_engine.GetItemAttrNumber (itemtype => itemType,
735 itemkey => itemKey,
736 aname => 'DOC_ROUND_NUMBER');
737
738
739
740 l_amendment_number := wf_engine.GetItemAttrNumber (itemtype => itemType,
741 itemkey => itemKey,
742 aname => 'DOC_AMENDMENT_NUMBER');
743
744 l_creator_full_name:= wf_engine.GetItemAttrText (itemtype => itemType,
745 itemkey => itemKey,
746 aname => 'AUCTIONEER_NAME');
747
748 l_redirect_func:= wf_engine.GetItemAttrText (itemtype => itemType,
749 itemkey => itemKey,
750 aname => 'REVIEWPG_REDIRECTFUNC');
751
752 /* Preserve creator's session language */
753 l_creator_user_name := wf_engine.GetItemAttrText (itemtype => itemType,
754 itemkey => itemKey,
755 aname => 'CREATOR_USER_NAME');
756
757 l_creator_user_id := wf_engine.GetItemAttrNumber (itemtype => itemType,
758 itemkey => itemKey,
759 aname => 'CREATOR_USER_ID');
760
761
762 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_creator_user_id,l_creator_session_lang_code);
763
764 for r1 in C_APPROVALS(l_auction_header_id,l_timeout_factor) loop
765
766 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
767 FND_LOG.string(log_level => FND_LOG.level_statement,
768 module => g_module_prefix || 'StartUserApprovalProcess',
769 message => 'r1.user_id : ' || r1.user_id);
770 END IF; --}
771
772 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(r1.user_id,l_language_code);
773
774 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
775 FND_LOG.string(log_level => FND_LOG.level_statement,
776 module => g_module_prefix || 'StartUserApprovalProcess',
777 message => 'r1.user_id : '|| r1.user_id ||';'|| 'l_language_code : ' || l_language_code);
778 END IF; --}
779
780 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
781 FND_LOG.string(log_level => FND_LOG.level_statement,
782 module => g_module_prefix || 'StartUserApprovalProcess',
783 message => '1. Calling SET_SESSION_LANGUAGE with l_language_code : ' || l_language_code);
784 END IF; --}
785
786 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null,l_language_code);
787
788 --Bug 6472383 : If the Negotiation Preview date is mentioned as 'Not Specified', i.e. if the value of
789 -- l_preview_date_nspec is not null, we need to replace it with the string specific to recipient's language
790 IF (l_preview_date_nspec is not null) THEN
791 l_preview_date_nspec := PON_AUCTION_PKG.getMessage('PON_AUC_PREVIEW_DATE_NOTSPEC',l_msg_suffix);
792 END IF;
793
794 l_itemKey := itemkey || '_' || r1.user_id;
795
796
797 wf_engine.createProcess ( itemType => l_itemType,
798 itemKey => l_itemKey,
799 process => 'USERAPPROVALS');
800
801
802 if (r1.view_by_date is not null) then
803 l_reminder_date := r1.view_by_date;
804 elsif (r1.open_bidding_date is not null) then
805 l_reminder_date := r1.open_bidding_date;
806 else
807 l_reminder_date := r1.close_bidding_date;
808 end if;
809
810 select sysdate+((l_reminder_date - sysdate) * l_timeout_factor)
811 into l_reminder_date
812 from dual;
813
814 wf_engine.SetItemAttrDate (itemtype => l_itemType,
815 itemkey => l_itemKey,
816 aname => 'TIMEOUT_USERPROCESS',
820 itemkey => l_itemKey,
817 avalue => l_reminder_date);
818
819 wf_engine.SetItemAttrText (itemtype => l_itemType,
821 aname => 'CREATOR_USER_NAME',
822 avalue => l_creator_user_name);
823
824 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
825 itemkey => l_itemKey,
826 aname => 'CREATOR_USER_ID',
827 avalue => l_creator_user_id);
828
829 wf_engine.SetItemAttrText (itemtype => l_itemType,
830 itemkey => l_itemKey,
831 aname => 'REVIEWPG_REDIRECTFUNC',
832 avalue => l_redirect_func);
833
834 wf_engine.SetItemAttrText (itemtype => l_itemType,
835 itemkey => l_itemKey,
836 aname => 'PREPARER_TP_NAME',
837 avalue => l_preparer_tp_name);
838
839 wf_engine.SetItemAttrText (itemtype => l_itemType,
840 itemkey => l_itemKey,
841 aname => 'AUCTION_TITLE',
842 avalue => l_auction_title);
843
844 wf_engine.SetItemAttrText (itemtype => l_itemType,
845 itemkey => l_itemKey,
846 aname => 'DOC_NUMBER',
847 avalue => l_doc_number);
848
849 wf_engine.SetItemAttrText (itemtype => l_itemType,
850 itemkey => l_itemKey,
851 aname => 'PREPARER_TP_CONTACT_NAME',
852 avalue => l_preparer_tp_contact_name);
853
854 wf_engine.SetItemAttrDate (itemtype => l_itemType,
855 itemkey => l_itemKey,
856 aname => 'AUCTION_START_DATE',
857 avalue => l_auction_start_date);
858
859 wf_engine.SetItemAttrDate (itemtype => l_itemType,
860 itemkey => l_itemKey,
861 aname => 'AUCTION_END_DATE',
862 avalue => l_auction_end_date);
863
864 wf_engine.SetItemAttrText (itemtype => l_itemType,
865 itemkey => l_itemKey,
866 aname => 'TIMEZONE',
867 avalue => l_timezone_dsp);
868
869 wf_engine.SetItemAttrDate (itemtype => l_itemType,
870 itemkey => l_itemKey,
871 aname => 'PREVIEW_DATE',
872 avalue => l_preview_date_in_tz);
873
874 wf_engine.SetItemAttrText (itemtype => l_itemType,
875 itemkey => l_itemKey,
876 aname => 'TP_TIME_ZONE1',
877 avalue => l_timezone1_disp);
878
879
880 wf_engine.SetItemAttrText (itemtype => l_itemType,
881 itemkey => l_itemKey,
882 aname => 'PREVIEW_DATE_NOTSPECIFIED',
883 avalue => l_preview_date_nspec);
884
885 wf_engine.SetItemAttrText (itemtype => l_itemType,
886 itemkey => l_itemKey,
887 aname => 'NOTE_TO_APPROVERS',
888 avalue => l_note_to_approvers);
889
890 wf_engine.SetItemAttrText (itemtype => l_itemType,
891 itemkey => l_itemKey,
892 aname => 'TOP_PROCESS_ITEM_KEY',
893 avalue =>
894 wf_engine.GetItemAttrText (itemtype => l_itemType,
895 itemkey => itemKey,
896 aname => 'TOP_PROCESS_ITEM_KEY')
897 );
898
899 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
900 itemkey => l_itemKey,
901 aname => 'AUCTION_HEADER_ID',
902 avalue => l_auction_header_id);
903
904 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
905 itemkey => l_itemKey,
906 aname => 'DOC_ROUND_NUMBER',
907 avalue => l_round_number);
908
909 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
910 itemkey => l_itemKey,
911 aname => 'DOC_AMENDMENT_NUMBER',
912 avalue => l_amendment_number);
913
914 l_review_changes_url := pon_wf_utl_pkg.get_dest_page_url (
915 p_dest_func => 'PON_NEG_CRT_HEADER'
916 ,p_notif_performer => 'BUYER');
917
918 wf_engine.SetItemAttrText (itemtype => l_itemType,
919 itemkey => l_itemKey,
920 aname => 'REVIEW_CHANGES_URL',
921 avalue => l_review_changes_url);
922
923
924 wf_engine.SetItemAttrText (itemtype => l_itemType,
925 itemkey => l_itemKey,
926 aname => 'APPOVER',
927 avalue => r1.user_name);
928
932 avalue => r1.user_id);
929 wf_engine.SetItemAttrNumber (itemtype => l_itemType,
930 itemkey => l_itemKey,
931 aname => 'APPOVER_ID',
933
934
935 wf_engine.SetItemAttrText (itemtype => l_itemType,
936 itemkey => l_itemKey,
937 aname => 'ORIGIN_USER_NAME',
938 avalue => fnd_global.user_name);
939
940 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(r1.user_name);
941
942 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
943
944 if (l_timezone is null) then
945 l_timezone := l_oex_timezone;
946 end if;
947
948 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
949
950 wf_engine.SetItemAttrText (itemtype => l_itemType,
951 itemkey => l_itemKey,
952 aname => 'TIMEZONE',
953 avalue => l_timezone_disp);
954
955 l_open_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(r1.open_bidding_date,l_oex_timezone,l_timezone);
956 l_close_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(r1.close_bidding_date,l_oex_timezone,l_timezone);
957
958 wf_engine.SetItemAttrDate (itemtype => l_itemType,
959 itemkey => l_itemKey,
960 aname => 'AUCTION_START_DATE',
961 avalue => l_open_date_in_tz);
962 if (r1.open_auction_now_flag = 'Y') then
963 wf_engine.SetItemAttrText (itemtype => l_itemType,
964 itemkey => l_itemKey,
965 aname => 'OPEN_IMMEDIATELY',
966 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_OPEN_IMM_AFTER_PUB',l_msg_suffix));
967
968 wf_engine.SetItemAttrText (itemtype => l_itemType,
969 itemkey => l_itemKey,
970 aname => 'O_TIMEZONE',
971 avalue => null);
972 else
973 wf_engine.SetItemAttrText (itemtype => l_itemType,
974 itemkey => l_itemKey,
975 aname => 'OPEN_IMMEDIATELY',
976 avalue =>null);
977
978 wf_engine.SetItemAttrText (itemtype => l_itemType,
979 itemkey => l_itemKey,
980 aname => 'O_TIMEZONE',
981 avalue => l_timezone_disp);
982 end if;
983
984 wf_engine.SetItemAttrDate (itemtype => l_itemType,
985 itemkey => l_itemKey,
986 aname => 'AUCTION_END_DATE',
987 avalue => l_close_date_in_tz);
988
989
990 wf_engine.SetItemAttrText (itemtype => l_itemType,
991 itemkey => l_itemKey,
992 aname => 'NOTE_TO_APPROVERS',
993 avalue => l_note_to_approvers);
994
995 select document_number
996 into l_orig_document_number
997 from pon_auction_headers_all
998 where auction_header_id = r1.auction_header_id_orig_amend;
999
1000 -- set notification subjects
1001 set_notification_subject(l_itemType, l_itemKey, l_msg_suffix, l_doc_number, l_orig_document_number, l_amendment_number, l_auction_title);
1002
1003 -- Bug 4295915: Set the workflow owner
1004 wf_engine.SetItemOwner(itemtype => l_itemtype,
1005 itemkey => l_itemkey,
1006 owner => fnd_global.user_name);
1007
1008 wf_engine.StartProcess (itemType => l_itemType,
1009 itemKey => l_itemKey );
1010 end loop;
1011
1012 /* Reset to creator's language */
1013 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null,l_creator_session_lang_code);
1014
1015 end StartUserApprovalProcess;
1016
1017
1018 Function Is_Valid_Response(p_auction_header_id number,p_user_id varchar2)
1019 return Varchar2 is
1020 l_result Varchar2(10) := 'N';
1021 Begin
1022 begin
1023 select 'Y'
1024 into l_result
1025 from pon_auction_headers_all auc,
1026 pon_neg_team_members neg
1027 where auc.auction_header_id = neg.auction_header_id
1028 and auc.auction_header_id = p_auction_header_id
1029 and auc.approval_status = 'INPROCESS'
1030 and neg.user_id = p_user_id
1031 and neg.approver_flag = 'Y'
1032 and neg.approval_status is null;
1033 exception when others then
1034 l_result := 'N';
1035 end;
1036 return(l_result);
1037 End Is_Valid_Response;
1038
1039 PROCEDURE User_Approved(itemtype in varchar2,
1040 itemkey in varchar2,
1041 actid in number,
1042 uncmode in varchar2,
1043 resultout out nocopy varchar2) is
1044 l_notes varchar2(2000);
1045 l_user_name varchar2(100);
1046 l_user_id number;
1047 l_auction_header_id number;
1048 l_top_process_item_key Varchar2(240);
1049 l_result Varchar2(30) := 'APPROVED';
1050 begin
1051 /* Get auction header id from the workflow */
1052 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1053 itemkey => itemkey,
1057 itemkey => itemkey,
1054 aname => 'AUCTION_HEADER_ID');
1055
1056 l_user_name := wf_engine.GetItemAttrText (itemtype => itemtype,
1058 aname => 'APPOVER');
1059
1060 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1061 itemkey => itemkey,
1062 aname => 'APPOVER_ID');
1063
1064 l_notes := wf_engine.GetItemAttrText (itemtype => itemtype,
1065 itemkey => itemkey,
1066 aname => 'APPROVER_NOTES');
1067
1068 l_top_process_item_key := wf_engine.GetItemAttrText (itemtype => itemType,
1069 itemkey => itemKey,
1070 aname => 'TOP_PROCESS_ITEM_KEY');
1071
1072 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1073 itemkey => l_top_process_item_key,
1074 aname => 'APPOVER_ID',
1075 avalue => l_user_id);
1076
1077 wf_engine.SetItemAttrText (itemtype => itemtype,
1078 itemkey => l_top_process_item_key,
1079 aname => 'APPOVER',
1080 avalue => l_user_name);
1081
1082
1083 /* Check is the responder a valid approver */
1084 if (Is_Valid_Response(l_auction_header_id,l_user_id) = 'N') then
1085 /* Responder is not a valid approver. Ignore this response */
1086 return;
1087 end if;
1088
1089 /* Insert a row into history table */
1090 UPD_AUCTION_STATUSHISTORY(l_auction_header_id, l_result,
1091 l_notes,l_user_id,'USER');
1092
1093
1094 Process_If_Doc_Approved(l_auction_header_id,l_top_process_item_key);
1095
1096
1097 end User_approved;
1098
1099 /* This is a sort of "backup" to approve online if workflow
1100 fails */
1101 PROCEDURE User_Decision_Without_WF(p_user_id in number,
1102 p_decision in varchar2,
1103 p_notes in varchar2,
1104 p_auctionHeaderId in number) is
1105 l_top_process_itemKey varchar2(240);
1106 begin
1107 /* Check is the responder a valid approver */
1108 if (Is_Valid_Response(p_auctionHeaderId,p_user_id) = 'N') then
1109 /* Responder is not a valid approver. Ignore this response */
1110 return;
1111 end if;
1112
1113 /* Insert a row into history table */
1114 UPD_AUCTION_STATUSHISTORY(p_auctionHeaderId, p_decision,
1115 p_notes, p_user_id,'USER');
1116
1117 /* Get the top process item key */
1118 Select wf_approval_item_key
1119 into l_top_process_itemKey
1120 from pon_auction_headers_all
1121 where auction_header_id = p_auctionHeaderId;
1122
1123 if (p_decision = 'APPROVE') then
1124 Process_If_Doc_Approved(p_auctionHeaderId,l_top_process_itemKey);
1125 end if;
1126 if (p_decision = 'REJECT') then
1127 Process_Doc_Rejected(p_auctionHeaderId,l_top_process_itemKey);
1128 end if;
1129
1130 end User_Decision_Without_WF;
1131
1132 PROCEDURE User_Rejected(itemtype in varchar2,
1133 itemkey in varchar2,
1134 actid in number,
1135 uncmode in varchar2,
1136 resultout out nocopy varchar2) is
1137 l_notes varchar2(2000);
1138 l_user_name varchar2(100);
1139 l_user_id number;
1140 l_auction_header_id number;
1141 l_top_process_item_key Varchar2(240);
1142 l_result Varchar2(30) := 'REJECTED';
1143 begin
1144
1145 /* Get auction header id from the workflow */
1146 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1147 itemkey => itemkey,
1148 aname => 'AUCTION_HEADER_ID');
1149
1150 l_user_name := wf_engine.GetItemAttrText (itemtype => itemtype,
1151 itemkey => itemkey,
1152 aname => 'APPOVER');
1153
1154 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1155 itemkey => itemkey,
1156 aname => 'APPOVER_ID');
1157
1158 l_notes := wf_engine.GetItemAttrText (itemtype => itemtype,
1159 itemkey => itemkey,
1160 aname => 'APPROVER_NOTES');
1161
1162 l_top_process_item_key := wf_engine.GetItemAttrText (itemtype => itemType,
1163 itemkey => itemKey,
1164 aname => 'TOP_PROCESS_ITEM_KEY');
1165
1166 wf_engine.SetItemAttrText (itemtype => itemtype,
1167 itemkey => l_top_process_item_key,
1168 aname => 'APPOVER',
1169 avalue => l_user_name);
1170
1171 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1172 itemkey => l_top_process_item_key,
1173 aname => 'APPOVER_ID',
1177 if (Is_Valid_Response(l_auction_header_id,l_user_id) = 'N') then
1174 avalue => l_user_id);
1175
1176 /* Check is the responder a valid approver */
1178 /* Responder is not a valid approver. Ignore this response */
1179 return;
1180 end if;
1181 wf_engine.SetItemAttrText (itemtype => itemtype,
1182 itemkey => l_top_process_item_key,
1183 aname => 'NOTE_TO_BUYER_ON_REJECT',
1184 avalue => l_notes);
1185
1186 /*Update PON_NEG_TEAM_MEMEBERS APPROVAL_STATUS field */
1187 /* Insert a row into history table */
1188 UPD_AUCTION_STATUSHISTORY(l_auction_header_id, l_result,
1189 l_notes,l_user_id,'USER');
1190 Process_Doc_Rejected(l_auction_header_id,l_top_process_item_key);
1191
1192 end User_Rejected;
1193
1194 PROCEDURE Doc_Approved(itemtype in varchar2,
1195 itemkey in varchar2,
1196 actid in number,
1197 uncmode in varchar2,
1198 resultout out nocopy varchar2) is
1199
1200 l_auction_header_id Number;
1201 l_status Varchar2(30) := 'APPROVED';
1202 l_user_id Number;
1203 l_approve_date PON_ACTION_HISTORY.ACTION_DATE%TYPE;
1204 l_approve_date_in_tz DATE;
1205 l_language_code varchar2(100);
1206 l_timezone varchar2(100);
1207 l_timezone_disp varchar2(100);
1208 l_oex_timezone varchar2(100);
1209 l_auction_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
1210 l_preview_date_in_tz DATE;
1211 l_timezone1_disp varchar2(240);
1212 l_preview_date_nspec FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1213
1214 Begin
1215 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1216 itemkey => itemkey,
1217 aname => 'AUCTION_HEADER_ID');
1218
1219 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1220 itemkey => itemkey,
1221 aname => 'CREATOR_USER_ID');
1222
1223 l_auction_contact_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1224 itemkey => itemkey,
1225 aname => 'TRADING_PARTNER_CONTACT_ID');
1226
1227
1228 l_preview_date_in_tz := wf_engine.GetItemAttrDate (itemtype => itemType,
1229 itemkey => itemKey,
1230 aname => 'PREVIEW_DATE');
1231 l_timezone1_disp := wf_engine.GetItemAttrText (itemtype => itemType,
1232 itemkey => itemKey,
1233 aname => 'TP_TIME_ZONE1');
1234
1235 l_preview_date_nspec := wf_engine.GetItemAttrText (itemtype => itemType,
1236 itemkey => itemKey,
1237 aname => 'PREVIEW_DATE_NOTSPECIFIED');
1238 SELECT max(action_date)
1239 INTO l_approve_date
1240 FROM pon_action_history
1241 WHERE object_id = l_auction_header_id
1242 and object_type_code = 'NEGOTIATION'
1243 and action_type = 'APPROVE';
1244
1245 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_user_id,l_language_code);
1246 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
1247
1248 -- Get the user's time zone
1249 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(l_auction_contact_id);
1250
1251 if (l_timezone is null or l_timezone = '') then
1252 l_timezone := l_oex_timezone;
1253 end if;
1254
1255
1256 -- Convert the date to the user's timezone.
1257 -- If the timezone is not recognized, just use server timezone
1258 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(l_timezone) = 1) THEN
1259 l_approve_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_approve_date,l_oex_timezone,l_timezone);
1260 ELSE
1261 l_approve_date_in_tz := l_approve_date;
1262 l_timezone := l_oex_timezone;
1263 END IF;
1264
1265 -- Set the dates based on the user's time zone
1266 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
1267
1268 wf_engine.SetItemAttrDate (itemtype => itemType,
1269 itemkey => itemKey,
1270 aname => 'APPROVE_DATE',
1271 avalue => l_approve_date_in_tz);
1272
1273 wf_engine.SetItemAttrText (itemtype => itemType,
1274 itemkey => itemKey,
1275 aname => 'TIMEZONE',
1276 avalue => l_timezone_disp);
1277
1278 UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
1279 l_status,
1280 NULL,
1281 l_user_id,'AUCTION');
1282
1283 End Doc_Approved;
1284
1285 PROCEDURE Doc_Rejected(itemtype in varchar2,
1286 itemkey in varchar2,
1287 actid in number,
1288 uncmode in varchar2,
1289 resultout out nocopy varchar2) is
1290
1291 l_auction_header_id Number;
1295 l_reject_date PON_ACTION_HISTORY.ACTION_DATE%TYPE;
1292 l_status Varchar2(30) := 'REJECTED';
1293 l_user_id Number;
1294 l_rejection_note Varchar2(2000);
1296 l_reject_date_in_tz DATE;
1297 l_language_code varchar2(100);
1298 l_timezone varchar2(100);
1299 l_timezone_disp varchar2(100);
1300 l_oex_timezone varchar2(100);
1301 l_auction_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
1302 l_preview_date_in_tz DATE;
1303 l_timezone1_disp varchar2(240);
1304 l_preview_date_nspec FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1305
1306 Begin
1307 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1308 itemkey => itemkey,
1309 aname => 'AUCTION_HEADER_ID');
1310
1311 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1312 itemkey => itemkey,
1313 aname => 'CREATOR_USER_ID');
1314
1315 l_rejection_note := wf_engine.GetItemAttrText (itemtype => itemtype,
1316 itemkey => itemkey,
1317 aname => 'NOTE_TO_BUYER_ON_REJECT');
1318
1319 l_auction_contact_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1320 itemkey => itemkey,
1321 aname => 'TRADING_PARTNER_CONTACT_ID');
1322
1323
1324 l_preview_date_in_tz := wf_engine.GetItemAttrDate (itemtype => itemType,
1325 itemkey => itemKey,
1326 aname => 'PREVIEW_DATE');
1327
1328
1329 l_timezone1_disp := wf_engine.GetItemAttrText (itemtype => itemType,
1330 itemkey => itemKey,
1331 aname => 'TP_TIME_ZONE1');
1332
1333 l_preview_date_nspec := wf_engine.GetItemAttrText (itemtype => itemType,
1334 itemkey => itemKey,
1335 aname => 'PREVIEW_DATE_NOTSPECIFIED');
1336
1337 SELECT max(action_date)
1338 INTO l_reject_date
1339 FROM pon_action_history
1340 WHERE object_id = l_auction_header_id
1341 and object_type_code = 'NEGOTIATION'
1342 and action_type = 'REJECT';
1343
1344 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_user_id,l_language_code);
1345 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
1346
1347 -- Get the user's time zone
1348 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(l_auction_contact_id);
1349
1350 if (l_timezone is null or l_timezone = '') then
1351 l_timezone := l_oex_timezone;
1352 end if;
1353
1354
1355 -- Convert the date to the user's timezone.
1356 -- If the timezone is not recognized, just use server timezone
1357 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(l_timezone) = 1) THEN
1358 l_reject_date_in_tz := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(l_reject_date,l_oex_timezone,l_timezone);
1359 ELSE
1360 l_reject_date_in_tz := l_reject_date;
1361 l_timezone := l_oex_timezone;
1362 END IF;
1363
1364 -- Set the dates based on the user's time zone
1365 l_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
1366
1367 wf_engine.SetItemAttrDate (itemtype => itemType,
1368 itemkey => itemKey,
1369 aname => 'REJECT_DATE',
1370 avalue => l_reject_date_in_tz);
1371
1372 wf_engine.SetItemAttrText (itemtype => itemType,
1373 itemkey => itemKey,
1374 aname => 'TIMEZONE',
1375 avalue => l_timezone_disp);
1376
1377 UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
1378 l_status,
1379 l_rejection_note,
1380 l_user_id,'AUCTION');
1381
1382 close_child_process(itemkey);
1383
1384 End Doc_Rejected;
1385
1386 PROCEDURE Doc_timedout(itemtype in varchar2,
1387 itemkey in varchar2,
1388 actid in number,
1389 uncmode in varchar2,
1390 resultout out nocopy varchar2) is
1391 l_auction_header_id Number;
1392 l_status Varchar2(30) := 'TIMEOUT';
1393 l_user_id Number;
1394 l_rejection_note Varchar2(2000);
1395 Begin
1396 l_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1397 itemkey => itemkey,
1398 aname => 'AUCTION_HEADER_ID');
1399
1400 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1401 itemkey => itemkey,
1402 aname => 'CREATOR_USER_ID');
1403
1404 l_rejection_note := wf_engine.GetItemAttrText (itemtype => itemtype,
1405 itemkey => itemkey,
1406 aname => 'NOTE_TO_BUYER_ON_REJECT');
1407
1408 UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
1412
1409 l_status,
1410 NULL,
1411 l_user_id,'AUCTION');
1413 close_child_process(itemkey);
1414
1415 End Doc_timedout;
1416
1417 Procedure UPD_AUCTION_STATUSHISTORY(p_auction_header_id number,
1418 p_status Varchar2,
1419 p_notes Varchar2,
1420 p_user_id number,
1421 p_upd_history_type varchar2 ) is
1422
1423 begin
1424
1425 if p_upd_history_type = 'USER' then
1426 if (p_status <> 'SUBMIT') then
1427 Update pon_neg_team_members
1428 set approval_status = decode(p_status,'APPROVE','APPROVED','REJECT','REJECTED',p_status)
1429 where auction_header_id = p_auction_header_id
1430 and user_id = p_user_id;
1431 end if;
1432 insert into pon_action_history
1433 (object_id,
1434 object_id2,
1435 object_type_code,
1436 sequence_num,
1437 action_type,
1438 action_date,
1439 action_user_id,
1440 action_note)
1441 values (
1442 p_auction_header_id,
1443 p_auction_header_id,
1444 'NEGOTIATION',
1445 0,
1446 decode(p_status,'APPROVED','APPROVE','REJECTED','REJECT',p_status),
1447 sysdate,
1448 p_user_id,
1449 p_notes
1450 );
1451 elsif p_upd_history_type = 'AUCTION' then
1452
1453 /*
1454 * In case the document has been APPROVED, REJECTED, TIMEOUT
1455 * then we unlock the negotiation. If we do not unlock the
1456 * neg the lock might remain with the approver in case he
1457 * navigated to the review page. Bug 4777895.
1458 */
1459
1460 update pon_auction_headers_all
1461 set approval_status = p_status,
1462 draft_locked = 'N',
1463 draft_locked_by = null,
1464 draft_locked_by_contact_id = null,
1465 draft_locked_date = null
1466 where auction_header_id = p_auction_header_id;
1467 end if;
1468
1469 end UPD_AUCTION_STATUSHISTORY;
1470
1471
1472 PROCEDURE SET_NOTIFICATION_SUBJECT(p_itemtype in varchar2,
1473 p_itemkey in varchar2,
1474 p_msg_suffix in varchar2,
1475 p_doc_number in varchar2,
1476 p_orig_document_number in varchar2,
1477 p_amendment_number in number,
1478 p_auction_title in varchar2) IS
1479 BEGIN
1480
1481
1482 if (p_amendment_number is not null and p_amendment_number > 0) then
1483 wf_engine.SetItemAttrText (itemtype => p_itemType,
1484 itemkey => p_itemKey,
1485 aname => 'REQUEST_FOR_APPROVALS_SUBJECT',
1486 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));
1487
1488 wf_engine.SetItemAttrText (itemtype => p_itemType,
1489 itemkey => p_itemKey,
1490 aname => 'PON_AUC_APPR_REMINDER_SUB',
1491 avalue => PON_AUCTION_PKG.getMessage('PON_AMEND_APPR_REMINDER_SUB',p_msg_suffix,'AMENDMENT_NUMBER', p_amendment_number, 'ORIG_NUMBER', p_orig_document_number));
1492
1493 wf_engine.SetItemAttrText (itemtype => p_itemType,
1494 itemkey => p_itemKey,
1495 aname => 'DOC_APPROVED_MAIL_SUBJECT',
1496 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));
1497
1498 wf_engine.SetItemAttrText (itemtype => p_itemType,
1499 itemkey => p_itemKey,
1500 aname => 'DOC_REJECTED_MAIL_SUBJECT',
1501 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));
1502
1503 else
1504 wf_engine.SetItemAttrText (itemtype => p_itemType,
1505 itemkey => p_itemKey,
1506 aname => 'REQUEST_FOR_APPROVALS_SUBJECT',
1507 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_APPR_REQ_SUBJECT',p_msg_suffix,'DOC_NUMBER', p_doc_number, 'AUCTION_TITLE', p_auction_title));
1508
1509
1510 wf_engine.SetItemAttrText (itemtype => p_itemType,
1511 itemkey => p_itemKey,
1512 aname => 'PON_AUC_APPR_REMINDER_SUB',
1513 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_APPR_REMINDER_SUB',p_msg_suffix,'DOC_NUMBER', p_doc_number));
1514
1515
1516 wf_engine.SetItemAttrText (itemtype => p_itemType,
1517 itemkey => p_itemKey,
1518 aname => 'DOC_APPROVED_MAIL_SUBJECT',
1519 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_APPR_APPRD_SUBJECT',p_msg_suffix, 'DOC_NUMBER',p_doc_number, 'AUCTION_TITLE', p_auction_title));
1520
1521 wf_engine.SetItemAttrText (itemtype => p_itemType,
1522 itemkey => p_itemKey,
1523 aname => 'DOC_REJECTED_MAIL_SUBJECT',
1524 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_APPR_REJ_SUBJECT', p_msg_suffix,'DOC_NUMBER',p_doc_number, 'AUCTION_TITLE', p_auction_title));
1525 end if;
1526
1527 END SET_NOTIFICATION_SUBJECT;
1528
1529 end PON_AUCTION_APPROVAL_PKG;