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