1 PACKAGE BODY OZF_Utility_PVT AS
2 /* $Header: ozfvutlb.pls 120.13.12010000.3 2008/11/24 17:38:22 nirprasa ship $ */
3
4 -- HISTORY
5 --
6 -- 15-Jun-2000 HOLIU Added procedures to get qp details
7 -- 15-Jun-2000 PTENDULK Commented the function is_in_my_area
8 -- as it will be released in R2
9 -- 28-Jun-2000 RCHAHAL Added Qualifier in get_qual_table_name_and_pk.
10 -- 13-Jul-2000 choang Added get_resource_id
11 -- 07-Aug-2000 ptendulk Added procedure Write_Conc_Log
12 -- 08-Aug-2000 ptendulk Modified procedure Write_Conc_Log
13 -- 30-Jan-2001 MPande Modified procedure get_object_name
14 -- 30-Jan-2001 ptendulk Modified Qualifier in get_qual_table_name_and_pk proc.
15 -- 13-Mar-2001 choang Removed extra close cursor code in create_log.
16 -- 03/27/2001 MPANDE MOved 4 Procedures from OZF to OZF
17 -- 03/29/2001 gjoby Added LIST and SQL condtions
18 -- in get_qual_table_name_and_pk
19 -- 29-Mar-2001 ptendulk Modified get_system_status_type proc.
20 ---13-Apr-2001 feliu Modified create_log proc.
21 -- 23-Apr-2001 feliu Modified create_log proc.
22 -- skarumur - 25-apr-2000
23 -- 07-May-2001 choang Added RCAM to get_qual_table_name_and_pk
24 -- 20-May-2001 ptendulk Modified get_system_status_type procedure for Progrozf
25 -- 24-May-2001 feliu Modified create_log proc.
26 -- 13-Jun-2001 ptendulk Added code for MUKUMAR to add validation for EONE
27 -- 15-Jun-2001 choang changed OFFR in get_qual_table_name_and_pk, and PRTN (to PTNR)
28 -- in get_object_name
29 -- 16-Jun-2001 ptendulk Added check_new_status_change procedure to obsolute
30 -- the old check_status_change api
31 -- 19-Jun-2001 ptendulk Modified Approval_Required_Flag function
32 -- 09-Jul-2001 ptendulk Added new function Check_Status_Change
33 -- 13-Sep-2001 slkrishn Added new function for amount rounding based on currency
34 -- 14-Jan-2001 sveerave Added send_wf_standalone_message procedure, and
35 -- Get_Resource_Role procedures for sending standalone mesages.
36 -- 18-Mar-2002 choang Added checkfile to dbdrv
37 -- 27-Mar-2002 dmvincen Added dialog and component validiation.
38 -- 17-May-2002 choang bug 2224836: changed get_sytem_timezone and get_user_timezone
39 -- to use HZ timezone profiles.
40 -- 06-Jun-2002 sveerave Added overloaded check_lookup_exists
41 -- which accepts view_application_id, query from fnd_lookups
42 -- 06-Jun-2002 sveerave Modified previous check_lookup_exists to call
43 -- newly created procedure.
44 -- 17-Jun-2002 sveerave Modified cursor in check_lookup_exists to have p_view_app_id
45 -- as NUMBER
46 -- 10-Jul-2002 musman Modified the procedure check_new_status_change, checking for
47 -- CAPL instead of TAPL if the object type is 'DELV'
48 -- 19-Dec-2002 mayjain Added get_install_info
49 -- 13-Oct-2003 yzhao get_qual_table_name_and_pk() for FUND, changed from ozf_fund_details_v to ozf_funds_all_b
50 -- Wed Oct 29 2003:3/44 PM RSSHARMA Added function get_party_name . Changed get_product_name to include FAMILY in Product level since the lookup
51 -- for product level for lumpsum , scandata and Net Accrual Offers has product level codes as PRODUCT AND FAMILY
52 -- Fri Jan 23 2004:3/20 PM RSSHARMA Added the following fuctions
53 -- 1. get_fnd_lookup_meaning
54 -- 2. get_currency_name
55 -- 3. get_media_name
56 -- 17-FEB-2005 feliu added functions
57 -- get_commited_amount
58 -- get_recal_commited_amount
59 -- get_utilized_amount
60 -- get_paid_amount
61 -- get_earned_amount
62 -- 19-May-05 Sahana Bug4382865: SQL Literal fix
63 -- 12-Jun-05 Ribha #4173825 Modified get_product_name
64 -- 31-Aug-05 mkothari Changed AMS to OZF for forecast tbl
65 -- 17-May-2006 asylvia fixed bug 5226685
66 -- Mon May 22 2006:5/59 PM rssharma Fixed bug # 5213655.Added getAttributeMethods to get AKAttribute long labels.
67 -- 09-Nov-2008 psomyaju Bug 7555174 - APPROVALS FOR 'GLOBAL' OFFERS - NO LEDGER DEFINED
68 -- 09-Nov-2008 psomyaju Bug 7415805 - INT1206.2:AMS:UNABLE TO APPROVE THE OFFER
69 -- 24-Nov-08 nirprasa Bug 7030415 - R12SIP WE CAN'T SETUP CURRENY CONVERSION TYPE FOR SPECIFIC OPERATING UNIT
70 ---------------------------------------------------------------------
71 -- Added for use by bind_parse.
72 TYPE col_val_rec IS RECORD (
73 col_name VARCHAR2(2000),
74 col_op VARCHAR2(10),
75 col_value VARCHAR2(2000) );
76
77 TYPE col_val_tbl IS TABLE OF col_val_rec INDEX BY BINARY_INTEGER;
78
79 ---------------------------------------------------------------------
80 -- FUNCTION
81 -- bind_parse
82 --
83 -- DESCRIPTION
84 -- Given a string containing the WHERE conditions in a WHERE
85 -- clause, return a tuple of column name and column value.
86 -- HISTORY
87 -- 25-Apr-2000 skarumur Created.
88 -- 26-Apr-2000 choang Modified to handle <> conditions.
89 ---------------------------------------------------------------------
90 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
91 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
92 OZF_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
93 G_OZF_APP_ID CONSTANT NUMBER := 682;
94
95 PROCEDURE bind_parse (
96 p_string IN VARCHAR2,
97 x_col_val_tbl OUT NOCOPY col_val_tbl
98 );
99
100
101
102 --======================================================================
103 -- PROCEDURE
104 -- debug_message
105 --
106 -- PURPOSE
107 -- Writes the message to the log file for the spec'd level and module
108 -- if logging is enabled for this level and module
109 --
110 -- HISTORY
111 -- 01-Oct-2003 huili Create.
112 --======================================================================
113 PROCEDURE debug_message (p_log_level IN NUMBER,
114 p_module_name IN VARCHAR2,
115 p_text IN VARCHAR2)
116 IS
117 BEGIN
118 IF( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
119 FND_LOG.STRING(p_log_level, p_module_name, p_text);
120 END IF;
121 END debug_message;
122
123
124 --======================================================================
125 -- PROCEDURE
126 -- log_message
127 --
128 -- PURPOSE
129 -- Writes a message to the log file if this level and module is enabled
130 -- The message gets set previously with FND_MESSAGE.SET_NAME,
131 -- SET_TOKEN, etc.
132 -- The message is popped off the message dictionary stack, if POP_MESSAGE
133 -- is TRUE. Pass FALSE for POP_MESSAGE if the message will also be
134 -- displayed to the user later.
135 -- Example usage:
136 -- FND_MESSAGE.SET_NAME(...); -- Set message
137 -- FND_MESSAGE.SET_TOKEN(...); -- Set token in message
138 -- FND_LOG.MESSAGE(..., FALSE); -- Log message
139 -- FND_MESSAGE.ERROR; -- Display message
140 --
141 -- HISTORY
142 -- 01-Oct-2003 huili Create.
143 --======================================================================
144
145 PROCEDURE log_message(p_log_level IN NUMBER,
146 p_module_name IN VARCHAR2,
147 p_RCS_ID IN VARCHAR2 := NULL,
148 p_pop_message IN BOOLEAN DEFAULT NULL)
149 IS
150 BEGIN
151 IF ( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
152 FND_LOG.MESSAGE(p_log_level, p_module_name, p_pop_message);
153 IF p_RCS_ID IS NOT NULL THEN
154 FND_MESSAGE.SET_TOKEN(G_RCS_ID, p_RCS_ID);
155 END IF;
156 END IF;
157 END log_message;
158
159 --======================================================================
160 -- FUNCTION
161 -- logging_enabled
162 --
163 -- PURPOSE
164 -- Return whether logging is enabled for a particular level
165 --
166 -- HISTORY
167 -- 03-Oct-2003 huili Create.
168 --======================================================================
169 FUNCTION logging_enabled (p_log_level IN NUMBER)
170 RETURN BOOLEAN
171 IS
172 BEGIN
173 RETURN (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
174 END;
175
176
177 ---------------------------------------------------------------------
178 -- FUNCTION
179 -- check_fk_exists
180 --
181 -- HISTORY
182 -- 05/14/99 cklee Created.
183 -- 25-Apr-2000 choang modified to use bind variables.
184 ---------------------------------------------------------------------
185 FUNCTION check_fk_exists(
186 p_table_name IN VARCHAR2,
187 p_pk_name IN VARCHAR2,
188 p_pk_value IN VARCHAR2,
189 p_pk_data_type IN NUMBER := g_number,
190 p_additional_where_clause IN VARCHAR2 := NULL
191 )
192 RETURN VARCHAR2
193 IS
194
195 l_sql VARCHAR2(4000);
196 l_count NUMBER;
197 l_bind_tbl col_val_tbl;
198
199 BEGIN
200 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
201 l_sql := l_sql || ' WHERE ' || UPPER(p_pk_name) || ' = :b1';
202
203 IF p_additional_where_clause IS NOT NULL THEN
204
205 -- Bug4382865: Start SQL Literal Fix
206 bind_parse (p_additional_where_clause, l_bind_tbl);
207
208 -- Support up to 4 WHERE conditions.
209 IF l_bind_tbl.COUNT <= 4 THEN
210 l_sql := l_sql || ' AND ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
211 FOR i IN 2..l_bind_tbl.COUNT LOOP
212 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
213 END LOOP;
214 ELSE
215 -- Exceeded the number of conditions supported
216 -- for bind variables.
217 l_sql := l_sql || ' WHERE ' || p_additional_where_clause;
218 END IF;
219 END IF;
220
221 l_sql := l_sql || ')';
222
223 IF (OZF_DEBUG_HIGH_ON) THEN
224 debug_message('SQL statement: '||l_sql);
225 END IF;
226
227 BEGIN
228 IF p_additional_where_clause IS NOT NULL THEN
229 IF l_bind_tbl.COUNT = 1 THEN
230 EXECUTE IMMEDIATE l_sql INTO l_count
231 USING p_pk_value, l_bind_tbl(1).col_value;
232 ELSIF l_bind_tbl.COUNT = 2 THEN
233 EXECUTE IMMEDIATE l_sql INTO l_count
234 USING p_pk_value, l_bind_tbl(1).col_value, l_bind_tbl(2).col_value;
235 ELSIF l_bind_tbl.COUNT = 3 THEN
236 EXECUTE IMMEDIATE l_sql INTO l_count
237 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
238 ELSIF l_bind_tbl.COUNT = 4 THEN
239 EXECUTE IMMEDIATE l_sql INTO l_count
240 USING p_pk_value, l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
241 ELSE
242 EXECUTE IMMEDIATE l_sql INTO l_count
243 USING p_pk_value;
244 END IF;
245 ELSE
246 EXECUTE IMMEDIATE l_sql INTO l_count
247 USING p_pk_value;
248 END IF;
249 EXCEPTION
250 WHEN NO_DATA_FOUND THEN
251 l_count := 0;
252 END;
253 -- Bug4382865: End SQL Literal Fix
254
255 IF l_count = 0 THEN
256 RETURN FND_API.g_false;
257 ELSE
258 RETURN FND_API.g_true;
259 END IF;
260
261 END check_fk_exists;
262
263
264 ---------------------------------------------------------------------
265 -- FUNCTION
266 -- check_lookup_exists
267 --
268 -- HISTORY
269 -- 05/14/99 cklee Created.
270 -- 25-Apr-2000 choang Use bind variables.
271 -- 07-jun-2002 sveerave if table name is specifically not passed, changes
272 -- are made to call overloaded procedure.
273 ---------------------------------------------------------------------
274 FUNCTION check_lookup_exists(
275 p_lookup_table_name IN VARCHAR2 := g_ozf_lookups,
276 p_lookup_type IN VARCHAR2,
277 p_lookup_code IN VARCHAR2
278 )
279 Return VARCHAR2
280 IS
281
282 l_sql VARCHAR2(4000);
283 l_count NUMBER;
284
285 BEGIN
286
287 IF p_lookup_table_name = g_ozf_lookups THEN
288 return check_lookup_exists (
289 p_lookup_type => p_lookup_type
290 , p_lookup_code => p_lookup_code
291 , p_view_application_id => 682
292 );
293 ELSE
294 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;
295 l_sql := l_sql || ' WHERE LOOKUP_TYPE = :b1';
296 l_sql := l_sql || ' AND LOOKUP_CODE = :b2';
297 l_sql := l_sql || ' AND ENABLED_FLAG = ''Y'')';
298
299 IF (OZF_DEBUG_HIGH_ON) THEN
300 debug_message('SQL statement: '||l_sql);
301 END IF;
302
303 BEGIN
304 EXECUTE IMMEDIATE l_sql INTO l_count
305 USING p_lookup_type, p_lookup_code;
306 EXCEPTION
307 WHEN NO_DATA_FOUND THEN
308 l_count := 0;
309 END;
310 IF l_count = 0 THEN
311 RETURN FND_API.g_false;
312 ELSE
313 RETURN FND_API.g_true;
314 END IF;
315 END IF;
316
317 END check_lookup_exists;
318
319
320
321 ---------------------------------------------------------------------
322 -- FUNCTION
323 -- overloaded check_lookup_exists
324 -- PURPOSE
325 -- This function checks if a lookup_code is valid from fnd_lookups when
326 -- view_application_id is passed in.
327 -- HISTORY
328 -- 07-jun-2002 sveerave created.
329 -- 17-Jun-2002 sveerave Modified cursor to have p_view_app_id as NUMBER
330 ---------------------------------------------------------------------
331 FUNCTION check_lookup_exists(
332 p_lookup_type IN VARCHAR2,
333 p_lookup_code IN VARCHAR2,
334 p_view_application_id IN NUMBER
335 )
336 Return VARCHAR2
337 IS
338 CURSOR cur_check_lookup_exists( p_lookup_type VARCHAR2
339 , p_lookup_code VARCHAR2
340 , p_view_app_id NUMBER) IS
341 SELECT 1 FROM fnd_lookup_values lkup
342 WHERE lkup.LOOKUP_TYPE = p_lookup_type
343 AND lkup.LOOKUP_CODE = p_lookup_code
344 AND lkup.view_application_id = p_view_app_id
345 AND lkup.ENABLED_FLAG = 'Y'
346 AND lkup.language = USERENV('LANG')
347 AND lkup.security_group_id = to_number(decode(substrb(userenv('CLIENT_INFO'),55,1
348 ), ' ', '0'
349 , NULL, '0'
350 , substrb(userenv('CLIENT_INFO'),55,10
351 )
352 )
353 );
354 l_count NUMBER := 0;
355
356 BEGIN
357
358 OPEN cur_check_lookup_exists( p_lookup_type
359 , p_lookup_code
360 , p_view_application_id);
361 FETCH cur_check_lookup_exists INTO l_count;
362 CLOSE cur_check_lookup_exists;
363
364 IF l_count = 0 THEN
365 RETURN FND_API.g_false;
366 ELSE
367 RETURN FND_API.g_true;
368 END IF;
369
370 END check_lookup_exists;
371
372
373 ---------------------------------------------------------------------
374 -- FUNCTION
375 -- check_uniqueness
376 --
377 -- HISTORY
378 -- 05/19/99 cklee Created.
379 -- 25-Apr-2000 choang Use bind_parse to enable use of bind variables.
380 ---------------------------------------------------------------------
381 FUNCTION check_uniqueness(
382 p_table_name IN VARCHAR2,
383 p_where_clause IN VARCHAR2
384 )
385 RETURN VARCHAR2
386 IS
387
388 l_sql VARCHAR2(4000);
389 l_count NUMBER;
390
391 l_bind_tbl col_val_tbl;
392
393 BEGIN
394
395 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
396 -- l_sql := l_sql || ' WHERE ' || p_where_clause;
397
398 bind_parse (p_where_clause, l_bind_tbl);
399
400 --
401 -- choang - 25-Apr-2000
402 -- Support up to 4 WHERE conditions for uniqueness. If
403 -- the number of conditions changes, then must also revise
404 -- the execute portion of the code.
405 IF l_bind_tbl.COUNT <= 4 THEN
406 l_sql := l_sql || ' WHERE ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
407 FOR i IN 2..l_bind_tbl.COUNT LOOP
408 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
409 END LOOP;
410 ELSE
411 -- Exceeded the number of conditions supported
412 -- for bind variables.
413 l_sql := l_sql || ' WHERE ' || p_where_clause;
414 END IF;
415
416 l_sql := l_sql || ')';
417
418 IF (OZF_DEBUG_HIGH_ON) THEN
419 debug_message('SQL statement: '||l_sql);
420 END IF;
421
422 --
423 -- choang - 25-Apr-2000
424 -- Modify here if number of WHERE conditions
425 -- supported changes.
426 BEGIN
427 IF l_bind_tbl.COUNT = 1 THEN
428 EXECUTE IMMEDIATE l_sql INTO l_count
429 USING l_bind_tbl(1).col_value;
430 ELSIF l_bind_tbl.COUNT = 2 THEN
431 EXECUTE IMMEDIATE l_sql INTO l_count
432 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value;
433 ELSIF l_bind_tbl.COUNT = 3 THEN
434 EXECUTE IMMEDIATE l_sql INTO l_count
435 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
436 ELSIF l_bind_tbl.COUNT = 4 THEN
437 EXECUTE IMMEDIATE l_sql INTO l_count
438 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
439 ELSE
440 EXECUTE IMMEDIATE l_sql INTO l_count;
441 END IF;
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 l_count := 0;
445 END;
446
447 IF l_count = 0 THEN
448 RETURN FND_API.g_true;
449 ELSE
450 RETURN FND_API.g_false;
451 END IF;
452
453 END check_uniqueness;
454
455
456 ---------------------------------------------------------------------
457 -- FUNCTION
458 -- is_Y_or_N
459 --
460 -- HISTORY
461 -- 05/19/99 cklee Created.
462 ---------------------------------------------------------------------
463 FUNCTION is_Y_or_N(
464 p_value IN VARCHAR2
465 )
466 RETURN VARCHAR2
467 IS
468 BEGIN
469 IF p_value = 'Y' or p_value = 'N' THEN
470 RETURN FND_API.g_true;
471 ELSE
472 RETURN FND_API.g_false;
473 END IF;
474 END is_Y_or_N;
475
476
477 ---------------------------------------------------------------------
478 -- PROCEDURE
479 -- debug_message
480 --
481 -- HISTORY
482 -- 10/10/99 holiu Created.
483 -- 13-mar-2002 choang bug 2262529 - g_miss_char is a nil char which
484 -- caused some problems in the java layer; removed
485 -- g_miss_char from the error message.
486 -- 14-mar-2002 choang added text G_MISS_CHAR to be displayed in place
487 -- of nil char for debugging purposes.
488 -- 09-Dec-2002 choang All calls to debug should check for msg level
489 -- before calling; removing the check in the debug
490 -- procedure to avoid reduncy.
491 ---------------------------------------------------------------------
492 PROCEDURE debug_message(
493 p_message_text IN VARCHAR2,
494 p_message_level IN NUMBER := NULL
495 )
496 IS
497 BEGIN
498 FND_MESSAGE.set_name('OZF', 'OZF_API_DEBUG_MESSAGE');
499 FND_MESSAGE.set_token('TEXT', REPLACE (p_message_text, FND_API.G_MISS_CHAR, 'G_MISS_CHAR'));
500 FND_MSG_PUB.add;
501 END debug_message;
502
503
504 ---------------------------------------------------------------------
505 -- PROCEDURE
506 -- error_message
507 --
508 -- HISTORY
509 -- 11/01/99 holiu Created.
510 ---------------------------------------------------------------------
511 PROCEDURE error_message(
512 p_message_name VARCHAR2,
513 p_token_name VARCHAR2 := NULL,
514 P_token_value VARCHAR2 := NULL
515 )
516 IS
517 BEGIN
518 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
519 FND_MESSAGE.set_name('OZF', p_message_name);
520 IF p_token_name IS NOT NULL THEN
521 FND_MESSAGE.set_token(p_token_name, p_token_value);
522 END IF;
523 FND_MSG_PUB.add;
524 END IF;
525 END error_message;
526
527
528 ---------------------------------------------------------------------
529 -- PROCEDURE
530 -- display_messages
531 --
532 -- HISTORY
533 -- 10/26/99 holiu Created.
534 ---------------------------------------------------------------------
535 PROCEDURE display_messages
536 IS
537 l_count NUMBER;
538 l_msg VARCHAR2(2000);
539 BEGIN
540 l_count := FND_MSG_PUB.count_msg;
541 FOR i IN 1 .. l_count LOOP
542 l_msg := FND_MSG_PUB.get(i, FND_API.g_false);
543 -- holiu: remove since adchkdrv does not like it
544 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
545 END LOOP;
546 END display_messages;
547
548
549 ---------------------------------------------------------------------
550 -- NAME
551 -- create_log
552 --
553 -- HISTORY
554 -- 09/21/99 ptendulk Created.
555 -- 12-Jan-2000 choang Added autonomous transaction.
556 ---------------------------------------------------------------------
557 PROCEDURE create_log(
558 x_return_status OUT NOCOPY VARCHAR2,
559 p_arc_log_used_by IN VARCHAR2,
560 p_log_used_by_id IN VARCHAR2,
561 p_msg_data IN VARCHAR2,
562 p_msg_level IN NUMBER DEFAULT NULL,
563 p_msg_type IN VARCHAR2 DEFAULT NULL,
564 p_desc IN VARCHAR2 DEFAULT NULL,
565 p_budget_id IN NUMBER DEFAULT NULL,
566 p_threshold_id IN NUMBER DEFAULT NULL,
567 p_transaction_id IN NUMBER DEFAULT NULL,
568 p_notification_creat_date IN DATE DEFAULT NULL,
569 p_activity_log_id IN NUMBER DEFAULT NULL
570 )
571 IS
572 PRAGMA AUTONOMOUS_TRANSACTION;
573 x_rowid VARCHAR2(30);
574 l_act_log_id ams_act_logs.activity_log_id%TYPE;
575 l_log_tran_id ams_act_logs.log_transaction_id%TYPE;
576
577 CURSOR c_log_seq IS
578 SELECT ams_act_logs_s.NEXTVAL,
579 ams_act_logs_transaction_id_s.NEXTVAL
580 FROM DUAL;
581
582 CURSOR c_log(l_my_log_id VARCHAR2) IS
583 SELECT rowid
584 FROM ams_act_logs
585 WHERE activity_log_id = l_my_log_id;
586
587 BEGIN
588
589 -- Standard Start of API savepoint
590 SAVEPOINT Create_act_log;
591
592 -- Initialize API return status to success
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594
595 --
596 -- API body
597 --
598
599 -- open cursor AND fetch into local variable
600 OPEN c_log_seq;
601 FETCH c_log_seq INTO l_act_log_id,l_log_tran_id ;
602 CLOSE c_log_seq;
603
604
605 INSERT INTO ams_act_logs (
606 activity_log_id
607 -- standard who columns
608 ,last_update_date
609 ,last_updated_by
610 ,creation_date
611 ,created_by
612 ,last_update_login
613 ,object_version_number
614 ,act_log_used_by_id
615 ,arc_act_log_used_by
616 ,log_transaction_id
617 ,log_message_text
618 ,log_message_level
619 ,log_message_type
620 ,description
621 ,budget_id
622 ,threshold_id
623 ,notification_creation_date
624 )
625 VALUES (
626 NVL(p_activity_log_id,l_act_log_id)
627 -- standard who columns
628 ,SYSDATE
629 ,FND_GLOBAL.User_Id
630 ,SYSDATE
631 ,FND_GLOBAL.User_Id
632 ,FND_GLOBAL.Conc_Login_Id
633 ,1 -- Object Version Number
634 ,p_log_used_by_id
635 ,p_arc_log_used_by
636 ,NVL(p_transaction_id,l_log_tran_id)
637 ,p_msg_data
638 ,p_msg_level
639 ,p_msg_type
640 ,p_desc
641 ,p_budget_id
642 ,p_threshold_id
643 ,p_notification_creat_date
644 ) ;
645
646
647 OPEN c_log(l_act_log_id);
648 FETCH c_log INTO x_rowid;
649 IF (c_log%NOTFOUND) THEN
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
651 END IF;
652 CLOSE c_log;
653
654 --
655 -- END of API body.
656 --
657
658 COMMIT;
659 EXCEPTION
660
661 WHEN OTHERS THEN
662 ROLLBACK TO create_act_log;
663 x_return_status := FND_API.g_ret_sts_unexp_error;
664
665 END create_log;
666
667
668 ---------------------------------------------------------------------
669 -- PROCEDURE
670 -- get_qual_table_name_and_pk
671 --
672 -- HISTORY
673 -- 05/20/99 tdonohoe Created.
674 -- 10/13/99 ptendulk Removed Parameter p_qual_id;
675 -- Added qualifiers DELI, EVEH;
676 -- Changed the name from Event Offerings to Event Offers
677 -- Changed the name from Event Offerings to Event Offers
678 -- 01/06/99 ptendulk Changed the return Statuses to Standard
679 -- return statuses
680 -- 04/24/00 tdonohoe Added Qualifier 'FCST' Forecast.
681 -- 06/14/00 ptendulk Added qualifier 'OFFR' Offers
682 -- 06/28/00 rchahal Added Qualifier 'FUND' Fund.
683 -- 30-Jan-2001 ptendulk Modified Qualifier table for Schedules.
684 -- 06-Apr-2001 choang Added DIWB, MODL and SCOR in get_qual_table_name_and_pk
685 -- added error message if no valid sys_qual mapped.
686 -- 09-Apr-2001 choang added CELL
687 -- 13-Jun-2001 ptendulk Added EONE
688 -- 15-Jun-2001 choang Changed OFFR to return ozf_offers and qp_list_header_id.
689 ---------------------------------------------------------------------
690 PROCEDURE get_qual_table_name_and_pk(
691 p_sys_qual IN VARCHAR2,
692 x_return_status OUT NOCOPY VARCHAR2,
693 x_table_name OUT NOCOPY VARCHAR2,
694 x_pk_name OUT NOCOPY VARCHAR2
695 )
696 IS
697 BEGIN
698 -- initialize return status
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700
701 IF (p_sys_qual ='CSCH') THEN
702 -- Start of code modified by ptendulk on 30-Jan-2001
703 --x_table_name := 'AMS_CAMPAIGN_SCHEDULES';
704 --x_pk_name := 'CAMPAIGN_SCHEDULE_ID';
705 -- End of code modified by ptendulk on 30-Jan-2001
706 x_table_name := 'AMS_CAMPAIGN_SCHEDULES_B';
707 x_pk_name := 'SCHEDULE_ID';
708 ELSIF (p_sys_qual ='CAMP') THEN
709 -- Start of code modified by ptendulk on 30-Jan-2001
710 --x_table_name := 'AMS_CAMPAIGNS_VL';
711 -- End of code modified by ptendulk on 30-Jan-2001
712 x_table_name := 'AMS_CAMPAIGNS_ALL_B';
713 x_pk_name := 'CAMPAIGN_ID';
714 ELSIF (p_sys_qual ='EVEO') THEN
715 x_table_name := 'AMS_EVENT_OFFERS_VL';
716 x_pk_name := 'EVENT_OFFER_ID';
717 ELSIF (p_sys_qual ='EONE') THEN
718 x_table_name := 'AMS_EVENT_OFFERS_VL';
719 x_pk_name := 'EVENT_OFFER_ID';
720 ELSIF (p_sys_qual ='EVEH') THEN
721 x_table_name := 'AMS_EVENT_HEADERS_VL';
722 x_pk_name := 'EVENT_HEADER_ID';
723 ELSIF (p_sys_qual ='DELV') THEN
724 x_table_name := 'AMS_DELIVERABLES_VL';
725 x_pk_name := 'DELIVERABLE_ID';
726 ELSIF (p_sys_qual ='AMET') THEN
727 x_table_name := 'OZF_ACT_METRICS_ALL';
728 x_pk_name := 'ACTIVITY_METRIC_ID';
729 --=========================================================
730 -- Following line of code is added by ptendulk on 14Jun2000
731 --=========================================================
732 ELSIF (p_sys_qual ='OFFR') THEN --added tdonohoe 04/24/2000
733 x_table_name := 'OZF_OFFERS';
734 x_pk_name := 'QP_LIST_HEADER_ID';
735 ELSIF (p_sys_qual ='FCST') THEN --added tdonohoe 04/24/2000
736 x_table_name := 'OZF_ACT_FORECASTS_ALL';
737 x_pk_name := 'FORECAST_ID';
738 ELSIF (p_sys_qual ='FUND') THEN --added rchahal 06/28/2000
739 -- yzhao 10/13/2003 fix bug 3167497 changed from OZF_FUND_DETAILS_V to ozf_funds_all_b
740 x_table_name := 'OZF_FUNDS_ALL_B'; -- yzhao: 'OZF_FUND_DETAILS_V';
741 x_pk_name := 'FUND_ID';
742 ELSIF (p_sys_qual ='PRIC') THEN --added skarumur 12/17/2000
743 x_table_name := 'AMS_PRICE_LIST_ATTRIBUTES';
744 x_pk_name := 'QP_LIST_HEADER_ID';
745 ELSIF (p_sys_qual ='LIST') THEN --added gjoby 03/26/2001
746 x_table_name := 'OZF_LIST_HEADERS_VL';
747 x_pk_name := 'LIST_HEADER_ID';
748 ELSIF (p_sys_qual ='IMPH') THEN --added gjoby 03/26/2001
749 x_table_name := 'AMS_IMP_LIST_HEADERS_VL';
750 x_pk_name := 'IMPORT_LIST_HEADER_ID';
751 ELSIF (p_sys_qual ='SQL') THEN --added gjoby 03/26/2001
752 x_table_name := 'OZF_LIST_QUERIES_ALL';
753 x_pk_name := 'LIST_QUERY_ID';
754 ELSIF p_sys_qual = 'DIWB' THEN
755 x_table_name := 'OZF_DISCOVERER_SQL';
756 x_pk_name := 'DISCOVERER_SQL_ID';
757 ELSIF p_sys_qual = 'MODL' THEN
758 x_table_name := 'OZF_DM_MODELS_ALL_B';
759 x_pk_name := 'MODEL_ID';
760 ELSIF p_sys_qual = 'SCOR' THEN
761 x_table_name := 'OZF_DM_SCORES_ALL_B';
762 x_pk_name := 'SCORE_ID';
763 ELSIF p_sys_qual = 'CELL' THEN
764 x_table_name := 'AMS_CELLS_ALL_B';
765 x_pk_name := 'CELL_ID';
766 ELSIF (p_sys_qual = 'RCAM') THEN
767 x_table_name := 'AMS_CAMPAIGNS_ALL_B';
768 x_pk_name := 'CAMPAIGN_ID';
769 ELSIF p_sys_qual = 'DILG' THEN -- Added dmvincen 03/27/2002
770 x_table_name := 'OZF_DIALOGS_ALL_B';
771 x_pk_name := 'DIALOG_ID';
772 ELSIF p_sys_qual in -- Added dmvincen 03/27/2002
773 ('OZF_COMP_START', 'OZF_COMP_SHOW_WEB_PAGE', 'OZF_COMP_END') THEN
774 x_table_name := 'OZF_DLG_FLOW_COMPS_B';
775 x_pk_name := 'FLOW_COMPONENT_ID';
776 ELSE
777 OZF_Utility_PVT.error_message ('OZF_INVALID_SYS_QUAL', 'SYS_QUALIFIER', p_sys_qual);
778 x_return_status := FND_API.g_ret_sts_unexp_error;
779 x_table_name := NULL;
780 x_pk_name := NULL;
781 END IF;
782
783 END get_qual_table_name_and_pk;
784
785
786 --------------------------------------------------------------------
787 -- NAME
788 -- get_source_code
789 --
790 -- HISTORY
791 -- 08/18/99 tdonohoe Created.
792 --------------------------------------------------------------------
793 PROCEDURE get_source_code(
794 p_activity_type IN VARCHAR2,
795 p_activity_id IN NUMBER,
796 x_return_status OUT NOCOPY VARCHAR2,
797 x_source_code OUT NOCOPY VARCHAR2 ,
798 x_source_id OUT NOCOPY NUMBER
799 )
800 IS
801 BEGIN
802
803 SELECT source_code,source_code_for_id INTO x_source_code,x_source_id
804 FROM ams_source_codes
805 WHERE arc_source_code_for = UPPER(p_activity_type)
806 AND source_code_for_id = UPPER(p_activity_id);
807
808
809
810 IF SQL%NOTFOUND THEN
811 x_return_status := FND_API.G_FALSE;
812 ELSE
813 x_return_status := FND_API.G_TRUE;
814 END IF;
815
816
817 EXCEPTION
818
819 WHEN OTHERS THEN
820 x_source_code := NULL;
821 x_source_id := NULL;
822 x_return_status := FND_API.G_FALSE;
823
824 End;
825
826
827 ---------------------------------------------------------------------
828 -- FUNCTION
829 -- get_object_name
830 --
831 -- HISTORY
832 -- 10/15/99 holiu Created.
833 -- 11/03/99 mpande inserted deliverable,event
834 -- 11/16/99 tdonohoe inserted campaign schedule.
835 -- 09-Dec-1999 choang Changed references of ozf_event_offers_all_vl to
836 -- ams_event_offers_vl and ozf_event_headers_all_vl
837 -- to ams_event_headers_vl.
838 -- 24-Aug-2000 choang Added FUND
839 -- 28-Sep-2000 choang Added PRTN
840 -- 13-Jun-2001 ptendulk Added EONE
841 -- 15-Jun-2001 choang changed PRNT to PTNR
842 --------------------------------------------------------------------
843 FUNCTION get_object_name(
844 p_sys_arc_qualifier IN VARCHAR2,
845 p_object_id IN NUMBER
846 )
847 RETURN VARCHAR2
848 IS
849
850 l_object_name VARCHAR2(1000);
851
852 CURSOR c_campaign(p_object_id IN NUMBER) IS
853 SELECT campaign_name
854 FROM ams_campaigns_vl
855 WHERE campaign_id = p_object_id;
856
857 --added 11/16/99 tdonohoe
858 CURSOR c_campaign_sched(p_object_id IN NUMBER) IS
859 SELECT c.campaign_name
860 FROM ams_campaigns_vl c,
861 ams_campaign_schedules s
862 WHERE s.campaign_schedule_id = p_object_id
863 AND s.campaign_id = c.campaign_id;
864
865 CURSOR c_deliv(p_object_id IN NUMBER) IS
866 SELECT deliverable_name
867 FROM ams_deliverables_vl
868 WHERE deliverable_id = p_object_id;
869
870 CURSOR c_event_header(p_object_id IN NUMBER) IS
871 SELECT event_header_name
872 FROM ams_event_headers_vl
873 WHERE event_header_id = p_object_id;
874
875 CURSOR c_event_offer(p_object_id IN NUMBER) IS
876 SELECT event_offer_name
877 FROM ams_event_offers_vl
878 WHERE event_offer_id = p_object_id;
879 --- updated by mpande 01/30/2001 to look into ozf_funds_all_vl not ozf_funds_vl
880 CURSOR c_fund (p_object_id IN NUMBER) IS
881 SELECT short_name
882 FROM ozf_funds_all_vl
883 WHERE fund_id = p_object_id;
884
885 CURSOR c_partner (p_object_id IN NUMBER) IS
886 SELECT party_name
887 FROM hz_parties
888 WHERE party_id = p_object_id;
889 BEGIN
890
891 l_object_name := NULL;
892
893 IF p_sys_arc_qualifier IS NULL OR p_object_id IS NULL THEN
894 RETURN l_object_name;
895 END IF;
896
897 IF p_sys_arc_qualifier = 'CAMP' THEN
898 OPEN c_campaign(p_object_id);
899 FETCH c_campaign INTO l_object_name;
900 CLOSE c_campaign;
901 ELSIF p_sys_arc_qualifier = 'CSCH' THEN
902 OPEN c_campaign_sched(p_object_id);
903 FETCH c_campaign_sched INTO l_object_name;
904 CLOSE c_campaign_sched;
905 ELSIF p_sys_arc_qualifier = 'DELI' THEN
906 OPEN c_deliv(p_object_id);
907 FETCH c_deliv INTO l_object_name;
908 CLOSE c_deliv;
909 ELSIF p_sys_arc_qualifier = 'EVEH' THEN
910 OPEN c_event_header(p_object_id);
911 FETCH c_event_header INTO l_object_name;
912 CLOSE c_event_header;
913 ELSIF p_sys_arc_qualifier = 'EVEO' THEN
914 OPEN c_event_offer(p_object_id);
915 FETCH c_event_offer INTO l_object_name;
916 CLOSE c_event_offer;
917 ELSIF p_sys_arc_qualifier = 'EONE' THEN
918 OPEN c_event_offer(p_object_id);
919 FETCH c_event_offer INTO l_object_name;
920 CLOSE c_event_offer;
921 ELSIF p_sys_arc_qualifier = 'FUND' THEN
922 OPEN c_fund (p_object_id);
923 FETCH c_fund INTO l_object_name;
924 CLOSE c_fund;
925 ELSIF p_sys_arc_qualifier = 'PTNR' THEN
926 OPEN c_partner (p_object_id);
927 FETCH c_partner INTO l_object_name;
928 CLOSE c_partner;
929 END IF;
930
931 RETURN l_object_name;
932
933 END get_object_name;
934
935
939 -- NOTE
936 ---------------------------------------------------------------------
937 -- PROCEDURE
938 -- Convert_Currency
940 -- Modified from code done by ptendulk.
941 -- HISTORY
942 -- 08-Dec-1999 choang Created.
943 -- 31-Aug-2000 ptendulk Added x_conversion_type parameter to the
944 -- Convert_Closest_Amount procedure
945 -- 09-Oct-2000 choang Modified error message handling for no rate
946 -- and invalid currency.
947 ---------------------------------------------------------------------
948 PROCEDURE Convert_Currency (
949 x_return_status OUT NOCOPY VARCHAR2,
950 p_from_currency IN VARCHAR2,
951 p_to_currency IN VARCHAR2,
952 p_conv_date IN DATE DEFAULT SYSDATE,
953 p_from_amount IN NUMBER,
954 x_to_amount OUT NOCOPY NUMBER
955 )
956 IS
957 L_CONVERSION_TYPE_PROFILE CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
958 L_USER_RATE CONSTANT NUMBER := 1; -- Currenty not used.
959 L_MAX_ROLL_DAYS CONSTANT NUMBER := -1; -- Negative so API rolls back to find the last conversion rate.
960 l_denominator NUMBER; -- Not used in Marketing.
961 l_numerator NUMBER; -- Not used in Marketing.
962 l_rate NUMBER; -- Not used in Marketing.
963 l_conversion_type VARCHAR2(30); -- Currency conversion type; see API documention for details.
964 BEGIN
965 -- Initialize return status.
966 x_return_status := FND_API.G_RET_STS_SUCCESS;
967
968 -- Get the currency conversion type from profile option
969 l_conversion_type := FND_PROFILE.Value (L_CONVERSION_TYPE_PROFILE);
970
971 -- Call the proper GL API to convert the amount.
972 GL_Currency_API.Convert_Closest_Amount (
973 x_from_currency => p_from_currency,
974 x_to_currency => p_to_currency,
975 x_conversion_date => p_conv_date,
976 x_conversion_type => l_conversion_type,
977 x_user_rate => L_USER_RATE,
978 x_amount => p_from_amount,
979 x_max_roll_days => L_MAX_ROLL_DAYS,
980 x_converted_amount => x_to_amount,
981 x_denominator => l_denominator,
982 x_numerator => l_numerator,
983 x_rate => l_rate
984 );
985 EXCEPTION
986 WHEN GL_Currency_API.NO_RATE THEN
987 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
988 FND_MESSAGE.Set_Name ('OZF', 'OZF_NO_RATE');
989 FND_MESSAGE.Set_Token ('CURRENCY_FROM', p_from_currency);
990 FND_MESSAGE.Set_Token ('CURRENCY_TO', p_to_currency);
991 FND_MSG_PUB.Add;
992 END IF;
993 x_return_status := FND_API.G_RET_STS_ERROR;
994 WHEN GL_Currency_API.INVALID_CURRENCY THEN
995 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
996 FND_MESSAGE.Set_Name ('OZF', 'OZF_INVALID_CURR');
997 FND_MESSAGE.Set_Token ('CURRENCY_FROM', p_from_currency);
998 FND_MESSAGE.Set_Token ('CURRENCY_TO', p_to_currency);
999 FND_MSG_PUB.Add;
1000 END IF;
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 END Convert_Currency;
1003
1004 ---------------------------------------------------------------------
1005 -- PROCEDURE
1006 -- get_lookup_meaning
1007 -- created by mpande 01/11/00
1008 -- PURPOSE
1009 -- This procedure will return the meaning from ozf_lookups if
1010 -- you pass the right lookup_type and lookup_code
1011 -- HISTORY
1012 -- 28-Apr-2000 choang Modified to use explicit cursor.
1013 -- 07-Aug-2000 choang Added close cursor for success conditions
1014 -- in the fetch.
1015 ---------------------------------------------------------------------
1016
1017 PROCEDURE get_lookup_meaning (
1018 p_lookup_type IN VARCHAR2,
1019 p_lookup_code IN VARCHAR2,
1020 x_return_status OUT NOCOPY VARCHAR2,
1021 x_meaning OUT NOCOPY VARCHAR2
1022 )
1023 IS
1024 CURSOR c_meaning IS
1025 SELECT meaning
1026 FROM ozf_lookups
1027 WHERE lookup_type = UPPER (p_lookup_type)
1028 AND lookup_code = UPPER (p_lookup_code);
1029 BEGIN
1030 OPEN c_meaning;
1031 FETCH c_meaning INTO x_meaning;
1032 IF c_meaning%NOTFOUND THEN
1033 CLOSE c_meaning;
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 x_meaning:= NULL;
1036 ELSE
1037 CLOSE c_meaning;
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039 END IF;
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 IF c_meaning%ISOPEN THEN
1043 CLOSE c_meaning;
1044 END IF;
1045 x_return_status := FND_API.G_RET_STS_ERROR;
1046 x_meaning := NULL;
1047 END get_lookup_meaning;
1048
1049
1050 ---------------------------------------------------------------------
1051 -- PROCEDURE
1052 -- get_System_Timezone
1053 --
1054 -- PURPOSE
1055 -- This procedure will return the timezone from the System Timezone profile option
1056 -- HISTORY created 04/24/2000 sugupta
1057 -- 17-May-2002 choang bug 2224836: changed to use SERVER_TIMEZONE_ID
1058 ---------------------------------------------------------------------
1059 PROCEDURE get_System_Timezone(
1063 x_sys_time_name OUT NOCOPY VARCHAR2
1060
1061 x_return_status OUT NOCOPY VARCHAR2,
1062 x_sys_time_id OUT NOCOPY NUMBER,
1064 ) IS
1065
1066 l_sys_time_id NUMBER;
1067 l_sys_name VARCHAR2(80);
1068
1069 cursor c_get_name(l_time_id IN NUMBER) is
1070 select NAME
1071 from HZ_TIMEZONES_VL
1072 where TIMEZONE_ID = l_time_id;
1073
1074 BEGIN
1075 -- Initialize API return status to success
1076 x_return_status := FND_API.G_RET_STS_SUCCESS;
1077 l_sys_time_id := FND_PROFILE.VALUE('SERVER_TIMEZONE_ID');
1078 OPEN c_get_name(l_sys_time_id);
1079 FETCH c_get_name into l_sys_name;
1080 IF (c_get_name%NOTFOUND) THEN
1081 CLOSE c_get_name;
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1083 return;
1084 END IF;
1085 CLOSE c_get_name;
1086
1087 x_sys_time_id := l_sys_time_id;
1088 x_sys_time_name := l_sys_name;
1089 EXCEPTION
1090 WHEN NO_DATA_FOUND THEN
1091 IF (c_get_name%ISOPEN) THEN
1092 CLOSE c_get_name;
1093 END IF;
1094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1095 END get_System_Timezone;
1096
1097 ---------------------------------------------------------------------
1098 -- PROCEDURE
1099 -- get_User_Timezone
1100 --
1101 -- PURPOSE
1102 -- This procedure will return the timezone from the User Timezone profile option
1103 -- HISTORY created 04/24/2000 sugupta
1104 -- 17-May-2002 choang bug 2224836: changed to use CLIENT_TIMEZONE_ID
1105 ---------------------------------------------------------------------
1106 PROCEDURE get_User_Timezone(
1107
1108 x_return_status OUT NOCOPY VARCHAR2,
1109 x_user_time_id OUT NOCOPY NUMBER,
1110 x_user_time_name OUT NOCOPY VARCHAR2
1111 ) IS
1112
1113 l_user_time_id NUMBER;
1114 l_user_time_name VARCHAR2(80);
1115
1116 cursor get_name(l_time_id IN NUMBER) is
1117 select NAME
1118 from HZ_TIMEZONES_VL
1119 where TIMEZONE_ID = l_time_id;
1120
1121 BEGIN
1122 -- Initialize API return status to success
1123 x_return_status := FND_API.G_RET_STS_SUCCESS;
1124 l_user_time_id := FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID');
1125 OPEN get_name(l_user_time_id);
1126 FETCH get_name into l_user_time_name;
1127 IF (get_name%NOTFOUND) THEN
1128 CLOSE get_name;
1129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1130 return;
1131 END IF;
1132 CLOSE get_name;
1133
1134 x_user_time_id := l_user_time_id;
1135 x_user_time_name := l_user_time_name;
1136 EXCEPTION
1137 WHEN NO_DATA_FOUND THEN
1138 IF (get_name%ISOPEN) THEN
1139 CLOSE get_name;
1140 END IF;
1141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1142 END get_User_Timezone;
1143
1144 -------------------------------------------------------------------------------------------------
1145 -- PROCEDURE
1146 -- Convert_Timezone
1147 --
1148 -- PURPOSE
1149 -- This procedure will take the user timezone and the input time, depending on the parameter
1150 -- p_convert_type it will convert the input time to System timezone or sent Usertimezone
1151 -- HISTORY
1152 -- 04/24/2000 sugupta created
1153 -- 04/26/2000 ptendulk Modified Added a parameter which will tell
1154 -- which timezone to convert time into.
1155 -- If the convert type is 'SYS' then input time will be
1156 -- converted into system timezone else it will be
1157 -- converted to user timezone .
1158 ---------------------------------------------------------------------------------------------------
1159 PROCEDURE Convert_Timezone(
1160 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1161 x_return_status OUT NOCOPY VARCHAR2,
1162 x_msg_count OUT NOCOPY NUMBER,
1163 x_msg_data OUT NOCOPY VARCHAR2,
1164
1165 p_user_tz_id IN NUMBER := null,
1166 p_in_time IN DATE , -- required
1167 p_convert_type IN VARCHAR2 := 'SYS' , -- (SYS/USER)
1168
1169 x_out_time OUT NOCOPY DATE
1170 ) IS
1171
1172 l_sys_time_id NUMBER;
1173 l_user_tz_id NUMBER := p_user_tz_id ;
1174 l_sys_time_name VARCHAR2(80);
1175 l_user_time_name VARCHAR2(80);
1176 l_return_status VARCHAR2(1); -- Return value from procedures
1177
1178 l_from_timezone_id NUMBER ;
1179 l_to_timezone_id NUMBEr ;
1180 BEGIN
1181
1182 -- Initialize API return status to success
1183 x_return_status := FND_API.G_RET_STS_SUCCESS;
1184
1185 get_System_Timezone(
1186 l_return_status,
1187 l_sys_time_id,
1188 l_sys_time_name);
1189
1190 IF (l_return_status = FND_API.G_RET_STS_ERROR OR
1191 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1192 THEN
1193 x_return_status := l_return_status;
1194 RETURN;
1195 END IF;
1196
1197 -- If the user timezone is not sent
1198 -- get it from profiles
1199 IF l_user_tz_id IS NULL THEN
1200 Get_User_Timezone(
1201 x_return_status => l_return_status,
1202 x_user_time_id => l_user_tz_id ,
1206
1203 x_user_time_name => l_user_time_name
1204 ) ;
1205 END IF;
1207 IF (l_return_status = FND_API.G_RET_STS_ERROR OR
1208 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1209 THEN
1210 x_return_status := l_return_status;
1211 RETURN;
1212 END IF;
1213
1214 IF p_convert_type = 'SYS' THEN
1215 l_from_timezone_id := l_user_tz_id ;
1216 l_to_timezone_id := l_sys_time_id ;
1217 ELSIF p_convert_type = 'USER' THEN
1218 l_from_timezone_id := l_sys_time_id ;
1219 l_to_timezone_id := l_user_tz_id ;
1220 END IF;
1221
1222 HZ_TIMEZONE_PUB.get_time(
1223 p_api_version => 1.0,
1224 p_init_msg_list => p_init_msg_list,
1225 p_source_tz_id => l_from_timezone_id ,
1226 p_dest_tz_id => l_to_timezone_id ,
1227 p_source_day_time => p_in_time,
1228 x_dest_day_time => x_out_time,
1229 x_return_status => x_return_status,
1230 x_msg_count => x_msg_count,
1231 x_msg_data => x_msg_data
1232 );
1233
1234 END Convert_Timezone ;
1235
1236
1237 ---------------------------------------------------------------------
1238 -- PROCEDURE
1239 -- bind_parse
1240 -- USAGE
1241 -- bind_parse (varchar2, col_val_tbl);
1242 -- The input string must have a space between the AND and operator clause
1243 -- and it must exclude the initial WHERE/AND statement.
1244 -- Example: source_code = 'xyz' and campaign_id <> 1
1245 ---------------------------------------------------------------------
1246 PROCEDURE bind_parse (
1247 p_string IN VARCHAR2,
1248 x_col_val_tbl OUT NOCOPY col_val_tbl)
1249 IS
1250 l_new_str VARCHAR2(4000);
1251 l_str VARCHAR2(4000) := p_string;
1252 l_curr_pos NUMBER; -- the position index of the operator string
1253 l_eq_pos NUMBER;
1254 l_not_pos NUMBER;
1255 l_and_pos NUMBER;
1256 i NUMBER := 1;
1257 BEGIN
1258 LOOP
1259 l_and_pos := INSTR (UPPER (l_str), ' AND ');
1260 -- handle condition where no more AND's are
1261 -- left -- usually if only one condition or
1262 -- the last condition in the WHERE clause.
1263 IF l_and_pos = 0 THEN
1264 l_new_str := l_str;
1265 ELSE
1266 l_new_str := SUBSTR (l_str, 1, l_and_pos - 1);
1267 END IF;
1268
1269 --
1270 -- The operator should also be passed
1271 -- back to the calling program.
1272 l_eq_pos := INSTR (l_new_str, '=');
1273 l_not_pos := INSTR (l_new_str, '<>');
1274 --
1275 -----------------------------------
1276 -- operator equal not equal
1277 -- error 0 0
1278 -- = 1 0
1279 -- <> 0 1
1280 -- = 1 2
1281 -- <> 2 1
1282 -----------------------------------
1283 IF l_eq_pos = 0 AND l_not_pos = 0 THEN
1284 -- Could not find either an = or an <>
1285 -- operator.
1286 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1287 FND_MESSAGE.set_name('OZF', 'OZF_UTIL_NO_WHERE_OPERATOR');
1288 FND_MSG_PUB.add;
1289 RAISE FND_API.g_exc_unexpected_error;
1290 END IF;
1291 ELSIF l_eq_pos > 0 AND l_not_pos = 0 THEN
1292 l_curr_pos := l_eq_pos;
1293 x_col_val_tbl(i).col_op := '=';
1294 ELSIF l_not_pos > 0 AND l_eq_pos = 0 THEN
1295 l_curr_pos := l_not_pos;
1296 x_col_val_tbl(i).col_op := '<>';
1297 ELSIF l_eq_pos < l_not_pos THEN
1298 l_curr_pos := l_eq_pos;
1299 x_col_val_tbl(i).col_op := '=';
1300 ELSE
1301 l_curr_pos := l_not_pos;
1302 x_col_val_tbl(i).col_op := '<>';
1303 END IF;
1304
1305 x_col_val_tbl(i).col_name := UPPER (LTRIM (RTRIM (SUBSTR (l_new_str, 1, l_curr_pos - 1))));
1306 -- Add 2 to the current position for '<>'.
1307 x_col_val_tbl(i).col_value := LTRIM (RTRIM (SUBSTR (l_new_str, l_curr_pos + 2)));
1308 --
1309 -- Remove the single quotes from the begin and end of the string value;
1310 -- no action if a numeric value.
1311 IF INSTR (x_col_val_tbl(i).col_value, '''', 1) = 1 THEN
1312 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value,2);
1313 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value, 1, LENGTH(x_col_val_tbl(i).col_value) - 1);
1314 END IF;
1315
1316 IF l_and_pos = 0 THEN
1317 EXIT; -- no more to parse
1318 END IF;
1319
1320 l_str := SUBSTR (l_str, l_and_pos + 4);
1321 i := i + 1;
1322 END LOOP;
1323 END bind_parse;
1324
1325
1326 ---------------------------------------------------------------------
1327 -- FUNCTION
1328 -- get_lookup_meaning
1329 -- USAGE
1330 -- Example:
1331 -- SELECT OZF_Utility_PVT.get_lookup_meaning ('AMS_CAMPAIGN_STATUS', status_code)
1332 -- FROM ams_campaigns_vl;
1333 -- HISTORY
1334 -- 28-Apr-2000 choang Created.
1335 ---------------------------------------------------------------------
1336 FUNCTION get_lookup_meaning (
1340 RETURN VARCHAR2
1337 p_lookup_type IN VARCHAR2,
1338 p_lookup_code IN VARCHAR2
1339 )
1341 IS
1342 l_meaning VARCHAR2(80);
1343
1344 CURSOR c_meaning IS
1345 SELECT meaning
1346 FROM ozf_lookups
1347 WHERE lookup_type = UPPER (p_lookup_type)
1348 AND lookup_code = UPPER (p_lookup_code);
1349 BEGIN
1350 OPEN c_meaning;
1351 FETCH c_meaning INTO l_meaning;
1352 CLOSE c_meaning;
1353
1354 RETURN l_meaning;
1355 END get_lookup_meaning;
1356
1357
1358 ---------------------------------------------------------------------
1359 -- FUNCTION
1360 -- get_resource_name
1361 -- USAGE
1362 -- Example:
1363 -- SELECT OZF_Utility_PVT.get_resource_name (owner_user_id)
1364 -- FROM ams_campaigns_vl
1365 -- HISTORY
1366 -- 28-Apr-2000 choang Created.
1367 ---------------------------------------------------------------------
1368 FUNCTION get_resource_name (
1369 p_resource_id IN VARCHAR2
1370 )
1371 RETURN VARCHAR2
1372 IS
1373 l_resource_name VARCHAR2(240);
1374
1375 CURSOR c_resource_name IS
1376 SELECT full_name
1377 FROM ams_jtf_rs_emp_v
1378 WHERE resource_id = p_resource_id;
1379 BEGIN
1380 IF p_resource_id IS NULL THEN
1381 RETURN NULL;
1382 END IF;
1383
1384 OPEN c_resource_name;
1385 FETCH c_resource_name INTO l_resource_name;
1386 CLOSE c_resource_name;
1387
1388 RETURN l_resource_name;
1389 END get_resource_name;
1390
1391
1392 -----------------------------------------------------------------------
1393 -- FUNCTION
1394 -- is_in_my_division
1395 --
1396 -- HISTORY
1397 -- 07/28/2000 holiu Created.
1398 -----------------------------------------------------------------------
1399 FUNCTION is_in_my_division(
1400 p_object_type IN VARCHAR2,
1401 p_object_id IN NUMBER,
1402 p_country_id IN NUMBER
1403 )
1404 RETURN VARCHAR2
1405 IS
1406
1407 l_area2 VARCHAR2(30);
1408 l_obj_area2 VARCHAR2(30);
1409
1410 CURSOR c_area2 IS
1411 SELECT area2_code
1412 FROM jtf_loc_hierarchies_vl
1413 WHERE location_hierarchy_id = p_country_id;
1414
1415 CURSOR c_camp_area2 IS
1416 SELECT B.area2_code
1417 FROM ams_campaigns_vl A, jtf_loc_hierarchies_vl B
1418 WHERE A.campaign_id = p_object_id
1419 AND A.city_id = B.location_hierarchy_id;
1420
1421 BEGIN
1422
1423 OPEN c_area2;
1424 FETCH c_area2 INTO l_area2;
1425 CLOSE c_area2;
1426
1427 IF l_area2 IS NULL THEN
1428 RETURN 'N';
1429 END IF;
1430
1431 IF p_object_type = 'CAMP' THEN
1432 OPEN c_camp_area2;
1433 FETCH c_camp_area2 INTO l_obj_area2;
1434 CLOSE c_camp_area2;
1435 END IF;
1436
1437 IF l_area2 = l_obj_area2 THEN
1438 RETURN 'Y';
1439 ELSE
1440 RETURN 'N';
1441 END IF;
1442
1443 END is_in_my_division;
1444
1445
1446 ---------------------------------------------------------------------
1447 -- FUNCTION
1448 -- get_product_name
1449 -- HISTORY
1450 -- 14-JUN-2000 holiu Create.
1451 -- 10-Apr-2002 choang applied changes requested by skarumur: removed
1452 -- l_product_name because it was declared as a
1453 -- varchar2(76) - too small for product name.
1454 -- 13-Jun-2005 Ribha Use p_org_id = -1 for bypassing access check
1455 -- (organization identifier)
1456 -- 17-May-2006 asylvia Include Return NULL for the function fixed bug 5226685
1457 ---------------------------------------------------------------------
1458 FUNCTION get_product_name(
1459 p_prod_level IN VARCHAR2,
1460 p_prod_id IN NUMBER,
1461 p_org_id IN NUMBER := NULL
1462 )
1463 RETURN VARCHAR2
1464 IS
1465 CURSOR c_product_name IS
1466 SELECT padded_concatenated_segments
1467 FROM mtl_system_items_kfv
1468 WHERE inventory_item_id = p_prod_id
1469 AND organization_id = p_org_id;
1470
1471 CURSOR c_product_name_all IS
1472 SELECT DISTINCT padded_concatenated_segments
1473 FROM mtl_system_items_kfv
1474 WHERE inventory_item_id = p_prod_id;
1475
1476 CURSOR c_category_name IS
1477 SELECT description
1478 FROM mtl_categories_v
1479 WHERE category_id = p_prod_id;
1480
1481 l_product_name c_product_name%ROWTYPE;
1482 l_product_name_all c_product_name_all%ROWTYPE;
1483 l_category_name c_category_name%ROWTYPE;
1484 BEGIN
1485 IF p_prod_id IS NULL THEN
1486 RETURN NULL;
1487 END IF;
1488
1489 IF p_prod_level IN ('PRICING_ATTRIBUTE1', 'PRODUCT') THEN
1490 IF p_org_id = -1 THEN
1491 OPEN c_product_name_all;
1492 FETCH c_product_name_all INTO l_product_name_all;
1493 CLOSE c_product_name_all;
1494 RETURN l_product_name_all.padded_concatenated_segments;
1495 ELSE
1496 OPEN c_product_name;
1497 FETCH c_product_name INTO l_product_name;
1498 CLOSE c_product_name;
1502 OPEN c_category_name;
1499 RETURN l_product_name.padded_concatenated_segments;
1500 END IF;
1501 ELSIF p_prod_level IN ('PRICING_ATTRIBUTE2', 'CATEGORY','FAMILY') THEN
1503 FETCH c_category_name INTO l_category_name;
1504 CLOSE c_category_name;
1505 RETURN l_category_name.description;
1506 END IF;
1507 RETURN NULL;
1508 END get_product_name;
1509
1510
1511
1512 ---------------------------------------------------------------------
1513 -- FUNCTION
1514 -- get_price_list_name
1515 -- HISTORY
1516 -- 14-JUN-2000 holiu Create.
1517 ---------------------------------------------------------------------
1518 FUNCTION get_price_list_name(
1519 p_price_list_line_id IN NUMBER
1520 )
1521 RETURN VARCHAR2
1522 IS
1523 l_name VARCHAR2(240);
1524
1525 CURSOR c_price_list_name IS
1526 SELECT qlh.name
1527 FROM qp_list_headers_vl qlh, qp_list_lines qll
1528 WHERE qll.list_header_id = qlh.list_header_id
1529 AND qll.list_line_id = p_price_list_line_id;
1530 BEGIN
1531 IF p_price_list_line_id IS NULL THEN
1532 RETURN NULL;
1533 END IF;
1534
1535 OPEN c_price_list_name;
1536 FETCH c_price_list_name INTO l_name;
1537 CLOSE c_price_list_name;
1538
1539 RETURN l_name;
1540 END get_price_list_name;
1541
1542
1543 ---------------------------------------------------------------------
1544 -- FUNCTION
1545 -- get_uom_name
1546 -- HISTORY
1547 -- 14-JUN-2000 holiu Create.
1548 ---------------------------------------------------------------------
1549 FUNCTION get_uom_name(
1550 p_uom_code IN VARCHAR2
1551 )
1552 RETURN VARCHAR2
1553 IS
1554 l_name VARCHAR2(25);
1555
1556 CURSOR c_uom IS
1557 SELECT unit_of_measure
1558 FROM mtl_units_of_measure
1559 WHERE uom_code = p_uom_code;
1560 BEGIN
1561 IF p_uom_code IS NULL THEN
1562 RETURN NULL;
1563 END IF;
1564
1565 OPEN c_uom;
1566 FETCH c_uom INTO l_name;
1567 CLOSE c_uom;
1568
1569 RETURN l_name;
1570 END get_uom_name;
1571
1572
1573 ---------------------------------------------------------------------
1574 -- FUNCTION
1575 -- get_qp_lookup_meaning
1576 -- DESCRIPTION
1577 -- Get the meaning of the given lookup code in qp_lookups.
1578 ---------------------------------------------------------------------
1579 FUNCTION get_qp_lookup_meaning(
1580 p_lookup_type IN VARCHAR2,
1581 p_lookup_code IN VARCHAR2
1582 )
1583 RETURN VARCHAR2
1584 IS
1585 l_meaning VARCHAR2(80);
1586
1587 CURSOR c_meaning IS
1588 SELECT meaning
1589 FROM qp_lookups
1590 WHERE lookup_type = UPPER(p_lookup_type)
1591 AND lookup_code = UPPER(p_lookup_code);
1592 BEGIN
1593 IF p_lookup_type IS NULL OR p_lookup_code IS NULL THEN
1594 RETURN NULL;
1595 END IF;
1596
1597 OPEN c_meaning;
1598 FETCH c_meaning INTO l_meaning;
1599 CLOSE c_meaning;
1600
1601 RETURN l_meaning;
1602 END get_qp_lookup_meaning;
1603
1604 ---------------------------------------------------------------------
1605 -- FUNCTION
1606 -- get_resource_id
1607 -- DESCRIPTION
1608 -- Returns resource_id from the JTF Resource module given
1609 -- an AOL user_id.
1610 ---------------------------------------------------------------------
1611 FUNCTION get_resource_id (
1612 p_user_id IN NUMBER
1613 )
1614 RETURN NUMBER
1615 IS
1616 l_resource_id NUMBER;
1617
1618 CURSOR c_resource IS
1619 SELECT resource_id
1620 FROM ams_jtf_rs_emp_v
1621 WHERE user_id = p_user_id;
1622 BEGIN
1623 OPEN c_resource;
1624 FETCH c_resource INTO l_resource_id;
1625 IF c_resource%NOTFOUND THEN
1626 l_resource_id := -1;
1627 -- Adding an error message will cause the function
1628 -- to violate the WNDS pragma, preventing it from
1629 -- being able to be called from a SQL statement.
1630 END IF;
1631 CLOSE c_resource;
1632
1633 RETURN l_resource_id;
1634 END get_resource_id;
1635
1636 ---------------------------------------------------------------------
1637 -- FUNCTION
1638 -- Write_Conc_Log
1639 -- DESCRIPTION
1640 -- Writes the log for Concurrent programs
1641 -- History
1642 -- 07-Aug-2000 PTENDULK Created
1643 -- 08-Aug-2000 PTENDULK Write the output in to log instead of output
1644 -- NOTE
1645 -- If the parameter p_text is passed then the value sent will be printed
1646 -- as log else the messages in the stack are printed.
1647 ---------------------------------------------------------------------
1648 PROCEDURE Write_Conc_Log
1649 ( p_text IN VARCHAR2 := NULL)
1650 IS
1651 l_count NUMBER;
1652 l_msg VARCHAR2(2000);
1653 l_cnt NUMBER ;
1654 BEGIN
1655 IF p_text IS NULL THEN
1656 l_count := FND_MSG_PUB.count_msg;
1657 FOR l_cnt IN 1 .. l_count
1658 LOOP
1659 l_msg := FND_MSG_PUB.get(l_cnt, FND_API.g_false);
1660 FND_FILE.PUT_LINE(FND_FILE.LOG, '(' || l_cnt || ') ' || l_msg);
1661 END LOOP;
1662 ELSE
1663 FND_FILE.PUT_LINE(FND_FILE.LOG, p_text );
1664 END IF;
1665
1666 END Write_Conc_Log ;
1667
1671 -- get_system_status_type
1668
1669 -----------------------------------------------------------------------
1670 -- FUNCTION
1672 --
1673 -- HISTORY
1674 -- 14-SEP-2000 holiu Create.
1675 -- 29-May-2001 ptendulk Added system status for Schedule
1676 -- 20-May-2001 ptendulk Added system status for programs.
1677 -- 13-Jun-2001 ptendulk Added EONE
1678 -----------------------------------------------------------------------
1679 FUNCTION get_system_status_type(
1680 p_object IN VARCHAR2
1681 )
1682 RETURN VARCHAR2
1683 IS
1684 BEGIN
1685
1686 IF p_object = 'CAMP' THEN
1687 RETURN 'AMS_CAMPAIGN_STATUS';
1688 ELSIF p_object IN ('EVEH', 'EVEO','EONE', 'EVET') THEN
1689 RETURN 'AMS_EVENT_STATUS';
1690 ELSIF p_object = 'DELV' THEN
1691 RETURN 'AMS_DELIV_STATUS';
1692 ELSIF p_object = 'CSCH' THEN
1693 RETURN 'AMS_CAMPAIGN_SCHEDULE_STATUS' ;
1694 ELSIF p_object = 'RCAM' THEN
1695 RETURN 'AMS_PROGRAM_STATUS' ;
1696 ELSIF p_object = 'OFFR' THEN
1697 RETURN 'OZF_OFFER_STATUS' ;
1698 ELSIF p_object = 'PRIC' THEN
1699 RETURN 'OZF_PRICELIST_STATUS' ;
1700 ELSE
1701 RETURN NULL;
1702 END IF;
1703
1704 END get_system_status_type;
1705
1706
1707 -----------------------------------------------------------------------
1708 -- FUNCTION
1709 -- get_system_status_code
1710 --
1711 -- HISTORY
1712 -- 14-SEP-2000 holiu Create.
1713 -----------------------------------------------------------------------
1714 FUNCTION get_system_status_code(
1715 p_user_status_id IN NUMBER
1716 )
1717 RETURN VARCHAR2
1718 IS
1719
1720 l_status_code VARCHAR2(30);
1721
1722 CURSOR c_status_code IS
1723 SELECT system_status_code
1724 FROM ams_user_statuses_vl
1725 WHERE user_status_id = p_user_status_id
1726 AND enabled_flag = 'Y';
1727
1728 BEGIN
1729
1730 OPEN c_status_code;
1731 FETCH c_status_code INTO l_status_code;
1732 CLOSE c_status_code;
1733
1734 RETURN l_status_code;
1735
1736 END get_system_status_code;
1737
1738
1739 -----------------------------------------------------------------------
1740 -- FUNCTION
1741 -- get_default_user_status
1742 --
1743 -- HISTORY
1744 -- 14-SEP-2000 holiu Create.
1745 -----------------------------------------------------------------------
1746 FUNCTION get_default_user_status(
1747 p_status_type IN VARCHAR2,
1748 p_status_code IN VARCHAR2
1749 )
1750 RETURN VARCHAR2
1751 IS
1752
1753 l_status_id NUMBER;
1754
1755 CURSOR c_status_id IS
1756 SELECT user_status_id
1757 FROM ams_user_statuses_vl
1758 WHERE system_status_type = p_status_type
1759 AND system_status_code = p_status_code
1760 AND default_flag = 'Y'
1761 AND enabled_flag = 'Y';
1762
1763 BEGIN
1764
1765 OPEN c_status_id;
1766 FETCH c_status_id INTO l_status_id;
1767 CLOSE c_status_id;
1768
1769 RETURN l_status_id;
1770
1771 END get_default_user_status;
1772
1773
1774 -----------------------------------------------------------------------
1775 -- PROCEDURE
1776 -- check_status_change
1777 --
1778 -- HISTORY
1779 -- 14-SEP-2000 holiu Create.
1780 -----------------------------------------------------------------------
1781 PROCEDURE check_status_change(
1782 p_object_type IN VARCHAR2,
1783 p_object_id IN NUMBER,
1784 p_old_status_id IN NUMBER,
1785 p_new_status_id IN NUMBER,
1786 x_approval_type OUT NOCOPY VARCHAR2,
1787 x_return_status OUT NOCOPY VARCHAR2
1788 )
1789 IS
1790
1791 l_theme_flag VARCHAR2(1);
1792 l_budget_flag VARCHAR2(1);
1793 l_status_type VARCHAR2(30);
1794 l_old_status_code VARCHAR2(30);
1795 l_new_status_code VARCHAR2(30);
1796
1797 CURSOR c_approval_flag IS
1798 SELECT theme_approval_flag, budget_approval_flag
1799 FROM ams_status_order_rules
1800 WHERE system_status_type = l_status_type
1801 AND current_status_code = l_old_status_code
1802 AND next_status_code = l_new_status_code;
1803
1804 BEGIN
1805
1806 x_return_status := FND_API.g_ret_sts_success;
1807 x_approval_type := NULL;
1808
1809 l_status_type := get_system_status_type(p_object_type);
1810 l_old_status_code := get_system_status_code(p_old_status_id);
1811 l_new_status_code := get_system_status_code(p_new_status_id);
1812
1813 IF l_old_status_code = l_new_status_code THEN
1814 RETURN;
1815 END IF;
1816
1817 OPEN c_approval_flag;
1818 FETCH c_approval_flag INTO l_theme_flag, l_budget_flag;
1819 IF c_approval_flag%NOTFOUND THEN
1820 x_return_status := FND_API.g_ret_sts_error;
1821 OZF_Utility_PVT.error_message('OZF_CAMP_BAD_STATUS_CHANGE');
1822 END IF;
1823 CLOSE c_approval_flag;
1824
1825 IF l_budget_flag = 'Y' THEN
1826 IF AMS_ObjectAttribute_PVT.check_object_attribute(
1827 p_object_type, p_object_id, 'BAPL') = FND_API.g_true
1828 THEN
1832 IF AMS_ObjectAttribute_PVT.check_object_attribute(
1829 x_approval_type := 'BUDGET';
1830 END IF;
1831 ELSIF l_theme_flag = 'Y' THEN
1833 p_object_type, p_object_id, 'TAPL') = FND_API.g_true
1834 THEN
1835 x_approval_type := 'THEME';
1836 END IF;
1837 END IF;
1838
1839 END check_status_change;
1840
1841
1842 --========================================================================
1843 -- Function
1844 -- Approval_required_flag
1845 -- Purpose
1846 -- This function will return the approval required flag for the
1847 -- given custom setup.
1848 --
1849 -- History
1850 -- 16-Jun-2001 ptendulk Created
1851 -- 19-Jun-2001 ptendulk Check specific attribute (bug in last code)
1852 --========================================================================
1853 FUNCTION Approval_Required_Flag( p_custom_setup_id IN NUMBER ,
1854 p_approval_type IN VARCHAR2)
1855 RETURN VARCHAR2 IS
1856 CURSOR c_custom_attr IS
1857 SELECT attr_available_flag
1858 FROM ams_custom_setup_attr
1859 WHERE custom_setup_id = p_custom_setup_id
1860 -- Following line is added by ptendulk on 19-Jun-2001
1861 AND object_attribute = p_approval_type ;
1862
1863 l_flag VARCHAR2(1) ;
1864 BEGIN
1865
1866 OPEN c_custom_attr;
1867 FETCH c_custom_attr INTO l_flag ;
1868 CLOSE c_custom_attr ;
1869 RETURN l_flag ;
1870
1871 END Approval_Required_Flag;
1872
1873 -----------------------------------------------------------------------
1874 -- PROCEDURE
1875 -- check_status_change
1876 --
1877 -- PURPOSE
1878 -- This procedure is created to override the obsoleted check_status_change
1879 -- procedure as object_attribute table is obsoleted now.
1880 --
1881 -- HISTORY
1882 -- 16-Jun-2001 ptendulk Created
1883 -- 02-Jul-2002 musman Added changes for deliverable approvals
1884 -----------------------------------------------------------------------
1885 PROCEDURE check_new_status_change(
1886 p_object_type IN VARCHAR2,
1887 p_object_id IN NUMBER,
1888 p_old_status_id IN NUMBER,
1889 p_new_status_id IN NUMBER,
1890 p_custom_setup_id IN NUMBER,
1891 x_approval_type OUT NOCOPY VARCHAR2,
1892 x_return_status OUT NOCOPY VARCHAR2
1893 )
1894 IS
1895
1896 l_theme_flag VARCHAR2(1);
1897 l_budget_flag VARCHAR2(1);
1898 l_status_type VARCHAR2(30);
1899 l_old_status_code VARCHAR2(30);
1900 l_new_status_code VARCHAR2(30);
1901
1902 l_custom_setup_attr VARCHAR2(4) := 'TAPL';
1903
1904 CURSOR c_approval_flag IS
1905 SELECT theme_approval_flag, budget_approval_flag
1906 FROM ams_status_order_rules
1907 WHERE system_status_type = l_status_type
1908 AND current_status_code = l_old_status_code
1909 AND next_status_code = l_new_status_code;
1910
1911 BEGIN
1912
1913 x_return_status := FND_API.g_ret_sts_success;
1914 x_approval_type := NULL;
1915
1916 l_status_type := get_system_status_type(p_object_type);
1917 l_old_status_code := get_system_status_code(p_old_status_id);
1918 l_new_status_code := get_system_status_code(p_new_status_id);
1919
1920 IF l_old_status_code = l_new_status_code THEN
1921 RETURN;
1922 END IF;
1923
1924 OPEN c_approval_flag;
1925 FETCH c_approval_flag INTO l_theme_flag, l_budget_flag;
1926 IF c_approval_flag%NOTFOUND THEN
1927 x_return_status := FND_API.g_ret_sts_error;
1928 OZF_Utility_PVT.error_message('OZF_CAMP_BAD_STATUS_CHANGE');
1929 END IF;
1930 CLOSE c_approval_flag;
1931
1932 IF l_budget_flag = 'Y' THEN
1933 IF Approval_Required_Flag(p_custom_setup_id, 'BAPL') = 'Y'
1934 THEN
1935 x_approval_type := 'BUDGET';
1936 END IF;
1937 ELSIF l_theme_flag = 'Y' THEN
1938
1939 /* since Deliv has only concept approval */
1940 IF p_object_type = 'DELV'
1941 THEN
1942 l_custom_setup_attr := 'CAPL';
1943 END IF;
1944 IF Approval_Required_Flag(p_custom_setup_id, l_custom_setup_attr) = 'Y'
1945 THEN
1946 x_approval_type := 'THEME';
1947 END IF;
1948 END IF;
1949
1950 END check_new_status_change;
1951
1952 ---------------------------------------------------------------------
1953 -- PROCEDURE
1954 -- Convert_Currency
1955 -- NOTE
1956 -- HISTORY
1957 -- 01-Sep-2000 slkrishn Created.
1958 -- 12-SEP-2000 mpande Updated
1959 -- 02/23/2001 mpande Updated for getting org id query
1960 -- 03/27/2001 MPANDE MOved from OZF to OZF
1961 ---------------------------------------------------------------------
1962 PROCEDURE convert_currency(
1963 p_set_of_books_id IN NUMBER
1964 ,p_from_currency IN VARCHAR2
1965 ,p_conversion_date IN DATE
1966 ,p_conversion_type IN VARCHAR2
1967 ,p_conversion_rate IN NUMBER
1968 ,p_amount IN NUMBER
1969 ,x_return_status OUT NOCOPY VARCHAR2
1970 ,x_acc_amount OUT NOCOPY NUMBER
1971 ,x_rate OUT NOCOPY NUMBER)
1975 l_max_roll_days NUMBER := -1;
1972 IS
1973 l_api_name VARCHAR2(30) := 'Convert Currency';
1974 l_to_currenvy VARCHAR2(30);
1976 l_user_rate NUMBER := NVL(p_conversion_rate, 1);
1977 l_numerator NUMBER;
1978 l_denominator NUMBER;
1979 l_org_id NUMBER;
1980 l_sob NUMBER;
1981 l_to_currency VARCHAR2(30);
1982
1983 --
1984 -- get functional currency
1985 -- gs.mrc_sob_type_code,
1986
1987 /* CURSOR c_get_gl_info(
1988 p_org_id IN NUMBER)
1989 IS
1990 SELECT gs.set_of_books_id
1991 ,gs.currency_code
1992 FROM gl_sets_of_books gs
1993 ,org_organization_definitions org
1994 WHERE gs.mrc_sob_type_code = 'P'
1995 AND org.set_of_books_id = gs.set_of_books_id
1996 AND org.operating_unit = p_org_id;
1997 */
1998 --02/23/2001 mpande changed as per Hornet requirements
1999 CURSOR c_get_gl_info( p_org_id IN NUMBER)
2000 IS
2001 SELECT gs.set_of_books_id
2002 , gs.currency_code
2003 FROM gl_sets_of_books gs
2004 , ozf_sys_parameters_all org
2005 WHERE org.set_of_books_id = gs.set_of_books_id
2006 AND NVL(org.org_id, -99) = NVL(p_org_id, -99);
2007
2008 BEGIN
2009
2010 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
2011
2012 IF l_org_id IS NULL THEN
2013 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2014 fnd_message.set_name('OZF', 'OZF_ORG_ID_NOTFOUND');
2015 fnd_msg_pub.add;
2016 END IF;
2017 RAISE fnd_api.g_exc_error;
2018 END IF;
2019
2020 OPEN c_get_gl_info(l_org_id);
2021 FETCH c_get_gl_info INTO l_sob, l_to_currency;
2022
2023 IF c_get_gl_info%NOTFOUND THEN
2024 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2025 fnd_message.set_name('OZF', 'OZF_GL_SOB_NOTFOUND');
2026 fnd_msg_pub.add;
2027 END IF;
2028
2029 RAISE fnd_api.g_exc_error;
2030 END IF;
2031 CLOSE c_get_gl_info;
2032
2033 --
2034 gl_currency_api.convert_closest_amount(
2035 x_from_currency => p_from_currency
2036 ,x_to_currency => l_to_currency
2037 ,x_conversion_date => p_conversion_date
2038 ,x_conversion_type => p_conversion_type
2039 ,x_user_rate => l_user_rate
2040 ,x_amount => p_amount
2041 ,x_max_roll_days => l_max_roll_days
2042 ,x_converted_amount => x_acc_amount
2043 ,x_denominator => l_denominator
2044 ,x_numerator => l_numerator
2045 ,x_rate => x_rate);
2046 --
2047
2048 EXCEPTION
2049 WHEN fnd_api.g_exc_error THEN
2050 x_return_status := fnd_api.g_ret_sts_error;
2051 WHEN gl_currency_api.no_rate THEN
2052 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2053 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2054 fnd_msg_pub.add;
2055 END IF;
2056
2057 x_return_status := fnd_api.g_ret_sts_error;
2058 WHEN gl_currency_api.invalid_currency THEN
2059 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2060 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2061 fnd_msg_pub.add;
2062 END IF;
2063
2064 x_return_status := fnd_api.g_ret_sts_error;
2065 WHEN OTHERS THEN
2066 RAISE;
2067 x_return_status := fnd_api.g_ret_sts_unexp_error;
2068 END convert_currency;
2069
2070 ---------------------------------------------------------------------
2071 -- PROCEDURE
2072 -- get_code_combinations
2073 --
2074 -- PURPOSE
2075 -- get code_combination concacnenated segments and ids
2076 -- 20-Sep-2000 slkrishn Created
2077 -- 03/27/2001 MPANDE MOved from OZF to OZF
2078 ---------------------------------------------------------------------
2079 FUNCTION get_code_combinations(
2080 p_code_combination_id IN NUMBER
2081 ,p_chart_of_accounts_id IN NUMBER)
2082 RETURN VARCHAR2
2083 IS
2084 l_api_name VARCHAR2(30) := 'Get_Code_Combinations';
2085 l_result BOOLEAN;
2086 l_app_name VARCHAR2(30) := 'SQLGL';
2087 l_flex_code VARCHAR2(30) := 'GL#';
2088 BEGIN
2089 l_result := fnd_flex_keyval.validate_ccid(
2090 appl_short_name => l_app_name
2091 ,key_flex_code => l_flex_code
2092 ,structure_number => p_chart_of_accounts_id
2093 ,combination_id => p_code_combination_id);
2094
2095 IF l_result THEN
2096 RETURN fnd_flex_keyval.concatenated_descriptions;
2097 ELSE
2098 RETURN '';
2099 END IF;
2100 EXCEPTION
2101 WHEN OTHERS THEN
2102 RAISE;
2103 END get_code_combinations;
2104 ---------------------------------------------------------------------
2105 -- PROCEDURE
2106 -- Convert_functional_Curr
2107 -- NOTE
2108 -- This procedures takes in amount and converts it to the functional currency
2109 -- and returns the converted amount,exchange_rate,set_of_book_id,
2110 -- f-nctional_currency_code,exchange_rate_date
2111
2112 -- HISTORY
2113 -- 20-Jul-2000 mpande Created.
2114 -- 02/23/2001 MPAnde Updated for getting org id query
2118 -- x_TC_CURRENCY_CODE IN OUT VARCHAR2,
2115 -- 03/27/2001 MPANDE MOved from OZF to OZF
2116 -- 01/13/2003 yzhao fix bug BUG 2750841(same as 2741039) - pass in org_id, default to null
2117 --parameter x_Amount1 IN OUT NUMBER -- reqd Parameter -- amount to be converted
2119 -- x_Set_of_books_id OUT NUMBER,
2120 -- x_MRC_SOB_TYPE_CODE OUT NUMBER, 'P' and 'R'
2121 -- We only do it for primary ('P' because we donot supprot MRC)
2122 -- x_FC_CURRENCY_CODE OUT VARCHAR2,
2123 -- x_EXCHANGE_RATE_TYPE OUT VARCHAR2,
2124 -- comes from a OZF profile or what ever is passed
2125 -- x_EXCHANGE_RATE_DATE OUT DATE,
2126 -- could come from a OZF profile but right now is sysdate
2127 -- x_EXCHANGE_RATE OUT VARCHAR2,
2128 -- x_return_status OUT VARCHAR2
2129 -- The following is the rule in the GL API
2130 -- If x_conversion_type = 'User', and the relationship between the
2131 -- two currencies is not fixed, x_user_rate will be used as the
2132 -- conversion rate to convert the amount
2133 -- else no_user_rate is required
2134
2135 ---------------------------------------------------------------------
2136
2137
2138 PROCEDURE calculate_functional_curr(
2139 p_from_amount IN NUMBER
2140 ,p_conv_date IN DATE DEFAULT SYSDATE
2141 ,p_tc_currency_code IN VARCHAR2
2142 ,p_org_id IN NUMBER DEFAULT NULL
2143 ,x_to_amount OUT NOCOPY NUMBER
2144 ,x_set_of_books_id OUT NOCOPY NUMBER
2145 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
2146 ,x_fc_currency_code OUT NOCOPY VARCHAR2
2147 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
2148 ,x_exchange_rate IN OUT NOCOPY NUMBER
2149 ,x_return_status OUT NOCOPY VARCHAR2)
2150 IS
2151 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
2152 l_user_rate CONSTANT NUMBER := 1;
2153 -- Currenty not used. -- this should be a profile
2154 l_max_roll_days CONSTANT NUMBER := -1;
2155 -- Negative so API rolls back to find the last conversion rate.
2156 -- this should be a profile
2157 l_denominator NUMBER; -- Not used in Marketing.
2158 l_numerator NUMBER; -- Not used in Marketing.
2159 l_conversion_type VARCHAR2(30);
2160 l_org_id NUMBER;
2161
2162 -- Cursor to get the primary set_of_books_id ,functional_currency_code
2163 -- changed the above query to look into operating unit and not organization_id
2164 --SEP12 mpande
2165 /*
2166 CURSOR c_get_gl_info(
2167 p_org_id IN NUMBER)
2168 IS
2169 SELECT gs.set_of_books_id
2170 ,gs.currency_code
2171 FROM gl_sets_of_books gs
2172 ,org_organization_definitions org
2173 WHERE org.set_of_books_id = gs.set_of_books_id
2174 AND org.operating_unit = p_org_id;
2175 */
2176 --02/23/2001 mpande changed as per Hornet requirements
2177 CURSOR c_get_gl_info(p_org_id IN NUMBER)
2178 IS
2179 SELECT gs.set_of_books_id
2180 , gs.currency_code
2181 FROM gl_sets_of_books gs
2182 , ozf_sys_parameters_all org
2183 WHERE org.set_of_books_id = gs.set_of_books_id
2184 AND NVL(org.org_id, -99) = NVL(p_org_id, -99);
2185
2186 BEGIN
2187 -- Initialize return status.
2188 x_return_status := fnd_api.g_ret_sts_success;
2189
2190 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2191 -- Mumu Pande 09/20/2000 Updated the following
2192 -- Get the currency conversion type from profile option
2193 IF x_exchange_rate_type IS NULL THEN
2194 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2195 ELSE
2196 l_conversion_type := x_exchange_rate_type;
2197 END IF;
2198
2199 IF l_conversion_type IS NULL THEN
2200 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2201 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
2202 fnd_msg_pub.add;
2203 END IF;
2204
2205 x_return_status := fnd_api.g_ret_sts_error;
2206 RETURN;
2207 ELSE
2208 IF ozf_utility_pvt.check_fk_exists('GL_DAILY_CONVERSION_TYPES',
2209 'CONVERSION_TYPE'
2210 ,l_conversion_type) = fnd_api.g_false
2211 THEN
2212 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2213 fnd_message.set_name('OZF', 'OZF_WRONG_CONVERSION_TYPE');
2214 fnd_msg_pub.add;
2215 END IF;
2216 x_return_status := fnd_api.g_ret_sts_error;
2217 RETURN;
2218 END IF;
2219 END IF;
2220
2221 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2222 /* yzhao: 01/13/2003 fix bug BUG 2750841(same as 2741039) - use org_id if it is passed,
2223 otherwise get from login session */
2224 IF (p_org_id IS NOT NULL) THEN
2225 l_org_id := p_org_id;
2226 ELSE
2227
2228 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
2229
2230 IF l_org_id IS NULL THEN
2231 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2232 fnd_message.set_name('OZF', 'OZF_ORG_ID_NOTFOUND');
2233 fnd_msg_pub.add;
2234 END IF;
2235
2236 RAISE fnd_api.g_exc_error;
2237 END IF;
2238
2239
2240 END IF;
2241
2242 IF (OZF_DEBUG_HIGH_ON) THEN
2243 ozf_utility_pvt.debug_message('debug: start ' || l_org_id);
2244 END IF;
2245
2246 x_mrc_sob_type_code := 'P';
2247 OPEN c_get_gl_info(l_org_id);
2248 FETCH c_get_gl_info INTO x_set_of_books_id, x_fc_currency_code;
2249
2250 IF c_get_gl_info%NOTFOUND THEN
2251 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2252 fnd_message.set_name('OZF', 'OZF_GL_SOB_NOTFOUND');
2253 fnd_msg_pub.add;
2254 END IF;
2255
2256 RAISE fnd_api.g_exc_error;
2257 END IF;
2258
2259 CLOSE c_get_gl_info;
2260 -- Call the proper GL API to convert the amount.
2261 gl_currency_api.convert_closest_amount(
2262 x_from_currency => p_tc_currency_code
2263 ,x_to_currency => x_fc_currency_code
2264 ,x_conversion_date => p_conv_date
2265 ,x_conversion_type => l_conversion_type
2266 ,x_user_rate => x_exchange_rate
2267 ,x_amount => p_from_amount
2268 ,x_max_roll_days => l_max_roll_days
2269 ,x_converted_amount => x_to_amount
2270 ,x_denominator => l_denominator
2271 ,x_numerator => l_numerator
2272 ,x_rate => x_exchange_rate);
2273
2274 x_exchange_rate_type := l_conversion_type;
2275 --
2276
2277 EXCEPTION
2278 WHEN fnd_api.g_exc_error THEN
2279 x_return_status := fnd_api.g_ret_sts_error;
2280 WHEN gl_currency_api.no_rate THEN
2281 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2282 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2283 fnd_msg_pub.add;
2284 END IF;
2285
2286 x_return_status := fnd_api.g_ret_sts_error;
2287 WHEN gl_currency_api.invalid_currency THEN
2288 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2289 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2290 fnd_msg_pub.add;
2291 END IF;
2292
2293 x_return_status := fnd_api.g_ret_sts_error;
2294 WHEN OTHERS THEN
2295 x_return_status := fnd_api.g_ret_sts_unexp_error;
2296
2297 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2298 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_functional_curency');
2299 END IF;
2300 END calculate_functional_curr;
2301
2302
2303 --======================================================================
2304 -- FUNCTION
2305 -- get_fund_ledger
2306
2307 -- PURPOSE
2308 -- The api return the budget's ledger in order:
2309 -- ledger on budget if specified - ledger on budget category if specified - budget creator default OU's ledger
2310 -- used for deriving budget's functional currency
2311 -- HISTORY
2312 -- 30-Sep-2005 kdass Create.
2313 --======================================================================
2314
2315 PROCEDURE get_fund_ledger(
2316 p_fund_id IN NUMBER
2317 ,x_org_id OUT NOCOPY NUMBER
2318 ,x_ledger_id OUT NOCOPY NUMBER
2319 ,x_return_status OUT NOCOPY VARCHAR2
2320 )
2321 IS
2322 l_ledger_name VARCHAR2(30) := NULL;
2323
2324 CURSOR c_fund_ledger IS
2325 SELECT ledger_id, org_id
2326 FROM ozf_funds_all_b
2327 WHERE fund_id = p_fund_id;
2328
2329 /* PENDING marketing category enhancement
2330 CURSOR c_category_ledger IS
2331 SELECT cat.ledger_id
2332 FROM ozf_funds_all_b fund, ams_categories_b cat
2333 WHERE fund_id = p_fund_id
2334 AND fund.category_id = cat.category_id;
2335 */
2336
2337 BEGIN
2338 x_ledger_id := NULL;
2339
2340 OPEN c_fund_ledger;
2341 FETCH c_fund_ledger INTO x_ledger_id, x_org_id;
2342 CLOSE c_fund_ledger;
2343
2344 IF x_ledger_id IS NOT NULL THEN
2345 x_return_status := fnd_api.g_ret_sts_success;
2346 RETURN;
2347 END IF;
2348
2349 /* PENDING marketing category enhancement
2350 OPEN c_category_ledger;
2351 FETCH c_category_ledger INTO x_ledger_id;
2352 CLOSE c_category_ledger;
2353
2354 IF x_ledger_id IS NOT NULL THEN
2355 x_return_status := fnd_api.g_ret_sts_success;
2356 RETURN;
2357 END IF;
2358 */
2359
2360 IF x_org_id IS NULL THEN
2361 x_ledger_id := NULL;
2362 x_return_status := fnd_api.g_ret_sts_success;
2363 RETURN;
2364 END IF;
2365
2366 -- Get the set_of_books_id for given org_id
2367 MO_UTILS.Get_Ledger_Info (
2368 p_operating_unit => x_org_id,
2369 p_ledger_id => x_ledger_id,
2370 p_ledger_name => l_ledger_name
2371 );
2372 x_return_status := fnd_api.g_ret_sts_success;
2373 RETURN;
2374
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377 x_return_status := fnd_api.g_ret_sts_unexp_error;
2378
2379 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2380 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2381 fnd_message.set_token('ROUTINE', 'OZF_UTLITY_PVT');
2382 fnd_message.set_token('ERRNO', sqlcode);
2383 fnd_message.set_token('REASON', sqlerrm);
2384 END IF;
2385
2386 END get_fund_ledger;
2387
2388
2389 --======================================================================
2390 -- FUNCTION
2391 -- get_object_org_ledger
2392
2393 -- PURPOSE
2394 -- The api return the object's org_id and ledger:
2395 -- offer: org_id for local offer
2396 -- user default org_id for global offer
2397 -- pric: org_id for price list
2398 -- budget: budget's org_id
2399 -- ledger on budget if specified - ledger on budget category if specified - budget creator default OU's ledger
2400 -- others: user default org_id for global offer
2401 -- HISTORY
2402 -- 13-Oct-2005 yzhao Create.
2403 --======================================================================
2404
2405 PROCEDURE get_object_org_ledger(
2406 p_object_id IN NUMBER
2407 ,p_object_type IN VARCHAR2
2408 ,x_org_id OUT NOCOPY NUMBER
2409 ,x_ledger_id OUT NOCOPY NUMBER
2410 ,x_return_status OUT NOCOPY VARCHAR2
2411 )
2412 IS
2413 l_ledger_name VARCHAR2(30);
2414
2415 -- Cursor to get the org_id for offer
2416 CURSOR c_offer_org_id (p_list_header_id IN NUMBER)IS
2417 SELECT org_id
2418 FROM ozf_offers
2419 WHERE qp_list_header_id = p_list_header_id;
2420
2421 -- Cursor to get the org_id for pric
2422 CURSOR c_pric_org_id (p_list_header_id IN NUMBER)IS
2423 SELECT orig_org_id
2424 FROM qp_list_headers_b
2425 WHERE list_header_id = p_list_header_id;
2426
2427 --Bugfix: 7555174, 7415805 (Start)
2428 CURSOR C_GET_OFFERS_BUDGET_ID(p_list_header_id IN NUMBER)IS
2429 SELECT budget_source_id, budget_source_type
2430 FROM ozf_act_budgets
2431 WHERE act_budget_used_by_id=p_list_header_id;
2432
2433 CURSOR c_camp_budget_id(p_campaign_id IN NUMBER) IS
2434 SELECT budget_source_id
2435 FROM ozf_act_budgets
2436 WHERE act_budget_used_by_id = p_campaign_id;
2437
2438 l_budget_source_id NUMBER;
2439 l_budget_source_type VARCHAR2(30);
2440 --Bugfix: 7555174, 7415805 (End)
2441
2442 BEGIN
2443 x_org_id := NULL;
2444 x_ledger_id := NULL;
2445
2446 IF p_object_type = 'OFFR' THEN
2447 OPEN c_offer_org_id( p_object_id) ;
2448 FETCH c_offer_org_id INTO x_org_id ;
2449 CLOSE c_offer_org_id ;
2450 IF OZF_DEBUG_HIGH_ON THEN
2451 ozf_utility_pvt.debug_message ('get_object_org_ledger() org_id for offer =' || x_org_id);
2452 END IF;
2453 ELSIF p_object_type = 'PRIC' THEN
2454 OPEN c_pric_org_id( p_object_id) ;
2455 FETCH c_pric_org_id INTO x_org_id ;
2456 CLOSE c_pric_org_id ;
2457 IF OZF_DEBUG_HIGH_ON THEN
2458 ozf_utility_pvt.debug_message ('get_object_org_ledger() org_id for pric =' || x_org_id);
2459 END IF;
2460 ELSIF p_object_type = 'FUND' THEN
2461 get_fund_ledger(p_fund_id => p_object_id
2462 , x_org_id => x_org_id
2463 , x_ledger_id => x_ledger_id
2464 , x_return_status => x_return_status);
2465 IF OZF_DEBUG_HIGH_ON THEN
2466 ozf_utility_pvt.debug_message ('get_object_org_ledger() org_id for fund =' || x_org_id
2467 || ' ledger_id=' || x_ledger_id);
2468 END IF;
2469 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2470 RAISE FND_API.g_exc_unexpected_error;
2471 END IF;
2472 END IF;
2473
2474 --Bugfix - 7555174 : Modified x_org_id check
2475 --Start (Bug-7555174)
2476 /*
2477 IF x_org_id IS NULL THEN
2478 x_org_id := NVL(mo_global.get_current_org_id, mo_utils.get_default_org_id);
2479 END IF;
2480 */
2481
2482 IF x_org_id IS NULL AND p_object_type = 'OFFR' THEN
2483 OPEN C_GET_OFFERS_BUDGET_ID(p_object_id);
2484 FETCH C_GET_OFFERS_BUDGET_ID INTO l_budget_source_id,l_budget_source_type;
2485 CLOSE C_GET_OFFERS_BUDGET_ID;
2486
2487 IF l_budget_source_type = 'CAMP' THEN
2488 OPEN c_camp_budget_id(l_budget_source_id);
2489 FETCH c_camp_budget_id INTO l_budget_source_id;
2490 CLOSE c_camp_budget_id;
2491 END IF;
2492
2493 ozf_utility_pvt.debug_message ('NP get_object_org_ledger() l_budget_source_id '|| l_budget_source_id);
2497 , x_return_status => x_return_status);
2494 get_fund_ledger(p_fund_id => l_budget_source_id
2495 , x_org_id => x_org_id
2496 , x_ledger_id => x_ledger_id
2498 END IF;
2499
2500 IF x_org_id IS NULL AND p_object_type <> 'OFFR' THEN
2501 x_org_id := NVL(mo_global.get_current_org_id, mo_utils.get_default_org_id);
2502 END IF;
2503 --End (Bug-7555174)
2504
2505 IF x_org_id IS NOT NULL AND
2506 x_ledger_id IS NULL THEN
2507 MO_UTILS.Get_Ledger_Info (
2508 p_operating_unit => x_org_id,
2509 p_ledger_id => x_ledger_id,
2510 p_ledger_name => l_ledger_name
2511 );
2512 END IF;
2513
2514 IF OZF_DEBUG_HIGH_ON THEN
2515 ozf_utility_pvt.debug_message ('get_object_org_ledger() final org_id =' || x_org_id
2516 || ' ledger_id=' || x_ledger_id);
2517 END IF;
2518 x_return_status := fnd_api.g_ret_sts_success;
2519
2520 EXCEPTION
2521 WHEN OTHERS THEN
2522 x_return_status := fnd_api.g_ret_sts_unexp_error;
2523
2524 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2525 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2526 fnd_message.set_token('ROUTINE', 'OZF_UTLITY_PVT');
2527 fnd_message.set_token('ERRNO', sqlcode);
2528 fnd_message.set_token('REASON', sqlerrm);
2529 END IF;
2530
2531 END get_object_org_ledger;
2532
2533
2534 ---------------------------------------------------------------------
2535 -- PROCEDURE
2536 -- calculate_functional_currency
2537 -- NOTE
2538 -- This procedures takes in amount and converts it to the functional currency
2539 -- and returns the converted amount,exchange_rate,ledger_id,
2540 -- functional_currency_code,exchange_rate_date
2541
2542 -- HISTORY
2543 -- 29-SEP-2005 kdass Created.
2544 ---------------------------------------------------------------------
2545 PROCEDURE calculate_functional_currency(
2546 p_from_amount IN NUMBER
2547 ,p_conv_date IN DATE DEFAULT SYSDATE
2548 ,p_tc_currency_code IN VARCHAR2
2549 ,p_ledger_id IN NUMBER DEFAULT NULL
2550 ,x_to_amount OUT NOCOPY NUMBER
2551 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
2552 ,x_fc_currency_code OUT NOCOPY VARCHAR2
2553 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
2554 ,x_exchange_rate IN OUT NOCOPY NUMBER
2555 ,x_return_status OUT NOCOPY VARCHAR2)
2556 IS
2557 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
2558 l_user_rate CONSTANT NUMBER := 1;
2559 -- Currenty not used. -- this should be a profile
2560 l_max_roll_days CONSTANT NUMBER := -1;
2561 -- Negative so API rolls back to find the last conversion rate.
2562 -- this should be a profile
2563 l_denominator NUMBER; -- Not used in Marketing.
2564 l_numerator NUMBER; -- Not used in Marketing.
2565 l_conversion_type VARCHAR2(30);
2566
2567 CURSOR c_get_gl_info(p_ledger_id IN NUMBER)
2568 IS
2569 SELECT currency_code
2570 FROM gl_ledgers_public_v
2571 WHERE ledger_id = p_ledger_id;
2572
2573 BEGIN
2574 -- Initialize return status.
2575 x_return_status := fnd_api.g_ret_sts_success;
2576
2577 IF x_exchange_rate_type IS NULL OR x_exchange_rate_type=FND_API.G_MISS_CHAR THEN
2578 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2579 ELSE
2580 l_conversion_type := x_exchange_rate_type;
2581 END IF;
2582
2583 IF l_conversion_type IS NULL THEN
2584 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2585 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
2586 fnd_msg_pub.add;
2587 END IF;
2588
2589 x_return_status := fnd_api.g_ret_sts_error;
2590 RETURN;
2591 ELSE
2592 IF ozf_utility_pvt.check_fk_exists('GL_DAILY_CONVERSION_TYPES',
2593 'CONVERSION_TYPE'
2594 ,l_conversion_type) = fnd_api.g_false
2595 THEN
2596 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2597 fnd_message.set_name('OZF', 'OZF_WRONG_CONVERSION_TYPE');
2598 fnd_msg_pub.add;
2599 END IF;
2600 x_return_status := fnd_api.g_ret_sts_error;
2601 RETURN;
2602 END IF;
2603 END IF;
2604
2605 x_mrc_sob_type_code := 'P';
2606
2607 OPEN c_get_gl_info(p_ledger_id);
2608 FETCH c_get_gl_info INTO x_fc_currency_code;
2609 CLOSE c_get_gl_info;
2610
2611 -- Call the proper GL API to convert the amount.
2612 gl_currency_api.convert_closest_amount(
2613 x_from_currency => p_tc_currency_code
2614 ,x_to_currency => x_fc_currency_code
2615 ,x_conversion_date => p_conv_date
2616 ,x_conversion_type => l_conversion_type
2617 ,x_user_rate => x_exchange_rate
2618 ,x_amount => p_from_amount
2619 ,x_max_roll_days => l_max_roll_days
2620 ,x_converted_amount => x_to_amount
2621 ,x_denominator => l_denominator
2622 ,x_numerator => l_numerator
2623 ,x_rate => x_exchange_rate);
2624
2625 x_exchange_rate_type := l_conversion_type;
2626
2627 EXCEPTION
2628 WHEN fnd_api.g_exc_error THEN
2629 x_return_status := fnd_api.g_ret_sts_error;
2630 WHEN gl_currency_api.no_rate THEN
2631 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2632 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2633 fnd_msg_pub.add;
2634 END IF;
2635
2636 x_return_status := fnd_api.g_ret_sts_error;
2637 WHEN gl_currency_api.invalid_currency THEN
2638 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2639 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2640 fnd_msg_pub.add;
2641 END IF;
2642
2643 x_return_status := fnd_api.g_ret_sts_error;
2644 WHEN OTHERS THEN
2645 x_return_status := fnd_api.g_ret_sts_unexp_error;
2646
2647 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2648 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_functional_curency');
2649 END IF;
2650 END calculate_functional_currency;
2651
2652 ---------------------------------------------------------------------
2653 -- PROCEDURE
2654 -- Convert_Currency
2655 -- NOTE
2656
2657 -- HISTORY
2658 -- 20-Jul-2000 mpande Created.
2659 --parameter p_from_currency IN VARCHAR2,
2660 -- p_to_currency IN VARCHAR2,
2661 -- p_conv_date IN DATE DEFAULT SYSDATE,
2662 -- p_from_amount IN NUMBER,
2663 -- x_to_amount OUT NUMBER
2664 -- If x_conversion_type = 'User', and the relationship between the
2665 -- two currencies is not fixed, x_user_rate will be used as the
2666 -- conversion rate to convert the amount
2667 -- else no_user_rate is required
2668 -- 02/23/2001 MPAnde Updated for getting org id query
2669 -- 03/27/2001 MPANDE MOved from OZF to OZF
2670 -- 04/07/2001 slkrishn Added p_conv_type and p_conv_rate with defaults
2671 ---------------------------------------------------------------------
2672
2673 PROCEDURE convert_currency(
2674 p_from_currency IN VARCHAR2
2675 ,p_to_currency IN VARCHAR2
2676 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
2677 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
2678 ,p_conv_date IN DATE DEFAULT SYSDATE
2679 ,p_from_amount IN NUMBER
2680 ,x_return_status OUT NOCOPY VARCHAR2
2681 ,x_to_amount OUT NOCOPY NUMBER
2682 ,x_rate OUT NOCOPY NUMBER)
2683 IS
2684 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
2685 l_user_rate CONSTANT NUMBER := 1;
2686 -- Currenty not used.
2687 -- this should be a profile
2688 l_max_roll_days CONSTANT NUMBER := -1;
2689 -- Negative so API rolls back to find the last conversion rate.
2690 -- this should be a profile
2691 l_denominator NUMBER; -- Not used in Marketing.
2692 l_numerator NUMBER; -- Not used in Marketing.
2693 l_conversion_type VARCHAR2(30); -- Curr conversion type; see API doc for details.
2694 BEGIN
2695 -- Initialize return status.
2696 x_return_status := fnd_api.g_ret_sts_success;
2697
2698 -- condition added to pass conversion types
2699 IF p_conv_type = FND_API.G_MISS_CHAR OR p_conv_type IS NULL THEN --Added for bug 7030415
2700 -- Get the currency conversion type from profile option
2701 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2702 -- Conversion type cannot be null in profile
2703 IF l_conversion_type IS NULL THEN
2704 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2705 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
2706 fnd_msg_pub.add;
2707 END IF;
2708 x_return_status := fnd_api.g_ret_sts_error;
2709 RETURN;
2710 END IF;
2711 ELSE
2712 l_conversion_type := p_conv_type;
2713 END IF;
2714
2715 -- Call the proper GL API to convert the amount.
2716 gl_currency_api.convert_closest_amount(
2717 x_from_currency => p_from_currency
2718 ,x_to_currency => p_to_currency
2719 ,x_conversion_date => p_conv_date
2720 ,x_conversion_type => l_conversion_type
2721 ,x_user_rate => p_conv_rate
2722 ,x_amount => p_from_amount
2723 ,x_max_roll_days => l_max_roll_days
2724 ,x_converted_amount => x_to_amount
2725 ,x_denominator => l_denominator
2726 ,x_numerator => l_numerator
2727 ,x_rate => x_rate);
2728 --
2729
2730 EXCEPTION
2731 WHEN gl_currency_api.no_rate THEN
2732 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2733 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2734 fnd_msg_pub.add;
2735 END IF;
2736
2737 x_return_status := fnd_api.g_ret_sts_error;
2738 WHEN gl_currency_api.invalid_currency THEN
2739 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2740 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2741 fnd_msg_pub.add;
2742 END IF;
2743
2747
2744 x_return_status := fnd_api.g_ret_sts_error;
2745 WHEN OTHERS THEN
2746 x_return_status := fnd_api.g_ret_sts_unexp_error;
2748 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2749 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_curency');
2750 END IF;
2751 END convert_currency;
2752
2753 /*============================================================================*/
2754 -- Start of Comments
2755 -- NAME
2756 -- Get_Resource_Role
2757 --
2758 -- PURPOSE
2759 -- This Procedure will be return the workflow user role for
2760 -- the resourceid sent
2761 -- Called By
2762 -- NOTES
2763 -- End of Comments
2764
2765 /*============================================================================*/
2766
2767 PROCEDURE Get_Resource_Role
2768 ( p_resource_id IN NUMBER,
2769 x_role_name OUT NOCOPY VARCHAR2,
2770 x_role_display_name OUT NOCOPY VARCHAR2 ,
2771 x_return_status OUT NOCOPY VARCHAR2
2772 )
2773 IS
2774 l_msg_count NUMBER;
2775 l_msg_data VARCHAR2(4000);
2776 l_error_msg VARCHAR2(4000);
2777
2778 CURSOR c_resource IS
2779 SELECT employee_id , user_id, category
2780 FROM ams_jtf_rs_emp_v
2781 WHERE resource_id = p_resource_id ;
2782
2783 l_person_id number;
2784 l_user_id number;
2785 l_category varchar2(30);
2786 BEGIN
2787 x_return_status := FND_API.G_RET_STS_SUCCESS;
2788 OPEN c_resource ;
2789 FETCH c_resource INTO l_person_id , l_user_id, l_category;
2790 IF c_resource%NOTFOUND THEN
2791 CLOSE c_resource ;
2792 x_return_status := FND_API.G_RET_STS_ERROR;
2793 OZF_Utility_PVT.error_message ('OZF_APPR_INVALID_RESOURCE_ID');
2794 return;
2795 END IF;
2796 CLOSE c_resource ;
2797 -- Pass the Employee ID to get the Role
2798 IF l_category = 'PARTY' THEN
2799 WF_DIRECTORY.getrolename
2800 ( p_orig_system => 'FND_USR',
2801 p_orig_system_id => l_user_id ,
2802 p_name => x_role_name,
2803 p_display_name => x_role_display_name
2804 );
2805 IF x_role_name is null then
2806 x_return_status := FND_API.G_RET_STS_ERROR;
2807 OZF_Utility_PVT.error_message ('OZF_APPR_INVALID_ROLE');
2808 return;
2809 END IF;
2810 ELSE
2811 WF_DIRECTORY.getrolename
2812 ( p_orig_system => 'PER',
2813 p_orig_system_id => l_person_id ,
2814 p_name => x_role_name,
2815 p_display_name => x_role_display_name
2816 );
2817 IF x_role_name is null then
2818 x_return_status := FND_API.G_RET_STS_ERROR;
2819 OZF_Utility_PVT.error_message ('OZF_APPR_INVALID_ROLE');
2820 return;
2821 END IF;
2822 END IF;
2823 EXCEPTION
2824 WHEN OTHERS THEN
2825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2826 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2827 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Get_Resource_Role');
2828 END IF;
2829 RAISE;
2830 END Get_Resource_Role;
2831
2832 --======================================================================
2833 -- Procedure Name: send_wf_standalone_message
2834 -- Type : Generic utility
2835 -- Pre-Req :
2836 -- Notes:
2837 -- Common utility to send standalone message without initiating
2838 -- process using workflow.
2839 -- Parameters:
2840 -- IN:
2841 -- p_item_type IN VARCHAR2 Required Default = "MAPGUTIL"
2842 -- item type for the workflow utility.
2843 -- p_message_name IN VARCHAR2 Required Default = "GEN_STDLN_MESG"
2844 -- Internal name for standalone message name
2845 -- p_subject IN VARCHAR2 Required
2846 -- Subject for the message
2847 -- p_body IN VARCHAR2 Optional
2848 -- Body for the message
2849 -- p_send_to_role_name IN VARCHAR2 Optional
2850 -- Role name to whom message is to be sent.
2851 -- Instead of this, one can send even p_send_to_res_id
2852 -- p_send_to_res_id IN NUMBER Optional
2853 -- Resource Id that will be used to get role name from WF_DIRECTORY.
2854 -- This is required if role name is not passed.
2855
2856 -- OUT:
2857 -- x_notif_id OUT NUMBER
2858 -- Notification Id created that is being sent to recipient.
2859 -- x_return_status OUT VARCHAR2
2860 -- Return status. If it is error, messages will be put in mesg pub.
2861 -- History:
2862 -- 11-Jan-2002 sveerave Created.
2863 --======================================================================
2864
2865 PROCEDURE send_wf_standalone_message(
2866 p_item_type IN VARCHAR2 := 'MAPGUTIL'
2867 ,p_message_name IN VARCHAR2 := 'GEN_STDLN_MESG'
2868 ,p_subject IN VARCHAR2
2869 ,p_body IN VARCHAR2 := NULL
2870 ,p_send_to_role_name IN VARCHAR2 := NULL
2871 ,p_send_to_res_id IN NUMBER := NULL
2875 IS
2872 ,x_notif_id OUT NOCOPY NUMBER
2873 ,x_return_status OUT NOCOPY VARCHAR2
2874 )
2876 l_role_name VARCHAR2(100) := p_send_to_role_name;
2877 l_display_role_name VARCHAR2(240);
2878 l_notif_id NUMBER;
2879
2880 BEGIN
2881 x_return_status := FND_API.G_RET_STS_SUCCESS;
2882 IF p_send_to_role_name IS NULL THEN
2883 OZF_UTILITY_PVT.get_resource_role
2884 ( p_resource_id => p_send_to_res_id,
2885 x_role_name => l_role_name,
2886 x_role_display_name => l_display_role_name,
2887 x_return_status => x_return_status
2888 );
2889 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2890 return;
2891 END IF;
2892 END IF;
2893 l_notif_id := WF_NOTIFICATION.Send
2894 ( role => l_role_name
2895 , msg_type => p_item_type
2896 , msg_name => p_message_name
2897 );
2898 WF_NOTIFICATION.SetAttrText( l_notif_id
2899 , 'GEN_MSG_SUBJECT'
2900 , p_subject
2901 );
2902 WF_NOTIFICATION.SetAttrText( l_notif_id
2903 , 'GEN_MSG_BODY'
2904 , p_body
2905 );
2906 WF_NOTIFICATION.SetAttrText( l_notif_id
2907 , 'GEN_MSG_SEND_TO'
2908 , l_role_name
2909 );
2910 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
2911 x_notif_id := l_notif_id;
2912 END send_wf_standalone_message;
2913
2914 --======================================================================
2915 -- FUNCTION
2916 -- Check_Status_Change
2917 --
2918 -- PURPOSE
2919 -- Created to check if the status change is valid or not.
2920 -- Returns FND_API.G_TRUE if it is valid status change
2921 -- or will return FND_API.G_FALSE
2922 --
2923 -- HISTORY
2924 -- 09-Jul-2001 ptendulk Create.
2925 --======================================================================
2926 FUNCTION Check_Status_Change(
2927 p_status_type IN VARCHAR2,
2928 p_current_status IN VARCHAR2,
2929 p_next_status IN VARCHAR2
2930 )
2931 RETURN VARCHAR2
2932 IS
2933 CURSOR c_stat_det IS
2934 SELECT 1 FROM DUAL
2935 WHERE EXISTS (SELECT * FROM ams_status_order_rules
2936 WHERE current_status_code = p_current_status
2937 AND next_status_code = p_next_status
2938 AND system_status_type = p_status_type ) ;
2939 l_dummy NUMBER ;
2940 BEGIN
2941
2942 OPEN c_stat_det ;
2943 FETCH c_stat_det INTO l_dummy ;
2944 CLOSE c_stat_det;
2945
2946 IF l_dummy IS NULL THEN
2947 RETURN FND_API.G_FALSE ;
2948 ELSE
2949 RETURN FND_API.G_TRUE ;
2950 END IF ;
2951 END Check_Status_Change;
2952 --======================================================================
2953 -- FUNCTION
2954 -- CurrRound
2955 --
2956 -- PURPOSE
2957 -- Returns the round value for an amount based on the currency
2958 --
2959 -- HISTORY
2960 -- 13-Sep-2001 slkrishn Create.
2961 --======================================================================
2962 FUNCTION CurrRound(
2963 p_amount IN NUMBER,
2964 p_currency_code IN VARCHAR2
2965 )
2966 RETURN NUMBER
2967 IS
2968 BEGIN
2969 RETURN gl_mc_currency_pkg.CurrRound(p_amount, p_currency_code);
2970 END CurrRound;
2971
2972 --======================================================================
2973 -- PROCEDURE
2974 -- get_install_info
2975 --
2976 -- PURPOSE
2977 -- Gets the installation information for an application
2978 -- with application_id p_dep_appl_id
2979 --
2980 -- HISTORY
2981 -- 19-Dec-2002 mayjain Create.
2982 --======================================================================
2983 procedure get_install_info(p_appl_id in number,
2984 p_dep_appl_id in number,
2985 x_status out nocopy varchar2,
2986 x_industry out nocopy varchar2,
2987 x_installed out nocopy number)
2988 IS
2989 l_installed BOOLEAN;
2990
2991 BEGIN
2992 l_installed := fnd_installation.get( appl_id => p_appl_id,
2993 dep_appl_id => p_dep_appl_id,
2994 status => x_status,
2995 industry => x_industry );
2996 IF (l_installed) THEN
2997 x_installed := 1;
2998 ELSE
2999 x_installed := 0;
3000 END IF;
3001
3002 END get_install_info;
3003
3004 --======================================================================
3005 -- PROCEDURE
3006 -- Get_Object_Name
3007 --
3008 -- PURPOSE
3009 -- Callback method for IBC to get the Associated Object name for an
3010 -- Electronic Deliverable Attachment.
3011 --
3012 -- HISTORY
3013 -- 3/7/2003 mayjain Create.
3014 --======================================================================
3015 PROCEDURE Get_Object_Name(
3016 p_association_type_code IN VARCHAR2
3017 ,p_associated_object_val1 IN VARCHAR2
3018 ,p_associated_object_val2 IN VARCHAR2
3019 ,p_associated_object_val3 IN VARCHAR2 DEFAULT NULL
3020 ,p_associated_object_val4 IN VARCHAR2 DEFAULT NULL
3021 ,p_associated_object_val5 IN VARCHAR2 DEFAULT NULL
3022 ,x_object_name OUT NOCOPY VARCHAR2
3023 ,x_object_code OUT NOCOPY VARCHAR2
3024 ,x_return_status OUT NOCOPY VARCHAR2
3025 ,x_msg_count OUT NOCOPY NUMBER
3026 ,x_msg_data OUT NOCOPY VARCHAR2
3027 )
3028 IS
3029
3030
3031 CURSOR Cur_Delv(p_delv_id IN NUMBER)
3032 IS
3033 SELECT deliverable_name
3034 FROM ams_deliverables_vl
3035 WHERE deliverable_id = p_delv_id;
3036
3037
3038 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJECT_NAME';
3039 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_UTILITY_PVT';
3040
3041 BEGIN
3042
3043 If p_association_type_code = 'OZF_DELV' then
3044 x_return_status := FND_API.G_RET_STS_SUCCESS;
3045
3046 OPEN Cur_Delv(p_associated_object_val1);
3047 FETCH Cur_Delv INTO x_object_name;
3048 CLOSE Cur_Delv;
3049
3050
3051 END IF;
3052 -- here you can add processing for other association type as the else part
3053
3054 EXCEPTION
3055 WHEN FND_API.G_EXC_ERROR THEN
3056 x_return_status := FND_API.G_RET_STS_ERROR;
3057 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3058 p_data => x_msg_data);
3059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3061 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3062 p_data => x_msg_data);
3063 WHEN OTHERS THEN
3064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3065 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3066 THEN
3067 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3068 END IF;
3069 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3070 p_data => x_msg_data);
3071
3072 END Get_Object_Name;
3073
3074
3075
3076 --========================================================================
3077 -- PROCEDURE
3078 -- get_user_id
3079 --
3080 -- PURPOSE
3081 -- This api will take a resource id and give the corresponding user_id
3082 --
3083 -- NOTE
3084 --
3085 -- HISTORY
3086 -- 28-may-2003 soagrawa Created
3087 --========================================================================
3088
3089
3090 FUNCTION get_user_id (
3091 p_resource_id IN NUMBER
3092 )
3093 RETURN NUMBER
3094 IS
3095 l_user_id NUMBER;
3096
3097 CURSOR c_user IS
3098 SELECT user_id
3099 FROM ams_jtf_rs_emp_v
3100 WHERE resource_id = p_resource_id;
3101 BEGIN
3102 OPEN c_user;
3103 FETCH c_user INTO l_user_id;
3104 IF c_user%NOTFOUND THEN
3105 l_user_id := -1;
3106 END IF;
3107 CLOSE c_user;
3108
3109 RETURN l_user_id;
3110 END get_user_id;
3111
3112 --========================================================================
3113 -- PROCEDURE
3114 -- get_party_name
3115 --
3116 -- PURPOSE
3117 -- This api returns party name given the customer_account_id
3118 --
3119 -- NOTE
3120 --
3121 -- HISTORY
3122 -- Wed Oct 29 2003:3/36 PM rssharma Created
3123 --========================================================================
3124
3125 FUNCTION get_party_name(p_cust_account_id IN NUMBER)
3126 RETURN VARCHAR2
3127 IS
3128 CURSOR c_party_name(p_cust_account_id NUMBER)
3129 IS
3130 SELECT hp.party_name FROM hz_parties hp , hz_cust_accounts hca
3131 WHERE hca.party_id = hp.party_id
3132 AND hca.status = 'A'
3133 AND hca.cust_account_id = p_cust_account_id;
3134 l_party_name hz_parties.party_name%type;
3135 BEGIN
3136 OPEN c_party_name(p_cust_account_id );
3137 FETCH c_party_name INTO l_party_name;
3138 CLOSE c_party_name;
3139 RETURN l_party_name;
3140 END;
3141
3142 --========================================================================
3143 -- PROCEDURE
3144 -- get_user_status_name
3145 --
3146 -- PURPOSE
3147 -- This api returns user_status_name given the user_status_id and the system_status_code
3148 --
3149 -- NOTE
3150 --
3151 -- HISTORY
3152 -- Wed Jan 21 2004:4/18 PM rssharma Created
3156 RETURN VARCHAR2
3153 --========================================================================
3154
3155 FUNCTION get_user_status_name(p_user_status_id IN NUMBER , p_system_status_type IN VARCHAR2)
3157 IS
3158 CURSOR c_user_status_name (p_user_status_id NUMBER , p_system_status_type VARCHAR2)IS
3159 SELECT name FROM ams_user_statuses_vl where user_status_id = p_user_status_id
3160 AND system_status_type = p_system_status_type;
3161 l_user_status_name ams_user_statuses_tl.name%type;
3162 BEGIN
3163 OPEN c_user_status_name(p_user_status_id , p_system_status_type );
3164 FETCH c_user_status_name INTO l_user_status_name;
3165 CLOSE c_user_status_name;
3166 RETURN l_user_status_name;
3167 END;
3168
3169 --========================================================================
3170 -- PROCEDURE
3171 -- get_currency_name
3172 --
3173 -- PURPOSE
3174 -- This api returns Currency Name given the Currency Code
3175 --
3176 -- NOTE
3177 --
3178 -- HISTORY
3179 -- Fri Jan 23 2004:3/18 PM rssharma Created
3180 --========================================================================
3181 FUNCTION get_currency_name(p_currency_code IN VARCHAR2) RETURN VARCHAR2
3182 IS
3183 CURSOR C_CURR_NAME(p_currency_code VARCHAR2) IS
3184 SELECT name FROM fnd_currencies_vl WHERE currency_code = p_currency_code;
3185
3186 l_currency_name fnd_currencies_vl.name%type;
3187
3188 BEGIN
3189 OPEN C_CURR_NAME(p_currency_code);
3190 FETCH C_CURR_NAME INTO l_currency_name;
3191 CLOSE C_CURR_NAME;
3192 return l_currency_name;
3193 END;
3194
3195 --========================================================================
3196 -- PROCEDURE
3197 -- get_currency_name
3198 --
3199 -- PURPOSE
3200 -- This api returns Media Name given the Media id
3201 --
3202 -- NOTE
3203 --
3204 -- HISTORY
3205 -- Fri Jan 23 2004:3/18 PM rssharma Created
3206 --========================================================================
3207 FUNCTION get_media_name(p_media_id IN NUMBER) RETURN VARCHAR2
3208 IS
3209 CURSOR c_media_name(p_media_id NUMBER) IS
3210 SELECT media_name FROM ams_media_vl
3211 WHERE media_id = p_media_id;
3212 l_media_name ams_media_tl.media_name%type;
3213
3214 BEGIN
3215 OPEN c_media_name(p_media_id);
3216 FETCH c_media_name INTO l_media_name;
3217 CLOSE c_media_name;
3218
3219 RETURN l_media_name;
3220 END;
3221
3222 --========================================================================
3223 -- PROCEDURE
3224 -- get_fnd_lookup_meaning
3225 --
3226 -- PURPOSE
3227 -- This api returns Meaning of a NON-OZF FND lookup given the lookupcode and lookup_type
3228 --
3229 -- NOTE
3230 --
3231 -- HISTORY
3232 -- Fri Jan 23 2004:3/18 PM rssharma Created
3233 --========================================================================
3234
3235 FUNCTION get_fnd_lookup_meaning(p_lookup_type IN VARCHAR2,p_lookup_code IN VARCHAR2) RETURN VARCHAR2
3236 IS
3237 CURSOR c_lookup_meaning(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2) IS
3238 SELECT meaning FROM fnd_lookups WHERE lookup_type = p_lookup_type
3239 AND lookup_code = p_lookup_code;
3240
3241 l_lookup_meaning fnd_lookups.meaning%type;
3242 BEGIN
3243 OPEN c_lookup_meaning(p_lookup_type,p_lookup_code);
3244 FETCH c_lookup_meaning INTO l_lookup_meaning;
3245 CLOSE c_lookup_meaning;
3246
3247 RETURN l_lookup_meaning;
3248 END;
3249
3250 --======================================================================
3251 -- FUNCTION
3252 -- Has_Sales_Rep_Role
3253 --
3254 -- PURPOSE
3255 -- This api returns FND_API.g_true if the given user has the "Sales
3256 -- Representative" role. It returns FND_API.g_false otherwise.
3257 --
3258 -- HISTORY
3259 -- 23-Feb-2004 yizhang Create.
3260 --======================================================================
3261 FUNCTION Has_Sales_Rep_Role(
3262 p_resource_id IN NUMBER
3263 ) RETURN VARCHAR2
3264 IS
3265 l_role_id number;
3266
3267 CURSOR csr_sales_rep(cv_resource_id IN NUMBER) IS
3268 select rr.role_id
3269 from jtf_rs_role_relations rr
3270 , jtf_rs_roles_vl rl
3271 where rr.role_id = rl.role_id
3272 and rl.role_type_code = 'SALES'
3273 and rl.role_code = 'SALES_REP'
3274 and rr.delete_flag = 'N'
3275 and TRUNC(sysdate) between TRUNC(rr.start_date_active)
3276 and TRUNC(nvl(rr.end_date_active,sysdate))
3277 and rr.role_resource_id = cv_resource_id;
3278
3279 BEGIN
3280 OPEN csr_sales_rep(p_resource_id);
3281 FETCH csr_sales_rep INTO l_role_id;
3282 IF csr_sales_rep%NOTFOUND THEN
3283 CLOSE csr_sales_rep;
3284 RETURN FND_API.g_false;
3285 END IF;
3286 CLOSE csr_sales_rep;
3287
3288 RETURN FND_API.g_true;
3289 END;
3290
3291 --======================================================================
3292 -- FUNCTION
3293 -- get_commited_amount
3294
3295 -- PURPOSE
3296 -- The api return committed budget amount.
3297
3298 -- HISTORY
3299 -- 17-Feb-2005 feliu Create.
3300 --======================================================================
3301
3302 FUNCTION get_commited_amount(p_list_header_id IN NUMBER)
3303 RETURN NUMBER
3304 IS
3308 SELECT SUM(NVL(plan_curr_committed_amt,0))
3305 p_committed_amount NUMBER := 0;
3306
3307 CURSOR curr_committed_amount(list_header_id NUMBER) IS
3309 FROM ozf_object_fund_summary
3310 WHERE object_id = list_header_id
3311 AND object_type = 'OFFR';
3312 /*
3313 SELECT SUM(AMOUNT)
3314 FROM(
3315 SELECT uti.plan_curr_amount amount
3316 FROM ozf_funds_utilized_all_b uti, ozf_act_budgets act
3317 WHERE uti.utilization_type ='REQUEST'
3318 AND component_type = 'OFFR'
3319 AND component_id = list_header_id
3320 AND act.activity_budget_id = uti.ams_activity_budget_id
3321 AND act.recal_flag is NULL
3322 UNION ALL
3323 SELECT 0-uti.plan_curr_amount amount
3324 FROM ozf_funds_utilized_all_b uti, ozf_act_budgets act
3325 WHERE uti.utilization_type ='TRANSFER'
3326 AND plan_type = 'OFFR'
3327 AND plan_id = list_header_id
3328 AND act.activity_budget_id = uti.ams_activity_budget_id
3329 AND act.recal_flag is NULL);
3330 */
3331 BEGIN
3332 OPEN curr_committed_amount( p_list_header_id ) ;
3333 FETCH curr_committed_amount INTO p_committed_amount ;
3334 CLOSE curr_committed_amount ;
3335 RETURN p_committed_amount ;
3336
3337 EXCEPTION
3338 WHEN OTHERS THEN
3339 RETURN 0;
3340 END;
3341
3342 --======================================================================
3343 -- FUNCTION
3344 -- get_commited_amount
3345
3346 -- PURPOSE
3347 -- The api return recal-committed budget amount.
3348
3349 -- HISTORY
3350 -- 17-Feb-2005 feliu Create.
3351 --======================================================================
3352
3353 FUNCTION get_recal_commited_amount(p_list_header_id IN NUMBER)
3354 RETURN NUMBER
3355 IS
3356 p_committed_amount NUMBER := 0;
3357
3358 CURSOR curr_committed_amount(list_header_id NUMBER) IS
3359 SELECT SUM(NVL(plan_curr_recal_committed_amt,0))
3360 FROM ozf_object_fund_summary
3361 WHERE object_id = list_header_id
3362 AND object_type = 'OFFR';
3363 /*
3364 SELECT SUM(AMOUNT)
3365 FROM(
3366 SELECT uti.plan_curr_amount amount
3367 FROM ozf_funds_utilized_all_b uti
3368 WHERE uti.utilization_type ='REQUEST'
3369 AND component_type = 'OFFR'
3370 AND component_id = list_header_id
3371 UNION ALL
3372 SELECT 0-uti.plan_curr_amount amount
3373 FROM ozf_funds_utilized_all_b uti
3374 WHERE uti.utilization_type ='TRANSFER'
3375 AND plan_type = 'OFFR'
3376 AND plan_id = list_header_id);
3377 */
3378 BEGIN
3379 OPEN curr_committed_amount( p_list_header_id ) ;
3380 FETCH curr_committed_amount INTO p_committed_amount ;
3381 CLOSE curr_committed_amount ;
3382 RETURN p_committed_amount ;
3383
3384 EXCEPTION
3385 WHEN OTHERS THEN
3386 RETURN 0;
3387 END;
3388
3389
3390 FUNCTION get_utilized_amount(p_list_header_id IN NUMBER)
3391 RETURN NUMBER
3392 IS
3393 p_utilized_amount NUMBER := 0;
3394
3395 CURSOR curr_utilized_amount(list_header_id NUMBER) IS
3396 SELECT SUM(NVL(plan_curr_utilized_amt,0))
3397 FROM ozf_object_fund_summary
3398 WHERE object_id = list_header_id
3399 AND object_type = 'OFFR';
3400
3401 /*
3402 SELECT SUM(uti.plan_curr_amount)
3403 FROM ozf_funds_utilized_all_vl uti
3404 WHERE uti.utilization_type IN
3405 ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
3406 AND plan_type = 'OFFR'
3407 AND plan_id = list_header_id;
3408 */
3409 BEGIN
3410 OPEN curr_utilized_amount( p_list_header_id ) ;
3411 FETCH curr_utilized_amount INTO p_utilized_amount ;
3412 CLOSE curr_utilized_amount ;
3413 RETURN p_utilized_amount ;
3414
3415 EXCEPTION
3416 WHEN OTHERS THEN
3417 RETURN 0;
3418 END;
3419
3420 --======================================================================
3421 -- FUNCTION
3422 -- get_commited_amount
3423
3424 -- PURPOSE
3425 -- The api return earned budget amount.
3426
3427 -- HISTORY
3428 -- 17-Feb-2005 feliu Create.
3429 --======================================================================
3430
3431 FUNCTION get_earned_amount(p_list_header_id IN NUMBER)
3432 RETURN NUMBER
3433 IS
3434 p_earned_amount NUMBER := 0;
3435
3436 CURSOR curr_earned_amount(list_header_id NUMBER) IS
3437 SELECT SUM(NVL(plan_curr_earned_amt,0))
3438 FROM ozf_object_fund_summary
3439 WHERE object_id = list_header_id
3440 AND object_type = 'OFFR';
3441 /*
3442 SELECT SUM(uti.plan_curr_amount)
3443 FROM ozf_funds_utilized_all_vl uti
3444 WHERE uti.utilization_type IN
3445 ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
3446 AND plan_type = 'OFFR'
3447 AND plan_id = p_list_header_id
3448 AND gl_posted_flag NOT in('N','F');
3449 */
3450 BEGIN
3451 OPEN curr_earned_amount( p_list_header_id ) ;
3452 FETCH curr_earned_amount INTO p_earned_amount ;
3453 CLOSE curr_earned_amount ;
3454 RETURN p_earned_amount ;
3455
3456 EXCEPTION
3457 WHEN OTHERS THEN
3458 RETURN 0;
3459 END;
3460
3461 --======================================================================
3462 -- FUNCTION
3463 -- get_commited_amount
3464
3465 -- PURPOSE
3466 -- The api return paid budget amount.
3467
3468 -- HISTORY
3469 -- 17-Feb-2005 feliu Create.
3470 --======================================================================
3471
3475 p_paid_amount NUMBER := 0;
3472 FUNCTION get_paid_amount(p_list_header_id IN NUMBER)
3473 RETURN NUMBER
3474 IS
3476
3477 CURSOR curr_paid_amount(list_header_id NUMBER) IS
3478 SELECT SUM(NVL(plan_curr_paid_amt,0))
3479 FROM ozf_object_fund_summary
3480 WHERE object_id = list_header_id
3481 AND object_type = 'OFFR';
3482
3483 /*
3484
3485 SELECT SUM(AMOUNT) FROM
3486 (SELECT SUM(plan_curr_amount - NVL(plan_curr_amount_remaining,0)) amount
3487 FROM ozf_funds_utilized_all_b util
3488 WHERE utilization_type = 'UTILIZED'
3489 AND NVL(util.gl_posted_flag,'Y') = 'Y'
3490 AND plan_type = 'OFFR'
3491 AND plan_id = list_header_id
3492 UNION AlL
3493 SELECT cuti.plan_curr_amount amount
3494 FROM ozf_funds_utilized_all_b util,
3495 ozf_claim_lines_util_all cuti,
3496 ozf_claim_lines_all cln,
3497 ozf_claims_all cla
3498 WHERE util.utilization_id(+) = cuti.utilization_id
3499 AND util.utilization_type IN ('ACCRUAL','ADJUSTMENT', 'CHARGEBACK', 'LEAD_ACCRUAL')
3500 AND cuti.claim_line_id = cln.claim_line_id
3501 AND cln.claim_id = cla.claim_id
3502 AND cla.status_code = 'CLOSED'
3503 AND util.plan_type = 'OFFR'
3504 AND util.plan_id = list_header_id);
3505 */
3506
3507 BEGIN
3508 OPEN curr_paid_amount( p_list_header_id ) ;
3509 FETCH curr_paid_amount INTO p_paid_amount ;
3510 CLOSE curr_paid_amount ;
3511 RETURN p_paid_amount ;
3512
3513 EXCEPTION
3514 WHEN OTHERS THEN
3515 RETURN 0;
3516 END;
3517
3518 FUNCTION get_org_name(p_org_id NUMBER)
3519 RETURN VARCHAR2
3520 IS
3521 /*
3522 -- Bug 5065276 SQL ID 16501642
3523 -- The name can be fetched directly from the source table
3524 -- rather than the view
3525 CURSOR c_org_name IS
3526 SELECT name
3527 FROM hr_operating_units
3528 WHERE organization_id = p_org_id;
3529 */
3530
3531 CURSOR c_org_name IS
3532 SELECT name
3533 FROM hr_all_organization_units_tl
3534 WHERE organization_id = p_org_id
3535 AND language = userenv('LANG');
3536
3537 l_org_name VARCHAR2(240);
3538
3539 BEGIN
3540 OPEN c_org_name;
3541 FETCH c_org_name INTO l_org_name;
3542 CLOSE c_org_name;
3543
3544 RETURN l_org_name;
3545 END get_org_name;
3546
3547 FUNCTION getAttributeName
3548 (
3549 p_attributeCode IN VARCHAR2
3550 )
3551 RETURN VARCHAR2
3552 IS
3553 BEGIN
3554 return getAttributeName(p_attributeCode => p_attributeCode, p_applicationId => G_OZF_APP_ID );
3555 END getAttributeName;
3556
3557 FUNCTION getAttributeName
3558 (
3559 p_attributeCode IN VARCHAR2
3560 , p_applicationId IN NUMBER
3561 ) RETURN VARCHAR2
3562 IS
3563 CURSOR c_name(cp_attributeCode VARCHAR2, cp_applicationId NUMBER) IS
3564 SELECT ak.attribute_label_long
3565 FROM ak_attributes_vl ak
3566 WHERE ak.attribute_code = upper(cp_attributeCode)
3567 AND ak.attribute_application_id = cp_applicationId;
3568
3569 l_attributeName AK_ATTRIBUTES_VL.NAME%TYPE;
3570 BEGIN
3571 OPEN c_name(cp_attributeCode => p_attributeCode, cp_applicationId => p_applicationId);
3572 FETCH c_name INTO l_attributeName;
3573 IF c_name%NOTFOUND THEN
3574 l_attributeName := null;
3575 END IF;
3576 CLOSE c_name;
3577 RETURN l_attributeName;
3578 END getAttributeName;
3579
3580
3581 END OZF_Utility_PVT;
[Home] [Help]