[Home] [Help]
PACKAGE BODY: APPS.PON_AWARD_APPROVAL_PKG
Source
1 PACKAGE BODY PON_AWARD_APPROVAL_PKG AS
2 /* $Header: PONAWAPB.pls 120.20.12020000.3 2013/02/09 07:47:34 hvutukur ship $ */
3 /*=======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME
8 | PONAWAPB.pls
9 |
10 | DESCRIPTION
11 | PL/SQL body for package PON_AWARD_APPROVAL_PKG
12 |
13 | NOTES
14 | PL/SQL routines for negotiation award approvals
15 |
16 | HISTORY
17 | Date UserName Description
18 | -------------------------------------------------------------------------------------------
19 | ? ? Created
20 |
21 | 25-Aug-05 sparames Bug 4295915: Missing owner in Sourcing notifications
22 | Added call to wf_engine.SetItemOwner
23 |
24 =========================================================================+*/
25
26
27 -- DEFINITIONS/DECLARATIONS
28 -- workflow item type
29 AWARD_APPROVAL_WF_ITEM_TYPE CONSTANT VARCHAR2(10) := 'PONAWAPR';
30
31 -- action history object type
32 AWARD_OBJECT_TYPE VARCHAR2(20) := 'NEGOTIATION_AWARD';
33
34 -- constants for identifying notification types
35 APPROVAL_REQUEST CONSTANT INTEGER := 1;
36 AWARD_APPROVED CONSTANT INTEGER := 2;
37 AWARD_REJECTED CONSTANT INTEGER := 3;
38 ERROR CONSTANT INTEGER := 4;
39 ERROR_FOR_ADMIN CONSTANT INTEGER := 5;
40
41 -- a record that stores information about an employee
42 TYPE employeeRecord IS RECORD (
43 user_id fnd_user.user_id%TYPE,
44 user_name fnd_user.user_name%TYPE,
45 person_id per_all_people_f.person_id%TYPE
46 );
47
48 -- a null employee
49 nullEmployeeRecord employeeRecord;
50
51
52 -- Updated Parsing and Formatting routines to be compatible with AME API 11.5.10.
53 PROCEDURE parse_ame_approver( p_approver_string IN VARCHAR2, p_approver OUT NOCOPY ame_util.approverRecord2 );
54 FUNCTION format_ame_approver( p_approver IN ame_util.approverRecord2 ) RETURN VARCHAR2;
55 FUNCTION format_ame_approver_list( p_approver_list IN ame_util.approversTable2 ) RETURN VARCHAR2;
56 FUNCTION is_old_approver_record( p_approver_string IN VARCHAR2 ) RETURN BOOLEAN;
57
58 -- parsing and formatting routines
59 PROCEDURE parse_approver(p_approver_string IN VARCHAR2,
60 p_approver OUT NOCOPY ame_util.approverRecord);
61 PROCEDURE format_approver(p_approver IN ame_util.approverRecord,
62 p_approver_string OUT NOCOPY VARCHAR2);
63 PROCEDURE parse_approver_list(p_approver_list_string IN VARCHAR2,
64 p_approver_list OUT NOCOPY ame_util.approversTable);
65 PROCEDURE format_approver_list(p_approver_list IN ame_util.approversTable,
66 p_approver_list_string OUT NOCOPY VARCHAR2);
67 FUNCTION convert_to_ame_api_insertion(p_pon_api_insertion IN NUMBER) RETURN VARCHAR2;
68 FUNCTION convert_to_pon_api_insertion(p_ame_api_insertion IN VARCHAR2) RETURN NUMBER;
69 FUNCTION convert_to_ame_authority(p_pon_authority IN NUMBER) RETURN VARCHAR2;
70 FUNCTION convert_to_pon_authority(p_ame_authority IN VARCHAR2) RETURN NUMBER;
71 FUNCTION convert_to_ame_approval_status(p_pon_approval_status IN NUMBER) RETURN VARCHAR2;
72 FUNCTION convert_to_pon_approval_status(p_ame_approval_status IN VARCHAR2) RETURN NUMBER;
73 FUNCTION parse_number_field(p_string VARCHAR2) RETURN NUMBER;
74
75 -- procedures for retreiving information for users and persons
76 FUNCTION get_display_name_for_user(p_user_id IN NUMBER) RETURN VARCHAR2;
77 FUNCTION get_display_name_for_user(p_user_name IN VARCHAR2) RETURN VARCHAR2;
78 FUNCTION get_display_name_for_person(p_person_id IN NUMBER) RETURN VARCHAR2;
79 PROCEDURE get_employee_info_for_user(p_user_id IN NUMBER, p_employee OUT NOCOPY employeeRecord);
80 PROCEDURE get_employee_info_for_user(p_user_name IN VARCHAR2, p_employee OUT NOCOPY employeeRecord);
81 PROCEDURE get_employee_info_for_person(p_person_id IN NUMBER, p_employee OUT NOCOPY employeeRecord);
82
83 -- function that returns true if two approvers match
84 FUNCTION approvers_match(p_approver1 ame_util.approverRecord,
85 p_approver2 ame_util.approverRecord) RETURN BOOLEAN;
86
87 --procedure used for error reporting
88 PROCEDURE trim_error_code(p_error_code IN NUMBER,
89 p_error_message_in IN VARCHAR2,
90 p_error_message_out OUT NOCOPY VARCHAR2);
91
92 -- IMPLEMENTATIONS
93 /*
94 Identifies an award as an OAM transaction.
95 */
96 PROCEDURE setup_oam_transaction(p_auction_header_id IN NUMBER,
97 p_transaction_id IN VARCHAR2,
98 p_user_id IN NUMBER,
99 p_last_update_date OUT NOCOPY DATE,
100 p_error_message OUT NOCOPY VARCHAR2) IS
101 l_award_approval_status VARCHAR2(30);
102 l_last_update_date DATE;
103
104 l_error_code NUMBER;
105
106 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
107 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
108 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
109 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.SETUP_OAM_TRANSACTION';
110 BEGIN
111
112 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'marking award ' || p_auction_header_id || ' as OAM transaction ' || p_transaction_id);
113
114 SELECT award_approval_status
115 INTO l_award_approval_status
116 FROM pon_auction_headers_all
117 WHERE auction_header_id = p_auction_header_id;
118
119 -- check if a transaction already exists and has been submitted for approval
120 IF l_award_approval_status = 'INPROCESS' THEN
121 log_string(l_exception_log_level, l_current_log_level, l_module_name, 'award ' || p_auction_header_id || ' is already in the approval process');
122 raise_application_error(-20001, PON_AUCTION_PKG.getMessage('PON_CANNOT_SBMT_AWARD_APPROVAL'));
123 END IF;
124
125 l_last_update_date := SYSDATE;
126
127 -- update pon_auction_headers_all
128 -- award_appr_ame_trans_id column with transaction id
129 -- award_appr_ame_txn_date column with SYSDATE
130 UPDATE pon_auction_headers_all
131 SET
132 award_appr_ame_trans_id = p_transaction_id,
133 award_appr_ame_txn_date = l_last_update_date,
134 last_updated_by = p_user_id,
135 last_update_date = l_last_update_date
136 WHERE auction_header_id = p_auction_header_id;
137
138 p_last_update_date := l_last_update_date;
139 p_error_message := NULL;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 -- reset output variables
144 p_error_message := NULL;
145
146 l_error_code := SQLCODE;
147 IF l_error_code <= -20000 AND l_error_code >= -20999 THEN
148 trim_error_code(l_error_code, SQLERRM, p_error_message);
149 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
150 ELSE
151 -- raise all Oracle-predefined and user-defined exceptions
152 RAISE;
153 END IF;
154 END setup_oam_transaction;
155
156 /*
157 Unidentifies an award as an OAM transaction. Does nothing if the award does not require approval.
158 */
159 PROCEDURE clear_oam_transaction(p_auction_header_id IN NUMBER,
160 p_user_id IN NUMBER) IS
161 BEGIN
162
163 -- update pon_auction_headers_all
164 -- award_approval_status with 'REQUIRED',
165 -- award_appr_ame_trans_id column with NULL
166 -- award_appr_ame_trans_prev_id with NULL
167 -- award_appr_ame_txn_date column with NULL
168 -- wf_award_approval_item_key column with NULL
169 UPDATE pon_auction_headers_all
170 SET
171 award_approval_status = 'REQUIRED',
172 award_appr_ame_trans_id = NULL,
173 award_appr_ame_trans_prev_id = NULL,
174 award_appr_ame_txn_date = NULL,
175 wf_award_approval_item_key = NULL,
176 last_updated_by = p_user_id,
177 last_update_date = SYSDATE
178 WHERE
179 auction_header_id = p_auction_header_id
180 AND NVL(award_approval_flag, 'Y') = 'Y';
181
182 END clear_oam_transaction;
183
184 /*
185 Checks whether or not an approver list can be modified by a session
186 given the database state of the transaction associated with the approver list.
187 */
188 PROCEDURE validate_transaction(p_auction_header_id IN NUMBER,
189 p_last_update_date IN DATE,
190 p_lock_transaction IN BOOLEAN) IS
191 l_last_update_date DATE;
192 l_award_approval_status VARCHAR2(30);
193
194 lock_not_acquired EXCEPTION;
195 transaction_modified EXCEPTION;
196 already_in_approval EXCEPTION;
197
198 PRAGMA EXCEPTION_INIT(lock_not_acquired, -54);
199
200 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
201 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
202 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
203 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.VALIDATE_TRANSACTION';
204 BEGIN
205
206 -- lock the transaction based on the p_lock_transaction parameter
207 IF p_lock_transaction THEN
208 SELECT last_update_date, award_approval_status
209 INTO l_last_update_date, l_award_approval_status
210 FROM pon_auction_headers_all
211 WHERE auction_header_id = p_auction_header_id
212 FOR UPDATE NOWAIT;
213 ELSE
214 SELECT last_update_date, award_approval_status
215 INTO l_last_update_date, l_award_approval_status
216 FROM pon_auction_headers_all
217 WHERE auction_header_id = p_auction_header_id;
218 END IF;
219
220 -- check if the transaction has already been modified
221 IF NVL(l_last_update_date, SYSDATE) <> NVL(p_last_update_date, SYSDATE) THEN
222 RAISE transaction_modified;
223 END IF;
224
225 -- check if the transaction has been submitted for approval
226 IF l_award_approval_status = 'INPROCESS' THEN
227 RAISE already_in_approval;
228 END IF;
229
230 EXCEPTION
231 WHEN lock_not_acquired THEN
232 -- a lock could not be acquired on the transaction; another user may be submitting it for approval
233 log_string(l_exception_log_level, l_current_log_level, l_module_name, 'a lock could not be acquired on award ' || p_auction_header_id);
234 raise_application_error(-20001, PON_AUCTION_PKG.getMessage('PON_CANNOT_SBMT_AWARD_APPROVAL'));
235 WHEN transaction_modified THEN
236 -- the transaction has been modified by another user session
237 log_string(l_exception_log_level, l_current_log_level, l_module_name, 'award ' || p_auction_header_id || ' has been modified by another user session');
238 raise_application_error(-20001, PON_AUCTION_PKG.getMessage('PON_CANNOT_SBMT_AWARD_APPROVAL'));
239 WHEN already_in_approval THEN
240 -- the transaction is in the approval process
241 log_string(l_exception_log_level, l_current_log_level, l_module_name, 'award ' || p_auction_header_id || ' is already in the approval process');
242 raise_application_error(-20001, PON_AUCTION_PKG.getMessage('PON_CANNOT_SBMT_AWARD_APPROVAL'));
243 END validate_transaction;
244
245 /*
246 Adds a row into the PON_AUCTION_HISTORY table.
247 */
248 PROCEDURE add_action_history(p_auction_header_id IN NUMBER,
249 p_transaction_id IN VARCHAR2,
250 p_action_type IN VARCHAR2,
251 p_notes IN VARCHAR2,
252 p_action_date IN DATE,
253 p_user_id IN NUMBER,
254 p_user_name IN VARCHAR2) IS
255 l_action_sequence_number NUMBER;
256 BEGIN
257
258 -- determine the current action sequence number for the award
259 BEGIN
260 SELECT MAX(sequence_num)
261 INTO l_action_sequence_number
262 FROM pon_action_history
263 WHERE
264 object_id = p_auction_header_id
265 AND object_type_code = AWARD_OBJECT_TYPE;
266 EXCEPTION
267 WHEN no_data_found THEN
268 NULL;
269 END;
270
271 -- increment the sequence number
272 -- if the current action sequence number is null or could not be determined, set it to 0
273 IF l_action_sequence_number IS NULL THEN
274 l_action_sequence_number := 0;
275 END IF;
276
277 -- create the action entry, incrementing the sequence number
278 INSERT INTO pon_action_history(
279 object_id,
280 object_id2,
281 object_type_code,
282 sequence_num,
283 action_type,
284 action_date,
285 action_user_id,
286 action_note)
287 VALUES (
288 p_auction_header_id,
289 TO_NUMBER(p_transaction_id),
290 AWARD_OBJECT_TYPE,
291 l_action_sequence_number + 1,
292 p_action_type,
293 p_action_date,
294 p_user_id,
295 p_notes);
296
297 END add_action_history;
298
299 /*
300 Updates an existing row in the PON_AUCTION_HISTORY table.
301
302 Only AWARD_APPROVAL_PENDING action entries can be updated.
303 */
304 PROCEDURE update_action_history(p_auction_header_id IN NUMBER,
305 p_transaction_id IN VARCHAR2,
306 p_action_type IN VARCHAR2,
307 p_notes IN VARCHAR2,
308 p_action_date IN DATE,
309 p_user_id IN NUMBER) IS
310 BEGIN
311
312 UPDATE pon_action_history
313 SET
314 action_type = p_action_type,
315 action_date = p_action_date,
316 action_note = p_notes
317 WHERE
318 object_id = p_auction_header_id
319 AND object_id2 = TO_NUMBER(p_transaction_id)
320 AND action_user_id = p_user_id
321 AND action_type = 'AWARD_APPROVAL_PENDING'
322 AND object_type_code = AWARD_OBJECT_TYPE;
323
324 END update_action_history;
325
326 /*
327 Sets the main user-independent attributes for a workflow item.
328 */
329 PROCEDURE set_core_attributes(p_item_type IN VARCHAR2,
330 p_item_key IN VARCHAR2,
331 p_auction_header_id IN NUMBER,
332 p_note_to_approvers IN VARCHAR2,
333 p_first_authority_approver_id IN NUMBER ) IS
334 l_trading_partner_contact_name VARCHAR2(255);
335 l_trading_partner_name VARCHAR2(255);
336 l_transaction_id VARCHAR2(80);
337 l_auction_title VARCHAR2(80);
338 l_document_number VARCHAR2(240);
339 l_open_bidding_date DATE;
340 l_close_bidding_date DATE;
341 l_award_by_date DATE;
342 l_doctype_group_name VARCHAR2(50);
343 l_msg_suffix VARCHAR2(10);
344 l_preview_date DATE;
345 BEGIN
346
347 -- retrieve the necessary negotiation information
348 SELECT
349 auc.trading_partner_contact_name,
350 auc.trading_partner_name,
351 auc.award_appr_ame_trans_id,
352 auc.auction_title,
353 auc.document_number,
354 auc.open_bidding_date,
355 auc.close_bidding_date,
356 auc.award_by_date,
357 dt.doctype_group_name,
358 auc.view_by_date
359 INTO
360 l_trading_partner_contact_name,
361 l_trading_partner_name,
362 l_transaction_id,
363 l_auction_title,
364 l_document_number,
365 l_open_bidding_date,
366 l_close_bidding_date,
367 l_award_by_date,
368 l_doctype_group_name,
369 l_preview_date
370 FROM
371 pon_auction_headers_all auc,
372 pon_auc_doctypes dt
373 WHERE
374 dt.doctype_id= auc.doctype_id
375 AND auc.auction_header_id = p_auction_header_id;
376
377 l_msg_suffix := PON_AUCTION_PKG.get_message_suffix(l_doctype_group_name);
378
379 --bug 9555176
380 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
381 itemkey => p_item_key,
382 aname => 'PREPARER_RESP_ID',
383 avalue => fnd_global.RESP_ID);
384
385 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
386 itemkey => p_item_key,
387 aname => 'PREPARER_APPL_ID',
388 avalue => fnd_global.RESP_APPL_ID);
389
390 --9555176 End
391
392
393 -- set standard notification header attributes
394 PON_WF_UTL_PKG.set_hdr_attributes(p_item_type,
395 p_item_key,
396 l_trading_partner_name,
397 l_auction_title,
398 l_document_number,
399 l_trading_partner_contact_name);
400
401 -- set other core attributes
402 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
403 itemkey => p_item_key,
404 aname => 'AUCTION_HEADER_ID',
405 avalue => p_auction_header_id);
406
407 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
408 itemkey => p_item_key,
409 aname => 'FIRST_AUTHORITY_APPROVER_ID',
410 avalue => p_first_authority_approver_id);
411
412 wf_engine.SetItemAttrText(itemtype => p_item_type,
413 itemkey => p_item_key,
414 aname => 'AME_TRANSACTION_ID',
415 avalue => l_transaction_id);
416
417 wf_engine.SetItemAttrText(itemtype => p_item_type,
418 itemkey => p_item_key,
419 aname => 'NOTE_TO_APPROVERS',
420 avalue => PON_AUCTION_PKG.replaceHtmlChars(p_note_to_approvers));
421
422 wf_engine.SetItemAttrText(itemtype => p_item_type,
423 itemkey => p_item_key,
424 aname => 'AWARD_SUMMARY_URL',
425 avalue => PON_WF_UTL_PKG.get_dest_page_url('PON_AWARD_SUMM', 'BUYER'));
426
427 wf_engine.SetItemAttrText(itemtype => p_item_type,
428 itemkey => p_item_key,
429 aname => 'MESSAGE_SUFFIX',
430 avalue => l_msg_suffix);
431
432 wf_engine.SetItemAttrDate(itemtype => p_item_type,
433 itemkey => p_item_key,
434 aname => 'OPEN_BIDDING_DATE',
435 avalue => l_open_bidding_date);
436
437 wf_engine.SetItemAttrDate(itemtype => p_item_type,
438 itemkey => p_item_key,
439 aname => 'CLOSE_BIDDING_DATE',
440 avalue => l_close_bidding_date);
441
442 wf_engine.SetItemAttrDate(itemtype => p_item_type,
443 itemkey => p_item_key,
444 aname => 'AWARD_BY_DATE',
445 avalue => l_award_by_date);
446
447 wf_engine.SetItemAttrDate (itemtype => p_item_type,
448 itemkey => p_item_key,
449 aname => 'PREVIEW_DATE',
450 avalue => l_preview_date);
451
452 wf_engine.SetItemAttrText(itemtype => p_item_type,
453 itemkey => p_item_key,
454 aname => 'ORIGIN_USER_NAME',
455 avalue => fnd_global.user_name);
456
457 END set_core_attributes;
458
459 /*
460 Sets the main user-dependent notification attributes for a workflow item.
461
462 NOTE:
463 Among other attributes, this procedure sets:
464 1. the particular notification subject attribute, based on p_notification_type.
465 2. the APPROVAL_DATE_TZ attribute if p_set_approval_date is true
466 3. the REJECTION_DATE_TZ attribute if p_set_rejection_date is true
467 */
468 PROCEDURE set_common_user_attributes(p_item_type IN VARCHAR2,
469 p_item_key IN VARCHAR2,
470 p_user_name IN VARCHAR2,
471 p_notification_type IN INTEGER,
472 p_set_approval_date BOOLEAN,
473 p_set_rejection_date BOOLEAN) IS
474 l_language_code VARCHAR2(5);
475 l_msg_suffix VARCHAR2(10);
476 l_auction_title VARCHAR2(80);
477 l_document_number VARCHAR2(240);
478 l_open_bidding_date DATE;
479 l_open_bidding_date_tz DATE;
480 l_close_bidding_date DATE;
481 l_close_bidding_date_tz DATE;
482 l_award_by_date DATE;
483 l_award_by_date_tz DATE;
484 l_approval_date DATE;
485 l_approval_date_tz DATE;
486 l_rejection_date DATE;
487 l_rejection_date_tz DATE;
488 l_preview_date DATE;
489 l_preview_date_tz DATE;
490 l_user_timezone VARCHAR2(80);
491 l_server_timezone VARCHAR2(80);
492 l_user_timezone_desc VARCHAR2(240);
493 BEGIN
494
495 -- set the db session language
496 PON_PROFILE_UTIL_PKG.get_wf_language(p_user_name, l_language_code);
497 PON_AUCTION_PKG.set_session_language(null, l_language_code);
498
499 l_msg_suffix := wf_engine.getItemAttrText(itemtype => p_item_type,
500 itemkey => p_item_key,
501 aname => 'MESSAGE_SUFFIX');
502
503 l_auction_title := wf_engine.getItemAttrText(itemtype => p_item_type,
504 itemkey => p_item_key,
505 aname => 'AUCTION_TITLE');
506
507 l_document_number := wf_engine.getItemAttrText(itemtype => p_item_type,
508 itemkey => p_item_key,
509 aname => 'DOC_NUMBER');
510
511 -- set the notification subject
512 IF p_notification_type = APPROVAL_REQUEST THEN
513 -- if the notification type is APPROVAL_REQUEST
514 -- set the notification subject for the initial approval request notification
515 -- as well as the subject for the reminder notification
516 wf_engine.SetItemAttrText(itemtype => p_item_type,
517 itemkey => p_item_key,
518 aname => 'APPROVAL_REQUEST_SUBJECT',
519 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_AWARD_APPROVAL_1',
520 l_msg_suffix,
521 'DOC_NUMBER',
522 l_document_number,
523 'DOC_TITLE',
524 l_auction_title));
525 wf_engine.SetItemAttrText(itemtype => p_item_type,
526 itemkey => p_item_key,
527 aname => 'REQUEST_REMINDER_SUBJECT',
528 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_AWARD_APPROVAL_2',
529 l_msg_suffix,
530 'DOC_NUMBER',
531 l_document_number,
532 'DOC_TITLE',
533 l_auction_title));
534 ELSIF p_notification_type = AWARD_APPROVED THEN
535 wf_engine.SetItemAttrText(itemtype => p_item_type,
536 itemkey => p_item_key,
537 aname => 'APPROVAL_SUBJECT',
538 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_AWARD_APPROVED_1',
539 l_msg_suffix,
540 'DOC_NUMBER',
541 l_document_number,
542 'DOC_TITLE',
543 l_auction_title));
544 ELSIF p_notification_type = AWARD_REJECTED THEN
545 wf_engine.SetItemAttrText(itemtype => p_item_type,
546 itemkey => p_item_key,
547 aname => 'REJECTION_SUBJECT',
548 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_AWARD_REJECTED_1',
549 l_msg_suffix,
550 'DOC_NUMBER',
551 l_document_number,
552 'DOC_TITLE',
553 l_auction_title));
554 ELSIF p_notification_type = ERROR THEN
555 wf_engine.SetItemAttrText(itemtype => p_item_type,
556 itemkey => p_item_key,
557 aname => 'ERROR_SUBJECT',
558 avalue => PON_AUCTION_PKG.getMessage('PON_AWARD_REJECTED_ERROR_1',
559 l_msg_suffix,
560 'DOC_NUMBER',
561 l_document_number,
562 'DOC_TITLE',
563 l_auction_title));
564 ELSIF p_notification_type = ERROR_FOR_ADMIN THEN
565 wf_engine.SetItemAttrText(itemtype => p_item_type,
566 itemkey => p_item_key,
567 aname => 'ERROR_SUBJECT_FOR_ADMIN',
568 avalue => PON_AUCTION_PKG.getMessage('PON_AWARD_REJECTED_ERROR_2',
569 l_msg_suffix,
570 'DOC_NUMBER',
571 l_document_number,
572 'DOC_TITLE',
573 l_auction_title));
574 END IF;
575
576 -- convert the open bidding date, close bidding date, award by date, approval date, and rejection_date
577 -- to dates in the user's timezone
578 l_user_timezone := PON_AUCTION_PKG.get_time_zone(p_user_name);
579 l_server_timezone := PON_AUCTION_PKG.get_oex_time_zone;
580
581 l_open_bidding_date := wf_engine.getItemAttrDate(itemtype => p_item_type,
582 itemkey => p_item_key,
583 aname => 'OPEN_BIDDING_DATE');
584
585 l_close_bidding_date := wf_engine.getItemAttrDate(itemtype => p_item_type,
586 itemkey => p_item_key,
587 aname => 'CLOSE_BIDDING_DATE');
588
589 l_award_by_date := wf_engine.getItemAttrDate(itemtype => p_item_type,
590 itemkey => p_item_key,
591 aname => 'AWARD_BY_DATE');
592
593 IF p_set_approval_date THEN
594 l_approval_date := wf_engine.getItemAttrDate(itemtype => p_item_type,
595 itemkey => p_item_key,
596 aname => 'APPROVAL_DATE');
597 END IF;
598
599 IF p_set_rejection_date THEN
600 l_rejection_date := wf_engine.getItemAttrDate(itemtype => p_item_type,
601 itemkey => p_item_key,
602 aname => 'REJECTION_DATE');
603 END IF;
604
605 l_preview_date := wf_engine.getItemAttrDate(itemtype => p_item_type,
606 itemkey => p_item_key,
607 aname => 'PREVIEW_DATE');
608
609 IF PON_OEX_TIMEZONE_PKG.valid_zone(l_user_timezone) = 1 THEN
610 l_open_bidding_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(l_open_bidding_date, l_server_timezone, l_user_timezone);
611 l_close_bidding_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(l_close_bidding_date, l_server_timezone, l_user_timezone);
612
613 -- the award by date may be null
614 IF l_award_by_date IS NOT NULL THEN
615 l_award_by_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(l_award_by_date, l_server_timezone, l_user_timezone);
616 ELSE
617 l_award_by_date_tz := NULL;
618 END IF;
619
620 IF p_set_approval_date THEN
621 l_approval_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(l_approval_date, l_server_timezone, l_user_timezone);
622 END IF;
623
624 IF p_set_rejection_date THEN
625 l_rejection_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(l_rejection_date, l_server_timezone, l_user_timezone);
626 END IF;
627
628 IF l_preview_date IS NOT NULL THEN
629 l_preview_date_tz := PON_OEX_TIMEZONE_PKG.convert_time(l_preview_date, l_server_timezone, l_user_timezone);
630 END IF;
631 ELSE
632 l_user_timezone := l_server_timezone;
633 l_open_bidding_date_tz := l_open_bidding_date;
634 l_close_bidding_date_tz := l_close_bidding_date;
635 l_award_by_date_tz := l_award_by_date;
636 l_preview_date_tz := l_preview_date;
637
638 IF p_set_approval_date THEN
639 l_approval_date_tz := l_approval_date;
640 END IF;
641
642 IF p_set_rejection_date THEN
643 l_rejection_date_tz := l_rejection_date;
644 END IF;
645 END IF;
646
647 l_user_timezone_desc := PON_AUCTION_PKG.get_timezone_description(l_user_timezone, l_language_code);
648
649 wf_engine.setItemAttrText(itemtype => p_item_type,
650 itemkey => p_item_key,
651 aname => 'USER_TIMEZONE',
652 avalue => l_user_timezone_desc);
653
654 wf_engine.setItemAttrDate(itemtype => p_item_type,
655 itemkey => p_item_key,
656 aname => 'OPEN_BIDDING_DATE_TZ',
657 avalue => l_open_bidding_date_tz);
658
659 wf_engine.setItemAttrDate(itemtype => p_item_type,
660 itemkey => p_item_key,
661 aname => 'CLOSE_BIDDING_DATE_TZ',
662 avalue => l_close_bidding_date_tz);
663
664 wf_engine.setItemAttrDate(itemtype => p_item_type,
665 itemkey => p_item_key,
666 aname => 'AWARD_BY_DATE_TZ',
667 avalue => l_award_by_date_tz);
668
669 -- if the award by date is not null, the timezone attribute associated with the date
670 -- must have the same value as the user's timezone attribute
671 -- otherwise, then the timezone attribute associated with the date must be null
672 IF l_award_by_date_tz IS NOT NULL THEN
673 wf_engine.setItemAttrText(itemtype => p_item_type,
674 itemkey => p_item_key,
675 aname => 'USER_TIMEZONE_AWARD_BY_DATE',
676 avalue => l_user_timezone_desc);
677 ELSE
678 wf_engine.setItemAttrText(itemtype => p_item_type,
679 itemkey => p_item_key,
680 aname => 'USER_TIMEZONE_AWARD_BY_DATE',
681 avalue => NULL);
682 END IF;
683
684 IF p_set_approval_date THEN
685 wf_engine.setItemAttrDate(itemtype => p_item_type,
686 itemkey => p_item_key,
687 aname => 'APPROVAL_DATE_TZ',
688 avalue => l_approval_date_tz);
689 END IF;
690
691 IF p_set_rejection_date THEN
692 wf_engine.setItemAttrDate(itemtype => p_item_type,
693 itemkey => p_item_key,
694 aname => 'REJECTION_DATE_TZ',
695 avalue => l_rejection_date_tz);
696 END IF;
697
698 IF l_preview_date_tz IS NOT NULL THEN
699 wf_engine.SetItemAttrText (itemtype => p_item_type,
700 itemkey => p_item_key,
701 aname => 'TP_TIME_ZONE1',
702 avalue => l_user_timezone_desc);
703
704 wf_engine.SetItemAttrText (itemtype => p_item_type,
705 itemkey => p_item_key,
706 aname => 'PREVIEW_DATE_NOTSPECIFIED',
707 avalue => null);
708 ELSE
709 wf_engine.SetItemAttrText (itemtype => p_item_type,
710 itemkey => p_item_key,
711 aname => 'TP_TIME_ZONE1',
712 avalue => null);
713
714 wf_engine.SetItemAttrText (itemtype => p_item_type,
715 itemkey => p_item_key,
716 aname => 'PREVIEW_DATE_NOTSPECIFIED',
717 avalue => PON_AUCTION_PKG.getMessage('PON_AUC_PREVIEW_DATE_NOTSPEC'));
718 END IF;
719
720 END set_common_user_attributes;
721
722 /*
723 Sets the main user-dependent notification attributes for a workflow item.
724
725 NOTE:
726 Among other attributes, this procedure sets:
727 1. the particular notification subject attribute, based on p_notification_type.
728 */
729 PROCEDURE set_common_user_attributes(p_item_type IN VARCHAR2,
730 p_item_key IN VARCHAR2,
731 p_user_name IN VARCHAR2,
732 p_notification_type IN INTEGER) IS
733 BEGIN
734
735 set_common_user_attributes(p_item_type, p_item_key, p_user_name, p_notification_type, FALSE, FALSE);
736
737 END set_common_user_attributes;
738
739 /*
740 Sets user-dependent attributes for the Award Approved notification.
741 */
742 PROCEDURE set_award_approved_attributes(p_item_type IN VARCHAR2,
743 p_item_key IN VARCHAR2) IS
744 l_tp_contact_user_name VARCHAR2(100);
745 BEGIN
746
747 l_tp_contact_user_name := wf_engine.getItemAttrText(itemtype => p_item_type,
748 itemkey => p_item_key,
749 aname => 'PREPARER_TP_CONTACT_NAME');
750
751 set_common_user_attributes(p_item_type,
752 p_item_key,
753 l_tp_contact_user_name,
754 AWARD_APPROVED,
755 TRUE,
756 FALSE);
757
758 END set_award_approved_attributes;
759
760 /*
761 Sets user-dependent attributes for the Award Rejected notification.
762 */
763 PROCEDURE set_award_rejected_attributes(p_item_type IN VARCHAR2,
764 p_item_key IN VARCHAR2) IS
765 l_tp_contact_user_name VARCHAR2(100);
766 BEGIN
767
768 l_tp_contact_user_name := wf_engine.getItemAttrText(itemtype => p_item_type,
769 itemkey => p_item_key,
770 aname => 'PREPARER_TP_CONTACT_NAME');
771
772 set_common_user_attributes(p_item_type,
773 p_item_key,
774 l_tp_contact_user_name,
775 AWARD_REJECTED,
776 FALSE,
777 TRUE);
778
779 END set_award_rejected_attributes;
780
781 /*
782 Sets user-dependent attributes for the Notify Approver notification.
783 */
784 PROCEDURE set_notify_approver_attributes(p_item_type IN VARCHAR2,
785 p_item_key IN VARCHAR2) IS
786 l_approver_user_name VARCHAR2(100);
787 BEGIN
788
789 l_approver_user_name := wf_engine.getItemAttrText(itemtype => p_item_type,
790 itemkey => p_item_key,
791 aname => 'APPROVER_USER');
792
793 -- reset notification's Respond attributes
794 wf_engine.setItemAttrText(itemtype => p_item_type,
795 itemkey => p_item_key,
796 aname => 'APPROVER_FORWARDEE_USER',
797 avalue => NULL);
798
799 wf_engine.setItemAttrText(itemtype => p_item_type,
800 itemkey => p_item_key,
801 aname => 'NOTE_TO_BUYER',
802 avalue => NULL);
803
804 -- reset approval-related attributes
805 wf_engine.SetItemAttrDate(itemtype => p_item_type,
806 itemkey => p_item_key,
807 aname => 'APPROVAL_DATE',
808 avalue => NULL);
809
810 wf_engine.SetItemAttrDate(itemtype => p_item_type,
811 itemkey => p_item_key,
812 aname => 'REJECTION_DATE',
813 avalue => NULL);
814
815 set_common_user_attributes(p_item_type,
816 p_item_key,
817 l_approver_user_name,
818 APPROVAL_REQUEST);
819
820 END set_notify_approver_attributes;
821
822 /*
823 Called by workflow activity to perform any special tasks
824 before executing the main part of the workflow process.
825 */
826 PROCEDURE pre_approval(itemtype IN VARCHAR2,
827 itemkey IN VARCHAR2,
828 actid IN NUMBER,
829 funcmode IN VARCHAR2,
830 resultout OUT NOCOPY VARCHAR2) IS
831 l_auction_header_id NUMBER;
832 l_transaction_id VARCHAR2(80);
833 l_tp_contact_user_id NUMBER;
834 l_tp_contact_user_name VARCHAR2(100);
835 BEGIN
836
837 l_auction_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
838 itemkey => itemkey,
839 aname => 'AUCTION_HEADER_ID');
840
841 l_transaction_id := wf_engine.GetItemAttrText(itemtype => itemtype,
842 itemkey => itemkey,
843 aname => 'AME_TRANSACTION_ID');
844
845 l_tp_contact_user_name := wf_engine.GetItemAttrText(itemtype => itemtype,
846 itemkey => itemkey,
847 aname => 'PREPARER_TP_CONTACT_NAME');
848
849 SELECT user_id
850 INTO l_tp_contact_user_id
851 FROM fnd_user
852 WHERE user_name = l_tp_contact_user_name;
853
854 -- update previous transaction id column with current transaction id
855 UPDATE pon_auction_headers_all
856 SET
857 award_appr_ame_trans_prev_id = l_transaction_id,
858 last_updated_by = l_tp_contact_user_id,
859 last_update_date = SYSDATE
860 WHERE auction_header_id = l_auction_header_id;
861
862 END pre_approval;
863
864 /*
865 Called by workflow activity to perform any special tasks
866 after executing the main part of the workflow process.
867 */
868 PROCEDURE post_approval(itemtype IN VARCHAR2,
869 itemkey IN VARCHAR2,
870 actid IN NUMBER,
871 funcmode IN VARCHAR2,
872 resultout OUT NOCOPY VARCHAR2) IS
873 BEGIN
874
875 NULL;
876
877 END post_approval;
878
879 /*
880 Sets the current approver to the next approver, if a next approver exists.
881 */
882 PROCEDURE get_next_approver(itemtype IN VARCHAR2,
883 itemkey IN VARCHAR2,
884 actid IN NUMBER,
885 funcmode IN VARCHAR2,
886 resultout OUT NOCOPY VARCHAR2) IS
887 l_auction_header_id NUMBER;
888 l_transaction_id VARCHAR2(80);
889
890 l_next_approver_employee employeeRecord;
891 l_next_approver_list ame_util.approversTable2;
892 l_next_approver ame_util.approverRecord2;
893 l_process_out varchar2(2);
894 l_person_id NUMBER;
895
896 l_next_approver_name VARCHAR2(240);
897 l_next_approver_string VARCHAR2(240);
898 l_success_flag BOOLEAN;
899 l_source_type_out VARCHAR2(50);
900 l_idList ame_util.idList;
901
902 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
903 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
904 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
905 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.GET_NEXT_APPROVER';
906 BEGIN
907
908 l_transaction_id := wf_engine.GetItemAttrText(itemtype => itemtype,
909 itemkey => itemkey,
910 aname => 'AME_TRANSACTION_ID');
911 l_success_flag := TRUE;
912
913 BEGIN
914 -- get the next approver from OAM
915 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.getNextApprovers4 on workflow item key ' || itemkey);
916
917 ame_api2.getNextApprovers4( applicationIdIn => APPLICATION_ID,
918 transactionIdIn => l_transaction_id,
919 transactionTypeIn => AWARD_TRANSACTION_TYPE,
920 approvalProcessCompleteYNOut => l_process_out,
921 nextApproversOut => l_next_approver_list);
922
923 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.getNextApprovers4 returns ' || fnd_global.newline || format_ame_approver_list(l_next_approver_list));
924
925 EXCEPTION
926 WHEN OTHERS THEN
927 resultout := 'OAM_API_ERROR';
928 l_success_flag := FALSE;
929
930 -- if the OAM API call raises an exception,
931 -- set the Application Error and OAM Error workflow attributes
932 -- this message is internal and can be seen only by the OAM administrator
933 wf_engine.SetItemAttrText(itemtype => itemtype,
934 itemkey => itemkey,
935 aname => 'OAM_ERROR',
936 avalue => SQLERRM);
937
938 -- this message can be seen by any buyer
939 wf_engine.SetItemAttrText(itemtype => itemtype,
940 itemkey => itemkey,
941 aname => 'APPLICATION_ERROR',
942 avalue => PON_AUCTION_PKG.getMessage('PON_OAM_ERROR_OCCURRED'));
943
944 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
945 END;
946
947 -- Check whether Parallel Approval is enabled in AME. If yes report an error.
948 IF l_next_approver_list.COUNT > 1 THEN
949 resultout := 'OAM_API_ERROR';
950 l_success_flag := FALSE;
951
952 -- this message can be seen only by admin.
953 wf_engine.SetItemAttrText(itemtype => itemtype,
954 itemkey => itemkey,
955 aname => 'OAM_ERROR',
956 avalue => PON_AUCTION_PKG.getMessage('PON_AME_PARALLEL_NOT_SUPPORTED'));
957
958 -- this message can be seen by any buyer.
959 wf_engine.SetItemAttrText(itemtype => itemtype,
960 itemkey => itemkey,
961 aname => 'APPLICATION_ERROR',
962 avalue => PON_AUCTION_PKG.getMessage('PON_AME_PARALLEL_NOT_SUPPORTED'));
963
964 log_string(l_exception_log_level, l_current_log_level, l_module_name, PON_AUCTION_PKG.getMessage('PON_AME_PARALLEL_NOT_SUPPORTED'));
965
966 END IF;
967
968
969 -- if the OAM API call returned successfully
970 IF l_success_flag THEN
971
972 IF l_process_out = 'Y' THEN
973 resultout := 'NO_NEXT_APPROVER';
974 ELSE
975
976 l_next_approver := l_next_approver_list(1); -- get the first approver from the approvers list.
977
978 IF l_next_approver.orig_system = 'POS' THEN -- Position Hierarchy setup in AME.
979
980 BEGIN
981 l_person_id := NULL;
982 ame_api3.parseApproverSource( approverSourceIn => l_next_approver.source,
983 sourceDescriptionOut => l_source_type_out,
984 ruleIdListOut => l_idList);
985
986 IF( l_next_approver.api_insertion = ame_util.apiAuthorityInsertion AND
987 l_source_type_out <> ame_util.forwardeeSource ) THEN
988 l_person_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
989 itemkey => itemkey,
990 aname => 'FIRST_AUTHORITY_APPROVER_ID');
991 END IF;
992 IF( l_person_id IS NULL OR l_person_id = -1 ) THEN
993 SELECT person_id into l_person_id FROM (
994 SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
995 WHERE asg.position_id = l_next_approver.orig_system_id and trunc(sysdate) between person.effective_start_date
996 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
997 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
998 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
999 and asg.assignment_status_type_id not in (
1000 SELECT assignment_status_type_id FROM per_assignment_status_types
1001 WHERE per_system_status = 'TERM_ASSIGN'
1002 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
1003 ) where rownum = 1;
1004 END IF;
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 l_person_id := NULL;
1008 END;
1009
1010 IF l_person_id IS NULL THEN -- There are no users associated to this position. Raise an error.
1011
1012 resultout := 'OAM_API_ERROR';
1013 l_success_flag := FALSE;
1014
1015 -- this message can be seen only by admin.
1016 wf_engine.SetItemAttrText(itemtype => itemtype,
1017 itemkey => itemkey,
1018 aname => 'OAM_ERROR',
1019 avalue => PON_AUCTION_PKG.getMessage('PON_NO_PERSON_FOR_POSITION', '', 'POSITION_NAME', l_next_approver.display_name ));
1020
1021 -- this message can be seen by any buyer.
1022 wf_engine.SetItemAttrText(itemtype => itemtype,
1023 itemkey => itemkey,
1024 aname => 'APPLICATION_ERROR',
1025 avalue => PON_AUCTION_PKG.getMessage('PON_NO_PERSON_FOR_POSITION', '', 'POSITION_NAME', l_next_approver.display_name ));
1026
1027 log_string(l_exception_log_level, l_current_log_level, l_module_name, PON_AUCTION_PKG.getMessage('PON_NO_PERSON_FOR_POSITION', '', 'POSITION_NAME', l_next_approver.display_name ));
1028 RETURN;
1029 ELSE
1030 get_employee_info_for_person(l_person_id, l_next_approver_employee);
1031 END IF;
1032
1033 ELSIF l_next_approver.orig_system = 'PER' THEN -- Emp-Supervisor Hierarchy setup in AME.
1034
1035 get_employee_info_for_person(l_next_approver.orig_system_id, l_next_approver_employee);
1036
1037 ELSE -- FND USER setup in AME.
1038
1039 get_employee_info_for_user(l_next_approver.orig_system_id, l_next_approver_employee);
1040
1041 END IF;
1042
1043 -- if next approver is valid
1044 IF l_next_approver_employee.user_id IS NOT NULL AND l_next_approver_employee.person_id IS NOT NULL THEN
1045
1046 -- set the approver username attribute
1047 wf_engine.SetItemAttrText(itemtype => itemtype,
1048 itemkey => itemkey,
1049 aname => 'APPROVER_USER',
1050 avalue => l_next_approver_employee.user_name);
1051
1052 l_next_approver_string := format_ame_approver(l_next_approver);
1053
1054 -- set the approver record string atttribute
1055 wf_engine.SetItemAttrText(itemtype => itemtype,
1056 itemkey => itemkey,
1057 aname => 'APPROVER_STRING',
1058 avalue => l_next_approver_string);
1059
1060 -- set attributes specific to approval request notification
1061 set_notify_approver_attributes(itemtype, itemkey);
1062
1063 -- get the auction header id workflow attribute
1064 l_auction_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1065 itemkey => itemkey,
1066 aname => 'AUCTION_HEADER_ID');
1067
1068 -- record that approval response is pending for this approver
1069 add_action_history(l_auction_header_id,
1070 l_transaction_id,
1071 'AWARD_APPROVAL_PENDING',
1072 NULL,
1073 SYSDATE,
1074 l_next_approver_employee.user_id,
1075 l_next_approver_employee.user_name);
1076
1077 resultout := 'VALID_NEXT_APPROVER';
1078
1079 -- otherwise
1080 ELSE
1081
1082 resultout := 'INVALID_NEXT_APPROVER';
1083 IF l_next_approver.orig_system = 'POS' THEN
1084 l_next_approver_name := get_display_name_for_person(l_person_id);
1085 ELSIF l_next_approver.orig_system = 'PER' THEN
1086 l_next_approver_name := get_display_name_for_person(l_next_approver.orig_system_id);
1087 ELSE
1088 l_next_approver_name := get_display_name_for_user(l_next_approver.orig_system_id);
1089 END IF;
1090 wf_engine.SetItemAttrText(itemtype => itemtype,
1091 itemkey => itemkey,
1092 aname => 'APPLICATION_ERROR',
1093 avalue => PON_AUCTION_PKG.getMessage('PON_INVALID_NEXT_APPROVER',
1094 NULL,
1095 'NAME',
1096 l_next_approver_name));
1097 END IF;
1098 END IF;
1099 END IF;
1100
1101 END get_next_approver;
1102
1103 /*
1104 Errors out the transaction.
1105 Sets workflow attributes used in notifying the trading partner contact.
1106 */
1107 PROCEDURE process_error(itemtype IN VARCHAR2,
1108 itemkey IN VARCHAR2,
1109 actid IN NUMBER,
1110 funcmode IN VARCHAR2,
1111 resultout OUT NOCOPY VARCHAR2) IS
1112 l_error_date DATE;
1113 l_tp_contact_user_id NUMBER;
1114 l_tp_contact_user_name VARCHAR2(100);
1115 l_auction_header_id NUMBER;
1116 l_transaction_id VARCHAR2(80);
1117 l_error_message VARCHAR2(2000);
1118 BEGIN
1119
1120 -- set rejection date attribute
1121 l_error_date := SYSDATE;
1122
1123 wf_engine.SetItemAttrDate(itemtype => itemtype,
1124 itemkey => itemkey,
1125 aname => 'REJECTION_DATE',
1126 avalue => l_error_date);
1127
1128 -- set attributes specific to OAM error notification
1129 l_tp_contact_user_name := wf_engine.getItemAttrText(itemtype => itemtype,
1130 itemkey => itemkey,
1131 aname => 'PREPARER_TP_CONTACT_NAME');
1132
1133 set_common_user_attributes(itemtype,
1134 itemkey,
1135 l_tp_contact_user_name,
1136 ERROR,
1137 FALSE,
1138 TRUE);
1139
1140 l_auction_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1141 itemkey => itemkey,
1142 aname => 'AUCTION_HEADER_ID');
1143
1144 l_transaction_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1145 itemkey => itemkey,
1146 aname => 'AME_TRANSACTION_ID');
1147
1148 l_error_message := wf_engine.GetItemAttrText(itemtype => itemtype,
1149 itemkey => itemkey,
1150 aname => 'APPLICATION_ERROR');
1151
1152 SELECT user_id
1153 INTO l_tp_contact_user_id
1154 FROM fnd_user
1155 WHERE user_name = l_tp_contact_user_name;
1156
1157 -- record that document as been rejected (as a result of an error)
1158 add_action_history(l_auction_header_id,
1159 l_transaction_id,
1160 'AWARD_REJECT',
1161 l_error_message,
1162 l_error_date,
1163 0,
1164 NULL);
1165
1166 -- change the approval status of the transaction to REJECTED
1167 UPDATE pon_auction_headers_all
1168 SET
1169 award_approval_status = 'REJECTED',
1170 last_updated_by = 0,
1171 last_update_date = SYSDATE
1172 WHERE auction_header_id = l_auction_header_id;
1173
1174 END process_error;
1175
1176 /*
1177 Determines if an OAM error has been raised.
1178 */
1179 PROCEDURE is_oam_error(itemtype IN VARCHAR2,
1180 itemkey IN VARCHAR2,
1181 actid IN NUMBER,
1182 funcmode IN VARCHAR2,
1183 resultout OUT NOCOPY VARCHAR2) IS
1184 BEGIN
1185
1186 IF wf_engine.GetItemAttrText(itemtype => itemtype,
1187 itemkey => itemkey,
1188 aname => 'OAM_ERROR') <> 'NO' THEN
1189 resultout := 'Y';
1190 ELSE
1191 resultout := 'N';
1192 END IF;
1193
1194 END is_oam_error;
1195
1196 /*
1197 Determines if an OAM administrator is available.
1198 Sets workflow attributes used in notifying the OAM administrator of an error.
1199 */
1200 PROCEDURE is_oam_admin_available(itemtype IN VARCHAR2,
1201 itemkey IN VARCHAR2,
1202 actid IN NUMBER,
1203 funcmode IN VARCHAR2,
1204 resultout OUT NOCOPY VARCHAR2) IS
1205 l_error_date DATE;
1206 l_oam_admin_user_name VARCHAR2(100);
1207 l_admin_approver ame_util.approverRecord2;
1208
1209 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1210 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
1211 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1212 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.IS_OAM_ADMIN_AVAILABLE';
1213 BEGIN
1214
1215 BEGIN
1216 -- determine the OAM administrator
1217 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.getAdminApprover');
1218 ame_api2.getAdminApprover(applicationIdIn => APPLICATION_ID,
1219 transactionTypeIn => AWARD_TRANSACTION_TYPE,
1220 adminApproverOut => l_admin_approver);
1221
1222 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.getAdminApprover returns ' || fnd_global.newline || format_ame_approver(l_admin_approver));
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 l_admin_approver := ame_util.emptyApproverRecord2;
1227
1228 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
1229 END;
1230
1231 IF l_admin_approver.orig_system_id IS NOT NULL THEN
1232 BEGIN
1233 SELECT user_name
1234 INTO l_oam_admin_user_name
1235 FROM fnd_user
1236 WHERE employee_id = l_admin_approver.orig_system_id;
1237 EXCEPTION
1238 WHEN no_data_found OR too_many_rows THEN
1239 NULL;
1240 END;
1241 END IF;
1242
1243 -- if the OAM administrator could be determined
1244 IF l_oam_admin_user_name IS NOT NULL THEN
1245 -- set OAM Administrator attribute
1246 wf_engine.setItemAttrText(itemtype => itemtype,
1247 itemkey => itemkey,
1248 aname => 'OAM_ADMIN_USER',
1249 avalue => l_oam_admin_user_name);
1250
1251 -- set attributes specific to OAM error notification for OAM administrator
1252 set_common_user_attributes(itemtype,
1253 itemkey,
1254 l_oam_admin_user_name,
1255 ERROR_FOR_ADMIN,
1256 FALSE,
1257 TRUE);
1258
1259 resultout := 'Y';
1260 ELSE
1261 -- otherwise
1262 resultout := 'N';
1263 END IF;
1264
1265 END is_oam_admin_available;
1266
1267 /*
1268 Marks the transaction as rejected.
1269 */
1270 PROCEDURE document_rejected(itemtype IN VARCHAR2,
1271 itemkey IN VARCHAR2,
1272 actid IN NUMBER,
1273 funcmode IN VARCHAR2,
1274 resultout OUT NOCOPY VARCHAR2) IS
1275 l_auction_header_id NUMBER;
1276 l_transaction_id VARCHAR2(80);
1277 BEGIN
1278
1279 -- if the rejection date is null, set it to the current date
1280 IF wf_engine.GetItemAttrDate(itemtype => itemtype,
1281 itemkey => itemkey,
1282 aname => 'REJECTION_DATE') IS NULL THEN
1283 wf_engine.SetItemAttrDate(itemtype => itemtype,
1284 itemkey => itemkey,
1285 aname => 'REJECTION_DATE',
1286 avalue => SYSDATE);
1287 END IF;
1288
1289 -- set attributes specific to award rejected notification
1290 set_award_rejected_attributes(itemtype, itemkey);
1291
1292 l_auction_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1293 itemkey => itemkey,
1294 aname => 'AUCTION_HEADER_ID');
1295
1296 l_transaction_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1297 itemkey => itemkey,
1298 aname => 'AME_TRANSACTION_ID');
1299
1300 -- change the approval status of the transaction to REJECTED
1301 UPDATE pon_auction_headers_all
1302 SET
1303 award_approval_status = 'REJECTED',
1304 last_updated_by = 0,
1305 last_update_date = SYSDATE
1306 WHERE auction_header_id = l_auction_header_id;
1307
1308 END document_rejected;
1309
1310 /*
1311 Marks the transaction as approved.
1312 */
1313 PROCEDURE document_approved(itemtype IN VARCHAR2,
1314 itemkey IN VARCHAR2,
1315 actid IN NUMBER,
1316 funcmode IN VARCHAR2,
1317 resultout OUT NOCOPY VARCHAR2) IS
1318 l_auction_header_id NUMBER;
1319 l_transaction_id VARCHAR2(80);
1320 l_approval_date DATE;
1321 BEGIN
1322
1323 -- if the approval date is null, set it to the current date
1324 l_approval_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
1325 itemkey => itemkey,
1326 aname => 'APPROVAL_DATE');
1327
1328 IF l_approval_date IS NULL THEN
1329 l_approval_date := SYSDATE;
1330
1331 wf_engine.SetItemAttrDate(itemtype => itemtype,
1332 itemkey => itemkey,
1333 aname => 'APPROVAL_DATE',
1334 avalue => l_approval_date);
1335 END IF;
1336
1337 -- set attributes specific to award approved notification
1338 set_award_approved_attributes(itemtype, itemkey);
1339
1340 l_auction_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1341 itemkey => itemkey,
1342 aname => 'AUCTION_HEADER_ID');
1343
1344 l_transaction_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1345 itemkey => itemkey,
1346 aname => 'AME_TRANSACTION_ID');
1347
1348 -- record that document as been approved (by all required approvers, if any)
1349 add_action_history(l_auction_header_id,
1350 l_transaction_id,
1351 'AWARD_APPROVE',
1352 PON_AUCTION_PKG.getMessage('PON_AWARD_IS_APPROVED'),
1353 l_approval_date,
1354 0,
1355 NULL);
1356
1357 -- change the approval status of the transaction to APPROVED
1358 UPDATE pon_auction_headers_all
1359 SET
1360 award_approval_status = 'APPROVED',
1361 last_updated_by = 0,
1362 last_update_date = SYSDATE
1363 WHERE auction_header_id = l_auction_header_id;
1364
1365 END document_approved;
1366
1367 /*
1368 Processes the approver response on the transaction.
1369 */
1370 PROCEDURE process_approver_response(itemtype IN VARCHAR2,
1371 itemkey IN VARCHAR2,
1372 actid IN NUMBER,
1373 funcmode IN VARCHAR2,
1374 resultout OUT NOCOPY VARCHAR2) IS
1375 l_response_type VARCHAR2(50);
1376 l_auction_header_id NUMBER;
1377 l_transaction_id VARCHAR2(80);
1378 l_response_date DATE;
1379 l_note_to_buyer VARCHAR2(2000);
1380 l_approver_user_name VARCHAR2(100);
1381 l_approver_string VARCHAR2(1500);
1382
1383 l_approver_employee employeeRecord;
1384 l_person_id NUMBER;
1385
1386 l_approver ame_util.approverRecord2;
1387 l_forwardee ame_util.approverRecord2;
1388 l_approver_old ame_util.approverRecord;
1389
1390 l_forwardee_user_name VARCHAR2(100);
1391 l_forwardee_employee employeeRecord;
1392 l_action_type VARCHAR2(25);
1393 l_approval_status VARCHAR2(50);
1394 l_valid_response BOOLEAN;
1395 l_success_flag BOOLEAN;
1396 l_source_type_out VARCHAR2(50);
1397 l_idList ame_util.idList;
1398
1399 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1400 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
1401 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1402 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.PROCESS_APPROVER_RESPONSE';
1403
1404 l_preparer_resp_id NUMBER;
1405 l_preparer_appl_id NUMBER;
1406
1407 BEGIN
1408
1409 -- bug 9555176 begin
1410 l_preparer_resp_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1411 itemkey => itemkey,
1412 aname => 'PREPARER_RESP_ID');
1413
1414 l_preparer_appl_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1415 itemkey => itemkey,
1416 aname => 'PREPARER_APPL_ID');
1417
1418 --Intialize the context of the approver as that of the preparer
1419
1420 fnd_global.apps_initialize(fnd_global.user_id, l_preparer_resp_id, l_preparer_appl_id);
1421
1422
1423
1424 --Bug 9555176 End
1425
1426 l_response_type := wf_engine.GetItemAttrText(itemtype => itemtype,
1427 itemkey => itemkey,
1428 aname => 'RESULT');
1429
1430 l_auction_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1431 itemkey => itemkey,
1432 aname => 'AUCTION_HEADER_ID');
1433
1434 l_transaction_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1435 itemkey => itemkey,
1436 aname => 'AME_TRANSACTION_ID');
1437
1438 l_approver_user_name := wf_engine.GetItemAttrText(itemtype => itemtype,
1439 itemkey => itemkey,
1440 aname => 'APPROVER_USER');
1441
1442 -- get the note to buyer attribute
1443 l_note_to_buyer := wf_engine.GetItemAttrText(itemtype => itemtype,
1444 itemkey => itemkey,
1445 aname => 'NOTE_TO_BUYER');
1446
1447 l_response_date := SYSDATE;
1448
1449 -- determine action type for response type
1450 IF l_response_type = 'APPROVE' THEN
1451 l_action_type := 'AWARD_APPROVE';
1452 ELSIF l_response_type = 'REJECT' THEN
1453 l_action_type := 'AWARD_REJECT';
1454 ELSIF l_response_type = 'FORWARD' THEN
1455 l_action_type := 'AWARD_APPROVAL_FORWARD';
1456 ELSIF l_response_type = 'APPROVE_AND_FORWARD' THEN
1457 l_action_type := 'AWARD_APPROVE_AND_FORWARD';
1458 END IF;
1459
1460
1461 l_approver_string := wf_engine.GetItemAttrText( itemtype => itemtype,
1462 itemkey => itemkey,
1463 aname => 'APPROVER_STRING');
1464
1465 log_string( l_statement_log_level,
1466 l_current_log_level,
1467 l_module_name,
1468 'l_approver_string :' || l_approver_string );
1469
1470 -- Find out the approver record type.
1471 IF is_old_approver_record( l_approver_string ) THEN
1472
1473 log_string( l_statement_log_level,
1474 l_current_log_level,
1475 l_module_name,
1476 'It is_old_approver_record. So calling ame_util.apprRecordToApprRecord2' );
1477 parse_approver(l_approver_string, l_approver_old);
1478 ame_util.apprRecordToApprRecord2( approverRecordIn => l_approver_old,
1479 approverRecord2Out => l_approver );
1480 ELSE
1481 parse_ame_approver(l_approver_string, l_approver);
1482 END IF;
1483
1484 log_string( l_statement_log_level,
1485 l_current_log_level,
1486 l_module_name,
1487 'Done with Parsing the approver string. Successfully framed the approver record.');
1488
1489 -- record approver response in PON_ACTION_HISTORY table
1490 IF l_approver.orig_system = 'POS' THEN -- Position Hierarchy setup in AME.
1491
1492 BEGIN
1493 l_person_id := NULL;
1494 ame_api3.parseApproverSource( approverSourceIn => l_approver.source,
1495 sourceDescriptionOut => l_source_type_out,
1496 ruleIdListOut => l_idList);
1497
1498 IF( l_approver.api_insertion = ame_util.apiAuthorityInsertion AND
1499 l_source_type_out <> ame_util.forwardeeSource ) THEN
1500 l_person_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1501 itemkey => itemkey,
1502 aname => 'FIRST_AUTHORITY_APPROVER_ID');
1503 END IF;
1504 IF( l_person_id IS NULL OR l_person_id = -1 ) THEN
1505 SELECT person_id into l_person_id FROM (
1506 SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
1507 WHERE asg.position_id = l_approver.orig_system_id and trunc(sysdate) between person.effective_start_date
1508 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
1509 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
1510 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
1511 and asg.assignment_status_type_id not in (
1512 SELECT assignment_status_type_id FROM per_assignment_status_types
1513 WHERE per_system_status = 'TERM_ASSIGN'
1514 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
1515 ) where rownum = 1;
1516 END IF;
1517 EXCEPTION
1518 WHEN OTHERS THEN
1519 l_person_id := NULL;
1520 END;
1521
1522 IF l_person_id IS NULL THEN -- There are no users associated to this position. Raise an error.
1523 resultout := 'OAM_API_ERROR';
1524 l_success_flag := FALSE;
1525
1526 -- this message can be seen only by admin.
1527 wf_engine.SetItemAttrText( itemtype => itemtype,
1528 itemkey => itemkey,
1529 aname => 'OAM_ERROR',
1530 avalue => PON_AUCTION_PKG.getMessage('PON_NO_PERSON_FOR_POSITION', '', 'POSITION_NAME', l_approver.display_name ));
1531
1532 -- this message can be seen by any buyer.
1533 wf_engine.SetItemAttrText( itemtype => itemtype,
1534 itemkey => itemkey,
1535 aname => 'APPLICATION_ERROR',
1536 avalue => PON_AUCTION_PKG.getMessage('PON_NO_PERSON_FOR_POSITION', '', 'POSITION_NAME', l_approver.display_name ));
1537
1538 log_string(l_exception_log_level, l_current_log_level, l_module_name, PON_AUCTION_PKG.getMessage('PON_NO_PERSON_FOR_POSITION', '', 'POSITION_NAME', l_approver.display_name ));
1539 RETURN;
1540 END IF;
1541
1542 get_employee_info_for_person(l_person_id, l_approver_employee);
1543
1544 ELSIF l_approver.orig_system = 'PER' THEN -- Emp-Supervisor Hierarchy setup in AME.
1545
1546 get_employee_info_for_person(l_approver.orig_system_id, l_approver_employee);
1547
1548 ELSE -- FND USER setup in AME.
1549
1550 get_employee_info_for_user(l_approver.orig_system_id, l_approver_employee);
1551
1552 END IF;
1553
1554 log_string( l_statement_log_level,
1555 l_current_log_level,
1556 l_module_name,
1557 'Got the user details and going to update the action history record.');
1558
1559 update_action_history( l_auction_header_id,
1560 l_transaction_id,
1561 l_action_type,
1562 l_note_to_buyer,
1563 l_response_date,
1564 l_approver_employee.user_id);
1565
1566 -- set the appropriate attributes depending on response type
1567 IF l_response_type IN ('APPROVE', 'APPROVE_AND_FORWARD') THEN
1568 wf_engine.SetItemAttrDate(itemtype => itemtype,
1569 itemkey => itemkey,
1570 aname => 'APPROVAL_DATE',
1571 avalue => l_response_date);
1572 ELSIF l_response_type = 'REJECT' THEN
1573 wf_engine.SetItemAttrDate(itemtype => itemtype,
1574 itemkey => itemkey,
1575 aname => 'REJECTION_DATE',
1576 avalue => l_response_date);
1577 END IF;
1578
1579
1580 -- perform validation on approval response
1581 l_valid_response := TRUE;
1582
1583 -- if the approver is approving, simply mark the result as an approval
1584 IF l_response_type = 'APPROVE' THEN
1585 resultout := 'APPROVAL';
1586 -- if the approver is rejecting, simply mark the result as a rejection
1587 ELSIF l_response_type = 'REJECT' THEN
1588 resultout := 'REJECTION';
1589 -- if the approver is forwarding, validate the forwardee
1590 ELSIF l_response_type IN ('FORWARD', 'APPROVE_AND_FORWARD') THEN
1591 l_forwardee_user_name := wf_engine.GetItemAttrText(itemtype => itemtype,
1592 itemkey => itemkey,
1593 aname => 'APPROVER_FORWARDEE_USER');
1594
1595 -- if no forwardee was specified
1596 IF l_forwardee_user_name IS NULL THEN
1597 resultout := 'INVALID_FORWARD';
1598 l_valid_response := FALSE;
1599
1600 -- set the Application Error workflow attribute
1601 wf_engine.SetItemAttrText(itemtype => itemtype,
1602 itemkey => itemkey,
1603 aname => 'APPLICATION_ERROR',
1604 avalue => PON_AUCTION_PKG.getMessage('PON_NO_FORWARDEE_SPECIFIED'));
1605 -- otherwise
1606 ELSE
1607 -- validate that the forwardee is an active employee and has a user account
1608 get_employee_info_for_user(l_forwardee_user_name, l_forwardee_employee);
1609
1610 -- response is valid only if forwardee is valid
1611 IF l_forwardee_employee.user_id IS NOT NULL AND l_forwardee_employee.person_id IS NOT NULL THEN
1612 resultout := 'VALID_FORWARD';
1613 ELSE
1614
1615 resultout := 'INVALID_FORWARD';
1616 l_valid_response := FALSE;
1617
1618 -- set the Application Error workflow attribute
1619 wf_engine.SetItemAttrText(itemtype => itemtype,
1620 itemkey => itemkey,
1621 aname => 'APPLICATION_ERROR',
1622 avalue => PON_AUCTION_PKG.getMessage('PON_INVALID_FORWARDEE', NULL, 'NAME', get_display_name_for_user(l_forwardee_user_name)));
1623 END IF;
1624 END IF;
1625 END IF;
1626
1627 -- post-validation: if validation was successful, update OAM approver list
1628 IF l_valid_response THEN
1629
1630 -- determine approval status for response type
1631 -- set approver's approval status
1632 IF l_response_type = 'APPROVE' THEN
1633 l_approver.approval_status:= ame_util.approvedStatus;
1634 ELSIF l_response_type = 'REJECT' THEN
1635 l_approver.approval_status := ame_util.rejectStatus;
1636 ELSIF l_response_type = 'FORWARD' THEN
1637 l_approver.approval_status := ame_util.forwardStatus;
1638 ELSIF l_response_type = 'APPROVE_AND_FORWARD' THEN
1639 l_approver.approval_status := ame_util.approveAndForwardStatus;
1640 END IF;
1641
1642 -- use forwardee's person id since person id is preferred
1643 IF l_forwardee_employee.person_id IS NOT NULL THEN
1644
1645 l_forwardee.orig_system_id := l_forwardee_employee.person_id;
1646 l_forwardee.orig_system := l_approver.orig_system;
1647
1648 -- set forwardee's api_insertion and authority fields
1649 IF l_approver.api_insertion IN (ame_util.oamGenerated, ame_util.apiAuthorityInsertion) AND
1650 l_approver.authority = ame_util.authorityApprover THEN
1651 l_forwardee.api_insertion := ame_util.apiAuthorityInsertion;
1652 ELSE
1653 l_forwardee.api_insertion := ame_util.apiInsertion;
1654 END IF;
1655
1656 IF l_forwardee.orig_system = 'POS' THEN
1657 select full_name into l_forwardee.display_name from per_all_people_f where person_id = l_forwardee.orig_system_id and TRUNC(sysdate) between effective_start_date and effective_end_date;
1658 BEGIN
1659 SELECT position_id into l_forwardee.orig_system_id FROM PER_ALL_ASSIGNMENTS_F pa
1660 WHERE pa.person_id = l_forwardee.orig_system_id and pa.primary_flag = 'Y' and pa.assignment_type in ('E','C')
1661 and pa.position_id is not null and pa.assignment_status_type_id not in (
1662 select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
1663 and TRUNC ( pa.effective_start_date )
1664 <= TRUNC(SYSDATE) AND NVL(pa.effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE);
1665 EXCEPTION
1666 WHEN OTHERS THEN
1667 l_forwardee.orig_system_id := NULL;
1668 END;
1669
1670 IF l_forwardee.orig_system_id IS NULL THEN
1671 resultout := 'INVALID_FORWARD';
1672 l_success_flag := FALSE;
1673
1674 -- this message can be seen only by admin.
1675 wf_engine.SetItemAttrText( itemtype => itemtype,
1676 itemkey => itemkey,
1677 aname => 'OAM_ERROR',
1678 avalue => PON_AUCTION_PKG.getMessage('PON_NO_POSITION_FOR_PERSON', '', 'PERSON_NAME', l_forwardee.display_name ));
1679
1680 -- this message can be seen by any buyer.
1681 wf_engine.SetItemAttrText( itemtype => itemtype,
1682 itemkey => itemkey,
1683 aname => 'APPLICATION_ERROR',
1684 avalue => PON_AUCTION_PKG.getMessage('PON_INVALID_FORWARDEE', NULL, 'NAME',l_forwardee.display_name ));
1685
1686 RETURN;
1687 END IF;
1688 END IF;
1689
1690 l_forwardee.approval_status := ame_util.nullStatus;
1691
1692 l_forwardee.authority := l_approver.authority;
1693 l_forwardee.approver_category := l_approver.approver_category;
1694 l_forwardee.item_class := l_approver.item_class ;
1695 l_forwardee.item_id := l_approver.item_id ;
1696
1697 SELECT name into l_forwardee.name FROM wf_roles
1698 WHERE orig_system = l_forwardee.orig_system and orig_system_id = l_forwardee.orig_system_id and rownum = 1;
1699
1700 IF l_forwardee.name IS NULL THEN
1701 resultout := 'INVALID_FORWARD';
1702 l_success_flag := FALSE;
1703
1704 -- this message can be seen only by admin.
1705 wf_engine.SetItemAttrText( itemtype => itemtype,
1706 itemkey => itemkey,
1707 aname => 'OAM_ERROR',
1708 avalue => PON_AUCTION_PKG.getMessage('PON_INVALID_FORWARDEE', NULL, 'NAME',l_forwardee.display_name ));
1709
1710 -- this message can be seen by any buyer.
1711 wf_engine.SetItemAttrText( itemtype => itemtype,
1712 itemkey => itemkey,
1713 aname => 'APPLICATION_ERROR',
1714 avalue => PON_AUCTION_PKG.getMessage('PON_INVALID_FORWARDEE', NULL, 'NAME', l_forwardee.display_name ));
1715
1716 RETURN;
1717 END IF;
1718 ELSE
1719 l_forwardee := ame_util.emptyApproverRecord2;
1720 END IF;
1721
1722 l_success_flag := TRUE;
1723
1724 BEGIN
1725
1726 -- update the approval status for the approver
1727 log_string(l_statement_log_level,
1728 l_current_log_level,
1729 l_module_name,
1730 'calling ame_api2.updateApprovalStatus on workflow item key ' || itemkey || fnd_global.newline ||
1731 'with approver' || fnd_global.newline || format_ame_approver(l_approver) || fnd_global.newline ||
1732 'and forwardee' || fnd_global.newline || format_ame_approver(l_forwardee));
1733
1734 ame_api2.updateApprovalStatus(applicationIdIn => APPLICATION_ID,
1735 transactionIdIn => l_transaction_id,
1736 approverIn => l_approver,
1737 transactionTypeIn => AWARD_TRANSACTION_TYPE,
1738 forwardeeIn => l_forwardee);
1739
1740 EXCEPTION
1741 WHEN OTHERS THEN
1742
1743 resultout := 'OAM_API_ERROR';
1744 l_success_flag := FALSE;
1745
1746 -- if the OAM API call raises an exception,
1747 -- set the Application Error and OAM Error workflow attributes
1748 -- this message is internal and can be seen only by the OAM administrator
1749 wf_engine.SetItemAttrText(itemtype => itemtype,
1750 itemkey => itemkey,
1751 aname => 'OAM_ERROR',
1752 avalue => SQLERRM);
1753
1754 -- this message can be seen by any buyer
1755 wf_engine.SetItemAttrText(itemtype => itemtype,
1756 itemkey => itemkey,
1757 aname => 'APPLICATION_ERROR',
1758 avalue => PON_AUCTION_PKG.getMessage('PON_OAM_ERROR_OCCURRED'));
1759
1760 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
1761 END;
1762 END IF;
1763
1764 END process_approver_response;
1765
1766 /*
1767 Starts a new Sourcing Award Approval workflow process for the specified transaction.
1768 */
1769 PROCEDURE start_workflow_process(p_auction_header_id IN NUMBER,
1770 p_transaction_id IN VARCHAR2,
1771 p_item_key IN VARCHAR2,
1772 p_note_to_approvers IN VARCHAR2,
1773 p_first_authority_approver_id IN NUMBER ) IS
1774 BEGIN
1775
1776 wf_engine.createProcess(itemtype => AWARD_APPROVAL_WF_ITEM_TYPE,
1777 itemkey => p_item_key,
1778 process => 'AWARD_APPROVAL');
1779
1780 -- set main language-independent workflow attributes
1781 set_core_attributes(AWARD_APPROVAL_WF_ITEM_TYPE, p_item_key, p_auction_header_id, p_note_to_approvers, p_first_authority_approver_id);
1782
1783
1784 -- Bug 4295915: Set the workflow owner
1785 wf_engine.SetItemOwner(itemtype => AWARD_APPROVAL_WF_ITEM_TYPE,
1786 itemkey => p_item_key,
1787 owner => fnd_global.user_name);
1788
1789 -- start workflow process
1790 wf_engine.StartProcess(itemtype => AWARD_APPROVAL_WF_ITEM_TYPE,
1791 itemkey => p_item_key);
1792
1793 END start_workflow_process;
1794
1795 /*
1796 Adds an approver to the approver list at a specified position.
1797 The resulting approver list is returned as a formatted string.
1798 */
1799 PROCEDURE add_approver(p_auction_header_id IN NUMBER,
1800 p_transaction_id IN VARCHAR2,
1801 p_approver_string IN VARCHAR2,
1802 p_position IN NUMBER,
1803 p_last_update_date IN DATE,
1804 p_approver_list_string OUT NOCOPY VARCHAR2,
1805 p_error_message OUT NOCOPY VARCHAR2) IS
1806
1807 l_approver_list ame_util.approversTable2;
1808 l_approver ame_util.approverRecord2;
1809 l_available_insertion_list ame_util.insertionsTable2;
1810 l_insertion_order ame_util.insertionRecord2;
1811 l_process_out varchar2(2);
1812
1813 l_error_code NUMBER;
1814
1815 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1816 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
1817 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1818 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.ADD_APPROVER';
1819 BEGIN
1820
1821 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'adding to approver list for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
1822
1823 -- check that caller can modify the approver list
1824 validate_transaction(p_auction_header_id, p_last_update_date, false);
1825
1826 parse_ame_approver(p_approver_string, l_approver);
1827
1828 -- get a list of all available insertions for the position at which the approver will be inserted
1829 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api3.getAvailableInsertions at position ' || p_position);
1830
1831 ame_api3.getAvailableInsertions(applicationIdIn => APPLICATION_ID,
1832 transactionIdIn => p_transaction_id,
1833 positionIn => p_position,
1834 transactionTypeIn => AWARD_TRANSACTION_TYPE,
1835 availableInsertionsOut => l_available_insertion_list);
1836
1837 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api3.getAvailableInsertions returns ' || fnd_global.newline || get_insertion_list_string(l_available_insertion_list));
1838
1839 -- select an absolute-order, after-approver, before-approver insertion in the list of available insertions
1840 -- that corresponds to the api insertion and authority value of the approver to be inserted into the approver list
1841
1842 FOR i IN 1 .. l_available_insertion_list.COUNT LOOP
1843 IF l_available_insertion_list(i).order_type IN
1844 (ame_util.absoluteOrder, ame_util.afterApprover, ame_util.beforeApprover) AND
1845 l_available_insertion_list(i).api_insertion = l_approver.api_insertion AND
1846 l_available_insertion_list(i).authority = l_approver.authority THEN
1847
1848 l_insertion_order := l_available_insertion_list(i);
1849
1850 l_approver.item_class := l_insertion_order.item_class;
1851 l_approver.item_id := l_insertion_order.item_id;
1852 l_approver.action_type_id := l_insertion_order.action_type_id;
1853 l_approver.group_or_chain_id := l_insertion_order.group_or_chain_id;
1854 l_approver.api_insertion := l_insertion_order.api_insertion;
1855 l_approver.authority := l_insertion_order.authority;
1856
1857 SELECT name into l_approver.name FROM wf_roles
1858 WHERE orig_system = l_approver.orig_system and orig_system_id = l_approver.orig_system_id and rownum = 1;
1859
1860 IF l_approver.name IS NULL THEN
1861 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
1862 l_approver.orig_system_id || ' -- orig_system :' || l_approver.orig_system );
1863 END IF;
1864
1865 EXIT;
1866
1867 END IF;
1868 END LOOP;
1869
1870 -- insert the approver into the approver list with the selected insertion
1871 log_string(
1872 l_statement_log_level,
1873 l_current_log_level,
1874 l_module_name,
1875 'calling ame_api3.insertApprover with approver insertion ' || fnd_global.newline || get_insertion_string(l_approver, l_insertion_order) || ' at position ' || p_position);
1876
1877 ame_api3.insertApprover( applicationIdIn => APPLICATION_ID,
1878 transactionIdIn => p_transaction_id,
1879 approverIn => l_approver,
1880 positionIn => p_position,
1881 insertionIn => l_insertion_order,
1882 transactionTypeIn => AWARD_TRANSACTION_TYPE);
1883
1884 -- recalculate approver list
1885 ame_api2.getAllApprovers7( applicationIdIn => APPLICATION_ID,
1886 transactionIdIn => p_transaction_id,
1887 transactionTypeIn => AWARD_TRANSACTION_TYPE,
1888 approvalProcessCompleteYNOut => l_process_out,
1889 approversOut => l_approver_list);
1890
1891 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.getAllApprovers7 returns ' || fnd_global.newline || format_ame_approver_list(l_approver_list));
1892
1893 p_approver_list_string := format_ame_approver_list(l_approver_list);
1894
1895 p_error_message := NULL;
1896
1897 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'added to approver list for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
1898
1899 EXCEPTION
1900 WHEN OTHERS THEN
1901 -- reset output variables
1902 p_approver_list_string := NULL;
1903 p_error_message := NULL;
1904
1905 l_error_code := SQLCODE;
1906 IF l_error_code <= -20000 AND l_error_code >= -20999 THEN
1907 trim_error_code(l_error_code, SQLERRM, p_error_message);
1908 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
1909 ELSE
1910 -- raise all Oracle-predefined and user-defined exceptions
1911 RAISE;
1912 END IF;
1913 END add_approver;
1914
1915 /*
1916 Deletes an approver from the approver list.
1917 The resulting approver list is returned as a formatted string.
1918 */
1919 PROCEDURE delete_approver(p_auction_header_id IN NUMBER,
1920 p_transaction_id IN VARCHAR2,
1921 p_approver_string IN VARCHAR2,
1922 p_last_update_date IN DATE,
1923 p_approver_list_string OUT NOCOPY VARCHAR2,
1924 p_error_message OUT NOCOPY VARCHAR2) IS
1925
1926 l_approver_list ame_util.approversTable2;
1927 l_approver ame_util.approverRecord2;
1928 l_process_out varchar2(2);
1929
1930 l_error_code NUMBER;
1931
1932 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1933 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
1934 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1935 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.DELETE_APPROVER';
1936 BEGIN
1937
1938 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'deleting from approver list for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
1939
1940 -- check that caller can modify the approver list
1941 validate_transaction(p_auction_header_id, p_last_update_date, false);
1942
1943 parse_ame_approver( p_approver_string, l_approver );
1944
1945 -- delete approver from approver list
1946 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api3.suppressApprover with ' || fnd_global.newline || format_ame_approver(l_approver));
1947
1948 ame_api3.suppressApprover(applicationIdIn => APPLICATION_ID,
1949 transactionIdIn => p_transaction_id,
1950 approverIn => l_approver,
1951 transactionTypeIn => AWARD_TRANSACTION_TYPE);
1952
1953 -- recalculate approver list
1954 ame_api2.getAllApprovers7( applicationIdIn => APPLICATION_ID,
1955 transactionIdIn => p_transaction_id,
1956 transactionTypeIn => AWARD_TRANSACTION_TYPE,
1957 approvalProcessCompleteYNOut => l_process_out,
1958 approversOut => l_approver_list);
1959
1960 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.getAllApprovers7 returns ' || fnd_global.newline || format_ame_approver_list(l_approver_list));
1961
1962 p_approver_list_string := format_ame_approver_list( l_approver_list);
1963
1964 p_error_message := NULL;
1965
1966 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'deleted from approver list for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
1967
1968 EXCEPTION
1969 WHEN OTHERS THEN
1970 -- reset output variables
1971 p_approver_list_string := NULL;
1972 p_error_message := NULL;
1973
1974 l_error_code := SQLCODE;
1975 IF l_error_code <= -20000 AND l_error_code >= -20999 THEN
1976 trim_error_code(l_error_code, SQLERRM, p_error_message);
1977 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
1978 ELSE
1979 -- raise all Oracle-predefined and user-defined exceptions
1980 RAISE;
1981 END IF;
1982 END delete_approver;
1983
1984 /*
1985 Changes the first authority approver in the approver list.
1986 The resulting approver list is returned as a formatted string.
1987 */
1988 PROCEDURE change_first_approver(p_auction_header_id IN NUMBER,
1989 p_transaction_id IN VARCHAR2,
1990 p_approver_string IN VARCHAR2,
1991 p_last_update_date IN DATE,
1992 p_approver_list_string OUT NOCOPY VARCHAR2,
1993 p_error_message OUT NOCOPY VARCHAR2) IS
1994
1995 l_approver_list ame_util.approversTable2;
1996 l_approver ame_util.approverRecord2;
1997 l_process_out VARCHAR2(1);
1998
1999 insertion ame_util.insertionRecord2;
2000 l_available_insertion_list ame_util.insertionsTable2;
2001 l_current_first_approver ame_util.approverRecord2;
2002
2003 l_error_code NUMBER;
2004
2005 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2006 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
2007 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
2008 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.CHANGE_FIRST_APPROVER';
2009 BEGIN
2010
2011 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'changing first authority approver for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
2012
2013 -- check that caller can modify the approver list
2014 validate_transaction(p_auction_header_id, p_last_update_date, false);
2015
2016 -- clear out all approver deletions
2017 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api3.clearSuppressions');
2018 ame_api3.clearSuppressions(applicationIdIn => APPLICATION_ID,
2019 transactionIdIn => p_transaction_id,
2020 transactionTypeIn => AWARD_TRANSACTION_TYPE);
2021
2022 parse_ame_approver(p_approver_string, l_approver);
2023
2024 -- corner case scenarios. Find out the current first approver and set the fields accordingly.
2025 ame_api2.getAllApprovers7( applicationIdIn => APPLICATION_ID,
2026 transactionIdIn => p_transaction_id,
2027 transactionTypeIn => AWARD_TRANSACTION_TYPE,
2028 approvalProcessCompleteYNOut => l_process_out,
2029 approversOut => l_approver_list);
2030
2031 for i in 1 .. l_approver_list.count loop
2032 if( l_approver_list(i).authority = ame_util.authorityApprover
2033 and l_approver_list(i).group_or_chain_id < 3
2034 and l_approver_list(i).api_insertion = ame_util.oamGenerated) then
2035 l_current_first_approver := l_approver_list(i) ;
2036 log_string(l_statement_log_level, l_current_log_level, l_module_name,'authorityApprover: ' || l_current_first_approver.authority);
2037
2038 exit;
2039 end if;
2040 end loop;
2041 log_string(l_statement_log_level, l_current_log_level, l_module_name,'l_current_first_approver.orig_system ' ||l_current_first_approver.orig_system || ', l_approver.orig_system ' ||l_approver.orig_system);
2042
2043
2044 IF l_current_first_approver.orig_system = 'POS' AND l_approver.orig_system = 'PER' THEN
2045
2046 log_string(l_statement_log_level, l_current_log_level, l_module_name, ' ---- Position Record. So trying to find out the position details ---- ');
2047
2048 l_approver.orig_system := 'POS';
2049 IF l_approver.orig_system_id IS NOT NULL THEN
2050 select full_name into l_approver.display_name from per_all_people_f where person_id = l_approver.orig_system_id and TRUNC(sysdate) between effective_start_date and effective_end_date ;
2051 BEGIN
2052 SELECT position_id into l_approver.orig_system_id FROM PER_ALL_ASSIGNMENTS_F pa
2053 WHERE pa.person_id = l_approver.orig_system_id and pa.primary_flag = 'Y' and pa.assignment_type in ('E','C')
2054 and pa.position_id is not null and pa.assignment_status_type_id not in (
2055 select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
2056 and TRUNC ( pa.effective_start_date )
2057 <= TRUNC(SYSDATE) AND NVL(pa.effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE);
2058 EXCEPTION
2059 WHEN OTHERS THEN
2060 l_approver.orig_system_id := NULL;
2061 END;
2062
2063 IF l_approver.orig_system_id IS NULL THEN
2064 raise_application_error(-20001,PON_AUCTION_PKG.getMessage('PON_NO_POSITION_FOR_PERSON', '', 'PERSON_NAME', l_approver.display_name ));
2065 END IF;
2066 END IF;
2067 END IF;
2068
2069 -- set the mandatory default attributes for the first authority approver.
2070 -- this will make sure we have populated the correct values.
2071 l_approver.authority := ame_util.authorityApprover;
2072 l_approver.api_insertion := ame_util.apiAuthorityInsertion;
2073 l_approver.approval_status := ame_util.nullStatus;
2074 l_approver.approver_category := ame_util.approvalApproverCategory ;
2075 l_approver.item_class := l_current_first_approver.item_class ;
2076 l_approver.item_id := l_current_first_approver.item_id ;
2077 l_approver.action_type_id := l_current_first_approver.action_type_id ;
2078 l_approver.group_or_chain_id := l_current_first_approver.group_or_chain_id ;
2079
2080 -- set first authority approver
2081 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.setFirstAuthorityApprover with ' || fnd_global.newline || format_ame_approver(l_approver));
2082
2083 SELECT name into l_approver.name FROM wf_roles
2084 WHERE orig_system = l_approver.orig_system and orig_system_id = l_approver.orig_system_id and rownum = 1;
2085
2086 IF l_approver.name IS NULL THEN
2087 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
2088 l_approver.orig_system_id || ' -- orig_system :' || l_approver.orig_system );
2089 END IF;
2090
2091 ame_api2.setFirstAuthorityApprover(applicationIdIn => APPLICATION_ID,
2092 transactionIdIn => p_transaction_id,
2093 approverIn => l_approver,
2094 transactionTypeIn => AWARD_TRANSACTION_TYPE,
2095 clearChainStatusYNIn => ame_util.booleanTrue);
2096
2097 -- recalculate approver list
2098 ame_api2.getAllApprovers7( applicationIdIn => APPLICATION_ID,
2099 transactionIdIn => p_transaction_id,
2100 transactionTypeIn => AWARD_TRANSACTION_TYPE,
2101 approvalProcessCompleteYNOut => l_process_out,
2102 approversOut => l_approver_list);
2103
2104 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.getAllApprovers7 returns ' || fnd_global.newline || format_ame_approver_list(l_approver_list));
2105
2106 p_approver_list_string := format_ame_approver_list( l_approver_list);
2107
2108 p_error_message := NULL;
2109
2110 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'changing first authority approver for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
2111
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114 -- reset output variables
2115 p_approver_list_string := NULL;
2116 p_error_message := NULL;
2117
2118 l_error_code := SQLCODE;
2119 IF l_error_code <= -20000 AND l_error_code >= -20999 THEN
2120 trim_error_code(l_error_code, SQLERRM, p_error_message);
2121 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
2122 ELSE
2123 -- raise all Oracle-predefined and user-defined exceptions
2124 RAISE;
2125 END IF;
2126 END change_first_approver;
2127
2128 /*
2129 Resets the approver list to its initial state.
2130 The resulting approver list is returned as a formatted string.
2131 */
2132 PROCEDURE reset_approver_list(p_auction_header_id IN NUMBER,
2133 p_transaction_id IN VARCHAR2,
2134 p_last_update_date IN DATE,
2135 p_approver_list_string OUT NOCOPY VARCHAR2,
2136 p_can_delete_oam_approvers OUT NOCOPY VARCHAR2,
2137 p_error_message OUT NOCOPY VARCHAR2) IS
2138
2139 l_approver_list ame_util.approversTable2;
2140 l_attribute_value1 VARCHAR2(10);
2141 l_attribute_value2 VARCHAR2(10);
2142 l_attribute_value3 VARCHAR2(10);
2143 l_process_out VARCHAR2(10);
2144
2145 l_error_code NUMBER;
2146
2147 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2148 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
2149 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
2150 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.RESET_APPROVER_LIST';
2151 BEGIN
2152
2153 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'resetting approver list for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
2154
2155 -- check that caller can modify the approver list
2156 validate_transaction(p_auction_header_id, p_last_update_date, false);
2157
2158 -- clear out all approval state
2159 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.clearAllApprovals');
2160 ame_api2.clearAllApprovals(applicationIdIn => APPLICATION_ID,
2161 transactionIdIn => p_transaction_id,
2162 transactionTypeIn => AWARD_TRANSACTION_TYPE);
2163
2164 -- recalculate approver list
2165 ame_api2.getAllApprovers7(applicationIdIn => APPLICATION_ID,
2166 transactionIdIn => p_transaction_id,
2167 transactionTypeIn => AWARD_TRANSACTION_TYPE,
2168 approvalProcessCompleteYNOut => l_process_out,
2169 approversOut => l_approver_list);
2170
2171 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api2.getAllApprovers7 returns ' || fnd_global.newline || format_ame_approver_list(l_approver_list));
2172
2173 p_approver_list_string := format_ame_approver_list(l_approver_list);
2174
2175 -- determine whether OAM-generated approvers can be deleted
2176 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api3.getAttributeValue with ' || ame_util.allowDeletingOamApprovers);
2177 ame_api3.getAttributeValue(applicationIdIn => APPLICATION_ID,
2178 transactionTypeIn => AWARD_TRANSACTION_TYPE,
2179 transactionIdIn => p_transaction_id,
2180 attributeNameIn => ame_util.allowDeletingOamApprovers,
2181 itemIdIn => NULL,
2182 attributeValue1Out => l_attribute_value1,
2183 attributeValue2Out => l_attribute_value2,
2184 attributeValue3Out => l_attribute_value3);
2185 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api3.getAttributeValue returns ' || l_attribute_value1);
2186
2187 IF l_attribute_value1 = 'true' THEN
2188 p_can_delete_oam_approvers := 'Y';
2189 ELSE
2190 p_can_delete_oam_approvers := 'N';
2191 END IF;
2192
2193 p_error_message := NULL;
2194
2195 log_string(l_statement_log_level, l_current_log_level, l_module_name, 'reset approver list for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
2196
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 -- reset output variables
2200 p_approver_list_string := NULL;
2201 p_can_delete_oam_approvers := NULL;
2202 p_error_message := NULL;
2203
2204 l_error_code := SQLCODE;
2205 IF l_error_code <= -20000 AND l_error_code >= -20999 THEN
2206 trim_error_code(l_error_code, SQLERRM, p_error_message);
2207 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
2208 ELSE
2209 -- raise all Oracle-predefined and user-defined exceptions
2210 RAISE;
2211 END IF;
2212 END reset_approver_list;
2213
2214 /*
2215 Submits a transaction for approval.
2216 -- p_first_authority_approver_id : This parameter will be used for futher processing only when the following conditions are satisfied
2217 -- 1. Position Hierarchy Rule is setup
2218 -- 2. User has specifically changed the first authority approver
2219 -- A WF attribute FIRST_AUTHORITY_APPROVER_ID will be set with this value and will be accessed only for Position Hierarchy setup.
2220 */
2221 PROCEDURE submit_for_approval(p_auction_header_id IN NUMBER,
2222 p_transaction_id IN VARCHAR2,
2223 p_user_id IN NUMBER,
2224 p_user_name IN VARCHAR2,
2225 p_last_update_date IN DATE,
2226 p_note_to_approvers IN VARCHAR2,
2227 p_reject_unawarded_responses IN VARCHAR2,
2228 p_note_to_rejected_suppliers IN VARCHAR2,
2229 p_has_items_flag IN VARCHAR2,
2230 p_has_scoring_teams_flag IN VARCHAR2,
2231 p_scoring_lock_tpc_id IN NUMBER,
2232 p_first_authority_approver_id IN NUMBER,
2233 p_error_message OUT NOCOPY VARCHAR2) IS
2234 l_item_key VARCHAR2(240);
2235
2236 l_error_code NUMBER;
2237
2238 x_return_status VARCHAR2(20);
2239 x_msg_count NUMBER;
2240 x_msg_data VARCHAR2(2000);
2241
2242 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2243 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
2244 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
2245 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.SUBMIT_FOR_APPROVAL';
2246
2247 -- Unsolicited Lines Project
2248 l_allow_unsol_lines VARCHAR2(1);
2249 l_neg_has_unsol_lines VARCHAR2(1);
2250 BEGIN
2251
2252 -- Unsolicited Lines Project
2253 l_allow_unsol_lines := PON_UNSOL_UTIL_PKG.ARE_UNSOL_LINES_ALLOWED(p_auction_header_id);
2254 IF l_allow_unsol_lines = 'Y' THEN
2255 l_neg_has_unsol_lines := PON_UNSOL_UTIL_PKG.HAS_UNSOL_LINES(p_auction_header_id);
2256 ELSE
2257 l_neg_has_unsol_lines := 'N';
2258 END IF;
2259 -- End Unsolicited Lines Project
2260
2261 -- reject all active responses without an award decision
2262 IF p_reject_unawarded_responses = 'Y' THEN
2263 --PON_AWARD_PKG.reject_unawarded_active_bids(p_auction_header_id, p_user_id, p_note_to_rejected_suppliers,p_has_items_flag);
2264
2265 /* Unsolicited lines project : If unsol lines are allowed and any offer has unsol lines, call a different procedure
2266 * to reject unawarded neg and unsol lines.
2267 */
2268 IF l_neg_has_unsol_lines = 'Y' THEN
2269 PON_UNSOL_AWARD_PKG.reject_unawarded_unsol_bids(p_auction_header_id, p_user_id, p_note_to_rejected_suppliers);
2270 ELSE
2271 PON_AWARD_PKG.reject_unawarded_active_bids(p_auction_header_id, p_user_id, p_note_to_rejected_suppliers, p_has_items_flag);
2272 END IF;
2273
2274 END IF;
2275
2276 -- check that caller can modify the approver list
2277 validate_transaction(p_auction_header_id, p_last_update_date, true);
2278
2279 -- if team scoring is enabled, call routine to lock team scoring
2280 IF (p_has_scoring_teams_flag = 'Y') THEN
2281 -- check to see if the auction was already locked for scoring
2282 -- if this were true, the p_scoring_lock_tpc_id will be -1
2283 -- as determined in the ApproverListAM from where this API is called.
2284 IF (p_scoring_lock_tpc_id = -1) THEN
2285 NULL;
2286 ELSE
2287 -- call pvt API to lock scoring
2288 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2289 fnd_log.string(log_level => fnd_log.level_unexpected
2290 ,module => 'pon_award_approval_pkg.submit_for_approval'
2291 ,message => 'before calling private API to lock team scoring');
2292 END IF;
2293
2294 PON_TEAM_SCORING_UTIL_PVT.lock_scoring(p_api_version => 1
2295 ,p_auction_header_id => p_auction_header_id
2296 ,p_tpc_id => p_scoring_lock_tpc_id
2297 ,x_return_status => x_return_status
2298 ,x_msg_data => x_msg_data
2299 ,x_msg_count => x_msg_count);
2300
2301 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2302 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2303 fnd_log.string(log_level => fnd_log.level_unexpected
2304 ,module => 'pon_award_approval_pkg.submit_for_approval'
2305 ,message => 'Error while locking team scoring');
2306 END IF;
2307 END IF;
2308 END IF;
2309 END IF;
2310
2311 -- record submit action in PON_ACTION_HISTORY table
2312 add_action_history(p_auction_header_id,
2313 p_transaction_id,
2314 'AWARD_APPROVAL_SUBMIT',
2315 p_note_to_approvers,
2316 SYSDATE,
2317 p_user_id,
2318 p_user_name);
2319
2320 -- generate the workflow item key
2321 -- make the workflow item key of the format '<auction_header_id>-<transaction_id>
2322 l_item_key := TO_CHAR(p_auction_header_id) || '-' || p_transaction_id;
2323
2324 -- change the award approval status to INPROCESS
2325 UPDATE pon_auction_headers_all
2326 SET
2327 award_approval_status = 'INPROCESS',
2328 wf_award_approval_item_key = l_item_key,
2329 last_updated_by = p_user_id,
2330 last_update_date = SYSDATE
2331 WHERE auction_header_id = p_auction_header_id;
2332
2333 -- start a new workflow process for the transaction
2334 start_workflow_process(p_auction_header_id,
2335 p_transaction_id,
2336 l_item_key,
2337 p_note_to_approvers,
2338 p_first_authority_approver_id);
2339
2340 -- Raise Business Event
2341 PON_BIZ_EVENTS_PVT.RAISE_NEG_AWD_APPR_STRT_EVENT(
2342 p_api_version => 1.0 ,
2343 p_init_msg_list => FND_API.G_FALSE,
2344 p_commit => FND_API.G_FALSE,
2345 p_auction_header_id => p_auction_header_id,
2346 x_return_status => x_return_status,
2347 x_msg_count => x_msg_count,
2348 x_msg_data => x_msg_data);
2349
2350 p_error_message := NULL;
2351
2352 EXCEPTION
2353 WHEN OTHERS THEN
2354 -- reset output variables
2355 p_error_message := NULL;
2356
2357 l_error_code := SQLCODE;
2358 IF l_error_code <= -20000 AND l_error_code >= -20999 THEN
2359 trim_error_code(l_error_code, SQLERRM, p_error_message);
2360 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
2361 ELSE
2362 -- raise all Oracle-predefined and user-defined exceptions
2363 RAISE;
2364 END IF;
2365 END submit_for_approval;
2366
2367 /*
2368 Makes an approval decision on a award on behalf of the specified user.
2369
2370 p_decision can be either APPROVE or REJECT.
2371 */
2372 PROCEDURE make_approval_decision(p_auction_header_id IN NUMBER,
2373 p_user_name IN VARCHAR2,
2374 p_decision IN VARCHAR2,
2375 p_note_to_buyer IN VARCHAR2,
2376 p_error_message OUT NOCOPY VARCHAR2) IS
2377 BEGIN
2378
2379 make_approval_decision(p_auction_header_id, p_user_name, p_decision, p_note_to_buyer, NULL, p_error_message);
2380
2381 END make_approval_decision;
2382
2383 /*
2384 Makes an approval decision on a transaction on behalf of the specified user.
2385 */
2386 PROCEDURE make_approval_decision(p_auction_header_id IN NUMBER,
2387 p_user_name IN VARCHAR2,
2388 p_decision IN VARCHAR2,
2389 p_note_to_buyer IN VARCHAR2,
2390 p_forwardee_user_name IN VARCHAR2,
2391 p_error_message OUT NOCOPY VARCHAR2) IS
2392 l_notification_id NUMBER;
2393 l_item_key VARCHAR2(240);
2394 l_notification_found BOOLEAN;
2395
2396 l_error_code NUMBER;
2397
2398 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2399 l_exception_log_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
2400 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
2401 l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.MAKE_APPROVAL_DECISION';
2402 BEGIN
2403
2404 SELECT wf_award_approval_item_key
2405 INTO l_item_key
2406 FROM pon_auction_headers_all
2407 WHERE auction_header_id = p_auction_header_id;
2408
2409 -- try to obtain the id of the notification to which the user is responding to
2410 BEGIN
2411 SELECT notification_id
2412 INTO l_notification_id
2413 FROM wf_item_activity_statuses
2414 WHERE
2415 assigned_user = p_user_name
2416 AND item_type = AWARD_APPROVAL_WF_ITEM_TYPE
2417 AND item_key = l_item_key
2418 AND activity_status = 'NOTIFIED';
2419
2420 l_notification_found := TRUE;
2421 EXCEPTION
2422 WHEN no_data_found THEN
2423 l_notification_found := FALSE;
2424 END;
2425
2426 -- if the notification was found, then respond to it with user's decision
2427 IF l_notification_found THEN
2428 wf_notification.SetAttrText(l_notification_id, 'RESULT', p_decision);
2429 wf_notification.SetAttrText(l_notification_id, 'NOTE_TO_BUYER', PON_AUCTION_PKG.replaceHtmlChars(p_note_to_buyer));
2430 wf_notification.SetAttrText(l_notification_id, 'APPROVER_FORWARDEE_USER', p_forwardee_user_name);
2431 wf_notification.respond(l_notification_id, p_decision, p_user_name);
2432 -- otherwise, raise an exception that the notification has already been responded to
2433 ELSE
2434 raise_application_error(-20001, PON_AUCTION_PKG.getMessage('PON_NOTIF_ALREADY_RESPONDED'));
2435 END IF;
2436
2437 p_error_message := NULL;
2438
2439 EXCEPTION
2440 WHEN OTHERS THEN
2441 -- reset output variables
2442 p_error_message := NULL;
2443
2444 l_error_code := SQLCODE;
2445 IF l_error_code <= -20000 AND l_error_code >= -20999 THEN
2446 trim_error_code(l_error_code, SQLERRM, p_error_message);
2447 log_string(l_exception_log_level, l_current_log_level, l_module_name, SQLERRM);
2448 ELSE
2449 -- raise all Oracle-predefined and user-defined exceptions
2450 RAISE;
2451 END IF;
2452 END make_approval_decision;
2453
2454 /*
2455 Returns true if p_approver1 and p_approver2 match; false otherwise.
2456 */
2457 FUNCTION approvers_match(p_approver1 ame_util.approverRecord,
2458 p_approver2 ame_util.approverRecord) RETURN BOOLEAN IS
2459 BEGIN
2460
2461 RETURN
2462 NVL(((p_approver1.user_id IS NULL AND p_approver2.user_id IS NULL) OR
2463 p_approver1.user_id = p_approver2.user_id) AND
2464 ((p_approver1.person_id IS NULL AND p_approver2.person_id IS NULL) OR
2465 p_approver1.person_id = p_approver2.person_id) AND
2466 ((p_approver1.api_insertion IS NULL AND p_approver2.api_insertion IS NULL) OR
2467 p_approver1.api_insertion = p_approver2.api_insertion) AND
2468 ((p_approver1.authority IS NULL AND p_approver2.authority IS NULL) OR
2469 p_approver1.authority = p_approver2.authority), FALSE);
2470
2471 END approvers_match;
2472
2473 /*
2474 Reconstructs an ame_util.approverRecord record from a string.
2475 */
2476 PROCEDURE parse_approver(p_approver_string IN VARCHAR2,
2477 p_approver OUT NOCOPY ame_util.approverRecord) IS
2478 l_start_index INTEGER;
2479 l_end_index INTEGER;
2480 l_delimiter_length INTEGER;
2481 l_field_value VARCHAR2(80);
2482 BEGIN
2483
2484 l_delimiter_length := LENGTHB(APPROVER_FIELD_DELIMITER);
2485
2486 l_start_index := 1;
2487 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2488 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2489 p_approver.user_id := parse_number_field(l_field_value);
2490
2491 l_start_index := l_end_index + l_delimiter_length;
2492 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2493 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2494 p_approver.person_id := parse_number_field(l_field_value);
2495
2496 l_start_index := l_end_index + l_delimiter_length;
2497 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2498 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2499 p_approver.api_insertion := convert_to_ame_api_insertion(parse_number_field(l_field_value));
2500
2501 l_start_index := l_end_index + l_delimiter_length;
2502 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2503 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2504 p_approver.authority := convert_to_ame_authority(parse_number_field(l_field_value));
2505
2506 l_start_index := l_end_index + l_delimiter_length;
2507 l_field_value := SUBSTRB(p_approver_string, l_start_index);
2508 p_approver.approval_status := convert_to_ame_approval_status(parse_number_field(l_field_value));
2509
2510 END parse_approver;
2511
2512
2513 FUNCTION is_old_approver_record( p_approver_string IN VARCHAR2 ) RETURN BOOLEAN IS
2514
2515 l_start_index INTEGER;
2516 l_end_index INTEGER;
2517 l_approver_string_length INTEGER;
2518 l_delimiter_length INTEGER;
2519 OLD_APPROVER_FIELD_DELIMITER VARCHAR2(4);
2520 l_delimiter_count INTEGER;
2521
2522 BEGIN
2523
2524 OLD_APPROVER_FIELD_DELIMITER := ',,';
2525 l_delimiter_length := LENGTHB(OLD_APPROVER_FIELD_DELIMITER);
2526
2527 l_approver_string_length := LENGTHB(p_approver_string);
2528 l_delimiter_length := LENGTHB(OLD_APPROVER_FIELD_DELIMITER);
2529
2530 l_start_index := 1;
2531 l_delimiter_count := 0;
2532
2533 WHILE l_start_index <= l_approver_string_length LOOP
2534
2535 l_end_index := INSTRB(p_approver_string, OLD_APPROVER_FIELD_DELIMITER, l_start_index, 1);
2536
2537 IF l_end_index = 0 THEN
2538 l_end_index := l_approver_string_length;
2539 END IF;
2540
2541 l_start_index := l_end_index + l_delimiter_length;
2542 l_delimiter_count := l_delimiter_count + 1;
2543
2544 END LOOP;
2545
2546 IF l_delimiter_count = 4 THEN
2547 return TRUE;
2548 ELSE
2549 return FALSE;
2550 END IF;
2551
2552 END is_old_approver_record;
2553
2554 /*
2555 Reconstructs an ame_util.approverRecord2 record from a string.
2556 */
2557 PROCEDURE parse_ame_approver( p_approver_string IN VARCHAR2, p_approver OUT NOCOPY ame_util.approverRecord2 ) IS
2558 l_start_index INTEGER;
2559 l_end_index INTEGER;
2560 l_delimiter_length INTEGER;
2561 l_field_value VARCHAR2(80);
2562 BEGIN
2563
2564 APPROVER_FIELD_DELIMITER := getAMEFieldDelimiter();
2565
2566 l_delimiter_length := LENGTHB(APPROVER_FIELD_DELIMITER);
2567
2568 l_start_index := 1;
2569 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2570 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2571 p_approver.name := l_field_value;
2572
2573 l_start_index := l_end_index + l_delimiter_length;
2574 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2575 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2576 p_approver.orig_system := l_field_value;
2577
2578 l_start_index := l_end_index + l_delimiter_length;
2579 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2580 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2581 p_approver.orig_system_id := parse_number_field(l_field_value);
2582
2583 l_start_index := l_end_index + l_delimiter_length;
2584 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2585 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2586 p_approver.display_name := l_field_value;
2587
2588 l_start_index := l_end_index + l_delimiter_length;
2589 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2590 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2591 p_approver.approver_category := l_field_value;
2592
2593 l_start_index := l_end_index + l_delimiter_length;
2594 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2595 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2596 p_approver.api_insertion := convert_to_ame_api_insertion(parse_number_field(l_field_value));
2597
2598 l_start_index := l_end_index + l_delimiter_length;
2599 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2600 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2601 p_approver.authority := convert_to_ame_authority(parse_number_field(l_field_value));
2602
2603 l_start_index := l_end_index + l_delimiter_length;
2604 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2605 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2606 p_approver.approval_status := convert_to_ame_approval_status(parse_number_field(l_field_value));
2607
2608 l_start_index := l_end_index + l_delimiter_length;
2609 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2610 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2611 p_approver.action_type_id := parse_number_field(l_field_value);
2612
2613 l_start_index := l_end_index + l_delimiter_length;
2614 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2615 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2616 p_approver.group_or_chain_id := parse_number_field(l_field_value);
2617
2618 l_start_index := l_end_index + l_delimiter_length;
2619 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2620 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2621 p_approver.occurrence := parse_number_field(l_field_value);
2622
2623 l_start_index := l_end_index + l_delimiter_length;
2624 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2625 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2626 p_approver.source := l_field_value;
2627
2628 l_start_index := l_end_index + l_delimiter_length;
2629 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2630 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2631 p_approver.item_class := l_field_value;
2632
2633 l_start_index := l_end_index + l_delimiter_length;
2634 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2635 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2636 p_approver.item_id := l_field_value;
2637
2638 l_start_index := l_end_index + l_delimiter_length;
2639 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2640 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2641 p_approver.item_class_order_number := parse_number_field(l_field_value);
2642
2643 l_start_index := l_end_index + l_delimiter_length;
2644 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2645 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2646 p_approver.item_order_number := parse_number_field(l_field_value);
2647
2648 l_start_index := l_end_index + l_delimiter_length;
2649 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2650 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2651 p_approver.sub_list_order_number := parse_number_field(l_field_value);
2652
2653 l_start_index := l_end_index + l_delimiter_length;
2654 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2655 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2656 p_approver.action_type_order_number := parse_number_field(l_field_value);
2657
2658 l_start_index := l_end_index + l_delimiter_length;
2659 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2660 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2661 p_approver.group_or_chain_order_number := parse_number_field(l_field_value);
2662
2663 l_start_index := l_end_index + l_delimiter_length;
2664 l_end_index := INSTRB(p_approver_string, APPROVER_FIELD_DELIMITER, l_start_index, 1);
2665 l_field_value := SUBSTRB(p_approver_string, l_start_index, l_end_index - l_start_index);
2666 p_approver.member_order_number := parse_number_field(l_field_value);
2667
2668 l_start_index := l_end_index + l_delimiter_length;
2669 l_field_value := SUBSTRB(p_approver_string, l_start_index);
2670 p_approver.approver_order_number := parse_number_field(l_field_value);
2671
2672 END parse_ame_approver;
2673
2674
2675 /*
2676 Creates a string representation of an ame_util.approverRecord record.
2677 */
2678 PROCEDURE format_approver(p_approver IN ame_util.approverRecord,
2679 p_approver_string OUT NOCOPY VARCHAR2) IS
2680 BEGIN
2681
2682 p_approver_string :=
2683 p_approver_string ||
2684 p_approver.user_id || APPROVER_FIELD_DELIMITER ||
2685 p_approver.person_id || APPROVER_FIELD_DELIMITER ||
2686 convert_to_pon_api_insertion(p_approver.api_insertion) || APPROVER_FIELD_DELIMITER ||
2687 convert_to_pon_authority(p_approver.authority) || APPROVER_FIELD_DELIMITER ||
2688 convert_to_pon_approval_status(p_approver.approval_status);
2689
2690 END format_approver;
2691
2692 /*
2693 Creates a string representation of an ame_util.approverRecord record.
2694 */
2695 FUNCTION format_ame_approver(p_approver IN ame_util.approverRecord2) RETURN VARCHAR2 IS
2696
2697 l_approver_string VARCHAR2(1500);
2698
2699 BEGIN
2700
2701 APPROVER_FIELD_DELIMITER := getAMEFieldDelimiter();
2702
2703 l_approver_string :=
2704 p_approver.name || APPROVER_FIELD_DELIMITER ||
2705 p_approver.orig_system || APPROVER_FIELD_DELIMITER ||
2706 p_approver.orig_system_id || APPROVER_FIELD_DELIMITER ||
2707 p_approver.display_name || APPROVER_FIELD_DELIMITER ||
2708 p_approver.approver_category || APPROVER_FIELD_DELIMITER ||
2709 convert_to_pon_api_insertion(p_approver.api_insertion) || APPROVER_FIELD_DELIMITER ||
2710 convert_to_pon_authority(p_approver.authority) || APPROVER_FIELD_DELIMITER ||
2711 convert_to_pon_approval_status(p_approver.approval_status) || APPROVER_FIELD_DELIMITER ||
2712 p_approver.action_type_id || APPROVER_FIELD_DELIMITER ||
2713 p_approver.group_or_chain_id || APPROVER_FIELD_DELIMITER ||
2714 p_approver.occurrence || APPROVER_FIELD_DELIMITER ||
2715 p_approver.source || APPROVER_FIELD_DELIMITER ||
2716 p_approver.item_class || APPROVER_FIELD_DELIMITER ||
2717 p_approver.item_id || APPROVER_FIELD_DELIMITER ||
2718 p_approver.item_class_order_number || APPROVER_FIELD_DELIMITER ||
2719 p_approver.item_order_number || APPROVER_FIELD_DELIMITER ||
2720 p_approver.sub_list_order_number || APPROVER_FIELD_DELIMITER ||
2721 p_approver.action_type_order_number || APPROVER_FIELD_DELIMITER ||
2722 p_approver.group_or_chain_order_number || APPROVER_FIELD_DELIMITER ||
2723 p_approver.member_order_number || APPROVER_FIELD_DELIMITER ||
2724 p_approver.approver_order_number;
2725
2726 return l_approver_string;
2727
2728 END format_ame_approver;
2729
2730 /*
2731 Reconstructs an ame_util.approversTable table from a string.
2732 */
2733 PROCEDURE parse_approver_list(p_approver_list_string IN VARCHAR2,
2734 p_approver_list OUT NOCOPY ame_util.approversTable) IS
2735 l_start_index INTEGER;
2736 l_end_index INTEGER;
2737 l_list_string_length INTEGER;
2738 l_delimiter_length INTEGER;
2739 l_list_index INTEGER;
2740 l_approver_string VARCHAR2(240);
2741 l_approver ame_util.approverRecord;
2742 BEGIN
2743
2744 l_list_string_length := LENGTHB(p_approver_list_string);
2745 l_delimiter_length := LENGTHB(APPROVER_RECORD_DELIMITER);
2746
2747 l_list_index := 1;
2748 l_start_index := 1;
2749
2750 WHILE l_start_index <= l_list_string_length LOOP
2751 l_end_index := INSTRB(p_approver_list_string, APPROVER_RECORD_DELIMITER, l_start_index, 1);
2752 IF l_end_index = 0 THEN
2753 l_end_index := l_list_string_length + 1;
2754 END IF;
2755
2756 l_approver_string := SUBSTRB(p_approver_list_string, l_start_index, l_end_index - l_start_index);
2757 parse_approver(l_approver_string, l_approver);
2758 p_approver_list(l_list_index) := l_approver;
2759 l_list_index := l_list_index + 1;
2760
2761 l_start_index := l_end_index + l_delimiter_length;
2762 END LOOP;
2763
2764 END parse_approver_list;
2765
2766 /*
2767 Creates a string representation of an ame_util.approversTable table.
2768 */
2769 PROCEDURE format_approver_list(p_approver_list IN ame_util.approversTable,
2770 p_approver_list_string OUT NOCOPY VARCHAR2) IS
2771 l_approver_string VARCHAR2(240);
2772 BEGIN
2773
2774 FOR i IN 1 .. p_approver_list.COUNT LOOP
2775 l_approver_string := '';
2776 format_approver(p_approver_list(i), l_approver_string);
2777
2778 IF i < p_approver_list.COUNT THEN
2779 p_approver_list_string := p_approver_list_string || l_approver_string || APPROVER_RECORD_DELIMITER;
2780 ELSE
2781 p_approver_list_string := p_approver_list_string || l_approver_string;
2782 END IF;
2783 END LOOP;
2784
2785 END format_approver_list;
2786
2787 /*
2788 Creates a string representation of an ame_util.approversTable2 table.
2789 */
2790 FUNCTION format_ame_approver_list( p_approver_list IN ame_util.approversTable2) RETURN VARCHAR2 IS
2791 l_approver_list_string VARCHAR2(32000);
2792 l_approver_string VARCHAR2(1500);
2793 BEGIN
2794
2795 APPROVER_RECORD_DELIMITER := getAMERecordDelimiter();
2796
2797 FOR i IN 1 .. p_approver_list.COUNT LOOP
2798 l_approver_string := '';
2799 l_approver_string := format_ame_approver( p_approver_list(i));
2800
2801 IF i < p_approver_list.COUNT THEN
2802 l_approver_list_string := l_approver_list_string || l_approver_string || APPROVER_RECORD_DELIMITER;
2803 ELSE
2804 l_approver_list_string := l_approver_list_string || l_approver_string;
2805 END IF;
2806 END LOOP;
2807
2808 return l_approver_list_string;
2809
2810 END format_ame_approver_list;
2811
2812
2813 /*
2814 Converts the specified PON_AWARD_APPROVAL_PKG api_insertion code to a corresponding ame_util api_insertion code.
2815 */
2816 FUNCTION convert_to_ame_api_insertion(p_pon_api_insertion IN NUMBER) RETURN VARCHAR2 IS
2817 BEGIN
2818 IF p_pon_api_insertion = API_AUTHORITY_INSERTION THEN
2819 RETURN ame_util.apiAuthorityInsertion;
2820 ELSIF p_pon_api_insertion = API_INSERTION THEN
2821 RETURN ame_util.apiInsertion;
2822 ELSIF p_pon_api_insertion = OAM_GENERATED THEN
2823 RETURN ame_util.oamGenerated;
2824 ELSE
2825 RETURN NULL;
2826 END IF;
2827 END convert_to_ame_api_insertion;
2828
2829 /*
2830 Converts the specified ame_util api_insertion code to a corresponding PON_AWARD_APPROVAL_PKG api_insertion code.
2831 */
2832 FUNCTION convert_to_pon_api_insertion(p_ame_api_insertion IN VARCHAR2) RETURN NUMBER IS
2833 BEGIN
2834 IF p_ame_api_insertion = ame_util.apiAuthorityInsertion THEN
2835 RETURN API_AUTHORITY_INSERTION;
2836 ELSIF p_ame_api_insertion = ame_util.apiInsertion THEN
2837 RETURN API_INSERTION;
2838 ELSIF p_ame_api_insertion = ame_util.oamGenerated THEN
2839 RETURN OAM_GENERATED;
2840 ELSE
2841 RETURN NULL;
2842 END IF;
2843 END convert_to_pon_api_insertion;
2844
2845 /*
2846 Converts the specified PON_AWARD_APPROVAL_PKG authority code to a corresponding ame_util authority code.
2847 */
2848 FUNCTION convert_to_ame_authority(p_pon_authority IN NUMBER) RETURN VARCHAR2 IS
2849 BEGIN
2850 IF p_pon_authority = PRE_APPROVER THEN
2851 RETURN ame_util.preApprover;
2852 ELSIF p_pon_authority = AUTHORITY_APPROVER THEN
2853 RETURN ame_util.authorityApprover;
2854 ELSIF p_pon_authority = POST_APPROVER THEN
2855 RETURN ame_util.postApprover;
2856 ELSE
2857 RETURN NULL;
2858 END IF;
2859 END convert_to_ame_authority;
2860
2861 /*
2862 Converts the specified ame_util authority code to a corresponding PON_AWARD_APPROVAL_PKG authority code.
2863 */
2864 FUNCTION convert_to_pon_authority(p_ame_authority IN VARCHAR2) RETURN NUMBER IS
2865 BEGIN
2866 IF p_ame_authority = ame_util.preApprover THEN
2867 RETURN PRE_APPROVER;
2868 ELSIF p_ame_authority = ame_util.authorityApprover THEN
2869 RETURN AUTHORITY_APPROVER;
2870 ELSIF p_ame_authority = ame_util.postApprover THEN
2871 RETURN POST_APPROVER;
2872 ELSE
2873 RETURN NULL;
2874 END IF;
2875 END convert_to_pon_authority;
2876
2877 /*
2878 Converts the specified PON_AWARD_APPROVAL_PKG approval_status code to a corresponding ame_util approval_status code.
2879 */
2880 FUNCTION convert_to_ame_approval_status(p_pon_approval_status IN NUMBER) RETURN VARCHAR2 IS
2881 BEGIN
2882 IF p_pon_approval_status = APPROVE_AND_FORWARD_STATUS THEN
2883 RETURN ame_util.approveAndForwardStatus;
2884 ELSIF p_pon_approval_status = APPROVED_STATUS THEN
2885 RETURN ame_util.approvedStatus;
2886 ELSIF p_pon_approval_status = CLEAR_EXCEPTIONS_STATUS THEN
2887 RETURN ame_util.clearExceptionsStatus;
2888 ELSIF p_pon_approval_status = EXCEPTION_STATUS THEN
2889 RETURN ame_util.exceptionStatus;
2890 ELSIF p_pon_approval_status = FORWARD_STATUS THEN
2891 RETURN ame_util.forwardStatus;
2892 ELSIF p_pon_approval_status = NO_RESPONSE_STATUS THEN
2893 RETURN ame_util.noResponseStatus;
2894 /*
2895 ELSIF p_pon_approval_status = NOTIFIED_STATUS THEN
2896 RETURN ame_util.notifiedStatus;
2897 */
2898 ELSIF p_pon_approval_status = REJECT_STATUS THEN
2899 RETURN ame_util.rejectStatus;
2900 /*
2901 ELSIF p_pon_approval_status = REPEATED_STATUS THEN
2902 RETURN ame_util.repeatedStatus;
2903 ELSIF p_pon_approval_status = SUPPRESSED_STATUS THEN
2904 RETURN ame_util.suppressedStatus;
2905 */
2906 ELSIF p_pon_approval_status = NULL_STATUS THEN
2907 RETURN ame_util.nullStatus;
2908 ELSE
2909 RETURN NULL;
2910 END IF;
2911 END convert_to_ame_approval_status;
2912
2913 /*
2914 Converts the specified ame_util approval_status code to a corresponding PON_AWARD_APPROVAL_PKG approval_status code.
2915 */
2916 FUNCTION convert_to_pon_approval_status(p_ame_approval_status IN VARCHAR2) RETURN NUMBER IS
2917 BEGIN
2918 IF p_ame_approval_status = ame_util.approveAndForwardStatus THEN
2919 RETURN APPROVE_AND_FORWARD_STATUS;
2920 ELSIF p_ame_approval_status = ame_util.approvedStatus THEN
2921 RETURN APPROVED_STATUS;
2922 ELSIF p_ame_approval_status = ame_util.clearExceptionsStatus THEN
2923 RETURN CLEAR_EXCEPTIONS_STATUS;
2924 ELSIF p_ame_approval_status = ame_util.exceptionStatus THEN
2925 RETURN EXCEPTION_STATUS;
2926 ELSIF p_ame_approval_status = ame_util.forwardStatus THEN
2927 RETURN FORWARD_STATUS;
2928 ELSIF p_ame_approval_status = ame_util.noResponseStatus THEN
2929 RETURN NO_RESPONSE_STATUS;
2930 /*
2931 ELSIF p_ame_approval_status = ame_util.notifiedStatus THEN
2932 RETURN NOTIFIED_STATUS;
2933 */
2934 ELSIF p_ame_approval_status = ame_util.rejectStatus THEN
2935 RETURN REJECT_STATUS;
2936 /*
2937 ELSIF p_ame_approval_status = ame_util.repeatedStatus THEN
2938 RETURN REPEATED_STATUS;
2939 ELSIF p_ame_approval_status = ame_util.suppressedStatus THEN
2940 RETURN SUPPRESSED_STATUS;
2941 */
2942 ELSIF p_ame_approval_status IS NULL THEN
2943 RETURN NULL_STATUS;
2944 ELSE
2945 RETURN NULL;
2946 END IF;
2947 END convert_to_pon_approval_status;
2948
2949 /*
2950 Parses a string specifying a number. If the string is null, null is returned.
2951 */
2952 FUNCTION parse_number_field(p_string VARCHAR2) RETURN NUMBER IS
2953 BEGIN
2954 IF p_string IS NULL THEN
2955 RETURN NULL;
2956 ELSE
2957 RETURN TO_NUMBER(p_string);
2958 END IF;
2959 END parse_number_field;
2960
2961 /*
2962 Retrieves the display name for the specified user.
2963 */
2964 FUNCTION get_display_name_for_user(p_user_id IN NUMBER) RETURN VARCHAR2 IS
2965 l_display_name VARCHAR2(240);
2966 BEGIN
2967
2968 BEGIN
2969 -- if the full name is null, use the user name
2970 SELECT NVL(persons.full_name, users.user_name)
2971 INTO l_display_name
2972 FROM
2973 per_all_people_f persons,
2974 fnd_user users
2975 WHERE
2976 users.employee_id = persons.person_id(+)
2977 AND users.user_id = p_user_id
2978 AND TRUNC(sysdate) between persons.effective_start_date and persons.effective_end_date;
2979 EXCEPTION
2980 WHEN no_data_found OR too_many_rows THEN
2981 NULL;
2982 END;
2983
2984 RETURN l_display_name;
2985
2986 END get_display_name_for_user;
2987
2988 /*
2989 Retrieves the display name for the specified user.
2990 */
2991 FUNCTION get_display_name_for_user(p_user_name IN VARCHAR2) RETURN VARCHAR2 IS
2992 l_display_name VARCHAR2(240);
2993 BEGIN
2994
2995 BEGIN
2996 -- if the full name is null, use the user name
2997 SELECT NVL(persons.full_name, users.user_name)
2998 INTO l_display_name
2999 FROM
3000 per_all_people_f persons,
3001 fnd_user users
3002 WHERE
3003 users.employee_id = persons.person_id(+)
3004 AND users.user_name = p_user_name
3005 AND TRUNC(sysdate) between persons.effective_start_date and persons.effective_end_date;
3006 EXCEPTION
3007 WHEN no_data_found OR too_many_rows THEN
3008 NULL;
3009 END;
3010
3011 RETURN l_display_name;
3012
3013 END get_display_name_for_user;
3014
3015 /*
3016 Retrieves the display name for the specified person.
3017 */
3018 FUNCTION get_display_name_for_person(p_person_id IN NUMBER) RETURN VARCHAR2 IS
3019 l_display_name VARCHAR2(240);
3020 BEGIN
3021
3022 BEGIN
3023 SELECT full_name
3024 INTO l_display_name
3025 FROM per_all_people_f
3026 WHERE person_id = p_person_id;
3027 EXCEPTION
3028 WHEN no_data_found OR too_many_rows THEN
3029 NULL;
3030 END;
3031
3032 RETURN l_display_name;
3033
3034 END get_display_name_for_person;
3035
3036 /*
3037 Retrieves employee information for the specified user.
3038 */
3039 PROCEDURE get_employee_info_for_user(p_user_id IN NUMBER, p_employee OUT NOCOPY employeeRecord) IS
3040 BEGIN
3041
3042 p_employee := nullEmployeeRecord;
3043
3044 SELECT
3045 users.user_id,
3046 users.user_name,
3047 --emp.person_id
3048 users.employee_id
3049 INTO
3050 p_employee.user_id,
3051 p_employee.user_name,
3052 p_employee.person_id
3053 FROM
3054 --pon_employees_current_v emp,
3055 fnd_user users
3056 WHERE
3057 -- emp.person_id = users.employee_id
3058 users.user_id = p_user_id
3059 AND users.start_date <= SYSDATE
3060 AND NVL(users.end_date, SYSDATE) >= SYSDATE;
3061
3062 EXCEPTION
3063 WHEN no_data_found OR too_many_rows THEN
3064 NULL;
3065 END get_employee_info_for_user;
3066
3067 /*
3068 Retrieves employee information for the specified user.
3069 */
3070 PROCEDURE get_employee_info_for_user(p_user_name IN VARCHAR2, p_employee OUT NOCOPY employeeRecord) IS
3071 BEGIN
3072
3073 p_employee := nullEmployeeRecord;
3074
3075 SELECT
3076 users.user_id,
3077 users.user_name,
3078 --emp.person_id
3079 users.employee_id
3080 INTO
3081 p_employee.user_id,
3082 p_employee.user_name,
3083 p_employee.person_id
3084 FROM
3085 -- pon_employees_current_v emp,
3086 fnd_user users
3087 WHERE
3088 -- emp.person_id = users.employee_id
3089 users.user_name = p_user_name
3090 AND users.start_date <= SYSDATE
3091 AND NVL(users.end_date, SYSDATE) >= SYSDATE;
3092
3093 EXCEPTION
3094 WHEN no_data_found OR too_many_rows THEN
3095 NULL;
3096 END get_employee_info_for_user;
3097
3098 /*
3099 Retrieves employee information for the specified person.
3100 */
3101 PROCEDURE get_employee_info_for_person(p_person_id IN NUMBER, p_employee OUT NOCOPY employeeRecord) IS
3102 BEGIN
3103
3104 p_employee := nullEmployeeRecord;
3105
3106 SELECT
3107 users.user_id,
3108 users.user_name,
3109 -- emp.person_id
3110 users.employee_id
3111 INTO
3112 p_employee.user_id,
3113 p_employee.user_name,
3114 p_employee.person_id
3115 FROM
3116 -- pon_employees_current_v emp,
3117 fnd_user users
3118 WHERE
3119 -- emp.person_id = users.employee_id
3120 users.employee_id = p_person_id
3121 AND users.start_date <= SYSDATE
3122 AND NVL(users.end_date, SYSDATE) >= SYSDATE
3123 AND rownum = 1;
3124
3125 EXCEPTION
3126 WHEN no_data_found OR too_many_rows THEN
3127 p_employee.user_id := NULL;
3128 p_employee.user_name := NULL;
3129 p_employee.person_id := NULL;
3130 END get_employee_info_for_person;
3131
3132 /*
3133 Removes the 'ORA<p_error_code>:' prefix in p_error_message_in if one exists. Otherwise, does nothing.
3134
3135 NOTE: this procedure works as long as p_error_code is negative.
3136 */
3137 PROCEDURE trim_error_code(p_error_code IN NUMBER,
3138 p_error_message_in IN VARCHAR2,
3139 p_error_message_out OUT NOCOPY VARCHAR2) IS
3140 BEGIN
3141
3142 IF INSTR(p_error_message_in, 'ORA' || p_error_code || ':') = 1 THEN
3143 p_error_message_out := LTRIM(SUBSTR(p_error_message_in, LENGTH('ORA' || p_error_code || ':') + 1));
3144 ELSE
3145 p_error_message_out := p_error_message_in;
3146 END IF;
3147
3148 END trim_error_code;
3149
3150 /***********************************
3151 DEBUGGING PROCEDURES AND FUNCTIONS
3152 ************************************/
3153
3154 /*
3155 Logs a string for a module using the Logging Framework.
3156
3157 The logging level used is FND_LOG.LEVEL_STATEMENT.
3158 */
3159 PROCEDURE log_string(p_module IN VARCHAR2,
3160 p_string IN VARCHAR2) IS
3161 BEGIN
3162
3163 log_string(FND_LOG.LEVEL_STATEMENT, FND_LOG.G_CURRENT_RUNTIME_LEVEL, p_module, p_string);
3164
3165 END log_string;
3166
3167 /*
3168 Logs a string for a module using the Logging Framework.
3169 */
3170 PROCEDURE log_string(p_level IN NUMBER,
3171 p_current_level IN NUMBER,
3172 p_module IN VARCHAR2,
3173 p_string IN VARCHAR2) IS
3174 BEGIN
3175
3176 IF p_level >= p_current_level THEN
3177 FND_LOG.string(p_level, p_module, p_string);
3178 END IF;
3179
3180 END log_string;
3181
3182
3183 /*
3184 Returns a string representation of an ame_util.insertionsTable.
3185 */
3186 FUNCTION get_insertion_list_string(p_insertion_list IN ame_util.insertionsTable2) RETURN VARCHAR2 IS
3187 l_insertion_list_string VARCHAR2(4000);
3188 BEGIN
3189
3190 FOR i IN 1 .. p_insertion_list.COUNT LOOP
3191
3192 l_insertion_list_string :=
3193 l_insertion_list_string ||
3194 i || ' (' ||
3195 get_api_insertion_string(p_insertion_list(i).api_insertion) || ', ' ||
3196 get_authority_string(p_insertion_list(i).authority) || ', ' ||
3197 get_order_type_string(p_insertion_list(i).order_type) || ', ' ||
3198 get_parameter_string(p_insertion_list(i).parameter) || ')';
3199
3200 IF i < p_insertion_list.COUNT THEN
3201 l_insertion_list_string := l_insertion_list_string || fnd_global.newline;
3202 END IF;
3203
3204 END LOOP;
3205
3206 RETURN l_insertion_list_string;
3207
3208 END get_insertion_list_string;
3209
3210
3211 FUNCTION get_insertion_string(p_approver IN ame_util.approverRecord2, p_order IN ame_util.insertionRecord2) RETURN VARCHAR2 IS
3212 BEGIN
3213
3214 RETURN format_ame_approver(p_approver) || ' ' || '(' || get_order_type_string(p_order.order_type) || ', ' || get_parameter_string(p_order.parameter) || ')';
3215
3216 END get_insertion_string;
3217
3218 /*
3219 Returns a string representation of the value of an ame_util.approverRecord's api_insertion field.
3220 */
3221 FUNCTION get_api_insertion_string(p_api_insertion IN VARCHAR2) RETURN VARCHAR2 IS
3222 BEGIN
3223
3224 IF p_api_insertion = ame_util.oamGenerated THEN
3225 RETURN 'oamGenerated';
3226 ELSIF p_api_insertion = ame_util.apiAuthorityInsertion THEN
3227 RETURN 'apiAuthorityInsertion';
3228 ELSIF p_api_insertion = ame_util.apiInsertion THEN
3229 RETURN 'apiInsertion';
3230 ELSE
3231 RETURN NULL;
3232 END IF;
3233
3234 END get_api_insertion_string;
3235
3236 /*
3237 Returns a string representation of the value of an ame_util.approverRecord's authority field.
3238 */
3239 FUNCTION get_authority_string(p_authority IN VARCHAR2) RETURN VARCHAR2 IS
3240 BEGIN
3241
3242 IF p_authority = ame_util.preApprover THEN
3243 RETURN 'preApprover';
3244 ELSIF p_authority = ame_util.authorityApprover THEN
3245 RETURN 'authorityApprover';
3246 ELSIF p_authority = ame_util.postApprover THEN
3247 RETURN 'postApprover';
3248 ELSE
3249 RETURN NULL;
3250 END IF;
3251
3252 END get_authority_string;
3253
3254 /*
3255 Returns a string representation of the value of an ame_util.approverRecord's approval_status field.
3256 */
3257 FUNCTION get_approval_status_string(p_approval_status IN VARCHAR2) RETURN VARCHAR2 IS
3258 BEGIN
3259
3260 IF p_approval_status = ame_util.approveAndForwardStatus THEN
3261 RETURN 'approveAndForwardStatus';
3262 ELSIF p_approval_status = ame_util.approvedStatus THEN
3263 RETURN 'approvedStatus';
3264 ELSIF p_approval_status = ame_util.clearExceptionsStatus THEN
3265 RETURN 'clearExceptionsStatus';
3266 ELSIF p_approval_status = ame_util.exceptionStatus THEN
3267 RETURN 'exceptionStatus';
3268 ELSIF p_approval_status = ame_util.forwardStatus THEN
3269 RETURN 'forwardStatus';
3270 ELSIF p_approval_status = ame_util.noResponseStatus THEN
3271 RETURN 'noResponseStatus';
3272 /*
3273 ELSIF p_approval_status = ame_util.notifiedStatus THEN
3274 RETURN 'notifiedStatus';
3275 */
3276 ELSIF p_approval_status = ame_util.rejectStatus THEN
3277 RETURN 'rejectStatus';
3278 /*
3279 ELSIF p_approval_status = ame_util.repeatedStatus THEN
3280 RETURN 'repeatedStatus';
3281 ELSIF p_approval_status = ame_util.suppressedStatus THEN
3282 RETURN 'suppressedStatus';
3283 */
3284 ELSIF p_approval_status IS NULL THEN
3285 RETURN 'nullStatus';
3286 ELSE
3287 RETURN p_approval_status;
3288 END IF;
3289
3290 END get_approval_status_string;
3291
3292 /*
3293 Returns a string representation of the value of an ame_util.orderRecord's or ame_util.insertionRecord's order_type field.
3294 */
3295 FUNCTION get_order_type_string(p_order_type IN VARCHAR2) RETURN VARCHAR2 IS
3296 BEGIN
3297
3298 IF p_order_type = ame_util.absoluteOrder THEN
3299 RETURN 'absoluteOrder';
3300 ELSIF p_order_type = ame_util.afterApprover THEN
3301 RETURN 'afterApprover';
3302 ELSIF p_order_type = ame_util.beforeApprover THEN
3303 RETURN 'beforeApprover';
3304 ELSIF p_order_type = ame_util.firstAuthority THEN
3305 RETURN 'firstAuthority';
3306 ELSIF p_order_type = ame_util.firstPostApprover THEN
3307 RETURN 'firstPostApprover';
3308 ELSIF p_order_type = ame_util.firstPreApprover THEN
3309 RETURN 'firstPreApprover';
3310 ELSIF p_order_type = ame_util.lastPostApprover THEN
3311 RETURN 'lastPostApprover';
3312 ELSIF p_order_type = ame_util.lastPreApprover THEN
3313 RETURN 'lastPreApprover';
3314 ELSIF p_order_type IS NULL THEN
3315 RETURN 'nullOrderType';
3316 ELSE
3317 RETURN p_order_type;
3318 END IF;
3319
3320 END get_order_type_string;
3321
3322 /*
3323 Returns a string representation of the value of an ame_util.orderRecord's or ame_util.insertionRecord's parameter field.
3324 */
3325 FUNCTION get_parameter_string(p_parameter IN VARCHAR2) RETURN VARCHAR2 IS
3326 BEGIN
3327
3328 IF p_parameter IS NOT NULL THEN
3329 RETURN p_parameter;
3330 ELSE
3331 RETURN 'nullParameter';
3332 END IF;
3333
3334 END get_parameter_string;
3335
3336 /*
3337 Makes an approval decision on a award on behalf of the specified user.
3338 */
3339 PROCEDURE user_respond(p_auction_header_id IN NUMBER,
3340 p_user_name IN VARCHAR2,
3341 p_decision IN VARCHAR2,
3342 p_forwardee_user_name IN VARCHAR2) IS
3343 l_result VARCHAR2(2000);
3344 BEGIN
3345
3346 make_approval_decision(p_auction_header_id, p_user_name, p_decision, NULL, p_forwardee_user_name, l_result);
3347
3348 IF l_result IS NOT NULL THEN
3349 raise_application_error(-20001, l_result);
3350 END IF;
3351
3352 END user_respond;
3353
3354 /*
3355 Makes an APPROVE approval decision on a award on behalf of the specified user.
3356 */
3357 PROCEDURE user_approve(p_auction_header_id IN NUMBER,
3358 p_user_name IN VARCHAR2) IS
3359 BEGIN
3360
3361 user_respond(p_auction_header_id, p_user_name, 'APPROVE', NULL);
3362
3363 END user_approve;
3364
3365 /*
3366 Makes a REJECT approval decision on a award on behalf of the specified user.
3367 */
3368 PROCEDURE user_reject(p_auction_header_id IN NUMBER,
3369 p_user_name IN VARCHAR2) IS
3370 BEGIN
3371
3372 user_respond(p_auction_header_id, p_user_name, 'REJECT', NULL);
3373
3374 END user_reject;
3375
3376 /*
3377 Makes a FORWARD approval decision on a award on behalf of the specified user.
3378 */
3379 PROCEDURE user_forward(p_auction_header_id IN NUMBER,
3380 p_user_name IN VARCHAR2,
3381 p_forwardee_user_name IN VARCHAR2) IS
3382 BEGIN
3383
3384 user_respond(p_auction_header_id, p_user_name, 'FORWARD', p_forwardee_user_name);
3385
3386 END user_forward;
3387
3388 /*
3389 Makes an APPROVE_AND_FORWARD approval decision on a award on behalf of the specified user.
3390 */
3391 PROCEDURE user_approve_and_forward(p_auction_header_id IN NUMBER,
3392 p_user_name IN VARCHAR2,
3393 p_forwardee_user_name IN VARCHAR2) IS
3394 BEGIN
3395
3396 user_respond(p_auction_header_id, p_user_name, 'APPROVE_AND_FORWARD', p_forwardee_user_name);
3397
3398 END user_approve_and_forward;
3399
3400
3401 FUNCTION getAMEFieldDelimiter return VARCHAR2 as
3402 BEGIN
3403 --RETURN(fnd_global.local_chr(ascii_chr => 11));
3404 RETURN(fnd_global.local_chr(ascii_chr => 10));
3405 END getAMEFieldDelimiter;
3406
3407 FUNCTION getAMERecordDelimiter return VARCHAR2 as
3408 BEGIN
3409 --RETURN(fnd_global.local_chr(ascii_chr => 12));
3410 RETURN(fnd_global.local_chr(ascii_chr => 13));
3411 END getAMERecordDelimiter;
3412
3413 END PON_AWARD_APPROVAL_PKG;